1 PACKAGE BIS_TARGET_PVT AUTHID CURRENT_USER AS
2 /* $Header: BISVTARS.pls 115.40 2003/11/15 08:39:20 gbhaloti ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVTARS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for creating and managing Targets for the
13 REM | Key Performance Framework.
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | 02-DEC-98 irchen Creation |
18 REM | 20-JAN-02 sashaik Added Retrieve_Org_level_value for 1740789 |
19 REM | 10-JAN-2003 rchandra for bug 2715432 , changed OUT parameter |
20 REM | x_Target_Level_Rec , x_Target_Rec to IN OUT |
21 REM | in API RETRIEVE_TARGET_FROM_SHNMS |
22 REM | and x_Target_Rec in API Value_ID_Conversion |
23 REM +=======================================================================+
24 */
25 --
26 --
27 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_TARGET_PVT';
28 G_SET_OF_BOOK_ID NUMBER := NULL;
29 --
30 --
31 TYPE Target_Table_Rec_Type IS RECORD
32 ( Target_ID NUMBER
33 , Target_Level_ID NUMBER
34 , Target_Level_Short_Name VARCHAR2(80)
35 , Target_Level_Name VARCHAR2(100)
36 , Plan_ID NUMBER
37 , Plan_Short_Name VARCHAR2(80)
38 , Plan_Name VARCHAR2(80)
39 , Org_Level_Value_ID VARCHAR2(250)
40 , Org_Level_Value_Name VARCHAR2(250)
41 , Time_Level_Value_ID VARCHAR2(250)
42 , Time_Level_Value_Name VARCHAR2(250)
43 , Dim1_Level_Value_ID VARCHAR2(250)
44 , Dim1_Level_Value_Name VARCHAR2(250)
45 , Dim2_Level_Value_ID VARCHAR2(250)
46 , Dim2_Level_Value_Name VARCHAR2(250)
47 , Dim3_Level_Value_ID VARCHAR2(250)
48 , Dim3_Level_Value_Name VARCHAR2(250)
49 , Dim4_Level_Value_ID VARCHAR2(250)
50 , Dim4_Level_Value_Name VARCHAR2(250)
51 , Dim5_Level_Value_ID VARCHAR2(250)
52 , Dim5_Level_Value_Name VARCHAR2(250)
53 , Dim6_Level_Value_ID VARCHAR2(250)
54 , Dim6_Level_Value_Name VARCHAR2(250)
55 , Dim7_Level_Value_ID VARCHAR2(250)
56 , Dim7_Level_Value_Name VARCHAR2(250)
57 , Target NUMBER
58 , Range1_low NUMBER
59 , Range1_high NUMBER
60 , Range2_low NUMBER
61 , Range2_high NUMBER
62 , Range3_low NUMBER
63 , Range3_high NUMBER
64 , Notify_Resp1_ID NUMBER
65 , Notify_Resp1_Short_Name VARCHAR2(100)
66 , Notify_Resp1_Name VARCHAR2(240)
67 , Notify_Resp2_ID NUMBER
68 , Notify_Resp2_Short_Name VARCHAR2(100)
69 , Notify_Resp2_Name VARCHAR2(240)
70 , Notify_Resp3_ID NUMBER
71 , Notify_Resp3_Short_Name VARCHAR2(100)
72 , Notify_Resp3_Name VARCHAR2(240)
73 );
74 --
75 TYPE Target_Table_Type IS TABLE of Target_Table_Rec_Type
76 INDEX BY BINARY_INTEGER;
77 --
78 --
79 -- Defines one target for a specific set of dimension values for
80 -- one target level
81 PROCEDURE Create_Target
82 ( p_api_version IN NUMBER
83 , p_is_dbimeasure IN NUMBER := 0 --added by gbhaloti #3148615
84 , p_commit IN VARCHAR2 := FND_API.G_FALSE
85 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
86 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
89 );
90 --
91 --
92 -- retrieve information for all targets of the given target level
93 -- if information about dimension values are not required, set all_info
94 -- to FALSE.
95 PROCEDURE Retrieve_Targets
96 ( p_api_version IN NUMBER
97 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
98 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
99 , x_Target_Tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_Type
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
102 );
103 --
104 --
105 -- retrieve information for one target
106 -- If information about dimension values are not required, set all_info
107 -- to FALSE.
108 PROCEDURE Retrieve_Target
109 ( p_api_version IN NUMBER
110 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
111 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
112 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
113 , x_return_status OUT NOCOPY VARCHAR2
114 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
115 );
116 --
117 --
118 -- retrieves the owners for one target
119 -- If information about dimension values are not required, set all_info
120 -- to FALSE.
121 PROCEDURE Retrieve_Target_Owners
122 ( p_api_version IN NUMBER
123 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
124 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
125 , x_Target_Owners_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Owners_Rec_Type
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
128 );
129 --
130 --
131 -- Modifies one target for a specific set of dimension values for
132 -- one target level
133 PROCEDURE Update_Target
134 ( p_api_version IN NUMBER
135 , p_is_dbimeasure IN NUMBER := 0 --gbhaloti #3148615
136 , p_commit IN VARCHAR2 := FND_API.G_FALSE
137 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
138 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
139 , x_return_status OUT NOCOPY VARCHAR2
140 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
141 );
142 --
143 --
144 -- Deletes one target for a specific set of dimension values for
145 -- one target level
146 PROCEDURE Delete_Target
147 ( p_api_version IN NUMBER
148 , p_commit IN VARCHAR2 := FND_API.G_FALSE
149 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
150 , x_return_status OUT NOCOPY VARCHAR2
151 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
152 );
153 --
154 --
155 -- Validates target record
156 PROCEDURE Validate_Target
157 ( p_api_version IN NUMBER
158 , p_is_dbimeasure IN NUMBER := 0 --added by gbhaloti #3148615
159 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
160 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
163 );
164 --
165 --
166 -- Value - ID conversion
167 PROCEDURE Value_ID_Conversion
168 ( p_api_version IN NUMBER
169 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
170 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
171 , x_return_status OUT NOCOPY VARCHAR2
172 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
173 );
174 --
175 --
176 PROCEDURE Retrieve_Last_Update_Date
177 ( p_api_version IN NUMBER
178 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
179 , x_last_update_date OUT NOCOPY DATE
180 , x_return_status OUT NOCOPY VARCHAR2
181 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
182 );
183
184
185 PROCEDURE GetID
186 ( p_api_version IN NUMBER
187 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
188 , x_Target_Rec OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type
189 , x_return_status OUT NOCOPY VARCHAR2
190 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
191 );
192
193 --
194 --
195 PROCEDURE Lock_Record
196 ( p_api_version IN NUMBER
197 , p_Target_Rec IN BIS_TARGET_PUB.Target_Rec_Type
198 , p_timestamp IN VARCHAR := NULL
199 , x_result OUT NOCOPY VARCHAR2
200 , x_return_status OUT NOCOPY VARCHAR2
201 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
202 );
203 --
204 --
205 PROCEDURE Retrieve_Measure_Dim_Values
206 ( p_api_version IN NUMBER
207 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
208 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
209 , p_User_ID IN NUMBER := NULL
210 , p_User_Name IN VARCHAR2 := NULL
211 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
212 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
213 , x_return_status OUT NOCOPY VARCHAR2
214 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
215 );
216 --
217 --
218 PROCEDURE GetQueryStatement
219 ( p_select_clause IN VARCHAR2
220 , p_from_clause IN VARCHAR2
221 , p_where_clause IN VARCHAR2
222 , p_order_by_clause IN VARCHAR2
223 , x_query_statement OUT NOCOPY VARCHAR2
224 );
225 --
226 --
227 --
228 PROCEDURE GetViewNames
229 ( p_target_level_id IN VARCHAR2
230 , x_org_view_name OUT NOCOPY VARCHAR2
231 , x_time_view_name OUT NOCOPY VARCHAR2
232 , x_dim1_view_name OUT NOCOPY VARCHAR2
233 , x_dim2_view_name OUT NOCOPY VARCHAR2
234 , x_dim3_view_name OUT NOCOPY VARCHAR2
235 , x_dim4_view_name OUT NOCOPY VARCHAR2
236 , x_dim5_view_name OUT NOCOPY VARCHAR2
237 , x_dim6_view_name OUT NOCOPY VARCHAR2
238 , x_dim7_view_name OUT NOCOPY VARCHAR2
239 );
240 --
241 --
242 --
243 FUNCTION GetComputingUserFunctionName
244 ( p_computing_function_id IN NUMBER
245 )
246 RETURN VARCHAR2;
247 --
248 --
249 FUNCTION GetNotifyResponsibilityName
250 ( p_responsibility_short_name IN VARCHAR2
251 )
252 RETURN VARCHAR2;
253 --
254 --
255 FUNCTION GetSetOfBookID
256 RETURN VARCHAR2;
257 PRAGMA RESTRICT_REFERENCES (GetSetOfBookID, WNDS);
258 --
259 --
260 FUNCTION Get_Target
261 ( p_computing_function_id IN NUMBER
262 , p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type
263 )
264 RETURN NUMBER;
265 --
266 --
267 FUNCTION Get_Target
268 ( p_computing_function_id IN NUMBER
269 , p_target_level_id IN NUMBER
270 )
271 RETURN NUMBER;
272
273 -- Retrieves the time level values for the given target
274 --
275 PROCEDURE Retrieve_Time_level_value
276 ( p_api_version IN NUMBER
277 , p_Target_Rec IN BIS_Target_PUB.Target_Rec_Type
278 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
279 , x_dimension_level_number OUT NOCOPY NUMBER
280 , x_return_status OUT NOCOPY VARCHAR2
281 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
282 );
283
284 -- Retrieves the time level for the given target level
285 --
286 PROCEDURE Retrieve_Time_level_value
287 ( p_api_version IN NUMBER
288 , p_Target_id IN NUMBER
289 , x_Dim_Level_Value_ID OUT NOCOPY VARCHAR2
290 , x_Dim_Level_Value_name OUT NOCOPY VARCHAR2
291 , x_dimension_level_number OUT NOCOPY NUMBER
292 , x_return_status OUT NOCOPY VARCHAR2
293 );
294
295 -- Retrieves the Org level values for the given target
296 --
297 PROCEDURE Retrieve_Org_level_value
298 ( p_api_version IN NUMBER
299 , p_Target_Rec IN BIS_Target_PUB.Target_Rec_Type
300 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
301 , x_dimension_level_number OUT NOCOPY NUMBER
302 , x_return_status OUT NOCOPY VARCHAR2
303 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
304 );
305
306 -- New Procedure to return TargetLevel and Target given the DimensionLevel ShortNames in any sequence
307 -- and the Measure Short Name
308 PROCEDURE Retrieve_Target_From_ShNms
309 ( p_api_version IN NUMBER
310 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
311 , p_Target_Rec IN BIS_TARGET_PUB.TARGET_REC_TYPE
312 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
313 , x_Target_Rec IN OUT NOCOPY BIS_TARGET_PUB.TARGET_REC_TYPE
314 , x_return_status OUT NOCOPY VARCHAR2
315 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
316 ) ;
317 --
318 --
319 END BIS_TARGET_PVT;
[Home] [Help]