[Home] [Help]
PACKAGE BODY: APPS.CSTPPWCL
Source
1 PACKAGE BODY CSTPPWCL AS
2 /* $Header: CSTPWCLB.pls 120.4 2005/07/20 04:22:11 skayitha noship $ */
3
4 /*---------------------------------------------------------------------------*
5 | PUBLIC PROCEDURE |
6 | process_wip_close_txns |
7 | This routine will be called by the worker as the last step in processing. |
8 *----------------------------------------------------------------------------*/
9 PROCEDURE process_wip_close_txns(
10 p_pac_period_id IN NUMBER,
11 p_start_date IN DATE,
12 p_end_date IN DATE,
13 p_cost_group_id IN NUMBER,
14 p_cost_type_id IN NUMBER,
15 p_user_id IN NUMBER,
16 p_login_id IN NUMBER,
17 p_request_id IN NUMBER,
18 p_prog_id IN NUMBER DEFAULT -1,
19 p_prog_app_id IN NUMBER DEFAULT -1,
20 x_err_num OUT NOCOPY NUMBER,
21 x_err_code OUT NOCOPY VARCHAR2,
22 x_err_msg OUT NOCOPY VARCHAR2)
23 IS
24
25 l_stmt_num NUMBER;
26 l_err_num NUMBER;
27 l_err_code VARCHAR2(240);
28 l_err_msg VARCHAR2(240);
29 cst_process_error EXCEPTION;
30
31 -----------------------------------------------------------------------------
32 -- Entities that were closed in the prior period will not be copied to
33 -- the next period. If they are reopened they will be treated as new
34 -- for that period.
35 -----------------------------------------------------------------------------
36
37 -----------------------------------------------------------------------------
38 -- Cursor to process period Job close transaction for all wip entities
39 -- you could have more than one job close transaction in the same
40 -- period but we are concerned with just one because at the end of
41 -- the period we flush out everything from the job if there is at
42 -- least one job close txn in that period. There would be actual cost
43 -- information in WPTCD for the last job close transaction against the
44 -- entity in the period.
45 -- Make sure that PAC record exists for this wip entity id
46 -- If record does not exist it means there has been
47 -- no activity for this entity, therefore no
48 -- wipclose calculations are required. This will be the case
49 -- when the job is defined but no issue/move/assy txns has
50 -- ever been done for the job till this period and it was
51 -- closed.
52 -- Job close in such cases have no fiscal cost impacts.
53 -----------------------------------------------------------------------------
54
55 CURSOR c_jobclose_txn IS
56 SELECT NVL(MAX(wt.transaction_id),-1) txn_id,
57 wt.organization_id org_id,
58 wt.wip_entity_id entity_id
59 FROM wip_transactions wt,
60 wip_entities we
61 WHERE wt.transaction_date BETWEEN TRUNC(p_start_date)
62 AND (TRUNC(p_end_date) + 0.99999)
63 AND wt.transaction_type = 6 --Job Close
64 AND we.wip_entity_id = wt.wip_entity_id
65 AND we.entity_type <> 2 -- Not a rep schedule
66 AND EXISTS (
67 SELECT 'X'
68 FROM wip_pac_period_balances wppb
69 WHERE wppb.pac_period_id = p_pac_period_id
70 AND wppb.cost_group_id = p_cost_group_id
71 AND wppb.wip_entity_id = wt.wip_entity_id
72 )
73 GROUP BY
74 wt.organization_id,
75 wt.wip_entity_id;
76
77
78 -----------------------------------------------------------------------------
79 -- Cursor to select rows for Rep. schedule. Rep. Schedules will be flushed out
80 -- at the end of each period. The balance will be written off to variance.
81 -- There will be no actual cost info in WPTCD as these costs are recognized
82 -- as period expenses and do not have any corresponding txn_id in WT
83 -----------------------------------------------------------------------------
84
85 CURSOR c_schedclose IS
86 SELECT wppb.wip_entity_id entity_id,
87 wppb.cost_group_id cost_group_id,
88 wppb.line_id line_id,
89 wppb.operation_seq_num op_seq_num
90 FROM wip_pac_period_balances wppb
91 WHERE wppb.pac_period_id = p_pac_period_id
92 AND wppb.cost_group_id = p_cost_group_id
93 AND wppb.wip_entity_type = 2; -- Rep. Svhedule
94
95 -----------------------------------------------------------------------------
96 -- Cursor to identify those WIP entities that do not have any assembly
97 -- reference. Such entities will be flushed out at the end of the period
98 -- as a period expense.
99 -----------------------------------------------------------------------------
100
101 CURSOR c_noassy_entity IS
102 SELECT wppb.wip_entity_id entity_id,
103 wppb.line_id,
104 SUM(NVL(wppb.pl_material_in,0)) +
105 SUM(NVL(wppb.pl_material_overhead_in,0)) +
106 SUM(NVL(wppb.pl_resource_in,0)) +
107 SUM(NVL(wppb.pl_outside_processing_in,0)) +
108 SUM(NVL(wppb.pl_overhead_in,0)) +
109 SUM(NVL(wppb.tl_resource_in,0)) +
110 SUM(NVL(wppb.tl_outside_processing_in,0)) +
111 SUM(NVL(wppb.tl_overhead_in,0)) value_in,
112 SUM(NVL(pl_material_out,0)) +
113 SUM(NVL(wppb.pl_material_overhead_out,0)) +
114 SUM(NVL(wppb.pl_resource_out,0)) +
115 SUM(NVL(wppb.pl_outside_processing_out,0)) +
116 SUM(NVL(wppb.pl_overhead_out,0)) +
117 SUM(NVL(wppb.tl_resource_out,0)) +
118 SUM(NVL(wppb.tl_outside_processing_out,0)) +
119 SUM(NVL(wppb.tl_overhead_out,0)) value_out
120 FROM wip_pac_period_balances wppb
121 WHERE wppb.pac_period_id = p_pac_period_id
122 AND wppb.cost_group_id = p_cost_group_id
123 AND EXISTS
124 ( SELECT 'X'
125 FROM wip_entities we
126 WHERE we.wip_entity_id = wppb.wip_entity_id
127 AND we.primary_item_id IS NULL
128 AND we.entity_type not in (6,7) -- Added for R12 PAC eAM enhancement to
129 -- exclude eAM jobs at the PAC period close
130 )
131 GROUP BY
132 wppb.wip_entity_id,
133 wppb.line_id;
134
135
136 BEGIN
137 ----------------------------------------------------------------------
138 -- Initialize Variables
139 ----------------------------------------------------------------------
140
141 l_err_num := 0;
142 l_err_code := '';
143 l_err_msg := '';
144 l_stmt_num := 5;
145
146 ----------------------------------------------------------------------
147 -- Process Job Close Transactions
148 ----------------------------------------------------------------------
149
150 l_stmt_num := 10;
151
152 FOR c_jobclose_rec IN c_jobclose_txn LOOP
153
154 l_stmt_num := 15;
155
156 flush_wip_costs(
157 p_pac_period_id => p_pac_period_id,
158 p_cost_group_id => p_cost_group_id,
159 p_entity_id => c_jobclose_rec.entity_id,
160 p_user_id => p_user_id,
161 p_login_id => p_login_id,
162 p_request_id => p_request_id,
163 p_prog_id => p_prog_id,
164 p_prog_app_id => p_prog_app_id,
165 x_err_num => l_err_num,
166 x_err_code => l_err_code,
167 x_err_msg => l_err_msg);
168
169 IF (l_err_num <>0) THEN
170
171 l_err_msg := SUBSTR('Fail_flush_wip_cost- Job:'
172 ||TO_CHAR(c_jobclose_rec.entity_id)
173 ||':'
174 ||l_err_msg,1,240);
175
176
177 RAISE CST_PROCESS_ERROR;
178
179 END IF;
180
181 l_stmt_num := 20;
182
183 INSERT INTO wip_pac_actual_cost_details wpacd
184 (
185 pac_period_id,
186 cost_group_id,
187 cost_type_id,
188 transaction_id,
189 level_type,
190 cost_element_id,
191 resource_id,
192 basis_resource_id,
193 transaction_costed_date,
194 actual_cost,
195 actual_value,
196 last_update_date,
197 last_updated_by,
198 creation_date,
199 created_by,
200 request_id,
201 program_application_id,
202 program_id,
203 program_update_date,
204 last_update_login
205 )
206 SELECT
207 p_pac_period_id,
208 p_cost_group_id,
209 p_cost_type_id,
210 c_jobclose_rec.txn_id,
211 1, -- Level Type
212 3, -- CE
213 NULL, -- resource_id
214 NULL, -- basis_resource_id
215 SYSDATE,
216 SUM(NVL(wppb.tl_resource_var,0)),
217 NULL, -- applied_value
218 SYSDATE,
219 p_user_id,
220 SYSDATE,
221 p_user_id,
222 p_request_id,
223 p_prog_app_id,
224 p_prog_id,
225 SYSDATE,
226 p_login_id
227 FROM wip_pac_period_balances wppb
228 WHERE wppb.pac_period_id = p_pac_period_id
229 AND wppb.cost_group_id = p_cost_group_id
230 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
231
232 l_stmt_num := 25;
233
234 INSERT INTO wip_pac_actual_cost_details wpacd
235 (
236 pac_period_id,
237 cost_group_id,
238 cost_type_id,
239 transaction_id,
240 level_type,
241 cost_element_id,
242 resource_id,
243 basis_resource_id,
244 transaction_costed_date,
245 actual_cost,
246 actual_value,
247 last_update_date,
248 last_updated_by,
249 creation_date,
250 created_by,
251 request_id,
252 program_application_id,
253 program_id,
254 program_update_date,
255 last_update_login
256 )
257 SELECT
258 p_pac_period_id,
259 p_cost_group_id,
260 p_cost_type_id,
261 c_jobclose_rec.txn_id,
262 1, -- Level Type
263 4, -- CE
264 NULL, -- resource_id
265 NULL, -- basis_resource_id
266 SYSDATE,
267 SUM(NVL(wppb.tl_outside_processing_var,0)),
268 NULL, -- applied_value
269 SYSDATE,
270 p_user_id,
271 SYSDATE,
272 p_user_id,
273 p_request_id,
274 p_prog_app_id,
275 p_prog_id,
276 SYSDATE,
277 p_login_id
278 FROM wip_pac_period_balances wppb
279 WHERE wppb.pac_period_id = p_pac_period_id
280 AND wppb.cost_group_id = p_cost_group_id
281 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
282
283 l_stmt_num := 30;
284
285 INSERT INTO wip_pac_actual_cost_details wpacd
286 (
287 pac_period_id,
288 cost_group_id,
289 cost_type_id,
290 transaction_id,
291 level_type,
292 cost_element_id,
293 resource_id,
294 basis_resource_id,
295 transaction_costed_date,
296 actual_cost,
297 actual_value,
298 last_update_date,
299 last_updated_by,
300 creation_date,
301 created_by,
302 request_id,
303 program_application_id,
304 program_id,
305 program_update_date,
306 last_update_login
307 )
308 SELECT
309 p_pac_period_id,
310 p_cost_group_id,
311 p_cost_type_id,
312 c_jobclose_rec.txn_id,
313 1, -- Level Type
314 5, -- CE
315 NULL, -- resource_id
316 NULL, -- basis_resource_id
317 SYSDATE,
318 SUM(NVL(wppb.tl_overhead_var,0)),
319 NULL, -- applied_value
320 SYSDATE,
321 p_user_id,
322 SYSDATE,
323 p_user_id,
324 p_request_id,
325 p_prog_app_id,
326 p_prog_id,
327 SYSDATE,
328 p_login_id
329 FROM wip_pac_period_balances wppb
330 WHERE wppb.pac_period_id = p_pac_period_id
331 AND wppb.cost_group_id = p_cost_group_id
332 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
333
334 l_stmt_num := 35;
335
336 INSERT INTO wip_pac_actual_cost_details wpacd
337 (
338 pac_period_id,
339 cost_group_id,
340 cost_type_id,
341 transaction_id,
342 level_type,
343 cost_element_id,
344 resource_id,
345 basis_resource_id,
346 transaction_costed_date,
347 actual_cost,
348 actual_value,
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 )
359 SELECT
360 p_pac_period_id,
361 p_cost_group_id,
362 p_cost_type_id,
363 c_jobclose_rec.txn_id,
364 2, -- Level Type
365 1, -- CE
366 NULL, -- resource_id
367 NULL, -- basis_resource_id
368 SYSDATE,
369 SUM(NVL(wppb.pl_material_temp_var,0)),
370 NULL, -- applied_value
371 SYSDATE,
372 p_user_id,
373 SYSDATE,
374 p_user_id,
375 p_request_id,
376 p_prog_app_id,
377 p_prog_id,
378 SYSDATE,
379 p_login_id
380 FROM wip_pac_period_balances wppb
381 WHERE wppb.pac_period_id = p_pac_period_id
382 AND wppb.cost_group_id = p_cost_group_id
383 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
384
385 l_stmt_num := 40;
386
387 INSERT INTO wip_pac_actual_cost_details wpacd
388 (
389 pac_period_id,
390 cost_group_id,
391 cost_type_id,
392 transaction_id,
393 level_type,
394 cost_element_id,
395 resource_id,
396 basis_resource_id,
397 transaction_costed_date,
398 actual_cost,
399 actual_value,
400 last_update_date,
401 last_updated_by,
402 creation_date,
403 created_by,
404 request_id,
405 program_application_id,
406 program_id,
407 program_update_date,
408 last_update_login
409 )
410 SELECT
411 p_pac_period_id,
412 p_cost_group_id,
413 p_cost_type_id,
414 c_jobclose_rec.txn_id,
415 2, -- Level Type
416 2, -- CE
417 NULL, -- resource_id
418 NULL, -- basis_resource_id
419 SYSDATE,
420 SUM(NVL(wppb.pl_material_overhead_temp_var,0)),
421 NULL, -- applied_value
422 SYSDATE,
423 p_user_id,
424 SYSDATE,
425 p_user_id,
426 p_request_id,
427 p_prog_app_id,
428 p_prog_id,
429 SYSDATE,
430 p_login_id
431 FROM wip_pac_period_balances wppb
432 WHERE wppb.pac_period_id = p_pac_period_id
433 AND wppb.cost_group_id = p_cost_group_id
434 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
435
436 l_stmt_num := 45;
437
438 INSERT INTO wip_pac_actual_cost_details wpacd
439 (
440 pac_period_id,
441 cost_group_id,
442 cost_type_id,
443 transaction_id,
444 level_type,
445 cost_element_id,
446 resource_id,
447 basis_resource_id,
448 transaction_costed_date,
449 actual_cost,
450 actual_value,
451 last_update_date,
452 last_updated_by,
453 creation_date,
454 created_by,
455 request_id,
456 program_application_id,
457 program_id,
458 program_update_date,
459 last_update_login
460 )
461 SELECT
462 p_pac_period_id,
463 p_cost_group_id,
464 p_cost_type_id,
465 c_jobclose_rec.txn_id,
466 2, -- Level Type
467 3, -- CE
468 NULL, -- resource_id
469 NULL, -- basis_resource_id
470 SYSDATE,
471 SUM(NVL(wppb.pl_resource_temp_var,0)),
472 NULL, -- applied_value
473 SYSDATE,
474 p_user_id,
475 SYSDATE,
476 p_user_id,
477 p_request_id,
478 p_prog_app_id,
479 p_prog_id,
480 SYSDATE,
481 p_login_id
482 FROM wip_pac_period_balances wppb
483 WHERE wppb.pac_period_id = p_pac_period_id
484 AND wppb.cost_group_id = p_cost_group_id
485 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
486
487 l_stmt_num := 50;
488
489 INSERT INTO wip_pac_actual_cost_details wpacd
490 (
491 pac_period_id,
492 cost_group_id,
493 cost_type_id,
494 transaction_id,
495 level_type,
496 cost_element_id,
497 resource_id,
498 basis_resource_id,
499 transaction_costed_date,
500 actual_cost,
501 actual_value,
502 last_update_date,
503 last_updated_by,
504 creation_date,
505 created_by,
506 request_id,
507 program_application_id,
508 program_id,
509 program_update_date,
510 last_update_login
511 )
512 SELECT
513 p_pac_period_id,
514 p_cost_group_id,
515 p_cost_type_id,
516 c_jobclose_rec.txn_id,
517 2, -- Level Type
518 4, -- CE
519 NULL, -- resource_id
520 NULL, -- basis_resource_id
521 SYSDATE,
522 SUM(NVL(wppb.pl_outside_processing_temp_var,0)),
523 NULL, -- applied_value
524 SYSDATE,
525 p_user_id,
526 SYSDATE,
527 p_user_id,
528 p_request_id,
529 p_prog_app_id,
530 p_prog_id,
531 SYSDATE,
532 p_login_id
533 FROM wip_pac_period_balances wppb
534 WHERE wppb.pac_period_id = p_pac_period_id
535 AND wppb.cost_group_id = p_cost_group_id
536 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
537
538 l_stmt_num := 55;
539
540 INSERT INTO wip_pac_actual_cost_details wpacd
541 (
542 pac_period_id,
543 cost_group_id,
544 cost_type_id,
545 transaction_id,
546 level_type,
547 cost_element_id,
548 resource_id,
549 basis_resource_id,
550 transaction_costed_date,
551 actual_cost,
552 actual_value,
553 last_update_date,
554 last_updated_by,
555 creation_date,
556 created_by,
557 request_id,
558 program_application_id,
559 program_id,
560 program_update_date,
561 last_update_login
562 )
563 SELECT
564 p_pac_period_id,
565 p_cost_group_id,
566 p_cost_type_id,
567 c_jobclose_rec.txn_id,
568 2, -- Level Type
569 5, -- CE
570 NULL, -- resource_id
571 NULL, -- basis_resource_id
572 SYSDATE,
573 SUM(NVL(wppb.pl_overhead_temp_var,0)),
574 NULL, -- applied_value
575 SYSDATE,
576 p_user_id,
577 SYSDATE,
578 p_user_id,
579 p_request_id,
580 p_prog_app_id,
581 p_prog_id,
582 SYSDATE,
583 p_login_id
584 FROM wip_pac_period_balances wppb
585 WHERE wppb.pac_period_id = p_pac_period_id
586 AND wppb.cost_group_id = p_cost_group_id
587 AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
588
589 END LOOP; -- JOBCLOSE_REC loop
590
591 FOR c_schedclose_rec IN c_schedclose LOOP
592
593 l_stmt_num := 60;
594
595 --------------------------------------------------------------
596 -- Flush Out Repetitive Schedule's Costs to variance.
597 --------------------------------------------------------------
598 flush_wip_costs(
599 p_pac_period_id => p_pac_period_id,
600 p_cost_group_id => p_cost_group_id,
601 p_entity_id => c_schedclose_rec.entity_id,
602 p_user_id => p_user_id,
603 p_login_id => p_login_id,
604 p_request_id => p_request_id,
605 p_prog_id => p_prog_id,
606 p_prog_app_id => p_prog_app_id,
607 x_err_num => l_err_num,
608 x_err_code => l_err_code,
609 x_err_msg => l_err_msg);
610
611 IF (l_err_num <>0) THEN
612
613 l_err_msg := SUBSTR('Fail_flush_wip_cost- Repetitive:'
614 ||TO_CHAR(c_schedclose_rec.entity_id)
615 ||':'
616 ||l_err_msg,1,240);
617 RAISE CST_PROCESS_ERROR;
618
619 END IF;
620
621 END LOOP; -- SCHEDCLOSE_REC loop
622
623 ----------------------------------------------------------------------
624 -- Now flush out those non standard entities that
625 -- do not have an assembly reference. Sunch entities
626 -- cannot have an assembly transactions.
627 -- These are treated as period expenses. This information
628 -- will not make it to WPTCD because flushing does not have
629 -- a txn_id in WT.
630 ----------------------------------------------------------------------
631
632
633 l_stmt_num := 65;
634
635 FOR c_noassy_rec IN c_noassy_entity LOOP
636
637 --------------------------------------------------------------
638 -- Flush only if there is value available to flush
639 --------------------------------------------------------------
640
641 IF (c_noassy_rec.value_in <> c_noassy_rec.value_out) THEN
642
643 l_stmt_num := 70;
644
645 flush_wip_costs (
646 p_pac_period_id => p_pac_period_id,
647 p_cost_group_id => p_cost_group_id,
648 p_entity_id => c_noassy_rec.entity_id,
649 p_user_id => p_user_id,
650 p_login_id => p_login_id,
651 p_request_id => p_request_id,
652 p_prog_id => p_prog_id,
653 p_prog_app_id => p_prog_app_id,
654 x_err_num => l_err_num,
655 x_err_code => l_err_code,
656 x_err_msg => l_err_msg);
657
658 IF (l_err_num <> 0) THEN
659
660 l_err_msg := SUBSTR('Fail_flush_wip_cost- Non Std Ent:'
661 ||TO_CHAR(c_noassy_rec.entity_id)
662 ||':'
663 ||l_err_msg,1,240);
664
665 RAISE CST_PROCESS_ERROR;
666
667 END IF;
668
669 END IF; -- check value_in <> value_out
670
671 END LOOP; -- NOASSY_REC loop
672
673
674 EXCEPTION
675
676 WHEN CST_PROCESS_ERROR THEN
677 x_err_num := l_err_num;
678 x_err_code := l_err_code;
679 x_err_msg := l_err_msg;
680
681 WHEN OTHERS THEN
682 ROLLBACK;
683 x_err_num := SQLCODE;
684 x_err_code := NULL;
685 x_err_msg := SUBSTR('CSTPPWCL.process_wip_close_txns('
686 || to_char(l_stmt_num)
687 || '): '
688 ||SQLERRM,1,240);
689 END process_wip_close_txns;
690
691 /*---------------------------------------------------------------------------*
692 | PUBLIC PROCEDURE |
693 | flush_wip_costs |
694 *----------------------------------------------------------------------------*/
695 PROCEDURE flush_wip_costs(
696 p_pac_period_id IN NUMBER,
697 p_cost_group_id IN NUMBER,
698 p_entity_id IN NUMBER,
699 p_user_id IN NUMBER,
700 p_login_id IN NUMBER,
701 p_request_id IN NUMBER,
702 p_prog_id IN NUMBER DEFAULT -1,
703 p_prog_app_id IN NUMBER DEFAULT -1,
704 x_err_num OUT NOCOPY NUMBER,
705 x_err_code OUT NOCOPY VARCHAR2,
706 x_err_msg OUT NOCOPY VARCHAR2)
707 IS
708
709 l_stmt_num NUMBER;
710 l_err_num NUMBER;
711 l_err_code VARCHAR2(240);
712 l_err_msg VARCHAR2(240);
713
714 BEGIN
715 ----------------------------------------------------------------------
716 -- Initialize Variables
717 ----------------------------------------------------------------------
718
719 l_err_num := 0;
720 l_err_code := '';
721 l_err_msg := '';
722
723 ----------------------------------------------------------------------
724 -- Flush out WIP entity's costs, write off to variance
725 ----------------------------------------------------------------------
726
727 -- Update the PL variance TEMP columns. TEMPVar = IN- - OUT - VAR
728 -- Because if Cost type is based on BOM based Algo, then if Job has negative balance
729 -- VAR columns will be updated and this amount will be flush to Variance account
730 -- while processing the Assembly txns
731 -- So only left value in the Job should be flushed to TEMP Variance
732 --------------------------------------------------------------------------------------
733 l_stmt_num := 5;
734
735 UPDATE wip_pac_period_balances wppb
736 SET tl_resource_var = NVL(tl_resource_in,0)
737 - NVL(tl_resource_out,0),
738 tl_outside_processing_var = NVL(tl_outside_processing_in,0)
739 - NVL(tl_outside_processing_out,0),
740 tl_overhead_var = NVL(tl_overhead_in,0) - NVL(tl_overhead_out,0),
741
742 pl_material_var = NVL(pl_material_in,0) - NVL(pl_material_out,0),
743
744 pl_material_overhead_var = NVL(pl_material_overhead_in,0) - NVL(pl_material_overhead_out,0),
745
746 pl_resource_var = NVL(pl_resource_in,0) - NVL(pl_resource_out,0),
747
748 pl_outside_processing_var = NVL(pl_outside_processing_in,0) - NVL(pl_outside_processing_out,0),
749
750 pl_overhead_var = NVL(pl_overhead_in,0) - NVL(pl_overhead_out,0),
751
752 -- Update the vartemp columns with Actual variance during job close
753 -- var columns contains total variance
754
755 pl_material_temp_var = NVL(pl_material_in,0) - NVL(pl_material_out,0)
756 - NVL(pl_material_var,0),
757
758 pl_material_overhead_temp_var = NVL(pl_material_overhead_in,0) - NVL(pl_material_overhead_out,0)
759 - NVL(pl_material_overhead_var,0),
760 pl_resource_temp_var = NVL(pl_resource_in,0) - NVL(pl_resource_out,0)
761 - NVL(pl_resource_var,0),
762 pl_outside_processing_temp_var = NVL(pl_outside_processing_in,0) - NVL(pl_outside_processing_out,0)
763 - NVL(pl_outside_processing_var,0),
764 pl_overhead_temp_var = NVL(pl_overhead_in,0) - NVL(pl_overhead_out,0)
765 - NVL(pl_overhead_var,0),
766
767 request_id = p_request_id,
768 last_update_date = SYSDATE,
769 program_update_date = SYSDATE
770 WHERE wppb.pac_period_id = p_pac_period_id
771 AND wppb.cost_group_id = p_cost_group_id
772 AND wppb.wip_entity_id = p_entity_id;
773
774 EXCEPTION
775
776 WHEN OTHERS THEN
777 x_err_num := SQLCODE;
778 x_err_code := NULL;
779 x_err_msg := SUBSTR('CSTPPWCL.flush_wip_costs('
780 || to_char(l_stmt_num)
781 || '): '
782 ||SQLERRM,1,240);
783 END flush_wip_costs;
784
785 END cstppwcl;