[Home] [Help]
PACKAGE BODY: APPS.QP_ITEMGROUP_UPG_UTIL_PVT
Source
1 PACKAGE BODY QP_ITEMGROUP_UPG_UTIL_PVT AS
2 /* $Header: QPXVUIGB.pls 120.0 2005/06/01 23:58:01 appldev noship $ */
3
4 err_msg VARCHAR2(240);
5
6 PROCEDURE Upgrade_Item_Groups IS
7
8 l_control_level NUMBER := 2;
9
10 l_new_structure_id NUMBER := 0;
11
12 l_new_category_id NUMBER := 0;
13 l_new_category_set_id NUMBER := 0;
14 l_enabled_flag VARCHAR2(1) := 'Y';
15 l_organization_id VARCHAR2(10);
16 l_description VARCHAR2(50) := 'Category to Upgrade Item Groups';
17 l_user_id NUMBER;
18
19 l_new_structure FND_FLEX_KEY_API.structure_type;
20 l_new_segment FND_FLEX_KEY_API.segment_type;
21 l_flexfield FND_FLEX_KEY_API.flexfield_type;
22 l_structure FND_FLEX_KEY_API.structure_type;
23
24 err_num NUMBER := 0;
25 err_msg VARCHAR2(240) := '';
26
27 CURSOR oe_item_groups_cur
28 IS
29 SELECT *
30 FROM oe_item_groups;
31
32 CURSOR oe_item_group_lines_cur (p_group_id NUMBER)
33 IS
34 SELECT *
35 FROM oe_item_group_lines
36 WHERE group_id = p_group_id;
37
38 BEGIN
39
40 FND_FLEX_KEY_API.set_session_mode('customer_data');
41
42 --Find the Item Categories flexfield
43 l_flexfield := FND_FLEX_KEY_API.find_flexfield(appl_short_name => 'INV',
44 flex_code => 'MCAT');
45
46
47 -- Find the structure if it already exists.
48 BEGIN
49 l_structure := FND_FLEX_KEY_API.find_structure(flexfield => l_flexfield,
50 structure_code => 'PRICELIST_ITEM_CATEGORIES');
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 NULL;
54 END;
55
56 --If structure already exists, i.e. upgrade has been run before
57 IF l_structure.structure_number IS NOT NULL THEN
58
59 BEGIN
60
61 --Get the structure_id corresponding to this structure
62 SELECT id_flex_num
63 INTO l_new_structure_id
64 FROM fnd_id_flex_structures
65 WHERE id_flex_code = 'MCAT'
66 AND id_flex_structure_code = 'PRICELIST_ITEM_CATEGORIES';
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 err_msg := substr(sqlerrm, 1, 240);
71 rollback;
72 QP_UTIL.Log_Error(
73 p_id1 => 'Structure Id '||to_char(l_new_structure_id),
74 p_error_type => 'ITEM_GROUP',
75 p_error_desc => err_msg,
76 p_error_module => 'Upgrade_Item_Groups');
77 raise;
78 END;
79
80 ELSE --If structure does not exist i.e.upgrade script not run before
81
82 --Create new Structure for the Item Categories FlexField
83 l_new_structure := FND_FLEX_KEY_API.new_structure(flexfield => l_flexfield,
84 structure_code => 'PRICELIST_ITEM_CATEGORIES',
85 structure_title => 'PriceList Item Categories',
86 description =>'Item Categories for PriceLists',
87 view_name => '',
88 freeze_flag => 'Y',
89 enabled_flag => 'Y',
90 segment_separator => '.',
91 cross_val_flag => 'N',
92 freeze_rollup_flag => 'N',
93 dynamic_insert_flag => 'N',
94 shorthand_enabled_flag => 'N',
95 shorthand_prompt => '',
96 shorthand_length => 0
97 );
98
99 --Add the newly created structure to the Item Categories flexfield
100 FND_FLEX_KEY_API.add_structure(flexfield => l_flexfield,
101 structure => l_new_structure);
102
103 --Create a new dummy segment for the new structure
104 l_new_segment := FND_FLEX_KEY_API.new_segment(flexfield => l_flexfield,
105 structure => l_new_structure,
106 segment_name => 'Dummy',
107 description => 'Dummy',
108 column_name => 'SEGMENT1',
109 segment_number => 1,
110 enabled_flag => 'Y',
111 displayed_flag => 'Y',
112 indexed_flag => 'Y',
113 value_set => '',
114 default_type => '',
115 default_value => '',
116 required_flag => 'N',
117 security_flag => 'N',
118 range_code => '',
119 display_size => 10,
120 description_size => 30,
121 concat_size => 30,
122 lov_prompt => 'Dummy',
123 window_prompt => 'Dummy');
124
125 --Add the newly created dummy segment to the new structure
126 FND_FLEX_KEY_API.add_segment(flexfield => l_flexfield,
127 structure => l_new_structure,
128 segment => l_new_segment);
129
130 --Fetch Structure id of newly created Structure;
131 BEGIN
132
133 SELECT id_flex_num
134 INTO l_new_structure_id
135 FROM fnd_id_flex_structures
136 WHERE id_flex_code = 'MCAT'
137 AND id_flex_structure_code = 'PRICELIST_ITEM_CATEGORIES';
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 err_msg := substr(sqlerrm, 1, 240);
142 rollback;
143 QP_UTIL.Log_Error(
144 p_id1 => 'Structure Id '||to_char(l_new_structure_id),
145 p_error_type => 'ITEM_GROUP',
146 p_error_desc => err_msg,
147 p_error_module => 'Upgrade_Item_Groups');
148 raise;
149 END;
150
151 END IF; --structure doesn't exist, upgrade not run before
152
153 --Get the organization_id
154 l_organization_id := QP_UTIL.Get_Item_Validation_Org;
155
156 --Get the User_id
157 l_user_id := FND_GLOBAL.USER_ID;
158
159 SELECT mtl_categories_s.nextval
160 INTO l_new_category_id
161 FROM dual;
162
163 BEGIN
164 INSERT INTO mtl_categories_b
165 (
166 category_id,
167 structure_id,
168 last_update_date,
169 last_updated_by,
170 creation_date,
171 created_by,
172 -- description,
173 summary_flag,
174 enabled_flag,
175 segment1
176 )
177 SELECT
178 l_new_category_id,
179 l_new_structure_id,
180 sysdate,
181 l_user_id,
182 sysdate,
183 l_user_id,
184 -- l_description,
185 'N',
186 l_enabled_flag, -- whether segment combination is enabled
187 'Item Category for Item Groups'
188 FROM dual
189 WHERE NOT EXISTS (SELECT 'X'
190 FROM mtl_categories_b b
191 WHERE b.structure_id = l_new_structure_id
192 AND b.segment1 = 'Item Category for Item Groups');
193
194 INSERT INTO mtl_categories_tl
195 (
196 category_id,
197 language,
198 source_lang,
199 description,
200 last_update_date,
201 last_updated_by,
202 creation_date,
203 created_by
204 )
205 SELECT
206 l_new_category_id,
207 l.LANGUAGE_CODE,
208 userenv('LANG'),
209 l_description,
210 sysdate,
211 l_user_id,
212 sysdate,
213 l_user_id
214 FROM FND_LANGUAGES l
215 WHERE l.INSTALLED_FLAG in ('I', 'B')
216 AND NOT EXISTS (SELECT 'X'
217 FROM mtl_categories_tl t
218 WHERE t.category_id = l_new_category_id
219 AND t.language = l.LANGUAGE_CODE)
220 AND EXISTS (SELECT 'X'
221 FROM mtl_categories_b b
222 WHERE b.category_id = l_new_category_id);
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 err_msg := substr(sqlerrm, 1, 240);
227 rollback;
228 QP_UTIL.Log_Error(
229 p_id1 => 'Category Id '||to_char(l_new_category_id),
230 p_id2 => 'Structure Id '||to_char(l_new_structure_id),
231 p_error_type => 'ITEM_GROUP',
232 p_error_desc => err_msg,
233 p_error_module => 'Upgrade_Item_Groups');
234 raise;
235 END;
236
237 commit;
238
239 FOR l_oe_item_groups_rec IN oe_item_groups_cur
240 LOOP
241
242 SELECT mtl_category_sets_s.nextval
243 INTO l_new_category_set_id
244 FROM dual;
245
246 BEGIN
247 INSERT INTO mtl_category_sets_b
248 (
249 category_set_id,
250 -- category_set_name,
251 structure_id,
252 validate_flag,
253 control_level,
254 -- description,
255 last_update_date,
256 last_updated_by,
257 creation_date,
258 created_by,
259 mult_item_cat_assign_flag
260 )
261 SELECT
262 l_new_category_set_id,
263 -- upper(l_oe_item_groups_rec.name) || '_CATEGORY_SET',
264 l_new_structure_id,
265 'N',
266 l_control_level, --Control level is item_level
267 -- l_oe_item_groups_rec.description,
268 l_oe_item_groups_rec.last_update_date,
269 l_oe_item_groups_rec.last_updated_by,
270 l_oe_item_groups_rec.creation_date,
271 l_oe_item_groups_rec.created_by,
272 'Y'
273 FROM dual
274 WHERE NOT EXISTS (SELECT 'X'
275 FROM mtl_category_sets_b b, mtl_category_sets_tl t
276 WHERE b.category_set_id = t.category_set_id
277 AND b.structure_id = l_new_structure_id
278 AND t.category_set_name =
279 substr(upper(l_oe_item_groups_rec.name),1,15) || '_CATEGORY_SET');
280
281 INSERT INTO mtl_category_sets_tl
282 (
283 category_set_id,
284 language,
285 source_lang,
286 category_set_name,
287 description,
288 last_update_date,
289 last_updated_by,
290 creation_date,
291 created_by
292 )
293 SELECT
294 l_new_category_set_id,
295 l.LANGUAGE_CODE,
296 userenv('LANG'),
297 substr(upper(l_oe_item_groups_rec.name),1,15) || '_CATEGORY_SET',
298 substr(l_oe_item_groups_rec.description,1, 240),
299 l_oe_item_groups_rec.last_update_date,
300 l_oe_item_groups_rec.last_updated_by,
301 l_oe_item_groups_rec.creation_date,
302 l_oe_item_groups_rec.created_by
303 FROM FND_LANGUAGES l
304 WHERE l.INSTALLED_FLAG IN ('I', 'B')
305 AND NOT EXISTS (SELECT 'X'
306 FROM mtl_category_sets_tl t
307 WHERE t.category_set_id = l_new_category_set_id
308 AND t.language = l.LANGUAGE_CODE)
309 AND EXISTS (SELECT 'X'
310 FROM mtl_category_sets_b b
311 WHERE b.category_set_id = l_new_category_set_id);
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 err_msg := substr(sqlerrm, 1, 240);
316 rollback;
317 QP_UTIL.Log_Error(
318 p_id1 => 'Category Set Id '||to_char(l_new_category_set_id),
319 p_id2 => 'Structure Id '||to_char(l_new_structure_id),
320 p_error_type => 'ITEM_GROUP',
321 p_error_desc => err_msg,
322 p_error_module => 'Upgrade_Item_Groups');
323 raise;
324 END;
325
326 FOR l_oe_item_group_lines_rec IN oe_item_group_lines_cur
327 (l_oe_item_groups_rec.group_id)
328 LOOP
329
330 BEGIN
331 INSERT INTO mtl_item_categories
332 (
333 inventory_item_id,
334 organization_id,
335 category_set_id,
336 category_id,
337 last_update_date,
338 last_updated_by,
339 creation_date,
340 created_by
341 )
342 SELECT
343 l_oe_item_group_lines_rec.inventory_item_id,
344 TO_NUMBER(l_organization_id),
345 l_new_category_set_id,
346 l_new_category_id,
347 l_oe_item_group_lines_rec.last_update_date,
348 l_oe_item_group_lines_rec.last_updated_by,
349 l_oe_item_group_lines_rec.creation_date,
350 l_oe_item_group_lines_rec.created_by
351 FROM dual
352 WHERE EXISTS (SELECT 'X'
353 FROM mtl_categories_b b
354 WHERE b.category_id = l_new_category_id)
355 AND EXISTS (SELECT 'X'
356 FROM mtl_category_sets_b s
357 WHERE s.category_set_id = l_new_category_set_id);
358
359 EXCEPTION
360 WHEN DUP_VAL_ON_INDEX OR NO_DATA_FOUND OR VALUE_ERROR THEN
361 err_msg := substr(sqlerrm, 1, 240);
362 rollback;
363 QP_UTIL.Log_Error(
364 p_id1 => 'Group Id '||to_char(l_oe_item_group_lines_rec.group_id),
365 p_id2 => 'Inventory Item Id '||to_char(l_oe_item_group_lines_rec.inventory_item_id),
366 p_error_type => 'ITEM_GROUP',
367 p_error_desc => err_msg,
368 p_error_module => 'Upgrade_Item_Groups');
369
370 WHEN OTHERS THEN
371 err_msg := substr(sqlerrm, 1, 240);
372 rollback;
373 QP_UTIL.Log_Error(
374 p_id1 => 'Category Set Id '||to_char(l_new_category_set_id),
375 p_id2 => 'Category Id '||to_char(l_new_category_id),
376 p_error_type => 'ITEM_GROUP',
377 p_error_desc => err_msg,
378 p_error_module => 'Upgrade_Item_Groups');
379 raise;
380 END;
381
382 commit;
383
384 END LOOP; /* Loop over records in oe_item_group_lines */
385
386 commit;
387
388 END LOOP; /* Loop over records in oe_item_groups */
389
390 EXCEPTION
391 WHEN OTHERS THEN
392 err_msg := substr(sqlerrm, 1, 240);
393 IF err_msg IS NULL THEN
394 err_msg := substr(FND_FLEX_KEY_API.message, 1, 240);
395 END IF;
396 QP_UTIL.Log_Error(
397 p_id1 => 'Category Set Id '||to_char(l_new_category_set_id),
398 p_id2 => 'Category Id '||to_char(l_new_category_id),
399 p_error_type => 'ITEM_GROUP',
400 p_error_desc => err_msg,
401 p_error_module => 'Upgrade_Item_Groups');
402 raise;
403
404 END Upgrade_Item_Groups;
405 END QP_ITEMGROUP_UPG_UTIL_PVT;