[Home] [Help]
PACKAGE BODY: APPS.CSTPPWAS
Source
1 PACKAGE BODY CSTPPWAS AS
2 /* $Header: CSTPWASB.pls 120.33 2007/11/13 19:09:25 vjavli ship $ */
3
4 G_MAX_RECORDS CONSTANT NUMBER := 1000;
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPWAS';
6 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7
8 /*---------------------------------------------------------------------------*
9 | PRIVATE PROCEDURE |
10 | insert_wip_costs |
11 *----------------------------------------------------------------------------*/
12 PROCEDURE insert_wip_costs (
13 p_pac_period_id IN NUMBER,
14 p_prior_period_id IN NUMBER,
15 p_cost_group_id IN NUMBER,
16 p_cost_type_id IN NUMBER,
17 p_item_id IN NUMBER,
18 p_entity_id IN NUMBER,
19 p_line_id IN NUMBER,
20 p_txn_id IN NUMBER,
21 p_net_qty IN NUMBER,
22 p_completed_assembly_qty IN NUMBER,
23 p_user_id IN NUMBER,
24 p_final_completion_flag IN NUMBER,
25 p_start_date IN DATE,
26 p_end_date IN DATE,
27 p_login_id IN NUMBER,
28 p_request_id IN NUMBER,
29 p_prog_id IN NUMBER DEFAULT -1,
30 p_prog_app_id IN NUMBER DEFAULT -1,
31 x_err_num OUT NOCOPY NUMBER,
32 x_err_code OUT NOCOPY VARCHAR2,
33 x_err_msg OUT NOCOPY VARCHAR2)
34 IS
35 l_stmt_num NUMBER;
36 l_err_num NUMBER;
37 l_err_code VARCHAR2(240);
38 l_err_msg VARCHAR2(240);
39
40 l_transaction_action_id NUMBER; --Aded for R12 PAC enhancement
41 l_transaction_source_type_id NUMBER; --Aded for R12 PAC enhancement
42 l_net_qty NUMBER; --Aded for R12 PAC enhancement
43 l_primary_quantity NUMBER; --Aded for R12 PAC enhancement
44 l_details NUMBER; --Aded for R12 PAC enhancement
45 l_job NUMBER;
46
47 l_api_name CONSTANT VARCHAR2(30) := 'insert_wip_costs';
48 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
49 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
50
51 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
52 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
53 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
54 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
55 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
56 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
57
58 BEGIN
59
60 IF (l_pLog) THEN
61
62 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
63 l_module || '.begin',
64 l_api_name || ' <<< Parameters:
65 p_pac_period_id = ' || p_pac_period_id || '
66 p_prior_period_id = ' || p_prior_period_id || '
67 p_item_id = ' || p_item_id || '
68 p_entity_id = ' || p_entity_id || '
69 p_line_id = ' || p_line_id || '
70 p_net_qty = ' || p_net_qty || '
71 p_completed_assembly_qty = ' || p_completed_assembly_qty ||'
72 p_final_completion_flag = ' || p_final_completion_flag ||'
73 p_txn_id = ' || p_txn_id );
74 END IF;
75
76 ----------------------------------------------------------------------
77 -- Initialize Variables
78 ----------------------------------------------------------------------
79 l_stmt_num := 0;
80 l_err_num := 0;
81 l_err_code := '';
82 l_err_msg := '';
83 l_details := 0;
84 l_job := 0;
85
86 --------------------------------------------------------------------------
87 -- Get the transaction_action_id, transaction_source_id and primary_qty
88 --------------------------------------------------------------------------
89 l_stmt_num := 10;
90 SELECT mmt.transaction_action_id,
91 mmt.transaction_source_type_id ,
92 mmt.primary_quantity
93 INTO l_transaction_action_id,
94 l_transaction_source_type_id,
95 l_primary_quantity
96 FROM mtl_material_transactions mmt
97 WHERE mmt.transaction_id = p_txn_id;
98
99 ------------------------------------------------------------
100 -- In case Asembly Returns cancels Assembly completions then
101 -- Get the completed Assembly Units in this period
102 ------------------------------------------------------------
103 l_stmt_num := 20;
104 IF (p_net_qty = 0 AND p_final_completion_flag = 1 AND l_transaction_action_id = 31 AND l_transaction_source_type_id =5) THEN
105 l_net_qty := p_completed_assembly_qty;
106 ELSE
107 l_net_qty := p_net_qty;
108 END IF;
109
110 -------------------------------------------------------------------------------------------
111 -- Assembly return transaction needs to relieved at prior period Avg of Relieved cost
112 -- In case of p_net_qty = 0 and final_completion transaction exists
113 -------------------------------------------------------------------------------------------
114 IF ( p_net_qty = 0 AND p_final_completion_flag = 1 AND
115 l_transaction_action_id = 32 AND l_transaction_source_type_id = 5) THEN-- Assembly Return
116 -----------------------------------------------------
117 -- PAC prior period exists
118 -----------------------------------------------------
119 l_stmt_num := 30;
120
121 SELECT COUNT(wppb.PAC_PERIOD_ID)
122 INTO l_job
123 FROM WIP_PAC_PERIOD_BALANCES wppb
124 WHERE wppb.WIP_ENTITY_ID = p_entity_id
125 AND wppb.PAC_PERIOD_ID = p_prior_period_id
126 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
127
128 -- Statement level log message for FND logging
129 IF (l_sLog) THEN
130 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
131 l_module || '.'||l_stmt_num,
132 'l_transaction_action_id :' || l_transaction_action_id || ','||
133 'l_transaction_source_type_id :' || l_transaction_source_type_id || ','||
134 'l_net_qty :' || l_net_qty || ','||
135 'l_job :' || l_job);
136 END IF;
137
138 l_stmt_num := 35;
139 IF ( p_prior_period_id <> -1 AND l_job <> 0 ) THEN
140
141 INSERT ALL
142 WHEN pp_pl_material_out <> 0 THEN
143 -- Previous Level and Material cost element
144 INTO mtl_pac_txn_cost_details
145 (pac_period_id,
146 cost_group_id,
147 cost_type_id ,
148 inventory_item_id,
149 transaction_id,
150 cost_element_id,
151 level_type,
152 transaction_cost,
153 wip_variance, -- New Column
154 last_update_date,
155 last_updated_by,
156 creation_date,
157 created_by,
158 request_id,
159 program_application_id,
160 program_id,
161 program_update_date,
162 last_update_login)
163 VALUES(p_pac_period,
164 p_cost_group,
165 p_cost_type,
166 p_item,
167 p_txn,
168 1,
169 2,
170 pp_pl_material_out,
171 0, -- New column value
172 SYSDATE,
173 p_user,
174 SYSDATE,
175 p_user,
176 p_request,
177 p_prog_app,
178 p_prog,
179 SYSDATE,
180 p_login)
181 -- Previous Level and Material Overhead cost element
182 WHEN pp_pl_material_overhead_out <> 0 THEN
183 INTO mtl_pac_txn_cost_details
184 (pac_period_id,
185 cost_group_id,
186 cost_type_id ,
187 inventory_item_id,
188 transaction_id,
189 cost_element_id,
190 level_type,
191 transaction_cost,
192 wip_variance, -- New Column
193 last_update_date,
194 last_updated_by,
195 creation_date,
196 created_by,
197 request_id,
198 program_application_id,
199 program_id,
200 program_update_date,
201 last_update_login)
202 VALUES(p_pac_period,
203 p_cost_group,
204 p_cost_type,
205 p_item,
206 p_txn,
207 2,
208 2,
209 pp_pl_material_overhead_out,
210 0, -- New column value
211 SYSDATE,
212 p_user,
213 SYSDATE,
214 p_user,
215 p_request,
216 p_prog_app,
217 p_prog,
218 SYSDATE,
219 p_login)
220 -- Previous Level and Resource cost element
221 WHEN pp_pl_resource_out <> 0 THEN
222 INTO mtl_pac_txn_cost_details
223 (pac_period_id,
224 cost_group_id,
225 cost_type_id ,
226 inventory_item_id,
227 transaction_id,
228 cost_element_id,
229 level_type,
230 transaction_cost,
231 wip_variance, -- New Column
232 last_update_date,
233 last_updated_by,
234 creation_date,
235 created_by,
236 request_id,
237 program_application_id,
238 program_id,
239 program_update_date,
240 last_update_login)
241 VALUES(p_pac_period,
242 p_cost_group,
243 p_cost_type,
244 p_item,
245 p_txn,
246 3,
247 2,
248 pp_pl_resource_out,
249 0, -- New column value
250 SYSDATE,
251 p_user,
252 SYSDATE,
253 p_user,
254 p_request,
255 p_prog_app,
256 p_prog,
257 SYSDATE,
258 p_login)
259 -- Previous Level and Outsideprocessing cost element
260 WHEN pp_pl_outside_processing_out <> 0 THEN
261 INTO mtl_pac_txn_cost_details
262 (pac_period_id,
263 cost_group_id,
264 cost_type_id ,
265 inventory_item_id,
266 transaction_id,
267 cost_element_id,
268 level_type,
269 transaction_cost,
270 wip_variance, -- New Column
271 last_update_date,
272 last_updated_by,
273 creation_date,
274 created_by,
275 request_id,
276 program_application_id,
277 program_id,
278 program_update_date,
279 last_update_login)
280 VALUES(p_pac_period,
281 p_cost_group,
282 p_cost_type,
283 p_item,
284 p_txn,
285 4,
286 2,
287 pp_pl_outside_processing_out,
288 0, -- New column value
289 SYSDATE,
290 p_user,
291 SYSDATE,
292 p_user,
293 p_request,
294 p_prog_app,
295 p_prog,
296 SYSDATE,
297 p_login)
298 -- Previous Level and Overhead cost element
299 WHEN pp_pl_overhead_out <> 0 THEN
300 INTO mtl_pac_txn_cost_details
301 (pac_period_id,
302 cost_group_id,
303 cost_type_id ,
304 inventory_item_id,
305 transaction_id,
306 cost_element_id,
307 level_type,
308 transaction_cost,
309 wip_variance, -- New Column
310 last_update_date,
311 last_updated_by,
312 creation_date,
313 created_by,
314 request_id,
315 program_application_id,
316 program_id,
317 program_update_date,
318 last_update_login)
319 VALUES(p_pac_period,
320 p_cost_group,
321 p_cost_type,
322 p_item,
323 p_txn,
324 5,
325 2,
326 pp_pl_overhead_out,
327 0, -- New column value
328 SYSDATE,
329 p_user,
330 SYSDATE,
331 p_user,
332 p_request,
333 p_prog_app,
334 p_prog,
335 SYSDATE,
336 p_login)
337 -- This Level and Resource cost element
338 WHEN pp_tl_resource_out <> 0 THEN
339 INTO mtl_pac_txn_cost_details
340 (pac_period_id,
341 cost_group_id,
342 cost_type_id ,
343 inventory_item_id,
344 transaction_id,
345 cost_element_id,
346 level_type,
347 transaction_cost,
348 wip_variance, -- New Column
349 last_update_date,
350 last_updated_by,
351 creation_date,
352 created_by,
353 request_id,
354 program_application_id,
355 program_id,
356 program_update_date,
357 last_update_login)
358 VALUES(p_pac_period,
359 p_cost_group,
360 p_cost_type,
361 p_item,
362 p_txn,
363 3,
364 1,
365 pp_tl_resource_out,
366 0, -- New column value
367 SYSDATE,
368 p_user,
369 SYSDATE,
370 p_user,
371 p_request,
372 p_prog_app,
373 p_prog,
374 SYSDATE,
375 p_login)
376 -- This Level and Overhead cost element
377 WHEN pp_tl_overhead_out <> 0 THEN
378 INTO mtl_pac_txn_cost_details
379 (pac_period_id,
380 cost_group_id,
381 cost_type_id ,
382 inventory_item_id,
383 transaction_id,
384 cost_element_id,
385 level_type,
386 transaction_cost,
387 wip_variance, -- New Column
388 last_update_date,
389 last_updated_by,
390 creation_date,
391 created_by,
392 request_id,
393 program_application_id,
394 program_id,
395 program_update_date,
396 last_update_login)
397 VALUES(p_pac_period,
398 p_cost_group,
399 p_cost_type,
400 p_item,
401 p_txn,
402 5,
403 1,
404 pp_tl_overhead_out,
405 0, -- New column value
406 SYSDATE,
407 p_user,
408 SYSDATE,
409 p_user,
410 p_request,
411 p_prog_app,
412 p_prog,
413 SYSDATE,
414 p_login)
415 -- This Level and Outsideprocessing cost element
416 WHEN pp_tl_outside_processing_out <> 0 THEN
417 INTO mtl_pac_txn_cost_details
418 (pac_period_id,
419 cost_group_id,
420 cost_type_id ,
421 inventory_item_id,
422 transaction_id,
423 cost_element_id,
424 level_type,
425 transaction_cost,
426 wip_variance, -- New Column
427 last_update_date,
428 last_updated_by,
429 creation_date,
430 created_by,
431 request_id,
432 program_application_id,
433 program_id,
434 program_update_date,
435 last_update_login)
436 VALUES(p_pac_period,
437 p_cost_group,
438 p_cost_type,
439 p_item,
440 p_txn,
441 4,
442 1,
443 pp_tl_outside_processing_out,
444 0, -- New column value
445 SYSDATE,
446 p_user,
447 SYSDATE,
448 p_user,
449 p_request,
450 p_prog_app,
451 p_prog,
452 SYSDATE,
453 p_login)
454 -- Create 0 TL Material instead of 0 PL Material when there is no non-zero cost details
455 WHEN (pp_pl_material_out = 0 AND pp_pl_material_overhead_out = 0 AND pp_pl_resource_out = 0 AND
456 pp_pl_outside_processing_out = 0 AND pp_pl_overhead_out = 0 AND pp_tl_resource_out = 0 AND
457 pp_tl_outside_processing_out = 0 AND pp_tl_overhead_out = 0) THEN
458 INTO mtl_pac_txn_cost_details
459 (pac_period_id,
460 cost_group_id,
461 cost_type_id ,
462 inventory_item_id,
463 transaction_id,
464 cost_element_id,
465 level_type,
466 transaction_cost,
467 last_update_date,
468 last_updated_by,
469 creation_date,
470 created_by,
471 request_id,
472 program_application_id,
473 program_id,
474 program_update_date,
475 last_update_login)
476 VALUES (p_pac_period,
477 p_cost_group,
478 p_cost_type,
479 p_item,
480 p_txn,
481 1, -- Material Cost Element
482 1, -- This Level
483 0,-- Zero Cost
484 sysdate,
485 p_user,
486 sysdate,
487 p_user,
488 p_request,
489 p_prog_app,
490 p_prog,
491 sysdate,
492 p_login)
493
494 SELECT p_pac_period_id p_pac_period,
495 p_cost_group_id p_cost_group,
496 p_cost_type_id p_cost_type,
497 p_item_id p_item,
498 p_txn_id p_txn,
499 SUM(nvl(wppb.pl_material_out/
500 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_material_out,
501 SUM(nvl(wppb.pl_material_overhead_out/
502 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_material_overhead_out,
503 SUM(nvl(wppb.pl_resource_out/
504 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_resource_out,
505 SUM(nvl(wppb.pl_outside_processing_out/
506 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_outside_processing_out,
507 SUM(nvl(wppb.pl_overhead_out/
508 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_overhead_out,
509 SUM(nvl(wppb.tl_resource_out/
510 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_resource_out,
511 SUM(nvl(wppb.tl_outside_processing_out/
512 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_outside_processing_out,
513 SUM(nvl(wppb.tl_overhead_out/
514 decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_overhead_out,
515 p_user_id p_user,
516 p_login_id p_login,
517 p_request_id p_request,
518 p_prog_app_id p_prog_app,
519 p_prog_id p_prog
520 FROM wip_pac_period_balances wppb
521 WHERE wppb.pac_period_id = p_prior_period_id
522 AND wppb.cost_group_id = p_cost_group_id
523 AND wppb.wip_entity_id = p_entity_id
524 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
525 ELSE
526 INSERT INTO mtl_pac_txn_cost_details
527 (pac_period_id,
528 cost_group_id,
529 cost_type_id ,
530 inventory_item_id,
531 transaction_id,
532 cost_element_id,
533 level_type,
534 transaction_cost,
535 last_update_date,
536 last_updated_by,
537 creation_date,
538 created_by,
539 request_id,
540 program_application_id,
541 program_id,
542 program_update_date,
543 last_update_login)
544 VALUES(p_pac_period_id,
545 p_cost_group_id,
546 p_cost_type_id,
547 p_item_id,
548 p_txn_id,
549 1, -- Material Cost Element
550 1, -- This Level
551 0,-- Zero Cost
552 sysdate,
553 p_user_id,
554 sysdate,
555 p_user_id,
556 p_request_id,
557 p_prog_app_id,
558 p_prog_id,
559 sysdate,
560 p_login_id);
561 END IF; -- End of IF p_prior_period_id <> -1
562
563 ELSE
564 --------------------------------------------------
565 --
566 --------------------------------------------------
567 l_stmt_num := 40;
568 INSERT ALL
569 -- Previous Level and Material cost element
570 WHEN (pl_material_temp <> 0 OR pl_material_temp_var <> 0) THEN
571 INTO mtl_pac_txn_cost_details
572 (pac_period_id,
573 cost_group_id,
574 cost_type_id,
575 inventory_item_id,
576 transaction_id,
577 cost_element_id,
578 level_type,
579 transaction_cost,
580 wip_variance, -- New Column
581 last_update_date,
582 last_updated_by,
583 creation_date,
584 created_by,
585 request_id,
586 program_application_id,
587 program_id,
588 program_update_date,
589 last_update_login)
590 VALUES(p_pac_period,
591 p_cost_group,
592 p_cost_type,
593 p_item,
594 p_txn,
595 1,
596 2,
597 pl_material_temp,
598 pl_material_temp_var,-- New column value
599 sysdate,
600 p_user,
601 sysdate,
602 p_user,
603 p_request,
604 p_prog_app,
605 p_prog,
606 sysdate,
607 p_login )
608 -- Previous Level and Material Overhead cost element
609 WHEN (pl_material_overhead_temp <> 0 OR pl_material_overhead_temp_var <> 0) THEN
610 INTO mtl_pac_txn_cost_details
611 (pac_period_id,
612 cost_group_id,
613 cost_type_id,
614 inventory_item_id,
615 transaction_id,
616 cost_element_id,
617 level_type,
618 transaction_cost,
619 wip_variance, -- New Column
620 last_update_date,
621 last_updated_by,
622 creation_date,
623 created_by,
624 request_id,
625 program_application_id,
626 program_id,
627 program_update_date,
628 last_update_login)
629 VALUES(p_pac_period,
630 p_cost_group,
631 p_cost_type,
632 p_item,
633 p_txn,
634 2,
635 2,
636 pl_material_overhead_temp,
637 pl_material_overhead_temp_var,-- New column value
638 sysdate,
639 p_user,
640 sysdate,
641 p_user,
642 p_request,
643 p_prog_app,
644 p_prog,
645 sysdate,
646 p_login )
647 -- Previous Level and Resource cost element
648 WHEN (pl_resource_temp <> 0 OR pl_resource_temp_var <> 0) THEN
649 INTO mtl_pac_txn_cost_details
650 (pac_period_id,
651 cost_group_id,
652 cost_type_id,
653 inventory_item_id,
654 transaction_id,
655 cost_element_id,
656 level_type,
657 transaction_cost,
658 wip_variance, -- New Column
659 last_update_date,
660 last_updated_by,
661 creation_date,
662 created_by,
663 request_id,
664 program_application_id,
665 program_id,
666 program_update_date,
667 last_update_login)
668 VALUES (p_pac_period,
669 p_cost_group,
670 p_cost_type,
671 p_item,
672 p_txn,
673 3,
674 2,
675 pl_resource_temp,
676 pl_resource_temp_var,-- New column value
677 sysdate,
678 p_user,
679 sysdate,
680 p_user,
681 p_request,
682 p_prog_app,
683 p_prog,
684 sysdate,
685 p_login )
686 -- Previous Level and Outside Processing cost element
687 WHEN (pl_outside_processing_temp <>0 OR pl_outside_processing_temp_var <> 0) THEN
688 INTO mtl_pac_txn_cost_details
689 (pac_period_id,
690 cost_group_id,
691 cost_type_id,
692 inventory_item_id,
693 transaction_id,
694 cost_element_id,
695 level_type,
696 transaction_cost,
697 wip_variance, -- New Column
698 last_update_date,
699 last_updated_by,
700 creation_date,
701 created_by,
702 request_id,
703 program_application_id,
704 program_id,
705 program_update_date,
706 last_update_login)
707 VALUES (p_pac_period,
708 p_cost_group,
709 p_cost_type,
710 p_item,
711 p_txn,
712 4,
713 2,
714 pl_outside_processing_temp,
715 pl_outside_processing_temp_var,-- New column value
716 sysdate,
717 p_user,
718 sysdate,
719 p_user,
720 p_request,
721 p_prog_app,
722 p_prog,
723 sysdate,
724 p_login )
725 -- Previous Level and Overhead cost element
726 WHEN (pl_overhead_temp <>0 OR pl_overhead_temp_var <> 0) THEN
727 INTO mtl_pac_txn_cost_details
728 (pac_period_id,
729 cost_group_id,
730 cost_type_id,
731 inventory_item_id,
732 transaction_id,
733 cost_element_id,
734 level_type,
735 transaction_cost,
736 wip_variance, -- New Column
737 last_update_date,
738 last_updated_by,
739 creation_date,
740 created_by,
741 request_id,
742 program_application_id,
743 program_id,
744 program_update_date,
745 last_update_login)
746 VALUES (p_pac_period,
747 p_cost_group,
748 p_cost_type,
749 p_item,
750 p_txn,
751 5,
752 2,
753 pl_overhead_temp,
754 pl_overhead_temp_var,-- New column value
755 sysdate,
756 p_user,
757 sysdate,
758 p_user,
759 p_request,
760 p_prog_app,
761 p_prog,
762 sysdate,
763 p_login )
764 -- This level and Resource Cost Element
765 WHEN (tl_resource_temp <> 0) THEN
766 INTO mtl_pac_txn_cost_details
767 (pac_period_id,
768 cost_group_id,
769 cost_type_id ,
770 inventory_item_id,
771 transaction_id,
772 cost_element_id,
773 level_type,
774 transaction_cost,
775 last_update_date,
776 last_updated_by,
777 creation_date,
778 created_by,
779 request_id,
780 program_application_id,
781 program_id,
782 program_update_date,
783 last_update_login)
784 VALUES(p_pac_period,
785 p_cost_group,
786 p_cost_type,
787 p_item,
788 p_txn,
789 3,
790 1,
791 tl_resource_temp,
792 sysdate,
793 p_user,
794 sysdate,
795 p_user,
796 p_request,
797 p_prog_app,
798 p_prog,
799 sysdate,
800 p_login)
801 -- This Level and Outside Processing cost element
802 WHEN tl_outside_processing_temp <>0 THEN
803 INTO mtl_pac_txn_cost_details
804 (pac_period_id,
805 cost_group_id,
806 cost_type_id ,
807 inventory_item_id,
808 transaction_id,
809 cost_element_id,
810 level_type,
811 transaction_cost,
812 last_update_date,
813 last_updated_by,
814 creation_date,
815 created_by,
816 request_id,
817 program_application_id,
818 program_id,
819 program_update_date,
820 last_update_login)
821 VALUES(p_pac_period,
822 p_cost_group,
823 p_cost_type,
824 p_item,
825 p_txn,
826 4,
827 1,
828 tl_outside_processing_temp,
829 sysdate,
830 p_user,
831 sysdate,
832 p_user,
833 p_request,
834 p_prog_app,
835 p_prog,
836 sysdate,
837 p_login)
838 -- This Level and Overhead cost element
839 WHEN tl_overhead_temp <> 0 THEN
840 INTO mtl_pac_txn_cost_details
841 (pac_period_id,
842 cost_group_id,
843 cost_type_id ,
844 inventory_item_id,
845 transaction_id,
846 cost_element_id,
847 level_type,
848 transaction_cost,
849 last_update_date,
850 last_updated_by,
851 creation_date,
852 created_by,
853 request_id,
854 program_application_id,
855 program_id,
856 program_update_date,
857 last_update_login)
858 VALUES(p_pac_period,
859 p_cost_group,
860 p_cost_type,
861 p_item,
862 p_txn,
863 5,
864 1,
865 tl_overhead_temp,
866 sysdate,
867 p_user,
868 sysdate,
869 p_user,
870 p_request,
871 p_prog_app,
872 p_prog,
873 sysdate,
874 p_login)
875 -- Create 0 TL Material instead of 0 PL Material when there is no non-zero cost details
876 WHEN (pl_material_temp = 0 AND pl_material_overhead_temp = 0 AND pl_resource_temp = 0 AND
877 pl_outside_processing_temp = 0 AND pl_overhead_temp = 0 AND tl_resource_temp = 0 AND
878 tl_outside_processing_temp = 0 AND tl_overhead_temp = 0) THEN
879 INTO mtl_pac_txn_cost_details
880 (pac_period_id,
881 cost_group_id,
882 cost_type_id ,
883 inventory_item_id,
884 transaction_id,
885 cost_element_id,
886 level_type,
887 transaction_cost,
888 last_update_date,
889 last_updated_by,
890 creation_date,
891 created_by,
892 request_id,
893 program_application_id,
894 program_id,
895 program_update_date,
896 last_update_login)
897 VALUES(p_pac_period,
898 p_cost_group,
899 p_cost_type,
900 p_item,
901 p_txn,
902 1, -- Material Cost Element
903 1, -- This Level
904 0,-- Zero Cost
905 sysdate,
906 p_user,
907 sysdate,
908 p_user,
909 p_request,
910 p_prog_app,
911 p_prog,
912 sysdate,
913 p_login)
914
915 SELECT p_pac_period_id p_pac_period,
916 p_cost_group_id p_cost_group,
917 p_cost_type_id p_cost_type,
918 p_item_id p_item,
919 p_txn_id p_txn,
920 NVL(SUM(wppb.pl_material_temp)/
921 decode(l_net_qty,0,1,l_net_qty),0) pl_material_temp,
922 NVL(SUM(wppb.pl_material_overhead_temp)/
923 decode(l_net_qty,0,1,l_net_qty),0) pl_material_overhead_temp,
924 NVL(SUM(wppb.pl_resource_temp)/
925 decode(l_net_qty,0,1,l_net_qty),0) pl_resource_temp,
926 NVL(SUM(wppb.pl_outside_processing_temp)/
927 decode(l_net_qty,0,1,l_net_qty),0) pl_outside_processing_temp,
928 NVL(SUM(wppb.pl_overhead_temp)/
929 decode(l_net_qty,0,1,l_net_qty),0) pl_overhead_temp,
930 NVL(SUM(wppb.tl_resource_temp)/
931 decode(l_net_qty,0,1,l_net_qty),0) tl_resource_temp,
932 NVL(SUM(wppb.tl_outside_processing_temp)/
933 decode(l_net_qty,0,1,l_net_qty),0) tl_outside_processing_temp,
934 NVL(SUM(wppb.tl_overhead_temp)/
935 decode(l_net_qty,0,1,l_net_qty),0) tl_overhead_temp,
936 -- All temp Variance Columns
937 NVL(SUM(wppb.pl_material_temp_var),0) pl_material_temp_var,
938 NVL(SUM(wppb.pl_material_overhead_temp_var),0) pl_material_overhead_temp_var,
939 NVL(SUM(wppb.pl_resource_temp_var),0) pl_resource_temp_var,
940 NVL(SUM(wppb.pl_outside_processing_temp_var),0) pl_outside_processing_temp_var,
941 NVL(SUM(wppb.pl_overhead_temp_var),0) pl_overhead_temp_var,
942 p_user_id p_user,
943 p_login_id p_login,
944 p_request_id p_request,
945 p_prog_app_id p_prog_app,
946 p_prog_id p_prog
947 FROM wip_pac_period_balances wppb
948 WHERE wppb.pac_period_id = p_pac_period_id
949 AND wppb.cost_group_id = p_cost_group_id
950 AND wppb.wip_entity_id = p_entity_id
951 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
952 END IF;
953
954 IF (l_pLog) THEN
955 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
956 l_module || '.end',
957 l_api_name || ' >>>');
958 END IF;
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 IF (l_uLog) THEN
963 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
964 l_module || '.' || l_stmt_num,
965 SQLERRM);
966 END IF;
967 ROLLBACK;
968 fnd_file.put_line(fnd_file.log,' Exception in Insert_wip_costs');
969 x_err_num := SQLCODE;
970 x_err_code := NULL;
971 x_err_msg := SUBSTR('CSTPPWAS.insert_wip_costs('
972 || to_char(l_stmt_num)
973 || '): '
974 ||SQLERRM,1,240);
975 END insert_wip_costs;
976
977 /*---------------------------------------------------------------------------*
978 | PRIVATE PROCEDURE: Relief_BOM_Quantity |
979 | |
980 | Design: Ray, Vinayak, Srinath and Subbu |
981 | |
982 | Description: |
983 | This Procedure relieves the Material costs based on Predefined |
984 | Materials |
985 | |
986 | Logic: |
987 | The first cursor gets the operation sequence number for a given job. |
988 | |
989 | Second cursor gets all the components at that operations. |
990 | |
991 | Check any record exists in table CST_PAC_REQ_OPER_COST_DETAILS (cprocd) |
992 | |
993 | IF (no record exists in cprocd) THEN |
994 | FOR each cost elements LOOP |
995 | |
996 | END LOOP; |
997 | ELSE |
998 | Third cursor to get the cost elements from cprocd |
999 | Relieve material costs accordingly |
1000 | END of Third cursor |
1001 | END IF; |
1002 | END of Second Cursor |
1003 | End of First Cursor |
1004 | UPDATE table WIP_PAC_PERIOD_BALANCES (wppb) |
1005 | Check the job balance in this period for each cost element. |
1006 | If the value is negative then put these values in VAR columns |
1007 | END; |
1008 *----------------------------------------------------------------------------*/
1009 PROCEDURE Relief_BOM_Quantity (
1010 p_pac_period_id IN NUMBER,
1011 p_prior_period_id IN NUMBER,
1012 p_cost_group_id IN NUMBER,
1013 p_cost_type_id IN NUMBER,
1014 p_entity_id IN NUMBER,
1015 p_line_id IN NUMBER,
1016 p_net_qty IN NUMBER,
1017 p_final_completion_flag IN NUMBER,
1018 p_scrap IN NUMBER,
1019 p_op_seq IN NUMBER,
1020 p_start_date IN DATE,
1021 p_end_date IN DATE,
1022 p_login_id IN NUMBER,
1023 p_user_id IN NUMBER,
1024 p_request_id IN NUMBER,
1025 p_prog_id IN NUMBER DEFAULT -1,
1026 p_prog_app_id IN NUMBER DEFAULT -1,
1027 x_err_num OUT NOCOPY NUMBER,
1028 x_err_code OUT NOCOPY VARCHAR2,
1029 x_err_msg OUT NOCOPY VARCHAR2)
1030 IS
1031
1032 l_lot_size NUMBER; -- Lot based materials project for R12
1033 l_include_comp_yield NUMBER; -- Component yield enhancement changes in R12
1034 l_org_id NUMBER;
1035 l_repetitive_schedule_id NUMBER;
1036
1037 CURSOR c_wip_opseq IS
1038 SELECT wppb.operation_seq_num operation_seq_num
1039 FROM WIP_PAC_PERIOD_BALANCES wppb
1040 WHERE wppb.pac_period_id = p_pac_period_id
1041 AND wppb.cost_group_id = p_cost_group_id
1042 AND wppb.wip_entity_id = p_entity_id
1043 AND NVL(wppb.line_id, -99) = nvl(p_line_id,-99)
1044 AND wppb.operation_seq_num <= decode(p_scrap,1,p_op_seq,
1045 wppb.operation_seq_num);
1046
1047 --Added decode for Lot based materials project for R12
1048 --Divide the value of qpa by comp_yield_factor
1049 CURSOR c_wro(c_op_sequence NUMBER) IS
1050 SELECT wro.inventory_item_id component,
1051 Decode(wro.basis_type,
1052 2, (wro.quantity_per_assembly / l_lot_size),
1053 wro.quantity_per_assembly) / decode(l_include_comp_yield,
1054 1, nvl(wro.component_yield_factor,1),
1055 1) quantity_per_assembly
1056 FROM WIP_REQUIREMENT_OPERATIONS wro
1057 WHERE wro.wip_entity_id = p_entity_id
1058 AND nvl(wro.repetitive_schedule_id ,-99) = nvl(l_repetitive_schedule_id,-99)
1059 AND wro.operation_seq_num = c_op_sequence
1060 AND wro.wip_supply_type NOT IN (4,5,6);
1061
1062 CURSOR c_cost_element(op_sequence_num NUMBER,component NUMBER) IS
1063 SELECT cost_element_id cst_ele_id
1064 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1065 WHERE cprocd.pac_period_id = p_pac_period_id
1066 AND cprocd.cost_group_id = p_cost_group_id
1067 AND cprocd.wip_entity_id = p_entity_id
1068 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1069 AND cprocd.operation_seq_num = op_sequence_num
1070 AND cprocd.inventory_item_id = component;
1071
1072 type t_cst_element_cost is table of number
1073 index by binary_integer;
1074
1075 l_op_relieved_comp_cost t_cst_element_cost;
1076 l_job_balance t_cst_element_cost;
1077 l_prior_relieved_comp_cost t_cst_element_cost;
1078
1079 l_applied_qty NUMBER;
1080 l_record_exists NUMBER;
1081 l_current_period_cost NUMBER;
1082 l_avl_relieve_qty NUMBER;
1083 l_avl_relieve_value NUMBER;
1084 l_relieved_qty NUMBER;
1085 l_skip_below_process NUMBER;
1086 l_prior_relieved_value NUMBER;
1087 l_prior_relieved_qty NUMBER;
1088 l_required_qty NUMBER;
1089 l_assembly_return_cnt NUMBER;
1090 l_entity_type NUMBER;
1091 l_stmt_num NUMBER;
1092 l_err_num NUMBER;
1093 l_err_code VARCHAR2(240);
1094 l_err_msg VARCHAR2(240);
1095
1096 l_api_name CONSTANT VARCHAR2(30) := 'Relief_BOM_Quantity';
1097 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1098 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1099
1100 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1101 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1102 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1103 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1104 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1105 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1106
1107 BEGIN
1108
1109 IF (l_pLog) THEN
1110
1111 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1112 l_module || '.begin',
1113 l_api_name || ' <<< Parameters:
1114 p_pac_period_id = ' || p_pac_period_id || '
1115 p_prior_period_id = ' || p_prior_period_id || '
1116 p_entity_id = ' || p_entity_id || '
1117 p_line_id = ' || p_line_id || '
1118 p_net_qty = ' || p_net_qty ||'
1119 p_final_completion_flag = ' || p_final_completion_flag ||'
1120 p_scrap = ' || p_scrap ||'
1121 p_op_seq = ' || p_op_seq );
1122 END IF;
1123
1124 l_stmt_num := 0;
1125 -- Lot based materials project for R12, get the lot size of job
1126 l_lot_size := 1;
1127
1128 ------------------------------------------------------
1129 -- Get the Organization id for Component yield project
1130 -- Get the entity type for LotBased project
1131 ------------------------------------------------------
1132 SELECT entity_type,
1133 organization_id
1134 INTO l_entity_type,
1135 l_org_id
1136 FROM wip_entities
1137 WHERE wip_entity_id = p_entity_id;
1138
1139 IF (l_entity_type <> 2) THEN -- Exclude repetitive schedules.
1140
1141 SELECT nvl(start_quantity,1) -- to avoid divide by zero error
1142 INTO l_lot_size
1143 FROM wip_discrete_jobs
1144 WHERE wip_entity_id = p_entity_id;
1145 ELSE
1146 -- Get the repetitive_schedule_id for a wip entity id and line id
1147 SELECT wrs.repetitive_schedule_id
1148 INTO l_repetitive_schedule_id
1149 FROM wip_repetitive_schedules wrs
1150 WHERE wrs.wip_entity_id = p_entity_id
1151 AND wrs.line_id = p_line_id;
1152 END IF;
1153
1154 ---------------------------------------------------------------------------
1155 -- Get the value of Include Component yield flag, which will determine
1156 -- whether to include or not component yield factor in quantity per
1157 -- assembly
1158 ---------------------------------------------------------------------------
1159 SELECT nvl(include_component_yield, 1)
1160 INTO l_include_comp_yield
1161 FROM wip_parameters
1162 WHERE organization_id = l_org_id;
1163
1164 -------------------------------------
1165 -- Intialize job balance PL/SQL table
1166 -------------------------------------
1167 FOR cost_element in 1..5 LOOP
1168 l_job_balance(cost_element) := 0;
1169 END LOOP;
1170
1171 FOR op_seq_rec IN c_wip_opseq LOOP
1172 ---------------------------------------------------
1173 -- Intialize Operation-Component level PL/SQL table
1174 ---------------------------------------------------
1175 l_stmt_num := 10;
1176 FOR cost_element in 1..5 LOOP
1177 l_op_relieved_comp_cost(cost_element) := 0;
1178 l_prior_relieved_comp_cost(cost_element) := 0;
1179 END LOOP;
1180
1181 FOR comp_rec IN c_wro(op_seq_rec.operation_seq_num) LOOP
1182 -------------------------------------------------------
1183 -- Check record count for this Job, Component in cprocd
1184 -------------------------------------------------------
1185 l_stmt_num := 20;
1186 SELECT COUNT(*)
1187 INTO l_record_exists
1188 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1189 WHERE cprocd.pac_period_id = p_pac_period_id
1190 AND cprocd.cost_group_id = p_cost_group_id
1191 AND cprocd.wip_entity_id = p_entity_id
1192 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1193 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1194 AND cprocd.inventory_item_id = comp_rec.component
1195 AND ROWNUM < 2;
1196
1197 --------------------------------------------------------------------
1198 -- If no record exists in cprocd, but there is completion/return txn
1199 --------------------------------------------------------------------
1200 IF (l_record_exists = 0 ) THEN
1201 ------------------------------------------------------------
1202 -- Repeat for 5 cost elements if no record exists in cprocd
1203 ------------------------------------------------------------
1204 l_stmt_num := 30;
1205 FOR i IN 1..5 LOOP
1206
1207 l_current_period_cost := 0;
1208 l_avl_relieve_qty := 0;
1209 -----------------------------------------------------
1210 -- No Final completion exists in this PAC Period
1211 -----------------------------------------------------
1212 IF (NVL(p_final_completion_flag,0) <> 1) THEN
1213 -------------------------------------------------------------------
1214 -- Calculate required Qty
1215 -------------------------------------------------------------------
1216 l_stmt_num := 40;
1217 l_avl_relieve_qty := p_net_qty * comp_rec.quantity_per_assembly;
1218 ----------------------------------------------
1219 -- Get the component cost from current period
1220 ----------------------------------------------
1221 BEGIN
1222
1223 SELECT SUM(NVL(cpicd.item_cost,0))
1224 INTO l_current_period_cost
1225 FROM cst_pac_item_costs cpic,
1226 cst_pac_item_cost_details cpicd
1227 WHERE cpic.pac_period_id = p_pac_period_id
1228 AND cpic.cost_group_id = p_cost_group_id
1229 AND cpic.inventory_item_id = comp_rec.component
1230 AND cpic.cost_layer_id = cpicd.cost_layer_id
1231 AND cpicd.cost_element_id = i
1232 GROUP BY cpicd.cost_element_id;
1233 EXCEPTION
1234 WHEN NO_DATA_FOUND THEN
1235 l_current_period_cost := 0;
1236 END;
1237 END IF;
1238 -----------------------------------------------------------------------
1239 -- Add period cost to PL/SQL table. This table value will be used
1240 -- while updating WPPB table at l_stmt_num := 270
1241 ------------------------------------------------------------------------
1242 l_stmt_num := 50;
1243 l_op_relieved_comp_cost(i) := l_op_relieved_comp_cost(i) + l_current_period_cost * l_avl_relieve_qty;
1244 -- This is used to find out balance to be relieved from this job in this period
1245 l_job_balance(i) := l_job_balance(i) + l_current_period_cost * l_avl_relieve_qty;
1246
1247 -- Statement level log message for FND logging
1248 IF (l_sLog) THEN
1249 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1250 l_module || '.'||l_stmt_num,
1251 'Operation Seq :' || op_seq_rec.operation_seq_num || ','||
1252 'Component :' || comp_rec.component || ','||
1253 'Cost Element :' || i || ','||
1254 'l_record_exists :' || l_record_exists || ','||
1255 'l_current_period_cost :' || l_current_period_cost || ','||
1256 'l_avl_relieve_qty :' || l_avl_relieve_qty || ','||
1257 'l_op_relieved_comp_cost :' || l_op_relieved_comp_cost(i) || ','||
1258 'l_job_balance :' || l_job_balance(i));
1259 END IF;
1260
1261 ------------------------------------------------------
1262 -- Insert a record into CST_PAC_REQ_OPER_COST_DETAILS
1263 ------------------------------------------------------
1264 l_stmt_num := 60;
1265 INSERT INTO CST_PAC_REQ_OPER_COST_DETAILS
1266 (pac_period_id,
1267 cost_group_id,
1268 wip_entity_id,
1269 line_id,
1270 inventory_item_id,
1271 cost_element_id,
1272 operation_seq_num,
1273 applied_value,
1274 applied_quantity,
1275 relieved_value,
1276 relieved_quantity,
1277 comp_variance,
1278 Temp_Relieved_value,
1279 -- who Columns
1280 last_update_date,
1281 last_updated_by,
1282 creation_date,
1283 created_by,
1284 request_id,
1285 program_application_id,
1286 program_id,
1287 program_update_date,
1288 last_update_login
1289 )
1290 VALUES (p_pac_period_id,
1291 p_cost_group_id,
1292 p_entity_id,
1293 p_line_id,
1294 comp_rec.component,
1295 i,
1296 op_seq_rec.operation_seq_num,
1297 0,
1298 0,
1299 l_current_period_cost * l_avl_relieve_qty,
1300 l_avl_relieve_qty,
1301 0,
1302 l_current_period_cost * l_avl_relieve_qty,
1303 sysdate,
1304 p_user_id,
1305 sysdate,
1306 p_user_id,
1307 p_request_id,
1308 p_prog_app_id,
1309 p_prog_id,
1310 sysdate,
1311 p_login_id);
1312 END LOOP;
1313 ---------------------------------------------------------------------------------------
1314 -- If record exists in cprocd table and there is(are) Assembly completion/return txn(s)
1315 ---------------------------------------------------------------------------------------
1316 ELSE
1317 -------------------------------------------------------------------------------------------
1318 -- Loop through the Cost elements for which already record is there in cprocd for Component
1319 -------------------------------------------------------------------------------------------
1320 FOR cstelement_rec in c_cost_element(op_seq_rec.operation_seq_num,comp_rec.component ) LOOP
1321
1322 l_applied_qty := 0;
1323 l_avl_relieve_value := 0;
1324 l_avl_relieve_qty := 0;
1325 l_prior_relieved_value := 0;
1326 l_prior_relieved_qty := 0;
1327 l_skip_below_process := 0;
1328 -------------------------------------------------------------
1329 -- Calculate the Required Quantity
1330 -------------------------------------------------------------
1331 l_stmt_num := 70;
1332 l_required_qty := p_net_qty * comp_rec.quantity_per_assembly;
1333
1334 -- Statement level log message for FND logging
1335 IF (l_sLog) THEN
1336 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1337 l_module || '.'||l_stmt_num,
1338 'Operation Seq :' || op_seq_rec.operation_seq_num || ','||
1339 'Component :' || comp_rec.component || ','||
1340 'Cost Element :' || cstelement_rec.cst_ele_id || ','||
1341 'p_net_qty :' || p_net_qty || ','||
1342 'l_required_qty :' || l_required_qty || ','||
1343 'quantity_per_assembly :'|| comp_rec.quantity_per_assembly ||','||
1344 'l_skip_below_process :'|| l_skip_below_process ||','||
1345 'p_final_completion_flag :' || p_final_completion_flag);
1346 END IF;
1347
1348
1349 ------------------------------------------------------
1350 -- p_net_qty < 0 then get Avg of Prior Relieved Value
1351 ------------------------------------------------------
1352 IF (p_net_qty < 0) THEN
1353 BEGIN
1354 l_stmt_num := 80;
1355 SELECT nvl(relieved_value,0),
1356 decode(nvl(relieved_quantity, 0),
1357 0,1,
1358 nvl(relieved_quantity, 0))
1359 INTO l_avl_relieve_value,
1360 l_avl_relieve_qty
1361 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1362 WHERE cprocd.pac_period_id = p_prior_period_id
1363 AND cprocd.cost_group_id = p_cost_group_id
1364 AND cprocd.wip_entity_id = p_entity_id
1365 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1366 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1367 AND cprocd.inventory_item_id = comp_rec.component
1368 AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 l_avl_relieve_value := 0;
1372 l_avl_relieve_qty := 1; --To avoid zero Division error
1373 END;
1374 ----------------------------------------------------------
1375 -- Calculate to be Relived value in case of p_net_qty < 0
1376 ----------------------------------------------------------
1377 l_stmt_num := 90;
1378 l_avl_relieve_value := (l_avl_relieve_value/l_avl_relieve_qty) * l_required_qty;
1379 l_avl_relieve_qty := l_required_qty;
1380 ------------------------------------------------------------------
1381 -- Add the Component Relieve value to the Operation Relieve value
1382 ------------------------------------------------------------------
1383 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) :=
1384 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) +
1385 l_avl_relieve_value;
1386 -- This is used to find out balance to be relieved from this job in this period
1387 -- For more Assembly returns than Assembly completions then sign of p_net_qty is negative.
1388 -- So making sign to opposite sign.
1389 l_job_balance(cstelement_rec.cst_ele_id) := l_job_balance(cstelement_rec.cst_ele_id) + l_avl_relieve_value * (-1);
1390
1391 -- Statement level log message for FND logging
1392 IF (l_sLog) THEN
1393 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1394 l_module || '.'||l_stmt_num,
1395 'l_avl_relieve_value :' || l_avl_relieve_value || ','||
1396 'l_avl_relieve_qty :' || l_avl_relieve_qty || ','||
1397 'l_op_relieved_comp_cost :' || l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) || ','||
1398 'l_job_balance :' || l_job_balance(cstelement_rec.cst_ele_id));
1399 END IF;
1400
1401 ELSE -- p_net_qty > 0 OR p_net_qty = 0
1402
1403 -------------------------------------------------------------------------------------
1404 -- p_net_qty = 0, Assembly completions cancels Assembly returns
1405 -------------------------------------------------------------------------------------
1406 l_stmt_num := 100;
1407 IF ( p_net_qty = 0) THEN
1408
1409 --------------------------------------
1410 --Check Final completion exists or not
1411 --------------------------------------
1412 IF (p_final_completion_flag = 1) THEN
1413
1414 ------------------------------------------------
1415 -- Get the Assembly return qty in this period
1416 ------------------------------------------------
1417 l_stmt_num := 110;
1418 SELECT sum(primary_quantity)
1419 INTO l_assembly_return_cnt
1420 FROM mtl_material_transactions mmt
1421 WHERE mmt.transaction_source_id = p_entity_id
1422 AND mmt.transaction_action_id = 32
1423 AND mmt.transaction_source_type_id = 5
1424 AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
1425 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
1426 AND (TRUNC(p_end_date) + 0.99999);
1427
1428 -------------------------------------------------------------------
1429 -- Get the Avg of Prior Relieved Value.
1430 -------------------------------------------------------------------
1431 l_stmt_num := 120;
1432 BEGIN
1433 SELECT nvl(Relieved_Value,0),
1434 decode(sign(nvl(Relieved_quantity,0)),
1435 0,1,
1436 Relieved_quantity)
1437 INTO l_prior_relieved_value,
1438 l_prior_relieved_qty
1439 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1440 WHERE cprocd.wip_entity_id = p_entity_id
1441 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1442 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1443 AND cprocd.inventory_item_id = comp_rec.component
1444 AND cprocd.cost_element_id = cstelement_rec.cst_ele_id
1445 AND cprocd.cost_group_id = p_cost_group_id
1446 AND cprocd.pac_period_id = p_prior_period_id;
1447 EXCEPTION
1448 WHEN NO_DATA_FOUND THEN
1449 l_prior_relieved_value := 0;
1450 l_prior_relieved_qty := 1; -- To avoid Zero division error
1451 END;
1452 ----------------------------------------------------------------------------
1453 -- Add the Net value = Avg of Prior Relieved Value * Assmebly return qty * qpa
1454 -- to PL/SQL table. This used to update the WPPB's TEMP column value
1455 -- and cprocd's Temp_Relieved_value
1456 -----------------------------------------------------------------------------
1457 l_prior_relieved_value := (l_prior_relieved_value / l_prior_relieved_qty) *
1458 l_assembly_return_cnt *
1459 comp_rec.quantity_per_assembly;
1460 l_prior_relieved_comp_cost(cstelement_rec.cst_ele_id) := l_prior_relieved_comp_cost(cstelement_rec.cst_ele_id) +
1461 (-1) * l_prior_relieved_value;
1462 -- This is used to find out balance to be relieved from this job in this period
1463 l_job_balance(cstelement_rec.cst_ele_id) := l_job_balance(cstelement_rec.cst_ele_id) + (-1) * l_prior_relieved_value;
1464
1465 -- Statement level log message for FND logging
1466 IF (l_sLog) THEN
1467 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1468 l_module || '.'||l_stmt_num,
1469 'l_assembly_return_cnt :' || l_assembly_return_cnt || ','||
1470 'l_prior_relieved_value :' || l_prior_relieved_value || ','||
1471 'l_prior_relieved_comp_cost :' || l_prior_relieved_comp_cost(cstelement_rec.cst_ele_id) || ','||
1472 'l_job_balance :' || l_job_balance(cstelement_rec.cst_ele_id));
1473 END IF;
1474 ELSE
1475 -------------------------------------------------------------------
1476 -- No final completion then relieve at average of prior completions
1477 -------------------------------------------------------------------
1478 l_stmt_num := 130;
1479 BEGIN
1480 SELECT nvl(relieved_value,0),
1481 decode(nvl(relieved_quantity, 0),
1482 0,1,
1483 nvl(relieved_quantity, 0))
1484 INTO l_avl_relieve_value,
1485 l_avl_relieve_qty
1486 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1487 WHERE cprocd.pac_period_id = p_prior_period_id
1488 AND cprocd.cost_group_id = p_cost_group_id
1489 AND cprocd.wip_entity_id = p_entity_id
1490 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1491 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1492 AND cprocd.inventory_item_id = comp_rec.component
1493 AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
1494 EXCEPTION
1495 WHEN NO_DATA_FOUND THEN
1496 l_avl_relieve_value := 0;
1497 l_avl_relieve_qty := 1; --To avoid zero Division error
1498 END;
1499 ---------------------------------------------------------------------
1500 -- Calculate to be Relived value = average of prior completions
1501 -- make l_avl_relieve_qty to zero so subsequent cprocd's relived_qty
1502 -- column update will not changed in this case
1503 -- Update the new variable l_skip_below_process = 1
1504 -- So that we can skip the
1505 ---------------------------------------------------------------------
1506 l_stmt_num := 140;
1507 l_avl_relieve_value := (l_avl_relieve_value/l_avl_relieve_qty);
1508 l_avl_relieve_qty := 0;
1509 l_skip_below_process := 1;
1510
1511 ------------------------------------------------------------------
1512 -- Add the Component Relieve value to the Operation Relieve value
1513 ------------------------------------------------------------------
1514 l_stmt_num := 150;
1515 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) :=
1516 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) +
1517 l_avl_relieve_value;
1518 -- This is used to find out balance to be relieved from this job in this period
1519 l_job_balance(cstelement_rec.cst_ele_id) := l_job_balance(cstelement_rec.cst_ele_id) + l_avl_relieve_value;
1520
1521 -- Statement level log message for FND logging
1522 IF (l_sLog) THEN
1523 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1524 l_module || '.'||l_stmt_num,
1525 'l_skip_below_process :' || l_skip_below_process || ','||
1526 'l_avl_relieve_value :' || l_avl_relieve_value || ','||
1527 'l_op_relieved_comp_cost :' || l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) || ','||
1528 'l_job_balance :' || l_job_balance(cstelement_rec.cst_ele_id));
1529 END IF;
1530 END IF;
1531
1532 END IF; --End of IF ( p_net_qty = 0)
1533
1534 -----------------------------------------------------------------------------
1535 -- Check to skip the below process or not. In case of p_net_qty = 0 and
1536 -- no final completion then l_skip_below_process = 1, below part is skipped
1537 -- in all other cases below IF will be executed
1538 -----------------------------------------------------------------------------
1539 IF (l_skip_below_process <> 1 ) THEN
1540 ------------------------------------------------------------------------
1541 -- Get the Available to Relieve Value and Available to Relieve Quantity
1542 -- applied quantity and relieved quantity
1543 ------------------------------------------------------------------------
1544 l_stmt_num := 160;
1545 SELECT (nvl(applied_value,0) - nvl(relieved_value,0) - nvl(comp_variance,0)),
1546 nvl(applied_quantity, 0),
1547 (nvl(applied_quantity, 0) - nvl(relieved_quantity, 0)),
1548 nvl(relieved_quantity, 0)
1549 INTO l_avl_relieve_value,
1550 l_applied_qty,
1551 l_avl_relieve_qty,
1552 l_relieved_qty
1553 FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
1554 WHERE cprocd.pac_period_id = p_pac_period_id
1555 AND cprocd.cost_group_id = p_cost_group_id
1556 AND cprocd.wip_entity_id = p_entity_id
1557 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1558 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1559 AND cprocd.inventory_item_id = comp_rec.component
1560 AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
1561
1562 -- Statement level log message for FND logging
1563 IF (l_sLog) THEN
1564 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1565 l_module || '.'||l_stmt_num,
1566 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1567 'l_applied_qty :' || l_applied_qty || ','||
1568 'l_avl_relieve_qty :' || l_avl_relieve_qty || ','||
1569 'l_relieved_qty :' || l_relieved_qty);
1570 END IF;
1571
1572 ---------------------------------------------------------------------
1573 -- Available to Relieve Value and Quantity are zero,
1574 -- So use Current Periodic Cost for Ordinary completions
1575 -- If Final completion exists in this period then Relieve Zero Value
1576 ---------------------------------------------------------------------
1577 l_stmt_num := 170;
1578 IF (l_avl_relieve_value = 0 and l_avl_relieve_qty = 0) THEN
1579 -------------------------------------------------------------------------------------------
1580 -- Final Completion exists in this period, Ignore Final Completion in case of p_net_qty < 0
1581 -------------------------------------------------------------------------------------------
1582 l_stmt_num := 180;
1583 IF (p_final_completion_flag = 1 AND p_net_qty >= 0) THEN
1584 -----------------------------------------------------
1585 -- Relieve Zero values
1586 -----------------------------------------------------
1587 l_avl_relieve_value := 0;
1588 --------------------------------------------------------
1589 -- Calculate the Relieve Qty in case of Final completion
1590 --------------------------------------------------------
1591 IF ( (l_required_qty > 0 AND l_applied_qty > l_required_qty) OR
1592 (l_required_qty < 0 AND l_applied_qty < l_required_qty)) THEN
1593
1594 l_avl_relieve_qty := l_applied_qty - l_relieved_qty;
1595 -- Statement level log message for FND logging
1596 IF (l_sLog) THEN
1597 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1598 l_module || '.'||l_stmt_num,
1599 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1600 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1601 END IF;
1602 END IF;
1603 ELSE
1604 l_stmt_num := 190;
1605 SELECT SUM(NVL(cpicd.item_cost,0))
1606 INTO l_current_period_cost
1607 FROM cst_pac_item_costs cpic,
1608 cst_pac_item_cost_details cpicd
1609 WHERE cpic.pac_period_id = p_pac_period_id
1610 AND cpic.cost_group_id = p_cost_group_id
1611 AND cpic.inventory_item_id = comp_rec.component
1612 AND cpic.cost_layer_id = cpicd.cost_layer_id
1613 AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
1614
1615 l_avl_relieve_value := l_current_period_cost * l_required_qty;
1616 l_avl_relieve_qty := l_required_qty;
1617 -- Statement level log message for FND logging
1618 IF (l_sLog) THEN
1619 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1620 l_module || '.'||l_stmt_num,
1621 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1622 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1623 END IF;
1624 END IF; -- End of IF at l_stmt_num := 180
1625 --------------------------------------------------------------------------
1626 -- Available to Relieve Value and Quantity are negative OR
1627 -- Available to Relieve Value and Quantity are positive
1628 -- So use WIP Avg Cost for Ordinary Completions
1629 -- If Final completion exists in this period then Relieve Available Value
1630 --------------------------------------------------------------------------
1631 ELSIF ((l_avl_relieve_value < 0 AND l_avl_relieve_qty < 0) OR
1632 (l_avl_relieve_value > 0 and l_avl_relieve_qty > 0)) THEN
1633
1634 --------------------------------------------------------------------------------------------
1635 -- Final Completion exists in this period, Ignore Final Completion in case of p_net_qty <0
1636 --------------------------------------------------------------------------------------------
1637 l_stmt_num := 200;
1638 IF (p_final_completion_flag = 1 AND p_net_qty >= 0 ) THEN
1639
1640 l_avl_relieve_value := l_avl_relieve_value;
1641 --------------------------------------------------------
1642 -- Calculate the Relieve Qty in case of Final completion
1643 --------------------------------------------------------
1644 IF ( (l_required_qty > 0 AND l_applied_qty > l_required_qty) OR
1645 (l_required_qty < 0 AND l_applied_qty < l_required_qty)) THEN
1646
1647 l_avl_relieve_qty := l_applied_qty - l_relieved_qty;
1648 -- Statement level log message for FND logging
1649 IF (l_sLog) THEN
1650 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1651 l_module || '.'||l_stmt_num,
1652 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1653 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1654 END IF;
1655 END IF;
1656
1657 ELSE -- Ordinary Completion Only
1658 ----------------------------------------------------------------
1659 -- Required Quantity is less than or Equal to Available Quantity
1660 ----------------------------------------------------------------
1661 l_stmt_num := 210;
1662 -- Added Modified new condition
1663 IF (l_required_qty = 0 OR
1664 (l_required_qty > 0 and l_avl_relieve_qty > 0 and l_required_qty <= l_avl_relieve_qty) OR
1665 (l_required_qty < 0 and l_avl_relieve_qty < 0 and l_required_qty >= l_avl_relieve_qty)) THEN
1666
1667 l_avl_relieve_value := (l_avl_relieve_value/l_avl_relieve_qty) * l_required_qty;
1668 l_avl_relieve_qty := l_required_qty;
1669
1670 -- Statement level log message for FND logging
1671 IF (l_sLog) THEN
1672 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1673 l_module || '.'||l_stmt_num,
1674 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1675 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1676 END IF;
1677 ELSE -- Required Quantity is more than Available Quantity
1678 ---------------------------------------------------
1679 -- Get the Current Periodic Cost
1680 ---------------------------------------------------
1681 l_stmt_num := 220;
1682 SELECT SUM(NVL(cpicd.item_cost,0))
1683 INTO l_current_period_cost
1684 FROM cst_pac_item_costs cpic,
1685 cst_pac_item_cost_details cpicd
1686 WHERE cpic.pac_period_id = p_pac_period_id
1687 AND cpic.cost_group_id = p_cost_group_id
1688 AND cpic.inventory_item_id = comp_rec.component
1689 AND cpic.cost_layer_id = cpicd.cost_layer_id
1690 AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
1691
1692 ------------------------------------------------------------------------------------------------------
1693 -- Relieve Value = Available to Relieve Value + (Required qty - Available to Relieve qty ) * PWAC Cost
1694 -- Required qty = p_net_qty * comp_rec.quantity_per_assembly
1695 ------------------------------------------------------------------------------------------------------
1696 l_avl_relieve_value := l_avl_relieve_value + (l_required_qty - l_avl_relieve_qty) *
1697 l_current_period_cost;
1698 l_avl_relieve_qty := l_required_qty;
1699
1700 -- Statement level log message for FND logging
1701 IF (l_sLog) THEN
1702 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1703 l_module || '.'||l_stmt_num,
1704 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1705 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1706 END IF;
1707 END IF; -- end of IF l_stmt_num := 210
1708
1709 END IF; -- End of Final Completion exists in this period, End of l_stmt_num := 200
1710 ------------------------------------------------------------------------------------------
1711 -- Available to Relieve Value and Available to Relieve Quantity both are opposite in signs
1712 ------------------------------------------------------------------------------------------
1713 ELSIF ((l_avl_relieve_value < 0 AND l_avl_relieve_qty >= 0) OR
1714 (l_avl_relieve_value > 0 AND l_avl_relieve_qty <= 0)) THEN
1715 --------------------------------------------------------------------------------------------
1716 -- Final Completion exists in this period, Ignore Final Completion in case of p_net_qty < 0
1717 --------------------------------------------------------------------------------------------
1718 l_stmt_num := 240;
1719 IF (p_final_completion_flag = 1 AND p_net_qty >= 0) THEN
1720 -------------------------------------------
1721 -- Relieve Available Value from Job
1722 -------------------------------------------
1723 l_avl_relieve_value := l_avl_relieve_value;
1724 --------------------------------------------------------
1725 -- Calculate the Relieve Qty in case of Final completion
1726 --------------------------------------------------------
1727 IF ( (l_required_qty > 0 AND l_applied_qty > l_required_qty) OR
1728 (l_required_qty < 0 AND l_applied_qty < l_required_qty)) THEN
1729
1730 l_avl_relieve_qty := l_applied_qty - l_relieved_qty;
1731 -- Statement level log message for FND logging
1732 IF (l_sLog) THEN
1733 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1734 l_module || '.'||l_stmt_num,
1735 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1736 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1737 END IF;
1738 END IF;
1739 ELSE
1740 -------------------------------------------------------------
1741 -- Ordinary Cmpletions Only, So get the Current Periodic Cost
1742 -------------------------------------------------------------
1743 l_stmt_num := 250;
1744 SELECT SUM(NVL(cpicd.item_cost,0))
1745 INTO l_current_period_cost
1746 FROM cst_pac_item_costs cpic,
1747 cst_pac_item_cost_details cpicd
1748 WHERE cpic.pac_period_id = p_pac_period_id
1749 AND cpic.cost_group_id = p_cost_group_id
1750 AND cpic.inventory_item_id = comp_rec.component
1751 AND cpic.cost_layer_id = cpicd.cost_layer_id
1752 AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
1753
1754 l_avl_relieve_value := l_current_period_cost * l_required_qty;
1755 l_avl_relieve_qty := l_required_qty;
1756
1757 -- Statement level log message for FND logging
1758 IF (l_sLog) THEN
1759 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1760 l_module || '.'||l_stmt_num,
1761 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1762 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1763 END IF;
1764 END IF; -- End of IF at l_stmt_num := 240
1765 END IF; -- End of IF at l_stmt_num := 170
1766
1767 ------------------------------------------------------------------
1768 -- Add the Component Relieve value to the Operation Relieve value
1769 ------------------------------------------------------------------
1770 l_stmt_num := 250;
1771 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) :=
1772 l_op_relieved_comp_cost(cstelement_rec.cst_ele_id) +
1773 l_avl_relieve_value;
1774 -- This is used to find out balance to be relieved from this job in this period
1775 l_job_balance(cstelement_rec.cst_ele_id) := l_job_balance(cstelement_rec.cst_ele_id) + l_avl_relieve_value;
1776
1777 -- Statement level log message for FND logging
1778 IF (l_sLog) THEN
1779 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1780 l_module || '.'||l_stmt_num,
1781 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1782 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1783 END IF;
1784 END IF; -- End of IF l_skip_below_process <> 1
1785
1786 END IF; -- End of IF p_net_qty < 0
1787
1788 ------------------------------------------------------
1789 -- Update cprocd table with Calculated Relieved value
1790 ------------------------------------------------------
1791 l_stmt_num := 260;
1792 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
1793 SET cprocd.Relieved_value = nvl(cprocd.Relieved_Value,0) +
1794 decode(p_net_qty,
1795 0,decode(p_final_completion_flag,
1796 0, 0,
1797 NULL,0,
1798 l_avl_relieve_value),
1799 l_avl_relieve_value),
1800 cprocd.Temp_Relieved_value = l_avl_relieve_value + (-1) * l_prior_relieved_value,
1801 -- Same as TEMP column in WPPB table. This will be used if Total Job value is -ve then
1802 -- Update the Comp_variance = Comp_variance + Temp_Relieved_value at the end
1803 cprocd.Relieved_quantity = nvl(cprocd.Relieved_quantity,0) + l_avl_relieve_qty,
1804 cprocd.last_update_date = SYSDATE,
1805 cprocd.last_updated_by = p_user_id,
1806 cprocd.last_update_login = p_login_id,
1807 cprocd.request_id = p_request_id,
1808 cprocd.program_application_id = p_prog_app_id,
1809 cprocd.program_id = p_prog_id,
1810 cprocd.program_update_date = SYSDATE
1811 WHERE cprocd.wip_entity_id = p_entity_id
1812 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1813 AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
1814 AND cprocd.inventory_item_id = comp_rec.component
1815 AND cprocd.cost_element_id = cstelement_rec.cst_ele_id
1816 AND cprocd.cost_group_id = p_cost_group_id
1817 AND cprocd.pac_period_id = p_pac_period_id;
1818
1819 -- Statement level log message for FND logging
1820 IF (l_sLog) THEN
1821 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1822 l_module || '.'||l_stmt_num,
1823 'l_avl_relieve_value :' ||l_avl_relieve_value || ','||
1824 'l_prior_relieved_value :' ||l_prior_relieved_value || ','||
1825 'l_avl_relieve_qty :' || l_avl_relieve_qty);
1826 END IF;
1827 END LOOP; -- End of Cost Element Loop
1828
1829 END IF; -- End of l_record_exists check
1830
1831 END LOOP; -- End of Component Loop
1832
1833 -----------------------------------------------------------------------------------
1834 -- Update the wip_pac_period_balances with relieved values
1835 -----------------------------------------------------------------------------------
1836 -- OUT columns will not be updated if p_net_qty = 0 and p_final_completion_flag <>1
1837 -- In this case only TEMP columns are updated
1838 ------------------------------------------------------------------------------------
1839 l_stmt_num := 270;
1840 UPDATE wip_pac_period_balances wppb
1841 SET wppb.pl_material_out = wppb.pl_material_out + decode(p_net_qty,0,
1842 decode(p_final_completion_flag,
1843 0,0,
1844 NULL,0,
1845 l_op_relieved_comp_cost(1)),
1846 l_op_relieved_comp_cost(1)),
1847 wppb.pl_material_temp = wppb.pl_material_temp + l_op_relieved_comp_cost(1)
1848 + decode(p_net_qty,0,
1849 decode(p_final_completion_flag,
1850 1, l_prior_relieved_comp_cost(1),
1851 0),
1852 0),
1853
1854 wppb.pl_material_overhead_out = wppb.pl_material_overhead_out + decode(p_net_qty,
1855 0,decode(p_final_completion_flag,
1856 0,0,
1857 NULL,0,
1858 l_op_relieved_comp_cost(2)),
1859 l_op_relieved_comp_cost(2)),
1860 wppb.pl_material_overhead_temp = wppb.pl_material_overhead_temp + l_op_relieved_comp_cost(2)
1861 + decode(p_net_qty,0,
1862 decode(p_final_completion_flag,
1863 1, l_prior_relieved_comp_cost(2),
1864 0),0),
1865
1866 wppb.pl_resource_out = wppb.pl_resource_out + decode(p_net_qty,
1867 0,decode(p_final_completion_flag,
1868 0,0,
1869 NULL,0,
1870 l_op_relieved_comp_cost(3)),
1871 l_op_relieved_comp_cost(3)),
1872 wppb.pl_resource_temp = wppb.pl_resource_temp + l_op_relieved_comp_cost(3)
1873 + decode(p_net_qty,0,
1874 decode(p_final_completion_flag,
1875 1, l_prior_relieved_comp_cost(3),
1876 0), 0),
1877
1878 wppb.pl_outside_processing_out = wppb.pl_outside_processing_out + decode(p_net_qty,
1879 0,decode(p_final_completion_flag,
1880 0,0,
1881 NULL,0,
1882 l_op_relieved_comp_cost(4)),
1883 l_op_relieved_comp_cost(4)),
1884 wppb.pl_outside_processing_temp = wppb.pl_outside_processing_temp + l_op_relieved_comp_cost(4)
1885 + decode(p_net_qty,0,
1886 decode(p_final_completion_flag,
1887 1, l_prior_relieved_comp_cost(4),
1888 0), 0),
1889
1890 wppb.pl_overhead_out = wppb.pl_overhead_out + decode(p_net_qty,
1891 0,decode(p_final_completion_flag,
1892 0,0,
1893 NULL,0,
1894 l_op_relieved_comp_cost(5)),
1895 l_op_relieved_comp_cost(5)),
1896 wppb.pl_overhead_temp = wppb.pl_overhead_temp + l_op_relieved_comp_cost(5)
1897 + decode(p_net_qty,0,
1898 decode(p_final_completion_flag,
1899 1, l_prior_relieved_comp_cost(5),
1900 0),0),
1901
1902 wppb.last_update_date = SYSDATE,
1903 wppb.last_updated_by = p_user_id,
1904 wppb.last_update_login = p_login_id,
1905 wppb.request_id = p_request_id,
1906 wppb.program_application_id = p_prog_app_id,
1907 wppb.program_id = p_prog_id,
1908 wppb.program_update_date = SYSDATE
1909
1910 WHERE wppb.wip_entity_id = p_entity_id
1911 AND wppb.pac_period_id = p_pac_period_id
1912 AND wppb.cost_type_id = p_cost_type_id
1913 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99)
1914 AND wppb.cost_group_id = p_cost_group_id
1915 AND wppb.operation_seq_num = op_seq_rec.operation_seq_num;
1916
1917 END LOOP; -- End of Operation Sequence Loop
1918
1919 -----------------------------------------------------------------------------
1920 -- Check Cost Element balance to be relieved from the job are Negative or Not
1921 -----------------------------------------------------------------------------
1922
1923 IF (l_job_balance(1) < 0) THEN
1924
1925 l_stmt_num := 280;
1926 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
1927 SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + nvl(cprocd.Temp_Relieved_value,0),
1928 cprocd.Relieved_value = nvl(cprocd.Relieved_value,0) - nvl(cprocd.Temp_Relieved_value,0),
1929 cprocd.last_update_date = SYSDATE,
1930 cprocd.last_updated_by = p_user_id,
1931 cprocd.last_update_login = p_login_id,
1932 cprocd.request_id = p_request_id,
1933 cprocd.program_application_id = p_prog_app_id,
1934 cprocd.program_id = p_prog_id,
1935 cprocd.program_update_date = SYSDATE
1936 WHERE cprocd.cost_group_id = p_cost_group_id
1937 AND cprocd.pac_period_id = p_pac_period_id
1938 AND cprocd.wip_entity_id = p_entity_id
1939 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1940 AND cprocd.cost_element_id = 1; -- Material Cost Element
1941
1942 l_stmt_num := 290;
1943 UPDATE WIP_PAC_PERIOD_BALANCES wppb
1944 -- New column to store the variance in case if total job balance is negative
1945 -- This column is not storing accumlated value
1946 SET wppb.pl_material_temp_var = wppb.pl_material_temp,
1947 -- This column is storing accumlated values of variance
1948 wppb.pl_material_var = nvl(wppb.pl_material_var,0) + nvl(wppb.pl_material_temp,0),
1949 --Subtract the variance column value from OUT column
1950 wppb.pl_material_out = wppb.pl_material_out - wppb.pl_material_temp,
1951 -- Make TEMP value to Zero
1952 wppb.pl_material_temp = 0,
1953
1954 wppb.last_update_date = SYSDATE,
1955 wppb.last_updated_by = p_user_id,
1956 wppb.last_update_login = p_login_id,
1957 wppb.request_id = p_request_id,
1958 wppb.program_application_id = p_prog_app_id,
1959 wppb.program_id = p_prog_id,
1960 wppb.program_update_date = SYSDATE
1961 WHERE wppb.cost_group_id = p_cost_group_id
1962 AND wppb.pac_period_id = p_pac_period_id
1963 AND wppb.cost_type_id = p_cost_type_id
1964 AND wppb.wip_entity_id = p_entity_id
1965 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
1966
1967 -- Statement level log message for FND logging
1968 IF (l_sLog) THEN
1969 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1970 l_module || '.'||l_stmt_num,
1971 ' Negative Value in Job of Cost element = 1');
1972 END IF;
1973 END IF;
1974
1975 IF (l_job_balance(2) < 0) THEN
1976
1977 l_stmt_num := 300;
1978 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
1979 SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
1980 cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
1981 cprocd.last_update_date = SYSDATE,
1982 cprocd.last_updated_by = p_user_id,
1983 cprocd.last_update_login = p_login_id,
1984 cprocd.request_id = p_request_id,
1985 cprocd.program_application_id = p_prog_app_id,
1986 cprocd.program_id = p_prog_id,
1987 cprocd.program_update_date = SYSDATE
1988 WHERE cprocd.cost_group_id = p_cost_group_id
1989 AND cprocd.pac_period_id = p_pac_period_id
1990 AND cprocd.wip_entity_id = p_entity_id
1991 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1992 AND cprocd.cost_element_id = 2; -- Material Overhead Cost Element
1993
1994 l_stmt_num := 310;
1995 UPDATE wip_pac_period_balances wppb
1996 -- New column to store the variance in case total job balance is negative
1997 SET wppb.pl_material_overhead_temp_var = wppb.pl_material_overhead_temp,
1998 -- This column is storing accumlated values of variance
1999 wppb.pl_material_overhead_var = nvl(wppb.pl_material_overhead_var,0) + nvl(wppb.pl_material_overhead_temp,0),
2000 --Subtract the variance column value from OUT column
2001 wppb.pl_material_overhead_out = wppb.pl_material_overhead_out - wppb.pl_material_overhead_temp,
2002 -- Make TEMP value to Zero
2003 wppb.pl_material_overhead_temp = 0,
2004 wppb.last_update_date = SYSDATE,
2005 wppb.last_updated_by = p_user_id,
2006 wppb.last_update_login = p_login_id,
2007 wppb.request_id = p_request_id,
2008 wppb.program_application_id = p_prog_app_id,
2009 wppb.program_id = p_prog_id,
2010 wppb.program_update_date = SYSDATE
2011 WHERE wppb.cost_group_id = p_cost_group_id
2012 AND wppb.pac_period_id = p_pac_period_id
2013 AND wppb.cost_type_id = p_cost_type_id
2014 AND wppb.wip_entity_id = p_entity_id
2015 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
2016
2017 -- Statement level log message for FND logging
2018 IF (l_sLog) THEN
2019 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
2020 l_module || '.'||l_stmt_num,
2021 ' Negative Value in Job for Cost element = 2');
2022 END IF;
2023 END IF;
2024
2025 IF (l_job_balance(3) < 0) THEN
2026
2027 l_stmt_num := 320;
2028 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
2029 SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
2030 cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
2031 cprocd.last_update_date = SYSDATE,
2032 cprocd.last_updated_by = p_user_id,
2033 cprocd.last_update_login = p_login_id,
2034 cprocd.request_id = p_request_id,
2035 cprocd.program_application_id = p_prog_app_id,
2036 cprocd.program_id = p_prog_id,
2037 cprocd.program_update_date = SYSDATE
2038 WHERE cprocd.cost_group_id = p_cost_group_id
2039 AND cprocd.pac_period_id = p_pac_period_id
2040 AND cprocd.wip_entity_id = p_entity_id
2041 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2042 AND cprocd.cost_element_id = 3; -- Resource Cost Element
2043
2044 l_stmt_num := 330;
2045 UPDATE wip_pac_period_balances wppb
2046 -- New column to store the variance in case if total job balance is negative
2047 SET wppb.pl_resource_temp_var = wppb.pl_resource_temp,
2048 -- This column is storing accumlated values of variance
2049 wppb.pl_resource_var = nvl(wppb.pl_resource_var,0) + nvl(wppb.pl_resource_temp,0),
2050 --Subtract the variance column value from OUT column
2051 wppb.pl_resource_out = wppb.pl_resource_out - wppb.pl_resource_temp,
2052 -- Make TEMP value to Zero
2053 wppb.pl_resource_temp = 0,
2054 wppb.last_update_date = SYSDATE,
2055 wppb.last_updated_by = p_user_id,
2056 wppb.last_update_login = p_login_id,
2057 wppb.request_id = p_request_id,
2058 wppb.program_application_id = p_prog_app_id,
2059 wppb.program_id = p_prog_id,
2060 wppb.program_update_date = SYSDATE
2061 WHERE wppb.cost_group_id = p_cost_group_id
2062 AND wppb.pac_period_id = p_pac_period_id
2063 AND wppb.cost_type_id = p_cost_type_id
2064 AND wppb.wip_entity_id = p_entity_id
2065 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
2066
2067 -- Statement level log message for FND logging
2068 IF (l_sLog) THEN
2069 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
2070 l_module || '.'||l_stmt_num,
2071 ' Negative Value in Job for Cost element = 3');
2072 END IF;
2073 END IF;
2074
2075 IF (l_job_balance(4) < 0) THEN
2076
2077 l_stmt_num := 340;
2078 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
2079 SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
2080 cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
2081 cprocd.last_update_date = SYSDATE,
2082 cprocd.last_updated_by = p_user_id,
2083 cprocd.last_update_login = p_login_id,
2084 cprocd.request_id = p_request_id,
2085 cprocd.program_application_id = p_prog_app_id,
2086 cprocd.program_id = p_prog_id,
2087 cprocd.program_update_date = SYSDATE
2088 WHERE cprocd.cost_group_id = p_cost_group_id
2089 AND cprocd.pac_period_id = p_pac_period_id
2090 AND cprocd.wip_entity_id = p_entity_id
2091 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2092 AND cprocd.cost_element_id = 4; -- Outside Processing Cost Element
2093
2094 l_stmt_num := 350;
2095 UPDATE wip_pac_period_balances wppb
2096 -- New column to store the varaince in case total job balance is negative
2097 SET wppb.pl_outside_processing_temp_var = wppb.pl_outside_processing_temp,
2098 -- This column is storing accumlated values of variance
2099 wppb.pl_outside_processing_var = nvl(wppb.pl_outside_processing_var,0) + nvl(wppb.pl_outside_processing_temp,0),
2100 --Subtract the variance column value from OUT column
2101 wppb.pl_outside_processing_out = wppb.pl_outside_processing_out - wppb.pl_outside_processing_temp,
2102 -- Make TEMP value to Zero
2103 wppb.pl_outside_processing_temp = 0,
2104 wppb.last_update_date = SYSDATE,
2105 wppb.last_updated_by = p_user_id,
2106 wppb.last_update_login = p_login_id,
2107 wppb.request_id = p_request_id,
2108 wppb.program_application_id = p_prog_app_id,
2109 wppb.program_id = p_prog_id,
2110 wppb.program_update_date = SYSDATE
2111 WHERE wppb.cost_group_id = p_cost_group_id
2112 AND wppb.pac_period_id = p_pac_period_id
2113 AND wppb.cost_type_id = p_cost_type_id
2114 AND wppb.wip_entity_id = p_entity_id
2115 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
2116
2117 -- Statement level log message for FND logging
2118 IF (l_sLog) THEN
2119 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
2120 l_module || '.'||l_stmt_num,
2121 ' Negative Value in Job for Cost element = 4');
2122 END IF;
2123 END IF;
2124
2125 IF (l_job_balance(5) < 0) THEN
2126
2127 l_stmt_num := 360;
2128 UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
2129 SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
2130 cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
2131 cprocd.last_update_date = SYSDATE,
2132 cprocd.last_updated_by = p_user_id,
2133 cprocd.last_update_login = p_login_id,
2134 cprocd.request_id = p_request_id,
2135 cprocd.program_application_id = p_prog_app_id,
2136 cprocd.program_id = p_prog_id,
2137 cprocd.program_update_date = SYSDATE
2138 WHERE cprocd.cost_group_id = p_cost_group_id
2139 AND cprocd.pac_period_id = p_pac_period_id
2140 AND cprocd.wip_entity_id = p_entity_id
2141 AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2142 AND cprocd.cost_element_id = 5; -- Overhead Processing Cost Element
2143
2144 l_stmt_num := 370;
2145 UPDATE wip_pac_period_balances wppb
2146 -- New column to store the varaince in case total job balance is negative
2147 SET wppb.pl_overhead_temp_var = wppb.pl_overhead_temp,
2148 -- This column is storing accumlated values of variance
2149 wppb.pl_overhead_var = nvl(wppb.pl_overhead_var,0) + nvl(wppb.pl_overhead_temp,0),
2150 --Subtract the variance column value from OUT column
2151 wppb.pl_overhead_out = wppb.pl_overhead_out - wppb.pl_overhead_temp,
2152 -- Make TEMP value to Zero
2153 wppb.pl_overhead_temp = 0,
2154 wppb.last_update_date = SYSDATE,
2155 wppb.last_updated_by = p_user_id,
2156 wppb.last_update_login = p_login_id,
2157 wppb.request_id = p_request_id,
2158 wppb.program_application_id = p_prog_app_id,
2159 wppb.program_id = p_prog_id,
2160 wppb.program_update_date = SYSDATE
2161 WHERE wppb.cost_group_id = p_cost_group_id
2162 AND wppb.pac_period_id = p_pac_period_id
2163 AND wppb.cost_type_id = p_cost_type_id
2164 AND wppb.wip_entity_id = p_entity_id
2165 AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
2166
2167 -- Statement level log message for FND logging
2168 IF (l_sLog) THEN
2169 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
2170 l_module || '.'||l_stmt_num,
2171 ' Negative Value in Job for Cost element = 5');
2172 END IF;
2173 END IF;
2174
2175 IF (l_pLog) THEN
2176 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2177 l_module || '.end',
2178 l_api_name || ' >>>');
2179 END IF;
2180
2181 EXCEPTION
2182 WHEN OTHERS THEN
2183 fnd_file.put_line(fnd_file.log,' Exception '||to_char(l_stmt_num));
2184 IF (l_uLog) THEN
2185 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
2186 l_module || '.' || l_stmt_num,
2187 SQLERRM);
2188 END IF;
2189 ROLLBACK;
2190 x_err_num := SQLCODE;
2191 x_err_code := NULL;
2192 x_err_msg := SUBSTR('CSTPPWAS.Relief_BOM_Quantity('
2193 || to_char(l_stmt_num)
2194 || '): '
2195 ||SQLERRM,1,240);
2196 END Relief_BOM_Quantity;
2197
2198 /*---------------------------------------------------------------------------*
2199 | PRIVATE PROCEDURE |
2200 | process_net_relief |
2201 *----------------------------------------------------------------------------*/
2202 PROCEDURE process_net_relief (
2203 p_pac_period_id IN NUMBER,
2204 p_prior_period_id IN NUMBER,
2205 p_cost_group_id IN NUMBER,
2206 p_cost_type_id IN NUMBER,
2207 p_legal_entity IN NUMBER,
2208 p_cost_method IN NUMBER,
2209 p_cost_layer_id IN NUMBER,
2210 p_qty_layer_id IN NUMBER,
2211 p_pac_rates_id IN NUMBER,
2212 p_entity_type IN NUMBER,
2213 p_org_id IN NUMBER,
2214 p_entity_id IN NUMBER,
2215 p_final_completion_flag IN NUMBER,
2216 p_material_relief_algorithm IN NUMBER,
2217 p_line_id IN NUMBER DEFAULT NULL,
2218 p_net_qty IN NUMBER,
2219 p_start_date IN DATE,
2220 p_end_date IN DATE,
2221 p_scrap IN NUMBER DEFAULT -1,
2222 p_op_seq IN NUMBER DEFAULT NULL,
2223 p_master_org_id IN NUMBER,
2224 p_uom_control IN NUMBER,
2225 p_user_id IN NUMBER,
2226 p_login_id IN NUMBER,
2227 p_request_id IN NUMBER,
2228 p_prog_id IN NUMBER DEFAULT -1,
2229 p_prog_app_id IN NUMBER DEFAULT -1,
2230 p_txn_category IN NUMBER,
2231 x_err_num OUT NOCOPY NUMBER,
2232 x_err_code OUT NOCOPY VARCHAR2,
2233 x_err_msg OUT NOCOPY VARCHAR2)
2234 IS
2235 CURSOR c_scrap_txn IS
2236 SELECT mmt.transaction_id txn_id,
2237 mmt.inventory_item_id item_id,
2238 mmt.primary_quantity pri_qty,
2239 mmt.organization_id org_id,
2240 mmt.subinventory_code subinv,
2241 mmt.transaction_action_id txn_action_id,
2242 mmt.transaction_source_type_id txn_src_type_id
2243 FROM mtl_material_transactions mmt
2244 WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
2245 AND (TRUNC(p_end_date) + 0.99999)
2246 AND mmt.transaction_source_type_id = 5
2247 AND mmt.transaction_source_id = p_entity_id
2248 AND NVL(mmt.repetitive_line_id,-99) = NVL(p_line_id,-99)
2249 AND mmt.transaction_action_id = 30
2250 AND NVL(mmt.operation_seq_num, -1) = NVL(p_op_seq,-1)
2251 ORDER BY mmt.primary_quantity DESC, mmt.transaction_id; -- minimize the occurences of negative periodic inventory quantity
2252
2253 CURSOR c_assy_txn IS
2254 SELECT mmt.transaction_id txn_id,
2255 mmt.inventory_item_id item_id,
2256 mmt.primary_quantity pri_qty,
2257 mmt.organization_id org_id,
2258 mmt.subinventory_code subinv,
2259 mmt.transaction_action_id txn_action_id,
2260 mmt.transaction_source_type_id txn_src_type_id
2261 FROM mtl_material_transactions mmt
2262 WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
2263 AND (TRUNC(p_end_date) + 0.99999)
2264 AND mmt.transaction_source_type_id = 5
2265 AND mmt.transaction_source_id = p_entity_id
2266 AND NVL(mmt.repetitive_line_id,-99) = NVL(p_line_id,-99)
2267 AND mmt.transaction_action_id IN (31,32)
2268 ORDER BY mmt.transaction_action_id, mmt.transaction_id; -- minimize the occurences of negative periodic inventory quantity
2269
2270 l_uom_conv_rate NUMBER;
2271 l_conv_net_qty NUMBER;
2272 l_item_id NUMBER;
2273 l_org_id NUMBER;
2274 l_stmt_num NUMBER;
2275 l_err_num NUMBER;
2276 l_err_code VARCHAR2(240);
2277 l_err_msg VARCHAR2(240);
2278 l_exp_item NUMBER;
2279 l_exp_flag NUMBER;
2280 l_wip_assy_hook NUMBER;
2281 cst_process_error EXCEPTION;
2282 /* Added new local variables for R12 PAC enhancements */
2283 l_assembly_return_cnt NUMBER;
2284 l_net_qty NUMBER;
2285 l_completed_assembly_qty NUMBER;
2286 l_net_completion NUMBER;
2287
2288 l_api_name CONSTANT VARCHAR2(30) := 'process_net_relief';
2289 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2290 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
2291
2292 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
2293 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2294 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2295 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2296 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2297 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2298
2299 BEGIN
2300
2301 IF (l_pLog) THEN
2302 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2303 l_module || '.begin',
2304 l_api_name || ' <<<');
2305
2306 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2307 l_module || '.begin',
2308 l_api_name || ' <<< Parameters:
2309 p_cost_layer_id = ' || p_cost_layer_id || '
2310 p_qty_layer_id = ' || p_qty_layer_id || '
2311 p_entity_type = ' || p_entity_type || '
2312 p_org_id = ' || p_org_id || '
2313 p_entity_id = ' || p_entity_id ||'
2314 p_final_completion_flag = ' || p_final_completion_flag ||'
2315 p_material_relief_algorithm = ' || p_material_relief_algorithm ||'
2316 p_line_id = ' || p_line_id ||'
2317 p_net_qty = ' || p_net_qty ||'
2318 p_scrap = ' || p_scrap ||'
2319 p_op_seq = ' || p_op_seq ||'
2320 p_txn_category = ' || p_txn_category);
2321 END IF;
2322 ----------------------------------------------------------------------
2323 -- Initialize Variables
2324 ----------------------------------------------------------------------
2325
2326 l_err_num := 0;
2327 l_err_code := '';
2328 l_err_msg := '';
2329 l_wip_assy_hook := -1;
2330
2331 IF (p_scrap = 1 AND p_entity_type <> 4) THEN
2332
2333 SELECT SUM(mmt.primary_quantity)
2334 INTO l_net_completion
2335 FROM mtl_material_transactions mmt
2336 WHERE mmt.transaction_source_id = p_entity_id
2337 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
2338 AND (TRUNC(p_end_date) + 0.99999)
2339 AND mmt.transaction_source_type_id = 5
2340 AND mmt.transaction_action_id IN (31,32);
2341
2342 END IF;
2343
2344 ----------------------------------------------------------------------
2345 -- This proc will be called for those entities/line that have a record
2346 -- in wppb.
2347 -- There is no need to check/create Create WIP BAL Rec as such
2348 -- a record will always exist when this proc is called.
2349 -- This is becasue a completion/scrap/return always required
2350 -- a preceding move transactions. The move transactions
2351 -- will create the WIPBAL record in the process_wipresovhd
2352 -- procedure. For CFMs, the rec will be created while processing
2353 -- the res_ovhds or material component issue txns.
2354 ----------------------------------------------------------------------
2355
2356
2357
2358 ----------------------------------------------------------------------
2359 -- Reset the Temp columns
2360 ----------------------------------------------------------------------
2361
2362 l_stmt_num := 0;
2363
2364 UPDATE wip_pac_period_balances wppb
2365 SET tl_resource_temp = 0,
2366 tl_overhead_temp = 0,
2367 tl_outside_processing_temp = 0,
2368 pl_material_temp = 0,
2369 pl_material_overhead_temp = 0,
2370 pl_resource_temp = 0,
2371 pl_outside_processing_temp = 0,
2372 pl_overhead_temp = 0
2373 WHERE wppb.pac_period_id = p_pac_period_id
2374 AND wppb.cost_group_id = p_cost_group_id
2375 AND wppb.wip_entity_id = p_entity_id
2376 AND NVL(wppb.line_id,-99) = decode(p_entity_type, 4, -99, NVL(p_line_id,-99));
2377
2378 ----------------------------------------------------------------------
2379 -- Relieve Costs
2380 ----------------------------------------------------------------------
2381
2382
2383 --------------------------------------------------------------------
2384 -- Check whether CFM completion/return/scrap.
2385 -- For non-scheduled CFMs, Each transaction will be Unique
2386 -- WIP entity.
2387 -- For CFMs, We do not distinguish between net completion and return.
2388 -- The Variance column has no meaning for CFM entities.
2389 -- Flush/Relieve all costs from the entity as:
2390 -- IN-OUT-VAR
2391 --------------------------------------------------------------------
2392
2393 IF (p_entity_type = 4) THEN
2394
2395 l_stmt_num := 10;
2396
2397 UPDATE wip_pac_period_balances wppb
2398 SET (tl_resource_out,
2399 tl_resource_temp,
2400 tl_outside_processing_out,
2401 tl_outside_processing_temp,
2402 tl_overhead_out,
2403 tl_overhead_temp,
2404 pl_material_out,
2405 pl_material_temp,
2406 pl_material_overhead_out,
2407 pl_material_overhead_temp,
2408 pl_resource_out,
2409 pl_resource_temp,
2410 pl_outside_processing_out,
2411 pl_outside_processing_temp,
2412 pl_overhead_out,
2413 pl_overhead_temp
2414 ) =
2415 (SELECT
2416 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2417 -NVL(wppb.tl_resource_var,0)),
2418 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2419 -NVL(wppb.tl_resource_var,0)),
2420 (NVL(wppb.tl_outside_processing_in,0)
2421 - NVL(wppb.tl_outside_processing_out,0)
2422 - NVL(wppb.tl_outside_processing_var,0)),
2423 (NVL(wppb.tl_outside_processing_in,0)
2424 - NVL(wppb.tl_outside_processing_out,0)
2425 - NVL(wppb.tl_outside_processing_var,0)),
2426 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2427 - NVL(wppb.tl_overhead_var,0)),
2428 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2429 - NVL(wppb.tl_overhead_var,0)),
2430 (NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
2431 - NVL(wppb.pl_material_var,0)),
2432 (NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
2433 - NVL(wppb.pl_material_var,0)),
2434 (NVL(wppb.pl_material_overhead_in,0)
2435 - NVL(wppb.pl_material_overhead_out,0)
2436 - NVL(wppb.pl_material_overhead_var,0)),
2437 (NVL(wppb.pl_material_overhead_in,0)
2438 - NVL(wppb.pl_material_overhead_out,0)
2439 - NVL(wppb.pl_material_overhead_var,0)),
2440 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
2441 - NVL(wppb.pl_resource_var,0)),
2442 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
2443 - NVL(wppb.pl_resource_var,0)),
2444 (NVL(wppb.pl_outside_processing_in,0)
2445 - NVL(wppb.pl_outside_processing_out,0)
2446 - NVL(wppb.pl_outside_processing_var,0)),
2447 (NVL(wppb.pl_outside_processing_in,0)
2448 - NVL(wppb.pl_outside_processing_out,0)
2449 - NVL(wppb.pl_outside_processing_var,0)),
2450 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
2451 - NVL(wppb.pl_overhead_var,0)),
2452 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
2453 - NVL(wppb.pl_overhead_var,0))
2454 FROM wip_pac_period_balances wppb2,
2455 wip_flow_schedules wfs
2456 WHERE wppb2.pac_period_id = p_pac_period_id
2457 AND wppb2.cost_group_id = p_cost_group_id
2458 AND wppb2.wip_entity_id = p_entity_id
2459 AND wppb2.operation_seq_num = wppb.operation_seq_num
2460 AND wfs.wip_entity_id = p_entity_id
2461 )
2462 WHERE wppb.pac_period_id = p_pac_period_id
2463 AND wppb.cost_group_id = p_cost_group_id
2464 AND wppb.wip_entity_id = p_entity_id
2465 AND wppb.operation_seq_num <=
2466 decode(p_scrap,1,nvl(p_op_seq,wppb.operation_seq_num),wppb.operation_seq_num);
2467 ELSE -- If not CFM completion/return
2468 /* material Completion Algo is based on BOM */
2469 /* This algo relieves the material costs based on the BOM */
2470 l_stmt_num := 20;
2471
2472 IF ( p_material_relief_algorithm = 0 ) THEN
2473 Relief_BOM_Quantity(p_pac_period_id => p_pac_period_id,
2474 p_prior_period_id => p_prior_period_id,
2475 p_cost_group_id => p_cost_group_id ,
2476 p_cost_type_id => p_cost_type_id,
2477 p_entity_id => p_entity_id,
2478 p_line_id => p_line_id,
2479 p_net_qty => p_net_qty,
2480 p_final_completion_flag => p_final_completion_flag,
2481 p_scrap => p_scrap,
2482 p_op_seq => p_op_seq,
2483 p_start_date => p_start_date,
2484 p_end_date => p_end_date,
2485 p_login_id => p_login_id,
2486 p_user_id => p_user_id,
2487 p_request_id => p_request_id,
2488 p_prog_id => p_prog_id,
2489 p_prog_app_id => p_prog_app_id,
2490 x_err_num => x_err_num,
2491 x_err_code => x_err_code,
2492 x_err_msg => x_err_msg );
2493 /* Calculation TL resource, Overhead and OSP */
2494 /* They always will be relieved based on actuals */
2495 IF (p_net_qty > 0) THEN
2496 l_stmt_num := 25;
2497 /* Final completion exists */
2498 IF p_final_completion_flag = 1 THEN
2499
2500 UPDATE wip_pac_period_balances wppb
2501 SET (tl_resource_out,
2502 tl_resource_temp,
2503 tl_outside_processing_out,
2504 tl_outside_processing_temp,
2505 tl_overhead_out,
2506 tl_overhead_temp
2507 ) =
2508 (SELECT
2509 NVL(wppb.tl_resource_out,0) +
2510 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2511 - NVL(tl_resource_var,0)),
2512 1,(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2513 - NVL(tl_resource_var,0)),
2514 0
2515 ),
2516 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2517 - NVL(tl_resource_var,0)),
2518 1,
2519 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2520 - NVL(tl_resource_var,0)),
2521 0
2522 ),
2523 NVL(tl_outside_processing_out,0) +
2524 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2525 - NVL(wppb.tl_outside_processing_out,0)
2526 - NVL(wppb.tl_outside_processing_var,0)),
2527 1,
2528 (NVL(wppb.tl_outside_processing_in,0)
2529 - NVL(wppb.tl_outside_processing_out,0)
2530 - NVL(wppb.tl_outside_processing_var,0)),
2531 0
2532 ),
2533 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2534 - NVL(wppb.tl_outside_processing_out,0)
2535 - NVL(wppb.tl_outside_processing_var,0)),
2536 1,
2537 (NVL(wppb.tl_outside_processing_in,0)
2538 - NVL(wppb.tl_outside_processing_out,0)
2539 - NVL(wppb.tl_outside_processing_var,0)),
2540 0
2541 ),
2542 NVL(tl_overhead_out,0) +
2543 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2544 - NVL(wppb.tl_overhead_var,0)),
2545 1,
2546 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2547 - NVL(wppb.tl_overhead_var,0)),
2548 0
2549 ),
2550 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2551 - NVL(wppb.tl_overhead_var,0)),
2552 1,
2553 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2554 - NVL(wppb.tl_overhead_var,0)),
2555 0
2556 )
2557 FROM wip_pac_period_balances wppb2
2558 WHERE wppb2.pac_period_id =p_pac_period_id
2559 AND wppb2.cost_group_id = p_cost_group_id
2560 AND wppb2.wip_entity_id = p_entity_id
2561 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2562 AND wppb2.operation_seq_num = wppb.operation_seq_num
2563 )
2564 WHERE wppb.pac_period_id = p_pac_period_id
2565 AND wppb.cost_group_id = p_cost_group_id
2566 AND wppb.wip_entity_id = p_entity_id
2567 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2568 AND wppb.operation_seq_num <= wppb.operation_seq_num;
2569 ELSE /* No Final completion exists */
2570 UPDATE wip_pac_period_balances wppb
2571 SET (tl_resource_out,
2572 tl_resource_temp,
2573 tl_outside_processing_out,
2574 tl_outside_processing_temp,
2575 tl_overhead_out,
2576 tl_overhead_temp
2577 ) =
2578 (SELECT
2579 NVL(wppb.tl_resource_out,0) +
2580 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2581 - NVL(tl_resource_var,0)),
2582 1,
2583 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2584 - NVL(tl_resource_var,0))*
2585 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2586 NVL(wppb2.relieved_assembly_units,0) - nvl(unrelieved_scrap_quantity,0)),
2587 0, 1,
2588 -1, 1,
2589 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2590 NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
2591 ),
2592 0
2593 ),
2594 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2595 - NVL(tl_resource_var,0)),
2596 1,
2597 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2598 - NVL(tl_resource_var,0))*
2599 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2600 NVL(wppb2.relieved_assembly_units,0) -
2601 nvl(unrelieved_scrap_quantity,0)),
2602 0, 1,
2603 -1, 1,
2604 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2605 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
2606 ),
2607 0
2608 ),
2609 NVL(tl_outside_processing_out,0) +
2610 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2611 - NVL(wppb.tl_outside_processing_out,0)
2612 - NVL(wppb.tl_outside_processing_var,0)),
2613 1,
2614 (NVL(wppb.tl_outside_processing_in,0)
2615 - NVL(wppb.tl_outside_processing_out,0)
2616 - NVL(wppb.tl_outside_processing_var,0))*
2617 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2618 NVL(wppb2.relieved_assembly_units,0) -
2619 NVL(wppb2.unrelieved_scrap_quantity,0)),
2620 0, 1,
2621 -1, 1,
2622 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2623 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
2624 ),
2625 0
2626 ),
2627 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2628 - NVL(wppb.tl_outside_processing_out,0)
2629 - NVL(wppb.tl_outside_processing_var,0)),
2630 1,
2631 (NVL(wppb.tl_outside_processing_in,0)
2632 - NVL(wppb.tl_outside_processing_out,0)
2633 - NVL(wppb.tl_outside_processing_var,0))*
2634 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2635 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
2636 0, 1,
2637 -1, 1,
2638 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2639 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
2640 ),
2641 0
2642 ),
2643 NVL(tl_overhead_out,0) +
2644 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2645 - NVL(wppb.tl_overhead_var,0)),
2646 1,
2647 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2648 - NVL(wppb.tl_overhead_var,0))*
2649 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2650 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
2651 0, 1,
2652 -1, 1,
2653 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2654 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
2655 ),
2656 0
2657 ),
2658 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2659 - NVL(wppb.tl_overhead_var,0)),
2660 1,
2661 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2662 - NVL(wppb.tl_overhead_var,0))*
2663 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
2664 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
2665 0, 1,
2666 -1, 1,
2667 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
2668 NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
2669 ),
2670 0
2671 )
2672 FROM wip_pac_period_balances wppb2
2673 WHERE wppb2.pac_period_id = p_pac_period_id
2674 AND wppb2.cost_group_id = p_cost_group_id
2675 AND wppb2.wip_entity_id = p_entity_id
2676 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2677 AND wppb2.operation_seq_num = wppb.operation_seq_num
2678 )
2679 WHERE wppb.pac_period_id = p_pac_period_id
2680 AND wppb.cost_group_id = p_cost_group_id
2681 AND wppb.wip_entity_id = p_entity_id
2682 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2683 AND wppb.operation_seq_num <=
2684 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2685 END IF;
2686 ELSIF (p_net_qty < 0) THEN
2687 l_stmt_num := 28;
2688 UPDATE wip_pac_period_balances wppb
2689 SET (tl_resource_out,
2690 tl_resource_temp,
2691 tl_outside_processing_out,
2692 tl_outside_processing_temp,
2693 tl_overhead_out,
2694 tl_overhead_temp) =
2695 (SELECT
2696 NVL(wppb.tl_resource_out,0) +
2697 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
2698 1,
2699 NVL(wppb2.tl_resource_out,0) *
2700 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2701 0,1,wppb2.relieved_assembly_units),
2702 0),
2703 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
2704 1,
2705 NVL(wppb2.tl_resource_out,0) *
2706 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2707 0,1,wppb2.relieved_assembly_units),
2708 0),
2709 NVL(wppb.tl_outside_processing_out,0) +
2710 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
2711 1,
2712 NVL(wppb2.tl_outside_processing_in,0)*
2713 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2714 0,1,wppb2.relieved_assembly_units),
2715 0),
2716 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
2717 1,
2718 NVL(wppb2.tl_outside_processing_in,0)*
2719 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2720 0,1,wppb2.relieved_assembly_units),
2721 0),
2722 NVL(wppb.tl_overhead_out,0) +
2723 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
2724 1,
2725 NVL(wppb2.tl_overhead_out,0) *
2726 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2727 0,1,wppb2.relieved_assembly_units),
2728 0),
2729 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
2730 1,
2731 NVL(wppb2.tl_overhead_out,0) *
2732 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2733 0,1,wppb2.relieved_assembly_units),
2734 0)
2735 FROM wip_pac_period_balances wppb2
2736 WHERE wppb2.pac_period_id = p_prior_period_id
2737 AND wppb2.cost_group_id = p_cost_group_id
2738 AND wppb2.wip_entity_id = p_entity_id
2739 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2740 AND wppb2.operation_seq_num = wppb.operation_seq_num
2741 )
2742 WHERE wppb.pac_period_id = p_pac_period_id
2743 AND wppb.cost_group_id = p_cost_group_id
2744 AND wppb.wip_entity_id = p_entity_id
2745 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2746 AND wppb.operation_seq_num <=
2747 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2748 ELSE /* p_net_qty=0 */
2749 /* Final Completion, Flush out everything from the Job for TL columns */
2750 l_stmt_num := 30;
2751 IF p_final_completion_flag = 1 THEN
2752
2753 SELECT sum(primary_quantity)
2754 INTO l_assembly_return_cnt
2755 FROM mtl_material_transactions mmt
2756 WHERE mmt.transaction_source_id = p_entity_id
2757 AND mmt.transaction_action_id = 32
2758 AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
2759 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
2760 AND (TRUNC(p_end_date) + 0.99999);
2761 -- If this period is not the first period
2762 IF p_prior_period_id <> -1 then
2763
2764 l_stmt_num := 32;
2765 UPDATE wip_pac_period_balances wppb
2766 SET (tl_resource_out,
2767 tl_resource_temp,
2768 tl_outside_processing_out,
2769 tl_outside_processing_temp,
2770 tl_overhead_out,
2771 tl_overhead_temp
2772 ) =
2773 (SELECT
2774 NVL(wppb.tl_resource_out,0) +
2775 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
2776 1,
2777 NVL(wppb2.tl_resource_out,0) *
2778 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2779 0,1,wppb2.relieved_assembly_units),
2780 0),
2781 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
2782 1, NVL(wppb2.tl_resource_out,0) *
2783 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2784 0,1,wppb2.relieved_assembly_units),
2785 0),
2786 NVL(wppb.tl_outside_processing_out,0) +
2787 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
2788 1,
2789 NVL(wppb2.tl_outside_processing_in,0)*
2790 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2791 0,1,wppb2.relieved_assembly_units),
2792 0),
2793 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
2794 1,
2795 NVL(wppb2.tl_outside_processing_in,0)*
2796 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2797 0,1,wppb2.relieved_assembly_units),
2798 0),
2799 NVL(wppb.tl_overhead_out,0) +
2800 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
2801 1,
2802 NVL(wppb2.tl_overhead_out,0) *
2803 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2804 0,1,wppb2.relieved_assembly_units),
2805 0),
2806 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
2807 1,
2808 NVL(wppb2.tl_overhead_out,0) *
2809 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2810 0,1,wppb2.relieved_assembly_units),
2811 0)
2812 FROM wip_pac_period_balances wppb2
2813 WHERE wppb2.pac_period_id = p_prior_period_id
2814 AND wppb2.cost_group_id = p_cost_group_id
2815 AND wppb2.wip_entity_id = p_entity_id
2816 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2817 AND wppb2.operation_seq_num = wppb.operation_seq_num
2818 )
2819 WHERE
2820 wppb.pac_period_id = p_pac_period_id
2821 AND wppb.cost_group_id = p_cost_group_id
2822 AND wppb.wip_entity_id = p_entity_id
2823 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2824 AND wppb.operation_seq_num <=
2825 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2826
2827 END IF; /* p_final_completion_flag = 1 */
2828 l_stmt_num := 34;
2829 UPDATE wip_pac_period_balances wppb
2830 SET (tl_resource_out,
2831 tl_resource_temp,
2832 tl_outside_processing_out,
2833 tl_outside_processing_temp,
2834 tl_overhead_out,
2835 tl_overhead_temp
2836 ) =
2837 (SELECT
2838 NVL(wppb.tl_resource_out,0) +
2839 decode(SIGN(NVL(wppb2.tl_resource_in,0) - NVL(wppb2.tl_resource_out,0)
2840 - NVL(wppb2.tl_resource_var,0)),
2841 1,
2842 (NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
2843 - NVL(wppb2.tl_resource_var,0)),
2844 0
2845 ),
2846 decode(SIGN(NVL(wppb2.tl_resource_in,0)- NVL(wppb2.tl_resource_out,0)
2847 - NVL(wppb2.tl_resource_var,0)),
2848 1,
2849 (NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
2850 - NVL(wppb2.tl_resource_var,0))
2851 ,0
2852 ),
2853 NVL(wppb.tl_outside_processing_out,0) +
2854 decode(SIGN(NVL(wppb2.tl_outside_processing_in,0)
2855 - NVL(wppb2.tl_outside_processing_out,0)
2856 - NVL(wppb2.tl_outside_processing_var,0)),
2857 1,
2858 (NVL(wppb2.tl_outside_processing_in,0)
2859 - NVL(wppb2.tl_outside_processing_out,0)
2860 - NVL(wppb2.tl_outside_processing_var,0))
2861 ,0
2862 ),
2863 decode(SIGN(NVL(wppb2.tl_outside_processing_in,0)
2864 - NVL(wppb2.tl_outside_processing_out,0)
2865 - NVL(wppb2.tl_outside_processing_var,0)),
2866 1,
2867 (NVL(wppb2.tl_outside_processing_in,0)
2868 - NVL(wppb2.tl_outside_processing_out,0)
2869 - NVL(wppb2.tl_outside_processing_var,0)),
2870 0
2871 ),
2872 NVL(tl_overhead_out,0) +
2873 decode(SIGN(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
2874 - NVL(wppb2.tl_overhead_var,0)),
2875 1,
2876 (NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
2877 - NVL(wppb2.tl_overhead_var,0)),
2878 0
2879 ),
2880 decode(SIGN(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
2881 - NVL(wppb2.tl_overhead_var,0)),
2882 1,
2883 (NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
2884 - NVL(wppb2.tl_overhead_var,0)),
2885 0
2886 )
2887 FROM wip_pac_period_balances wppb2
2888 WHERE wppb2.pac_period_id = p_pac_period_id
2889 AND wppb2.cost_group_id = p_cost_group_id
2890 AND wppb2.wip_entity_id = p_entity_id
2891 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2892 AND wppb2.operation_seq_num = wppb.operation_seq_num
2893 )
2894 WHERE
2895 wppb.pac_period_id = p_pac_period_id
2896 AND wppb.cost_group_id = p_cost_group_id
2897 AND wppb.wip_entity_id = p_entity_id
2898 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2899 AND wppb.operation_seq_num <=
2900 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2901
2902 ELSE /* No Final Completion */
2903 l_stmt_num := 36;
2904 UPDATE wip_pac_period_balances wppb
2905 SET (
2906 tl_resource_temp,
2907 tl_outside_processing_temp,
2908 tl_overhead_temp
2909 )
2910 =
2911 ( SELECT DECODE(
2912 SIGN(NVL(tl_resource_out,0)),
2913 1,
2914 NVL(tl_resource_out,0) /
2915 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
2916 0
2917 ),
2918 DECODE(
2919 SIGN(NVL(tl_outside_processing_out,0)),
2920 1,
2921 NVL(tl_outside_processing_out,0) /
2922 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
2923 0
2924 ),
2925 DECODE(
2926 SIGN(NVL(tl_overhead_out,0)),
2927 1,
2928 NVL(tl_overhead_out,0) /
2929 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
2930 0
2931 )
2932 FROM wip_pac_period_balances
2933 WHERE pac_period_id = p_prior_period_id
2934 AND cost_group_id = p_cost_group_id
2935 AND wip_entity_id = p_entity_id
2936 AND NVL(line_id,-99) = NVL(p_line_id,-99)
2937 AND operation_seq_num = wppb.operation_seq_num
2938 )
2939 WHERE pac_period_id = p_pac_period_id
2940 AND cost_group_id = p_cost_group_id
2941 AND wip_entity_id = p_entity_id
2942 AND NVL(line_id,-99) = NVL(p_line_id,-99)
2943 AND operation_seq_num <= decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2944 END IF; /* Final Completion Check */
2945 END IF; /* End of P_net_qty */
2946 ELSE -- Actuals Logic
2947 l_stmt_num := 38;
2948 IF (p_net_qty > 0) THEN -- completion are more than returns
2949
2950 IF p_final_completion_flag = 1 THEN /* Final completion exists */
2951 l_stmt_num := 40;
2952 UPDATE wip_pac_period_balances wppb
2953 SET (tl_resource_out,
2954 tl_resource_temp,
2955 tl_outside_processing_out,
2956 tl_outside_processing_temp,
2957 tl_overhead_out,
2958 tl_overhead_temp,
2959 pl_material_out,
2960 pl_material_temp,
2961 pl_material_overhead_out,
2962 pl_material_overhead_temp,
2963 pl_resource_out,
2964 pl_resource_temp,
2965 pl_outside_processing_out,
2966 pl_outside_processing_temp,
2967 pl_overhead_out,
2968 pl_overhead_temp
2969 ) =
2970 (SELECT
2971 NVL(wppb.tl_resource_out,0) +
2972 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2973 - NVL(tl_resource_var,0)),
2974 1,
2975 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2976 - NVL(tl_resource_var,0)),
2977 0
2978 ),
2979 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2980 - NVL(tl_resource_var,0)),
2981 1,
2982 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
2983 - NVL(tl_resource_var,0)),
2984 0
2985 ),
2986 NVL(tl_outside_processing_out,0) +
2987 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2988 - NVL(wppb.tl_outside_processing_out,0)
2989 - NVL(wppb.tl_outside_processing_var,0)),
2990 1,
2991 (NVL(wppb.tl_outside_processing_in,0)
2992 - NVL(wppb.tl_outside_processing_out,0)
2993 - NVL(wppb.tl_outside_processing_var,0)),
2994 0
2995 ),
2996 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
2997 - NVL(wppb.tl_outside_processing_out,0)
2998 - NVL(wppb.tl_outside_processing_var,0)),
2999 1,
3000 (NVL(wppb.tl_outside_processing_in,0)
3001 - NVL(wppb.tl_outside_processing_out,0)
3002 - NVL(wppb.tl_outside_processing_var,0)),
3003 0
3004 ),
3005 NVL(tl_overhead_out,0) +
3006 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3007 - NVL(wppb.tl_overhead_var,0)),
3008 1,
3009 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3010 - NVL(wppb.tl_overhead_var,0)),
3011 0
3012 ),
3013 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3014 - NVL(wppb.tl_overhead_var,0)),
3015 1,
3016 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3017 - NVL(wppb.tl_overhead_var,0)),
3018 0
3019 ),
3020 NVL(pl_material_out,0) +
3021 decode(SIGN(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
3022 - NVL(wppb.pl_material_var,0)),
3023 1,
3024 (NVL(wppb.pl_material_in,0) - NVL(wppb.pl_material_out,0) - nvl(wppb.pl_material_in_apull,0)
3025 - NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
3026 0
3027 ),
3028 decode(SIGN(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
3029 - NVL(wppb.pl_material_var,0)),
3030 1,
3031 (NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)-nvl(wppb.pl_material_in_apull,0)
3032 - NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
3033 0
3034 ),
3035 NVL(pl_material_overhead_out,0) +
3036 decode(SIGN(NVL(wppb.pl_material_overhead_in,0)
3037 - NVL(wppb.pl_material_overhead_out,0)
3038 - NVL(wppb.pl_material_overhead_var,0)),
3039 1,
3040 (NVL(wppb.pl_material_overhead_in,0)
3041 - NVL(wppb.pl_material_overhead_out,0) - nvl(wppb.pl_material_overhead_in_apull,0)
3042 - NVL(wppb.pl_material_overhead_var,0)) + nvl(wppb.pl_material_overhead_in_apull,0),
3043 0
3044 ),
3045 decode(SIGN(NVL(wppb.pl_material_overhead_in,0)
3046 - NVL(wppb.pl_material_overhead_out,0)
3047 - NVL(wppb.pl_material_overhead_var,0)),
3048 1,
3049 (NVL(wppb.pl_material_overhead_in,0)
3050 - NVL(wppb.pl_material_overhead_out,0) - nvl(wppb.pl_material_overhead_in_apull,0)
3051 - NVL(wppb.pl_material_overhead_var,0)) + nvl(wppb.pl_material_overhead_in_apull,0),
3052 0
3053 ),
3054 NVL(pl_resource_out,0) +
3055 decode(SIGN(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
3056 - NVL(wppb.pl_resource_var,0)),
3057 1,
3058 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0) - nvl(wppb.pl_resource_in_apull,0)
3059 - NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
3060 0
3061 ),
3062 decode(SIGN(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
3063 - NVL(wppb.pl_resource_var,0)),
3064 1,
3065 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0) - nvl(wppb.pl_resource_in_apull,0)
3066 - NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
3067 0
3068 ),
3069 NVL(pl_outside_processing_out,0) +
3070 decode(SIGN(NVL(wppb.pl_outside_processing_in,0)
3071 - NVL(wppb.pl_outside_processing_out,0)
3072 - NVL(wppb.pl_outside_processing_var,0)),
3073 1,
3074 (NVL(wppb.pl_outside_processing_in,0)
3075 - NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
3076 - NVL(wppb.pl_outside_processing_var,0)) + nvl(wppb.pl_outside_processing_in_apull,0),
3077 0
3078 ),
3079 decode(SIGN(NVL(wppb.pl_outside_processing_in,0)
3080 - NVL(wppb.pl_outside_processing_out,0)
3081 - NVL(wppb.pl_outside_processing_var,0)),
3082 1,
3083 (NVL(wppb.pl_outside_processing_in,0)
3084 - NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
3085 - NVL(wppb.pl_outside_processing_var,0)) + nvl(wppb.pl_outside_processing_in_apull,0),
3086 0
3087 ),
3088 NVL(pl_overhead_out,0) +
3089 decode(SIGN(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
3090 - NVL(wppb.pl_overhead_var,0)),
3091 1,
3092 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
3093 - NVL(wppb.pl_overhead_var,0)) + nvl(wppb.pl_overhead_in_apull,0),
3094 0
3095 ),
3096 decode(SIGN(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
3097 - NVL(wppb.pl_overhead_var,0)),
3098 1,
3099 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
3100 - NVL(wppb.pl_overhead_var,0)) + nvl(wppb.pl_overhead_in_apull,0),
3101 0
3102 )
3103 FROM wip_pac_period_balances wppb2
3104 WHERE wppb2.pac_period_id = p_pac_period_id
3105 AND wppb2.cost_group_id = p_cost_group_id
3106 AND wppb2.wip_entity_id = p_entity_id
3107 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3108 AND wppb2.operation_seq_num = wppb.operation_seq_num
3109 )
3110 WHERE wppb.pac_period_id = p_pac_period_id
3111 AND wppb.cost_group_id = p_cost_group_id
3112 AND wppb.wip_entity_id = p_entity_id
3113 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3114 AND wppb.operation_seq_num <= wppb.operation_seq_num;
3115 ELSE /* No Final completion exists */
3116 l_stmt_num := 42;
3117 UPDATE wip_pac_period_balances wppb
3118 SET (tl_resource_out,
3119 tl_resource_temp,
3120 tl_outside_processing_out,
3121 tl_outside_processing_temp,
3122 tl_overhead_out,
3123 tl_overhead_temp,
3124 pl_material_out,
3125 pl_material_temp,
3126 scrap_pull_material, -- Added by Bug#4717026
3127 pl_material_overhead_out,
3128 pl_material_overhead_temp,
3129 scrap_pull_material_overhead, -- Added by Bug#4717026
3130 pl_resource_out,
3131 pl_resource_temp,
3132 scrap_pull_resource, -- Added by Bug#4717026
3133 pl_outside_processing_out,
3134 pl_outside_processing_temp,
3135 scrap_pull_outside_processing, -- Added by Bug#4717026
3136 pl_overhead_out,
3137 pl_overhead_temp,
3138 scrap_pull_overhead -- Added by Bug#4717026
3139 ) =
3140 (SELECT
3141 NVL(wppb.tl_resource_out,0) +
3142 decode(SIGN(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
3143 - NVL(wppb2.tl_resource_var,0)),
3144 1,
3145 (NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
3146 - NVL(wppb2.tl_resource_var,0))*
3147 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3148 NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0)),
3149 0, 1,
3150 -1, 1,
3151 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3152 NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
3153 ),
3154 0
3155 ),
3156 decode(SIGN(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
3157 - NVL(wppb2.tl_resource_var,0)),
3158 1,
3159 (NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
3160 - NVL(wppb2.tl_resource_var,0))*
3161 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3162 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3163 0, 1,
3164 -1, 1,
3165 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3166 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3167 ),
3168 0
3169 ),
3170 NVL(tl_outside_processing_out,0) +
3171 decode(SIGN(NVL(wppb2.tl_outside_processing_in,0)
3172 - NVL(wppb2.tl_outside_processing_out,0)
3173 - NVL(wppb2.tl_outside_processing_var,0)),
3174 1,
3175 (NVL(wppb2.tl_outside_processing_in,0)
3176 - NVL(wppb2.tl_outside_processing_out,0)
3177 - NVL(wppb2.tl_outside_processing_var,0))*
3178 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3179 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3180 0, 1,
3181 -1, 1,
3182 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3183 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3184 ),
3185 0
3186 ),
3187 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
3188 - NVL(wppb.tl_outside_processing_out,0)
3189 - NVL(wppb.tl_outside_processing_var,0)),
3190 1,
3191 (NVL(wppb2.tl_outside_processing_in,0)
3192 - NVL(wppb2.tl_outside_processing_out,0)
3193 - NVL(wppb2.tl_outside_processing_var,0))*
3194 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3195 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3196 0, 1,
3197 -1, 1,
3198 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3199 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3200 ),
3201 0
3202 ),
3203 NVL(tl_overhead_out,0) +
3204 decode(SIGN(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
3205 - NVL(wppb2.tl_overhead_var,0)),
3206 1,
3207 (NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
3208 - NVL(wppb2.tl_overhead_var,0))*
3209 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3210 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3211 0, 1,
3212 -1, 1,
3213 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3214 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3215 ),
3216 0
3217 ),
3218 decode(SIGN(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
3219 - NVL(wppb2.tl_overhead_var,0)),
3220 1,
3221 (NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
3222 - NVL(wppb2.tl_overhead_var,0))*
3223 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3224 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3225 0, 1,
3226 -1, 1,
3227 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3228 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3229 ),
3230 0
3231 ),
3232 NVL(pl_material_out,0) +
3233 decode(SIGN(NVL(wppb2.pl_material_in,0)-NVL(wppb2.pl_material_out,0)
3234 - NVL(wppb2.pl_material_var,0)),
3235 1,
3236 (NVL(wppb2.pl_material_in,0)-NVL(wppb2.pl_material_out,0)- nvl(wppb2.pl_material_in_apull,0)
3237 - NVL(wppb2.pl_material_var,0)+ nvl(wppb.scrap_pull_material,0))*
3238 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3239 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3240 0, 1,
3241 -1, 1,
3242 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3243 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3244 ) + decode(p_scrap,1, p_net_qty * nvl(wppb.pl_material_in_apull,0)/(wppb.relieved_scrap_qty + nvl(l_net_completion,0)),
3245 nvl(wppb.pl_material_in_apull,0) - nvl(wppb.scrap_pull_material,0)),
3246 0
3247 ),
3248 decode(SIGN(NVL(wppb2.pl_material_in,0)-NVL(wppb2.pl_material_out,0)
3249 - NVL(wppb2.pl_material_var,0)),
3250 1,
3251 (NVL(wppb2.pl_material_in,0)- NVL(wppb2.pl_material_out,0)- nvl(wppb2.pl_material_in_apull,0)
3252 - NVL(wppb2.pl_material_var,0)+ nvl(wppb2.scrap_pull_material,0))*
3253 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3254 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3255 0, 1,
3256 -1, 1,
3257 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3258 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3259 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_material_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3260 nvl(wppb2.pl_material_in_apull,0) - nvl(wppb2.scrap_pull_material,0)),
3261 0
3262 ),
3263
3264 NVL(wppb.scrap_pull_material,0) +
3265 decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_material_in_apull,0)/
3266 (wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0),
3267
3268 NVL(pl_material_overhead_out,0) +
3269 decode(SIGN(NVL(wppb2.pl_material_overhead_in,0)
3270 - NVL(wppb2.pl_material_overhead_out,0)
3271 - NVL(wppb2.pl_material_overhead_var,0)),
3272 1,
3273 (NVL(wppb2.pl_material_overhead_in,0)
3274 - NVL(wppb2.pl_material_overhead_out,0)- nvl(wppb2.pl_material_overhead_in_apull,0)
3275 - NVL(wppb2.pl_material_overhead_var,0)+ nvl(wppb2.scrap_pull_material_overhead,0))*
3276 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3277 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3278 0, 1,
3279 -1, 1,
3280 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3281 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3282 ) + decode(p_scrap,1, p_net_qty * nvl(wppb.pl_material_overhead_in_apull,0)/(wppb.relieved_scrap_qty + nvl(l_net_completion,0)),
3283 nvl(wppb.pl_material_overhead_in_apull,0) - nvl(wppb2.scrap_pull_material_overhead,0)),
3284 0
3285 ),
3286 decode(SIGN(NVL(wppb2.pl_material_overhead_in,0)
3287 - NVL(wppb2.pl_material_overhead_out,0)
3288 - NVL(wppb2.pl_material_overhead_var,0)),
3289 1,
3290 (NVL(wppb2.pl_material_overhead_in,0)
3291 - NVL(wppb2.pl_material_overhead_out,0)-nvl(wppb2.pl_material_overhead_in_apull,0)
3292 - NVL(wppb2.pl_material_overhead_var,0)+ nvl(wppb2.scrap_pull_material_overhead,0))*
3293 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3294 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3295 0, 1,
3296 -1, 1,
3297 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3298 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3299 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_material_overhead_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3300 nvl(wppb2.pl_material_overhead_in_apull,0) - nvl(wppb2.scrap_pull_material_overhead,0)),
3301 0
3302 ),
3303
3304 NVL(wppb.scrap_pull_material_overhead,0) +
3305 decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_material_overhead_in_apull,0)/
3306 (wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0),
3307
3308 NVL(pl_resource_out,0) +
3309 decode(SIGN(NVL(wppb2.pl_resource_in,0)-NVL(wppb2.pl_resource_out,0)
3310 - NVL(wppb2.pl_resource_var,0)),
3311 1,
3312 (NVL(wppb2.pl_resource_in,0)- NVL(wppb2.pl_resource_out,0)- nvl(wppb2.pl_resource_in_apull,0)
3313 - NVL(wppb2.pl_resource_var,0) + NVL(wppb2.scrap_pull_resource,0) )*
3314 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3315 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3316 0, 1,
3317 -1, 1,
3318 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3319 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3320 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_resource_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3321 nvl(wppb2.pl_resource_in_apull,0) - NVL(wppb2.scrap_pull_resource,0)),
3322 0
3323 ),
3324
3325 decode(SIGN(NVL(wppb2.pl_resource_in,0)-NVL(wppb2.pl_resource_out,0)
3326 - NVL(wppb2.pl_resource_var,0)),
3327 1,
3328 (NVL(wppb2.pl_resource_in,0)- NVL(wppb2.pl_resource_out,0)- nvl(wppb2.pl_resource_in_apull,0)
3329 - NVL(wppb2.pl_resource_var,0) + NVL(wppb2.scrap_pull_resource,0))*
3330 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3331 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3332 0, 1,
3333 -1, 1,
3334 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3335 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3336 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_resource_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3337 nvl(wppb2.pl_resource_in_apull,0) - NVL(wppb2.scrap_pull_resource,0)),
3338 0
3339 ),
3340
3341 NVL(wppb.scrap_pull_resource,0) +
3342 decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_resource_in_apull,0)/
3343 (wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0),
3344
3345 NVL(pl_outside_processing_out,0) +
3346 decode(SIGN(NVL(wppb2.pl_outside_processing_in,0)
3347 - NVL(wppb2.pl_outside_processing_out,0)
3348 - NVL(wppb2.pl_outside_processing_var,0) + NVL(wppb.scrap_pull_outside_processing,0) ),
3349 1,
3350 (NVL(wppb2.pl_outside_processing_in,0)
3351 - NVL(wppb2.pl_outside_processing_out,0)- nvl(wppb2.pl_outside_processing_in_apull,0)
3352 - NVL(wppb2.pl_outside_processing_var,0) + NVL(wppb2.scrap_pull_resource,0))*
3353 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3354 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3355 0, 1,
3356 -1, 1,
3357 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3358 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3359 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_outside_processing_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3360 nvl(wppb2.pl_outside_processing_in_apull,0) - NVL(wppb2.scrap_pull_outside_processing,0)),
3361 0
3362 ),
3363 decode(SIGN(NVL(wppb.pl_outside_processing_in,0)
3364 - NVL(wppb.pl_outside_processing_out,0)
3365 - NVL(wppb.pl_outside_processing_var,0)),
3366 1,
3367 (NVL(wppb.pl_outside_processing_in,0)
3368 - NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
3369 - NVL(wppb.pl_outside_processing_var,0) + NVL(wppb.scrap_pull_outside_processing,0))*
3370 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3371 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3372 0, 1,
3373 -1, 1,
3374 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3375 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3376 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_outside_processing_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),
3377 nvl(wppb2.pl_outside_processing_in_apull,0) - NVL(wppb2.scrap_pull_outside_processing,0)),
3378 0
3379 ),
3380
3381 NVL(scrap_pull_outside_processing,0) +
3382 decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_outside_processing_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0),
3383
3384 NVL(pl_overhead_out,0) +
3385 decode(SIGN(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
3386 - NVL(wppb.pl_overhead_var,0)),
3387 1,
3388 (NVL(wppb2.pl_overhead_in,0)-NVL(wppb2.pl_overhead_out,0)-nvl(wppb2.pl_overhead_in_apull,0)
3389 - NVL(wppb2.pl_overhead_var,0) + NVL(wppb2.scrap_pull_overhead,0) )*
3390 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3391 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3392 0, 1,
3393 -1, 1,
3394 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3395 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3396 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_overhead_in_apull,0)/(relieved_scrap_qty + nvl(l_net_completion,0)),
3397 nvl(wppb2.pl_overhead_in_apull,0) - NVL(wppb2.scrap_pull_overhead,0)),
3398 0
3399 ),
3400 decode(SIGN(NVL(wppb2.pl_overhead_in,0)-NVL(wppb2.pl_overhead_out,0)
3401 - NVL(wppb2.pl_overhead_var,0)),
3402 1,
3403 (NVL(wppb2.pl_overhead_in,0)-NVL(wppb2.pl_overhead_out,0)-nvl(wppb2.pl_overhead_in_apull,0)
3404 - NVL(wppb2.pl_overhead_var,0) + NVL(wppb2.scrap_pull_overhead,0) )*
3405 decode(SIGN(NVL(wppb2.operation_completed_units,0) -
3406 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
3407 0, 1,
3408 -1, 1,
3409 p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
3410 NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
3411 ) + decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_overhead_in_apull,0)/(relieved_scrap_qty + nvl(l_net_completion,0)),
3412 nvl(wppb2.pl_overhead_in_apull,0) - NVL(wppb2.scrap_pull_overhead,0)),
3413 0
3414 ),
3415 NVL(wppb.scrap_pull_overhead,0) +
3416 decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_overhead_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0)
3417
3418 FROM wip_pac_period_balances wppb2
3419 WHERE wppb2.pac_period_id = p_pac_period_id
3420 AND wppb2.cost_group_id = p_cost_group_id
3421 AND wppb2.wip_entity_id = p_entity_id
3422 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3423 AND wppb2.operation_seq_num = wppb.operation_seq_num
3424 )
3425 WHERE wppb.pac_period_id = p_pac_period_id
3426 AND wppb.cost_group_id = p_cost_group_id
3427 AND wppb.wip_entity_id = p_entity_id
3428 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3429 AND wppb.operation_seq_num <=
3430 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3431 END IF;
3432 ELSIF p_net_qty < 0 THEN -- This is a net return
3433 l_stmt_num := 44;
3434 UPDATE wip_pac_period_balances wppb
3435 SET (tl_resource_out,
3436 tl_resource_temp,
3437 tl_outside_processing_out,
3438 tl_outside_processing_temp,
3439 tl_overhead_out,
3440 tl_overhead_temp,
3441 pl_material_out,
3442 pl_material_temp,
3443 pl_material_overhead_out,
3444 pl_material_overhead_temp,
3445 pl_resource_out,
3446 pl_resource_temp,
3447 pl_outside_processing_out,
3448 pl_outside_processing_temp,
3449 pl_overhead_out,
3450 pl_overhead_temp
3451 ) =
3452 (SELECT
3453 NVL(wppb.tl_resource_out,0) +
3454 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
3455 1,
3456 NVL(wppb2.tl_resource_out,0) *
3457 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3458 0,1,wppb2.relieved_assembly_units),
3459 0),
3460 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
3461 1,
3462 NVL(wppb2.tl_resource_out,0) *
3463 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3464 0,1,wppb2.relieved_assembly_units),
3465 0),
3466 NVL(wppb.tl_outside_processing_out,0) +
3467 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
3468 1,
3469 NVL(wppb2.tl_outside_processing_in,0)*
3470 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3471 0,1,wppb2.relieved_assembly_units),
3472 0),
3473 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
3474 1,
3475 NVL(wppb2.tl_outside_processing_in,0)*
3476 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3477 0,1,wppb2.relieved_assembly_units),
3478 0),
3479 NVL(wppb.tl_overhead_out,0) +
3480 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
3481 1,
3482 NVL(wppb2.tl_overhead_out,0) *
3483 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3484 0,1,wppb2.relieved_assembly_units),
3485 0),
3486 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
3487 1,
3488 NVL(wppb2.tl_overhead_out,0) *
3489 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3490 0,1,wppb2.relieved_assembly_units),
3491 0),
3492 NVL(wppb.pl_material_out,0) +
3493 decode(SIGN(NVL(wppb2.pl_material_out,0)),
3494 1,
3495 NVL(wppb2.pl_material_out,0) *
3496 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3497 0,1,wppb2.relieved_assembly_units),
3498 0),
3499 decode(SIGN(NVL(wppb2.pl_material_out,0)),
3500 1,
3501 NVL(wppb2.pl_material_out,0) *
3502 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3503 0,1,wppb2.relieved_assembly_units),
3504 0),
3505 NVL(wppb.pl_material_overhead_out,0) +
3506 decode(SIGN(NVL(wppb2.pl_material_overhead_out,0)),
3507 1,
3508 NVL(wppb2.pl_material_overhead_out,0) *
3509 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3510 0,1,wppb2.relieved_assembly_units),
3511 0),
3512 decode(SIGN(NVL(wppb2.pl_material_overhead_out,0)),
3513 1,
3514 NVL(wppb2.pl_material_overhead_out,0) *
3515 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3516 0,1,wppb2.relieved_assembly_units),
3517 0),
3518 NVL(wppb.pl_resource_out,0) +
3519 decode(SIGN(NVL(wppb2.pl_resource_out,0)),
3520 1,
3521 NVL(wppb2.pl_resource_out,0) *
3522 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3523 0,1,wppb2.relieved_assembly_units),
3524 0),
3525 decode(SIGN(NVL(wppb2.pl_resource_out,0)),
3526 1,
3527 NVL(wppb2.pl_resource_out,0) *
3528 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3529 0,1,wppb2.relieved_assembly_units),
3530 0),
3531 NVL(wppb.pl_outside_processing_out,0) +
3532 decode(SIGN(NVL(wppb2.pl_outside_processing_out,0)),
3533 1,
3534 NVL(wppb2.pl_outside_processing_out,0) *
3535 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3536 0,1,wppb2.relieved_assembly_units),
3537 0),
3538 decode(SIGN(NVL(wppb2.pl_outside_processing_out,0)),
3539 1,
3540 NVL(wppb2.pl_outside_processing_out,0) *
3541 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3542 0,1,wppb2.relieved_assembly_units),
3543 0),
3544 NVL(wppb.pl_overhead_out,0) +
3545 decode(SIGN(NVL(wppb2.pl_overhead_out,0)),
3546 1,
3547 NVL(wppb2.pl_overhead_out,0) *
3548 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3549 0,1,wppb2.relieved_assembly_units),
3550 0),
3551 decode(SIGN(NVL(wppb2.pl_overhead_out,0)),
3552 1,
3553 NVL(wppb2.pl_overhead_out,0) *
3554 p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3555 0,1,wppb2.relieved_assembly_units),
3556 0)
3557 FROM wip_pac_period_balances wppb2
3558 WHERE wppb2.pac_period_id = p_prior_period_id
3559 AND wppb2.cost_group_id = p_cost_group_id
3560 AND wppb2.wip_entity_id = p_entity_id
3561 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3562 AND wppb2.operation_seq_num = wppb.operation_seq_num
3563 )
3564 WHERE wppb.pac_period_id = p_pac_period_id
3565 AND wppb.cost_group_id = p_cost_group_id
3566 AND wppb.wip_entity_id = p_entity_id
3567 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3568 AND wppb.operation_seq_num <=
3569 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3570 ELSE
3571 l_stmt_num := 46;
3572 -- This is net cancellation between completions and returns
3573 IF p_final_completion_flag = 1 THEN
3574
3575 l_stmt_num := 48;
3576 SELECT sum(primary_quantity)
3577 INTO l_assembly_return_cnt
3578 FROM mtl_material_transactions mmt
3579 WHERE mmt.transaction_source_id = p_entity_id
3580 AND mmt.transaction_action_id = 32
3581 AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
3582 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
3583 AND (TRUNC(p_end_date) + 0.99999);
3584
3585 IF (p_prior_period_id <> -1) then
3586
3587 l_stmt_num := 50;
3588 UPDATE wip_pac_period_balances wppb
3589 SET ( tl_resource_out,
3590 tl_outside_processing_out,
3591 tl_overhead_out,
3592 pl_material_out,
3593 pl_material_overhead_out,
3594 pl_resource_out,
3595 pl_outside_processing_out,
3596 pl_overhead_out
3597 ) =
3598 (SELECT
3599 NVL(wppb.tl_resource_out,0) +
3600 decode(SIGN(NVL(wppb2.tl_resource_out,0)),
3601 1,
3602 NVL(wppb2.tl_resource_out,0) *
3603 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3604 0,1,wppb2.relieved_assembly_units),
3605 0),
3606 NVL(wppb.tl_outside_processing_out,0) +
3607 decode(SIGN(NVL(wppb2.tl_outside_processing_out,0)),
3608 1,
3609 NVL(wppb2.tl_outside_processing_in,0)*
3610 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3611 0,1,wppb2.relieved_assembly_units),
3612 0),
3613 NVL(wppb.tl_overhead_out,0) +
3614 decode(SIGN(NVL(wppb2.tl_overhead_out,0)),
3615 1,
3616 NVL(wppb2.tl_overhead_out,0) *
3617 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3618 0,1,wppb2.relieved_assembly_units),
3619 0),
3620 NVL(wppb.pl_material_out,0) +
3621 decode(SIGN(NVL(wppb2.pl_material_out,0)),
3622 1,
3623 NVL(wppb2.pl_material_out,0) *
3624 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3625 0,1,wppb2.relieved_assembly_units),
3626 0),
3627 NVL(wppb.pl_material_overhead_out,0) +
3628 decode(SIGN(NVL(wppb2.pl_material_overhead_out,0)),
3629 1,
3630 NVL(wppb2.pl_material_overhead_out,0) *
3631 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3632 0,1,wppb2.relieved_assembly_units),
3633 0),
3634 NVL(wppb.pl_resource_out,0) +
3635 decode(SIGN(NVL(wppb2.pl_resource_out,0)),
3636 1,
3637 NVL(wppb2.pl_resource_out,0) *
3638 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3639 0,1,wppb2.relieved_assembly_units),
3640 0),
3641 NVL(wppb.pl_outside_processing_out,0) +
3642 decode(SIGN(NVL(wppb2.pl_outside_processing_out,0)),
3643 1,
3644 NVL(wppb2.pl_outside_processing_out,0) *
3645 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3646 0,1,wppb2.relieved_assembly_units),
3647 0),
3648 NVL(wppb.pl_overhead_out,0) +
3649 decode(SIGN(NVL(wppb2.pl_overhead_out,0)),
3650 1,
3651 NVL(wppb2.pl_overhead_out,0) *
3652 l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3653 0,1,wppb2.relieved_assembly_units),
3654 0)
3655 FROM wip_pac_period_balances wppb2
3656 WHERE wppb2.pac_period_id = p_prior_period_id
3657 AND wppb2.cost_group_id = p_cost_group_id
3658 AND wppb2.wip_entity_id = p_entity_id
3659 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3660 AND wppb2.operation_seq_num = wppb.operation_seq_num
3661 )
3662 WHERE wppb.pac_period_id = p_pac_period_id
3663 AND wppb.cost_group_id = p_cost_group_id
3664 AND wppb.wip_entity_id = p_entity_id
3665 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3666 AND wppb.operation_seq_num <=
3667 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3668
3669 END IF; /* end of period <> -1 */
3670 l_stmt_num := 52;
3671 UPDATE wip_pac_period_balances wppb
3672 SET ( tl_resource_out,
3673 tl_resource_temp,
3674 tl_outside_processing_out,
3675 tl_outside_processing_temp,
3676 tl_overhead_out,
3677 tl_overhead_temp,
3678 pl_material_out,
3679 pl_material_temp,
3680 pl_material_overhead_out,
3681 pl_material_overhead_temp,
3682 pl_resource_out,
3683 pl_resource_temp,
3684 pl_outside_processing_out,
3685 pl_outside_processing_temp,
3686 pl_overhead_out,
3687 pl_overhead_temp
3688 ) =
3689 (SELECT
3690 NVL(wppb.tl_resource_out,0) +
3691 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
3692 - NVL(tl_resource_var,0)),
3693 1,
3694 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
3695 - NVL(tl_resource_var,0)),
3696 0
3697 ),
3698 decode(SIGN(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
3699 - NVL(tl_resource_var,0)),
3700 1,
3701 (NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
3702 - NVL(tl_resource_var,0))
3703 ,0
3704 ),
3705 NVL(tl_outside_processing_out,0) +
3706 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
3707 - NVL(wppb.tl_outside_processing_out,0)
3708 - NVL(wppb.tl_outside_processing_var,0)),
3709 1,
3710 (NVL(wppb.tl_outside_processing_in,0)
3711 - NVL(wppb.tl_outside_processing_out,0)
3712 - NVL(wppb.tl_outside_processing_var,0))
3713 ,0
3714 ),
3715 decode(SIGN(NVL(wppb.tl_outside_processing_in,0)
3716 - NVL(wppb.tl_outside_processing_out,0)
3717 - NVL(wppb.tl_outside_processing_var,0)),
3718 1,
3719 (NVL(wppb.tl_outside_processing_in,0)
3720 - NVL(wppb.tl_outside_processing_out,0)
3721 - NVL(wppb.tl_outside_processing_var,0)),
3722 0
3723 ),
3724 NVL(tl_overhead_out,0) +
3725 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3726 - NVL(wppb.tl_overhead_var,0)),
3727 1,
3728 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3729 - NVL(wppb.tl_overhead_var,0)),
3730 0
3731 ),
3732 decode(SIGN(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3733 - NVL(wppb.tl_overhead_var,0)),
3734 1,
3735 (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
3736 - NVL(wppb.tl_overhead_var,0)),
3737 0
3738 ),
3739 NVL(pl_material_out,0) +
3740 decode(SIGN(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
3741 - NVL(wppb.pl_material_var,0)),
3742 1,
3743 (NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)- nvl(wppb.pl_material_in_apull,0)
3744 - NVL(wppb.pl_material_var,0))
3745 + nvl(wppb.pl_material_in_apull,0),
3746 0
3747 ),
3748 decode(SIGN(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
3749 - NVL(wppb.pl_material_var,0)),
3750 1,
3751 (NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)-nvl(wppb.pl_material_in_apull,0)
3752 - NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
3753 0
3754 ),
3755 NVL(pl_material_overhead_out,0) +
3756 decode(SIGN(NVL(wppb.pl_material_overhead_in,0)
3757 - NVL(wppb.pl_material_overhead_out,0)
3758 - NVL(wppb.pl_material_overhead_var,0)),
3759 1,
3760 (NVL(wppb.pl_material_overhead_in,0)
3761 - NVL(wppb.pl_material_overhead_out,0)-nvl(wppb.pl_material_overhead_in_apull,0)
3762 - NVL(wppb.pl_material_overhead_var,0))
3763 + nvl(wppb.pl_material_overhead_in_apull,0),
3764 0
3765 ),
3766 decode(SIGN(NVL(wppb.pl_material_overhead_in,0)
3767 - NVL(wppb.pl_material_overhead_out,0)
3768 - NVL(wppb.pl_material_overhead_var,0)),
3769 1,
3770 (NVL(wppb.pl_material_overhead_in,0)
3771 - NVL(wppb.pl_material_overhead_out,0)-nvl(wppb.pl_material_overhead_in_apull,0)
3772 - NVL(wppb.pl_material_overhead_var,0))
3773 + nvl(wppb.pl_material_overhead_in_apull,0),
3774 0
3775 ),
3776 NVL(pl_resource_out,0) +
3777 decode(SIGN(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
3778 - NVL(wppb.pl_resource_var,0)),
3779 1,
3780 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)-nvl(wppb.pl_resource_in_apull,0)
3781 - NVL(wppb.pl_resource_var,0))
3782 + nvl(wppb.pl_resource_in_apull,0),
3783 0
3784 ),
3785 decode(SIGN(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
3786 - NVL(wppb.pl_resource_var,0)),
3787 1,
3788 (NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)-nvl(wppb.pl_resource_in_apull,0)
3789 - NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
3790 0
3791 ),
3792 NVL(pl_outside_processing_out,0) +
3793 decode(SIGN(NVL(wppb.pl_outside_processing_in,0)
3794 - NVL(wppb.pl_outside_processing_out,0)
3795 - NVL(wppb.pl_outside_processing_var,0)),
3796 1,
3797 (NVL(wppb.pl_outside_processing_in,0)
3798 - NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
3799 - NVL(wppb.pl_outside_processing_var,0))
3800 + nvl(wppb.pl_outside_processing_in_apull,0),
3801 0
3802 ),
3803 decode(SIGN(NVL(wppb.pl_outside_processing_in,0)
3804 - NVL(wppb.pl_outside_processing_out,0)
3805 - NVL(wppb.pl_outside_processing_var,0)),
3806 1,
3807 (NVL(wppb.pl_outside_processing_in,0)
3808 - NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
3809 - NVL(wppb.pl_outside_processing_var,0)) +
3810 nvl(wppb.pl_outside_processing_in_apull,0),
3811 0
3812 ),
3813 NVL(pl_overhead_out,0) +
3814 decode(SIGN(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
3815 - NVL(wppb.pl_overhead_var,0)),
3816 1,
3817 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
3818 - NVL(wppb.pl_overhead_var,0))
3819 + nvl(wppb.pl_overhead_in_apull,0),
3820 0
3821 ),
3822 decode(SIGN(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
3823 - NVL(wppb.pl_overhead_var,0)),
3824 1,
3825 (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
3826 - NVL(wppb.pl_overhead_var,0))
3827 + nvl(wppb.pl_overhead_in_apull,0),
3828 0
3829 )
3830 FROM wip_pac_period_balances wppb2
3831 WHERE wppb2.pac_period_id = p_pac_period_id
3832 AND wppb2.cost_group_id = p_cost_group_id
3833 AND wppb2.wip_entity_id = p_entity_id
3834 AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3835 AND wppb2.operation_seq_num = wppb.operation_seq_num
3836 )
3837 WHERE
3838 wppb.pac_period_id = p_pac_period_id
3839 AND wppb.cost_group_id = p_cost_group_id
3840 AND wppb.wip_entity_id = p_entity_id
3841 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3842 AND wppb.operation_seq_num <=
3843 decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3844 ELSE
3845 l_stmt_num := 54;
3846 UPDATE wip_pac_period_balances wppb
3847 SET (
3848 tl_resource_temp,
3849 tl_outside_processing_temp,
3850 tl_overhead_temp,
3851 pl_material_temp,
3852 pl_material_overhead_temp,
3853 pl_resource_temp,
3854 pl_outside_processing_temp,
3855 pl_overhead_temp
3856 )
3857 = (
3858 SELECT DECODE(
3859 SIGN(NVL(tl_resource_out,0)),
3860 1,
3861 NVL(tl_resource_out,0) /
3862 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3863 0
3864 ),
3865 DECODE(
3866 SIGN(NVL(tl_outside_processing_out,0)),
3867 1,
3868 NVL(tl_outside_processing_out,0) /
3869 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3870 0
3871 ),
3872 DECODE(
3873 SIGN(NVL(tl_overhead_out,0)),
3874 1,
3875 NVL(tl_overhead_out,0) /
3876 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3877 0
3878 ),
3879 DECODE(
3880 SIGN(NVL(pl_material_out,0)),
3881 1,
3882 NVL(pl_material_out,0) /
3883 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3884 0
3885 ),
3886 DECODE(
3887 SIGN(NVL(pl_material_overhead_out,0)),
3888 1,
3889 NVL(pl_material_overhead_out,0) /
3890 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3891 0
3892 ),
3893 DECODE(
3894 SIGN(NVL(pl_resource_out,0)),
3895 1,
3896 NVL(pl_resource_out,0) /
3897 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3898 0
3899 ),
3900 DECODE(
3901 SIGN(NVL(pl_outside_processing_out,0)),
3902 1,
3903 NVL(pl_outside_processing_out,0) /
3904 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3905 0
3906 ),
3907 DECODE(
3908 SIGN(NVL(pl_overhead_out,0)),
3909 1,
3910 NVL(pl_overhead_out,0) /
3911 DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3912 0
3913 )
3914 FROM wip_pac_period_balances
3915 WHERE pac_period_id = p_prior_period_id
3916 AND cost_group_id = p_cost_group_id
3917 AND wip_entity_id = p_entity_id
3918 AND NVL(line_id,-99) = NVL(p_line_id,-99)
3919 AND operation_seq_num = wppb.operation_seq_num
3920 )
3921 WHERE pac_period_id = p_pac_period_id
3922 AND cost_group_id = p_cost_group_id
3923 AND wip_entity_id = p_entity_id
3924 AND NVL(line_id,-99) = NVL(p_line_id,-99)
3925 AND operation_seq_num <= decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3926 END IF; -- end of p_final_completion_flag = 1
3927 END IF; -- Check whether p_net_qty is > 0, < 0 or = 0
3928 END IF; /* End of p_material_relief_algorithm */
3929
3930 ------------------------------------------------------------------
3931 -- Update wppb.RELIEVED_ASSEMBLY_UNITS
3932 ------------------------------------------------------------------
3933 l_stmt_num := 56;
3934 UPDATE wip_pac_period_balances wppb
3935 SET wppb.relieved_assembly_units =
3936 NVL(wppb.relieved_assembly_units,0) + p_net_qty
3937 WHERE wppb.pac_period_id = p_pac_period_id
3938 AND wppb.cost_group_id = p_cost_group_id
3939 AND wppb.wip_entity_id = p_entity_id
3940 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3941 AND wppb.operation_seq_num <= decode(p_scrap,1,p_op_seq,
3942 wppb.operation_seq_num);
3943 END IF; -- check for cfm
3944
3945 ----------------------------------------------------------------------
3946 -- Load transaction costs in MPTCD
3947 -- Costs to be loaded into MPTCD must be in the
3948 -- pri uom of the master item organization
3949 -- The costs stored in the temp column and p_net_qty are based
3950 -- on the organization's pri UOM which may be different from
3951 -- the item master org's pri UOM. So, we will convert both
3952 -- costs and quantity if the uom control is not 1 i.e. not at
3953 -- the item master org level
3954 ----------------------------------------------------------------------
3955
3956 l_uom_conv_rate := 1;
3957
3958 IF (p_uom_control <> 1) THEN
3959
3960 l_stmt_num := 60;
3961
3962 SELECT NVL(we.primary_item_id,-1),
3963 we.organization_id
3964 INTO l_item_id,
3965 l_org_id
3966 FROM wip_entities we
3967 WHERE we.wip_entity_id = p_entity_id;
3968
3969 IF (l_item_id <> -1) THEN
3970 l_stmt_num := 65;
3971 CSTPPINV.get_um_rate ( i_txn_org_id => l_org_id,
3972 i_master_org_id => p_master_org_id,
3973 i_txn_cost_group_id => -1,
3974 i_txfr_cost_group_id => -2,
3975 i_txn_action_id => -3,
3976 i_item_id => l_item_id,
3977 i_uom_control => p_uom_control,
3978 i_user_id => p_user_id,
3979 i_login_id => p_login_id,
3980 i_request_id => p_request_id,
3981 i_prog_id => p_prog_id,
3982 i_prog_appl_id => p_prog_app_id,
3983 o_um_rate => l_uom_conv_rate,
3984 o_err_num => l_err_num,
3985 o_err_code => l_err_code,
3986 o_err_msg => l_err_msg);
3987
3988 IF (l_err_num <> 0) THEN
3989
3990 l_err_msg := SUBSTR('UOM conv error wip_entity: '
3991 ||TO_CHAR(p_entity_id)
3992 ||':'
3993 ||l_err_msg,1,240);
3994 RAISE CST_PROCESS_ERROR;
3995
3996 END IF;
3997
3998 ELSE
3999 l_stmt_num := 67;
4000
4001 l_uom_conv_rate :=1;
4002
4003 END IF;
4004
4005 END IF; -- check for uom control level
4006
4007 l_stmt_num := 70;
4008 IF ( p_net_qty = 0 and p_final_completion_flag = 1 ) then
4009
4010 SELECT sum(primary_quantity)
4011 INTO l_completed_assembly_qty
4012 FROM mtl_material_transactions mmt
4013 WHERE mmt.transaction_source_id = p_entity_id
4014 AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
4015 AND mmt.transaction_action_id = 31
4016 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
4017 AND (TRUNC(p_end_date) + 0.99999);
4018 END IF;
4019
4020 l_conv_net_qty := p_net_qty * l_uom_conv_rate;
4021 -- Using same variable for conversion value aslo
4022 l_completed_assembly_qty := l_completed_assembly_qty * l_uom_conv_rate;
4023
4024 IF (p_scrap = 1) THEN -- Its a scrap txn
4025
4026 FOR c_txn_rec IN c_scrap_txn LOOP
4027
4028 l_stmt_num := 75;
4029
4030 insert_wip_costs
4031 (p_pac_period_id => p_pac_period_id,
4032 p_prior_period_id => p_prior_period_id,
4033 p_cost_group_id => p_cost_group_id,
4034 p_cost_type_id => p_cost_type_id,
4035 p_item_id => c_txn_rec.item_id,
4036 p_entity_id => p_entity_id,
4037 p_line_id => p_line_id,
4038 p_txn_id => c_txn_rec.txn_id,
4039 p_net_qty => l_conv_net_qty,
4040 p_completed_assembly_qty => NULL,
4041 p_final_completion_flag => NULL,
4042 p_start_date => p_start_date,
4043 p_end_date => p_end_date,
4044 p_user_id => p_user_id,
4045 p_login_id => p_login_id,
4046 p_request_id => p_request_id,
4047 p_prog_id => p_prog_id,
4048 p_prog_app_id => p_prog_app_id,
4049 x_err_num => l_err_num,
4050 x_err_code => l_err_code,
4051 x_err_msg => l_err_msg);
4052
4053 IF (l_err_num <> 0) THEN
4054
4055 l_err_msg := SUBSTR('Txn_id: '
4056 ||TO_CHAR(c_txn_rec.txn_id)
4057 ||':'
4058 ||l_err_msg,1,240);
4059 RAISE CST_PROCESS_ERROR;
4060
4061 END IF;
4062
4063 l_stmt_num := 80;
4064
4065 check_expense_flags (
4066 p_item_id => c_txn_rec.item_id,
4067 p_subinv => c_txn_rec.subinv,
4068 p_org_id => c_txn_rec.org_id,
4069 x_exp_item => l_exp_item,
4070 x_exp_flag => l_exp_flag,
4071 x_err_num => l_err_num,
4072 x_err_code => l_err_code,
4073 x_err_msg => l_err_msg);
4074
4075 IF (l_err_num <> 0) THEN
4076
4077 l_err_msg := SUBSTR('Item_id: '
4078 ||TO_CHAR(c_txn_rec.item_id)
4079 ||':'
4080 ||l_err_msg,1,240);
4081
4082 RAISE CST_PROCESS_ERROR;
4083
4084 END IF;
4085
4086 l_stmt_num := 85;
4087
4088 CSTPPWAC.cost_processor
4089 (i_legal_entity => p_legal_entity,
4090 i_pac_period_id => p_pac_period_id,
4091 i_org_id => p_org_id,
4092 i_cost_group_id => p_cost_group_id,
4093 i_txn_cost_group_id => NULL,
4094 i_txfr_cost_group_id => NULL,
4095 i_cost_type_id => p_cost_type_id,
4096 i_cost_method => p_cost_method,
4097 i_process_group => 1,
4098 i_txn_id => c_txn_rec.txn_id,
4099 i_qty_layer_id => p_qty_layer_id,
4100 i_cost_layer_id => p_cost_layer_id,
4101 i_pac_rates_id => p_pac_rates_id,
4102 i_item_id => c_txn_rec.item_id,
4103 i_txn_qty => c_txn_rec.pri_qty *
4104 l_uom_conv_rate,
4105 i_txn_action_id => c_txn_rec.txn_action_id,
4106 i_txn_src_type_id => c_txn_rec.txn_src_type_id,
4107 i_fob_point => NULL,
4108 i_exp_item => l_exp_item,
4109 i_exp_flag => l_exp_flag,
4110 i_cost_hook_used => -1,
4111 i_user_id => p_user_id,
4112 i_login_id => p_login_id,
4113 i_req_id => p_request_id,
4114 i_prg_appl_id => p_prog_app_id,
4115 i_prg_id => p_prog_id,
4116 i_txn_category => p_txn_category,
4117 o_err_num => l_err_num,
4118 o_err_code => l_err_code,
4119 o_err_msg => l_err_msg);
4120
4121 IF (l_err_num <> 0) THEN
4122
4123 l_err_msg := SUBSTR('Txn_id: '
4124 ||TO_CHAR(c_txn_rec.txn_id)
4125 ||':'
4126 ||l_err_msg,1,240);
4127 RAISE CST_PROCESS_ERROR;
4128
4129 END IF;
4130
4131 END LOOP; --scrap_txn_loop
4132
4133 ELSE -- Its is an assembly completion/return txn
4134
4135 l_stmt_num := 90;
4136
4137 FOR c_txn_rec IN c_assy_txn LOOP
4138 l_stmt_num := 95;
4139
4140 insert_wip_costs
4141 (p_pac_period_id => p_pac_period_id,
4142 p_prior_period_id => p_prior_period_id,
4143 p_cost_group_id => p_cost_group_id,
4144 p_cost_type_id => p_cost_type_id,
4145 p_item_id => c_txn_rec.item_id,
4146 p_entity_id => p_entity_id,
4147 p_line_id => p_line_id,
4148 p_txn_id => c_txn_rec.txn_id,
4149 p_net_qty => l_conv_net_qty,
4150 p_completed_assembly_qty => l_completed_assembly_qty,
4151 p_final_completion_flag => p_final_completion_flag,
4152 p_start_date => p_start_date,
4153 p_end_date => p_end_date,
4154 p_user_id => p_user_id,
4155 p_login_id => p_login_id,
4156 p_request_id => p_request_id,
4157 p_prog_id => p_prog_id,
4158 p_prog_app_id => p_prog_app_id,
4159 x_err_num => l_err_num,
4160 x_err_code => l_err_code,
4161 x_err_msg => l_err_msg);
4162
4163 IF (l_err_num <> 0) THEN
4164
4165 l_err_msg := SUBSTR('Txn_id: '
4166 ||TO_CHAR(c_txn_rec.txn_id)
4167 ||':'
4168 ||l_err_msg,1,240);
4169
4170 RAISE CST_PROCESS_ERROR;
4171
4172 END IF;
4173
4174 l_stmt_num := 100;
4175
4176 check_expense_flags (
4177 p_item_id => c_txn_rec.item_id,
4178 p_subinv => c_txn_rec.subinv,
4179 p_org_id => c_txn_rec.org_id,
4180 x_exp_item => l_exp_item,
4181 x_exp_flag => l_exp_flag,
4182 x_err_num => l_err_num,
4183 x_err_code => l_err_code,
4184 x_err_msg => l_err_msg);
4185
4186 IF (l_err_num <> 0) THEN
4187
4188 l_err_msg := SUBSTR('Item_id: '
4189 ||TO_CHAR(c_txn_rec.item_id)
4190 ||':'
4191 ||l_err_msg,1,240);
4192 RAISE CST_PROCESS_ERROR;
4193
4194 END IF;
4195
4196 l_stmt_num := 105;
4197
4198 CSTPPWAC.cost_processor
4199 (i_legal_entity => p_legal_entity,
4200 i_pac_period_id => p_pac_period_id,
4201 i_org_id => p_org_id,
4202 i_cost_group_id => p_cost_group_id,
4203 i_txn_cost_group_id => NULL,
4204 i_txfr_cost_group_id => NULL,
4205 i_cost_type_id => p_cost_type_id,
4206 i_cost_method => p_cost_method,
4207 i_process_group => 1,
4208 i_txn_id => c_txn_rec.txn_id,
4209 i_qty_layer_id => p_qty_layer_id,
4210 i_cost_layer_id => p_cost_layer_id,
4211 i_pac_rates_id => p_pac_rates_id,
4212 i_item_id => c_txn_rec.item_id,
4213 i_txn_qty => c_txn_rec.pri_qty *
4214 l_uom_conv_rate,
4215 i_txn_action_id => c_txn_rec.txn_action_id,
4216 i_txn_src_type_id => c_txn_rec.txn_src_type_id,
4217 i_fob_point => NULL,
4218 i_exp_item => l_exp_item,
4219 i_exp_flag => l_exp_flag,
4220 i_cost_hook_used => -1,
4221 i_user_id => p_user_id,
4222 i_login_id => p_login_id,
4223 i_req_id => p_request_id,
4224 i_prg_appl_id => p_prog_app_id,
4225 i_prg_id => p_prog_id,
4226 i_txn_category => p_txn_category,
4227 o_err_num => l_err_num,
4228 o_err_code => l_err_code,
4229 o_err_msg => l_err_msg);
4230
4231 IF (l_err_num <> 0) THEN
4232
4233 l_err_msg := SUBSTR('Txn_id: '
4234 ||TO_CHAR(c_txn_rec.txn_id)
4235 ||':'
4236 ||l_err_msg,1,240);
4237 RAISE CST_PROCESS_ERROR;
4238 END IF;
4239
4240 END LOOP; --assy_txn_loop
4241 END IF; -- check for p_scrap
4242
4243 IF (l_pLog) THEN
4244 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
4245 l_module || '.end',
4246 l_api_name || ' >>>');
4247 END IF;
4248
4249 EXCEPTION
4250
4251 WHEN CST_PROCESS_ERROR THEN
4252 IF (l_exceptionLog) THEN
4253 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
4254 l_module || '.' || l_stmt_num,
4255 l_err_msg);
4256 END IF;
4257 x_err_num := l_err_num;
4258 x_err_code := l_err_code;
4259 x_err_msg := l_err_msg;
4260
4261 WHEN OTHERS THEN
4262 IF (l_uLog) THEN
4263 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
4264 l_module || '.' || l_stmt_num,
4265 SQLERRM);
4266 END IF;
4267 ROLLBACK;
4268 x_err_num := SQLCODE;
4269 x_err_code := NULL;
4270 x_err_msg := SUBSTR('CSTPPWAS.process_net_relief('
4271 || to_char(l_stmt_num)
4272 || '): '
4273 ||SQLERRM,1,240);
4274
4275 END process_net_relief;
4276
4277 /*---------------------------------------------------------------------------*
4278 | PUBLIC PROCEDURE |
4279 | process_nonreworkassembly_txns |
4280 | called for items that have nonrework completion/return/scrap txns |
4281 | in the period |
4282 *----------------------------------------------------------------------------*/
4283 PROCEDURE process_nonreworkassembly_txns(
4284 p_pac_period_id IN NUMBER,
4285 p_start_date IN DATE,
4286 p_end_date IN DATE,
4287 p_prior_period_id IN NUMBER,
4288 p_item_id IN NUMBER,
4289 p_cost_group_id IN NUMBER,
4290 p_cost_type_id IN NUMBER,
4291 p_legal_entity IN NUMBER,
4292 p_cost_method IN NUMBER,
4293 p_pac_rates_id IN NUMBER,
4294 p_master_org_id IN NUMBER,
4295 p_material_relief_algorithm IN NUMBER,
4296 p_uom_control IN NUMBER,
4297 p_low_level_code IN NUMBER,
4298 p_user_id IN NUMBER,
4299 p_login_id IN NUMBER,
4300 p_request_id IN NUMBER,
4301 p_prog_id IN NUMBER DEFAULT -1,
4302 p_prog_app_id IN NUMBER DEFAULT -1,
4303 x_err_num OUT NOCOPY NUMBER,
4304 x_err_code OUT NOCOPY VARCHAR2,
4305 x_err_msg OUT NOCOPY VARCHAR2)
4306 IS
4307
4308 l_cost_method_hook NUMBER;
4309 l_cost_layer_id NUMBER;
4310 l_qty_layer_id NUMBER;
4311 l_open_flag VARCHAR2(1);
4312 l_stmt_num NUMBER;
4313 l_pri_uom_code VARCHAR2(3);
4314 l_err_num NUMBER;
4315 l_err_code VARCHAR2(240);
4316 l_err_msg VARCHAR2(240);
4317 l_exp_flag NUMBER;
4318 l_exp_item NUMBER;
4319 cst_process_error EXCEPTION;
4320 l_final_completion_count NUMBER;
4321
4322 l_api_name CONSTANT VARCHAR2(30) := 'process_nonreworkassembly_txns';
4323 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4324 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
4325
4326 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
4327 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
4328 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
4329 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
4330 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4331 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4332
4333 ------------------------------------------------------------------------------
4334 -- All relieves are based on the concept of net relieves. i.e.
4335 -- Net Relief = Completion - Return
4336 -- Job information is also built on the concept of net units earned i.e.
4337 -- if you complete 10 units from Op 10 and Return 5 units to Op 30 then,
4338 -- the net relief logic uses:
4339 -- net_qty = 10-5 = 5
4340 -- Resource earned at Op 10 = 10
4341 -- Resource earned at Op 30 = 5
4342 -- Its possible then the completion is done at period P1 where the PAC
4343 -- rates (say for a resource) were lower than the period (P2) in which the
4344 -- net return was done. Net return is always done based on prior period's
4345 -- values.
4346 ------------------------------------------------------------------------------
4347
4348 ------------------------------------------------------------------------------
4349 -- This cursor will give the net scrap qty for all wip_entity/line that :-
4350 -- 1. Had scrap transaction(s) in this period
4351 -- 2. AND entity belongs to one of the memeber organizations
4352 -- 3. AND entity has a record in wppb i.e. has some value
4353 -- 4. AND entity is a non-rework job
4354 ------------------------------------------------------------------------------
4355
4356 CURSOR c_non_rework_entity_scrap IS
4357 SELECT mmt.transaction_source_id entity_id,
4358 mmt.organization_id org_id,
4359 we.entity_type entity_type,
4360 mmt.repetitive_line_id line_id,
4361 mmt.operation_seq_num op_seq,
4362 SUM(mmt.primary_quantity) net_scrap
4363 FROM mtl_material_transactions mmt,
4364 cst_cost_group_assignments ccga,
4365 wip_entities we
4366 WHERE mmt.inventory_item_id = p_item_id
4367 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
4368 AND (TRUNC(p_end_date) + 0.99999)
4369 AND mmt.transaction_source_type_id = 5
4370 AND mmt.transaction_action_id = 30
4371 AND mmt.organization_id = ccga.organization_id
4372 AND ccga.cost_group_id = p_cost_group_id
4373 AND we.wip_entity_id = mmt.transaction_source_id
4374 AND we.organization_id = mmt.organization_id
4375 AND NOT EXISTS (SELECT 1
4376 FROM mtl_material_transactions mmt1
4377 WHERE mmt1.inventory_item_id = we.primary_item_id
4378 AND mmt1.transaction_source_id = we.wip_entity_id
4379 AND mmt1.organization_id = we.organization_id
4380 AND mmt1.transaction_source_type_id = 5
4381 AND mmt1.transaction_action_id in (1,27,33,34)
4382 AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
4383 AND (TRUNC (p_end_date) + 0.99999))
4384 GROUP BY
4385 mmt.transaction_source_id,
4386 mmt.organization_id,
4387 we.entity_type,
4388 mmt.repetitive_line_id,
4389 mmt.operation_seq_num
4390 ORDER BY
4391 SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
4392 mmt.transaction_source_id; -- a consistent tie breaker
4393
4394
4395 ------------------------------------------------------------------------------
4396 -- This cursor will give the net compl/ret qty for all wip_entity/line that :-
4397 -- 1. Had completion/return transaction(s) in this period
4398 -- 2. AND entity belongs to one of the memeber organizations
4399 -- 3. AND entity has a record in wppb i.e. has some value
4400 -- 4. AND entity is a non-rework job
4401 ------------------------------------------------------------------------------
4402
4403 CURSOR c_non_rework_entity_complete IS
4404 SELECT mmt.transaction_source_id entity_id,
4405 mmt.organization_id org_id,
4406 we.entity_type entity_type,
4407 mmt.repetitive_line_id line_id,
4408 SUM(mmt.primary_quantity) net_completion
4409 FROM mtl_material_transactions mmt,
4410 cst_cost_group_assignments ccga,
4411 wip_entities we
4412 WHERE mmt.inventory_item_id = p_item_id
4413 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
4414 AND (TRUNC(p_end_date) + 0.99999)
4415 AND mmt.transaction_source_type_id = 5
4416 AND mmt.transaction_action_id IN (31,32)
4417 AND mmt.organization_id = ccga.organization_id
4418 AND ccga.cost_group_id = p_cost_group_id
4419 AND we.wip_entity_id = mmt.transaction_source_id
4420 AND we.organization_id = mmt.organization_id
4421 AND NOT EXISTS (SELECT 1
4422 FROM mtl_material_transactions mmt1
4423 WHERE mmt1.inventory_item_id = we.primary_item_id
4424 AND mmt1.transaction_source_id = we.wip_entity_id
4425 AND mmt1.organization_id = we.organization_id
4426 AND mmt1.transaction_source_type_id = 5
4427 AND mmt1.transaction_action_id in (1,27,33,34)
4428 AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
4429 AND (TRUNC (p_end_date) + 0.99999))
4430 /* R12 PAC Enhancements for China and Taiwan: Exclude eAM entities as rebuildable jobs can be
4431 completed only in expense subinventories at 0 cost so they should not be costed and no
4432 distributions created */
4433 AND we.entity_type not in (6,7)
4434 GROUP BY
4435 mmt.transaction_source_id,
4436 mmt.organization_id,
4437 we.entity_type,
4438 mmt.repetitive_line_id
4439 ORDER BY
4440 SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
4441 mmt.transaction_source_id; -- a consistent tie breaker
4442
4443 ------------------------------------------------------------------------------
4444 -- This Cursor will get all scrap quantity without scrap account
4445 -- in this period for a given assembly
4446 -- Populates all the wip entities for this assembly
4447 ------------------------------------------------------------------------------
4448 CURSOR scrap_no_account is
4449 SELECT wmt.transaction_id wip_txn_id,
4450 wmt.wip_entity_id wip_entity,
4451 wmt.line_id line,
4452 wmt.fm_operation_seq_num from_op_seq,
4453 wmt.fm_intraoperation_step_type from_op_step,
4454 wmt.to_operation_seq_num to_op_seq,
4455 wmt.to_intraoperation_step_type to_op_step,
4456 wmt.primary_quantity pri_qty
4457 FROM cst_cost_group_assignments ccga,
4458 wip_move_transactions wmt
4459 WHERE wmt.transaction_date BETWEEN TRUNC(p_start_date)
4460 AND (TRUNC(p_end_date) + 0.99999)
4461 AND wmt.organization_id = ccga.organization_id
4462 AND ccga.cost_group_id = p_cost_group_id
4463 AND wmt.primary_item_id = p_item_id
4464 AND wmt.scrap_account_id is null
4465 AND (wmt.fm_intraoperation_step_type = 5 OR
4466 wmt.to_intraoperation_step_type = 5);
4467
4468 BEGIN
4469
4470 IF (l_pLog) THEN
4471 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
4472 l_module || '.begin',
4473 l_api_name || ' <<< Parameters:
4474 p_pac_period_id = ' || p_pac_period_id || '
4475 p_prior_period_id = ' || p_prior_period_id || '
4476 p_item_id = ' || p_item_id || '
4477 p_low_level_code = ' || p_low_level_code || '
4478 p_material_relief_algorithm = '|| p_material_relief_algorithm || '
4479 p_cost_type_id = ' || p_cost_type_id );
4480
4481 END IF;
4482
4483 ----------------------------------------------------------------------
4484 -- Initialize Variables
4485 ----------------------------------------------------------------------
4486
4487 l_err_num := 0;
4488 l_err_code := '';
4489 l_err_msg := '';
4490 l_cost_layer_id := 0;
4491 l_qty_layer_id := 0;
4492 l_cost_method_hook := -1;
4493
4494 ----------------------------------------------------------------------
4495 -- Check and Create layer for the assembly, if required
4496 ----------------------------------------------------------------------
4497
4498 l_stmt_num := 5;
4499 CSTPPCLM.layer_id (
4500 i_pac_period_id => p_pac_period_id,
4501 i_legal_entity => p_legal_entity,
4502 i_item_id => p_item_id,
4503 i_cost_group_id => p_cost_group_id,
4504 o_cost_layer_id => l_cost_layer_id,
4505 o_quantity_layer_id => l_qty_layer_id,
4506 o_err_num => l_err_num,
4507 o_err_code => l_err_code,
4508 o_err_msg => l_err_msg);
4509
4510 IF (l_err_num <> 0) THEN
4511 RAISE CST_PROCESS_ERROR;
4512 END IF;
4513
4514
4515 IF (l_cost_layer_id = 0 AND l_qty_layer_id = 0) THEN
4516 l_stmt_num := 10;
4517 CSTPPCLM.create_layer (
4518 i_pac_period_id => p_pac_period_id,
4519 i_legal_entity => p_legal_entity,
4520 i_item_id => p_item_id,
4521 i_cost_group_id => p_cost_group_id,
4522 i_user_id => p_user_id,
4523 i_login_id => p_login_id,
4524 i_request_id => p_request_id,
4525 i_prog_id => p_prog_id,
4526 i_prog_appl_id => p_prog_app_id,
4527 o_cost_layer_id => l_cost_layer_id,
4528 o_quantity_layer_id => l_qty_layer_id,
4529 o_err_num => l_err_num,
4530 o_err_code => l_err_code,
4531 o_err_msg => l_err_msg);
4532
4533 IF (l_err_num <> 0) THEN
4534 RAISE CST_PROCESS_ERROR;
4535 END IF;
4536 END IF; -- Check Create Layer
4537
4538 FOR c_scrap_rec IN scrap_no_account LOOP
4539 l_stmt_num := 15;
4540
4541 IF (c_scrap_rec.from_op_step <> 5 and c_scrap_rec.to_op_step = 5) then
4542
4543 l_stmt_num := 20;
4544 UPDATE WIP_PAC_PERIOD_BALANCES
4545 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
4546 WHERE wip_entity_id = c_scrap_rec.wip_entity
4547 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4548 AND operation_seq_num <= c_scrap_rec.to_op_seq
4549 AND cost_type_id = p_cost_type_id
4550 AND pac_period_id = p_pac_period_id
4551 AND cost_group_id = p_cost_group_id;
4552
4553 END IF;
4554
4555 IF (c_scrap_rec.from_op_step = 5 and c_scrap_rec.to_op_step <> 5) then
4556
4557 l_stmt_num := 25;
4558
4559 UPDATE WIP_PAC_PERIOD_BALANCES
4560 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
4561 WHERE wip_entity_id = c_scrap_rec.wip_entity
4562 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4563 AND operation_seq_num <= c_scrap_rec.from_op_seq
4564 AND cost_type_id = p_cost_type_id
4565 AND pac_period_id = p_pac_period_id
4566 AND cost_group_id = p_cost_group_id;
4567
4568 END IF;
4569
4570 IF (c_scrap_rec.from_op_step = 5 and c_scrap_rec.to_op_step = 5) then
4571
4572 l_stmt_num := 30;
4573
4574 IF (c_scrap_rec.from_op_seq > c_scrap_rec.to_op_seq) THEN
4575
4576 l_stmt_num := 35;
4577 UPDATE WIP_PAC_PERIOD_BALANCES
4578 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
4579 WHERE wip_entity_id = c_scrap_rec.wip_entity
4580 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4581 AND operation_seq_num > c_scrap_rec.to_op_seq
4582 AND operation_seq_num <= c_scrap_rec.from_op_seq
4583 AND cost_type_id = p_cost_type_id
4584 AND pac_period_id = p_pac_period_id
4585 AND cost_group_id = p_cost_group_id;
4586
4587 ELSE
4588
4589 l_stmt_num := 40;
4590
4591 UPDATE WIP_PAC_PERIOD_BALANCES
4592 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
4593 WHERE wip_entity_id = c_scrap_rec.wip_entity
4594 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4595 AND operation_seq_num > c_scrap_rec.from_op_seq
4596 AND operation_seq_num <= c_scrap_rec.to_op_seq
4597 AND cost_type_id = p_cost_type_id
4598 AND pac_period_id = p_pac_period_id
4599 AND cost_group_id = p_cost_group_id;
4600
4601 END IF;
4602 END IF;
4603
4604 END LOOP;
4605
4606 FOR c_ent_rec IN c_non_rework_entity_scrap LOOP
4607
4608 fnd_file.put_line(fnd_file.log,' Scrap_Qty Update << ');
4609 l_stmt_num := 45;
4610
4611 UPDATE WIP_PAC_PERIOD_BALANCES
4612 SET relieved_scrap_qty = nvl(relieved_scrap_qty,0) + c_ent_rec.net_scrap
4613 WHERE wip_entity_id = c_ent_rec.entity_id
4614 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_ent_rec.line_id,-99))
4615 AND operation_seq_num <= c_ent_rec.op_seq
4616 AND cost_type_id = p_cost_type_id
4617 AND pac_period_id = p_pac_period_id
4618 AND cost_group_id = p_cost_group_id;
4619
4620 END LOOP;
4621
4622
4623 l_stmt_num := 50;
4624
4625 --------------------------------------------------------------------------
4626 -- Process scrap transactions for non-rework jobs
4627 -- If number of records exceeds threshold value, insert them into
4628 -- CST_PAC_PERIOD_BALANCES and clear the PL/SQL tables.
4629 --------------------------------------------------------------------------
4630
4631 FOR c_ent_rec IN c_non_rework_entity_scrap LOOP
4632 l_stmt_num := 60;
4633 IF (CSTPPINV.l_item_id_tbl.COUNT >= G_MAX_RECORDS AND p_cost_method <> 4) THEN
4634 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
4635 i_cost_group_id => p_cost_group_id,
4636 i_txn_category => 4, /* Non-rework completions */
4637 i_user_id => p_user_id,
4638 i_login_id => p_login_id,
4639 i_request_id => p_request_id,
4640 i_prog_id => p_prog_id,
4641 i_prog_appl_id => p_prog_app_id,
4642 o_err_num => l_err_num,
4643 o_err_code => l_err_code,
4644 o_err_msg => l_err_msg);
4645 IF (l_err_num <> 0) THEN
4646 l_err_msg := SUBSTR('CSTPPWAS.process_nonreworkassembly_txns('
4647 ||TO_CHAR(l_stmt_num)
4648 ||'):'
4649 ||l_err_msg,1,240);
4650 RAISE CST_PROCESS_ERROR;
4651 END IF;
4652 END IF;
4653
4654 l_stmt_num := 65;
4655 process_net_relief (
4656 p_pac_period_id => p_pac_period_id,
4657 p_prior_period_id => p_prior_period_id,
4658 p_cost_group_id => p_cost_group_id,
4659 p_cost_type_id => p_cost_type_id,
4660 p_legal_entity => p_legal_entity,
4661 p_cost_method => p_cost_method,
4662 p_cost_layer_id => l_cost_layer_id,
4663 p_qty_layer_id => l_qty_layer_id,
4664 p_pac_rates_id => p_pac_rates_id,
4665 p_entity_type => c_ent_rec.entity_type,
4666 p_org_id => c_ent_rec.org_id,
4667 p_entity_id => c_ent_rec.entity_id,
4668 p_line_id => c_ent_rec.line_id,
4669 p_net_qty => c_ent_rec.net_scrap,
4670 p_start_date => p_start_date,
4671 p_end_date => p_end_date,
4672 p_scrap => 1,
4673 p_op_seq => c_ent_rec.op_seq,
4674 p_final_completion_flag => NULL,
4675 p_material_relief_algorithm => p_material_relief_algorithm,
4676 p_master_org_id => p_master_org_id,
4677 p_uom_control => p_uom_control,
4678 p_user_id => p_user_id,
4679 p_login_id => p_login_id,
4680 p_request_id => p_request_id,
4681 p_prog_id => p_prog_id,
4682 p_prog_app_id => p_prog_app_id,
4683 p_txn_category => 4, -- txn_category = 4 for non-rework completions/scrap
4684 x_err_num => l_err_num,
4685 x_err_code => l_err_code,
4686 x_err_msg => l_err_msg);
4687
4688 IF (l_err_num <>0) THEN
4689 l_err_msg := SUBSTR('scrap entity/line: '
4690 ||TO_CHAR(c_ent_rec.entity_id)
4691 ||'/'
4692 ||TO_CHAR(c_ent_rec.line_id)
4693 ||':'
4694 ||l_err_msg,1,240);
4695 RAISE CST_PROCESS_ERROR;
4696 END IF;
4697 END LOOP; --c_non_rework_entity_scrap
4698
4699 --------------------------------------------------------------------------
4700 -- Process completions transactions for non-rework jobs
4701 -- If number of records exceeds threshold value, insert them into
4702 -- CST_PAC_PERIOD_BALANCES and clear the PL/SQL tables.
4703 --------------------------------------------------------------------------
4704
4705 l_stmt_num := 70;
4706 FOR c_ent_rec IN c_non_rework_entity_complete LOOP
4707 l_stmt_num := 75;
4708
4709 SELECT count(*)
4710 INTO l_final_completion_count
4711 FROM mtl_material_transactions mmt
4712 WHERE mmt.transaction_source_id = c_ent_rec.entity_id
4713 AND nvl(mmt.repetitive_line_id,-99) = nvl( c_ent_rec.line_id,-99)
4714 AND mmt.final_completion_flag = 'Y'
4715 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
4716 AND (TRUNC(p_end_date) + 0.99999)
4717 AND ROWNUM < 2;
4718
4719 IF (CSTPPINV.l_item_id_tbl.COUNT >= G_MAX_RECORDS AND p_cost_method <> 4) THEN
4720 l_stmt_num := 80;
4721 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
4722 i_cost_group_id => p_cost_group_id,
4723 i_txn_category => 4, /* Non-rework completions */
4724 i_user_id => p_user_id,
4725 i_login_id => p_login_id,
4726 i_request_id => p_request_id,
4727 i_prog_id => p_prog_id,
4728 i_prog_appl_id => p_prog_app_id,
4729 o_err_num => l_err_num,
4730 o_err_code => l_err_code,
4731 o_err_msg => l_err_msg);
4732 IF (l_err_num <> 0) THEN
4733 l_err_msg := SUBSTR('CSTPPWAS.process_nonreworkassembly_txns('
4734 ||TO_CHAR(l_stmt_num)
4735 ||'):'
4736 ||l_err_msg,1,240);
4737 RAISE CST_PROCESS_ERROR;
4738 END IF;
4739 END IF;
4740
4741 l_stmt_num := 85;
4742 process_net_relief (
4743 p_pac_period_id => p_pac_period_id,
4744 p_prior_period_id => p_prior_period_id,
4745 p_cost_group_id => p_cost_group_id,
4746 p_cost_type_id => p_cost_type_id,
4747 p_legal_entity => p_legal_entity,
4748 p_cost_method => p_cost_method,
4749 p_cost_layer_id => l_cost_layer_id,
4750 p_qty_layer_id => l_qty_layer_id,
4751 p_pac_rates_id => p_pac_rates_id,
4752 p_entity_type => c_ent_rec.entity_type,
4753 p_org_id => c_ent_rec.org_id,
4754 p_entity_id => c_ent_rec.entity_id,
4755 p_line_id => c_ent_rec.line_id,
4756 p_net_qty => c_ent_rec.net_completion,
4757 p_start_date => p_start_date,
4758 p_end_date => p_end_date,
4759 p_scrap => -1,
4760 p_op_seq => NULL,
4761 p_final_completion_flag => l_final_completion_count,
4762 p_material_relief_algorithm => p_material_relief_algorithm,
4763 p_master_org_id => p_master_org_id,
4764 p_uom_control => p_uom_control,
4765 p_user_id => p_user_id,
4766 p_login_id => p_login_id,
4767 p_request_id => p_request_id,
4768 p_prog_id => p_prog_id,
4769 p_prog_app_id => p_prog_app_id,
4770 p_txn_category => 4, -- txn_category = 4 for non-rework completions/scrap
4771 x_err_num => l_err_num,
4772 x_err_code => l_err_code,
4773 x_err_msg => l_err_msg);
4774
4775 IF (l_err_num <>0) THEN
4776 l_err_msg := SUBSTR('cmpl entity/line: '
4777 ||TO_CHAR(c_ent_rec.entity_id)
4778 ||'/'
4779 ||TO_CHAR(c_ent_rec.line_id)
4780 ||':'
4781 ||l_err_msg,1,240);
4782 RAISE CST_PROCESS_ERROR;
4783 END IF;
4784 END LOOP; --c_non_rework_entity_complete
4785
4786 -- Flush the remaining records from PL/SQL tables.
4787 l_stmt_num := 90;
4788 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND p_cost_method <> 4) THEN
4789 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
4790 i_cost_group_id => p_cost_group_id,
4791 i_txn_category => 4, /* Non-rework completions */
4792 i_user_id => p_user_id,
4793 i_login_id => p_login_id,
4794 i_request_id => p_request_id,
4795 i_prog_id => p_prog_id,
4796 i_prog_appl_id => p_prog_app_id,
4797 o_err_num => l_err_num,
4798 o_err_code => l_err_code,
4799 o_err_msg => l_err_msg);
4800
4801 IF (l_err_num <> 0) THEN
4802 l_err_msg := SUBSTR('CSTPPWAS.process_nonreworkassembly_txns('
4803 ||TO_CHAR(l_stmt_num)
4804 ||'):'
4805 ||l_err_msg,1,240);
4806 RAISE CST_PROCESS_ERROR;
4807 END IF;
4808 END IF;
4809
4810 IF (p_cost_method <> 4) THEN
4811 l_stmt_num := 100;
4812 CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
4813 i_cost_group_id => p_cost_group_id,
4814 i_txn_category => 4, /* Non-rework Completions */
4815 i_low_level_code => p_low_level_code,
4816 i_user_id => p_user_id,
4817 i_login_id => p_login_id,
4818 i_request_id => p_request_id,
4819 i_prog_id => p_prog_id,
4820 i_prog_appl_id => p_prog_app_id,
4821 o_err_num => l_err_num,
4822 o_err_code => l_err_code,
4823 o_err_msg => l_err_msg);
4824
4825 IF (l_err_num <> 0) THEN
4826 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
4827 || TO_CHAR(l_stmt_num)
4828 ||'):'
4829 ||l_err_msg,1,240);
4830 RAISE CST_PROCESS_ERROR;
4831 END IF;
4832 END IF;
4833
4834 IF (l_pLog) THEN
4835 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
4836 l_module || '.end',
4837 l_api_name || ' >>>');
4838 END IF;
4839
4840 EXCEPTION
4841
4842 WHEN CST_PROCESS_ERROR THEN
4843 IF (l_exceptionLog) THEN
4844 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
4845 l_module || '.' || l_stmt_num,
4846 l_err_msg);
4847 END IF;
4848 x_err_num := l_err_num;
4849 x_err_code := l_err_code;
4850 x_err_msg := SUBSTR(l_err_msg,1,240);
4851
4852 WHEN OTHERS THEN
4853 IF (l_uLog) THEN
4854 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
4855 l_module || '.' || l_stmt_num,
4856 SQLERRM);
4857 END IF;
4858 ROLLBACK;
4859 x_err_num := SQLCODE;
4860 x_err_code := NULL;
4861 x_err_msg := SUBSTR('CSTPPWAS.process_nonreworkassembly_txns ('
4862 || to_char(l_stmt_num)
4863 || '): '
4864 ||SQLERRM,1,240);
4865
4866 END process_nonreworkassembly_txns;
4867
4868 /*---------------------------------------------------------------------------*
4869 | PUBLIC PROCEDURE |
4870 | process_reworkassembly_txns |
4871 | Called for items that have rework completion/return/scrap txns |
4872 | in the period |
4873 *----------------------------------------------------------------------------*/
4874 PROCEDURE process_reworkassembly_txns(
4875 p_pac_period_id IN NUMBER,
4876 p_start_date IN DATE,
4877 p_end_date IN DATE,
4878 p_prior_period_id IN NUMBER,
4879 p_item_id IN NUMBER,
4880 p_cost_group_id IN NUMBER,
4881 p_cost_type_id IN NUMBER,
4882 p_legal_entity IN NUMBER,
4883 p_cost_method IN NUMBER,
4884 p_pac_rates_id IN NUMBER,
4885 p_master_org_id IN NUMBER,
4886 p_material_relief_algorithm IN NUMBER,
4887 p_uom_control IN NUMBER,
4888 p_low_level_code IN NUMBER,
4889 p_user_id IN NUMBER,
4890 p_login_id IN NUMBER,
4891 p_request_id IN NUMBER,
4892 p_prog_id IN NUMBER DEFAULT -1,
4893 p_prog_app_id IN NUMBER DEFAULT -1,
4894 x_err_num OUT NOCOPY NUMBER,
4895 x_err_code OUT NOCOPY VARCHAR2,
4896 x_err_msg OUT NOCOPY VARCHAR2)
4897 IS
4898 l_cost_method_hook NUMBER;
4899 l_cost_layer_id NUMBER;
4900 l_qty_layer_id NUMBER;
4901 l_open_flag VARCHAR2(1);
4902 l_stmt_num NUMBER;
4903 l_pri_uom_code VARCHAR2(3);
4904 l_err_num NUMBER;
4905 l_err_code VARCHAR2(240);
4906 l_err_msg VARCHAR2(240);
4907 l_exp_flag NUMBER;
4908 l_exp_item NUMBER;
4909 cst_process_error EXCEPTION;
4910 l_final_completion_count NUMBER;
4911 l_co_txns_count NUMBER;
4912
4913 l_api_name CONSTANT VARCHAR2(30) := 'process_reworkassembly_txns';
4914 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4915 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
4916
4917 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
4918 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
4919 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
4920 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
4921 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4922 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4923
4924 ------------------------------------------------------------------------------
4925 -- This cursor will get all the WIP Component txns where this item
4926 -- is issued to a WIP entity building itself.(Non Standard Job-Rework)
4927 ------------------------------------------------------------------------------
4928
4929 CURSOR c_rework_issue_txns IS
4930 SELECT mmt.transaction_id txn_id,
4931 mmt.transaction_source_id entity_id,
4932 mmt.repetitive_line_id line_id,
4933 mmt.primary_quantity pri_qty,
4934 mmt.inventory_item_id item_id,
4935 mmt.operation_seq_num op_seq,
4936 mmt.organization_id org_id
4937 FROM mtl_material_transactions mmt,
4938 wip_entities we,
4939 cst_cost_group_assignments ccga /* bug3930450 - Added ccga join to process txns for a particular cost group */
4940 WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
4941 AND (TRUNC(p_end_date) + 0.99999)
4942 AND ccga.cost_group_id = p_cost_group_id /* bug3930450 */
4943 AND ccga.organization_id = mmt.organization_id /* bug3930450 */
4944 AND mmt.transaction_source_type_id = 5
4945 AND mmt.transaction_action_id IN (1,27,33,34)
4946 AND mmt.inventory_item_id = p_item_id
4947 AND we.wip_entity_id = mmt.transaction_source_id
4948 AND we.organization_id = mmt.organization_id
4949 AND NVL(we.primary_item_id,-1) = mmt.inventory_item_id;
4950
4951 -----------------------------------------------------------------------------
4952 -- This cursor will give the net scrap qty for all wip_entity/line that :-
4953 -- 1. Had scrap transaction(s) in this period
4954 -- 2. AND entity belongs to one of the memeber organizations
4955 -- 3. AND entity has a record in wppb i.e. has some value
4956 -- 4. AND entity is a rework job
4957 ------------------------------------------------------------------------------
4958
4959 CURSOR c_rework_entity_scrap IS
4960 SELECT mmt.transaction_source_id entity_id,
4961 mmt.organization_id org_id,
4962 we.entity_type entity_type,
4963 mmt.repetitive_line_id line_id,
4964 mmt.operation_seq_num op_seq,
4965 SUM(mmt.primary_quantity) net_scrap
4966 FROM mtl_material_transactions mmt,
4967 cst_cost_group_assignments ccga,
4968 wip_entities we
4969 WHERE mmt.inventory_item_id = p_item_id
4970 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
4971 AND (TRUNC(p_end_date) + 0.99999)
4972 AND mmt.transaction_source_type_id = 5
4973 AND mmt.transaction_action_id = 30
4974 AND mmt.organization_id = ccga.organization_id
4975 AND ccga.cost_group_id = p_cost_group_id
4976 AND we.wip_entity_id = mmt.transaction_source_id
4977 AND we.organization_id = mmt.organization_id
4978 AND EXISTS (SELECT 1
4979 FROM mtl_material_transactions mmt1
4980 WHERE mmt1.inventory_item_id = we.primary_item_id
4981 AND mmt1.transaction_source_id = we.wip_entity_id
4982 AND mmt1.organization_id = we.organization_id
4983 AND mmt1.transaction_source_type_id = 5
4984 AND mmt1.transaction_action_id in (1,27,33,34)
4985 AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
4986 AND (TRUNC (p_end_date) + 0.99999))
4987 GROUP BY
4988 mmt.transaction_source_id,
4989 mmt.organization_id,
4990 we.entity_type,
4991 mmt.repetitive_line_id,
4992 mmt.operation_seq_num
4993 ORDER BY
4994 SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
4995 mmt.transaction_source_id; -- a consistent tie breaker
4996
4997 -----------------------------------------------------------------------------
4998 -- This cursor will give the net compl/ret qty for all wip_entity/line that :-
4999 -- 1. Had completion/return transaction(s) in this period
5000 -- 2. AND entity belongs to one of the memeber organizations
5001 -- 3. AND entity has a record in wppb i.e. has some value
5002 -- 4. AND entity is a rework job
5003 ------------------------------------------------------------------------------
5004
5005 CURSOR c_rework_entity_complete IS
5006 SELECT mmt.transaction_source_id entity_id,
5007 mmt.organization_id org_id,
5008 we.entity_type entity_type,
5009 mmt.repetitive_line_id line_id,
5010 SUM(mmt.primary_quantity) net_completion
5011 FROM mtl_material_transactions mmt,
5012 cst_cost_group_assignments ccga,
5013 wip_entities we
5014 WHERE mmt.inventory_item_id = p_item_id
5015 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
5016 AND (TRUNC(p_end_date) + 0.99999)
5017 AND mmt.transaction_source_type_id = 5
5018 AND mmt.transaction_action_id IN (31,32)
5019 AND mmt.organization_id = ccga.organization_id
5020 AND ccga.cost_group_id = p_cost_group_id
5021 AND we.wip_entity_id = mmt.transaction_source_id
5022 AND we.organization_id = mmt.organization_id
5023 AND EXISTS(SELECT 1
5024 FROM mtl_material_transactions mmt1
5025 WHERE mmt1.inventory_item_id = we.primary_item_id
5026 AND mmt1.transaction_source_id = we.wip_entity_id
5027 AND mmt1.organization_id = we.organization_id
5028 AND mmt1.transaction_source_type_id = 5
5029 AND mmt1.transaction_action_id in (1,27,33,34)
5030 AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
5031 AND (TRUNC (p_end_date) + 0.99999))
5032 /* Exclude eAM entities as only rebuildable jobs can be completed only
5033 in expense subinventories at 0 cost so they should not be costed and no
5034 distributions created */
5035 AND we.entity_type not in (6,7)
5036 GROUP BY
5037 mmt.transaction_source_id,
5038 mmt.organization_id,
5039 we.entity_type,
5040 mmt.repetitive_line_id
5041 ORDER BY
5042 SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
5043 mmt.transaction_source_id; -- a consistent tie breaker
5044
5045 BEGIN
5046
5047 IF (l_pLog) THEN
5048
5049 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
5050 l_module || '.begin',
5051 l_api_name || ' <<< Parameters:
5052 p_pac_period_id = ' || p_pac_period_id || '
5053 p_prior_period_id = ' || p_prior_period_id || '
5054 p_item_id = ' || p_item_id || '
5055 p_low_level_code = ' || p_low_level_code || '
5056 p_material_relief_algorithm = '|| p_material_relief_algorithm || '
5057 p_cost_type_id = ' || p_cost_type_id );
5058 END IF;
5059
5060 ----------------------------------------------------------------------
5061 -- Initialize Variables
5062 ----------------------------------------------------------------------
5063
5064 l_err_num := 0;
5065 l_err_code := '';
5066 l_err_msg := '';
5067 l_cost_layer_id := 0;
5068 l_qty_layer_id := 0;
5069 l_cost_method_hook := -1;
5070 l_co_txns_count := 0;
5071 ----------------------------------------------------------------------
5072 -- Check and Create layer for the assembly, if required
5073 ----------------------------------------------------------------------
5074
5075 l_stmt_num := 5;
5076
5077 CSTPPCLM.layer_id (
5078 i_pac_period_id => p_pac_period_id,
5079 i_legal_entity => p_legal_entity,
5080 i_item_id => p_item_id,
5081 i_cost_group_id => p_cost_group_id,
5082 o_cost_layer_id => l_cost_layer_id,
5083 o_quantity_layer_id => l_qty_layer_id,
5084 o_err_num => l_err_num,
5085 o_err_code => l_err_code,
5086 o_err_msg => l_err_msg);
5087
5088 IF (l_err_num <> 0) THEN
5089 RAISE CST_PROCESS_ERROR;
5090 END IF;
5091
5092
5093 IF (l_cost_layer_id = 0 AND l_qty_layer_id = 0) THEN
5094
5095 l_stmt_num := 10;
5096
5097 CSTPPCLM.create_layer (
5098 i_pac_period_id => p_pac_period_id,
5099 i_legal_entity => p_legal_entity,
5100 i_item_id => p_item_id,
5101 i_cost_group_id => p_cost_group_id,
5102 i_user_id => p_user_id,
5103 i_login_id => p_login_id,
5104 i_request_id => p_request_id,
5105 i_prog_id => p_prog_id,
5106 i_prog_appl_id => p_prog_app_id,
5107 o_cost_layer_id => l_cost_layer_id,
5108 o_quantity_layer_id => l_qty_layer_id,
5109 o_err_num => l_err_num,
5110 o_err_code => l_err_code,
5111 o_err_msg => l_err_msg);
5112
5113 IF (l_err_num <> 0) THEN
5114 RAISE CST_PROCESS_ERROR;
5115 END IF;
5116 END IF; -- Check Create Layer
5117
5118 l_stmt_num := 15;
5119 FOR c_rework_rec IN c_rework_issue_txns LOOP
5120
5121 IF (CSTPPINV.l_item_id_tbl.COUNT >= G_MAX_RECORDS AND p_cost_method <> 4) THEN
5122 l_stmt_num := 20;
5123 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
5124 i_cost_group_id => p_cost_group_id,
5125 i_txn_category => 6, /* Rework Issues */
5126 i_user_id => p_user_id,
5127 i_login_id => p_login_id,
5128 i_request_id => p_request_id,
5129 i_prog_id => p_prog_id,
5130 i_prog_appl_id => p_prog_app_id,
5131 o_err_num => l_err_num,
5132 o_err_code => l_err_code,
5133 o_err_msg => l_err_msg);
5134
5135 IF (l_err_num <> 0) THEN
5136 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5137 ||TO_CHAR(l_stmt_num)
5138 ||'):'
5139 ||l_err_msg,1,240);
5140 RAISE CST_PROCESS_ERROR;
5141 END IF;
5142 END IF;
5143
5144 l_stmt_num := 25;
5145 CSTPPWMT.charge_wip_material (
5146 p_pac_period_id => p_pac_period_id,
5147 p_cost_group_id => p_cost_group_id,
5148 p_txn_id => c_rework_rec.txn_id,
5149 p_exp_item => NULL,
5150 p_exp_flag => NULL,
5151 p_legal_entity => p_legal_entity,
5152 p_cost_type_id => p_cost_type_id,
5153 p_cost_method => p_cost_method,
5154 p_pac_rates_id => p_pac_rates_id,
5155 p_master_org_id => p_master_org_id,
5156 p_material_relief_algorithm => p_material_relief_algorithm,
5157 p_uom_control => p_uom_control,
5158 p_user_id => p_user_id,
5159 p_login_id => p_login_id,
5160 p_request_id => p_request_id,
5161 p_prog_id => p_prog_id,
5162 p_prog_app_id => p_prog_app_id,
5163 p_txn_category => 6, /* Rework issues */
5164 x_cost_method_hook => l_cost_method_hook,
5165 x_err_num => l_err_num,
5166 x_err_code => l_err_code,
5167 x_err_msg => l_err_msg);
5168
5169 IF (l_err_num <>0) THEN
5170 l_err_msg := SUBSTR('Rewrk_txn_id: '
5171 ||TO_CHAR(c_rework_rec.txn_id)
5172 ||':'
5173 ||l_err_msg,1,240);
5174 RAISE CST_PROCESS_ERROR;
5175 END IF;
5176 END LOOP; --c_rework_issue_txns
5177
5178 l_stmt_num := 30;
5179 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND p_cost_method <> 4) THEN
5180
5181 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
5182 i_cost_group_id => p_cost_group_id,
5183 i_txn_category => 6, /* Rework Issues */
5184 i_user_id => p_user_id,
5185 i_login_id => p_login_id,
5186 i_request_id => p_request_id,
5187 i_prog_id => p_prog_id,
5188 i_prog_appl_id => p_prog_app_id,
5189 o_err_num => l_err_num,
5190 o_err_code => l_err_code,
5191 o_err_msg => l_err_msg);
5192
5193 IF (l_err_num <> 0) THEN
5194 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5195 ||TO_CHAR(l_stmt_num)
5196 ||'):'
5197 ||l_err_msg,1,240);
5198 RAISE CST_PROCESS_ERROR;
5199 END IF;
5200 END IF;
5201
5202 IF (p_cost_method <> 4) THEN
5203 l_stmt_num := 35;
5204 CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
5205 i_cost_group_id => p_cost_group_id,
5206 i_txn_category => 6, /* Rework Issues */
5207 i_low_level_code => p_low_level_code,
5208 i_user_id => p_user_id,
5209 i_login_id => p_login_id,
5210 i_request_id => p_request_id,
5211 i_prog_id => p_prog_id,
5212 i_prog_appl_id => p_prog_app_id,
5213 o_err_num => l_err_num,
5214 o_err_code => l_err_code,
5215 o_err_msg => l_err_msg);
5216
5217 IF (l_err_num <> 0) THEN
5218 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5219 || TO_CHAR(l_stmt_num)
5220 ||'):'
5221 ||l_err_msg,1,240);
5222 RAISE CST_PROCESS_ERROR;
5223 END IF;
5224 END IF;
5225
5226 ----------------------------------------------------------------------
5227 -- Relieve each entity's scrap/scrap_return cost
5228 ----------------------------------------------------------------------
5229
5230 l_stmt_num := 40;
5231
5232 FOR c_ent_rec IN c_rework_entity_scrap LOOP
5233
5234 l_co_txns_count := l_co_txns_count + 1;
5235
5236 IF (CSTPPINV.l_item_id_tbl.COUNT >= G_MAX_RECORDS AND p_cost_method <> 4) THEN
5237
5238 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
5239 i_cost_group_id => p_cost_group_id,
5240 i_txn_category => 7, /* Rework Completions */
5241 i_user_id => p_user_id,
5242 i_login_id => p_login_id,
5243 i_request_id => p_request_id,
5244 i_prog_id => p_prog_id,
5245 i_prog_appl_id => p_prog_app_id,
5246 o_err_num => l_err_num,
5247 o_err_code => l_err_code,
5248 o_err_msg => l_err_msg);
5249 IF (l_err_num <> 0) THEN
5250 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5251 ||TO_CHAR(l_stmt_num)
5252 ||'):'
5253 ||l_err_msg,1,240);
5254 RAISE CST_PROCESS_ERROR;
5255 END IF;
5256 END IF;
5257
5258 l_stmt_num := 45;
5259 process_net_relief (
5260 p_pac_period_id => p_pac_period_id,
5261 p_prior_period_id => p_prior_period_id,
5262 p_cost_group_id => p_cost_group_id,
5263 p_cost_type_id => p_cost_type_id,
5264 p_legal_entity => p_legal_entity,
5265 p_cost_method => p_cost_method,
5266 p_cost_layer_id => l_cost_layer_id,
5267 p_qty_layer_id => l_qty_layer_id,
5268 p_pac_rates_id => p_pac_rates_id,
5269 p_entity_type => c_ent_rec.entity_type,
5270 p_org_id => c_ent_rec.org_id,
5271 p_entity_id => c_ent_rec.entity_id,
5272 p_line_id => c_ent_rec.line_id,
5273 p_net_qty => c_ent_rec.net_scrap,
5274 p_start_date => p_start_date,
5275 p_end_date => p_end_date,
5276 p_scrap => 1,
5277 p_op_seq => c_ent_rec.op_seq,
5278 p_final_completion_flag => NULL,
5279 p_material_relief_algorithm => p_material_relief_algorithm,
5280 p_master_org_id => p_master_org_id,
5281 p_uom_control => p_uom_control,
5282 p_user_id => p_user_id,
5283 p_login_id => p_login_id,
5284 p_request_id => p_request_id,
5285 p_prog_id => p_prog_id,
5286 p_prog_app_id => p_prog_app_id,
5287 p_txn_category => 7, /* rework completions/scrap */
5288 x_err_num => l_err_num,
5289 x_err_code => l_err_code,
5290 x_err_msg => l_err_msg);
5291
5292 IF (l_err_num <> 0) THEN
5293
5294 l_err_msg := SUBSTR('scrap entity/line: '
5295 ||TO_CHAR(c_ent_rec.entity_id)
5296 ||'/'
5297 ||TO_CHAR(c_ent_rec.line_id)
5298 ||':'
5299 ||l_err_msg,1,240);
5300 RAISE CST_PROCESS_ERROR;
5301
5302 END IF;
5303 END LOOP; --c_rework_entity_scrap
5304
5305 ----------------------------------------------------------------------
5306 -- Relieve each entity's completion/return cost
5307 ----------------------------------------------------------------------
5308 l_stmt_num := 50;
5309
5310 FOR c_ent_rec IN c_rework_entity_complete LOOP
5311
5312 l_co_txns_count := l_co_txns_count + 1;
5313
5314 SELECT count(*)
5315 INTO l_final_completion_count
5316 FROM mtl_material_transactions mmt
5317 WHERE mmt.transaction_source_id = c_ent_rec.entity_id
5318 AND nvl(mmt.repetitive_line_id,-99) = nvl( c_ent_rec.line_id,-99)
5319 AND mmt.final_completion_flag = 'Y'
5320 AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
5321 AND (TRUNC(p_end_date) + 0.99999)
5322 AND ROWNUM < 2;
5323
5324 IF (CSTPPINV.l_item_id_tbl.COUNT >= G_MAX_RECORDS AND p_cost_method <> 4) THEN
5325 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
5326 i_cost_group_id => p_cost_group_id,
5327 i_txn_category => 7, /* Rework completions */
5328 i_user_id => p_user_id,
5329 i_login_id => p_login_id,
5330 i_request_id => p_request_id,
5331 i_prog_id => p_prog_id,
5332 i_prog_appl_id => p_prog_app_id,
5333 o_err_num => l_err_num,
5334 o_err_code => l_err_code,
5335 o_err_msg => l_err_msg);
5336
5337 IF (l_err_num <> 0) THEN
5338 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5339 ||TO_CHAR(l_stmt_num)
5340 ||'):'
5341 ||l_err_msg,1,240);
5342 RAISE CST_PROCESS_ERROR;
5343 END IF;
5344 END IF;
5345
5346 l_stmt_num := 55;
5347 process_net_relief (
5348 p_pac_period_id => p_pac_period_id,
5349 p_prior_period_id => p_prior_period_id,
5350 p_cost_group_id => p_cost_group_id,
5351 p_cost_type_id => p_cost_type_id,
5352 p_legal_entity => p_legal_entity,
5353 p_cost_method => p_cost_method,
5354 p_cost_layer_id => l_cost_layer_id,
5355 p_qty_layer_id => l_qty_layer_id,
5356 p_pac_rates_id => p_pac_rates_id,
5357 p_entity_type => c_ent_rec.entity_type,
5358 p_org_id => c_ent_rec.org_id,
5359 p_entity_id => c_ent_rec.entity_id,
5360 p_line_id => c_ent_rec.line_id,
5361 p_net_qty => c_ent_rec.net_completion,
5362 p_start_date => p_start_date,
5363 p_end_date => p_end_date,
5364 p_scrap => -1,
5365 p_op_seq => NULL,
5366 p_final_completion_flag => l_final_completion_count,
5367 p_material_relief_algorithm => p_material_relief_algorithm,
5368 p_master_org_id => p_master_org_id,
5369 p_uom_control => p_uom_control,
5370 p_user_id => p_user_id,
5371 p_login_id => p_login_id,
5372 p_request_id => p_request_id,
5373 p_prog_id => p_prog_id,
5374 p_prog_app_id => p_prog_app_id,
5375 p_txn_category => 7, /* rework completions/scrap */
5376 x_err_num => l_err_num,
5377 x_err_code => l_err_code,
5378 x_err_msg => l_err_msg);
5379
5380 IF (l_err_num <>0) THEN
5381
5382 l_err_msg := SUBSTR('cmpl entity/line: '
5383 ||TO_CHAR(c_ent_rec.entity_id)
5384 ||'/'
5385 ||TO_CHAR(c_ent_rec.line_id)
5386 ||':'
5387 ||l_err_msg,1,240);
5388 RAISE CST_PROCESS_ERROR;
5389
5390 END IF;
5391
5392 END LOOP; --c_rework_entity_complete
5393
5394 /* Insert into cppb */
5395 l_stmt_num := 60;
5396 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND p_cost_method <> 4) THEN
5397
5398 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
5399 i_cost_group_id => p_cost_group_id,
5400 i_txn_category => 7, /* Rework completions */
5401 i_user_id => p_user_id,
5402 i_login_id => p_login_id,
5403 i_request_id => p_request_id,
5404 i_prog_id => p_prog_id,
5405 i_prog_appl_id => p_prog_app_id,
5406 o_err_num => l_err_num,
5407 o_err_code => l_err_code,
5408 o_err_msg => l_err_msg);
5409 IF (l_err_num <> 0) THEN
5410 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5411 ||TO_CHAR(l_stmt_num)
5412 ||'):'
5413 ||l_err_msg,1,240);
5414 RAISE CST_PROCESS_ERROR;
5415 END IF;
5416 END IF;
5417
5418 /* Calculate Periodic Cost if cost method is not ILIFO and there
5419 have is atleast one cost owned transaction for rework assemblies */
5420
5421 IF (p_cost_method <> 4 AND l_co_txns_count > 0) THEN
5422 l_stmt_num := 65;
5423 CSTPPWAC.calculate_periodic_cost(i_pac_period_id => p_pac_period_id,
5424 i_cost_group_id => p_cost_group_id,
5425 i_cost_type_id => p_cost_type_id,
5426 i_low_level_code => p_low_level_code,
5427 i_item_id => NULL, /* Used only by PACP */
5428 i_user_id => p_user_id,
5429 i_login_id => p_login_id,
5430 i_request_id => p_request_id,
5431 i_prog_id => p_prog_id,
5432 i_prog_appl_id => p_prog_app_id,
5433 o_err_num => l_err_num,
5434 o_err_code => l_err_code,
5435 o_err_msg => l_err_msg);
5436
5437 IF (l_err_num <> 0) THEN
5438 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5439 ||TO_CHAR(l_stmt_num)
5440 ||'):'
5441 ||l_err_msg,1,240);
5442 RAISE CST_PROCESS_ERROR;
5443 END IF;
5444
5445 /* Update cppb */
5446 l_stmt_num := 70;
5447 CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
5448 i_cost_group_id => p_cost_group_id,
5449 i_txn_category => 7, /* Rework Completions */
5450 i_low_level_code => p_low_level_code,
5451 i_user_id => p_user_id,
5452 i_login_id => p_login_id,
5453 i_request_id => p_request_id,
5454 i_prog_id => p_prog_id,
5455 i_prog_appl_id => p_prog_app_id,
5456 o_err_num => l_err_num,
5457 o_err_code => l_err_code,
5458 o_err_msg => l_err_msg);
5459
5460 IF (l_err_num <> 0) THEN
5461 l_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5462 ||TO_CHAR(l_stmt_num)
5463 ||'):'
5464 ||l_err_msg,1,240);
5465 RAISE CST_PROCESS_ERROR;
5466 END IF;
5467 END IF;
5468
5469 IF (l_pLog) THEN
5470 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
5471 l_module || '.end',
5472 l_api_name || ' >>>');
5473 END IF;
5474
5475 EXCEPTION
5476
5477 WHEN CST_PROCESS_ERROR THEN
5478 IF (l_exceptionLog) THEN
5479 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
5480 l_module || '.' || l_stmt_num,
5481 l_err_msg);
5482 END IF;
5483 x_err_num := l_err_num;
5484 x_err_code := l_err_code;
5485 x_err_msg := SUBSTR(l_err_msg,1,240);
5486
5487 WHEN OTHERS THEN
5488 IF (l_uLog) THEN
5489 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
5490 l_module || '.' || l_stmt_num,
5491 SQLERRM);
5492 END IF;
5493 ROLLBACK;
5494 x_err_num := SQLCODE;
5495 x_err_code := NULL;
5496 x_err_msg := SUBSTR('CSTPPWAS.process_reworkassembly_txns('
5497 || to_char(l_stmt_num)
5498 || '): '
5499 ||SQLERRM,1,240);
5500 END process_reworkassembly_txns;
5501
5502 /*---------------------------------------------------------------------------*
5503 | PUBLIC PROCEDURE |
5504 | check_expense_flags |
5505 | utility procedure to return item and expense flags |
5506 | |
5507 *----------------------------------------------------------------------------*/
5508
5509 PROCEDURE check_expense_flags(
5510 p_item_id IN NUMBER,
5511 p_subinv IN VARCHAR2,
5512 p_org_id IN NUMBER,
5513 x_exp_item OUT NOCOPY NUMBER,
5514 x_exp_flag OUT NOCOPY NUMBER,
5515 x_err_num OUT NOCOPY NUMBER,
5516 x_err_code OUT NOCOPY VARCHAR2,
5517 x_err_msg OUT NOCOPY VARCHAR2)
5518 IS
5519
5520 l_stmt_num NUMBER;
5521 l_err_num NUMBER;
5522 l_err_code VARCHAR2(240);
5523 l_err_msg VARCHAR2(240);
5524 l_exp_item NUMBER;
5525 l_exp_flag NUMBER;
5526
5527 l_api_name CONSTANT VARCHAR2(30) := 'check_expense_flags';
5528 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
5529 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
5530
5531 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
5532 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
5533 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
5534 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
5535 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
5536 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
5537
5538 BEGIN
5539
5540 IF (l_pLog) THEN
5541 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
5542 l_module || '.begin',
5543 l_api_name || ' <<< Parameters:
5544 p_item_id = ' || p_item_id );
5545
5546 END IF;
5547 ----------------------------------------------------------------------
5548 -- Initialize Variables
5549 ----------------------------------------------------------------------
5550
5551 l_err_num := 0;
5552 l_err_code := '';
5553 l_err_msg := '';
5554 l_exp_item := 0;
5555 l_exp_flag := 0;
5556
5557 ----------------------------------------------------------------------
5558 -- Check Item flag
5559 ----------------------------------------------------------------------
5560
5561 l_stmt_num := 5;
5562
5563 SELECT decode(inventory_asset_flag,'Y',0,1)
5564 INTO l_exp_item
5565 FROM mtl_system_items msi
5566 WHERE inventory_item_id = p_item_id
5567 AND organization_id = p_org_id;
5568
5569 ----------------------------------------------------------------------
5570 -- Check Item flag
5571 ----------------------------------------------------------------------
5572
5573 l_stmt_num := 10;
5574
5575 IF (p_subinv IS NULL) THEN
5576 l_exp_flag := l_exp_item;
5577 ELSE
5578 SELECT decode(l_exp_item,1,1,decode(asset_inventory,1,0,1))
5579 INTO l_exp_flag
5580 FROM mtl_secondary_inventories msi
5581 WHERE secondary_inventory_name = p_subinv
5582 AND organization_id = p_org_id;
5583 END IF;
5584
5585 l_stmt_num := 15;
5586
5587 x_exp_item := l_exp_item;
5588 x_exp_flag := l_exp_flag;
5589
5590 IF (l_pLog) THEN
5591 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
5592 l_module || '.end',
5593 l_api_name || ' >>>');
5594 END IF;
5595
5596 EXCEPTION
5597
5598 WHEN OTHERS THEN
5599 IF (l_uLog) THEN
5600 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
5601 l_module || '.' || l_stmt_num,
5602 SQLERRM);
5603 END IF;
5604 ROLLBACK;
5605 x_err_num := SQLCODE;
5606 x_err_code := NULL;
5607 x_err_msg := SUBSTR('CSTPPWAS.check_expense_flags('
5608 || to_char(l_stmt_num)
5609 || '): '
5610 ||SQLERRM,1,240);
5611
5612 END check_expense_flags;
5613
5614 END cstppwas;