1 PACKAGE BODY hri_bpl_job AS
2 /* $Header: hribjob.pkb 120.1 2005/08/09 03:45:48 jtitmas noship $ */
3
4 TYPE g_varchar80_tabtype IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
5 TYPE g_varchar400_tabtype IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
6
7 /* Table of job display name segment formats by business group */
8 g_bg_segment_value g_varchar400_tabtype;
9
10 /* Table of product/job associations */
11 g_prod_cat_table g_varchar80_tabtype;
12 g_prod_cat_segment VARCHAR2(30);
13
14 g_job_role_ff_id NUMBER;
15 c_job_role_ff_name CONSTANT VARCHAR2(30):= 'HRI_MAP_JOB_JOB_ROLE';
16
17
18 /* Type of caching record to store the output of fast formula, */
19 /* By using the outputs in this records, the number of fast formula */
20 /* calls will reduce */
21
22 TYPE ff_output_rec IS RECORD
23 (job_role_code VARCHAR2(240)
24 );
25
26 TYPE g_ff_ouptut_tab_type IS TABLE OF ff_output_rec INDEX BY VARCHAR2(480);
27
28 g_job_role_cache g_ff_ouptut_tab_type;
29
30 /******************************************************************************/
31 /* PUBLIC Functions and Procedures */
32 /******************************************************************************/
33
34
35 /******************************************************************************/
36 /* HRI Job Categories consists of a number of member (lookups) for the set */
37 /* plus one lookup if a job does not match any of the set (other). The single */
38 /* (other) lookup is identified by the member lookup column holding NULL. */
39 /* */
40 /* Add_job_category inserts a row if it does not already exist, but updates */
41 /* the other lookup row if it exists already and is different. */
42 /******************************************************************************/
43 PROCEDURE add_job_category( p_job_cat_set IN NUMBER,
44 p_job_cat_lookup IN VARCHAR2 := null,
45 p_other_lookup IN VARCHAR2 := null )
46 IS
47
48 l_other_lookup VARCHAR2(30); -- Holds other lookup if it already exists
49
50 /* Selects the other lookup column from a row if it exists */
51 CURSOR row_exists_cur IS
52 SELECT other_lookup_code FROM hri_job_category_sets
53 WHERE job_category_set = p_job_cat_set
54 AND (member_lookup_code = p_job_cat_lookup
55 OR member_lookup_code IS NULL and p_job_cat_lookup IS NULL);
56
57 BEGIN
58
59 /* Check that primary key is valid - the job category set is within range */
60 /* and one (and only one) of the lookups must be populated */
61 IF (p_job_cat_lookup IS NULL AND p_other_lookup IS NULL) THEN
62 RETURN;
63 ELSIF (p_job_cat_lookup IS NOT NULL AND p_other_lookup IS NOT NULL) THEN
64 RETURN;
65 ELSIF (p_job_cat_set < 1 OR p_job_cat_set > 15) THEN
66 RETURN;
67 END IF;
68
69 /* See if a row already exists */
70 OPEN row_exists_cur;
71 FETCH row_exists_cur INTO l_other_lookup;
72 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
73 /* If row does not exist, insert it */
74 INSERT INTO hri_job_category_sets
75 ( job_category_set
76 , member_lookup_code
77 , other_lookup_code )
78 VALUES
79 ( p_job_cat_set
80 , p_job_cat_lookup
81 , p_other_lookup );
82 ELSIF (l_other_lookup <> p_other_lookup AND p_other_lookup IS NOT NULL) THEN
83 /* If the other row exists, update it if it is different */
84 UPDATE hri_job_category_sets
85 SET other_lookup_code = p_other_lookup
86 WHERE job_category_set = p_job_cat_set
87 AND member_lookup_code IS NULL;
88 END IF;
89 CLOSE row_exists_cur;
90
91 END add_job_category;
92
93 /******************************************************************************/
94 /* Removes given job category by blanket delete */
95 /******************************************************************************/
96 PROCEDURE remove_job_category( p_job_cat_set IN NUMBER,
97 p_job_cat_lookup IN VARCHAR2 := null,
98 p_other_lookup IN VARCHAR2 := null )
99 IS
100
101 BEGIN
102
103 /* Remove row if it exists */
104 DELETE FROM hri_job_category_sets
105 WHERE job_category_set = p_job_cat_set
106 AND (member_lookup_code = p_job_cat_lookup
107 OR other_lookup_code = p_other_lookup);
108
109 END remove_job_category;
110
111 /******************************************************************************/
112 /* This procedure is called by FNDLOAD via the tempalte hrijcts.lct */
113 /* Load row simply calls the update procedure */
114 /******************************************************************************/
115 PROCEDURE load_row( p_job_cat_set IN NUMBER,
116 p_job_cat_lookup IN VARCHAR2,
117 p_other_lookup IN VARCHAR2,
118 p_owner IN VARCHAR2 )
119 IS
120
121 BEGIN
122
123 /* Call to add_job_category includes the update functionality required */
124 add_job_category(p_job_cat_set, p_job_cat_lookup, p_other_lookup);
125
126 END load_row;
127
128
129 /******************************************************************************/
130 /* Caches the job segment which stores product category */
131 /******************************************************************************/
132 PROCEDURE cache_prod_cat_segment IS
133
134 CURSOR prod_cat_segment_csr IS
135 SELECT bfm.application_column_name
136 FROM bis_flex_mappings_v bfm
137 , bis_dimensions_vl bd
138 WHERE bfm.dimension_id = bd.dimension_id
139 AND bd.short_name = 'PRODUCT'
140 AND bfm.level_short_name = 'PRODUCT GROUP'
141 AND bfm.application_id = 800;
142
143 BEGIN
144
145 OPEN prod_cat_segment_csr;
146 FETCH prod_cat_segment_csr INTO g_prod_cat_segment;
147 CLOSE prod_cat_segment_csr;
148
149 IF (g_prod_cat_segment IS NULL) THEN
150 g_prod_cat_segment := 'NA_EDW';
151 END IF;
152
153 END cache_prod_cat_segment;
154
155
156 /******************************************************************************/
157 /* Finds the product category for a given job */
158 /******************************************************************************/
159 FUNCTION lookup_product_category( p_job_id IN NUMBER )
160 RETURN VARCHAR2 IS
161
162 TYPE prod_cat_csr_type IS REF CURSOR;
163 prod_cat_cv prod_cat_csr_type;
164
165 csr_sql_stmt VARCHAR2(200);
166 l_product_category VARCHAR2(80);
167
168 BEGIN
169
170 /* Cache product category segment */
171 IF (g_prod_cat_segment IS NULL) THEN
172 cache_prod_cat_segment;
173 END IF;
174
175 IF (g_prod_cat_segment <> 'NA_EDW') THEN
176
177 BEGIN
178 l_product_category := g_prod_cat_table(p_job_id);
179 EXCEPTION WHEN OTHERS THEN
180 csr_sql_stmt := 'SELECT pct.value ' ||
181 'FROM bis_product_categories_v pct, per_jobs job ' ||
182 'WHERE job.job_id = :1 ' ||
183 'AND pct.id = job.' || g_prod_cat_segment;
184 OPEN prod_cat_cv FOR csr_sql_stmt USING p_job_id;
185 LOOP
186 FETCH prod_cat_cv INTO l_product_category;
187 EXIT WHEN prod_cat_cv%NOTFOUND;
188 END LOOP;
189 CLOSE prod_cat_cv;
190 g_prod_cat_table(p_job_id) := l_product_category;
191 END;
192 END IF;
193
194 RETURN l_product_category;
195
196 END lookup_product_category;
197
198
199 /******************************************************************************/
200 /* Returns the configurable display format to use for a job name */
201 /******************************************************************************/
202 FUNCTION get_job_display_name(p_job_id IN NUMBER,
203 p_business_group_id IN NUMBER,
204 p_job_name IN VARCHAR2)
205 RETURN VARCHAR2 IS
206
207 l_return_value VARCHAR2(240);
208
209 BEGIN
210
211 /* Check parameters are passed in correctly */
212 IF (p_business_group_id IS NOT NULL AND
213 p_job_id IS NOT NULL AND
214 p_job_name IS NOT NULL) THEN
215
216 /* Trap NO_DATA_FOUND exceptions when accessing global table */
217 BEGIN
218 /* Re-use cached segment format if possible */
219 IF (g_bg_segment_value(p_business_group_id) IS NOT NULL) THEN
220 l_return_value := hr_misc_web.get_user_defined_job_segments
221 (p_job_segments => g_bg_segment_value(p_business_group_id)
222 ,p_job_name => p_job_name
223 ,p_job_id => p_job_id);
224 END IF;
225 EXCEPTION WHEN OTHERS THEN
226 /* Calculate segment format for business group */
227 g_bg_segment_value(p_business_group_id) :=
228 hr_misc_web.get_sshr_segment_value
229 (p_bg_id => p_business_group_id,
230 p_user_column_name => 'Display MEE Job Segments');
231 /* If a value is found use it */
232 IF (g_bg_segment_value(p_business_group_id) IS NOT NULL) THEN
233 l_return_value := hr_misc_web.get_user_defined_job_segments
234 (p_job_segments => g_bg_segment_value(p_business_group_id)
235 ,p_job_name => p_job_name
236 ,p_job_id => p_job_id);
237 END IF;
238 END;
239
240 END IF;
241
242 /* If no display format is found return the job name */
243 RETURN NVL(l_return_value, p_job_name);
244
245 END get_job_display_name;
246 --
247 -- -----------------------------------------------------------------------------
248 -- Inserts row into concurrent program log
249 -- -----------------------------------------------------------------------------
250 --
251 PROCEDURE output(p_text VARCHAR2) IS
252 --
253 BEGIN
254 --
255 HRI_BPL_CONC_LOG.output(p_text);
256 --
257 END output;
258 -- -----------------------------------------------------------------------------
259 -- Inserts row into concurrent program log if debugging is enabled
260 -- -----------------------------------------------------------------------------
261 --
262 PROCEDURE dbg(p_text VARCHAR2) IS
263 --
264 BEGIN
265 --
266 HRI_BPL_CONC_LOG.dbg(p_text);
267 --
268 END dbg;
269 --
270 -- -------------------------------------------------------------------------
271 -- Checks that the fast formula exist in the proper business group and
272 -- is compiled
273 -- -------------------------------------------------------------------------
274 --
275 FUNCTION ff_exists_and_compiled(p_business_group_id IN NUMBER
276 ,p_date IN DATE
277 ,p_ff_name IN VARCHAR2)
278 RETURN NUMBER
279 IS
280 --
281 -- Cursor to fetch job role fast formula
282 --
283 CURSOR c_job_role_formula IS
284 SELECT fff.formula_id
285 FROM
286 ff_formulas_f fff
287 ,ff_formula_types fft
288 WHERE fft.formula_type_name = 'QuickPaint'
289 AND fff.formula_type_id = fft.formula_type_id
290 AND fff.business_group_id = p_business_group_id
291 AND p_date BETWEEN fff.effective_start_date AND fff.effective_end_date
292 AND fff.formula_name = p_ff_name;
293 --
294 l_ff_id NUMBER;
295 --
296 BEGIN
297 --
298 -- Check if the fast formula exists
299 --
300 OPEN c_job_role_formula;
301 FETCH c_job_role_formula INTO l_ff_id;
302 CLOSE c_job_role_formula;
303 --
304 -- If the fast formula is not available then return null
305 --
306 IF l_ff_id IS NULL THEN
307 --
308 RETURN NULL;
309 --
310 END IF;
311 --
312 hri_bpl_abv.CheckFastFormulaCompiled(p_formula_id => l_ff_id,
313 p_bgttyp => p_business_group_id);
314 --
315 -- If no exception is raised then return the fast formula
316 --
317 RETURN l_ff_id;
318 --
319 EXCEPTION
320 --
321 -- Handling the case when the fast formula is not compiled
322 --
323 WHEN hri_bpl_abv.ff_not_compiled THEN
324 --
325 RAISE;
326 --
327 END ff_exists_and_compiled;
328
329 -- -----------------------------------------------------------------------
330 -- Function returning the id of the fast formula
331 -- -----------------------------------------------------------------------
332
333 FUNCTION get_job_role_ff_id
334 RETURN NUMBER
335 IS
336 --
337 BEGIN
338 --
339 -- Check if the formula_id is already cached
340 --
341 IF g_job_role_ff_id IS NULL THEN
342 g_job_role_ff_id := ff_exists_and_compiled
343 (p_business_group_id => 0
344 ,p_date => trunc(SYSDATE)
345 ,p_ff_name => c_job_role_ff_name
346 );
347 --
348 IF (g_job_role_ff_id IS NULL) AND (g_warning_flag = 'N') THEN
349 --
350 g_warning_flag := 'Y';
351 --
352 output('The fast formula' || ' ' || c_job_role_ff_name || ' ' || 'is not defined in business_group_id = 0');
353 --
354 RETURN g_job_role_ff_id;
355 --
356 END IF;
357 --
358 END IF;
359 --
360 RETURN g_job_role_ff_id;
361 --
362 END get_job_role_ff_id;
363 --
364 -- ---------------------------------------------------------------------------
365 -- Run the job role rule by calling the fast formula
366 -- ---------------------------------------------------------------------------
367
368 PROCEDURE run_job_role_rule
369 (p_business_group_id IN NUMBER
370 ,p_job_fmly_code IN VARCHAR2
371 ,p_job_fnctn_code IN VARCHAR2
372 ,p_job_role_code OUT NOCOPY VARCHAR2
373 )
374 IS
375 --
376 l_ff_id NUMBER;
377 l_job_code VARCHAR2(480);
378 l_inputs FF_EXEC.INPUTS_T;
379 l_outputs FF_EXEC.OUTPUTS_T;
380 l_bg_name PER_BUSINESS_GROUPS.NAME%TYPE;
381 --
382 CURSOR c_bg_name IS
383 SELECT name
384 FROM per_business_groups
385 WHERE business_group_id = p_business_group_id;
386 --
387 BEGIN
388 --
389 -- Identify the formula to be executed
390 --
391 l_ff_id := get_job_role_ff_id;
392 --
393 -- In case a formula is not defined then return 'NA_EDW'
394 --
395 IF l_ff_id IS NULL THEN
396 --
397 p_job_role_code := 'NA_EDW';
398 --
399 RETURN;
400 --
401 --
402 END IF;
403 --
404 -- If the job role for the specified combination of job_function and job
405 -- family is available in the cache, then return the value stored in the
406 -- cache instead of calling fast formula
407 --
408 BEGIN
409 --
410 l_job_code := p_job_fmly_code || p_job_fnctn_code;
411 --
412 p_job_role_code := g_job_role_cache(l_job_code).job_role_code;
413 --
414 RETURN;
415 --
416 EXCEPTION
417 --
418 WHEN OTHERS THEN
419 --
420 NULL;
421 --
422 END;
423 --
424 -- Initialize the formula input and output tables */
425 --
426 FF_Exec.Init_Formula
427 (l_ff_id
428 ,SYSDATE
429 ,l_inputs
430 ,l_outputs
431 );
432 --
433 -- Set the input values
434 --
435 IF l_inputs.count > 0 THEN
436 --
437 FOR l_loop_count IN l_inputs.FIRST..l_inputs.LAST LOOP
438 --
439 -- CODE the inputs here
440 --
441 IF l_inputs(l_loop_count).name = 'DATE_EARNED' THEN
442 l_inputs(l_loop_count).value := fnd_date.date_to_canonical(SYSDATE);
443 ELSIF upper(l_inputs(l_loop_count).name) = 'ASSIGNMENT_ID' THEN
444 l_inputs(l_loop_count).value := -1;
445 ELSIF upper(l_inputs(l_loop_count).name) = 'JOB_FAMILY_CODE' THEN
446 l_inputs(l_loop_count).value := p_job_fmly_code;
447 ELSIF upper(l_inputs(l_loop_count).name) = 'JOB_FUNCTION_CODE' THEN
448 l_inputs(l_loop_count).value := p_job_fnctn_code;
449 ELSIF upper(l_inputs(l_loop_count).name) = 'BUSINESS_GROUP_NAME' THEN
450 --
451 OPEN c_bg_name;
452 FETCH c_bg_name into l_bg_name;
453 CLOSE c_bg_name;
454 --
455 l_inputs(l_loop_count).value := l_bg_name;
456 --
457 END IF;
458
459 --
460 END LOOP;
461 --
462 END IF;
463 --
464 -- Run the fast formula
465 --
466 FF_Exec.Run_Formula
467 (l_inputs
468 ,l_outputs
469 );
470 --
471 -- Get the output from the fast formula
472 --
473 IF l_outputs.count > 0 THEN
474 --
475 FOR l_loop_count IN l_outputs.FIRST..l_outputs.LAST LOOP
476 --
477 -- CODE the outputs here
478 --
479 IF upper(l_outputs(l_loop_count).name) = 'JOB_ROLE_CODE' THEN
480 --
481 p_job_role_code := l_outputs(l_loop_count).value;
482 --
483 END IF;
484 --
485 IF hr_api.not_exists_in_hr_lookups
486 (p_lookup_type => 'HRI_CL_JOB_ROLE'
487 ,p_lookup_code => p_job_role_code
488 ,p_effective_date => SYSDATE) AND p_job_role_code <> 'NA_EDW'
489 THEN
490 --
491 g_warning_flag := 'Y';
492
493 --
494 output('The lookup does not contain the job role code' || ' ' || p_job_role_code );
495 --
496 END IF;
497 END LOOP;
498 --
499 END IF;
500 --
501 -- Store the values in cache
502 --
503 l_job_code := p_job_fmly_code || p_job_fnctn_code;
504 --
505 g_job_role_cache(l_job_code).job_role_code := NVL(p_job_role_code, 'NA_EDW');
506 --
507 END run_job_role_rule;
508
509 -- -------------------------------------------------------------------------
510 -- Function retuning the job role code
511 -- -------------------------------------------------------------------------
512
513 FUNCTION get_job_role_code(p_job_fmly_code IN VARCHAR,
514 p_job_fnctn_code IN VARCHAR2)
515 RETURN VARCHAR2
516 IS
517 --
518 l_job_role_code VARCHAR2(240);
519 --
520 BEGIN
521 --
522 -- Call to run the fast formula in order to get the job role code
523 --
524 run_job_role_rule
525 (p_business_group_id => 0
526 ,p_job_fmly_code => p_job_fmly_code
527 ,p_job_fnctn_code => p_job_fnctn_code
528 ,p_job_role_code => l_job_role_code
529 );
530 --
531 RETURN l_job_role_code;
532 --
533 END get_job_role_code;
534 --
535
536 END hri_bpl_job;
[Home] [Help]