[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_WRKFC_ORGMGR
Source
1 PACKAGE BODY HRI_OPL_WRKFC_ORGMGR AS
2 /* $Header: hriowfom.pkb 120.0 2005/06/24 07:33:46 appldev noship $ */
3 -- -----------------------------------------------------------------------------
4 -- Multithreading Calls --
5 -- -----------------------------------------------------------------------------
6 -- This package uses the hri multithreading utility for processing.
7 -- The Multithreading Utility Provides the Framework for processing collection
8 -- using multiple threads. The sequence of operation performed by the utility are
9 -- a) Invoke the PRE_PROCESS procedure to initialize the global variables and
10 -- return a SQL based on which the processing ranges will be created.
11 -- b) Invoke the PROCESS_RANGE procedure to process the assignments in the range
12 -- This part is done by multiple threads. The utility passes the range_id along
13 -- with the starting and ending object_id for the range. This range is to be
14 -- by the procedure
15 -- c) Invoke the POST_PROCESS procedure to perform the post processing tasks
16 -- -----------------------------------------------------------------------------
17 --
18 --
19 -- Global Multi Threading Array
20 --
21 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%ROWTYPE;
22 --
23 -- Global variables representing parameters
24 --
25 g_refresh_start_date DATE;
26 g_refresh_end_date DATE;
27 g_full_refresh VARCHAR2(5);
28 --
29 -- Global flag which determines whether debugging is turned on
30 --
31 g_debug_flag VARCHAR2(5);
32 --
33 -- Whether called from a concurrent program
34 --
35 g_concurrent_flag VARCHAR2(5);
36 -- ----------------------------------------------------------------------------
37 -- Inserts row into concurrent program log
38 --
39 --
40 PROCEDURE output(p_text VARCHAR2) IS
41 BEGIN
42 --
43 IF (g_concurrent_flag = 'Y') THEN
44 --
45 -- Write to the concurrent request log
46 --
47 fnd_file.put_line(fnd_file.log, p_text);
48 --
49 ELSE
50 --
51 hr_utility.trace(p_text);
52 --
53 END IF;
54 --
55 END output;
56 --
57 -- -----------------------------------------------------------------------------
58 -- Inserts row into concurrent program log if debugging is enabled
59 -- -----------------------------------------------------------------------------
60 --
61 PROCEDURE dbg(p_text VARCHAR2) IS
62 --
63 BEGIN
64 --
65 IF (g_debug_flag = 'Y' OR g_mthd_action_array.debug_flag = 'Y') THEN
66 --
67 -- Write to output
68 --
69 output(p_text);
70 --
71 END IF;
72 --
73 END dbg;
74 --
75 -- ----------------------------------------------------------------------------
76 -- Runs given sql statement dynamically without raising an exception
77 -- ----------------------------------------------------------------------------
78 --
79 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
80 IS
81 --
82 BEGIN
83 --
84 EXECUTE IMMEDIATE p_sql_stmt;
85 --
86 EXCEPTION WHEN OTHERS THEN
87 --
88 output('Could not run the following sql:');
89 output(SUBSTR(p_sql_stmt,1,230));
90 --
91 END run_sql_stmt_noerr;
92 --
93 -- ----------------------------------------------------------------------------
94 -- SET_PARAMETERS
95 -- sets up parameters required for the process.
96 -- ----------------------------------------------------------------------------
97 --
98 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER
99 ,p_mthd_range_id IN NUMBER DEFAULT NULL) IS
100 --
101 BEGIN
102 --
103 -- If parameters haven't already been set, then set them
104 --
105 IF p_mthd_action_id IS NULL THEN
106 --
107 -- Called from test harness
108 --
109 g_refresh_start_date := bis_common_parameters.get_global_start_date;
110 g_refresh_end_date := hr_general.end_of_time;
111 g_full_refresh := 'Y';
112 g_concurrent_flag := 'Y';
113 g_debug_flag := 'Y';
114 --
115 ELSIF (g_refresh_start_date IS NULL) THEN
116 --
117 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
118 g_refresh_start_date := g_mthd_action_array.collect_from_date;
119 g_refresh_end_date := hr_general.end_of_time;
120 g_full_refresh := g_mthd_action_array.full_refresh_flag;
121 g_concurrent_flag := 'Y';
122 g_debug_flag := g_mthd_action_array.debug_flag;
123 --
124 --
125 END IF;
126 --
127 END set_parameters;
128 --
129 PROCEDURE post_headcount_actuals(p_person_id IN NUMBER )
130 IS
131 --
132 -- Variables to populate WHO Columns
133 --
134 l_current_time DATE;
135 l_user_id NUMBER;
136 --
137 BEGIN
138 --
139 dbg('Inside process_headcount_actuals');
140 --
141 l_current_time := SYSDATE;
142 l_user_id := fnd_global.user_id;
143 --
144 INSERT INTO HRI_MAP_WRKFC_ORGMGR_CT (
145 --
146 -- Organization Manager Id
147 --
148 orgmgr_id
149 --
150 -- Effective dates
151 --
152 ,effective_start_date
153 ,effective_end_date
154 --
155 -- Dimensions
156 --
157 ,organization_id
158 ,job_id
159 ,position_id
160 ,grade_id
161 --
162 -- Net changes on effective date for all subordinates
163 --
164 ,total_headcount
165 --
166 -- Net changes on effective date for direct reports only
167 --
168 ,dr_headcount
169 --
170 -- WHO Columns
171 --
172 ,last_update_date
173 ,last_update_login
174 ,last_updated_by
175 ,created_by
176 ,creation_date)
177 SELECT
178 dlt.supervisor_person_id supervisor_person_id
179 ,dlt.effective_start_date effective_start_date
180 ,nvl(dlt.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')) effective_end_date
181 ,dlt.organization_id organization_id
182 ,dlt.job_id job_id
183 ,dlt.position_id position_id
184 ,dlt.grade_id grade_id
185 ,SUM(dlt.headcount_value * dlt.metric_adjust_multiplier)
186 headcount_adjust
187 ,SUM(dlt.headcount_value * dlt.metric_adjust_multiplier * dlt.direct_ind)
188 dr_headcount_adjust
189 --
190 -- WHO Columns
191 --
192 ,SYSDATE
193 ,l_user_id
194 ,l_user_id
195 ,l_user_id
196 ,SYSDATE
197 FROM (SELECT
198 orgmgr.sup_person_id supervisor_person_id
199 ,evts.supervisor_id direct_supervisor_person_id
200 ,GREATEST(evts.effective_change_date,
201 orgmgr.effective_start_date) effective_start_date
202 ,LEAST(evts.effective_change_end_date,
203 orgmgr.effective_end_date ) effective_end_date
204 ,evts.person_id person_id
205 ,evts.assignment_id assignment_id
206 ,evts.job_id job_id
207 ,evts.organization_id organization_id
208 ,evts.position_id position_id
209 ,evts.grade_id grade_id
210 ,evts.headcount headcount_value
211 ,evts.fte fte_value
212 ,DECODE(orgmgr.sub_relative_level, 0, 1, 0) direct_ind
213 ,1 metric_adjust_multiplier
214 ,orgmgr.sup_level supervisor_level
215 ,sub_assignment_id sub_assignment_id
216 FROM
217 hri_mb_asgn_events_ct evts
218 ,hri_cs_suph_orgmgr_ct orgmgr
219 WHERE orgmgr.sub_person_id = evts.supervisor_id
220 AND (evts.effective_change_date BETWEEN orgmgr.effective_start_date AND orgmgr.effective_end_date
221 OR orgmgr.effective_start_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date)
222 AND evts.pre_sprtn_asgn_end_ind = 0
223 AND evts.worker_term_ind = 0
224 AND orgmgr.sup_person_id = p_person_id
225 UNION ALL
226 SELECT
227 orgmgr.sup_person_id supervisor_person_id
228 ,evts.supervisor_id direct_supervisor_person_id
229 ,LEAST(evts.effective_change_end_date, orgmgr.effective_end_date) + 1
230 effective_start_date
231 ,null effective_end_date
232 ,evts.person_id person_id
233 ,evts.assignment_id assignment_id
234 ,evts.job_id job_id
235 ,evts.organization_id organization_id
236 ,evts.position_id position_id
237 ,evts.grade_id grade_id
238 ,evts.headcount headcount_value
239 ,evts.fte fte_value
240 ,DECODE(orgmgr.sub_relative_level, 0, 1, 0) direct_ind
241 ,-1 metric_adjust_multiplier
242 ,orgmgr.sup_level supervisor_level
243 ,sub_assignment_id sub_assignment_id
244 FROM
245 hri_mb_asgn_events_ct evts
246 ,hri_cs_suph_orgmgr_ct orgmgr
247 WHERE orgmgr.sub_person_id = evts.supervisor_id
248 AND (orgmgr.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
249 OR evts.effective_change_end_date BETWEEN orgmgr.effective_start_date AND orgmgr.effective_end_date)
250 AND LEAST(orgmgr.effective_end_date, evts.effective_change_end_date) < to_date('31-12-4712','DD-MM-YYYY')
251 AND evts.pre_sprtn_asgn_end_ind = 0
252 AND evts.worker_term_ind = 0
253 AND orgmgr.sup_person_id = p_person_id ) dlt
254 GROUP BY
255 dlt.supervisor_person_id
256 ,dlt.effective_start_date
257 ,dlt.effective_end_date
258 ,dlt.organization_id
259 ,dlt.job_id
260 ,dlt.position_id
261 ,dlt.grade_id;
262 --
263 dbg(SQL%ROWCOUNT||' records inserted into HRI_MAP_WRKFC_ORGMGR_CT');
264 dbg('Exiting post_headcount_actuals');
265 --
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 --
270 output(sqlerrm);
271 --
272 END post_headcount_actuals;
273 --
274 -- ----------------------------------------------------------------------------
275 -- PRE_PROCESS
276 -- Processes actions and inserts data into summary table
277 -- This procedure is executed for every person in a chunk
278 -- ----------------------------------------------------------------------------
279 --
280 PROCEDURE process_range(p_start_object_id IN NUMBER
281 ,p_end_object_id IN NUMBER )
282 IS
283 --
284 -- Declare the ref cursor
285 --
286 type person_to_process is ref cursor;
287 --
288 c_person_to_process PERSON_TO_PROCESS;
289 --
290 -- Holds assignment from the cursor
291 --
292 l_person_id NUMBER;
293 l_change_date DATE;
294 l_error_step NUMBER;
295 --
296 --
297 -- Variables to populate WHO Columns
298 --
299 l_current_time DATE;
300 l_user_id NUMBER;
301 --
302 BEGIN
303 --
304 dbg('Inside process_range');
305 dbg('range ='||p_start_object_id||' - '||p_end_object_id);
306 --
307 IF (g_full_refresh = 'Y') THEN
308 --
309 OPEN c_person_to_process FOR
310 SELECT DISTINCT sup_person_id
311 FROM hri_cs_suph_orgmgr_ct
312 WHERE sup_person_id BETWEEN p_start_object_id and p_end_object_id;
313 --
314 END IF;
315 --
316 -- Collect the assignment event details for every supervisor person in the
317 -- multithreading range.
318 --
319 LOOP
320 --
321 FETCH c_person_to_process INTO l_person_id;
322 EXIT WHEN c_person_to_process%NOTFOUND;
323 --
324 dbg('person = '||l_person_id);
325 --
326 BEGIN
327 --
328 -- Call the collect procedure which collects the assignments events
329 -- records for the assignment
330 --
331 post_headcount_actuals(p_person_id => l_person_id);
332 END;
333 --
334 END LOOP;
335 --
336 dbg('Done processing all persons in the range.');
337 --
338 -- Commit the data now
339 COMMIT;
340 --
341 IF c_person_to_process%ISOPEN THEN
342 --
343 CLOSE c_person_to_process;
344 --
345 END IF;
346
347
348 --
349 EXCEPTION
350 WHEN OTHERS THEN
351 --
352 output(sqlerrm);
353 --
354 IF c_person_to_process%ISOPEN THEN
355 --
356 CLOSE c_person_to_process;
357 --
358 END IF;
359 --
360 END process_range;
361 --
362 -- ----------------------------------------------------------------------------
363 -- PRE_PROCESS
364 -- This procedure includes all the logic required for performing the pre_process
365 -- task of HRI multithreading utility. It drops the indexes and return the SQL
366 -- required for generating the ranges
367 -- ----------------------------------------------------------------------------
368 --
369 PROCEDURE PRE_PROCESS(
370 --
371 p_mthd_action_id IN NUMBER,
372 p_sqlstr OUT NOCOPY VARCHAR2) IS
373 --
374 l_dummy1 VARCHAR2(2000);
375 l_dummy2 VARCHAR2(2000);
376 l_schema VARCHAR2(400);
377 --
378 BEGIN
379 --
380 -- Record the process start
381 --
382 --
383 -- Set up the parameters
384 --
385 set_parameters( p_mthd_action_id => p_mthd_action_id );
386 --
387 -- Disable the WHO trigger
388 --
389 run_sql_stmt_noerr('ALTER TRIGGER HRI_MAP_WRKFC_ORGMGR_CT_WHO DISABLE');
390 --
391 -- ---------------------------------------------------------------------------
392 -- Full Refresh Section
393 -- ---------------------------------------------------------------------------
394 --
395 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
396 --
397 -- If it's a full refresh
398 --
399 IF (g_full_refresh = 'Y') THEN
400 --
401 -- Drop Indexes
402 --
403 hri_utl_ddl.log_and_drop_indexes(
404 p_application_short_name => 'HRI',
405 p_table_name => 'HRI_MAP_WRKFC_ORGMGR_CT',
406 p_table_owner => l_schema);
407 --
408 -- Truncate the table
409 --
410 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MAP_WRKFC_ORGMGR_CT';
411 --
412 -- Select all people with employee assignments in the collection range.
413 -- The bind variable must be present for this sql to work when called
414 -- by PYUGEN, else itwill give error.
415 --
416 p_sqlstr :=
417 'SELECT /*+ parallel (ASG_EVT, default, default) */
418 DISTINCT person_id object_id
419 FROM hri_mb_asgn_events_ct asg_evt
420 ORDER BY person_id';
421 --
422 -- End of Full Refresh Section
423 -- -------------------------------------------------------------------------
424 --
425 -- -------------------------------------------------------------------------
426 -- Start of Incremental Refresh Section
427 --
428 ELSE
429 --
430 -- Incremental Refresh will be supported later.
431 --
432 NULL;
433 --
434 --
435 -- End of Incremental Refresh Section
436 -- -------------------------------------------------------------------------
437 --
438 END IF;
439 --
440 END IF;
441 --
442 --
443 END PRE_PROCESS;
444 --
445 -- ----------------------------------------------------------------------------
446 -- PROCESS_RANGE
447 -- This procedure is dynamically the HRI multithreading utility child threads
448 -- for processing the assignment ranges. The procedure invokes the overloaded
449 -- process_range procedure to process the range.
450 -- ----------------------------------------------------------------------------
451 --
452 PROCEDURE process_range(
453 errbuf OUT NOCOPY VARCHAR2
454 ,retcode OUT NOCOPY NUMBER
455 ,p_mthd_action_id IN NUMBER
456 ,p_mthd_range_id IN NUMBER
457 ,p_start_object_id IN NUMBER
458 ,p_end_object_id IN NUMBER)
459 IS
460 --
461 l_error_step NUMBER;
462 --
463 BEGIN
464 --
465 -- Initialize the global variables
466 --
467 set_parameters(p_mthd_action_id => p_mthd_action_id
468 ,p_mthd_range_id => p_mthd_range_id);
469 --
470 dbg('calling process_range for object range from '||p_start_object_id || ' to '|| p_end_object_id);
471 --
472 -- Depending on the refresh type call the corresponding refresh program
473 --
474 IF g_full_refresh = 'Y' THEN
475 --
476 process_range(p_start_object_id => p_start_object_id
477 ,p_end_object_id => p_end_object_id);
478 --
479 ELSE
480 --
481 -- Incremental Refresh will be supported later.
482 --
483 NULL;
484 --
485 END IF;
486 --
487 errbuf := 'SUCCESS';
488 retcode := 0;
489 EXCEPTION
490 WHEN others THEN
491 output('Error encountered while processing range ='||p_mthd_range_id );
492 output(sqlerrm);
493 errbuf := SQLERRM;
494 retcode := SQLCODE;
495 --
496 RAISE;
497 --
498 END process_range;
499 --
500 -- ----------------------------------------------------------------------------
501 -- POST_PROCESS
502 -- This procedure is dynamically invoked by the HRI Multithreading utility.
503 -- It finishes the processing by updating the BIS_REFRESH_LOG table
504 -- ----------------------------------------------------------------------------
505 --
506 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
507 --
508 l_dummy1 VARCHAR2(2000);
509 l_dummy2 VARCHAR2(2000);
510 l_schema VARCHAR2(400);
511 --
512 --
513 BEGIN
514 --
515 dbg('Inside post_process');
516 --
517 set_parameters(p_mthd_action_id);
518 --
519 hri_bpl_conc_log.record_process_start('HRI_OPL_WRKFC_ORGMGR');
520 --
521 -- Collect stats for full refresh
522 --
523 IF (g_full_refresh = 'Y') THEN
524 --
525 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
526 --
527 -- Create indexes
528 --
529 dbg('Full Refresh selected - Creating indexes');
530 --
531 hri_utl_ddl.recreate_indexes(
532 p_application_short_name => 'HRI',
533 p_table_name => 'HRI_MAP_WRKFC_ORGMGR_CT',
534 p_table_owner => l_schema);
535 --
536 -- Collect the statistics only when the process is NOT invoked by a concurrent manager
537 --
538 IF fnd_global.conc_request_id is null THEN
539 --
540 dbg('Full Refresh selected - gathering stats');
541 fnd_stats.gather_table_stats(l_schema,'HRI_MAP_WRKFC_ORGMGR_CT');
542 --
543 END IF;
544 --
545 END IF;
546 --
547 ELSE
548 --
549 -- Incremental Refresh will be supported later.
550 --
551 NULL;
552 --
553 END IF;
554 --
555 -- Enable the WHO trigger on the fact table
556 --
557 dbg('Enabling the who trigger');
558 run_sql_stmt_noerr('ALTER TRIGGER HRI_MAP_WRKFC_ORGMGR_CT_WHO ENABLE');
559 --
560 hri_bpl_conc_log.log_process_end(
561 p_status => TRUE
562 ,p_period_from => TRUNC(g_refresh_start_date)
563 ,p_period_to => TRUNC(SYSDATE)
564 ,p_attribute1 => g_full_refresh);
565 --
566 dbg('Exiting post_process');
567 --
568 END post_process;
569 --
570 -- ----------------------------------------------------------------------------
571 -- LOAD_TABLE
572 -- This procedure can be called from the Test harness to populate the table.
573 -- ----------------------------------------------------------------------------
574 --
575 PROCEDURE load_table
576 IS
577 --
578 l_sqlstr VARCHAR2(4000);
579 --
580 CURSOR c_range_cursor IS
581 SELECT mthd_range_id,
582 min(object_id) start_object_id,
583 max(object_id) end_object_id
584 FROM (SELECT hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
585 ,object_id
586 FROM ( SELECT DISTINCT sup_person_id object_id
587 FROM hri_cs_suph_orgmgr_ct
588 ORDER BY sup_person_id)
589 )
590 GROUP BY mthd_range_id;
591 --
592 BEGIN
593 --
594 dbg('Inside load_table');
595 --
596 -- Call Pre Process
597 --
598 pre_process(p_mthd_action_id => null,
599 p_sqlstr => l_sqlstr);
600 --
601 -- Call Process Range
602 --
603 FOR l_range IN c_range_cursor LOOP
604 --
605 dbg('range ='||l_range.start_object_id|| ' - '||l_range.end_object_id );
606 process_range(p_start_object_id => l_range.start_object_id
607 ,p_end_object_id => l_range.end_object_id);
608 --
609 COMMIT;
610 --
611 END LOOP;
612 --
613 -- Call Post Process
614 --
615 post_process (p_mthd_action_id => null);
616 --
617 dbg('Exiting load_table');
618 --
619 EXCEPTION
620 --
621 WHEN OTHERS THEN
622 --
623 output('Error in load_table = ');
624 output(SQLERRM);
625 RAISE;
626 --
627 END load_table;
628 --
629 END HRI_OPL_WRKFC_ORGMGR;