1 PACKAGE BODY CST_MGD_MSTR_BOOK_RPT AS
2 -- $Header: CSTGMBKB.pls 120.21.12020000.3 2012/10/01 10:44:21 nmogili 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 --| *** Italy / China enhancements *** |
22 --| get_break_by |
23 --| beforereport |
24 --| get_abc_group_name |
25 --| getledger_name |
26 --| --get_category_set_name |
27 --| get_shipment_num |
28 --| get_include_cost |
29 --| get_waybill |
30 --| get_po_number |
31 --| get_detail_level |
32 --| |
33 --| HISTORY |
34 --| 07/16/1999 ksaini Created from CSTRIADB.pls |
35 --| 08/30/1999 ksaini Remove ABC Class Name and Assignment |
36 --| Group name from the temp table |
37 --| 09/06/1999 ksaini Modified Code for Subinventory Range |
38 --| 08/19/2001 vjavli Bug#1917957 to fix the wrong begin |
39 --| quantity |
40 --| 10/04/2001 vjavli Cost group id NULL created as part of |
41 --| bug#1474753 fix; this is to support |
42 --| CST_MGD_INFL_ADJUSTMENT_PVT.Get_Period_|
43 --| End_Avg_Cost |
44 --| 10/20/2001 fdubois Two changes made :a) Begin On Hand Qty |
45 --| cannot be derived from |
46 --| MTL_PER_CLOSE_DTLS because the table is|
47 --| only used for Avg costing. Also the |
48 --| datte_to and date_from can fall within |
49 --| open periods. |
50 --| b) Date_to changed to extend up to |
51 --| 23:59:59 of the day. |
52 --| Bug#2011340 |
53 --| 01/28/2002 vjavli WIP transactions should be excluded |
54 --| Bug#2198569 |
55 --| 01/30/2002 vjavli excluded in procedures get_offset_qty |
56 --| get_item_txn_info |
57 --| 07/11/2002 vjavli Bug#2433926 fix no validation for |
58 --| transaction type disable date |
59 --| 09/18/2002 vjavli Bug#2576310 to consider the sub |
60 --| inventories while getting the begin |
61 --| onhand quantity |
62 --| 19/11/2002 tsimmond UTF8 : changed org_name size to 240 |
63 --| 02/14/2003 vjavli Bug#2799104: Average Cost Update Trn |
64 --| should be shown; filter logic of WIP |
65 --| transaction modified for correction |
66 --| 03/31/2003 vjavli Bug#2865534 fix: Cosigned inventory |
67 --| transactions to be eliminated |
68 --| owning_tp_type = 1 for consigned trn |
69 --| 05/23/2003 vjavli Bug#2904882 fix: transaction_id added |
70 --| temporary table inorder to get correct |
71 --| sort by transaction_date, |
72 --| transaction_id |
73 --| 05/29/2003 vjavli Bug#2977020 fix: ACU transactions not |
74 --| being displayed - reason: primary_qty |
75 --| is 0 ; verified in internal system for |
76 --| 3 types of ACUs. In all 3 types the qty|
77 --| is not null |
78 --| Found that percentage and new avg cost |
79 --| to be supported |
80 --| primary_qty <> 0 condition removed in |
81 --| get_item_txn_info |
82 --| 06/10/2003 vjavli regression from bug#2904882 fix: sort |
83 --| trunc(transaction_date), transaction_id|
84 --| get_item_txn_info cursor added with |
85 --| order by trunc(transaction_date), |
86 --| transaction_id bug#3002073 fix |
87 --| 06/22/2003 vjavli Bug#3013597 fix: cost type of the |
88 --| organization is to be verified. If cost|
89 --| type is Standard then Begin Unit Cost |
90 --| will be actual cost of very first txn |
91 --| and End Unit Cost will be actual cost |
92 --| of last txn. If Cost Type is Average, |
93 --| LIFO, FIFO then Begin Unit Cost will be|
94 --| prior cost of very first txn and End |
95 --| Unit Cost will be new cost of last txn |
96 --| in the report date range |
97 --| 06/22/2003 vjavli NOTE: Create_Infl_Adj_Rpt procedure |
98 --| removed since it is not invoked by any |
99 --| of the inflation reports |
100 --| 09/09/2003 fdubois bug#3118846 : exclude NON qty tracked |
101 --| subinventory transactions as the ON |
102 --| HAND qty in Inventory does NOT account |
103 --| for these quantities. |
104 --| 09/10/2003 fdubois code cleaning : removing unused API |
105 --| get_offset_qty and related code (dead |
106 --| code) |
107 --| 10/02/2003 fdubois bug#3147073 :exclude non asset items |
108 --| and non asset subinventory and |
109 --| exclude expense items |
110 --| 05/12/2004 vjavli Performance bug fix as in bug#2862480 |
111 --| for 11.5.9. Get_Acct_Period_Id and |
112 --| Get_Acct_Period_Id_invmbk modified |
113 --| NOTE:Get_Acct_Period_id and Get_Acct_ |
114 --| Period_Id_invmbk are not used anywhere |
115 --| 02/17/2006 vmutyala Bug # 4086259 Added Creation_date to |
116 --| CST_MGD_MSTR_BOOK_TEMP |
117 --| 02/24/2006 vmutyala Bug # 4912772 Performance issue in the |
118 --| dynamic query in Create_Inv_Msbk_Rpt is|
119 --| resolved by restructuring the query |
120 --| 10/27/2008 vjavli FP Bug 7458643 fix:Standard cost update|
121 --| transaction should be displayed with |
122 --| correct cost(new cost minus prior cost)|
123 --| * primary_quantity (onhand qty at that |
124 --| moment). l_primary_qty := 0; |
125 --| l_total_cost := |
126 --| (l_item_txn_info.new_cost - |
127 --| l_item_txn_info.prior_cost) * |
128 --| l_item_txn_info.primary_quantity; |
129 --| get_item_txn_info proc modified |
130 --|12/01/2008 vjavli Bug 7458643 fix: Standard cost Update |
131 --| found that primary_quantity is 0; |
132 --| We have to use quantity_adjusted |
133 --| Also, actual_cost will be NULL |
134 --| Even in Average Cost update, better to |
135 --| use quantity_adjusted instead of PQ |
136 --| quantity_adjusted will always has value|
137 --| In ACU, priorcost,newcost,actcost will |
138 --| will be NOT NULL |
139 --| |
140 --|-----------------------------------------------------------------------|
141 --| 05/27/2009 vputchal Italy and China Enhancements from India |
142 --| localization team Package re-designed |
143 --| The following functions added: |
144 --| get_break_by,beforereport |
145 --| get_abc_group_name,getledger_name |
146 --| --get_category_set_name,get_shipment_num|
147 --| get_include_cost, get_waybill |
148 --| get_po_number,get_detail_level |
149 --| 09/07/2009 ppandit Changed datatypes for P_LEGAL_ENTITY, |
150 --| P_LEDGER_ID and P_INVENTORY_ORG in Italy|
151 --| China Enhancements from SSI. Added |
152 --| functions get_date_from and get_date_to,|
153 --| improved get_po_number. Used REF |
154 --| CURSOR for table insertion logic. Added |
155 --| p_dummy, p_all_or_single, |
156 --| get_org_details |
157 --| 09/16/2009 ppandit Added following functions for XML |
158 --| elements |
159 --| get_inv_org, |
160 --| get_subinv_org_from, |
161 --| get_subinv_org_to, |
162 --| get_category_set_from, |
163 --| get_category_set_to, |
164 --| get_category_from, |
165 --| get_category_to, |
166 --| get_item_from, |
167 --| get_item_to, |
168 --| get_abc_class, |
169 --| get_break_by_desc, |
170 --| get_all_or_one, |
171 --| get_icx_date, |
172 --| get_page_penultimate, |
173 --| get_suborg_details |
174 --| 09/29/2009 ppandit Added get_begin_columns, get_end_columns|
175 --| 10/05/2009 ppandit Added get_summ_beg_cols, |
176 --| get_summ_end_cols |
177 --| 11/11/2009 ppandit Removed DISTINCT from summary functions |
178 --+=======================================================================+
179 --===================
180 -- VARIABLES
181 --===================
182
183 lc_sub_inv_max VARCHAR2 (10); -- Added for Italy China Enhancements
184 lc_sub_inv_min VARCHAR2 (10); -- Added for Italy China Enhancements
185 ln_inv_org NUMBER; -- Added for Italy China Enhancements
186
187 --===================
188 -- TYPES
189 --===================
190 TYPE report_rec_type IS RECORD (
191 transaction_id NUMBER
192 ,organization_id NUMBER
193 ,inventory_item_id NUMBER
194 ,uom_code VARCHAR2(3) -- added for inv book
195 ,item_code VARCHAR2(40) -- added for inv book
196 ,item_desc VARCHAR2(240) -- added for inv book
197 ,org_name VARCHAR2(240) -- added for inv book
198 ,currency_code VARCHAR2(15) -- added for inv book
199 ,txn_source VARCHAR2(30) -- added for inv book
200 ,txn_date DATE
201 ,txn_type VARCHAR2(80)
202 ,txn_ini_qty NUMBER
203 ,txn_ini_unit_cost NUMBER
204 ,txn_ini_h_total_cost NUMBER
205 ,txn_ini_adj_total_cost NUMBER
206 ,txn_qty NUMBER
207 ,txn_unit_cost NUMBER
208 ,txn_h_total_cost NUMBER
209 ,txn_adj_total_cost NUMBER
210 ,txn_fnl_qty NUMBER
211 ,txn_fnl_unit_cost NUMBER
212 ,txn_fnl_h_total_cost NUMBER
213 ,txn_fnl_adj_total_cost NUMBER
214 ,creation_date DATE
215 ,sub_inv_organization_id NUMBER -- Added by ppandit for Italy and China Enhancements
216 ,subinventory_code VARCHAR2 (10) -- Added by ppandit for Italy and China Enhancements
217 );
218
219 TYPE report_tbl_rec_type IS TABLE OF report_rec_type INDEX BY BINARY_INTEGER;
220
221 --===================
222 -- CONSTANTS
223 --===================
224 G_PKG_NAME CONSTANT VARCHAR2 (30) := 'CST_MGD_MSTR_BOOK_RPT';
225 GC_TXT_ISSUE VARCHAR2 (10) := 'Issue';
226 GC_TXT_RECEIPT VARCHAR2 (10) := 'Receipt';
227 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_MGD_INV_MASTER_BOOK';
228
229 --===================
230 -- GLOBAL VARIABLES
231 --===================
232 g_txn_cost_exc EXCEPTION;
233 g_msg VARCHAR2(255);
234
235 --===================
236 -- PRIVATE PROCEDURES
237 --===================
238
239 --========================================================================
240 -- PROCEDURE : Insert_Rpt_Data PRIVATE
241 -- PARAMETERS: p_rpt_item_rec Kardex report data for one row
242 -- COMMENT :
243 -- EXCEPTIONS: OTHERS
244 --========================================================================
245 PROCEDURE insert_rpt_data (p_rpt_item_rec IN report_rec_type)
246 IS
247 BEGIN
248
249 INSERT INTO cst_mgd_mstr_book_temp (
250 transaction_id
251 ,organization_id
252 ,inventory_item_id
253 ,uom_code -- added for inv book
254 ,item_code
255 ,item_desc
256 ,org_name
257 ,currency_code
258 ,txn_source -- added for inv book
259 ,txn_date
260 ,txn_type
261 ,txn_ini_qty
262 ,txn_ini_unit_cost
263 ,txn_ini_h_total_cost
264 ,txn_ini_adj_total_cost
265 ,txn_qty
266 ,txn_unit_cost
267 ,txn_h_total_cost
268 ,txn_adj_total_cost
269 ,txn_fnl_qty
270 ,txn_fnl_unit_cost
271 ,txn_fnl_h_total_cost
272 ,txn_fnl_adj_total_cost
273 ,creation_date
274 ,sub_inv_organization_id -- Added by ppandit for Italy and China Enhancements
275 ,subinventory_code -- Added by ppandit for Italy and China Enhancements
276 )
277 VALUES (
278 p_rpt_item_rec.transaction_id
279 ,p_rpt_item_rec.organization_id
280 ,p_rpt_item_rec.inventory_item_id
281 ,p_rpt_item_rec.uom_code -- added for inv book
282 ,p_rpt_item_rec.item_code -- added for inv book
283 ,p_rpt_item_rec.item_desc -- added for inv book
284 ,p_rpt_item_rec.org_name -- added for inv book
285 ,p_rpt_item_rec.currency_code -- added for inv book
286 ,p_rpt_item_rec.txn_source -- added for inv book
287 ,p_rpt_item_rec.txn_date
288 ,p_rpt_item_rec.txn_type
289 ,p_rpt_item_rec.txn_ini_qty
290 ,p_rpt_item_rec.txn_ini_unit_cost
291 ,p_rpt_item_rec.txn_ini_h_total_cost
292 ,p_rpt_item_rec.txn_ini_adj_total_cost
293 ,p_rpt_item_rec.txn_qty
294 ,p_rpt_item_rec.txn_unit_cost
295 ,p_rpt_item_rec.txn_h_total_cost
296 ,p_rpt_item_rec.txn_adj_total_cost
297 ,p_rpt_item_rec.txn_fnl_qty
298 ,p_rpt_item_rec.txn_fnl_unit_cost
299 ,p_rpt_item_rec.txn_fnl_h_total_cost
300 ,p_rpt_item_rec.txn_fnl_adj_total_cost
301 ,p_rpt_item_rec.creation_date
302 ,p_rpt_item_rec.sub_inv_organization_id -- Added by ppandit for Italy and China Enhancements
303 ,p_rpt_item_rec.subinventory_code -- Added by ppandit for Italy and China Enhancements
304 );
305
306 EXCEPTION
307
308 WHEN OTHERS THEN
309 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
310 THEN
311 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
312 , 'Insert_Rpt_Data'
313 );
314 END IF;
315 --RAISE; -- Commented by ppandit for Italy and China Enhancements
316
317 END insert_rpt_data;
318
319 --========================================================================
320 -- PROCEDURE : get_acct_period_id_invmbk PRIVATE
321 -- PARAMETERS: p_org_id Organization ID
322 -- p_rpt_from_date Report start date
323 -- P_rpt_to_date Report end date
324 -- x_rpt_from_acct_per_id Report start account period ID
325 -- x_rpt_to_acct_per_id Report end account period ID
326 -- COMMENT : Get the account period IDs for user defined reporting
327 -- period
328 -- EXCEPTIONS:
329 --========================================================================
330 PROCEDURE get_acct_period_id_invmbk (
331 p_org_id IN NUMBER
332 ,p_rpt_from_date IN VARCHAR2
333 ,p_rpt_to_date IN VARCHAR2
334 ,x_rpt_from_acct_per_id OUT NOCOPY NUMBER
335 ,x_rpt_to_acct_per_id OUT NOCOPY NUMBER
336 )
337 IS
338 l_rpt_from_acct_per_id NUMBER;
339 l_rpt_to_acct_per_id NUMBER;
340 l_rpt_from_date DATE;
341 l_rpt_to_date DATE;
342
343 -- Cursor to retrieve from accounting period id
344 CURSOR from_acct_period_cur(c_rpt_from_date DATE)
345 IS
346 SELECT f.acct_period_id
347 FROM org_acct_periods f
348 WHERE f.organization_id = p_org_id
349 AND f.period_start_date <= c_rpt_from_date
350 AND f.schedule_close_date >= c_rpt_from_date;
351
352 -- Cursor to retrieve to accounting period id
353 CURSOR to_acct_period_cur(c_rpt_to_date DATE)
354 IS
355 SELECT t.acct_period_id
356 FROM org_acct_periods t
357 WHERE t.organization_id = p_org_id
358 AND t.period_start_date <= c_rpt_to_date
359 AND t.schedule_close_date >= c_rpt_to_date;
360
361 -- Exception
362 acct_period_not_found_exc EXCEPTION;
363
364 BEGIN
365 l_rpt_from_date := TRUNC (FND_DATE.canonical_to_date (p_rpt_from_date));
366 l_rpt_to_date := TRUNC (FND_DATE.canonical_to_date (p_rpt_to_date));
367
368 -- Get from account period id
369 OPEN from_acct_period_cur (TO_DATE (p_date_from, 'YYYY/MM/DD HH24:MI:SS'));
370 FETCH from_acct_period_cur
371 INTO x_rpt_from_acct_per_id;
372
373 IF from_acct_period_cur%NOTFOUND THEN
374 RAISE acct_period_not_found_exc;
375 END IF;
376 CLOSE from_acct_period_cur;
377
378 -- Get to account period id
379 OPEN to_acct_period_cur(TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400)); -- Changed by ppandit for using params directly, Italy China Enhancements
380 FETCH to_acct_period_cur
381 INTO x_rpt_to_acct_per_id;
382
383 IF to_acct_period_cur%NOTFOUND THEN
384 RAISE acct_period_not_found_exc;
385 END IF;
386 CLOSE to_acct_period_cur;
387
388
389 EXCEPTION
390
391 WHEN acct_period_not_found_exc THEN
392 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ACCT_PER_ID_INVBK');
393
394 FND_MSG_PUB.Add;
395 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
396 THEN
397 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
398 , 'Get_Acct_Period_ID_invmbk'
399 );
400 END IF;
401 RAISE;
402
403
404 WHEN OTHERS THEN
405 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
406 THEN
407 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
408 , 'Get_Acct_Period_ID_invmbk'
409 );
410 END IF;
411 RAISE;
412
413 END Get_Acct_Period_ID_invmbk;
414
415 -- =======================================================================
416 -- Added for Italy and China Requirements
417 -- =======================================================================
418
419 --========================================================================
420 -- FUNCTION : BEFOREREPORT PUBLIC
421 -- PARAMETERS: none
422 --Return : Boolean
423 -- COMMENT : This is called from Inventory Master Book Report from XML
424 -- EXCEPTIONS:INVALID_DATE_RANGE_EXCEPT, OTHERS
425 --========================================================================
426 FUNCTION beforereport RETURN BOOLEAN IS
427 INVALID_DATE_RANGE_EXCEPT EXCEPTION;
428
429 l_resp_id NUMBER:= FND_PROFILE.VALUE('RESP_ID');
430 /* CURSOR lcu_inv_org -- Added for Italy China Enhancements
431 IS
432 SELECT HOU.organization_id
433 FROM hr_organization_units HOU
434 ,mtl_parameters_view MPV
435 ,xle_firstparty_information_v XFI
436 WHERE MPV.master_organization_id = p_legal_entity
437 AND HOU.organization_id = MPV.organization_id
438 AND XFI.legal_entity_id = MPV.master_organization_id;*/
439 /*Added for bug 14627818*/
440 CURSOR lcu_inv_org
441 IS
442 SELECT mp.organization_id
443 FROM mtl_parameters mp,
444 cst_organization_definitions cod
445 WHERE mp.organization_id = cod.organization_id
446 AND cod.legal_entity = p_legal_entity
447 AND EXISTS (
448 SELECT 1
449 FROM org_access ACC
450 WHERE acc.organization_id = MP.organization_id
451 AND acc.responsibility_id = l_resp_id
452 );
453
454 CURSOR lcu_sub_inv_org (ln_org IN NUMBER) -- Added for Italy China Enhancements
455 IS
456 SELECT MIN(MSI.secondary_inventory_name)
457 ,MAX(MSI.secondary_inventory_name)
458 FROM mtl_secondary_inventories MSI
459 WHERE MSI.organization_id = ln_org;
460 BEGIN
461 -- P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
462 GD_RPT_DATE_FROM := TRUNC(FND_DATE.canonical_to_date(p_date_from));
463 GD_RPT_DATE_TO := TRUNC(FND_DATE.canonical_to_date(p_date_to)) + (86399 / 86400);
464
465 IF GD_RPT_DATE_FROM > GD_RPT_DATE_TO THEN
466 FND_MESSAGE.SET_NAME('FND', 'INVALID DATE RANGE');
467 G_MSG := FND_MESSAGE.GET;
468 RAISE INVALID_DATE_RANGE_EXCEPT;
469 END IF;
470
471 BEGIN
472 fnd_file.put_line(fnd_file.LOG, 'Responsibilty ID ' || l_resp_id);
473 fnd_file.put_line(fnd_file.LOG, 'P_LEGAL_ENTITY ' || P_LEGAL_ENTITY);
474 fnd_file.put_line(fnd_file.LOG, 'P_LEDGER_ID ' || P_LEDGER_ID);
475 fnd_file.put_line(fnd_file.LOG, 'P_INVENTORY_ORG ' || P_INVENTORY_ORG);
476 fnd_file.put_line(fnd_file.LOG, 'P_DATE_FROM ' || P_DATE_FROM);
477 fnd_file.put_line(fnd_file.LOG, 'P_DATE_TO ' || P_DATE_TO);
478 fnd_file.put_line(fnd_file.LOG, 'P_ITEM_CODE_FROM ' || P_ITEM_CODE_FROM);
479 fnd_file.put_line(fnd_file.LOG, 'P_ITEM_CODE_TO ' || P_ITEM_CODE_TO);
480 fnd_file.put_line(fnd_file.LOG, 'P_CATEGORY_SET_ID_FROM ' || P_CATEGORY_SET_ID_FROM);
481 fnd_file.put_line(fnd_file.LOG, 'P_CATEGORY_SET_ID_TO ' || P_CATEGORY_SET_ID_TO);
482 fnd_file.put_line(fnd_file.LOG, 'P_CATEGORY_FROM ' || P_CATEGORY_FROM);
483 fnd_file.put_line(fnd_file.LOG, 'P_CATEGORY_TO ' || P_CATEGORY_TO);
484 fnd_file.put_line(fnd_file.LOG, 'P_CATEGORY_STRUCTURE ' || P_CATEGORY_STRUCTURE);
485 fnd_file.put_line(fnd_file.LOG, 'P_ABC_GROUP_ID ' || P_ABC_GROUP_ID);
486 fnd_file.put_line(fnd_file.LOG, 'P_ABC_CLASS_ID ' || P_ABC_CLASS_ID);
487 fnd_file.put_line(fnd_file.LOG, 'P_BREAK_BY ' || P_BREAK_BY);
488 fnd_file.put_line(fnd_file.LOG, 'P_PAGE_NUMBER ' || P_PAGE_NUMBER);
489 fnd_file.put_line(fnd_file.LOG, 'P_FISCAL_YEAR ' || P_FISCAL_YEAR);
490 fnd_file.put_line(fnd_file.LOG, 'P_SUBINV_FROM ' || P_SUBINV_FROM);
491 fnd_file.put_line(fnd_file.LOG, 'P_SUBINV_TO ' || P_SUBINV_TO);
492 fnd_file.put_line(fnd_file.LOG, 'P_DETAIL ' || P_DETAIL);
493 fnd_file.put_line(fnd_file.LOG, 'P_INCLUDE_ITEM_COST ' || P_INCLUDE_ITEM_COST);
494 fnd_file.put_line(fnd_file.LOG, 'P_ALL_OR_SINGLE ' || P_ALL_OR_SINGLE);
495
496 /* Changed for Italy China Enhancements start, added LOOP for ALL Inventory Org parameter */
497 IF p_inventory_org IS NULL THEN
498
499 FOR rec IN lcu_inv_org
500 LOOP
501
502 ln_inv_org := rec.organization_id;
503
504 IF lcu_sub_inv_org%ISOPEN THEN
505 CLOSE lcu_sub_inv_org;
506 END IF;
507
508 OPEN lcu_sub_inv_org (ln_inv_org);
509 FETCH lcu_sub_inv_org INTO lc_sub_inv_min, lc_sub_inv_max;
510 CLOSE lcu_sub_inv_org;
511
512 CST_MGD_MSTR_BOOK_RPT.create_inv_msbk_rpt (
513 p_org_id => ln_inv_org
514 ,p_category_set_id_from => p_category_set_id_from
515 ,p_category_set_id_to => p_category_set_id_to
516 ,p_category_from => p_category_from
517 ,p_category_to => p_category_to
518 ,p_subinv_from => lc_sub_inv_min
519 ,p_subinv_to => lc_sub_inv_max
520 ,p_abc_group_id => p_abc_group_id
521 ,p_abc_class_id => p_abc_class_id
522 ,p_item_from_code => p_item_code_from
523 ,p_item_to_code => p_item_code_to
524 ,p_rpt_from_date => p_date_from
525 ,p_rpt_to_date => p_date_to
526 );
527
528 END LOOP;
529
530 IF (NVL(ln_inv_org,0)=0) THEN
531 fnd_file.put_line(fnd_file.LOG, 'NOTE** The current responsibility does not have access to any Inventory Orgs under the given Legal Entity');
532 END IF;
533
534 ELSE
535 CST_MGD_MSTR_BOOK_RPT.create_inv_msbk_rpt (
536 p_org_id => p_inventory_org
537 ,p_category_set_id_from => p_category_set_id_from
538 ,p_category_set_id_to => p_category_set_id_to
539 ,p_category_from => p_category_from
540 ,p_category_to => p_category_to
541 ,p_subinv_from => p_subinv_from
542 ,p_subinv_to => p_subinv_to
543 ,p_abc_group_id => p_abc_group_id
544 ,p_abc_class_id => p_abc_class_id
545 ,p_item_from_code => p_item_code_from
546 ,p_item_to_code => p_item_code_to
547 ,p_rpt_from_date => p_date_from
548 ,p_rpt_to_date => p_date_to
549 );
550 END IF;
551 /* Changed for Italy China Enhancements end, added LOOP for ALL Inventory Orgs */
552 END;
553
554 -- Commented by ppandit
555 /* BEGIN
556 SELECT FND_DATE.date_to_chardate (TRUNC (FND_DATE.canonical_to_date (p_date_from)))
557 INTO p_date_from_formatting
558 FROM DUAL;
559 END;
560
561 BEGIN
562 SELECT FND_DATE.date_to_chardate (TRUNC (FND_DATE.canonical_to_date (p_date_to)))
563 INTO p_date_to_formatting
564 FROM DUAL;
565 END; */
566
567 BEGIN
568 SELECT fc.extended_precision, gsob.currency_code
569 INTO gn_precision_val, gc_currency_code
570 FROM gl_sets_of_books gsob,
571 org_organization_definitions ood,
572 fnd_currencies fc
573 WHERE ood.organization_id = p_inventory_org
574 AND ood.set_of_books_id = gsob.set_of_books_id
575 AND fc.currency_code = gsob.currency_code;
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 gn_precision_val := NULL;
579 gc_currency_code := NULL;
580 END;
581 /*
582 BEGIN
583 SELECT FRV.RESPONSIBILITY_NAME,
584 FND_DATE.DATE_TO_CHARDT(FCR.REQUEST_DATE),
585 FAV.APPLICATION_NAME,
586 FU.USER_NAME
587 INTO GC_RESPONSIBILITY
588 ,GC_REQUEST_TIME
589 ,GC_APPLICATION
590 ,GC_REQUESTED_BY
591 FROM FND_CONCURRENT_REQUESTS FCR,
592 FND_RESPONSIBILITY_VL FRV,
593 FND_APPLICATION_VL FAV,
594 FND_USER FU
595 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
596 AND FCR.RESPONSIBILITY_APPLICATION_ID = FRV.APPLICATION_ID
597 AND FCR.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
598 AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
599 AND FU.USER_ID = FCR.REQUESTED_BY;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 NULL;
603 END;
604 */
605 BEGIN
606 SELECT meaning
607 INTO gc_include_cost
608 FROM fnd_lookups
609 WHERE lookup_code = p_include_item_cost
610 AND lookup_type = 'YES_NO';
611 EXCEPTION
612 WHEN NO_DATA_FOUND THEN
613 gc_include_cost := 'N';
614 END;
615
616 BEGIN
617
618 SELECT meaning
619 INTO gc_detail
620 FROM fnd_lookups
621 WHERE lookup_code = p_detail
622 AND lookup_type = 'INV_BOOK_DETAIL';
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 gc_detail := NULL;
626 END;
627
628 IF p_abc_class_id IS NOT NULL THEN
629 BEGIN
630 SELECT abc_class_name
631 INTO gc_abc_class_name
632 FROM mtl_abc_classes
633 WHERE abc_class_id = p_abc_class_id;
634 EXCEPTION
635 WHEN NO_DATA_FOUND THEN
636 gc_abc_class_name := NULL;
637 END;
638 END IF;
639
640 IF p_abc_group_id IS NOT NULL THEN
641 BEGIN
642 SELECT assignment_group_name
643 INTO gc_abc_group_name
644 FROM mtl_abc_assignment_groups
645 WHERE assignment_group_id = p_abc_group_id;
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 gc_abc_group_name := NULL;
649 END;
650 END IF;
651
652 IF p_category_set_id_from IS NOT NULL THEN
653 BEGIN
654 SELECT category_set_name
655 INTO gc_category_set_name_1
656 FROM mtl_category_sets
657 WHERE category_set_id = p_category_set_id_from; -- Changed by ppandit P_CATEGORY_SET_ID to P_CATEGORY_SET_ID_FROM
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 gc_category_set_name_1 := NULL;
661 END;
662 END IF;
663
664 IF p_category_set_id_to IS NOT NULL THEN -- Added by ppandit
665 BEGIN
666 SELECT category_set_name
667 INTO gc_category_set_name_2
668 FROM mtl_category_sets
669 WHERE category_set_id = p_category_set_id_to;
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 gc_category_set_name_2 := NULL;
673 END;
674 END IF;
675
676 RETURN (TRUE);
677
678 EXCEPTION
679 WHEN INVALID_DATE_RANGE_EXCEPT THEN
680 FND_MESSAGE.SET_NAME('FND', 'INVALID DATE RANGE');
681 RETURN (TRUE);
682
683 END beforereport;
684
685 /* Included for Italy Joint Project */
686 -- +==========================================================================+
687 -- FUNCTION: get_shipment_num
688 -- PARAMETERS:
689 -- p_transaction_id IN NUMBER
690 -- COMMENT:
691 -- This procedure is called by Inventory Master Book Report
692 -- Return: VARCHAR2
693 -- PRE-COND: none
694 -- EXCEPTIONS: none
695 -- +==========================================================================+
696 FUNCTION get_shipment_num (p_transaction_id NUMBER) RETURN VARCHAR2 IS
697 lv_shipment_num VARCHAR2(100);
698 BEGIN
699 BEGIN
700 SELECT shipment_number
701 INTO lv_shipment_num
702 FROM mtl_material_transactions
703 WHERE transaction_id = p_transaction_id;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 lv_shipment_num:=NULL;
708 END;
709 RETURN (lv_shipment_num);
710 END get_shipment_Num;
711
712 /* Included for Italy Joint Project */
713 -- +==========================================================================+
714 -- FUNCTION: get_waybill
715 -- PARAMETERS:
716 -- p_transaction_id IN NUMBER
717 -- COMMENT:
718 -- This procedure is called by the Inventory Master Book Report
719 -- Return: VARCHAR2
720 -- PRE-COND: none
721 -- EXCEPTIONS: none
722 -- +==========================================================================+
723
724 FUNCTION get_waybill (p_transaction_id NUMBER) RETURN VARCHAR2 IS
725 lc_waybill_airbill VARCHAR2(100);
726 BEGIN
727 BEGIN
728 SELECT waybill_airbill
729 INTO lc_waybill_airbill
730 FROM mtl_material_transactions
731 WHERE transaction_id = p_transaction_id;
732 EXCEPTION
733 WHEN OTHERS THEN
734 lc_waybill_airbill := NULL;
735 END;
736 RETURN (lc_waybill_airbill);
737 END get_waybill;
738
739 /* Included for Italy Joint Project */
740 -- +==========================================================================+
741 -- FUNCTION: get_po_number
742 -- PARAMETERS:
743 -- p_transaction_id IN NUMBER
744 -- p_type IN VARCHAR2
745 -- COMMENT:
746 -- This procedure is called by Inventory Master Book Report
747 -- Return: VARCHAR2
748 -- PRE-COND: none
749 -- EXCEPTIONS: none
750 -- +==========================================================================+
751 FUNCTION get_po_number (p_transaction_id IN NUMBER, p_type IN VARCHAR2) -- Added p_type by ppandit for Italy - China Enhancements
752 RETURN VARCHAR2
753 IS
754 ln_org_id NUMBER;
755 ln_trx_line_id NUMBER; -- Added for Bug Number 8834843
756 ln_row_count NUMBER;
757 ln_trans_source_type_id NUMBER;
758 ln_trans_source_id NUMBER;
759 lc_po_or_so_number VARCHAR2 (100);
760 --lv_po_number VARCHAR2(100); -- Commented by ppandit for Italy and China JF Project
761 BEGIN
762 -- Code added by ppandit for Italy and China JF Project to capture correct PO or SO Numbers start
763 SELECT MMT.organization_id
764 ,MMT.transaction_source_type_id
765 ,MMT.transaction_source_id
766 ,MMT.trx_source_line_id
767 INTO ln_org_id
768 ,ln_trans_source_type_id
769 ,ln_trans_source_id
770 ,ln_trx_line_id
771 FROM mtl_material_transactions MMT
772 WHERE MMT.transaction_id = p_transaction_id;
773
774 IF ln_trans_source_type_id = 1 THEN
775 SELECT PHA.segment1
776 INTO lc_po_or_so_number
777 FROM po_headers_all PHA
778 WHERE PHA.po_header_id = ln_trans_source_id;
779 ELSIF ln_trans_source_type_id IN (2, 8, 12) THEN
780 SELECT OOH.order_number -- Changed to refer to OE tables for Bug Number 8834843
781 INTO lc_po_or_so_number
782 FROM oe_order_headers_all OOH
783 ,oe_order_lines_all OOL
784 WHERE OOH.header_id = OOL.header_id
785 AND OOL.line_id = ln_trx_line_id;
786 END IF;
787
788 IF (p_type = 'PO' AND ln_trans_source_type_id = 1) OR (p_type = 'SO' AND ln_trans_source_type_id IN (2, 8, 12)) THEN
789 RETURN (lc_po_or_so_number);
790 ELSE
791 RETURN NULL;
792 END IF;
793 -- Code added by ppandit for Italy and China JF Project to capture correct PO or SO Numbers end
794 /* ppandit Comment start - Commented by ppandit for Italy and China JF Project */
795 --BEGIN
796 -- SELECT transaction_source_name
797 -- INTO lv_po_number
798 -- FROM mtl_material_transactions
799 -- WHERE transaction_id =p_transaction_id;
800 --EXCEPTION
801 -- WHEN others THEN
802 -- lv_po_number:=NULL;
803 --END;
804 --RETURN (lv_po_number);
805 /* ppandit Comment end */
806 EXCEPTION
807 WHEN OTHERS THEN
808 RETURN NULL;
809 END get_po_number;
810
811 -- +==========================================================================+
812 -- FUNCTION: getledger_name
813 -- PARAMETERS: None
814 -- COMMENT:
815 -- This procedure is called by Inventory Master Book Report to get the name of the Ledger
816 -- Return: VARCHAR2
817 -- PRE-COND: none
818 -- EXCEPTIONS: none
819 -- +==========================================================================+
820 FUNCTION getledger_name RETURN VARCHAR2 IS
821 lv_ledgername VARCHAR2(100);
822 BEGIN
823 BEGIN
824 SELECT name
825 INTO lv_ledgername
826 FROM gl_ledgers
827 WHERE ledger_id = p_ledger_id;
828 EXCEPTION
829 WHEN no_data_found THEN
830 lv_ledgername := NULL;
831 END;
832 RETURN (lv_ledgername);
833 END getledger_Name;
834
835 -- +==========================================================================+
836 -- FUNCTION: get_inv_org
837 -- PARAMETERS: None
838 -- COMMENT:
839 -- This procedure is called by Inventory Master Book Report to get Inventory Org
840 -- Return: VARCHAR2
841 -- PRE-COND: none
842 -- EXCEPTIONS: none
843 -- +==========================================================================+
844 FUNCTION get_inv_org RETURN VARCHAR2
845 IS
846 lc_inventory_org VARCHAR2 (240);
847 BEGIN
848 SELECT HOU.name
849 INTO lc_inventory_org
850 FROM hr_all_organization_units HOU
851 WHERE HOU.organization_id = p_inventory_org;
852
853 RETURN lc_inventory_org;
854 EXCEPTION
855 WHEN NO_DATA_FOUND THEN
856 RETURN (' ');
857 WHEN OTHERS THEN
858 RETURN (' ');
859 END get_inv_org;
860 -- +==========================================================================+
861 -- FUNCTION: get_subinv_org_from
862 -- PARAMETERS: None
863 -- COMMENT:
864 -- This procedure is called by Inventory Master Book Report Subinventory From
865 -- Return: VARCHAR2
866 -- PRE-COND: none
867 -- EXCEPTIONS: none
868 -- +==========================================================================+
869 FUNCTION get_subinv_org_from RETURN VARCHAR2
870 IS
871 BEGIN
872 RETURN p_subinv_from;
873 END get_subinv_org_from;
874 -- +==========================================================================+
875 -- FUNCTION: get_subinv_org_to
876 -- PARAMETERS: None
877 -- COMMENT:
878 -- This procedure is called by Inventory Master Book Report Subinventory To
879 -- Return: VARCHAR2
880 -- PRE-COND: none
881 -- EXCEPTIONS: none
882 -- +==========================================================================+
883 FUNCTION get_subinv_org_to RETURN VARCHAR2
884 IS
885 BEGIN
886 RETURN p_subinv_to;
887 END get_subinv_org_to;
888 -- +==========================================================================+
889 -- FUNCTION: get_category_set_from
890 -- PARAMETERS: None
891 -- COMMENT:
892 -- This procedure is called by Inventory Master Book Report Category Set From
893 -- Return: VARCHAR2
894 -- PRE-COND: none
895 -- EXCEPTIONS: none
896 -- +==========================================================================+
897 FUNCTION get_category_set_from RETURN VARCHAR2
898 IS
899 BEGIN
900 RETURN gc_category_set_name_1;
901 END get_category_set_from;
902 -- +==========================================================================+
903 -- FUNCTION: get_category_set_to
904 -- PARAMETERS: None
905 -- COMMENT:
906 -- This procedure is called by Inventory Master Book Report to get Category Set To
907 -- Return: VARCHAR2
908 -- PRE-COND: none
909 -- EXCEPTIONS: none
910 -- +==========================================================================+
911 FUNCTION get_category_set_to RETURN VARCHAR2
912 IS
913 BEGIN
914 RETURN gc_category_set_name_2;
915 END get_category_set_to;
916 -- +==========================================================================+
917 -- FUNCTION: get_category_from
918 -- PARAMETERS: None
919 -- COMMENT:
920 -- This procedure is called by Inventory Master Book Report to get Category From
921 -- Return: VARCHAR2
922 -- PRE-COND: none
923 -- EXCEPTIONS: none
924 -- +==========================================================================+
925 FUNCTION get_category_from RETURN VARCHAR2
926 IS
927 BEGIN
928 RETURN (p_category_from);
929 EXCEPTION
930 WHEN OTHERS THEN
931 RETURN NULL;
932 END get_category_from;
933 -- +==========================================================================+
934 -- FUNCTION: get_category_to
935 -- PARAMETERS: None
936 -- COMMENT:
937 -- This procedure is called by Inventory Master Book Report to get to get Category To
938 -- Return: VARCHAR2
939 -- PRE-COND: none
940 -- EXCEPTIONS: none
941 -- +==========================================================================+
942 FUNCTION get_category_to RETURN VARCHAR2
943 IS
944 BEGIN
945 RETURN (p_category_to);
946 EXCEPTION
947 WHEN OTHERS THEN
948 RETURN NULL;
949 END get_category_to;
950 -- +==========================================================================+
951 -- FUNCTION: get_item_from
952 -- PARAMETERS: None
953 -- COMMENT:
954 -- This procedure is called by Inventory Master Book Report to get Item Code From
955 -- Return: VARCHAR2
956 -- PRE-COND: none
957 -- EXCEPTIONS: none
958 -- +==========================================================================+
959 FUNCTION get_item_from RETURN VARCHAR2
960 IS
961 BEGIN
962 RETURN p_item_code_from;
963 END get_item_from;
964 -- +==========================================================================+
965 -- FUNCTION: get_item_to
966 -- PARAMETERS: None
967 -- COMMENT:
968 -- This procedure is called by Inventory Master Book Report to get Item Code To
969 -- Return: VARCHAR2
970 -- PRE-COND: none
971 -- EXCEPTIONS: none
972 -- +==========================================================================+
973 FUNCTION get_item_to RETURN VARCHAR2
974 IS
975 BEGIN
976 RETURN p_item_code_to;
977 END get_item_to;
978 -- +==========================================================================+
979 -- FUNCTION: get_abc_class
980 -- PARAMETERS: None
981 -- COMMENT:
982 -- This procedure is called by Inventory Master Book Report to get ABC Class
983 -- Return: VARCHAR2
984 -- PRE-COND: none
985 -- EXCEPTIONS: none
986 -- +==========================================================================+
987 FUNCTION get_abc_class RETURN VARCHAR2
988 IS
989 BEGIN
990 RETURN gc_abc_class_name;
991 END get_abc_class;
992 -- +==========================================================================+
993 -- FUNCTION: get_break_by_desc
994 -- PARAMETERS: None
995 -- COMMENT:
996 -- This procedure is called by Inventory Master Book Report to get Break By meaning
997 -- Return: VARCHAR2
998 -- PRE-COND: none
999 -- EXCEPTIONS: none
1000 -- +==========================================================================+
1001 FUNCTION get_break_by_desc RETURN VARCHAR2
1002 IS
1003 lc_meaning VARCHAR2 (80);
1004 BEGIN
1005 SELECT MLS.meaning
1006 INTO lc_meaning
1007 FROM mfg_lookups MLS
1008 WHERE MLS.lookup_type = 'CST_BREAK_BY_INV'
1009 AND MLS.lookup_code = p_break_by
1010 AND MLS.enabled_flag = 'Y';
1011
1012 RETURN (lc_meaning);
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 RETURN (' ');
1016 WHEN OTHERS THEN
1017 RETURN (' ');
1018 END get_break_by_desc;
1019
1020 -- +==========================================================================+
1021 -- FUNCTION: get_all_or_one
1022 -- PARAMETERS: None
1023 -- COMMENT:
1024 -- This procedure is called by Inventory Master Book Report to get value of
1025 -- All or One parameter
1026 --
1027 -- Return: VARCHAR2
1028 -- PRE-COND: none
1029 -- EXCEPTIONS: none
1030 -- +==========================================================================+
1031 FUNCTION get_all_or_one RETURN VARCHAR2
1032 IS
1033 lc_meaning VARCHAR2 (80);
1034 BEGIN
1035 SELECT MLS.meaning
1036 INTO lc_meaning
1037 FROM mfg_lookups MLS
1038 WHERE MLS.lookup_type = 'CST_ALL_OR_ONE_INV'
1039 AND MLS.lookup_code = p_all_or_single
1040 AND MLS.enabled_flag = 'Y';
1041
1042 RETURN (lc_meaning);
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 RETURN (' ');
1046 WHEN OTHERS THEN
1047 RETURN (' ');
1048 END get_all_or_one;
1049
1050 -- +==========================================================================+
1051 -- FUNCTION: get_icx_date
1052 -- PARAMETERS: None
1053 -- COMMENT:
1054 -- This procedure is called by Inventory Master Book Report to get value of
1055 -- Sysdate as per ICX Date Format
1056 --
1057 -- Return: VARCHAR2
1058 -- PRE-COND: none
1059 -- EXCEPTIONS: none
1060 -- +==========================================================================+
1061 FUNCTION get_icx_date RETURN VARCHAR2
1062 IS
1063 lc_sysdate VARCHAR2 (80);
1064 BEGIN
1065 SELECT TO_CHAR(SYSDATE, FND_PROFILE.value('ICX_DATE_FORMAT_MASK'))
1066 INTO lc_sysdate
1067 FROM SYS.dual;
1068
1069 RETURN (lc_sysdate);
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 RETURN NULL;
1073 END get_icx_date;
1074
1075 -- +==========================================================================+
1076 -- FUNCTION: get_page_penultimate
1077 -- PARAMETERS: None
1078 -- COMMENT:
1079 -- This procedure is called by Inventory Master Book Report to get value of
1080 -- Page Numbering minus one
1081 --
1082 -- Return: VARCHAR2
1083 -- PRE-COND: none
1084 -- EXCEPTIONS: none
1085 -- +==========================================================================+
1086 FUNCTION get_page_penultimate RETURN NUMBER
1087 IS
1088 lc_sysdate VARCHAR2 (80);
1089 BEGIN
1090 RETURN (TO_NUMBER(p_page_number) - 1);
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 RETURN NULL;
1094 END get_page_penultimate;
1095
1096 -- +==========================================================================+
1097 -- FUNCTION: get_row_count
1098 -- PARAMETERS: None
1099 -- COMMENT:
1100 -- This procedure is called by Inventory Master Book Report to get record count
1101 --
1102 -- Return: NUMBER
1103 -- PRE-COND: none
1104 -- EXCEPTIONS: none
1105 -- +==========================================================================+
1106 FUNCTION get_row_count RETURN NUMBER
1107 IS
1108 BEGIN
1109 /* Added counter for row count as this function is called in G_MAIN and G_SUMMARY_MAIN as well */
1110 gn_row_count := gn_row_count + 1;
1111 RETURN (NVL(gn_row_count, 0));
1112 END get_row_count;
1113
1114 -- +==========================================================================+
1115 -- FUNCTION: get_category_structure
1116 -- PARAMETERS: None
1117 -- COMMENT:
1118 -- This procedure is called by Inventory Master Book Report to get value of
1119 -- Item or Category Flexfields segments
1120 --
1121 -- Return: VARCHAR2
1122 -- PRE-COND: none
1123 -- EXCEPTIONS: none
1124 -- +==========================================================================+
1125 FUNCTION get_category_structure (
1126 p_type IN VARCHAR2
1127 ,p_cat_struct_id IN NUMBER
1128 )
1129 RETURN VARCHAR2
1130 IS
1131 lr_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1132 lr_structure_rec FND_FLEX_KEY_API.structure_type;
1133 lr_segment_rec FND_FLEX_KEY_API.segment_type;
1134 lt_segment_rec FND_FLEX_KEY_API.segment_list;
1135 ln_segment_number NUMBER;
1136 lc_segment_number VARCHAR2 (850);
1137 lc_mcat_segs VARCHAR2 (2000);
1138
1139 BEGIN
1140
1141 FND_FLEX_KEY_API.set_session_mode ('customer_data');
1142
1143 IF p_type = 'ITEM' THEN -- Retrieve system item concatenated flexfield
1144 lc_segment_number := '';
1145 lr_flexfield_rec := FND_FLEX_KEY_API.find_flexfield ('INV', 'MSTK');
1146 lr_structure_rec := FND_FLEX_KEY_API.find_structure (lr_flexfield_rec, p_cat_struct_id);
1147 FND_FLEX_KEY_API.get_segments (
1148 flexfield => lr_flexfield_rec
1149 ,structure => lr_structure_rec
1150 ,nsegments => ln_segment_number
1151 ,segments => lt_segment_rec
1152 );
1153
1154 FOR l_idx IN 1..ln_segment_number LOOP
1155 lr_segment_rec := FND_FLEX_KEY_API.find_segment (
1156 lr_flexfield_rec
1157 ,lr_structure_rec
1158 ,lt_segment_rec (l_idx)
1159 );
1160
1161 lc_segment_number := lc_segment_number || 'MSI.' || lr_segment_rec.column_name;
1162
1163 IF l_idx < ln_segment_number THEN
1164 lc_segment_number := lc_segment_number || '||' || '''' || lr_structure_rec.segment_separator || '''' || '||';
1165 END IF;
1166
1167 END LOOP;
1168
1169 RETURN (lc_segment_number);
1170 END IF;
1171
1172 IF p_type = 'CAT' THEN -- Retrieve Item Category concatenated flexfield
1173 lc_mcat_segs := '';
1174 lr_flexfield_rec := FND_FLEX_KEY_API.find_flexfield ('INV', 'MCAT');
1175 lr_structure_rec := FND_FLEX_KEY_API.find_structure (
1176 lr_flexfield_rec
1177 ,p_cat_struct_id
1178 );
1179 FND_FLEX_KEY_API.get_segments (
1180 flexfield => lr_flexfield_rec
1181 ,structure => lr_structure_rec
1182 ,nsegments => ln_segment_number
1183 ,segments => lt_segment_rec
1184 );
1185
1186 FOR l_idx IN 1..ln_segment_number LOOP
1187 lr_segment_rec := FND_FLEX_KEY_API.find_segment (
1188 lr_flexfield_rec
1189 ,lr_structure_rec
1190 ,lt_segment_rec (l_idx)
1191 );
1192
1193 lc_mcat_segs := lc_mcat_segs || 'MCK.' || lr_segment_rec.column_name;
1194
1195 IF l_idx < ln_segment_number THEN
1196 lc_mcat_segs := lc_mcat_segs || ' || ' || '''' || lr_structure_rec.segment_separator || '''' || ' || ';
1197 END IF;
1198
1199 END LOOP;
1200 RETURN (lc_mcat_segs);
1201 END IF;
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 RETURN NULL;
1206 END get_category_structure;
1207
1208 -- +==========================================================================+
1209 -- FUNCTION: get_structure_id
1210 -- PARAMETERS: None
1211 -- COMMENT:
1212 -- This procedure is called by Inventory Master Book Report to get value of
1213 -- Category Structure ID
1214 --
1215 -- Return: NUMBER
1216 -- PRE-COND: none
1217 -- EXCEPTIONS: none
1218 -- +==========================================================================+
1219 FUNCTION get_structure_id (p_category_set_id IN NUMBER)
1220 RETURN NUMBER
1221 IS
1222 ln_cat_struct NUMBER;
1223 BEGIN
1224 SELECT MCS.structure_id
1225 INTO ln_cat_struct
1226 FROM mtl_category_sets MCS
1227 WHERE MCS.category_set_id = p_category_set_id;
1228
1229 RETURN (ln_cat_struct);
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 RETURN NULL;
1233 END get_structure_id;
1234
1235 /* Included for Italy Joint Project */
1236 -- +==========================================================================+
1237 -- FUNCTION: get_org_details
1238 -- PARAMETERS:
1239 -- p_org_id IN NUMBER
1240 -- p_number IN NUMBER
1241 -- COMMENT:
1242 -- This function is called in the XML of Inventory Master Book Report
1243 -- for getting the Org details
1244 -- Return: VARCHAR2
1245 -- PRE-COND: none
1246 -- EXCEPTIONS: none
1247 -- +==========================================================================+
1248 FUNCTION get_org_details (p_org_id IN NUMBER, p_number IN NUMBER)
1249 RETURN VARCHAR2 IS
1250 lc_location VARCHAR2 (100);
1251 lc_type VARCHAR2 (100);
1252 lc_address VARCHAR2 (500);
1253 BEGIN
1254 SELECT HOU.location_code
1255 ,HOU.internal_external_meaning
1256 ,HOU.address_line_1 ||
1257 ' ' ||
1258 HOU.address_line_2 ||
1259 ' ' ||
1260 HOU.address_line_3 ||
1261 ' ' ||
1262 HOU.town_or_city ||
1263 ' ' ||
1264 HOU.country
1265 INTO lc_location
1266 ,lc_type
1267 ,lc_address
1268 FROM hr_organization_units_v HOU
1269 WHERE HOU.organization_id = p_org_id;
1270
1271 IF p_number = 1 THEN
1272 RETURN (lc_location);
1273 ELSIF p_number = 2 THEN
1274 RETURN (lc_type);
1275 ELSIF p_number = 3 THEN
1276 RETURN (lc_address);
1277 END IF;
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 RETURN NULL;
1281 END get_org_details;
1282
1283 -- +==========================================================================+
1284 -- FUNCTION: get_suborg_details
1285 -- PARAMETERS:
1286 -- p_subinvname IN VARCHAR2
1287 -- p_org_id IN NUMBER
1288 -- p_number IN NUMBER
1289 -- COMMENT:
1290 -- This function is called in the XML of Inventory Master Book Report
1291 -- for getting the Sub Org details
1292 -- Return: VARCHAR2
1293 -- PRE-COND: none
1294 -- EXCEPTIONS: none
1295 -- +==========================================================================+
1296 FUNCTION get_suborg_details (
1297 p_subinvname IN VARCHAR2
1298 ,p_org_id IN NUMBER
1299 ,p_number IN NUMBER
1300 )
1301 RETURN VARCHAR2 IS
1302 lc_location VARCHAR2 (100);
1303 lc_type VARCHAR2 (100);
1304 lc_address VARCHAR2 (500);
1305 BEGIN
1306 SELECT H.location_code
1307 ,H.description
1308 ,H.address_line_1 ||
1309 ' ' ||
1310 H.address_line_2 ||
1311 ' ' ||
1312 H.country
1313 INTO lc_location
1314 ,lc_type
1315 ,lc_address
1316 FROM mtl_secondary_inventories A
1317 ,hr_locations_all H
1318 WHERE A.secondary_inventory_name = p_subinvname
1319 AND H.location_id(+) = A.location_id
1320 AND organization_id = p_org_id;
1321
1322 IF p_number = 1 THEN
1323 RETURN (lc_location);
1324 ELSIF p_number = 2 THEN
1325 RETURN (lc_type);
1326 ELSIF p_number = 3 THEN
1327 RETURN (lc_address);
1328 END IF;
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331 RETURN NULL;
1332 END get_suborg_details;
1333
1334 -- +==========================================================================+
1335 -- FUNCTION: get_begin_columns
1336 -- PARAMETERS:
1337 -- p_inventory_item_id IN NUMBER
1338 -- p_type IN NUMBER
1339 -- COMMENT:
1340 -- This function is called in the XML of Inventory Master Book Report
1341 -- for getting the Begin Cost, Quantity and Value when Break By is Item
1342 -- and report is running for ALL Inventory Organizations
1343 --
1344 -- Return: NUMBER
1345 -- PRE-COND: none
1346 -- EXCEPTIONS: none
1347 -- +==========================================================================+
1348 FUNCTION get_begin_columns (
1349 p_inventory_item_id IN NUMBER
1350 ,p_type IN NUMBER
1351 )
1352 RETURN NUMBER IS
1353 ln_txn_ini_qty NUMBER := 0;
1354 ln_txn_ini_value NUMBER := 0;
1355 ln_org_id NUMBER;
1356
1357 CURSOR lcu_begin_val (p_org_id IN NUMBER)
1358 IS
1359 SELECT CMIAKD.transaction_id TRANSACTION_ID
1360 ,CMIAKD.txn_date TXN_DATE
1361 ,CMIAKD.txn_ini_qty TXN_INI_QTY
1362 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
1363 ,CMIAKD.organization_id ORGANIZATION_ID
1364 FROM cst_mgd_mstr_book_temp CMIAKD
1365 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1366 AND CMIAKD.organization_id = p_org_id
1367 AND p_break_by = 1
1368 ORDER BY 5, 2, 1 ASC;
1369
1370 lr_rec lcu_begin_val%ROWTYPE;
1371
1372 CURSOR lcu_org
1373 IS
1374 SELECT DISTINCT organization_id
1375 FROM cst_mgd_mstr_book_temp
1376 WHERE inventory_item_id = p_inventory_item_id
1377 ORDER BY organization_id ASC;
1378
1379 BEGIN
1380 FOR r_org IN lcu_org LOOP
1381 OPEN lcu_begin_val (r_org.organization_id);
1382 FETCH lcu_begin_val INTO lr_rec;
1383 CLOSE lcu_begin_val;
1384
1385 ln_txn_ini_qty := ln_txn_ini_qty + lr_rec.txn_ini_qty;
1386 ln_txn_ini_value := ln_txn_ini_value + lr_rec.txn_ini_value;
1387 END LOOP;
1388
1389 IF p_type = 1 THEN
1390 IF ln_txn_ini_qty <> 0 THEN RETURN (ln_txn_ini_value/ln_txn_ini_qty); ELSE RETURN (0); END IF;
1391 ELSIF p_type = 2 THEN
1392 RETURN (ln_txn_ini_qty);
1393 ELSIF p_type = 3 THEN
1394 RETURN (ln_txn_ini_value);
1395 END IF;
1396 END get_begin_columns;
1397
1398 -- +==========================================================================+
1399 -- FUNCTION: get_end_columns
1400 -- PARAMETERS:
1401 -- p_inventory_item_id IN NUMBER
1402 -- p_type IN NUMBER
1403 -- COMMENT:
1404 -- This function is called in the XML of Inventory Master Book Report
1405 -- for getting the End Cost, Quantity and Value when Break By is Item
1406 -- and report is running for ALL Inventory Organizations
1407 --
1408 -- Return: NUMBER
1409 -- PRE-COND: none
1410 -- EXCEPTIONS: none
1411 -- +==========================================================================+
1412 FUNCTION get_end_columns (
1413 p_inventory_item_id IN NUMBER
1414 ,p_type IN NUMBER
1415 )
1416 RETURN NUMBER IS
1417 ln_txn_fnl_qty NUMBER := 0;
1418 ln_txn_fnl_value NUMBER := 0;
1419 ln_org_id NUMBER;
1420
1421 CURSOR lcu_end_val (p_org_id IN NUMBER)
1422 IS
1423 SELECT CMIAKD.transaction_id TRANSACTION_ID
1424 ,CMIAKD.txn_date TXN_DATE
1425 ,CMIAKD.txn_qty TXN_FNL_QTY
1426 ,ROUND(CMIAKD.txn_unit_cost, 2) * CMIAKD.txn_qty TXN_FNL_VALUE
1427 ,CMIAKD.organization_id ORGANIZATION_ID
1428 FROM cst_mgd_mstr_book_temp CMIAKD
1429 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1430 AND CMIAKD.organization_id = p_org_id
1431 AND p_break_by = 1
1432 ORDER BY 5, 2, 1 ASC;
1433
1434 CURSOR lcu_org
1435 IS
1436 SELECT DISTINCT organization_id
1437 FROM cst_mgd_mstr_book_temp
1438 WHERE inventory_item_id = p_inventory_item_id
1439 ORDER BY organization_id ASC;
1440
1441 BEGIN
1442
1443 FOR r_org IN lcu_org LOOP
1444 FOR lr_rec IN lcu_end_val (r_org.organization_id)
1445 LOOP
1446 ln_txn_fnl_qty := ln_txn_fnl_qty + lr_rec.txn_fnl_qty;
1447 ln_txn_fnl_value := ln_txn_fnl_value + lr_rec.txn_fnl_value;
1448 END LOOP;
1449 END LOOP;
1450
1451 ln_txn_fnl_qty := ln_txn_fnl_qty + get_begin_columns (p_inventory_item_id, 2);
1452 ln_txn_fnl_value := ln_txn_fnl_value + get_begin_columns (p_inventory_item_id, 3);
1453
1454 IF p_type = 1 THEN
1455 IF ln_txn_fnl_qty <> 0 THEN RETURN (ln_txn_fnl_value/ln_txn_fnl_qty); ELSE RETURN (0); END IF;
1456 ELSIF p_type = 2 THEN
1457 RETURN (ln_txn_fnl_qty);
1458 ELSIF p_type = 3 THEN
1459 RETURN (ln_txn_fnl_value);
1460 END IF;
1461 END get_end_columns;
1462
1463 -- +==========================================================================+
1464 -- FUNCTION: get_summ_beg_cols
1465 -- PARAMETERS:
1466 -- p_inventory_item_id IN NUMBER
1467 -- p_organization_id IN NUMBER
1468 -- p_sub_inv_org_name IN VARCHAR2
1469 -- p_sub_inv_org_id IN NUMBER
1470 -- p_type IN NUMBER
1471 -- COMMENT:
1472 -- This function is called in the XML of Inventory Master Book Report
1473 -- for getting the Begin Cost, Quantity and Value when report is running for
1474 -- detail as Summary
1475 --
1476 -- Return: NUMBER
1477 -- PRE-COND: none
1478 -- EXCEPTIONS: none
1479 -- +==========================================================================+
1480 FUNCTION get_summ_beg_cols (
1481 p_inventory_item_id IN NUMBER
1482 ,p_organization_id IN NUMBER
1483 ,p_sub_inv_org_name IN VARCHAR2
1484 ,p_sub_inv_org_id IN NUMBER
1485 ,p_type IN NUMBER
1486 )
1487 RETURN NUMBER IS
1488 ln_txn_ini_qty NUMBER := 0;
1489 ln_txn_ini_value NUMBER := 0;
1490 ln_org_id NUMBER;
1491
1492 CURSOR lcu_begin_val (p_org_id IN NUMBER)
1493 IS
1494 SELECT CMIAKD.transaction_id TRANSACTION_ID
1495 ,CMIAKD.txn_date TXN_DATE
1496 ,CMIAKD.txn_ini_qty TXN_INI_QTY
1497 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
1498 ,CMIAKD.organization_id ORGANIZATION_ID
1499 FROM cst_mgd_mstr_book_temp CMIAKD
1500 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1501 AND CMIAKD.organization_id = p_org_id
1502 AND p_break_by = 1
1503 ORDER BY 5, 2, 1 ASC;
1504
1505 lr_rec lcu_begin_val%ROWTYPE;
1506
1507 CURSOR lcu_summary_cols
1508 IS
1509 SELECT CMIAKD.transaction_id TRANSACTION_ID
1510 ,CMIAKD.currency_code CURRENCY_CODE
1511 ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
1512 ,CMIAKD.txn_date TXN_DATE
1513 ,CMIAKD.txn_type TXN_TYPE
1514 ,CMIAKD.txn_source TXN_SOURCE
1515 ,CMIAKD.txn_ini_qty TXN_INI_QTY
1516 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
1517 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
1518 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1519 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
1520 ,CMIAKD.txn_qty TXN_QTY
1521 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1522 ,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
1523 ,CMIAKD.organization_id ORGANIZATION_ID
1524 FROM cst_mgd_mstr_book_temp CMIAKD
1525 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1526 AND p_break_by = 1
1527 AND p_detail = 'S'
1528 UNION ALL
1529 SELECT CMIAKD.transaction_id TRANSACTION_ID
1530 ,CMIAKD.currency_code CURRENCY_CODE
1531 ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
1532 ,CMIAKD.txn_date TXN_DATE
1533 ,CMIAKD.txn_type TXN_TYPE
1534 ,CMIAKD.txn_source TXN_SOURCE
1535 ,CMIAKD.txn_ini_qty TXN_INI_QTY
1536 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
1537 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
1538 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1539 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
1540 ,CMIAKD.txn_qty TXN_QTY
1541 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1542 ,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
1543 ,CMIAKD.organization_id ORGANIZATION_ID
1544 FROM cst_mgd_mstr_book_temp CMIAKD
1545 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1546 AND CMIAKD.organization_id = p_organization_id
1547 AND p_break_by IN (2, 4)
1548 AND p_detail = 'S'
1549 UNION ALL
1550 SELECT CMIAKD.transaction_id TRANSACTION_ID
1551 ,CMIAKD.currency_code CURRENCY_CODE
1552 ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
1553 ,CMIAKD.txn_date TXN_DATE
1554 ,CMIAKD.txn_type TXN_TYPE
1555 ,CMIAKD.txn_source TXN_SOURCE
1556 ,CMIAKD.txn_ini_qty TXN_INI_QTY
1557 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
1558 ,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
1559 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1560 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
1561 ,CMIAKD.txn_qty TXN_QTY
1562 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1563 ,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
1564 ,CMIAKD.organization_id ORGANIZATION_ID
1565 FROM cst_mgd_mstr_book_temp CMIAKD
1566 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1567 AND CMIAKD.organization_id = p_organization_id
1568 AND CMIAKD.subinventory_code = p_sub_inv_org_name
1569 AND CMIAKD.sub_inv_organization_id = p_sub_inv_org_id
1570 AND p_break_by IN (3, 5)
1571 AND p_detail = 'S'
1572 ORDER BY 15, 4, 1 ASC;
1573
1574 ls_rec lcu_summary_cols%ROWTYPE;
1575
1576 CURSOR lcu_org
1577 IS
1578 SELECT DISTINCT organization_id
1579 FROM cst_mgd_mstr_book_temp
1580 WHERE inventory_item_id = p_inventory_item_id
1581 ORDER BY organization_id ASC;
1582
1583 BEGIN
1584 IF p_inventory_org IS NULL AND p_break_by = 1 AND p_detail = 'S' THEN
1585 FOR r_org IN lcu_org LOOP
1586
1587 OPEN lcu_begin_val (r_org.organization_id);
1588 FETCH lcu_begin_val INTO lr_rec;
1589 CLOSE lcu_begin_val;
1590
1591 ln_txn_ini_qty := ln_txn_ini_qty + lr_rec.txn_ini_qty;
1592 ln_txn_ini_value := ln_txn_ini_value + lr_rec.txn_ini_value;
1593 END LOOP;
1594 ELSIF NOT (p_inventory_org IS NULL AND p_break_by = 1) AND p_detail = 'S' THEN
1595 OPEN lcu_summary_cols;
1596 FETCH lcu_summary_cols INTO ls_rec;
1597 CLOSE lcu_summary_cols;
1598
1599 ln_txn_ini_qty := ls_rec.txn_ini_qty;
1600 ln_txn_ini_value := ls_rec.txn_ini_value;
1601 END IF;
1602
1603 IF p_type = 1 THEN
1604 IF ln_txn_ini_qty <> 0 THEN RETURN (ln_txn_ini_value/ln_txn_ini_qty); ELSE RETURN (0); END IF;
1605 ELSIF p_type = 2 THEN
1606 RETURN (ln_txn_ini_qty);
1607 ELSIF p_type = 3 THEN
1608 RETURN (ln_txn_ini_value);
1609 END IF;
1610 END get_summ_beg_cols;
1611
1612 -- +==========================================================================+
1613 -- FUNCTION: get_summ_end_cols
1614 -- PARAMETERS:
1615 -- p_inventory_item_id IN NUMBER
1616 -- p_organization_id IN NUMBER
1617 -- p_sub_inv_org_name IN VARCHAR2
1618 -- p_sub_inv_org_id IN NUMBER
1619 -- p_type IN NUMBER
1620 --
1621 -- COMMENT:
1622 -- This function is called in the XML of Inventory Master Book Report
1623 -- for getting the End Cost, Quantity and Value when report is running for
1624 -- detail as Summary
1625 --
1626 -- Return: NUMBER
1627 -- PRE-COND: none
1628 -- EXCEPTIONS: none
1629 -- +==========================================================================+
1630 FUNCTION get_summ_end_cols (
1631 p_inventory_item_id IN NUMBER
1632 ,p_organization_id IN NUMBER
1633 ,p_sub_inv_org_name IN VARCHAR2
1634 ,p_sub_inv_org_id IN NUMBER
1635 ,p_type IN NUMBER
1636 )
1637 RETURN NUMBER IS
1638 ln_txn_fnl_qty NUMBER := 0;
1639 ln_txn_fnl_value NUMBER := 0;
1640
1641 CURSOR lcu_end_val (p_org_id IN NUMBER)
1642 IS
1643 SELECT CMIAKD.transaction_id TRANSACTION_ID
1644 ,CMIAKD.txn_date TXN_DATE
1645 ,CMIAKD.txn_qty TXN_FNL_QTY
1646 ,ROUND(CMIAKD.txn_unit_cost, 2) * CMIAKD.txn_qty TXN_FNL_VALUE
1647 ,CMIAKD.organization_id ORGANIZATION_ID
1648 FROM cst_mgd_mstr_book_temp CMIAKD
1649 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1650 AND CMIAKD.organization_id = p_org_id
1651 AND p_break_by = 1
1652 ORDER BY 5, 2, 1 ASC;
1653
1654 CURSOR lcu_summary_cols
1655 IS
1656 SELECT CMIAKD.transaction_id TRANSACTION_ID
1657 ,CMIAKD.txn_date TXN_DATE
1658 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1659 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1660 ,CMIAKD.organization_id ORGANIZATION_ID
1661 FROM cst_mgd_mstr_book_temp CMIAKD
1662 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1663 AND p_break_by = 1
1664 AND p_detail = 'S'
1665 UNION ALL
1666 SELECT CMIAKD.transaction_id TRANSACTION_ID
1667 ,CMIAKD.txn_date TXN_DATE
1668 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1669 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1670 ,CMIAKD.organization_id ORGANIZATION_ID
1671 FROM cst_mgd_mstr_book_temp CMIAKD
1672 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1673 AND CMIAKD.organization_id = p_organization_id
1674 AND p_break_by IN (2, 4)
1675 AND p_detail = 'S'
1676 UNION ALL
1677 SELECT CMIAKD.transaction_id TRANSACTION_ID
1678 ,CMIAKD.txn_date TXN_DATE
1679 ,CMIAKD.txn_fnl_qty TXN_FNL_QTY
1680 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
1681 ,CMIAKD.organization_id ORGANIZATION_ID
1682 FROM cst_mgd_mstr_book_temp CMIAKD
1683 WHERE CMIAKD.inventory_item_id = p_inventory_item_id
1684 AND CMIAKD.organization_id = p_organization_id
1685 AND CMIAKD.subinventory_code = p_sub_inv_org_name
1686 AND CMIAKD.sub_inv_organization_id = p_sub_inv_org_id
1687 AND p_break_by IN (3, 5)
1688 AND p_detail = 'S'
1689 ORDER BY 5, 2, 1 ASC;
1690
1691 ls_rec lcu_summary_cols%ROWTYPE;
1692
1693 CURSOR lcu_org
1694 IS
1695 SELECT DISTINCT organization_id
1696 FROM cst_mgd_mstr_book_temp
1697 WHERE inventory_item_id = p_inventory_item_id
1698 ORDER BY organization_id ASC;
1699
1700 BEGIN
1701 IF p_inventory_org IS NULL AND p_break_by = 1 AND p_detail = 'S' THEN
1702 FOR r_org IN lcu_org LOOP
1703 FOR lr_rec IN lcu_end_val (r_org.organization_id)
1704 LOOP
1705 ln_txn_fnl_qty := ln_txn_fnl_qty + lr_rec.txn_fnl_qty;
1706 ln_txn_fnl_value := ln_txn_fnl_value + lr_rec.txn_fnl_value;
1707 END LOOP;
1708 END LOOP;
1709
1710 ln_txn_fnl_qty := ln_txn_fnl_qty + get_summ_beg_cols (p_inventory_item_id, p_organization_id, p_sub_inv_org_name, p_sub_inv_org_id, 2);
1711 ln_txn_fnl_value := ln_txn_fnl_value + get_summ_beg_cols (p_inventory_item_id, p_organization_id, p_sub_inv_org_name, p_sub_inv_org_id, 3);
1712 ELSIF NOT (p_inventory_org IS NULL AND p_break_by = 1) AND p_detail = 'S' THEN
1713 FOR ls_rec IN lcu_summary_cols LOOP
1714 ln_txn_fnl_qty := ls_rec.txn_fnl_qty;
1715 ln_txn_fnl_value := ls_rec.txn_fnl_value;
1716 END LOOP;
1717 END IF;
1718
1719 IF p_type = 1 THEN
1720 IF ln_txn_fnl_qty <> 0 THEN RETURN (ln_txn_fnl_value/ln_txn_fnl_qty); ELSE RETURN (0); END IF;
1721 ELSIF p_type = 2 THEN
1722 RETURN (ln_txn_fnl_qty);
1723 ELSIF p_type = 3 THEN
1724 RETURN (ln_txn_fnl_value);
1725 END IF;
1726 END get_summ_end_cols;
1727
1728 -- +==========================================================================+
1729 -- FUNCTION: get_break_by
1730 -- PARAMETERS: NONE
1731 -- COMMENT:
1732 -- This function is called in the XML of Inventory Master Book Report
1733 -- for getting the break by details
1734 -- Return: NUMBER
1735 -- PRE-COND: none
1736 -- EXCEPTIONS: none
1737 -- +==========================================================================+
1738 FUNCTION get_break_by RETURN NUMBER
1739 IS
1740 ln_break_by NUMBER;
1741 BEGIN
1742 /* Commented by ppandit for Italy - China Joint Fund Project */
1743 -- BEGIN
1744 -- SELECT MEANING
1745 -- INTO GC_BREAK
1746 -- FROM FND_LOOKUPS
1747 -- WHERE LOOKUP_CODE = P_BREAK_BY
1748 -- AND LOOKUP_TYPE = 'BREAK_BY_IMB';
1749 -- EXCEPTION
1750 -- WHEN NO_DATA_FOUND THEN
1751 -- GC_BREAK:=NULL;
1752 -- END;
1753 -- RETURN (GC_BREAK);
1754 SELECT p_break_by
1755 INTO ln_break_by
1756 FROM SYS.dual;
1757 RETURN (ln_break_by);
1758 END;
1759
1760 --========================================================================
1761 -- FUNCTION : get_detail_param Public
1762 -- PARAMETERS: None
1763 -- RETURN : VARCHAR2
1764 -- COMMENT : This function is called by Inventory Master Book Report to gets p_detail
1765 -- EXCEPTIONS: no_data_found
1766 --========================================================================
1767 FUNCTION get_detail_param RETURN VARCHAR2
1768 IS
1769 lc_detail_param VARCHAR2(10);
1770 BEGIN
1771 SELECT p_detail
1772 INTO lc_detail_param
1773 FROM SYS.dual;
1774 RETURN (lc_detail_param);
1775 END;
1776
1777 --========================================================================
1778 -- FUNCTION : get_include_item_cost Public
1779 -- PARAMETERS: None
1780 -- RETURN : VARCHAR2
1781 -- COMMENT : This Function is called by Inventory Master Book Report to gets the break by p_include_item_cost
1782 -- EXCEPTIONS: no_data_found
1783 --========================================================================
1784 FUNCTION get_include_item_cost RETURN VARCHAR2
1785 IS
1786 lc_incl_cost VARCHAR2(10);
1787 BEGIN
1788 SELECT p_include_item_cost
1789 INTO lc_incl_cost
1790 FROM SYS.dual;
1791 RETURN (lc_incl_cost);
1792 END;
1793
1794 -- +==========================================================================+
1795 -- FUNCTION: get_detail_level
1796 -- PARAMETERS: None
1797 -- COMMENT:
1798 -- This procedure is called by the Inventory Master Book Report to get the detail level
1799 -- like Summary, Detail, Intermidiate
1800 -- Return: VARCHAR2
1801 -- PRE-COND: none
1802 -- EXCEPTIONS: none
1803 -- +==========================================================================+
1804 FUNCTION get_detail_level RETURN VARCHAR2 IS
1805 BEGIN
1806 RETURN (gc_detail);
1807 END get_detail_level;
1808
1809 -- +==========================================================================+
1810 -- FUNCTION: get_date_from
1811 -- PARAMETERS: None
1812 -- COMMENT:
1813 -- This procedure is called by the Inventory Master Book Report to get the Lower of Date Range
1814 -- Added by ppandit for Italy and China JF Project
1815 -- Return: VARCHAR2
1816 -- +==========================================================================+
1817 FUNCTION get_date_from
1818 RETURN VARCHAR2
1819 IS
1820 lc_date VARCHAR2 (50);
1821 BEGIN
1822 SELECT TO_CHAR (TO_DATE (p_date_from, 'YYYY/MM/DD HH24:MI:SS'), FND_PROFILE.value('ICX_DATE_FORMAT_MASK'))
1823 INTO lc_date
1824 FROM SYS.dual;
1825
1826 RETURN (lc_date);
1827 END get_date_from;
1828
1829 -- +==========================================================================+
1830 -- FUNCTION: get_date_to
1831 -- PARAMETERS: None
1832 -- COMMENT:
1833 -- This procedure is called by the Inventory Master Book Report to get the Higher of Date Range
1834 --
1835 -- Return: VARCHAR2
1836 -- +==========================================================================+
1837 FUNCTION get_date_to
1838 RETURN VARCHAR2
1839 IS
1840 lc_date VARCHAR2 (50);
1841 BEGIN
1842 SELECT TO_CHAR (TO_DATE (p_date_to, 'YYYY/MM/DD HH24:MI:SS'), FND_PROFILE.value ('ICX_DATE_FORMAT_MASK'))
1843 INTO lc_date
1844 FROM SYS.dual;
1845
1846 RETURN (lc_date);
1847 END get_date_to;
1848
1849 -- +==========================================================================+
1850 -- FUNCTION: get_trx_action
1851 -- PARAMETERS: p_transaction_id
1852 -- COMMENT:
1853 -- This procedure is called by the Inventory Master Book Report to get the Higher of Date Range
1854 -- Added by ppandit for Italy and China JF Project
1855 -- Return: VARCHAR2
1856 -- +==========================================================================+
1857 FUNCTION get_trx_action (p_transaction_id IN NUMBER)
1858 RETURN VARCHAR2
1859 IS
1860 lc_transaction_action VARCHAR2 (80);
1861 BEGIN
1862 SELECT MLS.meaning
1863 INTO lc_transaction_action
1864 FROM mtl_material_transactions MMT
1865 ,mfg_lookups MLS
1866 WHERE MLS.lookup_code = MMT.transaction_action_id
1867 AND MLS.lookup_type = 'MTL_TRANSACTION_ACTION'
1868 AND MMT.transaction_id = p_transaction_id;
1869
1870 RETURN (lc_transaction_action);
1871 EXCEPTION
1872 WHEN OTHERS THEN
1873 RETURN NULL;
1874 END;
1875
1876 --========================================================================
1877 -- FUNCTION : CF_TITLEFORMULA Public
1878 -- PARAMETERS: None
1879 -- RETURN : VARCHAR2
1880 -- COMMENT :This Function is called by Inventory Master Book Report to the title
1881 -- EXCEPTIONS: OTHERS
1882 --========================================================================
1883
1884 FUNCTION CF_TITLEFORMULA RETURN CHAR IS
1885 TITLE VARCHAR2(40);
1886 BEGIN
1887 TITLE := '';
1888 IF P_DETAIL = 'S' THEN
1889 TITLE := 'Summary Level';
1890 ELSIF P_DETAIL = 'I' THEN
1891 TITLE := 'Intermediate Level';
1892 ELSIF P_DETAIL = 'D' THEN
1893 TITLE := 'Detail Level';
1894 END IF;
1895 IF P_INCLUDE_ITEM_COST = 'Y' THEN
1896 RETURN (CONCAT(TITLE
1897 ,' - Cost Included'));
1898 ELSE
1899 RETURN (TITLE);
1900 END IF;
1901 END CF_TITLEFORMULA;
1902
1903 -- +==========================================================================+
1904 -- FUNCTION: get_abc_group_name
1905 -- PARAMETERS: None
1906 -- Return : VARCHAR2
1907 -- COMMENT:
1908 -- This function is called by Inventory Master Book Report for getting the ABC Group Name
1909 -- for a given ABC Group
1910 -- PRE-COND: none
1911 -- EXCEPTIONS: none
1912 -- +==========================================================================+
1913 FUNCTION get_abc_group_name RETURN VARCHAR2 IS
1914 BEGIN
1915 RETURN gc_abc_group_name;
1916 END get_abc_group_name;
1917
1918 -- +==========================================================================+
1919 -- FUNCTION: get_include_cost
1920 -- PARAMETERS: None
1921 -- COMMENT:
1922 -- This procedure is called by the Inventory Master Book Report which returns 'Y/N'
1923 -- Return: VARCHAR2
1924 -- PRE-COND: none
1925 -- EXCEPTIONS: none
1926 -- +==========================================================================+
1927 FUNCTION get_include_cost RETURN VARCHAR2 IS
1928 BEGIN
1929 RETURN gc_include_cost;
1930 END get_include_cost;
1931
1932 --========================================================================
1933 -- FUNCTION : CF_COUNT_ROWSFORMULA PRIVATE
1934 -- PARAMETERS: None
1935 -- COMMENT :
1936 -- EXCEPTIONS: OTHERS
1937 --========================================================================
1938
1939 FUNCTION CF_COUNT_ROWSFORMULA RETURN NUMBER IS
1940 COUNT_ROWS NUMBER;
1941 BEGIN
1942 SELECT
1943 count(*)
1944 INTO COUNT_ROWS
1945 FROM
1946 CST_MGD_MSTR_BOOK_TEMP;
1947 RETURN (COUNT_ROWS);
1948 END CF_COUNT_ROWSFORMULA;
1949
1950 FUNCTION P_DATE_FROMVALIDTRIGGER RETURN BOOLEAN IS
1951 BEGIN
1952 RETURN (TRUE);
1953 END P_DATE_FROMVALIDTRIGGER;
1954
1955 FUNCTION CF_TXN_GROUP_TYPEFORMULA(TXN_TYPE_QTY IN NUMBER) RETURN CHAR IS
1956
1957 BEGIN
1958 IF TXN_TYPE_QTY > 0 THEN
1959 RETURN (GC_TXT_RECEIPT);
1960 ELSE
1961 RETURN (GC_TXT_ISSUE);
1962 END IF;
1963 END CF_TXN_GROUP_TYPEFORMULA;
1964
1965 FUNCTION CF_FINAL_QUANTITYFORMULA(CS_FINAL_QUANTITY IN NUMBER
1966 ,CS_B_QTY IN VARCHAR2) RETURN NUMBER IS
1967 BEGIN
1968 RETURN (NVL(CS_FINAL_QUANTITY
1969 ,0) + NVL(CS_B_QTY
1970 ,0));
1971 END CF_FINAL_QUANTITYFORMULA;
1972
1973
1974 --========================================================================
1975 -- PROCEDURE : get_acct_period_id PRIVATE
1976 -- PARAMETERS: p_org_id Organization ID
1977 -- p_rpt_from_date Report start date
1978 -- P_rpt_to_date Report end date
1979 -- x_rpt_from_acct_per_id Report start account period ID
1980 -- x_rpt_to_acct_per_id Report end account period ID
1981 -- COMMENT : Get the account period IDs for user defined reporting
1982 -- period
1983 -- EXCEPTIONS:
1984 --========================================================================
1985 PROCEDURE get_acct_period_id (
1986 p_org_id IN NUMBER
1987 , p_rpt_from_date IN VARCHAR2
1988 , p_rpt_to_date IN VARCHAR2
1989 , x_rpt_from_acct_per_id OUT NOCOPY NUMBER
1990 , x_rpt_to_acct_per_id OUT NOCOPY NUMBER
1991 )
1992 IS
1993 l_rpt_from_acct_per_id NUMBER;
1994 l_rpt_to_acct_per_id NUMBER;
1995 l_rpt_from_date DATE;
1996 l_rpt_to_date DATE;
1997
1998 -- Cursor to retrieve from accounting period id
1999 CURSOR from_acct_period_cur(c_rpt_from_date DATE)
2000 IS
2001 SELECT
2002 f.acct_period_id
2003 FROM
2004 org_acct_periods f
2005 WHERE f.organization_id = p_org_id
2006 AND f.period_start_date <= c_rpt_from_date
2007 AND f.schedule_close_date >= c_rpt_from_date
2008 AND F.Open_Flag = 'N'
2009 AND F.Period_Close_Date IS NOT NULL;
2010
2011 -- Cursor to retrieve to accounting period id
2012 CURSOR to_acct_period_cur(c_rpt_to_date DATE)
2013 IS
2014 SELECT
2015 t.acct_period_id
2016 FROM
2017 org_acct_periods t
2018 WHERE t.organization_id = p_org_id
2019 AND t.period_start_date <= c_rpt_to_date
2020 AND t.schedule_close_date >= c_rpt_to_date
2021 AND T.Open_Flag = 'N'
2022 AND T.Period_Close_Date IS NOT NULL;
2023
2024 -- Exception
2025 acct_period_not_found_exc EXCEPTION;
2026
2027 BEGIN
2028 l_rpt_from_date := TRUNC(FND_DATE.canonical_to_date(p_rpt_from_date));
2029 l_rpt_to_date := TRUNC(FND_DATE.canonical_to_date(p_rpt_to_date));
2030
2031 -- Get from account period id
2032 OPEN from_acct_period_cur(TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS'));
2033 FETCH from_acct_period_cur
2034 INTO x_rpt_from_acct_per_id;
2035
2036 IF from_acct_period_cur%NOTFOUND THEN
2037 RAISE acct_period_not_found_exc;
2038 END IF;
2039 CLOSE from_acct_period_cur;
2040
2041 -- Get to account period id
2042 OPEN to_acct_period_cur(TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400)); -- Changed by ppandit for using params directly, Italy China Enhancements
2043 FETCH to_acct_period_cur
2044 INTO x_rpt_to_acct_per_id;
2045
2046 IF to_acct_period_cur%NOTFOUND THEN
2047 RAISE acct_period_not_found_exc;
2048 END IF;
2049 CLOSE to_acct_period_cur;
2050
2051 EXCEPTION
2052
2053 WHEN acct_period_not_found_exc THEN
2054 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ND_ACCT_PER_ID');
2055 FND_MSG_PUB.Add;
2056 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2057 THEN
2058 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2059 , 'Get_Acct_Period_ID'
2060 );
2061 END IF;
2062 RAISE ;
2063
2064
2065 WHEN OTHERS THEN
2066 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2067 THEN
2068 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2069 , 'Get_Acct_Period_ID'
2070 );
2071 END IF;
2072 RAISE;
2073
2074 END get_acct_period_id;
2075
2076 --========================================================================
2077 -- PROCEDURE : Get_Unit_Infl_Adj_Cost PRIVATE
2078 -- PARAMETERS: p_org_id Organization ID
2079 -- p_acct_period_id Account period ID
2080 -- p_item_id Inventory item ID
2081 -- x_unit_infl_adj Inventory item period end unit
2082 -- inflation adjusted cost
2083 -- : x_init_qty Period begin quantity
2084 -- COMMENT : Retrieve item unit inflation adjusted cost and begin
2085 -- quantity
2086 -- EXCEPTIONS:
2087 --========================================================================
2088 PROCEDURE get_unit_infl_adj_cost (
2089 p_org_id IN NUMBER
2090 , p_acct_period_id IN NUMBER
2091 , p_item_id IN NUMBER
2092 , x_unit_infl_adj OUT NOCOPY NUMBER
2093 , x_init_qty OUT NOCOPY NUMBER
2094 )
2095 IS
2096 l_final_infl_adj NUMBER;
2097 l_final_qty NUMBER;
2098 BEGIN
2099
2100 SELECT
2101 Begin_Qty
2102 , NVL((Actual_Inflation_Adj - Issue_Inflation_Adj), 0)
2103 , NVL((Actual_Qty - Issue_Qty), 0)
2104 INTO
2105 x_init_qty
2106 , l_final_infl_adj
2107 , l_final_qty
2108 FROM
2109 CST_MGD_INFL_ADJUSTED_COSTS
2110 WHERE Organization_ID = p_org_id
2111 AND Acct_Period_ID = p_acct_period_id
2112 AND Inventory_Item_ID = p_item_id;
2113
2114 IF l_final_qty = 0
2115 THEN
2116 x_unit_infl_adj := 0;
2117 ELSE
2118 x_unit_infl_adj := l_final_infl_adj/l_final_qty;
2119 END IF;
2120
2121 EXCEPTION
2122
2123 WHEN OTHERS THEN
2124 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2125 THEN
2126 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2127 , 'Get_Unit_Infl_Adj_Cost'
2128 );
2129 END IF;
2130 RAISE;
2131
2132 END get_unit_infl_adj_cost;
2133
2134
2135 --========================================================================
2136 -- PROCEDURE : Get_Txn_Type PRIVATE
2137 -- PARAMETERS: p_txn_type_id Transaction type ID
2138 -- x_txn_type_name Transaction type name
2139 -- COMMENT : Retrieve transaction type name from ID
2140 -- EXCEPTIONS:
2141 --========================================================================
2142 PROCEDURE Get_Txn_Type (
2143 p_txn_type_id IN NUMBER
2144 , x_txn_type_name OUT NOCOPY VARCHAR2
2145 )
2146 IS
2147 BEGIN
2148
2149 SELECT
2150 SUBSTR(Transaction_Type_Name,1,30)
2151 INTO
2152 x_txn_type_name
2153 FROM
2154 MTL_TRANSACTION_TYPES
2155 WHERE Transaction_Type_ID = p_txn_type_id;
2156
2157 -- Bug#2433926 fix no validation for transaction type disable date
2158 -- AND NVL(Disable_Date, SYSDATE + 1) > SYSDATE;
2159
2160 EXCEPTION
2161 WHEN OTHERS THEN
2162 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2163 THEN
2164 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2165 , 'Get_Txn_Type'
2166 );
2167 END IF;
2168 RAISE;
2169
2170 END Get_Txn_Type;
2171
2172 --========================================================================
2173 -- PROCEDURE : get_item_txn_info PRIVATE
2174 -- PARAMETERS: p_org_id Organization ID
2175 -- p_item_id Inventory item ID
2176 -- p_acct_period_id Mfg accounting period ID
2177 -- p_per_first_txn_date First transaction date for
2178 -- reporting.
2179 -- p_per_last_txn_date Last transaction date for
2180 -- reporting.
2181 -- p_item_unit_cost Inventory item unit average cost
2182 -- p_primary_cost_method Primary Cost Method of Organization
2183 -- p_item_init_qty Inventory item period begin
2184 -- quantity
2185 -- p_item_unit_infl_adj Inventory item period end unit
2186 -- inflation adjusted cost
2187 -- x_rpt_item_tbl_rec Report data record
2188 -- COMMENT : Builds data for one row
2189 -- Bug3118846 fix : exclude NON qty tracked subinventories trx
2190 -- EXCEPTION : g_txn_cost_exc Missing transaction costs.
2191 --========================================================================
2192 PROCEDURE get_item_txn_info (
2193 p_org_id IN NUMBER
2194 , p_item_id IN NUMBER
2195 , p_uom_code IN VARCHAR2 --added for inv book
2196 , p_item_code IN VARCHAR2
2197 , p_item_desc IN VARCHAR2
2198 , p_org_name IN VARCHAR2
2199 , p_currency_code IN VARCHAR2
2200 , p_subinv_from IN VARCHAR2
2201 , p_subinv_to IN VARCHAR2
2202 , p_acct_period_id IN NUMBER
2203 , p_per_first_txn_date IN VARCHAR2
2204 , p_per_last_txn_date IN VARCHAR2
2205 , p_item_unit_cost IN NUMBER
2206 , p_primary_cost_method IN NUMBER
2207 , p_item_init_qty IN NUMBER
2208 , p_item_init_infl IN NUMBER
2209 , p_item_unit_infl_adj IN NUMBER
2210 , x_rpt_item_tbl_rec OUT NOCOPY Report_Tbl_Rec_Type
2211 )
2212 IS
2213 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
2214 l_txn_init_qty NUMBER;
2215 l_txn_init_infl NUMBER;
2216 l_prev_acct_period_id NUMBER;
2217 l_prev_sch_close_date DATE;
2218 l_index BINARY_INTEGER := 1;
2219 l_begin_unit_cost NUMBER;
2220 l_txn_cost_exc EXCEPTION;
2221 l_per_first_txn_date DATE;
2222 l_per_last_txn_date DATE;
2223 l_primary_qty NUMBER;
2224 l_total_cost NUMBER;
2225 -- Bug#2799104 fix: to exclude WIP scrap transaction and
2226 -- WIP cost update transaction
2227 -- value_change for Average Cost Update transaction
2228 -- Bug#7458643 fix: quantity_adjusted added
2229 CURSOR l_item_txn_csr IS
2230 SELECT mmt.transaction_id transaction_id,
2231 mmt.transaction_type_id transaction_type_id,
2232 mmt.transaction_source_type_id transaction_source_type_id,
2233 mmt.transaction_action_id transaction_action_id,
2234 mmt.transaction_date transaction_date,
2235 mmt.primary_quantity primary_quantity, mmt.actual_cost actual_cost,
2236 mmt.prior_cost prior_cost, mmt.new_cost new_cost,
2237 mmt.value_change value_change,
2238 mmt.percentage_change percentage_change,
2239 mmt.transfer_organization_id transfer_organization_id,
2240 mmt.creation_date creation_date, mmt.quantity_adjusted,
2241 NVL (mmt.subinventory_code, ' ') subinventory_code, mmt.organization_id subinventory_org_id
2242 FROM mtl_material_transactions mmt
2243 WHERE mmt.organization_id = p_org_id
2244 AND mmt.inventory_item_id = p_item_id
2245 AND NVL (mmt.acct_period_id, 0) =
2246 NVL (p_acct_period_id, NVL (mmt.acct_period_id, 0))
2247 AND NVL (mmt.subinventory_code, '0') >=
2248 NVL (p_subinv_from, NVL (mmt.subinventory_code, '0'))
2249 AND NVL (mmt.subinventory_code, '0') <=
2250 NVL (p_subinv_to, NVL (mmt.subinventory_code, '0'))
2251 AND mmt.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
2252 AND ( mmt.subinventory_code IS NULL
2253 OR mmt.subinventory_code =
2254 (SELECT secondary_inventory_name
2255 FROM mtl_secondary_inventories
2256 WHERE secondary_inventory_name = mmt.subinventory_code
2257 AND organization_id = mmt.organization_id
2258 AND quantity_tracked = 1
2259 AND asset_inventory = 1)
2260 )
2261 AND mmt.transaction_id NOT IN (
2262 SELECT mmt1.transaction_id
2263 FROM mtl_material_transactions mmt1
2264 WHERE mmt1.organization_id = p_org_id
2265 AND mmt1.inventory_item_id = p_item_id
2266 AND NVL (mmt1.acct_period_id, 0) =
2267 NVL (p_acct_period_id, NVL (mmt1.acct_period_id, 0))
2268 AND NVL (mmt1.subinventory_code, '0') >=
2269 NVL (p_subinv_from, NVL (mmt1.subinventory_code, '0'))
2270 AND NVL (mmt1.subinventory_code, '0') <=
2271 NVL (p_subinv_to, NVL (mmt1.subinventory_code, '0'))
2272 AND mmt1.transaction_source_type_id = 5
2273 AND mmt1.transaction_action_id = 24
2274 AND NVL (mmt1.owning_tp_type, 2) = 1)
2275 AND mmt.transaction_action_id <> 30
2276 AND NVL (mmt.owning_tp_type, 2) <> 1
2277 ORDER BY mmt.acct_period_id,
2278 mmt.transaction_date,
2279 mmt.creation_date,
2280 mmt.transaction_id;
2281
2282
2283
2284 BEGIN
2285
2286 -- The From date is at midnight for the day
2287 l_per_first_txn_date:=TRUNC(FND_DATE.canonical_to_date(p_per_first_txn_date));
2288 -- The to date is at 23:59:59 of that date entered.
2289 l_per_last_txn_date:=TRUNC(FND_DATE.canonical_to_date(p_per_last_txn_date)) + (86399 / 86400);
2290
2291 l_txn_init_qty := p_item_init_qty;
2292 l_txn_init_infl := p_item_init_infl;
2293
2294
2295 FOR l_item_txn_info IN l_item_txn_csr
2296 LOOP
2297
2298 -- =====================================================================
2299 -- Bug#2799104 fix: Average Cost Update display
2300 -- check whether the transaction is Average Cost Update
2301 -- If so, then value_change is the total cost
2302 -- for all other ACU transactions it is quantity_adjusted * actual_cost
2303 -- Bug#2977020 fix: support percentage and new average cost
2304 -- Set the Total Transaction Cost and primary quantity
2305 -- =====================================================================
2306 IF l_item_txn_info.transaction_source_type_id = 13 AND
2307 l_item_txn_info.transaction_action_id = 24 THEN
2308 -- Average Cost Update transaction
2309 IF l_item_txn_info.value_change IS NOT NULL THEN
2310 -- ACU type is value change
2311 l_primary_qty := 0; -- to avoid double counting of total quantity
2312 l_total_cost := l_item_txn_info.value_change;
2313 ELSIF l_item_txn_info.percentage_change IS NOT NULL THEN
2314 -- ACU type is percentage
2315 l_primary_qty := 0; -- to avoid double counting of total quantity
2316 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
2317 ELSE
2318 -- ACU type New Average Cost
2319 l_primary_qty := 0; -- to avoid double counting of total quantity
2320 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
2321 END IF;
2322
2323 ELSIF l_item_txn_info.transaction_source_type_id = 11 AND
2324 l_item_txn_info.transaction_action_id = 24 THEN
2325 -- FP Bug#7458643 fix: Standard Cost Update transaction
2326 l_primary_qty := 0; -- to avoid double counting of total quantity
2327 l_total_cost := (l_item_txn_info.new_cost - l_item_txn_info.prior_cost) * l_item_txn_info.quantity_adjusted;
2328 ELSE
2329 -- all other transactions
2330 l_primary_qty := l_item_txn_info.primary_quantity;
2331 l_total_cost := l_primary_qty * l_item_txn_info.actual_cost;
2332 END IF;
2333
2334 IF (l_item_txn_info.Actual_Cost IS NULL) THEN
2335 l_item_txn_info.Actual_Cost := 0;
2336 END IF;
2337
2338 IF (l_item_txn_info.Prior_Cost IS NULL) THEN
2339 l_item_txn_info.Prior_Cost := 0;
2340 END IF;
2341
2342 IF (l_item_txn_info.New_Cost IS NULL) THEN
2343 l_item_txn_info.New_Cost := 0;
2344 END IF;
2345
2346 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
2347 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
2348 l_rpt_item_tbl_rec(l_index).uom_code := p_uom_code;
2349 l_rpt_item_tbl_rec(l_index).org_name := p_org_name;
2350 l_rpt_item_tbl_rec(l_index).subinventory_code := l_item_txn_info.subinventory_code; -- Added by ppandit for Italy and China Enhancements
2351 l_rpt_item_tbl_rec(l_index).sub_inv_organization_id := l_item_txn_info.subinventory_org_id; -- Added by ppandit for Italy and China Enhancements
2352 l_rpt_item_tbl_rec(l_index).item_code := p_item_code;
2353 l_rpt_item_tbl_rec(l_index).item_desc := p_item_desc;
2354 l_rpt_item_tbl_rec(l_index).currency_code := p_currency_code;
2355 l_rpt_item_tbl_rec(l_index).txn_date :=
2356 l_item_txn_info.Transaction_Date;
2357 -- Bug 4086259 to insert creation_date into temp table
2358 l_rpt_item_tbl_rec(l_index).creation_date := l_item_txn_info.creation_date;
2359
2360 -- Bug#2904882 fix: transaction_id included for correct sort
2361 l_rpt_item_tbl_rec(l_index).transaction_id :=
2362 l_item_txn_info.Transaction_id;
2363
2364 begin
2365 -- Get Txn_Source_Type_Name
2366 Select Transaction_source_type_name
2367 Into l_rpt_item_tbl_rec(l_index).txn_source
2368 From MTL_TXN_SOURCE_TYPES
2369 Where Transaction_Source_Type_Id = l_item_txn_info.transaction_source_type_id;
2370 exception
2371 when no_data_found then
2372 select 'No TXN Source'
2373 INTO l_rpt_item_tbl_rec(l_index).txn_source
2374 from dual;
2375 end;
2376
2377 Get_Txn_Type
2378 ( p_txn_type_id => l_item_txn_info.Transaction_Type_ID
2379 , x_txn_type_name => l_rpt_item_tbl_rec(l_index).txn_type
2380 );
2381
2382 -- =======================================================================
2383 -- Beginning Balance - Beginning quantity and begin cost for each txn
2384 -- =======================================================================
2385 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
2386 -- Bug#3013597 fix: begin unit cost based on costing method of organization
2387 IF p_primary_cost_method = 1 THEN
2388 -- Standard costing organization
2389 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost :=
2390 l_item_txn_info.prior_Cost;
2391 ELSE
2392 -- Average 2, FIFO 5, LIFO 6 organizations
2393 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost :=
2394 l_item_txn_info.Prior_Cost;
2395 END IF;
2396
2397 -- ==================================================================
2398 -- Beginning balance -- Beginnning quantity * begin cost for each txn
2399 -- ==================================================================
2400 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
2401 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
2402 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
2403 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := l_txn_init_infl;
2404
2405 -- ==================================================================
2406 -- Final quantity for each transaction
2407 -- ==================================================================
2408 -- Total quantity including current transaction quantity
2409 -- Total quantity = Begin qty + transaction quantity
2410 -- For Standard Cost Update or Average Cost Update, primary_quantity
2411 -- will be set to 0 in the earlier logic to avoid double counting
2412 -- ==================================================================
2413 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
2414 l_rpt_item_tbl_rec(l_index).txn_ini_qty +
2415 l_primary_qty;
2416
2417 -- =============================================================
2418 -- Final Cost for each transaction
2419 -- New Cost which is the final cost after processing current txn
2420 -- =============================================================
2421 IF p_primary_cost_method = 1 THEN
2422 -- Standard costing organization
2423 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost :=
2424 l_item_txn_info.New_Cost;
2425 ELSE
2426 -- Average 2, FIFO 5, LIFO 6 organizations
2427 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost :=
2428 l_item_txn_info.New_Cost;
2429 END IF;
2430
2431 -- ==========================================================
2432 -- Final total balance
2433 -- ==========================================================
2434 -- all transactions
2435 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
2436 l_rpt_item_tbl_rec(l_index).txn_fnl_qty *
2437 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost;
2438
2439 IF ((l_item_txn_info.Primary_Quantity > 0)
2440 AND
2441 (l_item_txn_info.Transfer_Organization_ID IS NULL))
2442 OR
2443 (l_item_txn_info.Transfer_Organization_ID = p_org_id)
2444 THEN
2445 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
2446 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
2447 ELSE
2448 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
2449 l_rpt_item_tbl_rec(l_index).txn_fnl_qty * p_item_unit_infl_adj;
2450 END IF;
2451
2452 -- ===============================================================
2453 -- Transaction Quantity and Transaction Cost of each transaction
2454 -- Bug 7458643 fix: For Standard Cost Update - src_type_id is 11.
2455 -- Average Cost Update - src_type_id is 13, l_primary_qty is zero.
2456 -- For all other txns, l_primary_quantity has not null value.
2457 -- ===============================================================
2458 l_rpt_item_tbl_rec(l_index).txn_qty := l_primary_qty;
2459
2460 -- ======================================================
2461 -- Transaction Cost of each transaction
2462 -- ======================================================
2463 -- Bug 7458643 fix: standard cost update
2464 -- transaction unit cost is diff of new_cost - prior_cost
2465 -- for all other txns, actual_cost is a transaction cost
2466 -- Note that for Average Cost Update txn, actual_cost will
2467 -- have item adjustment cost.
2468 -- ======================================================
2469 IF l_item_txn_info.transaction_source_type_id = 11 AND
2470 l_item_txn_info.transaction_action_id = 24 THEN
2471 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
2472 (l_item_txn_info.new_Cost - l_item_txn_info.prior_Cost);
2473 ELSE
2474 l_rpt_item_tbl_rec(l_index).txn_unit_cost :=
2475 l_item_txn_info.Actual_Cost;
2476 END IF;
2477
2478 -- =======================================================
2479 -- Transaction Total Cost -- Txn Cost * txn quantity
2480 -- =======================================================
2481 -- total cost takes the value according to transaction type
2482 -- Bug#2799104 fix
2483 l_rpt_item_tbl_rec(l_index).txn_h_total_cost :=
2484 l_total_cost;
2485 l_rpt_item_tbl_rec(l_index).txn_adj_total_cost :=
2486 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost -
2487 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
2488
2489 -- ====================================================================
2490 -- Set initial quantity to final quantity so far for the next
2491 -- transaction, so that for the next transaction initial quantity will
2492 -- be final qty until previous transaction before the current txn
2493 -- being processed
2494 -- ====================================================================
2495 l_txn_init_qty := l_rpt_item_tbl_rec(l_index).txn_fnl_qty;
2496 l_txn_init_infl := l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost;
2497
2498 l_index := l_index + 1;
2499 END LOOP;
2500
2501 IF NVL(l_rpt_item_tbl_rec.FIRST, 0) = 0
2502 THEN
2503
2504 l_rpt_item_tbl_rec(l_index).organization_id := p_org_id;
2505 l_rpt_item_tbl_rec(l_index).inventory_item_id := p_item_id;
2506 l_rpt_item_tbl_rec(l_index).uom_code := p_uom_code;
2507 l_rpt_item_tbl_rec(l_index).org_name := p_org_name;
2508 l_rpt_item_tbl_rec(l_index).item_code := p_item_code;
2509 l_rpt_item_tbl_rec(l_index).item_desc := p_item_desc;
2510 l_rpt_item_tbl_rec(l_index).currency_code := p_currency_code;
2511 l_rpt_item_tbl_rec(l_index).txn_ini_qty := l_txn_init_qty;
2512
2513
2514 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost := p_item_unit_cost;
2515 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost :=
2516 l_rpt_item_tbl_rec(l_index).txn_ini_qty *
2517 l_rpt_item_tbl_rec(l_index).txn_ini_unit_cost;
2518 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost := p_item_init_infl;
2519 l_rpt_item_tbl_rec(l_index).txn_fnl_qty :=
2520 l_rpt_item_tbl_rec(l_index).txn_ini_qty;
2521 l_rpt_item_tbl_rec(l_index).txn_fnl_unit_cost := p_item_unit_cost;
2522 l_rpt_item_tbl_rec(l_index).txn_fnl_h_total_cost :=
2523 l_rpt_item_tbl_rec(l_index).txn_ini_h_total_cost;
2524 l_rpt_item_tbl_rec(l_index).txn_fnl_adj_total_cost :=
2525 l_rpt_item_tbl_rec(l_index).txn_ini_adj_total_cost;
2526 END IF;
2527
2528 x_rpt_item_tbl_rec := l_rpt_item_tbl_rec;
2529
2530 EXCEPTION
2531
2532 WHEN l_txn_cost_exc THEN
2533 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_UNIT_COST_NULL');
2534 FND_MSG_PUB.Add;
2535 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2536 THEN
2537 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2538 , 'get_item_txn_info'
2539 );
2540 END IF;
2541 RAISE g_txn_cost_exc;
2542 WHEN OTHERS THEN
2543 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2544 THEN
2545 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2546 , 'get_item_txn_info'
2547 );
2548 END IF;
2549 RAISE;
2550
2551 END get_item_txn_info;
2552
2553 --========================================================================
2554 -- PROCEDURE : create_inv_msbk_rpt PUBLIC
2555 -- PARAMETERS: p_org_id Organization ID
2556 -- p_item_from_code Report start item code
2557 -- p_item_to_code Report end item code
2558 -- p_rpt_from_date Report start date
2559 -- p_rpt_to_date Report end date
2560 -- COMMENT : Main procedure called by Inventory Master Book report
2561 --========================================================================
2562 PROCEDURE create_inv_msbk_rpt (
2563 p_org_id IN NUMBER
2564 ,p_category_set_id_from IN NUMBER
2565 ,p_category_set_id_to IN NUMBER
2566 ,p_category_from IN VARCHAR2
2567 ,p_category_to IN VARCHAR2
2568 ,p_subinv_from IN VARCHAR2
2569 ,p_subinv_to IN VARCHAR2
2570 ,p_abc_group_id IN NUMBER
2571 ,p_abc_class_id IN NUMBER
2572 ,p_item_from_code IN VARCHAR2
2573 ,p_item_to_code IN VARCHAR2
2574 ,p_rpt_from_date IN VARCHAR2
2575 ,p_rpt_to_date IN VARCHAR2
2576 )
2577 IS
2578 l_rpt_item_tbl_rec Report_Tbl_Rec_Type;
2579 TYPE lcu_ref_cursor IS REF CURSOR;
2580 lr_ref_cursor lcu_ref_cursor;
2581 l_item_id NUMBER;
2582 l_uom_code VARCHAR2 (3); -- added for inv book
2583 l_item_unit_cost NUMBER;
2584 l_begin_unit_cost NUMBER;
2585 base_period_id NUMBER;
2586 base_qty NUMBER;
2587 txn_count NUMBER;
2588 additional_qty NUMBER;
2589 l_rpt_from_acct_per_id NUMBER;
2590 l_rpt_to_acct_per_id NUMBER;
2591 l_final_infl_adj NUMBER;
2592 l_final_qty NUMBER;
2593 l_purchase_qty NUMBER;
2594 l_unit_infl_adj NUMBER;
2595 l_per_begin_qty NUMBER;
2596 l_begin_infl_adj NUMBER;
2597 l_per_first_txn_date DATE;
2598 l_per_last_txn_date DATE;
2599 l_period_start_date DATE;
2600 l_period_close_date DATE;
2601 l_period_open_flag VARCHAR2(1);
2602 l_msi_segment VARCHAR2(200); -- Added by ppandit
2603 l_cat_segment VARCHAR2(200); -- Added by ppandit
2604 lc_cat_string VARCHAR2(200); -- Added by ppandit
2605 l_struct NUMBER; -- Added by ppandit
2606 l_index BINARY_INTEGER;
2607 d_org_name VARCHAR2(240);
2608 d_currency_code VARCHAR2(15);
2609 d_item_desc VARCHAR2(240);
2610 d_item_code VARCHAR2(40);
2611 subinv_min VARCHAR2(10);
2612 subinv_max VARCHAR2(10);
2613 subinv_from VARCHAR2(10);
2614 subinv_to VARCHAR2(10);
2615 l_rpt_from_date DATE;
2616 l_rpt_to_date DATE;
2617 l_item_ohq NUMBER ;
2618 l_item_trx_qty NUMBER ;
2619
2620 -- primary costing method of an inventory organization
2621 l_primary_cost_method NUMBER;
2622
2623 --variables for dynamic sql query
2624 --v_cursorID INTEGER;
2625 v_select_clause VARCHAR2(4000);
2626 v_from_clause VARCHAR2(4000);
2627 v_where_clause VARCHAR2(8000);
2628 v_order_by VARCHAR2(4000);
2629 v_final_query VARCHAR2(32767);
2630 --v_dummy INTEGER;
2631
2632 -- Cursor to get primary costing method of an inventory organization
2633 CURSOR get_cost_method_cur (c_organization_id NUMBER)
2634 IS
2635 SELECT
2636 primary_cost_method
2637 FROM
2638 mtl_parameters
2639 WHERE
2640 organization_id = c_organization_id;
2641
2642 CURSOR lcu_cat_range (p_cat_set_id In NUMBER)
2643 IS
2644 SELECT category_set_name
2645 FROM mtl_category_sets
2646 WHERE category_set_id = p_cat_set_id;
2647
2648 lc_cat_set_high VARCHAR2 (30);
2649 lc_cat_set_low VARCHAR2 (30);
2650
2651 -- Exception for cost method not found
2652 cost_method_not_found_except EXCEPTION;
2653
2654 BEGIN
2655 -- The From date is at midnight for the day
2656 l_rpt_from_date := TRUNC (FND_DATE.canonical_to_date (p_rpt_from_date));
2657 -- The to date is at 23:59:59 of that date entered.
2658 l_rpt_to_date := TRUNC (FND_DATE.canonical_to_date (p_rpt_to_date)) + (86399 / 86400);
2659
2660 ---- Open Corsor for processing
2661 --v_CursorID := DBMS_SQL.OPEN_CURSOR;
2662
2663 --Bug # 4912772 Performance issue in the dynamic query resolved by restructuring the query
2664
2665 v_select_clause := NULL;
2666 v_from_clause := NULL;
2667 v_where_clause := NULL;
2668 v_order_by := NULL;
2669 v_final_query := NULL;
2670
2671 -- If one of the ranges for a subinventory is missing then retrieve the min and max values
2672 IF p_subinv_from IS NULL AND p_subinv_to IS NOT NULL THEN
2673 SELECT MIN (secondary_inventory_name)
2674 INTO subinv_min
2675 FROM mtl_secondary_inventories
2676 WHERE organization_id = p_org_id;
2677 END IF;
2678
2679 IF p_subinv_from IS NOT NULL AND p_subinv_to IS NULL THEN
2680 SELECT MAX (secondary_inventory_name)
2681 INTO subinv_max
2682 FROM mtl_secondary_inventories
2683 WHERE organization_id = p_org_id;
2684 END IF;
2685
2686 OPEN lcu_cat_range (p_category_set_id_from);
2687 FETCH lcu_cat_range INTO lc_cat_set_high;
2688 CLOSE lcu_cat_range;
2689
2690 OPEN lcu_cat_range (p_category_set_id_to);
2691 FETCH lcu_cat_range INTO lc_cat_set_low;
2692 CLOSE lcu_cat_range;
2693
2694 /* Logic added for inclusion of Category Set Range and dynamic decision on mtl_system_items_b segments by ppandit start */
2695 FOR r_cat IN (
2696 SELECT category_set_id
2697 FROM mtl_category_sets
2698 WHERE category_set_name BETWEEN lc_cat_set_high AND lc_cat_set_low
2699 AND mult_item_cat_assign_flag = 'N'
2700 ORDER BY category_set_id ASC
2701 )
2702 LOOP
2703
2704 l_struct := get_structure_id (r_cat.category_set_id);
2705 l_cat_segment := get_category_structure ('CAT' , l_struct);
2706 l_msi_segment := get_category_structure ('ITEM', 101);
2707
2708 IF l_msi_segment IS NOT NULL THEN
2709 -- Bug#3147073 : exclude expense items
2710 v_select_clause := 'SELECT DISTINCT MSI.inventory_item_id
2711 ,MSI.primary_uom_code
2712 ,' || l_msi_segment ||
2713 ',MSI.description
2714 ,OOD.organization_name
2715 ,GSOB.currency_code ';
2716
2717 v_from_clause := ' FROM mtl_system_items_b MSI
2718 ,gl_sets_of_books GSOB
2719 ,org_organization_definitions OOD
2720 ,mtl_material_transactions MTX ';
2721
2722 v_where_clause := ' WHERE MSI.organization_id = ' || p_org_id ||
2723 ' AND OOD.organization_id = MSI.organization_id
2724 AND OOD.set_of_books_id = GSOB.set_of_books_id
2725 AND MSI.inventory_asset_flag = ''Y''
2726 AND MSI.inventory_item_id = MTX.inventory_item_id
2727 AND MTX.organization_id = ' || p_org_id ||
2728 ' AND MTX.costed_flag IS NULL
2729 AND MTX.transaction_date BETWEEN TO_DATE(''' || p_date_from || ''',''YYYY/MM/DD HH24:MI:SS'') AND TO_DATE(''' || p_date_to || ''',''YYYY/MM/DD HH24:MI:SS'') + (86399 / 86400)';
2730
2731 --main order by clause
2732 v_order_by := ' ORDER BY MSI.inventory_item_id';
2733
2734 IF p_subinv_from IS NOT NULL OR p_subinv_to IS NOT NULL THEN
2735 v_where_clause := v_where_clause || ' AND MTX.subinventory_code >= ''' || p_subinv_from || '''' ||
2736 ' AND MTX.subinventory_code <= ''' || p_subinv_to || '''';
2737 END IF;
2738
2739 IF p_category_from IS NOT NULL AND p_category_to IS NOT NULL
2740 THEN
2741 lc_cat_string := ' AND ' || l_cat_segment || ' BETWEEN ''' || p_category_from || ''' AND ''' || p_category_to || '''';
2742 ELSIF p_category_from IS NULL AND p_category_to IS NULL THEN
2743 lc_cat_string := ' AND 1 = 1 ';
2744 END IF;
2745
2746 v_from_clause := v_from_clause || ', mtl_item_categories MIC
2747 , mtl_category_sets MCS
2748 , mtl_categories_b MCK ';
2749
2750 v_where_clause := v_where_clause || ' AND MIC.organization_id = ' || p_org_id ||
2751 ' AND MIC.category_set_id = MCS.category_set_id
2752 AND MCS.category_set_id = ' || r_cat.category_set_id ||
2753 ' AND MCK.category_id = MIC.category_id ' ||
2754 lc_cat_string ||
2755 ' AND MIC.inventory_item_id = MSI.inventory_item_id ';
2756
2757 IF p_abc_group_id IS NOT NULL AND p_abc_class_id IS NOT NULL THEN
2758 v_from_clause := v_from_clause || ',mtl_abc_classes MAC
2759 ,mtl_abc_assignments MAA
2760 ,mtl_abc_assignment_groups MAG ';
2761
2762 v_where_clause := v_where_clause || ' AND MAC.abc_class_id = MAA.abc_class_id
2763 AND MAA.assignment_group_id = MAG.assignment_group_id
2764 AND MAG.assignment_group_id = ' || p_abc_group_id ||
2765 ' AND MAC.organization_id = ' || p_org_id ||
2766 ' AND MAC.abc_class_id = ' || p_abc_class_id ||
2767 ' AND MAA.inventory_item_id = MSI.inventory_item_id ';
2768
2769 IF p_subinv_from IS NOT NULL OR p_subinv_to IS NOT NULL THEN
2770 v_where_clause := v_where_clause || ' AND MAG.secondary_inventory >= ''' || p_subinv_from || '''' ||
2771 ' AND MAG.secondary_inventory <= ''' || p_subinv_to || '''';
2772 END IF;
2773 ELSE IF p_abc_class_id IS NOT NULL THEN
2774 v_from_clause := v_from_clause || ',mtl_abc_classes MAC
2775 ,mtl_abc_assignments MAA ';
2776 v_where_clause := v_where_clause || ' AND MAC.abc_class_id = MAA.abc_class_id
2777 AND MAC.organization_id = ' || p_org_id ||
2778 ' AND MAC.abc_class_id = ' || p_abc_class_id ||
2779 ' AND MAA.inventory_item_id = MSI.inventory_item_id ';
2780 END IF;
2781 END IF;
2782
2783 IF p_item_from_code IS NOT NULL AND p_item_to_code IS NOT NULL THEN
2784 v_where_clause := v_where_clause || ' AND ' || l_msi_segment || ' BETWEEN ''' || p_item_from_code || ''' AND ''' || p_item_to_code || '''';
2785 END IF;
2786
2787 v_final_query := v_select_clause || v_from_clause || v_where_clause || v_order_by;
2788
2789 /* ppandit Comment Start - Commented by ppandit for Italy and China JF Project */
2790 -- --Parse the query
2791 -- DBMS_SQL.PARSE(v_cursorID, v_final_query, DBMS_SQL.V7);
2792 --
2793 -- -- Not needed as temporary table
2794 -- -- DELETE FROM CST_MGD_MSTR_BOOK_TEMP;
2795 --
2796 -- --bind the input variables
2797 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':org_id',p_org_id);
2798 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':to_date',l_rpt_to_date);
2799 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':from_date',l_rpt_from_date);
2800 --
2801 -- --for selection of items in a subinventory
2802 -- if p_subinv_from is not null then
2803 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_from',p_subinv_from);
2804 -- subinv_from := p_subinv_from;
2805 -- else if p_subinv_to is not null then
2806 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_from',subinv_min);
2807 -- subinv_from := subinv_min;
2808 -- end if;
2809 -- end if;
2810 --
2811 -- if p_subinv_to is not null then
2812 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_to',p_subinv_to);
2813 -- subinv_to := p_subinv_to;
2814 -- else if p_subinv_from is not null then
2815 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':subinv_to',subinv_max);
2816 -- subinv_to := subinv_max;
2817 -- end if;
2818 -- end if;
2819 --
2820 -- --for selection of items in a category
2821 -- if p_category_from is NOT NULL and p_category_to is NOT NULL and p_category_set_id is NOT NULL
2822 -- then
2823 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_from',p_category_from);
2824 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_to',p_category_to);
2825 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':category_set_id',p_category_set_id);
2826 -- end if;
2827 --
2828 --
2829 -- --for selection of items in a abc_class
2830 -- if p_abc_class_id is NOT NULL then
2831 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':abc_class_id',p_abc_class_id);
2832 -- end if;
2833 --
2834 -- if p_abc_group_id is NOT NULL then
2835 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':abc_group_id',p_abc_group_id);
2836 -- end if;
2837 --
2838 -- if p_item_from_code is NOT NULL and p_item_to_code IS NOT NULL then
2839 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':item_from_code',p_item_from_code);
2840 -- DBMS_SQL.BIND_VARIABLE(v_cursorID,':item_to_code',p_item_to_code);
2841 -- end if;
2842 --
2843 -- --define the output variables
2844 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, l_item_id);
2845 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 2, l_uom_code,3);
2846 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 3, d_item_code,40);
2847 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 4, d_item_desc,240);
2848 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 5, d_org_name,240);
2849 -- DBMS_SQL.DEFINE_COLUMN(v_cursorID, 6, d_currency_code,15);
2850 --
2851 -- --execute the sql statement we don't care about the return value
2852 -- v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
2853
2854 /* Comment by ppandit End */
2855
2856 -- Not needed as the On Hand Qty Does not rely on the MTL_PER_CLOSE_DTLS table
2857 -- Get_Acct_Period_ID_invmbk
2858 -- ( p_org_id => p_org_id
2859 -- , p_rpt_from_date => p_rpt_from_date
2860 -- , p_rpt_to_date => p_rpt_to_date
2861 -- , x_rpt_from_acct_per_id => l_rpt_from_acct_per_id
2862 -- , x_rpt_to_acct_per_id => l_rpt_to_acct_per_id
2863 -- );
2864
2865 -- Get costing method of organization
2866
2867 IF get_cost_method_cur%ISOPEN THEN
2868 CLOSE get_cost_method_cur;
2869 END IF;
2870
2871 OPEN get_cost_method_cur (p_org_id);
2872 FETCH get_cost_method_cur
2873 INTO l_primary_cost_method;
2874
2875 IF get_cost_method_cur%NOTFOUND THEN
2876 RAISE cost_method_not_found_except;
2877 END IF;
2878
2879 --LOOP -- Commented by ppandit for Italy and China JF Project
2880
2881 IF lr_ref_cursor%ISOPEN THEN
2882 CLOSE lr_ref_cursor;
2883 END IF;
2884
2885 OPEN lr_ref_cursor FOR v_final_query; -- Added by ppandit for Italy and China JF Project
2886 LOOP
2887 FETCH lr_ref_cursor INTO l_item_id, l_uom_code, d_item_code, d_item_desc, d_org_name, d_currency_code;
2888 EXIT WHEN lr_ref_cursor%NOTFOUND;
2889
2890 /* ppandit Comment Start - Commented by ppandit for Italy and China JF Project */
2891 -- -- fetch the rows and also check for exit condition
2892 --
2893 -- IF DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 THEN
2894 -- EXIT;
2895 -- END IF;
2896 --
2897 -- --retrieve the rows from the buffer into PL/SQL variables
2898 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_item_id);
2899 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,2,l_uom_code);
2900 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,3,d_item_code);
2901 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,4,d_item_desc);
2902 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,5,d_org_name);
2903 -- DBMS_SQL.COLUMN_VALUE(v_cursorID,6,d_currency_code);
2904 /* Comment by ppandit End */
2905
2906 -- First re initialize local var
2907 l_item_ohq := 0;
2908 l_item_trx_qty := 0;
2909 l_per_begin_qty := 0;
2910
2911 -- First get the Acutual On Hand Qty for the Ite, Org_id combination :
2912 -- Bug#2576310 to add the sub inventory range the where condition
2913 -- bug#3147073 : exclude non asset subinventories
2914 SELECT NVL (SUM (transaction_quantity), 0)
2915 INTO l_item_ohq
2916 FROM mtl_onhand_quantities
2917 WHERE inventory_item_id = l_item_id
2918 AND organization_id = p_org_id
2919 AND subinventory_code BETWEEN NVL (p_subinv_from, subinventory_code)
2920 AND NVL (p_subinv_to, subinventory_code)
2921 AND subinventory_code NOT IN (
2922 SELECT secondary_inventory_name
2923 FROM mtl_secondary_inventories
2924 WHERE organization_id = p_org_id
2925 AND asset_inventory = 2);
2926
2927 -- Get the qty between NOW and the P_FROM_DATE
2928 -- Bug#2198569 to excluded wip transactions
2929 -- transaction action id: 24 for wip cost update
2930 -- transaction action id: 30 for wip scrap transaction
2931 -- Bug#2576310 to add the sub inventory range the where condition
2932 -- Bug#2865534 fix: get only regular stock and exclude consigned stock
2933 -- for regular stock: organization_id is equal to owning_organization_id
2934 -- for consigned stock: organization_id is the inventory organization
2935 -- and owning_organization_id is the supplier organization.
2936 -- owning_tp_type will be 1 for consigned transaction
2937 -- bug#3118846 fix: exclude TRX from NON Qty tracked subinventories
2938 SELECT NVL (SUM (mmt.primary_quantity), 0)
2939 INTO l_item_trx_qty
2940 FROM mtl_material_transactions mmt
2941 WHERE mmt.organization_id = p_org_id
2942 AND mmt.inventory_item_id = l_item_id
2943 AND mmt.subinventory_code BETWEEN NVL (p_subinv_from,
2944 mmt.subinventory_code)
2945 AND NVL (p_subinv_to, mmt.subinventory_code)
2946 AND ( mmt.subinventory_code IS NULL
2947 OR mmt.subinventory_code =
2948 (SELECT secondary_inventory_name
2949 FROM mtl_secondary_inventories
2950 WHERE secondary_inventory_name = mmt.subinventory_code
2951 AND organization_id = mmt.organization_id
2952 AND quantity_tracked = 1
2953 AND asset_inventory = 1)
2954 )
2955 AND mmt.transaction_id NOT IN (
2956 SELECT mmt1.transaction_id
2957 FROM mtl_material_transactions mmt1
2958 WHERE mmt1.organization_id = p_org_id
2959 AND mmt1.inventory_item_id = l_item_id
2960 AND mmt1.subinventory_code BETWEEN NVL (p_subinv_from,
2961 mmt1.subinventory_code
2962 )
2963 AND NVL (p_subinv_to,
2964 mmt1.subinventory_code
2965 )
2966 AND mmt1.transaction_source_type_id = 5
2967 AND mmt1.transaction_action_id = 24
2968 AND NVL (mmt1.owning_tp_type, 2) = 1)
2969 AND mmt.transaction_action_id <> 30
2970 AND NVL (mmt.owning_tp_type, 2) <> 1
2971 AND mmt.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND SYSDATE;
2972
2973 -- On Hand Qty to begin = Actual On Hand MINUS sum of Trx qty form Now to begin date
2974 l_per_begin_qty := l_item_ohq - l_item_trx_qty ;
2975
2976 l_begin_infl_adj := 0;
2977 l_final_infl_adj := 0;
2978 l_final_qty := 0;
2979
2980
2981 IF l_final_qty = 0
2982 THEN
2983 l_unit_infl_adj := 0;
2984 ELSE
2985 l_unit_infl_adj := l_final_infl_adj/l_final_qty;
2986 END IF;
2987
2988 BEGIN
2989 -- Bug#2576310 fix subinventory code to add in the WHERE condition
2990 SELECT NVL (mmt.prior_cost, mmt.actual_cost)
2991 INTO l_item_unit_cost
2992 FROM mtl_material_transactions mmt
2993 WHERE mmt.transaction_id =
2994 (SELECT MIN (transaction_id)
2995 FROM mtl_material_transactions
2996 WHERE organization_id = p_org_id
2997 AND inventory_item_id = l_item_id
2998 AND transaction_date =
2999 (SELECT MIN (transaction_date)
3000 FROM mtl_material_transactions
3001 WHERE organization_id = p_org_id
3002 AND transaction_action_id NOT IN (24, 30)
3003 AND NVL (owning_tp_type, 2) <> 1
3004 AND transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS')
3005 AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
3006 AND inventory_item_id = l_item_id
3007 AND subinventory_code BETWEEN NVL (p_subinv_from,
3008 subinventory_code
3009 )
3010 AND NVL (p_subinv_to,
3011 subinventory_code
3012 )))
3013 AND ( mmt.subinventory_code IS NULL
3014 OR mmt.subinventory_code =
3015 (SELECT secondary_inventory_name
3016 FROM mtl_secondary_inventories
3017 WHERE secondary_inventory_name = mmt.subinventory_code
3018 AND organization_id = mmt.organization_id
3019 AND quantity_tracked = 1
3020 AND asset_inventory = 1)
3021 )
3022 AND mmt.transaction_id NOT IN (
3023 SELECT mmt1.transaction_id
3024 FROM mtl_material_transactions mmt1
3025 WHERE mmt1.organization_id = p_org_id
3026 AND mmt1.inventory_item_id = l_item_id
3027 AND NVL (mmt1.owning_tp_type, 2) = 1
3028 AND mmt1.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
3029 AND mmt1.subinventory_code BETWEEN NVL (p_subinv_from,
3030 mmt1.subinventory_code
3031 )
3032 AND NVL (p_subinv_to,
3033 mmt1.subinventory_code
3034 )
3035 AND mmt1.transaction_source_type_id = 5
3036 AND mmt1.transaction_action_id = 24)
3037 AND mmt.transaction_action_id <> 30
3038 AND NVL (mmt.owning_tp_type, 2) <> 1;
3039
3040 EXCEPTION
3041 WHEN NO_DATA_FOUND THEN
3042 NULL;
3043 END;
3044
3045 IF l_item_unit_cost IS NULL THEN l_item_unit_cost := 0; END IF;
3046
3047 get_item_txn_info
3048 ( p_org_id => p_org_id
3049 , p_item_id => l_item_id
3050 , p_uom_code => l_uom_code
3051 , p_item_code => d_item_code
3052 , p_item_desc => d_item_desc
3053 , p_org_name => d_org_name
3054 , p_currency_code => d_currency_code
3055 , p_subinv_from => p_subinv_from
3056 , p_subinv_to => p_subinv_to
3057 , p_acct_period_id => NULL
3058 , p_per_first_txn_date => p_rpt_from_date
3059 , p_per_last_txn_date => p_rpt_to_date
3060 , p_item_unit_cost => l_item_unit_cost
3061 , p_primary_cost_method => l_primary_cost_method
3062 , p_item_init_qty => l_per_begin_qty
3063 , p_item_init_infl => 0
3064 , p_item_unit_infl_adj => 0
3065 , x_rpt_item_tbl_rec => l_rpt_item_tbl_rec
3066 );
3067
3068 l_index := NVL(l_rpt_item_tbl_rec.FIRST, 0);
3069 IF l_index > 0
3070 THEN
3071 LOOP
3072
3073 Insert_Rpt_Data
3074 ( p_rpt_item_rec => l_rpt_item_tbl_rec(l_index)
3075 );
3076 EXIT WHEN l_index = l_rpt_item_tbl_rec.LAST;
3077 l_index := l_rpt_item_tbl_rec.NEXT(l_index);
3078 END LOOP;
3079 END IF;
3080
3081 END LOOP;
3082 CLOSE lr_ref_cursor;
3083
3084 END IF;
3085
3086 END LOOP;
3087 /* Logic added for inclusion of Category Set Range and dynamic decision on mtl_system_items_b segments by ppandit end */
3088
3089 EXCEPTION
3090 WHEN cost_method_not_found_except THEN
3091 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3092 THEN
3093 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
3094 , 'Create_Inv_Msbk_Rpt' || ' Cost Method Not Found'
3095 );
3096 END IF;
3097 RAISE;
3098
3099 WHEN OTHERS THEN
3100 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3101 THEN
3102 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
3103 , 'Create_Inv_Msbk_Rpt'
3104 );
3105 END IF;
3106 RAISE;
3107
3108 END create_inv_msbk_rpt;
3109
3110 END CST_MGD_MSTR_BOOK_RPT;
[Home] [Help]