[Home] [Help]
PACKAGE BODY: APPS.CSTPLCAS
Source
1 PACKAGE BODY CSTPLCAS AS
2 /* $Header: CSTLCASB.pls 120.2.12020000.2 2012/07/11 12:58:41 vkatakam ship $ */
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 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
424 COCD.NEW_OPERATION_FLAG = 2 AND */
425 COCD.TRANSACTION_ID = I_TXN_ID)
426 WHERE
427 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
428 W1.ORGANIZATION_ID = I_ORG_ID AND
429 /* w1.USAGE_RATE_OR_AMOUNT <> 0 AND Bug 9338256*/
430 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
431
432
433 l_stmt_num := 170;
434
435 UPDATE wip_operation_overheads w1
436 SET
437 (relieved_ovhd_scrap_units,
438 temp_relieved_value,
439 relieved_ovhd_scrap_value) =
440 (SELECT
441 NVL(w1.relieved_ovhd_scrap_units,0) +
442 decode(sign(applied_ovhd_units -
443 nvl(relieved_ovhd_completion_units,0)-
444 nvl(relieved_ovhd_final_comp_units,0)-
445 nvl(relieved_ovhd_scrap_units,0)),
446 1,
447 (applied_ovhd_units -
448 nvl(relieved_ovhd_completion_units,0)-
449 nvl(relieved_ovhd_final_comp_units,0)-
450 nvl(relieved_ovhd_scrap_units,0))*
451
452 /* new to solve divided by zero and over relieved */
453
454 decode(sign(i_txn_qty - (cocd.quantity_completed -
455 nvl(prior_completion_quantity,0) -
456 nvl(prior_scrap_quantity,0))),
457 -1,i_txn_qty/(cocd.quantity_completed -
458 nvl(prior_completion_quantity,0) -
459 nvl(prior_scrap_quantity,0)),
460 1),
461 0),
462 decode(sign(applied_ovhd_value -
463 nvl(relieved_ovhd_completion_value,0)-
464 nvl(relieved_variance_value,0)-
465 nvl(relieved_ovhd_scrap_value,0)),
466 1,
467 (applied_ovhd_value -
468 nvl(relieved_ovhd_completion_value,0)-
469 nvl(relieved_variance_value,0)-
470 nvl(relieved_ovhd_scrap_value,0))*
471
472 /* new to solve divided by zero and over relieved */
473
474 decode(sign(i_txn_qty - (cocd.quantity_completed -
475 nvl(prior_completion_quantity,0) -
476 nvl(prior_scrap_quantity,0))),
477 -1,i_txn_qty/(cocd.quantity_completed -
478 nvl(prior_completion_quantity,0) -
479 nvl(prior_scrap_quantity,0)),
480 1),
481 0),
482 nvl(W1.relieved_ovhd_scrap_value,0) +
483 decode(sign(applied_ovhd_value -
484 nvl(relieved_ovhd_completion_value,0)-
485 nvl(relieved_variance_value,0)-
486 nvl(relieved_ovhd_scrap_value,0)),
487 1,
488 (applied_ovhd_value -
489 nvl(relieved_ovhd_completion_value,0)-
490 nvl(relieved_variance_value,0)-
491 nvl(relieved_ovhd_scrap_value,0))*
492
493 /* new to solve divided by zero and over relieved */
494
495 decode(sign(i_txn_qty - (cocd.quantity_completed -
496 nvl(prior_completion_quantity,0) -
497 nvl(prior_scrap_quantity,0))),
498 -1,i_txn_qty/(cocd.quantity_completed -
499 nvl(prior_completion_quantity,0) -
500 nvl(prior_scrap_quantity,0)),
501 1),
502 0)
503 FROM
504 wip_operation_overheads W2,
505 cst_comp_snapshot COCD
506 WHERE
507 w1.wip_entity_id = w2.wip_entity_id AND
508 w1.operation_seq_num = w2.operation_seq_num AND
509 w1.resource_seq_num = w2.resource_seq_num AND
510 w1.overhead_id = w2.overhead_id AND
511 w1.organization_id = w2.organization_id AND
512 w1.basis_type = w2.basis_type AND
513 w2.operation_seq_num = cocd.operation_seq_num AND
514 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
515 cocd.new_operation_flag = 2 AND */
516 cocd.transaction_id = i_txn_id)
517 WHERE
518 w1.wip_entity_id = i_wip_entity_id AND
519 w1.organization_id = i_org_id AND
520 w1.operation_seq_num <= i_op_seq_num;
521
522
523
524
525 l_stmt_num := 180;
526
527 INSERT INTO WIP_SCRAP_VALUES
528 (
529 transaction_id,
530 level_type,
531 cost_element_id,
532 cost_element_value,
533 cost_update_id,
534 last_update_date,
535 last_updated_by,
536 created_by,
537 creation_date,
538 last_update_login,
539 request_id,
540 program_application_id,
541 program_id,
542 program_update_date
543 )
544 SELECT
545 i_txn_id, /* transaction_id, */
546 1, /* level_type, */
547 BR.cost_element_id, /* cost_element_id, */
548 sum( NVL( WOR.temp_relieved_value, 0 ) ) / i_txn_qty,
549 /* cost_element_value, */
550 NULL, /* cost_update_id, */
551 sysdate, /* last_update_date, */
552 i_user_id, /* last_updated_by, */
553 i_user_id, /* created_by, */
554 sysdate, /* creation_date, */
555 i_login_id, /* last_update_login, */
556 i_request_id, /* request_id, */
557 i_prog_appl_id, /* program_application_id, */
558 i_prog_id, /* program_id, */
559 sysdate /* program_update_date */
560 FROM
561 wip_operation_resources WOR,
562 bom_resources BR
563 WHERE
564 WOR.wip_entity_id = i_wip_entity_id AND
565 WOR.organization_id = i_org_id AND
566 BR.resource_id = WOR.resource_id AND
567 BR.organization_id = WOR.organization_id
568 group by
569 BR.cost_element_id
570 HAVING
571 SUM(nvl(temp_relieved_value,0)) <> 0;
572
573
574 l_stmt_num := 190;
575
576 INSERT INTO WIP_SCRAP_VALUES
577 (
578 transaction_id,
579 level_type,
580 cost_element_id,
581 cost_element_value,
582 cost_update_id,
583 last_update_date,
584 last_updated_by,
585 created_by,
586 creation_date,
587 last_update_login,
588 request_id,
589 program_application_id,
590 program_id,
591 program_update_date
592 )
593 SELECT
594 i_txn_id, /* transaction_id, */
595 1, /* level_type, */
596 5, /* cost_element_id, */
597 sum( NVL( WOO.temp_relieved_value, 0 ) ) / i_txn_qty,
598 /* cost_element_value, */
599 NULL, /* cost_update_id, */
600 sysdate, /* last_update_date, */
601 i_user_id, /* last_updated_by, */
602 i_user_id, /* created_by, */
603 sysdate, /* creation_date, */
604 i_login_id, /* last_update_login, */
605 i_request_id, /* request_id, */
606 i_prog_appl_id, /* program_application_id, */
607 i_prog_id, /* program_id, */
608 sysdate /* program_update_date */
609 FROM
610 wip_operation_overheads WOO
611 WHERE
612 WOO.wip_entity_id = i_wip_entity_id AND
613 WOO.organization_id = i_org_id
614 HAVING
615 SUM(nvl(temp_relieved_value,0)) <> 0;
616
617
618
619 /******************************************************
620 * Insert rows into mtl_cst_txn_cost_details *
621 ******************************************************/
622
623 l_stmt_num := 200;
624
625 INSERT INTO mtl_cst_txn_cost_details
626 (
627 TRANSACTION_ID,
628 ORGANIZATION_ID,
629 INVENTORY_ITEM_ID,
630 COST_ELEMENT_ID,
631 LEVEL_TYPE,
632 TRANSACTION_COST,
633 NEW_AVERAGE_COST,
634 PERCENTAGE_CHANGE,
635 VALUE_CHANGE,
636 LAST_UPDATE_DATE,
637 LAST_UPDATED_BY,
638 CREATION_DATE,
639 CREATED_BY,
640 LAST_UPDATE_LOGIN,
641 REQUEST_ID,
642 PROGRAM_APPLICATION_ID,
643 PROGRAM_ID,
644 PROGRAM_UPDATE_DATE
645 )
646 SELECT
647 i_txn_id,
648 i_org_id,
649 i_inv_item_id,
650 cost_element_id,
651 level_type,
652 cost_element_value,
653 NULL,
654 NULL,
655 NULL,
656 sysdate, /* LAST_UPDATE_DATE, */
657 i_user_id, /* LAST_UPDATED_BY, */
658 sysdate, /* CREATION_DATE, */
659 i_user_id, /* CREATED_BY, */
660 i_login_id, /* LAST_UPDATE_LOGIN, */
661 i_request_id, /* REQUEST_ID, */
662 i_prog_appl_id, /* PROGRAM_APPLICATION_ID,*/
663 i_prog_id, /* PROGRAM_ID, */
664 sysdate /* PROGRAM_UPDATE_DATE */
665 FROM
666 wip_scrap_values WSV
667 WHERE
668 WSV.transaction_id = i_txn_id AND
669 WSV.cost_update_id IS NULL;
670
671
672 /*------------------------------------------------------------
673 END Dual maintenance section with CSTPACSB.pls
674 ------------------------------------------------------------*/
675
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 o_err_num := SQLCODE;
680 o_err_msg := 'CSTPLCAS.scrap():' ||
681 to_char(l_stmt_num) || ':' ||
682 substrb(SQLERRM,1,150);
683
684 END scrap;
685
686
687
688
689
690
691
692 PROCEDURE scrap_return (
693 i_cost_method_id IN NUMBER,
694 i_txn_id IN NUMBER,
695 i_layer_id IN NUMBER,
696 i_inv_item_id IN NUMBER,
697 i_org_id IN NUMBER,
698 i_wip_entity_id IN NUMBER,
699 i_txn_qty IN NUMBER,
700 i_op_seq_num IN NUMBER,
701 i_user_id IN NUMBER,
702 i_login_id IN NUMBER,
703 i_request_id IN NUMBER,
704 i_prog_id IN NUMBER,
705 i_prog_appl_id IN NUMBER,
706 o_err_num OUT NOCOPY NUMBER,
707 o_err_msg OUT NOCOPY VARCHAR2
708 )
709 IS
710
711
712 l_stmt_num NUMBER := 0;
713
714 l_sql_stmt VARCHAR2(8000);
715 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
716 l_layer cst_wip_layers%ROWTYPE;
717
718 l_txn_qty_remaining NUMBER;
719 l_consumed_qty NUMBER;
720 l_lot_size NUMBER := 1; /* Added as part of LBM */
721 l_include_comp_yield NUMBER;
722
723 /* Changed as a part of LBM project Bug #3926918
724 Added decode to WRO.qpa to get proportioned qpa in
725 case of Lot Based Materials
726 Divide the value of quantity per assembly by component
727 yield factor if Include Component Yield Flag is checked
728 in WIP Parameters. */
729
730 cursor wro_cursor is
731 select WRO.operation_seq_num,
732 WRO.inventory_item_id,
733 Decode(WRO.basis_type, 2, WRO.quantity_per_assembly/l_lot_size,
734 WRO.quantity_per_assembly)/
735 DECODE(l_include_comp_yield,
736 1, nvl(WRO.component_yield_factor,1),
737 1) quantity_per_assembly,
738 WRO.relieved_matl_scrap_quantity,
739 decode( nvl( CCS.prior_scrap_quantity, 0 ), 0, 1,
740 i_txn_qty / CCS.prior_scrap_quantity ) component_ratio
741 from wip_requirement_operations WRO,
742 cst_comp_snapshot CCS
743 where WRO.wip_entity_id = i_wip_entity_id and
744 WRO.operation_seq_num <= i_op_seq_num and
745 WRO.wip_supply_type not in (4,5,6) and
746 WRO.quantity_per_assembly <> 0 and
747 CCS.transaction_id = i_txn_id and
748 CCS.wip_entity_id = WRO.wip_entity_id and
749 CCS.operation_seq_num = WRO.operation_seq_num; /* and
750 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
751 CCS.new_operation_flag = 2;*/
752
753 BEGIN
754
755 /* normally i_txn_qty < 0 for scrap return */
756
757 /*--------------------------------------------------
758 Update temp_relieved_value to zero in all tables
759 --------------------------------------------------*/
760
761 l_stmt_num := 10;
762
763 CSTPLMWI.reset_temp_columns
764 (
765 i_wip_entity_id,
766 o_err_num,
767 o_err_msg
768 );
769 IF o_err_num <> 0 THEN
770 RETURN;
771 END IF;
772
773
774 /*---------------------------------------------
775 Get the lot size of the job
776 (Form validation takes care lot size is not 0)
777 Added for Lot Based Materials project
778 ----------------------------------------------*/
779 l_stmt_num := 20;
780 SELECT wdj.start_quantity
781 INTO l_lot_size
782 FROM wip_discrete_jobs wdj
783 WHERE wdj.wip_entity_id = i_wip_entity_id
784 AND wdj.organization_id = i_org_id;
785
786 /*----------------------------------------------
787 Get the value of Include Component yield flag,
788 which will determine whether to include or not
789 component yield factor in quantity per assembly
790 ------------------------------------------------*/
791 l_stmt_num := 25;
792 SELECT nvl(include_component_yield, 1)
793 INTO l_include_comp_yield
794 FROM wip_parameters
795 WHERE organization_id = i_org_id;
796
797 /*-------------------------------------------
798 Replenish component material quantities
799 -------------------------------------------*/
800
801 l_stmt_num := 60;
802 FOR wro_rec IN wro_cursor LOOP
803
804 CSTPLMWI.init_wip_layers
805 (
806 i_wip_entity_id,
807 wro_rec.operation_seq_num,
808 wro_rec.inventory_item_id,
809 i_org_id,
810 i_txn_id,
811 i_layer_id,
812 i_user_id,
813 i_login_id,
814 i_request_id,
815 i_prog_id,
816 i_prog_appl_id,
817 o_err_num,
818 o_err_msg
819 );
820 IF o_err_num <> 0 THEN
821 RETURN;
822 END IF;
823
824
825
826 /* replenish WIP layer(s)
827 scrap return replenishes WIP in reverse order */
828 l_stmt_num := 70;
829 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
830 (
831 ' sign( CWL.relieved_matl_scrap_qty ) = ' ||
832 ' sign( :quantity_per_assembly ) ',
833 i_cost_method_id,
834 CSTPLMWI.REVERSE
835 );
836
837 l_stmt_num := 80;
838 open l_layer_cursor
839 for l_sql_stmt
840 using i_wip_entity_id,
841 wro_rec.operation_seq_num,
842 wro_rec.inventory_item_id,
843 wro_rec.quantity_per_assembly;
844
845
846 /* reduce the relieved_matl_scrap_quantity
847 by percentage using cst_comp_snapshot */
848 l_txn_qty_remaining := nvl(wro_rec.relieved_matl_scrap_quantity, 0) *
849 nvl(wro_rec.component_ratio, 0);
850
851
852 LOOP
853 exit when l_txn_qty_remaining = 0;
854
855 l_stmt_num := 90;
856 fetch l_layer_cursor into l_layer;
857
858 l_stmt_num := 100;
859 IF l_layer_cursor%NOTFOUND THEN
860
861 l_layer := CSTPLMWI.get_last_layer
862 (
863 i_wip_entity_id,
864 wro_rec.operation_seq_num,
865 wro_rec.inventory_item_id,
866 o_err_num,
867 o_err_msg
868 );
869 IF o_err_num <> 0 THEN
870 RETURN;
871 END IF;
872
873 l_consumed_qty := l_txn_qty_remaining;
874
875 ELSE
876 l_consumed_qty := sign( wro_rec.quantity_per_assembly ) *
877 greatest( sign( wro_rec.quantity_per_assembly ) *
878 -( l_layer.relieved_matl_scrap_qty ),
879 sign( wro_rec.quantity_per_assembly ) *
880 l_txn_qty_remaining );
881 END IF;
882
883
884 l_stmt_num := 110;
885 update cst_wip_layers CWL
886 set
887 relieved_matl_scrap_qty = relieved_matl_scrap_qty + l_consumed_qty,
888 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
889 where
890 wip_layer_id = l_layer.wip_layer_id and
891 inv_layer_id = l_layer.inv_layer_id;
892
893 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
894
895 END LOOP; /* l_layer_cursor */
896
897 l_stmt_num := 120;
898 close l_layer_cursor;
899
900 END LOOP; /* wro_cursor */
901
902
903
904
905 /* update WROCD */
906 l_stmt_num := 130;
907 update wip_req_operation_cost_details WROCD
908 set
909 (
910 WROCD.relieved_matl_scrap_value,
911 WROCD.temp_relieved_value
912 )
913 =
914 (
915 select
916 NVL( WROCD.relieved_matl_scrap_value, 0 ) +
917 sum( CWL.temp_relieved_qty * CWLCD.layer_cost ),
918 sum( CWL.temp_relieved_qty * CWLCD.layer_cost )
919 from
920 cst_wip_layers CWL,
921 cst_wip_layer_cost_details CWLCD
922 where
923 CWL.wip_entity_id = WROCD.wip_entity_id and
924 CWL.operation_seq_num = WROCD.operation_seq_num and
925 CWL.inventory_item_id = WROCD.inventory_item_id and
926 CWL.temp_relieved_qty <> 0 and
927 CWLCD.wip_layer_id = CWL.wip_layer_id and
928 CWLCD.inv_layer_id = CWL.inv_layer_id and
929 CWLCD.cost_element_id = WROCD.cost_element_id and
930 CWLCD.level_type in (1, 2)
931 )
932 where
933 (
934 WROCD.wip_entity_id,
935 WROCD.operation_seq_num,
936 WROCD.inventory_item_id
937 )
938 IN
939 (
940 select wip_entity_id,
941 operation_seq_num,
942 inventory_item_id
943 from wip_requirement_operations WRO
944 where
945 WRO.wip_entity_id = i_wip_entity_id and
946 WRO.operation_seq_num <= i_op_seq_num and
947
948 /* exclude bulk, supplier, phantom */
949
950 WRO.wip_supply_type not in (4,5,6) and
951 WRO.quantity_per_assembly <> 0
952 );
953
954
955
956 /* update WRO */
957 update wip_requirement_operations WRO
958 set relieved_matl_scrap_quantity
959 =
960 (
961 select
962 NVL( WRO.relieved_matl_scrap_quantity, 0 ) +
963 sum( CWL.temp_relieved_qty )
964 from
965 cst_wip_layers CWL
966 where
967 CWL.wip_entity_id = WRO.wip_entity_id and
968 CWL.operation_seq_num = WRO.operation_seq_num and
969 CWL.inventory_item_id = WRO.inventory_item_id and
970 CWL.temp_relieved_qty <> 0
971 )
972 where
973 WRO.wip_entity_id = i_wip_entity_id and
974 WRO.operation_seq_num <= i_op_seq_num and
975
976 /* exclude bulk, supplier, phantom */
977
978 WRO.wip_supply_type not in (4,5,6) and
979 WRO.quantity_per_assembly <> 0;
980
981
982
983 /*------------------------------------------------------------
984 BEGIN Dual maintenance section with CSTPACSB.pls
985 ------------------------------------------------------------*/
986
987
988 /* insert into wip_scrap_values for WROCD */
989
990 l_stmt_num := 140;
991
992 INSERT INTO WIP_SCRAP_VALUES
993 (
994 transaction_id,
995 level_type,
996 cost_element_id,
997 cost_element_value,
998 cost_update_id,
999 last_update_date,
1000 last_updated_by,
1001 created_by,
1002 creation_date,
1003 last_update_login,
1004 request_id,
1005 program_application_id,
1006 program_id,
1007 program_update_date
1008 )
1009 SELECT
1010 i_txn_id, /* transaction_id, */
1011 2, /* level_type, */
1012 WROCD.cost_element_id, /* cost_element_id, */
1013 sum( NVL( WROCD.temp_relieved_value, 0 ) ) / i_txn_qty,
1014 /* cost_element_value, */
1015 NULL, /* cost_update_id, */
1016 sysdate, /* last_update_date, */
1017 i_user_id, /* last_updated_by, */
1018 i_user_id, /* created_by, */
1019 sysdate, /* creation_date, */
1020 i_login_id, /* last_update_login, */
1021 i_request_id, /* request_id, */
1022 i_prog_appl_id, /* program_application_id, */
1023 i_prog_id, /* program_id, */
1024 sysdate /* program_update_date */
1025 FROM
1026 wip_req_operation_cost_details WROCD
1027 where
1028 wrocd.WIP_ENTITY_ID = i_wip_entity_id
1029 GROUP BY
1030 wrocd.COST_ELEMENT_ID
1031 HAVING
1032 sum( NVL( WROCD.temp_relieved_value, 0 ) ) <> 0;
1033
1034
1035
1036 l_stmt_num := 150;
1037
1038 UPDATE WIP_OPERATION_RESOURCES W1
1039 SET
1040 (RELIEVED_RES_SCRAP_UNITS,
1041 TEMP_RELIEVED_VALUE,
1042 RELIEVED_RES_scrap_VALUE) =
1043 (SELECT
1044 nvl(W1.RELIEVED_RES_scrap_UNITS,0)+
1045 nvl(W2.RELIEVED_RES_scrap_UNITS,0)*
1046 decode(abs(i_txn_qty),
1047 PRIOR_SCRAP_QUANTITY,-1,
1048 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1049 PRIOR_SCRAP_QUANTITY)),
1050 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
1051 decode(abs(i_txn_qty),
1052 PRIOR_SCRAP_QUANTITY,-1,
1053 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1054 PRIOR_SCRAP_QUANTITY)),
1055 nvl(W1.RELIEVED_RES_scrap_VALUE,0)+
1056 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
1057 decode(abs(i_txn_qty),
1058 PRIOR_SCRAP_QUANTITY,-1,
1059 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,
1060 PRIOR_SCRAP_QUANTITY))
1061 FROM
1062 WIP_OPERATION_RESOURCES W2,
1063 cst_comp_snapshot COCD
1064 WHERE
1065 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1066 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1067 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1068 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
1069 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
1070 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
1071 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1072 COCD.NEW_OPERATION_FLAG = 2 AND */
1073 COCD.TRANSACTION_ID = I_TXN_ID)
1074 WHERE
1075 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
1076 W1.ORGANIZATION_ID = I_ORG_ID AND
1077 w1.usage_rate_or_amount <> 0 AND
1078 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
1079
1080
1081 l_stmt_num := 160;
1082
1083 UPDATE wip_operation_overheads W1
1084 SET
1085 (RELIEVED_ovhd_SCRAP_UNITS,
1086 TEMP_RELIEVED_VALUE,
1087 RELIEVED_ovhd_scrap_value) =
1088 (SELECT
1089 nvl(W1.RELIEVED_ovhd_SCRAP_UNITS,0)+
1090 nvl(W2.RELIEVED_ovhd_SCRAP_UNITS,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(W2.RELIEVED_ovhd_scrap_value,0)*
1095 decode(abs(i_txn_qty),
1096 PRIOR_SCRAP_QUANTITY,-1,
1097 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
1098 nvl(W1.RELIEVED_ovhd_scrap_value,0)+
1099 nvl(W2.RELIEVED_ovhd_scrap_value,0)*
1100 decode(abs(i_txn_qty),
1101 PRIOR_SCRAP_QUANTITY,-1,
1102 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY))
1103 FROM
1104 wip_operation_overheads W2,
1105 cst_comp_snapshot COCD
1106 WHERE
1107 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1108 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1109 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1110 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
1111 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
1112 W2.BASIS_TYPE = W1.BASIS_TYPE AND
1113 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
1114 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
1115 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1116 COCD.NEW_OPERATION_FLAG = 2 AND */
1117 COCD.TRANSACTION_ID = I_TXN_ID)
1118 WHERE
1119 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
1120 W1.ORGANIZATION_ID = I_ORG_ID AND
1121 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
1122
1123
1124
1125
1126 l_stmt_num := 170;
1127
1128 INSERT INTO WIP_SCRAP_VALUES
1129 (
1130 transaction_id,
1131 level_type,
1132 cost_element_id,
1133 cost_element_value,
1134 cost_update_id,
1135 last_update_date,
1136 last_updated_by,
1137 created_by,
1138 creation_date,
1139 last_update_login,
1140 request_id,
1141 program_application_id,
1142 program_id,
1143 program_update_date
1144 )
1145 SELECT
1146 i_txn_id, /* transaction_id, */
1147 1, /* level_type, */
1148 BR.cost_element_id, /* cost_element_id, */
1149 sum( NVL( WOR.temp_relieved_value, 0 ) ) / i_txn_qty,
1150 /* cost_element_value, */
1151 NULL, /* cost_update_id, */
1152 sysdate, /* last_update_date, */
1153 i_user_id, /* last_updated_by, */
1154 i_user_id, /* created_by, */
1155 sysdate, /* creation_date, */
1156 i_login_id, /* last_update_login, */
1157 i_request_id, /* request_id, */
1158 i_prog_appl_id, /* program_application_id, */
1159 i_prog_id, /* program_id, */
1160 sysdate /* program_update_date */
1161 FROM
1162 wip_operation_resources WOR,
1163 bom_resources BR
1164 WHERE
1165 WOR.wip_entity_id = i_wip_entity_id AND
1166 WOR.organization_id = i_org_id AND
1167 BR.resource_id = WOR.resource_id AND
1168 BR.organization_id = WOR.organization_id
1169 group by
1170 BR.cost_element_id
1171 HAVING
1172 SUM(nvl(temp_relieved_value,0)) <> 0;
1173
1174
1175 l_stmt_num := 180;
1176
1177 INSERT INTO WIP_SCRAP_VALUES
1178 (
1179 transaction_id,
1180 level_type,
1181 cost_element_id,
1182 cost_element_value,
1183 cost_update_id,
1184 last_update_date,
1185 last_updated_by,
1186 created_by,
1187 creation_date,
1188 last_update_login,
1189 request_id,
1190 program_application_id,
1191 program_id,
1192 program_update_date
1193 )
1194 SELECT
1195 i_txn_id, /* transaction_id, */
1196 1, /* level_type, */
1197 5, /* cost_element_id, */
1198 sum( NVL( WOO.temp_relieved_value, 0 ) ) / i_txn_qty,
1199 /* cost_element_value, */
1200 NULL, /* cost_update_id, */
1201 sysdate, /* last_update_date, */
1202 i_user_id, /* last_updated_by, */
1203 i_user_id, /* created_by, */
1204 sysdate, /* creation_date, */
1205 i_login_id, /* last_update_login, */
1206 i_request_id, /* request_id, */
1207 i_prog_appl_id, /* program_application_id, */
1208 i_prog_id, /* program_id, */
1209 sysdate /* program_update_date */
1210 FROM
1211 wip_operation_overheads WOO
1212 WHERE
1213 WOO.wip_entity_id = i_wip_entity_id AND
1214 WOO.organization_id = i_org_id
1215 HAVING
1216 SUM(nvl(temp_relieved_value,0)) <> 0;
1217
1218
1219
1220 l_stmt_num := 190;
1221
1222 INSERT INTO mtl_cst_txn_cost_details
1223 (
1224 TRANSACTION_ID,
1225 ORGANIZATION_ID,
1226 INVENTORY_ITEM_ID,
1227 COST_ELEMENT_ID,
1228 LEVEL_TYPE,
1229 TRANSACTION_COST,
1230 NEW_AVERAGE_COST,
1231 PERCENTAGE_CHANGE,
1232 VALUE_CHANGE,
1233 LAST_UPDATE_DATE,
1234 LAST_UPDATED_BY,
1235 CREATION_DATE,
1236 CREATED_BY,
1237 LAST_UPDATE_LOGIN,
1238 REQUEST_ID,
1239 PROGRAM_APPLICATION_ID,
1240 PROGRAM_ID,
1241 PROGRAM_UPDATE_DATE
1242 )
1243 SELECT
1244 i_txn_id,
1245 i_org_id,
1246 i_inv_item_id,
1247 cost_element_id,
1248 level_type,
1249 cost_element_value,
1250 NULL,
1251 NULL,
1252 NULL,
1253 sysdate, /* LAST_UPDATE_DATE, */
1254 i_user_id, /* LAST_UPDATED_BY, */
1255 sysdate, /* CREATION_DATE, */
1256 i_user_id, /* CREATED_BY, */
1257 i_login_id, /* LAST_UPDATE_LOGIN, */
1258 i_request_id, /* REQUEST_ID, */
1259 i_prog_appl_id, /* PROGRAM_APPLICATION_ID, */
1260 i_prog_id, /* PROGRAM_ID, */
1261 sysdate /* PROGRAM_UPDATE_DATE */
1262 FROM
1263 wip_scrap_values WSV
1264 WHERE
1265 WSV.transaction_id = i_txn_id AND
1266 WSV.cost_update_id IS NULL;
1267
1268
1269 /*------------------------------------------------------------
1270 END Dual maintenance section with CSTPACSB.pls
1271 ------------------------------------------------------------*/
1272
1273
1274
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 o_err_num := SQLCODE;
1278 o_err_msg := 'CSTPLCAS.scrap_return():' ||
1279 to_char(l_stmt_num) || ':' ||
1280 substrb(SQLERRM,1,150);
1281
1282 END scrap_return;
1283
1284
1285
1286
1287
1288
1289
1290 END CSTPLCAS;