[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_CHANGES_PARAM_PVT
Source
1 PACKAGE BODY JTF_TERR_CHANGES_PARAM_PVT AS
2 /* $Header: jtftrpcb.pls 120.0 2005/06/02 18:21:44 appldev 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 -- G_DATE_FORMAT varchar2(20) := as_ofl_util_pkg.get_date_format;
11
12
13 TYPE day_TABLE IS table of varchar2(2000) INDEX BY BINARY_INTEGER;
14 TYPE year_TABLE IS table of varchar2(2000) INDEX BY BINARY_INTEGER;
15
16 day_data day_table;
17 year_data year_table;
18
19
20 procedure header is
21
22 -----------------------------------------------------------------------------------------------
23 --
24 -- PROCEDURE: header
25 --
26 -- DESCRIPTION: This procedure is creates the descriptive header in the parameter form
27 --
28 -----------------------------------------------------------------------------------------------
29 begin
30 --select to_char(sysdate,G_DATE_FORMAT) into v_date_time from dual;
31 htp.htmlopen;
32 htp.headOpen;
33 htp.title('Territory Changes Report');
34 htp.p('<SCRIPT language="JavaScript">');
35 htp.p(' function validateForm(objform) {');
36 htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
37 htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
38 htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
39 htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
40 htp.p(' var e_month = objform.p_em_date.selectedIndex;');
41 htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
42 htp.p(' var p_valid = true;');
43 htp.p(' if ((s_month == "3"== "30")||(s_month == "5"== "30")');
44 htp.p(' ||(s_month == "8"== "30")||(s_month == "10"== "30")){');
45 htp.p(' alert(''Start Date Must be a Valid Date'');');
46 htp.p(' p_valid = false;}');
47 htp.p(' if ((e_month == "3"== "30")||(e_month == "5"== "30")');
48 htp.p(' ||(e_month == "8"== "30")||(e_month == "10"== "30")){');
49 htp.p(' alert(''End Date Must be a Valid Date'');');
50 htp.p(' p_valid = false;}');
51 htp.p(' if (s_month == "1"){');
52 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
53 htp.p(' (s_day == "29"||s_day == "30")){');
54 htp.p(' alert(''Start Date Must be a Valid Date'');');
55 htp.p(' p_valid = false;}');
56 htp.p(' }');
57 htp.p(' if (s_month == "1"){');
58 htp.p(' if ((s_year != "2" != "6" != "10" != "14" != "18")');
59 htp.p(' (s_day == "28"||s_day == "29"||s_day == "30")){');
60 htp.p(' alert(''Start Date Must be a Valid Date'');');
61 htp.p(' p_valid = false;}');
62 htp.p(' }');
63 htp.p(' if (e_month == "1"){');
64 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
65 htp.p(' (e_day == "29"||e_day == "30")){');
66 htp.p(' alert(''End Date Must be a Valid Date'');');
67 htp.p(' p_valid = false;}');
68 htp.p(' }');
69 htp.p(' if (e_month == "1"){');
70 htp.p(' if ((e_year != "2" != "6" != "10" != "14" != "18")');
71 htp.p(' (e_day == "28"||e_day == "29"||e_day == "30")){');
72 htp.p(' alert(''End Date Must be a Valid Date'');');
73 htp.p(' p_valid = false;}');
74 htp.p(' }');
75 htp.p(' if (s_year > e_year){');
76 htp.p(' alert(''Start Date must be less than End Date'');');
77 htp.p(' p_valid = false;}');
78 htp.p(' if (s_year == e_year){');
79 htp.p(' if (s_month > e_month){');
80 htp.p(' alert(''Start Date must be less than End Date'');');
81 htp.p(' p_valid = false;}');
82 htp.p(' if (s_month == e_month){');
83 htp.p(' if (s_day > e_day){');
84 htp.p(' alert(''Start Date must be less than End Date'');');
85 htp.p(' p_valid = false;}');
86 htp.p(' }');
87 htp.p(' }');
88 -- htp.p(' alert(''Start Date must be less than End Date'');');
89
90 htp.p(' if (p_valid){');
91 htp.p(' objform.submit();}');
92 htp.p('}');
93 htp.p('</script>');
94 htp.headClose;
95 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
96 htp.tableOpen('border="0" ');
97 htp.tableRowOpen( calign => 'TOP' );
98 -- htp.tableData( htf.img(curl=>g_image_prefix||'oppty.gif'));
99 htp.tableData( '<FONT size=+1 face="times new roman">' || 'Territory Changes Report', cnowrap => 'TRUE');
100 htp.tableData(htf.bold(v_date_time),calign => 'right',ccolspan => '110');
101 htp.tableRowClose;
102 htp.tableClose;
103 htp.tableOpen( cattributes => 'border=0 cellspacing=0 cellpadding=0 width=561' );
104 htp.tableRowOpen( cvalign => 'top' );
105 htp.tableData( ' ', ccolspan => '2', cattributes => ' height=9');
106 htp.tableData( '<FONT face="Times New Roman">' ||htf.bold( 'Please specify the criteria and select OK. ') ||
107 '</FONT>', calign => 'center', crowspan => '2', ccolspan => '110', cattributes => ' width=346');
108 htp.tableData( ' ', ccolspan => '6');
109 htp.Br;
110 htp.tableRowClose;
111 htp.tableClose;
112 htp.bodyClose;
113 htp.headClose;
114 htp.htmlClose;
115 end;
116 -----------------------------------------------------------------------------------------------
117 procedure terr_changes_paramform is
118
119 -----------------------------------------------------------------------------------------------
120 --
121 -- PROCEDURE: Territory Changes
122 --
123 -- DESCRIPTION: This procedure is main body of the parameter form
124 --
125 -----------------------------------------------------------------------------------------------
126 /*
127 cursor cur_get_manager is
128 select distinct ltrim(rtrim(ppf.last_name))||', ' ||ltrim(rtrim(ppf.first_name)) manager_name ,
129 ppf.person_id manager_person_id
130 from per_people_f ppf,
131 as_sales_groups asg
132 where asg.manager_person_id = ppf.person_id
133 and asg.enabled_flag = 'Y'
134 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
135 and trunc(sysdate)<= nvl(asg.end_date_active,trunc(sysdate) )
136 order by ltrim(rtrim(ppf.last_name))||', ' ||ltrim(rtrim(ppf.first_name)) ;
137 */
138 day_counter NUMBER := 1;
139 year_counter NUMBER := 1990;
140
141 BEGIN
142
143
144 FOR i IN 1..31 LOOP
145 if day_counter <= 9 then
146 day_data(i) := '0'||day_counter;
147 day_counter := day_counter +1;
148 else
149 day_data(i) := to_char(day_counter);
150 day_counter := day_counter +1;
151 end if;
152 END LOOP;
153
154 FOR i IN 1..21 LOOP
155 year_data(i) := to_char(year_counter);
156 year_counter := year_counter +1;
157 END LOOP;
158
159 if (icx_sec.validateSession(c_function_code => 'JTF_TERR_CHGS_RPT', c_validate_only => 'Y')) then
160 header;
161
162 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
163 -------------------- Returns login user Id--------------------------
164 htp.FormOpen(owa_util.Get_Owa_Service_Path||'JTF_TERR_changes_report_PVT.report_wrapper',
165 cattributes => ' NAME="param"');
166 htp.htmlopen;
167 htp.headOpen;
168 htp.title('Territory Changes Report');
169 htp.headClose;
170 htp.bodyopen(cattributes=>'bgcolor="#CCCCCC"');
171 htp.tableopen;
172 htp.tableRowOpen( cvalign => 'top' );
173 htp.tableData( ' ', cattributes => ' height=9');
174 htp.tableData( '<FONT size=2 face="Times New Roman">' || '</FONT>', calign => 'right', crowspan => '2', ccolspan => '3', cattributes => ' width=154');
175 htp.tableData( ' ');
176 htp.tableRowClose;
177 htp.tableClose;
178 htp.tableOpen(cattributes=>'width="600" ');
179 -- OUTPUT FORMAT FIELD
180 htp.tableRowOpen();
181 htp.p('<td align="RIGHT" width="50%"valign="top">Select Output Format</td>');
182 htp.p('<td>');
183 htp.p ('<SELECT name="p_response">');
184 htp.FormSelectOption('Excel');
185 htp.FormSelectOption('HTML',cselected => 'TRUE');
186 htp.FormSelectClose;
187 htp.tableRowClose;
188 /* -- MANAGER/GROUP FIELD
189
190 htp.tableRowOpen();
191 htp.p('<td align="RIGHT" width="50%" valign="top">Manager/Group</td>');
192 htp.p('<td>');
193 htp.p ('<SELECT name="p_manager" >');
194 FOR rec_get_manager IN cur_get_manager LOOP
195 htp.FormSelectOption(rec_get_manager.manager_name,cattributes => ' value= '||rec_get_manager.manager_person_id);
196 END LOOP;
197 htp.FormSelectClose;
198 htp.p('</td>');
199 htp.tableRowClose;
200 */
201 -- EARLISET CHANGE DATE FIELD
202 htp.tableRowOpen();
203 htp.p('<td align="RIGHT" width="32%" valign="top">Earliest Change Date</td>');
204 htp.p('<td>');
205 htp.p ('<SELECT name="p_sd_date">');
206 -- htp.FormSelectOption(' ');
207 FOR i IN 1..day_data.count LOOP
208 htp.FormSelectOption(day_data(i),
209 cattributes => ' value= '||day_data(i),
210 cselected => 'TRUE' );
211 END LOOP;
212 htp.FormSelectClose;
213 htp.p('-');
214
215 htp.p ('<SELECT name="p_sm_date">');
216 htp.FormSelectOption('JAN',cselected => 'TRUE');
217 htp.FormSelectOption('FEB');
218 htp.FormSelectOption('MAR');
219 htp.FormSelectOption('APR');
220 htp.FormSelectOption('MAY');
221 htp.FormSelectOption('JUN');
222 htp.FormSelectOption('JUL');
223 htp.FormSelectOption('AUG');
224 htp.FormSelectOption('SEP');
225 htp.FormSelectOption('OCT');
226 htp.FormSelectOption('NOV');
227 htp.FormSelectOption('DEC');
228 htp.FormSelectClose;
229 htp.p('-');
230 htp.p ('<SELECT name="p_sy_date">');
231 -- htp.FormSelectOption(' ');
232 FOR i IN 1..year_data.count LOOP
233 htp.FormSelectOption(year_data(i),
234 cattributes => ' value= '||year_data(i));
235 END LOOP;
236 htp.FormSelectClose;
237 htp.p('</td>');
238 htp.tableRowClose;
239
240 -- LATEST CHANGE DATE FIELD
241 htp.tableRowOpen();
242 htp.p('<td align="RIGHT" width="32%" valign="top">Latest Change Date</td>');
243 htp.p('<td>');
244 htp.p ('<SELECT name="p_ed_date">');
245 -- htp.FormSelectOption(' ');
246 FOR i IN 1..day_data.count LOOP
247 htp.FormSelectOption(day_data(i),
248 cattributes => ' value= '||day_data(i),
249 cselected => 'TRUE' );
250 END LOOP;
251 htp.FormSelectClose;
252 htp.p('-');
253
254 htp.p ('<SELECT name="p_em_date">');
255 htp.FormSelectOption('JAN',cselected => 'TRUE');
256 htp.FormSelectOption('FEB');
257 htp.FormSelectOption('MAR');
258 htp.FormSelectOption('APR');
259 htp.FormSelectOption('MAY');
260 htp.FormSelectOption('JUN');
261 htp.FormSelectOption('JUL');
262 htp.FormSelectOption('AUG');
263 htp.FormSelectOption('SEP');
264 htp.FormSelectOption('OCT');
265 htp.FormSelectOption('NOV');
266 htp.FormSelectOption('DEC');
267 htp.FormSelectClose;
268 htp.p('-');
269
270 htp.p ('<SELECT name="p_ey_date">');
271 -- htp.FormSelectOption(' ');
272 FOR i IN 1..year_data.count LOOP
273 htp.FormSelectOption(year_data(i),
274 cattributes => ' value= '||year_data(i));
275 END LOOP;
276 htp.FormSelectClose;
277 htp.p('</td>');
278 htp.tableRowClose;
279
280 htp.tableClose;
281 htp.Br;
282 htp.Br;
283 htp.Br;
284 footer;
285 htp.FormClose;
286 htp.bodyclose;
287 htp.htmlclose;
288 else
289 htp.p('Invalid session');
290 end if;
291 exception
292 when others then
293 htp.p(SQLERRM);
294 END terr_changes_paramform;
295
296 procedure footer is
297 BEGIN
298 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
299 htp.htmlopen;
300 htp.tableRowOpen;
301 htp.tableData( htf.hr, crowspan => '1', ccolspan => '190', cnowrap => 'TRUE');
302 htp.tableRowClose;
303 htp.tableOpen( calign => 'center', cattributes => ' border=0 cellspacing=2 cellpadding=2' );
304 htp.tableRowOpen;
305 htp.formOpen( curl => l_agent||'/JTF_TERR_CHANGES_report_PVT.report_wrapper', cmethod => 'GET',
306 cattributes => ' NAME="MyForm" TARGET="_top"');
307
308 htp.p('<td>');
309 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
310 htp.p('</td>');
311 htp.tableData( '<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
312 htp.tableRowClose;
313 htp.tableClose;
314 htp.htmlClose;
315 END footer;
316 END;