[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_EVENT_INFO_PARAM
Source
4 /*
1 PACKAGE BODY ast_ofl_event_info_param AS
2 /* $Header: astrtipb.pls 115.12 2002/02/06 10:44:44 pkm ship $ */
3
5 Date Remarks
6 ---------- ------------------------------------------------
7 08/12/2000 Date format is changed to US instead of british.
8 */
9
10 TYPE day_table IS
11 TABLE OF VARCHAR2(2000)
12 INDEX BY BINARY_INTEGER;
13
14 TYPE year_table IS
15 TABLE OF VARCHAR2(2000)
16 INDEX BY BINARY_INTEGER;
17
18 g_image_prefix VARCHAR2(250) := '/OA_MEDIA/' || icx_sec.getid(icx_sec.pv_language_code) || '/';
19 l_user_id NUMBER;
20
21
22 v_date_time VARCHAR2(30);
23 l_agent VARCHAR2(200);
24 ctr1 INTEGER := 0;
25 ctr2 INTEGER := 0;
26 -- g_date_format VARCHAR2(20) := as_ofl_util_pkg.get_date_format; -- Changed for 11i AJScott
27 day_data day_table;
28 year_data year_table;
29
30
31 display_event_name VARCHAR2(200);
32
33
34 PROCEDURE header -- This procedure is creates the descriptive header in the parameter form
35 IS
36 BEGIN
37 -- SELECT TO_CHAR(SYSDATE, -- Changed for 11i AJScott
38 -- g_date_format)
39 -- INTO v_date_time
40 -- FROM DUAL
41
42 -- Begin Mod. RAAM 07/12/2000
43 -- Thanh asked to display date in US format instead of British format.
44 --SELECT fnd_date.date_to_chardate(SYSDATE)
45 -- End Mod.
46 SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY')
47 INTO v_date_time
48 FROM DUAL;
49 htp.htmlOpen;
50 htp.headOpen;
51 htp.title('Event Information');
52 htp.headClose;
53 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
54 htp.tableOpen('border="0" ');
55 htp.tableRowOpen(cAlign => 'TOP');
56
57 --htp.tableData(htf.img( cUrl => g_image_prefix || 'oppty.gif'));
58
59 htp.tableData('<FONT size=+1 face="times new roman">' || 'Event Information Report',
60 cNoWrap => 'TRUE');
61 htp.tableData(htf.bold(v_date_time),
62 cAlign => 'right',
63 cColSpan => '110');
64 htp.tableRowClose;
65 htp.tableClose;
66 htp.tableOpen(cAttributes => 'border=0 cellspacing=0 cellpadding=0 width=561');
67
68 htp.tableRowOpen( cvalign => 'top' );
69 htp.tableData( ' ', ccolspan => '2', cattributes => ' height=9');
70 htp.tableData( '<FONT face="Times New Roman">' ||htf.bold( 'Please specify the criteria and select OK. ') ||
71 '</FONT>', calign => 'center', crowspan => '2', ccolspan => '110', cattributes => ' width=346');
72 htp.tableData( ' ', ccolspan => '6');
73 htp.Br;
74 htp.tableRowClose;
75 htp.tableClose;
76 htp.bodyClose;
77 htp.headClose;
78 htp.htmlClose;
79 end;
80 -----------------------------------------------------------------------------------------------
81 procedure event_information_paramform is
82
83 -----------------------------------------------------------------------------------------------
84 --
85 -- PROCEDURE: event_reg_detail_paramform
86 --
87 -- DESCRIPTION: This procedure is main body of the parameter form
88 --
89 -----------------------------------------------------------------------------------------------
90
91 -- RN 25/9/00
92 -- CURSOR cur_promotion_code is
93 -- select code, name
94 -- from as_promotions_all
95 -- where type = 'E'
96 -- order by 1;
97
98 day_counter NUMBER := 1;
99 year_counter NUMBER := 1990;
100
101
102
103 BEGIN
104
105 FOR i IN 1..31 LOOP
106 if day_counter <= 9 then
107 day_data(i) := '0'||day_counter;
108 day_counter := day_counter +1;
109 else
110 day_data(i) := to_char(day_counter);
111 day_counter := day_counter +1;
112 end if;
113 END LOOP;
114
115 FOR i IN 1..21 LOOP
119
116 year_data(i) := to_char(year_counter);
117 year_counter := year_counter +1;
118 END LOOP;
120
121 if (icx_sec.validateSession) then
122 header;
123
124 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
125 -------------------- Returns login user Id--------------------------
126 htp.FormOpen(owa_util.Get_Owa_Service_Path||'AST_OFL_EVENT_INFO_REPORT.report_wrapper', cattributes => ' NAME="param"');
127 htp.htmlopen;
128 htp.headOpen;
129 htp.title('Event Information Report');
130 htp.p('<SCRIPT language="JavaScript">');
131
132 htp.p('window.name = "bigwindow";');
133 htp.p('function btn_press()');
134 htp.p('{');
135 htp.p('winpbook=window.open(''ast_ofl_event_info_param.popup_window'',''pbook'', ''toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,left=0,top=0,width=650,height=410'');');
136 htp.p('winpbook.focus();');
137 htp.p('}');
138
139 htp.p('window.name = "bigwindow";');
140 htp.p('function btn_press_high()');
141 htp.p('{');
142 htp.p('winpbook=window.open(''ast_ofl_event_info_param.high_popup_window'',''pbook'', ''toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,left=0,top=0,width=650,height=410'');');
143 htp.p('winpbook.focus();');
144 htp.p('}');
145
146 htp.p(' function validateForm(objform) {');
147 htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
148 htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
149 htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
150 htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
151 htp.p(' var e_month = objform.p_em_date.selectedIndex;');
152 htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
153 htp.p(' var p_valid = true;');
154 htp.p(' if ((s_month == "3"&&s_day== "30")||(s_month == "5"&&s_day== "30")');
155 htp.p(' ||(s_month == "8"&&s_day== "30")||(s_month == "10"&&s_day== "30")){');
156 htp.p(' alert(''Start Date Must be a Valid Date'');');
157 htp.p(' p_valid = false;}');
158 htp.p(' if ((e_month == "3"&&e_day== "30")||(e_month == "5"&&e_day== "30")');
159 htp.p(' ||(e_month == "8"&&e_day== "30")||(e_month == "10"&&e_day== "30")){');
160 htp.p(' alert(''End Date Must be a Valid Date'');');
161 htp.p(' p_valid = false;}');
162 htp.p(' if (s_month == "1"){');
163 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
164 htp.p(' && (s_day == "29"||s_day == "30")){');
165 htp.p(' alert(''Start Date Must be a Valid Date'');');
166 htp.p(' p_valid = false;}');
167 htp.p(' }');
168 htp.p(' if (s_month == "1"){');
169 htp.p(' if ((s_year != "2" != "6" != "10" != "14" != "18")');
170 htp.p(' && (s_day == "28"||s_day == "29"||s_day == "30")){');
171 htp.p(' alert(''Start Date Must be a Valid Date'');');
172 htp.p(' p_valid = false;}');
173 htp.p(' }');
174 htp.p(' if (e_month == "1"){');
175 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
176 htp.p(' && (e_day == "29"||e_day == "30")){');
177 htp.p(' alert(''End Date Must be a Valid Date'');');
178 htp.p(' p_valid = false;}');
179 htp.p(' }');
180 htp.p(' if (e_month == "1"){');
181 htp.p(' if ((e_year != "2" != "6" != "10" != "14" != "18")');
182 htp.p(' && (e_day == "28"||e_day == "29"||e_day == "30")){');
183 htp.p(' alert(''End Date Must be a Valid Date'');');
184 htp.p(' p_valid = false;}');
185 htp.p(' }');
186 htp.p(' if (s_year > e_year){');
187 htp.p(' alert(''Start Date must be less than End Date'');');
188 htp.p(' p_valid = false;}');
189 htp.p(' if (s_year == e_year){');
190 htp.p(' if (s_month > e_month){');
191 htp.p(' alert(''Start Date must be less than End Date'');');
192 htp.p(' p_valid = false;}');
193 htp.p(' if (s_month == e_month){');
194 htp.p(' if (s_day > e_day){');
195 htp.p(' alert(''Start Date must be less than End Date'');');
196 htp.p(' p_valid = false;}');
197 htp.p(' }');
198 htp.p(' }');
199 htp.p(' if (p_valid){');
200 htp.p(' objform.submit();}');
201 htp.p('}');
202 htp.p('</script>');
203 htp.headClose;
204 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
205 htp.tableopen;
206 htp.tableRowOpen( cvalign => 'top' );
207 htp.tableData( ' ', cattributes => ' height=9');
208 htp.tableData( '<FONT size=2 face="Times New Roman">' || '</FONT>', calign => 'right', crowspan => '2', ccolspan => '3', cattributes => ' width=154');
209 htp.tableData( ' ');
210 htp.tableRowClose;
211 htp.tableClose;
212 htp.tableOpen(cattributes=>'width="600" ');
213 htp.tableRowClose;
214
215 htp.tableRowOpen();
216 htp.p('<td align="RIGHT" width="32%" valign="top">Start Date</td>');
217 htp.p('<td>');
218 htp.p ('<SELECT name="p_sd_date">');
219 FOR i IN 1..day_data.count LOOP
220 htp.FormSelectOption(day_data(i),
221 cattributes => ' value= '||day_data(i),
222 cselected => 'TRUE' );
223 END LOOP;
224 htp.FormSelectClose;
228 FOR i IN 1..12 LOOP
225 htp.p('-');
226
227 htp.p ('<SELECT name="p_sm_date">');
229 if i=1 then
230 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
231 cattributes => ' value='||lpad(to_char(i),2,'0'),
232 cselected => 'TRUE' );
233 else
234 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
235 cattributes => ' value='||lpad(to_char(i),2,'0'));
236 end if;
237 END LOOP;
238 htp.FormSelectClose;
239 htp.p('-');
240
241 htp.p ('<SELECT name="p_sy_date">');
242 FOR i IN 1..year_data.count LOOP
243 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
244 htp.FormSelectOption(year_data(i),
245 cattributes => ' value= '||year_data(i),
246 cselected => 'TRUE' );
247 ELSE
248 htp.FormSelectOption(year_data(i),
249 cattributes => ' value= '||year_data(i));
250 END IF;
251 END LOOP;
252 htp.FormSelectClose;
253 htp.p('</td>');
254 htp.tableRowClose;
255
256 htp.tableRowOpen();
257 htp.p('<td align="RIGHT" width="32%" valign="top">End Date</td>');
258 htp.p('<td>');
259 htp.p ('<SELECT name="p_ed_date">');
260 FOR i IN 1..day_data.count LOOP
261 htp.FormSelectOption(day_data(i),
262 cattributes => ' value= '||day_data(i),
263 cselected => 'TRUE' );
264 END LOOP;
265 htp.FormSelectClose;
266 htp.p('-');
267
268 htp.p ('<SELECT name="p_em_date">');
269 FOR i IN 1..12 LOOP
270 if i=1 then
271 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
272 cattributes => ' value='||lpad(to_char(i),2,'0'),
273 cselected => 'TRUE' );
274 else
275 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
276 cattributes => ' value='||lpad(to_char(i),2,'0'));
277 end if;
278 END LOOP;
279 htp.FormSelectClose;
280 htp.p('-');
281
282 htp.p ('<SELECT name="p_ey_date">');
283 FOR i IN 1..year_data.count LOOP
284 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
285 htp.FormSelectOption(year_data(i),
286 cattributes => ' value= '||year_data(i),
287 cselected => 'TRUE' );
288 ELSE
289 htp.FormSelectOption(year_data(i),
290 cattributes => ' value= '||year_data(i));
291 END IF;
292 END LOOP;
293 htp.FormSelectClose;
294 htp.p('</td>');
295 htp.tableRowClose;
296
297 htp.tableRowOpen;
298 htp.p('<td align="LEFT" colspan="2"><b>Range of Event Codes:</b></td>');
299 htp.tableRowClose;
300
301 htp.tableRowOpen();
302 htp.p('<td align="RIGHT" width="32%" valign="top">From Event Code</td>');
303 htp.p('<td>');
304
305 htp.formHidden('p_promotion_code_low',null,null);
306 htp.formText('p_low_description',50,null);
307 htp.p('<td><A HREF="javascript:btn_press()">LOV</A></td>');
308 htp.p('</td>');
309 htp.tableRowClose;
310
311 htp.tableRowOpen();
312 htp.p('<td align="RIGHT" width="32%" valign="top">To Event Code</td>');
313 htp.p('<td>');
314
315 htp.formHidden('p_promotion_code_high',null,null);
316 htp.formText('p_high_description',50,null);
317 htp.p('<td><A HREF="javascript:btn_press_high()">LOV</A></td>');
318 htp.p('</td>');
319 htp.tableRowClose;
320
321 htp.tableClose;
322 htp.Br;
323 htp.Br;
324 htp.Br;
325 footer;
326 htp.FormClose;
327 htp.bodyclose;
328 htp.htmlclose;
329 else
330 htp.p('Invalid session');
331 end if;
332 exception
333 when others then
334 htp.p(SQLERRM);
335 END event_information_paramform;
336
337 procedure popup_window (p_event_code in varchar2 default null,
338 p_submit in varchar2 default null,
339 p_selected_event_code in varchar2 default null) is
340
341 CURSOR cur_promotion_code(Event_Code VARCHAR2) is
342 -- RN 25/9/00
343 SELECT event_offer_id code, event_offer_name name
344 FROM ams_event_offers_vl
345 WHERE event_offer_id LIKE NVL(UPPER(Event_Code), '%') || '%'
346 OR UPPER(event_offer_name) LIKE NVL(UPPER(Event_Code), '%') || '%'
347 -- select code, name
348 -- from as_promotions_all
349 -- where type = 'E'
350 -- and (upper(code) like nvl(upper(p_event_code),'%')||'%' or upper(name) like nvl(upper(p_event_code),'%')||'%')
351 order by 1;
352
353 BEGIN
354 htp.htmlOpen;
355 htp.headOpen;
356 htp.title('Event Information Report - Event Codes');
357 htp.p('<SCRIPT language="JavaScript">');
358 htp.p('function PassBack(p_low_description,p_promotion_code_low) {
359
360 var f_len = p_low_description.length
361 for (var i = 0; i < f_len; i++)
362 {
363 if (p_low_description.substring(i, (i+2)) == ''?$'')
364 {
365 p_low_description=p_low_description.substring(0,(i))+"''"+p_low_description.substring((i+2),(f_len))
366 }
367 f_len = p_low_description.length
368 }
369
370 opener.document.forms[0].p_low_description.value = p_low_description;
371 opener.document.forms[0].p_promotion_code_low.value = p_promotion_code_low;
372 opener.document.forms[0].p_low_description.focus();
373 close();
374
375 }');
376
380 htp.p('<CENTER>');
377 htp.p('</SCRIPT>');
378 htp.headClose;
379 htp.p('<BODY bgcolor="#CCCCCC">');
381 htp.bold('<font size="+1">Search Events</font>');
382 htp.formOpen('AST_OFL_EVENT_INFO_PARAM.popup_window','POST');
383 htp.tableOpen;
384 htp.tableRowOpen;
385 htp.tableData('Event Code: ');
386 htp.tableData(htf.formText('p_event_code',null,null,nvl(p_event_code,'%')));
387 htp.tableRowClose;
388 htp.tableClose;
389 htp.nl;
390 htp.formSubmit('p_submit','Search Events');
391 htp.formClose;
392 htp.tableOpen;
393 if p_event_code is NULL then
394 htp.tableRowOpen;
395 --htp.p('<td><i>Enter ''%'' to search for all events.</i></td>');
396 htp.p('<td><i>Enter the first few characters of the Event Code or Event Name</i></td>');
397 htp.tableRowClose;
398 htp.tableRowOpen;
399 htp.p('<td><i>to search by (wildcards such as _ and % are supported)</i></td>');
400 htp.tableRowClose;
401 else
402 for c_rec in cur_promotion_code(p_event_code) loop
403 htp.tableRowOpen;
404 display_event_name := c_rec.name;
405 -- check for any special characters ..... BJANI (12/03/2001)
406 if instr(c_rec.name,'''') > 0 then
407 -- adding an escape character .... BJANI
408 c_rec.name := REPLACE(c_rec.name,'''','?$');
409 end if;
410 -- BJANI end
411 htp.p('<td><A HREF="javascript:PassBack('''||c_rec.code||' '||c_rec.name||''','''||c_rec.code||''')">'||c_rec.code||' '||display_event_name||'</A></td>');
412 htp.tableRowClose;
413 end loop;
414 end if;
415 htp.tableClose;
416 htp.p('</CENTER>');
417 htp.bodyClose;
418 htp.htmlClose;
419 END popup_window;
420
421 procedure high_popup_window (p_event_code in varchar2 default null,
422 p_submit in varchar2 default null,
423 p_selected_event_code in varchar2 default null) is
424
425 CURSOR cur_promotion_code(Event_Code VARCHAR2) is
426 -- RN 25/9/00
427 SELECT event_offer_id code, event_offer_name name
428 FROM ams_event_offers_vl
429 WHERE event_offer_id LIKE NVL(UPPER(Event_Code), '%') || '%'
430 OR UPPER(event_offer_name) LIKE NVL(UPPER(Event_Code), '%') || '%'
431 -- select code, name
432 -- from as_promotions_all
433 -- where type = 'E'
434 -- and (upper(code) like nvl(upper(Event_Code),'%')||'%' or upper(name) like nvl(upper(Event_Code),'%')||'%')
435 order by 1;
436
437 BEGIN
438 htp.htmlOpen;
439 htp.headOpen;
440 htp.title('Event Information Report - Event Codes');
441 htp.p('<SCRIPT language="JavaScript">');
442
443 htp.p('function PassBack(p_high_description,p_promotion_code_high) {
444
445 var f_len = p_high_description.length
446 for (var i = 0; i < f_len; i++)
447 {
448 if (p_high_description.substring(i, (i+2)) == ''?$'')
449 {
450 p_high_description=p_high_description.substring(0,(i))+"''"+p_high_description.substring((i+2),(f_len))
451 }
452 f_len = p_high_description.length
453 }
454
455 opener.document.forms[0].p_high_description.value = p_high_description;
456 opener.document.forms[0].p_promotion_code_high.value = p_promotion_code_high;
457 opener.document.forms[0].p_high_description.focus();
458 close();
459
460 }');
461
462 htp.p('</SCRIPT>');
463
464 htp.headClose;
465 htp.p('<BODY bgcolor="#CCCCCC">');
466 htp.p('<CENTER>');
467 htp.bold('<font size="+1">Search Events</font>');
468 htp.formOpen('AST_OFL_EVENT_INFO_PARAM.high_popup_window','POST');
469 htp.tableOpen;
470 htp.tableRowOpen;
471 htp.tableData('Event Code: ');
472 htp.tableData(htf.formText('p_event_code',null,null,nvl(p_event_code,'%')));
473 htp.tableRowClose;
474 htp.tableClose;
475 htp.nl;
476 htp.formSubmit('p_submit','Search Events');
477 htp.formClose;
478 htp.tableOpen;
479 if p_event_code is NULL then
480 htp.tableRowOpen;
484 htp.tableRowOpen;
481 --htp.p('<td><i>Enter ''%'' to search for all events.</i></td>');
482 htp.p('<td><i>Enter the first few characters of the Event Code or Event Name</i></td>');
483 htp.tableRowClose;
485 htp.p('<td><i>to search by (wildcards such as _ and % are supported)</i></td>');
486 htp.tableRowClose;
487 else
488 for c_rec in cur_promotion_code(p_event_code) loop
489 htp.tableRowOpen;
490 display_event_name := c_rec.name;
491 -- check for any special characters ..... BJANI (12/03/2001)
492 if instr(c_rec.name,'''') > 0 then
493 -- adding an escape character .... BJANI
494 c_rec.name := REPLACE(c_rec.name,'''','?$');
495 end if;
496 -- BJANI end
497 htp.p('<td><A HREF="javascript:PassBack('''||c_rec.code||' '||c_rec.name||''','''||c_rec.code||''')">'||c_rec.code||' '||display_event_name||'</A></td>');
498 htp.tableRowClose;
499 end loop;
500 end if;
501 htp.tableClose;
502 htp.p('</CENTER>');
503 htp.bodyClose;
504 htp.htmlClose;
505 END high_popup_window;
506
507 procedure footer is
508 BEGIN
509 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
510 htp.htmlopen;
511 htp.tableRowOpen;
512 htp.tableData( htf.hr, crowspan => '1', ccolspan => '190', cnowrap => 'TRUE');
513 htp.tableRowClose;
514 htp.tableOpen( calign => 'center', cattributes => ' border=0 cellspacing=2 cellpadding=2' );
515 htp.tableRowOpen;
516 htp.formOpen( curl => l_agent||'/AST_OFL_EVENT_INFO_REPORT.report_wrapper', cmethod => 'post', cattributes => ' NAME="MyForm" TARGET="_top"');
517 --htp.tableData( htf.formSubmit( cvalue => 'OK', cattributes => ' onMouseOver="window.status=''OK'';return true"'));
518 htp.p('<td>');
519 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
520 htp.p('</td>');
521 --htp.tableData( '<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
522 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
523 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
524 htp.tableRowClose;
525 htp.tableClose;
526 htp.htmlClose;
527 END footer;
528 END;