1 PACKAGE BODY BIS_PMV_PAGE_PARAMS_PUB as
2 /* $Header: BISPPAGB.pls 120.0 2005/06/01 17:31:07 appldev noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.20=120.0):~PROD:~PATH:~FILE
5
6 PROCEDURE GET_KPI_HELP_TARGET
7 (p_function_name IN VARCHAR2
8 ,p_function_parameters IN VARCHAR2
9 ,p_web_html_call IN VARCHAR2
10 ,x_region_application_id IN OUT NOCOPY NUMBER
11 ,x_help_target IN OUT NOCOPY VARCHAR2
12 ,x_return_Status IN OUT NOCOPY VARCHAR2
13 ,x_msg_count IN OUT NOCOPY NUMBER
14 ,x_msg_data IN OUT NOCOPY VARCHAR2
15 );
16
17 PROCEDURE RETRIEVE_LASTUPDATE_DATE
18 (p_user_name IN VARCHAR2
19 ,p_page_id IN VARCHAR2
20 ,p_session_id IN NUMBER default null
21 ,x_last_update_date OUT NOCOPY VARCHAR2
22 ,x_return_Status OUT NOCOPY VARCHAR2
23 ,x_msg_count OUT NOCOPY NUMBER
24 ,x_msg_data OUT NOCOPY VARCHAR2
25 )
26 IS
27 /* Bug 2551994 Modifications to default as of date to sysdate for every login */
28 CURSOR c_lastupdatedate IS
29 SELECT to_char(bua.last_update_date,'RRRR/MM/DD HH24:MI:SS')last_update_date
30 ,bua.attribute_name, bua.session_value, bua.user_id, bua.session_id, bua.function_name
31 FROM bis_user_attributes bua, fnd_user fu
32 WHERE bua.page_id=p_page_id
33 AND bua.user_id = to_char(fu.user_id)
34 AND fu.user_name = p_user_name;
35
36 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
37 --Removed the cursor which was using portal table, but was not reaaly being used
38
39 l_function_name fnd_form_functions.function_name%TYPE;
40 CURSOR c_asofdatefunc IS
41 SELECT parameters
42 FROM fnd_form_functions
43 WHERE function_name = l_function_name and
44 instr(parameters,'pRequestType=P') > 0
45 and instr(parameters, 'pAsOfDate=') > 0;
46 l_session_id varchar2(200);
47 l_icx_session_id varchar2(200);
48 l_user_id varchar2(2000);
49 l_asof_date varchar2(2000);
50 l_attribute_name varchar2(200) := 'AS_OF_DATE';
51 l_asOfDate_parameter varchar2(2000);
52 l_asofdate_func varchar2(2000);
53 l_new_asofdate varchar2(2000);
54 l_index1 number;
55 l_index2 number;
56
57 BEGIN
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59 -- We need to comment the part about icx_sec.validateSession since it is going to fail anyway.
60 /*if (p_Session_id is null) then
61 if (not icx_sec.validateSession) then
62 null;
63 end if;
64 l_icx_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
65 else
66 */
67 l_icx_session_id := p_session_id;
68 --end if;
69
70 for c_rec in c_lastupdatedate loop
71 if (c_rec.attribute_name = 'AS_OF_DATE') then
72 l_asof_date := c_rec.session_value;
73 x_last_update_date := c_rec.last_update_Date;
74 l_user_id := c_rec.user_id;
75 l_session_id := c_rec.session_id;
76 l_function_name := c_rec.function_name;
77 exit;
78 end if;
79 end loop;
80 if ((l_session_id <> l_icx_session_id and l_icx_session_id <> '-1') or l_session_id is null) then
81
82 /* ARU Standards Issue : to_date cannot have a format like MON */
83
84 /*OPEN c_asOfDate;
85 FETCH c_asOfDate INTO l_asOfDate_parameter;
86 CLOSE c_asOfDate;*/
87 if (l_function_name is not null) then
88 OPEN c_asofdatefunc;
89 FETCH c_asofdatefunc into l_asofdate_parameter;
90 CLOSE c_asofdatefunc;
91 end if;
92 if l_asOfDate_parameter is not null then
93 l_index1 := instr(l_asOfDate_parameter, 'pAsOfDate=');
94 l_index2 := instr(l_asOfDate_parameter,'&', l_index1);
95 if l_index2 > 0 then
96 l_asofdate_func := substr(l_asOfDate_parameter,
97 l_index1+length('pAsOfDate='),l_index2-l_index1-length('pAsOfDate='));
98 else
99 l_asofdate_func := substr(l_asOfDate_parameter, l_index1+length('pAsOfDate='));
100 end if;
101
102 --l_asofdate_func := 'select to_char('||l_asofdate_func||',''DD-MON-YYYY'') from dual';
103 --As of Date 3094234--dd/mm/yyyy format
104 l_asofdate_func := 'select to_char('||l_asofdate_func||',''DD/MM/YYYY'') from dual';
105 execute immediate l_asofdate_func into l_new_asofdate;
106
107 if (upper(l_asof_date) <> upper(l_new_asofdate)) then
108
109 update bis_user_attributes
110 set session_value = l_new_asofdate
111 ,session_description = l_new_asofdate
112 , last_update_date = sysdate
113 where page_id=p_page_id
114 and user_id = l_user_id
115 and attribute_name = l_attribute_name;
116 x_last_update_date := to_char(sysdate,'RRRR/MM/DD HH24:MI:SS');
117 commit;
118 end if;
119 else
120
121 --if (l_asof_Date <> to_char(sysdate,'DD-MON-YYYY') and
122 --As of Date 3094234--dd/mm/yyyy format
123 if (l_asof_Date <> to_char(sysdate,'DD/MM/YYYY') and
124 l_Asof_date is not null)
125 then
126 update bis_user_attributes
127 set session_value = to_char(sysdate,'DD/MM/YYYY')
128 ,session_description = to_char(sysdate,'DD/MM/YYYY')
129 --As of Date 3094234--dd/mm/yyyy format
130 --set session_value = to_char(sysdate,'DD-MON-YYYY')
131 --,session_description = to_char(sysdate,'DD-MON-YYYY')
132 , last_update_date = sysdate
133 where page_id=p_page_id
134 and user_id = l_user_id
135 and attribute_name = l_attribute_name;
136 x_last_update_date := to_char(sysdate,'RRRR/MM/DD HH24:MI:SS');
137 commit;
138 end if;
139 end if; --end of l_asOfDate_parameter is not null
140 end if;
141 EXCEPTION
142 WHEN FND_API.G_EXC_ERROR THEN
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
145 x_msg_data);
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
148 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
149 x_msg_data);
150 WHEN OTHERS THEN
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
153 x_msg_data);
154 END;
155
156 PROCEDURE RETRIEVE_PARAMETER_STRING
157 (p_user_name IN VARCHAR2
158 ,p_page_id IN VARCHAR2
159 ,x_param_string OUT NOCOPY VARCHAR2
160 ,x_return_Status OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER
162 ,x_msg_data OUT NOCOPY VARCHAR2
163 )
164 IS
165 CURSOR c_paramstring IS
166 SELECT bua.attribute_name, bua.session_description
167 FROM bis_user_attributes bua, fnd_user fu
168 WHERE bua.page_id=p_page_id AND
169 bua.user_id=to_char(fu.user_id) AND
170 fu.user_name = p_user_name
171 ORDER BY bua.attribute_name;
172
173 l_param_string varchar2(32767);
174 BEGIN
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176 FOR c_rec IN c_paramString LOOP
177 if ( l_param_string is null ) then
178 l_param_string := 'dbiParameters%3DY';
179 end if;
180 l_param_string := l_param_string || '%26' || wfa_html.conv_special_url_chars(c_rec.attribute_name);
181 l_param_string := l_param_string || '%3D'; -- '='
182 l_param_string := l_param_string || wfa_html.conv_special_url_chars(c_Rec.session_description);
183 END LOOP;
184 x_param_string := l_param_string;
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
189 x_msg_data);
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
193 x_msg_data);
194 WHEN OTHERS THEN
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
197 x_msg_data);
198 END;
199 PROCEDURE RETRIEVE_PARAMSTR_BYUSERID
200 (p_user_id IN VARCHAR2
201 ,p_page_id IN VARCHAR2
202 ,x_param_string OUT NOCOPY VARCHAR2
203 ,x_return_Status OUT NOCOPY VARCHAR2
204 ,x_msg_count OUT NOCOPY NUMBER
205 ,x_msg_data OUT NOCOPY VARCHAR2
206 )
207 IS
208 CURSOR c_paramstring IS
209 SELECT bua.attribute_name, bua.session_description
210 FROM bis_user_attributes bua
211 WHERE bua.page_id=p_page_id AND
212 bua.user_id=p_user_id
213 ORDER BY bua.attribute_name;
214
215 l_param_string varchar2(32767);
216 BEGIN
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218 FOR c_rec IN c_paramString LOOP
219 if ( l_param_string is null ) then
220 l_param_string := 'dbiParameters=Y';
221 end if;
222 l_param_string := l_param_string || '&' || wfa_html.conv_special_url_chars(c_rec.attribute_name);
223 l_param_string := l_param_string || '='; -- '='
224 l_param_string := l_param_string || wfa_html.conv_special_url_chars(c_Rec.session_description);
225 END LOOP;
226 x_param_string := l_param_string;
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
231 x_msg_data);
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
235 x_msg_data);
236 WHEN OTHERS THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data =>
239 x_msg_data);
240 END;
241
242 /* This function is duplicated from bis_pmv_util package for the sake of having no dependencies */
243 -- mdamle 12/12/01 - This routine will return the value of a parameter (given the param name) within the parameter string
244 -- defined in form function
245 function getParameterValue(pParameters IN VARCHAR2, pParameterKey IN VARCHAR2) return varchar2 is
246 l_value varchar2(1000);
247 l_index1 number;
248
249 l_value_begin number;
250 l_value_end number;
251
252 begin
253 l_index1 := instr(pParameters, pParameterKey||'=');
254
255 if l_index1 > 0 then
256 l_value_begin := l_index1 + length(pParameterKey||'=');
257
258 l_value_end := instr(pParameters, '&', l_value_begin);
259
260 if l_value_end > 0 then
261 l_value := substr(pParameters, l_value_begin, l_value_end - l_value_begin);
262 else
263 l_value := substr(pParameters, l_value_begin);
264 end if;
265
266 else
267 l_value := '';
268 end if;
269
270 return l_value;
271
272 end getParameterValue;
273
274
275 /* Returns the application id and the help target for the gevn args */
276 PROCEDURE GET_HELP_TARGET
277 (p_function_name IN VARCHAR2
278 ,p_function_parameters IN VARCHAR2
279 ,p_web_html_call IN VARCHAR2
280 ,x_region_application_id OUT NOCOPY NUMBER
281 ,x_help_target OUT NOCOPY VARCHAR2
282 ,x_return_Status OUT NOCOPY VARCHAR2
283 ,x_msg_count OUT NOCOPY NUMBER
284 ,x_msg_data OUT NOCOPY VARCHAR2
285 ) IS
286
287 lAKRegionCode VARCHAR2(30);
288 lFunctionName VARCHAR2(30);
289 lRegionCode VARCHAR2(30);
290 lRegionHelpTarget VARCHAR2(256);
291
292 CURSOR getRegionAttributes (pRegionCode IN VARCHAR2) IS
293 SELECT region_application_id, help_target
294 FROM ak_regions
295 WHERE region_code = pRegionCode ;
296
297 CURSOR getFndHelpTarget IS
298 SELECT target_name
299 FROM fnd_help_targets
300 WHERE target_name = p_Function_Name ;
301
302 BEGIN
303
304
305 -- The application_id column of fnd_form_functions could be null, so we use the
306 --corr region_code to get the application_id
307 -- get the help_target and the application_id from the region
308 lAKRegionCode := getParameterValue(pParameters => p_web_html_call,
309 pParameterKey => 'akRegionCode' );
310
311 -- bug 2661052- do not show help for related links portlet
312 IF NOT (lAKRegionCode = 'BIS_PM_RELATED_LINK_LAYOUT' ) THEN
313
314 --assumption that most of the product teams use function name as their help
315 -- target name
316
317 IF (getFndHelpTarget%ISOPEN ) THEN
318 CLOSE getFndHelpTarget;
319 END IF;
320 OPEN getFndHelpTarget;
321 FETCH getFndHelpTarget INTO x_help_target;
322 CLOSE getFndHelpTarget;
323
324
325 lRegionCode := getParameterValue(pParameters => p_function_parameters,
326 pParameterKey => 'pRegionCode' );
327
328 IF (getRegionAttributes%ISOPEN ) THEN
329 CLOSE getRegionAttributes;
330 END IF;
331 OPEN getRegionAttributes(lRegionCode);
332 FETCH getRegionAttributes INTO x_region_application_id, lRegionHelpTarget;
333 CLOSE getRegionAttributes;
334
335 IF (x_help_target IS NULL) THEN
336 x_help_target := lRegionHelpTarget;
337 END IF;
338
339 IF (lAKRegionCode = 'BIS_PMF_PORTLET_TABLE_LAYOUT' ) THEN -- PMF
340 GET_KPI_HELP_TARGET(
341 p_function_name => p_function_name
342 ,p_function_parameters => p_function_parameters
343 ,p_web_html_call => p_web_html_call
344 ,x_region_application_id => x_region_application_id
345 ,x_help_target => x_help_target
346 ,x_return_Status => x_return_Status
347 ,x_msg_count => x_msg_count
348 ,x_msg_data => x_msg_data
349 );
350 END IF;
351 END IF; -- not related link
352
353 EXCEPTION
354 WHEN FND_API.G_EXC_ERROR THEN
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
357 p_data =>x_msg_data);
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
361 p_data =>x_msg_data);
362 WHEN OTHERS THEN
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
365 p_data => x_msg_data);
366
367
368 END GET_HELP_TARGET;
369
370 PROCEDURE GET_KPI_HELP_TARGET
371 (p_function_name IN VARCHAR2
372 ,p_function_parameters IN VARCHAR2
373 ,p_web_html_call IN VARCHAR2
374 ,x_region_application_id IN OUT NOCOPY NUMBER
375 ,x_help_target IN OUT NOCOPY VARCHAR2
376 ,x_return_Status IN OUT NOCOPY VARCHAR2
377 ,x_msg_count IN OUT NOCOPY NUMBER
378 ,x_msg_data IN OUT NOCOPY VARCHAR2
379 ) IS
380
381 CURSOR getFormFunAppId IS
382 SELECT fa.APPLICATION_ID
383 FROM fnd_application fa
384 WHERE fa.APPLICATION_SHORT_NAME = SUBSTR(p_Function_Name,1,3) ;
385 BEGIN
386
387 -- when using form function level, set the application id correctly
388 IF ((x_region_application_id IS NULL) AND
389 (x_help_target = p_function_name)) THEN
390
391 IF (getFormFunAppId%ISOPEN ) THEN
392 CLOSE getFormFunAppId;
393 END IF;
394 OPEN getFormFunAppId;
395 FETCH getFormFunAppId INTO x_region_application_id;
396 CLOSE getFormFunAppId;
397
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF (getFormFunAppId%ISOPEN ) THEN
403 CLOSE getFormFunAppId;
404 END IF;
405 END GET_KPI_HELP_TARGET;
406
407 END BIS_PMV_PAGE_PARAMS_PUB;
[Home] [Help]