1 PACKAGE BIS_Target_Level_PVT AS
2 /* $Header: BISVTALS.pls 120.0 2005/06/01 17:29:07 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVTALS.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Private API for creating and managing Indicator Levels
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | 20-MAY-00 jradhakr Added the Function Get_Level_Id_From_Dimlevels
20 REM | to the specifications
21 REM | 23-JAN-02 sashaik Added Retrieve_Org_level procedure for 1740789
22 REM | 29-SEP-02 arhegde bug#2528442 - added retrieve_mult_targ_levels() |
23 REM | 09-OCT-02 arhegde Modified for bug#2616667 |
24 REM | 21-OCT-04 arhegde bug# 3634587 The SQL used shows up on performance |
25 REM | repository top-20, Removed Retrieve_Measure_Notify_Resps() |
26 REM |
27 REM +=======================================================================+
28 */
29
30 --
31 -- PROCEDUREs
32 --
33 -- creates one Indicator Level
34 PROCEDURE Create_Target_Level
35 ( p_api_version IN NUMBER
36 , p_commit IN VARCHAR2 := FND_API.G_FALSE
37 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
38 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
39 , x_return_status OUT NOCOPY VARCHAR2
40 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
41 );
42 --
43 --
44 -- creates one Indicator Level for the given owner
45 PROCEDURE Create_Target_Level
46 ( p_api_version IN NUMBER
47 , p_commit IN VARCHAR2 := FND_API.G_FALSE
48 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
49 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
50 , p_owner IN VARCHAR2
51 , x_return_status OUT NOCOPY VARCHAR2
52 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
53 );
54 --
55 --
56 -- Gets All Indicator Levels
57 -- If information about the dimensions are not required, set all_info to
58 -- FALSE
59 PROCEDURE Count_Target_Levels
60 ( p_api_version IN NUMBER
61 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
62 , x_count OUT NOCOPY NUMBER
63 , x_return_status OUT NOCOPY VARCHAR2
64 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
65 );
66 --
67 --
68 -- Gets All Indicator Levels
69 -- If information about the dimensions are not required, set all_info to
70 -- FALSE
71 PROCEDURE Retrieve_Target_Levels
72 ( p_api_version IN NUMBER
73 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
74 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
75 , x_Target_Level_tbl OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Tbl_Type
76 , x_return_status OUT NOCOPY VARCHAR2
77 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
78 );
79 --
80 --
81 -- Gets Information for one Indicator Level
82 -- If information about the dimension are not required, set all_info to FALSE.
83 PROCEDURE Retrieve_Target_Level
84 ( p_api_version IN NUMBER
85 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
86 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
87 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
88 , x_return_status OUT NOCOPY VARCHAR2
89 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
90 );
91 --
92 -- Retrieves target level records into table given
93 -- multiple target level short names.
94 -- This is used in KPI portlet as of now to retrieve
95 -- details of all required target level short names with one call.
96 --
97 PROCEDURE retrieve_mult_targ_levels(
98 p_api_version IN NUMBER
99 ,p_target_level_tbl IN BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_Type
100 ,p_all_info IN VARCHAR2 := FND_API.G_TRUE
101 ,x_target_level_tbl OUT NOCOPY BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_Type
102 ,x_return_status OUT NOCOPY VARCHAR2
103 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
104 );
105
106 --
107 --
108 -- Update_Target_Levels
109 PROCEDURE Update_Target_Level
110 ( p_api_version IN NUMBER
111 , p_commit IN VARCHAR2 := FND_API.G_FALSE
112 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
113 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
116 );
117 --
118 --
119 -- Update_Target_Levels for the given owner
120 PROCEDURE Update_Target_Level
121 ( p_api_version IN NUMBER
122 , p_commit IN VARCHAR2 := FND_API.G_FALSE
123 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
124 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
125 , p_owner IN VARCHAR2
126 , p_up_loaded IN VARCHAR2 := FND_API.G_FALSE
127 , x_return_status OUT NOCOPY VARCHAR2
128 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
129 );
130 --
131 --
132 -- deletes one Target_Level
133 PROCEDURE Delete_Target_Level
134 ( p_api_version IN NUMBER
135 , p_force_delete IN NUMBER := 0--gbhaloti #3148615
136 , p_commit IN VARCHAR2 := FND_API.G_FALSE
137 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
140 );
141 --
142 --
143 Procedure Translate_Target_Level
144 ( p_api_version IN NUMBER
145 , p_commit IN VARCHAR2 := FND_API.G_FALSE
146 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
147 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
148 , p_owner IN VARCHAR2
149 , x_return_status OUT NOCOPY VARCHAR2
150 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
151 );
152 --
153 -- Validates measure
154 PROCEDURE Validate_Target_Level
155 ( p_api_version IN NUMBER
156 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
157 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
158 , x_return_status OUT NOCOPY VARCHAR2
159 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
160 );
161 --
162 -- Value - ID conversion
163 PROCEDURE Value_ID_Conversion
164 ( p_api_version IN NUMBER
165 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
166 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
167 , x_return_status OUT NOCOPY VARCHAR2
168 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
169 );
170 --
171 PROCEDURE Value_ID_Conversion
172 ( p_api_version IN NUMBER
173 , p_Target_Level_Short_Name IN VARCHAR2
174 , p_Target_Level_Name IN VARCHAR2
175 , x_Target_Level_ID OUT NOCOPY NUMBER
176 , x_return_status OUT NOCOPY VARCHAR2
177 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
178 );
179 --
180 Procedure Retrieve_User_Target_Levels
181 ( p_api_version IN NUMBER
182 , p_user_id IN NUMBER
183 , p_all_info IN VARCHAR2 Default FND_API.G_TRUE
184 , x_Target_Level_Tbl OUT NOCOPY BIS_Target_LEVEL_PUB.Target_Level_Tbl_Type
185 , x_return_status OUT NOCOPY VARCHAR2
186 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
187 );
188
189 /* Retrive available notification responsibilities for this user */
190 /* The SQL used shows up on performance repository top-20
191 bug# 3634587 - This API is not used anymore. Procedure Retrieve_Measure_Notify_Resps
192 ( p_api_version IN NUMBER
193 , p_user_id IN NUMBER
194 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
195 , x_notify_resp_tbl OUT NOCOPY BIS_RESPONSIBILITY_PVT.Notify_Responsibility_Tbl_Type
196 , x_return_status OUT NOCOPY VARCHAR2
197 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
198 );
199 */
200 --
201 --
202 PROCEDURE Lock_Record
203 ( p_api_version IN NUMBER
204 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
205 , p_timestamp IN VARCHAR := NULL
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
208 );
209 --
210 PROCEDURE Retrieve_Last_Update_Date
211 ( p_api_version IN NUMBER
212 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
213 , x_last_update_date OUT NOCOPY DATE
214 , x_return_status OUT NOCOPY VARCHAR2
215 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
216 );
217 --
218 FUNCTION Get_Level_Id_From_Dimlevels
219 ( p_tl_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
220 ) RETURN NUMBER;
221
222
223 -- Retrieves the time level for the given target level
224 --
225 PROCEDURE Retrieve_Time_level
226 ( p_api_version IN NUMBER
227 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
228 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
229 , x_dimension_level_number OUT NOCOPY NUMBER
230 , x_return_status OUT NOCOPY VARCHAR2
231 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
232 );
233
234 -- Retrieves the time level for the given target level
235 --
236 PROCEDURE Retrieve_Time_level
237 ( p_api_version IN NUMBER
238 , p_Target_Level_id IN NUMBER
239 , x_Dimension_Level_id OUT NOCOPY NUMBER
240 , x_Dimension_Level_short_Name OUT NOCOPY NUMBER
241 , x_Dimension_Level_name OUT NOCOPY NUMBER
242 , x_dimension_level_number OUT NOCOPY NUMBER
243 , x_return_status OUT NOCOPY VARCHAR2
244 );
245
246
247 --
248 -- Retrieves the Org level for the given target level
249 --
250 PROCEDURE Retrieve_Org_level
251 ( p_api_version IN NUMBER
252 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
253 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
254 , x_dimension_level_number OUT NOCOPY NUMBER
255 , x_return_status OUT NOCOPY VARCHAR2
256 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
257 );
258
259
260 -- New Function to return TargetLevelId given the DimensionLevel ShortNames
261 -- and the Measure Short Name
262 FUNCTION Get_Id_From_DimLevelShortNames
263 ( p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
264 ) RETURN NUMBER;
265
266 -- New Procedure to return TargetLevel given the DimensionLevel ShortNames in any sequence
267 -- and the Measure Short Name
268
269 PROCEDURE Retrieve_TL_From_DimLvlShNms
270 (p_api_version IN NUMBER
271 ,p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
272 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
273 , x_return_status OUT NOCOPY VARCHAR2
274 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
275 );
276
277 -- Given a target level short name update the
278 -- bis_target_levels, bis_target_levels_tl
279 -- for last_updated_by , created_by as 1
280 PROCEDURE updt_tl_attributes(p_tl_short_name IN VARCHAR2
281 ,p_tl_new_short_name IN VARCHAR2
282 ,x_return_status OUT NOCOPY VARCHAR2);
283
284 PROCEDURE Validate_Dimensions -- 2486702
285 (
286 p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_return_msg OUT NOCOPY VARCHAR2
289 );
290
291 END BIS_Target_Level_PVT;
[Home] [Help]