[Home] [Help]
PACKAGE BODY: APPS.ZPB_BUSAREA_VAL
Source
1 PACKAGE BODY ZPB_BUSAREA_VAL AS
2 /* $Header: ZPBVBAVB.pls 120.49 2007/12/04 14:37:23 mbhat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(15) := 'zpb_busarea_val';
5 G_MAX_NAME_LENGTH CONSTANT NUMBER := 30; -- n/3 to allow for UTF 8
6 G_READ_RULE CONSTANT VARCHAR2(9) := 'READ_RULE';
7 G_WRITE_RULE CONSTANT VARCHAR2(10) := 'WRITE_RULE';
8 G_OWNER_RULE CONSTANT VARCHAR2(10) := 'OWNER_RULE';
9 G_LOCK_OUT CONSTANT NUMBER := 2;
10 G_BUS_AREA_PATH_PREFIX CONSTANT VARCHAR(23) := 'oracle/apps/zpb/BusArea';
11 G_SECURITY_ADMIN_FOLDER CONSTANT VARCHAR(27) := '/ZPBSystem/Private/Manager';
12
13 G_LINE_DIM_TABLE_NAME VARCHAR2(60);
14 G_MEMBER_ID_COL VARCHAR2(60);
15 G_MEMBER_NAME_COL VARCHAR2(60);
16
17
18 TYPE epb_cur_type is REF CURSOR;
19
20 -----------------------------------------------------------------------------
21 /*
22
23 LOCK_OUT_USER
24
25 This procedure updates ZPB_ACCOUNT_STATES.READ_SCOPE
26 WRITE_SCOPE
27 OWNERSHIP
28 setting these columns to 2 (locked) as needed.
29
30 Also inserts the invalid querys name and path details into
31 the ZPB_VALIDATION_TEMP_DATA table for later retrieval in java layer.
32
33 -- p_baId -- Business Area Id
34 -- p_user_id -- User id pulled from query
35 -- p_queryName -- The Invalid Query Object Name
36 -- p_queryPath -- The Invalid Query object path
37 -- p_queryType -- G_READ_RULE,G_WRITE_RULE,G_OWNER_RULE
38 -- p_queryErrorType -- Tells whether the query is to be fixed +
39 -- marked as Invalid ("F") OR Just Refrshed ("R").
40 -- "R" only if a dimension has been removed
41 -- -- in which case fixing is not going to work.
42 -- p_init_fix -- Flag to confirm whether MD fixing should be done or not
43 -- We do not fix for real-time validation from UI.
44 -- p_statusSqlId -- Status sql id from query
45 */
46 ------------------------------------------------------------------------------
47 PROCEDURE LOCK_OUT_USER(p_baId IN NUMBER,
48 p_userid IN FND_USER.USER_ID%type,
49 p_queryName IN VARCHAR2,
50 p_queryPath IN ZPB_STATUS_SQL.QUERY_PATH%type,
51 p_queryType IN VARCHAR2,
52 p_queryErrorType IN VARCHAR2,
53 p_init_fix IN VARCHAR2,
54 p_statusSqlId IN ZPB_STATUS_SQL.STATUS_SQL_ID%type)
55 IS
56
57 BEGIN
58
59 IF p_init_fix = 'Y'
60 THEN
61 IF p_queryType = G_READ_RULE
62 THEN
63 UPDATE ZPB_ACCOUNT_STATES
64 SET READ_SCOPE = G_LOCK_OUT,
65 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
66 LAST_UPDATE_DATE = SYSDATE,
67 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
68 WHERE USER_ID = p_userId AND
69 BUSINESS_AREA_ID = p_baId;
70 ELSIF p_queryType = G_WRITE_RULE
71 THEN
72 UPDATE ZPB_ACCOUNT_STATES
73 SET WRITE_SCOPE = G_LOCK_OUT,
74 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
75 LAST_UPDATE_DATE = SYSDATE,
76 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
77 WHERE USER_ID = p_userId AND
78 BUSINESS_AREA_ID = p_baId;
79 ELSIF p_queryType = G_OWNER_RULE
80 THEN
81 UPDATE ZPB_ACCOUNT_STATES
82 SET OWNERSHIP = G_LOCK_OUT,
83 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
84 LAST_UPDATE_DATE = SYSDATE,
85 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
86 WHERE USER_ID = p_userId AND
87 BUSINESS_AREA_ID = p_baId;
88 END IF;
89
90 INSERT into ZPB_VALIDATION_TEMP_DATA
91 (BUSINESS_AREA_ID,
92 VALUE_TYPE,
93 VALUE,
94 STATUS_SQL_ID)
95 VALUES (p_baId,
96 p_queryErrorType,
97 p_queryPath || fnd_global.newline()|| p_queryName,
98 p_statusSqlId);
99
100 COMMIT;
101
102 END IF;
103 END LOCK_OUT_USER;
104
105 -------------------------------------------------------------------------
106 -- This procedure attaches the given AWs
107 -------------------------------------------------------------------------
108
109
110 procedure ATTACH_AWS(p_codeAW IN VARCHAR,
111 p_sharedAW IN VARCHAR)
112 is
113 begin
114 dbms_aw.execute ('aw attach '||p_codeAW||' ro');
115 dbms_aw.execute ('aw attach '||p_sharedAW||' ro');
116 dbms_aw.execute ('aw aliaslist '||p_sharedAW||' alias SHARED');
117 EXCEPTION
118 WHEN OTHERS THEN
119 null;
120 end ATTACH_AWS;
121
122 -------------------------------------------------------------------------
123 -- This procedure De-attaches the guven AWs
124 -------------------------------------------------------------------------
125
126 procedure DETACH_AWS(p_codeAW IN VARCHAR,
127 p_sharedAW IN VARCHAR)
128 is
129 begin
130 if (zpb_aw.interpbool('shw aw(attached ''' || p_codeAW || ''')')) then
131 zpb_aw.execute ('aw detach '||p_codeAW);
132 end if;
133 if (zpb_aw.interpbool('shw aw(attached ''' || p_sharedAW || ''')')) then
134 zpb_aw.execute ('aw detach '||p_sharedAW);
135 end if;
136 EXCEPTION
137 WHEN OTHERS THEN
138 null;
139 end DETACH_AWS;
140
141 -------------------------------------------------------------------------
142 -- Function which gets the Line member name given the member ID
143 -------------------------------------------------------------------------
144
145 FUNCTION GET_LINE_MEMBER_DESC(p_memberID IN VARCHAR)
146 RETURN VARCHAR IS
147 l_num NUMBER;
148 l_command VARCHAR2(300);
149 l_memberVal VARCHAR2(255);
150 BEGIN
151
152 l_command := 'SELECT '||G_MEMBER_NAME_COL||' FROM '
153 ||G_LINE_DIM_TABLE_NAME||' WHERE '||G_MEMBER_ID_COL|| ' = ''' ||p_memberID||'''';
154
155 EXECUTE IMMEDIATE l_command INTO l_memberVal;
156
157 return l_memberVal;
158 EXCEPTION
159 WHEN OTHERS THEN
160 return p_memberID;
161 END GET_LINE_MEMBER_DESC;
162 ------------------------------------------------------------------------------
163 /*
164
165 This procedure updates the invalid Published BP definition's status_code as
166 'INVALID_BP' and Inserts the invalid query's name and path details into
167 the ZPB_VALIDATION_TEMP_DATA table for later retrieval in java layer.
168 -- p_queryName -- The Invalid Query Object Name
169 -- p_queryPath -- The Invalid Query object path
170 -- p_queryErrorType -- Tells whether the query is to be fixed +
171 -- marked as Invalid ("F") OR Just Refrshed ("R")
172 -- p_acID -- ANALYSIS_CYCLE_ID
173 -- p_init_fix -- Flag to confirm whether MD fixing should be done
174 -- fixed or not
175 */
176 ------------------------------------------------------------------------------
177 PROCEDURE DISABLE_BP(p_baId IN NUMBER,
178 p_queryName IN VARCHAR,
179 p_queryPath IN VARCHAR,
180 p_queryErrorType IN VARCHAR,
181 p_acID IN zpb_analysis_cycles.analysis_cycle_id%TYPE := NULL,
182 p_init_fix IN VARCHAR2)
183 IS
184 l_num NUMBER;
185
186 BEGIN
187 IF(p_init_fix = 'Y') THEN
188 l_num := 0;
189 INSERT into ZPB_VALIDATION_TEMP_DATA
190 (BUSINESS_AREA_ID,
191 VALUE_TYPE,
192 VALUE,
193 ANALYSIS_CYCLE_ID)
194 VALUES (p_baId,
195 p_queryErrorType ,
196 p_queryPath || fnd_global.newline()|| p_queryName,
197 p_acID);
198
199 IF (p_AcID IS NOT NULL) THEN
200 BEGIN
201 SELECT nvl(published_ac_id, 0)
202 INTO l_num
203 FROM zpb_cycle_relationships
204 WHERE published_ac_id = p_acID;
205 EXCEPTION
206 WHEN no_data_found THEN
207 l_num := 0;
208 END;
209 -- Mark the BP as Invalid only if it is Published
210 IF(l_num <> 0) THEN
211 UPDATE zpb_analysis_cycles
212 SET STATUS_CODE = 'INVALID_BP'
213 WHERE analysis_cycle_id = p_acID
214 AND business_area_id = p_baID;
215 END IF;
216 END IF;
217 COMMIT;
218 END IF;
219 END DISABLE_BP;
220
221 -------------------------------------------------------------------------
222 -- REGISTER_ERROR - Code to distplay a error or warning message to the
223 -- user
224 --
225 --
226 -------------------------------------------------------------------------
227 PROCEDURE REGISTER_ERROR (p_val_type IN VARCHAR2,
228 p_err_type IN VARCHAR2,
229 p_error_msg IN VARCHAR2,
230 p_token_name1 IN VARCHAR2 := null,
231 p_token_val1 IN VARCHAR2 := null,
232 p_translate1 IN VARCHAR2 := 'N',
233 p_token_name2 IN VARCHAR2 := null,
234 p_token_val2 IN VARCHAR2 := null,
235 p_translate2 IN VARCHAR2 := 'N',
236 p_token_name3 IN VARCHAR2 := null,
237 p_token_val3 IN VARCHAR2 := null,
238 p_translate3 IN VARCHAR2 := 'N')
239 is
240 l_token1 VARCHAR2(255);
241 l_token2 VARCHAR2(255);
242 l_token3 VARCHAR2(255);
243 begin
244 if (p_token_name1 is not null) then
245 if (p_translate1 = 'Y') then
246 FND_MESSAGE.SET_NAME('ZPB', p_token_val1);
247 l_token1 := FND_MESSAGE.GET;
248 else
249 l_token1 := p_token_val1;
250 end if;
251 end if;
252 if (p_token_name2 is not null) then
253 if (p_translate2 = 'Y') then
254 FND_MESSAGE.CLEAR;
255 FND_MESSAGE.SET_NAME('ZPB', p_token_val2);
256 l_token2 := FND_MESSAGE.GET;
257 else
258 l_token2 := p_token_val2;
259 end if;
260 end if;
261 if (p_token_name3 is not null) then
262 if (p_translate3 = 'Y') then
263 FND_MESSAGE.CLEAR;
264 FND_MESSAGE.SET_NAME('ZPB', p_token_val3);
265 l_token3 := FND_MESSAGE.GET;
266 else
267 l_token3 := p_token_val3;
268 end if;
269 end if;
270
271 FND_MESSAGE.CLEAR;
272 FND_MESSAGE.SET_NAME('ZPB', p_error_msg);
273 if (p_token_name1 is not null) then
274 FND_MESSAGE.SET_TOKEN(p_token_name1, l_token1);
275 if (p_token_name2 is not null) then
276 FND_MESSAGE.SET_TOKEN(p_token_name2, l_token2);
277 if (p_token_name3 is not null) then
278 FND_MESSAGE.SET_TOKEN(p_token_name3, l_token3);
279 end if;
280 end if;
281 end if;
282
283 insert into ZPB_BUSAREA_VALIDATIONS
284 (VALIDATION_TYPE,
285 ERROR_TYPE,
286 MESSAGE)
287 values (p_val_type,
288 p_err_type,
289 FND_MESSAGE.GET);
290 FND_MESSAGE.CLEAR;
291 end REGISTER_ERROR;
292
293 -------------------------------------------------------------------------
294 -- Validates the existence in the business area of all the dimensions
295 -- required based on the BA's datasets
296 --
297 -------------------------------------------------------------------------
298 PROCEDURE VALIDATE_DATASET_DIMS(p_version_id IN NUMBER)
299 is
300 l_spec_dim_list VARCHAR2(512);
301 l_dataset_id ZPB_BUSAREA_DATASETS.DATASET_ID%type;
302 l_currency ZPB_BUSAREA_VERSIONS.CURRENCY_ENABLED%type;
303 l_dimension_id ZPB_BUSAREA_DIMENSIONS.DIMENSION_ID%type;
304 l_cursor epb_cur_type;
305 l_datatable_dim_list VARCHAR2(1000);
306 l_dataset_dim_list VARCHAR2(1000);
307 l_missing_dim_list VARCHAR2(1000);
308 l_dim_list VARCHAR2(1000);
309 l_dimension_name FEM_DIMENSIONS_VL.DIMENSION_NAME%type;
310 l_command VARCHAR2(3000);
311 i NUMBER;
312 j NUMBER;
313
314 cursor c_dataset_tables is
315 select distinct(TABLE_NAME)
316 from FEM_DATA_LOCATIONS
317 where dataset_code = l_dataset_id;
318
319 cursor c_datasets is
320 select A.DATASET_ID
321 from ZPB_BUSAREA_DATASETS A
322 where A.VERSION_ID = p_version_id
323 and A.DATASET_ID in
324 (select DATASET_CODE
325 from FEM_DATASETS_B
326 where ENABLED_FLAG = 'Y');
327
328 begin
329 --
330 -- Check for datasets that have had one or more dimensions removed
331 --
332 -- is the BA currency enabled
333 select CURRENCY_ENABLED
334 into l_currency
335 from ZPB_BUSAREA_VERSIONS
336 where VERSION_ID = p_version_id;
337
338 -- loop over each dataset
339 l_dataset_dim_list := '';
340 for each in c_datasets loop
341 l_dataset_id := each.DATASET_ID;
342 -- get the comma separated list of specifically defined
343 -- dimension IDs for the given dataset
344 begin
345 select varchar_assign_value
346 into l_spec_dim_list
347 from fem_datasets_attr fdat, fem_dim_attributes_b fatt
348 where fdat.attribute_id = fatt.attribute_id
349 AND fatt.attribute_varchar_label = 'ZPB_DIMENSION_LIST'
350 AND fdat.dataset_code = l_dataset_id;
351
352 exception when NO_DATA_FOUND then
353 l_spec_dim_list := '';
354 end;
355
356 -- find the data table that the dataset resides, if any
357 -- create a list of dimension IDs that are a union of all the dimensions
358 -- in all the dataset tables
359 l_datatable_dim_list := '';
360 for each in c_dataset_tables loop
361 -- get the dimensions that are in the data table
362 -- excluding the ledger dimension,
363 -- the currency dimension,
364 -- and any other dimensions in your list already
365 l_command :=
366 'select distinct(fem_xdims.dimension_id)
367 from fem_tab_column_prop fem_tab, fem_xdim_dimensions fem_xdims, fem_dimensions_b fem_dims
368 where fem_xdims.member_col = fem_tab.column_name
369 AND (fem_tab.table_name = '''||each.TABLE_NAME||''')
370 AND (fem_tab.column_property_code) = ''PROCESSING_KEY''
371 AND fem_dims.DIMENSION_ID = fem_xdims.DIMENSION_ID
372 AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''LEDGER''
373 AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''DATASET''
374 AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''SOURCE_SYSTEM''
375 AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''CURRENCY_TYPE''
376 AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''NATURAL_ACCOUNT''
377 AND fem_xdims.DIMENSION_TYPE_CODE <> ''LINE'' ';
378 if (length(l_dataset_dim_list) > 0 ) then
379 l_command := l_command ||
380 ' AND fem_xdims.dimension_id not in ('||l_dataset_dim_list ||')';
381 end if;
382 -- remove the currency dimension if the BA is currency enabled
383 if (l_currency = 'Y') then
384 l_command := l_command ||
385 ' AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''CURRENCY'' ';
386 end if;
387 -- if l_spec_dim_list has nothing in it, ignore it
388 -- if it has something in it, only get the intersection of dimensions
389 -- with l_spec_dim and what is in the dataset table
390 if (length(l_spec_dim_list) > 0 ) then
391 l_command := l_command ||
392 ' AND fem_xdims.dimension_id in ('||l_spec_dim_list ||')';
393 end if;
394
395 open l_cursor for l_command;
396 l_datatable_dim_list := '';
397 loop
398 fetch l_cursor into l_dimension_id;
399
400 exit when l_cursor%NOTFOUND;
401 if (length(l_datatable_dim_list) > 0) then
402 l_datatable_dim_list := l_datatable_dim_list || ',' || l_dimension_id;
403 else
404 l_datatable_dim_list := l_dimension_id;
405 end if;
406 end loop;
407
408 if (length(l_datatable_dim_list) > 0) then
409 if (length(l_dataset_dim_list) > 0) then
410 l_dataset_dim_list := l_dataset_dim_list || ',' || l_datatable_dim_list;
411 else
412 l_dataset_dim_list := l_datatable_dim_list;
413 end if;
414 end if;
415 end loop;
416
417 -- assemble a select statement to figure out which dimensions are in the dataset tables
418 -- but not in the business area
419 l_command := '';
420 if (length(l_dataset_dim_list) > 0 ) then
421 l_command := 'select dimension_id from ( ';
422 i := 1;
423 loop
424 j := instr (l_dataset_dim_list , ',', i);
425 if (j = 0) then
426 l_dimension_id := substr (l_dataset_dim_list, i);
427 else
428 l_dimension_id := substr (l_dataset_dim_list, i, j-i);
429 i := j+1;
430 end if;
431 l_command := l_command || ' select ' || l_dimension_id || ' dimension_id from dual ';
432 exit when j=0;
433 l_command := l_command || ' union ';
434 end loop;
435 l_command := l_command || ')';
436
437 l_command := l_command ||
438 ' where dimension_id not in (
439 select dimension_id
440 from ZPB_BUSAREA_DIMENSIONS
441 where version_id = ' || p_version_id;
442 -- only get those dimensions that haven't been added previously
443 if (length(l_missing_dim_list) > 0) then
444 if (substr(l_missing_dim_list, length(l_missing_dim_list)) = ',') then
445 l_dim_list := substr(l_missing_dim_list, 1, length(l_missing_dim_list)-1);
446 end if;
447 l_command := l_command || 'AND dimension_id not in(' || l_dim_list || ')';
448 end if;
449 l_command := l_command || ')';
450
451 open l_cursor for l_command;
452 loop
453 fetch l_cursor into l_dimension_id;
454 exit when l_cursor%NOTFOUND;
455 l_missing_dim_list := l_missing_dim_list || l_dimension_id || ',';
456 end loop;
457 end if;
458
459 end loop;
460
461 -- if there are missing dimensions, register an error
462 if (length(l_missing_dim_list) > 0) then
463 -- if the last character of list of dimensions is a comma, get rid of it
464 if (substr(l_missing_dim_list, length(l_missing_dim_list)) = ',') then
465 l_missing_dim_list := substr(l_missing_dim_list, 1, length(l_missing_dim_list)-1);
466 end if;
467 l_command := 'select DIMENSION_NAME
468 from FEM_DIMENSIONS_VL
469 where DIMENSION_ID in ('||l_missing_dim_list||')';
470 l_missing_dim_list := '';
471 open l_cursor for l_command;
472 loop
473 fetch l_cursor into l_dimension_name;
474 exit when l_cursor%NOTFOUND;
475 if (length(l_missing_dim_list) > 0) then
476 l_missing_dim_list := l_missing_dim_list||', '|| l_dimension_name;
477 else
478 l_missing_dim_list := l_dimension_name;
479 end if;
480 end loop;
481
482 -- Bug#4641094: Changed this to a WARNING (instead of an ERROR)
483 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_DSDIMS_MISSING',
484 'DIM_NAMES', l_missing_dim_list, 'N');
485 end if;
486 end VALIDATE_DATASET_DIMS;
487
488
489 -------------------------------------------------------------------------
490 -- VAL_AGAINST_EPF - Validates the Business Area version against EPF, to
491 -- ensure all metadata exists and is enabled in EPF
492 --
493 -- IN: p_version_id - The Version ID to validate
494 -- p_init_msg_list - Whether to initialize the message list
495 --
496 -- OUT: x_return_status - The return status
497 -- x_msg_count - The message count
498 -- x_msg_data - The message data
499 -------------------------------------------------------------------------
500 PROCEDURE VAL_AGAINST_EPF (p_version_id IN NUMBER)
501 is
502 l_proc_name CONSTANT VARCHAR2(33) := G_PKG_NAME||'.val_against_epf';
503
504
505 l_dim_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
506 l_hier_table FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%type;
507 l_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
508 l_vset_required FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
509
510 l_hierarchy ZPB_BUSAREA_HIER_MEMBERS.HIERARCHY_ID%type;
511 l_member_id ZPB_BUSAREA_HIER_MEMBERS.MEMBER_ID%type;
512 l_value_set_id ZPB_BUSAREA_HIER_MEMBERS.VALUE_SET_ID%type;
513 l_logical_dim_id ZPB_BUSAREA_HIER_MEMBERS.LOGICAL_DIM_ID%type;
514
515 l_command VARCHAR2(3000);
516 l_count NUMBER;
517 l_cursor epb_cur_type;
518
519 cursor c_hierarchies is
520 select A.HIERARCHY_ID,
521 A.LOGICAL_DIM_ID
522 from ZPB_BUSAREA_HIERARCHIES A
523 where A.VERSION_ID = p_version_id
524 and A.HIERARCHY_ID not in
525 (select HIERARCHY_OBJ_ID
526 from FEM_HIERARCHIES
527 where PERSONAL_FLAG = 'N');
528
529 cursor c_def_hierarchies is
530 select A.DIMENSION_ID,
531 A.DEFAULT_HIERARCHY_ID,
532 A.LOGICAL_DIM_ID
533 from ZPB_BUSAREA_DIMENSIONS A
534 where A.VERSION_ID = p_version_id
535 and A.DEFAULT_HIERARCHY_ID not in
536 (select HIERARCHY_OBJ_ID
537 from FEM_HIERARCHIES
538 where PERSONAL_FLAG = 'N');
539
540 cursor c_hier_versions is
541 select A.VERSION_ID,
542 A.HIERARCHY_ID,
543 A.HIER_VERSION_ID,
544 A.LOGICAL_DIM_ID
545 from ZPB_BUSAREA_HIER_VERSIONS A
546 where A.VERSION_ID = p_version_id
547 and A.HIER_VERSION_ID not in
548 (select B.OBJECT_DEFINITION_ID
549 from FEM_OBJECT_DEFINITION_B B
550 where A.HIERARCHY_ID = B.OBJECT_ID);
551
552 cursor c_levels is
553 select A.LEVEL_ID,
554 A.HIERARCHY_ID,
555 A.LOGICAL_DIM_ID
556 from ZPB_BUSAREA_LEVELS A
557 where A.VERSION_ID = p_version_id
558 and A.LEVEL_ID not in
559 (select B.DIMENSION_GROUP_ID
560 from FEM_DIMENSION_GRPS_B B,
561 FEM_HIER_DIMENSION_GRPS C
562 where B.DIMENSION_GROUP_ID = C.DIMENSION_GROUP_ID
563 and C.HIERARCHY_OBJ_ID = A.HIERARCHY_ID
564 and B.PERSONAL_FLAG = 'N');
565
566 cursor c_attributes is
567 select A.ATTRIBUTE_ID,
568 A.LOGICAL_DIM_ID
569 from ZPB_BUSAREA_ATTRIBUTES A
570 where A.VERSION_ID = p_version_id
571 and A.ATTRIBUTE_ID not in
572 (select ATTRIBUTE_ID
573 from FEM_DIM_ATTRIBUTES_B
574 where PERSONAL_FLAG = 'N');
575
576 cursor c_ledgers is
577 select A.LEDGER_ID
578 from ZPB_BUSAREA_LEDGERS A
579 where A.VERSION_ID = p_version_id
580 and A.LEDGER_ID not in
581 (select LEDGER_ID
582 from FEM_LEDGERS_B
583 where ENABLED_FLAG = 'Y');
584
585 cursor c_datasets is
586 select A.DATASET_ID
587 from ZPB_BUSAREA_DATASETS A
588 where A.VERSION_ID = p_version_id
589 and A.DATASET_ID not in
590 (select DATASET_CODE
591 from FEM_DATASETS_B
592 where ENABLED_FLAG = 'Y');
593
594 cursor c_dimensions is
595 select A.DIMENSION_ID, B.HIER_EDITOR_MANAGED_FLAG,
596 A.LOGICAL_DIM_ID
597 from ZPB_BUSAREA_DIMENSIONS A, FEM_XDIM_DIMENSIONS B
598 where A.VERSION_ID = p_version_id
599 and A.DIMENSION_ID = B.DIMENSION_ID;
600
601 begin
602 FND_MSG_PUB.INITIALIZE;
603
604 ZPB_LOG.WRITE (l_proc_name||'.begin', 'Begin validation against EPF');
605
606 --
607 -- Check for removed hierarchies
608 --
609 for each in c_hierarchies loop
610 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
611 'OBJ_TYPE', 'ZPB_HIERARCHY', 'Y');
612 ZPB_BUSAREA_MAINT.REMOVE_HIERARCHY(p_version_id,
613 each.LOGICAL_DIM_ID,
614 each.HIERARCHY_ID);
615 end loop;
616
617 --
618 -- Check for removed default hierarchies
619 --
620 for each in c_def_hierarchies loop
621 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
622 'OBJ_TYPE', 'ZPB_DEFAULT_HIERARCHY', 'Y');
623 update ZPB_BUSAREA_DIMENSIONS
624 set DEFAULT_HIERARCHY_ID = null,
625 LAST_UPDATE_DATE = sysdate,
626 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
627 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
628 where VERSION_ID = p_version_id
629 and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
630 and DIMENSION_ID = each.DIMENSION_ID;
631 end loop;
632
633 --
634 -- Check for removed hierarchy versions
635 --
636 for each in c_hier_versions loop
637 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
638 'OBJ_TYPE', 'ZPB_HIERARCHY_VERSION', 'Y');
639 ZPB_BUSAREA_MAINT.REMOVE_HIERARCHY_VERSION(p_version_id,
640 each.LOGICAL_DIM_ID,
641 each.HIERARCHY_ID,
642 each.HIER_VERSION_ID);
643 end loop;
644
645 --
646 -- Check for removed levels within hierarchies
647 --
648 for each in c_levels loop
649 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
650 'OBJ_TYPE', 'ZPB_LEVEL', 'Y');
651 ZPB_BUSAREA_MAINT.REMOVE_LEVEL(p_version_id,
652 each.LOGICAL_DIM_ID,
653 each.HIERARCHY_ID,
654 each.LEVEL_ID);
655 end loop;
656
657 --
658 -- Check for removed attributes
659 --
660 for each in c_attributes loop
661 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
662 'OBJ_TYPE', 'ZPB_ATTRIBUTE', 'Y');
663 ZPB_BUSAREA_MAINT.REMOVE_ATTRIBUTE(p_version_id,
664 each.LOGICAL_DIM_ID,
665 each.ATTRIBUTE_ID);
666 end loop;
667
668 --
669 -- Check for removed ledgers
670 --
671 for each in c_ledgers loop
672 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
673 'OBJ_TYPE', 'ZPB_LEDGER', 'Y');
674 delete from ZPB_BUSAREA_LEDGERS
675 where VERSION_ID = p_version_id
676 and LEDGER_ID = each.LEDGER_ID;
677 end loop;
678
679 --
680 -- Check for removed datasets
681 --
682 for each in c_datasets loop
683 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
684 'OBJ_TYPE', 'ZPB_DATASET', 'Y');
685 delete from ZPB_BUSAREA_DATASETS
686 where VERSION_ID = p_version_id
687 and DATASET_ID = each.DATASET_ID;
688 end loop;
689
690 for each in c_dimensions loop
691 select A.MEMBER_B_TABLE_NAME,
692 A.HIERARCHY_TABLE_NAME,
693 A.MEMBER_COL,
694 A.VALUE_SET_REQUIRED_FLAG
695 into l_dim_table, l_hier_table, l_col, l_vset_required
696 from FEM_XDIM_DIMENSIONS A,
697 ZPB_BUSAREA_DIMENSIONS B
698 where B.DIMENSION_ID = each.DIMENSION_ID
699 and B.VERSION_ID = p_version_id
700 and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
701 AND A.DIMENSION_ID = B.DIMENSION_ID;
702
703 --
704 -- Check for removed top level members of hierarchies
705 --
706 if (l_hier_table is not null) then
707 l_command :=
708 'select A.HIERARCHY_ID, A.MEMBER_ID, A.VALUE_SET_ID,
709 A.LOGICAL_DIM_ID
710 from ZPB_BUSAREA_HIER_MEMBERS A,
711 FEM_HIERARCHIES B
712 where A.HIERARCHY_ID = B.HIERARCHY_OBJ_ID
713 and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
714 and B.DIMENSION_ID = '||each.DIMENSION_ID||'
715 and A.VERSION_ID = '||p_version_id||'
716 and A.MEMBER_ID not in
717 (select distinct C.CHILD_ID
718 from '||l_hier_table||' C,
719 FEM_OBJECT_DEFINITION_B D,
720 '||l_dim_table||' E
721 where C.HIERARCHY_OBJ_DEF_ID = D.OBJECT_DEFINITION_ID
722 and D.OBJECT_ID = A.HIERARCHY_ID
723 and D.OBJECT_DEFINITION_ID = nvl(A.HIER_VERSION_ID,
724 D.OBJECT_DEFINITION_ID)
725 and C.CHILD_ID = E.'||l_col||'
726 and C.CHILD_ID = C.PARENT_ID
727 and C.CHILD_DEPTH_NUM = 1';
728 if (each.HIER_EDITOR_MANAGED_FLAG = 'Y') then
729 l_command := l_command||'
730 and E.ENABLED_FLAG = ''Y''
731 and E.PERSONAL_FLAG = ''N''';
732 end if;
733 if (l_vset_required = 'Y') then
734 l_command := l_command||
735 ' and A.VALUE_SET_ID = C.CHILD_VALUE_SET_ID
736 and A.VALUE_SET_ID = E.VALUE_SET_ID)';
737 else
738 l_command := l_command||')';
739 end if;
740
741 open l_cursor for l_command;
742 loop
743 fetch l_cursor into l_hierarchy, l_member_id, l_value_set_id,l_logical_dim_id;
744 exit when l_cursor%NOTFOUND;
745
746 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_REMOVED',
747 'OBJ_TYPE', 'ZPB_TOP_LEVEL_MEMBER', 'Y');
748 delete from ZPB_BUSAREA_HIER_MEMBERS
749 where VERSION_ID = p_version_id
750 and LOGICAL_DIM_ID = l_logical_dim_id
751 and HIERARCHY_ID = l_hierarchy
752 and MEMBER_ID = l_member_id
753 and VALUE_SET_ID = l_value_set_id;
754 end loop;
755 close l_cursor;
756 end if;
757 end loop;
758
759 ZPB_LOG.WRITE (l_proc_name||'.end', 'End validation against EPF');
760
761 end VAL_AGAINST_EPF;
762
763 -------------------------------------------------------------------------
764 -- VAL_DEFINITION - Validates the Business Area version against itself, to
765 -- ensure there are no internal inconsistencies
766 --
767 -- IN: p_version_id - The Version ID to validate
768 -- p_init_msg_list - Whether to initialize the message list
769 --
770 -- OUT: x_return_status - The return status
771 -- x_msg_count - The message count
772 -- x_msg_data - The message data
773 -------------------------------------------------------------------------
774 PROCEDURE VAL_DEFINITION (p_version_id IN NUMBER)
775 is
776 l_proc_name CONSTANT VARCHAR2(32) := G_PKG_NAME||'.val_definition';
777
778 l_dim_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
779 l_hier_table FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%type;
780 l_attr_table FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%type;
781 l_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
782 l_vset_required FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
783 l_dim_type FEM_XDIM_DIMENSIONS.DIMENSION_TYPE_CODE%type;
784 l_curr_attr_id FEM_DIM_ATTRIBUTES_B.ATTRIBUTE_ID%type;
785 l_org_dim_id FEM_DIM_ATTRIBUTES_B.DIMENSION_ID%type;
786
787 l_hierarchy ZPB_BUSAREA_HIER_MEMBERS.HIERARCHY_ID%type;
788 l_value_set_id ZPB_BUSAREA_HIER_MEMBERS.VALUE_SET_ID%type;
789 l_logical_dim_id ZPB_BUSAREA_HIER_MEMBERS.LOGICAL_DIM_ID%type;
790 l_org_logical_dim_id ZPB_BUSAREA_HIER_MEMBERS.LOGICAL_DIM_ID%type;
791
792 l_ba_id ZPB_BUSAREA_VERSIONS.BUSINESS_AREA_ID%type;
793 l_vers_type ZPB_BUSAREA_VERSIONS.VERSION_TYPE%type;
794 l_currency ZPB_BUSAREA_VERSIONS.CURRENCY_ENABLED%type;
795 l_intercompany ZPB_BUSAREA_VERSIONS.INTERCOMPANY_ENABLED%type;
796
797 l_vs_combo_id FEM_GLOBAL_VS_COMBO_DEFS.GLOBAL_VS_COMBO_ID%type;
798
799 l_owner_dim ZPB_DIMENSIONS.IS_OWNER_DIM%type;
800 l_def_hier ZPB_DIMENSIONS.DEFAULT_HIER%type;
801
802 l_dim_name FEM_DIMENSIONS_VL.DIMENSION_NAME%type;
803 l_attr_name FEM_DIM_ATTRIBUTES_VL.ATTRIBUTE_NAME%type;
804
805 l_hier_id NUMBER;
806 l_hier_vers_id NUMBER;
807 l_hier_name VARCHAR2(150);
808 l_no_hierarchies VARCHAR2(1);
809 l_curr_vers VARCHAR2(1);
810
811 l_command VARCHAR2(2000);
812 l_buffer1 VARCHAR2(1000);
813 l_buffer2 VARCHAR2(1000);
814 l_fdr_desc VARCHAR2(150);
815 l_count NUMBER;
816 l_fdr_id NUMBER;
817 l_cursor epb_cur_type;
818 l_cursor2 epb_cur_type;
819
820 l_attr_monetary_col
821 FEM_DIM_ATTRIBUTES_B.ATTRIBUTE_VALUE_COLUMN_NAME%type;
822 l_attr_ex_acc_col
823 FEM_DIM_ATTRIBUTES_B.ATTRIBUTE_VALUE_COLUMN_NAME%type;
824
825 l_ext_acct_type VARCHAR2(30);
826 l_monetary_stat VARCHAR2(30);
827 l_token VARCHAR2(4000) := null;
828 l_sql_stmt VARCHAR2(4000);
829 l_member_id VARCHAR2(34);
830 l_mem_desc VARCHAR2(255);
831
832
833 cursor c_dimensions is
834 select A.DIMENSION_ID,
835 A.DEFAULT_HIERARCHY_ID,
836 A.USE_MEMBER_CONDITIONS,
837 A.EPB_LINE_DIMENSION,
838 A.LOGICAL_DIM_ID,
839 A.AW_DIM_NAME,
840 A.AW_DIM_PREFIX,
841 DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
842 B.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
843 X.MEMBER_COL
844 from ZPB_BUSAREA_DIMENSIONS A,
845 FEM_FUNC_DIM_SETS_VL FDR,
846 FEM_DIMENSIONS_VL B,
847 FEM_XDIM_DIMENSIONS X
848 where A.VERSION_ID = p_version_id
849 and FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
850 and A.DIMENSION_ID = B.DIMENSION_ID
851 AND X.DIMENSION_ID = A.DIMENSION_ID;
852
853 cursor c_attributes(p_logical_dim_id number) is
854 select A.ATTRIBUTE_ID, B.VERSION_ID, A.NAME
855 from ZPB_BUSAREA_ATTRIBUTES_VL A,
856 FEM_DIM_ATTR_VERSIONS_B B
857 where A.LOGICAL_DIM_ID = p_logical_dim_id
858 and A.VERSION_ID = p_version_id
859 and A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
860 and B.DEFAULT_VERSION_FLAG = 'Y'
861 and B.AW_SNAPSHOT_FLAG = 'N';
862
863
864 cursor c_conditions_vset(p_logical_dim_id number) is
865 select A.ATTRIBUTE_ID, A.VALUE, A.VALUE_SET_ID
866 from ZPB_BUSAREA_CONDITIONS_V A
867 where A.VERSION_ID = p_version_id
868 and A.LOGICAL_DIM_ID = p_logical_dim_id
869 MINUS
870 select A.ATTRIBUTE_ID, A.VALUE, A.VALUE_SET_ID
871 from ZPB_BUSAREA_CONDITIONS_V A,
872 FEM_VALUE_SETS_B C,
873 FEM_GLOBAL_VS_COMBO_DEFS D
874 where A.VERSION_ID = p_version_id
875 and A.LOGICAL_DIM_ID = p_logical_dim_id
876 and A.VALUE_SET_ID is not null
877 and C.VALUE_SET_ID = A.VALUE_SET_ID
878 and C.DIMENSION_ID = A.DIMENSION_ID
879 and D.DIMENSION_ID = A.DIMENSION_ID
880 and D.VALUE_SET_ID = C.VALUE_SET_ID
881 and D.GLOBAL_VS_COMBO_ID = l_vs_combo_id;
882
883
884 cursor c_levels is
885 select name from ZPB_BUSAREA_LEVELS_VL
886 where version_id = p_version_id
887 order by logical_dim_id, hierarchy_id;
888
889 cursor c_hiers is
890 select hierarchy_id, name from ZPB_BUSAREA_HIERARCHIES_VL
891 where version_id = p_version_id
892 order by logical_dim_id;
893
894 begin
895 FND_MSG_PUB.INITIALIZE;
896
897 ZPB_LOG.WRITE (l_proc_name||'.begin', 'Begin BA '||p_version_id||
898 ' validation');
899
900 select BUSINESS_AREA_ID, VERSION_TYPE, CURRENCY_ENABLED,
901 INTERCOMPANY_ENABLED
902 into l_ba_id, l_vers_type, l_currency, l_intercompany
903 from ZPB_BUSAREA_VERSIONS
904 where VERSION_ID = p_version_id;
905
906 -------------------------------------------------------------------------
907 -- Validate Level and Hieracrchy names:
908 -------------------------------------------------------------------------
909
910 -- Check for level name length
911 for each_level in c_levels
912 loop
913 if length(each_level.name) > G_MAX_NAME_LENGTH then
914 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_LONG_NAME',
915 'NAME', each_level.NAME, 'N',
916 'OBJECTTYPE', 'ZPB_LEVEL', 'Y',
917 'MAX_NAME_LENGTH', G_MAX_NAME_LENGTH, 'N');
918 end if;
919 end loop;
920 -- end check for name length
921
922 -- Check for hierarchy name length
923 for each_hier in c_hiers
924 loop
925 if length(each_hier.name) > G_MAX_NAME_LENGTH then
926 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_LONG_NAME',
927 'NAME', each_hier.NAME, 'N',
928 'OBJECTTYPE', 'ZPB_HIERARCHY', 'Y',
929 'MAX_NAME_LENGTH', to_char(G_MAX_NAME_LENGTH), 'N');
930 end if;
931 end loop;
932 -- end check for hierarchy name length
933
934 -- Check for hierarchies with no current version
935 for each_hier in c_hiers
936 loop
937 select count(*)
938 into l_count
939 from FEM_OBJECT_DEFINITION_B
940 where OBJECT_ID = each_hier.HIERARCHY_ID
941 and effective_start_date <= sysdate
942 and effective_end_date >= sysdate;
943 if (l_count = 0) then
944 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_HIER_NOCURR',
945 'NAME', each_hier.NAME, 'N');
946 end if;
947 end loop;
948
949 -- end check for hierarchies with no current version
950
951 -------------------------------------------------------------------------
952 -- Validate users:
953 -------------------------------------------------------------------------
954 select count(*)
955 into l_count
956 from ZPB_BUSAREA_USERS A,
957 FND_USER_RESP_GROUPS B,
958 FND_RESPONSIBILITY C,
959 FND_USER D
960 where A.BUSINESS_AREA_ID = l_ba_id
961 and A.USER_ID = B.USER_ID
962 and B.RESPONSIBILITY_APPLICATION_ID = 210
963 and nvl(B.END_DATE, sysdate) >= sysdate
964 and nvl(B.START_DATE, sysdate) <= sysdate
965 and B.RESPONSIBILITY_ID = C.RESPONSIBILITY_ID
966 and C.APPLICATION_ID = 210
967 and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
968 and A.USER_ID = D.USER_ID
969 and nvl(D.END_DATE, sysdate) >= sysdate
970 and D.START_DATE <= sysdate;
971 if (l_count = 0) then
972 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_NO_SEC_USER');
973 end if;
974
975 -------------------------------------------------------------------------
976 -- Validate # of ledgers, dimensions, etc
977 -------------------------------------------------------------------------
978 select count(*)
979 into l_count
980 from ZPB_BUSAREA_DIMENSIONS A, FEM_XDIM_DIMENSIONS B
981 where A.VERSION_ID = p_version_id
982 and A.DIMENSION_ID = B.DIMENSION_ID
983 and B.DIMENSION_TYPE_CODE = 'TIME';
984 if (l_count = 0) then
985 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_MISSING',
986 'OBJ_TYPE', 'ZPB_TIME_DIMENSION', 'Y');
987 end if;
988
989 select count(*)
990 into l_count
991 from ZPB_BUSAREA_DIMENSIONS
992 where VERSION_ID = p_version_id
993 and EPB_LINE_DIMENSION = 'Y';
994 if (l_count = 0) then
995 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_MISSING',
996 'OBJ_TYPE', 'ZPB_LINE_DIMENSION', 'Y');
997 end if;
998
999 if (l_currency = 'Y') then
1000 select count(*)
1001 into l_count
1002 from ZPB_BUSAREA_DIMENSIONS A,
1003 FEM_XDIM_DIMENSIONS B
1004 where A.VERSION_ID = p_version_id
1005 and A.DIMENSION_ID = B.DIMENSION_ID
1006 and B.MEMBER_B_TABLE_NAME = 'FEM_CURRENCIES_VL';
1007 if (l_count = 0) then
1008 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_NO_CURR_DIM');
1009 end if;
1010 end if;
1011
1012 select count(*)
1013 into l_count
1014 from ZPB_BUSAREA_LEDGERS
1015 where VERSION_ID = p_version_id;
1016 if (l_count = 0) then
1017 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_MISSING',
1018 'OBJ_TYPE', 'ZPB_LEDGER', 'Y');
1019 --
1020 -- Abort check of dims. Need ledger to check dims
1021 --
1022 return;
1023 end if;
1024
1025 -----------------------------------------------------------------------
1026 -- Validate that all dataset dimensions are included
1027 -----------------------------------------------------------------------
1028 VALIDATE_DATASET_DIMS(p_version_id);
1029
1030 -----------------------------------------------------------------------
1031 -- Validate all ledgers in same valueset combo:
1032 -- NOTE: Different validation if the BA contains an FDR
1033 -----------------------------------------------------------------------
1034 -- check to see if the BA has an FDR
1035 select nvl(FUNC_DIM_SET_OBJ_DEF_ID, -99)
1036 into l_fdr_id
1037 from ZPB_BUSAREA_VERSIONS
1038 where VERSION_ID = p_version_id;
1039
1040 if (l_fdr_id = -99) then
1041 select count(distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER))
1042 into l_count
1043 from ZPB_BUSAREA_LEDGERS B,
1044 FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
1045 FEM_DIM_ATTR_VERSIONS_B E
1046 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
1047 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
1048 and E.DEFAULT_VERSION_FLAG = 'Y'
1049 and E.AW_SNAPSHOT_FLAG = 'N'
1050 and C.VERSION_ID = E.VERSION_ID
1051 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
1052 and B.LEDGER_ID = C.LEDGER_ID
1053 and B.VERSION_ID = p_version_id;
1054 else
1055 select count(distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER))
1056 into l_count
1057 from ZPB_BUSAREA_LEDGERS B,
1058 FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
1059 FEM_DIM_ATTR_VERSIONS_B E,
1060 fem_object_definition_b objdef,fem_object_catalog_b obj
1061 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
1062 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
1063 and E.DEFAULT_VERSION_FLAG = 'Y'
1064 and E.AW_SNAPSHOT_FLAG = 'N'
1065 and C.VERSION_ID = E.VERSION_ID
1066 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
1067 and B.LEDGER_ID = C.LEDGER_ID
1068 and B.VERSION_ID = p_version_id
1069 and objdef.object_definition_id=l_fdr_id
1070 and objdef.object_id=obj.object_id
1071 and C.DIM_ATTRIBUTE_NUMERIC_MEMBER<>obj.LOCAL_VS_COMBO_ID;
1072 end if;
1073
1074 if (l_count > 1) then
1075 if (l_fdr_id = -99) then
1076 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_LEDGERVS');
1077 else
1078 SELECT A.DISPLAY_NAME
1079 into l_fdr_desc
1080 FROM FEM_OBJECT_DEFINITION_VL A, FEM_FUNC_DIM_SETS_B B,
1081 ZPB_BUSAREA_VERSIONS C
1082 WHERE C.VERSION_ID = p_version_id
1083 and A.OBJECT_DEFINITION_ID = C.FUNC_DIM_SET_OBJ_DEF_ID;
1084
1085 REGISTER_ERROR('S', 'E', 'ZPB_BA_INV_FDR_GSVC',
1086 'ZPB_BUSAREA_FDR_NAME_TOKEN', l_fdr_desc, 'N');
1087 end if;
1088 --
1089 -- NEED TO ABORT REST OF CHECK
1090 --
1091 return;
1092 end if;
1093
1094 --
1095 -- Check the currency-org attribute
1096 --
1097 if (l_currency = 'Y') then
1098 begin
1099 select count(*)
1100 into l_count
1101 from FEM_DIM_ATTRIBUTES_B A,
1102 ZPB_BUSAREA_DIMENSIONS B
1103 where A.ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1104 and A.DIMENSION_ID = B.DIMENSION_ID
1105 and B.VERSION_ID = p_version_id;
1106 if (l_count > 1) then
1107 select count(distinct B.DIMENSION_ID)
1108 into l_count
1109 from FEM_DIM_ATTRIBUTES_B A,
1110 ZPB_BUSAREA_DIMENSIONS B
1111 where A.ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1112 and A.DIMENSION_ID = B.DIMENSION_ID
1113 and B.VERSION_ID = p_version_id;
1114
1115 if (l_count > 1) then
1116 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_INV_ORG_CURR');
1117 else
1118 begin
1119 select A.ATTRIBUTE_ID, A.DIMENSION_ID, B.LOGICAL_DIM_ID
1120 into l_curr_attr_id, l_org_dim_id, l_org_logical_dim_id
1121 from FEM_DIM_ATTRIBUTES_B A,
1122 ZPB_BUSAREA_DIMENSIONS B,
1123 ZPB_BUSAREA_ATTRIBUTES C
1124 where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1125 and A.DIMENSION_ID = B.DIMENSION_ID
1126 and B.LOGICAL_DIM_ID = C.LOGICAL_DIM_ID
1127 and B.VERSION_ID = p_version_id
1128 and C.VERSION_ID = B.VERSION_ID;
1129 exception
1130 when others then
1131 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_INV_ORG_CURR');
1132 end;
1133 end if;
1134 else
1135 begin
1136 select A.ATTRIBUTE_ID, A.DIMENSION_ID, B.LOGICAL_DIM_ID
1137 into l_curr_attr_id, l_org_dim_id, l_org_logical_dim_id
1138 from FEM_DIM_ATTRIBUTES_B A,
1139 ZPB_BUSAREA_DIMENSIONS B
1140 where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1141 and A.DIMENSION_ID = B.DIMENSION_ID
1142 and B.VERSION_ID = p_version_id;
1143 exception
1144 when others then
1145 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_INV_ORG_CURR');
1146 end;
1147 end if;
1148 exception
1149 when no_data_found then
1150 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_NO_CURR_ATTR');
1151 end;
1152 end if;
1153
1154 if (l_intercompany = 'Y') then
1155 select count(*)
1156 into l_count
1157 from ZPB_BUSAREA_DIMENSIONS A,
1158 FEM_TAB_COLUMNS_B B
1159 where A.VERSION_ID = p_version_id
1160 and A.DIMENSION_ID = B.DIMENSION_ID
1161 and B.COLUMN_NAME = 'INTERCOMPANY_ID'
1162 and B.TABLE_NAME = 'FEM_BALANCES';
1163 if (l_count <> 1) then
1164 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_INV_INTERCOMP');
1165 end if;
1166 end if;
1167
1168 select distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER)
1169 into l_vs_combo_id
1170 from ZPB_BUSAREA_LEDGERS B,
1171 FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
1172 FEM_DIM_ATTR_VERSIONS_B E
1173 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
1174 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
1175 and E.DEFAULT_VERSION_FLAG = 'Y'
1176 and E.AW_SNAPSHOT_FLAG = 'N'
1177 and C.VERSION_ID = E.VERSION_ID
1178 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
1179 and B.LEDGER_ID = C.LEDGER_ID
1180 and B.VERSION_ID = p_version_id;
1181
1182 --
1183 -- Check to ensure line type attribute exists on the line dimension
1184 --
1185 select count(*)
1186 into l_count
1187 from ZPB_BUSAREA_DIMENSIONS A,
1188 FEM_DIM_ATTRIBUTES_B B
1189 where A.DIMENSION_ID = B.DIMENSION_ID
1190 and B.ATTRIBUTE_VARCHAR_LABEL = 'EXTENDED_ACCOUNT_TYPE'
1191 and A.EPB_LINE_DIMENSION = 'Y'
1192 and A.VERSION_ID = p_version_id;
1193 if (l_count = 0) then
1194 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_EXT_ACT_TYPE');
1195 end if;
1196
1197
1198 ZPB_FEM_UTILS_PKG.INIT_HIER_MEMBER_CACHE(l_ba_id, l_vers_type);
1199
1200 for each in c_dimensions loop
1201 -- Check for dimension name length
1202 if length(each.dimension_name) > G_MAX_NAME_LENGTH then
1203 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_LONG_NAME',
1204 'NAME', each.DIMENSION_NAME, 'N',
1205 'OBJECTTYPE', 'ZPB_DIMENSION', 'Y',
1206 'MAX_NAME_LENGTH', G_MAX_NAME_LENGTH, 'N');
1207 end if;
1208 -- end check for dimension name length
1209 ---------------------------------------------------------------------
1210 -- See if there are any members in the dimension
1211 ---------------------------------------------------------------------
1212 l_command := 'select count(*)
1213 from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS
1214 ('||each.DIMENSION_ID||', '||each.LOGICAL_DIM_ID||','||l_ba_id||', '''||l_vers_type||'''))';
1215
1216 open l_cursor for l_command;
1217 fetch l_cursor into l_count;
1218 close l_cursor;
1219
1220 if (l_count = 0) then
1221 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_NOMEMBERS',
1222 'NAME', each.DIMENSION_NAME, 'N',
1223 'OBJECTTYPE', 'ZPB_DIMENSION', 'Y');
1224 end if;
1225
1226 select A.MEMBER_B_TABLE_NAME,
1227 A.HIERARCHY_TABLE_NAME,
1228 A.ATTRIBUTE_TABLE_NAME,
1229 A.MEMBER_COL,
1230 A.VALUE_SET_REQUIRED_FLAG,
1231 A.DIMENSION_TYPE_CODE
1232 into l_dim_table, l_hier_table, l_attr_table,
1233 l_col, l_vset_required, l_dim_type
1234 from FEM_XDIM_DIMENSIONS A,
1235 ZPB_BUSAREA_DIMENSIONS B
1236 where B.DIMENSION_ID = each.DIMENSION_ID
1237 AND B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1238 AND B.VERSION_ID = p_version_id
1239 AND A.DIMENSION_ID = B.DIMENSION_ID;
1240
1241 ---------------------------------------------------------------------
1242 -- Validate that all conditions have the right value set ID
1243 ---------------------------------------------------------------------
1244 --if (each.USE_MEMBER_CONDITIONS = 'Y') then
1245 -- for each_cond_vset in c_conditions_vset(each.LOGICAL_DIM_ID) loop
1246 -- null;
1247 -- end loop;
1248 --end if;
1249
1250 select count(*), decode(count(*), 0, 'Y', 'N')
1251 into l_count, l_no_hierarchies
1252 from ZPB_BUSAREA_HIERARCHIES_VL
1253 where DIMENSION_ID = each.DIMENSION_ID
1254 and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1255 and VERSION_ID = p_version_id;
1256
1257
1258 if (l_count = 0) then
1259 --
1260 -- Test to see if any members of attribute exists in B table
1261 -- can maybe use same command as would be used in SQ refresh code
1262 --
1263 null;
1264
1265 ---------------------------------------------------------------------
1266 -- Verify that the current dimension without hierarchies
1267 -- is not an ownership dim
1268 ---------------------------------------------------------------------
1269 begin
1270 select IS_OWNER_DIM
1271 into l_owner_dim
1272 from ZPB_DIMENSIONS
1273 where BUS_AREA_ID = l_ba_id
1274 and EPB_ID = each.AW_DIM_PREFIX;
1275 if (l_owner_dim = 'YES') then
1276 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_OWNER_NO_HIER',
1277 'NAME', each.DIMENSION_NAME, 'N');
1278 end if;
1279 exception
1280 when no_data_found then
1281 null;
1282 end;
1283
1284 ---------------------------------------------------------------------
1285 -- Time dimension must include a hierarchy
1286 ---------------------------------------------------------------------
1287 if (l_dim_type = 'TIME') then
1288 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_TIME_HIER');
1289 end if;
1290
1291 ---------------------------------------------------------------------
1292 -- Check to see if all attributes added have members associated
1293 ---------------------------------------------------------------------
1294 for each_attr in c_attributes(each.LOGICAL_DIM_ID) loop
1295
1296 -- Check for attribute name length
1297 if length(each.dimension_name) > G_MAX_NAME_LENGTH then
1298 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_LONG_NAME',
1299 'NAME', each.DIMENSION_NAME, 'N',
1300 'OBJECTTYPE', 'ZPB_ATTRIBUTE', 'Y',
1301 'MAX_NAME_LENGTH', G_MAX_NAME_LENGTH, 'N');
1302 end if;
1303 -- end check for attribute name length
1304
1305 l_command := 'select count(*)
1306 from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS
1307 ('||each.DIMENSION_ID||','||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
1308 ''')) A, '||l_attr_table||' B ';
1309 if (l_vset_required = 'Y') then
1310 l_command := l_command||'
1311 where A.MEMBER_ID = to_char(B.'||l_col||
1312 ') and A.VALUE_SET_ID = B.VALUE_SET_ID';
1313 else
1314 l_command := l_command||'
1315 where A.MEMBER_ID = to_char(B.'||l_col||')';
1316 end if;
1317 l_command := l_command||'
1318 and B.ATTRIBUTE_ID = '||each_attr.ATTRIBUTE_ID||'
1319 and B.VERSION_ID = '||each_attr.VERSION_ID||'
1320 and B.AW_SNAPSHOT_FLAG = ''N''';
1321
1322 open l_cursor for l_command;
1323 fetch l_cursor into l_count;
1324 close l_cursor;
1325
1326 if (l_count = 0) then
1327 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_NOATTRASSOC',
1328 'NAME', each_attr.NAME, 'N',
1329 'DIMNAME', each.DIMENSION_NAME, 'N');
1330 end if;
1331 end loop;
1332
1333 else
1334 ---------------------------------------------------------------------
1335 -- Check if default hier set
1336 ---------------------------------------------------------------------
1337 if (each.DEFAULT_HIERARCHY_ID is null) then
1338 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_DEFINE',
1339 'OBJ_TYPE', 'ZPB_DEFAULT_HIERARCHY', 'Y',
1340 'DIM', each.DIMENSION_NAME, 'N');
1341 else
1342 ------------------------------------------------------------------
1343 -- Check if default hierarchy in the BA
1344 ------------------------------------------------------------------
1345 select count(*)
1346 into l_count
1347 from ZPB_BUSAREA_HIERARCHIES
1348 where VERSION_ID = p_version_id
1349 and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1350 and HIERARCHY_ID = each.DEFAULT_HIERARCHY_ID;
1351 if (l_count = 0) then
1352 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_BAD_DEFHIER',
1353 'DIM', each.DIMENSION_NAME, 'N');
1354 end if;
1355 end if;
1356 -------------------------------------------------------------------
1357 -- Check if any hierarchies have no members:
1358 -------------------------------------------------------------------
1359 l_command := 'select distinct HIERARCHY_ID, VERSION_ID, LOGICAL_DIM_ID
1360 from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
1361 ('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||
1362 l_vers_type||'''))';
1363
1364 l_buffer1 := null;
1365 l_buffer2 := null;
1366 open l_cursor for l_command;
1367 loop
1368 fetch l_cursor into l_hier_id, l_hier_vers_id, l_logical_dim_id;
1369 exit when l_cursor%NOTFOUND;
1370
1371 if (l_hier_vers_id is not null and l_hier_vers_id <> '') then
1372 if (l_buffer1 is not null) then
1373 l_buffer1 := l_buffer1||', '||l_hier_vers_id;
1374 else
1375 l_buffer1 := l_hier_vers_id;
1376 end if;
1377 else
1378 if (l_buffer2 is not null) then
1379 l_buffer2 := l_buffer2||', '||l_hier_id;
1380 else
1381 l_buffer2 := l_hier_id;
1382 end if;
1383 end if;
1384 end loop;
1385 close l_cursor;
1386
1387 l_command := 'select A.HIERARCHY_ID, A.VERSION_ID, A.CURRENT_VERSION
1388 from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES
1389 ('||l_ba_id||','''||l_vers_type||''')) A,
1390 ZPB_BUSAREA_HIERARCHIES_VL B
1391 where A.HIERARCHY_ID = B.HIERARCHY_ID
1392 and A.LOGICAL_DIM_ID = B.LOGICAL_DIM_ID
1393 and B.VERSION_ID = '||p_version_id||'
1394 and B.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
1395 and B.DIMENSION_ID = '||each.DIMENSION_ID;
1396 if (l_buffer2 is not null) then
1397 l_command := l_command||' and ';
1398 if (l_buffer1 is not null) then
1399 l_command := l_command||'((A.VERSION_ID not in ('||l_buffer1||
1400 ') and A.CURRENT_VERSION = ''N'') OR ';
1401 end if;
1402 l_command := l_command||'(A.HIERARCHY_ID not in ('||l_buffer2||
1403 ') and A.CURRENT_VERSION = ''Y'')';
1404 if (l_buffer1 is not null) then
1405 l_command := l_command||')';
1406 end if;
1407 end if;
1408 open l_cursor for l_command;
1409 loop
1410 fetch l_cursor into l_hier_id, l_hier_vers_id, l_curr_vers;
1411 exit when l_cursor%NOTFOUND;
1412
1413 if (l_curr_vers = 'Y') then
1414 select OBJECT_NAME
1415 into l_hier_name
1416 from FEM_OBJECT_CATALOG_VL
1417 where OBJECT_ID = l_hier_id;
1418 else
1419 select DISPLAY_NAME
1420 into l_hier_name
1421 from FEM_OBJECT_DEFINITION_VL
1422 where OBJECT_DEFINITION_ID = l_hier_vers_id;
1423 end if;
1424
1425 if (l_hier_id = each.DEFAULT_HIERARCHY_ID and
1426 l_curr_vers = 'Y') then
1427 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_NOMEMBERS',
1428 'NAME', l_hier_name, 'N',
1429 'OBJECTTYPE', 'ZPB_DEFAULT_HIERARCHY', 'Y');
1430 else
1431 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_NOMEMBERS',
1432 'NAME', l_hier_name, 'N',
1433 'OBJECTTYPE', 'ZPB_HIERARCHY', 'Y');
1434 end if;
1435 end loop;
1436 close l_cursor;
1437
1438 ---------------------------------------------------------------------
1439 -- Check for bad time hierarchies
1440 ---------------------------------------------------------------------
1441 if (l_dim_type = 'TIME') then
1442 l_command :=
1443 'select distinct Y.HIERARCHY_ID, Y.VERSION_ID from
1444 FEM_HIER_DIMENSION_GRPS X,
1445 (select distinct A.HIERARCHY_ID, A.VERSION_ID,
1446 B.RELATIVE_DIMENSION_GROUP_SEQ PARENT_SEQ,
1447 C.RELATIVE_DIMENSION_GROUP_SEQ CHILD_SEQ
1448 from (table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
1449 ('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||
1450 l_vers_type||'''))) A,
1451 FEM_HIER_DIMENSION_GRPS B, FEM_HIER_DIMENSION_GRPS C,
1452 '||l_dim_table||' D, '||l_dim_table||' E
1453 where A.PARENT_ID = D.'||l_col||'
1454 and D.DIMENSION_GROUP_ID = B.DIMENSION_GROUP_ID
1455 and B.HIERARCHY_OBJ_ID = A.HIERARCHY_ID
1456 and A.CHILD_ID = E.'||l_col||'
1457 and E.DIMENSION_GROUP_ID = C.DIMENSION_GROUP_ID
1458 and C.HIERARCHY_OBJ_ID = A.HIERARCHY_ID) Y
1459 where X.HIERARCHY_OBJ_ID = Y.HIERARCHY_ID
1460 and X.RELATIVE_DIMENSION_GROUP_SEQ > Y.PARENT_SEQ
1461 and X.RELATIVE_DIMENSION_GROUP_SEQ < Y.CHILD_SEQ';
1462
1463 open l_cursor for l_command;
1464 loop
1465 fetch l_cursor into l_hier_id, l_hier_vers_id;
1466 exit when l_cursor%NOTFOUND;
1467
1468 if (l_hier_vers_id is null) then
1469 select OBJECT_NAME
1470 into l_hier_name
1471 from FEM_OBJECT_CATALOG_VL
1472 where OBJECT_ID = l_hier_id;
1473 else
1474 select DISPLAY_NAME
1475 into l_hier_name
1476 from FEM_OBJECT_DEFINITION_VL
1477 where OBJECT_DEFINITION_ID = l_hier_vers_id;
1478 end if;
1479
1480 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_SKIP_LVL_TIME',
1481 'HIER_NAME', l_hier_name, 'N');
1482 end loop;
1483 end if;
1484 ---------------------------------------------------------------------
1485 -- Check if any levels have no members
1486 ---------------------------------------------------------------------
1487 -- TODO:
1488 --
1489
1490 ---------------------------------------------------------------------
1491 -- Check to see if all attributes added have members associated
1492 ---------------------------------------------------------------------
1493 for each_attr in c_attributes(each.DIMENSION_ID) loop
1494
1495 -- Check for attribute name length
1496 if length(each_attr.name) > G_MAX_NAME_LENGTH then
1497 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_LONG_NAME',
1498 'NAME', each_attr.NAME, 'N',
1499 'OBJECTTYPE', 'ZPB_ATTRIBUTE', 'Y',
1500 'MAX_NAME_LENGTH', G_MAX_NAME_LENGTH, 'N');
1501 end if;
1502 -- end check for atribute name length
1503
1504 l_command := 'select count(*)
1505 from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
1506 ('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
1507 ''')) A, '||l_attr_table||' B ';
1508 if (l_vset_required = 'Y') then
1509 l_command := l_command||'
1510 where substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1) = B.'||
1511 l_col||' and substr(A.CHILD_ID, 1, '||
1512 'instr(A.CHILD_ID, ''_'')-1) = B.VALUE_SET_ID';
1513 else
1514 l_command := l_command||'
1515 where A.CHILD_ID = B.'||l_col;
1516 end if;
1517 l_command := l_command||'
1518 and B.ATTRIBUTE_ID = '||each_attr.ATTRIBUTE_ID||'
1519 and B.VERSION_ID = '||each_attr.VERSION_ID||'
1520 and B.AW_SNAPSHOT_FLAG = ''N''';
1521
1522 open l_cursor for l_command;
1523 fetch l_cursor into l_count;
1524 close l_cursor;
1525
1526 if (l_count = 0) then
1527 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_NOATTRASSOC',
1528 'NAME', each_attr.NAME, 'N',
1529 'DIMNAME', each.DIMENSION_NAME, 'N');
1530 end if;
1531 end loop;
1532
1533 -------------------------------------------------------------------
1534 -- Check to see if changing def hier on ownership dim
1535 -------------------------------------------------------------------
1536 begin
1537 select DEFAULT_HIER, IS_OWNER_DIM
1538 into l_def_hier, l_owner_dim
1539 from ZPB_DIMENSIONS
1540 where BUS_AREA_ID = l_ba_id
1541 and EPB_ID = each.AW_DIM_PREFIX;
1542
1543 if (l_owner_dim = 'YES' and
1544 l_def_hier <> to_char(each.DEFAULT_HIERARCHY_ID)) then
1545 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_OWNER_DEF_HIER',
1546 'NAME', each.DIMENSION_NAME, 'N');
1547 end if;
1548 exception
1549 when no_data_found then
1550 null;
1551 end;
1552
1553 -------------------------------------------------------------------
1554 -- Check to see no missing org-currency attribute relations
1555 -------------------------------------------------------------------
1556 if ((each.DIMENSION_ID = l_org_dim_id) AND
1557 (each.LOGICAL_DIM_ID = l_org_logical_dim_id)) then
1558 l_command :=
1559 'select count(*) '||
1560 'from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS('||
1561 each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
1562 ''')) A, '||l_attr_table||' B, FEM_DIM_ATTR_VERSIONS_B C';
1563 if (l_vset_required = 'Y') then
1564 l_command := l_command||'
1565 where substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1) = B.'||
1566 l_col||' and substr(A.CHILD_ID, 1, '||
1567 'instr(A.CHILD_ID, ''_'')-1) = B.VALUE_SET_ID';
1568 else
1569 l_command := l_command||'where A.CHILD_ID = B.'||l_col;
1570 end if;
1571 l_command := l_command||'
1572 and B.ATTRIBUTE_ID = '||l_curr_attr_id||'
1573 and B.VERSION_ID = C.VERSION_ID
1574 and C.ATTRIBUTE_ID = '||l_curr_attr_id||'
1575 and C.DEFAULT_VERSION_FLAG = ''Y''
1576 and C.AW_SNAPSHOT_FLAG = ''N''
1577 and B.AW_SNAPSHOT_FLAG = ''N''
1578 and B.DIM_ATTRIBUTE_VARCHAR_MEMBER is null';
1579
1580 open l_cursor for l_command;
1581 fetch l_cursor into l_count;
1582 close l_cursor;
1583 if (l_count > 0) then
1584 REGISTER_ERROR('S', 'E', 'ZPB_BUSAREA_VAL_CURR_ATTR_MISS');
1585 end if;
1586 end if;
1587 end if;
1588
1589 --
1590 -- Verify no org is missing the currency attribute
1591 -- if the BA is currency enabled
1592 --
1593 if ((l_currency = 'Y') and
1594 (each.LOGICAL_DIM_ID = l_org_logical_dim_id) and
1595 (each.DIMENSION_ID = l_org_dim_id)) then
1596
1597 l_command := 'select decode(count(*), 0, 0, 1)';
1598 if (l_no_hierarchies = 'Y') then
1599 if (l_vset_required = 'Y') then
1600 l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.MEMBER_ID, instr(A.MEMBER_ID, ''_'')+1))) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1601 else
1602 l_command := l_command || ' from (SELECT DISTINCT(to_number(A.MEMBER_ID)) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1603 end if;
1604 l_command := l_command || ' '||l_org_dim_id||', '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) A,';
1605 else
1606 if (l_vset_required = 'Y') then
1607 l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1))) ';
1608 else
1609 l_command := l_command || ' from (SELECT DISTINCT(to_number(A.CHILD_ID))';
1610 end if;
1611 l_command := l_command || ' from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS(';
1612 l_command := l_command || ' '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) A,';
1613 end if;
1614 l_command := l_command ||
1615 ' FEM_DIM_ATTR_GRPS B,' ||
1616 ' '||l_dim_table||' C ';
1617 if (l_no_hierarchies = 'N') then
1618 l_command := l_command || ', FEM_HIERARCHIES D';
1619 end if;
1620 l_command := l_command || ' where ';
1621 if (l_no_hierarchies = 'N')
1622 then
1623 if (l_vset_required = 'Y') then
1624 l_command := l_command ||
1625 'to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1)) = C.'||l_col||' AND ';
1626 else
1627 l_command := l_command || 'to_number(A.CHILD_ID) = C.'||l_col||' AND ';
1628 end if;
1629 end if;
1630 l_command := l_command ||
1631 ' B.DIMENSION_GROUP_ID = C.DIMENSION_GROUP_ID ';
1632 if (l_no_hierarchies = 'N') then
1633 l_command := l_command ||
1634 ' AND D.HIERARCHY_OBJ_ID = A.HIERARCHY_ID ' ||
1635 ' AND D.GROUP_SEQUENCE_ENFORCED_CODE <> ''NO_GROUPS''';
1636 end if;
1637 l_command := l_command ||
1638 ' MINUS select B.'||l_col||
1639 ' from FEM_DIM_ATTRIBUTES_B A,' ||
1640 ' '||l_dim_table||' B,' ||
1641 ' '||l_attr_table||' C,' ||
1642 ' FEM_DIM_ATTR_GRPS D,' ||
1643 ' FEM_DIM_ATTR_VERSIONS_B E,';
1644 if (l_no_hierarchies = 'Y') then
1645 l_command := l_command ||
1646 ' table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1647 l_command := l_command || ' '||l_org_dim_id||', '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) F';
1648 else
1649 l_command := l_command ||
1650 ' table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS(';
1651 l_command := l_command || ' '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) F';
1652 l_command := l_command || ', FEM_HIERARCHIES G ';
1653 end if;
1654
1655 l_command := l_command ||
1656 ' where A.ATTRIBUTE_VARCHAR_LABEL = ''ZPB_ORG_CURRENCY'' ' ||
1657 ' AND C.ATTRIBUTE_ID = A.ATTRIBUTE_ID' ||
1658 ' AND B.'||l_col||' = C.'||l_col;
1659 if (l_no_hierarchies = 'N')
1660 then
1661 if (l_vset_required = 'Y') then
1662 l_command := l_command ||
1663 ' AND B.'||l_col||' = to_number(substr(F.CHILD_ID, instr(F.CHILD_ID, ''_'')+1)) ';
1664 else
1665 l_command := l_command ||
1666 ' AND B.'||l_col||' = to_number(F.CHILD_ID) ';
1667 end if;
1668 end if;
1669 l_command := l_command ||
1670 ' AND A.ATTRIBUTE_ID = D.ATTRIBUTE_ID ' ||
1671 ' AND C.ATTRIBUTE_ID = D.ATTRIBUTE_ID ' ||
1672 ' AND E.ATTRIBUTE_ID = A.ATTRIBUTE_ID ' ||
1673 ' AND E.DEFAULT_VERSION_FLAG = ''Y'' ' ||
1674 ' AND E.AW_SNAPSHOT_FLAG = ''N'' ';
1675 if (l_no_hierarchies = 'N') then
1676 l_command := l_command ||
1677 ' AND F.HIERARCHY_ID = G.HIERARCHY_OBJ_ID ' ||
1678 ' AND G.GROUP_SEQUENCE_ENFORCED_CODE <> ''NO_GROUPS'' ';
1679 end if;
1680 l_command := l_command || ') ';
1681
1682 open l_cursor for l_command;
1683 fetch l_cursor into l_count;
1684 close l_cursor;
1685 if (l_count > 0) then
1686
1687 select DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
1688 A.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME
1689 into l_dim_name
1690 from FEM_DIMENSIONS_VL A,
1691 ZPB_BUSAREA_DIMENSIONS B,
1692 FEM_FUNC_DIM_SETS_VL FDR
1693 where B.DIMENSION_ID = l_org_dim_id
1694 and B.LOGICAL_DIM_ID = l_org_logical_dim_id
1695 and B.VERSION_ID = p_version_id
1696 and A.DIMENSION_ID = B.DIMENSION_ID
1697 and FDR.FUNC_DIM_SET_ID (+) = B.FUNC_DIM_SET_ID ;
1698
1699 select ATTRIBUTE_NAME
1700 into l_attr_name
1701 from FEM_DIM_ATTRIBUTES_VL
1702 where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1703 and DIMENSION_ID = l_org_dim_id;
1704
1705 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_ORG_NOCURRATTR',
1706 'ATTRNAME', l_attr_name, 'N', 'DIMNAME', l_dim_name, 'N');
1707 else
1708 l_command := 'select decode(count(*), 0, 0, 1)';
1709 if (l_no_hierarchies = 'Y') then
1710 if (l_vset_required = 'Y') then
1711 l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.MEMBER_ID, instr(A.MEMBER_ID, ''_'')+1))) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1712 else
1713 l_command := l_command || ' from (SELECT DISTINCT(to_number(A.MEMBER_ID)) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1714 end if;
1715 l_command := l_command || ' '||l_org_dim_id||', '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) A,';
1716 else
1717 if (l_vset_required = 'Y') then
1718 l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1)))';
1719 else
1720 l_command := l_command || ' from (SELECT DISTINCT(to_number(A.CHILD_ID))';
1721 end if;
1722 l_command := l_command || ' from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS(';
1723 l_command := l_command || ' '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) A,';
1724 end if;
1725 l_command := l_command ||
1726 ' '||l_dim_table||' C ';
1727 if (l_no_hierarchies = 'N') then
1728 l_command := l_command || ', FEM_HIERARCHIES D';
1729 l_command := l_command || ' where ';
1730 if (l_vset_required = 'Y') then
1731 l_command := l_command ||
1732 'to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1)) = C.'||l_col||' AND ';
1733 else
1734 l_command := l_command || 'to_number(A.CHILD_ID) = C.'||l_col||' AND ';
1735 end if;
1736 l_command := l_command ||
1737 ' D.HIERARCHY_OBJ_ID = A.HIERARCHY_ID ' ||
1738 ' AND D.GROUP_SEQUENCE_ENFORCED_CODE = ''NO_GROUPS''';
1739 end if;
1740 l_command := l_command ||
1741 ' MINUS select B.'||l_col||
1742 ' from FEM_DIM_ATTRIBUTES_B A,' ||
1743 ' '||l_dim_table||' B,' ||
1744 ' '||l_attr_table||' C,' ||
1745 ' FEM_DIM_ATTR_VERSIONS_B E,';
1746 if (l_no_hierarchies = 'Y') then
1747 l_command := l_command ||
1748 ' table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
1749 l_command := l_command || ' '||l_org_dim_id||', '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) F';
1750 else
1751 l_command := l_command ||
1752 ' table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS(';
1753 l_command := l_command || ' '||l_org_logical_dim_id||', '||l_ba_id||', ''P'')) F';
1754
1755 l_command := l_command || ', FEM_HIERARCHIES G ';
1756 end if;
1757
1758 l_command := l_command ||
1759 ' where A.ATTRIBUTE_VARCHAR_LABEL = ''ZPB_ORG_CURRENCY'' ' ||
1760 ' AND C.ATTRIBUTE_ID = A.ATTRIBUTE_ID' ||
1761 ' AND B.'||l_col||' = C.'||l_col;
1762 if (l_no_hierarchies = 'N')
1763 then
1764 if (l_vset_required = 'Y') then
1765 l_command := l_command ||
1766 ' AND B.'||l_col||' = to_number(substr(F.CHILD_ID, instr(F.CHILD_ID, ''_'')+1)) ';
1767 else
1768 l_command := l_command ||
1769 ' AND B.'||l_col||' = to_number(F.CHILD_ID) ';
1770 end if;
1771 end if;
1772 l_command := l_command ||
1773 ' AND E.ATTRIBUTE_ID = A.ATTRIBUTE_ID ' ||
1774 ' AND E.DEFAULT_VERSION_FLAG = ''Y'' ' ||
1775 ' AND E.AW_SNAPSHOT_FLAG = ''N'' ';
1776 if (l_no_hierarchies = 'N') then
1777 l_command := l_command ||
1778 ' AND F.HIERARCHY_ID = G.HIERARCHY_OBJ_ID ' ||
1779 ' AND G.GROUP_SEQUENCE_ENFORCED_CODE = ''NO_GROUPS'' ';
1780 end if;
1781 l_command := l_command || ') ';
1782 open l_cursor for l_command;
1783 fetch l_cursor into l_count;
1784 close l_cursor;
1785 if (l_count > 0) then
1786
1787 select DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
1788 A.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME
1789 into l_dim_name
1790 from FEM_DIMENSIONS_VL A,
1791 ZPB_BUSAREA_DIMENSIONS B,
1792 FEM_FUNC_DIM_SETS_VL FDR
1793 where B.DIMENSION_ID = l_org_dim_id
1794 and B.LOGICAL_DIM_ID = l_org_logical_dim_id
1795 and B.VERSION_ID = p_version_id
1796 and A.DIMENSION_ID = B.DIMENSION_ID
1797 and FDR.FUNC_DIM_SET_ID (+) = B.FUNC_DIM_SET_ID ;
1798
1799 select ATTRIBUTE_NAME
1800 into l_attr_name
1801 from FEM_DIM_ATTRIBUTES_VL
1802 where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
1803 and DIMENSION_ID = l_org_dim_id;
1804
1805 REGISTER_ERROR('S', 'W', 'ZPB_BUSAREA_VAL_ORG_NOCURRATTR',
1806 'ATTRNAME', l_attr_name, 'N', 'DIMNAME', l_dim_name, 'N');
1807 end if;
1808 end if;
1809 end if;
1810
1811 end loop;
1812
1813
1814 ZPB_LOG.WRITE (l_proc_name||'.end', 'End BA validation');
1815 end VAL_DEFINITION;
1816
1817 -------------------------------------------------------------------------
1818 -- FIND_IN_REPOS - Finds objects in the repository dependent on the
1819 -- given object
1820 --
1821 -- p_init_fix : Flag to confirm whether MD fixing should be done fixed or not
1822 -------------------------------------------------------------------------
1823 PROCEDURE FIND_IN_REPOS (p_business_area IN NUMBER,
1824 p_version_id IN NUMBER,
1825 p_object_id IN VARCHAR2,
1826 p_object_type IN VARCHAR2, -- Not used
1827 p_object_name IN VARCHAR2, -- Not used
1828 p_init_fix IN VARCHAR2)
1829 IS
1830 l_str VARCHAR2(300);
1831 l_str2 VARCHAR2(256);
1832 l_num NUMBER;
1833 l_taskID NUMBER;
1834 l_user FND_USER.USER_NAME%type;
1835 l_user_id FND_USER.USER_ID%type;
1836 l_xml BISM_OBJECTS.XML%type;
1837 l_line_dim VARCHAR2(150);
1838 l_folder BISM_OBJECTS.FOLDER_ID%type;
1839 l_queryPath ZPB_STATUS_SQL.QUERY_PATH%type;
1840 l_queryErrorType varchar2(1);
1841 l_dim ZPB_CYCLE_MODEL_DIMENSIONS.DIMENSION_NAME%type;
1842 l_dimName ZPB_CYCLE_MODEL_DIMENSIONS.DIMENSION_NAME%type;
1843 l_acID ZPB_ANALYSIS_CYCLES.ANALYSIS_CYCLE_ID%type;
1844 l_bpName ZPB_ANALYSIS_CYCLES.NAME%type;
1845 l_memberID ZPB_LINE_DIMENSIONALITY.MEMBER%type;
1846 l_memberName VARCHAR2(255);
1847 l_secFoldPath ZPB_STATUS_SQL.QUERY_PATH%type;
1848 l_statusSqlId ZPB_STATUS_SQL.STATUS_SQL_ID%type;
1849 l_command VARCHAR2(2000);
1850 l_cursor epb_cur_type;
1851
1852 CURSOR l_objs(p_search_str VARCHAR2,
1853 l_folder BISM_OBJECTS.FOLDER_ID%type) is
1854 SELECT distinct A.OBJECT_ID,
1855 A.OBJECT_NAME,
1856 B.OBJECT_TYPE_NAME,
1857 C.OBJECT_NAME FOLDER_NAME,
1858 A.FOLDER_ID
1859 FROM BISM_OBJECTS A,
1860 BISM_OBJECT_TYPES B,
1861 BISM_OBJECTS C,
1862 (select C.CONTAINER_ID
1863 from BISM_OBJECTS C,
1864 BISM_OBJECT_TYPES D
1865 where C.OBJECT_TYPE_ID = D.OBJECT_TYPE_ID
1866 and D.OBJECT_TYPE_NAME = 'Selection'
1867 and C.XML like p_search_str
1868 and C.FOLDER_ID IN
1869 (select OBJECT_ID
1870 from BISM_OBJECTS
1871 where OBJECT_TYPE_ID = 100
1872 start with OBJECT_ID = l_folder
1873 connect by FOLDER_ID = prior OBJECT_ID)) D
1874 WHERE A.OBJECT_TYPE_ID = B.OBJECT_TYPE_ID
1875 and A.FOLDER_ID = C.OBJECT_ID
1876 and A.OBJECT_ID = D.CONTAINER_ID
1877 and B.OBJECT_TYPE_NAME <> 'Selection';
1878
1879 cursor l_dc_tg_objs(p_search_str VARCHAR2, p_baID NUMBER) is
1880 select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
1881 into l_str2, l_str, l_acID, l_queryPath
1882 from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
1883 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1884 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1885 AND B.BUSINESS_AREA_ID = p_baID
1886 AND TARGET_OBJ_NAME = p_search_str;
1887
1888 cursor l_dc_data_objs(p_search_str VARCHAR2, p_baID NUMBER) is
1889 select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
1890 into l_str2, l_str, l_acID, l_queryPath
1891 from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
1892 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1893 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1894 AND B.BUSINESS_AREA_ID = p_baID
1895 AND DATAENTRY_OBJ_NAME = p_search_str;
1896
1897 cursor l_input_sel(p_search_str VARCHAR2, p_baID NUMBER) is
1898 select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, B.NAME
1899 into l_str, l_queryPath, l_acID, l_bpName
1900 from ZPB_SOLVE_INPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
1901 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1902 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1903 AND B.BUSINESS_AREA_ID = p_baID
1904 AND SELECTION_NAME = p_search_str;
1905
1906 cursor l_output_sel(p_search_str VARCHAR2, p_baID NUMBER) is
1907 select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, B.NAME
1908 into l_str, l_queryPath, l_acID, l_bpName
1909 from ZPB_SOLVE_OUTPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
1910 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1911 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1912 AND B.BUSINESS_AREA_ID = p_baID
1913 AND SELECTION_NAME = p_search_str;
1914
1915 cursor l_init_source(p_search_str VARCHAR2, p_baID NUMBER) is
1916 select SOURCE_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
1917 into l_str, l_queryPath, l_memberID, l_acID
1918 from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
1919 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1920 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1921 AND B.BUSINESS_AREA_ID = p_baID
1922 AND SOURCE_QUERY_NAME = p_search_str;
1923
1924 cursor l_init_target(p_search_str VARCHAR2, p_baID NUMBER) is
1925 select TARGET_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
1926 into l_str, l_queryPath, l_memberID, l_acID
1927 from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
1928 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1929 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1930 AND B.BUSINESS_AREA_ID = p_baID
1931 AND TARGET_QUERY_NAME = p_search_str;
1932
1933 cursor l_sum_sel1(p_search_str VARCHAR2, p_baID NUMBER) is
1934 select SUM_SELECTION_NAME, SUM_SELECTION_PATH, DIMENSION_NAME, A.ANALYSIS_CYCLE_ID
1935 into l_str, l_queryPath, l_dim, l_acID
1936 from zpb_cycle_model_dimensions A, ZPB_ANALYSIS_CYCLES B
1937 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1938 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1939 AND B.BUSINESS_AREA_ID = p_baID
1940 AND SUM_SELECTION_NAME = p_search_str;
1941
1942 cursor l_sum_sel2(p_search_str VARCHAR2, p_baID NUMBER) is
1943 select SUM_SELECTION_NAME, SUM_SELECTION_PATH, MEMBER, DIMENSION, A.ANALYSIS_CYCLE_ID
1944 into l_str, l_queryPath, l_memberID, l_dim, l_acID
1945 from ZPB_LINE_DIMENSIONALITY A, ZPB_ANALYSIS_CYCLES B
1946 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1947 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
1948 AND B.BUSINESS_AREA_ID = p_baID
1949 AND SUM_SELECTION_NAME = p_search_str;
1950
1951 CURSOR l_get_status_sql_id(p_query_path VARCHAR2) IS
1952 SELECT status_sql_id
1953 FROM zpb_status_sql
1954 WHERE query_path = p_query_path;
1955
1956 cursor query_objects(p_object_name varchar2, p_folder_name varchar2) is
1957 select distinct A.NAME, A.ANALYSIS_CYCLE_ID, B.QUERY_OBJECT_PATH
1958 from ZPB_ANALYSIS_CYCLES A,
1959 ZPB_CYCLE_MODEL_DIMENSIONS B
1960 where B.QUERY_OBJECT_NAME = p_object_name
1961 and B.QUERY_OBJECT_PATH like '%'||p_folder_name
1962 and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
1963 and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
1964 and A.BUSINESS_AREA_ID = p_business_area
1965 and not exists
1966 (select B.ANALYSIS_CYCLE_ID
1967 from ZPB_ANALYSIS_CYCLE_INSTANCES B
1968 where B.INSTANCE_AC_ID = A.ANALYSIS_CYCLE_ID);
1969 begin
1970 BEGIN
1971 select BUSAREA.OBJECT_ID
1972 into l_folder
1973 from BISM_OBJECTS ORCL,
1974 BISM_OBJECTS APPS,
1975 BISM_OBJECTS ZPB,
1976 BISM_OBJECTS BUSAREA
1977 where ORCL.USER_VISIBLE = 'Y'
1978 and APPS.USER_VISIBLE = 'Y'
1979 and ZPB.USER_VISIBLE = 'Y'
1980 and BUSAREA.USER_VISIBLE = 'Y'
1981 and ORCL.OBJECT_NAME = 'oracle'
1982 and APPS.OBJECT_NAME = 'apps'
1983 and ZPB.OBJECT_NAME = 'zpb'
1984 and BUSAREA.OBJECT_NAME = 'BusArea'||p_business_area
1985 and ORCL.FOLDER_ID = HEXTORAW('31')
1986 and APPS.FOLDER_ID = ORCL.OBJECT_ID
1987 and ZPB.FOLDER_ID = APPS.OBJECT_ID
1988 and BUSAREA.FOLDER_ID = ZPB.OBJECT_ID;
1989 EXCEPTION
1990 WHEN no_data_found THEN
1991 null;
1992 END;
1993
1994 l_secFoldPath := G_BUS_AREA_PATH_PREFIX || p_business_area || G_SECURITY_ADMIN_FOLDER;
1995
1996 if (p_object_id = '%') then
1997 l_queryErrorType := 'R';
1998 else
1999 l_queryErrorType := 'F';
2000 end if;
2001
2002 for each in l_objs('%'||p_object_id||'%', l_folder) loop
2003 begin
2004 if (instr (each.object_name, 'MODEL_QUERY') > 0) then
2005 if (l_line_dim is null) then
2006 select NAME
2007 into l_line_dim
2008 from ZPB_BUSAREA_DIMENSIONS_VL
2009 where VERSION_ID = p_version_id
2010 and DIMENSION_ID = (select MIN(DIMENSION_ID)
2011 from ZPB_BUSAREA_DIMENSIONS
2012 where VERSION_ID = p_version_id
2013 and EPB_LINE_DIMENSION = 'Y');
2014 end if;
2015
2016 for each_query in query_objects(each.object_name,each.folder_name)
2017 loop
2018 l_queryPath := each_query.QUERY_OBJECT_PATH;
2019 l_acID := each_query.ANALYSIS_CYCLE_ID;
2020 l_str := each_query.NAME;
2021 DISABLE_BP(p_business_area , each.object_name, l_queryPath,
2022 l_queryErrorType, l_acID, p_init_fix);
2023 if(l_queryErrorType = 'F') then
2024 REGISTER_ERROR ('O', 'W', 'ZPB_BUSAREA_VAL_INV_MOD_QUERY',
2025 'LINEDIM', l_line_dim, 'N',
2026 'NAME', l_str, 'N');
2027 end if;
2028 end loop;
2029
2030 elsif (instr (each.object_name, 'LOAD_DATA') > 0 or
2031 instr (each.object_name, 'EXCEPTION_') > 0) then
2032 begin
2033 l_num := to_number(substr(each.folder_name, 3));
2034 select A.TASK_NAME, A.TASK_ID, A.ANALYSIS_CYCLE_ID,
2035 nvl (D.INSTANCE_DESCRIPTION, B.NAME) NAME
2036 into l_str2, l_taskID, l_acID, l_str
2037 from ZPB_ANALYSIS_CYCLE_TASKS A,
2038 ZPB_ANALYSIS_CYCLES B,
2039 ZPB_TASK_PARAMETERS C,
2040 ZPB_ANALYSIS_CYCLE_INSTANCES D
2041 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2042 and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2043 and B.BUSINESS_AREA_ID = p_business_area
2044 and A.ANALYSIS_CYCLE_ID = l_num
2045 and A.TASK_ID = C.TASK_ID
2046 and C.NAME = 'QUERY_OBJECT_NAME'
2047 and C.VALUE = each.object_name
2048 and A.ANALYSIS_CYCLE_ID = D.INSTANCE_AC_ID(+);
2049
2050 SELECT value
2051 INTO l_queryPath
2052 FROM ZPB_TASK_PARAMETERS
2053 WHERE name = 'QUERY_OBJECT_PATH'
2054 AND TASK_ID = l_taskID;
2055
2056 IF(l_queryErrorType = 'F') THEN
2057 IF (instr (each.object_name, 'LOAD_DATA') > 0) then
2058 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_BP_TASK',
2059 'TASK_NAME', l_str2, 'N',
2060 'TASK_TYPE', 'ZPB_TASK_NAME_LOAD_DATA_MSG', 'Y',
2061 'NAME', l_str, 'N');
2062 ELSE
2063 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_BP_TASK',
2064 'TASK_NAME', l_str2, 'N',
2065 'TASK_TYPE','ZPB_TASK_NAME_EXCEPT_CHECK_MSG','Y',
2066 'NAME', l_str, 'N');
2067 END IF;
2068 END IF;
2069 DISABLE_BP(p_business_area ,each.object_name, l_queryPath,
2070 l_queryErrorType, l_acID, p_init_fix);
2071 EXCEPTION
2072 WHEN no_data_found THEN
2073 null; -- Bug 4214272
2074 END;
2075
2076 ELSIF (instr (each.object_name, 'CD_SOURCE') > 0) THEN
2077 FOR each_init_source in l_init_source(each.object_name, p_business_area) loop
2078 DISABLE_BP(p_business_area ,each.object_name,
2079 each_init_source.QUERY_PATH, l_queryErrorType,
2080 each_init_source.ANALYSIS_CYCLE_ID, p_init_fix);
2081
2082 l_memberName := GET_LINE_MEMBER_DESC(each_init_source.MEMBER);
2083
2084 IF(l_queryErrorType = 'F') THEN
2085 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_INIT_QUERY',
2086 'LINE_ITEM', l_memberName, 'N');
2087 END IF;
2088 END LOOP;
2089
2090 ELSIF (instr (each.object_name, 'CD_TARGET') > 0) THEN
2091 FOR each_init_target in l_init_target(each.object_name, p_business_area) loop
2092 DISABLE_BP(p_business_area ,each.object_name,
2093 each_init_target.QUERY_PATH, l_queryErrorType,
2094 each_init_target.ANALYSIS_CYCLE_ID, p_init_fix);
2095
2096 l_memberName := GET_LINE_MEMBER_DESC(each_init_target.MEMBER);
2097
2098 IF(l_queryErrorType = 'F') THEN
2099 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_INIT_QUERY',
2100 'LINE_ITEM', l_memberName, 'N');
2101 END IF;
2102 END LOOP;
2103
2104 ELSIF (instr (each.object_name, 'TARGET') > 0) then
2105 FOR each_dc_obj in l_dc_tg_objs(each.object_name, p_business_area) loop
2106 IF (instr(each.object_name, 'GEN_TEMPL') > 0) then
2107 l_str := 'ZPB_GENERATE_TEMPL_TASK_TARGET';
2108 ELSE
2109 l_str := 'ZPB_TARGET_MASTER';
2110 END IF;
2111
2112 IF(l_queryErrorType = 'F') THEN
2113 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_QUERY',
2114 'QUERY', l_str, 'Y',
2115 'NAME', each_dc_obj.TEMPLATE_NAME, 'N');
2116 END IF;
2117
2118 DISABLE_BP(p_business_area ,each.object_name,
2119 each_dc_obj.TARGET_OBJ_PATH, l_queryErrorType,
2120 each_dc_obj.ANALYSIS_CYCLE_ID, p_init_fix);
2121 END LOOP;
2122
2123 ELSIF (instr (each.object_name, '_DATA_') > 0) then
2124 FOR each_dc_obj in l_dc_data_objs(each.object_name, p_business_area) loop
2125 IF (instr(each.object_name, 'GEN_TEMPL') > 0) then
2126 l_str := 'ZPB_GENERATE_TEMPL_TASK_DATA';
2127 ELSE
2128 l_str := 'ZPB_DATA_MASTER';
2129 END IF;
2130
2131 IF(l_queryErrorType = 'F') THEN
2132 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_QUERY',
2133 'QUERY', l_str, 'Y',
2134 'NAME', each_dc_obj.TEMPLATE_NAME, 'N');
2135 END IF;
2136
2137 DISABLE_BP(p_business_area ,each.object_name,
2138 each_dc_obj.TARGET_OBJ_PATH, l_queryErrorType,
2139 each_dc_obj.ANALYSIS_CYCLE_ID,p_init_fix);
2140 END LOOP;
2141
2142 ELSIF (instr (each.object_name, 'ReadAccess') > 0) THEN
2143 l_str := substr(each.object_name, 1,
2144 instr(each.object_name, 'ReadAccess')+9);
2145 SELECT xml
2146 INTO l_xml
2147 FROM BISM_OBJECTS
2148 WHERE OBJECT_NAME = l_str
2149 AND FOLDER_ID = each.FOLDER_ID;
2150
2151 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2152
2153 SELECT USER_NAME
2154 INTO l_user
2155 FROM FND_USER
2156 WHERE USER_ID = l_user_id;
2157
2158 l_num := instr(l_xml, 'Description="')+13;
2159 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2160
2161 IF(l_queryErrorType = 'F') THEN
2162 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2163 'OBJ_TYPE', 'ZPB_MGR_READACCESS_DESCRIPTION', 'Y',
2164 'NAME', l_str, 'N',
2165 'USER', l_user, 'N');
2166 END IF;
2167
2168 for lock_user in
2169 l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2170 loop
2171
2172 LOCK_OUT_USER(p_business_area,
2173 l_user_id,
2174 each.object_name,
2175 l_secFoldPath,
2176 G_READ_RULE,
2177 l_queryErrorType,
2178 p_init_fix,
2179 lock_user.status_sql_id);
2180 end loop;
2181
2182 ELSIF (instr (each.object_name, 'WriteAccess') > 0) THEN
2183 l_str := substr(each.object_name, 1,
2184 instr(each.object_name, 'WriteAccess')+10);
2185 SELECT xml
2186 INTO l_xml
2187 FROM BISM_OBJECTS
2188 WHERE OBJECT_NAME = l_str
2189 AND FOLDER_ID = each.FOLDER_ID;
2190
2191 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2192
2193 SELECT USER_NAME
2194 INTO l_user
2195 FROM FND_USER
2196 WHERE USER_ID = l_user_id;
2197
2198
2199 l_num := instr(l_xml, 'Description="')+13;
2200 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2201
2202 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2203 'OBJ_TYPE', 'ZPB_MGR_WRITEACC_DESCRIPTION', 'Y',
2204 'NAME', l_str, 'N',
2205 'USER', l_user, 'N');
2206
2207 for lock_user
2208 in l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2209 loop
2210
2211 LOCK_OUT_USER(p_business_area,
2212 l_user_id,
2213 each.object_name,
2214 l_secFoldPath,
2215 G_WRITE_RULE,
2216 l_queryErrorType,
2217 p_init_fix,
2218 lock_user.status_sql_id);
2219 end loop;
2220
2221 ELSIF (instr (each.object_name, 'Ownership') > 0) THEN
2222 l_str := substr(each.object_name, 1,
2223 instr(each.object_name, 'Ownership')+8);
2224 SELECT xml
2225 INTO l_xml
2226 FROM BISM_OBJECTS
2227 WHERE OBJECT_NAME = l_str
2228 AND FOLDER_ID = each.FOLDER_ID;
2229
2230 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2231
2232 SELECT USER_NAME
2233 INTO l_user
2234 FROM FND_USER
2235 WHERE USER_ID = l_user_id;
2236
2237 l_num := instr(l_xml, 'Description="')+13;
2238 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2239
2240 -- Bug#5052923: Fixed message name.
2241 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2242 'OBJ_TYPE', 'ZPB_MGR_WRITEACC_DESCRIPTION', 'Y',
2243 'NAME', l_str, 'N',
2244 'USER', l_user, 'N');
2245
2246 for lock_user in
2247 l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2248 loop
2249
2250 LOCK_OUT_USER(p_business_area,
2251 l_user_id,
2252 each.object_name,
2253 l_secFoldPath,
2254 G_OWNER_RULE,
2255 l_queryErrorType,
2256 p_init_fix,
2257 lock_user.status_sql_id);
2258 end loop;
2259
2260 ELSIF (instr (each.object_name, 'INPUT') > 0) THEN
2261 FOR each_input_sel in l_input_sel(each.object_name, p_business_area) loop
2262 IF(l_queryErrorType = 'F') THEN
2263 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SOLVE',
2264 'NAME', each_input_sel.NAME, 'N');
2265 END IF;
2266 DISABLE_BP(p_business_area ,each.object_name,
2267 each_input_sel.SELECTION_PATH, l_queryErrorType,
2268 each_input_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2269 END LOOP;
2270
2271 ELSIF (instr (each.object_name, 'OUTPUT') > 0) THEN
2272 FOR each_output_sel in l_output_sel(each.object_name, p_business_area) loop
2273 IF(l_queryErrorType = 'F') THEN
2274 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SOLVE',
2275 'NAME', each_output_sel.NAME, 'N');
2276 END IF;
2277 DISABLE_BP(p_business_area ,each.object_name,
2278 each_output_sel.SELECTION_PATH, l_queryErrorType,
2279 each_output_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2280 END LOOP;
2281
2282 ELSIF (instr (each.object_name, 'SUM') > 0) THEN
2283 IF (instr(each.object_name, 'SUM_') > 0) THEN
2284 FOR each_sum_sel in l_sum_sel2(each.object_name, p_business_area) LOOP
2285 DISABLE_BP(p_business_area ,each.object_name,
2286 each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
2287 each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2288
2289 SELECT name INTO l_dimName FROM zpb_dimensions_vl
2290 WHERE bus_area_id = p_business_area
2291 AND aw_name = each_sum_sel.DIMENSION;
2292
2293 l_memberName := GET_LINE_MEMBER_DESC(each_sum_sel.MEMBER);
2294 IF(l_queryErrorType = 'F') THEN
2295 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_SUM_MEM_QUERY',
2296 'DIM_NAME', l_dimName, 'N',
2297 'LINE_ITEM', l_memberName, 'N');
2298 END IF;
2299 END LOOP;
2300 ELSE
2301 FOR each_sum_sel in l_sum_sel1(each.object_name, p_business_area) LOOP
2302 SELECT name INTO l_dimName FROM zpb_dimensions_vl
2303 WHERE bus_area_id = p_business_area
2304 AND aw_name = each_sum_sel.DIMENSION_NAME;
2305
2306 DISABLE_BP(p_business_area ,each.object_name,
2307 each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
2308 each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2309
2310 IF(l_queryErrorType = 'F') THEN
2311 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_REM_DIM_QUERY',
2312 'DIM_NAME', l_dimName, 'N');
2313 END IF;
2314
2315 END LOOP;
2316 END IF;
2317 END IF;
2318 EXCEPTION
2319 WHEN no_data_found THEN
2320 null;
2321 END;
2322 END LOOP;
2323 END FIND_IN_REPOS;
2324
2325
2326 -------------------------------------------------------------------------
2327 -- FIND_DEF_HIER_IN_REPOS - Finds objects in the repository dependent on the
2328 -- DEFAULT HIERARCHY
2329 -- Here we fix all the dependent queries but mark only those whose dimension
2330 -- is same as removed default hier's dimension
2331 --
2332 -- p_init_fix : Flag to confirm whether MD fixing should be done fixed or not
2333 -------------------------------------------------------------------------
2334 PROCEDURE FIND_DEF_HIER_IN_REPOS (p_business_area IN NUMBER,
2335 p_version_id IN NUMBER,
2336 p_object_id IN VARCHAR2,
2337 p_object_type IN VARCHAR2, -- Not used
2338 p_object_name IN VARCHAR2, -- Not used
2339 p_init_fix IN VARCHAR2)
2340 IS
2341 l_str VARCHAR2(300);
2342 l_str2 VARCHAR2(256);
2343 l_num NUMBER;
2344 l_taskID NUMBER;
2345 l_user FND_USER.USER_NAME%type;
2346 l_user_id FND_USER.USER_ID%type;
2347 l_xml BISM_OBJECTS.XML%type;
2348 l_line_dim VARCHAR2(150);
2349 l_line_dimID VARCHAR2(30);
2350 l_folder BISM_OBJECTS.FOLDER_ID%type;
2351 l_queryPath ZPB_STATUS_SQL.QUERY_PATH%type;
2352 l_queryErrorType varchar2(1);
2353 l_dim ZPB_CYCLE_MODEL_DIMENSIONS.DIMENSION_NAME%type;
2354 l_dimName ZPB_CYCLE_MODEL_DIMENSIONS.DIMENSION_NAME%type;
2355 l_acID ZPB_ANALYSIS_CYCLES.ANALYSIS_CYCLE_ID%type;
2356 l_bpName ZPB_ANALYSIS_CYCLES.NAME%type;
2357 l_memberID ZPB_LINE_DIMENSIONALITY.MEMBER%type;
2358 l_memberName VARCHAR2(255);
2359 l_rem_def_hier_dim VARCHAR2(30);
2360 l_current_dim VARCHAR2(30);
2361 l_secFoldPath ZPB_STATUS_SQL.QUERY_PATH%type;
2362 l_statusSqlId ZPB_STATUS_SQL.STATUS_SQL_ID%type;
2363 l_command VARCHAR2(1000);
2364 l_cursor epb_cur_type;
2365
2366 CURSOR l_objs(p_search_str VARCHAR2,
2367 l_folder BISM_OBJECTS.FOLDER_ID%type) is
2368 SELECT distinct A.OBJECT_ID,
2369 A.OBJECT_NAME,
2370 B.OBJECT_TYPE_NAME,
2371 C.OBJECT_NAME FOLDER_NAME,
2372 A.FOLDER_ID
2373 FROM BISM_OBJECTS A,
2374 BISM_OBJECT_TYPES B,
2375 BISM_OBJECTS C,
2376 (select C.CONTAINER_ID
2377 from BISM_OBJECTS C,
2378 BISM_OBJECT_TYPES D
2379 where C.OBJECT_TYPE_ID = D.OBJECT_TYPE_ID
2380 and D.OBJECT_TYPE_NAME = 'Selection'
2381 and C.XML like p_search_str
2382 and C.FOLDER_ID IN
2383 (select OBJECT_ID
2384 from BISM_OBJECTS
2385 where OBJECT_TYPE_ID = 100
2386 start with OBJECT_ID = l_folder
2387 connect by FOLDER_ID = prior OBJECT_ID)) D
2388 WHERE A.OBJECT_TYPE_ID = B.OBJECT_TYPE_ID
2389 and A.FOLDER_ID = C.OBJECT_ID
2390 and A.OBJECT_ID = D.CONTAINER_ID
2391 and B.OBJECT_TYPE_NAME <> 'Selection';
2392
2393 cursor l_dc_tg_objs(p_search_str VARCHAR2, p_baID NUMBER) is
2394 select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
2395 into l_str2, l_str, l_acID, l_queryPath
2396 from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
2397 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2398 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2399 AND B.BUSINESS_AREA_ID = p_baID
2400 AND TARGET_OBJ_NAME = p_search_str;
2401
2402 cursor l_dc_data_objs(p_search_str VARCHAR2, p_baID NUMBER) is
2403 select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
2404 into l_str2, l_str, l_acID, l_queryPath
2405 from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
2406 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2407 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2408 AND B.BUSINESS_AREA_ID = p_baID
2409 AND DATAENTRY_OBJ_NAME = p_search_str;
2410
2411 cursor l_input_sel(p_search_str VARCHAR2, p_baID NUMBER) is
2412 select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, DIMENSION, B.NAME
2413 into l_str, l_queryPath, l_acID, l_current_dim, l_bpName
2414 from ZPB_SOLVE_INPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
2415 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2416 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2417 AND B.BUSINESS_AREA_ID = p_baID
2418 AND SELECTION_NAME = p_search_str;
2419
2420 cursor l_output_sel(p_search_str VARCHAR2, p_baID NUMBER) is
2421 select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, DIMENSION, B.NAME
2422 into l_str, l_queryPath, l_acID, l_current_dim, l_bpName
2423 from ZPB_SOLVE_OUTPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
2424 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2425 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2426 AND B.BUSINESS_AREA_ID = p_baID
2427 AND SELECTION_NAME = p_search_str;
2428
2429 cursor l_init_source(p_search_str VARCHAR2, p_baID NUMBER) is
2430 select SOURCE_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
2431 into l_str, l_queryPath, l_memberID, l_acID
2432 from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
2433 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2434 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2435 AND B.BUSINESS_AREA_ID = p_baID
2436 AND SOURCE_QUERY_NAME = p_search_str;
2437
2438 cursor l_init_target(p_search_str VARCHAR2, p_baID NUMBER) is
2439 select TARGET_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
2440 into l_str, l_queryPath, l_memberID, l_acID
2441 from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
2442 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2443 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2444 AND B.BUSINESS_AREA_ID = p_baID
2445 AND TARGET_QUERY_NAME = p_search_str;
2446
2447 cursor l_sum_sel1(p_search_str VARCHAR2, p_baID NUMBER) is
2448 select SUM_SELECTION_NAME, SUM_SELECTION_PATH, DIMENSION_NAME, A.ANALYSIS_CYCLE_ID
2449 into l_str, l_queryPath, l_dim, l_acID
2450 from zpb_cycle_model_dimensions A, ZPB_ANALYSIS_CYCLES B
2451 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2452 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2453 AND B.BUSINESS_AREA_ID = p_baID
2454 AND SUM_SELECTION_NAME = p_search_str;
2455
2456 cursor l_sum_sel2(p_search_str VARCHAR2, p_baID NUMBER) is
2457 select SUM_SELECTION_NAME, SUM_SELECTION_PATH, MEMBER, DIMENSION, A.ANALYSIS_CYCLE_ID
2458 into l_str, l_queryPath, l_memberID, l_dim, l_acID
2459 from ZPB_LINE_DIMENSIONALITY A, ZPB_ANALYSIS_CYCLES B
2460 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2461 AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2462 AND B.BUSINESS_AREA_ID = p_baID
2463 AND SUM_SELECTION_NAME = p_search_str;
2464
2465 cursor l_source_dims(p_memberID VARCHAR2, p_acId NUMBER) is
2466 SELECT DIM SOURCE_DIMENSION
2467 INTO l_current_dim
2468 FROM ZPB_COPY_DIM_MEMBERS
2469 WHERE LINE_MEMBER_ID = p_memberID
2470 AND analysis_cycle_id = p_acId
2471 AND SOURCE_NUM_MEMBERS IS NOT NULL;
2472
2473 cursor l_target_dims(p_memberID VARCHAR2, p_acId NUMBER) is
2474 SELECT DIM TARGET_DIM
2475 INTO l_current_dim
2476 FROM ZPB_COPY_DIM_MEMBERS
2477 WHERE LINE_MEMBER_ID = p_memberID
2478 AND ANALYSIS_CYCLE_ID = p_acId
2479 AND TARGET_NUM_MEMBERS IS NOT NULL;
2480
2481 CURSOR l_get_status_sql_id(p_query_path VARCHAR2) IS
2482 SELECT status_sql_id
2483 FROM zpb_status_sql
2484 WHERE query_path = p_query_path;
2485
2486 cursor query_objects(p_object_name varchar2, p_folder_name varchar2) is
2487 select distinct A.NAME, A.ANALYSIS_CYCLE_ID, B.QUERY_OBJECT_PATH
2488 from ZPB_ANALYSIS_CYCLES A,
2489 ZPB_CYCLE_MODEL_DIMENSIONS B
2490 where B.QUERY_OBJECT_NAME = p_object_name
2491 and B.QUERY_OBJECT_PATH like '%'||p_folder_name
2492 and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2493 and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
2494 and A.BUSINESS_AREA_ID = p_business_area
2495 and not exists
2496 (select B.ANALYSIS_CYCLE_ID
2497 from ZPB_ANALYSIS_CYCLE_INSTANCES B
2498 where B.INSTANCE_AC_ID = A.ANALYSIS_CYCLE_ID);
2499
2500 begin
2501 BEGIN
2502 select BUSAREA.OBJECT_ID
2503 into l_folder
2504 from BISM_OBJECTS ORCL,
2505 BISM_OBJECTS APPS,
2506 BISM_OBJECTS ZPB,
2507 BISM_OBJECTS BUSAREA
2508 where ORCL.USER_VISIBLE = 'Y'
2509 and APPS.USER_VISIBLE = 'Y'
2510 and ZPB.USER_VISIBLE = 'Y'
2511 and BUSAREA.USER_VISIBLE = 'Y'
2512 and ORCL.OBJECT_NAME = 'oracle'
2513 and APPS.OBJECT_NAME = 'apps'
2514 and ZPB.OBJECT_NAME = 'zpb'
2515 and BUSAREA.OBJECT_NAME = 'BusArea'||p_business_area
2516 and ORCL.FOLDER_ID = HEXTORAW('31')
2517 and APPS.FOLDER_ID = ORCL.OBJECT_ID
2518 and ZPB.FOLDER_ID = APPS.OBJECT_ID
2519 and BUSAREA.FOLDER_ID = ZPB.OBJECT_ID;
2520
2521 EXCEPTION
2522 WHEN no_data_found THEN
2523 null;
2524 END;
2525 --get the removed Default Hier's Dimension ID
2526 SELECT AW_NAME INTO l_rem_def_hier_dim FROM ZPB_DIMENSIONS_VL
2527 WHERE BUS_AREA_ID = p_business_area
2528 AND DEFAULT_HIER = SUBSTR(p_object_id, INSTR(p_object_id, '_', -1, 1) + 1);
2529
2530 --get the ID for Line Dimension
2531 SELECT AW_NAME INTO l_line_dimID FROM ZPB_DIMENSIONS_VL
2532 WHERE BUS_AREA_ID = p_business_area
2533 AND DIM_TYPE = 'LINE';
2534
2535 l_secFoldPath := G_BUS_AREA_PATH_PREFIX || p_business_area || G_SECURITY_ADMIN_FOLDER;
2536
2537 for each in l_objs('%'||p_object_id||'%', l_folder) loop
2538 begin
2539 if (instr (each.object_name, 'MODEL_QUERY') > 0) then
2540 if (l_line_dim is null) then
2541 select NAME
2542 into l_line_dim
2543 from ZPB_BUSAREA_DIMENSIONS_VL
2544 where VERSION_ID = p_version_id
2545 and DIMENSION_ID = (select MIN(DIMENSION_ID)
2546 from ZPB_BUSAREA_DIMENSIONS
2547 where VERSION_ID = p_version_id
2548 and EPB_LINE_DIMENSION = 'Y');
2549 end if;
2550 for each_query in query_objects(each.object_name,each.folder_name)
2551 loop
2552 l_queryPath := each_query.QUERY_OBJECT_PATH;
2553 l_acID := each_query.ANALYSIS_CYCLE_ID;
2554 l_str := each_query.NAME;
2555 if (l_rem_def_hier_dim = l_line_dimID) then
2556 l_queryErrorType := 'F';
2557 REGISTER_ERROR ('O', 'W', 'ZPB_BUSAREA_VAL_INV_MOD_QUERY',
2558 'LINEDIM', l_line_dim, 'N',
2559 'NAME', l_str, 'N');
2560 else
2561 l_queryErrorType := 'R';
2562 end if;
2563
2564 DISABLE_BP(p_business_area , each.object_name, l_queryPath,
2565 l_queryErrorType, l_acID, p_init_fix);
2566 end loop;
2567
2568 elsif (instr (each.object_name, 'LOAD_DATA') > 0 or
2569 instr (each.object_name, 'EXCEPTION_') > 0) then
2570 begin
2571 l_num := to_number(substr(each.folder_name, 3));
2572 select A.TASK_NAME, A.TASK_ID, A.ANALYSIS_CYCLE_ID,
2573 nvl (D.INSTANCE_DESCRIPTION, B.NAME) NAME
2574 into l_str2, l_taskID, l_acID, l_str
2575 from ZPB_ANALYSIS_CYCLE_TASKS A,
2576 ZPB_ANALYSIS_CYCLES B,
2577 ZPB_TASK_PARAMETERS C,
2578 ZPB_ANALYSIS_CYCLE_INSTANCES D
2579 where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
2580 and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
2581 and B.BUSINESS_AREA_ID = p_business_area
2582 and A.ANALYSIS_CYCLE_ID = l_num
2583 and A.TASK_ID = C.TASK_ID
2584 and C.NAME = 'QUERY_OBJECT_NAME'
2585 and C.VALUE = each.object_name
2586 and A.ANALYSIS_CYCLE_ID = D.INSTANCE_AC_ID(+);
2587
2588 SELECT value
2589 INTO l_queryPath
2590 FROM ZPB_TASK_PARAMETERS
2591 WHERE name = 'QUERY_OBJECT_PATH'
2592 AND TASK_ID = l_taskID;
2593
2594 IF (instr (each.object_name, 'LOAD_DATA') > 0) then
2595 if(l_rem_def_hier_dim = l_line_dimID) then
2596 l_queryErrorType := 'F';
2597 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_BP_TASK',
2598 'TASK_NAME', l_str2, 'N',
2599 'TASK_TYPE', 'ZPB_TASK_NAME_LOAD_DATA_MSG', 'Y',
2600 'NAME', l_str, 'N');
2601 else
2602 l_queryErrorType := 'R';
2603 end if;
2604
2605 ELSE
2606 SELECT value
2607 INTO l_current_dim
2608 FROM ZPB_TASK_PARAMETERS
2609 WHERE name = 'EXCEPTION_DIMENSION'
2610 AND TASK_ID = l_taskID;
2611 if(l_rem_def_hier_dim = l_current_dim) then
2612 l_queryErrorType := 'F';
2613 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_BP_TASK',
2614 'TASK_NAME', l_str2, 'N',
2615 'TASK_TYPE','ZPB_TASK_NAME_EXCEPT_CHECK_MSG','Y',
2616 'NAME', l_str, 'N');
2617 else
2618 l_queryErrorType := 'R';
2619 end if;
2620 END IF;
2621 DISABLE_BP(p_business_area , each.object_name, l_queryPath,
2622 l_queryErrorType, l_acID, p_init_fix);
2623 EXCEPTION
2624 WHEN no_data_found THEN
2625 null; -- Bug 4214272
2626 END;
2627
2628 ELSIF (instr (each.object_name, 'CD_SOURCE') > 0) THEN
2629 FOR each_init_source in l_init_source(each.object_name, p_business_area) loop
2630 l_memberName := GET_LINE_MEMBER_DESC(each_init_source.MEMBER);
2631 l_queryErrorType := 'R';
2632 FOR each_source_dims in
2633 l_source_dims(each_init_source.MEMBER, each_init_source.ANALYSIS_CYCLE_ID) LOOP
2634 if(each_source_dims.SOURCE_DIMENSION = l_rem_def_hier_dim) then
2635 l_queryErrorType := 'F';
2636 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_INIT_QUERY',
2637 'LINE_ITEM', l_memberName, 'N');
2638 exit;
2639 end if;
2640 END LOOP;
2641 DISABLE_BP(p_business_area ,each.object_name,
2642 each_init_source.QUERY_PATH, l_queryErrorType,
2643 each_init_source.ANALYSIS_CYCLE_ID, p_init_fix);
2644
2645 END LOOP;
2646
2647 ELSIF (instr (each.object_name, 'CD_TARGET') > 0) THEN
2648 FOR each_init_target in l_init_target(each.object_name, p_business_area) loop
2649 l_memberName := GET_LINE_MEMBER_DESC(each_init_target.MEMBER);
2650 l_queryErrorType := 'R';
2651 FOR each_target_dims in
2652 l_target_dims(each_init_target.MEMBER, each_init_target.ANALYSIS_CYCLE_ID) LOOP
2653 if(each_target_dims.TARGET_DIM = l_rem_def_hier_dim) then
2654 l_queryErrorType := 'F';
2655 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_INIT_QUERY',
2656 'LINE_ITEM', l_memberName, 'N');
2657 exit;
2658 end if;
2659 END LOOP;
2660 DISABLE_BP(p_business_area ,each.object_name,
2661 each_init_target.QUERY_PATH, l_queryErrorType,
2662 each_init_target.ANALYSIS_CYCLE_ID, p_init_fix);
2663 END LOOP;
2664
2665 ELSIF (instr (each.object_name, 'TARGET') > 0) then
2666 FOR each_dc_obj in l_dc_tg_objs(each.object_name, p_business_area) loop
2667 IF (instr(each.object_name, 'GEN_TEMPL') > 0) then
2668 l_str := 'ZPB_GENERATE_TEMPL_TASK_TARGET'; -- generate template task target query
2669 ELSE
2670 l_str := 'ZPB_TARGET_MASTER'; -- target master query
2671 END IF;
2672
2673 if(l_rem_def_hier_dim = l_line_dimID) then
2674 l_queryErrorType := 'F';
2675 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_QUERY',
2676 'QUERY', l_str, 'Y',
2677 'NAME', each_dc_obj.TEMPLATE_NAME, 'N');
2678 else
2679 l_queryErrorType := 'R';
2680 end if;
2681 DISABLE_BP(p_business_area ,each.object_name,
2682
2683 each_dc_obj.TARGET_OBJ_PATH, l_queryErrorType,
2684 each_dc_obj.ANALYSIS_CYCLE_ID, p_init_fix);
2685 END LOOP;
2686
2687 ELSIF (instr (each.object_name, '_DATA_') > 0) then
2688 FOR each_dc_obj in l_dc_data_objs(each.object_name, p_business_area) loop
2689 IF (instr(each.object_name, 'GEN_TEMPL') > 0) then
2690 l_str := 'ZPB_GENERATE_TEMPL_TASK_DATA'; -- generate template task data query
2691 ELSE
2692 l_str := 'ZPB_DATA_MASTER'; -- data master query
2693 END IF;
2694 if(l_rem_def_hier_dim = l_line_dimID) then
2695 l_queryErrorType := 'F';
2696 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_QUERY',
2697 'QUERY', l_str, 'Y',
2698 'NAME', each_dc_obj.TEMPLATE_NAME, 'N');
2699 else
2700 l_queryErrorType := 'R';
2701 end if;
2702
2703 DISABLE_BP(p_business_area ,each.object_name,
2704 each_dc_obj.TARGET_OBJ_PATH, l_queryErrorType,
2705 each_dc_obj.ANALYSIS_CYCLE_ID,p_init_fix);
2706 END LOOP;
2707
2708 ELSIF (instr (each.object_name, 'ReadAccess') > 0) THEN
2709 l_str := substr(each.object_name, 1,
2710 instr(each.object_name, 'ReadAccess')+9);
2711 SELECT xml
2712 INTO l_xml
2713 FROM BISM_OBJECTS
2714 WHERE OBJECT_NAME = l_str
2715 AND FOLDER_ID = each.FOLDER_ID;
2716
2717 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2718
2719 SELECT USER_NAME
2720 INTO l_user
2721 FROM FND_USER
2722 WHERE USER_ID = l_user_id;
2723
2724 l_num := instr(l_xml, 'Description="')+13;
2725 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2726
2727 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2728 'OBJ_TYPE', 'ZPB_MGR_READACCESS_DESCRIPTION', 'Y',
2729 'NAME', l_str, 'N',
2730 'USER', l_user, 'N');
2731
2732 for lock_user in
2733 l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2734 loop
2735
2736 LOCK_OUT_USER(p_business_area,
2737 l_user_id,
2738 each.object_name,
2739 l_secFoldPath,
2740 G_READ_RULE,
2741 l_queryErrorType,
2742 p_init_fix,
2743 lock_user.status_sql_id);
2744 end loop;
2745
2746 ELSIF (instr (each.object_name, 'WriteAccess') > 0) THEN
2747 l_str := substr(each.object_name, 1,
2748 instr(each.object_name, 'WriteAccess')+10);
2749 SELECT xml
2750 INTO l_xml
2751 FROM BISM_OBJECTS
2752 WHERE OBJECT_NAME = l_str
2753 AND FOLDER_ID = each.FOLDER_ID;
2754
2755 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2756
2757 SELECT USER_NAME
2758 INTO l_user
2759 FROM FND_USER
2760 WHERE USER_ID = l_user_id;
2761
2762 l_num := instr(l_xml, 'Description="')+13;
2763 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2764
2765 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2766 'OBJ_TYPE', 'ZPB_MGR_WRITEACC_DESCRIPTION', 'Y',
2767 'NAME', l_str, 'N',
2768 'USER', l_user, 'N');
2769
2770 for lock_user in
2771 l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2772 loop
2773
2774 LOCK_OUT_USER(p_business_area,
2775 l_user_id,
2776 each.object_name,
2777 l_secFoldPath,
2778 G_WRITE_RULE,
2779 l_queryErrorType,
2780 p_init_fix,
2781 lock_user.status_sql_id);
2782 end loop;
2783
2784 ELSIF (instr (each.object_name, 'Ownership') > 0) THEN
2785 l_str := substr(each.object_name, 1,
2786 instr(each.object_name, 'Ownership')+8);
2787 SELECT xml
2788 INTO l_xml
2789 FROM BISM_OBJECTS
2790 WHERE OBJECT_NAME = l_str
2791 AND FOLDER_ID = each.FOLDER_ID;
2792
2793 l_user_id := to_number(substr(l_str, 1, instr(l_str, '_')-1));
2794
2795 SELECT USER_NAME
2796 INTO l_user
2797 FROM FND_USER
2798 WHERE USER_ID = l_user_id;
2799
2800 l_num := instr(l_xml, 'Description="')+13;
2801 l_str := substr(l_xml, l_num, instr(l_xml, '"', l_num)-l_num);
2802
2803 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SEC_RULE',
2804 'OBJ_TYPE', 'ZPB_MGR_WRITEACC_DESCRIPTION', 'Y',
2805 'NAME', l_str, 'N',
2806 'USER', l_user, 'N');
2807
2808
2809 for lock_user in
2810 l_get_status_sql_id(l_secFoldPath || '/' || each.object_name)
2811 loop
2812
2813 LOCK_OUT_USER(p_business_area,
2814 l_user_id,
2815 each.object_name,
2816 l_secFoldPath,
2817 G_OWNER_RULE,
2818 l_queryErrorType,
2819 p_init_fix,
2820 lock_user.status_sql_id);
2821 end loop;
2822
2823 ELSIF (instr (each.object_name, 'INPUT') > 0) THEN
2824 FOR each_input_sel in l_input_sel(each.object_name, p_business_area) loop
2825 IF(l_rem_def_hier_dim = each_input_sel.DIMENSION) then
2826 l_queryErrorType := 'F';
2827 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SOLVE',
2828 'NAME', each_input_sel.NAME, 'N');
2829 else
2830 l_queryErrorType := 'R';
2831 end if;
2832 DISABLE_BP(p_business_area ,each.object_name,
2833 each_input_sel.SELECTION_PATH, l_queryErrorType,
2834 each_input_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2835 END LOOP;
2836
2837 ELSIF (instr (each.object_name, 'OUTPUT') > 0) THEN
2838 FOR each_output_sel in l_output_sel(each.object_name, p_business_area) loop
2839 IF(l_rem_def_hier_dim = each_output_sel.DIMENSION) then
2840 l_queryErrorType := 'F';
2841 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_VAL_INV_SOLVE',
2842 'NAME', each_output_sel.NAME, 'N');
2843 else
2844 l_queryErrorType := 'R';
2845 end if;
2846 DISABLE_BP(p_business_area ,each.object_name,
2847 each_output_sel.SELECTION_PATH, l_queryErrorType,
2848 each_output_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2849 END LOOP;
2850
2851 ELSIF (instr (each.object_name, 'SUM') > 0) THEN
2852 IF (instr(each.object_name, 'SUM_') > 0) THEN
2853 FOR each_sum_sel in l_sum_sel2(each.object_name, p_business_area) LOOP
2854 SELECT name INTO l_dimName FROM zpb_dimensions_vl
2855 WHERE bus_area_id = p_business_area
2856 AND aw_name = each_sum_sel.DIMENSION;
2857
2858 l_memberName := GET_LINE_MEMBER_DESC(each_sum_sel.MEMBER);
2859 IF(l_rem_def_hier_dim = each_sum_sel.DIMENSION) then
2860 l_queryErrorType := 'F';
2861 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_SUM_MEM_QUERY',
2862 'DIM_NAME', l_dimName, 'N',
2863 'LINE_ITEM', l_memberName, 'N');
2864 else
2865 l_queryErrorType := 'R';
2866 end if;
2867 DISABLE_BP(p_business_area ,each.object_name,
2868 each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
2869 each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2870 END LOOP;
2871 ELSE
2872 FOR each_sum_sel in l_sum_sel1(each.object_name, p_business_area) LOOP
2873 SELECT name INTO l_dimName FROM zpb_dimensions_vl
2874 WHERE bus_area_id = p_business_area
2875 AND aw_name = each_sum_sel.DIMENSION_NAME;
2876 IF(l_rem_def_hier_dim = each_sum_sel.DIMENSION_NAME) then
2877 l_queryErrorType := 'F';
2878 REGISTER_ERROR('O', 'W', 'ZPB_BUSAREA_INV_REM_DIM_QUERY',
2879 'DIM_NAME', l_dimName, 'N');
2880 else
2881 l_queryErrorType := 'R';
2882 end if;
2883 DISABLE_BP(p_business_area ,each.object_name,
2884 each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
2885 each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
2886
2887 END LOOP;
2888 END IF;
2889 END IF;
2890 EXCEPTION
2891 WHEN no_data_found THEN
2892 null;
2893 END;
2894 END LOOP;
2895 END FIND_DEF_HIER_IN_REPOS;
2896
2897
2898 -------------------------------------------------------------------------
2899 -- VAL_AGAINST_EPB - Validates the Business Area version against EPB, to
2900 -- find any places where EPB will be adversely affected
2901 --
2902 -- IN: p_version_id - The Version ID to validate
2903 -- p_init_fix - Flag to confirm whether MD fixing should be done fixed or not
2904 --
2905 -------------------------------------------------------------------------
2906 PROCEDURE VAL_AGAINST_EPB (p_version_id IN NUMBER,
2907 p_init_fix IN VARCHAR2 DEFAULT 'N')
2908 is
2909 l_proc_name CONSTANT VARCHAR2(33) := G_PKG_NAME||'.val_against_epb';
2910
2911 l_refr_vers ZPB_BUSAREA_VERSIONS.VERSION_ID%type;
2912 l_vers_type ZPB_BUSAREA_VERSIONS.VERSION_TYPE%type;
2913 l_ba_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
2914 l_aw ZPB_BUSINESS_AREAS.DATA_AW%type;
2915 l_name ZPB_ANALYSIS_CYCLES.NAME%type;
2916 l_folder BISM_OBJECTS.FOLDER_ID%type;
2917 l_num NUMBER;
2918 l_id VARCHAR2(60);
2919 l_view VARCHAR2(60);
2920 l_val VARCHAR2(300);
2921 l_codeAW VARCHAR2(30);
2922 l_sharedAW VARCHAR2(30);
2923 l_tableID NUMBER;
2924
2925
2926 -- For Removed dimensions
2927 cursor l_dims is
2928 select A.DIMENSION_ID,
2929 DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
2930 C.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
2931 A.LOGICAL_DIM_ID
2932 from ZPB_BUSAREA_DIMENSIONS A,
2933 FEM_DIMENSIONS_VL C,
2934 FEM_FUNC_DIM_SETS_VL FDR
2935 where A.VERSION_ID = l_refr_vers
2936 and A.DIMENSION_ID = C.DIMENSION_ID
2937 AND FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
2938 and A.DIMENSION_ID not in
2939 (select B.DIMENSION_ID
2940 from ZPB_BUSAREA_DIMENSIONS B
2941 where B.VERSION_ID = p_version_id);
2942
2943 -- For Added dimensions
2944 cursor l_add_dims is
2945 select A.DIMENSION_ID
2946 from ZPB_BUSAREA_DIMENSIONS A,
2947 FEM_DIMENSIONS_VL C
2948 where A.VERSION_ID = p_version_id
2949 and A.DIMENSION_ID = C.DIMENSION_ID
2950 and A.DIMENSION_ID not in
2951 (select B.DIMENSION_ID
2952 from ZPB_BUSAREA_DIMENSIONS B
2953 where B.VERSION_ID = l_refr_vers);
2954
2955 cursor l_line_dims is
2956 select A.DIMENSION_ID,
2957 DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
2958 C.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
2959 A.EPB_LINE_DIMENSION,
2960 A.LOGICAL_DIM_ID
2961 from ZPB_BUSAREA_DIMENSIONS A,
2962 ZPB_BUSAREA_DIMENSIONS B,
2963 FEM_DIMENSIONS_VL C,
2964 FEM_FUNC_DIM_SETS_VL FDR
2965 where A.DIMENSION_ID = B.DIMENSION_ID
2966 and A.DIMENSION_ID = C.DIMENSION_ID
2967 and A.VERSION_ID = p_version_id
2968 and A.VERSION_ID = l_refr_vers
2969 AND FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
2970 and (A.EPB_LINE_DIMENSION = 'Y' and B.EPB_LINE_DIMENSION = 'N' or
2971 A.EPB_LINE_DIMENSION = 'N' and B.EPB_LINE_DIMENSION = 'Y');
2972
2973 cursor l_hiers is
2974 select decode (A.CURRENT_VERSION, 'Y', to_char(A.HIERARCHY_ID),
2975 A.HIERARCHY_ID||'V'||A.VERSION_ID) HIERARCHY_ID,
2976 C.OBJECT_NAME,
2977 E.AW_DIM_PREFIX AS DIMENSION_ID,
2978 D.DIMENSION_ID FEM_DIMENSION_ID, E.DEFAULT_HIERARCHY_ID,
2979 E.LOGICAL_DIM_ID,
2980 nvl(E.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
2981 from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES(l_ba_id,
2982 'R')) A,
2983 FEM_HIERARCHIES D,
2984 FEM_OBJECT_CATALOG_VL C,
2985 ZPB_BUSAREA_DIMENSIONS E
2986 where A.HIERARCHY_ID = C.OBJECT_ID
2987 and A.HIERARCHY_ID = D.HIERARCHY_OBJ_ID
2988 and A.LOGICAL_DIM_ID = E.LOGICAL_DIM_ID
2989 and D.DIMENSION_ID = E.DIMENSION_ID
2990 and E.VERSION_ID = l_refr_vers
2991 and decode (A.CURRENT_VERSION, 'Y', to_char(A.HIERARCHY_ID),
2992 A.HIERARCHY_ID||'V'||A.VERSION_ID) not in
2993 (select decode (B.CURRENT_VERSION, 'Y', to_char(B.HIERARCHY_ID),
2994 B.HIERARCHY_ID||'V'||B.VERSION_ID) HIERARCHY_ID
2995 from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES(l_ba_id,
2996 l_vers_type)) B);
2997 cursor l_levels is
2998 select B.LEVEL_ID,
2999 B.HIERARCHY_ID,
3000 A.DIMENSION_GROUP_NAME,
3001 C.AW_DIM_PREFIX AS DIMENSION_ID,
3002 A.DIMENSION_ID FEM_DIMENSION_ID,
3003 C.LOGICAL_DIM_ID,
3004 nvl(C.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
3005 from FEM_DIMENSION_GRPS_VL A,
3006 ZPB_BUSAREA_LEVELS B,
3007 ZPB_BUSAREA_DIMENSIONS C
3008 where A.DIMENSION_GROUP_ID = B.LEVEL_ID
3009 and B.VERSION_ID = l_refr_vers
3010 and C.VERSION_ID = l_refr_vers
3011 and C.LOGICAL_DIM_ID = B.LOGICAL_DIM_ID
3012 and C.DIMENSION_ID = A.DIMENSION_ID
3013 and B.LEVEL_ID not in
3014 (select C.LEVEL_ID
3015 from ZPB_BUSAREA_LEVELS C
3016 where C.VERSION_ID = p_version_id);
3017
3018 cursor l_datasets is
3019 select A.DATASET_ID, A.NAME
3020 from ZPB_BUSAREA_DATASETS_VL A
3021 where A.VERSION_ID = l_refr_vers
3022 and A.DATASET_ID not in
3023 (select B.DATASET_ID
3024 from ZPB_BUSAREA_DATASETS B
3025 where B.VERSION_ID = p_version_id);
3026
3027 cursor l_attrs is
3028 select A.ATTRIBUTE_ID, C.ATTRIBUTE_NAME,
3029 D.AW_DIM_PREFIX AS DIMENSION_ID,
3030 C.DIMENSION_ID FEM_DIMENSION_ID,
3031 D.LOGICAL_DIM_ID,
3032 nvl(D.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
3033 from ZPB_BUSAREA_ATTRIBUTES A,
3034 FEM_DIM_ATTRIBUTES_VL C,
3035 ZPB_BUSAREA_DIMENSIONS D
3036 where A.VERSION_ID = l_refr_vers
3037 and A.ATTRIBUTE_ID = C.ATTRIBUTE_ID
3038 and C.DIMENSION_ID = D.DIMENSION_ID
3039 and A.LOGICAL_DIM_ID = D.LOGICAL_DIM_ID
3040 and D.VERSION_ID = l_refr_vers
3041 and A.ATTRIBUTE_ID not in
3042 (select B.ATTRIBUTE_ID
3043 from ZPB_BUSAREA_ATTRIBUTES B
3044 where B.VERSION_ID = p_version_id);
3045
3046 cursor l_ac_datasets(p_ba NUMBER, p_dataset NUMBER) is
3047 select distinct nvl (C.INSTANCE_DESCRIPTION, A.NAME) NAME
3048 from ZPB_ANALYSIS_CYCLES A, ZPB_CYCLE_DATASETS B,
3049 ZPB_ANALYSIS_CYCLE_INSTANCES C
3050 where A.BUSINESS_AREA_ID = p_ba
3051 and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
3052 and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
3053 and B.DATASET_CODE = p_dataset
3054 and A.ANALYSIS_CYCLE_ID = C.INSTANCE_AC_ID(+);
3055
3056 begin
3057 FND_MSG_PUB.INITIALIZE;
3058
3059 ZPB_LOG.WRITE (l_proc_name||'.begin', 'Begin EPB validation of '||
3060 p_version_id);
3061 begin
3062 select A.BUSINESS_AREA_ID, A.VERSION_ID, C.DATA_AW, B.VERSION_TYPE
3063 into l_ba_id, l_refr_vers, l_aw, l_vers_type
3064 from ZPB_BUSAREA_VERSIONS A,
3065 ZPB_BUSAREA_VERSIONS B,
3066 ZPB_BUSINESS_AREAS C
3067 where A.BUSINESS_AREA_ID = B.BUSINESS_AREA_ID
3068 and A.VERSION_TYPE = 'R'
3069 and B.VERSION_ID = p_version_id
3070 and C.BUSINESS_AREA_ID = A.BUSINESS_AREA_ID;
3071 exception
3072 when no_data_found then
3073 l_refr_vers := null;
3074 end;
3075
3076
3077 BEGIN
3078 l_codeAW := zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(FND_GLOBAL.USER_ID);
3079 l_sharedAW := 'ZPB.ZPBDATA'||l_ba_id;
3080 ATTACH_AWS(l_codeAW, l_sharedAW);
3081
3082 SELECT SHAR_TABLE_ID INTO l_tableID FROM zpb_dimensions
3083 WHERE BUS_AREA_ID = l_ba_id
3084 AND dim_type = 'LINE';
3085
3086 SELECT table_name INTO G_LINE_DIM_TABLE_NAME FROM zpb_tables
3087 WHERE TABLE_ID = l_tableID;
3088
3089 SELECT COLUMN_NAME INTO G_MEMBER_ID_COL FROM ZPB_COLUMNS
3090 WHERE COLUMN_TYPE = 'MEMBER_COLUMN' AND TABLE_ID = l_tableID;
3091
3092 SELECT COLUMN_NAME INTO G_MEMBER_NAME_COL FROM ZPB_COLUMNS
3093 WHERE COLUMN_TYPE = 'LNAME_COLUMN' AND TABLE_ID = l_tableID;
3094 EXCEPTION
3095 WHEN no_data_found THEN
3096 null;
3097 END;
3098
3099
3100 --
3101 -- No refreshed version, then nothing to compare to (first time)
3102 --
3103 if (l_refr_vers is not null) then
3104 --
3105 -- Check for removed datasets in a BP
3106 --
3107 for each_dataset in l_datasets loop
3108 for each_ac in l_ac_datasets(l_ba_id, each_dataset.DATASET_ID) loop
3109 REGISTER_ERROR ('O', 'W', 'ZPB_BUSAREA_VAL_INV_DATASET',
3110 'BP_NAME', each_ac.NAME, 'N',
3111 'DATASET', each_dataset.NAME, 'N');
3112 end loop;
3113 end loop;
3114 --
3115 -- Check for any missing dimensions
3116 --
3117 for each_dim in l_dims loop
3118 REGISTER_ERROR ('S', 'E', 'ZPB_BUSAREA_VAL_INV_REM_DIM',
3119 'NAME', each_dim.DIMENSION_NAME, 'N');
3120 l_refr_vers := null;
3121 end loop;
3122
3123 if (l_refr_vers is not null) then
3124 for each_line_dim in l_line_dims loop
3125 REGISTER_ERROR ('S', 'E', 'ZPB_BUSAREA_VAL_INV_LINE_DIM',
3126 'NAME', each_line_dim.DIMENSION_NAME, 'N');
3127 l_refr_vers := null;
3128 end loop;
3129 end if;
3130 end if;
3131
3132 --
3133 -- IF missing dimensions, no need to validate rest, will result in
3134 -- many irroneous errors
3135 --
3136 if (l_refr_vers is not null) then
3137 --
3138 -- If Any Dimension is added, need to refresh all queries
3139 --
3140 --For Add Dimension case we need not validate and show warning/error msgs
3141 --as all query fixing process would only be backend, and unrelated to the user.
3142 if(p_init_fix = 'Y') then
3143
3144 IF NOT l_add_dims%ISOPEN THEN
3145 OPEN l_add_dims;
3146 END IF;
3147 FETCH l_add_dims INTO l_num;
3148
3149 IF l_add_dims%FOUND THEN
3150 FIND_IN_REPOS(l_ba_id, p_version_id, '%', NULL, NULL, p_init_fix);
3151 END IF;
3152 end if;
3153 --
3154 -- Hierarchies:
3155 --
3156 for each_hier in l_hiers loop
3157 l_id := each_hier.DIMENSION_ID ||'H_'|| nvl(each_hier.HIERARCHY_ID,0);
3158
3159 if (each_hier.FUNC_DIM_SET_ID = -99) then
3160 select DIMENSION_NAME
3161 into l_val
3162 from FEM_DIMENSIONS_VL
3163 where DIMENSION_ID = each_hier.FEM_DIMENSION_ID;
3164 else
3165 select FUNC_DIM_SET_NAME
3166 into l_val
3167 from FEM_FUNC_DIM_SETS_VL
3168 where FUNC_DIM_SET_ID = each_hier.FUNC_DIM_SET_ID;
3169 end if;
3170
3171 REGISTER_ERROR ('S', 'W', 'ZPB_BUSAREA_VAL_REMOVE_META',
3172 'OBJ_TYPE', 'ZPB_HIERARCHY', 'Y',
3173 'NAME', each_hier.OBJECT_NAME, 'N',
3174 'DIM_NAME', l_val, 'N');
3175
3176 IF(each_hier.HIERARCHY_ID = each_hier.DEFAULT_HIERARCHY_ID) THEN
3177 FIND_DEF_HIER_IN_REPOS(l_ba_id, p_version_id, l_id,
3178 'DEFAULT_HIERARCHY', each_hier.OBJECT_NAME, p_init_fix);
3179 ELSE
3180 FIND_IN_REPOS(l_ba_id, p_version_id, l_id,
3181 'HIERARCHY', each_hier.OBJECT_NAME, p_init_fix);
3182 END IF;
3183 end loop;
3184
3185 --
3186 -- Levels:
3187 --
3188 for each_level in l_levels loop
3189 l_id := each_level.DIMENSION_ID
3190 ||'H0LV'||nvl(each_level.LEVEL_ID,0);
3191
3192 if (each_level.FUNC_DIM_SET_ID = -99) then
3193 select DIMENSION_NAME
3194 into l_val
3195 from FEM_DIMENSIONS_VL
3196 where DIMENSION_ID = each_level.FEM_DIMENSION_ID;
3197 else
3198 select FUNC_DIM_SET_NAME
3199 into l_val
3200 from FEM_FUNC_DIM_SETS_VL
3201 where FUNC_DIM_SET_ID = each_level.FUNC_DIM_SET_ID;
3202 end if;
3203
3204 REGISTER_ERROR ('S', 'W', 'ZPB_BUSAREA_VAL_REMOVE_META',
3205 'OBJ_TYPE', 'ZPB_LEVEL', 'Y',
3206 'NAME', each_level.DIMENSION_GROUP_NAME, 'N',
3207 'DIM_NAME', l_val, 'N');
3208
3209 FIND_IN_REPOS(l_ba_id, p_version_id, l_id,
3210 'LEVEL', each_level.DIMENSION_GROUP_NAME, p_init_fix);
3211 end loop;
3212
3213 --
3214 -- Attributes:
3215 --
3216 for each_attr in l_attrs loop
3217 l_id := each_attr.DIMENSION_ID || 'A' || nvl(each_attr.ATTRIBUTE_ID,0);
3218
3219 if (each_attr.FUNC_DIM_SET_ID = -99) then
3220 select DIMENSION_NAME
3221 into l_val
3222 from FEM_DIMENSIONS_VL
3223 where DIMENSION_ID = each_attr.FEM_DIMENSION_ID;
3224 else
3225 select FUNC_DIM_SET_NAME
3226 into l_val
3227 from FEM_FUNC_DIM_SETS_VL
3228 where FUNC_DIM_SET_ID = each_attr.FUNC_DIM_SET_ID;
3229 end if;
3230
3231 REGISTER_ERROR ('S', 'W', 'ZPB_BUSAREA_VAL_REMOVE_META',
3232 'OBJ_TYPE', 'ZPB_ATTRIBUTE', 'Y',
3233 'NAME', each_attr.ATTRIBUTE_NAME, 'N',
3234 'DIM_NAME', l_val, 'N');
3235
3236 FIND_IN_REPOS(l_ba_id, p_version_id, l_id, 'ATTRIBUTE',
3237 each_attr.ATTRIBUTE_NAME, p_init_fix);
3238 end loop;
3239 end if;
3240 DETACH_AWS(l_codeAW, l_sharedAW);
3241
3242 ZPB_LOG.WRITE(l_proc_name||'.end', 'End EPB validation of '||p_version_id);
3243
3244 EXCEPTION
3245 WHEN OTHERS THEN
3246 DETACH_AWS(l_codeAW, l_sharedAW);
3247 end VAL_AGAINST_EPB;
3248
3249
3250 END ZPB_BUSAREA_VAL;