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