[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_DIM_REL_PUB
Source
1 PACKAGE BODY BSC_BIS_DIM_REL_PUB AS
2 /* $Header: BSCRPMDB.pls 120.12 2006/07/17 07:14:34 ppandey ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCRPMDB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for Dimension-Relationships, part of PMD APIs |
13 REM | |
14 REM | NOTES |
15 REM | 14-FEB-2003 PAJOHRI Created. |
16 REM | 21-MAY-2003 ADRAO Added Incremental Changes |
17 REM | 09-JUN-2003 ADRAO Added Granular Locking |
18 REM | 18-JUL-2003 Pradeep VL used in place of TL for NLS Bug#3053793 |
19 REM | 11-AUG-2003 ADEULGAO fixed bug#3081595 |
20 REM | 12-AUG-2003 ADRAO Added new index for Loader Performance for |
21 REM | for Dimension Object tables Bug#3090828 |
22 REM | 20-OCT-2003 PAJOHRI Bug#3179995 |
23 REM | 29-OCT-2003 PAJOHRI Bug#3120190,Modified API- Create_One_To_N_MTable|
24 REM | to Create Non_Unique Index on 'Master_Table'|
25 REM | for all its parent relation columns. |
26 REM | 20-OCT-2003 PAJOHRI Bug # 3179995 |
27 REM | 04-NOV-2003 PAJOHRI Bug # 3152258 |
28 REM | 08-DEC-2003 KYADAMAK Bug #3225685 |
29 REM | 15-DEC-2003 ADRAO Removed Dynamic SQLs for Bug #3236356 |
30 REM | 02-JAN-2004 Adeulgao fixed bug#3343898 |
31 REM | 28-JAN-2004 ADRAO Fixed API Assign_New_Dim_Obj_Rels(), to handle |
32 REM | MxN relationship when the child is updated for |
33 REM | Bug #3395161 |
34 REM | 19-MAR-2004 PAJOHRI Bug #3518647, Added a validation for message |
35 REM | text "BSC_MAX_DIM_OBJ_RELS" and replaced |
36 REM | VARCHAR2(8000) size to VARCHAR2(32000) |
37 REM | 29-MAR-2004 PAJOHRI Bug #3530886, Modified tablespaces for tables |
38 REM | VARCHAR2(8000) size to VARCHAR2(32000) |
39 REM | 23-APR-2004 ASHANKAR Bug #3518610,Added the fucntion Validate |
40 REM | listbutton |
41 REM | 15-OCT-2004 ASHANKAR Bug#3459282 Filter button Validation. |
42 REM | 16-FEB-2005 ashankar Bug#4184438 Added the Synch Up API |
43 REM | BSC_SYNC_MVLOGS.Sync_dim_table_mv_log |
44 REM | 02-May-2005 visuri Modified for Bug#4323383 |
45 REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
46 REM | 12-SEP-2005 adrao Modified API Assign_Dim_Obj_Rels for Bug4601099 |
47 REM | 29-SEP-2005 adrao Modified API Assign_Dim_Obj_Rels for Bug4619393 |
48 REM | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
49 REM | 27-DEC-2005 kyadamak Calling BIA API for bug#4875047 |
50 REM | 13-jan-2005 ashankar Bug#4947293 calling the API sync_dimension_table|
51 REM | dynamically |
52 REM | 31-JAN-2005 adrao Made a call to Refresh_BSC_PMF_Dim_View() API |
53 REM | for Bug#4758995 |
54 REM | 01-MAR-2006 adrao is_KPI_Flag_For_Dim_Obj_Rels Modified for |
55 REM | Bug#5057436 |
56 REM | 19-JUN-2006 adrao Bug#5300060 - refresh all the child dimension |
57 REM | objects as well |
58 REM | 26-JUN-2006 akoduri Bug#5335325 - Prototype flag not getting changed|
59 REM | when a BIS dimension object is added to AG Rep |
60 REM | 17-JUL-2006 ppandey Bug#5389895 - Create/Update Relationship issue |
61 REM | for non-numeric user code |
62 REM +=======================================================================+
63 */
64 CONFIG_LIMIT_RELS CONSTANT NUMBER := 5;
65 MAX_PARENTS_RELS_1_N CONSTANT NUMBER := 50;
66 --==============================================================
67 TYPE One_To_N_Index_Type IS Record
68 ( p_Column_Name VARCHAR2(30)
69 );
70 --==============================================================
71 TYPE One_To_N_Index_Table IS TABLE OF One_To_N_Index_Type INDEX BY BINARY_INTEGER;
72 --==============================================================
73 TYPE One_To_N_Original_Type IS Record
74 ( p_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
75 , p_Parent_Dim_Ids VARCHAR2(32000)
76 , p_Parent_Count NUMBER
77 , p_Refresh_Flag BOOLEAN
78 );
79 --==============================================================
80 TYPE One_To_N_Org_Table_Type IS TABLE OF One_To_N_Original_Type INDEX BY BINARY_INTEGER;
81 --==============================================================
82 TYPE M_To_N_Original_Type IS Record
83 ( p_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
84 , p_Parent_Dim_ID BSC_SYS_DIM_LEVEL_RELS.parent_dim_level_id%TYPE
85 , p_Refresh_Flag BOOLEAN
86 );
87 --==============================================================
88 TYPE M_To_N_Org_Table_Type IS TABLE OF M_To_N_Original_Type INDEX BY BINARY_INTEGER;
89 --==============================================================
90 TYPE Relation_Original_Type IS Record
91 ( p_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
92 , p_Parent_Dim_Id BSC_SYS_DIM_LEVEL_RELS.parent_dim_level_id%TYPE
93 , p_Relation_Type BSC_SYS_DIM_LEVEL_RELS.relation_type%TYPE
94 , p_Refresh_Flag BOOLEAN
95 , p_Refresh_No NUMBER
96 );
97
98 TYPE Relation_Table_Type IS TABLE OF Relation_Original_Type INDEX BY BINARY_INTEGER;
99
100 FUNCTION is_more
101 ( x_remain_id IN OUT NOCOPY VARCHAR2
102 , x_remain_rel_type IN OUT NOCOPY VARCHAR2
103 , x_remain_rel_column IN OUT NOCOPY VARCHAR2
104 , x_remain_data_type IN OUT NOCOPY VARCHAR2
105 , x_remain_data_source IN OUT NOCOPY VARCHAR2
106 , x_id OUT NOCOPY NUMBER
107 , x_rel_type OUT NOCOPY NUMBER
108 , x_rel_column OUT NOCOPY VARCHAR2
109 , x_data_type OUT NOCOPY VARCHAR2
110 , x_data_source OUT NOCOPY VARCHAR2
111 ) RETURN BOOLEAN;
112 --==============================================================
113 FUNCTION Create_One_To_N_MTable
114 ( p_dim_obj_id IN NUMBER
115 , x_return_status OUT NOCOPY VARCHAR2
116 , x_msg_count OUT NOCOPY NUMBER
117 , x_msg_data OUT NOCOPY VARCHAR2
118 ) RETURN BOOLEAN;
119 --==============================================================
120 FUNCTION Create_M_To_N_MTable
121 ( p_dim_obj_id IN NUMBER
122 , p_parent_id IN VARCHAR2
123 , x_return_status OUT NOCOPY VARCHAR2
124 , x_msg_count OUT NOCOPY NUMBER
125 , x_msg_data OUT NOCOPY VARCHAR2
126 ) RETURN BOOLEAN;
127 --==============================================================
128 PROCEDURE Drop_M_To_N_Unused_Tabs
129 ( p_dim_obj_id IN NUMBER
130 , p_parent_id IN VARCHAR2
131 , x_return_status OUT NOCOPY VARCHAR2
132 , x_msg_count OUT NOCOPY NUMBER
133 , x_msg_data OUT NOCOPY VARCHAR2
134 );
135
136 /*********************************************************************************
137 FUNCTION Get_Original_Child_Ids, Bug#5300060
138 *********************************************************************************/
139 FUNCTION Get_Original_Child_Ids
140 (
141 p_dim_obj_id IN NUMBER
142 ) RETURN VARCHAR2;
143
144 --==============================================================
145 FUNCTION Is_More
146 ( x_remain_id IN OUT NOCOPY VARCHAR2
147 , x_remain_rel_type IN OUT NOCOPY VARCHAR2
148 , x_id OUT NOCOPY NUMBER
149 , x_rel_type OUT NOCOPY NUMBER
150 ) RETURN BOOLEAN
151 IS
152 l_pos_ids NUMBER;
153 l_pos_rel_types NUMBER;
154 BEGIN
155 IF (x_remain_id IS NOT NULL) THEN
156 l_pos_ids := INSTR(x_remain_id, ',');
157 l_pos_rel_types := INSTR(x_remain_rel_type, ',');
158
159 IF (l_pos_ids > 0) THEN
160 x_id := TO_NUMBER(TRIM(SUBSTR(x_remain_id, 1, l_pos_ids - 1)));
161 x_rel_type := TO_NUMBER(TRIM(SUBSTR(x_remain_rel_type, 1, l_pos_rel_types - 1)));
162
163 x_remain_id := TRIM(SUBSTR(x_remain_id, l_pos_ids + 1));
164 x_remain_rel_type := TRIM(SUBSTR(x_remain_rel_type, l_pos_rel_types + 1));
165 ELSE
166 x_id := TO_NUMBER(TRIM(x_remain_id));
167 x_rel_type := TO_NUMBER(TRIM(x_remain_rel_type));
168
169 x_remain_id := NULL;
170 x_remain_rel_type := NULL;
171 END IF;
172 RETURN TRUE;
173 ELSE
174 RETURN FALSE;
175 END IF;
176 END Is_More;
177 --==============================================================
178 FUNCTION Is_More
179 ( p_dim_lev_ids IN OUT NOCOPY VARCHAR2
180 , p_dim_lev_id OUT NOCOPY VARCHAR2
181 ) RETURN BOOLEAN
182 IS
183 l_pos_ids NUMBER;
184 l_pos_rel_types NUMBER;
185 l_pos_rel_columns NUMBER;
186 BEGIN
187 IF (p_dim_lev_ids IS NOT NULL) THEN
188 l_pos_ids := INSTR(p_dim_lev_ids, ',');
189 IF (l_pos_ids > 0) THEN
190 p_dim_lev_id := TRIM(SUBSTR(p_dim_lev_ids, 1, l_pos_ids - 1));
191 p_dim_lev_ids := TRIM(SUBSTR(p_dim_lev_ids, l_pos_ids + 1));
192 ELSE
193 p_dim_lev_id := TRIM(p_dim_lev_ids);
194 p_dim_lev_ids := NULL;
195 END IF;
196 RETURN TRUE;
197 ELSE
198 RETURN FALSE;
199 END IF;
200 END Is_More;
201 --==============================================================
202 PROCEDURE get_Original_Relations
203 ( p_dim_obj_id IN NUMBER
204 , x_One_N_Table OUT NOCOPY BSC_BIS_DIM_REL_PUB.One_To_N_Org_Table_Type
205 , x_M_N_Table OUT NOCOPY BSC_BIS_DIM_REL_PUB.M_To_N_Org_Table_Type
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_msg_count OUT NOCOPY NUMBER
208 , x_msg_data OUT NOCOPY VARCHAR2
209 ) IS
210 CURSOR c_keep_original_rels IS
211 SELECT Dim_Level_Id
212 , Parent_Dim_Level_Id
213 , Relation_Type
214 FROM BSC_SYS_DIM_LEVEL_RELS
215 WHERE (Parent_Dim_Level_Id = p_dim_obj_id
216 AND Relation_Type <> 2 )
217 OR Dim_Level_Id = p_dim_obj_id
218 ORDER BY Dim_Level_Id;
219
220 l_One_N_Count NUMBER := 0;
221 l_M_N_Count NUMBER := 0;
222 BEGIN
223 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_REL_PUB.get_Original_Relations Procedure');
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225 FOR cd IN c_keep_original_rels LOOP
226 --for one to many relations where cd.Relation_Type = 1
227 IF ((l_One_N_Count = 0) AND (cd.Relation_Type = 1)) THEN
228 x_One_N_Table(l_One_N_Count).p_dim_obj_id := cd.Dim_Level_Id;
229 x_One_N_Table(l_One_N_Count).p_Parent_dim_ids := cd.Parent_Dim_Level_Id;
230 x_One_N_Table(l_One_N_Count).p_parent_count := 1;
231 x_One_N_Table(l_One_N_Count).p_refresh_flag := TRUE;
232 l_One_N_Count := l_One_N_Count + 1;
233 ELSIF ((l_One_N_Count <> 0) AND (cd.Relation_Type = 1) AND
234 (x_One_N_Table(l_One_N_Count - 1).p_dim_obj_id = cd.Dim_Level_Id)) THEN
235 x_One_N_Table(l_One_N_Count-1).p_Parent_dim_ids
236 := x_One_N_Table(l_One_N_Count-1).p_Parent_dim_ids||', '|| cd.Parent_Dim_Level_Id;
237 x_One_N_Table(l_One_N_Count-1).p_parent_count
238 := x_One_N_Table(l_One_N_Count-1).p_parent_count + 1;
239 ELSIF ((l_One_N_Count <> 0) AND (cd.Relation_Type = 1) AND
240 (x_One_N_Table(l_One_N_Count - 1).p_dim_obj_id <> cd.Dim_Level_Id)) THEN
241 x_One_N_Table(l_One_N_Count).p_parent_count := 1;
242 x_One_N_Table(l_One_N_Count).p_dim_obj_id := cd.Dim_Level_Id;
243 x_One_N_Table(l_One_N_Count).p_Parent_dim_ids := cd.Parent_Dim_Level_Id;
244 x_One_N_Table(l_One_N_Count).p_refresh_flag := TRUE;
245 l_One_N_Count := l_One_N_Count + 1;
246 END IF;
247 --for many to many relations where cd.Relation_Type = 2
248 IF (cd.Relation_Type = 2) THEN
249 IF (cd.Dim_Level_Id < cd.Parent_Dim_Level_Id) THEN
250 x_M_N_Table(l_M_N_Count).p_dim_obj_id := cd.Dim_Level_Id ;
251 x_M_N_Table(l_M_N_Count).p_Parent_dim_id := cd.Parent_Dim_Level_Id;
252 x_M_N_Table(l_M_N_Count).p_refresh_flag := TRUE;
253 l_M_N_Count := l_M_N_Count + 1;
254 ELSE
255 x_M_N_Table(l_M_N_Count).p_dim_obj_id := cd.Parent_Dim_Level_Id;
256 x_M_N_Table(l_M_N_Count).p_Parent_dim_id := cd.Dim_Level_Id ;
257 x_M_N_Table(l_M_N_Count).p_refresh_flag := TRUE;
258 l_M_N_Count := l_M_N_Count + 1;
259 END IF;
260 END IF;
261 END LOOP;
262 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_REL_PUB.get_Original_Relations Procedure');
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 IF (x_msg_data IS NOT NULL) THEN
267 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.get_Original_Relations ';
268 ELSE
269 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.get_Original_Relations ';
270 END IF;
271 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
272 WHEN OTHERS THEN
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 IF (x_msg_data IS NOT NULL) THEN
275 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.get_Original_Relations ';
276 ELSE
277 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.get_Original_Relations ';
278 END IF;
279 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
280 END get_Original_Relations;
281
282 /********************************************************************
283 PROCEDURE : store_Relations
284 DESCRIPTION : This procedure stores the relationship between
285 dimension objects into Cache.
286 INPUT : p_dim_obj_id :Dimension object corresponding to which
287 relationship needs to be stored.
288
289 OUPUT : x_rel_Table : Cache which stores the relationships.
290 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
291 /*******************************************************************/
292
293 PROCEDURE store_Relations
294 ( p_dim_obj_id IN NUMBER
295 , x_rel_Table OUT NOCOPY BSC_BIS_DIM_REL_PUB.Relation_Table_Type
296 , x_return_status OUT NOCOPY VARCHAR2
297 , x_msg_count OUT NOCOPY NUMBER
298 , x_msg_data OUT NOCOPY VARCHAR2
299 )IS
300 CURSOR c_relations IS
301 SELECT Dim_Level_Id
302 , Parent_Dim_Level_Id
303 , Relation_Type
304 FROM BSC_SYS_DIM_LEVEL_RELS
305 WHERE Parent_Dim_Level_Id = p_dim_obj_id
306 OR Dim_Level_Id = p_dim_obj_id
307 ORDER BY Dim_Level_Id;
308
309 l_Count NUMBER;
310
311 BEGIN
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313 l_Count := 0;
314
315 FOR cd IN c_relations LOOP
316 x_rel_Table(l_Count).p_Dim_Obj_ID := cd.Dim_Level_Id;
317 x_rel_Table(l_Count).p_Parent_Dim_Id := cd.Parent_Dim_Level_Id;
318 x_rel_Table(l_Count).p_Relation_Type := cd.Relation_Type;
319 x_rel_Table(l_Count).p_Refresh_Flag := FALSE;
320 x_rel_Table(l_Count).p_Refresh_No := -1;
321 l_Count := l_Count + 1;
322 END LOOP;
323
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 IF (x_msg_data IS NOT NULL) THEN
328 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.store_Relations ';
329 ELSE
330 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.store_Relations ';
331 END IF;
332 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
333 WHEN OTHERS THEN
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
335 IF (x_msg_data IS NOT NULL) THEN
336 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.store_Relations ';
337 ELSE
338 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.store_Relations ';
339 END IF;
340 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
341 END store_Relations;
342
343 /********************************************************************
344 PROCEDURE : is_Filtered_Applied
345 DESCRIPTION : This fucntion tells whether the dimension objects are being
346 used in Filter views or not.
347 INPUT : p_dim_level_id :Dimension object corresponding to which
348 Filter views need to be find out.
349
350 OUPUT : TRUE : Used in Filter views
351 FALSE: Not used in Filter Views
352 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
353 /*******************************************************************/
354 FUNCTION is_Filtered_Applied
355 (
356 p_dim_level_id IN BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
357 )RETURN BOOLEAN IS
358 l_count NUMBER;
359 BEGIN
360 SELECT COUNT(0)
361 INTO l_count
362 FROM BSC_SYS_FILTERS_VIEWS
363 WHERE DIM_LEVEL_ID = p_dim_level_id;
364
365 IF(l_count>0)THEN
366 RETURN TRUE;
367 ELSE
368 RETURN FALSE;
369 END IF;
370 END is_Filtered_Applied;
371
372 /********************************************************************
373 PROCEDURE : get_Filtered_Tabs
374 DESCRIPTION : This procedure returns the comma separated tabs where
375 dimension objects are used in filter views.
376 INPUT : p_dim_level_id :
377 p_par_dim_level_id
378
379 OUPUT : p_common_tabs (comma separated tabs where the passed dimension
380 objects are used as filters)
381 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
382 /*******************************************************************/
383
384 PROCEDURE get_Filtered_Tabs
385 (
386 p_dim_level_id IN NUMBER
387 , p_par_dim_level_id IN NUMBER
388 , p_common_tabs OUT NOCOPY VARCHAR2
389 , x_return_status OUT NOCOPY VARCHAR2
390 , x_msg_count OUT NOCOPY NUMBER
391 , x_msg_data OUT NOCOPY VARCHAR2
392 )IS
393
394 l_dim_level_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
395 l_common_tabs VARCHAR2(32000);
396 l_par_tab NUMBER;
397 l_child_tab NUMBER;
398 l_Count NUMBER;
399
400
401 CURSOR c_filter_par_tabs IS
402 SELECT DISTINCT source_code
403 FROM BSC_SYS_FILTERS_VIEWS
404 WHERE source_type = 1
405 AND dim_level_id = p_par_dim_level_id;
406
407 CURSOR c_filter_chd_tabs IS
408 SELECT DISTINCT source_code
409 FROM BSC_SYS_FILTERS_VIEWS
410 WHERE source_type = 1
411 AND dim_level_id = p_dim_level_id;
412
413 BEGIN
414 l_Count := 0;
415
416 IF((p_dim_level_id IS NOT NULL) AND (p_par_dim_level_id IS NOT NULL)) THEN
417 FOR cd_par IN c_filter_par_tabs LOOP
418 FOR cd_chd IN c_filter_chd_tabs LOOP
419 IF(cd_chd.source_code = cd_par.source_code) THEN
420 IF(l_Count = 0) THEN
421 l_common_tabs := cd_par.source_code;
422 l_Count := l_Count + 1;
423 ELSE
424 l_common_tabs := l_common_tabs || ',' || cd_par.source_code;
425 END IF;
426 EXIT;
427 END IF;
428 END LOOP;
429 END LOOP;
430 END IF;
431 p_common_tabs := l_common_tabs;
432
433 END get_Filtered_Tabs;
434
435 /********************************************************************
436 PROCEDURE : Validate_Filtered_Tabs
437 DESCRIPTION : This procedure Validates if the dimension objects being passed are used in
438 filter views.If yes then it get all the corresponding tabs
439 where the filter views need to be dropped.for each tab
440 it drops the child filter view.Parent view remains as it is.
441 INPUT : p_dim_level_id :
442 p_par_dim_level_id
443
444 OUPUT : Corresponding filter views for dimension objects are dropped.
445 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
446 /*******************************************************************/
447
448 PROCEDURE Validate_Filtered_Tabs
449 (
450 p_dim_obj_id IN BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
451 , p_par_dim_obj_id IN BSC_SYS_DIM_LEVEL_RELS.parent_dim_level_id%TYPE
452 , x_return_status OUT NOCOPY VARCHAR2
453 , x_msg_count OUT NOCOPY NUMBER
454 , x_msg_data OUT NOCOPY VARCHAR2
455 )IS
456 l_common_tabs VARCHAR2(32000);
457 l_tab VARCHAR2(30);
458 l_Sql VARCHAR2(32000);
459 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
460 BEGIN
461 FND_MSG_PUB.Initialize;
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 IF((p_dim_obj_id IS NOT NULL) AND (p_par_dim_obj_id IS NOT NULL)) THEN
465 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(p_dim_obj_id)
466 AND(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(p_par_dim_obj_id)))THEN
467
468 BSC_BIS_DIM_REL_PUB.get_Filtered_Tabs
469 (
470 p_dim_level_id => p_dim_obj_id
471 , p_par_dim_level_id => p_par_dim_obj_id
472 , p_common_tabs => l_common_tabs
473 , x_return_status => x_return_status
474 , x_msg_Count => x_msg_count
475 , x_msg_data => x_msg_data
476 );
477 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481 IF(l_common_tabs IS NOT NULL) THEN
482 WHILE (is_more( p_dim_lev_ids => l_common_tabs
483 , p_dim_lev_id => l_tab)
484 )LOOP
485 BSC_DIM_FILTERS_PUB.Drop_Filter
486 ( p_Tab_Id => l_tab
487 , p_Dim_Level_Id => p_dim_obj_id
488 , x_return_status => x_return_status
489 , x_msg_COUNT => x_msg_count
490 , x_msg_data => x_msg_data
491 );
492 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495 END LOOP;
496 END IF;
497 ELSIF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(p_dim_obj_id)
498 AND(NOT BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(p_par_dim_obj_id)))THEN
499 /*********************************************************
500 Its not possible to have filter applied on child and parent is not
501 having the filter applied.If this condition exists then we have to remove
502 the filter on the child.
503 No need to check for relations because we couldn't have reached here
504 if there was no relationship between dimesnion objects.
505 /*********************************************************/
506 l_Sql := ' SELECT DISTINCT A.TAB_ID '||
507 ' FROM ' ||
508 ' BSC_TABS_VL A '||
509 ' , BSC_TAB_INDICATORS B '||
510 ' , BSC_KPI_DIM_LEVELS_VL C '||
511 ' , BSC_SYS_DIM_LEVELS_VL D '||
512 ' WHERE A.TAB_ID =B.TAB_ID '||
513 ' AND B.INDICATOR =C.INDICATOR '||
514 ' AND C.LEVEL_TABLE_NAME = D.LEVEL_TABLE_NAME '||
515 ' AND D.DIM_LEVEL_ID IN (:1,:2) ' ;
516 OPEN l_cursor FOR l_sql USING p_dim_obj_id,p_par_dim_obj_id;
517 LOOP
518 FETCH l_cursor INTO l_tab ;
519 EXIT WHEN l_cursor%NOTFOUND;
520 BSC_DIM_FILTERS_PUB.Drop_Filter
521 ( p_Tab_Id => l_tab
522 , p_Dim_Level_Id => p_dim_obj_id
523 , x_return_status => x_return_status
524 , x_msg_COUNT => x_msg_count
525 , x_msg_data => x_msg_data
526 );
527 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 END IF;
530 END LOOP;
531 END IF;
532 END IF;
533
534
535 EXCEPTION
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 IF (x_msg_data IS NULL) THEN
538 FND_MSG_PUB.Count_And_Get
539 ( p_encoded => FND_API.G_FALSE
540 , p_count => x_msg_count
541 , p_data => x_msg_data
542 );
543 END IF;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
546 WHEN NO_DATA_FOUND THEN
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548 IF (x_msg_data IS NOT NULL) THEN
549 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs ';
550 ELSE
551 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs ';
552 END IF;
553 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
554 WHEN OTHERS THEN
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
556 IF (x_msg_data IS NOT NULL) THEN
557 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs ';
558 ELSE
559 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs ';
560 END IF;
561 END Validate_Filtered_Tabs;
562
563 /********************************************************************
564 PROCEDURE : Verify_Recreate_Filter_Views
565 DESCRIPTION : This procedure validates if the filtered view is fine or it got invalidated.
566 This may happen when the underlying view on which filter view is based
567 gets modified when more columns are added to it or some columns dropped
568 when relationship changes.
569 INPUT : p_source : Tab_Id
570 p_level_view_name :Filter view
571 p_dim_level_id : Dimesnion level id
572
573 OUPUT : If filter view was invalidated then it will recreate the filter view.
574 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
575 /*******************************************************************/
576
577 PROCEDURE Verify_Recreate_Filter_Views
578 (
579 p_source IN NUMBER
580 , p_level_view_name IN BSC_SYS_FILTERS_VIEWS.level_view_name%TYPE
581 , p_dim_level_id IN BSC_SYS_FILTERS_VIEWS.dim_level_id%TYPE
582 , x_return_status OUT NOCOPY VARCHAR2
583 , x_msg_count OUT NOCOPY NUMBER
584 , x_msg_data OUT NOCOPY VARCHAR2
585 )IS
586
587 l_Sql VARCHAR2(4000);
588 l_code NUMBER;
589 l_count NUMBER;
590 l_user_code VARCHAR2(100);
591 l_name VARCHAR2(100);
592 l_view_text VARCHAR2(32000);
593
594 BEGIN
595
596 FND_MSG_PUB.Initialize;
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598 BSC_APPS.Init_Bsc_Apps;
599
600 IF(BSC_UTILITY.is_View_Exists(p_level_view_name)) THEN
601 l_Sql := C_SELECT || C_SELECT_CLAUSE
602 || C_FROM || p_level_view_name
603 || C_WHERE || C_WHERE_CLAUSE ;
604
605 EXECUTE IMMEDIATE l_Sql INTO l_code, l_user_code, l_name;
606 END IF;
607 EXCEPTION
608 WHEN OTHERS THEN
609 IF(SQLCODE =-4063)THEN
610 BEGIN
611
612 SELECT count(1)
613 INTO l_count
614 FROM ALL_VIEWS
615 WHERE VIEW_NAME = p_level_view_name
616 AND OWNER = BSC_APPS.get_user_schema('APPS')
617 AND TEXT IS NOT NULL;
618
619 IF(l_count > 0) THEN
620 l_Sql := 'ALTER VIEW '|| p_level_view_name ||' COMPILE';
621
622 BSC_APPS.Do_Ddl_AT(l_Sql, ad_ddl.alter_view, p_level_view_name, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 NULL;
628 END;
629 END IF;
630 END Verify_Recreate_Filter_Views;
631
632 /********************************************************************
633 PROCEDURE : Validate_filter_views
634 DESCRIPTION : This procedure validates if the dimension objects are being used
635 in filter.If yes then it will validate all the filter views.
636 INPUT : p_dim_obj_id : Current dimension object
637 x_new_rel_Table : New relationship table
638 x_prev_rel_Table : Old relationship table.
639
640 OUPUT : If filter view was invalidated then it will recreate the filter view.
641 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
642 /*******************************************************************/
643
644 PROCEDURE Validate_filter_views
645 (
646 p_dim_obj_id IN NUMBER
647 , x_new_rel_Table IN BSC_BIS_DIM_REL_PUB.Relation_Table_Type
648 , x_prev_rel_Table IN BSC_BIS_DIM_REL_PUB.Relation_Table_Type
649 , x_return_status OUT NOCOPY VARCHAR2
650 , x_msg_count OUT NOCOPY NUMBER
651 , x_msg_data OUT NOCOPY VARCHAR2
652 )IS
653 CURSOR c_filter_tabs(l_dim_level_id IN NUMBER) IS
654 SELECT source_code,level_view_name,dim_level_id
655 FROM BSC_SYS_FILTERS_VIEWS
656 WHERE SOURCE_TYPE=1
657 AND DIM_LEVEL_ID =l_dim_level_id;
658
659 l_tab NUMBER;
660 l_level_view_name BSC_SYS_FILTERS_VIEWS.level_view_name%TYPE;
661 l_dim_level_id BSC_SYS_FILTERS_VIEWS.dim_level_id%TYPE;
662
663 l_old_count NUMBER;
664 l_new_count NUMBER;
665
666
667 BEGIN
668 FND_MSG_PUB.Initialize;
669 x_return_status := FND_API.G_RET_STS_SUCCESS;
670
671 l_new_count := x_new_rel_Table.COUNT;
672 l_old_count := x_prev_rel_Table.COUNT;
673
674 -- for old relationships
675 IF(l_old_count>0) THEN
676 FOR i IN 0..l_old_count -1 LOOP
677 IF(x_prev_rel_Table(i).p_Relation_Type=1) THEN
678 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_prev_rel_Table(i).p_Parent_Dim_Id))THEN
679 OPEN c_filter_tabs(x_prev_rel_Table(i).p_Parent_Dim_Id);
680 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
681 WHILE(c_filter_tabs%FOUND)LOOP
682 IF(l_level_view_name IS NOT NULL)THEN
683 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
684 (
685 p_source => l_tab
686 , p_level_view_name => l_level_view_name
687 , p_dim_level_id => l_dim_level_id
688 , x_return_status => x_return_status
689 , x_msg_count => x_msg_count
690 , x_msg_data => x_msg_data
691 );
692 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END IF;
695 END IF;
696 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
697 END LOOP;
698 CLOSE c_filter_tabs;
699 END IF;
700 ELSE
701 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_prev_rel_Table(i).p_Dim_Obj_ID))THEN
702 OPEN c_filter_tabs(x_prev_rel_Table(i).p_Dim_Obj_ID);
703 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
704 WHILE(c_filter_tabs%FOUND)LOOP
705 IF(l_level_view_name IS NOT NULL)THEN
706 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
707 (
708 p_source => l_tab
709 , p_level_view_name => l_level_view_name
710 , p_dim_level_id => l_dim_level_id
711 , x_return_status => x_return_status
712 , x_msg_count => x_msg_count
713 , x_msg_data => x_msg_data
714 );
715 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718 END IF;
719 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
720 END LOOP;
721 CLOSE c_filter_tabs;
722 END IF;
723
724 --Now for parent
725 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_prev_rel_Table(i).p_Parent_Dim_Id))THEN
726 OPEN c_filter_tabs(x_prev_rel_Table(i).p_Parent_Dim_Id);
727 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
728 WHILE(c_filter_tabs%FOUND)LOOP
729 IF(l_level_view_name IS NOT NULL)THEN
730 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
731 (
732 p_source => l_tab
733 , p_level_view_name => l_level_view_name
734 , p_dim_level_id => l_dim_level_id
735 , x_return_status => x_return_status
736 , x_msg_count => x_msg_count
737 , x_msg_data => x_msg_data
738 );
739 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742 END IF;
743 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
744 END LOOP;
745 CLOSE c_filter_tabs;
746 END IF;
747 END IF;
748 END LOOP;
749 END IF;
750
751 -- for new relationships
752 IF(l_new_count>0) THEN
753 FOR j IN 0..l_new_count -1 LOOP
754 IF(x_new_rel_Table(j).p_Relation_Type=1) THEN
755 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_new_rel_Table(j).p_Parent_Dim_Id))THEN
756 OPEN c_filter_tabs(x_new_rel_Table(j).p_Parent_Dim_Id);
757 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
758 WHILE(c_filter_tabs%FOUND)LOOP
759 IF(l_level_view_name IS NOT NULL)THEN
760 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
761 (
762 p_source => l_tab
763 , p_level_view_name => l_level_view_name
764 , p_dim_level_id => l_dim_level_id
765 , x_return_status => x_return_status
766 , x_msg_count => x_msg_count
767 , x_msg_data => x_msg_data
768 );
769 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772 END IF;
773 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
774 END LOOP;
775 CLOSE c_filter_tabs;
776 END IF;
777 ELSE
778 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_new_rel_Table(j).p_Dim_Obj_ID))THEN
779 OPEN c_filter_tabs(x_new_rel_Table(j).p_Dim_Obj_ID);
780 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
781 WHILE(c_filter_tabs%FOUND)LOOP
782 IF(l_level_view_name IS NOT NULL)THEN
783 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
784 (
785 p_source => l_tab
786 , p_level_view_name => l_level_view_name
787 , p_dim_level_id => l_dim_level_id
788 , x_return_status => x_return_status
789 , x_msg_count => x_msg_count
790 , x_msg_data => x_msg_data
791 );
792 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795 END IF;
796 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
797 END LOOP;
798 CLOSE c_filter_tabs;
799 END IF;
800
801 --Now for parent
802 IF(BSC_BIS_DIM_REL_PUB.is_Filtered_Applied(x_new_rel_Table(j).p_Parent_Dim_Id))THEN
803 OPEN c_filter_tabs(x_new_rel_Table(j).p_Parent_Dim_Id);
804 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
805 WHILE(c_filter_tabs%FOUND)LOOP
806 IF(l_level_view_name IS NOT NULL)THEN
807 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views
808 (
809 p_source => l_tab
810 , p_level_view_name => l_level_view_name
811 , p_dim_level_id => l_dim_level_id
812 , x_return_status => x_return_status
813 , x_msg_count => x_msg_count
814 , x_msg_data => x_msg_data
815 );
816 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END IF;
819 END IF;
820 FETCH c_filter_tabs INTO l_tab,l_level_view_name,l_dim_level_id;
821 END LOOP;
822 CLOSE c_filter_tabs;
823 END IF;
824 END IF;
825 END LOOP;
826 END IF;
827
828 EXCEPTION
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 IF (x_msg_data IS NULL) THEN
831 FND_MSG_PUB.Count_And_Get
832 ( p_encoded => FND_API.G_FALSE
833 , p_count => x_msg_count
834 , p_data => x_msg_data
835 );
836 END IF;
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
839 WHEN NO_DATA_FOUND THEN
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 IF (x_msg_data IS NOT NULL) THEN
842 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_filter_views ';
843 ELSE
844 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_filter_views ';
845 END IF;
846 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
847 WHEN OTHERS THEN
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 IF (x_msg_data IS NOT NULL) THEN
850 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_filter_views ';
851 ELSE
852 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_filter_views ';
853 END IF;
854 END Validate_filter_views;
855
856 /********************************************************************
857 PROCEDURE : Validate_Filter_Button
858 DESCRIPTION : This procedure does the filter button validations.
859 It is divided into 3 parts.
860 1.Previously there was no relationship and now relationship exists.
861 2.Previously relationship exists and now no relationship.
862 3.Changed/Unchanged relationships.
863 4.Relationship got reversed.
864 INPUT : p_dim_obj_id : Current dimesnion object whose relationship is being
865 effected.
866 x_new_rel_Table : Cache which stores the new relationship corresponding
867 to current dimension object.
868 x_prev_rel_Table : Cache which stores the old relationship for the current
869 dimension object.
870 OUPUT : Drops the filter views based on the following conditions
871
872 Validations
873 -----------
874 1. If A and B from none relationship changes to 1-M, then keep filter of the
875 A and remove filter of B.
876
877 2. If A and B have 1-M changes to none relationship, then keep filter of A
878 and remove filter of B.
879 3. If A and B have 1-M changes to M-N, then keep filter of A and remove
880 filter for B.
881 4. If A and B have M-N changes to 1-M, then keep filter of A and remove
882 filter for B.
883
884 5. Keep filters for both dimension objects whenever removing or adding a M-N
885 relationship since they are treaten as independent:
886
887 6. If A and B have none relationship changes to M-N, then keep filter of A
888 and B, since they will continue be treaten as independent.
889 7. If A and B have M-N changes to none relationship, then keep filter of A
890 and B.
891
892 8. In the case where the relationship is reversed, meaning the parent changes
893 to be the child, remove filter for both of the dimension objects.
894
895 AUTHOR : ashankar 25-OCT-2004 BUG 3459282
896 /*******************************************************************/
897 PROCEDURE Validate_Filter_Button
898 (
899 p_dim_obj_id IN NUMBER
900 , x_new_rel_Table IN BSC_BIS_DIM_REL_PUB.Relation_Table_Type
901 , x_prev_rel_Table IN BSC_BIS_DIM_REL_PUB.Relation_Table_Type
902 , x_return_status OUT NOCOPY VARCHAR2
903 , x_msg_count OUT NOCOPY NUMBER
904 , x_msg_data OUT NOCOPY VARCHAR2
905
906 )IS
907 l_new_rel_Table BSC_BIS_DIM_REL_PUB.Relation_Table_Type;
908 l_prev_rel_Table BSC_BIS_DIM_REL_PUB.Relation_Table_Type;
909 l_dim_obj_tbls BSC_UTILITY.varchar_tabletype;
910
911
912 l_New_Count NUMBER;
913 l_Old_Count NUMBER;
914 l_Count NUMBER;
915 l_par_count NUMBER;
916 l_child_number NUMBER;
917 l_common_tabs VARCHAR2(32000);
918 l_tab VARCHAR2(30);
919 l_outer_loop NUMBER;
920 l_inner_loop NUMBER;
921 l_found_count NUMBER;
922
923 BEGIN
924 FND_MSG_PUB.Initialize;
925 x_return_status := FND_API.G_RET_STS_SUCCESS;
926 l_new_rel_Table := x_new_rel_Table;
927 l_prev_rel_Table := x_prev_rel_Table;
928
929 l_New_Count := l_new_rel_Table.COUNT;
930 l_Old_Count := l_prev_rel_Table.COUNT;
931
932 /*****************************************************************
933 Previuosly there was no relationship and now there is relationship
934 In the new relationship check if it is 1xN relationship.
935 If yes then check the current dimension object is acting as a child or parent.
936 If as child then validate if the child and parent dimension objects are used in filter views.
937 If yes then get the common tabs where they are used as filters.
938 For each tab and for the current dimension object drop the filter views.
939 Don't drop the filter view of the parent dimension object.
940 If acting as parent then do the same for each of its child.
941 /*****************************************************************/
942
943 IF((l_Old_Count=0) AND(l_New_Count<>0))THEN
944 FOR i_index IN 0..l_New_Count -1 LOOP
945 IF(l_new_rel_Table(i_index).p_Relation_Type =1) THEN
946 IF((l_new_rel_Table(i_index).p_Dim_Obj_ID=p_dim_obj_id)
947 AND (l_new_rel_Table(i_index).p_Parent_Dim_Id<> p_dim_obj_id)) THEN
948
949 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
950 (
951 p_dim_obj_id => l_new_rel_Table(i_index).p_Dim_Obj_ID
952 , p_par_dim_obj_id => l_new_rel_Table(i_index).p_Parent_Dim_Id
953 , x_return_status => x_return_status
954 , x_msg_count => x_msg_count
955 , x_msg_data => x_msg_data
956 );
957 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959 END IF;
960
961 ELSIF((l_new_rel_Table(i_index).p_Dim_Obj_ID<>p_dim_obj_id)
962 AND (l_new_rel_Table(i_index).p_Parent_Dim_Id = p_dim_obj_id)) THEN
963
964 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
965 (
966 p_dim_obj_id => l_new_rel_Table(i_index).p_Dim_Obj_ID
967 , p_par_dim_obj_id => l_new_rel_Table(i_index).p_Parent_Dim_Id
968 , x_return_status => x_return_status
969 , x_msg_count => x_msg_count
970 , x_msg_data => x_msg_data
971 );
972 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975 END IF;
976 END IF;
977 END LOOP;
978 ELSIF((l_Old_Count<>0) AND(l_New_Count=0)) THEN
979 /*****************************************************************
980 Previuosly there was no relationship and now there is relationship
981 In the new relationship check if it is 1xN relationship.
982 If yes then check the current dimension object is acting as a child or parent.
983 If as child then validate if the child and parent dimension objects are used in filter views.
984 If yes then get the common tabs where they are used as filters.
985 For each tab and for the current dimension object drop the filter views.
986 Don't drop the filter view of the parent dimension object.
987 If acting as parent then do the same for each of its child.
988 /*****************************************************************/
989 FOR i_index IN 0..l_Old_Count -1 LOOP
990 IF(l_prev_rel_Table(i_index).p_Relation_Type =1) THEN
991 IF((l_prev_rel_Table(i_index).p_Dim_Obj_ID=p_dim_obj_id)
992 AND (l_prev_rel_Table(i_index).p_Parent_Dim_Id<> p_dim_obj_id)) THEN
993
994 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
995 (
996 p_dim_obj_id => l_prev_rel_Table(i_index).p_Dim_Obj_ID
997 , p_par_dim_obj_id => l_prev_rel_Table(i_index).p_Parent_Dim_Id
998 , x_return_status => x_return_status
999 , x_msg_count => x_msg_count
1000 , x_msg_data => x_msg_data
1001 );
1002 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 ELSIF((l_prev_rel_Table(i_index).p_Dim_Obj_ID<>p_dim_obj_id)
1007 AND (l_prev_rel_Table(i_index).p_Parent_Dim_Id =p_dim_obj_id))THEN
1008
1009 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
1010 (
1011 p_dim_obj_id => l_prev_rel_Table(i_index).p_Dim_Obj_ID
1012 , p_par_dim_obj_id => l_prev_rel_Table(i_index).p_Parent_Dim_Id
1013 , x_return_status => x_return_status
1014 , x_msg_count => x_msg_count
1015 , x_msg_data => x_msg_data
1016 );
1017 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020 END IF;
1021 END IF;
1022 END LOOP;
1023 ELSIF((l_Old_Count<>0) AND(l_New_Count<>0)) THEN
1024 /*****************************************************************
1025 Here we will find the difference in relationships between the old
1026 and new.To filter out the dimension objects we are using the following logic
1027 1.We check if the old relationship record eixts in the new Cache.
1028 If yes then we check if any change is there in the relationship.
1029 If relationship change then we flag the refresh flag in old cache to TRUE so that it can be
1030 acted upon.p_Refresh_No is the new Cache will be set to 0 to indicate that this record doesn't need to
1031 be touched.
1032 It may happen that the old record no longer exits and is no longer exists.In that case
1033 l_found_count will be set to -1.In that case too verify what the relationship type
1034 if it was set to -1 then set the refresh falg to TRUE.
1035
1036 If the relationships were revered then set the relfresh_flag to TRUE and set p_Refresh_No =1
1037 This indicates that we need to drop the filter views of both the dimension objects.
1038 /*****************************************************************/
1039
1040 FOR out_index IN 0..l_Old_Count - 1 LOOP
1041 l_found_count := -1;
1042 FOR in_index IN 0..l_New_Count - 1 LOOP
1043 IF((l_prev_rel_Table(out_index).p_Dim_Obj_ID = l_new_rel_Table(in_index).p_Dim_Obj_ID)
1044 AND ((l_prev_rel_Table(out_index).p_Parent_Dim_Id = l_new_rel_Table(in_index).p_Parent_Dim_Id))) THEN
1045 IF(l_prev_rel_Table(out_index).p_Relation_Type <> l_new_rel_Table(in_index).p_Relation_Type) THEN
1046 l_prev_rel_Table(out_index).p_Refresh_Flag := TRUE;
1047 l_new_rel_Table(in_index).p_Refresh_No := 0;
1048 l_found_count := in_index;
1049 ELSE
1050 l_new_rel_Table(in_index).p_Refresh_No := 0;
1051 END IF;
1052 ELSIF((l_prev_rel_Table(out_index).p_Dim_Obj_ID = l_new_rel_Table(in_index).p_Parent_Dim_Id)
1053 AND ((l_prev_rel_Table(out_index).p_Parent_Dim_Id = l_new_rel_Table(in_index).p_Dim_Obj_ID))
1054 AND (l_prev_rel_Table(out_index).p_Relation_Type = l_new_rel_Table(in_index).p_Relation_Type)) THEN
1055 l_prev_rel_Table(out_index).p_Refresh_Flag := TRUE;
1056 l_prev_rel_Table(out_index).p_Refresh_No := 1;
1057 END IF;
1058 END LOOP;
1059
1060 IF(l_found_count =-1) THEN
1061 IF(l_prev_rel_Table(out_index).p_Relation_Type = 1) THEN
1062 l_prev_rel_Table(out_index).p_Refresh_Flag := TRUE;
1063 END IF;
1064 END IF;
1065 END LOOP;--out_index
1066
1067 FOR j IN 0..l_prev_rel_Table.COUNT -1 LOOP
1068 IF((l_prev_rel_Table(j).p_Refresh_Flag=TRUE) AND (l_prev_rel_Table(j).p_Refresh_No =-1))THEN
1069 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
1070 (
1071 p_dim_obj_id => l_prev_rel_Table(j).p_Dim_Obj_ID
1072 , p_par_dim_obj_id => l_prev_rel_Table(j).p_Parent_Dim_Id
1073 , x_return_status => x_return_status
1074 , x_msg_count => x_msg_count
1075 , x_msg_data => x_msg_data
1076 );
1077 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1079 END IF;
1080 ELSIF((l_prev_rel_Table(j).p_Refresh_Flag=TRUE) AND (l_prev_rel_Table(j).p_Refresh_No =1)) THEN
1081
1082 BSC_DIM_FILTERS_PUB.Drop_Filter_By_Dim_Obj
1083 ( p_Dim_Level_Id => l_prev_rel_Table(j).p_Dim_Obj_ID
1084 , x_return_status => x_return_status
1085 , x_msg_Count => x_msg_Count
1086 , x_msg_data => x_msg_data
1087 );
1088 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1091
1092 BSC_DIM_FILTERS_PUB.Drop_Filter_By_Dim_Obj
1093 ( p_Dim_Level_Id => l_prev_rel_Table(j).p_Parent_Dim_Id
1094 , x_return_status => x_return_status
1095 , x_msg_Count => x_msg_Count
1096 , x_msg_data => x_msg_data
1097 );
1098 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101 END IF;
1102 END LOOP;
1103
1104 --Now for new relationships
1105
1106 /*****************************************************************
1107 We have to take into account the new relationships which were not
1108 there in the old Cache.If p_Refresh_No is set to 0 it means
1109 the no change in the relationships.
1110 We have to take into account p_Refresh_No =-1 (it means new relatiosnhip
1111 for the current dimension object) and p_Relation_Type =1
1112 /****************************************************************/
1113
1114 FOR i IN 0..l_new_rel_Table.COUNT -1 LOOP
1115 IF((l_new_rel_Table(i).p_Refresh_No = -1) AND (l_new_rel_Table(i).p_Relation_Type=1)) THEN
1116 BSC_BIS_DIM_REL_PUB.Validate_Filtered_Tabs
1117 (
1118 p_dim_obj_id => l_new_rel_Table(i).p_Dim_Obj_ID
1119 , p_par_dim_obj_id => l_new_rel_Table(i).p_Parent_Dim_Id
1120 , x_return_status => x_return_status
1121 , x_msg_count => x_msg_count
1122 , x_msg_data => x_msg_data
1123 );
1124 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127 END IF;
1128 END LOOP;
1129 END IF;
1130 /************************************************************
1131 This procedure is added to ensure that filter views are
1132 invalidated when relationship type is changed. If yes then
1133 it will recreate the filter views.
1134 /***********************************************************/
1135
1136 BSC_BIS_DIM_REL_PUB.Validate_filter_views
1137 (
1138 p_dim_obj_id => p_dim_obj_id
1139 , x_new_rel_Table => l_new_rel_Table
1140 , x_prev_rel_Table => l_prev_rel_Table
1141 , x_return_status => x_return_status
1142 , x_msg_count => x_msg_count
1143 , x_msg_data => x_msg_data
1144 );
1145 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1151 IF (x_msg_data IS NULL) THEN
1152 FND_MSG_PUB.Count_And_Get
1153 ( p_encoded => FND_API.G_FALSE
1154 , p_count => x_msg_count
1155 , p_data => x_msg_data
1156 );
1157 END IF;
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1160 WHEN NO_DATA_FOUND THEN
1161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1162 IF (x_msg_data IS NOT NULL) THEN
1163 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_Filter_Button ';
1164 ELSE
1165 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_Filter_Button ';
1166 END IF;
1167 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1168 WHEN OTHERS THEN
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 IF (x_msg_data IS NOT NULL) THEN
1171 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Validate_Filter_Button ';
1172 ELSE
1173 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Validate_Filter_Button ';
1174 END IF;
1175 END Validate_Filter_Button;
1176
1177 --=====================================================================================*/
1178
1179 FUNCTION get_Next_Alias
1180 (
1181 p_Alias IN VARCHAR2
1182 ) RETURN VARCHAR2
1183 IS
1184 l_alias VARCHAR2(3);
1185 l_return VARCHAR2(3);
1186 l_count NUMBER;
1187 BEGIN
1188 IF (p_Alias IS NULL) THEN
1189 l_return := 'A';
1190 ELSE
1191 l_count := LENGTH(p_Alias);
1192 IF (l_count = 1) THEN
1193 l_return := 'A0';
1194 ELSIF (l_count > 1) THEN
1195 l_alias := SUBSTR(p_Alias, 2);
1196 l_count := TO_NUMBER(l_alias)+1;
1197 l_return := SUBSTR(p_Alias, 1, 1)||TO_CHAR(l_count);
1198 END IF;
1199 END IF;
1200 RETURN l_return;
1201 END get_Next_Alias;
1202
1203 --==============================================================
1204 PROCEDURE Assign_Dim_Obj_Rels
1205 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1206 , p_dim_obj_id IN NUMBER
1207 , p_parent_ids IN VARCHAR2
1208 , p_parent_rel_type IN VARCHAR2
1209 , p_parent_rel_column IN VARCHAR2
1210 , p_parent_data_type IN VARCHAR2
1211 , p_parent_data_source IN VARCHAR2
1212 , p_child_ids IN VARCHAR2
1213 , p_child_rel_type IN VARCHAR2
1214 , p_child_rel_column IN VARCHAR2
1215 , p_child_data_type IN VARCHAR2
1216 , p_child_data_source IN VARCHAR2
1217 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
1218 , x_return_status OUT NOCOPY VARCHAR2
1219 , x_msg_count OUT NOCOPY NUMBER
1220 , x_msg_data OUT NOCOPY VARCHAR2
1221 ) IS
1222 l_parent_ids VARCHAR2(32000);
1223 l_parent_rel_type VARCHAR2(32000);
1224 l_parent_rel_column VARCHAR2(32000);
1225 l_parent_data_type VARCHAR2(32000);
1226 l_parent_data_source VARCHAR2(32000);
1227 l_child_ids VARCHAR2(32000);
1228 l_child_rel_type VARCHAR2(32000);
1229 l_child_rel_column VARCHAR2(32000);
1230 l_child_data_type VARCHAR2(32000);
1231 l_child_data_source VARCHAR2(32000);
1232
1233 CURSOR c_parent_ids IS
1234 SELECT parent_dim_level_id
1235 , relation_type
1236 , relation_col
1237 , data_source_type
1238 , data_source
1239 FROM BSC_SYS_DIM_LEVEL_RELS
1240 WHERE dim_level_id = p_dim_obj_id;
1241
1242 CURSOR c_childs_ids IS
1243 SELECT dim_level_id
1244 , relation_type
1245 , relation_col
1246 , data_source_type
1247 , data_source
1248 FROM BSC_SYS_DIM_LEVEL_RELS
1249 WHERE parent_dim_level_id = p_dim_obj_id
1250 AND relation_type = 1;
1251 BEGIN
1252 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels Procedure');
1253 FND_MSG_PUB.Initialize;
1254 x_return_status := FND_API.G_RET_STS_SUCCESS;
1255 IF (p_dim_obj_id IS NULL) THEN
1256 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1257 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
1258 FND_MSG_PUB.ADD;
1259 RAISE FND_API.G_EXC_ERROR;
1260 END IF;
1261 IF (p_parent_ids IS NOT NULL) THEN
1262 l_parent_ids := p_parent_ids;
1263 l_parent_rel_type := NVL(p_parent_rel_type, 'NULL');
1264 l_parent_rel_column := NVL(p_parent_rel_column, 'NULL');
1265 l_parent_data_type := NVL(p_parent_data_type, 'NULL');
1266 l_parent_data_source := NVL(p_parent_data_source, 'NULL');
1267 END IF;
1268 IF (p_child_ids IS NOT NULL) THEN
1269 l_child_ids := p_child_ids;
1270 l_child_rel_type := NVL(p_child_rel_type, 'NULL');
1271 l_child_rel_column := NVL(p_child_rel_column, 'NULL');
1272 l_child_data_type := NVL(p_child_data_type, 'NULL');
1273 l_child_data_source := NVL(p_child_data_source, 'NULL');
1274 END IF;
1275
1276 --added additional if condition to check for duplicates - Bug#4601099
1277 FOR cd IN c_parent_ids LOOP
1278 IF(INSTR(','||REPLACE(l_parent_ids, ' ', '')||',', ',' || cd.parent_dim_level_id ||',') = 0) THEN
1279 IF (l_parent_ids IS NULL) THEN
1280 l_parent_ids := NVL(TO_CHAR(cd.parent_dim_level_id), 'NULL');
1281 ELSE
1282 l_parent_ids := l_parent_ids||', '||NVL(TO_CHAR(cd.parent_dim_level_id), 'NULL');
1283 END IF;
1284 IF (l_parent_rel_type IS NULL) THEN
1285 l_parent_rel_type := NVL(TO_CHAR(cd.relation_type), 'NULL');
1286 ELSE
1287 l_parent_rel_type := l_parent_rel_type||', '||NVL(TO_CHAR(cd.relation_type), 'NULL');
1288 END IF;
1289 IF (l_parent_rel_column IS NULL) THEN
1290 l_parent_rel_column := NVL(cd.relation_col, 'NULL');
1291 ELSE
1292 l_parent_rel_column := l_parent_rel_column||', '||NVL(cd.relation_col, 'NULL');
1293 END IF;
1294 IF (l_parent_data_type IS NULL) THEN
1295 l_parent_data_type := NVL(cd.data_source_type, 'NULL');
1296 ELSE
1297 l_parent_data_type := l_parent_data_type||', '||NVL(cd.data_source_type, 'NULL');
1298 END IF;
1299 IF (l_parent_data_source IS NULL) THEN
1300 l_parent_data_source := NVL(cd.data_source, 'NULL');
1301 ELSE
1302 l_parent_data_source := l_parent_data_source||', '||NVL(cd.data_source, 'NULL');
1303 END IF;
1304 END IF;
1305 END LOOP;
1306 FOR cd IN c_childs_ids LOOP
1307 IF(INSTR(','||REPLACE(l_child_ids, ' ', '')||',', ',' || cd.dim_level_id ||',') = 0) THEN
1308 IF (l_child_ids IS NULL) THEN
1309 l_child_ids := NVL(TO_CHAR(cd.dim_level_id), 'NULL');
1310 ELSE
1311 l_child_ids := l_child_ids||', '||NVL(TO_CHAR(cd.dim_level_id), 'NULL');
1312 END IF;
1313 IF (l_child_rel_type IS NULL) THEN
1314 l_child_rel_type := NVL(TO_CHAR(cd.relation_type), 'NULL');
1315 ELSE
1316 l_child_rel_type := l_child_rel_type||', '||NVL(TO_CHAR(cd.relation_type), 'NULL');
1317 END IF;
1318 IF (l_child_rel_column IS NULL) THEN
1319 l_child_rel_column := NVL(cd.relation_col, 'NULL');
1320 ELSE
1321 l_child_rel_column := l_child_rel_column||', '||NVL(cd.relation_col, 'NULL');
1322 END IF;
1323 IF (l_child_data_type IS NULL) THEN
1324 l_child_data_type := NVL(cd.data_source_type, 'NULL');
1325 ELSE
1326 l_child_data_type := l_child_data_type||', '||NVL(cd.data_source_type, 'NULL');
1327 END IF;
1328 IF (l_child_data_source IS NULL) THEN
1329 l_child_data_source := NVL(cd.data_source, 'NULL');
1330 ELSE
1331 l_child_data_source := l_child_data_source||', '||NVL(cd.data_source, 'NULL');
1332 END IF;
1333 END IF;
1334 END LOOP;
1335
1336 --DBMS_OUTPUT.PUT_LINE(' BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels');
1337 --DBMS_OUTPUT.PUT_LINE(' (');
1338 --DBMS_OUTPUT.PUT_LINE(' p_commit => FND_API.G_FALSE');
1339 --DBMS_OUTPUT.PUT_LINE(' , p_dim_obj_id => '||p_dim_obj_id);
1340 --DBMS_OUTPUT.PUT_LINE(' , p_parent_ids => '''||l_parent_ids||''' ');
1341 --DBMS_OUTPUT.PUT_LINE(' , p_parent_rel_type => '''||l_parent_rel_type||'''');
1342 --DBMS_OUTPUT.PUT_LINE(' , p_parent_rel_column => '''||l_parent_rel_column||'''');
1343 --DBMS_OUTPUT.PUT_LINE(' , p_parent_data_type => '''||l_parent_data_type||'''');
1344 --DBMS_OUTPUT.PUT_LINE(' , p_parent_data_source => '''||l_parent_data_source||'''');
1345 --DBMS_OUTPUT.PUT_LINE(' , p_child_ids => '''||l_child_ids||'''');
1346 --DBMS_OUTPUT.PUT_LINE(' , p_child_rel_type => '''||l_child_rel_type||'''');
1347 --DBMS_OUTPUT.PUT_LINE(' , p_child_rel_column => '''||l_child_rel_column||'''');
1348 --DBMS_OUTPUT.PUT_LINE(' , p_child_data_type => '''||l_child_data_type||'''');
1349 --DBMS_OUTPUT.PUT_LINE(' , p_child_data_source => '''||l_child_data_source||'''');
1350 --DBMS_OUTPUT.PUT_LINE(' , x_return_status => l_return_status');
1351 --DBMS_OUTPUT.PUT_LINE(' , x_msg_count => l_msg_count');
1352 --DBMS_OUTPUT.PUT_LINE(' , x_msg_data => l_msg_data');
1353 --DBMS_OUTPUT.PUT_LINE(' );');
1354 BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels
1355 ( p_commit => FND_API.G_FALSE
1356 , p_dim_obj_id => p_dim_obj_id
1357 , p_parent_ids => l_parent_ids
1358 , p_parent_rel_type => l_parent_rel_type
1359 , p_parent_rel_column => l_parent_rel_column
1360 , p_parent_data_type => l_parent_data_type
1361 , p_parent_data_source => l_parent_data_source
1362 , p_child_ids => l_child_ids
1363 , p_child_rel_type => l_child_rel_type
1364 , p_child_rel_column => l_child_rel_column
1365 , p_child_data_type => l_child_data_type
1366 , p_child_data_source => l_child_data_source
1367 , p_time_stamp => p_time_stamp -- Granular Locking
1368 , x_return_status => x_return_status
1369 , x_msg_count => x_msg_count
1370 , x_msg_data => x_msg_data
1371 );
1372 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1373 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels');
1374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1375 END IF;
1376 IF (p_commit = FND_API.G_TRUE) THEN
1377 COMMIT;
1378 --DBMS_OUTPUT.PUT_LINE('COMMIT SUCCESSFUL');
1379 END IF;
1380 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels Procedure');
1381 EXCEPTION
1382 WHEN FND_API.G_EXC_ERROR THEN
1383 IF (x_msg_data IS NULL) THEN
1384 FND_MSG_PUB.Count_And_Get
1385 ( p_encoded => FND_API.G_FALSE
1386 , p_count => x_msg_count
1387 , p_data => x_msg_data
1388 );
1389 END IF;
1390 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1391 x_return_status := FND_API.G_RET_STS_ERROR;
1392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1393 IF (x_msg_data IS NULL) THEN
1394 FND_MSG_PUB.Count_And_Get
1395 ( p_encoded => FND_API.G_FALSE
1396 , p_count => x_msg_count
1397 , p_data => x_msg_data
1398 );
1399 END IF;
1400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1401 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1402 WHEN NO_DATA_FOUND THEN
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 IF (x_msg_data IS NOT NULL) THEN
1405 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels ';
1406 ELSE
1407 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels ';
1408 END IF;
1409 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1410 WHEN OTHERS THEN
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1412 IF (x_msg_data IS NOT NULL) THEN
1413 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels ';
1414 ELSE
1415 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Assign_Dim_Obj_Rels ';
1416 END IF;
1417 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1418 END Assign_Dim_Obj_Rels;
1419 /*********************************************************************************
1420 ASSIGN DIMENSION-LEVELS RELATIONSHIPS
1421 *********************************************************************************/
1422 /*
1423 This procedure allow user to assign dimension object relationships whose records
1424 will be inserted into the following table.
1425 1. BSC_SYS_DIM_LEVEL_RELS
1426 The procedure will remove all the older relationships before assigning new
1427 relationships.
1428
1429 Validations:
1430 1. Source must be same either BSC or PMF.
1431 2. Circularity check must be there.
1432 3. p_dim_obj_id must not be null.
1433 */
1434 PROCEDURE Assign_New_Dim_Obj_Rels
1435 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1436 , p_dim_obj_id IN NUMBER
1437 , p_parent_ids IN VARCHAR2
1438 , p_parent_rel_type IN VARCHAR2
1439 , p_parent_rel_column IN VARCHAR2
1440 , p_parent_data_type IN VARCHAR2
1441 , p_parent_data_source IN VARCHAR2
1442 , p_child_ids IN VARCHAR2
1443 , p_child_rel_type IN VARCHAR2
1444 , p_child_rel_column IN VARCHAR2
1445 , p_child_data_type IN VARCHAR2
1446 , p_child_data_source IN VARCHAR2
1447 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
1448 , p_is_not_config IN BOOLEAN := TRUE
1449 , x_return_status OUT NOCOPY VARCHAR2
1450 , x_msg_count OUT NOCOPY NUMBER
1451 , x_msg_data OUT NOCOPY VARCHAR2
1452 ) IS
1453 l_One_N_Table BSC_BIS_DIM_REL_PUB.One_To_N_Org_Table_Type;
1454 l_M_N_Table BSC_BIS_DIM_REL_PUB.M_To_N_Org_Table_Type;
1455
1456 l_prev_rel_Table BSC_BIS_DIM_REL_PUB.Relation_Table_Type;
1457 l_new_rel_Table BSC_BIS_DIM_REL_PUB.Relation_Table_Type;
1458
1459 l_bsc_dim_obj_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1460
1461 l_count NUMBER;
1462 l_flag BOOLEAN := TRUE;
1463 l_source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
1464
1465 l_child_ids VARCHAR2(32000);
1466 l_child_rel_type VARCHAR2(32000);
1467 l_child_rel_column VARCHAR2(32000);
1468 l_child_data_type VARCHAR2(32000);
1469 l_child_data_source VARCHAR2(32000);
1470
1471 l_refresh_kpi_ids VARCHAR2(32000);
1472
1473 l_parent_ids VARCHAR2(32000);
1474 l_parent_rel_type VARCHAR2(32000);
1475 l_parent_rel_column VARCHAR2(32000);
1476 l_parent_data_type VARCHAR2(32000);
1477 l_parent_data_source VARCHAR2(32000);
1478
1479 l_dim_obj_id VARCHAR2(200);
1480
1481 -- Start Granular Locking added by Aditya
1482 lg_Dim_Obj_Tab_p BSC_BIS_LOCKS_PUB.t_numberTable;
1483 lg_Dim_Obj_Tab_c BSC_BIS_LOCKS_PUB.t_numberTable;
1484 lg_dim_obj_ids VARCHAR2(32000);
1485
1486 lg_dim_obj_id VARCHAR2(30);
1487 lg_index NUMBER := 0;
1488 -- End Granular Locking added by Aditya
1489
1490 l_db_child_rel_type NUMBER;
1491 l_rel_ids VARCHAR2(32000);
1492 l_rel_id VARCHAR2(10);
1493 l_dim_obj_sname VARCHAR2(30);
1494 l_dim_obj_name VARCHAR2(400);
1495 l_is_denorm_deleted VARCHAR(1);
1496 l_dim_short_name VARCHAR2(30);
1497 l_Sql VARCHAR2(8000);
1498
1499 l_original_child_ids VARCHAR2(32000);
1500
1501
1502
1503 CURSOR c_par_dim_ids IS
1504 SELECT parent_dim_level_id
1505 , relation_type
1506 , dim_level_id
1507 FROM BSC_SYS_DIM_LEVEL_RELS
1508 WHERE dim_level_id = l_dim_obj_id;
1509
1510 CURSOR c_child_ids IS
1511 SELECT dim_level_id
1512 FROM BSC_SYS_DIM_LEVEL_RELS
1513 WHERE parent_dim_level_id = p_dim_obj_id
1514 AND relation_type <> 2;
1515
1516
1517 CURSOR c_Kpi_Dim_Set IS
1518 SELECT DISTINCT A.INDICATOR Indicator,
1519 A.DIM_SET_ID Dim_Set_Id,
1520 C.short_name
1521 FROM BSC_KPI_DIM_LEVELS_VL A,
1522 BSC_SYS_DIM_LEVELS_VL B,
1523 BSC_KPIS_B C
1524 WHERE A.LEVEL_TABLE_NAME=B.LEVEL_TABLE_NAME
1525 AND C.INDICATOR = A.INDICATOR
1526 AND C.SHARE_FLAG <> 2
1527 AND INSTR(l_Refresh_Kpi_Ids, ','||b.dim_level_id||',') > 0;
1528
1529 CURSOR c_new_relations IS
1530 SELECT DISTINCT Dim_Level_Id
1531 FROM BSC_SYS_DIM_LEVEL_RELS
1532 WHERE (Parent_Dim_Level_Id = p_dim_obj_id
1533 OR Dim_Level_Id = p_dim_obj_id);
1534
1535 -- added cursor for Bug #3395161
1536 CURSOR c_db_child_type IS
1537 SELECT Relation_Type
1538 FROM BSC_SYS_DIM_LEVEL_RELS
1539 WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id
1540 AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id;
1541
1542 l_one_N_flag BOOLEAN;
1543 l_One_N_Count NUMBER := 0;
1544 l_Num_One_N_Count NUMBER := 0;
1545 l_M_N_Count NUMBER := 0;
1546
1547 -- added for Bug#4601099
1548 l_Is_PMF_Recur_Type BOOLEAN;
1549
1550 BEGIN
1551 SAVEPOINT AssUnassBSCRelsPMD;
1552 FND_MSG_PUB.Initialize;
1553 x_return_status := FND_API.G_RET_STS_SUCCESS;
1554 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Procedure');
1555 --DBMS_OUTPUT.PUT_LINE('p_dim_obj_id '||p_dim_obj_id);
1556 --DBMS_OUTPUT.PUT_LINE('p_parent_ids '||p_parent_ids);
1557 --DBMS_OUTPUT.PUT_LINE('p_parent_rel_type '||p_parent_rel_type);
1558 --DBMS_OUTPUT.PUT_LINE('p_parent_rel_column '||p_parent_rel_column);
1559 --DBMS_OUTPUT.PUT_LINE('p_parent_data_type '||p_parent_data_type);
1560 --DBMS_OUTPUT.PUT_LINE('p_parent_data_source '||p_parent_data_source);
1561 --DBMS_OUTPUT.PUT_LINE('p_child_ids '||p_child_ids);
1562 --DBMS_OUTPUT.PUT_LINE('p_child_rel_type '||p_child_rel_type);
1563 --DBMS_OUTPUT.PUT_LINE('p_child_rel_column '||p_child_rel_column);
1564 --DBMS_OUTPUT.PUT_LINE('p_child_data_type '||p_child_data_type);
1565 --DBMS_OUTPUT.PUT_LINE('p_child_data_source '||p_child_data_source);
1566 IF (p_dim_obj_id IS NULL) THEN
1567 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1568 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
1569 FND_MSG_PUB.ADD;
1570 RAISE FND_API.G_EXC_ERROR;
1571 END IF;
1572 SELECT COUNT(*) INTO l_count
1573 FROM BSC_SYS_DIM_LEVELS_B
1574 WHERE dim_level_id = p_dim_obj_id;
1575 IF (l_count = 0) THEN
1576 FND_MESSAGE.SET_NAME('BSC','BSC_INCORRECT_NAME_ENTERED');
1577 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
1578 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_dim_obj_id);
1579 FND_MSG_PUB.ADD;
1580 RAISE FND_API.G_EXC_ERROR;
1581 END IF;
1582
1583 -- Restrict Period Dim Object from relationship.
1584 IF (p_parent_ids IS NOT NULL) THEN
1585 l_rel_ids := p_parent_ids;
1586 WHILE (is_more( p_dim_lev_ids => l_rel_ids
1587 , p_dim_lev_id => l_rel_id
1588 )) LOOP
1589 SELECT NAME, SHORT_NAME
1590 INTO l_dim_obj_name, l_dim_obj_sname
1591 FROM BSC_SYS_DIM_LEVELS_VL
1592 WHERE DIM_LEVEL_ID = l_rel_id;
1593
1594 BSC_UTILITY.Enable_Dimension_Entity(
1595 p_Entity_Type => BSC_UTILITY.c_DIMENSION_OBJECT
1596 , p_Entity_Short_Name => l_dim_obj_sname
1597 , p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
1598 , p_Entity_Name => l_dim_obj_name
1599 , x_Return_Status => x_return_status
1600 , x_Msg_Count => x_msg_count
1601 , x_Msg_Data => x_msg_data
1602 );
1603 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1605 END IF;
1606 END LOOP;
1607 END IF;
1608 IF (p_child_ids IS NOT NULL) THEN
1609 l_rel_ids := p_child_ids;
1610 WHILE (is_more( p_dim_lev_ids => l_rel_ids
1611 , p_dim_lev_id => l_rel_id
1612 )) LOOP
1613 SELECT NAME, SHORT_NAME
1614 INTO l_dim_obj_name, l_dim_obj_sname
1615 FROM BSC_SYS_DIM_LEVELS_VL
1616 WHERE DIM_LEVEL_ID = l_rel_id;
1617
1618 BSC_UTILITY.Enable_Dimension_Entity(
1619 p_Entity_Type => BSC_UTILITY.c_DIMENSION_OBJECT
1620 , p_Entity_Short_Name => l_dim_obj_sname
1621 , p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
1622 , p_Entity_Name => l_dim_obj_name
1623 , x_Return_Status => x_return_status
1624 , x_Msg_Count => x_msg_count
1625 , x_Msg_Data => x_msg_data
1626 );
1627 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1629 END IF;
1630 END LOOP;
1631 END IF;
1632
1633 SELECT NVL(source, 'BSC')
1634 , short_name
1635 INTO l_bsc_dim_obj_rec.bsc_parent_level_source
1636 , l_bsc_dim_obj_rec.bsc_parent_level_short_name
1637 FROM BSC_SYS_DIM_LEVELS_B
1638 WHERE dim_level_id = p_dim_obj_id;
1639 l_source := l_bsc_dim_obj_rec.bsc_parent_level_source;
1640 -- START: Granular Locking
1641 IF(p_is_not_config) THEN
1642 IF (l_child_ids IS NOT NULL) THEN
1643 lg_dim_obj_ids := l_child_ids;
1644
1645 WHILE (is_more( p_dim_lev_ids => lg_dim_obj_ids
1646 , p_dim_lev_id => lg_dim_obj_id)
1647 ) LOOP
1648 lg_Dim_Obj_Tab_c(lg_index) := NVL(TO_NUMBER(lg_dim_obj_id), -1);
1649 lg_index := lg_index + 1;
1650 END LOOP;
1651 END IF;
1652 IF (l_parent_ids IS NOT NULL) THEN
1653 lg_dim_obj_ids := l_parent_ids;
1654 lg_index := 0; -- Initialize the index to 0, since we have to pass two
1655 -- separate table params to the Locking Procedure.
1656 WHILE (is_more( p_dim_lev_ids => lg_dim_obj_ids
1657 , p_dim_lev_id => lg_dim_obj_id)
1658 ) LOOP
1659 lg_Dim_Obj_Tab_p(lg_index) := NVL(TO_NUMBER(lg_dim_obj_id), -1);
1660 lg_index := lg_index + 1;
1661 END LOOP;
1662 END IF;
1663 -- Lock all the Parent/Children and The Dimension Level affected.
1664 BSC_BIS_LOCKS_PUB.LOCK_UPDATE_RELATIONSHIPS
1665 ( p_dim_object_id => p_dim_obj_id
1666 , p_selected_parends => lg_Dim_Obj_Tab_p
1667 , p_selected_childs => lg_Dim_Obj_Tab_c
1668 , p_time_stamp => p_time_stamp
1669 , x_return_status => x_return_status
1670 , x_msg_count => x_msg_count
1671 , x_msg_data => x_msg_data
1672 );
1673 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675 END IF;
1676 END IF;
1677
1678 -- END: Granular Locking
1679 --find out all the initial childs first
1680 --for relation type 2, which are not needed
1681 --if dimension objects are of type 'BSC'
1682 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_BIS_DIM_REL_PUB.get_Original_Relations');
1683 BSC_BIS_DIM_REL_PUB.get_Original_Relations
1684 ( p_dim_obj_id => p_dim_obj_id
1685 , x_One_N_Table => l_One_N_Table
1686 , x_M_N_Table => l_M_N_Table
1687 , x_return_status => x_return_status
1688 , x_msg_count => x_msg_count
1689 , x_msg_data => x_msg_data
1690 );
1691 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1692 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.get_Original_Relations Failed: at BSC_BIS_DIM_REL_PUB.get_Original_Relations <'||x_msg_data||'>');
1693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1694 END IF;
1695
1696 -- Added for Bug#5300060
1697 l_original_child_ids := Get_Original_Child_Ids(p_dim_obj_id);
1698
1699 /****************************************************
1700 Store the original relationships for the filter view validation
1701 /****************************************************/
1702 IF((p_is_not_config) AND (l_source = 'BSC'))THEN
1703
1704 BSC_BIS_DIM_REL_PUB.store_Relations
1705 ( p_dim_obj_id => p_dim_obj_id
1706 , x_rel_Table => l_prev_rel_Table
1707 , x_return_status => x_return_status
1708 , x_msg_count => x_msg_count
1709 , x_msg_data => x_msg_data
1710 );
1711 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1712 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.store_Prev_Relations <'||x_msg_data||'>');
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714 END IF;
1715 END IF;
1716
1717 --DBMS_OUTPUT.PUT_LINE('AFTER BSC_BIS_DIM_REL_PUB.get_Original_Relations');
1718 --DBMS_OUTPUT.PUT_LINE(' --- INITIAL TABLE ----');
1719 --DBMS_OUTPUT.PUT_LINE('PRINT OUT OF THE TABLES THAT WE HAVE GOT 1 x N RELATIONS');
1720 /*FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
1721 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_dim_obj_id '||l_One_N_Table(i).p_dim_obj_id);
1722 IF (l_One_N_Table(i).p_refresh_flag) THEN
1723 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_refresh_flag TRUE');
1724 ELSE
1725 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_refresh_flag FALSE');
1726 END IF;
1727 END LOOP;
1728 --DBMS_OUTPUT.PUT_LINE('PRINT OUT OF THE TABLES THAT WE HAVE GOT M x N RELATIONS');
1729 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
1730 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_dim_obj_id '||l_M_N_Table(i).p_dim_obj_id);
1731 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_Parent_dim_id '||l_M_N_Table(i).p_Parent_dim_id);
1732 IF (l_M_N_Table(i).p_refresh_flag) THEN
1733 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_refresh_flag TRUE');
1734 ELSE
1735 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_refresh_flag FALSE');
1736 END IF;
1737 END LOOP;*/
1738
1739 -- START: Granular Locking
1740 -- The following statement direcly removes all the parents & children
1741 -- in the relationship. So for the time being, we need to implement
1742 -- granular locking to lock all the dimension levels that are going
1743 -- to be deleted. This will be removed once the DML statement is
1744 -- removed.
1745 -- checking for configuration flag
1746 IF(p_is_not_config) THEN
1747 --DBMS_OUTPUT.PUT_LINE('WRONGLY ENTERED');
1748 IF (l_source = 'BSC') THEN
1749 FOR cd IN c_child_ids LOOP
1750 --contains all the initial childs
1751 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL
1752 ( p_dim_level_id => cd.dim_level_id
1753 , p_time_stamp => NULL
1754 , x_return_status => x_return_status
1755 , x_msg_count => x_msg_count
1756 , x_msg_data => x_msg_data
1757 );
1758 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761 END LOOP;
1762 l_dim_obj_id := p_dim_obj_id;
1763 FOR pd IN c_par_dim_ids LOOP
1764 --contains all the initial parents
1765 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL
1766 ( p_dim_level_id => pd.parent_dim_level_id
1767 , p_time_stamp => NULL
1768 , x_return_status => x_return_status
1769 , x_msg_count => x_msg_count
1770 , x_msg_data => x_msg_data
1771 );
1772 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1774 END IF;
1775 END LOOP;
1776 END IF;
1777 END IF;
1778 -- END: Granular Locking
1779 --delete all the existing parents and childs first
1780 --IN future replace the delete SQL to call the existing APIs
1781 --to delete
1782 --DBMS_OUTPUT.PUT_LINE('BEFOR DELETE QUERY');
1783 DELETE FROM BSC_SYS_DIM_LEVEL_RELS
1784 WHERE dim_level_id = p_dim_obj_id
1785 OR parent_dim_level_id = p_dim_obj_id;
1786 l_child_ids := TRIM(p_child_ids);
1787 l_child_rel_type := TRIM(p_child_rel_type);
1788 l_child_rel_column := TRIM(p_child_rel_column);
1789 l_child_data_type := TRIM(p_child_data_type);
1790 l_child_data_source := TRIM(p_child_data_source);
1791 --DBMS_OUTPUT.PUT_LINE('Assigning Relations I '||l_child_ids);
1792 --DBMS_OUTPUT.PUT_LINE('BEFOR CHILD IDS');
1793 IF (l_child_ids IS NOT NULL) THEN
1794 WHILE (is_more( x_remain_id => l_child_ids
1795 , x_remain_rel_type => l_child_rel_type
1796 , x_remain_rel_column => l_child_rel_column
1797 , x_remain_data_type => l_child_data_type
1798 , x_remain_data_source => l_child_data_source
1799 , x_id => l_bsc_dim_obj_rec.bsc_level_id
1800 , x_rel_type => l_bsc_dim_obj_rec.bsc_relation_type
1801 , x_rel_column => l_bsc_dim_obj_rec.bsc_relation_column
1802 , x_data_type => l_bsc_dim_obj_rec.Bsc_Data_Source_Type
1803 , x_data_source => l_bsc_dim_obj_rec.Bsc_Data_Source
1804 )) LOOP
1805 l_bsc_dim_obj_rec.bsc_parent_level_id := p_dim_obj_id;
1806 SELECT NVL(source, 'BSC')
1807 , short_name
1808 INTO l_bsc_dim_obj_rec.Bsc_Source,
1809 l_bsc_dim_obj_rec.bsc_level_short_name
1810 FROM BSC_SYS_DIM_LEVELS_B
1811 WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
1812 IF ((l_bsc_dim_obj_rec.bsc_relation_type IS NULL) OR
1813 (l_bsc_dim_obj_rec.bsc_relation_type <> 1) AND (l_bsc_dim_obj_rec.bsc_relation_type <> 2)) THEN
1814 l_bsc_dim_obj_rec.bsc_relation_type := 1;
1815 END IF;
1816 IF (l_bsc_dim_obj_rec.bsc_parent_level_source <> l_bsc_dim_obj_rec.Bsc_Source) THEN
1817 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELS_SOURCE');
1818 FND_MESSAGE.SET_TOKEN('DIM_OBJ1', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.Bsc_Level_Id));
1819 FND_MESSAGE.SET_TOKEN('DIM_OBJ2', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.bsc_parent_level_id));
1820 FND_MSG_PUB.ADD;
1821 RAISE FND_API.G_EXC_ERROR;
1822 --DBMS_OUTPUT.PUT_LINE('ERROR FOR DELETE QUERY');
1823 END IF;
1824 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_level_id <'||l_bsc_dim_obj_rec.bsc_level_id);
1825 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_parent_level_id <'||l_bsc_dim_obj_rec.bsc_parent_level_id);
1826 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_relation_type <'||l_bsc_dim_obj_rec.bsc_relation_type);
1827 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_relation_column <'||l_bsc_dim_obj_rec.bsc_relation_column);
1828 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.Bsc_Data_Source_Type <'||l_bsc_dim_obj_rec.Bsc_Data_Source_Type);
1829 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.Bsc_Data_Source <'||l_bsc_dim_obj_rec.Bsc_Data_Source);
1830 IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1831 IF((l_bsc_dim_obj_rec.Bsc_Data_Source_Type IS NULL) OR
1832 ((l_bsc_dim_obj_rec.Bsc_Data_Source_Type <> 'TABLE') AND
1833 (l_bsc_dim_obj_rec.Bsc_Data_Source_Type <> 'API'))) THEN
1834 --need more clarifications what value should bo here
1835 l_bsc_dim_obj_rec.Bsc_Data_Source_Type := NULL;
1836 END IF;
1837 l_bsc_dim_obj_rec.bsc_relation_type := 1; --for PMF valid relationship is type 1
1838 END IF;
1839
1840 -- moved below the above IF condition for Bug#4619393
1841 IF (l_bsc_dim_obj_rec.bsc_relation_type = 2) THEN
1842 --for realtion type 2, pass this value as null, it will be generated internally
1843 l_bsc_dim_obj_rec.bsc_relation_column := NULL;
1844 END IF;
1845
1846 IF ((l_bsc_dim_obj_rec.bsc_relation_type = 2) AND
1847 (l_bsc_dim_obj_rec.Bsc_Level_Id = l_bsc_dim_obj_rec.bsc_parent_level_id)) THEN
1848 FND_MESSAGE.SET_NAME('BSC','BSC_SAME_DIM_LEVEL_REL');
1849 FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.Bsc_Level_Id));
1850 FND_MSG_PUB.ADD;
1851 RAISE FND_API.G_EXC_ERROR;
1852 --DBMS_OUTPUT.PUT_LINE('ERROR FOR DELETE QUERY');
1853 END IF;
1854 SELECT COUNT(*) INTO l_count
1855 FROM BSC_SYS_DIM_LEVEL_RELS
1856 WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id
1857 AND parent_dim_level_id = l_bsc_dim_obj_rec.bsc_parent_level_id;
1858 IF (l_count = 0) THEN
1859 IF (l_bsc_dim_obj_rec.bsc_relation_type = 1) THEN
1860 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_BIS_DIM_REL_PUB.Is_Valid_Relationship');
1861 l_flag := BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship
1862 ( p_commit => FND_API.G_FALSE
1863 , p_Dim_Level_Rec => l_bsc_dim_obj_rec
1864 , x_return_status => x_return_status
1865 , x_msg_count => x_msg_count
1866 , x_msg_data => x_msg_data
1867 );
1868 IF (NOT l_flag) THEN
1869 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship.');
1870 --DBMS_OUTPUT.PUT_LINE(SUBSTR(x_msg_data, 1, 200));
1871 RAISE FND_API.G_EXC_ERROR;
1872 --DBMS_OUTPUT.PUT_LINE('ERROR FOR DELETE QUERY');
1873 END IF;
1874 ELSE
1875 SELECT COUNT(*) INTO l_count
1876 FROM BSC_SYS_DIM_LEVEL_RELS
1877 WHERE dim_level_id = l_bsc_dim_obj_rec.bsc_parent_level_id
1878 AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
1879 IF (l_count <> 0) THEN
1880 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELATIONSHIPS');
1881 FND_MSG_PUB.ADD;
1882 RAISE FND_API.G_EXC_ERROR;
1883 END IF;
1884 END IF;
1885 --DBMS_OUTPUT.PUT_LINE('reached I 6');
1886 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation');
1887 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation
1888 ( p_commit => FND_API.G_FALSE
1889 , p_Dim_Level_Rec => l_bsc_dim_obj_rec
1890 , x_return_status => x_return_status
1891 , x_msg_count => x_msg_count
1892 , x_msg_data => x_msg_data
1893 );
1894 --DBMS_OUTPUT.PUT_LINE('reached I 7');
1895 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1896 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.create_dim_level_relation');
1897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1898 END IF;
1899 -- START Granluar Locking
1900 -- Change the time stamp of the Child Dimension Level
1901 IF(p_is_not_config) THEN
1902 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DIM_LEVEL
1903 ( p_dim_level_id => l_bsc_dim_obj_rec.Bsc_Level_Id
1904 , x_return_status => x_return_status
1905 , x_msg_count => x_msg_count
1906 , x_msg_data => x_msg_data
1907 );
1908 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1909 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.create_dim_level_relation');
1910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END IF;
1912 END IF;
1913 -- END Granluar Locking
1914 ELSE
1915 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELATIONSHIPS');
1916 FND_MSG_PUB.ADD;
1917 RAISE FND_API.G_EXC_ERROR;
1918 END IF;
1919 END LOOP;
1920 --DBMS_OUTPUT.PUT_LINE('reached I 9');
1921 END IF;
1922 --DBMS_OUTPUT.PUT_LINE('AFTER CHILD IDS');
1923 l_parent_ids := TRIM(p_parent_ids);
1924 l_parent_rel_type := TRIM(p_parent_rel_type);
1925 l_parent_rel_column := TRIM(p_parent_rel_column);
1926 l_parent_data_type := TRIM(p_parent_data_type);
1927 l_parent_data_source := TRIM(p_parent_data_source);
1928
1929 SELECT NVL(source, 'BSC')
1930 , short_name
1931 INTO l_bsc_dim_obj_rec.Bsc_Source,
1932 l_bsc_dim_obj_rec.bsc_level_short_name
1933 FROM BSC_SYS_DIM_LEVELS_B
1934 WHERE dim_level_id = p_dim_obj_id;
1935 --DBMS_OUTPUT.PUT_LINE('Assigning Relations II');
1936 --DBMS_OUTPUT.PUT_LINE('BEFORE PARENT IDS');
1937 IF (l_parent_ids IS NOT NULL) THEN
1938 WHILE(is_more( x_remain_id => l_parent_ids
1939 , x_remain_rel_type => l_parent_rel_type
1940 , x_remain_rel_column => l_parent_rel_column
1941 , x_remain_data_type => l_parent_data_type
1942 , x_remain_data_source => l_parent_data_source
1943 , x_id => l_bsc_dim_obj_rec.bsc_parent_level_id
1944 , x_rel_type => l_bsc_dim_obj_rec.bsc_relation_type
1945 , x_rel_column => l_bsc_dim_obj_rec.bsc_relation_column
1946 , x_data_type => l_bsc_dim_obj_rec.Bsc_Data_Source_Type
1947 , x_data_source => l_bsc_dim_obj_rec.Bsc_Data_Source
1948 )) LOOP
1949 l_bsc_dim_obj_rec.Bsc_Level_Id := p_dim_obj_id;
1950
1951 SELECT NVL(source, 'BSC')
1952 , short_name
1953 INTO l_bsc_dim_obj_rec.bsc_parent_level_source
1954 , l_bsc_dim_obj_rec.bsc_parent_level_short_name
1955 FROM BSC_SYS_DIM_LEVELS_B
1956 WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id;
1957 --DBMS_OUTPUT.PUT_LINE('PARENT IDS STAGE1');
1958
1959 IF ((l_bsc_dim_obj_rec.bsc_relation_type IS NULL) OR
1960 (l_bsc_dim_obj_rec.bsc_relation_type <> 1) AND (l_bsc_dim_obj_rec.bsc_relation_type <> 2)) THEN
1961 l_bsc_dim_obj_rec.bsc_relation_type := 1;
1962 END IF;
1963 IF(l_bsc_dim_obj_rec.bsc_parent_level_source <> l_bsc_dim_obj_rec.Bsc_Source) THEN
1964 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELS_SOURCE');
1965 FND_MESSAGE.SET_TOKEN('DIM_OBJ1', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.Bsc_Level_Id));
1966 FND_MESSAGE.SET_TOKEN('DIM_OBJ2', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.bsc_parent_level_id));
1967 FND_MSG_PUB.ADD;
1968 --DBMS_OUTPUT.PUT_LINE('PARENT IDS EXE ERROR');
1969 RAISE FND_API.G_EXC_ERROR;
1970
1971 END IF;
1972 IF (l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
1973 IF((l_bsc_dim_obj_rec.Bsc_Data_Source_Type IS NULL) OR
1974 ((l_bsc_dim_obj_rec.Bsc_Data_Source_Type <> 'TABLE') AND
1975 (l_bsc_dim_obj_rec.Bsc_Data_Source_Type <> 'API'))) THEN
1976 --need more clarifications what value should bo here
1977 l_bsc_dim_obj_rec.Bsc_Data_Source_Type := NULL;
1978 END IF;
1979 l_bsc_dim_obj_rec.bsc_relation_type := 1;
1980 END IF;
1981
1982 -- moved below the above IF condition for Bug#4619393
1983 IF (l_bsc_dim_obj_rec.bsc_relation_type = 2) THEN
1984 --for realtion type 2, pass this value as null, it will be generated internally
1985 l_bsc_dim_obj_rec.bsc_relation_column := NULL;
1986 END IF;
1987
1988 --DBMS_OUTPUT.PUT_LINE('PARENT IDS STAGE2');
1989 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_level_id <'||l_bsc_dim_obj_rec.bsc_level_id);
1990 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_parent_level_id <'||l_bsc_dim_obj_rec.bsc_parent_level_id);
1991 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_relation_type <'||l_bsc_dim_obj_rec.bsc_relation_type);
1992 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.bsc_relation_column <'||l_bsc_dim_obj_rec.bsc_relation_column);
1993 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.Bsc_Data_Source_Type <'||l_bsc_dim_obj_rec.Bsc_Data_Source_Type);
1994 --DBMS_OUTPUT.PUT_LINE('l_bsc_dim_obj_rec.Bsc_Data_Source <'||l_bsc_dim_obj_rec.Bsc_Data_Source);
1995 IF ((l_bsc_dim_obj_rec.bsc_relation_type = 2) AND
1996 (l_bsc_dim_obj_rec.Bsc_Level_Id = l_bsc_dim_obj_rec.bsc_parent_level_id)) THEN
1997 FND_MESSAGE.SET_NAME('BSC','BSC_SAME_DIM_LEVEL_REL');
1998 FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(l_bsc_dim_obj_rec.Bsc_Level_Id));
1999 FND_MSG_PUB.ADD;
2000 RAISE FND_API.G_EXC_ERROR;
2001 --DBMS_OUTPUT.PUT_LINE('PARENT IDS EXE ERROR');
2002 END IF;
2003
2004 -- Get the Dimension Object Relationship for Bug #3395161
2005 l_count := 1;
2006
2007 IF (c_db_child_type%ISOPEN) THEN
2008 CLOSE c_db_child_type;
2009 END IF;
2010
2011 OPEN c_db_child_type;
2012 FETCH c_db_child_type INTO l_db_child_rel_type;
2013 IF(c_db_child_type%NOTFOUND) THEN
2014 l_count := 0;
2015 END IF;
2016 CLOSE c_db_child_type;
2017
2018
2019 --DBMS_OUTPUT.PUT_LINE('PARENT IDS STAGE3');
2020 IF (l_count = 0) THEN
2021 IF (l_bsc_dim_obj_rec.bsc_relation_type = 1) THEN
2022 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship');
2023 l_flag := BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship
2024 ( p_commit => FND_API.G_FALSE
2025 , p_Dim_Level_Rec => l_bsc_dim_obj_rec
2026 , x_return_status => x_return_status
2027 , x_msg_count => x_msg_count
2028 , x_msg_data => x_msg_data
2029 );
2030 IF (NOT l_flag) THEN
2031 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship');
2032 RAISE FND_API.G_EXC_ERROR;
2033 --DBMS_OUTPUT.PUT_LINE('PARENT IDS EXE ERROR');
2034 END IF;
2035 ELSE
2036 SELECT COUNT(*) INTO l_count
2037 FROM BSC_SYS_DIM_LEVEL_RELS
2038 WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id
2039 AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
2040 IF (l_count <> 0) THEN
2041 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELATIONSHIPS');
2042 FND_MSG_PUB.ADD;
2043 RAISE FND_API.G_EXC_ERROR;
2044 END IF;
2045 END IF;
2046
2047 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation');
2048 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation
2049 ( p_commit => FND_API.G_FALSE
2050 , p_Dim_Level_Rec => l_bsc_dim_obj_rec
2051 , x_return_status => x_return_status
2052 , x_msg_count => x_msg_count
2053 , x_msg_data => x_msg_data
2054 );
2055 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2056 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.create_dim_level_relation');
2057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2058 END IF;
2059
2060 -- START Granluar Locking
2061 -- Change the time stamp of the Parent Dimension Level
2062 IF(p_is_not_config) THEN
2063 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DIM_LEVEL
2064 ( p_dim_level_id => l_bsc_dim_obj_rec.bsc_parent_level_id
2065 , x_return_status => x_return_status
2066 , x_msg_count => x_msg_count
2067 , x_msg_data => x_msg_data
2068 );
2069 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2070 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_DIMENSION_LEVELS_PUB.create_dim_level_relation');
2071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2072 END IF;
2073 END IF;
2074 --DBMS_OUTPUT.PUT_LINE('PARENT IDS STAGE4');
2075 -- END Granluar Locking
2076 ELSE
2077 -- Added condition to filter MxN type for parent shuttle DimObjs for Bug #3395161
2078 -- added further condition for Bug#4601099
2079 l_Is_PMF_Recur_Type := FALSE;
2080 IF ((l_bsc_dim_obj_rec.bsc_level_id = l_bsc_dim_obj_rec.bsc_parent_level_id) AND
2081 l_bsc_dim_obj_rec.Bsc_Source = 'PMF') THEN
2082 l_Is_PMF_Recur_Type := TRUE;
2083 END IF;
2084
2085 IF (NOT ((l_db_child_rel_type = 2) AND (l_bsc_dim_obj_rec.bsc_relation_type = 2)) AND l_Is_PMF_Recur_Type = FALSE)THEN
2086 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_RELATIONSHIPS');
2087 FND_MSG_PUB.ADD;
2088 RAISE FND_API.G_EXC_ERROR;
2089 END IF;
2090 END IF;
2091 END LOOP;
2092 END IF;
2093 --DBMS_OUTPUT.PUT_LINE('AFTER CHILD IDS');
2094 --get the size of the tables and put them into some local variables
2095 l_One_N_Count := l_One_N_Table.COUNT;
2096 l_M_N_Count := l_M_N_Table.COUNT;
2097 lg_index := l_M_N_Count;
2098 --DBMS_OUTPUT.PUT_LINE('l_One_N_Count '||l_One_N_Count);
2099 --DBMS_OUTPUT.PUT_LINE('l_M_N_Count '||l_M_N_Count);
2100 FOR cd IN c_new_relations LOOP
2101 l_dim_obj_id := cd.Dim_Level_Id;
2102 l_one_N_flag := TRUE;
2103 l_Num_One_N_Count := -1;
2104 l_count := 0;
2105 --DBMS_OUTPUT.PUT_LINE('cd.Dim_Level_Id '||l_dim_obj_id);
2106 FOR bsc_cn IN c_par_dim_ids LOOP
2107 --DBMS_OUTPUT.PUT_LINE('cn.parent_dim_level_id '||cn.parent_dim_level_id);
2108 --DBMS_OUTPUT.PUT_LINE('cn.relation_type '||cn.relation_type);
2109 IF ((bsc_cn.parent_dim_level_id = p_dim_obj_id) OR (bsc_cn.dim_level_id = p_dim_obj_id)) THEN
2110 IF (bsc_cn.relation_type = 1) THEN
2111 l_count := l_count + 1;
2112 IF (l_one_N_flag) THEN
2113 FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
2114 IF (l_One_N_Table(i).p_dim_obj_id = l_dim_obj_id) THEN
2115 l_one_N_flag := FALSE;
2116 l_Num_One_N_Count := i;
2117 EXIT;
2118 END IF;
2119 END LOOP;
2120 END IF;
2121 IF (l_Num_One_N_Count = -1) THEN
2122 l_One_N_Table(l_One_N_Count).p_dim_obj_id := l_dim_obj_id;
2123 l_One_N_Table(l_One_N_Count).p_refresh_flag := TRUE;
2124 l_One_N_Count := l_One_N_Count + 1;
2125 ELSE
2126 l_flag := FALSE;
2127 l_parent_ids := l_One_N_Table(l_Num_One_N_Count).p_Parent_dim_ids;
2128 --DBMS_OUTPUT.PUT_LINE('l_parent_ids '||l_parent_ids);
2129 WHILE (is_more( p_dim_lev_ids => l_parent_ids
2130 , p_dim_lev_id => lg_dim_obj_id)
2131 ) LOOP
2132 IF (lg_dim_obj_id = bsc_cn.parent_dim_level_id) THEN
2133 --DBMS_OUTPUT.PUT_LINE('p_parent_count '||l_One_N_Table(l_Num_One_N_Count).p_parent_count);
2134 --DBMS_OUTPUT.PUT_LINE('l_count '||l_count);
2135 IF (l_count = l_One_N_Table(l_Num_One_N_Count).p_parent_count) THEN
2136 l_One_N_Table(l_Num_One_N_Count).p_refresh_flag := FALSE;
2137 ELSE
2138 l_One_N_Table(l_Num_One_N_Count).p_refresh_flag := TRUE;
2139 END IF;
2140 l_flag := TRUE;
2141 EXIT;
2142 END IF;
2143 END LOOP;
2144 IF (NOT l_flag) THEN
2145 l_One_N_Table(l_Num_One_N_Count).p_refresh_flag := TRUE;
2146 EXIT;
2147 END IF;
2148 END IF;
2149 ELSIF (bsc_cn.relation_type = 2) THEN
2150 l_flag := FALSE;
2151 IF (l_dim_obj_id < bsc_cn.parent_dim_level_id) THEN
2152 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
2153 IF ((l_M_N_Table(i).p_dim_obj_id = l_dim_obj_id) AND
2154 (l_M_N_Table(i).p_Parent_dim_id = bsc_cn.parent_dim_level_id)) THEN
2155 IF(i < lg_index) THEN
2156 l_M_N_Table(i).p_refresh_flag := FALSE;
2157 END IF;
2158 l_flag := TRUE;
2159 EXIT;
2160 END IF;
2161 END LOOP;
2162 IF (NOT l_flag) THEN
2163 --DBMS_OUTPUT.PUT_LINE('l_M_N_Count '||l_M_N_Count);
2164 --DBMS_OUTPUT.PUT_LINE('cn.parent_dim_level_id '||cn.parent_dim_level_id);
2165 --DBMS_OUTPUT.PUT_LINE('l_dim_obj_id '||l_dim_obj_id);
2166 l_M_N_Table(l_M_N_Count).p_dim_obj_id := l_dim_obj_id ;
2167 l_M_N_Table(l_M_N_Count).p_Parent_dim_id := bsc_cn.parent_dim_level_id;
2168 l_M_N_Table(l_M_N_Count).p_refresh_flag := TRUE;
2169 l_M_N_Count := l_M_N_Count + 1;
2170 END IF;
2171 ELSE
2172 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
2173 IF ((l_M_N_Table(i).p_dim_obj_id = bsc_cn.parent_dim_level_id) AND
2174 (l_M_N_Table(i).p_Parent_dim_id = l_dim_obj_id)) THEN
2175 IF(i < lg_index) THEN
2176 l_M_N_Table(i).p_refresh_flag := FALSE;
2177 END IF;
2178 l_flag := TRUE;
2179 EXIT;
2180 END IF;
2181 END LOOP;
2182 IF (NOT l_flag) THEN
2183 --DBMS_OUTPUT.PUT_LINE('l_M_N_Count '||l_M_N_Count);
2184 --DBMS_OUTPUT.PUT_LINE('cn.parent_dim_level_id '||cn.parent_dim_level_id);
2185 --DBMS_OUTPUT.PUT_LINE('l_dim_obj_id '||l_dim_obj_id);
2186 l_M_N_Table(l_M_N_Count).p_dim_obj_id := bsc_cn.parent_dim_level_id;
2187 l_M_N_Table(l_M_N_Count).p_Parent_dim_id := l_dim_obj_id;
2188 l_M_N_Table(l_M_N_Count).p_refresh_flag := TRUE;
2189 l_M_N_Count := l_M_N_Count + 1;
2190 END IF;
2191 END IF;
2192 END IF;
2193 END IF;
2194 END LOOP;
2195 END LOOP;
2196 l_refresh_kpi_ids := ',';
2197 FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
2198 IF (l_One_N_Table(i).p_refresh_flag) THEN
2199 l_refresh_kpi_ids := l_refresh_kpi_ids||l_One_N_Table(i).p_dim_obj_id||',';
2200 END IF;
2201 END LOOP;
2202 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
2203 IF (l_M_N_Table(i).p_refresh_flag) THEN
2204 IF(INSTR(l_refresh_kpi_ids, ','||l_M_N_Table(i).p_dim_obj_id||',') = 0) THEN
2205 l_refresh_kpi_ids := l_refresh_kpi_ids||l_M_N_Table(i).p_dim_obj_id||',';
2206 END IF;
2207 IF(INSTR(l_refresh_kpi_ids, ','||l_M_N_Table(i).p_Parent_dim_id||',') = 0) THEN
2208 l_refresh_kpi_ids := l_refresh_kpi_ids||l_M_N_Table(i).p_Parent_dim_id||',';
2209 END IF;
2210 END IF;
2211 END LOOP;
2212
2213 --DBMS_OUTPUT.PUT_LINE('l_refresh_kpi_ids '||l_refresh_kpi_ids);
2214 IF (l_refresh_kpi_ids <> ',') THEN
2215 --DBMS_OUTPUT.PUT_LINE('Cascading changes to KPIs Part Starts Here');
2216 FOR cd IN c_kpi_dim_set LOOP
2217 --DBMS_OUTPUT.PUT_LINE('BEFORE BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet');
2218 IF(NOT (l_source = 'PMF' AND cd.short_name IS NULL)) THEN
2219 BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
2220 ( p_commit => FND_API.G_FALSE
2221 , p_kpi_id => cd.Indicator
2222 , p_dim_set_id => cd.Dim_Set_Id
2223 , p_delete => TRUE -- delete before creating in cascading
2224 , x_return_status => x_return_status
2225 , x_msg_count => x_msg_count
2226 , x_msg_data => x_msg_data
2227 );
2228 END IF;
2229 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2230 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet');
2231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2232 END IF;
2233 END LOOP;
2234 --DBMS_OUTPUT.PUT_LINE('Cascading changes to KPIs Part Ends Here');
2235 /********************************************************
2236 Check no of independent dimension objects in dimension set
2237 ********************************************************/
2238
2239 BSC_BIS_DIM_OBJ_PUB.check_indp_dimobjs
2240 (
2241 p_dim_id => p_dim_obj_id
2242 , x_return_status => x_return_status
2243 , x_msg_count => x_msg_count
2244 , x_msg_data => x_msg_data
2245 );
2246 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2247 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_OBJ_PUB.check_indp_dimobjs Failed: at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level <'||x_msg_data||'>');
2248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2249 END IF;
2250
2251 /*************************************************************
2252 List Button validation.For a list button all the dimension objects
2253 should have 1xM relationship.If the relationhsip is changed to
2254 MxN then list button should be disabled.
2255 /************************************************************/
2256 BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button
2257 ( p_Kpi_Id => NULL
2258 , p_Dim_Level_Id => p_dim_obj_id
2259 , x_return_status => x_return_status
2260 , x_msg_count => x_msg_count
2261 , x_msg_data => x_msg_data
2262 );
2263 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2264 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button');
2265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2266 END IF;
2267 END IF;
2268 --DBMS_OUTPUT.PUT_LINE(' --- FINAL TABLE ----');
2269 --DBMS_OUTPUT.PUT_LINE('PRINT OUT OF THE TABLES THAT WE HAVE GOT 1 x N RELATIONS');
2270 /*FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
2271 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_dim_obj_id '||l_One_N_Table(i).p_dim_obj_id);
2272 IF (l_One_N_Table(i).p_refresh_flag) THEN
2273 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_refresh_flag TRUE');
2274 ELSE
2275 --DBMS_OUTPUT.PUT_LINE('l_One_N_Table('||i||').p_refresh_flag FALSE');
2276 END IF;
2277 END LOOP;
2278 --DBMS_OUTPUT.PUT_LINE('PRINT OUT OF THE TABLES THAT WE HAVE GOT M x N RELATIONS');
2279 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
2280 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_dim_obj_id '||l_M_N_Table(i).p_dim_obj_id);
2281 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_Parent_dim_id '||l_M_N_Table(i).p_Parent_dim_id);
2282 IF (l_M_N_Table(i).p_refresh_flag) THEN
2283 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_refresh_flag TRUE');
2284 ELSE
2285 --DBMS_OUTPUT.PUT_LINE('l_M_N_Table('||i||').p_refresh_flag FALSE');
2286 END IF;
2287 END LOOP;*/
2288 IF ((p_is_not_config) AND (l_source = 'BSC')) THEN
2289 FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
2290 IF (l_One_N_Table(i).p_refresh_flag) THEN
2291 SELECT COUNT(A.Parent_Dim_Level_Id) INTO l_Count
2292 FROM BSC_SYS_DIM_LEVEL_RELS A
2293 WHERE A.Dim_Level_Id = l_One_N_Table(i).p_dim_obj_id
2294 AND A.Relation_Type = 1;
2295 IF (l_Count > MAX_PARENTS_RELS_1_N) THEN
2296 FND_MESSAGE.SET_NAME('BSC','BSC_MAX_DIM_OBJ_RELS');
2297 FND_MSG_PUB.ADD;
2298 RAISE FND_API.G_EXC_ERROR;
2299 END IF;
2300 END IF;
2301 END LOOP;
2302 --DBMS_OUTPUT.PUT_LINE('REFRESHING MASTER TABLES PART STARTS HERE');
2303 --DBMS_OUTPUT.PUT_LINE('--- ******** FOR 1 x N RELATIONS ******** -----');
2304 FOR i IN 0..(l_One_N_Table.COUNT-1) LOOP
2305 IF (l_One_N_Table(i).p_refresh_flag) THEN
2306 --DBMS_OUTPUT.PUT_LINE('Parameters to BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable');
2307 --DBMS_OUTPUT.PUT_LINE('p_dim_obj_id '||l_One_N_Table(i).p_dim_obj_id);
2308 l_flag := BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable
2309 ( p_dim_obj_id => l_One_N_Table(i).p_dim_obj_id
2310 , x_return_status => x_return_status
2311 , x_msg_count => x_msg_count
2312 , x_msg_data => x_msg_data
2313 );
2314 IF(NOT l_flag) THEN
2315 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable <'||x_msg_data||'>');
2316 RAISE FND_API.G_EXC_ERROR;
2317 END IF;
2318 END IF;
2319 END LOOP;
2320 --DBMS_OUTPUT.PUT_LINE('--- ******** FOR M x N RELATIONS ******** -----');
2321 FOR i IN 0..(l_M_N_Table.COUNT-1) LOOP
2322 IF (l_M_N_Table(i).p_refresh_flag) THEN
2323 --DBMS_OUTPUT.PUT_LINE('Parameters to BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable');
2324 --DBMS_OUTPUT.PUT_LINE('p_dim_obj_id '||l_M_N_Table(i).p_dim_obj_id);
2325 --DBMS_OUTPUT.PUT_LINE('p_parent_ids '||l_M_N_Table(i).p_Parent_dim_id);
2326 l_flag := BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable
2327 ( p_dim_obj_id => l_M_N_Table(i).p_dim_obj_id
2328 , p_parent_id => l_M_N_Table(i).p_Parent_dim_id
2329 , x_return_status => x_return_status
2330 , x_msg_count => x_msg_count
2331 , x_msg_data => x_msg_data
2332 );
2333 IF (NOT l_flag) THEN
2334 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable <'||x_msg_data||'>');
2335 RAISE FND_API.G_EXC_ERROR;
2336 END IF;
2337 END IF;
2338 END LOOP;
2339 --DBMS_OUTPUT.PUT_LINE('REFRESHING MASTER TABLES PART ENDS HERE');
2340 END IF;
2341
2342 IF((p_is_not_config) AND (l_source = 'BSC'))THEN
2343 BSC_BIS_DIM_REL_PUB.store_Relations
2344 ( p_dim_obj_id => p_dim_obj_id
2345 , x_rel_Table => l_new_rel_Table
2346 , x_return_status => x_return_status
2347 , x_msg_count => x_msg_count
2348 , x_msg_data => x_msg_data
2349 );
2350 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2351 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.store_Prev_Relations <'||x_msg_data||'>');
2352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2353 END IF;
2354
2355 BSC_BIS_DIM_REL_PUB.Validate_Filter_Button
2356 (
2357 p_dim_obj_id => p_dim_obj_id
2358 , x_new_rel_Table => l_new_rel_Table
2359 , x_prev_rel_Table => l_prev_rel_Table
2360 , x_return_status => x_return_status
2361 , x_msg_count => x_msg_count
2362 , x_msg_data => x_msg_data
2363
2364 );
2365 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2366 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.store_Prev_Relations <'||x_msg_data||'>');
2367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2368 END IF;
2369 END IF;
2370
2371 -- To delete denormailized table which is created from recursive relationship
2372 /****************************************************
2373 BSC_PMA_APIS_PUB.sync_dimension_table Should be called only when
2374 BSC53 is installed.So first we are checking if BSC53 is installed on the
2375 environment.Since this file is the part of MD/DD ARU we have made the call to
2376 the PL/SQL procedure "BSC_PMA_APIS_PUB.sync_dimension_table" dynamic so that
2377 the package gets complied on the pure BIS409 enviornments.
2378 /****************************************************/
2379
2380 IF(BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_TRUE) THEN
2381 l_is_denorm_deleted := FND_API.G_TRUE;
2382
2383 SELECT short_name
2384 INTO l_dim_short_name
2385 FROM bsc_sys_dim_levels_b
2386 WHERE dim_level_id = p_dim_obj_id;
2387
2388 BEGIN
2389 l_Sql := 'BEGIN IF(BSC_PMA_APIS_PUB.sync_dimension_table (:2,:3,:4)) THEN :1 :=FND_API.G_TRUE; ELSE :1:=FND_API.G_FALSE; END IF;END;';
2390 EXECUTE IMMEDIATE l_Sql USING IN l_dim_short_name,IN BIS_UTIL.G_ALTER_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
2391 EXCEPTION
2392 WHEN OTHERS THEN
2393 NULL;
2394 END;
2395
2396 IF(l_is_denorm_deleted=FND_API.G_FALSE) THEN
2397 RAISE FND_API.G_EXC_ERROR;
2398 END IF;
2399 END IF;
2400
2401 -- Added for Bug#4758995
2402 IF (l_source = BSC_UTILITY.c_PMF) THEN
2403
2404 SELECT short_name
2405 INTO l_dim_short_name
2406 FROM bsc_sys_dim_levels_b
2407 WHERE dim_level_id = p_dim_obj_id;
2408
2409 BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
2410 ( p_Short_Name => l_dim_short_name
2411 , x_return_status => x_return_status
2412 , x_msg_count => x_msg_count
2413 , x_msg_data => x_msg_data
2414 );
2415 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2417 END IF;
2418
2419 -- now, refresh all the child dimension object views as well - Bug#5300060
2420 -- Create an unique list child IDs ,which has been removed from the relationship, which exist in
2421 -- the relationship and that have been newly added to relationship
2422 l_rel_ids := BSC_UTILITY.Create_Unique_Comma_List(l_original_child_ids, p_child_ids);
2423
2424 IF (l_rel_ids IS NOT NULL) THEN
2425 WHILE (is_more( p_dim_lev_ids => l_rel_ids
2426 , p_dim_lev_id => l_rel_id
2427 )) LOOP
2428
2429 SELECT short_name
2430 INTO l_dim_short_name
2431 FROM bsc_sys_dim_levels_b
2432 WHERE dim_level_id = l_rel_id;
2433
2434 BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
2435 ( p_Short_Name => l_dim_short_name
2436 , x_return_status => x_return_status
2437 , x_msg_count => x_msg_count
2438 , x_msg_data => x_msg_data
2439 );
2440 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2442 END IF;
2443 END LOOP;
2444 END IF;
2445 END IF;
2446
2447 -- START Granluar Locking
2448 -- Change the time stamp of the main Dimension Level
2449 IF (p_is_not_config) THEN
2450 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DIM_LEVEL
2451 ( p_dim_level_id => p_dim_obj_id
2452 , x_return_status => x_return_status
2453 , x_msg_count => x_msg_count
2454 , x_msg_data => x_msg_data
2455 );
2456 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2458 END IF;
2459 IF (p_commit = FND_API.G_TRUE) THEN
2460 COMMIT;
2461 --DBMS_OUTPUT.PUT_LINE('COMMIT SUCCESSFUL');
2462 END IF;
2463 END IF;
2464
2465 -- END Granluar Locking
2466
2467 x_return_status := FND_API.G_RET_STS_SUCCESS;
2468 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels Procedure');
2469 EXCEPTION
2470 WHEN FND_API.G_EXC_ERROR THEN
2471 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2472 IF (c_db_child_type%ISOPEN) THEN
2473 CLOSE c_db_child_type;
2474 END IF;
2475 ROLLBACK TO AssUnassBSCRelsPMD;
2476 IF (x_msg_data IS NULL) THEN
2477 FND_MSG_PUB.Count_And_Get
2478 ( p_encoded => FND_API.G_FALSE
2479 , p_count => x_msg_count
2480 , p_data => x_msg_data
2481 );
2482 END IF;
2483 x_return_status := FND_API.G_RET_STS_ERROR;
2484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2485 IF (c_db_child_type%ISOPEN) THEN
2486 CLOSE c_db_child_type;
2487 END IF;
2488 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2489 ROLLBACK TO AssUnassBSCRelsPMD;
2490 IF (x_msg_data IS NULL) THEN
2491 FND_MSG_PUB.Count_And_Get
2492 ( p_encoded => FND_API.G_FALSE
2493 , p_count => x_msg_count
2494 , p_data => x_msg_data
2495 );
2496 END IF;
2497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2498 WHEN NO_DATA_FOUND THEN
2499 IF (c_db_child_type%ISOPEN) THEN
2500 CLOSE c_db_child_type;
2501 END IF;
2502 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2503 ROLLBACK TO AssUnassBSCRelsPMD;
2504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2505 IF (x_msg_data IS NOT NULL) THEN
2506 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels ';
2507 ELSE
2508 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels ';
2509 END IF;
2510 WHEN OTHERS THEN
2511 IF (c_db_child_type%ISOPEN) THEN
2512 CLOSE c_db_child_type;
2513 END IF;
2514 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2515 ROLLBACK TO AssUnassBSCRelsPMD;
2516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2517 IF (x_msg_data IS NOT NULL) THEN
2518 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels ';
2519 ELSE
2520 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels ';
2521 END IF;
2522 END Assign_New_Dim_Obj_Rels;
2523 /*********************************************************************************
2524 UNASSIGN DIMENSION-OBJECTS RELATIONSHIPS
2525 *********************************************************************************/
2526 PROCEDURE UnAssign_Dim_Obj_Rels
2527 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2528 , p_dim_obj_id IN NUMBER
2529 , p_parent_ids IN VARCHAR2
2530 , p_child_ids IN VARCHAR2
2531 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2532 , x_return_status OUT NOCOPY VARCHAR2
2533 , x_msg_count OUT NOCOPY NUMBER
2534 , x_msg_data OUT NOCOPY VARCHAR2
2535 ) IS
2536 l_dim_obj_ids VARCHAR2(32000);
2537 l_dim_obj_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
2538 l_flag BOOLEAN := TRUE;
2539
2540 l_parent_ids VARCHAR2(32000);
2541 l_parent_rel_type VARCHAR2(32000);
2542 l_parent_rel_column VARCHAR2(32000);
2543 l_parent_data_type VARCHAR2(32000);
2544 l_parent_data_source VARCHAR2(32000);
2545 l_child_ids VARCHAR2(32000);
2546 l_child_rel_type VARCHAR2(32000);
2547 l_child_rel_column VARCHAR2(32000);
2548 l_child_data_type VARCHAR2(32000);
2549 l_child_data_source VARCHAR2(32000);
2550
2551 CURSOR c_parent_ids IS
2552 SELECT parent_dim_level_id
2553 , relation_type
2554 , relation_col
2555 , data_source_type
2556 , data_source
2557 FROM BSC_SYS_DIM_LEVEL_RELS
2558 WHERE dim_level_id = p_dim_obj_id;
2559
2560 CURSOR c_childs_ids IS
2561 SELECT dim_level_id
2562 , relation_type
2563 , relation_col
2564 , data_source_type
2565 , data_source
2566 FROM BSC_SYS_DIM_LEVEL_RELS
2567 WHERE parent_dim_level_id = p_dim_obj_id
2568 AND relation_type = 1;
2569 BEGIN
2570 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels Procedure');
2571 FND_MSG_PUB.Initialize;
2572 x_return_status := FND_API.G_RET_STS_SUCCESS;
2573 IF (p_dim_obj_id IS NULL) THEN
2574 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2575 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
2576 FND_MSG_PUB.ADD;
2577 RAISE FND_API.G_EXC_ERROR;
2578 END IF;
2579 FOR cd IN c_parent_ids LOOP
2580 l_dim_obj_ids := p_parent_ids;
2581 l_flag := TRUE;
2582 IF (l_dim_obj_ids IS NOT NULL) THEN
2583 WHILE (is_more( p_dim_lev_ids => l_dim_obj_ids
2584 , p_dim_lev_id => l_dim_obj_id)
2585 ) LOOP
2586 IF (l_dim_obj_id = TO_CHAR(cd.parent_dim_level_id)) THEN
2587 l_flag := FALSE;
2588 EXIT;
2589 END IF;
2590 END LOOP;
2591 END IF;
2592 IF (l_flag) THEN
2593 IF (l_parent_ids IS NULL) THEN
2594 l_parent_ids := NVL(TO_CHAR(cd.parent_dim_level_id), 'NULL');
2595 ELSE
2596 l_parent_ids := l_parent_ids||', '||NVL(TO_CHAR(cd.parent_dim_level_id), 'NULL');
2597 END IF;
2598 IF (l_parent_rel_type IS NULL) THEN
2599 l_parent_rel_type := NVL(TO_CHAR(cd.relation_type), 'NULL');
2600 ELSE
2601 l_parent_rel_type := l_parent_rel_type||', '||NVL(TO_CHAR(cd.relation_type), 'NULL');
2602 END IF;
2603 IF (l_parent_rel_column IS NULL) THEN
2604 l_parent_rel_column := NVL(cd.relation_col, 'NULL');
2605 ELSE
2606 l_parent_rel_column := l_parent_rel_column||', '||NVL(cd.relation_col, 'NULL');
2607 END IF;
2608 IF (l_parent_data_type IS NULL) THEN
2609 l_parent_data_type := NVL(cd.data_source_type, 'NULL');
2610 ELSE
2611 l_parent_data_type := l_parent_data_type||', '||NVL(cd.data_source_type, 'NULL');
2612 END IF;
2613 IF (l_parent_data_source IS NULL) THEN
2614 l_parent_data_source := NVL(cd.data_source, 'NULL');
2615 ELSE
2616 l_parent_data_source := l_parent_data_source||', '||NVL(cd.data_source, 'NULL');
2617 END IF;
2618 END IF;
2619 END LOOP;
2620 FOR cd IN c_childs_ids LOOP
2621 l_dim_obj_ids := p_child_ids;
2622 l_flag := TRUE;
2623 IF (l_dim_obj_ids IS NOT NULL) THEN
2624 WHILE (is_more( p_dim_lev_ids => l_dim_obj_ids
2625 , p_dim_lev_id => l_dim_obj_id)
2626 ) LOOP
2627 IF (l_dim_obj_id = TO_CHAR(cd.dim_level_id)) THEN
2628 l_flag := FALSE;
2629 EXIT;
2630 END IF;
2631 END LOOP;
2632 END IF;
2633 IF (l_flag) THEN
2634 IF (l_child_ids IS NULL) THEN
2635 l_child_ids := NVL(TO_CHAR(cd.dim_level_id), 'NULL');
2636 ELSE
2637 l_child_ids := l_child_ids||', '||NVL(TO_CHAR(cd.dim_level_id), 'NULL');
2638 END IF;
2639 IF (l_child_rel_type IS NULL) THEN
2640 l_child_rel_type := NVL(TO_CHAR(cd.relation_type), 'NULL');
2641 ELSE
2642 l_child_rel_type := l_child_rel_type||', '||NVL(TO_CHAR(cd.relation_type), 'NULL');
2643 END IF;
2644 IF (l_child_rel_column IS NULL) THEN
2645 l_child_rel_column := NVL(cd.relation_col, 'NULL');
2646 ELSE
2647 l_child_rel_column := l_child_rel_column||', '||NVL(cd.relation_col, 'NULL');
2648 END IF;
2649 IF (l_child_data_type IS NULL) THEN
2650 l_child_data_type := NVL(cd.data_source_type, 'NULL');
2651 ELSE
2652 l_child_data_type := l_child_data_type||', '||NVL(cd.data_source_type, 'NULL');
2653 END IF;
2654 IF (l_child_data_source IS NULL) THEN
2655 l_child_data_source := NVL(cd.data_source, 'NULL');
2656 ELSE
2657 l_child_data_source := l_child_data_source||', '||NVL(cd.data_source, 'NULL');
2658 END IF;
2659 END IF;
2660 END LOOP;
2661 BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels
2662 ( p_commit => FND_API.G_FALSE
2663 , p_dim_obj_id => p_dim_obj_id
2664 , p_parent_ids => l_parent_ids
2665 , p_parent_rel_type => l_parent_rel_type
2666 , p_parent_rel_column => l_parent_rel_column
2667 , p_parent_data_type => l_parent_data_type
2668 , p_parent_data_source => l_parent_data_source
2669 , p_child_ids => l_child_ids
2670 , p_child_rel_type => l_child_rel_type
2671 , p_child_rel_column => l_child_rel_column
2672 , p_child_data_type => l_child_data_type
2673 , p_child_data_source => l_child_data_source
2674 , p_time_stamp => p_time_stamp
2675 , x_return_status => x_return_status
2676 , x_msg_count => x_msg_count
2677 , x_msg_data => x_msg_data
2678 );
2679 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2680 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels Failed: at BSC_BIS_DIM_REL_PUB.Assign_New_Dim_Obj_Rels');
2681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2682 END IF;
2683 IF (p_commit = FND_API.G_TRUE) THEN
2684 COMMIT;
2685 --DBMS_OUTPUT.PUT_LINE('COMMIT SUCCESSFUL');
2686 END IF;
2687 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels Procedure');
2688 EXCEPTION
2689 WHEN FND_API.G_EXC_ERROR THEN
2690 IF (x_msg_data IS NULL) THEN
2691 FND_MSG_PUB.Count_And_Get
2692 ( p_encoded => FND_API.G_FALSE
2693 , p_count => x_msg_count
2694 , p_data => x_msg_data
2695 );
2696 END IF;
2697 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2698 x_return_status := FND_API.G_RET_STS_ERROR;
2699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2700 IF (x_msg_data IS NULL) THEN
2701 FND_MSG_PUB.Count_And_Get
2702 ( p_encoded => FND_API.G_FALSE
2703 , p_count => x_msg_count
2704 , p_data => x_msg_data
2705 );
2706 END IF;
2707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2708 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2709 WHEN NO_DATA_FOUND THEN
2710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2711 IF (x_msg_data IS NOT NULL) THEN
2712 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels ';
2713 ELSE
2714 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels ';
2715 END IF;
2716 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2717 WHEN OTHERS THEN
2718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2719 IF (x_msg_data IS NOT NULL) THEN
2720 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels ';
2721 ELSE
2722 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.UnAssign_Dim_Obj_Rels ';
2723 END IF;
2724 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2725 END UnAssign_Dim_Obj_Rels;
2726
2727 /*********************************************************************************
2728 FUNCTION GET_PARENTS
2729 *********************************************************************************/
2730 FUNCTION get_parents
2731 (
2732 p_dim_obj_id IN NUMBER
2733 ) RETURN VARCHAR2
2734 IS
2735 l_parent_dim_names VARCHAR2(32000);
2736 l_name BSC_SYS_DIM_LEVELS_TL.NAME%TYPE;
2737
2738 CURSOR c_parent_dim_level_name IS
2739 SELECT l.name name
2740 FROM BSC_SYS_DIM_LEVEL_RELS r
2741 , BSC_SYS_DIM_LEVELS_VL l
2742 WHERE r.dim_level_id = p_dim_obj_id
2743 AND r.parent_dim_level_id = l.dim_level_id;
2744 BEGIN
2745 IF (c_parent_dim_level_name%ISOPEN) THEN
2746 CLOSE c_parent_dim_level_name;
2747 END IF;
2748
2749 FOR cd IN c_parent_dim_level_name LOOP
2750 l_name := cd.name;
2751 IF (l_name IS NOT NULL) THEN
2752 IF (l_parent_dim_names IS NULL) THEN
2753 l_parent_dim_names := l_name;
2754 ELSE
2755 l_parent_dim_names := l_parent_dim_names ||', '|| l_name;
2756 END IF;
2757 END IF;
2758 END LOOP;
2759 RETURN l_parent_dim_names;
2760 EXCEPTION
2761 WHEN OTHERS THEN
2762 IF (c_parent_dim_level_name%ISOPEN) THEN
2763 CLOSE c_parent_dim_level_name;
2764 END IF;
2765 RETURN NULL;
2766 END get_parents;
2767
2768 /*********************************************************************************
2769 FUNCTION GET_CHILDS
2770 *********************************************************************************/
2771 FUNCTION get_children
2772 (
2773 p_dim_obj_id IN NUMBER
2774 ) RETURN VARCHAR2
2775 IS
2776 l_child_dim_names VARCHAR2(32000);
2777 l_name BSC_SYS_DIM_LEVELS_TL.NAME%TYPE;
2778
2779 CURSOR c_child_dim_level_name IS
2780 SELECT l.name name
2781 FROM BSC_SYS_DIM_LEVEL_RELS r
2782 , BSC_SYS_DIM_LEVELS_VL l
2783 WHERE r.parent_dim_level_id = p_dim_obj_id
2784 AND r.dim_level_id = l.dim_level_id;
2785 BEGIN
2786 IF (c_child_dim_level_name%ISOPEN) THEN
2787 CLOSE c_child_dim_level_name;
2788 END IF;
2789
2790 FOR cd IN c_child_dim_level_name LOOP
2791 l_name := cd.name;
2792 IF (l_name IS NOT NULL) THEN
2793 IF (l_child_dim_names IS NULL) THEN
2794 l_child_dim_names := l_name;
2795 ELSE
2796 l_child_dim_names := l_child_dim_names ||', '|| l_name;
2797 END IF;
2798 END IF;
2799 END LOOP;
2800 RETURN l_child_dim_names;
2801 EXCEPTION
2802 WHEN OTHERS THEN
2803 IF (c_child_dim_level_name%ISOPEN) THEN
2804 CLOSE c_child_dim_level_name;
2805 END IF;
2806 RETURN NULL;
2807 END get_children;
2808
2809
2810 /*********************************************************************************
2811 FUNCTION Get_Original_Child_Ids -- Bug#5300060
2812 *********************************************************************************/
2813 FUNCTION Get_Original_Child_Ids
2814 (
2815 p_dim_obj_id IN NUMBER
2816 ) RETURN VARCHAR2
2817 IS
2818 l_child_dim_ids VARCHAR2(32000);
2819
2820 CURSOR c_child_dim_level_ids IS
2821 SELECT r.DIM_LEVEL_ID
2822 FROM BSC_SYS_DIM_LEVEL_RELS r
2823 WHERE r.parent_dim_level_id = p_dim_obj_id;
2824 BEGIN
2825 FOR cd IN c_child_dim_level_ids LOOP
2826 IF (l_child_dim_ids IS NULL) THEN
2827 l_child_dim_ids := CD.dim_level_id;
2828 ELSE
2829 l_child_dim_ids := l_child_dim_ids || ',' || CD.dim_level_id;
2830 END IF;
2831 END LOOP;
2832
2833 RETURN l_child_dim_ids;
2834 EXCEPTION
2835 WHEN OTHERS THEN
2836 RETURN NULL;
2837 END Get_Original_Child_Ids;
2838
2839 --==============================================================
2840 FUNCTION Is_More
2841 ( x_remain_id IN OUT NOCOPY VARCHAR2
2842 , x_remain_rel_type IN OUT NOCOPY VARCHAR2
2843 , x_remain_rel_column IN OUT NOCOPY VARCHAR2
2844 , x_remain_data_type IN OUT NOCOPY VARCHAR2
2845 , x_remain_data_source IN OUT NOCOPY VARCHAR2
2846 , x_id OUT NOCOPY NUMBER
2847 , x_rel_type OUT NOCOPY NUMBER
2848 , x_rel_column OUT NOCOPY VARCHAR2
2849 , x_data_type OUT NOCOPY VARCHAR2
2850 , x_data_source OUT NOCOPY VARCHAR2
2851 ) RETURN BOOLEAN
2852 IS
2853 l_pos_ids NUMBER;
2854 l_pos_rel_types NUMBER;
2855 l_pos_rel_columns NUMBER;
2856 l_pos_data_types NUMBER;
2857 l_pos_data_sources NUMBER;
2858
2859 BEGIN
2860 IF (x_remain_id IS NOT NULL) THEN
2861 l_pos_ids := INSTR(x_remain_id, ',');
2862 l_pos_rel_types := INSTR(x_remain_rel_type, ',');
2863 l_pos_rel_columns := INSTR(x_remain_rel_column, ',');
2864 l_pos_data_types := INSTR(x_remain_data_type, ',');
2865 l_pos_data_sources := INSTR(x_remain_data_source, ',');
2866
2867 IF (l_pos_ids > 0) THEN
2868 x_id := TO_NUMBER(TRIM(SUBSTR(x_remain_id, 1, l_pos_ids - 1)));
2869 x_rel_type := TO_NUMBER(TRIM(SUBSTR(x_remain_rel_type, 1, l_pos_rel_types - 1)));
2870 x_rel_column := TRIM(SUBSTR(x_remain_rel_column, 1, l_pos_rel_columns - 1));
2871 IF (UPPER(x_rel_column) = 'NULL') THEN
2872 x_rel_column := NULL;
2873 END IF;
2874 x_data_type := TRIM(SUBSTR(x_remain_data_type, 1, l_pos_data_types - 1));
2875 IF (UPPER(x_data_type) = 'NULL') THEN
2876 x_data_type := NULL;
2877 END IF;
2878 x_data_source := TRIM(SUBSTR(x_remain_data_source, 1, l_pos_data_sources - 1));
2879 IF (UPPER(x_data_source) = 'NULL') THEN
2880 x_data_source := NULL;
2881 END IF;
2882
2883 x_remain_id := TRIM(SUBSTR(x_remain_id, l_pos_ids + 1));
2884 x_remain_rel_type := TRIM(SUBSTR(x_remain_rel_type, l_pos_rel_types + 1));
2885 x_remain_rel_column := TRIM(SUBSTR(x_remain_rel_column, l_pos_rel_columns + 1));
2886 x_remain_data_type := TRIM(SUBSTR(x_remain_data_type, l_pos_data_types + 1));
2887 x_remain_data_source := TRIM(SUBSTR(x_remain_data_source, l_pos_data_sources + 1));
2888 ELSE
2889 x_id := TO_NUMBER(TRIM(x_remain_id));
2890 x_rel_type := TO_NUMBER(TRIM(x_remain_rel_type));
2891 x_rel_column := TRIM(x_remain_rel_column);
2892 IF (UPPER(x_rel_column) = 'NULL') THEN
2893 x_rel_column := NULL;
2894 END IF;
2895 x_data_type := TRIM(x_remain_data_type);
2896 IF (UPPER(x_data_type) = 'NULL') THEN
2897 x_data_type := NULL;
2898 END IF;
2899 x_data_source := TRIM(x_remain_data_source);
2900 IF (UPPER(x_data_source) = 'NULL') THEN
2901 x_data_source := NULL;
2902 END IF;
2903
2904 x_remain_id := NULL;
2905 x_remain_rel_column := NULL;
2906 x_remain_rel_type := NULL;
2907 x_remain_data_type := NULL;
2908 x_remain_data_source := NULL;
2909 END IF;
2910 RETURN TRUE;
2911 ELSE
2912 RETURN FALSE;
2913 END IF;
2914 END Is_More;
2915 /*******************************************************************************
2916 FUNCTION TO CREATE MASTER TABLE FOR ONE-MANY RELATIONS IN BSC
2917 ********************************************************************************/
2918 FUNCTION Create_One_To_N_MTable
2919 ( p_dim_obj_id IN NUMBER
2920 , x_return_status OUT NOCOPY VARCHAR2
2921 , x_msg_count OUT NOCOPY NUMBER
2922 , x_msg_data OUT NOCOPY VARCHAR2
2923 )
2924 RETURN BOOLEAN IS
2925 l_sql_stmt VARCHAR2(32000);
2926 l_sql_stmt1 VARCHAR2(32000);
2927 l_input_col_names VARCHAR2(32000);
2928 l_from_clause VARCHAR2(32000);
2929 l_where_clause VARCHAR2(32000);
2930 l_level_pk_cols VARCHAR2(32000);
2931 l_view_columns VARCHAR2(32000);
2932
2933 l_master_table VARCHAR2(30);
2934 l_input_table VARCHAR2(30);
2935 l_view_name VARCHAR2(30);
2936 l_dummy_table VARCHAR2(30) := 'BSC_B_DUMMY_TABLE';
2937 l_temp_table VARCHAR2(30);
2938
2939 l_level_pk_col BSC_SYS_DIM_LEVELS_B.Level_Pk_Col%TYPE;
2940 l_label_table_name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
2941 l_index_Table BSC_BIS_DIM_REL_PUB.One_To_N_Index_Table;
2942 l_index_Count NUMBER;
2943
2944 l_alias VARCHAR2(4);
2945 l_flag BOOLEAN;
2946
2947 l_col_names VARCHAR2(400) := NULL;
2948 l_bsc_dim_obj_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
2949 l_count NUMBER := 0;
2950 e_mlog_exception EXCEPTION;
2951 l_error_msg VARCHAR2(4000);
2952 l_max_code NUMBER;
2953 l_max_usr_code VARCHAR2(32000);
2954
2955 --cursor to get the columns for creation of view based on master-table
2956 CURSOR c_parents_Ids IS
2957 SELECT A.Parent_Dim_Level_Id
2958 , B.Level_Pk_Col
2959 , B.Level_Table_Name
2960 FROM BSC_SYS_DIM_LEVEL_RELS A
2961 , BSC_SYS_DIM_LEVELS_B B
2962 WHERE A.Dim_Level_Id = p_dim_obj_id
2963 AND B.Dim_Level_Id = A.Parent_Dim_Level_Id
2964 AND A.Relation_Type = 1;
2965 BEGIN
2966 SAVEPOINT CreateBSC1toNTabsPMD;
2967 FND_MSG_PUB.Initialize;
2968 x_return_status := FND_API.G_RET_STS_SUCCESS;
2969 BSC_APPS.Init_Bsc_Apps;
2970 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIMENSION_PUB.Create_One_To_N_MTable Function');
2971 IF (p_dim_obj_id IS NULL) THEN
2972 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2973 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
2974 FND_MSG_PUB.ADD;
2975 RAISE FND_API.G_EXC_ERROR;
2976 END IF;
2977 IF (p_dim_obj_id IS NOT NULL) THEN
2978 -- Bug #3236356
2979 SELECT COUNT(0) INTO l_count
2980 FROM BSC_SYS_DIM_LEVELS_B
2981 WHERE Dim_Level_Id = p_dim_obj_id;
2982
2983 IF (l_count = 0) THEN
2984 FND_MESSAGE.SET_NAME('BSC','BSC_INCORRECT_NAME_ENTERED');
2985 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
2986 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_dim_obj_id);
2987 FND_MSG_PUB.ADD;
2988 RAISE FND_API.G_EXC_ERROR;
2989 END IF;
2990 END IF;
2991 --DBMS_OUTPUT.PUT_LINE('2 ');
2992 SELECT level_table_name
2993 INTO l_bsc_dim_obj_rec.Bsc_Level_View_Name
2994 FROM BSC_SYS_DIM_LEVELS_B
2995 WHERE DIM_LEVEL_ID = p_dim_obj_id;
2996 --DBMS_OUTPUT.PUT_LINE('5 ');
2997 l_bsc_dim_obj_rec.Bsc_Level_Id := p_dim_obj_id;
2998 l_master_table := UPPER(l_bsc_dim_obj_rec.Bsc_Level_View_Name);
2999 --DBMS_OUTPUT.PUT_LINE('l_master_table <'||l_master_table||'>');
3000 l_input_table := UPPER('BSC_DI_'||l_bsc_dim_obj_rec.Bsc_Level_Id);
3001 --DBMS_OUTPUT.PUT_LINE('l_input_table <'||l_input_table||'>');
3002 l_view_name := UPPER('BSC_D_'||l_bsc_dim_obj_rec.Bsc_Level_Id||'_VL');
3003 --DBMS_OUTPUT.PUT_LINE('l_view_name <'||l_view_name||'>');
3004
3005 /************* LOGIC FOR GENERATION OF MASTER TABLE BASED ON RELATIONS 1-N************/
3006 l_flag := TRUE;
3007 l_alias := NULL;
3008 l_temp_table := l_dummy_table;
3009 WHILE (l_flag) LOOP
3010 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3011 ' WHERE OBJECT_NAME = :1';
3012 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3013 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_temp_table;
3014 IF (l_count = 0) THEN
3015 l_flag := FALSE;
3016 l_dummy_table := UPPER(l_temp_table);
3017 END IF;
3018 l_alias := BSC_BIS_DIM_REL_PUB.get_Next_Alias(l_alias);
3019 l_temp_table := l_dummy_table||l_alias;
3020 END LOOP;
3021 --DBMS_OUTPUT.PUT_LINE('l_dummy_table <'||l_dummy_table||'>');
3022
3023 l_col_names := 'A.CODE, A.USER_CODE, A.NAME, A.LANGUAGE, A.SOURCE_LANG ';
3024 l_view_columns := 'CODE, USER_CODE, NAME ';
3025 l_input_col_names := 'USER_CODE, NAME ';
3026
3027 --DBMS_OUTPUT.PUT_LINE('Original Master Columns <'||l_col_names||'>');
3028 l_alias := BSC_BIS_DIM_REL_PUB.get_Next_Alias(NULL);
3029 l_level_pk_cols := NULL;
3030 l_index_Count := 0;
3031 l_sql_stmt := 'CREATE TABLE '||l_dummy_table||' '||' TABLESPACE '||
3032 BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
3033 ' AS SELECT '||l_col_names||' ';
3034 FOR cd IN c_parents_Ids LOOP
3035 --DBMS_OUTPUT.PUT_LINE('11 l_level_pk_col <'||cd.Level_Pk_Col||'> l_label_table_name <'||cd.Level_Table_Name||'>' );
3036 l_level_pk_col := cd.Level_Pk_Col;
3037 l_label_table_name := cd.Level_Table_Name;
3038
3039 IF(l_level_pk_cols IS NULL) THEN
3040 l_level_pk_cols := ''''||l_level_pk_col||'''';
3041 ELSE
3042 l_level_pk_cols := l_level_pk_cols||', '||''''||l_level_pk_col||'''';
3043 END IF;
3044 l_sql_stmt1 := 'SELECT code,user_code FROM '||l_label_table_name||' WHERE code = (SELECT MAX(a.code) FROM '||l_label_table_name ||' a) AND ROWNUM <2';
3045 EXECUTE IMMEDIATE l_sql_stmt1 INTO l_max_code,l_max_usr_code ;
3046 l_alias := BSC_BIS_DIM_REL_PUB.get_Next_Alias(l_alias);
3047 --DBMS_OUTPUT.PUT_LINE('l_alias <'||l_alias||'>');
3048 l_sql_stmt := l_sql_stmt||' , ';
3049 l_sql_stmt := l_sql_stmt||' '||'NVL('||l_alias||'.CODE,'||l_max_code||') AS '||l_level_pk_col||', '||'NVL('||
3050 l_alias||'.USER_CODE,'''||l_max_usr_code||''') AS '||l_level_pk_col||'_USR ';
3051 l_view_columns := l_view_columns||', '||l_level_pk_col||', '||l_level_pk_col||'_USR ';
3052 l_input_col_names := l_input_col_names||', '||l_level_pk_col||'_USR ';
3053
3054 l_index_Table(l_index_Count).p_Column_Name := l_level_pk_col;
3055 l_index_Count := l_index_Count + 1;
3056 --from clause
3057 IF (l_alias = 'A0') THEN
3058 l_from_clause := ', '||l_label_table_name||' '||l_alias||' ';
3059 l_where_clause := ' WHERE ';
3060 ELSE
3061 l_from_clause := l_from_clause||', '||l_label_table_name||' '||l_alias||' ';
3062 l_where_clause := l_where_clause||' AND ';
3063 END IF;
3064 l_where_clause := l_where_clause||' A.CODE = '||l_alias||'.CODE(+) '||' AND A.LANGUAGE = '||l_alias||'.LANGUAGE(+) ';
3065 END LOOP;
3066 --DBMS_OUTPUT.PUT_LINE('l_level_pk_cols <'||l_level_pk_cols||'>');
3067 --DBMS_OUTPUT.PUT_LINE('length is '||(lengthb(l_sql_stmt) + lengthb('FROMa') + lengthb(l_from_clause) + lengthb(l_where_clause)));
3068 l_sql_stmt := l_sql_stmt||' FROM '||l_master_table||' A '||l_from_clause||l_where_clause;
3069 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1, 200));
3070 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 201, 200));
3071 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 401, 200));
3072 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 601, 200));
3073 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 801, 200));
3074 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1001, 200));
3075 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1201, 200));
3076 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1401, 200));
3077 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1601, 200));
3078 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1801, 200));
3079 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2001, 200));
3080 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2201, 200));
3081 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2401, 200));
3082 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2601, 200));
3083 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2801, 200));
3084 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3001, 200));
3085 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3201, 200));
3086 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3401, 200));
3087 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3601, 200));
3088 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3801, 200));
3089 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 4001, 200));
3090 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 4201, 200));
3091 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 4401, 200));
3092 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 4601, 200));
3093 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 4801, 200));
3094 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 5001, 200));
3095 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 5201, 200));
3096 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 5401, 200));
3097 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 5601, 200));
3098 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 5801, 200));
3099 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 6001, 200));
3100 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 6201, 200));
3101 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 6401, 200));
3102 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 6601, 200));
3103 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 6801, 200));
3104 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 7001, 200));
3105 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 7201, 200));
3106 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF DUMMY MASTER TABLE---------');
3107
3108 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_table, l_dummy_table);
3109
3110 --DBMS_OUTPUT.PUT_LINE('---------DROP MASTER TABLE----------');
3111 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3112 ' WHERE OBJECT_NAME = :1';
3113 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3114 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_master_table;
3115
3116 IF (l_count <> 0) THEN
3117 l_sql_stmt := 'DROP TABLE '||l_master_table;
3118 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3119 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.drop_table, l_master_table);
3120 END IF;
3121
3122 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF MASTER TABLE---------');
3123 l_sql_stmt := 'CREATE TABLE '||l_master_table||' '||' TABLESPACE '||
3124 BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
3125 ' AS SELECT * FROM '||l_dummy_table;
3126
3127
3128 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3129
3130 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_table, l_master_table);
3131
3132 --DBMS_OUTPUT.PUT_LINE('---------DROP DUMMY TABLE---------');
3133 l_sql_stmt := 'Drop Table '||l_dummy_table;
3134 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.drop_table, l_dummy_table);
3135
3136
3137 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF INDEXS ON MASTER TABLE---------');
3138 l_sql_stmt := ' CREATE UNIQUE INDEX '||l_master_table||'_U1 '||
3139 ' ON '||l_master_table||' (CODE,LANGUAGE) '||' '||
3140 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Index_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause;
3141 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3142 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_index, l_master_table);
3143
3144 -- Create a new UNIQUE INDEX for Loader Performance - Bug #3090828
3145 l_sql_stmt := ' CREATE UNIQUE INDEX '||l_master_table||'_U2 '||
3146 ' ON '||l_master_table||' (USER_CODE,LANGUAGE) '||' '||
3147 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Index_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause;
3148 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3149 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_index, l_master_table);
3150
3151 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF INDEXS ON MASTER TABLE LOADER BUG #3120190---------');
3152 FOR i IN 0..(l_index_Table.COUNT-1) LOOP
3153 --DBMS_OUTPUT.PUT_LINE('l_index_Table('||i||').p_Column_Name '||l_index_Table(i).p_Column_Name);
3154 -- Create a new Non-Unique INDEX for Loader Performance - Bug #3120190
3155 --Due to DB restrictions, index can't be created if length is > 30 characters.
3156 --DBMS_OUTPUT.PUT_LINE('index length '||LENGTH(l_master_table||'_N'||(i+1)));
3157 IF (LENGTH(l_master_table||'_N'||(i+1)) <= 30) THEN
3158 l_sql_stmt := ' CREATE INDEX '||l_master_table||'_N'||(i+1)||' '||
3159 ' ON '||l_master_table||' ('||l_index_Table(i).p_Column_Name||') '||' '||
3160 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Index_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause;
3161 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3162 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_index, l_master_table);
3163 ELSE
3164 EXIT;
3165 END IF;
3166 END LOOP;
3167
3168 /************* LOGIC FOR GENERATION OF MASTER TABLE ENDS HERE BASED ON RELATIONS 1-N************/
3169 --DBMS_OUTPUT.PUT_LINE('---------GENERATION OF INPUT TABLE---------');
3170 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3171 ' WHERE OBJECT_NAME = :1';
3172 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3173 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_input_table;
3174
3175 IF (l_count <> 0) THEN
3176 l_sql_stmt := 'DROP TABLE '||l_input_table;
3177 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3178 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.drop_table, l_input_table);
3179 END IF;
3180 l_sql_stmt := ' CREATE TABLE '||l_input_table||' '||' TABLESPACE '||
3181 BSC_APPS.Get_Tablespace_Name(BSC_APPS.Input_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
3182 ' AS SELECT '||l_input_col_names||' FROM '||l_master_table||' WHERE 1 = 2';
3183
3184 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1, 200));
3185 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 201, 200));
3186 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 401, 200));
3187 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 601, 200));
3188 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 801, 200));
3189 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1001, 200));
3190 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1201, 200));
3191 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1401, 200));
3192 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1601, 200));
3193 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1801, 200));
3194 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2001, 200));
3195 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2201, 200));
3196 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2401, 200));
3197 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2601, 200));
3198 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 2801, 200));
3199 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3001, 200));
3200 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 3201, 200));
3201
3202 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_table, l_input_table);
3203
3204 l_sql_stmt := ' CREATE UNIQUE INDEX '||l_input_table||'_U1 '||
3205 ' ON '||l_input_table||' (USER_CODE) '||' '||
3206 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.Input_Index_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause;
3207 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3208 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_index, l_input_table);
3209
3210 --create dynamic view for modified master table
3211 --DBMS_OUTPUT.PUT_LINE('---------GENERATION OF VIEW BASED ON MASTER TABLE---------');
3212 l_sql_stmt := ' CREATE OR REPLACE VIEW '||l_view_name||' AS (' ||
3213 ' SELECT '||l_view_columns||
3214 ' FROM '||l_master_table||
3215 ' WHERE LANGUAGE = USERENV(''LANG''))';
3216 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3217 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_view, l_view_name);
3218
3219 /************************************************************************
3220 Child dimension object table will contain the columns of the parent
3221 dimension object and will be added or removed.So after the creation of the new
3222 child dimension object table we need to cascade these changes to
3223 MLOG table also.
3224 /************************************************************************/
3225 IF NOT (BSC_SYNC_MVLOGS.Sync_dim_table_mv_log(l_master_table,l_error_msg)) THEN
3226 RAISE e_mlog_exception;
3227 END IF;
3228
3229 COMMIT;
3230 x_return_status := FND_API.G_RET_STS_SUCCESS;
3231 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIMENSION_PUB.Create_One_To_N_MTable Function');
3232 RETURN TRUE;
3233 EXCEPTION
3234 WHEN e_mlog_exception THEN
3235 ROLLBACK TO CreateBSC1toNTabsPMD;
3236 x_msg_data := NULL;
3237 x_msg_data := l_error_msg || ' -> BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable';
3238 x_return_status := FND_API.G_RET_STS_ERROR;
3239 RETURN FALSE;
3240 WHEN FND_API.G_EXC_ERROR THEN
3241 ROLLBACK TO CreateBSC1toNTabsPMD;
3242 IF (x_msg_data IS NULL) THEN
3243 FND_MSG_PUB.Count_And_Get
3244 ( p_encoded => FND_API.G_FALSE
3245 , p_count => x_msg_count
3246 , p_data => x_msg_data
3247 );
3248 END IF;
3249 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3250 x_return_status := FND_API.G_RET_STS_ERROR;
3251 RETURN FALSE;
3252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3253 ROLLBACK TO CreateBSC1toNTabsPMD;
3254 IF (x_msg_data IS NULL) THEN
3255 FND_MSG_PUB.Count_And_Get
3256 ( p_encoded => FND_API.G_FALSE
3257 , p_count => x_msg_count
3258 , p_data => x_msg_data
3259 );
3260 END IF;
3261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3262 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3263 RETURN FALSE;
3264 WHEN NO_DATA_FOUND THEN
3265 ROLLBACK TO CreateBSC1toNTabsPMD;
3266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3267 IF (x_msg_data IS NOT NULL) THEN
3268 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable ';
3269 ELSE
3270 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable ';
3271 END IF;
3272 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3273 RETURN FALSE;
3274 WHEN OTHERS THEN
3275 ROLLBACK TO CreateBSC1toNTabsPMD;
3276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3277 IF (x_msg_data IS NOT NULL) THEN
3278 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable ';
3279 ELSE
3280 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Create_One_To_N_MTable ';
3281 END IF;
3282 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3283 RETURN FALSE;
3284 END Create_One_To_N_MTable;
3285
3286 /*******************************************************************************
3287 FUNCTION TO CREATE MASTER TABLE FOR MANY TO MANY RELATIONS IN BSC
3288 ********************************************************************************/
3289 FUNCTION Create_M_To_N_MTable
3290 ( p_dim_obj_id IN NUMBER
3291 , p_parent_id IN VARCHAR2
3292 , x_return_status OUT NOCOPY VARCHAR2
3293 , x_msg_count OUT NOCOPY NUMBER
3294 , x_msg_data OUT NOCOPY VARCHAR2
3295 )
3296 RETURN BOOLEAN IS
3297 l_sql_stmt VARCHAR2(32000);
3298 l_flag BOOLEAN;
3299 l_count NUMBER := 0;
3300
3301 l_c_dim_level_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;
3302 l_c_abbr BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
3303 l_c_level_pk_col BSC_SYS_DIM_LEVELS_B.Level_PK_Col%TYPE;
3304 l_c_level_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
3305
3306 l_p_dim_level_id BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE;
3307 l_p_abbr BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
3308 l_p_level_pk_col BSC_SYS_DIM_LEVELS_B.Level_PK_Col%TYPE;
3309 l_p_level_table BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
3310
3311 l_master_table VARCHAR2(30);
3312 l_input_table VARCHAR2(30);
3313 BEGIN
3314 SAVEPOINT CreateBSCMtoNTabsPMD;
3315 FND_MSG_PUB.Initialize;
3316 x_return_status := FND_API.G_RET_STS_SUCCESS;
3317 BSC_APPS.Init_Bsc_Apps;
3318 IF (p_dim_obj_id IS NULL) THEN
3319 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3320 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
3321 FND_MSG_PUB.ADD;
3322 RAISE FND_API.G_EXC_ERROR;
3323 END IF;
3324 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIMENSION_PUB.Create_M_To_N_MTable Function');
3325 IF (p_dim_obj_id IS NOT NULL) THEN
3326
3327 -- Bug #3236356
3328 SELECT COUNT(0) INTO l_count
3329 FROM BSC_SYS_DIM_LEVELS_B
3330 WHERE Dim_Level_Id = p_dim_obj_id;
3331
3332 IF (l_count = 0) THEN
3333 FND_MESSAGE.SET_NAME('BSC','BSC_INCORRECT_NAME_ENTERED');
3334 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
3335 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_dim_obj_id);
3336 FND_MSG_PUB.ADD;
3337 RAISE FND_API.G_EXC_ERROR;
3338 END IF;
3339 END IF;
3340 SELECT COUNT(*) INTO l_count
3341 FROM BSC_SYS_DIM_LEVEL_RELS
3342 WHERE dim_level_id = p_dim_obj_id
3343 AND parent_dim_level_id = p_parent_id
3344 AND relation_type = 2;
3345 --DBMS_OUTPUT.PUT_LINE('After');
3346 IF (l_count = 0) THEN
3347 --DBMS_OUTPUT.PUT_LINE('Parameters to BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs');
3348 --DBMS_OUTPUT.PUT_LINE('p_dim_obj_id '||p_dim_obj_id);
3349 --DBMS_OUTPUT.PUT_LINE('p_parent_ids '||p_parent_id);
3350 BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs
3351 ( p_dim_obj_id => p_dim_obj_id
3352 , p_parent_id => p_parent_id
3353 , x_return_status => x_return_status
3354 , x_msg_count => x_msg_count
3355 , x_msg_data => x_msg_data
3356 );
3357 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3358 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable Failed: at BSC_DIMENSION_LEVELS_PUB.Drop_M_To_N_Unused_Tabs <'||x_msg_data||'>');
3359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3360 END IF;
3361 ELSE
3362 SELECT dim_level_id
3363 , abbreviation
3364 , level_pk_col
3365 , level_table_name
3366 INTO l_c_dim_level_id
3367 , l_c_abbr
3368 , l_c_level_pk_col
3369 , l_c_level_table
3370 FROM BSC_SYS_DIM_LEVELS_B WHERE dim_level_id = p_dim_obj_id;
3371
3372 SELECT dim_level_id
3373 , abbreviation
3374 , level_pk_col
3375 , level_table_name
3376 INTO l_p_dim_level_id
3377 , l_p_abbr
3378 , l_p_level_pk_col
3379 , l_p_level_table
3380 FROM BSC_SYS_DIM_LEVELS_B WHERE dim_level_id = p_parent_id;
3381
3382 IF (l_c_abbr <= l_p_abbr) THEN
3383 l_master_table := 'BSC_D_'||l_c_abbr||'_'||l_p_abbr;
3384 ELSE
3385 l_master_table := 'BSC_D_'||l_p_abbr||'_'||l_c_abbr;
3386 END IF;
3387 l_master_table := UPPER(l_master_table);
3388 IF (l_c_dim_level_id <= l_p_dim_level_id) THEN
3389 l_input_table := 'BSC_DI_'||l_c_dim_level_id||'_'||l_p_dim_level_id;
3390 ELSE
3391 l_input_table := 'BSC_DI_'||l_p_dim_level_id||'_'||l_c_dim_level_id;
3392 END IF;
3393 --DBMS_OUTPUT.PUT_LINE('---------DROP MASTER TABLE IF EXISTS----------');
3394 --DBMS_OUTPUT.PUT_LINE('l_master_table <'||l_master_table||'>');
3395
3396 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3397 ' WHERE OBJECT_NAME = :1';
3398 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3399 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_master_table;
3400
3401 IF (l_count = 0) THEN
3402 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF MASTER TABLE---------');
3403 l_sql_stmt := ' CREATE TABLE '||l_master_table||' '||' TABLESPACE '||
3404 BSC_APPS.Get_Tablespace_Name(BSC_APPS.Dimension_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
3405 ' AS SELECT '||' A.CODE AS '||l_c_level_pk_col||
3406 ', B.CODE AS '||l_p_level_pk_col||
3407 ' FROM '||l_c_level_table||' A, '||
3408 l_p_level_table||' B '||
3409 ' WHERE A.LANGUAGE = B.LANGUAGE '||
3410 ' AND A.LANGUAGE = '''||USERENV('LANG')||'''';
3411
3412 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||SUBSTR(l_sql_stmt, 1, 200)||'>');
3413 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||SUBSTR(l_sql_stmt, 201, 200)||'>');
3414
3415 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_table, l_master_table);
3416 END IF;
3417 --DBMS_OUTPUT.PUT_LINE('l_input_table <'||l_input_table||'>');
3418 --DBMS_OUTPUT.PUT_LINE('---------CREATION OF INPUT TABLE---------');
3419 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3420 ' WHERE OBJECT_NAME = :1';
3421 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3422 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_input_table;
3423 IF (l_count = 0) THEN
3424 l_sql_stmt := ' CREATE TABLE '||l_input_table||' '||' TABLESPACE '||
3425 BSC_APPS.Get_Tablespace_Name(BSC_APPS.Input_Table_Tbs_Type)||' '||BSC_APPS.bsc_storage_clause||
3426 ' AS SELECT '||' A.USER_CODE AS '||l_c_level_pk_col||'_USR, B.USER_CODE AS '||
3427 l_p_level_pk_col||'_USR '||
3428 ' FROM '||l_c_level_table||' A, '||
3429 ' '||l_p_level_table||' B '||
3430 ' WHERE 1 = 2 ';
3431
3432 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 1, 200));
3433 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 201, 200));
3434 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 401, 200));
3435 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 601, 200));
3436 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_sql_stmt, 801, 200));
3437 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.create_table, l_input_table);
3438 END IF;
3439 --insert into BSC_DB_TABLES_RELS & BSC_DB_TABLES
3440 --DBMS_OUTPUT.PUT_LINE('INSERT INTO BSC_DB_TABLES_RELS');
3441 SELECT COUNT(*) INTO l_count
3442 FROM BSC_DB_TABLES_RELS
3443 WHERE Source_Table_Name = l_input_table;
3444 --DBMS_OUTPUT.PUT_LINE('INSERT INTO BSC_DB_TABLES_RELS '||l_count);
3445 --DBMS_OUTPUT.PUT_LINE('l_master_table '||l_master_table);
3446 --DBMS_OUTPUT.PUT_LINE('l_input_table '||l_input_table);
3447 IF (l_count = 0) THEN
3448 INSERT INTO BSC_DB_TABLES_RELS
3449 (Table_Name, Source_Table_Name, Relation_Type)
3450 VALUES (l_master_table, l_input_table, 0);
3451 ELSE
3452 UPDATE BSC_DB_TABLES_RELS
3453 SET Table_Name = l_master_table
3454 WHERE Source_Table_Name = l_input_table;
3455 END IF;
3456 --DBMS_OUTPUT.PUT_LINE('INSERT INTO BSC_DB_TABLES');
3457 SELECT COUNT(*) INTO l_count
3458 FROM BSC_DB_TABLES
3459 WHERE Table_Name = l_input_table;
3460 --DBMS_OUTPUT.PUT_LINE('INSERT INTO BSC_DB_TABLES '||l_count);
3461 IF (l_count = 0) THEN
3462 INSERT INTO BSC_DB_TABLES
3463 (Table_Name, Table_Type, Periodicity_Id,
3464 Source_Data_Type, Source_File_Name)
3465 VALUES (l_input_table, 2, 0, 0, NULL);
3466 END IF;
3467 END IF;
3468
3469 COMMIT;
3470 x_return_status := FND_API.G_RET_STS_SUCCESS;
3471 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIMENSION_PUB.Create_M_To_N_MTable Function');
3472 RETURN TRUE;
3473 EXCEPTION
3474 WHEN FND_API.G_EXC_ERROR THEN
3475 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3476 ROLLBACK TO CreateBSCMtoNTabsPMD;
3477 IF (x_msg_data IS NULL) THEN
3478 FND_MSG_PUB.Count_And_Get
3479 ( p_encoded => FND_API.G_FALSE
3480 , p_count => x_msg_count
3481 , p_data => x_msg_data
3482 );
3483 END IF;
3484 x_return_status := FND_API.G_RET_STS_ERROR;
3485 RETURN FALSE;
3486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3487 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3488 ROLLBACK TO CreateBSCMtoNTabsPMD;
3489 IF (x_msg_data IS NULL) THEN
3490 FND_MSG_PUB.Count_And_Get
3491 ( p_encoded => FND_API.G_FALSE
3492 , p_count => x_msg_count
3493 , p_data => x_msg_data
3494 );
3495 END IF;
3496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3497 RETURN FALSE;
3498 WHEN NO_DATA_FOUND THEN
3499 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3500 ROLLBACK TO CreateBSCMtoNTabsPMD;
3501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3502 IF (x_msg_data IS NOT NULL) THEN
3503 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable ';
3504 ELSE
3505 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable ';
3506 END IF;
3507 RETURN FALSE;
3508 WHEN OTHERS THEN
3509 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3510 ROLLBACK TO CreateBSCMtoNTabsPMD;
3511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3512 IF (x_msg_data IS NOT NULL) THEN
3513 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable ';
3514 ELSE
3515 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Create_M_To_N_MTable ';
3516 END IF;
3517 RETURN FALSE;
3518 END Create_M_To_N_MTable;
3519 /*******************************************************************************
3520 DROP MASTER TABLES THAT ARE NOT USED IN THE CONTEXT FOR M x N RELATIONSHIPS
3521 ********************************************************************************/
3522 PROCEDURE Drop_M_To_N_Unused_Tabs
3523 ( p_dim_obj_id IN NUMBER
3524 , p_parent_id IN VARCHAR2
3525 , x_return_status OUT NOCOPY VARCHAR2
3526 , x_msg_count OUT NOCOPY NUMBER
3527 , x_msg_data OUT NOCOPY VARCHAR2
3528 ) IS
3529 l_sql_stmt VARCHAR2(32000);
3530 l_count NUMBER;
3531
3532 l_master_table VARCHAR2(50);
3533 l_input_table VARCHAR2(50);
3534
3535 l_c_abbre BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
3536 l_p_abbre BSC_SYS_DIM_LEVELS_B.Abbreviation%TYPE;
3537 BEGIN
3538 SAVEPOINT DropBSCMtoNTabsPMD;
3539 FND_MSG_PUB.Initialize;
3540 x_return_status := FND_API.G_RET_STS_SUCCESS;
3541 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIMENSION_PUB.Drop_M_To_N_Unused_Tabs Procedure');
3542 IF (p_dim_obj_id IS NULL) THEN
3543 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3544 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_OBJ_ID'), TRUE);
3545 FND_MSG_PUB.ADD;
3546 RAISE FND_API.G_EXC_ERROR;
3547 END IF;
3548
3549 SELECT abbreviation INTO l_p_abbre
3550 FROM BSC_SYS_DIM_LEVELS_B
3551 WHERE dim_level_id = p_dim_obj_id;
3552
3553 SELECT abbreviation INTO l_c_abbre
3554 FROM BSC_SYS_DIM_LEVELS_B
3555 WHERE dim_level_id = p_parent_id;
3556 --drop tables, needs changes, will be done in IInd release
3557 IF (p_dim_obj_id <= p_parent_id) THEN
3558 l_input_table := 'BSC_DI_'||p_dim_obj_id||'_'||p_parent_id;
3559 ELSE
3560 l_input_table := 'BSC_DI_'||p_parent_id||'_'||p_dim_obj_id;
3561 END IF;
3562
3563 IF (l_c_abbre <= l_p_abbre) THEN
3564 l_master_table := UPPER('BSC_D_'||l_c_abbre||'_'||l_p_abbre);
3565 ELSE
3566 l_master_table := UPPER('BSC_D_'||l_p_abbre||'_'||l_c_abbre);
3567 END IF;
3568
3569 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3570 ' WHERE OBJECT_NAME = :1';
3571 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3572 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_master_table;
3573 IF (l_count <> 0) THEN
3574 l_sql_stmt := 'DROP TABLE '||l_master_table;
3575 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3576 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.drop_table, l_master_table);
3577 END IF;
3578 l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
3579 ' WHERE OBJECT_NAME = :1';
3580 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3581 EXECUTE IMMEDIATE l_sql_stmt INTO l_count USING l_input_table;
3582 IF (l_count <> 0) THEN
3583 l_sql_stmt := 'DROP TABLE '||l_input_table;
3584 --DBMS_OUTPUT.PUT_LINE('l_sql_stmt <'||l_sql_stmt||'>');
3585 BSC_APPS.DO_DDL(l_sql_stmt, ad_ddl.drop_table, l_input_table);
3586 END IF;
3587 --DBMS_OUTPUT.PUT_LINE('DELETE TABLES BSC_DB_TABLES '||l_input_table);
3588 DELETE FROM BSC_DB_TABLES
3589 WHERE Table_Name = l_input_table;
3590
3591 DELETE FROM BSC_DB_TABLES_RELS
3592 WHERE Source_Table_Name = l_input_table;
3593
3594 x_return_status := FND_API.G_RET_STS_SUCCESS;
3595 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIMENSION_PUB.Drop_M_To_N_Unused_Tabs Procedure');
3596 EXCEPTION
3597 WHEN FND_API.G_EXC_ERROR THEN
3598 ROLLBACK TO DropBSCMtoNTabsPMD;
3599 IF (x_msg_data IS NULL) THEN
3600 FND_MSG_PUB.Count_And_Get
3601 ( p_encoded => FND_API.G_FALSE
3602 , p_count => x_msg_count
3603 , p_data => x_msg_data
3604 );
3605 END IF;
3606 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3607 x_return_status := FND_API.G_RET_STS_ERROR;
3608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3609 ROLLBACK TO DropBSCMtoNTabsPMD;
3610 IF (x_msg_data IS NULL) THEN
3611 FND_MSG_PUB.Count_And_Get
3612 ( p_encoded => FND_API.G_FALSE
3613 , p_count => x_msg_count
3614 , p_data => x_msg_data
3615 );
3616 END IF;
3617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3618 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3619 WHEN NO_DATA_FOUND THEN
3620 ROLLBACK TO DropBSCMtoNTabsPMD;
3621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3622 IF (x_msg_data IS NOT NULL) THEN
3623 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs ';
3624 ELSE
3625 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs ';
3626 END IF;
3627 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3628 WHEN OTHERS THEN
3629 ROLLBACK TO DropBSCMtoNTabsPMD;
3630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3631 IF (x_msg_data IS NOT NULL) THEN
3632 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs ';
3633 ELSE
3634 x_msg_data := SQLERRM||' at BSC_BIS_DIM_REL_PUB.Drop_M_To_N_Unused_Tabs ';
3635 END IF;
3636 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3637 END Drop_M_To_N_Unused_Tabs;
3638
3639 /********************************************************************************
3640 WARNING :
3641 This function will return false if any changes to dim_object relations
3642 will result in structural changes. This is designed to fulfil the UI screen
3643 need and not a generic function so it should not be called internally from any
3644 other APIs without proper impact analysis.
3645 ********************************************************************************/
3646 FUNCTION is_KPI_Flag_For_Dim_Obj_Rels
3647 ( p_dim_obj_id IN NUMBER
3648 , p_parent_ids IN VARCHAR2
3649 , p_parent_rel_type IN VARCHAR2
3650 , p_child_ids IN VARCHAR2
3651 , p_child_rel_type IN VARCHAR2
3652 ) RETURN VARCHAR2 IS
3653 l_Msg_Data VARCHAR2(32000);
3654 l_msg_count NUMBER;
3655
3656 l_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
3657
3658 l_par_original_ids VARCHAR2(32000);
3659 l_par_original_types VARCHAR2(32000);
3660 l_par_original_id VARCHAR2(32000);
3661 l_par_original_type VARCHAR2(32000);
3662
3663 l_chd_original_ids VARCHAR2(32000);
3664 l_chd_original_types VARCHAR2(32000);
3665 l_chd_original_id VARCHAR2(32000);
3666 l_chd_original_type VARCHAR2(32000);
3667
3668 l_child_ids VARCHAR2(32000);
3669 l_child_rel_types VARCHAR2(32000);
3670 l_child_id VARCHAR2(100);
3671 l_child_rel_type VARCHAR2(100);
3672 l_final_chd_ids VARCHAR2(32000);
3673
3674 l_parent_ids VARCHAR2(32000);
3675 l_parent_rel_types VARCHAR2(32000);
3676 l_parent_id VARCHAR2(100);
3677 l_parent_rel_type VARCHAR2(100);
3678
3679 l_temp_ids VARCHAR2(32000);
3680 l_temp_types VARCHAR2(32000);
3681
3682 l_Strut_Flag BOOLEAN := FALSE;
3683 l_kpi_names VARCHAR2(32000);
3684 l_par_flag BOOLEAN := FALSE;
3685
3686 CURSOR c_Source IS
3687 SELECT Source
3688 FROM BSC_SYS_DIM_LEVELS_B
3689 WHERE dim_level_id = p_dim_obj_id;
3690
3691 CURSOR c_Par_Dim_Ids IS
3692 SELECT Parent_Dim_Level_Id
3693 , Relation_Type
3694 , Dim_Level_Id
3695 FROM BSC_SYS_DIM_LEVEL_RELS
3696 WHERE dim_level_id = p_dim_obj_id;
3697
3698 CURSOR c_Child_Ids IS
3699 SELECT Dim_Level_Id
3700 FROM BSC_SYS_DIM_LEVEL_RELS
3701 WHERE parent_dim_level_id = p_dim_obj_id;
3702
3703
3704 CURSOR c_kpi_dim_set IS
3705 SELECT DISTINCT C.Name||'['||C.Indicator||']' Name, C.short_name
3706 FROM BSC_KPI_DIM_LEVELS_B A
3707 , BSC_SYS_DIM_LEVELS_B D
3708 , BSC_KPIS_VL C
3709 WHERE A.Level_Table_Name = D.Level_Table_Name
3710 AND D.Dim_Level_Id = p_dim_obj_id
3711 AND C.share_flag <> 2
3712 AND C.Indicator = A.Indicator;
3713
3714 CURSOR c_Kpi_Dim_Set1 IS
3715 SELECT DISTINCT C.Name||'['||C.Indicator||']' Name, C.short_name
3716 FROM BSC_KPI_DIM_LEVELS_VL A,
3717 BSC_SYS_DIM_LEVELS_VL B,
3718 BSC_KPIS_VL C
3719 WHERE A.LEVEL_TABLE_NAME=B.LEVEL_TABLE_NAME
3720 AND C.INDICATOR = A.INDICATOR
3721 AND C.SHARE_FLAG <> 2
3722 AND INSTR(', '||l_final_chd_ids||',', ', '||b.dim_level_id||',') > 0;
3723
3724 BEGIN
3725 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_REL_PUB.is_KPI_Flag_For_Dim_Obj_Rels Function');
3726
3727 FND_MSG_PUB.Initialize;
3728 IF (NOT BSC_UTILITY.isBscInProductionMode()) THEN
3729 RETURN NULL;
3730 END IF;
3731
3732 OPEN c_Source;
3733 FETCH c_Source INTO l_Source;
3734 CLOSE c_Source;
3735 IF (l_Source IS NULL) THEN
3736 RETURN NULL;
3737 END IF;
3738
3739 --check if any childs are of type Many to Many
3740 l_child_ids := TRIM(p_child_ids);
3741 l_child_rel_types := TRIM(p_child_rel_type);
3742 IF (l_child_ids IS NOT NULL) THEN
3743 WHILE (is_more( x_remain_id => l_child_ids
3744 , x_remain_rel_type => l_child_rel_types
3745 , x_id => l_child_id
3746 , x_rel_type => l_child_rel_type
3747 )) LOOP
3748 l_child_rel_type := NVL(l_child_rel_type, 1);
3749 IF (l_child_rel_type = 2) THEN
3750 --add children in parents
3751 IF (l_parent_ids IS NULL) THEN
3752 l_parent_ids := l_child_id||', ';
3753 l_parent_rel_types := l_child_rel_type||', ';
3754 ELSE
3755 l_parent_ids := l_parent_ids||l_child_id||', ';
3756 l_parent_rel_types := l_parent_rel_types||l_child_rel_type||', ';
3757 END IF;
3758 END IF;
3759
3760 --We need the children from M N relationship here
3761 --prepare final children list
3762 IF (l_final_chd_ids IS NULL) THEN
3763 l_final_chd_ids := l_child_id;
3764 ELSE
3765 l_final_chd_ids := l_final_chd_ids||', '||l_child_id;
3766 END IF;
3767 --END IF;
3768 END LOOP;
3769 END IF;
3770 FOR cd IN c_Child_Ids LOOP
3771 --contains all the initial children
3772 l_Strut_Flag := TRUE;
3773 IF (l_chd_original_ids IS NULL) OR (l_chd_original_types IS NULL) THEN
3774 l_chd_original_ids := cd.Dim_Level_Id;
3775 l_chd_original_types := 1;
3776 ELSE
3777 l_chd_original_ids := l_chd_original_ids||','||cd.Dim_Level_Id;
3778 l_chd_original_types := l_chd_original_types||','||1;
3779 END IF;
3780 l_child_ids := TRIM(p_child_ids);
3781 l_child_rel_types := TRIM(p_child_rel_type);
3782 IF (l_child_ids IS NOT NULL) THEN
3783 WHILE (is_more( x_remain_id => l_child_ids
3784 , x_remain_rel_type => l_child_rel_types
3785 , x_id => l_child_id
3786 , x_rel_type => l_child_rel_type
3787 )) LOOP
3788 l_child_rel_type := NVL(l_child_rel_type, 1);
3789 --DBMS_OUTPUT.PUT_LINE('l_child_id <'||l_child_id||'>');
3790 --DBMS_OUTPUT.PUT_LINE('l_child_rel_type <'||l_child_rel_type||'>');
3791 IF ((l_child_rel_type <> 2) AND (cd.Dim_Level_Id = l_child_id)) THEN
3792 l_Strut_Flag := FALSE;
3793 END IF;
3794 END LOOP;
3795 END IF;
3796 IF (l_Strut_Flag) THEN
3797 --DBMS_OUTPUT.PUT_LINE('cd.Dim_Level_Id <'||cd.Dim_Level_Id||'>');
3798 --DBMS_OUTPUT.PUT_LINE('cd.Relation_Type <1>');
3799 EXIT;
3800 END IF;
3801 END LOOP;
3802 --DBMS_OUTPUT.PUT_LINE('l_chd_original_ids <'||l_chd_original_ids||'>');
3803 --DBMS_OUTPUT.PUT_LINE('l_chd_original_types <'||l_chd_original_types||'>');
3804 --DBMS_OUTPUT.PUT_LINE('p_child_ids <'||p_child_ids||'>');
3805 --DBMS_OUTPUT.PUT_LINE('l_final_chd_ids <'||l_final_chd_ids||'>');
3806 --DBMS_OUTPUT.PUT_LINE('p_child_rel_type <'||p_child_rel_type||'>');
3807 --DBMS_OUTPUT.PUT_LINE('1111p_parent_ids <'||l_parent_ids||'>');
3808 IF (l_parent_ids IS NOT NULL) THEN
3809
3810 --l_parent_ids are only parents coming from M by N relationship
3811
3812 l_child_ids := l_parent_ids;
3813 l_child_rel_types := l_parent_rel_types;
3814 ELSE
3815 l_child_ids := NULL;
3816 l_child_rel_types := NULL;
3817 END IF;
3818 --IF (NOT l_Strut_Flag) THEN
3819 FOR cd IN c_par_dim_ids LOOP
3820 --contains all the initial parents
3821 l_Strut_Flag := TRUE;
3822 --DBMS_OUTPUT.PUT_LINE('cd.Parent_Dim_Level_Id <'||cd.Parent_Dim_Level_Id||'> cd.Relation_Type <'||cd.Relation_Type||'>');
3823 IF ((l_par_original_ids IS NULL) OR (l_par_original_types IS NULL)) THEN
3824 l_par_original_ids := cd.Parent_Dim_Level_Id;
3825 l_par_original_types := cd.Relation_Type;
3826 ELSE
3827 l_par_original_ids := l_par_original_ids||', '||cd.Parent_Dim_Level_Id;
3828 l_par_original_types := l_par_original_types||', '||cd.Relation_Type;
3829 END IF;
3830 --appending children coming from MN Relationsip to l_parent_ids
3831 l_parent_ids := NVL(l_child_ids, '')||TRIM(p_parent_ids);
3832 l_parent_rel_types := NVL(l_child_rel_types, '')||TRIM(p_parent_rel_type);
3833 IF (l_parent_ids IS NOT NULL) THEN
3834 --final parents iterator(Inside)
3835 WHILE (is_more( x_remain_id => l_parent_ids
3836 , x_remain_rel_type => l_parent_rel_types
3837 , x_id => l_parent_id
3838 , x_rel_type => l_parent_rel_type
3839 )) LOOP
3840 l_parent_rel_type := NVL(l_parent_rel_type, 1);
3841 --DBMS_OUTPUT.PUT_LINE('l_parent_id <'||l_parent_id||'>');
3842 --DBMS_OUTPUT.PUT_LINE('l_parent_rel_type <'||l_parent_rel_type||'>');
3843 IF ((cd.Parent_Dim_Level_Id = l_parent_id) AND (cd.Relation_Type = l_parent_rel_type)) THEN
3844 l_Strut_Flag := FALSE;
3845 EXIT;
3846 END IF;
3847 END LOOP;
3848 END IF;
3849 IF (l_Strut_Flag) THEN
3850 --DBMS_OUTPUT.PUT_LINE('cd.Parent_Dim_Level_Id <'||cd.Parent_Dim_Level_Id||'>');
3851 --DBMS_OUTPUT.PUT_LINE('cd.Relation_Type <'||cd.Relation_Type||'>');
3852 l_par_flag := TRUE; --Will be set to True if Structural Chnges are needed do to Change in Parent
3853 EXIT;
3854 END IF;
3855 END LOOP;
3856 --END IF;
3857 --DBMS_OUTPUT.PUT_LINE('l_par_original_ids <'||l_par_original_ids||'>');
3858 --DBMS_OUTPUT.PUT_LINE('l_par_original_types <'||l_par_original_types||'>');
3859 --DBMS_OUTPUT.PUT_LINE('p_parent_ids <'||NVL(l_child_ids, '')||p_parent_ids||'>');
3860 --DBMS_OUTPUT.PUT_LINE('p_parent_rel_type <'||NVL(l_child_rel_types, '')||p_parent_rel_type||'>');
3861 --IF (NOT l_Strut_Flag) THEN
3862 IF ((p_parent_ids IS NOT NULL) OR (l_child_ids IS NOT NULL)) THEN
3863 l_parent_ids := NVL(l_child_ids, '')||TRIM(p_parent_ids);
3864 l_parent_rel_types := NVL(l_child_rel_types, '')||TRIM(p_parent_rel_type);
3865
3866 --final parents iterator(Outside)
3867 WHILE (is_more( x_remain_id => l_parent_ids
3868 , x_remain_rel_type => l_parent_rel_types
3869 , x_id => l_parent_id
3870 , x_rel_type => l_parent_rel_type
3871 )) LOOP
3872 l_Strut_Flag := TRUE;
3873 l_temp_ids := l_par_original_ids;
3874 l_temp_types := l_par_original_types;
3875 WHILE (is_more( x_remain_id => l_temp_ids
3876 , x_remain_rel_type => l_temp_types
3877 , x_id => l_par_original_id
3878 , x_rel_type => l_par_original_type
3879 )) LOOP
3880 IF ((l_par_original_id = l_parent_id) AND (l_par_original_type = l_parent_rel_type)) THEN
3881 l_Strut_Flag := FALSE;
3882 EXIT;
3883 END IF;
3884 END LOOP;
3885 IF (l_Strut_Flag) THEN
3886 --DBMS_OUTPUT.PUT_LINE('l_parent_id <'||l_parent_id||'>');
3887 --DBMS_OUTPUT.PUT_LINE('l_parent_rel_type <'||l_parent_rel_type||'>');
3888 l_par_flag := TRUE;
3889 EXIT;
3890 END IF;
3891 END LOOP;
3892 END IF;
3893 --END IF;
3894 --IF (NOT l_Strut_Flag) THEN
3895 --DBMS_OUTPUT.PUT_LINE('<< l_final_chd_ids1 >>'||l_final_chd_ids);
3896 IF (l_final_chd_ids IS NOT NULL) THEN
3897 l_child_ids := TRIM(l_final_chd_ids);
3898 l_child_rel_types := NULL;
3899 --final_child iterator
3900 WHILE (is_more( x_remain_id => l_child_ids
3901 , x_remain_rel_type => l_child_rel_types
3902 , x_id => l_child_id
3903 , x_rel_type => l_child_rel_type
3904 )) LOOP
3905 l_Strut_Flag := TRUE;
3906 l_temp_ids := l_chd_original_ids;
3907 l_temp_types := l_chd_original_types;
3908 ---original child iterator
3909 WHILE (is_more( x_remain_id => l_temp_ids
3910 , x_remain_rel_type => l_temp_types
3911 , x_id => l_chd_original_id
3912 , x_rel_type => l_chd_original_type
3913 )) LOOP
3914 -- Added l_final_chd_types comparison for Bug#5057436
3915 IF ((l_chd_original_id = l_child_id) AND (l_chd_original_type = l_child_rel_type)) THEN
3916 l_Strut_Flag := FALSE;
3917 EXIT;
3918 END IF;
3919 END LOOP;
3920 IF (l_Strut_Flag) THEN
3921 --DBMS_OUTPUT.PUT_LINE('l_child_id <'||l_child_id||'>');
3922 --DBMS_OUTPUT.PUT_LINE('l_child_rel_type <'||l_child_rel_type||'>');
3923 EXIT;
3924 END IF;
3925 END LOOP;
3926 END IF;
3927 --END IF;
3928
3929 --Check to see if any chld DOs have beed removed. If yes, they will be added in l_final_chd_ids for the warning
3930 l_temp_ids := l_chd_original_ids;
3931 l_temp_types := l_chd_original_types;
3932
3933 --DBMS_OUTPUT.PUT_LINE('<< l_final_chd_ids >>'||l_final_chd_ids);
3934 --DBMS_OUTPUT.PUT_LINE('<< l_chd_original_ids >>'||l_chd_original_ids);
3935
3936 WHILE (is_more( x_remain_id => l_temp_ids
3937 , x_remain_rel_type => l_temp_types
3938 , x_id => l_chd_original_id
3939 , x_rel_type => l_chd_original_type
3940 )) LOOP
3941
3942 IF (INSTR(', '||l_final_chd_ids||',', ', '||l_chd_original_id||',') = 0) THEN
3943 IF (l_final_chd_ids IS NULL) THEN
3944 l_final_chd_ids := l_chd_original_id;
3945 ELSE
3946 l_final_chd_ids := l_final_chd_ids ||', '||l_chd_original_id;
3947 END IF;
3948 END IF;
3949 END LOOP;
3950 --DBMS_OUTPUT.PUT_LINE('<< l_final_chd_ids >>'||l_final_chd_ids);
3951
3952 IF (l_strut_flag) THEN
3953 IF (l_par_flag) THEN
3954 FOR cd IN c_kpi_dim_set LOOP
3955 IF(NOT (l_source = 'PMF' AND cd.short_name is NULL)) THEN
3956 IF (l_kpi_names IS NULL) THEN
3957 l_kpi_names := cd.Name;
3958 ELSIF (INSTR(', '||l_kpi_names||', ', ', '||cd.Name||', ') = 0 ) THEN
3959 l_kpi_names := l_kpi_names||', '||cd.Name;
3960 END IF;
3961 END IF;
3962 END LOOP;
3963 END IF;
3964
3965
3966
3967 IF (l_final_chd_ids IS NOT NULL) THEN
3968 FOR cd IN c_kpi_dim_set1 LOOP
3969 IF(NOT (l_source = 'PMF' AND cd.short_name is NULL)) THEN
3970 IF (l_kpi_names IS NULL) THEN
3971 l_kpi_names := cd.Name;
3972 ELSIF (INSTR(', '||l_kpi_names||', ', ', '||cd.Name||', ') = 0 ) THEN
3973 l_kpi_names := l_kpi_names||', '||cd.Name;
3974 END IF;
3975 END IF;
3976 END LOOP;
3977 END IF;
3978 END IF;
3979
3980
3981 --DBMS_OUTPUT.PUT_LINE('<< l_kpi_names >>');
3982 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_kpi_names, 1, 200));
3983 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_kpi_names, 201, 200));
3984 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_kpi_names, 401, 200));
3985 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_kpi_names, 601, 200));
3986 --DBMS_OUTPUT.PUT_LINE(SUBSTR(l_kpi_names, 801, 200));
3987 IF ((l_strut_flag) AND (l_kpi_names IS NOT NULL)) THEN
3988 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
3989 FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpi_names);
3990 FND_MSG_PUB.ADD;
3991 RAISE FND_API.G_EXC_ERROR;
3992 END IF;
3993 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_REL_PUB.is_KPI_Flag_For_Dim_Obj_Rels Function');
3994 RETURN NULL;
3995 EXCEPTION
3996 WHEN FND_API.G_EXC_ERROR THEN
3997 IF (l_Msg_Data IS NULL) THEN
3998 FND_MSG_PUB.Count_And_Get
3999 ( p_encoded => FND_API.G_FALSE
4000 , p_count => l_msg_count
4001 , p_data => l_Msg_Data
4002 );
4003 END IF;
4004 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR BSC_BIS_DIM_REL_PUB.is_KPI_Flag_For_Dim_Obj_Rels');
4005 RETURN l_Msg_Data;
4006 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4007 IF (l_Msg_Data IS NULL) THEN
4008 FND_MSG_PUB.Count_And_Get
4009 ( p_encoded => FND_API.G_FALSE
4010 , p_count => l_msg_count
4011 , p_data => l_Msg_Data
4012 );
4013 END IF;
4014 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR BSC_BIS_DIM_REL_PUB.is_KPI_Flag_For_Dim_Obj_Rels');
4015 RETURN l_Msg_Data;
4016 WHEN OTHERS THEN
4017 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||SQLERRM);
4018 RETURN NULL;
4019 END is_KPI_Flag_For_Dim_Obj_Rels;
4020 /********************************************************************************/
4021 FUNCTION check_config_impact_rels
4022 (
4023 p_dim_obj_id IN NUMBER
4024 , p_parent_ids IN VARCHAR2
4025 , p_parent_rel_type IN VARCHAR2
4026 , p_parent_rel_column IN VARCHAR2
4027 , p_parent_data_type IN VARCHAR2
4028 , p_parent_data_source IN VARCHAR2
4029 , p_child_ids IN VARCHAR2
4030 , p_child_rel_type IN VARCHAR2
4031 , p_child_rel_column IN VARCHAR2
4032 , p_child_data_type IN VARCHAR2
4033 , p_child_data_source IN VARCHAR2
4034 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
4035 ) RETURN VARCHAR2 IS
4036 l_kpi_id NUMBER;
4037 l_dimset_id NUMBER;
4038 l_parent_ids VARCHAR2(32000);
4039 l_child_ids VARCHAR2(32000);
4040 l_no_rels NUMBER;
4041 l_msg_count NUMBER;
4042 l_Msg_Data VARCHAR2(32000);
4043 x_return_status VARCHAR2(32000);
4044 x_msg_count NUMBER;
4045 x_msg_data VARCHAR2(32000);
4046 TYPE index_by_table_kpi IS Record
4047 (
4048 kpi_id NUMBER
4049 , dim_set_id NUMBER
4050 );
4051
4052 TYPE index_by_table_type_kpi IS TABLE OF index_by_table_kpi INDEX BY BINARY_INTEGER;
4053 TYPE index_by_table IS Record
4054 ( p_no_dim_object VARCHAR2(32000)
4055 );
4056 TYPE index_by_table_type IS TABLE OF index_by_table INDEX BY BINARY_INTEGER;
4057 dim_objs_in_dimset index_by_table_type_kpi;
4058 dimobjs_array index_by_table_type;
4059
4060 CURSOR cr_kpi_dim_set IS
4061 SELECT INDICATOR,DIM_SET_ID
4062 FROM BSC_KPI_DIM_LEVEL_PROPERTIES
4063 WHERE DIM_LEVEL_ID = p_dim_obj_id;
4064
4065 i NUMBER;
4066
4067 BEGIN
4068 SAVEPOINT sp_before_rel_config;
4069 IF(p_parent_ids IS NOT NULL OR p_child_ids IS NOT NULL) THEN
4070 --DBMS_OUTPUT.PUT_LINE('BEFORE ASSIGN DIM OBJE RELS LOOP' );
4071 Assign_New_Dim_Obj_Rels
4072 ( p_dim_obj_id => p_dim_obj_id
4073 , p_parent_ids => p_parent_ids
4074 , p_parent_rel_type => p_parent_rel_type
4075 , p_parent_rel_column => p_parent_rel_column
4076 , p_parent_data_type => p_parent_data_type
4077 , p_parent_data_source => p_parent_data_source
4078 , p_child_ids => p_child_ids
4079 , p_child_rel_type => p_child_rel_type
4080 , p_child_rel_column => p_child_rel_column
4081 , p_child_data_type => p_child_data_type
4082 , p_child_data_source => p_child_data_source
4083 , p_time_stamp => p_time_stamp
4084 , p_is_not_config => FALSE
4085 , x_return_status => x_return_status
4086 , x_msg_count => x_msg_count
4087 , x_msg_data => x_msg_data
4088 );
4089 --DBMS_OUTPUT.PUT_LINE('X RETURN STATUS IS '|| x_return_status );
4090 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4091 OPEN cr_kpi_dim_set;
4092 -- bug#3405498 meastmon 28-jan-2004: The following is not supported in 8i
4093 --FETCH cr_kpi_dim_set BULK COLLECT INTO dim_objs_in_dimset;
4094 dim_objs_in_dimset.delete;
4095 i := 0;
4096 LOOP
4097 FETCH cr_kpi_dim_set INTO l_kpi_id, l_dimset_id;
4098 EXIT WHEN cr_kpi_dim_set%NOTFOUND;
4099 i := i+1;
4100 dim_objs_in_dimset(i).kpi_id := l_kpi_id;
4101 dim_objs_in_dimset(i).dim_set_id := l_dimset_id;
4102 END LOOP;
4103 CLOSE cr_kpi_dim_set;
4104
4105 --DBMS_OUTPUT.PUT_LINE('BEFORE MAIN LOOP' );
4106 FOR index_loop IN 1..(dim_objs_in_dimset.COUNT) LOOP
4107 l_kpi_id := dim_objs_in_dimset(index_loop).kpi_id;
4108 l_dimset_id := dim_objs_in_dimset(index_loop).dim_set_id;
4109
4110 SELECT COUNT(INDICATOR) INTO l_no_rels
4111 FROM BSC_KPI_DIM_LEVELS_B
4112 WHERE INDICATOR = l_kpi_id
4113 AND DIM_SET_ID = l_dimset_id
4114 AND PARENT_LEVEL_INDEX >= 0;
4115 --DBMS_OUTPUT.PUT_LINE('kpi_id :- '||l_kpi_id||'dimset_id :- '||l_dimset_id||'No of rels are :- '||l_no_rels );
4116 IF(l_no_rels > BSC_BIS_KPI_MEAS_PUB.CONFIG_LIMIT_RELS) THEN
4117 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SUMMARY_LVL');
4118 FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
4119 FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
4120 FND_MSG_PUB.ADD;
4121 ROLLBACK TO sp_before_rel_config;
4122 RAISE FND_API.G_EXC_ERROR;
4123 END IF;
4124 END LOOP;
4125 END IF;
4126 END IF;
4127 ROLLBACK TO sp_before_rel_config;
4128 IF(cr_kpi_dim_set%ISOPEN) THEN
4129 CLOSE cr_kpi_dim_set;
4130 END IF ;
4131 RETURN NULL;
4132 EXCEPTION
4133 WHEN FND_API.G_EXC_ERROR THEN
4134 IF (l_Msg_Data IS NULL) THEN
4135 FND_MSG_PUB.Count_And_Get
4136 ( p_encoded => FND_API.G_FALSE
4137 , p_count => l_msg_count
4138 , p_data => l_Msg_Data
4139 );
4140 END IF;
4141 IF(cr_kpi_dim_set%ISOPEN) THEN
4142 CLOSE cr_kpi_dim_set;
4143 END IF ;
4144 RETURN l_Msg_Data;
4145 WHEN OTHERS THEN
4146 ROLLBACK TO sp_before_rel_config;
4147 IF(cr_kpi_dim_set%ISOPEN) THEN
4148 CLOSE cr_kpi_dim_set;
4149 END IF ;
4150 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||SQLERRM);
4151 RETURN NULL;
4152 END check_config_impact_rels;
4153 --**************************************************************************
4154 FUNCTION check_invalid_pmf_view_inrel
4155 (
4156 p_dim_obj_id IN NUMBER
4157 , p_parent_ids IN VARCHAR2
4158 , p_parent_rel_type IN VARCHAR2
4159 , p_parent_rel_column IN VARCHAR2
4160 , p_parent_data_type IN VARCHAR2
4161 , p_parent_data_source IN VARCHAR2
4162 , p_child_ids IN VARCHAR2
4163 , p_child_rel_type IN VARCHAR2
4164 , p_child_rel_column IN VARCHAR2
4165 , p_child_data_type IN VARCHAR2
4166 , p_child_data_source IN VARCHAR2
4167 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
4168 ) RETURN VARCHAR2 IS
4169 l_msg_count NUMBER;
4170 l_Msg_Data VARCHAR2(32000);
4171 x_return_status VARCHAR2(32000);
4172 x_msg_count NUMBER;
4173 x_msg_data VARCHAR2(32000);
4174 l_short_name VARCHAR2(32000);
4175
4176 CURSOR C_SHORT_NAMES_IDS IS
4177 SELECT short_name
4178 FROM bsc_sys_dim_levels_vl
4179 WHERE INSTR(','||p_child_ids ||',',','||dim_level_id||',') > 0;
4180
4181
4182
4183 BEGIN
4184 SAVEPOINT sp_before_rel_view;
4185 --DBMS_OUTPUT.PUT_LINE('In check_invalid_pmf_view_inrel: The child dimensions are :-' ||p_child_ids);
4186 --DBMS_OUTPUT.PUT_LINE('In check_invalid_pmf_view_inrel: The parent dimensions are :-'|| p_parent_ids);
4187 IF(p_parent_ids IS NOT NULL OR p_child_ids IS NOT NULL) THEN
4188 --DBMS_OUTPUT.PUT_LINE('BEFORE ASSIGN DIM OBJE RELS LOOP' );
4189 Assign_New_Dim_Obj_Rels
4190 ( p_dim_obj_id => p_dim_obj_id
4191 , p_parent_ids => p_parent_ids
4192 , p_parent_rel_type => p_parent_rel_type
4193 , p_parent_rel_column => p_parent_rel_column
4194 , p_parent_data_type => p_parent_data_type
4195 , p_parent_data_source => p_parent_data_source
4196 , p_child_ids => p_child_ids
4197 , p_child_rel_type => p_child_rel_type
4198 , p_child_rel_column => p_child_rel_column
4199 , p_child_data_type => p_child_data_type
4200 , p_child_data_source => p_child_data_source
4201 , p_time_stamp => p_time_stamp
4202 , p_is_not_config => FALSE
4203 , x_return_status => x_return_status
4204 , x_msg_count => x_msg_count
4205 , x_msg_data => x_msg_data
4206 );
4207 --DBMS_OUTPUT.PUT_LINE('X RETURN STATUS IS '|| x_return_status );
4208 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4209 --DBMS_OUTPUT.PUT_LINE('assign new dim rel is success');
4210 FOR CD IN C_SHORT_NAMES_IDS LOOP
4211 --DBMS_OUTPUT.PUT_LINE('Calling validate pmf view for :-'|| CD.SHORT_NAME);
4212 BSC_BIS_DIM_OBJ_PUB.Validate_PMF_Views
4213 (
4214 p_Dim_Obj_Short_Name => CD.SHORT_NAME
4215 , p_Dim_Obj_View_Name => NULL
4216 , x_Return_Status => x_return_status
4217 , x_Msg_Count => x_msg_count
4218 , x_Msg_Data => x_msg_data
4219 );
4220 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4221 RAISE FND_API.G_EXC_ERROR;
4222 --DBMS_OUTPUT.PUT_LINE('faliled the check for :-'|| CD.SHORT_NAME);
4223 END IF;
4224
4225 END LOOP;
4226 --DBMS_OUTPUT.PUT_LINE('For present dimension object :- '|| p_dim_obj_id);
4227
4228 SELECT SHORT_NAME
4229 INTO l_short_name
4230 FROM BSC_SYS_DIM_LEVELS_VL
4231 WHERE DIM_LEVEL_ID = p_dim_obj_id;
4232 --DBMS_OUTPUT.PUT_LINE('For present dimension object shortname :- '|| l_short_name);
4233 BSC_BIS_DIM_OBJ_PUB.Validate_PMF_Views
4234 (
4235 p_Dim_Obj_Short_Name => l_short_name
4236 , p_Dim_Obj_View_Name => NULL
4237 , x_Return_Status => x_return_status
4238 , x_Msg_Count => x_msg_count
4239 , x_Msg_Data => x_msg_data
4240 );
4241 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4242 RAISE FND_API.G_EXC_ERROR;
4243 --DBMS_OUTPUT.PUT_LINE('ooops faliled the check for present dim :-'|| l_short_name);
4244 END IF;
4245 END IF;
4246 ROLLBACK TO sp_before_rel_view;
4247 --DBMS_OUTPUT.PUT_LINE('validte view is succeed');
4248 END IF;
4249 RETURN FND_API.G_RET_STS_SUCCESS;
4250 EXCEPTION
4251 WHEN FND_API.G_EXC_ERROR THEN
4252 IF (x_msg_data IS NULL) THEN
4253 FND_MSG_PUB.Count_And_Get
4254 ( p_encoded => FND_API.G_FALSE
4255 , p_count => x_msg_count
4256 , p_data => x_msg_data
4257 );
4258 END IF;
4259 IF(C_SHORT_NAMES_IDS%ISOPEN) THEN
4260 CLOSE C_SHORT_NAMES_IDS;
4261 END IF;
4262 ROLLBACK TO sp_before_rel_view;
4263 RETURN x_msg_data;
4264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4265 IF (x_msg_data IS NULL) THEN
4266 FND_MSG_PUB.Count_And_Get
4267 ( p_encoded => FND_API.G_FALSE
4268 , p_count => x_msg_count
4269 , p_data => x_msg_data
4270 );
4271 END IF;
4272 IF(C_SHORT_NAMES_IDS%ISOPEN) THEN
4273 CLOSE C_SHORT_NAMES_IDS;
4274 END IF;
4275 ROLLBACK TO sp_before_rel_view;
4276 RETURN x_msg_data;
4277
4278 WHEN OTHERS THEN
4279 ROLLBACK TO sp_before_rel_view;
4280 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||SQLERRM);
4281 RETURN x_msg_data;
4282 END check_invalid_pmf_view_inrel;
4283
4284 --***************************************************************
4285
4286 END BSC_BIS_DIM_REL_PUB;