[Home] [Help]
PACKAGE BODY: APPS.ICX_CATG
Source
1 PACKAGE BODY icx_catg AS
2 /* $Header: ICXSTDCB.pls 115.2 99/07/17 03:26:58 porting ship $ */
3
4 ----------------------------------------------------
5 PROCEDURE xls (p_where IN VARCHAR2 )
6 IS
7
8 v_dcd_name VARCHAR2(200) := NULL;
9 v_help_url VARCHAR2(2000) := NULL;
10 v_language_code VARCHAR2(30) := NULL;
11
12 l_where_clause VARCHAR2(2000) := NULL;
13 l_region_code VARCHAR2(50):= NULL;
14 l_total_rows NUMBER := 0;
15 y_table icx_util.char240_table;
16
17 l_field_sep VARCHAR2(10) := icx_store_batch_utils.g_field_sep;
18 l_line_sep VARCHAR2(10) := icx_store_batch_utils.g_line_sep;
19
20 l_line LONG := NULL;
21
22 l_date_format VARCHAR2(50) := NULL;
23
24 BEGIN
25
26 IF icx_sec.validateSession THEN
27
28 -- Initialize the error page
29 icx_util.error_page_setup;
30
31 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
32
33 v_dcd_name := owa_util.get_cgi_env('SCRIPT_NAME');
34 v_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
35 v_help_url := '/OA_DOC/' || v_language_code || '/awc' || '/icxitems.htm';
36
37 l_where_clause := icx_call.decrypt2(p_where);
38 l_region_code := 'ICX_CATG_DETLS_LOAD_R';
39
40 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
41 P_PARENT_REGION_CODE => l_region_code,
42 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
43 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
44 P_WHERE_CLAUSE => l_where_clause,
45 P_RETURN_PARENTS => 'T',
46 P_RETURN_CHILDREN => 'F');
47
48 l_total_rows := ak_query_pkg.g_results_table.count;
49
50 IF l_total_rows = 0 THEN
51 -- Print the no data found message.
52 -- Do not spwan the spread sheet
53 -- This will not happen
54 htp.htmlOpen;
55 htp.headOpen;
56 icx_util.copyright;
57 js.scriptOpen;
58 icx_admin_sig.help_win_script(v_help_url, v_language_code);
59 js.scriptClose;
60 htp.title(ak_query_pkg.g_regions_table(0).name);
61 htp.headClose;
62
63 icx_admin_sig.toolbar(language_code => v_language_code,
64 disp_find => 'icx_store_catg_templates.find_categories');
65 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
66 fnd_message.set_token('NAME_OF_REGION_TOKEN',
67 ak_query_pkg.g_regions_table(0).name);
68 htp.br;
69 htp.tableOpen('BORDER=0');
70 htp.tableRowOpen;
71 htp.tableData(cvalue => '<B><FONT size=+1>'||fnd_message.get||'</FONT></B>
72 ',cattributes => 'VALIGN="MIDDLE"');
73 htp.tableClose;
74 htp.br;
75 icx_admin_sig.footer;
76 ELSE
77 -- Set the mime type to spread sheet for automatic spawning
78 owa_util.mime_header('application/msexcel');
79
80 -- Print the first three instruction lines
81 FND_MESSAGE.SET_NAME('ICX','ICX_CATG_XL_LINE1');
82 htp.p(FND_MESSAGE.GET);
83 FND_MESSAGE.SET_NAME('ICX','ICX_CATG_XL_LINE2');
84 FND_MESSAGE.SET_TOKEN('ICX_DATE_FORMAT', l_date_format);
85 htp.p(FND_MESSAGE.GET);
86 FND_MESSAGE.SET_NAME('ICX','ICX_CATG_XL_LINE3');
87 htp.p(FND_MESSAGE.GET);
88 htp.p;
89
90 FOR i IN ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST LOOP
91 IF (ak_query_pkg.g_items_table(i).secured_column = 'F'
92 AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y' )
93 OR (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_CATEGORY_ID') THEN
94
95 l_line := l_line || ak_query_pkg.g_items_table(i).attribute_label_long || l_field_sep;
96
97 END IF; -- IF ak_query_pkg....
98
99 END LOOP;
100
101 -- Print the line string
102 htp.p(l_line);
103
104 -- FOR r IN p_start_row -1..v_end_row - 1 LOOP
105 FOR r IN ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST LOOP
106 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(r),y_table);
107
108 l_line := NULL;
109 FOR i IN ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST LOOP
110 IF (ak_query_pkg.g_items_table(i).secured_column = 'F'
111 AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y')
112 OR ( ak_query_pkg.g_items_table(i).attribute_code = 'ICX_CATEGORY_ID') THEN
113
114 l_line := l_line || y_table(ak_query_pkg.g_items_table(i).value_id)
115 || l_field_sep;
116 END IF; -- IF ... secured_column = 'F'...
117
118 END LOOP; -- FOR i ...
119
120 htp.p(l_line);
121
122 END LOOP; -- FOR r ...
123
124 END IF; -- IF l_totlal_rows = 0
125
126 END IF; -- IF validateSession
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 -- htp.p('Error in xls: ' || substr(SQLERRM, 1, 512));
131 icx_util.add_error(substr(SQLERRM, 12, 512));
132 icx_util.error_page_print;
133 END xls;
134
135 END icx_catg;