[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_JOBH
Source
1 PACKAGE BODY hri_opl_jobh AS
2 /* $Header: hripjobh.pkb 120.4 2006/10/11 15:38:06 jtitmas noship $ */
3
4 -- Value sets used to define job levels
5 g_job_fmly_vset_id NUMBER;
6 g_job_fnctn_vset_id NUMBER;
7
8 -- HRI schema name
9 g_hri_schema VARCHAR2(30);
10
11 -- Full refresh mode
12 g_full_refresh VARCHAR2(30);
13
14 -- Whether job levels are populated from KEY or DESCRIPTIVE flexs
15 g_job_fmly_flex_type VARCHAR2(30);
16 g_job_fnctn_flex_type VARCHAR2(30);
17
18 -- Record for caching job family and function flex columns
19 TYPE job_group_rec IS RECORD
20 (job_fmly_column VARCHAR2(30),
21 job_fnctn_column VARCHAR2(30));
22
23 -- Cache for flex columns
24 TYPE job_flex_tab_type IS TABLE OF job_group_rec INDEX BY VARCHAR2(300);
25 job_flex_cache job_flex_tab_type;
26 job_flex_reset job_flex_tab_type;
27
28 -- Return character
29 g_rtn VARCHAR2(5) := '
30 ';
31
32 -- -------------------------------------------------------------------------
33 -- Inserts row into concurrent program log
34 -- -------------------------------------------------------------------------
35 PROCEDURE output(p_text VARCHAR2) IS
36
37 BEGIN
38 hri_bpl_conc_log.output(p_text);
39 END output;
40
41 -- -------------------------------------------------------------------------
42 -- Inserts row into concurrent program log if debugging is enabled
43 -- -------------------------------------------------------------------------
44 PROCEDURE dbg(p_text VARCHAR2) IS
45
46 BEGIN
47 hri_bpl_conc_log.dbg(p_text);
48 END dbg;
49
50 -- ----------------------------------------------------------------------------
51 -- 3601362 Runs given sql statement dynamically without raising an exception
52 -- ----------------------------------------------------------------------------
53 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
54
55 BEGIN
56 EXECUTE IMMEDIATE p_sql_stmt;
57 EXCEPTION WHEN OTHERS THEN
58 output('Could not run the following sql:');
59 output(SUBSTR(p_sql_stmt,1,230));
60 dbg(sqlerrm);
61 END run_sql_stmt_noerr;
62
63 -- --------------------------------------------------------------------
64 -- Returns whether a job family/function valueset is stored against
65 -- the KEY or DESCRIPTIVE flexfield or neither (NULL)
66 -- --------------------------------------------------------------------
67 FUNCTION get_flexfield_type(p_job_type IN VARCHAR2,
68 p_value_set_id IN NUMBER)
69 RETURN VARCHAR2 IS
70
71 CURSOR full_keyflex_csr IS
72 SELECT 'KEY'
73 FROM fnd_id_flex_segments_vl
74 WHERE application_id = 800
75 AND id_flex_code = 'JOB'
76 AND flex_value_set_id = p_value_set_id
77 AND rownum = 1;
78
79 CURSOR full_descr_flex_csr IS
80 SELECT 'DESCRIPTIVE'
81 FROM
82 fnd_descr_flex_col_usage_vl dfcu
83 WHERE dfcu.descriptive_flexfield_name = 'PER_JOBS'
84 AND dfcu.application_id = 800
85 AND dfcu.flex_value_set_id = p_value_set_id
86 AND rownum = 1;
87
88 CURSOR incr_jfm_flex_type_csr IS
89 SELECT
90 flexfield_type
91 FROM
92 hri_cnfg_jobh_flex_cols
93 WHERE job_fmly_column IS NOT NULL
94 AND rownum = 1;
95
96 CURSOR incr_jfn_flex_type_csr IS
97 SELECT
98 flexfield_type
99 FROM
100 hri_cnfg_jobh_flex_cols
101 WHERE job_fnctn_column IS NOT NULL
102 AND rownum = 1;
103
104 l_flexfield_type VARCHAR2(30);
105
106 BEGIN
107
108 -- In full refresh mode check the flexfield structure for
109 -- the corresponding valueset associated with the job type
110 IF (g_full_refresh = 'Y' OR
111 g_full_refresh IS NULL) THEN
112
113 -- Check if the valueset is linked to a keyflex
114 OPEN full_keyflex_csr;
115 FETCH full_keyflex_csr INTO l_flexfield_type;
116 CLOSE full_keyflex_csr;
117
118 -- If no keyflex link try descriptive
119 IF (l_flexfield_type IS NULL) THEN
120 OPEN full_descr_flex_csr;
121 FETCH full_descr_flex_csr INTO l_flexfield_type;
122 CLOSE full_descr_flex_csr;
123 END IF;
124
125 -- In incremental refresh check the stored structure information
126 -- from the job hierarchy configuration table
127 ELSE
128
129 -- Set the return variable from the corresponding cursor
130 IF (p_job_type = 'JOB_FUNCTION') THEN
131 OPEN incr_jfn_flex_type_csr;
132 FETCH incr_jfn_flex_type_csr INTO l_flexfield_type;
133 CLOSE incr_jfn_flex_type_csr;
134 ELSIF (p_job_type = 'JOB_FAMILY') THEN
135 OPEN incr_jfm_flex_type_csr;
136 FETCH incr_jfm_flex_type_csr INTO l_flexfield_type;
137 CLOSE incr_jfm_flex_type_csr;
138 END IF;
139
140 END IF;
141
142 -- If neither flexfield type is found return NA_EDW
143 RETURN NVL(l_flexfield_type, 'NA_EDW');
144
145 END get_flexfield_type;
146
147 -- --------------------------------------------------------------------
148 -- Returns whether a job family/function valueset is stored against
149 -- the KEY or DESCRIPTIVE flexfield or neither (NA_EDW)
150 -- --------------------------------------------------------------------
151 FUNCTION get_flexfield_type(p_job_type IN VARCHAR2)
152 RETURN VARCHAR2 IS
153
154 BEGIN
155
156 -- Populate the cache if it is empty
157 IF (g_job_fmly_flex_type IS NULL) THEN
158
159 -- Cache both job family and function flexfield types
160 g_job_fnctn_flex_type := get_flexfield_type
161 (p_job_type => 'JOB_FUNCTION',
162 p_value_set_id => g_job_fnctn_vset_id);
163 g_job_fmly_flex_type := get_flexfield_type
164 (p_job_type => 'JOB_FAMILY',
165 p_value_set_id => g_job_fmly_vset_id);
166 END IF;
167
168 -- Return respective value from cache
169 IF (p_job_type = 'JOB_FUNCTION') THEN
170 RETURN g_job_fnctn_flex_type;
171 ELSIF (p_job_type = 'JOB_FAMILY') THEN
172 RETURN g_job_fmly_flex_type;
173 END IF;
174
175 RETURN 'NA_EDW';
176
177 END get_flexfield_type;
178
179 /******************************************************************************/
180 /* CONFIGURATION TABLE SECTION */
181 /******************************************************************************/
182
183 -- ----------------------------------------------------------------------------
184 -- Refreshes the configuration table, which maps flexfield structure columns
185 -- to job hierarchy levels
186 -- ----------------------------------------------------------------------------
187 PROCEDURE refresh_config_table IS
188
189 CURSOR keyflex_csr(v_valueset_id NUMBER) IS
190 SELECT
191 to_char(fsg.id_flex_num) job_flex_code
192 ,MIN(fsg.application_column_name) flex_column
193 FROM
194 fnd_id_flex_segments_vl fsg
195 WHERE fsg.application_id = 800
196 AND fsg.id_flex_code = 'JOB'
197 AND fsg.flex_value_set_id = v_valueset_id
198 GROUP BY fsg.id_flex_num;
199
200 CURSOR descr_flex_csr(v_valueset_id NUMBER) IS
201 SELECT
202 DECODE(ctxt.global_flag,
203 'Y', 'NA_EDW',
204 dfcu.descriptive_flex_context_code) job_flex_code
205 ,dfcu.application_column_name flex_column
206 ,ctxt.global_flag global_flag
207 FROM
208 fnd_descr_flex_col_usage_vl dfcu
209 ,fnd_descr_flex_contexts_vl ctxt
210 WHERE dfcu.descriptive_flexfield_name = 'PER_JOBS'
211 AND dfcu.application_id = 800
212 AND dfcu.flex_value_set_id = v_valueset_id
213 AND ctxt.application_id = dfcu.application_id
214 AND ctxt.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
215 AND ctxt.descriptive_flex_context_code = dfcu.descriptive_flex_context_code;
216
217 l_job_fmly_flex_type VARCHAR2(30);
218 l_job_fnctn_flex_type VARCHAR2(30);
219 l_job_fmly_global_col VARCHAR2(30);
220 l_job_fnctn_global_col VARCHAR2(30);
221 l_index VARCHAR2(300);
222 l_index_type VARCHAR2(30);
223 l_index_code VARCHAR2(240);
224
225 BEGIN
226
227 -- Truncate table
228 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.hri_cnfg_jobh_flex_cols';
229
230 -- Get flexfield types for full refresh of config table
231 l_job_fnctn_flex_type := get_flexfield_type('JOB_FUNCTION');
232 l_job_fmly_flex_type := get_flexfield_type('JOB_FAMILY');
233
234 -- -------------------------------------------
235 -- Cache Job Function Flexfield Configuration
236 -- -------------------------------------------
237
238 IF (l_job_fnctn_flex_type = 'KEY') THEN
239
240 -- Populate job function flex columns in cache from key flex
241 FOR keyflex_rec IN keyflex_csr(g_job_fnctn_vset_id) LOOP
242 job_flex_cache('KEY|' || keyflex_rec.job_flex_code).job_fnctn_column
243 := keyflex_rec.flex_column;
244 END LOOP;
245
246 ELSIF (l_job_fnctn_flex_type = 'DESCRIPTIVE') THEN
247
248 -- Populate job function flex columns in cache from desc flex
249 FOR descr_flex_rec IN descr_flex_csr(g_job_fnctn_vset_id) LOOP
250 job_flex_cache('DESCRIPTIVE|' || descr_flex_rec.job_flex_code).job_fnctn_column
251 := descr_flex_rec.flex_column;
252 -- Note global column if available
253 IF (descr_flex_rec.global_flag = 'Y') THEN
254 l_job_fnctn_global_col := descr_flex_rec.flex_column;
255 END IF;
256 END LOOP;
257
258 END IF;
259
260 -- -----------------------------------------
261 -- Cache Job Family Flexfield Configuration
262 -- -----------------------------------------
263
264 IF (l_job_fmly_flex_type = 'KEY') THEN
265
266 -- Populate job family flex columns in cache from key flex
267 FOR keyflex_rec IN keyflex_csr(g_job_fmly_vset_id) LOOP
268 job_flex_cache('KEY|' || keyflex_rec.job_flex_code).job_fmly_column
269 := keyflex_rec.flex_column;
270 END LOOP;
271
272 ELSIF (l_job_fmly_flex_type = 'DESCRIPTIVE') THEN
273
274 -- Populate job function flex columns in cache from desc flex
275 FOR descr_flex_rec IN descr_flex_csr(g_job_fmly_vset_id) LOOP
276 job_flex_cache('DESCRIPTIVE|' || descr_flex_rec.job_flex_code).job_fmly_column
277 := descr_flex_rec.flex_column;
278 -- Note global column if available
279 IF (descr_flex_rec.global_flag = 'Y') THEN
280 l_job_fmly_global_col := descr_flex_rec.flex_column;
281 END IF;
282 END LOOP;
283
284 END IF;
285
286 -- -----------------------------------------
287 -- Insert from cache
288 -- -----------------------------------------
289
290 l_index := job_flex_cache.FIRST;
291
292 WHILE l_index IS NOT NULL LOOP
293
294 l_index_type := SUBSTR(l_index, 1, INSTR(l_index, '|') - 1);
295 l_index_code := SUBSTR(l_index, INSTR(l_index, '|') + 1);
296
297 -- If a DF global is available use it for all DF contexts
298 IF (l_index_type = 'DESCRIPTIVE') THEN
299
300 -- Check the job function global
301 IF (l_job_fnctn_global_col IS NOT NULL) THEN
302 job_flex_cache(l_index).job_fnctn_column := l_job_fnctn_global_col;
303 END IF;
304
305 -- Check the job family global
306 IF (l_job_fmly_global_col IS NOT NULL) THEN
307 job_flex_cache(l_index).job_fmly_column := l_job_fmly_global_col;
308 END IF;
309
310 END IF;
311
312 -- Insert the value into the configuration table
313 INSERT INTO hri_cnfg_jobh_flex_cols
314 (flexfield_type
315 ,job_flex_code
316 ,job_fnctn_column
317 ,job_fmly_column)
318 VALUES
319 (l_index_type
320 ,l_index_code
321 ,job_flex_cache(l_index).job_fnctn_column
322 ,job_flex_cache(l_index).job_fmly_column);
323
324 l_index := job_flex_cache.NEXT(l_index);
325
326 END LOOP;
327
328 -- commit
329 COMMIT;
330
331 END refresh_config_table;
332
333 /******************************************************************************/
334 /* GENERATE LOV VIEWS SECTION */
335 /******************************************************************************/
336
337 -- ----------------------------------------------------------------------------
338 -- Attaches comments to the LOV views created
339 -- ----------------------------------------------------------------------------
340 PROCEDURE attach_comments IS
341
342 -- View and column descriptions for job family
343 l_job_fmly_view VARCHAR2(500);
344 l_job_fmly_id VARCHAR2(500);
345 l_job_fmly_value VARCHAR2(500);
346
347 -- View and column descriptions for job family
348 l_job_fnctn_view VARCHAR2(500);
349 l_job_fnctn_id VARCHAR2(500);
350 l_job_fnctn_value VARCHAR2(500);
351
352 -- Generic column descriptions
353 l_start_date VARCHAR2(500);
354 l_end_date VARCHAR2(500);
355
356 BEGIN
357
358 -- View and column descriptions for job family
359 l_job_fmly_view :=
360 'List of values for the job family level, dynamically generated from the ' ||
361 'value set defined in the profile option "BIS: HR Job Hierarchy Job Family Level"';
362 l_job_fmly_id := 'Unique identifier for job family';
363 l_job_fmly_value := 'Job family name';
364
365 -- View and column descriptions for job function
366 l_job_fnctn_view :=
367 'List of values for the job function level, dynamically generated from the ' ||
368 'value set defined in the profile option "BIS: HR Job Hierarchy Job Function Level"';
369 l_job_fnctn_id := 'Unique identifier for job function';
370 l_job_fnctn_value := 'Job function name';
371
372 -- Generic column descriptions
373 l_start_date := 'Effective start date of value';
374 l_end_date := 'Effective end date of value';
375
376 -- ----------------------------------------------------------------------------
377 -- Comments on Job Family view
378 -- ----------------------------------------------------------------------------
379 EXECUTE IMMEDIATE 'COMMENT ON TABLE hri_cl_job_family_v'
380 || ' IS ''' || l_job_fmly_view || '''';
381 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_family_v.id'
382 || ' IS ''' || l_job_fmly_id || '''';
383 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_family_v.value'
384 || ' IS ''' || l_job_fmly_value || '''';
385 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_family_v.start_date'
386 || ' IS ''' || l_start_date || '''';
387 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_family_v.end_date'
388 || ' IS ''' || l_end_date || '''';
389
390 -- ----------------------------------------------------------------------------
391 -- Comments on Job Function view
392 -- ----------------------------------------------------------------------------
393 EXECUTE IMMEDIATE 'COMMENT ON TABLE hri_cl_job_function_v'
394 || ' IS ''' || l_job_fnctn_view || '''';
395 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_function_v.id'
396 || ' IS ''' || l_job_fnctn_id || '''';
397 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_function_v.value'
398 || ' IS ''' || l_job_fnctn_value || '''';
399 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_function_v.start_date'
400 || ' IS ''' || l_start_date || '''';
401 EXECUTE IMMEDIATE 'COMMENT ON COLUMN hri_cl_job_function_v.end_date'
402 || ' IS ''' || l_end_date || '''';
403
404 EXCEPTION WHEN OTHERS THEN
405 null;
406 END attach_comments;
407
408 -- ----------------------------------------------------------------------------
409 -- Generates the LOV views for job fmly and job fnctn
410 -- The code to generate each view is done separately to make it easier to
411 -- make modifications to one in future
412 -- ----------------------------------------------------------------------------
413 PROCEDURE generate_lov_views IS
414
415 l_vset_lov_sql VARCHAR2(4000);
416 l_view_sql VARCHAR2(4000);
417 l_dummy1 VARCHAR2(2000);
418 l_dummy2 VARCHAR2(2000);
419 l_fnd_schema VARCHAR2(30);
420 l_fmly_sql VARCHAR2(4000);
421 l_fnctn_sql VARCHAR2(4000);
422
423 BEGIN
424
425 -- Get FND schema name
426 IF (fnd_installation.get_app_info('FND',l_dummy1, l_dummy2, l_fnd_schema)) THEN
427
428 -- ----------------------------------------------------------------------------
429 -- Generate Job Family view
430 -- ----------------------------------------------------------------------------
431
432 -- Bug 3387576 - Added distinct flag = 'Y' for job level 2
433 hri_bpl_flex.get_value_set_lov_sql
434 (p_flex_value_set_id => g_job_fmly_vset_id
435 ,p_sql_stmt => l_vset_lov_sql
436 ,p_distinct_flag => 'Y');
437
438 -- If the valueset is defined add it as the first UNION
439 IF (l_vset_lov_sql IS NOT NULL) THEN
440 --
441 l_fmly_sql := l_vset_lov_sql || g_rtn;
442 l_vset_lov_sql := l_vset_lov_sql || g_rtn || 'UNION ALL' || g_rtn;
443 --
444 ELSE
445 --
446 l_fmly_sql := 'SELECT ' || g_rtn
447 || 'id_char id' ||g_rtn
448 || ',hri_oltp_view_message.get_unassigned_msg value' || g_rtn
449 || ',hr_general.start_of_time start_date' || g_rtn
450 || ',hr_general.end_of_time end_date' || g_rtn
451 || ',''2'' order_by' || g_rtn
452 || 'FROM hri_unassigned';
453 END IF;
454
455 l_view_sql :=
456 'CREATE OR REPLACE FORCE VIEW hri_cl_job_family_v
457 (id
458 ,value
459 ,start_date
460 ,end_date
461 ,order_by)
462 AS
463 ' || l_vset_lov_sql ||
464 'SELECT
465 id_char
466 ,hri_oltp_view_message.get_unassigned_msg value
467 ,hr_general.start_of_time
468 ,hr_general.end_of_time
469 ,''2''
470 FROM hri_unassigned';
471
472 -- Log the statement in debug mode
473 dbg('About to create job family view with:');
474 dbg(' ');
475 dbg(l_view_sql);
476 dbg(' ');
477
478 -- Execute view creation statement
479 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
480 application_short_name => 'APPS',
481 statement_type => ad_ddl.create_view,
482 statement => l_view_sql,
483 object_name => 'HRI_CL_JOB_FAMILY_V');
484 -- -------------------------------------------------------------------------
485 -- Generate OBI Job Family View
486 -- -------------------------------------------------------------------------
487
488 l_view_sql :=
489 'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_family_v
490 (fmly_family_pk
491 ,fmly_family_name
492 ,fmly_start_date
493 ,fmly_end_date
494 ,fmly_order_by)
495 AS
496 ' || l_vset_lov_sql ||
497 'SELECT
498 id_char
499 ,hri_oltp_view_message.get_unassigned_msg value
500 ,hr_general.start_of_time
501 ,hr_general.end_of_time
502 ,''2''
503 FROM hri_unassigned';
504
505 -- Log the statement in debug mode
506 dbg('About to create obi job family view with:');
507 dbg(' ');
508 dbg(l_view_sql);
509 dbg(' ');
510
511 -- Execute view creation statement
512 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
513 application_short_name => 'APPS',
514 statement_type => ad_ddl.create_view,
515 statement => l_view_sql,
516 object_name => 'HRI_OBI_CL_JOB_FAMILY_V');
517
518
519 -- Reset view sql
520 l_vset_lov_sql := null;
521
522 -- ----------------------------------------------------------------------------
523 -- Generate Job Function view
524 -- ----------------------------------------------------------------------------
525
526 -- Bug 3387576 - Added distinct flag = 'N' for job level 1
527 hri_bpl_flex.get_value_set_lov_sql
528 (p_flex_value_set_id => g_job_fnctn_vset_id
529 ,p_sql_stmt => l_vset_lov_sql
530 ,p_distinct_flag => 'N');
531
532 -- If the valueset is defined add it as the first UNION
533 IF (l_vset_lov_sql IS NOT NULL) THEN
534 --
535 l_fnctn_sql := l_vset_lov_sql || g_rtn;
536 l_vset_lov_sql := l_vset_lov_sql || g_rtn || 'UNION ALL' || g_rtn;
537 --
538 ELSE
539 --
540 l_fnctn_sql := 'SELECT ' || g_rtn
541 || 'id_char id' ||g_rtn
542 || ',hri_oltp_view_message.get_unassigned_msg value' || g_rtn
543 || ',hr_general.start_of_time start_date' || g_rtn
544 || ',hr_general.end_of_time end_date' || g_rtn
545 || ',''2'' order_by' || g_rtn
546 || 'FROM hri_unassigned';
547 END IF;
548
549 l_view_sql :=
550 'CREATE OR REPLACE FORCE VIEW hri_cl_job_function_v
551 (id
552 ,value
553 ,start_date
554 ,end_date
555 ,order_by)
556 AS
557 ' || l_vset_lov_sql ||
558 'SELECT
559 id_char
560 ,hri_oltp_view_message.get_unassigned_msg value
561 ,hr_general.start_of_time
562 ,hr_general.end_of_time
563 ,''2''
564 FROM hri_unassigned';
565
566 -- Log the statement in debug mode
567 dbg('About to create job function view with:');
568 dbg(' ');
569 dbg(l_view_sql);
570 dbg(' ');
571
572 -- Execute view creation statement
573 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
574 application_short_name => 'APPS',
575 statement_type => ad_ddl.create_view,
576 statement => l_view_sql,
577 object_name => 'HRI_CL_JOB_FUNCTION_V');
578
579 -- ----------------------------------------------------------------------------
580 -- Generate OBI Job Function View
581 -- ----------------------------------------------------------------------------
582 l_view_sql :=
583 'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_function_v
584 (fnct_function_pk
585 ,fnct_function_name
586 ,fnct_start_date
587 ,fnct_end_date
588 ,fnct_order_by)
589 AS
590 ' || l_vset_lov_sql ||
591 'SELECT
592 id_char
593 ,hri_oltp_view_message.get_unassigned_msg value
594 ,hr_general.start_of_time
595 ,hr_general.end_of_time
596 ,''2''
597 FROM hri_unassigned';
598
599 -- Log the statement in debug mode
600 dbg('About to create obi job function view with:');
601 dbg(' ');
602 dbg(l_view_sql);
603 dbg(' ');
604
605 -- Execute view creation statement
606 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
607 application_short_name => 'APPS',
608 statement_type => ad_ddl.create_view,
609 statement => l_view_sql,
610 object_name => 'HRI_OBI_CL_JOB_FUNCTION_V');
611
612 -- Reset view sql
613 l_vset_lov_sql := null;
614
615 -- ------------------------------------------------------------------------
616 -- Generate OBI Job Family Function View
617 -- ------------------------------------------------------------------------
618
619 l_vset_lov_sql :=
620 'SELECT DISTINCT' || g_rtn
621 || 'job.job_fmly_code || '' ('' || job.job_fnctn_code || '')'' fmfn_fmlyfnct_pk' || g_rtn
622 || ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
623 || '' ('' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || '')'' fmfn_fmlyfnct_name' || g_rtn
624 || ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
625 || '' ('' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || '')'' fmfn_fmlyfnct_name_unq' || g_rtn
626 || ',job.job_fmly_code fmfn_family_fk' || g_rtn
627 || ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg) fmfn_family_name' || g_rtn
628 || ',job.job_fnctn_code fmfn_function_fk' || g_rtn
629 || ',NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) fmfn_function_name' || g_rtn
630 || ',(CASE' || g_rtn
631 || ' WHEN jfm.value IS NULL THEN' || g_rtn
632 || ' CASE WHEN jfn.value IS NULL THEN NULL' || g_rtn
633 || ' WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
634 || ' ELSE jfn.value' || g_rtn
635 || ' END' || g_rtn
636 || ' WHEN jfn.value IS NULL THEN' || g_rtn
637 || ' CASE WHEN jfm.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
638 || ' ELSE jfm.value' || g_rtn
639 || ' END' || g_rtn
640 || ' WHEN jfm.value = hri_oltp_view_message.get_unassigned_msg THEN' || g_rtn
641 || ' CASE WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
642 || ' ELSE jfn.value' || g_rtn
643 || ' END' || g_rtn
644 || ' WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN jfm.value' || g_rtn
645 || ' ELSE jfm.value || '' ('' || jfn.value || '')''' || g_rtn
646 || ' END) fmfn_order_by' || g_rtn
647 || 'FROM hri_cs_jobh_ct job,' || g_rtn
648 || '(' || l_fmly_sql || ') jfm,' || g_rtn
649 || '(' || l_fnctn_sql || ') jfn' || g_rtn
650 || 'WHERE job.job_fmly_code = jfm.id(+)' || g_rtn
651 || 'AND job.job_fnctn_code = jfn.id(+)';
652 --
653 --
654 l_view_sql :=
655 'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_fmlyfnct_v
656 (fmfn_fmlyfnct_pk
657 ,fmfn_fmlyfnct_name
658 ,fmfn_fmlyfnct_name_unq
659 ,fmfn_family_fk
660 ,fmfn_family_name
661 ,fmfn_function_fk
662 ,fmfn_function_name
663 ,fmfn_order_by)
664 AS
665 ' || l_vset_lov_sql;
666
667
668 -- Log the statement in debug mode
669 dbg('About to create obi job family function view with:');
670 dbg(' ');
671 dbg(l_view_sql);
672 dbg(' ');
673
674 -- Execute view creation statement
675 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
676 application_short_name => 'APPS',
677 statement_type => ad_ddl.create_view,
678 statement => l_view_sql,
679 object_name => 'HRI_OBI_CL_JOB_FMLYFNCT_V');
680
681
682 -- Add comments to objects for eTRM
683 attach_comments;
684 --
685 END IF;
686 --
687 END generate_lov_views;
688
689 /******************************************************************************/
690 /* LOAD JOB HIERARCHY TABLE SECTION */
691 /******************************************************************************/
692
693 -- ---------------------------------------------------------------------------
694 -- Looks up the columns to use for a given flexfield structure and job type
695 -- ---------------------------------------------------------------------------
696 PROCEDURE get_job_flex_columns(p_flex_type IN VARCHAR2,
697 p_flex_code IN VARCHAR2,
698 p_job_fmly_column OUT NOCOPY VARCHAR2,
699 p_job_fnctn_column OUT NOCOPY VARCHAR2) IS
700
701 CURSOR job_segment_csr IS
702 SELECT
703 job_fmly_column
704 ,job_fnctn_column
705 FROM
706 hri_cnfg_jobh_flex_cols
707 WHERE flexfield_type = p_flex_type
708 AND job_flex_code = p_flex_code;
709
710 BEGIN
711
712 -- PL/SQL block to trap cache misses
713 BEGIN
714
715 -- Return appropriate columns from cache
716 p_job_fmly_column := job_flex_cache
717 (p_flex_type || '|' || p_flex_code).job_fmly_column;
718 p_job_fnctn_column := job_flex_cache
719 (p_flex_type || '|' || p_flex_code).job_fnctn_column;
720
721 -- Cache miss
722 EXCEPTION WHEN OTHERS THEN
723
724 -- Populate cache
725 OPEN job_segment_csr;
726 FETCH job_segment_csr INTO p_job_fmly_column, p_job_fnctn_column;
727 CLOSE job_segment_csr;
728
729 -- If no record found in DESCRIPTIVE mode then check the global
730 IF (p_flex_type = 'DESCRIPTIVE' AND
731 p_flex_code <> 'NA_EDW' AND
732 p_job_fmly_column IS NULL AND
733 p_job_fnctn_column IS NULL) THEN
734
735 -- Recursively call function to get the global values from cache
736 get_job_flex_columns
737 (p_flex_type => 'DESCRIPTIVE',
738 p_flex_code => 'NA_EDW',
739 p_job_fmly_column => p_job_fmly_column,
740 p_job_fnctn_column => p_job_fnctn_column);
741
742 END IF;
743
744 -- Populate cache with results
745 job_flex_cache(p_flex_type || '|' || p_flex_code).job_fmly_column
746 := p_job_fmly_column;
747 job_flex_cache(p_flex_type || '|' || p_flex_code).job_fnctn_column
748 := p_job_fnctn_column;
749
750 END;
751
752 END get_job_flex_columns;
753
754 -- --------------------------------------------------------------------------
755 -- Returns the flexfield column to use for the given flexfield type and code
756 -- --------------------------------------------------------------------------
757 FUNCTION get_job_flex_column(p_flex_type IN VARCHAR2,
758 p_flex_code IN VARCHAR2,
759 p_job_type IN VARCHAR2)
760 RETURN VARCHAR2 IS
761
762 l_job_fmly_column VARCHAR2(30);
763 l_job_fnctn_column VARCHAR2(30);
764
765 BEGIN
766
767 -- Call cache function to get flex columns
768 get_job_flex_columns
769 (p_flex_type => p_flex_type,
770 p_flex_code => p_flex_code,
771 p_job_fmly_column => l_job_fmly_column,
772 p_job_fnctn_column => l_job_fnctn_column);
773
774 -- Return appropriate column
775 IF (p_job_type = 'JOB_FAMILY') THEN
776 RETURN l_job_fmly_column;
777 ELSIF (p_job_type = 'JOB_FUNCTION') THEN
778 RETURN l_job_fnctn_column;
779 END IF;
780
781 -- Return column to select for invalid job type
782 RETURN '''NA_EDW''';
783
784 END get_job_flex_column;
785
786 -- ----------------------------------------------------------------------------
787 -- 3943809 This function determines the segment which stores the job family
788 -- or job function information for a ID_FLEX_NUM for the job kff.
789 -- ----------------------------------------------------------------------------
790 FUNCTION decode_keyflex_value
791 (p_id_flex_num NUMBER,
792 p_job_type VARCHAR2,
793 p_segment1 VARCHAR2,
794 p_segment2 VARCHAR2,
795 p_segment3 VARCHAR2,
796 p_segment4 VARCHAR2,
797 p_segment5 VARCHAR2,
798 p_segment6 VARCHAR2,
799 p_segment7 VARCHAR2,
800 p_segment8 VARCHAR2,
801 p_segment9 VARCHAR2,
802 p_segment10 VARCHAR2,
803 p_segment11 VARCHAR2,
804 p_segment12 VARCHAR2,
805 p_segment13 VARCHAR2,
806 p_segment14 VARCHAR2,
807 p_segment15 VARCHAR2,
808 p_segment16 VARCHAR2,
809 p_segment17 VARCHAR2,
810 p_segment18 VARCHAR2,
811 p_segment19 VARCHAR2,
812 p_segment20 VARCHAR2,
813 p_segment21 VARCHAR2,
814 p_segment22 VARCHAR2,
815 p_segment23 VARCHAR2,
816 p_segment24 VARCHAR2,
817 p_segment25 VARCHAR2,
818 p_segment26 VARCHAR2,
819 p_segment27 VARCHAR2,
820 p_segment28 VARCHAR2,
821 p_segment29 VARCHAR2,
822 p_segment30 VARCHAR2)
823 RETURN VARCHAR2 IS
824
825 l_output VARCHAR2(240);
826 l_job_segment VARCHAR2(30);
827
828 BEGIN
829
830 -- Get the segment column for the job family or function
831 l_job_segment := get_job_flex_column
832 (p_flex_type => 'KEY',
833 p_flex_code => to_char(p_id_flex_num),
834 p_job_type => p_job_type);
835
836 -- Populate the ouptut variable with the value of the segment
837 IF l_job_segment = 'SEGMENT1' THEN l_output := p_segment1;
838 ELSIF l_job_segment = 'SEGMENT2' THEN l_output := p_segment2;
839 ELSIF l_job_segment = 'SEGMENT3' THEN l_output := p_segment3;
840 ELSIF l_job_segment = 'SEGMENT4' THEN l_output := p_segment4;
841 ELSIF l_job_segment = 'SEGMENT5' THEN l_output := p_segment5;
842 ELSIF l_job_segment = 'SEGMENT6' THEN l_output := p_segment6;
843 ELSIF l_job_segment = 'SEGMENT7' THEN l_output := p_segment7;
844 ELSIF l_job_segment = 'SEGMENT8' THEN l_output := p_segment8;
845 ELSIF l_job_segment = 'SEGMENT9' THEN l_output := p_segment9;
846 ELSIF l_job_segment = 'SEGMENT10' THEN l_output := p_segment10;
847 ELSIF l_job_segment = 'SEGMENT11' THEN l_output := p_segment11;
848 ELSIF l_job_segment = 'SEGMENT12' THEN l_output := p_segment12;
849 ELSIF l_job_segment = 'SEGMENT13' THEN l_output := p_segment13;
850 ELSIF l_job_segment = 'SEGMENT14' THEN l_output := p_segment14;
851 ELSIF l_job_segment = 'SEGMENT15' THEN l_output := p_segment15;
852 ELSIF l_job_segment = 'SEGMENT16' THEN l_output := p_segment16;
853 ELSIF l_job_segment = 'SEGMENT17' THEN l_output := p_segment17;
854 ELSIF l_job_segment = 'SEGMENT18' THEN l_output := p_segment18;
855 ELSIF l_job_segment = 'SEGMENT19' THEN l_output := p_segment19;
856 ELSIF l_job_segment = 'SEGMENT20' THEN l_output := p_segment20;
857 ELSIF l_job_segment = 'SEGMENT21' THEN l_output := p_segment21;
858 ELSIF l_job_segment = 'SEGMENT22' THEN l_output := p_segment22;
859 ELSIF l_job_segment = 'SEGMENT23' THEN l_output := p_segment23;
860 ELSIF l_job_segment = 'SEGMENT24' THEN l_output := p_segment24;
861 ELSIF l_job_segment = 'SEGMENT25' THEN l_output := p_segment25;
862 ELSIF l_job_segment = 'SEGMENT26' THEN l_output := p_segment26;
863 ELSIF l_job_segment = 'SEGMENT27' THEN l_output := p_segment27;
864 ELSIF l_job_segment = 'SEGMENT28' THEN l_output := p_segment28;
865 ELSIF l_job_segment = 'SEGMENT29' THEN l_output := p_segment29;
866 ELSIF l_job_segment = 'SEGMENT30' THEN l_output := p_segment30;
867 ELSE l_output := 'NA_EDW';
868 END IF;
869
870 RETURN NVL(l_output,'NA_EDW');
871
872 END decode_keyflex_value;
873
874 -- ----------------------------------------------------------------------------
875 -- This function determines the attribute which stores the job family
876 -- or job function information for a descriptive flexfield
877 -- ----------------------------------------------------------------------------
878 FUNCTION decode_descr_flex_value
879 (p_attribute_category VARCHAR2,
880 p_job_type VARCHAR2,
881 p_attribute1 VARCHAR2,
882 p_attribute2 VARCHAR2,
883 p_attribute3 VARCHAR2,
884 p_attribute4 VARCHAR2,
885 p_attribute5 VARCHAR2,
886 p_attribute6 VARCHAR2,
887 p_attribute7 VARCHAR2,
888 p_attribute8 VARCHAR2,
889 p_attribute9 VARCHAR2,
890 p_attribute10 VARCHAR2,
891 p_attribute11 VARCHAR2,
892 p_attribute12 VARCHAR2,
893 p_attribute13 VARCHAR2,
894 p_attribute14 VARCHAR2,
895 p_attribute15 VARCHAR2,
896 p_attribute16 VARCHAR2,
897 p_attribute17 VARCHAR2,
898 p_attribute18 VARCHAR2,
899 p_attribute19 VARCHAR2,
900 p_attribute20 VARCHAR2)
901 RETURN VARCHAR2 IS
902
903 l_output VARCHAR2(240);
904 l_job_attribute VARCHAR2(30);
905 l_attribute_category VARCHAR2(30);
906
907 BEGIN
908
909 -- If no attribute category is passed use the global
910 l_attribute_category := NVL(p_attribute_category, 'NA_EDW');
911
912 -- Get the attribute column for the job type
913 l_job_attribute := get_job_flex_column
914 (p_flex_type => 'DESCRIPTIVE',
915 p_flex_code => l_attribute_category,
916 p_job_type => p_job_type);
917
918 -- Return the value of the attribute column
919 IF l_job_attribute = 'ATTRIBUTE1' THEN l_output := p_attribute1;
920 ELSIF l_job_attribute = 'ATTRIBUTE2' THEN l_output := p_attribute2;
921 ELSIF l_job_attribute = 'ATTRIBUTE3' THEN l_output := p_attribute3;
922 ELSIF l_job_attribute = 'ATTRIBUTE4' THEN l_output := p_attribute4;
923 ELSIF l_job_attribute = 'ATTRIBUTE5' THEN l_output := p_attribute5;
924 ELSIF l_job_attribute = 'ATTRIBUTE6' THEN l_output := p_attribute6;
925 ELSIF l_job_attribute = 'ATTRIBUTE7' THEN l_output := p_attribute7;
926 ELSIF l_job_attribute = 'ATTRIBUTE8' THEN l_output := p_attribute8;
927 ELSIF l_job_attribute = 'ATTRIBUTE9' THEN l_output := p_attribute9;
928 ELSIF l_job_attribute = 'ATTRIBUTE10' THEN l_output := p_attribute10;
929 ELSIF l_job_attribute = 'ATTRIBUTE11' THEN l_output := p_attribute11;
930 ELSIF l_job_attribute = 'ATTRIBUTE12' THEN l_output := p_attribute12;
931 ELSIF l_job_attribute = 'ATTRIBUTE13' THEN l_output := p_attribute13;
932 ELSIF l_job_attribute = 'ATTRIBUTE14' THEN l_output := p_attribute14;
933 ELSIF l_job_attribute = 'ATTRIBUTE15' THEN l_output := p_attribute15;
934 ELSIF l_job_attribute = 'ATTRIBUTE16' THEN l_output := p_attribute16;
935 ELSIF l_job_attribute = 'ATTRIBUTE17' THEN l_output := p_attribute17;
936 ELSIF l_job_attribute = 'ATTRIBUTE18' THEN l_output := p_attribute18;
937 ELSIF l_job_attribute = 'ATTRIBUTE19' THEN l_output := p_attribute19;
938 ELSIF l_job_attribute = 'ATTRIBUTE20' THEN l_output := p_attribute20;
939 ELSE l_output := 'NA_EDW';
940 END IF;
941
942 RETURN NVL(l_output, 'NA_EDW');
943
944 END decode_descr_flex_value;
945
946 -- Returns the column string for the function to get the values
947 -- from the appropriate flexfield columns
948 FUNCTION get_column_string(p_job_type IN VARCHAR2,
949 p_flex_type IN VARCHAR2)
950 RETURN VARCHAR2 IS
951
952 l_cnfg_column VARCHAR2(30);
953 l_column_string VARCHAR2(1000);
954
955 BEGIN
956
957 -- Formulate the return column string
958 IF (p_flex_type = 'KEY') THEN
959
960 l_column_string :=
961 'hri_opl_jobh.decode_keyflex_value
962 (pjd.id_flex_num, ''' || p_job_type || ''',
963 pjd.segment1, pjd.segment2, pjd.segment3, pjd.segment4, pjd.segment5,
964 pjd.segment6, pjd.segment7, pjd.segment8, pjd.segment9, pjd.segment10,
965 pjd.segment11, pjd.segment12, pjd.segment13, pjd.segment14, pjd.segment15,
966 pjd.segment16, pjd.segment17, pjd.segment18, pjd.segment19, pjd.segment20,
967 pjd.segment21, pjd.segment22, pjd.segment23, pjd.segment24, pjd.segment25,
968 pjd.segment26, pjd.segment27, pjd.segment28, pjd.segment29, pjd.segment30)';
969
970 ELSIF (p_flex_type = 'DESCRIPTIVE') THEN
971
972 l_column_string :=
973 'hri_opl_jobh.decode_descr_flex_value
974 (job.attribute_category, ''' || p_job_type || ''',
975 job.attribute1, job.attribute2, job.attribute3, job.attribute4, job.attribute5,
976 job.attribute6, job.attribute7, job.attribute8, job.attribute9, job.attribute10,
977 job.attribute11, job.attribute12, job.attribute13, job.attribute14, job.attribute15,
978 job.attribute16, job.attribute17, job.attribute18, job.attribute19, job.attribute20)';
979
980 ELSE
981
982 l_column_string := '''NA_EDW''';
983
984 END IF;
985
986 -- Return
987 RETURN l_column_string;
988
989 END get_column_string;
990
991 -- ----------------------------------------------------------------------------
992 -- Truncates and repopulates the job hierarchy table (full refresh mode)
993 -- Assumes the refresh_config_table procedure has already been called
994 -- ----------------------------------------------------------------------------
995 --
996 PROCEDURE collect_hierarchy_table IS
997
998 -- Values to populate WHO columns
999 l_current_time DATE := SYSDATE;
1000 l_user_id NUMBER := fnd_global.user_id;
1001 l_job_fmly_column VARCHAR2(1000);
1002 l_job_fnctn_column VARCHAR2(1000);
1003 l_sql_stmt VARCHAR2(32000);
1004
1005 BEGIN
1006
1007 -- 3601362 Disable the WHO Trigger
1008 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO DISABLE');
1009
1010 -- Truncate table
1011 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.hri_cs_jobh_ct';
1012
1013 -- Get the job family and function column strings
1014 l_job_fnctn_column := get_column_string
1015 (p_job_type => 'JOB_FUNCTION',
1016 p_flex_type => get_flexfield_type('JOB_FUNCTION'));
1017 l_job_fmly_column := get_column_string
1018 (p_job_type => 'JOB_FAMILY',
1019 p_flex_type => get_flexfield_type('JOB_FAMILY'));
1020
1021 -- Formulate the insert SQL statement
1022 l_sql_stmt :=
1023 'INSERT INTO hri_cs_jobh_ct
1024 (job_id
1025 ,job_fmly_code
1026 ,job_fnctn_code
1027 ,last_update_date
1028 ,last_update_login
1029 ,last_updated_by
1030 ,created_by
1031 ,creation_date )
1032 SELECT
1033 job.job_id job_id
1034 ,' || l_job_fmly_column || '
1035 ,' || l_job_fnctn_column || '
1036 ,:l_current_time
1037 ,:l_user_id
1038 ,:l_user_id
1039 ,:l_user_id
1040 ,:l_current_time
1041 FROM
1042 per_jobs job
1043 ,per_job_definitions pjd
1044 WHERE job.job_definition_id = pjd.job_definition_id
1045 UNION ALL
1046 SELECT
1047 -1 job_id
1048 ,''NA_EDW'' job_fmly_code
1049 ,''NA_EDW'' job_fnctn_code
1050 ,:l_current_time
1051 ,:l_user_id
1052 ,:l_user_id
1053 ,:l_user_id
1054 ,:l_current_time
1055 FROM dual';
1056
1057 -- Run insert statement
1058 EXECUTE IMMEDIATE l_sql_stmt USING
1059 l_current_time,
1060 l_user_id,
1061 l_user_id,
1062 l_user_id,
1063 l_current_time,
1064 l_current_time,
1065 l_user_id,
1066 l_user_id,
1067 l_user_id,
1068 l_current_time;
1069
1070 -- Commit
1071 COMMIT;
1072
1073 -- Gather Stats
1074 fnd_stats.gather_table_stats(g_hri_schema, 'HRI_CS_JOBH_CT');
1075
1076 -- 3601362 Enable the WHO trigger
1077 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO ENABLE');
1078
1079 END collect_hierarchy_table;
1080 --
1081 -- ----------------------------------------------------------------------------
1082 -- Incrementally refreshes the job hierarchy table (incremental refresh mode)
1083 -- ----------------------------------------------------------------------------
1084 --
1085 PROCEDURE update_hierarchy_table IS
1086
1087 -- Values to populate WHO columns
1088 l_current_time DATE := SYSDATE;
1089 l_user_id NUMBER := fnd_global.user_id;
1090
1091 -- PL/SQL table of updated job records
1092 TYPE l_number_tab_type IS TABLE OF hri_cs_jobh_ct.job_id%TYPE;
1093 l_upd_job_ids L_NUMBER_TAB_TYPE;
1094
1095 -- Variables for Dynamic SQL
1096 l_job_fmly_column VARCHAR2(1000);
1097 l_job_fnctn_column VARCHAR2(1000);
1098 l_sql_stmt VARCHAR2(32000);
1099
1100 BEGIN
1101
1102 -- Get the job family and function column strings
1103 l_job_fnctn_column := get_column_string
1104 (p_job_type => 'JOB_FUNCTION',
1105 p_flex_type => get_flexfield_type('JOB_FUNCTION'));
1106 l_job_fmly_column := get_column_string
1107 (p_job_type => 'JOB_FAMILY',
1108 p_flex_type => get_flexfield_type('JOB_FAMILY'));
1109
1110 -- Insert completely new rows
1111 l_sql_stmt :=
1112 'INSERT INTO hri_cs_jobh_ct
1113 (job_id
1114 ,job_fmly_code
1115 ,job_fnctn_code
1116 ,last_update_date
1117 ,last_update_login
1118 ,last_updated_by
1119 ,created_by
1120 ,creation_date )
1121 SELECT
1122 job.job_id job_id
1123 ,' || l_job_fmly_column || '
1124 ,' || l_job_fnctn_column || '
1125 ,:l_current_time
1126 ,:l_user_id
1127 ,:l_user_id
1128 ,:l_user_id
1129 ,:l_current_time
1130 FROM
1131 per_jobs job
1132 ,per_job_definitions pjd
1133 WHERE job.job_definition_id = pjd.job_definition_id
1134 AND NOT EXISTS
1135 (SELECT null
1136 FROM hri_cs_jobh_ct jobh
1137 WHERE jobh.job_id = job.job_id)';
1138
1139 EXECUTE IMMEDIATE l_sql_stmt USING
1140 l_current_time
1141 ,l_user_id
1142 ,l_user_id
1143 ,l_user_id
1144 ,l_current_time;
1145
1146 -- Commit changes
1147 COMMIT;
1148
1149 -- Delete rows which no longer exist
1150 -- Bug 3347127 - don't delete unassigned row
1151 DELETE FROM hri_cs_jobh_ct jobh
1152 WHERE NOT EXISTS
1153 (SELECT null
1154 FROM per_jobs job
1155 WHERE job.job_id = jobh.job_id)
1156 AND jobh.job_id <> -1;
1157
1158 -- Update changed rows
1159 -- 3943809 The job family and function values is determined by the function
1160 -- get_family_function_code based on the setup
1161 l_sql_stmt :=
1162 'UPDATE hri_cs_jobh_ct jobh
1163 SET (job_fmly_code
1164 ,job_fnctn_code) =
1165 (SELECT
1166 ' || l_job_fmly_column || '
1167 ,' || l_job_fnctn_column || '
1168 FROM
1169 per_jobs job
1170 ,per_job_definitions pjd
1171 WHERE job.job_definition_id = pjd.job_definition_id
1172 AND job.job_id = jobh.job_id)
1173 WHERE EXISTS
1174 (SELECT null
1175 FROM
1176 per_jobs job
1177 ,per_job_definitions pjd
1178 WHERE job.job_definition_id = pjd.job_definition_id
1179 AND job.job_id = jobh.job_id
1180 AND (jobh.job_fmly_code <> ' || l_job_fmly_column || '
1181 OR jobh.job_fnctn_code <> ' || l_job_fnctn_column || ')
1182 )
1183 RETURNING jobh.job_id INTO :l_upd_job_ids';
1184
1185 EXECUTE IMMEDIATE l_sql_stmt RETURNING BULK COLLECT INTO l_upd_job_ids;
1186
1187 -- Commit changes
1188 COMMIT;
1189
1190 -- If the job family and function of any of the existing records is changed then
1191 -- the corresponding changes should be refelected in the assingment delta table also
1192 -- So insert the JOB_ID of the updated records into the assingment delta table
1193 -- so that the changes can be made to the assignment delta table by the incr process
1194
1195 IF (l_upd_job_ids.LAST > 0 AND
1196 fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
1197
1198 BEGIN
1199
1200 FORALL i IN 1..l_upd_job_ids.LAST SAVE EXCEPTIONS
1201 INSERT INTO HRI_EQ_ASG_SUP_WRFC
1202 (SOURCE_TYPE,
1203 SOURCE_ID)
1204 VALUES
1205 ('JOB',
1206 l_upd_job_ids(i));
1207
1208 EXCEPTION WHEN OTHERS THEN
1209
1210 dbg(sql%bulk_exceptions.count|| ' job records already exists in the event queue ');
1211
1212 END;
1213
1214 -- Commit changes
1215 COMMIT;
1216
1217 END IF;
1218
1219 END update_hierarchy_table;
1220 --
1221 --
1222 -- ----------------------------------------------------------------------------
1223 -- Full Refresh Entry Point
1224 -- ----------------------------------------------------------------------------
1225 --
1226 PROCEDURE full_refresh IS
1227
1228 l_dummy1 VARCHAR2(2000);
1229 l_dummy2 VARCHAR2(2000);
1230
1231 BEGIN
1232
1233 -- Get HRI schema name
1234 IF (fnd_installation.get_app_info
1235 ('HRI',l_dummy1, l_dummy2, g_hri_schema)) THEN
1236
1237 -- Initialize globals
1238 g_job_fmly_vset_id := fnd_profile.value('HR_BIS_JOB_FAMILY');
1239 g_job_fnctn_vset_id := fnd_profile.value('HR_BIS_JOB_FUNCTION');
1240 g_full_refresh := 'Y';
1241
1242 -- Debug the parameters
1243 dbg('Full Refresh: ' || g_full_refresh);
1244 dbg('Job Family: ' || to_char(g_job_fmly_vset_id));
1245 dbg('Job Function: ' || to_char(g_job_fnctn_vset_id));
1246
1247 -- Reset global caches
1248 g_job_fmly_flex_type := NULL;
1249 g_job_fnctn_flex_type := NULL;
1250 job_flex_cache := job_flex_reset;
1251
1252 refresh_config_table;
1253
1254 generate_lov_views;
1255
1256 collect_hierarchy_table;
1257
1258 END IF;
1259
1260 END full_refresh;
1261
1262 -- ----------------------------------------------------------------------------
1263 -- Full Refresh Entry Point from concurrent manager
1264 -- ----------------------------------------------------------------------------
1265 PROCEDURE full_refresh(errbuf OUT NOCOPY VARCHAR2,
1266 retcode OUT NOCOPY VARCHAR2) IS
1267
1268 BEGIN
1269 full_refresh;
1270 EXCEPTION WHEN OTHERS THEN
1271 errbuf := SQLERRM;
1272 retcode := SQLCODE;
1273 RAISE;
1274 END full_refresh;
1275
1276 -- ----------------------------------------------------------------------------
1277 -- Incremental Refresh Entry Point
1278 -- ----------------------------------------------------------------------------
1279 PROCEDURE incr_refresh IS
1280
1281 BEGIN
1282
1283 -- Initialize globals
1284 g_full_refresh := 'N';
1285
1286 -- Reset global caches
1287 g_job_fmly_flex_type := NULL;
1288 g_job_fnctn_flex_type := NULL;
1289 job_flex_cache := job_flex_reset;
1290
1291 -- Debug the parameters
1292 dbg('Full Refresh: ' || g_full_refresh);
1293
1294 -- Incrementally update job hierarchy table
1295 update_hierarchy_table;
1296
1297 END incr_refresh;
1298
1299 -- ----------------------------------------------------------------------------
1300 -- Incremental Refresh Entry Point from concurrent manager
1301 -- ----------------------------------------------------------------------------
1302 PROCEDURE incr_refresh(errbuf OUT NOCOPY VARCHAR2,
1303 retcode OUT NOCOPY VARCHAR2) IS
1304
1305 BEGIN
1306 incr_refresh;
1307 EXCEPTION WHEN OTHERS THEN
1308 errbuf := SQLERRM;
1309 retcode := SQLCODE;
1310 RAISE;
1311 END incr_refresh;
1312
1313 -- ----------------------------------------------------------------------------
1314 -- Obsolete Incremental Refresh Entry point
1315 -- ----------------------------------------------------------------------------
1316 PROCEDURE incr_refresh(p_refresh_flex IN VARCHAR2) IS
1317
1318 BEGIN
1319
1320 incr_refresh;
1321
1322 END incr_refresh;
1323 END hri_opl_jobh;