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