[Home] [Help]
PACKAGE BODY: APPS.ZPB_UTIL_PVT
Source
1 PACKAGE BODY ZPB_UTIL_PVT AS
2 /* $Header: ZPBUTILB.pls 120.6 2007/12/04 14:36:01 mbhat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(15) := 'zpb_util_pvt';
5
6 function compare_queries(p_dataAw IN varchar2,
7 p_first_query IN varchar2,
8 p_second_query IN varchar2,
9 p_line_dim IN varchar2) return integer
10 AS
11 l_api_name CONSTANT VARCHAR2(30) := 'compare_queries';
12 l_vs varchar2(100);
13 l_dataAwQual varchar2(70);
14 l_first_superset boolean;
15 l_second_superset boolean;
16 l_equal integer;
17 begin
18 l_dataAwQual := p_dataAw ||'!';
19 -- call the first query
20 zpb_aw_status.get_status(p_dataAw,p_first_query);
21 -- get the valuseset name
22 l_vs := '&' || 'obj(prp ''LASTQUERYVS'' '||''''||l_dataAwQual||p_line_dim ||''')';
23 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'valueset name:' ||l_vs);
24 -- initialize
25 zpb_aw.execute('push oknullstatus '||l_dataAwQual ||p_line_dim);
26 zpb_aw.execute('oknullstatus=y');
27 if (not zpb_aw.interpbool('shw exists(''l_temp_vs'')')) then
28 zpb_aw.execute(' dfn l_temp_vs valueset '||l_dataAwQual ||p_line_dim|| ' aw ' ||p_dataAw);
29 end if;
30
31 -- lmt the first valueset to the first query
32 zpb_aw.execute('lmt '|| l_dataAwQual ||'l_temp_vs to '|| l_vs );
33
34 -- generate the valuseset for the second query
35 zpb_aw_status.get_status(p_dataAw,p_second_query);
36 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr w 40 values('||l_dataAwQual ||'l_temp_vs)'),1,254));
37 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr w 40 values('||l_vs||')'),1,254));
38
39 -- check if the two valusesets are identical
40 l_first_superset := zpb_aw.interpbool('shw inlist(values('||l_dataAwQual||'l_temp_vs)'|| ' values('||l_vs||'))');
41 l_second_superset := zpb_aw.interpbool('shw inlist(values('||l_dataAwQual||l_vs||')'|| ' values(l_temp_vs))');
42
43 if l_first_superset then
44 if l_second_superset then
45 l_equal := 0;
46 else
47 l_equal := 1;
48 end if;
49 else
50 if l_second_superset then
51 l_equal := 2;
52 else
53 l_equal := 3;
54 end if;
55 end if;
56
57 return l_equal;
58 exception
59 when others then
60 l_equal := 0;
61 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90)
62 );
63 return l_equal;
64 end;
65
66 PROCEDURE compare_dim_members(p_dim_name IN varchar2,
67 p_first_query IN varchar2,
68 p_second_query IN varchar2,
69 x_equal OUT NOCOPY integer) IS
70 l_api_name CONSTANT VARCHAR2(30) := 'compare_line_members';
71 l_dataAw varchar2(30);
72 l_dataAwQual varchar2(70);
73 l_temp_vs varchar2(100);
74 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
75
76 begin
77 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
78 l_dataAwQual := l_dataAw ||'!';
79 zpb_aw.execute('aw attach '|| l_dataAw || ' first ');
80 zpb_aw.execute('aw attach '|| zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(fnd_global.user_id) || ' ro');
81
82 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'Dimension:' ||l_dataAwQual|| p_dim_name);
83
84 x_equal := compare_queries(l_dataAw,p_first_query,p_second_query,p_dim_name);
85 -- cleanup and return
86 zpb_aw.execute('delete l_temp_vs aw ' ||l_dataAw);
87 zpb_aw.execute('pop oknullstatus '||l_dataAwQual ||p_dim_name);
88 zpb_aw.execute('aw detach '|| l_dataAw );
89 zpb_aw.execute('aw detach '|| zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(fnd_global.user_id) );
90
91 exception
92 when others then
93 x_equal := 0;
94 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
95
96 end compare_dim_members;
97
98
99 -- b 4948928
100 -- Set_Expired_Users
101 -- sets expired user to temporary table to be used as list for notification
102 -- IN
103 -- p_session_id Number
104 -- P_User_Name VARCHAR2
105 -- OUT
106
107 procedure set_expired_users(p_session_id in number, p_user_name in VARCHAR2)
108
109 is
110
111 begin
112
113 INSERT INTO ZPB_WF_INACTIVE_USERS_GT(
114 SESSION_ID,
115 USER_NAME
116
117 )
118 VALUES(
119 p_session_id,
120 p_user_name
121 );
122
123 return;
124
125 exception
126 when others then
127 raise;
128
129 end Set_Expired_Users;
130
131
132
133 --
134 -- String_To_UserTable
135 -- Converts a comma/space delimited string of users into a UserTable
136 -- IN
137 -- P_UserList VARCHAR2
138 -- OUT
139 -- RETURN
140 -- P_UserTable WF_DIRECTORY.UserTable
141 --
142
143 procedure String_To_UserTable (p_UserList in VARCHAR2,
144 p_UserTable out NOCOPY WF_DIRECTORY.UserTable)
145 is
146
147 c1 integer;
148 u1 integer := 0;
149 l_userList varchar2(32000);
150 l_users WF_DIRECTORY.UserTable;
151 l_sessionID number;
152
153
154
155 begin
156
157
158 if (p_UserList is not NULL) then
159
160 -- Set sessionID for expired users;
161 select SYS_CONTEXT('USERENV','SESSIONID') into l_sessionID From dual;
162 --
163 -- Substring and insert users into UserTable
164 --
165 l_userList := ltrim(p_UserList);
166 <<UserLoop>>
167 loop
168 c1 := instr(l_userList, ',');
169 if (c1 = 0) then
170
171 -- b4948928 check if valid user. if not do not add it to table.
172 -- add it to expired list
173 if wf_directory.useractive(l_userList) = TRUE then
174 p_UserTable(u1) := l_userList;
175 u1 := u1 + 1;
176 else
177 set_expired_users(l_sessionID, l_userList);
178 end if;
179
180 exit;
181
182 else
183 -- b4948928 check if valid user. if not do not add it to table.
184 -- add it to expired list
185 if wf_directory.useractive(substr(l_userList, 1, c1-1)) = TRUE then
186 p_UserTable(u1) := substr(l_userList, 1, c1-1);
187 u1 := u1 + 1;
188 else
189 set_expired_users(l_sessionID, substr(l_userList, 1, c1-1));
190 end if;
191
192 end if;
193
194 --u1 := u1 + 1;
195 l_userList := ltrim(substr(l_userList, c1+1));
196 end loop UserLoop;
197 end if;
198
199 end String_To_UserTable;
200
201 --
202 -- This procedure is written mainly to handle user names which include
203 -- 'Space' in them viz 'GOPI NATH'
204 -- Here, for converting role_users String to Table we call our private procedure
205 -- instead of WF_DIRECTORY.String_To_UserTable
206 -- IN
207 -- role_name - AdHoc role name
208 -- role_users - Space or comma delimited list of apps-based users
209 -- or adhoc users
210 -- OUT
211 --
212 procedure AddUsersToAdHocRole(role_name in varchar2,
213 role_users in varchar2)
214 is
215 l_users WF_DIRECTORY.UserTable;
216
217 begin
218
219 if (role_users is NOT NULL) then
220 ZPB_UTIL_PVT.String_To_UserTable (p_UserList=>AddUsersToAdHocRole.role_users,
221 p_UserTable=>l_users);
222
223 WF_DIRECTORY.AddUsersToAdHocRole2(role_name=>AddUsersToAdHocRole.role_name,
224 role_users=>l_users);
225 end if;
226
227 exception
228 when others then
229 wf_core.context('ZPB_UTIL_PVT', 'AddUsersToAdHocRole',
230 role_name, '"'||role_users||'"');
231 raise;
232 end AddUsersToAdHocRole;
233
234 ----------------------------------------------------------------------------
235 -- CLOBToChar - Function that converts a CLOB to a VARCHAR2, unless the
236 -- CLOB is greater than 3900 characters, in which case it
237 -- returns null. Used for optimization in SV.GET.SOLVEDEF
238 ----------------------------------------------------------------------------
239 function CLOBToChar(p_clob in CLOB) return VARCHAR2
240 is
241 begin
242 if (length(p_clob) < 3900) then
243 return to_char(p_clob);
244 else
245 return null;
246 end if;
247 end CLOBToChar;
248
249 --
250 -- This procedure is to populate model_equation and calc_parameters columns
251 -- into SV.DEF.VAR variable .The same cant be done in olap program like other
252 -- columns as these two columns are are of CLOB data type and hence OLAP cant
253 -- recognize them. DBMS_AW.INTERPCLOB( ) procedure takes the clob parameter and
254 -- executes it as a olap command. This procedure is been called from
255 -- SV.GET.SOLVEDEF program on make effective
256 -- Bug 4036563 .
257 --
258
259 PROCEDURE populate_SVDEFVAR
260 (p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE)
261 is
262 CURSOR records_cur IS
263 SELECT model_equation,calc_parameters,member
264 FROM zpb_solve_member_defs
265 WHERE analysis_cycle_id = p_ac_id
266 and source_type = 1200;
267
268 l_modelEquation ZPB_SOLVE_MEMBER_DEFS.MODEL_EQUATION%TYPE;
269 l_calcParam ZPB_SOLVE_MEMBER_DEFS.CALC_PARAMETERS%TYPE;
270 l_modelEquationres ZPB_SOLVE_MEMBER_DEFS.MODEL_EQUATION%TYPE;
271 l_calcParamres ZPB_SOLVE_MEMBER_DEFS.CALC_PARAMETERS%TYPE;
272 offset number := 1;
273 opRec zpb_solve_member_defs%ROWTYPE;
274 i number;
275 indx number;
276 begin
277 ZPB_AW.execute('push SV.LN.DIM');
278
279 for opRec in records_cur loop
280 ZPB_AW.EXECUTE('lmt SV.LN.DIM to '''||opRec.Member||'''');
281 l_modelEquationRes := opRec.model_equation ;
282 l_calcParamRes := opRec.CALC_PARAMETERS;
283
284 if (ZPB_AW.INTERPBOOL('shw SV.DEF.VAR(SV.DEF.PROP.DIM ''EQUATION'') eq NA')) then
285 dbms_lob.CREATETEMPORARY(l_modelEquation, true);
286 --iterating through each clob to replace ' with \'
287
288 offset := dbms_lob.instr(l_modelEquationres, '\');
289 while (offset <> 0)
290 loop
291 l_modelEquationres := dbms_lob.substr(l_modelEquationres,offset-1,1)||
292 '\'||dbms_lob.substr(lob_loc => l_modelEquationres,
293 offset => offset);
294 offset := dbms_lob.instr(l_modelEquationres, '\', offset+2);
295 end loop;
296 offset := dbms_lob.instr(l_modelEquationres, '''');
297
298 while (offset <> 0)
299 loop
300 l_modelEquationres := dbms_lob.substr(l_modelEquationres,offset-1,1)||
301 '\'||dbms_lob.substr(lob_loc => l_modelEquationres,
302 offset => offset);
303 offset := dbms_lob.instr(l_modelEquationres, '''', offset+2);
304 end loop;
305
306 l_modelEquation :=
307 'SV.DEF.VAR(SV.DEF.PROP.DIM ''EQUATION'') = joinlines( ';
308 indx := 1;
309 loop
310 i := dbms_lob.instr(l_modelEquationres, ')', indx+3800);
311 if (i > 0) then
312 l_modelEquation := l_modelEquation || ' - '||fnd_global.newline()||
313 ''''||dbms_lob.substr(l_modelEquationres,i-indx+1,indx)||'''';
314 indx := i+1;
315 else
316 l_modelEquation := l_modelEquation || ' - '||fnd_global.newline()||
317 ''''||dbms_lob.substr(l_modelEquationres,4000,indx)||'''';
318 exit;
319 end if;
320 end loop;
321 l_modelEquation := l_modelEquation || ')';
322 l_modelEquationres := DBMS_AW.INTERPCLOB(l_modelEquation);
323 end if;
324 if (ZPB_AW.INTERPBOOL('shw SV.DEF.VAR(SV.DEF.PROP.DIM ''CALC_PARAMS'') eq NA')) then
325 -- for calc_parameters column
326 dbms_lob.CREATETEMPORARY(l_calcParam, true);
327
328 offset := dbms_lob.instr(l_calcParamRes, '\');
329 while (offset <> 0)
330 loop
331 l_calcParamres := dbms_lob.substr(l_calcParamres, offset-1, 1)||
332 '\'||dbms_lob.substr(lob_loc => l_calcParamres, offset => offset);
333 offset := dbms_lob.instr(l_calcParamres, '\', offset+2);
334 end loop;
335 offset := dbms_lob.instr(l_calcParamres, '''');
336
337 while (offset <> 0)
338 loop
339 l_calcParamres := dbms_lob.substr(l_calcParamres, offset-1, 1)||
340 '\'||dbms_lob.substr(lob_loc => l_calcParamres, offset => offset);
341 offset := dbms_lob.instr(l_calcParamres, '''', offset+2);
342 end loop;
343
344 offset := dbms_lob.instr(l_calcParamres, fnd_global.newline());
345 while (offset <> 0)
346 loop
347 if (dbms_lob.substr(l_calcParamres, offset-1, 1) <> '-')
348 then
349 l_calcParamres := dbms_lob.substr(l_calcParamres, offset-1, 1)||
350 '-'||dbms_lob.substr(lob_loc => l_calcParamres, offset => offset);
351 end if;
352 offset := dbms_lob.instr(l_calcParamres, fnd_global.newline(), offset+2);
353 end loop;
354
355
356 l_calcParam :=
357 'SV.DEF.VAR(SV.DEF.PROP.DIM ''CALC_PARAMS'') = joinlines( ';
358 indx := 1;
359 loop
360 i := dbms_lob.instr(l_calcParamres, ')', indx+3800);
361 if (i > 0) then
362 l_calcParam := l_calcParam || ' - '||fnd_global.newline()||
363 ''''||dbms_lob.substr(l_calcParamres,i-indx+1,indx)||'''';
364 indx := i+1;
365 else
366 l_calcParam := l_calcParam || ' - '||fnd_global.newline()||
367 ''''||dbms_lob.substr(l_calcParamres,4000,indx)||'''';
368 exit;
369 end if;
370 end loop;
371 l_calcParam := l_calcParam || ')';
372 l_calcParamres := DBMS_AW.INTERPCLOB(l_calcParam);
373 end if;
374 end loop;
375 zpb_aw.execute('pop SV.LN.DIM ');
376 end populate_SVDEFVAR ;
377
378 -- This procedure modified the olap page pool size session parameter
379 -- setting_id corresponds to ZPB profile parameters. If the corresponding profile
380 -- is not set, the page pool size is unchanged
381 -- 1 = ZPB_OPPS_DATA_MOVE
382 -- 2 = ZPB_OPPS_DATA_SOLVE
383 -- 3 = ZPB_OPPS_AW_BUILD
384 procedure set_opps(setting_id in number, user_id in number) is
385
386 l_api_name CONSTANT VARCHAR2(30) := 'set_opps';
387 l_callbase varchar2(64);
388 l_callnumber varchar2(64);
389 l_call varchar2(64);
390
391 begin
392 l_callbase := 'alter session set olap_page_pool_size=';
393
394 if setting_id = ZPB_UTIL_PVT.ZPB_OPPS_DATA_MOVE then
395 l_callnumber:=FND_PROFILE.VALUE_SPECIFIC('ZPB_OPPS_DATA_MOVE', user_id);
396 end if;
397
398 if setting_id = ZPB_UTIL_PVT.ZPB_OPPS_DATA_SOLVE then
399 l_callnumber:=FND_PROFILE.VALUE_SPECIFIC('ZPB_OPPS_DATA_SOLVE', user_id);
400 end if;
401
402 if setting_id = ZPB_UTIL_PVT.ZPB_OPPS_AW_BUILD then
403 l_callnumber:=FND_PROFILE.VALUE_SPECIFIC('ZPB_OPPS_AW_BUILD', user_id);
404 end if;
405
406 l_call := l_callbase || l_callnumber;
407
408 if l_callnumber is not null then
409 execute immediate l_call;
410 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'executed ' ||l_call);
411 end if;
412
413 exception
414 when others then
415 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
416 end set_opps;
417
418 -- This function returns the current olap page pool size
419 function get_opps return number is
420 l_api_name CONSTANT VARCHAR2(30) := 'get_opps';
421 return_val varchar2(32);
422 begin
423 select value into return_val
424 from v$parameter
425 where name='olap_page_pool_size';
426
427 return to_number(return_val);
428
429 exception
430 when others then
431 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
432 return to_number(return_val);
433 end get_opps;
434
435 procedure set_opps_spec(setting in number) is
436 l_api_name CONSTANT VARCHAR2(30) := 'set_opps_spec';
437 l_callbase varchar2(64);
438 l_call varchar2(64);
439 begin
440 if setting is not null then
441 l_callbase := 'alter session set olap_page_pool_size=';
442 l_call := l_callbase || to_char(setting);
443 execute immediate l_call;
444 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'executed ' ||l_call);
445 end if;
446 exception
447 when others then
448 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
449 end set_opps_spec;
450
451 procedure exec_ddl(p_cmd varchar2) is
452
453 begin
454 execute immediate p_cmd;
455 end exec_ddl;
456
457 end ZPB_UTIL_PVT;