[Home] [Help]
PACKAGE BODY: APPS.CSTPLCAC
Source
1 PACKAGE BODY CSTPLCAC AS
2 /* $Header: CSTLCACB.pls 120.2.12010000.2 2008/08/08 12:30:43 smsasidh 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 cocd.new_operation_flag = 2 AND
834 cocd.transaction_id = i_txn_id)
835 WHERE
836 w1.wip_entity_id = i_wip_entity_id AND
837 w1.organization_id = i_org_id;
838
839
840
841 l_stmt_num := 200;
842
843 UPDATE wip_operation_overheads w1
844 SET
845 (relieved_ovhd_completion_units,
846 temp_relieved_value,
847 relieved_ovhd_completion_value) =
848 (SELECT
849 NVL(w1.relieved_ovhd_completion_units,0) +
850 decode(sign(applied_ovhd_units -
851 nvl(relieved_ovhd_completion_units,0)-
852 nvl(relieved_ovhd_final_comp_units,0)-
853 nvl(relieved_ovhd_scrap_units,0)),
854 1,
855 (applied_ovhd_units -
856 nvl(relieved_ovhd_completion_units,0)-
857 nvl(relieved_ovhd_final_comp_units,0)-
858 nvl(relieved_ovhd_scrap_units,0))*
859 --
860 -- new to solve divided by zero and over relieved
861 --
862 decode(sign(i_txn_qty - (cocd.quantity_completed -
863 nvl(prior_completion_quantity,0) -
864 nvl(prior_scrap_quantity,0))),
865 -1,i_txn_qty/(cocd.quantity_completed -
866 nvl(prior_completion_quantity,0) -
867 nvl(prior_scrap_quantity,0)),
868 1),
869 0),
870 decode(sign(applied_ovhd_value -
871 nvl(relieved_ovhd_completion_value,0)-
872 nvl(relieved_variance_value,0)-
873 nvl(relieved_ovhd_scrap_value,0)),
874 1,
875 (applied_ovhd_value -
876 nvl(relieved_ovhd_completion_value,0)-
877 nvl(relieved_variance_value,0)-
878 nvl(relieved_ovhd_scrap_value,0))*
879 --
880 -- new to solve divided by zero and over relieved
881 --
882 decode(sign(i_txn_qty - (cocd.quantity_completed -
883 nvl(prior_completion_quantity,0) -
884 nvl(prior_scrap_quantity,0))),
885 -1,i_txn_qty/(cocd.quantity_completed -
886 nvl(prior_completion_quantity,0) -
887 nvl(prior_scrap_quantity,0)),
888 1),
889 0),
890 nvl(w1.relieved_ovhd_completion_value,0) +
891 decode(sign(applied_ovhd_value -
892 nvl(relieved_ovhd_completion_value,0)-
893 nvl(relieved_variance_value,0)-
894 nvl(relieved_ovhd_scrap_value,0)),
895 1,
896 (applied_ovhd_value -
897 nvl(relieved_ovhd_completion_value,0)-
898 nvl(relieved_variance_value,0)-
899 nvl(relieved_ovhd_scrap_value,0))*
900 --
901 -- new to solve divided by zero and over relieved
902 --
903 decode(sign(i_txn_qty - (cocd.quantity_completed -
904 nvl(prior_completion_quantity,0) -
905 nvl(prior_scrap_quantity,0))),
906 -1,i_txn_qty/(cocd.quantity_completed -
907 nvl(prior_completion_quantity,0) -
908 nvl(prior_scrap_quantity,0)),
909 1),
910 0)
911 FROM
912 wip_operation_overheads w2,
913 cst_comp_snapshot cocd
914 WHERE
915 w1.wip_entity_id = w2.wip_entity_id AND
916 w1.operation_seq_num = w2.operation_seq_num AND
917 w1.resource_seq_num = w2.resource_seq_num AND
918 w1.overhead_id = w2.overhead_id AND
919 w1.organization_id = w2.organization_id AND
920 w1.basis_type = w2.basis_type AND /* Added for bug 5247584 */
921 w2.operation_seq_num = cocd.operation_seq_num AND
922 cocd.new_operation_flag = 2 AND
923 cocd.transaction_id = i_txn_id)
924 WHERE
925 w1.wip_entity_id = i_wip_entity_id AND
926 w1.organization_id = i_org_id;
927
928
929
930 /************************************************************
931 * Insert into mtl_cst_txn_cost_details now that the *
932 * Costs have been computed ... *
933 * 3 statements are required --> one each for PL costs *
934 * , TL Res/OSP costs and TL ovhd costs. *
935 * Remember - the cst_txn_cost_detail tables stores unit *
936 * cost - but the wip tables store the value in the *
937 * temp_relieved_value column - so we have to divide by the *
938 * txn_qty to arrive at the unit cost. *
939 ************************************************************/
940
941
942 l_stmt_num := 210;
943
944 INSERT INTO mtl_cst_txn_cost_details
945 (
946 TRANSACTION_ID,
947 ORGANIZATION_ID,
948 INVENTORY_ITEM_ID,
949 COST_ELEMENT_ID,
950 LEVEL_TYPE,
951 TRANSACTION_COST,
952 NEW_AVERAGE_COST,
953 PERCENTAGE_CHANGE,
954 VALUE_CHANGE,
955 LAST_UPDATE_DATE,
956 LAST_UPDATED_BY,
957 CREATION_DATE,
958 CREATED_BY,
959 LAST_UPDATE_LOGIN,
960 REQUEST_ID,
961 PROGRAM_APPLICATION_ID,
962 PROGRAM_ID,
963 PROGRAM_UPDATE_DATE
964 )
965 SELECT
966 i_txn_id,
967 i_org_id,
968 i_inv_item_id,
969 wrocd.cost_element_id,
970 2,
971 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
972 NULL,
973 NULL,
974 NULL,
975 SYSDATE,
976 i_user_id,
977 SYSDATE,
978 i_user_id,
979 i_login_id,
980 i_request_id,
981 i_prog_appl_id,
982 i_prog_id,
983 SYSDATE
984 FROM
985 WIP_REQ_OPERATION_COST_DETAILS wrocd
986 where
987 WIP_ENTITY_ID = i_wip_entity_id AND
988 ORGANIZATION_ID = i_org_id
989 GROUP BY
990 wrocd.cost_element_id
991 HAVING
992 sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
993
994
995 l_stmt_num := 220;
996
997 INSERT INTO mtl_cst_txn_cost_details
998 (
999 TRANSACTION_ID,
1000 ORGANIZATION_ID,
1001 INVENTORY_ITEM_ID,
1002 COST_ELEMENT_ID,
1003 LEVEL_TYPE,
1004 TRANSACTION_COST,
1005 NEW_AVERAGE_COST,
1006 PERCENTAGE_CHANGE,
1007 VALUE_CHANGE,
1008 LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY,
1010 CREATION_DATE,
1011 CREATED_BY,
1012 LAST_UPDATE_LOGIN,
1013 REQUEST_ID,
1014 PROGRAM_APPLICATION_ID,
1015 PROGRAM_ID,
1016 PROGRAM_UPDATE_DATE
1017 )
1018 SELECT
1019 i_txn_id,
1020 i_org_id,
1021 i_inv_item_id,
1022 br.cost_element_id,
1023 1,
1024 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
1025 NULL,
1026 NULL,
1027 NULL,
1028 SYSDATE,
1029 i_user_id,
1030 SYSDATE,
1031 i_user_id,
1032 i_login_id,
1033 i_request_id,
1034 i_prog_appl_id,
1035 i_prog_id,
1036 SYSDATE
1037 FROM
1038 BOM_RESOURCES BR,
1039 WIP_OPERATION_RESOURCES WOR
1040 WHERE
1041 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
1042 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
1043 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
1044 WOR.ORGANIZATION_ID = i_org_id
1045 GROUP BY
1046 BR.COST_ELEMENT_ID
1047 HAVING
1048 sum(nvl(wor.temp_relieved_value,0)) <> 0;
1049
1050 l_stmt_num := 230;
1051
1052 INSERT INTO mtl_cst_txn_cost_details
1053 (
1054 TRANSACTION_ID,
1055 ORGANIZATION_ID,
1056 INVENTORY_ITEM_ID,
1057 COST_ELEMENT_ID,
1058 LEVEL_TYPE,
1059 TRANSACTION_COST,
1060 NEW_AVERAGE_COST,
1061 PERCENTAGE_CHANGE,
1062 VALUE_CHANGE,
1063 LAST_UPDATE_DATE,
1064 LAST_UPDATED_BY,
1065 CREATION_DATE,
1066 CREATED_BY,
1067 LAST_UPDATE_LOGIN,
1068 REQUEST_ID,
1069 PROGRAM_APPLICATION_ID,
1070 PROGRAM_ID,
1071 PROGRAM_UPDATE_DATE
1072 )
1073 SELECT
1074 i_txn_id,
1075 i_org_id,
1076 i_inv_item_id,
1077 5,
1078 1,
1079 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
1080 NULL,
1081 NULL,
1082 NULL,
1083 SYSDATE,
1084 i_user_id,
1085 SYSDATE,
1086 i_user_id,
1087 i_login_id,
1088 i_request_id,
1089 i_prog_appl_id,
1090 i_prog_id,
1091 SYSDATE
1092 FROM
1093 WIP_OPERATION_OVERHEADS
1094 WHERE
1095 WIP_ENTITY_ID = i_wip_entity_id AND
1096 ORGANIZATION_ID = i_org_id
1097 HAVING
1098 SUM(nvl(temp_relieved_value,0)) <> 0;
1099
1100
1101 --------------------------------------------------------------
1102 -- END Dual maintenance section with CSTPACCB.pls
1103 --------------------------------------------------------------
1104
1105
1106 END IF; -- main IF
1107
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 o_err_num := SQLCODE;
1112 o_err_msg := 'CSTPLCAC.assembly_completion():' ||
1113 to_char(l_stmt_num) || ':' ||
1114 substr(SQLERRM,1,150);
1115
1116 END assembly_completion;
1117
1118
1119
1120
1121
1122
1123
1124 PROCEDURE assembly_return (
1125 i_cost_method_id IN NUMBER,
1126 i_txn_id IN NUMBER,
1127 i_layer_id IN NUMBER,
1128 i_inv_item_id IN NUMBER,
1129 i_org_id IN NUMBER,
1130 i_wip_entity_id IN NUMBER,
1131 i_txn_qty IN NUMBER,
1132 i_user_id IN NUMBER,
1133 i_login_id IN NUMBER,
1134 i_request_id IN NUMBER,
1135 i_prog_id IN NUMBER,
1136 i_prog_appl_id IN NUMBER,
1137 o_err_num OUT NOCOPY NUMBER,
1138 o_err_msg OUT NOCOPY VARCHAR2
1139 )
1140 IS
1141
1142 l_stmt_num NUMBER := 0;
1143
1144 l_sql_stmt VARCHAR2(8000);
1145 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
1146 l_layer cst_wip_layers%ROWTYPE;
1147
1148 l_txn_qty_remaining NUMBER;
1149 l_consumed_qty NUMBER;
1150
1151 /* Bug 2186966 */
1152 l_exp_item NUMBER;
1153
1154 l_lot_size NUMBER := 1; /* Added as part of LBM */
1155 l_include_comp_yield NUMBER;
1156
1157 /* Changed as a part of LBM project Bug #3926918
1158 Added decode to WRO.qpa to get proportioned qpa in
1159 case of Lot Based Materials
1160 Divide the value of quantity per assembly by component
1161 yield factor if Include Component Yield Flag is checked
1162 in WIP Parameters.*/
1163
1164 /* Begin Bug 4246122 */
1165 l_use_val_cost_type NUMBER;
1166 l_wip_entity_type NUMBER;
1167 l_comp_cost_source NUMBER;
1168 l_c_cost_type_id NUMBER;
1169 l_prior_completed_qty NUMBER;
1170 l_wro_count NUMBER;
1171 /* End Bug 4246122 */
1172
1173 cursor wro_cursor is
1174 select WRO.operation_seq_num,
1175 WRO.inventory_item_id,
1176 Decode(WRO.basis_type, 2, WRO.quantity_per_assembly/l_lot_size,
1177 WRO.quantity_per_assembly)/
1178 DECODE(l_include_comp_yield,
1179 1, nvl(WRO.component_yield_factor,1),
1180 1) quantity_per_assembly,
1181 WRO.relieved_matl_completion_qty,
1182 decode( nvl( CCS.prior_completion_quantity, 0 ), 0, 1,
1183 i_txn_qty / CCS.prior_completion_quantity ) component_ratio
1184 from wip_requirement_operations WRO,
1185 cst_comp_snapshot CCS
1186 where WRO.wip_entity_id = i_wip_entity_id and
1187 WRO.wip_supply_type not in (4,5,6) and
1188 WRO.quantity_per_assembly <> 0 and
1189 CCS.transaction_id = i_txn_id and
1190 CCS.wip_entity_id = WRO.wip_entity_id and
1191 CCS.operation_seq_num = WRO.operation_seq_num and
1192 CCS.new_operation_flag = 2
1193 /* Begin Bug 4246122*/
1194 UNION ALL
1195 select WRO.operation_seq_num,
1196 WRO.inventory_item_id,
1197 WRO.quantity_per_assembly,
1198 WRO.relieved_matl_completion_qty,
1199 decode( nvl( WRO.relieved_matl_completion_qty, 0 ), 0, 1,
1200 i_txn_qty /l_prior_completed_qty ) component_ratio
1201 from wip_requirement_operations WRO
1202 where WRO.wip_entity_id = i_wip_entity_id and
1203 WRO.wip_supply_type not in (4,5,6) and
1204 WRO.quantity_per_assembly <> 0 and
1205 NOT EXISTS ( select 'Exists'
1206 from wip_operations wo
1207 where wo.wip_entity_id = i_wip_entity_id and
1208 wo.organization_id = i_org_id) ;
1209 /* End Bug 4246122 */
1210
1211 BEGIN
1212
1213 -- normally i_txn_qty < 0 for assembly return
1214 ----------------------------------------------------
1215 -- Update temp_relieved_value to zero in all tables
1216 ----------------------------------------------------
1217
1218 l_stmt_num := 10;
1219
1220 CSTPLMWI.reset_temp_columns
1221 (
1222 i_wip_entity_id,
1223 o_err_num,
1224 o_err_msg
1225 );
1226 IF o_err_num <> 0 THEN
1227 RETURN;
1228 END IF;
1229
1230 /* Begin Addition for Bug 4246122 */
1231
1232 l_stmt_num := 15;
1233 select we.entity_type
1234 into l_wip_entity_type
1235 from wip_entities we
1236 where we.wip_entity_id = i_wip_entity_id and
1237 we.entity_type in (1,3,4); /* excludes Repetitive */
1238
1239
1240 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
1241
1242 l_stmt_num := 20;
1243 select
1244 wac.completion_cost_source,
1245 nvl( wac.cost_type_id, -1 )
1246 into
1247 l_comp_cost_source,
1248 l_c_cost_type_id
1249 from
1250 wip_accounting_classes wac,
1251 wip_discrete_jobs wdj
1252 where
1253 wdj.wip_entity_id = i_wip_entity_id and
1254 wdj.organization_id = i_org_id and
1255 wdj.class_code = wac.class_code and
1256 wdj.organization_id = wac.organization_id;
1257
1258 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
1259
1260 l_stmt_num := 23;
1261 select
1262 wac.completion_cost_source,
1263 nvl( wac.cost_type_id, -1 )
1264 into
1265 l_comp_cost_source,
1266 l_c_cost_type_id
1267 from
1268 wip_accounting_classes wac,
1269 wip_flow_schedules wfs
1270 where
1271 wfs.wip_entity_id = i_wip_entity_id and
1272 wfs.organization_id = i_org_id and
1273 wfs.class_code = wac.class_code and
1274 wfs.organization_id = wac.organization_id;
1275
1276 END IF;
1277
1278 /*-----------------------------------------------------
1279 -- If a non-std job has no bill or routing associated
1280 -- with it or if a std job has no bill or routing
1281 -- associated with it - these need to be treated
1282 -- specially.
1283 ----------------------------------------------------- */
1284
1285 IF l_wip_entity_type in (1,3) /* Discrete */ THEN
1286
1287 l_stmt_num := 25;
1288 SELECT
1289 decode( job_type,
1290 1, decode( bom_revision,
1291 NULL, decode(routing_revision,NULL,-1,1),
1292 1 ),
1293 3, decode( bom_reference_id,
1294 NULL, decode(routing_reference_id,NULL,-1,1),
1295 1 ),
1296 1 )
1297 into
1298 l_use_val_cost_type
1299 from
1300 WIP_DISCRETE_JOBS
1301 WHERE
1302 WIP_ENTITY_ID = i_wip_entity_id AND
1303 ORGANIZATION_ID = i_org_id;
1304
1305 ELSIF l_wip_entity_type = 4 /* Flow */ THEN
1306
1307 l_stmt_num := 30;
1308 SELECT
1309 decode( bom_revision,
1310 NULL, decode(routing_revision,NULL,-1,1),
1311 1 )
1312 into
1313 l_use_val_cost_type
1314 from
1315 wip_flow_schedules wfs
1316 WHERE
1317 wfs.WIP_ENTITY_ID = i_wip_entity_id AND
1318 wfs.ORGANIZATION_ID = i_org_id;
1319
1320 END IF;
1321
1322 /* Material Requirements can be added manually for a job */
1323 if (l_use_val_cost_type = -1) then
1324 /* Commented for Bug6734270.If there is a resource
1325 added manually then also the l_use_val_cost_type
1326 should be 1
1327 select count(*)
1328 into l_wro_count
1329 from wip_requirement_operations
1330 where wip_entity_id = i_wip_entity_id
1331 and organization_id = i_org_id
1332 and quantity_per_assembly <>0;
1333 */
1334 SELECT COUNT(1)
1335 INTO l_wro_count
1336 FROM dual
1337 WHERE EXISTS ( SELECT NULL
1338 FROM wip_requirement_operations wro
1339 WHERE wro.wip_entity_id = i_wip_entity_id
1340 AND wro.quantity_per_assembly <>0
1341 UNION ALL
1342 SELECT NULL
1343 FROM wip_operation_resources wor
1344 WHERE wor.wip_entity_id = i_wip_entity_id
1345 AND wor.usage_rate_or_amount <>0
1346 );
1347
1348 if (l_wro_count > 0) then
1349 l_use_val_cost_type := 1;
1350 end if;
1351 end if;
1352
1353 IF ( l_comp_cost_source = 1 and l_use_val_cost_type = -1) THEN
1354 l_stmt_num :=35;
1355 INSERT INTO mtl_cst_txn_cost_details
1356 (
1357 TRANSACTION_ID,
1358 ORGANIZATION_ID,
1359 INVENTORY_ITEM_ID,
1360 COST_ELEMENT_ID,
1361 LEVEL_TYPE,
1362 TRANSACTION_COST,
1363 NEW_AVERAGE_COST,
1364 PERCENTAGE_CHANGE,
1365 VALUE_CHANGE,
1366 LAST_UPDATE_DATE,
1367 LAST_UPDATED_BY,
1368 CREATION_DATE,
1369 CREATED_BY,
1370 LAST_UPDATE_LOGIN,
1371 REQUEST_ID,
1372 PROGRAM_APPLICATION_ID,
1373 PROGRAM_ID,
1374 PROGRAM_UPDATE_DATE
1375 )
1376 SELECT
1377 i_txn_id,
1378 i_org_id,
1379 i_inv_item_id,
1380 COST_ELEMENT_ID,
1381 LEVEL_TYPE,
1382 ITEM_COST,
1383 NULL,
1384 NULL,
1385 NULL,
1386 SYSDATE,
1387 i_user_id,
1388 SYSDATE,
1389 i_user_id,
1390 i_login_id,
1391 i_request_id,
1392 i_prog_appl_id,
1393 i_prog_id,
1394 SYSDATE
1395 FROM
1396 CST_LAYER_COST_DETAILS
1397 WHERE LAYER_ID = i_layer_id
1398 AND NOT ( COST_ELEMENT_ID = 2 AND
1399 LEVEL_TYPE = 1 );
1400
1401 ELSE
1402 l_prior_completed_qty :=i_txn_qty;
1403 l_stmt_num :=40;
1404 select decode( nvl(sum(mmt.primary_quantity),0),0,i_txn_qty,sum(mmt.primary_quantity))
1405 into l_prior_completed_qty
1406 from mtl_material_transactions mmt
1407 where mmt.transaction_source_type_id=5
1408 and mmt.transaction_action_id in (31,32)
1409 and mmt.transaction_source_id = i_wip_entity_id
1410 and mmt.organization_id = i_org_id
1411 and mmt.costed_flag is NULL;
1412 /*End of Addition for Bug 4246122 */
1413
1414 /*---------------------------------------------
1415 Get the lot size of the job
1416 (Form validation takes care lot size is not 0)
1417 Added for Lot Based Materials project
1418 ----------------------------------------------*/
1419
1420 SELECT wdj.start_quantity
1421 INTO l_lot_size
1422 FROM wip_discrete_jobs wdj
1423 WHERE wdj.wip_entity_id = i_wip_entity_id
1424 AND wdj.organization_id = i_org_id;
1425
1426 /*----------------------------------------------
1427 Get the value of Include Component yield flag,
1428 which will determine whether to include or not
1429 component yield factor in quantity per assembly
1430 ------------------------------------------------*/
1431 SELECT nvl(include_component_yield, 1)
1432 INTO l_include_comp_yield
1433 FROM wip_parameters
1434 WHERE organization_id = i_org_id;
1435
1436 ---------------------------------------------
1437 -- Replenish component material quantities
1438 ---------------------------------------------
1439
1440 FOR wro_rec IN wro_cursor LOOP
1441
1442 /* Get the expense flag for the item */
1443 --------------------------------------------------------
1444 -- Get whether the Component is Asset/Expense
1445 --------------------------------------------------------
1446
1447 SELECT decode(INVENTORY_ASSET_FLAG,'Y',0,1)
1448 INTO l_exp_item
1449 FROM MTL_SYSTEM_ITEMS
1450 WHERE INVENTORY_ITEM_ID = wro_rec.inventory_item_id
1451 AND ORGANIZATION_ID = i_org_id;
1452
1453
1454 /* If item is not an expense item, create-consume layers */
1455 IF ( l_exp_item <> 1 ) THEN
1456
1457 CSTPLMWI.init_wip_layers
1458 (
1459 i_wip_entity_id,
1460 wro_rec.operation_seq_num,
1461 wro_rec.inventory_item_id,
1462 i_org_id,
1463 i_txn_id,
1464 i_layer_id,
1465 i_user_id,
1466 i_login_id,
1467 i_request_id,
1468 i_prog_id,
1469 i_prog_appl_id,
1470 o_err_num,
1471 o_err_msg
1472 );
1473 IF o_err_num <> 0 THEN
1474 RETURN;
1475 END IF;
1476
1477
1478 -- assembly completions consume WIP layer(s) in reverse order
1479 l_stmt_num := 50;
1480 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
1481 (
1482 ' sign( CWL.relieved_matl_comp_qty ) = ' ||
1483 ' sign( :quantity_per_assembly ) ',
1484 i_cost_method_id,
1485 CSTPLMWI.REVERSE
1486 );
1487
1488 l_stmt_num := 60;
1489 open l_layer_cursor
1490 for l_sql_stmt
1491 using i_wip_entity_id,
1492 wro_rec.operation_seq_num,
1493 wro_rec.inventory_item_id,
1494 wro_rec.quantity_per_assembly;
1495
1496
1497 -- reduce the relieved_matl_completion_qty
1498 -- by percentage using cst_comp_snapshot
1499 l_txn_qty_remaining := nvl(wro_rec.relieved_matl_completion_qty, 0) *
1500 nvl(wro_rec.component_ratio, 0);
1501
1502 LOOP
1503 exit when l_txn_qty_remaining = 0;
1504
1505 l_stmt_num := 70;
1506 fetch l_layer_cursor into l_layer;
1507
1508 l_stmt_num := 80;
1509 IF l_layer_cursor%NOTFOUND THEN
1510 l_layer := CSTPLMWI.get_last_layer
1511 (
1512 i_wip_entity_id,
1513 wro_rec.operation_seq_num,
1514 wro_rec.inventory_item_id,
1515 o_err_num,
1516 o_err_msg
1517 );
1518 l_consumed_qty := l_txn_qty_remaining;
1519
1520 ELSE
1521 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
1522 greatest( sign( wro_rec.quantity_per_assembly ) *
1523 -( l_layer.relieved_matl_comp_qty ),
1524 sign( wro_rec.quantity_per_assembly ) *
1525 l_txn_qty_remaining );
1526 END IF;
1527
1528 l_stmt_num := 90;
1529 update cst_wip_layers CWL
1530 set
1531 relieved_matl_comp_qty = relieved_matl_comp_qty + l_consumed_qty,
1532 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
1533 where
1534 wip_layer_id = l_layer.wip_layer_id and
1535 inv_layer_id = l_layer.inv_layer_id;
1536
1537 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
1538
1539 END LOOP; -- l_layer_cursor
1540
1541 l_stmt_num := 100;
1542 close l_layer_cursor;
1543
1544 -- Don't close wro_cursor here - Bug 2186966
1545 -- END LOOP; -- wro_cursor
1546
1547
1548
1549
1550
1551 -- update WROCD
1552 l_stmt_num := 110;
1553 update wip_req_operation_cost_details WROCD
1554 set
1555 (
1556 WROCD.relieved_matl_completion_value,
1557 WROCD.temp_relieved_value
1558 )
1559 =
1560 (
1561 select
1562 NVL( WROCD.relieved_matl_completion_value, 0 ) +
1563 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
1564 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
1565 from
1566 cst_wip_layers CWL,
1567 cst_wip_layer_cost_details CWLCD
1568 where
1569 CWL.wip_entity_id = WROCD.wip_entity_id and
1570 CWL.operation_seq_num = WROCD.operation_seq_num and
1571 CWL.inventory_item_id = WROCD.inventory_item_id and
1572 CWL.temp_relieved_qty <> 0 and
1573 CWLCD.wip_layer_id = CWL.wip_layer_id and
1574 CWLCD.inv_layer_id = CWL.inv_layer_id and
1575 CWLCD.cost_element_id = WROCD.cost_element_id and
1576 CWLCD.level_type in (1, 2)
1577 )
1578 where
1579 (
1580 WROCD.wip_entity_id,
1581 WROCD.operation_seq_num,
1582 WROCD.inventory_item_id
1583 )
1584 IN
1585 (
1586 select wip_entity_id,
1587 operation_seq_num,
1588 inventory_item_id
1589 from wip_requirement_operations WRO
1590 where
1591 WRO.wip_entity_id = i_wip_entity_id and
1592 /* Restrict only to the current Item */
1593 WRO.operation_seq_num = wro_rec.operation_seq_num and
1594 WRO.inventory_item_id = wro_rec.inventory_item_id and
1595 --
1596 -- exclude bulk, supplier, phantom
1597 --
1598 WRO.wip_supply_type not in (4,5,6) and
1599 WRO.quantity_per_assembly <> 0
1600 );
1601
1602
1603
1604 -- update WRO
1605 update wip_requirement_operations WRO
1606 set relieved_matl_completion_qty
1607 =
1608 (
1609 select
1610 NVL( WRO.relieved_matl_completion_qty, 0 ) +
1611 sum( CWL.temp_relieved_qty )
1612 from
1613 cst_wip_layers CWL
1614 where
1615 CWL.wip_entity_id = WRO.wip_entity_id and
1616 CWL.operation_seq_num = WRO.operation_seq_num and
1617 CWL.inventory_item_id = WRO.inventory_item_id and
1618 CWL.temp_relieved_qty <> 0
1619 )
1620 where
1621 WRO.wip_entity_id = i_wip_entity_id and
1622 /* Only for Current Item */
1623 WRO.operation_seq_num = wro_rec.operation_seq_num and
1624 WRO.inventory_item_id = wro_rec.inventory_item_id and
1625 --
1626 -- exclude bulk, supplier, phantom
1627 --
1628 WRO.wip_supply_type not in (4,5,6) and
1629 WRO.quantity_per_assembly <> 0;
1630
1631 ELSE
1632 -- If Item is an Expense Item
1633 -- Just Insert into WROCD if not already there and
1634 -- update relieved_matl_completion_qty
1635
1636 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
1637 (
1638 WIP_ENTITY_ID,
1639 OPERATION_SEQ_NUM,
1640 ORGANIZATION_ID,
1641 INVENTORY_ITEM_ID,
1642 COST_ELEMENT_ID,
1643 APPLIED_MATL_VALUE,
1644 RELIEVED_MATL_COMPLETION_VALUE,
1645 RELIEVED_MATL_SCRAP_VALUE,
1646 LAST_UPDATED_BY,
1647 LAST_UPDATE_DATE,
1648 CREATION_DATE,
1649 CREATED_BY,
1650 LAST_UPDATE_LOGIN,
1651 REQUEST_ID,
1652 PROGRAM_APPLICATION_ID,
1653 PROGRAM_ID,
1654 PROGRAM_UPDATE_DATE
1655 )
1656 SELECT
1657 i_wip_entity_id, -- WIP_ENTITY_ID,
1658 wro_rec.operation_seq_num, -- OPERATION_SEQ_NUM,
1659 i_org_id, -- ORGANIZATION_ID,
1660 wro_rec.inventory_item_id, -- INVENTORY_ITEM_ID,
1661 CCE.cost_element_id, -- COST_ELEMENT_ID,
1662 0, -- APPLIED_MATL_VALUE,
1663 0, -- RELIEVED_MATL_COMPLETION_VALUE,
1664 0, -- RELIEVED_MATL_SCRAP_VALUE,
1665 i_user_id, -- LAST_UPDATED_BY,
1666 sysdate, -- LAST_UPDATE_DATE,
1667 sysdate, -- CREATION_DATE,
1668 i_user_id, -- CREATED_BY,
1669 i_login_id, -- LAST_UPDATE_LOGIN,
1670 i_request_id, -- REQUEST_ID,
1671 i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
1672 i_prog_id, -- PROGRAM_ID,
1673 sysdate -- PROGRAM_UPDATE_DATE
1674 from
1675 cst_cost_elements CCE
1676 where
1677 NOT EXISTS
1678 (
1679 SELECT 'X'
1680 FROM WIP_REQ_OPERATION_COST_DETAILS WROCD2
1681 WHERE
1682 WROCD2.WIP_ENTITY_ID = i_wip_entity_id AND
1683 WROCD2.OPERATION_SEQ_NUM = wro_rec.operation_seq_num AND
1684 WROCD2.INVENTORY_ITEM_ID = wro_rec.inventory_item_id AND
1685 WROCD2.COST_ELEMENT_ID = CCE.cost_element_id
1686 ) AND
1687 EXISTS
1688 (
1689 select 'x'
1690 from wip_requirement_operations WRO
1691 where WRO.wip_entity_id = i_wip_entity_id and
1692 WRO.operation_seq_num = wro_rec.operation_seq_num and
1693 WRO.inventory_item_id = wro_rec.inventory_item_id and
1694 WRO.wip_supply_type not in (4, 5, 6)
1695 )
1696 group by
1697 CCE.cost_element_id;
1698
1699 /* Changed for LBM project. Added decode to qpa for Lot Based Materials */
1700
1701 UPDATE wip_requirement_operations w1
1702 SET
1703 relieved_matl_completion_qty =
1704 (SELECT
1705 nvl(w1.relieved_matl_completion_qty,0) +
1706 i_txn_qty*(DECODE(w2.basis_type, 2, w2.quantity_per_assembly/l_lot_size,
1707 w2.quantity_per_assembly)/
1708 DECODE(l_include_comp_yield,
1709 1, nvl(w2.component_yield_factor,1),
1710 1))
1711 FROM
1712 wip_requirement_operations w2
1713 WHERE
1714 w1.wip_entity_id = w2.wip_entity_id AND
1715 w1.organization_id = w2.organization_id AND
1716 w1.inventory_item_id = w2.inventory_item_id AND
1717 w1.operation_seq_num = w2.operation_seq_num )
1718 WHERE
1719 --
1720 -- Exclude bulk, supplier, phantom
1721 --
1722 w1.wip_supply_type not in (4,5,6) AND
1723 w1.wip_entity_id = i_wip_entity_id AND
1724 w1.organization_id = i_org_id AND
1725 w1.inventory_item_id = wro_rec.inventory_item_id AND
1726 w1.operation_seq_num = wro_rec.operation_seq_num AND
1727 w1.quantity_per_assembly <> 0;
1728
1729
1730 END IF; -- End IF Not Expense Item
1731
1732
1733 END LOOP; -- wro_cursor
1734
1735
1736
1737 --------------------------------------------------------------
1738 -- BEGIN Dual maintenance section with CSTPACCB.pls
1739 --------------------------------------------------------------
1740
1741 l_stmt_num := 120;
1742
1743 UPDATE wip_operation_resources w1
1744 SET
1745 (relieved_res_completion_units,
1746 temp_relieved_value,
1747 relieved_res_completion_value) =
1748 (SELECT
1749 --
1750 -- relieved_res_completion_units
1751 --
1752 nvl(w1.relieved_res_completion_units,0)+
1753 decode(SIGN(w2.relieved_res_completion_value),1,
1754 nvl(w2.relieved_res_completion_units,0)*
1755 decode(abs(i_txn_qty),
1756 prior_completion_quantity,-1,
1757 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1758 prior_completion_quantity)),
1759 0),
1760 --
1761 -- temp_relieved_value
1762 --
1763 decode(SIGN(w2.relieved_res_completion_value),1,
1764 nvl(W2.relieved_res_completion_value,0)*
1765 decode(abs(i_txn_qty),
1766 prior_completion_quantity,-1,
1767 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1768 prior_completion_quantity)),
1769 0),
1770 ---
1771 --- relieved_res_completion_value
1772 ---
1773 nvl(w1.relieved_res_completion_value,0)+
1774 decode(SIGN(w2.relieved_res_completion_value),1,
1775 nvl(w2.relieved_res_completion_value,0)*
1776 decode(abs(i_txn_qty),
1777 prior_completion_quantity,-1,
1778 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1779 prior_completion_quantity)),
1780 0)
1781 FROM
1782 wip_operation_resources w2,
1783 bom_resources BR,
1784 cst_comp_snapshot cocd
1785 WHERE
1786 w2.wip_entity_id = w1.wip_entity_id AND
1787 w2.organization_id = w1.organization_id AND
1788 w2.operation_seq_num = w1.operation_seq_num AND
1789 w2.resource_seq_num = w1.resource_seq_num AND
1790 w2.basis_type = w1.basis_type AND /* Added for bug 5247584 */
1791 BR.resource_id = w2.resource_id AND
1792 w2.wip_entity_id = cocd.wip_entity_id AND
1793 w2.operation_seq_num = cocd.operation_seq_num AND
1794 cocd.new_operation_flag = 2 AND
1795 cocd.transaction_id = i_txn_id)
1796 WHERE
1797 w1.wip_entity_id = i_wip_entity_id AND
1798 w1.organization_id = i_org_id;
1799
1800
1801
1802
1803 l_stmt_num := 130;
1804
1805 UPDATE wip_operation_overheads w1
1806 SET
1807 (relieved_ovhd_completion_units,
1808 temp_relieved_value,
1809 relieved_ovhd_completion_value) =
1810 (SELECT
1811 ---
1812 --- relieved_ovhd_completion_units
1813 ---
1814 nvl(w1.relieved_ovhd_completion_units,0)+
1815 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1816 nvl(W2.relieved_ovhd_completion_units,0)*
1817 decode(abs(i_txn_qty),
1818 prior_completion_quantity,-1,
1819 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1820 prior_completion_quantity)),
1821 0),
1822 ---
1823 --- temp_relieved_value
1824 ---
1825 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1826 nvl(w2.relieved_ovhd_completion_value,0)*
1827 decode(abs(i_txn_qty),
1828 prior_completion_quantity,-1,
1829 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1830 prior_completion_quantity)),
1831 0),
1832
1833 ---
1834 --- relieved_ovhd_completion_value
1835 ---
1836 nvl(w1.relieved_ovhd_completion_value,0)+
1837 decode(SIGN(w2.relieved_ovhd_completion_value),1,
1838 nvl(w2.relieved_ovhd_completion_value,0)*
1839 decode(abs(i_txn_qty),
1840 prior_completion_quantity,-1,
1841 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
1842 prior_completion_quantity)),
1843 0)
1844
1845 FROM
1846 wip_operation_overheads w2,
1847 cst_comp_snapshot cocd
1848 WHERE
1849 w2.wip_entity_id = w1.wip_entity_id AND
1850 w2.organization_id = w1.organization_id AND
1851 w2.operation_seq_num = w1.operation_seq_num AND
1852 w2.resource_seq_num = w1.resource_seq_num AND
1853 w2.overhead_id = w1.overhead_id AND
1854 w2.basis_type = w1.basis_type AND /* Added for bug 5247584 */
1855 w2.wip_entity_id = cocd.wip_entity_id AND
1856 w2.operation_seq_num = cocd.operation_seq_num AND
1857 cocd.new_operation_flag = 2 AND
1858 cocd.transaction_id = i_txn_id)
1859 WHERE
1860 w1.wip_entity_id = i_wip_entity_id AND
1861 w1.organization_id = i_org_id;
1862
1863
1864
1865
1866 l_stmt_num := 140;
1867
1868 INSERT INTO mtl_cst_txn_cost_details
1869 (
1870 TRANSACTION_ID,
1871 ORGANIZATION_ID,
1872 INVENTORY_ITEM_ID,
1873 COST_ELEMENT_ID,
1874 LEVEL_TYPE,
1875 TRANSACTION_COST,
1876 NEW_AVERAGE_COST,
1877 PERCENTAGE_CHANGE,
1878 VALUE_CHANGE,
1879 LAST_UPDATE_DATE,
1880 LAST_UPDATED_BY,
1881 CREATION_DATE,
1882 CREATED_BY,
1883 LAST_UPDATE_LOGIN,
1884 REQUEST_ID,
1885 PROGRAM_APPLICATION_ID,
1886 PROGRAM_ID,
1887 PROGRAM_UPDATE_DATE
1888 )
1889 SELECT
1890 i_txn_id,
1891 i_org_id,
1892 i_inv_item_id,
1893 wrocd.cost_element_id,
1894 2,
1895 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
1896 NULL,
1897 NULL,
1898 NULL,
1899 SYSDATE,
1900 i_user_id,
1901 SYSDATE,
1902 i_user_id,
1903 i_login_id,
1904 i_request_id,
1905 i_prog_appl_id,
1906 i_prog_id,
1907 SYSDATE
1908 FROM
1909 WIP_REQ_OPERATION_COST_DETAILS wrocd
1910 where
1911 WIP_ENTITY_ID = i_wip_entity_id AND
1912 ORGANIZATION_ID = i_org_id
1913 GROUP BY
1914 wrocd.cost_element_id
1915 HAVING
1916 sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
1917
1918 l_stmt_num := 150;
1919
1920 INSERT INTO mtl_cst_txn_cost_details
1921 (
1922 TRANSACTION_ID,
1923 ORGANIZATION_ID,
1924 INVENTORY_ITEM_ID,
1925 COST_ELEMENT_ID,
1926 LEVEL_TYPE,
1927 TRANSACTION_COST,
1928 NEW_AVERAGE_COST,
1929 PERCENTAGE_CHANGE,
1930 VALUE_CHANGE,
1931 LAST_UPDATE_DATE,
1932 LAST_UPDATED_BY,
1933 CREATION_DATE,
1934 CREATED_BY,
1935 LAST_UPDATE_LOGIN,
1936 REQUEST_ID,
1937 PROGRAM_APPLICATION_ID,
1938 PROGRAM_ID,
1939 PROGRAM_UPDATE_DATE
1940 )
1941 SELECT
1942 i_txn_id,
1943 i_org_id,
1944 i_inv_item_id,
1945 br.cost_element_id,
1946 1,
1947 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
1948 NULL,
1949 NULL,
1950 NULL,
1951 SYSDATE,
1952 i_user_id,
1953 SYSDATE,
1954 i_user_id,
1955 i_login_id,
1956 i_request_id,
1957 i_prog_appl_id,
1958 i_prog_id,
1959 SYSDATE
1960 FROM
1961 BOM_RESOURCES BR,
1962 WIP_OPERATION_RESOURCES WOR
1963 WHERE
1964 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
1965 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
1966 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
1967 WOR.ORGANIZATION_ID = i_org_id
1968 GROUP BY
1969 BR.COST_ELEMENT_ID
1970 HAVING
1971 sum(nvl(wor.temp_relieved_value,0)) <> 0;
1972
1973 l_stmt_num := 160;
1974
1975 INSERT INTO mtl_cst_txn_cost_details
1976 (
1977 TRANSACTION_ID,
1978 ORGANIZATION_ID,
1979 INVENTORY_ITEM_ID,
1980 COST_ELEMENT_ID,
1981 LEVEL_TYPE,
1982 TRANSACTION_COST,
1983 NEW_AVERAGE_COST,
1984 PERCENTAGE_CHANGE,
1985 VALUE_CHANGE,
1986 LAST_UPDATE_DATE,
1987 LAST_UPDATED_BY,
1988 CREATION_DATE,
1989 CREATED_BY,
1990 LAST_UPDATE_LOGIN,
1991 REQUEST_ID,
1992 PROGRAM_APPLICATION_ID,
1993 PROGRAM_ID,
1994 PROGRAM_UPDATE_DATE
1995 )
1996 SELECT
1997 i_txn_id,
1998 i_org_id,
1999 i_inv_item_id,
2000 5,
2001 1,
2002 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2003 NULL,
2004 NULL,
2005 NULL,
2006 SYSDATE,
2007 i_user_id,
2008 SYSDATE,
2009 i_user_id,
2010 i_login_id,
2011 i_request_id,
2012 i_prog_appl_id,
2013 i_prog_id,
2014 SYSDATE
2015 FROM
2016 WIP_OPERATION_OVERHEADS
2017 WHERE
2018 WIP_ENTITY_ID = i_wip_entity_id AND
2019 ORGANIZATION_ID = i_org_id
2020 HAVING
2021 SUM(nvl(temp_relieved_value,0)) <> 0;
2022
2023 --------------------------------------------------------------
2024 -- BEGIN Dual maintenance section with CSTPACCB.pls
2025 --------------------------------------------------------------
2026
2027 END IF;
2028
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 o_err_num := SQLCODE;
2032 o_err_msg := 'CSTPLCAC.assembly_return():' ||
2033 to_char(l_stmt_num) || ':' ||
2034 substr(SQLERRM,1,150);
2035
2036 END assembly_return;
2037
2038
2039
2040
2041
2042
2043
2044 END CSTPLCAC;