1 PACKAGE BSC_BIS_LOCKS_PVT AS
2 /* $Header: BSCVLOCS.pls 120.1 2005/07/12 08:48:58 adrao noship $ */
3
4 TYPE t_lock_Rec IS RECORD(
5 obj_key1 number /* object Id */
6 ,obj_key2 number /* sub-object Id */
7 ,obj_index number /* objecr order */
8 ,obj_Flag varchar(10) /* D= Deleted, A= Added, C=Change */
9 );
10
11 TYPE t_lock_table IS TABLE OF t_lock_Rec
12 INDEX BY BINARY_INTEGER; /* The Object Id will used to Index */
13
14 /*-------------------------------------------------------------------------------------------------------------------
15 Procedure private functions for Measure Locking
16 -------------------------------------------------------------------------------------------------------------------*/
17 FUNCTION get_dataset_Name(
18 p_dataset_id IN NUMBER
19 ) RETURN VARCHAR2;
20 FUNCTION get_Datasource_Name(
21 p_datasource_id IN NUMBER
22 ) RETURN VARCHAR2;
23 /*------------------------------------------------------------------------------------------
24 Getting Time Stamp for Dataset
25 -------------------------------------------------------------------------------------------*/
26 Function GET_TIME_STAMP_DATASET (
27 p_dim_set_id IN number
28 ) return varchar2;
29 /*------------------------------------------------------------------------------------------
30 Getting Time Stamp for Datasource
31 -------------------------------------------------------------------------------------------*/
32 Function GET_TIME_STAMP_DATASOURCE (
33 p_measure_id IN number
34 ) return varchar2;
35 /*------------------------------------------------------------------------------------------
36 Setting Time Stamp for Data set
37 -------------------------------------------------------------------------------------------*/
38 Procedure SET_TIME_STAMP_DATASET (
39 p_dim_set_id IN number
40 ,x_return_status OUT NOCOPY varchar2
41 ,x_msg_count OUT NOCOPY number
42 ,x_msg_data OUT NOCOPY varchar2
43 );
44 /*------------------------------------------------------------------------------------------
45 Bug#4045278: Overloaded for Setting Time Stamp for Data set to take in last_update_date parameter
46 -------------------------------------------------------------------------------------------*/
47 Procedure SET_TIME_STAMP_DATASET (
48 p_dim_set_id IN number
49 ,p_lud IN BSC_SYS_DATASETS_B.LAST_UPDATE_DATE%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 Setting Time Stamp for Datasource
56 -------------------------------------------------------------------------------------------*/
57 Procedure SET_TIME_STAMP_DATASOURCE (
58 p_measure_id IN number
59 ,x_return_status OUT NOCOPY varchar2
60 ,x_msg_count OUT NOCOPY number
61 ,x_msg_data OUT NOCOPY varchar2
62 );
63 /*------------------------------------------------------------------------------------------
64 Bug#4045278: Overloaded for Setting Time Stamp for Datasource to take in last_update_date parameter
65 -------------------------------------------------------------------------------------------*/
66 Procedure SET_TIME_STAMP_DATASOURCE (
67 p_measure_id IN number
68 ,p_lud IN BSC_SYS_MEASURES.LAST_UPDATE_DATE%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 Lock a Datasets
75 -------------------------------------------------------------------------------------------------------------------*/
76 Procedure LOCK_DATASET (
77 p_dataset_id IN number
78 ,p_time_stamp IN varchar2 := null
79 ,x_measure_id1 OUT NOCOPY number
80 ,x_measure_id2 OUT NOCOPY number
81 ,x_return_status OUT NOCOPY varchar2
82 ,x_msg_count OUT NOCOPY number
83 ,x_msg_data OUT NOCOPY varchar2
84 );
85 /*------------------------------------------------------------------------------------------
86 Procedure to Lock a Datasource
87 -------------------------------------------------------------------------------------------------------------------*/
88 Procedure LOCK_DATASOURCE(
89 p_measure_id IN number
90 ,p_time_stamp IN varchar2 := null
91 ,p_dataset_name IN varchar2 := null
92 ,x_return_status OUT NOCOPY varchar2
93 ,x_msg_count OUT NOCOPY number
94 ,x_msg_data OUT NOCOPY varchar2
95 );
96
97 /*------------------------------------------------------------------------------------------
98 Private Procedures and functons
99 -------------------------------------------------------------------------------------------------------------------*/
100 FUNCTION get_Dim_Level_Name(
101 p_dim_level_id IN NUMBER
102 ) RETURN VARCHAR2;
103 FUNCTION get_Dim_Group_Name(
104 p_dim_group_id IN NUMBER
105 ) RETURN VARCHAR2;
106 FUNCTION get_Dim_Set_Name(
107 p_kpi_id IN NUMBER
108 ,p_dim_set_id IN NUMBER
109 ) RETURN VARCHAR2;
110 FUNCTION get_KPI_Name(
111 p_kpi_id IN NUMBER
112 ) RETURN VARCHAR2;
113
114 Procedure get_selected_dim_objs(
115 p_dimension_id IN NUMBER
116 ,x_selected_dim_objs OUT NOCOPY t_lock_table
117 ,x_return_status OUT NOCOPY varchar2
118 ,x_msg_count OUT NOCOPY number
119 ,x_msg_data OUT NOCOPY varchar2
120 );
121 Procedure get_kpi_dim_sets_by_dim(
122 p_selected_dimensions IN t_lock_table
123 ,x_selected_dim_sets OUT NOCOPY t_lock_table
124 ,x_return_status OUT NOCOPY varchar2
125 ,x_msg_count OUT NOCOPY number
126 ,x_msg_data OUT NOCOPY varchar2
127 );
128 Procedure get_kpi_dim_sets_by_Rel(
129 p_child_dim_obj IN number
130 ,p_parent_dim_obj IN number
131 ,x_selected_dim_sets OUT NOCOPY t_lock_table
132 ,x_return_status OUT NOCOPY varchar2
133 ,x_msg_count OUT NOCOPY number
134 ,x_msg_data OUT NOCOPY varchar2
135 );
136 Procedure get_selected_dimensions(
137 p_dim_obj_id IN NUMBER
138 ,x_selected_dimensions OUT NOCOPY t_lock_table
139 ,x_return_status OUT NOCOPY varchar2
140 ,x_msg_count OUT NOCOPY number
141 ,x_msg_data OUT NOCOPY varchar2
142 );
143
144 Procedure get_impacted_objects(
145 p_selected_objects IN t_lock_table
146 ,p_previous_objects IN t_lock_table
147 ,x_impacted_objects OUT NOCOPY t_lock_table
148 ,x_return_status OUT NOCOPY varchar2
149 ,x_msg_count OUT NOCOPY number
150 ,x_msg_data OUT NOCOPY varchar2
151 );
152 Procedure convert_table(
153 p_numberTable IN BSC_BIS_LOCKS_PUB.t_numberTable
154 ,x_lock_table OUT NOCOPY t_lock_table
155 ,x_return_status OUT NOCOPY varchar2
156 ,x_msg_count OUT NOCOPY number
157 ,x_msg_data OUT NOCOPY varchar2
158 );
159
160 /*------------------------------------------------------------------------------------------
161 Procedure to Lock a Dimension Objects
162 -------------------------------------------------------------------------------------------------------------------*/
163 Procedure LOCK_DIM_LEVEL(
164 p_dim_level_id IN number
165 ,p_time_stamp IN varchar2 := null
166 ,x_return_status OUT NOCOPY varchar2
167 ,x_msg_count OUT NOCOPY number
168 ,x_msg_data OUT NOCOPY varchar2
169 );
170
171 /*------------------------------------------------------------------------------------------
172 Procedure to Lock a Dimension Group
173 -------------------------------------------------------------------------------------------------------------------*/
174 Procedure LOCK_DIM_GROUP (
175 p_dim_group_id IN number
176 ,p_time_stamp IN varchar2 := null
177 ,x_return_status OUT NOCOPY varchar2
178 ,x_msg_count OUT NOCOPY number
179 ,x_msg_data OUT NOCOPY varchar2
180 );
181
182 /*------------------------------------------------------------------------------------------
183 Procedure to Lock a Dimension Set
184 -------------------------------------------------------------------------------------------------------------------*/
185 Procedure LOCK_DIM_SET (
186 p_kpi_id IN number
187 ,p_dim_set_id IN number
188 ,p_time_stamp IN varchar2 := null
189 ,x_return_status OUT NOCOPY varchar2
190 ,x_msg_count OUT NOCOPY number
191 ,x_msg_data OUT NOCOPY varchar2
192 );
193
194 /*------------------------------------------------------------------------------------------
195 Procedure to Lock a KPI
196 -------------------------------------------------------------------------------------------------------------------*/
197 Procedure LOCK_KPI(
198 p_kpi_id IN number
199 ,p_time_stamp IN varchar2 := null
200 ,p_full_lock_flag IN varchar2 := FND_API.G_FALSE
201 ,x_return_status OUT NOCOPY varchar2
202 ,x_msg_count OUT NOCOPY number
203 ,x_msg_data OUT NOCOPY varchar2
204 );
205
206 /*------------------------------------------------------------------------------------------
207 Getting Time Stamp for Dimension Level
208 ------------------------------------------------------------------------------------------*/
209 Function GET_TIME_STAMP_DIM_LEVEL(
210 p_dim_level_id IN number
211 ) return varchar2;
212
213 /*------------------------------------------------------------------------------------------
214 Getting Time Stamp for Dimension Group
215 -------------------------------------------------------------------------------------------*/
216 Function GET_TIME_STAMP_DIM_GROUP (
217 p_dim_group_id IN number
218 ) return varchar2;
219
220 /*------------------------------------------------------------------------------------------
221 Getting Time Stamp Dimension Set
222 -------------------------------------------------------------------------------------------*/
223 Function GET_TIME_STAMP_DIM_SET (
224 p_kpi_id IN number
225 ,p_dim_set_id IN number
226 ) return varchar2;
227
228 /*------------------------------------------------------------------------------------------
229 Getting Time Stamp for KPIs (Indicators)
230 -------------------------------------------------------------------------------------------*/
231 Function GET_TIME_STAMP_KPI (
232 p_kpi_id IN number
233 ) return varchar2;
234
235 /*------------------------------------------------------------------------------------------
236 Setting Time Stamp for Dimension Objects
237 -------------------------------------------------------------------------------------------*/
238 Procedure SET_TIME_STAMP_DIM_LEVEL (
239 p_dim_level_id IN number
240 ,x_return_status OUT NOCOPY varchar2
241 ,x_msg_count OUT NOCOPY number
242 ,x_msg_data OUT NOCOPY varchar2
243 );
244
245 /*------------------------------------------------------------------------------------------
246 Setting Time Stamp for Dimension Group
247 -------------------------------------------------------------------------------------------*/
248 Procedure SET_TIME_STAMP_DIM_GROUP (
249 p_dim_group_id IN number
250 ,x_return_status OUT NOCOPY varchar2
251 ,x_msg_count OUT NOCOPY number
252 ,x_msg_data OUT NOCOPY varchar2
253 );
254
255 /*------------------------------------------------------------------------------------------
256 Setting Time Stamp for Dimension Set
257 -------------------------------------------------------------------------------------------*/
258 Procedure SET_TIME_STAMP_DIM_SET (
259 p_kpi_id IN number
260 , p_dim_set_id IN number
261 ,x_return_status OUT NOCOPY varchar2
262 ,x_msg_count OUT NOCOPY number
263 ,x_msg_data OUT NOCOPY varchar2
264 );
265
266 /*------------------------------------------------------------------------------------------
267 Setting Time Stamp for KPI
268 -------------------------------------------------------------------------------------------*/
269 Procedure SET_TIME_STAMP_KPI (
270 p_kpi_id IN number
271 ,x_return_status OUT NOCOPY varchar2
272 ,x_msg_count OUT NOCOPY number
273 ,x_msg_data OUT NOCOPY varchar2
274 );
275
276 /*------------------------------------------------------------------------------------------
277 Procedure LOCK_CREATE_DIMENSION
278
279 This Procedure will make all the necessaries locks to Create a Dimensions (Dimension Group)
280 according with the PMD UI for 'Performance Measures > Dimensions > Create Dimension'
281 This procedure will lock all the dimension object that will assign to the new Dimension
282 <parameters>
283 p_selected_dim_objets: Array with the Ids corresponding to the Dimesion Objects
284 that will be assigned to the new dimension.
285 -------------------------------------------------------------------------------------------*/
286 Procedure LOCK_CREATE_DIMENSION (
287 p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
288 ,x_return_status OUT NOCOPY varchar2
289 ,x_msg_count OUT NOCOPY number
290 ,x_msg_data OUT NOCOPY varchar2
291 );
292
293 /*------------------------------------------------------------------------------------------
294 Procedure LOCK_UPDATE_DIMENSION
295 This Procedure will make all the necessaries locks to Update a Dimension (Dimension Group)
296 according with the PMD UI for 'Performance Measures > Dimensions > Update Dimension'
297 This procedure will lock the dimension passed in the parameter p_dimension_id,
298 the dimension objects passed in the parameter p_selected_dim_objets,
299 and the dimension set (in the kpis) that uses the dimension when it is necessary.
300 <parameters>
301 p_dimension_id: Dimension Id (Dimension Group) to update
302 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
303 that will have the dimension.
304 p_time_stamp: Last update of dimension information changed by the user
305
306
307 -------------------------------------------------------------------------------------------*/
308 Procedure LOCK_UPDATE_DIMENSION (
309 p_dimension_id IN number
310 ,p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
311 ,p_time_stamp IN varchar2 := null
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 LOCK_UPDATE_DIM_OBJ_IN_DIM
319 This procedure will make all the necessaries locks to Update a Dimension
320 Object propertis in a dimencion.
321 (Dimension level properties in a Dimension Group
322
323 -------------------------------------------------------------------------------------------*/
324 Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM(
325 p_dim_object_id IN number
326 ,p_dimension_id IN number
327 ,p_time_stamp IN varchar2
328 ,x_return_status OUT NOCOPY varchar2
329 ,x_msg_count OUT NOCOPY number
330 ,x_msg_data OUT NOCOPY varchar2
331 );
332
333 /*------------------------------------------------------------------------------------------
334 Procedure LOCK_CREATE_DIMENSION_OBJECT
335 This procedure will make all the necessaries locks to Create a Dimension Object (Dimension Level)
336 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
337 Create Dimension Object'
338 <parameters>
339 p_selected_dimensions: This Array has the Ids corresponding to the Dimensions where
340 the dimension object will be assigned.
341 -------------------------------------------------------------------------------------------*/
342 Procedure LOCK_CREATE_DIMENSION_OBJECT(
343 p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
344 ,x_return_status OUT NOCOPY varchar2
345 ,x_msg_count OUT NOCOPY number
346 ,x_msg_data OUT NOCOPY varchar2
347 );
348
349 /*------------------------------------------------------------------------------------------
350 Procedure LOCK_UPDATE_DIMENSION_OBJECT
351 This procedure will make all the necessaries locks to Update a Dimension Object (Dimension Level)
352 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
353 Update Dimension Object'
354 <parameters>
355 p_dim_object_id: Dimension Object Id (Dimension Level) to update
356 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
357 that will have the dimension.
358 p_time_stamp: Last update of dimension object information changed by the user.
359 It is mandatory in order of checking if the dimension object has been
360 updated by other user.
361 -------------------------------------------------------------------------------------------*/
362 Procedure LOCK_UPDATE_DIMENSION_OBJECT(
363 p_dim_object_id IN number
364 ,p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
365 ,p_time_stamp IN varchar2
366 ,x_return_status OUT NOCOPY varchar2
367 ,x_msg_count OUT NOCOPY number
368 ,x_msg_data OUT NOCOPY varchar2
369 );
370
371 /*------------------------------------------------------------------------------------------
372 Procedure LOCK_UPDATE_DIM_OBJ_RELATIONSHIPS
373 This process Lock all affected object when the relationships for a given dimension
374 object are updated.
375 <parameters>
376 p_dim_object_id: Dimension Object Id (Dimension Level) to update
377 p_selected_parends: This array has the Ids corresponding to the Parent Dimension Objects
378 that will have the dimension object (Selected Parent Dimension Objects)
379 p_selected_childs: This array has the Ids corresponding to the Child Dimension Objects
380 that will have the dimension object (Selected Child Dimension Objects).
381 p_time_stamp: Last update of dimension object information changed by the user.
382 It is mandatory in order of checking if the dimension object has
383 been updated by other user.
384 -------------------------------------------------------------------------------------------*/
385 Procedure LOCK_UPDATE_RELATIONSHIPS(
386 p_dim_object_id IN number
387 ,p_selected_parends IN BSC_BIS_LOCKS_PUB.t_numberTable
388 ,p_selected_childs IN BSC_BIS_LOCKS_PUB.t_numberTable
389 ,p_time_stamp IN varchar2
390 ,x_return_status OUT NOCOPY varchar2
391 ,x_msg_count OUT NOCOPY number
392 ,x_msg_data OUT NOCOPY varchar2
393 );
394
395 /*------------------------------------------------------------------------------------------
396 Procedure LOCK_ASSIGN_ DIM_SET
397 Use this procedure to lock necessary object when a Dimension Set need to be assign
398 to a specific Analysis Option
399 <parameters>
400 p_kpi_id : Indicator Id
401 p_dim_set_id : Dimension Set Id
402 p_time_stamp : Time stamp.
403
404 Note: By Now this parmeter will used to make the lock.
405 Future version will used other parameters
406
407 -------------------------------------------------------------------------------------------*/
408 Procedure LOCK_ASSIGN_DIM_SET (
409 p_kpi_id IN number
410 ,p_option_group0 IN number
411 ,p_option_group1 IN number
412 ,p_option_group2 IN number
413 ,p_serie_id IN number
414 ,p_dim_set_id IN number
415 ,p_time_stamp IN varchar2
416 ,x_return_status OUT NOCOPY varchar2
417 ,x_msg_count OUT NOCOPY number
418 ,x_msg_data OUT NOCOPY varchar2
419 );
420
421 /*------------------------------------------------------------------------------------------
422 -------------------------------------------------------------------------------------------*/
423 PROCEDURE LOCK_TAB
424 (
425 p_tab_id IN NUMBER
426 ,p_time_stamp IN VARCHAR2 := NULL
427 ,x_return_status OUT NOCOPY VARCHAR2
428 ,x_msg_count OUT NOCOPY NUMBER
429 ,x_msg_data OUT NOCOPY VARCHAR2
430 );
431
432
433 PROCEDURE LOCK_TAB_VIEW_ID
434 (
435 p_tab_id IN NUMBER
436 ,p_tab_view_id IN NUMBER
437 ,p_time_stamp IN VARCHAR2 := NULL
438 ,x_return_status OUT NOCOPY VARCHAR2
439 ,x_msg_count OUT NOCOPY NUMBER
440 ,x_msg_data OUT NOCOPY VARCHAR2
441 );
442
443 FUNCTION get_TabView_Name(
444 p_tab_id IN NUMBER
445 ,p_tab_view_id IN NUMBER
446 ) RETURN VARCHAR2 ;
447
448 FUNCTION get_tabview_time_stamp (
449 p_tab_id IN NUMBER
450 ,p_tab_view_id IN NUMBER
451 ) RETURN VARCHAR2;
452
453 FUNCTION get_tab_time_stamp(
454 p_tab_id IN NUMBER
455 )RETURN VARCHAR2;
456
457
458 /*------------------------------------------------------------------------------------------
459 *
460 * Calendar and Periodicities locking public APIs
461 *
462 -------------------------------------------------------------------------------------------*/
463
464
465 PROCEDURE Lock_Calendar (
466 p_Calendar_Id IN NUMBER
467 , p_Time_Stamp IN VARCHAR2
468 , x_Return_Status OUT NOCOPY VARCHAR2
469 , x_Msg_Count OUT NOCOPY NUMBER
470 , x_Msg_Data OUT NOCOPY VARCHAR2
471 );
472
473 PROCEDURE Lock_Periodicity (
474 p_Periodicity_Id IN NUMBER
475 , p_Time_Stamp IN VARCHAR2
476 , x_Return_Status OUT NOCOPY VARCHAR2
477 , x_Msg_Count OUT NOCOPY NUMBER
478 , x_Msg_Data OUT NOCOPY VARCHAR2
479 );
480
481 FUNCTION Get_Calendar_Name (p_Calendar_Id IN NUMBER) RETURN VARCHAR2;
482 FUNCTION Get_Periodicity_Name (p_Periodicity_Id IN NUMBER) RETURN VARCHAR2;
483
484
485 END BSC_BIS_LOCKS_PVT;