[Home] [Help]
PACKAGE BODY: APPS.CSTPLCAS
Source
1 PACKAGE BODY CSTPLCAS AS
2 /* $Header: CSTLCASB.pls 120.1 2006/02/15 15:53:32 nnayak noship $ */
3
4
5
6 PROCEDURE scrap (
7 i_cost_method_id IN NUMBER,
8 i_txn_id IN NUMBER,
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_op_seq_num IN NUMBER,
15 i_user_id IN NUMBER,
16 i_login_id IN NUMBER,
17 i_request_id IN NUMBER,
18 i_prog_id IN NUMBER,
19 i_prog_appl_id IN NUMBER,
20 o_err_num OUT NOCOPY NUMBER,
21 o_err_msg OUT NOCOPY VARCHAR2
22 )
23 IS
24
25 l_stmt_num NUMBER := 0;
26
27 l_sql_stmt VARCHAR2(8000);
28 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
29 l_layer cst_wip_layers%ROWTYPE;
30
31 l_txn_qty_remaining NUMBER;
32 l_consumed_qty NUMBER;
33 l_lot_size NUMBER := 1;
34 l_include_comp_yield NUMBER;
35
36 /* Changed as a part of LBM project Bug #3926918
37 Removed select * and added required columns and
38 added decode to qpa to get proportioned qpa for
39 Lot Based Materials.
40 Divide the value of quantity per assembly by component
41 yield factor if Include Component Yield Flag is checked
42 in WIP Parameters.*/
43
44 cursor wro_cursor( i_wip_entity_id NUMBER, i_op_seq_num NUMBER ) is
45 select operation_seq_num,
46 inventory_item_id,
47 Decode(basis_type, 2, quantity_per_assembly/l_lot_size,
48 quantity_per_assembly)/
49 DECODE(l_include_comp_yield,
50 1, nvl(component_yield_factor,1),
51 1) quantity_per_assembly
52 from wip_requirement_operations WRO
53 where WRO.wip_entity_id = i_wip_entity_id and
54 WRO.operation_seq_num <= i_op_seq_num and
55 WRO.wip_supply_type not in (4,5,6) and
56 WRO.quantity_per_assembly <> 0;
57
58 BEGIN
59
60 /* normally i_txn_qty > 0 for scrap */
61
62 /*----------------------------------------------------
63 Update temp_relieved_value to zero in all tables
64 ----------------------------------------------------*/
65
66 l_stmt_num := 10;
67
68 CSTPLMWI.reset_temp_columns
69 (
70 i_wip_entity_id,
71 o_err_num,
72 o_err_msg
73 );
74 IF o_err_num <> 0 THEN
75 RETURN;
76 END IF;
77
78
79 /*---------------------------------------------
80 Get the lot size of the job
81 (Form validation takes care lot size is not 0)
82 Added for Lot Based Materials project
83 ----------------------------------------------*/
84 L_STMT_NUM := 20;
85 SELECT wdj.start_quantity
86 INTO l_lot_size
87 FROM wip_discrete_jobs wdj
88 WHERE wdj.wip_entity_id = i_wip_entity_id
89 AND wdj.organization_id = i_org_id;
90
91 /*----------------------------------------------
92 Get the value of Include Component yield flag,
93 which will determine whether to include or not
94 component yield factor in quantity per assembly
95 ------------------------------------------------*/
96 l_stmt_num := 25;
97 SELECT nvl(include_component_yield, 1)
98 INTO l_include_comp_yield
99 FROM wip_parameters
100 WHERE organization_id = i_org_id;
101
102 /*--------------------------------------------
103 Consume material component quantities
104 --------------------------------------------*/
105
106 l_stmt_num := 60;
107 FOR wro_rec IN wro_cursor( i_wip_entity_id, i_op_seq_num ) LOOP
108
109 l_stmt_num := 70;
110 CSTPLMWI.init_wip_layers
111 (
112 i_wip_entity_id,
113 wro_rec.operation_seq_num,
114 wro_rec.inventory_item_id,
115 i_org_id,
116 i_txn_id,
117 i_layer_id,
118 i_user_id,
119 i_login_id,
120 i_request_id,
121 i_prog_id,
122 i_prog_appl_id,
123 o_err_num,
124 o_err_msg
125 );
126 IF o_err_num <> 0 THEN
127 RETURN;
128 END IF;
129
130
131
132 /* consume WIP layer(s)
133 scraps consume WIP in normal order */
134 l_stmt_num := 80;
135 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
136 (
137 ' sign( CWL.applied_matl_qty - ' ||
138 ' CWL.relieved_matl_comp_qty - ' ||
139 ' CWL.relieved_matl_scrap_qty - ' ||
140 ' CWL.relieved_matl_final_comp_qty ) = ' ||
141 ' sign( :quantity_per_assembly ) ',
142 i_cost_method_id,
143 CSTPLMWI.NORMAL
144 );
145
146 l_stmt_num := 90;
147 open l_layer_cursor
148 for l_sql_stmt
149 using i_wip_entity_id,
150 wro_rec.operation_seq_num,
151 wro_rec.inventory_item_id,
152 wro_rec.quantity_per_assembly;
153
154 l_txn_qty_remaining := i_txn_qty * wro_rec.quantity_per_assembly;
155
156 LOOP
157 exit when l_txn_qty_remaining = 0;
158
159 l_stmt_num := 100;
160 fetch l_layer_cursor into l_layer;
161
162 l_stmt_num := 110;
163 IF l_layer_cursor%NOTFOUND THEN
164
165 l_layer := CSTPLMWI.get_last_layer
166 (
167 i_wip_entity_id,
168 wro_rec.operation_seq_num,
169 wro_rec.inventory_item_id,
170 o_err_num,
171 o_err_msg
172 );
173 IF o_err_num <> 0 THEN
174 RETURN;
175 END IF;
176
177 l_consumed_qty := l_txn_qty_remaining;
178
179 ELSE
180 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
181 least( sign( wro_rec.quantity_per_assembly ) *
182 ( l_layer.applied_matl_qty -
183 l_layer.relieved_matl_comp_qty -
184 l_layer.relieved_matl_scrap_qty -
185 l_layer.relieved_matl_final_comp_qty ),
186 sign( wro_rec.quantity_per_assembly ) *
187 l_txn_qty_remaining );
188 END IF;
189
190 l_stmt_num := 120;
191 update cst_wip_layers CWL
192 set
193 relieved_matl_scrap_qty = relieved_matl_scrap_qty + l_consumed_qty,
194 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
195 where
196 wip_layer_id = l_layer.wip_layer_id and
197 inv_layer_id = l_layer.inv_layer_id;
198
199 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
200
201 END LOOP; /* l_layer_cursor */
202
203 l_stmt_num := 130;
204 close l_layer_cursor;
205
206 END LOOP; /* wro_cursor */
207
208
209
210 /* update WROCD */
211 l_stmt_num := 140;
212 update wip_req_operation_cost_details WROCD
213 set
214 (
215 WROCD.relieved_matl_scrap_value,
216 WROCD.temp_relieved_value
217 )
218 =
219 (
220 select
221 NVL( WROCD.relieved_matl_scrap_value, 0 ) +
222 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
223 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
224 from
225 cst_wip_layers CWL,
226 cst_wip_layer_cost_details CWLCD
227 where
228 CWL.wip_entity_id = WROCD.wip_entity_id and
229 CWL.operation_seq_num = WROCD.operation_seq_num and
230 CWL.inventory_item_id = WROCD.inventory_item_id and
231 CWL.temp_relieved_qty <> 0 and
232 CWLCD.wip_layer_id = CWL.wip_layer_id and
233 CWLCD.inv_layer_id = CWL.inv_layer_id and
234 CWLCD.cost_element_id = WROCD.cost_element_id and
235 CWLCD.level_type in (1, 2)
236 )
237 where
238 (
239 WROCD.wip_entity_id,
240 WROCD.operation_seq_num,
241 WROCD.inventory_item_id
242 )
243 IN
244 (
245 select wip_entity_id,
246 operation_seq_num,
247 inventory_item_id
248 from wip_requirement_operations WRO
249 where
250 WRO.wip_entity_id = i_wip_entity_id and
251 WRO.operation_seq_num <= i_op_seq_num and
252
253 /* exclude bulk, supplier, phantom */
254
255 WRO.wip_supply_type not in (4,5,6) and
256 WRO.quantity_per_assembly <> 0
257 );
258
259
260
261 /* update WRO */
262 update wip_requirement_operations WRO
263 set relieved_matl_scrap_quantity
264 =
265 (
266 select
267 NVL( WRO.relieved_matl_scrap_quantity, 0 ) +
268 sum( CWL.temp_relieved_qty )
269 from
270 cst_wip_layers CWL
271 where
272 CWL.wip_entity_id = WRO.wip_entity_id and
273 CWL.operation_seq_num = WRO.operation_seq_num and
274 CWL.inventory_item_id = WRO.inventory_item_id and
275 CWL.temp_relieved_qty <> 0
276 )
277 where
278 WRO.wip_entity_id = i_wip_entity_id and
279 WRO.operation_seq_num <= i_op_seq_num and
280
281 /* exclude bulk, supplier, phantom */
282
283 WRO.wip_supply_type not in (4,5,6) and
284 WRO.quantity_per_assembly <> 0;
285
286
287
288 /*--------------------------------------------------------------
289 BEGIN Dual maintenance section with CSTPACSB.pls
290 --------------------------------------------------------------*/
291
292 /* insert into wip_scrap_values for WROCD */
293
294 l_stmt_num := 150;
295
296 INSERT INTO WIP_SCRAP_VALUES
297 (
298 transaction_id,
299 level_type,
300 cost_element_id,
301 cost_element_value,
302 cost_update_id,
303 last_update_date,
304 last_updated_by,
305 created_by,
306 creation_date,
307 last_update_login,
308 request_id,
309 program_application_id,
310 program_id,
311 program_update_date
312 )
313 SELECT
314 i_txn_id, /* transaction_id, */
315 2, /* level_type, */
316 WROCD.cost_element_id, /* cost_element_id, */
317 sum( NVL( WROCD.temp_relieved_value, 0 ) ) / i_txn_qty,
318 /* cost_element_value, */
319 NULL, /* cost_update_id, */
320 sysdate, /* last_update_date, */
321 i_user_id, /* last_updated_by, */
322 i_user_id, /* created_by, */
323 sysdate, /* creation_date, */
324 i_login_id, /* last_update_login, */
325 i_request_id, /* request_id, */
326 i_prog_appl_id, /* program_application_id,*/
327 i_prog_id, /* program_id, */
328 sysdate /* program_update_date */
329 FROM
330 wip_req_operation_cost_details WROCD
331 where
332 wrocd.WIP_ENTITY_ID = i_wip_entity_id
333 GROUP BY
334 wrocd.COST_ELEMENT_ID
335 HAVING
336 sum( NVL( WROCD.temp_relieved_value, 0 ) ) <> 0;
337
338
339
340
341 l_stmt_num := 160;
342
343 UPDATE wip_operation_resources w1
344 SET
345 (relieved_res_scrap_units,
346 temp_relieved_value,
347 relieved_res_scrap_value) =
348 (SELECT
349 NVL(w1.relieved_res_scrap_units,0) +
350 decode(sign(applied_resource_units -
351 nvl(relieved_res_completion_units,0)-
352 nvl(relieved_res_final_comp_units,0)-
353 nvl(relieved_res_scrap_units,0)),
354 1,
355 (applied_resource_units -
356 nvl(relieved_res_completion_units,0)-
357 nvl(relieved_res_final_comp_units,0)-
358 nvl(relieved_res_scrap_units,0))*
359 /*
360 new to solve divided by zero and over relieved
361 when txn_qty/completed - prior_completion - prior_scrap
362 is greater than or equal to one, set it to one
363 ie. flush out 1*value remain in the job same as completion 8/28/98
364 */
365 decode(sign(i_txn_qty - (cocd.quantity_completed -
366 nvl(prior_completion_quantity,0) -
367 nvl(prior_scrap_quantity,0))),
368 -1,i_txn_qty/(cocd.quantity_completed -
369 nvl(prior_completion_quantity,0) -
370 nvl(prior_scrap_quantity,0)),
371 1),
372 0),
373 decode(sign(applied_resource_value -
374 nvl(relieved_res_completion_value,0)-
375 nvl(relieved_variance_value,0)-
376 nvl(relieved_res_scrap_value,0)),
377 1,
378 (applied_resource_value -
379 nvl(relieved_res_completion_value,0)-
380 nvl(relieved_variance_value,0)-
381 nvl(relieved_res_scrap_value,0))*
382
383 /* new to solve divided by zero and over relieved */
384
385 decode(sign(i_txn_qty - (cocd.quantity_completed -
386 nvl(prior_completion_quantity,0) -
387 nvl(prior_scrap_quantity,0))),
388 -1,i_txn_qty/(cocd.quantity_completed -
389 nvl(prior_completion_quantity,0) -
390 nvl(prior_scrap_quantity,0)),
391 1),
392 0),
393 nvl(w1.relieved_res_scrap_value,0) +
394 decode(sign(applied_resource_value -
395 nvl(relieved_res_completion_value,0)-
396 nvl(relieved_variance_value,0)-
397 nvl(relieved_res_scrap_value,0)),
398 1,
399 (applied_resource_value -
400 nvl(relieved_res_completion_value,0)-
401 nvl(relieved_variance_value,0)-
402 nvl(relieved_res_scrap_value,0))*
403
404 /* new to solve divided by zero and over relieved */
405
406 decode(sign(i_txn_qty - (cocd.quantity_completed -
407 nvl(prior_completion_quantity,0) -
408 nvl(prior_scrap_quantity,0))),
409 -1,i_txn_qty/(cocd.quantity_completed -
410 nvl(prior_completion_quantity,0) -
411 nvl(prior_scrap_quantity,0)),
412 1),
413 0)
414 FROM
415 wip_operation_resources w2,
416 cst_comp_snapshot cocd
417 WHERE
418 W1.WIP_ENTITY_ID = W2.WIP_ENTITY_ID AND
419 W1.OPERATION_SEQ_NUM = W2.OPERATION_SEQ_NUM AND
420 W1.RESOURCE_SEQ_NUM = W2.RESOURCE_SEQ_NUM AND
421 W1.ORGANIZATION_ID = W2.ORGANIZATION_ID AND
422 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
423 COCD.NEW_OPERATION_FLAG = 2 AND
424 COCD.TRANSACTION_ID = I_TXN_ID)
425 WHERE
426 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
427 W1.ORGANIZATION_ID = I_ORG_ID AND
428 w1.USAGE_RATE_OR_AMOUNT <> 0 AND
429 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
430
431
432 l_stmt_num := 170;
433
434 UPDATE wip_operation_overheads w1
435 SET
436 (relieved_ovhd_scrap_units,
437 temp_relieved_value,
438 relieved_ovhd_scrap_value) =
439 (SELECT
440 NVL(w1.relieved_ovhd_scrap_units,0) +
441 decode(sign(applied_ovhd_units -
442 nvl(relieved_ovhd_completion_units,0)-
443 nvl(relieved_ovhd_final_comp_units,0)-
444 nvl(relieved_ovhd_scrap_units,0)),
445 1,
446 (applied_ovhd_units -
447 nvl(relieved_ovhd_completion_units,0)-
448 nvl(relieved_ovhd_final_comp_units,0)-
449 nvl(relieved_ovhd_scrap_units,0))*
450
451 /* new to solve divided by zero and over relieved */
452
453 decode(sign(i_txn_qty - (cocd.quantity_completed -
454 nvl(prior_completion_quantity,0) -
455 nvl(prior_scrap_quantity,0))),
456 -1,i_txn_qty/(cocd.quantity_completed -
457 nvl(prior_completion_quantity,0) -
458 nvl(prior_scrap_quantity,0)),
459 1),
460 0),
461 decode(sign(applied_ovhd_value -
462 nvl(relieved_ovhd_completion_value,0)-
463 nvl(relieved_variance_value,0)-
464 nvl(relieved_ovhd_scrap_value,0)),
465 1,
466 (applied_ovhd_value -
467 nvl(relieved_ovhd_completion_value,0)-
468 nvl(relieved_variance_value,0)-
469 nvl(relieved_ovhd_scrap_value,0))*
470
471 /* new to solve divided by zero and over relieved */
472
473 decode(sign(i_txn_qty - (cocd.quantity_completed -
474 nvl(prior_completion_quantity,0) -
475 nvl(prior_scrap_quantity,0))),
476 -1,i_txn_qty/(cocd.quantity_completed -
477 nvl(prior_completion_quantity,0) -
478 nvl(prior_scrap_quantity,0)),
479 1),
480 0),
481 nvl(W1.relieved_ovhd_scrap_value,0) +
482 decode(sign(applied_ovhd_value -
483 nvl(relieved_ovhd_completion_value,0)-
484 nvl(relieved_variance_value,0)-
485 nvl(relieved_ovhd_scrap_value,0)),
486 1,
487 (applied_ovhd_value -
488 nvl(relieved_ovhd_completion_value,0)-
489 nvl(relieved_variance_value,0)-
490 nvl(relieved_ovhd_scrap_value,0))*
491
492 /* new to solve divided by zero and over relieved */
493
494 decode(sign(i_txn_qty - (cocd.quantity_completed -
495 nvl(prior_completion_quantity,0) -
496 nvl(prior_scrap_quantity,0))),
497 -1,i_txn_qty/(cocd.quantity_completed -
498 nvl(prior_completion_quantity,0) -
499 nvl(prior_scrap_quantity,0)),
500 1),
501 0)
502 FROM
503 wip_operation_overheads W2,
504 cst_comp_snapshot COCD
505 WHERE
506 w1.wip_entity_id = w2.wip_entity_id AND
507 w1.operation_seq_num = w2.operation_seq_num AND
508 w1.resource_seq_num = w2.resource_seq_num AND
509 w1.overhead_id = w2.overhead_id AND
510 w1.organization_id = w2.organization_id AND
511 w1.basis_type = w2.basis_type AND
512 w2.operation_seq_num = cocd.operation_seq_num AND
513 cocd.new_operation_flag = 2 AND
514 cocd.transaction_id = i_txn_id)
515 WHERE
516 w1.wip_entity_id = i_wip_entity_id AND
517 w1.organization_id = i_org_id AND
518 w1.operation_seq_num <= i_op_seq_num;
519
520
521
522
523 l_stmt_num := 180;
524
525 INSERT INTO WIP_SCRAP_VALUES
526 (
527 transaction_id,
528 level_type,
529 cost_element_id,
530 cost_element_value,
531 cost_update_id,
532 last_update_date,
533 last_updated_by,
534 created_by,
535 creation_date,
536 last_update_login,
537 request_id,
538 program_application_id,
539 program_id,
540 program_update_date
541 )
542 SELECT
543 i_txn_id, /* transaction_id, */
544 1, /* level_type, */
545 BR.cost_element_id, /* cost_element_id, */
546 sum( NVL( WOR.temp_relieved_value, 0 ) ) / i_txn_qty,
547 /* cost_element_value, */
548 NULL, /* cost_update_id, */
549 sysdate, /* last_update_date, */
550 i_user_id, /* last_updated_by, */
551 i_user_id, /* created_by, */
552 sysdate, /* creation_date, */
553 i_login_id, /* last_update_login, */
554 i_request_id, /* request_id, */
555 i_prog_appl_id, /* program_application_id, */
556 i_prog_id, /* program_id, */
557 sysdate /* program_update_date */
558 FROM
559 wip_operation_resources WOR,
560 bom_resources BR
561 WHERE
562 WOR.wip_entity_id = i_wip_entity_id AND
563 WOR.organization_id = i_org_id AND
564 BR.resource_id = WOR.resource_id AND
565 BR.organization_id = WOR.organization_id
566 group by
567 BR.cost_element_id
568 HAVING
569 SUM(nvl(temp_relieved_value,0)) <> 0;
570
571
572 l_stmt_num := 190;
573
574 INSERT INTO WIP_SCRAP_VALUES
575 (
576 transaction_id,
577 level_type,
578 cost_element_id,
579 cost_element_value,
580 cost_update_id,
581 last_update_date,
582 last_updated_by,
583 created_by,
584 creation_date,
585 last_update_login,
586 request_id,
587 program_application_id,
588 program_id,
589 program_update_date
590 )
591 SELECT
592 i_txn_id, /* transaction_id, */
593 1, /* level_type, */
594 5, /* cost_element_id, */
595 sum( NVL( WOO.temp_relieved_value, 0 ) ) / i_txn_qty,
596 /* cost_element_value, */
597 NULL, /* cost_update_id, */
598 sysdate, /* last_update_date, */
599 i_user_id, /* last_updated_by, */
600 i_user_id, /* created_by, */
601 sysdate, /* creation_date, */
602 i_login_id, /* last_update_login, */
603 i_request_id, /* request_id, */
604 i_prog_appl_id, /* program_application_id, */
605 i_prog_id, /* program_id, */
606 sysdate /* program_update_date */
607 FROM
608 wip_operation_overheads WOO
609 WHERE
610 WOO.wip_entity_id = i_wip_entity_id AND
611 WOO.organization_id = i_org_id
612 HAVING
613 SUM(nvl(temp_relieved_value,0)) <> 0;
614
615
616
617 /******************************************************
618 * Insert rows into mtl_cst_txn_cost_details *
619 ******************************************************/
620
621 l_stmt_num := 200;
622
623 INSERT INTO mtl_cst_txn_cost_details
624 (
625 TRANSACTION_ID,
626 ORGANIZATION_ID,
627 INVENTORY_ITEM_ID,
628 COST_ELEMENT_ID,
629 LEVEL_TYPE,
630 TRANSACTION_COST,
631 NEW_AVERAGE_COST,
632 PERCENTAGE_CHANGE,
633 VALUE_CHANGE,
634 LAST_UPDATE_DATE,
635 LAST_UPDATED_BY,
636 CREATION_DATE,
637 CREATED_BY,
638 LAST_UPDATE_LOGIN,
639 REQUEST_ID,
640 PROGRAM_APPLICATION_ID,
641 PROGRAM_ID,
642 PROGRAM_UPDATE_DATE
643 )
644 SELECT
645 i_txn_id,
646 i_org_id,
647 i_inv_item_id,
648 cost_element_id,
649 level_type,
650 cost_element_value,
651 NULL,
652 NULL,
653 NULL,
654 sysdate, /* LAST_UPDATE_DATE, */
655 i_user_id, /* LAST_UPDATED_BY, */
656 sysdate, /* CREATION_DATE, */
657 i_user_id, /* CREATED_BY, */
658 i_login_id, /* LAST_UPDATE_LOGIN, */
659 i_request_id, /* REQUEST_ID, */
660 i_prog_appl_id, /* PROGRAM_APPLICATION_ID,*/
661 i_prog_id, /* PROGRAM_ID, */
662 sysdate /* PROGRAM_UPDATE_DATE */
663 FROM
664 wip_scrap_values WSV
665 WHERE
666 WSV.transaction_id = i_txn_id AND
667 WSV.cost_update_id IS NULL;
668
669
670 /*------------------------------------------------------------
671 END Dual maintenance section with CSTPACSB.pls
672 ------------------------------------------------------------*/
673
674
675 EXCEPTION
676 WHEN OTHERS THEN
677 o_err_num := SQLCODE;
678 o_err_msg := 'CSTPLCAS.scrap():' ||
679 to_char(l_stmt_num) || ':' ||
680 substrb(SQLERRM,1,150);
681
682 END scrap;
683
684
685
686
687
688
689
690 PROCEDURE scrap_return (
691 i_cost_method_id IN NUMBER,
692 i_txn_id IN NUMBER,
693 i_layer_id IN NUMBER,
694 i_inv_item_id IN NUMBER,
695 i_org_id IN NUMBER,
696 i_wip_entity_id IN NUMBER,
697 i_txn_qty IN NUMBER,
698 i_op_seq_num IN NUMBER,
699 i_user_id IN NUMBER,
700 i_login_id IN NUMBER,
701 i_request_id IN NUMBER,
702 i_prog_id IN NUMBER,
703 i_prog_appl_id IN NUMBER,
704 o_err_num OUT NOCOPY NUMBER,
705 o_err_msg OUT NOCOPY VARCHAR2
706 )
707 IS
708
709
710 l_stmt_num NUMBER := 0;
711
712 l_sql_stmt VARCHAR2(8000);
713 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
714 l_layer cst_wip_layers%ROWTYPE;
715
716 l_txn_qty_remaining NUMBER;
717 l_consumed_qty NUMBER;
718 l_lot_size NUMBER := 1; /* Added as part of LBM */
719 l_include_comp_yield NUMBER;
720
721 /* Changed as a part of LBM project Bug #3926918
722 Added decode to WRO.qpa to get proportioned qpa in
723 case of Lot Based Materials
724 Divide the value of quantity per assembly by component
725 yield factor if Include Component Yield Flag is checked
726 in WIP Parameters. */
727
728 cursor wro_cursor is
729 select WRO.operation_seq_num,
730 WRO.inventory_item_id,
731 Decode(WRO.basis_type, 2, WRO.quantity_per_assembly/l_lot_size,
732 WRO.quantity_per_assembly)/
733 DECODE(l_include_comp_yield,
734 1, nvl(WRO.component_yield_factor,1),
735 1) quantity_per_assembly,
736 WRO.relieved_matl_scrap_quantity,
737 decode( nvl( CCS.prior_scrap_quantity, 0 ), 0, 1,
738 i_txn_qty / CCS.prior_scrap_quantity ) component_ratio
739 from wip_requirement_operations WRO,
740 cst_comp_snapshot CCS
741 where WRO.wip_entity_id = i_wip_entity_id and
742 WRO.operation_seq_num <= i_op_seq_num and
743 WRO.wip_supply_type not in (4,5,6) and
744 WRO.quantity_per_assembly <> 0 and
745 CCS.transaction_id = i_txn_id and
746 CCS.wip_entity_id = WRO.wip_entity_id and
747 CCS.operation_seq_num = WRO.operation_seq_num and
748 CCS.new_operation_flag = 2;
749
750 BEGIN
751
752 /* normally i_txn_qty < 0 for scrap return */
753
754 /*--------------------------------------------------
755 Update temp_relieved_value to zero in all tables
756 --------------------------------------------------*/
757
758 l_stmt_num := 10;
759
760 CSTPLMWI.reset_temp_columns
761 (
762 i_wip_entity_id,
763 o_err_num,
764 o_err_msg
765 );
766 IF o_err_num <> 0 THEN
767 RETURN;
768 END IF;
769
770
771 /*---------------------------------------------
772 Get the lot size of the job
773 (Form validation takes care lot size is not 0)
774 Added for Lot Based Materials project
775 ----------------------------------------------*/
776 l_stmt_num := 20;
777 SELECT wdj.start_quantity
778 INTO l_lot_size
779 FROM wip_discrete_jobs wdj
780 WHERE wdj.wip_entity_id = i_wip_entity_id
781 AND wdj.organization_id = i_org_id;
782
783 /*----------------------------------------------
784 Get the value of Include Component yield flag,
785 which will determine whether to include or not
786 component yield factor in quantity per assembly
787 ------------------------------------------------*/
788 l_stmt_num := 25;
789 SELECT nvl(include_component_yield, 1)
790 INTO l_include_comp_yield
791 FROM wip_parameters
792 WHERE organization_id = i_org_id;
793
794 /*-------------------------------------------
795 Replenish component material quantities
796 -------------------------------------------*/
797
798 l_stmt_num := 60;
799 FOR wro_rec IN wro_cursor LOOP
800
801 CSTPLMWI.init_wip_layers
802 (
803 i_wip_entity_id,
804 wro_rec.operation_seq_num,
805 wro_rec.inventory_item_id,
806 i_org_id,
807 i_txn_id,
808 i_layer_id,
809 i_user_id,
810 i_login_id,
811 i_request_id,
812 i_prog_id,
813 i_prog_appl_id,
814 o_err_num,
815 o_err_msg
816 );
817 IF o_err_num <> 0 THEN
818 RETURN;
819 END IF;
820
821
822
823 /* replenish WIP layer(s)
824 scrap return replenishes WIP in reverse order */
825 l_stmt_num := 70;
826 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
827 (
828 ' sign( CWL.relieved_matl_scrap_qty ) = ' ||
829 ' sign( :quantity_per_assembly ) ',
830 i_cost_method_id,
831 CSTPLMWI.REVERSE
832 );
833
834 l_stmt_num := 80;
835 open l_layer_cursor
836 for l_sql_stmt
837 using i_wip_entity_id,
838 wro_rec.operation_seq_num,
839 wro_rec.inventory_item_id,
840 wro_rec.quantity_per_assembly;
841
842
843 /* reduce the relieved_matl_scrap_quantity
844 by percentage using cst_comp_snapshot */
845 l_txn_qty_remaining := nvl(wro_rec.relieved_matl_scrap_quantity, 0) *
846 nvl(wro_rec.component_ratio, 0);
847
848
849 LOOP
850 exit when l_txn_qty_remaining = 0;
851
852 l_stmt_num := 90;
853 fetch l_layer_cursor into l_layer;
854
855 l_stmt_num := 100;
856 IF l_layer_cursor%NOTFOUND THEN
857
858 l_layer := CSTPLMWI.get_last_layer
859 (
860 i_wip_entity_id,
861 wro_rec.operation_seq_num,
862 wro_rec.inventory_item_id,
863 o_err_num,
864 o_err_msg
865 );
866 IF o_err_num <> 0 THEN
867 RETURN;
868 END IF;
869
870 l_consumed_qty := l_txn_qty_remaining;
871
872 ELSE
873 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
874 greatest( sign( wro_rec.quantity_per_assembly ) *
875 -( l_layer.relieved_matl_scrap_qty ),
876 sign( wro_rec.quantity_per_assembly ) *
877 l_txn_qty_remaining );
878 END IF;
879
880
881 l_stmt_num := 110;
882 update cst_wip_layers CWL
883 set
884 relieved_matl_scrap_qty = relieved_matl_scrap_qty + l_consumed_qty,
885 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
886 where
887 wip_layer_id = l_layer.wip_layer_id and
888 inv_layer_id = l_layer.inv_layer_id;
889
890 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
891
892 END LOOP; /* l_layer_cursor */
893
894 l_stmt_num := 120;
895 close l_layer_cursor;
896
897 END LOOP; /* wro_cursor */
898
899
900
901
902 /* update WROCD */
903 l_stmt_num := 130;
904 update wip_req_operation_cost_details WROCD
905 set
906 (
907 WROCD.relieved_matl_scrap_value,
908 WROCD.temp_relieved_value
909 )
910 =
911 (
912 select
913 NVL( WROCD.relieved_matl_scrap_value, 0 ) +
914 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
915 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
916 from
917 cst_wip_layers CWL,
918 cst_wip_layer_cost_details CWLCD
919 where
920 CWL.wip_entity_id = WROCD.wip_entity_id and
921 CWL.operation_seq_num = WROCD.operation_seq_num and
922 CWL.inventory_item_id = WROCD.inventory_item_id and
923 CWL.temp_relieved_qty <> 0 and
924 CWLCD.wip_layer_id = CWL.wip_layer_id and
925 CWLCD.inv_layer_id = CWL.inv_layer_id and
926 CWLCD.cost_element_id = WROCD.cost_element_id and
927 CWLCD.level_type in (1, 2)
928 )
929 where
930 (
931 WROCD.wip_entity_id,
932 WROCD.operation_seq_num,
933 WROCD.inventory_item_id
934 )
935 IN
936 (
937 select wip_entity_id,
938 operation_seq_num,
939 inventory_item_id
940 from wip_requirement_operations WRO
941 where
942 WRO.wip_entity_id = i_wip_entity_id and
943 WRO.operation_seq_num <= i_op_seq_num and
944
945 /* exclude bulk, supplier, phantom */
946
947 WRO.wip_supply_type not in (4,5,6) and
948 WRO.quantity_per_assembly <> 0
949 );
950
951
952
953 /* update WRO */
954 update wip_requirement_operations WRO
955 set relieved_matl_scrap_quantity
956 =
957 (
958 select
959 NVL( WRO.relieved_matl_scrap_quantity, 0 ) +
960 sum( CWL.temp_relieved_qty )
961 from
962 cst_wip_layers CWL
963 where
964 CWL.wip_entity_id = WRO.wip_entity_id and
965 CWL.operation_seq_num = WRO.operation_seq_num and
966 CWL.inventory_item_id = WRO.inventory_item_id and
967 CWL.temp_relieved_qty <> 0
968 )
969 where
970 WRO.wip_entity_id = i_wip_entity_id and
971 WRO.operation_seq_num <= i_op_seq_num and
972
973 /* exclude bulk, supplier, phantom */
974
975 WRO.wip_supply_type not in (4,5,6) and
976 WRO.quantity_per_assembly <> 0;
977
978
979
980 /*------------------------------------------------------------
981 BEGIN Dual maintenance section with CSTPACSB.pls
982 ------------------------------------------------------------*/
983
984
985 /* insert into wip_scrap_values for WROCD */
986
987 l_stmt_num := 140;
988
989 INSERT INTO WIP_SCRAP_VALUES
990 (
991 transaction_id,
992 level_type,
993 cost_element_id,
994 cost_element_value,
995 cost_update_id,
996 last_update_date,
997 last_updated_by,
998 created_by,
999 creation_date,
1000 last_update_login,
1001 request_id,
1002 program_application_id,
1003 program_id,
1004 program_update_date
1005 )
1006 SELECT
1007 i_txn_id, /* transaction_id, */
1008 2, /* level_type, */
1009 WROCD.cost_element_id, /* cost_element_id, */
1010 sum( NVL( WROCD.temp_relieved_value, 0 ) ) / i_txn_qty,
1011 /* cost_element_value, */
1012 NULL, /* cost_update_id, */
1013 sysdate, /* last_update_date, */
1014 i_user_id, /* last_updated_by, */
1015 i_user_id, /* created_by, */
1016 sysdate, /* creation_date, */
1017 i_login_id, /* last_update_login, */
1018 i_request_id, /* request_id, */
1019 i_prog_appl_id, /* program_application_id, */
1020 i_prog_id, /* program_id, */
1021 sysdate /* program_update_date */
1022 FROM
1023 wip_req_operation_cost_details WROCD
1024 where
1025 wrocd.WIP_ENTITY_ID = i_wip_entity_id
1026 GROUP BY
1027 wrocd.COST_ELEMENT_ID
1028 HAVING
1029 sum( NVL( WROCD.temp_relieved_value, 0 ) ) <> 0;
1030
1031
1032
1033 l_stmt_num := 150;
1034
1035 UPDATE WIP_OPERATION_RESOURCES W1
1036 SET
1037 (RELIEVED_RES_SCRAP_UNITS,
1038 TEMP_RELIEVED_VALUE,
1039 RELIEVED_RES_scrap_VALUE) =
1040 (SELECT
1041 nvl(W1.RELIEVED_RES_scrap_UNITS,0)+
1042 nvl(W2.RELIEVED_RES_scrap_UNITS,0)*
1043 decode(abs(i_txn_qty),
1044 PRIOR_SCRAP_QUANTITY,-1,
1045 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1046 PRIOR_SCRAP_QUANTITY)),
1047 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
1048 decode(abs(i_txn_qty),
1049 PRIOR_SCRAP_QUANTITY,-1,
1050 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1051 PRIOR_SCRAP_QUANTITY)),
1052 nvl(W1.RELIEVED_RES_scrap_VALUE,0)+
1053 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
1054 decode(abs(i_txn_qty),
1055 PRIOR_SCRAP_QUANTITY,-1,
1056 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1057 PRIOR_SCRAP_QUANTITY))
1058 FROM
1059 WIP_OPERATION_RESOURCES W2,
1060 cst_comp_snapshot COCD
1061 WHERE
1062 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1063 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1064 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1065 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
1066 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
1067 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
1068 COCD.NEW_OPERATION_FLAG = 2 AND
1069 COCD.TRANSACTION_ID = I_TXN_ID)
1070 WHERE
1071 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
1072 W1.ORGANIZATION_ID = I_ORG_ID AND
1073 w1.usage_rate_or_amount <> 0 AND
1074 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
1075
1076
1077 l_stmt_num := 160;
1078
1079 UPDATE wip_operation_overheads W1
1080 SET
1081 (RELIEVED_ovhd_SCRAP_UNITS,
1082 TEMP_RELIEVED_VALUE,
1083 RELIEVED_ovhd_scrap_value) =
1084 (SELECT
1085 nvl(W1.RELIEVED_ovhd_SCRAP_UNITS,0)+
1086 nvl(W2.RELIEVED_ovhd_SCRAP_UNITS,0)*
1087 decode(abs(i_txn_qty),
1088 PRIOR_SCRAP_QUANTITY,-1,
1089 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
1090 nvl(W2.RELIEVED_ovhd_scrap_value,0)*
1091 decode(abs(i_txn_qty),
1092 PRIOR_SCRAP_QUANTITY,-1,
1093 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
1094 nvl(W1.RELIEVED_ovhd_scrap_value,0)+
1095 nvl(W2.RELIEVED_ovhd_scrap_value,0)*
1096 decode(abs(i_txn_qty),
1097 PRIOR_SCRAP_QUANTITY,-1,
1098 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY))
1099 FROM
1100 wip_operation_overheads W2,
1101 cst_comp_snapshot COCD
1102 WHERE
1103 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1104 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1105 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1106 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
1107 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
1108 W2.BASIS_TYPE = W1.BASIS_TYPE AND
1109 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
1110 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
1111 COCD.NEW_OPERATION_FLAG = 2 AND
1112 COCD.TRANSACTION_ID = I_TXN_ID)
1113 WHERE
1114 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
1115 W1.ORGANIZATION_ID = I_ORG_ID AND
1116 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
1117
1118
1119
1120
1121 l_stmt_num := 170;
1122
1123 INSERT INTO WIP_SCRAP_VALUES
1124 (
1125 transaction_id,
1126 level_type,
1127 cost_element_id,
1128 cost_element_value,
1129 cost_update_id,
1130 last_update_date,
1131 last_updated_by,
1132 created_by,
1133 creation_date,
1134 last_update_login,
1135 request_id,
1136 program_application_id,
1137 program_id,
1138 program_update_date
1139 )
1140 SELECT
1141 i_txn_id, /* transaction_id, */
1142 1, /* level_type, */
1143 BR.cost_element_id, /* cost_element_id, */
1144 sum( NVL( WOR.temp_relieved_value, 0 ) ) / i_txn_qty,
1145 /* cost_element_value, */
1146 NULL, /* cost_update_id, */
1147 sysdate, /* last_update_date, */
1148 i_user_id, /* last_updated_by, */
1149 i_user_id, /* created_by, */
1150 sysdate, /* creation_date, */
1151 i_login_id, /* last_update_login, */
1152 i_request_id, /* request_id, */
1153 i_prog_appl_id, /* program_application_id, */
1154 i_prog_id, /* program_id, */
1155 sysdate /* program_update_date */
1156 FROM
1157 wip_operation_resources WOR,
1158 bom_resources BR
1159 WHERE
1160 WOR.wip_entity_id = i_wip_entity_id AND
1161 WOR.organization_id = i_org_id AND
1162 BR.resource_id = WOR.resource_id AND
1163 BR.organization_id = WOR.organization_id
1164 group by
1165 BR.cost_element_id
1166 HAVING
1167 SUM(nvl(temp_relieved_value,0)) <> 0;
1168
1169
1170 l_stmt_num := 180;
1171
1172 INSERT INTO WIP_SCRAP_VALUES
1173 (
1174 transaction_id,
1175 level_type,
1176 cost_element_id,
1177 cost_element_value,
1178 cost_update_id,
1179 last_update_date,
1180 last_updated_by,
1181 created_by,
1182 creation_date,
1183 last_update_login,
1184 request_id,
1185 program_application_id,
1186 program_id,
1187 program_update_date
1188 )
1189 SELECT
1190 i_txn_id, /* transaction_id, */
1191 1, /* level_type, */
1192 5, /* cost_element_id, */
1193 sum( NVL( WOO.temp_relieved_value, 0 ) ) / i_txn_qty,
1194 /* cost_element_value, */
1195 NULL, /* cost_update_id, */
1196 sysdate, /* last_update_date, */
1197 i_user_id, /* last_updated_by, */
1198 i_user_id, /* created_by, */
1199 sysdate, /* creation_date, */
1200 i_login_id, /* last_update_login, */
1201 i_request_id, /* request_id, */
1202 i_prog_appl_id, /* program_application_id, */
1203 i_prog_id, /* program_id, */
1204 sysdate /* program_update_date */
1205 FROM
1206 wip_operation_overheads WOO
1207 WHERE
1208 WOO.wip_entity_id = i_wip_entity_id AND
1209 WOO.organization_id = i_org_id
1210 HAVING
1211 SUM(nvl(temp_relieved_value,0)) <> 0;
1212
1213
1214
1215 l_stmt_num := 190;
1216
1217 INSERT INTO mtl_cst_txn_cost_details
1218 (
1219 TRANSACTION_ID,
1220 ORGANIZATION_ID,
1221 INVENTORY_ITEM_ID,
1222 COST_ELEMENT_ID,
1223 LEVEL_TYPE,
1224 TRANSACTION_COST,
1225 NEW_AVERAGE_COST,
1226 PERCENTAGE_CHANGE,
1227 VALUE_CHANGE,
1228 LAST_UPDATE_DATE,
1229 LAST_UPDATED_BY,
1230 CREATION_DATE,
1231 CREATED_BY,
1232 LAST_UPDATE_LOGIN,
1233 REQUEST_ID,
1234 PROGRAM_APPLICATION_ID,
1235 PROGRAM_ID,
1236 PROGRAM_UPDATE_DATE
1237 )
1238 SELECT
1239 i_txn_id,
1240 i_org_id,
1241 i_inv_item_id,
1242 cost_element_id,
1243 level_type,
1244 cost_element_value,
1245 NULL,
1246 NULL,
1247 NULL,
1248 sysdate, /* LAST_UPDATE_DATE, */
1249 i_user_id, /* LAST_UPDATED_BY, */
1250 sysdate, /* CREATION_DATE, */
1251 i_user_id, /* CREATED_BY, */
1252 i_login_id, /* LAST_UPDATE_LOGIN, */
1253 i_request_id, /* REQUEST_ID, */
1254 i_prog_appl_id, /* PROGRAM_APPLICATION_ID, */
1255 i_prog_id, /* PROGRAM_ID, */
1256 sysdate /* PROGRAM_UPDATE_DATE */
1257 FROM
1258 wip_scrap_values WSV
1259 WHERE
1260 WSV.transaction_id = i_txn_id AND
1261 WSV.cost_update_id IS NULL;
1262
1263
1264 /*------------------------------------------------------------
1265 END Dual maintenance section with CSTPACSB.pls
1266 ------------------------------------------------------------*/
1267
1268
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 o_err_num := SQLCODE;
1273 o_err_msg := 'CSTPLCAS.scrap_return():' ||
1274 to_char(l_stmt_num) || ':' ||
1275 substrb(SQLERRM,1,150);
1276
1277 END scrap_return;
1278
1279
1280
1281
1282
1283
1284
1285 END CSTPLCAS;