1 PACKAGE BODY ZPB_AW AS
2 /* $Header: zpbaw.plb 120.14 2007/12/04 14:43:17 mbhat ship $ */
3
4 m_ascii_nl constant number := ascii(fnd_global.local_chr(10));
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_AW';
7 G_SCHEMA VARCHAR2(16);
8
9 -------------------------------------------------------------------------------
10 -- EXECUTE
11 --
12 -- Function to call dbms_aw.execute
13 --
14 -- IN: p_cmd (varchar2) - The AW command to execute
15 --
16 -------------------------------------------------------------------------------
17 procedure EXECUTE (p_cmd in varchar2)
18 is
19 begin
20 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
21 zpb_log.write_statement ('zpb_aw.execute',
22 'Executing AW Statement: '||p_cmd);
23 end if;
24 dbms_aw.execute(p_cmd);
25 end execute;
26
27 -------------------------------------------------------------------------------
28 -- INTERP <-- DEPRECATED - Use EVAL_TEXT or EVAL_NUMBER instead -->
29 --
30 -- Wrapper around the call to the AW, which will parse the output. If no
31 -- output is expected, you may just run zpb_aw.execute() instead.
32 --
33 -- IN: p_cmd (varchar2) - The AW command to execute
34 -- OUT: varchar2 - The output of the the AW command
35 --
36 -------------------------------------------------------------------------------
37 function INTERP (p_cmd in varchar2)
38 return varchar2
39 is
40 l_return varchar2(4000);
41 begin
42 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
43 zpb_log.write_statement ('zpb_aw.interp',
44 'Interpreting AW Statement: '||p_cmd);
45
46 l_return := dbms_lob.substr(dbms_aw.interp (p_cmd), 4000);
47
48 zpb_log.write_statement ('zpb_aw.interp',
49 'AW Statement returned: '||l_return);
50 return l_return;
51 else
52 return dbms_lob.substr(dbms_aw.interp (p_cmd), 4000);
53 end if;
54 end INTERP;
55
56 -------------------------------------------------------------------------------
57 -- INTERPBOOL
58 --
59 -- Wrapper around the call the AW with boolean (yes/no) output expected.
60 -- Will handle conversion within the NLS_LANGUAGE setting (Bug 4058390).
61 --
62 -- IN: p_cmd (varchar2) - The AW boolean command to execute
63 -- OUT: boolean - The output of the the AW command
64 --
65 -------------------------------------------------------------------------------
66 function INTERPBOOL (p_cmd in varchar2)
67 return boolean is
68 l_return boolean;
69 begin
70 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
71 zpb_log.write_statement ('zpb_aw.interpbool',
72 'Interpreting AW Statement: '||p_cmd);
73 end if;
74
75 if (dbms_aw.eval_number('if nafill('||substr(p_cmd, 5)||
76 ', no) then 1 else 0') = 1) then
77 l_return := true;
78 else
79 l_return := false;
80 end if;
81
82 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
83 if (l_return) then
84 zpb_log.write_statement ('zpb_aw.interpbool',
85 'AW Statement returned true');
86 else
87 zpb_log.write_statement ('zpb_aw.interpbool',
88 'AW Statement returned false');
89 end if;
90 end if;
91 return l_return;
92 end INTERPBOOL;
93
94 -------------------------------------------------------------------------------
95 -- EVAL_TEXT
96 --
97 -- Improved version of INTERP, which avoids many OLAP bugs. No need to
98 -- use "show" in front of command. Returns text-based queries, null if NA.
99 -- -
100 -- IN: p_cmd (varchar2) - The AW command to execute
101 -- OUT: varchar2 - The output of the the AW command
102 --
103 -------------------------------------------------------------------------------
104 function EVAL_TEXT (p_cmd in VARCHAR2)
105 return VARCHAR2 is
106 l_return varchar2(4000);
107 begin
108 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
109 zpb_log.write_statement ('zpb_aw.eval_text',
110 'Interpreting AW Statement: '||p_cmd);
111
112 l_return := dbms_lob.substr(dbms_aw.eval_text(p_cmd), 4000);
113
114 zpb_log.write_statement ('zpb_aw.eval_text',
115 'AW Statement returned: '||l_return);
116 return l_return;
117 else
118 return dbms_lob.substr(dbms_aw.eval_text(p_cmd), 4000);
119 end if;
120 end EVAL_TEXT;
121
122 -------------------------------------------------------------------------------
123 -- EVAL_NUMBER
124 --
125 -- Improved version of INTERP, which avoids many OLAP bugs. No need to
126 -- use "show" in front of command. Returns numeric queries, null if NA.
127 -- -
128 -- IN: p_cmd (varchar2) - The AW command to execute
129 -- OUT: varchar2 - The output of the the AW command
130 --
131 -------------------------------------------------------------------------------
132 function EVAL_NUMBER (p_cmd in VARCHAR2)
133 return NUMBER is
134 l_return NUMBER;
135 begin
136 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
137 zpb_log.write_statement ('zpb_aw.eval_text',
138 'Interpreting AW Statement: '||p_cmd);
139
140 l_return := dbms_aw.eval_number(p_cmd);
141
142 zpb_log.write_statement ('zpb_aw.eval_text',
143 'AW Statement returned: '||l_return);
144 return l_return;
145 else
146 return dbms_aw.eval_number(p_cmd);
147 end if;
148 end EVAL_NUMBER;
149 ------------------------------------------------------------------------------
150 -- DETACH_ALL
151 --
152 -- Detaches all AW's on the session
153 ------------------------------------------------------------------------------
154 procedure DETACH_ALL is
155 l_aws VARCHAR2(4000);
156 l_aw VARCHAR2(30);
157 i NUMBER;
158 j NUMBER;
159 begin
160 l_aws := INTERP
161 ('shw blankstrip(joinchars(joincols (aw (list) '' '')) BOTH)');
162 i := 1;
163 loop
164 j := instr (l_aws, ' ', i);
165 if (j = 0) then
166 l_aw := substr (l_aws, i);
167 else
168 l_aw := substr (l_aws, i, j-i);
169 i := j+1;
170 end if;
171
172 if (l_aw <> 'EXPRESS') then
173 EXECUTE ('aw detach '||l_aw);
174 end if;
175
176 exit when j=0;
177 end loop;
178 end DETACH_ALL;
179
180 -------------------------------------------------------------------------------
181 -- GET_ANNOTATION_AW
182 --
183 -- Returns the un-qualified (schema not prepended) annotation aw name for
184 -- the business area.
185 --
186 -- IN: p_business_area_id (number) - The Business Area ID. If null, then
187 -- uses the Business Area in context, or
188 -- the business area currently logged in as
189 -------------------------------------------------------------------------------
190 function GET_ANNOTATION_AW
191 (p_business_area_id IN ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type)
192 return ZPB_BUSINESS_AREAS.ANNOTATION_AW%type is
193 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
194 l_annotation_aw ZPB_BUSINESS_AREAS.ANNOTATION_AW%type;
195 begin
196 l_business_area_id := nvl(p_business_area_id,
197 sys_context('ZPB_CONTEXT', 'business_area_id'));
198 if (l_business_area_id is null) then
199 select BUSINESS_AREA_ID
200 into l_business_area_id
201 from ZPB_CURRENT_USER_V;
202 end if;
203
204 select ANNOTATION_AW
205 into l_annotation_aw
206 from ZPB_BUSINESS_AREAS
207 where BUSINESS_AREA_ID = l_business_area_id;
208
209 return l_annotation_aw;
210 end GET_ANNOTATION_AW;
211
212 -------------------------------------------------------------------------------
213 -- GET_CODE_AW
214 --
215 -- Returns the un-qualified (schema not prepended) code aw name for
216 -- this user.
217 --
218 -- IN: p_user (varchar2) - The FND_USER USER_ID
219 -------------------------------------------------------------------------------
220 function GET_CODE_AW (p_user in varchar2)
221 return varchar2 is
222 begin
223 return FND_PROFILE.VALUE_SPECIFIC('ZPB_CODE_AW_NAME',
224 to_number(p_user));
225 end GET_CODE_AW;
226
227 -------------------------------------------------------------------------------
228 -- GET_PERSONAL_AW
229 --
230 -- Returns the un-qualified (schema not prepended) personal aw name for
231 -- this user.
232 --
233 -- IN: p_user (varchar2) - The FND_USER USER_ID
234 -------------------------------------------------------------------------------
235 function GET_PERSONAL_AW (p_user in varchar2,
236 p_business_area_id in ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type)
237 return ZPB_USERS.PERSONAL_AW%type is
238 l_user FND_USER.USER_ID%type;
239 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
240 l_personal_aw ZPB_USERS.PERSONAL_AW%type;
241 begin
242 l_business_area_id := nvl(p_business_area_id,
243 sys_context('ZPB_CONTEXT', 'business_area_id'));
244 if (l_business_area_id is null) then
245 select BUSINESS_AREA_ID
246 into l_business_area_id
247 from ZPB_CURRENT_USER_V;
248 end if;
249
250 l_user := to_number(nvl(p_user, sys_context('ZPB_CONTEXT', 'shadow_id')));
251 if (l_user is null) then
252 select SHADOW_ID
253 into l_user
254 from ZPB_CURRENT_USER_V;
255 end if;
256
257 begin
258 select PERSONAL_AW
259 into l_personal_aw
260 from ZPB_USERS
261 where BUSINESS_AREA_ID = l_business_area_id
262 and USER_ID = l_user;
263 exception
264 when no_data_found then
265 null;
266 end;
267 return l_personal_aw;
268 end GET_PERSONAL_AW;
269
270 -------------------------------------------------------------------------------
271 -- GET_SHARED_AW
272 --
273 -- Returns the un-qualified (schema not prepended) shared aw name for
274 -- the business area.
275 --
276 -- IN: p_business_area_id (number) - The Business Area ID. If null, then
277 -- uses the Business Area in context, or
278 -- the business area currently logged in as
279 -------------------------------------------------------------------------------
280 function GET_SHARED_AW
281 (p_business_area_id IN ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type)
282 return ZPB_BUSINESS_AREAS.DATA_AW%type is
283 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
284 l_shared_aw ZPB_BUSINESS_AREAS.DATA_AW%type;
285 begin
286 l_business_area_id := nvl(p_business_area_id,
287 sys_context('ZPB_CONTEXT', 'business_area_id'));
288 if (l_business_area_id is null) then
289 select BUSINESS_AREA_ID
290 into l_business_area_id
291 from ZPB_CURRENT_USER_V;
292 end if;
293
294 select DATA_AW
295 into l_shared_aw
296 from ZPB_BUSINESS_AREAS
297 where BUSINESS_AREA_ID = l_business_area_id;
298
299 return l_shared_aw;
300 end GET_SHARED_AW;
301
302 -------------------------------------------------------------------------------
303 -- GET_SCHEMA
304 --
305 -- Returns the schema where the aw's reside
306 -------------------------------------------------------------------------------
307 function GET_SCHEMA
308 return varchar2 is
309 begin
310 if (G_SCHEMA is not null) then
311 return G_SCHEMA;
312 end if;
313
314 select ORACLE_USERNAME
315 into G_SCHEMA
316 from FND_ORACLE_USERID a,
317 FND_APPLICATION b,
318 FND_PRODUCT_INSTALLATIONS c
319 where a.ORACLE_ID = c.ORACLE_ID
320 and c.APPLICATION_ID = b.APPLICATION_ID
321 and b.APPLICATION_SHORT_NAME = 'ZPB';
322
323 return G_SCHEMA;
324 end GET_SCHEMA;
325
326 -------------------------------------------------------------------------------
327 -- GET_AW_SHORT_NAME
328 --
329 -- Procedure to get the AW short name, used in CWM and view names. If a
330 -- personal AW is passed in, will use the username. Otherwise, is the same as
331 -- the AW actual name
332 --
333 -- IN: p_aw (varchar2) - The actual name of the AW
334 -- OUT: varchar2 - The short name of the AW
335 --
336 -------------------------------------------------------------------------------
337 function GET_AW_SHORT_NAME (p_aw in varchar2) return varchar2 is
338 l_return varchar2 (16);
339 l_aw varchar2 (64);
340 begin
341 l_aw := p_aw;
342 if (instr (l_aw, '.') > 0) then
343 l_aw := substr (l_aw, instr (l_aw, '.')+1);
344 end if;
345 if (instr (l_aw, '_') > 0) then
346 l_return := upper (substr (l_aw, 1, instr (l_aw, '_') - 1));
347 else
348 l_return := upper (l_aw);
349 end if;
350 return l_return;
351 end GET_AW_SHORT_NAME;
352
353 -------------------------------------------------------------------------------
354 -- GET_AW_TINY_NAME
355 --
356 -- Procedure to get ZPB followed by business area id from ZPB.ZPBDATAXXX
357 -- Used in CWM and view names. If a personal AW is passed in,
358 -- its name will not be changed other than the stripping of schema prefix.
359 --
360 -- IN: p_aw (varchar2) - The actual name of the AW
361 -- OUT: varchar2 - ZPB + BA_ID
362 --
363 -------------------------------------------------------------------------------
364 function GET_AW_TINY_NAME (p_aw in varchar2) return varchar2 is
365 l_return varchar2 (16);
366 j number;
367 begin
368
369 l_return := get_aw_short_name(p_aw);
370 j :=instr(l_return, 'DATA');
371
372 if j> 0 then
373 l_return := substr(l_return, 0, j-1) || substr(l_return, j+4, length(l_return) - j - 3);
374 end if;
375
376 return l_return;
377
378 end GET_AW_TINY_NAME;
379
380 -------------------------------------------------------------------------------
381 -- INITIALIZE
382 --
383 -- Initializes the AW session by attaching code, annotation and shared AW for
384 -- the Business Area specified, and setting context and session-wide
385 -- parameters
386 --
387 -- No commit is done by this procedure
388 --
389 -- IN: p_business_area_id NUMBER - The Business Area ID to work under
390 -------------------------------------------------------------------------------
391 PROCEDURE INITIALIZE(p_api_version IN NUMBER,
392 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
393 p_validation_level IN NUMBER
394 := FND_API.G_VALID_LEVEL_FULL,
395 x_return_status OUT NOCOPY varchar2,
396 x_msg_count OUT NOCOPY number,
397 x_msg_data OUT NOCOPY varchar2,
398 p_business_area_id IN NUMBER,
399 p_shadow_id IN NUMBER,
400 p_shared_rw IN VARCHAR2 := FND_API.G_FALSE,
401 p_annot_rw IN VARCHAR2 := FND_API.G_FALSE,
402 p_detach_all IN VARCHAR2 := FND_API.G_TRUE)
403 is
404 l_api_name CONSTANT VARCHAR2(20) := G_PKG_NAME||'.initialize';
408 l_schema VARCHAR2(10);
405 l_api_version CONSTANT NUMBER := 1.0;
406 l_code_AW VARCHAR2(30);
407 l_annot_AW VARCHAR2(30);
409 l_trace VARCHAR2(200);
410 l_comm VARCHAR2(200);
411 l_user_id NUMBER;
412 begin
413 -- Standard call to check for call compatibility.
414 IF NOT FND_API.Compatible_API_Call( l_api_version,
415 p_api_version, l_api_name, G_PKG_NAME)
416 THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END IF;
419 -- Initialize message list if p_init_msg_list is set to TRUE.
420 IF FND_API.to_Boolean(p_init_msg_list) THEN
421 FND_MSG_PUB.initialize;
422 END IF;
423
424 -- Initialize API return status to success
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 ZPB_LOG.WRITE (l_api_name||'.begin',
428 'Initializing Bus Area '||p_business_area_id);
429
430 if p_detach_all = FND_API.G_TRUE then
431 DETACH_ALL;
432 end if;
433
434 if (FND_GLOBAL.USER_ID = -1) then
435 l_user_id := p_shadow_id;
436 else
437 l_user_id := FND_GLOBAL.USER_ID;
438 end if;
439
440 ZPB_SECURITY_CONTEXT.INITCONTEXT(to_char(l_user_id),
441 to_char(p_shadow_id),
442 to_char(FND_GLOBAL.RESP_ID),
443 to_char(FND_GLOBAL.SESSION_ID),
444 p_business_area_id);
445
446 l_schema := GET_SCHEMA||'.';
447 l_code_AW := l_schema||GET_CODE_AW(p_shadow_id);
448 l_annot_AW := l_schema||GET_ANNOTATION_AW;
449
450 EXECUTE('badline=yes');
451 EXECUTE('recursive=yes');
452 EXECUTE('oknullstatus=yes');
453 EXECUTE('set naskip2 yes');
454 EXECUTE('set dividebyzero yes');
455
456 EXECUTE('aw attach '||l_code_AW||' ro last');
457
458 l_trace := FND_PROFILE.VALUE_SPECIFIC('ZPB_SQL_TRACE', l_user_id);
459 if (l_trace is not null and instr(l_trace, 'AWLOG:') > 0) then
460 l_comm := substr(l_trace, instr(l_trace, 'AWLOG:')+6);
461 ZPB_AW.EXECUTE ('CM.LOGFILE = '''||l_comm||'''');
462 end if;
463
464 if (p_annot_rw = FND_API.G_TRUE) then
465 EXECUTE('aw attach '||l_annot_AW||' rw');
466 else
467 EXECUTE('aw attach '||l_annot_AW||' ro');
468 end if;
469 if (p_shared_rw = FND_API.G_TRUE) then
470 EXECUTE('aw attach '||l_schema||GET_SHARED_AW||' rw');
471 EXECUTE('aw aliaslist '||l_schema||GET_SHARED_AW||' alias shared');
472 EXECUTE('aw aliaslist '||l_schema||GET_SHARED_AW||' alias s');
473 EXECUTE('aw aliaslist '||l_schema||GET_SHARED_AW||' alias aggaw');
474 if (zpb_aw.interpbool('show exists(''LANG'')')) then
475 EXECUTE('lmt LANG to '''||FND_GLOBAL.CURRENT_LANGUAGE||'''');
476 end if;
477 else
478 EXECUTE('call pa.attach.shared('''||FND_GLOBAL.USER_ID||''' no)');
479 end if;
480
481 end INITIALIZE;
482
483 -------------------------------------------------------------------------------
484 -- INITIALIZE_FOR_AC
485 --
486 -- Initializes the AW session by attaching code, annotation and shared AW for
487 -- the business process specified, and setting context and session-wide
488 -- parameters
489 --
490 -- No commit is done by this procedure
491 --
492 -- IN: p_analysis_cycle_id NUMBER - The Analysis Cycle to initialize against
493 -------------------------------------------------------------------------------
494 PROCEDURE INITIALIZE_FOR_AC(p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
496 p_validation_level IN NUMBER
497 := FND_API.G_VALID_LEVEL_FULL,
498 x_return_status OUT NOCOPY varchar2,
499 x_msg_count OUT NOCOPY number,
500 x_msg_data OUT NOCOPY varchar2,
501 p_analysis_cycle_id IN NUMBER,
502 p_shared_rw IN VARCHAR2:=FND_API.G_FALSE,
503 p_annot_rw IN VARCHAR2:=FND_API.G_FALSE)
504 is
505 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
506 begin
507
508 -- support both analysis cycle ids and current instance ids
509 begin
510 select BUSINESS_AREA_ID
511 into l_business_area_id
512 from ZPB_ANALYSIS_CYCLES
513 where ANALYSIS_CYCLE_ID = p_analysis_cycle_id;
514 exception
515 when no_data_found then
516 -- most likely there will be more than row for the same current instance id
517 select max(BUSINESS_AREA_ID)
518 into l_business_area_id
519 from ZPB_ANALYSIS_CYCLES
520 where CURRENT_INSTANCE_ID = p_analysis_cycle_id;
521 end;
522
523 INITIALIZE (p_api_version => p_api_version,
524 p_init_msg_list => p_init_msg_list,
525 p_validation_level => p_validation_level,
526 x_return_status => x_return_status,
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data,
532 end INITIALIZE_FOR_AC;
529 p_business_area_id => l_business_area_id,
530 p_shared_rw => p_shared_rw,
531 p_annot_rw => p_annot_rw);
533
534 -------------------------------------------------------------------------------
535 -- INITIALIZE_USER
536 --
537 -- Initializes the AW session by attaching the personal AW for
538 -- the user specified. Will initialize the shared AW's
539 --
540 -- No commit is done by this procedure
541 --
542 -- IN: p_business_area_id NUMBER - The Business Area ID to work under
543 -------------------------------------------------------------------------------
544 PROCEDURE INITIALIZE_USER(p_api_version IN NUMBER,
545 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
546 p_validation_level IN NUMBER
547 := FND_API.G_VALID_LEVEL_FULL,
548 x_return_status OUT NOCOPY varchar2,
549 x_msg_count OUT NOCOPY number,
550 x_msg_data OUT NOCOPY varchar2,
551 p_user IN FND_USER.USER_ID%type,
552 p_business_area_id IN NUMBER,
553 p_attach_readwrite IN VARCHAR2,
554 p_sync_shared IN VARCHAR2,
555 p_detach_all IN VARCHAR2 :=FND_API.G_FALSE)
556 is
557 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME||'.initialize_user';
558 l_api_version CONSTANT NUMBER := 1.0;
559 l_code_AW VARCHAR2(30);
560 l_annot_AW VARCHAR2(30);
561 l_pers_AW VARCHAR2(30);
562 l_schema VARCHAR2(10);
563 l_onattach BOOLEAN;
564 begin
565 -- Standard call to check for call compatibility.
566 IF NOT FND_API.Compatible_API_Call( l_api_version,
567 p_api_version, l_api_name, G_PKG_NAME)
568 THEN
569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570 END IF;
571 -- Initialize message list if p_init_msg_list is set to TRUE.
572 IF FND_API.to_Boolean(p_init_msg_list) THEN
573 FND_MSG_PUB.initialize;
574 END IF;
575
576 -- Initialize API return status to success
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578
579 ZPB_LOG.WRITE (l_api_name||'.begin', 'Initializing User '||p_user);
580
581 if (p_business_area_id is null) then
582 null;
583 end if;
584
585 if p_detach_all = FND_API.G_TRUE then
586 DETACH_ALL;
587 end if;
588
589 l_schema := GET_SCHEMA||'.';
590 l_code_AW := l_schema||GET_CODE_AW(p_user);
591
592 EXECUTE('badline=yes');
593 EXECUTE('recursive=yes');
594 EXECUTE('oknullstatus=yes');
595 EXECUTE('set naskip2 yes');
596 EXECUTE('set dividebyzero yes');
597
598 EXECUTE('aw attach '||l_code_AW||' ro last');
599
600 l_onattach := false;
601
602 if not ((sys_context('ZPB_CONTEXT', 'business_area_id') is null or
603 sys_context('ZPB_CONTEXT', 'business_area_id') <> p_business_area_id or
604 not INTERPBOOL('shw aw(attached ''SHARED'')'))) then
605
606 ZPB_SECURITY_CONTEXT.INITCONTEXT(sys_context('ZPB_CONTEXT','user_id'),
607 p_user,
608 sys_context('ZPB_CONTEXT','resp_id'),
609 sys_context('ZPB_CONTEXT','session_id'),
610 sys_context('ZPB_CONTEXT',
611 'business_area_id'));
612 EXECUTE ('DM.PRS.DATAAW = NA');
613 if (INTERPBOOL('shw aw(attached ''PERSONAL'')')) then
614 select PERSONAL_AW
615 into l_pers_AW
616 from ZPB_USERS
617 where BUSINESS_AREA_ID =
618 sys_context('ZPB_CONTEXT', 'business_area_id')
619 and USER_ID = p_user;
620 if (not INTERPBOOL('shw aw(attached '''||l_pers_aw||''')') or
621 INTERPBOOL('shw aw(name ''PERSONAL'') ne aw(name '''||
622 l_pers_aw||''')')) then
623 EXECUTE ('aw detach ''PERSONAL''');
624 l_onattach := true;
625 end if;
626 end if;
627 end if;
628
629 if (p_attach_readwrite = FND_API.G_TRUE) then
630 EXECUTE('call PA.ATTACH.PERSONAL('''||p_user||''' ''rw'')');
631 else
632 EXECUTE('call PA.ATTACH.PERSONAL('''||p_user||''' ''ro'')');
633 end if;
634
635 if (l_onattach) then
636 --
637 -- ONATTACH needs to be called in the case where the user's personal is
638 -- already attached for whatever reason: the DM.PRS structures will
639 -- point to the wrong AW in this
640 --
641 EXECUTE('call PERSONAL!ONATTACH');
642 end if;
643
644 if (sys_context('ZPB_CONTEXT', 'business_area_id') is null or
645 sys_context('ZPB_CONTEXT', 'business_area_id') <> p_business_area_id or
646 not INTERPBOOL('shw aw(attached ''SHARED'')')) then
647 INITIALIZE(1.0,
648 p_init_msg_list,
649 p_validation_level,
650 x_return_status,
651 x_msg_count,
655 FND_API.G_FALSE,
652 x_msg_data,
653 p_business_area_id,
654 p_user,
656 FND_API.G_FALSE,
657 FND_API.G_FALSE);
658 end if;
659
660 if (p_sync_shared = FND_API.G_TRUE) then
661 EXECUTE('call PA.ATTACH.SHARED('''||p_user||''' yes)');
662 end if;
663
664 end INITIALIZE_USER;
665
666 -------------------------------------------------------------------------------
667 -- clean_workspace
668 --
669 -- Procedure detaches the code and shared AWs and resets the ZPB context.
670 -- Designed to be called by backend programs that initiate an
671 -- OLAP workspace with full data access.
672 --
673 -- No commit is done by this procedure
674 --
675 -------------------------------------------------------------------------------
676 PROCEDURE clean_workspace ( p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2,
678 p_validation_level IN NUMBER,
679 x_return_status OUT NOCOPY varchar2,
680 x_msg_count OUT NOCOPY number,
681 x_msg_data OUT NOCOPY varchar2)
682 IS
683
684 l_api_name CONSTANT VARCHAR2(15) := 'clean_workspace';
685 l_api_version CONSTANT NUMBER := 1.0;
686 BEGIN
687 -- Standard Start of API savepoint
688 SAVEPOINT zpb_aw_clean_workspace;
689 -- Standard call to check for call compatibility.
690 IF NOT FND_API.Compatible_API_Call( l_api_version,
691 p_api_version,
692 l_api_name,
693 G_PKG_NAME)
694 THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697 -- Initialize message list if p_init_msg_list is set to TRUE.
698 IF FND_API.to_Boolean(p_init_msg_list) THEN
699 FND_MSG_PUB.initialize;
700 END IF;
701 -- Initialize API return status to success
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME||'.'||l_api_name||'.begin',
705 'Begin OLAP workspace cleaning');
706
707 --
708 -- Detach all AW's that are attached, except for EXPRESS
709 --
710 DETACH_ALL;
711
712 ZPB_SECURITY_CONTEXT.INITCONTEXT(null, null, null, null, null);
713
714 DBMS_AW.SHUTDOWN;
715
716 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME||'.'||l_api_name||'.end',
717 'OLAP workspace has been cleaned');
718
719 -- Standard call to get message count and if count is 1, get message info.
720 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data );
721
722 EXCEPTION
723 WHEN FND_API.G_EXC_ERROR THEN
724 ROLLBACK TO zpb_aw_clean_workspace;
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 FND_MSG_PUB.Count_And_Get(
727 p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
731 ROLLBACK TO zpb_aw_clean_workspace;
732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
733 FND_MSG_PUB.Count_And_Get(
734 p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737 WHEN OTHERS THEN
738 ROLLBACK TO zpb_aw_clean_workspace;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
741 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name );
742 END IF;
743 FND_MSG_PUB.Count_And_Get(
744 p_count => x_msg_count,
745 p_data => x_msg_data
746 );
747
748 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME, l_api_name);
749 END clean_workspace;
750
751 end ZPB_AW;
[Home] [Help]