[Home] [Help]
PACKAGE BODY: APPS.CSTPACWS
Source
1 PACKAGE BODY CSTPACWS AS
2 /* $Header: CSTPACSB.pls 120.6 2011/08/18 01:17:06 anjha ship $ */
3
4 PROCEDURE scrap (
5 i_trx_id IN NUMBER,
6 i_txn_qty IN NUMBER,
7 i_wip_entity_id IN NUMBER,
8 i_inv_item_id IN NUMBER,
9 i_org_id IN NUMBER,
10 i_cost_group_id IN NUMBER,
11 i_op_seq_num IN NUMBER,
12 i_cost_type_id IN NUMBER,
13 i_res_cost_type_id IN NUMBER,
14 err_num OUT NOCOPY NUMBER,
15 err_code OUT NOCOPY VARCHAR2,
16 err_msg OUT NOCOPY VARCHAR2)
17
18 is
19
20 stmt_num NUMBER;
21 i_lot_size NUMBER;
22 l_system_option_id NUMBER;
23 l_comp_cost_source NUMBER;
24 l_future_issued_qty NUMBER := 0; /* Added for bug 3715567 */
25 i_txn_date DATE;
26 l_include_comp_yield NUMBER;
27
28 /* Cursor added for bug 3715567 */
29 CURSOR c_wip_req_op IS
30 SELECT
31 wip_entity_id,
32 organization_id,
33 inventory_item_id,
34 operation_seq_num,
35 wip_supply_type
36 FROM
37 wip_requirement_operations wro
38 WHERE
39 --
40 -- Exclude bulk, supplier, phantom
41 --
42 wro.wip_supply_type not in (4,5,6) AND
43 wro.wip_entity_id = i_wip_entity_id AND
44 wro.organization_id = i_org_id AND
45 wro.quantity_per_assembly <> 0 AND
46 wro.operation_seq_num <= i_op_seq_num;
47
48 BEGIN
49
50 /***************************************************
51 * Update temp_relieved_value to zero in all tables *
52 ***************************************************/
53
54 stmt_num := 10;
55
56 UPDATE WIP_REQ_OPERATION_COST_DETAILS
57 SET temp_relieved_value = 0
58 where
59 WIP_ENTITY_ID = i_wip_entity_id;
60
61 stmt_num := 20;
62
63 UPDATE WIP_OPERATION_RESOURCES
64 SET temp_relieved_value = 0
65 where
66 WIP_ENTITY_ID = i_wip_entity_id;
67
68 stmt_num := 30;
69
70 UPDATE WIP_OPERATION_OVERHEADS
71 SET temp_relieved_value = 0
72 where
73 WIP_ENTITY_ID = i_wip_entity_id;
74
75 stmt_num := 40;
76
77 SELECT start_quantity
78 into i_lot_size
79 from
80 WIP_DISCRETE_JOBS
81 where
82 WIP_ENTITY_ID = i_wip_entity_id and
83 ORGANIZATION_ID = i_org_id;
84
85
86 stmt_num := 50;
87
88 select wac.completion_cost_source,
89 nvl(wac.SYSTEM_OPTION_ID,-1)
90 into l_comp_cost_source,l_system_option_id
91 from
92 wip_accounting_classes wac,
93 wip_discrete_jobs wdj
94 where
95 wdj.wip_entity_id = i_wip_entity_id and
96 wdj.organization_id = i_org_id and
97 wdj.class_code = wac.class_code and
98 wdj.organization_id = wac.organization_id;
99
100
101 -- /******************************************************
102 -- * Relieve PL Costs from WIP .... *
103 -- ******************************************************/
104
105
106
107 stmt_num := 90;
108 -- If no material has been issued to the Job, there will be no
109 -- rows in WROCD for the components. However, the cost relief is
110 -- supposed to be based on the current average cost of the
111 -- components. Therefore insert rows for all components.
112 -- If some components have been issued, they should not be inserted
113
114
115 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
116 (WIP_ENTITY_ID,
117 OPERATION_SEQ_NUM,
118 ORGANIZATION_ID,
119 INVENTORY_ITEM_ID,
120 COST_ELEMENT_ID,
121 APPLIED_MATL_VALUE,
122 LAST_UPDATED_BY,
123 LAST_UPDATE_DATE,
124 CREATION_DATE,
125 CREATED_BY,
126 LAST_UPDATE_LOGIN,
127 REQUEST_ID,
128 PROGRAM_APPLICATION_ID,
129 PROGRAM_ID,
130 PROGRAM_UPDATE_DATE)
131 SELECT
132 i_wip_entity_id,
133 wro.operation_seq_num,
134 i_org_id,
135 wro.inventory_item_id,
136 clcd.cost_element_id,
137 0,
138 -1,
139 SYSDATE,
140 SYSDATE,
141 -1,
142 -1,
143 -1,
144 -1,
145 -1,
146 SYSDATE
147 from
148 WIP_REQUIREMENT_OPERATIONS WRO,
149 CST_LAYER_COST_DETAILS CLCD,
150 CST_QUANTITY_LAYERS CQL
151 WHERE
152 WRO.WIP_ENTITY_ID = i_wip_entity_id AND
153 WRO.INVENTORY_ITEM_ID = CQL.INVENTORY_ITEM_ID AND
154 WRO.ORGANIZATION_ID = CQL.ORGANIZATION_ID AND
155 CQL.COST_GROUP_ID = I_COST_GROUP_ID AND
156 CQL.LAYER_ID = CLCD.LAYER_ID AND
157 WRO.OPERATION_SEQ_NUM <= i_op_seq_num AND
158 not EXISTS
159 (SELECT
160 'X'
161 FROM
162 WIP_REQ_OPERATION_COST_DETAILS WROCD
163 WHERE
164 WROCD.WIP_ENTITY_ID = i_wip_entity_id AND
165 WROCD.INVENTORY_ITEM_ID= WRO.INVENTORY_ITEM_ID AND
166 WROCD.OPERATION_SEQ_NUM= WRO.OPERATION_SEQ_NUM AND
167 WROCD.COST_ELEMENT_ID = CLCD.COST_ELEMENT_ID)
168 GROUP BY CLCD.COST_ELEMENT_ID,wro.operation_seq_num,
169 wro.inventory_item_id;
170
171 /* Added for bug 3715567 */
172 stmt_num := 95;
173 select transaction_date
174 into i_txn_date
175 from mtl_material_transactions
176 where transaction_id = i_trx_id;
177
178 stmt_num := 98;
179 /* Get the value of Include Component yield flag, which will determine
180 whether to include or not component yield factor in quantity per assembly*/
181 SELECT nvl(include_component_yield,1)
182 INTO l_include_comp_yield
183 FROM wip_parameters
184 WHERE organization_id = i_org_id;
185
186 stmt_num := 100;
187 /* Added loop for bug 3715567 */
188 FOR wro_rec in c_wip_req_op LOOP
189 BEGIN
190
191 l_future_issued_qty := 0;
192
193 BEGIN
194 SELECT nvl(sum(primary_quantity),0)
195 INTO l_future_issued_qty
196 FROM mtl_material_transactions
197 WHERE organization_id = wro_rec.organization_id
198 AND inventory_item_id = wro_rec.inventory_item_id
199 AND operation_seq_num = wro_rec.operation_seq_num
200 AND transaction_source_id = wro_rec.wip_entity_id
201 AND ( (transaction_date > i_txn_date) or
202 (transaction_date = i_txn_date and transaction_id > i_trx_id) )
203 AND costed_flag IS NOT NULL
204 AND nvl(move_transaction_id,-999) <>
205 ( Select nvl(move_transaction_id,-999)
206 from mtl_material_transactions
207 where transaction_id = i_trx_id);
208 EXCEPTION
209 WHEN Others THEN
210 l_future_issued_qty := 0;
211 END;
212
213 UPDATE WIP_REQ_OPERATION_COST_DETAILS w1
214 SET (temp_relieved_value,
215 relieved_matl_scrap_value) =
216 (SELECT
217 decode(SIGN(nvl(wro.quantity_issued,0)-
218 nvl(wro.relieved_matl_completion_qty,0)-
219 nvl(wro.relieved_matl_final_comp_qty,0)-
220 nvl(wro.relieved_matl_scrap_quantity,0)-
221 /* LBM Project Changes */
222 i_txn_qty*(decode(wro.basis_type, 2,
223 wro.quantity_per_assembly/i_lot_size,
224 wro.quantity_per_assembly)/
225 decode(l_include_comp_yield,
226 1, nvl(wro.component_yield_factor,1),
227 1)) +
228 l_future_issued_qty), /* Bug 6485658 */
229 SIGN(wro.quantity_per_assembly),
230 /* LBM project Changes */
231 i_txn_qty*(decode(wro.basis_type, 2,
232 wro.quantity_per_assembly/i_lot_size,
233 wro.quantity_per_assembly)/
234 decode(l_include_comp_yield,
235 1, nvl(wro.component_yield_factor,1),
236 1))*
237 decode(SIGN(nvl(applied_matl_value,0)-
238 nvl(relieved_matl_completion_value,0)-
239 nvl(relieved_variance_value,0)-
240 nvl(relieved_matl_scrap_value,0)),
241 /* Bug 3479419: AVTR = 0 Start*/
242 0, 0,
243 /* Bug 3479419: AVTR = 0 End*/
244 SIGN(wro.quantity_per_assembly),
245 ( nvl(applied_matl_value,0)-
246 nvl(relieved_matl_completion_value,0)-
247 nvl(relieved_variance_value,0)-
248 nvl(relieved_matl_scrap_value,0))
249 /(wro.quantity_issued-
250 nvl(wro.relieved_matl_completion_qty,0)-
251 nvl(wro.relieved_matl_final_comp_qty,0)-
252 nvl(wro.relieved_matl_scrap_quantity,0)+
253 l_future_issued_qty), /* Fix for bug 3715567 */
254 nvl(decode(cost_element_id,
255 1,cic.material_cost,
256 2,cic.material_overhead_cost,
257 3,cic.resource_cost,
258 4,cic.outside_processing_cost,
259 5,cic.overhead_cost),0)),
260 0,
261 decode(SIGN(nvl(applied_matl_value,0)-
262 nvl(relieved_matl_completion_value,0)-
263 nvl(relieved_variance_value,0)-
264 nvl(relieved_matl_scrap_value,0)),
265 /* Bug 3479419: AVTR = 0 Start*/
266 0, 0,
267 /* Bug 3479419: AVTR = 0 End*/
268 SIGN(wro.quantity_per_assembly),
269 (nvl(applied_matl_value,0)-
270 nvl(relieved_matl_completion_value,0)-
271 nvl(relieved_variance_value,0)-
272 nvl(relieved_matl_scrap_value,0)),
273 /* LBM Project Changes */
274 i_txn_qty*(decode(wro.basis_type, 2,
275 wro.quantity_per_assembly/i_lot_size,
276 wro.quantity_per_assembly)/
277 decode(l_include_comp_yield,
278 1, nvl(wro.component_yield_factor,1),
279 1))*
280 nvl(decode(cost_element_id,
281 1,cic.material_cost,
282 2,cic.material_overhead_cost,
283 3,cic.resource_cost,
284 4,cic.outside_processing_cost,
285 5,cic.overhead_cost),0)),
286 -1*SIGN(wro.quantity_per_assembly),
287 decode(SIGN(nvl(applied_matl_value,0)-
288 nvl(relieved_matl_completion_value,0)-
289 nvl(relieved_variance_value,0)-
290 nvl(relieved_matl_scrap_value,0)),
291 /* Bug 3479419: AVTR = 0 Start*/
292 /* LBM Project Changes */
293 0, (i_txn_qty*(decode(wro.basis_type, 2,
294 wro.quantity_per_assembly/i_lot_size,
295 wro.quantity_per_assembly)/
296 decode(l_include_comp_yield,
297 1, nvl(wro.component_yield_factor,1),
298 1))-
299 decode(sign(wro.quantity_issued -
300 nvl(wro.relieved_matl_completion_qty,0) -
301 nvl(wro.relieved_matl_final_comp_qty,0) -
302 nvl(wro.relieved_matl_scrap_quantity,0) +
303 l_future_issued_qty),
304 SIGN(wro.quantity_per_assembly),
305 (wro.quantity_issued -
306 nvl(wro.relieved_matl_completion_qty,0) -
307 nvl(wro.relieved_matl_final_comp_qty,0) -
308 nvl(wro.relieved_matl_scrap_quantity,0) +
309 l_future_issued_qty),
310 0))* /* Bug 6485658 */
311 nvl(decode(cost_element_id,
312 1,cic.material_cost,
313 2,cic.material_overhead_cost,
314 3,cic.resource_cost,
315 4,cic.outside_processing_cost,
316 5,cic.overhead_cost),0),
317 /* Bug 3479419: AVTR = 0 End*/
318 SIGN(wro.quantity_per_assembly),
319 decode(sign(wro.quantity_issued -
320 nvl(wro.relieved_matl_completion_qty,0)-
321 nvl(wro.relieved_matl_final_comp_qty,0)-
322 nvl(wro.relieved_matl_scrap_quantity,0) +
323 l_future_issued_qty),
324 SIGN(wro.quantity_per_assembly),
325 (nvl(applied_matl_value,0)-
326 nvl(relieved_matl_completion_value,0)-
327 nvl(relieved_variance_value,0)-
328 nvl(relieved_matl_scrap_value,0)+
329 /* LBM Project Changes */
330 (i_txn_qty*(decode(wro.basis_type,
331 2,wro.quantity_per_assembly/i_lot_size,
332 wro.quantity_per_assembly)/
333 decode(l_include_comp_yield,
334 1, nvl(wro.component_yield_factor,1),
335 1)
336 ) -
337 (wro.quantity_issued -
338 nvl(wro.relieved_matl_completion_qty,0)-
339 nvl(wro.relieved_matl_final_comp_qty,0)-
340 nvl(wro.relieved_matl_scrap_quantity,0) +
341 l_future_issued_qty)) * /* Bug 6485658 */
342 nvl(decode(cost_element_id,
343 1,cic.material_cost,
344 2,cic.material_overhead_cost,
345 3,cic.resource_cost,
346 4,cic.outside_processing_cost,
347 5,cic.overhead_cost),0)),
348 (i_txn_qty*(decode(wro.basis_type,
349 2,wro.quantity_per_assembly/i_lot_size,
350 wro.quantity_per_assembly)/
351 decode(l_include_comp_yield,
352 1, nvl(wro.component_yield_factor,1),
353 1)
354 )
355 )*nvl(decode(cost_element_id,
356 1,cic.material_cost,
357 2,cic.material_overhead_cost,
358 3,cic.resource_cost,
359 4,cic.outside_processing_cost,
360 5,cic.overhead_cost),0)
361 ),
362 /* LBM Project Changes */
363 i_txn_qty*(decode(wro.basis_type,
364 2,wro.quantity_per_assembly/i_lot_size,
365 wro.quantity_per_assembly)/
366 decode(l_include_comp_yield,
367 1, nvl(wro.component_yield_factor,1),
368 1)
369 )*
370 nvl(decode(cost_element_id,
371 1,cic.material_cost,
372 2,cic.material_overhead_cost,
373 3,cic.resource_cost,
374 4,cic.outside_processing_cost,
375 5,cic.overhead_cost),0))),
376
377 nvl(w1.relieved_matl_scrap_value,0)+
378 decode(SIGN(nvl(wro.quantity_issued,0)-
379 nvl(wro.relieved_matl_completion_qty,0)-
380 nvl(wro.relieved_matl_final_comp_qty,0)-
381 nvl(wro.relieved_matl_scrap_quantity,0)-
382 /* LBM Project Changes */
383 i_txn_qty*(decode(wro.basis_type, 2,
384 wro.quantity_per_assembly/i_lot_size,
385 wro.quantity_per_assembly)/
386 decode(l_include_comp_yield,
387 1, nvl(wro.component_yield_factor,1),
388 1)) +
389 l_future_issued_qty), /* Bug 6485658 */
390 SIGN(wro.quantity_per_assembly),
391 /* LBM project Changes */
392 i_txn_qty*(decode(wro.basis_type, 2,
393 wro.quantity_per_assembly/i_lot_size,
394 wro.quantity_per_assembly)/
395 decode(l_include_comp_yield,
396 1, nvl(wro.component_yield_factor,1),
397 1))*
398 decode(SIGN(nvl(applied_matl_value,0)-
399 nvl(relieved_matl_completion_value,0)-
400 nvl(relieved_variance_value,0)-
401 nvl(relieved_matl_scrap_value,0)),
402 /* Bug 3479419: AVTR = 0 Start*/
403 0, 0,
404 /* Bug 3479419: AVTR = 0 End*/
405 SIGN(wro.quantity_per_assembly),
406 ( nvl(applied_matl_value,0)-
407 nvl(relieved_matl_completion_value,0)-
408 nvl(relieved_variance_value,0)-
409 nvl(relieved_matl_scrap_value,0))
410 /(wro.quantity_issued-
411 nvl(wro.relieved_matl_completion_qty,0)-
412 nvl(wro.relieved_matl_final_comp_qty,0)-
413 nvl(wro.relieved_matl_scrap_quantity,0)+
414 l_future_issued_qty), /* Fix for bug 3715567 */
415 nvl(decode(cost_element_id,
416 1,cic.material_cost,
417 2,cic.material_overhead_cost,
418 3,cic.resource_cost,
419 4,cic.outside_processing_cost,
420 5,cic.overhead_cost),0)),
421 0,
422 decode(SIGN(nvl(applied_matl_value,0)-
423 nvl(relieved_matl_completion_value,0)-
424 nvl(relieved_variance_value,0)-
425 nvl(relieved_matl_scrap_value,0)),
426 /* Bug 3479419: AVTR = 0 Start*/
427 0, 0,
428 /* Bug 3479419: AVTR = 0 End*/
429 SIGN(wro.quantity_per_assembly),
430 (nvl(applied_matl_value,0)-
431 nvl(relieved_matl_completion_value,0)-
432 nvl(relieved_variance_value,0)-
433 nvl(relieved_matl_scrap_value,0)),
434 /* LBM Project Changes */
435 i_txn_qty*(decode(wro.basis_type, 2,
436 wro.quantity_per_assembly/i_lot_size,
437 wro.quantity_per_assembly)/
438 decode(l_include_comp_yield,
439 1, nvl(wro.component_yield_factor,1),
440 1))*
441 nvl(decode(cost_element_id,
442 1,cic.material_cost,
443 2,cic.material_overhead_cost,
444 3,cic.resource_cost,
445 4,cic.outside_processing_cost,
446 5,cic.overhead_cost),0)),
447 -1*SIGN(wro.quantity_per_assembly),
448 decode(SIGN(nvl(applied_matl_value,0)-
449 nvl(relieved_matl_completion_value,0)-
450 nvl(relieved_variance_value,0)-
451 nvl(relieved_matl_scrap_value,0)),
452 /* Bug 3479419: AVTR = 0 Start*/
453 /* LBM Project Changes */
454 0, (i_txn_qty*(decode(wro.basis_type, 2,
455 wro.quantity_per_assembly/i_lot_size,
456 wro.quantity_per_assembly)/
457 decode(l_include_comp_yield,
458 1, nvl(wro.component_yield_factor,1),
459 1))-
460 decode(sign(wro.quantity_issued -
461 nvl(wro.relieved_matl_completion_qty,0) -
462 nvl(wro.relieved_matl_final_comp_qty,0) -
463 nvl(wro.relieved_matl_scrap_quantity,0) +
464 l_future_issued_qty),
465 SIGN(wro.quantity_per_assembly),
466 (wro.quantity_issued -
467 nvl(wro.relieved_matl_completion_qty,0) -
468 nvl(wro.relieved_matl_final_comp_qty,0) -
469 nvl(wro.relieved_matl_scrap_quantity,0) +
470 l_future_issued_qty),
471 0))* /* Bug 6485658 */
472 nvl(decode(cost_element_id,
473 1,cic.material_cost,
474 2,cic.material_overhead_cost,
475 3,cic.resource_cost,
476 4,cic.outside_processing_cost,
477 5,cic.overhead_cost),0),
478 /* Bug 3479419: AVTR = 0 End*/
479 SIGN(wro.quantity_per_assembly),
480 decode(sign(wro.quantity_issued -
481 nvl(wro.relieved_matl_completion_qty,0)-
482 nvl(wro.relieved_matl_final_comp_qty,0)-
483 nvl(wro.relieved_matl_scrap_quantity,0) +
484 l_future_issued_qty),
485 SIGN(wro.quantity_per_assembly),
486 (nvl(applied_matl_value,0)-
487 nvl(relieved_matl_completion_value,0)-
488 nvl(relieved_variance_value,0)-
489 nvl(relieved_matl_scrap_value,0)+
490 /* LBM Project Changes */
491 (i_txn_qty*(decode(wro.basis_type,
492 2,wro.quantity_per_assembly/i_lot_size,
493 wro.quantity_per_assembly)/
494 decode(l_include_comp_yield,
495 1, nvl(wro.component_yield_factor,1),
496 1)
497 ) -
498 (wro.quantity_issued -
499 nvl(wro.relieved_matl_completion_qty,0)-
500 nvl(wro.relieved_matl_final_comp_qty,0)-
501 nvl(wro.relieved_matl_scrap_quantity,0) +
502 l_future_issued_qty)) * /* Bug 6485658 */
503 nvl(decode(cost_element_id,
504 1,cic.material_cost,
505 2,cic.material_overhead_cost,
506 3,cic.resource_cost,
507 4,cic.outside_processing_cost,
508 5,cic.overhead_cost),0)),
509 (i_txn_qty*(decode(wro.basis_type,
510 2,wro.quantity_per_assembly/i_lot_size,
511 wro.quantity_per_assembly)/
512 decode(l_include_comp_yield,
513 1, nvl(wro.component_yield_factor,1),
514 1)
515 )
516 )*nvl(decode(cost_element_id,
517 1,cic.material_cost,
518 2,cic.material_overhead_cost,
519 3,cic.resource_cost,
520 4,cic.outside_processing_cost,
521 5,cic.overhead_cost),0)
522 ),
523 /* LBM Project Changes */
524 i_txn_qty*(decode(wro.basis_type,
525 2,wro.quantity_per_assembly/i_lot_size,
526 wro.quantity_per_assembly)/
527 decode(l_include_comp_yield,
528 1, nvl(wro.component_yield_factor,1),
529 1)
530 )*
531 nvl(decode(cost_element_id,
532 1,cic.material_cost,
533 2,cic.material_overhead_cost,
534 3,cic.resource_cost,
535 4,cic.outside_processing_cost,
536 5,cic.overhead_cost),0)))
537 FROM
538 WIP_REQ_OPERATION_COST_DETAILS w2,
539 WIP_REQUIREMENT_OPERATIONS wro,
540 CST_QUANTITY_LAYERS cic
541 where
542 w2.WIP_ENTITY_ID = w1.WIP_ENTITY_ID AND
543 w2.ORGANIZATION_ID = w1.ORGANIZATION_ID AND
544 w2.INVENTORY_ITEM_ID = w1.INVENTORY_ITEM_ID AND
545 w2.OPERATION_SEQ_NUM = w1.OPERATION_SEQ_NUM AND
546 w2.COST_ELEMENT_ID = w1.COST_ELEMENT_ID AND
547 w2.WIP_ENTITY_ID = wro.WIP_ENTITY_ID AND
548 w2.ORGANIZATION_ID = wro.ORGANIZATION_ID AND
549 w2.INVENTORY_ITEM_ID = wro.INVENTORY_ITEM_ID AND
550 w2.OPERATION_SEQ_NUM = wro.OPERATION_SEQ_NUM AND
551 i_cost_group_id = cic.COST_GROUP_ID(+) AND
552 wro.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID(+) AND
553 wro.ORGANIZATION_ID = cic.ORGANIZATION_ID(+))
554 WHERE
555 w1.wip_entity_id = wro_rec.wip_entity_id AND
556 w1.organization_id = wro_rec.organization_id AND
557 w1.inventory_item_id = wro_rec.inventory_item_id AND
558 w1.operation_seq_num = wro_rec.operation_seq_num;
559
560 END;
561 END LOOP;
562 /* End loop for bug 3715567 */
563
564 -- /******************************************************
565 -- * Relieve PL Units from WIP .... *
566 -- ******************************************************/
567
568 stmt_num := 110;
569
570 UPDATE WIP_REQUIREMENT_OPERATIONS w
571 SET RELIEVED_MATL_SCRAP_QUANTITY =
572 (SELECT
573 nvl(w.RELIEVED_MATL_SCRAP_QUANTITY,0) +
574 /* LBM Project Changes */
575 i_txn_qty*(decode(w2.basis_type, 2,
576 w2.quantity_per_assembly/i_lot_size,
577 w2.quantity_per_assembly)/
578 decode(l_include_comp_yield,
579 1, nvl(w2.component_yield_factor,1),
580 1))
581 FROM
582 WIP_REQUIREMENT_OPERATIONS w2
583 where
584 w.WIP_ENTITY_ID = w2.WIP_ENTITY_ID AND
585 w.INVENTORY_ITEM_ID = w2.INVENTORY_ITEM_ID AND
586 w.OPERATION_SEQ_NUM = w2.OPERATION_SEQ_NUM AND
587 w.ORGANIZATION_ID = w2.ORGANIZATION_ID)
588 WHERE
589 w.WIP_ENTITY_ID = i_wip_entity_id AND
590 w.ORGANIZATION_ID = i_org_id AND
591 w.WIP_SUPPLY_TYPE not in (4,5,6) AND
592 w.OPERATION_SEQ_NUM <= i_op_seq_num;
593
594
595
596
597 stmt_num := 130;
598
599 INSERT INTO WIP_SCRAP_VALUES
600 (
601 transaction_id,
602 level_type,
603 cost_element_id,
604 cost_update_id,
605 last_update_date,
606 last_updated_by,
607 created_by,
608 creation_date,
609 last_update_login,
610 cost_element_value,
611 request_id,
612 program_application_id,
613 program_id,
614 program_update_date
615 )
616 SELECT
617 i_trx_id,
618 2,
619 wrocd.cost_element_id,
620 NULL,
621 SYSDATE,
622 -1,
623 -1,
624 SYSDATE,
625 -1,
626 sum(nvl(temp_relieved_value,0))/i_txn_qty,
627 -1,
628 -1,
629 -1,
630 SYSDATE
631 FROM
632 WIP_REQ_OPERATION_COST_DETAILS wrocd
633 where
634 wrocd.WIP_ENTITY_ID = i_wip_entity_id AND
635 wrocd.ORGANIZATION_ID = i_org_id
636 GROUP BY wrocd.COST_ELEMENT_ID
637 HAVING sum(nvl(temp_relieved_value,0)) <> 0;
638
639
640 IF (l_system_option_id = 1) THEN
641
642 stmt_num := 140;
643
644 UPDATE wip_operation_resources w1
645 SET
646 (relieved_res_scrap_units,
647 temp_relieved_value,
648 relieved_res_scrap_value) =
649 (SELECT
650 NVL(w1.relieved_res_scrap_units,0) +
651 decode(sign(applied_resource_units -
652 nvl(relieved_res_completion_units,0)-
653 nvl(relieved_res_final_comp_units,0)-
654 nvl(relieved_res_scrap_units,0)),
655 1,
656 (applied_resource_units -
657 nvl(relieved_res_completion_units,0)-
658 nvl(relieved_res_final_comp_units,0)-
659 nvl(relieved_res_scrap_units,0))*
660 --
661 -- new to solve divided by zero and over relieved
662 -- when txn_qty/completed - prior_completion - prior_scrap
663 -- is greater than or equal to one, set it to one
664 -- ie. flush out 1*value remain in the job same as completion 8/28/98
665 --
666 decode(sign(i_txn_qty - (cocd.quantity_completed -
667 nvl(prior_completion_quantity,0) -
668 nvl(prior_scrap_quantity,0))),
669 -1,i_txn_qty/(cocd.quantity_completed -
670 nvl(prior_completion_quantity,0) -
671 nvl(prior_scrap_quantity,0)),
672 1),
673 0),
674 decode(sign(applied_resource_value -
675 nvl(relieved_res_completion_value,0)-
676 nvl(relieved_variance_value,0)-
677 nvl(relieved_res_scrap_value,0)),
678 1,
679 (applied_resource_value -
680 nvl(relieved_res_completion_value,0)-
681 nvl(relieved_variance_value,0)-
682 nvl(relieved_res_scrap_value,0))*
683 --
684 -- new to solve divided by zero and over relieved
685 --
686 decode(sign(i_txn_qty - (cocd.quantity_completed -
687 nvl(prior_completion_quantity,0) -
688 nvl(prior_scrap_quantity,0))),
689 -1,i_txn_qty/(cocd.quantity_completed -
690 nvl(prior_completion_quantity,0) -
691 nvl(prior_scrap_quantity,0)),
692 1),
693 0),
694 nvl(w1.relieved_res_scrap_value,0) +
695 decode(sign(applied_resource_value -
696 nvl(relieved_res_completion_value,0)-
697 nvl(relieved_variance_value,0)-
698 nvl(relieved_res_scrap_value,0)),
699 1,
700 (applied_resource_value -
701 nvl(relieved_res_completion_value,0)-
702 nvl(relieved_variance_value,0)-
703 nvl(relieved_res_scrap_value,0))*
704 --
705 -- new to solve divided by zero and over relieved
706 --
707 decode(sign(i_txn_qty - (cocd.quantity_completed -
708 nvl(prior_completion_quantity,0) -
709 nvl(prior_scrap_quantity,0))),
710 -1,i_txn_qty/(cocd.quantity_completed -
711 nvl(prior_completion_quantity,0) -
712 nvl(prior_scrap_quantity,0)),
713 1),
714 0)
715 FROM
716 wip_operation_resources w2,
717 cst_comp_snapshot cocd
718 WHERE
719 W1.WIP_ENTITY_ID = W2.WIP_ENTITY_ID AND
720 W1.OPERATION_SEQ_NUM = W2.OPERATION_SEQ_NUM AND
721 W1.RESOURCE_SEQ_NUM = W2.RESOURCE_SEQ_NUM AND
722 W1.ORGANIZATION_ID = W2.ORGANIZATION_ID AND
723 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
724 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
725 COCD.NEW_OPERATION_FLAG = 2 AND */
726 COCD.TRANSACTION_ID = I_TRX_ID)
727 WHERE
728 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
729 W1.ORGANIZATION_ID = I_ORG_ID AND
730 /*bug7346242: Commented the condition below. Usage rate for
731 resource shouldn't be checked, when system option is
732 Actual resource charges
733 w1.USAGE_RATE_OR_AMOUNT <> 0 AND*/
734 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
735
736
737 stmt_num := 145;
738
739 UPDATE wip_operation_overheads w1
740 SET
741 (relieved_ovhd_scrap_units,
742 temp_relieved_value,
743 relieved_ovhd_scrap_value) =
744 (SELECT
745 NVL(w1.relieved_ovhd_scrap_units,0) +
746 decode(sign(applied_ovhd_units -
747 nvl(relieved_ovhd_completion_units,0)-
748 nvl(relieved_ovhd_final_comp_units,0)-
749 nvl(relieved_ovhd_scrap_units,0)),
750 1,
751 (applied_ovhd_units -
752 nvl(relieved_ovhd_completion_units,0)-
753 nvl(relieved_ovhd_final_comp_units,0)-
754 nvl(relieved_ovhd_scrap_units,0))*
755 --
756 -- new to solve divided by zero and over relieved
757 --
758 decode(sign(i_txn_qty - (cocd.quantity_completed -
759 nvl(prior_completion_quantity,0) -
760 nvl(prior_scrap_quantity,0))),
761 -1,i_txn_qty/(cocd.quantity_completed -
762 nvl(prior_completion_quantity,0) -
763 nvl(prior_scrap_quantity,0)),
764 1),
765 0),
766 decode(sign(applied_ovhd_value -
767 nvl(relieved_ovhd_completion_value,0)-
768 nvl(relieved_variance_value,0)-
769 nvl(relieved_ovhd_scrap_value,0)),
770 1,
771 (applied_ovhd_value -
772 nvl(relieved_ovhd_completion_value,0)-
773 nvl(relieved_variance_value,0)-
774 nvl(relieved_ovhd_scrap_value,0))*
775 --
776 -- new to solve divided by zero and over relieved
777 --
778 decode(sign(i_txn_qty - (cocd.quantity_completed -
779 nvl(prior_completion_quantity,0) -
780 nvl(prior_scrap_quantity,0))),
781 -1,i_txn_qty/(cocd.quantity_completed -
782 nvl(prior_completion_quantity,0) -
783 nvl(prior_scrap_quantity,0)),
784 1),
785 0),
786 nvl(W1.relieved_ovhd_scrap_value,0) +
787 decode(sign(applied_ovhd_value -
788 nvl(relieved_ovhd_completion_value,0)-
789 nvl(relieved_variance_value,0)-
790 nvl(relieved_ovhd_scrap_value,0)),
791 1,
792 (applied_ovhd_value -
793 nvl(relieved_ovhd_completion_value,0)-
794 nvl(relieved_variance_value,0)-
795 nvl(relieved_ovhd_scrap_value,0))*
796 --
797 -- new to solve divided by zero and over relieved
798 --
799 decode(sign(i_txn_qty - (cocd.quantity_completed -
800 nvl(prior_completion_quantity,0) -
801 nvl(prior_scrap_quantity,0))),
802 -1,i_txn_qty/(cocd.quantity_completed -
803 nvl(prior_completion_quantity,0) -
804 nvl(prior_scrap_quantity,0)),
805 1),
806 0)
807 FROM
808 wip_operation_overheads W2,
809 cst_comp_snapshot COCD
810 WHERE
811 w1.wip_entity_id = w2.wip_entity_id AND
812 w1.operation_seq_num = w2.operation_seq_num AND
813 w1.resource_seq_num = w2.resource_seq_num AND
814 w1.overhead_id = w2.overhead_id AND
815 /*bug#3469342. */
816 w1.basis_type = w2.basis_type AND
817 w1.organization_id = w2.organization_id AND
818 w2.operation_seq_num = cocd.operation_seq_num AND
819 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
820 cocd.new_operation_flag = 2 AND */
821 cocd.transaction_id = i_trx_id)
822 WHERE
823 w1.wip_entity_id = i_wip_entity_id AND
824 w1.organization_id = i_org_id AND
825 w1.operation_seq_num <= i_op_seq_num;
826
827 ELSE
828
829 -- /******************************************************
830 -- * Relieve This Level Resource costs/units from WIP ...*
831 -- ******************************************************/
832
833 stmt_num := 150;
834
835 UPDATE WIP_OPERATION_RESOURCES w1
836 SET
837 (relieved_res_scrap_units,
838 temp_relieved_value,
839 relieved_res_scrap_value) =
840 (SELECT
841 nvl(w1.relieved_res_scrap_units,0)+
842 decode(basis_type,
843 1,i_txn_qty*usage_rate_or_amount,
844 2,i_txn_qty*usage_rate_or_amount/i_lot_size,
845 i_txn_qty*usage_rate_or_amount),
846 decode(SIGN(applied_resource_units-
847 nvl(relieved_res_completion_units,0)-
848 nvl(relieved_res_final_comp_units,0)-
849 nvl(relieved_res_scrap_units,0)-
850 i_txn_qty*decode(basis_type,
851 1,usage_rate_or_amount,
852 2,usage_rate_or_amount/i_lot_size,
853 usage_rate_or_amount)),
854 SIGN(usage_rate_or_amount),
855 i_txn_qty*decode(basis_type,
856 1,usage_rate_or_amount,
857 2,usage_rate_or_amount/i_lot_size,
858 usage_rate_or_amount)*
859 decode(SIGN(nvl(applied_resource_value,0)-
860 nvl(relieved_res_completion_value,0)-
861 nvl(relieved_variance_value,0)-
862 nvl(relieved_res_scrap_value,0)),
863 SIGN(usage_rate_or_amount),
864 decode(basis_type,
865 1,((nvl(applied_resource_value,0)-
866 nvl(relieved_res_completion_value,0)-
867 nvl(relieved_variance_value,0)-
868 nvl(relieved_res_scrap_value,0))
869 /(applied_resource_units-
870 nvl(relieved_res_completion_units,0)-
871 nvl(relieved_res_final_comp_units,0)-
872 nvl(relieved_res_scrap_units,0))),
873 2,nvl(applied_resource_value,0)/
874 decode(applied_resource_units,
875 0,1,applied_resource_units),
876 /* Bug4213652 dividing by applied units
877 to get the correct relieved value*/
878 ((nvl(applied_resource_value,0)-
879 nvl(relieved_res_completion_value,0)-
880 nvl(relieved_variance_value,0)-
881 nvl(relieved_res_scrap_value,0))
882 /(applied_resource_units-
883 nvl(relieved_res_completion_units,0)-
884 nvl(relieved_res_final_comp_units,0)-
885 nvl(relieved_res_scrap_units,0)))),
886 crc.resource_rate),
887 0,
888 decode(SIGN(nvl(applied_resource_value,0)-
889 nvl(relieved_res_completion_value,0)-
890 nvl(relieved_variance_value,0)-
891 nvl(relieved_res_scrap_value,0)),
892 SIGN(usage_rate_or_amount),
893 (nvl(applied_resource_value,0)-
894 nvl(relieved_res_completion_value,0)-
895 nvl(relieved_variance_value,0)-
896 nvl(relieved_res_scrap_value,0)),-
897 i_txn_qty*decode(basis_type,
898 1,usage_rate_or_amount,
899 2,usage_rate_or_amount/i_lot_size,
900 usage_rate_or_amount)*
901 crc.resource_rate),
902 -1*SIGN(usage_rate_or_amount),
903 decode(SIGN(nvl(applied_resource_value,0)-
904 nvl(relieved_res_completion_value,0)-
905 nvl(relieved_variance_value,0)-
906 nvl(relieved_res_scrap_value,0)),-
907 SIGN(usage_rate_or_amount),
908 decode( SIGN(applied_resource_units -
909 nvl(relieved_res_completion_units,0) -
910 nvl(relieved_res_final_comp_units,0) -
911 nvl(relieved_res_scrap_units,0)),
912 SIGN(usage_rate_or_amount),
913 (nvl(applied_resource_value,0)-
914 nvl(relieved_res_completion_value,0)-
915 nvl(relieved_variance_value,0)-
916 nvl(relieved_res_scrap_value,0)+
917 (i_txn_qty*decode(basis_type,
918 1,usage_rate_or_amount,
919 2,usage_rate_or_amount/i_lot_size,
920 usage_rate_or_amount) -
921 (applied_resource_units -
922 nvl(relieved_res_completion_units,0) -
923 nvl(relieved_res_final_comp_units,0) -
924 nvl(relieved_res_scrap_units,0)))*
925 crc.resource_rate
926 ),
927 (i_txn_qty*decode(basis_type,
928 1,usage_rate_or_amount,
929 2,usage_rate_or_amount/i_lot_size,
930 usage_rate_or_amount)
931 )*crc.resource_rate
932 ),
933 i_txn_qty*
934 decode(basis_type,
935 1,usage_rate_or_amount,
936 2,usage_rate_or_amount/i_lot_size,
937 usage_rate_or_amount)*
938 crc.resource_rate)),
939 nvl(w1.relieved_res_scrap_value,0) +
940 decode(SIGN(applied_resource_units-
941 nvl(relieved_res_completion_units,0)-
942 nvl(relieved_res_final_comp_units,0)-
943 nvl(relieved_res_scrap_units,0)-
944 i_txn_qty*decode(basis_type,
945 1,usage_rate_or_amount,
946 2,usage_rate_or_amount/i_lot_size,
947 usage_rate_or_amount)),
948 SIGN(usage_rate_or_amount),
949 i_txn_qty*decode(basis_type,
950 1,usage_rate_or_amount,
951 2,usage_rate_or_amount/i_lot_size,
952 usage_rate_or_amount)*
953 decode(SIGN(nvl(applied_resource_value,0)-
954 nvl(relieved_res_completion_value,0)-
955 nvl(relieved_variance_value,0)-
956 nvl(relieved_res_scrap_value,0)),
957 SIGN(usage_rate_or_amount),
958 decode(basis_type,
959 1,((nvl(applied_resource_value,0)-
960 nvl(relieved_res_completion_value,0)-
961 nvl(relieved_variance_value,0)-
962 nvl(relieved_res_scrap_value,0))
963 /(applied_resource_units-
964 nvl(relieved_res_completion_units,0)-
965 nvl(relieved_res_final_comp_units,0)-
966 nvl(relieved_res_scrap_units,0))),
967 2,nvl(applied_resource_value,0)/
968 decode(applied_resource_units,
969 0,1,applied_resource_units),
970 /* Bug4213652 dividing by applied units
971 to get the correct relieved value*/
972 ((nvl(applied_resource_value,0)-
973 nvl(relieved_res_completion_value,0)-
974 nvl(relieved_variance_value,0)-
975 nvl(relieved_res_scrap_value,0))
976 /(applied_resource_units-
977 nvl(relieved_res_completion_units,0)-
978 nvl(relieved_res_final_comp_units,0)-
979 nvl(relieved_res_scrap_units,0)))),
980 crc.resource_rate),
981 0,
982 decode(SIGN(nvl(applied_resource_value,0)-
983 nvl(relieved_res_completion_value,0)-
984 nvl(relieved_variance_value,0)-
985 nvl(relieved_res_scrap_value,0)),
986 SIGN(usage_rate_or_amount),
987 (nvl(applied_resource_value,0)-
988 nvl(relieved_res_completion_value,0)-
989 nvl(relieved_variance_value,0)-
990 nvl(relieved_res_scrap_value,0)),-
991 i_txn_qty*decode(basis_type,
992 1,usage_rate_or_amount,
993 2,usage_rate_or_amount/i_lot_size,
994 usage_rate_or_amount)*
995 crc.resource_rate),
996 -1*SIGN(usage_rate_or_amount),
997 decode(SIGN(nvl(applied_resource_value,0)-
998 nvl(relieved_res_completion_value,0)-
999 nvl(relieved_variance_value,0)-
1000 nvl(relieved_res_scrap_value,0)),-
1001 SIGN(usage_rate_or_amount),
1002 decode( SIGN(applied_resource_units -
1003 nvl(relieved_res_completion_units,0) -
1004 nvl(relieved_res_final_comp_units,0) -
1005 nvl(relieved_res_scrap_units,0)),
1006 SIGN(usage_rate_or_amount),
1007 (nvl(applied_resource_value,0)-
1008 nvl(relieved_res_completion_value,0)-
1009 nvl(relieved_variance_value,0)-
1010 nvl(relieved_res_scrap_value,0)+
1011 (i_txn_qty*decode(basis_type,
1012 1,usage_rate_or_amount,
1013 2,usage_rate_or_amount/i_lot_size,
1014 usage_rate_or_amount) -
1015 (applied_resource_units -
1016 nvl(relieved_res_completion_units,0) -
1017 nvl(relieved_res_final_comp_units,0) -
1018 nvl(relieved_res_scrap_units,0)))*
1019 crc.resource_rate
1020 ),
1021 (i_txn_qty*decode(basis_type,
1022 1,usage_rate_or_amount,
1023 2,usage_rate_or_amount/i_lot_size,
1024 usage_rate_or_amount)
1025 )*crc.resource_rate
1026 ),
1027 i_txn_qty*
1028 decode(basis_type,
1029 1,usage_rate_or_amount,
1030 2,usage_rate_or_amount/i_lot_size,
1031 usage_rate_or_amount)*
1032 crc.resource_rate))
1033 FROM
1034 wip_operation_resources w2,
1035 cst_resource_costs crc
1036 WHERE
1037 w2.wip_entity_id = w1.wip_entity_id AND
1038 w2.operation_seq_num = w1.operation_seq_num AND
1039 w2.resource_seq_num = w1.resource_seq_num AND
1040 w2.organization_id = w2.organization_id AND
1041 w2.resource_id = crc.resource_id AND
1042 w2.organization_id = crc.organization_id AND
1043 crc.cost_type_id = i_res_cost_type_id)
1044 WHERE
1045 w1.wip_entity_id = i_wip_entity_id AND
1046 w1.organization_id = i_org_id AND
1047 w1.usage_rate_or_amount <> 0 AND
1048 w1.operation_seq_num <= i_op_seq_num;
1049
1050
1051
1052 /***********************************************************
1053 * Relieve TL Ovhd (Move based) units and Costs .. *
1054 ***********************************************************/
1055
1056 stmt_num := 185;
1057
1058 -- For the pre-defined completion/scrap algorithm, if no overheads have
1059 -- been charged then they must be relieved at the cost in the
1060 -- rates cost type. However, if nothing has been charged, there are
1061 -- no rows in WOO. So insert these rows.
1062
1063 INSERT INTO WIP_OPERATION_OVERHEADS
1064 (WIP_ENTITY_ID,
1065 OPERATION_SEQ_NUM,
1066 RESOURCE_SEQ_NUM,
1067 ORGANIZATION_ID,
1068 OVERHEAD_ID,
1069 BASIS_TYPE,
1070 APPLIED_OVHD_UNITS,
1071 APPLIED_OVHD_VALUE,
1072 RELIEVED_OVHD_COMPLETION_UNITS,
1073 RELIEVED_OVHD_SCRAP_UNITS,
1074 RELIEVED_OVHD_COMPLETION_VALUE,
1075 RELIEVED_OVHD_SCRAP_VALUE,
1076 TEMP_RELIEVED_VALUE,
1077 LAST_UPDATED_BY,
1078 CREATION_DATE,
1079 CREATED_BY,
1080 LAST_UPDATE_LOGIN,
1081 REQUEST_ID,
1082 PROGRAM_APPLICATION_ID,
1083 PROGRAM_ID,
1084 PROGRAM_UPDATE_DATE,
1085 LAST_UPDATE_DATE)
1086 SELECT
1087 i_wip_entity_id,
1088 wo.operation_seq_num,
1089 -1,
1090 i_org_id,
1091 cdo.overhead_id,
1092 cdo.basis_type,
1093 0,
1094 0,
1095 0,
1096 0,
1097 0,
1098 0,
1099 0,
1100 -1,
1101 SYSDATE,
1102 -1,
1103 -1,
1104 -1,
1105 -1,
1106 -1,
1107 SYSDATE,
1108 SYSDATE
1109 FROM
1110 WIP_OPERATIONS WO,
1111 CST_DEPARTMENT_OVERHEADS CDO
1112 WHERE
1113 WO.WIP_ENTITY_ID = i_wip_entity_id AND
1114 WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
1115 CDO.COST_TYPE_ID = i_res_cost_type_id AND
1116 CDO.BASIS_TYPE IN (1,2) AND
1117 WO.OPERATION_SEQ_NUM <= i_op_seq_num AND
1118 NOT EXISTS
1119 (SELECT 'X'
1120 FROM
1121 WIP_OPERATION_OVERHEADS WOO
1122 where
1123 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
1124 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
1125 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
1126 WOO.BASIS_TYPE = CDO.BASIS_TYPE AND
1127 WOO.RESOURCE_SEQ_NUM = -1);
1128
1129 stmt_num := 190;
1130
1131 UPDATE wip_operation_overheads w1
1132 SET
1133 (relieved_ovhd_scrap_units,
1134 temp_relieved_value,
1135 relieved_ovhd_scrap_value) =
1136 (SELECT
1137 nvl(w1.relieved_ovhd_scrap_units,0)+
1138 decode(w2.basis_type,
1139 1,i_txn_qty,
1140 2,i_txn_qty/i_lot_size),
1141 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1142 nvl(relieved_ovhd_completion_units,0)-
1143 nvl(relieved_ovhd_final_comp_units,0)-
1144 nvl(relieved_ovhd_scrap_units,0)-
1145 decode(w2.basis_type,
1146 1,i_txn_qty,
1147 2,i_txn_qty/i_lot_size)),
1148 1,
1149 decode(SIGN(nvl(applied_ovhd_value,0)-
1150 nvl(relieved_ovhd_completion_value,0)-
1151 nvl(relieved_variance_value,0)-
1152 nvl(relieved_ovhd_scrap_value,0)),
1153 1,
1154 decode(w2.basis_type,
1155 2,nvl(applied_ovhd_value,0),
1156 (nvl(applied_ovhd_value,0)-
1157 nvl(relieved_ovhd_completion_value,0)-
1158 nvl(relieved_variance_value,0)-
1159 nvl(relieved_ovhd_scrap_value,0))
1160 /(nvl(applied_ovhd_units,0)-
1161 nvl(relieved_ovhd_completion_units,0)-
1162 nvl(relieved_ovhd_final_comp_units,0)-
1163 nvl(relieved_ovhd_scrap_units,0)))*
1164 decode(w2.basis_type,
1165 1,i_txn_qty,
1166 2,i_txn_qty/i_lot_size),
1167 cdo.rate_or_amount*
1168 decode(w2.basis_type,
1169 1,i_txn_qty,
1170 2,i_txn_qty/i_lot_size)),
1171 0,
1172 decode(SIGN(nvl(applied_ovhd_value,0)-
1173 nvl(relieved_ovhd_completion_value,0)-
1174 nvl(relieved_variance_value,0)-
1175 nvl(relieved_ovhd_scrap_value,0)),
1176 1,
1177 (nvl(applied_ovhd_value,0)-
1178 nvl(relieved_ovhd_completion_value,0)-
1179 nvl(relieved_variance_value,0)-
1180 nvl(relieved_ovhd_scrap_value,0)),
1181 cdo.rate_or_amount*
1182 decode(w2.basis_type,
1183 1,i_txn_qty,
1184 2,i_txn_qty/i_lot_size)),
1185 -1,
1186 decode(SIGN(nvl(applied_ovhd_value,0)-
1187 nvl(relieved_ovhd_completion_value,0)-
1188 nvl(relieved_variance_value,0)-
1189 nvl(relieved_ovhd_scrap_value,0)),
1190 1,
1191 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1192 nvl(relieved_ovhd_completion_units,0)-
1193 nvl(relieved_ovhd_final_comp_units,0)-
1194 nvl(relieved_ovhd_scrap_units,0)),
1195 1,
1196 (nvl(applied_ovhd_value,0)-
1197 nvl(relieved_ovhd_completion_value,0)-
1198 nvl(relieved_variance_value,0)-
1199 nvl(relieved_ovhd_scrap_value,0)+
1200 (decode(w2.basis_type,
1201 1,i_txn_qty,
1202 2,i_txn_qty/i_lot_size)-
1203 (nvl(w2.applied_ovhd_units,0)-
1204 nvl(relieved_ovhd_completion_units,0)-
1205 nvl(relieved_ovhd_final_comp_units,0)-
1206 nvl(relieved_ovhd_scrap_units,0)))*
1207 cdo.rate_or_amount
1208 ),
1209 (decode(w2.basis_type,
1210 1,i_txn_qty,
1211 2,i_txn_qty/i_lot_size)
1212 )*cdo.rate_or_amount
1213 ),
1214 cdo.rate_or_amount*
1215 decode(w2.basis_type,
1216 1,i_txn_qty,
1217 2,i_txn_qty/i_lot_size))),
1218 nvl(w1.relieved_ovhd_scrap_value,0) +
1219 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1220 nvl(relieved_ovhd_completion_units,0)-
1221 nvl(relieved_ovhd_final_comp_units,0)-
1222 nvl(relieved_ovhd_scrap_units,0)-
1223 decode(w2.basis_type,
1224 1,i_txn_qty,
1225 2,i_txn_qty/i_lot_size)),
1226 1,
1227 decode(SIGN(nvl(applied_ovhd_value,0)-
1228 nvl(relieved_ovhd_completion_value,0)-
1229 nvl(relieved_variance_value,0)-
1230 nvl(relieved_ovhd_scrap_value,0)),
1231 1,
1232 decode(w2.basis_type,
1233 2,nvl(applied_ovhd_value,0),
1234 (nvl(applied_ovhd_value,0)-
1235 nvl(relieved_ovhd_completion_value,0)-
1236 nvl(relieved_variance_value,0)-
1237 nvl(relieved_ovhd_scrap_value,0))
1238 /(nvl(applied_ovhd_units,0)-
1239 nvl(relieved_ovhd_completion_units,0)-
1240 nvl(relieved_ovhd_final_comp_units,0)-
1241 nvl(relieved_ovhd_scrap_units,0)))*
1242 decode(w2.basis_type,
1243 1,i_txn_qty,
1244 2,i_txn_qty/i_lot_size),
1245 cdo.rate_or_amount*
1246 decode(w2.basis_type,
1247 1,i_txn_qty,
1248 2,i_txn_qty/i_lot_size)),
1249 0,
1250 decode(SIGN(nvl(applied_ovhd_value,0)-
1251 nvl(relieved_ovhd_completion_value,0)-
1252 nvl(relieved_variance_value,0)-
1253 nvl(relieved_ovhd_scrap_value,0)),
1254 1,
1255 (nvl(applied_ovhd_value,0)-
1256 nvl(relieved_ovhd_completion_value,0)-
1257 nvl(relieved_variance_value,0)-
1258 nvl(relieved_ovhd_scrap_value,0)),
1259 cdo.rate_or_amount*
1260 decode(w2.basis_type,
1261 1,i_txn_qty,
1262 2,i_txn_qty/i_lot_size)),
1263 -1,
1264 decode(SIGN(nvl(applied_ovhd_value,0)-
1265 nvl(relieved_ovhd_completion_value,0)-
1266 nvl(relieved_variance_value,0)-
1267 nvl(relieved_ovhd_scrap_value,0)),
1268 1,
1269 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1270 nvl(relieved_ovhd_completion_units,0)-
1271 nvl(relieved_ovhd_final_comp_units,0)-
1272 nvl(relieved_ovhd_scrap_units,0)),
1273 1,
1274 (nvl(applied_ovhd_value,0)-
1275 nvl(relieved_ovhd_completion_value,0)-
1276 nvl(relieved_variance_value,0)-
1277 nvl(relieved_ovhd_scrap_value,0)+
1278 (decode(w2.basis_type,
1279 1,i_txn_qty,
1280 2,i_txn_qty/i_lot_size)-
1281 (nvl(w2.applied_ovhd_units,0)-
1282 nvl(relieved_ovhd_completion_units,0)-
1283 nvl(relieved_ovhd_final_comp_units,0)-
1284 nvl(relieved_ovhd_scrap_units,0)))*
1285 cdo.rate_or_amount
1286 ),
1287 (decode(w2.basis_type,
1288 1,i_txn_qty,
1289 2,i_txn_qty/i_lot_size)
1290 )*cdo.rate_or_amount
1291 ),
1292 cdo.rate_or_amount*
1293 decode(w2.basis_type,
1294 1,i_txn_qty,
1295 2,i_txn_qty/i_lot_size)))
1296 FROM
1297 wip_operation_overheads w2,
1298 cst_department_overheads cdo,
1299 wip_operations wo
1300 WHERE
1301 w2.wip_entity_id = w1.wip_entity_id AND
1302 w2.organization_id = w1.organization_id AND
1303 w2.operation_seq_num = w1.operation_seq_num AND
1304 w2.resource_seq_num = w1.resource_seq_num AND
1305 w2.overhead_id = w1.overhead_id AND
1306 w2.basis_type = w1.basis_type AND
1307 w2.wip_entity_id = wo.wip_entity_id AND
1308 w2.organization_id = wo.organization_id AND
1309 w2.operation_seq_num = wo.operation_seq_num AND
1310 cdo.department_id = wo.department_id AND
1311 cdo.overhead_id = w2.overhead_id AND
1312 cdo.basis_type = w2.basis_type AND
1313 cdo.cost_type_id = i_res_cost_type_id)
1314 WHERE
1315 w1.wip_entity_id = i_wip_entity_id AND
1316 w1.organization_id = i_org_id AND
1317 w1.operation_seq_num <= i_op_seq_num AND
1318 w1.basis_type IN (1,2) AND
1319 w1.resource_seq_num = -1 AND
1320 EXISTS
1321 (
1322 SELECT 'X'
1323 FROM
1324 cst_department_overheads cdo2,
1325 wip_operations wo2
1326 WHERE
1327 wo2.wip_entity_id = w1.wip_entity_id AND
1328 wo2.organization_id = w1.organization_id AND
1329 wo2.operation_seq_num = w1.operation_seq_num AND
1330 wo2.department_id = cdo2.department_id AND
1331 w1.overhead_id = cdo2.overhead_id AND
1332 w1.basis_type = cdo2.basis_type AND
1333 cdo2.cost_type_id = i_res_cost_type_id);
1334
1335
1336 /**********************************************************
1337 * There may be overheads in wip_operation_overheads whose *
1338 * Basis may be different from thatin cst_dept_ovhds ,so *
1339 * we need to relieve these anyway if there is value to re-*
1340 * lieve. - OPEN ISSUE - DO WE DO THIS OR NOT?? *
1341 **********************************************************/
1342
1343
1344 stmt_num := 210;
1345
1346 UPDATE WIP_OPERATION_OVERHEADS w1
1347 set
1348 (relieved_ovhd_scrap_units,
1349 temp_relieved_value,
1350 relieved_ovhd_scrap_value) =
1351 (SELECT
1352 nvl(w1.relieved_ovhd_scrap_units,0)+
1353 decode(w2.basis_type,
1354 1,i_txn_qty,
1355 2,i_txn_qty/i_lot_size),
1356 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1357 nvl(relieved_ovhd_completion_units,0)-
1358 nvl(relieved_ovhd_final_comp_units,0)-
1359 nvl(relieved_ovhd_scrap_units,0)-
1360 decode(w2.basis_type,
1361 1,i_txn_qty,
1362 2,i_txn_qty/i_lot_size)),
1363 1,
1364 decode(SIGN(nvl(applied_ovhd_value,0)-
1365 nvl(relieved_ovhd_completion_value,0)-
1366 nvl(relieved_variance_value,0)-
1367 nvl(relieved_ovhd_scrap_value,0)),
1368 1,
1369 decode(w2.basis_type,
1370 2,nvl(applied_ovhd_value,0),
1371 (nvl(applied_ovhd_value,0)-
1372 nvl(relieved_ovhd_completion_value,0)-
1373 nvl(relieved_variance_value,0)-
1374 nvl(relieved_ovhd_scrap_value,0))
1375 /(nvl(applied_ovhd_units,0)-
1376 nvl(relieved_ovhd_completion_units,0)-
1377 nvl(relieved_ovhd_final_comp_units,0)-
1378 nvl(relieved_ovhd_scrap_units,0)))*
1379 decode(w2.basis_type,
1380 1,i_txn_qty,
1381 2,i_txn_qty/i_lot_size),
1382 0),
1383 0,
1384 decode(SIGN(nvl(applied_ovhd_value,0)-
1385 nvl(relieved_ovhd_completion_value,0)-
1386 nvl(relieved_variance_value,0)-
1387 nvl(relieved_ovhd_scrap_value,0)),
1388 1,
1389 (nvl(applied_ovhd_value,0)-
1390 nvl(relieved_ovhd_completion_value,0)-
1391 nvl(relieved_variance_value,0)-
1392 nvl(relieved_ovhd_scrap_value,0)),
1393 0),
1394 -1,
1395 decode(SIGN(nvl(applied_ovhd_value,0)-
1396 nvl(relieved_ovhd_completion_value,0)-
1397 nvl(relieved_variance_value,0)-
1398 nvl(relieved_ovhd_scrap_value,0)),
1399 1,
1400 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1401 nvl(relieved_ovhd_completion_units,0)-
1402 nvl(relieved_ovhd_final_comp_units,0)-
1403 nvl(relieved_ovhd_scrap_units,0)),
1404 1,
1405 (nvl(applied_ovhd_value,0)-
1406 nvl(relieved_ovhd_completion_value,0)-
1407 nvl(relieved_variance_value,0)-
1408 nvl(relieved_ovhd_scrap_value,0)+
1409 (decode(w2.basis_type,
1410 1,i_txn_qty,
1411 2,i_txn_qty/i_lot_size)-
1412 (nvl(w2.applied_ovhd_units,0)-
1413 nvl(relieved_ovhd_completion_units,0)-
1414 nvl(relieved_ovhd_final_comp_units,0)-
1415 nvl(relieved_ovhd_scrap_units,0)))*0
1416 ),
1417 (decode(w2.basis_type,
1418 1,i_txn_qty,
1419 2,i_txn_qty/i_lot_size)
1420 )*0
1421 ),
1422 0)),
1423 nvl(w1.relieved_ovhd_scrap_value,0)+
1424 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1425 nvl(relieved_ovhd_completion_units,0)-
1426 nvl(relieved_ovhd_final_comp_units,0)-
1427 nvl(relieved_ovhd_scrap_units,0)-
1428 decode(w2.basis_type,
1429 1,i_txn_qty,
1430 2,i_txn_qty/i_lot_size)),
1431 1,
1432 decode(SIGN(nvl(applied_ovhd_value,0)-
1433 nvl(relieved_ovhd_completion_value,0)-
1434 nvl(relieved_variance_value,0)-
1435 nvl(relieved_ovhd_scrap_value,0)),
1436 1,
1437 decode(w2.basis_type,
1438 2,nvl(applied_ovhd_value,0),
1439 (nvl(applied_ovhd_value,0)-
1440 nvl(relieved_ovhd_completion_value,0)-
1441 nvl(relieved_variance_value,0)-
1442 nvl(relieved_ovhd_scrap_value,0))
1443 /(nvl(applied_ovhd_units,0)-
1444 nvl(relieved_ovhd_completion_units,0)-
1445 nvl(relieved_ovhd_final_comp_units,0)-
1446 nvl(relieved_ovhd_scrap_units,0)))*
1447 decode(w2.basis_type,
1448 1,i_txn_qty,
1449 2,i_txn_qty/i_lot_size),
1450 0),
1451 0,
1452 decode(SIGN(nvl(applied_ovhd_value,0)-
1453 nvl(relieved_ovhd_completion_value,0)-
1454 nvl(relieved_variance_value,0)-
1455 nvl(relieved_ovhd_scrap_value,0)),
1456 1,
1457 (nvl(applied_ovhd_value,0)-
1458 nvl(relieved_ovhd_completion_value,0)-
1459 nvl(relieved_variance_value,0)-
1460 nvl(relieved_ovhd_scrap_value,0)),
1461 0),
1462 -1,
1463 decode(SIGN(nvl(applied_ovhd_value,0)-
1464 nvl(relieved_ovhd_completion_value,0)-
1465 nvl(relieved_variance_value,0)-
1466 nvl(relieved_ovhd_scrap_value,0)),
1467 1,
1468 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1469 nvl(relieved_ovhd_completion_units,0)-
1470 nvl(relieved_ovhd_final_comp_units,0)-
1471 nvl(relieved_ovhd_scrap_units,0)),
1472 1,
1473 (nvl(applied_ovhd_value,0)-
1474 nvl(relieved_ovhd_completion_value,0)-
1475 nvl(relieved_variance_value,0)-
1476 nvl(relieved_ovhd_scrap_value,0)+
1477 (decode(w2.basis_type,
1478 1,i_txn_qty,
1479 2,i_txn_qty/i_lot_size)-
1480 (nvl(w2.applied_ovhd_units,0)-
1481 nvl(relieved_ovhd_completion_units,0)-
1482 nvl(relieved_ovhd_final_comp_units,0)-
1483 nvl(relieved_ovhd_scrap_units,0)))*0
1484 ),
1485 (decode(w2.basis_type,
1486 1,i_txn_qty,
1487 2,i_txn_qty/i_lot_size)
1488 )*0
1489 ),
1490 0))
1491 FROM
1492 WIP_OPERATION_OVERHEADS w2
1493 WHERE
1494 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1495 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1496 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1497 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
1498 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
1499 W2.BASIS_TYPE = W1.BASIS_TYPE)
1500 WHERE
1501 W1.WIP_ENTITY_ID = i_wip_entity_id AND
1502 W1.ORGANIZATION_ID = i_org_id AND
1503 W1.OPERATION_SEQ_NUM <= i_op_seq_num AND
1504 W1.BASIS_TYPE IN (1,2) AND
1505 W1.RESOURCE_SEQ_NUM = -1 AND
1506 NOT EXISTS
1507 (
1508 SELECT 'X'
1509 FROM
1510 CST_DEPARTMENT_OVERHEADS CDO2,
1511 WIP_OPERATIONS WO2
1512 WHERE
1513 WO2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
1514 WO2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
1515 WO2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
1516 WO2.DEPARTMENT_ID = CDO2.DEPARTMENT_ID AND
1517 W1.OVERHEAD_ID = CDO2.OVERHEAD_ID AND
1518 W1.BASIS_TYPE = CDO2.BASIS_TYPE AND
1519 CDO2.COST_TYPE_ID = i_res_cost_type_id);
1520
1521
1522 /***********************************************************
1523 * Relieve TL Res based overheads and Units ... *
1524 ***********************************************************/
1525
1526 stmt_num := 225;
1527
1528 INSERT INTO WIP_OPERATION_OVERHEADS
1529 (WIP_ENTITY_ID,
1530 OPERATION_SEQ_NUM,
1531 RESOURCE_SEQ_NUM,
1532 ORGANIZATION_ID,
1533 OVERHEAD_ID,
1534 BASIS_TYPE,
1535 APPLIED_OVHD_UNITS,
1536 APPLIED_OVHD_VALUE,
1537 RELIEVED_OVHD_COMPLETION_UNITS,
1538 RELIEVED_OVHD_SCRAP_UNITS,
1539 RELIEVED_OVHD_COMPLETION_VALUE,
1540 RELIEVED_OVHD_SCRAP_VALUE,
1541 TEMP_RELIEVED_VALUE,
1542 LAST_UPDATED_BY,
1543 CREATION_DATE,
1544 CREATED_BY,
1545 LAST_UPDATE_LOGIN,
1546 REQUEST_ID,
1547 PROGRAM_APPLICATION_ID,
1548 PROGRAM_ID,
1549 PROGRAM_UPDATE_DATE,
1550 LAST_UPDATE_DATE)
1551 SELECT
1552 i_wip_entity_id,
1553 wo.operation_seq_num,
1554 wor.resource_seq_num,
1555 i_org_id,
1556 cdo.overhead_id,
1557 cdo.basis_type,
1558 0,
1559 0,
1560 0,
1561 0,
1562 0,
1563 0,
1564 0,
1565 -1,
1566 SYSDATE,
1567 -1,
1568 -1,
1569 -1,
1570 -1,
1571 -1,
1572 SYSDATE,
1573 SYSDATE
1574 FROM
1575 WIP_OPERATIONS WO,
1576 WIP_OPERATION_RESOURCES WOR,
1577 CST_DEPARTMENT_OVERHEADS CDO,
1578 CST_RESOURCE_OVERHEADS CRO
1579 WHERE
1580 WO.WIP_ENTITY_ID = i_wip_entity_id AND
1581 WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
1582 WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
1583 CDO.DEPARTMENT_ID = DECODE(WOR.PHANTOM_FLAG,
1584 1, WOR.DEPARTMENT_ID,
1585 WO.DEPARTMENT_ID) AND
1586 CDO.COST_TYPE_ID = i_res_cost_type_id AND
1587 CDO.BASIS_TYPE IN (3,4) AND
1588 CRO.COST_TYPE_ID = i_res_cost_type_id AND
1589 CRO.RESOURCE_ID = WOR.RESOURCE_ID AND
1590 CRO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
1591 WO.OPERATION_SEQ_NUM <= i_op_seq_num AND
1592 NOT EXISTS
1593 (SELECT 'X'
1594 FROM
1595 WIP_OPERATION_OVERHEADS WOO
1596 WHERE
1597 WOO.WIP_ENTITY_ID = i_wip_entity_id AND
1598 WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
1599 WOO.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM AND
1600 WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
1601 WOO.BASIS_TYPE = CDO.BASIS_TYPE);
1602
1603 stmt_num := 230;
1604
1605 UPDATE wip_operation_overheads w1
1606 SET
1607 (relieved_ovhd_scrap_units,
1608 temp_relieved_value,
1609 relieved_ovhd_scrap_value) =
1610 (SELECT
1611 nvl(w1.relieved_ovhd_scrap_units,0)+
1612 decode(w2.basis_type,
1613 3,i_txn_qty*decode(wor.basis_type,
1614 1,usage_rate_or_amount,
1615 2,usage_rate_or_amount/i_lot_size,
1616 usage_rate_or_amount),
1617 4,wor.temp_relieved_value),
1618 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1619 nvl(relieved_ovhd_completion_units,0)-
1620 nvl(relieved_ovhd_final_comp_units,0)-
1621 nvl(relieved_ovhd_scrap_units,0)-
1622 decode(w2.basis_type,
1623 3,i_txn_qty*decode(wor.basis_type,
1624 1,usage_rate_or_amount,
1625 2,usage_rate_or_amount/i_lot_size,
1626 usage_rate_or_amount),
1627 4,wor.temp_relieved_value)),
1628 SIGN(wor.usage_rate_or_amount),
1629 decode(SIGN(nvl(applied_ovhd_value,0)-
1630 nvl(relieved_ovhd_completion_value,0)-
1631 nvl(w2.relieved_variance_value,0)-
1632 nvl(relieved_ovhd_scrap_value,0)),
1633 SIGN(wor.usage_rate_or_amount),
1634 ((nvl(applied_ovhd_value,0)-
1635 nvl(relieved_ovhd_completion_value,0)-
1636 nvl(w2.relieved_variance_value,0)-
1637 nvl(relieved_ovhd_scrap_value,0))
1638 /(nvl(applied_ovhd_units,0)-
1639 nvl(relieved_ovhd_completion_units,0)-
1640 nvl(relieved_ovhd_final_comp_units,0)-
1641 nvl(relieved_ovhd_scrap_units,0)))*
1642 decode(w2.basis_type,
1643 3,i_txn_qty*
1644 decode(wor.basis_type,
1645 1,wor.usage_rate_or_amount,
1646 2,wor.usage_rate_or_amount/i_lot_size),
1647 4,nvl(wor.temp_relieved_value,0)),
1648 nvl(cdo.rate_or_amount,0)*
1649 decode(w2.basis_type,
1650 3,i_txn_qty*
1651 decode(wor.basis_type,
1652 1,wor.usage_rate_or_amount,
1653 2,wor.usage_rate_or_amount/i_lot_size),
1654 4,nvl(wor.temp_relieved_value,0))),
1655 0,
1656 decode(SIGN(nvl(applied_ovhd_value,0)-
1657 nvl(relieved_ovhd_completion_value,0)-
1658 nvl(w2.relieved_variance_value,0)-
1659 nvl(relieved_ovhd_scrap_value,0)),
1660 SIGN(wor.usage_rate_or_amount),
1661 (nvl(applied_ovhd_value,0)-
1662 nvl(relieved_ovhd_completion_value,0)-
1663 nvl(w2.relieved_variance_value,0)-
1664 nvl(relieved_ovhd_scrap_value,0)),
1665 nvl(cdo.rate_or_amount,0)*
1666 decode(w2.basis_type,
1667 3,i_txn_qty*
1668 decode(wor.basis_type,
1669 1,wor.usage_rate_or_amount,
1670 2,wor.usage_rate_or_amount/i_lot_size),
1671 4,nvl(wor.temp_relieved_value,0))),
1672 -1*SIGN(wor.usage_rate_or_amount),
1673 decode(SIGN(nvl(applied_ovhd_value,0)-
1674 nvl(relieved_ovhd_completion_value,0)-
1675 nvl(w2.relieved_variance_value,0)-
1676 nvl(relieved_ovhd_scrap_value,0)),
1677 SIGN(wor.usage_rate_or_amount),
1678 decode( SIGN(nvl(w2.applied_ovhd_units,0)-
1679 nvl(relieved_ovhd_completion_units,0)-
1680 nvl(relieved_ovhd_final_comp_units,0)-
1681 nvl(relieved_ovhd_scrap_units,0)),
1682 SIGN(wor.usage_rate_or_amount),
1683 (nvl(applied_ovhd_value,0)-
1684 nvl(relieved_ovhd_completion_value,0)-
1685 nvl(w2.relieved_variance_value,0)-
1686 nvl(relieved_ovhd_scrap_value,0)+
1687 (decode(w2.basis_type,
1688 3,i_txn_qty*decode(wor.basis_type,
1689 1,usage_rate_or_amount,
1690 2,usage_rate_or_amount/i_lot_size,
1691 usage_rate_or_amount),
1692 4,wor.temp_relieved_value)-
1693 (nvl(w2.applied_ovhd_units,0)-
1694 nvl(relieved_ovhd_completion_units,0)-
1695 nvl(relieved_ovhd_final_comp_units,0)-
1696 nvl(relieved_ovhd_scrap_units,0)))*
1697 nvl(cdo.rate_or_amount,0)
1698 ),
1699 (decode(w2.basis_type,
1700 3,i_txn_qty*decode(wor.basis_type,
1701 1,usage_rate_or_amount,
1702 2,usage_rate_or_amount/i_lot_size,
1703 usage_rate_or_amount),
1704 4,wor.temp_relieved_value)
1705 )*nvl(cdo.rate_or_amount,0)
1706 ),
1707 nvl(cdo.rate_or_amount,0)*
1708 decode(w2.basis_type,
1709 3,i_txn_qty*
1710 decode(wor.basis_type,
1711 1,wor.usage_rate_or_amount,
1712 2,wor.usage_rate_or_amount/i_lot_size),
1713 4,nvl(wor.temp_relieved_value,0)))),
1714 nvl(w1.relieved_ovhd_scrap_value,0) +
1715 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1716 nvl(relieved_ovhd_completion_units,0)-
1717 nvl(relieved_ovhd_final_comp_units,0)-
1718 nvl(relieved_ovhd_scrap_units,0)-
1719 decode(w2.basis_type,
1720 3,i_txn_qty*decode(wor.basis_type,
1721 1,usage_rate_or_amount,
1722 2,usage_rate_or_amount/i_lot_size,
1723 usage_rate_or_amount),
1724 4,wor.temp_relieved_value)),
1725 SIGN(wor.usage_rate_or_amount),
1726 decode(SIGN(nvl(applied_ovhd_value,0)-
1727 nvl(relieved_ovhd_completion_value,0)-
1728 nvl(w2.relieved_variance_value,0)-
1729 nvl(relieved_ovhd_scrap_value,0)),
1730 SIGN(wor.usage_rate_or_amount),
1731 ((nvl(applied_ovhd_value,0)-
1732 nvl(relieved_ovhd_completion_value,0)-
1733 nvl(w2.relieved_variance_value,0)-
1734 nvl(relieved_ovhd_scrap_value,0))
1735 /(nvl(applied_ovhd_units,0)-
1736 nvl(relieved_ovhd_completion_units,0)-
1737 nvl(relieved_ovhd_final_comp_units,0)-
1738 nvl(relieved_ovhd_scrap_units,0)))*
1739 decode(w2.basis_type,
1740 3,i_txn_qty*
1741 decode(wor.basis_type,
1742 1,wor.usage_rate_or_amount,
1743 2,wor.usage_rate_or_amount/i_lot_size),
1744 4,nvl(wor.temp_relieved_value,0)),
1745 nvl(cdo.rate_or_amount,0)*
1746 decode(w2.basis_type,
1747 3,i_txn_qty*
1748 decode(wor.basis_type,
1749 1,wor.usage_rate_or_amount,
1750 2,wor.usage_rate_or_amount/i_lot_size),
1751 4,nvl(wor.temp_relieved_value,0))),
1752 0,
1753 decode(SIGN(nvl(applied_ovhd_value,0)-
1754 nvl(relieved_ovhd_completion_value,0)-
1755 nvl(w2.relieved_variance_value,0)-
1756 nvl(relieved_ovhd_scrap_value,0)),
1757 SIGN(wor.usage_rate_or_amount),
1758 (nvl(applied_ovhd_value,0)-
1759 nvl(relieved_ovhd_completion_value,0)-
1760 nvl(w2.relieved_variance_value,0)-
1761 nvl(relieved_ovhd_scrap_value,0)),
1762 nvl(cdo.rate_or_amount,0)*
1763 decode(w2.basis_type,
1764 3,i_txn_qty*
1765 decode(wor.basis_type,
1766 1,wor.usage_rate_or_amount,
1767 2,wor.usage_rate_or_amount/i_lot_size),
1768 4,nvl(wor.temp_relieved_value,0))),
1769 -1*SIGN(wor.usage_rate_or_amount),
1770 decode(SIGN(nvl(applied_ovhd_value,0)-
1771 nvl(relieved_ovhd_completion_value,0)-
1772 nvl(w2.relieved_variance_value,0)-
1773 nvl(relieved_ovhd_scrap_value,0)),
1774 SIGN(wor.usage_rate_or_amount),
1775 decode( SIGN(nvl(w2.applied_ovhd_units,0)-
1776 nvl(relieved_ovhd_completion_units,0)-
1777 nvl(relieved_ovhd_final_comp_units,0)-
1778 nvl(relieved_ovhd_scrap_units,0)),
1779 SIGN(wor.usage_rate_or_amount),
1780 (nvl(applied_ovhd_value,0)-
1781 nvl(relieved_ovhd_completion_value,0)-
1782 nvl(w2.relieved_variance_value,0)-
1783 nvl(relieved_ovhd_scrap_value,0)+
1784 (decode(w2.basis_type,
1785 3,i_txn_qty*decode(wor.basis_type,
1786 1,usage_rate_or_amount,
1787 2,usage_rate_or_amount/i_lot_size,
1788 usage_rate_or_amount),
1789 4,wor.temp_relieved_value)-
1790 (nvl(w2.applied_ovhd_units,0)-
1791 nvl(relieved_ovhd_completion_units,0)-
1792 nvl(relieved_ovhd_final_comp_units,0)-
1793 nvl(relieved_ovhd_scrap_units,0)))*
1794 nvl(cdo.rate_or_amount,0)
1795 ),
1796 (decode(w2.basis_type,
1797 3,i_txn_qty*decode(wor.basis_type,
1798 1,usage_rate_or_amount,
1799 2,usage_rate_or_amount/i_lot_size,
1800 usage_rate_or_amount),
1801 4,wor.temp_relieved_value)
1802 )*nvl(cdo.rate_or_amount,0)
1803 ),
1804 nvl(cdo.rate_or_amount,0)*
1805 decode(w2.basis_type,
1806 3,i_txn_qty*
1807 decode(wor.basis_type,
1808 1,wor.usage_rate_or_amount,
1809 2,wor.usage_rate_or_amount/i_lot_size),
1810 4,nvl(wor.temp_relieved_value,0))))
1811 FROM
1812 wip_operation_overheads w2,
1813 cst_department_overheads cdo,
1814 wip_operations wo,
1815 wip_operation_resources wor,
1816 cst_resource_overheads cro
1817 WHERE
1818 w2.wip_entity_id = w1.wip_entity_id AND
1819 w2.organization_id = w1.organization_id AND
1820 w2.operation_seq_num = w1.operation_seq_num AND
1821 w2.overhead_id = w1.overhead_id AND
1822 w2.basis_type = w1.basis_type AND
1823 w2.resource_seq_num = w1.resource_seq_num AND
1824 w2.wip_entity_id = wo.wip_entity_id AND
1825 w2.organization_id = wo.organization_id AND
1826 w2.operation_seq_num = wo.operation_seq_num AND
1827 w2.wip_entity_id = wor.wip_entity_id AND
1828 w2.organization_id = wor.organization_id AND
1829 w2.operation_seq_num = wor.operation_seq_num AND
1830 w2.resource_seq_num = wor.resource_seq_num AND
1831 CDO.DEPARTMENT_ID = DECODE(WOR.PHANTOM_FLAG,
1832 1, WOR.DEPARTMENT_ID,
1833 WO.DEPARTMENT_ID) AND
1834 cdo.overhead_id = w2.overhead_id AND
1835 cdo.basis_type = w2.basis_type AND
1836 cdo.cost_type_id = i_res_cost_type_id AND
1837 cro.overhead_id = cdo.overhead_id AND
1838 cro.resource_id = wor.resource_id AND
1839 cro.cost_type_id = i_res_cost_type_id)
1840 WHERE
1841 w1.wip_entity_id = i_wip_entity_id AND
1842 w1.organization_id = i_org_id AND
1843 w1.operation_seq_num <= i_op_seq_num AND
1844 w1.basis_type IN (3,4) AND
1845 EXISTS
1846 (
1847 SELECT 'X'
1848 FROM
1849 cst_department_overheads cdo2,
1850 wip_operations wo2,
1851 cst_resource_overheads cro2,
1852 wip_operation_resources wor2
1853 WHERE
1854 w1.wip_entity_id = wo2.wip_entity_id AND
1855 w1.organization_id = wo2.organization_id AND
1856 w1.operation_seq_num = wo2.operation_seq_num AND
1857 w1.wip_entity_id = wor2.wip_entity_id AND
1858 w1.organization_id = wor2.organization_id AND
1859 w1.operation_seq_num = wor2.operation_seq_num AND
1860 w1.resource_seq_num = wor2.resource_seq_num AND
1861 wor2.usage_rate_or_amount <> 0 AND
1862 cdo2.department_id = DECODE(wor2.phantom_flag,
1863 1, wor2.department_id,
1864 wo2.department_id) AND
1865 cdo2.overhead_id = w1.overhead_id AND
1866 cdo2.basis_type = w1.basis_type AND
1867 cdo2.cost_type_id = i_res_cost_type_id AND
1868 cdo2.overhead_id = cro2.overhead_id AND
1869 cro2.resource_id = wor2.resource_id AND
1870 cro2.cost_type_id = i_res_cost_type_id);
1871
1872
1873
1874 /************************************************************
1875 * Relieve TL Res based Ovhds and Units where association no *
1876 * longer exists. Relieve excess units at zero cost. *
1877 ************************************************************/
1878
1879 stmt_num := 250;
1880
1881 UPDATE wip_operation_overheads w1
1882 SET
1883 (relieved_ovhd_scrap_units,
1884 temp_relieved_value,
1885 relieved_ovhd_scrap_value) =
1886 (SELECT
1887 nvl(w1.relieved_ovhd_scrap_units,0)+
1888 decode(w2.basis_type,
1889 3,i_txn_qty*decode(wor.basis_type,
1890 1,usage_rate_or_amount,
1891 2,usage_rate_or_amount/i_lot_size,
1892 usage_rate_or_amount),
1893 4,wor.temp_relieved_value),
1894 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1895 nvl(relieved_ovhd_completion_units,0)-
1896 nvl(relieved_ovhd_final_comp_units,0)-
1897 nvl(relieved_ovhd_scrap_units,0)-
1898 decode(w2.basis_type,
1899 3,i_txn_qty*decode(wor.basis_type,
1900 1,usage_rate_or_amount,
1901 2,usage_rate_or_amount/i_lot_size,
1902 usage_rate_or_amount),
1903 4,wor.temp_relieved_value)),
1904 SIGN(wor.usage_rate_or_amount),
1905 decode(SIGN(nvl(applied_ovhd_value,0)-
1906 nvl(relieved_ovhd_completion_value,0)-
1907 nvl(w2.relieved_variance_value,0)-
1908 nvl(relieved_ovhd_scrap_value,0)),
1909 SIGN(wor.usage_rate_or_amount),
1910 ((nvl(applied_ovhd_value,0)-
1911 nvl(relieved_ovhd_completion_value,0)-
1912 nvl(w2.relieved_variance_value,0)-
1913 nvl(relieved_ovhd_scrap_value,0))
1914 /(nvl(applied_ovhd_units,0)-
1915 nvl(relieved_ovhd_completion_units,0)-
1916 nvl(relieved_ovhd_final_comp_units,0)-
1917 nvl(relieved_ovhd_scrap_units,0)))*
1918 decode(w2.basis_type,
1919 3,i_txn_qty*
1920 decode(wor.basis_type,
1921 1,wor.usage_rate_or_amount,
1922 2,wor.usage_rate_or_amount/i_lot_size),
1923 4,nvl(wor.temp_relieved_value,0)),
1924 0),
1925 0,
1926 decode(SIGN(nvl(applied_ovhd_value,0)-
1927 nvl(relieved_ovhd_completion_value,0)-
1928 nvl(w2.relieved_variance_value,0)-
1929 nvl(relieved_ovhd_scrap_value,0)),
1930 SIGN(wor.usage_rate_or_amount),
1931 (nvl(applied_ovhd_value,0)-
1932 nvl(relieved_ovhd_completion_value,0)-
1933 nvl(w2.relieved_variance_value,0)-
1934 nvl(relieved_ovhd_scrap_value,0)),
1935 0),
1936 -1*SIGN(wor.usage_rate_or_amount),
1937 decode(SIGN(nvl(applied_ovhd_value,0)-
1938 nvl(relieved_ovhd_completion_value,0)-
1939 nvl(w2.relieved_variance_value,0)-
1940 nvl(relieved_ovhd_scrap_value,0)),
1941 SIGN(wor.usage_rate_or_amount),
1942 (nvl(applied_ovhd_value,0)-
1943 nvl(relieved_ovhd_completion_value,0)-
1944 nvl(w2.relieved_variance_value,0)-
1945 nvl(relieved_ovhd_scrap_value,0)+
1946 0),
1947 0)),
1948 nvl(w1.relieved_ovhd_scrap_value,0) +
1949 decode(SIGN(nvl(w2.applied_ovhd_units,0)-
1950 nvl(relieved_ovhd_completion_units,0)-
1951 nvl(relieved_ovhd_final_comp_units,0)-
1952 nvl(relieved_ovhd_scrap_units,0)-
1953 decode(w2.basis_type,
1954 3,i_txn_qty*decode(wor.basis_type,
1955 1,usage_rate_or_amount,
1956 2,usage_rate_or_amount/i_lot_size,
1957 usage_rate_or_amount),
1958 4,wor.temp_relieved_value)),
1959 SIGN(wor.usage_rate_or_amount),
1960 decode(SIGN(nvl(applied_ovhd_value,0)-
1961 nvl(relieved_ovhd_completion_value,0)-
1962 nvl(w2.relieved_variance_value,0)-
1963 nvl(relieved_ovhd_scrap_value,0)),
1964 SIGN(wor.usage_rate_or_amount),
1965 ((nvl(applied_ovhd_value,0)-
1966 nvl(relieved_ovhd_completion_value,0)-
1967 nvl(w2.relieved_variance_value,0)-
1968 nvl(relieved_ovhd_scrap_value,0))
1969 /(nvl(applied_ovhd_units,0)-
1970 nvl(relieved_ovhd_completion_units,0)-
1971 nvl(relieved_ovhd_final_comp_units,0)-
1972 nvl(relieved_ovhd_scrap_units,0)))*
1973 decode(w2.basis_type,
1974 3,i_txn_qty*
1975 decode(wor.basis_type,
1976 1,wor.usage_rate_or_amount,
1977 2,wor.usage_rate_or_amount/i_lot_size),
1978 4,nvl(wor.temp_relieved_value,0)),
1979 0),
1980 0,
1981 decode(SIGN(nvl(applied_ovhd_value,0)-
1982 nvl(relieved_ovhd_completion_value,0)-
1983 nvl(w2.relieved_variance_value,0)-
1984 nvl(relieved_ovhd_scrap_value,0)),
1985 SIGN(wor.usage_rate_or_amount),
1986 (nvl(applied_ovhd_value,0)-
1987 nvl(relieved_ovhd_completion_value,0)-
1988 nvl(w2.relieved_variance_value,0)-
1989 nvl(relieved_ovhd_scrap_value,0)),
1990 0),
1991 -1*SIGN(wor.usage_rate_or_amount),
1992 decode(SIGN(nvl(applied_ovhd_value,0)-
1993 nvl(relieved_ovhd_completion_value,0)-
1994 nvl(w2.relieved_variance_value,0)-
1995 nvl(relieved_ovhd_scrap_value,0)),
1996 SIGN(wor.usage_rate_or_amount),
1997 (nvl(applied_ovhd_value,0)-
1998 nvl(relieved_ovhd_completion_value,0)-
1999 nvl(w2.relieved_variance_value,0)-
2000 nvl(relieved_ovhd_scrap_value,0)+
2001 0),
2002 0))
2003 FROM
2004 WIP_OPERATION_OVERHEADS w2,
2005 WIP_OPERATION_RESOURCES WOR
2006 WHERE
2007 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
2008 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
2009 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
2010 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
2011 W2.BASIS_TYPE = W1.BASIS_TYPE AND
2012 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
2013 W2.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
2014 W2.ORGANIZATION_ID = WOR.ORGANIZATION_ID AND
2015 W2.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
2016 W2.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM)
2017
2018 WHERE
2019 W1.WIP_ENTITY_ID = i_wip_entity_id AND
2020 W1.ORGANIZATION_ID = i_org_id AND
2021 W1.OPERATION_SEQ_NUM <= i_op_seq_num AND
2022 W1.BASIS_TYPE IN (3,4) AND
2023 NOT EXISTS
2024 (
2025 SELECT 'X'
2026 FROM
2027 CST_DEPARTMENT_OVERHEADS CDO2,
2028 WIP_OPERATIONS WO2,
2029 CST_RESOURCE_OVERHEADS CRO2,
2030 WIP_OPERATION_RESOURCES WOR2
2031 WHERE
2032 W1.WIP_ENTITY_ID = WO2.WIP_ENTITY_ID AND
2033 W1.ORGANIZATION_ID = WO2.ORGANIZATION_ID AND
2034 W1.OPERATION_SEQ_NUM = WO2.OPERATION_SEQ_NUM AND
2035 W1.WIP_ENTITY_ID = WOR2.WIP_ENTITY_ID AND
2036 W1.ORGANIZATION_ID = WOR2.ORGANIZATION_ID AND
2037 W1.OPERATION_SEQ_NUM = WOR2.OPERATION_SEQ_NUM AND
2038 W1.RESOURCE_SEQ_NUM = WOR2.RESOURCE_SEQ_NUM AND
2039 cdo2.department_id = DECODE(wor2.phantom_flag,
2040 1, wor2.department_id,
2041 wo2.department_id) AND
2042 CDO2.OVERHEAD_ID = W1.OVERHEAD_ID AND
2043 CDO2.BASIS_TYPE = W1.BASIS_TYPE AND
2044 CDO2.COST_TYPE_ID = i_res_cost_type_id AND
2045 CDO2.OVERHEAD_ID = CRO2.OVERHEAD_ID AND
2046 CRO2.RESOURCE_ID = WOR2.RESOURCE_ID AND
2047 CRO2.COST_TYPE_ID = i_res_cost_type_id);
2048
2049 END IF; -- The system option if ends here.
2050
2051
2052 stmt_num := 260;
2053
2054 INSERT INTO wip_scrap_values
2055 (
2056 transaction_id,
2057 level_type,
2058 cost_element_id,
2059 cost_update_id,
2060 last_update_date,
2061 last_updated_by,
2062 created_by,
2063 creation_date,
2064 last_update_login,
2065 cost_element_value,
2066 request_id,
2067 program_application_id,
2068 program_id,
2069 program_update_date
2070 )
2071 SELECT
2072 i_trx_id,
2073 1,
2074 br.cost_element_id,
2075 NULL,
2076 SYSDATE,
2077 -1,
2078 -1,
2079 SYSDATE,
2080 -1,
2081 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2082 -1,
2083 -1,
2084 -1,
2085 SYSDATE
2086 FROM
2087 wip_operation_resources wor,
2088 bom_resources br
2089 WHERE
2090 br.resource_id = wor.resource_id AND
2091 br.organization_id = wor.organization_id AND
2092 wip_entity_id = i_wip_entity_id AND
2093 wor.organization_id = i_org_id
2094 group by br.cost_element_id
2095 HAVING
2096 SUM(nvl(temp_relieved_value,0)) <> 0;
2097
2098
2099 stmt_num := 270;
2100
2101 INSERT INTO wip_scrap_values
2102 (
2103 transaction_id,
2104 level_type,
2105 cost_element_id,
2106 cost_update_id,
2107 last_update_date,
2108 last_updated_by,
2109 created_by,
2110 creation_date,
2111 last_update_login,
2112 cost_element_value,
2113 request_id,
2114 program_application_id,
2115 program_id,
2116 program_update_date
2117 )
2118 SELECT
2119 i_trx_id,
2120 1,
2121 5,
2122 NULL,
2123 SYSDATE,
2124 -1,
2125 -1,
2126 SYSDATE,
2127 -1,
2128 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2129 -1,
2130 -1,
2131 -1,
2132 SYSDATE
2133 FROM
2134 WIP_OPERATION_OVERHEADS
2135 WHERE
2136 wip_entity_id = i_wip_entity_id AND
2137 organization_id = i_org_id
2138 HAVING
2139 SUM(nvl(temp_relieved_value,0)) <> 0;
2140
2141
2142
2143 /******************************************************
2144 * Insert rows into mtl_cst_txn_cost_details *
2145 ******************************************************/
2146
2147 stmt_num := 290;
2148
2149 INSERT INTO mtl_cst_txn_cost_details
2150 (
2151 TRANSACTION_ID,
2152 ORGANIZATION_ID,
2153 INVENTORY_ITEM_ID,
2154 COST_ELEMENT_ID,
2155 LEVEL_TYPE,
2156 TRANSACTION_COST,
2157 NEW_AVERAGE_COST,
2158 PERCENTAGE_CHANGE,
2159 VALUE_CHANGE,
2160 LAST_UPDATE_DATE,
2161 LAST_UPDATED_BY,
2162 CREATION_DATE,
2163 CREATED_BY,
2164 LAST_UPDATE_LOGIN,
2165 REQUEST_ID,
2166 PROGRAM_APPLICATION_ID,
2167 PROGRAM_ID,
2168 PROGRAM_UPDATE_DATE)
2169 SELECT
2170 i_trx_id,
2171 i_org_id,
2172 i_inv_item_id,
2173 cost_element_id,
2174 level_type,
2175 cost_element_value,
2176 NULL,
2177 NULL,
2178 NULL,
2179 SYSDATE,
2180 -1,
2181 SYSDATE,
2182 -1,
2183 -1,
2184 -1,
2185 -1,
2186 -1,
2187 SYSDATE
2188 FROM
2189 wip_scrap_values
2190 WHERE
2191 transaction_id = i_trx_id AND
2192 cost_update_id IS NULL;
2193
2194
2195 EXCEPTION
2196
2197 WHEN OTHERS THEN
2198 err_num := SQLCODE;
2199 err_msg := 'CSTPACWS' || to_char(stmt_num) || substr(SQLERRM,1,150);
2200
2201 END scrap;
2202
2203
2204
2205
2206
2207 PROCEDURE scrap_return (
2208 i_trx_id IN NUMBER,
2209 i_txn_qty IN NUMBER,
2210 i_wip_entity_id IN NUMBER,
2211 i_inv_item_id IN NUMBER,
2212 i_org_id IN NUMBER,
2213 i_op_seq_num IN NUMBER,
2214 i_cost_type_id IN NUMBER,
2215 err_num OUT NOCOPY NUMBER,
2216 err_code OUT NOCOPY VARCHAR2,
2217 err_msg OUT NOCOPY VARCHAR2)
2218
2219 is
2220
2221 stmt_num NUMBER;
2222 i_lot_size NUMBER;
2223 l_system_option_id NUMBER;
2224 l_comp_cost_source NUMBER;
2225 l_include_comp_yield NUMBER;
2226
2227 BEGIN
2228
2229 /***************************************************
2230 * Update temp_relieved_value to zero in all tables *
2231 ***************************************************/
2232
2233 stmt_num := 10;
2234
2235 UPDATE WIP_REQ_OPERATION_COST_DETAILS
2236 SET temp_relieved_value = 0
2237 where
2238 WIP_ENTITY_ID = i_wip_entity_id;
2239
2240 stmt_num := 20;
2241
2242 UPDATE WIP_OPERATION_RESOURCES
2243 SET temp_relieved_value = 0
2244 where
2245 WIP_ENTITY_ID = i_wip_entity_id;
2246
2247 stmt_num := 30;
2248
2249 UPDATE WIP_OPERATION_OVERHEADS
2250 SET temp_relieved_value = 0
2251 where
2252 WIP_ENTITY_ID = i_wip_entity_id;
2253
2254 stmt_num := 40;
2255
2256 SELECT start_quantity
2257 into i_lot_size
2258 from
2259 WIP_DISCRETE_JOBS
2260 where
2261 WIP_ENTITY_ID = i_wip_entity_id and
2262 ORGANIZATION_ID = i_org_id;
2263
2264 stmt_num := 50;
2265
2266 select wac.completion_cost_source,nvl(system_option_id,-1)
2267 into l_comp_cost_source,l_system_option_id
2268 from
2269 wip_accounting_classes wac,
2270 wip_discrete_jobs wdj
2271 where
2272 wdj.wip_entity_id = i_wip_entity_id and
2273 wdj.organization_id = i_org_id and
2274 wdj.class_code = wac.class_code and
2275 wdj.organization_id = wac.organization_id;
2276
2277 stmt_num := 60;
2278 /* Get the value of Include Component yield flag, which will determine
2279 whether to include or not component yield factor in quantity per assembly*/
2280 SELECT nvl(include_component_yield, 1)
2281 INTO l_include_comp_yield
2282 FROM wip_parameters
2283 WHERE organization_id = i_org_id;
2284
2285
2286 /******************************************************
2287 * Compute PL Costs for WIP Scrap Return ... *
2288 ******************************************************/
2289 stmt_num := 100;
2290
2291 UPDATE WIP_REQ_OPERATION_COST_DETAILS W1
2292 SET
2293 (TEMP_RELIEVED_VALUE,
2294 RELIEVED_MATL_SCRAP_VALUE) =
2295 (SELECT
2296 decode(SIGN(nvl(wro.RELIEVED_MATL_SCRAP_QUANTITY,0)-
2297 /* LBM Project Changes */
2298 abs(i_txn_qty)*(decode(wro.basis_type, 2,
2299 wro.quantity_per_assembly/i_lot_size,
2300 wro.quantity_per_assembly)/
2301 decode(l_include_comp_yield,
2302 1, nvl(wro.component_yield_factor,1),
2303 1))),
2304 SIGN(wro.quantity_per_assembly),
2305 /* LBM Project Changes */
2306 i_txn_qty*(decode(wro.basis_type, 2,
2307 wro.quantity_per_assembly/i_lot_size,
2308 wro.quantity_per_assembly)/
2309 decode(l_include_comp_yield,
2310 1, nvl(wro.component_yield_factor,1),
2311 1))*
2312 (nvl(relieved_matl_scrap_value,0)/
2313 decode(RELIEVED_MATL_SCRAP_QUANTITY,
2314 0,1,
2315 NULL,1,RELIEVED_MATL_SCRAP_QUANTITY)),
2316 0,
2317 -1*nvl(relieved_matl_scrap_value,0),
2318 -1*SIGN(wro.quantity_per_assembly),
2319 -1*nvl(relieved_matl_scrap_value,0)),
2320 nvl(w1.relieved_matl_scrap_value,0) +
2321 decode(SIGN(nvl(wro.RELIEVED_MATL_SCRAP_QUANTITY,0)-
2322 /* LBM Project Changes */
2323 abs(i_txn_qty)*(decode(wro.basis_type, 2,
2324 wro.quantity_per_assembly/i_lot_size,
2325 wro.quantity_per_assembly)/
2326 decode(l_include_comp_yield,
2327 1, nvl(wro.component_yield_factor,1),
2328 1))),
2329 SIGN(wro.quantity_per_assembly),
2330 /* LBM Project Changes */
2331 i_txn_qty*(decode(wro.basis_type, 2,
2332 wro.quantity_per_assembly/i_lot_size,
2333 wro.quantity_per_assembly)/
2334 decode(l_include_comp_yield,
2335 1, nvl(wro.component_yield_factor,1),
2336 1))*
2337 (nvl(relieved_matl_scrap_value,0)/
2338 decode(RELIEVED_MATL_SCRAP_QUANTITY,
2339 0,1,
2340 NULL,1,RELIEVED_MATL_SCRAP_QUANTITY)),
2341 0,
2342 -1*nvl(relieved_matl_scrap_value,0),
2343 -1*SIGN(wro.quantity_per_assembly),
2344 -1*nvl(relieved_matl_scrap_value,0))
2345 FROM
2346 WIP_REQ_OPERATION_COST_DETAILS W2,
2347 WIP_REQUIREMENT_OPERATIONS WRO
2348 WHERE
2349 W1.WIP_ENTITY_ID = W2.WIP_ENTITY_ID AND
2350 W1.ORGANIZATION_ID = W2.ORGANIZATION_ID AND
2351 W1.OPERATION_SEQ_NUM = W2.OPERATION_SEQ_NUM AND
2352 W1.INVENTORY_ITEM_ID = W2.INVENTORY_ITEM_ID AND
2353 W1.COST_ELEMENT_ID = W2.COST_ELEMENT_ID AND
2354 W2.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID AND
2355 W2.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND
2356 W2.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM AND
2357 W2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID)
2358 WHERE
2359 (w1.WIP_ENTITY_ID, w1.ORGANIZATION_ID,
2360 w1.INVENTORY_ITEM_ID, w1.OPERATION_SEQ_NUM) IN
2361 (SELECT
2362 WIP_ENTITY_ID, ORGANIZATION_ID,
2363 INVENTORY_ITEM_ID,OPERATION_SEQ_NUM
2364 from
2365 WIP_REQUIREMENT_OPERATIONS wro2
2366 where
2367 wro2.WIP_ENTITY_ID = i_wip_entity_id AND
2368 wro2.ORGANIZATION_ID = i_org_id AND
2369 wro2.QUANTITY_PER_ASSEMBLY <> 0 AND
2370 wro2.OPERATION_SEQ_NUM <= i_op_seq_num AND
2371 wro2.WIP_SUPPLY_TYPE not in (4,5,6));
2372
2373
2374
2375
2376 stmt_num := 120;
2377
2378 UPDATE WIP_REQUIREMENT_OPERATIONS w
2379 SET RELIEVED_MATL_SCRAP_QUANTITY =
2380 (SELECT
2381 nvl(w.RELIEVED_MATL_SCRAP_QUANTITY,0) +
2382 decode(w2.RELIEVED_MATL_SCRAP_QUANTITY,
2383 0,0,
2384 /* LBM Project Changes */
2385 i_txn_qty*(decode(w2.basis_type, 2,
2386 w2.quantity_per_assembly/i_lot_size,
2387 w2.quantity_per_assembly)/
2388 decode(l_include_comp_yield,
2389 1, nvl(w2.component_yield_factor,1),
2390 1)))
2391 FROM
2392 WIP_REQUIREMENT_OPERATIONS w2
2393 where
2394 w.WIP_ENTITY_ID = w2.WIP_ENTITY_ID AND
2395 w.INVENTORY_ITEM_ID = w2.INVENTORY_ITEM_ID AND
2396 w.OPERATION_SEQ_NUM = w2.OPERATION_SEQ_NUM AND
2397 w.ORGANIZATION_ID = w2.ORGANIZATION_ID)
2398 WHERE
2399 w.WIP_ENTITY_ID = i_wip_entity_id AND
2400 w.ORGANIZATION_ID = i_org_id AND
2401 w.WIP_SUPPLY_TYPE not in (4,5,6) AND
2402 w.QUANTITY_PER_ASSEMBLY <> 0 AND
2403 w.OPERATION_SEQ_NUM <= i_op_seq_num;
2404
2405 stmt_num := 140;
2406
2407 INSERT INTO WIP_SCRAP_VALUES
2408 (
2409 transaction_id,
2410 level_type,
2411 cost_element_id,
2412 cost_update_id,
2413 last_update_date,
2414 last_updated_by,
2415 created_by,
2416 creation_date,
2417 last_update_login,
2418 cost_element_value,
2419 request_id,
2420 program_application_id,
2421 program_id,
2422 program_update_date
2423 )
2424 SELECT
2425 i_trx_id,
2426 2,
2427 wrocd.cost_element_id,
2428 NULL,
2429 SYSDATE,
2430 -1,
2431 -1,
2432 SYSDATE,
2433 -1,
2434 sum(nvl(temp_relieved_value,0))/i_txn_qty,
2435 -1,
2436 -1,
2437 -1,
2438 SYSDATE
2439 FROM
2440 WIP_REQ_OPERATION_COST_DETAILS wrocd
2441 where
2442 wrocd.WIP_ENTITY_ID = i_wip_entity_id AND
2443 wrocd.ORGANIZATION_ID = i_org_id
2444 GROUP BY wrocd.COST_ELEMENT_ID
2445 HAVING sum(nvl(temp_relieved_value,0)) <> 0;
2446
2447
2448
2449 If (l_system_option_id = 1) THEN
2450
2451 stmt_num := 150;
2452
2453 UPDATE WIP_OPERATION_RESOURCES W1
2454 SET
2455 (RELIEVED_RES_SCRAP_UNITS,
2456 TEMP_RELIEVED_VALUE,
2457 RELIEVED_RES_scrap_VALUE) =
2458 (SELECT
2459 nvl(W1.RELIEVED_RES_scrap_UNITS,0)+
2460 nvl(W2.RELIEVED_RES_scrap_UNITS,0)*
2461 decode(abs(i_txn_qty),
2462 PRIOR_SCRAP_QUANTITY,-1,
2463 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
2464 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
2465 decode(abs(i_txn_qty),
2466 PRIOR_SCRAP_QUANTITY,-1,
2467 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
2468 nvl(W1.RELIEVED_RES_scrap_VALUE,0)+
2469 nvl(W2.RELIEVED_RES_scrap_VALUE,0)*
2470 decode(abs(i_txn_qty),
2471 PRIOR_SCRAP_QUANTITY,-1,
2472 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY))
2473 FROM
2474 WIP_OPERATION_RESOURCES W2,
2475 cst_comp_snapshot COCD
2476 WHERE
2477 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
2478 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
2479 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
2480 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
2481 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
2482 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
2483 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
2484 COCD.NEW_OPERATION_FLAG = 2 AND */
2485 COCD.TRANSACTION_ID = I_TRX_ID)
2486 WHERE
2487 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2488 W1.ORGANIZATION_ID = I_ORG_ID AND
2489 w1.usage_rate_or_amount <> 0 AND
2490 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
2491
2492
2493 stmt_num := 155;
2494
2495 UPDATE wip_operation_overheads W1
2496 SET
2497 (RELIEVED_ovhd_SCRAP_UNITS,
2498 TEMP_RELIEVED_VALUE,
2499 RELIEVED_ovhd_scrap_value) =
2500 (SELECT
2501 nvl(W1.RELIEVED_ovhd_SCRAP_UNITS,0)+
2502 nvl(W2.RELIEVED_ovhd_SCRAP_UNITS,0)*
2503 decode(abs(i_txn_qty),
2504 PRIOR_SCRAP_QUANTITY,-1,
2505 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
2506 nvl(W2.RELIEVED_ovhd_scrap_value,0)*
2507 decode(abs(i_txn_qty),
2508 PRIOR_SCRAP_QUANTITY,-1,
2509 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY)),
2510 nvl(W1.RELIEVED_ovhd_scrap_value,0)+
2511 nvl(W2.RELIEVED_ovhd_scrap_value,0)*
2512 decode(abs(i_txn_qty),
2513 PRIOR_SCRAP_QUANTITY,-1,
2514 i_txn_qty/decode(PRIOR_SCRAP_QUANTITY,NULL,1,0,1,PRIOR_SCRAP_QUANTITY))
2515 FROM
2516 wip_operation_overheads W2,
2517 cst_comp_snapshot COCD
2518 WHERE
2519 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
2520 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
2521 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
2522 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
2523 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
2524 /*bug#3469342. */
2525 w1.basis_type = w2.basis_type AND
2526 W2.WIP_ENTITY_ID = COCD.WIP_ENTITY_ID AND
2527 W2.OPERATION_SEQ_NUM = COCD.OPERATION_SEQ_NUM AND
2528 /* Bug 1180589: This is an incorrect condition. New operations should be picked up too
2529 COCD.NEW_OPERATION_FLAG = 2 AND */
2530 COCD.TRANSACTION_ID = I_TRX_ID)
2531 WHERE
2532 W1.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
2533 W1.ORGANIZATION_ID = I_ORG_ID AND
2534 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
2535
2536
2537 ELSE
2538
2539 stmt_num := 160;
2540
2541 UPDATE WIP_OPERATION_RESOURCES W1
2542 SET
2543 (RELIEVED_RES_SCRAP_UNITS,
2544 TEMP_RELIEVED_VALUE,
2545 RELIEVED_RES_SCRAP_VALUE) =
2546 (SELECT
2547 nvl(w1.RELIEVED_RES_SCRAP_UNITS,0) +
2548 decode(w2.RELIEVED_RES_SCRAP_UNITS,
2549 0,0,
2550 i_txn_qty*decode(w2.basis_type,
2551 1,usage_rate_or_amount,
2552 2,usage_rate_or_amount/i_lot_size,
2553 usage_rate_or_amount)),
2554 decode(SIGN(nvl(relieved_res_scrap_units,0)-
2555 abs(i_txn_qty)*
2556 decode(w2.basis_type,
2557 1,usage_rate_or_amount,
2558 2,usage_rate_or_amount/i_lot_size,
2559 usage_rate_or_amount)),
2560 SIGN(usage_rate_or_amount),
2561 i_txn_qty*decode(basis_type,
2562 1,usage_rate_or_amount,
2563 2,usage_rate_or_amount/i_lot_size,
2564 usage_rate_or_amount)*
2565 (nvl(relieved_res_scrap_value,0)/
2566 decode(relieved_res_scrap_units,
2567 0,1,
2568 NULL,1,
2569 relieved_res_scrap_units)),
2570 0,
2571 -1*nvl(relieved_res_scrap_value,0),
2572 -1*SIGN(usage_rate_or_amount),
2573 0),
2574 nvl(w1.relieved_res_scrap_value,0) +
2575 decode(SIGN(nvl(relieved_res_scrap_units,0)-
2576 abs(i_txn_qty)*
2577 decode(w2.basis_type,
2578 1,usage_rate_or_amount,
2579 2,usage_rate_or_amount/i_lot_size,
2580 usage_rate_or_amount)),
2581 SIGN(usage_rate_or_amount),
2582 i_txn_qty*decode(basis_type,
2583 1,usage_rate_or_amount,
2584 2,usage_rate_or_amount/i_lot_size,
2585 usage_rate_or_amount)*
2586 (nvl(relieved_res_scrap_value,0)/
2587 decode(relieved_res_scrap_units,
2588 0,1,
2589 NULL,1,
2590 relieved_res_scrap_units)),
2591 0,
2592 -1*nvl(relieved_res_scrap_value,0),
2593 -1*SIGN(usage_rate_or_amount),
2594 0)
2595 FROM
2596 WIP_OPERATION_RESOURCES w2
2597 where
2598 w2.WIP_ENTITY_ID = w1.WIP_ENTITY_ID AND
2599 w2.OPERATION_SEQ_NUM = w1.OPERATION_SEQ_NUM AND
2600 w2.RESOURCE_SEQ_NUM = w1.RESOURCE_SEQ_NUM AND
2601 w2.ORGANIZATION_ID = w2.ORGANIZATION_ID)
2602 where
2603 w1.WIP_ENTITY_ID = i_wip_entity_id AND
2604 w1.ORGANIZATION_ID = i_org_id AND
2605 w1.usage_rate_or_amount <> 0 AND
2606 w1.OPERATION_SEQ_NUM <= i_op_seq_num;
2607
2608
2609
2610 /********************************************************
2611 * Compute TL Move based Ovhd costs for Scrap Return ... *
2612 *********************************************************/
2613
2614 stmt_num := 200;
2615
2616 UPDATE WIP_OPERATION_OVERHEADS W1
2617 SET
2618 (RELIEVED_OVHD_SCRAP_UNITS,
2619 TEMP_RELIEVED_VALUE,
2620 RELIEVED_OVHD_SCRAP_VALUE) =
2621 (SELECT
2622 nvl(w1.relieved_ovhd_scrap_units,0) +
2623 decode(w2.relieved_ovhd_scrap_units,
2624 0,0,
2625 decode(basis_type,
2626 1,i_txn_qty,
2627 2,i_txn_qty/i_lot_size)),
2628 decode(SIGN(nvl(relieved_ovhd_scrap_units,0) -
2629 abs(i_txn_qty)*
2630 decode(basis_type,
2631 1,1,
2632 2,1/i_lot_size)),
2633 1,
2634 decode(basis_type,
2635 1,i_txn_qty,
2636 2,i_txn_qty/i_lot_size)*
2637 (nvl(relieved_ovhd_scrap_value,0)/
2638 decode(relieved_ovhd_scrap_units,
2639 0,1,
2640 NULL,1,
2641 relieved_ovhd_scrap_units)),
2642 0,
2643 -1*nvl(relieved_ovhd_scrap_value,0),
2644 -1,
2645 0),
2646 nvl(w1.relieved_ovhd_scrap_value,0) +
2647 decode(SIGN(nvl(relieved_ovhd_scrap_units,0) -
2648 abs(i_txn_qty)*
2649 decode(basis_type,
2650 1,1,
2651 2,1/i_lot_size)),
2652 1,
2653 decode(basis_type,
2654 1,i_txn_qty,
2655 2,i_txn_qty/i_lot_size)*
2656 (nvl(relieved_ovhd_scrap_value,0)/
2657 decode(relieved_ovhd_scrap_units,
2658 0,1,
2659 NULL,1,
2660 relieved_ovhd_scrap_units)),
2661 0,
2662 -1*nvl(relieved_ovhd_scrap_value,0),
2663 -1,
2664 0)
2665 FROM
2666 WIP_OPERATION_OVERHEADS W2
2667 WHERE
2668 W1.WIP_ENTITY_ID = W2.WIP_ENTITY_ID AND
2669 W1.ORGANIZATION_ID = W2.ORGANIZATION_ID AND
2670 W1.OPERATION_SEQ_NUM = W2.OPERATION_SEQ_NUM AND
2671 W1.OVERHEAD_ID = W2.OVERHEAD_ID AND
2672 W1.BASIS_TYPE = W2.BASIS_TYPE)
2673 where
2674 W1.WIP_ENTITY_ID = i_wip_entity_id and
2675 W1.ORGANIZATION_ID = i_org_id and
2676 W1.OPERATION_SEQ_NUM <= i_op_seq_num and
2677 W1.BASIS_TYPE IN (1,2);
2678
2679
2680
2681 /********************************************************
2682 * Compute TL Res based Ovhd costs for Scrap Return ... *
2683 *********************************************************/
2684
2685 stmt_num := 220;
2686
2687
2688 UPDATE WIP_OPERATION_OVERHEADS W1
2689 SET
2690 (RELIEVED_OVHD_SCRAP_UNITS,
2691 TEMP_RELIEVED_VALUE,
2692 RELIEVED_OVHD_SCRAP_VALUE) =
2693 (SELECT
2694 nvl(w1.relieved_ovhd_scrap_units,0) +
2695 decode(w2.relieved_ovhd_scrap_units,
2696 0,0,
2697 decode(w2.basis_type,
2698 3,i_txn_qty*decode(wor.basis_type,
2699 1,usage_rate_or_amount,
2700 2,usage_rate_or_amount/i_lot_size),
2701 4,wor.temp_relieved_value)),
2702 decode(SIGN(nvl(relieved_ovhd_scrap_units,0) -
2703 abs(decode(w2.basis_type,
2704 3,i_txn_qty*decode(wor.basis_type,
2705 1,usage_rate_or_amount,
2706 2,usage_rate_or_amount/i_lot_size),
2707 4,wor.temp_relieved_value))),
2708 SIGN(wor.usage_rate_or_amount),
2709 decode(w2.basis_type,
2710 3,i_txn_qty*decode(wor.basis_type,
2711 1,usage_rate_or_amount,
2712 2,usage_rate_or_amount/i_lot_size),
2713 4,wor.temp_relieved_value)*
2714 (nvl(relieved_ovhd_scrap_value,0)/
2715 decode(relieved_ovhd_scrap_units,
2716 0,1,
2717 NULL,1,
2718 relieved_ovhd_scrap_units)),
2719 0,
2720 -1*nvl(relieved_ovhd_scrap_value,0),
2721 -1*SIGN(wor.usage_rate_or_amount),
2722 0),
2723 nvl(relieved_ovhd_scrap_value,0) +
2724 decode(SIGN(nvl(relieved_ovhd_scrap_units,0) -
2725 abs(decode(w2.basis_type,
2726 3,i_txn_qty*decode(wor.basis_type,
2727 1,usage_rate_or_amount,
2728 2,usage_rate_or_amount/i_lot_size),
2729 4,wor.temp_relieved_value))),
2730 SIGN(wor.usage_rate_or_amount),
2731 decode(w2.basis_type,
2732 3,i_txn_qty*decode(wor.basis_type,
2733 1,usage_rate_or_amount,
2734 2,usage_rate_or_amount/i_lot_size),
2735 4,wor.temp_relieved_value)*
2736 (nvl(relieved_ovhd_scrap_value,0)/
2737 decode(relieved_ovhd_scrap_units,
2738 0,1,
2739 NULL,1,
2740 relieved_ovhd_scrap_units)),
2741 0,
2742 -1*nvl(relieved_ovhd_scrap_value,0),
2743 -1*SIGN(wor.usage_rate_or_amount),
2744 0)
2745 FROM
2746 WIP_OPERATION_OVERHEADS W2,
2747 WIP_OPERATION_RESOURCES WOR
2748 where
2749 W2.WIP_ENTITY_ID = W1.WIP_ENTITY_ID AND
2750 W2.ORGANIZATION_ID = W1.ORGANIZATION_ID AND
2751 W2.OPERATION_SEQ_NUM = W1.OPERATION_SEQ_NUM AND
2752 W2.RESOURCE_SEQ_NUM = W1.RESOURCE_SEQ_NUM AND
2753 W2.OVERHEAD_ID = W1.OVERHEAD_ID AND
2754 W2.BASIS_TYPE = W1.BASIS_TYPE AND
2755 W2.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
2756 W2.ORGANIZATION_ID = WOR.ORGANIZATION_ID AND
2757 W2.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
2758 W2.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM)
2759 where
2760 W1.WIP_ENTITY_ID = i_wip_entity_id AND
2761 W1.ORGANIZATION_ID = i_org_id AND
2762 W1.OPERATION_SEQ_NUM <= i_op_seq_num AND
2763 W1.BASIS_TYPE IN (3,4);
2764
2765
2766 END IF; -- The If for system option ends here.
2767
2768 stmt_num := 245;
2769
2770 INSERT INTO WIP_SCRAP_VALUES
2771 (
2772 transaction_id,
2773 level_type,
2774 cost_element_id,
2775 cost_update_id,
2776 last_update_date,
2777 last_updated_by,
2778 created_by,
2779 creation_date,
2780 last_update_login,
2781 cost_element_value,
2782 request_id,
2783 program_application_id,
2784 program_id,
2785 program_update_date
2786 )
2787 SELECT
2788 i_trx_id,
2789 1,
2790 br.cost_element_id,
2791 NULL,
2792 SYSDATE,
2793 -1,
2794 -1,
2795 SYSDATE,
2796 -1,
2797 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2798 -1,
2799 -1,
2800 -1,
2801 SYSDATE
2802 FROM
2803 WIP_OPERATION_RESOURCES wor,
2804 BOM_RESOURCES br
2805 WHERE
2806 br.RESOURCE_ID = WOR.RESOURCE_ID AND
2807 br.ORGANIZATION_ID = WOR.ORGANIZATION_ID AND
2808 WIP_ENTITY_ID = i_wip_entity_id AND
2809 wor.ORGANIZATION_ID = i_org_id
2810 group by br.cost_element_id
2811 HAVING
2812 SUM(nvl(temp_relieved_value,0)) <> 0;
2813
2814
2815 stmt_num := 250;
2816
2817 INSERT INTO WIP_SCRAP_VALUES
2818 (
2819 transaction_id,
2820 level_type,
2821 cost_element_id,
2822 cost_update_id,
2823 last_update_date,
2824 last_updated_by,
2825 created_by,
2826 creation_date,
2827 last_update_login,
2828 cost_element_value,
2829 request_id,
2830 program_application_id,
2831 program_id,
2832 program_update_date
2833 )
2834 SELECT
2835 i_trx_id,
2836 1,
2837 5,
2838 NULL,
2839 SYSDATE,
2840 -1,
2841 -1,
2842 SYSDATE,
2843 -1,
2844 SUM(nvl(temp_relieved_value,0))/i_txn_qty,
2845 -1,
2846 -1,
2847 -1,
2848 SYSDATE
2849 FROM
2850 WIP_OPERATION_OVERHEADS
2851 WHERE
2852 WIP_ENTITY_ID = i_wip_entity_id AND
2853 ORGANIZATION_ID = i_org_id
2854 HAVING
2855 SUM(nvl(temp_relieved_value,0)) <> 0;
2856
2857
2858 stmt_num := 270;
2859
2860 INSERT INTO mtl_cst_txn_cost_details
2861 (
2862 TRANSACTION_ID,
2863 ORGANIZATION_ID,
2864 INVENTORY_ITEM_ID,
2865 COST_ELEMENT_ID,
2866 LEVEL_TYPE,
2867 TRANSACTION_COST,
2868 NEW_AVERAGE_COST,
2869 PERCENTAGE_CHANGE,
2870 VALUE_CHANGE,
2871 LAST_UPDATE_DATE,
2872 LAST_UPDATED_BY,
2873 CREATION_DATE,
2874 CREATED_BY,
2875 LAST_UPDATE_LOGIN,
2876 REQUEST_ID,
2877 PROGRAM_APPLICATION_ID,
2878 PROGRAM_ID,
2879 PROGRAM_UPDATE_DATE)
2880 SELECT
2881 i_trx_id,
2882 i_org_id,
2883 i_inv_item_id,
2884 cost_element_id,
2885 level_type,
2886 cost_element_value,
2887 NULL,
2888 NULL,
2889 NULL,
2890 SYSDATE,
2891 -1,
2892 SYSDATE,
2893 -1,
2894 -1,
2895 -1,
2896 -1,
2897 -1,
2898 SYSDATE
2899 FROM
2900 WIP_SCRAP_VALUES
2901 WHERE
2902 TRANSACTION_ID = i_trx_id AND
2903 COST_UPDATE_ID IS NULL;
2904
2905
2906 EXCEPTION
2907
2908 WHEN OTHERS THEN
2909 err_num := SQLCODE;
2910 err_msg := 'CSTPACWS' || to_char(stmt_num) || substr(SQLERRM,1,150);
2911
2912
2913 END scrap_return;
2914
2915
2916 END CSTPACWS;