[Home] [Help]
PACKAGE BODY: APPS.BISVIEWER
Source
1 Package Body bisviewer as
2 /* $Header: BISVIEWB.pls 120.6 2006/02/13 02:47:11 nbarik noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.363=120.6):~PROD:~PATH:~FILE
5 -- create new header and footer in showReport. 08/17/2000 aleung
6 ----------------------------------------------------------------------------
7 -- PACKAGE: bisviewer --
8 -- --
9 -- DESCRIPTION: Report Generate main engine. --
10 -- --
11 -- MODIFICATIONS --
12 -- Date User Modification --
13 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
14 -- 8/17/2000 aleung intial creation --
15 -- 5/10/2002 nkishore Added pParameters for showReport --
16 -- 6/12/2002 mdamle Disabled calling OA JSP from here --
17 -- 10/10/2002 mdamle Added parameter for pEnableForecastGraph --
18 -- 4/16/2003 nkishore Added pRespId to get_notify_rpt_url bug 2833251 --
19 -- 6/27/2003 rcmuthuk Added p_UserId to get_notify_rpt_url bug 2810397--
20 -- 07-JUN-03 gsanap Added p_NextExtraViewBy to drilldown bug 3007145--
21 -- 08/21/03 nbarik Bug Fix 3099831 - delay function access check --
22 -- 28-JAN-04 nkishore BugFix 3075441 --
23 -- 18-Jan-05 smargand Enh #4031345 Report XML definition --
24 ----------------------------------------------------------------------------
25
26 -- declare global variables
27 gvImageDirectory varchar2(1000) := BIS_REPORT_UTIL_PVT.get_Images_Server;
28 gv_font_name varchar2(30) := 'Arial';
29 gv_font_size varchar2(5) := '3';
30
31 ----------------------------------------------------------------------------------------
32 -- Procedure: showReport --
33 -- --
34 -- HISTORY --
35 -- Date Developer Modifications --
36 -- 05-Nov-2001 aleung redirect to bisviewm.jsp --
37 -- 08-Mar-2002 nbarik redirect to OA.jsp --
38 -- 13-Oct-2004 ugodavar added pMaxResultSetSize & pOutputFormat --
39 ----------------------------------------------------------------------------------------
40 procedure showReport(pRegionCode in varchar2 ,
41 pFunctionName in varchar2 ,
42 pApplicationId in varchar2 default NULL,
43 pSessionId in varchar2 default NULL,
44 pUserId in varchar2 default NULL,
45 pResponsibilityId in varchar2 default NULL,
46 pFirstTime in number default 1,
47 pHideParm in varchar2 default 'false',
48 pHideGraph in varchar2 default 'false',
49 pHideTable in varchar2 default 'false',
50 pHideRinfo in varchar2 default 'false',
51 pSortInfo in varchar2 default null,
52 pMode in varchar2 default 'SHOW',
53 pPreFunctionName in varchar2 default NULL,
54 pScheduleId in varchar2 default NULL,
55 pRequestType in varchar2 default 'R',
56 pFileId in number default null,
57 pParameterDisplayOnly in varchar2 default 'N',
58 pForceRun in varchar2 default 'N',
59 pDisplayParameters in varchar2 default 'Y',
60 pReportSchedule in varchar2 default 'Y',
61 pCSVFileName in varchar2 default NULL,
62 pPageId in varchar2 default NULL,
63 pParameters in varchar2 default NULL,
64 -- mdamle 10/10/2002 - Enh# 2460903 - Forecast Graph
65 pEnableForecastGraph in varchar2 default NULL,
66 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
67 pCustomView in varchar2 default NULL,
68 pObjectType IN VARCHAR2 DEFAULT NULL,
69 pautoRefresh IN VARCHAR2 DEFAULT NULL,
70 -- Enh Display Run Button
71 pDispRun IN VARCHAR2 DEFAULT NULL,
72 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
73 pDrillDefaultParameters IN VARCHAR2 DEFAULT NULL,
74 --ugodavar - 10/12/04 - Enh.3946492
75 pMaxResultSetSize IN number DEFAULT NULL,
76 pOutputFormat IN VARCHAR2 DEFAULT NULL,
77 -- nbarik - 02/08/05 - Enhancement 4120795
78 hideNav IN VARCHAR2 DEFAULT NULL,
79 tab IN VARCHAR2 DEFAULT NULL,
80 displayOnlyParameters IN VARCHAR2 DEFAULT NULL,
81 displayOnlyNoViewByParams IN VARCHAR2 DEFAULT NULL,
82 pBCFromFunctionName IN VARCHAR2 DEFAULT NULL,
83 pPrevBCInfo IN VARCHAR2 DEFAULT NULL,
84 pResetView IN VARCHAR2 DEFAULT NULL
85 ) is
86
87 vSessionId varchar2(80);
88 vUserId varchar2(80);
89 vResponsibilityId varchar2(80);
90
91 jspURL varchar2(32767);
92 jspParams varchar2(32767);
93 functionid NUMBER;
94 l_function VARCHAR2(32000);
95 l_application_id NUMBER;
96 vPageId VARCHAR2(80);
97
98 CURSOR cFndResp (pRespId in varchar2) is
99 select application_id
100 from fnd_responsibility
101 where responsibility_id=pRespId;
102 l_render_type varchar2(200) := 'HTML';
103
104 --serao -for grouped parameters
105 lParamGrp BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type;
106 lTCTExists boolean := false;
107
108 vURL varchar2(32000);
109 l_servlet_agent varchar2(2000);
110 l_transaction_id number;
111 lNestedRegionCode varchar2(250);
112 lAsofdateExists boolean;
113
114 -- DIMENSION VALUE EXTENSION - DRILL - Bug 3230530 / Bug 3004363
115 lTimeAttribute varchar2(250) ;
116 l_DrillDefaultParameters VARCHAR2(3000);
117 begin
118
119 /*
120 if fnd_profile.value('BIS_SQL_TRACE')= 'Y' then
121 dbms_session.set_sql_trace(true);
122 end if;
123 */
124 --nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
125 l_DrillDefaultParameters := pDrillDefaultParameters;
126 vPageId := pPageId;
127 if pScheduleId is null then
128 if (not icx_sec.ValidateSession) then
129 return;
130 end if;
131 vSessionId := icx_sec.getID(icx_sec.PV_SESSION_ID);
132 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
133
134 if pResponsibilityId is not null then
135 vResponsibilityId := pResponsibilityId;
136 else
137 vResponsibilityId := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
138 end if;
139
140 /* aleung, 8/15/03, bug 3099831 - delay this function access check to bisviewm.jsp
141 if not BIS_GRAPH_REGION_HTML_FORMS.hasFunctionAccess(vUserId, pFunctionName) then
142 displayErrorMessage(BIS_REPORT_UTIL_PVT.Get_Report_Title
143 (pFunctionName)||BIS_PMV_UTIL.getAppendTitle(pRegionCode),
144 '', '', 4, pRegionCode);
145 return;
146 end if;
147 */
148 else
149 vSessionId := pSessionId;
150 vUserId := pUserId;
151
152 BEGIN
153 SELECT responsibility_id INTO vResponsibilityId
154 FROM bis_scheduler
155 WHERE schedule_Id = pScheduleId;
156 EXCEPTION
157 WHEN OTHERS THEN NULL;
158 END;
159
160 vResponsibilityId := pResponsibilityId;
161 bis_autoinc_schedule.autoIncrementDates(pRegionCode,
162 pFunctionName,
163 vSessionId,
164 vUserId,
165 vResponsibilityId,
166 pScheduleId);
167 end if;
168
169 -- nbarik - 06/15/04 - Bug Fix 3687555 - Get it from context
170 l_application_id := FND_GLOBAL.RESP_APPL_ID;
171 IF (l_application_id IS NULL OR l_application_id = -1) THEN
172 OPEN cFNDResp(vResponsibilityId);
173 FETCH cFNDResp INTO l_application_id;
174 CLOSE cFNDResp;
175 END IF;
176
177 -- this is now taken care of by java drill code
178 /*
179 IF (pMode = 'RELATED') THEN
180 -- pass the page id.
181 -- serao- 08/23/2002- bug 2514044 - pass respId
182
183 BIS_PMV_PARAMETERS_PVT.getParameterGroupsForRegion( pRegionCode, lParamGrp, lTCTExists, lNestedRegionCode, lAsofdateExists);
184
185 -- DIMENSION VALUE EXTENSION - DRILL - Bug 3230530 / Bug 3004363
186 BIS_PMV_DRILL_PVT.copyGroupedParameters(pSessionId => vSessionId,
187 pUserId => vUserId,
188 pPreFunctionName => pPreFunctionName,
189 pFunctionName => pFunctionName,
190 pRegionCode => pRegionCode,
191 pPageId => pPageId,
192 pRespId => vResponsibilityId,
193 pParameterGroup => lParamGrp,
194 pTCTExists => lTCTExists,
195 pNestedRegionCode => lNestedRegionCode,
196 pAsofDateExists => lAsofdateExists,
197 xTimeAttribute => lTimeAttribute
198 ) ;
199
200 --nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
201 BIS_PMV_PARAMETERS_PVT.VALIDATE_SAVE_DRILL_PARAMS(
202 pRegionCode => pRegionCode,
203 pFunctionName => pFunctionName,
204 pParameterGroup => lParamGrp,
205 pPageId => pPageId,
206 pUserId => vUserId,
207 pSessionId => vSessionId,
208 x_DrillDefaultParameters => l_DrillDefaultParameters
209 );
210 --serao- 08/27/2002- bug 2530913 - do not pass page_id once drilled into a related link
211 vPageId := NULL;
212
213 end if;
214 */
215 BEGIN
216
217 -- mdamle 6/12/2002 - Disabled calling OA JSP from here
218 -- PMV will always start from bisviewm.jsp irrespective
219 -- of the render type. The OA table will then be
220 -- inserted in the JSP at runtime if needed.
221 /*
222 --if fnd_profile.value('PMV_RENDER_TYPE')= 'OAF' then --Nihar added for OA Rendering
223 if BIS_PMV_UTIL.get_Render_type(pRegionCode, vUserId, vResponsibilityId)= 'OAF' then
224 -- if (l_render_type = 'OAF') then
225 --amkulkar check the generic fun
226 select function_id
227 into functionid
228 from fnd_form_functions
229 where function_name = 'BIS_PMV_OA_JSP';
230 jspParams := 'akRegionCode=BISREPORTPAGE&akRegionApplicationId=191';
231 jspParams := jspParams ||'&reportRegionCode='||BIS_PMV_UTIL.encode(pRegionCode)||'&functionName='||BIS_PMV_UTIL.encode(pFunctionName);
232 jspParams := jspParams ||'&forceRun='||pForceRun||'¶meterDisplayOnly='||pParameterDisplayOnly||'&displayParameters='||pDisplayParameters;
233 jspParams := jspParams ||'&showSchedule='||pReportSchedule||'&pFirstTime='||pFirstTime||'&pMode='||pMode;
234 jspParams := jspParams ||'&scheduleId='||pScheduleId||'&requestType='||pRequestType;
235 jspParams := jspParams ||'&fileId='||pFileId ||'&pResponsibilityId='||pResponsibilityId;
236 jspParams := jspParams ||'&pUserId='||pUserId||'&pSessionId='||pSessionId;
237 jspParams := jspParams ||'&pApplicationId='||pApplicationId||'&pPreFunctionName='||BIS_PMV_UTIL.encode(pPreFunctionNAme);
238 jspParams := jspParams ||'&pObjectType='||pObjectType;
239 if (vPageId is not null) then
240 jspParams := jspParams ||'&_pageid='||vPageId;
241 end if;
242 if (pParameters is not null) then
243 jspParams := jspParams ||'&pParameters='||BIS_PMV_UTIL.encode(pParameters);
244 end if;
245 else
246 */
247
248
249 select function_id
250 into functionid
251 from fnd_form_functions
252 where function_name = 'BIS_PMV_REPORT_JSP';
253
254 jspParams := 'regionCode='||BIS_PMV_UTIL.encode(pRegionCode)||'&functionName='||BIS_PMV_UTIL.encode(pFunctionName);
255 jspParams := jspParams ||'&forceRun='||pForceRun||'¶meterDisplayOnly='||pParameterDisplayOnly||'&displayParameters='||pDisplayParameters;
256 jspParams := jspParams ||'&showSchedule='||pReportSchedule||'&pFirstTime='||pFirstTime||'&pMode='||pMode;
257 jspParams := jspParams ||'&scheduleId='||pScheduleId||'&requestType='||pRequestType;
258 jspParams := jspParams ||'&fileId='||pFileId ||'&pResponsibilityId='||pResponsibilityId;
259 jspParams := jspParams ||'&pUserId='||pUserId||'&pSessionId='||vSessionId;
260 jspParams := jspParams ||'&pApplicationId='||pApplicationId||'&pPreFunctionName='||BIS_PMV_UTIL.encode(pPreFunctionNAme);
261
262 if (pCSVFileName is not null) then
263 jspParams := jspParams ||'&pCSVFileName='||BIS_PMV_UTIL.encode(pCSVFileName);
264 end if;
265 if (vPageId is not null) then
266 jspParams := jspParams ||'&_pageid='||vPageId;
267 end if;
268
269 --Bug Fix 2997706
270 if (pObjectType is not null) then
271 jspParams := jspParams ||'&pObjectType='||pObjectType;
272 end if;
273
274 if (displayOnlyParameters is not null) then
275 jspParams := jspParams ||'&displayOnlyParameters='||BIS_PMV_UTIL.encode(displayOnlyParameters);
276 end if;
277 if (displayOnlyNoViewByParams is not null) then
278 jspParams := jspParams ||'&displayOnlyNoViewByParams='||BIS_PMV_UTIL.encode(displayOnlyNoViewByParams);
279 end if;
280
281 if (pParameters is not null) then
282 jspParams := jspParams ||'&pParameters='||BIS_PMV_UTIL.encode(pParameters);--Fix for 2445406
283 end if;
284
285 -- mdamle 10/10/2002 - Enh# 2460903 - Forecast Graph
286 if (pEnableForecastGraph is not null) then
287 jspParams := jspParams ||'&pEnableForecastGraph='||pEnableForecastGraph;
288 end if;
289
290 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
291 if (pCustomView is not null) then
292 jspParams := jspParams ||'&pCustomView='||BIS_PMV_UTIL.encode(pCustomView);
293 end if;
294 if (pautorefresh is not null) then
295 jspParams := jspParams || '&autoRefresh='||BIS_PMV_UTIL.encode(pautorefresh);
296 end if;
297 --We need to create an icx transaction. Neal has approved the use of this API.:q
298 --l_Transaction_id := icx_sec. createTransaction(p_session_id => vsessionid);
299
300 -- end if;
301
302
303 -- kiprabha - Enh Display Run Button
304 if (pDispRun is not null) then
305 jspParams := jspParams || '&pDispRun='||pDispRun;
306 end if ;
307
308 IF (l_DrillDefaultParameters IS NOT NULL) THEN
309 jspParams := jspParams || '&' || l_DrillDefaultParameters;
310 END IF;
311
312 --ugodavar - Enh.3946492
313 if(pMaxResultSetSize is not null) then
314 jspParams := jspParams ||'&pMaxResultSetSize='||pMaxResultSetSize;
315 end if;
316
317 if(pOutputFormat is not null) then
318 jspParams := jspParams ||'&pOutputFormat='||pOutputFormat;
319 end if;
320
321 -- nbarik - 02/08/05 - Enhancement 4120795
322 if(hideNav is not null) then
323 jspParams := jspParams ||'&hideNav='||hideNav;
324 end if;
325
326 if(tab is not null) then
327 jspParams := jspParams ||'&tab='||tab;
328 end if;
329
330 if(pBCFromFunctionName is not null) then
331 jspParams := jspParams ||'&pBCFromFunctionName='|| pBCFromFunctionName;
332 end if;
333
334 if(pPrevBCInfo is not null) then
335 jspParams := jspParams ||'&pPrevBCInfo='|| pPrevBCInfo;
336 end if;
337
338 if(pResetView is not null) then
339 jspParams := jspParams ||'&pResetView='|| pResetView;
340 end if;
341
342 EXCEPTION
346 --Invoke the bisviewm.jsp
343 WHEN OTHERS THEN NULL;
344 END;
345
347 /*
348 l_function := icx_call.encrypt2(l_application_id||'*'||vResponsibilityId||'*'||
349 icx_sec.g_Security_group_id||'*'||functionId||'**]'
350 ,icx_sec.getId(icx_sec.PV_SESSION_ID));
351 OracleApps.RF(F=>l_function, P=> icx_call.encrypt2(jspParams,icx_sec.getID(icx_sec.PV_SESSION_ID)));
352
353 l_servlet_agent := FND_WEB_CONFIG.JSP_AGENT; -- 'http://serv:port/OA_HTML/'
354 IF ( l_servlet_agent IS NULL ) THEN -- 'APPS_SERVLET_AGENT' is null
355 l_servlet_agent := FND_WEB_CONFIG.WEB_SERVER || 'OA_HTML/';
356 END IF;
357
358 vURL := l_servlet_agent || 'bisviewm.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID ||'&'||jspParams;
359 vURL := vURL || '&transactionid='||icx_call.encrypt3(l_transaction_id);
360 owa_util.redirect_url(vURL);
361 */
362 OracleApps.runFunction(c_function_id => functionId
363 ,n_session_id => vSessionId
364 ,c_parameters => jspParams
365 ,p_resp_appl_id => l_application_id
366 ,p_responsibility_id => vResponsibilityId
367 ,p_Security_group_id => icx_sec.g_Security_group_id
368 );
369 /*
370 if fnd_profile.value('BIS_SQL_TRACE')= 'Y' then
371 dbms_session.set_sql_trace(false);
372 end if;
373 */
374 Exception
375 when others then null;
376 end showReport;
377
378 procedure drilldown(pRegionCode in varchar2,
379 pFunctionName in varchar2,
380 pSessionId in Varchar2,
381 pUserId in Varchar2,
382 pResponsibilityId in Varchar2,
383 pCurrValue in varchar2,
384 pCurrLevel in varchar2,
385 pCurrAttCode in varchar2,
386 pNextLevel in varchar2,
387 pNextAttCode in varchar2,
388 pDimension in varchar2,
389 pYaxisView in varchar2,
390 pOrgParam in varchar2,
391 pOrgValue in varchar2,
392 pScheduleId in varchar2 default null,
393 pPageId in varchar2 default null,
394 pNextExtraViewBy in varchar2 default NULL) is
395 begin
396 NULL;
397 end drilldown;
398
399 procedure displayErrorMessage (p_reportTitle in varchar2,
400 p_dimn_level_short_name in varchar2,
401 p_dimn_level_value in varchar2,
402 p_error_no in number,
403 pRegionCode in varchar2 default null) is
404 begin
405 -- nbarik - 02/02/06 - Bug Fix 4941888
406 -- This shouldn't be used, since it has dependency, making the content null, will remove it later
407 NULL;
408 end displayErrorMessage;
409
410 procedure displayError (pErrorNumber in Number,
411 pOracleErrorNo in varchar2,
412 pOracleErrorMessage in varchar2) is
413
414 vErrorMessage varchar2(2000);
415
416 begin
417 htp.htmlOpen;
418 htp.headOpen;
419 htp.headClose;
420 htp.fontOpen(cface=>gv_font_name, csize=>gv_font_size, ccolor=>'red');
421 /*gsanap 4/16/04 encoding errormessage bug fix 3568859 */
422 vErrorMessage := to_char(pErrorNumber,'99999') || ' ' || pOracleErrorNo
423 || ' ' || wf_notification.SubstituteSpecialChars(pOracleErrorMessage);
424 htp.print(vErrorMessage);
425
426 htp.fontClose;
427 htp.br;
428 htp.fontOpen(cface=>gv_font_name, csize=>gv_font_size);
429 htp.fontClose;
430 htp.htmlClose;
431 end displayError;
432
433 --BugFix 3075441, add p_NlsLangCode
434 FUNCTION GET_NOTIFY_RPT_URL(
435 p_measure_id IN VARCHAR2,
436 p_region_code in varchar2 default null,
437 p_function_name in varchar2 default null,
438 p_bplan_name IN VARCHAR2 default null,
439 p_viewby_level_short_name IN VARCHAR2 default null,
440 p_Parm1Level_short_name IN VARCHAR2 default null, p_Parm1Value_name IN VARCHAR2 default null,
441 p_Parm2Level_short_name IN VARCHAR2 default null, p_Parm2Value_name IN VARCHAR2 default null,
442 p_Parm3Level_short_name IN VARCHAR2 default null, p_Parm3Value_name IN VARCHAR2 default null,
443 p_Parm4Level_short_name IN VARCHAR2 default null, p_Parm4Value_name IN VARCHAR2 default null,
444 p_Parm5Level_short_name IN VARCHAR2 default null, p_Parm5Value_name IN VARCHAR2 default null,
445 p_Parm6Level_short_name IN VARCHAR2 default null, p_Parm6Value_name IN VARCHAR2 default null,
446 p_Parm7Level_short_name IN VARCHAR2 default null, p_Parm7Value_name IN VARCHAR2 default null,
447 p_Parm8Level_short_name IN VARCHAR2 default null, p_Parm8Value_name IN VARCHAR2 default null,
448 p_Parm9Level_short_name IN VARCHAR2 default null, p_Parm9Value_name IN VARCHAR2 default null,
452 p_Parm13Level_short_name IN VARCHAR2 default null, p_Parm13Value_name IN VARCHAR2 default null,
449 p_Parm10Level_short_name IN VARCHAR2 default null, p_Parm10Value_name IN VARCHAR2 default null,
450 p_Parm11Level_short_name IN VARCHAR2 default null, p_Parm11Value_name IN VARCHAR2 default null,
451 p_Parm12Level_short_name IN VARCHAR2 default null, p_Parm12Value_name IN VARCHAR2 default null,
453 p_TimeParmLevel_short_name in varchar2 default null, p_TimeFromParmValue_name in varchar2 default null,
454 p_TimeToParmValue_name in varchar2 default null, p_resp_id in varchar2 default null, p_UserId IN VARCHAR2 default null,
455 p_NlsLangCode in varchar2 default null) RETURN VARCHAR2 IS
456 l_url VARCHAR2(32767);
457 BEGIN
458 l_url := BIS_PMV_PMF_PVT.GET_NOTIFY_RPT_URL( p_measure_id => p_measure_id
459 ,p_region_code => p_region_code
460 ,p_function_name => p_function_name
461 ,p_bplan_name => p_bplan_name
465 ,p_Parm2Level_short_name => p_parm2level_short_name
462 ,p_viewby_level_short_name => p_viewby_level_short_name
463 ,p_Parm1Level_short_name => p_parm1level_short_name
464 ,p_Parm1Value_name => p_parm1value_name
466 ,p_Parm2Value_name => p_parm2value_name
467 ,p_Parm3Level_short_name => p_parm3level_short_name
468 ,p_Parm3Value_name => p_parm3value_name
469 ,p_Parm4Level_short_name => p_parm4level_short_name
470 ,p_Parm4Value_name => p_parm4value_name
471 ,p_Parm5Level_short_name => p_parm5level_short_name
472 ,p_Parm5Value_name => p_parm5value_name
473 ,p_Parm6Level_short_name => p_parm6level_short_name
474 ,p_Parm6Value_name => p_parm6value_name
475 ,p_Parm7Level_short_name => p_parm7level_short_name
476 ,p_Parm7Value_name => p_parm7value_name
477 ,p_Parm8Level_short_name => p_parm8level_short_name
478 ,p_Parm8Value_name => p_parm8value_name
479 ,p_Parm9Level_short_name => p_parm9level_short_name
480 ,p_Parm9Value_name => p_parm9value_name
481 ,p_Parm10Level_short_name => p_parm10level_short_name
482 ,p_Parm10Value_name => p_parm10value_name
483 ,p_Parm11Level_short_name => p_parm11level_short_name
484 ,p_Parm11Value_name => p_parm11value_name
485 ,p_Parm12Level_short_name => p_parm12level_short_name
486 ,p_Parm12Value_name => p_parm12value_name
487 ,p_Parm13Level_short_name => p_parm13level_short_name
488 ,p_Parm13Value_name => p_parm13value_name
489 ,p_TimeParmLevel_short_name => p_TimeParmLevel_short_name
490 ,p_TimeFromParmValue_name => p_TimeFromParmValue_name
491 ,p_TimeToParmValue_name => p_TimeToParmValue_name
492 ,p_resp_id => p_resp_id
493 ,p_UserId => p_UserId
494 ,p_NlsLangCode => p_NlsLangCode);
495 return l_url;
496 END GET_NOTIFY_RPT_URL;
497
498
499 -- Report XML Enhancement Senthil
500 procedure showXmlReport(reportName in varchar2 ,
501 sourceType in varchar2 default 'MDS',
502 pFunctionName in varchar2 ,
503 pApplicationId in varchar2 default NULL,
504 pSessionId in varchar2 default NULL,
505 pUserId in varchar2 default NULL,
506 pResponsibilityId in varchar2 default NULL,
507 pFirstTime in number default 1,
508 pHideParm in varchar2 default 'false',
509 pHideGraph in varchar2 default 'false',
510 pHideTable in varchar2 default 'false',
511 pHideRinfo in varchar2 default 'false',
512 pSortInfo in varchar2 default null,
513 pMode in varchar2 default 'SHOW',
514 pPreFunctionName in varchar2 default NULL,
515 pScheduleId in varchar2 default NULL,
516 pRequestType in varchar2 default 'R',
517 pFileId in number default null,
518 pParameterDisplayOnly in varchar2 default 'N',
519 pForceRun in varchar2 default 'N',
520 pDisplayParameters in varchar2 default 'Y',
521 pReportSchedule in varchar2 default 'Y',
522 pCSVFileName in varchar2 default NULL,
523 pPageId in varchar2 default NULL,
524 pParameters in varchar2 default NULL,
525 -- mdamle 10/10/2002 - Enh# 2460903 - Forecast Graph
526 pEnableForecastGraph in varchar2 default NULL,
527 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
528 pCustomView in varchar2 default NULL,
529 pObjectType IN VARCHAR2 DEFAULT NULL,
530 pautoRefresh IN VARCHAR2 DEFAULT NULL,
531 -- Enh Display Run Button
532 pDispRun IN VARCHAR2 DEFAULT NULL,
533 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
534 pDrillDefaultParameters IN VARCHAR2 DEFAULT NULL,
535 --ugodavar - 10/12/04 - Enh.3946492
536 pMaxResultSetSize IN number DEFAULT NULL,
537 pOutputFormat IN VARCHAR2 DEFAULT NULL
538 ) is
539
540 vSessionId varchar2(80);
541 vUserId varchar2(80);
542 vResponsibilityId varchar2(80);
543
544 jspURL varchar2(32767);
545 jspParams varchar2(32767);
546 functionid NUMBER;
547 l_function VARCHAR2(32000);
548 l_application_id NUMBER;
549 vPageId VARCHAR2(80);
550 x_msg_data VARCHAR2(4000);
551
552
553
554 CURSOR cFndResp (pRespId in varchar2) is
555 select application_id
556 from fnd_responsibility
557 where responsibility_id=pRespId;
558 l_render_type varchar2(200) := 'HTML';
559
560 --serao -for grouped parameters
561 lParamGrp BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type;
562 lTCTExists boolean := false;
563
564 vURL varchar2(32000);
565 l_servlet_agent varchar2(2000);
566 l_transaction_id number;
567 lNestedRegionCode varchar2(250);
568 lAsofdateExists boolean;
569
570
571 begin
572
573 vPageId := pPageId;
574
575
576 if pScheduleId is null then
577 if (not icx_sec.ValidateSession) then
578 return;
579 end if;
580 vSessionId := icx_sec.getID(icx_sec.PV_SESSION_ID);
581 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
582
583 if pResponsibilityId is not null then
584 vResponsibilityId := pResponsibilityId;
585 else
586 vResponsibilityId := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
587 end if;
588
589 else
590 vSessionId := pSessionId;
591 vUserId := pUserId;
592
593 BEGIN
597 EXCEPTION
594 SELECT responsibility_id INTO vResponsibilityId
595 FROM bis_scheduler
596 WHERE schedule_Id = pScheduleId;
598 WHEN OTHERS THEN NULL;
599 END;
600
601 vResponsibilityId := pResponsibilityId;
602 /* Commenting out for XML Reports
603 bis_autoinc_schedule.autoIncrementDates(pRegionCode,
604 pFunctionName,
605 vSessionId,
606 vUserId,
607 vResponsibilityId,
608 pScheduleId);
609 */
610 end if;
611
612 -- nbarik - 06/15/04 - Bug Fix 3687555 - Get it from context
613 l_application_id := FND_GLOBAL.RESP_APPL_ID;
614 IF (l_application_id IS NULL OR l_application_id = -1) THEN
615 OPEN cFNDResp(vResponsibilityId);
616 FETCH cFNDResp INTO l_application_id;
617 CLOSE cFNDResp;
618 END IF;
619
620 BEGIN
621
622 select function_id
623 into functionid
624 from fnd_form_functions
625 where function_name = 'BIS_PMV_REPORT_JSP';
626 -- Maintain the rest of the params, can phase out in a timely manner.
627 -- TODO : cleanup params here
628 -- DEBUG MODE
629
630 jspParams := 'reportName='||BIS_PMV_UTIL.encode(reportName)||'&sourceType='||BIS_PMV_UTIL.encode(sourceType);
631 jspParams := jspParams ||'&functionName='||BIS_PMV_UTIL.encode(pFunctionName);
632 jspParams := jspParams ||'&forceRun='||pForceRun||'¶meterDisplayOnly='||pParameterDisplayOnly||'&displayParameters='||pDisplayParameters;
633 jspParams := jspParams ||'&showSchedule='||pReportSchedule||'&pFirstTime='||pFirstTime||'&pMode='||pMode;
634 jspParams := jspParams ||'&scheduleId='||pScheduleId||'&requestType='||pRequestType;
635 jspParams := jspParams ||'&fileId='||pFileId ||'&pResponsibilityId='||pResponsibilityId;
636 jspParams := jspParams ||'&pUserId='||pUserId||'&pSessionId='||vSessionId;
637 jspParams := jspParams ||'&pApplicationId='||pApplicationId||'&pPreFunctionName='||BIS_PMV_UTIL.encode(pPreFunctionNAme);
638
639 if (pCSVFileName is not null) then
640 jspParams := jspParams ||'&pCSVFileName='||BIS_PMV_UTIL.encode(pCSVFileName);
641 end if;
642 if (vPageId is not null) then
643 jspParams := jspParams ||'&_pageid='||vPageId;
644 end if;
645
646 --Bug Fix 2997706
647 if (pObjectType is not null) then
648 jspParams := jspParams ||'&pObjectType='||pObjectType;
649 end if;
650
651 if (pParameters is not null) then
652 jspParams := jspParams ||'&pParameters='||BIS_PMV_UTIL.encode(pParameters);--Fix for 2445406
653 end if;
654
655 -- mdamle 10/10/2002 - Enh# 2460903 - Forecast Graph
656 if (pEnableForecastGraph is not null) then
657 jspParams := jspParams ||'&pEnableForecastGraph='||pEnableForecastGraph;
658 end if;
659
660 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
661 if (pCustomView is not null) then
662 jspParams := jspParams ||'&pCustomView='||BIS_PMV_UTIL.encode(pCustomView);
663 end if;
664 if (pautorefresh is not null) then
665 jspParams := jspParams || '&autoRefresh='||BIS_PMV_UTIL.encode(pautorefresh);
666 end if;
667 --We need to create an icx transaction. Neal has approved the use of this API.:q
668 --l_Transaction_id := icx_sec. createTransaction(p_session_id => vsessionid);
669
670 -- end if;
671
672
673 -- kiprabha - Enh Display Run Button
674 if (pDispRun is not null) then
675 jspParams := jspParams || '&pDispRun='||pDispRun;
676 end if ;
677
678 --IF (l_DrillDefaultParameters IS NOT NULL) THEN
679 -- jspParams := jspParams || '&' || l_DrillDefaultParameters;
680 --END IF;
681
682 --ugodavar - Enh.3946492
683 if(pMaxResultSetSize is not null) then
684 jspParams := jspParams ||'&pMaxResultSetSize='||pMaxResultSetSize;
685 end if;
686
687 if(pOutputFormat is not null) then
688 jspParams := jspParams ||'&pOutputFormat='||pOutputFormat;
689 end if;
690
691
692 EXCEPTION
693 WHEN OTHERS THEN
694
695 x_msg_data := SQLERRM;
696
697 NULL;
698 END;
699
700
701 OracleApps.runFunction(c_function_id => functionId
702 ,n_session_id => vSessionId
703 ,c_parameters => jspParams
704 ,p_resp_appl_id => l_application_id
705 ,p_responsibility_id => vResponsibilityId
706 ,p_Security_group_id => icx_sec.g_Security_group_id
707 );
708
709 Exception
710 when others then null;
711 end showXmlReport;
712
713
714 end bisviewer;