[Home] [Help]
PACKAGE BODY: APPS.BISVIEWER_PFJ
Source
1 package body bisviewer_pfj as
2 /* $Header: BISPFJB.pls 115.34 2002/11/18 20:36:49 kiprabha ship $ */
3 -- declare global variables
4
5 -----------------------------------------------------------------------------
6 -- FUNCTION: pieDataSet --
7 -- --
8 -- DESCRIPTION: This function will transform an input string into a format --
9 -- necessary for a PFJ pie graph type. --
10 -- For example, it will transform input string --
11 -- 'setDataSeries(1,2,3,...99,);' into a new string --
12 -- 'setDataSeries(1);setDataSeries(2);...setDataSeries(99);' --
13 -- --
14 -- --
15 -- PARAMETERS: inputStr string of PFJ format - data set for a graph --
16 -- --
17 -- MODIFICATIONS: --
18 -- DATE DEVELOPER COMMENTS --
19 -- 22-JUN-00 dmarkman Created --
20 -- --
21 -- 20-MAR-01 dmarkman bug#1529456 pie chart - 'other'slice --
22 -----------------------------------------------------------------------------
23
24 FUNCTION pieDataSet(inputStr in VARCHAR2, pGraphDataPoints in VARCHAR2, pGraphLegend in out NOCOPY varchar2)
25 RETURN VARCHAR2 IS
26
27 v_start NUMBER := 1;
28 v_finish NUMBER := 1;
29 v_dataPointsCount NUMBER := 0;
30 v_sum NUMBER := 0;
31 v_flag NUMBER := 0;
32
33 v_inStr VARCHAR2(32000) := inputStr;
34 v_outStr VARCHAR2(32000) := '';
35 v_tempStr VARCHAR2(1000) := '';
36
37 BEGIN
38
39 v_inStr := REPLACE(v_inStr, 'setDataSeries');
40 v_inStr := REPLACE(v_inStr, '(');
41 v_inStr := REPLACE(v_inStr, ')');
42 v_inStr := REPLACE(v_inStr, ';');
43
44 v_finish := INSTR(v_inStr, ',');
45
46 WHILE(v_finish <> 0) LOOP
47
48 v_tempStr := SUBSTR(v_inStr, v_start, v_finish - v_start);
49
50 if(v_dataPointsCount < TO_NUMBER(pGraphDataPoints)) then
51
52 v_outStr := v_outStr || 'setDataSeries(' || v_tempStr || ');';
53
54 else
55
56 v_sum := v_sum + TO_NUMBER(v_tempStr);
57
58 v_flag := 1;
59
60 end if;
61
62 v_start := v_finish + 1;
63
64 v_finish := INSTR(v_inStr, ',', v_start);
65
66 v_dataPointsCount := v_dataPointsCount + 1;
67
68 END LOOP;
69
70 if(v_flag > 0) then -- put 'other' slice
71
72 v_outStr := v_outStr || 'setDataSeries(' || v_sum || ');';
73
74 pGraphLegend := substr(pGraphLegend,1,instr(pGraphLegend,',',1,pGraphDataPoints)) || fnd_message.get_string('BIS','OTHER');
75
76 end if;
77
78 RETURN v_outStr;
79
80 END pieDataSet;
81
82 -----------------------------------------------------------------------------
83 -- FUNCTION: buildChartApplet --
84 -- --
85 -- DESCRIPTION: This function will create an applet that displays a PFJ --
86 -- graph. See PFJ documentation fot more details. --
87 -- --
88 -- PARAMETERS: pAppletWidth Applet width --
89 -- pFrameYaxis Frame Y axis --
90 -- pFrameHeight Frame height --
91 -- pGraphStyle Graph style --
92 -- pGraphyaxis Graph Y axis --
93 -- pGraphTitle Graph title --
94 -- pGraphLegend Graph legend --
95 -- pXaxisLabel X axis label --
96 -- pGraphValue Graph data set --
97 -- --
98 -- MODIFICATIONS: --
99 -- DATE DEVELOPER COMMENTS --
100 -- 22-JUN-00 dmarkman Created --
101 -- 20-Sep-00 aleung use variable applet width and height --
102 -- 13-Nov-00 dmarkman comment out the servlet --
103 -- 16-Nov-00 dmarkman set viewable datapoints to 12 --
104 -- 05-Dec-00 dmarkman in viewable datapoints, pass a parameter --
105 -- pGraphDataPoints to a call setViewableGroups --
106 -- (applet). To be added to sevlet as well --
107 -- 06-Dec-00 hali Added pGraphDataPoints to servlet
108 -- 07-Dec-00 hali Added vMode for no Default sample points in servlet
109
110 -- 08-Dec-00 hali Activated the servlet
111 -- 07-Mar-01 dmarkman bug#1671995
112 -- 09/07/01 mdamle Add Graph Number poplist --
113 -----------------------------------------------------------------------------
114
115 function buildChartApplet ( pAppletWidth in number,
116 pAppletHeight in number,
117 pFrameYaxis in number,
118 pFrameHeight in number,
119 pGraphStyle in varchar2,
120 pGraphyaxis in varchar2,
121 pGraphTitle in varchar2,
122 pGraphLegend in varchar2,
123 pXaxisLabel in varchar2,
124 pGraphValue in varchar2,
125 pGraphDataPoints in varchar2,
126 pGraphName in varchar2 default null,
127 pRequestType in varchar2 default null,
128 pScheduleId in number default null,
129 pDeltaFontSize in number default 0,
130 pFontType in varchar2 default 'Dialog',
131 -- mdamle 09/07/01 - Add Graph Number poplist
132 pFileId in number default null
133 ) return varchar2 is
134
135
136 vApplet varchar2(32000) := '';
137 vGraphValue1 varchar2(32000) := '';
138 vGraphTitle varchar2(32000) := '';
139 vGraphyaxis varchar2(32000) :='';
140 vGraphValue varchar2(32000) := pGraphValue;
141 vGraphLegend varchar2(32000) := pGraphLegend;
142 vXaxisLabel varchar2(32000) := pXaxisLabel;
143 vDepthRadiusValue number := 40;
144
145 --hali 6/12/00 No Default Sample points in servlet
146
147 vMode varchar2(320) := 'nodefault';
148 vAgent varchar2(100);
149 vGraphName varchar2(1000);
150
151 vGraphStyle NUMBER := pGraphStyle;
152
153 vUserId varchar2(80);
154 vRespId varchar2(80);
155 vFileId number;
156 vAppsId number;
157 vFunctionId number;
158 vSessionId varchar2(80);
159 vCookieName varchar2(2000);
160 vCookieValue varchar2(2000);
161 vTransactionId varchar2(2000);
162 vDBCName varchar2(2000);
163 vWebAgent varchar2(2000);
164 vFwkAgent varchar2(2000);
165 vLanguageCode varchar2(1000);
166 vReturnStatus varchar2(2000);
167 vMsgData varchar2(2000);
168 vMsgCount number;
169 vParameterString varchar2(32000);
170 l_function varchar2(32000);
171 vDummyString varchar2(32000);
172
173 cursor cScheduler(cpScheduleId in number) is
174 -- mdamle 09/07/01 - Add Graph Number poplist
175 -- Removed fileId from select
176 select user_id, responsibility_id
177 from bis_scheduler
178 where schedule_id = cpScheduleId;
179
180 /*
181 cursor cAppsId(cpRespId in number) is
182 select application_id
183 from fnd_responsibility
184 where responsibility_id = cpRespId;
185
186 cursor cFuncId(cpFunctionName in varchar2) is
187 select function_id
188 from fnd_form_functions
189 where function_name = cpFunctionName;
190 */
191
192 vSqlErr varchar2(32000);
193 l_plsql_agent varchar2(32000);
194
195 begin
196
197 if (TO_NUMBER(vGraphStyle) = 117) then
198
199 vDepthRadiusValue := 0;
200 vGraphStyle := 17;
201
202 elsif (TO_NUMBER(vGraphStyle) = 118) then
203
204 vDepthRadiusValue := 0;
205 vGraphStyle := 18;
206
207 elsif (TO_NUMBER(vGraphStyle) = 41) then
208
209 vDepthRadiusValue := 0;
210
211 elsif (TO_NUMBER(vGraphStyle) = 141) then
212
213 vGraphStyle := 41;
214
215 elsif (TO_NUMBER(vGraphStyle) = 131) then
216
217 vDepthRadiusValue := 0;
218 vGraphStyle := 31;
219
220 end if;
221
222 if (TO_NUMBER(vGraphStyle) = 55) then
223
224 vGraphLegend := pXaxisLabel;
225 vXaxisLabel := pGraphLegend;
226 vGraphValue := pieDataSet(vGraphValue,'11',vGraphLegend);
227
228 end if;
229
230
231 /*********SERVLET*START*******COMMENTED OUT*******/
232
233 -- SELECT fnd_profile.value('BIS_SERVLET_BASE_PATH')
234 -- INTO vAgent
235 -- FROM dual;
236
237 vAgent := fnd_profile.value('BIS_SERVLET_BASE_PATH');
238
239 --hali 11/30/00 Cannot have spaces,# and semi-colon in the URL
240
241 -- dmarkman 03/06/01 URL is too long bug#1671995 - remove spaces in vGraphValue.
242 vGraphValue := replace(vGraphValue,' ');
243
244 -- dmarkman 05/01/01 '&' in the data in the URL bug#1765105
245
246 vGraphLegend := replace(vGraphLegend,' ','^');
247 vGraphLegend := replace(vGraphLegend,'",',',,,,');
248 vGraphLegend := replace(vGraphLegend,'"','');
249 vGraphLegend := replace(vGraphLegend,'%','Percent^');
250 vGraphLegend := replace(vGraphLegend,'&','%26!');
251
252 vXaxisLabel := replace(vXaxisLabel,' ','^');
253 vXaxisLabel := replace(vXaxisLabel,'",',',,,,');
254 vXaxisLabel := replace(vXaxisLabel,'"','');
255 vXaxisLabel := replace(vXaxisLabel,'%','Percent^');
256 vXaxisLabel := replace(vXaxisLabel,'&','%26');
257
258 vGraphyaxis := replace(pGraphyaxis,' ','^');
259 vGraphyaxis := replace(vGraphyaxis,'",',',,,,');
260 vGraphyaxis := replace(vGraphyaxis,'"','');
261 vGraphyaxis := replace(vGraphyaxis ,'%','Percent^');
262 vGraphyaxis := replace(vGraphyaxis,'&','%26');
263
264 vGraphTitle := replace(pGraphTitle,' ','^');
265 vGraphTitle := replace(vGraphTitle,'",',',,,,');
266 vGraphTitle := replace(vGraphTitle,'"','');
267 vGraphTitle := replace(vGraphTitle,'%','Percent^');
268 vGraphTitle := replace(vGraphTitle,'&','%26');
269
270 if pGraphTitle is null then
271 vGraphName := pGraphName;
272 else
273 vGraphName := pGraphTitle;
274 end if;
275
276 vApplet := '<img src ="' || vAgent || '/oracle.apps.bis.chart.BisGraph?';
277 vApplet := vApplet || 'param='||vMode;
278 vApplet := vApplet || '&pGraphStyle='||vGraphStyle;
279 vApplet := vApplet || '&vDepthRadiusValue='||vDepthRadiusValue;
280 vApplet := vApplet || '&pHeight='||pAppletHeight;
281 vApplet := vApplet || '&pWidth='||pAppletWidth;
282 vApplet := vApplet || '&pFrameYaxis='||pFrameYaxis;
283 vApplet := vApplet || '&pFrameHeight='||pFrameHeight;
284 vApplet := vApplet || '&pGraphDataPoints='||pGraphDataPoints;
285 vApplet := vApplet || '&vDeltaFontSize='||pDeltaFontSize;
286 vApplet := vApplet || '&vFontType='||pFontType;
287 vApplet := vApplet || '&pGraphyaxis=' ||vGraphyaxis;
288 vApplet := vApplet || '&pGraphTitle=' ||vGraphTitle;
289 vApplet := vApplet || '&vGraphLegend=' || vGraphLegend;
290 vApplet := vApplet || '&vXaxisLabel=' || vXaxisLabel;
291 vApplet := vApplet || '&vGraphValue=' || vGraphValue;
292 vApplet := vApplet || '" ALT="'||vGraphName||'">';
293
294 vApplet := replace(vApplet,'",',',,,,');
295 vApplet := replace(vApplet,'"','');
296 vApplet := replace(vApplet,';',',');
297 vApplet := replace(vApplet,'#','~');
298 --vApplet := replace(vApplet,'%','Percent^');
299
300 -- vApplet := vApplet || '" height='|| to_char(pAppletHeight);
301 -- vApplet := vApplet || ' width='||to_char(pAppletWidth)||'>';
302 -- aleung 9/20/2000 change 700 to
303 -- to_char(pAppletWidth)
304
305 --aleung, 8/27/01, invoke the graph servlet as OracleApps.RF, pass file id and request type
306 if pRequestType = 'G' and pScheduleId > 0 then
307 if cScheduler%ISOPEN then
308 close cScheduler;
309 end if;
310 open cScheduler(pScheduleId);
311 -- mdamle 09/07/01 - Add Graph Number poplist
312 -- Removed file_id from fetch
313 fetch cScheduler into vUserId, vRespId;
314 close cScheduler;
315 /*
316 if cAppsId%ISOPEN then
317 close cAppsId;
318 end if;
319 open cAppsId(vRespId);
320 fetch cAppsId into vAppsId;
321 close cAppsId;
322
323 if cFuncId%ISOPEN then
324 close cFuncId;
325 end if;
326 open cFuncId('BIS_PMV_GRAPH_SERVLET');
327 fetch cFuncId into vFunctionId;
328 close cFuncId;
329
330 */
331 --create ICX Session
332 BIS_ICX_SECURITY_PVT.CREATE_ICX_SESSION
333 (p_user_id => vUserId
334 ,x_session_id => vSessionId
335 ,x_cookie_value => vCookieValue
336 ,x_cookie_name => vCookieName
337 ,x_transaction_id => vTransactionId
338 ,x_dbc_name => vDBCName
339 ,x_apps_web_agent => vWebAgent
340 ,x_apps_fwk_agent => vFwkAgent
341 ,x_language_code => vLanguageCode
342 ,x_return_Status => vReturnStatus
343 ,x_msg_Data => vMsgData
344 ,x_msg_count => vMsgCount);
345
346 /*
347 l_function := icx_Call.encrypt2(vAppsId||'*'||vRespId ||'*'||
348 icx_sec.g_security_group_id || '*'||vFunctionId||'**]',
349 vSessionId);
350 */
351 -- mdamle 09/07/01 - Add Graph Number poplist
352 -- Changed vFileId to pFileId
353 vParameterString := vParameterString || 'pFileId='||pFileId;
354 vParameterString := vParameterString || '&pRequestType=G';
355 vParameterString := vParameterString || '¶m='||vMode;
356 vParameterString := vParameterString || '&pGraphStyle='||vGraphStyle;
357 vParameterString := vParameterString || '&vDepthRadiusValue='||vDepthRadiusValue;
358 vParameterString := vParameterString || '&pHeight='||pAppletHeight;
359 vParameterString := vParameterString || '&pWidth='||pAppletWidth;
360 vParameterString := vParameterString || '&pFrameYaxis='||pFrameYaxis;
361 vParameterString := vParameterString || '&pFrameHeight='||pFrameHeight;
362 vParameterString := vParameterString || '&pGraphDataPoints='||pGraphDataPoints;
363 vParameterString := vParameterString || '&vDeltaFontSize='||pDeltaFontSize;
364 vParameterString := vParameterString || '&vFontType='||pFontType;
365 vParameterString := vParameterString || '&pGraphyaxis=' ||vGraphyaxis;
366 vParameterString := vParameterString || '&pGraphTitle=' ||vGraphTitle;
367 vParameterString := vParameterString || '&vGraphLegend=' ||vGraphLegend;
368 vParameterString := vParameterString || '&vXaxisLabel=' ||vXaxisLabel;
369 vParameterString := vParameterString || '&vGraphValue=' ||vGraphValue;
370
371 vParameterString := replace(vParameterString,'",',',,,,');
372 vParameterString := replace(vParameterString,'"','');
373 vParameterString := replace(vParameterString,';',',');
374 vParameterString := replace(vParameterString,'#','~');
375
376
380 OracleApps.RF(F => l_function,
377 vDummyString := utl_http.request(vAgent||'/oracle.apps.bis.chart.BisGraph?dbc='||FND_WEB_CONFIG.DATABASE_ID
378 ||'&sessionid='||vsessionid||'&'||vParameterString);
379 /*
381 P => icx_call.encrypt2(vParameterString, vSessionId));
382
383 */
384
385 end if;
386
387 /********SERVLET*END*****COMMENTED OUT*******/
388
389
390 /*******APPLET*START*********
391
392 vApplet := '<applet CODE="oracle.apps.bis.chart.BISChartApplet.class" WIDTH="'||to_char(pAppletWidth)||'"
393 HEIGHT="' || to_char(pAppletHeight) || '" ARCHIVE="/OA_JAVA/oracle/apps/bis/jar/bischart.jar">
394 <param name="TDGSCRIPT"
395 value="
396 setGraphType(' || vGraphStyle || ');
397 setFontSizeAbsolute(true);
398 setFontSize(getTitle(),8);
399 setFontSizeAbsolute(true);
400 setFontSize(get01Label(),6);
401 setFontSizeAbsolute(true);
402 setFontSize(getY1Label(),6);
403 setFontSizeAbsolute(true);
404 setFontSize(getY1Title(),7);
405 setFontSizeAbsolute(true);
406 setFontSize(getX1Label(),6);
407 setFontStyle(getX1Label(),0);
408 setX1LabelRotate(2);
409 setAutofit(getY1Label(),true);
410 setAutofit(getX1Label(),true);
411 setFontSize(getLegendText(),5);
412 setUseOffScreen(false);
413 setAutofit(getFrame(),false);
414 setManualRedraw(false);
415 setLegendOrient(0);
416 setReshapeEnable(true);
417 setSelectionEnableMove(true);
418 setDepthRadius(' || vDepthRadiusValue || ');
419 setFillColor(getLegendArea(),new Color(255,255,255));
420 setRect(getFrame(),new Rectangle(-12000,-'|| pFrameYaxis ||',21000,'|| pFrameHeight ||'));
421 setRect(getTitle(),new Rectangle(-14913,13283,29850,1800));
422 setRect(getLegendArea(),new Rectangle(9200,4000,6000,7000));
423 setFillColor(getTitle(),new Color(0,0,255));
424 setTextRotation(getO1Label(),2); setTextFormatPattern(getY1Label(),2);
425 setTextRotation(getX1Label(),1);
426 setTextRotation(getY1Label(),0);
427 setScrollOffsetGroups(3);
428 setViewableGroups(' || pGraphDataPoints || ');
429 setViewableSeries(10);
430 setY1TitleString("' || pGraphyaxis || '");
431 setTextString(getTitle(), "' || pGraphTitle || '");
432 setSeriesLabelArray(' || vGraphLegend || ');
433 setGroupLabelArray(' || vXaxisLabel || '); ' || vGraphValue ||
434 '">
435 </applet>';
436
437 /*****APPLET*END************************/
438
439
440 return vApplet;
441 exception when others then
445
442 null;
443 end buildChartApplet;
444
446
447 end bisviewer_pfj;
448