[Home] [Help]
PACKAGE BODY: APPS.CST_MGD_INFL_ADJUSTMENT_RPT
Source
1 PACKAGE BODY CST_MGD_INFL_ADJUSTMENT_RPT AS
2 /* $Header: CSTRIADB.pls 120.3 2006/02/15 05:10:07 vmutyala noship $ */
3
4 --===================
5 -- TYPES
6 --===================
7 TYPE Report_Rec_Type IS RECORD
8 ( organization_id NUMBER
9 , inventory_item_id NUMBER
10 , txn_date DATE
11 , txn_type VARCHAR2(30)
12 , txn_ini_qty NUMBER
13 , txn_ini_unit_cost NUMBER
14 , txn_ini_h_total_cost NUMBER
15 , txn_ini_adj_total_cost NUMBER
16 , txn_qty NUMBER
17 , txn_unit_cost NUMBER
18 , txn_h_total_cost NUMBER
19 , txn_adj_total_cost NUMBER
20 , txn_fnl_qty NUMBER
21 , txn_fnl_unit_cost NUMBER
22 , txn_fnl_h_total_cost NUMBER
23 , txn_fnl_adj_total_cost NUMBER
24 , creation_date DATE
25 , txn_id NUMBER
26 );
27
28 TYPE Report_Tbl_Rec_Type IS TABLE OF Report_Rec_Type
29 INDEX BY BINARY_INTEGER;
30
31 --================================
32 -- PRIVATE CONSTANTS AND VARIABLES
33 --================================
34 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
35
36 --===================
37 -- PRIVATE PROCEDURES
38 --===================
39
40 --=======================================================================
41 -- PROCEDURE : Get_valid_cost_group PRIVATE
42 -- PARAMETERS: p_org_id Organization ID
43 -- x_cost_group_id valid cost group id
44 -- COMMENT : Procedure to get the valid cost group
45 --========================================================================
46 PROCEDURE Get_valid_cost_group
47 ( p_org_id IN NUMBER
48 ,x_cost_group_id IN OUT NOCOPY CST_COST_GROUPS.cost_group_id%TYPE
49 )
50 IS
51
52 -- get the default cost group
53 CURSOR c_default_cost_group_cur IS
54 SELECT
55 default_cost_group_id
56 FROM MTL_PARAMETERS
57 WHERE organization_id = p_org_id;
58
59 BEGIN
60
61 -- Get the default cost group
62 OPEN c_default_cost_group_cur;
63
64 FETCH c_default_cost_group_cur
65 INTO x_cost_group_id;
66
67 CLOSE c_default_cost_group_cur;
68
69 END;
70
71 --=======================================================================
72 -- PROCEDURE : Get_Previous_Acct_Period PRIVATE
73 -- PARAMETERS: p_organization_id Organization ID
74 -- p_acct_period_id Accounting Period ID
75 -- x_prev_acct_period_id Previous Accounting Period ID
76 -- x_prev_sch_close_date Previous schedule close date
77 -- COMMENT : Procedure to get the previous accounting period
78 --========================================================================
79 PROCEDURE Get_Previous_Acct_Period
80 ( p_organization_id IN NUMBER
81 , p_period_start_date IN DATE
82 , x_prev_acct_period_id OUT NOCOPY NUMBER
83 , x_prev_sch_close_date OUT NOCOPY DATE
84 )
85 IS
86 -- Cursor to obtain previous accounting period Id
87 CURSOR previous_acct_period_cur( c_period_start_date DATE
88 , c_organization_id NUMBER
89 )
90 IS
91 SELECT
92 acct_period_id
93 , schedule_close_date
94 FROM
95 ORG_ACCT_PERIODS
96 WHERE trunc(period_start_date) < c_period_start_date
97 AND organization_id = c_organization_id
98 ORDER BY
99 period_start_date DESC;
100
101 BEGIN
102
103 OPEN previous_acct_period_cur( p_period_start_date
104 , p_organization_id
105 );
106
107 FETCH previous_acct_period_cur
108 INTO x_prev_acct_period_id
109 ,x_prev_sch_close_date;
110
111 CLOSE previous_acct_period_cur;
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 NULL;
116
117 END Get_Previous_Acct_period;
118
119 --========================================================================
120 -- PROCEDURE : Insert_Rpt_Data PRIVATE
121 -- PARAMETERS: p_rpt_item_rec Kardex report data for one row
122 -- COMMENT :
123 -- EXCEPTIONS: OTHERS
124 -- HISTORY: NVL syntax added by vjavli
125 -- part of bug#1474753 fix
126 --========================================================================
127 PROCEDURE Insert_Rpt_Data
128 ( p_rpt_item_rec IN Report_Rec_Type
129 )
130 IS
131 BEGIN
132 INSERT INTO
133 CST_MGD_INFL_ADJ_KARDEX_DATA(
134 ORGANIZATION_ID
135 , INVENTORY_ITEM_ID
136 , TXN_DATE
137 , TXN_TYPE
138 , TXN_INI_QTY
139 , TXN_INI_UNIT_COST
140 , TXN_INI_H_TOTAL_COST
141 , TXN_INI_ADJ_TOTAL_COST
142 , TXN_QTY
143 , TXN_UNIT_COST
144 , TXN_H_TOTAL_COST
145 , TXN_ADJ_TOTAL_COST
146 , TXN_FNL_QTY
147 , TXN_FNL_UNIT_COST
148 , TXN_FNL_H_TOTAL_COST
149 , TXN_FNL_ADJ_TOTAL_COST
150 , CREATION_DATE
151 , TRANSACTION_ID
152 )
153 VALUES(
154 p_rpt_item_rec.organization_id
155 , p_rpt_item_rec.inventory_item_id
156 , p_rpt_item_rec.txn_date
157 , p_rpt_item_rec.txn_type
158 , NVL(p_rpt_item_rec.txn_ini_qty,0)
159 , NVL(p_rpt_item_rec.txn_ini_unit_cost,0)
160 , NVL(p_rpt_item_rec.txn_ini_h_total_cost,0)
161 , NVL(p_rpt_item_rec.txn_ini_adj_total_cost,0)
162 , p_rpt_item_rec.txn_qty
163 , p_rpt_item_rec.txn_unit_cost
164 , p_rpt_item_rec.txn_h_total_cost
165 , p_rpt_item_rec.txn_adj_total_cost
166 , NVL(p_rpt_item_rec.txn_fnl_qty,0)
167 , NVL(p_rpt_item_rec.txn_fnl_unit_cost,0)
168 , NVL(p_rpt_item_rec.txn_fnl_h_total_cost,0)
169 , NVL(p_rpt_item_rec.txn_fnl_adj_total_cost,0)
170 , p_rpt_item_rec.creation_date
171 , p_rpt_item_rec.txn_id
172 );
173
174 EXCEPTION
175
176 WHEN OTHERS THEN
177 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
178 THEN
179 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
180 , 'Insert_Rpt_Data'
181 );
182 END IF;
183 RAISE;
184
185 END Insert_Rpt_Data;
186
187
188 --========================================================================
189 -- PROCEDURE : Get_Acct_Period_ID PRIVATE
190 -- PARAMETERS: p_org_id Organization ID
191 -- p_rpt_from_date Report start date
192 -- P_rpt_to_date Report end date
193 -- x_rpt_from_acct_per_id Report start account period ID
194 -- x_rpt_to_acct_per_id Report end account period ID
195 -- COMMENT : Get the account period IDs for user defined reporting
196 -- period
197 -- EXCEPTIONS:
198 --========================================================================
199 PROCEDURE Get_Acct_Period_ID
200 ( p_org_id IN NUMBER
201 , p_rpt_from_date IN DATE
202 , P_rpt_to_date IN DATE
203 , x_rpt_from_acct_per_id OUT NOCOPY NUMBER
204 , x_rpt_to_acct_per_id OUT NOCOPY NUMBER
205 )
206 IS
207 l_rpt_from_acct_per_id NUMBER;
208 l_rpt_to_acct_per_id NUMBER;
209 BEGIN
210 -- get account period id for report from date
211 SELECT
212 acct_period_id
213 INTO
214 x_rpt_from_acct_per_id
215 FROM
216 ORG_ACCT_PERIODS oap
217 WHERE oap.organization_id = p_org_id
218 AND oap.period_start_date <= p_rpt_from_date
219 AND oap.schedule_close_date >= p_rpt_from_date
220 AND oap.open_flag = 'N'
221 AND oap.period_close_date IS NOT NULL;
222
223 -- get account period id for report to date
224 SELECT
225 acct_period_id
226 INTO
227 x_rpt_to_acct_per_id
228 FROM
229 ORG_ACCT_PERIODS oap
230 WHERE oap.organization_id = p_org_id
231 AND oap.period_start_date <= p_rpt_to_date
232 AND oap.schedule_close_date >= p_rpt_to_date
233 AND oap.open_flag = 'N'
234 AND oap.period_close_date IS NOT NULL;
235
236
237 EXCEPTION
238
239 WHEN NO_DATA_FOUND THEN
240 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ND_ACCT_PER_ID');
241 FND_MSG_PUB.Add;
242 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
243 THEN
244 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
245 , 'Get_Acct_Period_ID'
246 );
247 END IF;
248 RAISE ;
249
250
251 WHEN OTHERS THEN
252 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
255 , 'Get_Acct_Period_ID'
256 );
257 END IF;
258 RAISE;
259
260 END Get_Acct_Period_ID;
261
262
263 --========================================================================
264 -- PROCEDURE : Get_Unit_Infl_Adj_Cost PRIVATE
265 -- PARAMETERS: p_org_id Organization ID
266 -- p_acct_period_id Account period ID
267 -- p_item_id Inventory item ID
268 -- x_unit_infl_adj Inventory item period end unit
269 -- inflation adjusted cost
270 -- : x_init_qty Period begin quantity
271 -- COMMENT : Retrieve item unit inflation adjusted cost and begin
272 -- quantity
273 -- EXCEPTIONS:
274 --========================================================================
275 PROCEDURE Get_Unit_Infl_Adj_Cost
276 ( p_org_id IN NUMBER
277 , p_acct_period_id IN NUMBER
278 , p_item_id IN NUMBER
279 , x_unit_infl_adj OUT NOCOPY NUMBER
280 , x_init_qty OUT NOCOPY NUMBER
281 )
282 IS
283 l_final_infl_adj NUMBER;
284 l_final_qty NUMBER;
285 BEGIN
286
287 SELECT
288 Begin_Qty
289 , NVL((Actual_Inflation_Adj - Issue_Inflation_Adj), 0)
290 , NVL((Actual_Qty - Issue_Qty), 0)
291 INTO
292 x_init_qty
293 , l_final_infl_adj
294 , l_final_qty
295 FROM
296 CST_MGD_INFL_ADJUSTED_COSTS
297 WHERE Organization_ID = p_org_id
298 AND Acct_Period_ID = p_acct_period_id
299 AND Inventory_Item_ID = p_item_id;
300
301 IF l_final_qty = 0
302 THEN
303 x_unit_infl_adj := 0;
304 ELSE
305 x_unit_infl_adj := l_final_infl_adj/l_final_qty;
306 END IF;
307
308 EXCEPTION
309
310 WHEN OTHERS THEN
311 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
312 THEN
313 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
314 , 'Get_Unit_Infl_Adj_Cost'
315 );
316 END IF;
317 RAISE;
318
319 END Get_Unit_Infl_Adj_Cost;
320
321
322 --========================================================================
323 -- PROCEDURE : Get_Txn_Type PRIVATE
324 -- PARAMETERS: p_txn_type_id Transaction type ID
325 -- x_txn_type_name Transaction type name
326 -- COMMENT : Retrieve transaction type name from ID
327 -- EXCEPTIONS:
328 --========================================================================
329 PROCEDURE Get_Txn_Type
330 ( p_txn_type_id IN NUMBER
331 , x_txn_type_name OUT NOCOPY VARCHAR2
332 )
333 IS
334 BEGIN
335
336 SELECT
337 Transaction_Type_Name
338 INTO
339 x_txn_type_name
340 FROM
341 MTL_TRANSACTION_TYPES
342 WHERE Transaction_Type_ID = p_txn_type_id
343 AND NVL(Disable_Date, SYSDATE + 1) > SYSDATE;
344
345 EXCEPTION
346
347 WHEN OTHERS THEN
348 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
349 THEN
350 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
351 , 'Get_Txn_Type'
352 );
353 END IF;
354 RAISE;
355
356 END Get_Txn_Type;
357
358
359 --========================================================================
360 -- PROCEDURE : Get_Offset_Qty PRIVATE
361 -- PARAMETERS: p_org_id Organization ID
362 -- p_item_id Inventory item ID
363 -- p_from_acct_per_id Report start account period ID
364 -- p_rpt_from_date Report start date
365 -- p_cost_group_id Valid Cost Group ID
366 -- x_off_set_qty Amount needed to off set begin
367 -- quantity
368 -- COMMENT : If user wants to create report from a date that does not
369 -- coincide with the account period start date. The initial
370 -- quantity needs to be off set by this amount.
371 -- Exclude Sub inventory transactions transaction action id 2
372 -- Bug#2912818 fix: Exclude consigned transaction
373 -- nvl(owning_tp_type,2) <> 1 added
374 -- EXCEPTIONS:
375 --========================================================================
376 PROCEDURE Get_Offset_Qty
377 ( p_org_id IN NUMBER
378 , p_item_id IN NUMBER
379 , p_from_acct_per_id IN NUMBER
380 , p_rpt_from_date IN DATE
381 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
382 , x_offset_qty OUT NOCOPY NUMBER
383 )
384 IS
385 BEGIN
386
387 SELECT
388 NVL(SUM(Primary_Quantity), 0)
389 INTO
390 x_offset_qty
391 FROM
392 MTL_MATERIAL_TRANSACTIONS
393 WHERE Organization_ID = p_org_id
394 AND Inventory_Item_ID = p_item_id
395 AND Acct_Period_ID = p_from_acct_per_id
396 AND transaction_action_id <> 2
397 AND Transaction_Date < p_rpt_from_date
398 AND Cost_Group_ID = p_cost_group_id
399 AND nvl(owning_tp_type,2) <> 1;
400
401 EXCEPTION
402
403 WHEN NO_DATA_FOUND THEN
404 x_offset_qty := 0;
405
406 WHEN OTHERS THEN
407 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
408 THEN
409 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
410 , 'Get_Offset_Qty'
411 );
412 END IF;
413 RAISE;
414
415 END Get_Offset_Qty;
416
417
418 --========================================================================
419 -- PROCEDURE : Get_Item_Txn_Info PRIVATE
420 -- PARAMETERS: p_org_id Organization ID
421 -- p_item_id Inventory item ID
422 -- p_acct_period_id Mfg accounting period ID
423 -- p_per_first_txn_date First transaction date for
424 -- reporting.
425 -- p_per_last_txn_date Last transaction date for
426 -- reporting.
427 --
428 -- p_item_prev_cost previous period item cost
429 -- removed: p_item_unit_cost Inventory item unit average cost
430 --
431 -- p_item_init_qty Inventory item period begin
432 -- quantity
433 -- p_item_unit_infl_adj Inventory item period end unit
434 -- inflation adjusted cost
435 -- p_offset_qty Offset initial quantity for first
436 -- reporting period.
437 -- p_cost_group_id valid cost group id
438 -- x_rpt_item_tbl_rec Report data record
439 -- COMMENT : Builds data for one row
440 -- exclude sub inventory transactions transaction action id 2
441 -- Bug#2912818 fix: Exclude consigned inventory transaction
442 -- nvl(owning_tp_type,2) <> 1 added
443 -- EXCEPTION : l_txn_cost_exc Missing transaction costs.
444 --========================================================================
445 PROCEDURE Get_Item_Txn_Info
446 ( p_org_id IN NUMBER
447 , p_item_id IN NUMBER
448 , p_acct_period_id IN NUMBER
449 , p_per_first_txn_date IN DATE
450 , p_per_last_txn_date IN DATE
451 , p_item_prev_cost IN NUMBER
452 , p_item_init_qty IN NUMBER
453 , p_item_init_infl IN NUMBER
454 , p_item_unit_infl_adj IN NUMBER
455 , p_offset_qty IN NUMBER
456 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
457 , x_rpt_item_tbl_rec OUT NOCOPY Report_Tbl_Rec_Type
458 )
459 IS
460 l_routine CONSTANT VARCHAR2(30) := 'get_item_txn_info';
461
462 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
463 l_txn_init_qty NUMBER;
464 l_txn_init_infl NUMBER;
465 l_prev_acct_period_id NUMBER;
466 l_prev_sch_close_date DATE;
467 l_index BINARY_INTEGER := 1;
468 l_begin_unit_cost NUMBER;
469 l_txn_cost_exc EXCEPTION;
470 CURSOR l_item_txn_csr IS
471 SELECT
472 Transaction_ID
473 , Transaction_Type_ID
474 , Transaction_Date
475 , Primary_Quantity
476 , Actual_Cost
477 , Prior_Cost
478 , New_Cost
479 , Transfer_Organization_ID
480 , transaction_source_type_id
481 , transaction_action_id
482 , creation_date
483 FROM
484 MTL_MATERIAL_TRANSACTIONS
485 WHERE Organization_ID = p_org_id
486 AND Inventory_Item_ID = p_item_id
487 AND Acct_Period_ID = p_acct_period_id
488 AND transaction_action_id <> 2
489 AND Transaction_Date BETWEEN p_per_first_txn_date
490 AND p_per_last_txn_date
491 AND Primary_Quantity <> 0
492 AND Cost_Group_ID = p_cost_group_id
493 AND nvl(owning_tp_type,2) <> 1
494 ORDER BY
495 TRUNC(Transaction_Date)
496 , creation_date
497 , transaction_id;
498
499 -- local debug variables to use within loop
500 l_debug_level NUMBER;
501 l_state_level NUMBER;
502
503 BEGIN
504
505 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
507 , G_MODULE_HEAD || l_routine || '.begin'
508 , l_routine || '<'
509 );
510 END IF;
511
512 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
513 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
514 , G_MODULE_HEAD || l_routine || '.acctpd'
515 , 'Account Period Id:' || p_acct_period_id
516 );
517 END IF;
518
519 l_txn_init_qty := p_item_init_qty - p_offset_qty;
520 l_txn_init_infl := p_item_init_infl;
521
522 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
523 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
524 , G_MODULE_HEAD || l_routine || '.txniniinfl'
525 , 'Txn initial inflation:' || l_txn_init_infl ||
526 ' ' || 'Txn initial inflation cost:' || p_item_unit_infl_adj
527 );
528 END IF;
529
530 -- Assign local debug variables to use within loop
531 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
532 l_state_level := FND_LOG.LEVEL_STATEMENT;
533
534 FOR l_item_txn_info IN l_item_txn_csr
535 LOOP
536
537 IF (l_item_txn_info.Actual_Cost IS NULL)
538 THEN
539 RAISE l_txn_cost_exc;
540 END IF;
541
542 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
543 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
544 l_rpt_item_tbl_rec(l_index).txn_date :=
545 l_item_txn_info.Transaction_Date;
546
547 --Bug 4086030 transaction id and creation date are added
548 l_rpt_item_tbl_rec(l_index).txn_id := l_item_txn_info.transaction_id;
549 l_rpt_item_tbl_rec(l_index).creation_date := l_item_txn_info.creation_date;
550 Get_Txn_Type
551 ( p_txn_type_id => l_item_txn_info.Transaction_Type_ID
552 , x_txn_type_name => l_rpt_item_tbl_rec(l_index).txn_type
553 );
554
555 -- ==================================================================
556 -- Beginning Inventory Calculation
557 -- ==================================================================
558 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
559 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost :=
560 l_item_txn_info.Prior_Cost;
561 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
562 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
563 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
564 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := l_txn_init_infl;
565
566 -- ======================================================================
567 -- Transaction: Calculation
568 -- ======================================================================
569 l_rpt_item_tbl_rec(l_index).txn_qty := l_item_txn_info.Primary_Quantity;
570
571 -- Bug#3850750 fix: Average Cost Update Transactions
572 IF l_item_txn_info.transaction_source_type_id = 13 AND
573 l_item_txn_info.transaction_action_id = 24 THEN
574 -- Transaction Unit Cost should be delta equal to new cost - prior cost
575 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
576 l_item_txn_info.New_Cost - l_item_txn_info.Prior_Cost;
577 ELSE
578 -- All other transactions
579 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
580 l_item_txn_info.Actual_Cost;
581 END IF;
582
583 l_rpt_item_tbl_rec(l_index).txn_h_total_cost :=
584 l_rpt_item_tbl_rec(l_index).txn_qty *
585 l_rpt_item_tbl_rec(l_index).txn_unit_cost;
586
587 -- ======================================================================
588 -- Transaction Total Inflation Cost
589 -- bug#2862030 fix: calculation logic change for transaction
590 -- adjustment total cost
591 -- bug#2919777 fix: calculatin logic applies only to issues
592 -- For receipts, the old logic holds good since inflation happens
593 -- only for issues and NOT for receipts
594 --
595 -- Bug#3878129 fix: No inflation calculation for PO Receipt correction
596 -- Bug#3927188 fix: No inflation calculation for SO Pick Release txns
597 -- if the transaction is an internal transfer
598 -- If the transaction is outbound of LE, then inflation will be calculated
599 -- ======================================================================
600 IF l_rpt_item_tbl_rec(l_index).txn_qty < 0 THEN
601 -- Process issues
602
603 -- =========================================
604 -- Check for PO Receipt delivery adjustments
605 -- Bug3878129 fix: PO Receipt correction
606 -- =========================================
607 IF l_item_txn_info.transaction_source_type_id = 1 AND
608 l_item_txn_info.transaction_action_id = 29 THEN
609 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost := 0;
610
611 ELSIF l_item_txn_info.transaction_source_type_id = 2 AND
612 l_item_txn_info.transaction_action_id = 28 THEN
613 -- ===============================================================
614 -- Check for Sales Order Staging transfer
615 -- Bug#3927188 fix: SO Pick Release txn with an internal transfer
616 -- ===============================================================
617 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost := 0;
618
619 ELSIF l_item_txn_info.transaction_source_type_id = 13 AND
620 l_item_txn_info.transaction_action_id = 5 THEN
621 -- ===============================================================
622 -- Check for VMI Planning Transfer Issues
623 -- Bug#3862228 fix: VMI Planning Transfers - inflation adj is 0
624 -- ===============================================================
625 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost := 0;
626 ELSE
627 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost :=
628 l_item_txn_info.Primary_Quantity * p_item_unit_infl_adj;
629 END IF;
630
631 ELSE
632 -- receipts
633 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost := 0;
634 END IF;
635
636 -- ====================================================================
637 -- Final Inventory Calculation
638 -- Bug#2862030 fix: final adjustment cost is initial adjustment cost
639 -- PLUS transaction adjustment cost
640 -- Old logic: txn_fnl_qty * p_item_unit_infl_adj
641 -- ====================================================================
642 IF l_item_txn_info.transaction_source_type_id = 13 AND
643 l_item_txn_info.transaction_action_id = 24 THEN
644 -- Bug#3850750 fix: Transaction Qty of Average Cost Update txns
645 -- Do not add transaction qty of average cost update transactions
646 -- since the cost is updated on the inventory balance quantity
647 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
648 l_rpt_item_tbl_rec(l_index).txn_ini_qty;
649 ELSIF l_item_txn_info.transaction_source_type_id = 13 AND
650 l_item_txn_info.transaction_action_id = 5 THEN
651 -- Bug#3862228 fix: VMI Planning transfers to be excluded
652 -- Do not add transaction qty of VMI planning transfers
653 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
654 l_rpt_item_tbl_rec(l_index).txn_ini_qty;
655 ELSE
656 -- All other transactions
657 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
658 l_rpt_item_tbl_rec(l_index).txn_ini_qty +
659 l_item_txn_info.Primary_Quantity;
660 END IF;
661
662 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost :=
663 l_item_txn_info.New_Cost;
664 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
665 l_rpt_item_tbl_rec(l_index).txn_fnl_qty *
666 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost;
667
668 IF ((l_item_txn_info.Primary_Quantity > 0)
669 AND
670 (l_item_txn_info.Transfer_Organization_ID IS NULL))
671 OR
672 (l_item_txn_info.Transfer_Organization_ID = p_org_id)
673 THEN
674 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
675 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
676 ELSE
677 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
678 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost +
679 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost;
680 END IF;
681
682 l_txn_init_qty := l_rpt_item_tbl_rec(l_index).txn_fnl_qty;
683 l_txn_init_infl := l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost;
684
685 IF (l_state_level >= l_debug_level) THEN
686 FND_LOG.string(l_state_level
687 , G_MODULE_HEAD || l_routine || '.itemtxninfo'
688 , '*** Item Txn Info *** '
689 );
690
691 FND_LOG.string(l_state_level
692 , G_MODULE_HEAD || l_routine || '.itemtxndate'
693 , 'Item Id:' || l_rpt_item_tbl_rec(l_index).inventory_item_id
694 || ' Transaction Date:' || l_rpt_item_tbl_rec(l_index).txn_date
695 );
696
697 FND_LOG.string(l_state_level
698 , G_MODULE_HEAD || l_routine || '.inicostdtls1'
699 , 'Initial cost details:' || 'initial qty:'
700 || l_rpt_item_tbl_rec(l_index).txn_ini_qty || ' '
701 || 'unit cost:'
702 || l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost
703 );
704
705 FND_LOG.string(l_state_level
706 , G_MODULE_HEAD || l_routine || '.inicostdtls2'
707 , 'historical total cost:' || l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost || ' ' || 'adj total cost:' || l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost
708 );
709
710 FND_LOG.string(l_state_level
711 , G_MODULE_HEAD || l_routine || '.txncostdtls1'
712 , 'Transaction cost details:' || 'trn qty:' || l_rpt_item_tbl_rec(l_index).txn_qty || ' ' || 'unit cost:' || l_rpt_item_tbl_rec(l_index).txn_unit_cost
713 );
714
715 FND_LOG.string(l_state_level
716 , G_MODULE_HEAD || l_routine || '.txncostdtls2'
717 , 'historical total cost:' || l_rpt_item_tbl_rec(l_index).txn_h_total_cost || ' ' || 'adj total cost:' || l_rpt_item_tbl_rec(l_index).txn_adj_total_cost
718 );
719
720 FND_LOG.string(l_state_level
721 , G_MODULE_HEAD || l_routine || '.fnlcostdtls1'
722 , 'Final cost details:' || 'final qty:' || l_rpt_item_tbl_rec(l_index).txn_fnl_qty || ' ' || 'unit cost:' || l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost
723 );
724
725 FND_LOG.string(l_state_level
726 , G_MODULE_HEAD || l_routine || '.fnlcostdtls2'
727 , 'historical total cost:' || l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost || ' ' || 'adj total cost:' || l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost
728 );
729
730 END IF;
731
732 l_index := l_index + 1;
733 END LOOP;
734
735 IF NVL(l_rpt_item_tbl_rec.FIRST, 0) = 0
736 THEN
737
738 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
739 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
740 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
741 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost := p_item_prev_cost;
742 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
743 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
744 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
745 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := p_item_init_infl;
746 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
747 l_rpt_item_tbl_rec(l_index).txn_ini_qty;
748 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost := p_item_prev_cost;
749 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
750 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost;
751 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
752 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
753 END IF;
754
755 x_rpt_item_tbl_rec := l_rpt_item_tbl_rec;
756
757 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
758 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
759 ,G_MODULE_HEAD || l_routine || '.end'
760 ,l_routine || '>'
761 );
762 END IF;
763
764 EXCEPTION
765
766 WHEN l_txn_cost_exc THEN
767 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_UNIT_COST_NULL');
768 FND_MSG_PUB.Add;
769 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
770 THEN
771 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
772 , 'Get_Item_Txn_Info'
773 );
774 END IF;
775 RAISE;
776
777 WHEN OTHERS THEN
778 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
779 THEN
780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
781 , 'Get_Item_Txn_Info'
782 );
783 END IF;
784 RAISE;
785
786 END Get_Item_Txn_Info;
787
788
789 --========================================================================
790 -- PROCEDURE : Create_Infl_Adj_Rpt PRIVATE
791 -- PARAMETERS: p_org_id Organization ID
792 -- p_item_from_code Report start item code
793 -- p_item_to_code Report end item code
794 -- p_rpt_from_date Report start date
795 -- p_rpt_to_date Report end date
796 -- COMMENT : Main procedure called by Kardex report
797 --========================================================================
798 PROCEDURE Create_Infl_Adj_Rpt
799 ( p_org_id IN NUMBER
800 , p_item_from_code IN VARCHAR2 := NULL
801 , p_item_to_code IN VARCHAR2 := NULL
802 , p_rpt_from_date IN VARCHAR2
803 , p_rpt_to_date IN VARCHAR2
804 )
805 IS
806 l_routine CONSTANT VARCHAR2(30) := 'create_infl_adj_rpt';
807
808 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
809 l_item_id NUMBER;
810 l_item_unit_cost NUMBER;
811 l_begin_unit_cost NUMBER;
812 l_acct_period_id NUMBER;
813 l_rpt_from_acct_per_id NUMBER;
814 l_rpt_to_acct_per_id NUMBER;
815 l_final_infl_adj NUMBER;
816 l_final_qty NUMBER;
817 l_purchase_qty NUMBER;
818 l_unit_infl_adj NUMBER;
819 l_per_begin_qty NUMBER;
820 l_begin_infl_adj NUMBER;
821 l_offset_qty NUMBER;
822 l_per_first_txn_date DATE;
823 l_per_last_txn_date DATE;
824 l_period_start_date DATE;
825 l_period_schedule_close_date DATE;
826 l_index BINARY_INTEGER;
827 l_cost_group_id CST_COST_GROUPS.cost_group_id%TYPE;
828
829 -- Previous Period variables
830 l_prev_acct_period_id NUMBER;
831 l_prev_sch_close_date DATE;
832 l_previous_qty NUMBER;
833 l_previous_cost NUMBER;
834 l_previous_inflation_adj NUMBER;
835 l_previous_unit_cost NUMBER;
836
837 -- Date range variables for mid night settings
838 l_rpt_date_from DATE;
839 l_rpt_date_to DATE;
840
841 -- Date range variables for accounting period comparison
842 l_date_from DATE;
843 l_date_to DATE;
844
845 CURSOR l_item_range_csr( c_org_id NUMBER
846 , c_item_from_code VARCHAR2
847 , c_item_to_code VARCHAR2
848 , c_rpt_from_acct_per_id NUMBER
849 , c_rpt_to_acct_per_id NUMBER
850 )
851 IS
852 SELECT
853 INFL.Inventory_Item_ID
854 , INFL.Acct_Period_ID
855 , NVL(INFL.Begin_Qty,0)
856 , NVL(INFL.Begin_Inflation_Adj,0)
857 , (NVL(INFL.Actual_Inflation_Adj,0) - ABS(NVL(INFL.Issue_Inflation_Adj,0)))
858 , (NVL(INFL.Actual_Qty,0) - ABS(NVL(INFL.Issue_Qty,0)))
859 , PER.Schedule_Close_Date
860 , PER.Period_Start_Date
861 FROM
862 CST_MGD_INFL_ADJUSTED_COSTS INFL
863 , MTL_SYSTEM_ITEMS_B_KFV MSI
864 , ORG_ACCT_PERIODS PER
865 WHERE INFL.Organization_ID = c_org_id
866 AND MSI.Organization_ID = c_org_id
867 AND PER.Organization_ID = c_org_id
868 AND INFL.Inventory_Item_ID = MSI.Inventory_Item_ID
869 AND MSI.Concatenated_Segments BETWEEN c_item_from_code
870 AND c_item_to_code
871 AND INFL.Acct_Period_ID BETWEEN c_rpt_from_acct_per_id
872 AND c_rpt_to_acct_per_id
873 AND INFL.Acct_Period_ID = PER.Acct_Period_ID
874 AND PER.Open_Flag = 'N'
875 AND PER.PERIOD_CLOSE_DATE IS NOT NULL
876 ORDER BY INFL.Acct_Period_ID;
877
878
879 CURSOR l_item_all_csr( c_org_id NUMBER
880 , c_rpt_from_acct_per_id NUMBER
881 , c_rpt_to_acct_per_id NUMBER
882 )
883 IS
884 SELECT
885 INFL.Inventory_Item_ID
886 , INFL.Acct_Period_ID
887 , NVL(INFL.Begin_Qty,0)
888 , NVL(INFL.Begin_Inflation_Adj,0)
889 , (NVL(INFL.Actual_Inflation_Adj,0) - ABS(NVL(INFL.Issue_Inflation_Adj,0)))
890 , (NVL(INFL.Actual_Qty,0) - ABS(NVL(INFL.Issue_Qty,0)))
891 , PER.Schedule_Close_Date
892 , PER.Period_Start_Date
893 FROM
894 CST_MGD_INFL_ADJUSTED_COSTS INFL
895 , MTL_SYSTEM_ITEMS_B MSI
896 , ORG_ACCT_PERIODS PER
897 WHERE INFL.Organization_ID = c_org_id
898 AND MSI.Organization_ID = c_org_id
899 AND PER.Organization_ID = c_org_id
900 AND INFL.Inventory_Item_ID = MSI.Inventory_Item_ID
901 AND INFL.Acct_Period_ID BETWEEN c_rpt_from_acct_per_id
902 AND c_rpt_to_acct_per_id
903 AND INFL.Acct_Period_ID = PER.Acct_Period_ID
904 AND PER.Open_Flag = 'N'
905 AND PER.PERIOD_CLOSE_DATE IS NOT NULL
906 ORDER BY INFL.Acct_Period_ID;
907 BEGIN
908
909 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
911 ,G_MODULE_HEAD || l_routine || '.begin'
912 , l_routine || '<'
913 );
914 END IF;
915
916 DELETE FROM CST_MGD_INFL_ADJ_KARDEX_DATA;
917
918 -- From Date is set at midnight for the day
919 l_rpt_date_from := TRUNC(FND_DATE.canonical_to_date(p_rpt_from_date));
920 -- To date set to mid night 23:59:59
921 l_rpt_date_to := TRUNC(FND_DATE.canonical_to_date(p_rpt_to_date)) + (86399/86400);
922
923 -- Date range for accounting periods
924 -- used to get the accounting periods
925 l_date_from := FND_DATE.canonical_to_date(p_rpt_from_date);
926 l_date_to := FND_DATE.canonical_to_date(p_rpt_to_date);
927
928 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
930 ,G_MODULE_HEAD || l_routine || '.rptdatefrom'
931 ,'Canonical Date From:' || l_rpt_date_from
932 );
933
934 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
935 ,G_MODULE_HEAD || l_routine || '.rptdateto'
936 ,'Canonical Date To:' || l_rpt_date_to
937 );
938
939 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
940 ,G_MODULE_HEAD || l_routine || '.acctpdfrom'
941 ,'Accounting Period Date From:' || l_date_from
942 );
943
944 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
945 ,G_MODULE_HEAD || l_routine || '.acctpdto'
946 ,'Accounting Period Date To:' || l_date_to
947 );
948
949 END IF;
950
951
952 -- dbms_output.put_line('before Get_Acct_Period_ID');
953 Get_Acct_Period_ID
954 ( p_org_id => p_org_id
955 , p_rpt_from_date => l_date_from
956 , p_rpt_to_date => l_date_to
957 , x_rpt_from_acct_per_id => l_rpt_from_acct_per_id
958 , x_rpt_to_acct_per_id => l_rpt_to_acct_per_id
959 );
960
961 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
962 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
963 ,G_MODULE_HEAD || l_routine || '.rptacctpd'
964 ,'Report from Acct period id:' || l_rpt_from_acct_per_id ||
965 ' ' || 'Report to Acct period id:' || l_rpt_to_acct_per_id
966 );
967 END IF;
968
969 -- dbms_output.put_line('before Check_Period_Close');
970 CST_MGD_INFL_ADJUSTMENT_PVT.Check_Period_Close
971 ( p_org_id => p_org_id
972 , p_acct_period_id => l_rpt_to_acct_per_id
973 );
974
975 IF p_item_from_code IS NULL OR p_item_to_code IS NULL
976 THEN
977 OPEN l_item_all_csr( p_org_id
978 , l_rpt_from_acct_per_id
979 , l_rpt_to_acct_per_id
980 );
981 ELSE
982 OPEN l_item_range_csr( p_org_id
983 , p_item_from_code
984 , p_item_to_code
985 , l_rpt_from_acct_per_id
986 , l_rpt_to_acct_per_id
987 );
988 END IF;
989 LOOP
990 IF p_item_from_code IS NULL OR p_item_to_code IS NULL
991 THEN
992 FETCH
993 l_item_all_csr
994 INTO
995 l_item_id
996 , l_acct_period_id
997 , l_per_begin_qty
998 , l_begin_infl_adj
999 , l_final_infl_adj
1000 , l_final_qty
1001 , l_period_schedule_close_date
1002 , l_period_start_date;
1003 EXIT WHEN l_item_all_csr%NOTFOUND;
1004 ELSE
1005 FETCH
1006 l_item_range_csr
1007 INTO
1008 l_item_id
1009 , l_acct_period_id
1010 , l_per_begin_qty
1011 , l_begin_infl_adj
1012 , l_final_infl_adj
1013 , l_final_qty
1014 , l_period_schedule_close_date
1015 , l_period_start_date;
1016 EXIT WHEN l_item_range_csr%NOTFOUND;
1017 END IF;
1018
1019 -- CST_MGD_INFL_ADJUSTMENT_PVT.Get_Purchase_Qty
1020 -- ( p_org_id => p_org_id
1021 -- , p_inventory_item_id => l_item_id
1022 -- , p_acct_period_id => l_acct_period_id
1023 -- , x_purchase_qty => l_purchase_qty
1024 -- );
1025
1026 -- IF l_final_qty - l_purchase_qty = 0
1027 -- THEN
1028 -- l_unit_infl_adj := 0;
1029 -- ELSE
1030 -- l_unit_infl_adj := l_final_infl_adj/(l_final_qty - l_purchase_qty);
1031 -- END IF;
1032
1033 IF l_final_qty = 0
1034 THEN
1035 l_unit_infl_adj := 0;
1036 ELSE
1037 l_unit_infl_adj := l_final_infl_adj/l_final_qty;
1038 END IF;
1039
1040 -- Get the valid cost group
1041 -- as part of bug#1474753 fix
1042 Get_valid_cost_group( p_org_id
1043 ,l_cost_group_id);
1044
1045 IF l_acct_period_id = l_rpt_from_acct_per_id
1046 THEN
1047 -- in case report start date doesn't align with
1048 -- account period start date
1049 Get_Offset_Qty
1050 ( p_org_id => p_org_id
1051 , p_item_id => l_item_id
1052 , p_from_acct_per_id => l_rpt_from_acct_per_id
1053 , p_rpt_from_date => l_rpt_date_from
1054 , p_cost_group_id => l_cost_group_id
1055 , x_offset_qty => l_offset_qty
1056 );
1057 ELSE
1058 l_offset_qty := 0;
1059 END IF;
1060
1061 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.string(FND_LOG.LEVEL_EVENT
1063 ,G_MODULE_HEAD || l_routine || '.offsetqty'
1064 ,'Offset Qty:' || l_offset_qty
1065 );
1066 END IF;
1067
1068 -- dbms_output.put_line('before Get_Period_End_Avg_Cost');
1069 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Period_End_Avg_Cost
1070 ( p_acct_period_id => l_acct_period_id
1071 , p_org_id => p_org_id
1072 , p_inv_item_id => l_item_id
1073 , p_cost_group_id => l_cost_group_id
1074 , x_period_end_item_avg_cost => l_item_unit_cost
1075 );
1076
1077 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.string(FND_LOG.LEVEL_EVENT
1079 ,G_MODULE_HEAD || l_routine || '.itemunitcost'
1080 ,'Item Id:' || l_item_id || ' ' ||
1081 'Item Unit Cost:' || l_item_unit_cost
1082 );
1083 END IF;
1084
1085 -- Get Previous Account Period Id
1086 Get_Previous_Acct_Period
1087 ( p_organization_id => p_org_id
1088 , p_period_start_date => l_period_start_date
1089 , x_prev_acct_period_id => l_prev_acct_period_id
1090 , x_prev_sch_close_date => l_prev_sch_close_date
1091 );
1092
1093 -- Schedule close date set to 23:59:59
1094 l_prev_sch_close_date := TRUNC(l_prev_sch_close_date) + (86399/86400);
1095
1096 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1097 FND_LOG.string(FND_LOG.LEVEL_EVENT
1098 ,G_MODULE_HEAD || l_routine || '.itempdinfo'
1099 ,'Item Id:' || l_item_id || ' ' ||
1100 'Period Start Date:' || l_period_start_date || ' ' ||
1101 'Previous Acct Period Id:' || l_prev_acct_period_id || ' ' ||
1102 'Previous Schedule Close Date:' || l_prev_sch_close_date
1103 );
1104 END IF;
1105
1106 -- Get Previous Period Info
1107 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Previous_Period_info
1108 ( p_country_code => NULL
1109 , p_organization_id => p_org_id
1110 , p_inventory_item_id => l_item_id
1111 , p_acct_period_id => l_acct_period_id
1112 , p_prev_acct_period_id => l_prev_acct_period_id
1113 , p_cost_group_id => l_cost_group_id
1114 , x_previous_qty => l_previous_qty
1115 , x_previous_cost => l_previous_cost
1116 , x_previous_inflation_adj => l_previous_inflation_adj
1117 );
1118
1119 IF l_previous_qty <> 0 THEN
1120 l_previous_unit_cost := (l_previous_cost / l_previous_qty);
1121 ELSE
1122 l_previous_unit_cost := 0;
1123 END IF;
1124
1125 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1126 FND_LOG.string(FND_LOG.LEVEL_EVENT
1127 ,G_MODULE_HEAD || l_routine || '.prevpdinfo'
1128 ,'Previous Qty:' || l_previous_qty || ' ' ||
1129 'Previous Period Cost:' || l_previous_cost || ' ' ||
1130 'Previous Inflation Adj:' || l_previous_inflation_adj
1131 );
1132 END IF;
1133
1134 IF l_rpt_from_acct_per_id <> l_rpt_to_acct_per_id
1135 AND
1136 l_acct_period_id = l_rpt_from_acct_per_id
1137 THEN
1138 l_per_first_txn_date := l_rpt_date_from;
1139 l_per_last_txn_date :=
1140 TRUNC(l_period_schedule_close_date) + (86399/86400);
1141 ELSIF
1142 l_rpt_from_acct_per_id <> l_rpt_to_acct_per_id
1143 AND
1144 l_acct_period_id = l_rpt_to_acct_per_id
1145 THEN
1146 l_per_first_txn_date := TRUNC(l_period_start_date);
1147 l_per_last_txn_date := l_rpt_date_to;
1148 ELSIF
1149 l_rpt_from_acct_per_id = l_rpt_to_acct_per_id
1150 THEN
1151 l_per_first_txn_date := l_rpt_date_from;
1152 l_per_last_txn_date := l_rpt_date_to;
1153 ELSE
1154 l_per_first_txn_date := TRUNC(l_period_start_date);
1155 l_per_last_txn_date :=
1156 TRUNC(l_period_schedule_close_date) + (86399/86400);
1157 END IF;
1158
1159 -- dbms_output.put_line('before Get_Item_Txn_Info');
1160 Get_Item_Txn_Info
1161 ( p_org_id => p_org_id
1162 , p_item_id => l_item_id
1163 , p_acct_period_id => l_acct_period_id
1164 , p_per_first_txn_date => l_per_first_txn_date
1165 , p_per_last_txn_date => l_per_last_txn_date
1166 , p_item_prev_cost => l_previous_unit_cost
1167 , p_item_init_qty => l_per_begin_qty
1168 , p_item_init_infl => l_begin_infl_adj
1169 , p_item_unit_infl_adj => l_unit_infl_adj
1170 , p_offset_qty => l_offset_qty
1171 , p_cost_group_id => l_cost_group_id
1172 , x_rpt_item_tbl_rec => l_rpt_item_tbl_rec
1173 );
1174 -- dbms_output.put_line('after Get_Item_Txn_Info');
1175
1176 l_index := NVL(l_rpt_item_tbl_rec.FIRST, 0);
1177 IF l_index > 0
1178 THEN
1179 LOOP
1180 Insert_Rpt_Data
1181 ( p_rpt_item_rec => l_rpt_item_tbl_rec(l_index)
1182 );
1183 EXIT WHEN l_index = l_rpt_item_tbl_rec.LAST;
1184 l_index := l_rpt_item_tbl_rec.NEXT(l_index);
1185 END LOOP;
1186 END IF;
1187
1188 END LOOP;
1189
1190 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1191 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1192 ,G_MODULE_HEAD || l_routine || '.end'
1193 ,l_routine || '>'
1194 );
1195 END IF;
1196
1197 EXCEPTION
1198
1199 WHEN OTHERS THEN
1200 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1201 THEN
1202 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1203 , 'Create_Infl_Adj_Rpt'
1204 );
1205 END IF;
1206 RAISE;
1207
1208 END Create_Infl_Adj_Rpt;
1209
1210
1211 END CST_MGD_INFL_ADJUSTMENT_RPT;