[Home] [Help]
PACKAGE BODY: APPS.CST_MGD_LIFO_COST_PROCESSOR
Source
1 PACKAGE BODY CST_MGD_LIFO_COST_PROCESSOR AS
2 --$Header: CSTGLCPB.pls 120.2 2006/05/26 08:00:52 vmutyala noship $
3 --/*==========================================================================+
4 --| Copyright (c) 2001 Oracle Corporation Belmont, California, USA |
5 --| All rights reserved. |
6 --+===========================================================================+
7 --| |
8 --| File Name : CSTGLCPB.pls |
9 --| Description : Incremental Lifo Cost Processor |
10 --| |
11 --| Revision |
12 --| 1/29/99 DHerring Created |
13 --| 2/1/99 DHerring Compiled |
14 --| 2/3/99 DHerring Tested |
15 --| 2/5/99 DHerring Re-Formatted to meet MGD standards |
16 --| 2/23/99 DHerring further modification to meet standards |
17 --| and modification to lifo calculation |
18 --| 3/4/99 DHerring incorporated feedback from review |
19 --| 3/5/99 DHerring added correct exception handling |
20 --| 6/16/99 DHerring added extra logic to support change in |
21 --| requirement for market value |
22 --| 7/09/99 DHerring Extra procedures added to use temp table |
23 --| and simplify incremental lifo report |
24 --| 1/29/01 AFerrara Added procedure get_pac_id |
25 --| Added procedure check_quantity |
26 --| 04/13/2001 Vjavli Created procedure lifo_purge for the |
27 --| purge functionality |
28 --| 04/13/2001 Vjavli Created procedure selective_purge as part |
29 --| of purge functionality. This procedure will be |
30 --| invoked by lifo_purge |
31 --| 04/16/2001 Vjavli Created log and log initialize procedures |
32 --| 04/23/2001 vjavli updated with commit size logic |
33 --| 04/26/2001 vjavli removed commit size logic as per the meeting |
34 --| 05/09/2001 vjavli modified master org cursor in the lifo purge |
35 --| 05/16/2001 vjavli selective purge modified to purge the records |
36 --| upto entered period |
37 --| 07/25/2001 vjavli recalculate total quantity and delta for the open|
38 --| period layer in the populate_layers procedure |
39 --| This is to fix the bug# 1785079 |
40 --| 08/10/2001 vjavli Recalculate only for the open period |
41 --| fix to bug#1929915 |
42 --| 08/20/2001 vjavli to fix first_period issue for the begin qty <=0 |
43 --| Selective_LIFO_Purge modified |
44 --| 11/19/2002 tsimmond UTF8 :l_master_org_name changed to VARCHAR2(240) |
45 --| 12/04/2002 fdubois adding NOCOPY for OUT parameters |
46 --| 04/06/2005 vjavli XLE Uptake:pop_detail_data INSERT stmts modified |
47 --| 04/06/2005 vjavli XLE Uptake:pop_summary_data INSERT stmts modified|
48 --| 01/08/2006 vjavli FP:11i8-12.0 Bug 4028737 fix: Base bug 3775498 |
49 --| find_first_period : begin_layer_quantity <= 0 is |
50 --| compared to identify the first period |
51 --| 05/24/2006 vmutyala Replaced ORG_ORGANIZATION_DEFINITIONS to avoid |
52 --| performance issue and added the join condition on|
53 --| cost_layer_id between pic and pql in all insert |
54 --| into cstgilev_temp statements. Bug 5239725 |
55 --+==========================================================================*/
56
57 --=================
58 -- TYPES
59 --=================
60
61 TYPE period_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
62
63 --=================
64 -- CONSTANTS
65 --=================
66
67 G_CST_MGD_LIFO_COST_PROCESSOR VARCHAR2(30) := 'CST_MGD_LIFO_COST_PROCESSOR';
68
69 --==================
70 -- GLOBAL VARIABLES
71 --==================
72
73 g_period_tab period_tbl_type;
74 g_current_period_index BINARY_INTEGER := 0;
75 g_old_cost_group_id NUMBER := 0;
76 g_empty_period_tab period_tbl_type;
77
78 --====================
79 -- Debug log variables
80 --====================
81
82 g_log_level NUMBER := NULL; -- 0 for manual test
83 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF, SQL, SRS
84
85 --=========================================================================
86 -- PROCEDURE : find_first_period PRIVATE
87 -- PARAMETERS : p_pac_period_id period id
88 -- : p_item_id inventory item id
89 -- : p_cost_group_id cost group id
90 -- : p_cost_type_id cost type id
91 -- COMMENT : Find the first period to calculate incremental lifo
92 -- from. This is either the first period recorded or
93 -- the period with a preceeding total quantity of 0.
94 -- The procedure loops back through previous periods
95 -- until it find the first period to calculate from
96 -- and assigns this period id to a global variable.
97 -- PRE-COND : The procedure is fed the correct period id and item id.
98 -- The period ids can be sorted in chonological order.
99 -- The total quantity layer column can be accessed.
100 --=========================================================================
101 PROCEDURE find_first_period
102 ( p_pac_period_id IN NUMBER
103 , p_item_id IN NUMBER
104 , p_cost_group_id IN NUMBER
105 , p_cost_type_id IN NUMBER
106 )
107 IS
108
109 --================
110 -- CURSORS
111 --================
112
113 CURSOR period_cur IS
114 SELECT
115 cst_pac_periods.pac_period_id
116 FROM
117 cst_pac_periods,
118 cst_pac_item_costs
119 WHERE cst_pac_periods.pac_period_id = cst_pac_item_costs.pac_period_id
120 AND cst_pac_item_costs.inventory_item_id = p_item_id
121 AND cst_pac_item_costs.cost_group_id = p_cost_group_id
122 AND cst_pac_periods.cost_type_id = p_cost_type_id
123 ORDER BY cst_pac_periods.period_year, cst_pac_periods.period_num;
124
125
126
127 --=================
128 -- LOCAL VARIABLES
129 --=================
130
131 l_begin_quantity NUMBER;
132 l_market_value NUMBER;
133 l_current_period NUMBER;
134
135 BEGIN
136
137 -- Initialize Local Variables
138
139 l_begin_quantity := 0;
140
141
142 -- clear the pl/sql table before use
143 g_period_tab := g_empty_period_tab;
144
145 -- open cursor
146
147 IF NOT period_cur%ISOPEN
148 THEN
149 OPEN period_cur;
150 END IF;
151
152 FETCH period_cur INTO g_period_tab(g_period_tab.COUNT+1);
153
154 WHILE period_cur%FOUND
155 LOOP
156
157 FETCH period_cur INTO g_period_tab(g_period_tab.COUNT+1);
158
159 END LOOP;
160
161 CLOSE period_cur;
162
163 -- Initialize on the current period p_pac_period_id
164
165 g_current_period_index := g_period_tab.FIRST;
166
167 WHILE g_period_tab(g_current_period_index) <> p_pac_period_id
168 LOOP
169
170 g_current_period_index := g_period_tab.NEXT(g_current_period_index);
171
172 END LOOP;
173
174 l_current_period := g_period_tab(g_current_period_index);
175
176 -- The sequence of the plsql table gperiod_tab is correct because the
177 -- cursor period_cur is ordered by period year then period num
178 -- FP:11i8-12.0: Bug 4028737 fix
179
180 LOOP
181
182 SELECT
183 cpql.begin_layer_quantity
184 ,cpic.market_value
185 INTO
186 l_begin_quantity
187 ,l_market_value
188 FROM
189 cst_pac_item_costs cpic
190 , cst_pac_quantity_layers cpql
191 WHERE cpic.pac_period_id = l_current_period
192 AND cpic.inventory_item_id = p_item_id
193 AND cpic.cost_group_id = p_cost_group_id
194 AND cpic.cost_layer_id = cpql.cost_layer_id;
195
196
197 -- Stop retrograding through previous periods if
198 -- you reach the first period
199 -- or the begin quantity is <= 0
200 -- or a market value was entered for the period
201 -- FP:11i8-12.0: Bug 4028737 fix
202
203 EXIT WHEN g_current_period_index = g_period_tab.FIRST
204 OR l_begin_quantity <= 0
205 OR l_market_value IS NOT NULL;
206
207 g_current_period_index := g_period_tab.PRIOR(g_current_period_index);
208 l_current_period := g_period_tab(g_current_period_index);
209
210 END LOOP;
211
212 g_old_cost_group_id := p_cost_group_id;
213
214
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
221 , 'find_first_period'
222 );
223 END IF;
224 RAISE;
225 END find_first_period;
226
227 --=========================================================================
228 -- PROCEDURE : populate_layers PRIVATE
229 -- PARAMETERS : p_pac_period_id period id
230 -- : p_item_id inventory item id
231 -- : p_cost_group_id cost group id
232 -- : p_cost_type_id cost type id
233 -- : p_user_id user id
234 -- : p_login_id login id
235 -- : p_req_id requisition id
236 -- : p_prg_id prg id
237 -- : p_prg_appl_id prg appl id
238 -- COMMENT : This procedure finds the delta balance
239 -- : between periods and records it in
240 -- : cst_pac_quantity_layers for each
241 -- : period.
242 -- : This is neccessary for the lifo calcualtion
243 -- : the delta quantity may be a negative number.
244 -- PRE-COND : Calacualte the delta inventory per item per period
245 -- : and populate CST_PAC_QUANTITY_LAYER.LAYER_QUANTITY
246 -- : with that value.
247 -- UPDATED BY : Veeresha Javli
248 -- Recalculated total quantity and delta for the open period
249 -- layer
250 -- Perform recalculation only for open period
251 --=========================================================================
252 PROCEDURE populate_layers
253 ( p_pac_period_id IN NUMBER
254 , p_item_id IN NUMBER
255 , p_cost_group_id IN NUMBER
256 , p_cost_type_id IN NUMBER
257 , p_user_id IN NUMBER
258 , p_login_id IN NUMBER
259 , p_req_id IN NUMBER
260 , p_prg_id IN NUMBER
261 , p_prg_appl_id IN NUMBER
262 )
263 IS
264
265 --==============
266 -- CURSORS
267 --==============
268
269 -- Cursor to get issue qty, make qty and buy qty
270 CURSOR get_quantity_cur(c_period_id NUMBER
271 ,c_cost_group_id NUMBER
272 ,c_inventory_item_id NUMBER) IS
273 SELECT
274 nvl(buy_quantity,0)
275 ,nvl(make_quantity,0)
276 ,nvl(issue_quantity,0)
277 ,market_value
278 FROM
279 cst_pac_item_costs
280 WHERE pac_period_id = c_period_id
281 AND cost_group_id = c_cost_group_id
282 AND inventory_item_id = c_inventory_item_id;
283
284 CURSOR get_period_status_cur(c_period_id NUMBER) IS
285 SELECT open_flag
286 FROM CST_PAC_PERIODS
287 WHERE pac_period_id = c_period_id;
288
289
290 --=================
291 -- LOCAL VARIABLES
292 --=================
293
294 l_current_total NUMBER;
295 l_previous_total NUMBER;
296 l_delta_period NUMBER;
297 l_delta_period_index BINARY_INTEGER;
298 l_delta_quantity NUMBER;
299 l_market_value NUMBER;
300 l_buy_quantity NUMBER;
301 l_make_quantity NUMBER;
302 l_issue_quantity NUMBER;
303 l_open_flag VARCHAR2(1);
304
305 BEGIN
306
307 -- initialise local variables
308
309 l_delta_period_index := g_current_period_index;
310 l_delta_period := g_period_tab(l_delta_period_index);
311 l_delta_quantity := 0;
312 l_current_total := 0;
313 l_previous_total := 0;
314
315 LOOP
316
317 SELECT
318 total_layer_quantity
319 , market_value
320 INTO
321 l_current_total
322 , l_market_value
323 FROM
324 cst_pac_item_costs
325 WHERE pac_period_id = l_delta_period
326 AND inventory_item_id = p_item_id
327 AND cost_group_id = p_cost_group_id;
328
329 -- If a market value was entered then the delta is the total quantity
330
331 IF
332 l_market_value IS NULL
333 THEN
334 l_delta_quantity := l_current_total - l_previous_total;
335 ELSE
336 l_delta_quantity := l_current_total;
337 END IF;
338
339 UPDATE cst_pac_quantity_layers
340 SET
341 last_updated_by = p_user_id
342 , last_update_date = sysdate
343 , last_update_login = p_login_id
344 , request_id = p_req_id
345 , program_application_id = p_prg_appl_id
346 , program_id = p_prg_id
347 , program_update_date = sysdate
348 , layer_quantity = l_delta_quantity
349 WHERE pac_period_id = l_delta_period
350 AND inventory_item_id = p_item_id
351 AND cost_group_id = p_cost_group_id;
352
353 EXIT WHEN l_delta_period_index = g_period_tab.LAST;
354
355 l_delta_period_index := g_period_tab.NEXT(l_delta_period_index);
356 l_delta_period := g_period_tab(l_delta_period_index);
357
358 l_previous_total := l_current_total;
359
360
361 END LOOP;
362
363 -- Recalculate total quantity and delta for the open period layer
364
365 -- get the status of the last period
366 OPEN get_period_status_cur(l_delta_period);
367
368 FETCH get_period_status_cur
369 INTO l_open_flag;
370
371 CLOSE get_period_status_cur;
372
373 -- Check whether period is open
374 IF (l_open_flag = 'Y') THEN
378 ,p_item_id );
375 -- get the quantities
376 OPEN get_quantity_cur(l_delta_period
377 ,p_cost_group_id
379
380 FETCH get_quantity_cur
381 INTO l_buy_quantity
382 ,l_make_quantity
383 ,l_issue_quantity
384 ,l_market_value;
385
386 CLOSE get_quantity_cur;
387
388 l_current_total := l_previous_total + l_buy_quantity + l_make_quantity - l_issue_quantity;
389
390 UPDATE cst_pac_item_costs
391 SET
392 last_updated_by = p_user_id
393 ,last_update_date = sysdate
394 ,last_update_login = p_login_id
395 ,request_id = p_req_id
396 ,program_application_id = p_prg_appl_id
397 ,program_id = p_prg_id
398 ,program_update_date = sysdate
399 ,total_layer_quantity = l_current_total
400 WHERE pac_period_id = l_delta_period
401 AND inventory_item_id = p_item_id
402 AND cost_group_id = p_cost_group_id;
403
404
405 IF
406 l_market_value IS NULL
407 THEN
408 l_delta_quantity := l_current_total - l_previous_total;
409 ELSE
410 l_delta_quantity := l_current_total;
411 END IF;
412
413
414 UPDATE cst_pac_quantity_layers
415 SET
416 last_updated_by = p_user_id
417 , last_update_date = sysdate
418 , last_update_login = p_login_id
419 , request_id = p_req_id
420 , program_application_id = p_prg_appl_id
421 , program_id = p_prg_id
422 , program_update_date = sysdate
423 , layer_quantity = l_delta_quantity
424 WHERE pac_period_id = l_delta_period
425 AND inventory_item_id = p_item_id
426 AND cost_group_id = p_cost_group_id;
427
428 END IF; -- open flag
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
433 THEN
434 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
435 , 'populate_layers'
436 );
437 END IF;
438 RAISE;
439 END populate_layers;
440
441 --=========================================================================
442 -- PROCEDURE : calc_lifo_cost PRIVATE
443 -- PARAMETERS : p_pac_period_id period id
444 -- : p_cost_group_id cost group id
445 -- : p_cost_type_id cost type id
446 -- : p_user_id user id
447 -- COMMENT : Calculate the Incremental LIFO item cost and populate
448 -- CST_PAC_ITEM_COSTS.ITEM_COST with that value.
449 -- PRE-COND : The delta quantity can be pulled for CST_PAC_QUANTITY_LAYERS.
450 -- The weighted average cost per item per period can be easily
451 -- calculated
452 --=========================================================================
453 PROCEDURE calc_lifo_cost
454 ( p_pac_period_id IN NUMBER
455 , p_item_id IN NUMBER
456 , p_cost_group_id IN NUMBER
457 , p_cost_type_id IN NUMBER
458 , p_user_id IN NUMBER
459 )
460 IS
461
462 --================
463 -- TYPES
464 --================
465
466 TYPE l_inventory_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
467
468 --=================
469 -- LOCAL VARIABLES
470 --=================
471
472 l_process_error EXCEPTION;
473 l_wac_cost NUMBER;
474 l_quantity_layers NUMBER;
475 l_total_quantity NUMBER;
476 l_lifo_cost NUMBER;
477 l_inventory_value NUMBER;
478 l_x_quant NUMBER;
479 l_y_quant NUMBER;
480 l_current_period NUMBER;
481 l_delta_period_index BINARY_INTEGER;
482 l_delta_period NUMBER;
483 l_market_value NUMBER;
484 j_market_value NUMBER;
485 l_justification VARCHAR2(240);
486 l_rowid ROWID;
487 l_rowid_char VARCHAR2(24);
488 l_inventory_tab l_inventory_tbl_type;
489 l_empty_inventory_tab l_inventory_tbl_type;
490
491
492 buy_quantity NUMBER;
493 item_buy_cost NUMBER;
494 make_quantity NUMBER;
495 item_make_cost NUMBER;
496 inventory_item_id NUMBER;
497 wac_cost NUMBER;
498
499
500 -- Now calculate the LIFO cost and populate item_cost
501 -- in cst_mgd_lifo_item_costs
502
503 BEGIN
504
505 -- initialise local variables
506
507 l_wac_cost := 0;
508 l_quantity_layers := 0;
509 l_total_quantity := 0;
510 l_lifo_cost := 0;
511 l_inventory_value := 0;
512 l_x_quant := 0;
513 l_y_quant := 0;
514 l_current_period := g_period_tab(g_current_period_index);
515 l_delta_period_index := g_current_period_index;
516 l_delta_period := l_current_period;
517
518 -- initialize the PL/SQL table
519
520 l_inventory_tab(0) := 0;
521
522 FOR l_inventory_index IN g_period_tab.FIRST .. g_period_tab.LAST
523 LOOP
527 LOOP
524 l_inventory_tab(l_inventory_index) := 0;
525 END LOOP;
526
528
529 SELECT
530 cpic.buy_quantity
531 , cpic.item_buy_cost
532 , cpic.make_quantity
533 , cpic.item_make_cost
534 , cpic.inventory_item_id
535 , cpic.market_value
536 INTO
537 buy_quantity
538 , item_buy_cost
539 , make_quantity
540 , item_make_cost
541 , inventory_item_id
542 , l_market_value
543 FROM
544 cst_pac_item_costs cpic
545 WHERE cpic.pac_period_id = l_current_period
546 AND cpic.inventory_item_id = p_item_id
547 AND cpic.cost_group_id = p_cost_group_id;
548
549 -- If a market value was entered the use that in place of weighted average
550
551 IF
552 l_market_value IS NOT NULL
553 THEN
554 SELECT
555 cpql.layer_quantity
556 , l_market_value
557 INTO
558 l_quantity_layers
559 , l_wac_cost
560 FROM
561 cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
562 WHERE cpic.pac_period_id = cpql.pac_period_id
563 AND cpic.cost_group_id = cpql.cost_group_id
564 AND cpic.inventory_item_id = cpql.inventory_item_id
565 AND cpic.pac_period_id = l_current_period
566 AND cpic.inventory_item_id = p_item_id
567 AND cpic.cost_group_id = p_cost_group_id;
568 ELSE
569 SELECT
570 cpql.layer_quantity
571 , DECODE((cpic.buy_quantity + cpic.make_quantity)
572 , 0, 0
573 , (cpic.buy_quantity * cpic.item_buy_cost +
574 cpic.make_quantity * cpic.item_make_cost)/
575 (cpic.buy_quantity + cpic.make_quantity)
576 )
577 INTO
578 l_quantity_layers
579 , l_wac_cost
580 FROM
581 cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
582 WHERE cpic.pac_period_id = cpql.pac_period_id
583 AND cpic.cost_group_id = cpql.cost_group_id
584 AND cpic.inventory_item_id = cpql.inventory_item_id
585 AND cpic.pac_period_id = l_current_period
586 AND cpic.inventory_item_id = p_item_id
587 AND cpic.cost_group_id = p_cost_group_id;
588 END IF;
589
590 -- finds the delta quantity for the period being calculated for
591 -- The delta has been previously calculated and populated in
592 -- cst_pac_quantity_layers
593
594 l_x_quant := l_quantity_layers;
595
596 -- if there is a negative delta quantity. Work out
597 -- which year the inventory for this item
598 -- should be removed from with the following
599 -- while loop
600
601 l_delta_period_index := g_current_period_index;
602
603 WHILE l_x_quant < 0 AND l_delta_period_index <> g_period_tab.FIRST
604 LOOP
605
606 l_delta_period_index := g_period_tab.PRIOR(l_delta_period_index);
607 l_delta_period := g_period_tab(l_delta_period_index);
608
609 SELECT
610 cpql.layer_quantity
611 , DECODE((cpic.buy_quantity + cpic.make_quantity)
612 , 0, 0
613 , (cpic.buy_quantity * cpic.item_buy_cost +
614 cpic.make_quantity * cpic.item_make_cost)/
615 (cpic.buy_quantity + cpic.make_quantity))
616 , cpic.market_value
617 INTO
618 l_y_quant
619 , l_wac_cost
620 , j_market_value
621 FROM cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
622 WHERE cpic.pac_period_id = cpql.pac_period_id
623 AND cpic.cost_group_id = cpql.cost_group_id
624 AND cpic.inventory_item_id = cpql.inventory_item_id
625 AND cpic.pac_period_id = l_delta_period
626 AND cpic.inventory_item_id = p_item_id
627 AND cpic.cost_group_id = p_cost_group_id;
628
629 IF j_market_value IS NOT NULL THEN
630 l_wac_cost := j_market_value;
631 j_market_value := NULL;
632 END IF;
633
634 l_x_quant := l_x_quant + l_y_quant;
635
636 END LOOP;
637
638 l_inventory_tab(g_current_period_index) :=
639 l_inventory_tab(l_delta_period_index - 1)
640 + l_x_quant * l_wac_cost;
641
642 -- Using current period parameter p_pac_period_id
643 -- as the premise to exit, instead of
644 -- gperiod_tab.LAST allows more flexibility
645 -- You can calculate the lifo unit cost for
646 -- earlier periods
647
648 EXIT WHEN g_period_tab(g_current_period_index) = p_pac_period_id;
649
650 g_current_period_index := g_period_tab.NEXT(g_current_period_index);
651 l_current_period := g_period_tab(g_current_period_index);
652
653 END LOOP;
654
655 SELECT
656 total_layer_quantity
657 INTO
658 l_total_quantity
659 FROM cst_pac_item_costs
660 WHERE pac_period_id = l_current_period
661 AND inventory_item_id = p_item_id
662 AND cost_group_id = p_cost_group_id;
663
664 IF l_total_quantity <= 0
665 THEN
666 l_lifo_cost := 0;
667 ELSE
668 l_lifo_cost := l_inventory_tab(g_current_period_index)/l_total_quantity;
669 END IF;
670
671 SELECT rowid
675 ,l_market_value
672 ,market_value
673 ,justification
674 INTO l_rowid
676 ,l_justification
677 FROM cst_pac_item_costs
678 WHERE pac_period_id = l_current_period
679 AND inventory_item_id = p_item_id
680 AND cost_group_id = p_cost_group_id;
681
682 l_rowid_char := ROWIDTOCHAR(l_rowid);
683
684 -- Call the table handler to update item cost
685
686 CST_PAC_ITEM_COSTS_PKG.update_row( l_rowid_char
687 , l_lifo_cost
688 , l_market_value
689 , l_justification
690 , sysdate
691 , p_user_id
692 );
693
694 -- clean PL/SQL table of previous calculations
695
696 l_inventory_tab := l_empty_inventory_tab;
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 THEN
702 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
703 , 'calc_lifo_cost'
704 );
705 END IF;
706 RAISE;
707 END calc_lifo_cost;
708
709 --=========================================================================
710 -- PROCEDURE : lifo_cost_processor PUBLIC
711 -- PARAMETERS : p_pac_period_id period id
712 -- : p_cost_group_id cost group id
713 -- : p_cost_type_id cost type id
714 -- : p_user_id user id
715 -- : p_login_id login id
716 -- : p_req_id requisition id
717 -- : p_prg_id prg id
718 -- : p_prg_appl_id prg appl id
719 -- : x_retcode 0 success, 1 warning, 2 error
720 -- : x_errbuff error buffer
721 -- COMMENT : Gateway procedure to the three procedures that calcualate
722 -- incremental LIFO. Called from the pac worker after
723 -- transactional processing and loops through all inventory
724 -- items for a particular period.
725 -- PRE-COND : The weighted average cost recorded in CST_PAC_ITEM_COSTS
726 -- for the period must be solely for items bought or made in
727 -- that period.
728 --=========================================================================
729 PROCEDURE lifo_cost_processor
730 ( p_pac_period_id IN NUMBER
731 , p_cost_group_id IN NUMBER
732 , p_cost_type_id IN NUMBER
733 , p_user_id IN NUMBER
734 , p_login_id IN NUMBER
735 , p_req_id IN NUMBER
736 , p_prg_id IN NUMBER
737 , p_prg_appl_id IN NUMBER
738 , x_retcode OUT NOCOPY NUMBER
739 , x_errbuff OUT NOCOPY VARCHAR2
740 , x_errcode OUT NOCOPY VARCHAR2
741 )
742 IS
743
744 --=================
745 -- CURSORS
746 --=================
747
748 CURSOR item_cur IS
749 SELECT
750 inventory_item_id
751 FROM
752 cst_pac_item_costs
753 WHERE pac_period_id = p_pac_period_id
754 AND cost_group_id = p_cost_group_id;
755
756 --=================
757 -- LOCAL VARIABLES
758 --=================
759
760 l_current_item NUMBER;
761
762 BEGIN
763
764 -- initialize the message stack
765
766 FND_MSG_PUB.Initialize;
767
768 -- loop on items
769 OPEN item_cur;
770
771 LOOP
772
773 FETCH item_cur INTO l_current_item;
774 IF item_cur%NOTFOUND
775 THEN
776 EXIT;
777 END IF;
778
779 -- find the first period to calculate from
780
781 CST_MGD_LIFO_COST_PROCESSOR.find_first_period( p_pac_period_id
782 , l_current_item
783 , p_cost_group_id
784 , p_cost_type_id
785 );
786
787 -- record the delta quantity between periods
788
789 CST_MGD_LIFO_COST_PROCESSOR.populate_layers( p_pac_period_id
790 , l_current_item
791 , p_cost_group_id
792 , p_cost_type_id
793 , p_user_id
794 , p_login_id
795 , p_req_id
796 , p_prg_id
797 , p_prg_appl_id
798 );
799
800 -- calculate and record the lifo item cost
801
802 CST_MGD_LIFO_COST_PROCESSOR.calc_lifo_cost( p_pac_period_id
803 , l_current_item
804 , p_cost_group_id
805 , p_cost_type_id
806 , p_user_id
810 END LOOP;
807 );
808
809
811
812 -- report success
813
814 x_errbuff := NULL;
815 x_retcode := 0;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
820 THEN
821 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
822 , 'lifo_cost_processor'
823 );
824 END IF;
825 x_retcode := 2;
826 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
827 END lifo_cost_processor;
828
829
830
831 --=========================================================================
832 -- PROCEDURE : pop_summary_data PUBLIC
833 -- PARAMETERS : p_legal_entity legal entity
834 -- : p_pac_period_id period id
835 -- : p_cost_group_id cost group id
836 -- : p_cost_type_id cost type id
837 -- : p_master_org master organization
838 -- : p_item_code_from beginning of item range
839 -- : p_item_code_to end of item range
840 -- COMMENT : Procedure that populates a temporary table with the
841 -- exact data required for the Periodic Incremental LIFO
842 -- Valuation Report (Summary).
843 -- PRE-COND : The procedure is called from a public procedure called
844 -- CST_MGD_LIFO_COST_PROCESSOR.populate_temp_table
845 --=========================================================================
846 PROCEDURE pop_summary_data
847 ( p_legal_entity_id IN NUMBER
848 , p_pac_period_id IN NUMBER
849 , p_cost_group_id IN NUMBER
850 , p_cost_type_id IN NUMBER
851 , p_master_org IN NUMBER
852 , p_item_from IN NUMBER
853 , p_item_to IN NUMBER
854 )
855 IS
856
857 --=================
858 -- CURSORS
859 --=================
860
861 CURSOR item_cur IS
862 SELECT
863 inventory_item_id
864 FROM
865 cst_pac_item_costs
866 WHERE pac_period_id = p_pac_period_id
867 AND cost_group_id = p_cost_group_id
868 AND inventory_item_id BETWEEN p_item_from AND p_item_to;
869
870 --================
871 -- TYPES
872 --================
873
874 TYPE l_inventory_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
875
876 --=================
877 -- LOCAL VARIABLES
878 --=================
879
880 l_process_error EXCEPTION;
881 l_current_item NUMBER;
882 l_market_value NUMBER;
883 l_inventory_tab l_inventory_tbl_type;
884 l_empty_inventory_tab l_inventory_tbl_type;
885
886 BEGIN
887
888 -- initialize the message stack
889
890 FND_MSG_PUB.Initialize;
891
892 -- loop on items
893 OPEN item_cur;
894
895 LOOP
896
897 FETCH item_cur INTO l_current_item;
898 IF item_cur%NOTFOUND
899 THEN
900 EXIT;
901 END IF;
902
903 SELECT
904 market_value
905 INTO
906 l_market_value
907 FROM
908 cst_pac_item_costs
909 WHERE pac_period_id = p_pac_period_id
910 AND inventory_item_id = l_current_item
911 AND cost_group_id = p_cost_group_id;
912
913 IF l_market_value IS null
914 THEN
915
916 INSERT into CSTGILEV_TEMP(
917 item_id
918 , item_desc
919 , period_id
920 , period_name
921 , wac
922 , lifo_cost
923 , layer_quantity
924 , total_layer_quantity
925 , item_code
926 , uom_code
927 , inventory_value)
928 SELECT
929 pic.inventory_item_id
930 , msi.description
931 , pp.pac_period_id
932 , pp.period_name
933 , DECODE((pic.make_quantity+pic.buy_quantity)
934 , 0, 0
935 ,(pic.item_make_cost*pic.make_quantity +
936 pic.item_buy_cost*pic.buy_quantity)/
937 ( pic.make_quantity+pic.buy_quantity))
938 , pic.item_cost
939 , pql.layer_quantity
940 , pic.total_layer_quantity
941 , kfv.concatenated_segments
942 , msi.primary_uom_code
943 , (pic.item_cost * pic.total_layer_quantity)
944 FROM
945 cst_cost_groups cg
946 , cst_le_cost_types clt
947 , cst_pac_periods pp
948 , cst_pac_item_costs pic
949 , cst_pac_quantity_layers pql
950 , mtl_system_items msi
951 , mtl_system_items_kfv kfv
952 WHERE clt.legal_entity = p_legal_entity_id
953 AND pp.legal_entity = clt.legal_entity
954 AND cg.legal_entity = clt.legal_entity
955 AND cg.cost_group_id = p_cost_group_id
956 AND pp.pac_period_id = p_pac_period_id
957 AND clt.cost_type_id = pp.cost_type_id
958 AND clt.cost_type_id = p_cost_type_id
962 AND pql.cost_group_id = pic.cost_group_id
959 AND pic.cost_group_id = cg.cost_group_id
960 AND pic.pac_period_id = p_pac_period_id
961 AND pql.pac_period_id = pic.pac_period_id
963 AND pql.inventory_item_id = l_current_item
964 AND msi.inventory_item_id = l_current_item
965 AND msi.organization_id = cg.organization_id
966 AND kfv.inventory_item_id = l_current_item
967 AND pic.inventory_item_id = kfv.inventory_item_id
968 AND kfv.organization_id = p_master_org
969 AND pic.cost_layer_id = pql.cost_layer_id;
970
971 ELSE
972
973 INSERT into CSTGILEV_TEMP(
974 item_id
975 , item_desc
976 , period_id
977 , period_name
978 , wac
979 , lifo_cost
980 , market_value
981 , layer_quantity
982 , total_layer_quantity
983 , item_code
984 , uom_code
985 , inventory_value)
986 SELECT
987 pic.inventory_item_id
988 , msi.description
989 , pp.pac_period_id
990 , pp.period_name
991 , DECODE((pic.make_quantity+pic.buy_quantity)
992 , 0, 0
993 ,(pic.item_make_cost*pic.make_quantity +
994 pic.item_buy_cost*pic.buy_quantity)/
995 ( pic.make_quantity+pic.buy_quantity))
996 , pic.item_cost
997 , pic.market_value
998 , pql.layer_quantity
999 , pic.total_layer_quantity
1000 , kfv.concatenated_segments
1001 , msi.primary_uom_code
1002 , (pic.market_value * pic.total_layer_quantity)
1003 FROM
1004 cst_cost_groups cg
1005 , cst_le_cost_types clt
1006 , cst_pac_periods pp
1007 , cst_pac_item_costs pic
1008 , cst_pac_quantity_layers pql
1009 , mtl_system_items msi
1010 , mtl_system_items_kfv kfv
1011 WHERE clt.legal_entity = p_legal_entity_id
1012 AND pp.legal_entity = clt.legal_entity
1013 AND cg.legal_entity = clt.legal_entity
1014 AND cg.cost_group_id = p_cost_group_id
1015 AND pp.pac_period_id = p_pac_period_id
1016 AND clt.cost_type_id = pp.cost_type_id
1017 AND clt.cost_type_id = p_cost_type_id
1018 AND pic.cost_group_id = cg.cost_group_id
1019 AND pic.pac_period_id = p_pac_period_id
1020 AND pql.pac_period_id = pic.pac_period_id
1021 AND pql.cost_group_id = pic.cost_group_id
1022 AND pql.inventory_item_id = l_current_item
1023 AND msi.inventory_item_id = l_current_item
1024 AND msi.organization_id = cg.organization_id
1025 AND kfv.inventory_item_id = l_current_item
1026 AND pic.inventory_item_id = kfv.inventory_item_id
1027 AND kfv.organization_id = p_master_org
1028 AND pic.cost_layer_id = pql.cost_layer_id;
1029
1030 END IF;
1031
1032 END LOOP;
1033
1034 -- find the total value of all calculated inventory values
1035
1036 UPDATE CSTGILEV_TEMP
1037 SET total_inventory_value =
1038 (SELECT SUM(inventory_value)
1039 FROM cstgilev_temp
1040 WHERE period_id = p_pac_period_id)
1041 WHERE period_id = p_pac_period_id
1042 AND item_id = l_current_item;
1043
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1047 THEN
1048 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
1049 , 'pop_summary_data'
1050 );
1051 END IF;
1052 RAISE;
1053 END pop_summary_data;
1054
1055 --=========================================================================
1056 -- PROCEDURE : pop_detail_data PUBLIC
1057 -- PARAMETERS : p_legal_entity legal entity
1058 -- : p_pac_period_id period id
1059 -- : p_cost_group_id cost group id
1060 -- : p_cost_type_id cost type id
1061 -- : p_master_org master organization
1062 -- : p_item_code_from beginning of range
1063 -- : p_item_code_to end of item range
1064 -- COMMENT : Procedure that populates a temporary table with the
1065 -- exact data required for the Periodic Incremental LIFO
1066 -- Valuation Report (Detail).
1067 -- PRE-COND : The procedure is called from a public procedure called
1068 -- CST_MGD_LIFO_COST_PROCESSOR.populate_temp_table
1069 --=========================================================================
1070 PROCEDURE pop_detail_data
1071 ( p_legal_entity_id IN NUMBER
1072 , p_pac_period_id IN NUMBER
1073 , p_cost_group_id IN NUMBER
1074 , p_cost_type_id IN NUMBER
1075 , p_master_org IN NUMBER
1076 , p_item_from IN NUMBER
1077 , p_item_to IN NUMBER
1078 )
1079 IS
1080
1081 --=================
1082 -- CURSORS
1083 --=================
1084
1085 CURSOR item_cur IS
1086 SELECT
1087 inventory_item_id
1088 FROM
1092 AND inventory_item_id BETWEEN p_item_from AND p_item_to;
1089 cst_pac_item_costs
1090 WHERE pac_period_id = p_pac_period_id
1091 AND cost_group_id = p_cost_group_id
1093
1094 --================
1095 -- TYPES
1096 --================
1097
1098 TYPE l_inventory_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1099
1100 --=================
1101 -- LOCAL VARIABLES
1102 --=================
1103
1104 l_process_error EXCEPTION;
1105 l_current_period NUMBER;
1106 l_market_value NUMBER;
1107 l_current_item NUMBER;
1108 l_inventory_tab l_inventory_tbl_type;
1109 l_empty_inventory_tab l_inventory_tbl_type;
1110
1111 BEGIN
1112
1113 -- initialize the message stack
1114
1115 FND_MSG_PUB.Initialize;
1116
1117 -- loop on items
1118 OPEN item_cur;
1119
1120 LOOP
1121
1122 FETCH item_cur INTO l_current_item;
1123
1124 IF item_cur%NOTFOUND
1125 THEN
1126 EXIT;
1127 END IF;
1128
1129 -- initialize the PL/SQL table
1130
1131 l_inventory_tab(0) := 0;
1132
1133 -- Find the first period to calculate from
1134
1135 CST_MGD_LIFO_COST_PROCESSOR.find_first_period( p_pac_period_id
1136 , l_current_item
1137 , p_cost_group_id
1138 , p_cost_type_id
1139 );
1140
1141 FOR l_inventory_index IN g_period_tab.FIRST .. g_period_tab.LAST
1142 LOOP
1143 l_inventory_tab(l_inventory_index) := 0;
1144 END LOOP;
1145
1146 LOOP
1147
1148 SELECT
1149 market_value
1150 INTO
1151 l_market_value
1152 FROM
1153 cst_pac_item_costs
1154 WHERE pac_period_id = g_period_tab(g_current_period_index)
1155 AND inventory_item_id = l_current_item
1156 AND cost_group_id = p_cost_group_id;
1157
1158 -- Exit loop if period = current period
1159
1160 EXIT WHEN g_period_tab(g_current_period_index) = p_pac_period_id;
1161
1162 IF l_market_value IS NULL
1163 THEN
1164
1165 INSERT into CSTGILEV_TEMP(
1166 item_id
1167 , item_desc
1168 , period_id
1169 , period_name
1170 , wac
1171 , layer_quantity
1172 , total_layer_quantity
1173 , item_code
1174 , uom_code
1175 , inventory_value)
1176 SELECT
1177 pic.inventory_item_id
1178 , msi.description
1179 , pp.pac_period_id
1180 , pp.period_name
1181 , DECODE((pic.make_quantity+pic.buy_quantity)
1182 , 0, 0
1183 ,(pic.item_make_cost*pic.make_quantity +
1184 pic.item_buy_cost*pic.buy_quantity)/
1185 ( pic.make_quantity+pic.buy_quantity))
1186 , pql.layer_quantity
1187 , pic.total_layer_quantity
1188 , kfv.concatenated_segments
1189 , msi.primary_uom_code
1190 , (pic.item_cost * pic.total_layer_quantity)
1191 FROM
1192 cst_cost_groups cg
1193 , cst_le_cost_types clt
1194 , cst_pac_periods pp
1195 , cst_pac_item_costs pic
1196 , cst_pac_quantity_layers pql
1197 , mtl_system_items msi
1198 , mtl_system_items_kfv kfv
1199 WHERE clt.legal_entity = p_legal_entity_id
1200 AND pp.legal_entity = clt.legal_entity
1201 AND cg.legal_entity = clt.legal_entity
1202 AND cg.cost_group_id = p_cost_group_id
1203 AND pp.pac_period_id = g_period_tab(g_current_period_index)
1204 AND clt.cost_type_id = pp.cost_type_id
1205 AND clt.cost_type_id = p_cost_type_id
1206 AND pic.cost_group_id = cg.cost_group_id
1207 AND pic.pac_period_id = g_period_tab(g_current_period_index)
1208 AND pql.pac_period_id = g_period_tab(g_current_period_index)
1209 AND pql.cost_group_id = pic.cost_group_id
1210 AND pql.inventory_item_id = l_current_item
1211 AND msi.inventory_item_id = pql.inventory_item_id
1212 AND msi.organization_id = cg.organization_id
1213 AND kfv.inventory_item_id = msi.inventory_item_id
1214 AND pic.inventory_item_id = kfv.inventory_item_id
1215 AND kfv.organization_id = p_master_org
1216 AND pic.cost_layer_id = pql.cost_layer_id;
1217
1218
1219 ELSE
1220
1221 INSERT into CSTGILEV_TEMP(
1222 item_id
1223 , item_desc
1224 , period_id
1225 , period_name
1226 , wac
1227 , market_value
1228 , justification
1229 , layer_quantity
1230 , total_layer_quantity
1231 , item_code
1232 , uom_code
1233 , inventory_value)
1234 SELECT
1235 pic.inventory_item_id
1236 , msi.description
1237 , pp.pac_period_id
1238 , pp.period_name
1239 , pic.market_value
1243 , pic.total_layer_quantity
1240 , pic.market_value
1241 , pic.justification
1242 , pql.layer_quantity
1244 , kfv.concatenated_segments
1245 , msi.primary_uom_code
1246 , (pic.market_value * pic.total_layer_quantity)
1247 FROM
1248 cst_cost_groups cg
1249 , cst_le_cost_types clt
1250 , cst_pac_periods pp
1251 , cst_pac_item_costs pic
1252 , cst_pac_quantity_layers pql
1253 , mtl_system_items msi
1254 , mtl_system_items_kfv kfv
1255 WHERE clt.legal_entity = p_legal_entity_id
1256 AND pp.legal_entity = clt.legal_entity
1257 AND cg.legal_entity = clt.legal_entity
1258 AND cg.cost_group_id = p_cost_group_id
1259 AND pp.pac_period_id = g_period_tab(g_current_period_index)
1260 AND clt.cost_type_id = pp.cost_type_id
1261 AND clt.cost_type_id = p_cost_type_id
1262 AND pic.cost_group_id = cg.cost_group_id
1263 AND pic.pac_period_id = g_period_tab(g_current_period_index)
1264 AND pql.pac_period_id = g_period_tab(g_current_period_index)
1265 AND pql.cost_group_id = pic.cost_group_id
1266 AND pql.inventory_item_id = l_current_item
1267 AND msi.inventory_item_id = pql.inventory_item_id
1268 AND msi.organization_id = cg.organization_id
1269 AND kfv.inventory_item_id = msi.inventory_item_id
1270 AND pic.inventory_item_id = kfv.inventory_item_id
1271 AND kfv.organization_id = p_master_org
1272 AND pic.cost_layer_id = pql.cost_layer_id;
1273
1274 END IF;
1275
1276 g_current_period_index := g_period_tab.NEXT(g_current_period_index);
1277 l_current_period := g_period_tab(g_current_period_index);
1278
1279 END LOOP;
1280
1281 -- The final report record has a unique format
1282 -- dependent on the existence of a market value
1283
1284 IF l_market_value IS NULL
1285 THEN
1286
1287 INSERT into CSTGILEV_TEMP(
1288 item_id
1289 , item_desc
1290 , period_id
1291 , period_name
1292 , wac
1293 , lifo_cost
1294 , justification
1295 , layer_quantity
1296 , total_layer_quantity
1297 , item_code
1298 , uom_code
1299 , inventory_value)
1300 SELECT
1301 pic.inventory_item_id
1302 , msi.description
1303 , pp.pac_period_id
1304 , pp.period_name
1305 , DECODE((pic.make_quantity+pic.buy_quantity)
1306 , 0, 0
1307 ,(pic.item_make_cost*pic.make_quantity +
1308 pic.item_buy_cost*pic.buy_quantity)/
1309 ( pic.make_quantity+pic.buy_quantity))
1310 , pic.item_cost lifo_cost
1311 , pic.justification
1312 , pql.layer_quantity
1313 , pic.total_layer_quantity
1314 , kfv.concatenated_segments
1315 , msi.primary_uom_code
1316 , (pic.item_cost * pic.total_layer_quantity)
1317 FROM
1318 cst_cost_groups cg
1319 , cst_le_cost_types clt
1320 , cst_pac_periods pp
1321 , cst_pac_item_costs pic
1322 , cst_pac_quantity_layers pql
1323 , mtl_system_items msi
1324 , mtl_system_items_kfv kfv
1325 WHERE clt.legal_entity = p_legal_entity_id
1326 AND pp.legal_entity = clt.legal_entity
1327 AND cg.legal_entity = clt.legal_entity
1328 AND cg.cost_group_id = p_cost_group_id
1329 AND pp.pac_period_id = p_pac_period_id
1330 AND clt.cost_type_id = pp.cost_type_id
1331 AND clt.cost_type_id = p_cost_type_id
1332 AND pic.cost_group_id = cg.cost_group_id
1333 AND pic.pac_period_id = p_pac_period_id
1334 AND pql.pac_period_id = pic.pac_period_id
1335 AND pql.cost_group_id = pic.cost_group_id
1336 AND pql.inventory_item_id = l_current_item
1337 AND msi.inventory_item_id = pql.inventory_item_id
1338 AND msi.organization_id = cg.organization_id
1339 AND kfv.inventory_item_id = msi.inventory_item_id
1340 AND pic.inventory_item_id = kfv.inventory_item_id
1341 AND kfv.organization_id = p_master_org
1342 AND pic.cost_layer_id = pql.cost_layer_id;
1343
1344 ELSE
1345
1346 INSERT into CSTGILEV_TEMP(
1347 item_id
1348 , item_desc
1349 , period_id
1350 , period_name
1351 , wac
1352 , lifo_cost
1353 , market_value
1354 , justification
1355 , layer_quantity
1356 , total_layer_quantity
1357 , item_code
1358 , uom_code
1359 , inventory_value)
1360 SELECT
1361 pic.inventory_item_id
1362 , msi.description
1363 , pp.pac_period_id
1364 , pp.period_name
1365 , DECODE((pic.make_quantity+pic.buy_quantity)
1366 , 0, 0
1367 ,(pic.item_make_cost*pic.make_quantity +
1368 pic.item_buy_cost*pic.buy_quantity)/
1369 ( pic.make_quantity+pic.buy_quantity))
1370 , pic.item_cost lifo_cost
1374 , pic.total_layer_quantity
1371 , pic.market_value
1372 , pic.justification
1373 , pql.layer_quantity
1375 , kfv.concatenated_segments
1376 , msi.primary_uom_code
1377 , (pic.market_value * pic.total_layer_quantity)
1378 FROM
1379 cst_cost_groups cg
1380 , cst_le_cost_types clt
1381 , cst_pac_periods pp
1382 , cst_pac_item_costs pic
1383 , cst_pac_quantity_layers pql
1384 , mtl_system_items msi
1385 , mtl_system_items_kfv kfv
1386 WHERE clt.legal_entity = p_legal_entity_id
1387 AND pp.legal_entity = clt.legal_entity
1388 AND cg.legal_entity = clt.legal_entity
1389 AND cg.cost_group_id = p_cost_group_id
1390 AND pp.pac_period_id = p_pac_period_id
1391 AND clt.cost_type_id = pp.cost_type_id
1392 AND clt.cost_type_id = p_cost_type_id
1393 AND pic.cost_group_id = cg.cost_group_id
1394 AND pic.pac_period_id = p_pac_period_id
1395 AND pql.pac_period_id = pic.pac_period_id
1396 AND pql.cost_group_id = pic.cost_group_id
1397 AND pql.inventory_item_id = l_current_item
1398 AND msi.inventory_item_id = pql.inventory_item_id
1399 AND msi.organization_id = cg.organization_id
1400 AND kfv.inventory_item_id = msi.inventory_item_id
1401 AND pic.inventory_item_id = kfv.inventory_item_id
1402 AND kfv.organization_id = p_master_org
1403 AND pic.cost_layer_id = pql.cost_layer_id;
1404
1405 END IF;
1406
1407
1408 -- Only insert one value into total_inventory_value
1409 -- so that the report sums correctly
1410
1411 UPDATE CSTGILEV_TEMP
1412 SET total_inventory_value =
1413 (SELECT
1414 inventory_value
1415 FROM cstgilev_temp
1416 WHERE period_id = p_pac_period_id
1417 AND item_id = l_current_item)
1418 WHERE period_id = p_pac_period_id
1419 AND item_id = l_current_item;
1420
1421 END LOOP;
1422
1423 -- clean PL/SQL table of previous calculations
1424
1425 l_inventory_tab := l_empty_inventory_tab;
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1430 THEN
1431 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
1432 , 'pop_detail_data'
1433 );
1434 END IF;
1435 RAISE;
1436 END pop_detail_data;
1437
1438 --=========================================================================
1439 -- PROCEDURE : populate_temp_table PUBLIC
1440 -- PARAMETERS : p_legal_entity legal entity
1441 -- : p_pac_period_id period id
1442 -- : p_cost_group_id cost group id
1443 -- : p_cost_type_id cost type id
1444 -- : p_item_code_from beginning of item range
1445 -- : p_item_code_to end of item range
1446 -- : x_retcode 0 success, 1 warning, 2 error
1447 -- : x_errbuff error buffer
1448 -- COMMENT : This Procedure decides whether to populate
1449 -- the temporary table CSTGILEV_TEMP with summarized
1450 -- or detailed information.
1451 -- PRE-COND : The procedure is called from a before report trigger in
1452 -- the incremental LIFO evaluation report. The cost processor
1453 -- has already run.
1454 --=========================================================================
1455 PROCEDURE populate_temp_table
1456 ( p_legal_entity_id IN NUMBER
1457 , p_pac_period_id IN NUMBER
1458 , p_cost_group_id IN NUMBER
1459 , p_cost_type_id IN NUMBER
1460 , p_detailed_report IN VARCHAR2
1461 , p_item_code_from IN VARCHAR2
1462 , p_item_code_to IN VARCHAR2
1463 , x_retcode OUT NOCOPY NUMBER
1464 , x_errbuff OUT NOCOPY VARCHAR2
1465 , x_errcode OUT NOCOPY VARCHAR2
1466 )
1467 IS
1468
1469 l_master_org NUMBER;
1470 l_min_item_from NUMBER;
1471 l_max_item_to NUMBER;
1472 l_item_from VARCHAR2(24);
1473 l_item_to VARCHAR2(24);
1474
1475
1476 BEGIN
1477
1478 l_master_org := 0;
1479
1480 -- First find the master organization
1481
1482 SELECT organization_id
1483 INTO l_master_org
1484 FROM cst_cost_groups
1485 WHERE cost_group_id = p_cost_group_id;
1486
1487 -- Calculate the min and max ranges
1488
1489 SELECT
1490 min(inventory_item_id)
1491 , max(inventory_item_id)
1492 INTO
1493 l_item_from
1494 , l_item_to
1495 FROM
1496 cst_pac_item_costs
1497 WHERE cost_group_id = p_cost_group_id
1498 AND pac_period_id = p_pac_period_id;
1499
1500 -- Find if the :from or :to range paramenters have values
1501 -- if they are null then set them to the appropriate
1502 -- min or max values
1503
1504 IF p_item_code_from IS not null
1505 THEN
1506 SELECT
1507 inventory_item_id
1508 INTO
1509 l_item_from
1510 FROM mtl_system_items_kfv
1514
1511 WHERE concatenated_segments = p_item_code_from
1512 AND organization_id = l_master_org;
1513 END IF;
1515 IF p_item_code_to IS not null
1516 THEN
1517 SELECT
1518 inventory_item_id
1519 INTO
1520 l_item_to
1521 FROM mtl_system_items_kfv
1522 WHERE concatenated_segments = p_item_code_to
1523 AND organization_id = l_master_org;
1524 END IF;
1525
1526 -- Call either the procedure for the summary or detailed report
1527 -- depending on input parameter
1528
1529 IF p_detailed_report = 'Y'
1530 THEN
1531
1532 -- If this is a detailed report populate the
1533 -- temporary table with period layers.
1534
1535 CST_MGD_LIFO_COST_PROCESSOR.pop_detail_data(p_legal_entity_id
1536 , p_pac_period_id
1537 , p_cost_group_id
1538 , p_cost_type_id
1539 , l_master_org
1540 , l_item_from
1541 , l_item_to
1542 );
1543 ELSE
1544
1545 -- If this is a summary report populate the
1546 -- temporary table with data representing the
1547 -- current period.
1548
1549 CST_MGD_LIFO_COST_PROCESSOR.pop_summary_data(p_legal_entity_id
1550 , p_pac_period_id
1551 , p_cost_group_id
1552 , p_cost_type_id
1553 , l_master_org
1554 , l_item_from
1555 , l_item_to
1556 );
1557
1558 END IF;
1559
1560 -- report success
1561
1562 x_errbuff := NULL;
1563 x_retcode := 0;
1564
1565 EXCEPTION
1566 WHEN OTHERS THEN
1567 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1568 THEN
1569 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
1570 , 'populate_temp_table'
1571 );
1572 END IF;
1573 x_retcode := 2;
1574 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1575 END populate_temp_table;
1576
1577
1578 --=========================================================================
1579 -- PROCEDURE : get_period_id PUBLIC
1580 -- PARAMETERS : p_interface_id interface id
1581 -- : p_legal_entity legal entity
1582 -- : p_cost_type_id cost type id
1583 -- : p_pac_period_id period id
1584 -- : p_err_num end of item range
1585 -- : p_err_code 0 success, 1 warning, 2 error
1586 -- : p_err_msg error buffer
1587 -- COMMENT : This procedere gets the period id to manage
1588 -- the LIFO loading layer utility
1589 -- PRE-COND :
1590 --=========================================================================
1591 PROCEDURE get_pac_id
1592 ( p_interface_header_id IN NUMBER
1593 , p_legal_entity IN NUMBER
1594 , p_cost_type_id IN NUMBER
1595 , p_pac_period_id OUT NOCOPY NUMBER
1596 , p_err_num OUT NOCOPY NUMBER
1597 , p_err_code OUT NOCOPY VARCHAR2
1598 , p_err_msg OUT NOCOPY VARCHAR2
1599 )
1600 IS
1601
1602 l_stmt_num NUMBER;
1603 l_count NUMBER;
1604
1605 BEGIN
1606 ----------------------------------------------------------------------
1607 -- Initialize Variables
1608 ----------------------------------------------------------------------
1609
1610 l_stmt_num := 1;
1611
1612 SELECT cpp.pac_period_id
1613 INTO p_pac_period_id
1614 FROM cst_pac_periods cpp
1615 WHERE (cpp.period_name, cpp.cost_type_id) =
1616 ( SELECT cpici.period_name,
1617 cct.cost_type_id
1618 FROM cst_pc_item_cost_interface cpici,
1619 cst_cost_types cct
1620 WHERE cpici.interface_header_id = p_interface_header_id
1621 AND cpici.cost_type = cct.cost_type
1622 )
1623 AND cpp.open_flag = 'N';
1624
1625 l_stmt_num := 2;
1626
1627 UPDATE cst_pc_item_cost_interface cpici
1628 SET cpici.pac_period_id = p_pac_period_id
1629 WHERE cpici.interface_header_id = p_interface_header_id;
1630
1631 l_stmt_num := 3;
1632
1633 SELECT count(*)
1634 INTO l_count
1635 FROM cst_pc_item_cost_interface cpici
1636 WHERE cpici.pac_period_id = p_pac_period_id
1637 AND (cpici.inventory_item_id,cpici.cost_group,cpici.cost_type) IN
1638 (
1639 SELECT inventory_item_id, cost_group , cost_type
1640 FROM cst_pc_item_cost_interface
1641 WHERE interface_header_id = p_interface_header_id
1642 );
1643
1644 IF l_count > 1 THEN
1645 p_pac_period_id := 0;
1646 p_err_num := 99;
1650 || '): '
1647 p_err_code := NULL;
1648 p_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR.get_pac_id('
1649 || to_char(l_stmt_num)
1651 ||'TOO MANY PERIODS',1,240);
1652 END IF;
1653
1654
1655 EXCEPTION
1656
1657 WHEN NO_DATA_FOUND THEN
1658 ROLLBACK;
1659 p_err_num := 1403;
1660 p_err_code := NULL;
1661 p_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR.get_pac_id('
1662 || to_char(l_stmt_num)
1663 || '): '
1664 ||SQLERRM,1,240);
1665
1666 WHEN OTHERS THEN
1667 ROLLBACK;
1668 p_err_num := SQLCODE;
1669 p_err_code := NULL;
1670 p_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR.get_pac_id('
1671 || to_char(l_stmt_num)
1672 || '): '
1673 ||SQLERRM,1,240);
1674
1675
1676 END get_pac_id;
1677
1678
1679 --=========================================================================
1680 -- PROCEDURE : check_quantity PUBLIC
1681 -- PARAMETERS : p_interface_group_id interface id
1682 -- : p_err_num end of item range
1683 -- : p_err_code 0 success, 1 warning, 2 error
1684 -- : p_err_msg error buffer
1685 -- COMMENT : This procedere check if layer quantity of period n is equal
1686 -- to begin layer quantity of period n+1 for the LIFO loading layer
1687 -- PRE-COND :
1688 --=========================================================================
1689 PROCEDURE check_quantity
1690 ( p_interface_group_id IN NUMBER
1691 , p_err_num OUT NOCOPY NUMBER
1692 , p_err_code OUT NOCOPY VARCHAR2
1693 , p_err_msg OUT NOCOPY VARCHAR2
1694 )
1695 IS
1696
1697 CURSOR c_interface (a_interface_group_id IN NUMBER) IS
1698 SELECT interface_header_id,
1699 cost_group,
1700 cost_type,
1701 inventory_item_id,
1702 begin_layer_quantity,
1703 layer_quantity
1704 FROM cst_pc_item_cost_interface
1705 WHERE interface_group_id = a_interface_group_id
1706 ORDER BY cost_group, cost_type, inventory_item_id, pac_period_id;
1707
1708 RECINTERFACE c_interface%ROWTYPE;
1709
1710 l_cost_group VARCHAR2(10) := NULL;
1711 l_cost_type VARCHAR2(10);
1712 l_inventory_item_id NUMBER;
1713 l_begin_layer_quantity NUMBER;
1714 l_layer_quantity NUMBER;
1715 l_primary_cost_method NUMBER;
1716 l_initial_quantity NUMBER;
1717
1718 l_stmt_num NUMBER;
1719
1720 BEGIN
1721 ----------------------------------------------------------------------
1722 -- Initialize Variables
1723 ----------------------------------------------------------------------
1724
1725 l_stmt_num := 1;
1726
1727
1728 OPEN c_interface(p_interface_group_id);
1729 LOOP
1730 FETCH c_interface INTO RECINTERFACE;
1731 IF (c_interface %NOTFOUND) THEN
1732 EXIT;
1733 ELSE
1734 IF l_cost_group IS NULL THEN -- First record fetched
1735 SELECT clct.primary_cost_method
1736 INTO l_primary_cost_method
1737 FROM cst_cost_types cct,
1738 cst_le_cost_types clct
1739 WHERE cct.cost_type_id = clct.cost_type_id
1740 AND clct.legal_entity =
1741 (SELECT DISTINCT ccg.legal_entity
1742 FROM cst_cost_groups ccg,
1743 cst_cost_group_assignments ccga
1744 WHERE ccg.cost_group_id = ccga.cost_group_id
1745 AND ccg.cost_group_type = 2
1746 AND ccg.cost_group IN
1747 (SELECT cpici.cost_group
1748 FROM cst_pc_item_cost_interface cpici
1749 WHERE cpici.interface_header_id =
1750 RECINTERFACE.interface_header_id
1751 )
1752 )
1753 AND cct.cost_type IN
1754 (SELECT cpici.cost_type
1755 FROM cst_pc_item_cost_interface cpici
1756 WHERE cpici.interface_header_id =
1757 RECINTERFACE.interface_header_id
1758 );
1759
1760 SELECT cpici.begin_layer_quantity
1761 INTO l_initial_quantity
1762 FROM cst_pc_item_cost_interface cpici
1763 WHERE cpici.interface_header_id =
1764 RECINTERFACE.interface_header_id;
1765
1766 IF l_primary_cost_method <> 4 OR l_initial_quantity <> 0 THEN
1767 EXIT;
1768 END IF;
1769 ELSIF (l_cost_group = RECINTERFACE.cost_group AND
1770 l_cost_type = RECINTERFACE.cost_type AND
1771 l_inventory_item_id = RECINTERFACE.inventory_item_id) THEN
1772 IF l_layer_quantity <> RECINTERFACE.begin_layer_quantity THEN
1773 p_err_num := 99;
1774 p_err_code := NULL;
1775 p_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR'
1776 ||'.check_quantity('
1777 || to_char(l_stmt_num)
1778 || '): '
1779 ||'begin layer quantity not correct'
1780 ||' for interface_id='
1784 SET process_flag = 3,
1781 ||TO_CHAR(RECINTERFACE.interface_header_id),1,240);
1782
1783 UPDATE cst_pc_item_cost_interface
1785 error_flag = 22,
1786 error_explanation = 'Begin layer quantity not correct for LIFO record'
1787 WHERE interface_header_id = RECINTERFACE.interface_header_id;
1788 COMMIT;
1789 EXIT;
1790 END IF;
1791 END IF;
1792 l_cost_group := RECINTERFACE.cost_group;
1793 l_cost_type := RECINTERFACE.cost_type;
1794 l_inventory_item_id := RECINTERFACE.inventory_item_id;
1795 l_begin_layer_quantity := RECINTERFACE.begin_layer_quantity;
1796 l_layer_quantity := RECINTERFACE.layer_quantity;
1797 END IF;
1798 END LOOP;
1799 CLOSE c_interface;
1800
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 ROLLBACK;
1804 p_err_num := SQLCODE;
1805 p_err_code := NULL;
1806 p_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR.check_quantity('
1807 || to_char(l_stmt_num)
1808 || '): '
1809 ||SQLERRM,1,240);
1810
1811
1812 END check_quantity;
1813
1814 --=========================================================================
1815 -- PROCEDURE : loading_lifo_cost PUBLIC
1816 -- PARAMETERS : p_interface_group_id interface unique id
1817 -- : p_err_num end of item range
1818 -- : p_err_code 0 success, 1 warning, 2 error
1819 -- : p_err_msg error buffer
1820 -- COMMENT : This procedure reads cost group, period id, item id from
1821 -- the interface header table and uses them as input to
1822 -- the standard procedure that calculates lifo.
1823 -- PRE-COND :
1824 --=========================================================================
1825 PROCEDURE loading_lifo_cost
1826 (p_interface_group_id IN NUMBER
1827 ,p_user_id IN NUMBER
1828 ,p_login_id IN NUMBER
1829 ,p_req_id IN NUMBER
1830 ,p_prg_id IN NUMBER
1831 ,p_prg_appl_id IN NUMBER
1832 ,x_err_num OUT NOCOPY NUMBER
1833 ,x_err_code OUT NOCOPY VARCHAR2
1834 ,x_err_msg OUT NOCOPY VARCHAR2
1835 )
1836 IS
1837
1838 CURSOR c_interface (a_interface_group_id IN NUMBER) IS
1839 SELECT pac_period_id,
1840 cost_group_id,
1841 cost_type,
1842 inventory_item_id,
1843 interface_header_id
1844 FROM cst_pc_item_cost_interface
1845 WHERE interface_group_id = a_interface_group_id
1846 ORDER BY cost_group, cost_type, inventory_item_id, pac_period_id;
1847
1848 RECINTERFACE c_interface%ROWTYPE;
1849
1850 l_stmt_num NUMBER;
1851 l_user_id NUMBER;
1852 l_cost_type_id NUMBER;
1853 l_primary_cost_method NUMBER;
1854 l_login_id NUMBER;
1855 l_req_id NUMBER;
1856 l_prg_id NUMBER;
1857 l_prg_appl_id NUMBER;
1858
1859
1860 BEGIN
1861 ----------------------------------------------------------------------
1862 -- Initialize Variables
1863 ----------------------------------------------------------------------
1864
1865 l_stmt_num := 1;
1866
1867 l_user_id := p_user_id;
1868 l_login_id := p_login_id;
1869 l_req_id := p_req_id;
1870 l_prg_id := p_prg_id;
1871 l_prg_appl_id := p_prg_appl_id;
1872
1873 OPEN c_interface(p_interface_group_id);
1874 LOOP
1875 FETCH c_interface INTO RECINTERFACE;
1876 IF (c_interface %NOTFOUND) THEN
1877 EXIT;
1878 ELSE
1879 SELECT clct.primary_cost_method
1880 INTO l_primary_cost_method
1881 FROM cst_cost_types cct,
1882 cst_le_cost_types clct
1883 WHERE cct.cost_type_id = clct.cost_type_id
1884 AND clct.legal_entity =
1885 (SELECT DISTINCT ccg.legal_entity
1886 FROM cst_cost_groups ccg,
1887 cst_cost_group_assignments ccga
1888 WHERE ccg.cost_group_id = ccga.cost_group_id
1889 AND ccg.cost_group_type = 2
1890 AND ccg.cost_group IN
1891 (SELECT cpici.cost_group
1892 FROM cst_pc_item_cost_interface cpici
1893 WHERE cpici.interface_header_id =
1894 RECINTERFACE.interface_header_id
1895 )
1896 )
1897 AND cct.cost_type IN
1898 (SELECT cpici.cost_type
1899 FROM cst_pc_item_cost_interface cpici
1900 WHERE cpici.interface_header_id =
1901 RECINTERFACE.interface_header_id
1902 );
1903
1904 IF l_primary_cost_method <> 4 THEN
1905 EXIT;
1906 END IF;
1907
1908 -- The interface table does not hold the cost type id
1909 -- The lifo processor requires the cost type id
1910
1911 SELECT cost_type_id
1912 INTO l_cost_type_id
1913 FROM cst_cost_types
1914 WHERE cost_type = RECINTERFACE.cost_type;
1918 CST_MGD_LIFO_COST_PROCESSOR.find_first_period(RECINTERFACE.pac_period_id
1915
1916 -- find the first period to calculate from
1917
1919 ,RECINTERFACE.inventory_item_id
1920 ,RECINTERFACE.cost_group_id
1921 ,l_cost_type_id
1922 );
1923
1924 -- record the delta quantity between periods
1925
1926 CST_MGD_LIFO_COST_PROCESSOR.populate_layers(RECINTERFACE.pac_period_id
1927 ,RECINTERFACE.inventory_item_id
1928 ,RECINTERFACE.cost_group_id
1929 ,l_cost_type_id
1930 ,l_user_id
1931 ,l_login_id
1932 ,l_req_id
1933 ,l_prg_id
1934 ,l_prg_appl_id
1935 );
1936
1937 -- call the cost processor to calc lifo
1938
1939 CST_MGD_LIFO_COST_PROCESSOR.calc_lifo_cost(RECINTERFACE.pac_period_id
1940 ,RECINTERFACE.inventory_item_id
1941 ,RECINTERFACE.cost_group_id
1942 ,l_cost_type_id
1943 ,l_user_id);
1944
1945
1946 END IF;
1947 END LOOP;
1948 CLOSE c_interface;
1949
1950 -- report success
1951
1952 x_err_msg := NULL;
1953 x_err_code := 0;
1954
1955 EXCEPTION
1956 WHEN OTHERS THEN
1957 ROLLBACK;
1958 x_err_num := SQLCODE;
1959 x_err_code := NULL;
1960 x_err_msg := SUBSTR('CST_MGD_LIFO_COST_PROCESSOR.loading_lifo_cost('
1961 || to_char(l_stmt_num)
1962 || '): '
1963 ||SQLERRM,1,240);
1964
1965
1966 END loading_lifo_cost;
1967
1968
1969 --========================================================================
1970 -- PROCEDURE : Log_Initialize PRIVATE
1971 -- COMMENT : Initializes the log facility. It should be called from
1972 -- the top level procedure of each concurrent program
1973 --=======================================================================--
1974 PROCEDURE Log_Initialize
1975 IS
1976 BEGIN
1977 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
1978 IF g_log_level IS NULL THEN
1979 g_log_mode := 'OFF';
1980 ELSE
1981 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
1982 g_log_mode := 'SRS';
1983 ELSE
1984 g_log_mode := 'SQL';
1985 END IF;
1986 END IF;
1987
1988 END Log_Initialize;
1989
1990
1991 --========================================================================
1992 -- PROCEDURE : Log PRIVATE
1993 -- PARAMETERS: p_level IN priority of the message - from
1994 -- highest to lowest:
1995 -- -- G_LOG_ERROR
1996 -- -- G_LOG_EXCEPTION
1997 -- -- G_LOG_EVENT
1998 -- -- G_LOG_PROCEDURE
1999 -- -- G_LOG_STATEMENT
2000 -- p_msg IN message to be print on the log
2001 -- file
2002 -- COMMENT : Add an entry to the log
2003 --========================================================================
2004 PROCEDURE Log
2005 ( p_priority IN NUMBER
2006 , p_msg IN VARCHAR2
2007 )
2008 IS
2009 BEGIN
2010 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
2011 THEN
2012 IF g_log_mode = 'SQL'
2013 THEN
2014 -- SQL*Plus session: uncomment the next line during unit test
2015 -- DBMS_OUTPUT.put_line(p_msg);
2016 NULL;
2017 ELSE
2018 -- Concurrent request
2019 FND_FILE.put_line
2020 ( FND_FILE.log
2021 , p_msg
2022 );
2023 END IF;
2024 END IF;
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027 NULL;
2028 END Log;
2029
2030 --=========================================================================
2031 -- PROCEDURE : selective_purge PRIVATE
2032 -- PARAMETERS : p_legal_entity_id legal entity
2033 -- : p_pac_period_id user specified layer period id
2034 -- : p_first_period_id first period id for an item
2035 -- : p_cost_group_id cost group id
2036 -- : p_cost_type_id cost type id
2037 -- : p_item_id Inventory item id
2038 -- COMMENT : This Procedure selectively purges the historical LIFO layers
2039 -- where the delta <=0 and market value does not exist for a
2040 -- given inventory item per cost group per cost type
2041 --=========================================================================
2042 PROCEDURE selective_purge
2046 , p_cost_group_id IN NUMBER
2043 ( p_legal_entity_id IN NUMBER
2044 , p_pac_period_id IN NUMBER
2045 , p_first_period_id IN NUMBER
2047 , p_cost_type_id IN NUMBER
2048 , p_item_id IN NUMBER
2049 )
2050 IS
2051
2052 --=================
2053 -- CURSORS
2054 --=================
2055
2056 -- cursor to get market value
2057 CURSOR get_market_value_cur(c_period_id NUMBER
2058 ,c_cost_group_id NUMBER
2059 ,c_item_id NUMBER)
2060 IS
2061 SELECT
2062 market_value
2063 FROM CST_PAC_ITEM_COSTS
2064 WHERE pac_period_id = c_period_id
2065 AND cost_group_id = c_cost_group_id
2066 AND inventory_item_id = c_item_id;
2067
2068 -- cursor to get begin layer quantity
2069 CURSOR get_begin_quantity_cur(c_period_id NUMBER
2070 ,c_cost_group_id NUMBER
2071 ,c_item_id NUMBER)
2072 IS
2073 SELECT
2074 begin_layer_quantity
2075 FROM CST_PAC_QUANTITY_LAYERS
2076 WHERE pac_period_id = c_period_id
2077 AND cost_group_id = c_cost_group_id
2078 AND inventory_item_id = c_item_id;
2079
2080
2081 -- cursor to obtain delta (layer quantity)
2082 CURSOR get_layer_quantity_cur(c_period_id NUMBER
2083 ,c_item_id NUMBER
2084 ,c_cost_group_id NUMBER)
2085 IS
2086 SELECT
2087 layer_quantity
2088 FROM CST_PAC_QUANTITY_LAYERS
2089 WHERE pac_period_id = c_period_id
2090 AND inventory_item_id = c_item_id
2091 AND cost_group_id = c_cost_group_id;
2092
2093 -- cursor to obtain the period end date
2094 CURSOR get_period_end_date_cur(c_period_id NUMBER)
2095 IS
2096 SELECT
2097 period_end_date
2098 FROM CST_PAC_PERIODS
2099 WHERE pac_period_id = c_period_id;
2100
2101 -- cursor to obtain the prior purge count
2102 CURSOR get_purge_prior_cnt_cur(c_cost_group_id NUMBER,
2103 c_item_id NUMBER,
2104 c_first_period_end_date DATE)
2105 IS
2106 SELECT
2107 COUNT(*)
2108 FROM CST_PAC_ITEM_COSTS
2109 WHERE cost_group_id = c_cost_group_id
2110 AND inventory_item_id = c_item_id
2111 AND pac_period_id IN (SELECT pac_period_id
2112 FROM CST_PAC_PERIODS
2113 WHERE period_end_date
2114 < c_first_period_end_date);
2115
2116
2117
2118 --=================
2119 -- LOCAL VARIABLES
2120 --=================
2121
2122 l_period_id NUMBER;
2123 l_market_value NUMBER;
2124 l_layer_quantity NUMBER;
2125 l_first_layer_quantity NUMBER;
2126 l_first_begin_quantity NUMBER;
2127 l_first_period_end_date DATE;
2128 l_errorcode NUMBER;
2129 l_errortext VARCHAR2(200);
2130
2131
2132 l_purge_prior_count NUMBER := 0;
2133 l_rec_purge_count NUMBER;
2134 l_total_purge_count NUMBER := 0;
2135
2136 -- store the list of item cost periods
2137 l_period_index BINARY_INTEGER;
2138
2139
2140 BEGIN
2141
2142 CST_MGD_LIFO_COST_PROCESSOR.Log
2143 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2144 ,'Start of Proc:Selective Purge'
2145 );
2146
2147 -- initialize the message stack
2148 FND_MSG_PUB.Initialize;
2149
2150 -- get market value for the first period
2151 OPEN get_market_value_cur(p_first_period_id
2152 ,p_cost_group_id
2153 ,p_item_id
2154 );
2155 FETCH get_market_value_cur INTO l_market_value;
2156
2157 CST_MGD_LIFO_COST_PROCESSOR.Log
2158 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2159 ,'Market Value: ' || to_char(l_market_value)
2160 );
2161
2162 CLOSE get_market_value_cur;
2163
2164 -- get begining quantity for the first period
2165 OPEN get_begin_quantity_cur(p_first_period_id
2166 ,p_cost_group_id
2167 ,p_item_id
2168 );
2169
2170 FETCH get_begin_quantity_cur INTO l_first_begin_quantity;
2171
2172 CST_MGD_LIFO_COST_PROCESSOR.Log
2173 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2174 ,'Begin quantity of the first period: ' ||
2175 to_char(l_first_begin_quantity)
2176 );
2177
2178 CLOSE get_begin_quantity_cur;
2179
2180 OPEN get_period_end_date_cur(p_first_period_id);
2181 FETCH get_period_end_date_cur
2182 INTO l_first_period_end_date;
2183
2184 CST_MGD_LIFO_COST_PROCESSOR.Log
2185 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2186 ,'First period end date: ' ||
2187 to_char(l_first_period_end_date)
2188
2189 );
2190
2191 CLOSE get_period_end_date_cur;
2192
2193
2194 -- delete prior item cost layers
2198 OPEN get_purge_prior_cnt_cur(p_cost_group_id,
2195 IF (l_market_value IS NOT NULL) OR (l_first_begin_quantity <= 0) THEN
2196
2197 -- get the historical prior purge count
2199 p_item_id,
2200 l_first_period_end_date);
2201
2202 FETCH get_purge_prior_cnt_cur
2203 INTO l_purge_prior_count;
2204
2205 CLOSE get_purge_prior_cnt_cur;
2206
2207 -- Delete all the prior item costs
2208 DELETE CST_PAC_ITEM_COSTS
2209 WHERE cost_group_id = p_cost_group_id
2210 AND inventory_item_id = p_item_id
2211 AND pac_period_id IN (SELECT pac_period_id
2212 FROM CST_PAC_PERIODS
2213 WHERE period_end_date
2214 < l_first_period_end_date);
2215
2216
2217 -- Delete all the prior item quantity layers
2218 DELETE CST_PAC_QUANTITY_LAYERS
2219 WHERE cost_group_id = p_cost_group_id
2220 AND inventory_item_id = p_item_id
2221 AND pac_period_id IN (SELECT pac_period_id
2222 FROM CST_PAC_PERIODS
2223 WHERE period_end_date
2224 < l_first_period_end_date);
2225
2226
2227 COMMIT; -- Deleted all the prior item cost layers with commit size
2228 CST_MGD_LIFO_COST_PROCESSOR.Log
2229 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2230 ,'Number of historical item cost layers purged: ' ||
2231 to_char(l_purge_prior_count)
2232 );
2233
2234
2235 -- update the total purge count
2236 l_total_purge_count := l_total_purge_count + l_purge_prior_count;
2237
2238 END IF;
2239
2240
2241 -- initialize the purge count
2242 l_rec_purge_count := 0;
2243
2244 -- initialize the cost layer range for commit
2245
2246 -- Get the period index of the p_pac_period_id
2247 -- first period index
2248 l_period_index :=
2249 CST_MGD_LIFO_COST_PROCESSOR.g_current_period_index;
2250
2251 -- get the first period id
2252 l_period_id :=
2253 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_period_index);
2254
2255 WHILE (l_period_index <>
2256 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.LAST) LOOP
2257
2258 IF (l_period_id = p_pac_period_id) THEN
2259
2260 CST_MGD_LIFO_COST_PROCESSOR.Log
2261 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2262 ,'Current Period Index: ' ||
2263 to_char(l_period_index) || ' ' ||
2264 'Period Id:' || to_char(l_period_id)
2265 );
2266 EXIT;
2267 END IF;
2268 -- get the next period index
2269 l_period_index :=
2270 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.NEXT(l_period_index);
2271
2272 l_period_id :=
2273 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_period_index);
2274
2275 END LOOP;
2276
2277 WHILE (l_period_id <> p_first_period_id) LOOP
2278 OPEN get_layer_quantity_cur(l_period_id
2279 ,p_item_id
2280 ,p_cost_group_id);
2281
2282 CST_MGD_LIFO_COST_PROCESSOR.Log
2283 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2284 ,'Period Id: ' ||
2285 to_char(l_period_id)
2286 );
2287
2288 FETCH get_layer_quantity_cur
2289 INTO l_layer_quantity;
2290
2291 CST_MGD_LIFO_COST_PROCESSOR.Log
2292 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2293 ,'Delta of the item cost layer: ' ||
2294 to_char(l_layer_quantity)
2295 );
2296
2297 CLOSE get_layer_quantity_cur;
2298
2299 -- delete the layer if the delta is negative or 0
2300 IF (l_layer_quantity <= 0) THEN
2301
2302 DELETE CST_PAC_ITEM_COSTS
2303 WHERE pac_period_id = l_period_id
2304 AND inventory_item_id = p_item_id
2305 AND cost_group_id = p_cost_group_id;
2306
2307 DELETE CST_PAC_QUANTITY_LAYERS
2308 WHERE pac_period_id = l_period_id
2309 AND inventory_item_id = p_item_id
2310 AND cost_group_id = p_cost_group_id;
2311
2312
2313 -- get the previous period layer
2314 l_period_index :=
2315 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.PRIOR(l_period_index);
2316
2317 l_period_id :=
2318 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_period_index);
2319
2320 -- add delta to the end quantity of the previous period item costs
2321 UPDATE CST_PAC_ITEM_COSTS
2322 SET total_layer_quantity = total_layer_quantity + l_layer_quantity
2323 WHERE pac_period_id = l_period_id
2324 AND inventory_item_id = p_item_id
2325 AND cost_group_id = p_cost_group_id;
2326
2327 -- add delta to the previous period quantity layer
2328 UPDATE CST_PAC_QUANTITY_LAYERS
2332 AND cost_group_id = p_cost_group_id;
2329 SET layer_quantity = layer_quantity + l_layer_quantity
2330 WHERE pac_period_id = l_period_id
2331 AND inventory_item_id = p_item_id
2333
2334 l_rec_purge_count := l_rec_purge_count + 1;
2335
2336 ELSE
2337
2338 -- get the previous period layer
2339 l_period_index :=
2340 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.PRIOR(l_period_index);
2341
2342 l_period_id :=
2343 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_period_index);
2344
2345 END IF;
2346
2347
2348 END LOOP;
2349
2350
2351 -- for the first period
2352 -- check whether delta is <= 0 and market value is null
2353 IF (l_period_id = p_first_period_id) THEN
2354 OPEN get_layer_quantity_cur(p_first_period_id
2355 ,p_item_id
2356 ,p_cost_group_id);
2357
2358 FETCH get_layer_quantity_cur
2359 INTO l_first_layer_quantity;
2360
2361 CLOSE get_layer_quantity_cur;
2362
2363 IF ((l_first_layer_quantity <= 0) AND ( l_market_value IS NULL)) THEN
2364 -- Delete the item cost and quantity layer
2365 DELETE CST_PAC_ITEM_COSTS
2366 WHERE pac_period_id = p_first_period_id
2367 AND inventory_item_id = p_item_id
2368 AND cost_group_id = p_cost_group_id;
2369
2370 DELETE CST_PAC_QUANTITY_LAYERS
2371 WHERE pac_period_id = p_first_period_id
2372 AND inventory_item_id = p_item_id
2373 AND cost_group_id = p_cost_group_id;
2374
2375 -- update the purge counters
2376 l_rec_purge_count := l_rec_purge_count + 1;
2377
2378 ELSE
2379 -- Update begin quantity to 0
2380 -- Update delta to the end quantity
2381 UPDATE CST_PAC_QUANTITY_LAYERS
2382 SET begin_layer_quantity = 0,
2383 layer_quantity = (SELECT total_layer_quantity
2384 FROM CST_PAC_ITEM_COSTS
2385 WHERE pac_period_id = p_first_period_id
2386 AND inventory_item_id = p_item_id
2387 AND cost_group_id = p_cost_group_id)
2388 WHERE pac_period_id = p_first_period_id
2389 AND inventory_item_id = p_item_id
2390 AND cost_group_id = p_cost_group_id;
2391 END IF;
2392
2393 END IF;
2394
2395 COMMIT; -- deleted all the item cost layers
2396 -- update total purge count
2397 l_total_purge_count := l_total_purge_count + l_rec_purge_count;
2398
2399 CST_MGD_LIFO_COST_PROCESSOR.Log
2400 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_EVENT
2401 ,'Inventory Item Id: ' || to_char(p_item_id)
2402 || ' Total item cost layers purged: ' ||
2403 to_char(l_total_purge_count)
2404 );
2405
2406
2407 CST_MGD_LIFO_COST_PROCESSOR.Log
2408 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2409 ,'End of Proc:Selective Purge'
2410 );
2411
2412 EXCEPTION
2413
2414 WHEN OTHERS THEN
2415 l_errorcode := SQLCODE;
2416 l_errortext := SUBSTR(SQLERRM,1,200);
2417 CST_MGD_LIFO_COST_PROCESSOR.Log
2418 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_ERROR
2419 ,to_char(l_errorcode) || l_errortext
2420 );
2421
2422 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2423 THEN
2424 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
2425 ,'selective_purge'
2426 );
2427 END IF;
2428
2429 END selective_purge;
2430
2431
2432 --=========================================================================
2433 -- PROCEDURE : lifo_purge PUBLIC
2434 -- PARAMETERS : x_retcode 0 success, 1 warning, 2 error
2435 -- : x_errbuff error buffer
2436 -- : p_legal_entity legal entity
2437 -- : p_cost_group_id cost group id
2438 -- : p_cost_type_id cost type id
2439 -- : p_pac_period_id user specified period id
2440 -- : p_category_set_name Item category set name
2441 -- : p_category_struct Category Structure used by
2442 -- category pair
2443 -- : p_category_from begining of item category
2444 -- range
2445 -- : p_category_to end of item category range
2446 -- : p_item_from beginning of item range
2447 -- : p_item_to end of item range
2448 -- COMMENT : This Procedure purges the historical LIFO layers as per the
2449 -- purge algorithm. This procedure will invoke the private
2450 -- procedures find_first_period and selective_purge
2454 ,x_retcode OUT NOCOPY VARCHAR2
2451 --=========================================================================
2452 PROCEDURE lifo_purge
2453 (x_errbuff OUT NOCOPY VARCHAR2
2455 ,p_legal_entity_id IN NUMBER
2456 ,p_cost_group_id IN NUMBER
2457 ,p_cost_type_id IN NUMBER
2458 ,p_pac_period_id IN NUMBER
2459 ,p_category_set_name IN VARCHAR2
2460 ,p_category_struct IN NUMBER
2461 ,p_category_from IN VARCHAR2
2462 ,p_category_to IN VARCHAR2
2463 ,p_item_from IN VARCHAR2
2464 ,p_item_to IN VARCHAR2
2465 )
2466 IS
2467
2468 --=================
2469 -- CURSORS
2470 --=================
2471
2472 -- cursor to obtain master organization for the cost group
2473 -- where cost group type is Organization (Organization cost group)
2474 CURSOR master_org_cur(c_cost_group_id NUMBER) IS
2475 SELECT
2476 ccg.organization_id,
2477 HOU.name
2478 FROM CST_COST_GROUPS ccg,
2479 HR_ORGANIZATION_UNITS HOU,
2480 HR_ORGANIZATION_INFORMATION HOI
2481 WHERE HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
2482 AND HOI.ORG_INFORMATION1 = 'INV'
2483 AND HOI.ORG_INFORMATION2 = 'Y'
2484 AND ( HOI.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
2485 AND ccg.cost_group_id = c_cost_group_id
2486 AND ccg.cost_group_type = 2
2487 AND ccg.organization_id = HOU.organization_id;
2488
2489 -- cursor to obtain list of item numbers for a given legal entity,
2490 -- cost period, cost group, cost type, item category, item range
2491 -- and master organization
2492 CURSOR item_number_cur(c_legal_entity_id NUMBER,
2493 c_pac_period_id NUMBER,
2494 c_cost_group_id NUMBER,
2495 c_cost_type_id NUMBER,
2496 c_item_number_from VARCHAR2,
2497 c_item_number_to VARCHAR2,
2498 c_category_struct NUMBER,
2499 c_category_from VARCHAR2,
2500 c_category_to VARCHAR2,
2501 c_master_org_id NUMBER) IS
2502 SELECT
2503 msi.concatenated_segments item_number,
2504 msi.inventory_item_id
2505 FROM
2506 CST_PAC_PERIODS pp
2507 ,CST_PAC_ITEM_COSTS pic
2508 ,MTL_SYSTEM_ITEMS_KFV msi
2509 ,MTL_ITEM_CATEGORIES mic
2510 ,MTL_CATEGORIES_KFV mc
2511 WHERE pp.legal_entity = c_legal_entity_id
2512 AND pic.cost_group_id = c_cost_group_id
2513 AND pp.cost_type_id = c_cost_type_id
2514 AND pp.pac_period_id = c_pac_period_id
2515 AND pic.pac_period_id = pp.pac_period_id
2516 AND pic.inventory_item_id = msi.inventory_item_id
2517 AND msi.inventory_item_id = mic.inventory_item_id
2518 AND msi.organization_id = mic.organization_id
2519 AND mic.category_id = mc.category_id
2520 AND mc.structure_id = c_category_struct
2521 AND mc.concatenated_segments
2522 BETWEEN nvl(c_category_from,mc.concatenated_segments)
2523 AND nvl(c_category_to,mc.concatenated_segments)
2524 AND msi.concatenated_segments
2525 BETWEEN nvl(c_item_number_from,msi.concatenated_segments)
2526 AND nvl(c_item_number_to,msi.concatenated_segments)
2527 AND msi.organization_id = c_master_org_id
2528 ORDER BY msi.concatenated_segments;
2529
2530
2531 --=================
2532 -- LOCAL VARIABLES
2533 --=================
2534
2535 l_first_period_id NUMBER;
2536 l_profile_org_id NUMBER;
2537 l_master_org_id NUMBER;
2538 l_master_org_name VARCHAR2(240);
2539 l_item_number VARCHAR2(240);
2540 l_inventory_item_id NUMBER;
2541
2542 -- store the list of item cost periods
2543 l_period_tab CST_MGD_LIFO_COST_PROCESSOR.period_tbl_type;
2544 l_first_period_index BINARY_INTEGER;
2545 l_period_index BINARY_INTEGER;
2546 l_next_period_index BINARY_INTEGER;
2547 l_period_id NUMBER;
2548 l_next_period_id NUMBER;
2549 l_total_quantity NUMBER;
2550
2551 BEGIN
2552
2553 -- initialize log
2554 CST_MGD_LIFO_COST_PROCESSOR.Log_Initialize;
2555
2556 -- initialize the message stack
2557 FND_MSG_PUB.Initialize;
2558
2559 CST_MGD_LIFO_COST_PROCESSOR.Log
2560 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2561 ,'Start of Proc:Lifo purge'
2562 );
2563
2564 -- Print the Parameter values
2565 CST_MGD_LIFO_COST_PROCESSOR.Log
2566 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2567 ,'----- PARAMETERS -----'
2568 );
2569
2570 CST_MGD_LIFO_COST_PROCESSOR.Log
2571 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2572 ,'Legal entity Id : ' || to_char(p_legal_entity_id)
2573 );
2574
2575 CST_MGD_LIFO_COST_PROCESSOR.Log
2576 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2577 ,'Cost group Id : ' || to_char(p_cost_group_id)
2578 );
2579
2580 CST_MGD_LIFO_COST_PROCESSOR.Log
2581 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2585 CST_MGD_LIFO_COST_PROCESSOR.Log
2582 ,'Cost type Id : ' || to_char(p_cost_type_id)
2583 );
2584
2586 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2587 ,'Pac Period Id : ' || to_char(p_pac_period_id)
2588 );
2589
2590 CST_MGD_LIFO_COST_PROCESSOR.Log
2591 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2592 ,'Category Set name : ' || p_category_set_name
2593 );
2594
2595 CST_MGD_LIFO_COST_PROCESSOR.Log
2596 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2597 ,'Category Structure : ' || to_char(p_category_struct)
2598 );
2599
2600 CST_MGD_LIFO_COST_PROCESSOR.Log
2601 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2602 ,'Category From : ' || p_category_from
2603 );
2604
2605 CST_MGD_LIFO_COST_PROCESSOR.Log
2606 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2607 ,'Category To : ' || p_category_to
2608 );
2609
2610 CST_MGD_LIFO_COST_PROCESSOR.Log
2611 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2612 ,'Item From : ' || p_item_from
2613 );
2614
2615 CST_MGD_LIFO_COST_PROCESSOR.Log
2616 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2617 ,'Item To : ' || p_item_to
2618 );
2619
2620
2621 -- get master organization id
2622 OPEN master_org_cur(p_cost_group_id);
2623
2624 FETCH master_org_cur
2625 INTO l_master_org_id,
2626 l_master_org_name;
2627
2628
2629 CST_MGD_LIFO_COST_PROCESSOR.Log
2630 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2631 ,'Master Organization Id: ' || to_char(l_master_org_id)
2632 || ' ' || l_master_org_name
2633 );
2634
2635 CLOSE master_org_cur;
2636
2637
2638 -- for each item find the first period and purge the historical LIFO layers
2639 FOR item_number_list in item_number_cur(p_legal_entity_id
2640 ,p_pac_period_id
2641 ,p_cost_group_id
2642 ,p_cost_type_id
2643 ,p_item_from
2644 ,p_item_to
2645 ,p_category_struct
2646 ,p_category_from
2647 ,p_category_to
2648 ,l_master_org_id)
2649
2650 LOOP
2651
2652 -- Item Information
2653 l_item_number := item_number_list.item_number;
2654 l_inventory_item_id := item_number_list.inventory_item_id;
2655
2656 CST_MGD_LIFO_COST_PROCESSOR.Log
2657 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2658 ,'Item Number: ' || l_item_number
2659 || ' ' || 'Item Id: ' || to_char(l_inventory_item_id)
2660 );
2661
2662
2663 -- Get the first period per item per cost group per cost type
2664 CST_MGD_LIFO_COST_PROCESSOR.find_first_period(p_pac_period_id
2665 ,l_inventory_item_id
2666 ,p_cost_group_id
2667 ,p_cost_type_id);
2668
2669 -- first period index
2670 l_first_period_index := CST_MGD_LIFO_COST_PROCESSOR.g_current_period_index;
2671
2672 -- first period id
2673 l_first_period_id :=
2674 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_first_period_index);
2675
2676 -- get the total quantity of the first period
2677 SELECT
2678 total_layer_quantity
2679 INTO
2680 l_total_quantity
2681 FROM
2682 cst_pac_item_costs
2683 WHERE pac_period_id = l_first_period_id
2684 AND inventory_item_id = l_inventory_item_id
2685 AND cost_group_id = p_cost_group_id;
2686
2687 -- Get the period index of the p_pac_period_id
2688
2689 l_period_index := l_first_period_index;
2690
2691 WHILE (l_period_index <=
2692 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.LAST) LOOP
2693
2694 l_period_id :=
2695 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_period_index);
2696
2697 IF (l_period_id = p_pac_period_id) THEN
2698
2699 CST_MGD_LIFO_COST_PROCESSOR.Log
2700 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2701 ,'Current Period Index: ' ||
2702 to_char(l_period_index) || ' ' ||
2703 'Period Id:' || to_char(l_period_id)
2704 );
2705 EXIT;
2706 END IF;
2707
2708 l_period_index :=
2709 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.NEXT(l_period_index);
2710
2711 END LOOP;
2712
2713 -- get the proper first period when total qty is <= 0
2714 IF (l_total_quantity <= 0
2715 AND l_first_period_index <> g_period_tab.LAST) THEN
2716
2717 l_next_period_index :=
2718 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab.NEXT(l_first_period_index);
2719 l_next_period_id :=
2720 CST_MGD_LIFO_COST_PROCESSOR.g_period_tab(l_next_period_index);
2721
2722 -- check the incremented index lies within the current period index
2723 IF l_next_period_index <= l_period_index THEN
2724 l_first_period_index := l_next_period_index;
2725 l_first_period_id := l_next_period_id;
2726 END IF;
2727
2728 END IF;
2729
2730 CST_MGD_LIFO_COST_PROCESSOR.Log
2731 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_EVENT
2732 ,'Proc:find first period completed'
2733 );
2734
2735 CST_MGD_LIFO_COST_PROCESSOR.Log
2736 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_STATEMENT
2737 ,'First Period Id: ' || to_char(l_first_period_id)
2738 );
2739
2740 -- selective purge of the item cost historical LIFO layers
2741 CST_MGD_LIFO_COST_PROCESSOR.selective_purge(p_legal_entity_id
2742 ,p_pac_period_id
2743 ,l_first_period_id
2744 ,p_cost_group_id
2745 ,p_cost_type_id
2746 ,l_inventory_item_id);
2747
2748
2749 END LOOP; -- for the list of items
2750
2751 CST_MGD_LIFO_COST_PROCESSOR.Log
2752 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2753 ,'End of Proc:Lifo purge'
2754 );
2755
2756
2757 CST_MGD_LIFO_COST_PROCESSOR.Log
2758 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2759 ,'Selective LIFO Purge Successful'
2760 );
2761
2762 x_errbuff := NULL;
2763 x_retcode := RETCODE_SUCCESS;
2764
2765 EXCEPTION
2766
2767 WHEN OTHERS THEN
2768 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2769 THEN
2770 FND_MSG_PUB.Add_Exc_Msg( G_CST_MGD_LIFO_COST_PROCESSOR
2771 ,'lifo_purge'
2772 );
2773 END IF;
2774
2775 CST_MGD_LIFO_COST_PROCESSOR.Log
2776 (CST_MGD_LIFO_COST_PROCESSOR.G_LOG_PROCEDURE
2777 ,'Selective LIFO Purge Failed'
2778 );
2779
2780 x_retcode := RETCODE_ERROR;
2781 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
2782
2783 END Lifo_purge;
2784
2785 END CST_MGD_LIFO_COST_PROCESSOR;