[Home] [Help]
PACKAGE BODY: APPS.AST_OFL_ACCESSES_REPORT
Source
1 PACKAGE BODY AST_OFL_ACCESSES_REPORT
2 /* $Header: astrtacb.pls 115.10 2002/02/06 12:33:03 pkm ship $ */
3 AS
4 g_image_prefix VARCHAR2(250) := '/OA_MEDIA/' || icx_sec.getid(icx_sec.pv_language_code) || '/';
5 l_agent VARCHAR2(200);
6 l_sgrp_name VARCHAR2(60);
7 l_user_id NUMBER;
8 l_groups_found BOOLEAN; --Added by Thanh Huynh 01/27/01
9 l_flname VARCHAR2(60);
10 l_pid NUMBER;
11 v_date_time VARCHAR2(30);
12 v_sales_group_id NUMBER := NULL; --Variables for sales group & rep
13 v_salesrep_id NUMBER := NULL;
14 v_tab VARCHAR2(1);
15 v_tabrow VARCHAR2(2000);
16 ----------------------------------------------------------------------------------------------------
17
18 PROCEDURE header
19 IS
20 BEGIN
21 SELECT to_char(SYSDATE,'DD-MON-YYYY')
22 INTO v_date_time
23 FROM dual;
24
25 htp.htmlOpen;
26 htp.headOpen;
27 htp.title('Accesses Report');
28 htp.headClose;
29 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
30 htp.tableOpen();
31 htp.tableRowOpen(cAlign => 'TOP');
32 htp.tableData( '<FONT size=+1 face="Times New Roman">' || 'Accesses Report', cnowrap => 'TRUE');
33 htp.tableData(htf.bold(v_date_time), cAlign => 'right', cColSpan => '110');
34 htp.tableRowClose;
35 htp.tableClose;
36 htp.tableOpen(cAttributes => ' border=0 cellspacing=0 cellpadding=0 width=561' );
37 htp.tableRowOpen(cVAlign => 'top' );
38 htp.tableData(' ', cColSpan => '2', cAttributes => ' height=9');
39 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');
40 htp.tableData(' ', cColSpan => '6');
41 htp.br;
42 htp.tableRowClose;
43 htp.tableClose;
44 htp.bodyClose;
45 htp.headClose;
46 htp.htmlClose;
47 END;
48
49 ----------------------------------------------------------------------------------------------------
50 PROCEDURE accesses_paramform
51 IS
52
53
54 --for sales group
55
56 CURSOR cur_sales_group(p_userid NUMBER) IS
57 select grpd.group_id sgi,
58 decode(grpd.group_id, grpd.parent_group_id,
59 decode(topgrp.manager_flag, 'Y', grptl.group_name,
60 ' *'||grptl.group_name),
61 decode(topgrp.manager_flag, 'Y',
62 decode(grpd.immediate_parent_flag, 'Y',
63 '-'||grptl.group_name, '--'||grptl.group_name),
64 decode(grpd.immediate_parent_flag, 'Y',
65 ' -'||grptl.group_name, ' --'||grptl.group_name))) name
66 from jtf_rs_groups_denorm grpd,
67 jtf_rs_groups_tl grptl,
68 (select distinct grpb.group_id, rrb2.manager_flag
69 from jtf_rs_groups_b grpb,
70 jtf_rs_role_relations rrel2,
71 jtf_rs_roles_b rrb2,
72 jtf_rs_resource_extns rsc2,
73 jtf_rs_group_members mem,
74 fnd_user fnu
75 where grpb.group_id = mem.group_id
76 and trunc(sysdate) between grpb.start_date_active
77 and nvl(grpb.end_date_active, trunc(sysdate))
78 and rrb2.role_type_code in ('SALES','TELESALES')
79 and (rrb2.manager_flag = 'Y' or rrb2.admin_flag = 'Y')
80 and rrel2.role_id = rrb2.role_id
81 and trunc(sysdate) between rrel2.start_date_active
82 and nvl(rrel2.end_date_active, trunc(sysdate))
83 and rrel2.role_resource_type = 'RS_GROUP_MEMBER'
84 and rrel2.role_resource_id = mem.group_member_id
85 and mem.resource_id = rsc2.resource_id
86 and mem.delete_flag='N'
87 and rsc2.source_id = fnu.employee_id
88 and fnu.user_id = p_userid) topgrp
89 where grptl.group_id = grpd.group_id
90 and grpd.parent_group_id = topgrp.group_id
91 and trunc(sysdate) between grpd.start_date_active
92 and nvl(grpd.end_date_active, trunc(sysdate))
93 order by 2 desc;
94
95 --for sales rep
96
97 CURSOR cur_sales_rep IS
98 select distinct rsc.source_id pid,
99 rsc.source_name flname
100 from jtf_rs_resource_extns rsc,
101 jtf_rs_group_members gmem,
102 jtf_rs_role_relations rrel,
103 jtf_rs_roles_b rrb,
104 (select distinct grpd.group_id
105 from jtf_rs_groups_denorm grpd,
106 jtf_rs_role_relations rrel2,
107 jtf_rs_roles_b rrb2,
108 jtf_rs_resource_extns rsc2,
109 jtf_rs_group_members mem,
110 fnd_user fnu
111 where grpd.parent_group_id = mem.group_id
112 and nvl(grpd.end_date_active, trunc(sysdate)) >= trunc(sysdate)
113 and rrb2.role_type_code in ('SALES','TELESALES')
114 and (rrb2.admin_flag = 'Y' or rrb2.manager_flag = 'Y')
115 and rrel2.role_id = rrb2.role_id
116 and trunc(sysdate) between rrel2.start_date_active
117 and nvl(rrel2.end_date_active, trunc(sysdate))
118 and rrel2.role_resource_type = 'RS_GROUP_MEMBER'
119 and rrel2.role_resource_id = mem.group_member_id
120 and mem.resource_id = rsc2.resource_id
121 and mem.delete_flag='N'
122 and rsc2.source_id = fnu.employee_id
123 and fnu.user_id = fnd_global.user_id) grps
124 where gmem.group_id = grps.group_id
125 and rsc.resource_id = gmem.resource_id
126 and rrel.role_resource_id = gmem.group_member_id
127 and trunc(sysdate) between rrel.start_date_active
128 and nvl(rrel.end_date_active, trunc(sysdate))
129 and rrb.role_type_code in ('SALES','TELESALES')
130 and rrb.admin_flag = 'N'
131 and rrel.role_id = rrb.role_id
132 and rrel.role_resource_type = 'RS_GROUP_MEMBER'
133 and gmem.delete_flag='N'
134 UNION
135 select distinct rsc.source_id pid,
136 rsc.source_name flname
137 from jtf_rs_resource_extns rsc,
138 fnd_user fnu
139 where rsc.source_id = fnu.employee_id
140 and fnu.user_id = fnd_global.user_id
141 order by 2;
142
143 BEGIN
144
145 IF (icx_sec.validateSession(c_commit => FALSE))
146 THEN
147 l_user_id := icx_sec.getID(icx_sec.pv_user_id); -- Returns login user Id
148 htp.formOpen(owa_util.Get_Owa_Service_Path || 'ast_ofl_accesses_report_pkg.accesses_wrapper', cattributes => ' NAME="param"');
149 header;
150 htp.htmlOpen;
151 htp.headOpen;
152 htp.title('Accesses Report');
153 htp.headClose;
154 footer;
155
156 htp.bodyOpen(cAttributes => 'bgcolor="#CCCCCC"');
157 htp.tableOpen;
158 htp.tableRowOpen(cVAlign => 'top');
159 htp.tableData(' ', cAttributes => ' height=9');
160 htp.tableData('<FONT size=2 face="Times New Roman">' || '</FONT>',
161 cAlign => 'right',
162 cRowSpan => '2',
163 cColSpan => '3',
164 cAttributes => ' width=154');
165 htp.tableData(' ');
166 htp.tableRowClose;
167 htp.tableClose;
168
169 htp.tableOpen(cAttributes => 'width="700"');
170 htp.tableRowOpen();
171 htp.p('<td align="RIGHT" width="50%"valign="top">Select Output Format</td>');
172 htp.p('<td>');
173 htp.p('<SELECT name="p_response">');
174 htp.formSelectOption('Excel');
175 htp.formSelectOption('HTML',cSelected => 'TRUE');
176 htp.formSelectClose;
177 htp.tableRowClose;
178
179 htp.tableRowOpen();
180 htp.p('<td align="RIGHT" width="32%" valign="top">Sales Group</td>');
181 htp.p('<td>');
182 htp.p('<SELECT name="p_sgp">');
183
184 /* Changed by Thanh Huynh 01/27/01 */
185 l_groups_found := FALSE;
186 FOR rec_sales_group IN cur_sales_group(l_user_id)
187 LOOP
188 htp.formSelectOption(rec_sales_group.name, cAttributes => ' value= ' || rec_sales_group.sgi);
189 l_groups_found := TRUE;
190 END LOOP;
191 htp.formSelectClose;
192 htp.p('</td>');
193 htp.tableRowClose;
194 htp.tableRowOpen();
195 htp.p('<td align="RIGHT" width="32%" valign="top">Sales Rep</td>');
196 htp.p('<td>');
197 htp.p('<SELECT name="p_srp">');
198 /* Changed by Thanh Huynh 01/27/01 */
199 IF l_groups_found
200 THEN
201 htp.formSelectOption('ALL', cAttributes => ' value= -999', cSelected
202 => 'TRUE');
203 FOR rec_sales_rep IN cur_sales_rep
204 LOOP
205 htp.formSelectOption(rec_sales_rep.flname,
206 cAttributes => ' value= ' || rec_sales_rep.pid);
207
208 END LOOP;
209 ELSE
210 SELECT rsc.source_name, rsc.source_id
211 INTO l_flname, l_pid
212 FROM jtf_rs_resource_extns rsc,
213 fnd_user fnu
214 WHERE rsc.source_id = fnu.employee_id
215 AND fnu.user_id = fnd_global.user_id;
216 htp.formSelectOption(l_flname,
217 cAttributes => ' value= ' || l_pid);
218 END IF;
219 /* End of Changes by Thanh Huynh 01/27/01 */
220
221 /* Commented by sesundar on 30-jan-01
222 FOR rec_sales_rep IN cur_sales_rep
223 LOOP
224 IF rec_sales_rep.flname ='ALL' THEN
225 htp.formSelectOption(rec_sales_rep.flname,
226 cAttributes => ' value= ' || rec_sales_rep.pid,
227 cSelected => 'TRUE');
228 ELSE
229 htp.formSelectOption(rec_sales_rep.flname,
230 cAttributes => ' value= ' || rec_sales_rep.pid);
231 END IF;
232 END LOOP; */
233 htp.formSelectClose;
234 htp.p('</td>');
235 htp.tableRowClose;
236
237 htp.tableRowOpen();
238 htp.p('<td align="RIGHT" width="50%"valign="top">Access Type</td>');
239 htp.p('<td>');
240 htp.p('<SELECT name="p_access_type">');
241 htp.formSelectOption('Account',cSelected => 'TRUE');
242 htp.formSelectOption('Opportunity');
243 htp.formSelectOption('Lead');
244 htp.formSelectClose;
245 htp.tableRowClose;
246
247 htp.tableClose;
248 htp.Br;
249 htp.Br;
250 htp.Br;
251 footer;
252 htp.FormClose;
253 htp.bodyclose;
254 htp.htmlclose;
255 ELSE
256 htp.p('Invalid session');
257 END IF;
258
259 EXCEPTION
260 WHEN OTHERS THEN htp.p(SQLERRM);
261
262 END accesses_paramform;
263
264 PROCEDURE footer
265 IS
266 BEGIN
267 l_agent := owa_util.get_cgi_env('SCRIPT_NAME');
268 htp.htmlOpen;
269 htp.tableRowOpen;
270 htp.tableData(htf.hr, cRowSpan => '1', cColSpan => '190', cNoWrap => 'TRUE');
271 htp.tableRowClose;
272 htp.tableOpen(cAlign => 'center', cAttributes => ' border=0 cellspacing=2 cellpadding=2');
273 htp.tableRowOpen;
274 htp.formOpen(owa_util.Get_Owa_Service_Path || 'ast_ofl_accesses_report_pkg.accesses_wrapper', cAttributes => ' NAME="param"');
275 htp.tableData(htf.formSubmit(cValue => 'OK', cAttributes => ' onMouseOver="window.status=''OK'';return true"'));
276 htp.tableData( '<INPUT type=button value="Reset" onClick="history.back()" onMouseOver="window.status="Cancel";return true">');
277 htp.tableData( '<INPUT type=button value="Cancel" onClick="window.close()" onMouseOver="window.status="Close";return true">');
278 htp.tableRowClose;
279 htp.tableClose;
280 htp.htmlClose;
281 END footer;
282
283 END AST_OFL_ACCESSES_REPORT;