[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_ACTLS
Source
1 PACKAGE BODY HRI_OPL_ACTLS AS
2 /* $Header: hrioactl.pkb 120.3 2005/12/22 21:36:27 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 -- 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 --
130 -- ----------------------------------------------------------------------------
131 -- PRE_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_range(p_start_object_id IN NUMBER
137 ,p_end_object_id IN NUMBER )
138 IS
139 --
140 TYPE assignment_actions_tab IS TABLE OF pay_assignment_actions.assignment_action_id%TYPE;
141 l_assignment_actions assignment_actions_tab;
142 --
143 -- Variables to populate WHO Columns
144 --
145 l_current_time DATE;
146 l_user_id NUMBER;
147 --
148 BEGIN
149 --
150 dbg('Inside process_range');
151 dbg('range ='||p_start_object_id||' - '||p_end_object_id);
152 --
153 l_current_time := SYSDATE;
154 l_user_id := fnd_global.user_id;
155
156 IF (g_full_refresh = 'Y') THEN
157 --
158 SELECT assignment_action_id
159 BULK COLLECT INTO l_assignment_actions
160 FROM pay_assignment_actions paa,
161 pay_payroll_actions ppa
162 WHERE paa.payroll_action_id = ppa.payroll_action_id
163 AND ppa.action_type IN ('C', 'S')
164 AND ppa.effective_date BETWEEN g_refresh_start_date AND g_refresh_end_date
165 AND paa.assignment_id BETWEEN p_start_object_id and p_end_object_id;
166 --
167 END IF;
168 --
169 --
170 FORALL i IN l_assignment_actions.FIRST .. l_assignment_actions.LAST SAVE EXCEPTIONS
171 --
172 INSERT INTO HRI_MB_ACTLS_CT (
173 hri_mb_actls_ct_id
174 ,effective_date
175 ,date_earned
176 ,assignment_id
177 ,organization_id
178 ,job_id
179 ,position_id
180 ,grade_id
181 ,element_type_id
182 ,input_value_id
183 ,cost_allocation_keyflex_id
184 -- ,debit_or_credit
185 ,actual_value
186 ,currency_code
187 --
188 -- WHO Columns
189 --
190 ,last_update_date
191 ,last_update_login
192 ,last_updated_by
193 ,created_by
194 ,creation_date)
195 SELECT
196 hri_mb_actls_ct_s.nextval
197 ,ppa2.effective_date
198 ,ppa2.date_earned
199 ,paaf.assignment_id
200 ,paaf.organization_id
201 ,paaf.job_id
202 ,paaf.position_id
203 ,paaf.grade_id
204 ,pivf.element_type_id
205 ,pc.input_value_id
206 ,pc.cost_allocation_keyflex_id
207 -- ,pc.debit_or_credit
208 ,pc.costed_value
209 ,petf.output_currency_code
210 ,l_current_time
211 ,l_user_id
212 ,l_user_id
213 ,l_user_id
214 ,l_current_time
215 FROM per_all_assignments_f paaf,
216 pay_assignment_actions paa2,
217 pay_payroll_actions ppa2,
218 pay_run_results ppr,
219 pay_costs pc,
220 pay_input_values_f pivf,
221 pay_element_types_f petf
222 WHERE paaf.assignment_id = paa2.assignment_id
223 AND paa2.payroll_action_id = ppa2.payroll_action_id
224 AND paa2.assignment_action_id = ppr.assignment_action_id
225 AND ppr.run_result_id = pc.run_result_id
226 --AND nvl(pc.distributed_input_value_id, pc.input_value_id) = pivf.input_value_id
227 AND pc.input_value_id = pivf.input_value_id
228 AND pivf.element_type_id = petf.element_type_id
229 AND ppr.element_type_id = petf.element_type_id
230 AND pc.balance_or_cost = 'C'
231 AND pc.assignment_action_id = l_assignment_actions(i)
232 AND ppa2.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
233 AND ppa2.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
234 AND ppa2.effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
235 AND ppa2.effective_date BETWEEN g_refresh_start_date AND g_refresh_end_date;
236 --
237 dbg(SQL%ROWCOUNT||' records inserted into HRI_MB_ACTLS_CT');
238 dbg('Done processing all assignments in the range.');
239 --
240 -- Commit the data now
241 COMMIT;
242 --
243 --
244 EXCEPTION
245 WHEN OTHERS THEN
246 --
247 output(sqlerrm);
248 --
249 -- RAISE;
250 --
251 --
252 END process_range;
253 --
254 -- ----------------------------------------------------------------------------
255 -- PRE_PROCESS
256 -- This procedure includes all the logic required for performing the pre_process
257 -- task of HRI multithreading utility. It drops the indexes and return the SQL
258 -- required for generating the ranges
259 -- ----------------------------------------------------------------------------
260 --
261 PROCEDURE PRE_PROCESS(
262 --
263 p_mthd_action_id IN NUMBER,
264 p_sqlstr OUT NOCOPY VARCHAR2) IS
265 --
266 l_dummy1 VARCHAR2(2000);
267 l_dummy2 VARCHAR2(2000);
268 l_schema VARCHAR2(400);
269 --
270 BEGIN
271 --
272 -- Record the process start
273 --
274 --
275 -- Set up the parameters
276 --
277 set_parameters( p_mthd_action_id => p_mthd_action_id );
278 --
279 -- Disable the WHO trigger
280 --
281 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_ACTLS_CT_WHO DISABLE');
282 --
283 -- ---------------------------------------------------------------------------
284 -- Full Refresh Section
285 -- ---------------------------------------------------------------------------
286 --
287 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
288 --
289 -- If it's a full refresh
290 --
291 IF (g_full_refresh = 'Y') THEN
292 --
293 -- Drop Indexes
294 --
295 hri_utl_ddl.log_and_drop_indexes(
296 p_application_short_name => 'HRI',
297 p_table_name => 'HRI_MB_ACTLS_CT',
298 p_table_owner => l_schema);
299 --
300 -- Truncate the table
301 --
302 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_ACTLS_CT';
303 --
304 -- Select all people with employee assignments in the collection range.
305 -- The bind variable must be present for this sql to work when called
306 -- by PYUGEN, else itwill give error.
307 --
308 p_sqlstr :=
309 'SELECT /*+ PARALLEL(asgn, DEFAULT, DEFAULT) */
310 DISTINCT
311 asgn.assignment_id object_id
312 FROM per_all_assignments_f asgn
313 WHERE asgn.assignment_type in (''E'',''C'')
314 AND asgn.effective_end_date >= to_date(''' ||
315 to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
316 ''',''DD-MM-YYYY'') - 1
317 ORDER BY asgn.assignment_id';
318 --
319 -- End of Full Refresh Section
320 -- -------------------------------------------------------------------------
321 --
322 -- -------------------------------------------------------------------------
323 -- Start of Incremental Refresh Section
324 --
325 ELSE
326 --
327 -- Incremental Refresh will be supported later.
328 --
329 NULL;
330 --
331 --
332 -- End of Incremental Refresh Section
333 -- -------------------------------------------------------------------------
334 --
335 END IF;
336 --
337 END IF;
338 --
339 --
340 END PRE_PROCESS;
341 --
342 -- ----------------------------------------------------------------------------
343 -- PROCESS_RANGE
344 -- This procedure is dynamically the HRI multithreading utility child threads
345 -- for processing the assignment ranges. The procedure invokes the overloaded
346 -- process_range procedure to process the range.
347 -- ----------------------------------------------------------------------------
348 --
349 PROCEDURE process_range(
350 errbuf OUT NOCOPY VARCHAR2
351 ,retcode OUT NOCOPY NUMBER
352 ,p_mthd_action_id IN NUMBER
353 ,p_mthd_range_id IN NUMBER
354 ,p_start_object_id IN NUMBER
355 ,p_end_object_id IN NUMBER)
356 IS
357 --
358 l_error_step NUMBER;
359 --
360 BEGIN
361 --
362 -- Initialize the global variables
363 --
364 set_parameters(p_mthd_action_id => p_mthd_action_id
365 ,p_mthd_range_id => p_mthd_range_id);
366 --
367 dbg('calling process_range for object range from '||p_start_object_id || ' to '|| p_end_object_id);
368 --
369 -- Depending on the refresh type call the corresponding refresh program
370 --
371 IF g_full_refresh = 'Y' THEN
372 --
373 process_range(p_start_object_id => p_start_object_id
374 ,p_end_object_id => p_end_object_id);
375 --
376 ELSE
377 --
378 -- Incremental Refresh will be supported later.
379 --
380 NULL;
381 --
382 END IF;
383 --
384 errbuf := 'SUCCESS';
385 retcode := 0;
386 EXCEPTION
387 WHEN others THEN
388 output('Error encountered while processing range ='||p_mthd_range_id );
389 output(sqlerrm);
390 errbuf := SQLERRM;
391 retcode := SQLCODE;
392 --
393 RAISE;
394 --
395 END process_range;
396 --
397 -- ----------------------------------------------------------------------------
398 -- POST_PROCESS
399 -- This procedure is dynamically invoked by the HRI Multithreading utility.
400 -- It finishes the processing by updating the BIS_REFRESH_LOG table
401 -- ----------------------------------------------------------------------------
402 --
403 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
404 --
405 l_dummy1 VARCHAR2(2000);
406 l_dummy2 VARCHAR2(2000);
407 l_schema VARCHAR2(400);
408 --
409 --
410 BEGIN
411 --
412 dbg('Inside post_process');
413 --
414 set_parameters(p_mthd_action_id);
415 --
416 hri_bpl_conc_log.record_process_start('HRI_OPL_ACTLS');
417 --
418 -- Collect stats for full refresh
419 --
420 IF (g_full_refresh = 'Y') THEN
421 --
422 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
423 --
424 -- Create indexes
425 --
426 dbg('Full Refresh selected - Creating indexes');
427 --
428 hri_utl_ddl.recreate_indexes(
429 p_application_short_name => 'HRI',
430 p_table_name => 'HRI_MB_ACTLS_CT',
431 p_table_owner => l_schema);
432 --
433 -- Collect the statistics only when the process is NOT invoked by a concurrent manager
434 --
435 IF fnd_global.conc_request_id is null THEN
436 --
437 dbg('Full Refresh selected - gathering stats');
438 fnd_stats.gather_table_stats(l_schema,'HRI_MB_ACTLS_CT');
439 --
440 END IF;
441 --
442 END IF;
443 --
444 ELSE
445 --
446 -- Incremental Refresh will be supported later.
447 --
448 NULL;
449 --
450 END IF;
451 --
452 -- Enable the WHO trigger on the fact table
453 --
454 dbg('Enabling the who trigger');
455 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_ACTLS_CT_WHO ENABLE');
456 --
457 hri_bpl_conc_log.log_process_end(
458 p_status => TRUE
459 ,p_period_from => TRUNC(g_refresh_start_date)
460 ,p_period_to => TRUNC(SYSDATE)
461 ,p_attribute1 => g_full_refresh);
462 --
463 dbg('Exiting post_process');
464 --
465 END post_process;
466 --
467 -- ----------------------------------------------------------------------------
468 -- LOAD_TABLE
469 -- This procedure can be called from the Test harness to populate the table.
470 -- ----------------------------------------------------------------------------
471 --
472 PROCEDURE load_table
473 IS
474 --
475 l_sqlstr VARCHAR2(4000);
476 --
477 CURSOR c_range_cursor IS
478 SELECT mthd_range_id,
479 min(object_id) start_object_id,
480 max(object_id) end_object_id
481 FROM (SELECT hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
482 ,object_id
483 FROM (SELECT DISTINCT asgn.assignment_id object_id
484 FROM per_all_assignments_f asgn
485 WHERE asgn.assignment_type in ('E','C')
486 AND asgn.effective_end_date >= g_refresh_start_date - 1
487 ORDER BY asgn.assignment_id)
488 )
489 GROUP BY mthd_range_id;
490 --
491 BEGIN
492 --
493 dbg('Inside load_table');
494 --
495 -- Call Pre Process
496 --
497 pre_process(p_mthd_action_id => null,
498 p_sqlstr => l_sqlstr);
499 --
500 -- Call Process Range
501 --
502 FOR l_range IN c_range_cursor LOOP
503 --
504 dbg('range ='||l_range.start_object_id|| ' - '||l_range.end_object_id );
505 process_range(p_start_object_id => l_range.start_object_id
506 ,p_end_object_id => l_range.end_object_id);
507 --
508 COMMIT;
509 --
510 END LOOP;
511 --
512 -- Call Post Process
513 --
514 post_process (p_mthd_action_id => null);
515 --
516 dbg('Exiting load_table');
517 --
518 EXCEPTION
519 --
520 WHEN OTHERS THEN
521 --
522 output('Error in load_table = ');
523 output(SQLERRM);
524 RAISE;
525 --
526 END load_table;
527 --
528 END HRI_OPL_ACTLS;