[Home] [Help]
PACKAGE BODY: APPS.CSTPACWC
Source
1 PACKAGE BODY CSTPACWC AS
2 /* $Header: CSTPACCB.pls 120.8.12010000.4 2008/10/28 00:10:00 jkwac 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 (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))* /* Added l_future_issued_qty for bug 4259782 */
1456 nvl(decode(cost_element_id,
1457 1,cql.material_cost,
1458 2,cql.material_overhead_cost,
1459 3,cql.resource_cost,
1460 4,cql.outside_processing_cost,
1461 5,cql.overhead_cost),0),
1462 /* Bug 3479419: AVTR = 0 End*/
1463 SIGN(wro.quantity_per_assembly),
1464 (nvl(applied_matl_value,0)-
1465 nvl(relieved_matl_completion_value,0)-
1466 nvl(relieved_variance_value,0)-
1467 nvl(relieved_matl_scrap_value,0)+
1468 /* LBM project Changes */
1469 (i_txn_qty*(decode(wro.basis_type, 2,
1470 wro.quantity_per_assembly/l_lot_size,
1471 wro.quantity_per_assembly)/
1472 decode(l_include_comp_yield,
1473 1, nvl(wro.component_yield_factor,1),
1474 1))-
1475 (wro.quantity_issued -
1476 nvl(wro.relieved_matl_completion_qty,0) -
1477 nvl(wro.relieved_matl_final_comp_qty,0) -
1478 nvl(wro.relieved_matl_scrap_quantity,0) +
1479 l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
1480 nvl(decode(cost_element_id,
1481 1,cql.material_cost,
1482 2,cql.material_overhead_cost,
1483 3,cql.resource_cost,
1484 4,cql.outside_processing_cost,
1485 5,cql.overhead_cost),0)),
1486 /* LBM project Changes */
1487 i_txn_qty*(decode(wro.basis_type, 2,
1488 wro.quantity_per_assembly/l_lot_size,
1489 wro.quantity_per_assembly)/
1490 decode(l_include_comp_yield,
1491 1, nvl(wro.component_yield_factor,1),
1492 1))*
1493 nvl(decode(cost_element_id,
1494 1,cql.material_cost,
1495 2,cql.material_overhead_cost,
1496 3,cql.resource_cost,
1497 4,cql.outside_processing_cost,
1498 5,cql.overhead_cost),0))),
1499
1500 nvl(w1.relieved_matl_completion_value,0)+
1501 /* LBM project Changes */
1502 decode(SIGN(nvl(wro.quantity_issued,0)-
1503 nvl(wro.relieved_matl_completion_qty,0)-
1504 nvl(wro.relieved_matl_final_comp_qty,0)-
1505 nvl(wro.relieved_matl_scrap_quantity,0)-
1506 /* LBM project Changes */
1507 i_txn_qty*(decode(wro.basis_type, 2, wro.quantity_per_assembly/l_lot_size,
1508 wro.quantity_per_assembly)/
1509 decode(l_include_comp_yield,
1510 1, nvl(wro.component_yield_factor,1),
1511 1)) + l_future_issued_qty), /* Added l_future_issued_qty for bug 4259782 */
1512 SIGN(wro.quantity_per_assembly),
1513 /* LBM project Changes */
1514 i_txn_qty*(decode(wro.basis_type, 2,
1515 wro.quantity_per_assembly/l_lot_size,
1516 wro.quantity_per_assembly)/
1517 decode(l_include_comp_yield,
1518 1, nvl(wro.component_yield_factor,1),
1519 1))*
1520 decode(SIGN(nvl(applied_matl_value,0)-
1521 nvl(relieved_matl_completion_value,0)-
1522 nvl(relieved_variance_value,0)-
1523 nvl(relieved_matl_scrap_value,0)),
1524 /* Bug 3479419: AVTR = 0 Start*/
1525 0, 0,
1526 /* Bug 3479419: AVTR = 0 End*/
1527 SIGN(wro.quantity_per_assembly),
1528 ( nvl(applied_matl_value,0)-
1529 nvl(relieved_matl_completion_value,0)-
1530 nvl(relieved_variance_value,0)-
1531 nvl(relieved_matl_scrap_value,0))
1532 /(wro.quantity_issued-
1533 nvl(wro.relieved_matl_completion_qty,0)-
1534 nvl(wro.relieved_matl_final_comp_qty,0)-
1535 nvl(wro.RELIEVED_MATL_SCRAP_QUANTITY,0)+
1536 l_future_issued_qty), /* Fix for bug 2158763 */
1537 nvl(decode(cost_element_id,
1538 1,cql.material_cost,
1539 2,cql.material_overhead_cost,
1540 3,cql.resource_cost,
1541 4,cql.outside_processing_cost,
1542 5,cql.overhead_cost),0)),
1543 0,
1544 decode(SIGN(nvl(applied_matl_value,0)-
1545 nvl(relieved_matl_completion_value,0)-
1546 nvl(relieved_variance_value,0)-
1547 nvl(relieved_matl_scrap_value,0)),
1548 /* Bug 3479419: AVTR = 0 Start*/
1549 0, 0,
1550 /* Bug 3479419: AVTR = 0 End*/
1551 SIGN(wro.quantity_per_assembly),
1552 (nvl(applied_matl_value,0)-
1553 nvl(relieved_matl_completion_value,0)-
1554 nvl(relieved_variance_value,0)-
1555 nvl(relieved_matl_scrap_value,0)),
1556 /* LBM project Changes */
1557 i_txn_qty*(decode(wro.basis_type, 2,
1558 wro.quantity_per_assembly/l_lot_size,
1559 wro.quantity_per_assembly)/
1560 decode(l_include_comp_yield,
1561 1, nvl(wro.component_yield_factor,1),
1562 1))*
1563 nvl(decode(cost_element_id,
1564 1,cql.material_cost,
1565 2,cql.material_overhead_cost,
1566 3,cql.resource_cost,
1567 4,cql.outside_processing_cost,
1568 5,cql.overhead_cost),0)),
1569 -1*SIGN(wro.quantity_per_assembly),
1570 decode(SIGN(nvl(applied_matl_value,0)-
1571 nvl(relieved_matl_completion_value,0)-
1572 nvl(relieved_variance_value,0)-
1573 nvl(relieved_matl_scrap_value,0)),
1574 /* Bug 3479419: AVTR = 0 Start*/
1575 /* LBM project Changes */
1576 0, (i_txn_qty*(decode(wro.basis_type, 2,
1577 wro.quantity_per_assembly/l_lot_size,
1578 wro.quantity_per_assembly)/
1579 decode(l_include_comp_yield,
1580 1, nvl(wro.component_yield_factor,1),
1581 1)) -
1582 (wro.quantity_issued -
1583 nvl(wro.relieved_matl_completion_qty,0) -
1584 nvl(wro.relieved_matl_final_comp_qty,0) -
1585 nvl(wro.relieved_matl_scrap_quantity,0) +
1586 l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
1587 nvl(decode(cost_element_id,
1588 1,cql.material_cost,
1589 2,cql.material_overhead_cost,
1590 3,cql.resource_cost,
1591 4,cql.outside_processing_cost,
1592 5,cql.overhead_cost),0),
1593 /* Bug 3479419: AVTR = 0 End*/
1594 SIGN(wro.quantity_per_assembly),
1595 (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 /* LBM project Changes */
1600 (i_txn_qty*(decode(wro.basis_type, 2,
1601 wro.quantity_per_assembly/l_lot_size,
1602 wro.quantity_per_assembly)/
1603 decode(l_include_comp_yield,
1604 1, nvl(wro.component_yield_factor,1),
1605 1))-
1606 (wro.quantity_issued -
1607 nvl(wro.relieved_matl_completion_qty,0) -
1608 nvl(wro.relieved_matl_final_comp_qty,0) -
1609 nvl(wro.relieved_matl_scrap_quantity,0) +
1610 l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
1611 nvl(decode(cost_element_id,
1612 1,cql.material_cost,
1613 2,cql.material_overhead_cost,
1614 3,cql.resource_cost,
1615 4,cql.outside_processing_cost,
1616 5,cql.overhead_cost),0)),
1617 /* LBM project Changes */
1618 i_txn_qty*(decode(wro.basis_type, 2,
1619 wro.quantity_per_assembly/l_lot_size,
1620 wro.quantity_per_assembly)/
1621 decode(l_include_comp_yield,
1622 1, nvl(wro.component_yield_factor,1),
1623 1))*
1624 nvl(decode(cost_element_id,
1625 1,cql.material_cost,
1626 2,cql.material_overhead_cost,
1627 3,cql.resource_cost,
1628 4,cql.outside_processing_cost,
1629 5,cql.overhead_cost),0)))
1630 FROM
1631 wip_req_operation_cost_details w2,
1632 wip_requirement_operations wro,
1633 cst_quantity_layers cql
1634 WHERE
1635 w2.wip_entity_id = w1.wip_entity_id AND
1636 w2.organization_id = w1.organization_id AND
1637 w2.inventory_item_id = w1.inventory_item_id AND
1638 w2.operation_seq_num = w1.operation_seq_num AND
1639 w2.cost_element_id = w1.cost_element_id AND
1640 w2.wip_entity_id = wro.wip_entity_id AND
1641 w2.organization_id = wro.organization_id AND
1642 w2.inventory_item_id = wro.inventory_item_id AND
1643 w2.operation_seq_num = wro.operation_seq_num AND
1644 i_cost_group_id = cql.cost_group_id(+) AND
1645 wro.inventory_item_id = cql.inventory_item_id(+) AND
1646 wro.organization_id = cql.organization_id(+))
1647 WHERE
1648 w1.wip_entity_id = wro_rec.wip_entity_id AND
1649 w1.organization_id = wro_rec.organization_id AND
1650 w1.inventory_item_id = wro_rec.inventory_item_id AND
1651 w1.operation_seq_num = wro_rec.operation_seq_num;
1652 END;
1653 END LOOP;
1654
1655 /*---------------------------------------------------
1656 | Qty must be updated after value ...
1657 |--------------------------------------------------*/
1658
1659
1660 stmt_num := 270;
1661
1662 UPDATE wip_requirement_operations w1
1663 SET
1664 relieved_matl_completion_qty =
1665 (SELECT
1666 nvl(w1.relieved_matl_completion_qty,0) +
1667 /* LBM project Changes */
1668 i_txn_qty*(decode(basis_type, 2,
1669 quantity_per_assembly/l_lot_size,
1670 quantity_per_assembly)/
1671 decode(l_include_comp_yield,
1672 1, nvl(component_yield_factor,1),
1673 1))
1674 FROM
1675 wip_requirement_operations w2
1676 WHERE
1677 w1.wip_entity_id = w2.wip_entity_id AND
1678 w1.organization_id = w2.organization_id AND
1679 w1.inventory_item_id = w2.inventory_item_id AND
1680 w1.operation_seq_num = w2.operation_seq_num)
1681 WHERE
1682 --
1683 -- Exclude bulk, supplier, phantom
1684 --
1685 w1.wip_supply_type not in (4,5,6) AND
1686 w1.wip_entity_id = i_wip_entity_id AND
1687 w1.organization_id = i_org_id AND
1688 w1.quantity_per_assembly <> 0;
1689
1690
1691 -- /******************************************************
1692 -- * Relieve This Level Resource costs/units from WIP ...*
1693 -- ******************************************************/
1694
1695 IF (l_system_option_id = 1) THEN
1696
1697 -- If we use the actual resource option, then use the snapshot for
1698 -- both resources and overheads.
1699
1700 stmt_num := 290;
1701
1702 UPDATE wip_operation_resources w1
1703 SET
1704 (relieved_res_completion_units,
1705 temp_relieved_value,
1706 relieved_res_completion_value) =
1707 (SELECT
1708 nvl(w1.relieved_res_completion_units,0) +
1709 decode(sign(applied_resource_units -
1710 nvl(relieved_res_completion_units,0)-
1711 nvl(relieved_res_final_comp_units,0)-
1712 nvl(relieved_res_scrap_units,0)),
1713 1,
1714 (applied_resource_units -
1715 nvl(relieved_res_completion_units,0)-
1716 nvl(relieved_res_final_comp_units,0)-
1717 nvl(relieved_res_scrap_units,0))*
1718 --
1719 -- new to solve divided by zero and over relieved
1720 -- when txn_qty/completed - prior_completion - prior_scrap
1721 -- is greater than or equal to one, set it to one
1722 -- ie. flush out 1*value remain in the job 1/30/98
1723 --
1724 decode(sign(i_txn_qty - (cocd.quantity_completed -
1725 nvl(prior_completion_quantity,0) -
1726 nvl(prior_scrap_quantity,0))),
1727 -1,i_txn_qty/(cocd.quantity_completed -
1728 nvl(prior_completion_quantity,0) -
1729 nvl(prior_scrap_quantity,0)),
1730 1),
1731 0),
1732 decode(sign(applied_resource_value -
1733 nvl(relieved_res_completion_value,0)-
1734 nvl(relieved_variance_value,0)-
1735 nvl(relieved_res_scrap_value,0)),
1736 1,
1737 (applied_resource_value -
1738 nvl(relieved_res_completion_value,0)-
1739 nvl(relieved_variance_value,0)-
1740 nvl(relieved_res_scrap_value,0))*
1741 --
1742 -- new to solve divided by zero and over relieved
1743 --
1744 decode(sign(i_txn_qty - (cocd.quantity_completed -
1745 nvl(prior_completion_quantity,0) -
1746 nvl(prior_scrap_quantity,0))),
1747 -1,i_txn_qty/(cocd.quantity_completed -
1748 nvl(prior_completion_quantity,0) -
1749 nvl(prior_scrap_quantity,0)),
1750 1),
1751 0),
1752 nvl(w1.relieved_res_completion_value,0) +
1753 decode(sign(applied_resource_value -
1754 nvl(relieved_res_completion_value,0)-
1755 nvl(relieved_variance_value,0)-
1756 nvl(relieved_res_scrap_value,0)),
1757 1,
1758 (applied_resource_value -
1759 nvl(relieved_res_completion_value,0)-
1760 nvl(relieved_variance_value,0)-
1761 nvl(relieved_res_scrap_value,0))*
1762 --
1763 -- new to solve divided by zero and over relieved
1764 --
1765 decode(sign(i_txn_qty - (cocd.quantity_completed -
1766 nvl(prior_completion_quantity,0) -
1767 nvl(prior_scrap_quantity,0))),
1768 -1,i_txn_qty/(cocd.quantity_completed -
1769 nvl(prior_completion_quantity,0) -
1770 nvl(prior_scrap_quantity,0)),
1771 1),
1772 0)
1773 FROM
1774 wip_operation_resources w2,
1775 cst_comp_snapshot cocd
1776 WHERE
1777 w1.wip_entity_id = w2.wip_entity_id AND
1778 w1.operation_seq_num = w2.operation_seq_num AND
1779 w1.resource_seq_num = w2.resource_seq_num AND
1780 w1.organization_id = w2.organization_id AND
1781 w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
1782 w2.operation_seq_num = cocd.operation_seq_num AND
1783 cocd.new_operation_flag = 2 AND
1784 cocd.transaction_id = i_trx_id)
1785 WHERE
1786 w1.wip_entity_id = i_wip_entity_id AND
1787 w1.organization_id = i_org_id;
1788
1789
1790
1791 stmt_num := 295;
1792
1793 UPDATE wip_operation_overheads w1
1794 SET
1795 (relieved_ovhd_completion_units,
1796 temp_relieved_value,
1797 relieved_ovhd_completion_value) =
1798 (SELECT
1799 NVL(w1.relieved_ovhd_completion_units,0) +
1800 decode(sign(applied_ovhd_units -
1801 nvl(relieved_ovhd_completion_units,0)-
1802 nvl(relieved_ovhd_final_comp_units,0)-
1803 nvl(relieved_ovhd_scrap_units,0)),
1804 1,
1805 (applied_ovhd_units -
1806 nvl(relieved_ovhd_completion_units,0)-
1807 nvl(relieved_ovhd_final_comp_units,0)-
1808 nvl(relieved_ovhd_scrap_units,0))*
1809 --
1810 -- new to solve divided by zero and over relieved
1811 --
1812 decode(sign(i_txn_qty - (cocd.quantity_completed -
1813 nvl(prior_completion_quantity,0) -
1814 nvl(prior_scrap_quantity,0))),
1815 -1,i_txn_qty/(cocd.quantity_completed -
1816 nvl(prior_completion_quantity,0) -
1817 nvl(prior_scrap_quantity,0)),
1818 1),
1819 0),
1820 decode(sign(applied_ovhd_value -
1821 nvl(relieved_ovhd_completion_value,0)-
1822 nvl(relieved_variance_value,0)-
1823 nvl(relieved_ovhd_scrap_value,0)),
1824 1,
1825 (applied_ovhd_value -
1826 nvl(relieved_ovhd_completion_value,0)-
1827 nvl(relieved_variance_value,0)-
1828 nvl(relieved_ovhd_scrap_value,0))*
1829 --
1830 -- new to solve divided by zero and over relieved
1831 --
1832 decode(sign(i_txn_qty - (cocd.quantity_completed -
1833 nvl(prior_completion_quantity,0) -
1834 nvl(prior_scrap_quantity,0))),
1835 -1,i_txn_qty/(cocd.quantity_completed -
1836 nvl(prior_completion_quantity,0) -
1837 nvl(prior_scrap_quantity,0)),
1838 1),
1839 0),
1840 nvl(w1.relieved_ovhd_completion_value,0) +
1841 decode(sign(applied_ovhd_value -
1842 nvl(relieved_ovhd_completion_value,0)-
1843 nvl(relieved_variance_value,0)-
1844 nvl(relieved_ovhd_scrap_value,0)),
1845 1,
1846 (applied_ovhd_value -
1847 nvl(relieved_ovhd_completion_value,0)-
1848 nvl(relieved_variance_value,0)-
1849 nvl(relieved_ovhd_scrap_value,0))*
1850 --
1851 -- new to solve divided by zero and over relieved
1852 --
1853 decode(sign(i_txn_qty - (cocd.quantity_completed -
1854 nvl(prior_completion_quantity,0) -
1855 nvl(prior_scrap_quantity,0))),
1856 -1,i_txn_qty/(cocd.quantity_completed -
1857 nvl(prior_completion_quantity,0) -
1858 nvl(prior_scrap_quantity,0)),
1859 1),
1860 0)
1861 FROM
1862 wip_operation_overheads w2,
1863 cst_comp_snapshot cocd
1864 WHERE
1865 w1.wip_entity_id = w2.wip_entity_id AND
1866 w1.operation_seq_num = w2.operation_seq_num AND
1867 w1.resource_seq_num = w2.resource_seq_num AND
1868 w1.overhead_id = w2.overhead_id AND
1869 w1.organization_id = w2.organization_id AND
1870 w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
1871 w1.basis_type = w2.basis_type AND
1872 w2.operation_seq_num = cocd.operation_seq_num AND
1873 cocd.new_operation_flag = 2 AND
1874 cocd.transaction_id = i_trx_id)
1875 WHERE
1876 w1.wip_entity_id = i_wip_entity_id AND
1877 w1.organization_id = i_org_id;
1878
1879
1880 ELSIF (l_system_option_id = 2) THEN
1881
1882 -- Or ... If we are using the Pre defined resource option, use
1883 -- the pre defined rates for resources and overheads.
1884
1885 stmt_num := 300;
1886
1887 UPDATE wip_operation_resources w1
1888 SET
1889 (relieved_res_completion_units,
1890 temp_relieved_value,
1891 relieved_res_completion_value) =
1892 (SELECT
1893 nvl(w1.relieved_res_completion_units,0)+
1894 decode(basis_type,
1895 1,i_txn_qty*usage_rate_or_amount,
1896 2,i_txn_qty*usage_rate_or_amount/l_lot_size,
1897 i_txn_qty*usage_rate_or_amount),
1898 decode(SIGN(applied_resource_units-
1899 nvl(relieved_res_completion_units,0)-
1900 nvl(relieved_res_final_comp_units,0)-
1901 nvl(relieved_res_scrap_units,0)-
1902 i_txn_qty*decode(basis_type,
1903 1,usage_rate_or_amount,
1904 2,usage_rate_or_amount/l_lot_size,
1905 usage_rate_or_amount)),
1906 SIGN(usage_rate_or_amount),
1907 i_txn_qty*decode(basis_type,
1908 1,usage_rate_or_amount,
1909 2,usage_rate_or_amount/l_lot_size,
1910 usage_rate_or_amount)*
1911 decode(SIGN(nvl(applied_resource_value,0)-
1912 nvl(relieved_res_completion_value,0)-
1913 nvl(relieved_variance_value,0)-
1914 nvl(relieved_res_scrap_value,0)),
1915 SIGN(usage_rate_or_amount),
1916 decode(basis_type,
1917 1,((nvl(applied_resource_value,0)-
1918 nvl(relieved_res_completion_value,0)-
1919 nvl(relieved_variance_value,0)-
1920 nvl(relieved_res_scrap_value,0))
1921 /(applied_resource_units-
1922 nvl(relieved_res_completion_units,0)-
1923 nvl(relieved_res_final_comp_units,0)-
1924 nvl(relieved_res_scrap_units,0))),
1925 2,nvl(applied_resource_value,0)/
1926 decode(applied_resource_units,
1927 0,1,applied_resource_units),
1928 ((nvl(applied_resource_value,0)-
1929 nvl(relieved_res_completion_value,0)-
1930 nvl(relieved_variance_value,0)-
1931 nvl(relieved_res_scrap_value,0))
1932 /(applied_resource_units-
1933 nvl(relieved_res_completion_units,0)-
1934 nvl(relieved_res_final_comp_units,0)-
1935 nvl(relieved_res_scrap_units,0)))),
1936 crc.resource_rate),
1937 0,
1938 decode(SIGN(nvl(applied_resource_value,0)-
1939 nvl(relieved_res_completion_value,0)-
1940 nvl(relieved_variance_value,0)-
1941 nvl(relieved_res_scrap_value,0)),
1942 SIGN(usage_rate_or_amount),
1943 (nvl(applied_resource_value,0)-
1944 nvl(relieved_res_completion_value,0)-
1945 nvl(relieved_variance_value,0)-
1946 nvl(relieved_res_scrap_value,0)),
1947 i_txn_qty*decode(basis_type,
1948 1,usage_rate_or_amount,
1949 2,usage_rate_or_amount/l_lot_size,
1950 usage_rate_or_amount)*
1951 crc.resource_rate),
1952 -1*SIGN(usage_rate_or_amount),
1953 decode(SIGN(nvl(applied_resource_value,0)-
1954 nvl(relieved_res_completion_value,0)-
1955 nvl(relieved_variance_value,0)-
1956 nvl(relieved_res_scrap_value,0)),
1957 SIGN(usage_rate_or_amount),
1958 (nvl(applied_resource_value,0)-
1959 nvl(relieved_res_completion_value,0)-
1960 nvl(relieved_variance_value,0)-
1961 nvl(relieved_res_scrap_value,0)+
1962 (i_txn_qty*
1963 decode(basis_type,
1964 1,usage_rate_or_amount,
1965 2,usage_rate_or_amount/l_lot_size,
1966 usage_rate_or_amount) -
1967 (applied_resource_units -
1968 nvl(relieved_res_completion_units,0) -
1969 nvl(relieved_res_final_comp_units,0) -
1970 nvl(relieved_res_scrap_units,0)))*
1971 crc.resource_rate),
1972 i_txn_qty*
1973 decode(basis_type,
1974 1,usage_rate_or_amount,
1975 2,usage_rate_or_amount/l_lot_size,
1976 usage_rate_or_amount)*
1977 crc.resource_rate)),
1978 nvl(w1.relieved_res_completion_value,0) +
1979 decode(SIGN(applied_resource_units-
1980 nvl(relieved_res_completion_units,0)-
1981 nvl(relieved_res_final_comp_units,0)-
1982 nvl(relieved_res_scrap_units,0)-
1983 i_txn_qty*decode(basis_type,
1984 1,usage_rate_or_amount,
1985 2,usage_rate_or_amount/l_lot_size,
1986 usage_rate_or_amount)),
1987 SIGN(usage_rate_or_amount),
1988 i_txn_qty*decode(basis_type,
1989 1,usage_rate_or_amount,
1990 2,usage_rate_or_amount/l_lot_size,
1991 usage_rate_or_amount)*
1992 decode(SIGN(nvl(applied_resource_value,0)-
1993 nvl(relieved_res_completion_value,0)-
1994 nvl(relieved_variance_value,0)-
1995 nvl(relieved_res_scrap_value,0)),
1996 SIGN(usage_rate_or_amount),
1997 decode(basis_type,
1998 1,((nvl(applied_resource_value,0)-
1999 nvl(relieved_res_completion_value,0)-
2000 nvl(relieved_variance_value,0)-
2001 nvl(relieved_res_scrap_value,0))
2002 /(applied_resource_units-
2003 nvl(relieved_res_completion_units,0)-
2004 nvl(relieved_res_final_comp_units,0)-
2005 nvl(relieved_res_scrap_units,0))),
2006 2,nvl(applied_resource_value,0)/
2007 decode(applied_resource_units,
2008 0,1,applied_resource_units),
2009 ((nvl(applied_resource_value,0)-
2010 nvl(relieved_res_completion_value,0)-
2011 nvl(relieved_variance_value,0)-
2012 nvl(relieved_res_scrap_value,0))
2013 /(applied_resource_units-
2014 nvl(relieved_res_completion_units,0)-
2015 nvl(relieved_res_final_comp_units,0)-
2016 nvl(relieved_res_scrap_units,0)))),
2017 crc.resource_rate),
2018 0,
2019 decode(SIGN(nvl(applied_resource_value,0)-
2020 nvl(relieved_res_completion_value,0)-
2021 nvl(relieved_variance_value,0)-
2022 nvl(relieved_res_scrap_value,0)),
2023 SIGN(usage_rate_or_amount),
2024 (nvl(applied_resource_value,0)-
2025 nvl(relieved_res_completion_value,0)-
2026 nvl(relieved_variance_value,0)-
2027 nvl(relieved_res_scrap_value,0)),
2028 i_txn_qty*decode(basis_type,
2029 1,usage_rate_or_amount,
2030 2,usage_rate_or_amount/l_lot_size,
2031 usage_rate_or_amount)*
2032 crc.resource_rate),
2033 -1*SIGN(usage_rate_or_amount),
2034 decode(SIGN(nvl(applied_resource_value,0)-
2035 nvl(relieved_res_completion_value,0)-
2036 nvl(relieved_variance_value,0)-
2037 nvl(relieved_res_scrap_value,0)),
2038 SIGN(usage_rate_or_amount),
2039 (nvl(applied_resource_value,0)-
2040 nvl(relieved_res_completion_value,0)-
2041 nvl(relieved_variance_value,0)-
2042 nvl(relieved_res_scrap_value,0)+
2043 (i_txn_qty*
2044 decode(basis_type,
2045 1,usage_rate_or_amount,
2046 2,usage_rate_or_amount/l_lot_size,
2047 usage_rate_or_amount) -
2048 (applied_resource_units -
2049 nvl(relieved_res_completion_units,0) -
2050 nvl(relieved_res_final_comp_units,0) -
2051 nvl(relieved_res_scrap_units,0)))*
2052 crc.resource_rate),
2053 i_txn_qty*
2054 decode(basis_type,
2055 1,usage_rate_or_amount,
2056 2,usage_rate_or_amount/l_lot_size,
2057 usage_rate_or_amount)*
2058 crc.resource_rate))
2059 FROM
2060 wip_operation_resources w2,
2061 cst_resource_costs crc
2062 WHERE
2063 w2.wip_entity_id = w1.wip_entity_id AND
2064 w2.operation_seq_num = w1.operation_seq_num AND
2065 w2.resource_seq_num = w1.resource_seq_num AND
2066 w2.organization_id = w2.organization_id AND
2067 w2.resource_id = crc.resource_id AND
2068 w2.organization_id = crc.organization_id AND
2069 crc.cost_type_id = i_res_cost_type_id)
2070 WHERE
2071 w1.wip_entity_id = i_wip_entity_id AND
2072 w1.organization_id = i_org_id AND
2073 w1.usage_rate_or_amount <> 0;
2074
2075
2076 /***********************************************************
2077 * Relieve TL Ovhd (Move based) units and Costs .. *
2078 * Open Issue : Do we relieve Ovhds for which associations *
2079 * no longer exist in CDO. *
2080 ***********************************************************/
2081
2082
2083 stmt_num := 305;
2084
2085 -- For the pre-defined completion algorithm, if no overheads have
2086 -- been charged then they must be relieved at the cost in the
2087 -- rates cost type. However, if nothing has been charged, there are
2088 -- no rows in WOO. So insert these rows.
2089
2090 INSERT INTO WIP_OPERATION_OVERHEADS
2091 (WIP_ENTITY_ID,
2092 OPERATION_SEQ_NUM,
2093 RESOURCE_SEQ_NUM,
2094 ORGANIZATION_ID,
2095 OVERHEAD_ID,
2096 BASIS_TYPE,
2097 APPLIED_OVHD_UNITS,
2098 APPLIED_OVHD_VALUE,
2099 RELIEVED_OVHD_COMPLETION_UNITS,
2100 RELIEVED_OVHD_SCRAP_UNITS,
2101 RELIEVED_OVHD_COMPLETION_VALUE,
2102 RELIEVED_OVHD_SCRAP_VALUE,
2103 TEMP_RELIEVED_VALUE,
2104 LAST_UPDATED_BY,
2105 CREATION_DATE,
2106 CREATED_BY,
2107 LAST_UPDATE_LOGIN,
2108 REQUEST_ID,
2109 PROGRAM_APPLICATION_ID,
2110 PROGRAM_ID,
2111 PROGRAM_UPDATE_DATE,
2112 LAST_UPDATE_DATE)
2113 SELECT
2114 i_wip_entity_id,
2115 wo.operation_seq_num,
2116 -1,
2117 i_org_id,
2118 cdo.overhead_id,
2119 cdo.basis_type,
2120 0,
2121 0,
2122 0,
2123 0,
2124 0,
2125 0,
2126 0,
2127 -1,
2128 SYSDATE,
2129 -1,
2130 -1,
2131 -1,
2132 -1,
2133 -1,
2134 SYSDATE,
2135 SYSDATE
2136 FROM
2137 WIP_OPERATIONS WO,
2138 CST_DEPARTMENT_OVERHEADS CDO
2139 WHERE
2140 WO.WIP_ENTITY_ID = i_wip_entity_id AND
2141 WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
2142 CDO.COST_TYPE_ID = i_res_cost_type_id AND
2143 CDO.BASIS_TYPE IN (1,2) AND
2144 NOT EXISTS
2145 (SELECT 'X'
2146 FROM
2147 WIP_OPERATION_OVERHEADS WOO
2148 where
2149 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
2150 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
2151 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2152 WOO.BASIS_TYPE = CDO.BASIS_TYPE AND
2153 WOO.RESOURCE_SEQ_NUM = -1);
2154
2155
2156 stmt_num := 310;
2157
2158 UPDATE wip_operation_overheads w1
2159 SET
2160 (relieved_ovhd_completion_units,
2161 temp_relieved_value,
2162 relieved_ovhd_completion_value) =
2163 (SELECT
2164 nvl(w1.relieved_ovhd_completion_units,0)+
2165 decode(w2.basis_type,
2166 1,i_txn_qty,
2167 2,i_txn_qty/l_lot_size),
2168 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2169 nvl(relieved_ovhd_completion_units,0)-
2170 nvl(relieved_ovhd_final_comp_units,0)-
2171 nvl(relieved_ovhd_scrap_units,0)-
2172 decode(w2.basis_type,
2173 1,i_txn_qty,
2174 2,i_txn_qty/l_lot_size)),
2175 1,
2176 decode(SIGN(nvl(applied_ovhd_value,0)-
2177 nvl(relieved_ovhd_completion_value,0)-
2178 nvl(relieved_variance_value,0)-
2179 nvl(relieved_ovhd_scrap_value,0)),
2180 1,
2181 decode(w2.basis_type,
2182 2,nvl(applied_ovhd_value,0),
2183 (nvl(applied_ovhd_value,0)-
2184 nvl(relieved_ovhd_completion_value,0)-
2185 nvl(relieved_variance_value,0)-
2186 nvl(relieved_ovhd_scrap_value,0))
2187 /(nvl(applied_ovhd_units,0)-
2188 nvl(relieved_ovhd_completion_units,0)-
2189 nvl(relieved_ovhd_final_comp_units,0)-
2190 nvl(relieved_ovhd_scrap_units,0)))*
2191 decode(w2.basis_type,
2192 1,i_txn_qty,
2193 2,i_txn_qty/l_lot_size),
2194 cdo.rate_or_amount*
2195 decode(w2.basis_type,
2196 1,i_txn_qty,
2197 2,i_txn_qty/l_lot_size)),
2198 0,
2199 decode(SIGN(nvl(applied_ovhd_value,0)-
2200 nvl(relieved_ovhd_completion_value,0)-
2201 nvl(relieved_variance_value,0)-
2202 nvl(relieved_ovhd_scrap_value,0)),
2203 1,
2204 (nvl(applied_ovhd_value,0)-
2205 nvl(relieved_ovhd_completion_value,0)-
2206 nvl(relieved_variance_value,0)-
2207 nvl(relieved_ovhd_scrap_value,0)),
2208 cdo.rate_or_amount*
2209 decode(w2.basis_type,
2210 1,i_txn_qty,
2211 2,i_txn_qty/l_lot_size)),
2212 -1,
2213 decode(SIGN(nvl(applied_ovhd_value,0)-
2214 nvl(relieved_ovhd_completion_value,0)-
2215 nvl(relieved_variance_value,0)-
2216 nvl(relieved_ovhd_scrap_value,0)),
2217 1,
2218 (nvl(applied_ovhd_value,0)-
2219 nvl(relieved_ovhd_completion_value,0)-
2220 nvl(relieved_variance_value,0)-
2221 nvl(relieved_ovhd_scrap_value,0)+
2222 (decode(w2.basis_type,
2223 1,i_txn_qty,
2224 2,i_txn_qty/l_lot_size)-
2225 (nvl(w2.applied_ovhd_units,0)-
2226 nvl(relieved_ovhd_completion_units,0)-
2227 nvl(relieved_ovhd_final_comp_units,0)-
2228 nvl(relieved_ovhd_scrap_units,0)))*
2229 cdo.rate_or_amount),
2230 cdo.rate_or_amount*
2231 decode(w2.basis_type,
2232 1,i_txn_qty,
2233 2,i_txn_qty/l_lot_size))),
2234 nvl(w1.relieved_ovhd_completion_value,0) +
2235 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2236 nvl(relieved_ovhd_completion_units,0)-
2237 nvl(relieved_ovhd_final_comp_units,0)-
2238 nvl(relieved_ovhd_scrap_units,0)-
2239 decode(w2.basis_type,
2240 1,i_txn_qty,
2241 2,i_txn_qty/l_lot_size)),
2242 1,
2243 decode(SIGN(nvl(applied_ovhd_value,0)-
2244 nvl(relieved_ovhd_completion_value,0)-
2245 nvl(relieved_variance_value,0)-
2246 nvl(relieved_ovhd_scrap_value,0)),
2247 1,
2248 decode(w2.basis_type,
2249 2,nvl(applied_ovhd_value,0),
2250 (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 /(nvl(applied_ovhd_units,0)-
2255 nvl(relieved_ovhd_completion_units,0)-
2256 nvl(relieved_ovhd_final_comp_units,0)-
2257 nvl(relieved_ovhd_scrap_units,0)))*
2258 decode(w2.basis_type,
2259 1,i_txn_qty,
2260 2,i_txn_qty/l_lot_size),
2261 cdo.rate_or_amount*
2262 decode(w2.basis_type,
2263 1,i_txn_qty,
2264 2,i_txn_qty/l_lot_size)),
2265 0,
2266 decode(SIGN(nvl(applied_ovhd_value,0)-
2267 nvl(relieved_ovhd_completion_value,0)-
2268 nvl(relieved_variance_value,0)-
2269 nvl(relieved_ovhd_scrap_value,0)),
2270 1,
2271 (nvl(applied_ovhd_value,0)-
2272 nvl(relieved_ovhd_completion_value,0)-
2273 nvl(relieved_variance_value,0)-
2274 nvl(relieved_ovhd_scrap_value,0)),
2275 cdo.rate_or_amount*
2276 decode(w2.basis_type,
2277 1,i_txn_qty,
2278 2,i_txn_qty/l_lot_size)),
2279 -1,
2280 decode(SIGN(nvl(applied_ovhd_value,0)-
2281 nvl(relieved_ovhd_completion_value,0)-
2282 nvl(relieved_variance_value,0)-
2283 nvl(relieved_ovhd_scrap_value,0)),
2284 1,
2285 (nvl(applied_ovhd_value,0)-
2286 nvl(relieved_ovhd_completion_value,0)-
2287 nvl(relieved_variance_value,0)-
2288 nvl(relieved_ovhd_scrap_value,0) +
2289 (decode(w2.basis_type,
2290 1,i_txn_qty,
2291 2,i_txn_qty/l_lot_size)-
2292 (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 cdo.rate_or_amount),
2297 cdo.rate_or_amount*
2298 decode(w2.basis_type,
2299 1,i_txn_qty,
2300 2,i_txn_qty/l_lot_size)))
2301 FROM
2302 wip_operation_overheads w2,
2303 cst_department_overheads cdo,
2304 wip_operations wo
2305 WHERE
2306 w2.wip_entity_id = w1.wip_entity_id AND
2307 w2.organization_id = w1.organization_id AND
2308 w2.operation_seq_num = w1.operation_seq_num AND
2309 w2.overhead_id = w1.overhead_id AND
2310 w2.basis_type = w1.basis_type AND
2311 w2.wip_entity_id = wo.wip_entity_id AND
2312 w2.organization_id = wo.organization_id AND
2313 w2.operation_seq_num = wo.operation_seq_num AND
2314 cdo.department_id = wo.department_id AND
2315 cdo.overhead_id = w2.overhead_id AND
2316 cdo.basis_type = w2.basis_type AND
2317 cdo.cost_type_id = i_res_cost_type_id)
2318 WHERE
2319 w1.wip_entity_id = i_wip_entity_id AND
2320 w1.organization_id = i_org_id AND
2321 w1.basis_type IN (1,2) AND
2322 EXISTS
2323 (
2324 SELECT 'X'
2325 FROM
2326 cst_department_overheads cdo2,
2327 wip_operations wo2
2328 WHERE
2329 wo2.wip_entity_id = w1.wip_entity_id AND
2330 wo2.organization_id = w1.organization_id AND
2331 wo2.operation_seq_num = w1.operation_seq_num AND
2332 wo2.department_id = cdo2.department_id AND
2333 w1.overhead_id = cdo2.overhead_id AND
2334 w1.basis_type = cdo2.basis_type AND
2335 cdo2.cost_type_id = i_res_cost_type_id);
2336
2337 /***********************************************************
2338 * Relieve TL Res based overheads and Units ... *
2339 ***********************************************************/
2340
2341 stmt_num := 320;
2342
2343 INSERT INTO WIP_OPERATION_OVERHEADS
2344 (WIP_ENTITY_ID,
2345 OPERATION_SEQ_NUM,
2346 RESOURCE_SEQ_NUM,
2347 ORGANIZATION_ID,
2348 OVERHEAD_ID,
2349 BASIS_TYPE,
2350 APPLIED_OVHD_UNITS,
2351 APPLIED_OVHD_VALUE,
2352 RELIEVED_OVHD_COMPLETION_UNITS,
2353 RELIEVED_OVHD_SCRAP_UNITS,
2354 RELIEVED_OVHD_COMPLETION_VALUE,
2355 RELIEVED_OVHD_SCRAP_VALUE,
2356 TEMP_RELIEVED_VALUE,
2357 LAST_UPDATED_BY,
2358 CREATION_DATE,
2359 CREATED_BY,
2360 LAST_UPDATE_LOGIN,
2361 REQUEST_ID,
2362 PROGRAM_APPLICATION_ID,
2363 PROGRAM_ID,
2364 PROGRAM_UPDATE_DATE,
2365 LAST_UPDATE_DATE)
2366 SELECT
2367 i_wip_entity_id,
2368 wo.operation_seq_num,
2369 wor.resource_seq_num,
2370 i_org_id,
2371 cdo.overhead_id,
2372 cdo.basis_type,
2373 0,
2374 0,
2375 0,
2376 0,
2377 0,
2378 0,
2379 0,
2380 -1,
2381 SYSDATE,
2382 -1,
2383 -1,
2384 -1,
2385 -1,
2386 -1,
2387 SYSDATE,
2388 SYSDATE
2389 FROM
2390 WIP_OPERATIONS WO,
2391 WIP_OPERATION_RESOURCES WOR,
2392 CST_DEPARTMENT_OVERHEADS CDO,
2393 CST_RESOURCE_OVERHEADS CRO
2394 WHERE
2395 WO.WIP_ENTITY_ID = i_wip_entity_id AND
2396 WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
2397 WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
2398 WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
2399 CDO.COST_TYPE_ID = i_res_cost_type_id AND
2400 CDO.BASIS_TYPE IN (3,4) AND
2401 CRO.COST_TYPE_ID = i_res_cost_type_id AND
2402 CRO.RESOURCE_ID = WOR.RESOURCE_ID AND
2403 CRO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2404 NOT EXISTS
2405 (SELECT 'X'
2406 FROM
2407 WIP_OPERATION_OVERHEADS WOO
2408 WHERE
2409 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
2410 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
2411 WOO.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM AND
2412 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
2413 WOO.BASIS_TYPE = CDO.BASIS_TYPE);
2414
2415 stmt_num := 330;
2416
2417 UPDATE wip_operation_overheads w1
2418 SET
2419 (relieved_ovhd_completion_units,
2420 temp_relieved_value,
2421 relieved_ovhd_completion_value) =
2422 (SELECT
2423 nvl(w1.relieved_ovhd_completion_units,0)+
2424 decode(w2.basis_type,
2425 3,i_txn_qty*decode(wor.basis_type,
2426 1,usage_rate_or_amount,
2427 2,usage_rate_or_amount/l_lot_size,
2428 usage_rate_or_amount),
2429 4,wor.temp_relieved_value),
2430 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2431 nvl(relieved_ovhd_completion_units,0)-
2432 nvl(relieved_ovhd_final_comp_units,0)-
2433 nvl(relieved_ovhd_scrap_units,0)-
2434 decode(w2.basis_type,
2435 3,i_txn_qty*decode(wor.basis_type,
2436 1,usage_rate_or_amount,
2437 2,usage_rate_or_amount/l_lot_size,
2438 usage_rate_or_amount),
2439 4,wor.temp_relieved_value)),
2440 SIGN(wor.usage_rate_or_amount),
2441 decode(SIGN(nvl(applied_ovhd_value,0)-
2442 nvl(relieved_ovhd_completion_value,0)-
2443 nvl(w2.relieved_variance_value,0)-
2444 nvl(relieved_ovhd_scrap_value,0)),
2445 SIGN(wor.usage_rate_or_amount),
2446 ((nvl(applied_ovhd_value,0)-
2447 nvl(relieved_ovhd_completion_value,0)-
2448 nvl(w2.relieved_variance_value,0)-
2449 nvl(relieved_ovhd_scrap_value,0))
2450 /(nvl(applied_ovhd_units,0)-
2451 nvl(relieved_ovhd_completion_units,0)-
2452 nvl(relieved_ovhd_final_comp_units,0)-
2453 nvl(relieved_ovhd_scrap_units,0)))*
2454 decode(w2.basis_type,
2455 3,i_txn_qty*
2456 decode(wor.basis_type,
2457 1,wor.usage_rate_or_amount,
2458 2,wor.usage_rate_or_amount/l_lot_size),
2459 4,nvl(wor.temp_relieved_value,0)),
2460 nvl(cdo.rate_or_amount,0)*
2461 decode(w2.basis_type,
2462 3,i_txn_qty*
2463 decode(wor.basis_type,
2464 1,wor.usage_rate_or_amount,
2465 2,wor.usage_rate_or_amount/l_lot_size),
2466 4,nvl(wor.temp_relieved_value,0))),
2467 0,
2468 decode(SIGN(nvl(applied_ovhd_value,0)-
2469 nvl(relieved_ovhd_completion_value,0)-
2470 nvl(w2.relieved_variance_value,0)-
2471 nvl(relieved_ovhd_scrap_value,0)),
2472 SIGN(wor.usage_rate_or_amount),
2473 (nvl(applied_ovhd_value,0)-
2474 nvl(relieved_ovhd_completion_value,0)-
2475 nvl(w2.relieved_variance_value,0)-
2476 nvl(relieved_ovhd_scrap_value,0)),
2477 nvl(cdo.rate_or_amount,0)*
2478 decode(w2.basis_type,
2479 3,i_txn_qty*
2480 decode(wor.basis_type,
2481 1,wor.usage_rate_or_amount,
2482 2,wor.usage_rate_or_amount/l_lot_size),
2483 4,nvl(wor.temp_relieved_value,0))),
2484 -1*SIGN(wor.usage_rate_or_amount),
2485 decode(SIGN(nvl(applied_ovhd_value,0)-
2486 nvl(relieved_ovhd_completion_value,0)-
2487 nvl(w2.relieved_variance_value,0)-
2488 nvl(relieved_ovhd_scrap_value,0)),
2489 SIGN(wor.usage_rate_or_amount),
2490 (nvl(applied_ovhd_value,0)-
2491 nvl(relieved_ovhd_completion_value,0)-
2492 nvl(w2.relieved_variance_value,0)-
2493 nvl(relieved_ovhd_scrap_value,0)+
2494 (decode(w2.basis_type,
2495 3,i_txn_qty*decode(wor.basis_type,
2496 1,usage_rate_or_amount,
2497 2,usage_rate_or_amount/l_lot_size,
2498 usage_rate_or_amount),
2499 4,wor.temp_relieved_value)-
2500 (nvl(w2.applied_ovhd_units,0)-
2501 nvl(relieved_ovhd_completion_units,0)-
2502 nvl(relieved_ovhd_final_comp_units,0)-
2503 nvl(relieved_ovhd_scrap_units,0)))*
2504 nvl(cdo.rate_or_amount,0)),
2505 nvl(cdo.rate_or_amount,0)*
2506 decode(w2.basis_type,
2507 3,i_txn_qty*
2508 decode(wor.basis_type,
2509 1,wor.usage_rate_or_amount,
2510 2,wor.usage_rate_or_amount/l_lot_size),
2511 4,nvl(wor.temp_relieved_value,0)))),
2512 nvl(w1.relieved_ovhd_completion_value,0) +
2513 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
2514 nvl(relieved_ovhd_completion_units,0)-
2515 nvl(relieved_ovhd_final_comp_units,0)-
2516 nvl(relieved_ovhd_scrap_units,0)-
2517 decode(w2.basis_type,
2518 3,i_txn_qty*decode(wor.basis_type,
2519 1,usage_rate_or_amount,
2520 2,usage_rate_or_amount/l_lot_size,
2521 usage_rate_or_amount),
2522 4,wor.temp_relieved_value)),
2523 SIGN(wor.usage_rate_or_amount),
2524 decode(SIGN(nvl(applied_ovhd_value,0)-
2525 nvl(relieved_ovhd_completion_value,0)-
2526 nvl(w2.relieved_variance_value,0)-
2527 nvl(relieved_ovhd_scrap_value,0)),
2528 SIGN(wor.usage_rate_or_amount),
2529 ((nvl(applied_ovhd_value,0)-
2530 nvl(relieved_ovhd_completion_value,0)-
2531 nvl(w2.relieved_variance_value,0)-
2532 nvl(relieved_ovhd_scrap_value,0))
2533 /(nvl(applied_ovhd_units,0)-
2534 nvl(relieved_ovhd_completion_units,0)-
2535 nvl(relieved_ovhd_final_comp_units,0)-
2536 nvl(relieved_ovhd_scrap_units,0)))*
2537 decode(w2.basis_type,
2538 3,i_txn_qty*
2539 decode(wor.basis_type,
2540 1,wor.usage_rate_or_amount,
2541 2,wor.usage_rate_or_amount/l_lot_size),
2542 4,nvl(wor.temp_relieved_value,0)),
2543 nvl(cdo.rate_or_amount,0)*
2544 decode(w2.basis_type,
2545 3,i_txn_qty*
2546 decode(wor.basis_type,
2547 1,wor.usage_rate_or_amount,
2548 2,wor.usage_rate_or_amount/l_lot_size),
2549 4,nvl(wor.temp_relieved_value,0))),
2550 0,
2551 decode(SIGN(nvl(applied_ovhd_value,0)-
2552 nvl(relieved_ovhd_completion_value,0)-
2553 nvl(w2.relieved_variance_value,0)-
2554 nvl(relieved_ovhd_scrap_value,0)),
2555 SIGN(wor.usage_rate_or_amount),
2556 (nvl(applied_ovhd_value,0)-
2557 nvl(relieved_ovhd_completion_value,0)-
2558 nvl(w2.relieved_variance_value,0)-
2559 nvl(relieved_ovhd_scrap_value,0)),
2560 nvl(cdo.rate_or_amount,0)*
2561 decode(w2.basis_type,
2562 3,i_txn_qty*
2563 decode(wor.basis_type,
2564 1,wor.usage_rate_or_amount,
2565 2,wor.usage_rate_or_amount/l_lot_size),
2566 4,nvl(wor.temp_relieved_value,0))),
2567 -1*SIGN(wor.usage_rate_or_amount),
2568 decode(SIGN(nvl(applied_ovhd_value,0)-
2569 nvl(relieved_ovhd_completion_value,0)-
2570 nvl(w2.relieved_variance_value,0)-
2571 nvl(relieved_ovhd_scrap_value,0)),
2572 SIGN(wor.usage_rate_or_amount),
2573 (nvl(applied_ovhd_value,0)-
2574 nvl(relieved_ovhd_completion_value,0)-
2575 nvl(w2.relieved_variance_value,0)-
2576 nvl(relieved_ovhd_scrap_value,0)+
2577 (decode(w2.basis_type,
2578 3,i_txn_qty*decode(wor.basis_type,
2579 1,usage_rate_or_amount,
2580 2,usage_rate_or_amount/l_lot_size,
2581 usage_rate_or_amount),
2582 4,wor.temp_relieved_value)-
2583 (nvl(w2.applied_ovhd_units,0)-
2584 nvl(relieved_ovhd_completion_units,0)-
2585 nvl(relieved_ovhd_final_comp_units,0)-
2586 nvl(relieved_ovhd_scrap_units,0)))*
2587 nvl(cdo.rate_or_amount,0)),
2588 nvl(cdo.rate_or_amount,0)*
2589 decode(w2.basis_type,
2590 3,i_txn_qty*
2591 decode(wor.basis_type,
2592 1,wor.usage_rate_or_amount,
2593 2,wor.usage_rate_or_amount/l_lot_size),
2594 4,nvl(wor.temp_relieved_value,0))))
2595 FROM
2596 wip_operation_overheads w2,
2597 cst_department_overheads cdo,
2598 wip_operations wo,
2599 wip_operation_resources wor,
2600 cst_resource_overheads cro
2601 WHERE
2602 w2.wip_entity_id = w1.wip_entity_id AND
2603 w2.organization_id = w1.organization_id AND
2604 w2.operation_seq_num = w1.operation_seq_num AND
2605 w2.overhead_id = w1.overhead_id AND
2606 w2.basis_type = w1.basis_type AND
2607 w2.resource_seq_num = w1.resource_seq_num AND
2608 w2.wip_entity_id = wo.wip_entity_id AND
2609 w2.organization_id = wo.organization_id AND
2610 w2.operation_seq_num = wo.operation_seq_num AND
2611 w2.wip_entity_id = wor.wip_entity_id AND
2612 w2.organization_id = wor.organization_id AND
2613 w2.operation_seq_num = wor.operation_seq_num AND
2614 w2.resource_seq_num = wor.resource_seq_num AND
2615 wo.department_id = cdo.department_id AND
2616 cdo.overhead_id = w2.overhead_id AND
2617 cdo.basis_type = w2.basis_type AND
2618 cdo.cost_type_id = i_res_cost_type_id AND
2619 cro.overhead_id = cdo.overhead_id AND
2620 cro.resource_id = wor.resource_id AND
2621 cro.cost_type_id = i_res_cost_type_id)
2622 WHERE
2623 w1.wip_entity_id = i_wip_entity_id AND
2624 w1.organization_id = i_org_id AND
2625 w1.basis_type IN (3,4) AND
2626 EXISTS
2627 (
2628 SELECT 'X'
2629 FROM
2630 cst_department_overheads cdo2,
2631 wip_operations wo2,
2632 cst_resource_overheads cro2,
2633 wip_operation_resources wor2
2634 WHERE
2635 w1.wip_entity_id = wo2.wip_entity_id AND
2636 w1.organization_id = wo2.organization_id AND
2637 w1.operation_seq_num = wo2.operation_seq_num AND
2638 w1.wip_entity_id = wor2.wip_entity_id AND
2639 w1.organization_id = wor2.organization_id AND
2640 w1.operation_seq_num = wor2.operation_seq_num AND
2641 w1.resource_seq_num = wor2.resource_seq_num AND
2642 wor2.usage_rate_or_amount <> 0 AND
2643 wo2.department_id = cdo2.department_id AND
2644 cdo2.overhead_id = w1.overhead_id AND
2645 cdo2.basis_type = w1.basis_type AND
2646 cdo2.cost_type_id = i_res_cost_type_id AND
2647 cdo2.overhead_id = cro2.overhead_id AND
2648 cro2.resource_id = wor2.resource_id AND
2649 cro2.cost_type_id = i_res_cost_type_id);
2650
2651 END IF; -- System option if condition ends here.
2652
2653
2654 END IF; -- Main If ends here.
2655
2656 /************************************************************
2657 * Insert into mtl_cst_txn_cost_details now that the *
2658 * Costs have been computed ... *
2659 * 3 statements are required --> one each for PL costs *
2660 * , TL Res/OSP costs and TL ovhd costs. *
2661 * Remember - the cst_txn_cost_detail tables stores unit *
2662 * cost - but the wip tables store the value in the *
2663 * temp_relieved_value column - so we have to divide by the *
2664 * txn_qty to arrive at the unit cost. *
2665 ************************************************************/
2666
2667 IF(l_insert_ind <> 1) THEN
2668 /*BUG 7346225: For Final completion the MCTCD should be populated from
2669 WPB since this one has rounded values not like WROCD, WOR or WOO and
2670 this is prefered since Final completion should relieve the accounted
2671 value */
2672
2673 IF (i_final_comp_flag='Y') THEN
2674 stmt_num := 350;
2675 /* Bug 7346243: Removed Variance value from Available
2676 Value for Final Completion */
2677 INSERT INTO mtl_cst_txn_cost_details
2678 (
2679 TRANSACTION_ID,
2680 ORGANIZATION_ID,
2681 INVENTORY_ITEM_ID,
2682 COST_ELEMENT_ID,
2683 LEVEL_TYPE,
2684 TRANSACTION_COST,
2685 NEW_AVERAGE_COST,
2686 PERCENTAGE_CHANGE,
2687 VALUE_CHANGE,
2688 LAST_UPDATE_DATE,
2689 LAST_UPDATED_BY,
2690 CREATION_DATE,
2691 CREATED_BY,
2692 LAST_UPDATE_LOGIN,
2693 REQUEST_ID,
2694 PROGRAM_APPLICATION_ID,
2695 PROGRAM_ID,
2696 PROGRAM_UPDATE_DATE)
2697 SELECT
2698 i_trx_id,
2699 i_org_id,
2700 i_inv_item_id,
2701 cce.cost_element_id,
2702 1,
2703 decode(cce.cost_element_id,
2704 1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2705 2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
2706 3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
2707 4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
2708 5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0)))/i_txn_qty,
2709 NULL,
2710 NULL,
2711 NULL,
2712 SYSDATE,
2713 i_user_id,
2714 SYSDATE,
2715 i_user_id,
2716 i_login_id,
2717 i_request_id,
2718 i_prog_appl_id,
2719 i_prog_id,
2720 SYSDATE
2721 FROM
2722 CST_COST_ELEMENTS CCE,
2723 WIP_PERIOD_BALANCES WPB
2724 WHERE
2725 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2726 WPB.ORGANIZATION_ID = I_ORG_ID AND
2727 CCE.COST_ELEMENT_ID <> 2
2728 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID;
2729
2730 stmt_num := 355;
2731 /* Bug 7346243: Removed Variance value from Available
2732 Value for Final Completion */
2733 INSERT INTO mtl_cst_txn_cost_details
2734 (
2735 TRANSACTION_ID,
2736 ORGANIZATION_ID,
2737 INVENTORY_ITEM_ID,
2738 COST_ELEMENT_ID,
2739 LEVEL_TYPE,
2740 TRANSACTION_COST,
2741 NEW_AVERAGE_COST,
2742 PERCENTAGE_CHANGE,
2743 VALUE_CHANGE,
2744 LAST_UPDATE_DATE,
2745 LAST_UPDATED_BY,
2746 CREATION_DATE,
2747 CREATED_BY,
2748 LAST_UPDATE_LOGIN,
2749 REQUEST_ID,
2750 PROGRAM_APPLICATION_ID,
2751 PROGRAM_ID,
2752 PROGRAM_UPDATE_DATE)
2753 SELECT
2754 i_trx_id,
2755 i_org_id,
2756 i_inv_item_id,
2757 cce.cost_element_id,
2758 2,
2759 decode(cce.cost_element_id,
2760 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2761 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
2762 3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
2763 4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
2764 5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0)))/i_txn_qty,
2765 NULL,
2766 NULL,
2767 NULL,
2768 SYSDATE,
2769 i_user_id,
2770 SYSDATE,
2771 i_user_id,
2772 i_login_id,
2773 i_request_id,
2774 i_prog_appl_id,
2775 i_prog_id,
2776 SYSDATE
2777 FROM
2778 CST_COST_ELEMENTS CCE,
2779 WIP_PERIOD_BALANCES WPB
2780 WHERE
2781 WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2782 WPB.ORGANIZATION_ID = I_ORG_ID
2783 GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID;
2784
2785 ELSE
2786 stmt_num := 360;
2787
2788 INSERT INTO mtl_cst_txn_cost_details
2789 (
2790 TRANSACTION_ID,
2791 ORGANIZATION_ID,
2792 INVENTORY_ITEM_ID,
2793 COST_ELEMENT_ID,
2794 LEVEL_TYPE,
2795 TRANSACTION_COST,
2796 NEW_AVERAGE_COST,
2797 PERCENTAGE_CHANGE,
2798 VALUE_CHANGE,
2799 LAST_UPDATE_DATE,
2800 LAST_UPDATED_BY,
2801 CREATION_DATE,
2802 CREATED_BY,
2803 LAST_UPDATE_LOGIN,
2804 REQUEST_ID,
2805 PROGRAM_APPLICATION_ID,
2806 PROGRAM_ID,
2807 PROGRAM_UPDATE_DATE)
2808 SELECT
2809 i_trx_id,
2810 i_org_id,
2811 i_inv_item_id,
2812 wrocd.cost_element_id,
2813 2,
2814 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
2815 NULL,
2816 NULL,
2817 NULL,
2818 SYSDATE,
2819 i_user_id,
2820 SYSDATE,
2821 i_user_id,
2822 i_login_id,
2823 i_request_id,
2824 i_prog_appl_id,
2825 i_prog_id,
2826 SYSDATE
2827 FROM
2828 WIP_REQ_OPERATION_COST_DETAILS wrocd
2829 where
2830 WIP_ENTITY_ID = i_wip_entity_id AND
2831 ORGANIZATION_ID = i_org_id
2832 GROUP BY wrocd.cost_element_id
2833 HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
2834
2835
2836 stmt_num := 370;
2837
2838 INSERT INTO mtl_cst_txn_cost_details
2839 (
2840 TRANSACTION_ID,
2841 ORGANIZATION_ID,
2842 INVENTORY_ITEM_ID,
2843 COST_ELEMENT_ID,
2844 LEVEL_TYPE,
2845 TRANSACTION_COST,
2846 NEW_AVERAGE_COST,
2847 PERCENTAGE_CHANGE,
2848 VALUE_CHANGE,
2849 LAST_UPDATE_DATE,
2850 LAST_UPDATED_BY,
2851 CREATION_DATE,
2852 CREATED_BY,
2853 LAST_UPDATE_LOGIN,
2854 REQUEST_ID,
2855 PROGRAM_APPLICATION_ID,
2856 PROGRAM_ID,
2857 PROGRAM_UPDATE_DATE)
2858 SELECT
2859 i_trx_id,
2860 i_org_id,
2861 i_inv_item_id,
2862 br.cost_element_id,
2863 1,
2864 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
2865 NULL,
2866 NULL,
2867 NULL,
2868 SYSDATE,
2869 i_user_id,
2870 SYSDATE,
2871 i_user_id,
2872 i_login_id,
2873 i_request_id,
2874 i_prog_appl_id,
2875 i_prog_id,
2876 SYSDATE
2877 FROM
2878 BOM_RESOURCES BR,
2879 WIP_OPERATION_RESOURCES WOR
2880 WHERE
2881 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
2882 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
2883 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
2884 WOR.ORGANIZATION_ID = i_org_id
2885 GROUP BY BR.COST_ELEMENT_ID
2886 HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
2887
2888 stmt_num := 390;
2889
2890 INSERT INTO mtl_cst_txn_cost_details
2891 (
2892 TRANSACTION_ID,
2893 ORGANIZATION_ID,
2894 INVENTORY_ITEM_ID,
2895 COST_ELEMENT_ID,
2896 LEVEL_TYPE,
2897 TRANSACTION_COST,
2898 NEW_AVERAGE_COST,
2899 PERCENTAGE_CHANGE,
2900 VALUE_CHANGE,
2901 LAST_UPDATE_DATE,
2902 LAST_UPDATED_BY,
2903 CREATION_DATE,
2904 CREATED_BY,
2905 LAST_UPDATE_LOGIN,
2906 REQUEST_ID,
2907 PROGRAM_APPLICATION_ID,
2908 PROGRAM_ID,
2909 PROGRAM_UPDATE_DATE)
2910 SELECT
2911 i_trx_id,
2912 i_org_id,
2913 i_inv_item_id,
2914 5,
2915 1,
2916 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2917 NULL,
2918 NULL,
2919 NULL,
2920 SYSDATE,
2921 i_user_id,
2922 SYSDATE,
2923 i_user_id,
2924 i_login_id,
2925 i_request_id,
2926 i_prog_appl_id,
2927 i_prog_id,
2928 SYSDATE
2929 FROM
2930 WIP_OPERATION_OVERHEADS
2931 WHERE
2932 WIP_ENTITY_ID = i_wip_entity_id AND
2933 ORGANIZATION_ID = i_org_id
2934 HAVING
2935 SUM(nvl(temp_relieved_value,0)) <> 0;
2936
2937 END IF;
2938
2939 stmt_num := 400;
2940 --
2941 -- R11.5 Enhanced Final Completion
2942 --
2943 IF (i_final_comp_flag = 'Y' AND l_comp_cost_source = 1
2944 AND l_use_val_cost_type <> -1) THEN
2945
2946 ---------------------------------------------
2947 -- Check if there is any -ve final completion
2948 -- for TL R, O, OSP and PL cost elements
2949 ---------------------------------------------
2950 stmt_num := 410;
2951 SELECT count(*)
2952 INTO l_count
2953 FROM mtl_cst_txn_cost_details mctcd,
2954 mtl_material_transactions mmt
2955 WHERE mctcd.transaction_id = mmt.transaction_id
2956 AND mctcd.transaction_id = i_trx_id
2957 AND mctcd.transaction_cost < 0;
2958
2959 IF (l_count <> 0) THEN
2960
2961 -----------------------------------------
2962 -- insert into wip_cost_txn_interface
2963 -----------------------------------------
2964 -- Get wip txn_id
2965 -----------------------------------------
2966 stmt_num := 440;
2967
2968 SELECT wip_transactions_s.nextval
2969 INTO l_wcti_txn_id
2970 FROM dual;
2971
2972 ----------------------------------------------------------------
2973 -- Insert into WCTI
2974 ----------------------------------------------------------------
2975 stmt_num := 460;
2976 INSERT INTO wip_cost_txn_interface
2977 (transaction_id,
2978 acct_period_id,
2979 process_status,
2980 process_phase,
2981 transaction_type,
2982 organization_id,
2983 wip_entity_id,
2984 wip_entity_name,
2985 entity_type,
2986 transaction_date,
2987 last_update_date,
2988 last_updated_by,
2989 last_update_login,
2990 creation_date,
2991 created_by,
2992 request_id,
2993 program_application_id,
2994 program_id,
2995 program_update_date)
2996 SELECT
2997 l_wcti_txn_id,
2998 i_acct_period_id,
2999 2,
3000 3,
3001 7, -- new transaction_type for final completion variance
3002 i_org_id,
3003 i_wip_entity_id,
3004 w.wip_entity_name,
3005 1,
3006 i_txn_date,
3007 sysdate,
3008 i_user_id,
3009 i_login_id,
3010 sysdate,
3011 i_user_id,
3012 i_request_id,
3013 i_prog_appl_id,
3014 i_prog_id,
3015 sysdate
3016 FROM
3017 wip_entities w
3018 WHERE
3019 w.wip_entity_id = i_wip_entity_id
3020 AND w.organization_id = i_org_id;
3021
3022 neg_final_completion( i_org_id => i_org_id,
3023 i_txn_date => i_txn_date,
3024 i_wip_entity_id => i_wip_entity_id,
3025 i_wcti_txn_id => l_wcti_txn_id,
3026 i_txn_qty => i_txn_qty,
3027 i_trx_id => i_trx_id,
3028 i_acct_period_id => i_acct_period_id,
3029 i_user_id => i_user_id,
3030 i_login_id => i_login_id,
3031 i_request_id => i_request_id,
3032 i_prog_id => i_prog_id,
3033 i_prog_appl_id => i_prog_appl_id,
3034 err_num => l_err_num,
3035 err_code => l_err_code,
3036 err_msg => l_err_msg);
3037
3038 IF (l_err_num <> 0) THEN
3039 raise proc_fail;
3040 END IF;
3041
3042 END IF;
3043 END IF;
3044 END IF;
3045
3046 EXCEPTION
3047 WHEN proc_fail THEN
3048 err_num := l_err_num;
3049 err_code := l_err_code;
3050 err_msg := l_err_msg;
3051
3052 WHEN OTHERS THEN
3053 err_num := SQLCODE;
3054 err_msg := 'CSTPACWC:' || ' complete:' || to_char(stmt_num) || ':' ||
3055 substr(SQLERRM,1,150);
3056
3057 END complete;
3058
3059 PROCEDURE neg_final_completion (
3060 i_org_id IN NUMBER,
3061 i_txn_date IN DATE,
3062 i_wip_entity_id IN NUMBER,
3063 i_wcti_txn_id IN NUMBER,
3064 i_txn_qty IN NUMBER,
3065 i_trx_id IN NUMBER,
3066 i_acct_period_id IN NUMBER,
3067 i_user_id IN NUMBER,
3068 i_login_id IN NUMBER,
3069 i_request_id IN NUMBER,
3070 i_prog_id IN NUMBER,
3071 i_prog_appl_id IN NUMBER,
3072 err_num OUT NOCOPY NUMBER,
3073 err_code OUT NOCOPY VARCHAR2,
3074 err_msg OUT NOCOPY VARCHAR2)
3075 IS
3076 stmt_num NUMBER;
3077 l_pri_curr VARCHAR2(15);
3078 l_sob_id NUMBER;
3079
3080 BEGIN
3081
3082 stmt_num := 461;
3083
3084 /* The following line in the FROM clause has been commented out because
3085 we will now have to refer cst_organization_definitions as an impact
3086 of the HR-PROFILE option.*/
3087
3088 SELECT set_of_books_id
3089 INTO l_sob_id
3090 /*FROM org_organization_definitions*/
3091 FROM cst_organization_definitions
3092 WHERE organization_id = i_org_id;
3093
3094 stmt_num := 462;
3095 SELECT currency_code
3096 INTO l_pri_curr
3097 FROM gl_sets_of_books
3098 WHERE set_of_books_id = l_sob_id;
3099
3100 stmt_num := 465;
3101 INSERT INTO wip_transactions
3102 (transaction_id,
3103 acct_period_id,
3104 transaction_type,
3105 organization_id,
3106 wip_entity_id,
3107 transaction_date,
3108 last_update_date,
3109 last_updated_by,
3110 last_update_login,
3111 creation_date,
3112 created_by,
3113 request_id,
3114 program_application_id,
3115 program_id,
3116 program_update_date)
3117 SELECT
3118 wcti.transaction_id,
3119 wcti.acct_period_id,
3120 wcti.transaction_type,
3121 wcti.organization_id,
3122 wcti.wip_entity_id,
3123 wcti.transaction_date,
3124 sysdate,
3125 i_user_id,
3126 i_login_id,
3127 sysdate,
3128 i_user_id,
3129 i_request_id,
3130 i_prog_appl_id,
3131 i_prog_id,
3132 sysdate
3133 FROM wip_cost_txn_interface wcti
3134 WHERE transaction_id = i_wcti_txn_id;
3135
3136 /*------------------------------------------+
3137 | Do Accounting for wip valuation accounts
3138 +-------------------------------------------*/
3139 stmt_num := 470;
3140 INSERT INTO wip_transaction_accounts
3141 (transaction_id, reference_account,
3142 last_update_date, last_updated_by,
3143 creation_date, created_by,
3144 last_update_login, organization_id,
3145 transaction_date, wip_entity_id,
3146 repetitive_schedule_id, accounting_line_type,
3147 transaction_value, base_transaction_value,
3148 contra_set_id, primary_quantity,
3149 rate_or_amount, basis_type,
3150 resource_id, cost_element_id,
3151 activity_id, currency_code,
3152 currency_conversion_date, currency_conversion_type,
3153 currency_conversion_rate,
3154 request_id, program_application_id,
3155 program_id, program_update_date)
3156 SELECT
3157 i_wcti_txn_id,
3158 decode(mctcd.cost_element_id,
3159 1, wdj.material_account,
3160 2, wdj.material_overhead_account,
3161 3, wdj.resource_account,
3162 4, wdj.outside_processing_account,
3163 5, wdj.overhead_account),
3164 sysdate, i_user_id,
3165 sysdate, i_user_id,
3166 i_login_id, i_org_id,
3167 i_txn_date, i_wip_entity_id,
3168 NULL, 7,
3169 NULL,
3170 decode(c1.minimum_accountable_unit,
3171 NULL, round(-SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
3172 round(-SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
3173 * c1.minimum_accountable_unit),
3174 NULL, NULL,
3175 NULL, NULL,
3176 NULL, mctcd.cost_element_id,
3177 NULL, NULL,
3178 NULL, NULL,
3179 NULL,
3180 i_request_id, i_prog_appl_id,
3181 i_prog_id, sysdate
3182 FROM mtl_cst_txn_cost_details mctcd,
3183 mtl_material_transactions mmt,
3184 wip_discrete_jobs wdj,
3185 fnd_currencies c1
3186 WHERE mctcd.transaction_id = mmt.transaction_id
3187 AND mmt.transaction_source_id = wdj.wip_entity_id
3188 AND mctcd.transaction_id = i_trx_id
3189 AND mctcd.transaction_cost < 0
3190 AND c1.currency_code = l_pri_curr
3191 GROUP BY
3192 decode(mctcd.cost_element_id,
3193 1, wdj.material_account,
3194 2, wdj.material_overhead_account,
3195 3, wdj.resource_account,
3196 4, wdj.outside_processing_account,
3197 5, wdj.overhead_account),
3198 mctcd.cost_element_id,
3199 c1.minimum_accountable_unit,
3200 c1.precision;
3201 /*------------------------------------------+
3202 | Do Accounting for wip variance accounts
3203 +-------------------------------------------*/
3204 stmt_num := 480;
3205 INSERT INTO wip_transaction_accounts
3206 (transaction_id, reference_account,
3207 last_update_date, last_updated_by,
3208 creation_date, created_by,
3209 last_update_login, organization_id,
3210 transaction_date, wip_entity_id,
3211 repetitive_schedule_id, accounting_line_type,
3212 transaction_value, base_transaction_value,
3213 contra_set_id, primary_quantity,
3214 rate_or_amount, basis_type,
3215 resource_id, cost_element_id,
3216 activity_id, currency_code,
3217 currency_conversion_date, currency_conversion_type,
3218 currency_conversion_rate,
3219 request_id, program_application_id,
3220 program_id, program_update_date)
3221 SELECT
3222 i_wcti_txn_id,
3223 wdj.material_variance_account,
3224 sysdate, i_user_id,
3225 sysdate, i_user_id,
3226 i_login_id, i_org_id,
3227 i_txn_date, i_wip_entity_id,
3228 NULL, 8,
3229 NULL,
3230 /* decode(c1.minimum_accountable_unit,
3231 NULL, round(SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
3232 round(SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
3233 * c1.minimum_accountable_unit), */
3234 decode(c1.minimum_accountable_unit,
3235 NULL, SUM(round((mctcd.transaction_cost*i_txn_qty),c1.precision)),
3236 sum(round((mctcd.transaction_cost*i_txn_qty)/c1.minimum_accountable_unit)
3237 * c1.minimum_accountable_unit)),
3238 NULL, NULL,
3239 NULL, NULL,
3240 NULL, 1,
3241 NULL, NULL,
3242 NULL, NULL,
3243 NULL,
3244 i_request_id, i_prog_appl_id,
3245 i_prog_id, sysdate
3246 FROM mtl_cst_txn_cost_details mctcd,
3247 mtl_material_transactions mmt,
3248 wip_discrete_jobs wdj,
3249 fnd_currencies c1
3250 WHERE mctcd.transaction_id = mmt.transaction_id
3251 AND mmt.transaction_source_id = wdj.wip_entity_id
3252 AND mctcd.transaction_id = i_trx_id
3253 AND mctcd.transaction_cost < 0
3254 AND mctcd.level_type = 2
3255 AND c1.currency_code = l_pri_curr
3256 GROUP BY
3257 wdj.material_variance_account,
3258 c1.minimum_accountable_unit,
3259 c1.precision;
3260
3261 stmt_num := 490;
3262 INSERT INTO wip_transaction_accounts
3263 (transaction_id, reference_account,
3264 last_update_date, last_updated_by,
3265 creation_date, created_by,
3266 last_update_login, organization_id,
3267 transaction_date, wip_entity_id,
3268 repetitive_schedule_id, accounting_line_type,
3269 transaction_value, base_transaction_value,
3270 contra_set_id, primary_quantity,
3271 rate_or_amount, basis_type,
3272 resource_id, cost_element_id,
3273 activity_id, currency_code,
3274 currency_conversion_date, currency_conversion_type,
3275 currency_conversion_rate,
3276 request_id, program_application_id,
3277 program_id, program_update_date)
3278 SELECT
3279 i_wcti_txn_id,
3280 decode(mctcd.cost_element_id,
3281 3, wdj.resource_variance_account,
3282 4, wdj.outside_proc_variance_account,
3283 5, wdj.overhead_variance_account),
3284 sysdate, i_user_id,
3285 sysdate, i_user_id,
3286 i_login_id, i_org_id,
3287 i_txn_date, i_wip_entity_id,
3288 NULL, 8,
3289 NULL,
3290 decode(c1.minimum_accountable_unit,
3291 NULL, round(mctcd.transaction_cost*i_txn_qty,c1.precision),
3292 round(mctcd.transaction_cost*i_txn_qty/c1.minimum_accountable_unit)
3293 * c1.minimum_accountable_unit),
3294 NULL, NULL,
3295 NULL, NULL,
3296 NULL, mctcd.cost_element_id,
3297 NULL, NULL,
3298 NULL, NULL,
3299 NULL,
3300 i_request_id, i_prog_appl_id,
3301 i_prog_id, sysdate
3302 FROM mtl_cst_txn_cost_details mctcd,
3303 mtl_material_transactions mmt,
3304 wip_discrete_jobs wdj,
3305 fnd_currencies c1
3306 WHERE mctcd.transaction_id = mmt.transaction_id
3307 AND mmt.transaction_source_id = wdj.wip_entity_id
3308 AND mctcd.transaction_id = i_trx_id
3309 AND mctcd.transaction_cost < 0
3310 AND mctcd.level_type = 1
3311 AND mctcd.cost_element_id in (3,4,5)
3312 AND c1.currency_code = l_pri_curr;
3313
3314 stmt_num := 493;
3315
3316 UPDATE WIP_TRANSACTION_ACCOUNTS
3317 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
3318 WHERE TRANSACTION_ID = i_wcti_txn_id;
3319
3320
3321 stmt_num := 495;
3322 DELETE wip_cost_txn_interface
3323 WHERE transaction_id = i_wcti_txn_id;
3324
3325 stmt_num := 500;
3326 UPDATE wip_period_balances wpb
3327 SET
3328 (last_update_date,
3329 last_updated_by,
3330 last_update_login,
3331 request_id,
3332 program_application_id,
3333 program_id,
3334 program_update_date,
3335 pl_material_var,
3336 pl_material_overhead_var,
3337 pl_resource_var,
3338 pl_outside_processing_var,
3339 pl_overhead_var,
3340 tl_material_var,
3341 tl_material_overhead_var,
3342 tl_resource_var,
3343 tl_outside_processing_var,
3344 tl_overhead_var) =
3345 (SELECT
3346 sysdate,
3347 i_user_id,
3348 i_login_id,
3349 i_request_id,
3350 i_prog_id,
3351 i_prog_appl_id,
3352 sysdate,
3353 pl_material_var + decode(c1.minimum_accountable_unit,
3354 NULL, round(i_txn_qty*sum(decode(level_type,
3355 2,decode(cost_element_id,
3356 1,nvl(transaction_cost,0)
3357 ,0),0)),c1.precision),
3358 round((i_txn_qty*sum(decode(level_type,
3359 2,decode(cost_element_id,
3360 1,nvl(transaction_cost,0)
3361 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3362 pl_material_overhead_var + decode(c1.minimum_accountable_unit,
3363 NULL, round(i_txn_qty*sum(decode(level_type,
3364 2,decode(cost_element_id,
3365 2,nvl(transaction_cost,0)
3366 ,0),0)),c1.precision),
3367 round((i_txn_qty*sum(decode(level_type,
3368 2,decode(cost_element_id,
3369 2,nvl(transaction_cost,0)
3370 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3371 pl_resource_var + decode(c1.minimum_accountable_unit,
3372 NULL, round(i_txn_qty*sum(decode(level_type,
3373 2,decode(cost_element_id,
3374 3,nvl(transaction_cost,0)
3375 ,0),0)),c1.precision),
3376 round((i_txn_qty*sum(decode(level_type,
3377 2,decode(cost_element_id,
3378 3,nvl(transaction_cost,0)
3379 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3380 pl_outside_processing_var + decode(c1.minimum_accountable_unit,
3381 NULL, round(i_txn_qty*sum(decode(level_type,
3382 2,decode(cost_element_id,
3383 4,nvl(transaction_cost,0)
3384 ,0),0)),c1.precision),
3385 round((i_txn_qty*sum(decode(level_type,
3386 2,decode(cost_element_id,
3387 4,nvl(transaction_cost,0)
3388 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3389 pl_overhead_var + decode(c1.minimum_accountable_unit,
3390 NULL, round(i_txn_qty*sum(decode(level_type,
3391 2,decode(cost_element_id,
3392 5,nvl(transaction_cost,0)
3393 ,0),0)),c1.precision),
3394 round((i_txn_qty*sum(decode(level_type,
3395 2,decode(cost_element_id,
3396 5,nvl(transaction_cost,0)
3397 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3398 tl_material_var + decode(c1.minimum_accountable_unit,
3399 NULL, round(i_txn_qty* sum(decode(level_type,
3400 1,decode(cost_element_id,
3401 1,nvl(transaction_cost,0)
3402 ,0),0)),c1.precision),
3403 round((i_txn_qty* sum(decode(level_type,
3404 1,decode(cost_element_id,
3405 1,nvl(transaction_cost,0)
3406 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3407 tl_material_overhead_var + 0, /* The TL MO never gets Cr to the Job*/
3408 tl_resource_var + decode(c1.minimum_accountable_unit,
3409 NULL, round(i_txn_qty* sum(decode(level_type,
3410 1,decode(cost_element_id,
3411 3,nvl(transaction_cost,0)
3412 ,0),0)),c1.precision),
3413 round((i_txn_qty* sum(decode(level_type,
3414 1,decode(cost_element_id,
3415 3,nvl(transaction_cost,0)
3416 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3417 tl_outside_processing_var + decode(c1.minimum_accountable_unit,
3418 NULL, round(i_txn_qty* sum(decode(level_type,
3419 1,decode(cost_element_id,
3420 4,nvl(transaction_cost,0)
3421 ,0),0)),c1.precision),
3422 round((i_txn_qty* sum(decode(level_type,
3423 1,decode(cost_element_id,
3424 4,nvl(transaction_cost,0)
3425 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
3426 tl_overhead_var + decode(c1.minimum_accountable_unit,
3427 NULL, round(i_txn_qty* sum(decode(level_type,
3428 1,decode(cost_element_id,
3429 5,nvl(transaction_cost,0)
3430 ,0),0)),c1.precision),
3431 round((i_txn_qty* sum(decode(level_type,
3432 1,decode(cost_element_id,
3433 5,nvl(transaction_cost,0)
3434 ,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit)
3435 FROM
3436 mtl_cst_txn_cost_details mctcd,
3437 fnd_currencies c1
3438 WHERE transaction_id = i_trx_id
3439 AND transaction_cost < 0
3440 AND c1.currency_code = l_pri_curr
3441 GROUP BY c1.minimum_accountable_unit, c1.precision)
3442 WHERE
3443 wip_entity_id = i_wip_entity_id AND
3444 organization_id = i_org_id AND
3445 acct_period_id = i_acct_period_id;
3446
3447 stmt_num := 510;
3448 --
3449 -- We have to re-avg with 0 cost but not -ve cost
3450 --
3451 UPDATE mtl_cst_txn_cost_details
3452 SET transaction_cost = 0
3453 WHERE transaction_cost < 0
3454 AND transaction_id = i_trx_id;
3455
3456 EXCEPTION
3457 WHEN OTHERS THEN
3458 err_code := 'neg_final_completion';
3459 err_num := SQLCODE;
3460 err_msg := 'CSTPACWC:' || 'neg_final_comletion: ' ||
3461 to_char(stmt_num) || ':' || substr(SQLERRM,1,150);
3462
3463 END neg_final_completion;
3464
3465 PROCEDURE assembly_return (
3466 i_trx_id IN NUMBER,
3467 i_txn_qty IN NUMBER,
3468 i_wip_entity_id IN NUMBER,
3469 i_org_id IN NUMBER,
3470 i_inv_item_id IN NUMBER,
3471 i_cost_type_id IN NUMBER,
3472 i_layer_id IN NUMBER,
3473 i_movhd_cost_type_id OUT NOCOPY NUMBER,
3474 i_res_cost_type_id IN NUMBER,
3475 i_user_id IN NUMBER,
3476 i_login_id IN NUMBER,
3477 i_request_id IN NUMBER,
3478 i_prog_id IN NUMBER,
3479 i_prog_appl_id IN NUMBER,
3480 err_num OUT NOCOPY NUMBER,
3481 err_code OUT NOCOPY VARCHAR2,
3482 err_msg OUT NOCOPY VARCHAR2)
3483
3484 is
3485
3486 stmt_num NUMBER;
3487 l_system_option_id NUMBER;
3488 i_lot_size NUMBER;
3489 l_comp_cost_source NUMBER;
3490 l_c_cost_type_id NUMBER;
3491 l_insert_ind NUMBER;
3492 l_use_val_cost_type NUMBER;
3493 l_routing_check NUMBER := 0;
3494 l_qty_per_assy NUMBER; /* bug 3504776 */
3495 l_include_comp_yield NUMBER;
3496
3497
3498 BEGIN
3499
3500 /***************************************************
3501 * Update temp_relieved_value to zero in all tables *
3502 ***************************************************/
3503
3504 stmt_num := 10;
3505
3506 UPDATE WIP_REQ_OPERATION_COST_DETAILS
3507 SET temp_relieved_value = 0
3508 where
3509 WIP_ENTITY_ID = i_wip_entity_id;
3510
3511 stmt_num := 20;
3512
3513 UPDATE WIP_OPERATION_RESOURCES
3514 SET temp_relieved_value = 0
3515 where
3516 WIP_ENTITY_ID = i_wip_entity_id;
3517
3518 stmt_num := 30;
3519
3520 UPDATE WIP_OPERATION_OVERHEADS
3521 SET temp_relieved_value = 0
3522 where
3523 WIP_ENTITY_ID = i_wip_entity_id;
3524
3525 stmt_num := 40;
3526
3527
3528
3529 select wac.completion_cost_source, nvl(wac.cost_type_id,-1),
3530 wdj.start_quantity,nvl(system_option_id,-1)
3531 into l_comp_cost_source,l_c_cost_type_id,i_lot_size,
3532 l_system_option_id
3533 from
3534 wip_accounting_classes wac,
3535 wip_discrete_jobs wdj
3536 where
3537 wdj.wip_entity_id = i_wip_entity_id and
3538 wdj.organization_id = i_org_id and
3539 wdj.class_code = wac.class_code and
3540 wdj.organization_id = wac.organization_id;
3541
3542
3543 l_insert_ind := 0;
3544
3545 stmt_num := 60;
3546
3547 /*---------------------------------------------------
3548 | If a non-std job has no bill or routing associated
3549 | with it or if a std job has no bill or routing
3550 | associated with it - these need to be treated
3551 | specially.
3552 |-----------------------------------------------------+*/
3553
3554 SELECT
3555 decode(job_type,
3556 1,decode(bom_revision,
3557 NULL,decode(routing_revision,NULL,-1,1),
3558 1),
3559 3,decode(bom_reference_id,
3560 NULL,decode(routing_reference_id,NULL,-1,1),
3561 1),
3562 1)
3563 into
3564 l_use_val_cost_type
3565 from
3566 WIP_DISCRETE_JOBS
3567 WHERE
3568 WIP_ENTITY_ID = i_wip_entity_id AND
3569 ORGANIZATION_ID = i_org_id;
3570
3571 /* Bug 3504776 - the standard material requirements can be added manually for the job.
3572 In this case, we want to derive the completion costs based on job costs */
3573 if (l_use_val_cost_type = -1) then
3574 /* Commented for Bug6734270.If there is a resource added manually
3575 then also the l_use_val_cost_type should be 1
3576 select count(*)
3577 into l_qty_per_assy
3578 from wip_requirement_operations
3579 where wip_entity_id = i_wip_entity_id
3580 and quantity_per_assembly <>0;
3581 */
3582 SELECT COUNT(1)
3583 INTO l_qty_per_assy
3584 FROM dual
3585 WHERE EXISTS ( SELECT NULL
3586 FROM wip_requirement_operations wro
3587 WHERE wro.wip_entity_id = i_wip_entity_id
3588 AND wro.quantity_per_assembly <>0
3589 UNION ALL
3590 SELECT NULL
3591 FROM wip_operation_resources wor
3592 WHERE wor.wip_entity_id = i_wip_entity_id
3593 AND wor.usage_rate_or_amount <>0
3594 );
3595
3596
3597 if (l_qty_per_assy > 0) then
3598 l_use_val_cost_type := 1;
3599 end if;
3600 end if;
3601
3602 /*----------------------------------------------
3603 | If the completions are costed by the system, we
3604 | follow the system rules for earning material
3605 | ovhd upon completion. If the completion is
3606 | costed by the cost type then we will earn
3607 | material overhead based on the costs in the cost type
3608 | We need to figure out, for the given job, where the
3609 | costs are coming from and hence how MO is to be
3610 | earned. This info will passed back to the calling
3611 | rotuine and used by the cost processor.
3612 |--------------------------------------------------+*/
3613
3614 stmt_num := 90;
3615
3616 IF (l_comp_cost_source=1) THEN
3617 i_movhd_cost_type_id:= i_res_cost_type_id;
3618 ELSE i_movhd_cost_type_id:=l_c_cost_type_id;
3619 END IF;
3620
3621 /*-------------------------------------------------
3622 | If the Completions are performed from a User spec
3623 | cost type, the returns should also be performed
3624 | from that cost type. So, check this condition.
3625 --------------------------------------------------*/
3626
3627 /*-------------------------------------------------
3628 | As in the case of completions from a cost type, if
3629 | the cost type specified is different from the avg
3630 | cost type we drive from CICD.
3631 |--------------------------------------------------*/
3632
3633 IF (l_comp_cost_source = 2 AND l_c_cost_type_id > 0 AND
3634 l_c_cost_type_id <> 2) THEN
3635
3636 l_insert_ind := 1;
3637
3638 stmt_num := 70;
3639
3640 INSERT INTO mtl_cst_txn_cost_details
3641 (
3642 TRANSACTION_ID,
3643 ORGANIZATION_ID,
3644 INVENTORY_ITEM_ID,
3645 COST_ELEMENT_ID,
3646 LEVEL_TYPE,
3647 TRANSACTION_COST,
3648 NEW_AVERAGE_COST,
3649 PERCENTAGE_CHANGE,
3650 VALUE_CHANGE,
3651 LAST_UPDATE_DATE,
3652 LAST_UPDATED_BY,
3653 CREATION_DATE,
3654 CREATED_BY,
3655 LAST_UPDATE_LOGIN,
3656 REQUEST_ID,
3657 PROGRAM_APPLICATION_ID,
3658 PROGRAM_ID,
3659 PROGRAM_UPDATE_DATE)
3660 SELECT
3661 i_trx_id,
3662 i_org_id,
3663 i_inv_item_id,
3664 COST_ELEMENT_ID,
3665 LEVEL_TYPE,
3666 SUM(ITEM_COST),
3667 NULL,
3668 NULL,
3669 NULL,
3670 SYSDATE,
3671 i_user_id,
3672 SYSDATE,
3673 i_user_id,
3674 i_login_id,
3675 i_request_id,
3676 i_prog_appl_id,
3677 i_prog_id,
3678 SYSDATE
3679 FROM
3680 CST_ITEM_COST_DETAILS
3681 WHERE
3682 INVENTORY_ITEM_ID = I_INV_ITEM_ID AND
3683 ORGANIZATION_ID = I_ORG_ID AND
3684 COST_TYPE_ID = L_C_COST_TYPE_ID AND
3685 NOT (COST_ELEMENT_ID = 2 AND
3686 LEVEL_TYPE = 1)
3687 GROUP BY COST_ELEMENT_ID,LEVEL_TYPE
3688 HAVING SUM(ITEM_COST) <> 0;
3689
3690
3691
3692 /*------------------------------------------------------
3693 | If completions are from a cost type and the cost type
3694 | is the average cost type, drive from CLCD.
3695 | OR
3696 | If completions are supposed to be system derived but
3697 | the job has no bill/routing(==> we use valuation
3698 | cost type).
3699 |-----------------------------------------------------*/
3700
3701 ELSIF((l_comp_cost_source = 2 AND l_c_cost_type_id = 2)
3702 OR
3703 (l_comp_cost_source = 1 AND l_use_val_cost_type = -1)) THEN
3704
3705
3706 l_insert_ind := 1;
3707
3708 stmt_num := 80;
3709
3710 INSERT INTO mtl_cst_txn_cost_details
3711 (
3712 TRANSACTION_ID,
3713 ORGANIZATION_ID,
3714 INVENTORY_ITEM_ID,
3715 COST_ELEMENT_ID,
3716 LEVEL_TYPE,
3717 TRANSACTION_COST,
3718 NEW_AVERAGE_COST,
3719 PERCENTAGE_CHANGE,
3720 VALUE_CHANGE,
3721 LAST_UPDATE_DATE,
3722 LAST_UPDATED_BY,
3723 CREATION_DATE,
3724 CREATED_BY,
3725 LAST_UPDATE_LOGIN,
3726 REQUEST_ID,
3727 PROGRAM_APPLICATION_ID,
3728 PROGRAM_ID,
3729 PROGRAM_UPDATE_DATE)
3730 SELECT
3731 i_trx_id,
3732 i_org_id,
3733 i_inv_item_id,
3734 COST_ELEMENT_ID,
3735 LEVEL_TYPE,
3736 ITEM_COST,
3737 NULL,
3738 NULL,
3739 NULL,
3740 SYSDATE,
3741 i_user_id,
3742 SYSDATE,
3743 i_user_id,
3744 i_login_id,
3745 i_request_id,
3746 i_prog_appl_id,
3747 i_prog_id,
3748 SYSDATE
3749 FROM
3750 CST_LAYER_COST_DETAILS
3751 WHERE
3752 LAYER_ID = i_layer_id AND
3753 NOT (COST_ELEMENT_ID = 2 AND
3754 LEVEL_TYPE = 1);
3755
3756 /*---------------------------------------------------
3757 New Assembly Return Algorithm
3758
3759 For System I (User-defined)
3760 ---------------------------
3761 - PL materials (Both System I and System II)
3762 - Resources
3763 - Ovhd
3764 If CompU = 0 OR CompV = 0
3765 CompU, CompV = unchanged
3766
3767 If Sign(CompU) <> Sign(CompV)
3768 CompU, CompV = unchanged
3769
3770 If Sign(CompU) = Sign(CompV)
3771 IF CompU > Q*Usage_rate => QTR (quantity to Relieve)
3772 CompU = CompU + Q*Usage_rate
3773 CompV = CompV + CompV/CompU * Q * Usage_rate
3774
3775 IF CompU = Q*Usage
3776 CompU = 0
3777 CompV = 0
3778
3779 IF CompU < Q*Usage
3780 IF CompU and CompV > 0
3781 CompU = 0
3782 CompV = 0
3783
3784 IF CompU and CompV < 0
3785 CompU, CompV = unchanged
3786
3787
3788 For System II (Actual)
3789 ---------------------
3790 - Resources
3791 - Ovhd
3792
3793 IF CompU < 0
3794 CompU and CompV unchanged
3795
3796 ----------------------------------------------------*/
3797
3798 /*---------------------------------------------------
3799 | If completion costs are system calculated ...
3800 |---------------------------------------------------*/
3801
3802 ELSIF (l_comp_cost_source = 1 AND l_use_val_cost_type <> -1) THEN
3803
3804
3805 /******************************************************
3806 * Compute PL Costs for WIP Assembly Return *
3807 ******************************************************/
3808
3809 /* Bug fix for 918694
3810 Assembly return for PL cost elements should be the same
3811 as TL resource, ovhd and OSP.
3812 ie. using actual alogrithm to return for all the PL/TL
3813 cost elements no matter what system alogrithm users
3814 has chosen.
3815 I am still keeping the codes but comment out
3816 in case users request in the future.
3817 */
3818
3819 stmt_num := 100;
3820
3821 /* Bug fix for 2138569
3822 For jobs that have no routing, but the assembly has a BOM.
3823 The table cst_comp_snap_temp does not get populated and thus
3824 there are no rows in cst_comp_snapshot table. Resulting which
3825 the calculations based on the CCS table returns zero value.
3826 And value of Assembly returned is incorrect.
3827
3828 To fix this, have decided to check if the wip_operations table
3829 has any rows for the job(wip_entity_id). If no rows exist then
3830 will use the WRO and WROCD tables to calculate the value else
3831 will use the CCS table to calculate the values.
3832 */
3833
3834 l_routing_check := 0;
3835
3836 select count(1)
3837 into l_routing_check
3838 from wip_operations wo
3839 where wo.wip_entity_id = i_wip_entity_id;
3840
3841 if l_routing_check > 0
3842 then
3843
3844 stmt_num := 105;
3845
3846 UPDATE wip_req_operation_cost_details w1
3847 SET
3848 (temp_relieved_value,
3849 relieved_matl_completion_value) =
3850 (SELECT
3851 --
3852 -- temp_relieved_value
3853 --
3854 decode(SIGN(w2.relieved_matl_completion_value),1,
3855 nvl(W2.relieved_matl_completion_value,0)*
3856 decode(abs(i_txn_qty),
3857 prior_completion_quantity,-1,
3858 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
3859 prior_completion_quantity)),
3860 0),
3861 ---
3862 --- relieved_matl_completion_value
3863 ---
3864 nvl(w1.relieved_matl_completion_value,0)+
3865 decode(SIGN(w2.relieved_matl_completion_value),1,
3866 nvl(w2.relieved_matl_completion_value,0)*
3867 decode(abs(i_txn_qty),
3868 prior_completion_quantity,-1,
3869 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
3870 prior_completion_quantity)),
3871 0)
3872 FROM
3873 wip_req_operation_cost_details w2,
3874 cst_comp_snapshot cocd
3875 WHERE
3876 w1.wip_entity_id = w2.wip_entity_id AND
3877 w1.organization_id = w2.organization_id AND
3878 w1.operation_seq_num = w2.operation_seq_num AND
3879 w1.inventory_item_id = w2.inventory_item_id AND
3880 w1.cost_element_id = w2.cost_element_id AND
3881 w2.wip_entity_id = cocd.wip_entity_id AND
3882 w2.operation_seq_num = cocd.operation_seq_num AND
3883 cocd.new_operation_flag = 2 AND
3884 cocd.transaction_id = i_trx_id
3885 )
3886 WHERE
3887 w1.wip_entity_id = i_wip_entity_id AND
3888 w1.organization_id = i_org_id;
3889
3890 stmt_num := 107;
3891
3892 UPDATE wip_requirement_operations w1
3893 SET
3894 relieved_matl_completion_qty =
3895 (SELECT
3896 --
3897 -- relieved_matl_completion_qty
3898 --
3899 nvl(w1.relieved_matl_completion_qty,0)+
3900 decode(SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),1,
3901 nvl(w2.relieved_matl_completion_qty,0)*
3902 decode(abs(i_txn_qty),
3903 prior_completion_quantity,-1,
3904 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
3905 prior_completion_quantity)),
3906 0)
3907 FROM
3908 wip_req_operation_cost_details wrocd,
3909 wip_requirement_operations w2,
3910 cst_comp_snapshot cocd
3911 WHERE
3912 w1.wip_entity_id = w2.wip_entity_id AND
3913 w1.inventory_item_id = w2.inventory_item_id AND
3914 w1.operation_seq_num = w2.operation_seq_num AND
3915 w1.organization_id = w2.organization_id AND
3916 w2.wip_entity_id = wrocd.wip_entity_id AND
3917 w2.organization_id = wrocd.organization_id AND
3918 w2.operation_seq_num = wrocd.operation_seq_num AND
3919 w2.inventory_item_id = wrocd.inventory_item_id AND
3920 w2.wip_entity_id = cocd.wip_entity_id AND
3921 w2.operation_seq_num = cocd.operation_seq_num AND
3922 cocd.new_operation_flag = 2 AND
3923 cocd.transaction_id = i_trx_id
3924 GROUP BY
3925 w2.wip_entity_id,
3926 w2.organization_id,
3927 w2.inventory_item_id,
3928 w2.operation_seq_num,
3929 prior_completion_quantity,
3930 w2.relieved_matl_completion_qty
3931 )
3932 WHERE
3933 w1.wip_entity_id = i_wip_entity_id AND
3934 w1.organization_id = i_org_id;
3935
3936 else
3937
3938 stmt_num := 110;
3939
3940 /* Get the value of Include Component yield flag, which will
3941 determine whether to include or not component yield factor in
3942 quantity per assembly*/
3943 SELECT nvl(include_component_yield, 1)
3944 INTO l_include_comp_yield
3945 FROM wip_parameters
3946 WHERE organization_id = i_org_id;
3947
3948 stmt_num := 115;
3949
3950 UPDATE wip_req_operation_cost_details w1
3951 SET
3952 (temp_relieved_value,
3953 relieved_matl_completion_value) =
3954 (SELECT
3955 ---
3956 --- temp_relieved_value
3957 ---
3958 DECODE(wro.relieved_matl_completion_qty,0,
3959 0,
3960 NULL,
3961 0,
3962 DECODE(w1.relieved_matl_completion_value,0,
3963 0,
3964 NULL,
3965 0,
3966 DECODE(SIGN(wro.relieved_matl_completion_qty),
3967 SIGN(w1.relieved_matl_completion_value),
3968 DECODE(SIGN(wro.relieved_matl_completion_qty-
3969 /* LBM project Changes */
3970 ABS(i_txn_qty)*(decode(wro.basis_type, 2,
3971 wro.quantity_per_assembly/i_lot_size,
3972 wro.quantity_per_assembly)/
3973 decode(l_include_comp_yield,
3974 1, nvl(wro.component_yield_factor,1),
3975 1))),
3976 SIGN(wro.quantity_per_assembly),
3977 /* LBM project Changes */
3978 i_txn_qty*(decode(wro.basis_type, 2,
3979 wro.quantity_per_assembly/i_lot_size,
3980 wro.quantity_per_assembly)/
3981 decode(l_include_comp_yield,
3982 1, nvl(wro.component_yield_factor,1),
3983 1))*
3984 relieved_matl_completion_value/
3985 wro.relieved_matl_completion_qty,
3986 0,
3987 -1*relieved_matl_completion_value,
3988 DECODE(SIGN(wro.relieved_matl_completion_qty),
3989 SIGN(wro.quantity_per_assembly),
3990 -1*relieved_matl_completion_value,
3991 0)),
3992 0))),
3993 ---
3994 --- relieved_matl_completion_value
3995 ---
3996 NVL(relieved_matl_completion_value,0)+
3997 DECODE(wro.relieved_matl_completion_qty,0,
3998 0,
3999 NULL,
4000 0,
4001 DECODE(w1.relieved_matl_completion_value,0,
4002 0,
4003 NULL,
4004 0,
4005 DECODE(SIGN(wro.relieved_matl_completion_qty),
4006 SIGN(w1.relieved_matl_completion_value),
4007 DECODE(SIGN(wro.relieved_matl_completion_qty-
4008 /* LBM project Changes */
4009 ABS(i_txn_qty)*(decode(wro.basis_type, 2,
4010 wro.quantity_per_assembly/i_lot_size,
4011 wro.quantity_per_assembly)/
4012 decode(l_include_comp_yield,
4013 1, nvl(wro.component_yield_factor,1),
4014 1))),
4015 SIGN(wro.quantity_per_assembly),
4016 /* LBM project Changes */
4017 i_txn_qty*(decode(wro.basis_type, 2,
4018 wro.quantity_per_assembly/i_lot_size,
4019 wro.quantity_per_assembly)/
4020 decode(l_include_comp_yield,
4021 1, nvl(wro.component_yield_factor,1),
4022 1))*
4023 relieved_matl_completion_value/
4024 wro.relieved_matl_completion_qty,
4025 0,
4026 -1*relieved_matl_completion_value,
4027 DECODE(SIGN(wro.relieved_matl_completion_qty),
4028 SIGN(wro.quantity_per_assembly),
4029 -1*relieved_matl_completion_value,
4030 0)),
4031 0)))
4032 FROM
4033 wip_req_operation_cost_details w2,
4034 wip_requirement_operations wro
4035 WHERE
4036 w1.wip_entity_id = w2.wip_entity_id AND
4037 w1.organization_id = w2.organization_id AND
4038 w1.operation_seq_num = w2.operation_seq_num AND
4039 w1.inventory_item_id = w2.inventory_item_id AND
4040 w1.cost_element_id = w2.cost_element_id AND
4041 w2.wip_entity_id = wro.wip_entity_id AND
4042 w2.organization_id = wro.organization_id AND
4043 w2.operation_seq_num = wro.operation_seq_num AND
4044 w2.inventory_item_id = wro.inventory_item_id
4045 )
4046 WHERE
4047 (w1.wip_entity_id, w1.organization_id,
4048 w1.inventory_item_id, w1.operation_seq_num) IN
4049 (SELECT
4050 wip_entity_id, organization_id,
4051 inventory_item_id,operation_seq_num
4052 FROM
4053 wip_requirement_operations wro2
4054 WHERE
4055 wro2.wip_entity_id = i_wip_entity_id AND
4056 wro2.organization_id = i_org_id AND
4057 wro2.quantity_per_assembly <> 0);
4058
4059 stmt_num := 117;
4060
4061 UPDATE wip_requirement_operations w
4062 SET relieved_matl_completion_qty =
4063 (SELECT
4064 NVL(w.relieved_matl_completion_qty,0)+
4065 DECODE(w.relieved_matl_completion_qty,0,
4066 0,
4067 NULL,
4068 0,
4069 DECODE(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0)),0,
4070 0,
4071 NULL,
4072 0,
4073 DECODE(SIGN(w.relieved_matl_completion_qty),
4074 SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),
4075 DECODE(SIGN(w.relieved_matl_completion_qty-
4076 /* LBM project Changes */
4077 ABS(i_txn_qty)*(decode(w.basis_type, 2,
4078 w.quantity_per_assembly/i_lot_size,
4079 w.quantity_per_assembly)/
4080 decode(l_include_comp_yield,
4081 1, nvl(w.component_yield_factor,1),
4082 1))),
4083 SIGN(w.quantity_per_assembly),
4084 /* LBM project Changes */
4085 i_txn_qty*(decode(w.basis_type, 2,
4086 w.quantity_per_assembly/i_lot_size,
4087 w.quantity_per_assembly)/
4088 decode(l_include_comp_yield,
4089 1, nvl(w.component_yield_factor,1),
4090 1)),
4091 0,
4092 -1*relieved_matl_completion_qty,
4093 DECODE(SIGN(w.relieved_matl_completion_qty),
4094 SIGN(w.quantity_per_assembly),
4095 -1*relieved_matl_completion_qty,
4096 0)),
4097 0)))
4098 FROM
4099 wip_req_operation_cost_details wrocd,
4100 wip_requirement_operations w2
4101 WHERE
4102 w.wip_entity_id = w2.wip_entity_id AND
4103 w.inventory_item_id = w2.inventory_item_id AND
4104 w.operation_seq_num = w2.operation_seq_num AND
4105 w.organization_id = w2.organization_id AND
4106 w2.wip_entity_id = wrocd.wip_entity_id AND
4107 w2.organization_id = wrocd.organization_id AND
4108 w2.operation_seq_num = wrocd.operation_seq_num AND
4109 w2.inventory_item_id = wrocd.inventory_item_id
4110 GROUP BY
4111 w2.wip_entity_id,
4112 w2.organization_id,
4113 w2.inventory_item_id,
4114 w2.operation_seq_num,
4115 w2.quantity_per_assembly,
4116 w2.relieved_matl_completion_qty
4117 )
4118 WHERE
4119 w.wip_entity_id = i_wip_entity_id AND
4120 w.organization_id = i_org_id AND
4121 w.quantity_per_assembly <> 0;
4122
4123 end if;
4124
4125 /*******************************************************
4126 * Compute TL resource costs for Assembly return ... *
4127 *******************************************************/
4128
4129 /*
4130 R11.5 Assembly Return at average cost
4131 For resources, overheads and OSP
4132 all return using Actual resources algorithm
4133 regardless of which system option
4134 */
4135
4136 -- If the option is to use Actual resources, then go with the
4137 -- snapshot table.
4138
4139 stmt_num := 160;
4140
4141 UPDATE wip_operation_resources w1
4142 SET
4143 (relieved_res_completion_units,
4144 temp_relieved_value,
4145 relieved_res_completion_value) =
4146 (SELECT
4147 --
4148 -- relieved_res_completion_units
4149 --
4150 nvl(w1.relieved_res_completion_units,0)+
4151 decode(SIGN(w2.relieved_res_completion_value),1,
4152 nvl(w2.relieved_res_completion_units,0)*
4153 decode(abs(i_txn_qty),
4154 prior_completion_quantity,-1,
4155 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4156 prior_completion_quantity)),
4157 0),
4158 --
4159 -- temp_relieved_value
4160 --
4161 decode(SIGN(w2.relieved_res_completion_value),1,
4162 nvl(W2.relieved_res_completion_value,0)*
4163 decode(abs(i_txn_qty),
4164 prior_completion_quantity,-1,
4165 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4166 prior_completion_quantity)),
4167 0),
4168 ---
4169 --- relieved_res_completion_value
4170 ---
4171 nvl(w1.relieved_res_completion_value,0)+
4172 decode(SIGN(w2.relieved_res_completion_value),1,
4173 nvl(w2.relieved_res_completion_value,0)*
4174 decode(abs(i_txn_qty),
4175 prior_completion_quantity,-1,
4176 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4177 prior_completion_quantity)),
4178 0)
4179 FROM
4180 wip_operation_resources w2,
4181 cst_comp_snapshot cocd
4182 WHERE
4183 w2.wip_entity_id = w1.wip_entity_id AND
4184 w2.organization_id = w1.organization_id AND
4185 w2.operation_seq_num = w1.operation_seq_num AND
4186 w2.resource_seq_num = w1.resource_seq_num AND
4187 w2.wip_entity_id = cocd.wip_entity_id AND
4188 w2.operation_seq_num = cocd.operation_seq_num AND
4189 cocd.new_operation_flag = 2 AND
4190 cocd.transaction_id = i_trx_id)
4191 WHERE
4192 w1.wip_entity_id = i_wip_entity_id AND
4193 w1.organization_id = i_org_id;
4194
4195
4196
4197 stmt_num := 165;
4198
4199 UPDATE wip_operation_overheads w1
4200 SET
4201 (relieved_ovhd_completion_units,
4202 temp_relieved_value,
4203 relieved_ovhd_completion_value) =
4204 (SELECT
4205 ---
4206 --- relieved_ovhd_completion_units
4207 ---
4208 nvl(w1.relieved_ovhd_completion_units,0)+
4209 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4210 nvl(W2.relieved_ovhd_completion_units,0)*
4211 decode(abs(i_txn_qty),
4212 prior_completion_quantity,-1,
4213 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4214 prior_completion_quantity)),
4215 0),
4216 ---
4217 --- temp_relieved_value
4218 ---
4219 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4220 nvl(w2.relieved_ovhd_completion_value,0)*
4221 decode(abs(i_txn_qty),
4222 prior_completion_quantity,-1,
4223 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4224 prior_completion_quantity)),
4225 0),
4226
4227 ---
4228 --- relieved_ovhd_completion_value
4229 ---
4230 nvl(w1.relieved_ovhd_completion_value,0)+
4231 decode(SIGN(w2.relieved_ovhd_completion_value),1,
4232 nvl(w2.relieved_ovhd_completion_value,0)*
4233 decode(abs(i_txn_qty),
4234 prior_completion_quantity,-1,
4235 i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
4236 prior_completion_quantity)),
4237 0)
4238
4239 FROM
4240 wip_operation_overheads w2,
4241 cst_comp_snapshot cocd
4242 WHERE
4243 w2.wip_entity_id = w1.wip_entity_id AND
4244 w2.organization_id = w1.organization_id AND
4245 w2.operation_seq_num = w1.operation_seq_num AND
4246 w2.resource_seq_num = w1.resource_seq_num AND
4247 w2.overhead_id = w1.overhead_id AND
4248 w2.basis_type = w1.basis_type AND
4249 w2.wip_entity_id = cocd.wip_entity_id AND
4250 w2.operation_seq_num = cocd.operation_seq_num AND
4251 cocd.new_operation_flag = 2 AND
4252 cocd.transaction_id = i_trx_id)
4253 WHERE
4254 w1.wip_entity_id = i_wip_entity_id AND
4255 w1.organization_id = i_org_id;
4256
4257 END IF;
4258
4259 /************************************************************
4260 * Insert into mtl_cst_txn_cost_details now that the *
4261 * Costs have been computed ... *
4262 * 3 statements are required --> one each for PL costs *
4263 * , TL Res/OSP costs and TL ovhd costs. *
4264 * Remember - the cst_txn_cost_detail tables stores unit *
4265 * cost - but the wip tables store the value in the *
4266 * temp_relieved_value column - so we have to divide by the *
4267 * txn_qty to arrive at the unit cost. *
4268 * Also, this insert should only be performed if the indicat *
4269 * or is <> 1.
4270 ************************************************************/
4271
4272 IF (l_insert_ind <>1) THEN
4273
4274 stmt_num := 270;
4275
4276 INSERT INTO mtl_cst_txn_cost_details
4277 (
4278 TRANSACTION_ID,
4279 ORGANIZATION_ID,
4280 INVENTORY_ITEM_ID,
4281 COST_ELEMENT_ID,
4282 LEVEL_TYPE,
4283 TRANSACTION_COST,
4284 NEW_AVERAGE_COST,
4285 PERCENTAGE_CHANGE,
4286 VALUE_CHANGE,
4287 LAST_UPDATE_DATE,
4288 LAST_UPDATED_BY,
4289 CREATION_DATE,
4290 CREATED_BY,
4291 LAST_UPDATE_LOGIN,
4292 REQUEST_ID,
4293 PROGRAM_APPLICATION_ID,
4294 PROGRAM_ID,
4295 PROGRAM_UPDATE_DATE)
4296 SELECT
4297 i_trx_id,
4298 i_org_id,
4299 i_inv_item_id,
4300 wrocd.cost_element_id,
4301 2,
4302 sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
4303 NULL,
4304 NULL,
4305 NULL,
4306 SYSDATE,
4307 i_user_id,
4308 SYSDATE,
4309 i_user_id,
4310 i_login_id,
4311 i_request_id,
4312 i_prog_appl_id,
4313 i_prog_id,
4314 SYSDATE
4315 FROM
4316 WIP_REQ_OPERATION_COST_DETAILS wrocd
4317 where
4318 WIP_ENTITY_ID = i_wip_entity_id AND
4319 ORGANIZATION_ID = i_org_id
4320 GROUP BY wrocd.cost_element_id
4321 HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
4322
4323 stmt_num := 290;
4324
4325 INSERT INTO mtl_cst_txn_cost_details
4326 (
4327 TRANSACTION_ID,
4328 ORGANIZATION_ID,
4329 INVENTORY_ITEM_ID,
4330 COST_ELEMENT_ID,
4331 LEVEL_TYPE,
4332 TRANSACTION_COST,
4333 NEW_AVERAGE_COST,
4334 PERCENTAGE_CHANGE,
4335 VALUE_CHANGE,
4336 LAST_UPDATE_DATE,
4337 LAST_UPDATED_BY,
4338 CREATION_DATE,
4339 CREATED_BY,
4340 LAST_UPDATE_LOGIN,
4341 REQUEST_ID,
4342 PROGRAM_APPLICATION_ID,
4343 PROGRAM_ID,
4344 PROGRAM_UPDATE_DATE)
4345 SELECT
4346 i_trx_id,
4347 i_org_id,
4348 i_inv_item_id,
4349 br.cost_element_id,
4350 1,
4351 sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
4352 NULL,
4353 NULL,
4354 NULL,
4355 SYSDATE,
4356 i_user_id,
4357 SYSDATE,
4358 i_user_id,
4359 i_login_id,
4360 i_request_id,
4361 i_prog_appl_id,
4362 i_prog_id,
4363 SYSDATE
4364 FROM
4365 BOM_RESOURCES BR,
4366 WIP_OPERATION_RESOURCES WOR
4367 WHERE
4368 WOR.RESOURCE_ID = BR.RESOURCE_ID AND
4369 WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
4370 WOR.WIP_ENTITY_ID = i_wip_entity_id AND
4371 WOR.ORGANIZATION_ID = i_org_id
4372 GROUP BY BR.COST_ELEMENT_ID
4373 HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
4374
4375 stmt_num := 310;
4376
4377 INSERT INTO mtl_cst_txn_cost_details
4378 (
4379 TRANSACTION_ID,
4380 ORGANIZATION_ID,
4381 INVENTORY_ITEM_ID,
4382 COST_ELEMENT_ID,
4383 LEVEL_TYPE,
4384 TRANSACTION_COST,
4385 NEW_AVERAGE_COST,
4386 PERCENTAGE_CHANGE,
4387 VALUE_CHANGE,
4388 LAST_UPDATE_DATE,
4389 LAST_UPDATED_BY,
4390 CREATION_DATE,
4391 CREATED_BY,
4392 LAST_UPDATE_LOGIN,
4393 REQUEST_ID,
4394 PROGRAM_APPLICATION_ID,
4395 PROGRAM_ID,
4396 PROGRAM_UPDATE_DATE)
4397 SELECT
4398 i_trx_id,
4399 i_org_id,
4400 i_inv_item_id,
4401 5,
4402 1,
4403 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
4404 NULL,
4405 NULL,
4406 NULL,
4407 SYSDATE,
4408 i_user_id,
4409 SYSDATE,
4410 i_user_id,
4411 i_login_id,
4412 i_request_id,
4413 i_prog_appl_id,
4414 i_prog_id,
4415 SYSDATE
4416 FROM
4417 WIP_OPERATION_OVERHEADS
4418 WHERE
4419 WIP_ENTITY_ID = i_wip_entity_id AND
4420 ORGANIZATION_ID = i_org_id
4421 HAVING
4422 SUM(nvl(temp_relieved_value,0)) <> 0;
4423
4424 END IF;
4425
4426 EXCEPTION
4427 WHEN OTHERS THEN
4428 err_num := SQLCODE;
4429 err_msg := 'CSTPACWC:' || 'assembly_return:' || to_char(stmt_num) ||
4430 ' ' || substr(SQLERRM,1,150);
4431
4432 END assembly_return;
4433
4434 END CSTPACWC;