[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_PIPELINE_BUCKETS_PARAM
Source
4 /*
1 PACKAGE BODY ast_ofl_pipeline_buckets_param AS
2 /* $Header: astrtbpb.pls 115.21 2002/02/06 13:11:30 pkm ship $ */
3
5 Date Remarks
6 ---------- ------------------------------------------------
7 13/12/2000 Imaged is removed from the report as there are no
8 images in the media directory.
9 All fnd_profile.value_wnps are replace with fnd
10 profile.value function.
11 JTF_Profile_default_currency is used here instead
12 of as_dafault_currency to get the the currency
13 code.
14 Parameters Start date and End Date month was
15 display format is changed from MM to MON.
16 Start and End Year is default to current year.
17 Report Date format is changed from US type to
18 DD-MON-YYYY
19 Sales Group cursor is modified to fetch the groups
20 which does not have parent or children.
21 03/01/2001 Sales Group query is modified revert the chage
22 made on 13/12/2000. The above change created
23 another problem of showing the same sales group
24 twice for some users. This condition commented
28 02/14/2001 Cursor Sales Group SELECT is modified as per Thanh
25 out now.
26 01/24/2001 Also all the SQL hints should be modified to have a space
27 after the + sign.
29 Huynh(02/11). The new statement looks very small and neat.
30 Refer the previous version for old SQL.
31 02/27/2001 Thanh adviced us to check for delete flag = 'N' while
32 using group members table in order to pickup only the
33 valid members.
34 */
35
36 TYPE day_table IS
37 TABLE OF VARCHAR2(2000)
38 INDEX BY BINARY_INTEGER;
39
40 TYPE year_table IS
41 TABLE OF VARCHAR2(2000)
42 INDEX BY BINARY_INTEGER;
43
44 g_image_prefix VARCHAR2(250) := '/OA_MEDIA/' || icx_sec.getid(icx_sec.pv_language_code) || '/';
45 l_user_id NUMBER;
46 v_date_time VARCHAR2(30);
47 l_agent VARCHAR2(200);
48 ctr1 INTEGER := 0;
49 ctr2 INTEGER := 0;
50 l_test owa_util.dateType;
51 day_data day_table;
52 year_data year_table;
53
54 -- variable for supporting multi-currency
55 v_usr_currency_code VARCHAR2(15);
56 --------------------------------------------------------------------------------
57 PROCEDURE header IS
58 BEGIN
59 -- Begin Mod. RAAM 07/12/2000
60 -- Thanh asked to display date in US format instead of British format.
61 --SELECT fnd_date.date_to_chardate(SYSDATE)
62 -- End Mod.
63 SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY')
64 INTO v_date_time
65 FROM DUAL;
66
67 htp.htmlOpen;
68 htp.headOpen;
69 htp.title('Pipeline Buckets by Range');
70 htp.headClose;
71 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
72 htp.tableOpen('border="0" ');
73 htp.tableRowOpen(cAlign => 'TOP');
74
75 -- Begin Mod. Raam on 13/12/2000
76 -- Images are not available at the media directory.
77 -- Once it is available the follwing code should be
78 -- uncommented.
79 --htp.tableData(htf.img(cUrl => g_image_prefix || 'oppty.gif'));
80 -- End Mod.
81
82 htp.tableData('<FONT size=+1 face="times new roman">' || 'Pipeline Buckets by Range Report', cNoWrap => 'TRUE');
83 htp.tableData(htf.bold(v_date_time), cAlign => 'right', cColSpan => '110');
84 htp.tableRowClose;
85 htp.tableClose;
86 htp.tableOpen(cAttributes => 'border=0 cellspacing=0 cellpadding=0 width=561');
87 htp.tableRowOpen(cVAlign => 'top');
88 htp.tableData(' ', cColSpan => '2', cAttributes => ' height=9');
89 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');
90 htp.tableData(' ', cColSpan => '6');
91 htp.br;
92 htp.tableRowClose;
93 htp.tableClose;
94 htp.bodyClose;
95 htp.headClose;
96 htp.htmlClose;
97 END;
98 --------------------------------------------------------------------------------
99 PROCEDURE pipeline_buckets_paramform IS
100 day_counter NUMBER := 1;
101 year_counter NUMBER := 1990;
102 probability_counter NUMBER := 1;
103 CURSOR prob_cursor
104 IS
105 select probability_value win_probability
106 from as_forecast_prob
107 where UPPER(enabled_flag) = 'Y'
108 and trunc(SYSDATE) between nvl(start_date_active, trunc(SYSDATE))
109 and nvl(end_date_active, trunc(SYSDATE))
110 order by 1;
111
112 CURSOR intern_process_cursor
113 IS
114 select lookup_code
115 from fnd_lookup_values
116 where lookup_type='AST_INTERNAL_PROCESS_STATUS'
117 and enabled_flag='Y';
118
119 CURSOR Cur_Sales_Group(P_Userid NUMBER) IS
120 SELECT grpd.group_id sgi
121 , DECODE(grpd.group_id
122 , grpd.parent_group_id
123 , DECODE(topgrp.manager_flag
124 , 'Y'
125 , grptl.group_name, ' *'||grptl.group_name
126 )
127 , DECODE(topgrp.manager_flag
128 , 'Y'
129 , DECODE(grpd.immediate_parent_flag
130 , 'Y'
131 , '-'||grptl.group_name, '--'||grptl.group_name
132 )
133 , DECODE(grpd.immediate_parent_flag
134 , 'Y'
135 , ' -'||grptl.group_name, ' --'||grptl.group_name
136 )
137 )
138 ) name
139 FROM Jtf_rs_groups_denorm grpd
140 , Jtf_rs_groups_tl grptl
141 , (SELECT DISTINCT grpb.group_id
142 , rrb2.manager_flag
143 FROM Jtf_rs_groups_b grpb
144 , Jtf_rs_role_relations rrel2
145 , Jtf_rs_roles_b rrb2
146 , Jtf_rs_resource_extns rsc2
147 , Jtf_rs_group_members mem
148 , Fnd_user fnu
149 WHERE grpb.group_id = mem.group_id
150 AND TRUNC(SYSDATE) BETWEEN grpb.start_date_active
151 AND NVL(grpb.end_date_active, TRUNC(SYSDATE))
152 AND rrb2.role_type_code IN ('SALES', 'TELESALES')
153 AND (rrb2.manager_flag = 'Y'
154 OR rrb2.admin_flag = 'Y')
155 AND rrel2.role_id = rrb2.role_id
156 AND TRUNC(SYSDATE) BETWEEN rrel2.start_date_active
162 -- End Mod.
157 AND NVL(rrel2.end_date_active, TRUNC(SYSDATE))
158 AND rrel2.role_resource_type = 'RS_GROUP_MEMBER'
159 AND rrel2.role_resource_id = mem.group_member_id
160 -- Begin Mod Raam on 02.27.2001
161 AND mem.delete_flag = 'N'
163 AND mem.resource_id = rsc2.resource_id
164 AND rsc2.source_id = fnu.employee_id
165 AND fnu.user_id = P_Userid) topgrp
166 WHERE grptl.group_id = grpd.group_id
167 AND grpd.parent_group_id = topgrp.group_id
168 AND TRUNC(SYSDATE) BETWEEN grpd.start_date_active
169 AND NVL(grpd.end_date_active, TRUNC(SYSDATE))
170 ORDER BY 2 DESC;
171
172 CURSOR cur_currencies IS
173 SELECT currency_code
174 FROM Fnd_currencies
175 WHERE UPPER(enabled_flag) = 'Y'
176 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
177 AND NVL(end_date_active, SYSDATE)
178 ORDER BY 1;
179 BEGIN
180 FOR i IN 1..31 LOOP
181 IF day_counter <= 9 THEN
182 day_data(i) := '0' || day_counter;
183 day_counter := day_counter + 1;
184 ELSE
185 day_data(i) := TO_CHAR(day_counter);
186 day_counter := day_counter + 1;
187 END IF;
188 END LOOP;
189
190 FOR i IN 1..21 LOOP
191 year_data(i) := TO_CHAR(year_counter);
192 year_counter := year_counter + 1;
193 END LOOP;
194
195 IF (icx_sec.validateSession) THEN
196 header;
197 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
198 -- Returns login user Id
199 -- set user's currency code
200 v_usr_currency_code := FND_PROFILE.Value('JTF_PROFILE_DEFAULT_CURRENCY');
201 htp.formOpen(owa_util.Get_Owa_Service_Path || 'ast_ofl_pipeline_buckets_rpt.report_wrapper', cAttributes => ' NAME="param"');
202 htp.htmlOpen;
203 htp.headOpen;
204 htp.title('Pipeline Buckets by Range Report');
205 htp.p('<SCRIPT language="JavaScript">');
206 htp.p(' function validateForm(objform) {');
207 htp.p(' var s_day = objform.p_sd_date.selectedIndex;');
208 htp.p(' var s_month = objform.p_sm_date.selectedIndex;');
209 htp.p(' var s_year = objform.p_sy_date.selectedIndex;');
210 htp.p(' var e_day = objform.p_ed_date.selectedIndex;');
211 htp.p(' var e_month = objform.p_em_date.selectedIndex;');
212 htp.p(' var e_year = objform.p_ey_date.selectedIndex;');
213 htp.p(' var low_prob = objform.p_low_prob.selectedIndex;');
214 htp.p(' var high_prob = objform.p_high_prob.selectedIndex;');
215 htp.p(' var p_valid = true;');
216 htp.p(' if ((s_month == "3"&&s_day== "30")||(s_month == "5"&&s_day== "30")');
217 htp.p(' ||(s_month == "8"&&s_day== "30")||(s_month == "10"&&s_day== "30")){');
218 htp.p(' alert(''Start Date Must be a Valid Date'');');
219 htp.p(' p_valid = false;}');
220 htp.p(' if ((e_month == "3"&&e_day== "30")||(e_month == "5"&&e_day== "30")');
221 htp.p(' ||(e_month == "8"&&e_day== "30")||(e_month == "10"&&e_day== "30")){');
222 htp.p(' alert(''End Date Must be a Valid Date'');');
223 htp.p(' p_valid = false;}');
224 htp.p(' if (s_month == "1"){');
225 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
226 htp.p(' && (s_day == "29"||s_day == "30")){');
227 htp.p(' alert(''Start Date Must be a Valid Date'');');
228 htp.p(' p_valid = false;}');
229 htp.p(' }');
230 htp.p(' if (s_month == "1"){');
231 htp.p(' if ((s_year != "2"&&s_year != "6"&&s_year != "10"&&s_year != "14"&&s_year != "18")');
232 htp.p(' && (s_day == "28"||s_day == "29"||s_day == "30")){');
233 htp.p(' alert(''Start Date Must be a Valid Date'');');
234 htp.p(' p_valid = false;}');
235 htp.p(' }');
236 htp.p(' if (e_month == "1"){');
237 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
238 htp.p(' && (e_day == "29"||e_day == "30")){');
239 htp.p(' alert(''End Date Must be a Valid Date'');');
240 htp.p(' p_valid = false;}');
241 htp.p(' }');
242 htp.p(' if (e_month == "1"){');
243 htp.p(' if ((e_year != "2"&&e_year != "6"&&e_year != "10"&&e_year != "14"&&e_year != "18")');
244 htp.p(' && (e_day == "28"||e_day == "29"||e_day == "30")){');
245 htp.p(' alert(''End Date Must be a Valid Date'');');
246 htp.p(' p_valid = false;}');
247 htp.p(' }');
248 htp.p(' if (s_year > e_year){');
249 htp.p(' alert(''Start Date must be less than End Date'');');
250 htp.p(' p_valid = false;}');
251 htp.p(' if (s_year == e_year){');
252 htp.p(' if (s_month > e_month){');
253 htp.p(' alert(''Start Date must be less than End Date'');');
254 htp.p(' p_valid = false;}');
255 htp.p(' if (s_month == e_month){');
256 htp.p(' if (s_day > e_day){');
257 htp.p(' alert(''Start Date must be less than End Date'');');
258 htp.p(' p_valid = false;}');
259 htp.p(' }');
260 htp.p(' }');
264 htp.p(' if (p_valid){');
261 htp.p(' if (low_prob > high_prob){');
262 htp.p(' alert(''Low probability must be less than High probability'');');
263 htp.p(' p_valid = false;}');
265 htp.p(' objform.submit();}');
266 htp.p('}');
267 htp.p('</script>');
268 htp.headClose;
269 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
270 htp.tableOpen;
271 htp.tableRowOpen(cVAlign => 'top');
272 htp.tableData(' ', cAttributes => ' height=9');
273 htp.tableData('<FONT size=2 face="Times New Roman">' || '</FONT>', cAlign => 'right', cRowSpan => '2', cColSpan => '3', cAttributes => ' width=154');
274 htp.tableData( ' ');
275 htp.tableRowClose;
276 htp.tableClose;
277 htp.tableOpen(cAttributes => 'width="600" ');
278 htp.tableRowOpen();
279 -- htp.p(L_USER_ID);
280 htp.p('<td align="RIGHT" width="50%"valign="top">Select Output Format</td>');
281 htp.p('<td>');
282 htp.p('<SELECT name="p_response">');
283 htp.formSelectOption('Excel');
284 htp.formSelectOption('HTML', cSelected => 'TRUE');
285 htp.formSelectClose;
286 htp.tableRowClose;
287 htp.tableRowClose;
288 htp.tableRowOpen();
289 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter Start Date</td>');
290 htp.p('<td>');
291 htp.p('<SELECT name="p_sd_date">');
292 FOR i IN 1..day_data.count LOOP
293 htp.formSelectOption(day_data(i),
294 cAttributes => ' value= ' || day_data(i),
295 cSelected => 'TRUE' );
296 END LOOP;
297 htp.formSelectClose;
298 htp.p('-');
299 htp.p('<SELECT name="p_sm_date">');
300 FOR i IN 1..12 LOOP
301 IF i = 1 THEN
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(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
307 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
308 END IF;
309 END LOOP;
310 htp.formSelectClose;
311 htp.p('-');
312 htp.p('<SELECT name="p_sy_date">');
313 FOR i IN 1..year_data.count LOOP
314 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
315 htp.formSelectOption(year_data(i),
316 cAttributes => ' value= ' || year_data(i),
317 cSelected => 'TRUE');
318 ELSE
319 htp.formSelectOption(year_data(i),
320 cAttributes => ' value= ' || year_data(i));
321 END IF;
322 END LOOP;
323 htp.formSelectClose;
324 htp.p('</td>');
325 htp.tableRowClose;
326 htp.tableRowOpen();
327 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter End Date</td>');
328 htp.p('<td>');
329 htp.p('<SELECT name="p_ed_date">');
330 FOR i IN 1..day_data.count LOOP
331 htp.formSelectOption(day_data(i),
332 cAttributes => ' value= ' || day_data(i),
333 cSelected => 'TRUE' );
334 END LOOP;
335 htp.formSelectClose;
336 htp.p('-');
337 htp.p('<SELECT name="p_em_date">');
338 FOR i IN 1..12 LOOP
339 IF i=1 THEN
340 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
341 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'),
342 cSelected => 'TRUE');
343 ELSE
344 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
345 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
346 END IF;
347 END LOOP;
348 htp.formSelectClose;
349 htp.p('-');
350 htp.p ('<SELECT name="p_ey_date">');
351 FOR i IN 1..year_data.count LOOP
352 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
353 htp.formSelectOption(year_data(i),
354 cAttributes => ' value= ' || year_data(i),
355 cSelected => 'TRUE' );
356 ELSE
357 htp.formSelectOption(year_data(i),
358 cAttributes => ' value= ' || year_data(i));
359 END IF;
360 END LOOP;
361 htp.formSelectClose;
362 htp.p('</td>');
363 htp.tableRowClose;
364 htp.tableRowOpen();
365 htp.p('<td align="RIGHT" width="50%" valign="top">Are these Close Dates or Creation Dates</td>');
366 htp.p('<td>');
367 htp.p ('<SELECT name="p_close_or_entry">');
368 htp.formSelectOption('Creation Dates',
369 cAttributes => ' value= ' || 'ENTRY');
370 htp.formSelectOption('Close Dates',
371 cAttributes => ' value= ' || 'CLOSE',
372 cSelected => 'TRUE' );
373 htp.formSelectClose;
374 htp.p('</td>');
375 htp.tableRowClose;
376 htp.tableRowOpen();
377 htp.p('<td align="RIGHT" width="50%" valign="top">Sales Group</td>');
378 htp.p('<td>');
379 htp.p('<SELECT name="p_sgp">');
380 FOR rec_sales_group IN cur_sales_group(l_user_id) LOOP
381 htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || rec_sales_group.sgi);
382 -- htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || 7062);
383 END LOOP;
384 htp.formSelectClose;
385 htp.p('</td>');
386 htp.tableRowClose;
387 -- htp.tableRowOpen();
388 -- htp.p('<td align="RIGHT" width="32%" valign="top">Sales Rep</td>');
389 -- htp.p('<td>');
390 -- htp.p ('<SELECT name="p_srp">');
391 -- FOR rec_sales_rep IN cur_sales_rep LOOP
392 -- IF rec_sales_rep.flname ='ALL' THEN
393 -- htp.formSelectOption(rec_sales_rep.flname, cAttributes => ' value= ' || rec_sales_rep.pid, cSelected => 'TRUE');
394 -- ELSE
395 -- htp.formSelectOption(rec_sales_rep.flname, cAttributes => ' value= ' || rec_sales_rep.pid);
396 -- END IF;
397 -- END LOOP;
398 -- htp.formSelectClose;
402 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter Low probability</td>');
399 -- htp.p('</td>');
400 -- htp.tableRowClose;
401 htp.tableRowOpen();
403 htp.p('<td>');
404 htp.p('<SELECT name="p_low_prob">');
405 htp.formSelectOption(' ');
406 FOR cur_rec IN prob_cursor LOOP
407 htp.formSelectOption(cur_rec.win_probability, cAttributes => ' value= ' || cur_rec.win_probability);
408 END LOOP;
409 htp.formSelectClose;
410 htp.p('</td>');
411 htp.tableRowClose;
412 htp.tableRowOpen();
413 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter High probability</td>');
414 htp.p('<td>');
415 htp.p('<SELECT name="p_high_prob">');
416 htp.formSelectOption(' ');
417 FOR cur_rec IN prob_cursor LOOP
418 htp.formSelectOption(cur_rec.win_probability, cAttributes => ' value= ' || cur_rec.win_probability);
419 END LOOP;
420 htp.formSelectClose;
421 htp.p('</td>');
422 htp.tableRowClose;
423 htp.tableRowOpen();
424 htp.p('<td align="RIGHT" width="50%" valign="top">Internal Process Status</td>');
425 htp.p('<td>');
426 htp.p('<SELECT name="p_process_status">');
427 htp.formSelectOption(' ');
428 FOR cur_rec IN intern_process_cursor LOOP
429 htp.formSelectOption(cur_rec.lookup_code, cAttributes => ' value= ' || cur_rec.lookup_code);
430 END LOOP;
431 htp.formSelectClose;
432 htp.p('</td>');
433 htp.tableRowClose;
434 htp.tableRowOpen();
435 htp.br;
436 htp.p('<td align="RIGHT" width="50%"valign="top">Reporting Currency</td>');
437 htp.p('<td>');
438 htp.p('<SELECT name="p_crcy">');
439 FOR rec_currencies IN cur_currencies LOOP
440 IF rec_currencies.currency_code = v_usr_currency_code THEN
441 htp.formSelectOption(rec_currencies.currency_code, cSelected => 'TRUE');
442 ELSE
443 htp.formSelectOption(rec_currencies.currency_code);
444 END IF;
445 END LOOP;
446 htp.formSelectClose;
447 htp.p('</td>');
448 htp.tableRowClose;
449 htp.tableClose;
450 htp.tableOpen(cAttributes => 'border="0" width="80%"');
451 htp.tableRowOpen;
452 htp.p('<td align = "center" width="100%">');
453 htp.p('Groups and Totals');
454 htp.p('<input type="radio" value="NON-REPEATING" checked name="p_repeat_values">');
455 htp.p(' ');
456 htp.p('Repeating Values');
457 htp.p('<input type="radio" name="p_repeat_values" value="REPEATING">');
458 htp.p('</td>');
459 htp.tableRowClose;
460 htp.tableClose;
461 htp.br;
462 htp.br;
463 htp.br;
464 footer;
465 htp.formClose;
466 htp.bodyClose;
467 htp.htmlClose;
468 rollback;
469 ELSE
470 htp.p('Invalid session');
471 END IF;
472 EXCEPTION
473 WHEN others THEN
474 htp.p(SQLERRM);
475 END pipeline_buckets_paramform;
476 --------------------------------------------------------------------------------
477 PROCEDURE footer IS
478 BEGIN
479 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
480 htp.htmlOpen;
481 htp.tableRowOpen;
482 htp.tableData(htf.hr, cRowSpan => '1', cColSpan => '190', cNoWrap => 'TRUE');
483 htp.tableRowClose;
484 htp.tableOpen(cAlign => 'center', cAttributes => ' border=0 cellspacing=2 cellpadding=2');
485 htp.tableRowOpen;
486 htp.formOpen(cUrl => l_agent || 'ast_ofl_pipeline_buckets_rpt.report_wrapper', cMethod => 'post', cAttributes => ' NAME="MyForm" TARGET="_top"');
487 -- htp.tableData(htf.formSubmit(cValue => 'OK', cAttributes => ' onMouseOver="window.status=''OK'';return true"'));
488 htp.p('<td>');
489 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
490 htp.p('</td>');
491 --htp.tableData('<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
492 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
493 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
494 htp.tableRowClose;
495 htp.tableClose;
496 htp.htmlClose;
497 END footer;
498 END;