1 Package Body ENI_VALUESET_CATEGORY AS
2 /* $Header: ENIITCTB.pls 115.26 2004/03/12 09:23:37 sbag noship $ */
3
4 g_error boolean:= false;
5 g_errbuf varchar2(10000);
6 g_warn boolean:= false;
7 g_count number;
8 l_vbh_catset_id number;
9 --
10
11 --
12 -- Purpose: Main Procedure to control the flow and handle concurrent manager variables
13
14 procedure ENI_POPULATE_MAIN
15 ( Errbuf out NOCOPY Varchar2,
16 retcode out NOCOPY Varchar2)
17 is
18
19 begin
20
21 FND_FILE.PUT_NAMES('enivalcat.log','enivalcat.out',fnd_profile.value('EDW_LOGFILE_DIR'));
22 g_error := eni_validate_structure;
23
24 if g_error = true then
25 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Validation ');
26 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
27 Errbuf:=g_errbuf;
28 retcode:=2;
29 else
30 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Validation complete');
31 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
32 eni_populate_category;
33
34 if g_warn = true then
35 Errbuf:=g_errbuf;
36 retcode:=1;
37 end if;
38
39 if g_error = true then
40 Errbuf:=g_errbuf;
41 retcode:=2;
42 end if;
43 end if;
44
45 end ENI_POPULATE_MAIN;
46
47
48
49 function ENI_VALIDATE_STRUCTURE return boolean
50 is
51 l_struct_code number;
52 l_application_column varchar(10);
53 l_enabled_flag varchar2(1);
54 l_flex_value_set_id number;
55 invalid_segment exception;
56 segment_not_enabled exception;
57 value_set_null exception;
58
59 begin
60
61 -- get vbh category set
62
63 BEGIN
64 select category_set_id into l_vbh_catset_id
65 from mtl_default_category_sets
66 where functional_area_id = 11;
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 l_vbh_catset_id := 1000000006;
71 END;
72
73 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category set id is:' || l_vbh_catset_id);
74
75 -- Check to see if the structure asscociated with the category set is PRODUCT_CATEGORIES --
76 begin
77 select id_flex_num into l_struct_code
78 from fnd_id_flex_structures a, mtl_category_sets_b b
79 where a.id_flex_num=b.structure_id
80 and b.category_set_id = l_vbh_catset_id
81 and id_flex_structure_code='PRODUCT_CATEGORIES';
82
83 fnd_file.put_line(fnd_file.log, 'Structure is: ' || l_struct_code);
84 exception
85 when NO_DATA_FOUND then
86 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: The flex structure associated with this category set is not PRODUCT_CATEGORIES');
87 g_error:=true;
88 goto end_block;
89 end;
90
91
92 -- Check to see if segment1 associated with the structure is valid --
93
94 select application_column_name, enabled_flag, to_number(flex_value_set_id)
95 into l_application_column, l_enabled_flag, l_flex_value_set_id
96 from fnd_id_flex_segments
97 where application_id = 401
98 and id_flex_code = 'MCAT'
99 and enabled_flag = 'Y'
100 and id_flex_num = l_struct_code;
101
102 fnd_file.put_line(fnd_file.log, 'Flexvalue set id ' || l_flex_value_set_id);
103
104 if l_application_column <> 'SEGMENT1' then
105 raise invalid_segment;
106 elsif l_flex_value_set_id is null then
107 raise value_set_null;
108 end if;
109
110
111
112 return g_error;
113
114 << end_block >>
115 null;
116
117 exception
118 when INVALID_SEGMENT then
119 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: The segment associated with this category set is not SEGMENT1');
120 g_error:=true;
121 return g_error;
122 when VALUE_SET_NULL then
123 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Value set associated with this category set is null');
124 g_error:=true;
125 return g_error;
126 when NO_DATA_FOUND then
127 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: There is no SEGMENT associated with PRODUCT_CATEGORIES category set OR it is disabled');
128 g_error:=true;
129 return g_error;
130 when TOO_MANY_ROWS then
131 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: More than 1 SEGMENT is associated with PRODUCT_CATEGORIES category set');
132 g_error:=true;
133 return g_error;
134 when OTHERS then
135 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: ' || sqlerrm);
136 g_error:=true;
137 return g_error;
138
139 end; -- Procedure to validate structure
140
141 FUNCTION get_flex_value_set_id(p_appl_id varchar2, p_id_flex_code Varchar2, p_vbh_catset_id number) RETURN number is
142 l_flex_value_set_id number;
143 l_struct_id NUMBER;
144 l_count_segments NUMBER;
145 begin
146
147 select structure_id into l_struct_id
148 from mtl_category_sets_b
149 where category_set_id = p_vbh_catset_id;
150
151 select count(flex_value_set_id)
152 into l_count_segments
153 from fnd_id_flex_segments
154 where application_id = p_appl_id
155 and id_flex_code = p_id_flex_code
156 and enabled_flag = 'Y'
157 and id_flex_num = l_struct_id;
158
159
160 if l_count_segments = 1 then
161
162 select flex_value_set_id into l_flex_value_set_id
163 from fnd_id_flex_segments
164 where application_id = p_appl_id
165 and id_flex_code = p_id_flex_code
166 and enabled_flag = 'Y'
167 and id_flex_num = l_struct_id;
168 else
169 l_flex_value_set_id := null;
170 end if;
171
172 return l_flex_value_set_id;
173
174 exception
175 when no_data_found then
176 return null;
177 when others then
178 raise;
179 end get_flex_value_set_id;
180
181
182
183 -- Purpose: Procedure to look at a valueset and populate the Financial
184 -- Reporting Category Set with categories.
185
186 procedure ENI_POPULATE_CATEGORY
187 is
188
189
190 l_value INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
191 l_return_status VARCHAR2(10000);
192 l_errorcode NUMBER;
193 l_msg_count NUMBER := 0;
194 l_msg_data VARCHAR2(10000);
195 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
196 l_category_id NUMBER;
197 -- l_vbh_catset_id NUMBER;
198 l_control NUMBER;
199 l_mult_flag VARCHAR2(1);
200 l_struct_code number;
201 l_data varchar2(10000);
202 l_msg_index_out varchar2(10000);
203 l_count number :=0;
204 l_catg_upd number := 0;
205 multiple_cat_allowed exception;
206 control_org exception;
207 l_value_set_id number;
208
209
210 -- Get all the values from the valueset attached to the category set "Product Classification"
211 cursor c1(l_value_set_id number) is
212 select /*+ first_rows */ val.FLEX_VALUE_MEANING l_segment1,
213 val.DESCRIPTION l_description,
214 val.SUMMARY_FLAG l_summary_flag,
215 val.ENABLED_FLAG l_enabled_flag,
216 val.START_DATE_ACTIVE l_start_date_active,
217 val.END_DATE_ACTIVE l_end_date_active,
218 mtb.category_id category_id,
219 cat.STRUCTURE_ID l_structure_id,
220 1 update_flag -- Update Flag to indicate updateable records
221 from FND_FLEX_VALUES_VL val, --FND_ID_FLEX_SEGMENTS seg,
222 MTL_CATEGORY_SETS cat, MTL_CATEGORIES_B mtb, MTL_CATEGORIES_TL mtl
223 where val.FLEX_VALUE_SET_ID = l_value_set_id -- seg.FLEX_VALUE_SET_ID
224 -- and seg.ID_FLEX_CODE='MCAT'
225 -- and seg.APPLICATION_ID = '401'
226 -- and seg.APPLICATION_COLUMN_NAME ='SEGMENT1'
227 -- and seg.ID_FLEX_NUM=cat.STRUCTURE_ID
228 and cat.CATEGORY_SET_ID = l_vbh_catset_id
229 and cat.structure_id = mtb.structure_id
230 and mtb.segment1 = val.flex_value_meaning
231 and mtl.category_id = mtb.category_id
232 and (to_date(to_char(val.last_update_date,'DD/MM/YYYY HH:MI'),'DD/MM/YYYY HH:MI')> to_date(to_char(mtb.last_update_date,'DD/MM/YYYY HH:MI'),'DD/MM/YYYY HH:MI')
233 or mtl.description <> val.description
234 or mtb.end_date_active <> val.end_date_active)
235 union all
236 select val.FLEX_VALUE_MEANING l_segment1,
237 val.DESCRIPTION l_description,
238 val.SUMMARY_FLAG l_summary_flag,
239 val.ENABLED_FLAG l_enabled_flag,
240 val.START_DATE_ACTIVE l_start_date_active,
241 val.END_DATE_ACTIVE l_end_date_active,
242 to_number(null),
243 cat.STRUCTURE_ID l_structure_id,
244 0 -- Insert Flag to indicate a new node
245 from FND_FLEX_VALUES_VL val,-- FND_ID_FLEX_SEGMENTS seg,
246 MTL_CATEGORY_SETS cat
247 where val.FLEX_VALUE_SET_ID = l_value_set_id -- seg.FLEX_VALUE_SET_ID
248 -- and seg.ID_FLEX_CODE='MCAT'
249 -- and seg.APPLICATION_ID = '401'
250 -- and seg.APPLICATION_COLUMN_NAME ='SEGMENT1'
251 -- and seg.ID_FLEX_NUM=cat.STRUCTURE_ID
252 and cat.CATEGORY_SET_ID = l_vbh_catset_id
253 and not exists(select category_id from mtl_categories_b
254 where structure_id = cat.structure_id
255 and segment1 = val.flex_value_meaning);
256
257 begin
258
259
260 -- Go thru the cursor and insert every value as a category in the category set
261
262 begin
263 select FLEX_VALUE_SET_ID
264 into l_value_set_id
265 from FND_ID_FLEX_SEGMENTS
266 where APPLICATION_ID = '401'
267 and ID_FLEX_CODE = 'MCAT'
268 and APPLICATION_COLUMN_NAME = 'SEGMENT1'
269 and ID_FLEX_NUM = (select STRUCTURE_ID
270 from MTL_CATEGORY_SETS_B
271 where CATEGORY_SET_ID = l_vbh_catset_id)
272 and ENABLED_FLAG = 'Y';
273
274 exception
275 when no_data_found then
276 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: The segment associated with
277 the default category set is not SEGMENT1');
278 g_error:=true;
279 goto end_block;
280 end;
281
282 FOR c1_rec IN c1(l_value_set_id) LOOP
283
284 l_count:=l_count+1;
285 l_category_rec.structure_id :=c1_rec.l_structure_id ;
286 l_category_rec.segment1 := c1_rec.l_segment1;
287 l_category_rec.description := c1_rec.l_description;
288 l_category_rec.summary_flag := c1_rec.l_summary_flag;
289 l_category_rec.enabled_flag := c1_rec.l_enabled_flag;
290 l_category_rec.start_date_active := c1_rec.l_start_date_active;
291 l_category_rec.end_date_active := c1_rec.l_end_date_active;
292 l_category_rec.disable_date := c1_rec.l_end_date_active;
293
294 fnd_msg_pub.initialize;
295
296 if c1_rec.update_flag = 1 then
297 l_category_rec.category_id := c1_rec.category_id;
298
299 FND_FILE.PUT_LINE(FND_FILE.LOG,'Selected node for update: '||c1_rec.l_segment1);
300 INV_ITEM_CATEGORY_PUB.Update_Category(
301 p_api_version=>1,
302 x_return_status =>l_return_status,
303 x_errorcode=> l_errorcode ,
304 x_msg_count=> l_msg_count,
305 x_msg_data=> l_msg_data,
306 p_category_rec =>l_category_rec
307 );
308 else
309
310 FND_FILE.PUT_LINE(FND_FILE.LOG,'Selected node for insert: '|| c1_rec.l_segment1);
311 INV_ITEM_CATEGORY_PUB.Create_Category (p_api_version=>1,
312 x_return_status =>l_return_status,
313 x_errorcode=> l_errorcode ,
314 x_msg_count=> l_msg_count,
315 x_msg_data=> l_msg_data,
316 p_category_rec =>l_category_rec,
317 x_category_id =>l_category_id);
318 end if;
319
320 if l_msg_count > 0 then
321 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST,p_encoded=>FND_API.G_FALSE, p_msg_index_out=>l_msg_index_out, p_data=>l_data);
322 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in insert/update of node' );
323 FND_FILE.PUT_LINE(FND_FILE.LOG,substrb(l_data,1,1000));
324 FND_FILE.PUT_LINE(FND_FILE.LOG,substrb(l_data,1001,1000));
325 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' );
326
327 g_warn:=true;
328 else
329 FND_FILE.PUT_LINE(FND_FILE.LOG,'Node committed to database ' );
330 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' );
331 end if;
332
333 end loop;
334
335 -- If there are no values in the value set then raise exception
336
337 if l_count =0 then
338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No nodes were updated or inserted. This could be because there were no changes in the valueset since the last time it was updated or there are no values defined in the hierarchy');
339 g_count := l_count;
340 end if;
341
342
343 <<end_block>>
344 null;
345
346 exception
347 when others then
348 g_errbuf:= 'Error in Category Population'||sqlerrm;
349 -- dbms_output.put_line('Error in Category Population'||sqlerrm);
350 g_error:=true;
351 raise;
352 end;
353
354 end;
[Home] [Help]