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