[Home] [Help]
PACKAGE BODY: APPS.AMS_MTL_CATG_LOADER_PVT
Source
1 PACKAGE BODY AMS_MTL_CATG_LOADER_PVT AS
2 /* $Header: amsvmcab.pls 120.2 2006/01/11 04:42:36 inanaiah noship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- Load_Inv_Category
8 --
9 -- PURPOSE
10 -- This procedure is created to as a concurrent program which
11 -- will load inventory categories into ams denorm table.
12 --
13 -- NOTES
14 --
15 --
16 -- HISTORY
17 -- 07/01/2002 ABHOLA Created
18 -- End of Comments
19
20 PROCEDURE Load_Inv_Category
21 (errbuf OUT NOCOPY VARCHAR2,
22 retcode OUT NOCOPY NUMBER)
23
24 IS
25 valid_ccid boolean ;
26
27 /** USE DISABLE DATE
28 -- cursor c1 is select category_id, structure_id
29 -- from mtl_categories_vl
30 -- where ENABLED_FLAG = 'Y'
31 -- and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
32 -- and nvl(END_DATE_ACTIVE,sysdate) >= sysdate ;
33 ***/
34
35
36 cursor c1 is select category_id, structure_id
37 from mtl_categories_vl
38 where nvl(DISABLE_DATE,sysdate) >= sysdate ;
39
40 c1_rec c1%ROWTYPE;
41
42
43 /*****cursor get_lang is select language_code
44 from fnd_languages
45 where installed_flag in ('I','B');
46 l_language VARCHAR2(20); ****/
47
48
49 cursor get_cat_b is select count(*) from AMS_MTL_CATEGORIES_DENORM_B;
50
51 cursor get_cat_tl is select count(*) from AMS_MTL_CATEGORIES_DENORM_TL;
52
53 lcount NUMBER;
54
55
56 begin
57
58 /***** OPEN get_lang;
59 FETCH get_lang INTO l_language;
60 CLOSE get_lang; ******/
61
62 OPEN get_cat_b;
63 FETCH get_cat_b into lcount;
64 CLOSE get_cat_b;
65
66 if (lcount > 1) then
67
68 delete from AMS_MTL_CATEGORIES_DENORM_B;
69
70 end if;
71
72 OPEN get_cat_tl;
73 FETCH get_cat_tl into lcount;
74 CLOSE get_cat_tl;
75
76 if (lcount > 1) then
77
78 delete from AMS_MTL_CATEGORIES_DENORM_TL;
79
80 end if;
81
82
83 OPEN get_cat_b;
84 FETCH get_cat_b into lcount;
85 CLOSE get_cat_b;
86
87
88 for c1_rec IN c1 LOOP
89
90 valid_ccid := FND_FLEX_KEYVAL.validate_ccid(appl_short_name => 'INV',
91 key_flex_code => 'MCAT',
92 structure_number => c1_rec.structure_id,
93 combination_id => c1_rec.category_id);
94 if (valid_ccid) then
95
96
97 INSERT INTO AMS_MTL_CATEGORIES_DENORM_B (
98 CATEGORY_ID ,
99 STRUCTURE_ID ,
100 LAST_UPDATE_DATE ,
101 LAST_UPDATED_BY ,
102 CREATION_DATE ,
103 CREATED_BY ,
104 LAST_UPDATE_LOGIN ,
105 concatenated_ids )
106 VALUES (
107 c1_rec.category_id,
108 c1_rec.structure_id,
109 sysdate,
110 FND_GLOBAL.user_id,
111 sysdate,
112 FND_GLOBAL.user_id,
113 FND_GLOBAL.conc_login_id,
114 FND_FLEX_KEYVAL.concatenated_values
115 );
116
117 insert into AMS_MTL_CATEGORIES_DENORM_TL (
118 CATEGORY_ID ,
119 LANGUAGE ,
120 SOURCE_LANG ,
121 LAST_UPDATE_DATE ,
122 LAST_UPDATED_BY ,
123 CREATION_DATE ,
124 CREATED_BY ,
125 LAST_UPDATE_LOGIN ,
126 concatenated_description )
127 SELECT
128 c1_rec.category_id,
129 l.language_code,
130 USERENV('LANG'),
131 sysdate,
132 FND_GLOBAL.user_id,
133 sysdate,
134 FND_GLOBAL.user_id,
135 FND_GLOBAL.conc_login_id,
136 FND_FLEX_KEYVAL.concatenated_descriptions
137 FROM fnd_languages l
138 WHERE l.installed_flag IN ('I', 'B')
139 AND NOT EXISTS (SELECT NULL
140 FROM AMS_MTL_CATEGORIES_DENORM_TL t
141 WHERE t.category_id = c1_rec.category_id
142 AND t.language = l.language_code);
143
144
145 else
146
147
148 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Invalid Category '||to_char(c1_rec.category_id) );
149
150 end if;
151
152 END LOOP;
153
154 retcode := 0;
155
156 commit;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160
161 retcode := 1;
162
163 END;
164
165 -- ===========================================
166 -- ADD_LANGUAGE
167 --=============================================
168 procedure ADD_LANGUAGE
169 is
170 begin
171 delete from AMS_MTL_CATEGORIES_DENORM_TL T
172 where not exists
173 (select NULL
174 from AMS_MTL_CATEGORIES_DENORM_B B
175 where B.CATEGORY_ID = T.CATEGORY_ID
176 );
177
178 update AMS_MTL_CATEGORIES_DENORM_TL T set (
179 CATEGORY_ID
180 ) = (select
181 B.CATEGORY_ID
182 from AMS_MTL_CATEGORIES_DENORM_TL B
183 where B.CATEGORY_ID = T.CATEGORY_ID
184 and B.LANGUAGE = T.SOURCE_LANG)
185 where (
186 T.CATEGORY_ID,
187 T.LANGUAGE
188 ) in (select
189 SUBT.CATEGORY_ID,
190 SUBT.LANGUAGE
191 from AMS_MTL_CATEGORIES_DENORM_TL SUBB, AMS_MTL_CATEGORIES_DENORM_TL SUBT
192 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
193 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
194 and (SUBB.CATEGORY_ID <> SUBT.CATEGORY_ID
195 ));
196
197
198 INSERT INTO AMS_MTL_CATEGORIES_DENORM_TL (
199 CATEGORY_ID ,
200 LANGUAGE ,
201 SOURCE_LANG ,
202 LAST_UPDATE_DATE ,
203 LAST_UPDATED_BY ,
204 CREATION_DATE ,
205 CREATED_BY ,
206 LAST_UPDATE_LOGIN ,
207 concatenated_description )
208 SELECT
209 B.CATEGORY_ID,
210 l.language_code,
211 B.SOURCE_LANG,
212 B.LAST_UPDATE_DATE,
213 B.LAST_UPDATED_BY,
214 B.CREATION_DATE,
215 B.CREATED_BY,
216 B.LAST_UPDATE_LOGIN,
217 B.concatenated_description
218 from AMS_MTL_CATEGORIES_DENORM_TL B, FND_LANGUAGES L
219 where L.INSTALLED_FLAG in ('I', 'B')
220 and B.LANGUAGE = userenv('LANG')
221 and not exists
222 (select NULL
223 from AMS_MTL_CATEGORIES_DENORM_TL T
224 where T.CATEGORY_ID = B.CATEGORY_ID
225 and T.LANGUAGE = L.LANGUAGE_CODE);
226
227 end ADD_LANGUAGE;
228
229
230 PROCEDURE UPGRADE_INTEREST_TYPES
231 (errbuf OUT NOCOPY VARCHAR2,
232 retcode OUT NOCOPY NUMBER)
233 IS
234 CURSOR c_get_comp
235 IS
236 SELECT interest_type_id,Competitor_product_id
237 ,COMPETITOR_PRODUCT_NAME
238 FROM ams_competitor_products_vl
239 WHERE interest_type_id is not null;
240
241 l_interest_type_id NUMBER;
242 l_competitor_prod_id NUMBER;
243 l_comp_name VARCHAR2(240);
244
245 CURSOR c_get_category
246 IS
247 SELECT PRODUCT_CATEGORY_ID
248 ,PRODUCT_CAT_SET_ID
249 FROM as_interest_types_b
250 where interest_type_id = l_interest_type_id;
251
252 l_cat_rec c_get_category%ROWTYPE;
253
254 CURSOR c_get_interest_type
255 IS
256 SELECT interest_type
257 FROM as_interest_types_tl
258 where interest_type_id = l_interest_type_id
259 and language = userenv('LANG');
260
261 l_interest_type varchar2(80);
262
263 i NUMBER := 0;
264
265 BEGIN
266 OPEN c_get_comp;
267 LOOP
268 FETCH c_get_comp INTO l_interest_type_id,l_competitor_prod_id,l_comp_name;
269 EXIT WHEN c_get_comp%NOTFOUND;
270
271 OPEN c_get_category;
272 FETCH c_get_category INTO l_cat_rec;
273
274 IF c_get_category%FOUND
275 AND l_cat_rec.product_category_id is not null
276 AND l_cat_rec.product_cat_set_id is not null
277 THEN
278 UPDATE ams_competitor_products_b
279 SET category_id = l_cat_rec.product_category_id
280 ,category_set_id = l_cat_rec.product_cat_set_id
281 WHERE competitor_product_id = l_competitor_prod_id;
282 ELSE
283
284 OPEN c_get_interest_type;
285 FETCH c_get_interest_type INTO l_interest_type;
286 CLOSE c_get_interest_type;
287 IF ( i=0) THEN
288 FND_MESSAGE.Set_Name('AMS', 'AMS_API_INTEREST_TYPE_MISSING');
289 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get());
290 -- Category Mapping is missing for the following Interest Types in the as_interest_types:
291 END IF;
292 FND_MESSAGE.Set_Name('AMS', 'AMS_INTEREST_TYPE_ASSOC_COMP');
293 -- Interest Type NAME ( Id: ID) associated with the COMPNAME (Competitor_Id: COMPID).
294 FND_MESSAGE.Set_Token('NAME',l_interest_type);
295 FND_MESSAGE.Set_Token('ID',l_interest_type_id);
296 FND_MESSAGE.Set_Token('COMPNAME',l_comp_name);
297 FND_MESSAGE.Set_Token('COMPID',l_competitor_prod_id);
298 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get());
299 i := i+1;
300 END IF;
301 CLOSE c_get_category;
302 END LOOP;
303 CLOSE c_get_comp;
304
305 retcode := 0;
306
307 commit;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311
312 retcode := 1;
313
314 END UPGRADE_INTEREST_TYPES;
315
316 PROCEDURE UPGRADE_CATEGORIES
317 (errbuf OUT NOCOPY VARCHAR2,
318 retcode OUT NOCOPY NUMBER)
319 IS
320
321 CURSOR c_get_cat
322 IS
323 SELECT activity_product_id,category_set_id
324 ,category_id, act_product_used_by_id, arc_act_product_used_by
325 FROM ams_act_products act
326 WHERE level_type_code = 'FAMILY'
327 and category_id is not null
328 and category_set_id is not null
329 and category_set_id not in (select distinct category_set_id
330 from ENI_PROD_DEN_HRCHY_PARENTS_V);
331
332 l_activity_product_id NUMBER;
333 l_category_set_id NUMBER;
334 l_category_id NUMBER;
335 l_obj_id NUMBER;
336 l_obj_type VARCHAR2(10);
337
338
339 CURSOR c_get_map_cat
340 IS
341 SELECT dt.target_catg_id cat_id, hd.target_catg_set_id cat_set_id
342 FROM ego_catg_map_hdrs_b hd
343 ,ego_catg_map_dtls dt
344 WHERE hd.source_catg_set_id = l_category_set_id
345 AND dt.source_catg_id = l_category_id
346 AND hd.catg_map_id = dt.catg_map_id;
347
348 l_cat_rec c_get_map_cat%ROWTYPE;
349
350 CURSOR c_get_cat_name( cat_id IN NUMBER)
351 IS
352 SELECT description
353 from mtl_categories_vl
354 where category_id = cat_id ;
355
356 l_cat_name_rec c_get_cat_name%ROWTYPE;
357 i number := 0;
358
359 BEGIN
360
361 OPEN c_get_cat;
362 LOOP
363 FETCH c_get_cat INTO l_activity_product_id,l_category_set_id,l_category_id,l_obj_id,l_obj_type;
364 EXIT WHEN c_get_cat%NOTFOUND;
365
366 OPEN c_get_cat_name(l_category_id);
367 FETCH c_get_cat_name INTO l_cat_name_rec.description ;
368 CLOSE c_get_cat_name;
369
370 OPEN c_get_map_cat;
371 FETCH c_get_map_cat INTO l_cat_rec;
372
373 IF c_get_map_cat%FOUND
374 THEN
375 UPDATE ams_act_products
376 SET category_id = l_cat_rec.cat_id
377 ,category_set_id = l_cat_rec.cat_set_id
378 WHERE activity_product_id = l_activity_product_id;
379 ELSE
380 IF (i = 0) THEN
381 FND_MESSAGE.Set_Name('AMS', 'AMS_API_CAT_MAPPING_MISSING');
382 -- Category Mapping is missing for the following:
383 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get());
384 END IF;
385 FND_MESSAGE.Set_Name('AMS', 'AMS_API_CAT_ASSOC_OBJECT');
386 -- NAME ( Id: ID) category associated with the object OBJTYPE (ObjId: OBJID).
387 FND_MESSAGE.Set_Token('NAME',l_cat_name_rec.description);
388 FND_MESSAGE.Set_Token('ID',l_category_id);
389 FND_MESSAGE.Set_Token('OBJTYPE',l_obj_type);
390 FND_MESSAGE.Set_Token('OBJID',l_obj_id);
391 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get());
392 i := i+1;
393 END IF;
394 CLOSE c_get_map_cat;
395 END LOOP;
396 CLOSE c_get_cat;
397
398 -- R12 : Changes due to Marketing Requirement for data migration
399 BEGIN
400
401 UPDATE AMS_ACT_PRODUCTS A
402 SET (A.CATEGORY_ID, A.CATEGORY_SET_ID) =
403 (SELECT B.CATEGORY_ID, B.CATEGORY_SET_ID
404 FROM MTL_ITEM_CATEGORIES B,
405 MTL_DEFAULT_CATEGORY_SETS D
406 WHERE B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
407 AND B.ORGANIZATION_ID = A.ORGANIZATION_ID
408 AND D.CATEGORY_SET_ID = B.CATEGORY_SET_ID
409 AND D.FUNCTIONAL_AREA_ID = 11)
410 WHERE A.CATEGORY_ID IS NULL
411 AND A.LEVEL_TYPE_CODE = 'PRODUCT'
412 AND ARC_ACT_PRODUCT_USED_BY IN ('CAMP','CSCH');
413
414 END;
415
416 retcode := 0;
417
418 commit;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422
423 retcode := 1;
424
425 END UPGRADE_CATEGORIES;
426
427
428 END AMS_MTL_CATG_LOADER_PVT;