1 PACKAGE BODY HR_BIS_ALERTS AS
2 /* $Header: hrbistr.pkb 115.32 2003/12/29 02:14:11 prasharm ship $ */
3
4 c_ampersand constant VARCHAR2(1) := fnd_global.local_chr(38);
5
6 g_orgprc_const VARCHAR2(4) := 'SINR';
7
8 --Package Global Exceptions
9 e_g_fatal_error exception;
10
11 --Targets etc...
12 e_g_no_tr_found exception;
13 e_g_no_pm_found exception;
14 e_g_no_pm_known exception;
15 e_g_no_tl_found exception;
16 e_g_no_tv_found exception;
17 e_g_no_pd_found exception;
18
19 --Others...
20 e_g_no_ff_bg exception; -- bg for a ff
21 e_g_no_ff exception; -- fast formula
22 e_g_no_bg exception; -- business group
23 e_g_no_sg exception; -- security group
24 e_g_bad_freq_code exception;
25 e_g_no_ap exception; -- application_id for a resp
26 e_g_bmt_invalid exception; -- Budget Meas type is not a lookup and hence not valid
27
28 --Function specific
29 e_g_tr_not_populated exception; -- Get_target_rec
30
31 e_g_bis_fn_error exception; -- Error calling a bis function
32
33 -- Records
34
35 --Record to store the session details
36 TYPE hri_session_rec_type IS RECORD
37 (user_id NUMBER --fnd_GLOBAL.USER_ID
38 ,resp_id NUMBER
39 ,resp_appl_id NUMBER
40 ,security_group_id NUMBER);
41
42 --Details of session at start
43 g_session_rec hri_session_rec_type;
44
45 --Record to store the required Information pertaining to a target
46 TYPE hri_target_rec_type IS RECORD
47 (measure_short_name bisbv_performance_measures.measure_short_name%TYPE
48 ,target_id bis_target_values.target_id%TYPE
49 ,target_level_id bisfv_target_levels.target_level_id%TYPE
50 ,target_level_short_name bisfv_target_levels.target_level_short_name%TYPE
51 ,measure_id bisbv_performance_measures.measure_id%TYPE
52 ,plan_id bis_target_values.plan_id%TYPE
53 ,org_level_short_name bisfv_target_levels.org_level_short_name%TYPE
54 ,org_level_value_id bisbv_targets.org_level_value_id%TYPE
55 ,time_level_value_id bisbv_targets.time_level_value_id%TYPE
56 ,time_level_short_name bisfv_target_levels.time_level_short_name%TYPE
57 ,dim1_level_short_name bisfv_target_levels.dimension1_level_short_name%TYPE
58 ,dim1_level_value_id bisbv_targets.dim1_level_value_id%TYPE
59 ,dim2_level_short_name bisfv_target_levels.dimension2_level_short_name%TYPE
60 ,dim2_level_value_id bisbv_targets.dim2_level_value_id%TYPE
61 ,dim3_level_short_name bisfv_target_levels.dimension3_level_short_name%TYPE
62 ,dim3_level_value_id bisbv_targets.dim3_level_value_id%TYPE
63 ,dim4_level_short_name bisfv_target_levels.dimension4_level_short_name%TYPE
64 ,dim4_level_value_id bisbv_targets.dim4_level_value_id%TYPE
65 ,dim5_level_short_name bisfv_target_levels.dimension5_level_short_name%TYPE
66 ,dim5_level_value_id bisbv_targets.dim5_level_value_id%TYPE
67 ,unit_of_measure bisbv_target_levels.unit_of_measure%TYPE
68 ,workflow_item_type bisfv_target_levels.workflow_item_type%TYPE
69 ,workflow_process_short_name bisfv_target_levels.workflow_process_short_name%TYPE
70 ,target bisfv_targets.target%TYPE
71 ,range1_low bisfv_targets.range1_low%TYPE
72 ,range1_high bisfv_targets.range1_high%TYPE
73 ,range2_low bisfv_targets.range2_low%TYPE
74 ,range2_high bisfv_targets.range2_high%TYPE
75 ,range3_low bisfv_targets.range3_low%TYPE
76 ,range3_high bisfv_targets.range3_high%TYPE
77 ,notify_resp1_id fnd_responsibility.responsibility_id%TYPE
78 ,notify_resp1_appl_id fnd_responsibility.application_id%TYPE
79 ,notify_resp1_short_name bisfv_targets.notify_resp1_short_name%TYPE
80 ,notify_resp1_name bisfv_targets.notify_resp1_name%TYPE
81 ,notify_resp2_id fnd_responsibility.responsibility_id%TYPE
82 ,notify_resp2_appl_id fnd_responsibility.application_id%TYPE
83 ,notify_resp2_short_name bisfv_targets.notify_resp2_short_name%TYPE
84 ,notify_resp2_name bisfv_targets.notify_resp2_name%TYPE
85 ,notify_resp3_id fnd_responsibility.responsibility_id%TYPE
86 ,notify_resp3_appl_id fnd_responsibility.application_id%TYPE
87 ,notify_resp3_short_name bisfv_targets.notify_resp3_short_name%TYPE
88 ,notify_resp3_name bisfv_targets.notify_resp3_name%TYPE
89 -- ,budget_measurement_type hr_lookups.lookup_code%TYPE
90 ,period_start_DATE DATE
91 ,period_end_DATE DATE);
92
93 --**********************************************************************
94 -- COMMON FUNCTIONS AND PROCEDURES
95 --**********************************************************************
96
97 --DEBUG FUNCTIONS--
98 -------------------------------------------------------------------------
99 -- pl - put line
100 -- NOTE all code must be commented out except NULL when delivered
101 -------------------------------------------------------------------------
102
103 PROCEDURE pl(p_text IN VARCHAR2,
104 p_text2 IN VARCHAR2 DEFAULT NULL)
105 IS
106
107 BEGIN
108 --dbms_output.put_line(p_text); -- *** NOTE THIS LINE MUST BE COMMENTED OUT BEFORE RETURNinG TO ARCS
109 /*
110 INSERT INTO hri.hri_debug
111 (text1
112 ,text2
113 ,insert_date
114 )
115 VALUES
116 (substr(p_text,1,239)
117 ,substr(p_text2,1,239)
118 ,sysdate
119 )
120 ;
121 COMMIT;
122 /**/
123 /*
124 CREATE TABLE HRI.HRI_DEBUG
125 (
126 TEXT1 VARCHAR2(240)
127 ,TEXT2 VARCHAR2(240)
128 ,INSERT_DATE DATE
129 );
130 /**/
131 null;
132 END;
133
134 -------------------------------------------------------------------------
135 -- debug_hri_target_rec
136 --
137 -- UtilISes pl to debug target record
138 -------------------------------------------------------------------------
139 PROCEDURE debug_hri_target_rec
140 (p_target_rec IN hri_target_rec_type)
141 IS
142 BEGIN
143 pl('HRI TARGET REC => ');
144 pl(' measure_short_name',p_target_rec.measure_short_name);
145 pl(' target_id',to_char(p_target_rec.target_id));
146 pl(' target_level_id',to_char(p_target_rec.target_level_id));
147 pl(' indicator_id',to_char(p_target_rec.measure_id));
148 pl(' Plan id ',to_char(p_target_rec.plan_id));
149 pl(' Org ',p_target_rec.org_level_short_name);
150 pl(' Org id ',p_target_rec.org_level_value_id);
151 pl(' Time ',p_target_rec.time_level_short_name);
152 pl(' Time id ',p_target_rec.time_level_value_id);
153 pl(' Dim1 ',p_target_rec.dim1_level_short_name);
154 pl(' Dim1 id ',p_target_rec.dim1_level_value_id);
155 pl(' Dim2 ',p_target_rec.dim2_level_short_name);
156 pl(' Dim2 id ',p_target_rec.dim2_level_value_id);
157 pl(' Dim3 ',p_target_rec.dim3_level_short_name);
158 pl(' Dim3 id ',p_target_rec.dim3_level_value_id);
159 pl(' Dim4 ',p_target_rec.dim4_level_short_name);
160 pl(' Dim4 id ',p_target_rec.dim4_level_value_id);
161 pl(' Dim5 ',p_target_rec.dim5_level_short_name);
162 pl(' Dim5 id ',p_target_rec.dim5_level_value_id);
163 pl(' Uom ',p_target_rec.unit_of_measure);
164 pl(' Workflow',p_target_rec.workflow_process_short_name);
165 pl(' Range 1 Low',to_char(p_target_rec.RANGE1_LOW));
166 pl(' Range 1 High',to_char(p_target_rec.RANGE1_HIGH));
167 pl(' Range 2 Low',to_char(p_target_rec.RANGE2_LOW));
168 pl(' Range 2 High',to_char(p_target_rec.RANGE2_HIGH));
169 pl(' Range 3 Low',to_char(p_target_rec.RANGE3_LOW));
170 pl(' Range 3 High',to_char(p_target_rec.RANGE3_HIGH));
171 -- pl(' Bdgt M Type ',p_target_rec.budget_measurement_type); Bug 2530846
172
173 pl(' Start date',to_char(p_target_rec.period_start_date));
174 pl(' End Date',to_char(p_target_rec.period_END_date));
175
176 pl(' Resp 1 ID',p_target_rec.notify_resp1_id);
177 pl(' Resp 1 SN',p_target_rec.notify_resp1_short_name);
178 pl(' Resp 1 LN',p_target_rec.notify_resp1_name);
179 pl(' Resp 2 ID',p_target_rec.notify_resp2_id);
180 pl(' Resp 2 SN',p_target_rec.notify_resp2_short_name);
181 pl(' Resp 2 LN',p_target_rec.notify_resp2_name);
182 pl(' Resp 3 ID',p_target_rec.notify_resp3_id);
183 pl(' Resp 3 SN',p_target_rec.notify_resp3_short_name);
184 pl(' Resp 3 LN',p_target_rec.notify_resp3_name);
185
186
187 END debug_hri_target_rec;
188
189 -------------------------------------------------------------------------
190 -- debug_bis_actual_rec
191 --
192 -- Utilises pl to debug bis actual record
193 -------------------------------------------------------------------------
194 PROCEDURE debug_bis_actual_rec
195 (p_actual_rec IN bis_actual_pub.actual_rec_type)
196 IS
197 BEGIN
198
199 pl('BIS ACTUAL REC => ');
200 pl(' Actual ', p_actual_rec.actual);
201 pl(' Target Lvl id ', to_char(p_actual_rec.target_level_id));
202 pl(' Target Lvl SN ', p_actual_rec.target_level_short_name);
203 pl(' Target Lvl LN ', p_actual_rec.target_level_name);
204 pl(' Time V ID ',p_actual_rec.time_level_value_id);
205 pl(' Time V N ',p_actual_rec.time_level_value_name);
206 pl(' Org V ID ',p_actual_rec.org_level_value_id);
207 pl(' Org V N ',p_actual_rec.org_level_value_name);
208 pl(' Dim1 V ID ',p_actual_rec.dim1_level_value_id);
209 pl(' Dim1 V N ',p_actual_rec.dim1_level_value_name);
210 pl(' Dim2 V ID ',p_actual_rec.dim2_level_value_id);
211 pl(' Dim2 V N ',p_actual_rec.dim2_level_value_name);
212 pl(' Dim3 V ID ',p_actual_rec.dim3_level_value_id);
213 pl(' Dim3 V N ',p_actual_rec.dim3_level_value_name);
214 pl(' Dim4 V ID ',p_actual_rec.dim4_level_value_id);
215 pl(' Dim4 V N ',p_actual_rec.dim4_level_value_name);
216 pl(' Dim5 V ID ',p_actual_rec.dim5_level_value_id);
217 pl(' Dim5 V N ',p_actual_rec.dim5_level_value_name);
218 pl(' Resp ID ', to_char(p_actual_rec.responsibility_id));
219 pl(' Resp SN ', p_actual_rec.responsibility_short_name);
220 pl(' Resp LN ', p_actual_rec.responsibility_name);
221
222 END debug_bis_actual_rec;
223 --**********************************************************************
224 -- PRIVATE FUNCTIONS
225 --**********************************************************************
226 ------------------------------------------------------------------------
227 -- Procedure: Get_Dates_from_Time_Dim
228 --
229 -- Parameters: Target_id
230 --
231 -- Description: Returns the start and end date for the time dimension
232 ------------------------------------------------------------------------
233 PROCEDURE get_dates_from_time_dim
234 (p_time_level_value_id IN VARCHAR2
235 ,o_period_start_date OUT NOCOPY DATE
236 ,o_period_end_date OUT NOCOPY DATE)
237 IS
238 --
239 -- get the target start and end dates
240 --
241 CURSOR pd_cur (p_c_time_level_value_id VARCHAR2) IS
242 SELECT start_date
243 , end_date
244 FROM bis_hr_months_v
245 WHERE id = p_c_time_level_value_id
246 UNION
247 SELECT start_date
248 , end_date
249 FROM bis_hr_bimonths_v
250 WHERE id = p_c_time_level_value_id
251 UNION
252 SELECT start_date
253 , end_date
254 FROM bis_hr_quarters_v
255 WHERE id = p_c_time_level_value_id
256 UNION
257 SELECT start_date
258 , end_date
259 FROM bis_hr_semiyears_v
260 WHERE id = p_c_time_level_value_id
261 UNION
262 SELECT start_date
263 , end_date
264 FROM bis_hr_years_v
265 WHERE id = p_c_time_level_value_id;
266
267 BEGIN
268
269 pl(' Time Level Value ID: ',p_time_level_value_id);
270 --
271 -- get the target period dates
272 --
273 OPEN pd_cur(p_time_level_value_id);
274 FETCH pd_cur INTO
275 o_period_start_date
276 ,o_period_end_date;
277 IF pd_cur%NOTFOUND THEN
278 CLOSE pd_cur;
279 RAISE e_g_no_pd_found;
280 END IF;
281 CLOSE pd_cur;
282
283 pl(' Start Date: ',to_char(o_period_start_date));
284 pl(' End Date: ' ,to_char(o_period_end_date));
285
286 END;
287 ------------------------------------------------------------------------
288 -- Procedure: Get_Target_rec
289 --
290 -- RETURN: HRI_Target_Rec_Type - details derived FROM the target_id
291 --
292 -- Parameters: Target_id
293 --
294 -- Description
295 -- This function queries all the relevant information
296 -- required concerning the target, including info at these
297 -- levels:
298 -- indicator
299 -- target level
300 -- target
301 -- time dimension calendars
302 ------------------------------------------------------------------------
303 FUNCTION get_target_rec
304 (p_target_id IN NUMBER)
305 RETURN hri_target_rec_type
306 IS
307
308 --records from the cursors to be deleted
309 l_bis_tarlev_rec bisfv_target_levels%rowtype;
310 l_bis_target_rec bisfv_targets%rowtype;
311 l_target_rec hri_target_rec_type;
312
313 --
314 -- get the performance measure
315 --
316 CURSOR pm_cur (p_target_level_id IN NUMBER) IS
317 SELECT pm.measure_id
318 , pm.measure_short_name
319 FROM bisbv_performance_measures pm
320 , bisbv_target_levels tl
321 WHERE pm.measure_id = tl.measure_id
322 AND tl.target_level_id = p_target_level_id;
323 --
324 -- get the target level
325 --
326 CURSOR tl_cur (p_target_level_id IN NUMBER) IS
327 SELECT *
328 FROM bisfv_target_levels
329 WHERE target_level_id = p_target_level_id;
330 --
331 -- get the target value
332 --
333 CURSOR tv_cur (p_target_id IN NUMBER) IS
334 SELECT *
335 FROM bisfv_targets trg
336 WHERE trg.target_id = p_target_id;
337
338 -- Cursor to verify validity of the budget meaurement type
339 -- at least one row returned if valid
340 CURSOR bmt_cur
341 (p_c_bmt IN VARCHAR2)
342 IS
343 SELECT lookup_code
344 FROM hr_lookups
345 WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE'
346 AND lookup_code = p_c_bmt;
347
348 -- Cursor to obtain fnd_responsibility_id
349 -- from role short name and ensure that the
350 -- role currently exists as an FND responsibility
351 CURSOR resp_id_cur
352 (p_c_role_name IN VARCHAR2)
353 IS
354 SELECT wfr.orig_system_id
355 , rsp.application_id
356 FROM wf_roles wfr
357 , fnd_responsibility rsp
358 WHERE wfr.orig_system_id = rsp.responsibility_id
359 AND wfr.name = p_c_role_name
360 AND wfr.orig_system like 'FND_RESP%';
361
362 ------------------------------------------------------
363 BEGIN
364 pl('--------------------');
365 pl('*Get Target Rec','Start');
366 pl(' Get Target ',to_char(p_target_id));
367
368 --
369 -- get the target value for this target
370 --
371 OPEN tv_cur(p_target_id);
372 FETCH tv_cur INTO l_bis_target_rec;
373 IF tv_cur%NOTFOUND THEN
374 CLOSE tv_cur;
375 RAISE e_g_no_tr_found;
376 END IF;
377 CLOSE tv_cur;
378 --
379 -- get the target level
380 --
381 OPEN tl_cur(l_bis_target_rec.target_level_id);
382 FETCH tl_cur INTO l_bis_tarlev_rec;
383 IF tl_cur%NOTFOUND THEN
384 CLOSE tl_cur;
385 RAISE e_g_no_tl_found;
386 END IF;
387 CLOSE tl_cur;
388 --
389 -- get the performance measure
390 --
394 ,l_target_rec.measure_short_name;
391 OPEN pm_cur(l_bis_target_rec.target_level_id);
392 FETCH pm_cur INTO
393 l_target_rec.measure_id
395 IF pm_cur%NOTFOUND THEN
396 CLOSE pm_cur;
397 RAISE e_g_no_pm_found;
398 END IF;
399 CLOSE pm_cur;
400
401 -- FiX FOR BUG 1578137
402 --Get the 3 fnd resp ids from the role
403 -- and confirm they are FND RESPs
404 --Get ROLE 1 ID
405 OPEN resp_id_cur (l_bis_target_rec.notify_resp1_short_name);
406 FETCH resp_id_cur INTO
407 l_target_rec.notify_resp1_id
408 ,l_target_rec.notify_resp1_appl_id;
409 -- Make sure the ID is NULL
410 -- this will be used later as a check whether to verify
411 -- whether a responsibility and target has been setup
412 -- for role 1
413 IF resp_id_cur%NOTFOUND THEN
414 l_target_rec.notify_resp1_id := NULL;
415 END IF;
416 CLOSE resp_id_cur;
417
418 --Get ROLE 2 ID
419 OPEN resp_id_cur (l_bis_target_rec.notify_resp2_short_name);
420 FETCH resp_id_cur INTO
421 l_target_rec.notify_resp2_id
422 ,l_target_rec.notify_resp2_appl_id;
423 -- Make sure the ID is NULL
424 -- this will be used later as a check whether to verify
425 -- whether a responsibility and target has been setup
426 -- for role 2
427 IF resp_id_cur%NOTFOUND THEN
428 l_target_rec.notify_resp2_id := NULL;
429 END IF;
430 CLOSE resp_id_cur;
431
432 --Get ROLE 3 ID
433 OPEN resp_id_cur (l_bis_target_rec.notify_resp3_short_name);
434 FETCH resp_id_cur INTO
435 l_target_rec.notify_resp3_id
436 ,l_target_rec.notify_resp3_appl_id;
437 -- Make sure the ID is NULL
438 -- this will be used later as a check whether to verify
439 -- whether a responsibility and target has been setup
440 -- for role 3
441 IF resp_id_cur%NOTFOUND THEN
442 l_target_rec.notify_resp3_id := NULL;
443 END IF;
444 CLOSE resp_id_cur;
445
446 --
447 -- get the target period dates
448 --
449 get_dates_from_time_dim
450 (l_bis_target_rec.time_level_value_id
451 ,l_target_rec.period_start_date
452 ,l_target_rec.period_end_date);
453
454 --
455 -- copy the target dimensions to the target record. This will be passed as a parameter
456 -- to the calculate actuals procedure
457 --
458
459 -- measure_short_name and id - done
460 l_target_rec.target_id := p_target_id;
461 l_target_rec.target_level_id := l_bis_tarlev_rec.target_level_id;
462 l_target_rec.target_level_short_name := l_bis_tarlev_rec.target_level_short_name;
463
464 l_target_rec.plan_id := l_bis_target_rec.plan_id;
465 l_target_rec.org_level_short_name := l_bis_tarlev_rec.org_level_short_name;
466 l_target_rec.org_level_value_id := l_bis_target_rec.org_level_value_id;
467 l_target_rec.time_level_value_id := l_bis_target_rec.time_level_value_id;
468 l_target_rec.time_level_short_name := l_bis_tarlev_rec.time_level_short_name;
469 l_target_rec.dim1_level_short_name := l_bis_tarlev_rec.dimension1_level_short_name;
470 l_target_rec.dim1_level_value_id := l_bis_target_rec.dim1_level_value_id;
471 l_target_rec.dim2_level_short_name := l_bis_tarlev_rec.dimension2_level_short_name;
472 l_target_rec.dim2_level_value_id := l_bis_target_rec.dim2_level_value_id;
473 l_target_rec.dim3_level_short_name := l_bis_tarlev_rec.dimension3_level_short_name;
474 l_target_rec.dim3_level_value_id := l_bis_target_rec.dim3_level_value_id;
475 l_target_rec.dim4_level_short_name := l_bis_tarlev_rec.dimension4_level_short_name;
476 l_target_rec.dim4_level_value_id := l_bis_target_rec.dim4_level_value_id;
477 l_target_rec.dim5_level_short_name := l_bis_tarlev_rec.dimension5_level_short_name;
478 l_target_rec.dim5_level_value_id := l_bis_target_rec.dim5_level_value_id;
479 l_target_rec.unit_of_measure := l_bis_tarlev_rec.unit_of_measure;
480 l_target_rec.workflow_item_type := l_bis_tarlev_rec.workflow_item_type;
481 l_target_rec.workflow_process_short_name := l_bis_tarlev_rec.workflow_process_short_name;
482 l_target_rec.target := l_bis_target_rec.target;
483 l_target_rec.range1_low := l_bis_target_rec.range1_low;
484 l_target_rec.range1_high := l_bis_target_rec.range1_high;
485 l_target_rec.range2_low := l_bis_target_rec.range2_low;
486 l_target_rec.range2_high := l_bis_target_rec.range2_high;
487 l_target_rec.range3_low := l_bis_target_rec.range3_low;
488 l_target_rec.range3_high := l_bis_target_rec.range3_high;
489 l_target_rec.notify_resp1_short_name := l_bis_target_rec.notify_resp1_short_name;
490 l_target_rec.notify_resp1_name := l_bis_target_rec.notify_resp1_name;
491 l_target_rec.notify_resp2_short_name := l_bis_target_rec.notify_resp2_short_name;
492 l_target_rec.notify_resp2_name := l_bis_target_rec.notify_resp2_name;
493 l_target_rec.notify_resp3_short_name := l_bis_target_rec.notify_resp3_short_name;
494 l_target_rec.notify_resp3_name := l_bis_target_rec.notify_resp3_name;
495
496 -- Get the Budget Meas Type
497 -- Required for:
498 -- 1) Key to ABV if exists
499 -- 2) Compiling name of budget FF
500
504 -- then a budget is required
501 -- Can't use uom because that is translated and is un-reliable
502 -- If the measure is
503 -- manpower variance, manpower separation or recruitment
505 -- otherwise set it to null
506 IF l_target_rec.measure_short_name
507 IN ('HRMSPFTE','HRMSPHEAD'
508 ,'HRMVRFTE','HRMVRHEAD'
509 ,'HRRCSFTE','HRRCSHEAD')
510 THEN
511 -- Best way to find budget code is to strip it from the measure name
512 -- measure name composed <APP Name(2 Char)><Measure type(4 Char)><Budget Type>
513 -- I don't like this because the code could have spaces in it,
514 -- could be v long and relies on the code representing the budget!!
515 OPEN bmt_cur(substr(l_target_rec.measure_short_name,6));
516 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
517 FETCH bmt_cur INTO l_target_rec.unit_of_measure;
518 IF bmt_cur%NOTFOUND THEN
519 CLOSE bmt_cur;
520 pl(' Budget Measurement Type not valid :', substr(l_target_rec.measure_short_name,6));
521 RAISE e_g_bmt_invalid;
522 END IF;
523 CLOSE bmt_cur;
524
525 ELSE
526 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
527 l_target_rec.unit_of_measure := to_char(NULL);
528 END IF;
529
530 --debug_hri_target_rec(l_target_rec);
531
532 pl('*Get Target Rec','End');
533 pl('--------------------');
534 RETURN l_target_rec;
535
536
537 EXCEPTION
538 WHEN e_g_no_tr_found THEN
539 pl('*Get Target Rec','NO TR Exception - End');
540 RAISE e_g_tr_not_populated;
541 WHEN e_g_no_pm_found THEN
542 pl('*Get Target Rec','NO PM Exception - End');
543 RAISE e_g_tr_not_populated;
544 WHEN e_g_no_tl_found THEN
545 pl('*Get Target Rec','NO TL Exception - End');
546 RAISE e_g_tr_not_populated;
547 WHEN e_g_no_tv_found THEN
548 pl('*Get Target Rec','NO TV Exception - End');
549 RAISE e_g_tr_not_populated;
550 WHEN e_g_no_pd_found THEN
551 pl('*Get Target Rec','NO PD Exception - End');
552 RAISE e_g_tr_not_populated;
553 WHEN e_g_bmt_invalid THEN
554 pl('*Get Target Rec','BMT Invalid Exception - End');
555 RAISE e_g_tr_not_populated;
556 WHEN OTHERS THEN
557 pl('*Get Target Rec','Other Exception - End');
558 RAISE e_g_tr_not_populated;
559
560 END Get_Target_rec;
561 ------------------------------------------------------------------------
562 -- Function: Get_bis_actual_rec
563 --
564 -- RETURN: bis_actual_pub.actual_rec_type
565 --
566 -- Description: This function returns the data in the bis actual
567 -- record format required for calculating the actuals
568 --
569 ------------------------------------------------------------------------
570 FUNCTION get_bis_actual_rec
571 (p_target_rec IN hri_target_rec_type)
572 RETURN
573 bis_actual_pub.actual_rec_type
574 IS
575
576 l_actual_rec bis_actual_pub.actual_rec_type;
577
578 BEGIN
579 l_actual_rec.target_level_id := p_target_rec.target_level_id;
580 l_actual_rec.target_level_short_name := p_target_rec.target_level_short_name;
581 --l_actual_rec.target_level_name := p_target_rec.target_level_name;
582 l_actual_rec.time_level_value_id := p_target_rec.time_level_value_id;
583 l_actual_rec.time_level_value_name := p_target_rec.time_level_short_name;
584 l_actual_rec.org_level_value_id := p_target_rec.org_level_value_id;
585 l_actual_rec.org_level_value_name := p_target_rec.org_level_short_name;
586 l_actual_rec.dim1_level_value_id := p_target_rec.dim1_level_value_id;
587 l_actual_rec.dim1_level_value_name := p_target_rec.dim1_level_short_name;
588 l_actual_rec.dim2_level_value_id := p_target_rec.dim2_level_value_id;
589 l_actual_rec.dim2_level_value_name := p_target_rec.dim2_level_short_name;
590 l_actual_rec.dim3_level_value_id := p_target_rec.dim3_level_value_id;
591 l_actual_rec.dim3_level_value_name := p_target_rec.dim3_level_short_name;
592 l_actual_rec.dim4_level_value_id := p_target_rec.dim4_level_value_id;
593 l_actual_rec.dim4_level_value_name := p_target_rec.dim4_level_short_name;
594 l_actual_rec.dim5_level_value_id := p_target_rec.dim5_level_value_id;
595 l_actual_rec.dim5_level_value_name := p_target_rec.dim5_level_short_name;
596 --l_actual_rec.responsibility_id := p_target_rec.responsibility_id;
597 --l_actual_rec.responsibility_short_name := p_target_rec.responsibility_short_name;
598 --l_actual_rec.responsibility_name := p_target_rec.responsibility_name;
599
600 RETURN l_actual_rec;
601
602 END get_bis_actual_rec;
603 ------------------------------------------------------------------------
604 -- Procedure: Get_Session
605 --
606 -- RETURN: HRI_Session_Rec_Type - details of session
607 --
608 -- Parameters: None session is setup of global variables
609 --
610 -- Description
611 -- This function queries all the relevant information
612 -- required concerning the current session allowing the
613 -- session to be recreated exactly. includes:
614 -- USER
615 -- Responsibility
616 --
617 ------------------------------------------------------------------------
618 FUNCTION get_session
619 RETURN HRI_Session_Rec_Type
620 IS
624 l_session_rec.user_id := fnd_global.user_id;
621 l_session_rec hri_session_rec_type;
622
623 BEGIN
625 l_session_rec.resp_id := fnd_global.resp_id;
626 l_session_rec.resp_appl_id := fnd_global.resp_appl_id;
627 l_session_rec.security_group_id := fnd_global.security_group_id;
628
629 RETURN l_session_rec;
630
631 END get_session;
632 ------------------------------------------------------------------------
633 -- Procedure: set_session
634 --
635 -- Parameters: HRI_Session_Rec_Type
636 --
637 -- Description
638 -- This function recreates session specIFied IN Session
639 -- Rec.
640 -- USER
641 -- Responsibility
642 --
643 ------------------------------------------------------------------------
644 PROCEDURE set_session
645 (p_session_rec IN hri_session_rec_type)
646 IS
647 BEGIN
648
649 fnd_global.apps_initialize
650 (p_session_rec.user_id
651 ,p_session_rec.resp_id
652 ,p_session_rec.resp_appl_id
653 ,p_session_rec.security_group_id
654 );
655
656 END set_session;
657
658 PROCEDURE debug_session
659 (p_session_rec IN hri_session_rec_type)
660 IS
661 BEGIN
662 pl(' Session USER_ID',to_char(p_session_rec.user_id));
663 pl(' Session RESP_ID',to_char(p_session_rec.resp_id));
664 pl(' Session resp_appl_id',to_char(p_session_rec.resp_appl_id));
665 pl(' Session security_group_id',to_char(p_session_rec.security_group_id));
666 END debug_session;
667 -------------------------------------------------------------------------
668 -- hr_budget
669 -------------------------------------------------------------------------
670 --
671 -- This is function is required for targets where the target value is
672 -- obtained from the database and not entered by the user. An example of
673 -- this is manpower variance where the target value is obtained from a
674 -- HR budget.
675 --
676 --
677 FUNCTION hr_budget(p_rec IN bis_target_pub.target_rec_type) RETURN NUMBER IS
678
679 BEGIN
680 RETURN 1;
681 END;
682
683 -------------------------------------------------------------------------
684 -- Calculate percentage
685 -------------------------------------------------------------------------
686
687 FUNCTION percent(p_target IN NUMBER, p_percent IN NUMBER) RETURN NUMBER IS
688
689 BEGIN
690 RETURN (p_target/100)*nvl(p_percent,0);
691 END;
692
693
694 --**********************************************************************
695 -- TEXT TRANSLATIONS
696 --**********************************************************************
697
698 -------------------------------------------------------------------------
699 -- Translate All
700 -------------------------------------------------------------------------
701
702
703 function translate_all RETURN VARCHAR2 IS
704 BEGIN
705 fnd_message.set_name ('HRI','HR_BIS_ALL');
706 RETURN fnd_message.get;
707 END;
708
709
710 -------------------------------------------------------------------------
711 -- Translate Location
712 -------------------------------------------------------------------------
713
714 function translate_location(p_location_id IN NUMBER) RETURN VARCHAR2 IS
715 BEGIN
716 IF p_location_id = -1 THEN
717 RETURN translate_all;
718 ELSE
719 RETURN hr_general.decode_location(p_location_id);
720 END IF;
721 END;
722
723 -------------------------------------------------------------------------
724 -- Translate Job
725 -------------------------------------------------------------------------
726 -- This function accepts the job ID as a parameter and will RETURN the
727 -- job name. IF the job id is -1 THEN 'All' is RETURNed.
728 --
729 FUNCTION translate_job(p_job_id IN NUMBER) RETURN VARCHAR2 IS
730 BEGIN
731 IF p_job_id = -1 THEN
732 RETURN translate_all;
733 ELSE
734 RETURN hr_reports.get_job(p_job_id);
735 END IF;
736 END;
737
738
739 -------------------------------------------------------------------------
740 -- Translate Job Category
741 -------------------------------------------------------------------------
742 -- This function accepts the job category ID as a parameter and will return the
743 -- name of the job category. IF the job category id is -1 then 'All' is returned.
744 --
745 FUNCTION translate_job_category(p_jobcat_id IN VARCHAR2) RETURN VARCHAR2 IS
746 BEGIN
747 IF p_jobcat_id = '-1' THEN
748 RETURN translate_all;
749 ELSE
750 RETURN hr_reports.get_lookup_meaning('JOB_CATEGORIES',p_jobcat_id);
751 END IF;
752 END;
753
754
755 -------------------------------------------------------------------------
756 -- Translate include Subordinate indicator
757 -------------------------------------------------------------------------
758
759 FUNCTION translate_orgprc(p_orgprc IN VARCHAR2) RETURN VARCHAR2 IS
760
761 l_text hr_lookups.meaning%TYPE;
762
763 BEGIN
764 IF p_orgprc = 'SINR' THEN
765 l_text := Hr_General.Decode_Lookup('YES_NO','N');
766 ELSIF p_orgprc = 'SINR' THEN
767 l_text := Hr_General.Decode_Lookup('YES_NO','Y');
768 ELSE
772 END;
769 l_text := '';
770 END IF;
771 RETURN l_text;
773
774 -------------------------------------------------------------------------
775 -- Get Activity Version Name
776 -------------------------------------------------------------------------
777
778 FUNCTION get_activity_version_name
779 (p_activity_version_id IN NUMBER)
780 RETURN VARCHAR2
781 IS
782
783 l_activity_version_name ota_activity_versions.version_name%TYPE;
784
785 CURSOR av_cur (p_activity_version_id IN NUMBER) IS
786 SELECT version_name
787 FROM ota_activity_versions
788 WHERE activity_version_id = p_activity_version_id;
789
790
791 BEGIN
792
793 IF p_activity_version_id = -1 THEN
794 l_activity_version_name := translate_all;
795 ELSE
796 OPEN av_cur(p_activity_version_id);
797 FETCH av_cur INTO l_activity_version_name;
798 CLOSE av_cur;
799 END IF;
800
801 RETURN l_activity_version_name;
802
803 END;
804
805 -------------------------------------------------------------------------
806 --
807 -- Function: ORG_SECURITY_GROUP
808 --
809 --
810 -- Descrition: Get the security group of the target organzation.
811 -- This is required as a parametr when calling
812 -- apps_initialize and as a parameter on the report url.
813 --
814 -------------------------------------------------------------------------
815 FUNCTION get_org_security_group_id
816 (p_organization_id IN NUMBER)
817 RETURN NUMBER
818 IS
819
820 l_security_group_id per_business_groups.security_group_id%TYPE; --Varchar2
821 --
822 -- get the security group id
823 --
824 CURSOR sg_cur(p_organization_id IN VARCHAR2) IS
825 SELECT b.security_group_id
826 FROM per_business_groups b
827 , hr_all_organization_units o
828 where o.business_group_id = b.business_group_id
829 AND o.organization_id = p_organization_id;
830
831 BEGIN
832
833 pl('*Get_org_security_group_id','Start');
834 pl(' org_id',to_char(p_organization_id));
835
836 OPEN sg_cur(p_organization_id);
837 FETCH sg_cur INTO l_security_group_id;
838 IF sg_cur%NOTFOUND THEN
839 CLOSE sg_cur;
840 RAISE e_g_no_sg;
841 END IF;
842 CLOSE sg_cur;
843
844 pl(' security_group_id',l_security_group_id);
845 pl('*Get_org_security_group_id','End');
846 RETURN to_number(l_security_group_id);
847
848 END get_org_security_group_id;
849 -------------------------------------------------------------------------
850 --
851 -- VALIDATE THE RESPONSIBLITIES ORGANIZATION SECURITY
852 --
853 -------------------------------------------------------------------------
854 --
855 -- Verify that notify responsibility is authorized to access details from
856 -- the target organization. If the responsibility does not have access to
857 -- the organization processing will be aborted.
858 -------------------------------------------------------------------------
859 FUNCTION validate_resp_org_security
860 (p_responsibility_id IN fnd_responsibility.responsibility_id%TYPE
861 ,p_application_id IN fnd_responsibility.application_id%TYPE
862 ,p_organization_id IN hr_all_organization_units.organization_id%TYPE)
863 RETURN BOOLEAN
864 IS
865
866 l_dummy NUMBER;
867 l_status boolean;
868
869 l_business_group_id per_business_groups.business_group_id%TYPE;
870 l_security_group_id NUMBER; -- Not per_business_groups.security_group_id%TYPE this is varchar2
871 l_org_structure_version_id per_org_structure_versions.org_structure_version_id%TYPE;
872
873 -- Holds the session at the start of the function
874 -- used to return the session back to original
875 l_session_rec hri_session_rec_type;
876
877 --
878 -- OH_CUR ORGANIZATION IN HIERARCHY
879 --
880 CURSOR oh_cur(p_org_structure_version_id IN NUMBER
881 ,p_organization_id IN NUMBER) IS
882 SELECT 1
883 FROM per_org_structure_elements ose
884 WHERE ose.org_structure_version_id = p_org_structure_version_id
885 AND (ose.organization_id_child = p_organization_id
886 or ose.organization_id_parent = p_organization_id
887 ) ;
888
889 BEGIN
890
891 pl('*Validate Resp Org Security', 'Start');
892 -- Get current session
893 l_session_rec := get_session;
894 pl('Session changed at start: ');
895 debug_session(l_session_rec);
896
897 pl(' Alert - Responsibility id: ',to_char(p_responsibility_id));
898 pl(' Alert - Organization id: ',to_char(p_organization_id));
899 pl(' Alert - Resp Appl id: ',to_char(p_application_id));
900
901 l_security_group_id := get_org_security_group_id(p_organization_id);
902
903 pl(' Alert - Security Group id: ',to_char(l_security_group_id));
904 --
905 -- Changes the session globals
906 -- and get the business group and organization structure version id
907
908 -- NOTE:
909 -- Solution to issues in bug 1413300 (not inserting into actuals table)
910 -- USER_ID can not be NULL because of issues of inserting a value into a NOT NULL column
914 (-2
911 -- USER_ID can not be -1 because of condition in HR_GENERAL.Get_Business_Group
912 -- Using -2
913 hrFastAnswers.initialize
915 ,p_responsibility_id
916 ,p_application_id
917 ,l_business_group_id
918 ,l_org_structure_version_id
919 ,l_security_group_id);
920
921 pl(' Alert - Business group id: ',to_char(l_business_group_id));
922 pl(' Alert - Org structure version id: ',to_char(l_org_structure_version_id));
923 --put out current session for debug purposes
924 pl('Session changed to: ');
925 debug_session(get_session);
926
927 --
928 -- check that the organization is part of the responsibilities hierarchy
929 --
930
931 OPEN oh_cur (l_org_structure_version_id
932 ,p_organization_id);
933 FETCH oh_cur INTO l_dummy;
934 IF oh_cur%NOTFOUND THEN
935 l_status := FALSE;
936 pl('False');
937 ELSE
938 l_status := TRUE;
939 pl('True');
940 END IF;
941 CLOSE oh_cur;
942
943 --Return the session back to original
944 set_session(l_session_rec);
945 pl('Session returned to conditions at start: ');
946 debug_session(l_session_rec);
947
948 pl('*Validate Resp Org Security', 'End');
949
950 RETURN l_status;
951
952 END validate_resp_org_security;
953
954 -------------------------------------------------------------------------
955 --
956 -- PROCEDURE: get resp details
957 -- Parameter: p_resp_no
958 -- p_target_rec - hri target record type
959 -- Return params o_resp_id
960 -- o_resp_short_name
961 -- o_resp_name
962 --
963 -- Description: Simplet Proc to cut down coding.
964 -- Returns the appropriate resp detail columns for a
965 -- resp number. Resp number corresponds to the target
966 -- responsibility number.
967 -------------------------------------------------------------------------
968 PROCEDURE get_resp_details
969 (p_resp_no IN NUMBER
970 ,p_target_rec IN hri_target_rec_type
971 ,o_resp_id OUT NOCOPY fnd_responsibility.responsibility_id%TYPE
972 ,o_resp_appl_id OUT NOCOPY fnd_responsibility.application_id%TYPE
973 ,o_resp_short_name OUT NOCOPY bisfv_targets.notify_resp1_short_name%TYPE
974 ,o_resp_name OUT NOCOPY bisfv_targets.notify_resp1_name%TYPE)
975 IS
976 BEGIN
977 IF p_resp_no = 1 THEN
978 o_resp_id := p_target_rec.notify_resp1_id;
979 o_resp_appl_id := p_target_rec.notify_resp1_appl_id;
980 o_resp_short_name := p_target_rec.notify_resp1_short_name;
981 o_resp_name := p_target_rec.notify_resp1_name;
982 ELSIF p_resp_no = 2 THEN
983 o_resp_id := p_target_rec.notify_resp2_id;
984 o_resp_appl_id := p_target_rec.notify_resp2_appl_id;
985 o_resp_short_name := p_target_rec.notify_resp2_short_name;
986 o_resp_name := p_target_rec.notify_resp2_name;
987 ELSIF p_resp_no = 3 THEN
988 o_resp_id := p_target_rec.notify_resp3_id;
989 o_resp_appl_id := p_target_rec.notify_resp3_appl_id;
990 o_resp_short_name := p_target_rec.notify_resp3_short_name;
991 o_resp_name := p_target_rec.notify_resp3_name;
992 ELSE
993 RAISE no_data_found;
994 END IF;
995
996 END get_resp_details;
997 -------------------------------------------------------------------------
998 --
999 -- FUNCTION: FREQUENCY_CODE
1000 -- Description: This procedure returns the name of the time
1001 -- dimension and the equivalent HR frequency
1002 --
1003 -- Parameter: p_time_short_name - freq. name HR BIS dim code
1004 --
1005 -- Return: frequency code from FREQUENCY lookup
1006 --
1007 -------------------------------------------------------------------------
1008
1009 FUNCTION frequency_code (p_time_short_name IN VARCHAR2)
1010 RETURN VARCHAR2
1011 IS
1012
1013 l_frequency VARCHAR2(2);
1014
1015 BEGIN
1016
1017 IF p_time_short_name = 'HR MONTH' THEN
1018 l_frequency := 'CM';
1019 ELSIF p_time_short_name = 'HR BIMONTH' THEN
1020 l_frequency := 'BM';
1021 ELSIF p_time_short_name = 'HR QUARTER' THEN
1022 l_frequency := 'Q';
1023 ELSIF p_time_short_name = 'HR SEMIYEAR' THEN
1024 l_frequency := 'SY';
1025 ELSIF p_time_short_name = 'HR YEAR' THEN
1026 l_frequency := 'Y';
1027 ELSE
1028 RAISE e_g_bad_freq_code;
1029 END IF;
1030
1031 RETURN l_frequency;
1032
1033 END frequency_code;
1034
1035 -------------------------------------------------------------------------
1036 --
1037 -- PROCEDURE: get_report_date_params
1038 --
1039 -- Description: This procedure returns the report start date and freq
1040 -- code for the time short name and period start
1041 -- date.
1042 --
1043 -------------------------------------------------------------------------
1044 PROCEDURE get_report_date_params
1045 (p_time_level_short_name IN VARCHAR2
1046 ,p_period_start_date IN DATE
1047 ,o_freq_code OUT NOCOPY VARCHAR2
1051 l_report_start_date DATE;
1048 ,o_start_date OUT NOCOPY VARCHAR2)
1049 IS
1050 l_freq VARCHAR2(2);
1052
1053 BEGIN
1054
1055 l_freq := frequency_code(p_time_level_short_name);
1056
1057 IF l_freq = 'CM' THEN
1058 l_report_start_date := add_months(p_period_start_date,-12);
1059 ELSIF l_freq = 'BM' THEN
1060 l_report_start_date := add_months(p_period_start_date,-12);
1061 ELSIF l_freq = 'Q' THEN
1062 l_report_start_date := add_months(p_period_start_date,-24);
1063 ELSIF l_freq = 'SY' THEN
1064 l_report_start_date := add_months(p_period_start_date,-24);
1065 ELSIF l_freq = 'Y' THEN
1066 l_report_start_date := add_months(p_period_start_date,-48);
1067 END IF;
1068
1069 pl('frequency: ',l_freq);
1070 pl('start date: ',to_char(l_report_start_date,'DD-MON-YYYY'));
1071
1072 o_freq_code := l_freq;
1073 o_start_date := l_report_start_date;
1074
1075 END get_report_date_params;
1076
1077 -------------------------------------------------------------------------
1078 -- --
1079 -- FUNCTION: GET_FORMULA_ID --
1080 -- --
1081 -------------------------------------------------------------------------
1082 FUNCTION get_formula_id
1083 (p_business_group_id IN NUMBER
1084 ,p_budget_type IN VARCHAR2)
1085 RETURN ff_formulas.formula_id%TYPE
1086 IS
1087
1088 CURSOR formula_cur
1089 (p_c_business_group_id NUMBER
1090 ,p_c_formula_name VARCHAR2)
1091 IS
1092 SELECT formula_id
1093 FROM ff_formulas_f
1094 WHERE ( (p_c_business_group_id IS null
1095 AND business_group_id IS null )
1096 OR
1097 p_c_business_group_id = business_group_id
1098 )
1099 AND trunc(sysdate) BETWEEN
1100 effective_start_date AND effective_end_date
1101 AND formula_name = p_c_formula_name;
1102
1103 l_formula_id ff_formulas.formula_id%TYPE;
1104
1105 BEGIN
1106 pl('*Get Formula id','Start');
1107 pl(' Budgt Type : ', p_budget_type);
1108
1109 IF p_budget_type IN ('FTE','HEAD') THEN
1110 OPEN formula_cur(p_business_group_id
1111 ,'BUDGET_'||p_budget_type);
1112 FETCH formula_cur INTO l_formula_id;
1113 IF formula_cur%NOTFOUND THEN
1114 pl(' Custom ff not found: ','BUDGET_'||p_budget_type);
1115 CLOSE formula_cur;
1116 OPEN formula_cur(NULL
1117 ,'TEMPLATE_'||p_budget_type);
1118 FETCH formula_cur INTO l_formula_id;
1119 IF formula_cur%NOTFOUND THEN
1120 CLOSE formula_cur;
1121 RAISE e_g_no_ff;
1122 END IF;
1123 CLOSE formula_cur;
1124 ELSE
1125 CLOSE formula_cur;
1126 END IF;
1127
1128 -- IF the fast formula was not an FTE or HEAD FF then
1129 ELSE
1130 OPEN formula_cur(p_business_group_id
1131 ,p_budget_type);
1132 FETCH formula_cur INTO l_formula_id;
1133 IF formula_cur%NOTFOUND THEN
1134 pl(' Custom ff not found: ',p_budget_type);
1135 CLOSE formula_cur;
1136 OPEN formula_cur(null
1137 ,'TEMPLATE_'||p_budget_type);
1138 FETCH formula_cur INTO l_formula_id;
1139 IF formula_cur%NOTFOUND THEN
1140 CLOSE formula_cur;
1141 RAISE e_g_no_ff;
1142 END IF;
1143 CLOSE formula_cur;
1144 ELSE
1145 CLOSE formula_cur;
1146 END IF;
1147 END IF;
1148 RETURN l_formula_id;
1149
1150 EXCEPTION
1151 WHEN e_g_no_ff THEN
1152 pl('*Get Formula',' Exception - No FF Found - End');
1153 RAISE e_g_no_ff;
1154
1155 END get_formula_id;
1156
1157 -------------------------------------------------------------------------
1158 --
1159 -- FUNCTION: get_ORG_BUSINESS_GROUP_id(p_org_id)
1160 --
1161 -- Parameters organization id
1162 --
1163 -- RETURNs business group id
1164 --
1165 -- Description: This function returns the business group of a given
1166 -- organization. the function is used when posting
1167 -- actuals as the business group is required when
1168 -- getting the fast formula id. The fast formula ID IS
1169 -- required when calculating the assignmnet budget
1170 -- measurement value.
1171 --
1172 -------------------------------------------------------------------------
1173
1174 FUNCTION get_org_business_group_id
1175 (p_org_id IN NUMBER)
1176 RETURN NUMBER
1177 IS
1178 --
1179 -- This cursor gets the organizations business group
1180 --
1181 CURSOR c_bg_id
1182 (p_c_org_id NUMBER)
1183 IS
1184 SELECT business_group_id
1185 FROM hr_all_organization_units
1186 WHERE sysdate
1187 BETWEEN date_from
1188 AND nvl(date_to, hr_general.end_of_time)
1189 AND organization_id = p_c_org_id;
1190
1191 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
1192
1193 BEGIN
1194
1195 pl('*Get Org Business Group id','Start');
1196 pl('p_org_id',to_char(p_org_id));
1197 OPEN c_bg_id(p_org_id);
1198 FETCH c_bg_id INTO l_business_group_id;
1199 pl('p_org_id',to_char(l_business_group_id));
1200 IF c_bg_id%NOTFOUND THEN
1201 CLOSE c_bg_id;
1202 pl('raise e_g_no_bg');
1203 RAISE e_g_no_bg;
1204 END IF;
1205 CLOSE c_bg_id;
1206 pl('Business Group id',to_char(l_business_group_id));
1207 pl('*Get Org Business Group id','End');
1208 RETURN l_business_group_id;
1209
1210 END get_org_business_group_id;
1211 -------------------------------------------------------------------------
1212 --
1213 -- Post Actuals
1214 --
1215 -------------------------------------------------------------------------
1216 PROCEDURE post_actual
1217 (p_actual_rec IN bis_ACTUAL_PUB.Actual_Rec_Type)
1218 IS
1219
1220 x_error_Tbl bis_UTILITIES_PUB.Error_Tbl_Type;
1221 x_msg_count NUMBER;
1222 x_msg_data VARCHAR2(30);
1223 x_return_status VARCHAR2(30);
1224
1225 l_Measure_short_name bis_indicators.short_name%TYPE;
1226
1227 BEGIN
1228
1229 pl('*Post Actual','Start');
1230
1231 debug_bis_actual_rec(p_actual_rec);
1232
1233 IF p_actual_rec.actual IS NOT NULL THEN
1234 pl(' Posting actual...');
1235 bis_ACTUAL_PUB.Post_Actual( p_api_version => 1.0
1236 , p_commit => FND_API.G_TRUE
1237 , p_Actual_Rec => p_actual_rec
1238 , x_return_status => x_return_status
1239 , x_msg_count => x_msg_count
1240 , x_msg_data => x_msg_data
1241 , x_error_Tbl => x_error_Tbl);
1242
1243 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1244 RAISE e_g_bis_fn_error;
1245 END IF;
1246 pl(' BIS Post actual return status ', x_return_status);
1247 END IF;
1248
1249 pl('*Post Actual','End');
1250
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 -- carry on because this may be called many times
1254 pl('*Post Actual','Exception Raised - End');
1255
1256 END;
1257 -------------------------------------------------------------------------
1258 -- ********************************************************************
1259 -------------------------------------------------------------------------
1260 -- *********************************************************************
1261 --
1262 -- START OF PMF DEFINITIONS
1263 --
1264 -- *********************************************************************
1265 -------------------------------------------------------------------------
1266 -- *********************************************************************
1267 -------------------------------------------------------------------------
1268
1269 -- **********************************************************************
1270 --
1271 -- M A N P O W E R S E P A R A T I O N S
1272 --
1273 -- **********************************************************************
1274
1275 -------------------------------------------------------------------------
1276 -- SEND MANPOWER SEPARATION notification
1277 -------------------------------------------------------------------------
1278 -- This procedure is called when the actual manpower separations exceed
1279 -- the target value. The procedure will build the notification message
1280 -- text, which includes a hypertext link to run the manpower losses report,
1281 -- and will then pass the notification to the workflow engine for
1282 -- dIStribution.
1283 --
1284 PROCEDURE send_notification_hrmsp
1285 (p_resp_no IN NUMBER
1286 ,p_target IN NUMBER
1287 ,p_actual IN NUMBER
1288 ,p_target_rec IN hri_target_rec_type)
1289 IS
1290
1291 l_bmt_name hr_lookups.meaning%TYPE;
1292 l_dummy VARCHAR2(2000);
1293 l_freq_code VARCHAR2(4);
1294 l_subject fnd_new_messages.message_text%TYPE;
1295 l_message VARCHAR2(2000);
1296 l_org_name per_organization_units.name%TYPE;
1297 l_period_name VARCHAR2(50);
1298 l_param VARCHAR2(2000);
1299 l_report_name VARCHAR2(8) := 'HRMNPLOS';
1300 l_return_status VARCHAR2(2000);
1301
1302 l_start_date DATE;
1303
1304 l_error VARCHAR2(2000);
1305
1306 -- Session params
1307 l_security_group_id per_business_groups.security_group_id%TYPE;
1308 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
1309 l_resp_id fnd_responsibility.responsibility_id%TYPE;
1310 l_resp_short_name bisfv_targets.notify_resp1_short_name%TYPE;
1311 l_resp_name bisfv_targets.notify_resp1_name%TYPE;
1312 l_resp_appl_id fnd_responsibility.application_id%TYPE;
1313
1314 l_formula_id ff_formulas_f.formula_id%TYPE;
1315
1316 BEGIN
1317 pl('----------------------------------');
1318 pl('*SEND MPS notification','Start');
1319 pl('----------------------------------');
1320 -----------------------------------------------------------------------
1321 -- Build the notification subject line and message text
1322 -----------------------------------------------------------------------
1323 --
1324 -- Get the following information that will be shown on the notification
1325 -- and populate the relevant tokens in the message HR_BIS_PMF_SUBJECT
1326 --
1327 -- 1. Get Session parameters for the report
1328 -- 2. Get Parameter Names:
1332 -- 3. build notification text
1329 -- Organzation
1330 -- Budget Measurement Type
1331 -- Period
1333 -- 4. get the report frequency, number of periods and start date
1334 -- 5. build the report parameter string
1335 -- 6. start the workflow process
1336 --
1337 -- ---------------------------------------------------------------------
1338 -- 1. Get Session parameters for the report
1339 -- ---------------------------------------------------------------------
1340 get_resp_details(p_resp_no
1341 ,p_target_rec
1342 ,l_resp_id
1343 ,l_resp_appl_id
1344 ,l_resp_short_name
1345 ,l_resp_name);
1346 l_business_group_id := get_org_business_group_id(p_target_rec.org_level_value_id);
1347 l_security_group_id := get_org_security_group_id(p_target_rec.org_level_value_id);
1348 -- ---------------------------------------------------------------------
1349 -- 2. Get the parameter names
1350 -- ---------------------------------------------------------------------
1351 --
1352 -- Organization
1353 --
1354 hr_reports.get_organization(p_target_rec.org_level_value_id
1355 ,l_org_name
1356 ,l_dummy);
1357 pl(' org name : ',l_org_name);
1358 --
1359 -- Budget Name
1360 --
1361 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
1362 l_bmt_name := hr_reports.get_lookup_meaning('BUDGET_MEASUREMENT_TYPE'
1363 ,p_target_rec.unit_of_measure);
1364 pl(' bmt name : ',l_bmt_name);
1365 --
1366 -- Period Name
1367 --
1368 l_period_name := to_char(p_target_rec.period_start_date,'DD-MON-YYYY')
1369 ||' - '||to_char(p_target_rec.period_END_date,'DD-MON-YYYY');
1370 pl(' period name :',l_period_name);
1371 -- ---------------------------------------------------------------------
1372 -- 3. Build the notification text
1373 -- ---------------------------------------------------------------------
1374 --
1375 -- Build subject message text
1376 --
1377 fnd_message.set_name('HRI','HR_BIS_PMF_HRMSP_SUBJECT');
1378 l_subject := fnd_message.get;
1379 --
1380 -- Build the body message text
1381 --
1382 fnd_message.set_name('HRI','HR_BIS_PMF_HRMSP_MSG');
1383 fnd_message.set_token('ORGANIZATION',l_org_name);
1384 fnd_message.set_token('PERIOD', l_period_name);
1385 fnd_message.set_token('BUDGET',l_bmt_name);
1386 fnd_message.set_token('TARGET',p_target);
1387 fnd_message.set_token('ACTUAL',p_actual);
1388 --fnd_message.set_token('ROLE',l_resp_name);
1389 l_message := fnd_message.get;
1390 pl(' Message:',l_message);
1391 -- ---------------------------------------------------------------------
1392 -- 4. build the report parameter string
1393 -- ---------------------------------------------------------------------
1394 -- Build the url that will be used to run the report from the
1395 -- notification.
1396 l_report_name := 'HRMNPLOS';
1397
1398 get_report_date_params(p_target_rec.time_level_short_name
1399 ,p_target_rec.period_start_date
1400 ,l_freq_code
1401 ,l_start_date);
1402 pl(' Frequency: ',l_freq_code);
1403 pl(' Start date: ',to_char(l_start_date,'DD-MON-YYYY'));
1404
1405 -- Added formula id as Workforce Losses requires it for fast formula
1406 -- Bug 2530846
1407 l_formula_id := get_formula_id(l_business_group_id
1408 ,p_target_rec.unit_of_measure);
1409 pl(' Formula_id: ',to_char(l_formula_id));
1410
1411
1412 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
1413 l_param := 'org_id=' ||p_target_rec.org_level_value_id ||'*'||
1414 'orgprc=' ||g_orgprc_const ||'*'||
1415 'bpl_id=' ||p_target_rec.plan_id ||'*'||
1416 'bgttyp=' ||p_target_rec.unit_of_measure||'*'||
1417 'bus_id=' ||to_char(l_business_group_id)||'*'||
1418 'formul=' ||to_char(l_formula_id) ||'*'||
1419 'frqncy=' ||l_freq_code ||'*'||
1420 'geolvl=1*' ||
1421 'geoval=-1*' ||
1422 'job_id=-1*' ||
1423 'jobcat=__ALL__*'||
1424 'prodid=-1*' ||
1425 'startd=' ||to_char(l_start_date,'YYYY-MM-DD')||'*'||
1426 'end_dt=' ||to_char(p_target_rec.period_end_date,'YYYY-MM-DD') ||'*'||
1427 'viewby=HR_BIS_TIME*'||
1428 c_ampersand||'responsibility_application_id='
1429 ||to_char(l_resp_appl_id)||
1430 c_ampersand||'security_group_id='
1431 ||l_security_group_id;
1432
1433 pl('wf ',p_target_rec.workflow_item_type);
1434 pl('process ',p_target_rec.workflow_process_short_name);
1435 pl('resp ',l_resp_short_name);
1436 pl('resp id ',to_char(l_resp_id));
1437 pl('applic ',to_char(l_resp_appl_id));
1438 pl('secgrp ',l_security_group_id);
1439 pl('param ',substr(l_param,1,240));
1440 pl('report ',l_report_name);
1441 pl('message ',substr(l_message,1,240));
1442 pl('subject ',l_subject);
1443 -- ---------------------------------------------------------------------
1447 bis_util.strt_wf_process
1444 -- 5. start the workflow process
1445 -- ---------------------------------------------------------------------
1446 pl('start workflow process');
1448 (p_exception_message => l_message
1449 ,p_msg_subject => l_subject
1450 ,p_exception_date => sysdate
1451 ,p_item_type => p_target_rec.workflow_item_type
1452 ,p_wf_process => p_target_rec.workflow_process_short_name
1453 ,p_notify_resp_name => l_resp_short_name
1454 ,p_report_name1 => l_report_name
1455 ,p_report_param1 => l_param
1456 ,p_report_resp1_id => l_resp_id
1457 ,x_return_status => l_return_status);
1458
1459 pl(' Resp id',to_char(l_resp_id));
1460
1461 pl(' Workflow return status: ',l_return_status);
1462
1463 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1464 RAISE e_g_bis_fn_error;
1465 END IF;
1466
1467 pl('*SEND Manpower Separation notification','End');
1468
1469 END send_notification_hrmsp;
1470
1471 -----------------------------------------------------------------------
1472 -- CALCULATE ACTUAL MANPOWER SEPARATIONS
1473 -----------------------------------------------------------------------
1474
1475 FUNCTION hrmsp_actual
1476 (p_actual_rec IN bis_actual_pub.actual_rec_type
1477 ,p_bmtype IN VARCHAR2)
1478 RETURN NUMBER
1479 IS
1480
1481 l_budget_value NUMBER(38,2); -- budget measurement value
1482 l_loss_type VARCHAR2(20); -- type of separation
1483 l_separated_count NUMBER(38,2); -- count of separated people
1484
1485 l_period_start_date DATE;
1486 l_period_end_date DATE;
1487
1488 l_business_group_id NUMBER;
1489
1490 l_formula_id ff_formulas_f.formula_id%TYPE;
1491
1492 --
1493 -- get each assignment that has separated IN the period defined
1494 -- by the target time dimension
1495 --
1496 -- This Cursor has been changed due to bug 1718083 to be exactly the same
1497 -- as the Workforce Seperations report. JRHYDE
1498 --
1499 -- A Loss occurs when the last day of a person's assignment occurs prior to
1500 -- the date of the period and the asg start date is prior to the first day of
1501 -- the period
1502 CURSOR asg_cur
1503 (p_c_organization_id IN NUMBER
1504 ,p_c_period_start_date IN date
1505 ,p_c_period_end_date IN date) IS
1506 SELECT asg.assignment_id
1507 , asg.effective_end_date
1508 FROM per_all_assignments_f asg
1509 , per_assignment_status_types ast
1510 WHERE p_c_period_start_date-1 between
1511 asg.effective_start_date and
1512 asg.effective_end_date
1513 AND asg.assignment_type = 'E'
1514 AND asg.organization_id = p_c_organization_id
1515 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1516 AND ast.per_system_status = 'ACTIVE_ASSIGN'
1517 AND NOT EXISTS
1518 (SELECT null
1519 FROM per_all_assignments_f asg2,
1520 per_assignment_status_types ast2
1521 WHERE p_c_period_end_date between
1522 asg2.effective_start_date and
1523 asg2.effective_end_date
1524 AND asg2.assignment_type = 'E'
1525 AND asg2.assignment_id = asg.assignment_id
1526 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
1527 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
1528 AND asg2.organization_id = p_c_organization_id);
1529
1530 BEGIN
1531 pl('*HRMSP ACTUAL','Start');
1532
1533 --
1534 -- Get the fast formula ID of the FTE or HEAD. This is used to
1535 -- calculate the budget measurement value of an employees assignment.
1536 --
1537
1538 --Business Group is that of the organization of the - JRHYDE
1539 l_business_group_id := get_org_business_group_id(to_number(p_actual_rec.org_level_value_id));
1540
1541 l_formula_id := get_formula_id(l_business_group_id
1542 ,p_bmtype);
1543
1544 pl('fast formula id: ',to_char(l_formula_id));
1545
1546 --Get start and end dates from the time dim
1547 get_dates_from_time_dim
1548 (p_actual_rec.time_level_value_id
1549 ,l_period_start_date
1550 ,l_period_end_date);
1551
1552 --
1553 -- Loop through each employee assignment that separated in the period defined
1554 -- by the PMF time dimesnion and then calculate the budget measurement value
1555 -- using the budget measurement type specified as a unit of measurement.
1556 --
1557 pl('Loop through employees separated in the target period');
1558 l_separated_count := 0;
1559 FOR asg_rec IN asg_cur
1560 (to_number(p_actual_rec.org_level_value_id)
1561 ,l_period_start_date
1562 ,l_period_end_date) LOOP
1563
1564 pl(' asg id: ',to_char(asg_rec.assignment_id));
1565 l_loss_type := hrfastanswers.getassignmentcategory
1566 (p_assignment_id => asg_rec.assignment_id
1567 ,p_period_start_date => l_period_start_date
1568 ,p_period_END_date => l_period_end_date
1569 ,p_top_org => p_actual_rec.org_level_value_id
1570 ,p_movement_type => 'OUT');
1571
1572 pl(' loss: ',l_loss_type);
1573
1574 IF l_loss_type = 'SEPARATED' THEN
1575
1576 l_budget_value := hrfastanswers.getbudgetvalue
1577 (p_budget_metric_formula_id => l_formula_id
1578 ,p_budget_metric => p_bmtype
1579 ,p_assignment_id => asg_rec.assignment_id
1580 ,p_effective_date => asg_rec.effective_end_date
1581 ,p_session_date => sysdate);
1582
1583 pl(p_bmtype
1584 ,to_char(l_budget_value));
1585
1586 l_separated_count := l_separated_count + l_budget_value;
1587 END IF;
1588
1589 END LOOP;
1590
1591 pl('actual = ',to_char(l_separated_count));
1592 pl('*HRMSP ACTUAL','End');
1593
1594 RETURN l_separated_count;
1595
1596 EXCEPTION
1597 WHEN e_g_no_bg THEN
1598 pl('No BG found for FF');
1599 RAISE e_g_no_ff_bg;
1600
1601 END;
1602
1603 -------------------------------------------------------------------------
1604 -- PROCESS MANPOWER SEPARATION TARGETS
1605 -------------------------------------------------------------------------
1606
1607 PROCEDURE process_hrmsp
1608 (p_target_rec IN hri_target_rec_type)
1609 IS
1610
1611 l_actual NUMBER(22,2);
1612 l_mod_target bisfv_targets.target%TYPE;
1613
1614 l_actual_rec bis_actual_pub.actual_rec_type;
1615
1616 BEGIN
1617
1618 pl('*PROCESS MANPOWER SEPARATION TARGET','Start');
1619
1620 /* IS THIS ACTUALLY NEEDED -JRHYDE 20/12/00
1621 hrFastAnswers.LoadOrgHierarchy(p_target_rec.org_level_value_id
1622 ,g_org_structure_version_id);*/
1623
1624 -- Convert the data from target record format into an actual record format
1625 l_actual_rec := get_bis_actual_rec(p_target_rec);
1626
1627 IF p_target_rec.measure_short_name = 'HRMSPFTE' THEN
1628 l_actual := hrmsp_actual(l_actual_rec,'FTE');
1629 ELSIF p_target_rec.measure_short_name = 'HRMSPHEAD' THEN
1630 l_actual := hrmsp_actual(l_actual_rec,'HEAD');
1631 END IF;
1632
1633 pl('actual: ',to_char(l_actual));
1634 pl('target: ',to_char(p_target_rec.target));
1635
1636 --
1637 -- Send a notification if
1638 -- The actual value of workforce separations is
1639 -- greater than the target.
1640 -- If a value of -1 has been returned
1641 -- then abort processing as an error has occurred.
1642 --
1643 -- This process will be repeated for each of the three levels
1644 -- of notification that are available
1645 --
1646 IF l_actual = -1 THEN
1647 pl('Actual = -1','No workflow');
1648 null;
1649 ELSE
1650 IF p_target_rec.notify_resp1_id IS NOT NULL THEN
1651 /* 115.11 */
1652 IF validate_resp_org_security
1653 (p_target_rec.notify_resp1_id
1654 ,p_target_rec.notify_resp1_appl_id
1655 ,to_number(p_target_rec.org_level_value_id)) THEN
1656 l_mod_target := p_target_rec.target
1657 + ((p_target_rec.target/100)*nvl(p_target_rec.range1_high,0));
1658 pl(' Processing responsibility 1: ',p_target_rec.notify_resp1_short_name);
1659 pl(' Modified target: ',l_mod_target);
1660 pl(' Actual: ',l_actual);
1661 IF l_actual > l_mod_target THEN
1662 send_notification_hrmsp(1
1663 ,l_mod_target
1664 ,l_actual
1665 ,p_target_rec);
1666 END IF; -- actual < target
1667 END IF; -- valid resp1_id security
1668 END IF; --notify_resp1_id not null
1669
1670 IF p_target_rec.notify_resp2_id IS NOT NULL THEN
1671 IF validate_resp_org_security
1672 (p_target_rec.notify_resp2_id
1673 ,p_target_rec.notify_resp2_appl_id
1674 ,to_number(p_target_rec.org_level_value_id)) THEN
1675 l_mod_target := p_target_rec.target
1676 + ((p_target_rec.target/100)*nvl(p_target_rec.range2_high,0));
1677 pl(' Processing responsibility 2: ',p_target_rec.notify_resp2_short_name);
1678 pl(' Modified target: ',l_mod_target);
1679 pl(' Actual: ',l_actual);
1680 IF l_actual > l_mod_target THEN
1681 send_notification_hrmsp(2
1682 ,l_mod_target
1683 ,l_actual
1684 ,p_target_rec);
1685 END IF; -- actual < target
1686 END IF; -- valid resp2_id security
1687 END IF; --notify_resp2_id not null
1688
1689 IF p_target_rec.notify_resp3_id IS NOT NULL THEN
1690 IF validate_resp_org_security
1691 (p_target_rec.notify_resp3_id
1692 ,p_target_rec.notify_resp3_appl_id
1693 ,to_number(p_target_rec.org_level_value_id)) THEN
1694 l_mod_target := p_target_rec.target
1695 + ((p_target_rec.target/100)*nvl(p_target_rec.range3_high,0));
1696 pl(' Processing responsibility 3: ',p_target_rec.notify_resp3_short_name);
1697 pl(' Modified target: ',l_mod_target);
1698 pl(' Actual: ',l_actual);
1699 IF l_actual > l_mod_target THEN
1700 send_notification_hrmsp(3
1701 ,l_mod_target
1702 ,l_actual
1703 ,p_target_rec);
1704 END IF; -- actual < target
1705 END IF; -- valid resp3_id security
1706 END IF; --notify_resp3_id not null
1707 END IF; --actual = -1
1711 -- **********************************************************************
1708 pl('*PROCESS MANPOWER SEPARATION TARGET','End');
1709 END;
1710
1712 --
1713 -- M A N P O W E R V A R I A N C E
1714 --
1715 -- **********************************************************************
1716
1717 -------------------------------------------------------------------------
1718 --
1719 -- SEND A MANPOWER VARIANCE notification
1720 --
1721 -------------------------------------------------------------------------
1722 --
1723 -- This procedure builds the notification message including the URL
1724 -- to run the manpoiwer variance report and issues a call to the workflow
1725 -- engine to send the notification.
1726 --
1727
1728 PROCEDURE send_notification_hrmvr
1729 (p_type IN VARCHAR2
1730 ,p_resp_no IN NUMBER --No. between 1-3 signifying which resp
1731 ,p_target IN NUMBER
1732 ,p_actual IN NUMBER
1733 ,p_target_rec IN hri_target_rec_type)
1734 IS
1735
1736 l_subject fnd_new_messages.message_text%TYPE;
1737 l_message VARCHAR2(2000);
1738 l_freq_code VARCHAR2(4);
1739 l_dummy VARCHAR2(2000);
1740 l_start_date date;
1741 l_report_name VARCHAR2(8);
1742 l_return_status VARCHAR2(2000);
1743 l_error VARCHAR2(2000);
1744 l_param VARCHAR2(2000);
1745
1746 -- Dimension Params
1747 l_org_name per_organization_units.name%TYPE;
1748 l_org_type hr_lookups.meaning%TYPE;
1749 l_orgprc hr_lookups.meaning%TYPE;
1750 l_bmt_name hr_lookups.meaning%TYPE;
1751 l_job_name per_jobs.name%TYPE;
1752 l_job_cat hr_lookups.meaning%TYPE;
1753 l_loc_name hr_locations.location_code%TYPE;
1754
1755 l_period_name VARCHAR2(50);
1756
1757 -- Session params
1758 l_security_group_id per_business_groups.security_group_id%TYPE;
1759 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
1760 l_resp_id fnd_responsibility.responsibility_id%TYPE;
1761 l_resp_short_name bisfv_targets.notify_resp1_short_name%TYPE;
1762 l_resp_name bisfv_targets.notify_resp1_name%TYPE;
1763 l_resp_appl_id fnd_responsibility.application_id%TYPE;
1764
1765 BEGIN
1766
1767 pl('-----------------------------------');
1768 pl('*SEND Manpower Variance notification','Start');
1769 pl('-----------------------------------');
1770 pl(' type: ',p_type);
1771 pl(' responsibility no',to_char(p_resp_no));
1772 pl(' actual: ',to_char(p_actual));
1773 -----------------------------------------------------------------------
1774 -- Build the notification subject line and message text
1775 -----------------------------------------------------------------------
1776 --
1777 -- Get the following information that will be shown on the notification
1778 -- and populate the relevant tokens in the message HR_BIS_PMF_SUBJECT
1779 --
1780 -- 1. Get Session parameters for the report
1781 -- 2. Get Parameter Names:
1782 -- Organzation name
1783 -- Location
1784 -- Job or Job Category
1785 -- Budget Measurement Type Name
1786 -- Period name
1787 -- 3. build notification text
1788 -- 4. get the report frequency, number of periods and start date
1789 -- 5. build the report parameter string
1790 -- 6. start the workflow process
1791 --
1792 -- ---------------------------------------------------------------------
1793 -- 1. Get Session parameters for the report
1794 -- ---------------------------------------------------------------------
1795 get_resp_details(p_resp_no
1796 ,p_target_rec
1797 ,l_resp_id
1798 ,l_resp_appl_id
1799 ,l_resp_short_name
1800 ,l_resp_name);
1801 l_business_group_id := get_org_business_group_id(p_target_rec.org_level_value_id);
1802 l_security_group_id := get_org_security_group_id(p_target_rec.org_level_value_id);
1803 -- ---------------------------------------------------------------------
1804 -- 2. Get the parameter names
1805 -- ---------------------------------------------------------------------
1806 --
1807 -- Organization
1808 --
1809 hr_reports.get_organization(p_target_rec.org_level_value_id,l_org_name,l_org_type);
1810 l_orgprc := translate_orgprc(g_orgprc_const);
1811 pl(' org name: ',l_org_name);
1812 --
1813 -- Location
1814 --
1815 l_loc_name := translate_location(p_target_rec.dim1_level_value_id);
1816 pl(' loc name: ',l_loc_name);
1817 --
1818 -- Job / Job Category
1819 --
1820 IF p_target_rec.dim2_level_short_name = 'TOTAL GEOGRAPHY' THEN
1821 l_job_name := translate_all;
1822 l_job_cat := translate_all;
1823 ELSIF p_target_rec.dim2_level_short_name = 'JOB' THEN
1824 l_job_name := translate_job(p_target_rec.dim2_level_value_id);
1825 l_job_cat := translate_all;
1826 ELSIF p_target_rec.dim2_level_short_name = 'JOB CATEGORY' THEN
1827 l_job_name := translate_all;
1828 l_job_cat := translate_job_category(p_target_rec.dim2_level_value_id);
1829 END IF;
1830 pl(' job name: ',l_job_name);
1831 pl(' job category name: ',l_job_cat);
1832 --
1833 -- Budget
1834 --
1835 l_bmt_name := hr_reports.get_lookup_meaning
1836 ('BUDGET_MEASUREMENT_TYPE',p_target_rec.unit_of_measure);
1837 pl(' bmt name: ',l_bmt_name);
1841 l_period_name := to_char(p_target_rec.period_start_date,'DD-MON-YYYY')
1838 --
1839 -- Period
1840 --
1842 ||' - '||to_char(p_target_rec.period_END_date,'DD-MON-YYYY');
1843 pl(' Period Name:',l_period_name);
1844 -- ---------------------------------------------------------------------
1845 -- 2. Build the notification text
1846 -- ---------------------------------------------------------------------
1847 --
1848 -- Build subject message text
1849 --
1850 IF p_type = 'ABOVE' THEN
1851 fnd_message.set_name('HRI','HR_BIS_PMF_HRMVR_HIGH_SUBJECT');
1852 l_subject := fnd_message.get;
1853 ELSE
1854 fnd_message.set_name('HRI','HR_BIS_PMF_HRMVR_LOW_SUBJECT');
1855 l_subject := fnd_message.get;
1856 END IF;
1857 --
1858 -- Build the body message text
1859 --
1860 IF p_type = 'ABOVE' THEN
1861 fnd_message.set_name('HRI','HR_BIS_PMF_HRMVR_HIGH_MSG');
1862 ELSE
1863 fnd_message.set_name('HRI','HR_BIS_PMF_HRMVR_LOW_MSG');
1864 END IF;
1865 fnd_message.set_token('ORGANIZATION',l_org_name);
1866 fnd_message.set_token('JOB',l_job_name);
1867 fnd_message.set_token('CATEGORY',l_job_cat);
1868 fnd_message.set_token('LOCATION',l_loc_name);
1869 fnd_message.set_token('PERIOD',l_period_name);
1870 fnd_message.set_token('BUDGET',l_bmt_name);
1871 fnd_message.set_token('TARGET',p_target);
1872 fnd_message.set_token('ACTUAL',p_actual);
1873 --fnd_message.set_token('ROLE',l_resp_name);
1874 l_message := fnd_message.get;
1875 pl(' Message:',l_message);
1876
1877 -- ---------------------------------------------------------------------
1878 -- 4. build the report parameter string
1879 -- ---------------------------------------------------------------------
1880 -- Build the url that will be used to run the report from the
1881 -- notification.
1882 l_report_name := 'HRMNPSUM';
1883
1884 get_report_date_params(p_target_rec.time_level_short_name
1885 ,p_target_rec.period_start_date
1886 ,l_freq_code
1887 ,l_start_date);
1888 pl(' Frequency: ',l_freq_code);
1889 pl(' Start date: ',to_char(l_start_date,'DD-MON-YYYY'));
1890
1891 l_param := 'org_id=' ||p_target_rec.org_level_value_id ||'*'||
1892 'orgprc=' ||g_orgprc_const ||'*'||
1893 'bpl_id=' ||p_target_rec.plan_id ||'*'||
1894 'bgttyp=' ||p_target_rec.unit_of_measure ||'*'||
1895 'frqncy=' ||l_freq_code ||'*'||
1896 'geolvl=1*' ||
1897 'geoval=-1*' ||
1898 'bus_id=' ||to_char(l_business_group_id) ||'*'||
1899 'prodid=-1*' ||
1900 'startd=' ||to_char(l_start_date,'YYYY-MM-DD')||'*'||
1901 'end_dt=' ||to_char(p_target_rec.period_END_date,'YYYY-MM-DD')||'*'||
1902 'viewby=HR_BIS_TIME*';
1903
1904 IF p_target_rec.dim2_level_short_name = 'JOB' THEN
1905 l_param := l_param ||
1906 'job_id=' ||p_target_rec.dim2_level_value_id ||'*'||
1907 'jobcat=__ALL__*';
1908 ELSIF p_target_rec.dim1_level_short_name = 'JOB CATEGORY' THEN
1909 l_param := l_param ||
1910 'job_id=-1*' ||
1911 'jobcat=' ||p_target_rec.dim2_level_value_id ||'*';
1912 ELSE
1913 l_param := l_param ||
1914 'job_id=-1*' ||
1915 'jobcat=__ALL__*';
1916 END IF;
1917
1918 l_param := l_param ||
1919 c_ampersand||'responsibility_application_id='
1920 ||to_char(l_resp_appl_id)||
1921 c_ampersand||'security_group_id='
1922 ||l_security_group_id;
1923
1924 pl('wf ',p_target_rec.workflow_item_type);
1925 pl('process ',p_target_rec.workflow_process_short_name);
1926 pl('resp ',l_resp_short_name);
1927 pl('resp id ',to_char(l_resp_id));
1928 pl('applic ',to_char(l_resp_appl_id));
1929 pl('secgrp ',l_security_group_id);
1930 pl('param ',substr(l_param,1,240));
1931 pl('report ',l_report_name);
1932 pl('message ',substr(l_message,1,240));
1933 pl('subject ',l_subject);
1934 -- ---------------------------------------------------------------------
1935 -- 5. start the workflow process
1936 -- ---------------------------------------------------------------------
1937 pl('start workflow process');
1938 bis_util.strt_wf_process
1939 (p_exception_message => l_message
1940 ,p_msg_subject => l_subject
1941 ,p_exception_date => sysdate
1942 ,p_item_type => p_target_rec.workflow_item_type
1943 ,p_wf_process => p_target_rec.workflow_process_short_name
1944 ,p_notify_resp_name => l_resp_short_name
1945 ,p_report_name1 => l_report_name
1946 ,p_report_param1 => l_param
1947 ,p_report_resp1_id => l_resp_id
1948 ,x_return_status => l_return_status);
1949
1950 pl(' Resp id : ',to_char(l_resp_id));
1951
1952 pl(' Workflow return status : ',l_return_status);
1953
1954 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1955 RAISE e_g_bis_fn_error;
1956 END IF;
1957
1958 pl('*SEND Manpower Variance notification','End');
1959
1960 END;
1961 -------------------------------------------------------------------------
1962 --
1963 -- Compare actual to target
1964 --
1965 -------------------------------------------------------------------------
1966 PROCEDURE hrmvr_compare_values
1967 (p_target_rec IN hri_target_rec_type
1968 ,p_actual IN NUMBER) IS
1969
1970 l_mod_target bisfv_targets.target%TYPE;
1971
1972 BEGIN
1973 --
1974 -- process range 1
1975 --
1976 IF p_target_rec.notify_resp1_id IS NOT NULL THEN
1977 IF validate_resp_org_security
1978 (p_target_rec.notify_resp1_id
1979 ,p_target_rec.notify_resp1_appl_id
1980 ,to_number(p_target_rec.org_level_value_id)) THEN
1981 IF p_target_rec.range1_high IS NOT NULL THEN
1982 l_mod_target := p_target_rec.target + ((p_target_rec.target/100)*p_target_rec.range1_high);
1983 pl(' Processing responsibility 1: ',p_target_rec.notify_resp1_short_name);
1984 pl(' Modified target: ',l_mod_target);
1985 pl(' Actual: ',p_actual);
1986 IF p_actual > l_mod_target THEN
1987 send_notification_hrmvr('ABOVE'
1988 ,1
1989 ,l_mod_target
1990 ,p_actual
1991 ,p_target_rec);
1992 END IF;
1993 END IF; --range high
1994 IF p_target_rec.range1_low IS NOT NULL THEN
1995 l_mod_target := p_target_rec.target
1996 - ((p_target_rec.target/100)*p_target_rec.range1_low);
1997 pl(' Processing responsibility 1: ',p_target_rec.notify_resp1_short_name);
1998 pl(' Modified target: ',l_mod_target);
1999 pl(' Actual: ',p_actual);
2000 IF p_actual < l_mod_target THEN
2001 send_notification_hrmvr('BELOW'
2002 ,1
2003 ,l_mod_target
2004 ,p_actual
2005 ,p_target_rec);
2006 END IF;
2007 END IF; -- range low
2008 END IF; -- valid resp1_id security
2009 END IF; --notify_resp1_id not null
2010
2011 --
2012 -- process range 2
2013 --
2014 IF p_target_rec.notify_resp2_id IS NOT NULL THEN
2015 IF validate_resp_org_security
2016 (p_target_rec.notify_resp2_id
2017 ,p_target_rec.notify_resp2_appl_id
2018 ,to_number(p_target_rec.org_level_value_id)) THEN
2019 IF p_target_rec.range2_high IS NOT NULL THEN
2020 l_mod_target := p_target_rec.target
2021 + ((p_target_rec.target/100)*p_target_rec.range2_high);
2022 pl(' Processing responsibility 2: ',p_target_rec.notify_resp2_short_name);
2023 pl(' Modified target: ',l_mod_target);
2024 pl(' Actual: ',p_actual);
2025 IF p_actual > l_mod_target THEN
2026 send_notification_hrmvr('ABOVE'
2027 ,2
2028 ,l_mod_target
2029 ,p_actual
2030 ,p_target_rec);
2031 END IF;
2032 END IF; -- range high
2033 IF p_target_rec.range2_low IS NOT NULL THEN
2034 l_mod_target := p_target_rec.target
2035 - ((p_target_rec.target/100)*p_target_rec.range2_low);
2036 pl(' Processing responsibility 2: ',p_target_rec.notify_resp2_short_name);
2037 pl(' Modified target: ',l_mod_target);
2038 pl(' Actual: ',p_actual);
2039 IF p_actual < l_mod_target THEN
2040 send_notification_hrmvr('BELOW'
2041 ,2
2042 ,l_mod_target
2043 ,p_actual
2044 ,p_target_rec);
2045 END IF;
2046 END IF; -- range low
2047 END IF; -- valid resp2_id security
2048 END IF; --notify_resp2_id not null
2049
2050 --
2051 -- process range 3
2052 --
2053 IF p_target_rec.notify_resp3_id IS NOT NULL THEN
2054 IF validate_resp_org_security
2055 (p_target_rec.notify_resp3_id
2056 ,p_target_rec.notify_resp3_appl_id
2057 ,to_number(p_target_rec.org_level_value_id)) THEN
2058 IF p_target_rec.range3_high IS NOT NULL THEN
2059 l_mod_target := p_target_rec.target
2060 + ((p_target_rec.target/100)*p_target_rec.range3_high);
2061 pl(' Processing responsibility 3: ',p_target_rec.notify_resp3_short_name);
2062 pl(' Modified target: ',l_mod_target);
2063 pl(' Actual: ',p_actual);
2064 IF p_actual > l_mod_target THEN
2065 send_notification_hrmvr('ABOVE'
2066 ,3
2067 ,l_mod_target
2068 ,p_actual
2069 ,p_target_rec);
2070 END IF;
2071 END IF; -- range high
2072 IF p_target_rec.range3_low IS NOT NULL THEN
2073 l_mod_target := p_target_rec.target
2074 - ((p_target_rec.target/100)*p_target_rec.range3_low);
2075 pl(' Processing responsibility 3: ',p_target_rec.notify_resp3_short_name);
2076 pl(' Modified target: ',l_mod_target);
2077 pl(' Actual: ',p_actual);
2078 IF p_actual < l_mod_target THEN
2079 send_notification_hrmvr('BELOW'
2080 ,3
2081 ,l_mod_target
2082 ,p_actual
2083 ,p_target_rec);
2084 END IF;
2085 END IF; -- range low
2086 END IF; -- valid resp3_id security
2087 END IF; --notify_resp3_id not null
2088
2089 END hrmvr_compare_values;
2090
2091 -------------------------------------------------------------------------
2092 --
2093 -- CALCULATE THE ACTUAL MANPOWER VARIANCE
2094 --
2095 -------------------------------------------------------------------------
2096 --
2097 -- This procedure will calculate the total actual manpower as of today
2098 -- filtered using the target dimensions.
2099 --
2100
2101 FUNCTION hrmvr_actual
2102 (p_actual_rec IN bis_actual_pub.actual_rec_type
2103 ,p_bmtype IN VARCHAR2)
2104 RETURN NUMBER
2105 IS
2106
2107 l_budget_value NUMBER(20,2) := 0;
2108 l_total_manpower NUMBER(20,2) := 0;
2109
2110 l_business_group_id NUMBER;
2111
2112 l_period_start_date DATE;
2113 l_period_end_date DATE;
2114
2115 l_formula_id ff_formulas.formula_id%TYPE;
2116
2117 --
2118 -- Get the total manpower matching the target dimensions
2119 --
2120 -- This Cursor has been changed due to bug 1718083 to be exactly the same
2121 -- as the Workforce Total report. JRHYDE
2122 --
2123 -- To make this the same as the report have to calculate the abv at the end
2124 -- of the period. As total workforce is counted when an assignment exists
2125 -- on the end date and the start date. Regardless of whether it is the same date track row,
2126 -- as long as it is the same assignment. But the report also factors in any
2127 -- Gains occuring during the period. So this equates to all assignments at the
2128 -- end of a period.
2129 CURSOR mv_cur
2130 (p_c_business_group_id NUMBER
2131 ,p_c_actual_rec bis_actual_pub.actual_rec_type
2132 ,p_c_period_start_date DATE
2133 ,p_c_period_end_date DATE)
2134 IS
2135 SELECT asg.assignment_id
2136 , asg.effective_start_date
2137 FROM per_all_assignments_f asg
2138 , per_assignment_status_types ast
2139 WHERE ( (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
2140 AND asg.job_id IN (SELECT jei.job_id
2141 FROM per_job_extra_info jei
2142 WHERE jei.jei_information1 = p_c_actual_rec.dim2_level_value_id)
2143 )
2144 OR
2145 (p_c_actual_rec.dim2_level_value_name = 'JOB'
2146 AND
2147 asg.job_id = to_number(p_c_actual_rec.dim2_level_value_id) )
2148 OR
2149 (p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
2150 )
2151 AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
2152 AND
2153 asg.location_id = to_number(p_c_actual_rec.dim3_level_value_id))
2154 OR
2155 p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY'
2156 )
2157 AND asg.business_group_id = p_c_business_group_id
2158 AND asg.organization_id = to_number(p_c_actual_rec.org_level_value_id)
2159 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2160 AND ast.per_system_status = 'ACTIVE_ASSIGN'
2161 AND asg.assignment_type = 'E' -- Bug 2357061
2162 AND p_c_period_end_date BETWEEN
2163 asg.effective_start_date AND
2164 asg.effective_end_date
2165 /* Commented out because of the method in HRMNPSUM used to calc TOTAL
2166 AND EXISTS
2167 (SELECT null
2168 FROM per_all_assignments_f asg2
2169 , per_assignment_status_types ast2
2170 WHERE p_c_period_start_date-1 BETWEEN
2171 asg2.effective_start_date AND
2172 asg2.effective_end_date
2173 AND asg2.assignment_id = asg.assignment_id
2174 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
2175 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
2176 AND asg2.organization_id = p_c_actual_rec.org_level_value_id
2177 )
2178 */
2179 ;
2180
2181 BEGIN
2182
2183 pl('*HRMVR actual','Start');
2184 --
2185 -- If a fast formula exists this will be used to calculate the
2186 -- manpower value
2187 --
2188 l_business_group_id := get_org_business_group_id(to_number(p_actual_rec.org_level_value_id));
2189 pl(' Business Group id',to_char(l_business_group_id));
2190 pl(' unit of measure ',p_bmtype);
2191 l_formula_id := get_formula_id(l_business_group_id
2192 ,p_bmtype);
2193 pl(' formula id ',to_char(l_formula_id));
2194 pl(' dim1 ',p_actual_rec.dim1_level_value_id);
2195 pl(' dim2 ',p_actual_rec.dim2_level_value_id);
2196 pl(' dim3 ',p_actual_rec.dim3_level_value_id);
2197 pl(' dim1 ',p_actual_rec.dim1_level_value_name);
2198 pl(' dim2 ',p_actual_rec.dim2_level_value_name);
2199 pl(' dim3 ',p_actual_rec.dim3_level_value_name);
2200
2201
2202 --Get start and end dates from the time dim
2203 get_dates_from_time_dim
2204 (p_actual_rec.time_level_value_id
2205 ,l_period_start_date
2206 ,l_period_end_date);
2207 --
2208 -- get all the eligible assignments
2209 --
2210 pl('loop through assignments');
2211
2212 FOR mv_rec IN mv_cur
2213 (l_business_group_id
2214 ,p_actual_rec
2215 ,l_period_start_date
2216 ,l_period_end_date)
2217 LOOP
2218 pl(' assignment id ',to_char(mv_rec.assignment_id));
2219
2220 l_budget_value := HrFastAnswers.GetBudgetValue
2221 (p_budget_metric_formula_id => l_formula_id
2222 ,p_budget_metric => p_bmtype
2223 ,p_assignment_id => mv_rec.assignment_id
2224 ,p_effective_date => mv_rec.effective_start_date
2225 ,p_session_date => sysdate);
2226
2227 pl(p_bmtype
2228 ,to_char(l_budget_value));
2229
2230 l_total_manpower := l_total_manpower + l_budget_value;
2231 END LOOP;
2232
2233 pl('Actual = ',to_char(l_total_manpower));
2234 pl('*HRMVR actual','End');
2235
2236 RETURN l_total_manpower;
2237
2238 END hrmvr_actual;
2239
2240 -------------------------------------------------------------------------
2241 --
2242 -- PROCESS MANPOWER VARIANCE TARGETS
2243 --
2244 -------------------------------------------------------------------------
2245
2246 PROCEDURE process_hrmvr
2247 (p_target_rec IN hri_target_rec_type)
2248 IS
2249
2250 l_actual NUMBER(22,2);
2251 l_budget_value NUMBER(22,2);
2252 l_budget_id per_budgets_v.budget_id%TYPE;
2253 l_business_group_id NUMBER;
2254
2255 l_actual_rec bis_actual_pub.actual_rec_type;
2256
2257 --
2258 -- get the budget value
2259 --
2260
2261 CURSOR bv_cur(p_budget_id IN NUMBER
2262 ,p_business_group_id IN NUMBER
2263 ,p_target_rec IN hri_target_rec_type) IS
2264 SELECT sum(bval.value) budget_value
2265 FROM per_budget_values bval
2266 , per_budget_elements be
2267 , per_budget_versions bver
2268 , per_time_periods tp
2269 , per_budgets_v bud
2270 where bud.unit = p_target_rec.unit_of_measure
2271 AND bud.business_group_id = l_business_group_id
2272 AND bud.budget_id = p_budget_id
2273 AND bud.budget_id = bver.budget_id
2274 AND trunc(sysdate) BETWEEN
2275 bver.date_FROM AND nvl( bver.date_to, SYSDATE+1 )
2276 AND be.budget_version_id = bver.budget_version_id
2277 AND be.budget_element_id = bval.budget_element_id
2278 AND tp.time_period_id = bval.time_period_id
2279 AND be.organization_id = p_target_rec.org_level_value_id
2280 AND ((p_target_rec.dim2_level_short_name = 'JOB CATEGORY'
2281 AND be.job_id IN (SELECT jei.job_id
2282 FROM per_job_extra_info jei
2283 WHERE jei.jei_information1 = p_target_rec.dim2_level_value_id))
2284 OR (p_target_rec.dim2_level_short_name = 'JOB'
2285 AND be.job_id = p_target_rec.dim2_level_value_id)
2286 OR (p_target_rec.dim2_level_short_name = 'TOTAL JOBS'))
2287 AND tp.start_date = p_target_rec.period_start_date
2288 AND tp.END_date = p_target_rec.period_END_date;
2289
2290 BEGIN
2291
2292 pl('*Processing manpower variance','Start');
2293 pl(' Dim3 (budget) ',p_target_rec.dim3_level_value_id);
2294
2295 -- Convert the date from target record format into an actual record format
2296 l_actual_rec := get_bis_actual_rec(p_target_rec);
2297
2298 --
2299 -- IF the target based on a HR budget (i.e. dimension 3 is populate)
2300 -- then get the target value from the budget
2301 -- otherwise use the value from the target itself.
2302 -- Note: If the target contains both the name
2303 -- of a budget and a target value, the target value is ignored.
2304 --
2305
2306 -- hrFastAnswers.LoadOrgHierarchy(p_target_rec.org_level_value_id,g_org_structure_version_id);
2307
2308 IF (p_target_rec.dim3_level_value_id = '-1'
2309 OR
2310 p_target_rec.dim3_level_value_id IS NULL )
2311 THEN
2312
2313 pl(' Budget not specified so using target values');
2314
2315 IF p_target_rec.measure_short_name = 'HRMVRFTE' THEN
2316 l_actual := hrmvr_actual(l_actual_rec,'FTE');
2317 ELSIF p_target_rec.measure_short_name = 'HRMVRHEAD' THEN
2318 l_actual := hrmvr_actual(l_actual_rec,'HEAD');
2319 END IF;
2320 pl(' actual ',to_char(l_actual));
2321 hrmvr_compare_values(p_target_rec
2322 ,l_actual);
2323 ELSE
2324 l_budget_id := to_NUMBER(substr(p_target_rec.dim3_level_value_id
2325 ,instr(p_target_rec.dim3_level_value_id,'+')+1));
2326 l_business_group_id := get_org_business_group_id(to_number(p_target_rec.org_level_value_id));
2327 pl(' Budget used so querying budget');
2328 OPEN bv_cur(l_budget_id
2329 ,l_business_group_id
2330 ,p_target_rec);
2331 FETCH bv_cur
2332 INTO l_actual;
2333
2334 IF bv_cur%NOTFOUND THEN
2335 CLOSE bv_cur;
2336 ELSE
2337 CLOSE bv_cur;
2338 pl(' budget target ',to_char(p_target_rec.target));
2339
2340 IF p_target_rec.measure_short_name = 'HRMVRFTE' THEN
2341 l_actual := hrmvr_actual(l_actual_rec,'FTE');
2342 ELSIF p_target_rec.measure_short_name = 'HRMVRHEAD' THEN
2346 hrmvr_compare_values(p_target_rec
2343 l_actual := hrmvr_actual(l_actual_rec,'HEAD');
2344 END IF;
2345
2347 ,l_actual);
2348 END IF;
2349 END IF;
2350 pl('*Processing manpower variance','End');
2351
2352 END;
2353
2354 -- **********************************************************************
2355 --
2356 -- RECRUITMENT SUCCESS
2357 --
2358 -- **********************************************************************
2359
2360
2361 -------------------------------------------------------------------------
2362 --
2363 -- SEND RECRUITMENT SUCCESS notification
2364 --
2365 -------------------------------------------------------------------------
2366 -- This procedure will build and send a recruitment success notification.
2367 -- It is called from the procedure hrrcs_process if the actual recruitment
2368 -- success rate is less than the targeted success rate.
2369 --
2370
2371 PROCEDURE send_notification_hrrcs
2372 (p_resp_no IN NUMBER
2373 ,p_target IN NUMBER
2374 ,p_actual IN NUMBER
2375 ,p_target_rec hri_target_rec_type)
2376 IS
2377
2378 l_subject fnd_new_messages.message_text%TYPE;
2379 l_message VARCHAR2(2000);
2380 l_freq_code VARCHAR2(4);
2381 l_dummy VARCHAR2(2000);
2382 l_param VARCHAR2(2000);
2383 l_error VARCHAR2(2000);
2384
2385 l_start_date DATE;
2386 l_report_name VARCHAR2(8) := 'HRCOMREC';
2387 l_return_status VARCHAR2(2000);
2388
2389 l_actual VARCHAR2(30);
2390 l_target VARCHAR2(30);
2391
2392 l_org_name per_organization_units.name%TYPE;
2393 l_org_type hr_lookups.meaning%TYPE;
2394 l_orgprc hr_lookups.meaning%TYPE;
2395 l_bmt_name hr_lookups.meaning%TYPE;
2396 l_job_name per_jobs.name%TYPE;
2397 l_job_cat hr_lookups.meaning%TYPE := '';
2398 l_loc_name hr_locations.location_code%TYPE;
2399
2400 l_period_name VARCHAR2(50);
2401
2402 -- Session params
2403 l_security_group_id per_business_groups.security_group_id%TYPE;
2404 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
2405 l_resp_id fnd_responsibility.responsibility_id%TYPE;
2406 l_resp_short_name bisfv_targets.notify_resp1_short_name%TYPE;
2407 l_resp_name bisfv_targets.notify_resp1_name%TYPE;
2408 l_resp_appl_id fnd_responsibility.application_id%TYPE;
2409
2410 BEGIN
2411
2412 pl('-------------------------------------');
2413 pl('*SEND recruitment success notification','Start');
2414
2415 -----------------------------------------------------------------------
2416 -- Build the notification subject line and message text
2417 -----------------------------------------------------------------------
2418 --
2419 -- Get the following information that will be shown on the notification
2420 -- and populate the relevant tokens in the message HR_BIS_PMF_SUBJECT
2421 --
2422 -- 1. Get Session parameters for the report
2423 -- 2. Get Parameter Names:
2424 -- Organzation name
2425 -- Location
2426 -- Job or Job Category
2427 -- Budget Measurement Type Name
2428 -- Period name
2429 -- 3. build notification text
2430 -- 4. get the report frequency, number of periods and start date
2431 -- 5. build the report parameter string
2432 -- 6. start the workflow process
2433 --
2434 -- ---------------------------------------------------------------------
2435 -- 1. Get Session parameters for the report
2436 -- ---------------------------------------------------------------------
2437 get_resp_details(p_resp_no
2438 ,p_target_rec
2439 ,l_resp_id
2440 ,l_resp_appl_id
2441 ,l_resp_short_name
2442 ,l_resp_name);
2443 l_business_group_id := get_org_business_group_id(p_target_rec.org_level_value_id);
2444 l_security_group_id := get_org_security_group_id(p_target_rec.org_level_value_id);
2445 -- ---------------------------------------------------------------------
2446 -- 1. Get the parameter names
2447 -- ---------------------------------------------------------------------
2448 --
2449 -- Organization
2450 --
2451 hr_reports.get_organization(p_target_rec.org_level_value_id,l_org_name,l_org_type);
2452 l_orgprc := translate_orgprc(g_orgprc_const);
2453 pl(' org name: ',l_org_name);
2454 --
2455 -- Location
2456 --
2457 l_loc_name := translate_location(p_target_rec.dim1_level_value_id);
2458 pl(' loc name : ',l_loc_name);
2459 --
2460 -- Job / Job Category
2461 --
2462 IF p_target_rec.dim2_level_short_name = 'JOB' THEN
2463 l_job_name := translate_job(p_target_rec.dim2_level_value_id);
2464 l_job_cat := translate_all;
2465 ELSIF p_target_rec.dim2_level_short_name = 'JOB CATEGORY' THEN
2466 l_job_name := translate_all;
2467 l_job_cat := translate_job_category(p_target_rec.dim2_level_value_id);
2468 ELSIF p_target_rec.dim2_level_short_name = 'TOTAL JOBS' THEN
2469 l_job_name := translate_all;
2470 l_job_cat := translate_all;
2471 END IF;
2472 pl(' job name: ',l_job_name);
2473 pl(' job category name: ',l_job_cat);
2474 --
2475 -- Budget Name
2476 --
2477 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
2481 --
2478 l_bmt_name := hr_reports.get_lookup_meaning('BUDGET_MEASUREMENT_TYPE'
2479 ,p_target_rec.unit_of_measure);
2480 pl(' bmt name: ',l_bmt_name);
2482 -- Period Name
2483 --
2484 l_period_name := to_char(p_target_rec.period_start_date,'DD-MON-YYYY')
2485 ||' - '||to_char(p_target_rec.period_END_date,'DD-MON-YYYY');
2486 pl(' period name: ',l_period_name);
2487 -- ---------------------------------------------------------------------
2488 -- 2. Build the notification text
2489 -- ---------------------------------------------------------------------
2490 --
2491 -- Build subject message text
2492 --
2493 fnd_message.set_name('HRI','HR_BIS_PMF_HRRCS_SUBJECT');
2494 l_subject := fnd_message.get;
2495 --
2496 -- Add Percent signs to actual values
2497 --
2498 l_actual := to_char(p_actual)||'%';
2499 l_target := to_char(p_target)||'%';
2500 --
2501 -- Build the body message text
2502 --
2503 fnd_message.set_name('HRI','HR_BIS_PMF_HRRCS_MSG');
2504 fnd_message.set_token('ORGANIZATION',l_org_name);
2505 fnd_message.set_token('LOCATION',l_loc_name);
2506 fnd_message.set_token('JOB',l_job_name);
2507 fnd_message.set_token('CATEGORY',l_job_cat);
2508 fnd_message.set_token('PERIOD',l_period_name);
2509 fnd_message.set_token('TARGET',l_target);
2510 fnd_message.set_token('ACTUAL',l_actual);
2511 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
2512 fnd_message.set_token('BUDGET',l_bmt_name);
2513 --fnd_message.set_token('ROLE',l_resp_name);
2514 l_message := fnd_message.get;
2515 pl(' Message:',l_message);
2516
2517 -- ---------------------------------------------------------------------
2518 -- 4. build the report parameter string
2519 -- ---------------------------------------------------------------------
2520 -- Build the url that will be used to run the report from the
2521 -- notification.
2522 l_report_name := 'HRCOMREC';
2523
2524 get_report_date_params(p_target_rec.time_level_short_name
2525 ,p_target_rec.period_start_date
2526 ,l_freq_code
2527 ,l_start_date);
2528 pl(' Frequency: ',l_freq_code);
2529 pl('Start date: ',to_char(l_start_date,'DD-MON-YYYY'));
2530
2531 -- Bug 2530846 replacing use of budget_measurement_type with Unit_of_measure
2532 l_param := 'org_id=' ||p_target_rec.org_level_value_id ||'*'||
2533 'orgprc=' ||g_orgprc_const ||'*'||
2534 'bus_id=' ||to_char(l_business_group_id) ||'*'||
2535 'bpl_id=' ||p_target_rec.plan_id ||'*'||
2536 'bgttyp=' ||p_target_rec.unit_of_measure ||'*'||
2537 'frqncy=' ||l_freq_code ||'*'||
2538 'startd=' ||to_char(l_start_date,'YYYY-MM-DD')||'*'||
2539 'end_dt=' ||to_char(p_target_rec.period_END_date,'YYYY-MM-DD')||'*';
2540
2541 IF p_target_rec.dim2_level_short_name = 'JOB' THEN
2542 l_param := l_param||
2543 'job_id=' ||p_target_rec.dim2_level_value_id ||'*'||
2544 'jobcat=__ALL__*';
2545 ELSIF p_target_rec.dim2_level_short_name = 'JOB CATEGORY' THEN
2546 l_param := l_param||
2547 'job_id=-1*'||
2548 'jobcat=' ||p_target_rec.dim2_level_value_id ||'*';
2549 ELSE
2550 l_param := l_param||
2551 'job_id=-1*'||
2552 'jobcat=__ALL__*';
2553 END IF;
2554 --
2555 -- Append the responsibility and security group ID to the URL. This is used by
2556 -- ICX to switch the responsibility when running the report.
2557 --
2558 l_param := l_param ||
2559 c_ampersand||'responsibility_application_id='
2560 ||to_char(l_resp_appl_id)||
2561 c_ampersand||'security_group_id='
2562 ||l_security_group_id;
2563
2564 pl('wf ',p_target_rec.workflow_item_type);
2565 pl('process ',p_target_rec.workflow_process_short_name);
2566 pl('resp ',l_resp_short_name);
2567 pl('resp id ',to_char(l_resp_id));
2568 pl('applic ',to_char(l_resp_appl_id));
2569 pl('secgrp ',l_security_group_id);
2570 pl('param ',substr(l_param,1,240));
2571 pl('report ',l_report_name);
2572 pl('message ',substr(l_message,1,240));
2573 pl('subject ',l_subject);
2574 -- ---------------------------------------------------------------------
2575 -- 5. start the workflow process
2576 -- ---------------------------------------------------------------------
2577 pl('start workflow process');
2578 bis_util.strt_wf_process
2579 (p_exception_message => l_message
2580 ,p_msg_subject => l_subject
2581 ,p_exception_date => sysdate
2582 ,p_item_type => p_target_rec.workflow_item_type
2583 ,p_wf_process => p_target_rec.workflow_process_short_name
2584 ,p_notify_resp_name => l_resp_short_name
2585 ,p_report_name1 => l_report_name
2586 ,p_report_param1 => l_param
2587 ,p_report_resp1_id => l_resp_id
2588 ,x_return_status => l_return_status);
2589
2590 pl(' Resp id : ',to_char(l_resp_id));
2591
2592 pl(' Workflow return status : ',l_return_status);
2593
2594 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2595 RAISE e_g_bis_fn_error;
2599
2596 END IF;
2597
2598 pl('*SEND Recruitment Success notification','End');
2600 END;
2601
2602 -------------------------------------------------------------------------
2603 --
2604 -- CALCULATE RECRUITMENT SUCCESS ACTUAL
2605 --
2606 -------------------------------------------------------------------------
2607 --
2608 -- This function will calculate and return the recruitment success rate. the
2609 -- recruitment success rate is the percentage of vacant places that were
2610 -- successfully filled.
2611 --
2612 FUNCTION hrrcs_actual
2613 (p_actual_rec IN bis_actual_pub.actual_rec_type
2614 ,p_bmtype IN VARCHAR2)
2615 RETURN NUMBER
2616 IS
2617
2618 l_success_rate NUMBER;
2619 l_total_vacancies NUMBER;
2620 l_total_assignments NUMBER;
2621
2622 l_period_start_date DATE;
2623 l_period_end_date DATE;
2624
2625 l_business_group_id NUMBER;
2626 l_budget_value NUMBER;
2627
2628 l_formula_id ff_formulas_f.formula_id%TYPE;
2629
2630 --
2631 -- This cursor gets each vacancy that closed in period defined by the
2632 -- pmf time dimension
2633 --
2634
2635 CURSOR vac_cur(p_c_actual_rec bis_actual_pub.actual_rec_type
2636 ,p_c_bmtype VARCHAR2
2637 ,p_c_period_start_date DATE
2638 ,p_c_period_end_date DATE)
2639 IS
2640 SELECT v.budget_measurement_value
2641 , v.vacancy_id
2642 FROM per_vacancies v
2643 WHERE ( (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
2644 AND v.job_id IN
2645 (SELECT jei.job_id
2646 FROM per_job_extra_info jei
2647 WHERE jei.jei_information1
2648 = p_c_actual_rec.dim2_level_value_id)
2649 )
2650 OR
2651 (p_c_actual_rec.dim2_level_value_name = 'JOB'
2652 AND v.job_id = to_number(p_c_actual_rec.dim2_level_value_id)
2653 )
2654 OR
2655 (p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
2656 )
2657 AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
2658 AND v.location_id = to_number(p_c_actual_rec.dim1_level_value_id)
2659 )
2660 OR
2661 (p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY')
2662 )
2663 AND v.budget_measurement_type = p_c_bmtype
2664 AND v.status = 'CLOSED' --Closed vacancy, bug 2449031
2665 AND v.date_to BETWEEN
2666 p_c_period_start_date AND p_c_period_end_date
2667 AND v.organization_id = p_actual_rec.org_level_value_id;
2668
2669 --
2670 -- This cursor gets the first instance of an employee assignment that
2671 -- was created from a given vacancy
2672 --
2673 CURSOR asg_cur (p_vacancy_id IN NUMBER) IS
2674 SELECT a.assignment_id
2675 , a.vacancy_id
2676 , a.effective_start_date
2677 FROM per_all_assignments_f a
2678 WHERE (a.assignment_id, a.effective_start_date) IN
2679 (SELECT b.assignment_id,min(b.effective_start_date)
2680 FROM per_all_assignments_f b
2681 WHERE b.assignment_type = 'E'
2682 AND b.vacancy_id = p_vacancy_id
2683 GROUP BY b.assignment_id);
2684
2685 BEGIN
2686
2687 pl('*HRRCS Actual','Start');
2688 --
2689 -- Get the fast formula ID of the FTE or HEAD. This is used to
2690 -- calculate the budget measurement value of an employees assignment
2691 --
2692 l_business_group_id :=
2693 get_org_business_group_id(to_number(p_actual_rec.org_level_value_id));
2694
2695 pl('Business Group id: ', l_business_group_id);
2696 l_formula_id := get_formula_id(l_business_group_id
2697 ,p_bmtype);
2698 pl('Formula id ',to_char(l_formula_id));
2699
2700 --Get start and end dates from the time dim
2701 get_dates_from_time_dim
2702 (p_actual_rec.time_level_value_id
2703 ,l_period_start_date
2704 ,l_period_end_date);
2705
2706 --
2707 -- Loop through each vacancy that closed in the period defined by the pmf
2708 -- time dimension and calculate the total number of vacancies.
2709 --
2710 l_total_vacancies := 0;
2711 l_total_assignments := 0;
2712
2713 FOR vac_rec IN vac_cur(p_actual_rec
2714 ,p_bmtype
2715 ,l_period_start_date
2716 ,l_period_end_date)
2717 LOOP
2718
2719 pl('Vacancy ',to_char(vac_rec.vacancy_id));
2720 pl('Openings ',to_char(vac_rec.budget_measurement_value));
2721
2722 l_total_vacancies := l_total_vacancies+vac_rec.budget_measurement_value;
2723 --
2724 -- get the budget value for each employee assignment created from
2725 -- this vacancy
2726 --
2727 FOR asg_rec IN asg_cur(vac_rec.vacancy_id) LOOP
2728 pl(' Asg ',to_char(asg_rec.assignment_id));
2729
2730 l_budget_value := hrFastAnswers.getBudgetValue
2731 (p_budget_metric_formula_id => l_formula_id
2732 ,p_budget_metric => p_bmtype
2733 ,p_assignment_id => asg_rec.assignment_id
2737 l_total_assignments := l_total_assignments + l_budget_value;
2734 ,p_effective_date => asg_rec.effective_start_date
2735 ,p_session_date => sysdate);
2736
2738 pl(p_bmtype,to_char(l_budget_value));
2739
2740 END LOOP;
2741 END LOOP;
2742 --
2743 -- Calculate the recruitment success rate
2744 --
2745 pl('Total vacancies: ',to_char(l_total_vacancies));
2746 pl('Total assignments: ',to_char(l_total_assignments));
2747
2748 IF l_total_vacancies = 0 THEN
2749 l_success_rate := null;
2750 ELSIF l_total_assignments = 0 THEN
2751 l_success_rate := 0;
2752 ELSE
2753 l_success_rate := round((l_total_assignments / l_total_vacancies ) * 100,1);
2754 END IF;
2755
2756 pl('+Success rate: ',to_char(l_success_rate));
2757 pl('*HRRCS Actual','End');
2758
2759 RETURN l_success_rate;
2760
2761 END hrrcs_actual;
2762
2763 -------------------------------------------------------------------------
2764 --
2765 -- PROCESS RECRUITMENT SUCCESS TARGETS
2766 --
2767 -------------------------------------------------------------------------
2768 --
2769 -- This procedure will calculate the actual recruitment success rates
2770 -- using the target dimensions and will compare it to the target value. If
2771 -- that actual value is below the target value then a notification is sent. The
2772 -- notification is sent to the notIFy responsibility specIFied IN the target.
2773 --
2774 --
2775 PROCEDURE process_hrrcs
2776 (p_target_rec IN hri_target_rec_type)
2777 IS
2778
2779 l_actual NUMBER;
2780 l_mod_target bisfv_targets.target%TYPE;
2781 l_actual_rec bis_actual_pub.actual_rec_type;
2782
2783 BEGIN
2784
2785 pl('*Process Recruitment Success Target','Start');
2786
2787 -- Convert the data from target record format into an actual record format
2788 l_actual_rec := get_bis_actual_rec(p_target_rec);
2789
2790 IF p_target_rec.measure_short_name = 'HRRCSFTE' THEN
2791 l_actual := hrrcs_actual(l_actual_rec,'FTE');
2792 ELSIF p_target_rec.measure_short_name = 'HRRCSHEAD' THEN
2793 l_actual := hrrcs_actual(l_actual_rec,'HEAD');
2794 END IF;
2795
2796 --
2797 -- Send a notification if the actual success rate is below the
2798 -- the target success rate.
2799 -- If a value of -1 has been returned
2800 -- then abort processing as an error has occurred.
2801 --
2802 IF l_actual = -1 THEN
2803 null;
2804 ELSE
2805 IF p_target_rec.notify_resp1_id IS NOT NULL THEN
2806 IF validate_resp_org_security
2807 (p_target_rec.notify_resp1_id
2808 ,p_target_rec.notify_resp1_appl_id
2809 ,to_number(p_target_rec.org_level_value_id)) THEN
2810 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range1_low);
2811 pl(' Processing responsibility 1: ',p_target_rec.notify_resp1_short_name);
2812 pl(' Modified target: ',l_mod_target);
2813 pl(' Actual: ',l_actual);
2814 IF l_actual < l_mod_target THEN
2815 send_notification_hrrcs(1
2816 ,l_mod_target
2817 ,l_actual
2818 ,p_target_rec);
2819 END IF; -- actual < target
2820 END IF; -- valid resp1_id security
2821 END IF; --notify_resp1_id not null
2822 --
2823 IF p_target_rec.notify_resp2_id IS NOT NULL THEN
2824 IF validate_resp_org_security
2825 (p_target_rec.notify_resp2_id
2826 ,p_target_rec.notify_resp2_appl_id
2827 ,to_number(p_target_rec.org_level_value_id)) THEN
2828 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range2_low);
2829 pl(' Processing responsibility 2: ',p_target_rec.notify_resp2_short_name);
2830 pl(' Modified target: ',l_mod_target);
2831 pl(' Actual: ',l_actual);
2832 IF l_actual < l_mod_target THEN
2833 send_notification_hrrcs(2
2834 ,l_mod_target
2835 ,l_actual
2836 ,p_target_rec);
2837 END IF; -- actual < target
2838 END IF; -- valid resp2_id security
2839 END IF; --notify_resp2_id not null
2840 --
2841 IF p_target_rec.notify_resp3_id IS NOT NULL THEN
2842 IF validate_resp_org_security
2843 (p_target_rec.notify_resp3_id
2844 ,p_target_rec.notify_resp3_appl_id
2845 ,to_number(p_target_rec.org_level_value_id)) THEN
2846 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range3_low);
2847 pl(' Processing responsibility 3: ',p_target_rec.notify_resp3_short_name);
2848 pl(' Modified target: ',l_mod_target);
2849 pl(' Actual: ',l_actual);
2850 IF l_actual < l_mod_target THEN
2851 send_notification_hrrcs(3
2852 ,l_mod_target
2853 ,l_actual
2854 ,p_target_rec);
2855 END IF; -- actual < target
2856 END IF; -- valid resp3_id security
2857 END IF; --notify_resp3_id not null
2858 END IF; -- actual = -1
2859
2860 pl('*Process Recruitment Success Target','End');
2861
2865 --
2862 END process_hrrcs;
2863
2864 -- **********************************************************************
2866 -- TRAINING SUCCESS
2867 --
2868 -- **********************************************************************
2869
2870 -------------------------------------------------------------------------
2871 --
2872 -- Procedure: send_notification_HRTRS
2873 --
2874 -------------------------------------------------------------------------
2875
2876
2877 PROCEDURE send_notification_hrtrs
2878 (p_resp_no IN NUMBER
2879 ,p_target IN NUMBER
2880 ,p_actual IN NUMBER
2881 ,p_target_rec IN hri_target_rec_type)
2882 IS
2883
2884 l_subject fnd_new_messages.message_text%TYPE;
2885 l_message VARCHAR2(2000);
2886 l_freq_code VARCHAR2(4);
2887 l_periods NUMBER;
2888 l_dummy VARCHAR2(2000);
2889 l_start_date date;
2890 l_return_status VARCHAR2(2000);
2891 l_param VARCHAR2(2000);
2892 l_error VARCHAR2(2000);
2893
2894 l_report_name VARCHAR2(8) := 'HRTRNSUC';
2895
2896 l_activity_version_name ota_activity_versions.version_name%TYPE; -- name of training course (activity version)
2897 l_actual VARCHAR2(80); -- Actual value (e.g. 25%)
2898 l_job_cat hr_lookups.meaning%TYPE; -- Job category
2899 l_job_name per_jobs.name%TYPE; -- Job name
2900 l_loc_name hr_locations.location_code%TYPE; -- Location name
2901 l_org_name per_organization_units.name%TYPE; -- Organization name
2902 l_org_type hr_lookups.meaning%TYPE; --
2903 l_orgprc hr_lookups.meaning%TYPE; -- include subordinate indicator (yes/no)
2904 l_target VARCHAR2(80); -- Target value (e.g. 75%)
2905 l_period_name VARCHAR2(50);
2906
2907 -- Session params
2908 l_security_group_id per_business_groups.security_group_id%TYPE;
2909 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
2910 l_resp_id fnd_responsibility.responsibility_id%TYPE;
2911 l_resp_short_name bisfv_targets.notify_resp1_short_name%TYPE;
2912 l_resp_name bisfv_targets.notify_resp1_name%TYPE;
2913 l_resp_appl_id fnd_responsibility.application_id%TYPE;
2914
2915 BEGIN
2916
2917 pl('----------------------------------');
2918 pl('*SEND Training Success notification','Start');
2919 pl('----------------------------------');
2920 -----------------------------------------------------------------------
2921 -- Build the notification subject line and message text
2922 -----------------------------------------------------------------------
2923 --
2924 -- Get the following information that will be shown on the notification
2925 -- and populate the relevant tokens in the message HR_BIS_PMF_SUBJECT
2926 --
2927 -- 1. Get Session parameters for the report
2928 -- 2. Get Parameter Names:
2929 -- Organzation
2930 -- Location
2931 -- Job or Job Category
2932 -- Training Activity
2933 -- Period
2934 -- 3. build notification text
2935 -- 4. get the report frequency, number of periods and start date
2936 -- 5. build the report parameter string
2937 -- 6. start the workflow process
2938 --
2939 -- ---------------------------------------------------------------------
2940 -- 1. Get Session parameters for the report
2941 -- ---------------------------------------------------------------------
2942 get_resp_details(p_resp_no
2943 ,p_target_rec
2944 ,l_resp_id
2945 ,l_resp_appl_id
2946 ,l_resp_short_name
2947 ,l_resp_name);
2948 l_business_group_id := get_org_business_group_id(p_target_rec.org_level_value_id);
2949 l_security_group_id := get_org_security_group_id(p_target_rec.org_level_value_id);
2950 -- ---------------------------------------------------------------------
2951 -- 2. Get the parameter names
2952 -- ---------------------------------------------------------------------
2953 --
2954 -- Organization
2955 --
2956 hr_reports.get_organization(p_target_rec.org_level_value_id,l_org_name,l_dummy);
2957 l_orgprc := translate_orgprc(g_orgprc_const);
2958 pl(' org name: ',l_org_name);
2959 --
2960 -- Location
2961 --
2962 l_loc_name := translate_location(p_target_rec.dim1_level_value_id);
2963 pl(' loc name: ',l_loc_name);
2964 --
2965 -- Job / Job Category
2966 --
2967 IF p_target_rec.dim2_level_short_name = 'JOB' THEN
2968 l_job_name := hr_reports.get_job(p_target_rec.dim2_level_value_id);
2969 l_job_cat := translate_all;
2970 ELSIF p_target_rec.dim2_level_short_name = 'JOB CATEGORY' THEN
2971 l_job_name := translate_all;
2972 l_job_cat := hr_reports.get_lookup_meaning('JOB_CATEGORIES',p_target_rec.dim2_level_value_id);
2973 ELSIF p_target_rec.dim2_level_short_name = 'TOTAL JOBS' THEN
2974 l_job_name := translate_all;
2975 l_job_cat := translate_all;
2976 END IF;
2977 pl(' job name: ',l_job_name);
2978 pl(' jobcat name: ',l_job_cat);
2979 --
2980 -- Training Activity Version
2981 --
2982 l_activity_version_name := get_activity_version_name(p_target_rec.dim3_level_value_id);
2986 --
2983 pl(' Train Act Ver name: ',l_activity_version_name);
2984 --
2985 -- Period Name
2987 l_period_name := to_char(p_target_rec.period_start_date,'DD-MON-YYYY')
2988 ||' - '||to_char(p_target_rec.period_END_date,'DD-MON-YYYY');
2989 pl(' Period Name:',l_period_name);
2990 --
2991 -- Include % on target and actual values
2992 --
2993 l_actual := to_char(p_actual)||'%';
2994 l_target := to_char(p_target)||'%';
2995 -- ---------------------------------------------------------------------
2996 -- 2. Build the notification text
2997 -- ---------------------------------------------------------------------
2998 --
2999 -- Build subject message text
3000 --
3001 fnd_message.set_name('HRI','HR_BIS_PMF_HRTRS_SUBJECT');
3002 l_subject := fnd_message.get;
3003 --
3004 -- Build the body message text
3005 --
3006 -- If the target has the activity version (i.e. the name of the training course) set
3007 -- then we need to use the notification that does not include the link to the
3008 -- training report this is because the training report does not have the activity
3009 -- version as a parameter
3010 --
3011 IF p_target_rec.dim3_level_short_name = 'TOTAL ACTIVITY VERSIONS' THEN
3012 fnd_message.set_name('HRI','HR_BIS_PMF_HRTRS_MSG');
3013 ELSE
3014 fnd_message.set_name('HRI','HR_BIS_PMF_HRTRS_MSG_NO_URL');
3015 END IF;
3016 fnd_message.set_token('ORGANIZATION',l_org_name);
3017 fnd_message.set_token('LOCATION',l_loc_name);
3018 fnd_message.set_token('JOB',l_job_name);
3019 fnd_message.set_token('CATEGORY',l_job_cat);
3020 fnd_message.set_token('ACTIVITY',l_activity_version_name);
3021 fnd_message.set_token('PERIOD',l_period_name);
3022 fnd_message.set_token('TARGET',l_target);
3023 fnd_message.set_token('ACTUAL',l_actual);
3024 --fnd_message.set_token('ROLE',l_resp_name);
3025 l_message := fnd_message.get;
3026 pl(' Message:', l_message);
3027
3028 -- ---------------------------------------------------------------------
3029 -- 4. build the report parameter string
3030 -- ---------------------------------------------------------------------
3031 -- Build the url that will be used to run the report from the
3032 -- notification.
3033 l_report_name := 'HRTRNSUC';
3034
3035 get_report_date_params(p_target_rec.time_level_short_name
3036 ,p_target_rec.period_start_date
3037 ,l_freq_code
3038 ,l_start_date);
3039
3040 pl(' Frequency: ',l_freq_code);
3041 pl(' Start date: ',to_char(l_start_date,'DD-MON-YYYY'));
3042
3043 l_param := 'org_id=' ||p_target_rec.org_level_value_id ||'*'||
3044 'orgprc=' ||g_orgprc_const ||'*'||
3045 'bpl_id=' ||p_target_rec.plan_id ||'*'||
3046 'loc_id=' ||p_target_rec.dim1_level_value_id ||'*'||
3047 'frqncy=' ||l_freq_code ||'*'||
3048 'startd=' ||to_char(l_start_date,'YYYY-MM-DD')||'*'||
3049 'end_dt=' ||to_char(p_target_rec.period_end_date,'YYYY-MM-DD')||'*';
3050
3051 IF p_target_rec.dim2_level_short_name = 'JOB' THEN
3052 l_param := l_param ||
3053 'job_id='||p_target_rec.dim2_level_value_id||'*'||
3054 'jobcat=__ALL__*';
3055 ELSIF p_target_rec.dim2_level_short_name = 'JOB CATEGORY' THEN
3056 l_param := l_param ||
3057 'jobcat='||p_target_rec.dim2_level_value_id||'*'||
3058 'job_id=-1*';
3059 ELSIF p_target_rec.dim2_level_short_name = 'TOTAL JOBS' THEN
3060 l_param := l_param ||
3061 'jobcat=__ALL__*'||
3062 'job_id=-1*';
3063 END IF;
3064
3065 l_param := l_param ||
3066 c_ampersand||'responsibility_application_id='
3067 ||to_char(l_resp_appl_id)||
3068 c_ampersand||'security_group_id='
3069 ||l_security_group_id;
3070
3071 pl('wf ',p_target_rec.workflow_item_type);
3072 pl('process ',p_target_rec.workflow_process_short_name);
3073 pl('resp ',l_resp_short_name);
3074 pl('resp id ',to_char(l_resp_id));
3075 pl('applic ',to_char(l_resp_appl_id));
3076 pl('secgrp ',l_security_group_id);
3077 pl('param ',substr(l_param,1,240));
3078 pl('report ',l_report_name);
3079 pl('message ',substr(l_message,1,240));
3080 pl('subject ',l_subject);
3081 -- ---------------------------------------------------------------------
3082 -- 5. start the workflow process
3083 -- ---------------------------------------------------------------------
3084 pl(' start workflow process');
3085 bis_util.strt_wf_process
3086 (p_exception_message => l_message
3087 ,p_msg_subject => l_subject
3088 ,p_exception_date => sysdate
3089 ,p_item_type => p_target_rec.workflow_item_type
3090 ,p_wf_process => p_target_rec.workflow_process_short_name
3091 ,p_notify_resp_name => l_resp_short_name
3092 ,p_report_name1 => l_report_name
3093 ,p_report_param1 => l_param
3094 ,p_report_resp1_id => l_resp_id
3095 ,x_return_status => l_return_status);
3096
3097 pl(' Resp id',to_char(l_resp_id));
3098
3099 pl(' Workflow return status: ',l_return_status);
3100
3101 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3102 RAISE e_g_bis_fn_error;
3103 END IF;
3104
3108
3105 pl('*SEND Training Success notification','End');
3106
3107 END;
3109 -------------------------------------------------------------------------
3110 --
3111 -- CALCULATE ACTUAL TRAINING SUCCESS
3112 --
3113 -------------------------------------------------------------------------
3114 --
3115 -- This function will return the actual training success rate based on
3116 -- the dimensions held in the p_target_rec record. This record is either
3117 -- populated from infrmation contained in the target or when posting actuals
3118 -- it is populated from the performance measure section of the configurable
3119 -- home page. The actual training success rate is expressed as a percentage of
3120 -- successful training hours against total training hours
3121 --
3122
3123
3124 FUNCTION hrtrs_actual
3125 (p_actual_rec IN bis_actual_pub.actual_rec_type)
3126 RETURN NUMBER
3127 IS
3128
3129 l_actual NUMBER;
3130 l_these_hours NUMBER;
3131 l_total_delivered NUMBER;
3132 l_total_success NUMBER;
3133
3134 l_period_start_date DATE;
3135 l_period_end_date DATE;
3136
3137 l_business_group_id NUMBER;
3138
3139 l_formula_id ff_formulas_f.formula_id%TYPE;
3140
3141 -- get the training duration for each attendance to a scheduled event
3142 -- that start date lies between the period start and end date
3143 --
3144 -- This Cursor has been changed due to bug 1718083 to be exactly the same
3145 -- as the Workforce Total report. JRHYDE
3146 --
3147 CURSOR train_dur_cur(p_c_actual_rec bis_actual_pub.actual_rec_type
3148 ,p_c_period_start_date DATE
3149 ,p_c_period_end_date DATE)
3150 IS
3151 SELECT asg.assignment_id
3152 , asg.organization_id
3153 , evt.duration
3154 , evt.duration_units
3155 , ver.version_name
3156 , evt.title
3157 , dbk.successful_attendance_flag
3158 FROM per_assignments_f asg
3159 , ota_booking_status_types bst
3160 , ota_activity_versions ver
3161 , ota_events evt
3162 , ota_delegate_bookings dbk
3163 WHERE dbk.delegate_assignment_id = asg.assignment_id
3164 AND evt.course_start_date BETWEEN
3165 asg.effective_start_date AND asg.effective_end_date
3166 AND dbk.booking_status_type_id = bst.booking_status_type_id
3167 AND bst.type = 'A' -- Attended
3168 AND dbk.event_id = evt.event_id
3169 AND asg.assignment_type = 'E'
3170 AND evt.event_type = 'SCHEDULED'
3171 AND evt.activity_version_id = ver.activity_version_id
3172 AND nvl(evt.event_status, 'X') <> 'C' /*Not Cancelled*/
3173 /* Time Dim */
3174 AND evt.course_end_date < trunc(sysdate)
3175 AND evt.course_end_date BETWEEN
3176 p_c_period_start_date AND
3177 p_c_period_end_date
3178 /* Org Dim*/
3179 AND asg.organization_id = p_c_actual_rec.org_level_value_id
3180 /* Dim 1 Location*/
3181 AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
3182 AND asg.location_id = p_c_actual_rec.dim1_level_value_id)
3183 OR (p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY')
3184 )
3185 /* Dim 2 Job or Job Category*/
3186 AND ( (p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
3187 OR (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
3188 AND asg.job_id IN (SELECT jei.job_id
3189 FROM per_job_extra_info jei
3190 WHERE jei.jei_information1 = p_c_actual_rec.dim2_level_value_id))
3191 OR (p_c_actual_rec.dim2_level_value_name = 'JOB'
3192 AND asg.job_id = p_c_actual_rec.dim2_level_value_id)
3193 )
3194 /* Dim 3 Activity Version */
3195 AND ( (p_c_actual_rec.dim3_level_value_name = 'ACTIVITY VERSION'
3196 AND evt.activity_version_id = p_c_actual_rec.dim3_level_value_id)
3197 OR (p_c_actual_rec.dim3_level_value_name = 'TOTAL ACTIVITY VERSIONS')
3198 );
3199
3200 BEGIN
3201
3202 pl('*HRTRS actual','Start');
3203
3204 -- Initialise the hours counters
3205 l_total_delivered := 0;
3206 l_total_success := 0;
3207
3208 l_business_group_id := get_org_business_group_id(to_number(p_actual_rec.org_level_value_id));
3209
3210 l_formula_id := get_formula_id(l_business_group_id
3211 ,'BIS_TRAINING_CONVERT_DURATION');
3212 pl(' Formula id: ',to_char(l_formula_id));
3213
3214 --Get start and end dates from the time dim
3215 get_dates_from_time_dim
3216 (p_actual_rec.time_level_value_id
3217 ,l_period_start_date
3218 ,l_period_end_date);
3219
3220
3221 --
3222 -- Loop through all training duration rows
3223 --
3224 FOR evt_dur_rec IN train_dur_cur(p_actual_rec
3225 ,l_period_start_date
3226 ,l_period_end_date)
3227 LOOP
3228 pl(' Asg id: ',to_char(evt_dur_rec.assignment_id));
3229 pl(' Duration: ', to_char(evt_dur_rec.duration));
3230 pl(' Duration Units: ', evt_dur_rec.duration_units);
3231 -- Run FF to convert duration to hours
3232 l_these_hours := HrFastAnswers.TrainingConvertDuration
3233 (l_formula_id
3234 ,evt_dur_rec.duration
3235 ,evt_dur_rec.duration_units
3236 ,'H'
3237 ,evt_dur_rec.version_name
3241 -- the total isn't corrupted
3238 ,evt_dur_rec.title
3239 ,sysdate);
3240 -- Ensure that if the fast formula returns null
3242 IF l_these_hours IS NOT NULL THEN
3243 l_total_delivered := l_total_delivered + l_these_hours;
3244 pl(' Successful Attendance: ', evt_dur_rec.successful_attendance_flag);
3245 IF evt_dur_rec.successful_attendance_flag = 'Y' THEN
3246 l_total_success := l_total_success + l_these_hours;
3247 END IF;
3248 END IF;
3249 END LOOP;
3250
3251 pl('Total delivered: ',to_char(l_total_delivered));
3252 pl('Total success: ',to_char(l_total_success));
3253 --
3254 -- Calculate the success rate and return value
3255 --
3256 IF l_total_delivered = 0 THEN
3257 l_actual := NULL;
3258 ELSIF l_total_success = 0 THEN
3259 l_actual := 0;
3260 ELSE
3261 l_actual := round(( l_total_success / l_total_delivered ) * 100,1);
3262 END IF;
3263
3264 pl('Actual : ',to_char(l_actual));
3265 pl('*HRTRS actual','End');
3266 RETURN l_actual;
3267
3268 END;
3269
3270 -------------------------------------------------------------------------
3271 --
3272 -- PROCESS TRAINING SUCCESS TARGETS
3273 --
3274 -------------------------------------------------------------------------
3275
3276 PROCEDURE process_hrtrs
3277 (p_target_rec IN hri_target_rec_type)
3278 IS
3279
3280 l_actual NUMBER;
3281 l_mod_target NUMBER;
3282 l_org_struct_version_id per_org_structure_versions.org_structure_version_id%TYPE;
3283
3284 l_actual_rec bis_actual_pub.actual_rec_type;
3285
3286 BEGIN
3287
3288 pl('*Processing Training Success Target','Start');
3289
3290 -- hrFastAnswers.LoadOrgHierarchy(p_target_rec.org_level_value_id,l_org_struct_version_id);
3291
3292 -- Convert the data from target record format into an actual record format
3293 l_actual_rec := get_bis_actual_rec(p_target_rec);
3294
3295 l_actual := hrtrs_actual(l_actual_rec);
3296
3297 --
3298 -- Send a notification if the actual success rate is below the
3299 -- the target success rate. If a value of -1 has been returned
3300 -- then abort processing as an error has occurred.
3301 --
3302
3303 IF l_actual = -1 THEN
3304 null;
3305 ELSE
3306 IF p_target_rec.notify_resp1_id IS NOT NULL THEN
3307 IF validate_resp_org_security
3308 (p_target_rec.notify_resp1_id
3309 ,p_target_rec.notify_resp1_appl_id
3310 ,to_number(p_target_rec.org_level_value_id)) THEN
3311 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range1_low);
3312 pl(' Processing responsibility 1: ',p_target_rec.notify_resp1_short_name);
3313 pl(' Modified target: ',l_mod_target);
3314 pl(' Actual: ',l_actual);
3315 IF l_actual < l_mod_target THEN
3316 send_notification_hrtrs(1
3317 ,l_mod_target
3318 ,l_actual
3319 ,p_target_rec);
3320 END IF; -- actual < target
3321 END IF; -- valid resp1_id security
3322 END IF; --notify_resp1_id not null
3323 --
3324 IF p_target_rec.notify_resp2_id IS NOT NULL THEN
3325 IF validate_resp_org_security
3326 (p_target_rec.notify_resp2_id
3327 ,p_target_rec.notify_resp2_appl_id
3328 ,to_number(p_target_rec.org_level_value_id)) THEN
3329 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range2_low);
3330 pl(' Processing responsibility 2: ',p_target_rec.notify_resp2_short_name);
3331 pl(' Modified target: ',l_mod_target);
3332 pl(' Actual: ',l_actual);
3333 IF l_actual < l_mod_target THEN
3334 send_notification_hrtrs(2
3335 ,l_mod_target
3336 ,l_actual
3337 ,p_target_rec);
3338 END IF; -- actual < target
3339 END IF; -- valid resp2_id security
3340 END IF; --notify_resp2_id not null
3341 --
3342 IF p_target_rec.notify_resp3_id IS NOT NULL THEN
3343 IF validate_resp_org_security
3344 (p_target_rec.notify_resp3_id
3345 ,p_target_rec.notify_resp3_appl_id
3346 ,to_number(p_target_rec.org_level_value_id)) THEN
3347 l_mod_target := p_target_rec.target - percent(p_target_rec.target,p_target_rec.range1_low);
3348 pl(' Processing responsibility 3: ',p_target_rec.notify_resp3_short_name);
3349 pl(' Modified target: ',l_mod_target);
3350 pl(' Actual: ',l_actual);
3351 IF l_actual < l_mod_target THEN
3352 send_notification_hrtrs(3
3353 ,l_mod_target
3354 ,l_actual
3355 ,p_target_rec);
3356 END IF; -- actual < target
3357 END IF; -- valid resp3_id security
3358 END IF; --notify_resp3_id not null
3359 END IF; -- actual = -1
3360
3361 pl('*Processing Training Success Target','End');
3362
3363 END;
3364 -------------------------------------------------------------------------
3365 -- *********************************************************************
3369 -- END OF PMF DEFINITIONS
3366 -------------------------------------------------------------------------
3367 -- *********************************************************************
3368 --
3370 --
3371 -- *********************************************************************
3372 -------------------------------------------------------------------------
3373 -- *********************************************************************
3374 -------------------------------------------------------------------------
3375
3376 -------------------------------------------------------------------------
3377 -- Procedure: Post_target_level_actuals
3378 --
3379 -- Parameters: Target_rec - Target Record containing all required
3380 -- information pertaining to the original target param
3381 --
3382 -- Description
3383 -- This procedure loops through all the user selections
3384 -- i.e. the required actuals, and calculates the
3385 -- corresponding actuals for each and posts to the actuals
3386 -- table (using post_actuals).
3387 --
3388 -------------------------------------------------------------------------
3389 PROCEDURE post_target_level_actuals
3390 (p_target_rec IN hri_target_rec_type)
3391 IS
3392
3393 --Table of records containing the users indicator selections
3394 -- i.e. what the users want to see on their home page
3395 l_user_selection_tbl bis_INDICATOR_REGION_PUB.indicator_Region_Tbl_Type;
3396
3397 --Needed to pass target_level_short_name into the bis record
3398 --bis_actual_pub.retrieve_user_selections
3399 l_target_level_rec bis_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
3400
3401 -- Needed to pass specification to post_actuals
3402 l_actual_rec bis_ACTUAL_PUB.Actual_Rec_Type;
3403
3404 l_return_status VARCHAR2(1);
3405 l_msg_count NUMBER;
3406 l_msg_data VARCHAR2(250);
3407 l_error_tbl bis_UTILITIES_PUB.Error_Tbl_Type;
3408 i NUMBER := 0;
3409 l_actual NUMBER(38,2);
3410
3411 l_security_group_id per_business_groups.security_group_id%TYPE;
3412
3413 BEGIN
3414 pl('--------------------');
3415 pl('*POST TARGET LEVEL ACTUALS','Start');
3416 --
3417 -- Get all the user selections (these are the performance measures that the user
3418 -- has setup on their home page). We only get the ones for the current target level
3419 -- this means that to dISplay actuals on the home page providing they have targets
3420 -- set up.
3421 --
3422 pl('Retreive User Selections');
3423 l_target_level_rec.target_Level_Short_Name := p_target_rec.target_level_short_name;
3424 pl(' target_level_short_name',p_target_rec.target_level_short_name);
3425
3426 bis_actual_pub.Retrieve_User_Selections
3427 ( p_api_version => 1.0
3428 ,p_Target_Level_Rec => l_Target_Level_Rec
3429 ,x_indicator_Region_Tbl => l_user_selection_Tbl
3430 ,x_return_status => l_return_status
3431 ,x_msg_count => l_msg_count
3432 ,x_msg_data => l_msg_data
3433 ,x_error_Tbl => l_error_tbl
3434 );
3435 pl('return status: ',l_return_status);
3436
3437 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3438 -- One reason for excpetion may be that 0 rows are returned
3439 -- hence the use of an exception to stop this bombing the
3440 -- whole process and not calcing the targets
3441 RAISE e_g_bis_fn_error;
3442 END IF;
3443
3444 -- Preparring the actual record
3445 -- Using p_target_rec VALUES for l_target_rec to stop any un necessary
3446 -- re querying of this target level data that is correct for the target.
3447 -- Don't use l_user_selection_tbl.dimx_Level_Value_Name to supply
3448 -- dimensionx_level_short_name because:
3449 -- The base table bis_user_ind_selections doesn't contain the short_names
3450 -- and bis_actual_pub.retrieve_user_selections which calls
3451 -- bis_indicator_region_pvt.rectrieve_user_ind_selections - doesn't
3452 -- actually query this from bis_target_levels and put it in the
3453 -- l_user_selection_tbl!! So have to query these ourselves.
3454 -- Using the p_target_rec becuase this data is target level info
3455 -- which is common to all user selections
3456 l_actual_rec.time_level_value_id := p_target_rec.time_level_value_id;
3457 l_actual_rec.org_level_value_name := p_target_rec.org_level_short_name;
3458 l_actual_rec.dim1_level_value_name := p_target_rec.dim1_level_short_name;
3459 l_actual_rec.dim2_level_value_name := p_target_rec.dim2_level_short_name;
3460 l_actual_rec.dim3_level_value_name := p_target_rec.dim3_level_short_name;
3461 l_actual_rec.dim4_level_value_name := p_target_rec.dim4_level_short_name;
3462 l_actual_rec.dim5_level_value_name := p_target_rec.dim5_level_short_name;
3463
3464 pl('Count of user selections ', to_char(l_user_selection_Tbl.COUNT));
3465
3466 -- Loop through all user selections (for this target level)
3467 -- calculating and publishing the actuals for each
3468 FOR i IN 1..l_user_selection_Tbl.count LOOP
3469
3470 l_actual_rec.target_level_id := l_user_selection_tbl(i).target_level_id;
3471 l_actual_rec.target_level_short_name := l_user_selection_tbl(i).target_level_short_name;
3472 l_actual_rec.Responsibility_id := l_user_selection_tbl(i).Responsibility_id;
3476 l_actual_rec.org_level_value_id := l_user_selection_tbl(i).org_level_value_id;
3473 l_actual_rec.Responsibility_short_name := l_user_selection_tbl(i).Responsibility_short_name;
3474 l_actual_rec.Responsibility_name := l_user_selection_tbl(i).Responsibility_name;
3475 l_actual_rec.target_level_id := l_user_selection_tbl(i).target_level_id;
3477 l_actual_rec.dim1_level_value_id := l_user_selection_tbl(i).dim1_level_value_id;
3478 l_actual_rec.dim2_level_value_id := l_user_selection_tbl(i).dim2_level_value_id;
3479 l_actual_rec.dim3_level_value_id := l_user_selection_tbl(i).dim3_level_value_id;
3480 l_actual_rec.dim4_level_value_id := l_user_selection_tbl(i).dim4_level_value_id;
3481 l_actual_rec.dim5_level_value_id := l_user_selection_tbl(i).dim5_level_value_id;
3482
3483 --l_actual_rec.Responsibility_ID := p_target_rec.notify_resp1_id;
3484 --l_actual_rec.Responsibility_Short_Name := p_target_rec.notify_resp1_short_name;
3485 --l_actual_rec.Responsibility_Name := p_target_rec.notify_resp1_name;
3486
3487 --debug_bis_actual_rec(l_actual_rec);
3488
3489 -- Transfer control to the appropriate module which will calculate
3490 -- and post the actual values to the users home page.
3491 --
3492 -- Note: The function retrieve_user_ind_selections does not RETURN
3493 -- the responsibility AND we are therefore unable to verIFy that
3494 -- the user is authorized to access information on the given
3495 -- organization. Core bis have been requested to provide the
3496 -- responsibility.
3497 --
3498
3499 pl('Target Measure Short Name =',p_target_rec.measure_short_name);
3500
3501 IF p_target_rec.measure_short_name = 'HRMSPFTE' THEN
3502 l_actual_rec.actual := hrmsp_actual(l_actual_rec,'FTE');
3503 post_actual(l_actual_rec);
3504 ELSIF p_target_rec.measure_short_name = 'HRMSPHEAD' THEN
3505 l_actual_rec.actual := hrmsp_actual(l_actual_rec,'HEAD');
3506 post_actual(l_actual_rec);
3507 ELSIF p_target_rec.measure_short_name = 'HRMVRFTE' THEN
3508 l_actual_rec.actual := hrmvr_actual(l_actual_rec,'FTE');
3509 post_actual(l_actual_rec);
3510 ELSIF p_target_rec.measure_short_name = 'HRMVRHEAD' THEN
3511 l_actual_rec.actual := hrmvr_actual(l_actual_rec,'HEAD');
3512 post_actual(l_actual_rec);
3513 ELSIF p_target_rec.measure_short_name = 'HRRCSFTE' THEN
3514 l_actual_rec.actual := hrrcs_actual(l_actual_rec,'FTE');
3515 post_actual(l_actual_rec);
3516 ELSIF p_target_rec.measure_short_name = 'HRRCSHEAD' THEN
3517 l_actual_rec.actual := hrrcs_actual(l_actual_rec,'HEAD');
3518 post_actual(l_actual_rec);
3519 ELSIF p_target_rec.measure_short_name = 'HRTRS' THEN
3520 l_actual_rec.actual := hrtrs_actual(l_actual_rec);
3521 post_actual(l_actual_rec);
3522 END IF;
3523
3524 END LOOP;
3525
3526 pl('*POST TARGET LEVEL ACTUALS','End');
3527 pl('--------------------');
3528 EXCEPTION
3529 -- Need to continue if this exceptions so the process can continue
3530 -- with processing the targets
3531 WHEN OTHERS THEN
3532 NULL;
3533
3534 END post_target_level_actuals;
3535
3536 --***********************************************************************
3537 --
3538 -- PUBLIC FUNCTIONS AND PROCEDURE
3539 --
3540 --***********************************************************************
3541
3542 ------------------------------------------------------------------------------------------------
3543 --
3544 -- Procedure: Calc_and_post_target_actuals
3545 --
3546 -- Parameters: Target id
3547 --
3548 -- Description: This procedure is design as a method of debugging the actual posting methods.
3549 -- Calculates the actuals for the target specified without first calculating the
3550 -- targets.
3551 --
3552 ------------------------------------------------------------------------------------------------
3553 PROCEDURE calc_and_post_target_actuals
3554 (p_target_id IN NUMBER
3555 ,p_date IN DATE DEFAULT SYSDATE)
3556 IS
3557
3558 l_target_rec hri_target_rec_type;
3559 l_session_rec hri_session_rec_type;
3560
3561 BEGIN
3562 pl('*Calc and Post Target Actuals', 'Start');
3563 pl('Target id',to_char(p_target_id));
3564 l_session_rec := get_session;
3565 debug_session(l_session_rec);
3566
3567 l_target_rec := get_target_rec(p_target_id);
3568 post_target_level_actuals(l_target_rec);
3569
3570 END calc_and_post_target_actuals;
3571 ------------------------------------------------------------------------------------------------
3572 --
3573 -- Procedure: PROCESS_PMF_ALERT
3574 --
3575 -- Parameters: Target id
3576 --
3577 -- Description
3578 -- This procedure is the common entry point for all HR target values. It IS
3579 -- called by the HR alerts for each target value selected for processing (refer
3580 -- to the HR alerts for more details of the selection procedure). The procedure
3581 -- will retreive details of the target AND pass control to the relevant routine.
3582 --
3583 -- The performance measures currently supported are:
3584 --
3585 -- MSP = Manpower separation
3586 -- MVR = Manpower variance
3587 -- TRS = Training success
3588 -- RCS = Recruitment success
3589 --
3590 --
3591 -- Note
3592 -- To verify that the actual has been posted check in:
3593 -- BIS.BIS_ACTUAL_VALUES
3594 -- To verify that the notification has been posted check in:
3595 -- APPLSYS.WF
3596 ------------------------------------------------------------------------------------------------
3597
3598 PROCEDURE process_target
3599 (p_target_id IN NUMBER)
3600 IS
3601
3602 l_subject VARCHAR2(80);
3603 l_message VARCHAR2(2000);
3604 l_date date;
3605 l_param VARCHAR2(2000);
3606
3607 l_target_rec hri_target_rec_type;
3608 l_session_rec hri_session_rec_type;
3609
3610 e_fatal_error exception;
3611 e_pm_not_found exception;
3612 e_pm_not_known exception;
3613 e_tl_not_found exception;
3614 e_tv_not_found exception;
3615 e_pd_not_found exception;
3616
3617 BEGIN
3618
3619 pl('*PROCESS TARGET ','Start');
3620 pl(' TARGET = ',to_char(p_target_id));
3621
3622 --get the session, record to package globals and output details to debug
3623 g_session_rec := get_session;
3624 debug_session(g_session_rec);
3625
3626 ----------------------------------------------------------------------------------
3627 -- Get the target's details
3628 ----------------------------------------------------------------------------------
3629 l_target_rec := Get_Target_Rec(p_target_id);
3630
3631 debug_hri_target_rec(l_target_rec);
3632
3633 ---------------------------------------------------------------------------------
3634 -- Rerieve user selections and calculate the respective actuals
3635 -- This is prior to the target calculations as a precution
3636 -- because in the target calculations the apps globals may be changed
3637 -- causing issues with inserting actuals see bug 1413300
3638 ----------------------------------------------------------------------------------
3639 post_target_level_actuals(l_target_rec);
3640
3641 -- Check if any of the responsibilities exist
3642 -- Otherwise skip the target processing and exit
3643 IF (l_target_rec.notify_resp1_id IS NOT NULL
3644 OR
3645 l_target_rec.notify_resp2_id IS NOT NULL
3646 OR
3647 l_target_rec.notify_resp3_id IS NOT NULL
3648 ) THEN
3649
3650 pl('Checking responsibilities of targets');
3651 ----------------------------------------------------------------------------------
3652 -- Transfer control to the correct module
3653 ----------------------------------------------------------------------------------
3654 -- PROCESS_HRMSP Manpower separation
3655 -- PROCESS_HRMVR Manpower variance
3656 -- PROCESS_HRRCS Recruitment success
3657 -- PROCESS_HRTRS Training success
3658 IF l_target_rec.measure_short_name IN ('HRMSPFTE','HRMSPHEAD') THEN
3659 process_hrmsp(l_target_rec);
3660 ELSIF l_target_rec.measure_short_name IN ('HRMVRFTE','HRMVRHEAD') THEN
3661 process_hrmvr(l_target_rec);
3662 ELSIF l_target_rec.measure_short_name IN ('HRRCSFTE','HRRCSHEAD') THEN
3663 process_hrrcs(l_target_rec);
3664 ELSIF l_target_rec.measure_short_name = 'HRTRS' THEN
3665 process_hrtrs(l_target_rec);
3666 END IF;
3667 END IF; -- A target resp exists
3668
3669 pl('*PROCESS TARGET ','END');
3670 /*
3671 EXCEPTION
3672 WHEN OTHERS THEN
3673 g_error_msg := substr(sqlerrm,1,2000);
3674 RAISE;
3675 */
3676 END process_target;
3677
3678 END;
[Home] [Help]