[Home] [Help]
PACKAGE BODY: APPS.PER_FR_D2_PKG
Source
1 PACKAGE BODY PER_FR_D2_PKG AS
2 /* $Header: pefrd2rp.pkb 120.1 2005/12/20 13:34:05 aparkes noship $ */
3
4 cursor csr_get_extra_units(p_effective_date date) is
5 select
6 max(fnd_number.canonical_to_number(decode(
7 R.row_low_range_or_name,'BASE_UNIT',CINST.value))) base_unit
8 ,max(fnd_number.canonical_to_number(decode(
9 R.row_low_range_or_name,'X_COT_A',CINST.value))) x_cot_a
10 ,max(fnd_number.canonical_to_number(decode(
11 R.row_low_range_or_name,'X_COT_B',CINST.value))) x_cot_b
12 ,max(fnd_number.canonical_to_number(decode(
13 R.row_low_range_or_name,'X_COT_C',CINST.value))) x_cot_c
14 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
15 'X_COT_YOUNG_AGE',CINST.value))) x_cot_young_age
16 ,max(fnd_number.canonical_to_number(decode(
17 R.row_low_range_or_name,'X_COT_OLD_AGE',CINST.value))) x_cot_old_age
18 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
19 'X_COT_AGE_UNITS',CINST.value))) x_cot_age_units
20 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
21 'X_COT_TRAINING_HOURS',CINST.value))) x_cot_training_hours
22 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
23 'X_COT_TRAINING_UNITS',CINST.value))) x_cot_training_units
24 ,max(fnd_number.canonical_to_number(decode(
25 R.row_low_range_or_name,'X_COT_AP',CINST.value))) x_cot_ap
26 ,max(fnd_number.canonical_to_number(decode(
27 R.row_low_range_or_name,'X_COT_IMPRO',CINST.value))) x_cot_impro
28 ,max(fnd_number.canonical_to_number(decode(
29 R.row_low_range_or_name,'X_COT_CAT',CINST.value))) x_cot_cat
30 ,max(fnd_number.canonical_to_number(decode(
31 R.row_low_range_or_name,'X_COT_CDTD',CINST.value))) x_cot_cdtd
32 ,max(fnd_number.canonical_to_number(decode(
33 R.row_low_range_or_name,'X_COT_CFP',CINST.value))) x_cot_cfp
34 ,max(fnd_number.canonical_to_number(decode(
35 R.row_low_range_or_name,'X_IPP_LOW_RATE',CINST.value))) x_ipp_low_rate
36 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
37 'X_IPP_MEDIUM_RATE',CINST.value))) x_ipp_medium_rate
38 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
39 'X_IPP_HIGH_RATE',CINST.value))) x_ipp_high_rate
40 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
41 'X_IPP_LOW_UNITS',CINST.value))) x_ipp_low_units
42 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
43 'X_IPP_MEDIUM_UNITS',CINST.value))) x_ipp_medium_units
44 ,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
45 'X_IPP_HIGH_UNITS',CINST.value))) x_ipp_high_units
46 ,max(fnd_number.canonical_to_number(decode(
47 R.row_low_range_or_name,'X_HIRE_UNITS',CINST.value))) x_hire_units
48 from pay_user_tables TAB
49 , pay_user_rows_f R
50 , pay_user_columns C
51 , pay_user_column_instances_f CINST
52 where TAB.user_table_name = 'FR_D2_RATES'
53 and TAB.legislation_code = 'FR'
54 and TAB.business_group_id is null
55 and C.user_table_id = TAB.user_table_id
56 and C.legislation_code = 'FR'
57 and C.business_group_id is null
58 and C.user_column_name = 'VALUE'
59 and CINST.user_column_id = C.user_column_id
60 and R.user_table_id = TAB.user_table_id
61 and p_effective_date between R.effective_start_date
62 and R.effective_end_date
63 and R.business_group_id is null
64 and R.legislation_code = 'FR'
65 and CINST.user_row_id = R.user_row_id
66 and p_effective_date between CINST.effective_start_date
67 and CINST.effective_end_date
68 and CINST.business_group_id is null
69 and CINST.legislation_code = 'FR';
70
71 type t_extra_units is record (
72 effective_date date,
73 rec csr_get_extra_units%ROWTYPE);
74 g_extra_units t_extra_units;
75
76 function set_headcounts (p_establishment_id in number,
77 p_1jan in date,
78 p_31dec in date,
79 p_headcount_obligation out nocopy number,
80 p_headcount_particular out nocopy number,
81 p_basis_obligation out nocopy number,
82 p_obligation out nocopy number,
83 p_breakdown_particular out nocopy varchar2,
84 p_count_disabled out nocopy varchar2,
85 p_disabled_where_clause out nocopy varchar2)
86 return integer
87 IS
88 pcs_count table_of_number;
89 -- #4068197 new pl/sql table to accomodate pcs codes
90 pcs_codes table_of_varchar;
91 -- #4068197
92 l_proc varchar2(50);
93 l_return integer;
94 l_employee_count number;
95 l_estab_hours number;
96 l_business_group_id number;
97 l_continue_flag integer;
98 l_formula_id number;
99 l_formula_start_date date;
100 previous_block block_record;
101 first_block boolean;
102 block_fired integer;
103 pending_block integer;
104 l_list_disabled varchar2(32000);
105 percent_disabled_obligation number;
106 l_blocks table_of_block;
107 i binary_integer;
108 begin
109 --Initialising Local Variables
110 l_proc :='set_headcounts';
111 hr_utility.set_location('entering '||l_proc,0);
112 --
113 -- initialize OUT parameters
114 l_return := 0;
115 p_headcount_obligation := 0;
116 p_headcount_particular := 0;
117 p_basis_obligation := 0;
118 p_obligation := 0;
119 p_breakdown_particular := '';
120 p_count_disabled := '';
121 p_disabled_where_clause := '';
122 --
123 l_estab_hours := get_estab_hours (p_establishment_id);
124 --
125 select business_group_id
126 into l_business_group_id
127 from hr_all_organization_units
128 where organization_id = p_establishment_id;
129 --
130 percent_disabled_obligation :=
131 fnd_number.canonical_to_number(hruserdt.get_table_value
132 (l_business_group_id,
133 'FR_D2_RATES',
134 'VALUE',
135 'PERCENT_OBLIGATION'
136 ,p_1jan));
137 --
138 get_formula_ref (p_31dec, l_business_group_id, l_formula_id, l_formula_start_date);
139 --
140 hr_utility.set_location(l_proc,5);
141 --
142 -- work out list of disabled employees (regardless of headcounts)
143 --
144 l_list_disabled := list_disabled (p_establishment_id, p_1jan, p_31dec);
145 --
146 -- loop on all employees to work out individual headcounts
147 --
148 if csr_get_emp_year%isopen then
149 close csr_get_emp_year;
150 end if;
151 --
152 for rec_emp_year in csr_get_emp_year (p_establishment_id, p_1jan, p_31dec) loop
153 --
154 l_employee_count := 0;
155 first_block := true;
156 --
157 -- first populate the PL/SQL table with all the blocks for this employee
158 --
159 populate_blocks_table (p_establishment_id, p_1jan, p_31dec, rec_emp_year.person_id, l_blocks);
160 --
161 -- now loop on all blocks for this employee
162 --
163 i := l_blocks.first;
164 block_fired := 0;
165 --
166 while i is not null loop
167 --
168 pending_block := i;
169 --
170 if first_block then
171 previous_block := l_blocks(i);
172 l_continue_flag := 1;
173 first_block := false;
174 else
175 if relevant_change(previous_block,l_blocks(i)) then
176 --
177 -- fire formula for the big block
178 --
179 l_continue_flag := contract_prorated (previous_block,
180 l_business_group_id,
181 l_estab_hours,
182 p_31dec,
183 l_employee_count,
184 l_formula_id,
185 l_formula_start_date);
186 --
187 previous_block := l_blocks(i);
188 block_fired := i-1;
189 --
190 else -- enlarge current block
191 previous_block.block_start_date := least(previous_block.block_start_date,
192 l_blocks(i).block_start_date);
193 previous_block.block_end_date := greatest(previous_block.block_end_date,
194 l_blocks(i).block_end_date);
195 end if;
196 end if;
197 --
198 exit when l_continue_flag = 0;
199 --
200 i := l_blocks.next(i);
201 end loop;
202 --
203 if (pending_block<>block_fired) then
204 l_continue_flag := contract_prorated (previous_block,
205 l_business_group_id,
206 l_estab_hours,
207 p_31dec,
208 l_employee_count,
209 l_formula_id,
210 l_formula_start_date);
211 end if;
212 --
213 hr_utility.set_location('headcount for '||to_char(rec_emp_year.person_id)||' is '||to_char(l_employee_count),50);
214 --
215 if l_employee_count > 0 then -- update headcounts
216 --
217 p_headcount_obligation := p_headcount_obligation + l_employee_count;
218 --
219 update_particular (p_establishment_id,
220 rec_emp_year.person_id,
221 p_1jan,
222 p_31dec,
223 l_business_group_id,
224 l_employee_count,
225 p_headcount_particular,
226 pcs_count,
227 pcs_codes);
228 --
229 update_count_disabled (rec_emp_year.person_id, l_list_disabled,
230 l_employee_count, p_count_disabled);
231 --
232 else -- remove employee from list of disabled (when relevant)
233 trunc_list_disabled (rec_emp_year.person_id,l_list_disabled);
234 end if;
235 --
236 end loop;
237 --
238 if length(l_list_disabled) > 0 then
239 p_disabled_where_clause :=
240 'and per.person_id in (' || l_list_disabled || ') ';
241 -- bug 4219037 b; non-changable parts of this lexical parameter
242 -- moved into Q_DISABLED_EMP itself.
243 else
244 p_disabled_where_clause := 'and 0=1 ';
245 end if;
246 --
247 p_headcount_obligation := floor(p_headcount_obligation);
248 p_headcount_particular := floor(p_headcount_particular);
249 p_basis_obligation := p_headcount_obligation - p_headcount_particular;
250 p_obligation := floor(percent_disabled_obligation * p_basis_obligation /100);
251 -- #4068197
252 p_breakdown_particular := string_of_particular(pcs_count, pcs_codes);
253 -- #4068197
254 --
255 hr_utility.set_location('leaving '||l_proc,80);
256 return l_return;
257 --
258 exception
259 when others then
260 hr_utility.set_location('SetHeaERR:'||substr(sqlerrm,1,80),90);
261 return 1;
262 end set_headcounts;
263 --
264 --
265 function contract_prorated (p_block in block_record,
266 p_business_group_id in number,
267 p_estab_hours in number,
268 p_31dec in date,
269 p_tmp_total in out nocopy number,
270 p_formula_id in number,
271 p_formula_start_date in date) return integer
272 is
273 l_proc varchar2(50);
274 l_flag integer:=1;
275 l_debug_text varchar2(50);
276 l_emp_cat varchar2(30);
277 l_daily_hours varchar2(30);
278 l_weekly_hours varchar2(30);
279 l_monthly_hours varchar2(30);
280 l_inputs ff_exec.inputs_t;
281 l_outputs ff_exec.outputs_t;
282 begin
283 -- Initialising Local Variables
284 l_proc :='contract_prorated';
285 --
286 if include_this_person_type (p_block.person_type_usages,
287 p_business_group_id,
288 p_block.block_start_date)
289 then
290 if p_block.asg_employment_category is null then
291 l_emp_cat := 'U';
292 else
293 begin -- first get employment category for this assignment
294 --Bug #4183533
295 if p_block.asg_type = 'C' then
296 l_emp_cat := hruserdt.get_table_value (p_business_group_id,
297 'CWK_ASG_CATEGORY', 'FR_D2_CATEGORY',
298 p_block.asg_employment_category, p_block.block_start_date);
299 else
300 l_emp_cat := hruserdt.get_table_value (p_business_group_id,
301 'EMP_CAT', 'FR_D2_CATEGORY',
302 p_block.asg_employment_category, p_block.block_start_date);
303 end if;
304 -- Bug #4183533
305 exception
306 when others then
307 l_emp_cat := 'U';
308 end;
309 end if;
310 --
311 begin -- now get legal values
312 l_daily_hours := hruserdt.get_table_value(p_business_group_id,
313 'FR_LEGISLATIVE_RATES','VALUE','DAILY_HOURS',
314 p_block.block_start_date);
315 l_weekly_hours := hruserdt.get_table_value(p_business_group_id,
316 'FR_LEGISLATIVE_RATES','VALUE','WEEKLY_HOURS',
317 p_block.block_start_date);
318 l_monthly_hours := hruserdt.get_table_value(p_business_group_id,
319 'FR_LEGISLATIVE_RATES','VALUE','MONTHLY_HOURS',
320 p_block.block_start_date);
321 exception
322 when others then
323 l_daily_hours := '1';
324 l_weekly_hours := '1';
325 l_monthly_hours := '1';
326 end;
327 --
328 -- Initialize formula
329 --
330 ff_exec.init_formula (p_formula_id,
331 p_formula_start_date,
332 l_inputs,
333 l_outputs
334 );
335 --
336 if (l_inputs.first is not null) and (l_inputs.last is not null)
337 then
338 -- Set up context values for the formula
339 for l_in_cnt in
340 l_inputs.first..l_inputs.last
341 loop
342 if l_inputs(l_in_cnt).name='ASSIGNMENT_ID' then
343 l_inputs(l_in_cnt).value := p_block.asg_id;
344 end if;
345 if l_inputs(l_in_cnt).name='DATE_EARNED' then
346 l_inputs(l_in_cnt).value :=
347 fnd_date.date_to_canonical(p_block.block_start_date);
348 end if;
349 if l_inputs(l_in_cnt).name='BLOCK_START_DATE' then
350 l_inputs(l_in_cnt).value :=
351 fnd_date.date_to_canonical(p_block.block_start_date);
352 end if;
353 if l_inputs(l_in_cnt).name='BLOCK_END_DATE' then
354 l_inputs(l_in_cnt).value :=
355 fnd_date.date_to_canonical(p_block.block_end_date);
356 end if;
357 if l_inputs(l_in_cnt).name='ESTABLISHMENT_MONTHLY_HOURS' then
358 l_inputs(l_in_cnt).value :=
359 fnd_number.number_to_canonical(p_estab_hours);
360 end if;
361 if l_inputs(l_in_cnt).name='RUNNING_TOTAL' then
362 l_inputs(l_in_cnt).value :=
363 fnd_number.number_to_canonical(p_tmp_total);
364 end if;
365 if l_inputs(l_in_cnt).name='END_OF_YEAR' then
366 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_31dec);
367 end if;
368 if l_inputs(l_in_cnt).name='EMPLOYMENT_CATEGORY' then
369 l_inputs(l_in_cnt).value := l_emp_cat;
370 end if;
371 if l_inputs(l_in_cnt).name='LEGAL_DAILY_HOURS' then
372 l_inputs(l_in_cnt).value := l_daily_hours;
373 end if;
374 if l_inputs(l_in_cnt).name='LEGAL_MONTHLY_HOURS' then
375 l_inputs(l_in_cnt).value := l_monthly_hours;
376 end if;
377 if l_inputs(l_in_cnt).name='LEGAL_WEEKLY_HOURS' then
378 l_inputs(l_in_cnt).value := l_weekly_hours;
379 end if;
380 end loop;
381 end if;
382 --
383 -- Run the formula
384 --
385 ff_exec.run_formula (l_inputs ,
386 l_outputs
387 );
388 --
389 for l_out_cnt in
390 l_outputs.first..l_outputs.last
391 loop
392 if l_outputs(l_out_cnt).name = 'NEW_TOTAL' then
393 p_tmp_total :=
394 fnd_number.canonical_to_number(l_outputs(l_out_cnt).value);
395 end if;
396 if l_outputs(l_out_cnt).name = 'CONTINUE_FLAG' then
397 l_flag := l_outputs(l_out_cnt).value;
398 end if;
399 if l_outputs(l_out_cnt).name = 'DEBUG_TEXT' then
400 l_debug_text := l_outputs(l_out_cnt).value;
401 end if;
402 end loop;
403 --
404 hr_utility.set_location('leaving ff with debug_text='||l_debug_text,70);
405 --
406 end if;
407 --
408 return l_flag;
409 --
410 exception
411 when others then
412 hr_utility.set_location('ConProERR:'||substr(sqlerrm,1,80),90);
413 return 0;
414 end contract_prorated;
415 --
416 --
417 function get_estab_hours (p_establishment_id in number)
418 return number
419 is
420 l_hours_text hr_organization_information.org_information4%type;
421 l_hours number;
422 begin
423 --#3464382 Changed the query to fetch the data from the table and not from the view
424 select org_information4
425 into l_hours_text
426 from hr_organization_information
427 where organization_id = p_establishment_id
428 and org_information_context = 'FR_ESTAB_INFO';
429 --
430 l_hours := fnd_number.canonical_to_number(l_hours_text);
431 --
432 return l_hours;
433 exception
434 when others then
435 return 0;
436 end get_estab_hours;
437 --
438 --
439 procedure get_pcs_code (p_report_qualifier in varchar2
440 ,p_job_id in per_jobs.job_id%type default null
441 ,p_job_name in per_jobs.name%type default null
442 ,p_pcs_code in out nocopy varchar2
443 ,p_effective_date in date) is
444 l_unused_char varchar2(240);
445 l_unused_date date;
446 l_job_name per_jobs.name%type;
447 l_unused_number number;
448
449 --To get the message
450 l_value varchar2(240);
451 l_proc varchar2(200);
452 begin
453 --Initialising Local Variables
454 l_proc := 'Update_pcs_code';
455
456 hr_utility.set_location('enter '||l_proc,5);
457 --
458 --Get the Effective Date
459 IF p_report_qualifier = 'DADS' THEN
460 l_unused_date := to_date('31-12-2002', 'DD-MM-YYYY');
461 ELSE
462 l_unused_date := to_date('31-12-2003', 'DD-MM-YYYY');
463 END IF;
464 hr_utility.set_location('The last date of the old period date is '||l_unused_date,10);
465 --
466 -- Check for the date
467 IF p_effective_date <= l_unused_date THEN
468 hr_utility.set_location('In old period '||l_proc,20);
469 --The Code should be an old code
470 --Check whether the obtained code is new code or not
471 IF ascii(substr(p_pcs_code, -1 )) < ascii(0) OR ascii(substr(p_pcs_code, -1)) > ascii(9) THEN
472 l_unused_number := 0;
473 --Then check whether there is mapping or not
474 select count(lookup_code)
475 into l_unused_number
476 from fnd_common_lookups
477 where lookup_type = 'FR_PCS_CODE'
478 and description = p_pcs_code;
479 IF l_unused_number = 1 THEN
480 -- If there is more than one or zero old pcs code for the given new code, then the new code is printed
481 -- No error message is given for this
482 select lookup_code
483 into p_pcs_code
484 from fnd_common_lookups
485 where lookup_type = 'FR_PCS_CODE'
486 and description = p_pcs_code;
487 END IF;
488 END IF; --Ignore when it is a old code
489 ELSE
490 hr_utility.set_location('In new period '||l_proc,30);
491 --The code should be a new code
492 --Check whether the obtained code is old code or not
493 IF ascii(substr(p_pcs_code, -1 )) >= ascii(0) AND ascii(substr(p_pcs_code, -1)) <= ascii(9) THEN
494 --Then check whether there is mapping or not
495 select description
496 into l_unused_char
497 from fnd_common_lookups
498 where lookup_type = 'FR_PCS_CODE'
499 and lookup_code = p_pcs_code;
500 IF l_unused_char IS NULL THEN
501 --Get the job name
502 IF p_job_name is null THEN
503 select name
504 into l_job_name
505 from per_jobs
506 where job_id = p_job_id;
507 ELSE
508 l_job_name := p_job_name;
509 END IF;
510 --More than one mew code exists for the given new code
511 l_value := pay_fr_general.get_payroll_message('PAY_75193_OLD_CODE', null, null, null);
512 fnd_file.put_line(fnd_file.log, l_job_name||l_value);
513 p_pcs_code := NULL;
514 ELSE
515 p_pcs_code := l_unused_char;
516 END IF;
517 END IF; --Ignore when it is a new code
518 END IF;
519 hr_utility.set_location('leaving '||l_proc,50);
520 --
521 Exception
522 when others then
523 hr_utility.set_location('Error has been created in the package'||l_proc, 60);
524 hr_utility.set_location('Error is '||sqlerrm, 70);
525 end get_pcs_code;
526 --
527 --
528 -- overloaded procedure get_job_info 115.15
529 procedure get_job_info (p_establishment_id in number,
530 p_person_id in number,
531 p_1jan in date,
532 p_31dec in date,
533 p_pcs_code out nocopy varchar2,
534 p_job_title out nocopy varchar2)
535 is
536 cursor csr_last_job
537 is
538 -- select last job in the year for an employee
539 select job_id
540 from per_all_assignments_f
541 where person_id = p_person_id
542 and nvl(establishment_id,-1) = p_establishment_id
543 and effective_start_date <= p_31dec
544 and effective_end_date >= p_1jan
545 order by primary_flag desc, effective_start_date desc;
546 --
547 lid per_jobs.job_id%type;
548 begin
549 --
550 for rec_job in csr_last_job loop
551 lid := rec_job.job_id;
552 exit;
553 end loop;
554 --
555 select job_information1, name
556 into p_pcs_code, p_job_title
557 from per_jobs_v
558 where job_id = lid
559 and nvl(job_information_category,' ') = 'FR';
560 --
561 begin -- get pcs-code
562 --
563 -- Bug No: 3311942
564 --get the valid pcs code
565 per_fr_d2_pkg. get_pcs_code (p_report_qualifier => 'D2'
566 ,p_job_id => lid
567 ,p_pcs_code => p_pcs_code
568 ,p_effective_date => p_31dec);
569 --
570 exception
571 when others then
572 p_pcs_code := '0';
573 end;
574 --
575 exception
576 when others then
577 p_pcs_code := '0';
578 p_job_title := '?';
579 end get_job_info;
580 --
581 procedure get_job_info (p_establishment_id in number,
582 p_person_id in number,
583 p_1jan in date,
584 p_31dec in date,
585 p_year in number,
586 p_pcs_code out nocopy varchar2,
587 p_job_title out nocopy varchar2,
588 p_hours_training out nocopy number,
589 p_hire_year out nocopy number,
590 p_year_became_permanent out nocopy number)
591 is
592 l_date_start date;
593 begin
594 -- call overloaded private proc
595 get_job_info (p_establishment_id,
596 p_person_id,
597 p_1jan,
598 p_31dec,
599 p_pcs_code,
600 p_job_title);
601 -- get hours training
602 --
603 select nvl(max(fnd_number.canonical_to_number(pei_information2)),0)
604 into p_hours_training
605 from per_people_extra_info
606 where person_id = p_person_id
607 and nvl(pei_information_category,' ') = 'FR_PROF_TRAIN'
608 and nvl(pei_information1,' ') = to_char(p_year);
609 --
610 -- get year of hire / placement (bug 4219037 d)
611 --
612 select max(date_start)
613 into l_date_start
614 from (select date_start
615 from per_periods_of_service
616 where person_id = p_person_id
617 and date_start <= p_31dec
618 union all
619 select date_start
620 from per_periods_of_placement
621 where person_id = p_person_id
622 and date_start <= p_31dec);
623 p_hire_year := to_number(to_char(l_date_start,'YYYY'));
624 --
625 -- get p_year_became_permanent for bug 4237723
626 -- Will be null where there is no contract e.g. Contingent workers
627 --
628 select to_number(to_char(min(effective_start_date),'YYYY'))
629 into p_year_became_permanent
630 from per_contracts_f pcf
631 where pcf.effective_start_date >= l_date_start
632 and pcf.person_id = p_person_id
633 and pcf.CTR_INFORMATION_CATEGORY = 'FR'
634 and pcf.CTR_INFORMATION2 = 'PERMANENT'
635 and pcf.STATUS like 'A-%';
636 --
637 end get_job_info;
638 --
639 --
640 function list_disabled (p_establishment_id in number,
641 p_1jan in date,
642 p_31dec in date)
643 return varchar2
644 is
645 l_list varchar2(32000);
646 first boolean:=true;
647 begin
648 hr_utility.set_location('entering list_disabled',5);
649 --
650 for rec_disabled in csr_get_disabled (p_establishment_id,p_1jan,p_31dec) loop
651 --
652 if not first then
653 l_list := l_list || ',';
654 end if;
655 l_list := l_list || to_char(rec_disabled.id);
656 first := false;
657 end loop;
658 --
659 hr_utility.set_location('list of disabled ='||l_list,15);
660 --
661 return l_list;
662 --
663 exception
664 when others then
665 hr_utility.set_location('LisDisERR:'||substr(sqlerrm,1,80),90);
666 return '-1';
667 end list_disabled;
668 --
669 --
670 procedure trunc_list_disabled (p_person_id in number,
671 p_list in out nocopy varchar2)
672 is
673 l_pos integer;
674 l_id_text varchar2(15);
675 l_length integer;
676 begin
677 -- p_list is assumed to be like eg. '897,6734,9912'
678 if length(p_list) > 0 then
679 l_pos := posid_in_list(p_person_id,p_list);
680 if l_pos > 0 then
681 l_id_text := to_char(p_person_id);
682 l_length := length(l_id_text);
683 if substr(p_list,l_pos+l_length,1) = ',' then
684 l_length := l_length + 1;
685 else
686 if substr(p_list,l_pos-1,1) = ',' then
687 l_pos := l_pos - 1;
688 l_length := l_length + 1;
689 end if;
690 end if;
691 p_list := substr(p_list,1,l_pos-1) || substr(p_list,l_pos+l_length);
692 hr_utility.set_location('removed '||l_id_text||' from list of disabled',50);
693 hr_utility.set_location('new list is '||substr(p_list,1,80),70);
694 end if;
695 end if;
696 end trunc_list_disabled;
697 --
698 --
699 procedure update_particular (p_establishment_id in number,
700 p_person_id in number,
701 p_1jan in date,
702 p_31dec in date,
703 p_business_group_id in number,
704 p_employee_count in number,
705 p_headcount_particular in out nocopy number,
706 p_pcs_count in out nocopy table_of_number,
707 p_pcs_codes in out nocopy table_of_Varchar)
708 is
709 l_pcs_code_text varchar2(30);
710 l_pcs_code number(30):=0;
711 l_pcs_particular varchar2(1);
712 l_job_title per_jobs_v.name%TYPE;
713 -- #4068197
714 l_exists varchar2(1);
715 l_pcs_code_count number;
716 -- #4068197
717 begin
718 --
719 get_job_info (p_establishment_id,
720 p_person_id,
721 p_1jan,
722 p_31dec,
723 l_pcs_code_text,
724 l_job_title);
725 --
726 hr_utility.set_location('update_particular pcs_code='||l_pcs_code_text,10);
727 --
728 --
729 Begin
730 l_pcs_particular := hruserdt.get_table_value (p_business_group_id,
731 'FR_PCS_CODE', 'FR_D2_PARTICULAR', l_pcs_code_text, p_31dec);
732 exception
733 when no_data_found then
734 l_pcs_particular := 'N';
735 end;
736
737 Begin
738 IF l_pcs_particular = 'N' THEN
739 l_pcs_particular := hruserdt.get_table_value (p_business_group_id,
740 'FR_NEW_PCS_CODE', 'FR_D2_PARTICULAR', l_pcs_code_text, p_31dec);
741 END IF;
742 Exception
743 When no_data_found then
744 l_pcs_particular := 'N';
745 end;
746 hr_utility.set_location('update_particular l_pcs_particular = '||l_pcs_particular, 11);
747 --
748 if l_pcs_particular = 'Y' then
749 p_headcount_particular := p_headcount_particular + p_employee_count;
750 l_pcs_code_count := p_pcs_count.first;
751 if l_pcs_code_count is not null then
752 l_exists := 'Y';
753 else
754 l_exists := 'N';
755 end if;
756 while l_exists = 'Y'
757 loop
758 if p_pcs_codes(l_pcs_code_count) = l_pcs_code_text then
759 l_exists := 'N';
760 else
761 l_pcs_code_count := p_pcs_count.next(l_pcs_code_count);
762 if l_pcs_code_count is not null then
763 l_exists := 'Y';
764 else
765 l_exists := 'N';
766 end if;
767 end if;
768 end loop;
769 if l_pcs_code_count is not null then
770 p_pcs_count(l_pcs_code_count) := p_pcs_count(l_pcs_code_count) + p_employee_count;
771 p_pcs_codes(l_pcs_code_count) := l_pcs_code_text;
772 else
773 l_pcs_code_count := p_pcs_count.last;
774 if l_pcs_code_count is null then
775 l_pcs_code_count := 0;
776 end if;
777 p_pcs_count(l_pcs_code_count+1) := p_employee_count;
778 p_pcs_codes(l_pcs_code_count + 1) := l_pcs_code_text;
779 end if;
780 end if;
781 --
782 exception
783 when no_data_found then
784 null;
785 when others then
786 hr_utility.set_location('UpdParERR:'||substr(sqlerrm,1,80),90);
787 end update_particular;
788 --
789 --
790 function string_of_particular (p_pcs_count in table_of_number,
791 p_pcs_codes in table_of_varchar)
792 return varchar2
793 is
794 l_string varchar2(32000);
795 i binary_integer;
796 first boolean:= true;
797 begin
798 l_string := '';
799 --
800 hr_utility.set_location('enter string_of_particular',5);
801 --
802 i := p_pcs_count.first;
803 while i is not null loop
804 --
805 hr_utility.set_location('table of particular not empty',10);
806 --
807 if not first then
808 l_string := l_string || ' union ';
809 end if;
810 l_string := l_string || 'select ''' || p_pcs_codes(i) || ''' pc, ';
811 l_string := l_string || to_char(round(p_pcs_count(i),1)) || ' ph from dual';
812 i := p_pcs_count.next(i);
813 first := false;
814 end loop;
815 --
816 hr_utility.set_location('string_of_particular is '||l_string,50);
817 --
818 if l_string is null then
819 l_string := 'select 0 pc, 0 ph from dual';
820 end if;
821 --
822 hr_utility.set_location('string_of_particular is '||l_string,55);
823 --
824 return l_string;
825 --
826 exception
827 when others then
828 hr_utility.set_location('StrOfParERR:'||substr(sqlerrm,1,80),90);
829 return 'select 0 pc, 0 ph from dual';
830 end string_of_particular;
831 --
832 --
833 procedure update_count_disabled (p_person_id in number,
834 p_list in varchar2,
835 p_employee_count in number,
836 p_count_disabled in out nocopy varchar2)
837 is
838 l_pos integer;
839 l_proc varchar2(50);
840 begin
841 -- Initialising Local Variables
842 l_proc :='update_count_disabled';
843 hr_utility.set_location('enter '||l_proc,5);
844 --
845 l_pos := posid_in_list(p_person_id,p_list);
846 --
847 if l_pos > 0 then
848 --
849 hr_utility.set_location(to_char(p_person_id)||' is disabled',10);
850 --
851 p_count_disabled := p_count_disabled || to_char(p_person_id) || '=';
852 p_count_disabled := p_count_disabled || to_char(round(p_employee_count,2)) || ';';
853 --
854 end if;
855 --
856 exception
857 when others then
858 hr_utility.set_location('UpdCouDisERR:'||substr(sqlerrm,1,80),90);
859 end update_count_disabled;
860 --
861 --
862 procedure get_formula_ref (p_effective_date in date,
863 p_business_group_id in number,
864 p_formula_id out nocopy number,
865 p_formula_start_date out nocopy date)
866 is
867 begin
868 select formula_id, effective_start_date
869 into p_formula_id, p_formula_start_date
870 from ff_formulas_f
871 where formula_name = 'USER_CONTRACT_PRORATED'
872 and business_group_id = nvl(p_business_group_id,-1)
873 and p_effective_date between effective_start_date and effective_end_date;
874 exception
875 when no_data_found then
876 select formula_id, effective_start_date
877 into p_formula_id, p_formula_start_date
878 from ff_formulas_f
879 where formula_name = 'TEMPLATE_CONTRACT_PRORATED'
880 and legislation_code = 'FR'
881 and p_effective_date between effective_start_date and effective_end_date;
882 end get_formula_ref;
883 --
884 --
885 function relevant_change (block1 in block_record,
886 block2 in block_record)
887 return boolean
888 is
889 l_return boolean;
890 begin
891 if block1.asg_id = block2.asg_id
892 and block1.asg_status = block2.asg_status
893 and block1.asg_primary = block2.asg_primary
894 and nvl(block1.asg_employment_category,' ') =
895 nvl(block2.asg_employment_category,' ')
896 and nvl(block1.asg_freq,' ') = nvl(block2.asg_freq,' ')
897 and nvl(block1.asg_hours,hr_api.g_number) =
898 nvl(block2.asg_hours,hr_api.g_number)
899 and block1.asg_type = block2.asg_type
900 and nvl(block1.ctr_type,' ') = nvl(block2.ctr_type,' ')
901 and nvl(block1.ctr_fr_person_replaced,' ') =
902 nvl(block2.ctr_fr_person_replaced,' ')
903 and nvl(block1.ctr_status,' ') = nvl(block2.ctr_status,' ')
904 and nvl(block1.ass_employee_category,' ') =
905 nvl(block2.ass_employee_category,' ')
906 and nvl(block1.asg_full_time_freq,' ') = nvl(block2.asg_full_time_freq,' ')
907 and nvl(block1.asg_full_time_hours,hr_api.g_number) =
908 nvl(block2.asg_full_time_hours,hr_api.g_number)
909 and nvl(block1.asg_fte_value,hr_api.g_number) =
910 nvl(block2.asg_fte_value,hr_api.g_number)
911 and block1.per_type_id = block2.per_type_id
912 and block1.person_type_usages = block2.person_type_usages
913 then
914 l_return := false;
915 else
916 l_return := true;
917 end if;
918 --
919 return l_return;
920 end relevant_change;
921 --
922 --
923 function posid_in_list (p_id in number,
924 p_list in varchar2)
925 return integer
926 is
927 l_id_text varchar2(15);
928 l_pos integer;
929 l_char_before varchar2(1);
930 l_char_after varchar2(1);
931 begin
932 l_id_text := to_char(p_id);
933 l_pos := instr(p_list,l_id_text);
934 if l_pos > 0 then
935 if l_pos = 1 then
936 l_char_before := 'X';
937 else
938 l_char_before := nvl(substr(p_list,l_pos-1,1),'X');
939 end if;
940 if l_pos+length(l_id_text) > length(p_list) then
941 l_char_after := 'X';
942 else
943 l_char_after := nvl(substr(p_list,l_pos+length(l_id_text),1),'X');
944 end if;
945 if l_char_before in ('0','1','2','3','4','5','6','7','8','9')
946 or l_char_after in ('0','1','2','3','4','5','6','7','8','9') then
947 l_pos := 0;
948 hr_utility.set_location('id-string found in list but not a proper id',50);
949 end if;
950 end if;
951 return l_pos;
952 end posid_in_list;
953 --
954 --
955 function include_this_person_type (p_user_person_types in varchar2,
956 p_business_group_id in number,
957 p_effective_date in date)
958 return boolean
959 is
960 l_include boolean := false;
961 l_ptu_delim varchar2(10):=
962 hr_person_type_usage_info.get_user_person_type_separator;
963 l_start_pos number;
964 l_end_pos number;
965 --
966 begin
967 l_start_pos := 1;
968 while l_start_pos <= length(p_user_person_types) loop
969 l_end_pos := instr(p_user_person_types||l_ptu_delim,
970 l_ptu_delim,l_start_pos);
971 begin
972 if hruserdt.get_table_value(p_business_group_id
973 ,'FR_USER_PERSON_TYPE', 'INCLUDE_D2'
974 ,substr(p_user_person_types,l_start_pos,
975 l_end_pos-l_start_pos)
976 ,p_effective_date) = 'Y'
977 then
978 l_include := true;
979 exit;
980 end if;
981 exception when others then null;
982 end;
983 l_start_pos := l_end_pos + length(l_ptu_delim);
984 end loop;
985 --
986 return l_include;
987 end include_this_person_type;
988 --
989 --
990 procedure get_extra_units (p_establishment_id in number,
991 p_effective_date in date,
992 p_base_unit out nocopy number,
993 p_xcot_a out nocopy number,
994 p_xcot_b out nocopy number,
995 p_xcot_c out nocopy number,
996 p_xcot_young_age out nocopy number,
997 p_xcot_old_age out nocopy number,
998 p_xcot_age_units out nocopy number,
999 p_xcot_training_hours out nocopy number,
1000 p_xcot_training_units out nocopy number,
1001 p_xcot_ap out nocopy number,
1002 p_xcot_impro out nocopy number,
1003 p_xcot_cat out nocopy number,
1004 p_xcot_cdtd out nocopy number,
1005 p_xcot_cfp out nocopy number,
1006 p_xipp_low_rate out nocopy number,
1007 p_xipp_medium_rate out nocopy number,
1008 p_xipp_high_rate out nocopy number,
1009 p_xipp_low_units out nocopy number,
1010 p_xipp_medium_units out nocopy number,
1011 p_xipp_high_units out nocopy number,
1012 p_hire_units out nocopy number)
1013 is
1014 begin
1015 if g_extra_units.effective_date is null
1016 or g_extra_units.effective_date <> p_effective_date
1017 then
1018 -- Prime cache
1019 open csr_get_extra_units(p_effective_date);
1020 fetch csr_get_extra_units into g_extra_units.rec;
1021 close csr_get_extra_units;
1022 g_extra_units.effective_date := p_effective_date;
1023 -- don't use or cache p_establishment_id as the extra units are seeded.
1024 end if;
1025 p_base_unit := g_extra_units.rec.base_unit;
1026 p_xcot_a := g_extra_units.rec.x_cot_a;
1027 p_xcot_b := g_extra_units.rec.x_cot_b;
1028 p_xcot_c := g_extra_units.rec.x_cot_c;
1029 p_xcot_young_age := g_extra_units.rec.x_cot_young_age;
1030 p_xcot_old_age := g_extra_units.rec.x_cot_old_age;
1031 p_xcot_age_units := g_extra_units.rec.x_cot_age_units;
1032 p_xcot_training_hours := g_extra_units.rec.x_cot_training_hours;
1033 p_xcot_training_units := g_extra_units.rec.x_cot_training_units;
1034 p_xcot_ap := g_extra_units.rec.x_cot_ap;
1035 p_xcot_impro := g_extra_units.rec.x_cot_impro;
1036 p_xcot_cat := g_extra_units.rec.x_cot_cat;
1037 p_xcot_cdtd := g_extra_units.rec.x_cot_cdtd;
1038 p_xcot_cfp := g_extra_units.rec.x_cot_cfp;
1039 p_xipp_low_rate := g_extra_units.rec.x_ipp_low_rate;
1040 p_xipp_medium_rate := g_extra_units.rec.x_ipp_medium_rate;
1041 p_xipp_high_rate := g_extra_units.rec.x_ipp_high_rate;
1042 p_xipp_low_units := g_extra_units.rec.x_ipp_low_units;
1043 p_xipp_medium_units := g_extra_units.rec.x_ipp_medium_units;
1044 p_xipp_high_units := g_extra_units.rec.x_ipp_high_units;
1045 p_hire_units := g_extra_units.rec.x_hire_units;
1046 exception
1047 when others then
1048 hr_utility.set_location('GetExtUniERR:'||substr(sqlerrm,1,80),90);
1049 end get_extra_units;
1050 --
1051 --
1052 procedure populate_blocks_table (p_establishment_id in number,
1053 p_1jan in date,
1054 p_31dec in date,
1055 p_person_id in number,
1056 p_blocks out nocopy table_of_block)
1057 is
1058 l_block_start date;
1059 l_block_end date;
1060 l_period_start date;
1061 l_period_end date;
1062 l_asg_done boolean;
1063 begin
1064 --
1065 -- first clear table
1066 --
1067 p_blocks.delete;
1068 --
1069 for rec_asg in csr_get_asg_emp (p_establishment_id,
1070 p_1jan,
1071 p_31dec,
1072 p_person_id) loop
1073 --
1074 l_period_start := p_1jan;
1075 l_period_end := p_31dec;
1076 l_asg_done := false;
1077 --
1078 while not l_asg_done loop
1079 l_block_end := latest_block (rec_asg.asg_id,
1080 p_establishment_id,
1081 l_period_start,
1082 l_period_end);
1083 --
1084 if l_block_end = to_date('31124712','DDMMYYYY') then
1085 l_asg_done := true;
1086 else
1087 l_block_start := beginning_of_block(rec_asg.asg_id,l_block_end,p_1jan);
1088 add_block_row (p_blocks,rec_asg.asg_id,l_block_start,l_block_end);
1089 l_period_end := l_block_start - 1;
1090 if l_block_start = p_1jan then
1091 l_asg_done := true;
1092 end if;
1093 end if;
1094 end loop;
1095 end loop;
1096 exception
1097 when others then
1098 hr_utility.set_location('PopBloTabERR:'||substr(sqlerrm,1,80),90);
1099 end populate_blocks_table;
1100 --
1101 --
1102 function latest_block (p_assignment_id in number,
1103 p_establishment_id in number,
1104 p_start_period in date,
1105 p_end_period in date)
1106 return date
1107 is
1108 l_end_date date;
1109 begin
1110 --
1111 select nvl(least(p_end_period,max(a.effective_end_date)),to_date('31124712','DDMMYYYY'))
1112 into l_end_date
1113 from per_assignment_status_types t,
1114 per_all_assignments_f a
1115 where a.assignment_id = p_assignment_id
1116 and a.establishment_id = p_establishment_id
1117 and a.effective_start_date <= p_end_period
1118 and a.effective_end_date >= p_start_period
1119 and a.assignment_type in ('E','C')
1120 and t.assignment_status_type_id = a.assignment_status_type_id
1121 and nvl(t.per_system_status,'') in ('ACTIVE_ASSIGN','SUSP_ASSIGN'
1122 ,'ACTIVE_CWK','SUSP_CWK_ASG');
1123 --
1124 if sql%found then
1125 return l_end_date;
1126 else
1127 return to_date('31124712','DDMMYYYY');
1128 end if;
1129 exception
1130 when others then
1131 hr_utility.set_location('LatBloERR:'||substr(sqlerrm,1,80),90);
1132 end latest_block;
1133 --
1134 --
1135 function beginning_of_block (p_assignment_id in number,
1136 p_end_date in date,
1137 p_1jan in date)
1138 return date
1139 is
1140 l_start_asg date;
1141 l_start_ctr date;
1142 l_start_bud date;
1143 l_start_per date;
1144 l_start_ptu date;
1145 begin
1146 -- get latest change to:
1147 -- The assignment
1148 -- The person
1149 -- The latest assignment row's contract, if any
1150 -- The latest person type change (there may be multiple concurrent person
1151 -- types, some of which may end prior to the end of this period).
1152 -- The latest FTE budget value change, if any (such rows may not be
1153 -- contiguous and may end prior to the end of this period).
1154 select a.effective_start_date,
1155 p.effective_start_date,
1156 c.effective_start_date,
1157 max(decode(sign(ptu.effective_end_date-p_end_date),
1158 -1,ptu.effective_end_date+1,
1159 ptu.effective_start_date)),
1160 max(decode(sign(b.effective_end_date-p_end_date),
1161 -1,b.effective_end_date+1,
1162 b.effective_start_date))
1163 into l_start_asg,
1164 l_start_per,
1165 l_start_ctr,
1166 l_start_ptu,
1167 l_start_bud
1168 from per_all_assignments_f a,
1169 per_all_people_f p,
1170 per_contracts_f c,
1171 per_person_type_usages_f ptu,
1172 per_assignment_budget_values_f b
1173 where a.assignment_id = p_assignment_id
1174 and p.person_id = a.person_id
1175 and c.contract_id(+) = a.contract_id
1176 and ptu.person_id = p.person_id
1177 and b.assignment_id(+) = a.assignment_id
1178 and b.unit (+) = 'FTE'
1179 and p_end_date between a.effective_start_date
1180 and a.effective_end_date
1181 and p_end_date between p.effective_start_date
1182 and p.effective_end_date
1183 and p_end_date between c.effective_start_date(+)
1184 and c.effective_end_date(+)
1185 and ptu.effective_start_date <= p_end_date
1186 and ptu.effective_end_date >= p.effective_start_date
1187 and b.effective_start_date(+) <= p_end_date
1188 and b.effective_end_date(+) >= a.effective_start_date
1189 group by a.effective_start_date,
1190 p.effective_start_date,
1191 c.effective_start_date;
1192 --
1193 return greatest(p_1jan,
1194 l_start_asg,
1195 l_start_per,
1196 nvl(l_start_ctr,l_start_per),
1197 l_start_ptu,
1198 nvl(l_start_bud,l_start_asg));
1199 --
1200 exception
1201 when no_data_found then
1202 return p_1jan;
1203 when others then
1204 hr_utility.set_location('BegOfBloERR:'||substr(sqlerrm,1,80),90);
1205 end beginning_of_block;
1206 --
1207 --
1208 procedure add_block_row (p_block_table in out nocopy table_of_block,
1209 p_assignment_id in number,
1210 p_start_date in date,
1211 p_end_date in date)
1212 is
1213 i binary_integer;
1214 l_person_id per_all_people_f.person_id%TYPE;
1215 l_ptu_delim varchar2(10);
1216 --
1217 cursor csr_get_person_type_usages is
1218 select ppttl.user_person_type
1219 from per_person_type_usages_f pptu,
1220 per_person_types_tl ppttl
1221 where pptu.person_id = l_person_id
1222 and ppttl.person_type_id = pptu.person_type_id
1223 and p_start_date between pptu.effective_start_date
1224 and pptu.effective_end_date
1225 and ppttl.language = userenv('LANG');
1226 --
1227 l_user_person_type per_person_types_tl.user_person_type%TYPE;
1228 begin
1229 --
1230 i := p_block_table.last;
1231 if i is null then
1232 i:=1;
1233 else
1234 i:=i+1;
1235 end if;
1236 --
1237 p_block_table(i).asg_id := p_assignment_id;
1238 p_block_table(i).block_start_date := p_start_date;
1239 p_block_table(i).block_end_date := p_end_date;
1240 --
1241 select per.person_type_id,
1242 asg.assignment_status_type_id,
1243 asg.primary_flag,
1244 asg.employment_category,
1245 asg.frequency,
1246 asg.normal_hours,
1247 ctr.type,
1248 ctr.ctr_information5,
1249 ctr.status,
1250 scl.segment2,
1251 nvl(pos.frequency,nvl(org.ORG_INFORMATION4,bus.ORG_INFORMATION4)),
1252 nvl(pos.working_hours,
1253 fnd_number.canonical_to_number(nvl(org.ORG_INFORMATION3,
1254 bus.ORG_INFORMATION3))),
1255 bud.value,
1256 asg.assignment_type,
1257 asg.person_id
1258 into p_block_table(i).per_type_id,
1259 p_block_table(i).asg_status,
1260 p_block_table(i).asg_primary,
1261 p_block_table(i).asg_employment_category,
1262 p_block_table(i).asg_freq,
1263 p_block_table(i).asg_hours,
1264 p_block_table(i).ctr_type,
1265 p_block_table(i).ctr_fr_person_replaced,
1266 p_block_table(i).ctr_status,
1267 p_block_table(i).ass_employee_category,
1268 p_block_table(i).asg_full_time_freq,
1269 p_block_table(i).asg_full_time_hours,
1270 p_block_table(i).asg_fte_value,
1271 p_block_table(i).asg_type,
1272 l_person_id
1273 from per_all_people_f per,
1274 per_contracts_f ctr,
1275 hr_soft_coding_keyflex scl,
1276 per_all_positions pos,
1277 hr_organization_information org,
1278 hr_organization_information bus,
1279 per_assignment_budget_values_f bud,
1280 per_all_assignments_f asg
1281 where asg.assignment_id = p_assignment_id
1282 and p_start_date between asg.effective_start_date
1283 and asg.effective_end_date
1284 and per.person_id = asg.person_id
1285 and p_start_date between per.effective_start_date
1286 and per.effective_end_date
1287 and ctr.contract_id (+) = asg.contract_id
1288 and ctr.ctr_information_category (+) = 'FR'
1289 and p_start_date between ctr.effective_start_date (+)
1290 and ctr.effective_end_date (+)
1291 and scl.soft_coding_keyflex_id (+) = asg.soft_coding_keyflex_id
1292 and pos.position_id (+) = asg.position_id
1293 and org.organization_id (+) = asg.organization_id
1294 and org.org_information_context (+) || '' = 'Work Day Information'
1295 and bus.organization_id (+) = asg.business_group_id
1296 and bus.org_information_context (+) || '' = 'Work Day Information'
1297 and bud.assignment_id (+) = asg.assignment_id
1298 and p_start_date between bud.effective_start_date (+)
1299 and bud.effective_end_date (+)
1300 and bud.unit (+) = 'FTE';
1301 --
1302 open csr_get_person_type_usages;
1303 fetch csr_get_person_type_usages into p_block_table(i).person_type_usages;
1304 if csr_get_person_type_usages%FOUND then
1305 l_ptu_delim := hr_person_type_usage_info.get_user_person_type_separator;
1306 loop
1307 fetch csr_get_person_type_usages into l_user_person_type;
1308 exit when csr_get_person_type_usages%NOTFOUND;
1309 p_block_table(i).person_type_usages :=
1310 p_block_table(i).person_type_usages ||
1311 l_ptu_delim || l_user_person_type;
1312 end loop;
1313 end if;
1314 close csr_get_person_type_usages;
1315 --
1316 exception
1317 when others then
1318 hr_utility.set_location('AddBloRowERR:'||substr(sqlerrm,1,80),90);
1319 end add_block_row;
1320 --
1321 --
1322 END PER_FR_D2_PKG;