[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_UPDATE_SAVED_CARTS
Source
1 PACKAGE BODY ICX_REQ_UPDATE_SAVED_CARTS as
2 /* $Header: ICXRQUPB.pls 115.4 99/07/17 03:23:42 porting ship $ */
3
4 ------------------------------------------------------
5 procedure cartSearch is
6 ------------------------------------------------------
7 c_language varchar2(30);
8 begin
9 c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
10
11
12 -- Check if session is valid
13 if (icx_sec.validatesession('ICX_UPDATE_SAVED_CARTS')) then
14
15
16 icx_on_utilities.FindPage(p_region_appl_id => 601,
17 p_region_code => 'ICX_SAVED_CARTS_R',
18 p_goto_url => 'ICX_REQ_UPDATE_SAVED_CARTS.displaySavedCarts',
19 p_lines_now => 1,
20 p_lines_next => 5,
21 p_new_url => 'ICX_REQ_NAVIGATION.ic_parent?cart_id=' || icx_call.encrypt2('0'),
22 p_help_url => '/OA_DOC/' || c_language || '/awe' || '/icxhlprq.htm'
23 );
24
25 end if;
26 end;
27
28
29 ------------------------------------------------------
30 procedure displaySavedCarts(a_1 in varchar2 default null,
31 c_1 in varchar2 default null,
32 i_1 in varchar2 default null,
33 a_2 in varchar2 default null,
34 c_2 in varchar2 default null,
35 i_2 in varchar2 default null,
36 a_3 in varchar2 default null,
37 c_3 in varchar2 default null,
38 i_3 in varchar2 default null,
39 a_4 in varchar2 default null,
40 c_4 in varchar2 default null,
41 i_4 in varchar2 default null,
42 a_5 in varchar2 default null,
43 c_5 in varchar2 default null,
44 i_5 in varchar2 default null,
45 p_start_row in number default 1,
46 p_end_row in number default null,
47 p_where in varchar2 default null ) is
48 ------------------------------------------------------
49
50 sess_web_user number(15);
51 c_language varchar2(30);
52 c_title varchar2(80);
53 c_prompts icx_util.g_prompts_table;
54 where_clause varchar2(2000);
55 total_rows number;
56 end_row number;
57 c_query_size number;
58 i number := 0;
59 r number := 0;
60 display_text varchar2(240);
61 condensed_params varchar2(20);
62 y_table icx_util.char240_table;
63 l_encrypted_where number;
64 g_reg_ind number;
65 c_message varchar2(2000);
66 err_num number;
67 err_mesg varchar2(240);
68 l_cart_id_value_id number;
69 l_cart_name_value_id number;
70 l_emergency_flag_value_id number;
71 l_emergency varchar2(30);
72
73 /* New vars to use the Bind vars logic **/
74 l_where_binds ak_query_pkg.bind_tab;
75 l_where_clause varchar2(2000);
76 v_index number;
77
78 begin
79
80 if icx_sec.validateSession('ICX_UPDATE_SAVED_CARTS') then
81
82 sess_web_user := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
83 c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
84
85 icx_util.getPrompts(601,'ICX_SAVED_CARTS_R',c_title,c_prompts);
86 icx_util.error_page_setup;
87
88 htp.htmlOpen;
89 htp.headOpen;
90 icx_util.copyright;
91 js.scriptOpen;
92 htp.p('function help_window() {
93 help_win = window.open(''/OA_DOC/' || c_language || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250");
94 help_win = window.open(''/OA_DOC/' || c_language || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250")} ');
95
96 htp.p('function saved_cart_rows(start_num, end_num, param_id) {
97 document.DISPLAY_PACKPROC.p_start_row.value = start_num
98 document.DISPLAY_PACKPROC.p_end_row.value = end_num
99 document.DISPLAY_PACKPROC.parameters_id.value = param_id
100 document.DISPLAY_PACKPROC.submit()
101 }');
102
103
104 -- FND_MESSAGE.SET_TOKEN('OBJECT_TO_DELETE_TOKEN', '???');
105 -- FND_MESSAGE.SET_NAME('ICX', 'ICX_DELETE_OBJECT');
106 htp.p('function delete_saved_cart(delete_name, condensed_params) {
107 //if (confirm("' || FND_MESSAGE.GET || '") ) {
108 //if (confirm("Are you sure you want to delete requisition # "+delete_name+"?")) {
109 if (confirm(delete_name)) {
110 parent.location="ICX_REQ_UPDATE_SAVED_CARTS.deleteSavedCarts?condensed_params=" + condensed_params
111 }
112 }');
113 js.scriptClose;
114 htp.title(c_title);
115 htp.headClose;
116
117 /* get number of rows to display */
118 select QUERY_SET into c_query_size
119 from ICX_PARAMETERS;
120
121 if p_where is not null then
122 where_clause := icx_call.decrypt2(p_where);
123 icx_on_utilities.unpack_whereSegment(where_clause,l_where_clause,l_where_binds);
124 else
125 where_clause := icx_on_utilities.whereSegment(a_1,c_1,i_1,a_2,c_2,i_2,a_3,c_3,i_3,a_4,c_4,i_4,a_5,c_5,i_5);
126
127 icx_on_utilities.unpack_whereSegment(where_clause, l_where_clause, l_where_binds);
128 -- if where_clause is not null then
129 if l_where_clause is not null then
130
131 /* added to take care of Bind vars Bug **/
132
133 -- where_clause := where_clause || ' AND ';
134 l_where_clause := l_where_clause || ' AND ';
135 end if;
136 -- where_clause := where_clause || 'SHOPPER_ID=' || sess_web_user ||
137 -- ' AND ( SAVED_FLAG = ''1'' OR SAVED_FLAG= ''4'')';
138 l_where_clause := l_where_clause || 'SHOPPER_ID= :ICXBIND0 AND ( SAVED_FLAG = ''1'' OR SAVED_FLAG= ''4'')';
139
140 v_index := l_where_binds.COUNT;
141 l_where_binds(v_index).name := 'ICXBIND0';
142 l_where_binds(v_index).value := sess_web_user;
143
144 where_clause := l_where_clause;
145
146 if l_where_binds.count > 0 then
147
148 for i in l_where_binds.first .. l_where_binds.last LOOP
149 where_clause := where_clause || '*' || l_where_binds(i).value;
150 end loop;
151
152 end if;
153
154 where_clause := where_clause || '**]';
155
156 l_encrypted_where := icx_call.encrypt2(where_clause);
157
158 end if;
159
160
161 /* old code - 1/21/97 */
162 -- if (where_clause is not null ) then
163 -- where_clause := where_clause || ' AND ';
164 -- end if;
165 -- where_clause := where_clause || 'SHOPPER_ID=' || sess_web_user;
166
167 -- Only display save (1) and errored carts (4).
168 -- where_clause := where_clause || ' AND ( SAVED_FLAG=''1'' OR SAVED_FLAG=''4'') ';
169 /* old code - 1/21/97 */
170
171
172 htp.comment(where_clause);
173
174 -- set up end rows to display
175 if p_end_row is null then
176 end_row := c_query_size;
177 else
178 end_row := p_end_row;
179 end if;
180
181 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
182 P_PARENT_REGION_CODE => 'ICX_SAVED_CARTS_R',
183 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
184 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
185 -- P_WHERE_CLAUSE => where_clause,
186 P_WHERE_CLAUSE => l_where_clause,
187 P_RETURN_PARENTS => 'T',
188 P_RETURN_CHILDREN => 'F',
189 P_RANGE_LOW => p_start_row,
190 P_RANGE_HIGH => end_row,
191 P_WHERE_BINDS => l_where_binds);
192
193
194 /* get number of total rows returned by lov to be used to determine if
195 we need to display the next/previous buttons */
196
197 g_reg_ind := ak_query_pkg.g_regions_table.FIRST;
198 total_rows := ak_query_pkg.g_regions_table(g_reg_ind).total_result_count;
199 if end_row > total_rows then
200 end_row := total_rows;
201 end if;
202
203 icx_admin_sig.toolbar(language_code => c_language,
204 disp_find => 'icx_req_update_saved_carts.cartSearch');
205 htp.formOpen('ICX_REQ_UPDATE_SAVED_CARTS.displaySavedCarts','POST','','','NAME="DISPLAY_SAVED_CARTS"');
206
207
208 i := 0;
209
210 if ak_query_pkg.g_results_table.COUNT > 0 then
211 htp.tableOpen('BORDER=0');
212 htp.tableRowOpen;
213 htp.tableData('<H2>'||c_title||'</H2>');
214 htp.tableRowClose;
215 htp.tableClose;
216 if p_start_row <> 1 or end_row <> total_rows
217 then
218 icx_on_utilities2.displaySetIcons(c_language,'icx_req_update_saved_carts.displaySavedCarts',p_start_row,end_row,l_encrypted_where,c_query_size,total_rows);
219 end if;
220 end if;
221
222
223 if ak_query_pkg.g_results_table.COUNT = 0 then
224 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
225 fnd_message.set_token('NAME_OF_REGION_TOKEN',c_title);
226 htp.p('<H3>'||fnd_message.get||'</H3>');
227 else
228 htp.tableOpen('BORDER=4');
229 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER')||'">');
230 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
231 if ak_query_pkg.g_items_table(i).secured_column = 'F'
232 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
233 then
234 htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long);
235 end if;
236
237 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_CART_ID' then
238 l_cart_id_value_id := ak_query_pkg.g_items_table(i).value_id;
239 end if;
240 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_EMERGENCY_FLAG' then
241 l_emergency_flag_value_id := ak_query_pkg.g_items_table(i).value_id;
242 end if;
243 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_REQ_NUMBER_SEG1' then
244 l_cart_name_value_id := ak_query_pkg.g_items_table(i).value_id;
245 end if;
246
247 end loop;
248 htp.tableData('');
249 htp.tableRowClose;
250 for r in ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST loop
251 -- if r >= p_start_row-1 and r <= end_row-1
252 -- then
253 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(r),y_table);
254 htp.tableRowOpen;
255 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
256 if ak_query_pkg.g_items_table(i).secured_column = 'F'
257 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
258 then
259 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_REQ_NUMBER_SEG1' then
260
261 if (y_table(l_emergency_flag_value_id) = 'N') OR
262 (y_table(l_emergency_flag_value_id) IS NULL) then
263 l_emergency := 'NO';
264 else
265 l_emergency := 'YES';
266 end if;
267
268 htp.tableData(htf.anchor('icx_req_navigation.ic_parent?cart_id='|| icx_call.encrypt2(y_table(l_cart_id_value_id)) ||'&'||'emergency='||icx_call.encrypt2(l_emergency),
269 y_table(ak_query_pkg.g_items_table(i).value_id),'','onMouseOver="return true"'));
270
271 --Display status falg in plain ENGLISH
272 elsif (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SAVED_FLAG') then
273 if ( y_table(ak_query_pkg.g_items_table(i).value_id) = '4' ) then
274
275 -- display_text := 'Error';
276 FND_MESSAGE.SET_NAME('FND','AFDICT_ERROR_TITLE');
277 display_text := FND_MESSAGE.GET;
278
279 elsif ( y_table(ak_query_pkg.g_items_table(i).value_id) = '1' ) then
280
281 FND_MESSAGE.SET_NAME('ICX','ICX_SAVED');
282 display_text := FND_MESSAGE.GET;
283 else
284
285 FND_MESSAGE.SET_NAME('ICX','ICX_UNKNOWN');
286 display_text := FND_MESSAGE.GET;
287 end if;
288 htp.tableData(display_text);
289
290 --Display delete button
291 elsif (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_DELETE') then
292 condensed_params := icx_call.encrypt2( y_table(l_cart_id_value_id) ||'*'|| p_start_row||'*'||end_row||'*'||l_encrypted_where||'**]');
293
294
295
296
297
298 FND_MESSAGE.SET_NAME('ICX', 'ICX_DELETE_OBJECT');
299 FND_MESSAGE.SET_TOKEN('OBJECT_TO_DELETE_TOKEN', y_table(l_cart_name_value_id));
300
301
302 htp.tableData(htf.anchor('javascript:delete_saved_cart('''||icx_util.replace_quotes(FND_MESSAGE.GET) ||''','''||condensed_params||''')',
303 htf.img('/OA_MEDIA/'||c_language||'/FNDIDELR.gif','CENTER','','','border=no width=17 height=16'),'','onMouseOver="return true"'));
304
305 else
306 if ak_query_pkg.g_items_table(i).value_id is null then
307 htp.tableData('');
308 else
309 if (y_table(ak_query_pkg.g_items_table(i).value_id) is not null) then
310 htp.tableData(y_table(ak_query_pkg.g_items_table(i).value_id));
311 else
312 htp.tableData(htf.br);
313 end if;
314 end if;
315 end if;
316 end if;
317 end loop; -- items
318
319 htp.tableRowClose;
320 -- end if; -- < p_start_row -1
321 end loop; -- Results
322 htp.tableClose;
323 end if;
324
325 -- if p_start_row <> 1 or end_row <> total_rows
326 -- then
327 icx_on_utilities2.displaySetIcons(c_language,'icx_req_update_saved_carts.displaySavedCarts',p_start_row,end_row,l_encrypted_where,c_query_size,total_rows,FALSE);
328 -- end if;
329 htp.formClose;
330 icx_admin_sig.footer;
331 htp.htmlClose;
332
333 end if; -- validate session
334
335 exception
336 when others then
337 err_num := SQLCODE;
338 c_message := SQLERRM;
339 select substr(c_message,12,512) into err_mesg from dual;
340 icx_util.add_error(err_mesg);
341 icx_admin_sig.error_screen(err_mesg);
342 end displaySavedCarts;
343
344
345 ------------------------------------------------------
346 procedure deleteSavedCarts(condensed_params in number default null) is
347 ------------------------------------------------------
348 v_cart_id number;
349 Y varchar2(2000);
350 params icx_on_utilities.v80_table;
351
352 Begin
353
354 if icx_sec.validateSession('ICX_UPDATE_SAVED_CARTS') then
355
356 Y := icx_call.decrypt2(condensed_params);
357 icx_on_utilities.unpack_parameters(Y, params);
358
359 v_cart_id := params(1);
360
361 /* Delete all cart lines */
362 delete from icx_shopping_cart_lines
363 Where cart_id = v_cart_id;
364
365 /* Delete cart header */
366 delete from icx_shopping_carts
367 where cart_id = v_cart_id;
368
369 delete icx_cart_line_distributions
370 where cart_id = v_cart_id;
371
372 delete icx_cart_distributions
373 where cart_id = v_cart_id;
374
375 ICX_REQ_UPDATE_SAVED_CARTS.displaySavedCarts(p_start_row => params(2),
376 p_end_row => params(3),
377 p_where => params(4));
378 end if;
379 End;
380
381
382 end ICX_REQ_UPDATE_SAVED_CARTS;