1 PACKAGE BODY CST_MGD_MSTR_BOOK_RPT AS
2 -- $Header: CSTGMBKB.pls 120.2.12010000.4 2008/12/02 05:52:33 vjavli ship $
3 --+=======================================================================+
4 --| Copyright (c) 1999 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| CSTGMBKB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body for Inventory Master Book report data generation |
13 --| |
14 --| PROCEDURE LIST |
15 --| Insert_Rpt_Data |
16 --| Get_Acct_Period_ID |
17 --| Get_Unit_Infl_Adj_Cost |
18 --| Get_Ini_Total_Infl |
19 --| Get_Item_Txn_Info |
20 --| Create_Inv_Msbk_Rpt |
21 --| |
22 --| HISTORY |
23 --| 07/16/1999 ksaini Created from CSTRIADB.pls |
24 --| 08/30/1999 ksaini Remove ABC Class Name and Assignment |
25 --| Group name from the temp table |
26 --| 09/06/1999 ksaini Modified Code for Subinventory Range |
27 --| 08/19/2001 vjavli Bug#1917957 to fix the wrong begin |
28 --| quantity |
29 --| 10/04/2001 vjavli Cost group id NULL created as part of |
30 --| bug#1474753 fix; this is to support |
31 --| CST_MGD_INFL_ADJUSTMENT_PVT.Get_Period_|
32 --| End_Avg_Cost |
33 --| 10/20/2001 fdubois Two changes made :a) Begin On Hand Qty |
34 --| cannot be derived from |
35 --| MTL_PER_CLOSE_DTLS because the table is|
36 --| only used for Avg costing. Also the |
37 --| datte_to and date_from can fall within |
38 --| open periods. |
39 --| b) Date_to changed to extend up to |
40 --| 23:59:59 of the day. |
41 --| Bug#2011340 |
42 --| 01/28/2002 vjavli WIP transactions should be excluded |
43 --| Bug#2198569 |
44 --| 01/30/2002 vjavli excluded in procedures get_offset_qty |
45 --| get_item_txn_info |
46 --| 07/11/2002 vjavli Bug#2433926 fix no validation for |
47 --| transaction type disable date |
48 --| 09/18/2002 vjavli Bug#2576310 to consider the sub |
49 --| inventories while getting the begin |
50 --| onhand quantity |
51 --| 19/11/2002 tsimmond UTF8 : changed org_name size to 240 |
52 --| 02/14/2003 vjavli Bug#2799104: Average Cost Update Trn |
53 --| should be shown; filter logic of WIP |
54 --| transaction modified for correction |
55 --| 03/31/2003 vjavli Bug#2865534 fix: Cosigned inventory |
56 --| transactions to be eliminated |
57 --| owning_tp_type = 1 for consigned trn |
58 --| 05/23/2003 vjavli Bug#2904882 fix: transaction_id added |
59 --| temporary table inorder to get correct |
60 --| sort by transaction_date, |
61 --| transaction_id |
62 --| 05/29/2003 vjavli Bug#2977020 fix: ACU transactions not |
63 --| being displayed - reason: primary_qty |
64 --| is 0 ; verified in internal system for |
65 --| 3 types of ACUs. In all 3 types the qty|
66 --| is not null |
67 --| Found that percentage and new avg cost |
68 --| to be supported |
69 --| primary_qty <> 0 condition removed in |
70 --| get_item_txn_info |
71 --| 06/10/2003 vjavli regression from bug#2904882 fix: sort |
72 --| trunc(transaction_date), transaction_id|
73 --| get_item_txn_info cursor added with |
74 --| order by trunc(transaction_date), |
75 --| transaction_id bug#3002073 fix |
76 --| 06/22/2003 vjavli Bug#3013597 fix: cost type of the |
77 --| organization is to be verified. If cost|
78 --| type is Standard then Begin Unit Cost |
79 --| will be actual cost of very first txn |
80 --| and End Unit Cost will be actual cost |
81 --| of last txn. If Cost Type is Average, |
82 --| LIFO, FIFO then Begin Unit Cost will be|
83 --| prior cost of very first txn and End |
84 --| Unit Cost will be new cost of last txn |
85 --| in the report date range |
86 --| 06/22/2003 vjavli NOTE: Create_Infl_Adj_Rpt procedure |
87 --| removed since it is not invoked by any |
88 --| of the inflation reports |
89 --| 09/09/2003 fdubois bug#3118846 : exclude NON qty tracked |
90 --| subinventory transactions as the ON |
91 --| HAND qty in Inventory does NOT account |
92 --| for these quantities. |
93 --| 09/10/2003 fdubois code cleaning : removing unused API |
94 --| get_offset_qty and related code (dead |
95 --| code) |
96 --| 10/02/2003 fdubois bug#3147073 :exclude non asset items |
97 --| and non asset subinventory and |
98 --| exclude expense items |
99 --| 05/12/2004 vjavli Performance bug fix as in bug#2862480 |
100 --| for 11.5.9. Get_Acct_Period_Id and |
101 --| Get_Acct_Period_Id_invmbk modified |
102 --| NOTE:Get_Acct_Period_id and Get_Acct_ |
103 --| Period_Id_invmbk are not used anywhere |
104 --| 02/17/2006 vmutyala Bug # 4086259 Added Creation_date to |
105 --| CST_MGD_MSTR_BOOK_TEMP |
106 --| 02/24/2006 vmutyala Bug # 4912772 Performance issue in the |
107 --| dynamic query in Create_Inv_Msbk_Rpt is|
108 --| resolved by restructuring the query |
109 --| 10/27/2008 vjavli FP Bug 7458643 fix:Standard cost update|
110 --| transaction should be displayed with |
111 --| correct cost(new cost minus prior cost)|
112 --| * primary_quantity (onhand qty at that |
113 --| moment). l_primary_qty := 0; |
114 --| l_total_cost := |
115 --| (l_item_txn_info.new_cost - |
116 --| l_item_txn_info.prior_cost) * |
117 --| l_item_txn_info.primary_quantity; |
118 --| Get_Item_Txn_Info proc modified |
119 --|12/01/2008 vjavli Bug 7458643 fix: Standard cost Update |
120 --| found that primary_quantity is 0; |
121 --| We have to use quantity_adjusted |
122 --| Also, actual_cost will be NULL |
123 --| Even in Average Cost update, better to |
124 --| use quantity_adjusted instead of PQ |
125 --| quantity_adjusted will always has value|
126 --| In ACU, priorcost,newcost,actcost will |
127 --| will be NOT NULL |
128 --+=======================================================================+
129
130
131 --===================
132 -- TYPES
133 --===================
134 TYPE Report_Rec_Type IS RECORD
135 ( transaction_id NUMBER
136 , organization_id NUMBER
137 , inventory_item_id NUMBER
138 , uom_code varchar2(3) -- added for inv book
139 , item_code varchar2(40) -- added for inv book
140 , item_desc varchar2(240) -- added for inv book
141 , org_name varchar2(240) -- added for inv book
142 , currency_code varchar2(15) -- added for inv book
143 , txn_source varchar2(30) -- added for inv_book
144 , txn_date DATE
145 , txn_type VARCHAR2(30)
146 , txn_ini_qty NUMBER
147 , txn_ini_unit_cost NUMBER
148 , txn_ini_h_total_cost NUMBER
149 , txn_ini_adj_total_cost NUMBER
150 , txn_qty NUMBER
151 , txn_unit_cost NUMBER
152 , txn_h_total_cost NUMBER
153 , txn_adj_total_cost NUMBER
154 , txn_fnl_qty NUMBER
155 , txn_fnl_unit_cost NUMBER
156 , txn_fnl_h_total_cost NUMBER
157 , txn_fnl_adj_total_cost NUMBER
158 , CREATION_DATE DATE
159 );
160
161 TYPE Report_Tbl_Rec_Type IS TABLE OF Report_Rec_Type
162 INDEX BY BINARY_INTEGER;
163
164 --===================
165 -- CONSTANTS
166 --===================
167 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_MGD_MSTR_BOOK_RPT';
168 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_MGD_INV_MASTER_BOOK';
169
170 --===================
171 -- GLOBAL VARIABLES
172 --===================
173 g_txn_cost_exc EXCEPTION;
174
175 --===================
176 -- PRIVATE PROCEDURES
177 --===================
178
179 --========================================================================
180 -- PROCEDURE : Insert_Rpt_Data PRIVATE
181 -- PARAMETERS: p_rpt_item_rec Kardex report data for one row
182 -- COMMENT :
183 -- EXCEPTIONS: OTHERS
184 --========================================================================
185 PROCEDURE Insert_Rpt_Data (
186 p_rpt_item_rec IN Report_Rec_Type
187 )
188 IS
189 BEGIN
190 INSERT INTO
191 CST_MGD_MSTR_BOOK_TEMP(
192 TRANSACTION_ID
193 , ORGANIZATION_ID
194 , INVENTORY_ITEM_ID
195 , UOM_CODE -- added for inv book
196 , ITEM_CODE
197 , ITEM_DESC
198 , ORG_NAME
199 , CURRENCY_CODE
200 , TXN_SOURCE -- added for inv book
201 , TXN_DATE
202 , TXN_TYPE
203 , TXN_INI_QTY
204 , TXN_INI_UNIT_COST
205 , TXN_INI_H_TOTAL_COST
206 , TXN_INI_ADJ_TOTAL_COST
207 , TXN_QTY
208 , TXN_UNIT_COST
209 , TXN_H_TOTAL_COST
210 , TXN_ADJ_TOTAL_COST
211 , TXN_FNL_QTY
212 , TXN_FNL_UNIT_COST
213 , TXN_FNL_H_TOTAL_COST
214 , TXN_FNL_ADJ_TOTAL_COST
215 , CREATION_DATE
216 )
217 VALUES(
218 p_rpt_item_rec.transaction_id
219 , p_rpt_item_rec.organization_id
220 , p_rpt_item_rec.inventory_item_id
221 , p_rpt_item_rec.uom_code -- added for inv book
222 , p_rpt_item_rec.item_code -- added for inv book
223 , p_rpt_item_rec.item_desc -- added for inv book
224 , p_rpt_item_rec.org_name -- added for inv book
225 , p_rpt_item_rec.currency_code -- added for inv book
226 , p_rpt_item_rec.txn_source -- added for inv book
227 , p_rpt_item_rec.txn_date
228 , p_rpt_item_rec.txn_type
229 , p_rpt_item_rec.txn_ini_qty
230 , p_rpt_item_rec.txn_ini_unit_cost
231 , p_rpt_item_rec.txn_ini_h_total_cost
232 , p_rpt_item_rec.txn_ini_adj_total_cost
233 , p_rpt_item_rec.txn_qty
234 , p_rpt_item_rec.txn_unit_cost
235 , p_rpt_item_rec.txn_h_total_cost
236 , p_rpt_item_rec.txn_adj_total_cost
237 , p_rpt_item_rec.txn_fnl_qty
238 , p_rpt_item_rec.txn_fnl_unit_cost
239 , p_rpt_item_rec.txn_fnl_h_total_cost
240 , p_rpt_item_rec.txn_fnl_adj_total_cost
241 , p_rpt_item_rec.CREATION_DATE
242 );
243
244 EXCEPTION
245
246 WHEN OTHERS THEN
247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
248 THEN
249 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
250 , 'Insert_Rpt_Data'
251 );
252 END IF;
253 RAISE;
254
255 END Insert_Rpt_Data;
256
257 --========================================================================
258 -- PROCEDURE : Get_Acct_Period_ID_invmbk PRIVATE
259 -- PARAMETERS: p_org_id Organization ID
260 -- p_rpt_from_date Report start date
261 -- P_rpt_to_date Report end date
262 -- x_rpt_from_acct_per_id Report start account period ID
263 -- x_rpt_to_acct_per_id Report end account period ID
264 -- COMMENT : Get the account period IDs for user defined reporting
265 -- period
266 -- EXCEPTIONS:
267 --========================================================================
268 PROCEDURE Get_Acct_Period_ID_invmbk (
269 p_org_id IN NUMBER
270 , p_rpt_from_date IN VARCHAR2
271 , p_rpt_to_date IN VARCHAR2
272 , x_rpt_from_acct_per_id OUT NOCOPY NUMBER
273 , x_rpt_to_acct_per_id OUT NOCOPY NUMBER
274 )
275 IS
276 l_rpt_from_acct_per_id NUMBER;
277 l_rpt_to_acct_per_id NUMBER;
278 l_rpt_from_date DATE;
279 l_rpt_to_date DATE;
280
281 -- Cursor to retrieve from accounting period id
282 CURSOR from_acct_period_cur(c_rpt_from_date DATE)
283 IS
284 SELECT
285 f.acct_period_id
286 FROM
287 org_acct_periods f
288 WHERE f.organization_id = p_org_id
289 AND f.period_start_date <= c_rpt_from_date
290 AND f.schedule_close_date >= c_rpt_from_date;
291
292 -- Cursor to retrieve to accounting period id
293 CURSOR to_acct_period_cur(c_rpt_to_date DATE)
294 IS
295 SELECT
296 t.acct_period_id
297 FROM
298 org_acct_periods t
299 WHERE t.organization_id = p_org_id
300 AND t.period_start_date <= c_rpt_to_date
301 AND t.schedule_close_date >= c_rpt_to_date;
302
303 -- Exception
304 acct_period_not_found_exc EXCEPTION;
305
306 BEGIN
307 l_rpt_from_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_from_date));
308 l_rpt_to_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_to_date));
309
310 -- Get from account period id
311 OPEN from_acct_period_cur(l_rpt_from_date);
312 FETCH from_acct_period_cur
313 INTO x_rpt_from_acct_per_id;
314
315 IF from_acct_period_cur%NOTFOUND THEN
316 RAISE acct_period_not_found_exc;
317 END IF;
318 CLOSE from_acct_period_cur;
319
320 -- Get to account period id
324
321 OPEN to_acct_period_cur(l_rpt_to_date);
322 FETCH to_acct_period_cur
323 INTO x_rpt_to_acct_per_id;
325 IF to_acct_period_cur%NOTFOUND THEN
326 RAISE acct_period_not_found_exc;
327 END IF;
328 CLOSE to_acct_period_cur;
329
330
331 EXCEPTION
332
333 WHEN acct_period_not_found_exc THEN
334 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ACCT_PER_ID_INVBK');
335
336 FND_MSG_PUB.Add;
337 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
338 THEN
339 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
340 , 'Get_Acct_Period_ID_invmbk'
341 );
342 END IF;
343 RAISE ;
344
345
346 WHEN OTHERS THEN
347 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
348 THEN
349 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
350 , 'Get_Acct_Period_ID_invmbk'
351 );
352 END IF;
353 RAISE;
354
355 END Get_Acct_Period_ID_invmbk;
356
357
358 --========================================================================
359 -- PROCEDURE : Get_Acct_Period_ID PRIVATE
360 -- PARAMETERS: p_org_id Organization ID
361 -- p_rpt_from_date Report start date
362 -- P_rpt_to_date Report end date
363 -- x_rpt_from_acct_per_id Report start account period ID
364 -- x_rpt_to_acct_per_id Report end account period ID
365 -- COMMENT : Get the account period IDs for user defined reporting
366 -- period
367 -- EXCEPTIONS:
368 --========================================================================
369 PROCEDURE Get_Acct_Period_ID (
370 p_org_id IN NUMBER
371 , p_rpt_from_date IN VARCHAR2
372 , p_rpt_to_date IN VARCHAR2
373 , x_rpt_from_acct_per_id OUT NOCOPY NUMBER
374 , x_rpt_to_acct_per_id OUT NOCOPY NUMBER
375 )
376 IS
377 l_rpt_from_acct_per_id NUMBER;
378 l_rpt_to_acct_per_id NUMBER;
379 l_rpt_from_date DATE;
380 l_rpt_to_date DATE;
381
382 -- Cursor to retrieve from accounting period id
383 CURSOR from_acct_period_cur(c_rpt_from_date DATE)
384 IS
385 SELECT
386 f.acct_period_id
387 FROM
388 org_acct_periods f
389 WHERE f.organization_id = p_org_id
390 AND f.period_start_date <= c_rpt_from_date
391 AND f.schedule_close_date >= c_rpt_from_date
392 AND F.Open_Flag = 'N'
393 AND F.Period_Close_Date IS NOT NULL;
394
395 -- Cursor to retrieve to accounting period id
396 CURSOR to_acct_period_cur(c_rpt_to_date DATE)
397 IS
398 SELECT
399 t.acct_period_id
400 FROM
401 org_acct_periods t
402 WHERE t.organization_id = p_org_id
403 AND t.period_start_date <= c_rpt_to_date
404 AND t.schedule_close_date >= c_rpt_to_date
405 AND T.Open_Flag = 'N'
406 AND T.Period_Close_Date IS NOT NULL;
407
408 -- Exception
409 acct_period_not_found_exc EXCEPTION;
410
411 BEGIN
412 l_rpt_from_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_from_date));
413 l_rpt_to_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_to_date));
414
415 -- Get from account period id
416 OPEN from_acct_period_cur(l_rpt_from_date);
417 FETCH from_acct_period_cur
418 INTO x_rpt_from_acct_per_id;
419
420 IF from_acct_period_cur%NOTFOUND THEN
421 RAISE acct_period_not_found_exc;
422 END IF;
423 CLOSE from_acct_period_cur;
424
425 -- Get to account period id
426 OPEN to_acct_period_cur(l_rpt_to_date);
427 FETCH to_acct_period_cur
428 INTO x_rpt_to_acct_per_id;
429
430 IF to_acct_period_cur%NOTFOUND THEN
431 RAISE acct_period_not_found_exc;
432 END IF;
433 CLOSE to_acct_period_cur;
434
435 EXCEPTION
436
437 WHEN acct_period_not_found_exc THEN
438 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ND_ACCT_PER_ID');
439 FND_MSG_PUB.Add;
440 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
441 THEN
442 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
443 , 'Get_Acct_Period_ID'
444 );
445 END IF;
446 RAISE ;
447
448
449 WHEN OTHERS THEN
450 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
451 THEN
452 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
453 , 'Get_Acct_Period_ID'
454 );
455 END IF;
456 RAISE;
457
458 END Get_Acct_Period_ID;
459
460
461 --========================================================================
462 -- PROCEDURE : Get_Unit_Infl_Adj_Cost PRIVATE
463 -- PARAMETERS: p_org_id Organization ID
464 -- p_acct_period_id Account period ID
465 -- p_item_id Inventory item ID
466 -- x_unit_infl_adj Inventory item period end unit
467 -- inflation adjusted cost
468 -- : x_init_qty Period begin quantity
469 -- COMMENT : Retrieve item unit inflation adjusted cost and begin
473 PROCEDURE Get_Unit_Infl_Adj_Cost (
470 -- quantity
471 -- EXCEPTIONS:
472 --========================================================================
474 p_org_id IN NUMBER
475 , p_acct_period_id IN NUMBER
476 , p_item_id IN NUMBER
477 , x_unit_infl_adj OUT NOCOPY NUMBER
478 , x_init_qty OUT NOCOPY NUMBER
479 )
480 IS
481 l_final_infl_adj NUMBER;
482 l_final_qty NUMBER;
483 BEGIN
484
485 SELECT
486 Begin_Qty
487 , NVL((Actual_Inflation_Adj - Issue_Inflation_Adj), 0)
488 , NVL((Actual_Qty - Issue_Qty), 0)
489 INTO
490 x_init_qty
491 , l_final_infl_adj
492 , l_final_qty
493 FROM
494 CST_MGD_INFL_ADJUSTED_COSTS
495 WHERE Organization_ID = p_org_id
496 AND Acct_Period_ID = p_acct_period_id
497 AND Inventory_Item_ID = p_item_id;
498
499 IF l_final_qty = 0
500 THEN
501 x_unit_infl_adj := 0;
502 ELSE
503 x_unit_infl_adj := l_final_infl_adj/l_final_qty;
504 END IF;
505
506 EXCEPTION
507
508 WHEN OTHERS THEN
509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
510 THEN
511 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
512 , 'Get_Unit_Infl_Adj_Cost'
513 );
514 END IF;
515 RAISE;
516
517 END Get_Unit_Infl_Adj_Cost;
518
519
520 --========================================================================
521 -- PROCEDURE : Get_Txn_Type PRIVATE
522 -- PARAMETERS: p_txn_type_id Transaction type ID
523 -- x_txn_type_name Transaction type name
524 -- COMMENT : Retrieve transaction type name from ID
525 -- EXCEPTIONS:
526 --========================================================================
527 PROCEDURE Get_Txn_Type (
528 p_txn_type_id IN NUMBER
529 , x_txn_type_name OUT NOCOPY VARCHAR2
530 )
531 IS
532 BEGIN
533
534 SELECT
535 Transaction_Type_Name
536 INTO
537 x_txn_type_name
538 FROM
539 MTL_TRANSACTION_TYPES
540 WHERE Transaction_Type_ID = p_txn_type_id;
541
542 -- Bug#2433926 fix no validation for transaction type disable date
543 -- AND NVL(Disable_Date, SYSDATE + 1) > SYSDATE;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
548 THEN
549 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
550 , 'Get_Txn_Type'
551 );
552 END IF;
553 RAISE;
554
555 END Get_Txn_Type;
556
557 --========================================================================
558 -- PROCEDURE : Get_Item_Txn_Info PRIVATE
559 -- PARAMETERS: p_org_id Organization ID
560 -- p_item_id Inventory item ID
561 -- p_acct_period_id Mfg accounting period ID
562 -- p_per_first_txn_date First transaction date for
563 -- reporting.
564 -- p_per_last_txn_date Last transaction date for
565 -- reporting.
566 -- p_item_unit_cost Inventory item unit average cost
567 -- p_primary_cost_method Primary Cost Method of Organization
568 -- p_item_init_qty Inventory item period begin
569 -- quantity
570 -- p_item_unit_infl_adj Inventory item period end unit
571 -- inflation adjusted cost
572 -- x_rpt_item_tbl_rec Report data record
573 -- COMMENT : Builds data for one row
574 -- Bug3118846 fix : exclude NON qty tracked subinventories trx
575 -- EXCEPTION : g_txn_cost_exc Missing transaction costs.
576 --========================================================================
577 PROCEDURE Get_Item_Txn_Info (
578 p_org_id IN NUMBER
579 , p_item_id IN NUMBER
580 , p_uom_code IN VARCHAR2 --added for inv book
581 , p_item_code IN VARCHAR2
582 , p_item_desc IN VARCHAR2
583 , p_org_name IN VARCHAR2
584 , p_currency_code IN VARCHAR2
585 , p_subinv_from IN VARCHAR2
586 , p_subinv_to IN VARCHAR2
587 , p_acct_period_id IN NUMBER
588 , p_per_first_txn_date IN VARCHAR2
589 , p_per_last_txn_date IN VARCHAR2
590 , p_item_unit_cost IN NUMBER
591 , p_primary_cost_method IN NUMBER
592 , p_item_init_qty IN NUMBER
593 , p_item_init_infl IN NUMBER
594 , p_item_unit_infl_adj IN NUMBER
595 , x_rpt_item_tbl_rec OUT NOCOPY Report_Tbl_Rec_Type
596 )
597 IS
598 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
599 l_txn_init_qty NUMBER;
600 l_txn_init_infl NUMBER;
601 l_prev_acct_period_id NUMBER;
602 l_prev_sch_close_date DATE;
603 l_index BINARY_INTEGER := 1;
604 l_begin_unit_cost NUMBER;
605 l_txn_cost_exc EXCEPTION;
606 l_per_first_txn_date DATE;
607 l_per_last_txn_date DATE;
608 l_primary_qty NUMBER;
609 l_total_cost NUMBER;
610 -- Bug#2799104 fix: to exclude WIP scrap transaction and
611 -- WIP cost update transaction
615 SELECT
612 -- value_change for Average Cost Update transaction
613 -- Bug#7458643 fix: quantity_adjusted added
614 CURSOR l_item_txn_csr IS
616 mmt.Transaction_ID Transaction_ID
617 , mmt.Transaction_Type_ID Transaction_Type_ID
618 , mmt.Transaction_Source_Type_Id Transaction_Source_Type_Id
619 , mmt.Transaction_action_id Transaction_action_id
620 , mmt.Transaction_Date Transaction_Date
621 , mmt.Primary_Quantity Primary_Quantity
622 , mmt.Actual_Cost Actual_Cost
623 , mmt.Prior_Cost Prior_Cost
624 , mmt.New_Cost New_Cost
625 , mmt.value_change value_change
626 , mmt.percentage_change percentage_change
627 , mmt.Transfer_Organization_ID Transfer_Organization_ID
628 , mmt.Creation_date Creation_date
629 , mmt.quantity_adjusted
630 FROM
631 MTL_MATERIAL_TRANSACTIONS mmt
632 WHERE mmt.Organization_ID = p_org_id
633 AND mmt.Inventory_Item_ID = p_item_id
634 AND NVL(mmt.Acct_Period_ID,0) = NVL(p_acct_period_id,NVL(mmt.ACCT_PERIOD_ID,0))
635 AND NVL(mmt.Subinventory_code,'0') >= NVL(p_subinv_from,NVL(mmt.Subinventory_code,'0'))
636 AND NVL(mmt.Subinventory_code,'0') <= NVL(p_subinv_to,NVL(mmt.Subinventory_code,'0'))
637 AND mmt.Transaction_Date BETWEEN l_per_first_txn_date
638 AND l_per_last_txn_date
639 AND ( mmt.SUBINVENTORY_CODE is NULL
640 OR
641 mmt.SUBINVENTORY_CODE =
642 ( SELECT SECONDARY_INVENTORY_NAME
643 FROM MTL_SECONDARY_INVENTORIES
644 WHERE SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
645 AND ORGANIZATION_ID = mmt.ORGANIZATION_ID
646 AND QUANTITY_TRACKED = 1
647 AND ASSET_INVENTORY = 1)
648 )
649 AND mmt.transaction_id NOT IN
650 (SELECT mmt1.transaction_id
651 FROM
652 MTL_MATERIAL_TRANSACTIONS mmt1
653 WHERE mmt1.organization_id = p_org_id
654 AND mmt1.inventory_item_id = p_item_id
655 AND NVL(mmt1.Acct_Period_ID,0) = NVL(p_acct_period_id,NVL(mmt1.ACCT_PERIOD_ID,0))
656 AND NVL(mmt1.Subinventory_code,'0') >= NVL(p_subinv_from,NVL(mmt1.Subinventory_code,'0'))
657 AND NVL(mmt1.Subinventory_code,'0') <= NVL(p_subinv_to,NVL(mmt1.Subinventory_code,'0'))
658 AND mmt1.transaction_source_type_id = 5
659 AND mmt1.transaction_action_id = 24
660 AND nvl(mmt1.owning_tp_type,2) = 1
661 )
662 AND mmt.Transaction_action_id <> 30
663 AND nvl(mmt.owning_tp_type,2) <> 1
664 ORDER BY
665 mmt.Acct_Period_ID
666 , trunc(mmt.Transaction_Date)
667 , mmt.CREATION_DATE
668 , mmt.transaction_id;
669
670
671
672 BEGIN
673
674 -- The From date is at midnight for the day
675 l_per_first_txn_date:=TRUNC(FND_DATE.canonical_to_date(p_per_first_txn_date));
676 -- The to date is at 23:59:59 of that date entered.
677 l_per_last_txn_date:=TRUNC(FND_DATE.canonical_to_date(p_per_last_txn_date)) + (86399/86400);
678
679 l_txn_init_qty := p_item_init_qty;
680 l_txn_init_infl := p_item_init_infl;
681
682
683 FOR l_item_txn_info IN l_item_txn_csr
684 LOOP
685
686 -- =====================================================================
687 -- Bug#2799104 fix: Average Cost Update display
688 -- check whether the transaction is Average Cost Update
689 -- If so, then value_change is the total cost
690 -- for all other ACU transactions it is quantity_adjusted * actual_cost
691 -- Bug#2977020 fix: support percentage and new average cost
692 -- Set the Total Transaction Cost and primary quantity
693 -- =====================================================================
694 IF l_item_txn_info.transaction_source_type_id = 13 AND
695 l_item_txn_info.transaction_action_id = 24 THEN
696 -- Average Cost Update transaction
697 IF l_item_txn_info.value_change IS NOT NULL THEN
698 -- ACU type is value change
699 l_primary_qty := 0; -- to avoid double counting of total quantity
700 l_total_cost := l_item_txn_info.value_change;
701 ELSIF l_item_txn_info.percentage_change IS NOT NULL THEN
702 -- ACU type is percentage
703 l_primary_qty := 0; -- to avoid double counting of total quantity
704 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
705 ELSE
706 -- ACU type New Average Cost
707 l_primary_qty := 0; -- to avoid double counting of total quantity
708 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
709 END IF;
710
711 ELSIF l_item_txn_info.transaction_source_type_id = 11 AND
712 l_item_txn_info.transaction_action_id = 24 THEN
713 -- FP Bug#7458643 fix: Standard Cost Update transaction
714 l_primary_qty := 0; -- to avoid double counting of total quantity
715 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
716 ELSE
717 -- all other transactions
718 l_primary_qty := l_item_txn_info.primary_quantity;
719 l_total_cost := l_primary_qty * l_item_txn_info.actual_cost;
720 END IF;
721
722 IF (l_item_txn_info.Actual_Cost IS NULL) THEN
723 l_item_txn_info.Actual_Cost := 0;
724 END IF;
725
726 IF (l_item_txn_info.Prior_Cost IS NULL) THEN
727 l_item_txn_info.Prior_Cost := 0;
728 END IF;
729
733
730 IF (l_item_txn_info.New_Cost IS NULL) THEN
731 l_item_txn_info.New_Cost := 0;
732 END IF;
734
735 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
736 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
737 l_rpt_item_tbl_rec(l_index).uom_code := p_uom_code;
738 l_rpt_item_tbl_rec(l_index).org_name := p_org_name;
739 l_rpt_item_tbl_rec(l_index).item_code := p_item_code;
740 l_rpt_item_tbl_rec(l_index).item_desc := p_item_desc;
741 l_rpt_item_tbl_rec(l_index).currency_code := p_currency_code;
742 l_rpt_item_tbl_rec(l_index).txn_date :=
743 l_item_txn_info.Transaction_Date;
744 -- Bug 4086259 to insert creation_date into temp table
745 l_rpt_item_tbl_rec(l_index).CREATION_DATE := l_item_txn_info.CREATION_DATE;
746
747 -- Bug#2904882 fix: transaction_id included for correct sort
748 l_rpt_item_tbl_rec(l_index).transaction_id :=
749 l_item_txn_info.Transaction_id;
750
751 begin
752 -- Get Txn_Source_Type_Name
753 Select Transaction_source_type_name
754 Into l_rpt_item_tbl_rec(l_index).txn_source
755 From MTL_TXN_SOURCE_TYPES
756 Where Transaction_Source_Type_Id = l_item_txn_info.transaction_source_type_id;
757 exception
758 when no_data_found then
759 select 'No TXN Source'
760 INTO l_rpt_item_tbl_rec(l_index).txn_source
761 from dual;
762 end;
763
764 Get_Txn_Type
765 ( p_txn_type_id => l_item_txn_info.Transaction_Type_ID
766 , x_txn_type_name => l_rpt_item_tbl_rec(l_index).txn_type
767 );
768
769 -- =======================================================================
770 -- Beginning Balance - Beginning quantity and begin cost for each txn
771 -- =======================================================================
772 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
773 -- Bug#3013597 fix: begin unit cost based on costing method of organization
774 IF p_primary_cost_method = 1 THEN
775 -- Standard costing organization
776 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost :=
777 l_item_txn_info.prior_Cost;
778 ELSE
779 -- Average 2, FIFO 5, LIFO 6 organizations
780 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost :=
781 l_item_txn_info.Prior_Cost;
782 END IF;
783
784 -- ==================================================================
785 -- Beginning balance -- Beginnning quantity * begin cost for each txn
786 -- ==================================================================
787 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
788 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
789 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
790 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := l_txn_init_infl;
791
792 -- ==================================================================
793 -- Final quantity for each transaction
794 -- ==================================================================
795 -- Total quantity including current transaction quantity
796 -- Total quantity = Begin qty + transaction quantity
797 -- For Standard Cost Update or Average Cost Update, primary_quantity
798 -- will be set to 0 in the earlier logic to avoid double counting
799 -- ==================================================================
800 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
801 l_rpt_item_tbl_rec(l_index).txn_ini_qty +
802 l_primary_qty;
803
804 -- =============================================================
805 -- Final Cost for each transaction
806 -- New Cost which is the final cost after processing current txn
807 -- =============================================================
808 IF p_primary_cost_method = 1 THEN
809 -- Standard costing organization
810 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost :=
811 l_item_txn_info.New_Cost;
812 ELSE
813 -- Average 2, FIFO 5, LIFO 6 organizations
814 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost :=
815 l_item_txn_info.New_Cost;
816 END IF;
817
818 -- ==========================================================
819 -- Final total balance
820 -- ==========================================================
821 -- all transactions
822 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
823 l_rpt_item_tbl_rec(l_index).txn_fnl_qty *
824 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost;
825
826 IF ((l_item_txn_info.Primary_Quantity > 0)
827 AND
828 (l_item_txn_info.Transfer_Organization_ID IS NULL))
829 OR
830 (l_item_txn_info.Transfer_Organization_ID = p_org_id)
831 THEN
832 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
833 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
834 ELSE
835 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
836 l_rpt_item_tbl_rec(l_index).txn_fnl_qty * p_item_unit_infl_adj;
837 END IF;
838
839 -- ===============================================================
840 -- Transaction Quantity and Transaction Cost of each transaction
844 -- ===============================================================
841 -- Bug 7458643 fix: For Standard Cost Update - src_type_id is 11.
842 -- Average Cost Update - src_type_id is 13, l_primary_qty is zero.
843 -- For all other txns, l_primary_quantity has not null value.
845 l_rpt_item_tbl_rec(l_index).txn_qty := l_primary_qty;
846
847 -- ======================================================
848 -- Transaction Cost of each transaction
849 -- ======================================================
850 -- Bug 7458643 fix: standard cost update
851 -- transaction unit cost is diff of new_cost - prior_cost
852 -- for all other txns, actual_cost is a transaction cost
853 -- Note that for Average Cost Update txn, actual_cost will
854 -- have item adjustment cost.
855 -- ======================================================
856 IF l_item_txn_info.transaction_source_type_id = 11 AND
857 l_item_txn_info.transaction_action_id = 24 THEN
858 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
859 (l_item_txn_info.new_Cost - l_item_txn_info.prior_Cost);
860 ELSE
861 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
862 l_item_txn_info.Actual_Cost;
863 END IF;
864
865 -- ======================================================
866 -- Transaction Total Cost -- Txn Cost * txn quantity
867 -- =======================================================
868 -- total cost takes the value according to transaction type
869 -- Bug#2799104 fix
870 l_rpt_item_tbl_rec(l_index).txn_h_total_cost :=
871 l_total_cost;
872 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost :=
873 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost -
874 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
875
876 -- ====================================================================
877 -- Set initial quantity to final quantity so far for the next
878 -- transaction, so that for the next transaction initial quantity will
879 -- be final qty until previous transaction before the current txn
880 -- being processed
881 -- ===================================================================
882 l_txn_init_qty := l_rpt_item_tbl_rec(l_index).txn_fnl_qty;
883 l_txn_init_infl := l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost;
884
885 l_index := l_index + 1;
886 END LOOP;
887
888 IF NVL(l_rpt_item_tbl_rec.FIRST, 0) = 0
889 THEN
890
891 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
892 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
893 l_rpt_item_tbl_rec(l_index).uom_code := p_uom_code;
894 l_rpt_item_tbl_rec(l_index).org_name := p_org_name;
895 l_rpt_item_tbl_rec(l_index).item_code := p_item_code;
896 l_rpt_item_tbl_rec(l_index).item_desc := p_item_desc;
897 l_rpt_item_tbl_rec(l_index).currency_code := p_currency_code;
898 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
899
900
901 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost := p_item_unit_cost;
902 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
903 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
904 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
905 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := p_item_init_infl;
906 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
907 l_rpt_item_tbl_rec(l_index).txn_ini_qty;
908 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost := p_item_unit_cost;
909 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
910 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost;
911 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
912 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
913 END IF;
914
915 x_rpt_item_tbl_rec := l_rpt_item_tbl_rec;
916
917 EXCEPTION
918
919 WHEN l_txn_cost_exc THEN
920 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_UNIT_COST_NULL');
921 FND_MSG_PUB.Add;
922 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
923 THEN
924 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
925 , 'Get_Item_Txn_Info'
926 );
927 END IF;
928 RAISE g_txn_cost_exc;
929 WHEN OTHERS THEN
930 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
931 THEN
932 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
933 , 'Get_Item_Txn_Info'
934 );
935 END IF;
936 RAISE;
937
938 END Get_Item_Txn_Info;
939
940
941 --========================================================================
942 -- PROCEDURE : Create_Inv_Msbk_Rpt PUBLIC
943 -- PARAMETERS: p_org_id Organization ID
944 -- p_item_from_code Report start item code
945 -- p_item_to_code Report end item code
946 -- p_rpt_from_date Report start date
947 -- p_rpt_to_date Report end date
948 -- COMMENT : Main procedure called by Inventory Master Book report
949 --========================================================================
950 PROCEDURE Create_Inv_Msbk_Rpt (
951 p_org_id IN NUMBER
955 , p_subinv_from IN VARCHAR2 := NULL
952 , p_category_set_id IN NUMBER := NULL
953 , p_category_from IN VARCHAR2 := NULL
954 , p_category_to IN VARCHAR2 := NULL
956 , p_subinv_to IN VARCHAR2 := NULL
957 , p_abc_group_id IN NUMBER := NULL
958 , p_abc_class_id IN NUMBER := NULL
959 , p_item_from_code IN VARCHAR2 := NULL
960 , p_item_to_code IN VARCHAR2 := NULL
961 , p_rpt_from_date IN VARCHAR2
962 , p_rpt_to_date IN VARCHAR2
963 )
964 IS
965 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
966 l_item_id NUMBER;
967 l_uom_code VARCHAR2(3); -- added for inv book
968 l_item_unit_cost NUMBER;
969 l_begin_unit_cost NUMBER;
970 base_period_id NUMBER;
971 base_qty NUMBER;
972 txn_count NUMBER;
973 additional_qty NUMBER;
974 l_rpt_from_acct_per_id NUMBER;
975 l_rpt_to_acct_per_id NUMBER;
976 l_final_infl_adj NUMBER;
977 l_final_qty NUMBER;
978 l_purchase_qty NUMBER;
979 l_unit_infl_adj NUMBER;
980 l_per_begin_qty NUMBER;
981 l_begin_infl_adj NUMBER;
982 l_per_first_txn_date DATE;
983 l_per_last_txn_date DATE;
984 l_period_start_date DATE;
985 l_period_close_date DATE;
986 l_period_open_flag VARCHAR2(1);
987 l_index BINARY_INTEGER;
988 d_org_name VARCHAR2(240);
989 d_currency_code VARCHAR2(15);
990 d_item_desc VARCHAR2(240);
991 d_item_code VARCHAR2(40);
992 subinv_min VARCHAR2(10);
993 subinv_max VARCHAR2(10);
994 subinv_from VARCHAR2(10);
995 subinv_to VARCHAR2(10);
996 l_rpt_from_date DATE;
997 l_rpt_to_date DATE;
998 l_item_ohq NUMBER ;
999 l_item_trx_qty NUMBER ;
1000
1001 -- primary costing method of an inventory organization
1002 l_primary_cost_method NUMBER;
1003
1004 --variables for dynamic sql query
1005 v_cursorID INTEGER;
1006 v_select_clause VARCHAR2(4000);
1007 v_from_clause VARCHAR2(4000);
1008 v_where_clause VARCHAR2(4000);
1009 v_order_by VARCHAR2(4000);
1010 v_final_query VARCHAR2(4000);
1011 v_dummy INTEGER;
1012
1013 -- Cursor to get primary costing method of an inventory organization
1014 CURSOR get_cost_method_cur(c_organization_id NUMBER)
1015 IS
1016 SELECT
1017 primary_cost_method
1018 FROM
1019 mtl_parameters
1020 WHERE
1021 organization_id = c_organization_id;
1022
1023 -- Exception for cost method not found
1024 cost_method_not_found_except EXCEPTION;
1025
1026 BEGIN
1027 -- The From date is at midnight for the day
1028 l_rpt_from_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_from_date));
1029 -- The to date is at 23:59:59 of that date entered.
1030 l_rpt_to_date:=TRUNC(FND_DATE.canonical_to_date(p_rpt_to_date)) + (86399/86400);
1031
1032
1033 -- Open Corsor for processing
1034 v_CursorID := DBMS_SQL.OPEN_CURSOR;
1035
1036 --Bug # 4912772 Performance issue in the dynamic query resolved by restructuring the query
1037
1038 v_select_clause:=NULL;
1039 v_from_clause:=NULL;
1040 v_where_clause:=NULL;
1041 v_order_by:=NULL;
1042 v_final_query:=NULL;
1043
1044 -- If one of the ranges for a subinventory is missing then retrieve the min and max values
1045 if p_subinv_from is NULL and p_subinv_to is NOT NULL then
1046 SELECT min(secondary_inventory_name)
1047 INTO subinv_min
1048 FROM MTL_SECONDARY_INVENTORIES
1049 WHERE ORGANIZATION_ID = p_org_id;
1050 end if;
1051
1052 if p_subinv_from is NOT NULL and p_subinv_to is NULL then
1053 SELECT max(secondary_inventory_name)
1054 INTO subinv_max
1055 FROM MTL_SECONDARY_INVENTORIES
1056 WHERE ORGANIZATION_ID = p_org_id;
1057 end if;
1058
1059 -- Bug#3147073 : exclude expense items
1060 v_select_clause := 'SELECT distinct MSI.Inventory_Item_Id
1061 , MSI.Primary_UOM_Code
1062 , MSI.concatenated_segments
1063 , MSI.description
1064 , ood.organization_name
1065 , gsob.currency_code ';
1066
1067 v_from_clause := ' FROM MTL_SYSTEM_ITEMS_KFV MSI
1068 , gl_sets_of_books gsob
1069 , org_organization_definitions ood
1070 , MTL_MATERIAL_TRANSACTIONS MTX ';
1071
1072 v_where_clause := ' WHERE MSI.Organization_ID=:org_id
1073 AND ood.organization_id = MSI.Organization_id
1074 AND ood.set_of_books_id = gsob.set_of_books_id
1075 AND msi.inventory_asset_flag = ''Y''
1076 AND MSI.Inventory_Item_ID = MTX.INVENTORY_ITEM_ID
1077 AND MTX.ORGANIZATION_ID= :org_id
1078 AND MTX.COSTED_FLAG is null
1079 AND MTX.TRANSACTION_DATE BETWEEN :from_date AND :to_date ';
1080
1081 --main order by clause
1082 v_order_by := 'ORDER BY MSI.INVENTORY_ITEM_ID';
1083
1084 if p_subinv_from is NOT NULL or p_subinv_to is NOT NULL then
1085 v_where_clause := v_where_clause || ' AND MTX.SUBINVENTORY_CODE >= :subinv_from
1086 AND MTX.SUBINVENTORY_CODE <= :subinv_to ';
1087 end if;
1088
1092 , MTL_CATEGORY_SETS MCS
1089 if p_category_from is NOT NULL and p_category_to is NOT NULL and p_category_set_id is NOT NULL
1090 then
1091 v_from_clause := v_from_clause || ', MTL_ITEM_CATEGORIES MIC
1093 , MTL_CATEGORIES_KFV MCK ';
1094
1095 v_where_clause := v_where_clause || ' AND MIC.ORGANIZATION_ID = :org_id
1096 AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
1097 AND MCS.CATEGORY_SET_ID = :category_set_id
1098 AND MCK.CATEGORY_ID = MIC.category_id
1099 AND MCK.concatenated_segments between :category_from and :category_to
1100 AND MIC.inventory_item_id = MSI.Inventory_Item_ID ';
1101 end if;
1102
1103 if p_abc_group_id is not null and p_abc_class_id is not null then
1104 v_from_clause := v_from_clause || ', MTL_ABC_CLASSES MAC
1105 , MTL_ABC_ASSIGNMENTS MAA
1106 , MTL_ABC_ASSIGNMENT_GROUPS MAG ';
1107
1108 v_where_clause := v_where_clause || ' AND MAC.ABC_CLASS_ID = MAA.ABC_CLASS_ID
1109 and MAA.ASSIGNMENT_GROUP_ID = MAG.ASSIGNMENT_GROUP_ID
1110 and MAG.ASSIGNMENT_GROUP_ID = :abc_group_id
1111 and MAC.ORGANIZATION_ID = :org_id
1112 and MAC.ABC_CLASS_ID = :abc_class_id
1113 AND MAA.INVENTORY_ITEM_ID = MSI.Inventory_Item_ID ';
1114
1115 if p_subinv_from is NOT NULL or p_subinv_to is not null then
1116 v_where_clause := v_where_clause || ' AND MAG.SECONDARY_INVENTORY >= :subinv_from
1117 AND MAG.SECONDARY_INVENTORY <= :subinv_to ' ;
1118 end if;
1119 else if p_abc_class_id is not null then
1120 v_from_clause := v_from_clause || ', MTL_ABC_CLASSES MAC
1121 , MTL_ABC_ASSIGNMENTS MAA ';
1122 v_where_clause := v_where_clause || ' AND MAC.ABC_CLASS_ID = MAA.ABC_CLASS_ID
1123 and MAC.ORGANIZATION_ID = :org_id
1124 and MAC.ABC_CLASS_ID = :abc_class_id
1125 AND MAA.INVENTORY_ITEM_ID = MSI.Inventory_Item_ID ';
1126 end if;
1127 end if;
1128
1129 if p_item_from_code is NOT NULL and p_item_to_code IS NOT NULL then
1130 v_where_clause := v_where_clause || ' AND MSI.Concatenated_Segments BETWEEN :item_from_code AND :item_to_code ';
1131 end if;
1132
1133
1134 v_final_query := v_select_clause || v_from_clause || v_where_clause || v_order_by;
1135
1136 --Parse the query
1137 DBMS_SQL.PARSE(v_cursorID, v_final_query, DBMS_SQL.V7);
1138
1139 -- Not needed as temporary table
1140 -- DELETE FROM CST_MGD_MSTR_BOOK_TEMP;
1141
1142 --bind the input variables
1143 DBMS_SQL.BIND_VARIABLE(v_cursorID,':org_id',p_org_id);
1144 DBMS_SQL.BIND_VARIABLE(v_cursorID,':to_date',l_rpt_to_date);
1145 DBMS_SQL.BIND_VARIABLE(v_cursorID,':from_date',l_rpt_from_date);
1146
1147 --for selection of items in a subinventory
1148 if p_subinv_from is not null then
1149 DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_from',p_subinv_from);
1150 subinv_from := p_subinv_from;
1151 else if p_subinv_to is not null then
1152 DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_from',subinv_min);
1153 subinv_from := subinv_min;
1154 end if;
1155 end if;
1156
1157 if p_subinv_to is not null then
1158 DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_to',p_subinv_to);
1159 subinv_to := p_subinv_to;
1160 else if p_subinv_from is not null then
1161 DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_to',subinv_max);
1162 subinv_to := subinv_max;
1163 end if;
1164 end if;
1165
1166 --for selection of items in a category
1167 if p_category_from is NOT NULL and p_category_to is NOT NULL and p_category_set_id is NOT NULL
1168 then
1169 DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_from',p_category_from);
1170 DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_to',p_category_to);
1171 DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_set_id',p_category_set_id);
1172 end if;
1173
1174
1175 --for selection of items in a abc_class
1176 if p_abc_class_id is NOT NULL then
1177 DBMS_SQL.BIND_VARIABLE(v_cursorID,':abc_class_id',p_abc_class_id);
1178 end if;
1179
1180 if p_abc_group_id is NOT NULL then
1181 DBMS_SQL.BIND_VARIABLE(v_cursorID,':abc_group_id',p_abc_group_id);
1182 end if;
1183
1184 if p_item_from_code is NOT NULL and p_item_to_code IS NOT NULL then
1185 DBMS_SQL.BIND_VARIABLE(v_cursorID,':item_from_code',p_item_from_code);
1186 DBMS_SQL.BIND_VARIABLE(v_cursorID,':item_to_code',p_item_to_code);
1187 end if;
1188
1189 --define the output variables
1190 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, l_item_id);
1191 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 2, l_uom_code,3);
1192 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 3, d_item_code,40);
1193 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 4, d_item_desc,240);
1194 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 5, d_org_name,240);
1195 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 6, d_currency_code,15);
1196
1197 --execute the sql statement we don't care about the return value
1198 v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
1199
1200
1201 -- Not needed as the On Hand Qty Does not rely on the MTL_PER_CLOSE_DTLS table
1202 -- Get_Acct_Period_ID_invmbk
1203 -- ( p_org_id => p_org_id
1204 -- , p_rpt_from_date => p_rpt_from_date
1205 -- , p_rpt_to_date => p_rpt_to_date
1206 -- , x_rpt_from_acct_per_id => l_rpt_from_acct_per_id
1210 -- Get costing method of organization
1207 -- , x_rpt_to_acct_per_id => l_rpt_to_acct_per_id
1208 -- );
1209
1211 OPEN get_cost_method_cur(p_org_id);
1212 FETCH get_cost_method_cur
1213 INTO l_primary_cost_method;
1214
1215 IF get_cost_method_cur%NOTFOUND THEN
1216 raise cost_method_not_found_except;
1217 END IF;
1218
1219 LOOP
1220
1221 -- fetch the rows and also check for exit condition
1222
1223 IF DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 THEN
1224 EXIT;
1225 END IF;
1226
1227 --retrieve the rows from the buffer into PL/SQL variables
1228 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_item_id);
1229 DBMS_SQL.COLUMN_VALUE(v_cursorID,2,l_uom_code);
1230 DBMS_SQL.COLUMN_VALUE(v_cursorID,3,d_item_code);
1231 DBMS_SQL.COLUMN_VALUE(v_cursorID,4,d_item_desc);
1232 DBMS_SQL.COLUMN_VALUE(v_cursorID,5,d_org_name);
1233 DBMS_SQL.COLUMN_VALUE(v_cursorID,6,d_currency_code);
1234
1235 -- First re initialize local var
1236 l_item_ohq := 0 ;
1237 l_item_trx_qty := 0;
1238 l_per_begin_qty := 0;
1239
1240 -- First get the Acutual On Hand Qty for the Ite, Org_id combination :
1241 -- Bug#2576310 to add the sub inventory range the where condition
1242 -- bug#3147073 : exclude non asset subinventories
1243 SELECT NVL(SUM(transaction_quantity),0)
1244 INTO l_item_ohq
1245 FROM mtl_onhand_quantities
1246 WHERE inventory_item_id = l_item_id
1247 AND organization_id = p_org_id
1248 AND subinventory_code BETWEEN nvl(p_subinv_from,subinventory_code)
1249 AND nvl(p_subinv_to,subinventory_code)
1250 AND subinventory_code not in
1251 ( SELECT secondary_inventory_name
1252 FROM mtl_secondary_inventories
1253 WHERE organization_id = p_org_id
1254 AND asset_inventory = 2 ) ;
1255
1256 -- Get the qty between NOW and the P_FROM_DATE
1257 -- Bug#2198569 to excluded wip transactions
1258 -- transaction action id: 24 for wip cost update
1259 -- transaction action id: 30 for wip scrap transaction
1260 -- Bug#2576310 to add the sub inventory range the where condition
1261 -- Bug#2865534 fix: get only regular stock and exclude consigned stock
1262 -- for regular stock: organization_id is equal to owning_organization_id
1263 -- for consigned stock: organization_id is the inventory organization
1264 -- and owning_organization_id is the supplier organization.
1265 -- owning_tp_type will be 1 for consigned transaction
1266 -- bug#3118846 fix: exclude TRX from NON Qty tracked subinventories
1267 SELECT NVL(SUM(mmt.Primary_Quantity), 0)
1268 INTO l_item_trx_qty
1269 FROM MTL_MATERIAL_TRANSACTIONS mmt
1270 WHERE mmt.ORGANIZATION_ID = p_org_id
1271 AND mmt.INVENTORY_ITEM_ID = l_item_id
1272 AND mmt.SUBINVENTORY_CODE BETWEEN nvl(p_subinv_from, mmt.subinventory_code)
1273 AND nvl(p_subinv_to,mmt.subinventory_code)
1274 AND ( mmt.SUBINVENTORY_CODE is NULL
1275 OR
1276 mmt.SUBINVENTORY_CODE =
1277 ( SELECT SECONDARY_INVENTORY_NAME
1278 FROM MTL_SECONDARY_INVENTORIES
1279 WHERE SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
1280 AND ORGANIZATION_ID = mmt.ORGANIZATION_ID
1281 AND QUANTITY_TRACKED = 1
1282 AND ASSET_INVENTORY = 1 )
1283 )
1284 AND mmt.transaction_id NOT IN
1285 (SELECT mmt1.transaction_id
1286 FROM MTL_MATERIAL_TRANSACTIONS mmt1
1287 WHERE mmt1.organization_id = p_org_id
1288 AND mmt1.inventory_item_id = l_item_id
1289 AND mmt1.SUBINVENTORY_CODE
1290 BETWEEN nvl(p_subinv_from, mmt1.subinventory_code)
1291 AND nvl(p_subinv_to,mmt1.subinventory_code)
1292 AND mmt1.transaction_source_type_id = 5
1293 AND mmt1.transaction_action_id = 24
1294 AND nvl(mmt1.owning_tp_type,2) = 1
1295 )
1296 AND mmt.Transaction_action_id <> 30
1297 AND nvl(mmt.owning_tp_type,2) <> 1
1298 AND mmt.TRANSACTION_DATE between l_rpt_from_date
1299 AND sysdate;
1300
1301 -- On Hand Qty to begin = Actual On Hand MINUS sum of Trx qty form Now to begin date
1302 l_per_begin_qty := l_item_ohq - l_item_trx_qty ;
1303
1304 l_begin_infl_adj := 0;
1305 l_final_infl_adj := 0;
1306 l_final_qty := 0;
1307
1308
1309 IF l_final_qty = 0
1310 THEN
1311 l_unit_infl_adj := 0;
1312 ELSE
1313 l_unit_infl_adj := l_final_infl_adj/l_final_qty;
1314 END IF;
1315
1316 begin
1317 -- Bug#2576310 fix subinventory code to add in the WHERE condition
1318 Select NVL(mmt.PRIOR_COST,mmt.ACTUAL_COST)
1319 INTO l_item_unit_cost
1320 FROM MTL_MATERIAL_TRANSACTIONS mmt
1321 WHERE mmt.TRANSACTION_ID = (SELECT min(transaction_id)
1322 From Mtl_Material_Transactions
1323 Where Organization_Id = p_org_id
1324 And Inventory_Item_Id = l_item_id
1325 And Transaction_date =
1326 (Select min(Transaction_date)
1327 From Mtl_Material_Transactions
1328 Where Organization_Id = p_org_id
1329 And Transaction_action_id NOT IN (24,30)
1330 AND nvl(owning_tp_type, 2) <> 1
1331 And Transaction_date between l_rpt_from_date and l_rpt_to_date
1332 And Inventory_Item_Id = l_item_id
1333 AND subinventory_code BETWEEN nvl(p_subinv_from, subinventory_code)
1334 AND nvl(p_subinv_to, subinventory_code) ))
1335 AND ( mmt.SUBINVENTORY_CODE is NULL
1336 OR
1337 mmt.SUBINVENTORY_CODE =
1338 ( SELECT SECONDARY_INVENTORY_NAME
1339 FROM MTL_SECONDARY_INVENTORIES
1340 WHERE SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
1341 AND ORGANIZATION_ID = mmt.ORGANIZATION_ID
1342 AND QUANTITY_TRACKED = 1
1343 AND ASSET_INVENTORY = 1 )
1344 )
1345 AND mmt.transaction_id NOT IN
1346 (SELECT mmt1.transaction_id
1347 FROM MTL_MATERIAL_TRANSACTIONS mmt1
1348 WHERE mmt1.organization_id = p_org_id
1349 AND mmt1.inventory_item_id = l_item_id
1350 AND nvl(mmt1.owning_tp_type,2) = 1
1351 AND mmt1.transaction_date
1352 BETWEEN l_rpt_from_date and l_rpt_to_date
1353 AND mmt1.subinventory_code
1354 BETWEEN nvl(p_subinv_from, mmt1.subinventory_code) AND nvl(p_subinv_to, mmt1.subinventory_code)
1355 AND mmt1.transaction_source_type_id = 5
1356 AND mmt1.transaction_action_id = 24
1357 )
1358 AND mmt.Transaction_action_id <> 30
1359 AND nvl(mmt.owning_tp_type,2) <> 1;
1360
1361 exception
1362 when no_data_found then
1363 null;
1364 end;
1365
1366 if l_item_unit_cost is NULL then l_item_unit_cost:=0; end if;
1367
1368
1369 Get_Item_Txn_Info
1370 ( p_org_id => p_org_id
1371 , p_item_id => l_item_id
1372 , p_uom_code => l_uom_code
1373 , p_item_code => d_item_code
1374 , p_item_desc => d_item_desc
1375 , p_org_name => d_org_name
1376 , p_currency_code => d_currency_code
1377 , p_subinv_from => subinv_from
1378 , p_subinv_to => subinv_to
1379 , p_acct_period_id => NULL
1380 , p_per_first_txn_date => p_rpt_from_date
1381 , p_per_last_txn_date => p_rpt_to_date
1382 , p_item_unit_cost => l_item_unit_cost
1383 , p_primary_cost_method => l_primary_cost_method
1384 , p_item_init_qty => l_per_begin_qty
1385 , p_item_init_infl => 0
1386 , p_item_unit_infl_adj => 0
1387 , x_rpt_item_tbl_rec => l_rpt_item_tbl_rec
1388 );
1389
1390
1391
1392 l_index := NVL(l_rpt_item_tbl_rec.FIRST, 0);
1393 IF l_index > 0
1394 THEN
1395 LOOP
1396
1397 Insert_Rpt_Data
1398 ( p_rpt_item_rec => l_rpt_item_tbl_rec(l_index)
1399 );
1400 EXIT WHEN l_index = l_rpt_item_tbl_rec.LAST;
1401 l_index := l_rpt_item_tbl_rec.NEXT(l_index);
1402 END LOOP;
1403 END IF;
1404
1405 END LOOP;
1406
1407 EXCEPTION
1408 WHEN cost_method_not_found_except THEN
1409 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1410 THEN
1411 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1412 , 'Create_Inv_Msbk_Rpt' || ' Cost Method Not Found'
1413 );
1414 END IF;
1415 RAISE;
1416
1417 WHEN OTHERS THEN
1418 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1419 THEN
1420 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1421 , 'Create_Inv_Msbk_Rpt'
1422 );
1423 END IF;
1424 RAISE;
1425
1426 END Create_Inv_Msbk_Rpt;
1427
1428 END CST_MGD_MSTR_BOOK_RPT;
[Home] [Help]