[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_TRANS_SIZES_PARAM
Source
1 PACKAGE BODY AST_OFL_TRANS_SIZES_PARAM AS
2 /* $Header: astrttpb.pls 115.24 2002/02/07 15:24:53 pkm ship $ */
3
4 g_image_prefix varchar2(250) := '/OA_MEDIA/'||icx_sec.getid(icx_sec.pv_language_code)||'/';
5 l_user_id number;
6 v_date_time varchar2(30);
7 l_agent varchar2(200);
8 ctr1 integer := 0;
9 ctr2 integer := 0;
10
11 -- Changed for 11i AJScott
12 -- G_DATE_FORMAT varchar2(20) := as_ofl_util_pkg.get_date_format;
13
14 l_test owa_util.dateType;
15
16
17 TYPE day_TABLE IS table of varchar2(2000) INDEX BY BINARY_INTEGER;
18 TYPE year_TABLE IS table of varchar2(2000) INDEX BY BINARY_INTEGER;
19
20 day_data day_table;
21 year_data year_table;
22
23 /* variable for supporting multi-currency */
24 v_usr_currency_code varchar2(15);
25
26
27 procedure header is
28
29 -----------------------------------------------------------------------------------------------
30 --
31 -- PROCEDURE: header
32 --
33 -- DESCRIPTION: This procedure is creates the descriptive header in the parameter form
34 --
35 -----------------------------------------------------------------------------------------------
36 begin
37
38 select to_char(sysdate, 'DD-MON-YYYY') into v_date_time from dual;
39
40 htp.htmlopen;
41 htp.headOpen;
42 htp.title('Transaction Counts and Deal Sizes');
43 htp.headClose;
44 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
45 htp.tableOpen('border="0" ');
46 htp.tableRowOpen( calign => 'TOP' );
47 --htp.tableData( htf.img(curl=>g_image_prefix||'oppty.gif'));
48 htp.tableData( '<FONT size=+1 face="times new roman">' || 'Transaction Counts and Deal Sizes Report', cnowrap => 'TRUE');
49 htp.tableData(htf.bold(v_date_time),calign => 'right',ccolspan => '110');
50 htp.tableRowClose;
51 htp.tableClose;
52 htp.tableOpen( cattributes => 'border=0 cellspacing=0 cellpadding=0 width=561' );
53 htp.tableRowOpen( cvalign => 'top' );
54 htp.tableData( ' ', ccolspan => '2', cattributes => ' height=9');
55 htp.tableData( '<FONT face="Times New Roman">' ||htf.bold( 'Please specify the criteria and select OK. ') ||
56 '</FONT>', calign => 'center', crowspan => '2', ccolspan => '110', cattributes => ' width=346');
57 htp.tableData( ' ', ccolspan => '6');
58 htp.Br;
59 htp.tableRowClose;
60 htp.tableClose;
61 htp.bodyClose;
62 htp.headClose;
63 htp.htmlClose;
64 end;
65 -----------------------------------------------------------------------------------------------
66 procedure trans_deal_sizes_paramform is
67
68 -----------------------------------------------------------------------------------------------
69 --
70 -- PROCEDURE: trans_deal_sizes_paramform
71 --
72 -- DESCRIPTION: This procedure is main body of the parameter form
73 --
74 -----------------------------------------------------------------------------------------------
75 --Added new sales group on 15-feb-01 by sesundar
76
77 CURSOR cur_sales_group(p_userid NUMBER) IS
78 select grpd.group_id sgi,
79 decode(grpd.group_id, grpd.parent_group_id,
80 decode(topgrp.manager_flag, 'Y', grptl.group_name,
81 ' *'||grptl.group_name),
82 decode(topgrp.manager_flag, 'Y',
83 decode(grpd.immediate_parent_flag, 'Y',
84 '-'||grptl.group_name, '--'||grptl.group_name),
85 decode(grpd.immediate_parent_flag, 'Y',
86 ' -'||grptl.group_name, ' --'||grptl.group_name))) name
87 from jtf_rs_groups_denorm grpd,
88 jtf_rs_groups_tl grptl,
89 (select distinct grpb.group_id, rrb2.manager_flag
90 from jtf_rs_groups_b grpb,
91 jtf_rs_role_relations rrel2,
92 jtf_rs_roles_b rrb2,
93 jtf_rs_resource_extns rsc2,
94 jtf_rs_group_members mem,
95 fnd_user fnu
96 where grpb.group_id = mem.group_id
97 and trunc(sysdate) between grpb.start_date_active
98 and nvl(grpb.end_date_active, trunc(sysdate))
99 and rrb2.role_type_code in ('SALES','TELESALES')
100 and (rrb2.manager_flag = 'Y' or rrb2.admin_flag = 'Y')
101 and rrel2.role_id = rrb2.role_id
102 and trunc(sysdate) between rrel2.start_date_active
103 and nvl(rrel2.end_date_active, trunc(sysdate))
104 and rrel2.role_resource_type = 'RS_GROUP_MEMBER'
105 and rrel2.role_resource_id = mem.group_member_id
106 and mem.resource_id = rsc2.resource_id
107 and mem.delete_flag='N'
108 and rsc2.source_id = fnu.employee_id
109 and fnu.user_id = p_userid) topgrp
110 where grptl.group_id = grpd.group_id
111 and grpd.parent_group_id = topgrp.group_id
112 and trunc(sysdate) between grpd.start_date_active
113 and nvl(grpd.end_date_active, trunc(sysdate))
114 order by 2 desc;
115
116 CURSOR cur_currencies
117 IS
118 select currency_code
119 from fnd_currencies_vl
120 where upper(enabled_flag) = 'Y'
121 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
122 and nvl(end_date_active, trunc(sysdate))
123 order by 1;
124
125
126 day_counter NUMBER := 1;
127 year_counter NUMBER := 1990;
128
129 BEGIN
130
131 FOR i IN 1..31 LOOP
132 if day_counter <= 9 then
133 day_data(i) := '0'||day_counter;
134 day_counter := day_counter +1;
135 else
136 day_data(i) := to_char(day_counter);
137 day_counter := day_counter +1;
138 end if;
139 END LOOP;
140
141 FOR i IN 1..21 LOOP
142 year_data(i) := to_char(year_counter);
143 year_counter := year_counter +1;
144 END LOOP;
145
146
147 if (icx_sec.validateSession(c_commit => FALSE)) then
148 header;
149
150 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
151 -------------------- Returns login user Id--------------------------
152
153 /* set user's currency code */
154 v_usr_currency_code := FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY');
155
156 htp.FormOpen(owa_util.Get_Owa_Service_Path||'ast_ofl_TRANS_SIZES_REPORT.report_wrapper', cattributes => ' NAME="param"');
157 htp.htmlopen;
158 htp.headOpen;
159 htp.title('Event Registration Summary Report');
160 htp.p('<SCRIPT language="JavaScript">');
161 htp.p(' function validateForm(objform) {');
162 htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
163 htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
164 htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
165 htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
166 htp.p(' var e_month = objform.p_em_date.selectedIndex;');
167 htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
168 htp.p(' var p_valid = true;');
169 htp.p(' if ((s_month == "3"&&s_day== "30")||(s_month == "5"&&s_day== "30")');
170 htp.p(' ||(s_month == "8"&&s_day== "30")||(s_month == "10"&&s_day== "30")){');
171 htp.p(' alert(''Start Date Must be a Valid Date'');');
172 htp.p(' p_valid = false;}');
173 htp.p(' if ((e_month == "3"&&e_day== "30")||(e_month == "5"&&e_day== "30")');
174 htp.p(' ||(e_month == "8"&&e_day== "30")||(e_month == "10"&&e_day== "30")){');
175 htp.p(' alert(''End Date Must be a Valid Date'');');
176 htp.p(' p_valid = false;}');
177 htp.p(' if (s_month == "1"){');
178 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
179 htp.p(' && (s_day == "29"||s_day == "30")){');
180 htp.p(' alert(''Start Date Must be a Valid Date'');');
181 htp.p(' p_valid = false;}');
182 htp.p(' }');
183 htp.p(' if (s_month == "1"){');
184 htp.p(' if ((s_year != "2"&&s_year != "6"&&s_year != "10"&&s_year != "14"&&s_year != "18")');
185 htp.p(' && (s_day == "28"||s_day == "29"||s_day == "30")){');
186 htp.p(' alert(''Start Date Must be a Valid Date'');');
187 htp.p(' p_valid = false;}');
188 htp.p(' }');
189 htp.p(' if (e_month == "1"){');
190 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
191 htp.p(' && (e_day == "29"||e_day == "30")){');
192 htp.p(' alert(''End Date Must be a Valid Date'');');
193 htp.p(' p_valid = false;}');
194 htp.p(' }');
195 htp.p(' if (e_month == "1"){');
196 htp.p(' if ((e_year != "2"&&e_year != "6"&&e_year != "10"&&e_year != "14"&&e_year != "18")');
197 htp.p(' && (e_day == "28"||e_day == "29"||e_day == "30")){');
198 htp.p(' alert(''End Date Must be a Valid Date'');');
199 htp.p(' p_valid = false;}');
200 htp.p(' }');
201 htp.p(' if (s_year > e_year){');
202 htp.p(' alert(''Start Date must be less than End Date'');');
203 htp.p(' p_valid = false;}');
204 htp.p(' if (s_year == e_year){');
205 htp.p(' if (s_month > e_month){');
206 htp.p(' alert(''Start Date must be less than End Date'');');
207 htp.p(' p_valid = false;}');
208 htp.p(' if (s_month == e_month){');
209 htp.p(' if (s_day > e_day){');
210 htp.p(' alert(''Start Date must be less than End Date'');');
211 htp.p(' p_valid = false;}');
212 htp.p(' }');
213 htp.p(' }');
214 htp.p(' if (p_valid){');
215 htp.p(' objform.submit();}');
216 htp.p('}');
217 htp.p('</script>');
218 htp.headClose;
219 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
220 htp.tableopen;
221 htp.tableRowOpen( cvalign => 'top' );
222 htp.tableData( ' ', cattributes => ' height=9');
223 htp.tableData( '<FONT size=2 face="Times New Roman">' || '</FONT>', calign => 'right', crowspan => '2', ccolspan => '3', cattributes => ' width=154');
224 htp.tableData( ' ');
225 htp.tableRowClose;
226 htp.tableClose;
227 htp.tableOpen(cattributes=>'width="600" ');
228 htp.tableRowOpen();
229 htp.p('<td align="RIGHT" width="50%"valign="top">Select Output Format</td>');
230 htp.p('<td>');
231 htp.p ('<SELECT name="p_response">');
232 htp.FormSelectOption('Excel');
233 htp.FormSelectOption('HTML',cselected => 'TRUE');
234 htp.FormSelectClose;
235 htp.tableRowClose;
236
237 htp.tableRowClose;
238
239 htp.tableRowOpen();
240 htp.p('<td align="RIGHT" width="50%" valign="top">Start Date</td>');
241 htp.p('<td>');
242 --owa_util.choose_date('p_test', sysdate);
243 htp.p ('<SELECT name="p_sd_date">');
244 -- htp.FormSelectOption(' ');
245 FOR i IN 1..day_data.count LOOP
246 htp.FormSelectOption(day_data(i),
247 cattributes => ' value= '||day_data(i),
248 cselected => 'TRUE' );
249 END LOOP;
250 htp.FormSelectClose;
251 htp.p('-');
252
253 htp.p ('<SELECT name="p_sm_date">');
254 FOR i IN 1..12 LOOP
255 if i=1 then
256 --htp.FormSelectOption(lpad(to_char(i),2,'0'),
257 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
258 cattributes => ' value='||lpad(to_char(i),2,'0'),
259 cselected => 'TRUE' );
260 else
261 --htp.FormSelectOption(lpad(to_char(i),2,'0'),
262 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
263 cattributes => ' value='||lpad(to_char(i),2,'0'));
264 end if;
265 END LOOP;
266 htp.FormSelectClose;
267 htp.p('-');
268
269 htp.p ('<SELECT name="p_sy_date">');
270 -- htp.FormSelectOption(' ');
271 FOR i IN 1..year_data.count LOOP
272 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
273 htp.FormSelectOption(year_data(i),
274 cattributes => ' value= '||year_data(i),
275 cselected => 'TRUE');
276 ELSE
277 htp.FormSelectOption(year_data(i),
278 cattributes => ' value= '||year_data(i));
279 END IF;
280 END LOOP;
281 htp.FormSelectClose;
282 htp.p('</td>');
283 htp.tableRowClose;
284
285 htp.tableRowOpen();
286 htp.p('<td align="RIGHT" width="50%" valign="top">End Date</td>');
287 htp.p('<td>');
288 htp.p ('<SELECT name="p_ed_date">');
289 -- htp.FormSelectOption(' ');
290 FOR i IN 1..day_data.count LOOP
291 htp.FormSelectOption(day_data(i),
292 cattributes => ' value= '||day_data(i),
293 cselected => 'TRUE' );
294 END LOOP;
295 htp.FormSelectClose;
296 htp.p('-');
297
298 htp.p ('<SELECT name="p_em_date">');
299 FOR i IN 1..12 LOOP
300 if i=1 then
301 --htp.FormSelectOption(lpad(to_char(i),2,'0'),
302 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
303 cattributes => ' value='||lpad(to_char(i),2,'0'),
304 cselected => 'TRUE' );
305 else
306 --htp.FormSelectOption(lpad(to_char(i),2,'0'),
307 htp.FormSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
308 cattributes => ' value='||lpad(to_char(i),2,'0'));
309 end if;
310 END LOOP;
311 htp.FormSelectClose;
312 htp.p('-');
313
314 htp.p ('<SELECT name="p_ey_date">');
315 -- htp.FormSelectOption(' ');
316 FOR i IN 1..year_data.count LOOP
317 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
318 htp.FormSelectOption(year_data(i),
319 cattributes => ' value= '||year_data(i),
320 cselected => 'TRUE');
321 ELSE
322 htp.FormSelectOption(year_data(i),
323 cattributes => ' value= '||year_data(i));
324 END IF;
325 END LOOP;
326 htp.FormSelectClose;
327 htp.p('</td>');
328 htp.tableRowClose;
329
330 htp.tableRowOpen();
331 htp.p('<td align="RIGHT" width="50%" valign="top">Are these Close Dates or Creation Dates</td>');
332 htp.p('<td>');
333 htp.p ('<SELECT name="p_close_or_entry">');
334 htp.FormSelectOption('Creation Dates',
335 cattributes => ' value= '||'ENTRY');
336 htp.FormSelectOption('Close Dates',
337 cattributes => ' value= '||'CLOSE',
338 cselected => 'TRUE' );
339 htp.FormSelectClose;
340 htp.p('</td>');
341 htp.tableRowClose;
342
343 htp.tableRowOpen();
344 htp.p('<td align="RIGHT" width="50%" valign="top">Sales Group</td>');
345 htp.p('<td>');
346 htp.p ('<SELECT name="p_sgp">');
347 FOR rec_sales_group IN cur_sales_group(l_user_id) LOOP
348 htp.FormSelectOption(rec_sales_group.name,cattributes => ' value= '||rec_sales_group.sgi);
349 --htp.FormSelectOption(rec_sales_group.name,cattributes => ' value= '||7062);
350 END LOOP;
351 htp.FormSelectClose;
352 htp.p('</td>');
353 htp.tableRowClose;
354 --htp.p('<INPUT type=button value="Submit" onClick="populate1();return true" >');
355
356 htp.tableRowOpen();
357 htp.p('<td align="RIGHT" width="50%"valign="top">Reporting Currency</td>');
358 htp.p('<td>');
359 htp.p ('<SELECT name="p_crcy">');
360 FOR rec_currencies IN cur_currencies
361 LOOP
362 if rec_currencies.currency_code = v_usr_currency_code
363 then
364 htp.FormSelectOption(rec_currencies.currency_code, cselected => 'TRUE');
365 else
366 htp.FormSelectOption(rec_currencies.currency_code);
367 end if;
368 END LOOP;
369 htp.FormSelectClose;
370 htp.p('</td>');
371 htp.tableRowClose;
372 htp.tableClose;
373
374 htp.tableOpen(cattributes=>'border="0" width="80%"');
375 htp.tableRowOpen;
376 htp.p('<td align = "center" width="100%">');
377 htp.p('Groups and Totals');
378 htp.p('<input type="radio" value="NON-REPEATING" checked name="p_repeat_values">');
379 htp.p(' ');
380 htp.p('Repeating Values');
381 htp.p('<input type="radio" name="p_repeat_values" value="REPEATING">');
382 htp.p('</td>');
383 htp.tableRowClose;
384
385 htp.tableClose;
386 htp.Br;
387 htp.Br;
388 htp.Br;
389 footer;
390 htp.FormClose;
391 htp.bodyclose;
392 htp.htmlclose;
393 else
394 htp.p('Invalid session');
395 end if;
396
397 rollback;
398
399 exception
400 when others then
401 htp.p(SQLERRM);
402 END trans_deal_sizes_paramform;
403
404
405 procedure footer is
406 BEGIN
407 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
408 htp.htmlopen;
409 htp.tableRowOpen;
410 htp.tableData( htf.hr, crowspan => '1', ccolspan => '190', cnowrap => 'TRUE');
411 htp.tableRowClose;
412 htp.tableOpen( calign => 'center', cattributes => ' border=0 cellspacing=2 cellpadding=2' );
413 htp.tableRowOpen;
414 htp.formOpen( curl => l_agent||'ast_ofl_TRANS_SIZES_REPORT.report_wrapper', cmethod => 'post', cattributes => ' NAME="MyForm" TARGET="_top"');
415 --htp.tableData( htf.formSubmit( cvalue => 'OK', cattributes => ' onMouseOver="window.status=''OK'';return true"'));
416 htp.p('<td>');
417 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
418 htp.p('</td>');
419 --htp.tableData( '<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
420 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
421 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
422 htp.tableRowClose;
423 htp.tableClose;
424 htp.htmlClose;
425 END footer;
426 END;