1 PACKAGE BODY INVICGDS AS
2 /* $Header: INVICGDB.pls 120.2.12020000.2 2012/07/11 08:22:55 yifwang ship $ */
3
4 PROCEDURE inv_update_item_desc(
5 inv_item_id IN NUMBER DEFAULT NULL,
6 org_id IN NUMBER DEFAULT NULL,
7 first_elem_break IN NUMBER DEFAULT 30,
8 use_name_as_first_elem IN VARCHAR2 DEFAULT 'N',
9 delimiter IN VARCHAR2 DEFAULT NULL,
10 show_all_delim IN VARCHAR2 DEFAULT 'Y'
11 )
12 IS
13
14 delim_val VARCHAR2(1);
15 new_description VARCHAR2(240);
16 new_error_text VARCHAR2(240);
17 counter NUMBER;
18 desc_control_level NUMBER;
19 master_org_id_val NUMBER;
20
21 /* This cursor gets all child item rows for a given III and org*/
22
23 CURSOR get_item_rows(iii NUMBER, org NUMBER) IS
24 SELECT inventory_item_id, organization_id--, description
28 AND MSI.ITEM_CATALOG_GROUP_ID is NOT NULL;
25 FROM mtl_system_items_B MSI
26 WHERE (MSI.inventory_item_id = iii OR iii IS NULL)
27 AND (MSI.organization_id = org OR org IS NULL)
29
30 /* This cursor gets all child item rows for a given III and master org
31 Note: it also returns the master org row; a master org is its own child*/
32 /*
33 CURSOR get_child_item_rows(iii NUMBER, m_org NUMBER) IS
34 SELECT inventory_item_id, organization_id, description
35 FROM mtl_system_items_VL MSI
36 WHERE MSI.inventory_item_id = NVL(iii, MSI.inventory_item_id)
37 AND MSI.item_catalog_group_id is NOT NULL
38 AND MSI.organization_id in (SELECT organization_id
39 FROM mtl_parameters mp
40 WHERE mp.master_organization_id = m_org);
41 */
42 CURSOR get_child_item_rows(iii NUMBER, m_org NUMBER) IS
43 SELECT inventory_item_id, organization_id
44 FROM mtl_system_items_B MSI
45 WHERE MSI.inventory_item_id = iii
46 AND EXISTS (SELECT NULL
47 FROM mtl_parameters mp
48 WHERE mp.master_organization_id = m_org
49 AND MSI.organization_id = mp.organization_id);
50
51
52 l_INSTALLED_FLAG VARCHAR2(1);
53
54 BEGIN
55
56 -- Identify the current session language as Base ('B') or Installed ('I')
57 --
58 select INSTALLED_FLAG
59 into l_INSTALLED_FLAG
60 from FND_LANGUAGES
61 where LANGUAGE_CODE = userenv('LANG');
62
63 /*Get the delimiter if needed..will have to do this only once
64 now and pass the value to the inv_get_icg_desc procedure*/
65
66 IF (delimiter is NULL) THEN
67 SELECT FT.concatenated_segment_delimiter INTO delim_val
68 FROM fnd_id_flex_structures FT
69 WHERE FT.ID_FLEX_CODE = 'MICG'
70 AND FT.APPLICATION_ID = 401;
71 ELSE
72 delim_val := delimiter;
73 END IF;
74
75 counter := 1;
76 FOR gir in get_item_rows(inv_item_id, org_id) LOOP
77
78 INVICGDS.inv_get_icg_desc(gir.inventory_item_id,
79 first_elem_break,
80 use_name_as_first_elem,
81 delim_val,
82 show_all_delim,
83 new_description,
84 new_error_text);
85
86 SELECT mp.master_organization_id INTO master_org_id_val
87 FROM mtl_parameters mp
88 WHERE mp.organization_id = gir.organization_id;
89
90 SELECT mia.control_level INTO desc_control_level
91 FROM mtl_item_attributes mia
92 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.DESCRIPTION';
93
94 IF master_org_id_val = gir.organization_id THEN
95
96 IF desc_control_level = 1 THEN /*item level: update all children*/
97
98 FOR gcir in get_child_item_rows( gir.inventory_item_id,
99 gir.organization_id )
100 LOOP
101
102 UPDATE mtl_system_items_B MSI
103 SET
104 MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
105 WHERE MSI.inventory_item_id = gcir.inventory_item_id
106 AND MSI.organization_id = gcir.organization_id;
107
108 --
109 -- R11.5 MLS
110 --
111 update MTL_SYSTEM_ITEMS_TL
112 set
113 DESCRIPTION = new_description
114 -- , LAST_UPDATE_DATE = l_sysdate
115 -- , LAST_UPDATED_BY = user_id
116 -- , LAST_UPDATE_LOGIN = login_id
117 , SOURCE_LANG = userenv('LANG')
118 where INVENTORY_ITEM_ID = gcir.inventory_item_id
119 and ORGANIZATION_ID = gcir.organization_id
120 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
121
122 END LOOP;
123
124 ELSIF desc_control_level = 2 THEN
125
126 UPDATE mtl_system_items_B MSI
127 SET
128 MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
129 WHERE MSI.inventory_item_id = gir.inventory_item_id
130 AND MSI.organization_id = gir.organization_id;
131
132 --
133 -- R11.5 MLS
134 --
135 update MTL_SYSTEM_ITEMS_TL
136 set
137 DESCRIPTION = new_description
138 -- , LAST_UPDATE_DATE = l_sysdate
139 -- , LAST_UPDATED_BY = user_id
140 -- , LAST_UPDATE_LOGIN = login_id
141 , SOURCE_LANG = userenv('LANG')
142 where INVENTORY_ITEM_ID = gir.inventory_item_id
143 and ORGANIZATION_ID = gir.organization_id
144 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
145
146 ELSE
147 null; /*error in control level*/
148
149 END IF;
150
151 ELSIF master_org_id_val <> gir.organization_id THEN /*child org*/
152
153 IF desc_control_level = 1 THEN /*item level:do nothing */
154 NULL;
155
156 ELSIF desc_control_level = 2 THEN
157
158 UPDATE mtl_system_items_B MSI
159 SET
160 MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
161 WHERE MSI.inventory_item_id = gir.inventory_item_id
162 AND MSI.organization_id = gir.organization_id;
163
164 --
165 -- R11.5 MLS
166 --
167 update MTL_SYSTEM_ITEMS_TL
168 set
169 DESCRIPTION = new_description
170 -- , LAST_UPDATE_DATE = l_sysdate
171 -- , LAST_UPDATED_BY = user_id
172 -- , LAST_UPDATE_LOGIN = login_id
173 , SOURCE_LANG = userenv('LANG')
174 where INVENTORY_ITEM_ID = gir.inventory_item_id
175 and ORGANIZATION_ID = gir.organization_id
179 null; /*error in control level*/
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
177
178 ELSE
180 END IF;
181
182 END IF;
183
184 /* Intermittent commit logic*/
185 counter := counter + 1;
186 IF counter > 100 THEN
187 commit;
188 counter :=1;
189 END IF;
190
191 END LOOP;
192
193 commit;
194
195 EXCEPTION
196 WHEN NO_DATA_FOUND then
197 null;
198
199 WHEN others THEN
200 rollback; /*Note: the rows already commited will still remain
201 No error mechanism.
202 No place holder to return SQLCODE or SQLERRM
203 The design called for a bare-bones mass update script
204 runnable from the SQL> prompt
205 */
206
207 END inv_update_item_desc;
208
209
210 FUNCTION inv_fn_get_icg_desc(
211 inv_item_id IN NUMBER,
212 first_elem_break IN NUMBER DEFAULT 30,
213 use_name_as_first_elem IN VARCHAR2 DEFAULT 'N',
214 delimiter IN VARCHAR2 DEFAULT NULL,
215 show_all_delim IN VARCHAR2 DEFAULT 'Y',
216 show_error_flag IN VARCHAR2 DEFAULT 'Y'
217 ) return VARCHAR2 IS
218
219 descr VARCHAR2(240);
220 desc_err VARCHAR2(240);
221 dummy_err VARCHAR2(240);
222 errtxt VARCHAR2(240);
223
224 BEGIN
225 errtxt := NULL;
226
227 INVICGDS.inv_get_icg_desc(inv_item_id,
228 first_elem_break,
229 use_name_as_first_elem,
230 delimiter,
231 show_all_delim,
232 descr,
233 errtxt);
234
235 IF (errtxt IS NOT NULL) then
236 /*Some error has occured somewhere in
237 **the inv_get_icg_desc call
238 ** Show error if the flag is Y
239 */
240 IF (show_error_flag = 'Y') THEN
241 return(errtxt);
242 ELSE
243 return(NULL);
244 END IF;
245 ELSE
246 return(descr);
247 END IF;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 IF (show_error_flag = 'Y') THEN
252 dummy_err := 'INVICGDS(2):'||SQLCODE||':'||substrb(SQLERRM, 1,30)||errtxt ;
253 desc_err := substr( dummy_err, 1, 240);
254 return(desc_err);
255 ELSE
256 return(NULL);
257 END IF;
258
259 END inv_fn_get_icg_desc;
260
261
262 PROCEDURE inv_get_icg_desc(
263 inv_item_id IN NUMBER,
264 first_elem_break IN NUMBER DEFAULT 30,
265 use_name_as_first_elem IN VARCHAR2 DEFAULT 'N',
266 delimiter IN VARCHAR2 DEFAULT NULL,
267 show_all_delim IN VARCHAR2 DEFAULT 'Y',
268 description_for_item OUT NOCOPY VARCHAR2,
269 error_text IN OUT NOCOPY VARCHAR2
270 ) IS
271 icg_id_val NUMBER;
272 c_desc_el_values VARCHAR2(240);
273 icg_desc_or_name VARCHAR2(240);
274 dummyerr VARCHAR2(500);
275 delim_val VARCHAR2(1);
276 l1 NUMBER;
277 l2 NUMBER;
278 LEN1 NUMBER;
279 LEN2 NUMBER;
280 excess1 NUMBER;
281 excess2 NUMBER;
282
283 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
284
285 BEGIN
286 /*First of all check to see if the item has an ICG defined for it
287 **There is no point in proceeding further if there is no ICG for the item
288 */
289 BEGIN
290 SELECT item_catalog_group_id INTO icg_id_val
291 FROM mtl_system_items_B MSI
292 WHERE MSI.inventory_item_id = inv_item_id
293 AND rownum =1;
294 /*added rownum clause because this stmt may return more than one
295 identical rows*/
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 description_for_item := NULL;
299 error_text := NULL;
300 raise NO_DATA_FOUND; /* to be handled by enclosing block*/
301 WHEN OTHERS THEN
302 dummyerr:= error_text|| ' INVICGDS(3-1): '||SQLCODE||':'
303 ||substrb(SQLERRM,1,30);
304 error_text := substr(dummyerr, 1, 240);
305 END;
306
307 c_desc_el_values := NULL;
308 icg_desc_or_name := NULL;
309
310 INVICGDS.inv_concat_desc_values(
311 inv_item_id,
312 icg_id_val,
313 delimiter,
314 show_all_delim,
315 c_desc_el_values,
316 error_text);
317
318 IF (use_name_as_first_elem = 'Y') THEN
319 SELECT MICGK.concatenated_segments into icg_desc_or_name
320 FROM mtl_item_catalog_groups_kfv MICGK
321 WHERE MICGK.item_catalog_group_id = icg_id_val;
322
323 ELSIF (use_name_as_first_elem = 'N') THEN
324 SELECT MICG.description into icg_desc_or_name
325 FROM mtl_item_catalog_groups MICG
326 WHERE MICG.item_catalog_group_id = icg_id_val;
327 ELSE
328 error_text:= error_text||' INVICGDS(3-2): use_name_as_first_elem should be Y/N';
329 /*Error in parameter*/
330 END IF;
331
332 IF (delimiter is NULL) THEN
333 SELECT FT.concatenated_segment_delimiter INTO delim_val
334 FROM fnd_id_flex_structures FT
335 WHERE FT.ID_FLEX_CODE = 'MICG'
336 AND FT.APPLICATION_ID = 401;
337 ELSE
338 delim_val := delimiter;
339 END IF;
340
341
342 IF (icg_desc_or_name is not NULL) THEN
343
344 l1:= length(icg_desc_or_name);
345 LEN1:= first_elem_break;
346 excess1:= LEN1 - l1;
347 l2:= length(c_desc_el_values);
348 LEN2:= 240 - LEN1;
349 excess2:= LEN2 - l2;
350
351 IF l_inv_debug_level IN(101, 102) THEN
352 INVPUTLI.info('info'||l1|| ' '||LEN1 || ' '|| excess1);
353 INVPUTLI.info('info'||l2|| ' '|| LEN2|| ' '|| excess2);
354 END IF;
355
356 IF (l1 = LEN1 OR l2 = LEN2) THEN
357 icg_desc_or_name := substr(icg_desc_or_name, 1, least(LEN1, l1));
358 c_desc_el_values := substr(c_desc_el_values, 1, least(LEN2, l2));
359 ELSIF (l1 > LEN1 AND l2 < LEN2) THEN
360 icg_desc_or_name := substr(icg_desc_or_name, 1, least(LEN1 + excess2 , l1));
361 ELSIF (l1 < LEN1 AND l2 > LEN2) THEN
362 c_desc_el_values := substr(c_desc_el_values, 1, least(LEN2 + excess1 , l2));
363 ELSIF (l1 > LEN1 AND l2 > LEN2) THEN
364 icg_desc_or_name := substr(icg_desc_or_name, 1, LEN1);
365 c_desc_el_values := substr(c_desc_el_values, 1, LEN2);
366 ELSE
367 /*(l1 < LEN1 AND l2 < LEN2) do nothing*/
368 null;
369 END IF;
370
371 description_for_item := icg_desc_or_name||c_desc_el_values;
372
373 ELSE /*icg_desc_or_name is null
374 assign c_desc_el_values to description_for_item
375 but take out the leading delimiter */
376
377 description_for_item := substr(c_desc_el_values, 2, 240);
378
379 END IF;
380
381 <<get_out>>
382 NULL;
383
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN NULL;
386 /*This basically handles the subblock returned no_data_found
387 We found that using user defined exception did not work (pl/sql bug?)
388 The first select stmt is the one MOST likely to get caught in
389 no_data_found.
390 If data found there the other selects are pretty much guaranteed
391 to work okay
392 */
393
394 WHEN OTHERS THEN
395 dummyerr:= error_text|| ' INVICGDS(3-3): '||SQLCODE||':'
396 ||substrb(SQLERRM,1,30);
397 error_text := substr(dummyerr, 1, 240);
398
399 END inv_get_icg_desc;
400
401
402 PROCEDURE inv_concat_desc_values(
403 inv_item_id IN NUMBER,
404 icg_id IN NUMBER,
405 delimiter IN VARCHAR2 DEFAULT NULL,
406 show_all_delim IN VARCHAR2 DEFAULT 'Y',
407 concat_desc OUT NOCOPY VARCHAR2,
408 err_text IN OUT NOCOPY VARCHAR2
409 ) IS
410
411 c_desc VARCHAR2(240);
412 dummyerr VARCHAR2(500);
413 delim_val VARCHAR2(1);
414 first_elem_val NUMBER;
415
416 CURSOR get_desc_elem( iii NUMBER) IS
417 SELECT element_name, element_value, element_sequence
418 FROM mtl_descr_element_values MDEV
419 WHERE MDEV.inventory_item_id = iii
420 AND MDEV.default_element_flag = 'Y'
421 ORDER BY element_sequence;
422
423 CURSOR get_not_null_desc_elem( iii NUMBER) IS
424 SELECT element_name, element_value, element_sequence
425 FROM mtl_descr_element_values MDEV
426 WHERE MDEV.inventory_item_id = iii
427 AND MDEV.default_element_flag = 'Y'
428 AND MDEV.element_value is not NULL
429 ORDER BY element_sequence;
430
431 BEGIN
432
433 IF (delimiter is NULL) THEN
434 SELECT FT.concatenated_segment_delimiter INTO delim_val
435 FROM fnd_id_flex_structures FT
436 WHERE FT.ID_FLEX_CODE = 'MICG'
437 AND FT.APPLICATION_ID = 401;
438 ELSE
439 delim_val := delimiter;
440 END IF;
441
442 c_desc := delim_val; /*Initialize c_desc to delimiter*/
443
444 first_elem_val := 1;
445
446 IF (show_all_delim = 'Y') then
447 FOR gde IN get_desc_elem(inv_item_id) LOOP
448 if first_elem_val = 1 then
449 c_desc := delim_val||gde.element_value;
450 first_elem_val := 0;
451 else
452 /*Bug 14308401 Added the condition to check the length of c_desc */
453 IF (length(c_desc)>=240) THEN
454 NULL;
455 ELSIF ((length(gde.element_value)+length(c_desc)+1)>240) THEN
456 c_desc := c_desc||delim_val||substr(gde.element_value, 1,239-length(c_desc));
457 ELSE
458 c_desc := c_desc||delim_val||gde.element_value;
459 END IF;
460 end if;
461 END LOOP;
462
463 ELSIF (show_all_delim = 'N') then
464 /*Same logic as above but with a different cursor*/
465 FOR gde IN get_not_null_desc_elem(inv_item_id) LOOP
466 if first_elem_val = 1 then
467 c_desc := delim_val||gde.element_value;
468 first_elem_val := 0;
469 else
470 /*Bug 14308401 Added the condition to check the length of c_desc */
471 IF (length(c_desc)>=240) THEN
472 NULL;
473 ELSIF ((length(gde.element_value)+length(c_desc)+1)>240) THEN
474 c_desc := c_desc||delim_val||substr(gde.element_value, 1,239-length(c_desc));
475 ELSE
476 c_desc := c_desc||delim_val||gde.element_value;
477 END IF;
478 end if;
479 END LOOP;
480
481 ELSE
482 c_desc:= 'Error: show_all_delim in inv_concat_desc_values should be Y/N';
483
484 END IF;
485 concat_desc := c_desc;
486
487 EXCEPTION
488 WHEN OTHERS THEN
489 dummyerr := err_text||'INVICGDS(4):'||SQLCODE||':'||substrb(SQLERRM, 1,30);
490 err_text := substr(dummyerr, 1, 240);
491
492 END inv_concat_desc_values;
493
494
495 END INVICGDS;
[Home] [Help]