1 PACKAGE BODY HRI_BPL_PERSON_TYPE AS
2 /* $Header: hribptu.pkb 120.3 2005/08/23 05:57:14 jtitmas noship $ */
3
4 -- DBI person type categorization
5 TYPE g_wkth_cat_rec_type IS RECORD
6 (wkth_wktyp_sk_fk VARCHAR2(240)
7 ,wkth_lvl1_sk_fk VARCHAR2(240)
8 ,wkth_lvl2_sk_fk VARCHAR2(240)
9 ,wkth_wktyp_code VARCHAR2(240)
10 ,wkth_lvl1_code VARCHAR2(240)
11 ,wkth_lvl2_code VARCHAR2(240)
12 ,include_flag VARCHAR2(30));
13
14 TYPE g_wkth_base_rec_type IS RECORD
15 (person_type_id NUMBER
16 ,system_person_type VARCHAR2(30)
17 ,user_person_type VARCHAR2(240)
18 ,business_group_id NUMBER
19 ,primary_flag VARCHAR2(30)
20 ,employment_category VARCHAR2(30)
21 ,assignment_type VARCHAR2(30));
22
23 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
24
25 TYPE g_varchar240_tab_type IS TABLE OF NUMBER INDEX BY VARCHAR2(240);
26
27 TYPE g_wkth_cat_tab_type IS TABLE OF g_wkth_cat_rec_type INDEX BY BINARY_INTEGER;
28
29 -- Cache tables for DBI person type dimension
30 g_cache_wkth_base_rec g_wkth_base_rec_type;
31 g_cache_wkth_cat_rec g_wkth_cat_rec_type;
32 g_cache_formula_ids g_number_tab_type;
33 g_cache_prsntyp_sk g_varchar240_tab_type;
34 g_cache_wkth_values g_wkth_cat_tab_type;
35
36 -- globals to cache the person type information for performance.
37 g_user_person_type per_person_types_tl.user_person_type%type;
38 g_person_id per_all_people_f.person_id%type;
39 g_effective_date date;
40 g_EMP_person_type per_person_types_tl.user_person_type%type;
41 g_CWK_person_type per_person_types_tl.user_person_type%type;
42 g_APL_person_type per_person_types_tl.user_person_type%type;
43 g_EXEMP_person_type per_person_types_tl.user_person_type%type;
44 g_EXCWK_person_type per_person_types_tl.user_person_type%type;
45 g_EXAPL_person_type per_person_types_tl.user_person_type%type;
46 g_OTHER_person_type per_person_types_tl.user_person_type%type;
47 g_CONCAT_person_type varchar2(4000);
48
49 -- constants.
50 c_separator varchar2(1) := '.';
51 --
52 -- -----------------------------------------------------------------------------
53 -- Global Variables required by invoking the CATEGORIZE_PERSON_TYPE fast formula
54 -- -----------------------------------------------------------------------------
55 --
56 -- Global Parameter for caching setup business group id
57 --
58 g_debug_flag VARCHAR2(1) := NVL(fnd_profile.value('HRI_ENBL_DTL_LOG'),'N');
59 g_concurrent_flag VARCHAR2(1);
60 g_review_ff_id NUMBER;
61 c_prsn_type_ff_name CONSTANT VARCHAR2(30) := 'CATEGORIZE_PERSON_TYPE';
62 --
63 -- Type of caching record to store the output of fast formula,
64 -- By using the outputs in this records, the number of fast formula
65 -- calls will reduce
66 --
67 TYPE ff_output_rec IS RECORD
68 (used_for_summarization VARCHAR2(30)
69 ,person_type_category VARCHAR2(30));
70 --
71 TYPE g_ff_ouptut_tab_type IS TABLE OF ff_output_rec INDEX BY BINARY_INTEGER;
72 --
73 g_per_typ_cache g_ff_ouptut_tab_type;
74 --
75 -- -----------------------------------------------------------------------------
76 --
77 PROCEDURE UPDATE_PERSON_TYPE_GLOBALS(
78 p_effective_date IN DATE
79 ,p_person_id IN NUMBER)
80 IS
81
82 -- bug 2820666, added support for 'CWK' segments in person type.
83 CURSOR csr_person_types
84 IS
85 SELECT ttl.user_person_type
86 ,DECODE(typ.system_person_type
87 ,'EMP' ,1
88 ,'CWK' ,2
89 ,'APL' ,3
90 ,'EX_EMP',4
91 ,'EX_CWK',5
92 ,'EX_APL',6
93 ,7) order_by
94 ,DECODE(typ.system_person_type
95 ,'EMP'
96 ,ttl.user_person_type
97 ,NULL ) EMP_PERSON_TYPE
98 ,DECODE(typ.system_person_type
99 ,'CWK'
100 ,ttl.user_person_type
101 ,NULL ) CWK_PERSON_TYPE
102 ,DECODE(typ.system_person_type
103 ,'APL'
104 ,ttl.user_person_type
105 ,NULL ) APL_PERSON_TYPE
106 ,DECODE(typ.system_person_type
107 ,'EX_EMP'
108 ,ttl.user_person_type
109 ,NULL ) EXEMP_PERSON_TYPE
110 ,DECODE(typ.system_person_type
111 ,'EX_CWK'
112 ,ttl.user_person_type
113 ,NULL ) EXCWK_PERSON_TYPE
114 ,DECODE(typ.system_person_type
115 ,'EX_APL'
116 ,ttl.user_person_type
117 ,NULL ) EXAPL_PERSON_TYPE
118 ,DECODE(typ.system_person_type
119 ,'OTHER'
120 ,ttl.user_person_type
121 ,NULL ) OTHER_PERSON_TYPE
122 FROM per_person_types_tl ttl
123 ,per_person_types typ
124 ,per_person_type_usages_f ptu
125 WHERE ttl.language = userenv('LANG')
126 AND ttl.person_type_id = typ.person_type_id
127 AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
128 AND typ.person_type_id = ptu.person_type_id
129 AND p_effective_date BETWEEN ptu.effective_start_date
130 AND ptu.effective_end_date
131 AND ptu.person_id = p_person_id
132 ORDER BY DECODE(typ.system_person_type
133 ,'EMP' ,1
134 ,'CWK' ,2
135 ,'APL' ,3
136 ,'EX_EMP',4
137 ,'EX_CWK',5
138 ,'EX_APL',6
139 ,7
140 );
141
142 l_user_concat_person_type varchar2(4000);
143
144 BEGIN
145
146 FOR l_user_person_rec in csr_person_types
147 LOOP
148 IF (l_user_concat_person_type IS NULL)
149 THEN
150 l_user_concat_person_type := l_user_person_rec.user_person_type;
151
152 -- update the global individual person type segements.
153 g_emp_person_type := l_user_person_rec.EMP_PERSON_TYPE;
154 g_exemp_person_type := l_user_person_rec.EXEMP_PERSON_TYPE;
155 g_apl_person_type := l_user_person_rec.APL_PERSON_TYPE;
156 g_exapl_person_type := l_user_person_rec.EXAPL_PERSON_TYPE;
157 g_cwk_person_type := l_user_person_rec.CWK_PERSON_TYPE;
158 g_excwk_person_type := l_user_person_rec.EXCWK_PERSON_TYPE;
159
160 ELSE
161
162 IF g_exemp_person_type IS NULL THEN
163 g_exemp_person_type := l_user_person_rec.EXEMP_PERSON_TYPE;
164 END IF;
165
166 IF g_apl_person_type IS NULL THEN
167 g_apl_person_type := l_user_person_rec.APL_PERSON_TYPE;
168 END IF;
169
170 IF g_exapl_person_type IS NULL THEN
171 g_exapl_person_type := l_user_person_rec.EXAPL_PERSON_TYPE;
172 END IF;
173
174 -- append the person types to the concatenated string.
175 begin
176 l_user_concat_person_type := l_user_concat_person_type
177 || c_separator
178 || l_user_person_rec.user_person_type;
179 exception
180 when others then
181 l_user_concat_person_type := '';
182 end;
183
184 END IF;
185 END LOOP;
186
187 -- update the cache.
188 g_concat_person_type := l_user_concat_person_type;
189 g_effective_date := p_effective_date;
190 g_person_id := p_person_id;
191
192
193 END UPDATE_PERSON_TYPE_GLOBALS;
194
195
196
197 FUNCTION GET_EMP_USER_PERSON_TYPE
198 (p_effective_date IN DATE
199 ,p_person_id IN NUMBER
200 )
201 RETURN VARCHAR2
202 IS
203
204
205 BEGIN
206
207 IF (g_person_id = p_person_id
208 AND g_effective_date = p_effective_date) THEN
209 -- cache hit, already have the user person types cached
210 RETURN NVL(g_EMP_person_type, g_exemp_person_type);
211
212 ELSE
213 -- cache miss, get the user person type[s] from translation table.
214 UPDATE_PERSON_TYPE_GLOBALS
215 (p_effective_date => p_effective_date
216 ,p_person_id => p_person_id
217 );
218
219
220 RETURN NVL(g_EMP_person_type,g_exemp_person_type) ;
221
222 END IF;
223
224 END GET_EMP_USER_PERSON_TYPE;
225
226
227 FUNCTION GET_APL_USER_PERSON_TYPE
228 (p_effective_date IN DATE
229 ,p_person_id IN NUMBER
230 )
231 RETURN VARCHAR2
232 IS
233
234
235 BEGIN
236
237 IF (g_person_id = p_person_id
238 AND g_effective_date = p_effective_date) THEN
239 -- cache hit, already have the user person types cached
240 RETURN NVL(g_APL_person_type,g_exAPL_person_type);
241 ELSE
242 -- cache miss, get the user person type[s] from translation table.
243 UPDATE_PERSON_TYPE_GLOBALS
244 (p_effective_date => p_effective_date
245 ,p_person_id => p_person_id
246 );
247
248 RETURN NVL(g_APL_person_type,g_exAPL_person_type);
249
250 END IF;
251
252 END GET_APL_USER_PERSON_TYPE;
253
254 FUNCTION GET_CWK_USER_PERSON_TYPE
255 (p_effective_date IN DATE
256 ,p_person_id IN NUMBER
257 )
258 RETURN VARCHAR2
259 IS
260
261
262 BEGIN
263
264 IF (g_person_id = p_person_id
265 AND g_effective_date = p_effective_date) THEN
266 -- cache hit, already have the user person types cached
267 RETURN NVL(g_CWK_person_type, g_excwk_person_type);
268
269 ELSE
270 -- cache miss, get the user person type[s] from translation table.
271 UPDATE_PERSON_TYPE_GLOBALS
272 (p_effective_date => p_effective_date
273 ,p_person_id => p_person_id
274 );
275
276
277 RETURN NVL(g_cwk_person_type,g_excwk_person_type) ;
278
279 END IF;
280
281 END GET_CWK_USER_PERSON_TYPE;
282
283
284 FUNCTION GET_CONCAT_USER_PERSON_TYPE
285 (p_effective_date IN DATE
286 ,p_person_id IN NUMBER
287 )
288 RETURN VARCHAR2
289 IS
290
291
292 BEGIN
293
294 IF (g_person_id = p_person_id
295 AND g_effective_date = p_effective_date) THEN
296 -- cache hit, already have the user person types cached
297 RETURN g_CONCAT_person_type;
298
299 ELSE
300 -- cache miss, get the user person type[s] from translation table.
301 UPDATE_PERSON_TYPE_GLOBALS
302 (p_effective_date => p_effective_date
303 ,p_person_id => p_person_id
304 );
305
306 RETURN g_CONCAT_person_type;
307
308 END IF;
309
310 END GET_CONCAT_USER_PERSON_TYPE;
311
312 FUNCTION get_emp_system_type(p_effective_date IN DATE,
313 p_person_id IN NUMBER)
314 RETURN VARCHAR2 IS
315
316 CURSOR emp_type_csr IS
317 SELECT
318 ppt.system_person_type
319 FROM
320 per_person_types ppt
321 ,per_person_type_usages_f ptu
322 WHERE ptu.person_id = p_person_id
323 AND p_effective_date
324 BETWEEN ptu.effective_start_date AND ptu.effective_end_date
325 AND ptu.person_type_id = ppt.person_type_id
326 AND ppt.system_person_type IN ('EMP','EX_EMP')
327 ORDER BY DECODE(ppt.system_person_type,'EMP',1,2);
328
329 l_return_type VARCHAR2(30);
330
331 BEGIN
332
333 OPEN emp_type_csr;
334 FETCH emp_type_csr INTO l_return_type;
335 CLOSE emp_type_csr;
336
337 RETURN l_return_type;
338
339 END get_emp_system_type;
340 --
341 -- -----------------------------------------------------------------------------
342 -- 3829100 Routines added for CATEGORIZE_PERSON_TYPE fast formula
343 -- -----------------------------------------------------------------------------
344 --
345 -- -----------------------------------------------------------------------------
346 -- Inserts row into concurrent program log
347 -- -----------------------------------------------------------------------------
348 --
349 PROCEDURE output(p_text VARCHAR2) IS
350 --
351 BEGIN
352 --
353 -- Bug 4105868: Collection Diagnostics
354 --
355 HRI_BPL_CONC_LOG.output(p_text);
356 --
357 END output;
358 --
359 -- -----------------------------------------------------------------------------
360 -- Inserts row into concurrent program log if debugging is enabled
361 -- -----------------------------------------------------------------------------
362 --
363 PROCEDURE dbg(p_text VARCHAR2) IS
364 --
365 BEGIN
366 --
367 -- Bug 4105868: Collection Diagnostics
368 --
369 HRI_BPL_CONC_LOG.dbg(p_text);
370 --
371 END dbg;
372 --
373 -- -----------------------------------------------------------------------------
374 -- Procedure to check if the fast formula for a business group on the given date
375 -- exists and is compiled
376 -- -----------------------------------------------------------------------------
377 --
378 FUNCTION ff_exists_and_compiled(p_business_group_id IN NUMBER
379 ,p_date IN DATE
380 ,p_ff_name IN VARCHAR2)
381 RETURN NUMBER
382 IS
383 --
384 -- Cursor to fetch peformance rating fast formula
385 --
386 CURSOR c_perf_formula IS
387 SELECT formula_id
388 FROM ff_formulas_f
389 WHERE business_group_id = p_business_group_id
390 AND p_date BETWEEN effective_start_date
391 AND effective_end_date
392 AND formula_name = p_ff_name;
393 --
394 l_ff_id NUMBER;
395 --
396 BEGIN
397 --
398 -- Check if the fast fromula exists
399 --
400 OPEN c_perf_formula;
401 FETCH c_perf_formula INTO l_ff_id;
402 CLOSE c_perf_formula;
403 --
404 -- If the fast formula is not available then return null
405 --
406 IF l_ff_id IS NULL THEN
407 --
408 RETURN NULL;
409 --
410 END IF;
411 --
412 hri_bpl_abv.CheckFastFormulaCompiled(p_formula_id => l_ff_id,
413 p_bgttyp => p_business_group_id);
414 --
415 dbg('formula id ='||l_ff_id);
416 --
417 -- If no exception is raised then return the fast formula
418 --
419 RETURN l_ff_id;
420 --
421 EXCEPTION
422 --
423 -- Handling the case when the fast formula is not compiled
424 --
425 WHEN hri_bpl_abv.ff_not_compiled THEN
426 --
427 RAISE;
428 --
429 END ff_exists_and_compiled;
430 --
431 -- -----------------------------------------------------------------------------
432 -- GET_EXCLUSION_FF
433 -- This function returns the fast formula_id of the formula NORMALIZE_REVIEW_RATING
434 -- This formula should be created in setup business group and is used for
435 -- determining the normalizing the rating give to a performance review
436 -- -----------------------------------------------------------------------------
437 --
438 FUNCTION get_person_typ_ff_id
439 RETURN NUMBER IS
440 --
441 l_message fnd_new_messages.message_text%TYPE;
442 --
443 BEGIN
444 --
445 -- Check if the formula_id is already cached, or else determine the ff_id
446 -- The NORMALIZE_REVIEW_RATING formula should always be created in the Setup
447 -- business group
448 --
449 IF g_review_ff_id is null THEN
450 --
451 g_review_ff_id := ff_exists_and_compiled
452 (p_business_group_id => 0
453 ,p_date => trunc(SYSDATE)
454 ,p_ff_name => c_prsn_type_ff_name);
455 --
456 IF g_review_ff_id IS NULL AND
457 g_warning_flag IS NULL THEN
458 --
459 g_warning_flag := 'Y';
460 --
461 -- Bug 4105868: : Collection Diagnostics
462 --
463 fnd_message.set_name('HRI', 'HRI_407291_FF_NOT_DFND_IN_BG');
464 fnd_message.set_token('FF_NAME', c_prsn_type_ff_name );
465 fnd_message.set_token('PERSON_TYPE_CATEGORY'
466 ,hr_bis.bis_decode_lookup('HRI_PERSON_TYPE_CATEGORY'
467 ,'NA_EDW'
468 )
469 );
470 --
471 l_message := fnd_message.get;
472 --
473 hri_bpl_conc_log.log_process_info
474 (p_msg_type => 'WARNING'
475 ,p_note => l_message
476 ,p_package_name => 'HRI_BPL_PERSON_TYPE'
477 ,p_msg_sub_group => 'GET_PERSON_TYP_FF_ID'
478 ,p_sql_err_code => SQLCODE
479 ,p_msg_group => 'BPL_PRSN_TYPE'
480 );
481 --
482 output(l_message);
483 --
484 -- output('WARNING! The fast formula '||c_prsn_type_ff_name ||
485 -- ' is not defined in business_group_id = 0 '||
486 -- ', all person types will be categorized as '||
487 -- hr_bis.bis_decode_lookup('HRI_PERSON_TYPE_CATEGORY','NA_EDW') );
488 --
489 RETURN g_review_ff_id;
490 --
491 END IF;
492 --
493 END IF;
494 --
495 RETURN g_review_ff_id;
496 --
497 END get_person_typ_ff_id;
498
499 /******************************************************************************/
500 /* DBI Person Type dimension */
501 /******************************************************************************/
502
503 -- -------------------------------------------------------------
504 -- Gets fast formula id for HRI_MAP_WORKER_TYPE formula
505 -- -------------------------------------------------------------
506 FUNCTION get_worker_cat_ff_id
507 (p_business_group_id IN NUMBER)
508 RETURN NUMBER IS
509
510 -- Finds HRI_MAP_WORKER_TYPE formula for given business group first
511 -- If that doesn't exist then finds a global formula (Setup BG)
512 CURSOR worker_cat_ff_csr IS
513 SELECT fff.formula_id
514 FROM
515 ff_formulas_f fff
516 ,ff_formula_types fft
517 WHERE fft.formula_type_name = 'QuickPaint'
518 AND trunc(sysdate) between fff.effective_start_date AND fff.effective_end_date
519 AND fff.formula_type_id = fft.formula_type_id
520 AND fff.formula_name = 'HRI_MAP_WORKER_TYPE'
521 AND fff.business_group_id IN (p_business_group_id,0)
522 ORDER BY
523 fff.business_group_id DESC;
524
525 l_formula_id NUMBER;
526
527 BEGIN
528
529 -- See if formula id is already stored in cache
530 BEGIN
531 l_formula_id := g_cache_formula_ids(p_business_group_id);
532
533 -- If cache miss then get formula id from cursor
534 EXCEPTION WHEN OTHERS THEN
535 OPEN worker_cat_ff_csr;
536 FETCH worker_cat_ff_csr INTO l_formula_id;
537 CLOSE worker_cat_ff_csr;
538 g_cache_formula_ids(p_business_group_id) := l_formula_id;
539 END;
540
541 RETURN l_formula_id;
542
543 END get_worker_cat_ff_id;
544
545 -- -------------------------------------------------------------
546 -- Returns worker type hierarchy for given worker type record
547 -- If there is an appropriate fast formula it is run otherwise
548 -- default logic is used to categorize the record
549 -- -------------------------------------------------------------
550 FUNCTION run_worker_cat_ff
551 (p_wkth_base_rec IN g_wkth_base_rec_type)
552 RETURN g_wkth_cat_rec_type IS
553
554 l_formula_id NUMBER;
555 l_wkth_cat_rec g_wkth_cat_rec_type;
556 l_inputs FF_EXEC.INPUTS_T;
557 l_outputs FF_EXEC.OUTPUTS_T;
558 l_wktyp_code VARCHAR2(30);
559
560 BEGIN
561
562 -- Get the fast formula id to run
563 l_formula_id := get_worker_cat_ff_id(p_wkth_base_rec.business_group_id);
564
565 -- Initialize worker type code based on person type
566 -- Default include flag
567 IF (p_wkth_base_rec.system_person_type = 'EMP' OR
568 p_wkth_base_rec.system_person_type = 'CWK') THEN
569 l_wkth_cat_rec.include_flag := 'Y';
570 l_wktyp_code := p_wkth_base_rec.system_person_type;
571 ELSE
572 l_wkth_cat_rec.include_flag := 'N';
573 l_wktyp_code := 'NA_EDW';
574 END IF;
575
576 -- If a formula is returned then run it
577 IF (l_formula_id IS NOT NULL) THEN
578
579 -- Run FF procedure initialization
580 FF_Exec.Init_Formula
581 (l_formula_id,
582 SYSDATE,
583 l_inputs,
584 l_outputs);
585
586 -- Populate input array
587 IF l_inputs.count > 0 THEN
588
589 FOR l_loop_count in l_inputs.first..l_inputs.last LOOP
590
591 IF l_inputs(l_loop_count).name = 'DATE_EARNED' THEN
592 l_inputs(l_loop_count).value := fnd_date.date_to_canonical(SYSDATE);
593 ELSIF upper(l_inputs(l_loop_count).name) = 'ASSIGNMENT_ID' THEN
594 l_inputs(l_loop_count).value := -1;
595 ELSIF upper(l_inputs(l_loop_count).name) = 'SYSTEM_PERSON_TYPE' THEN
596 l_inputs(l_loop_count).value := p_wkth_base_rec.system_person_type;
597 ELSIF upper(l_inputs(l_loop_count).name) = 'USER_PERSON_TYPE' THEN
598 l_inputs(l_loop_count).value := p_wkth_base_rec.user_person_type;
599 ELSIF upper(l_inputs(l_loop_count).name) = 'EMPLOYMENT_CATEGORY' THEN
600 l_inputs(l_loop_count).value := p_wkth_base_rec.employment_category;
601 ELSIF upper(l_inputs(l_loop_count).name) = 'PRIMARY_FLAG' THEN
602 l_inputs(l_loop_count).value := p_wkth_base_rec.primary_flag;
603 ELSIF upper(l_inputs(l_loop_count).name) = 'ASSIGNMENT_TYPE' THEN
604 l_inputs(l_loop_count).value := p_wkth_base_rec.assignment_type;
605 END IF;
606
607 END LOOP;
608
609 END IF;
610
611 -- Execute formula
612 FF_Exec.Run_Formula
613 (l_inputs,
614 l_outputs);
615
616 -- Get results from output array
617 IF l_outputs.count > 0 THEN
618
619 FOR l_loop_count in l_outputs.first..l_outputs.last LOOP
620
621 IF upper(l_outputs(l_loop_count).name) = 'INCLUDE_IN_REPORTS' THEN
622 l_wkth_cat_rec.include_flag := l_outputs(l_loop_count).value;
623 ELSIF upper(l_outputs(l_loop_count).name) = 'WORKER_TYPE_LVL1' THEN
624 l_wkth_cat_rec.wkth_lvl1_code := l_outputs(l_loop_count).value;
625 ELSIF upper(l_outputs(l_loop_count).name) = 'WORKER_TYPE_LVL2' THEN
626 l_wkth_cat_rec.wkth_lvl2_code := l_outputs(l_loop_count).value;
627 END IF;
628
629 END LOOP;
630
631 -- Compose surrogate keys for levels
632 l_wkth_cat_rec.wkth_lvl1_sk_fk := l_wktyp_code || '-' ||
633 l_wkth_cat_rec.wkth_lvl1_code;
634
635 l_wkth_cat_rec.wkth_lvl2_sk_fk := l_wkth_cat_rec.wkth_lvl1_sk_fk || '-' ||
636 l_wkth_cat_rec.wkth_lvl2_code;
637
638 END IF;
639
640 ELSE
641
642 -- Populate return record with default hierarchy information
643 l_wkth_cat_rec.wkth_lvl1_sk_fk := l_wktyp_code || '-NA_EDW';
644 l_wkth_cat_rec.wkth_lvl1_code := 'NA_EDW';
645 l_wkth_cat_rec.wkth_lvl2_sk_fk := l_wktyp_code || '-NA_EDW-NA_EDW';
646 l_wkth_cat_rec.wkth_lvl2_code := 'NA_EDW';
647
648 END IF;
649
650 RETURN l_wkth_cat_rec;
651
652 END run_worker_cat_ff;
653
654 -- --------------------------------------------------------------
655 -- Gets the worker type hierarchy information for the base record
656 -- by running the fast formula
657 -- --------------------------------------------------------------
658 FUNCTION cache_wkth_categories
659 (p_wkth_base_rec IN g_wkth_base_rec_type)
660 RETURN g_wkth_cat_rec_type IS
661
662 BEGIN
663
664 -- Check whether there is a cache hit
665 IF (p_wkth_base_rec.person_type_id = g_cache_wkth_base_rec.person_type_id AND
666 p_wkth_base_rec.system_person_type = g_cache_wkth_base_rec.system_person_type AND
667 p_wkth_base_rec.user_person_type = g_cache_wkth_base_rec.user_person_type AND
668 p_wkth_base_rec.business_group_id = g_cache_wkth_base_rec.business_group_id AND
669 p_wkth_base_rec.primary_flag = g_cache_wkth_base_rec.primary_flag AND
670 p_wkth_base_rec.employment_category = g_cache_wkth_base_rec.employment_category AND
671 p_wkth_base_rec.assignment_type = g_cache_wkth_base_rec.assignment_type) THEN
672 RETURN g_cache_wkth_cat_rec;
673 END IF;
674
675 -- No cache hit - populate the cache
676 g_cache_wkth_base_rec := p_wkth_base_rec;
677 g_cache_wkth_cat_rec := run_worker_cat_ff(p_wkth_base_rec);
678
679 RETURN g_cache_wkth_cat_rec;
680
681 END cache_wkth_categories;
682
683 -- --------------------------------------------------------------
684 -- Gets the worker type hierarchy information from the collected
685 -- table for the given surrogate key
686 -- --------------------------------------------------------------
687 PROCEDURE cache_wkth_values(p_prsntyp_sk_pk IN NUMBER) IS
688
689 -- Get hierarchy columns from the table
690 CURSOR wkth_values_csr IS
691 SELECT
692 wkth_wktyp_sk_fk
693 ,wkth_wktyp_code
694 ,wkth_lvl1_sk_fk
695 ,wkth_lvl1_code
696 ,wkth_lvl2_sk_fk
697 ,wkth_lvl2_code
698 FROM
699 hri_cs_prsntyp_ct
700 WHERE prsntyp_sk_pk = p_prsntyp_sk_pk;
701
702 l_wkth_wktyp_sk_fk VARCHAR2(240);
703 l_wkth_wktyp_code VARCHAR2(240);
704 l_wkth_lvl1_sk_fk VARCHAR2(240);
705 l_wkth_lvl1_code VARCHAR2(240);
706 l_wkth_lvl2_sk_fk VARCHAR2(240);
707 l_wkth_lvl2_code VARCHAR2(240);
708
709 BEGIN
710
711 -- Populate local variables from cursor
712 OPEN wkth_values_csr;
713 FETCH wkth_values_csr INTO
714 l_wkth_wktyp_sk_fk,
715 l_wkth_wktyp_code,
716 l_wkth_lvl1_sk_fk,
717 l_wkth_lvl1_code,
718 l_wkth_lvl2_sk_fk,
719 l_wkth_lvl2_code;
720 CLOSE wkth_values_csr;
721
722 -- Populate cache
723 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_wktyp_sk_fk := l_wkth_wktyp_sk_fk;
724 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_wktyp_code := l_wkth_wktyp_code;
725 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_lvl1_sk_fk := l_wkth_lvl1_sk_fk;
726 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_lvl1_code := l_wkth_lvl1_code;
727 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_lvl2_sk_fk := l_wkth_lvl2_sk_fk;
728 g_cache_wkth_values(p_prsntyp_sk_pk).wkth_lvl2_code := l_wkth_lvl2_code;
729
730 END cache_wkth_values;
731
732 -- --------------------------------------------------------------
733 -- Returns single column value for given base record by running
734 -- the fast formula
735 -- --------------------------------------------------------------
736 FUNCTION get_wkth_lvl1_sk_fk
737 (p_person_type_id IN NUMBER
738 ,p_system_person_type IN VARCHAR2
739 ,p_user_person_type IN VARCHAR2
740 ,p_business_group_id IN NUMBER
741 ,p_primary_flag IN VARCHAR2
742 ,p_employment_category IN VARCHAR2
743 ,p_assignment_type IN VARCHAR2)
744 RETURN VARCHAR2 IS
745
746 l_wkth_base_rec g_wkth_base_rec_type;
747 l_wkth_cat_rec g_wkth_cat_rec_type;
748
749 BEGIN
750
751 -- Set up base level record
752 l_wkth_base_rec.person_type_id := p_person_type_id;
753 l_wkth_base_rec.system_person_type := p_system_person_type;
754 l_wkth_base_rec.user_person_type := p_user_person_type;
755 l_wkth_base_rec.business_group_id := p_business_group_id;
756 l_wkth_base_rec.primary_flag := p_primary_flag;
757 l_wkth_base_rec.employment_category := p_employment_category;
758 l_wkth_base_rec.assignment_type := p_assignment_type;
759
760 -- Populate hierarchy record
761 l_wkth_cat_rec := cache_wkth_categories(l_wkth_base_rec);
762
763 -- Return required column
764 RETURN l_wkth_cat_rec.wkth_lvl1_sk_fk;
765
766 END get_wkth_lvl1_sk_fk;
767
768 -- --------------------------------------------------------------
769 -- Returns single column value for given base record by running
770 -- the fast formula
771 -- --------------------------------------------------------------
772 FUNCTION get_wkth_lvl2_sk_fk
773 (p_person_type_id IN NUMBER
774 ,p_system_person_type IN VARCHAR2
775 ,p_user_person_type IN VARCHAR2
776 ,p_business_group_id IN NUMBER
777 ,p_primary_flag IN VARCHAR2
778 ,p_employment_category IN VARCHAR2
779 ,p_assignment_type IN VARCHAR2)
780 RETURN VARCHAR2 IS
781
782 l_wkth_base_rec g_wkth_base_rec_type;
783 l_wkth_cat_rec g_wkth_cat_rec_type;
784
785 BEGIN
786
787 -- Set up base level record
788 -- Populate hierarchy record
789 -- Return required column
790 l_wkth_base_rec.person_type_id := p_person_type_id;
791 l_wkth_base_rec.system_person_type := p_system_person_type;
792 l_wkth_base_rec.user_person_type := p_user_person_type;
793 l_wkth_base_rec.business_group_id := p_business_group_id;
794 l_wkth_base_rec.primary_flag := p_primary_flag;
795 l_wkth_base_rec.employment_category := p_employment_category;
796 l_wkth_base_rec.assignment_type := p_assignment_type;
797
798 l_wkth_cat_rec := cache_wkth_categories(l_wkth_base_rec);
799
800 RETURN l_wkth_cat_rec.wkth_lvl2_sk_fk;
801
802 END get_wkth_lvl2_sk_fk;
803
804
805 -- --------------------------------------------------------------
806 -- Returns single column value for given base record by running
807 -- the fast formula
808 -- --------------------------------------------------------------
809 FUNCTION get_wkth_lvl1_code
810 (p_person_type_id IN NUMBER
811 ,p_system_person_type IN VARCHAR2
812 ,p_user_person_type IN VARCHAR2
813 ,p_business_group_id IN NUMBER
814 ,p_primary_flag IN VARCHAR2
815 ,p_employment_category IN VARCHAR2
816 ,p_assignment_type IN VARCHAR2)
817 RETURN VARCHAR2 IS
818
819 l_wkth_base_rec g_wkth_base_rec_type;
820 l_wkth_cat_rec g_wkth_cat_rec_type;
821
822 BEGIN
823
824 -- Set up base level record
825 l_wkth_base_rec.person_type_id := p_person_type_id;
826 l_wkth_base_rec.system_person_type := p_system_person_type;
827 l_wkth_base_rec.user_person_type := p_user_person_type;
828 l_wkth_base_rec.business_group_id := p_business_group_id;
829 l_wkth_base_rec.primary_flag := p_primary_flag;
830 l_wkth_base_rec.employment_category := p_employment_category;
831 l_wkth_base_rec.assignment_type := p_assignment_type;
832
833 -- Populate hierarchy record
834 l_wkth_cat_rec := cache_wkth_categories(l_wkth_base_rec);
835
836 -- Return required column
837 RETURN l_wkth_cat_rec.wkth_lvl1_code;
838
839 END get_wkth_lvl1_code;
840
841 -- --------------------------------------------------------------
842 -- Returns single column value for given base record by running
843 -- the fast formula
844 -- --------------------------------------------------------------
845 FUNCTION get_wkth_lvl2_code
846 (p_person_type_id IN NUMBER
847 ,p_system_person_type IN VARCHAR2
848 ,p_user_person_type IN VARCHAR2
849 ,p_business_group_id IN NUMBER
850 ,p_primary_flag IN VARCHAR2
851 ,p_employment_category IN VARCHAR2
852 ,p_assignment_type IN VARCHAR2)
853 RETURN VARCHAR2 IS
854
855 l_wkth_base_rec g_wkth_base_rec_type;
856 l_wkth_cat_rec g_wkth_cat_rec_type;
857
858 BEGIN
859
860 -- Set up base level record
861 l_wkth_base_rec.person_type_id := p_person_type_id;
862 l_wkth_base_rec.system_person_type := p_system_person_type;
863 l_wkth_base_rec.user_person_type := p_user_person_type;
864 l_wkth_base_rec.business_group_id := p_business_group_id;
865 l_wkth_base_rec.primary_flag := p_primary_flag;
866 l_wkth_base_rec.employment_category := p_employment_category;
867 l_wkth_base_rec.assignment_type := p_assignment_type;
868
869 -- Populate hierarchy record
870 l_wkth_cat_rec := cache_wkth_categories(l_wkth_base_rec);
871
872 -- Return required column
873 RETURN l_wkth_cat_rec.wkth_lvl2_code;
874
875 END get_wkth_lvl2_code;
876
877
878 -- --------------------------------------------------------------
879 -- Returns single column value for given primary key from the
880 -- collected table
881 -- --------------------------------------------------------------
882 FUNCTION get_wkth_wktyp_code
883 (p_prsntyp_sk_pk IN NUMBER)
884 RETURN VARCHAR2 IS
885
886 l_wkth_wktyp_code VARCHAR2(240);
887
888 BEGIN
889 -- Get column value from the cache
890 BEGIN
891 l_wkth_wktyp_code := g_cache_wkth_values(p_prsntyp_sk_pk).wkth_wktyp_code;
892
893 -- If there is no value in the cache then load the cache from the table
894 EXCEPTION WHEN OTHERS THEN
895 cache_wkth_values(p_prsntyp_sk_pk);
896 l_wkth_wktyp_code := g_cache_wkth_values(p_prsntyp_sk_pk).wkth_wktyp_code;
897 END;
898
899 RETURN l_wkth_wktyp_code;
900
901 END get_wkth_wktyp_code;
902
903 -- --------------------------------------------------------------
904 -- Returns single column value for given base record by running
905 -- the fast formula
906 -- --------------------------------------------------------------
907 FUNCTION get_include_flag
908 (p_person_type_id IN NUMBER
909 ,p_system_person_type IN VARCHAR2
910 ,p_user_person_type IN VARCHAR2
911 ,p_business_group_id IN NUMBER
912 ,p_primary_flag IN VARCHAR2
913 ,p_employment_category IN VARCHAR2
914 ,p_assignment_type IN VARCHAR2)
915 RETURN VARCHAR2 IS
916
917 l_wkth_base_rec g_wkth_base_rec_type;
918 l_wkth_cat_rec g_wkth_cat_rec_type;
919
920 BEGIN
921
922 -- Set up base level record
923 l_wkth_base_rec.person_type_id := p_person_type_id;
924 l_wkth_base_rec.system_person_type := p_system_person_type;
925 l_wkth_base_rec.user_person_type := p_user_person_type;
926 l_wkth_base_rec.business_group_id := p_business_group_id;
927 l_wkth_base_rec.primary_flag := p_primary_flag;
928 l_wkth_base_rec.employment_category := p_employment_category;
929 l_wkth_base_rec.assignment_type := p_assignment_type;
930
931 -- Populate hierarchy record
932 l_wkth_cat_rec := cache_wkth_categories(l_wkth_base_rec);
933
934 -- Return required column
935 RETURN l_wkth_cat_rec.include_flag;
936
937 END get_include_flag;
938
939 -- -------------------------------------------------------------------------
940 -- Looks up person type surrogate key given the composite OLTP primary key
941 -- -------------------------------------------------------------------------
942 FUNCTION get_prsntyp_sk_fk
943 (p_person_type_id IN NUMBER
944 ,p_employment_category IN VARCHAR2
945 ,p_primary_flag IN VARCHAR2
946 ,p_assignment_type IN VARCHAR2)
947 RETURN NUMBER IS
948
949 -- Cursor to get the SK also cache any information
950 -- that will be resused later
951 CURSOR prsntyp_sk_csr IS
952 SELECT
953 prsntyp_sk_pk
954 ,wkth_wktyp_code
955 FROM hri_cs_prsntyp_ct
956 WHERE person_type_id = p_person_type_id
957 AND employment_category_code = p_employment_category
958 AND primary_flag_code = p_primary_flag
959 AND assignment_type_code = p_assignment_type;
960
961 l_cache_key VARCHAR2(240);
962 l_prsntyp_sk_fk NUMBER;
963 l_wkth_wktyp_code VARCHAR2(240);
964
965 BEGIN
966
967 -- Formulate cache key
968 l_cache_key := to_char(p_person_type_id) || '|' ||
969 p_primary_flag || '|' ||
970 p_employment_category || '|' ||
971 p_assignment_type;
972
973 -- Test if surrogate key can be returned from the cache
974 BEGIN
975
976 l_prsntyp_sk_fk := g_cache_prsntyp_sk(l_cache_key);
977
978 -- If no value is in the cache then populate cache
979 EXCEPTION WHEN OTHERS THEN
980
981 -- Get SK from table
982 OPEN prsntyp_sk_csr;
983 FETCH prsntyp_sk_csr INTO l_prsntyp_sk_fk, l_wkth_wktyp_code;
984 CLOSE prsntyp_sk_csr;
985
986 -- Store SK in cache for future reference
987 IF (l_prsntyp_sk_fk IS NULL) THEN
988 l_prsntyp_sk_fk := -1;
989 g_cache_prsntyp_sk(l_cache_key) := l_prsntyp_sk_fk;
990 g_cache_wkth_values(l_prsntyp_sk_fk).wkth_wktyp_code := 'NA_EDW';
991 ELSE
992 g_cache_prsntyp_sk(l_cache_key) := l_prsntyp_sk_fk;
993 g_cache_wkth_values(l_prsntyp_sk_fk).wkth_wktyp_code := l_wkth_wktyp_code;
994 END IF;
995
996 END;
997
998 -- Return SK
999 RETURN l_prsntyp_sk_fk;
1000
1001 END get_prsntyp_sk_fk;
1002
1003 END HRI_BPL_PERSON_TYPE;
[Home] [Help]