1 package BSC_DIMENSION_LEVELS_PVT as
2 /* $Header: BSCVDMLS.pls 120.0 2005/06/01 16:11:30 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVDMLS.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 | 07-MAY-2003 Retrieve_Relationship() Added by ADRAO for change Enh#2901823 |
21 | |
22 | 14-JUN-03 mahrao Added Translate_dimesnsion_level procedure for enh# 2842894 |
23 | 17-NOV-2003 PAJOHRI Bug #3232366 |
24 +======================================================================================+
25 */
26
27 TYPE Dim_Level_Rec_Type is Record(
28 Level_Short_Name varchar2(30)
29 ,Level_Long_Name varchar2(255)
30 );
31
32 TYPE Dim_Level_Tbl_Type is TABLE OF Dim_Level_Rec_Type
33 INDEX BY BINARY_INTEGER;
34
35 -- Procedure to Create all pertaining information for a given Dimension Level.
36 -- Though all procedures in this package may be called individually, this
37 -- procedure is the entry point to populate all meta data for dimension levels.
38 procedure Create_Dim_Level(
39 p_commit IN varchar2 := FND_API.G_FALSE
40 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
41 ,x_return_status OUT NOCOPY varchar2
42 ,x_msg_count OUT NOCOPY number
43 ,x_msg_data OUT NOCOPY varchar2
44 );
45
46 procedure Retrieve_Dim_Level(
47 p_commit IN varchar2 := FND_API.G_FALSE
48 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
49 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
50 ,x_return_status OUT NOCOPY varchar2
51 ,x_msg_count OUT NOCOPY number
52 ,x_msg_data OUT NOCOPY varchar2
53 );
54
55 procedure Update_Dim_Level(
56 p_commit IN varchar2 := FND_API.G_FALSE
57 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
58 ,x_return_status OUT NOCOPY varchar2
59 ,x_msg_count OUT NOCOPY number
60 ,x_msg_data OUT NOCOPY varchar2
61 );
62
63 -- Procedure to Delete all pertaining information for a given Dimension Level.
64 -- Though all procedures in this package may be called individually, this
65 -- procedure is the entry point to delete all meta data for dimension levels.
66 procedure Delete_Dim_Level(
67 p_commit IN varchar2 := FND_API.G_FALSE
68 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
69 ,x_return_status OUT NOCOPY varchar2
70 ,x_msg_count OUT NOCOPY number
71 ,x_msg_data OUT NOCOPY varchar2
72 );
73
74 -- Procedure to Populate the necessary meta data in BSC for a dimension level.
75 procedure Create_Bsc_Dim_Levels_Md(
76 p_commit IN varchar2 := FND_API.G_FALSE
77 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
78 ,x_return_status OUT NOCOPY varchar2
79 ,x_msg_count OUT NOCOPY number
80 ,x_msg_data OUT NOCOPY varchar2
81 );
82
83 procedure Retrieve_Bsc_Dim_Levels_Md(
84 p_commit IN varchar2 := FND_API.G_FALSE
85 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
86 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
87 ,x_return_status OUT NOCOPY varchar2
88 ,x_msg_count OUT NOCOPY number
89 ,x_msg_data OUT NOCOPY varchar2
90 );
91
92 procedure Update_Bsc_Dim_Levels_Md(
93 p_commit IN varchar2 := FND_API.G_FALSE
94 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
95 ,x_return_status OUT NOCOPY varchar2
96 ,x_msg_count OUT NOCOPY number
97 ,x_msg_data OUT NOCOPY varchar2
98 );
99
100 -- Procedure to Delete the necessary meta data in BSC for a dimension level.
101 procedure Delete_Bsc_Dim_Levels_Md(
102 p_commit IN varchar2 := FND_API.G_FALSE
103 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
104 ,x_return_status OUT NOCOPY varchar2
105 ,x_msg_count OUT NOCOPY number
106 ,x_msg_data OUT NOCOPY varchar2
107 );
108
109 -- Procedure to Populate information on the columns for the dimension level
110 -- view or table.
111 procedure Create_Bsc_Sys_Dim_Lvl_Cols(
112 p_commit IN varchar2 := FND_API.G_FALSE
113 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
114 ,x_return_status OUT NOCOPY varchar2
115 ,x_msg_count OUT NOCOPY number
116 ,x_msg_data OUT NOCOPY varchar2
117 );
118
119 procedure Retrieve_Bsc_Sys_Dim_Lvl_Cols(
120 p_commit IN varchar2 := FND_API.G_FALSE
121 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
122 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
123 ,x_return_status OUT NOCOPY varchar2
124 ,x_msg_count OUT NOCOPY number
125 ,x_msg_data OUT NOCOPY varchar2
126 );
127
128 procedure Update_Bsc_Sys_Dim_Lvl_Cols(
129 p_commit IN varchar2 := FND_API.G_FALSE
130 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
131 ,x_return_status OUT NOCOPY varchar2
132 ,x_msg_count OUT NOCOPY number
133 ,x_msg_data OUT NOCOPY varchar2
134 );
135
136 -- Procedure to Delete information on the columns for the dimension level
137 -- view or table.
138 procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
139 p_commit IN varchar2 := FND_API.G_FALSE
140 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
141 ,x_return_status OUT NOCOPY varchar2
142 ,x_msg_count OUT NOCOPY number
143 ,x_msg_data OUT NOCOPY varchar2
144 );
145
146 function Get_Next_Value(
147 p_table_name IN varchar2
148 ,p_column_name IN varchar2
149 )return number;
150
151 function Get_Id(
152 p_table_name IN varchar2
153 ,p_column_name IN varchar2
154 ,p_column_value IN varchar2
155 ,p_column_ID_name IN varchar2
156 ) return number;
157
158 function Validate_Dim_Level(
159 p_level_name IN varchar2
160 ) return varchar2;
161
162 function Validate_Dim_Group(
163 p_group_name varchar2
164 ) return number;
165
166 function get_dim_levels(
167 p_meas_short_name varchar2
168 ,p_dim_short_name varchar2
169 ) return Dim_Level_Tbl_Type;
170
171 function Validate_Value(
172 p_Table_Name varchar2
173 ,p_Table_Column_Name varchar2
174 ,p_Column_Value number
175 ) return number;
176
177 function Get_Object_Name(
178 p_Table_Name varchar2
179 ,p_Table_Name_Column varchar2
180 ,p_Table_Id_Column varchar2
181 ,p_Id_Value number
182 ) return varchar2;
183
184 /*********************************************************************************
185 -- Procedures to Handle Relationships between Dimension Levels
186 **********************************************************************************/
187 ---------
188 PROCEDURE Create_Dim_Level_Relation(
189 p_commit IN varchar2 := FND_API.G_FALSE
190 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
191 ,x_return_status OUT NOCOPY varchar2
192 ,x_msg_count OUT NOCOPY number
193 ,x_msg_data OUT NOCOPY varchar2
194 );
195 ----------
196 PROCEDURE Delete_Dim_Level_Relation(
197 p_commit IN varchar2 := FND_API.G_FALSE
198 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
199 ,x_return_status OUT NOCOPY varchar2
200 ,x_msg_count OUT NOCOPY number
201 ,x_msg_data OUT NOCOPY varchar2
202 );
203 -----------
204 FUNCTION Evaluate_Circular_Relationship(
205 p_Child_level_Id IN number
206 ,p_Parent_Dim_Level_Id IN number
207 ,p_Relation_Type IN number := 1
208 ,p_Output_Flag IN boolean := TRUE
209 ,x_Parents OUT NOCOPY varchar2
210 ,x_return_status OUT NOCOPY varchar2
211 ,x_msg_count OUT NOCOPY number
212 ,x_msg_data OUT NOCOPY varchar2
213 ) RETURN boolean;
214 -----------
215 FUNCTION get_Dim_Level_Name(
216 p_Child_level_Id IN number
217 ) RETURN varchar2;
218 -----------
219 FUNCTION get_Dim_Level_Id(
220 p_Short_Name IN varchar2
221 ) RETURN number;
222 ----------
223 PROCEDURE Create_BSC_Dim_Level_View (
224 p_commit IN varchar2 := FND_API.G_FALSE
225 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
226 ,x_return_status OUT NOCOPY varchar2
227 ,x_msg_count OUT NOCOPY number
228 ,x_msg_data OUT NOCOPY varchar2
229 );
230
231 ----------
232 procedure Retrieve_Relationship
233 (
234 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
235 , x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
236 , x_return_status OUT NOCOPY VARCHAR2
237 , x_msg_count OUT NOCOPY NUMBER
238 , x_msg_data OUT NOCOPY VARCHAR2
239 );
240
241 /*********************************************************************************
242 **********************************************************************************/
243 FUNCTION get_Relation_Column(
244 p_Child_level_Id IN NUMBER
245 , p_Parent_level_Id IN NUMBER
246 , p_Relation_Type IN NUMBER
247 , x_return_status OUT NOCOPY VARCHAR2
248 , x_msg_count OUT NOCOPY NUMBER
249 , x_msg_data OUT NOCOPY VARCHAR2
250 ) RETURN VARCHAR2;
251 /*********************************************************************************
252 **********************************************************************************/
253
254 PROCEDURE Translate_Dimension_Level (
255 p_Commit IN VARCHAR2 := FND_API.G_FALSE
256 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
257 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
258 ,x_return_status OUT NOCOPY VARCHAR2
259 ,x_msg_count OUT NOCOPY NUMBER
260 ,x_msg_data OUT NOCOPY VARCHAR2
261 );
262 --
263
264 procedure Trans_DimObj_By_Given_Lang
265 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
266 , p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
267 , x_return_status OUT NOCOPY VARCHAR2
268 , x_msg_count OUT NOCOPY NUMBER
269 , x_msg_data OUT NOCOPY VARCHAR2
270 );
271 --=============================================================================
272 FUNCTION Validate_Dim_Level_Id (
273 p_dim_level_id IN NUMBER
274 ) RETURN NUMBER ;
275 --=============================================================================
276 end BSC_DIMENSION_LEVELS_PVT;
[Home] [Help]