1 package BSC_DIMENSION_LEVELS_PUB as
2 /* $Header: BSCPDMLS.pls 120.2 2005/11/22 08:00:27 ppandey noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPDMLS.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Public spec version. |
19 | This package creates a BSC Dimension (Level). |
20 | History: |
21 | 24-APR-2003 is_Child_Parent() Added by ADRAO for Enh#2901823 |
22 | 07-MAY-2003 Retrieve_Relationship() Added by ADRAO for change Enh#2901823 |
23 | 05-JUN-2003 ADRAO Added who column for Dimension Objects and modified |
24 | APIs for Granular Locking - PMD |
25 | 14-JUN-03 mahrao Added Translate_dimesnsion_level procedure for enh# 2842894 |
26 | 31-JUL-03 mahrao Increased the size of Bsc_Dim_Level_Long_Name and Bsc_Dim_Level_Help
27 | in Bsc_Dim_Level_Rec_Type record type for bug# 3030788.
28 | 02-MAR-04 ankgoel Bug #3464470 |
29 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
30 | 09-Feb-05 ankgoel Bug#4172055 LUD validations for dim_lvls_by_group |
31 | 15-FEB-05 ppandey Enh #4016669, support ID, Value for Autogenerated Dimension Obj|
32 | 28-JUN-05 arhegde bug# 4323426 Increased length of |
33 | Bsc_Dim_Level_Rec_Type.Bsc_Parent_Level_Name |
34 +======================================================================================+
35 */
36
37 TYPE Bsc_Dim_Level_Rec_Type is RECORD(
38 Bsc_Dim_Comp_Disp_Name varchar2(45)
39 ,Bsc_Dim_Level_Long_Name BSC_SYS_DIM_LEVELS_TL.NAME%TYPE
40 ,Bsc_Dim_Level_Help BSC_SYS_DIM_LEVELS_TL.HELP%TYPE
41 ,Bsc_Dim_Tot_Disp_Name varchar2(45)
42 ,Bsc_Language varchar2(5)
43 ,Bsc_Level_Abbreviation varchar2(24)
44 ,Bsc_Level_Column_Name varchar2(30)
45 ,Bsc_Level_Column_Type varchar2(1)
46 ,Bsc_Level_Comp_Order_By number
47 ,Bsc_Level_Custom_Group number
48 ,Bsc_Level_Disp_Key_Size number
49 ,Bsc_Level_Id number
50 ,Bsc_Level_Index number
51 ,Bsc_Level_Name varchar2(30)
52 ,Bsc_Level_Name_Column varchar2(30)
53 ,Bsc_Level_Pk_Key varchar2(30)
54 ,Bsc_Level_Short_Name varchar2(30)
55 ,Bsc_Level_User_Key_Size number
56 ,Bsc_Level_Table_Type number
57 ,Bsc_Level_Value_Order_By number
58 ,Bsc_Level_View_Name varchar2(30)
59 ,Bsc_Pk_Col varchar2(30)
60 ,Bsc_Source varchar2(10)
61 ,Bsc_Source_Language varchar2(5)
62 ,Bsc_Source_Level_Long_Name varchar2(255)
63 ,Bsc_Relation_Column varchar2(100)
64 ,Bsc_Relation_Type number
65 ,Bsc_Parent_Level_Id number
66 ,Bsc_Parent_Level_Index number
67 ,Bsc_Parent_Level_Short_Name varchar2(30)
68 ,Bsc_Parent_Level_Source varchar2(10)
69 ,Bsc_Flag number:=0
70 ,Source varchar2(10) /* PMF source need */
71 ,Bsc_Parent_Level_Name BSC_SYS_DIM_LEVELS_TL.NAME%TYPE /* PMF source need */
72 ,Bsc_Data_Source_Type varchar2(30) /* Relationship Data Source type TABLE/VIEW or API */
73 ,Bsc_Data_Source varchar2(240) /* Relationship Data Source Name */
74 -- PMD
75 ,Bsc_Created_By BSC_SYS_DIM_LEVELS_B.created_by%TYPE -- PMD: Dim Level WHO columns
76 ,Bsc_Creation_Date BSC_SYS_DIM_LEVELS_B.creation_date%TYPE -- For granular locking
77 ,Bsc_Last_Updated_By BSC_SYS_DIM_LEVELS_B.last_updated_by%TYPE -- PMD
78 ,Bsc_Last_Update_Date BSC_SYS_DIM_LEVELS_B.last_update_date%TYPE -- PMD
79 ,Bsc_Last_Update_Login BSC_SYS_DIM_LEVELS_B.last_update_login%TYPE -- PMD
80 );
81
82
83 TYPE Bsc_Dim_Level_Tbl_Type IS TABLE OF Bsc_Dim_Level_Rec_Type
84 INDEX BY BINARY_INTEGER;
85
86 -- Procedure to Create all pertaining information for a given Dimension Level.
87 -- Though all procedures in this package may be called individually, this
88 -- procedure is the entry point to populate all meta data for dimension levels.
89 procedure Create_Dim_Level(
90 p_commit IN varchar2 := FND_API.G_FALSE
91 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
92 ,x_return_status OUT NOCOPY varchar2
93 ,x_msg_count OUT NOCOPY number
94 ,x_msg_data OUT NOCOPY varchar2
95 );
96
97 procedure Retrieve_Dim_Level(
98 p_commit IN varchar2 := FND_API.G_FALSE
99 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
100 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
101 ,x_return_status OUT NOCOPY varchar2
102 ,x_msg_count OUT NOCOPY number
103 ,x_msg_data OUT NOCOPY varchar2
104 );
105 procedure Create_Dim_Level(
106 p_commit IN varchar2 := FND_API.G_FALSE
107 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
108 ,p_create_tables IN BOOLEAN
109 ,x_return_status OUT NOCOPY varchar2
110 ,x_msg_count OUT NOCOPY number
111 ,x_msg_data OUT NOCOPY varchar2
112 );
113
114 procedure Update_Dim_Level(
115 p_commit IN varchar2 := FND_API.G_FALSE
116 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
117 ,x_return_status OUT NOCOPY varchar2
118 ,x_msg_count OUT NOCOPY number
119 ,x_msg_data OUT NOCOPY varchar2
120 );
121
122 -- Procedure to Delete all pertaining information for a given Dimension Level.
123 -- Though all procedures in this package may be called individually, this
124 -- procedure is the entry point to delete all meta data for dimension levels.
125 procedure Delete_Dim_Level(
126 p_commit IN varchar2 := FND_API.G_FALSE
127 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
128 ,x_return_status OUT NOCOPY varchar2
129 ,x_msg_count OUT NOCOPY number
130 ,x_msg_data OUT NOCOPY varchar2
131 );
132
133 -- Procedure to Populate the necessary meta data in BSC for a dimension level.
134 procedure Create_Bsc_Dim_Levels_Md(
135 p_commit IN varchar2 := FND_API.G_FALSE
136 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
137 ,x_return_status OUT NOCOPY varchar2
138 ,x_msg_count OUT NOCOPY number
139 ,x_msg_data OUT NOCOPY varchar2
140 );
141
142 procedure Retrieve_Bsc_Dim_Levels_Md(
143 p_commit IN varchar2 := FND_API.G_FALSE
144 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
145 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
146 ,x_return_status OUT NOCOPY varchar2
147 ,x_msg_count OUT NOCOPY number
148 ,x_msg_data OUT NOCOPY varchar2
149 );
150
151 procedure Update_Bsc_Dim_Levels_Md(
152 p_commit IN varchar2 := FND_API.G_FALSE
153 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
154 ,x_return_status OUT NOCOPY varchar2
155 ,x_msg_count OUT NOCOPY number
156 ,x_msg_data OUT NOCOPY varchar2
157 );
158
159 -- Procedure to Delete the necessary meta data in BSC for a dimension level.
160 procedure Delete_Bsc_Dim_Levels_Md(
161 p_commit IN varchar2 := FND_API.G_FALSE
162 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
163 ,x_return_status OUT NOCOPY varchar2
164 ,x_msg_count OUT NOCOPY number
165 ,x_msg_data OUT NOCOPY varchar2
166 );
167
168 -- Procedure to Populate information on the columns for the dimension level
169 -- view or table.
170 procedure Create_Bsc_Sys_Dim_Lvl_Cols(
171 p_commit IN varchar2 := FND_API.G_FALSE
172 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
173 ,x_return_status OUT NOCOPY varchar2
174 ,x_msg_count OUT NOCOPY number
175 ,x_msg_data OUT NOCOPY varchar2
176 );
177
178 procedure Retrieve_Bsc_Sys_Dim_Lvl_Cols(
179 p_commit IN varchar2 := FND_API.G_FALSE
180 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
181 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
182 ,x_return_status OUT NOCOPY varchar2
183 ,x_msg_count OUT NOCOPY number
184 ,x_msg_data OUT NOCOPY varchar2
185 );
186
187 procedure Update_Bsc_Sys_Dim_Lvl_Cols(
188 p_commit IN varchar2 := FND_API.G_FALSE
189 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
190 ,x_return_status OUT NOCOPY varchar2
191 ,x_msg_count OUT NOCOPY number
192 ,x_msg_data OUT NOCOPY varchar2
193 );
194
195 -- Procedure to Delete information on the columns for the dimension level
196 -- view or table.
197 procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
198 p_commit IN varchar2 := FND_API.G_FALSE
199 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
200 ,x_return_status OUT NOCOPY varchar2
201 ,x_msg_count OUT NOCOPY number
202 ,x_msg_data OUT NOCOPY varchar2
203 );
204
205 /*********************************************************************************
206 -- Procedure to Create a Relationship between two Dimension Levels
207 **********************************************************************************/
208
209 -- Thi procedure is the entry point to create Dimension Level Relationships
210 PROCEDURE Create_Dim_Level_Relation(
211 p_commit IN varchar2 := FND_API.G_FALSE
212 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
213 ,x_return_status OUT NOCOPY varchar2
214 ,x_msg_count OUT NOCOPY number
215 ,x_msg_data OUT NOCOPY varchar2
216 );
217 ----------
218 PROCEDURE Delete_Dim_Level_Relation(
219 p_commit IN varchar2 := FND_API.G_FALSE
220 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
221 ,x_return_status OUT NOCOPY varchar2
222 ,x_msg_count OUT NOCOPY number
223 ,x_msg_data OUT NOCOPY varchar2
224 );
225 -----------
226 FUNCTION Is_Valid_Relationship(
227 p_commit IN varchar2 := FND_API.G_FALSE
228 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
229 ,x_return_status OUT NOCOPY varchar2
230 ,x_msg_count OUT NOCOPY number
231 ,x_msg_data OUT NOCOPY varchar2
232 ) RETURN BOOLEAN;
233 -----------
234 PROCEDURE is_dependent(
235 p_commit IN varchar2 := FND_API.G_FALSE
236 ,p_child_dim_level_short_name IN varchar2
237 ,p_parent_dim_level_short_name IN varchar2
238 ,x_return_value OUT NOCOPY varchar2
239 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
240 ,x_msg_count OUT NOCOPY number
241 ,x_msg_data OUT NOCOPY varchar2
242 );
243
244 PROCEDURE get_parent_dimension_levels(
245 p_commit IN varchar2 := FND_API.G_FALSE
246 ,p_child_dim_level_short_name IN varchar2
247 ,p_parent_dim_level_short_names OUT NOCOPY varchar2
248 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
249 ,x_msg_count OUT NOCOPY number
250 ,x_msg_data OUT NOCOPY varchar2
251 );
252
253 PROCEDURE get_child_dimension_levels(
254 p_commit IN varchar2 := FND_API.G_FALSE
255 ,p_parent_dim_level_short_name IN varchar2
256 ,p_child_dim_level_short_names OUT NOCOPY varchar2
257 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
258 ,x_msg_count OUT NOCOPY number
259 ,x_msg_data OUT NOCOPY varchar2
260 );
261
262 --------------------------------------------------------------------------------------
263 -- is_Child_Parent :
264 -- Check to see if the passed Dim Level Ids form a Parent child relationship.
265 --
266 -- Added by ADRAO for Enh#2901823
267 -- Values returned wouid be in x_return_status (FND_API.G_FALSE or FND_API.G_TRUE)
268 --------------------------------------------------------------------------------------
269 -----------
270 FUNCTION is_Child_Parent
271 (
272 p_child_dim_level_short_name IN VARCHAR2
273 , p_parent_dim_level_short_name IN VARCHAR2
274 , x_return_status OUT NOCOPY VARCHAR2
275 , x_msg_count OUT NOCOPY NUMBER
276 , x_msg_data OUT NOCOPY VARCHAR2
277 ) RETURN BOOLEAN;
278
279
280 PROCEDURE Retrieve_Relationship (
281 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
282 , x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
283 , x_return_status OUT NOCOPY VARCHAR2
284 , x_msg_count OUT NOCOPY NUMBER
285 , x_msg_data OUT NOCOPY VARCHAR2
286 ) ;
287
288 /*********************************************************************************
289 **********************************************************************************/
290 PROCEDURE Drop_Dim_Level_Tabs
291 (
292 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
293 , x_return_status OUT NOCOPY VARCHAR2
294 , x_msg_count OUT NOCOPY NUMBER
295 , x_msg_data OUT NOCOPY VARCHAR2
296 );
297 /*********************************************************************************
298 **********************************************************************************/
299 PROCEDURE Translate_Dimension_Level (
300 p_Commit IN VARCHAR2 := FND_API.G_FALSE
301 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
302 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
303 ,x_return_status OUT NOCOPY VARCHAR2
304 ,x_msg_count OUT NOCOPY NUMBER
305 ,x_msg_data OUT NOCOPY VARCHAR2
306 );
307 --
308 --
309 PROCEDURE load_dimension_level(
310 p_commit IN VARCHAR2 := FND_API.G_FALSE
311 ,p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
312 ,x_return_status OUT NOCOPY VARCHAR2
313 ,x_msg_count OUT NOCOPY NUMBER
314 ,x_msg_data OUT NOCOPY VARCHAR2
315 );
316 --
317
318 PROCEDURE Trans_DimObj_By_Given_Lang
319 (
320 p_commit IN VARCHAR2 := FND_API.G_FALSE
321 , p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
322 , x_return_status OUT NOCOPY VARCHAR2
323 , x_msg_count OUT NOCOPY NUMBER
324 , x_msg_data OUT NOCOPY VARCHAR2
325 );
326
327 procedure Validate_Imported_Level_Views
328 (
329 ERRBUF OUT NOCOPY VARCHAR2
330 , RETCODE OUT NOCOPY VARCHAR2
333 end BSC_DIMENSION_LEVELS_PUB;
331 );
332
[Home] [Help]