[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT
Source
1 package body icx_cat as
2 /* $Header: ICXCATHB.pls 115.3 99/07/17 03:15:41 porting ship $ */
3
4
5
6 procedure main is
7
8 l_title varchar2(80);
9 l_prompts icx_util.g_prompts_table;
10
11 begin
12 if icx_sec.validateSession then
13 icx_util.getPrompts(601,'ICX_RELATED_CATEGORIES_R',l_title,l_prompts);
14
15 htp.htmlOpen;
16 htp.headOpen;
17 icx_util.copyright;
18 htp.title(l_title);
19 htp.headClose;
20
21 htp.p('<FRAMESET rows="285,*">
22 <FRAME name="header" src="ICX_CAT.cat_head">
23 <FRAME name="tail" src="ICX_CAT.cat_tail">
24 </FRAMESET>');
25
26
27 htp.p('<NOFRAMESET>');
28 FND_MESSAGE.SET_NAME('ICX','ICX_BROWSER');
29 htp.p(FND_MESSAGE.Get);
30 htp.p('</NOFRAMESET>');
31 htp.p('in Main');
32 htp.htmlClose;
33 end if; -- validateSession
34
35 end main;
36
37
38
39
40 procedure cat_head(p_category_set_id in varchar2 default null,
41 p_category_id in varchar2 default null,
42 p_category in varchar2 default null,
43 p_query_flag in varchar2 default 'F') is
44
45 cursor category_sets is
46 select category_set_id,
47 category_set_name
48 from icx_category_set_lov;
49
50 cursor relations is
51 select lookup_code,
52 meaning
53 from fnd_lookups
54 where lookup_type = 'ICX_RELATIONS'
55 and enabled_flag = 'Y';
56
57 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
58 l_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
59 l_language varchar2(30);
60 i number;
61 l_title varchar2(80);
62 l_prompts icx_util.g_prompts_table;
63 lov_title varchar2(80);
64 lov_prompts icx_util.g_prompts_table;
65 err_num number;
66 err_mesg varchar2(512);
67 temp_text varchar2(2000);
68 temp_cat_set_id number;
69 temp_cat_set varchar2(30);
70 temp_relation_code varchar2(30);
71 temp_relation varchar2(80);
72
73 begin
74 if icx_sec.validateSession then
75 icx_util.error_page_setup;
76 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
77 icx_util.getPrompts(601,'ICX_RELATED_CATEGORIES_R',l_title,l_prompts);
78 icx_util.getPrompts(178,'ICX_LOV',lov_title,lov_prompts);
79
80 htp.htmlOpen;
81 htp.headOpen;
82 icx_util.copyright;
83
84 htp.title(l_title);
85
86 js.scriptOpen;
87 icx_admin_sig.help_win_script('/OA_HTML/'||l_language||'/ICXHLMCH.htm');
88 icx_util.LOVScript;
89 js.null_alert;
90 js.equal_alert;
91
92 htp.p('function autoquery() {
93 parent.tail.document.Tail.p_category_set_id.value = document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
94 parent.tail.document.Tail.p_category_id.value = document.Category.ICX_CATEGORY_ID.value
95 parent.tail.document.Tail.p_category_name.value = document.Category.ICX_CATEGORY.value
96 parent.tail.document.Tail.submit()
97 }');
98
99 htp.p('function set_changed() {
100 document.Category.ICX_CATEGORY_ID.value = ""
101 document.Category.ICX_CATEGORY.value = ""
102 document.Category.ICX_RELATED_CATEGORY_ID.value = ""
103 document.Category.ICX_RELATED_CATEGORY.value = ""
104 parent.tail.document.Tail.p_category_set_id.value = document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
105 parent.tail.document.Tail.p_category_id.value = ""
106 parent.tail.document.Tail.p_category_name.value = ""
107 parent.tail.document.Tail.submit()
108 }');
109
110 htp.p('function cat_changed() {
111 document.Category.ICX_CATEGORY_ID.value = ""
112 parent.tail.document.Tail.p_category_set_id.value = document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
113 parent.tail.document.Tail.p_category_id.value = ""
114 parent.tail.document.Tail.p_category_name.value = document.Category.ICX_CATEGORY.value
115 parent.tail.document.Tail.submit()
116 }');
117
118 htp.p('function pre_cat_LOV() {
119 var l_where = "CATEGORY_SET_ID=" + document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
120 LOV(''178'',''ICX_CATEGORY'',''601'',''ICX_RELATED_CATEGORIES_R'',''Category'',''header'','''',l_where)
121 }');
122
123 htp.p('function post_cat_LOV(cat_id, cat_name) {
124 parent.tail.document.Tail.p_category_set_id.value = document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
125 parent.tail.document.Tail.p_category_id.value = cat_id
126 parent.tail.document.Tail.p_category_name.value = cat_name
127 parent.tail.document.Tail.submit()
128 }');
129
130 FND_MESSAGE.SET_NAME('ICX','ICX_CAT_BEFORE');
131 htp.p('function pre_rel_cat_LOV() {
132 if (!null_alert(document.Category.ICX_CATEGORY.value,"'||icx_util.replace_quotes(FND_MESSAGE.Get)||'")) {
133 var l_where = "CATEGORY_SET_ID=" + document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value
134 LOV(''178'',''ICX_RELATED_CATEGORY'',''601'',''ICX_RELATED_CATEGORIES_R'',''Category'',''header'','''',l_where)
135 }
136 }');
137 -- remove manual where clause for now because html can not deal
138 -- with the spaces in the category name
139 -- var l_where = "CATEGORY_SET_ID=" + document.Category.ICX_CATEGORY_SET_ID.options[document.Category.ICX_CATEGORY_SET_ID.selectedIndex].value + "^@~^and^@~^CONCATENATED_SEGMENTS<>''" + document.Category.ICX_CATEGORY.value + "''"
140
141
142 FND_MESSAGE.SET_NAME('ICX','ICX_NOT_NULL');
143 htp.p('function cat_submit() {
144 if (!null_alert(document.Category.ICX_CATEGORY.value,"'||icx_util.replace_quotes(l_prompts(4))||' '||icx_util.replace_quotes(FND_MESSAGE.Get)||'")) {
145 if (document.Category.ICX_RELATION.options[document.Category.ICX_RELATION.selectedIndex].value != "TOP") {');
146 FND_MESSAGE.SET_NAME('ICX','ICX_NOT_NULL');
147 htp.p(' if (!null_alert(document.Category.ICX_RELATED_CATEGORY.value,"'||icx_util.replace_quotes(l_prompts(7))||' '||icx_util.replace_quotes(FND_MESSAGE.Get)||'")) {');
148 FND_MESSAGE.SET_NAME('ICX','ICX_CAT_PARENT');
149 htp.p(' if (!equal_alert(document.Category.ICX_CATEGORY.value,document.Category.ICX_RELATED_CATEGORY.value,"'||icx_util.replace_quotes(FND_MESSAGE.Get)||'")) {
150 document.Category.submit()
151 }
152 }
153 } else {
154 document.Category.submit()
155 }
156 }
157 }');
158
159 js.scriptClose;
160 htp.headClose;
161 htp.bodyOpen(icx_admin_sig.background);
162 icx_admin_sig.toolbar(language_code => l_language);
163
164 htp.formOpen(l_agent||'/icx_cat.cat_insert','POST','','','NAME="Category"');
165
166 htp.tableOpen;
167 htp.tableRowOpen;
168 htp.tableData('<H2>'||l_title||'</H2>');
169 htp.tableRowClose;
170 htp.tableClose;
171
172 htp.tableOpen;
173
174 -- Category Set poplist
175 htp.tableRowOpen;
176 htp.tableData(l_prompts(2),'RIGHT');
177 htp.p('<TD>'||htf.formSelectOpen('ICX_CATEGORY_SET_ID','','','onchange="set_changed()"'));
178 open category_sets;
179 loop
180 fetch category_sets into temp_cat_set_id, temp_cat_set;
181 exit when category_sets%NOTFOUND;
182 if temp_cat_set_id = p_category_set_id then
183 htp.formSelectOption(temp_cat_set,'SELECTED','VALUE="'||temp_cat_set_id||'"');
184 else
185 htp.formSelectOption(temp_cat_set,'','VALUE="'||temp_cat_set_id||'"');
186 end if;
187 end loop;
188 close category_sets;
189 htp.p(htf.formSelectClose||'</TD>');
190
191 -- Category text field
192 htp.formHidden('ICX_CATEGORY_ID',p_category_id);
193 htp.tableData(l_prompts(4),'RIGHT');
194 htp.tableData(htf.formText('ICX_CATEGORY',30,81,p_category,'onchange="cat_changed()"'));
195 htp.tableData(htf.anchor('javascript:pre_cat_LOV()',htf.img('/OA_MEDIA/'||l_language||'/FNDILOV.gif','CENTER',icx_util.replace_alt_quotes(lov_title),'','BORDER=0 WIDTH=23 HEIGHT=21'),'','onMouseOver="window.status='''||
196 icx_util.replace_onMouseOver_quotes(lov_title)||''';return true"'));
197 htp.tableRowClose;
198
199 -- Relation poplist
200 htp.tableRowOpen;
201 htp.tableData(l_prompts(5),'RIGHT');
202 htp.p('<TD>'||htf.formSelectOpen('ICX_RELATION'));
203 open relations;
204 loop
205 fetch relations into temp_relation_code, temp_relation;
206 exit when relations%NOTFOUND;
207 htp.formSelectOption(temp_relation,'','VALUE="'||temp_relation_code||'"');
208 end loop;
209 close relations;
210 htp.p(htf.formSelectClose||'</TD>');
211
212 -- Related Category text field
213 htp.formHidden('ICX_RELATED_CATEGORY_ID');
214 htp.tableData(l_prompts(7),'RIGHT');
215 htp.tableData(htf.formText('ICX_RELATED_CATEGORY',30,81,'','onchange="document.Category.ICX_RELATED_CATEGORY_ID.value = ''''"'));
216 htp.tableData(htf.anchor('javascript:pre_rel_cat_LOV()',htf.img('/OA_MEDIA/'||l_language||
217 '/FNDILOV.gif','CENTER',icx_util.replace_alt_quotes(lov_title),'','BORDER=0 WIDTH=23 HEIGHT=21'),'','onMouseOver="window.status='''||icx_util.replace_onMouseOver_quotes(lov_title)||''';return true"'));
218 htp.tableRowClose;
219 htp.tableClose;
220
221
222 -- Write submit and clear buttons
223 htp.tableOpen;
224 htp.tableRowOpen;
225 icx_util.DynamicButton(l_prompts(8),'FNDBSBMT.gif',l_prompts(8),'javascript:cat_submit()',l_language,FALSE);
226 if (instr(c_browser, 'MSIE') = 0) then
227 icx_util.DynamicButton(l_prompts(9),'FNDBCLR.gif',l_prompts(9),'javascript:document.Category.reset()',l_language,FALSE);
228 end if;
229 htp.tableRowClose;
230 htp.tableClose;
231
232
233 -- Query relationships if screen is being repainted after a commit
234 if p_query_flag = 'T' then
235 htp.p('<SCRIPT LANGUAGE="JavaScript">');
236 htp.p('autoquery()');
237 htp.p('</SCRIPT>');
238 end if;
239
240
241 htp.formClose;
242 icx_sig.footer;
243 htp.bodyClose;
244 htp.htmlClose;
245
246 end if; -- ValidateSession
247
248 exception
249 when others then
250 err_num := SQLCODE;
251 temp_text := SQLERRM;
252 select substr(temp_text,12,512) into err_mesg from dual;
253 icx_util.add_error(err_mesg);
254 icx_admin_sig.error_screen(l_title,l_language);
255
256 end; -- cat_head
257
258
259
260
261 procedure cat_tail(p_category_set_id in varchar2 default null,
262 p_category_id in varchar2 default null,
263 p_category_name in varchar2 default null,
264 p_start_row in number default 1,
265 p_end_row in number default null) is
266
267 l_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
268 l_responsibility_id number := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
269 l_user_id number := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
270 l_title varchar2(80);
271 l_prompts icx_util.g_prompts_table;
272 err_num number;
273 err_mesg varchar2(512);
274 temp_text varchar2(2000);
275 l_language varchar2(30);
276 l_result_row_table icx_util.char240_table;
277 l_total_rows number;
278 l_end_row number;
279 l_query_size number;
280 l_where varchar2(2000);
281 l_order_by varchar2(2000);
282 l_count number;
283 j number;
284 temp_cat_set_id number;
285 temp_cat_id number;
286 temp_related_cat_id number;
287 temp_cat varchar2(240);
288 temp_related_cat varchar2(240);
289 temp_relation_item number;
290
291 begin
292 if icx_sec.validateSession then
293 icx_util.error_page_setup;
294 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
295 icx_util.getPrompts(601,'ICX_RELATED_CATEGORIES_DISP_R',l_title,l_prompts);
296
297 htp.htmlOpen;
298 htp.headOpen;
299 icx_util.copyright;
300 js.scriptOpen;
301
302 -- Javascript function to handle CD buttons
303 htp.p('function rows(start_num, end_num) {
304 document.Tail.p_start_row.value = start_num
305 document.Tail.p_end_row.value = end_num
306 document.Tail.submit()
307 }');
308
309 -- javascript function to confirm delete of relationship
310 FND_MESSAGE.Set_name('ICX','ICX_DELETE');
311 htp.p('function delete_relation(cat, related_cat, cat_set_id, cat_id, related_cat_id) {
312 if (confirm("'||icx_util.replace_quotes(FND_MESSAGE.Get)||': " + cat + " - " + related_cat)) {
313 open('''||l_agent||'/ICX_CAT.cat_delete?icx_category_set_id='' + cat_set_id + ''&icx_category_id='' + cat_id + ''&icx_related_category_id='' + related_cat_id,''tail'')
314 }
315 }');
316
317 js.scriptClose;
318 htp.headClose;
319
320 htp.bodyOpen(icx_admin_sig.background);
321
322 htp.formOpen(l_agent||'/icx_cat.cat_tail','POST','','','NAME="Tail"');
323
324 htp.formHidden('p_category_set_id',p_category_set_id);
325 htp.formHidden('p_category_id',p_category_id);
326 htp.formHidden('p_category_name',p_category_name);
327 htp.formHidden('p_start_row',p_start_row);
328 htp.formHidden('p_end_row',p_end_row);
329
330
331 -- if p_category_id is null then check that p_category_name is valid
332 if p_category_id is null then
333
334 select count(*) into l_count
335 from mtl_categories_kfv mck,
336 mtl_category_sets mcs
337 where (mcs.validate_flag = 'Y' and
338 mck.category_id in (
339 select mcsv.category_id
340 from mtl_category_set_valid_cats mcsv
341 where mcsv.category_set_id = p_category_set_id) and
342 mck.concatenated_segments = p_category_name)
343 or (mcs.validate_flag <> 'Y' and
344 mcs.structure_id = mck.structure_id and
345 mck.concatenated_segments = p_category_name);
346
347 else
348
349 l_count := 1;
350
351 end if;
352
353
354 -- if p_category_id is not null or p_category_name is valid then
355 -- perform object navigator query, otherwise display error message
356
357 if l_count <> 0 then
358
359 -- Construct where clause
360 if p_category_id is not null then
361 l_where := 'CATEGORY_SET_ID = '||p_category_set_id||' and CATEGORY_ID = '||p_category_id;
362 else
363 l_where := 'CATEGORY_SET_ID = '||p_category_set_id||' and CATEGORY_NAME = '''||p_category_name||'''';
364 end if;
365
366 -- Construct orderby clause
367 l_order_by := 'RELATIONSHIP_TYPE DESC, RELATED_CATEGORY_NAME ASC';
368
369 -- Look up the number of rows to display
370 select QUERY_SET into l_query_size
371 from ICX_PARAMETERS;
372
373 -- figure end row value to display
374 if p_end_row is null then
375 l_end_row := l_query_size;
376 else
377 l_end_row := p_end_row;
378 end if;
379
380 -- Call to Object Navigator to execute query and return data
381 -- as well as object and region structures
382
383 ak_query_pkg.exec_query (
384 P_PARENT_REGION_APPL_ID => 601 ,
385 P_PARENT_REGION_CODE => 'ICX_RELATED_CATEGORIES_DISP_R',
386 P_ORDER_BY_CLAUSE => l_order_by ,
387 P_WHERE_CLAUSE => l_where ,
388 P_RESPONSIBILITY_ID => l_responsibility_id ,
389 P_USER_ID => l_user_id ,
390 P_RETURN_PARENTS => 'T' ,
391 P_RETURN_CHILDREN => 'F' ,
392 P_RANGE_LOW => p_start_row,
393 P_RANGE_HIGH => l_end_row);
394
395
396
397 -- get number of total rows returned by lov to be used to
398 -- determine if we need to display the next/previous buttons
399 l_total_rows := ak_query_pkg.g_regions_table(0).total_result_count;
400
401
402 -- check end row value
403 if l_end_row > l_total_rows then
404 l_end_row := l_total_rows;
405 end if;
406
407
408 -- display data and CD buttons if necessary
409 j := 0;
410 for i in 1..ak_query_pkg.g_results_table.COUNT loop
411 j := j + 1;
412
413 -- If this is the first iteration of the loop then
414 -- display next/previous set buttons if list of values returns
415 -- more than the standard query size and also display
416 -- the table header
417 if j = 1 then
418 if (l_total_rows > l_query_size) and not
419 (p_start_row = 1 and l_end_row = l_total_rows) then
420 icx_on_utilities2.displaySetIcons (
421 P_LANGUAGE_CODE => l_language,
422 P_PACKPROC => 'JS',
423 P_START_ROW => p_start_row,
424 P_STOP_ROW => l_end_row,
425 P_ENCRYPTED_WHERE => '1',
426 P_QUERY_SET => l_query_size,
427 P_ROW_COUNT => l_total_rows,
428 P_JSPROC => 'rows');
429
430 end if; -- CD Buttons
431
432
433 -- display table header
434 htp.tableOpen('BORDER=1');
435 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER')||'">');
436 for k in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
437 if ak_query_pkg.g_items_table(k).secured_column = 'F' and
438 ak_query_pkg.g_items_table(k).node_display_flag = 'Y' then
439 htp.tableData(htf.strong(ak_query_pkg.g_items_table(k).attribute_label_long),'LEFT');
440 end if;
441 end loop;
442 htp.tableRowClose;
443
444 end if; -- CD Buttons and table header
445
446
447 -- load data for current row into temp pl/sql table
448 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(i-1), l_result_row_table);
449
450
451 -- display one row of data
452 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW')||'">');
453 for k in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
454 if ak_query_pkg.g_items_table(k).secured_column = 'F' and
455 ak_query_pkg.g_items_table(k).node_display_flag = 'Y' then
456
457 if (ak_query_pkg.g_items_table(k).attribute_code = 'ICX_RELATED_CATEGORY' or
458 ak_query_pkg.g_items_table(k).attribute_code = 'ICX_RELATED_CATEGORY_DESC') then
459 for x in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
460 if ak_query_pkg.g_items_table(x).attribute_code = 'ICX_RELATION' then
461 temp_relation_item := x;
462 end if;
463 end loop;
464 -- dont display related category if relationship is TOP
465 if l_result_row_table(ak_query_pkg.g_items_table(temp_relation_item).value_id) = 'TOP' then
466 htp.tableData('<BR>');
467 else
468 htp.p(icx_on_utilities.formatData(icx_on_utilities.formatText(l_result_row_table(ak_query_pkg.g_items_table(k).value_id),
469 ak_query_pkg.g_items_table(k).bold,ak_query_pkg.g_items_table(k).italic),ak_query_pkg.g_items_table(k).horizontal_alignment,ak_query_pkg.g_items_table(k).vertical_alignment));
470 end if;
471 else
472 htp.p(icx_on_utilities.formatData(icx_on_utilities.formatText(l_result_row_table(ak_query_pkg.g_items_table(k).value_id),
473 ak_query_pkg.g_items_table(k).bold,ak_query_pkg.g_items_table(k).italic),ak_query_pkg.g_items_table(k).horizontal_alignment,ak_query_pkg.g_items_table(k).vertical_alignment));
474 end if;
475 end if;
476
477 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_CATEGORY' then
478 temp_cat := l_result_row_table(ak_query_pkg.g_items_table(k).value_id);
479 end if;
480
481 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_RELATED_CATEGORY' then
482 temp_related_cat := l_result_row_table(ak_query_pkg.g_items_table(k).value_id);
483 end if;
484
485 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_CATEGORY_SET_ID' then
486 temp_cat_set_id := l_result_row_table(ak_query_pkg.g_items_table(k).value_id);
487 end if;
488
489 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_CATEGORY_ID' then
490 temp_cat_id := l_result_row_table(ak_query_pkg.g_items_table(k).value_id);
491 end if;
492
493 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_RELATED_CATEGORY_ID' then
494 temp_related_cat_id := l_result_row_table(ak_query_pkg.g_items_table(k).value_id);
495 end if;
496
497 end loop;
498 htp.tableData(htf.anchor('javascript:delete_relation('''||icx_util.replace_quotes(temp_cat)||''','''||icx_util.replace_quotes(temp_related_cat)||''','''||temp_cat_set_id||''','''||temp_cat_id||''','''||temp_related_cat_id||
499 ''')',htf.img('/OA_MEDIA/'||l_language||'/FNDIDELR.gif','CENTER',icx_util.replace_alt_quotes(l_prompts(6)),'','BORDER=0 WIDTH=16 HEIGHT=17'),'','onMouseOver="window.status='''||icx_util.replace_onMouseOver_quotes(l_prompts(6))||
500 ''';return true"'));
501
502 htp.tableRowClose;
503
504 end loop; -- Display data
505
506 htp.tableClose;
507
508
509 -- print button set if appropriate
510 if (l_total_rows > l_query_size) and not
511 (p_start_row = 1 and l_end_row = l_total_rows) then
512 icx_on_utilities2.displaySetIcons (
513 P_LANGUAGE_CODE => l_language,
514 P_PACKPROC => 'JS',
515 P_START_ROW => p_start_row,
516 P_STOP_ROW => l_end_row,
517 P_ENCRYPTED_WHERE => '1',
518 P_QUERY_SET => l_query_size,
519 P_ROW_COUNT => l_total_rows,
520 P_JSPROC => 'rows');
521
522 end if;
523
524
525 -- display message if no rows were returned by query
526 if j = 0 then
527 fnd_message.set_name('ICX','ICX_CAT_NO_RELATION');
528 fnd_message.set_token('CATEGORY',p_category_name);
529 htp.p('<H3>'||fnd_message.get||'</H3>');
530 end if;
531
532 else
533
534 if p_category_id is not null or p_category_name is not null then
535 -- display message that category name is not valid
536 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
537 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',p_category_name);
538 htp.p('<H3>'||FND_MESSAGE.Get||'<H3>');
539 end if;
540
541 end if; -- l_count = 1
542
543
544 htp.formClose;
545 htp.bodyClose;
546 htp.htmlClose;
547
548 end if; -- ValidateSession
549
550 exception
551 when others then
552 err_num := SQLCODE;
553 temp_text := SQLERRM;
554 select substr(temp_text,12,512) into err_mesg from dual;
555 icx_util.add_error(err_mesg);
556 icx_admin_sig.error_screen(l_title,l_language);
557
558 end cat_tail;
559
560
561
562 procedure cat_insert(icx_category_set_id in varchar2 default null,
563 icx_category_id in varchar2 default null,
564 icx_category in varchar2 default null,
565 icx_relation in varchar2 default null,
566 icx_related_category_id in varchar2 default null,
567 icx_related_category in varchar2 default null) is
568
569 l_return_status varchar2(1) := 'S';
570 l_msg_count number;
571 l_msg_data varchar2(240);
572 l_user_id number := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
573
574
575 begin
576 if icx_sec.validateSession then
577 icx_util.error_page_setup;
578
579 -- insert top relation for this category if needed
580 if icx_relation = 'TOP' then
581
582 ICX_Related_Categories_PUB.Insert_Relation
583 ( p_api_version_number => 1.0 ,
584 p_init_msg_list => FND_API.G_TRUE ,
585 p_simulate => FND_API.G_FALSE ,
586 p_commit => FND_API.G_TRUE ,
587 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
588 p_return_status => l_return_status ,
589 p_msg_count => l_msg_count ,
590 p_msg_data => l_msg_data ,
591 p_category_set_id => icx_category_set_id ,
592 p_category_id => icx_category_id ,
593 p_category => icx_category ,
594 p_related_category_id => icx_category_id ,
595 p_related_category => icx_category ,
596 p_relationship_type => 'TOP' ,
597 p_created_by => l_user_id
598 );
599
600 end if;
601
602
603 -- insert child relation for this category if needed
604 if l_return_status = 'S' and icx_related_category is not null then
605 ICX_Related_Categories_PUB.Insert_Relation
606 ( p_api_version_number => 1.0 ,
607 p_init_msg_list => FND_API.G_FALSE ,
608 p_simulate => FND_API.G_FALSE ,
609 p_commit => FND_API.G_TRUE ,
610 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
611 p_return_status => l_return_status ,
612 p_msg_count => l_msg_count ,
613 p_msg_data => l_msg_data ,
614 p_category_set_id => icx_category_set_id ,
615 p_category_id => icx_category_id ,
616 p_category => icx_category ,
617 p_related_category_id => icx_related_category_id ,
618 p_related_category => icx_related_category ,
619 p_relationship_type => 'CHILD' ,
620 p_created_by => l_user_id
621 );
622
623 end if;
624
625
626 -- if API call did not succeed, then print errors with standard error page
627 if l_return_status <> 'S' then
628
629 icx_admin_sig.error_screen(null,null,l_msg_count,l_msg_data);
630
631 else
632
633 -- repaint header
634 icx_cat.cat_head(icx_category_set_id, icx_category_id, icx_category, 'T');
635
636 end if;
637
638
639 end if; -- ValidateSession
640
641 end cat_insert;
642
643
644
645
646 procedure cat_delete(icx_category_set_id in varchar2 default null,
647 icx_category_id in varchar2 default null,
648 icx_related_category_id in varchar2 default null) is
649
650 cursor category_name is
651 select CONCATENATED_SEGMENTS
652 from icx_category_lov
653 where category_id = icx_category_id;
654
655 l_return_status varchar2(1) := 'S';
656 l_msg_count number;
657 l_msg_data varchar2(240);
658 l_user_id number := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
659 l_category varchar2(245);
660
661
662 begin
663 if icx_sec.validateSession then
664 icx_util.error_page_setup;
665
666
667 -- delete relation
668
669 ICX_Related_Categories_PUB.Delete_Relation
670 ( p_api_version_number => 1.0 ,
671 p_init_msg_list => FND_API.G_TRUE ,
672 p_simulate => FND_API.G_FALSE ,
673 p_commit => FND_API.G_TRUE ,
674 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
675 p_return_status => l_return_status ,
676 p_msg_count => l_msg_count ,
677 p_msg_data => l_msg_data ,
678 p_category_set_id => icx_category_set_id ,
679 p_category_id => icx_category_id ,
680 p_related_category_id => icx_related_category_id
681 );
682
683
684 -- if API call did not succeed, then print errors with standard error page
685 if l_return_status <> 'S' then
686
687 icx_admin_sig.error_screen(null,null,l_msg_count,l_msg_data);
688
689 else
690
691 -- repaint relationships
692 open category_name;
693 fetch category_name into l_category;
694 close category_name;
695 icx_cat.cat_tail(icx_category_set_id, icx_category_id,l_category);
696
697 end if;
698
699
700 end if; -- ValidateSession
701
702 end cat_delete;
703
704
705
706
707 end icx_cat;