[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_CONC_DIM
Source
1 PACKAGE BODY hri_oltp_conc_dim AS
2 /* $Header: hriocdim.pkb 120.9 2006/10/06 17:47:11 smohapat noship $ */
3
4 g_msg_sub_group VARCHAR2(400);
5
6 -- ----------------------------------------------------------
7 -- Loads Supervisor Hierarchy History table
8 -- ----------------------------------------------------------
9 PROCEDURE load_supervisor_history
10 (errbuf OUT NOCOPY VARCHAR2
11 ,retcode OUT NOCOPY VARCHAR2) IS
12
13 l_start_date VARCHAR2(80);
14 l_end_date VARCHAR2(80);
15 l_full_refresh VARCHAR2(10);
16
17 BEGIN
18
19 l_full_refresh := hri_oltp_conc_param.get_parameter_value
20 (p_parameter_name => 'FULL_REFRESH',
21 p_process_table_name => 'HRI_CS_SUPH');
22
23 l_end_date := fnd_date.date_to_displaydt(hr_general.end_of_time);
24
25 -- Set the refresh start date for full refresh
26 IF (l_full_refresh = 'Y') THEN
27 l_start_date := hri_oltp_conc_param.get_parameter_value
28 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
29 p_process_table_name => 'HRI_CS_SUPH');
30 ELSE
31 l_start_date := fnd_date.date_to_canonical
32 (fnd_date.displaydt_to_date
33 (hri_bpl_conc_log.get_last_collect_to_date
34 ('HRI_CS_SUPH','HRI_CS_SUPH')));
35 END IF;
36
37 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
38 hri_bpl_conc_log.dbg('Collect from: ' || l_start_date);
39
40 load_supervisor_history
41 (errbuf => errbuf,
42 retcode => retcode,
43 p_chunk_size => 1500,
44 p_start_date => l_start_date,
45 p_end_date => l_end_date,
46 p_full_refresh => l_full_refresh,
47 p_drop_mv_log => 'N');
48 --
49 END load_supervisor_history;
50
51 -- ----------------------------------------------------------
52 -- Loads Supervisor Hierarchy History table
53 -- ----------------------------------------------------------
54 PROCEDURE load_supervisor_history
55 (errbuf OUT NOCOPY VARCHAR2
56 ,retcode OUT NOCOPY VARCHAR2
57 ,p_start_date IN VARCHAR2
58 ,p_end_date IN VARCHAR2
59 ,p_full_refresh IN VARCHAR2
60 ,p_chunk_size IN NUMBER
61 ,p_drop_mv_log IN VARCHAR2) IS
62
63 l_start_date DATE;
64 l_end_date DATE;
65 l_is_hr_installed VARCHAR2(10);
66 l_full_refresh VARCHAR2(10);
67 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- Variable to store value for
68 -- Profile HRI:DBI Force Foundation HR Processes
69 --
70 BEGIN
71
72 hri_oltp_conc_suph_master.load_all_managers
73 (errbuf => errbuf,
74 retcode => retcode,
75 p_chunk_size => p_chunk_size,
76 p_start_date => p_start_date,
77 p_end_date => p_end_date,
78 p_full_refresh => p_full_refresh);
79
80 END load_supervisor_history;
81
82 -- ----------------------------------------------------------
83 -- Loads Position Hierarchy table
84 -- ----------------------------------------------------------
85 PROCEDURE load_all_positions
86 (errbuf OUT NOCOPY VARCHAR2
87 ,retcode OUT NOCOPY VARCHAR2
88 ,p_chunk_size IN NUMBER
89 ) IS
90 --
91 BEGIN
92 --
93 hri_bpl_conc_log.record_process_start('HRI_CS_POSH');
94 --
95 hri_opl_posh.load_all_positions
96 (errbuf => errbuf
97 ,retcode => retcode
98 ,p_chunk_size => p_chunk_size
99 );
100 --
101 hri_bpl_conc_log.log_process_end
102 (p_status => TRUE
103 ,p_attribute1 => p_chunk_size
104 );
105 --
106 -- Bug 4105868: Collection Diagnostics
107 --
108 EXCEPTION
109 --
110 WHEN OTHERS THEN
111 --
112 hri_bpl_conc_log.log_process_end
113 (p_status => FALSE
114 ,p_attribute1 => p_chunk_size
115 );
116 --
117 END load_all_positions;
118 --
119 -- ----------------------------------------------------------
120 -- Loads Organization Hierarchy table
121 -- ----------------------------------------------------------
122 --
123 PROCEDURE load_all_organizations
124 (errbuf OUT NOCOPY VARCHAR2
125 ,retcode OUT NOCOPY VARCHAR2
126 ,p_chunk_size IN NUMBER
127 ) IS
128 --
129 BEGIN
130 --
131 hri_bpl_conc_log.record_process_start('HRI_ORG_HRCHY_SUMMARY');
132 --
133 hri_opl_orgh.load
134 (errbuf => errbuf
135 ,retcode => retcode
136 ,p_chunk_size => p_chunk_size
137 );
138 --
139 hri_bpl_conc_log.log_process_end
140 (p_status => TRUE
141 ,p_attribute1 => p_chunk_size
142 );
143 --
144 -- Bug 4105868: Collection Diagnostics
145 --
146 EXCEPTION
147 --
148 WHEN OTHERS THEN
149 --
150 hri_bpl_conc_log.log_process_end
151 (p_status => FALSE
152 ,p_attribute1 => p_chunk_size
153 );
154 --
155 END load_all_organizations;
156
157 -- ----------------------------------------------------------
158 -- Loads Job Hierarchy table
159 -- ----------------------------------------------------------
160 PROCEDURE load_all_jobs
161 (errbuf OUT NOCOPY VARCHAR2
162 ,retcode OUT NOCOPY VARCHAR2
163 ,p_full_refresh IN VARCHAR2) IS
164
165 l_full_refresh VARCHAR2(30);
166
167 BEGIN
168
169 -- Log process start
170 hri_bpl_conc_log.record_process_start('HRI_CS_JOBH_CT');
171
172 -- If the full refresh parameter is not provided, default it
173 IF (p_full_refresh IS NULL) THEN
174 l_full_refresh := hri_oltp_conc_param.get_parameter_value
175 (p_parameter_name => 'FULL_REFRESH',
176 p_process_table_name => 'HRI_CS_JOBH_CT');
177 ELSE
178 l_full_refresh := p_full_refresh;
179 END IF;
180
181 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
182 hri_bpl_conc_log.dbg('Collect from: N/A');
183
184 IF (l_full_refresh = 'Y') THEN
185 hri_opl_jobh.full_refresh;
186 ELSE
187 hri_opl_jobh.incr_refresh(p_refresh_flex => 'N');
188 END IF;
189
190 hri_bpl_conc_log.log_process_end
191 (p_status => TRUE
192 ,p_period_from => hr_general.start_of_time
193 ,p_period_to => hr_general.end_of_time
194 ,p_attribute1 => p_full_refresh
195 );
196
197 EXCEPTION WHEN OTHERS THEN
198
199 hri_bpl_conc_log.log_process_end
200 (p_status => FALSE
201 ,p_period_from => hr_general.start_of_time
202 ,p_period_to => hr_general.end_of_time
203 ,p_attribute1 => p_full_refresh);
204
205 END load_all_jobs;
206
207 -- ----------------------------------------------------------
208 -- Loads Person Type Hierarchy tables
209 -- ----------------------------------------------------------
210 PROCEDURE load_all_person_types(errbuf OUT NOCOPY VARCHAR2,
211 retcode OUT NOCOPY VARCHAR2,
212 p_full_refresh IN VARCHAR2) IS
213
214 l_full_refresh VARCHAR2(30);
215
216 BEGIN
217
218 -- As the process is being run from CM, enable output logging
219 hri_bpl_conc_log.record_process_start('HRI_CS_PRSNTYP_CT');
220
221 -- If the full refresh parameter is not provided, default it
222 IF (p_full_refresh IS NULL) THEN
223 l_full_refresh := hri_oltp_conc_param.get_parameter_value
224 (p_parameter_name => 'FULL_REFRESH',
225 p_process_table_name => 'HRI_CS_PRSNTYP_CT');
226 ELSE
227 l_full_refresh := p_full_refresh;
228 END IF;
229
230 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
231 hri_bpl_conc_log.dbg('Collect from: N/A');
232
233 -- Set warning off (in case this session is re-used)
234 hri_bpl_person_type.g_warning_flag := 'N';
235
236 -- Call the appropriate function depending on full refresh parameter
237 IF (l_full_refresh = 'Y') THEN
238 hri_opl_person_type_ctgry.full_refresh;
239 ELSE
240 hri_opl_person_type_ctgry.incr_refresh;
241 END IF;
242
243 -- In case a warning was raised in the person type fast formula
244 -- package, then process should be marked as warning
245 IF hri_bpl_person_type.g_warning_flag = 'Y' THEN
246 errbuf := 'WARNING';
247 retcode := 1;
248 END IF;
249
250 -- Log process end
251 hri_bpl_conc_log.log_process_end
252 (p_status => TRUE
253 ,p_period_from => hr_general.start_of_time
254 ,p_period_to => hr_general.end_of_time
255 ,p_attribute1 => 'N');
256
257 -- Bug 4105868: Collection Diagnostics
258 EXCEPTION WHEN OTHERS THEN
259
260 g_msg_sub_group := NVL(g_msg_sub_group, 'INCR_REFRESH');
261
262 hri_bpl_conc_log.log_process_info
263 (p_package_name => 'HRI_OPL_PERSON_TYPE_CTGRY'
264 ,p_msg_type => 'ERROR'
265 ,p_msg_group => 'PRSN_TYP_CNGS'
266 ,p_msg_sub_group => g_msg_sub_group
267 ,p_sql_err_code => SQLCODE
268 ,p_note => SQLERRM);
269
270 hri_bpl_conc_log.log_process_end
271 (p_status => FALSE
272 ,p_period_from => hr_general.start_of_time
273 ,p_period_to => hr_general.end_of_time
274 ,p_attribute1 => 'N');
275
276 RAISE;
277
278 END load_all_person_types;
279
280 -- ----------------------------------------------------------
281 -- Loads Person Dimension CT
282 -- ----------------------------------------------------------
283 PROCEDURE load_all_persons(errbuf OUT NOCOPY VARCHAR2,
284 retcode OUT NOCOPY VARCHAR2,
285 p_full_refresh IN VARCHAR2) IS
286
287 l_full_refresh VARCHAR2(30);
288
289 BEGIN
290
291 -- As the process is being run from CM, enable output logging
292 hri_bpl_conc_log.record_process_start('HRI_CS_PER_PERSON_CT');
293
294 -- If the full refresh parameter is not provided, default it
295 IF (p_full_refresh IS NULL) THEN
296 l_full_refresh := hri_oltp_conc_param.get_parameter_value
297 (p_parameter_name => 'FULL_REFRESH',
298 p_process_table_name => 'HRI_CS_PER_PERSON_CT');
299 ELSE
300 l_full_refresh := p_full_refresh;
301 END IF;
302
303 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
304 hri_bpl_conc_log.dbg('Collect from: N/A');
305
306 -- Call the appropriate function depending on full refresh parameter
307 IF (l_full_refresh = 'Y') THEN
308 hri_opl_per_person.load(1500,
309 to_char(hr_general.start_of_time, 'YYYY/MM/DD HH24:MI:SS'),
310 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
311 ,'Y');
312 ELSE
313 hri_opl_per_person.load(1500,
314 to_char(hr_general.start_of_time, 'YYYY/MM/DD HH24:MI:SS'),
315 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
316 ,'N');
317
318 END IF;
319
320 -- Log process end
321 hri_bpl_conc_log.log_process_end
322 (p_status => TRUE
323 ,p_period_from => hr_general.start_of_time
324 ,p_period_to => hr_general.end_of_time
325 ,p_attribute1 => 'N');
326
327 EXCEPTION WHEN OTHERS THEN
328
329 g_msg_sub_group := NVL(g_msg_sub_group, 'INCR_REFRESH');
330
331 hri_bpl_conc_log.log_process_info
332 (p_package_name => 'HRI_CS_PER_PERSON_CT'
333 ,p_msg_type => 'ERROR'
334 ,p_msg_group => 'PRSN_CNGS'
335 ,p_msg_sub_group => g_msg_sub_group
336 ,p_sql_err_code => SQLCODE
337 ,p_note => SQLERRM);
338
339 hri_bpl_conc_log.log_process_end
340 (p_status => FALSE
341 ,p_period_from => hr_general.start_of_time
342 ,p_period_to => hr_general.end_of_time
343 ,p_attribute1 => 'N');
344
345 RAISE;
346
347 END load_all_persons;
348
349
350 -- ----------------------------------------------------------
351 -- Loads period of work band table
352 -- ----------------------------------------------------------
353 PROCEDURE load_all_pow_bands
354 (errbuf OUT NOCOPY VARCHAR2
355 ,retcode OUT NOCOPY VARCHAR2
356 ,p_full_refresh IN VARCHAR2) IS
357
358 l_full_refresh VARCHAR2(30);
359
360 BEGIN
361
362 -- Log process start
363 hri_bpl_conc_log.record_process_start('HRI_CS_POW_BAND_CT');
364
365 -- If the full refresh parameter is not provided, default it
366 IF (p_full_refresh IS NULL) THEN
367 l_full_refresh := hri_oltp_conc_param.get_parameter_value
368 (p_parameter_name => 'FULL_REFRESH',
369 p_process_table_name => 'HRI_CS_POW_BAND_CT');
370 ELSE
371 l_full_refresh := p_full_refresh;
372 END IF;
373
374 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
375 hri_bpl_conc_log.dbg('Collect from: N/A');
376
377 -- Set warning off - in case the session is reused
378 hri_opl_period_of_work.g_warning_flag := 'N';
379
380 -- Refresh only when the full refresh flag is set to 'Y'
381 IF (l_full_refresh = 'Y') THEN
382
383 -- Full refresh
384 hri_opl_period_of_work.full_refresh;
385
386 ELSE
387
388 -- Period of work bands are not refreshed in incremental mode
389 -- Log this into the concurrent program log
390 hri_bpl_conc_log.output('Period of work bands are not ' ||
391 'refreshed in incremental mode');
392
393 END IF;
394
395 -- In case a warning is raised then the process should be marked as warning
396 IF (hri_opl_period_of_work.g_warning_flag = 'Y') THEN
397 errbuf:= 'WARNING';
398 retcode:= 1;
399 END IF;
400
401 -- Log process end
402 hri_bpl_conc_log.log_process_end
403 (p_status => TRUE
404 ,p_period_from => hr_general.start_of_time
405 ,p_period_to => hr_general.end_of_time
406 ,p_attribute1 => 'N');
407
408 EXCEPTION WHEN OTHERS THEN
409
410 hri_bpl_conc_log.log_process_end
411 (p_status => FALSE
412 ,p_period_from => hr_general.start_of_time
413 ,p_period_to => hr_general.end_of_time
414 ,p_attribute1 => 'N');
415
416 RAISE;
417
418 END load_all_pow_bands;
419
420 --
421 -- ----------------------------------------------------------
422 -- Loads job role table
423 -- ----------------------------------------------------------
424 --
425 PROCEDURE load_all_job_job_roles
426 (errbuf OUT NOCOPY VARCHAR2
427 ,retcode OUT NOCOPY VARCHAR2
428 ,p_full_refresh IN VARCHAR2) IS
429
430 l_full_refresh VARCHAR2(30);
431
432 BEGIN
433
434 -- Log process start
435 hri_bpl_conc_log.record_process_start('HRI_CS_JOB_JOB_ROLE_CT');
436
437 -- If the full refresh parameter is not provided, default it
438 IF (p_full_refresh IS NULL) THEN
439 l_full_refresh := hri_oltp_conc_param.get_parameter_value
440 (p_parameter_name => 'FULL_REFRESH',
441 p_process_table_name => 'HRI_CS_JOB_JOB_ROLE_CT');
442 ELSE
443 l_full_refresh := p_full_refresh;
444 END IF;
445
446 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
447 hri_bpl_conc_log.dbg('Collect from: N/A');
448
449 -- Set warning off - in case the session is reused
450 hri_bpl_job.g_warning_flag := 'N';
451
452 IF (l_full_refresh = 'Y') THEN
453 hri_opl_job_job_role.full_refresh;
454 ELSE
455 hri_opl_job_job_role.incr_refresh;
456 END IF;
457
458 -- In case a warning is raised then the process should be marked as warning
459 IF (hri_bpl_job.g_warning_flag = 'Y') THEN
460 errbuf:= 'WARNING';
461 retcode:= 1;
462 END IF;
463
464 -- Log process end
465 hri_bpl_conc_log.log_process_end
466 (p_status => TRUE
467 ,p_period_from => hr_general.start_of_time
468 ,p_period_to => SYSDATE
469 ,p_attribute1 => 'N');
470
471 EXCEPTION WHEN OTHERS THEN
472
473 hri_bpl_conc_log.log_process_end
474 (p_status => FALSE
475 ,p_period_from => hr_general.start_of_time
476 ,p_period_to => SYSDATE
477 ,p_attribute1 => 'N');
478
479 RAISE;
480
481 END load_all_job_job_roles;
482
483 END HRI_OLTP_CONC_DIM;