1 PACKAGE BODY hri_opl_suph_events AS
2 /* $Header: hrioshe.pkb 120.4 2005/11/11 03:06:42 jtitmas noship $ */
3
4 -- End of time
5 g_end_of_time DATE := hr_general.end_of_time;
6
7 -- Global HRI Multithreading Array
8 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
9
10 -- Global parameters
11 g_refresh_start_date DATE;
12 g_full_refresh VARCHAR2(30);
13
14 g_sysdate DATE;
15 g_user NUMBER;
16
17 -- ----------------------------------------------------------------------------
18 -- Runs given sql statement dynamically
19 -- ----------------------------------------------------------------------------
20 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
21
22 BEGIN
23
24 EXECUTE IMMEDIATE p_sql_stmt;
25
26 EXCEPTION WHEN OTHERS THEN
27
28 null;
29
30 END run_sql_stmt_noerr;
31
32 -- ----------------------------------------------------------------------------
33 -- Sets global parameters from multi-threading process parameters
34 -- ----------------------------------------------------------------------------
35 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER) IS
36
37 BEGIN
38
39 -- If parameters haven't already been set, then set them
40 IF (g_refresh_start_date IS NULL) THEN
41 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
42 (p_mthd_action_id);
43 g_refresh_start_date := g_mthd_action_array.collect_from_date;
44 g_full_refresh := g_mthd_action_array.full_refresh_flag;
45 g_sysdate := sysdate;
46 g_user := fnd_global.user_id;
47 END IF;
48
49 END set_parameters;
50
51 -- Deletes and replaces the assignment records to be refreshed in
52 -- the supervisor hierarchy events helper table
53 PROCEDURE process_range_incr(p_start_asg_id IN NUMBER,
54 p_end_asg_id IN NUMBER) IS
55
56 l_dummy1 VARCHAR2(2000);
57 l_dummy2 VARCHAR2(2000);
58 l_schema VARCHAR2(400);
59
60 BEGIN
61
62 -- Single thread insert
63 INSERT INTO hri_cs_asgn_suph_events_ct
64 (assignment_id
65 ,effective_start_date
66 ,effective_end_date
67 ,person_id
68 ,supervisor_person_id
69 ,supervisor_assignment_id
70 ,business_group_id
71 ,assignment_type
72 ,primary_flag
73 ,assignment_status_type_id
74 ,last_update_date
75 ,last_updated_by
76 ,last_update_login
77 ,created_by
78 ,creation_date)
79 SELECT
80 chg.assignment_id
81 ,chg.effective_start_date
82 -- Day before date of next supervisor change or if no further changes
83 -- then the latest end date for the assignment
84 ,NVL(LEAD(chg.effective_start_date, 1) OVER
85 (PARTITION BY chg.assignment_id
86 ORDER BY chg.effective_start_date) - 1
87 ,chg.latest_end_date)
88 effective_end_date
89 ,chg.person_id
90 ,chg.supervisor_id supervisor_person_id
91 ,TO_NUMBER(NULL) supervisor_assignment_id
92 ,chg.business_group_id
93 ,chg.assignment_type
94 ,chg.primary_flag
95 ,chg.assignment_status_type_id
96 ,g_sysdate
97 ,g_user
98 ,g_user
99 ,g_user
100 ,g_sysdate
101 FROM
102 (SELECT
103 prv.assignment_id
104 ,prv.effective_start_date
105 -- Latest end date for an active assignment
106 ,LAST_VALUE(prv.effective_end_date) OVER
107 (PARTITION BY prv.assignment_id
108 ORDER BY prv.effective_start_date
109 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
110 latest_end_date
111 ,prv.person_id
112 ,prv.supervisor_id
113 ,prv.business_group_id
114 ,prv.assignment_type
115 ,prv.primary_flag
116 ,prv.assignment_status_type_id
117 -- Previous supervisor value - set first row so that it is always
118 -- picked up as a change
119 ,NVL(LAG(prv.supervisor_id, 1) OVER
120 (PARTITION BY prv.assignment_id
121 ORDER BY prv.effective_start_date)
122 ,-999)
123 supervisor_prv_id
124 FROM
125 (SELECT
126 asg.assignment_id
127 ,GREATEST(asg.effective_start_date, eq.erlst_evnt_effective_date)
128 effective_start_date
129 ,asg.effective_end_date
130 ,asg.person_id
131 ,NVL(asg.supervisor_id, -1) supervisor_id
132 ,asg.primary_flag
133 ,asg.assignment_status_type_id
134 ,asg.assignment_type
135 ,asg.business_group_id
136 FROM
137 hri_eq_sprvsr_hrchy_chgs eq
138 ,per_all_assignments_f asg
139 ,per_assignment_status_types ast
140 WHERE asg.assignment_type IN ('E', 'C')
141 AND asg.primary_flag = 'Y'
142 AND eq.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
143 AND asg.effective_end_date >= eq.erlst_evnt_effective_date
144 AND eq.assignment_id = asg.assignment_id
145 AND ast.assignment_status_type_id = asg.assignment_status_type_id
146 AND ast.per_system_status <> 'TERM_ASSIGN'
147 ) prv
148 ) chg
149 -- Filter out date-tracked records where no supervisor change has occurred
150 WHERE chg.supervisor_id <> chg.supervisor_prv_id;
151
152 COMMIT;
153
154 END process_range_incr;
155
156 -- Truncates and repopulates the supervisor events helper table
157 PROCEDURE process_range_full(p_start_asg_id IN NUMBER,
158 p_end_asg_id IN NUMBER) IS
159
160 BEGIN
161
162 -- Single thread insert
163 INSERT INTO hri_cs_asgn_suph_events_ct
164 (assignment_id
165 ,effective_start_date
166 ,effective_end_date
167 ,person_id
168 ,supervisor_person_id
169 ,supervisor_assignment_id
170 ,business_group_id
171 ,assignment_type
172 ,primary_flag
173 ,assignment_status_type_id
174 ,last_update_date
175 ,last_updated_by
176 ,last_update_login
177 ,created_by
178 ,creation_date)
179 SELECT
180 chg.assignment_id
181 ,chg.effective_start_date
182 -- Day before date of next supervisor change or if no further changes
183 -- then the latest end date for the assignment
184 ,NVL(LEAD(chg.effective_start_date, 1) OVER
185 (PARTITION BY chg.assignment_id
186 ORDER BY chg.effective_start_date) - 1
187 ,chg.latest_end_date)
188 effective_end_date
189 ,chg.person_id
190 ,chg.supervisor_id supervisor_person_id
191 ,TO_NUMBER(NULL) supervisor_assignment_id
192 ,chg.business_group_id
193 ,chg.assignment_type
194 ,chg.primary_flag
195 ,chg.assignment_status_type_id
196 ,g_sysdate
197 ,g_user
198 ,g_user
199 ,g_user
200 ,g_sysdate
201 FROM
202 (SELECT
203 prv.assignment_id
204 ,prv.effective_start_date
205 -- Latest end date for an active assignment
206 ,LAST_VALUE(prv.effective_end_date) OVER
207 (PARTITION BY prv.assignment_id
208 ORDER BY prv.effective_start_date
209 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
210 latest_end_date
211 ,prv.person_id
212 ,prv.supervisor_id
213 ,prv.business_group_id
214 ,prv.assignment_type
215 ,prv.primary_flag
216 ,prv.assignment_status_type_id
217 -- Previous supervisor value - set first row so that it is always
218 -- picked up as a change
219 ,NVL(LAG(prv.supervisor_id, 1) OVER
220 (PARTITION BY prv.assignment_id
221 ORDER BY prv.effective_start_date)
222 ,-999)
223 supervisor_prv_id
224 FROM
225 (SELECT
226 asg.assignment_id
227 ,GREATEST(asg.effective_start_date, g_refresh_start_date)
228 effective_start_date
229 ,asg.effective_end_date
230 ,asg.person_id
231 ,NVL(asg.supervisor_id, -1) supervisor_id
232 ,asg.primary_flag
233 ,asg.assignment_status_type_id
234 ,asg.assignment_type
235 ,asg.business_group_id
236 FROM
237 per_all_assignments_f asg
238 ,per_assignment_status_types ast
239 WHERE asg.assignment_type IN ('E', 'C')
240 AND asg.primary_flag = 'Y'
241 AND asg.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
242 AND asg.effective_end_date >= g_refresh_start_date
243 AND ast.assignment_status_type_id = asg.assignment_status_type_id
244 AND ast.per_system_status <> 'TERM_ASSIGN'
245 ) prv
246 ) chg
247 -- Filter out date-tracked records where no supervisor change has occurred
248 WHERE chg.supervisor_id <> chg.supervisor_prv_id;
249
250 COMMIT;
251
252 END process_range_full;
253
254 -- ----------------------------------------------------------------------------
255 -- PROCESS_RANGE
256 -- This procedure includes the logic required for processing the assignments
257 -- which have been included in the range. It is dynamically invoked by the
258 -- multithreading child process. It manages the multithreading ranges.
259 -- ----------------------------------------------------------------------------
260 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
261 ,retcode OUT NOCOPY NUMBER
262 ,p_mthd_action_id IN NUMBER
263 ,p_mthd_range_id IN NUMBER
264 ,p_start_object_id IN NUMBER
265 ,p_end_object_id IN NUMBER) IS
266
267 BEGIN
268
269 -- Set the parameters
270 set_parameters(p_mthd_action_id);
271
272 -- Process range in corresponding refresh mode
273 IF g_full_refresh = 'Y' THEN
274 process_range_full
275 (p_start_asg_id => p_start_object_id,
276 p_end_asg_id => p_end_object_id);
277 ELSE
278 process_range_incr
279 (p_start_asg_id => p_start_object_id,
280 p_end_asg_id => p_end_object_id);
281 END IF;
282
283 END process_range;
284
285 -- Populates the supervisor events helper table in shared hr mode
286 PROCEDURE single_thread_shared_hrms IS
287
288 BEGIN
289
290 -- Enable native parallelism
291 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
292 EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
293
294 -- Single thread insert
295 INSERT /*+ APPEND */ INTO hri_cs_asgn_suph_events_ct
296 (assignment_id
297 ,effective_start_date
298 ,effective_end_date
299 ,person_id
300 ,supervisor_person_id
301 ,supervisor_assignment_id
302 ,business_group_id
303 ,assignment_type
304 ,primary_flag
305 ,assignment_status_type_id
306 ,last_update_date
307 ,last_updated_by
308 ,last_update_login
309 ,created_by
310 ,creation_date)
311 SELECT
312 asg.assignment_id
313 ,GREATEST(NVL(pos.date_start, ppp.date_start),
314 TRUNC(SYSDATE))
315 effective_start_date
316 ,NVL(pos.actual_termination_date,
317 NVL(ppp.actual_termination_date, g_end_of_time))
318 effective_end_date
319 ,asg.person_id
320 ,NVL(asg.supervisor_id, -1) supervisor_person_id
321 ,to_number(null) supervisor_assignment_id
322 ,asg.business_group_id
323 ,asg.assignment_type
324 ,asg.primary_flag
325 ,asg.assignment_status_type_id
326 ,g_sysdate
327 ,g_user
328 ,g_user
329 ,g_user
330 ,g_sysdate
331 FROM
332 per_all_assignments_f asg
333 ,per_assignment_status_types ast
334 ,per_periods_of_service pos
335 ,per_periods_of_placement ppp
336 WHERE asg.assignment_type IN ('E', 'C')
337 AND asg.primary_flag = 'Y'
338 AND asg.effective_end_date >= g_refresh_start_date
339 AND ast.assignment_status_type_id = asg.assignment_status_type_id
340 AND ast.per_system_status <> 'TERM_ASSIGN'
341 AND trunc(SYSDATE) BETWEEN asg.effective_start_date
342 AND asg.effective_end_date
343 AND asg.period_of_service_id = pos.period_of_service_id (+)
344 AND asg.person_id = ppp.person_id (+)
345 AND asg.period_of_placement_date_start = ppp.date_start (+);
346
347 COMMIT;
348
349 END single_thread_shared_hrms;
350
351 -- ----------------------------------------------------------------------------
352 -- Pre process entry point
353 -- ----------------------------------------------------------------------------
354 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
355 p_sqlstr OUT NOCOPY VARCHAR2) IS
356
357 l_sql_stmt VARCHAR2(2000);
358 l_dummy1 VARCHAR2(2000);
359 l_dummy2 VARCHAR2(2000);
360 l_schema VARCHAR2(400);
361
362 BEGIN
363
364 -- Set parameter globals
365 set_parameters
366 (p_mthd_action_id => p_mthd_action_id);
367
368 -- Get HRI schema name - get_app_info populates l_schema
369 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
370 null;
371 END IF;
372
373 -- Disable WHO trigger
374 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ASGN_SUPH_EVENTS_CT_WHO DISABLE');
375
376 -- ********************
377 -- Full Refresh Section
378 -- ********************
379 IF (g_full_refresh = 'Y' OR
380 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
381
382 -- Empty out supervisor hierarchy events helper table
383 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_ASGN_SUPH_EVENTS_CT';
384 EXECUTE IMMEDIATE(l_sql_stmt);
385
386 -- In shared HR mode populate the table in a single direct insert
387 -- Do not return a SQL statement so that the process_range and
388 -- post_process will not be executed
389 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
390
391 -- Call API to insert rows
392 single_thread_shared_hrms;
393
394 -- Call post processing API
395 post_process
396 (p_mthd_action_id => p_mthd_action_id);
397
398 ELSE
399
400 -- Set the SQL statement for the entire range
401 p_sqlstr :=
402 'SELECT /*+ PARALLEL(asg, DEFAULT, DEFAULT) */
403 DISTINCT assignment_id object_id
404 FROM per_all_assignments_f asg
405 WHERE assignment_type IN (''E'', ''C'')
406 AND primary_flag = ''Y''
407 AND effective_end_date >= to_date(''' ||
408 to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
409 ''',''DD-MM-YYYY'')
410 ORDER BY assignment_id';
411
412 END IF;
413
414 ELSE
415
416 -- Delete rows to be updated incrementally
417 DELETE FROM hri_cs_asgn_suph_events_ct ase
418 WHERE ase.rowid IN
419 (SELECT ase2.rowid
420 FROM
421 hri_cs_asgn_suph_events_ct ase2
422 ,hri_eq_sprvsr_hrchy_chgs eq
423 WHERE eq.assignment_id = ase2.assignment_id
424 AND ase2.effective_start_date >= eq.erlst_evnt_effective_date);
425
426 -- commit
427 COMMIT;
428
429 -- End date rows to be updated incrementally
430 UPDATE hri_cs_asgn_suph_events_ct ase
431 SET effective_end_date =
432 (SELECT (evt.erlst_evnt_effective_date - 1)
433 FROM hri_eq_sprvsr_hrchy_chgs evt
434 WHERE evt.assignment_id = ase.assignment_id)
435 WHERE ase.rowid IN
436 (SELECT ase2.rowid
437 FROM
438 hri_cs_asgn_suph_events_ct ase2
439 ,hri_eq_sprvsr_hrchy_chgs eq
440 WHERE eq.assignment_id = ase2.assignment_id
441 AND ase2.effective_end_date >= eq.erlst_evnt_effective_date);
442
443 -- commit
444 COMMIT;
445
446 -- Set the SQL statement for the incremental range
447 p_sqlstr :=
448 'SELECT /*+ PARALLEL(eq, DEFAULT, DEFAULT) */
449 assignment_id object_id
450 FROM hri_eq_sprvsr_hrchy_chgs eq
451 ORDER BY assignment_id';
452
453 END IF;
454
455 -- Enable WHO trigger
456 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ASGN_SUPH_EVENTS_CT_WHO ENABLE');
457
458 END pre_process;
459
460 -- ----------------------------------------------------------------------------
461 -- Post process entry point
462 -- ----------------------------------------------------------------------------
463 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
464
465 l_dummy1 VARCHAR2(2000);
466 l_dummy2 VARCHAR2(2000);
467 l_schema VARCHAR2(400);
468
469 BEGIN
470
471 -- Check parameters are set
472 set_parameters(p_mthd_action_id);
473
474 -- Get HRI schema name - get_app_info populates l_schema
475 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
476 null;
477 END IF;
478
479 -- Bug 4632040 - gather stats
480 fnd_stats.gather_table_stats(l_schema, 'HRI_CS_ASGN_SUPH_EVENTS_CT');
481
482 -- Log the process unless called from test harness
483 IF (p_mthd_action_id > -1) THEN
484
485 -- Log process end
486 hri_bpl_conc_log.record_process_start('HRI_CS_ASGN_SUPH_EVENTS_CT');
487 hri_bpl_conc_log.log_process_end(
488 p_status => TRUE
489 ,p_period_from => TRUNC(g_refresh_start_date)
490 ,p_period_to => TRUNC(SYSDATE)
491 ,p_attribute1 => g_full_refresh);
492
493 END IF;
494
495 END post_process;
496
497 -- Populates table in a single thread
498 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
499
500 l_end_asg_id NUMBER;
501 l_dummy VARCHAR2(32000);
502 l_from_date DATE := hri_bpl_parameter.get_bis_global_start_date;
503
504 BEGIN
505
506 -- get max assignment id
507 SELECT max(assignment_id) INTO l_end_asg_id
508 FROM per_all_assignments_f;
509
510 -- Set globals
511 g_refresh_start_date := l_from_date;
512 g_full_refresh := p_full_refresh_flag;
513
514 -- Pre process
515 pre_process(-1, l_dummy);
516
517 -- Process range
518 IF (p_full_refresh_flag = 'Y') THEN
519 process_range_full(0, l_end_asg_id);
520 ELSE
521 process_range_incr(0, l_end_asg_id);
522 END IF;
523
524 -- Post process
525 post_process(-1);
526
527 END single_thread_process;
528
529 END hri_opl_suph_events;
[Home] [Help]