1 PACKAGE BSC_LOCKS_PUB AS
2 /* $Header: BSCPLOKS.pls 120.3 2005/09/19 18:00:43 calaw noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'BSC_LOCKS_PUB';
5 --TYPE T_ARRAY_OF_LOCK_OBJECT_KEY IS TABLE OF VARCHAR2(500);
6 --TYPE T_ARRAY_OF_LOCK_OBJECT_TYPE IS TABLE OF VARCHAR2(50);
7
8 /*------------------------------------------------------------------------------------------
9 Procedure CHECK_SYSTEM_LOCK
10 This procedure is called when users enter a UI flow. It verifies that the
11 Object being modified is not locked. If the Object is locked, some other user
12 is currently in the process of saving his changes to the database.
13 As a result, the value retrieved from the database is still the old value.
14 An error will be raised to indicate that the user has to wait until
15 the save is completed.
16 <parameters>
17 p_object_key: The primary key of the Object, usually the TO_CHAR value
18 of the Object ID. If the Object has composite keys,
19 the value to pass in will be a concatenation of
20 all the keys, separated by commas
21 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
22 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
23 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
24 "PERIODICITY", and "TABLE"
25 p_program_id: -100 = Data Loader UI
26 -101 = Data Loader Backend
27 -200 = Generate Database
28 -201 = Generate Documentation
29 -202 = Rename Interface Table
30 -203 = Generate Database Configuration
31 -300 = Administrator
32 -400 = Objective Designer
33 -500 = Builder
34 -600 = Performance Scorecard
35 -700 = System Upgrade
36 -800 = System Migration
37 p_user_id: Application User ID
38 p_cascade_lock_level: Number of level for cascade locks
39 Default is -1 which means enable cascade locking
40 all the way to the lowest level
41 -------------------------------------------------------------------------------------------*/
42 Procedure CHECK_SYSTEM_LOCK (
43 p_object_key IN varchar2
44 ,p_object_type IN varchar2
45 ,p_program_id IN number
46 ,p_user_id IN number := NULL
47 ,p_cascade_lock_level IN number := -1
48 ,x_return_status OUT NOCOPY varchar2
49 ,x_msg_count OUT NOCOPY number
50 ,x_msg_data OUT NOCOPY varchar2
51 );
52
53
54 /*------------------------------------------------------------------------------------------
55 Procedure CHECK_SYSTEM_LOCKS
56 This procedure is called when users enter a UI flow. It verifies that the
57 Objects being modified are not locked. If any of the Objects is locked,
58 some other user is currently in the process of saving his changes to the database.
59 As a result, the value retrieved from the database is still the old value.
60 An error will be raised to indicate that the user has to wait until
61 the save is completed.
62 <parameters>
63 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
64 of the Object ID. If the Object has composite keys,
65 the value to pass in will be a concatenation of
66 all the keys, separated by commas
67 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
68 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
69 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
70 "PERIODICITY", and "TABLE"
71 p_program_id: -100 = Data Loader UI
72 -101 = Data Loader Backend
73 -200 = Generate Database
74 -201 = Generate Documentation
75 -202 = Rename Interface Table
76 -203 = Generate Database Configuration
77 -300 = Administrator
78 -400 = Objective Designer
79 -500 = Builder
80 -600 = Performance Scorecard
81 -700 = System Upgrade
82 -800 = System Migration
83 p_user_id: Application User ID
84 p_cascade_lock_level: Number of level for cascade locks
85 Default is -1 which means enable cascade locking
86 all the way to the lowest level
87 -------------------------------------------------------------------------------------------*/
88 Procedure CHECK_SYSTEM_LOCKS (
89 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
90 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
91 ,p_program_id IN number
92 ,p_user_id IN number := NULL
93 ,p_cascade_lock_level IN number := -1
94 ,x_return_status OUT NOCOPY varchar2
95 ,x_msg_count OUT NOCOPY number
96 ,x_msg_data OUT NOCOPY varchar2
97 );
98
99
100 /*------------------------------------------------------------------------------------------
101 Function GET_SYSTEM_TIME
102 This function returns the current database system date.
103 The system date will be cached by the calling module as the "Query Time",
104 which will be used by BSC_LOCKS_PUB.Get_System_Lock to determine
105 if a lock can be acquired on an Object.
106 <parameters>
107 none
108 -------------------------------------------------------------------------------------------*/
109 Function GET_SYSTEM_TIME
110 return DATE;
111
112
113 /*------------------------------------------------------------------------------------------
114 Procedure SYNCHRONIZE
115 This procedure removes the invalid and deleted Objects from the locking tables
116 When we delete an Object or update the key value of an Object,
117 the entries in the lock tables remain there. After some time,
118 more and more invalid rows will be in the lock tables.
119 This SYNCHRONIZE api cleans up the lock tables and synchronizes the data
120 with the latest metadata. It will be called by the Generate Database
121 concurrent request after the generation process has completed.
122 <parameters>
123 p_program_id: -100 = Data Loader UI
124 -101 = Data Loader Backend
125 -200 = Generate Database
126 -201 = Generate Documentation
127 -202 = Rename Interface Table
128 -203 = Generate Database Configuration
129 -300 = Administrator
130 -400 = Objective Designer
131 -500 = Builder
132 -600 = Performance Scorecard
133 -700 = System Upgrade
134 -800 = System Migration
135 p_user_id: Application User ID
136 -------------------------------------------------------------------------------------------*/
137 Procedure SYNCHRONIZE (
138 p_program_id IN number
139 ,p_user_id IN number
140 ,x_return_status OUT NOCOPY varchar2
141 ,x_msg_count OUT NOCOPY number
142 ,x_msg_data OUT NOCOPY varchar2
143 );
144
145
146 /*------------------------------------------------------------------------------------------
147 Procedure GET_SYSTEN_LOCK
148 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
149 to be the locking procedure for BSC modules. Instead of calling the API
150 at the start of the process flow, this new API will be called right
151 before the changes are committed to the database.
152
153 Passing in the key and type of the top-level object, this API will
154 figure out all the related objects that needed to be locked.
155 If the locks cannot be acquired, either because some other users
156 are possessing one of more of the required locks or the data
157 has been modified since the last query time, an exception will be raised
158 indicating that the user has to requery and re-do the changes.
159 <parameters>
160 p_object_key: The primary key of the Object, usually the TO_CHAR value
161 of the Object ID. If the Object has composite keys,
162 the value to pass in will be a concatenation of
163 all the keys, separated by commas
164 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
165 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
166 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
167 "PERIODICITY", and "TABLE"
168 p_lock_type: 'W' for write lock, 'R' for read lock
169 p_query_time: The query time at the start of the process flow
170 p_program_id: -100 = Data Loader UI
171 -101 = Data Loader Backend
172 -200 = Generate Database
173 -201 = Generate Documentation
174 -202 = Rename Interface Table
175 -203 = Generate Database Configuration
176 -300 = Administrator
177 -400 = Objective Designer
178 -500 = Builder
179 -600 = Performance Scorecard
180 -700 = System Upgrade
181 -800 = System Migration
182 p_user_id: Application User ID
183 p_cascade_lock_level: Number of level for cascade locks
184 Default is -1 which means enable cascade locking
185 all the way to the lowest level
186 -------------------------------------------------------------------------------------------*/
187 Procedure GET_SYSTEM_LOCK (
188 p_object_key IN varchar2
189 ,p_object_type IN varchar2
190 ,p_lock_type IN varchar2 := 'W'
191 ,p_query_time IN date
192 ,p_program_id IN number
193 ,p_user_id IN number := NULL
194 ,p_cascade_lock_level IN number := -1
195 ,x_return_status OUT NOCOPY varchar2
196 ,x_msg_count OUT NOCOPY number
197 ,x_msg_data OUT NOCOPY varchar2
198 );
199
200
201 /*------------------------------------------------------------------------------------------
202 Procedure GET_SYSTEN_LOCKS
203 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
204 to be the locking procedure for BSC modules. Instead of calling the API
205 at the start of the process flow, this new API will be called right
206 before the changes are committed to the database.
207
208 Passing in the keys and types of the top-level objects, this API will
209 figure out all the related objects that needed to be locked.
210 If the locks cannot be acquired, either because some other users
211 are possessing one of more of the required locks or the data
212 has been modified since the last query time, an exception will be raised
216 of the Object IDs. If the Object has composite keys,
213 indicating that the user has to requery and re-do the changes.
214 <parameters>
215 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
217 the value to pass in will be a concatenation of
218 all the keys, separated by commas
219 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
220 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
221 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
222 "PERIODICITY", and "TABLE"
223 p_lock_type: 'W' for write lock, 'R' for read lock
224 p_query_time: The query time at the start of the process flow
225 p_program_id: -100 = Data Loader UI
226 -101 = Data Loader Backend
227 -200 = Generate Database
228 -201 = Generate Documentation
229 -202 = Rename Interface Table
230 -203 = Generate Database Configuration
231 -300 = Administrator
232 -400 = Objective Designer
233 -500 = Builder
234 -600 = Performance Scorecard
235 -700 = System Upgrade
236 -800 = System Migration
237 p_user_id: Application User ID
238 p_cascade_lock_level: Number of level for cascade locks
239 Default is -1 which means enable cascade locking
240 all the way to the lowest level
241 -------------------------------------------------------------------------------------------*/
242 Procedure GET_SYSTEM_LOCKS (
243 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
244 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
245 ,p_lock_type IN varchar2 := 'W'
246 ,p_query_time IN date
247 ,p_program_id IN number
248 ,p_user_id IN number := NULL
249 ,p_cascade_lock_level IN number := -1
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 /*------------------------------------------------------------------------------------------
257 Procedure GET_SYSTEN_LOCKS
258 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
259 to be the locking procedure for BSC modules. Instead of calling the API
260 at the start of the process flow, this new API will be called right
261 before the changes are committed to the database.
262
263 Passing in the keys and types of the top-level objects, this API will
264 figure out all the related objects that needed to be locked.
265 If the locks cannot be acquired, either because some other users
266 are possessing one of more of the required locks or the data
267 has been modified since the last query time, an exception will be raised
268 indicating that the user has to requery and re-do the changes.
269 <parameters>
270 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
271 of the Object IDs. If the Object has composite keys,
272 the value to pass in will be a concatenation of
273 all the keys, separated by commas
274 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
275 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
276 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
277 "PERIODICITY", and "TABLE"
278 p_lock_types: 'W' for write lock, 'R' for read lock
279 p_query_time: The query time at the start of the process flow
280 p_program_id: -100 = Data Loader UI
281 -101 = Data Loader Backend
282 -200 = Generate Database
283 -201 = Generate Documentation
284 -202 = Rename Interface Table
285 -203 = Generate Database Configuration
286 -300 = Administrator
287 -400 = Objective Designer
288 -500 = Builder
289 -600 = Performance Scorecard
290 -700 = System Upgrade
291 -800 = System Migration
292 p_user_id: Application User ID
293 p_cascade_lock_level: Number of level for cascade locks
294 Default is -1 which means enable cascade locking
295 all the way to the lowest level
296 -------------------------------------------------------------------------------------------*/
297 Procedure GET_SYSTEM_LOCKS (
298 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
299 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
300 ,p_lock_types IN BSC_LOCK_LOCK_TYPE_LIST
301 ,p_query_time IN date
302 ,p_program_id IN number
303 ,p_user_id IN number := NULL
304 ,p_cascade_lock_level IN number := -1
305 ,x_return_status OUT NOCOPY varchar2
306 ,x_msg_count OUT NOCOPY number
307 ,x_msg_data OUT NOCOPY varchar2
308 );
309
310
311 /*------------------------------------------------------------------------------------------
312 Procedure GET_SYSTEN_LOCK
313 This procedure locks all the objects with a certain type
314 <parameters>
318 "PERIODICITY", and "TABLE"
315 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
316 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
317 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
319 p_lock_type: 'W' for write lock, 'R' for read lock
320 p_query_time: The query time at the start of the process flow
321 p_program_id: -100 = Data Loader UI
322 -101 = Data Loader Backend
323 -200 = Generate Database
324 -201 = Generate Documentation
325 -202 = Rename Interface Table
326 -203 = Generate Database Configuration
327 -300 = Administrator
328 -400 = Objective Designer
329 -500 = Builder
330 -600 = Performance Scorecard
331 -700 = System Upgrade
332 -800 = System Migration
333 p_user_id: Application User ID
334 p_cascade_lock_level: Number of level for cascade locks
335 Default is -1 which means enable cascade locking
336 all the way to the lowest level
337 -------------------------------------------------------------------------------------------*/
338 Procedure GET_SYSTEM_LOCK (
339 p_object_type IN varchar2
340 ,p_lock_type IN varchar2 := 'W'
341 ,p_query_time IN date
342 ,p_program_id IN number
343 ,p_user_id IN number := NULL
344 ,p_cascade_lock_level IN number := -1
345 ,x_return_status OUT NOCOPY varchar2
346 ,x_msg_count OUT NOCOPY number
347 ,x_msg_data OUT NOCOPY varchar2
348 );
349
350
351 /*------------------------------------------------------------------------------------------
352 Procedure GET_SYSTEN_LOCK
353 This procedure locks the whole table. This feature is used in Migration.
354 <parameters>
355 p_program_id: -100 = Data Loader UI
356 -101 = Data Loader Backend
357 -200 = Generate Database
358 -201 = Generate Documentation
359 -202 = Rename Interface Table
360 -203 = Generate Database Configuration
361 -300 = Administrator
362 -400 = Objective Designer
363 -500 = Builder
364 -600 = Performance Scorecard
365 -700 = System Upgrade
366 -800 = System Migration
367 p_user_id: Application User ID
368 -------------------------------------------------------------------------------------------*/
369 Procedure GET_SYSTEM_LOCK (
370 p_program_id IN number
371 ,p_user_id IN number
372 ,x_return_status OUT NOCOPY varchar2
373 ,x_msg_count OUT NOCOPY number
374 ,x_msg_data OUT NOCOPY varchar2
375 );
376
377
378 /*------------------------------------------------------------------------------------------
379 Procedure GET_SYSTEM_LOCK
380 Due to the fact that not all the BSC modules will uptake the new locking scheme
381 immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
382 for backward compatibility issues. In this procedure, we will check for whole
383 system exclusive locks acquired by modules that haven't uptaken the new
384 locking scheme. Only when none of these modules have acquired locks that
385 the new Object-Level locking will be proceeded. Next, a row will be inserted
386 into BSC_CURRENT_SESSIONS. It will be seen as a whole system exclusive lock by
387 modules haven't implemented the new locking scheme. For modules that have uptaken
388 the new locking scheme, those entries in BSC_CURRENT_SESSION will be ignored.
389 <parameters>
390 p_program_id: -100 = Data Loader UI
391 -101 = Data Loader Backend
392 -200 = Generate Database
393 -201 = Generate Documentation
394 -202 = Rename Interface Table
395 -203 = Generate Database Configuration
396 -300 = Administrator
397 -400 = Objective Designer
398 -500 = Builder
399 -600 = Performance Scorecard
400 -700 = System Upgrade
401 -800 = System Migration
402 p_user_id: Application User ID
403 p_icx_session_id: Application Session ID
404 -------------------------------------------------------------------------------------------*/
405 Procedure GET_SYSTEM_LOCK (
406 p_program_id IN number
407 ,p_user_id IN number
408 ,p_icx_session_id IN number
409 ,x_return_status OUT NOCOPY varchar2
410 ,x_msg_count OUT NOCOPY number
411 ,x_msg_data OUT NOCOPY varchar2
412 );
413
414
415 /*------------------------------------------------------------------------------------------
416 Procedure REMOVE_SYSTEM_LOCK
417 Due to the fact that not all the BSC modules will uptake the new locking scheme
418 immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
419 for backward compatibility issues. This procedure will be called at the end
420 of the process. The entry in BSC_CURRENT_SESSIONS for the current session
421 will be deleted
422 <parameters>
426
423 none
424 -------------------------------------------------------------------------------------------*/
425 Procedure REMOVE_SYSTEM_LOCK;
427 /*------------------------------------------------------------------------------------------
428 -------------------------------------------------------------------------------------------*/
429 END BSC_LOCKS_PUB;
[Home] [Help]