[Home] [Help]
PACKAGE BODY: APPS.CSTPLCAC
Source
1 PACKAGE BODY CSTPLCAC AS
2 /* $Header: CSTLCACB.pls 120.3.12020000.2 2012/07/11 12:55:51 vkatakam ship $ */
3
4
5 PROCEDURE assembly_completion (
6 i_cost_method_id IN NUMBER,
7 i_txn_id IN NUMBER,
8 i_txn_date IN DATE,
9 i_layer_id IN NUMBER,
10 i_inv_item_id IN NUMBER,
11 i_org_id IN NUMBER,
12 i_wip_entity_id IN NUMBER,
13 i_txn_qty IN NUMBER,
14 i_final_comp_flag IN VARCHAR2,
15 i_cost_type_id IN NUMBER,
16 i_res_cost_type_id IN NUMBER,
17 i_cost_group_id IN NUMBER,
18 i_acct_period_id IN NUMBER,
19 i_user_id IN NUMBER,
20 i_login_id IN NUMBER,
21 i_request_id IN NUMBER,
22 i_prog_id IN NUMBER,
23 i_prog_appl_id IN NUMBER,
24 o_movhd_cost_type_id OUT NOCOPY NUMBER,
25 o_err_num OUT NOCOPY NUMBER,
26 o_err_msg OUT NOCOPY VARCHAR2
27 )
28 IS
29
30 l_stmt_num NUMBER := 0;
31
32 l_sql_stmt VARCHAR2(8000);
33 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
34 l_layer cst_wip_layers%ROWTYPE;
35
36 l_txn_qty_remaining NUMBER;
37 l_consumed_qty NUMBER;
38
39 /* Bug 2186966 */
40
41 l_exp_item NUMBER;
42
43 l_lot_size NUMBER := 1; /* Added as part of LBM */
44 l_include_comp_yield NUMBER;
45
46 /* Changed as a part of LBM project Bug #3926918
47 Added decode to WRO.qpa to get proportioned qpa in
48 case of Lot Based Materials
49 Divide the value of quantity per assembly by component
50 yield factor if Include Component Yield Flag is checked
51 in WIP Parameters.*/
52 cursor wro_cursor is
53 select WRO.operation_seq_num,
54 WRO.inventory_item_id,
55 Decode(WRO.basis_type, 2, WRO.quantity_per_assembly/l_lot_size,
56 WRO.quantity_per_assembly)/
57 DECODE(l_include_comp_yield,
58 1, nvl(WRO.component_yield_factor,1),
59 1) quantity_per_assembly,
60 WRO.component_yield_factor
61 from wip_requirement_operations WRO
62 where WRO.wip_entity_id = i_wip_entity_id and
63 WRO.wip_supply_type not in (4,5,6) and
64 WRO.quantity_per_assembly <> 0;
65
66 l_comp_cost_source NUMBER;
67 l_c_cost_type_id NUMBER;
68 l_use_val_cost_type NUMBER;
69 l_err_code VARCHAR2(240);
70 l_src_cost_type_id NUMBER;
71 l_wip_entity_type NUMBER;
72 l_wro_count NUMBER; /* Added for bug 4246122*/
73
74
75 BEGIN
76
77 -- normally i_txn_qty > 0 for assembly completions
78
79 ----------------------------------------------------
80 -- Update temp_relieved_value to zero in all tables
81 ----------------------------------------------------
82
83 l_stmt_num := 10;
84
85 CSTPLMWI.reset_temp_columns
86 (
87 i_wip_entity_id,
88 o_err_num,
89 o_err_msg
90 );
91 IF o_err_num <> 0 THEN
92 RETURN;
93 END IF;
94
95
96 /*---------------------------------------------
97 Get the lot size of the job
98 (Form validation takes care lot size is not 0)
99 Added for Lot Based Materials project
100 ----------------------------------------------*/
101 l_stmt_num := 20;
102 SELECT wdj.start_quantity
103 INTO l_lot_size
104 FROM wip_discrete_jobs wdj
105 WHERE wdj.wip_entity_id = i_wip_entity_id
106 AND wdj.organization_id = i_org_id;
107
108 /*----------------------------------------------
109 Get the value of Include Component yield flag,
110 which will determine whether to include or not
111 component yield factor in quantity per assembly
112 ------------------------------------------------*/
113 l_stmt_num := 25;
114 SELECT nvl(include_component_yield, 1)
115 INTO l_include_comp_yield
116 FROM wip_parameters
117 WHERE organization_id = i_org_id;
118
119 -----------------------------------------
120 -- retrieve information for determining
121 -- one of five cases
122 -----------------------------------------
123
124 l_stmt_num := 45;
125 select we.entity_type
126 into l_wip_entity_type
127 from wip_entities we
128 where we.wip_entity_id = i_wip_entity_id and
129 we.entity_type in (1,3,4); /* excludes Repetitive */
130
131
132 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
133
134 l_stmt_num := 50;
135 select
136 wac.completion_cost_source,
137 nvl( wac.cost_type_id, -1 )
138 into
139 l_comp_cost_source,
140 l_c_cost_type_id
141 from
142 wip_accounting_classes wac,
143 wip_discrete_jobs wdj
144 where
145 wdj.wip_entity_id = i_wip_entity_id and
146 wdj.organization_id = i_org_id and
147 wdj.class_code = wac.class_code and
148 wdj.organization_id = wac.organization_id;
149
150 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
151
152 l_stmt_num := 53;
153 select
154 wac.completion_cost_source,
155 nvl( wac.cost_type_id, -1 )
156 into
157 l_comp_cost_source,
158 l_c_cost_type_id
159 from
160 wip_accounting_classes wac,
161 wip_flow_schedules wfs
162 where
163 wfs.wip_entity_id = i_wip_entity_id and
164 wfs.organization_id = i_org_id and
165 wfs.class_code = wac.class_code and
166 wfs.organization_id = wac.organization_id;
167
168 END IF;
169
170 -----------------------------------------------------
171 -- If a non-std job has no bill or routing associated
172 -- with it or if a std job has no bill or routing
173 -- associated with it - these need to be treated
174 -- specially.
175 -----------------------------------------------------
176
177 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
178
179 l_stmt_num := 60;
180 SELECT
181 decode( job_type,
182 1, decode( bom_revision,
183 NULL, decode(routing_revision,NULL,-1,1),
184 1 ),
185 3, decode( bom_reference_id,
186 NULL, decode(routing_reference_id,NULL,-1,1),
187 1 ),
188 1 )
189 into
190 l_use_val_cost_type
191 from
192 WIP_DISCRETE_JOBS
193 WHERE
194 WIP_ENTITY_ID = i_wip_entity_id AND
195 ORGANIZATION_ID = i_org_id;
196
197 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
198
199 l_stmt_num := 63;
200 SELECT
201 decode( bom_revision,
202 NULL, decode(routing_revision,NULL,-1,1),
203 1 )
204 into
205 l_use_val_cost_type
206 from
207 wip_flow_schedules wfs
208 WHERE
209 wfs.WIP_ENTITY_ID = i_wip_entity_id AND
210 wfs.ORGANIZATION_ID = i_org_id;
211
212 END IF;
213
214 /* Added for bug 4246122
215 Material Requirement can be added manually for the job */
216 IF (l_use_val_cost_type = -1) THEN
217 /* Commented for Bug6734270.If there is a resource
218 added manually then also the l_use_val_cost_type
219 should be 1
220
221 SELECT COUNT(*)
222 INTO l_wro_count
223 FROM wip_requirement_operations
224 WHERE wip_entity_id = i_wip_entity_id
225 AND organization_id = i_org_id
226 AND quantity_per_assembly <> 0;
227 */
228
229 SELECT COUNT(1)
230 INTO l_wro_count
231 FROM dual
232 WHERE EXISTS ( SELECT NULL
233 FROM wip_requirement_operations wro
234 WHERE wro.wip_entity_id = i_wip_entity_id
235 AND wro.quantity_per_assembly <>0
236 UNION ALL
237 SELECT NULL
238 FROM wip_operation_resources wor
239 WHERE wor.wip_entity_id = i_wip_entity_id
240 AND wor.usage_rate_or_amount <>0
241 );
242
243
244 if (l_wro_count > 0) then
245 l_use_val_cost_type := 1;
246 end if;
247 END IF;
248
249
250 /*----------------------------------------------
251 | If the completions are costed by the system, we
252 | follow the system rules for earning material
253 | ovhd upon completion. If the completion is
254 | costed by the cost type then we will earn
255 | material overhead based on the costs in the cost type
256 | We need to figure out, for the given job, where the
257 | costs are coming from and hence how MO is to be
258 | earned. This info will passed back to the calling
259 | rotuine and used by the cost processor.
260 |--------------------------------------------------+*/
261
262 l_stmt_num := 70;
263
264 IF( l_comp_cost_source = 1 ) THEN
265 o_movhd_cost_type_id := i_res_cost_type_id;
266 ELSE
267 o_movhd_cost_type_id := l_c_cost_type_id;
268 END IF;
269
270
271
272
273
274
275
276 ---------------------------------------------------------
277 -- Final Completion
278 ---------------------------------------------------------
279
280 IF ( i_final_comp_flag = 'Y' ) THEN
281 -- If final completion, flush out all WIP layer quantities,
282 -- then call Avg completion algorithm to flush out the values
283 -- from WROCD, WRO, WOO, WOR
284
285 l_stmt_num := 80;
286 update cst_wip_layers CWL
287 set
288 CWL.relieved_matl_comp_qty =
289 ( CWL.applied_matl_qty -
290 CWL.relieved_matl_scrap_qty -
291 CWL.relieved_matl_final_comp_qty ),
292 CWL.temp_relieved_qty =
293 ( CWL.applied_matl_qty -
294 CWL.relieved_matl_comp_qty -
295 CWL.relieved_matl_scrap_qty -
296 CWL.relieved_matl_final_comp_qty )
297 where
298 CWL.wip_entity_id = i_wip_entity_id and
299 ( CWL.applied_matl_qty -
300 CWL.relieved_matl_comp_qty -
301 CWL.relieved_matl_scrap_qty -
302 CWL.relieved_matl_final_comp_qty ) >= 0;
303
304 l_stmt_num := 90;
305 update cst_wip_layers CWL
306 set
307 CWL.relieved_matl_final_comp_qty =
308 ( CWL.applied_matl_qty -
309 CWL.relieved_matl_comp_qty -
310 CWL.relieved_matl_scrap_qty ),
311 CWL.temp_relieved_qty =
312 ( CWL.applied_matl_qty -
313 CWL.relieved_matl_comp_qty -
314 CWL.relieved_matl_scrap_qty -
315 CWL.relieved_matl_final_comp_qty )
316 where
317 CWL.wip_entity_id = i_wip_entity_id and
318 ( CWL.applied_matl_qty -
319 CWL.relieved_matl_comp_qty -
320 CWL.relieved_matl_scrap_qty -
321 CWL.relieved_matl_final_comp_qty ) < 0;
322
323 -- Call the Average Costing WIP Assembly Completion routine.
324 l_stmt_num := 100;
325 CSTPACWC.complete
326 (
327 i_trx_id => i_txn_id,
328 i_txn_qty => i_txn_qty,
329 i_txn_date => i_txn_date,
330 i_acct_period_id => i_acct_period_id,
331 i_wip_entity_id => i_wip_entity_id,
332 i_org_id => i_org_id,
333 i_inv_item_id => i_inv_item_id,
334 i_cost_type_id => i_cost_type_id,
335 i_res_cost_type_id => i_res_cost_type_id,
336 i_final_comp_flag => i_final_comp_flag,
337 i_layer_id => i_layer_id,
338 i_movhd_cost_type_id => o_movhd_cost_type_id,
339 i_cost_group_id => i_cost_group_id,
340 i_user_id => i_user_id,
341 i_login_id => i_login_id,
342 i_request_id => i_request_id,
343 i_prog_id => i_prog_id,
344 i_prog_appl_id => i_prog_appl_id,
345 err_num => o_err_num,
346 err_code => l_err_code,
347 err_msg => o_err_msg
348 );
349
350 RETURN;
351
352
353
354 ELSIF( l_comp_cost_source = 2 OR
355 ( l_comp_cost_source = 1 AND l_use_val_cost_type = -1) ) THEN
356
357 ---------------------------------------------------------------
358 -- Regular Completion, with user-specified cost type
359 -- OR
360 -- Regular Completion, supposed to be dynamic, but no bom/routing
361 --
362 -- In this case we complete using the cost from CICD
363 ---------------------------------------------------------------
364
365
366 -- If this was to be dynamically computed, but without
367 -- bom/routing, then we use the valuation cost type, which
368 -- should be the cost_type_id passed in to this function.
369
370 IF l_comp_cost_source = 1 THEN
371 l_src_cost_type_id := i_cost_type_id;
372 ELSE
373 l_src_cost_type_id := l_c_cost_type_id;
374 END IF;
375
376 l_stmt_num := 110;
377
378 INSERT INTO mtl_cst_txn_cost_details
379 (
380 TRANSACTION_ID,
381 ORGANIZATION_ID,
382 INVENTORY_ITEM_ID,
383 COST_ELEMENT_ID,
384 LEVEL_TYPE,
385 TRANSACTION_COST,
386 NEW_AVERAGE_COST,
387 PERCENTAGE_CHANGE,
388 VALUE_CHANGE,
389 LAST_UPDATE_DATE,
390 LAST_UPDATED_BY,
391 CREATION_DATE,
392 CREATED_BY,
393 LAST_UPDATE_LOGIN,
394 REQUEST_ID,
395 PROGRAM_APPLICATION_ID,
396 PROGRAM_ID,
397 PROGRAM_UPDATE_DATE
398 )
399 SELECT
400 i_txn_id,
401 i_org_id,
402 i_inv_item_id,
403 COST_ELEMENT_ID,
404 LEVEL_TYPE,
405 ITEM_COST,
406 NULL,
407 NULL,
408 NULL,
409 SYSDATE,
410 i_user_id,
411 SYSDATE,
412 i_user_id,
413 i_login_id,
414 i_request_id,
415 i_prog_appl_id,
416 i_prog_id,
417 SYSDATE
418 FROM CST_LAYER_COST_DETAILS
419 WHERE LAYER_ID = i_layer_id
420 AND NOT ( COST_ELEMENT_ID = 2 AND
421 LEVEL_TYPE = 1 );
422
423 ELSE
424
425 ------------------------------------------------------------
426 -- Derive the Comp costs dynamically based on current costs
427 -- in the JOb ...
428 ------------------------------------------------------------
429
430 ----------------------------------------------
431 -- Consume component material quantities
432 ----------------------------------------------
433
434
435 FOR wro_rec IN wro_cursor LOOP
436 /* Get the expense flag for the item */
437 --------------------------------------------------------
438 -- Get whether the Component is Asset/Expense
439 --------------------------------------------------------
440
441 SELECT decode(INVENTORY_ASSET_FLAG,'Y',0,1)
442 INTO l_exp_item
443 FROM MTL_SYSTEM_ITEMS
444 WHERE INVENTORY_ITEM_ID = wro_rec.inventory_item_id
445 AND ORGANIZATION_ID = i_org_id;
446
447
448 /* If item is not an expense item, create-consume layers */
449 IF ( l_exp_item <> 1 ) THEN
450 CSTPLMWI.init_wip_layers
451 (
452 i_wip_entity_id,
453 wro_rec.operation_seq_num,
454 wro_rec.inventory_item_id,
455 i_org_id,
456 i_txn_id,
457 i_layer_id,
458 i_user_id,
459 i_login_id,
460 i_request_id,
461 i_prog_id,
462 i_prog_appl_id,
463 o_err_num,
464 o_err_msg
465 );
466 IF o_err_num <> 0 THEN
467 RETURN;
468 END IF;
469
470
471 -- consume WIP layer(s)
472 -- assembly completions consume WIP in normal order
473 l_stmt_num := 120;
474 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
475 (
476 ' sign( CWL.applied_matl_qty - ' ||
477 ' CWL.relieved_matl_comp_qty - ' ||
478 ' CWL.relieved_matl_scrap_qty - ' ||
479 ' CWL.relieved_matl_final_comp_qty ) = ' ||
480 ' sign( :quantity_per_assembly ) ',
481 i_cost_method_id,
482 CSTPLMWI.NORMAL
483 );
484
485 l_stmt_num := 130;
486 open l_layer_cursor
487 for l_sql_stmt
488 using i_wip_entity_id,
489 wro_rec.operation_seq_num,
490 wro_rec.inventory_item_id,
491 wro_rec.quantity_per_assembly;
492
493 l_txn_qty_remaining := i_txn_qty * wro_rec.quantity_per_assembly;
494
495 LOOP
496 exit when l_txn_qty_remaining = 0;
497
498 l_stmt_num := 140;
499 fetch l_layer_cursor into l_layer;
500
501 l_stmt_num := 150;
502 IF l_layer_cursor%NOTFOUND THEN
503
504 l_layer := CSTPLMWI.get_last_layer
505 (
506 i_wip_entity_id,
507 wro_rec.operation_seq_num,
508 wro_rec.inventory_item_id,
509 o_err_num,
510 o_err_msg
511 );
512 IF o_err_num <> 0 THEN
513 RETURN;
514 END IF;
515
516 l_consumed_qty := l_txn_qty_remaining;
517
518 ELSE
519 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
520 least( sign( wro_rec.quantity_per_assembly ) *
521 ( l_layer.applied_matl_qty -
522 l_layer.relieved_matl_comp_qty -
523 l_layer.relieved_matl_scrap_qty -
524 l_layer.relieved_matl_final_comp_qty ),
525 sign( wro_rec.quantity_per_assembly ) *
526 l_txn_qty_remaining );
527 END IF;
528
529
530 l_stmt_num := 160;
531 update cst_wip_layers CWL
532 set
533 relieved_matl_comp_qty = relieved_matl_comp_qty + l_consumed_qty,
534 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
535 where
536 wip_layer_id = l_layer.wip_layer_id and
537 inv_layer_id = l_layer.inv_layer_id;
538
539 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
540
541 END LOOP; -- l_layer_cursor
542
543 l_stmt_num := 170;
544 close l_layer_cursor;
545
546 /* Don't close cursor here */
547 -- END LOOP; -- wro_cursor
548
549
550 /* Update WROCD for the non expense item */
551
552
553 -- update WROCD
554
555 l_stmt_num := 180;
556 update wip_req_operation_cost_details WROCD
557 set
558 (
559 WROCD.relieved_matl_completion_value,
560 WROCD.temp_relieved_value
561 )
562 =
563 (
564 select
565 NVL( WROCD.relieved_matl_completion_value, 0 ) +
566 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
567 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
568 from
569 cst_wip_layers CWL,
570 cst_wip_layer_cost_details CWLCD
571 where
572 CWL.wip_entity_id = WROCD.wip_entity_id and
573 CWL.operation_seq_num = WROCD.operation_seq_num and
574 CWL.inventory_item_id = WROCD.inventory_item_id and
575 CWL.temp_relieved_qty <> 0 and
576 CWLCD.wip_layer_id = CWL.wip_layer_id and
577 CWLCD.inv_layer_id = CWL.inv_layer_id and
578 CWLCD.cost_element_id = WROCD.cost_element_id and
579 CWLCD.level_type in (1, 2)
580 )
581 where
582 (
583 WROCD.wip_entity_id,
584 WROCD.operation_seq_num,
585 WROCD.inventory_item_id
586 )
587 IN
588 (
589 select wip_entity_id,
590 operation_seq_num,
591 inventory_item_id
592 from wip_requirement_operations WRO
593 where
594 WRO.wip_entity_id = i_wip_entity_id and
595 /* Restrict only to the current Item */
596 WRO.operation_seq_num = wro_rec.operation_seq_num and
597 WRO.inventory_item_id = wro_rec.inventory_item_id and
598 --
599 -- exclude bulk, supplier, phantom
600 --
601 WRO.wip_supply_type not in (4,5,6) and
602 WRO.quantity_per_assembly <> 0
603 );
604
605 /* Update WRO record for this item. */
606
607 -- update WRO
608 update wip_requirement_operations WRO
609 set relieved_matl_completion_qty
610 =
611 (
612 select
613 NVL( WRO.relieved_matl_completion_qty, 0 ) +
614 sum( CWL.temp_relieved_qty )
615 from
616 cst_wip_layers CWL
617 where
618 CWL.wip_entity_id = WRO.wip_entity_id and
619 CWL.operation_seq_num = WRO.operation_seq_num and
620 CWL.inventory_item_id = WRO.inventory_item_id and
621 CWL.temp_relieved_qty <> 0
622 )
623 where
624 WRO.wip_entity_id = i_wip_entity_id and
625 /* Only for Current Item */
626 WRO.operation_seq_num = wro_rec.operation_seq_num and
627 WRO.inventory_item_id = wro_rec.inventory_item_id and
628 --
629 -- exclude bulk, supplier, phantom
630 --
631 WRO.wip_supply_type not in (4,5,6) and
632 WRO.quantity_per_assembly <> 0;
633
634 ELSE
635 -- If Item is an Expense Item
636 -- Just Insert into WROCD if not already there and
637 -- update relieved_matl_completion_qty
638
639 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
640 (
641 WIP_ENTITY_ID,
642 OPERATION_SEQ_NUM,
643 ORGANIZATION_ID,
644 INVENTORY_ITEM_ID,
645 COST_ELEMENT_ID,
646 APPLIED_MATL_VALUE,
647 RELIEVED_MATL_COMPLETION_VALUE,
648 RELIEVED_MATL_SCRAP_VALUE,
649 LAST_UPDATED_BY,
650 LAST_UPDATE_DATE,
651 CREATION_DATE,
652 CREATED_BY,
653 LAST_UPDATE_LOGIN,
654 REQUEST_ID,
655 PROGRAM_APPLICATION_ID,
656 PROGRAM_ID,
657 PROGRAM_UPDATE_DATE
658 )
659 SELECT
660 i_wip_entity_id, -- WIP_ENTITY_ID,
661 wro_rec.operation_seq_num, -- OPERATION_SEQ_NUM,
662 i_org_id, -- ORGANIZATION_ID,
663 wro_rec.inventory_item_id, -- INVENTORY_ITEM_ID,
664 CCE.cost_element_id, -- COST_ELEMENT_ID,
665 0, -- APPLIED_MATL_VALUE,
666 0, -- RELIEVED_MATL_COMPLETION_VALUE,
667 0, -- RELIEVED_MATL_SCRAP_VALUE,
668 i_user_id, -- LAST_UPDATED_BY,
669 sysdate, -- LAST_UPDATE_DATE,
670 sysdate, -- CREATION_DATE,
671 i_user_id, -- CREATED_BY,
672 i_login_id, -- LAST_UPDATE_LOGIN,
673 i_request_id, -- REQUEST_ID,
674 i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
675 i_prog_id, -- PROGRAM_ID,
676 sysdate -- PROGRAM_UPDATE_DATE
677 from
678 cst_cost_elements CCE
679 where
680 NOT EXISTS
681 (
682 SELECT 'X'
683 FROM WIP_REQ_OPERATION_COST_DETAILS WROCD2
684 WHERE
685 WROCD2.WIP_ENTITY_ID = i_wip_entity_id AND
686 WROCD2.OPERATION_SEQ_NUM = wro_rec.operation_seq_num AND
687 WROCD2.INVENTORY_ITEM_ID = wro_rec.inventory_item_id AND
688 WROCD2.COST_ELEMENT_ID = CCE.cost_element_id
689 ) AND
690 EXISTS
691 (
692 select 'x'
693 from wip_requirement_operations WRO
694 where WRO.wip_entity_id = i_wip_entity_id and
695 WRO.operation_seq_num = wro_rec.operation_seq_num and
696 WRO.inventory_item_id = wro_rec.inventory_item_id and
697 WRO.wip_supply_type not in (4, 5, 6)
698 )
699 group by
700 CCE.cost_element_id;
701
702 /* Changed as part of LBM project. Added decode to qpa for Lot Based Materials */
703
704 UPDATE wip_requirement_operations w1
705 SET
706 relieved_matl_completion_qty =
707 (SELECT
708 nvl(w1.relieved_matl_completion_qty,0) +
709 i_txn_qty*(Decode(w2.basis_type, 2,w2.quantity_per_assembly/l_lot_size,
710 w2.quantity_per_assembly) /
711 decode(l_include_comp_yield,
712 1, nvl(w2.component_yield_factor,1),
713 1))
714 FROM
715 wip_requirement_operations w2
716 WHERE
717 w1.wip_entity_id = w2.wip_entity_id AND
718 w1.organization_id = w2.organization_id AND
719 w1.inventory_item_id = w2.inventory_item_id AND
720 w1.operation_seq_num = w2.operation_seq_num )
721 WHERE
722 --
723 -- Exclude bulk, supplier, phantom
724 --
725 w1.wip_supply_type not in (4,5,6) AND
726 w1.wip_entity_id = i_wip_entity_id AND
727 w1.organization_id = i_org_id AND
728 w1.inventory_item_id = wro_rec.inventory_item_id AND
729 w1.operation_seq_num = wro_rec.operation_seq_num AND
730 w1.quantity_per_assembly <> 0;
731
732
733 END IF; -- End IF Not Expense Item
734
735
736 END LOOP; -- wro_cursor
737
738
739 --------------------------------------------------------------
740 -- BEGIN Dual maintenance section with CSTPACCB.pls
741 --------------------------------------------------------------
742
743 -----------------------------------------------------------
744 -- Relieve This Level Resource costs/units from WIP ...
745 -----------------------------------------------------------
746
747 -- If we use the actual resource option, then use the snapshot for
748 -- both resources and overheads.
749
750 l_stmt_num := 190;
751
752 UPDATE wip_operation_resources w1
753 SET
754 (relieved_res_completion_units,
755 temp_relieved_value,
756 relieved_res_completion_value) =
757 (SELECT
758 nvl(w1.relieved_res_completion_units,0) +
759 decode(sign(applied_resource_units -
760 nvl(relieved_res_completion_units,0)-
761 nvl(relieved_res_final_comp_units,0)-
762 nvl(relieved_res_scrap_units,0)),
763 1,
764 (applied_resource_units -
765 nvl(relieved_res_completion_units,0)-
766 nvl(relieved_res_final_comp_units,0)-
767 nvl(relieved_res_scrap_units,0))*
768 --
769 -- new to solve divided by zero and over relieved
770 -- when txn_qty/completed - prior_completion - prior_scrap
771 -- is greater than or equal to one, set it to one
772 -- ie. flush out 1*value remain in the job 1/30/98
773 --
774 decode(sign(i_txn_qty - (cocd.quantity_completed -
775 nvl(prior_completion_quantity,0) -
776 nvl(prior_scrap_quantity,0))),
777 -1,i_txn_qty/(cocd.quantity_completed -
778 nvl(prior_completion_quantity,0) -
779 nvl(prior_scrap_quantity,0)),
780 1),
781 0),
782 decode(sign(applied_resource_value -
783 nvl(relieved_res_completion_value,0)-
784 nvl(relieved_variance_value,0)-
785 nvl(relieved_res_scrap_value,0)),
786 1,
787 (applied_resource_value -
788 nvl(relieved_res_completion_value,0)-
789 nvl(relieved_variance_value,0)-
790 nvl(relieved_res_scrap_value,0))*
791 --
792 -- new to solve divided by zero and over relieved
793 --
794 decode(sign(i_txn_qty - (cocd.quantity_completed -
795 nvl(prior_completion_quantity,0) -
796 nvl(prior_scrap_quantity,0))),
797 -1,i_txn_qty/(cocd.quantity_completed -
798 nvl(prior_completion_quantity,0) -
799 nvl(prior_scrap_quantity,0)),
800 1),
801 0),
802 nvl(w1.relieved_res_completion_value,0) +
803 decode(sign(applied_resource_value -
804 nvl(relieved_res_completion_value,0)-
805 nvl(relieved_variance_value,0)-
806 nvl(relieved_res_scrap_value,0)),
807 1,
808 (applied_resource_value -
809 nvl(relieved_res_completion_value,0)-
810 nvl(relieved_variance_value,0)-
811 nvl(relieved_res_scrap_value,0))*
812 --
813 -- new to solve divided by zero and over relieved
814 --
815 decode(sign(i_txn_qty - (cocd.quantity_completed -
816 nvl(prior_completion_quantity,0) -
817 nvl(prior_scrap_quantity,0))),
818 -1,i_txn_qty/(cocd.quantity_completed -
819 nvl(prior_completion_quantity,0) -
820 nvl(prior_scrap_quantity,0)),
821 1),
822 0)
823 FROM
824 wip_operation_resources w2,
825 cst_comp_snapshot cocd
826 WHERE
827 w1.wip_entity_id = w2.wip_entity_id AND
828 w1.operation_seq_num = w2.operation_seq_num AND
829 w1.resource_seq_num = w2.resource_seq_num AND
830 w1.organization_id = w2.organization_id AND
831 w1.basis_type = w2.basis_type AND /* Added for bug 5247584 */
832 w2.operation_seq_num = cocd.operation_seq_num AND
833 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
834 cocd.new_operation_flag = 2 AND */
835 cocd.transaction_id = i_txn_id)
836 WHERE
837 w1.wip_entity_id = i_wip_entity_id AND
838 w1.organization_id = i_org_id;
839
840
841
842 l_stmt_num := 200;
843
844 UPDATE wip_operation_overheads w1
845 SET
846 (relieved_ovhd_completion_units,
847 temp_relieved_value,
848 relieved_ovhd_completion_value) =
849 (SELECT
850 NVL(w1.relieved_ovhd_completion_units,0) +
851 decode(sign(applied_ovhd_units -
852 nvl(relieved_ovhd_completion_units,0)-
853 nvl(relieved_ovhd_final_comp_units,0)-
854 nvl(relieved_ovhd_scrap_units,0)),
855 1,
856 (applied_ovhd_units -
857 nvl(relieved_ovhd_completion_units,0)-
858 nvl(relieved_ovhd_final_comp_units,0)-
859 nvl(relieved_ovhd_scrap_units,0))*
860 --
861 -- new to solve divided by zero and over relieved
862 --
863 decode(sign(i_txn_qty - (cocd.quantity_completed -
864 nvl(prior_completion_quantity,0) -
865 nvl(prior_scrap_quantity,0))),
866 -1,i_txn_qty/(cocd.quantity_completed -
867 nvl(prior_completion_quantity,0) -
868 nvl(prior_scrap_quantity,0)),
869 1),
870 0),
871 decode(sign(applied_ovhd_value -
872 nvl(relieved_ovhd_completion_value,0)-
873 nvl(relieved_variance_value,0)-
874 nvl(relieved_ovhd_scrap_value,0)),
875 1,
876 (applied_ovhd_value -
877 nvl(relieved_ovhd_completion_value,0)-
878 nvl(relieved_variance_value,0)-
879 nvl(relieved_ovhd_scrap_value,0))*
880 --
881 -- new to solve divided by zero and over relieved
882 --
883 decode(sign(i_txn_qty - (cocd.quantity_completed -
884 nvl(prior_completion_quantity,0) -
885 nvl(prior_scrap_quantity,0))),
886 -1,i_txn_qty/(cocd.quantity_completed -
887 nvl(prior_completion_quantity,0) -
888 nvl(prior_scrap_quantity,0)),
889 1),
890 0),
891 nvl(w1.relieved_ovhd_completion_value,0) +
892 decode(sign(applied_ovhd_value -
893 nvl(relieved_ovhd_completion_value,0)-
894 nvl(relieved_variance_value,0)-
895 nvl(relieved_ovhd_scrap_value,0)),
896 1,
897 (applied_ovhd_value -
898 nvl(relieved_ovhd_completion_value,0)-
899 nvl(relieved_variance_value,0)-
900 nvl(relieved_ovhd_scrap_value,0))*
901 --
902 -- new to solve divided by zero and over relieved
903 --
904 decode(sign(i_txn_qty - (cocd.quantity_completed -
905 nvl(prior_completion_quantity,0) -
906 nvl(prior_scrap_quantity,0))),
907 -1,i_txn_qty/(cocd.quantity_completed -
908 nvl(prior_completion_quantity,0) -
909 nvl(prior_scrap_quantity,0)),
910 1),
911 0)
912 FROM
913 wip_operation_overheads w2,
914 cst_comp_snapshot cocd
915 WHERE
916 w1.wip_entity_id = w2.wip_entity_id AND
917 w1.operation_seq_num = w2.operation_seq_num AND
918 w1.resource_seq_num = w2.resource_seq_num AND
919 w1.overhead_id = w2.overhead_id AND
920 w1.organization_id = w2.organization_id AND
921 w1.basis_type = w2.basis_type AND /* Added for bug 5247584 */
922 w2.operation_seq_num = cocd.operation_seq_num AND
923 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
924 cocd.new_operation_flag = 2 AND */
925 cocd.transaction_id = i_txn_id)
926 WHERE
927 w1.wip_entity_id = i_wip_entity_id AND
928 w1.organization_id = i_org_id;
929
930
931
932 /************************************************************
933 * Insert into mtl_cst_txn_cost_details now that the *
934 * Costs have been computed ... *
935 * 3 statements are required --> one each for PL costs *
936 * , TL Res/OSP costs and TL ovhd costs. *
937 * Remember - the cst_txn_cost_detail tables stores unit *
938 * cost - but the wip tables store the value in the *
939 * temp_relieved_value column - so we have to divide by the *
940 * txn_qty to arrive at the unit cost. *
941 ************************************************************/
942
943
944 l_stmt_num := 210;
945
946 INSERT INTO mtl_cst_txn_cost_details
947 (
948 TRANSACTION_ID,
949 ORGANIZATION_ID,
950 INVENTORY_ITEM_ID,
951 COST_ELEMENT_ID,
952 LEVEL_TYPE,
953 TRANSACTION_COST,
954 NEW_AVERAGE_COST,
955 PERCENTAGE_CHANGE,
956 VALUE_CHANGE,
957 LAST_UPDATE_DATE,
958 LAST_UPDATED_BY,
959 CREATION_DATE,
960 CREATED_BY,
961 LAST_UPDATE_LOGIN,
962 REQUEST_ID,
963 PROGRAM_APPLICATION_ID,
964 PROGRAM_ID,
965 PROGRAM_UPDATE_DATE
966 )
967 SELECT
968 i_txn_id,
969 i_org_id,
970 i_inv_item_id,
971 wrocd.cost_element_id,
972 2,
973 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
974 NULL,
975 NULL,
976 NULL,
977 SYSDATE,
978 i_user_id,
979 SYSDATE,
980 i_user_id,
981 i_login_id,
982 i_request_id,
983 i_prog_appl_id,
984 i_prog_id,
985 SYSDATE
986 FROM
987 WIP_REQ_OPERATION_COST_DETAILS wrocd
988 where
989 WIP_ENTITY_ID = i_wip_entity_id AND
990 ORGANIZATION_ID = i_org_id
991 GROUP BY
992 wrocd.cost_element_id
993 HAVING
994 sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
995
996
997 l_stmt_num := 220;
998
999 INSERT INTO mtl_cst_txn_cost_details
1000 (
1001 TRANSACTION_ID,
1002 ORGANIZATION_ID,
1003 INVENTORY_ITEM_ID,
1004 COST_ELEMENT_ID,
1005 LEVEL_TYPE,
1006 TRANSACTION_COST,
1007 NEW_AVERAGE_COST,
1008 PERCENTAGE_CHANGE,
1009 VALUE_CHANGE,
1010 LAST_UPDATE_DATE,
1011 LAST_UPDATED_BY,
1012 CREATION_DATE,
1013 CREATED_BY,
1014 LAST_UPDATE_LOGIN,
1015 REQUEST_ID,
1016 PROGRAM_APPLICATION_ID,
1017 PROGRAM_ID,
1018 PROGRAM_UPDATE_DATE
1019 )
1020 SELECT
1021 i_txn_id,
1022 i_org_id,
1023 i_inv_item_id,
1024 br.cost_element_id,
1025 1,
1026 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
1027 NULL,
1028 NULL,
1029 NULL,
1030 SYSDATE,
1031 i_user_id,
1032 SYSDATE,
1033 i_user_id,
1034 i_login_id,
1035 i_request_id,
1036 i_prog_appl_id,
1037 i_prog_id,
1038 SYSDATE
1039 FROM
1040 BOM_RESOURCES BR,
1041 WIP_OPERATION_RESOURCES WOR
1042 WHERE
1043 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
1044 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
1045 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
1046 WOR.ORGANIZATION_ID = i_org_id
1047 GROUP BY
1048 BR.COST_ELEMENT_ID
1049 HAVING
1050 sum(nvl(wor.temp_relieved_value,0)) <> 0;
1051
1052 l_stmt_num := 230;
1053
1054 INSERT INTO mtl_cst_txn_cost_details
1055 (
1056 TRANSACTION_ID,
1057 ORGANIZATION_ID,
1058 INVENTORY_ITEM_ID,
1059 COST_ELEMENT_ID,
1060 LEVEL_TYPE,
1061 TRANSACTION_COST,
1062 NEW_AVERAGE_COST,
1063 PERCENTAGE_CHANGE,
1064 VALUE_CHANGE,
1065 LAST_UPDATE_DATE,
1066 LAST_UPDATED_BY,
1067 CREATION_DATE,
1068 CREATED_BY,
1069 LAST_UPDATE_LOGIN,
1070 REQUEST_ID,
1071 PROGRAM_APPLICATION_ID,
1072 PROGRAM_ID,
1073 PROGRAM_UPDATE_DATE
1074 )
1075 SELECT
1076 i_txn_id,
1077 i_org_id,
1078 i_inv_item_id,
1079 5,
1080 1,
1081 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
1082 NULL,
1083 NULL,
1084 NULL,
1085 SYSDATE,
1086 i_user_id,
1087 SYSDATE,
1088 i_user_id,
1089 i_login_id,
1090 i_request_id,
1091 i_prog_appl_id,
1092 i_prog_id,
1093 SYSDATE
1094 FROM
1095 WIP_OPERATION_OVERHEADS
1096 WHERE
1097 WIP_ENTITY_ID = i_wip_entity_id AND
1098 ORGANIZATION_ID = i_org_id
1099 HAVING
1100 SUM(nvl(temp_relieved_value,0)) <> 0;
1101
1102
1103 --------------------------------------------------------------
1104 -- END Dual maintenance section with CSTPACCB.pls
1105 --------------------------------------------------------------
1106
1107
1108 END IF; -- main IF
1109
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 o_err_num := SQLCODE;
1114 o_err_msg := 'CSTPLCAC.assembly_completion():' ||
1115 to_char(l_stmt_num) || ':' ||
1116 substr(SQLERRM,1,150);
1117
1118 END assembly_completion;
1119
1120
1121
1122
1123
1124
1125
1126 PROCEDURE assembly_return (
1127 i_cost_method_id IN NUMBER,
1128 i_txn_id IN NUMBER,
1129 i_layer_id IN NUMBER,
1130 i_inv_item_id IN NUMBER,
1131 i_org_id IN NUMBER,
1132 i_wip_entity_id IN NUMBER,
1133 i_txn_qty IN NUMBER,
1134 i_user_id IN NUMBER,
1135 i_login_id IN NUMBER,
1136 i_request_id IN NUMBER,
1137 i_prog_id IN NUMBER,
1138 i_prog_appl_id IN NUMBER,
1139 o_err_num OUT NOCOPY NUMBER,
1140 o_err_msg OUT NOCOPY VARCHAR2
1141 )
1142 IS
1143
1144 l_stmt_num NUMBER := 0;
1145
1146 l_sql_stmt VARCHAR2(8000);
1147 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
1148 l_layer cst_wip_layers%ROWTYPE;
1149
1150 l_txn_qty_remaining NUMBER;
1151 l_consumed_qty NUMBER;
1152
1153 /* Bug 2186966 */
1154 l_exp_item NUMBER;
1155
1156 l_lot_size NUMBER := 1; /* Added as part of LBM */
1157 l_include_comp_yield NUMBER;
1158
1159 /* Changed as a part of LBM project Bug #3926918
1160 Added decode to WRO.qpa to get proportioned qpa in
1161 case of Lot Based Materials
1162 Divide the value of quantity per assembly by component
1163 yield factor if Include Component Yield Flag is checked
1164 in WIP Parameters.*/
1165
1166 /* Begin Bug 4246122 */
1167 l_use_val_cost_type NUMBER;
1168 l_wip_entity_type NUMBER;
1169 l_comp_cost_source NUMBER;
1170 l_c_cost_type_id NUMBER;
1171 l_prior_completed_qty NUMBER;
1172 l_wro_count NUMBER;
1173 /* End Bug 4246122 */
1174
1175 cursor wro_cursor is
1176 select WRO.operation_seq_num,
1177 WRO.inventory_item_id,
1178 Decode(WRO.basis_type, 2, WRO.quantity_per_assembly/l_lot_size,
1179 WRO.quantity_per_assembly)/
1180 DECODE(l_include_comp_yield,
1181 1, nvl(WRO.component_yield_factor,1),
1182 1) quantity_per_assembly,
1183 WRO.relieved_matl_completion_qty,
1184 decode( nvl( CCS.prior_completion_quantity, 0 ), 0, 1,
1185 i_txn_qty / CCS.prior_completion_quantity ) component_ratio
1186 from wip_requirement_operations WRO,
1187 cst_comp_snapshot CCS
1188 where WRO.wip_entity_id = i_wip_entity_id and
1189 WRO.wip_supply_type not in (4,5,6) and
1190 WRO.quantity_per_assembly <> 0 and
1191 CCS.transaction_id = i_txn_id and
1192 CCS.wip_entity_id = WRO.wip_entity_id and
1193 CCS.operation_seq_num = WRO.operation_seq_num /* and
1194 Bug 1180589: This is an incorrect condition. New operations should be picked up too
1195 CCS.new_operation_flag = 2 */
1196 /* Begin Bug 4246122*/
1197 UNION ALL
1198 select WRO.operation_seq_num,
1199 WRO.inventory_item_id,
1200 WRO.quantity_per_assembly,
1201 WRO.relieved_matl_completion_qty,
1202 decode( nvl( WRO.relieved_matl_completion_qty, 0 ), 0, 1,
1203 i_txn_qty /l_prior_completed_qty ) component_ratio
1204 from wip_requirement_operations WRO
1205 where WRO.wip_entity_id = i_wip_entity_id and
1206 WRO.wip_supply_type not in (4,5,6) and
1207 WRO.quantity_per_assembly <> 0 and
1208 NOT EXISTS ( select 'Exists'
1209 from wip_operations wo
1210 where wo.wip_entity_id = i_wip_entity_id and
1211 wo.organization_id = i_org_id) ;
1212 /* End Bug 4246122 */
1213
1214 BEGIN
1215
1216 -- normally i_txn_qty < 0 for assembly return
1217 ----------------------------------------------------
1218 -- Update temp_relieved_value to zero in all tables
1219 ----------------------------------------------------
1220
1221 l_stmt_num := 10;
1222
1223 CSTPLMWI.reset_temp_columns
1224 (
1225 i_wip_entity_id,
1226 o_err_num,
1227 o_err_msg
1228 );
1229 IF o_err_num <> 0 THEN
1230 RETURN;
1231 END IF;
1232
1233 /* Begin Addition for Bug 4246122 */
1234
1235 l_stmt_num := 15;
1236 select we.entity_type
1237 into l_wip_entity_type
1238 from wip_entities we
1239 where we.wip_entity_id = i_wip_entity_id and
1240 we.entity_type in (1,3,4); /* excludes Repetitive */
1241
1242
1243 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
1244
1245 l_stmt_num := 20;
1246 select
1247 wac.completion_cost_source,
1248 nvl( wac.cost_type_id, -1 )
1249 into
1250 l_comp_cost_source,
1251 l_c_cost_type_id
1252 from
1253 wip_accounting_classes wac,
1254 wip_discrete_jobs wdj
1255 where
1256 wdj.wip_entity_id = i_wip_entity_id and
1257 wdj.organization_id = i_org_id and
1258 wdj.class_code = wac.class_code and
1259 wdj.organization_id = wac.organization_id;
1260
1261 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
1262
1263 l_stmt_num := 23;
1264 select
1265 wac.completion_cost_source,
1266 nvl( wac.cost_type_id, -1 )
1267 into
1268 l_comp_cost_source,
1269 l_c_cost_type_id
1270 from
1271 wip_accounting_classes wac,
1272 wip_flow_schedules wfs
1273 where
1274 wfs.wip_entity_id = i_wip_entity_id and
1275 wfs.organization_id = i_org_id and
1276 wfs.class_code = wac.class_code and
1277 wfs.organization_id = wac.organization_id;
1278
1279 END IF;
1280
1281 /*-----------------------------------------------------
1282 -- If a non-std job has no bill or routing associated
1283 -- with it or if a std job has no bill or routing
1284 -- associated with it - these need to be treated
1285 -- specially.
1286 ----------------------------------------------------- */
1287
1288 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
1289
1290 l_stmt_num := 25;
1291 SELECT
1292 decode( job_type,
1293 1, decode( bom_revision,
1294 NULL, decode(routing_revision,NULL,-1,1),
1295 1 ),
1296 3, decode( bom_reference_id,
1297 NULL, decode(routing_reference_id,NULL,-1,1),
1298 1 ),
1299 1 )
1300 into
1301 l_use_val_cost_type
1302 from
1303 WIP_DISCRETE_JOBS
1304 WHERE
1305 WIP_ENTITY_ID = i_wip_entity_id AND
1306 ORGANIZATION_ID = i_org_id;
1307
1308 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
1309
1310 l_stmt_num := 30;
1311 SELECT
1312 decode( bom_revision,
1313 NULL, decode(routing_revision,NULL,-1,1),
1314 1 )
1315 into
1316 l_use_val_cost_type
1317 from
1318 wip_flow_schedules wfs
1319 WHERE
1320 wfs.WIP_ENTITY_ID = i_wip_entity_id AND
1321 wfs.ORGANIZATION_ID = i_org_id;
1322
1323 END IF;
1324
1325 /* Material Requirements can be added manually for a job */
1326 if (l_use_val_cost_type = -1) then
1327 /* Commented for Bug6734270.If there is a resource
1328 added manually then also the l_use_val_cost_type
1329 should be 1
1330 select count(*)
1331 into l_wro_count
1332 from wip_requirement_operations
1333 where wip_entity_id = i_wip_entity_id
1334 and organization_id = i_org_id
1335 and quantity_per_assembly <>0;
1336 */
1337 SELECT COUNT(1)
1338 INTO l_wro_count
1339 FROM dual
1340 WHERE EXISTS ( SELECT NULL
1341 FROM wip_requirement_operations wro
1342 WHERE wro.wip_entity_id = i_wip_entity_id
1343 AND wro.quantity_per_assembly <>0
1344 UNION ALL
1345 SELECT NULL
1346 FROM wip_operation_resources wor
1347 WHERE wor.wip_entity_id = i_wip_entity_id
1348 AND wor.usage_rate_or_amount <>0
1349 );
1350
1351 if (l_wro_count > 0) then
1352 l_use_val_cost_type := 1;
1353 end if;
1354 end if;
1355
1356 IF ( l_comp_cost_source = 1 and l_use_val_cost_type = -1) THEN
1357 l_stmt_num :=35;
1358 INSERT INTO mtl_cst_txn_cost_details
1359 (
1360 TRANSACTION_ID,
1361 ORGANIZATION_ID,
1362 INVENTORY_ITEM_ID,
1363 COST_ELEMENT_ID,
1364 LEVEL_TYPE,
1365 TRANSACTION_COST,
1366 NEW_AVERAGE_COST,
1367 PERCENTAGE_CHANGE,
1368 VALUE_CHANGE,
1369 LAST_UPDATE_DATE,
1370 LAST_UPDATED_BY,
1371 CREATION_DATE,
1372 CREATED_BY,
1373 LAST_UPDATE_LOGIN,
1374 REQUEST_ID,
1375 PROGRAM_APPLICATION_ID,
1376 PROGRAM_ID,
1377 PROGRAM_UPDATE_DATE
1378 )
1379 SELECT
1380 i_txn_id,
1381 i_org_id,
1382 i_inv_item_id,
1383 COST_ELEMENT_ID,
1384 LEVEL_TYPE,
1385 ITEM_COST,
1386 NULL,
1387 NULL,
1388 NULL,
1389 SYSDATE,
1390 i_user_id,
1391 SYSDATE,
1392 i_user_id,
1393 i_login_id,
1394 i_request_id,
1395 i_prog_appl_id,
1396 i_prog_id,
1397 SYSDATE
1398 FROM
1399 CST_LAYER_COST_DETAILS
1400 WHERE LAYER_ID = i_layer_id
1401 AND NOT ( COST_ELEMENT_ID = 2 AND
1402 LEVEL_TYPE = 1 );
1403
1404 ELSE
1405 l_prior_completed_qty :=i_txn_qty;
1406 l_stmt_num :=40;
1407 select decode( nvl(sum(mmt.primary_quantity),0),0,i_txn_qty,sum(mmt.primary_quantity))
1408 into l_prior_completed_qty
1409 from mtl_material_transactions mmt
1410 where mmt.transaction_source_type_id=5
1411 and mmt.transaction_action_id in (31,32)
1412 and mmt.transaction_source_id = i_wip_entity_id
1413 and mmt.organization_id = i_org_id
1414 and mmt.costed_flag is NULL;
1415 /*End of Addition for Bug 4246122 */
1416
1417 /*---------------------------------------------
1418 Get the lot size of the job
1419 (Form validation takes care lot size is not 0)
1420 Added for Lot Based Materials project
1421 ----------------------------------------------*/
1422
1423 SELECT wdj.start_quantity
1424 INTO l_lot_size
1425 FROM wip_discrete_jobs wdj
1426 WHERE wdj.wip_entity_id = i_wip_entity_id
1427 AND wdj.organization_id = i_org_id;
1428
1429 /*----------------------------------------------
1430 Get the value of Include Component yield flag,
1431 which will determine whether to include or not
1432 component yield factor in quantity per assembly
1433 ------------------------------------------------*/
1434 SELECT nvl(include_component_yield, 1)
1435 INTO l_include_comp_yield
1436 FROM wip_parameters
1437 WHERE organization_id = i_org_id;
1438
1439 ---------------------------------------------
1440 -- Replenish component material quantities
1441 ---------------------------------------------
1442
1443 FOR wro_rec IN wro_cursor LOOP
1444
1445 /* Get the expense flag for the item */
1446 --------------------------------------------------------
1447 -- Get whether the Component is Asset/Expense
1448 --------------------------------------------------------
1449
1450 SELECT decode(INVENTORY_ASSET_FLAG,'Y',0,1)
1451 INTO l_exp_item
1452 FROM MTL_SYSTEM_ITEMS
1453 WHERE INVENTORY_ITEM_ID = wro_rec.inventory_item_id
1454 AND ORGANIZATION_ID = i_org_id;
1455
1456
1457 /* If item is not an expense item, create-consume layers */
1458 IF ( l_exp_item <> 1 ) THEN
1459
1460 CSTPLMWI.init_wip_layers
1461 (
1462 i_wip_entity_id,
1463 wro_rec.operation_seq_num,
1464 wro_rec.inventory_item_id,
1465 i_org_id,
1466 i_txn_id,
1467 i_layer_id,
1468 i_user_id,
1469 i_login_id,
1470 i_request_id,
1471 i_prog_id,
1472 i_prog_appl_id,
1473 o_err_num,
1474 o_err_msg
1475 );
1476 IF o_err_num <> 0 THEN
1477 RETURN;
1478 END IF;
1479
1480
1481 -- assembly completions consume WIP layer(s) in reverse order
1482 l_stmt_num := 50;
1483 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
1484 (
1485 ' sign( CWL.relieved_matl_comp_qty ) = ' ||
1486 ' sign( :quantity_per_assembly ) ',
1487 i_cost_method_id,
1488 CSTPLMWI.REVERSE
1489 );
1490
1491 l_stmt_num := 60;
1492 open l_layer_cursor
1493 for l_sql_stmt
1494 using i_wip_entity_id,
1495 wro_rec.operation_seq_num,
1496 wro_rec.inventory_item_id,
1497 wro_rec.quantity_per_assembly;
1498
1499
1500 -- reduce the relieved_matl_completion_qty
1501 -- by percentage using cst_comp_snapshot
1502 l_txn_qty_remaining := nvl(wro_rec.relieved_matl_completion_qty, 0) *
1503 nvl(wro_rec.component_ratio, 0);
1504
1505 LOOP
1506 exit when l_txn_qty_remaining = 0;
1507
1508 l_stmt_num := 70;
1509 fetch l_layer_cursor into l_layer;
1510
1511 l_stmt_num := 80;
1512 IF l_layer_cursor%NOTFOUND THEN
1513 l_layer := CSTPLMWI.get_last_layer
1514 (
1515 i_wip_entity_id,
1516 wro_rec.operation_seq_num,
1517 wro_rec.inventory_item_id,
1518 o_err_num,
1519 o_err_msg
1520 );
1521 l_consumed_qty := l_txn_qty_remaining;
1522
1523 ELSE
1524 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
1525 greatest( sign( wro_rec.quantity_per_assembly ) *
1526 -( l_layer.relieved_matl_comp_qty ),
1527 sign( wro_rec.quantity_per_assembly ) *
1528 l_txn_qty_remaining );
1529 END IF;
1530
1531 l_stmt_num := 90;
1532 update cst_wip_layers CWL
1533 set
1534 relieved_matl_comp_qty = relieved_matl_comp_qty + l_consumed_qty,
1535 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
1536 where
1537 wip_layer_id = l_layer.wip_layer_id and
1538 inv_layer_id = l_layer.inv_layer_id;
1539
1540 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
1541
1542 END LOOP; -- l_layer_cursor
1543
1544 l_stmt_num := 100;
1545 close l_layer_cursor;
1546
1547 -- Don't close wro_cursor here - Bug 2186966
1548 -- END LOOP; -- wro_cursor
1549
1550
1551
1552
1553
1554 -- update WROCD
1555 l_stmt_num := 110;
1556 update wip_req_operation_cost_details WROCD
1557 set
1558 (
1559 WROCD.relieved_matl_completion_value,
1560 WROCD.temp_relieved_value
1561 )
1562 =
1563 (
1564 select
1565 NVL( WROCD.relieved_matl_completion_value, 0 ) +
1566 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
1567 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
1568 from
1569 cst_wip_layers CWL,
1570 cst_wip_layer_cost_details CWLCD
1571 where
1572 CWL.wip_entity_id = WROCD.wip_entity_id and
1573 CWL.operation_seq_num = WROCD.operation_seq_num and
1574 CWL.inventory_item_id = WROCD.inventory_item_id and
1575 CWL.temp_relieved_qty <> 0 and
1576 CWLCD.wip_layer_id = CWL.wip_layer_id and
1577 CWLCD.inv_layer_id = CWL.inv_layer_id and
1578 CWLCD.cost_element_id = WROCD.cost_element_id and
1579 CWLCD.level_type in (1, 2)
1580 )
1581 where
1582 (
1583 WROCD.wip_entity_id,
1584 WROCD.operation_seq_num,
1585 WROCD.inventory_item_id
1586 )
1587 IN
1588 (
1589 select wip_entity_id,
1590 operation_seq_num,
1591 inventory_item_id
1592 from wip_requirement_operations WRO
1593 where
1594 WRO.wip_entity_id = i_wip_entity_id and
1595 /* Restrict only to the current Item */
1596 WRO.operation_seq_num = wro_rec.operation_seq_num and
1597 WRO.inventory_item_id = wro_rec.inventory_item_id and
1598 --
1599 -- exclude bulk, supplier, phantom
1600 --
1601 WRO.wip_supply_type not in (4,5,6) and
1602 WRO.quantity_per_assembly <> 0
1603 );
1604
1605
1606
1607 -- update WRO
1608 update wip_requirement_operations WRO
1609 set relieved_matl_completion_qty
1610 =
1611 (
1612 select
1613 NVL( WRO.relieved_matl_completion_qty, 0 ) +
1614 sum( CWL.temp_relieved_qty )
1615 from
1616 cst_wip_layers CWL
1617 where
1618 CWL.wip_entity_id = WRO.wip_entity_id and
1619 CWL.operation_seq_num = WRO.operation_seq_num and
1620 CWL.inventory_item_id = WRO.inventory_item_id and
1621 CWL.temp_relieved_qty <> 0
1622 )
1623 where
1624 WRO.wip_entity_id = i_wip_entity_id and
1625 /* Only for Current Item */
1626 WRO.operation_seq_num = wro_rec.operation_seq_num and
1627 WRO.inventory_item_id = wro_rec.inventory_item_id and
1628 --
1629 -- exclude bulk, supplier, phantom
1630 --
1631 WRO.wip_supply_type not in (4,5,6) and
1632 WRO.quantity_per_assembly <> 0;
1633
1634 ELSE
1635 -- If Item is an Expense Item
1636 -- Just Insert into WROCD if not already there and
1637 -- update relieved_matl_completion_qty
1638
1639 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
1640 (
1641 WIP_ENTITY_ID,
1642 OPERATION_SEQ_NUM,
1643 ORGANIZATION_ID,
1644 INVENTORY_ITEM_ID,
1645 COST_ELEMENT_ID,
1646 APPLIED_MATL_VALUE,
1647 RELIEVED_MATL_COMPLETION_VALUE,
1648 RELIEVED_MATL_SCRAP_VALUE,
1649 LAST_UPDATED_BY,
1650 LAST_UPDATE_DATE,
1651 CREATION_DATE,
1652 CREATED_BY,
1653 LAST_UPDATE_LOGIN,
1654 REQUEST_ID,
1655 PROGRAM_APPLICATION_ID,
1656 PROGRAM_ID,
1657 PROGRAM_UPDATE_DATE
1658 )
1659 SELECT
1660 i_wip_entity_id, -- WIP_ENTITY_ID,
1661 wro_rec.operation_seq_num, -- OPERATION_SEQ_NUM,
1662 i_org_id, -- ORGANIZATION_ID,
1663 wro_rec.inventory_item_id, -- INVENTORY_ITEM_ID,
1664 CCE.cost_element_id, -- COST_ELEMENT_ID,
1665 0, -- APPLIED_MATL_VALUE,
1666 0, -- RELIEVED_MATL_COMPLETION_VALUE,
1667 0, -- RELIEVED_MATL_SCRAP_VALUE,
1668 i_user_id, -- LAST_UPDATED_BY,
1669 sysdate, -- LAST_UPDATE_DATE,
1670 sysdate, -- CREATION_DATE,
1671 i_user_id, -- CREATED_BY,
1672 i_login_id, -- LAST_UPDATE_LOGIN,
1673 i_request_id, -- REQUEST_ID,
1674 i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
1675 i_prog_id, -- PROGRAM_ID,
1676 sysdate -- PROGRAM_UPDATE_DATE
1677 from
1678 cst_cost_elements CCE
1679 where
1680 NOT EXISTS
1681 (
1682 SELECT 'X'
1683 FROM WIP_REQ_OPERATION_COST_DETAILS WROCD2
1684 WHERE
1685 WROCD2.WIP_ENTITY_ID = i_wip_entity_id AND
1686 WROCD2.OPERATION_SEQ_NUM = wro_rec.operation_seq_num AND
1687 WROCD2.INVENTORY_ITEM_ID = wro_rec.inventory_item_id AND
1688 WROCD2.COST_ELEMENT_ID = CCE.cost_element_id
1689 ) AND
1690 EXISTS
1691 (
1692 select 'x'
1693 from wip_requirement_operations WRO
1694 where WRO.wip_entity_id = i_wip_entity_id and
1695 WRO.operation_seq_num = wro_rec.operation_seq_num and
1696 WRO.inventory_item_id = wro_rec.inventory_item_id and
1697 WRO.wip_supply_type not in (4, 5, 6)
1698 )
1699 group by
1700 CCE.cost_element_id;
1701
1702 /* Changed for LBM project. Added decode to qpa for Lot Based Materials */
1703
1704 UPDATE wip_requirement_operations w1
1705 SET
1706 relieved_matl_completion_qty =
1707 (SELECT
1708 nvl(w1.relieved_matl_completion_qty,0) +
1709 i_txn_qty*(DECODE(w2.basis_type, 2, w2.quantity_per_assembly/l_lot_size,
1710 w2.quantity_per_assembly)/
1711 DECODE(l_include_comp_yield,
1712 1, nvl(w2.component_yield_factor,1),
1713 1))
1714 FROM
1715 wip_requirement_operations w2
1716 WHERE
1717 w1.wip_entity_id = w2.wip_entity_id AND
1718 w1.organization_id = w2.organization_id AND
1719 w1.inventory_item_id = w2.inventory_item_id AND
1720 w1.operation_seq_num = w2.operation_seq_num )
1721 WHERE
1722 --
1723 -- Exclude bulk, supplier, phantom
1724 --
1725 w1.wip_supply_type not in (4,5,6) AND
1726 w1.wip_entity_id = i_wip_entity_id AND
1727 w1.organization_id = i_org_id AND
1728 w1.inventory_item_id = wro_rec.inventory_item_id AND
1729 w1.operation_seq_num = wro_rec.operation_seq_num AND
1730 w1.quantity_per_assembly <> 0;
1731
1732
1733 END IF; -- End IF Not Expense Item
1734
1735
1736 END LOOP; -- wro_cursor
1737
1738
1739
1740 --------------------------------------------------------------
1741 -- BEGIN Dual maintenance section with CSTPACCB.pls
1742 --------------------------------------------------------------
1743
1744 l_stmt_num := 120;
1745
1746 UPDATE wip_operation_resources w1
1747 SET
1748 (relieved_res_completion_units,
1749 temp_relieved_value,
1750 relieved_res_completion_value) =
1751 (SELECT
1752 --
1753 -- relieved_res_completion_units
1754 --
1755 nvl(w1.relieved_res_completion_units,0)+
1756 decode(SIGN(w2.relieved_res_completion_value),1,
1757 nvl(w2.relieved_res_completion_units,0)*
1758 decode(abs(i_txn_qty),
1759 prior_completion_quantity,-1,
1760 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1761 prior_completion_quantity)),
1762 0),
1763 --
1764 -- temp_relieved_value
1765 --
1766 decode(SIGN(w2.relieved_res_completion_value),1,
1767 nvl(W2.relieved_res_completion_value,0)*
1768 decode(abs(i_txn_qty),
1769 prior_completion_quantity,-1,
1770 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1771 prior_completion_quantity)),
1772 0),
1773 ---
1774 --- relieved_res_completion_value
1775 ---
1776 nvl(w1.relieved_res_completion_value,0)+
1777 decode(SIGN(w2.relieved_res_completion_value),1,
1778 nvl(w2.relieved_res_completion_value,0)*
1779 decode(abs(i_txn_qty),
1780 prior_completion_quantity,-1,
1781 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1782 prior_completion_quantity)),
1783 0)
1784 FROM
1785 wip_operation_resources w2,
1786 bom_resources BR,
1787 cst_comp_snapshot cocd
1788 WHERE
1789 w2.wip_entity_id = w1.wip_entity_id AND
1790 w2.organization_id = w1.organization_id AND
1791 w2.operation_seq_num = w1.operation_seq_num AND
1792 w2.resource_seq_num = w1.resource_seq_num AND
1793 w2.basis_type = w1.basis_type AND /* Added for bug 5247584 */
1794 BR.resource_id = w2.resource_id AND
1795 w2.wip_entity_id = cocd.wip_entity_id AND
1796 w2.operation_seq_num = cocd.operation_seq_num AND
1797 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1798 cocd.new_operation_flag = 2 AND */
1799 cocd.transaction_id = i_txn_id)
1800 WHERE
1801 w1.wip_entity_id = i_wip_entity_id AND
1802 w1.organization_id = i_org_id;
1803
1804
1805
1806
1807 l_stmt_num := 130;
1808
1809 UPDATE wip_operation_overheads w1
1810 SET
1811 (relieved_ovhd_completion_units,
1812 temp_relieved_value,
1813 relieved_ovhd_completion_value) =
1814 (SELECT
1815 ---
1816 --- relieved_ovhd_completion_units
1817 ---
1818 nvl(w1.relieved_ovhd_completion_units,0)+
1819 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1820 nvl(W2.relieved_ovhd_completion_units,0)*
1821 decode(abs(i_txn_qty),
1822 prior_completion_quantity,-1,
1823 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1824 prior_completion_quantity)),
1825 0),
1826 ---
1827 --- temp_relieved_value
1828 ---
1829 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1830 nvl(w2.relieved_ovhd_completion_value,0)*
1831 decode(abs(i_txn_qty),
1832 prior_completion_quantity,-1,
1833 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1834 prior_completion_quantity)),
1835 0),
1836
1837 ---
1838 --- relieved_ovhd_completion_value
1839 ---
1840 nvl(w1.relieved_ovhd_completion_value,0)+
1841 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1842 nvl(w2.relieved_ovhd_completion_value,0)*
1843 decode(abs(i_txn_qty),
1844 prior_completion_quantity,-1,
1845 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1846 prior_completion_quantity)),
1847 0)
1848
1849 FROM
1850 wip_operation_overheads w2,
1851 cst_comp_snapshot cocd
1852 WHERE
1853 w2.wip_entity_id = w1.wip_entity_id AND
1854 w2.organization_id = w1.organization_id AND
1855 w2.operation_seq_num = w1.operation_seq_num AND
1856 w2.resource_seq_num = w1.resource_seq_num AND
1857 w2.overhead_id = w1.overhead_id AND
1858 w2.basis_type = w1.basis_type AND /* Added for bug 5247584 */
1859 w2.wip_entity_id = cocd.wip_entity_id AND
1860 w2.operation_seq_num = cocd.operation_seq_num AND
1861 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1862 cocd.new_operation_flag = 2 AND */
1863 cocd.transaction_id = i_txn_id)
1864 WHERE
1865 w1.wip_entity_id = i_wip_entity_id AND
1866 w1.organization_id = i_org_id;
1867
1868
1869
1870
1871 l_stmt_num := 140;
1872
1873 INSERT INTO mtl_cst_txn_cost_details
1874 (
1875 TRANSACTION_ID,
1876 ORGANIZATION_ID,
1877 INVENTORY_ITEM_ID,
1878 COST_ELEMENT_ID,
1879 LEVEL_TYPE,
1880 TRANSACTION_COST,
1881 NEW_AVERAGE_COST,
1882 PERCENTAGE_CHANGE,
1883 VALUE_CHANGE,
1884 LAST_UPDATE_DATE,
1885 LAST_UPDATED_BY,
1886 CREATION_DATE,
1887 CREATED_BY,
1888 LAST_UPDATE_LOGIN,
1889 REQUEST_ID,
1890 PROGRAM_APPLICATION_ID,
1891 PROGRAM_ID,
1892 PROGRAM_UPDATE_DATE
1893 )
1894 SELECT
1895 i_txn_id,
1896 i_org_id,
1897 i_inv_item_id,
1898 wrocd.cost_element_id,
1899 2,
1900 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
1901 NULL,
1902 NULL,
1903 NULL,
1904 SYSDATE,
1905 i_user_id,
1906 SYSDATE,
1907 i_user_id,
1908 i_login_id,
1909 i_request_id,
1910 i_prog_appl_id,
1911 i_prog_id,
1912 SYSDATE
1913 FROM
1914 WIP_REQ_OPERATION_COST_DETAILS wrocd
1915 where
1916 WIP_ENTITY_ID = i_wip_entity_id AND
1917 ORGANIZATION_ID = i_org_id
1918 GROUP BY
1919 wrocd.cost_element_id
1920 HAVING
1921 sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
1922
1923 l_stmt_num := 150;
1924
1925 INSERT INTO mtl_cst_txn_cost_details
1926 (
1927 TRANSACTION_ID,
1928 ORGANIZATION_ID,
1929 INVENTORY_ITEM_ID,
1930 COST_ELEMENT_ID,
1931 LEVEL_TYPE,
1932 TRANSACTION_COST,
1933 NEW_AVERAGE_COST,
1934 PERCENTAGE_CHANGE,
1935 VALUE_CHANGE,
1936 LAST_UPDATE_DATE,
1937 LAST_UPDATED_BY,
1938 CREATION_DATE,
1939 CREATED_BY,
1940 LAST_UPDATE_LOGIN,
1941 REQUEST_ID,
1942 PROGRAM_APPLICATION_ID,
1943 PROGRAM_ID,
1944 PROGRAM_UPDATE_DATE
1945 )
1946 SELECT
1947 i_txn_id,
1948 i_org_id,
1949 i_inv_item_id,
1950 br.cost_element_id,
1951 1,
1952 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
1953 NULL,
1954 NULL,
1955 NULL,
1956 SYSDATE,
1957 i_user_id,
1958 SYSDATE,
1959 i_user_id,
1960 i_login_id,
1961 i_request_id,
1962 i_prog_appl_id,
1963 i_prog_id,
1964 SYSDATE
1965 FROM
1966 BOM_RESOURCES BR,
1967 WIP_OPERATION_RESOURCES WOR
1968 WHERE
1969 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
1970 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
1971 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
1972 WOR.ORGANIZATION_ID = i_org_id
1973 GROUP BY
1974 BR.COST_ELEMENT_ID
1975 HAVING
1976 sum(nvl(wor.temp_relieved_value,0)) <> 0;
1977
1978 l_stmt_num := 160;
1979
1980 INSERT INTO mtl_cst_txn_cost_details
1981 (
1982 TRANSACTION_ID,
1983 ORGANIZATION_ID,
1984 INVENTORY_ITEM_ID,
1985 COST_ELEMENT_ID,
1986 LEVEL_TYPE,
1987 TRANSACTION_COST,
1988 NEW_AVERAGE_COST,
1989 PERCENTAGE_CHANGE,
1990 VALUE_CHANGE,
1991 LAST_UPDATE_DATE,
1992 LAST_UPDATED_BY,
1993 CREATION_DATE,
1994 CREATED_BY,
1995 LAST_UPDATE_LOGIN,
1996 REQUEST_ID,
1997 PROGRAM_APPLICATION_ID,
1998 PROGRAM_ID,
1999 PROGRAM_UPDATE_DATE
2000 )
2001 SELECT
2002 i_txn_id,
2003 i_org_id,
2004 i_inv_item_id,
2005 5,
2006 1,
2007 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2008 NULL,
2009 NULL,
2010 NULL,
2011 SYSDATE,
2012 i_user_id,
2013 SYSDATE,
2014 i_user_id,
2015 i_login_id,
2016 i_request_id,
2017 i_prog_appl_id,
2018 i_prog_id,
2019 SYSDATE
2020 FROM
2021 WIP_OPERATION_OVERHEADS
2022 WHERE
2023 WIP_ENTITY_ID = i_wip_entity_id AND
2024 ORGANIZATION_ID = i_org_id
2025 HAVING
2026 SUM(nvl(temp_relieved_value,0)) <> 0;
2027
2028 --------------------------------------------------------------
2029 -- BEGIN Dual maintenance section with CSTPACCB.pls
2030 --------------------------------------------------------------
2031
2032 END IF;
2033
2034 EXCEPTION
2035 WHEN OTHERS THEN
2036 o_err_num := SQLCODE;
2037 o_err_msg := 'CSTPLCAC.assembly_return():' ||
2038 to_char(l_stmt_num) || ':' ||
2039 substr(SQLERRM,1,150);
2040
2041 END assembly_return;
2042
2043
2044
2045
2046
2047
2048
2049 END CSTPLCAC;