[Home] [Help]
PACKAGE BODY: APPS.PER_FASTFORMULA_EVENTS_UTILITY
Source
1 Package Body per_fastformula_events_utility as
2 /* $Header: perffevt.pkb 120.1 2005/06/06 04:36:38 ssmukher noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |------------------------< per_fastformula_event >-------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 function per_fastformula_event(p_event_type in varchar2,
9 p_formula_type in varchar2,
10 p_business_group_id in number,
11 p_person_id in number,
12 p_start_date in date,
13 p_end_date in date)
14 return number is
15 --
16 l_inputs ff_exec.inputs_t;
17 l_outputs ff_exec.outputs_t;
18 l_number number := 0;
19 l_formula_id number := 0;
20 l_formula_type_id number := 0;
21 l_start_date date := null;
22 --
23 -- cursors used for promotion event
24 --
25 cursor csr_get_emp_asgs is
26 select paaf.effective_start_date,
27 paaf.assignment_id
28 from per_all_assignments_f paaf,
29 per_assignment_status_types past
30 where paaf.person_id = p_person_id
31 and paaf.assignment_type = 'E'
32 -- bug 2975652 only want to count active assignments
33 and paaf.assignment_status_type_id = past.assignment_status_type_id
34 and past.per_system_status = 'ACTIVE_ASSIGN'
35 and paaf.effective_start_date
36 between p_start_date
37 and p_end_date
38 order by paaf.effective_start_date;
39 --
40 l_csr_get_emp_asgs_rec csr_get_emp_asgs%rowtype;
41 l_counter number := 0;
42 --
43 begin
44 --
45 hr_utility.set_location('In',10);
46 --
47 /* seeded formula promotion template is commented out and always returns 0
48 return 0 here - no need to call formula */
49 if p_event_type = 'PROMOTION_TEMPLATE'
50 then
51 l_counter := 0;
52 else
53 -- select formula id for event type (fast formula name)
54 -- check formula exists at the start date entered.
55 --
56 l_formula_id := get_formula_id (p_event_type,
57 p_formula_type,
58 p_business_group_id,
59 p_start_date);
60 if l_formula_id = 0 -- if there is no fast formula : bug 3540677
61 then
62 return 0;
63 else
64 --
65 ff_exec.init_formula
66 (p_formula_id => l_formula_id,
67 p_effective_date => p_start_date,
68 p_inputs => l_inputs,
69 p_outputs => l_outputs);
70 --
71 if p_event_type = 'PROMOTION'
72 then
73 l_counter := 0;
74 --
75 open csr_get_emp_asgs;
76 --
77 loop
78 --
79 hr_utility.set_location('In Loop',10);
80 --
81 fetch csr_get_emp_asgs into l_csr_get_emp_asgs_rec;
82 hr_utility.set_location('fetching into l_csr_get_emp_asgs_rec',15);
83 if csr_get_emp_asgs%rowcount < 1
84 then
85 l_counter := 0;
86 fnd_message.set_name('PER','PER_289770_PERSON_NOT_FOUND');
87 fnd_message.set_token
88 ('PROC','per_fastformula_events_utility.per_fastformula_event');
89 hr_utility.set_location
90 ('person not found or no updates to assignment record between '||
91 'dates ',18);
92 -- fnd_message.raise_error;
93 end if;
94 exit when csr_get_emp_asgs%notfound;
95 --
96 l_number := 0;
97 /*
98 dt_fndate.change_ses_date
99 (p_ses_date => l_csr_get_emp_asgs_rec.effective_start_date
100 ,p_commit => l_number);
101 hr_utility.set_location('l_number '||TO_CHAR(l_number),20);
102 */
103 hr_utility.set_location('ses date '
104 ||l_csr_get_emp_asgs_rec.effective_start_date,22);
105 --
106 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1)
107 loop
108 if l_inputs(l_count).name = 'ASSIGNMENT_ID'
109 then
110 --
111 l_inputs(l_count).value :=
112 l_csr_get_emp_asgs_rec.assignment_id;
113 hr_utility.set_location('asg_id '||
114 l_csr_get_emp_asgs_rec.assignment_id,25);
115 --
116 elsif l_inputs(l_count).name = 'DATE_EARNED'
117 then
118 l_inputs(l_count).value :=
119 to_char(trunc(l_csr_get_emp_asgs_rec.effective_start_date),
120 'YYYY/MM/DD');
121 hr_utility.set_location('date_earned '
122 ||l_csr_get_emp_asgs_rec.effective_start_date,30);
123 end if;
124 --
125 end loop;
126 --
127 ff_exec.run_formula(p_inputs => l_inputs,
128 p_outputs => l_outputs);
129 --
130 if l_outputs(1).value <> 0
131 then
132 l_counter := l_counter + l_outputs(1).value;
133 hr_utility.set_location('promotions returned: '||l_counter,35);
134 end if;
135 hr_utility.set_location('Out Loop',40);
136 end loop;
137 --
138 close csr_get_emp_asgs;
139 --
140 end if;
141 end if; -- if formula_id = 0 ie if there is no fast formula : bug 3540677
142 end if; -- if promotion template
143 --
144 return l_counter;
145 --
146 end per_fastformula_event;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |------------------------< per_fastformula_event >-------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- overloaded module to run for one date
153 --
154 function per_fastformula_event(p_event_type in varchar2,
155 p_formula_type in varchar2,
156 p_business_group_id in number,
157 p_person_id in number,
158 p_effective_date in date)
159 return number is
160 --
161 l_inputs ff_exec.inputs_t;
162 l_outputs ff_exec.outputs_t;
163 l_number number := 0;
164 l_formula_id number := 0;
165 --
166 -- cursors used for promotion event
167 --
168 cursor csr_get_emp_asgs_snapshot is
169 select effective_start_date,
170 assignment_id
171 from per_all_assignments_f
172 where person_id = p_person_id
173 and assignment_type = 'E'
174 and p_effective_date
175 between effective_start_date
176 and effective_end_date
177 order by effective_start_date;
178 --
179 l_csr_get_emp_asgs_snapshot csr_get_emp_asgs_snapshot%rowtype;
180 l_counter number := 0;
181 --
182 begin
183 --
184 hr_utility.set_location('In',10);
185 if p_event_type = 'PROMOTION_TEMPLATE'
186 then
187 l_counter := 0;
188 else
189 --
190 -- select formula id for event type (fast formula name)
191 -- check formula exists at the start date entered.
192 --
193 l_formula_id := get_formula_id (p_event_type,
194 p_formula_type,
195 p_business_group_id,
196 p_effective_date);
197 if l_formula_id = 0 -- if there is no fast formula : bug 3540677
198 then
199 return 0;
200 else
201 --
202 ff_exec.init_formula
203 (p_formula_id => l_formula_id,
204 p_effective_date => p_effective_date,
205 p_inputs => l_inputs,
206 p_outputs => l_outputs);
207 --
208 if p_event_type = 'PROMOTION'
209 then
210 --
211 open csr_get_emp_asgs_snapshot;
212 --
213 loop
214 --
215 hr_utility.set_location('In Loop',10);
216 --
217 fetch csr_get_emp_asgs_snapshot into
218 l_csr_get_emp_asgs_snapshot;
219 hr_utility.set_location('fetching into l_csr_get_emp_asgs_rec',15);
220 if csr_get_emp_asgs_snapshot%rowcount < 1
221 then
222 l_counter := 0;
223 fnd_message.set_name('PER','PER_289770_PERSON_NOT_FOUND');
224 fnd_message.set_token
225 ('PROC','per_fastformula_events_utility.per_fastformula_event');
226 -- fnd_message.raise_error;
227 end if;
228 exit when csr_get_emp_asgs_snapshot%notfound;
229 --
230 l_number := 0;
231 /*
232 dt_fndate.change_ses_date
233 (p_ses_date =>
234 l_csr_get_emp_asgs_snapshot.effective_start_date
235 ,p_commit => l_number);
236 hr_utility.set_location('l_number '||TO_CHAR(l_number),20);
237 */
238 hr_utility.set_location('ses date '
239 ||l_csr_get_emp_asgs_snapshot.effective_start_date,20);
240 --
241 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1)
242 loop
243 if l_inputs(l_count).name = 'ASSIGNMENT_ID'
244 then
245 --
246 l_inputs(l_count).value :=
247 l_csr_get_emp_asgs_snapshot.assignment_id;
248 hr_utility.set_location('asg_id '||
249 l_csr_get_emp_asgs_snapshot.assignment_id,25);
250 --
251 elsif l_inputs(l_count).name = 'DATE_EARNED'
252 then
253 l_inputs(l_count).value :=
254 to_char(trunc
255 (l_csr_get_emp_asgs_snapshot.effective_start_date),
256 'YYYY/MM/DD');
257 hr_utility.set_location('date_earned '
258 ||l_csr_get_emp_asgs_snapshot.effective_start_date,30);
259 end if;
260 --
261 end loop;
262 --
263 ff_exec.run_formula(p_inputs => l_inputs,
264 p_outputs => l_outputs);
265 --
266 if l_outputs(1).value <> 0
267 then
268 l_counter := l_counter + l_outputs(1).value;
269 hr_utility.set_location('promotions returned: '||l_counter,35);
270 end if;
271 hr_utility.set_location('Out Loop',40);
272 end loop;
273 --
274 close csr_get_emp_asgs_snapshot;
275 --
276 end if;
277 end if; -- if no fast formula ie if formula_id = 0 - bug 3540677
278 end if; -- if event type = promotion_template
279 --
280 return l_counter;
281 --
282 end per_fastformula_event;
283 --
284
285
286 -- ----------------------------------------------------------------------------
287 -- | --------------- Added by ssmukher for Employment Equity report ---------|
288 -----------------------< Overloaded function for fetching the dates -----------
289 -- |------------------------< per_fastformula_event >-------------------------|
290 -- ----------------------------------------------------------------------------
291 --
292 function per_fastformula_event(p_event_type in varchar2,
293 p_formula_type in varchar2,
294 p_business_group_id in number,
295 p_person_id in number,
296 p_start_date in date,
297 p_end_date in date,
298 p_date_tab out nocopy date_tab) -- Added by ssmukher
299 return number is
300 --
301 l_inputs ff_exec.inputs_t;
302 l_outputs ff_exec.outputs_t;
303 l_formula_id number ;
304 l_formula_type_id number;
305 l_start_date date ;
306 l_cnt number; /* Added by ssmukher for employment equity report */
307 --
308 -- cursors used for promotion event
309 --
310 cursor csr_get_emp_asgs is
311 select paaf.effective_start_date,
312 paaf.assignment_id
313 from per_all_assignments_f paaf,
314 per_assignment_status_types past
315 where paaf.person_id = p_person_id
316 and paaf.assignment_type = 'E'
317 -- bug 2975652 only want to count active assignments
318 and paaf.assignment_status_type_id = past.assignment_status_type_id
319 and past.per_system_status = 'ACTIVE_ASSIGN'
320 and paaf.effective_start_date
321 between p_start_date
322 and p_end_date
323 order by paaf.effective_start_date;
324 --
325 l_csr_get_emp_asgs_rec csr_get_emp_asgs%rowtype;
326 l_counter number ;
327 --
328 begin
329 l_formula_id := 0;
330 l_start_date := null;
331 l_formula_type_id := 0;
332 l_counter := 0;
333 --
334 --hr_utility.trace_on(null,'EQUPIPE');
335 hr_utility.set_location('In',10);
336 /* Deleting the PL/SQL table contents Added by ssmukher */
337 p_date_tab.delete;
338 --
339 /* seeded formula promotion template is commented out and always returns 0
340 return 0 here - no need to call formula */
341 if p_event_type = 'PROMOTION_TEMPLATE'
342 then
343 l_counter := 0;
344 else
345 -- select formula id for event type (fast formula name)
346 -- check formula exists at the start date entered.
347 --
348 l_formula_id := get_formula_id (p_event_type,
349 p_formula_type,
350 p_business_group_id,
351 p_start_date);
355 else
352 if l_formula_id = 0 -- if there is no fast formula : bug 3540677
353 then
354 return 0;
356 --
357 ff_exec.init_formula
358 (p_formula_id => l_formula_id,
359 p_effective_date => p_start_date,
360 p_inputs => l_inputs,
361 p_outputs => l_outputs);
362 --
363 if p_event_type = 'PROMOTION'
364 then
365 l_counter := 0;
366 /* Added by ssmukher for Employment Equity report */
367 l_cnt := 0;
368 --
369 open csr_get_emp_asgs;
370 --
371 loop
372 --
373 hr_utility.set_location('In Loop',10);
374 --
375 fetch csr_get_emp_asgs into l_csr_get_emp_asgs_rec;
376 hr_utility.set_location('fetching into l_csr_get_emp_asgs_rec',15);
377 if csr_get_emp_asgs%rowcount < 1
378 then
379 l_counter := 0;
380 fnd_message.set_name('PER','PER_289770_PERSON_NOT_FOUND');
381 fnd_message.set_token
382 ('PROC','per_fastformula_events_utility.per_fastformula_event');
383 hr_utility.set_location
384 ('person not found or no updates to assignment record between '||
385 'dates ',18);
386 -- fnd_message.raise_error;
387 end if;
388 exit when csr_get_emp_asgs%notfound;
389 --
390 hr_utility.set_location('ses date '
391 ||l_csr_get_emp_asgs_rec.effective_start_date,22);
392 --
393 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1)
394 loop
395 if l_inputs(l_count).name = 'ASSIGNMENT_ID'
396 then
397 --
398 l_inputs(l_count).value :=
399 l_csr_get_emp_asgs_rec.assignment_id;
400 hr_utility.set_location('asg_id '||
401 l_csr_get_emp_asgs_rec.assignment_id,25);
402 --
403 elsif l_inputs(l_count).name = 'DATE_EARNED'
404 then
408 hr_utility.set_location('date_earned '
405 l_inputs(l_count).value :=
406 to_char(trunc(l_csr_get_emp_asgs_rec.effective_start_date),
407 'YYYY/MM/DD');
409 ||l_csr_get_emp_asgs_rec.effective_start_date,30);
410 end if;
411 --
412 end loop;
413 --
414 ff_exec.run_formula(p_inputs => l_inputs,
415 p_outputs => l_outputs);
416 --
417 if l_outputs(1).value <> 0
418 then
419 l_counter := l_counter + l_outputs(1).value;
420 hr_utility.set_location('promotions returned: '||l_counter,35);
421 /* Added by ssmukher for Employment Equity report */
422 l_cnt := l_cnt + 1;
423 p_date_tab(l_cnt) := l_csr_get_emp_asgs_rec.effective_start_date;
424 end if;
425 hr_utility.set_location('Out Loop',40);
426 end loop;
427 --
428 close csr_get_emp_asgs;
429 --
430 end if;
431 end if; -- if formula_id = 0 ie if there is no fast formula : bug 3540677
432 end if; -- if promotion template
433 --
434 return l_counter;
435 --
436 end per_fastformula_event;
437 -- ----------------------------------------------------------------------------
438 -- |----------------------------< get_formula_id >----------------------------|
439 -- ----------------------------------------------------------------------------
440 --
441 function get_formula_id (p_event_type in varchar2,
442 p_formula_type in varchar2,
443 p_business_group_id in number,
444 p_effective_date in date)
445 return number
446 is
447 l_formula_id number := 0;
448 --
449 cursor csr_get_bg_formula_id is
450 select fff.formula_id
451 from ff_formulas_f fff
452 ,ff_formula_types fft
453 where fff.formula_name = p_event_type
454 and fff.formula_type_id = fft.formula_type_id
455 and fft.formula_type_name = p_formula_type
456 and fff.business_group_id = p_business_group_id
457 and p_effective_date
458 between fff.effective_start_date
459 and fff.effective_end_date;
460 --
461 cursor csr_get_global_formula_id is
462 select fff.formula_id
463 from ff_formulas_f fff
464 ,ff_formula_types fft
465 where fff.formula_name = 'PROMOTION_TEMPLATE'
466 and fff.formula_type_id = fft.formula_type_id
467 and fft.formula_type_name = p_formula_type
468 and fff.business_group_id is null
469 and p_effective_date
470 between fff.effective_start_date
471 and fff.effective_end_date;
472 --
473 l_csr_formula_id number := 0;
474 --
475 begin
476 --
477 open csr_get_bg_formula_id;
478 loop
479 fetch csr_get_bg_formula_id into
480 l_csr_formula_id;
481 exit when csr_get_bg_formula_id%found;
482 if csr_get_bg_formula_id%notfound
483 then
484 fnd_message.set_name('PER','PER_289772_FORMULA_NOT_FOUND');
485 fnd_message.set_token
486 --
487 ('PROC','per_fastformula_events_utility.get_formula_id');
488 hr_utility.set_location('no formula for this business group '
489 ||p_business_group_id,44);
490 --
491 l_csr_formula_id := 0; -- bug 3540677
492 return 0;
493 --
494 -- fnd_message.raise_error; Bug 3540677 do not raise error if no
495 -- fast formula
496 --
497 -- below commented out as currently PROMOTION_TEMPLATE
498 -- is commented out, cannot be altered and always returns 0
499 -- therefore below never needs to be processed
500 -- however it will be needed if promotion_template is ever
501 -- delivered uncommented
502 /* open csr_get_global_formula_id;
503 loop
504 fetch csr_get_global_formula_id into
505 l_csr_formula_id;
506 if csr_get_global_formula_id%rowcount < 1
507 then
508 return 0;
509 hr_utility.set_location
510 ('you might be stuck here if promo_template has no text '
511 ||l_formula_id,44);
512 fnd_message.set_name('PER','PER_289772_FORMULA_NOT_FOUND');
513 fnd_message.set_token
514 ('PROC','per_fastformula_events_utility.get_formula_id');
515 fnd_message.raise_error;
516 hr_utility.set_location('l_formula_id '||l_formula_id,45);
517 hr_utility.set_location('p_business_group_id '
518 ||p_business_group_id,46);
519 end if;
520 exit when csr_get_global_formula_id%notfound;
521 end loop;
522 close csr_get_global_formula_id; */
523 end if;
524 end loop;
525 close csr_get_bg_formula_id;
526 l_formula_id := l_csr_formula_id;
527 --
528 return l_formula_id;
529 end get_formula_id;
530 --
531 end per_fastformula_events_utility;