1 PACKAGE BSC_BIS_DIMENSION_PUB AS
2 /* $Header: BSCGPMDS.pls 120.3 2006/01/06 03:22:23 akoduri noship $ */
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 | BSCCPMDB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for Dimension in PMF & Dimension Group in BSC |
13 REM | part of PMD APIs |
14 REM | |
15 REM | NOTES |
16 REM | 02-MAY-2003 PAJOHRI Created. |
17 REM | 13-SEP-2003 MAHRAO Fix for bug# 3099977, Added p_create_view flag |
18 REM | 04-NOV-2003 PAJOHRI Bug #3152258 |
19 REM | 04-NOV-2003 PAJOHRI Bug #3220613 |
20 REM | 04-NOV-2003 PAJOHRI Bug #3232366 |
21 REM | 08-DEC-2003 KYADAMAK Bug #3225685 |
22 REM | 12-APR-2004 PAJOHRI Bug #3426566, created the following new functions|
23 REM | Get_Bis_Dimension_ID() |
24 REM | Get_Bsc_Dimension_ID() |
25 REM | 09-AUG-2004 sawu Added constant c_BSC_DIM |
26 REM | 11-AUG-2004 sawu Added create_dimension() for bug#3819855 with |
27 REM | p_is_default_short_name |
28 REM | 08-SEP-2004 visuri Added Dim_With_Single_Dim_Obj() and |
29 REM | Is_Dim_Empty() for bug #3784852 |
30 REM | 09-SEP-2004 visuri Shifted Remove_Empty_Dims_For_DimSet() from |
31 REM | BSC_BIS_KPI_MEAS_PUB for bug #3784852 |
32 REM | 27-OCT-2004 sawu Bug#3947903: added Is_Objective_Assigned() |
33 REM | 06-JUN-2005 mdamle Enh#4403547 - Set default p_commit to false for |
34 REM | dim. group apis called from EOs |
35 REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
36 REM | 06-Jan-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
37 REM +=======================================================================+
38 */
39
40 UNASSIGNED_DIM CONSTANT VARCHAR2(10) := 'UNASSIGNED';
41 c_BSC_DIM CONSTANT VARCHAR2(8) := 'BSC_DIM_';
42 /*********************************************************************************
43 CREATE DIMENSION
44 *********************************************************************************/
45 PROCEDURE Create_Dimension
46 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
47 , p_dim_short_name IN VARCHAR2
48 , p_display_name IN VARCHAR2
49 , p_description IN VARCHAR2
50 , p_dim_obj_short_names IN VARCHAR2
51 , p_application_id IN NUMBER
52 , p_create_view IN NUMBER := 0
53 , p_hide IN VARCHAR2 := FND_API.G_FALSE
54 , x_return_status OUT NOCOPY VARCHAR2
55 , x_msg_count OUT NOCOPY NUMBER
56 , x_msg_data OUT NOCOPY VARCHAR2
57 );
58
59 PROCEDURE Create_Dimension
60 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
61 , p_dim_short_name IN VARCHAR2
62 , p_display_name IN VARCHAR2
63 , p_description IN VARCHAR2
64 , p_dim_obj_short_names IN VARCHAR2
65 , p_application_id IN NUMBER
66 , p_create_view IN NUMBER := 0
67 , p_hide IN VARCHAR2 := FND_API.G_FALSE
68 , p_is_default_short_name IN VARCHAR2
69 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 );
74 /*********************************************************************************
75 ASSIGN DIMENSION OBJECTS TO DIMENSION
76 *********************************************************************************/
77
78 PROCEDURE Assign_Dimension_Objects
79 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
80 , p_dim_short_name IN VARCHAR2
81 , p_dim_obj_short_names IN VARCHAR2
82 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
83 , p_create_view IN NUMBER := 0
84 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
85 , x_return_status OUT NOCOPY VARCHAR2
86 , x_msg_count OUT NOCOPY NUMBER
87 , x_msg_data OUT NOCOPY VARCHAR2
88 );
89
90 /*********************************************************************************
91 ASSIGN OR UPDATE A DIMENSION OBJECT TO DIMENSION
92 *********************************************************************************/
93
94 PROCEDURE Assign_Dimension_Object
95 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
96 , p_dim_short_name IN VARCHAR2
97 , p_dim_obj_short_name IN VARCHAR2
98 , p_comp_flag IN NUMBER
99 , p_no_items IN NUMBER
100 , p_parent_in_tot IN NUMBER
101 , p_total_flag IN NUMBER
102 , p_default_value IN VARCHAR2
103 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
104 , p_create_view IN NUMBER := 0
105 , p_where_clause IN VARCHAR2 := NULL
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 );
110
111 /*********************************************************************************
112 ASSIGN & UNASSIGN DIMENSION OBJECTS TO DIMENSION
113 *********************************************************************************/
114
115 PROCEDURE Assign_Unassign_Dim_Objs
116 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
117 , p_dim_short_name IN VARCHAR2
118 , p_assign_dim_obj_names IN VARCHAR2
119 , p_unassign_dim_obj_names IN VARCHAR2
120 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
121 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
122 , x_return_status OUT NOCOPY VARCHAR2
123 , x_msg_count OUT NOCOPY NUMBER
124 , x_msg_data OUT NOCOPY VARCHAR2
125 );
126
127 /*********************************************************************************
128 UPDATE DIMENSION
129 *********************************************************************************/
130 PROCEDURE Update_Dimension
131 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
132 , p_dim_short_name IN VARCHAR2
133 , p_display_name IN VARCHAR2
134 , p_description IN VARCHAR2
135 , p_application_id IN NUMBER
136 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
137 , p_hide IN VARCHAR2 := FND_API.G_FALSE
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_msg_count OUT NOCOPY NUMBER
140 , x_msg_data OUT NOCOPY VARCHAR2
141 );
142
143 /*********************************************************************************
144 UPDATE DIMENSION
145 *********************************************************************************/
146 PROCEDURE Update_Dimension
147 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
148 , p_dim_short_name IN VARCHAR2
149 , p_display_name IN VARCHAR2
150 , p_description IN VARCHAR2
151 , p_application_id IN NUMBER
152 , p_dim_obj_short_names IN VARCHAR2
153 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
154 , p_hide IN VARCHAR2 := FND_API.G_FALSE
155 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_msg_count OUT NOCOPY NUMBER
158 , x_msg_data OUT NOCOPY VARCHAR2
159 );
160
161 /*********************************************************************************
162 UPDATE DIMENSION
163 *********************************************************************************/
164 PROCEDURE Update_Dimension
165 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
166 , p_dim_short_name IN VARCHAR2
167 , p_display_name IN VARCHAR2
168 , p_description IN VARCHAR2
169 , p_application_id IN NUMBER
170 , p_assign_dim_obj_names IN VARCHAR2
171 , p_unassign_dim_obj_names IN VARCHAR2
172 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
173 , p_hide IN VARCHAR2 := FND_API.G_FALSE
177 , x_msg_data OUT NOCOPY VARCHAR2
174 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
175 , x_return_status OUT NOCOPY VARCHAR2
176 , x_msg_count OUT NOCOPY NUMBER
178 );
179 /*********************************************************************************
180 DELETE DIMENSION
181 *********************************************************************************/
182 PROCEDURE Delete_Dimension
183 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
184 , p_dim_short_name IN VARCHAR2
185 , x_return_status OUT NOCOPY VARCHAR2
186 , x_msg_count OUT NOCOPY NUMBER
187 , x_msg_data OUT NOCOPY VARCHAR2
188 );
189
190 /*********************************************************************************
191 UNASSIGN DIMENSION OBJECTS FROM DIMENSION
192 *********************************************************************************/
193 PROCEDURE Unassign_Dimension_Objects
194 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
195 , p_dim_short_name IN VARCHAR2
196 , p_dim_obj_short_names IN VARCHAR2
197 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
198 , x_return_status OUT NOCOPY VARCHAR2
199 , x_msg_count OUT NOCOPY NUMBER
200 , x_msg_data OUT NOCOPY VARCHAR2
201 );
202 /*********************************************************************************
203 GET DIMENSION SOURCE
204 *********************************************************************************/
205 FUNCTION Get_Dimension_Source
206 (
207 p_short_Name IN VARCHAR2
208 ) RETURN VARCHAR2;
209 --=============================================================================
210 PROCEDURE Get_Lvl_Dtls
211 ( p_dim_lvl_shrt_name VARCHAR2
212 , x_source OUT NOCOPY VARCHAR2
213 , x_dim_lvl_name OUT NOCOPY VARCHAR2
214 , x_dim_lvl_view_name OUT NOCOPY VARCHAR2
215 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
216 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
217 , x_return_status OUT NOCOPY VARCHAR2
218 , x_msg_count OUT NOCOPY NUMBER
219 , x_msg_data OUT NOCOPY VARCHAR2
220 );
221 --=============================================================================
222 PROCEDURE Get_Spec_Edw_Dtls
223 ( p_dim_lvl_shrt_name VARCHAR2
224 , x_dim_lvl_view_name OUT NOCOPY VARCHAR2
225 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
226 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
227 , x_return_status OUT NOCOPY VARCHAR2
228 , x_msg_count OUT NOCOPY NUMBER
229 , x_msg_data OUT NOCOPY VARCHAR2
230 );
231 --=============================================================================
232 PROCEDURE Get_Gene_Edw_Dtls
233 ( p_dim_lvl_shrt_name VARCHAR2
234 , x_dim_lvl_view_name IN OUT NOCOPY VARCHAR2
235 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
236 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
237 , x_return_status OUT NOCOPY VARCHAR2
238 , x_msg_count OUT NOCOPY NUMBER
239 , x_msg_data OUT NOCOPY VARCHAR2
240 );
241 --=============================================================================
242 FUNCTION is_KPI_Flag_For_DimProp_Change
243 ( p_dim_short_name IN VARCHAR2
244 , p_dim_Obj_Short_Name IN VARCHAR2
245 , p_Default_Value IN VARCHAR2
246 ) RETURN VARCHAR2;
247 --=============================================================================
248 FUNCTION is_KPI_Flag_For_Dimension
249 ( p_Dim_Short_Name IN VARCHAR2
250 , p_Dim_Obj_Short_Names IN VARCHAR2
251 ) RETURN VARCHAR2;
252 --*********** Checking configuration impacting adding dimension objects*****************
253 FUNCTION is_config_impact_dim
254 ( p_Dim_Short_Name IN VARCHAR2
255 , p_Dim_Obj_Short_Names IN VARCHAR2
256 ) RETURN VARCHAR2;
257 /*********************************************************************************************
258 Returns the Dim_Group_ID of BIS Dimension
259 *********************************************************************************************/
260 FUNCTION Get_Bis_Dimension_ID
261 ( p_Short_Name IN BIS_DIMENSIONS.Short_Name%TYPE
262 ) RETURN NUMBER;
263
264 /*********************************************************************************************
265 Returns the Dim_Group_ID of BSC Dimension
266 *********************************************************************************************/
267 FUNCTION Get_Bsc_Dimension_ID
268 ( p_Short_Name IN BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE
269 ) RETURN NUMBER;
270
271 /*********************************************************************************************
272 Returns the Name of BSC Dimension
273 *********************************************************************************************/
274 FUNCTION Get_Bsc_Dimension_Name
275 ( p_Short_Name IN BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE
276 ) RETURN VARCHAR2;
277 /*********************************************************************************************
278 Checks if a Dimension is Attached to a Objective
282 ) RETURN BOOLEAN;
279 *********************************************************************************************/
280 FUNCTION Is_Dimension_in_Ind
281 ( p_dim_group_id IN BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE
283
284 /**************************************************************************************************************
285 API TO Check if the Dimension/Dimensions is Empty
286 ****************************************************************************************************************/
287 FUNCTION Is_Dim_Empty
288 (
289 p_dim_short_names IN VARCHAR
290
291 ) RETURN VARCHAR2 ;
292 /**************************************************************************************************************
293 API TO Check if the Dimension/Dimensions has Single Dimension Object
294 ****************************************************************************************************************/
295 FUNCTION Dim_With_Single_Dim_Obj
296 (
297 p_dim_short_names IN VARCHAR
298
299 ) RETURN VARCHAR2 ;
300 /*********************************************************************************
301 API TO REMOVE EMPTY DIMENSION FROM DIMENSION SET
302 *********************************************************************************/
303 PROCEDURE Remove_Empty_Dims_For_DimSet
304 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
305 , p_dim_short_names IN VARCHAR2
306 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
307 , x_return_status OUT NOCOPY VARCHAR2
308 , x_msg_count OUT NOCOPY NUMBER
309 , x_msg_data OUT NOCOPY VARCHAR2
310 );
311
312 /*********************************************************************************
313 API TO Check if Dimension is Assigned to any KPI
314 *********************************************************************************/
315 FUNCTION Is_Objective_Assigned
316 (
317 p_dim_short_name IN VARCHAR2
318 ) RETURN VARCHAR2;
319
320
321 END BSC_BIS_DIMENSION_PUB;