[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_CMNTS_ACTLS_ORGMGR
Source
1 PACKAGE BODY HRI_OPL_CMNTS_ACTLS_ORGMGR AS
2 /* $Header: hriocaom.pkb 120.3 2005/08/11 06:55:11 ddutta 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 -- ----------------------------------------------------------------------------
38 -- Inserts row into concurrent program log
39 --
40 --
41 PROCEDURE output(p_text VARCHAR2) IS
42 BEGIN
43 --
44 IF (g_concurrent_flag = 'Y') THEN
45 --
46 -- Write to the concurrent request log
47 --
48 fnd_file.put_line(fnd_file.log, p_text);
49 --
50 ELSE
51 --
52 hr_utility.trace(p_text);
53 --
54 END IF;
55 --
56 END output;
57 --
58 -- -----------------------------------------------------------------------------
59 -- Inserts row into concurrent program log if debugging is enabled
60 -- -----------------------------------------------------------------------------
61 --
62 PROCEDURE dbg(p_text VARCHAR2) IS
63 --
64 BEGIN
65 --
66 IF (g_debug_flag = 'Y') THEN
67 --
68 -- Write to output
69 --
70 output(p_text);
71 --
72 END IF;
73 --
74 END dbg;
75 --
76 -- ----------------------------------------------------------------------------
77 -- Runs given sql statement dynamically without raising an exception
78 -- ----------------------------------------------------------------------------
79 --
80 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
81 IS
82 --
83 BEGIN
84 --
85 EXECUTE IMMEDIATE p_sql_stmt;
86 --
87 EXCEPTION WHEN OTHERS THEN
88 --
89 output('Could not run the following sql:');
90 output(SUBSTR(p_sql_stmt,1,230));
91 --
92 END run_sql_stmt_noerr;
93 --
94 -- ----------------------------------------------------------------------------
95 -- SET_PARAMETERS
96 -- sets up parameters required for the process.
97 -- ----------------------------------------------------------------------------
98 --
99 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER
100 ,p_mthd_range_id IN NUMBER DEFAULT NULL) IS
101 --
102 BEGIN
103 --
104 -- If parameters haven't already been set, then set them
105 --
106 IF p_mthd_action_id IS NULL THEN
107 --
108 -- Called from test harness
109 --
110 g_refresh_start_date := bis_common_parameters.get_global_start_date;
111 g_refresh_end_date := hr_general.end_of_time;
112 g_full_refresh := 'Y';
113 g_concurrent_flag := 'Y';
114 g_debug_flag := 'Y';
115 --
116 ELSIF (g_refresh_start_date IS NULL) THEN
117 --
118 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
119 g_refresh_start_date := g_mthd_action_array.collect_from_date;
120 g_refresh_end_date := hr_general.end_of_time;
121 g_full_refresh := g_mthd_action_array.full_refresh_flag;
122 g_concurrent_flag := 'Y';
123 g_debug_flag := g_mthd_action_array.debug_flag;
124 --
125 --
126 END IF;
127 --
128 END set_parameters;
129 --
130 -- ----------------------------------------------------------------------------
131 -- PROCESS
132 -- Processes actions and inserts data into summary table
133 -- This procedure is executed for every person in a chunk
134 -- ----------------------------------------------------------------------------
135 --
136 PROCEDURE process(p_person_id IN NUMBER)
137 IS
138 --
139 -- Variables to populate WHO Columns
140 --
141 l_current_time DATE;
142 l_user_id NUMBER;
143 --
144 BEGIN
145 --
146 dbg('Inside process');
147 --
148 l_current_time := SYSDATE;
149 l_user_id := fnd_global.user_id;
150 --
151 INSERT INTO HRI_MDP_CMNTS_ACTLS_ORGMGR_CT (
152 orgmgr_id
153 ,effective_start_date
154 ,effective_end_date
155 ,assignment_id
156 ,organization_id
157 ,job_id
158 ,position_id
159 ,grade_id
160 ,element_type_id
161 ,input_value_id
162 ,cost_allocation_keyflex_id
163 ,commitment_value
164 ,dr_commitment_value
165 ,actual_value
166 ,dr_actual_value
167 ,currency_code
168 --
169 -- WHO Columns
170 --
171 ,last_update_date
172 ,last_update_login
173 ,last_updated_by
174 ,created_by
175 ,creation_date)
176 SELECT orgmgr.sup_person_id ORGMGR_ID
177 ,GREATEST(cmntactl.effective_start_date,orgmgr.effective_start_date) EFFECTIVE_START_DATE
178 ,LEAST(cmntactl.effective_end_date,orgmgr.effective_end_date) EFFECTIVE_END_DATE
179 ,cmntactl.assignment_id ASSIGNMENT_ID
180 ,cmntactl.organization_id ORGANIZATION_ID
181 ,cmntactl.job_id JOB_ID
182 ,cmntactl.position_id POSITION_ID
183 ,cmntactl.grade_id GRADE_ID
184 ,cmntactl.element_type_id ELEMENT_TYPE_ID
185 ,cmntactl.input_value_id INPUT_VALUE_ID
186 ,cmntactl.cost_allocation_keyflex_id COST_ALLOCATION_KEYFLEX_ID
187 ,CASE
188 WHEN NVL(cmntactl.commitment_value,0) > NVL(cmntactl.actual_value,0)
189 THEN (cmntactl.commitment_value - NVL(cmntactl.actual_value,0))
190 ELSE 0
191 END COMMITMENT_VALUE
192 ,CASE
193 WHEN NVL(cmntactl.commitment_value,0) > NVL(cmntactl.actual_value,0)
194 THEN (cmntactl.commitment_value - NVL(cmntactl.actual_value,0)) * DECODE(orgmgr.sub_relative_level, 0, 1, 0)
195 ELSE 0
196 END DR_COMMITMENT_VALUE
197 ,cmntactl.actual_value ACTUAL_VALUE
198 ,cmntactl.actual_value * DECODE(orgmgr.sub_relative_level, 0, 1, 0) DR_ACTUAL_VALUE
199 ,cmntactl.currency_code CURRENCY_CODE
200 ,SYSDATE
201 ,l_user_id
202 ,l_user_id
203 ,l_user_id
204 ,SYSDATE
205 FROM hri_md_cmnts_actls_ct cmntactl
206 ,hri_cs_suph_orgmgr_ct orgmgr
207 WHERE cmntactl.organization_id = orgmgr.sub_organization_id
208 AND orgmgr.sup_person_id = p_person_id;
209 --
210 dbg(SQL%ROWCOUNT||' records inserted into HRI_MDP_CMNTS_ACTLS_ORGMGR_CT');
211 dbg('Exiting process');
212 COMMIT;
213 --
214 EXCEPTION
215 WHEN OTHERS THEN
216 --
217 output(sqlerrm);
218 --
219 -- RAISE;
220 --
221 --
222 END process;
223 -- ----------------------------------------------------------------------------
224 -- PROCESS_RANGE
225 -- Processes actions and inserts data into summary table
226 -- This procedure is executed for every person in a chunk
227 -- ----------------------------------------------------------------------------
228 --
229 PROCEDURE process_range(p_start_object_id IN NUMBER
230 ,p_end_object_id IN NUMBER )
231 IS
232 --
233 -- Declare the ref cursor
234 --
235 type person_to_process is ref cursor;
236 --
237 c_person_to_process PERSON_TO_PROCESS;
238 --
239 -- Holds assignment from the cursor
240 --
241 l_person_id NUMBER;
242 l_change_date DATE;
243 l_error_step NUMBER;
244 --
245 --
246 -- Variables to populate WHO Columns
247 --
248 l_current_time DATE;
249 l_user_id NUMBER;
250 --
251 BEGIN
252 --
253 dbg('Inside process_range');
254 dbg('range ='||p_start_object_id||' - '||p_end_object_id);
255 --
256 IF (g_full_refresh = 'Y') THEN
257 --
258 OPEN c_person_to_process FOR
259 SELECT DISTINCT sup_person_id
260 FROM hri_cs_suph_orgmgr_ct
261 WHERE sup_person_id BETWEEN p_start_object_id and p_end_object_id;
262 --
263 END IF;
264 --
265 -- Collect the assignment event details for every supervisor person in the
266 -- multithreading range.
267 --
268 LOOP
269 --
270 FETCH c_person_to_process INTO l_person_id;
271 EXIT WHEN c_person_to_process%NOTFOUND;
272 --
273 dbg('person = '||l_person_id);
274 --
275 BEGIN
276 --
277 -- Call the collect procedure which collects the assignments events
278 -- records for the assignment
279 --
280 process(p_person_id => l_person_id);
281 END;
282 --
283 END LOOP;
284 --
285 dbg('Done processing all persons in the range.');
286 --
287 -- Commit the data now
288 COMMIT;
289 --
290 IF c_person_to_process%ISOPEN THEN
291 --
292 CLOSE c_person_to_process;
293 --
294 END IF;
295 --
296 EXCEPTION
297 WHEN OTHERS THEN
298 --
299 output(sqlerrm);
300 --
301 IF c_person_to_process%ISOPEN THEN
302 --
303 CLOSE c_person_to_process;
304 --
305 END IF;
306 --
307 END process_range;
308 --
309 -- ----------------------------------------------------------------------------
310 -- PRE_PROCESS
311 -- This procedure includes all the logic required for performing the pre_process
312 -- task of HRI multithreading utility. It drops the indexes and return the SQL
313 -- required for generating the ranges
314 -- ----------------------------------------------------------------------------
315 --
316 PROCEDURE PRE_PROCESS(
317 --
318 p_mthd_action_id IN NUMBER,
319 p_sqlstr OUT NOCOPY VARCHAR2) IS
320 --
321 l_dummy1 VARCHAR2(2000);
322 l_dummy2 VARCHAR2(2000);
323 l_schema VARCHAR2(400);
324 --
325 BEGIN
326 --
327 -- Record the process start
328 --
329 --
330 -- Set up the parameters
331 --
332 set_parameters( p_mthd_action_id => p_mthd_action_id );
333 --
334 -- Disable the WHO trigger
335 --
336 -- run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_CMNTS_ACTLS_ORGMGR_CT_WHO DISABLE');
337 --
338 -- ---------------------------------------------------------------------------
339 -- Full Refresh Section
340 -- ---------------------------------------------------------------------------
341 --
342 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
343 --
344 -- If it's a full refresh
345 --
346 IF (g_full_refresh = 'Y') THEN
347 --
348 -- Drop Indexes
349 --
350 hri_utl_ddl.log_and_drop_indexes(
351 p_application_short_name => 'HRI',
352 p_table_name => 'HRI_MDP_CMNTS_ACTLS_ORGMGR_CT',
353 p_table_owner => l_schema);
354 --
355 -- Truncate the table
356 --
357 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MDP_CMNTS_ACTLS_ORGMGR_CT';
358 --
359 -- Select all organization managers in the collection range.
360 --
361 p_sqlstr :=
362 'SELECT /*+ parallel (ORGMGR, default, default) */
363 DISTINCT sup_person_id object_id
364 FROM hri_cs_suph_orgmgr_ct orgmgr
365 ORDER BY sup_person_id';
366 --
367 -- End of Full Refresh Section
368 -- -------------------------------------------------------------------------
369 --
370 -- -------------------------------------------------------------------------
371 -- Start of Incremental Refresh Section
372 --
373 ELSE
374 --
375 -- Incremental Refresh will be supported later.
376 --
377 NULL;
378 --
379 --
380 -- End of Incremental Refresh Section
381 -- -------------------------------------------------------------------------
382 --
383 END IF;
384 --
385 END IF;
386 --
387 --
388 END PRE_PROCESS;
389 --
390 -- ----------------------------------------------------------------------------
391 -- PROCESS_RANGE
392 -- This procedure is dynamically the HRI multithreading utility child threads
393 -- for processing the assignment ranges. The procedure invokes the overloaded
394 -- process_range procedure to process the range.
395 -- ----------------------------------------------------------------------------
396 --
397 PROCEDURE process_range(
398 errbuf OUT NOCOPY VARCHAR2
399 ,retcode OUT NOCOPY NUMBER
400 ,p_mthd_action_id IN NUMBER
401 ,p_mthd_range_id IN NUMBER
402 ,p_start_object_id IN NUMBER
403 ,p_end_object_id IN NUMBER)
404 IS
405 --
406 l_error_step NUMBER;
407 --
408 BEGIN
409 --
410 -- Initialize the global variables
411 --
412 set_parameters(p_mthd_action_id => p_mthd_action_id
413 ,p_mthd_range_id => p_mthd_range_id);
414 --
415 dbg('calling process_range for object range from '||p_start_object_id || ' to '|| p_end_object_id);
416 --
417 -- Depending on the refresh type call the corresponding refresh program
418 --
419 IF g_full_refresh = 'Y' THEN
420 --
421 process_range(p_start_object_id => p_start_object_id
422 ,p_end_object_id => p_end_object_id);
423 --
424 ELSE
425 --
426 -- Incremental Refresh will be supported later.
427 --
428 NULL;
429 --
430 END IF;
431 --
432 errbuf := 'SUCCESS';
433 retcode := 0;
434 EXCEPTION
435 WHEN others THEN
436 output('Error encountered while processing range ='||p_mthd_range_id );
437 output(sqlerrm);
438 errbuf := SQLERRM;
439 retcode := SQLCODE;
440 --
441 RAISE;
442 --
443 END process_range;
444 --
445 -- ----------------------------------------------------------------------------
446 -- POST_PROCESS
447 -- This procedure is dynamically invoked by the HRI Multithreading utility.
448 -- It finishes the processing by updating the BIS_REFRESH_LOG table
449 -- ----------------------------------------------------------------------------
450 --
451 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
452 --
453 l_dummy1 VARCHAR2(2000);
454 l_dummy2 VARCHAR2(2000);
455 l_schema VARCHAR2(400);
456 --
457 --
458 BEGIN
459 --
460 dbg('Inside post_process');
461 --
462 set_parameters(p_mthd_action_id);
463 --
464 hri_bpl_conc_log.record_process_start('HRI_OPL_CMNTS_ACTLS_ORGMGR');
465 --
466 -- Collect stats for full refresh
467 --
468 IF (g_full_refresh = 'Y') THEN
469 --
470 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
471 --
472 -- Create indexes
473 --
474 dbg('Full Refresh selected - Creating indexes');
475 --
476 hri_utl_ddl.recreate_indexes(
477 p_application_short_name => 'HRI',
478 p_table_name => 'HRI_MDP_CMNTS_ACTLS_ORGMGR_CT',
479 p_table_owner => l_schema);
480 --
481 -- Collect the statistics only when the process is NOT invoked by a concurrent manager
482 --
483 IF fnd_global.conc_request_id is null THEN
484 --
485 dbg('Full Refresh selected - gathering stats');
486 fnd_stats.gather_table_stats(l_schema,'HRI_MDP_CMNTS_ACTLS_ORGMGR_CT');
487 --
488 END IF;
489 --
490 END IF;
491 --
492 ELSE
493 --
494 -- Incremental Refresh will be supported later.
495 --
496 NULL;
497 --
498 END IF;
499 --
500 -- Enable the WHO trigger on the fact table
501 --
502 dbg('Enabling the who trigger');
503 -- run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_CMNTS_ACTLS_ORGMGR_CT_WHO ENABLE');
504 --
505 hri_bpl_conc_log.log_process_end(
506 p_status => TRUE
507 ,p_period_from => TRUNC(g_refresh_start_date)
508 ,p_period_to => TRUNC(SYSDATE)
509 ,p_attribute1 => g_full_refresh);
510 --
511 dbg('Exiting post_process');
512 --
513 END post_process;
514 -- ----------------------------------------------------------------------------
515 -- LOAD_TABLE
516 -- This procedure can be called from the Test harness to populate the table.
517 -- ----------------------------------------------------------------------------
518 --
519 PROCEDURE load_table
520 IS
521 --
522 --
523 l_sqlstr VARCHAR2(4000);
524 --
525 CURSOR c_range_cursor IS
526 SELECT mthd_range_id,
527 min(object_id) start_object_id,
528 max(object_id) end_object_id
529 FROM (SELECT hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
530 ,object_id
531 FROM ( SELECT DISTINCT sup_person_id object_id
532 FROM hri_cs_suph_orgmgr_ct
533 ORDER BY sup_person_id)
534 )
535 GROUP BY mthd_range_id;
536 --
537 BEGIN
538 --
539 dbg('Inside load_table');
540 --
541 -- Call Pre Process
542 --
543 pre_process(p_mthd_action_id => null,
544 p_sqlstr => l_sqlstr);
545 --
546 -- Call Process Range
547 --
548 FOR l_range IN c_range_cursor LOOP
549 --
550 dbg('range ='||l_range.start_object_id|| ' - '||l_range.end_object_id );
551 process_range(p_start_object_id => l_range.start_object_id
552 ,p_end_object_id => l_range.end_object_id);
553 --
554 COMMIT;
555 --
556 END LOOP;
557 --
558 -- Call Post Process
559 --
560 post_process (p_mthd_action_id => null);
561 --
562 dbg('Exiting load_table');
563 --
564 EXCEPTION
565 --
566 WHEN OTHERS THEN
567 --
568 output('Error in load_table = ');
569 output(SQLERRM);
570 RAISE;
571 --
572 END load_table;
573 --
574 END HRI_OPL_CMNTS_ACTLS_ORGMGR;