[Home] [Help]
PACKAGE BODY: APPS.CSTPFCHK
Source
1 PACKAGE BODY CSTPFCHK AS
2 -- $Header: CSTFCHKB.pls 120.4.12010000.3 2008/11/10 13:13:41 anjha 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,
238 i_cost_type_id,
239 i_cost_group_id,
240 i_cost_layer_id,
241 cpicd.cost_element_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
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 **
446 ** in to mtl_pac_actual_cost_details the current average cost for **
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)
596 ,mpacd.actual_cost),
597 (mpacd.prior_cost*l_cur_onhand + mpacd.actual_cost*i_txn_qty)/l_new_onhand)),
598 new_buy_cost =
599 decode(sign(l_cur_buy_qty),-1,
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),
613 decode(sign(i_make_qty), -1,
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,
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,
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)),
753 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,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)),
764 SUM(DECODE(COST_ELEMENT_ID,5,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)),
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
777 GROUP BY COST_LAYER_ID)
778 WHERE cpic.cost_layer_id = i_cost_layer_id
779 AND EXISTS
780 (SELECT 'there is detail cost'
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,
847 I_USER_ID IN NUMBER,
848 I_LOGIN_ID IN NUMBER,
849 I_REQ_ID IN NUMBER,
850 I_PRG_APPL_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;
913 o_err_msg := 'CSTPFCHK.Current_Pac_Cost_Hook ('|| to_char(l_stmt_num) || '): '
910 when OTHERS then
911 rollback;
912 o_err_num := SQLCODE;
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
983
984 -- PROCEDURE
985 -- copy_prior_info_hook
986 --
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,
996 I_REQUEST_ID IN NUMBER,
997 I_PROG_APP_ID IN NUMBER,
998 I_PROG_ID IN NUMBER,
999 O_Err_Num OUT NOCOPY 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 BIS_DEBUG_PUB.Initialize;
1047 l_file:=BIS_DEBUG_PUB.Set_debug_mode('FILE');
1048 BIS_DEBUG_PUB.Debug_on;
1049
1050 BIS_DEBUG_PUB.Add('Beginning Balance Cost Hook Reached');
1051
1052 --------------------------------------------------------------------
1053 -- Copy from previous period, if this is not the first run period --
1054 --------------------------------------------------------------------
1055 IF (i_prior_pac_period_id <> -1) THEN
1056
1057 --------------------------------------------------------
1058 -- Making sure that we have no data in current period --
1059 --------------------------------------------------------
1060 l_stmt_num := 10;
1061 l_count := 0;
1062 SELECT count(*)
1063 INTO l_count
1064 FROM cst_pac_item_costs
1065 WHERE pac_period_id = i_pac_period_id
1066 AND cost_group_id = i_cost_group_id;
1067
1071
1068 IF (l_count <> 0) THEN
1069 raise CURRENT_DATA_EXISTS;
1070 END IF;
1072 l_stmt_num := 20;
1073 l_count := 0;
1074 SELECT count(*)
1075 INTO l_count
1076 FROM wip_pac_period_balances
1077 WHERE pac_period_id = i_pac_period_id
1078 AND cost_group_id = i_cost_group_id;
1079
1080 IF (l_count <> 0) THEN
1081 raise CURRENT_DATA_EXISTS;
1082 END IF;
1083
1084
1085 --------------------------------------------------------------------------------
1086 -- Copy data from previous period to current period of the following tables : --
1087 -- 1. cst_pac_item_costs --
1088 -- 2. cst_pac_item_cost_details --
1089 -- 3. cst_pac_quantity_layers --
1090 -- New cost_layer_id and quantity_layer_id are generated for every rows --
1091 -- inserted. --
1092 --------------------------------------------------------------------------------
1093 FOR l_prior_period_cost IN prior_period_cost_cursor LOOP
1094
1095 SELECT cst_pac_item_costs_s.nextval
1096 INTO l_cost_layer_id
1097 FROM dual;
1098
1099 -------------------------------------------
1100 -- Copy prior info of CST_PAC_ITEM_COSTS --
1101 -------------------------------------------
1102 l_stmt_num := 30;
1103 INSERT INTO cst_pac_item_costs (
1104 cost_layer_id,
1105 pac_period_id,
1106 cost_group_id,
1107 inventory_item_id,
1108 total_layer_quantity,
1109 buy_quantity,
1110 make_quantity,
1111 issue_quantity,
1112 item_cost,
1113 begin_item_cost,
1114 item_buy_cost,
1115 item_make_cost,
1116 material_cost,
1117 material_overhead_cost,
1118 resource_cost,
1119 overhead_cost,
1120 outside_processing_cost,
1121 pl_material,
1122 pl_material_overhead,
1123 pl_resource,
1124 pl_outside_processing,
1125 pl_overhead,
1126 tl_material,
1127 tl_material_overhead,
1128 tl_resource,
1129 tl_outside_processing,
1130 tl_overhead,
1131 pl_item_cost,
1132 tl_item_cost,
1133 unburdened_cost,
1134 burden_cost,
1135 last_update_date,
1136 last_updated_by,
1137 creation_date,
1138 created_by,
1139 request_id,
1140 program_application_id,
1141 program_id,
1142 program_update_date,
1143 last_update_login)
1144 SELECT
1145 l_cost_layer_id,
1146 i_pac_period_id,
1147 cost_group_id,
1148 inventory_item_id,
1149 total_layer_quantity,
1150 0,
1151 0,
1152 0,
1153 item_cost,
1154 item_cost,
1155 item_buy_cost,
1156 item_make_cost,
1157 material_cost,
1158 material_overhead_cost,
1159 resource_cost,
1160 overhead_cost,
1161 outside_processing_cost,
1162 pl_material,
1163 pl_material_overhead,
1164 pl_resource,
1165 pl_outside_processing,
1166 pl_overhead,
1167 tl_material,
1168 tl_material_overhead,
1169 tl_resource,
1170 tl_outside_processing,
1171 tl_overhead,
1172 pl_item_cost,
1173 tl_item_cost,
1174 unburdened_cost,
1175 burden_cost,
1176 SYSDATE,
1177 i_user_id,
1178 SYSDATE,
1179 i_user_id,
1180 i_request_id,
1181 i_prog_app_id,
1182 i_prog_id,
1183 SYSDATE,
1184 i_login_id
1185 FROM cst_pac_item_costs cpic
1186 WHERE cpic.cost_layer_id = l_prior_period_cost.cost_layer_id;
1187
1188 --------------------------------------------------
1189 -- Copy prior info of CST_PAC_ITEM_COST_DETAILS --
1190 --------------------------------------------------
1191 l_stmt_num := 40;
1192 INSERT INTO cst_pac_item_cost_details (
1193 cost_layer_id,
1194 cost_element_id,
1195 level_type,
1196 item_cost,
1197 item_buy_cost,
1198 item_make_cost,
1199 last_update_date,
1200 last_updated_by,
1201 creation_date,
1202 created_by,
1203 request_id,
1204 program_application_id,
1205 program_id,
1206 program_update_date,
1207 last_update_login)
1208 SELECT
1209 l_cost_layer_id,
1210 cpicd.cost_element_id,
1211 cpicd.level_type,
1212 0,
1213 0,
1214 0,
1215 SYSDATE,
1216 i_user_id,
1217 SYSDATE,
1218 i_user_id,
1219 i_request_id,
1220 i_prog_app_id,
1221 i_prog_id,
1222 SYSDATE,
1223 i_login_id
1224 FROM cst_pac_item_cost_details cpicd
1225 WHERE cpicd.cost_layer_id = l_prior_period_cost.cost_layer_id;
1226
1227 FOR l_prior_period_qty IN
1228 prior_period_quantity_cursor (l_prior_period_cost.cost_layer_id) LOOP
1232 FROM dual;
1229
1230 SELECT cst_pac_quantity_layers_s.nextval
1231 INTO l_quantity_layer_id
1233
1234 ------------------------------------------------
1235 -- Copy prior info of CST_PAC_QUANTITY_LAYERS --
1236 ------------------------------------------------
1237 l_stmt_num := 50;
1238 INSERT INTO cst_pac_quantity_layers (
1239 quantity_layer_id,
1240 cost_layer_id,
1241 pac_period_id,
1242 cost_group_id,
1243 inventory_item_id,
1244 layer_quantity,
1245 last_update_date,
1246 last_updated_by,
1247 creation_date,
1248 created_by,
1249 request_id,
1250 program_application_id,
1251 program_id,
1252 program_update_date,
1253 last_update_login)
1254 SELECT
1255 l_quantity_layer_id,
1256 l_cost_layer_id,
1257 i_pac_period_id,
1258 cost_group_id,
1259 inventory_item_id,
1260 layer_quantity,
1261 SYSDATE,
1262 i_user_id,
1263 SYSDATE,
1264 i_user_id,
1265 i_request_id,
1266 i_prog_app_id,
1267 i_prog_id,
1268 SYSDATE,
1269 i_login_id
1270 FROM cst_pac_quantity_layers cpql
1271 WHERE cpql.quantity_layer_id = l_prior_period_qty.quantity_layer_id;
1272
1273 -- =============================================================================
1274 -- Bug 4028737 fix:
1275 -- Update begin_layer_quantity with cpic.total_layer_quantity of previous period
1276 -- =============================================================================
1277 l_stmt_num := 55;
1278 UPDATE cst_pac_quantity_layers
1279 SET begin_layer_quantity = (SELECT total_layer_quantity
1280 FROM cst_pac_item_costs
1281 WHERE cost_layer_id = l_prior_period_cost.cost_layer_id)
1282 WHERE quantity_layer_id = l_quantity_layer_id;
1283
1284
1285 END LOOP;
1286
1287 END LOOP;
1288
1289 ---------------------------------------------------------------------------
1290 -- Copy prior info of wip_pac_period_balances --
1291 -- Only the followings are copied : --
1292 -- 1. Discrete jobs that are opened or closed in the current period. --
1293 -- 2. Scheduled CFM that are opened or closed in the current period. --
1294 -- 3. Repetitive Schedules having at least line that are opened or --
1295 -- closed in the current period. --
1296 -- Thus jobs/schedules that are closed in the previous period will not --
1297 -- be copied to current period.
1298 ---------------------------------------------------------------------------
1299 l_stmt_num := 60;
1300 SELECT period_start_date
1301 INTO l_current_start_date
1302 FROM CST_PAC_PERIODS
1303 WHERE pac_period_id = i_pac_period_id;
1304
1305 l_stmt_num := 70;
1306 INSERT INTO wip_pac_period_balances (
1307 pac_period_id,
1308 cost_group_id,
1309 cost_type_id,
1310 organization_id,
1311 wip_entity_id,
1312 line_id,
1313 operation_seq_num,
1314 operation_completed_units,
1315 relieved_assembly_units,
1316 tl_resource_in,
1317 tl_resource_out,
1318 tl_outside_processing_in,
1319 tl_outside_processing_out,
1320 tl_overhead_in,
1321 tl_overhead_out,
1322 pl_material_in,
1323 pl_material_out,
1324 pl_resource_in,
1325 pl_resource_out,
1326 pl_overhead_in,
1327 pl_overhead_out,
1328 pl_outside_processing_in,
1329 pl_outside_processing_out,
1330 pl_material_overhead_in,
1331 pl_material_overhead_out,
1332 tl_resource_temp,
1333 tl_outside_processing_temp,
1334 tl_overhead_temp,
1335 pl_material_temp,
1336 pl_material_overhead_temp,
1337 pl_resource_temp,
1338 pl_outside_processing_temp,
1339 pl_overhead_temp,
1340 tl_resource_var,
1341 tl_outside_processing_var,
1342 tl_overhead_var,
1343 pl_material_var,
1344 pl_material_overhead_var,
1345 pl_resource_var,
1346 pl_outside_processing_var,
1347 pl_overhead_var,
1348 wip_entity_type,
1349 unrelieved_scrap_quantity,
1350 last_update_date,
1351 last_updated_by,
1352 creation_date,
1353 created_by,
1354 request_id,
1355 program_application_id,
1356 program_id,
1357 program_update_date,
1358 last_update_login )
1359 SELECT
1360 i_pac_period_id,
1361 wppb.cost_group_id,
1362 wppb.cost_type_id,
1363 wppb.organization_id,
1364 wppb.wip_entity_id,
1365 wppb.line_id,
1366 wppb.operation_seq_num,
1367 wppb.operation_completed_units,
1368 wppb.relieved_assembly_units,
1369 wppb.tl_resource_in,
1370 wppb.tl_resource_out,
1371 wppb.tl_outside_processing_in,
1372 wppb.tl_outside_processing_out,
1373 wppb.tl_overhead_in,
1377 wppb.pl_resource_in,
1374 wppb.tl_overhead_out,
1375 wppb.pl_material_in,
1376 wppb.pl_material_out,
1378 wppb.pl_resource_out,
1379 wppb.pl_overhead_in,
1380 wppb.pl_overhead_out,
1381 wppb.pl_outside_processing_in,
1382 wppb.pl_outside_processing_out,
1383 wppb.pl_material_overhead_in,
1384 wppb.pl_material_overhead_out,
1385 wppb.tl_resource_temp,
1386 wppb.tl_outside_processing_temp,
1387 wppb.tl_overhead_temp,
1388 wppb.pl_material_temp,
1389 wppb.pl_material_overhead_temp,
1390 wppb.pl_resource_temp,
1391 wppb.pl_outside_processing_temp,
1392 wppb.pl_overhead_temp,
1393 wppb.tl_resource_var,
1394 wppb.tl_outside_processing_var,
1395 wppb.tl_overhead_var,
1396 wppb.pl_material_var,
1397 wppb.pl_material_overhead_var,
1398 wppb.pl_resource_var,
1399 wppb.pl_outside_processing_var,
1400 wppb.pl_overhead_var,
1401 wppb.wip_entity_type,
1402 wppb.unrelieved_scrap_quantity,
1403 SYSDATE,
1404 i_user_id,
1405 SYSDATE,
1406 i_user_id,
1407 i_request_id,
1408 i_prog_app_id,
1409 i_prog_id,
1410 SYSDATE,
1411 i_login_id
1412 FROM
1413 wip_pac_period_balances wppb, wip_entities we
1414 WHERE
1415 wppb.pac_period_id = i_prior_pac_period_id
1416 AND wppb.cost_group_id = i_cost_group_id
1417 AND wppb.wip_entity_id = we.wip_entity_id
1418 AND (
1419 ( we.entity_type IN (1,3) AND EXISTS (
1420 SELECT 'X'
1421 FROM wip_discrete_jobs wdj
1422 WHERE
1423 wdj.wip_entity_id = wppb.wip_entity_id AND
1424 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
1425 OR (we.entity_type = 4 AND EXISTS (
1426 SELECT 'X'
1427 FROM wip_flow_schedules wfs
1428 WHERE
1429 wfs.wip_entity_id = wppb.wip_entity_id AND
1430 wfs.scheduled_flag = 1 AND
1431 wfs.status IN (1,2) AND
1432 NVL(wfs.date_closed, l_current_start_date) >= l_current_start_date))
1433 OR (we.entity_type =2 AND EXISTS (
1434 SELECT 'X'
1435 FROM wip_repetitive_schedules wrs
1436 WHERE
1437 wrs.wip_entity_id = wppb.wip_entity_id AND
1438 wrs.line_id = wppb.line_id AND
1439 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
1440
1441
1442 ---------------------------------------
1443 -- Added R12 PAC enhancement
1444 ---------------------------------------
1445 l_stmt_num := 75;
1446 INSERT INTO CST_PAC_REQ_OPER_COST_DETAILS
1447 (pac_period_id,
1448 cost_group_id,
1449 wip_entity_id,
1450 line_id,
1451 inventory_item_id,
1452 cost_element_id,
1453 operation_seq_num,
1454 applied_value,
1455 applied_quantity,
1456 relieved_value,
1457 relieved_quantity,
1458 comp_variance,
1459 temp_relieved_value,
1460 last_update_date,
1461 last_updated_by,
1462 creation_date,
1463 created_by,
1464 request_id ,
1465 program_application_id,
1466 program_id,
1467 program_update_date,
1468 last_update_login)
1469 SELECT i_pac_period_id,
1470 wprocd.cost_group_id,
1471 wprocd.wip_entity_id,
1472 wprocd.line_id,
1473 wprocd.inventory_item_id,
1474 wprocd.cost_element_id,
1475 wprocd.operation_seq_num,
1476 wprocd.applied_value,
1477 wprocd.applied_quantity,
1478 wprocd.relieved_value,
1479 wprocd.relieved_quantity,
1480 wprocd.comp_variance,
1481 0,
1482 SYSDATE,
1483 i_user_id,
1484 SYSDATE,
1485 i_user_id,
1486 i_request_id,
1487 i_prog_app_id,
1488 i_prog_id,
1489 SYSDATE,
1490 i_login_id
1491 FROM CST_PAC_REQ_OPER_COST_DETAILS wprocd,
1492 WIP_ENTITIES we
1493 WHERE wprocd.pac_period_id = i_prior_pac_period_id
1494 AND wprocd.cost_group_id = i_cost_group_id
1495 AND wprocd.wip_entity_id = we.wip_entity_id
1496 AND (
1497 ( we.entity_type IN (1,3) AND EXISTS (
1498 SELECT 'X'
1499 FROM wip_discrete_jobs wdj
1500 WHERE
1501 wdj.wip_entity_id = wprocd.wip_entity_id AND
1502 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
1503 OR (we.entity_type = 2 AND EXISTS (
1504 SELECT 'X'
1505 FROM wip_repetitive_schedules wrs
1506 WHERE
1507 wrs.wip_entity_id = wprocd.wip_entity_id AND
1508 wrs.line_id = wprocd.line_id AND
1509 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
1510
1511
1512 END IF;
1513
1514 EXCEPTION
1515 when process_error then
1516 o_err_num := l_err_num;
1520 rollback;
1517 o_err_code := l_err_code;
1518 o_err_msg := l_err_msg;
1519 when OTHERS then
1521 o_err_num := SQLCODE;
1522 o_err_msg := 'CSTPFCHK.Copy_Prior_Info_Hook ('|| to_char(l_stmt_num) || '
1523 ): '
1524 || substr(SQLERRM,1,200);
1525
1526 END copy_prior_info_hook;
1527
1528 -- ===================================================
1529 -- Periodic Cost Update invoked for Incremental LIFO
1530 -- The procedure is a copy from BOM115100 inorder to
1531 -- prevent regression. The regression is due to
1532 -- cppb insert/update introduced in R12 code
1533 -- ===================================================
1534 PROCEDURE periodic_cost_update_hook (
1535 I_PAC_PERIOD_ID IN NUMBER,
1536 I_COST_GROUP_ID IN NUMBER,
1537 I_COST_TYPE_ID IN NUMBER,
1538 I_TXN_ID IN NUMBER,
1539 I_COST_LAYER_ID IN NUMBER,
1540 I_QTY_LAYER_ID IN NUMBER,
1541 I_ITEM_ID IN NUMBER,
1542 I_USER_ID IN NUMBER,
1543 I_LOGIN_ID IN NUMBER,
1544 I_REQ_ID IN NUMBER,
1545 I_PRG_APPL_ID IN NUMBER,
1546 I_PRG_ID IN NUMBER,
1547 I_TXN_CATEGORY IN NUMBER,
1548 I_TXN_QTY IN NUMBER,
1549 O_Err_Num OUT NOCOPY NUMBER,
1550 O_Err_Code OUT NOCOPY VARCHAR2,
1551 O_Err_Msg OUT NOCOPY VARCHAR2)
1552 IS
1553 l_update_flag NUMBER;
1554 l_stmt_num NUMBER;
1555 l_onhand NUMBER := 0;
1556 BEGIN
1557
1558 /********************************************************************
1559 ** Insert into mpacd, all the elemental cost : **
1560 ** - exists in cpicd, but not exists in mptcd **
1561 ** It will use the current cost in cpicd as the new cost **
1562 ********************************************************************/
1563 l_stmt_num := 5;
1564 INSERT INTO mtl_pac_actual_cost_details (
1565 transaction_id,
1566 pac_period_id,
1567 cost_type_id,
1568 cost_group_id,
1569 cost_layer_id,
1570 cost_element_id,
1571 level_type,
1572 last_update_date,
1573 last_updated_by,
1574 creation_date,
1575 created_by,
1576 last_update_login,
1577 request_id,
1578 program_application_id,
1579 program_id,
1580 program_update_date,
1581 inventory_item_id,
1582 actual_cost,
1583 prior_cost,
1584 prior_buy_cost,
1585 prior_make_cost,
1586 new_cost,
1587 new_buy_cost,
1588 new_make_cost,
1589 variance_amount,
1590 insertion_flag,
1591 user_entered,
1592 transaction_costed_date,
1593 txn_category)
1594 SELECT
1595 i_txn_id,
1596 i_pac_period_id,
1597 i_cost_type_id,
1598 i_cost_group_id,
1599 i_cost_layer_id,
1600 cpicd.cost_element_id,
1601 cpicd.level_type,
1602 sysdate,
1603 i_user_id,
1604 sysdate,
1605 i_user_id,
1606 i_login_id,
1607 i_req_id,
1608 i_prg_appl_id,
1609 i_prg_id,
1610 sysdate,
1611 i_item_id,
1612 nvl(cpicd.item_cost,0),
1613 nvl(cpicd.item_cost,0),
1614 nvl(cpicd.item_buy_cost,0),
1615 nvl(cpicd.item_make_cost,0),
1616 nvl(cpicd.item_cost,0),
1617 nvl(cpicd.item_buy_cost,0),
1618 nvl(cpicd.item_make_cost,0),
1619 0,
1620 'Y',
1621 'N',
1622 NULL,
1623 i_txn_category
1624 FROM cst_pac_item_cost_details cpicd
1625 WHERE cpicd.cost_layer_id = i_cost_layer_id
1626 AND not exists (
1627 SELECT 'not exists in mptcd'
1628 FROM mtl_pac_txn_cost_details mptcd
1629 WHERE mptcd.transaction_id = i_txn_id
1630 AND mptcd.pac_period_id = i_pac_period_id
1631 AND mptcd.cost_group_id = i_cost_group_id
1632 AND mptcd.cost_element_id = cpicd.cost_element_id
1633 AND mptcd.level_type = cpicd.level_type);
1634
1635 /********************************************************************
1636 ** Insert into mpacd, all the elemental cost : **
1637 ** - exists in mptcd and cpicd **
1638 ** - exists in mptcd but not exists in cpicd **
1639 ** New cost will be calculated based on current cost (if exists) **
1640 ** and cost change in mptcd. **
1641 ********************************************************************/
1642 l_stmt_num := 10;
1643 INSERT INTO mtl_pac_actual_cost_details (
1644 transaction_id,
1645 pac_period_id,
1646 cost_type_id,
1647 cost_group_id,
1648 cost_layer_id,
1649 cost_element_id,
1650 level_type,
1651 last_update_date,
1652 last_updated_by,
1653 creation_date,
1654 created_by,
1655 last_update_login,
1656 request_id,
1657 program_application_id,
1658 program_id,
1659 program_update_date,
1663 prior_buy_cost,
1660 inventory_item_id,
1661 actual_cost,
1662 prior_cost,
1664 prior_make_cost,
1665 new_cost,
1666 new_buy_cost,
1667 new_make_cost,
1668 variance_amount,
1669 insertion_flag,
1670 user_entered,
1671 transaction_costed_date,
1672 txn_category,
1673 onhand_variance_amount)
1674 SELECT
1675 i_txn_id,
1676 i_pac_period_id,
1677 i_cost_type_id,
1678 i_cost_group_id,
1679 i_cost_layer_id,
1680 mptcd.cost_element_id,
1681 mptcd.level_type,
1682 sysdate,
1683 i_user_id,
1684 sysdate,
1685 i_user_id,
1686 i_login_id,
1687 i_req_id,
1688 i_prg_appl_id,
1689 i_prg_id,
1690 sysdate,
1691 mptcd.inventory_item_id,
1692 decode(mptcd.new_periodic_cost,NULL,
1693 decode(mptcd.percentage_change,NULL,
1694 /* value change formula */
1695 decode(sign(cpql.layer_quantity),1,
1696 decode(sign(i_txn_qty),1,
1697 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1698 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity +
1699 (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1700 0,
1701 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1702 (mptcd.value_change/i_txn_qty*cpql.layer_quantity))/nvl(cpql.layer_quantity,-1)),
1703 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1704 0,
1705 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1706 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1707 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1708 0,
1709 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1710 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1711 nvl(cpicd.item_cost,0)),
1712 /* percentage change formula */
1713 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
1714 /* new average cost formula */
1715 mptcd.new_periodic_cost),
1716 nvl(cpicd.item_cost,0),
1717 nvl(cpicd.item_buy_cost,0),
1718 nvl(cpicd.item_make_cost,0),
1719 decode(mptcd.new_periodic_cost,NULL,
1720 decode(mptcd.percentage_change,NULL,
1721 /* value change formula */
1722 decode(sign(cpql.layer_quantity),1,
1723 decode(sign(i_txn_qty),1,
1724 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1725 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity +
1726 (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1727 0,
1728 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1729 (mptcd.value_change/i_txn_qty*cpql.layer_quantity))/nvl(cpql.layer_quantity,-1)),
1730 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1731 0,
1732 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1733 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1734 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1735 0,
1736 (nvl(cpicd.item_cost,0)*nvl(cpql.layer_quantity,0) +
1737 mptcd.value_change)/nvl(cpql.layer_quantity,-1))),
1738 nvl(cpicd.item_cost,0)),
1739 /* percentage change formula */
1740 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
1741 /* new average cost formula */
1742 mptcd.new_periodic_cost),
1743 nvl(cpicd.item_buy_cost,0),
1744 nvl(cpicd.item_make_cost,0),
1745 decode(mptcd.value_change,NULL,
1746 0,
1747 decode(sign(cpql.layer_quantity),1,
1748 decode(sign(i_txn_qty),1,
1749 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1750 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + (mptcd.value_change/i_txn_qty*cpql.layer_quantity)),-1,
1751 (mptcd.value_change/i_txn_qty*cpql.layer_quantity) + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1752 0),
1753 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1754 mptcd.value_change + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1755 0)),
1756 decode(sign(nvl(cpicd.item_cost,0) * cpql.layer_quantity + mptcd.value_change),-1,
1757 mptcd.value_change + nvl(cpicd.item_cost,0) * cpql.layer_quantity,
1758 0)),
1759 mptcd.value_change)),
1760 'Y',
1761 'N',
1762 NULL,
1763 i_txn_category,
1764 decode(mptcd.value_change,NULL,
1765 0,
1766 decode(sign(i_txn_qty),1,
1767 decode(sign(cpql.layer_quantity),1,
1768 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
1769 mptcd.value_change*(1-cpql.layer_quantity/i_txn_qty),
1770 0
1771 ),
1772 0
1773 ),
1774 0
1775 )
1776 )
1777 FROM mtl_pac_txn_cost_details mptcd,
1778 cst_pac_item_cost_details cpicd,
1782 AND mptcd.cost_group_id = i_cost_group_id
1779 cst_pac_quantity_layers cpql
1780 WHERE mptcd.transaction_id = i_txn_id
1781 AND mptcd.pac_period_id = i_pac_period_id
1783 AND cpql.cost_layer_id = i_cost_layer_id
1784 AND cpql.quantity_layer_id = i_qty_layer_id
1785 AND cpicd.cost_layer_id (+) = i_cost_layer_id
1786 AND cpicd.cost_element_id (+) = mptcd.cost_element_id
1787 AND cpicd.level_type (+) = mptcd.level_type;
1788
1789 l_stmt_num := 20;
1790 DELETE FROM cst_pac_item_cost_details
1791 WHERE cost_layer_id = i_cost_layer_id;
1792
1793 l_stmt_num := 30;
1794 INSERT INTO cst_pac_item_cost_details(
1795 cost_layer_id,
1796 cost_element_id,
1797 level_type,
1798 last_update_date,
1799 last_updated_by,
1800 creation_date,
1801 created_by,
1802 last_update_login,
1803 request_id,
1804 program_application_id,
1805 program_id,
1806 program_update_date,
1807 item_cost,
1808 item_buy_cost,
1809 item_make_cost)
1810 SELECT i_cost_layer_id,
1811 mpacd.cost_element_id,
1812 mpacd.level_type,
1813 sysdate,
1814 i_user_id,
1815 sysdate,
1816 i_user_id,
1817 i_login_id,
1818 i_req_id,
1819 i_prg_appl_id,
1820 i_prg_id,
1821 sysdate,
1822 mpacd.new_cost,
1823 mpacd.new_buy_cost,
1824 mpacd.new_make_cost
1825 FROM mtl_pac_actual_cost_details mpacd
1826 WHERE mpacd.transaction_id = i_txn_id
1827 AND mpacd.cost_group_id = i_cost_group_id
1828 AND mpacd.cost_layer_id = i_cost_layer_id
1829 AND mpacd.insertion_flag = 'Y';
1830
1831 /* It's flag to indicate if we need to update begin item cost with the new item cost */
1832 /* If the update type is NOT value_change, set the flag to 1 to indicate updating of begin */
1833 /* item cost with the new cost. Otherwise set it to 0 */
1834 l_stmt_num := 40;
1835 SELECT DECODE(MAX(value_change),NULL, 1, 0)
1836 INTO l_update_flag
1837 FROM mtl_pac_txn_cost_details mptcd
1838 WHERE mptcd.transaction_id = i_txn_id
1839 AND mptcd.pac_period_id = i_pac_period_id
1840 AND mptcd.cost_group_id = i_cost_group_id;
1841
1842 l_stmt_num := 50;
1843 UPDATE cst_pac_item_costs cpic
1844 SET (last_updated_by,
1845 last_update_date,
1846 last_update_login,
1847 request_id,
1848 program_application_id,
1849 program_id,
1850 program_update_date,
1851 pl_material,
1852 pl_material_overhead,
1853 pl_resource,
1854 pl_outside_processing,
1855 pl_overhead,
1856 tl_material,
1857 tl_material_overhead,
1858 tl_resource,
1859 tl_outside_processing,
1860 tl_overhead,
1861 material_cost,
1862 material_overhead_cost,
1863 resource_cost,
1864 outside_processing_cost,
1865 overhead_cost,
1866 pl_item_cost,
1867 tl_item_cost,
1868 item_cost,
1869 begin_item_cost,
1870 item_buy_cost,
1871 item_make_cost,
1872 unburdened_cost,
1873 burden_cost) =
1874 (SELECT
1875 i_user_id,
1876 sysdate,
1877 i_login_id,
1878 i_req_id,
1879 i_prg_appl_id,
1880 i_prg_id,
1881 sysdate,
1882 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
1883 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
1884 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
1885 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
1886 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
1887 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
1888 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
1889 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
1890 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
1891 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
1892 SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST,0)),
1893 SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST,0)),
1894 SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST,0)),
1895 SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST,0)),
1896 SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST,0)),
1897 SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)),
1898 SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)),
1899 SUM(ITEM_COST),
1900 DECODE(l_update_flag, 1, SUM(ITEM_COST), cpic.begin_item_cost),
1901 SUM(ITEM_BUY_COST),
1902 SUM(ITEM_MAKE_COST),
1903 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,2,ITEM_COST,0),ITEM_COST)),
1904 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0))
1905 FROM CST_PAC_ITEM_COST_DETAILS v
1906 WHERE v.cost_layer_id = i_cost_layer_id
1907 GROUP BY COST_LAYER_ID)
1908 WHERE cpic.cost_layer_id = i_cost_layer_id
1909 AND EXISTS
1910 (SELECT 'there is detail cost'
1911 FROM cst_pac_item_cost_details cpicd
1912 WHERE cpicd.cost_layer_id = i_cost_layer_id);
1913
1914 /* Fix for Bug 1970458
1915 * For a value change periodic update cost transaction,
1916 * update the primary_quantity in mmt to the layer quantity from cpql.
1917 * Prior to this, the quantity at the beginning of the period was being
1918 * used and this caused errors in the distributions.
1919 * The layer qty can be obtained from cst_pac_quantity_layers
1920 */
1921
1922 l_stmt_num := 60;
1923 if (l_update_flag = 0) then -- If value change is not null
1924 select nvl(layer_quantity,0)
1925 into l_onhand
1926 from cst_pac_quantity_layers
1927 where cost_group_id = i_cost_group_id and
1928 pac_period_id = i_pac_period_id and
1929 inventory_item_id = i_item_id;
1930
1931 UPDATE mtl_material_transactions mmt
1932 SET --primary_quantity = l_onhand,
1933 /* Bug 2288994. Update periodic_primary_quantity also */
1934 periodic_primary_quantity = l_onhand
1935 WHERE mmt.value_change IS NOT NULL
1936 AND mmt.transaction_id = i_txn_id;
1937 fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
1938 end if;
1939
1940 EXCEPTION
1941 when OTHERS then
1942 rollback;
1943 o_err_num := SQLCODE;
1944 o_err_msg := 'CSTPPWAC.Periodic_Cost_Update (' || to_char(l_stmt_num) || '): '
1945 || substr(SQLERRM,1,200);
1946
1947 END periodic_cost_update_hook;
1948
1949
1950 END CSTPFCHK;