[Home] [Help]
PACKAGE BODY: APPS.ZPB_PERSONAL_AW
Source
1 package body ZPB_PERSONAL_AW as
2 /* $Header: zpbpersonalaw.plb 120.29 2007/12/04 15:40:39 mbhat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_PERSONAL_AW';
5 G_LOCK_OUT CONSTANT Number := 2;
6 g_olapSchema varchar2(3) := zpb_aw.get_schema;
7
8 g_annotAW varchar2(30);
9 g_codeAW varchar2(30);
10 g_sharedAW varchar2(30);
11 g_personalAW varchar2(30);
12 g_attach_mode varchar2(2);
13
14 -------------------------------------------------------------------------------
15 -- GET_AWS
16 -------------------------------------------------------------------------------
17 procedure GET_AWS (p_user in varchar2)
18 is
19 begin
20
21 g_annotAW := ZPB_AW.GET_ANNOTATION_AW;
22 g_codeAW := ZPB_AW.GET_CODE_AW (p_user);
23 g_sharedAW := ZPB_AW.GET_SHARED_AW;
24 g_personalAW := ZPB_AW.GET_PERSONAL_AW (p_user);
25 end GET_AWS;
26
27 -------------------------------------------------------------------------------
28 -- ANNOTATIONS_CREATE - Driver program to create the user's personal
29 -- annotation from the shared AW
30 --
31 -- IN: p_user - The user ID
32 -------------------------------------------------------------------------------
33 procedure ANNOTATION_CREATE(p_user in varchar2)
34 is
35 l_global_ecm zpb_ecm.global_ecm;
36 l_annot_ecm zpb_ecm.annot_ecm;
37 l_annEntry varchar2(16);
38 l_annCells varchar2(16);
39 l_annLookup varchar2(16);
40 l_dims varchar2(500);
41 l_persAw varchar2(60);
42 begin
43
44 zpb_log.write('zpb_personal_aw.annotation_create.begin',
45 'Creating personal metadata for '||p_user);
46
47 l_persAw := g_olapSchema||'.'||g_personalAW;
48 zpb_aw.execute ('call pa.attach.shared ('''||p_user||''')');
49 l_global_ecm := zpb_ecm.get_global_ecm(g_sharedAW);
50
51 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_annotAW||' ro');
52
53 l_annot_ecm := zpb_ecm.get_annotation_ecm(g_sharedAW);
54 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_sharedAW);
55 zpb_aw.execute('lmt '||l_global_ecm.AnnEntryDim||' remove all');
56
57 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_annotAW||' ro');
58 zpb_aw.execute ('lmt name to obj(property ''PERSONALOBJ'') eq yes');
59 import (p_user, g_annotAW, 'N');
60
61 --
62 -- Define the formulas. Must be redefined instead of imported:
63 --
64 zpb_aw.execute ('call an.form.pers.crt('''||l_persAw||''' '''||
65 g_olapSchema||'.'||g_sharedAW||''' '''||
66 g_olapSchema||'.'||g_annotAW||''')');
67
68 zpb_log.write('zpb_personal_aw.annotation_create.end',
69 'End annotation creation');
70 end ANNOTATION_CREATE;
71
72 -------------------------------------------------------------------------------
73 -- AW_CREATE - Driver program to create the user's personal AW from the shared
74 -- AW
75 --
76 -- IN: p_user - The user ID
77 -------------------------------------------------------------------------------
78 procedure AW_CREATE(p_user in varchar2,
79 p_business_area_id in number)
80 is
81 l_ignore boolean;
82 l_vIgnore varchar2(1) := 'S';
83 l_aw varchar2(30);
84 l_ecm zpb_ecm.global_ecm;
85 l_valid boolean := false;
86 l_exists boolean := true;
87 l_retStat varchar2(1);
88 l_msgCnt number;
89 l_msgData varchar2(2000);
90 l_resp varchar2(30);
91
92 -- Added for Bug: 5842827
93 aw_already_exists_exception EXCEPTION;
94
95 begin
96 select count(*)
97 into l_msgCnt
98 from ZPB_ACCOUNT_STATES
99 where USER_ID = p_user
100 and BUSINESS_AREA_ID = p_business_area_id
101 and ACCOUNT_STATUS = 0;
102
103 if (l_msgCnt > 0) then
104 l_valid := true;
105 select count(*)
106 into l_msgCnt
107 from ZPB_USERS
108 where USER_ID = p_user
109 and BUSINESS_AREA_ID = p_business_area_id;
110
111 if (l_msgCnt = 0) then
112 insert into ZPB_USERS
113 (BUSINESS_AREA_ID,
114 USER_ID,
115 LAST_BUSAREA_LOGIN,
116 SHADOW_ID,
117 PERSONAL_AW,
118 CREATION_DATE,
119 CREATED_BY,
120 LAST_UPDATE_DATE,
121 LAST_UPDATED_BY)
122 values
123 (p_business_area_id,
124 p_user,
125 'N',
126 p_user,
127 'ZPB'||p_user||'A'||p_business_area_id,
128 sysdate,
129 FND_GLOBAL.USER_ID,
130 sysdate,
131 FND_GLOBAL.USER_ID);
132 end if;
133 end if;
134
135 if (l_valid) then
136 if (sys_context('ZPB_CONTEXT', 'business_area_id') is null) then
137 select to_char(RESPONSIBILITY_ID)
138 into l_resp
139 from FND_RESPONSIBILITY
140 where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
141
142 FND_GLOBAL.APPS_INITIALIZE (p_user, l_resp, 210);
143
144 zpb_aw.initialize (p_api_version => 1.0,
145 x_return_status => l_retStat,
146 x_msg_count => l_msgCnt,
147 x_msg_data => l_msgData,
148 p_business_area_id => p_business_area_id,
149 p_shadow_id => p_user,
150 p_shared_rw => FND_API.G_FALSE);
151 end if;
152
153 get_aws(p_user);
154
155 begin
156 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_personalAW||' ro');
157
158 -- Added for Bug: 5842827
159 l_exists := true;
160 exception
161 when others then
162 l_exists := false;
163 end;
164 end if;
165
166 -- Added for Bug: 5842827
167 IF l_exists = TRUE THEN
168 RAISE aw_already_exists_exception;
169 END IF;
170
171 if (l_valid and l_exists = false) then
172 g_attach_mode := 'rw';
173
174 -- Metadata for SHARED_VIEWS is shared between all users and thus
175 -- does not to be manipulated on a per user basis
176
177 zpb_aw.execute ('aw create '||g_olapSchema||'.'||g_personalAW);
178 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
179
180 -- Set up the personal DM objects
181 zpb_aw.execute('DM.PRS.DATAAW='''||g_olapSchema||'.'||g_personalAW||'''');
182 zpb_aw.execute('DM.PRS.ANNOTAW='''||g_olapSchema||'.'||g_personalAW||'''');
183 zpb_aw.execute('DM.PRS.ECMLOCATOR = '''||g_olapSchema||'.'||g_personalAW||
184 '!ECMLOCATOR''');
185 zpb_aw.execute ('DM.PRS.DIMDIM = '''||g_olapSchema||'.'||g_personalAW||
186 '!DMENTRY''');
187 zpb_aw.execute ('DM.PRS.MEASDIM = '''||g_olapSchema||'.'||g_personalAW||
188 '!MEASURE''');
189
190 l_ecm := zpb_ecm.get_global_ecm (g_sharedAW);
191 l_aw := g_olapSchema||'.'||g_sharedAW;
192
193 METADATA_CREATE (p_user);
194
195 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
196 ANNOTATION_CREATE (p_user);
197
198 -- Set up the Perosnal DM objects
199 l_ecm := zpb_ecm.get_global_ecm (g_personalAW);
200 l_aw := g_olapSchema||'.'||g_personalAW;
201
202 --
203 -- Convert SHARED formulas to point to Personal Aw
204 --
205 zpb_aw.execute ('call PA.CONVERT.FORMULAS('''||g_olapSchema||'.'||
206 g_personalAW||''' '''||g_olapSchema||'.'||
207 g_sharedAW||''')');
208
209 --
210 -- Generate personal AW structures, and update the metadata catalogs
211 --
212 zpb_aw.execute ('call PA.META.CREATE('''||g_olapSchema||'.'||
213 g_personalAW||''')');
214
215 zpb_aw.execute ('call pa.attach.shared('''||p_user||''' false)');
216
217 --dbms_output.put_line ('done meta create: '||to_char(sysdate, 'HH:MI:SS'));
218 VIEWS_UPDATE (g_personalAW, p_user);
219
220 --dbms_output.put_line ('done views create: '||to_char(sysdate, 'HH:MI:SS'))
221
222 l_ignore := MEASURES_SHARED_UPDATE (p_user, l_vIgnore);
223 --dbms_output.put_line ('done meas create: '||to_char(sysdate, 'HH:MI:SS'));
224 zpb_aw.execute ('upd '||g_olapSchema||'.'||g_personalAW);
225 commit;
226
227 -- Metadata for SHARED_VIEWS is shared between all users and thus does not to be
228 -- manipulated on a per user basis
229 -- ZPB_METADATA_MAP_PKG.BUILD(g_personalAW, g_sharedAW, 'PERSONAL', 'N');
230
231 --dbms_output.put_line ('done map create: '||to_char(sysdate,
232 --'HH:MI:SS'));
233
234 -- set metadata refresh state table to force SECURITY_UPDATE
235 update ZPB_ACCOUNT_STATES
236 set READ_SCOPE = 1, WRITE_SCOPE = 1, OWNERSHIP = 1,
237 metadata_scope = 1,
238 LAST_UPDATE_DATE = sysdate,
239 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
240 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
241 LAST_LOGIN_DATE = null
242 where USER_ID = p_user
243 and BUSINESS_AREA_ID = p_business_area_id;
244
245 -- Added for Bug: 5842827
246 UPDATE zpb_dc_objects
247 SET copy_source_type_flag = 'Y',
248 create_solve_program_flag = 'Y',
249 create_instance_measures_flag = 'Y',
250 status = 'DISTRIBUTION_PENDING'
251 WHERE object_type IN ('C', 'W')
252 AND object_user_id = p_user
253 AND business_area_id = p_business_area_id;
254
255 UPDATE zpb_dc_objects
256 SET copy_source_type_flag = 'Y',
257 create_solve_program_flag = 'Y',
258 create_instance_measures_flag = 'Y'
259 WHERE object_type IN ('E')
260 AND object_user_id = p_user
261 AND business_area_id = p_business_area_id;
262
263 commit;
264 end if;
265
266 begin
267 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
268 exception
269 when others then
270 null;
271 end;
272
273 -- Added exception block for Bug: 5842827
274 EXCEPTION
275 WHEN aw_already_exists_exception THEN
276 fnd_message.set_name('ZPB', 'ZPB_APPMGR_EXISTS_ERROR');
277 fnd_message.set_token('OBJNAME', 'Personal AW (' ||g_personalAW||')');
278
279 RAISE;
280 end AW_CREATE;
281
282 -------------------------------------------------------------------------------
283 -- AW_DELETE - Driver program to completely and irreversibly delete
284 -- the user's personal AW. Will delete the AW and any
285 -- SQL Views defined for that AW.
286 --
287 -- IN: p_user - The user ID
288 -------------------------------------------------------------------------------
289 procedure AW_DELETE(p_user in varchar2,
290 p_business_area_id in number)
291 is
292 l_count number;
293 l_retStat varchar2(1);
294 l_msgCnt number;
295 l_msgData varchar2(2000);
296
297 -- Added for Bug: 5842827
298 l_sid NUMBER;
299 l_serial_no NUMBER;
300 l_sess_user VARCHAR2(30);
301 l_os_user VARCHAR2(30);
302 l_status VARCHAR2(8);
303 l_schema_name VARCHAR2(30);
304 l_machine VARCHAR2(64);
305
306 aw_attached_rw_exception EXCEPTION;
307
308 begin
309 if (sys_context('ZPB_CONTEXT', 'business_area_id') is null) then
310
311 /* Commented out for Bug: 5007146
312 select to_char(RESPONSIBILITY_ID)
313 into l_resp
314 from FND_RESPONSIBILITY
315 where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
316
317 FND_GLOBAL.APPS_INITIALIZE (p_user, l_resp, 210);
318 */
319
320 zpb_aw.initialize (p_api_version => 1.0,
321 x_return_status => l_retStat,
322 x_msg_count => l_msgCnt,
323 x_msg_data => l_msgData,
324 p_business_area_id => p_business_area_id,
325 p_shadow_id => p_user,
326 p_shared_rw => FND_API.G_FALSE);
327 end if;
328 get_aws(p_user);
329
330 -- Fix for Bug: 5842827 - start ...
331 personal_aw_rw_scan(p_user => p_user,
332 p_business_area => p_business_area_id,
333 p_sid => l_sid,
334 p_serial_no => l_serial_no,
335 p_sess_user => l_sess_user,
336 p_os_user => l_os_user,
337 p_status => l_status,
338 p_schema_name => l_schema_name,
339 p_machine => l_machine);
340
341 IF l_sid <> 0 THEN
342 RAISE aw_attached_rw_exception;
343 END IF;
344 -- Fix for Bug: 5842827 - ... End
345
346 -- delete from zpb_metadata_map where aw_name = g_personalAW;
347 zpb_metadata_pkg.delete_user(g_personalAW);
348
349 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_codeAW||' ro');
350
351 begin
352 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_personalAW||' ro');
353 exception
354 when others then
355 --
356 -- AW doesnt exist, so just return
357 --
358 return;
359 end;
360
361 zpb_olap_views_pkg.remove_user_views (p_user, p_business_area_id);
362
363 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
364 zpb_aw.execute ('aw delete '||g_olapSchema||'.'||g_personalAW);
365
366 EXCEPTION
367 WHEN aw_attached_rw_exception THEN
368
369 fnd_message.set_name('ZPB','ZPB_PERSONAL_AW_SESSION_MSG');
370
371 fnd_message.set_token('SESSION_USER', l_sess_user);
372 fnd_message.set_token('OS_USER', l_os_user);
373 fnd_message.set_token('STATUS', l_status);
374 fnd_message.set_token('SCHEMA', l_schema_name);
375 fnd_message.set_token('MACHINE', l_machine);
376
377 RAISE;
378
379 end AW_DELETE;
380
381 -------------------------------------------------------------------------------
382 -- AW_UPDATE - Driver program to update the user's personal AW from the shared
383 -- AW
384 --
385 -- IN: p_user - The user ID
386 -- x_return_status - The return status
387 --
388 -- OUT: whether the structures have changed to require a new Metadata Map
389 -------------------------------------------------------------------------------
390 function AW_UPDATE(p_user IN VARCHAR2,
391 x_return_status IN OUT NOCOPY VARCHAR2,
392 p_read_only IN VARCHAR2)
393 return BOOLEAN
394 is
395 cursor state_cur is
396 select nvl (READ_SCOPE, 0) +
397 nvl(WRITE_SCOPE, 0) +
398 nvl(OWNERSHIP, 0) +
399 nvl(METADATA_SCOPE, 0) needs_update
400 from ZPB_ACCOUNT_STATES
401 where USER_ID = p_user
402 and RESP_ID = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID)
403 and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
404
405 l_rdAcc NUMBER;
406 l_wrtAcc NUMBER;
407 l_mdAcc NUMBER;
408 l_own NUMBER;
409 l_dims varchar2(500);
410 l_ret boolean;
411 l_ret2 boolean;
412 l_upd boolean := false;
413 l_proc varchar2(9) := 'aw_update';
414
415 begin
416 ZPB_LOG.WRITE ('zpb_metadata_map.aw_update.begin', 'Begin Metadata Update');
417
418 ZPB_AW.EXECUTE ('PA.VIEW.DELETED = no');
419 --
420 -- For security, check to see if security needs updating:
421 --
422 for each in state_cur loop
423 if (each.needs_update > 0) then
424 l_upd := true;
425 exit;
426 end if;
427 end loop;
428
429 l_dims := METADATA_UPDATE(p_user, x_return_status);
430
431 if (l_upd = true) then
432 ZPB_AW.EXECUTE ('PA.VIEW.DELETED = yes');
433 SECURITY_UPDATE(p_user, x_return_status);
434 --
435 -- Force refresh on all dim's views. If we need a metadata update
436 -- as well, then force a full refresh for logic's simplicity
437 --
438 if (l_dims is null) then
439 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
440 VIEWS_UPDATE(g_personalAW, p_user, l_dims, 'Y');
441
442 ZPB_METADATA_PKG.BUILD_PERSONAL_DIMS (g_personalAW, g_sharedAW,
443 'PERSONAL', l_dims);
444 l_dims := null;
445 else
446 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
447 end if;
448
449 --
450 -- Update DC objects to regenerate the solve program (5093114)
451 --
452 update ZPB_DC_OBJECTS
453 set CREATE_SOLVE_PROGRAM_FLAG = 'Y',
454 LAST_UPDATE_DATE = sysdate,
455 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
456 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
457 where BUSINESS_AREA_ID = sys_context('ZPB_CONTEXT', 'business_area_id')
458 and OBJECT_USER_ID = p_user
459 and STATUS <> 'SUBMITTED_TO_SHARED';
460 end if;
461
462 if x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR and p_read_only = FND_API.G_FALSE then
463 -- reset metadata refresh state table
464 update ZPB_ACCOUNT_STATES
465 set METADATA_SCOPE = 0, READ_SCOPE = 0, WRITE_SCOPE = 0, OWNERSHIP = 0,
466 LAST_UPDATE_DATE = sysdate,
467 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
468 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
469 where USER_ID = p_user
470 and BUSINESS_AREA_ID = sys_context('ZPB_CONTEXT', 'business_area_id')
471 and nvl(READ_SCOPE, -1) <> G_LOCK_OUT
472 and nvl(WRITE_SCOPE, -1) <> G_LOCK_OUT
473 and nvl(OWNERSHIP, -1) <> G_LOCK_OUT
474 and nvl(METADATA_SCOPE, -1) <> G_LOCK_OUT;
475 end if;
476
477 if (l_dims is not null) then
478 begin
479 ZPB_LOG.WRITE_EVENT (G_PKG_NAME||'.'||l_proc,
480 'Updating dimensions: '||l_dims);
481
482 ZPB_METADATA_PKG.BUILD_PERSONAL_DIMS (g_personalAW, g_sharedAW,
483 'PERSONAL', l_dims);
484 VIEWS_UPDATE(g_personalAW, p_user, l_dims, 'Y');
485 l_ret := true;
486 exception
487 when others then
488 ZPB_LOG.LOG_PLSQL_EXCEPTION (G_PKG_NAME, l_proc);
489 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
490 l_proc,
491 'ZPB_STARTUP_DIM_MAP_ERR_MSG');
492 --
493 -- Reattach personal to remove changes to dimensions:
494 --
495 zpb_aw.execute('aw detach '||g_olapSchema||'.'||g_personalAW);
496 zpb_aw.execute('call PA.ATTACH.PERSONAL('''||p_user||''' '''||
497 g_attach_mode||''')');
498 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
499 FND_API.G_RET_STS_UNEXP_ERROR);
500
501 end;
502 end if;
503
504 l_ret2 := MEASURES_SHARED_UPDATE(p_user, x_return_status);
505
506 return (l_ret or l_ret2);
507 end AW_UPDATE;
508
509 -------------------------------------------------------------------------------
510 -- DATA_VIEWS_CREATE - Creates the views associated with the measures of the
511 -- instance.
512 --
513 -- IN: p_user - User ID
514 -- p_instance - The instance ID
515 -------------------------------------------------------------------------------
516 procedure DATA_VIEWS_CREATE(p_user in varchar2,
517 p_instance in varchar2,
518 p_type in varchar2,
519 p_template in varchar2,
520 p_approver in varchar2)
521 is
522 l_aw varchar2(32);
523 begin
524 zpb_log.write('zpb_personal_aw.data_views_create.begin',
525 'Creating data views for '||p_user||
526 ', instance: '||p_instance);
527
528 get_aws(p_user);
529 l_aw := zpb_aw.get_schema||'.'||g_personalAw;
530
531 ZPB_AW.EXECUTE ('CALL CM.BUILD.INSTVIEW ('''||l_aw||''' '''||
532 p_instance||''' '''||p_type||''' '''||p_template||''' '''||
533 p_approver||''')');
534
535 zpb_log.write('zpb_personal_aw.data_views_create.end',
536 'Created data views for '||p_user||
537 ', instance: '||p_instance);
538 end DATA_VIEWS_CREATE;
539
540 -------------------------------------------------------------------------------
541 -- IMPORT - Imports objects from one AW to personal AW
542 --
543 -- IN: p_user - The user id.
544 -- p_fromAw - The AW to import from. Defaults to the shared AW
545 -- p_noScope - 'Y' if readscoping should be removed for the import
546 -------------------------------------------------------------------------------
547 procedure IMPORT (p_user in varchar2,
548 p_fromAw in varchar2,
549 p_noScope in varchar2)
550 is
551 l_LOB BLOB;
552 l_aw varchar2(30);
553 l_dims varchar2(500);
554 l_dim varchar2(30);
555 l_obj varchar2(60);
556 l_val varchar2(4000);
557 i number;
558 j number;
559 l_ecm zpb_ecm.global_ecm;
560 begin
561 get_aws(p_user);
562
563 if (zpb_aw.interpbool('shw aw(attached '''||g_olapSchema||'.'||
564 g_personalAW||''')')) then
565 if (lower(g_attach_mode) = 'rw') then
566 zpb_aw.execute ('upd '||g_olapSchema||'.'||g_personalAW);
567 end if;
568
569 commit;
570 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
571 end if;
572
573 l_aw := nvl(p_fromAW, g_sharedAW);
574 l_LOB := dbms_aw.eif_blob_out(g_olapSchema, l_aw);
575
576 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||l_aw);
577 zpb_aw.execute ('call pa.attach.personal('''||p_user||''' '''||
578 g_attach_mode||''')');
579
580 if (p_noScope = 'Y') then
581 l_ecm := zpb_ecm.get_global_ecm(g_personalAW);
582 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
583 i := 1;
584 loop
585 j := instr (l_dims, ' ', i);
586 if (j = 0) then
587 l_dim := substr (l_dims, i);
588 else
589 l_dim := substr (l_dims, i, j-i);
590 i := j+1;
591 end if;
592
593 l_obj := zpb_aw.interp('shw PERSONAL!'||l_ecm.ExpObjVar||' (PERSONAL!'
594 ||l_ecm.DimDim||' '''||l_dim||''')');
595 l_val := ZPB_AW.EVAL_TEXT('obj(pmtread ''PERSONAL!'||
596 l_obj||''')');
597
598 if (length(l_val) > 1) then
599 zpb_aw.execute ('cns PERSONAL!'||l_obj);
600 zpb_aw.execute ('prp ''__READSCOPE'' obj(pmtread ''PERSONAL!'||
601 l_obj||''')');
602 zpb_aw.execute ('permit read when true');
603 end if;
604
605 exit when j=0;
606 end loop;
607 end if;
608
609 dbms_aw.eif_blob_in(g_olapSchema,
610 g_personalAW,
611 l_LOB,
612 DBMS_AW.EIFIMP_DATA);
613
614 if (p_noScope = 'Y') then
615 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
616 i := 1;
617 loop
618 j := instr (l_dims, ' ', i);
619 if (j = 0) then
620 l_dim := substr (l_dims, i);
621 else
622 l_dim := substr (l_dims, i, j-i);
623 i := j+1;
624 end if;
625
626 l_obj := zpb_aw.interp('shw PERSONAL!'||l_ecm.ExpObjVar||' (PERSONAL!'
627 ||l_ecm.DimDim||' '''||l_dim||''')');
628 if (zpb_aw.interpbool('shw obj(hasproperty ''__READSCOPE'' '||
629 '''PERSONAL!'||l_obj||''')')) then
630 l_val := zpb_aw.interp ('shw obj(property ''__READSCOPE'' '||
631 '''PERSONAL!'||l_obj||''')');
632 if (length(l_val) > 1) then
633 zpb_aw.execute ('cns PERSONAL!'||l_obj);
634 zpb_aw.execute ('permit read when '||l_val);
635 zpb_aw.execute ('prp delete ''__READSCOPE''');
636 end if;
637 end if;
638 exit when j=0;
639 end loop;
640 end if;
641 if (lower(g_attach_mode) = 'rw') then
642 zpb_aw.execute ('upd '||g_olapSchema||'.'||g_personalAW);
643 end if;
644 commit;
645
646 end IMPORT;
647
648 -------------------------------------------------------------------------------
649 -- MEASURES_DELETE - Deletes measures defined in the personal
650 --
651 -- IN: p_user - The User ID
652 -- p_instance - The instance ID
653 -- p_type - SHARED_VIEW, PERSONAL, APPROVER. Def SHARED_VIEW
654 -- p_template - The template ID. Null if N/A (default)
655 -- p_approvee - The approvee ID. Null if N/A (default)
656 --
657 -------------------------------------------------------------------------------
658 procedure MEASURES_DELETE(p_user in varchar2,
659 p_instance in varchar2,
660 p_type in varchar2,
661 p_template in varchar2,
662 p_approvee in varchar2)
663 is
664 l_mode varchar2(30);
665 l_template varchar2(30);
666 l_user varchar2(30);
667 begin
668 get_aws (p_user);
669
670 --
671 -- Delete from the metadata map:
672 --
673 ZPB_METADATA_PKG.REMOVE_INSTANCE(g_personalAW,
674 p_instance,
675 p_type,
676 p_template,
677 p_approvee);
678
679 if (p_type = 'SHARED_VIEW') then
680 l_mode := 'TOTAL';
681 l_template := 'NA';
682 l_user := 'NA';
683 elsif (p_type = 'PERSONAL') then
684 l_mode := 'PERSONAL';
685 l_template := ''''||p_template||'''';
686 l_user := 'NA';
687 elsif (p_type = 'APPROVER') then
688 l_mode := 'PERSONAL';
689 l_template := ''''||p_template||'''';
690 l_user := ''''||p_approvee||'''';
691 end if;
692
693 zpb_aw.execute ('call CM.DELPERSINST ('''||p_instance||''' '''||l_mode||
694 ''' '||l_template||' '||l_user||')');
695
696 end MEASURES_DELETE;
697
698 -------------------------------------------------------------------------------
699 -- MEASURES_SHARED_UPDATE - Creates any formulas and views that point to the
700 -- shared AW measure formulas
701 --
702 -- IN: p_user - User ID
703 -- x_return_status - The return status
704 --
705 -- OUT: whether the structures have changed to require a new Metadata Map
706 -------------------------------------------------------------------------------
707 function MEASURES_SHARED_UPDATE(p_user IN VARCHAR2,
708 x_return_status IN OUT NOCOPY VARCHAR2)
709
710 return BOOLEAN
711 is
712 l_value varchar2(20);
713 begin
714 zpb_log.write('zpb_personal_aw.measures_shared_update.begin',
715 'Creating structures for shared measures');
716
717 l_value := zpb_aw.interp ('call PA.MERGE.INST ('''||p_user||''' '''||
718 FND_GLOBAL.RESP_ID||''' '''||
719 g_personalAW||''' '''||g_sharedAW||''')');
720 if (l_value <> 'S') then
721 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
722 FND_API.G_RET_STS_UNEXP_ERROR);
723 end if;
724
725 zpb_log.write('zpb_personal_aw.measures_shared_update.end', 'Done');
726 return true;
727 end MEASURES_SHARED_UPDATE;
728 -------------------------------------------------------------------------------
729 -- METADATA_CREATE - Copies the metadata objects from shared AW into a
730 -- new personal AW. Copies all dimensions, hierarchies,
731 -- levels, aggregation, allocation, and attributes defined
732 -- in ECM.
733 --
734 -- IN: p_user - User ID
735 -------------------------------------------------------------------------------
736 procedure METADATA_CREATE (p_user in varchar2)
737 is
738 l_userAW varchar2(16);
739 l_dims varchar2(4000);
740 l_attrs varchar2(4000);
741 l_ecmDim varchar2(16);
742 l_ecmAttr varchar2(16);
743 l_value varchar2(30);
744 i number;
745 j number;
746 l_objList dbms_aw.eif_objlist_t;
747 l_global_ecm zpb_ecm.global_ecm;
748 l_dim_data zpb_ecm.dimension_data;
749 l_dim_ecm zpb_ecm.dimension_ecm;
750 l_dim_line_ecm zpb_ecm.dimension_line_ecm;
751 l_dim_time_ecm zpb_ecm.dimension_time_ecm;
752 l_global_attr_ecm zpb_ecm.global_attr_ecm;
753 l_aggr_ecm zpb_ecm.aggr_ecm;
754 l_alloc_ecm zpb_ecm.alloc_ecm;
755 l_annot_ecm zpb_ecm.annot_ecm;
756 l_attr_ecm zpb_ecm.attr_ecm;
757 l_line_type_ecm zpb_ecm.line_type_ecm;
758
759 begin
760 zpb_log.write('zpb_personal_aw.metadata_create.begin',
761 'Creating personal metadata for '||p_user);
762
763 get_aws(p_user);
764 zpb_aw.execute ('call pa.attach.shared ('''||p_user||''')');
765
766 l_global_ecm := zpb_ecm.get_global_ecm(g_sharedAW);
767
768 --
769 -- GLOBAL METADATA:
770 --
771
772 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_annotAW||' ro');
773 l_annot_ecm := zpb_ecm.get_annotation_ecm(g_sharedAW);
774 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_annotAW);
775
776 zpb_aw.execute ('oknullstatus=yes');
777 zpb_aw.execute ('lmt '||l_global_ecm.ShapeEntryDim||' remove all');
778 zpb_aw.execute ('lmt '||l_global_ecm.MeasViewDim||' remove all');
779 zpb_aw.execute ('lmt '||l_annot_ecm.CellsObjDim||' remove all');
780 zpb_aw.execute ('lmt '||l_annot_ecm.LookupObjDim||' remove all');
781 zpb_aw.execute ('limit '||l_global_ecm.SecUserDim||' to '''||p_user||'''');
782 zpb_aw.execute ('limit '||l_global_ecm.SecEntityDim||' to user.entity');
783 zpb_aw.execute ('lmt MEASURE remove all');
784 zpb_aw.execute ('lmt INSTANCE remove all');
785 zpb_aw.execute ('lmt LANG to all');
786
787 --
788 -- Dimension metadata:
789 --
790 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
791 i := 1;
792 loop
793 j := instr (l_dims, ' ', i);
794 if (j = 0) then
795 l_ecmDim := substr (l_dims, i);
796 else
797 l_ecmDim := substr (l_dims, i, j-i);
798 i := j+1;
799 end if;
800 l_dim_ecm := zpb_ecm.get_dimension_ecm (l_ecmDim, g_sharedAW);
801 l_dim_data := zpb_ecm.get_dimension_data (l_ecmDim, g_sharedAW);
802
803 --
804 -- Limit the levels/hierarchies down to what the user has access to
805 --
806 if (zpb_aw.interp('shw statlen('||l_dim_ecm.HierDim||')') <> '0') then
807 if (zpb_aw.interpbool ('shw any ('||l_dim_ecm.LevelDimScpFrm||
808 ' eq ''N'') or any ('||
809 l_dim_ecm.HierDimScpFrm||' eq ''N'')')) then
810 zpb_aw.execute('mnt '||l_dim_ecm.LevelDim||' delete '||
811 l_dim_ecm.LevelDimScpFrm||' eq ''N''');
812 zpb_aw.execute('mnt '||l_dim_ecm.HierDim||' delete '||
813 l_dim_ecm.HierDimScpFrm||' eq ''N''');
814
815 --
816 -- Fix the parentrel/inhier
817 --
818 zpb_aw.execute ('push '||l_dim_data.ExpObj);
819 zpb_aw.execute ('lmt '||l_dim_data.ExpObj||' to '||
820 l_dim_ecm.LevelRel||' eq NA');
821 zpb_aw.execute (l_dim_ecm.InHierVar||' = NO');
822 zpb_aw.execute (l_dim_ecm.ParentRel||' = NA');
823
824 zpb_aw.execute ('lmt '||l_dim_data.ExpObj||' to '||
825 l_dim_ecm.InHierVar||' ('||l_dim_data.ExpObj||' '||
826 l_dim_ecm.ParentRel||') eq NO');
827 zpb_aw.execute (l_dim_ecm.ParentRel||' = NA');
828
829 zpb_aw.execute ('pop '||l_dim_data.ExpObj);
830
831 zpb_aw.execute ('call DB.SET.GID('''||g_olapSchema||'.'||
832 g_sharedAW||''' '''||l_dim_data.ExpObj||''')');
833
834 zpb_aw.execute ('call PA.SET.ORDER (''SHARED'' '''||
835 l_dim_data.ExpObj||''')');
836 end if;
837 end if;
838 exit when j=0;
839 end loop;
840
841 l_global_attr_ecm := zpb_ecm.get_global_attr_ecm(g_sharedAW);
842
843 zpb_aw.execute('lmt '||l_global_ecm.AttrDim||' to '||
844 l_global_ecm.AttrDimScpFrm||' eq ''N''');
845 zpb_aw.execute('lmt '||l_global_ecm.DimDim||' to all');
846 zpb_aw.execute('mnt '||l_global_ecm.DimDim||' delete '||
847 l_global_attr_ecm.RangeDimRel);
848 zpb_aw.execute('mnt '||l_global_ecm.AttrDim||' delete '||
849 l_global_ecm.AttrDimScpFrm||' eq ''N''');
850
851 zpb_aw.execute ('aw attach '||g_olapSchema||'.'||g_sharedAW||' first');
852 zpb_aw.execute ('lmt name to obj(property ''PERSONALOBJ'') eq yes');
853 import (p_user, null, 'N');
854
855 zpb_aw.execute ('call PA.ATTACH.SHARED('''||p_user||''' false)');
856
857 zpb_log.write('zpb_personal_aw.metadata_create.end',
858 'Created personal metadata for '||p_user);
859
860 end METADATA_CREATE;
861
862 -------------------------------------------------------------------------------
863 -- METADATA_UPDATE - Updates the Personal AW with any changes to the shared
864 -- AW's metadata. It will merge the changes in the
865 -- shared with the personal, never deleting any user-
866 -- created personal metadata or data objects.
867 --
868 -- IN: p_user - The user ID
869 -- x_return_status - The return status
870 --
871 -- OUT: The list of Dimension ID's whose views need to be recreated
872 -------------------------------------------------------------------------------
873 function METADATA_UPDATE(p_user IN VARCHAR2,
874 x_return_status IN OUT NOCOPY VARCHAR2)
875 return VARCHAR2
876 is
877 l_ret varchar2(500);
878 l_proc varchar2(15) := 'metadata_update';
879 begin
880 --
881 -- First determine if we are going to update the metadata scoping
882 --
883 l_ret := zpb_aw.interp('shw PA.META.UPDATE('''||p_user||''' '''||
884 g_olapSchema||'.'||g_sharedAW||''' '''||
885 g_olapSchema||'.'||g_personalAW||''')');
886 if (l_ret = 'NA' or l_ret = '') then
887 l_ret := null;
888 end if;
889
890 return l_ret;
891
892 exception
893 when others then
894 ZPB_LOG.LOG_PLSQL_EXCEPTION (G_PKG_NAME, l_proc);
895 ZPB_ERROR_HANDLER.REGISTER_CONFIRMATION
896 (G_PKG_NAME,
897 l_proc,
898 'ZPB_STARTUP_META_UPD_ERR_MSG');
899
900 zpb_aw.execute ('aw detach '||g_olapSchema||'.'||g_personalAW);
901 zpb_aw.execute ('call PA.ATTACH.PERSONAL('''||p_user||''' '''||
902 g_attach_mode||''')');
903 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
904 FND_API.G_RET_STS_UNEXP_ERROR);
905 return null;
906 end METADATA_UPDATE;
907
908 -------------------------------------------------------------------------------
909 -- SECURITY_UPDATE - Updates the data access control structures to reflect
910 -- the last maintenance changes for a given user.
911 --
912 -- IN: p_user - The User Id
913 -- x_return_status - The return status
914 ------------------------------------------------------------------------------
915 procedure SECURITY_UPDATE(p_user IN VARCHAR2,
916 x_return_status IN OUT NOCOPY VARCHAR2)
917 is
918 l_proc varchar2(31) := 'security_update';
919 l_dims varchar2(500);
920 l_ecmDim varchar2(30);
921 i number;
922 j number;
923 l_dim_data zpb_ecm.dimension_data;
924 begin
925 zpb_aw.execute('call SC.SET.PERS.SCP('''||p_user||''' '''||
926 g_olapSchema||'.'||g_sharedAW||''' '''||g_olapSchema||'.'||
927 g_personalAW||''')');
928 exception
929 when others then
930 ZPB_LOG.LOG_PLSQL_EXCEPTION (G_PKG_NAME, l_proc);
931 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
932 l_proc,
933 'ZPB_STARTUP_SEC_ERR_MSG');
934 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
935 FND_API.G_RET_STS_UNEXP_ERROR);
936
937 end SECURITY_UPDATE;
938
939 -------------------------------------------------------------------------------
940 -- API name : Startup
941 -- Type : Private
942 -- Function : Starts up the OLAP session for the user. Attaches the AW's
943 -- needed for the session, synch's them up, and distributes any
944 -- measures needed
945 -- Pre-reqs : None.
946 -- Parameters :
947 -- IN : p_api_version IN NUMBER Required
948 -- p_init_msg_list IN VARCHAR2 Optional Default = G_FALSE
949 -- p_commit IN VARCHAR2 Optional Default = G_FALSE
950 -- p_validation_level IN NUMBER Optional Default = G_VALID_LEVEL_FULL
951 -- p_user_id IN NUMBER The user id to start up
952 --
953 -- OUT : x_return_status OUT VARCHAR2(1)
954 -- x_msg_count OUT NUMBER
955 -- x_msg_data OUT VARCHAR2(2000)
956 --
957 -- Version : Current version 1.0
958 -- Initial version 1.0
959 --
960 -- Notes : None
961 --
962 -------------------------------------------------------------------------------
963 procedure STARTUP(p_api_version IN NUMBER,
964 p_init_msg_list IN VARCHAR2,
965 p_commit IN VARCHAR2,
966 p_validation_level IN NUMBER,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2,
970 p_user IN VARCHAR2,
971 p_read_only IN VARCHAR2)
972 is
973 l_api_name CONSTANT VARCHAR2(30) := 'Startup';
974 l_api_version CONSTANT NUMBER := 1.0;
975
976 l_resp number;
977 l_app number;
978 l_mgrResp number;
979 l_count number;
980 l_comm varchar2(200);
981 l_updated boolean;
982 l_aw varchar2(60);
983 l_trace varchar2(90);
984 l_rw_mode VARCHAR2(1);
985 l_ro VARCHAR2(1);
986
987 l_ecm zpb_ecm.global_ecm;
988
989 cursor del_wrksh is
990 select distinct TEMPLATE_ID, AC_INSTANCE_ID
991 from ZPB_DC_OBJECTS
992 where DELETE_INSTANCE_MEASURES_FLAG = 'Y'
993 and OBJECT_USER_ID = p_user
994 and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
995 begin
996 --
997 -- Begin generic Apps PL/SQL API:
998 --
999
1000 if not FND_API.Compatible_API_Call (l_api_version,
1001 p_api_version,
1002 l_api_name,
1003 G_PKG_NAME) then
1004 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1005 end if;
1006
1007 if (FND_API.TO_BOOLEAN (p_init_msg_list)) then
1008 FND_MSG_PUB.INITIALIZE;
1009 end if;
1010
1011 x_return_status := FND_API.G_RET_STS_SUCCESS;
1012 zpb_log.write('zpb_personal_aw.startup', 'Begin startup for '||p_user);
1013
1014 --dbms_output.put_line ('Start: '||to_char(sysdate, 'HH:MI:SS'));
1015
1016 select RESPONSIBILITY_ID
1017 into l_mgrResp
1018 from FND_RESPONSIBILITY
1019 where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
1020
1021 l_resp := nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')),
1022 FND_GLOBAL.RESP_ID);
1023 --
1024 -- Temp workaround of USER_ID being -1 in OLAP connection:
1025 --
1026 if (FND_GLOBAL.USER_ID = -1) then
1027 select APPLICATION_ID
1028 into l_app
1029 from FND_APPLICATION
1030 where APPLICATION_SHORT_NAME = 'ZPB';
1031
1032 FND_GLOBAL.APPS_INITIALIZE(to_number(p_user), l_resp, l_app);
1033 end if;
1034
1035 get_aws(p_user);
1036
1037 -- if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1038 --dbms_output.put_line ('End Attach: '||to_char(sysdate, 'HH:MI:SS'));
1039
1040 --
1041 -- Attempt to attach the Personal AW:
1042 --
1043 if (p_read_only = FND_API.G_FALSE) then
1044 g_attach_mode := 'rw';
1045 l_rw_mode := FND_API.G_TRUE;
1046 l_ro := 'N';
1047 else
1048 g_attach_mode := 'ro';
1049 l_rw_mode := FND_API.G_FALSE;
1050 l_ro := 'Y';
1051 end if;
1052
1053 if (l_resp <> l_mgrResp) then
1054
1055 ZPB_AW.INITIALIZE_USER (p_api_version => 1.0,
1056 x_return_status => x_return_status,
1057 x_msg_count => x_msg_count,
1058 x_msg_data => x_msg_data,
1059 p_user => p_user,
1060 p_attach_readwrite => l_rw_mode,
1061 p_sync_shared => FND_API.G_FALSE,
1062 p_detach_all => FND_API.G_TRUE);
1063 --
1064 -- Run upgrade script:
1065 --
1066 l_comm := DBMS_AW.EVAL_TEXT ('DB.UPGRADE ('''||g_olapSchema||'.'||
1067 g_personalAW||''' ''PERSONAL'' NA '''||l_ro||''')');
1068
1069 l_ecm := zpb_ecm.get_global_ecm (g_personalAW);
1070 l_aw := g_olapSchema||'.'||g_personalAW;
1071
1072 --
1073 -- Update the AW with changes from the shared:
1074 --
1075 l_updated := false;
1076 --dbms_output.put_line ('Upd start: '||to_char(sysdate, 'HH:MI:SS'));
1077 l_updated := AW_UPDATE(p_user, x_return_status, p_read_only);
1078
1079 --
1080 -- Look for deleted worksheets:
1081 --
1082 for each in del_wrksh loop
1083 MEASURES_DELETE (p_user, each.AC_INSTANCE_ID,
1084 'PERSONAL', each.TEMPLATE_ID);
1085 end loop;
1086
1087 MEASURES_APPROVER_UPDATE(p_user, x_return_status);
1088
1089 if (p_read_only = FND_API.G_FALSE) then
1090 zpb_aw.execute ('upd');
1091 commit;
1092 end if;
1093 --dbms_output.put_line ('Update end: '||to_char(sysdate, 'HH:MI:SS'));
1094
1095 --
1096 -- Detach and Re-attach the Shared AW, augmenting the dimensions with
1097 -- the personal AW's personal AW dimension members
1098 --
1099 zpb_aw.execute ('call PA.ATTACH.SHARED('''||p_user||''' yes)');
1100 else
1101 ZPB_AW.INITIALIZE (p_api_version => 1.0,
1102 x_return_status => x_return_status,
1103 x_msg_count => x_msg_count,
1104 x_msg_data => x_msg_data,
1105 p_business_area_id => sys_context('ZPB_CONTEXT',
1106 'business_area_id'),
1107 p_shadow_id => p_user);
1108
1109 end if;
1110
1111 -- end if;
1112
1113 if (FND_API.TO_BOOLEAN (p_commit)) then
1114 commit work;
1115 end if;
1116
1117 --
1118 -- Enable tracing if requested (do it after startup, as attaching AW's
1119 -- can hog tracefiles:
1120 --
1121
1122 l_trace := FND_PROFILE.VALUE_SPECIFIC('ZPB_SQL_TRACE', p_user);
1123 if (l_trace is not null) then
1124 l_trace := substr(l_trace, 1, 1);
1125 if (l_trace = '1' or l_trace = '2' or
1126 l_trace = '3' or l_trace = '4') then
1127 l_comm := 'alter session set max_dump_file_size = unlimited';
1128 execute immediate l_comm;
1129 l_comm := 'alter session set tracefile_identifier = '''||
1130 g_personalAW||'''';
1131 execute immediate l_comm;
1132 l_comm := 'alter session set SQL_TRACE = true';
1133 execute immediate l_comm;
1134 if (l_trace = '2' or l_trace = '4') then
1135 l_comm := 'alter session set events=''10046 trace '||
1136 'name context forever, level 4''';
1137 execute immediate l_comm;
1138 end if;
1139 if (l_trace = '3' or l_trace = '4') then
1140 zpb_aw.execute ('dotf tracefile');
1141 end if;
1142 end if;
1143 end if;
1144
1145 FND_MSG_PUB.COUNT_AND_GET
1146 (p_count => x_msg_count,
1147 p_data => x_msg_data);
1148
1149 zpb_log.write('zpb_personal_aw.startup.end',
1150 'Completed startup for '||p_user);
1151 /*
1152 exception
1153 when FND_API.G_EXC_ERROR then
1154 x_return_status := FND_API.G_RET_STS_ERROR;
1155 FND_MSG_PUB.COUNT_AND_GET
1156 (p_count => x_msg_count,
1157 p_data => x_msg_data);
1158
1159 when FND_API.G_EXC_UNEXPECTED_ERROR then
1160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1161 FND_MSG_PUB.COUNT_AND_GET
1162 (p_count => x_msg_count,
1163 p_data => x_msg_data);
1164
1165 when OTHERS then
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1167 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
1168 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1169 end if;
1170 FND_MSG_PUB.COUNT_AND_GET
1171 (p_count => x_msg_count,
1172 p_data => x_msg_data);
1173 */
1174 end STARTUP;
1175
1176 -------------------------------------------------------------------------------
1177 -- VIEWS_UPDATE - Creates/Updates the Dimension LMAPs for the personal AW.
1178 --
1179 -- IN: p_user - User ID
1180 -- p_dims - Space-separated list of dimension id's to update.
1181 -- List is generated in METADATA_UPDATE
1182 -------------------------------------------------------------------------------
1183 procedure VIEWS_UPDATE(p_aw in varchar2,
1184 p_user in varchar2,
1185 p_dims in varchar2,
1186 p_doPers in varchar2)
1187 is
1188 l_dims VARCHAR2(500);
1189 l_hiers VARCHAR2(1000);
1190 l_ecmDim VARCHAR2(60);
1191 l_hier VARCHAR2(60);
1192 l_aw VARCHAR2(60);
1193 i NUMBER;
1194 j NUMBER;
1195 hi NUMBER;
1196 hj NUMBER;
1197
1198 l_dim_ecm zpb_ecm.dimension_ecm;
1199 l_dim_data zpb_ecm.dimension_data;
1200 begin
1201 zpb_log.write('zpb_personal_aw.views_update.begin',
1202 'Updating metadata views for dims: '||p_dims);
1203
1204 if (p_dims is not null) then
1205 zpb_aw.execute ('call DB.BUILD.LMAP ('''||g_olapSchema||'.'||
1206 g_personalAW||''' '''||p_dims||''')');
1207 else
1208 zpb_aw.execute ('call DB.BUILD.LMAP ('''||g_olapSchema||'.'||
1209 g_personalAW||''')');
1210 end if;
1211
1212 --
1213 -- The following checks for any personal levels with members in each
1214 -- hierarchy, and if it finds one, will rebuild the personal personal
1215 -- dimension view. Bug 4127898.
1216 --
1217 if (p_doPers = 'Y') then
1218 if (p_dims is not null) then
1219 l_dims := p_dims;
1220 else
1221 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
1222 end if;
1223
1224 l_aw := ZPB_AW.GET_SCHEMA||'.'||p_aw||'!';
1225 i := 1;
1226 loop
1227 j := instr (l_dims, ' ', i);
1228 if (j = 0) then
1229 l_ecmDim := substr (l_dims, i);
1230 else
1231 l_ecmDim := substr (l_dims, i, j-i);
1232 i := j+1;
1233 end if;
1234 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, p_aw);
1235
1236 --
1237 -- If a personal level exists on the dimension...
1238 --
1239 if (to_number(ZPB_AW.INTERP('shw statlen(lmt('||l_aw||
1240 l_dim_ecm.LevelDim||' to '||l_aw||
1241 l_dim_ecm.LevelPersVar||'))')) > 0) then
1242 hi := 1;
1243 l_dim_data := zpb_ecm.get_dimension_data(l_ecmDim, p_aw);
1244 l_hiers := ZPB_AW.INTERP('shw CM.GETDIMVALUES('''||l_aw||
1245 l_dim_ecm.HierDim||''')');
1246 loop
1247 hj := instr (l_hiers, ' ', hi);
1248 if (hj = 0) then
1249 l_hier := substr (l_hiers, hi);
1250 else
1251 l_hier := substr (l_hiers, hi, hj-hi);
1252 hi := hj+1;
1253 end if;
1254
1255 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.HierDim||' to '''||
1256 l_hier||'''');
1257 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.LevelDim||
1258 ' to '||l_aw||l_dim_ecm.HierLevelVS);
1259 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.LevelDim||
1260 ' keep '||l_aw||l_dim_ecm.LevelPersVar);
1261 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_data.ExpObj||' to '||l_aw||
1262 l_dim_ecm.HOrderVS);
1263 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_data.ExpObj||' keep '||
1264 l_aw||l_dim_ecm.LevelRel);
1265 if (to_number(ZPB_AW.INTERP('shw statlen('||l_aw||
1266 l_dim_data.ExpObj||')')) > 0) then
1267 ZPB_OLAP_VIEWS_PKG.CREATE_DIMENSION_VIEWS(p_aw,
1268 'PERSONAL',
1269 l_ecmDim);
1270 exit;
1271 end if;
1272 exit when hj=0;
1273 end loop;
1274 end if;
1275 exit when j=0;
1276 end loop;
1277 end if;
1278
1279 zpb_log.write('zpb_personal_aw.views_update.end',
1280 'Updated metadata views');
1281 end VIEWS_UPDATE;
1282
1283 -------------------------------------------------------------------------------
1284 -- PERSONAL_AW_RW_SCAN checks whether the given user's personal AW is
1285 -- attached R/W by an open session and returns session info sufficient
1286 -- do close the session.
1287 --
1288 -- IN : user ID
1289 -- IN : Business Area ID
1290 -- OUT : SID
1291 -- OUT : serial_no
1292 -- OUT : sess_user
1293 -- OUT : OS_user
1294 -- OUT : status
1295 -- OUT : schema_name
1296 -- OUT : machine name
1297 -------------------------------------------------------------------------------
1298 procedure PERSONAL_AW_RW_SCAN(p_user in varchar2,
1299 p_business_area in NUMBER,
1300 p_SID out nocopy number,
1301 p_serial_no out nocopy number,
1302 p_sess_user out nocopy varchar2,
1303 p_os_user out nocopy varchar2,
1304 p_status out nocopy varchar2,
1305 p_schema_name out nocopy varchar2,
1306 p_machine out nocopy varchar2)
1307
1308 is
1309 l_val number;
1310 begin
1311
1312 zpb_log.write('zpb_personal_aw.personal_aw_rw_scan.begin',
1313 'Scanning for personal AW r/w session for '||p_user);
1314
1315 --
1316 -- Need to separate these queries. When lumped together, performance is bad
1317 --
1318 select awseq#
1319 into l_val
1320 from sys.aw$
1321 where awname = upper(zpb_aw.get_personal_aw(p_user, p_business_area));
1322
1323 select sid
1324 into l_val
1325 from v$lock
1326 where id1 = 2
1327 and id2 = l_val
1328 and lmode = 5;
1329
1330 select sid, serial#, username, osuser, status, schemaname, machine
1331 into p_SID, p_serial_no, p_sess_user, p_os_user,
1332 p_status, p_schema_name, p_machine
1333 from v$session
1334 where sid = l_val
1335 and status <> 'KILLED';
1336
1337 zpb_log.write('zpb_personal_aw.views_update.end',
1338 'Personal r/w AW sessions scanned for '||p_user);
1339 exception
1340 when no_data_found then
1341 null;
1342 when others then
1343 raise;
1344
1345 end PERSONAL_AW_RW_SCAN;
1346
1347 -------------------------------------------------------------------------------
1348 -- PERSONAL_AW_SESS_CLOSE kills the specified personal AW r/w session.
1349 --
1350 -- IN : SID
1351 -- IN : SERIAL NO
1352 -------------------------------------------------------------------------------
1353 procedure personal_aw_sess_close(p_SID in number, p_serial_no in number)
1354 is
1355 session_id varchar2(64);
1356 l_starttime date;
1357 l_numtries integer;
1358 l_stillExists integer;
1359 begin
1360
1361 select sysdate into l_starttime from dual;
1362 session_id := '''' || p_SID || ',' || p_serial_no || '''';
1363 execute immediate 'alter system kill session ' || session_id || ' immediate';
1364
1365 -- do not return until we are sure that the session has been killed
1366 -- give up if it is not killed after 30 seconds
1367 l_numtries:=0;
1368 while l_numtries < 60 loop
1369 select count(1) into l_stillExists
1370 from v$session ses, v$aw_olap vao
1371 where ses.sid=p_SID and
1372 ses.logon_time<l_starttime and
1373 vao.session_id=ses.sid;
1374 -- if session still not killed sleep for a second and try again
1375 if l_stillExists = 0 then
1376 return;
1377 end if;
1378 dbms_lock.sleep(1);
1379 l_numtries:=l_numtries+1;
1380 end loop;
1381
1382 exception
1383 when others then
1384 raise;
1385
1386 end personal_aw_sess_close;
1387
1388 -------------------------------------------------------------------------------
1389 -- MEASURES_APPROVER_UPDATE - Deletes any approver formulas that have been
1390 -- submitted and thus made obsolete in a previous
1391 -- user session
1392 --
1393 -- IN: p_user - User ID
1394 -- x_return_status - The return status
1395 --
1396 -------------------------------------------------------------------------------
1397 procedure MEASURES_APPROVER_UPDATE(p_user IN VARCHAR2,
1398 x_return_status IN OUT NOCOPY VARCHAR2)
1399 is
1400 l_proc varchar2(25) := 'measures_approver_update';
1401
1402 cursor deleted_measures is
1403 select AC_INSTANCE_ID,
1404 OBJECT_USER_ID,
1405 TEMPLATE_ID
1406 from ZPB_DC_OBJECTS
1407 where APPROVER_USER_ID = p_user
1408 and DELETE_APPROVAL_MEASURES_FLAG = 'Y'
1409 and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
1410
1411 cursor pers_to_attach is
1412 select distinct
1413 OBJECT_USER_ID
1414 from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B , ZPB_MEASURES Z
1415 where APPROVER_USER_ID = p_user
1416 and A.AC_INSTANCE_ID = B.ANALYSIS_CYCLE_ID
1417 and A.AC_INSTANCE_ID = Z.INSTANCE_ID
1418 and A.TEMPLATE_ID = Z.TEMPLATE_ID
1419 and Z.TYPE = 'APPROVER_DATA'
1420 and A.OBJECT_USER_ID = Z.APPROVEE_ID
1421 and A.OBJECT_TYPE in ('C', 'W')
1422 and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1423 and A.business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id')
1424 and A.STATUS='SUBMITTED';
1425
1426 begin
1427
1428 --
1429 -- Remove deleted approver formulas:
1430 --
1431 for del_meas in deleted_measures
1432 loop
1433 begin
1434 MEASURES_DELETE (p_user, del_meas.AC_INSTANCE_ID, 'APPROVER',
1435 del_meas.TEMPLATE_ID, del_meas.OBJECT_USER_ID);
1436
1437 update ZPB_DC_OBJECTS
1438 set DELETE_APPROVAL_MEASURES_FLAG = 'N'
1439 where APPROVER_USER_ID = p_user
1440 and AC_INSTANCE_ID = del_meas.AC_INSTANCE_ID
1441 and TEMPLATE_ID = del_meas.TEMPLATE_ID
1442 and OBJECT_USER_ID = del_meas.OBJECT_USER_ID;
1443 exception
1444 when others then
1445 ZPB_LOG.LOG_PLSQL_EXCEPTION (G_PKG_NAME, l_proc);
1446 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION
1447 (G_PKG_NAME,
1448 l_proc,
1449 'ZPB_STARTUP_MEAS_DLT_ERR_MSG',
1450 'INST',
1451 del_meas.AC_INSTANCE_ID);
1452 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
1453 FND_API.G_RET_STS_UNEXP_ERROR);
1454 end;
1455 end loop;
1456
1457 for personal in pers_to_attach
1458 loop
1459
1460 begin
1461 --
1462 -- Attach the approvee's personal AW:
1463 --
1464 zpb_aw.execute('CALL PA.ATTACH.APPROVEE('''||zpb_aw.get_schema||'.'||
1465 zpb_aw.get_personal_aw(personal.OBJECT_USER_ID)||''')');
1466 exception
1467 when others then
1468 ZPB_LOG.LOG_PLSQL_EXCEPTION (G_PKG_NAME, l_proc);
1469 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION
1470 (G_PKG_NAME,
1471 l_proc,
1472 'ZPB_STARTUP_APP_MEAS_ERR_MSG',
1473 'OBJ',
1474 personal.OBJECT_USER_ID);
1475 ZPB_ERROR_HANDLER.MERGE_STATUS(x_return_status,
1476 FND_API.G_RET_STS_UNEXP_ERROR);
1477 end;
1478 end loop;
1479
1480 end MEASURES_APPROVER_UPDATE;
1481
1482 -------------------------------------------------------------------------
1483 -- UPDATE_SHADOW - Called when a user starts shadowing another user
1484 --
1485 -- IN: p_business_area_id - The current business area id
1486 -- p_shadow_id - The user id of the user who is being shadowed
1487 -----------------------------------------------------------------------
1488 PROCEDURE UPDATE_SHADOW (p_business_area_id IN NUMBER,
1489 p_shadow_id IN NUMBER)
1490 is
1491
1492 l_id_to_set_to number;
1493
1494 begin
1495
1496 l_id_to_set_to := p_shadow_id;
1497 if l_id_to_set_to is null then
1498 l_id_to_set_to := FND_GLOBAL.USER_ID;
1499 end if;
1500
1501
1502 update ZPB_USERS
1503 set SHADOW_ID = l_id_to_set_to,
1504 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1505 LAST_UPDATE_DATE = sysdate
1506 where USER_ID = FND_GLOBAL.USER_ID
1507 and BUSINESS_AREA_ID = p_business_area_id;
1508 end UPDATE_SHADOW;
1509
1510 end ZPB_PERSONAL_AW;