[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_LEAD_ASSIGN_PARAM
Source
1 PACKAGE BODY ast_ofl_lead_assign_param
2 /* $Header: astrtlpb.pls 115.8 2002/02/05 17:27:38 pkm ship $ */
3 AS
4
5 /*
6 Date Remarks
7 ---------- ------------------------------------------------
8 05/30/2001 Created
9 */
10
11 TYPE day_table IS
12 TABLE OF VARCHAR2(2000)
13 INDEX BY BINARY_INTEGER;
14
15 TYPE year_table IS
16 TABLE OF VARCHAR2(2000)
17 INDEX BY BINARY_INTEGER;
18
19 g_image_prefix VARCHAR2(250) := '/OA_MEDIA/' || icx_sec.getid(icx_sec.pv_language_code) || '/';
20 l_user_id NUMBER;
21 v_date_time VARCHAR2(30);
22 l_agent VARCHAR2(200);
23 ctr1 INTEGER := 0;
24 ctr2 INTEGER := 0;
25 l_test owa_util.dateType;
26 day_data day_table;
27 year_data year_table;
28
29 -- variable for supporting multi-currency
30 v_usr_currency_code VARCHAR2(15);
31 --------------------------------------------------------------------------------
32 PROCEDURE header IS
33 BEGIN
34 SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY')
35 INTO v_date_time
36 FROM DUAL;
37
38 htp.htmlOpen;
39 htp.headOpen;
40 htp.title('Lead Assignment');
41 htp.headClose;
42 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
43 htp.tableOpen('border="0" ');
44 htp.tableRowOpen(cAlign => 'TOP');
45
46
47 htp.tableData('<FONT size=+1 face="times new roman">' || 'Lead Assignment Report', cNoWrap => 'TRUE');
48 htp.tableData(htf.bold(v_date_time), cAlign => 'right', cColSpan => '110');
49 htp.tableRowClose;
50 htp.tableClose;
51 htp.tableOpen(cAttributes => 'border=0 cellspacing=0 cellpadding=0 width=561');
52 htp.tableRowOpen(cVAlign => 'top');
53 htp.tableData(' ', cColSpan => '2', cAttributes => ' height=9');
54 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');
55 htp.tableData(' ', cColSpan => '6');
56 htp.br;
57 htp.tableRowClose;
58 htp.tableClose;
59 htp.bodyClose;
60 htp.headClose;
61 htp.htmlClose;
62 END;
63 --------------------------------------------------------------------------------
64 PROCEDURE lead_assign_param_form IS
65 day_counter NUMBER := 1;
66 year_counter NUMBER := 1990;
67 probability_counter NUMBER := 1;
68
69 CURSOR Cur_Sales_Group(P_Userid NUMBER) IS
70 SELECT grpd.group_id sgi
71 , DECODE(grpd.group_id
72 , grpd.parent_group_id
73 , DECODE(topgrp.manager_flag
74 , 'Y'
75 , grptl.group_name, ' *'||grptl.group_name
76 )
77 , DECODE(topgrp.manager_flag
78 , 'Y'
79 , DECODE(grpd.immediate_parent_flag
80 , 'Y'
81 , '-'||grptl.group_name, '--'||grptl.group_name
82 )
83 , DECODE(grpd.immediate_parent_flag
84 , 'Y'
85 , ' -'||grptl.group_name, ' --'||grptl.group_name
86 )
87 )
88 ) name
89 FROM Jtf_rs_groups_denorm grpd
90 , Jtf_rs_groups_tl grptl
91 , (SELECT DISTINCT grpb.group_id
92 , rrb2.manager_flag
93 FROM Jtf_rs_groups_b grpb
94 , Jtf_rs_role_relations rrel2
95 , Jtf_rs_roles_b rrb2
96 , Jtf_rs_resource_extns rsc2
97 , Jtf_rs_group_members mem
98 , Fnd_user fnu
99 WHERE grpb.group_id = mem.group_id
100 AND TRUNC(SYSDATE) BETWEEN grpb.start_date_active
101 AND NVL(grpb.end_date_active, TRUNC(SYSDATE))
102 AND rrb2.role_type_code IN ('SALES', 'TELESALES')
103 AND (rrb2.manager_flag = 'Y'
104 OR rrb2.admin_flag = 'Y')
105 AND rrel2.role_id = rrb2.role_id
106 AND TRUNC(SYSDATE) BETWEEN rrel2.start_date_active
107 AND NVL(rrel2.end_date_active, TRUNC(SYSDATE))
108 AND rrel2.role_resource_type = 'RS_GROUP_MEMBER'
109 AND rrel2.role_resource_id = mem.group_member_id
110 -- Begin Mod Raam on 02.27.2001
111 AND mem.delete_flag = 'N'
112 -- End Mod.
113 AND mem.resource_id = rsc2.resource_id
114 AND rsc2.source_id = fnu.employee_id
115 AND fnu.user_id = P_Userid) topgrp
116 WHERE grptl.group_id = grpd.group_id
117 AND grpd.parent_group_id = topgrp.group_id
118 AND TRUNC(SYSDATE) BETWEEN grpd.start_date_active
119 AND NVL(grpd.end_date_active, TRUNC(SYSDATE))
120 ORDER BY 2 DESC;
121
122 CURSOR cur_sales_rep(iUserID NUMBER) IS
123 select distinct source_id pid, source_name flname
124 from JTF_RS_RESOURCE_EXTNS
125 where source_id in (
126 select distinct b.person_id
127 from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a
128 where b.manager_person_id = a.source_id
129 and a.user_id = iUserID
130 );
131
132 /*
133 select distinct rsc.source_id pid,
134 rsc.source_name flname
135 from jtf_rs_resource_extns rsc,
136 jtf_rs_group_members gmem,
137 jtf_rs_role_relations rrel,
138 jtf_rs_roles_b rrb,
139 (select distinct grpd.group_id
140 from jtf_rs_groups_denorm grpd,
141 jtf_rs_role_relations rrel2,
142 jtf_rs_roles_b rrb2,
143 jtf_rs_resource_extns rsc2,
144 jtf_rs_group_members mem,
145 fnd_user fnu
146 where grpd.parent_group_id = mem.group_id
147 and nvl(grpd.end_date_active, trunc(sysdate)) >= trunc(sysdate)
148 and rrb2.role_type_code in ('SALES','TELESALES')
149 and (rrb2.admin_flag = 'Y' or rrb2.manager_flag = 'Y')
150 and rrel2.role_id = rrb2.role_id
151 and trunc(sysdate) between rrel2.start_date_active
152 and nvl(rrel2.end_date_active, trunc(sysdate))
153 and rrel2.role_resource_type = 'RS_GROUP_MEMBER'
154 and rrel2.role_resource_id = mem.group_member_id
155 and mem.resource_id = rsc2.resource_id
156 and mem.delete_flag='N'
157 and rsc2.source_id = fnu.employee_id
158 and fnu.user_id = fnd_global.user_id) grps
159 where gmem.group_id = grps.group_id
160 and rsc.resource_id = gmem.resource_id
161 and rrel.role_resource_id = gmem.group_member_id
162 and trunc(sysdate) between rrel.start_date_active
163 and nvl(rrel.end_date_active, trunc(sysdate))
164 and rrb.role_type_code in ('SALES','TELESALES')
165 and rrb.admin_flag = 'N'
166 and rrel.role_id = rrb.role_id
167 and rrel.role_resource_type = 'RS_GROUP_MEMBER'
168 and gmem.delete_flag='N'
169 UNION
170 select distinct rsc.source_id pid,
171 rsc.source_name flname
172 from jtf_rs_resource_extns rsc,
173 fnd_user fnu
174 where rsc.source_id = fnu.employee_id
175 and fnu.user_id = fnd_global.user_id
176 order by 2;
177 */
178
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');
204 htp.title('Lead Assignment Report');
201 htp.formOpen(owa_util.Get_Owa_Service_Path || 'AST_OFL_LEADASSIGN_RPT_PKG.lead_assign_rpt_wrapper', cAttributes => ' NAME="param"');
202 htp.htmlOpen;
203 htp.headOpen;
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 p_valid = true;');
214 htp.p(' if ((s_month == "3"&&s_day== "30")||(s_month == "5"&&s_day== "30")');
215 htp.p(' ||(s_month == "8"&&s_day== "30")||(s_month == "10"&&s_day== "30")){');
216 htp.p(' alert(''Start Date Must be a Valid Date'');');
217 htp.p(' p_valid = false;}');
218 htp.p(' if ((e_month == "3"&&e_day== "30")||(e_month == "5"&&e_day== "30")');
219 htp.p(' ||(e_month == "8"&&e_day== "30")||(e_month == "10"&&e_day== "30")){');
220 htp.p(' alert(''End Date Must be a Valid Date'');');
221 htp.p(' p_valid = false;}');
222 htp.p(' if (s_month == "1"){');
223 htp.p(' if ((s_year == "2"||s_year == "6"||s_year == "10"||s_year == "14"||s_year == "18")');
224 htp.p(' && (s_day == "29"||s_day == "30")){');
225 htp.p(' alert(''Start Date Must be a Valid Date'');');
226 htp.p(' p_valid = false;}');
227 htp.p(' }');
228 htp.p(' if (s_month == "1"){');
229 htp.p(' if ((s_year != "2"&&s_year != "6"&&s_year != "10"&&s_year != "14"&&s_year != "18")');
230 htp.p(' && (s_day == "28"||s_day == "29"||s_day == "30")){');
231 htp.p(' alert(''Start Date Must be a Valid Date'');');
232 htp.p(' p_valid = false;}');
233 htp.p(' }');
234 htp.p(' if (e_month == "1"){');
235 htp.p(' if ((e_year == "2"||e_year == "6"||e_year == "10"||e_year == "14"||e_year == "18")');
236 htp.p(' && (e_day == "29"||e_day == "30")){');
237 htp.p(' alert(''End Date Must be a Valid Date'');');
238 htp.p(' p_valid = false;}');
239 htp.p(' }');
240 htp.p(' if (e_month == "1"){');
241 htp.p(' if ((e_year != "2"&&e_year != "6"&&e_year != "10"&&e_year != "14"&&e_year != "18")');
242 htp.p(' && (e_day == "28"||e_day == "29"||e_day == "30")){');
243 htp.p(' alert(''End Date Must be a Valid Date'');');
244 htp.p(' p_valid = false;}');
245 htp.p(' }');
246 htp.p(' if (s_year > e_year){');
247 htp.p(' alert(''Start Date must be less than End Date'');');
248 htp.p(' p_valid = false;}');
249 htp.p(' if (s_year == e_year){');
250 htp.p(' if (s_month > e_month){');
251 htp.p(' alert(''Start Date must be less than End Date'');');
252 htp.p(' p_valid = false;}');
253 htp.p(' if (s_month == e_month){');
254 htp.p(' if (s_day > e_day){');
255 htp.p(' alert(''Start Date must be less than End Date'');');
256 htp.p(' p_valid = false;}');
257 htp.p(' }');
258 htp.p(' }');
259 htp.p(' if (p_valid){');
260 htp.p(' objform.submit();}');
261 htp.p('}');
262 htp.p('</script>');
263 htp.headClose;
264 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
265 htp.tableOpen;
266 htp.tableRowOpen(cVAlign => 'top');
267 htp.tableData(' ', cAttributes => ' height=9');
268 htp.tableData('<FONT size=2 face="Times New Roman">' || '</FONT>', cAlign => 'right', cRowSpan => '2', cColSpan => '3', cAttributes => ' width=154');
269 htp.tableData( ' ');
270 htp.tableRowClose;
271 htp.tableClose;
272 htp.tableOpen(cAttributes => 'width="600" ');
273 htp.tableRowOpen();
274 -- htp.p(L_USER_ID);
275 htp.p('<td align="RIGHT" width="50%"valign="top">Select Output Format</td>');
276 htp.p('<td>');
277 htp.p('<SELECT name="p_response">');
278 htp.formSelectOption('Excel');
279 htp.formSelectOption('HTML', cSelected => 'TRUE');
280 htp.formSelectClose;
281 htp.tableRowClose;
282 htp.tableRowClose;
283 htp.tableRowOpen();
284 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter Start Date</td>');
285 htp.p('<td>');
286 htp.p('<SELECT name="p_sd_date">');
287 FOR i IN 1..day_data.count LOOP
288 htp.formSelectOption(day_data(i),
289 cAttributes => ' value= ' || day_data(i),
290 cSelected => 'TRUE' );
291 END LOOP;
292 htp.formSelectClose;
293 htp.p('-');
294 htp.p('<SELECT name="p_sm_date">');
295 FOR i IN 1..12 LOOP
296 IF i = 1 THEN
300 ELSE
297 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
298 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'),
299 cSelected => 'TRUE');
301 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
302 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
303 END IF;
304 END LOOP;
305 htp.formSelectClose;
306 htp.p('-');
307 htp.p('<SELECT name="p_sy_date">');
308 FOR i IN 1..year_data.count LOOP
309 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
310 htp.formSelectOption(year_data(i),
311 cAttributes => ' value= ' || year_data(i),
312 cSelected => 'TRUE');
313 ELSE
314 htp.formSelectOption(year_data(i),
315 cAttributes => ' value= ' || year_data(i));
316 END IF;
317 END LOOP;
318 htp.formSelectClose;
319 htp.p('</td>');
320 htp.tableRowClose;
321 htp.tableRowOpen();
322 htp.p('<td align="RIGHT" width="50%" valign="top">Please Enter End Date</td>');
323 htp.p('<td>');
324 htp.p('<SELECT name="p_ed_date">');
325 FOR i IN 1..day_data.count LOOP
326 htp.formSelectOption(day_data(i),
327 cAttributes => ' value= ' || day_data(i),
328 cSelected => 'TRUE' );
329 END LOOP;
330 htp.formSelectClose;
331 htp.p('-');
332 htp.p('<SELECT name="p_em_date">');
333 FOR i IN 1..12 LOOP
334 IF i=1 THEN
335 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
336 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'),
337 cSelected => 'TRUE');
338 ELSE
339 htp.formSelectOption(TO_CHAR(TO_DATE(i, 'MM'), 'MON'),
340 cAttributes => ' value=' || LPAD(TO_CHAR(i), 2, '0'));
341 END IF;
342 END LOOP;
343 htp.formSelectClose;
344 htp.p('-');
345 htp.p ('<SELECT name="p_ey_date">');
346 FOR i IN 1..year_data.count LOOP
347 IF year_data(i) = TO_CHAR(SYSDATE, 'YYYY') THEN
348 htp.formSelectOption(year_data(i),
349 cAttributes => ' value= ' || year_data(i),
350 cSelected => 'TRUE' );
351 ELSE
352 htp.formSelectOption(year_data(i),
353 cAttributes => ' value= ' || year_data(i));
354 END IF;
355 END LOOP;
356 htp.formSelectClose;
357 htp.p('</td>');
358 htp.tableRowClose;
359 htp.tableRowOpen();
360 --htp.p('<td align="RIGHT" width="50%" valign="top">Are these Close Dates or Creation Dates</td>');
361 --htp.p('<td>');
362 --htp.p ('<SELECT name="p_close_or_entry">');
363 --htp.formSelectOption('Creation Dates',
364 -- cAttributes => ' value= ' || 'ENTRY');
365 --htp.formSelectOption('Close Dates',
366 -- cAttributes => ' value= ' || 'CLOSE',
367 -- cSelected => 'TRUE' );
368 --htp.formSelectClose;
369 --htp.p('</td>');
370 htp.tableRowClose;
371 --htp.tableRowOpen();
372 --htp.p('<td align="RIGHT" width="50%" valign="top">Sales Group</td>');
373 --htp.p('<td>');
374 --htp.p('<SELECT name="p_sgp">');
375 --FOR rec_sales_group IN cur_sales_group(l_user_id) LOOP
376 -- htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || rec_sales_group.sgi);
377 --htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || 7062);
378 --END LOOP;
379 --htp.formSelectClose;
380 --htp.p('</td>');
381 --htp.tableRowClose;
382 htp.tableRowOpen();
383 htp.p('<td align="RIGHT" width="32%" valign="top">Sales Rep</td>');
384 htp.p('<td>');
385 htp.p ('<SELECT name="p_srp">');
386 FOR rec_sales_rep IN cur_sales_rep(l_user_id) LOOP
387 IF rec_sales_rep.flname ='ALL' THEN
388 htp.formSelectOption(rec_sales_rep.flname, cAttributes => ' value= ' || rec_sales_rep.pid, cSelected => 'TRUE');
389 ELSE
390 htp.formSelectOption(rec_sales_rep.flname, cAttributes => ' value= ' || rec_sales_rep.pid);
391 END IF;
392 END LOOP;
393 htp.formSelectOption('ALL', cAttributes => ' value= -1');
394 htp.formSelectOption('All My Team', cAttributes => ' value= -2');
395 htp.formSelectClose;
396 htp.p('</td>');
397 htp.tableRowClose;
398 htp.tableClose;
399 htp.br;
400 htp.br;
401 htp.br;
402 footer;
403 htp.formClose;
404 htp.bodyClose;
405 htp.htmlClose;
406 rollback;
407 ELSE
408 htp.p('Invalid session');
409 END IF;
410 EXCEPTION
411 WHEN others THEN
412 htp.p(SQLERRM);
413 END lead_assign_param_form;
414 --------------------------------------------------------------------------------
415 PROCEDURE footer IS
416 BEGIN
417 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
418 htp.htmlOpen;
419 htp.tableRowOpen;
423 htp.tableRowOpen;
420 htp.tableData(htf.hr, cRowSpan => '1', cColSpan => '190', cNoWrap => 'TRUE');
421 htp.tableRowClose;
422 htp.tableOpen(cAlign => 'center', cAttributes => ' border=0 cellspacing=2 cellpadding=2');
424 htp.formOpen(cUrl => l_agent || 'ast_ofl_pipeline_buckets_rpt.report_wrapper', cMethod => 'post', cAttributes => ' NAME="MyForm" TARGET="_top"');
425 -- htp.tableData(htf.formSubmit(cValue => 'OK', cAttributes => ' onMouseOver="window.status=''OK'';return true"'));
426 htp.p('<td>');
427 htp.p('<INPUT TYPE="BUTTON" VALUE="OK" onClick="validateForm(document.param)">');
428 htp.p('</td>');
429 --htp.tableData('<INPUT type=button value="Cancel" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
430 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
431 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
432 htp.tableRowClose;
433 htp.tableClose;
434 htp.htmlClose;
435 END footer;
436 END;