[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_JOBH
Source
1 PACKAGE BODY hri_opl_jobh AS
2 /* $Header: hripjobh.pkb 120.6 2011/10/25 07:12:31 vepravee 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 (SELECT distinct job_fmly_code,job_fnctn_code 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 /*
655 Removed the following line from the dynamic sql and modified with inline view
656 || 'FROM hri_cs_jobh_ct job,' || g_rtn
657 and replace with
658 || ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
659 */
660
661 l_view_sql :=
662 'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_fmlyfnct_v
663 (fmfn_fmlyfnct_pk
664 ,fmfn_fmlyfnct_name
665 ,fmfn_fmlyfnct_name_unq
666 ,fmfn_family_fk
667 ,fmfn_family_name
668 ,fmfn_function_fk
669 ,fmfn_function_name
670 ,fmfn_order_by)
671 AS
672 ' || l_vset_lov_sql;
673
674
675 -- Log the statement in debug mode
676 dbg('About to create obi job family function view with:');
677 dbg(' ');
678 dbg(l_view_sql);
679 dbg(' ');
680
681 -- Execute view creation statement
682 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
683 application_short_name => 'APPS',
684 statement_type => ad_ddl.create_view,
685 statement => l_view_sql,
686 object_name => 'HRI_OBI_CL_JOB_FMLYFNCT_V');
687
688
689 -- Add comments to objects for eTRM
690 attach_comments;
691 --
692 END IF;
693 --
694 END generate_lov_views;
695
696 /******************************************************************************/
697 /* LOAD JOB HIERARCHY TABLE SECTION */
698 /******************************************************************************/
699
700 -- ---------------------------------------------------------------------------
701 -- Looks up the columns to use for a given flexfield structure and job type
702 -- ---------------------------------------------------------------------------
703 PROCEDURE get_job_flex_columns(p_flex_type IN VARCHAR2,
704 p_flex_code IN VARCHAR2,
705 p_job_fmly_column OUT NOCOPY VARCHAR2,
706 p_job_fnctn_column OUT NOCOPY VARCHAR2) IS
707
708 CURSOR job_segment_csr IS
709 SELECT
710 job_fmly_column
711 ,job_fnctn_column
712 FROM
713 hri_cnfg_jobh_flex_cols
714 WHERE flexfield_type = p_flex_type
715 AND job_flex_code = p_flex_code;
716
717 BEGIN
718
719 -- PL/SQL block to trap cache misses
720 BEGIN
721
722 -- Return appropriate columns from cache
723 p_job_fmly_column := job_flex_cache
724 (p_flex_type || '|' || p_flex_code).job_fmly_column;
725 p_job_fnctn_column := job_flex_cache
726 (p_flex_type || '|' || p_flex_code).job_fnctn_column;
727
728 -- Cache miss
729 EXCEPTION WHEN OTHERS THEN
730
731 -- Populate cache
732 OPEN job_segment_csr;
733 FETCH job_segment_csr INTO p_job_fmly_column, p_job_fnctn_column;
734 CLOSE job_segment_csr;
735
736 -- If no record found in DESCRIPTIVE mode then check the global
737 IF (p_flex_type = 'DESCRIPTIVE' AND
738 p_flex_code <> 'NA_EDW' AND
739 p_job_fmly_column IS NULL AND
740 p_job_fnctn_column IS NULL) THEN
741
742 -- Recursively call function to get the global values from cache
743 get_job_flex_columns
744 (p_flex_type => 'DESCRIPTIVE',
745 p_flex_code => 'NA_EDW',
746 p_job_fmly_column => p_job_fmly_column,
747 p_job_fnctn_column => p_job_fnctn_column);
748
749 END IF;
750
751 -- Populate cache with results
752 job_flex_cache(p_flex_type || '|' || p_flex_code).job_fmly_column
753 := p_job_fmly_column;
754 job_flex_cache(p_flex_type || '|' || p_flex_code).job_fnctn_column
755 := p_job_fnctn_column;
756
757 END;
758
759 END get_job_flex_columns;
760
761 -- --------------------------------------------------------------------------
762 -- Returns the flexfield column to use for the given flexfield type and code
763 -- --------------------------------------------------------------------------
764 FUNCTION get_job_flex_column(p_flex_type IN VARCHAR2,
765 p_flex_code IN VARCHAR2,
766 p_job_type IN VARCHAR2)
767 RETURN VARCHAR2 IS
768
769 l_job_fmly_column VARCHAR2(30);
770 l_job_fnctn_column VARCHAR2(30);
771
772 BEGIN
773
774 -- Call cache function to get flex columns
775 get_job_flex_columns
776 (p_flex_type => p_flex_type,
777 p_flex_code => p_flex_code,
778 p_job_fmly_column => l_job_fmly_column,
779 p_job_fnctn_column => l_job_fnctn_column);
780
781 -- Return appropriate column
782 IF (p_job_type = 'JOB_FAMILY') THEN
783 RETURN l_job_fmly_column;
784 ELSIF (p_job_type = 'JOB_FUNCTION') THEN
785 RETURN l_job_fnctn_column;
786 END IF;
787
788 -- Return column to select for invalid job type
789 RETURN '''NA_EDW''';
790
791 END get_job_flex_column;
792
793 -- ----------------------------------------------------------------------------
794 -- 3943809 This function determines the segment which stores the job family
795 -- or job function information for a ID_FLEX_NUM for the job kff.
796 -- ----------------------------------------------------------------------------
797 FUNCTION decode_keyflex_value
798 (p_id_flex_num NUMBER,
799 p_job_type VARCHAR2,
800 p_segment1 VARCHAR2,
801 p_segment2 VARCHAR2,
802 p_segment3 VARCHAR2,
803 p_segment4 VARCHAR2,
804 p_segment5 VARCHAR2,
805 p_segment6 VARCHAR2,
806 p_segment7 VARCHAR2,
807 p_segment8 VARCHAR2,
808 p_segment9 VARCHAR2,
809 p_segment10 VARCHAR2,
810 p_segment11 VARCHAR2,
811 p_segment12 VARCHAR2,
812 p_segment13 VARCHAR2,
813 p_segment14 VARCHAR2,
814 p_segment15 VARCHAR2,
815 p_segment16 VARCHAR2,
816 p_segment17 VARCHAR2,
817 p_segment18 VARCHAR2,
818 p_segment19 VARCHAR2,
819 p_segment20 VARCHAR2,
820 p_segment21 VARCHAR2,
821 p_segment22 VARCHAR2,
822 p_segment23 VARCHAR2,
823 p_segment24 VARCHAR2,
824 p_segment25 VARCHAR2,
825 p_segment26 VARCHAR2,
826 p_segment27 VARCHAR2,
827 p_segment28 VARCHAR2,
828 p_segment29 VARCHAR2,
829 p_segment30 VARCHAR2)
830 RETURN VARCHAR2 IS
831
832 l_output VARCHAR2(240);
833 l_job_segment VARCHAR2(30);
834
835 BEGIN
836
837 -- Get the segment column for the job family or function
838 l_job_segment := get_job_flex_column
839 (p_flex_type => 'KEY',
840 p_flex_code => to_char(p_id_flex_num),
841 p_job_type => p_job_type);
842
843 -- Populate the ouptut variable with the value of the segment
844 IF l_job_segment = 'SEGMENT1' THEN l_output := p_segment1;
845 ELSIF l_job_segment = 'SEGMENT2' THEN l_output := p_segment2;
846 ELSIF l_job_segment = 'SEGMENT3' THEN l_output := p_segment3;
847 ELSIF l_job_segment = 'SEGMENT4' THEN l_output := p_segment4;
848 ELSIF l_job_segment = 'SEGMENT5' THEN l_output := p_segment5;
849 ELSIF l_job_segment = 'SEGMENT6' THEN l_output := p_segment6;
850 ELSIF l_job_segment = 'SEGMENT7' THEN l_output := p_segment7;
851 ELSIF l_job_segment = 'SEGMENT8' THEN l_output := p_segment8;
852 ELSIF l_job_segment = 'SEGMENT9' THEN l_output := p_segment9;
853 ELSIF l_job_segment = 'SEGMENT10' THEN l_output := p_segment10;
854 ELSIF l_job_segment = 'SEGMENT11' THEN l_output := p_segment11;
855 ELSIF l_job_segment = 'SEGMENT12' THEN l_output := p_segment12;
856 ELSIF l_job_segment = 'SEGMENT13' THEN l_output := p_segment13;
857 ELSIF l_job_segment = 'SEGMENT14' THEN l_output := p_segment14;
858 ELSIF l_job_segment = 'SEGMENT15' THEN l_output := p_segment15;
859 ELSIF l_job_segment = 'SEGMENT16' THEN l_output := p_segment16;
860 ELSIF l_job_segment = 'SEGMENT17' THEN l_output := p_segment17;
861 ELSIF l_job_segment = 'SEGMENT18' THEN l_output := p_segment18;
862 ELSIF l_job_segment = 'SEGMENT19' THEN l_output := p_segment19;
863 ELSIF l_job_segment = 'SEGMENT20' THEN l_output := p_segment20;
864 ELSIF l_job_segment = 'SEGMENT21' THEN l_output := p_segment21;
865 ELSIF l_job_segment = 'SEGMENT22' THEN l_output := p_segment22;
866 ELSIF l_job_segment = 'SEGMENT23' THEN l_output := p_segment23;
867 ELSIF l_job_segment = 'SEGMENT24' THEN l_output := p_segment24;
868 ELSIF l_job_segment = 'SEGMENT25' THEN l_output := p_segment25;
869 ELSIF l_job_segment = 'SEGMENT26' THEN l_output := p_segment26;
870 ELSIF l_job_segment = 'SEGMENT27' THEN l_output := p_segment27;
871 ELSIF l_job_segment = 'SEGMENT28' THEN l_output := p_segment28;
872 ELSIF l_job_segment = 'SEGMENT29' THEN l_output := p_segment29;
873 ELSIF l_job_segment = 'SEGMENT30' THEN l_output := p_segment30;
874 ELSE l_output := 'NA_EDW';
875 END IF;
876
877 RETURN NVL(l_output,'NA_EDW');
878
879 END decode_keyflex_value;
880
881 -- ----------------------------------------------------------------------------
882 -- This function determines the attribute which stores the job family
883 -- or job function information for a descriptive flexfield
884 -- ----------------------------------------------------------------------------
885 FUNCTION decode_descr_flex_value
886 (p_attribute_category VARCHAR2,
887 p_job_type VARCHAR2,
888 p_attribute1 VARCHAR2,
889 p_attribute2 VARCHAR2,
890 p_attribute3 VARCHAR2,
891 p_attribute4 VARCHAR2,
892 p_attribute5 VARCHAR2,
893 p_attribute6 VARCHAR2,
894 p_attribute7 VARCHAR2,
895 p_attribute8 VARCHAR2,
896 p_attribute9 VARCHAR2,
897 p_attribute10 VARCHAR2,
898 p_attribute11 VARCHAR2,
899 p_attribute12 VARCHAR2,
900 p_attribute13 VARCHAR2,
901 p_attribute14 VARCHAR2,
902 p_attribute15 VARCHAR2,
903 p_attribute16 VARCHAR2,
904 p_attribute17 VARCHAR2,
905 p_attribute18 VARCHAR2,
906 p_attribute19 VARCHAR2,
907 p_attribute20 VARCHAR2)
908 RETURN VARCHAR2 IS
909
910 l_output VARCHAR2(240);
911 l_job_attribute VARCHAR2(30);
912 l_attribute_category VARCHAR2(30);
913
914 BEGIN
915
916 -- If no attribute category is passed use the global
917 l_attribute_category := NVL(p_attribute_category, 'NA_EDW');
918
919 -- Get the attribute column for the job type
920 l_job_attribute := get_job_flex_column
921 (p_flex_type => 'DESCRIPTIVE',
922 p_flex_code => l_attribute_category,
923 p_job_type => p_job_type);
924
925 -- Return the value of the attribute column
926 IF l_job_attribute = 'ATTRIBUTE1' THEN l_output := p_attribute1;
927 ELSIF l_job_attribute = 'ATTRIBUTE2' THEN l_output := p_attribute2;
928 ELSIF l_job_attribute = 'ATTRIBUTE3' THEN l_output := p_attribute3;
929 ELSIF l_job_attribute = 'ATTRIBUTE4' THEN l_output := p_attribute4;
930 ELSIF l_job_attribute = 'ATTRIBUTE5' THEN l_output := p_attribute5;
931 ELSIF l_job_attribute = 'ATTRIBUTE6' THEN l_output := p_attribute6;
932 ELSIF l_job_attribute = 'ATTRIBUTE7' THEN l_output := p_attribute7;
933 ELSIF l_job_attribute = 'ATTRIBUTE8' THEN l_output := p_attribute8;
934 ELSIF l_job_attribute = 'ATTRIBUTE9' THEN l_output := p_attribute9;
935 ELSIF l_job_attribute = 'ATTRIBUTE10' THEN l_output := p_attribute10;
936 ELSIF l_job_attribute = 'ATTRIBUTE11' THEN l_output := p_attribute11;
937 ELSIF l_job_attribute = 'ATTRIBUTE12' THEN l_output := p_attribute12;
938 ELSIF l_job_attribute = 'ATTRIBUTE13' THEN l_output := p_attribute13;
939 ELSIF l_job_attribute = 'ATTRIBUTE14' THEN l_output := p_attribute14;
940 ELSIF l_job_attribute = 'ATTRIBUTE15' THEN l_output := p_attribute15;
941 ELSIF l_job_attribute = 'ATTRIBUTE16' THEN l_output := p_attribute16;
942 ELSIF l_job_attribute = 'ATTRIBUTE17' THEN l_output := p_attribute17;
943 ELSIF l_job_attribute = 'ATTRIBUTE18' THEN l_output := p_attribute18;
944 ELSIF l_job_attribute = 'ATTRIBUTE19' THEN l_output := p_attribute19;
945 ELSIF l_job_attribute = 'ATTRIBUTE20' THEN l_output := p_attribute20;
946 ELSE l_output := 'NA_EDW';
947 END IF;
948
949 RETURN NVL(l_output, 'NA_EDW');
950
951 END decode_descr_flex_value;
952
953 -- Returns the column string for the function to get the values
954 -- from the appropriate flexfield columns
955 FUNCTION get_column_string(p_job_type IN VARCHAR2,
956 p_flex_type IN VARCHAR2)
957 RETURN VARCHAR2 IS
958
959 l_cnfg_column VARCHAR2(30);
960 l_column_string VARCHAR2(1000);
961
962 BEGIN
963
964 -- Formulate the return column string
965 IF (p_flex_type = 'KEY') THEN
966
967 l_column_string :=
968 'hri_opl_jobh.decode_keyflex_value
969 (pjd.id_flex_num, ''' || p_job_type || ''',
970 pjd.segment1, pjd.segment2, pjd.segment3, pjd.segment4, pjd.segment5,
971 pjd.segment6, pjd.segment7, pjd.segment8, pjd.segment9, pjd.segment10,
972 pjd.segment11, pjd.segment12, pjd.segment13, pjd.segment14, pjd.segment15,
973 pjd.segment16, pjd.segment17, pjd.segment18, pjd.segment19, pjd.segment20,
974 pjd.segment21, pjd.segment22, pjd.segment23, pjd.segment24, pjd.segment25,
975 pjd.segment26, pjd.segment27, pjd.segment28, pjd.segment29, pjd.segment30)';
976
977 ELSIF (p_flex_type = 'DESCRIPTIVE') THEN
978
979 l_column_string :=
980 'hri_opl_jobh.decode_descr_flex_value
981 (job.attribute_category, ''' || p_job_type || ''',
982 job.attribute1, job.attribute2, job.attribute3, job.attribute4, job.attribute5,
983 job.attribute6, job.attribute7, job.attribute8, job.attribute9, job.attribute10,
984 job.attribute11, job.attribute12, job.attribute13, job.attribute14, job.attribute15,
985 job.attribute16, job.attribute17, job.attribute18, job.attribute19, job.attribute20)';
986
987 ELSE
988
989 l_column_string := '''NA_EDW''';
990
991 END IF;
992
993 -- Return
994 RETURN l_column_string;
995
996 END get_column_string;
997
998 -- ----------------------------------------------------------------------------
999 -- Truncates and repopulates the job hierarchy table (full refresh mode)
1000 -- Assumes the refresh_config_table procedure has already been called
1001 -- ----------------------------------------------------------------------------
1002 --
1003 PROCEDURE collect_hierarchy_table IS
1004
1005 -- Values to populate WHO columns
1006 l_current_time DATE := SYSDATE;
1007 l_user_id NUMBER := fnd_global.user_id;
1008 l_job_fmly_column VARCHAR2(1000);
1009 l_job_fnctn_column VARCHAR2(1000);
1010 l_sql_stmt VARCHAR2(32000);
1011
1012 BEGIN
1013
1014 -- 3601362 Disable the WHO Trigger
1015 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO DISABLE');
1016
1017 -- Truncate table
1018 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.hri_cs_jobh_ct';
1019
1020 -- Get the job family and function column strings
1021 l_job_fnctn_column := get_column_string
1022 (p_job_type => 'JOB_FUNCTION',
1023 p_flex_type => get_flexfield_type('JOB_FUNCTION'));
1024 l_job_fmly_column := get_column_string
1025 (p_job_type => 'JOB_FAMILY',
1026 p_flex_type => get_flexfield_type('JOB_FAMILY'));
1027
1028 -- Formulate the insert SQL statement
1029 l_sql_stmt :=
1030 'INSERT INTO hri_cs_jobh_ct
1031 (job_id
1032 ,job_fmly_code
1033 ,job_fnctn_code
1034 ,last_update_date
1035 ,last_update_login
1036 ,last_updated_by
1037 ,created_by
1038 ,creation_date )
1039 SELECT
1040 job.job_id job_id
1041 ,' || l_job_fmly_column || '
1042 ,' || l_job_fnctn_column || '
1043 ,:l_current_time
1044 ,:l_user_id
1045 ,:l_user_id
1046 ,:l_user_id
1047 ,:l_current_time
1048 FROM
1049 per_jobs job
1050 ,per_job_definitions pjd
1051 WHERE job.job_definition_id = pjd.job_definition_id
1052 UNION ALL
1053 SELECT
1054 -1 job_id
1055 ,''NA_EDW'' job_fmly_code
1056 ,''NA_EDW'' job_fnctn_code
1057 ,:l_current_time
1058 ,:l_user_id
1059 ,:l_user_id
1060 ,:l_user_id
1061 ,:l_current_time
1062 FROM dual';
1063
1064 -- Run insert statement
1065 EXECUTE IMMEDIATE l_sql_stmt USING
1066 l_current_time,
1067 l_user_id,
1068 l_user_id,
1069 l_user_id,
1070 l_current_time,
1071 l_current_time,
1072 l_user_id,
1073 l_user_id,
1074 l_user_id,
1075 l_current_time;
1076
1077 -- Commit
1078 COMMIT;
1079
1080 -- Gather Stats
1081 fnd_stats.gather_table_stats(g_hri_schema, 'HRI_CS_JOBH_CT');
1082
1083 -- 3601362 Enable the WHO trigger
1084 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO ENABLE');
1085
1086 END collect_hierarchy_table;
1087 --
1088 -- ----------------------------------------------------------------------------
1089 -- Incrementally refreshes the job hierarchy table (incremental refresh mode)
1090 -- ----------------------------------------------------------------------------
1091 --
1092 PROCEDURE update_hierarchy_table IS
1093
1094 -- Values to populate WHO columns
1095 l_current_time DATE := SYSDATE;
1096 l_user_id NUMBER := fnd_global.user_id;
1097
1098 -- PL/SQL table of updated job records
1099 TYPE l_number_tab_type IS TABLE OF hri_cs_jobh_ct.job_id%TYPE;
1100 l_upd_job_ids L_NUMBER_TAB_TYPE;
1101
1102 -- Variables for Dynamic SQL
1103 l_job_fmly_column VARCHAR2(1000);
1104 l_job_fnctn_column VARCHAR2(1000);
1105 l_sql_stmt VARCHAR2(32000);
1106
1107 BEGIN
1108
1109 -- Get the job family and function column strings
1110 l_job_fnctn_column := get_column_string
1111 (p_job_type => 'JOB_FUNCTION',
1112 p_flex_type => get_flexfield_type('JOB_FUNCTION'));
1113 l_job_fmly_column := get_column_string
1114 (p_job_type => 'JOB_FAMILY',
1115 p_flex_type => get_flexfield_type('JOB_FAMILY'));
1116
1117 -- Insert completely new rows
1118 l_sql_stmt :=
1119 'INSERT INTO hri_cs_jobh_ct
1120 (job_id
1121 ,job_fmly_code
1122 ,job_fnctn_code
1123 ,last_update_date
1124 ,last_update_login
1125 ,last_updated_by
1126 ,created_by
1127 ,creation_date )
1128 SELECT
1129 job.job_id job_id
1130 ,' || l_job_fmly_column || '
1131 ,' || l_job_fnctn_column || '
1132 ,:l_current_time
1133 ,:l_user_id
1134 ,:l_user_id
1135 ,:l_user_id
1136 ,:l_current_time
1137 FROM
1138 per_jobs job
1139 ,per_job_definitions pjd
1140 WHERE job.job_definition_id = pjd.job_definition_id
1141 AND NOT EXISTS
1142 (SELECT null
1143 FROM hri_cs_jobh_ct jobh
1144 WHERE jobh.job_id = job.job_id)';
1145
1146 EXECUTE IMMEDIATE l_sql_stmt USING
1147 l_current_time
1148 ,l_user_id
1149 ,l_user_id
1150 ,l_user_id
1151 ,l_current_time;
1152
1153 -- Commit changes
1154 COMMIT;
1155
1156 -- Delete rows which no longer exist
1157 -- Bug 3347127 - don't delete unassigned row
1158 DELETE FROM hri_cs_jobh_ct jobh
1159 WHERE NOT EXISTS
1160 (SELECT null
1161 FROM per_jobs job
1162 WHERE job.job_id = jobh.job_id)
1163 AND jobh.job_id <> -1;
1164
1165 -- Update changed rows
1166 -- 3943809 The job family and function values is determined by the function
1167 -- get_family_function_code based on the setup
1168 l_sql_stmt :=
1169 'UPDATE hri_cs_jobh_ct jobh
1170 SET (job_fmly_code
1171 ,job_fnctn_code) =
1172 (SELECT
1173 ' || l_job_fmly_column || '
1174 ,' || l_job_fnctn_column || '
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 WHERE EXISTS
1181 (SELECT null
1182 FROM
1183 per_jobs job
1184 ,per_job_definitions pjd
1185 WHERE job.job_definition_id = pjd.job_definition_id
1186 AND job.job_id = jobh.job_id
1187 AND (jobh.job_fmly_code <> ' || l_job_fmly_column || '
1188 OR jobh.job_fnctn_code <> ' || l_job_fnctn_column || ')
1189 )
1190 RETURNING jobh.job_id INTO :l_upd_job_ids';
1191
1192 EXECUTE IMMEDIATE l_sql_stmt RETURNING BULK COLLECT INTO l_upd_job_ids;
1193
1194 -- Commit changes
1195 COMMIT;
1196
1197 -- If the job family and function of any of the existing records is changed then
1198 -- the corresponding changes should be refelected in the assingment delta table also
1199 -- So insert the JOB_ID of the updated records into the assingment delta table
1200 -- so that the changes can be made to the assignment delta table by the incr process
1201
1202 IF (l_upd_job_ids.LAST > 0 AND
1203 fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
1204
1205 BEGIN
1206
1207 FORALL i IN 1..l_upd_job_ids.LAST SAVE EXCEPTIONS
1208 INSERT INTO HRI_EQ_ASG_SUP_WRFC
1209 (SOURCE_TYPE,
1210 SOURCE_ID)
1211 VALUES
1212 ('JOB',
1213 l_upd_job_ids(i));
1214
1215 EXCEPTION WHEN OTHERS THEN
1216
1217 dbg(sql%bulk_exceptions.count|| ' job records already exists in the event queue ');
1218
1219 END;
1220
1221 -- Commit changes
1222 COMMIT;
1223
1224 END IF;
1225
1226 END update_hierarchy_table;
1227 --
1228 --
1229 -- ----------------------------------------------------------------------------
1230 -- Full Refresh Entry Point
1231 -- ----------------------------------------------------------------------------
1232 --
1233 PROCEDURE full_refresh IS
1234
1235 l_dummy1 VARCHAR2(2000);
1236 l_dummy2 VARCHAR2(2000);
1237
1238 BEGIN
1239
1240 -- Get HRI schema name
1241 IF (fnd_installation.get_app_info
1242 ('HRI',l_dummy1, l_dummy2, g_hri_schema)) THEN
1243
1244 -- Initialize globals
1245 g_job_fmly_vset_id := fnd_profile.value('HR_BIS_JOB_FAMILY');
1246 g_job_fnctn_vset_id := fnd_profile.value('HR_BIS_JOB_FUNCTION');
1247 g_full_refresh := 'Y';
1248
1249 -- Debug the parameters
1250 dbg('Full Refresh: ' || g_full_refresh);
1251 dbg('Job Family: ' || to_char(g_job_fmly_vset_id));
1252 dbg('Job Function: ' || to_char(g_job_fnctn_vset_id));
1253
1254 -- Reset global caches
1255 g_job_fmly_flex_type := NULL;
1256 g_job_fnctn_flex_type := NULL;
1257 job_flex_cache := job_flex_reset;
1258
1259 refresh_config_table;
1260
1261 generate_lov_views;
1262
1263 collect_hierarchy_table;
1264
1265 END IF;
1266
1267 END full_refresh;
1268
1269 -- ----------------------------------------------------------------------------
1270 -- Full Refresh Entry Point from concurrent manager
1271 -- ----------------------------------------------------------------------------
1272 PROCEDURE full_refresh(errbuf OUT NOCOPY VARCHAR2,
1273 retcode OUT NOCOPY VARCHAR2) IS
1274
1275 BEGIN
1276 full_refresh;
1277 EXCEPTION WHEN OTHERS THEN
1278 errbuf := SQLERRM;
1279 retcode := SQLCODE;
1280 RAISE;
1281 END full_refresh;
1282
1283 -- ----------------------------------------------------------------------------
1284 -- Incremental Refresh Entry Point
1285 -- ----------------------------------------------------------------------------
1286 PROCEDURE incr_refresh IS
1287
1288 BEGIN
1289
1290 -- Initialize globals
1291 g_full_refresh := 'N';
1292
1293 -- Reset global caches
1294 g_job_fmly_flex_type := NULL;
1295 g_job_fnctn_flex_type := NULL;
1296 job_flex_cache := job_flex_reset;
1297
1298 -- Debug the parameters
1299 dbg('Full Refresh: ' || g_full_refresh);
1300
1301 -- Incrementally update job hierarchy table
1302 update_hierarchy_table;
1303
1304 END incr_refresh;
1305
1306 -- ----------------------------------------------------------------------------
1307 -- Incremental Refresh Entry Point from concurrent manager
1308 -- ----------------------------------------------------------------------------
1309 PROCEDURE incr_refresh(errbuf OUT NOCOPY VARCHAR2,
1310 retcode OUT NOCOPY VARCHAR2) IS
1311
1312 BEGIN
1313 incr_refresh;
1314 EXCEPTION WHEN OTHERS THEN
1315 errbuf := SQLERRM;
1316 retcode := SQLCODE;
1317 RAISE;
1318 END incr_refresh;
1319
1320 -- ----------------------------------------------------------------------------
1321 -- Obsolete Incremental Refresh Entry point
1322 -- ----------------------------------------------------------------------------
1323 PROCEDURE incr_refresh(p_refresh_flex IN VARCHAR2) IS
1324
1325 BEGIN
1326
1327 incr_refresh;
1328
1329 END incr_refresh;
1330 END hri_opl_jobh;