[Home] [Help]
PACKAGE BODY: APPS.CSTPACWC
Source
1 PACKAGE BODY CSTPACWC AS
2 /* $Header: CSTPACCB.pls 120.17 2011/10/13 11:10:36 sgundugo ship $ */
3
4 PROCEDURE complete (
5 i_trx_id IN NUMBER,
6 i_txn_qty IN NUMBER,
7 i_txn_date IN DATE,
8 i_acct_period_id IN NUMBER,
9 i_wip_entity_id IN NUMBER,
10 i_org_id IN NUMBER,
11 i_inv_item_id IN NUMBER,
12 i_cost_type_id IN NUMBER,
13 i_res_cost_type_id IN NUMBER,
14 i_final_comp_flag IN VARCHAR2,
15 i_layer_id IN NUMBER,
16 i_movhd_cost_type_id OUT NOCOPY NUMBER,
17 i_cost_group_id IN NUMBER,
18 i_user_id IN NUMBER,
19 i_login_id IN NUMBER,
20 i_request_id IN NUMBER,
21 i_prog_id IN NUMBER,
22 i_prog_appl_id IN NUMBER,
23 err_num OUT NOCOPY NUMBER,
24 err_code OUT NOCOPY VARCHAR2,
25 err_msg OUT NOCOPY VARCHAR2)
26
27 is
28
29 l_auto_final_comp_condition VARCHAR2(1);
30 l_auto_final_comp VARCHAR2(1);
31 l_comp_cost_source NUMBER;
32 l_c_cost_type_id NUMBER;
33 l_system_option_id NUMBER;
34 l_lot_size NUMBER;
35 l_insert_ind NUMBER;
36 l_use_val_cost_type NUMBER;
37 l_acct_period_id NUMBER;
38 l_wcti_txn_id NUMBER;
39 l_count NUMBER;
40 stmt_num NUMBER;
41 l_err_num NUMBER;
42 l_err_code VARCHAR2(240);
43 l_err_msg VARCHAR2(240);
44 proc_fail EXCEPTION;
45 l_future_issued_qty NUMBER := 0 ; /* Added as bugfix 2158763 */
46 l_include_comp_yield NUMBER;
47 l_cost_element NUMBER; /* 2937695 */
48 l_job_value NUMBER; /* 2937695 */
49 l_qty_per_assy NUMBER; /* bug 3504776 */
50
51 /* Cursor added for bug 2158763 */
52 CURSOR c_wip_req_op IS
53 SELECT
54 wip_entity_id,
55 organization_id,
56 inventory_item_id,
57 operation_seq_num,
58 wip_supply_type
59 FROM
60 wip_requirement_operations wro
61 WHERE
62 --
63 -- Exclude bulk, supplier, phantom
64 --
65 wro.wip_supply_type not in (4,5,6) AND
66 wro.wip_entity_id = i_wip_entity_id AND
67 wro.organization_id = i_org_id AND
68 wro.quantity_per_assembly <> 0;
69
70 /*Bug6734270: Final completion cursor should not bother
71 about the quantity per assembly and flush every component
72 if it has value
73 */
74 CURSOR c_wip_final_req_op IS
75 SELECT
76 wip_entity_id,
77 organization_id,
78 inventory_item_id,
79 operation_seq_num,
80 wip_supply_type
81 FROM
82 wip_requirement_operations wro
83 WHERE
84 wro.wip_entity_id = i_wip_entity_id AND
85 wro.organization_id = i_org_id ;
86
87
88 /* 2937695 */
89 CURSOR c_cost_elements IS
90 SELECT
91 cost_element_id,
92 sum(
93 nvl(applied_matl_value,0) -
94 nvl(relieved_matl_completion_value,0) -
95 nvl(relieved_variance_value,0) -
96 nvl(relieved_matl_scrap_value,0)
97 )
98 FROM
99 wip_req_operation_cost_details
100 WHERE
101 wip_entity_id = i_wip_entity_id
102 GROUP BY
103 cost_element_id;
104 BEGIN
105
106 -- The Algorithm for the Completion processing is as below.
107
108 /****************************************************************************
109 * *
110 * Is this a final comp ? ----> Flush all costs in Job! *
111 * || YES and STOP. *
112 * || NO *
113 * \/ *
114 * Dynamically derive (use cost type spec.) *
115 * comp. costs ?? ------------------------> Derive completion costs *
116 * YES || NO from cost type. *
117 * \/ and STOP. /\ *
118 * Is this a job with no || YES *
119 * Routing and NO Bill?? ------------------------------------ *
120 * || (use valuation cost type) *
121 * || NO *
122 * \/ *
123 * Derive completion costs *
124 * based on incurred costs of Job and STOP! *
125 * *
126 ****************************************************************************/
127
128
129 -- Set temp_relieved_value to zero in all tables.
130
131
132
133 stmt_num := 10;
134
135 UPDATE wip_req_operation_cost_details
136 SET temp_relieved_value = 0
137 WHERE
138 WIP_ENTITY_ID = i_wip_entity_id AND
139 ORGANIZATION_ID = i_org_id;
140
141 stmt_num := 20;
142
143 UPDATE WIP_OPERATION_RESOURCES
144 SET temp_relieved_value = 0
145 WHERE
146 WIP_ENTITY_ID = i_wip_entity_id AND
147 ORGANIZATION_ID = i_org_id;
148
149 stmt_num := 30;
150
151 UPDATE WIP_OPERATION_OVERHEADS
152 SET temp_relieved_value = 0
153 WHERE
154 WIP_ENTITY_ID = i_wip_entity_id AND
155 ORGANIZATION_ID = i_org_id;
156
157
158 stmt_num := 50;
159
160 select wac.completion_cost_source, nvl(wac.cost_type_id,-1),
161 wdj.start_quantity,nvl(wac.SYSTEM_OPTION_ID,-1)
162 into l_comp_cost_source,l_c_cost_type_id,l_lot_size,l_system_option_id
163 from
164 wip_accounting_classes wac,
165 wip_discrete_jobs wdj
166 where
167 wdj.wip_entity_id = i_wip_entity_id and
168 wdj.organization_id = i_org_id and
169 wdj.class_code = wac.class_code and
170 wdj.organization_id = wac.organization_id;
171
172
173 stmt_num := 80;
174
175 /*---------------------------------------------------
176 | If a non-std job has no bill or routing associated
177 | with it or if a std job has no bill or routing
178 | associated with it - these need to be treated
179 | specially.
180 |-----------------------------------------------------+*/
181
182 SELECT
183 decode(job_type,
184 1,decode(bom_revision,
185 NULL,decode(routing_revision,NULL,-1,1),
186 1),
187 3,decode(bom_reference_id,
188 NULL,decode(routing_reference_id,NULL,-1,1),
189 1),
190 1)
191 into
192 l_use_val_cost_type
193 from
194 WIP_DISCRETE_JOBS
195 WHERE
196 WIP_ENTITY_ID = i_wip_entity_id AND
197 ORGANIZATION_ID = i_org_id;
198
199 /* Bug 3504776 - the standard material requirements can be added manually for the job.
200 In this case, we want to derive the completion costs based on job costs */
201 if (l_use_val_cost_type = -1) then
202 /* Commented for Bug6734270.If there is a resource added manually
203 then also the l_use_val_cost_type should be 1
204 select count(*)
205 into l_qty_per_assy
206 from wip_requirement_operations
207 where wip_entity_id = i_wip_entity_id
208 and quantity_per_assembly <>0;
209 */
210 SELECT count(1)
211 INTO l_qty_per_assy
212 FROM dual
213 WHERE EXISTS ( SELECT NULL
214 FROM wip_requirement_operations wro
215 WHERE wro.wip_entity_id = i_wip_entity_id
216 AND wro.quantity_per_assembly <>0
217 UNION ALL
218 SELECT NULL
219 FROM wip_operation_resources wor
220 WHERE wor.wip_entity_id = i_wip_entity_id
221 AND wor.usage_rate_or_amount <>0
222 );
223
224
225 if (l_qty_per_assy > 0) then
226 l_use_val_cost_type := 1;
227 end if;
228 end if;
229
230 /*----------------------------------------------
231 | If the completions are costed by the system, we
232 | follow the system rules for earning material
233 | ovhd upon completion. If the completion is
234 | costed by the cost type then we will earn
235 | material overhead based on the costs in the cost type
236 | We need to figure out, for the given job, where the
237 | costs are coming from and hence how MO is to be
238 | earned. This info will passed back to the calling
239 | rotuine and used by the cost processor.
240 |--------------------------------------------------+*/
241
242 stmt_num := 90;
243
244 IF (l_comp_cost_source=1) THEN
245 i_movhd_cost_type_id:= i_res_cost_type_id;
246 ELSE i_movhd_cost_type_id:=l_c_cost_type_id;
247 END IF;
248
249
250 /*-------------------------------------------------------------+
251 | Initialize insert indicator : This will indicate to us if we
252 | need to insert a row into cst_txn_cost_details in the last
253 | step. There are cases where the algorithm inserts into
254 | cst_txn_cost_details without updating the detailed wip
255 | tables. In these cases
256 | we will directly insert into cst_txn_cost_details
257 | and so we need to skip the insert stmt in
258 | the end.
259 |--------------------------------------------------------------*/
260
261 l_insert_ind := 0;
262
263
264 /***********************************************************
265 * If this is a final completion ==> *
266 * Relieve all costs: *
267 * *
268 * CASE 1: Applicable if regular completions are costed *
269 * based on the system derived costs *
270 * and the job has a bill and/or routing *
271 * ------------------------------------------------------- *
272 * 1. If there is value to relieve - flush it out ans set *
273 * any available units to zero. *
274 * 2. If there is no value to relieve, but there are units *
275 * to relieve, flush units out. *
276 * 3. If there are no units, there is no value, so STOP. *
277 * *
278 * CASE 2 : Applicable if the regular completions are based *
279 * on the user specified costs (in a cost type) *
280 * OR *
281 * regular completions are SYSTEM derived and the *
282 * the job has no bill and no routing. *
283 * ------------------------------------------------------- *
284 * In this case, we do not maintain costs for resources, *
285 * ovhds, components in our tables. We go to wip_period_ba- *
286 * lances to figure out the residual costs in the Job. *
287 * *
288 * Note : *
289 * For automatic final completion, the condition is *
290 * satisfied if : *
291 * Completed_quantity + Scrapped quantity >= Job qtty *
292 * This is needed because for Jobs with no routings it is *
293 * possible to overcomplete in WIP, so the condition may be *
294 * satified multiple times. We will flush out costs for all *
295 * instances of the condition being satisfied. *
296 * We however, do not have to worry about computing this *
297 * since WIP code checks this condition before commiting a *
298 * a completion transaction and flags the completion as a *
299 * final completion. *
300 ***********************************************************/
301
302 --
303 -- Actual Logic begins here.
304 --
305
306 IF (i_final_comp_flag = 'Y' AND l_comp_cost_source = 1
307 AND l_use_val_cost_type <> -1) THEN
308
309 /*---------------------------------------------------------
310 | This is for Case 1 explained above ... |
311 | i.e. This is a FINAL COMP and regular completions are |
312 | costed by the system AND the job has a bill and/or |
313 | routing. |
314 ----------------------------------------------------------*/
315
316 /*-----------------------------------------------------
317 New Final Completion Algorithm
318 ------------------------------
319
320 IF the total InValue (Applied value - completion Value - Scrap Value)
321 of all the sub-elements (operation level) > 0
322
323 THEN Flush out all the cost in all the sub-elements
324 even though it MIGHT drive some of the InValue of
325 the sub-elements to -ve
326 ------------------------------------------------------*/
327
328 /*******************************************************
329 * Flush out PL costs and units ... *
330 * If we have components with a -ve qtty_per_assembly *
331 * Then the relieved_value will be -ve ==> Hence check *
332 * the Available Value to Relieve based on the *
333 * SIGN(quantity_per_assembly); However, if the *
334 * quantity_per_assembly = 0 then we assume a +ve value *
335 *******************************************************/
336
337 /* Bug 4246122: Included l_future_issued_qty for final
338 completion also.Update of WRO is now in loop */
339 FOR wro_rec IN c_wip_final_req_op LOOP
340
341 l_future_issued_qty := 0;
342
343 BEGIN
344 stmt_num := 100;
345 SELECT nvl(sum(primary_quantity),0)
346 INTO l_future_issued_qty
347 FROM mtl_material_transactions
348 WHERE organization_id = wro_rec.organization_id
349 AND inventory_item_id = wro_rec.inventory_item_id
350 AND operation_seq_num = wro_rec.operation_seq_num
351 AND transaction_source_id = wro_rec.wip_entity_id
352 AND ( (transaction_date > i_txn_date) or
353 (transaction_date = i_txn_date and transaction_id > i_trx_id) )
354 AND costed_flag IS NOT NULL
355 AND nvl(completion_transaction_id,-999) <>
356 ( Select nvl(completion_transaction_id,-999)
357 from mtl_material_transactions
358 where transaction_id = i_trx_id);
359 EXCEPTION
360 WHEN Others THEN
361 l_future_issued_qty := 0;
362 END;
363
364
365 stmt_num := 102;
366 UPDATE wip_requirement_operations w
367 SET
368 relieved_matl_completion_qty =
369 (SELECT
370 nvl(quantity_issued,0)-
371 nvl(relieved_matl_final_comp_qty,0)-
372 nvl(relieved_matl_scrap_quantity,0) +
373 l_future_issued_qty /* for bug 4246122 */
374 FROM wip_requirement_operations w2
375 WHERE
376 w.wip_entity_id = w2.wip_entity_id AND
377 w.organization_id = w2.organization_id AND
378 w.inventory_item_id = w2.inventory_item_id AND
379 w.operation_seq_num = w2.operation_seq_num
380 )
381 WHERE
382 w.wip_entity_id = i_wip_entity_id AND
383 w.organization_id = i_org_id AND
384 w.inventory_item_id = wro_rec.inventory_item_id AND /*added for bug 4246122 */
385 w.operation_seq_num = wro_rec.operation_seq_num /*added for bug 4246122 */
386 AND exists (
387 SELECT 'x'
388 FROM wip_req_operation_cost_details wrocd
389 WHERE wrocd.wip_entity_id = w.wip_entity_id
390 AND wrocd.organization_id = w.organization_id
391 GROUP BY
392 wrocd.wip_entity_id,
393 wrocd.organization_id,
394 wrocd.cost_element_id
395 HAVING sum(nvl(applied_matl_value,0) -
396 nvl(relieved_matl_completion_value,0) -
397 nvl(relieved_variance_value,0) -
398 nvl(relieved_matl_scrap_value,0)) >= 0
399 );
400
401 stmt_num := 105;
402 /* New final completion algorithm */
403 /* when net is -ve */
404 UPDATE wip_requirement_operations w
405 SET
406 relieved_matl_final_comp_qty =
407 (SELECT
408 nvl(quantity_issued,0)-
409 nvl(relieved_matl_completion_qty,0)-
410 nvl(relieved_matl_scrap_quantity,0) +
411 l_future_issued_qty /* for bug 4246122 */
412 FROM wip_requirement_operations w2
413 WHERE
414 w.wip_entity_id = w2.wip_entity_id AND
415 w.organization_id = w2.organization_id AND
416 w.inventory_item_id = w2.inventory_item_id AND
417 w.operation_seq_num = w2.operation_seq_num
418 )
419 WHERE
420 w.wip_entity_id = i_wip_entity_id AND
421 w.organization_id = i_org_id AND
422 w.inventory_item_id = wro_rec.inventory_item_id AND /* added for bug 4246122 */
423 w.operation_seq_num = wro_rec.operation_seq_num /*added for bug 4246122 */
424 AND not exists (
425 SELECT 'x'
426 FROM wip_req_operation_cost_details wrocd
427 WHERE wrocd.wip_entity_id = w.wip_entity_id
428 AND wrocd.organization_id = w.organization_id
429 GROUP BY
430 wrocd.wip_entity_id,
431 wrocd.organization_id,
432 wrocd.cost_element_id
433 HAVING sum(nvl(applied_matl_value,0) -
434 nvl(relieved_matl_completion_value,0) -
435 nvl(relieved_variance_value,0) -
436 nvl(relieved_matl_scrap_value,0)) >= 0
437 );
438 END LOOP ; /* Added for bug 4246122 */
439
440 /* 2937695 */
441 OPEN c_cost_elements;
442 LOOP
443 FETCH c_cost_elements INTO l_cost_element, l_job_value;
444 EXIT WHEN c_cost_elements%NOTFOUND;
445
446 IF l_job_value >= 0 THEN
447 stmt_num := 110;
448 UPDATE wip_req_operation_cost_details w
449 SET relieved_matl_completion_value =
450 nvl(applied_matl_value,0)-
451 nvl(relieved_variance_value,0)-
452 nvl(relieved_matl_scrap_value,0),
453 temp_relieved_value =
454 nvl(applied_matl_value,0)-
455 nvl(relieved_matl_completion_value,0)-
456 nvl(relieved_variance_value,0)-
457 nvl(relieved_matl_scrap_value,0)
458 WHERE w.wip_entity_id = i_wip_entity_id AND
459 w.cost_element_id = l_cost_element;
460 ELSE
461 stmt_num := 115;
462 UPDATE wip_req_operation_cost_details w
463 SET relieved_variance_value =
464 nvl(applied_matl_value,0)-
465 nvl(relieved_matl_completion_value,0)-
466 nvl(relieved_matl_scrap_value,0),
467 temp_relieved_value =
468 nvl(applied_matl_value,0)-
469 nvl(relieved_matl_completion_value,0)-
470 nvl(relieved_variance_value,0)-
471 nvl(relieved_matl_scrap_value,0)
472 WHERE w.wip_entity_id = i_wip_entity_id AND
473 w.cost_element_id = l_cost_element;
474 END IF;
475 END LOOP;
476
477 /*******************************************************
478 * flush out TL Resource costs and units ... *
479 *******************************************************/
480
481 stmt_num := 120;
482 UPDATE wip_operation_resources w
483 SET
484 (relieved_res_completion_units,
485 relieved_res_completion_value,
486 temp_relieved_value
487 ) = (
488 SELECT
489 ---
490 --- relieved_res_completion_units
491 ---
492 nvl(applied_resource_units,0)-
493 nvl(relieved_res_final_comp_units,0)-
494 nvl(relieved_res_scrap_units,0),
495 ---
496 --- relieved_res_completion_value
497 ---
498 nvl(applied_resource_value,0)-
499 nvl(relieved_variance_value,0)-
500 nvl(relieved_res_scrap_value,0),
501 ---
502 --- temp_relieved_value
503 ---
504 nvl(applied_resource_value,0)-
505 nvl(relieved_res_completion_value,0)-
506 nvl(relieved_variance_value,0)-
507 nvl(relieved_res_scrap_value,0)
508 FROM wip_operation_resources w2
509 WHERE
510 w.wip_entity_id = w2.wip_entity_id and
511 w.organization_id = w2.organization_id and
512 w.operation_seq_num = w2.operation_seq_num and
513 w.resource_seq_num = w2.resource_seq_num
514 )
515 WHERE w.wip_entity_id = i_wip_entity_id
516 AND w.organization_id = i_org_id
517 AND EXISTS
518 (SELECT null
519 FROM wip_operation_resources wor,
520 bom_resources br
521 WHERE wor.wip_entity_id = i_wip_entity_id
522 AND wor.organization_id = i_org_id
523 AND wor.organization_id = br.organization_id
524 AND wor.resource_id = br.resource_id
525 AND EXISTS
526 (SELECT null
527 FROM wip_operation_resources w3,
528 bom_resources br3
529 WHERE w3.wip_entity_id = i_wip_entity_id
530 AND w3.organization_id = i_org_id
531 AND w3.resource_seq_num = w.resource_seq_num
532 AND w3.operation_seq_num = w.operation_seq_num
533 AND w3.resource_id = br3.resource_id
534 AND w3.organization_id = br3.organization_id
535 AND br3.cost_element_id = br.cost_element_id)
536 GROUP BY br.cost_element_id
537 HAVING sum(nvl(applied_resource_value,0) -
538 nvl(relieved_res_completion_value,0) -
539 nvl(relieved_variance_value,0) -
540 nvl(relieved_res_scrap_value,0)) >= 0);
541
542 /*Bug #2518907 - Moved stmt 121 within if statement to prevent its execution
543 if stmt 120 updates wor rows*/
544 if (SQL%ROWCOUNT = 0) then
545 stmt_num := 121;
546 /* new final completion algorithm */
547 /* if Net is -ve, write to variance */
548 UPDATE wip_operation_resources w
549 SET
550 (relieved_res_final_comp_units,
551 relieved_variance_value,
552 temp_relieved_value
553 ) = (
554 SELECT
555 ---
556 --- relieved_res_final_comp_units
557 ---
558 nvl(applied_resource_units,0)-
559 nvl(relieved_res_completion_units,0)-
560 nvl(relieved_res_scrap_units,0),
561 ---
562 --- relieved_variance_value
563 ---
564 nvl(applied_resource_value,0)-
565 nvl(relieved_res_completion_value,0)-
566 nvl(relieved_res_scrap_value,0),
567 ---
568 --- temp_relieved_value
569 ---
570 nvl(applied_resource_value,0)-
571 nvl(relieved_res_completion_value,0)-
572 nvl(relieved_variance_value,0)-
573 nvl(relieved_res_scrap_value,0)
574 FROM wip_operation_resources w2
575 WHERE
576 w.wip_entity_id = w2.wip_entity_id and
577 w.organization_id = w2.organization_id and
578 w.operation_seq_num = w2.operation_seq_num and
579 w.resource_seq_num = w2.resource_seq_num
580 )
581 WHERE w.wip_entity_id = i_wip_entity_id
582 AND w.organization_id = i_org_id
583 AND EXISTS
584 (SELECT null
585 FROM wip_operation_resources wor,
586 bom_resources br
587 WHERE wor.wip_entity_id = i_wip_entity_id
588 AND wor.organization_id = i_org_id
589 AND wor.organization_id = br.organization_id
590 AND wor.resource_id = br.resource_id
591 AND EXISTS
592 (SELECT null
593 FROM wip_operation_resources w3,
594 bom_resources br3
595 WHERE w3.wip_entity_id = i_wip_entity_id
596 AND w3.organization_id = i_org_id
597 AND w3.resource_seq_num = w.resource_seq_num
598 AND w3.operation_seq_num = w.operation_seq_num
599 AND w3.resource_id = br3.resource_id
600 AND w3.organization_id = br3.organization_id
601 AND br3.cost_element_id = br.cost_element_id)
602 GROUP BY br.cost_element_id
603 HAVING sum(nvl(applied_resource_value,0) -
604 nvl(relieved_res_completion_value,0) -
605 nvl(relieved_variance_value,0) -
606 nvl(relieved_res_scrap_value,0)) < 0);
607 end if;
608
609 /*******************************************************
610 * Flush out TL Res based Overhead costs and units ... *
611 *******************************************************/
612
613 /*******************************************************
614 * Flush out TL Move based Overhead costs and units ... *
615 *******************************************************/
616
617 stmt_num := 132;
618
619 /* flush out TL Overhead move based resources based */
620 UPDATE wip_operation_overheads w
621 set (relieved_ovhd_completion_units,
622 relieved_ovhd_completion_value,
623 temp_relieved_value) =
624 (SELECT
625 ---
626 --- relieved_ovhd_completion_units
627 ---
628 nvl(applied_ovhd_units,0)-
629 nvl(relieved_ovhd_scrap_units,0) -
630 nvl(relieved_ovhd_final_comp_units,0),
631 ---
632 --- relieved_ovhd_completion_value
633 ---
634 nvl(applied_ovhd_value,0)-
635 nvl(relieved_ovhd_scrap_value,0) -
636 nvl(relieved_variance_value,0),
637 ---
638 --- temp_relieved_value
639 ---
640 nvl(applied_ovhd_value,0)-
641 nvl(relieved_ovhd_completion_value,0)-
642 nvl(relieved_variance_value,0)-
643 nvl(relieved_ovhd_scrap_value,0)
644 FROM
645 wip_operation_overheads w2
646 where
647 w.wip_entity_id = w2.wip_entity_id AND
648 w.organization_id = w2.organization_id AND
649 w.operation_seq_num = w2.operation_seq_num AND
650 w.resource_seq_num = w2.resource_seq_num AND
651 w.overhead_id = w2.overhead_id AND
652 w.basis_type = w2.basis_type
653 )
654 WHERE
655 w.wip_entity_id = i_wip_entity_id AND
656 w.organization_id = i_org_id
657 AND exists (
658 SELECT 'x'
659 FROM wip_operation_overheads woo
660 WHERE woo.wip_entity_id = w.wip_entity_id
661 AND woo.organization_id = w.organization_id
662 HAVING sum(nvl(applied_ovhd_value,0) -
663 nvl(relieved_ovhd_completion_value,0) -
664 nvl(relieved_variance_value,0) -
665 nvl(relieved_ovhd_scrap_value,0)) >= 0
666 );
667
668 /*Bug #2518907 - Moved stmt 133 within if statement to prevent its execution
669 if stmt 132 updates woo rows*/
670 if (SQL%ROWCOUNT = 0) then
671 stmt_num := 133;
672 /* New final completion algorithm */
673 /* for net value of overhead is -ve, write to variance */
674 UPDATE wip_operation_overheads w
675 set (relieved_ovhd_final_comp_units,
676 relieved_variance_value,
677 temp_relieved_value) =
678 (SELECT
679 ---
680 --- relieved_ovhd_final_comp_units
681 ---
682 nvl(applied_ovhd_units,0)-
683 nvl(relieved_ovhd_completion_units,0)-
684 nvl(relieved_ovhd_scrap_units,0),
685 ---
686 --- relieved_variance_value
687 ---
688 nvl(applied_ovhd_value,0)-
689 nvl(relieved_ovhd_completion_value,0)-
690 nvl(relieved_ovhd_scrap_value,0),
691 ---
692 --- temp_relieved_value
693 ---
694 nvl(applied_ovhd_value,0)-
695 nvl(relieved_ovhd_completion_value,0)-
696 nvl(relieved_variance_value,0)-
697 nvl(relieved_ovhd_scrap_value,0)
698 FROM
699 wip_operation_overheads w2
700 where
701 w.wip_entity_id = w2.wip_entity_id AND
702 w.organization_id = w2.organization_id AND
703 w.operation_seq_num = w2.operation_seq_num AND
704 w.resource_seq_num = w2.resource_seq_num AND
705 w.overhead_id = w2.overhead_id AND
706 w.basis_type = w2.basis_type
707 )
708 WHERE
709 w.wip_entity_id = i_wip_entity_id AND
710 w.organization_id = i_org_id
711 AND exists (
712 SELECT 'x'
713 FROM wip_operation_overheads woo
714 WHERE woo.wip_entity_id = w.wip_entity_id
715 AND woo.organization_id = w.organization_id
716 HAVING sum(nvl(applied_ovhd_value,0) -
717 nvl(relieved_ovhd_completion_value,0) -
718 nvl(relieved_variance_value,0) -
719 nvl(relieved_ovhd_scrap_value,0)) < 0
720 );
721 end if;
722
723
724 /*------------------------------------------------------+
725 | This is for the Case 2 - completion based on user spec
726 | cost type, and this is a final completion.
727 | We also need to include the case where the job has
728 | SYSTEM derived costs but has no bill/rtg ==> regular
729 | completions are costed from the valuation cost type and
730 | final completions are computed off wip_period_balances.
731 |-------------------------------------------------------*/
732
733
734 ELSIF (i_final_comp_flag = 'Y'
735 AND
736 ((l_comp_cost_source = 2 and l_c_cost_type_id > 0)
737 OR (l_comp_cost_source = 1 AND l_use_val_cost_type = -1))) THEN
738
739 /*----------------------------------------------------------
740 | Set the insert indicator to ensure that we skip the insert
741 | into cst_txn_cst_details at the end of the file.
742 | Then insert into mtl_cst_txn_cost_details in 2 passes,
743 | one for PL costs and one for TL costs ...
744 |-----------------------------------------------------------*/
745
746
747 /*-------------------------------------------------------
748 | TL MO should never be inserted - it will be earned by
749 | the Cost processor, so weed out cost_element_id = 2
750 |______________________________________________________*/
751
752 l_insert_ind := 1;
753
754 stmt_num := 135;
755 /* Bug 7346243: Removed Variance value from Available
756 Value for Final Completion */
757 INSERT INTO mtl_cst_txn_cost_details
758 (
759 TRANSACTION_ID,
760 ORGANIZATION_ID,
761 INVENTORY_ITEM_ID,
762 COST_ELEMENT_ID,
763 LEVEL_TYPE,
764 TRANSACTION_COST,
765 NEW_AVERAGE_COST,
766 PERCENTAGE_CHANGE,
767 VALUE_CHANGE,
768 LAST_UPDATE_DATE,
769 LAST_UPDATED_BY,
770 CREATION_DATE,
771 CREATED_BY,
772 LAST_UPDATE_LOGIN,
773 REQUEST_ID,
774 PROGRAM_APPLICATION_ID,
775 PROGRAM_ID,
776 PROGRAM_UPDATE_DATE)
777 SELECT
778 i_trx_id,
779 i_org_id,
780 i_inv_item_id,
781 cce.cost_element_id,
782 1,
783 decode(cce.cost_element_id,
784 1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
785 2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
786 3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
787 4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
788 5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0)))/i_txn_qty,
789 NULL,
790 NULL,
791 NULL,
792 SYSDATE,
793 i_user_id,
794 SYSDATE,
795 i_user_id,
796 i_login_id,
797 i_request_id,
798 i_prog_appl_id,
799 i_prog_id,
800 SYSDATE
801 FROM
802 CST_COST_ELEMENTS CCE,
803 WIP_PERIOD_BALANCES WPB
804 WHERE
805 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
806 WPB.ORGANIZATION_ID = I_ORG_ID AND
807 CCE.COST_ELEMENT_ID <> 2
808 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
809 HAVING
810 decode(cce.cost_element_id,
811 1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
812 2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
813 3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
814 4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
815 5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0))) > 0;
816
817 stmt_num := 150;
818
819 INSERT INTO mtl_cst_txn_cost_details
820 (
821 TRANSACTION_ID,
822 ORGANIZATION_ID,
823 INVENTORY_ITEM_ID,
824 COST_ELEMENT_ID,
825 LEVEL_TYPE,
826 TRANSACTION_COST,
827 NEW_AVERAGE_COST,
828 PERCENTAGE_CHANGE,
829 VALUE_CHANGE,
830 LAST_UPDATE_DATE,
831 LAST_UPDATED_BY,
832 CREATION_DATE,
833 CREATED_BY,
834 LAST_UPDATE_LOGIN,
835 REQUEST_ID,
836 PROGRAM_APPLICATION_ID,
837 PROGRAM_ID,
838 PROGRAM_UPDATE_DATE)
839 SELECT
840 i_trx_id,
841 i_org_id,
842 i_inv_item_id,
843 cce.cost_element_id,
844 2,
845 decode(cce.cost_element_id,
846 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
847 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
848 3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
849 4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
850 5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0)))/i_txn_qty,
851 NULL,
852 NULL,
853 NULL,
854 SYSDATE,
855 i_user_id,
856 SYSDATE,
857 i_user_id,
858 i_login_id,
859 i_request_id,
860 i_prog_appl_id,
861 i_prog_id,
862 SYSDATE
863 FROM
864 CST_COST_ELEMENTS CCE,
865 WIP_PERIOD_BALANCES WPB
866 WHERE
867 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
868 WPB.ORGANIZATION_ID = I_ORG_ID
869 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
870 HAVING
871 decode(cce.cost_element_id,
872 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
873 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
874 3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
875 4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
876 5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0))) > 0;
877
878
879 /*=====================================================================
880 Bug6734270: Added the following to flush out the values
881 From WROCD,WRO,WOR,WOO if any exists.(Requirements which have
882 quantity per assembly as 0 or usage rate as 0 can be added to
883 work order for assemblies having no BOM or Routing and those
884 will have WROCD,WOR etc which needs to be flushed as otherwise
885 in subsequent Scrap or Completion after adding a requirement
886 having quantity per assembly as <> 0 or usage rate as <> 0
887 extra value would be relieved.
888 ======================================================================*/
889
890
891 FOR wro_rec IN c_wip_final_req_op LOOP
892 l_future_issued_qty := 0;
893
894 BEGIN
895 stmt_num := 160;
896 SELECT nvl(sum(primary_quantity),0)
897 INTO l_future_issued_qty
898 FROM mtl_material_transactions
899 WHERE organization_id = wro_rec.organization_id
900 AND inventory_item_id = wro_rec.inventory_item_id
901 AND operation_seq_num = wro_rec.operation_seq_num
902 AND transaction_source_id = wro_rec.wip_entity_id
903 AND ( (transaction_date > i_txn_date) or
904 (transaction_date = i_txn_date and transaction_id > i_trx_id) )
905 AND costed_flag IS NOT NULL
906 AND nvl(completion_transaction_id,-999) <>
907 ( Select nvl(completion_transaction_id,-999)
908 from mtl_material_transactions
909 where transaction_id = i_trx_id);
910 EXCEPTION
911 WHEN Others THEN
912 l_future_issued_qty := 0;
913 END;
914
915 stmt_num :=170;
916 UPDATE wip_requirement_operations w
917 SET
918 relieved_matl_completion_qty =
919 (SELECT
920 nvl(quantity_issued,0)-
921 nvl(relieved_matl_final_comp_qty,0)-
922 nvl(relieved_matl_scrap_quantity,0)
923 + l_future_issued_qty
924 FROM wip_requirement_operations w2
925 WHERE
926 w.wip_entity_id = w2.wip_entity_id AND
927 w.organization_id = w2.organization_id AND
928 w.inventory_item_id = w2.inventory_item_id AND
929 w.operation_seq_num = w2.operation_seq_num
930 )
931 WHERE
932 w.wip_entity_id = i_wip_entity_id AND
933 w.organization_id = i_org_id AND
934 w.inventory_item_id = wro_rec.inventory_item_id AND
935 w.operation_seq_num = wro_rec.operation_seq_num
936 AND exists (
937 SELECT 'x'
938 FROM wip_req_operation_cost_details wrocd
939 WHERE wrocd.wip_entity_id = w.wip_entity_id
940 AND wrocd.organization_id = w.organization_id
941 GROUP BY
942 wrocd.wip_entity_id,
943 wrocd.organization_id,
944 wrocd.cost_element_id
945 HAVING sum(nvl(applied_matl_value,0) -
946 nvl(relieved_matl_completion_value,0) -
947 nvl(relieved_variance_value,0) -
948 nvl(relieved_matl_scrap_value,0)) >= 0
949 );
950
951 END LOOP ;
952
953
954 OPEN c_cost_elements;
955 LOOP
956 FETCH c_cost_elements INTO l_cost_element, l_job_value;
957 EXIT WHEN c_cost_elements%NOTFOUND;
958
959 IF l_job_value >= 0 THEN
960 stmt_num := 180;
961 UPDATE wip_req_operation_cost_details w
962 SET relieved_matl_completion_value =
963 nvl(applied_matl_value,0)-
964 nvl(relieved_variance_value,0)-
965 nvl(relieved_matl_scrap_value,0),
966 temp_relieved_value =
967 nvl(applied_matl_value,0)-
968 nvl(relieved_matl_completion_value,0)-
969 nvl(relieved_variance_value,0)-
970 nvl(relieved_matl_scrap_value,0)
971 WHERE w.wip_entity_id = i_wip_entity_id AND
972 w.cost_element_id = l_cost_element;
973 END IF;
974 END LOOP;
975
976 /*******************************************************
977 * flush out TL Resource costs and units ... *
978 *******************************************************/
979
980 stmt_num := 190;
981 UPDATE wip_operation_resources w
982 SET
983 (relieved_res_completion_units,
984 relieved_res_completion_value,
985 temp_relieved_value
986 ) = (
987 SELECT
988 ---
989 --- relieved_res_completion_units
990 ---
991 nvl(applied_resource_units,0)-
992 nvl(relieved_res_final_comp_units,0)-
993 nvl(relieved_res_scrap_units,0),
994 ---
995 --- relieved_res_completion_value
996 ---
997 nvl(applied_resource_value,0)-
998 nvl(relieved_variance_value,0)-
999 nvl(relieved_res_scrap_value,0),
1000 ---
1001 --- temp_relieved_value
1002 ---
1003 nvl(applied_resource_value,0)-
1004 nvl(relieved_res_completion_value,0)-
1005 nvl(relieved_variance_value,0)-
1006 nvl(relieved_res_scrap_value,0)
1007 FROM wip_operation_resources w2
1008 WHERE
1009 w.wip_entity_id = w2.wip_entity_id and
1010 w.organization_id = w2.organization_id and
1011 w.operation_seq_num = w2.operation_seq_num and
1012 w.resource_seq_num = w2.resource_seq_num
1013 )
1014 WHERE w.wip_entity_id = i_wip_entity_id
1015 AND w.organization_id = i_org_id
1016 AND EXISTS
1017 (SELECT null
1018 FROM wip_operation_resources wor,
1019 bom_resources br
1020 WHERE wor.wip_entity_id = i_wip_entity_id
1021 AND wor.organization_id = i_org_id
1022 AND wor.organization_id = br.organization_id
1023 AND wor.resource_id = br.resource_id
1024 AND EXISTS
1025 (SELECT null
1026 FROM wip_operation_resources w3,
1027 bom_resources br3
1028 WHERE w3.wip_entity_id = i_wip_entity_id
1029 AND w3.organization_id = i_org_id
1030 AND w3.resource_seq_num = w.resource_seq_num
1031 AND w3.operation_seq_num = w.operation_seq_num
1032 AND w3.resource_id = br3.resource_id
1033 AND w3.organization_id = br3.organization_id
1034 AND br3.cost_element_id = br.cost_element_id)
1035 GROUP BY br.cost_element_id
1036 HAVING sum(nvl(applied_resource_value,0) -
1037 nvl(relieved_res_completion_value,0) -
1038 nvl(relieved_variance_value,0) -
1039 nvl(relieved_res_scrap_value,0)) >= 0);
1040
1041 /*******************************************************
1042 * Flush out TL Res based Overhead costs and units ... *
1043 *******************************************************/
1044
1045 /*******************************************************
1046 * Flush out TL Move based Overhead costs and units ... *
1047 *******************************************************/
1048
1049 stmt_num := 200;
1050
1051 /* flush out TL Overhead move based resources based */
1052 UPDATE wip_operation_overheads w
1053 set (relieved_ovhd_completion_units,
1054 relieved_ovhd_completion_value,
1055 temp_relieved_value) =
1056 (SELECT
1057 ---
1058 --- relieved_ovhd_completion_units
1059 ---
1060 nvl(applied_ovhd_units,0)-
1061 nvl(relieved_ovhd_scrap_units,0) -
1062 nvl(relieved_ovhd_final_comp_units,0),
1063 ---
1064 --- relieved_ovhd_completion_value
1065 ---
1066 nvl(applied_ovhd_value,0)-
1067 nvl(relieved_ovhd_scrap_value,0) -
1068 nvl(relieved_variance_value,0),
1069 ---
1070 --- temp_relieved_value
1071 ---
1072 nvl(applied_ovhd_value,0)-
1073 nvl(relieved_ovhd_completion_value,0)-
1074 nvl(relieved_variance_value,0)-
1075 nvl(relieved_ovhd_scrap_value,0)
1076 FROM
1077 wip_operation_overheads w2
1078 where
1079 w.wip_entity_id = w2.wip_entity_id AND
1080 w.organization_id = w2.organization_id AND
1081 w.operation_seq_num = w2.operation_seq_num AND
1082 w.resource_seq_num = w2.resource_seq_num AND
1083 w.overhead_id = w2.overhead_id AND
1084 w.basis_type = w2.basis_type
1085 )
1086 WHERE
1087 w.wip_entity_id = i_wip_entity_id AND
1088 w.organization_id = i_org_id
1089 AND exists (
1090 SELECT 'x'
1091 FROM wip_operation_overheads woo
1092 WHERE woo.wip_entity_id = w.wip_entity_id
1093 AND woo.organization_id = w.organization_id
1094 HAVING sum(nvl(applied_ovhd_value,0) -
1095 nvl(relieved_ovhd_completion_value,0) -
1096 nvl(relieved_variance_value,0) -
1097 nvl(relieved_ovhd_scrap_value,0)) >= 0
1098 );
1099
1100
1101
1102
1103 /*---------------------------------------------------------
1104 | Completion from a User specified cost type |
1105 | ------------------------------------------ |
1106 | We could go to CICD or Cst_layer_cost_details if the |
1107 | entire org has one average cost, which will be the case |
1108 | for Average costing in a non-project environment. |
1109 | In a project system however, the average cost could be |
1110 | different within the same org. If the user chooses to |
1111 | use the average cost of a given project to cost comple- |
1112 | tions then CICD will not contain all the |
1113 | information. We have to go to CLCD in that case. |
1114 | |
1115 ---------------------------------------------------------*/
1116
1117 /*--------------------------------------------------------
1118 | This is a regular completion. The costs are specified by
1119 | the user and the cost type specified in a cost type which
1120 | is different from the average cost type.
1121 |---------------------------------------------------------*/
1122
1123
1124
1125 ELSIF(l_comp_cost_source = 2 AND l_c_cost_type_id >0 AND
1126 l_c_cost_type_id <> 2) THEN
1127
1128
1129 l_insert_ind := 1;
1130
1131
1132 stmt_num := 220;
1133
1134 INSERT INTO mtl_cst_txn_cost_details
1135 (
1136 TRANSACTION_ID,
1137 ORGANIZATION_ID,
1138 INVENTORY_ITEM_ID,
1139 COST_ELEMENT_ID,
1140 LEVEL_TYPE,
1141 TRANSACTION_COST,
1142 NEW_AVERAGE_COST,
1143 PERCENTAGE_CHANGE,
1144 VALUE_CHANGE,
1145 LAST_UPDATE_DATE,
1146 LAST_UPDATED_BY,
1147 CREATION_DATE,
1148 CREATED_BY,
1149 LAST_UPDATE_LOGIN,
1150 REQUEST_ID,
1151 PROGRAM_APPLICATION_ID,
1152 PROGRAM_ID,
1153 PROGRAM_UPDATE_DATE)
1154 SELECT
1155 i_trx_id,
1156 i_org_id,
1157 i_inv_item_id,
1158 COST_ELEMENT_ID,
1159 LEVEL_TYPE,
1160 SUM(ITEM_COST),
1161 NULL,
1162 NULL,
1163 NULL,
1164 SYSDATE,
1165 i_user_id,
1166 SYSDATE,
1167 i_user_id,
1168 i_login_id,
1169 i_request_id,
1170 i_prog_appl_id,
1171 i_prog_id,
1172 SYSDATE
1173 FROM
1174 CST_ITEM_COST_DETAILS
1175 WHERE
1176 INVENTORY_ITEM_ID = I_INV_ITEM_ID AND
1177 ORGANIZATION_ID = I_ORG_ID AND
1178 COST_TYPE_ID = L_C_COST_TYPE_ID AND
1179 NOT (COST_ELEMENT_ID = 2 AND
1180 LEVEL_TYPE = 1)
1181 GROUP BY COST_ELEMENT_ID,LEVEL_TYPE
1182 HAVING SUM(ITEM_COST) <> 0;
1183
1184
1185
1186 ELSIF((l_comp_cost_source = 2 AND l_c_cost_type_id = i_cost_type_id)
1187 OR
1188 (l_comp_cost_source = 1 AND l_use_val_cost_type = -1)) THEN
1189
1190 /*--------------------------------------------------------
1191 | This is for the case where :
1192 |
1193 | Costs are obtained from user cost type and the user cost
1194 | type happens to be the valuation cost type
1195 | OR
1196 | Costs are supposed to be SYSTEM derived but the job has no
1197 | bill/routing (==> we use the valuation cost type).
1198 |----------------------------------------------------------*/
1199
1200
1201 l_insert_ind := 1;
1202
1203 stmt_num := 230;
1204
1205 INSERT INTO mtl_cst_txn_cost_details
1206 (
1207 TRANSACTION_ID,
1208 ORGANIZATION_ID,
1209 INVENTORY_ITEM_ID,
1210 COST_ELEMENT_ID,
1211 LEVEL_TYPE,
1212 TRANSACTION_COST,
1213 NEW_AVERAGE_COST,
1214 PERCENTAGE_CHANGE,
1215 VALUE_CHANGE,
1216 LAST_UPDATE_DATE,
1217 LAST_UPDATED_BY,
1218 CREATION_DATE,
1219 CREATED_BY,
1220 LAST_UPDATE_LOGIN,
1221 REQUEST_ID,
1222 PROGRAM_APPLICATION_ID,
1223 PROGRAM_ID,
1224 PROGRAM_UPDATE_DATE)
1225 SELECT
1226 i_trx_id,
1227 i_org_id,
1228 i_inv_item_id,
1229 COST_ELEMENT_ID,
1230 LEVEL_TYPE,
1231 ITEM_COST,
1232 NULL,
1233 NULL,
1234 NULL,
1235 SYSDATE,
1236 i_user_id,
1237 SYSDATE,
1238 i_user_id,
1239 i_login_id,
1240 i_request_id,
1241 i_prog_appl_id,
1242 i_prog_id,
1243 SYSDATE
1244 FROM
1245 CST_LAYER_COST_DETAILS
1246 WHERE
1247 LAYER_ID = i_layer_id AND
1248 NOT (COST_ELEMENT_ID = 2 AND
1249 LEVEL_TYPE = 1);
1250
1251 ELSE
1252
1253 /*************************************************************
1254 * Derive the Comp costs dynamically based on current costs *
1255 * in the JOb ... *
1256 *************************************************************/
1257
1258 -- If no material has been issued to the Job, there will be no
1259 -- rows in WROCD for the components. However, the cost relief is
1260 -- supposed to be based on the current average cost of the
1261 -- components. Therefore insert rows for all components.
1262 -- If some components have been issued, they should not be inserted
1263
1264 stmt_num := 240;
1265
1266 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
1267 (WIP_ENTITY_ID,
1268 OPERATION_SEQ_NUM,
1269 ORGANIZATION_ID,
1270 INVENTORY_ITEM_ID,
1271 COST_ELEMENT_ID,
1272 APPLIED_MATL_VALUE,
1273 LAST_UPDATED_BY,
1274 LAST_UPDATE_DATE,
1275 CREATION_DATE,
1276 CREATED_BY,
1277 LAST_UPDATE_LOGIN,
1278 REQUEST_ID,
1279 PROGRAM_APPLICATION_ID,
1280 PROGRAM_ID,
1281 PROGRAM_UPDATE_DATE)
1282 SELECT
1283 i_wip_entity_id,
1284 wro.operation_seq_num,
1285 i_org_id,
1286 wro.inventory_item_id,
1287 clcd.cost_element_id,
1288 0,
1289 i_user_id,
1290 SYSDATE,
1291 SYSDATE,
1292 i_user_id,
1293 i_login_id,
1294 i_request_id,
1295 i_prog_id,
1296 i_prog_appl_id,
1297 SYSDATE
1298 from
1299 WIP_REQUIREMENT_OPERATIONS WRO,
1300 CST_LAYER_COST_DETAILS CLCD,
1301 CST_QUANTITY_LAYERS CQL
1302 WHERE
1303 WRO.WIP_ENTITY_ID = i_wip_entity_id AND
1304 WRO.INVENTORY_ITEM_ID = CQL.INVENTORY_ITEM_ID AND
1305 WRO.ORGANIZATION_ID = CQL.ORGANIZATION_ID AND
1306 CQL.COST_GROUP_ID = I_COST_GROUP_ID AND
1307 CQL.LAYER_ID = CLCD.LAYER_ID AND
1308 not EXISTS
1309 (SELECT
1310 'X'
1311 FROM
1312 WIP_REQ_OPERATION_COST_DETAILS WROCD
1313 WHERE
1314 WROCD.WIP_ENTITY_ID = i_wip_entity_id AND
1315 WROCD.INVENTORY_ITEM_ID= WRO.INVENTORY_ITEM_ID AND
1316 WROCD.OPERATION_SEQ_NUM= WRO.OPERATION_SEQ_NUM AND
1317 WROCD.COST_ELEMENT_ID = CLCD.COST_ELEMENT_ID)
1318 GROUP BY CLCD.COST_ELEMENT_ID,wro.operation_seq_num,
1319 wro.inventory_item_id;
1320
1321 stmt_num := 245;
1322
1323 /* Get the value of Include Component yield flag, which will determine
1324 whether to include or not component yield factor in quantity per assembly*/
1325 SELECT nvl(include_component_yield, 1)
1326 INTO l_include_comp_yield
1327 FROM wip_parameters
1328 WHERE organization_id = i_org_id;
1329
1330 stmt_num := 250;
1331
1332 /* fix for bug 2158763 */
1333 FOR wro_rec in c_wip_req_op LOOP
1334 BEGIN
1335
1336 l_future_issued_qty := 0;
1337
1338 /* IF wro_rec.wip_supply_type = 2 THEN -- commented as bugfix
1339 to allow component issue transactions for both, PUSH and PULL, components
1340 to be considered for calculation of l_future_issued_qty */
1341
1342 BEGIN
1343 SELECT nvl(sum(primary_quantity),0)
1344 INTO l_future_issued_qty
1345 FROM mtl_material_transactions
1346 WHERE organization_id = wro_rec.organization_id
1347 AND inventory_item_id = wro_rec.inventory_item_id
1348 AND operation_seq_num = wro_rec.operation_seq_num
1349 AND transaction_source_id = wro_rec.wip_entity_id
1350 /* Bug 3715567: use txn_date to determine the future issued qty */
1351 AND ( (transaction_date > i_txn_date) or
1352 (transaction_date = i_txn_date and transaction_id > i_trx_id) )
1353 AND costed_flag IS NOT NULL
1354 /* Applied nvl for bug 2391936 */
1355 AND nvl(completion_transaction_id,-999) <>
1356 ( Select nvl(completion_transaction_id,-999)
1357 from mtl_material_transactions
1358 where transaction_id = i_trx_id);
1359 EXCEPTION
1360 WHEN Others THEN
1361 l_future_issued_qty := 0;
1362 END;
1363
1364 /* END IF; -- commented as bug fix 2391936 */
1365
1366 UPDATE WIP_REQ_OPERATION_COST_DETAILS w1
1367 SET (temp_relieved_value,
1368 relieved_matl_completion_value) =
1369 (SELECT
1370 decode(SIGN(nvl(wro.quantity_issued,0)-
1371 nvl(wro.relieved_matl_completion_qty,0)-
1372 nvl(wro.relieved_matl_final_comp_qty,0)-
1373 nvl(wro.relieved_matl_scrap_quantity,0)-
1374 /* LBM project Changes */
1375 i_txn_qty*(decode(wro.basis_type, 2,
1376 wro.quantity_per_assembly/l_lot_size,
1377 wro.quantity_per_assembly)/
1378 decode(l_include_comp_yield,
1379 1, nvl(wro.component_yield_factor,1),
1380 1)) + l_future_issued_qty), /* Added l_future_issued_qty for bug 4259782 */
1381 SIGN(wro.quantity_per_assembly),
1382 /* LBM project Changes */
1383 i_txn_qty*(decode(wro.basis_type, 2,
1384 wro.quantity_per_assembly/l_lot_size,
1385 wro.quantity_per_assembly)/
1386 decode(l_include_comp_yield,
1387 1, nvl(wro.component_yield_factor,1),
1388 1))*
1389 decode(SIGN(nvl(applied_matl_value,0)-
1390 nvl(relieved_matl_completion_value,0)-
1391 nvl(relieved_variance_value,0)-
1392 nvl(relieved_matl_scrap_value,0)),
1393 /* Bug 3479419: AVTR = 0 Start*/
1394 0, 0,
1395 /* Bug 3479419: AVTR = 0 End*/
1396 SIGN(wro.quantity_per_assembly),
1397 ( nvl(applied_matl_value,0)-
1398 nvl(relieved_matl_completion_value,0)-
1399 nvl(relieved_variance_value,0)-
1400 nvl(relieved_matl_scrap_value,0))/
1401 (wro.quantity_issued-
1402 nvl(wro.relieved_matl_completion_qty,0)-
1403 nvl(wro.relieved_matl_final_comp_qty,0)-
1404 nvl(wro.relieved_matl_scrap_quantity,0)+
1405 l_future_issued_qty), /* Fix for bug 2158763 */
1406 nvl(decode(cost_element_id,
1407 1,cql.material_cost,
1408 2,cql.material_overhead_cost,
1409 3,cql.resource_cost,
1410 4,cql.outside_processing_cost,
1411 5,cql.overhead_cost),0)),
1412 0,
1413 decode(SIGN(nvl(applied_matl_value,0)-
1414 nvl(relieved_matl_completion_value,0)-
1415 nvl(relieved_variance_value,0)-
1416 nvl(relieved_matl_scrap_value,0)),
1417 /* Bug 3479419: AVTR = 0 Start*/
1418 0, 0,
1419 /* Bug 3479419: AVTR = 0 End*/
1420 SIGN(wro.quantity_per_assembly),
1421 (nvl(applied_matl_value,0)-
1422 nvl(relieved_matl_completion_value,0)-
1423 nvl(relieved_variance_value,0)-
1424 nvl(relieved_matl_scrap_value,0)),
1425 /* LBM project Changes */
1426 i_txn_qty*(decode(wro.basis_type, 2,
1427 wro.quantity_per_assembly/l_lot_size,
1428 wro.quantity_per_assembly)/
1429 decode(l_include_comp_yield,
1430 1, nvl(wro.component_yield_factor,1),
1431 1))*
1432 nvl(decode(cost_element_id,
1433 1,cql.material_cost,
1434 2,cql.material_overhead_cost,
1435 3,cql.resource_cost,
1436 4,cql.outside_processing_cost,
1437 5,cql.overhead_cost),0)),
1438 -1*SIGN(wro.quantity_per_assembly),
1439 decode(SIGN(nvl(applied_matl_value,0)-
1440 nvl(relieved_matl_completion_value,0)-
1441 nvl(relieved_variance_value,0)-
1442 nvl(relieved_matl_scrap_value,0)),
1443 /* Bug 3479419: AVTR = 0 Start*/
1444 /* LBM project Changes */
1445 0, (i_txn_qty*(decode(wro.basis_type, 2,
1446 wro.quantity_per_assembly/l_lot_size,
1447 wro.quantity_per_assembly)/
1448 decode(l_include_comp_yield,
1449 1, nvl(wro.component_yield_factor,1),
1450 1))-
1451 decode(sign(wro.quantity_issued -
1452 nvl(wro.relieved_matl_completion_qty,0) -
1453 nvl(wro.relieved_matl_final_comp_qty,0) -
1454 nvl(wro.relieved_matl_scrap_quantity,0) +
1455 l_future_issued_qty),
1456 SIGN(wro.quantity_per_assembly), (wro.quantity_issued -
1457 nvl(wro.relieved_matl_completion_qty,0) -
1458 nvl(wro.relieved_matl_final_comp_qty,0) -
1459 nvl(wro.relieved_matl_scrap_quantity,0) +
1460 l_future_issued_qty),
1461 0)
1462 )* /* Added l_future_issued_qty for bug 4259782 */
1463 nvl(decode(cost_element_id,
1464 1,cql.material_cost,
1465 2,cql.material_overhead_cost,
1466 3,cql.resource_cost,
1467 4,cql.outside_processing_cost,
1468 5,cql.overhead_cost),0),
1469 /* Bug 3479419: AVTR = 0 End*/
1470 SIGN(wro.quantity_per_assembly),
1471 decode(sign(wro.quantity_issued -
1472 nvl(wro.relieved_matl_completion_qty,0) -
1473 nvl(wro.relieved_matl_final_comp_qty,0) -
1474 nvl(wro.relieved_matl_scrap_quantity,0) +
1475 l_future_issued_qty),
1476 sign(wro.quantity_per_assembly), (nvl(applied_matl_value,0)-
1477 nvl(relieved_matl_completion_value,0)-
1478 nvl(relieved_variance_value,0)-
1479 nvl(relieved_matl_scrap_value,0)+
1480 /* LBM project Changes */
1481 (i_txn_qty*(decode(wro.basis_type, 2,
1482 wro.quantity_per_assembly/l_lot_size,
1483 wro.quantity_per_assembly)/
1484 decode(l_include_comp_yield,
1485 1, nvl(wro.component_yield_factor,1),
1486 1))-
1487 (wro.quantity_issued -
1488 nvl(wro.relieved_matl_completion_qty,0) -
1489 nvl(wro.relieved_matl_final_comp_qty,0) -
1490 nvl(wro.relieved_matl_scrap_quantity,0) +
1491 l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
1492 nvl(decode(cost_element_id,
1493 1,cql.material_cost,
1494 2,cql.material_overhead_cost,
1495 3,cql.resource_cost,
1496 4,cql.outside_processing_cost,
1497 5,cql.overhead_cost),0)),
1498 (i_txn_qty*(decode(wro.basis_type, 2,
1499 wro.quantity_per_assembly/l_lot_size,
1500 wro.quantity_per_assembly)/
1501 decode(l_include_comp_yield,
1502 1, nvl(wro.component_yield_factor,1),
1503 1))*
1504 nvl(decode(cost_element_id,
1505 1,cql.material_cost,
1506 2,cql.material_overhead_cost,
1507 3,cql.resource_cost,
1508 4,cql.outside_processing_cost,
1509 5,cql.overhead_cost),0)
1510 )),
1511 /* LBM project Changes */
1512 i_txn_qty*(decode(wro.basis_type, 2,
1513 wro.quantity_per_assembly/l_lot_size,
1514 wro.quantity_per_assembly)/
1515 decode(l_include_comp_yield,
1516 1, nvl(wro.component_yield_factor,1),
1517 1))*
1518 nvl(decode(cost_element_id,
1519 1,cql.material_cost,
1520 2,cql.material_overhead_cost,
1521 3,cql.resource_cost,
1522 4,cql.outside_processing_cost,
1523 5,cql.overhead_cost),0))),
1524
1525 nvl(w1.relieved_matl_completion_value,0)+
1526 decode(SIGN(nvl(wro.quantity_issued,0)-
1527 nvl(wro.relieved_matl_completion_qty,0)-
1528 nvl(wro.relieved_matl_final_comp_qty,0)-
1529 nvl(wro.relieved_matl_scrap_quantity,0)-
1530 /* LBM project Changes */
1531 i_txn_qty*(decode(wro.basis_type, 2,
1532 wro.quantity_per_assembly/l_lot_size,
1533 wro.quantity_per_assembly)/
1534 decode(l_include_comp_yield,
1535 1, nvl(wro.component_yield_factor,1),
1536 1)) + l_future_issued_qty), /* Added l_future_issued_qty for bug 4259782 */
1537 SIGN(wro.quantity_per_assembly),
1538 /* LBM project Changes */
1539 i_txn_qty*(decode(wro.basis_type, 2,
1540 wro.quantity_per_assembly/l_lot_size,
1541 wro.quantity_per_assembly)/
1542 decode(l_include_comp_yield,
1543 1, nvl(wro.component_yield_factor,1),
1544 1))*
1545 decode(SIGN(nvl(applied_matl_value,0)-
1546 nvl(relieved_matl_completion_value,0)-
1547 nvl(relieved_variance_value,0)-
1548 nvl(relieved_matl_scrap_value,0)),
1549 /* Bug 3479419: AVTR = 0 Start*/
1550 0, 0,
1551 /* Bug 3479419: AVTR = 0 End*/
1552 SIGN(wro.quantity_per_assembly),
1553 ( nvl(applied_matl_value,0)-
1554 nvl(relieved_matl_completion_value,0)-
1555 nvl(relieved_variance_value,0)-
1556 nvl(relieved_matl_scrap_value,0))/
1557 (wro.quantity_issued-
1558 nvl(wro.relieved_matl_completion_qty,0)-
1559 nvl(wro.relieved_matl_final_comp_qty,0)-
1560 nvl(wro.relieved_matl_scrap_quantity,0)+
1561 l_future_issued_qty), /* Fix for bug 2158763 */
1562 nvl(decode(cost_element_id,
1563 1,cql.material_cost,
1564 2,cql.material_overhead_cost,
1565 3,cql.resource_cost,
1566 4,cql.outside_processing_cost,
1567 5,cql.overhead_cost),0)),
1568 0,
1569 decode(SIGN(nvl(applied_matl_value,0)-
1570 nvl(relieved_matl_completion_value,0)-
1571 nvl(relieved_variance_value,0)-
1572 nvl(relieved_matl_scrap_value,0)),
1573 /* Bug 3479419: AVTR = 0 Start*/
1574 0, 0,
1575 /* Bug 3479419: AVTR = 0 End*/
1576 SIGN(wro.quantity_per_assembly),
1577 (nvl(applied_matl_value,0)-
1578 nvl(relieved_matl_completion_value,0)-
1579 nvl(relieved_variance_value,0)-
1580 nvl(relieved_matl_scrap_value,0)),
1581 /* LBM project Changes */
1582 i_txn_qty*(decode(wro.basis_type, 2,
1583 wro.quantity_per_assembly/l_lot_size,
1584 wro.quantity_per_assembly)/
1585 decode(l_include_comp_yield,
1586 1, nvl(wro.component_yield_factor,1),
1587 1))*
1588 nvl(decode(cost_element_id,
1589 1,cql.material_cost,
1590 2,cql.material_overhead_cost,
1591 3,cql.resource_cost,
1592 4,cql.outside_processing_cost,
1593 5,cql.overhead_cost),0)),
1594 -1*SIGN(wro.quantity_per_assembly),
1595 decode(SIGN(nvl(applied_matl_value,0)-
1596 nvl(relieved_matl_completion_value,0)-
1597 nvl(relieved_variance_value,0)-
1598 nvl(relieved_matl_scrap_value,0)),
1599 /* Bug 3479419: AVTR = 0 Start*/
1600 /* LBM project Changes */
1601 0, (i_txn_qty*(decode(wro.basis_type, 2,
1602 wro.quantity_per_assembly/l_lot_size,
1603 wro.quantity_per_assembly)/
1604 decode(l_include_comp_yield,
1605 1, nvl(wro.component_yield_factor,1),
1606 1))-
1607 decode(sign(wro.quantity_issued -
1608 nvl(wro.relieved_matl_completion_qty,0) -
1609 nvl(wro.relieved_matl_final_comp_qty,0) -
1610 nvl(wro.relieved_matl_scrap_quantity,0) +
1611 l_future_issued_qty),
1612 SIGN(wro.quantity_per_assembly), (wro.quantity_issued -
1613 nvl(wro.relieved_matl_completion_qty,0) -
1614 nvl(wro.relieved_matl_final_comp_qty,0) -
1615 nvl(wro.relieved_matl_scrap_quantity,0) +
1616 l_future_issued_qty),
1617 0)
1618 )* /* Added l_future_issued_qty for bug 4259782 */
1619 nvl(decode(cost_element_id,
1620 1,cql.material_cost,
1621 2,cql.material_overhead_cost,
1622 3,cql.resource_cost,
1623 4,cql.outside_processing_cost,
1624 5,cql.overhead_cost),0),
1625 /* Bug 3479419: AVTR = 0 End*/
1626 SIGN(wro.quantity_per_assembly),
1627 decode(sign(wro.quantity_issued -
1628 nvl(wro.relieved_matl_completion_qty,0) -
1629 nvl(wro.relieved_matl_final_comp_qty,0) -
1630 nvl(wro.relieved_matl_scrap_quantity,0) +
1631 l_future_issued_qty),
1632 sign(wro.quantity_per_assembly), (nvl(applied_matl_value,0)-
1633 nvl(relieved_matl_completion_value,0)-
1634 nvl(relieved_variance_value,0)-
1635 nvl(relieved_matl_scrap_value,0)+
1636 /* LBM project Changes */
1637 (i_txn_qty*(decode(wro.basis_type, 2,
1638 wro.quantity_per_assembly/l_lot_size,
1639 wro.quantity_per_assembly)/
1640 decode(l_include_comp_yield,
1641 1, nvl(wro.component_yield_factor,1),
1642 1))-
1643 (wro.quantity_issued -
1644 nvl(wro.relieved_matl_completion_qty,0) -
1645 nvl(wro.relieved_matl_final_comp_qty,0) -
1646 nvl(wro.relieved_matl_scrap_quantity,0) +
1647 l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
1648 nvl(decode(cost_element_id,
1649 1,cql.material_cost,
1650 2,cql.material_overhead_cost,
1651 3,cql.resource_cost,
1652 4,cql.outside_processing_cost,
1653 5,cql.overhead_cost),0)),
1654 (i_txn_qty*(decode(wro.basis_type, 2,
1655 wro.quantity_per_assembly/l_lot_size,
1656 wro.quantity_per_assembly)/
1657 decode(l_include_comp_yield,
1658 1, nvl(wro.component_yield_factor,1),
1659 1))*
1660 nvl(decode(cost_element_id,
1661 1,cql.material_cost,
1662 2,cql.material_overhead_cost,
1663 3,cql.resource_cost,
1664 4,cql.outside_processing_cost,
1665 5,cql.overhead_cost),0)
1666 )),
1667 /* LBM project Changes */
1668 i_txn_qty*(decode(wro.basis_type, 2,
1669 wro.quantity_per_assembly/l_lot_size,
1670 wro.quantity_per_assembly)/
1671 decode(l_include_comp_yield,
1672 1, nvl(wro.component_yield_factor,1),
1673 1))*
1674 nvl(decode(cost_element_id,
1675 1,cql.material_cost,
1676 2,cql.material_overhead_cost,
1677 3,cql.resource_cost,
1678 4,cql.outside_processing_cost,
1679 5,cql.overhead_cost),0)))
1680 FROM
1681 wip_req_operation_cost_details w2,
1682 wip_requirement_operations wro,
1683 cst_quantity_layers cql
1684 WHERE
1685 w2.wip_entity_id = w1.wip_entity_id AND
1686 w2.organization_id = w1.organization_id AND
1687 w2.inventory_item_id = w1.inventory_item_id AND
1688 w2.operation_seq_num = w1.operation_seq_num AND
1689 w2.cost_element_id = w1.cost_element_id AND
1690 w2.wip_entity_id = wro.wip_entity_id AND
1691 w2.organization_id = wro.organization_id AND
1692 w2.inventory_item_id = wro.inventory_item_id AND
1693 w2.operation_seq_num = wro.operation_seq_num AND
1694 i_cost_group_id = cql.cost_group_id(+) AND
1695 wro.inventory_item_id = cql.inventory_item_id(+) AND
1696 wro.organization_id = cql.organization_id(+))
1697 WHERE
1698 w1.wip_entity_id = wro_rec.wip_entity_id AND
1699 w1.organization_id = wro_rec.organization_id AND
1700 w1.inventory_item_id = wro_rec.inventory_item_id AND
1701 w1.operation_seq_num = wro_rec.operation_seq_num;
1702 END;
1703 END LOOP;
1704
1705 /*---------------------------------------------------
1706 | Qty must be updated after value ...
1707 |--------------------------------------------------*/
1708
1709
1710 stmt_num := 270;
1711
1712 UPDATE wip_requirement_operations w1
1713 SET
1714 relieved_matl_completion_qty =
1715 (SELECT
1716 nvl(w1.relieved_matl_completion_qty,0) +
1717 /* LBM project Changes */
1718 i_txn_qty*(decode(basis_type, 2,
1719 quantity_per_assembly/l_lot_size,
1720 quantity_per_assembly)/
1721 decode(l_include_comp_yield,
1722 1, nvl(component_yield_factor,1),
1723 1))
1724 FROM
1725 wip_requirement_operations w2
1726 WHERE
1727 w1.wip_entity_id = w2.wip_entity_id AND
1728 w1.organization_id = w2.organization_id AND
1729 w1.inventory_item_id = w2.inventory_item_id AND
1730 w1.operation_seq_num = w2.operation_seq_num)
1731 WHERE
1732 --
1733 -- Exclude bulk, supplier, phantom
1734 --
1735 w1.wip_supply_type not in (4,5,6) AND
1736 w1.wip_entity_id = i_wip_entity_id AND
1737 w1.organization_id = i_org_id AND
1738 w1.quantity_per_assembly <> 0;
1739
1740
1741 -- /******************************************************
1742 -- * Relieve This Level Resource costs/units from WIP ...*
1743 -- ******************************************************/
1744
1745 IF (l_system_option_id = 1) THEN
1746
1747 -- If we use the actual resource option, then use the snapshot for
1748 -- both resources and overheads.
1749
1750 stmt_num := 290;
1751
1752 UPDATE wip_operation_resources w1
1753 SET
1754 (relieved_res_completion_units,
1755 temp_relieved_value,
1756 relieved_res_completion_value) =
1757 (SELECT
1758 nvl(w1.relieved_res_completion_units,0) +
1759 decode(sign(applied_resource_units -
1760 nvl(relieved_res_completion_units,0)-
1761 nvl(relieved_res_final_comp_units,0)-
1762 nvl(relieved_res_scrap_units,0)),
1763 1,
1764 (applied_resource_units -
1765 nvl(relieved_res_completion_units,0)-
1766 nvl(relieved_res_final_comp_units,0)-
1767 nvl(relieved_res_scrap_units,0))*
1768 --
1769 -- new to solve divided by zero and over relieved
1770 -- when txn_qty/completed - prior_completion - prior_scrap
1771 -- is greater than or equal to one, set it to one
1772 -- ie. flush out 1*value remain in the job 1/30/98
1773 --
1774 decode(sign(i_txn_qty - (cocd.quantity_completed -
1775 nvl(prior_completion_quantity,0) -
1776 nvl(prior_scrap_quantity,0))),
1777 -1,i_txn_qty/(cocd.quantity_completed -
1778 nvl(prior_completion_quantity,0) -
1779 nvl(prior_scrap_quantity,0)),
1780 1),
1781 0),
1782 decode(sign(applied_resource_value -
1783 nvl(relieved_res_completion_value,0)-
1784 nvl(relieved_variance_value,0)-
1785 nvl(relieved_res_scrap_value,0)),
1786 1,
1787 (applied_resource_value -
1788 nvl(relieved_res_completion_value,0)-
1789 nvl(relieved_variance_value,0)-
1790 nvl(relieved_res_scrap_value,0))*
1791 --
1792 -- new to solve divided by zero and over relieved
1793 --
1794 decode(sign(i_txn_qty - (cocd.quantity_completed -
1795 nvl(prior_completion_quantity,0) -
1796 nvl(prior_scrap_quantity,0))),
1797 -1,i_txn_qty/(cocd.quantity_completed -
1798 nvl(prior_completion_quantity,0) -
1799 nvl(prior_scrap_quantity,0)),
1800 1),
1801 0),
1802 nvl(w1.relieved_res_completion_value,0) +
1803 decode(sign(applied_resource_value -
1804 nvl(relieved_res_completion_value,0)-
1805 nvl(relieved_variance_value,0)-
1806 nvl(relieved_res_scrap_value,0)),
1807 1,
1808 (applied_resource_value -
1809 nvl(relieved_res_completion_value,0)-
1810 nvl(relieved_variance_value,0)-
1811 nvl(relieved_res_scrap_value,0))*
1812 --
1813 -- new to solve divided by zero and over relieved
1814 --
1815 decode(sign(i_txn_qty - (cocd.quantity_completed -
1816 nvl(prior_completion_quantity,0) -
1817 nvl(prior_scrap_quantity,0))),
1818 -1,i_txn_qty/(cocd.quantity_completed -
1819 nvl(prior_completion_quantity,0) -
1820 nvl(prior_scrap_quantity,0)),
1821 1),
1822 0)
1823 FROM
1824 wip_operation_resources w2,
1825 cst_comp_snapshot cocd
1826 WHERE
1827 w1.wip_entity_id = w2.wip_entity_id AND
1828 w1.operation_seq_num = w2.operation_seq_num AND
1829 w1.resource_seq_num = w2.resource_seq_num AND
1830 w1.organization_id = w2.organization_id AND
1831 w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
1832 w2.operation_seq_num = cocd.operation_seq_num AND
1833 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1834 cocd.new_operation_flag = 2 AND */
1835 cocd.transaction_id = i_trx_id)
1836 WHERE
1837 w1.wip_entity_id = i_wip_entity_id AND
1838 w1.organization_id = i_org_id;
1839
1840
1841
1842 stmt_num := 295;
1843
1844 UPDATE wip_operation_overheads w1
1845 SET
1846 (relieved_ovhd_completion_units,
1847 temp_relieved_value,
1848 relieved_ovhd_completion_value) =
1849 (SELECT
1850 NVL(w1.relieved_ovhd_completion_units,0) +
1851 decode(sign(applied_ovhd_units -
1852 nvl(relieved_ovhd_completion_units,0)-
1853 nvl(relieved_ovhd_final_comp_units,0)-
1854 nvl(relieved_ovhd_scrap_units,0)),
1855 1,
1856 (applied_ovhd_units -
1857 nvl(relieved_ovhd_completion_units,0)-
1858 nvl(relieved_ovhd_final_comp_units,0)-
1859 nvl(relieved_ovhd_scrap_units,0))*
1860 --
1861 -- new to solve divided by zero and over relieved
1862 --
1863 decode(sign(i_txn_qty - (cocd.quantity_completed -
1864 nvl(prior_completion_quantity,0) -
1865 nvl(prior_scrap_quantity,0))),
1866 -1,i_txn_qty/(cocd.quantity_completed -
1867 nvl(prior_completion_quantity,0) -
1868 nvl(prior_scrap_quantity,0)),
1869 1),
1870 0),
1871 decode(sign(applied_ovhd_value -
1872 nvl(relieved_ovhd_completion_value,0)-
1873 nvl(relieved_variance_value,0)-
1874 nvl(relieved_ovhd_scrap_value,0)),
1875 1,
1876 (applied_ovhd_value -
1877 nvl(relieved_ovhd_completion_value,0)-
1878 nvl(relieved_variance_value,0)-
1879 nvl(relieved_ovhd_scrap_value,0))*
1880 --
1881 -- new to solve divided by zero and over relieved
1882 --
1883 decode(sign(i_txn_qty - (cocd.quantity_completed -
1884 nvl(prior_completion_quantity,0) -
1885 nvl(prior_scrap_quantity,0))),
1886 -1,i_txn_qty/(cocd.quantity_completed -
1887 nvl(prior_completion_quantity,0) -
1888 nvl(prior_scrap_quantity,0)),
1889 1),
1890 0),
1891 nvl(w1.relieved_ovhd_completion_value,0) +
1892 decode(sign(applied_ovhd_value -
1893 nvl(relieved_ovhd_completion_value,0)-
1894 nvl(relieved_variance_value,0)-
1895 nvl(relieved_ovhd_scrap_value,0)),
1896 1,
1897 (applied_ovhd_value -
1898 nvl(relieved_ovhd_completion_value,0)-
1899 nvl(relieved_variance_value,0)-
1900 nvl(relieved_ovhd_scrap_value,0))*
1901 --
1902 -- new to solve divided by zero and over relieved
1903 --
1904 decode(sign(i_txn_qty - (cocd.quantity_completed -
1905 nvl(prior_completion_quantity,0) -
1906 nvl(prior_scrap_quantity,0))),
1907 -1,i_txn_qty/(cocd.quantity_completed -
1908 nvl(prior_completion_quantity,0) -
1909 nvl(prior_scrap_quantity,0)),
1910 1),
1911 0)
1912 FROM
1913 wip_operation_overheads w2,
1914 cst_comp_snapshot cocd
1915 WHERE
1916 w1.wip_entity_id = w2.wip_entity_id AND
1917 w1.operation_seq_num = w2.operation_seq_num AND
1918 w1.resource_seq_num = w2.resource_seq_num AND
1919 w1.overhead_id = w2.overhead_id AND
1920 w1.organization_id = w2.organization_id AND
1921 w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
1922 w1.basis_type = w2.basis_type AND
1923 w2.operation_seq_num = cocd.operation_seq_num AND
1924 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
1925 cocd.new_operation_flag = 2 AND */
1926 cocd.transaction_id = i_trx_id)
1927 WHERE
1928 w1.wip_entity_id = i_wip_entity_id AND
1929 w1.organization_id = i_org_id;
1930
1931
1932 ELSIF (l_system_option_id = 2) THEN
1933
1934 -- Or ... If we are using the Pre defined resource option, use
1935 -- the pre defined rates for resources and overheads.
1936
1937 stmt_num := 300;
1938
1939 UPDATE wip_operation_resources w1
1940 SET
1941 (relieved_res_completion_units,
1942 temp_relieved_value,
1943 relieved_res_completion_value) =
1944 (SELECT
1945 nvl(w1.relieved_res_completion_units,0)+
1946 decode(basis_type,
1947 1,i_txn_qty*usage_rate_or_amount,
1948 2,i_txn_qty*usage_rate_or_amount/l_lot_size,
1949 i_txn_qty*usage_rate_or_amount),
1950 decode(SIGN(applied_resource_units-
1951 nvl(relieved_res_completion_units,0)-
1952 nvl(relieved_res_final_comp_units,0)-
1953 nvl(relieved_res_scrap_units,0)-
1954 i_txn_qty*decode(basis_type,
1955 1,usage_rate_or_amount,
1956 2,usage_rate_or_amount/l_lot_size,
1957 usage_rate_or_amount)),
1958 SIGN(usage_rate_or_amount),
1959 i_txn_qty*decode(basis_type,
1960 1,usage_rate_or_amount,
1961 2,usage_rate_or_amount/l_lot_size,
1962 usage_rate_or_amount)*
1963 decode(SIGN(nvl(applied_resource_value,0)-
1964 nvl(relieved_res_completion_value,0)-
1965 nvl(relieved_variance_value,0)-
1966 nvl(relieved_res_scrap_value,0)),
1967 SIGN(usage_rate_or_amount),
1968 decode(basis_type,
1969 1,((nvl(applied_resource_value,0)-
1970 nvl(relieved_res_completion_value,0)-
1971 nvl(relieved_variance_value,0)-
1972 nvl(relieved_res_scrap_value,0))
1973 /(applied_resource_units-
1974 nvl(relieved_res_completion_units,0)-
1975 nvl(relieved_res_final_comp_units,0)-
1976 nvl(relieved_res_scrap_units,0))),
1977 2,nvl(applied_resource_value,0)/
1978 decode(applied_resource_units,
1979 0,1,applied_resource_units),
1980 ((nvl(applied_resource_value,0)-
1981 nvl(relieved_res_completion_value,0)-
1982 nvl(relieved_variance_value,0)-
1983 nvl(relieved_res_scrap_value,0))
1984 /(applied_resource_units-
1985 nvl(relieved_res_completion_units,0)-
1986 nvl(relieved_res_final_comp_units,0)-
1987 nvl(relieved_res_scrap_units,0)))),
1988 crc.resource_rate),
1989 0,
1990 decode(SIGN(nvl(applied_resource_value,0)-
1991 nvl(relieved_res_completion_value,0)-
1992 nvl(relieved_variance_value,0)-
1993 nvl(relieved_res_scrap_value,0)),
1994 SIGN(usage_rate_or_amount),
1995 (nvl(applied_resource_value,0)-
1996 nvl(relieved_res_completion_value,0)-
1997 nvl(relieved_variance_value,0)-
1998 nvl(relieved_res_scrap_value,0)),
1999 i_txn_qty*decode(basis_type,
2000 1,usage_rate_or_amount,
2001 2,usage_rate_or_amount/l_lot_size,
2002 usage_rate_or_amount)*
2003 crc.resource_rate),
2004 -1*SIGN(usage_rate_or_amount),
2005 decode(SIGN(nvl(applied_resource_value,0)-
2006 nvl(relieved_res_completion_value,0)-
2007 nvl(relieved_variance_value,0)-
2008 nvl(relieved_res_scrap_value,0)),
2009 SIGN(usage_rate_or_amount),
2010 decode( sign(applied_resource_units -
2011 nvl(relieved_res_completion_units,0) -
2012 nvl(relieved_res_final_comp_units,0) -
2013 nvl(relieved_res_scrap_units,0)),
2014 SIGN(usage_rate_or_amount),
2015 (nvl(applied_resource_value,0)-
2016 nvl(relieved_res_completion_value,0)-
2017 nvl(relieved_variance_value,0)-
2018 nvl(relieved_res_scrap_value,0)+
2019 (i_txn_qty*
2020 decode(basis_type,
2021 1,usage_rate_or_amount,
2022 2,usage_rate_or_amount/l_lot_size,
2023 usage_rate_or_amount) -
2024 (applied_resource_units -
2025 nvl(relieved_res_completion_units,0) -
2026 nvl(relieved_res_final_comp_units,0) -
2027 nvl(relieved_res_scrap_units,0)))*
2028 crc.resource_rate),
2029 (i_txn_qty*
2030 decode(basis_type,
2031 1,usage_rate_or_amount,
2032 2,usage_rate_or_amount/l_lot_size,
2033 usage_rate_or_amount)*crc.resource_rate)
2034 ),
2035 i_txn_qty*
2036 decode(basis_type,
2037 1,usage_rate_or_amount,
2038 2,usage_rate_or_amount/l_lot_size,
2039 usage_rate_or_amount)*
2040 crc.resource_rate)),
2041 nvl(w1.relieved_res_completion_value,0) +
2042 decode(SIGN(applied_resource_units-
2043 nvl(relieved_res_completion_units,0)-
2044 nvl(relieved_res_final_comp_units,0)-
2045 nvl(relieved_res_scrap_units,0)-
2046 i_txn_qty*decode(basis_type,
2047 1,usage_rate_or_amount,
2048 2,usage_rate_or_amount/l_lot_size,
2049 usage_rate_or_amount)),
2050 SIGN(usage_rate_or_amount),
2051 i_txn_qty*decode(basis_type,
2052 1,usage_rate_or_amount,
2053 2,usage_rate_or_amount/l_lot_size,
2054 usage_rate_or_amount)*
2055 decode(SIGN(nvl(applied_resource_value,0)-
2056 nvl(relieved_res_completion_value,0)-
2057 nvl(relieved_variance_value,0)-
2058 nvl(relieved_res_scrap_value,0)),
2059 SIGN(usage_rate_or_amount),
2060 decode(basis_type,
2061 1,((nvl(applied_resource_value,0)-
2062 nvl(relieved_res_completion_value,0)-
2063 nvl(relieved_variance_value,0)-
2064 nvl(relieved_res_scrap_value,0))
2065 /(applied_resource_units-
2066 nvl(relieved_res_completion_units,0)-
2067 nvl(relieved_res_final_comp_units,0)-
2068 nvl(relieved_res_scrap_units,0))),
2069 2,nvl(applied_resource_value,0)/
2070 decode(applied_resource_units,
2071 0,1,applied_resource_units),
2072 ((nvl(applied_resource_value,0)-
2073 nvl(relieved_res_completion_value,0)-
2074 nvl(relieved_variance_value,0)-
2075 nvl(relieved_res_scrap_value,0))
2076 /(applied_resource_units-
2077 nvl(relieved_res_completion_units,0)-
2078 nvl(relieved_res_final_comp_units,0)-
2079 nvl(relieved_res_scrap_units,0)))),
2080 crc.resource_rate),
2081 0,
2082 decode(SIGN(nvl(applied_resource_value,0)-
2083 nvl(relieved_res_completion_value,0)-
2084 nvl(relieved_variance_value,0)-
2085 nvl(relieved_res_scrap_value,0)),
2086 SIGN(usage_rate_or_amount),
2087 (nvl(applied_resource_value,0)-
2088 nvl(relieved_res_completion_value,0)-
2089 nvl(relieved_variance_value,0)-
2090 nvl(relieved_res_scrap_value,0)),
2091 i_txn_qty*decode(basis_type,
2092 1,usage_rate_or_amount,
2093 2,usage_rate_or_amount/l_lot_size,
2094 usage_rate_or_amount)*
2095 crc.resource_rate),
2096 -1*SIGN(usage_rate_or_amount),
2097 decode(SIGN(nvl(applied_resource_value,0)-
2098 nvl(relieved_res_completion_value,0)-
2099 nvl(relieved_variance_value,0)-
2100 nvl(relieved_res_scrap_value,0)),
2101 SIGN(usage_rate_or_amount),
2102 decode( sign(applied_resource_units -
2103 nvl(relieved_res_completion_units,0) -
2104 nvl(relieved_res_final_comp_units,0) -
2105 nvl(relieved_res_scrap_units,0)),
2106 SIGN(usage_rate_or_amount),
2107 (nvl(applied_resource_value,0)-
2108 nvl(relieved_res_completion_value,0)-
2109 nvl(relieved_variance_value,0)-
2110 nvl(relieved_res_scrap_value,0)+
2111 (i_txn_qty*
2112 decode(basis_type,
2113 1,usage_rate_or_amount,
2114 2,usage_rate_or_amount/l_lot_size,
2115 usage_rate_or_amount) -
2116 (applied_resource_units -
2117 nvl(relieved_res_completion_units,0) -
2118 nvl(relieved_res_final_comp_units,0) -
2119 nvl(relieved_res_scrap_units,0)))*
2120 crc.resource_rate),
2121 (i_txn_qty*
2122 decode(basis_type,
2123 1,usage_rate_or_amount,
2124 2,usage_rate_or_amount/l_lot_size,
2125 usage_rate_or_amount)*crc.resource_rate)
2126 ),
2127 i_txn_qty*
2128 decode(basis_type,
2129 1,usage_rate_or_amount,
2130 2,usage_rate_or_amount/l_lot_size,
2131 usage_rate_or_amount)*
2132 crc.resource_rate))
2133 FROM
2134 wip_operation_resources w2,
2135 cst_resource_costs crc
2136 WHERE
2137 w2.wip_entity_id = w1.wip_entity_id AND
2138 w2.operation_seq_num = w1.operation_seq_num AND
2139 w2.resource_seq_num = w1.resource_seq_num AND
2140 w2.organization_id = w2.organization_id AND
2141 w2.resource_id = crc.resource_id AND
2142 w2.organization_id = crc.organization_id AND
2143 crc.cost_type_id = i_res_cost_type_id)
2144 WHERE
2145 w1.wip_entity_id = i_wip_entity_id AND
2146 w1.organization_id = i_org_id AND
2147 w1.usage_rate_or_amount <> 0;
2148
2149
2150 /***********************************************************
2151 * Relieve TL Ovhd (Move based) units and Costs .. *
2152 * Open Issue : Do we relieve Ovhds for which associations *
2153 * no longer exist in CDO. *
2154 ***********************************************************/
2155
2156
2157 stmt_num := 305;
2158
2159 -- For the pre-defined completion algorithm, if no overheads have
2160 -- been charged then they must be relieved at the cost in the
2161 -- rates cost type. However, if nothing has been charged, there are
2162 -- no rows in WOO. So insert these rows.
2163
2164 INSERT INTO WIP_OPERATION_OVERHEADS
2165 (WIP_ENTITY_ID,
2166 OPERATION_SEQ_NUM,
2167 RESOURCE_SEQ_NUM,
2168 ORGANIZATION_ID,
2169 OVERHEAD_ID,
2170 BASIS_TYPE,
2171 APPLIED_OVHD_UNITS,
2172 APPLIED_OVHD_VALUE,
2173 RELIEVED_OVHD_COMPLETION_UNITS,
2174 RELIEVED_OVHD_SCRAP_UNITS,
2175 RELIEVED_OVHD_COMPLETION_VALUE,
2176 RELIEVED_OVHD_SCRAP_VALUE,
2177 TEMP_RELIEVED_VALUE,
2178 LAST_UPDATED_BY,
2179 CREATION_DATE,
2180 CREATED_BY,
2181 LAST_UPDATE_LOGIN,
2182 REQUEST_ID,
2183 PROGRAM_APPLICATION_ID,
2184 PROGRAM_ID,
2185 PROGRAM_UPDATE_DATE,
2186 LAST_UPDATE_DATE)
2187 SELECT
2188 i_wip_entity_id,
2189 wo.operation_seq_num,
2190 -1,
2191 i_org_id,
2192 cdo.overhead_id,
2193 cdo.basis_type,
2194 0,
2195 0,
2196 0,
2197 0,
2198 0,
2199 0,
2200 0,
2201 -1,
2202 SYSDATE,
2203 -1,
2204 -1,
2205 -1,
2206 -1,
2207 -1,
2208 SYSDATE,
2209 SYSDATE
2210 FROM
2211 WIP_OPERATIONS WO,
2212 CST_DEPARTMENT_OVERHEADS CDO
2213 WHERE
2214 WO.WIP_ENTITY_ID = i_wip_entity_id AND
2215 WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
2216 CDO.COST_TYPE_ID = i_res_cost_type_id AND
2217 CDO.BASIS_TYPE IN (1,2) AND
2218 NOT EXISTS
2219 (SELECT 'X'
2220 FROM
2221 WIP_OPERATION_OVERHEADS WOO
2222 where
2223 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
2224 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
2225 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2226 WOO.BASIS_TYPE = CDO.BASIS_TYPE AND
2227 WOO.RESOURCE_SEQ_NUM = -1);
2228
2229
2230 stmt_num := 310;
2231
2232 UPDATE wip_operation_overheads w1
2233 SET
2234 (relieved_ovhd_completion_units,
2235 temp_relieved_value,
2236 relieved_ovhd_completion_value) =
2237 (SELECT
2238 nvl(w1.relieved_ovhd_completion_units,0)+
2239 decode(w2.basis_type,
2240 1,i_txn_qty,
2241 2,i_txn_qty/l_lot_size),
2242 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2243 nvl(relieved_ovhd_completion_units,0)-
2244 nvl(relieved_ovhd_final_comp_units,0)-
2245 nvl(relieved_ovhd_scrap_units,0)-
2246 decode(w2.basis_type,
2247 1,i_txn_qty,
2248 2,i_txn_qty/l_lot_size)),
2249 1,
2250 decode(SIGN(nvl(applied_ovhd_value,0)-
2251 nvl(relieved_ovhd_completion_value,0)-
2252 nvl(relieved_variance_value,0)-
2253 nvl(relieved_ovhd_scrap_value,0)),
2254 1,
2255 decode(w2.basis_type,
2256 2,nvl(applied_ovhd_value,0),
2257 (nvl(applied_ovhd_value,0)-
2258 nvl(relieved_ovhd_completion_value,0)-
2259 nvl(relieved_variance_value,0)-
2260 nvl(relieved_ovhd_scrap_value,0))
2261 /(nvl(applied_ovhd_units,0)-
2262 nvl(relieved_ovhd_completion_units,0)-
2263 nvl(relieved_ovhd_final_comp_units,0)-
2264 nvl(relieved_ovhd_scrap_units,0)))*
2265 decode(w2.basis_type,
2266 1,i_txn_qty,
2267 2,i_txn_qty/l_lot_size),
2268 cdo.rate_or_amount*
2269 decode(w2.basis_type,
2270 1,i_txn_qty,
2271 2,i_txn_qty/l_lot_size)),
2272 0,
2273 decode(SIGN(nvl(applied_ovhd_value,0)-
2274 nvl(relieved_ovhd_completion_value,0)-
2275 nvl(relieved_variance_value,0)-
2276 nvl(relieved_ovhd_scrap_value,0)),
2277 1,
2278 (nvl(applied_ovhd_value,0)-
2279 nvl(relieved_ovhd_completion_value,0)-
2280 nvl(relieved_variance_value,0)-
2281 nvl(relieved_ovhd_scrap_value,0)),
2282 cdo.rate_or_amount*
2283 decode(w2.basis_type,
2284 1,i_txn_qty,
2285 2,i_txn_qty/l_lot_size)),
2286 -1,
2287 decode(SIGN(nvl(applied_ovhd_value,0)-
2288 nvl(relieved_ovhd_completion_value,0)-
2289 nvl(relieved_variance_value,0)-
2290 nvl(relieved_ovhd_scrap_value,0)),
2291 1,
2292 decode(sign(nvl(w2.applied_ovhd_units,0)-
2293 nvl(relieved_ovhd_completion_units,0)-
2294 nvl(relieved_ovhd_final_comp_units,0)-
2295 nvl(relieved_ovhd_scrap_units,0)),
2296 1,
2297 (nvl(applied_ovhd_value,0)-
2298 nvl(relieved_ovhd_completion_value,0)-
2299 nvl(relieved_variance_value,0)-
2300 nvl(relieved_ovhd_scrap_value,0)+
2301 (decode(w2.basis_type,
2302 1,i_txn_qty,
2303 2,i_txn_qty/l_lot_size)-
2304 (nvl(w2.applied_ovhd_units,0)-
2305 nvl(relieved_ovhd_completion_units,0)-
2306 nvl(relieved_ovhd_final_comp_units,0)-
2307 nvl(relieved_ovhd_scrap_units,0))
2308 )*cdo.rate_or_amount),
2309 decode(w2.basis_type,
2310 1,i_txn_qty,
2311 2,i_txn_qty/l_lot_size)*cdo.rate_or_amount
2312 ),
2313 cdo.rate_or_amount*
2314 decode(w2.basis_type,
2315 1,i_txn_qty,
2316 2,i_txn_qty/l_lot_size))),
2317 nvl(w1.relieved_ovhd_completion_value,0) +
2318 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2319 nvl(relieved_ovhd_completion_units,0)-
2320 nvl(relieved_ovhd_final_comp_units,0)-
2321 nvl(relieved_ovhd_scrap_units,0)-
2322 decode(w2.basis_type,
2323 1,i_txn_qty,
2324 2,i_txn_qty/l_lot_size)),
2325 1,
2326 decode(SIGN(nvl(applied_ovhd_value,0)-
2327 nvl(relieved_ovhd_completion_value,0)-
2328 nvl(relieved_variance_value,0)-
2329 nvl(relieved_ovhd_scrap_value,0)),
2330 1,
2331 decode(w2.basis_type,
2332 2,nvl(applied_ovhd_value,0),
2333 (nvl(applied_ovhd_value,0)-
2334 nvl(relieved_ovhd_completion_value,0)-
2335 nvl(relieved_variance_value,0)-
2336 nvl(relieved_ovhd_scrap_value,0))
2337 /(nvl(applied_ovhd_units,0)-
2338 nvl(relieved_ovhd_completion_units,0)-
2339 nvl(relieved_ovhd_final_comp_units,0)-
2340 nvl(relieved_ovhd_scrap_units,0)))*
2341 decode(w2.basis_type,
2342 1,i_txn_qty,
2343 2,i_txn_qty/l_lot_size),
2344 cdo.rate_or_amount*
2345 decode(w2.basis_type,
2346 1,i_txn_qty,
2347 2,i_txn_qty/l_lot_size)),
2348 0,
2349 decode(SIGN(nvl(applied_ovhd_value,0)-
2350 nvl(relieved_ovhd_completion_value,0)-
2351 nvl(relieved_variance_value,0)-
2352 nvl(relieved_ovhd_scrap_value,0)),
2353 1,
2354 (nvl(applied_ovhd_value,0)-
2355 nvl(relieved_ovhd_completion_value,0)-
2356 nvl(relieved_variance_value,0)-
2357 nvl(relieved_ovhd_scrap_value,0)),
2358 cdo.rate_or_amount*
2359 decode(w2.basis_type,
2360 1,i_txn_qty,
2361 2,i_txn_qty/l_lot_size)),
2362 -1,
2363 decode(SIGN(nvl(applied_ovhd_value,0)-
2364 nvl(relieved_ovhd_completion_value,0)-
2365 nvl(relieved_variance_value,0)-
2366 nvl(relieved_ovhd_scrap_value,0)),
2367 1,
2368 decode(sign(nvl(w2.applied_ovhd_units,0)-
2369 nvl(relieved_ovhd_completion_units,0)-
2370 nvl(relieved_ovhd_final_comp_units,0)-
2371 nvl(relieved_ovhd_scrap_units,0)),
2372 1,
2373 (nvl(applied_ovhd_value,0)-
2374 nvl(relieved_ovhd_completion_value,0)-
2375 nvl(relieved_variance_value,0)-
2376 nvl(relieved_ovhd_scrap_value,0)+
2377 (decode(w2.basis_type,
2378 1,i_txn_qty,
2379 2,i_txn_qty/l_lot_size)-
2380 (nvl(w2.applied_ovhd_units,0)-
2381 nvl(relieved_ovhd_completion_units,0)-
2382 nvl(relieved_ovhd_final_comp_units,0)-
2383 nvl(relieved_ovhd_scrap_units,0))
2384 )*cdo.rate_or_amount),
2385 decode(w2.basis_type,
2386 1,i_txn_qty,
2387 2,i_txn_qty/l_lot_size)*cdo.rate_or_amount
2388 ),
2389 cdo.rate_or_amount*
2390 decode(w2.basis_type,
2391 1,i_txn_qty,
2392 2,i_txn_qty/l_lot_size)))
2393 FROM
2394 wip_operation_overheads w2,
2395 cst_department_overheads cdo,
2396 wip_operations wo
2397 WHERE
2398 w2.wip_entity_id = w1.wip_entity_id AND
2399 w2.organization_id = w1.organization_id AND
2400 w2.operation_seq_num = w1.operation_seq_num AND
2401 w2.overhead_id = w1.overhead_id AND
2402 w2.basis_type = w1.basis_type AND
2403 w2.wip_entity_id = wo.wip_entity_id AND
2404 w2.organization_id = wo.organization_id AND
2405 w2.operation_seq_num = wo.operation_seq_num AND
2406 cdo.department_id = wo.department_id AND
2407 cdo.overhead_id = w2.overhead_id AND
2408 cdo.basis_type = w2.basis_type AND
2409 cdo.cost_type_id = i_res_cost_type_id)
2410 WHERE
2411 w1.wip_entity_id = i_wip_entity_id AND
2412 w1.organization_id = i_org_id AND
2413 w1.basis_type IN (1,2) AND
2414 EXISTS
2415 (
2416 SELECT 'X'
2417 FROM
2418 cst_department_overheads cdo2,
2419 wip_operations wo2
2420 WHERE
2421 wo2.wip_entity_id = w1.wip_entity_id AND
2422 wo2.organization_id = w1.organization_id AND
2423 wo2.operation_seq_num = w1.operation_seq_num AND
2424 wo2.department_id = cdo2.department_id AND
2425 w1.overhead_id = cdo2.overhead_id AND
2426 w1.basis_type = cdo2.basis_type AND
2427 cdo2.cost_type_id = i_res_cost_type_id);
2428
2429 /***********************************************************
2430 * Relieve TL Res based overheads and Units ... *
2431 ***********************************************************/
2432
2433 stmt_num := 320;
2434
2435 INSERT INTO WIP_OPERATION_OVERHEADS
2436 (WIP_ENTITY_ID,
2437 OPERATION_SEQ_NUM,
2438 RESOURCE_SEQ_NUM,
2439 ORGANIZATION_ID,
2440 OVERHEAD_ID,
2441 BASIS_TYPE,
2442 APPLIED_OVHD_UNITS,
2443 APPLIED_OVHD_VALUE,
2444 RELIEVED_OVHD_COMPLETION_UNITS,
2445 RELIEVED_OVHD_SCRAP_UNITS,
2446 RELIEVED_OVHD_COMPLETION_VALUE,
2447 RELIEVED_OVHD_SCRAP_VALUE,
2448 TEMP_RELIEVED_VALUE,
2449 LAST_UPDATED_BY,
2450 CREATION_DATE,
2451 CREATED_BY,
2452 LAST_UPDATE_LOGIN,
2453 REQUEST_ID,
2454 PROGRAM_APPLICATION_ID,
2455 PROGRAM_ID,
2456 PROGRAM_UPDATE_DATE,
2457 LAST_UPDATE_DATE)
2458 SELECT
2459 i_wip_entity_id,
2460 wo.operation_seq_num,
2461 wor.resource_seq_num,
2462 i_org_id,
2463 cdo.overhead_id,
2464 cdo.basis_type,
2465 0,
2466 0,
2467 0,
2468 0,
2469 0,
2470 0,
2471 0,
2472 -1,
2473 SYSDATE,
2474 -1,
2475 -1,
2476 -1,
2477 -1,
2478 -1,
2479 SYSDATE,
2480 SYSDATE
2481 FROM
2482 WIP_OPERATIONS WO,
2483 WIP_OPERATION_RESOURCES WOR,
2484 CST_DEPARTMENT_OVERHEADS CDO,
2485 CST_RESOURCE_OVERHEADS CRO
2486 WHERE
2487 WO.WIP_ENTITY_ID = i_wip_entity_id AND
2488 WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
2489 WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
2490 CDO.DEPARTMENT_ID = DECODE(WOR.PHANTOM_FLAG,
2491 1,WOR.DEPARTMENT_ID,
2492 WO.DEPARTMENT_ID) AND
2493 CDO.COST_TYPE_ID = i_res_cost_type_id AND
2494 CDO.BASIS_TYPE IN (3,4) AND
2495 CRO.COST_TYPE_ID = i_res_cost_type_id AND
2496 CRO.RESOURCE_ID = WOR.RESOURCE_ID AND
2497 CRO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2498 NOT EXISTS
2499 (SELECT 'X'
2500 FROM
2501 WIP_OPERATION_OVERHEADS WOO
2502 WHERE
2503 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
2504 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
2505 WOO.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM AND
2506 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2507 WOO.BASIS_TYPE = CDO.BASIS_TYPE);
2508
2509 stmt_num := 330;
2510
2511 UPDATE wip_operation_overheads w1
2512 SET
2513 (relieved_ovhd_completion_units,
2514 temp_relieved_value,
2515 relieved_ovhd_completion_value) =
2516 (SELECT
2517 nvl(w1.relieved_ovhd_completion_units,0)+
2518 decode(w2.basis_type,
2519 3,i_txn_qty*decode(wor.basis_type,
2520 1,usage_rate_or_amount,
2521 2,usage_rate_or_amount/l_lot_size,
2522 usage_rate_or_amount),
2523 4,wor.temp_relieved_value),
2524 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2525 nvl(relieved_ovhd_completion_units,0)-
2526 nvl(relieved_ovhd_final_comp_units,0)-
2527 nvl(relieved_ovhd_scrap_units,0)-
2528 decode(w2.basis_type,
2529 3,i_txn_qty*decode(wor.basis_type,
2530 1,usage_rate_or_amount,
2531 2,usage_rate_or_amount/l_lot_size,
2532 usage_rate_or_amount),
2533 4,wor.temp_relieved_value)),
2534 SIGN(wor.usage_rate_or_amount),
2535 decode(SIGN(nvl(applied_ovhd_value,0)-
2536 nvl(relieved_ovhd_completion_value,0)-
2537 nvl(w2.relieved_variance_value,0)-
2538 nvl(relieved_ovhd_scrap_value,0)),
2539 SIGN(wor.usage_rate_or_amount),
2540 ((nvl(applied_ovhd_value,0)-
2541 nvl(relieved_ovhd_completion_value,0)-
2542 nvl(w2.relieved_variance_value,0)-
2543 nvl(relieved_ovhd_scrap_value,0))
2544 /(nvl(applied_ovhd_units,0)-
2545 nvl(relieved_ovhd_completion_units,0)-
2546 nvl(relieved_ovhd_final_comp_units,0)-
2547 nvl(relieved_ovhd_scrap_units,0)))*
2548 decode(w2.basis_type,
2549 3,i_txn_qty*
2550 decode(wor.basis_type,
2551 1,wor.usage_rate_or_amount,
2552 2,wor.usage_rate_or_amount/l_lot_size),
2553 4,nvl(wor.temp_relieved_value,0)),
2554 nvl(cdo.rate_or_amount,0)*
2555 decode(w2.basis_type,
2556 3,i_txn_qty*
2557 decode(wor.basis_type,
2558 1,wor.usage_rate_or_amount,
2559 2,wor.usage_rate_or_amount/l_lot_size),
2560 4,nvl(wor.temp_relieved_value,0))),
2561 0,
2562 decode(SIGN(nvl(applied_ovhd_value,0)-
2563 nvl(relieved_ovhd_completion_value,0)-
2564 nvl(w2.relieved_variance_value,0)-
2565 nvl(relieved_ovhd_scrap_value,0)),
2566 SIGN(wor.usage_rate_or_amount),
2567 (nvl(applied_ovhd_value,0)-
2568 nvl(relieved_ovhd_completion_value,0)-
2569 nvl(w2.relieved_variance_value,0)-
2570 nvl(relieved_ovhd_scrap_value,0)),
2571 nvl(cdo.rate_or_amount,0)*
2572 decode(w2.basis_type,
2573 3,i_txn_qty*
2574 decode(wor.basis_type,
2575 1,wor.usage_rate_or_amount,
2576 2,wor.usage_rate_or_amount/l_lot_size),
2577 4,nvl(wor.temp_relieved_value,0))),
2578 -1*SIGN(wor.usage_rate_or_amount),
2579 decode(SIGN(nvl(applied_ovhd_value,0)-
2580 nvl(relieved_ovhd_completion_value,0)-
2581 nvl(w2.relieved_variance_value,0)-
2582 nvl(relieved_ovhd_scrap_value,0)),
2583 SIGN(wor.usage_rate_or_amount),
2584 decode(sign(nvl(w2.applied_ovhd_units,0)-
2585 nvl(relieved_ovhd_completion_units,0)-
2586 nvl(relieved_ovhd_final_comp_units,0)-
2587 nvl(relieved_ovhd_scrap_units,0)),
2588 SIGN(wor.usage_rate_or_amount),
2589 (nvl(applied_ovhd_value,0)-
2590 nvl(relieved_ovhd_completion_value,0)-
2591 nvl(w2.relieved_variance_value,0)-
2592 nvl(relieved_ovhd_scrap_value,0)+
2593 (decode(w2.basis_type,
2594 3,i_txn_qty*decode(wor.basis_type,
2595 1,usage_rate_or_amount,
2596 2,usage_rate_or_amount/l_lot_size,
2597 usage_rate_or_amount),
2598 4,wor.temp_relieved_value)-
2599 (nvl(w2.applied_ovhd_units,0)-
2600 nvl(relieved_ovhd_completion_units,0)-
2601 nvl(relieved_ovhd_final_comp_units,0)-
2602 nvl(relieved_ovhd_scrap_units,0)))*
2603 nvl(cdo.rate_or_amount,0)),
2604 nvl(cdo.rate_or_amount,0)*
2605 decode(w2.basis_type,
2606 3,i_txn_qty*
2607 decode(wor.basis_type,
2608 1,wor.usage_rate_or_amount,
2609 2,wor.usage_rate_or_amount/l_lot_size),
2610 4,nvl(wor.temp_relieved_value,0))
2611 ),
2612 nvl(cdo.rate_or_amount,0)*
2613 decode(w2.basis_type,
2614 3,i_txn_qty*
2615 decode(wor.basis_type,
2616 1,wor.usage_rate_or_amount,
2617 2,wor.usage_rate_or_amount/l_lot_size),
2618 4,nvl(wor.temp_relieved_value,0)))),
2619 nvl(w1.relieved_ovhd_completion_value,0) +
2620 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2621 nvl(relieved_ovhd_completion_units,0)-
2622 nvl(relieved_ovhd_final_comp_units,0)-
2623 nvl(relieved_ovhd_scrap_units,0)-
2624 decode(w2.basis_type,
2625 3,i_txn_qty*decode(wor.basis_type,
2626 1,usage_rate_or_amount,
2627 2,usage_rate_or_amount/l_lot_size,
2628 usage_rate_or_amount),
2629 4,wor.temp_relieved_value)),
2630 SIGN(wor.usage_rate_or_amount),
2631 decode(SIGN(nvl(applied_ovhd_value,0)-
2632 nvl(relieved_ovhd_completion_value,0)-
2633 nvl(w2.relieved_variance_value,0)-
2634 nvl(relieved_ovhd_scrap_value,0)),
2635 SIGN(wor.usage_rate_or_amount),
2636 ((nvl(applied_ovhd_value,0)-
2637 nvl(relieved_ovhd_completion_value,0)-
2638 nvl(w2.relieved_variance_value,0)-
2639 nvl(relieved_ovhd_scrap_value,0))
2640 /(nvl(applied_ovhd_units,0)-
2641 nvl(relieved_ovhd_completion_units,0)-
2642 nvl(relieved_ovhd_final_comp_units,0)-
2643 nvl(relieved_ovhd_scrap_units,0)))*
2644 decode(w2.basis_type,
2645 3,i_txn_qty*
2646 decode(wor.basis_type,
2647 1,wor.usage_rate_or_amount,
2648 2,wor.usage_rate_or_amount/l_lot_size),
2649 4,nvl(wor.temp_relieved_value,0)),
2650 nvl(cdo.rate_or_amount,0)*
2651 decode(w2.basis_type,
2652 3,i_txn_qty*
2653 decode(wor.basis_type,
2654 1,wor.usage_rate_or_amount,
2655 2,wor.usage_rate_or_amount/l_lot_size),
2656 4,nvl(wor.temp_relieved_value,0))),
2657 0,
2658 decode(SIGN(nvl(applied_ovhd_value,0)-
2659 nvl(relieved_ovhd_completion_value,0)-
2660 nvl(w2.relieved_variance_value,0)-
2661 nvl(relieved_ovhd_scrap_value,0)),
2662 SIGN(wor.usage_rate_or_amount),
2663 (nvl(applied_ovhd_value,0)-
2664 nvl(relieved_ovhd_completion_value,0)-
2665 nvl(w2.relieved_variance_value,0)-
2666 nvl(relieved_ovhd_scrap_value,0)),
2667 nvl(cdo.rate_or_amount,0)*
2668 decode(w2.basis_type,
2669 3,i_txn_qty*
2670 decode(wor.basis_type,
2671 1,wor.usage_rate_or_amount,
2672 2,wor.usage_rate_or_amount/l_lot_size),
2673 4,nvl(wor.temp_relieved_value,0))),
2674 -1*SIGN(wor.usage_rate_or_amount),
2675 decode(SIGN(nvl(applied_ovhd_value,0)-
2676 nvl(relieved_ovhd_completion_value,0)-
2677 nvl(w2.relieved_variance_value,0)-
2678 nvl(relieved_ovhd_scrap_value,0)),
2679 SIGN(wor.usage_rate_or_amount),
2680 decode(sign(nvl(w2.applied_ovhd_units,0)-
2681 nvl(relieved_ovhd_completion_units,0)-
2682 nvl(relieved_ovhd_final_comp_units,0)-
2683 nvl(relieved_ovhd_scrap_units,0)),
2684 SIGN(wor.usage_rate_or_amount),
2685 (nvl(applied_ovhd_value,0)-
2686 nvl(relieved_ovhd_completion_value,0)-
2687 nvl(w2.relieved_variance_value,0)-
2688 nvl(relieved_ovhd_scrap_value,0)+
2689 (decode(w2.basis_type,
2690 3,i_txn_qty*decode(wor.basis_type,
2691 1,usage_rate_or_amount,
2692 2,usage_rate_or_amount/l_lot_size,
2693 usage_rate_or_amount),
2694 4,wor.temp_relieved_value)-
2695 (nvl(w2.applied_ovhd_units,0)-
2696 nvl(relieved_ovhd_completion_units,0)-
2697 nvl(relieved_ovhd_final_comp_units,0)-
2698 nvl(relieved_ovhd_scrap_units,0)))*
2699 nvl(cdo.rate_or_amount,0)),
2700 nvl(cdo.rate_or_amount,0)*
2701 decode(w2.basis_type,
2702 3,i_txn_qty*
2703 decode(wor.basis_type,
2704 1,wor.usage_rate_or_amount,
2705 2,wor.usage_rate_or_amount/l_lot_size),
2706 4,nvl(wor.temp_relieved_value,0))
2707 ),
2708 nvl(cdo.rate_or_amount,0)*
2709 decode(w2.basis_type,
2710 3,i_txn_qty*
2711 decode(wor.basis_type,
2712 1,wor.usage_rate_or_amount,
2713 2,wor.usage_rate_or_amount/l_lot_size),
2714 4,nvl(wor.temp_relieved_value,0))))
2715 FROM
2716 wip_operation_overheads w2,
2717 cst_department_overheads cdo,
2718 wip_operations wo,
2719 wip_operation_resources wor,
2720 cst_resource_overheads cro
2721 WHERE
2722 w2.wip_entity_id = w1.wip_entity_id AND
2723 w2.organization_id = w1.organization_id AND
2724 w2.operation_seq_num = w1.operation_seq_num AND
2725 w2.overhead_id = w1.overhead_id AND
2726 w2.basis_type = w1.basis_type AND
2727 w2.resource_seq_num = w1.resource_seq_num AND
2728 w2.wip_entity_id = wo.wip_entity_id AND
2729 w2.organization_id = wo.organization_id AND
2730 w2.operation_seq_num = wo.operation_seq_num AND
2731 w2.wip_entity_id = wor.wip_entity_id AND
2732 w2.organization_id = wor.organization_id AND
2733 w2.operation_seq_num = wor.operation_seq_num AND
2734 w2.resource_seq_num = wor.resource_seq_num AND
2735 CDO.DEPARTMENT_ID = DECODE(WOR.PHANTOM_FLAG,
2736 1,WOR.DEPARTMENT_ID,
2737 WO.DEPARTMENT_ID) AND
2738 cdo.overhead_id = w2.overhead_id AND
2739 cdo.basis_type = w2.basis_type AND
2740 cdo.cost_type_id = i_res_cost_type_id AND
2741 cro.overhead_id = cdo.overhead_id AND
2742 cro.resource_id = wor.resource_id AND
2743 cro.cost_type_id = i_res_cost_type_id)
2744 WHERE
2745 w1.wip_entity_id = i_wip_entity_id AND
2746 w1.organization_id = i_org_id AND
2747 w1.basis_type IN (3,4) AND
2748 EXISTS
2749 (
2750 SELECT 'X'
2751 FROM
2752 cst_department_overheads cdo2,
2753 wip_operations wo2,
2754 cst_resource_overheads cro2,
2755 wip_operation_resources wor2
2756 WHERE
2757 w1.wip_entity_id = wo2.wip_entity_id AND
2758 w1.organization_id = wo2.organization_id AND
2759 w1.operation_seq_num = wo2.operation_seq_num AND
2760 w1.wip_entity_id = wor2.wip_entity_id AND
2761 w1.organization_id = wor2.organization_id AND
2762 w1.operation_seq_num = wor2.operation_seq_num AND
2763 w1.resource_seq_num = wor2.resource_seq_num AND
2764 wor2.usage_rate_or_amount <> 0 AND
2765 cdo2.department_id = DECODE(wor2.phantom_flag,
2766 1,wor2.department_id,
2767 wo2.department_id) AND
2768 cdo2.overhead_id = w1.overhead_id AND
2769 cdo2.basis_type = w1.basis_type AND
2770 cdo2.cost_type_id = i_res_cost_type_id AND
2771 cdo2.overhead_id = cro2.overhead_id AND
2772 cro2.resource_id = wor2.resource_id AND
2773 cro2.cost_type_id = i_res_cost_type_id);
2774
2775 END IF; -- System option if condition ends here.
2776
2777
2778 END IF; -- Main If ends here.
2779
2780 /************************************************************
2781 * Insert into mtl_cst_txn_cost_details now that the *
2782 * Costs have been computed ... *
2783 * 3 statements are required --> one each for PL costs *
2784 * , TL Res/OSP costs and TL ovhd costs. *
2785 * Remember - the cst_txn_cost_detail tables stores unit *
2786 * cost - but the wip tables store the value in the *
2787 * temp_relieved_value column - so we have to divide by the *
2788 * txn_qty to arrive at the unit cost. *
2789 ************************************************************/
2790
2791 IF(l_insert_ind <> 1) THEN
2792 /*BUG 7346225: For Final completion the MCTCD should be populated from
2793 WPB since this one has rounded values not like WROCD, WOR or WOO and
2794 this is prefered since Final completion should relieve the accounted
2795 value */
2796
2797 IF (i_final_comp_flag='Y') THEN
2798 stmt_num := 350;
2799 /* Bug 7346243: Removed Variance value from Available
2800 Value for Final Completion */
2801 INSERT INTO mtl_cst_txn_cost_details
2802 (
2803 TRANSACTION_ID,
2804 ORGANIZATION_ID,
2805 INVENTORY_ITEM_ID,
2806 COST_ELEMENT_ID,
2807 LEVEL_TYPE,
2808 TRANSACTION_COST,
2809 NEW_AVERAGE_COST,
2810 PERCENTAGE_CHANGE,
2811 VALUE_CHANGE,
2812 LAST_UPDATE_DATE,
2813 LAST_UPDATED_BY,
2814 CREATION_DATE,
2815 CREATED_BY,
2816 LAST_UPDATE_LOGIN,
2817 REQUEST_ID,
2818 PROGRAM_APPLICATION_ID,
2819 PROGRAM_ID,
2820 PROGRAM_UPDATE_DATE)
2821 SELECT
2822 i_trx_id,
2823 i_org_id,
2824 i_inv_item_id,
2825 cce.cost_element_id,
2826 1,
2827 decode(cce.cost_element_id,
2828 1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2829 2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
2830 3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
2831 4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
2832 5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0)))/i_txn_qty,
2833 NULL,
2834 NULL,
2835 NULL,
2836 SYSDATE,
2837 i_user_id,
2838 SYSDATE,
2839 i_user_id,
2840 i_login_id,
2841 i_request_id,
2842 i_prog_appl_id,
2843 i_prog_id,
2844 SYSDATE
2845 FROM
2846 CST_COST_ELEMENTS CCE,
2847 WIP_PERIOD_BALANCES WPB
2848 WHERE
2849 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2850 WPB.ORGANIZATION_ID = I_ORG_ID AND
2851 CCE.COST_ELEMENT_ID <> 2
2852 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
2853 HAVING decode(cce.cost_element_id,
2854 1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2855 2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
2856 3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
2857 4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
2858 5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0))) <> 0;
2859
2860 stmt_num := 355;
2861 /* Bug 7346243: Removed Variance value from Available
2862 Value for Final Completion */
2863 INSERT INTO mtl_cst_txn_cost_details
2864 (
2865 TRANSACTION_ID,
2866 ORGANIZATION_ID,
2867 INVENTORY_ITEM_ID,
2868 COST_ELEMENT_ID,
2869 LEVEL_TYPE,
2870 TRANSACTION_COST,
2871 NEW_AVERAGE_COST,
2872 PERCENTAGE_CHANGE,
2873 VALUE_CHANGE,
2874 LAST_UPDATE_DATE,
2875 LAST_UPDATED_BY,
2876 CREATION_DATE,
2877 CREATED_BY,
2878 LAST_UPDATE_LOGIN,
2879 REQUEST_ID,
2880 PROGRAM_APPLICATION_ID,
2881 PROGRAM_ID,
2882 PROGRAM_UPDATE_DATE)
2883 SELECT
2884 i_trx_id,
2885 i_org_id,
2886 i_inv_item_id,
2887 cce.cost_element_id,
2888 2,
2889 decode(cce.cost_element_id,
2890 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2891 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
2892 3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
2893 4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
2894 5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0)))/i_txn_qty,
2895 NULL,
2896 NULL,
2897 NULL,
2898 SYSDATE,
2899 i_user_id,
2900 SYSDATE,
2901 i_user_id,
2902 i_login_id,
2903 i_request_id,
2904 i_prog_appl_id,
2905 i_prog_id,
2906 SYSDATE
2907 FROM
2908 CST_COST_ELEMENTS CCE,
2909 WIP_PERIOD_BALANCES WPB
2910 WHERE
2911 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2912 WPB.ORGANIZATION_ID = I_ORG_ID
2913 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
2914 HAVING decode(cce.cost_element_id,
2915 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2916 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
2917 3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
2918 4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
2919 5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0))) <> 0;
2920
2921 ELSE
2922 stmt_num := 360;
2923
2924 INSERT INTO mtl_cst_txn_cost_details
2925 (
2926 TRANSACTION_ID,
2927 ORGANIZATION_ID,
2928 INVENTORY_ITEM_ID,
2929 COST_ELEMENT_ID,
2930 LEVEL_TYPE,
2931 TRANSACTION_COST,
2932 NEW_AVERAGE_COST,
2933 PERCENTAGE_CHANGE,
2934 VALUE_CHANGE,
2935 LAST_UPDATE_DATE,
2936 LAST_UPDATED_BY,
2937 CREATION_DATE,
2938 CREATED_BY,
2939 LAST_UPDATE_LOGIN,
2940 REQUEST_ID,
2941 PROGRAM_APPLICATION_ID,
2942 PROGRAM_ID,
2943 PROGRAM_UPDATE_DATE)
2944 SELECT
2945 i_trx_id,
2946 i_org_id,
2947 i_inv_item_id,
2948 wrocd.cost_element_id,
2949 2,
2950 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
2951 NULL,
2952 NULL,
2953 NULL,
2954 SYSDATE,
2955 i_user_id,
2956 SYSDATE,
2957 i_user_id,
2958 i_login_id,
2959 i_request_id,
2960 i_prog_appl_id,
2961 i_prog_id,
2962 SYSDATE
2963 FROM
2964 WIP_REQ_OPERATION_COST_DETAILS wrocd
2965 where
2966 WIP_ENTITY_ID = i_wip_entity_id AND
2967 ORGANIZATION_ID = i_org_id
2968 GROUP BY wrocd.cost_element_id
2969 HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
2970
2971
2972 stmt_num := 370;
2973
2974 INSERT INTO mtl_cst_txn_cost_details
2975 (
2976 TRANSACTION_ID,
2977 ORGANIZATION_ID,
2978 INVENTORY_ITEM_ID,
2979 COST_ELEMENT_ID,
2980 LEVEL_TYPE,
2981 TRANSACTION_COST,
2982 NEW_AVERAGE_COST,
2983 PERCENTAGE_CHANGE,
2984 VALUE_CHANGE,
2985 LAST_UPDATE_DATE,
2986 LAST_UPDATED_BY,
2987 CREATION_DATE,
2988 CREATED_BY,
2989 LAST_UPDATE_LOGIN,
2990 REQUEST_ID,
2991 PROGRAM_APPLICATION_ID,
2992 PROGRAM_ID,
2993 PROGRAM_UPDATE_DATE)
2994 SELECT
2995 i_trx_id,
2996 i_org_id,
2997 i_inv_item_id,
2998 br.cost_element_id,
2999 1,
3000 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
3001 NULL,
3002 NULL,
3003 NULL,
3004 SYSDATE,
3005 i_user_id,
3006 SYSDATE,
3007 i_user_id,
3008 i_login_id,
3009 i_request_id,
3010 i_prog_appl_id,
3011 i_prog_id,
3012 SYSDATE
3013 FROM
3014 BOM_RESOURCES BR,
3015 WIP_OPERATION_RESOURCES WOR
3016 WHERE
3017 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
3018 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
3019 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
3020 WOR.ORGANIZATION_ID = i_org_id
3021 GROUP BY BR.COST_ELEMENT_ID
3022 HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
3023
3024 stmt_num := 390;
3025
3026 INSERT INTO mtl_cst_txn_cost_details
3027 (
3028 TRANSACTION_ID,
3029 ORGANIZATION_ID,
3030 INVENTORY_ITEM_ID,
3031 COST_ELEMENT_ID,
3032 LEVEL_TYPE,
3033 TRANSACTION_COST,
3034 NEW_AVERAGE_COST,
3035 PERCENTAGE_CHANGE,
3036 VALUE_CHANGE,
3037 LAST_UPDATE_DATE,
3038 LAST_UPDATED_BY,
3039 CREATION_DATE,
3040 CREATED_BY,
3041 LAST_UPDATE_LOGIN,
3042 REQUEST_ID,
3043 PROGRAM_APPLICATION_ID,
3044 PROGRAM_ID,
3045 PROGRAM_UPDATE_DATE)
3046 SELECT
3047 i_trx_id,
3048 i_org_id,
3049 i_inv_item_id,
3050 5,
3051 1,
3052 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
3053 NULL,
3054 NULL,
3055 NULL,
3056 SYSDATE,
3057 i_user_id,
3058 SYSDATE,
3059 i_user_id,
3060 i_login_id,
3061 i_request_id,
3062 i_prog_appl_id,
3063 i_prog_id,
3064 SYSDATE
3065 FROM
3066 WIP_OPERATION_OVERHEADS
3067 WHERE
3068 WIP_ENTITY_ID = i_wip_entity_id AND
3069 ORGANIZATION_ID = i_org_id
3070 HAVING
3071 SUM(nvl(temp_relieved_value,0)) <> 0;
3072
3073 END IF;
3074
3075 stmt_num := 400;
3076 --
3077 -- R11.5 Enhanced Final Completion
3078 --
3079 IF (i_final_comp_flag = 'Y' AND l_comp_cost_source = 1
3080 AND l_use_val_cost_type <> -1) THEN
3081
3082 ---------------------------------------------
3083 -- Check if there is any -ve final completion
3084 -- for TL R, O, OSP and PL cost elements
3085 ---------------------------------------------
3086 stmt_num := 410;
3087 SELECT count(*)
3088 INTO l_count
3089 FROM mtl_cst_txn_cost_details mctcd,
3090 mtl_material_transactions mmt
3091 WHERE mctcd.transaction_id = mmt.transaction_id
3092 AND mctcd.transaction_id = i_trx_id
3093 AND mctcd.transaction_cost < 0;
3094
3095 IF (l_count <> 0) THEN
3096
3097 -----------------------------------------
3098 -- insert into wip_cost_txn_interface
3099 -----------------------------------------
3100 -- Get wip txn_id
3101 -----------------------------------------
3102 stmt_num := 440;
3103
3104 SELECT wip_transactions_s.nextval
3105 INTO l_wcti_txn_id
3106 FROM dual;
3107
3108 ----------------------------------------------------------------
3109 -- Insert into WCTI
3110 ----------------------------------------------------------------
3111 stmt_num := 460;
3112 INSERT INTO wip_cost_txn_interface
3113 (transaction_id,
3114 acct_period_id,
3115 process_status,
3116 process_phase,
3117 transaction_type,
3118 organization_id,
3119 wip_entity_id,
3120 wip_entity_name,
3121 entity_type,
3122 transaction_date,
3123 last_update_date,
3124 last_updated_by,
3125 last_update_login,
3126 creation_date,
3127 created_by,
3128 request_id,
3129 program_application_id,
3130 program_id,
3131 program_update_date)
3132 SELECT
3133 l_wcti_txn_id,
3134 i_acct_period_id,
3135 2,
3136 3,
3137 7, -- new transaction_type for final completion variance
3138 i_org_id,
3139 i_wip_entity_id,
3140 w.wip_entity_name,
3141 1,
3142 i_txn_date,
3143 sysdate,
3144 i_user_id,
3145 i_login_id,
3146 sysdate,
3147 i_user_id,
3148 i_request_id,
3149 i_prog_appl_id,
3150 i_prog_id,
3151 sysdate
3152 FROM
3153 wip_entities w
3154 WHERE
3155 w.wip_entity_id = i_wip_entity_id
3156 AND w.organization_id = i_org_id;
3157
3158 neg_final_completion( i_org_id => i_org_id,
3159 i_txn_date => i_txn_date,
3160 i_wip_entity_id => i_wip_entity_id,
3161 i_wcti_txn_id => l_wcti_txn_id,
3162 i_txn_qty => i_txn_qty,
3163 i_trx_id => i_trx_id,
3164 i_acct_period_id => i_acct_period_id,
3165 i_user_id => i_user_id,
3166 i_login_id => i_login_id,
3167 i_request_id => i_request_id,
3168 i_prog_id => i_prog_id,
3169 i_prog_appl_id => i_prog_appl_id,
3170 err_num => l_err_num,
3171 err_code => l_err_code,
3172 err_msg => l_err_msg);
3173
3174 IF (l_err_num <> 0) THEN
3175 raise proc_fail;
3176 END IF;
3177
3178 END IF;
3179 END IF;
3180 END IF;
3181
3182 EXCEPTION
3183 WHEN proc_fail THEN
3184 err_num := l_err_num;
3185 err_code := l_err_code;
3186 err_msg := l_err_msg;
3187
3188 WHEN OTHERS THEN
3189 err_num := SQLCODE;
3190 err_msg := 'CSTPACWC:' || ' complete:' || to_char(stmt_num) || ':' ||
3191 substr(SQLERRM,1,150);
3192
3193 END complete;
3194
3195 PROCEDURE neg_final_completion (
3196 i_org_id IN NUMBER,
3197 i_txn_date IN DATE,
3198 i_wip_entity_id IN NUMBER,
3199 i_wcti_txn_id IN NUMBER,
3200 i_txn_qty IN NUMBER,
3201 i_trx_id IN NUMBER,
3202 i_acct_period_id IN NUMBER,
3203 i_user_id IN NUMBER,
3204 i_login_id IN NUMBER,
3205 i_request_id IN NUMBER,
3206 i_prog_id IN NUMBER,
3207 i_prog_appl_id IN NUMBER,
3208 err_num OUT NOCOPY NUMBER,
3209 err_code OUT NOCOPY VARCHAR2,
3210 err_msg OUT NOCOPY VARCHAR2)
3211 IS
3212 stmt_num NUMBER;
3213 l_pri_curr VARCHAR2(15);
3214 l_sob_id NUMBER;
3215
3216 /* Bug 8277421 */
3217 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
3218 l_return_status VARCHAR2(10);
3219 l_msg_count NUMBER;
3220 l_msg_data VARCHAR2(2000);
3221
3222 BEGIN
3223
3224 stmt_num := 461;
3225
3226 /* The following line in the FROM clause has been commented out because
3227 we will now have to refer cst_organization_definitions as an impact
3228 of the HR-PROFILE option.*/
3229
3230 SELECT set_of_books_id
3231 INTO l_sob_id
3232 /*FROM org_organization_definitions*/
3233 FROM cst_organization_definitions
3234 WHERE organization_id = i_org_id;
3235
3236 stmt_num := 462;
3237 SELECT currency_code
3238 INTO l_pri_curr
3239 FROM gl_sets_of_books
3240 WHERE set_of_books_id = l_sob_id;
3241
3242 stmt_num := 465;
3243 INSERT INTO wip_transactions
3244 (transaction_id,
3245 acct_period_id,
3246 transaction_type,
3247 organization_id,
3248 wip_entity_id,
3249 transaction_date,
3250 last_update_date,
3251 last_updated_by,
3252 last_update_login,
3253 creation_date,
3254 created_by,
3255 request_id,
3256 program_application_id,
3257 program_id,
3258 program_update_date)
3259 SELECT
3260 wcti.transaction_id,
3261 wcti.acct_period_id,
3262 wcti.transaction_type,
3263 wcti.organization_id,
3264 wcti.wip_entity_id,
3265 wcti.transaction_date,
3266 sysdate,
3267 i_user_id,
3268 i_login_id,
3269 sysdate,
3270 i_user_id,
3271 i_request_id,
3272 i_prog_appl_id,
3273 i_prog_id,
3274 sysdate
3275 FROM wip_cost_txn_interface wcti
3276 WHERE transaction_id = i_wcti_txn_id;
3277
3278 /*------------------------------------------+
3279 | Do Accounting for wip valuation accounts
3280 +-------------------------------------------*/
3281 stmt_num := 470;
3282 INSERT INTO wip_transaction_accounts
3283 (transaction_id, reference_account,
3284 last_update_date, last_updated_by,
3285 creation_date, created_by,
3286 last_update_login, organization_id,
3287 transaction_date, wip_entity_id,
3288 repetitive_schedule_id, accounting_line_type,
3289 transaction_value, base_transaction_value,
3290 contra_set_id, primary_quantity,
3291 rate_or_amount, basis_type,
3292 resource_id, cost_element_id,
3293 activity_id, currency_code,
3294 currency_conversion_date, currency_conversion_type,
3295 currency_conversion_rate,
3296 request_id, program_application_id,
3297 program_id, program_update_date)
3298 SELECT
3299 i_wcti_txn_id,
3300 decode(mctcd.cost_element_id,
3301 1, wdj.material_account,
3302 2, wdj.material_overhead_account,
3303 3, wdj.resource_account,
3304 4, wdj.outside_processing_account,
3305 5, wdj.overhead_account),
3306 sysdate, i_user_id,
3307 sysdate, i_user_id,
3308 i_login_id, i_org_id,
3309 i_txn_date, i_wip_entity_id,
3310 NULL, 7,
3311 NULL,
3312 decode(c1.minimum_accountable_unit,
3313 NULL, round(-SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
3314 round(-SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
3315 * c1.minimum_accountable_unit),
3316 NULL, NULL,
3317 NULL, NULL,
3318 NULL, mctcd.cost_element_id,
3319 NULL, NULL,
3320 NULL, NULL,
3321 NULL,
3322 i_request_id, i_prog_appl_id,
3323 i_prog_id, sysdate
3324 FROM mtl_cst_txn_cost_details mctcd,
3325 mtl_material_transactions mmt,
3326 wip_discrete_jobs wdj,
3327 fnd_currencies c1
3328 WHERE mctcd.transaction_id = mmt.transaction_id
3329 AND mmt.transaction_source_id = wdj.wip_entity_id
3330 AND mctcd.transaction_id = i_trx_id
3331 AND mctcd.transaction_cost < 0
3332 AND c1.currency_code = l_pri_curr
3333 GROUP BY
3334 decode(mctcd.cost_element_id,
3335 1, wdj.material_account,
3336 2, wdj.material_overhead_account,
3337 3, wdj.resource_account,
3338 4, wdj.outside_processing_account,
3339 5, wdj.overhead_account),
3340 mctcd.cost_element_id,
3341 c1.minimum_accountable_unit,
3342 c1.precision;
3343 /*------------------------------------------+
3344 | Do Accounting for wip variance accounts
3345 +-------------------------------------------*/
3346 stmt_num := 480;
3347 INSERT INTO wip_transaction_accounts
3348 (transaction_id, reference_account,
3349 last_update_date, last_updated_by,
3350 creation_date, created_by,
3351 last_update_login, organization_id,
3352 transaction_date, wip_entity_id,
3353 repetitive_schedule_id, accounting_line_type,
3354 transaction_value, base_transaction_value,
3355 contra_set_id, primary_quantity,
3356 rate_or_amount, basis_type,
3357 resource_id, cost_element_id,
3358 activity_id, currency_code,
3359 currency_conversion_date, currency_conversion_type,
3360 currency_conversion_rate,
3361 request_id, program_application_id,
3362 program_id, program_update_date)
3363 SELECT
3364 i_wcti_txn_id,
3365 wdj.material_variance_account,
3366 sysdate, i_user_id,
3367 sysdate, i_user_id,
3368 i_login_id, i_org_id,
3369 i_txn_date, i_wip_entity_id,
3370 NULL, 8,
3371 NULL,
3372 /* decode(c1.minimum_accountable_unit,
3373 NULL, round(SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
3374 round(SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
3375 * c1.minimum_accountable_unit), */
3376 decode(c1.minimum_accountable_unit,
3377 NULL, SUM(round((mctcd.transaction_cost*i_txn_qty),c1.precision)),
3378 sum(round((mctcd.transaction_cost*i_txn_qty)/c1.minimum_accountable_unit)
3379 * c1.minimum_accountable_unit)),
3380 NULL, NULL,
3381 NULL, NULL,
3382 NULL, 1,
3383 NULL, NULL,
3384 NULL, NULL,
3385 NULL,
3386 i_request_id, i_prog_appl_id,
3387 i_prog_id, sysdate
3388 FROM mtl_cst_txn_cost_details mctcd,
3389 mtl_material_transactions mmt,
3390 wip_discrete_jobs wdj,
3391 fnd_currencies c1
3392 WHERE mctcd.transaction_id = mmt.transaction_id
3393 AND mmt.transaction_source_id = wdj.wip_entity_id
3394 AND mctcd.transaction_id = i_trx_id
3395 AND mctcd.transaction_cost < 0
3396 AND mctcd.level_type = 2
3397 AND c1.currency_code = l_pri_curr
3398 GROUP BY
3399 wdj.material_variance_account,
3400 c1.minimum_accountable_unit,
3401 c1.precision;
3402
3403 stmt_num := 490;
3404 INSERT INTO wip_transaction_accounts
3405 (transaction_id, reference_account,
3406 last_update_date, last_updated_by,
3407 creation_date, created_by,
3408 last_update_login, organization_id,
3409 transaction_date, wip_entity_id,
3410 repetitive_schedule_id, accounting_line_type,
3411 transaction_value, base_transaction_value,
3412 contra_set_id, primary_quantity,
3413 rate_or_amount, basis_type,
3414 resource_id, cost_element_id,
3415 activity_id, currency_code,
3416 currency_conversion_date, currency_conversion_type,
3417 currency_conversion_rate,
3418 request_id, program_application_id,
3419 program_id, program_update_date)
3420 SELECT
3421 i_wcti_txn_id,
3422 decode(mctcd.cost_element_id,
3423 3, wdj.resource_variance_account,
3424 4, wdj.outside_proc_variance_account,
3425 5, wdj.overhead_variance_account),
3426 sysdate, i_user_id,
3427 sysdate, i_user_id,
3428 i_login_id, i_org_id,
3429 i_txn_date, i_wip_entity_id,
3430 NULL, 8,
3431 NULL,
3432 decode(c1.minimum_accountable_unit,
3433 NULL, round(mctcd.transaction_cost*i_txn_qty,c1.precision),
3434 round(mctcd.transaction_cost*i_txn_qty/c1.minimum_accountable_unit)
3435 * c1.minimum_accountable_unit),
3436 NULL, NULL,
3437 NULL, NULL,
3438 NULL, mctcd.cost_element_id,
3439 NULL, NULL,
3440 NULL, NULL,
3441 NULL,
3442 i_request_id, i_prog_appl_id,
3443 i_prog_id, sysdate
3444 FROM mtl_cst_txn_cost_details mctcd,
3445 mtl_material_transactions mmt,
3446 wip_discrete_jobs wdj,
3447 fnd_currencies c1
3448 WHERE mctcd.transaction_id = mmt.transaction_id
3449 AND mmt.transaction_source_id = wdj.wip_entity_id
3450 AND mctcd.transaction_id = i_trx_id
3451 AND mctcd.transaction_cost < 0
3452 AND mctcd.level_type = 1
3453 AND mctcd.cost_element_id in (3,4,5)
3454 AND c1.currency_code = l_pri_curr;
3455
3456 stmt_num := 491;
3457
3458 UPDATE WIP_TRANSACTION_ACCOUNTS
3459 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
3460 WHERE TRANSACTION_ID = i_wcti_txn_id;
3461
3462 /* Bug 8277421 */
3463 stmt_num := 492;
3464 IF SQL%ROWCOUNT > 0 THEN
3465
3466 stmt_num := 493;
3467 l_trx_info.transaction_id := i_wcti_txn_id;
3468 l_trx_info.inv_organization_id := i_org_id;
3469 l_trx_info.wip_resource_id := -1;
3470 l_trx_info.wip_basis_type_id := -1;
3471 l_trx_info.txn_type_id := 7;
3472 l_trx_info.transaction_date := i_txn_date;
3473
3474 CST_XLA_PVT.Create_WIPXLAEvent (
3475 p_api_version => 1,
3476 p_init_msg_list => FND_API.G_FALSE,
3477 p_commit => FND_API.G_FALSE,
3478 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3479 x_return_status => l_return_status,
3480 x_msg_count => l_msg_count,
3481 x_msg_data => l_msg_data,
3482 p_trx_info => l_trx_info);
3483
3484 END IF;
3485
3486 stmt_num := 495;
3487 DELETE wip_cost_txn_interface
3488 WHERE transaction_id = i_wcti_txn_id;
3489
3490 stmt_num := 500;
3491 UPDATE wip_period_balances wpb
3492 SET
3493 (last_update_date,
3494 last_updated_by,
3495 last_update_login,
3496 request_id,
3497 program_application_id,
3498 program_id,
3499 program_update_date,
3500 pl_material_var,
3501 pl_material_overhead_var,
3502 pl_resource_var,
3503 pl_outside_processing_var,
3504 pl_overhead_var,
3505 tl_material_var,
3506 tl_material_overhead_var,
3507 tl_resource_var,
3508 tl_outside_processing_var,
3509 tl_overhead_var) =
3510 (SELECT
3511 sysdate,
3512 i_user_id,
3513 i_login_id,
3514 i_request_id,
3515 i_prog_id,
3516 i_prog_appl_id,
3517 sysdate,
3518 pl_material_var + decode(c1.minimum_accountable_unit,
3519 NULL, round(i_txn_qty*sum(decode(level_type,
3520 2,decode(cost_element_id,
3521 1,nvl(transaction_cost,0)
3522 ,0),0)),c1.precision),
3523 round((i_txn_qty*sum(decode(level_type,
3524 2,decode(cost_element_id,
3525 1,nvl(transaction_cost,0)
3526 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3527 pl_material_overhead_var + decode(c1.minimum_accountable_unit,
3528 NULL, round(i_txn_qty*sum(decode(level_type,
3529 2,decode(cost_element_id,
3530 2,nvl(transaction_cost,0)
3531 ,0),0)),c1.precision),
3532 round((i_txn_qty*sum(decode(level_type,
3533 2,decode(cost_element_id,
3534 2,nvl(transaction_cost,0)
3535 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3536 pl_resource_var + decode(c1.minimum_accountable_unit,
3537 NULL, round(i_txn_qty*sum(decode(level_type,
3538 2,decode(cost_element_id,
3539 3,nvl(transaction_cost,0)
3540 ,0),0)),c1.precision),
3541 round((i_txn_qty*sum(decode(level_type,
3542 2,decode(cost_element_id,
3543 3,nvl(transaction_cost,0)
3544 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3545 pl_outside_processing_var + decode(c1.minimum_accountable_unit,
3546 NULL, round(i_txn_qty*sum(decode(level_type,
3547 2,decode(cost_element_id,
3548 4,nvl(transaction_cost,0)
3549 ,0),0)),c1.precision),
3550 round((i_txn_qty*sum(decode(level_type,
3551 2,decode(cost_element_id,
3552 4,nvl(transaction_cost,0)
3553 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3554 pl_overhead_var + decode(c1.minimum_accountable_unit,
3555 NULL, round(i_txn_qty*sum(decode(level_type,
3556 2,decode(cost_element_id,
3557 5,nvl(transaction_cost,0)
3558 ,0),0)),c1.precision),
3559 round((i_txn_qty*sum(decode(level_type,
3560 2,decode(cost_element_id,
3561 5,nvl(transaction_cost,0)
3562 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3563 tl_material_var + decode(c1.minimum_accountable_unit,
3564 NULL, round(i_txn_qty* sum(decode(level_type,
3565 1,decode(cost_element_id,
3566 1,nvl(transaction_cost,0)
3567 ,0),0)),c1.precision),
3568 round((i_txn_qty* sum(decode(level_type,
3569 1,decode(cost_element_id,
3570 1,nvl(transaction_cost,0)
3571 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3572 tl_material_overhead_var + 0, /* The TL MO never gets Cr to the Job*/
3573 tl_resource_var + decode(c1.minimum_accountable_unit,
3574 NULL, round(i_txn_qty* sum(decode(level_type,
3575 1,decode(cost_element_id,
3576 3,nvl(transaction_cost,0)
3577 ,0),0)),c1.precision),
3578 round((i_txn_qty* sum(decode(level_type,
3579 1,decode(cost_element_id,
3580 3,nvl(transaction_cost,0)
3581 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3582 tl_outside_processing_var + decode(c1.minimum_accountable_unit,
3583 NULL, round(i_txn_qty* sum(decode(level_type,
3584 1,decode(cost_element_id,
3585 4,nvl(transaction_cost,0)
3586 ,0),0)),c1.precision),
3587 round((i_txn_qty* sum(decode(level_type,
3588 1,decode(cost_element_id,
3589 4,nvl(transaction_cost,0)
3590 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3591 tl_overhead_var + decode(c1.minimum_accountable_unit,
3592 NULL, round(i_txn_qty* sum(decode(level_type,
3593 1,decode(cost_element_id,
3594 5,nvl(transaction_cost,0)
3595 ,0),0)),c1.precision),
3596 round((i_txn_qty* sum(decode(level_type,
3597 1,decode(cost_element_id,
3598 5,nvl(transaction_cost,0)
3599 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit)
3600 FROM
3601 mtl_cst_txn_cost_details mctcd,
3602 fnd_currencies c1
3603 WHERE transaction_id = i_trx_id
3604 AND transaction_cost < 0
3605 AND c1.currency_code = l_pri_curr
3606 GROUP BY c1.minimum_accountable_unit, c1.precision)
3607 WHERE
3608 wip_entity_id = i_wip_entity_id AND
3609 organization_id = i_org_id AND
3610 acct_period_id = i_acct_period_id;
3611
3612 stmt_num := 510;
3613 --
3614 -- We have to re-avg with 0 cost but not -ve cost
3615 --
3616 UPDATE mtl_cst_txn_cost_details
3617 SET transaction_cost = 0
3618 WHERE transaction_cost < 0
3619 AND transaction_id = i_trx_id;
3620
3621 EXCEPTION
3622 WHEN OTHERS THEN
3623 err_code := 'neg_final_completion';
3624 err_num := SQLCODE;
3625 err_msg := 'CSTPACWC:' || 'neg_final_comletion: ' ||
3626 to_char(stmt_num) || ':' || substr(SQLERRM,1,150);
3627
3628 END neg_final_completion;
3629
3630 PROCEDURE assembly_return (
3631 i_trx_id IN NUMBER,
3632 i_txn_qty IN NUMBER,
3633 i_wip_entity_id IN NUMBER,
3634 i_org_id IN NUMBER,
3635 i_inv_item_id IN NUMBER,
3636 i_cost_type_id IN NUMBER,
3637 i_layer_id IN NUMBER,
3638 i_movhd_cost_type_id OUT NOCOPY NUMBER,
3639 i_res_cost_type_id IN NUMBER,
3640 i_user_id IN NUMBER,
3641 i_login_id IN NUMBER,
3642 i_request_id IN NUMBER,
3643 i_prog_id IN NUMBER,
3644 i_prog_appl_id IN NUMBER,
3645 err_num OUT NOCOPY NUMBER,
3646 err_code OUT NOCOPY VARCHAR2,
3647 err_msg OUT NOCOPY VARCHAR2)
3648
3649 is
3650
3651 stmt_num NUMBER;
3652 l_system_option_id NUMBER;
3653 i_lot_size NUMBER;
3654 l_comp_cost_source NUMBER;
3655 l_c_cost_type_id NUMBER;
3656 l_insert_ind NUMBER;
3657 l_use_val_cost_type NUMBER;
3658 l_routing_check NUMBER := 0;
3659 l_qty_per_assy NUMBER; /* bug 3504776 */
3660 l_include_comp_yield NUMBER;
3661
3662
3663 BEGIN
3664
3665 /***************************************************
3666 * Update temp_relieved_value to zero in all tables *
3667 ***************************************************/
3668
3669 stmt_num := 10;
3670
3671 UPDATE WIP_REQ_OPERATION_COST_DETAILS
3672 SET temp_relieved_value = 0
3673 where
3674 WIP_ENTITY_ID = i_wip_entity_id;
3675
3676 stmt_num := 20;
3677
3678 UPDATE WIP_OPERATION_RESOURCES
3679 SET temp_relieved_value = 0
3680 where
3681 WIP_ENTITY_ID = i_wip_entity_id;
3682
3683 stmt_num := 30;
3684
3685 UPDATE WIP_OPERATION_OVERHEADS
3686 SET temp_relieved_value = 0
3687 where
3688 WIP_ENTITY_ID = i_wip_entity_id;
3689
3690 stmt_num := 40;
3691
3692
3693
3694 select wac.completion_cost_source, nvl(wac.cost_type_id,-1),
3695 wdj.start_quantity,nvl(system_option_id,-1)
3696 into l_comp_cost_source,l_c_cost_type_id,i_lot_size,
3697 l_system_option_id
3698 from
3699 wip_accounting_classes wac,
3700 wip_discrete_jobs wdj
3701 where
3702 wdj.wip_entity_id = i_wip_entity_id and
3703 wdj.organization_id = i_org_id and
3704 wdj.class_code = wac.class_code and
3705 wdj.organization_id = wac.organization_id;
3706
3707
3708 l_insert_ind := 0;
3709
3710 stmt_num := 60;
3711
3712 /*---------------------------------------------------
3713 | If a non-std job has no bill or routing associated
3714 | with it or if a std job has no bill or routing
3715 | associated with it - these need to be treated
3716 | specially.
3717 |-----------------------------------------------------+*/
3718
3719 SELECT
3720 decode(job_type,
3721 1,decode(bom_revision,
3722 NULL,decode(routing_revision,NULL,-1,1),
3723 1),
3724 3,decode(bom_reference_id,
3725 NULL,decode(routing_reference_id,NULL,-1,1),
3726 1),
3727 1)
3728 into
3729 l_use_val_cost_type
3730 from
3731 WIP_DISCRETE_JOBS
3732 WHERE
3733 WIP_ENTITY_ID = i_wip_entity_id AND
3734 ORGANIZATION_ID = i_org_id;
3735
3736 /* Bug 3504776 - the standard material requirements can be added manually for the job.
3737 In this case, we want to derive the completion costs based on job costs */
3738 if (l_use_val_cost_type = -1) then
3739 /* Commented for Bug6734270.If there is a resource added manually
3740 then also the l_use_val_cost_type should be 1
3741 select count(*)
3742 into l_qty_per_assy
3743 from wip_requirement_operations
3744 where wip_entity_id = i_wip_entity_id
3745 and quantity_per_assembly <>0;
3746 */
3747 SELECT COUNT(1)
3748 INTO l_qty_per_assy
3749 FROM dual
3750 WHERE EXISTS ( SELECT NULL
3751 FROM wip_requirement_operations wro
3752 WHERE wro.wip_entity_id = i_wip_entity_id
3753 AND wro.quantity_per_assembly <>0
3754 UNION ALL
3755 SELECT NULL
3756 FROM wip_operation_resources wor
3757 WHERE wor.wip_entity_id = i_wip_entity_id
3758 AND wor.usage_rate_or_amount <>0
3759 );
3760
3761
3762 if (l_qty_per_assy > 0) then
3763 l_use_val_cost_type := 1;
3764 end if;
3765 end if;
3766
3767 /*----------------------------------------------
3768 | If the completions are costed by the system, we
3769 | follow the system rules for earning material
3770 | ovhd upon completion. If the completion is
3771 | costed by the cost type then we will earn
3772 | material overhead based on the costs in the cost type
3773 | We need to figure out, for the given job, where the
3774 | costs are coming from and hence how MO is to be
3775 | earned. This info will passed back to the calling
3776 | rotuine and used by the cost processor.
3777 |--------------------------------------------------+*/
3778
3779 stmt_num := 90;
3780
3781 IF (l_comp_cost_source=1) THEN
3782 i_movhd_cost_type_id:= i_res_cost_type_id;
3783 ELSE i_movhd_cost_type_id:=l_c_cost_type_id;
3784 END IF;
3785
3786 /*-------------------------------------------------
3787 | If the Completions are performed from a User spec
3788 | cost type, the returns should also be performed
3789 | from that cost type. So, check this condition.
3790 --------------------------------------------------*/
3791
3792 /*-------------------------------------------------
3793 | As in the case of completions from a cost type, if
3794 | the cost type specified is different from the avg
3795 | cost type we drive from CICD.
3796 |--------------------------------------------------*/
3797
3798 IF (l_comp_cost_source = 2 AND l_c_cost_type_id > 0 AND
3799 l_c_cost_type_id <> 2) THEN
3800
3801 l_insert_ind := 1;
3802
3803 stmt_num := 70;
3804
3805 INSERT INTO mtl_cst_txn_cost_details
3806 (
3807 TRANSACTION_ID,
3808 ORGANIZATION_ID,
3809 INVENTORY_ITEM_ID,
3810 COST_ELEMENT_ID,
3811 LEVEL_TYPE,
3812 TRANSACTION_COST,
3813 NEW_AVERAGE_COST,
3814 PERCENTAGE_CHANGE,
3815 VALUE_CHANGE,
3816 LAST_UPDATE_DATE,
3817 LAST_UPDATED_BY,
3818 CREATION_DATE,
3819 CREATED_BY,
3820 LAST_UPDATE_LOGIN,
3821 REQUEST_ID,
3822 PROGRAM_APPLICATION_ID,
3823 PROGRAM_ID,
3824 PROGRAM_UPDATE_DATE)
3825 SELECT
3826 i_trx_id,
3827 i_org_id,
3828 i_inv_item_id,
3829 COST_ELEMENT_ID,
3830 LEVEL_TYPE,
3831 SUM(ITEM_COST),
3832 NULL,
3833 NULL,
3834 NULL,
3835 SYSDATE,
3836 i_user_id,
3837 SYSDATE,
3838 i_user_id,
3839 i_login_id,
3840 i_request_id,
3841 i_prog_appl_id,
3842 i_prog_id,
3843 SYSDATE
3844 FROM
3845 CST_ITEM_COST_DETAILS
3846 WHERE
3847 INVENTORY_ITEM_ID = I_INV_ITEM_ID AND
3848 ORGANIZATION_ID = I_ORG_ID AND
3849 COST_TYPE_ID = L_C_COST_TYPE_ID AND
3850 NOT (COST_ELEMENT_ID = 2 AND
3851 LEVEL_TYPE = 1)
3852 GROUP BY COST_ELEMENT_ID,LEVEL_TYPE
3853 HAVING SUM(ITEM_COST) <> 0;
3854
3855
3856
3857 /*------------------------------------------------------
3858 | If completions are from a cost type and the cost type
3859 | is the average cost type, drive from CLCD.
3860 | OR
3861 | If completions are supposed to be system derived but
3862 | the job has no bill/routing(==> we use valuation
3863 | cost type).
3864 |-----------------------------------------------------*/
3865
3866 ELSIF((l_comp_cost_source = 2 AND l_c_cost_type_id = 2)
3867 OR
3868 (l_comp_cost_source = 1 AND l_use_val_cost_type = -1)) THEN
3869
3870
3871 l_insert_ind := 1;
3872
3873 stmt_num := 80;
3874
3875 INSERT INTO mtl_cst_txn_cost_details
3876 (
3877 TRANSACTION_ID,
3878 ORGANIZATION_ID,
3879 INVENTORY_ITEM_ID,
3880 COST_ELEMENT_ID,
3881 LEVEL_TYPE,
3882 TRANSACTION_COST,
3883 NEW_AVERAGE_COST,
3884 PERCENTAGE_CHANGE,
3885 VALUE_CHANGE,
3886 LAST_UPDATE_DATE,
3887 LAST_UPDATED_BY,
3888 CREATION_DATE,
3889 CREATED_BY,
3890 LAST_UPDATE_LOGIN,
3891 REQUEST_ID,
3892 PROGRAM_APPLICATION_ID,
3893 PROGRAM_ID,
3894 PROGRAM_UPDATE_DATE)
3895 SELECT
3896 i_trx_id,
3897 i_org_id,
3898 i_inv_item_id,
3899 COST_ELEMENT_ID,
3900 LEVEL_TYPE,
3901 ITEM_COST,
3902 NULL,
3903 NULL,
3904 NULL,
3905 SYSDATE,
3906 i_user_id,
3907 SYSDATE,
3908 i_user_id,
3909 i_login_id,
3910 i_request_id,
3911 i_prog_appl_id,
3912 i_prog_id,
3913 SYSDATE
3914 FROM
3915 CST_LAYER_COST_DETAILS
3916 WHERE
3917 LAYER_ID = i_layer_id AND
3918 NOT (COST_ELEMENT_ID = 2 AND
3919 LEVEL_TYPE = 1);
3920
3921 /*---------------------------------------------------
3922 New Assembly Return Algorithm
3923
3924 For System I (User-defined)
3925 ---------------------------
3926 - PL materials (Both System I and System II)
3927 - Resources
3928 - Ovhd
3929 If CompU = 0 OR CompV = 0
3930 CompU, CompV = unchanged
3931
3932 If Sign(CompU) <> Sign(CompV)
3933 CompU, CompV = unchanged
3934
3935 If Sign(CompU) = Sign(CompV)
3936 IF CompU > Q*Usage_rate => QTR (quantity to Relieve)
3937 CompU = CompU + Q*Usage_rate
3938 CompV = CompV + CompV/CompU * Q * Usage_rate
3939
3940 IF CompU = Q*Usage
3941 CompU = 0
3942 CompV = 0
3943
3944 IF CompU < Q*Usage
3945 IF CompU and CompV > 0
3946 CompU = 0
3947 CompV = 0
3948
3949 IF CompU and CompV < 0
3950 CompU, CompV = unchanged
3951
3952
3953 For System II (Actual)
3954 ---------------------
3955 - Resources
3956 - Ovhd
3957
3958 IF CompU < 0
3959 CompU and CompV unchanged
3960
3961 ----------------------------------------------------*/
3962
3963 /*---------------------------------------------------
3964 | If completion costs are system calculated ...
3965 |---------------------------------------------------*/
3966
3967 ELSIF (l_comp_cost_source = 1 AND l_use_val_cost_type <> -1) THEN
3968
3969
3970 /******************************************************
3971 * Compute PL Costs for WIP Assembly Return *
3972 ******************************************************/
3973
3974 /* Bug fix for 918694
3975 Assembly return for PL cost elements should be the same
3976 as TL resource, ovhd and OSP.
3977 ie. using actual alogrithm to return for all the PL/TL
3978 cost elements no matter what system alogrithm users
3979 has chosen.
3980 I am still keeping the codes but comment out
3981 in case users request in the future.
3982 */
3983
3984 stmt_num := 100;
3985
3986 /* Bug fix for 2138569
3987 For jobs that have no routing, but the assembly has a BOM.
3988 The table cst_comp_snap_temp does not get populated and thus
3989 there are no rows in cst_comp_snapshot table. Resulting which
3990 the calculations based on the CCS table returns zero value.
3991 And value of Assembly returned is incorrect.
3992
3993 To fix this, have decided to check if the wip_operations table
3994 has any rows for the job(wip_entity_id). If no rows exist then
3995 will use the WRO and WROCD tables to calculate the value else
3996 will use the CCS table to calculate the values.
3997 */
3998
3999 l_routing_check := 0;
4000
4001 select count(1)
4002 into l_routing_check
4003 from wip_operations wo
4004 where wo.wip_entity_id = i_wip_entity_id;
4005
4006 if l_routing_check > 0
4007 then
4008
4009 stmt_num := 105;
4010
4011 UPDATE wip_req_operation_cost_details w1
4012 SET
4013 (temp_relieved_value,
4014 relieved_matl_completion_value) =
4015 (SELECT
4016 --
4017 -- temp_relieved_value
4018 --
4019 decode(SIGN(w2.relieved_matl_completion_value),sign(wro.quantity_per_assembly),
4020 nvl(W2.relieved_matl_completion_value,0)*
4021 decode(abs(i_txn_qty),
4022 prior_completion_quantity,-1,
4023 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4024 prior_completion_quantity)),
4025 0),
4026 ---
4027 --- relieved_matl_completion_value
4028 ---
4029 nvl(w1.relieved_matl_completion_value,0)+
4030 decode(SIGN(w2.relieved_matl_completion_value),sign(wro.quantity_per_assembly),
4031 nvl(w2.relieved_matl_completion_value,0)*
4032 decode(abs(i_txn_qty),
4033 prior_completion_quantity,-1,
4034 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4035 prior_completion_quantity)),
4036 0)
4037 FROM
4038 wip_req_operation_cost_details w2,
4039 wip_requirement_operations wro,
4040 cst_comp_snapshot cocd
4041 WHERE
4042 w1.wip_entity_id = w2.wip_entity_id AND
4043 w1.organization_id = w2.organization_id AND
4044 w1.operation_seq_num = w2.operation_seq_num AND
4045 w1.inventory_item_id = w2.inventory_item_id AND
4046 w1.cost_element_id = w2.cost_element_id AND
4047 w2.wip_entity_id = cocd.wip_entity_id AND
4048 w2.operation_seq_num = cocd.operation_seq_num AND
4049 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
4050 cocd.new_operation_flag = 2 AND */
4051 cocd.transaction_id = i_trx_id AND
4052 wro.wip_entity_id = w2.wip_entity_id AND
4053 wro.organization_id = w2.organization_id AND
4054 wro.inventory_item_id = w2.inventory_item_id AND
4055 wro.operation_seq_num = w2.operation_seq_num
4056 )
4057 WHERE
4058 w1.wip_entity_id = i_wip_entity_id AND
4059 w1.organization_id = i_org_id;
4060
4061 stmt_num := 107;
4062
4063 UPDATE wip_requirement_operations w1
4064 SET
4065 relieved_matl_completion_qty =
4066 (SELECT
4067 --
4068 -- relieved_matl_completion_qty
4069 --
4070 nvl(w1.relieved_matl_completion_qty,0)+
4071 decode(SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),sign(w1.quantity_per_assembly),
4072 nvl(w2.relieved_matl_completion_qty,0)*
4073 decode(abs(i_txn_qty),
4074 prior_completion_quantity,-1,
4075 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4076 prior_completion_quantity)),
4077 0)
4078 FROM
4079 wip_req_operation_cost_details wrocd,
4080 wip_requirement_operations w2,
4081 cst_comp_snapshot cocd
4082 WHERE
4083 w1.wip_entity_id = w2.wip_entity_id AND
4084 w1.inventory_item_id = w2.inventory_item_id AND
4085 w1.operation_seq_num = w2.operation_seq_num AND
4086 w1.organization_id = w2.organization_id AND
4087 w2.wip_entity_id = wrocd.wip_entity_id AND
4088 w2.organization_id = wrocd.organization_id AND
4089 w2.operation_seq_num = wrocd.operation_seq_num AND
4090 w2.inventory_item_id = wrocd.inventory_item_id AND
4091 w2.wip_entity_id = cocd.wip_entity_id AND
4092 w2.operation_seq_num = cocd.operation_seq_num AND
4093 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
4094 cocd.new_operation_flag = 2 AND */
4095 cocd.transaction_id = i_trx_id
4096 GROUP BY
4097 w2.wip_entity_id,
4098 w2.organization_id,
4099 w2.inventory_item_id,
4100 w2.operation_seq_num,
4101 prior_completion_quantity,
4102 w2.relieved_matl_completion_qty
4103 )
4104 WHERE
4105 w1.wip_entity_id = i_wip_entity_id AND
4106 w1.organization_id = i_org_id;
4107
4108 else
4109
4110 stmt_num := 110;
4111
4112 /* Get the value of Include Component yield flag, which will
4113 determine whether to include or not component yield factor in
4114 quantity per assembly*/
4115 SELECT nvl(include_component_yield, 1)
4116 INTO l_include_comp_yield
4117 FROM wip_parameters
4118 WHERE organization_id = i_org_id;
4119
4120 stmt_num := 115;
4121
4122 UPDATE wip_req_operation_cost_details w1
4123 SET
4124 (temp_relieved_value,
4125 relieved_matl_completion_value) =
4126 (SELECT
4127 ---
4128 --- temp_relieved_value
4129 ---
4130 DECODE(wro.relieved_matl_completion_qty,0,
4131 0,
4132 NULL,
4133 0,
4134 DECODE(w1.relieved_matl_completion_value,0,
4135 0,
4136 NULL,
4137 0,
4138 DECODE(SIGN(wro.relieved_matl_completion_qty),
4139 SIGN(w1.relieved_matl_completion_value),
4140 DECODE(SIGN(wro.relieved_matl_completion_qty-
4141 /* LBM project Changes */
4142 ABS(i_txn_qty)*(decode(wro.basis_type, 2,
4143 wro.quantity_per_assembly/i_lot_size,
4144 wro.quantity_per_assembly)/
4145 decode(l_include_comp_yield,
4146 1, nvl(wro.component_yield_factor,1),
4147 1))),
4148 SIGN(wro.quantity_per_assembly),
4149 /* LBM project Changes */
4150 i_txn_qty*(decode(wro.basis_type, 2,
4151 wro.quantity_per_assembly/i_lot_size,
4152 wro.quantity_per_assembly)/
4153 decode(l_include_comp_yield,
4154 1, nvl(wro.component_yield_factor,1),
4155 1))*
4156 relieved_matl_completion_value/
4157 wro.relieved_matl_completion_qty,
4158 0,
4159 -1*relieved_matl_completion_value,
4160 DECODE(SIGN(wro.relieved_matl_completion_qty),
4161 SIGN(wro.quantity_per_assembly),
4162 -1*relieved_matl_completion_value,
4163 0)),
4164 0))),
4165 ---
4166 --- relieved_matl_completion_value
4167 ---
4168 NVL(relieved_matl_completion_value,0)+
4169 DECODE(wro.relieved_matl_completion_qty,0,
4170 0,
4171 NULL,
4172 0,
4173 DECODE(w1.relieved_matl_completion_value,0,
4174 0,
4175 NULL,
4176 0,
4177 DECODE(SIGN(wro.relieved_matl_completion_qty),
4178 SIGN(w1.relieved_matl_completion_value),
4179 DECODE(SIGN(wro.relieved_matl_completion_qty-
4180 /* LBM project Changes */
4181 ABS(i_txn_qty)*(decode(wro.basis_type, 2,
4182 wro.quantity_per_assembly/i_lot_size,
4183 wro.quantity_per_assembly)/
4184 decode(l_include_comp_yield,
4185 1, nvl(wro.component_yield_factor,1),
4186 1))),
4187 SIGN(wro.quantity_per_assembly),
4188 /* LBM project Changes */
4189 i_txn_qty*(decode(wro.basis_type, 2,
4190 wro.quantity_per_assembly/i_lot_size,
4191 wro.quantity_per_assembly)/
4192 decode(l_include_comp_yield,
4193 1, nvl(wro.component_yield_factor,1),
4194 1))*
4195 relieved_matl_completion_value/
4196 wro.relieved_matl_completion_qty,
4197 0,
4198 -1*relieved_matl_completion_value,
4199 DECODE(SIGN(wro.relieved_matl_completion_qty),
4200 SIGN(wro.quantity_per_assembly),
4201 -1*relieved_matl_completion_value,
4202 0)),
4203 0)))
4204 FROM
4205 wip_req_operation_cost_details w2,
4206 wip_requirement_operations wro
4207 WHERE
4208 w1.wip_entity_id = w2.wip_entity_id AND
4209 w1.organization_id = w2.organization_id AND
4210 w1.operation_seq_num = w2.operation_seq_num AND
4211 w1.inventory_item_id = w2.inventory_item_id AND
4212 w1.cost_element_id = w2.cost_element_id AND
4213 w2.wip_entity_id = wro.wip_entity_id AND
4214 w2.organization_id = wro.organization_id AND
4215 w2.operation_seq_num = wro.operation_seq_num AND
4216 w2.inventory_item_id = wro.inventory_item_id
4217 )
4218 WHERE
4219 (w1.wip_entity_id, w1.organization_id,
4220 w1.inventory_item_id, w1.operation_seq_num) IN
4221 (SELECT
4222 wip_entity_id, organization_id,
4223 inventory_item_id,operation_seq_num
4224 FROM
4225 wip_requirement_operations wro2
4226 WHERE
4227 wro2.wip_entity_id = i_wip_entity_id AND
4228 wro2.organization_id = i_org_id AND
4229 wro2.quantity_per_assembly <> 0);
4230
4231 stmt_num := 117;
4232
4233 UPDATE wip_requirement_operations w
4234 SET relieved_matl_completion_qty =
4235 (SELECT
4236 NVL(w.relieved_matl_completion_qty,0)+
4237 DECODE(w.relieved_matl_completion_qty,0,
4238 0,
4239 NULL,
4240 0,
4241 DECODE(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0)),0,
4242 0,
4243 NULL,
4244 0,
4245 DECODE(SIGN(w.relieved_matl_completion_qty),
4246 SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),
4247 DECODE(SIGN(w.relieved_matl_completion_qty-
4248 /* LBM project Changes */
4249 ABS(i_txn_qty)*(decode(w.basis_type, 2,
4250 w.quantity_per_assembly/i_lot_size,
4251 w.quantity_per_assembly)/
4252 decode(l_include_comp_yield,
4253 1, nvl(w.component_yield_factor,1),
4254 1))),
4255 SIGN(w.quantity_per_assembly),
4256 /* LBM project Changes */
4257 i_txn_qty*(decode(w.basis_type, 2,
4258 w.quantity_per_assembly/i_lot_size,
4259 w.quantity_per_assembly)/
4260 decode(l_include_comp_yield,
4261 1, nvl(w.component_yield_factor,1),
4262 1)),
4263 0,
4264 -1*relieved_matl_completion_qty,
4265 DECODE(SIGN(w.relieved_matl_completion_qty),
4266 SIGN(w.quantity_per_assembly),
4267 -1*relieved_matl_completion_qty,
4268 0)),
4269 0)))
4270 FROM
4271 wip_req_operation_cost_details wrocd,
4272 wip_requirement_operations w2
4273 WHERE
4274 w.wip_entity_id = w2.wip_entity_id AND
4275 w.inventory_item_id = w2.inventory_item_id AND
4276 w.operation_seq_num = w2.operation_seq_num AND
4277 w.organization_id = w2.organization_id AND
4278 w2.wip_entity_id = wrocd.wip_entity_id AND
4279 w2.organization_id = wrocd.organization_id AND
4280 w2.operation_seq_num = wrocd.operation_seq_num AND
4281 w2.inventory_item_id = wrocd.inventory_item_id
4282 GROUP BY
4283 w2.wip_entity_id,
4284 w2.organization_id,
4285 w2.inventory_item_id,
4286 w2.operation_seq_num,
4287 w2.quantity_per_assembly,
4288 w2.relieved_matl_completion_qty
4289 )
4290 WHERE
4291 w.wip_entity_id = i_wip_entity_id AND
4292 w.organization_id = i_org_id AND
4293 w.quantity_per_assembly <> 0;
4294
4295 end if;
4296
4297 /*******************************************************
4298 * Compute TL resource costs for Assembly return ... *
4299 *******************************************************/
4300
4301 /*
4302 R11.5 Assembly Return at average cost
4303 For resources, overheads and OSP
4304 all return using Actual resources algorithm
4305 regardless of which system option
4306 */
4307
4308 -- If the option is to use Actual resources, then go with the
4309 -- snapshot table.
4310
4311 stmt_num := 160;
4312
4313 UPDATE wip_operation_resources w1
4314 SET
4315 (relieved_res_completion_units,
4316 temp_relieved_value,
4317 relieved_res_completion_value) =
4318 (SELECT
4319 --
4320 -- relieved_res_completion_units
4321 --
4322 nvl(w1.relieved_res_completion_units,0)+
4323 decode(SIGN(w2.relieved_res_completion_value),1,
4324 nvl(w2.relieved_res_completion_units,0)*
4325 decode(abs(i_txn_qty),
4326 prior_completion_quantity,-1,
4327 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4328 prior_completion_quantity)),
4329 0),
4330 --
4331 -- temp_relieved_value
4332 --
4333 decode(SIGN(w2.relieved_res_completion_value),1,
4334 nvl(W2.relieved_res_completion_value,0)*
4335 decode(abs(i_txn_qty),
4336 prior_completion_quantity,-1,
4337 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4338 prior_completion_quantity)),
4339 0),
4340 ---
4341 --- relieved_res_completion_value
4342 ---
4343 nvl(w1.relieved_res_completion_value,0)+
4344 decode(SIGN(w2.relieved_res_completion_value),1,
4345 nvl(w2.relieved_res_completion_value,0)*
4346 decode(abs(i_txn_qty),
4347 prior_completion_quantity,-1,
4348 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4349 prior_completion_quantity)),
4350 0)
4351 FROM
4352 wip_operation_resources w2,
4353 cst_comp_snapshot cocd
4354 WHERE
4355 w2.wip_entity_id = w1.wip_entity_id AND
4356 w2.organization_id = w1.organization_id AND
4357 w2.operation_seq_num = w1.operation_seq_num AND
4358 w2.resource_seq_num = w1.resource_seq_num AND
4359 w2.wip_entity_id = cocd.wip_entity_id AND
4360 w2.operation_seq_num = cocd.operation_seq_num AND
4361 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
4362 cocd.new_operation_flag = 2 AND */
4363 cocd.transaction_id = i_trx_id)
4364 WHERE
4365 w1.wip_entity_id = i_wip_entity_id AND
4366 w1.organization_id = i_org_id;
4367
4368
4369
4370 stmt_num := 165;
4371
4372 UPDATE wip_operation_overheads w1
4373 SET
4374 (relieved_ovhd_completion_units,
4375 temp_relieved_value,
4376 relieved_ovhd_completion_value) =
4377 (SELECT
4378 ---
4379 --- relieved_ovhd_completion_units
4380 ---
4381 nvl(w1.relieved_ovhd_completion_units,0)+
4382 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4383 nvl(W2.relieved_ovhd_completion_units,0)*
4384 decode(abs(i_txn_qty),
4385 prior_completion_quantity,-1,
4386 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4387 prior_completion_quantity)),
4388 0),
4389 ---
4390 --- temp_relieved_value
4391 ---
4392 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4393 nvl(w2.relieved_ovhd_completion_value,0)*
4394 decode(abs(i_txn_qty),
4395 prior_completion_quantity,-1,
4396 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4397 prior_completion_quantity)),
4398 0),
4399
4400 ---
4401 --- relieved_ovhd_completion_value
4402 ---
4403 nvl(w1.relieved_ovhd_completion_value,0)+
4404 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4405 nvl(w2.relieved_ovhd_completion_value,0)*
4406 decode(abs(i_txn_qty),
4407 prior_completion_quantity,-1,
4408 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4409 prior_completion_quantity)),
4410 0)
4411
4412 FROM
4413 wip_operation_overheads w2,
4414 cst_comp_snapshot cocd
4415 WHERE
4416 w2.wip_entity_id = w1.wip_entity_id AND
4417 w2.organization_id = w1.organization_id AND
4418 w2.operation_seq_num = w1.operation_seq_num AND
4419 w2.resource_seq_num = w1.resource_seq_num AND
4420 w2.overhead_id = w1.overhead_id AND
4421 w2.basis_type = w1.basis_type AND
4422 w2.wip_entity_id = cocd.wip_entity_id AND
4423 w2.operation_seq_num = cocd.operation_seq_num AND
4424 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
4425 cocd.new_operation_flag = 2 AND */
4426 cocd.transaction_id = i_trx_id)
4427 WHERE
4428 w1.wip_entity_id = i_wip_entity_id AND
4429 w1.organization_id = i_org_id;
4430
4431 END IF;
4432
4433 /************************************************************
4434 * Insert into mtl_cst_txn_cost_details now that the *
4435 * Costs have been computed ... *
4436 * 3 statements are required --> one each for PL costs *
4437 * , TL Res/OSP costs and TL ovhd costs. *
4438 * Remember - the cst_txn_cost_detail tables stores unit *
4439 * cost - but the wip tables store the value in the *
4440 * temp_relieved_value column - so we have to divide by the *
4441 * txn_qty to arrive at the unit cost. *
4442 * Also, this insert should only be performed if the indicat *
4443 * or is <> 1.
4444 ************************************************************/
4445
4446 IF (l_insert_ind <>1) THEN
4447
4448 stmt_num := 270;
4449
4450 INSERT INTO mtl_cst_txn_cost_details
4451 (
4452 TRANSACTION_ID,
4453 ORGANIZATION_ID,
4454 INVENTORY_ITEM_ID,
4455 COST_ELEMENT_ID,
4456 LEVEL_TYPE,
4457 TRANSACTION_COST,
4458 NEW_AVERAGE_COST,
4459 PERCENTAGE_CHANGE,
4460 VALUE_CHANGE,
4461 LAST_UPDATE_DATE,
4462 LAST_UPDATED_BY,
4463 CREATION_DATE,
4464 CREATED_BY,
4465 LAST_UPDATE_LOGIN,
4466 REQUEST_ID,
4467 PROGRAM_APPLICATION_ID,
4468 PROGRAM_ID,
4469 PROGRAM_UPDATE_DATE)
4470 SELECT
4471 i_trx_id,
4472 i_org_id,
4473 i_inv_item_id,
4474 wrocd.cost_element_id,
4475 2,
4476 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
4477 NULL,
4478 NULL,
4479 NULL,
4480 SYSDATE,
4481 i_user_id,
4482 SYSDATE,
4483 i_user_id,
4484 i_login_id,
4485 i_request_id,
4486 i_prog_appl_id,
4487 i_prog_id,
4488 SYSDATE
4489 FROM
4490 WIP_REQ_OPERATION_COST_DETAILS wrocd
4491 where
4492 WIP_ENTITY_ID = i_wip_entity_id AND
4493 ORGANIZATION_ID = i_org_id
4494 GROUP BY wrocd.cost_element_id
4495 HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
4496
4497 stmt_num := 290;
4498
4499 INSERT INTO mtl_cst_txn_cost_details
4500 (
4501 TRANSACTION_ID,
4502 ORGANIZATION_ID,
4503 INVENTORY_ITEM_ID,
4504 COST_ELEMENT_ID,
4505 LEVEL_TYPE,
4506 TRANSACTION_COST,
4507 NEW_AVERAGE_COST,
4508 PERCENTAGE_CHANGE,
4509 VALUE_CHANGE,
4510 LAST_UPDATE_DATE,
4511 LAST_UPDATED_BY,
4512 CREATION_DATE,
4513 CREATED_BY,
4514 LAST_UPDATE_LOGIN,
4515 REQUEST_ID,
4516 PROGRAM_APPLICATION_ID,
4517 PROGRAM_ID,
4518 PROGRAM_UPDATE_DATE)
4519 SELECT
4520 i_trx_id,
4521 i_org_id,
4522 i_inv_item_id,
4523 br.cost_element_id,
4524 1,
4525 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
4526 NULL,
4527 NULL,
4528 NULL,
4529 SYSDATE,
4530 i_user_id,
4531 SYSDATE,
4532 i_user_id,
4533 i_login_id,
4534 i_request_id,
4535 i_prog_appl_id,
4536 i_prog_id,
4537 SYSDATE
4538 FROM
4539 BOM_RESOURCES BR,
4540 WIP_OPERATION_RESOURCES WOR
4541 WHERE
4542 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
4543 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
4544 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
4545 WOR.ORGANIZATION_ID = i_org_id
4546 GROUP BY BR.COST_ELEMENT_ID
4547 HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
4548
4549 stmt_num := 310;
4550
4551 INSERT INTO mtl_cst_txn_cost_details
4552 (
4553 TRANSACTION_ID,
4554 ORGANIZATION_ID,
4555 INVENTORY_ITEM_ID,
4556 COST_ELEMENT_ID,
4557 LEVEL_TYPE,
4558 TRANSACTION_COST,
4559 NEW_AVERAGE_COST,
4560 PERCENTAGE_CHANGE,
4561 VALUE_CHANGE,
4562 LAST_UPDATE_DATE,
4563 LAST_UPDATED_BY,
4564 CREATION_DATE,
4565 CREATED_BY,
4566 LAST_UPDATE_LOGIN,
4567 REQUEST_ID,
4568 PROGRAM_APPLICATION_ID,
4569 PROGRAM_ID,
4570 PROGRAM_UPDATE_DATE)
4571 SELECT
4572 i_trx_id,
4573 i_org_id,
4574 i_inv_item_id,
4575 5,
4576 1,
4577 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
4578 NULL,
4579 NULL,
4580 NULL,
4581 SYSDATE,
4582 i_user_id,
4583 SYSDATE,
4584 i_user_id,
4585 i_login_id,
4586 i_request_id,
4587 i_prog_appl_id,
4588 i_prog_id,
4589 SYSDATE
4590 FROM
4591 WIP_OPERATION_OVERHEADS
4592 WHERE
4593 WIP_ENTITY_ID = i_wip_entity_id AND
4594 ORGANIZATION_ID = i_org_id
4595 HAVING
4596 SUM(nvl(temp_relieved_value,0)) <> 0;
4597
4598 END IF;
4599
4600 EXCEPTION
4601 WHEN OTHERS THEN
4602 err_num := SQLCODE;
4603 err_msg := 'CSTPACWC:' || 'assembly_return:' || to_char(stmt_num) ||
4604 ' ' || substr(SQLERRM,1,150);
4605
4606 END assembly_return;
4607
4608 END CSTPACWC;