[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_EVENT_ATTENDEES_PARAM
Source
1 PACKAGE BODY ast_ofl_event_attendees_param AS
2 /* $Header: astrtepb.pls 115.12 2002/02/06 13:11:39 pkm ship $ */
3
4 TYPE day_table
5 IS
6 TABLE OF VARCHAR2(2000)
7 INDEX BY BINARY_INTEGER;
8 TYPE year_table
9 IS
10 TABLE OF VARCHAR2(2000)
11 INDEX BY BINARY_INTEGER;
12 g_image_prefix VARCHAR2(250) := '/OA_MEDIA/'||icx_sec.getid(icx_sec.pv_language_code)||'/';
13 l_user_id NUMBER;
14 v_date_time VARCHAR2(30);
15 l_agent VARCHAR2(200);
16 ctr1 INTEGER := 0;
17 ctr2 INTEGER := 0;
18 day_data day_table;
19 year_data year_table;
20 ----------------------------------------------------------------------------------------------------
21 PROCEDURE header
22 IS
23 BEGIN
24 SELECT fnd_date.date_to_chardate(SYSDATE)
25 INTO v_date_time
26 FROM DUAL;
27 htp.htmlOpen;
28 htp.headOpen;
29 htp.title('Event Attendees Information');
30 htp.headClose;
31 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
32 htp.tableOpen('border="0" ');
33 htp.tableRowOpen(cAlign => 'TOP');
34 -- htp.tableData(htf.img(cUrl => g_image_prefix || 'oppty.gif'));
35 htp.tableData('<FONT size=+1 face="times new roman">' || 'Event Attendees Report', cNoWrap => 'TRUE');
36 htp.tableData(htf.bold(v_date_time), cAlign => 'right', cColSpan => '110');
37 htp.tableRowClose;
38 htp.tableClose;
39 htp.tableOpen(cAttributes => 'border=0 cellspacing=0 cellpadding=0 width=561');
40 htp.tableRowOpen(cVAlign => 'top');
41 htp.tableData(' ', cColSpan => '2', cAttributes => ' height=9');
42 htp.tableData('<FONT face="Times New Roman">' || htf.bold('Please specify the criteria and select OK. ') || '</FONT>', cAlign => 'center', cRowSpan => '2', cColSpan => '110', cAttributes => ' width=346');
43 htp.tableData(' ', cColSpan => '6');
44 htp.br;
45 htp.tableRowClose;
46 htp.tableClose;
47 htp.bodyClose;
48 htp.headClose;
49 htp.htmlClose;
50 END;
51 ----------------------------------------------------------------------------------------------------
52 PROCEDURE event_attendees_paramform
53 IS
54 /* RN 25/9/00
55 CURSOR cur_promotion_code
56 IS
57 SELECT code, name
58 FROM as_promotions_all
59 WHERE type = 'E'
60 ORDER BY 1;
61 */
62 day_counter NUMBER := 1;
63 year_counter NUMBER := 1990;
64 BEGIN
65 FOR i IN 1..31 LOOP
66 IF day_counter <= 9 THEN
67 day_data(i) := '0' || day_counter;
68 day_counter := day_counter + 1;
69 ELSE
70 day_data(i) := TO_CHAR(day_counter);
71 day_counter := day_counter + 1;
72 END IF;
73 END LOOP;
74 FOR i IN 1..21 LOOP
75 year_data(i) := TO_CHAR(year_counter);
76 year_counter := year_counter + 1;
77 END LOOP;
78 IF (icx_sec.validateSession) THEN
79 header;
80 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
81 --Returns login user Id
82 htp.formOpen(owa_util.Get_Owa_Service_Path || 'ast_ofl_event_attendees_report.report_wrapper', cAttributes => ' NAME="param"');
83 htp.htmlOpen;
84 htp.headOpen;
85 htp.title('Event Attendees Report');
86 htp.p('<SCRIPT language="JavaScript">');
87 htp.p('window.name = "bigwindow";');
88 htp.p('function btn_press()');
89 htp.p('{');
90 htp.p('winpbook=window.open(''ast_ofl_event_attendees_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'');');
91 htp.p('winpbook.focus();');
92 htp.p('}');
93 htp.p('window.name = "bigwindow";');
94 htp.p('function btn_press_high()');
95 htp.p('{');
96 htp.p('winpbook=window.open(''ast_ofl_event_attendees_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'');');
97 htp.p('winpbook.focus();');
98 htp.p('}');
99 htp.p(' function validateForm(objform) {');
100 htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
101 htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
102 htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
103 htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
104 htp.p(' var e_month = objform.p_em_date.selectedIndex;');
105 htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
106 htp.p(' var p_valid = true;');
107 htp.p(' if ((s_month == "3"&&s_day== "30")||(s_month == "5"&&s_day== "30")');
108 htp.p(' ||(s_month == "8"&&s_day== "30")||(s_month == "10"&&s_day== "30")){');
109 htp.p(' alert(''Start Date Must be a Valid Date'');');
110 htp.p(' p_valid = false;}');
111 htp.p(' if ((e_month == "3"&&e_day== "30")||(e_month == "5"&&e_day== "30")');
112 htp.p(' ||(e_month == "8"&&e_day== "30")||(e_month == "10"&&e_day== "30")){');
113 htp.p(' alert(''End Date Must be a Valid Date'');');
114 htp.p(' p_valid = false;}');
115 htp.p(' if (s_month == "1"){');
116 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
117 htp.p(' && (s_day == "29"||s_day == "30")){');
118 htp.p(' alert(''Start Date Must be a Valid Date'');');
119 htp.p(' p_valid = false;}');
120 htp.p(' }');
121 htp.p(' if (s_month == "1"){');
122 htp.p(' if ((s_year != "2" != "6" != "10" != "14" != "18")');
123 htp.p(' && (s_day == "28"||s_day == "29"||s_day == "30")){');
124 htp.p(' alert(''Start Date Must be a Valid Date'');');
125 htp.p(' p_valid = false;}');
126 htp.p(' }');
127 htp.p(' if (e_month == "1"){');
128 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
129 htp.p(' && (e_day == "29"||e_day == "30")){');
130 htp.p(' alert(''End Date Must be a Valid Date'');');
131 htp.p(' p_valid = false;}');
132 htp.p(' }');
133 htp.p(' if (e_month == "1"){');
134 htp.p(' if ((e_year != "2" != "6" != "10" != "14" != "18")');
135 htp.p(' && (e_day == "28"||e_day == "29"||e_day == "30")){');
136 htp.p(' alert(''End Date Must be a Valid Date'');');
137 htp.p(' p_valid = false;}');
138 htp.p(' }');
139 htp.p(' if (s_year > e_year){');
140 htp.p(' alert(''Start Date must be less than End Date'');');
141 htp.p(' p_valid = false;}');
142 htp.p(' if (s_year == e_year){');
143 htp.p(' if (s_month > e_month){');
144 htp.p(' alert(''Start Date must be less than End Date'');');
145 htp.p(' p_valid = false;}');
146 htp.p(' if (s_month == e_month){');
147 htp.p(' if (s_day > e_day){');
148 htp.p(' alert(''Start Date must be less than End Date'');');
149 htp.p(' p_valid = false;}');
150 htp.p(' }');
151 htp.p(' }');
152 htp.p(' if (p_valid){');
153 htp.p(' objform.submit();}');
154 htp.p('}');
155 htp.p('</script>');
156 htp.headClose;
157 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
158 htp.tableOpen;
159 htp.tableRowOpen(cVAlign => 'top');
160 htp.tableData(' ', cAttributes => ' height=9');
161 htp.tableData('<FONT size=2 face="Times New Roman">' || '</FONT>', cAlign => 'right', cRowSpan => '2', cColSpan => '3', cAttributes => ' width=154');
162 htp.tableData(' ');
163 htp.tableRowClose;
164 htp.tableClose;
165 htp.tableOpen(cAttributes => 'width="600" ');
166 --htp.tableRowOpen();
167 --htp.p('<td align="RIGHT" width="32%" valign="top">Start Date</td>');
168 --htp.p('<td>');
169 --htp.p('<INPUT TYPE= "TEXT", NAME="p_start_date", VALUE="", SIZE="11">');
170 --htp.p('</td>');
171 --htp.tableRowClose;
172 htp.tableRowClose;
173 htp.tableRowOpen();
174 htp.p('<td align="RIGHT" width="32%" valign="top">Start Date</td>');
175 htp.p('<td>');
176
177 /*
178 htp.p('<SELECT name="p_sm_date">');
179 FOR i IN 1..12 LOOP
180 IF i = 1 THEN
181 htp.formSelectOption(LPAD(TO_CHAR(i), 2, '0'), cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'), cSelected => 'TRUE');
182 ELSE
183 htp.formSelectOption(LPAD(TO_CHAR(i), 2, '0'), cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
184 END IF;
185 END LOOP;
186 htp.formSelectClose;
187 htp.p('-');
188 */
189 htp.p('<SELECT name="p_sd_date">');
190 FOR i IN 1..day_data.count LOOP
191 htp.formSelectOption(day_data(i), cAttributes => ' value= ' || day_data(i), cSelected => 'TRUE');
192 END LOOP;
193 htp.FormSelectClose;
194 htp.p('-');
195
196 htp.p('<SELECT name="p_sm_date">');
197 htp.FormSelectOption('JAN',cselected => 'TRUE');
198 htp.FormSelectOption('FEB');
199 htp.FormSelectOption('MAR');
200 htp.FormSelectOption('APR');
201 htp.FormSelectOption('MAY');
202 htp.FormSelectOption('JUN');
203 htp.FormSelectOption('JUL');
204 htp.FormSelectOption('AUG');
205 htp.FormSelectOption('SEP');
206 htp.FormSelectOption('OCT');
207 htp.FormSelectOption('NOV');
208 htp.FormSelectOption('DEC');
209 htp.formSelectClose;
210 htp.p('-');
211
212 htp.p('<SELECT name="p_sy_date">');
213 FOR i IN 1..year_data.count LOOP
214 htp.formSelectOption(year_data(i), cAttributes => ' value= ' || year_data(i));
215 END LOOP;
216 htp.formSelectClose;
217 htp.p('</td>');
218 htp.tableRowClose;
219 htp.tableRowOpen();
220 htp.p('<td align="RIGHT" width="32%" valign="top">End Date</td>');
221 htp.p('<td>');
222 htp.p('<SELECT name="p_ed_date">');
223 FOR i IN 1..day_data.count LOOP
224 htp.formSelectOption(day_data(i), cAttributes => ' value= '||day_data(i), cSelected => 'TRUE');
225 END LOOP;
226 htp.formSelectClose;
227 htp.p('-');
228
229 /*
230 htp.p('<SELECT name="p_em_date">');
231 FOR i IN 1..12 LOOP
232 IF i = 1 THEN
233 htp.formSelectOption(LPAD(TO_CHAR(i), 2, '0'), cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'), cSelected => 'TRUE');
234 ELSE
235 htp.formSelectOption(LPAD(TO_CHAR(i), 2, '0'), 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_em_date">');
242 htp.FormSelectOption('JAN',cselected => 'TRUE');
243 htp.FormSelectOption('FEB');
244 htp.FormSelectOption('MAR');
245 htp.FormSelectOption('APR');
246 htp.FormSelectOption('MAY');
247 htp.FormSelectOption('JUN');
248 htp.FormSelectOption('JUL');
249 htp.FormSelectOption('AUG');
250 htp.FormSelectOption('SEP');
251 htp.FormSelectOption('OCT');
252 htp.FormSelectOption('NOV');
253 htp.FormSelectOption('DEC');
254 htp.formSelectClose;
255 htp.p('-');
256
257 htp.p('<SELECT name="p_ey_date">');
258 -- htp.formSelectOption(' ');
259 FOR i IN 1..year_data.count LOOP
260 htp.formSelectOption(year_data(i), cAttributes => ' value= ' || year_data(i), cSelected => 'TRUE');
261 END LOOP;
262 htp.formSelectClose;
263 htp.p('</td>');
264 htp.tableRowClose;
265 htp.tableRowOpen;
266 htp.p('<td align="LEFT" colspan="2"><b>Range of Event Codes:</b></td>');
267 htp.tableRowClose;
268 htp.tableRowOpen();
269 htp.p('<td align="RIGHT" width="32%" valign="top">From Event Code</td>');
270 htp.p('<td>');
271 htp.formHidden('p_promotion_code_low', NULL, NULL);
272 htp.formText('p_low_description', 50, NULL);
273 htp.p('<td><A HREF="javascript:btn_press()">LOV</A></td>');
274 --htp.p('<SELECT name="p_promotion_code_low">');
275 --htp.formSelectOption('0', cSelected => 'TRUE');
276 --FOR rec_promotion_code IN cur_promotion_code LOOP
277 --htp.formSelectOption(rec_promotion_code.code || ' ' || rec_promotion_code.name, cAttributes => ' value= ' || rec_promotion_code.code);
278 --END LOOP;
279 --htp.formSelectClose;
280 htp.p('</td>');
281 htp.tableRowClose;
282 htp.tableRowOpen();
283 htp.p('<td align="RIGHT" width="32%" valign="top">To Event Code</td>');
284 htp.p('<td>');
285 htp.formHidden('p_promotion_code_high', NULL, NULL);
286 htp.formText('p_high_description', 50, NULL);
287 htp.p('<td><A HREF="javascript:btn_press_high()">LOV</A></td>');
288 --htp.p('<SELECT name="p_promotion_code_High">');
289 --htp.formSelectOption('9999999', cSelected => 'TRUE');
290 --FOR rec_promotion_code IN cur_promotion_code LOOP
291 --htp.formSelectOption(rec_promotion_code.code || ' ' || rec_promotion_code.name ,cAttributes => ' value= ' || rec_promotion_code.code);
292 --END LOOP;
293 --htp.formSelectClose;
294 htp.p('</td>');
295 htp.tableRowClose;
296 htp.tableClose;
297 htp.br;
298 htp.br;
299 htp.br;
300 footer;
301 htp.formClose;
302 htp.bodyClose;
303 htp.htmlClose;
304 ELSE
305 htp.p('Invalid session');
306 END IF;
307 EXCEPTION
308 WHEN others THEN
309 htp.p(SQLERRM);
310 END event_attendees_paramform;
311 ----------------------------------------------------------------------------------------------------
312 PROCEDURE popup_window
313 (p_event_code IN VARCHAR2 DEFAULT NULL,
314 p_submit IN VARCHAR2 DEFAULT NULL,
315 p_selected_event_code IN VARCHAR2 DEFAULT NULL)
316 IS
317 CURSOR cur_promotion_code(p_e_c varchar2)
318 IS
319 -- RN 25/9/00
320 SELECT event_offer_id code, event_offer_name name
321 FROM ams_event_offers_vl
322 WHERE event_offer_id LIKE p_e_c
323 OR UPPER(event_offer_name) LIKE p_e_c
324 -- SELECT code, name
325 -- FROM as_promotions_all
326 -- WHERE type = 'E'
327 -- AND (UPPER(CODE) LIKE NVL(UPPER(p_event_code), '%') || '%'
328 -- OR UPPER(name) LIKE NVL(UPPER(p_event_code), '%') || '%')
329 ORDER BY 1;
330 BEGIN
331 htp.htmlOpen;
332 htp.headOpen;
333 htp.title('Event Attendees Report - Event Codes');
334 htp.p('<SCRIPT language="JavaScript">');
335 htp.p('function PassBack(p_low_description,p_promotion_code_low) {');
336 htp.p('opener.document.forms[0].p_low_description.value = p_low_description;');
337 htp.p('opener.document.forms[0].p_promotion_code_low.value = p_promotion_code_low;');
338 htp.p('opener.document.forms[0].p_low_description.focus();');
339 htp.p('close();');
340 htp.p('}');
341 htp.p('</SCRIPT>');
342 htp.headClose;
343 htp.p('<BODY bgcolor="#CCCCCC">');
344 htp.p('<CENTER>');
345 htp.bold('<font size="+1">Search Events</font>');
346 htp.formOpen('ast_ofl_event_attendees_param.popup_window', 'POST');
347 htp.tableOpen;
348 htp.tableRowOpen;
349 htp.tableData('Event Code: ');
350 -- htp.tableData(htf.formText('p_event_code', NULL, NULL, NVL(p_event_code, '%')));
351 htp.tableData(htf.formText('p_event_code', NULL, NULL, p_event_code));
352 htp.tableRowClose;
353 htp.tableClose;
354 htp.nl;
355 htp.formSubmit('p_submit', 'Search Events');
356 htp.formClose;
357 htp.tableOpen;
358 if p_event_code is NULL then
359 htp.tableRowOpen;
360 --htp.p('<td><i>Enter ''%'' to search for all events.</i></td>');
361 htp.p('<td><i>Enter the first few characters of the Event Code or Event Name</i></td>');
362 htp.tableRowClose;
363 htp.tableRowOpen;
364 htp.p('<td><i>to search by (wildcards such as _ and % are supported)</i></td>');
365 htp.tableRowClose;
366 else
367 -- FOR c_rec IN cur_promotion_code(nvl(p_event_code,'') || '%') LOOP
368 FOR c_rec IN cur_promotion_code(upper(p_event_code) || '%') LOOP
369 htp.tableRowOpen;
370 htp.p('<td><A HREF="javascript:PassBack(''' || c_rec.code || ' ' || c_rec.name || ''',''' || c_rec.code || ''')">' || c_rec.code || ' ' || c_rec.name || '</A></td>');
371 htp.tableRowClose;
372 END LOOP;
373 end if;
374 htp.tableClose;
375 htp.p('</CENTER>');
376 htp.bodyClose;
377 htp.htmlClose;
378 END popup_window;
379 ----------------------------------------------------------------------------------------------------
380 PROCEDURE high_popup_window
381 (p_event_code IN VARCHAR2 DEFAULT NULL,
382 p_submit IN VARCHAR2 DEFAULT NULL,
383 p_selected_event_code IN VARCHAR2 DEFAULT NULL)
384 IS
385 CURSOR cur_promotion_code(p_e_c varchar2)
386 IS
387 -- RN 25/9/00
388 SELECT event_offer_id code, event_offer_name name
389 FROM ams_event_offers_vl
390 WHERE event_offer_id LIKE p_e_c
391 OR UPPER(event_offer_name) LIKE p_e_c
392 -- SELECT code, name
393 -- FROM as_promotions_all
394 -- WHERE type = 'E'
395 -- AND (UPPER(code) LIKE NVL(UPPER(p_event_code), '%') || '%'
396 -- OR UPPER(name) LIKE NVL(UPPER(p_event_code), '%') || '%')
397 ORDER BY 1;
398 BEGIN
399 htp.htmlOpen;
400 htp.headOpen;
401 htp.title('Event Attendees Report - Event Codes');
402 htp.p('<SCRIPT language="JavaScript">');
403 htp.p('function PassBack(p_high_description,p_promotion_code_high) {');
404 htp.p('opener.document.forms[0].p_high_description.value = p_high_description;');
405 htp.p('opener.document.forms[0].p_promotion_code_high.value = p_promotion_code_high;');
406 htp.p('opener.document.forms[0].p_high_description.focus();');
407 htp.p('close();');
408 htp.p('}');
409 htp.p('</SCRIPT>');
410 htp.headClose;
411 htp.p('<BODY bgcolor="#CCCCCC">');
412 htp.p('<CENTER>');
413 htp.bold('<font size="+1">Search Events</font>');
414 htp.formOpen('ast_ofl_event_attendees_param.high_popup_window', 'POST');
415 htp.tableOpen;
416 htp.tableRowOpen;
417 htp.tableData('Event Code: ');
418 htp.tableData(htf.formText('p_event_code', NULL, NULL, p_event_code));
419 htp.tableRowClose;
420 htp.tableClose;
421 htp.nl;
422 htp.formSubmit('p_submit', 'Search Events');
423 htp.formClose;
424 htp.tableOpen;
425 if p_event_code is NULL then
426 htp.tableRowOpen;
427 --htp.p('<td><i>Enter ''%'' to search for all events.</i></td>');
428 htp.p('<td><i>Enter the first few characters of the Event Code or Event Name</i></td>');
429 htp.tableRowClose;
430 htp.tableRowOpen;
431 htp.p('<td><i>to search by (wildcards such as _ and % are supported)</i></td>');
432 htp.tableRowClose;
433 else
434 FOR c_rec IN cur_promotion_code(upper(p_event_code) || '%') LOOP
435 htp.tableRowOpen;
436 htp.p('<td><A HREF="javascript:PassBack(''' || c_rec.code || ' ' || c_rec.name || ''',''' || c_rec.code || ''')">' || c_rec.code || ' ' || c_rec.name || '</A></td>');
437 htp.tableRowClose;
438 END LOOP;
439 end if;
440 htp.tableClose;
441 htp.p('</CENTER>');
442 htp.bodyClose;
443 htp.htmlClose;
444 END high_popup_window;
445 ----------------------------------------------------------------------------------------------------
446 PROCEDURE footer
447 IS
448 BEGIN
449 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
450 htp.htmlOpen;
451 htp.tableRowOpen;
452 htp.tableData(htf.hr, cRowSpan => '1', cColSpan => '190', cNoWrap => 'TRUE');
453 htp.tableRowClose;
454 htp.tableOpen(cAlign => 'center', cAttributes => ' border=0 cellspacing=2 cellpadding=2');
455 htp.tableRowOpen;
456 htp.formOpen(cUrl => l_agent || 'ast_ofl_event_attendees_report.report_wrapper', cMethod => 'post', cAttributes => ' NAME="MyForm" TARGET="_top"');
457 --htp.tableData(htf.formSubmit(cValue => 'OK', cAttributes => ' onMouseOver="window.status=''OK'';return true"'));
458 htp.p('<td>');
459 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
460 htp.p('</td>');
461 --htp.tableData('<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
462 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
463 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
464 htp.tableRowClose;
465 htp.tableClose;
466 htp.htmlClose;
467 END footer;
468 END;