[Home] [Help]
PACKAGE BODY: APPS.HRI_EDW_DIM_EVENTS
Source
1 PACKAGE BODY hri_edw_dim_events AS
2 /* $Header: hriedevt.pkb 120.0 2005/05/29 07:08:13 appldev noship $ */
3
4 /******************************************************************************/
5 FUNCTION global_exists( p_user_event_type IN VARCHAR2 )
6 RETURN NUMBER IS
7
8 CURSOR row_exists_cur IS
9 SELECT 1
10 FROM hri_edw_user_events
11 WHERE user_event_type = 'GLOBAL_' || p_user_event_type;
12
13 l_temp NUMBER;
14
15 BEGIN
16
17 OPEN row_exists_cur;
18 FETCH row_exists_cur INTO l_temp;
19 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
20 l_temp := 0;
21 END IF;
22 CLOSE row_exists_cur;
23
24 RETURN l_temp;
25
26 END global_exists;
27
28 PROCEDURE update_global_enabled_flag( p_user_event_type IN VARCHAR2
29 , p_value IN VARCHAR2 )
30 IS
31
32 BEGIN
33
34 UPDATE hri_edw_user_events
35 SET global_enabled_flag = p_value
36 WHERE user_event_type = p_user_event_type
37 OR user_event_type = 'GLOBAL_' || p_user_event_type;
38
39 END update_global_enabled_flag;
40
41 PROCEDURE set_global( p_user_event_type IN VARCHAR2
42 , p_value IN NUMBER )
43 IS
44
45 l_global_exists NUMBER;
46
47 BEGIN
48
49 l_global_exists := global_exists( p_user_event_type );
50
51 IF (l_global_exists > 0) THEN
52 UPDATE hri_edw_user_events
53 SET global_threshold_value = p_value
54 WHERE user_event_type = 'GLOBAL_' || p_user_event_type
55 OR user_event_type = p_user_event_type;
56 END IF;
57
58 RETURN;
59
60 END set_global;
61
62
63 PROCEDURE add_global( p_user_event_type IN VARCHAR2
64 , p_threshold_units IN VARCHAR2
65 , p_global_threshold_value IN NUMBER
66 , p_global_enabled_flag IN VARCHAR2)
67 IS
68
69 l_global_exists NUMBER;
70
71 BEGIN
72
73 l_global_exists := global_exists( p_user_event_type );
74
75 IF (l_global_exists = 0) THEN
76 INSERT INTO hri_edw_user_events
77 ( user_event_id
78 , user_event_type
79 , threshold_units
80 , global_threshold_value
81 , global_enabled_flag)
82 VALUES
83 ( hri_edw_user_events_s.nextval
84 , 'GLOBAL_' || p_user_event_type
85 , p_threshold_units
86 , p_global_threshold_value
87 , p_global_enabled_flag);
88 END IF;
89
90 RETURN;
91
92 END add_global;
93
94 PROCEDURE enable_global( p_user_event_type IN VARCHAR2 )
95 IS
96
97 l_global_exists NUMBER;
98
99 BEGIN
100
101 l_global_exists := global_exists( p_user_event_type );
102
103 IF (l_global_exists > 0) THEN
104 update_global_enabled_flag( p_user_event_type, 'Y' );
105 END IF;
106
107 END enable_global;
108
109
110 PROCEDURE disable_global( p_user_event_type IN VARCHAR2 )
111 IS
112
113 l_global_exists NUMBER;
114
115 BEGIN
116
117 l_global_exists := global_exists( p_user_event_type );
118
119 IF (l_global_exists > 0) THEN
120 update_global_enabled_flag( p_user_event_type, 'N' );
121 END IF;
122
123 END disable_global;
124
125
126 PROCEDURE add_event( p_user_event_type IN VARCHAR2
127 , p_event_code IN VARCHAR2
128 , p_event_threshold IN NUMBER )
129 IS
130
131 l_global_threshold_value NUMBER;
132 l_global_enabled_flag VARCHAR2(30);
133 l_threshold_units VARCHAR2(30);
134
135 CURSOR global_values_cur IS
136 SELECT glb.global_threshold_value
137 ,glb.global_enabled_flag
138 ,glb.threshold_units
139 FROM hri_edw_user_events glb
140 WHERE glb.user_event_type = 'GLOBAL_' || p_user_event_type
141 AND NOT EXISTS (SELECT 1
142 FROM hri_edw_user_events evt
143 WHERE evt.user_event_type = p_user_event_type
144 AND evt.event_code = p_event_code);
145
146
147 BEGIN
148
149 OPEN global_values_cur;
150 FETCH global_values_cur INTO l_global_threshold_value,
151 l_global_enabled_flag, l_threshold_units;
152 IF (global_values_cur%NOTFOUND OR global_values_cur%NOTFOUND IS NULL) THEN
153 CLOSE global_values_cur;
154 ELSE
155 INSERT INTO hri_edw_user_events
156 ( user_event_id
157 , user_event_type
158 , event_code
159 , threshold_value
160 , threshold_units
161 , global_threshold_value
162 , global_enabled_flag)
163 VALUES
164 ( hri_edw_user_events_s.nextval
165 , p_user_event_type
166 , p_event_code
167 , p_event_threshold
168 , l_threshold_units
169 , l_global_threshold_value
170 , l_global_enabled_flag);
171 END IF;
172
173 RETURN;
174
175 END add_event;
176
177 PROCEDURE drop_event( p_user_event_type IN VARCHAR2
178 , p_event_code IN VARCHAR2)
179 IS
180
181 BEGIN
182
183 DELETE FROM hri_edw_user_events
184 WHERE user_event_type = p_user_event_type
185 AND event_code = p_event_code;
186
187 RETURN;
188
189 END drop_event;
190
191 PROCEDURE load_hrhy_row( p_event_id IN NUMBER,
192 p_owner IN VARCHAR2,
193 p_hierarchy IN VARCHAR2,
194 p_level_number IN NUMBER,
195 p_event_code IN VARCHAR2,
196 p_parent_event_id IN NUMBER,
197 p_reason_type IN VARCHAR2,
198 p_user_event_type IN VARCHAR2 )
199 IS
200
201 l_event_code VARCHAR2(30);
202 l_parent_event_id NUMBER;
203 l_reason_type VARCHAR2(30);
204 l_user_event_type VARCHAR2(30);
205
206 CURSOR row_exists_cur IS
207 SELECT
208 event_code
209 ,parent_event_id
210 ,reason_type
211 ,user_event_type
212 FROM hri_edw_event_hrchys
213 WHERE hierarchy = p_hierarchy
214 AND level_number = p_level_number
215 AND event_id = p_event_id;
216
217 BEGIN
218
219 OPEN row_exists_cur;
220 FETCH row_exists_cur INTO l_event_code,
221 l_parent_event_id,
222 l_reason_type,
223 l_user_event_type;
224 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
225 CLOSE row_exists_cur;
226 INSERT INTO hri_edw_event_hrchys
227 ( event_id
228 , hierarchy
229 , level_number
230 , event_code
231 , parent_event_id
232 , reason_type
233 , user_event_type )
234 values
235 ( p_event_id
236 , p_hierarchy
237 , p_level_number
238 , p_event_code
239 , p_parent_event_id
240 , p_reason_type
241 , p_user_event_type );
242 ELSE
243 CLOSE row_exists_cur;
244 UPDATE hri_edw_event_hrchys
245 SET event_code = p_event_code
246 , parent_event_id = p_parent_event_id
247 , reason_type = p_reason_type
248 , user_event_type = p_user_event_type
249 WHERE hierarchy = p_hierarchy
250 AND level_number = p_level_number
251 AND event_id = p_event_id;
252 END IF;
253
254 END load_hrhy_row;
255
256 PROCEDURE load_user_row( p_user_event_type IN VARCHAR2,
257 p_event_code IN VARCHAR2,
258 p_owner IN VARCHAR2,
259 p_threshold_value IN NUMBER,
260 p_threshold_units IN VARCHAR2,
261 p_glbl_thr_value IN NUMBER,
262 p_global_flag IN VARCHAR2 )
263 IS
264
265 l_user_event_type VARCHAR2(30);
266 l_event_code VARCHAR2(30);
267 l_threshold_value NUMBER;
268 l_threshold_units VARCHAR2(30);
269 l_glbl_thr_value NUMBER;
270 l_global_flag VARCHAR2(30);
271
272 CURSOR row_exists_cur IS
273 SELECT
274 user_event_type
275 ,event_code
276 ,threshold_value
277 ,threshold_units
278 ,global_threshold_value
279 ,global_enabled_flag
280 FROM hri_edw_user_events
281 WHERE user_event_type = p_user_event_type
282 AND (event_code = p_event_code OR event_code IS NULL);
283
284 BEGIN
285
286 OPEN row_exists_cur;
287 FETCH row_exists_cur INTO l_user_event_type,
288 l_event_code,
289 l_threshold_value,
290 l_threshold_units,
291 l_glbl_thr_value,
292 l_global_flag;
293 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
294 CLOSE row_exists_cur;
295 INSERT INTO hri_edw_user_events
296 ( user_event_id
297 , user_event_type
298 , event_code
299 , threshold_value
300 , threshold_units
301 , global_threshold_value
302 , global_enabled_flag )
303 values
304 ( hri_edw_user_events_s.nextval
305 , p_user_event_type
306 , p_event_code
307 , p_threshold_value
308 , p_threshold_units
309 , p_glbl_thr_value
310 , p_global_flag );
311 ELSE
312 CLOSE row_exists_cur;
313 UPDATE hri_edw_user_events
314 SET threshold_value = p_threshold_value
315 , threshold_units = p_threshold_units
316 , global_threshold_value = p_glbl_thr_value
317 , global_enabled_flag = p_global_flag
318 WHERE user_event_type = p_user_event_type
319 AND (event_code = p_event_code OR event_code IS NULL);
320 END IF;
321
322 END load_user_row;
323
324 PROCEDURE load_cmbn_row( p_combination_id IN NUMBER,
325 p_owner IN VARCHAR2,
326 p_gain_event_id IN NUMBER,
327 p_loss_event_id IN NUMBER,
328 p_rec_event_id IN NUMBER,
329 p_sep_event_id IN NUMBER,
330 p_reason_type IN VARCHAR2,
331 p_facts IN VARCHAR2,
332 p_description IN VARCHAR2 )
333 IS
334
335 l_combination_id NUMBER;
336 l_gain_event_id NUMBER;
337 l_loss_event_id NUMBER;
338 l_rec_event_id NUMBER;
339 l_sep_event_id NUMBER;
340 l_reason_type VARCHAR2(30);
341 l_facts VARCHAR2(30);
342 l_description VARCHAR2(80);
343
344 CURSOR row_exists_cur IS
345 SELECT
346 combination_id
347 ,gain_event_id
348 ,loss_event_id
349 ,rec_event_id
350 ,sep_event_id
351 ,reason_type
352 ,facts
353 ,description
354 FROM hri_edw_event_hrchy_cmbns
355 WHERE combination_id = p_combination_id;
356
357 BEGIN
358
359 OPEN row_exists_cur;
360 FETCH row_exists_cur INTO l_combination_id,
361 l_gain_event_id,
362 l_loss_event_id,
363 l_rec_event_id,
364 l_sep_event_id,
365 l_reason_type,
366 l_facts,
367 l_description;
368 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
369 CLOSE row_exists_cur;
370 INSERT INTO hri_edw_event_hrchy_cmbns
371 ( combination_id
372 , gain_event_id
373 , loss_event_id
374 , rec_event_id
375 , sep_event_id
376 , reason_type
377 , facts
378 , description )
379 values
380 ( p_combination_id
381 , p_gain_event_id
382 , p_loss_event_id
383 , p_rec_event_id
384 , p_sep_event_id
385 , p_reason_type
386 , p_facts
387 , p_description );
388 ELSE
389 CLOSE row_exists_cur;
390 UPDATE hri_edw_event_hrchy_cmbns
391 SET gain_event_id = p_gain_event_id
392 , loss_event_id = p_loss_event_id
393 , rec_event_id = p_rec_event_id
394 , sep_event_id = p_sep_event_id
395 , reason_type = p_reason_type
396 , facts = p_facts
397 , description = p_description
398 WHERE combination_id = p_combination_id;
399 END IF;
400
401 END load_cmbn_row;
402
403
404 /******************************************************************************/
405
406 END hri_edw_dim_events;