4 --| Copyright (c) 2000 Oracle Corporation Belmont, California, USA |
1 PACKAGE BODY INV_ITEM_ATTRIBUTES_PKG AS
2 /* $Header: INVGIAPB.pls 120.2.12020000.2 2012/11/14 09:36:08 liswang ship $ */
3 --/*==========================================================================+
5 --| All rights reserved. |
6 --+===========================================================================+
7 --| |
8 --| File Name : invgiapb.pls |
12 --| be constructed, based on the pl/sql table of records which |
9 --| Description : Item attribute processor for the Item Attribute copy form. |
10 --| Creates a pl/sql table of records and populates it with |
11 --| information seeded in the AK dictionary. Queries can then |
13 --| retreive the date to populate MTL_ITEM_ATTRIBUTES_TEMP, on |
14 --| which the form is based. |
15 --| |
16 --| Revision |
17 --| 13-Sep-00 dherring Created |
18 --| 17-JUL-01 dherring Updated with performance enhancements |
19 --| 06-NOV-02 vjavli Bug#2643619 fix: more than one SEGMENT in system |
20 --| items flex field - build_item_cursor procedure to|
21 --| build the dynamic cursor to select the items |
22 --| 21-NOV-02 vma Performance: modify code to print to log only if |
23 --| debug profile option is enabled; add NOCOPY to |
24 --| OUT parameters of find_org_list, get_type_struct,|
25 --| call_item_update |
26 --| 20-FEB-03 vjavli Bug#2808261 fix: organization item records is |
27 --| getting repeated. Found that when there is no |
28 --| item for that organization, it should not be |
29 --| inserted in MTL_ITEM_ATTRIBUTES_TEMP |
30 --| 18-MAR-03 vjavli Bug#2855692 fix: all items should be displayed |
31 --| in the items range for all the organizations |
32 --| procedure: populate_temp_table modified with |
33 --| WHILE loop for the item_cursor |
34 --| 09-FEB-04 vjavli GSCC fix: file.sql.47 |
35 --| FND_INSTALLATION.get_app_info used to obtain |
36 --| owner of database schema. This will be used in |
37 --| WHERE condition to compare with owner along with |
38 --| table_name of all_tab_columns |
39 --| 10-FEB-04 nkilleda Bug#3148944 fix: Unapproved items should be |
40 --| excluded from item range when item from and item |
41 --| to fields are input by user. These items are PLM |
42 --| items and should not be visible to an ERPuser. |
43 --| Modified Build_Item_Cursor (l_mstk_w) |
44 --| 11-Mar-04 TMANDA Bug#3497035 : Replaced p_cat_set_id is not null |
45 --| with p_cat_set_id <> -1 in populate_temp_table |
46 --| and Build_Item_Cursor procedures. |
47 --| Commented unncessary ELSE clauses where the |
48 --| variables are being set to null. |
49 --| 21-May-04 vto Bug 3571949: Fixed issue with folder prompts not |
50 --| translated. |
51 --| 14-NOV-04 nesoni Bug# 3770547. Procedure populate_temp_table and |
52 --| Build_Item_Cursor have been modified to |
53 --| incorporate attribute_category as additional IN |
54 --| parameter for filtering items for coping. Added |
55 --| NOCOPY to OUT parameters. |
56 --| 16-DEC-04 nesoni BUG #4025750. Procedure populate_temp_table |
57 --| modified to incorporate CopyDffToNull as |
58 --| additional IN parameter. |
59 --| 20-DEC-04 nesoni BUG #4064005. Procedure populate_temp_table |
60 --| and Build_item_Cursor are modified to accept |
61 --| attribute_category as bind parameter. |
62 --| 20-DEC-04 MYERRAMS BUG #5001785. Modified the Update queries to |
63 --| use bind variables instead of SQL Literals to |
64 --| improve the performance. |
65 --+==========================================================================*/
66
67 --=================
68 -- CONSTANTS
69 --=================
70
71 G_INV_ITEM_ATTRIBUTES_PKG VARCHAR2(30) := 'INV_ITEM_ATTRIBUTES_PKG';
72
73 --==================
74 -- GLOBAL VARIABLES
75 --==================
76
77 g_att_tab att_tbl_type;
78 g_sel_tab sel_tbl_type;
79 g_cho_rec cho_rec_type;
80 g_current_att_index BINARY_INTEGER := 0;
81 g_current_sel_index BINARY_INTEGER := 0;
82 g_empty_att_tab att_tbl_type;
83 g_empty_sel_tab sel_tbl_type;
84 g_itm_rec inv_item_grp.item_rec_type;
85 g_org_tab INV_ORGHIERARCHY_PVT.orgid_tbl_type;
86 g_current_org_index BINARY_INTEGER := 0;
87 g_empty_org_tab INV_ORGHIERARCHY_PVT.orgid_tbl_type;
88 g_count NUMBER := 0;
89 g_unit_test_mode BOOLEAN := FALSE;
90 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_ATT_MTN';
91 G_SLEEP_TIME NUMBER := 15;
92 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
93
94 g_submit_failure_exc EXCEPTION;
95
96 TYPE g_request_tbl_type IS TABLE OF NUMBER
97 INDEX BY BINARY_INTEGER;
98 TYPE g_item_cur_type IS REF CURSOR;
99
100
101 --=========================================================================
102 -- PROCEDURE : get_type_struct PRIVATE
103 -- PARAMETERS :
104 -- COMMENT : initialize the pl/sql table with the list user selected
105 -- attributes
109 -- If the PL/SQL table of records has already been initialized
106 -- This code needed to initialize the pl/sql table with these
107 -- user selected attributes. The pl/sql table is global and it's
108 -- contents can then bew accessed by other apis in this package.
110 -- it's id is simply passed back to the calling procedure.
111 -- PRE-COND : This procedure will be called from the form
112 --=========================================================================
113 PROCEDURE get_type_struct
114 (p_att_tab OUT NOCOPY ATT_TBL_TYPE
115 ,p_cho_rec OUT NOCOPY CHO_REC_TYPE
116 ,p_sel_tab OUT NOCOPY SEL_TBL_TYPE
117 )
118 IS
119
120 --=================
121 -- LOCAL VARIABLES
122 --=================
123
124 BEGIN
125
126 p_att_tab := g_att_tab;
127 p_cho_rec := g_cho_rec;
128 p_sel_tab := g_sel_tab;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
133 THEN
134 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
135 , 'get_type_struct'
136 );
137 END IF;
138 RAISE;
139 END get_type_struct;
140
141 --=========================================================================
142 -- PROCEDURE : set_type_struct PRIVATE
143 -- PARAMETERS :
144 -- COMMENT : allows the form to populate
145 -- the two pl/sql tables with the chosen
146 -- record values and the unique id of the
147 -- records to be updated
148 -- PRE-COND : This procedure will be called from the form
149 --=========================================================================
150 PROCEDURE set_type_struct
151 (p_att_tab IN ATT_TBL_TYPE
152 ,p_cho_rec IN CHO_REC_TYPE
153 ,p_sel_tab IN SEL_TBL_TYPE
154 )
155 IS
156
157 --=================
158 -- LOCAL VARIABLES
159 --=================
160
161 BEGIN
162
163 g_att_tab := p_att_tab;
164 g_cho_rec := p_cho_rec;
165 g_sel_tab := p_sel_tab;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
170 THEN
171 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
172 , 'set_type_struct'
173 );
174 END IF;
175 RAISE;
176 END set_type_struct;
177
178 --=========================================================================
179 -- PROCEDURE : populate_type_struct PRIVATE
180 -- PARAMETERS :
181 -- COMMENT : populate the pl/sql table of records with approprite
182 -- data to construct the query to feed the populate temp
183 -- table procedure.
184 -- The ak meta model is normalized. Most of the information
185 -- we required is scattered amongst many tables.
186 -- This procedure uses 6 simple queries to populate
187 -- the records. This avoids a costly single query with
188 -- a join involving 6 tables.
189 -- PRE-COND : This procedure must be fed a list of columns selected
190 -- for display by the user using the find canvas of the
191 -- item_attributes_copy form.
192 --=========================================================================
193 PROCEDURE populate_type_struct(p_att_tab IN ATT_TBL_TYPE)
194 IS
195
196 BEGIN
197
198 -- Initialize on the first user selected attribute
199
200 g_att_tab := p_att_tab;
201 g_current_att_index := g_att_tab.FIRST;
202 g_count := g_att_tab.COUNT;
203
204 LOOP
205
206 g_att_tab(g_current_att_index).temp_column_name :=
207 RPAD('ATTRIBUTE',12,LPAD(TO_CHAR(g_current_att_index + 1),3,'0'));
208
209 /* Added for Bug 4064006 */
210 SELECT COLUMN_TYPE
211 ,LOOKUP_TABLE
212 ,LOOKUP_COLUMN
213 ,LOOKUP_TYPE
214 ,LOOKUP_TYPE_VALUE
215 ,REFERENCE_KEY_COLUMN
216 INTO g_att_tab(g_current_att_index).column_type
217 ,g_att_tab(g_current_att_index).lookup_table
218 ,g_att_tab(g_current_att_index).lookup_column
219 ,g_att_tab(g_current_att_index).lookup_type
220 ,g_att_tab(g_current_att_index).lookup_type_value
221 ,g_att_tab(g_current_att_index).reference_key_column
222 FROM MTL_ITEM_ATTRIBUTES_SEED_INFO
223 WHERE attribute_code = g_att_tab(g_current_att_index).item_column_name
224 ORDER BY rowid;
225
226 /*SELECT default_value_varchar2
227 ,display_value_length
228 INTO g_att_tab(g_current_att_index).foreign_key_name
229 ,g_att_tab(g_current_att_index).column_type
230 FROM ak_object_attributes
231 WHERE database_object_name = 'MTL_SYSTEM_ITEMS_VL'
232 AND attribute_code = g_att_tab(g_current_att_index).item_column_name
233 ORDER BY rowid;
234
235 -- If the column is a flexfield then do not overwrite
236 -- the display column entry in the table of records
237 -- because that has already been populated with
238 -- the display name in the forms package
239 -- FND_ATTR_AVAILABLE
240
241 IF g_att_tab(g_current_att_index).column_type <> 4
242 THEN
243
244 SELECT attribute_label_long
245 INTO g_att_tab(g_current_att_index).display_column
246 FROM ak_object_attributes_vl
247 WHERE database_object_name = 'MTL_SYSTEM_ITEMS_VL'
248 AND attribute_code = g_att_tab(g_current_att_index).item_column_name;
249
250 END IF;
251
252 IF g_att_tab(g_current_att_index).foreign_key_name IS NOT NULL
256 SELECT from_to_name
253 AND g_att_tab(g_current_att_index).column_type = 1
254 THEN
255
257 ,to_from_name
258 INTO g_att_tab(g_current_att_index).lookup_table
259 ,g_att_tab(g_current_att_index).lookup_column
260 FROM ak_foreign_keys_tl
261 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
262 AND language = 'US';
263
264 SELECT from_to_description
265 ,to_from_description
266 INTO g_att_tab(g_current_att_index).lookup_type
267 ,g_att_tab(g_current_att_index).lookup_type_value
268 FROM ak_foreign_keys_tl
269 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
270 AND language = 'US';
271
272 SELECT attribute_code
273 INTO g_att_tab(g_current_att_index).foreign_key_column
274 FROM ak_foreign_key_columns
275 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name;
276
277 SELECT attribute_code
278 INTO g_att_tab(g_current_att_index).reference_key_column
279 FROM ak_unique_key_columns
280 WHERE unique_key_name = g_att_tab(g_current_att_index).lookup_table;
281
282 ELSIF g_att_tab(g_current_att_index).foreign_key_name IS NOT NULL
283 AND g_att_tab(g_current_att_index).column_type = 2
284 THEN
285
286 SELECT from_to_name
287 ,to_from_name
288 INTO g_att_tab(g_current_att_index).lookup_table
289 ,g_att_tab(g_current_att_index).lookup_column
290 FROM ak_foreign_keys_tl
291 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
292 AND language = 'US';
293
294 SELECT from_to_description
295 ,to_from_description
296 INTO g_att_tab(g_current_att_index).lookup_type
297 ,g_att_tab(g_current_att_index).lookup_type_value
298 FROM ak_foreign_keys_tl
299 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
300 AND language = 'US';
301
302 SELECT attribute_code
303 INTO g_att_tab(g_current_att_index).foreign_key_column
304 FROM ak_foreign_key_columns
305 WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name;
306
307 END IF;
308 */
309 EXIT WHEN g_current_att_index = g_att_tab.LAST;
310
311 g_current_att_index := g_att_tab.NEXT(g_current_att_index);
312
313 END LOOP;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
318 THEN
319 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
320 , 'populate_type_struct'
321 );
322 END IF;
323 RAISE;
324 END populate_type_struct;
325
326 --=========================================================================
327 -- PROCEDURE : clear_type_struct PRIVATE
328 -- PARAMETERS :
329 -- COMMENT : clear the pl/sql table
330 -- PRE-COND : This procedure will be called from the form
331 --=========================================================================
332 PROCEDURE clear_type_struct
333 IS
334
335 BEGIN
336
337 -- clear the pl/sql table before use
338 g_att_tab := g_empty_att_tab;
339
340 -- reset global index
341 g_current_att_index := 0;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
346 THEN
347 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
348 , 'clear_type_struct'
349 );
350 END IF;
351 RAISE;
352 END clear_type_struct;
353
354 --========================================================================
355 -- PROCEDURE : Build_Item_Cursor PRIVATE
356 -- PARAMETERS: p_category_id IN Category Id
357 -- p_category_set_id IN Category Set Id
361 -- p_organization_id IN Organization id
358 -- p_item_id IN Inventory Item Id
359 -- p_item_from IN Item Number From
360 -- p_item_to IN Item Number To
362 -- p_sts_code IN Inventory Item Status Code
363 -- p_attribute_category IN VARCHAR2
364 -- p_copy_dff_to_null IN VARCHAR2
365 -- p_attribute_category IN VARCHAR2
366 -- x_item_cursor OUT NOCOPY item cursor statement
367 -- COMMENT : This procedure builds the item cursor statement. This statement
368 -- needs to be built at run time (dynamic SQL) because of the
369 -- dynamic nature of the System Item flexfield.
370 -- This procedure introduced as part of bug#2643619 fix for
371 -- more than one SEGMENT issue
372 --=========================================================================
373 /* Bug: 3770547
374 One more input parameter AttributeCategory added to find items that need to be populated*/
375 /* Bug: 4025750
376 One more filter parameter p_copy_dff_to_null added to find items that need to be populated*/
377 PROCEDURE Build_Item_Cursor
378 ( p_category_id IN NUMBER
379 , p_category_set_id IN NUMBER
380 , p_item_id IN NUMBER
381 , p_item_from IN VARCHAR2
382 , p_item_to IN VARCHAR2
383 , p_organization_id IN NUMBER
384 , p_sts_code IN VARCHAR2
385 , p_attribute_category IN VARCHAR2
386 , p_copy_dff_to_null IN VARCHAR2
387 , x_item_cursor IN OUT NOCOPY VARCHAR2
388 )
389 IS
390 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
391 l_structure_rec FND_FLEX_KEY_API.structure_type;
392 l_segment_rec FND_FLEX_KEY_API.segment_type;
393 l_segment_tbl FND_FLEX_KEY_API.segment_list;
394 l_segment_number NUMBER;
395 l_mstk_segs VARCHAR2(850);
396 l_mcat_f VARCHAR2(2000);
397 l_mcat_w1 VARCHAR2(2000);
398 l_mstk_w VARCHAR2(2000);
399 l_sts_w VARCHAR2(2000);
400
401 l_category_id NUMBER;
402 l_category_set_id NUMBER;
403 l_item_id NUMBER;
404 l_item_from VARCHAR2(2000);
405 l_item_to VARCHAR2(2000);
406 l_organization_id NUMBER;
407 l_sts_code VARCHAR2(2000);
408 -- Bug: 3770547.
409 l_dff_w VARCHAR2(2000);
410 BEGIN
411
412 IF G_DEBUG = 'Y' THEN
413 INV_ORGHIERARCHY_PVT.Log
414 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
415 , '> Build_Item_Cursor'
416 );
417 END IF;
418
419 FND_FLEX_KEY_API.set_session_mode('customer_data');
420
421 -- retrieve system item concatenated flexfield
422 l_mstk_segs := '';
423 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
424 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
425 FND_FLEX_KEY_API.get_segments
426 ( flexfield => l_flexfield_rec
427 , structure => l_structure_rec
428 , nsegments => l_segment_number
429 , segments => l_segment_tbl
430 );
431 FOR l_idx IN 1..l_segment_number LOOP
432 l_segment_rec := FND_FLEX_KEY_API.find_segment
433 ( l_flexfield_rec
434 , l_structure_rec
435 , l_segment_tbl(l_idx)
436 );
437 l_mstk_segs := l_mstk_segs ||'itm.'||l_segment_rec.column_name;
438 IF l_idx < l_segment_number THEN
439 l_mstk_segs := l_mstk_segs||'||'||''''||l_structure_rec.segment_separator||''''||'||';
440 END IF;
441 END LOOP;
442
443 IF G_DEBUG = 'Y' THEN
444 INV_ORGHIERARCHY_PVT.Log
445 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
446 , 'item flexfield segments:'||l_mstk_segs
447 );
448 END IF;
449
450 IF p_item_from IS NOT NULL AND p_item_to IS NOT NULL THEN
451 l_mstk_w := ' AND '||l_mstk_segs||' BETWEEN :l_item_from
452 AND :l_item_to';
453 ELSIF p_item_from IS NOT NULL AND p_item_to IS NULL THEN
454 l_mstk_w := ' AND '||l_mstk_segs||' >= :l_item_from';
455 ELSIF p_item_from IS NULL AND p_item_to IS NOT NULL THEN
456 l_mstk_w := ' AND '||l_mstk_segs||' <= :l_item_to';
457 ELSIF p_item_id <> -1 THEN
458 l_mstk_w := ' AND itm.inventory_item_id = :l_item_id';
459 --Bug#3497035
460 -- ELSE
461 -- l_mstk_w := NULL;
462 END IF;
463 --
464 -- Bug 3418944 : Modified by NKILLEDA
465 --
466 -- Added the check to remove the items that are not approved
467 -- ( => approval_status <> 'A' ) from the range of items.
468 --
469 -- Unapproved items are created in PLM and they should not be
470 -- to be visible to an ERP user.
471 --
472 IF l_mstk_w IS NOT NULL THEN
473 l_mstk_w := l_mstk_w ||' AND NVL(itm.approval_status,''A'') = ''A''';
474 END IF;
475
476 --Bug#3497035
477 -- l_mcat_f := ', mtl_item_categories mic';
478
479 IF p_category_id <> -1 AND p_category_set_id <> -1 THEN
480 --Bug#3497035
481 -- IF p_category_id <> -1 AND p_category_set_id IS NOT NULL THEN
482 l_mcat_f := ', mtl_item_categories mic';
483 l_mcat_w1 := ' AND mic.organization_id = itm.organization_id' ||
484 ' AND mic.inventory_item_id = itm.inventory_item_id' ||
485 ' AND mic.category_set_id = :l_category_set_id' ||
486 ' AND mic.category_id = :l_category_id';
487 --Bug#3497035
488 -- ELSE
489 -- l_mcat_f := NULL;
490 -- l_mcat_w1 := NULL;
491 END IF;
492
493 IF p_sts_code IS NOT NULL THEN
497 -- l_sts_w := NULL;
494 l_sts_w := ' AND itm.inventory_item_status_code LIKE :l_sts_code';
495 --Bug#3497035
496 -- ELSE
498 END IF;
499
500
501 /* Bug: 3770547. Verify if AttributeCategory is present then
502 * construct appropriate where clasue and update query accordingly. */
503 IF p_attribute_category IS NOT NULL THEN
504 --Modified for bug 4025750.
505 IF p_copy_dff_to_null IS NULL OR p_copy_dff_to_null = 'NO' THEN
506 l_dff_w := ' AND itm.attribute_category = :l_attribute_category ';
507 ELSIF p_copy_dff_to_null = 'YES' THEN
508 l_dff_w := ' AND (itm.attribute_category IS NULL OR itm.attribute_category = :l_attribute_category )';
509 END IF;
510 ELSE
511 l_dff_w := NULL;
512 END IF;
513
514 x_item_cursor := 'SELECT DISTINCT
515 par.organization_code
516 , par.organization_id ' ||
517 ', ' || l_mstk_segs ||
518 ', itm.inventory_item_id' ||
519 ' FROM mtl_system_items_b itm' ||
520 ' , mtl_parameters par' ||
521 l_mcat_f ||
522 ' WHERE itm.organization_id = par.organization_id' ||
523 ' AND itm.organization_id = :organization_id' ||
524 l_mstk_w ||
525 l_mcat_w1 ||
526 l_sts_w ||
527 l_dff_w;
528
529 IF G_DEBUG = 'Y' THEN
530 INV_ORGHIERARCHY_PVT.Log
531 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
532 , SUBSTR(x_item_cursor, 1, 250)
533 );
534 INV_ORGHIERARCHY_PVT.Log
535 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
536 , SUBSTR(x_item_cursor, 251, 500)
537 );
538 INV_ORGHIERARCHY_PVT.Log
539 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
540 , SUBSTR(x_item_cursor, 501, 750)
541 );
542
543 INV_ORGHIERARCHY_PVT.Log
544 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
545 , '< Build_Item_Cursor'
546 );
547 END IF;
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF G_DEBUG = 'Y' THEN
551 INV_ORGHIERARCHY_PVT.Log
552 ( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
553 , FND_FLEX_KEY_API.message
554 );
555 END IF;
556 RAISE;
557
558 END Build_Item_Cursor;
559
560 --=========================================================================
561 -- PROCEDURE : populate_temp_table PUBLIC
562 -- PARAMETERS :
563 -- COMMENT : contruct queries to populate the temp table
564 -- PRE-COND : the table of records must contain all the info necessary
565 -- : to contruct these queries.
566 --=========================================================================
567 /* Bug: 3770547
568 One more input parameter AttributeCategory added to find items that need to be populated*/
569 /* Bug: 4025750
570 One more filter parameter p_copy_dff_to_null added to find items that need to be populated*/
571 PROCEDURE populate_temp_table
572 (p_item_id IN NUMBER
573 ,p_org_code_list IN INV_ORGHIERARCHY_PVT.orgid_tbl_type
574 ,p_cat_id IN NUMBER
575 ,p_cat_set_id IN NUMBER
576 ,p_item_low IN VARCHAR2
577 ,p_item_high IN VARCHAR2
578 ,p_sts_code IN VARCHAR2
579 ,p_attribute_category IN VARCHAR2
580 ,p_copy_dff_to_null IN VARCHAR2)--Added p_copy_dff_to_null parameter for Bug 4025750.
581 IS
582
583 --=================
584 -- LOCAL VARIABLES
585 --=================
586
587 l_dml_str VARCHAR2(250);
588 l_bu_id NUMBER;
589 l_org_id NUMBER;
590 l_org_index BINARY_INTEGER;
591
592 -- ======================
593 -- Dynamic Cursor Variable
594 -- =======================
595 TYPE g_item_cur_type IS REF CURSOR;
596 l_item_cur g_item_cur_type;
597
598 -- Variable to hold the SELECT statement
599 l_item_cursor VARCHAR2(4000);
600
601 -- =========================================
602 -- Dynamic Cursor SELECT statement variables
603 -- =========================================
604 l_organization_code VARCHAR2(3);
605 l_organization_id NUMBER;
606 l_item_number VARCHAR2(1025);
607 l_inventory_item_id NUMBER;
608 l_scenario_id NUMBER;
609
610 /* Added for Bug 4064005.*/
611 item_low_input boolean;
612 item_high_input boolean;
613 item_id_input boolean;
614 cat_set_id_input boolean;
615 sts_code_input boolean;
616 attribute_category_input boolean;
617
618 BEGIN
619 /* Following code block added for bug 4025750.
620 */
621 IF p_attribute_category IS NOT NULL
622 AND p_copy_dff_to_null IS NOT NULL
623 AND p_copy_dff_to_null = 'YES' THEN
627 RPAD('ATTRIBUTE',12,LPAD(TO_CHAR(g_current_att_index + 1),3,'0'));
624 g_current_att_index := g_att_tab.LAST;
625 g_current_att_index := g_current_att_index + 1;
626 g_att_tab(g_current_att_index).temp_column_name :=
628 g_att_tab(g_current_att_index).column_type := 4;
629 g_att_tab(g_current_att_index).item_column_name := 'Attribute_Category';
630 g_att_tab(g_current_att_index).display_column := 'Attribute_Category';
631 g_count := g_count + 1;
632 END IF;
633
634 -- First make sure there is no left over data from a previous
635 -- navigation to this form
636
637 DELETE FROM MTL_ITEM_ATTRIBUTES_TEMP;
638
639 l_org_index := p_org_code_list.FIRST;
640 l_org_id := p_org_code_list(l_org_index);
641
642 /* Added for Bug 4064005. */
643 IF p_item_low IS NULL THEN
644 item_low_input := FALSE;
645 ELSE
646 item_low_input := TRUE;
647 END IF;
648 IF p_item_high IS NULL THEN
649 item_high_input := FALSE;
650 ELSE
651 item_high_input := TRUE;
652 END IF;
653 IF p_item_id = -1 THEN
654 item_id_input := FALSE;
655 ELSE
656 item_id_input := TRUE;
657 END IF;
658 IF p_cat_set_id = -1 THEN
659 cat_set_id_input := FALSE;
660 ELSE cat_set_id_input := TRUE;
661 END IF;
662 IF p_sts_code IS NULL THEN
663 sts_code_input := FALSE;
664 ELSE
665 sts_code_input := TRUE;
666 END IF;
667 IF p_attribute_category IS NULL THEN
668 attribute_category_input := FALSE;
669 ELSE
670 attribute_category_input := TRUE;
671 END IF;
672
673
674 LOOP
675
676 -- populate the temp table with data context data
677
678 -- Build Dynamic cursor
679 -- p_attribute_category is passed as additional in parameter. Bug: 3770547
680 Build_Item_Cursor
681 ( p_category_id => p_cat_id
682 , p_category_set_id => p_cat_set_id
683 , p_item_id => p_item_id
684 , p_item_from => p_item_low
685 , p_item_to => p_item_high
686 , p_organization_id => l_org_id
687 , p_sts_code => p_sts_code
688 , p_attribute_category => p_attribute_category
689 , p_copy_dff_to_null => p_copy_dff_to_null
690 , x_item_cursor => l_item_cursor
691 );
692
693
694 /* Added for Bug 4064005 */
695 IF item_low_input AND item_high_input AND cat_set_id_input AND sts_code_input AND attribute_category_input THEN
696 OPEN l_item_cur FOR l_item_cursor
697 USING l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id, p_sts_code, p_attribute_category;
698 ELSIF item_low_input AND item_high_input AND cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
699 OPEN l_item_cur FOR l_item_cursor
700 USING l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id, p_sts_code;
701 ELSIF item_low_input AND NOT item_high_input AND cat_set_id_input AND sts_code_input AND attribute_category_input THEN
702 OPEN l_item_cur FOR l_item_cursor
703 USING l_org_id, p_item_low, p_cat_set_id, p_cat_id, p_sts_code, p_attribute_category;
704 ELSIF item_low_input AND NOT item_high_input AND cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
705 OPEN l_item_cur FOR l_item_cursor
706 USING l_org_id, p_item_low, p_cat_set_id, p_cat_id, p_sts_code;
707 ELSIF NOT item_low_input AND item_high_input AND cat_set_id_input AND sts_code_input AND attribute_category_input THEN
708 OPEN l_item_cur FOR l_item_cursor
709 USING l_org_id, p_item_high, p_cat_set_id, p_cat_id, p_sts_code, p_attribute_category;
710 ELSIF NOT item_low_input AND item_high_input AND cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
711 OPEN l_item_cur FOR l_item_cursor
712 USING l_org_id, p_item_high, p_cat_set_id, p_cat_id, p_sts_code;
713 ELSIF item_id_input AND cat_set_id_input AND sts_code_input AND attribute_category_input THEN
714 OPEN l_item_cur FOR l_item_cursor
715 USING l_org_id, p_item_id, p_cat_set_id, p_cat_id, p_sts_code, p_attribute_category;
716 ELSIF item_id_input AND cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
717 OPEN l_item_cur FOR l_item_cursor
718 USING l_org_id, p_item_id, p_cat_set_id, p_cat_id, p_sts_code;
719 ELSIF item_low_input AND item_high_input AND cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
720 OPEN l_item_cur FOR l_item_cursor
721 USING l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id, p_attribute_category;
722 ELSIF item_low_input AND item_high_input AND cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
723 OPEN l_item_cur FOR l_item_cursor
724 USING l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id;
728 ELSIF item_low_input AND NOT item_high_input AND cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
725 ELSIF item_low_input AND NOT item_high_input AND cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
726 OPEN l_item_cur FOR l_item_cursor
727 USING l_org_id, p_item_low, p_cat_set_id, p_cat_id, p_attribute_category;
729 OPEN l_item_cur FOR l_item_cursor
730 USING l_org_id, p_item_low, p_cat_set_id, p_cat_id;
731 ELSIF NOT item_low_input AND item_high_input AND cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
732 OPEN l_item_cur FOR l_item_cursor
733 USING l_org_id, p_item_high, p_cat_set_id, p_cat_id, p_attribute_category;
734 ELSIF NOT item_low_input AND item_high_input AND cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
735 OPEN l_item_cur FOR l_item_cursor
736 USING l_org_id, p_item_high, p_cat_set_id, p_cat_id;
737 ELSIF item_id_input AND cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
738 OPEN l_item_cur FOR l_item_cursor
739 USING l_org_id, p_item_id, p_cat_set_id, p_cat_id, p_attribute_category;
740 ELSIF item_id_input AND cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
741 OPEN l_item_cur FOR l_item_cursor
742 USING l_org_id, p_item_id, p_cat_set_id, p_cat_id;
743 ELSIF item_low_input AND item_high_input AND NOT cat_set_id_input AND sts_code_input AND attribute_category_input THEN
744 OPEN l_item_cur FOR l_item_cursor
745 USING l_org_id, p_item_low, p_item_high, p_sts_code, p_attribute_category;
746 ELSIF item_low_input AND item_high_input AND NOT cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
747 OPEN l_item_cur FOR l_item_cursor
748 USING l_org_id, p_item_low, p_item_high, p_sts_code;
749 ELSIF item_low_input AND NOT item_high_input AND NOT cat_set_id_input AND sts_code_input AND attribute_category_input THEN
750 OPEN l_item_cur FOR l_item_cursor
751 USING l_org_id, p_item_low, p_sts_code, p_attribute_category;
752 ELSIF item_low_input AND NOT item_high_input AND NOT cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
753 OPEN l_item_cur FOR l_item_cursor
754 USING l_org_id, p_item_low, p_sts_code;
755 ELSIF NOT item_low_input AND item_high_input AND NOT cat_set_id_input AND sts_code_input AND attribute_category_input THEN
756 OPEN l_item_cur FOR l_item_cursor
757 USING l_org_id, p_item_high, p_sts_code, p_attribute_category;
758 ELSIF NOT item_low_input AND item_high_input AND NOT cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
759 OPEN l_item_cur FOR l_item_cursor
760 USING l_org_id, p_item_high, p_sts_code;
761 ELSIF item_id_input AND NOT cat_set_id_input AND sts_code_input AND attribute_category_input THEN
762 OPEN l_item_cur FOR l_item_cursor
763 USING l_org_id, p_item_id, p_sts_code, p_attribute_category;
764 ELSIF item_id_input AND NOT cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
765 OPEN l_item_cur FOR l_item_cursor
766 USING l_org_id, p_item_id, p_sts_code;
767 ELSIF item_low_input AND item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
768 OPEN l_item_cur FOR l_item_cursor
769 USING l_org_id, p_item_low, p_item_high, p_attribute_category;
770 ELSIF item_low_input AND item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
771 OPEN l_item_cur FOR l_item_cursor
772 USING l_org_id, p_item_low, p_item_high;
773 ELSIF item_low_input AND NOT item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
774 OPEN l_item_cur FOR l_item_cursor
775 USING l_org_id, p_item_low, p_attribute_category;
776 ELSIF item_low_input AND NOT item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
777 OPEN l_item_cur FOR l_item_cursor
778 USING l_org_id, p_item_low;
779 ELSIF NOT item_low_input AND item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
780 OPEN l_item_cur FOR l_item_cursor
781 USING l_org_id, p_item_high, p_attribute_category;
782 ELSIF NOT item_low_input AND item_high_input AND NOT cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
783 OPEN l_item_cur FOR l_item_cursor
784 USING l_org_id, p_item_high;
785 ELSIF item_id_input AND NOT cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
786 OPEN l_item_cur FOR l_item_cursor
787 USING l_org_id, p_item_id, p_attribute_category;
788 ELSIF item_id_input AND NOT cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
789 OPEN l_item_cur FOR l_item_cursor
790 USING l_org_id, p_item_id;
791
792 /* Added for Bug 4064006 */
793 ELSIF NOT item_low_input AND NOT item_high_input AND NOT item_id_input AND cat_set_id_input AND sts_code_input AND attribute_category_input THEN
794 OPEN l_item_cur FOR l_item_cursor
795 USING l_org_id, p_cat_set_id, p_cat_id, p_sts_code , p_attribute_category;
796 ELSIF NOT item_low_input AND NOT item_high_input AND NOT item_id_input AND cat_set_id_input AND sts_code_input AND NOT attribute_category_input THEN
797 OPEN l_item_cur FOR l_item_cursor
798 USING l_org_id, p_cat_set_id, p_cat_id, p_sts_code;
799 ELSIF NOT item_low_input AND NOT item_high_input AND NOT item_id_input AND cat_set_id_input AND NOT sts_code_input AND attribute_category_input THEN
800 OPEN l_item_cur FOR l_item_cursor
801 USING l_org_id, p_cat_set_id, p_cat_id, p_attribute_category;
802 ELSIF NOT item_low_input AND NOT item_high_input AND NOT item_id_input AND cat_set_id_input AND NOT sts_code_input AND NOT attribute_category_input THEN
803 OPEN l_item_cur FOR l_item_cursor
804 USING l_org_id, p_cat_set_id, p_cat_id;
805
806 END IF;
807
808 /* Commented during bug 4064005
812 IF p_item_low IS NOT NULL
809 -- Item Info for the selected category set, category, item range or item id
810 -- with inventory item status code
811
813 AND p_item_high IS NOT NULL
814 AND p_cat_id <> -1
815 --Bug#3497035
816 -- AND p_cat_set_id IS NOT NULL
817 AND p_cat_set_id <> -1
818 AND p_sts_code IS NOT NULL THEN
819
820 OPEN l_item_cur FOR l_item_cursor
821 USING
822 l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id, p_sts_code;
823
824 ELSIF p_item_low IS NOT NULL
825 AND p_item_high IS NULL
826 AND p_cat_id <> -1
827 --Bug#3497035
828 -- AND p_cat_set_id IS NOT NULL
829 AND p_cat_set_id <> -1
830 AND p_sts_code IS NOT NULL THEN
831
832 OPEN l_item_cur FOR l_item_cursor
833 USING
834 l_org_id, p_item_low, p_cat_set_id, p_cat_id, p_sts_code;
835
836 ELSIF p_item_low IS NULL
837 AND p_item_high IS NOT NULL
838 AND p_cat_id <> -1
839 --Bug#3497035
840 -- AND p_cat_set_id IS NOT NULL
841 AND p_cat_set_id <> -1
842 AND p_sts_code IS NOT NULL THEN
843
844 OPEN l_item_cur FOR l_item_cursor
845 USING
846 l_org_id, p_item_high, p_cat_set_id, p_cat_id, p_sts_code;
847
848 ELSIF p_item_id <> -1
849 AND p_cat_id <> -1
850 --Bug#3497035
851 -- AND p_cat_set_id IS NOT NULL
852 AND p_cat_set_id <> -1
853 AND p_sts_code IS NOT NULL THEN
854
855 OPEN l_item_cur FOR l_item_cursor
856 USING
857 l_org_id, p_item_id, p_cat_set_id, p_cat_id, p_sts_code;
858
859 ELSIF p_item_low IS NOT NULL
860 AND p_item_high IS NOT NULL
861 AND p_cat_id <> -1
862 --Bug#3497035
863 -- AND p_cat_set_id IS NOT NULL
864 AND p_cat_set_id <> -1
865 AND p_sts_code IS NULL THEN
866
867 OPEN l_item_cur FOR l_item_cursor
868 USING
869 l_org_id, p_item_low, p_item_high, p_cat_set_id, p_cat_id;
870
871 ELSIF p_item_low IS NOT NULL
872 AND p_item_high IS NULL
873 AND p_cat_id <> -1
874 --Bug#3497035
875 -- AND p_cat_set_id IS NOT NULL
876 AND p_cat_set_id <> -1
877 AND p_sts_code IS NULL THEN
878
879 OPEN l_item_cur FOR l_item_cursor
880 USING
881 l_org_id, p_item_low, p_cat_set_id, p_cat_id;
882
883 ELSIF p_item_low IS NULL
884 AND p_item_high IS NOT NULL
885 AND p_cat_id <> -1
886 --Bug#3497035
887 -- AND p_cat_set_id IS NOT NULL
888 AND p_cat_set_id <> -1
889 AND p_sts_code IS NULL THEN
890
891 OPEN l_item_cur FOR l_item_cursor
892 USING
893 l_org_id, p_item_high, p_cat_set_id, p_cat_id;
894
895 ELSIF p_item_id <> -1
896 AND p_cat_id <> -1
897 --Bug#3497035
898 -- AND p_cat_set_id IS NOT NULL
899 AND p_cat_set_id <> -1
900 AND p_sts_code IS NULL THEN
901
902 OPEN l_item_cur FOR l_item_cursor
903 USING
904 l_org_id, p_item_id, p_cat_set_id, p_cat_id;
905
906 ELSIF p_item_low IS NOT NULL
907 AND p_item_high IS NOT NULL
908 --Bug#3497035
909 AND (p_cat_id = -1 OR p_cat_set_id = -1)
910 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
911 AND p_sts_code IS NOT NULL THEN
912
913 OPEN l_item_cur FOR l_item_cursor
914 USING
915 l_org_id, p_item_low, p_item_high, p_sts_code;
916
917 ELSIF p_item_low IS NOT NULL
918 AND p_item_high IS NULL
919 --Bug#3497035
920 AND (p_cat_id = -1 OR p_cat_set_id = -1)
921 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
922 AND p_sts_code IS NOT NULL THEN
923
924 OPEN l_item_cur FOR l_item_cursor
925 USING
926 l_org_id, p_item_low, p_sts_code;
927
928 ELSIF p_item_low IS NULL
929 AND p_item_high IS NOT NULL
930 --Bug#3497035
931 AND (p_cat_id = -1 OR p_cat_set_id = -1)
932 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
933 AND p_sts_code IS NOT NULL THEN
934
935 OPEN l_item_cur FOR l_item_cursor
936 USING
937 l_org_id, p_item_high, p_sts_code;
938
939 ELSIF p_item_id <> -1
940 --Bug#3497035
941 AND (p_cat_id = -1 OR p_cat_set_id = -1)
942 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
943 AND p_sts_code IS NOT NULL THEN
944
945 OPEN l_item_cur FOR l_item_cursor
946 USING
947 l_org_id, p_item_id, p_sts_code;
948
949 ELSIF p_item_low IS NOT NULL
950 AND p_item_high IS NOT NULL
951 --Bug#3497035
952 AND (p_cat_id = -1 OR p_cat_set_id = -1)
953 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
954 AND p_sts_code IS NULL THEN
955
956 OPEN l_item_cur FOR l_item_cursor
957 USING
958 l_org_id, p_item_low, p_item_high;
959
960 ELSIF p_item_low IS NOT NULL
961 AND p_item_high IS NULL
962 --Bug#3497035
963 AND (p_cat_id = -1 OR p_cat_set_id = -1)
964 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
965 AND p_sts_code IS NULL THEN
966
967 OPEN l_item_cur FOR l_item_cursor
968 USING
969 l_org_id, p_item_low;
970
971 ELSIF p_item_low IS NULL
972 AND p_item_high IS NOT NULL
973 --Bug#3497035
974 AND (p_cat_id = -1 OR p_cat_set_id = -1)
975 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
976 AND p_sts_code IS NULL THEN
977
978 OPEN l_item_cur FOR l_item_cursor
979 USING
980 l_org_id, p_item_high;
981
982 ELSIF p_item_id <> -1
983 --Bug#3497035
987
984 AND (p_cat_id = -1 OR p_cat_set_id = -1)
985 -- AND (p_cat_id = -1 OR p_cat_set_id IS NULL)
986 AND p_sts_code IS NULL THEN
988 OPEN l_item_cur FOR l_item_cursor
989 USING
990 l_org_id, p_item_id;
991
992 END IF;
993 */
994
995
996 FETCH l_item_cur
997 INTO l_organization_code
998 ,l_organization_id
999 ,l_item_number
1000 ,l_inventory_item_id;
1001
1002 WHILE l_item_cur%FOUND LOOP
1003
1004 IF G_DEBUG = 'Y' THEN
1005 INV_ORGHIERARCHY_PVT.Log
1006 ( INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1007 , 'Organization Code:' || l_organization_code || ' ' ||
1008 'Organization Id:' || l_organization_id || ' ' ||
1009 'Item Number:' || l_item_number || ' ' ||
1010 'Inventory Item Id:' || l_inventory_item_id
1011 );
1012 END IF;
1013
1014 -- populate the table
1015 INSERT INTO MTL_ITEM_ATTRIBUTES_TEMP(
1016 organization_code
1017 ,organization_id
1018 ,item_code
1019 ,item_id
1020 )
1021 VALUES
1022 ( l_organization_code
1023 , l_organization_id
1024 , l_item_number
1025 , l_inventory_item_id
1026 );
1027
1028 FETCH l_item_cur
1029 INTO l_organization_code
1030 ,l_organization_id
1031 ,l_item_number
1032 ,l_inventory_item_id;
1033
1034
1035 END LOOP; -- item cursor loop
1036
1037 CLOSE l_item_cur;
1038
1039 EXIT WHEN l_org_index = p_org_code_list.LAST;
1040 l_org_index := p_org_code_list.NEXT(l_org_index);
1041 l_org_id := p_org_code_list(l_org_index);
1042
1043 END LOOP;
1044
1045 g_current_att_index := g_att_tab.FIRST;
1046
1047 LOOP
1048
1049 IF g_att_tab(g_current_att_index).column_type = 1 THEN
1050
1051
1052 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
1053 || g_att_tab(g_current_att_index).temp_column_name
1054 || ' ) = '
1055 || ' (SELECT '
1056 || g_att_tab(g_current_att_index).item_column_name
1057 || ' FROM MTL_SYSTEM_ITEMS_VL itm '
1058 || ' WHERE tmp.item_id = itm.inventory_item_id '
1059 || ' AND tmp.organization_id = itm.organization_id '
1060 || ')';
1061
1062 EXECUTE IMMEDIATE l_dml_str;
1063 /*myerrams, Modified the following query to use bind variables. Bug: 5001785*/
1064 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
1065 || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_DSP')
1066 || ' ) = '
1067 || ' (SELECT FND.'
1068 || g_att_tab(g_current_att_index).lookup_column
1069 || ' FROM '
1070 || g_att_tab(g_current_att_index).lookup_table
1071 || ' FND '
1072 || ' WHERE FND.'
1073 || g_att_tab(g_current_att_index).lookup_type
1074 || ' = :1'
1075 || ' AND FND.'
1076 || g_att_tab(g_current_att_index).reference_key_column
1077 || ' = '
1078 || ' TMP.'
1079 || g_att_tab(g_current_att_index).temp_column_name
1080 || ')';
1081
1082 EXECUTE IMMEDIATE l_dml_str
1083 USING g_att_tab(g_current_att_index).lookup_type_value;
1084
1085 ELSIF g_att_tab(g_current_att_index).column_type = 2 THEN
1086
1087 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
1088 || g_att_tab(g_current_att_index).temp_column_name
1089 || ' ) = '
1090 || ' (SELECT '
1091 || g_att_tab(g_current_att_index).item_column_name
1092 || ' FROM MTL_SYSTEM_ITEMS_VL ITM '
1093 || ' WHERE TMP.item_id = ITM.inventory_item_id '
1094 || ' AND TMP.organization_id = ITM.organization_id '
1095 || ')';
1096
1097 EXECUTE IMMEDIATE l_dml_str;
1098
1099 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
1100 || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_DSP')
1101 || ' ) = '
1102 || ' (SELECT FND.'
1103 || g_att_tab(g_current_att_index).lookup_column
1104 || ' FROM '
1105 || g_att_tab(g_current_att_index).lookup_table
1106 || ' FND '
1107 || ' WHERE FND.'
1108 || g_att_tab(g_current_att_index).lookup_type_value
1109 || ' = '
1110 || ' TMP.'
1111 || g_att_tab(g_current_att_index).temp_column_name
1112 || ')';
1113
1114 EXECUTE IMMEDIATE l_dml_str;
1115
1116 ELSE
1117
1118 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
1119 || g_att_tab(g_current_att_index).temp_column_name
1120 || ' ) = '
1121 || ' (SELECT '
1122 || g_att_tab(g_current_att_index).item_column_name
1123 || ' FROM MTL_SYSTEM_ITEMS_VL itm '
1124 || ' WHERE tmp.item_id = itm.inventory_item_id '
1125 || ' AND tmp.organization_id = itm.organization_id '
1126 || ')';
1127
1128
1129 EXECUTE IMMEDIATE l_dml_str;
1130
1131 l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
1132 || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_dsp')
1133 || ' ) = '
1134 || ' (SELECT '
1135 || g_att_tab(g_current_att_index).item_column_name
1136 || ' FROM MTL_SYSTEM_ITEMS_VL itm '
1137 || ' WHERE tmp.item_id = itm.inventory_item_id '
1138 || ' AND tmp.organization_id = itm.organization_id '
1139 || ')';
1140
1141 EXECUTE IMMEDIATE l_dml_str;
1142
1143 END IF;
1144
1148
1145 EXIT WHEN g_current_att_index = g_att_tab.LAST;
1146
1147 g_current_att_index := g_att_tab.NEXT(g_current_att_index);
1149 END LOOP;
1150
1151 COMMIT;
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1156 THEN
1157 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
1158 , 'populate_temp_table'
1159 );
1160 END IF;
1161 RAISE;
1162
1163 END populate_temp_table;
1164
1165 --=========================================================================
1166 -- PROCEDURE : clear_temp_table PUBLIC
1167 -- PARAMETERS :
1168 -- COMMENT : clear MTL_ITEM_ATTRIBUTES_TEMP
1169 -- simple command to purge all records in temp table
1170 -- this may not seem necessary as a temp table loses
1171 -- it's data at the eand of each session.
1172 -- However the session will last until the form is
1173 -- dismissed and the user may query several times
1174 -- before dismissing the form.
1175 -- Each time there is a new query the temp table
1176 -- needs to be purged.
1177
1178 -- PRE-COND : This procedure prior to poulating the temp table
1179 --=========================================================================
1180 PROCEDURE clear_temp_table
1181 IS
1182
1183 BEGIN
1184
1185 delete MTL_ITEM_ATTRIBUTES_TEMP;
1186
1187 commit;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1192 THEN
1193 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
1194 , 'clear_temp_table'
1195 );
1196 END IF;
1197 RAISE;
1198 END clear_temp_table;
1199
1200 --=========================================================================
1201 -- PROCEDURE : find_org_list PUBLIC
1202 -- PARAMETERS :
1203 -- COMMENT : Find the organizations that exist in the master org
1204 -- PRE-COND : called from form
1205 --=========================================================================
1206 PROCEDURE find_org_list
1207 ( p_org_tab OUT NOCOPY INV_ORGHIERARCHY_PVT.orgid_tbl_type
1208 )
1209 IS
1210
1211 --================
1212 -- CURSORS
1213 --================
1214
1215 CURSOR org_cur IS
1216 SELECT ORGANIZATION_ID
1217 FROM ORG_ORGANIZATION_DEFINITIONS
1218 WHERE ORGANIZATION_ID IN
1219 (SELECT DISTINCT ORGANIZATION_ID_PARENT
1220 FROM PER_ORG_STRUCTURE_ELEMENTS)
1221 OR
1222 ORGANIZATION_ID IN
1223 (SELECT ORGANIZATION_ID_CHILD
1224 FROM PER_ORG_STRUCTURE_ELEMENTS)
1225 ORDER BY ORGANIZATION_NAME;
1226
1227 BEGIN
1228
1229 -- clear the pl/sql table before use
1230 g_org_tab := g_empty_org_tab;
1231
1232 -- open cursor
1233
1234 IF NOT org_cur%ISOPEN
1235 THEN
1236 OPEN org_cur;
1237 END IF;
1238
1239 FETCH org_cur INTO g_org_tab(g_org_tab.COUNT+1);
1240
1241 WHILE org_cur%FOUND
1242 LOOP
1243
1244 FETCH org_cur INTO g_org_tab(g_org_tab.COUNT+1);
1245
1246 END LOOP;
1247
1248 CLOSE org_cur;
1249
1250 p_org_tab := g_org_tab;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1255 THEN
1256 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
1257 , 'find_org_list'
1258 );
1259 END IF;
1260 END find_org_list;
1261
1262 --=========================================================================
1263 -- PROCEDURE : call_item_update PUBLIC
1264 -- PARAMETERS :
1265 -- COMMENT : This procedure is not currently used.
1266 -- : If the form is enhanced to offer an online
1267 -- : option in the future the code to offer that
1268 -- : functionality should be written here
1269 -- PRE-COND : This procedure will be called from the form
1270 --=========================================================================
1271 PROCEDURE call_item_update
1272 ( p_att_tab IN INV_ITEM_ATTRIBUTES_PKG.att_tbl_type
1273 ,p_sel_tab IN INV_ITEM_ATTRIBUTES_PKG.sel_tbl_type
1274 ,p_inventory_item_id OUT NOCOPY NUMBER
1275 ,p_organization_id OUT NOCOPY NUMBER
1276 ,p_return_status OUT NOCOPY VARCHAR2
1277 ,p_error_tab OUT NOCOPY INV_Item_GRP.Error_tbl_type
1278 )
1279 IS
1280
1281 x_errbuff VARCHAR2(240);
1282 x_retcode NUMBER;
1283
1284 BEGIN
1285
1286 x_errbuff := NULL;
1287 x_retcode := 0;
1288
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1292 THEN
1293 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
1294 , 'call_item_update'
1295 );
1296 END IF;
1297 x_retcode := 2;
1298 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1299 END call_item_update;
1300
1301 --=========================================================================
1302 -- PROCEDURE : Determine_Return_Code PRIVATE
1303 -- PARAMETERS: x_retcode OUT NOCOPY Return code
1304 -- x_errbuff OUT NOCOPY Return message
1305 -- COMMENT : This procedure verifies that all the records have been
1309 PROCEDURE Determine_Return_Code
1306 -- successfully processed by the Item Open Interface program and
1307 -- returns a warning in case of failure.
1308 --=========================================================================
1310 ( x_retcode OUT NOCOPY VARCHAR2
1311 , x_errbuff OUT NOCOPY VARCHAR2
1312 )
1313 IS
1314 l_error_count NUMBER;
1315 BEGIN
1316
1317 IF G_DEBUG = 'Y' THEN
1318 INV_ORGHIERARCHY_PVT.Log
1319 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1320 , '> Determine_Return_Code'
1321 );
1322 END IF;
1323
1324 SELECT COUNT(*)
1325 INTO l_error_count
1326 FROM mtl_system_items_interface
1327 WHERE NVL(request_id, 0) = NVL(FND_GLOBAL.conc_request_id, 0);
1328
1329 IF l_error_count > 0 THEN
1330 x_retcode := RETCODE_WARNING;
1331 FND_MESSAGE.Set_Name('INV', 'INV_MGD_ITEM_ORG_ASSIGN_WARN');
1332 FND_MESSAGE.Set_Token('RECORD_NUMBER', l_error_count);
1333 x_errbuff := FND_MESSAGE.Get;
1334 ELSE
1335 x_retcode := RETCODE_SUCCESS;
1336 x_errbuff := NULL;
1337 END IF;
1338 IF G_DEBUG = 'Y' THEN
1339 INV_ORGHIERARCHY_PVT.Log
1340 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1341 , '< Determine_Return_Code'
1342 );
1343 END IF;
1344
1345 END Determine_Return_Code;
1346
1347 --========================================================================
1348 -- FUNCTION : Has_Worker_Completed PRIVATE
1349 -- PARAMETERS: p_request_id IN NUMBER
1350 -- RETURNS : BOOLEAN
1351 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
1352 -- has completed; FALSE otherwise
1353 --=========================================================================
1354 FUNCTION Has_Worker_Completed
1355 ( p_request_id IN NUMBER
1356 )
1357 RETURN BOOLEAN
1358 IS
1359 l_count NUMBER;
1360 l_result BOOLEAN;
1361 BEGIN
1362
1363 IF G_DEBUG = 'Y' THEN
1364 INV_ORGHIERARCHY_PVT.Log
1365 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1366 , '> Has_Worker_Completed'
1367 );
1368 END IF;
1369
1370 SELECT COUNT(*)
1371 INTO l_count
1372 FROM fnd_concurrent_requests
1373 WHERE request_id = p_request_id
1374 AND phase_code = 'C';
1375
1376 IF l_count = 1 THEN
1377 l_result := TRUE;
1378 ELSE
1379 l_result := FALSE;
1380 END IF;
1381
1382 IF G_DEBUG = 'Y' THEN
1383 INV_ORGHIERARCHY_PVT.Log
1384 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1385 , '< Has_Worker_Completed'
1386 );
1387 END IF;
1388
1389 RETURN l_result;
1390
1391 END Has_Worker_Completed;
1392
1393 --========================================================================
1394 -- PROCEDURE : Wait_For_Worker PRIVATE
1395 -- PARAMETERS: p_workers IN workers' request ID
1396 -- x_worker_idx OUT position in p_workers of the
1397 -- completed worked
1398 -- COMMENT : This procedure polls the submitted workers and suspend
1399 -- the program till the completion of one of them; it returns
1400 -- the completed worker through x_worker_idx
1401 --=========================================================================
1402 PROCEDURE Wait_For_Worker
1403 ( p_workers IN g_request_tbl_type
1404 , x_worker_idx OUT NOCOPY BINARY_INTEGER
1405 )
1406 IS
1407 l_done BOOLEAN;
1408 BEGIN
1409
1410 IF G_DEBUG = 'Y' THEN
1411 INV_ORGHIERARCHY_PVT.Log
1412 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1413 , '> Wait_For_Worker'
1414 );
1415 END IF;
1416
1417 l_done := FALSE;
1418
1419 WHILE (NOT l_done) LOOP
1420
1421 FOR l_Idx IN 1..p_workers.COUNT LOOP
1422
1423 IF Has_Worker_Completed(p_workers(l_Idx)) THEN
1424 l_done := TRUE;
1425 x_worker_idx := l_Idx;
1426 EXIT;
1427 END IF;
1428
1429 END LOOP;
1430
1431 IF (NOT l_done) THEN
1432 DBMS_LOCK.sleep(G_SLEEP_TIME);
1433 END IF;
1434
1435 END LOOP;
1436
1437 IF G_DEBUG = 'Y' THEN
1438 INV_ORGHIERARCHY_PVT.Log
1439 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1440 , '< Wait_For_Worker'
1441 );
1442 END IF;
1443
1444 END Wait_For_Worker;
1445
1446
1447 --========================================================================
1448 -- PROCEDURE : Wait_For_All_Workers PRIVATE
1449 -- PARAMETERS: p_workers IN workers' request ID
1450 -- COMMENT : This procedure polls the submitted workers and suspend
1451 -- the program till the completion of all of them.
1452 --=========================================================================
1453 PROCEDURE Wait_For_All_Workers
1454 ( p_workers IN g_request_tbl_type
1455 )
1456 IS
1457 l_done BOOLEAN;
1458 BEGIN
1459
1460 IF G_DEBUG = 'Y' THEN
1461 INV_ORGHIERARCHY_PVT.Log
1462 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1463 , '> Wait_For_All_Workers'
1464 );
1465 END IF;
1466
1467 l_done := FALSE;
1468
1469 WHILE (NOT l_done) LOOP
1470
1471 l_done := TRUE;
1472
1473 FOR l_Idx IN 1..p_workers.COUNT LOOP
1474
1475 IF NOT Has_Worker_Completed(p_workers(l_Idx)) THEN
1476 l_done := FALSE;
1477 EXIT;
1478 END IF;
1479
1480 END LOOP;
1481
1482 IF (NOT l_done) THEN
1483 DBMS_LOCK.sleep(G_SLEEP_TIME);
1484 END IF;
1485
1486 END LOOP;
1487
1491 , '< Wait_For_All_Workers'
1488 IF G_DEBUG = 'Y' THEN
1489 INV_ORGHIERARCHY_PVT.Log
1490 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1492 );
1493 END IF;
1494
1495 END Wait_For_All_Workers;
1496
1497 --========================================================================
1498 -- PROCEDURE : Submit_Item_Update PRIVATE
1499 -- PARAMETERS: p_organization_id IN an organization
1500 -- p_set_process_id IN Set process ID
1501 -- x_workers IN OUT NOCOPY workers' request ID
1502 -- p_request_count IN max worker number
1503 -- COMMENT : This procedure submits the Item Update concurrent program.
1504 -- Before submitting the request, it verifies that there are
1505 -- enough workers available and wait for the completion of one
1506 -- if necessary.
1507 -- The list of workers' request ID is updated.
1508 --=========================================================================
1509 PROCEDURE Submit_Item_Update
1510 ( p_organization_id IN NUMBER
1511 , p_set_process_id IN NUMBER
1512 , x_workers IN OUT NOCOPY g_request_tbl_type
1513 , p_request_count IN NUMBER
1514 )
1515 IS
1516 l_worker_idx BINARY_INTEGER;
1517 l_request_id NUMBER;
1518 BEGIN
1519
1520 IF G_DEBUG = 'Y' THEN
1521 INV_ORGHIERARCHY_PVT.Log
1522 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1523 , '> Submit_Item_Update'
1524 );
1525
1526 INV_ORGHIERARCHY_PVT.Log
1527 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1528 , 'x_workers.COUNT: '||TO_CHAR(x_workers.COUNT)
1529 );
1530 END IF;
1531
1532 IF NOT g_unit_test_mode THEN
1533
1534 IF x_workers.COUNT < p_request_count THEN
1535 -- number of workers submitted so far does not exceed the maximum
1536 -- number of workers allowed
1537 l_worker_idx := x_workers.COUNT + 1;
1538 ELSE
1539 -- need to wait for a submitted worker to finish
1540 Wait_For_Worker
1541 ( p_workers => x_workers
1542 , x_worker_idx => l_worker_idx
1543 );
1544 END IF;
1545
1546 IF NOT FND_REQUEST.Set_Options
1547 ( implicit => 'WARNING'
1548 , protected => 'YES'
1549 )
1550 THEN
1551 RAISE g_submit_failure_exc;
1552 END IF;
1553
1554 -- argument 7 is the run mode which is 2 for update
1555
1556 x_workers(l_worker_idx):= FND_REQUEST.Submit_Request
1557 ( application => 'INV'
1558 , program => 'INCOIN'
1559 , argument1 => p_organization_id
1560 , argument2 => 1
1561 , argument3 => 1
1562 , argument4 => 1
1563 , argument5 => 1
1564 , argument6 => p_set_process_id
1565 , argument7 => 2
1566 );
1567
1568 IF x_workers(l_worker_idx) = 0 THEN
1569 RAISE g_submit_failure_exc;
1570 END IF;
1571
1572 END IF;
1573
1574 COMMIT;
1575
1576 IF G_DEBUG = 'Y' THEN
1577 INV_ORGHIERARCHY_PVT.Log
1578 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1579 , '< Submit_Item_Update'
1580 );
1581 END IF;
1582
1583 END Submit_Item_Update;
1584
1585 --========================================================================
1586 -- FUNCTION : Get_Set_Process_ID PRIVATE
1587 -- PARAMETERS: None
1588 -- RETURNS : NUMBER
1589 -- COMMENT : This function returns the next set process ID to be used to
1590 -- run the Item Open Interface
1591 --=========================================================================
1592 FUNCTION Get_Set_Process_ID
1593 RETURN NUMBER
1594 IS
1595 l_set_process_id NUMBER;
1596 BEGIN
1597
1598 IF G_DEBUG = 'Y' THEN
1599 INV_ORGHIERARCHY_PVT.Log
1600 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1601 , '> Get_Set_Process_ID'
1602 );
1603 END IF;
1604
1605 SELECT mtl_system_items_intf_sets_s.NEXTVAL
1606 INTO l_set_process_id
1607 FROM dual;
1608
1609 IF G_DEBUG = 'Y' THEN
1610 INV_ORGHIERARCHY_PVT.Log
1611 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1612 , '< Get_Set_Process_ID'
1613 );
1614 END IF;
1615
1616 RETURN l_set_process_id;
1617
1618 END Get_Set_Process_ID;
1619
1620 --========================================================================
1621 -- FUNCTION : Get_Master_Org PRIVATE
1622 -- PARAMETERS: p_org_hier_level_id IN Organization Hierarchy
1623 -- Level Id
1624 -- RETURNS : NUMBER
1625 -- COMMENT : This function returns the ID of the master organization
1626 -- common to all the organizations in the hierarchy.
1627 --=========================================================================
1628 FUNCTION Get_Master_Org
1629 ( p_org_hier_level_id IN NUMBER
1630 )
1631 RETURN NUMBER
1632 IS
1633 l_master_org_id NUMBER;
1634 BEGIN
1635
1636 SELECT master_organization_id
1637 INTO l_master_org_id
1638 FROM mtl_parameters
1639 WHERE organization_id = p_org_hier_level_id;
1640
1641 RETURN l_master_org_id;
1642
1643 END Get_Master_Org;
1644
1645 --========================================================================
1649 -- enables it to run it from SQL*Plus. The Item Interface will
1646 -- PROCEDURE : Set_Unit_Test_Mode PUBLIC
1647 -- COMMENT : This procedure sets the unit test mode that prevents the
1648 -- program from attempting to submit concurrent requests and
1650 -- not be run.
1651 --=========================================================================
1652 PROCEDURE Set_Unit_Test
1653 IS
1654 BEGIN
1655 g_unit_test_mode := TRUE;
1656 END Set_Unit_Test;
1657
1658 --=========================================================================
1659 -- PROCEDURE : batch item update PUBLIC
1660 -- PARAMETERS: x_errbuf error buffer
1661 -- x_retcode 0 success, 1 warning, 2 error
1662 -- p_seq_id sequence number
1663 -- COMMENT : Called from a concurrent program if used
1664 -- this procedure allows the user to work in a
1665 -- no modal fashion.
1666 -- Blanket Update records in the MTL_SYSTEM_ITEMS table
1667 -- The struct att_tab contains the columns that
1668 -- are to be updated and the default values they are
1669 -- to be updated to.
1670 -- The struct sel_tab contains the unique id
1671 -- of the records that are to be updated
1672 -- The procedure constructs the record p_item_rec
1673 -- with the default values
1674 -- It then loops through the selected records
1675 -- and calls the published item update api
1676 -- for each unique record.
1677 -- PRE-COND : This procedure will be called from the form
1678 --=========================================================================
1679 PROCEDURE batch_item_update
1680 ( x_errbuff OUT NOCOPY VARCHAR2
1681 , x_retcode OUT NOCOPY NUMBER
1682 , p_seq_id IN NUMBER
1683 )
1684
1685 IS
1686
1687 --================
1688 -- TYPE
1689 --================
1690
1691 TYPE itm_rec_type IS
1692 RECORD (item_column_name VARCHAR2(240)
1693 ,chosen_value VARCHAR2(240));
1694
1695 TYPE itm_tbl_type IS TABLE OF itm_rec_type
1696 INDEX BY BINARY_INTEGER;
1697
1698 TYPE upd_rec_type IS
1699 RECORD (item_id NUMBER
1700 ,organization_id NUMBER);
1701
1702 TYPE upd_tbl_type IS TABLE OF upd_rec_type
1703 INDEX BY BINARY_INTEGER;
1704
1705 --================
1706 -- CURSORS
1707 --================
1708
1709 CURSOR att_cur IS
1710 SELECT item_column_name
1711 ,chosen_value
1712 FROM mtl_item_values_temp
1713 WHERE item_update_id = p_seq_id
1714 ORDER BY current_att_index;
1715
1716 CURSOR sel_cur IS
1717 SELECT inventory_item_id
1718 ,organization_id
1719 FROM mtl_update_records_temp
1720 WHERE item_update_id = p_seq_id;
1721
1722 --=================
1723 -- LOCAL VARIABLES
1724 --=================
1725 l_current_sel_index BINARY_INTEGER := 0;
1726 l_current_att_index BINARY_INTEGER := 0;
1727 l_att_tab itm_tbl_type;
1728 l_sel_tab upd_tbl_type;
1729
1730 l_commit VARCHAR2(20) := fnd_api.g_FALSE;
1731 l_lock_rows VARCHAR2(20) := fnd_api.g_TRUE;
1732 l_validation_level NUMBER := fnd_api.g_VALID_LEVEL_FULL;
1733 l_item_rec INV_Item_GRP.Item_rec_type;
1734 x_item_rec INV_Item_GRP.Item_rec_type;
1735 x_return_status VARCHAR2(1);
1736 x_Error_tbl INV_Item_GRP.Error_tbl_type;
1737 l_sel_item VARCHAR2(24);
1738 l_org_code VARCHAR2(24);
1739 l_status VARCHAR2(10);
1740 l_dml_str VARCHAR2(250);
1741 l_max_batch_size NUMBER;
1742 l_batch_size NUMBER;
1743 l_master_org_id NUMBER;
1744 l_min_index NUMBER;
1745 l_max_index NUMBER;
1746 l_set_process_id NUMBER;
1747 l_organization_id NUMBER;
1748 l_workers_tbl g_request_tbl_type;
1749 l_request_count NUMBER;
1750 l_count NUMBER;
1751 l_data_type VARCHAR2(24);
1752
1753 -- variables for FND_INSTALLATION procedure
1754 l_app_owner_schema VARCHAR2(30);
1755 l_app_status VARCHAR2(1);
1756 l_app_industry VARCHAR2(1);
1757 l_app_info_status BOOLEAN;
1758
1759 BEGIN
1760
1761 INV_ORGHIERARCHY_PVT.Log_Initialize;
1762
1763 IF G_DEBUG = 'Y' THEN
1764 INV_ORGHIERARCHY_PVT.Log
1765 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1766 , '> Batch Item Update'
1767 );
1768 END IF;
1769
1770 -- the following select and if statement are included
1771 -- to ensure the INV_ITEM_ATTRIBUTES_PKG is compiled
1772 -- system tests found that the dependancy on package
1773 --
1774 SELECT status
1775 INTO l_status
1776 FROM user_objects
1777 WHERE object_name = 'INV_ITEM_ATTRIBUTES_PKG'
1778 AND object_type = 'PACKAGE BODY';
1779
1780 IF l_status = 'INVALID' THEN
1781 DBMS_DDL.ALTER_COMPILE('package','apps','INV_ITEM_ATTRIBUTES_PKG');
1782 END IF;
1783
1784 -- open item attribute cursor
1785
1786 IF NOT att_cur%ISOPEN
1787 THEN
1788 OPEN att_cur;
1789 END IF;
1790
1791 FETCH att_cur INTO l_att_tab(l_att_tab.COUNT);
1792
1793 WHILE att_cur%FOUND
1794 LOOP
1795
1796 FETCH att_cur INTO l_att_tab(l_att_tab.COUNT);
1797
1798 END LOOP;
1799
1800 CLOSE att_cur;
1801
1802 IF NOT sel_cur%ISOPEN
1803 THEN
1804 OPEN sel_cur;
1805 END IF;
1806
1807 FETCH sel_cur INTO l_sel_tab(l_sel_tab.COUNT+1);
1808
1812 FETCH sel_cur INTO l_sel_tab(l_sel_tab.COUNT+1);
1809 WHILE sel_cur%FOUND
1810 LOOP
1811
1813
1814 END LOOP;
1815
1816 CLOSE sel_cur;
1817
1818 -- get the max batch size from the profile option;
1819 -- default it to 1000 if the profile option is not defined.
1820 l_max_batch_size := NVL( TO_NUMBER
1821 ( FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT')
1822 )
1823 , 1000
1824 );
1825
1826 IF G_DEBUG = 'Y' THEN
1827 INV_ORGHIERARCHY_PVT.Log
1828 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1829 , 'l_max_batch_size: '||TO_CHAR(l_max_batch_size)
1830 );
1831 END IF;
1832
1833 -- get the max number of workers from the profile option;
1834 -- default it to 1 if the profile option is not defined.
1835 l_request_count := NVL( TO_NUMBER
1836 ( FND_PROFILE.Value('INV_MGD_MAX_WORK')
1837 )
1838 , 1
1839 );
1840
1841 IF G_DEBUG = 'Y' THEN
1842 INV_ORGHIERARCHY_PVT.Log
1843 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1844 , 'l_request_count: '||TO_CHAR(l_request_count)
1845 );
1846 END IF;
1847
1848 l_batch_size := 0;
1849 l_set_process_id := Get_Set_Process_ID;
1850
1851 -- Get Application database owner schema
1852 l_app_info_status := FND_INSTALLATION.get_app_info
1853 (application_short_name => 'INV'
1854 ,status => l_app_status
1855 ,industry => l_app_industry
1856 ,oracle_schema => l_app_owner_schema
1857 );
1858
1859 -- get the master organization ID
1860
1861 l_current_sel_index := l_sel_tab.FIRST;
1862
1863 l_master_org_id := Get_Master_Org(l_sel_tab(l_current_sel_index).organization_id);
1864
1865 IF l_sel_tab.COUNT > 0 THEN
1866
1867 l_min_index := 1;
1868
1869 IF l_sel_tab.COUNT > l_max_batch_size THEN
1870 l_max_index := l_max_batch_size;
1871 ELSE
1872 l_max_index := l_sel_tab.COUNT;
1873 END IF;
1874
1875 LOOP
1876
1877 -- loop through all the records to be updated
1878
1879 l_current_att_index := 0;
1880
1881 INSERT INTO mtl_system_items_interface
1882 ( process_flag
1883 , set_process_id
1884 , transaction_type
1885 , inventory_item_id
1886 , organization_id
1887 , last_update_date
1888 , last_updated_by
1889 , creation_date
1890 , created_by
1891 , last_update_login
1892 , request_id
1893 , program_application_id
1894 , program_id
1895 , program_update_date
1896 , copy_item_id
1897 , copy_organization_id
1898 )
1899 VALUES
1900 ( 1
1901 , l_set_process_id
1905 , SYSDATE
1902 , 'UPDATE'
1903 , l_sel_tab(l_current_sel_index).item_id
1904 , l_sel_tab(l_current_sel_index).organization_id
1906 , FND_GLOBAL.user_id
1907 , SYSDATE
1908 , FND_GLOBAL.user_id
1909 , FND_GLOBAL.login_id
1910 , FND_GLOBAL.conc_request_id
1911 , FND_GLOBAL.prog_appl_id
1912 , FND_GLOBAL.conc_program_id
1913 , SYSDATE
1914 , l_sel_tab(l_current_sel_index).item_id
1915 , l_master_org_id
1916 );
1917
1918 IF l_current_sel_index >= l_max_index THEN
1919
1920 -- Update the records in the interface table with
1921 -- the values to copy from
1922
1923 SELECT count(*)
1924 INTO l_count
1925 FROM mtl_item_values_temp
1926 WHERE item_update_id = p_seq_id;
1927
1928 LOOP
1929
1930 -- Update the records in the interface table with
1931 -- the values to copy from
1932
1933 EXIT WHEN l_current_att_index = l_count;
1934
1935 -- Establish if the value is to be updated with null
1936
1937 IF l_att_tab(l_current_att_index).chosen_value IS NULL THEN
1938 -- Bug#2445587 fix: Copy item attribute should not copy item status to NULL
1939 -- column inventory_item_status_code not required to be assigned with !
1940 IF l_att_tab(l_current_att_index).item_column_name <> 'INVENTORY_ITEM_STATUS_CODE'
1941 THEN
1942 /***
1943 -- fix for 12.2 OLP compatible
1944
1945 SELECT data_type
1946 INTO l_data_type
1947 FROM all_tab_columns
1948 WHERE table_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
1949 AND owner = l_app_owner_schema
1950 AND column_name = upper(l_att_tab(l_current_att_index).item_column_name);
1951 ***/
1952
1953 -- bug#15842720 fix for 12.2 OLP compatible
1954 SELECT data_type
1955 INTO l_data_type
1956 FROM user_synonyms syn, dba_tab_columns col
1957 WHERE syn.synonym_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
1958 AND col.owner = syn.table_owner
1959 AND col.table_name = syn.table_name
1960 AND col.owner = l_app_owner_schema
1961 AND col.column_name = upper(l_att_tab(l_current_att_index).item_column_name);
1962
1963 IF l_data_type = 'NUMBER' THEN
1964 UPDATE mtl_item_values_temp tmp
1965 SET tmp.chosen_value = '-999999'
1966 WHERE tmp.current_att_index = l_current_att_index
1967 AND tmp.item_update_id = p_seq_id;
1968 ELSIF l_data_type = 'VARCHAR2' THEN
1969 UPDATE mtl_item_values_temp tmp
1970 SET tmp.chosen_value = '!'
1971 WHERE tmp.current_att_index = l_current_att_index
1972 AND tmp.item_update_id = p_seq_id;
1973 END IF;
1974
1975 ELSE
1976
1977 UPDATE mtl_item_values_temp tmp
1978 SET tmp.chosen_value = FND_PROFILE.VALUE('INV_STATUS_DEFAULT')
1979 WHERE tmp.current_att_index = l_current_att_index
1980 AND tmp.item_update_id = p_seq_id;
1981 END IF; -- inventory_item_status_code check
1982
1983 END IF;
1984
1985 /*myerrams, Modified the following query to use bind variables. Bug: 5001785*/
1986 l_dml_str := 'UPDATE mtl_system_items_interface int SET (int.'
1987 || l_att_tab(l_current_att_index).item_column_name
1988 || ') = ('
1989 || ' SELECT tmp.chosen_value'
1990 || ' FROM mtl_item_values_temp tmp '
1991 || ' WHERE tmp.current_att_index = :1'
1992 || ' AND tmp.item_update_id = :2'
1993 || ') WHERE (int.set_process_id = :3'
1994 || ')';
1995 EXECUTE IMMEDIATE l_dml_str
1996 USING l_current_att_index, p_seq_id, l_set_process_id;
1997
1998 IF G_DEBUG = 'Y' THEN
1999 INV_ORGHIERARCHY_PVT.Log
2000 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2001 , 'Update Complete'
2002 );
2003 END IF;
2004
2005 l_current_att_index := l_current_att_index + 1;
2006
2007 l_min_index := l_max_index + 1;
2008 IF l_sel_tab.COUNT > (l_max_index + l_max_batch_size) THEN
2009 l_max_index := l_max_index+l_max_batch_size;
2010 ELSE
2011 l_max_index := l_sel_tab.COUNT;
2012 END IF;
2013
2014 END LOOP;
2015
2016 Submit_Item_Update(l_sel_tab(l_current_sel_index).organization_id
2017 ,l_set_process_id
2018 ,l_workers_tbl
2019 ,l_request_count);
2020
2021 IF G_DEBUG = 'Y' THEN
2022 INV_ORGHIERARCHY_PVT.Log
2023 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2024 , 'Submit done'
2025 );
2026 END IF;
2027
2028 l_batch_size := 0;
2029 l_set_process_id := Get_Set_Process_ID;
2030 l_current_att_index := 0;
2031
2032 END IF;
2033
2034 EXIT WHEN l_current_sel_index = l_sel_tab.LAST;
2035
2036 l_current_sel_index := l_current_sel_index + 1;
2037
2038 END LOOP;
2039
2040 END IF;
2041
2042 Wait_For_All_Workers(p_workers => l_workers_tbl);
2043
2044 DELETE mtl_item_values_temp
2045 WHERE item_update_id = p_seq_id;
2046
2047 DELETE mtl_update_records_temp
2048 WHERE item_update_id = p_seq_id;
2049
2050 Determine_Return_Code
2051 ( x_retcode => x_retcode
2055 IF G_DEBUG = 'Y' THEN
2052 , x_errbuff => x_errbuff
2053 );
2054
2056 INV_ORGHIERARCHY_PVT.Log
2057 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2058 , '< Batch Item Update'
2059 );
2060 END IF;
2061
2062 EXCEPTION
2063 WHEN g_submit_failure_exc THEN
2064 FND_MESSAGE.Set_Name('INV', 'INV_UNABLE_TO_SUBMIT_CONC');
2065 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
2066 x_retcode := RETCODE_ERROR;
2067 WHEN OTHERS THEN
2068 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2069 THEN
2070 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
2071 , 'batch_item_update'
2072 );
2073 x_retcode := RETCODE_ERROR;
2074 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
2075 END IF;
2076
2077 END batch_item_update;
2078
2079 --=========================================================================
2080 -- PROCEDURE : populate_temp_tables PUBLIC
2081 -- PARAMETERS :
2082 -- COMMENT : This procedure is called just before
2083 -- the call to the concurrent program
2084 -- which will update the item attributes.
2085 --
2086 -- PRE-COND : This procedure will be called from the form
2087 --=========================================================================
2088 PROCEDURE populate_temp_tables
2089 (p_att_tab IN INV_ITEM_ATTRIBUTES_PKG.att_tbl_type
2090 ,x_seq_id OUT NOCOPY NUMBER
2091 )
2092 IS
2093
2094 --=================
2095 -- LOCAL VARIABLES
2096 --=================
2097
2098 l_seq_id NUMBER;
2099 l_last NUMBER;
2100
2101 BEGIN
2102
2103 g_att_tab := p_att_tab;
2104 g_current_att_index := g_att_tab.FIRST;
2105
2106 l_last := g_count - 1;
2107
2108 -- generate the sequence id which will identify this update
2109 SELECT mtl_update_session_s.NEXTVAL INTO l_seq_id FROM dual;
2110
2111 -- populate the item values temp table with the names and values
2112 -- of the attributes that need to be copied.
2113 -- This information can not be read directly from
2114 -- the MTL_ITEM_ATTRIBUTES_TEMP table as the attributes names
2115 -- have been assigned dynamically. for more please read the dld
2116
2117 LOOP
2118
2119 INSERT INTO mtl_item_values_temp(item_update_id
2120 ,item_column_name
2121 ,chosen_value
2122 ,current_att_index
2123 )
2124 VALUES(l_seq_id
2125 ,g_att_tab(g_current_att_index).item_column_name
2126 ,g_att_tab(g_current_att_index).chosen_value
2127 ,g_current_att_index
2128 );
2129
2130 EXIT WHEN g_current_att_index = l_last;
2131
2132 g_current_att_index := g_att_tab.NEXT(g_current_att_index);
2133
2134 END LOOP;
2135
2136 -- populate the update records table with the organization id and the
2137 -- item id of all the records to be copied to.
2138 -- This information can be read directly from the table
2139 -- mtl_item_attributes_temp.
2140
2141 INSERT into mtl_update_records_temp(
2142 item_update_id
2143 ,inventory_item_id
2144 ,organization_id)
2145 SELECT
2146 l_seq_id
2147 ,mia.item_id
2148 ,mia.organization_id
2149 FROM
2150 mtl_item_attributes_temp mia
2151 WHERE mia.checkbox = 'Y';
2152
2153 -- return the sequence id
2154 x_seq_id := l_seq_id;
2155
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2159 THEN
2160 FND_MSG_PUB.Add_Exc_Msg( G_INV_ITEM_ATTRIBUTES_PKG
2161 , 'populate_temp_tables'
2162 );
2163 END IF;
2164 RAISE;
2165 END populate_temp_tables;
2166
2167 END INV_ITEM_ATTRIBUTES_PKG;
[Home] [Help]