[Home] [Help]
PACKAGE BODY: APPS.CSTPFCHK
Source
1 PACKAGE BODY CSTPFCHK AS
2 -- $Header: CSTFCHKB.pls 120.8 2011/08/03 08:48:24 pbasrani ship $
3 --+===========================================================================+
4 --| |
5 --| Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| File Name : CSTFCHKB.pls |
10 --| Description : Cost Method specific processing extension |
11 --| |
12 --| Revision |
13 --| 11/12/98 Jung Ha Creation |
14 --| 2/5/99 DHerring Added Content to Hooks |
15 --| 3/5/99 DHerring 1. copy_prior_info_hook (line 1109) |
16 --| set beginning balance to 0 |
17 --| CST_PAC_ITEM_COST.begin_item_cost = 0 |
18 --| 2. calc_pac_cost_hook (line 710) |
19 --| set item_cost to 0. This column is |
20 --| populated from the worker by |
21 --| CST_MGD_LIFO_COST_PROCESSOR.calc_lifo_cost |
22 --| 3. calc_pac_cost_hook (line 749) |
23 --| set layer_quantity to 0. This column is |
24 --| populated from the worker by |
25 --| CST_MGD_LIFO_COST_PROCESSOR.calc_lifo_cost |
26 --| 5/29/99 DHerring Altered copy_prior_info hook. CPIC.make_quantity |
27 --| and CPIC.buy_quantity are set to 0. This ensures |
28 --| that the weighted average cost is only calculated |
29 --| for items bought or sold in the current period |
30 --| 08/21/2001 vjavli issue_quantity initialized to 0 |
31 --| part of bug#1929915 fix |
32 --| 11/22/2004 vjavli Bug#3942504 fix: Tracking bug |
33 --| OUT NOCOPY added as part of arcs checkin stds |
34 --| Original bug#3775498 fix in 11.5.8 |
35 --| initializing begin_layer_quantity not required |
36 --| 08/03/2005 vjavli Added procedure periodic_cost_update_hook which is|
37 --| a copy from BOM115100 version to prevent any |
38 --| regression. The regression is due to cppb insert,|
39 --| update apis introduced in R12 code for PWAC method|
40 --| 11/27/2005 vjavli copy_prior_info_hook: insert new column |
41 --| unrelieved_scrap_quantity into wip_pac_period_ |
42 --| balances. Insert new table: CST_PAC_REQ_OPER_ |
43 --| COST_DETAILS |
44 --| 01/07/2005 vjavli FP:11i8-12.0:Bug 4028737 fix:begin layer quantity |
45 --| initialized to total layer quantity of previous |
46 --| period. Base bug 3775498 fix. |
47 --| 05/24/2006 vmutyala Bug 5239716 : Replaced Non Mergeable View |
48 --| CST_PAC_ITEM_COSTS_V with base table |
49 --| CST_PAC_ITEM_COST_DETAILS |
50 --| 02/07/2008 vjavli Bug 6751847 performance fix: i_txn_category |
51 --| parameter added to procedures compute_pac_cost_ |
52 --| hook, calc_pac_cost_hook and periodic_cost_update_|
53 --| hook. i_txn_category added while insert into |
54 --| MPACD; CSTPPWAC.apply_material_ovhd definition |
55 --| changed to add i_txn_category |
56 --+===========================================================================+
57
58 -- FUNCTION
59 -- compute_pac_cost_hook
60 --
61 function compute_pac_cost_hook(
62 I_PAC_PERIOD_ID IN NUMBER,
63 I_ORG_ID IN NUMBER,
64 I_COST_GROUP_ID IN NUMBER,
65 I_COST_TYPE_ID IN NUMBER,
66 I_TXN_ID IN NUMBER,
67 I_COST_LAYER_ID IN NUMBER,
68 I_PAC_RATES_ID IN NUMBER,
69 I_ITEM_ID IN NUMBER,
70 I_TXN_QTY IN NUMBER,
71 I_TXN_ACTION_ID IN NUMBER,
72 I_TXN_SRC_TYPE_ID IN NUMBER,
73 I_INTERORG_REC IN NUMBER,
74 I_ACROSS_CGS IN NUMBER,
75 I_EXP_FLAG IN NUMBER,
76 I_USER_ID IN NUMBER,
77 I_LOGIN_ID IN NUMBER,
78 I_REQ_ID IN NUMBER,
79 I_PRG_APPL_ID IN NUMBER,
80 I_PRG_ID IN NUMBER,
81 I_TXN_CATEGORY IN NUMBER,
82 O_Err_Num OUT NOCOPY NUMBER,
83 O_Err_Code OUT NOCOPY VARCHAR2,
84 O_Err_Msg OUT NOCOPY VARCHAR2
85 )
86 return integer IS
87 l_ret_val NUMBER;
88 l_level NUMBER;
89 l_txn_cost_exist NUMBER;
90 l_cost_details NUMBER;
91 l_err_num NUMBER;
92 l_err_code VARCHAR2(240);
93 l_err_msg VARCHAR2(240);
94 l_stmt_num NUMBER;
95 process_error EXCEPTION;
96
97 BEGIN
98 -- initialize local variables
99 l_err_num := 0;
100 l_err_code := '';
101 l_err_msg := '';
102 l_txn_cost_exist := 0;
103 l_cost_details := 0;
104
105 l_stmt_num := 10;
106
107 select count(*)
108 into l_txn_cost_exist
109 from mtl_pac_txn_cost_details
110 where transaction_id = i_txn_id
111 and cost_group_id = i_cost_group_id
112 and pac_period_id = i_pac_period_id;
113
114 if (l_txn_cost_exist > 0) then
115
116 l_ret_val := 1;
117 l_stmt_num := 20;
118
119 INSERT INTO mtl_pac_actual_cost_details (
120 transaction_id,
121 pac_period_id,
122 cost_type_id,
123 cost_group_id,
124 cost_layer_id,
125 cost_element_id,
126 level_type,
127 last_update_date,
128 last_updated_by,
129 creation_date,
130 created_by,
131 last_update_login,
132 request_id,
133 program_application_id,
134 program_id,
135 program_update_date,
136 inventory_item_id,
137 actual_cost,
138 prior_cost,
139 prior_buy_cost,
140 prior_make_cost,
141 new_cost,
142 new_buy_cost,
143 new_make_cost,
144 insertion_flag,
145 user_entered,
146 transaction_costed_date,
147 txn_category)
148 SELECT
149 i_txn_id,
150 i_pac_period_id,
151 i_cost_type_id,
152 i_cost_group_id,
153 i_cost_layer_id,
154 mptcd.cost_element_id,
155 mptcd.level_type,
156 sysdate,
157 i_user_id,
158 sysdate,
159 i_user_id,
160 i_login_id,
161 i_req_id,
162 i_prg_appl_id,
163 i_prg_id,
164 sysdate,
165 mptcd.inventory_item_id,
166 mptcd.transaction_cost,
167 0,
168 0,
169 0,
170 NULL,
171 NULL,
172 NULL,
173 'Y',
174 'N',
175 NULL,
176 i_txn_category
177 FROM mtl_pac_txn_cost_details mptcd
178 WHERE transaction_id = i_txn_id
179 AND pac_period_id = i_pac_period_id
180 AND cost_group_id = i_cost_group_id;
181
182 else
183 l_ret_val := 0;
184
185 /********************************************************************
186 ** Create detail rows in MTL_PAC_ACTUAL_COST_DETAILS based on **
187 ** records in CST_PAC_ITEM_COST_DETAILS. Since we are using **
188 ** current average the actual cost, prior cost and new cost are **
189 ** all the same. **
190 ** If detail rows do not exist in CST_PAC_ITEM_COST_DETAILS, **
191 ** we will insert a TL material 0 cost layer. **
192 ********************************************************************/
193
194 l_stmt_num := 30;
195
196 select count(*)
197 into l_cost_details
198 from cst_pac_item_cost_details
199 where cost_layer_id = i_cost_layer_id;
200
201 if (l_cost_details > 0) then
202
203 l_stmt_num := 40;
204
205
206 INSERT INTO mtl_pac_actual_cost_details (
207 transaction_id,
208 pac_period_id,
209 cost_type_id,
210 cost_group_id,
211 cost_layer_id,
212 cost_element_id,
213 level_type,
214 last_update_date,
215 last_updated_by,
216 creation_date,
217 created_by,
218 last_update_login,
219 request_id,
220 program_application_id,
221 program_id,
222 program_update_date,
223 inventory_item_id,
224 actual_cost,
225 prior_cost,
226 prior_buy_cost,
227 prior_make_cost,
228 new_cost,
229 new_buy_cost,
230 new_make_cost,
231 insertion_flag,
232 user_entered,
233 transaction_costed_date,
234 txn_category)
235 SELECT
236 i_txn_id,
237 i_pac_period_id,
241 cpicd.cost_element_id,
238 i_cost_type_id,
239 i_cost_group_id,
240 i_cost_layer_id,
242 cpicd.level_type,
243 sysdate,
244 i_user_id,
245 sysdate,
246 i_user_id,
247 i_login_id,
248 i_req_id,
249 i_prg_appl_id,
250 i_prg_id,
251 sysdate,
252 i_item_id,
253 cpicd.item_cost,
254 cpicd.item_cost,
255 cpicd.item_buy_cost,
256 cpicd.item_make_cost,
257 cpicd.item_cost,
258 cpicd.item_buy_cost,
259 cpicd.item_make_cost,
260 'N',
261 'N',
262 NULL,
263 i_txn_category
264 FROM cst_pac_item_cost_details cpicd
265 WHERE cpicd.cost_layer_id = i_cost_layer_id;
266
267 else
268 l_stmt_num := 50;
269
270 INSERT INTO mtl_pac_actual_cost_details (
271 transaction_id,
272 pac_period_id,
273 cost_type_id,
274 cost_group_id,
275 cost_layer_id,
276 cost_element_id,
277 level_type,
278 last_update_date,
279 last_updated_by,
280 creation_date,
281 created_by,
282 last_update_login,
283 request_id,
284 program_application_id,
285 program_id,
286 program_update_date,
287 inventory_item_id,
288 actual_cost,
289 prior_cost,
290 prior_buy_cost,
291 prior_make_cost,
292 new_cost,
293 new_buy_cost,
294 new_make_cost,
295 insertion_flag,
296 user_entered,
297 transaction_costed_date,
298 txn_category)
299 VALUES(
300 i_txn_id,
301 i_pac_period_id,
302 i_cost_type_id,
303 i_cost_group_id,
304 i_cost_layer_id,
305 1,
306 1,
307 sysdate,
308 i_user_id,
309 sysdate,
310 i_user_id,
311 i_login_id,
312 i_req_id,
313 i_prg_appl_id,
314 i_prg_id,
315 sysdate,
316 i_item_id,
317 0,
318 NULL,
319 NULL,
320 NULL,
321 0,
322 0,
323 0,
324 'N',
325 'N',
326 NULL,
327 i_txn_category);
328 end if;
329
330 end if;
331
332
333 -- Apply material overhead to certain txns which are asset item and
334 -- asset subinventory
335 if ((i_exp_flag <> 1) AND
336 ((i_txn_action_id = 27 and i_txn_src_type_id = 1) or -- PO receipt
337 (i_txn_action_id = 1 and i_txn_src_type_id = 1) or -- RTV
338 (i_txn_action_id = 29 and i_txn_src_type_id = 1) or -- Delivery Adj
339 (i_txn_action_id = 31 and i_txn_src_type_id = 5) or -- WIP completions
340 (i_txn_action_id = 32 and i_txn_src_type_id = 5) or -- Assembly return
341 (i_across_cgs = 1 and i_interorg_rec = 1)) -- Across CGs and Ownership
342 ) then -- changes
343
344 l_level := 1;
345
346 CSTPPWAC.apply_material_ovhd(
347 i_pac_period_id,
348 i_org_id,
349 i_cost_group_id,
350 i_cost_type_id,
351 i_txn_id,
352 i_cost_layer_id,
353 i_pac_rates_id,
354 i_item_id,
355 i_txn_qty,
356 l_level,
357 i_user_id,
358 i_login_id,
359 i_req_id,
360 i_prg_appl_id,
361 i_prg_id,
362 i_txn_category,
363 l_err_num,
364 l_err_code,
365 l_err_msg);
366
367 if (l_err_num <> 0) then
368 -- Error occured
369 raise process_error;
370 end if;
371 l_ret_val := 1;
372 end if;
373
374 return l_ret_val;
375
376 EXCEPTION
377 when process_error then
378 o_err_num := l_err_num;
379 o_err_code := l_err_code;
380 o_err_msg := l_err_msg;
381 return l_ret_val;
382 when OTHERS then
383 rollback;
384 o_err_num := SQLCODE;
385 o_err_msg := 'CSTPFCHK.Compute_Pac_Cost_Hook ('|| to_char(l_stmt_num) || '): '
386 || substr(SQLERRM,1,200);
387 return l_ret_val;
388 END compute_pac_cost_hook;
389
390
391
392 -- PROCEDURE
393 -- calc_pac_cost_hook
394 --
395 procedure calc_pac_cost_hook(
396 I_PAC_PERIOD_ID IN NUMBER,
397 I_COST_GROUP_ID IN NUMBER,
398 I_COST_TYPE_ID IN NUMBER,
399 I_TXN_ID IN NUMBER,
400 I_COST_LAYER_ID IN NUMBER,
401 I_QTY_LAYER_ID IN NUMBER,
402 I_ITEM_ID IN NUMBER,
403 I_TXN_QTY IN NUMBER,
404 I_ISSUE_QTY IN NUMBER,
405 I_BUY_QTY IN NUMBER,
406 I_MAKE_QTY IN NUMBER,
407 I_USER_ID IN NUMBER,
408 I_LOGIN_ID IN NUMBER,
409 I_REQ_ID IN NUMBER,
410 I_PRG_APPL_ID IN NUMBER,
411 I_PRG_ID IN NUMBER,
412 I_TXN_CATEGORY IN NUMBER,
413 O_Err_Num OUT NOCOPY NUMBER,
414 O_Err_Code OUT NOCOPY VARCHAR2,
415 O_Err_Msg OUT NOCOPY VARCHAR2
416 ) IS
417 l_cur_onhand NUMBER;
418 l_cur_buy_qty NUMBER;
419 l_cur_make_qty NUMBER;
420 l_new_onhand NUMBER;
421 l_new_buy_qty NUMBER;
422 l_new_make_qty NUMBER;
423
424 l_err_num NUMBER;
425 l_err_code VARCHAR2(240);
426 l_err_msg VARCHAR2(240);
427 l_stmt_num NUMBER;
428 process_error EXCEPTION;
429
430 BEGIN
431 -- initialize local variables
432 l_err_num := 0;
433 l_err_code := '';
434 l_err_msg := '';
435 l_cur_onhand := 0;
436 l_cur_buy_qty := 0;
437 l_cur_make_qty := 0;
438 l_new_onhand := 0;
439 l_new_buy_qty := 0;
440 l_new_make_qty := 0;
441
442
446 ** in to mtl_pac_actual_cost_details the current average cost for **
443 /********************************************************************
444 ** Update mtl_pac_actual_cost_details and update the prior cost **
445 ** to the current average for the elements that exists and insert **
447 ** the elements that do not exist. **
448 ********************************************************************/
449
450 l_stmt_num := 10;
451
452 UPDATE mtl_pac_actual_cost_details mpacd
453 SET prior_cost = 0,
454 prior_buy_cost = 0,
455 prior_make_cost = 0,
456 new_cost = NULL,
457 new_buy_cost = NULL,
458 new_make_cost = NULL
459 WHERE mpacd.transaction_id = i_txn_id
460 AND mpacd.cost_group_id = i_cost_group_id
461 AND mpacd.cost_layer_id = i_cost_layer_id;
462
463 l_stmt_num := 20;
464
465 UPDATE mtl_pac_actual_cost_details mpacd
466 SET (prior_cost,
467 prior_buy_cost,
468 prior_make_cost,
469 insertion_flag) =
470 (SELECT cpicd.item_cost,
471 cpicd.item_buy_cost,
472 cpicd.item_make_cost,
473 'N'
474 FROM cst_pac_item_cost_details cpicd
475 WHERE cpicd.cost_layer_id = i_cost_layer_id
476 AND cpicd.cost_element_id = mpacd.cost_element_id
477 AND cpicd.level_type = mpacd.level_type)
478 WHERE mpacd.transaction_id = i_txn_id
479 AND mpacd.cost_group_id = i_cost_group_id
480 AND mpacd.cost_layer_id = i_cost_layer_id
481 AND EXISTS
482 (SELECT 'there is details in cpicd'
483 FROM cst_pac_item_cost_details cpicd
484 WHERE cpicd.cost_layer_id = i_cost_layer_id
485 AND cpicd.cost_element_id = mpacd.cost_element_id
486 AND cpicd.level_type = mpacd.level_type);
487
488 l_stmt_num := 30;
489
490 INSERT INTO mtl_pac_actual_cost_details (
491 transaction_id,
492 pac_period_id,
493 cost_type_id,
494 cost_group_id,
495 cost_layer_id,
496 cost_element_id,
497 level_type,
498 last_update_date,
499 last_updated_by,
500 creation_date,
501 created_by,
502 last_update_login,
503 request_id,
504 program_application_id,
505 program_id,
506 program_update_date,
507 inventory_item_id,
508 actual_cost,
509 prior_cost,
510 prior_buy_cost,
511 prior_make_cost,
512 new_cost,
513 new_buy_cost,
514 new_make_cost,
515 insertion_flag,
516 user_entered,
517 transaction_costed_date,
518 txn_category)
519 SELECT i_txn_id,
520 i_pac_period_id,
521 i_cost_type_id,
522 i_cost_group_id,
523 i_cost_layer_id,
524 cpicd.cost_element_id,
525 cpicd.level_type,
526 sysdate,
527 i_user_id,
528 sysdate,
529 i_user_id,
530 i_login_id,
531 i_req_id,
532 i_prg_appl_id,
533 i_prg_id,
534 sysdate,
535 i_item_id,
536 0,
537 cpicd.item_cost,
538 cpicd.item_buy_cost,
539 cpicd.item_make_cost,
540 NULL,
541 NULL,
542 NULL,
543 'N',
544 'N',
545 NULL,
546 i_txn_category
547 FROM cst_pac_item_cost_details cpicd
548 WHERE cost_layer_id = i_cost_layer_id
549 AND NOT EXISTS
550 (SELECT 'this detail is not in mpacd already'
551 FROM mtl_pac_actual_cost_details mpacd
552 WHERE mpacd.transaction_id = i_txn_id
553 AND mpacd.cost_group_id = i_cost_group_id
554 AND mpacd.cost_layer_id = i_cost_layer_id
555 AND mpacd.cost_element_id = cpicd.cost_element_id
556 AND mpacd.level_type = cpicd.level_type);
557
558
559 /********************************************************************
560 ** Compute new average cost. **
561 ********************************************************************/
562 l_stmt_num := 40;
563
564 SELECT total_layer_quantity,
565 buy_quantity,
566 make_quantity
567 INTO l_cur_onhand,
568 l_cur_buy_qty,
569 l_cur_make_qty
570 FROM cst_pac_item_costs
571 WHERE cost_layer_id = i_cost_layer_id;
572
573
574 /********************************************************************
575 ** Update Item costs and Quantity **
576 ********************************************************************/
577 l_new_onhand := l_cur_onhand + i_txn_qty;
578 l_new_buy_qty := l_cur_buy_qty + i_buy_qty;
579 l_new_make_qty := l_cur_make_qty + i_make_qty;
580
581
582 l_stmt_num := 50;
583
584 UPDATE mtl_pac_actual_cost_details mpacd
585 SET new_cost =
586 decode(sign(l_cur_onhand),-1,
587 decode(sign(i_txn_qty), -1,
588 (mpacd.prior_cost*l_cur_onhand + mpacd.actual_cost*i_txn_qty)/l_new_onhand,
589 decode(sign(l_new_onhand),-1, mpacd.prior_cost,
590 mpacd.actual_cost)),
591 decode(sign(i_txn_qty), -1,
592 decode(sign(l_new_onhand), 1,
593 decode(sign((mpacd.prior_cost*l_cur_onhand + mpacd.actual_cost*i_txn_qty)/l_new_onhand),1,
594 (mpacd.prior_cost*l_cur_onhand + mpacd.actual_cost*i_txn_qty)/l_new_onhand,
595 0)
599 decode(sign(l_cur_buy_qty),-1,
596 ,mpacd.actual_cost),
597 (mpacd.prior_cost*l_cur_onhand + mpacd.actual_cost*i_txn_qty)/l_new_onhand)),
598 new_buy_cost =
600 decode(sign(i_buy_qty), -1,
601 (mpacd.prior_buy_cost*l_cur_buy_qty + mpacd.actual_cost*i_buy_qty)/l_new_buy_qty,
602 decode(sign(l_new_buy_qty),-1, mpacd.prior_buy_cost,
603 mpacd.actual_cost)),
604 decode(sign(i_buy_qty), -1,
605 decode(sign(l_new_buy_qty), 1,
606 decode(sign((mpacd.prior_buy_cost*l_cur_buy_qty + mpacd.actual_cost*i_buy_qty)/l_new_buy_qty),1,
607 (mpacd.prior_buy_cost*l_cur_buy_qty + mpacd.actual_cost*i_buy_qty)/l_new_buy_qty,
608 0)
609 ,mpacd.actual_cost),
610 (mpacd.prior_buy_cost*l_cur_buy_qty + mpacd.actual_cost*i_buy_qty)/decode(l_new_buy_qty,0,1,l_new_buy_qty))),
611 new_make_cost =
612 decode(sign(l_cur_make_qty),-1,
613 decode(sign(i_make_qty), -1,
614 (mpacd.prior_make_cost*l_cur_make_qty + mpacd.actual_cost*i_make_qty)/l_new_make_qty,
615 decode(sign(l_new_make_qty),-1, mpacd.prior_make_cost,
616 mpacd.actual_cost)),
617 decode(sign(i_make_qty), -1,
618 decode(sign(l_new_make_qty), 1,
619 decode(sign((mpacd.prior_make_cost*l_cur_make_qty + mpacd.actual_cost*i_make_qty)/l_new_make_qty),1,
620 (mpacd.prior_make_cost*l_cur_make_qty + mpacd.actual_cost*i_make_qty)/l_new_make_qty,
621 0)
622 ,mpacd.actual_cost),
623 (mpacd.prior_make_cost*l_cur_make_qty + mpacd.actual_cost*i_make_qty)/decode(l_new_make_qty,0,1,l_new_make_qty)))
624 WHERE mpacd.transaction_id = i_txn_id
625 AND mpacd.cost_layer_id = i_cost_layer_id;
626
627
628 l_stmt_num := 60;
629
630 UPDATE cst_pac_item_cost_details cpicd
631 SET (last_update_date,
632 last_updated_by,
633 last_update_login,
634 request_id,
635 program_application_id,
636 program_id,
637 program_update_date,
638 item_cost,
639 item_buy_cost,
640 item_make_cost) =
641 (SELECT sysdate,
642 i_user_id,
643 i_login_id,
644 i_req_id,
645 i_prg_appl_id,
646 i_prg_id,
647 sysdate,
648 new_cost,
649 new_buy_cost,
650 new_make_cost
651 FROM mtl_pac_actual_cost_details mpacd
652 WHERE mpacd.transaction_id = i_txn_id
653 AND mpacd.cost_group_id = i_cost_group_id
654 AND mpacd.cost_layer_id = i_cost_layer_id
655 AND mpacd.cost_element_id = cpicd.cost_element_id
656 AND mpacd.level_type = cpicd.level_type)
657 WHERE cpicd.cost_layer_id = i_cost_layer_id;
658
659 l_stmt_num := 70;
660
661 INSERT INTO cst_pac_item_cost_details(
662 cost_layer_id,
663 cost_element_id,
664 level_type,
665 last_update_date,
666 last_updated_by,
667 creation_date,
668 created_by,
669 last_update_login,
670 request_id,
671 program_application_id,
672 program_id,
673 program_update_date,
674 item_cost,
675 item_buy_cost,
676 item_make_cost)
677 SELECT i_cost_layer_id,
678 mpacd.cost_element_id,
679 mpacd.level_type,
680 sysdate,
681 i_user_id,
682 sysdate,
683 i_user_id,
684 i_login_id,
685 i_req_id,
686 i_prg_appl_id,
687 i_prg_id,
688 sysdate,
689 mpacd.new_cost,
690 mpacd.new_buy_cost,
691 mpacd.new_make_cost
692 FROM mtl_pac_actual_cost_details mpacd
693 WHERE mpacd.transaction_id = i_txn_id
694 AND mpacd.cost_group_id = i_cost_group_id
695 AND mpacd.cost_layer_id = i_cost_layer_id
696 AND mpacd.insertion_flag = 'Y';
697
698
699 /********************************************************************
700 ** Update layer quantity and layer costs information **
701 ********************************************************************/
702 l_stmt_num := 80;
703
704 UPDATE cst_pac_item_costs cpic
705 SET (last_updated_by,
706 last_update_date,
707 last_update_login,
708 request_id,
709 program_application_id,
710 program_id,
711 program_update_date,
712 total_layer_quantity,
713 issue_quantity,
714 buy_quantity,
715 make_quantity,
716 pl_material,
717 pl_material_overhead,
718 pl_resource,
719 pl_outside_processing,
720 pl_overhead,
721 tl_material,
722 tl_material_overhead,
723 tl_resource,
724 tl_outside_processing,
725 tl_overhead,
726 material_cost,
727 material_overhead_cost,
728 resource_cost,
729 outside_processing_cost,
730 overhead_cost,
731 pl_item_cost,
732 tl_item_cost,
733 item_cost,
734 item_buy_cost,
735 item_make_cost,
736 unburdened_cost,
737 burden_cost) =
738 (SELECT
739 i_user_id,
740 sysdate,
741 i_login_id,
742 i_req_id,
743 i_prg_appl_id,
744 i_prg_id,
745 sysdate,
746 l_new_onhand,
747 issue_quantity + i_issue_qty,
748 buy_quantity + i_buy_qty,
749 make_quantity + i_make_qty,
753 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
750 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
751 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
752 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
754 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
755 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
756 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
757 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
758 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
759 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
760 SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST,0)),
761 SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST,0)),
762 SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST,0)),
763 SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST,0)),
764 SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST,0)),
765 SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)),
766 SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)),
767 -- The following value is a change from the
768 -- code for PAC. 0 is inserted into
769 -- CST_PAC_ITEM_COSTS.item_cost.
770 0,
771 SUM(ITEM_BUY_COST),
772 SUM(ITEM_MAKE_COST),
773 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,2,ITEM_COST,0),ITEM_COST)),
774 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0))
775 FROM CST_PAC_ITEM_COST_DETAILS v
776 WHERE v.cost_layer_id = i_cost_layer_id
780 (SELECT 'there is detail cost'
777 GROUP BY COST_LAYER_ID)
778 WHERE cpic.cost_layer_id = i_cost_layer_id
779 AND EXISTS
781 FROM cst_pac_item_cost_details cpicd
782 WHERE cpicd.cost_layer_id = i_cost_layer_id);
783
784
785 l_stmt_num := 90;
786
787 --===========================================================
788 -- The following statement is not required in incremental
789 -- lifo as layer_quantity is populated in package
790 -- CST_MGD_LIFO_COST_PROCESSOR.populate_layers
791 --===========================================================
792 -- UPDATE cst_pac_quantity_layers cpql
793 -- SET (last_updated_by,
794 -- last_update_date,
795 -- last_update_login,
796 -- request_id,
797 -- program_application_id,
798 -- program_id,
799 -- program_update_date,
800 -- layer_quantity) =
801 -- (SELECT
802 -- i_user_id,
803 -- sysdate,
804 -- i_login_id,
805 -- i_req_id,
806 -- i_prg_appl_id,
807 -- i_prg_id,
808 -- sysdate,
809 -- l_new_onhand
810 -- FROM sys.dual)
811 -- WHERE cpql.quantity_layer_id = i_qty_layer_id
812 -- AND EXISTS
813 -- (SELECT 'there is a layer'
814 -- FROM cst_pac_quantity_layers cpql
815 -- WHERE cpql.quantity_layer_id = i_qty_layer_id);
816 --===========================================================
817 -- The statement has not been deleted for comparison reasons
818 --===========================================================
819
820 EXCEPTION
821 when process_error then
822 o_err_num := l_err_num;
823 o_err_code := l_err_code;
824 o_err_msg := l_err_msg;
825 when OTHERS then
826 rollback;
827 o_err_num := SQLCODE;
828 o_err_msg := 'CSTPPWAC.Calc_PWAC_Cost (' || to_char(l_stmt_num) || '): '
829 || substr(SQLERRM,1,200);
830
831 END calc_pac_cost_hook;
832
833
834 -- PROCEDURE
835 -- current_pac_cost_hook
836 --
837 procedure current_pac_cost_hook(
838 I_COST_LAYER_ID IN NUMBER,
839 I_QTY_LAYER_ID IN NUMBER,
840 I_TXN_QTY IN NUMBER,
841 I_ISSUE_QTY IN NUMBER,
842 I_BUY_QTY IN NUMBER,
843 I_MAKE_QTY IN NUMBER,
844 I_TXN_ACTION_ID IN NUMBER,
845 I_EXP_FLAG IN NUMBER,
846 I_NO_UPDATE_QTY IN NUMBER,
850 I_PRG_APPL_ID IN NUMBER,
847 I_USER_ID IN NUMBER,
848 I_LOGIN_ID IN NUMBER,
849 I_REQ_ID IN NUMBER,
851 I_PRG_ID IN NUMBER,
852 O_Err_Num OUT NOCOPY NUMBER,
853 O_Err_Code OUT NOCOPY VARCHAR2,
854 O_Err_Msg OUT NOCOPY VARCHAR2
855 ) IS
856 l_err_num NUMBER;
857 l_err_code VARCHAR2(240);
858 l_err_msg VARCHAR2(240);
859 l_stmt_num NUMBER;
860 process_error EXCEPTION;
861
862 BEGIN
863 -- initialize local variables
864 l_err_num := 0;
865 l_err_code := '';
866 l_err_msg := '';
867
868
869 if ((i_txn_action_id = 30) or (i_no_update_qty = 1) or (i_exp_flag = 1)) then
870 return;
871 else
872
873 l_stmt_num := 10;
874
875 UPDATE cst_pac_item_costs cpic
876 SET last_update_date = sysdate,
877 last_updated_by = i_user_id,
878 last_update_login = i_login_id,
879 request_id = i_req_id,
880 program_application_id = i_prg_appl_id,
881 program_id = i_prg_id,
882 program_update_date = sysdate,
883 total_layer_quantity = total_layer_quantity + i_txn_qty,
884 issue_quantity = issue_quantity + i_issue_qty,
885 buy_quantity = buy_quantity + i_buy_qty,
886 make_quantity = make_quantity + i_make_qty
887 WHERE cpic.cost_layer_id = i_cost_layer_id;
888
889
890 l_stmt_num := 20;
891
892 UPDATE cst_pac_quantity_layers cpql
893 SET last_update_date = sysdate,
894 last_updated_by = i_user_id,
895 last_update_login = i_login_id,
896 request_id = i_req_id,
897 program_application_id = i_prg_appl_id,
898 program_id = i_prg_id,
899 program_update_date = sysdate,
900 layer_quantity = layer_quantity + i_txn_qty
901 WHERE cpql.quantity_layer_id = i_qty_layer_id;
902
903 end if;
904
905 EXCEPTION
906 when process_error then
907 o_err_num := l_err_num;
908 o_err_code := l_err_code;
909 o_err_msg := l_err_msg;
910 when OTHERS then
911 rollback;
912 o_err_num := SQLCODE;
913 o_err_msg := 'CSTPFCHK.Current_Pac_Cost_Hook ('|| to_char(l_stmt_num) || '): '
914 || substr(SQLERRM,1,200);
915
916 END current_pac_cost_hook;
917
918 -- FUNCTION
919 -- pac_wip_issue_cost_hook
920 --
921 FUNCTION pac_wip_issue_cost_hook(
922 I_PAC_PERIOD_ID IN NUMBER,
923 I_ORG_ID IN NUMBER,
924 I_COST_GROUP_ID IN NUMBER,
925 I_COST_TYPE_ID IN NUMBER,
926 I_COST_METHOD IN NUMBER,
927 I_TXN_ID IN NUMBER,
928 I_COST_LAYER_ID IN NUMBER,
929 I_QTY_LAYER_ID IN NUMBER,
930 I_PAC_RATES_ID IN NUMBER,
931 I_ITEM_ID IN NUMBER,
932 I_PRI_QTY IN NUMBER,
933 I_TXN_ACTION_ID IN NUMBER,
934 I_ENTITY_ID IN NUMBER,
935 I_LINE_ID IN NUMBER,
936 I_OP_SEQ IN NUMBER,
937 I_EXP_FLAG IN NUMBER,
938 I_USER_ID IN NUMBER,
939 I_LOGIN_ID IN NUMBER,
940 I_REQ_ID IN NUMBER,
941 I_PRG_APPL_ID IN NUMBER,
942 I_PRG_ID IN NUMBER,
943 O_Err_Num OUT NOCOPY NUMBER,
944 O_Err_Code OUT NOCOPY VARCHAR2,
945 O_Err_Msg OUT NOCOPY VARCHAR2
946 )
947 return integer IS
948 l_ret_val NUMBER;
949 l_level NUMBER;
950 l_txn_cost_exist NUMBER;
951 l_cost_details NUMBER;
952 l_err_num NUMBER;
953 l_err_code VARCHAR2(240);
954 l_err_msg VARCHAR2(240);
955 l_stmt_num NUMBER;
956 process_error EXCEPTION;
957
958 BEGIN
959 -- initialize local variables
960 l_err_num := 0;
961 l_err_code := '';
962 l_err_msg := '';
963 l_ret_val := 0;
964
965
966 return l_ret_val;
967
968
969 EXCEPTION
970 when process_error then
971 o_err_num := l_err_num;
972 o_err_code := l_err_code;
973 o_err_msg := l_err_msg;
974 return l_ret_val;
975 when OTHERS then
976 rollback;
977 o_err_num := SQLCODE;
978 o_err_msg := 'CSTPFCHK.Compute_Pac_Cost_Hook ('|| to_char(l_stmt_num) || '): '
979 || substr(SQLERRM,1,200);
980 return l_ret_val;
981 END pac_wip_issue_cost_hook;
982
986 --
983
984 -- PROCEDURE
985 -- copy_prior_info_hook
987 procedure copy_prior_info_hook(
988 I_PAC_PERIOD_ID IN NUMBER,
989 I_PRIOR_PAC_PERIOD_ID IN NUMBER,
990 I_LEGAL_ENTITY IN NUMBER,
991 I_COST_TYPE_ID IN NUMBER,
992 I_COST_GROUP_ID IN NUMBER,
993 I_COST_METHOD IN NUMBER,
994 I_USER_ID IN NUMBER,
995 I_LOGIN_ID IN NUMBER,
999 O_Err_Num OUT NOCOPY NUMBER,
996 I_REQUEST_ID IN NUMBER,
997 I_PROG_APP_ID IN NUMBER,
998 I_PROG_ID IN NUMBER,
1000 O_Err_Code OUT NOCOPY VARCHAR2,
1001 O_Err_Msg OUT NOCOPY VARCHAR2
1002 ) IS
1003
1004 l_err_num NUMBER;
1005 l_err_code VARCHAR2(240);
1006 l_err_msg VARCHAR2(240);
1007 l_stmt_num NUMBER;
1008 l_count NUMBER;
1009 l_use_hook NUMBER;
1010 l_cost_layer_id NUMBER;
1011 l_quantity_layer_id NUMBER;
1012 l_cost_method_type NUMBER;
1013 l_current_start_date DATE;
1014 CURRENT_DATA_EXISTS EXCEPTION;
1015 PROCESS_ERROR EXCEPTION;
1016
1017 CURSOR prior_period_cost_cursor IS
1018 SELECT cost_layer_id
1019 FROM cst_pac_item_costs cpic
1020 WHERE cpic.pac_period_id = i_prior_pac_period_id
1021 AND cpic.cost_group_id = i_cost_group_id;
1022
1023 CURSOR prior_period_quantity_cursor (P_cost_layer_id number) IS
1024 SELECT quantity_layer_id
1025 FROM cst_pac_quantity_layers cpql
1026 WHERE cpql.cost_layer_id = P_cost_layer_id;
1027
1028 CURSOR prior_period_jobs_cursor IS
1029 SELECT distinct(wip_entity_id)
1030 FROM wip_pac_period_balances wppb
1031 WHERE wppb.pac_period_id = i_prior_pac_period_id
1032 AND wppb.cost_group_id = i_cost_group_id;
1033
1034 --l_file VARCHAR2(100);
1035
1036 BEGIN
1037 ----------------------------------------------------------------------
1038 -- Initialize Variables
1039 ----------------------------------------------------------------------
1040
1041 l_err_num := 0;
1042 l_err_code := '';
1043 l_err_msg := '';
1044
1045
1046
1047 --------------------------------------------------------------------
1048 -- Copy from previous period, if this is not the first run period --
1049 --------------------------------------------------------------------
1050 IF (i_prior_pac_period_id <> -1) THEN
1051
1052 --------------------------------------------------------
1053 -- Making sure that we have no data in current period --
1054 --------------------------------------------------------
1055 l_stmt_num := 10;
1056 l_count := 0;
1057 SELECT count(*)
1058 INTO l_count
1059 FROM cst_pac_item_costs
1060 WHERE pac_period_id = i_pac_period_id
1061 AND cost_group_id = i_cost_group_id;
1062
1063 IF (l_count <> 0) THEN
1064 raise CURRENT_DATA_EXISTS;
1065 END IF;
1066
1067 l_stmt_num := 20;
1068 l_count := 0;
1069 SELECT count(*)
1070 INTO l_count
1071 FROM wip_pac_period_balances
1072 WHERE pac_period_id = i_pac_period_id
1073 AND cost_group_id = i_cost_group_id;
1074
1075 IF (l_count <> 0) THEN
1076 raise CURRENT_DATA_EXISTS;
1077 END IF;
1078
1079
1080 --------------------------------------------------------------------------------
1081 -- Copy data from previous period to current period of the following tables : --
1082 -- 1. cst_pac_item_costs --
1083 -- 2. cst_pac_item_cost_details --
1084 -- 3. cst_pac_quantity_layers --
1085 -- New cost_layer_id and quantity_layer_id are generated for every rows --
1086 -- inserted. --
1087 --------------------------------------------------------------------------------
1088 FOR l_prior_period_cost IN prior_period_cost_cursor LOOP
1089
1090 SELECT cst_pac_item_costs_s.nextval
1091 INTO l_cost_layer_id
1092 FROM dual;
1093
1094 -------------------------------------------
1095 -- Copy prior info of CST_PAC_ITEM_COSTS --
1096 -------------------------------------------
1097 l_stmt_num := 30;
1098 INSERT INTO cst_pac_item_costs (
1099 cost_layer_id,
1100 pac_period_id,
1101 cost_group_id,
1102 inventory_item_id,
1103 total_layer_quantity,
1104 buy_quantity,
1105 make_quantity,
1106 issue_quantity,
1107 item_cost,
1108 begin_item_cost,
1109 item_buy_cost,
1110 item_make_cost,
1111 material_cost,
1112 material_overhead_cost,
1113 resource_cost,
1114 overhead_cost,
1115 outside_processing_cost,
1116 pl_material,
1117 pl_material_overhead,
1118 pl_resource,
1119 pl_outside_processing,
1120 pl_overhead,
1121 tl_material,
1122 tl_material_overhead,
1123 tl_resource,
1124 tl_outside_processing,
1125 tl_overhead,
1126 pl_item_cost,
1127 tl_item_cost,
1128 unburdened_cost,
1129 burden_cost,
1130 last_update_date,
1131 last_updated_by,
1132 creation_date,
1133 created_by,
1134 request_id,
1135 program_application_id,
1136 program_id,
1137 program_update_date,
1138 last_update_login)
1139 SELECT
1140 l_cost_layer_id,
1141 i_pac_period_id,
1142 cost_group_id,
1143 inventory_item_id,
1144 total_layer_quantity,
1145 0,
1146 0,
1147 0,
1148 item_cost,
1149 item_cost,
1150 item_buy_cost,
1151 item_make_cost,
1152 material_cost,
1153 material_overhead_cost,
1154 resource_cost,
1155 overhead_cost,
1156 outside_processing_cost,
1157 pl_material,
1158 pl_material_overhead,
1159 pl_resource,
1160 pl_outside_processing,
1161 pl_overhead,
1162 tl_material,
1163 tl_material_overhead,
1167 pl_item_cost,
1164 tl_resource,
1165 tl_outside_processing,
1166 tl_overhead,
1168 tl_item_cost,
1169 unburdened_cost,
1170 burden_cost,
1171 SYSDATE,
1172 i_user_id,
1173 SYSDATE,
1174 i_user_id,
1175 i_request_id,
1176 i_prog_app_id,
1177 i_prog_id,
1178 SYSDATE,
1179 i_login_id
1180 FROM cst_pac_item_costs cpic
1184 -- Copy prior info of CST_PAC_ITEM_COST_DETAILS --
1181 WHERE cpic.cost_layer_id = l_prior_period_cost.cost_layer_id;
1182
1183 --------------------------------------------------
1185 --------------------------------------------------
1186 l_stmt_num := 40;
1187 INSERT INTO cst_pac_item_cost_details (
1188 cost_layer_id,
1189 cost_element_id,
1190 level_type,
1191 item_cost,
1192 item_buy_cost,
1193 item_make_cost,
1194 last_update_date,
1195 last_updated_by,
1196 creation_date,
1197 created_by,
1198 request_id,
1199 program_application_id,
1200 program_id,
1201 program_update_date,
1202 last_update_login)
1203 SELECT
1204 l_cost_layer_id,
1205 cpicd.cost_element_id,
1206 cpicd.level_type,
1207 0,
1208 0,
1209 0,
1210 SYSDATE,
1211 i_user_id,
1212 SYSDATE,
1213 i_user_id,
1214 i_request_id,
1215 i_prog_app_id,
1216 i_prog_id,
1217 SYSDATE,
1218 i_login_id
1219 FROM cst_pac_item_cost_details cpicd
1220 WHERE cpicd.cost_layer_id = l_prior_period_cost.cost_layer_id;
1221
1222 FOR l_prior_period_qty IN
1223 prior_period_quantity_cursor (l_prior_period_cost.cost_layer_id) LOOP
1224
1225 SELECT cst_pac_quantity_layers_s.nextval
1226 INTO l_quantity_layer_id
1227 FROM dual;
1228
1229 ------------------------------------------------
1230 -- Copy prior info of CST_PAC_QUANTITY_LAYERS --
1231 ------------------------------------------------
1232 l_stmt_num := 50;
1233 INSERT INTO cst_pac_quantity_layers (
1234 quantity_layer_id,
1235 cost_layer_id,
1236 pac_period_id,
1237 cost_group_id,
1238 inventory_item_id,
1239 layer_quantity,
1240 last_update_date,
1241 last_updated_by,
1242 creation_date,
1243 created_by,
1244 request_id,
1245 program_application_id,
1246 program_id,
1247 program_update_date,
1248 last_update_login)
1249 SELECT
1250 l_quantity_layer_id,
1251 l_cost_layer_id,
1252 i_pac_period_id,
1253 cost_group_id,
1254 inventory_item_id,
1255 layer_quantity,
1256 SYSDATE,
1257 i_user_id,
1258 SYSDATE,
1259 i_user_id,
1260 i_request_id,
1261 i_prog_app_id,
1262 i_prog_id,
1263 SYSDATE,
1264 i_login_id
1265 FROM cst_pac_quantity_layers cpql
1266 WHERE cpql.quantity_layer_id = l_prior_period_qty.quantity_layer_id;
1267
1268 -- =============================================================================
1269 -- Bug 4028737 fix:
1270 -- Update begin_layer_quantity with cpic.total_layer_quantity of previous period
1271 -- =============================================================================
1272 l_stmt_num := 55;
1273 UPDATE cst_pac_quantity_layers
1274 SET begin_layer_quantity = (SELECT total_layer_quantity
1275 FROM cst_pac_item_costs
1276 WHERE cost_layer_id = l_prior_period_cost.cost_layer_id)
1277 WHERE quantity_layer_id = l_quantity_layer_id;
1278
1279
1280 END LOOP;
1281
1282 END LOOP;
1283
1284 ---------------------------------------------------------------------------
1285 -- Copy prior info of wip_pac_period_balances --
1286 -- Only the followings are copied : --
1287 -- 1. Discrete jobs that are opened or closed in the current period. --
1288 -- 2. Scheduled CFM that are opened or closed in the current period. --
1289 -- 3. Repetitive Schedules having at least line that are opened or --
1290 -- closed in the current period. --
1291 -- Thus jobs/schedules that are closed in the previous period will not --
1292 -- be copied to current period.
1296 INTO l_current_start_date
1293 ---------------------------------------------------------------------------
1294 l_stmt_num := 60;
1295 SELECT period_start_date
1297 FROM CST_PAC_PERIODS
1298 WHERE pac_period_id = i_pac_period_id;
1299
1300 l_stmt_num := 70;
1301 INSERT INTO wip_pac_period_balances (
1302 pac_period_id,
1303 cost_group_id,
1304 cost_type_id,
1305 organization_id,
1306 wip_entity_id,
1307 line_id,
1308 operation_seq_num,
1309 operation_completed_units,
1310 relieved_assembly_units,
1311 tl_resource_in,
1312 tl_resource_out,
1313 tl_outside_processing_in,
1314 tl_outside_processing_out,
1315 tl_overhead_in,
1316 tl_overhead_out,
1317 pl_material_in,
1318 pl_material_out,
1319 pl_resource_in,
1320 pl_resource_out,
1321 pl_overhead_in,
1322 pl_overhead_out,
1323 pl_outside_processing_in,
1324 pl_outside_processing_out,
1325 pl_material_overhead_in,
1326 pl_material_overhead_out,
1327 tl_resource_temp,
1328 tl_outside_processing_temp,
1329 tl_overhead_temp,
1330 pl_material_temp,
1331 pl_material_overhead_temp,
1332 pl_resource_temp,
1333 pl_outside_processing_temp,
1334 pl_overhead_temp,
1335 tl_resource_var,
1336 tl_outside_processing_var,
1337 tl_overhead_var,
1338 pl_material_var,
1339 pl_material_overhead_var,
1340 pl_resource_var,
1341 pl_outside_processing_var,
1342 pl_overhead_var,
1343 wip_entity_type,
1344 unrelieved_scrap_quantity,
1345 last_update_date,
1346 last_updated_by,
1347 creation_date,
1348 created_by,
1349 request_id,
1350 program_application_id,
1351 program_id,
1352 program_update_date,
1353 last_update_login )
1354 SELECT
1355 i_pac_period_id,
1356 wppb.cost_group_id,
1357 wppb.cost_type_id,
1358 wppb.organization_id,
1359 wppb.wip_entity_id,
1360 wppb.line_id,
1361 wppb.operation_seq_num,
1362 wppb.operation_completed_units,
1363 wppb.relieved_assembly_units,
1364 wppb.tl_resource_in,
1365 wppb.tl_resource_out,
1366 wppb.tl_outside_processing_in,
1367 wppb.tl_outside_processing_out,
1368 wppb.tl_overhead_in,
1369 wppb.tl_overhead_out,
1370 wppb.pl_material_in,
1371 wppb.pl_material_out,
1375 wppb.pl_overhead_out,
1372 wppb.pl_resource_in,
1373 wppb.pl_resource_out,
1374 wppb.pl_overhead_in,
1376 wppb.pl_outside_processing_in,
1377 wppb.pl_outside_processing_out,
1378 wppb.pl_material_overhead_in,
1379 wppb.pl_material_overhead_out,
1380 wppb.tl_resource_temp,
1381 wppb.tl_outside_processing_temp,
1382 wppb.tl_overhead_temp,
1383 wppb.pl_material_temp,
1384 wppb.pl_material_overhead_temp,
1385 wppb.pl_resource_temp,
1386 wppb.pl_outside_processing_temp,
1387 wppb.pl_overhead_temp,
1388 wppb.tl_resource_var,
1389 wppb.tl_outside_processing_var,
1390 wppb.tl_overhead_var,
1391 wppb.pl_material_var,
1392 wppb.pl_material_overhead_var,
1393 wppb.pl_resource_var,
1394 wppb.pl_outside_processing_var,
1395 wppb.pl_overhead_var,
1396 wppb.wip_entity_type,
1397 wppb.unrelieved_scrap_quantity,
1398 SYSDATE,
1399 i_user_id,
1400 SYSDATE,
1401 i_user_id,
1402 i_request_id,
1403 i_prog_app_id,
1404 i_prog_id,
1405 SYSDATE,
1406 i_login_id
1407 FROM
1408 wip_pac_period_balances wppb, wip_entities we
1409 WHERE
1410 wppb.pac_period_id = i_prior_pac_period_id
1411 AND wppb.cost_group_id = i_cost_group_id
1412 AND wppb.wip_entity_id = we.wip_entity_id
1413 AND (
1414 ( we.entity_type IN (1,3) AND EXISTS (
1415 SELECT 'X'
1416 FROM wip_discrete_jobs wdj
1417 WHERE
1418 wdj.wip_entity_id = wppb.wip_entity_id AND
1419 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
1420 OR (we.entity_type = 4 AND EXISTS (
1421 SELECT 'X'
1422 FROM wip_flow_schedules wfs
1423 WHERE
1424 wfs.wip_entity_id = wppb.wip_entity_id AND
1425 wfs.scheduled_flag = 1 AND
1426 wfs.status IN (1,2) AND
1427 NVL(wfs.date_closed, l_current_start_date) >= l_current_start_date))
1428 OR (we.entity_type =2 AND EXISTS (
1429 SELECT 'X'
1430 FROM wip_repetitive_schedules wrs
1431 WHERE
1435
1432 wrs.wip_entity_id = wppb.wip_entity_id AND
1433 wrs.line_id = wppb.line_id AND
1434 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
1436
1437 ---------------------------------------
1438 -- Added R12 PAC enhancement
1439 ---------------------------------------
1440 l_stmt_num := 75;
1441 INSERT INTO CST_PAC_REQ_OPER_COST_DETAILS
1442 (pac_period_id,
1443 cost_group_id,
1444 wip_entity_id,
1445 line_id,
1446 inventory_item_id,
1447 cost_element_id,
1448 operation_seq_num,
1449 applied_value,
1450 applied_quantity,
1451 relieved_value,
1452 relieved_quantity,
1453 comp_variance,
1454 temp_relieved_value,
1455 last_update_date,
1456 last_updated_by,
1457 creation_date,
1458 created_by,
1459 request_id ,
1460 program_application_id,
1461 program_id,
1462 program_update_date,
1463 last_update_login)
1464 SELECT i_pac_period_id,
1465 wprocd.cost_group_id,
1466 wprocd.wip_entity_id,
1467 wprocd.line_id,
1468 wprocd.inventory_item_id,
1469 wprocd.cost_element_id,
1470 wprocd.operation_seq_num,
1471 wprocd.applied_value,
1472 wprocd.applied_quantity,
1473 wprocd.relieved_value,
1474 wprocd.relieved_quantity,
1475 wprocd.comp_variance,
1476 0,
1477 SYSDATE,
1478 i_user_id,
1479 SYSDATE,
1480 i_user_id,
1481 i_request_id,
1482 i_prog_app_id,
1483 i_prog_id,
1484 SYSDATE,
1485 i_login_id
1486 FROM CST_PAC_REQ_OPER_COST_DETAILS wprocd,
1487 WIP_ENTITIES we
1488 WHERE wprocd.pac_period_id = i_prior_pac_period_id
1489 AND wprocd.cost_group_id = i_cost_group_id
1490 AND wprocd.wip_entity_id = we.wip_entity_id
1491 AND (
1492 ( we.entity_type IN (1,3) AND EXISTS (
1493 SELECT 'X'
1494 FROM wip_discrete_jobs wdj
1495 WHERE
1496 wdj.wip_entity_id = wprocd.wip_entity_id AND
1497 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
1498 OR (we.entity_type = 2 AND EXISTS (
1499 SELECT 'X'
1500 FROM wip_repetitive_schedules wrs
1501 WHERE
1502 wrs.wip_entity_id = wprocd.wip_entity_id AND
1503 wrs.line_id = wprocd.line_id AND
1504 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
1505
1506
1507 END IF;
1508
1509 EXCEPTION
1510 when process_error then
1511 o_err_num := l_err_num;
1512 o_err_code := l_err_code;
1513 o_err_msg := l_err_msg;
1514 when OTHERS then
1515 rollback;
1516 o_err_num := SQLCODE;
1517 o_err_msg := 'CSTPFCHK.Copy_Prior_Info_Hook ('|| to_char(l_stmt_num) || '
1518 ): '
1519 || substr(SQLERRM,1,200);
1520
1521 END copy_prior_info_hook;
1522
1523 -- ===================================================
1524 -- Periodic Cost Update invoked for Incremental LIFO
1525 -- The procedure is a copy from BOM115100 inorder to
1526 -- prevent regression. The regression is due to
1527 -- cppb insert/update introduced in R12 code
1528 -- ===================================================
1529 PROCEDURE periodic_cost_update_hook (
1530 I_PAC_PERIOD_ID IN NUMBER,
1531 I_COST_GROUP_ID IN NUMBER,
1532 I_COST_TYPE_ID IN NUMBER,
1533 I_TXN_ID IN NUMBER,
1534 I_COST_LAYER_ID IN NUMBER,
1535 I_QTY_LAYER_ID IN NUMBER,
1536 I_ITEM_ID IN NUMBER,
1537 I_USER_ID IN NUMBER,
1538 I_LOGIN_ID IN NUMBER,
1539 I_REQ_ID IN NUMBER,
1540 I_PRG_APPL_ID IN NUMBER,
1541 I_PRG_ID IN NUMBER,
1542 I_TXN_CATEGORY IN NUMBER,
1543 I_TXN_QTY IN NUMBER,
1544 O_Err_Num OUT NOCOPY NUMBER,
1545 O_Err_Code OUT NOCOPY VARCHAR2,
1546 O_Err_Msg OUT NOCOPY VARCHAR2)
1547 IS
1548 l_update_flag NUMBER;
1552
1549 l_stmt_num NUMBER;
1550 l_onhand NUMBER := 0;
1551 BEGIN
1553 /********************************************************************
1554 ** Insert into mpacd, all the elemental cost : **
1555 ** - exists in cpicd, but not exists in mptcd **
1556 ** It will use the current cost in cpicd as the new cost **
1557 ********************************************************************/
1558 l_stmt_num := 5;
1559 INSERT INTO mtl_pac_actual_cost_details (
1560 transaction_id,
1561 pac_period_id,
1562 cost_type_id,
1563 cost_group_id,
1564 cost_layer_id,
1565 cost_element_id,
1566 level_type,
1567 last_update_date,
1568 last_updated_by,
1569 creation_date,
1570 created_by,
1571 last_update_login,
1572 request_id,
1573 program_application_id,
1574 program_id,
1575 program_update_date,
1576 inventory_item_id,
1577 actual_cost,
1578 prior_cost,
1579 prior_buy_cost,
1580 prior_make_cost,
1581 new_cost,
1582 new_buy_cost,
1583 new_make_cost,
1584 variance_amount,
1585 insertion_flag,
1586 user_entered,
1587 transaction_costed_date,
1588 txn_category)
1589 SELECT
1590 i_txn_id,
1591 i_pac_period_id,
1592 i_cost_type_id,
1593 i_cost_group_id,
1594 i_cost_layer_id,
1595 cpicd.cost_element_id,
1596 cpicd.level_type,
1597 sysdate,
1598 i_user_id,
1599 sysdate,
1600 i_user_id,
1601 i_login_id,
1602 i_req_id,
1603 i_prg_appl_id,
1604 i_prg_id,
1605 sysdate,
1606 i_item_id,
1607 nvl(cpicd.item_cost,0),
1608 nvl(cpicd.item_cost,0),
1609 nvl(cpicd.item_buy_cost,0),
1610 nvl(cpicd.item_make_cost,0),
1611 nvl(cpicd.item_cost,0),
1612 nvl(cpicd.item_buy_cost,0),
1613 nvl(cpicd.item_make_cost,0),
1614 0,
1615 'Y',
1616 'N',
1617 NULL,
1618 i_txn_category
1619 FROM cst_pac_item_cost_details cpicd
1620 WHERE cpicd.cost_layer_id = i_cost_layer_id
1621 AND not exists (
1622 SELECT 'not exists in mptcd'
1623 FROM mtl_pac_txn_cost_details mptcd
1624 WHERE mptcd.transaction_id = i_txn_id
1625 AND mptcd.pac_period_id = i_pac_period_id
1626 AND mptcd.cost_group_id = i_cost_group_id
1627 AND mptcd.cost_element_id = cpicd.cost_element_id
1628 AND mptcd.level_type = cpicd.level_type);
1629
1630 /********************************************************************
1631 ** Insert into mpacd, all the elemental cost : **
1632 ** - exists in mptcd and cpicd **
1633 ** - exists in mptcd but not exists in cpicd **
1634 ** New cost will be calculated based on current cost (if exists) **
1635 ** and cost change in mptcd. **
1636 ********************************************************************/
1637 l_stmt_num := 10;
1638 INSERT INTO mtl_pac_actual_cost_details (
1639 transaction_id,
1640 pac_period_id,
1641 cost_type_id,
1642 cost_group_id,
1643 cost_layer_id,
1644 cost_element_id,
1645 level_type,
1646 last_update_date,
1647 last_updated_by,
1648 creation_date,
1649 created_by,
1650 last_update_login,
1651 request_id,
1652 program_application_id,
1653 program_id,
1654 program_update_date,
1655 inventory_item_id,
1656 actual_cost,
1657 prior_cost,
1658 prior_buy_cost,
1659 prior_make_cost,
1660 new_cost,
1661 new_buy_cost,
1662 new_make_cost,
1663 variance_amount,
1664 insertion_flag,
1665 user_entered,
1666 transaction_costed_date,
1667 txn_category,
1668 onhand_variance_amount)
1669 SELECT
1670 i_txn_id,
1671 i_pac_period_id,
1672 i_cost_type_id,
1673 i_cost_group_id,
1674 i_cost_layer_id,
1675 mptcd.cost_element_id,
1676 mptcd.level_type,
1677 sysdate,
1678 i_user_id,
1679 sysdate,
1680 i_user_id,
1681 i_login_id,
1682 i_req_id,
1683 i_prg_appl_id,
1684 i_prg_id,
1685 sysdate,
1686 mptcd.inventory_item_id,
1687 decode(mptcd.new_periodic_cost,NULL,
1688 decode(mptcd.percentage_change,NULL,
1689 /* value change formula */
1690 decode(sign(cpql.layer_quantity),1,
1691 decode(sign(i_txn_qty),1,
1692 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1693 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity +
1694 (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1695 0,
1696 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1700 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1697 (mptcd.value_change/i_txn_qty*cpql.layer_quantity))/nvl(cpql.layer_quantity,-1)),
1698 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1699 0,
1701 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1702 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1703 0,
1704 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1705 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1706 nvl(cpicd.item_cost,0)),
1707 /* percentage change formula */
1708 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
1709 /* new average cost formula */
1710 mptcd.new_periodic_cost),
1711 nvl(cpicd.item_cost,0),
1712 nvl(cpicd.item_buy_cost,0),
1713 nvl(cpicd.item_make_cost,0),
1714 decode(mptcd.new_periodic_cost,NULL,
1715 decode(mptcd.percentage_change,NULL,
1716 /* value change formula */
1717 decode(sign(cpql.layer_quantity),1,
1718 decode(sign(i_txn_qty),1,
1719 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1720 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity +
1721 (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1722 0,
1723 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1724 (mptcd.value_change/i_txn_qty*cpql.layer_quantity))/nvl(cpql.layer_quantity,-1)),
1725 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1726 0,
1727 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1728 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1729 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1730 0,
1731 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1732 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1733 nvl(cpicd.item_cost,0)),
1734 /* percentage change formula */
1735 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
1736 /* new average cost formula */
1737 mptcd.new_periodic_cost),
1738 nvl(cpicd.item_buy_cost,0),
1739 nvl(cpicd.item_make_cost,0),
1740 decode(mptcd.value_change,NULL,
1741 0,
1742 decode(sign(cpql.layer_quantity),1,
1743 decode(sign(i_txn_qty),1,
1744 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1745 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1746 (mptcd.value_change/i_txn_qty*cpql.layer_quantity) + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1747 0),
1748 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1749 mptcd.value_change + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1750 0)),
1751 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1752 mptcd.value_change + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1753 0)),
1754 mptcd.value_change)),
1755 'Y',
1756 'N',
1757 NULL,
1758 i_txn_category,
1759 decode(mptcd.value_change,NULL,
1760 0,
1761 decode(sign(i_txn_qty),1,
1762 decode(sign(cpql.layer_quantity),1,
1763 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1764 mptcd.value_change*(1-cpql.layer_quantity/i_txn_qty),
1765 0
1766 ),
1767 0
1768 ),
1769 0
1770 )
1771 )
1772 FROM mtl_pac_txn_cost_details mptcd,
1773 cst_pac_item_cost_details cpicd,
1774 cst_pac_quantity_layers cpql
1775 WHERE mptcd.transaction_id = i_txn_id
1776 AND mptcd.pac_period_id = i_pac_period_id
1777 AND mptcd.cost_group_id = i_cost_group_id
1778 AND cpql.cost_layer_id = i_cost_layer_id
1779 AND cpql.quantity_layer_id = i_qty_layer_id
1780 AND cpicd.cost_layer_id (+) = i_cost_layer_id
1781 AND cpicd.cost_element_id (+) = mptcd.cost_element_id
1782 AND cpicd.level_type (+) = mptcd.level_type;
1783
1784 l_stmt_num := 20;
1785 DELETE FROM cst_pac_item_cost_details
1786 WHERE cost_layer_id = i_cost_layer_id;
1787
1788 l_stmt_num := 30;
1789 INSERT INTO cst_pac_item_cost_details(
1790 cost_layer_id,
1791 cost_element_id,
1792 level_type,
1793 last_update_date,
1794 last_updated_by,
1795 creation_date,
1796 created_by,
1797 last_update_login,
1798 request_id,
1799 program_application_id,
1800 program_id,
1801 program_update_date,
1802 item_cost,
1803 item_buy_cost,
1804 item_make_cost)
1805 SELECT i_cost_layer_id,
1806 mpacd.cost_element_id,
1807 mpacd.level_type,
1808 sysdate,
1809 i_user_id,
1810 sysdate,
1811 i_user_id,
1812 i_login_id,
1813 i_req_id,
1814 i_prg_appl_id,
1815 i_prg_id,
1816 sysdate,
1817 mpacd.new_cost,
1818 mpacd.new_buy_cost,
1819 mpacd.new_make_cost
1820 FROM mtl_pac_actual_cost_details mpacd
1821 WHERE mpacd.transaction_id = i_txn_id
1822 AND mpacd.cost_group_id = i_cost_group_id
1823 AND mpacd.cost_layer_id = i_cost_layer_id
1824 AND mpacd.insertion_flag = 'Y';
1825
1826 /* It's flag to indicate if we need to update begin item cost with the new item cost */
1827 /* If the update type is NOT value_change, set the flag to 1 to indicate updating of begin */
1831 INTO l_update_flag
1828 /* item cost with the new cost. Otherwise set it to 0 */
1829 l_stmt_num := 40;
1830 SELECT DECODE(MAX(value_change),NULL, 1, 0)
1832 FROM mtl_pac_txn_cost_details mptcd
1833 WHERE mptcd.transaction_id = i_txn_id
1834 AND mptcd.pac_period_id = i_pac_period_id
1835 AND mptcd.cost_group_id = i_cost_group_id;
1836
1837 l_stmt_num := 50;
1838 UPDATE cst_pac_item_costs cpic
1839 SET (last_updated_by,
1840 last_update_date,
1841 last_update_login,
1842 request_id,
1843 program_application_id,
1844 program_id,
1845 program_update_date,
1846 pl_material,
1847 pl_material_overhead,
1848 pl_resource,
1849 pl_outside_processing,
1850 pl_overhead,
1851 tl_material,
1852 tl_material_overhead,
1853 tl_resource,
1854 tl_outside_processing,
1855 tl_overhead,
1856 material_cost,
1857 material_overhead_cost,
1858 resource_cost,
1859 outside_processing_cost,
1860 overhead_cost,
1861 pl_item_cost,
1862 tl_item_cost,
1863 item_cost,
1864 begin_item_cost,
1865 item_buy_cost,
1866 item_make_cost,
1867 unburdened_cost,
1868 burden_cost) =
1869 (SELECT
1870 i_user_id,
1871 sysdate,
1872 i_login_id,
1873 i_req_id,
1874 i_prg_appl_id,
1875 i_prg_id,
1876 sysdate,
1877 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
1878 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
1879 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
1880 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
1881 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
1882 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
1883 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
1884 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
1885 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
1886 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
1887 SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST,0)),
1888 SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST,0)),
1889 SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST,0)),
1890 SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST,0)),
1891 SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST,0)),
1892 SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)),
1893 SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)),
1894 SUM(ITEM_COST),
1895 DECODE(l_update_flag, 1, SUM(ITEM_COST), cpic.begin_item_cost),
1896 SUM(ITEM_BUY_COST),
1897 SUM(ITEM_MAKE_COST),
1901 WHERE v.cost_layer_id = i_cost_layer_id
1898 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,2,ITEM_COST,0),ITEM_COST)),
1899 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0))
1900 FROM CST_PAC_ITEM_COST_DETAILS v
1902 GROUP BY COST_LAYER_ID)
1903 WHERE cpic.cost_layer_id = i_cost_layer_id
1904 AND EXISTS
1905 (SELECT 'there is detail cost'
1906 FROM cst_pac_item_cost_details cpicd
1907 WHERE cpicd.cost_layer_id = i_cost_layer_id);
1908
1909 /* Fix for Bug 1970458
1910 * For a value change periodic update cost transaction,
1911 * update the primary_quantity in mmt to the layer quantity from cpql.
1912 * Prior to this, the quantity at the beginning of the period was being
1913 * used and this caused errors in the distributions.
1914 * The layer qty can be obtained from cst_pac_quantity_layers
1915 */
1916
1917 l_stmt_num := 60;
1918
1919 select nvl(layer_quantity,0)
1920 into l_onhand
1921 from cst_pac_quantity_layers
1922 where cost_group_id = i_cost_group_id and
1923 pac_period_id = i_pac_period_id and
1924 inventory_item_id = i_item_id;
1925
1926 UPDATE mtl_material_transactions mmt
1927 SET --primary_quantity = l_onhand,
1928 /* Bug 2288994. Update periodic_primary_quantity also */
1929 periodic_primary_quantity = l_onhand
1930 WHERE mmt.transaction_id = i_txn_id;
1931 fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
1932
1933
1934 EXCEPTION
1935 when OTHERS then
1936 rollback;
1937 o_err_num := SQLCODE;
1938 o_err_msg := 'CSTPPWAC.Periodic_Cost_Update (' || to_char(l_stmt_num) || '): '
1939 || substr(SQLERRM,1,200);
1940
1941 END periodic_cost_update_hook;
1942
1943
1944 END CSTPFCHK;