[Home] [Help]
PACKAGE BODY: APPS.PQH_PSF_BUS
Source
1 package body PQH_PSF_BUS as
2 /* $Header: pqpsfbus.pkb 120.10.12010000.1 2008/07/28 13:04:27 appldev ship $ */
3 --
4 function POSITION_CONTROL_ENABLED(P_ORGANIZATION_ID NUMBER default null,
5 p_effective_date in date default sysdate,
6 p_assignment_id number default null) RETURN VARCHAR2 IS
7 --
8 l_return varchar2(100);
9 --
10 BEGIN
11 l_return := per_pqh_shr.POSITION_CONTROL_ENABLED(P_ORGANIZATION_ID => p_organization_id,
12 p_effective_date => p_effective_date,
13 p_assignment_id => p_assignment_id);
14 return l_return;
15 --
16 END;
17 --
18 --
19 --
20 function pos_assignments_exist(p_position_id number,
21 p_validation_start_date date, p_validation_end_date date)
22 return boolean is
23 l_dummy varchar2(10);
24 l_proc varchar2(100):= 'PQH_PSF_BUS.POS_ASSIGNMENTS_EXIST';
25
26 cursor c1 is
27 select 'x'
28 from dual
29 where exists (
30 select null
31 from per_all_assignments_f asg, per_assignment_status_types ast
32 where asg.position_id = p_position_id
33 and asg.assignment_type in ('E', 'C','A') -- changes made for the bug 5680305
34 and asg.assignment_status_type_id = ast.assignment_status_type_id
35 and ast.per_system_status <> 'TERM_ASSIGN'
36 and ((asg.effective_start_date between p_validation_start_date
37 and p_validation_end_date) or
38 (asg.effective_end_date between p_validation_start_date
39 and p_validation_end_date) or
40 (asg.effective_start_date <= p_validation_start_date
41 and effective_end_date >=p_validation_end_date))
42 );
43 begin
44 hr_utility.set_location('Entering '||l_proc,10);
45 open c1;
46 fetch c1 into l_dummy;
47 if c1%found then
48 close c1;
49 hr_utility.set_location('Leaveing '||l_proc,11);
50 return true;
51 end if;
52 close c1;
53 hr_utility.set_location('Leaveing '||l_proc,12);
54 return false;
55 end pos_assignments_exist;
56 --
57 --
58 -- ---------------------------------------------------------------------------
59 -- |-----------------< hr_psf_bus_insert_validate >---------------------|
60 -- ---------------------------------------------------------------------------
61 --
62 procedure hr_psf_bus_insert_validate(p_rec in hr_psf_shd.g_rec_type
63 ,p_effective_date in date
64 ) is
65 l_chk_position_job_grade boolean;
66 l_transaction_status varchar2(100);
67 --
68 cursor c_position_transaction(p_position_transaction_id number) is
69 select transaction_status
70 from pqh_position_transactions
71 where position_transaction_id = p_position_transaction_id;
72 --
73 begin
74 null;
75 /*
76 hr_utility.set_location('Check position is submitted for PC Org validation rule:', 630);
77 --
78 if p_rec.position_transaction_id is not null then
79 open c_position_transaction(p_rec.position_transaction_id);
80 fetch c_position_transaction into l_transaction_status;
81 if (l_transaction_status <> 'SUBMITTED') then
82 hr_utility.set_message(8302, 'PQH_NO_SUBMIT_CANT_CRE_POS');
83 hr_utility.raise_error;
84 end if;
85 end if;
86 */
87 --
88 --
89 end;
90 --
91 -- ---------------------------------------------------------------------------
92 -- |-----------------< hr_psf_bus_update_validate >---------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 procedure hr_psf_bus_update_validate(p_rec in hr_psf_shd.g_rec_type
96 ,p_effective_date in date
97 ,p_validation_start_date in date
98 ,p_validation_end_date in date
99 ,p_datetrack_mode in varchar2
100 ) is
101 --
102 --
103 l_chk_seasonal boolean;
104 l_chk_overlap boolean;
105 l_permit_extended_pay boolean;
106 l_chk_work_pay_term_dates boolean;
107 l_chk_position_job_grade boolean;
108 l_chk_earliest_hire_date boolean;
109 l_chk_prop_date_for_layoff boolean;
110 l_transaction_status varchar2(100);
111 l_asg_max_count number;
112 --
113 cursor c_position_transaction(p_position_transaction_id number) is
114 select transaction_status
115 from pqh_position_transactions
116 where position_transaction_id = p_position_transaction_id;
117 --
118 cursor c_asg_max_count(p_position_id number
119 , p_validation_start_date date
120 , p_validation_end_date date) is
121 select max(pqh_psf_bus.sum_assignment_fte(p_position_id,ed))
122 from
123 (select a.effective_start_date ed
124 from per_all_assignments_f a
125 where a.position_id = p_position_id
126 and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
127 and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
128 ))
129 union
130 select a.effective_end_date ed
131 from per_all_assignments_f a
132 where a.position_id = p_position_id
133 and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
134 and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
135 ))
136 union
137 select p_validation_start_date ed
138 from dual
139 union
140 select p_validation_end_date ed
141 from dual);
142 --
143 begin
144 --
145 /*
146 if p_rec.position_transaction_id is not null then
147 open c_position_transaction(p_rec.position_transaction_id);
148 fetch c_position_transaction into l_transaction_status;
149 if (l_transaction_status <> 'SUBMITTED') then
150 hr_utility.set_message(8302, 'PQH_NO_SUBMIT_CANT_CRE_POS');
151 hr_utility.raise_error;
152 end if;
153 end if;
154 */
155 --
156 --
157 --
158 hr_utility.set_location('AVAILABILITY_STATUS_ID :'||p_rec.availability_status_id, 620);
159
160 if ((p_datetrack_mode IN ('CORRECTION', 'UPDATE','UPDATE_CHANGE_INSERT',
161 'UPDATE_OVERRIDE')) AND
162 ('ELIMINATED'=hr_psf_shd.get_availability_status(
163 p_rec.availability_status_id,p_rec.business_group_id))) then
164 hr_utility.set_location('AVAILABILITY STATUS : ELIMINATED', 621);
165 if (pqh_psf_bus.pos_assignments_exist(p_rec.position_id,
166 p_validation_start_date, p_validation_end_date)) then
167 hr_utility.set_location('ASSIGNMENTS EXIST FOR ELIMINATED POSITION', 623);
168 pqh_utility.set_message(800,'PER_POS_ELIMINATED',p_rec.organization_id);
169 pqh_utility.raise_error;
170 end if;
171 end if;
172 --
173 --
174 --
175 hr_utility.set_location('Check FTE validation rule:', 630);
176 --
177 -- Check FTE Validation Rule
178 --
179 --
180 hr_utility.set_location('p_validation_start_date:'||p_validation_start_date, 630);
181 hr_utility.set_location('p_validation_end_date:'||p_validation_end_date, 630);
182 --
183 if (p_rec.position_type = 'SHARED') then
184 --
185 open c_asg_max_count(p_rec.position_id, p_validation_start_date, p_validation_end_date);
186 fetch c_asg_max_count into l_asg_max_count;
187 close c_asg_max_count;
188 hr_utility.set_location('l_max_count:'||l_asg_max_count, 630);
189 hr_utility.set_location('Position FTE:'||p_rec.fte, 630);
190 if (l_asg_max_count > p_rec.fte) then
191 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',p_rec.organization_id);
192 pqh_utility.raise_error;
193 end if;
194 hr_utility.set_location('Check FTE validation rule:', 630);
195 --
196 end if;
197 --
198 hr_utility.set_location('Check Earliest hire date validation rule:', 640);
199 --
200 -- Check Earliest hire date validation rule
201 --
202 --
203 l_chk_earliest_hire_date := pqh_psf_bus.chk_earliest_hire_date(
204 p_position_id => p_rec.position_id, p_earliest_hire_date => p_rec.earliest_hire_date );
205 if (not l_chk_earliest_hire_date ) then
206 pqh_utility.set_message(8302,'PQH_ASG_HIRED_BEFORE_EARLIEST',p_rec.organization_id);
207 pqh_utility.raise_error;
208 end if;
209 hr_utility.set_location('Check Earliest hire date validation rule:', 640);
210 --
211 -- Check proposed date for layoff validation rule
212 --
213 --
214 l_chk_prop_date_for_layoff := pqh_psf_bus.chk_prop_date_for_layoff(
215 p_position_id => p_rec.position_id, p_proposed_date_for_layoff => p_rec.proposed_date_for_layoff );
216 if (not l_chk_prop_date_for_layoff ) then
217 --pqh_utility.set_message(8302,'PQH_LAYOFF_DT_GT_ASG_DT',p_rec.organization_id);
218 pqh_utility.set_message(8302,'PQH_ASG_DT_GT_LAYOFF_DT',p_rec.organization_id);
219 pqh_utility.raise_error;
220 end if;
221 hr_utility.set_location('Check proposed date for layoff validation rule:', 650);
222 end;
223 --
224 -- ---------------------------------------------------------------------------
225 -- |-----------------< per_asg_bus_insert_validate >--------------------|
226 -- ---------------------------------------------------------------------------
227 --
228 PROCEDURE per_asg_bus_insert_validate(p_rec per_asg_shd.g_rec_type
229 ,p_effective_date in date) IS
230 l_bgt_lt_abv_fte boolean := false;
231 l_open_status varchar2(30);
232 l_proposed_date_for_layoff date;
233 l_rec hr_all_positions%rowtype;
234 l_fte_capacity number(15,2);
235 l_sum number(15,2);
236 l_bdgt number(15,2);
237 l_available_fte number:=0;
238 l_person_fte number:=0;
239 l_default_asg_fte number:=0;
240 l_chk_pos_budget boolean;
241 l_overlap_dates_present boolean := false;
242 l_future_res_date date;
243 l_asg_st_date date;
244 l_realloc number;
245 l_bgt_realloc number;
246
247 cursor c_position is
248 select *
249 from hr_all_positions
250 where position_id = p_rec.position_id;
251 --
252 cursor c_single_pos_future_asg(p_position_id number, p_effective_date date) is
253 select min(effective_start_date)
254 from per_all_assignments_f
255 where position_id = p_position_id
256 and effective_start_date > p_effective_date;
257 --
258 BEGIN
259 hr_utility.set_location('Insert Validate: Before open Position', 100);
260 if (p_rec.position_id is not null) then
261 open c_position;
262 hr_utility.set_location('Insert Validate: After open before fetch Position', 110);
263 fetch c_position into l_rec;
264 hr_utility.set_location('Insert Validate: After fetch Position', 120);
265 if ( c_position%found) then
266 close c_position;
267 --
268 -- Check for Assignment attached to a Seasonal Position is with in seasonal dates
269 --
270 hr_utility.set_location('Insert Validate: Before Seasonal Validation', 130);
271 if l_rec.seasonal_flag = 'Y' then
272 if not pqh_psf_bus.chk_seasonal_dates(
273 p_position_id => p_rec.position_id,
274 p_seasonal_flag => l_rec.seasonal_flag,
275 p_assignment_start_date => p_effective_date) then
276 pqh_utility.set_message(8302,'PQH_NON_SEASONAL_ASG_DATE',l_rec.organization_id);
277 pqh_utility.raise_error;
278 end if;
279 end if;
280 --
281 -- Check whether Assignment Grade is same as Position Grade.
282 --
283 hr_utility.set_location('Insert Validate: Before Assignment Grade', 130);
284 if p_rec.grade_id <> l_rec.entry_grade_id then
285 pqh_utility.set_message(8302,'PQH_NON_POSITION_GRADE',l_rec.organization_id);
286 pqh_utility.raise_error;
287 end if;
288 --
289 -- Check assignment start date to be greater than earliest hire date of the position
290 --
291 hr_utility.set_location('Insert Validate: greater than earliest hire date', 130);
292 if p_rec.effective_start_date < l_rec.earliest_hire_date then
293 pqh_utility.set_message(8302,'PQH_ASG_HIRED_BEFORE_EARLIEST',l_rec.organization_id);
294 pqh_utility.raise_error;
295 end if;
296 --
297 -- Check whether assignment date is before proposed date for Layoff
298 --
299 hr_utility.set_location('Insert Validate: before proposed date for Layoff', 130);
300 if (p_rec.effective_start_date > l_rec.proposed_date_for_layoff ) then
301 pqh_utility.set_message(8302,'PQH_ASG_DT_GT_LAYOFF_DT',l_rec.organization_id);
302 pqh_utility.raise_error;
303 end if;
304 --
305 -- Validate whether a SHARED position has FTE greater than the sum of the budgeted FTE's attached to the Position
306 --
307 hr_utility.set_location('Insert Validate: SHARED position has FTE greater than', 130);
308 if (l_rec.position_type = 'SHARED') then
309 --
310 l_default_asg_fte := pqh_psf_bus.default_assignment_fte(p_rec.business_group_id);
311 --
312 pqh_psf_bus.CHK_ABV_FTE_GT_POS_BGT_FTE(
313 p_assignment_id => p_rec.assignment_id,
314 p_position_id => p_rec.position_id,
315 p_effective_date => p_rec.effective_start_date,
316 p_default_asg_fte => l_default_asg_fte,
317 p_bgt_lt_abv_fte => l_bgt_lt_abv_fte
318 );
319 --
320 elsif (l_rec.position_type = 'SINGLE') then
321 l_bgt_lt_abv_fte := false;
322 l_default_asg_fte := 1;
323 end if;
324 --
325 if not l_bgt_lt_abv_fte then
326 hr_utility.set_location('l_default_asg_fte :'||l_default_asg_fte, 135);
327 pqh_psf_bus.chk_future_pos_asg_fte(
328 p_assignment_id => p_rec.assignment_id,
329 p_position_id => p_rec.position_id,
330 p_validation_start_date => p_rec.effective_start_date,
331 p_validation_end_date => hr_general.end_of_time,
332 p_default_asg_fte => l_default_asg_fte);
333 end if;
334 --
335 --Check Insert allowed..
336 --
337 hr_utility.set_location('Insert Validate:Check Insert allowed', 130);
338 if l_rec.position_type = 'POOLED' then
339 hr_utility.set_location('Insert Validate:POOLED', 130);
340 if pqh_psf_bus.open_status(p_rec.position_id, p_rec.effective_start_date) = 'OPEN' then
341 null;
342 else
343 pqh_utility.set_message(8302,'PQH_POOLED_POS_NOT_OPEN',l_rec.organization_id);
344 pqh_utility.raise_error;
345 end if;
346 hr_utility.set_location('Insert Validate:END POOLED', 130);
347 elsif l_rec.position_type = 'SINGLE' or l_rec.position_type = 'SHARED' then
348 hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
349 if pqh_psf_bus.open_status(p_rec.position_id, p_rec.effective_start_date) = 'OPEN' then
350 hr_utility.set_location('Insert Validate:OPEN', 130);
351 if not l_bgt_lt_abv_fte then
352 hr_utility.set_location('p_rec.business_group_id : '||p_rec.business_group_id, 131);
353 if l_rec.position_type = 'SINGLE' then
354 l_default_asg_fte := 1;
355 else
356 l_default_asg_fte := pqh_psf_bus.default_assignment_fte(p_rec.business_group_id);
357 end if;
358 hr_utility.set_location('l_default_asg_fte : '||l_default_asg_fte, 13);
359 --
360 if chk_reserved_fte(p_rec.assignment_id, p_rec.person_id,
361 p_rec.position_id, l_rec.position_type,
362 p_rec.effective_start_date, l_default_asg_fte) then
363 pqh_psf_bus.reserved_error(p_rec.assignment_id,
364 p_rec.person_id,
365 p_rec.position_id,
366 p_rec.effective_start_date,
367 l_rec.organization_id,
368 l_default_asg_fte);
369 --hr_utility.set_location('POSITION RESERVED', 114);
370 --pqh_utility.set_message(8302,'PQH_POS_RESERVED',l_rec.organization_id);
371 --pqh_utility.raise_error;
372 else
373 l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
374 p_rec.position_id, l_rec.position_type,
375 p_rec.effective_start_date, hr_general.end_of_time, l_default_asg_fte);
376 if l_future_res_date is not null then
377 hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
378 hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
379 pqh_utility.set_message_level_cd('W');
380 pqh_utility.raise_error;
381 end if;
382 end if;
383 end if;
384 end if;
385 hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
386 elsif (l_rec.position_type = 'NONE') then
387 null;
388 end if;
389 --
390 /*
391 hr_utility.set_location('Money Related Rule before chk_pos_budget', 135);
392 l_chk_pos_budget := chk_pos_budget(p_rec.position_id, p_rec.effective_start_date);
393 hr_utility.set_location('Money Related Rule after chk_pos_budget', 136);
394 if not l_chk_pos_budget then
395 hr_utility.set_location('Money Related Rule failed', 140);
396 pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_rec.organization_id);
397 pqh_utility.raise_error;
398 else
399 hr_utility.set_location('Money Related Rule success', 140);
400 end if;
401 */
402 else
403 close c_position;
404 end if;
405 end if;
406 --
407 hr_utility.set_location('per_asg_insert_validate End',400);
408 END;
409 --
410 -- ---------------------------------------------------------------------------
411 -- |-----------------< per_asg_bus_update_validate >--------------------|
412 -- ---------------------------------------------------------------------------
413 --
414 PROCEDURE per_asg_bus_update_validate(p_rec per_asg_shd.g_rec_type
415 ,p_effective_date in date
416 ,p_validation_start_date in date
417 ,p_validation_end_date in date
418 ,p_datetrack_mode in varchar2 ) IS
419 l_bgt_lt_abv_fte boolean := false;
420 l_open_status varchar2(30);
421 l_proposed_date_for_layoff date;
422 l_rec hr_all_positions%rowtype;
423 l_asg per_all_assignments%rowtype;
424 l_fte_capacity number(15,2);
425 l_sum number(15,2);
426 l_bdgt number(15,2);
427 l_available_fte number:=0;
428 l_assignment_fte number:=0;
429 l_overlap_dates_present boolean := false;
430 l_future_res_date date;
431 l_asg_st_date date;
432 l_realloc number;
433 l_bgt_realloc number;
434 l_default_asg_fte number;
435
436 cursor c_position is
437 select *
438 from hr_all_positions
439 where position_id = p_rec.position_id;
440
441 cursor c_assignment is
442 select *
443 from per_all_assignments
444 where assignment_id = p_rec.assignment_id;
445 --
446 cursor c_single_pos_future_asg(p_position_id number, p_effective_date date) is
447 select min(effective_start_date)
448 from per_all_assignments_f
449 where position_id = p_position_id
450 and effective_start_date > p_effective_date;
451 --
452 BEGIN
453 hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
454 hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
455 hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
456 hr_utility.set_location('p_effective_date '||p_effective_date, 101);
457 hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
458
459 open c_position;
460 fetch c_position into l_rec;
461
462 open c_assignment;
463 fetch c_assignment into l_asg;
464
465 if (c_position%found) then
466 close c_position;
467 --
468 -- Check for Assignment attached to a Seasonal Position is with in seasonal dates
469 --
470 if p_rec.position_id <> nvl(l_asg.position_id, -999) then
471 if l_rec.seasonal_flag = 'Y' then
472 if not pqh_psf_bus.chk_seasonal_dates(
473 p_position_id => p_rec.position_id,
474 p_seasonal_flag => l_rec.seasonal_flag,
475 p_assignment_start_date => p_validation_start_date) then
476
477 pqh_utility.set_message(8302,'PQH_NON_SEASONAL_ASG_DATE',l_rec.organization_id);
478 pqh_utility.raise_error;
479 end if;
480 end if;
481 end if;
482 --
483 -- Check whether Assignment Grade is same as Position Grade.
484 --
485 if p_rec.position_id <> nvl(l_asg.position_id, -999) or p_rec.grade_id <> l_asg.grade_id then
486 if p_rec.grade_id <> l_rec.entry_grade_id then
487 pqh_utility.set_message(8302,'PQH_NON_POSITION_GRADE',l_rec.organization_id);
488 pqh_utility.raise_error;
489 end if;
490 end if;
491 --
492 -- Check assignment start date to be greater than earliest hire date of the position
493 --
494 if p_rec.position_id <> nvl(l_asg.position_id, -999) or p_validation_start_date <> l_asg.effective_start_date then
495 if p_validation_start_date < l_rec.earliest_hire_date then
496 pqh_utility.set_message(8302,'PQH_ASG_HIRED_BEFORE_EARLIEST',l_rec.organization_id);
497 pqh_utility.raise_error;
498 end if;
499 --
500 -- Check whether assignment date is before proposed date for Layoff
501 --
502 if (p_validation_start_date > l_rec.proposed_date_for_layoff ) then
503 pqh_utility.set_message(8302,'PQH_ASG_DT_GT_LAYOFF_DT',l_rec.organization_id);
504 pqh_utility.raise_error;
505 end if;
506 end if;
507 --
508 -- Validate whether a SHARED position has FTE greater than the sum of the budgeted FTE's attached to the Position
509 --
510 --
511 -- Validate whether a SHARED position has FTE greater than the sum of the budgeted FTE's attached to the Position
512 --
513 hr_utility.set_location('Insert Validate: SHARED position has FTE greater than', 130);
514 if p_rec.position_id <> nvl(l_asg.position_id, -999) then
515 if (l_rec.position_type = 'SHARED') then
516 --
517 hr_utility.set_location('before l_overlap_dates_present ', 100);
518 l_default_asg_fte := null;
519 --pqh_psf_bus.default_assignment_fte(p_rec.business_group_id);
520 --
521 pqh_psf_bus.CHK_ABV_FTE_GT_POS_BGT_FTE(
522 p_assignment_id => p_rec.assignment_id,
523 p_position_id => p_rec.position_id,
524 p_effective_date => p_validation_start_date,
525 p_default_asg_fte => l_default_asg_fte,
526 p_bgt_lt_abv_fte => l_bgt_lt_abv_fte
527 );
528 --
529 elsif (l_rec.position_type = 'SINGLE') then
530 l_bgt_lt_abv_fte := false;
531 l_default_asg_fte := 1;
532 end if;
533 --
534 if not l_bgt_lt_abv_fte then
535 hr_utility.set_location('l_default_asg_fte :'||l_default_asg_fte, 135);
536 pqh_psf_bus.chk_future_pos_asg_fte(
537 p_assignment_id => p_rec.assignment_id,
538 p_position_id => p_rec.position_id,
539 p_validation_start_date => p_validation_start_date,
540 p_validation_end_date => hr_general.end_of_time,
541 p_default_asg_fte => l_default_asg_fte);
542 end if;
543 --
544 hr_utility.set_location('after chk_future_pos_asg_fte ', 100);
545 --
546 if (l_rec.position_type = 'SHARED') then
547 --
548 hr_utility.set_location('before PQH_FTE_NE_SHARED_POS_FTE_CAP ', 100);
549 --
550 l_assignment_fte := pqh_psf_bus.assignment_fte(p_rec.assignment_id, p_validation_start_date);
551 if (l_rec.fte/l_rec.max_persons <> l_assignment_fte) then
552 pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_rec.organization_id);
553 pqh_utility.raise_error;
554 end if;
555 --
556 hr_utility.set_location('after PQH_FTE_NE_SHARED_POS_FTE_CAP ', 100);
557 --
558 end if;
559 --
560 --
561 --Check Insert allowed..
562 --
563 if l_rec.position_type = 'POOLED' then
564 if pqh_psf_bus.open_status(l_rec.position_id, l_rec.effective_start_date) = 'OPEN' then
565 null;
566 else
567 pqh_utility.set_message(8302,'PQH_POOLED_POS_NOT_OPEN',l_rec.organization_id);
568 pqh_utility.raise_error;
569 end if;
570 elsif l_rec.position_type = 'SINGLE' or l_rec.position_type = 'SHARED' then
571 hr_utility.set_location('SINGLE OR SHARED', 111);
572 if pqh_psf_bus.open_status(l_rec.position_id, l_rec.effective_start_date) = 'OPEN' then
573 hr_utility.set_location('OPEN', 112);
574 if not l_bgt_lt_abv_fte then
575 hr_utility.set_location('NOT l_bgt_lt_abv_fte ', 113);
576 if chk_reserved_fte(p_rec.assignment_id, p_rec.person_id,
577 p_rec.position_id, l_rec.position_type,
578 p_validation_start_date) then
579 pqh_psf_bus.reserved_error(p_rec.assignment_id,
580 p_rec.person_id,
581 p_rec.position_id,
582 p_validation_start_date,
583 l_rec.organization_id);
584
585 --hr_utility.set_location('POSITION RESERVED', 114);
586 --pqh_utility.set_message(8302,'PQH_POS_RESERVED',l_rec.organization_id);
587 --pqh_utility.raise_error;
588 else
589 l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
590 p_rec.position_id, l_rec.position_type,
591 p_validation_start_date, hr_general.end_of_time);
592 if l_future_res_date is not null then
593 hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
594 hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
595 pqh_utility.set_message_level_cd('W');
596 pqh_utility.raise_error;
597 end if;
598 end if;
599 end if;
600 end if;
601 elsif (l_rec.position_type = 'NONE') then
602 null;
603 end if;
604 end if;
605 else
606 close c_position;
607 end if;
608 hr_utility.set_location('Exiting PQH_PSF_BUS', 130);
609 --
610 END;
611 --
612 -- ---------------------------------------------------------------------------
613 -- |-----------------< per_asg_bus_delete_validate >--------------------|
614 -- ---------------------------------------------------------------------------
615 --
616 PROCEDURE per_asg_bus_delete_validate(p_rec per_asg_shd.g_rec_type
617 ,p_effective_date in date
618 ,p_validation_start_date in date
619 ,p_validation_end_date in date
620 ,p_datetrack_mode in varchar2 ) IS
621 l_position_id number;
622 l_organization_id number;
623 l_fte number;
624 l_bgt_lt_abv_fte boolean;
625 l_bdgt number;
626 l_sum number;
627 l_sum1 number;
628 l_sum2 number;
629 l_overlap_period number;
630 l_overlap_dates_present boolean;
631 l_realloc number;
632 l_bgt_realloc number;
633 --
634 cursor c_changed_dates(p_position_id number,
635 p_validation_start_date date ,p_validation_end_date date) is
636 select effective_start_date, business_group_id
637 from per_all_assignments_f
638 where position_id = p_position_id
639 and effective_start_date between p_validation_start_date and p_validation_end_date
640 union
641 select effective_end_date, business_group_id
642 from per_all_assignments_f
643 where position_id = p_position_id
644 and effective_end_date between p_validation_start_date and p_validation_end_date
645 union
646 select abv.effective_start_date, abv.business_group_id
647 from per_assignment_budget_values_f abv, per_all_assignments_f asg
648 where abv.assignment_id = asg.assignment_id
649 and asg.position_id = p_position_id
650 and abv.effective_start_date between p_validation_start_date and p_validation_end_date
651 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
652 union
653 select abv.effective_end_date, abv.business_group_id
654 from per_assignment_budget_values_f abv, per_all_assignments_f asg
655 where abv.assignment_id = asg.assignment_id
656 and asg.position_id = p_position_id
657 and abv.effective_end_date between p_validation_start_date and p_validation_end_date
658 and asg.effective_end_date between p_validation_start_date and p_validation_end_date
659 union
660 select effective_start_date, business_group_id
661 from hr_all_positions_f
662 where position_id = p_position_id
663 and effective_start_date between p_validation_start_date and p_validation_end_date;
664 --
665 cursor c_position_id(p_assignment_id number, p_effective_date date) is
666 select position_id
667 from per_all_assignments_f
668 where assignment_id = p_assignment_id
669 and p_effective_date between effective_start_date and effective_end_date;
670 --
671 cursor c_position_fte(p_position_id number, p_date date) is
672 select overlap_period, fte, organization_id
673 from hr_all_positions_f
674 where position_id = p_position_id
675 and p_date between effective_start_date and effective_end_date;
676 --
677 begin
678 hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);
679 l_position_id := p_rec.position_id;
680 hr_utility.set_location('l_position_id : ' || l_position_id,51);
681 hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
682 hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
683 hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
684 hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
685 hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
686 if l_position_id is null then
687 open c_position_id(p_rec.assignment_id, p_effective_date);
688 fetch c_position_id into l_position_id;
689 close c_position_id;
690 end if;
691 hr_utility.set_location('l_position_id : ' || l_position_id,51);
692 --
693 if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
694 for r1 in c_changed_dates(l_position_id, p_validation_start_date, p_validation_end_date)
695 loop
696 hr_utility.set_location('Effective Start Date : ' || r1.effective_start_date,50);
697 --
698 open c_position_fte(l_position_id, r1.effective_start_date);
699 fetch c_position_fte into l_overlap_period,l_fte, l_organization_id;
700 close c_position_fte;
701 hr_utility.set_location('l_overlap_period : ' || l_overlap_period,51);
702 --
703 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
704 p_position_id => l_position_id,
705 p_overlap_period => l_overlap_period,
706 p_assignment_start_date => r1.effective_start_date);
707 --
708 if not l_overlap_dates_present then
709 --
710 l_bdgt := budgeted_fte(l_position_id, r1.effective_start_date);
711 --
712 l_realloc := pqh_reallocation_pkg.get_reallocation(
713 p_position_id => l_position_id
714 ,p_start_date => r1.effective_start_date
715 ,p_end_date => r1.effective_start_date
716 ,p_effective_date => r1.effective_start_date
717 ,p_system_budget_unit =>'FTE'
718 ,p_business_group_id => r1.business_group_id
719 );
720 --
721 --
722 l_sum1 := pqh_psf_bus.sum_assignment_fte(l_position_id, r1.effective_start_date, p_rec.assignment_id);
723 l_sum2 := pqh_psf_bus.assignment_fte(p_rec.assignment_id, r1.effective_start_date);
724 l_sum := l_sum1 + l_sum2;
725 --
726 hr_utility.set_location('l_bdgt '||l_bdgt, 101);
727 hr_utility.set_location('l_fte '||l_fte, 101);
728 hr_utility.set_location('l_sum1'||l_sum1, 101);
729 hr_utility.set_location('l_sum2'||l_sum2, 101);
730 hr_utility.set_location('l_sum '||l_sum, 101);
731
732 --
733 if l_bdgt is not null or l_realloc is not null then
734 l_bgt_realloc := nvl(l_bdgt,0) + nvl(l_realloc,0);
735 --
736 if l_bgt_realloc < l_sum then
737 l_bgt_lt_abv_fte := true;
738 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_BGT_FTE',l_organization_id);
739 pqh_utility.raise_error;
740 end if;
741 else
742 --
743 if l_fte < l_sum then
744 l_bgt_lt_abv_fte := true;
745 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',l_organization_id);
746 pqh_utility.raise_error;
747 end if;
748 end if;
749 end if;
750 end loop;
751 end if;
752 hr_utility.set_location('Exiting pqh_asg_bus_delete_validate', 400);
753 --
754 end;
755 --
756 -- ---------------------------------------------------------------------------
757 -- |----------------------< per_abv_insert_validate >-------------------|
758 -- ---------------------------------------------------------------------------
759 --
760 PROCEDURE per_abv_insert_validate(
761 p_assignment_id number,
762 p_value number,
763 p_unit varchar2,
764 p_effective_date date) is
765 l_proc varchar2(100) := 'per_abv_insert_validate';
766 l_position_id hr_all_positions_f.position_id%type;
767 l_fte hr_all_positions_f.fte%type;
768 l_max_persons hr_all_positions_f.max_persons%type;
769 l_position_type hr_all_positions_f.position_type%type;
770 l_organization_id number;
771 l_sum_abv number:=0;
772 l_pos_budget_fte number:=0;
773 l_person_id number;
774 l_available_fte number;
775 l_overlap_dates_present boolean := false;
776 l_overlap_period number;
777 l_abv_gt_fte boolean := false;
778 l_realloc number;
779 l_bgt_realloc number;
780 l_business_group_id number;
781
782 cursor c_asg is
783 select paf.position_id, paf.person_id, paf.business_group_id
784 from per_all_assignments_f paf
785 where paf.assignment_id = p_assignment_id
786 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
787
788 cursor c_positions is
789 select position_id, fte, max_persons, position_type, organization_id, overlap_period
790 from hr_all_positions_f psf
791 where position_id =
792 (select position_id
793 from per_all_assignments_f paf
794 where paf.assignment_id = p_assignment_id
795 and p_effective_date between paf.effective_start_date and paf.effective_end_date)
796 and p_effective_date between psf.effective_start_date and psf.effective_end_date;
797
798 cursor c_sum_abv (p_position_id number) is
799 select sum(value)
800 from per_assignment_budget_values_f abv, per_all_assignments_f asg
801 where abv.assignment_id = asg.assignment_id
802 and asg.position_id = p_position_id
803 and p_effective_date between asg.effective_start_date and asg.effective_end_date
804 and p_effective_date between abv.effective_start_date and abv.effective_end_date
805 and abv.unit in ('F', 'FTE');
806
807 begin
808 hr_utility.set_location('Entering Procedure '||l_proc, 100);
809 hr_utility.set_location('p_unit '||p_unit, 101);
810 hr_utility.set_location('p_assignment_id '||nvl(p_assignment_id,-1), 102);
811 hr_utility.set_location('p_effective_date '||p_effective_date, 102);
812 open c_asg;
813 fetch c_asg into l_position_id, l_person_id, l_business_group_id;
814 close c_asg;
815 --
816 hr_utility.set_location('l_position_id '||nvl(l_position_id,-1), 103);
817 --
818 if p_unit in ('FTE') then
819 hr_utility.set_location('Unit is FTE:'||l_proc, 110);
820 open c_positions;
821 fetch c_positions into l_position_id, l_fte, l_max_persons,
822 l_position_type, l_organization_id, l_overlap_period;
823 close c_positions;
824 --
825 hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
826 l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
827 --
828 --
829 if l_position_type in ('SINGLE', 'SHARED') then
830 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
831 p_position_id => l_position_id,
832 p_overlap_period => l_overlap_period,
833 p_assignment_start_date => p_effective_date);
834 if not l_overlap_dates_present then
835 --
836 --Validate Position Budget values with sum of Assignment Budget Values
837 --
838 l_sum_abv := pqh_psf_bus.sum_assignment_fte(l_position_id, p_effective_date);
839 --
840 l_pos_budget_fte := budgeted_fte(l_position_id, p_effective_date);
841 --
842 l_realloc := pqh_reallocation_pkg.get_reallocation(
843 p_position_id => l_position_id
844 ,p_start_date => p_effective_date
845 ,p_end_date => p_effective_date
846 ,p_effective_date => p_effective_date
847 ,p_system_budget_unit =>'FTE'
848 ,p_business_group_id => l_business_group_id
849 );
850 --
851 --
852 hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
853 hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
854 hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
855 hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
856 hr_utility.set_location('FTE: p_value:'||p_value, 150);
857 --
858 if l_pos_budget_fte is not null or l_realloc is not null then
859 l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
860 --
861 if (l_bgt_realloc < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
862 l_abv_gt_fte := true;
863 ---Position Budget FTE is less than the sum of the assignment budget FTE
864 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_BGT_FTE',l_organization_id);
865 pqh_utility.raise_error;
866 end if;
867 else
868 if (l_fte < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
869 l_abv_gt_fte := true;
870 ---Position FTE is less than the sum of the assignment budget FTE
871 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',l_organization_id);
872 pqh_utility.raise_error;
873 end if;
874 end if;
875 --
876 if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
877 --
878 -- Check Reserved
879 --
880 hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
881 if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
882 hr_utility.set_location('Insert Validate:OPEN', 130);
883 l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
884 hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
885 if (nvl(p_value,0) > l_available_fte) then
886 pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
887 l_position_id,
888 p_effective_date,
889 l_organization_id);
890 end if;
891 end if;
892 end if;
893 end if;
894 end if;
895 --
896 hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
897 l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
898 --
899 if (l_position_type = 'SHARED') and (l_fte/l_max_persons <> p_value) then
900 pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
901 pqh_utility.raise_error;
902 end if;
903 end if;
904 hr_utility.set_location('Exiting Procedure '||l_proc, 200);
905 end;
906 --
907 -- ---------------------------------------------------------------------------
908 -- |----------------------< per_abv_update_validate >-------------------|
909 -- ---------------------------------------------------------------------------
910 --
911 PROCEDURE per_abv_update_validate(
912 p_abv_id number,
913 p_assignment_id number,
914 p_value number,
915 p_unit varchar2,
916 p_effective_date date,
917 p_validation_start_date date,
918 p_validation_end_date date,
919 p_datetrack_mode varchar2) is
920 l_proc varchar2(100) := 'per_abv_update_validate';
921 l_position_id hr_all_positions_f.position_id%type;
922 l_fte hr_all_positions_f.fte%type;
923 l_max_persons hr_all_positions_f.max_persons%type;
924 l_position_type hr_all_positions_f.position_type%type;
925 l_organization_id number;
926 l_sum_abv number;
927 l_pos_budget_fte number;
928 l_person_id number;
929 l_available_fte number;
930 l_assignment_fte number;
931 l_temp number;
932 l_overlap_dates_present boolean := false;
933 l_overlap_period number;
934 l_abv_gt_fte boolean:=false;
935 l_realloc number;
936 l_bgt_realloc number;
937 l_business_group_id number;
938 --
939 cursor c_asg(p_assignment_id number, p_effective_date date) is
940 select position_id, person_id, business_group_id
941 from per_all_assignments_f paf
942 where paf.assignment_id = p_assignment_id
943 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
944 --
945 cursor c_positions(p_effective_date date) is
946 select position_id, fte, max_persons, position_type, organization_id, overlap_period
947 from hr_all_positions_f psf
948 where position_id =
949 (select position_id
950 from per_all_assignments_f paf
951 where paf.assignment_id = p_assignment_id
952 and p_effective_date between paf.effective_start_date and paf.effective_end_date)
953 and p_effective_date between psf.effective_start_date and psf.effective_end_date;
954
955 cursor c_sum_abv (p_position_id number,p_assignment_id number, p_effective_date date) is
956 select sum(value)
957 from per_assignment_budget_values_f abv, per_all_assignments_f asg
958 where abv.assignment_id = asg.assignment_id
959 and asg.position_id = p_position_id
960 and p_effective_date between asg.effective_start_date and asg.effective_end_date
961 and p_effective_date between abv.effective_start_date and abv.effective_end_date
962 and abv.unit in ('FTE')
963 and asg.assignment_id <> nvl(p_assignment_id,-999);
964
965 begin
966 hr_utility.set_location('Entering Procedure '||l_proc, 100);
967 hr_utility.set_location('p_effective_date '||p_effective_date, 100);
968 hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
969 hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
970 hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
971 hr_utility.set_location('p_unit '||p_unit, 100);
972 if p_unit in ('FTE') then
973 open c_asg(p_assignment_id, p_effective_date);
974 fetch c_asg into l_position_id, l_person_id, l_business_group_id;
975 close c_asg;
976 hr_utility.set_location('Unit is FTE:'||l_proc, 110);
977 open c_positions(p_validation_start_date);
978 fetch c_positions into l_position_id, l_fte, l_max_persons,
979 l_position_type, l_organization_id, l_overlap_period;
980 hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
981 l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
982 if c_positions%found then
983 close c_positions;
984 hr_utility.set_location('c_positions found', 121);
985 --
986 if l_position_type in ('SINGLE', 'SHARED') then
987 hr_utility.set_location('pos type SINGLE OR SHARED', 122);
988 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
989 p_position_id => l_position_id,
990 p_overlap_period => l_overlap_period,
991 p_assignment_start_date => p_effective_date);
992 hr_utility.set_location('After chk_Overlap_dates', 123);
993 if not l_overlap_dates_present then
994 hr_utility.set_location('Overlap_dates not present', 124);
995 --
996 --Validate Position Budget values with sum of Assignment Budget Values
997 open c_sum_abv(l_position_id,p_assignment_id, p_validation_start_date);
998 fetch c_sum_abv into l_sum_abv;
999 close c_sum_abv;
1000 --
1001 hr_utility.set_location('Before budgeted fte', 124);
1002 l_pos_budget_fte := budgeted_fte(l_position_id, p_validation_start_date);
1003 --
1004 l_realloc := pqh_reallocation_pkg.get_reallocation(
1005 p_position_id => l_position_id
1006 ,p_start_date => p_validation_start_date
1007 ,p_end_date => p_validation_start_date
1008 ,p_effective_date => p_validation_start_date
1009 ,p_system_budget_unit =>'FTE'
1010 ,p_business_group_id => l_business_group_id
1011 );
1012 --
1013 --
1014 --
1015 hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1016 hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1017 hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1018 hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1019 hr_utility.set_location('FTE: p_value:'||p_value, 150);
1020 --
1021 if l_pos_budget_fte is not null or l_realloc is not null then
1022 l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
1023 --
1024 hr_utility.set_location('l_pos_budget_fte is null', 151);
1025 if (l_bgt_realloc < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
1026 hr_utility.set_location('PQH_SUM_ABV_FTE_GT_POS_BGT_FTE', 152);
1027 l_abv_gt_fte := true;
1028 ---Position Budget FTE is less than the sum of the assignment budget FTE
1029 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_BGT_FTE' ,l_organization_id);
1030 pqh_utility.raise_error;
1031 end if;
1032 else
1033 if (l_fte < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
1034 hr_utility.set_location('PQH_SUM_ABV_FTE_GT_POS_FTE :'||l_organization_id, 153);
1035 l_abv_gt_fte := true;
1036 ---Position FTE is less than the sum of the assignment budget FTE
1037 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',l_organization_id);
1038 pqh_utility.raise_error;
1039 end if;
1040 end if;
1041 --
1042 hr_utility.set_location('Before check reserved', 154);
1043 --
1044 -- Check Reserved
1045 --
1046 if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
1047 hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
1048 --if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
1049 hr_utility.set_location('Insert Validate:OPEN', 130);
1050 l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
1051 l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0);
1052 hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
1053 hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 133);
1054 l_temp := l_available_fte + l_assignment_fte ;
1055 if (nvl(p_value,0) > l_temp ) then
1056 pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
1057 l_position_id,
1058 p_effective_date,
1059 l_organization_id);
1060
1061 end if;
1062 --end if;
1063 end if;
1064 end if;
1065 end if;
1066 --
1067 hr_utility.set_location('Before fte capacity', 155);
1068 --
1069 if (l_position_type = 'SHARED') and (l_fte/l_max_persons <> p_value) then
1070 hr_utility.set_location('PQH_FTE_NE_SHARED_POS_FTE_CAP', 156);
1071 pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
1072 pqh_utility.raise_error;
1073 end if;
1074 --
1075 hr_utility.set_location('After fte capacity', 157);
1076 --
1077 end if;
1078 end if;
1079 hr_utility.set_location('Exiting Procedure '||l_proc, 200);
1080 end;
1081 --
1082 --
1083 -- ---------------------------------------------------------------------------
1084 -- |----------------------< funded_status >-----------------------------|
1085 -- ---------------------------------------------------------------------------
1086 -- Description:
1087 -- Retrieves the funded_status of the position.
1088 --
1089 function funded_status
1090 (p_position_id in number) return varchar2 is
1091 l_funded_status varchar2(30);
1092 begin
1093 l_funded_status := 'Y';
1094 return(l_funded_status);
1095 end;
1096 --
1097 --
1098 -- ---------------------------------------------------------------------------
1099 -- |----------------------< sum_assignment_fte >------------------------|
1100 -- ---------------------------------------------------------------------------
1101 -- Description:
1102 -- Retrieves the sum_assignment_fte of the position.
1103 -- If for an assignment budget value does not exist or fte doesn't exist, it's treated as 1
1104 --
1105 function sum_assignment_fte
1106 (p_position_id in number, p_effective_date in date) return number is
1107 l_assignment_fte number(15,2):=0;
1108 --
1109 CURSOR c_budgeted_fte(p_position_id number) is
1110 select sum(nvl(value,1))
1111 from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1112 per_assignment_status_types ast
1113 where abv.assignment_id(+) = asn.assignment_id
1114 and p_effective_date between asn.effective_start_date and asn.effective_end_date
1115 and p_effective_date between abv.effective_start_date and abv.effective_end_date
1116 and asn.position_id = p_position_id
1117 and asn.assignment_type in ('E', 'C')
1118 and abv.unit(+) = 'FTE'
1119 and asn.assignment_status_type_id = ast.assignment_status_type_id
1120 and ast.per_system_status <> 'TERM_ASSIGN';
1121 --
1122 begin
1123 /*
1124 l_assignment_fte := pqh_utility.get_pos_budget_values(
1125 p_position_id,p_effective_date,p_effective_date, 'FTE');
1126 */
1127 if p_position_id is not null then
1128 -- l_assignment_fte := 1;
1129 open c_budgeted_fte(p_position_id);
1130 fetch c_budgeted_fte into l_assignment_fte;
1131 close c_budgeted_fte;
1132 else
1133 l_assignment_fte := 0;
1134 end if;
1135 return(nvl(l_assignment_fte,0));
1136 end;
1137 --
1138 --
1139 -- ---------------------------------------------------------------------------
1140 -- |----------------------< sum_assignment_fte >------------------------|
1141 -- ---------------------------------------------------------------------------
1142 -- Description:
1143 -- Retrieves the sum_assignment_fte of the position.
1144 -- If for an assignment budget value does not exist or fte doesn't exist, it's treated as 1
1145 -- Overloaded to check if p_assignment_id is passed, it should be checked in the sql query.
1146 --
1147 function sum_assignment_fte
1148 (p_position_id in number, p_effective_date in date, p_assignment_id in number) return number is
1149 l_assignment_fte number(15,2):=0;
1150 --
1151 CURSOR c_budgeted_fte(p_position_id number) is
1152 select sum(nvl(value,1))
1153 from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1154 per_assignment_status_types ast
1155 where abv.assignment_id(+) = asn.assignment_id
1156 and asn.assignment_id <> p_assignment_id
1157 and p_effective_date between asn.effective_start_date and asn.effective_end_date
1158 and p_effective_date between abv.effective_start_date and abv.effective_end_date
1159 and asn.position_id = p_position_id
1160 and asn.assignment_type in ('E', 'C')
1161 and abv.unit(+) = 'FTE'
1162 and asn.assignment_status_type_id = ast.assignment_status_type_id
1163 and ast.per_system_status <> 'TERM_ASSIGN';
1164 --
1165 begin
1166 /*
1167 l_assignment_fte := pqh_utility.get_pos_budget_values(
1168 p_position_id,p_effective_date,p_effective_date, 'FTE');
1169 */
1170 if p_position_id is not null then
1171 -- l_assignment_fte := 1;
1172 open c_budgeted_fte(p_position_id);
1173 fetch c_budgeted_fte into l_assignment_fte;
1174 close c_budgeted_fte;
1175 else
1176 l_assignment_fte := 0;
1177 end if;
1178 return(nvl(l_assignment_fte,0));
1179 end;
1180 --
1181 -- ---------------------------------------------------------------------------
1182 -- |----------------------< person_fte >------------------------|
1183 -- ---------------------------------------------------------------------------
1184 -- Description:
1185 -- Retrieves the assignment_fte of the position.
1186 --
1187 function person_fte
1188 (p_person_id in number, p_position_id in number, p_effective_date in date, p_ex_assignment_id number) return number is
1189 l_person_id number;
1190 l_assignment_fte number(15,2):=0;
1191 CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
1192 select nvl(sum(nvl(value,1)),0)
1193 from per_all_assignments_f asn,FND_SESSIONS SS,
1194 per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1195 per_assignment_status_types ast
1196 where abv.assignment_id(+) = asn.assignment_id
1197 and SS.SESSION_ID = USERENV('sessionid')
1198 and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1199 and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1200 and SS2.SESSION_ID(+) = USERENV('sessionid')
1201 and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
1202 and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
1203 and asn.position_id = p_position_id
1204 and asn.person_id = p_person_id
1205 and asn.assignment_type in ('E', 'C')
1206 and abv.unit(+) = 'FTE'
1207 and asn.assignment_status_type_id = ast.assignment_status_type_id
1208 and ast.per_system_status <> 'TERM_ASSIGN';
1209
1210 CURSOR c_budgeted_fte_ex_asg(p_person_id number, p_position_id number, p_ex_assignment_id number) is
1211 select nvl(sum(nvl(value,1)),0)
1212 from per_all_assignments_f asn,FND_SESSIONS SS,
1213 per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1214 per_assignment_status_types ast
1215 where abv.assignment_id(+) = asn.assignment_id
1216 and SS.SESSION_ID = USERENV('sessionid')
1217 and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1218 and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1219 and SS2.SESSION_ID(+) = USERENV('sessionid')
1220 and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
1221 and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
1222 and asn.position_id = p_position_id
1223 and asn.person_id = p_person_id
1224 and asn.assignment_id <> p_ex_assignment_id
1225 and asn.assignment_type in ('E', 'C')
1226 and abv.unit(+) = 'FTE'
1227 and asn.assignment_status_type_id = ast.assignment_status_type_id
1228 and ast.per_system_status <> 'TERM_ASSIGN';
1229 begin
1230 if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
1231 if p_ex_assignment_id is null then
1232 open c_budgeted_fte(p_person_id, p_position_id);
1233 fetch c_budgeted_fte into l_assignment_fte;
1234 hr_utility.set_location('l_person_id : '||l_person_id, 630);
1235 hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1236 close c_budgeted_fte;
1237 else
1238 open c_budgeted_fte_ex_asg(p_person_id, p_position_id, p_ex_assignment_id);
1239 fetch c_budgeted_fte_ex_asg into l_assignment_fte;
1240 hr_utility.set_location('l_person_id : '||l_person_id, 630);
1241 hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1242 close c_budgeted_fte_ex_asg;
1243 end if;
1244 end if;
1245 return(l_assignment_fte);
1246 end;
1247 --
1248 --
1249 -- ---------------------------------------------------------------------------
1250 -- |--------------------< default_assignment_fte >----------------------|
1251 -- ---------------------------------------------------------------------------
1252 -- Description:
1253 -- Retrieves the sum_assignment_fte of the position.
1254 --
1255 function default_assignment_fte
1256 (p_organization_id in number) return number is
1257 l_default_asg_fte number(15,2):=0;
1258 cursor c1 is
1259 select to_number(org_information2,'99999999.99')
1260 from hr_organization_information
1261 where org_information_context like 'Budget Value Defaults'
1262 and organization_id = p_organization_id
1263 and org_information1='FTE';
1264 begin
1265 open c1;
1266 fetch c1 into l_default_asg_fte;
1267 close c1;
1268 return l_default_asg_fte;
1269 end;
1270 --
1271 -- ---------------------------------------------------------------------------
1272 -- |----------------------< assignment_fte >-----------------------------|
1273 -- ---------------------------------------------------------------------------
1274 -- Description:
1275 -- Retrieves the assignment_fte of the position.
1276 --
1277 function assignment_fte
1278 (p_assignment_id in number) return number is
1279 l_assignment_fte number(15,2);
1280
1281 CURSOR c_budgeted_fte(p_assignment_id number) is
1282 select nvl(value,1)
1283 from per_all_assignments_f asn,FND_SESSIONS SS,
1284 per_assignment_budget_values_f abv, FND_SESSIONS SS2
1285 where abv.assignment_id(+) = asn.assignment_id
1286 and SS.SESSION_ID = USERENV('sessionid')
1287 and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1288 and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1289 and SS2.SESSION_ID(+) = USERENV('sessionid')
1290 and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
1291 and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
1292 and asn.assignment_id = p_assignment_id
1293 and asn.assignment_type in ('E', 'C')
1294 and abv.unit(+) = 'FTE';
1295 begin
1296 if p_assignment_id is not null then
1297 -- l_assignment_fte := 1;
1298 open c_budgeted_fte(p_assignment_id);
1299 fetch c_budgeted_fte into l_assignment_fte;
1300 close c_budgeted_fte;
1301 -- else
1302 -- l_assignment_fte := 0;
1303 end if;
1304 return(l_assignment_fte);
1305 end;
1306 --
1307 --
1308 -- ---------------------------------------------------------------------------
1309 -- |------------------------< future approved actions >-----------------|
1310 -- ---------------------------------------------------------------------------
1311 -- Description:
1312 -- Retrieves the future approved actions of the position.
1313 --
1314 function future_approved_actions
1315 (p_position_id in number) return varchar2 is
1316 --
1317 l_position_transaction_id number;
1318 --
1319 cursor c1 is
1320 select position_transaction_id
1321 from pqh_position_transactions
1322 where position_id = nvl(p_position_id, -1)
1323 and transaction_status in ('SUBMITTED','APPROVED');
1324 --
1325 begin
1326 --
1327 if p_position_id is not null then
1328 open c1;
1329 fetch c1 into l_position_transaction_id;
1330 if c1%found then
1331 close c1;
1332 return('Y');
1333 end if;
1334 end if;
1335 --
1336 return('N');
1337 --
1338 end;
1339 --
1340 --
1341 --
1342 -- ---------------------------------------------------------------------------
1343 -- |----------------------< open_status >-------------------------------|
1344 -- ---------------------------------------------------------------------------
1345 -- Description:
1346 -- Retrieves the open_status of the position.
1347 --
1348 function open_status
1349 (p_position_id in number, p_effective_date in date) return varchar2 is
1350 l_open_status varchar2(30);
1351 l_availability_status varchar2(30);
1352 l_business_group_id number(15);
1353 l_availability_status_id number(15);
1354 l_position_type varchar2(30);
1355 l_pos_effective_date date;
1356 l_reserved_status varchar2(30);
1357 l_resrv_start_date date;
1358 l_resrv_end_date date;
1359 l_overlap_period number(15,2);
1360 l_overlap_start_date date;
1361 l_overlap_end_date date;
1362 l_resrv_person_id number(15);
1363 l_fte_reserved number(15,2);
1364 l_vacancy_status varchar2(30);
1365 l_funded_status varchar2(30);
1366
1367 cursor c_positions is
1368 select availability_status_id, business_group_id, position_type, date_effective
1369 from hr_all_positions_f
1370 where position_id = p_position_id
1371 and p_effective_date between effective_start_date and effective_end_date;
1372 begin
1373 l_open_status := 'OPEN';
1374 open c_positions;
1375 fetch c_positions into
1376 l_availability_status_id,l_business_group_id, l_position_type, l_pos_effective_date;
1377 close c_positions;
1378 l_availability_status :=
1379 hr_psf_shd.get_availability_status(l_availability_status_id,l_business_group_id);
1380 hr_utility.set_location('Open Status- l_availability_status:'||l_availability_status,
1381 310);
1382 hr_utility.set_location('Open Status- l_position_type:'||l_position_type,
1383 320);
1384 l_funded_status := funded_status(p_position_id);
1385 --
1386 hr_utility.set_location('Open Status- l_funded_status:'||l_funded_status,
1387 320);
1388
1389 if (l_position_type in ( 'SINGLE', 'SHARED' )) then
1390 l_vacancy_status:= vacancy_status(p_position_id, p_effective_date);
1391 hr_utility.set_location('Open Status- l_vacancy_status:'||l_vacancy_status,
1392 320);
1393 if (l_pos_effective_date <= p_effective_date)
1394 and ( l_availability_status = 'ACTIVE' )
1395 and ( l_funded_status = 'Y')
1396 and ( l_vacancy_status <> 'FILLED') then
1397 l_open_status := 'OPEN';
1398 else
1399 l_open_status := 'NOT_OPEN';
1400 end if;
1401 elsif (l_position_type in ( 'POOLED')) then
1402 if (l_pos_effective_date <= p_effective_date)
1403 and ( l_availability_status = 'ACTIVE' )
1404 and ( l_funded_status = 'Y') then
1405 l_open_status := 'OPEN';
1406 else
1407 l_open_status := 'NOT_OPEN';
1408 end if;
1409 end if;
1410 hr_utility.set_location('Open Status- l_open_status:'||l_open_status,
1411 330);
1412 /*
1413 reserved_status(p_position_id, l_reserved_status, l_resrv_start_date, l_resrv_end_date, l_resrv_person_id, l_fte_reserved);
1414 if (l_reserved_status in ( 'NEW_HIRE', 'MANAGEMENT_DISCRETION'))
1415 and (l_fte_reserved > 0 )
1416 and (p_effective_date between
1417 nvl(l_resrv_start_date, p_effective_date) and nvl(l_resrv_end_date, p_effective_date)) then
1418 l_open_status := 'NOT_OPEN';
1419 end if;
1420
1421 overlap_period(p_position_id, l_overlap_period, l_overlap_start_date, l_overlap_end_date);
1422 if (l_overlap_period > 0)
1423 and (p_effective_date between
1424 nvl(l_overlap_start_date, p_effective_date) and nvl(l_resrv_end_date, l_overlap_end_date)) then
1425 l_open_status := 'OPEN';
1426 end if;
1427 */
1428 return(l_open_status);
1429 end;
1430 --
1431 --
1432 -- ---------------------------------------------------------------------------
1433 -- |----------------------< overlap_period >-----------------------------|
1434 -- ---------------------------------------------------------------------------
1435 -- Description:
1436 -- Retrieves the overlap_period of the position.
1437 --
1438 procedure overlap_period
1439 (p_position_id in number, p_overlap_period out nocopy number,
1440 p_start_date out nocopy date, p_end_date out nocopy date) is
1441 l_overlap_period varchar2(30);
1442 l_overlap_unit_cd varchar2(30);
1443
1444 cursor c1 is
1445 select overlap_period, overlap_unit_cd
1446 from hr_all_positions
1447 where position_id = p_position_id;
1448
1449 cursor c2 is
1450 select fnd_date.canonical_to_date(poei_information3), fnd_date.canonical_to_date(poei_information4)
1451 from per_position_extra_info
1452 where information_type = 'PER_OVERLAP'
1453 and position_id = p_position_id;
1454
1455 begin
1456 open c2;
1457 fetch c2 into p_start_date, p_end_date;
1458 close c2;
1459
1460 open c1;
1461 fetch c1 into l_overlap_period, l_overlap_unit_cd;
1462 close c1;
1463
1464 p_overlap_period := l_overlap_period;
1465 exception when others then
1466 p_overlap_period := null;
1467 p_start_date := null;
1468 p_end_date := null;
1469 raise;
1470
1471 end;
1472 --
1473 --
1474 -- ---------------------------------------------------------------------------
1475 -- |----------------------< reserved_status >-----------------------------|
1476 -- ---------------------------------------------------------------------------
1477 -- Description:
1478 -- Retrieves the reserved_status of the position.
1479 -- NEW_HIRE, TYPE_OF_LOA, PERSON_RESERVED_FOR, RESERVED_FTE, MANAGEMENT_DESCRETION
1480 procedure reserved_status
1481 (p_position_id in number, p_reserved_status out nocopy varchar2,
1482 p_start_date out nocopy date, p_end_date out nocopy date, p_person_id out nocopy number, p_fte_reserved out nocopy number) is
1483 l_reserved_status varchar2(30);
1484 l_fte_reserved number(15,2);
1485 l_person_id number(15);
1486
1487 cursor c1 is
1488 select fnd_date.canonical_to_date(poei_information3) poei_information3,
1489 nvl(fnd_date.canonical_to_date(poei_information4),
1490 hr_general.end_of_time) poei_information4,
1491 poei_information5, poei_information6, poei_information7
1492 from per_position_extra_info
1493 where position_id = p_position_id
1494 and information_type = 'PER_RESERVED';
1495
1496 begin
1497 open c1;
1498 fetch c1 into p_start_date, p_end_date, l_person_id, l_fte_reserved, l_reserved_status;
1499 close c1;
1500
1501 p_reserved_status := l_reserved_status;
1502 p_person_id := l_person_id;
1503 p_fte_reserved := l_fte_reserved;
1504 exception when others then
1505 p_reserved_status := null;
1506 p_start_date := null;
1507 p_end_date := null;
1508 p_person_id := null;
1509 p_fte_reserved := null;
1510 raise;
1511 end reserved_status;
1512
1513 function chk_reserved(p_position_id number) return boolean is
1514 l_reserved_status varchar2(50);
1515 l_start_date date;
1516 l_end_date date;
1517 l_person_id number;
1518 l_fte_reserved number;
1519 begin
1520 reserved_status
1521 (p_position_id , l_reserved_status ,
1522 l_start_date , l_end_date , l_person_id , l_fte_reserved );
1523 if l_reserved_status is not null then
1524 return true;
1525 end if;
1526 return false;
1527 end;
1528 --
1529 --
1530 -- ---------------------------------------------------------------------------
1531 -- |----------------------< review_status >-----------------------------|
1532 -- ---------------------------------------------------------------------------
1533 -- Description:
1534 -- Retrieves the review_status of the position.
1535 --
1536 function review_status
1537 (p_position_id in number) return varchar2 is
1538 l_review_status varchar2(30);
1539 begin
1540 l_review_status := 'Y';
1541 return(l_review_status);
1542 end;
1543 --
1544 --
1545 -- ---------------------------------------------------------------------------
1546 -- |----------------------< vacancy_status >-----------------------------|
1547 -- ---------------------------------------------------------------------------
1548 -- Description:
1549 -- Retrieves the vacancy_status of the position.
1550 -- FILLED, PARTIALLY FILLED, VACANT
1551 function vacancy_status
1552 (p_position_id in number, p_effective_date in date) return varchar2 is
1553 l_vacancy_status varchar2(30);
1554 l_position_type varchar2(30);
1555 l_fte number(15,2);
1556 l_budgeted_fte number;
1557 l_assignment_id number(15);
1558 l_sum_asg_fte number;
1559 --
1560 CURSOR C1 IS
1561 select position_type, FTE from hr_all_positions_f
1562 where position_id = p_position_id
1563 and p_effective_date
1564 between effective_start_date and effective_end_date;
1565
1566 CURSOR C_ASSIGNMENTS IS
1567 select assignment_id
1568 from per_all_assignments_f
1569 where position_id = p_position_id
1570 and p_effective_date
1571 between effective_start_date and effective_end_date
1572 and assignment_type in ('E', 'C');
1573 begin
1574 l_vacancy_status := 'VACANT';
1575 open c1;
1576 fetch c1 into l_position_type, l_fte;
1577 close c1;
1578
1579 if (l_position_type = 'SINGLE') then
1580 open C_ASSIGNMENTS;
1581 fetch C_ASSIGNMENTS into l_assignment_id;
1582 if (C_ASSIGNMENTS%FOUND) then
1583 l_vacancy_status := 'FILLED';
1584 else
1585 l_vacancy_status := 'VACANT';
1586 end if;
1587 close C_ASSIGNMENTS;
1588 return(l_vacancy_status);
1589 elsif (l_position_type = 'SHARED') then
1590 hr_utility.set_location('Shared',101);
1591 open C_ASSIGNMENTS;
1592 fetch C_ASSIGNMENTS into l_assignment_id;
1593 if (C_ASSIGNMENTS%NOTFOUND) then
1594 close C_ASSIGNMENTS;
1595 l_vacancy_status := 'VACANT';
1596 return(l_vacancy_status);
1597 else
1598 close C_ASSIGNMENTS;
1599 l_vacancy_status := 'FILLED';
1600 l_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
1601 l_sum_asg_fte := sum_assignment_fte(p_position_id, p_effective_date);
1602 hr_utility.set_location('l_fte' || l_fte,102);
1603 hr_utility.set_location('sum_asg_fte :' || l_sum_asg_fte,102);
1604 if l_fte is not null then
1605 if nvl(l_fte,0) > nvl(l_sum_asg_fte,0) then
1606 /*if nvl(l_fte,0) >
1607 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_budget_values(
1608 p_position_id,p_effective_date,p_effective_date, 'FTE') then
1609 */
1610 l_vacancy_status := 'PARTIALLY FILLED';
1611 else
1612 l_vacancy_status := 'FILLED';
1613 end if;
1614 else
1615 l_vacancy_status := 'VACANT';
1616 end if;
1617 end if;
1618 elsif (l_position_type = 'POOLED') then
1619 l_vacancy_status := 'VACANT';
1620 else
1621 l_vacancy_status := 'VACANT';
1622 end if;
1623 -- l_vacancy_status:= 'FILLED';
1624 return(l_vacancy_status);
1625 end;
1626 --
1627 function permit_extended_pay(p_position_id varchar2) return boolean is
1628 l_position_family varchar2(100);
1629 l_chk boolean := false;
1630 cursor c1 is
1631 select poei_information3
1632 from per_position_extra_info
1633 where position_id = p_position_id
1634 and information_type = 'PER_FAMILY'
1635 and poei_information3 in ('ACADEMIC','FACULTY');
1636 begin
1637 if p_position_id is not null then
1638 open c1;
1639 fetch c1 into l_position_family;
1640 if c1%found then
1641 close c1;
1642 return true;
1643 else
1644 close c1;
1645 return false;
1646 end if;
1647 else
1648 return(false);
1649 end if;
1650 end;
1651 --
1652 function permit_extended_pay_poi(p_rec in pe_poi_shd.g_rec_type) return boolean is
1653 l_position_family varchar2(100);
1654 l_chk boolean := false;
1655 l_position_extra_info_id number := nvl(p_rec.position_extra_info_id,-1);
1656 cursor c1 is
1657 select poei_information3
1658 from per_position_extra_info
1659 where position_id = p_rec.position_id
1660 and position_extra_info_id <> l_position_extra_info_id
1661 and information_type = 'PER_FAMILY'
1662 and poei_information3 in ('ACADEMIC','FACULTY');
1663 begin
1664 if p_rec.position_id is not null then
1665 open c1;
1666 fetch c1 into l_position_family;
1667 if c1%found then
1668 close c1;
1669 return true;
1670 else
1671 close c1;
1672 return false;
1673 end if;
1674 else
1675 return(false);
1676 end if;
1677 end;
1678 --
1679 -- ---------------------------------------------------------------------------
1680 -- |----------------------< chk_overlap_dates >--------------------------|
1681 -- ---------------------------------------------------------------------------
1682 -- Description:
1683 -- Checks the overlap_dates of the position.
1684 --
1685 function chk_overlap_dates
1686 (p_position_id in number, p_overlap_period number, p_assignment_start_date date) return boolean is
1687 l_dummy varchar2(30);
1688 cursor c2 is
1689 select 'x'
1690 from per_position_extra_info
1691 where p_assignment_start_date
1692 between fnd_date.canonical_to_date(poei_information3)
1693 and fnd_date.canonical_to_date(poei_information4)
1694 and position_id = p_position_id
1695 and information_type = 'PER_OVERLAP';
1696 begin
1697 if p_overlap_period is not null
1698 and p_position_id is not null and p_assignment_start_date is not null then
1699 open c2;
1700 fetch c2 into l_dummy;
1701 if c2%found then
1702 close c2;
1703 return(true);
1704 else
1705 close c2;
1706 return(false);
1707 end if;
1708 close c2;
1709 end if;
1710 return(false);
1711 end;
1712 --
1713 function chk_seasonal_dates( p_position_id number, p_seasonal_flag varchar2, p_assignment_start_date date)
1714 return boolean is
1715 l_f_season_start_date varchar2(20);
1716 l_f_season_end_date varchar2(20);
1717 l_season_start_date varchar2(20);
1718 l_season_end_date varchar2(20);
1719
1720 cursor c1 is
1721 select poei_information3,
1722 poei_information4
1723 from per_position_extra_info
1724 where position_id = p_position_id
1725 and information_type = 'PER_SEASONAL';
1726
1727 begin
1728 if nvl(p_seasonal_flag,'N')='Y' then
1729 if p_position_id is not null then
1730 open c1;
1731 loop
1732 fetch c1 into l_f_season_start_date, l_f_season_end_date;
1733 exit when (c1%notfound );
1734 --
1735 l_season_start_date := to_char(p_assignment_start_date,'RRRR')||substr(l_f_season_start_date,5,6);
1736 l_season_end_date := to_char(p_assignment_start_date,'RRRR')||substr(l_f_season_end_date,5,6);
1737 if to_date(l_season_end_date,'RRRR/MM/DD') < to_date(l_season_start_date,'RRRR/MM/DD') then
1738 if to_date(l_season_start_date,'RRRR/MM/DD') > p_assignment_start_date then
1739 l_season_start_date := substr(l_season_start_date,1,4)-1||substr(l_f_season_start_date,5,6);
1740 else
1741 l_season_end_date := substr(l_season_end_date,1,4)+1||substr(l_f_season_end_date,5,6);
1742 end if;
1743 end if;
1744 --
1745 if (p_assignment_start_date between to_date(l_season_start_date,'RRRR/MM/DD')
1746 and to_date(l_season_end_date,'RRRR/MM/DD')) then
1747 close c1;
1748 return(true);
1749 end if;
1750 end loop;
1751 close c1;
1752 end if;
1753 return(false);
1754 end if;
1755 return(true);
1756 end;
1757 --
1758 function chk_seasonal(p_position_id number) return boolean is
1759 l_dummy varchar2(1);
1760 cursor c_seasonal is
1761 select 'X'
1762 from hr_all_positions
1763 where position_id = p_position_id
1764 and seasonal_flag = 'Y';
1765 begin
1766 open c_seasonal;
1767 fetch c_seasonal into l_dummy;
1768 close c_seasonal;
1769 if l_dummy is not null then
1770 return(true);
1771 end if;
1772 return(false);
1773 end;
1774 --
1775 function chk_seasonal_poi(p_position_id number) return boolean is
1776 l_dummy varchar2(1);
1777 l_position_id number := nvl(p_position_id,-1);
1778 cursor c_seasonal is
1779 select 'X'
1780 from per_position_extra_info
1781 where position_id = l_position_id
1782 and information_type = 'PER_SEASONAL';
1783 begin
1784 open c_seasonal;
1785 fetch c_seasonal into l_dummy;
1786 if c_seasonal%notfound then
1787 close c_seasonal;
1788 return(true);
1789 end if;
1790 close c_seasonal;
1791 return(false);
1792 end;
1793 --
1794 function chk_overlap_poi(p_position_id number) return boolean is
1795 l_dummy varchar2(1);
1796 l_position_id number := nvl(p_position_id,-1);
1797 cursor c_overlap is
1798 select 'X'
1799 from per_position_extra_info
1800 where position_id = l_position_id
1801 and information_type = 'PER_OVERLAP';
1802 begin
1803 open c_overlap;
1804 fetch c_overlap into l_dummy;
1805 if c_overlap%notfound then
1806 close c_overlap;
1807 return(true);
1808 end if;
1809 close c_overlap;
1810 return(false);
1811 end;
1812 --
1813 function pos_assignments_exists(p_position_id number) return boolean is
1814 l_dummy varchar2(1);
1815 cursor c1 is
1816 select 'x'
1817 from per_all_assignments
1818 where position_id = p_position_id
1819 and assignment_type in ('E', 'C');
1820 begin
1821 open c1;
1822 fetch c1 into l_dummy;
1823 close c1;
1824 if l_dummy is not null then
1825 return(true);
1826 else
1827 return(false);
1828 end if;
1829 end;
1830 --
1831 function chk_overlap(p_position_id number) return boolean is
1832 l_dummy varchar2(1);
1833 cursor c1 is
1834 select 'X'
1835 from hr_all_positions
1836 where position_id = p_position_id
1837 and overlap_period is not null;
1838 begin
1839 open c1;
1840 fetch c1 into l_dummy;
1841 close c1;
1842 if l_dummy is not null then
1843 return(true);
1844 else
1845 return(false);
1846 end if;
1847 end;
1848 --
1849 function chk_amendment_info(
1850 amendment_date date,
1851 amendment_recommendation varchar2,
1852 amendment_ref_number varchar2) return boolean is
1853 begin
1854 if (amendment_date is null
1855 and amendment_recommendation is null
1856 and amendment_ref_number is null ) or
1857 (amendment_date is not null
1858 and amendment_recommendation is not null
1859 and amendment_ref_number is not null ) then
1860 return(true);
1861 else
1862 return(false);
1863 end if;
1864 end;
1865 --
1866 --
1867 --
1868 function no_assignments(p_position_id number) return number is
1869 l_count number(15);
1870 cursor c1 is
1871 select count(1)
1872 from per_all_assignments
1873 where position_id = p_position_id
1874 and assignment_type in ('E', 'C');
1875 begin
1876 open c1;
1877 fetch c1 into l_count;
1878 close c1;
1879 return l_count;
1880 end;
1881 --
1882 --
1883 --
1884 function no_assignments(p_position_id number, p_effective_date date) return number is
1885 l_count number(15);
1886 --
1887 cursor c1 is
1888 select count(1)
1889 from per_all_assignments_f
1890 where position_id = p_position_id
1891 and assignment_type in ('E', 'C')
1892 and p_effective_date between effective_start_date and effective_end_date;
1893 --
1894 begin
1895 open c1;
1896 fetch c1 into l_count;
1897 close c1;
1898 return l_count;
1899 end;
1900 --
1901 function max_persons(p_position_id number) return number is
1902 l_max_persons number(15,2);
1903 cursor c1 is
1904 select max_persons
1905 from hr_all_positions
1906 where position_id = p_position_id;
1907 begin
1908 open c1;
1909 fetch c1 into l_max_persons;
1910 close c1;
1911 return (l_max_persons);
1912 end;
1913 --
1914 function proposed_date_for_layoff(p_position_id number) return date is
1915 l_proposed_date_for_layoff date;
1916 cursor c1 is
1917 select proposed_date_for_layoff
1918 from hr_all_positions_f
1919 where position_id = p_position_id;
1920 begin
1921 open c1;
1922 fetch c1 into l_proposed_date_for_layoff;
1923 close c1;
1924 return(l_proposed_date_for_layoff);
1925 end;
1926 --
1927 function fte_capacity(p_position_id number) return number is
1928 l_fte_capacity number(15,2);
1929 cursor c1 is
1930 select fte/max_persons
1931 from hr_all_positions
1932 where position_id = p_position_id;
1933 begin
1934 open c1;
1935 fetch c1 into l_fte_capacity;
1936 close c1;
1937 return(l_fte_capacity);
1938 end;
1939 --
1940 function position_type(p_position_id number) return varchar2 is
1941 l_position_type varchar2(32);
1942 cursor c1 is
1943 select position_type
1944 from hr_all_positions
1945 where position_id = p_position_id;
1946 begin
1947 open c1;
1948 fetch c1 into l_position_type;
1949 close c1;
1950 return(l_position_type);
1951 end;
1952 --
1953 function grade(p_position_id number) return number is
1954 l_grade_id number(15);
1955 cursor c1 is
1956 select entry_grade_id
1957 from hr_all_positions
1958 where position_id = p_position_id;
1959 begin
1960 open c1;
1961 fetch c1 into l_grade_id;
1962 close c1;
1963 return(l_grade_id);
1964 end ;
1965 --
1966 function work_period_type_cd(p_position_id number) return varchar2 is
1967 l_work_period_type_cd varchar2(50);
1968 cursor c1 is
1969 select work_period_type_cd
1970 from hr_all_positions
1971 where position_id = p_position_id;
1972 begin
1973 open c1;
1974 fetch c1 into l_work_period_type_cd;
1975 close c1;
1976 return(l_work_period_type_cd);
1977 end ;
1978 --
1979 function chk_work_pay_term_dates(p_work_period_type_cd hr_all_positions_f.work_period_type_cd%type
1980 ,p_work_term_end_day_cd hr_all_positions_f.work_term_end_day_cd%type
1981 ,p_work_term_end_month_cd hr_all_positions_f.work_term_end_month_cd%type
1982 ,p_pay_term_end_day_cd hr_all_positions_f.pay_term_end_day_cd%type
1983 ,p_pay_term_end_month_cd hr_all_positions_f.pay_term_end_month_cd%type
1984 ,p_term_start_day_cd hr_all_positions_f.term_start_day_cd%type
1985 ,p_term_start_month_cd hr_all_positions_f.term_start_month_cd%type
1986 ) return boolean is
1987 begin
1988 if (p_work_period_type_cd = 'Y') then
1989 return(true);
1990 elsif (p_work_term_end_day_cd is null
1991 and p_work_term_end_month_cd is null
1992 and p_pay_term_end_day_cd is null
1993 and p_pay_term_end_month_cd is null
1994 and p_term_start_day_cd is null
1995 and p_term_start_month_cd is null) then
1996 return(true);
1997 else
1998 return(false);
1999 end if;
2000 end ;
2001 --
2002 function chk_position_job_grade(p_position_grade_id number, p_job_id number) return boolean is
2003 l_dummy varchar2(15);
2004 l_chk_position_job_grade boolean := false;
2005 cursor c1 is
2006 select 'x'
2007 from per_valid_grades
2008 where job_id = p_job_id
2009 and grade_id = p_position_grade_id;
2010 begin
2011 if (p_position_grade_id is not null) then
2012 open c1;
2013 hr_utility.set_location('Entering:'||'chk_position_job_grade', 10);
2014 fetch c1 into l_dummy;
2015 if c1%notfound then
2016 close c1;
2017 return(false);
2018 else
2019 close c1;
2020 return(true);
2021 end if;
2022 end if;
2023 return(true);
2024 end ;
2025 --
2026 function position_min_asg_dt(p_position_id number) return date is
2027 l_min_asg_date date;
2028 cursor c_min_asg_dt(p_position_id number) is
2029 select min(effective_start_date)
2030 from per_all_assignments_f
2031 where position_id = p_position_id
2032 and assignment_type in ('E', 'C');
2033 begin
2034 open c_min_asg_dt(p_position_id );
2035 fetch c_min_asg_dt into l_min_asg_date;
2036 close c_min_asg_dt;
2037 return l_min_asg_date;
2038 end;
2039 --
2040 function position_max_asg_dt(p_position_id number) return date is
2041 l_max_asg_date date;
2042 cursor c_max_asg_dt(p_position_id number) is
2043 select max(effective_start_date)
2044 from per_all_assignments_f
2045 where position_id = p_position_id
2046 and assignment_type in ('E', 'C');
2047 begin
2048 open c_max_asg_dt(p_position_id );
2049 fetch c_max_asg_dt into l_max_asg_date;
2050 close c_max_asg_dt;
2051 return l_max_asg_date;
2052 end;
2053 --
2054 function chk_earliest_hire_date(p_position_id number, p_earliest_hire_date date)
2055 return boolean is
2056 begin
2057 if (position_min_asg_dt(p_position_id) < p_earliest_hire_date) then
2058 return false;
2059 else
2060 return true;
2061 end if;
2062 return true;
2063 end;
2064 --
2065 function chk_prop_date_for_layoff(p_position_id number, p_proposed_date_for_layoff date)
2066 return boolean is
2067 begin
2068 if (position_max_asg_dt(p_position_id) >= p_proposed_date_for_layoff) then
2069 return false;
2070 else
2071 return true;
2072 end if;
2073 return true;
2074 end;
2075 --
2076 function GET_SYSTEM_SHARED_TYPE(p_availability_status_id number)
2077 return varchar2 is
2078 cursor c1 is select system_type_cd
2079 from per_shared_types
2080 where shared_type_id = p_availability_status_id;
2081 l_system_type varchar2(30);
2082 begin
2083 open c1;
2084 fetch c1 into l_system_type;
2085 if c1%notfound then
2086 close c1;
2087 return null ;
2088 else
2089 close c1;
2090 end if;
2091 return l_system_type;
2092 end;
2093 --
2094 function budgeted_fte (p_position_id in number,
2095 p_effective_date in date) return number is
2096
2097 l_calendar varchar2(200);
2098 l_budget_id number;
2099 l_budget_unit1_id number;
2100 l_budget_unit2_id number;
2101 l_budget_unit3_id number;
2102 l_unit1_name varchar2(200);
2103 l_unit2_name varchar2(200);
2104 l_unit3_name varchar2(200);
2105 l_budgeted_fte number;
2106 l_business_group_id number;
2107 --
2108 cursor c_bus_grp_id(p_position_id number) is
2109 select business_group_id
2110 from hr_all_positions_f
2111 where position_id = p_position_id;
2112 --
2113 cursor c1(p_unit_id number) is select system_type_cd from
2114 per_shared_types where shared_type_id = p_unit_id;
2115 cursor c2(p_budget_id number) is select bdt.budget_detail_id
2116 from pqh_budget_details bdt,pqh_budget_versions bvr
2117 where bvr.budget_id = p_budget_id
2118 and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
2119 and bdt.budget_version_id = bvr.budget_version_id
2120 and bdt.position_id = p_position_id;
2121 cursor c3(p_budget_detail_id number) is
2122 select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
2123 from pqh_budget_periods bpr, per_time_periods tp_s,
2124 per_time_periods tp_e
2125 where bpr.budget_detail_id = p_budget_detail_id
2126 and tp_s.time_period_id = bpr.start_time_period_id
2127 and tp_e.time_period_id = bpr.end_time_period_id
2128 and tp_s.period_set_name = l_calendar
2129 and tp_e.period_set_name = l_calendar
2130 and p_effective_date between tp_s.start_date and tp_e.end_date;
2131 begin
2132 begin
2133 open c_bus_grp_id(p_position_id);
2134 fetch c_bus_grp_id into l_business_group_id;
2135 close c_bus_grp_id;
2136 --
2137 hr_utility.set_location('l_business_group_id:' || l_business_group_id, 550
2138 );
2139
2140 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
2141 into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
2142 from pqh_budgets
2143 where position_control_flag = 'Y'
2144 and budgeted_entity_cd = 'POSITION'
2145 and business_group_id = l_business_group_id
2146 and p_effective_date between budget_start_date and budget_end_date
2147 and (
2148 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'FTE'
2149 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'FTE'
2150 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'FTE'
2151 );
2152 --
2153 hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2154 hr_utility.set_location('l_calendar:' || l_calendar, 600);
2155 hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2156 hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2157 hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2158 --
2159 open c1(l_budget_unit1_id);
2160 fetch c1 into l_unit1_name;
2161 close c1;
2162 open c1(l_budget_unit2_id);
2163 fetch c1 into l_unit2_name;
2164 close c1;
2165 open c1(l_budget_unit3_id);
2166 fetch c1 into l_unit3_name;
2167 close c1;
2168 hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2169 hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2170 hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2171 exception
2172 when others then
2173 hr_utility.set_location('Error: ' || SQLERRM, 602);
2174 return l_budgeted_fte;
2175 end;
2176 hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2177 for i in c2(l_budget_id) loop
2178 -- row corresponding to the position is picked up
2179 hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2180 --
2181 for j in c3(i.budget_detail_id) loop
2182 hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2183 if l_unit1_name ='FTE' then
2184 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit1_value,0);
2185 elsif l_unit2_name ='FTE' then
2186 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit2_value,0);
2187 elsif l_unit3_name ='FTE' then
2188 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit3_value,0);
2189 end if;
2190 end loop;
2191 end loop;
2192 hr_utility.set_location('l_budgeted_fte:' || l_budgeted_fte, 605);
2193 return l_budgeted_fte;
2194 end;
2195 --
2196 -- Function to calculate position budgeted FTE/Headcount
2197 --
2198 function get_position_budgeted_fte( p_position_id in number default null
2199 ,p_budget_entity in varchar2
2200 ,p_start_date in date default sysdate
2201 ,p_end_date in date default sysdate
2202 ,p_unit_of_measure in varchar2
2203 ,p_business_group_id in number
2204 ,p_budgeted_fte_date out nocopy date
2205 ) return number is
2206 --
2207 l_budgeted_fte number;
2208 --
2209 cursor c_date is
2210 select stp.start_date
2211 from pqh_budget_periods bper,
2212 pqh_budget_details bdet,
2213 per_time_periods stp,
2214 per_time_periods etp
2215 where bper.budget_detail_id = bdet.budget_detail_id
2216 and p_position_id = bdet.position_id
2217 and bper.start_time_period_id = stp.time_period_id
2218 and bper.end_time_period_id = etp.time_period_id
2219 and etp.end_date >= p_start_date
2220 and stp.start_date <= p_end_date
2221 union
2222 select effective_start_date start_date
2223 from per_all_assignments_f
2224 where p_position_id = position_id
2225 and assignment_type in ('E', 'C')
2226 and effective_start_date between p_start_date and p_end_date
2227 union
2228 select abv.effective_start_date start_date
2229 from per_assignment_budget_values_f abv, per_all_assignments_f asg
2230 where abv.assignment_id = asg.assignment_id
2231 and p_position_id = asg.position_id
2232 and asg.assignment_type in ('E', 'C')
2233 and abv.unit = 'FTE'
2234 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
2235 and asg.effective_start_date between p_start_date and p_end_date;
2236
2237 Begin
2238 for l_date in c_date
2239 loop
2240 hr_utility.set_location('get_position_budgeted_fte' , 500);
2241 hr_utility.set_location('p_start_date' || p_start_date, 501);
2242 hr_utility.set_location('p_end_date' || p_end_date, 502);
2243 hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2244
2245 l_budgeted_fte := budgeted_fte(p_position_id => p_position_id
2246 ,p_budget_entity => p_budget_entity
2247 ,p_effective_date => l_date.start_date
2248 ,p_unit_of_measure => p_unit_of_measure
2249 ,p_business_group_id => p_business_group_id);
2250 if l_budgeted_fte is not null then
2251 p_budgeted_fte_date := l_date.start_date;
2252 return (l_budgeted_fte);
2253 end if;
2254 end loop;
2255 hr_utility.set_location('get_position_budgeted_fte '
2256 ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2257 hr_utility.set_location('get_position_budgeted_fte '
2258 ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2259 return (l_budgeted_fte);
2260 exception when others then
2261 p_budgeted_fte_date := null;
2262 raise;
2263 End;
2264 --
2265 --
2266 -- Function to calculate Job budgeted FTE/Headcount
2267 --
2268 function get_job_budgeted_fte(
2269 p_job_id in number default null
2270 ,p_budget_entity in varchar2
2271 ,p_start_date in date default sysdate
2272 ,p_end_date in date default sysdate
2273 ,p_unit_of_measure in varchar2
2274 ,p_business_group_id in number
2275 ,p_budgeted_fte_date out nocopy date
2276 ) return number is
2277
2278 cursor c_date is
2279 select stp.start_date
2280 from pqh_budget_periods bper,
2281 pqh_budget_details bdet,
2282 per_time_periods stp,
2283 per_time_periods etp
2284 where bper.budget_detail_id = bdet.budget_detail_id
2285 and p_job_id = bdet.job_id
2286 and bper.start_time_period_id = stp.time_period_id
2287 and bper.end_time_period_id = etp.time_period_id
2288 and etp.end_date >= p_start_date
2289 and stp.start_date <= p_end_date
2290 union
2291 select effective_start_date start_date
2292 from per_all_assignments_f
2293 where p_job_id = job_id
2294 and assignment_type in ('E', 'C')
2295 and effective_start_date between p_start_date and p_end_date
2296 union
2297 select abv.effective_start_date start_date
2298 from per_assignment_budget_values_f abv, per_all_assignments_f asg
2299 where abv.assignment_id = asg.assignment_id
2300 and p_job_id = asg.job_id
2301 and asg.assignment_type in ('E', 'C')
2302 and abv.unit = 'FTE'
2303 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
2304 and asg.effective_start_date between p_start_date and p_end_date;
2305
2306 l_budgeted_fte number;
2307 Begin
2308 for l_date in c_date
2309 loop
2310 hr_utility.set_location('get_job_budgeted_fte ', 500);
2311 hr_utility.set_location('p_start_date' || p_start_date, 501);
2312 hr_utility.set_location('p_end_date' || p_end_date, 502);
2313 hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2314
2315 l_budgeted_fte := budgeted_fte(
2316 p_job_id => p_job_id
2317 ,p_budget_entity => p_budget_entity
2318 ,p_effective_date => l_date.start_date
2319 ,p_unit_of_measure => p_unit_of_measure
2320 ,p_business_group_id => p_business_group_id);
2321 if l_budgeted_fte is not null then
2322 p_budgeted_fte_date := l_date.start_date;
2323 return (l_budgeted_fte);
2324 end if;
2325 end loop;
2326 hr_utility.set_location('get_job_budgeted_fte '||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2327 hr_utility.set_location('get_job_budgeted_fte '||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2328 return (l_budgeted_fte);
2329 exception when others then
2330 p_budgeted_fte_date := null;
2331 raise;
2332 End;
2333 --
2334 --
2335 -- Function to calculate Organization budgeted FTE/Headcount
2336 --
2337 function get_org_budgeted_fte(
2338 p_organization_id in number default null
2339 ,p_budget_entity in varchar2
2340 ,p_start_date in date default sysdate
2341 ,p_end_date in date default sysdate
2342 ,p_unit_of_measure in varchar2
2343 ,p_business_group_id in number
2344 ,p_budgeted_fte_date out nocopy date
2345 ) return number is
2346
2347 cursor c_date is
2348
2349 select stp.start_date
2350 from pqh_budget_periods bper,
2351 pqh_budget_details bdet,
2352 per_time_periods stp,
2353 per_time_periods etp
2354 where bper.budget_detail_id = bdet.budget_detail_id
2355 and p_organization_id = bdet.organization_id
2356 and bper.start_time_period_id = stp.time_period_id
2357 and bper.end_time_period_id = etp.time_period_id
2358 and etp.end_date >= p_start_date
2359 and stp.start_date <= p_end_date
2360 union
2361 select effective_start_date start_date
2362 from per_all_assignments_f
2363 where p_organization_id = organization_id
2364 and assignment_type in ('E', 'C')
2365 and effective_start_date between p_start_date and p_end_date
2366 union
2367 select abv.effective_start_date start_date
2368 from per_assignment_budget_values_f abv, per_all_assignments_f asg
2369 where abv.assignment_id = asg.assignment_id
2370 and p_organization_id = asg.organization_id
2371 and asg.assignment_type in ('E', 'C')
2372 and abv.unit = 'FTE'
2373 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
2374 and asg.effective_start_date between p_start_date and p_end_date;
2375
2376 l_budgeted_fte number;
2377 Begin
2378 for l_date in c_date
2379 loop
2380 hr_utility.set_location('get_org_budgeted_fte ', 500);
2381 hr_utility.set_location('p_start_date' || p_start_date, 501);
2382 hr_utility.set_location('p_end_date' || p_end_date, 502);
2383 hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2384
2385 l_budgeted_fte := budgeted_fte(
2386 p_organization_id => p_organization_id
2387 ,p_budget_entity => p_budget_entity
2388 ,p_effective_date => l_date.start_date
2389 ,p_unit_of_measure => p_unit_of_measure
2390 ,p_business_group_id => p_business_group_id);
2391 if l_budgeted_fte is not null then
2392 p_budgeted_fte_date := l_date.start_date;
2393 return (l_budgeted_fte);
2394 end if;
2395 end loop;
2396 hr_utility.set_location('get_org_budgeted_fte '
2397 ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2398 hr_utility.set_location('get_org_budgeted_fte '
2399 ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2400 return (l_budgeted_fte);
2401 exception when others then
2402 p_budgeted_fte_date := null;
2403 raise;
2404 End;
2405 --
2406 --
2407 -- Function to calculate Grade budgeted FTE/Headcount
2408 --
2409 function get_grade_budgeted_fte(
2410 p_grade_id in number default null
2411 ,p_budget_entity in varchar2
2412 ,p_start_date in date default sysdate
2413 ,p_end_date in date default sysdate
2414 ,p_unit_of_measure in varchar2
2415 ,p_business_group_id in number
2416 ,p_budgeted_fte_date out nocopy date
2417 ) return number is
2418
2419 cursor c_date is
2420 select stp.start_date
2421 from pqh_budget_periods bper,
2422 pqh_budget_details bdet,
2423 per_time_periods stp,
2424 per_time_periods etp
2425 where bper.budget_detail_id = bdet.budget_detail_id
2426 and p_grade_id = bdet.grade_id
2427 and bper.start_time_period_id = stp.time_period_id
2428 and bper.end_time_period_id = etp.time_period_id
2429 and etp.end_date >= p_start_date
2430 and stp.start_date <= p_end_date
2431 union
2432 select effective_start_date start_date
2433 from per_all_assignments_f
2434 where p_grade_id = grade_id
2435 and assignment_type in ('E', 'C')
2436 and effective_start_date between p_start_date and p_end_date
2437 union
2438 select abv.effective_start_date start_date
2439 from per_assignment_budget_values_f abv, per_all_assignments_f asg
2440 where abv.assignment_id = asg.assignment_id
2441 and p_grade_id = asg.grade_id
2442 and asg.assignment_type in ('E', 'C')
2443 and abv.unit = 'FTE'
2444 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
2445 and asg.effective_start_date between p_start_date and p_end_date;
2446
2447 l_budgeted_fte number;
2448 Begin
2449 for l_date in c_date
2450 loop
2451 hr_utility.set_location('p_start_date' || p_start_date, 500);
2452 hr_utility.set_location('p_end_date' || p_end_date, 500);
2453 hr_utility.set_location('p_effective_date' || l_date.start_date, 500);
2454
2455 l_budgeted_fte := budgeted_fte(
2456 p_grade_id => p_grade_id
2457 ,p_budget_entity => p_budget_entity
2458 ,p_effective_date => l_date.start_date
2459 ,p_unit_of_measure => p_unit_of_measure
2460 ,p_business_group_id => p_business_group_id);
2461 if l_budgeted_fte is not null then
2462 p_budgeted_fte_date := l_date.start_date;
2463 return (l_budgeted_fte);
2464 end if;
2465 end loop;
2466 hr_utility.set_location('get_grade_budgeted_fte '
2467 ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2468 hr_utility.set_location('get_grade_budgeted_fte '
2469 ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2470 return (l_budgeted_fte);
2471 exception when others then
2472 p_budgeted_fte_date := null;
2473 raise;
2474 End;
2475 --
2476 --
2477 -- Function to calculate budgeted FTE/Headcount values for different entities
2478 --
2479 function get_budgeted_fte( p_position_id in number default null
2480 ,p_job_id in number default null
2481 ,p_grade_id in number default null
2482 ,p_organization_id in number default null
2483 ,p_budget_entity in varchar2
2484 ,p_start_date in date default sysdate
2485 ,p_end_date in date default sysdate
2486 ,p_unit_of_measure in varchar2
2487 ,p_business_group_id in number
2488 ,p_budgeted_fte_date out nocopy date
2489 ) return number is
2490 l_budgeted_fte number;
2491 Begin
2492 if (p_position_id is not null) then
2493 l_budgeted_fte := get_position_budgeted_fte(
2494 p_position_id => p_position_id
2495 ,p_budget_entity => p_budget_entity
2496 ,p_start_date => p_start_date
2497 ,p_end_date => p_end_date
2498 ,p_unit_of_measure => p_unit_of_measure
2499 ,p_business_group_id => p_business_group_id
2500 ,p_budgeted_fte_date => p_budgeted_fte_date);
2501 elsif (p_job_id is not null) then
2502 l_budgeted_fte := get_job_budgeted_fte(
2503 p_job_id => p_job_id
2504 ,p_budget_entity => p_budget_entity
2505 ,p_start_date => p_start_date
2506 ,p_end_date => p_end_date
2507 ,p_unit_of_measure => p_unit_of_measure
2508 ,p_business_group_id => p_business_group_id
2509 ,p_budgeted_fte_date => p_budgeted_fte_date);
2510 elsif (p_organization_id is not null) then
2511 l_budgeted_fte := get_org_budgeted_fte(
2512 p_organization_id => p_organization_id
2513 ,p_budget_entity => p_budget_entity
2514 ,p_start_date => p_start_date
2515 ,p_end_date => p_end_date
2516 ,p_unit_of_measure => p_unit_of_measure
2517 ,p_business_group_id => p_business_group_id
2518 ,p_budgeted_fte_date => p_budgeted_fte_date);
2519 elsif (p_grade_id is not null) then
2520 l_budgeted_fte := get_grade_budgeted_fte(
2521 p_grade_id => p_grade_id
2522 ,p_budget_entity => p_budget_entity
2523 ,p_start_date => p_start_date
2524 ,p_end_date => p_end_date
2525 ,p_unit_of_measure => p_unit_of_measure
2526 ,p_business_group_id => p_business_group_id
2527 ,p_budgeted_fte_date => p_budgeted_fte_date);
2528
2529 end if;
2530 return (l_budgeted_fte);
2531 exception when others then
2532 p_budgeted_fte_date := null;
2533 raise;
2534 End;
2535 --
2536 --
2537 -- Function to calculate budgeted FTE/Headcount values for different entities
2538 --
2539 function budgeted_fte( p_position_id in number default null
2540 ,p_job_id in number default null
2541 ,p_grade_id in number default null
2542 ,p_organization_id in number default null
2543 ,p_budget_entity in varchar2
2544 ,p_effective_date in date default sysdate
2545 ,p_unit_of_measure in varchar2
2546 ,p_business_group_id in number
2547 ) return number is
2548
2549
2550 l_calendar varchar2(200);
2551 l_budget_id number;
2552 l_budget_unit1_id number;
2553 l_budget_unit2_id number;
2554 l_budget_unit3_id number;
2555 l_unit1_name varchar2(200);
2556 l_unit2_name varchar2(200);
2557 l_unit3_name varchar2(200);
2558 l_budgeted_fte number;
2559
2560 cursor c1(p_unit_id number) is
2561 select system_type_cd
2562 from per_shared_types
2563 where shared_type_id = p_unit_id;
2564
2565 cursor c2(p_budget_id number) is
2566 select bdet.budget_detail_id
2567 from pqh_budget_details bdet,pqh_budget_versions bvr
2568 where bvr.budget_id = p_budget_id
2569 and hr_general.effective_date between bvr.date_from and nvl(bvr.date_to,hr_general.effective_date)
2570 and bdet.budget_version_id = bvr.budget_version_id
2571 and nvl(p_organization_id, nvl(bdet.organization_id, -1)) =
2572 nvl(bdet.organization_id, -1)
2573 and nvl(p_job_id, nvl(bdet.job_id, -1)) =
2574 nvl(bdet.job_id, -1)
2575 and nvl(p_position_id, nvl(bdet.position_id, -1)) =
2576 nvl(bdet.position_id, -1)
2577 and nvl(p_grade_id, nvl(bdet.grade_id, -1)) =
2578 nvl(bdet.grade_id, -1);
2579
2580 cursor c3(p_budget_detail_id number) is
2581 select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
2582 from pqh_budget_periods bpr, per_time_periods tp_s,
2583 per_time_periods tp_e
2584 where bpr.budget_detail_id = p_budget_detail_id
2585 and tp_s.time_period_id = bpr.start_time_period_id
2586 and tp_e.time_period_id = bpr.end_time_period_id
2587 and tp_s.period_set_name = l_calendar
2588 and tp_e.period_set_name = l_calendar
2589 and p_effective_date between tp_s.start_date and tp_e.end_date;
2590 begin
2591 begin
2592
2593 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
2594 into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
2595 from pqh_budgets
2596 where position_control_flag = 'Y'
2597 and budgeted_entity_cd = p_budget_entity
2598 and business_group_id = p_business_group_id
2599 and p_effective_date between budget_start_date and budget_end_date
2600 and (
2601 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = p_unit_of_measure
2602 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_unit_of_measure
2603 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure
2604 );
2605 --
2606 hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2607 hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2608 hr_utility.set_location('l_calendar:' || l_calendar, 600);
2609 hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2610 hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2611 hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2612 --
2613 open c1(l_budget_unit1_id);
2614 fetch c1 into l_unit1_name;
2615 close c1;
2616 open c1(l_budget_unit2_id);
2617 fetch c1 into l_unit2_name;
2618 close c1;
2619 open c1(l_budget_unit3_id);
2620 fetch c1 into l_unit3_name;
2621 close c1;
2622 hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2623 hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2624 hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2625 exception
2626 when others then
2627 hr_utility.set_location('Error: ' || SQLERRM, 602);
2628 return l_budgeted_fte;
2629 end;
2630 hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2631 for i in c2(l_budget_id) loop
2632 -- row corresponding to the position is picked up
2633 hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2634 --
2635 for j in c3(i.budget_detail_id) loop
2636 hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2637 if l_unit1_name = p_unit_of_measure then
2638 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit1_value,0);
2639
2640 elsif l_unit2_name = p_unit_of_measure then
2641 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit2_value,0);
2642
2643 elsif l_unit3_name = p_unit_of_measure then
2644 l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit3_value,0);
2645
2646 end if;
2647 end loop;
2648 end loop;
2649 hr_utility.set_location('l_budgeted_fte:' || l_budgeted_fte, 605);
2650 return l_budgeted_fte;
2651 end;
2652 --
2653 --
2654 function reserved_fte(p_person_id number, p_position_id number, p_effective_date date) return number is
2655 l_fte number:=0;
2656 l_status varchar2(150);
2657 cursor c1(p_person_id number, p_position_id number, p_effective_date date) is
2658 select to_number(poei_information6,'99999999.99') fte
2659 from per_position_extra_info
2660 where p_effective_date
2661 between fnd_date.canonical_to_date(poei_information3)
2662 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
2663 and position_id = p_position_id
2664 and information_type= 'PER_RESERVED'
2665 and poei_information5 = p_person_id;
2666 begin
2667 if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
2668 open c1(p_person_id, p_position_id, p_effective_date);
2669 fetch c1 into l_fte;
2670 close c1;
2671 end if;
2672 return l_fte;
2673 end;
2674 --
2675 function remain_reserved_fte(p_person_id number, p_position_id number, p_effective_date date) return number is
2676 l_reserved_fte number;
2677 l_person_fte number;
2678 l_fte number:=0;
2679 begin
2680 l_reserved_fte := reserved_fte(p_person_id, p_position_id, p_effective_date);
2681 l_person_fte := person_fte(p_person_id, p_position_id, p_effective_date,-1);
2682 if l_reserved_fte - l_person_fte > 0 then
2683 l_fte := l_reserved_fte - l_person_fte;
2684 end if;
2685 return l_fte;
2686 end;
2687 --
2688 function unreserved_fte(p_position_id number, p_effective_date date) return number is
2689 l_pos_fte number:=0;
2690 l_fte number:=0;
2691 l_status varchar2(150);
2692 cursor c1(p_position_id number, p_effective_date date) is
2693 select sum(to_number(poei_information6,'99999999.99')) fte
2694 from per_position_extra_info
2695 where p_effective_date
2696 between fnd_date.canonical_to_date(poei_information3)
2697 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
2698 and position_id = p_position_id
2699 and information_type= 'PER_RESERVED';
2700 --
2701 begin
2702 if p_position_id is not null and p_effective_date is not null then
2703 --
2704 l_pos_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
2705 --
2706 open c1(p_position_id, p_effective_date);
2707 fetch c1 into l_fte;
2708 close c1;
2709 end if;
2710 hr_utility.set_location('l_pos_fte : '||l_pos_fte, 131);
2711 hr_utility.set_location('l_fte : '||l_fte, 131);
2712 if l_pos_fte - nvl(l_fte,0) >=0 then
2713 return l_pos_fte - nvl(l_fte,0);
2714 else
2715 return 0;
2716 end if;
2717 end;
2718 --
2719 function used_unreserved_fte(p_person_id number, p_position_id number, p_effective_date date) return number is
2720 l_uu_fte number := 0;
2721 l_person_fte number;
2722 l_reserved_fte number;
2723 begin
2724 l_person_fte := pqh_psf_bus.person_fte(p_person_id, p_position_id, p_effective_date, -1);
2725 l_reserved_fte := pqh_psf_bus.reserved_fte(p_person_id, p_position_id, p_effective_date);
2726 hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
2727 hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
2728 if l_reserved_fte>0 and l_person_fte - l_reserved_fte > 0then
2729 l_uu_fte := l_person_fte - l_reserved_fte;
2730 end if;
2731 return l_uu_fte;
2732 end;
2733 --
2734 --
2735 function reserved_overused(p_position_id number, p_effective_date date) return number is
2736 l_reserved_overused number := 0;
2737 l_reserved_person_overused number := 0;
2738 cursor c1(p_position_id number, p_effective_date date) is
2739 select poei_information5 person_id
2740 from per_position_extra_info
2741 where p_effective_date
2742 between fnd_date.canonical_to_date(poei_information3)
2743 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
2744 and position_id = p_position_id
2745 and poei_information5 is not null
2746 and information_type= 'PER_RESERVED';
2747 begin
2748 if p_position_id is not null and p_effective_date is not null then
2749 for r1 in c1(p_position_id, p_effective_date)
2750 loop
2751 l_reserved_person_overused := used_unreserved_fte(r1.person_id, p_position_id, p_effective_date);
2752 l_reserved_overused := l_reserved_overused + l_reserved_person_overused;
2753 end loop;
2754 end if;
2755 return l_reserved_overused;
2756 end;
2757 --
2758 -- This function is used for assignment cost defaulting and doesnot consider
2759 -- default values
2760 --
2761 function assignment_fte(p_assignment_id number, p_effective_date date) return number is
2762 l_fte number := 0;
2763 cursor c1(p_assignment_id number, p_effective_date date) is
2764 select nvl(abv.value,0)
2765 from per_assignment_budget_values_f abv, per_all_assignments_f asg,
2766 per_assignment_status_types ast
2767 where asg.assignment_id = p_assignment_id
2768 and abv.assignment_id = asg.assignment_id
2769 and asg.assignment_type in ('E', 'C')
2770 and abv.unit = 'FTE'
2771 and p_effective_date between asg.effective_start_date and asg.effective_end_date
2772 and p_effective_date between abv.effective_start_date and abv.effective_end_date
2773 and asg.assignment_status_type_id = ast.assignment_status_type_id
2774 and ast.per_system_status <> 'TERM_ASSIGN';
2775 --
2776 begin
2777 open c1(p_assignment_id, p_effective_date);
2778 fetch c1 into l_fte;
2779 close c1;
2780 return l_fte;
2781 end;
2782 --
2783 --
2784 function remain_unreserved_fte(p_position_id number, p_effective_date date) return number is
2785 l_unreserved_fte number;
2786 l_nonreserved_asg_fte number;
2787 l_reserved_overused number;
2788 l_uu_fte number := 0;
2789 l_person_fte number;
2790 l_reserved_fte number;
2791 l_temp number;
2792 begin
2793 l_unreserved_fte := unreserved_fte(p_position_id, p_effective_date);
2794 l_nonreserved_asg_fte := nonreserved_asg_fte(p_position_id, p_effective_date);
2795 l_reserved_overused := reserved_overused(p_position_id, p_effective_date);
2796 hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
2797 hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
2798 hr_utility.set_location('uu: l_reserved_overused : '||l_reserved_overused, 131);
2799 l_temp := ((l_unreserved_fte - l_nonreserved_asg_fte) - l_reserved_overused);
2800 if l_temp > 0then
2801 l_uu_fte := l_temp;
2802 end if;
2803 return l_uu_fte;
2804 end;
2805 --
2806 function available_fte(p_person_id number, p_position_id number, p_effective_date date) return number is
2807 l_remain_reserved_fte number;
2808 l_remain_unreserved_fte number;
2809 l_fte number:=0;
2810 begin
2811 hr_utility.set_location('p_person_id : '||p_person_id, 131);
2812 hr_utility.set_location('p_position_id : '||p_position_id, 131);
2813 hr_utility.set_location('p_effective_date : '||p_effective_date, 131);
2814 if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
2815 l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
2816 l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
2817 l_fte := l_remain_reserved_fte + l_remain_unreserved_fte;
2818 hr_utility.set_location('l_remain_reserved_fte : '||l_remain_reserved_fte, 131);
2819 hr_utility.set_location('l_remain_unreserved_fte : '||l_remain_unreserved_fte, 131);
2820 hr_utility.set_location('l_fte : '||l_fte, 131);
2821 end if;
2822 return l_fte;
2823 end;
2824 --
2825 --
2826 function budgeted_money (p_position_id in number,
2827 p_effective_date in date) return number is
2828
2829 l_calendar varchar2(200);
2830 l_budget_id number;
2831 l_budget_unit1_id number;
2832 l_budget_unit2_id number;
2833 l_budget_unit3_id number;
2834 l_unit1_name varchar2(200);
2835 l_unit2_name varchar2(200);
2836 l_unit3_name varchar2(200);
2837 l_budgeted_money number;
2838 l_business_group_id number;
2839 --
2840 cursor c_bus_grp_id(p_position_id number) is
2841 select business_group_id
2842 from hr_all_positions_f
2843 where position_id = p_position_id;
2844 --
2845 cursor c1(p_unit_id number) is select system_type_cd from
2846 per_shared_types where shared_type_id = p_unit_id;
2847 cursor c2(p_budget_id number) is
2848 select bdt.budget_detail_id, bdt.budget_unit1_value, bdt.budget_unit2_value, bdt.budget_unit3_value
2849 from pqh_budget_details bdt,pqh_budget_versions bvr
2850 where bvr.budget_id = p_budget_id
2851 and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
2852 and bdt.budget_version_id = bvr.budget_version_id
2853 and bdt.position_id = p_position_id;
2854 begin
2855 hr_utility.set_location('inside get_pos_budget',10);
2856 begin
2857 open c_bus_grp_id(p_position_id);
2858 fetch c_bus_grp_id into l_business_group_id;
2859 close c_bus_grp_id;
2860 --
2861 hr_utility.set_location('l_business_group_id:' || l_business_group_id, 20);
2862 --
2863 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
2864 into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
2865 from pqh_budgets
2866 where position_control_flag = 'Y'
2867 and budgeted_entity_cd = 'POSITION'
2868 and business_group_id = l_business_group_id
2869 and p_effective_date between budget_start_date and budget_end_date
2870 and (
2871 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
2872 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
2873 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
2874 );
2875 hr_utility.set_location('budget is:'||l_budget_id,30);
2876 open c1(l_budget_unit1_id);
2877 fetch c1 into l_unit1_name;
2878 close c1;
2879 hr_utility.set_location('unit1 is:'||l_unit1_name,40);
2880 open c1(l_budget_unit2_id);
2881 fetch c1 into l_unit2_name;
2882 close c1;
2883 hr_utility.set_location('unit2 is:'||l_unit2_name,50);
2884 open c1(l_budget_unit3_id);
2885 fetch c1 into l_unit3_name;
2886 close c1;
2887 hr_utility.set_location('unit3 is:'||l_unit3_name,60);
2888 exception
2889 when others then
2890 hr_utility.set_location('some error occured',65);
2891 return l_budgeted_money;
2892 end;
2893 for i in c2(l_budget_id) loop
2894 hr_utility.set_location('budget_detail_id:'||i.budget_detail_id, 80);
2895 -- row corresponding to the position is picked up
2896 if l_unit1_name ='MONEY' then
2897 l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit1_value,0);
2898 elsif l_unit2_name ='MONEY' then
2899 l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit2_value,0);
2900 elsif l_unit3_name ='MONEY' then
2901 l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit3_value,0);
2902 end if;
2903 hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 90);
2904 end loop;
2905 hr_utility.set_location('total budgeted_money is:'||l_budgeted_money, 100);
2906 return l_budgeted_money;
2907 exception
2908 when others then
2909 hr_utility.set_location('some error occured -2 ', 110);
2910 return l_budgeted_money;
2911 end;
2912 --
2913 function get_pos_actuals_commitment(
2914 p_position_id in number,
2915 p_effective_date in date,
2916 p_ex_assignment_id in number default -1
2917 ) return number is
2918 l_actual_commitment number := 0;
2919 l_start_date date;
2920 l_end_date date;
2921 l_last_payroll_dt date;
2922 l_budget_version_id number;
2923 l_budget_id number;
2924 l_budget_unit1_id number;
2925 l_budget_unit2_id number;
2926 l_budget_unit3_id number;
2927 l_unit1_name varchar2(200);
2928 l_unit2_name varchar2(200);
2929 l_unit3_name varchar2(200);
2930 l_calendar varchar2(200);
2931 l_business_group_id number;
2932 --
2933 cursor c_bus_grp_id(p_position_id number) is
2934 select business_group_id
2935 from hr_all_positions_f
2936 where position_id = p_position_id;
2937 --
2938 cursor c1(p_unit_id number) is select system_type_cd from
2939 per_shared_types where shared_type_id = p_unit_id;
2940 --
2941 begin
2942 hr_utility.set_location('inside chk_pos_budget',10);
2943 hr_utility.set_location('position_id is '||p_position_id,20);
2944 hr_utility.set_location('effective_date is '||to_char(p_effective_date,'dd-MM-RRRR'),30);
2945 begin
2946 open c_bus_grp_id(p_position_id);
2947 fetch c_bus_grp_id into l_business_group_id;
2948 close c_bus_grp_id;
2949 --
2950 hr_utility.set_location('l_business_group_id:' || l_business_group_id,40);
2951 --
2952 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
2953 , budget_start_date, budget_end_date
2954 into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
2955 ,l_start_date, l_end_date
2956 from pqh_budgets
2957 where position_control_flag = 'Y'
2958 and budgeted_entity_cd = 'POSITION'
2959 and business_group_id = l_business_group_id
2960 and p_effective_date between budget_start_date and budget_end_date
2961 and (
2962 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
2963 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
2964 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
2965 );
2966 hr_utility.set_location('budget exists' || l_budget_id, 50);
2967 hr_utility.set_location('start_date is '||to_char(l_start_date,'dd-MM-RRRR'),55);
2968 hr_utility.set_location('end_date is '||to_char(l_end_date,'dd-MM-RRRR'),60);
2969 --
2970 select budget_version_id into l_budget_version_id
2971 from pqh_budget_versions bvr
2972 where budget_id = l_budget_id
2973 and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date);
2974 hr_utility.set_location('budget version exists' || l_budget_version_id, 70);
2975 open c1(l_budget_unit1_id);
2976 fetch c1 into l_unit1_name;
2977 close c1;
2978 hr_utility.set_location('unit1 is ' || l_unit1_name, 80);
2979 open c1(l_budget_unit2_id);
2980 fetch c1 into l_unit2_name;
2981 close c1;
2982 hr_utility.set_location('unit2 is ' || l_unit2_name, 90);
2983 open c1(l_budget_unit3_id);
2984 fetch c1 into l_unit3_name;
2985 close c1;
2986 hr_utility.set_location('unit3 is ' || l_unit3_name, 100);
2987 exception
2988 when others then
2989 hr_utility.set_location('some error occured', 110);
2990 return null;
2991 end;
2992 --
2993 if l_unit1_name ='MONEY' then
2994 l_actual_commitment := l_actual_commitment +
2995 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
2996 (
2997 p_budget_version_id =>l_budget_version_id,
2998 p_position_id =>p_position_id,
2999 p_start_date =>l_start_date,
3000 p_end_date =>l_end_date,
3001 p_unit_of_measure_id =>l_budget_unit1_id,
3002 p_value_type =>'T',
3003 p_ex_assignment_id =>p_ex_assignment_id
3004 );
3005 hr_utility.set_location('unit1_amt is'||l_actual_commitment, 120);
3006 elsif l_unit2_name ='MONEY' then
3007 l_actual_commitment := l_actual_commitment +
3008 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
3009 (
3010 p_budget_version_id =>l_budget_version_id,
3011 p_position_id =>p_position_id,
3012 p_start_date =>l_start_date,
3013 p_end_date =>l_end_date,
3014 p_unit_of_measure_id =>l_budget_unit2_id,
3015 p_value_type =>'T',
3016 p_ex_assignment_id =>p_ex_assignment_id
3017 );
3018 hr_utility.set_location('unit2_amt is'||l_actual_commitment, 120);
3019 elsif l_unit3_name ='MONEY' then
3020 l_actual_commitment := l_actual_commitment +
3021 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
3022 (
3023 p_budget_version_id =>l_budget_version_id,
3024 p_position_id =>p_position_id,
3025 p_start_date =>l_start_date,
3026 p_end_date =>l_end_date,
3027 p_unit_of_measure_id =>l_budget_unit3_id,
3028 p_value_type =>'T',
3029 p_ex_assignment_id =>p_ex_assignment_id
3030 );
3031 hr_utility.set_location('unit3_amt is'||l_actual_commitment, 120);
3032 end if;
3033 return l_actual_commitment;
3034 exception
3035 when others then
3036 hr_utility.set_location('some error occured - 2', 120);
3037 return null;
3038 end;
3039 --
3040 function get_asg_actuals_commitment(
3041 p_assignment_id in number,
3042 p_effective_date in date) return number is
3043 l_actuals number := 0;
3044 l_commitments number:=0;
3045 l_start_date date;
3046 l_end_date date;
3047 l_last_payroll_dt date;
3048 l_budget_version_id number;
3049 l_budget_id number;
3050 l_budget_unit1_id number;
3051 l_budget_unit2_id number;
3052 l_budget_unit3_id number;
3053 l_unit1_name varchar2(200);
3054 l_unit2_name varchar2(200);
3055 l_unit3_name varchar2(200);
3056 l_calendar varchar2(200);
3057 l_business_group_id number;
3058 --
3059 cursor c_bus_grp_id(p_assignment_id number) is
3060 select business_group_id
3061 from per_all_assignments_f
3062 where assignment_id = p_assignment_id
3063 and p_effective_date between effective_start_date and effective_end_date;
3064 --
3065 cursor c1(p_unit_id number) is select system_type_cd from
3066 per_shared_types where shared_type_id = p_unit_id;
3067
3068 begin
3069 begin
3070 open c_bus_grp_id(p_assignment_id);
3071 fetch c_bus_grp_id into l_business_group_id;
3072 close c_bus_grp_id;
3073 --
3074 hr_utility.set_location('l_business_group_id:' || l_business_group_id, 600
3075 );
3076 --
3077 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
3078 , budget_start_date, budget_end_date
3079 into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
3080 ,l_start_date, l_end_date
3081 from pqh_budgets
3082 where position_control_flag = 'Y'
3083 and budgeted_entity_cd = 'POSITION'
3084 and business_group_id = l_business_group_id
3085 and p_effective_date between budget_start_date and budget_end_date
3086 and (
3087 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
3088 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
3089 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3090 );
3091 --
3092 select budget_version_id into l_budget_version_id
3093 from pqh_budget_versions bvr
3094 where budget_id = l_budget_id
3095 and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date);
3096 open c1(l_budget_unit1_id);
3097 fetch c1 into l_unit1_name;
3098 close c1;
3099 open c1(l_budget_unit2_id);
3100 fetch c1 into l_unit2_name;
3101 close c1;
3102 open c1(l_budget_unit3_id);
3103 fetch c1 into l_unit3_name;
3104 close c1;
3105 exception
3106 when others then
3107 return null;
3108 end;
3109 --
3110 if l_unit1_name ='MONEY' then
3111 l_actuals := l_actuals + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_actuals
3112 (p_assignment_id =>p_assignment_id,
3113 p_actuals_start_date =>l_start_date,
3114 p_actuals_end_date =>l_end_date,
3115 p_unit_of_measure_id =>l_budget_unit1_id,
3116 p_last_payroll_dt =>l_last_payroll_dt);
3117 l_commitments := l_commitments + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_commitment(
3118 p_assignment_id =>p_assignment_id,
3119 p_budget_version_id =>l_budget_version_id,
3120 p_period_start_date =>l_start_date,
3121 p_period_end_date =>l_end_date,
3122 p_unit_of_measure_id =>l_budget_unit1_id);
3123 elsif l_unit2_name ='MONEY' then
3124 l_actuals := l_actuals + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_actuals
3125 (p_assignment_id =>p_assignment_id,
3126 p_actuals_start_date =>l_start_date,
3127 p_actuals_end_date =>l_end_date,
3128 p_unit_of_measure_id =>l_budget_unit2_id,
3129 p_last_payroll_dt =>l_last_payroll_dt);
3130 l_commitments := l_commitments + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_commitment(
3131 p_assignment_id =>p_assignment_id,
3132 p_budget_version_id =>l_budget_version_id,
3133 p_period_start_date =>l_start_date,
3134 p_period_end_date =>l_end_date,
3135 p_unit_of_measure_id =>l_budget_unit2_id);
3136 elsif l_unit3_name ='MONEY' then
3137 l_actuals := l_actuals + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_actuals
3138 (p_assignment_id =>p_assignment_id,
3139 p_actuals_start_date =>l_start_date,
3140 p_actuals_end_date =>l_end_date,
3141 p_unit_of_measure_id =>l_budget_unit3_id,
3142 p_last_payroll_dt =>l_last_payroll_dt);
3143 l_commitments := l_commitments + pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_commitment(
3144 p_assignment_id =>p_assignment_id,
3145 p_budget_version_id =>l_budget_version_id,
3146 p_period_start_date =>l_start_date,
3147 p_period_end_date =>l_end_date,
3148 p_unit_of_measure_id =>l_budget_unit3_id);
3149 end if;
3150 return l_actuals + l_commitments;
3151 exception
3152 when others then
3153 return null;
3154 end;
3155 --
3156 --
3157 function chk_pos_budget(p_position_id in number, p_effective_date in date) return boolean is
3158 l_budgeted_money number;
3159 l_pos_actuals_commitment number;
3160 begin
3161 hr_utility.set_location('inside chk_pos_budget', 30);
3162 l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3163 l_pos_actuals_commitment := get_pos_actuals_commitment(p_position_id,p_effective_date);
3164 hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 10);
3165 hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 20);
3166 if l_budgeted_money <> -1 and l_pos_actuals_commitment <> -1 then
3167 if l_pos_actuals_commitment >l_budgeted_money then
3168 hr_utility.set_location('actual> budget', 30);
3169 return false;
3170 end if;
3171 end if;
3172 return true;
3173 exception
3174 when others then
3175 return true;
3176 end;
3177 --
3178 function chk_pos_budget(p_position_id in number, p_effective_date in date, p_ex_assignment_id number) return boolean is
3179 l_budgeted_money number;
3180 l_pos_actuals_commitment number;
3181 l_pos_actuals_cmmt_ex_asg number;
3182 l_asg_actuals_commitment number;
3183 begin
3184 l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3185 hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 100);
3186 if l_budgeted_money > -1 then
3187 l_asg_actuals_commitment := get_asg_actuals_commitment(p_ex_assignment_id,p_effective_date);
3188 hr_utility.set_location('l_asg_actuals_commitment:'||l_asg_actuals_commitment, 100);
3189 if l_asg_actuals_commitment > 0 then
3190 l_pos_actuals_cmmt_ex_asg := get_pos_actuals_commitment(
3191 p_position_id,
3192 p_effective_date,
3193 p_ex_assignment_id);
3194 l_pos_actuals_commitment := l_pos_actuals_cmmt_ex_asg+l_asg_actuals_commitment;
3195
3196 hr_utility.set_location('l_pos_actuals_cmmt_ex_asg:'||l_pos_actuals_cmmt_ex_asg, 100);
3197 hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 100);
3198 if l_pos_actuals_cmmt_ex_asg <> -1 then
3199 if ((l_pos_actuals_cmmt_ex_asg < l_budgeted_money)
3200 and (l_pos_actuals_commitment > l_budgeted_money)) then
3201 return true;
3202 end if;
3203 end if;
3204 end if;
3205 end if;
3206 return false;
3207 exception
3208 when others then
3209 return false;
3210 end;
3211 --
3212 --
3213 --
3214 function pos_reserved_fte(p_position_id number, p_effective_date date,
3215 p_ex_position_extra_info_id number default -1) return number is
3216 l_fte number:=0;
3217 l_status varchar2(150);
3218 l_ex_position_extra_info_id number := nvl(p_ex_position_extra_info_id, -1);
3219 cursor c1(p_position_id number, p_effective_date date, p_ex_position_extra_info_id number) is
3220 select sum(poei_information6) fte
3221 from per_position_extra_info
3222 where p_effective_date
3223 between fnd_date.canonical_to_date(poei_information3)
3224 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
3225 and position_id = p_position_id
3226 and information_type= 'PER_RESERVED'
3227 and position_extra_info_id <> l_ex_position_extra_info_id;
3228 begin
3229 if p_position_id is not null and p_effective_date is not null then
3230 open c1(p_position_id, p_effective_date, p_ex_position_extra_info_id );
3231 fetch c1 into l_fte;
3232 close c1;
3233 end if;
3234 return l_fte;
3235 end;
3236 --
3237 function poei_reserved_fte(p_position_extra_info_id number) return number is
3238 l_fte number;
3239 cursor c1(p_position_extra_info_id number) is
3240 select poei_information6
3241 from per_position_extra_info
3242 where position_extra_info_id = p_position_extra_info_id;
3243 begin
3244 if p_position_extra_info_id is not null then
3245 open c1(p_position_extra_info_id);
3246 fetch c1 into l_fte;
3247 close c1;
3248 end if;
3249 --
3250 return l_fte;
3251 end;
3252 --
3253 --
3254 function nonreserved_asg_fte(p_position_id number, p_effective_date date,
3255 p_ex_position_extra_info_id number default -1, p_ex_person_id number default -1) return number is
3256 l_unreserved_fte number;
3257 l_reserved_fte number;
3258 l_person_fte number;
3259 l_uu_fte number := 0;
3260 l_ex_person_id number := nvl(p_ex_person_id,-1);
3261 l_ex_position_extra_info_id number := nvl(p_ex_position_extra_info_id,-1);
3262 --
3263 cursor c1(p_position_id number, p_effective_date date,
3264 p_ex_position_extra_info_id number, p_ex_person_id number) is
3265 select sum(nvl(value,0))
3266 from per_all_assignments_f asn,
3267 per_assignment_budget_values_f abv,
3268 per_assignment_status_types ast
3269 where abv.assignment_id = asn.assignment_id
3270 and asn.EFFECTIVE_START_DATE <= p_effective_date
3271 and asn.EFFECTIVE_END_DATE >= p_effective_date
3272 and abv.EFFECTIVE_START_DATE <= p_effective_date
3273 and abv.EFFECTIVE_END_DATE >= p_effective_date
3274 and asn.position_id = p_position_id
3275 and asn.person_id <> l_ex_person_id
3276 and asn.assignment_type in ('E', 'C')
3277 and abv.unit = 'FTE'
3278 and asn.assignment_status_type_id = ast.assignment_status_type_id
3279 and ast.per_system_status <> 'TERM_ASSIGN'
3280 and not exists (
3281 select null
3282 from per_position_extra_info
3283 where information_type= 'PER_RESERVED'
3284 and position_id = p_position_id
3285 and position_extra_info_id <> l_ex_position_extra_info_id
3286 and fnd_date.canonical_to_date(poei_information3) <= p_effective_date
3287 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
3288 >= p_effective_date
3289 and poei_information5 = asn.person_id);
3290 l_nonreserved_fte number:=0;
3291 begin
3292 open c1(p_position_id, p_effective_date,
3293 p_ex_position_extra_info_id, p_ex_person_id);
3294 fetch c1 into l_nonreserved_fte;
3295 hr_utility.set_location('l_nonreserved_fte : '||l_nonreserved_fte, 131);
3296 return nvl(l_nonreserved_fte,0);
3297 end;
3298 --
3299 function position_fte(p_position_id number, p_effective_date date) return number is
3300 l_fte number;
3301 cursor c1(p_position_id number, p_effective_date date) is
3302 select fte
3303 from hr_all_positions_f
3304 where position_id = p_position_id
3305 and p_effective_date between effective_start_date and effective_end_date;
3306 begin
3307 open c1(p_position_id, p_effective_date);
3308 fetch c1 into l_fte;
3309 close c1;
3310 return l_fte;
3311 end;
3312 --
3313 -- ---------------------------------------------------------------------------
3314 -- |--------------------------< person_asg_fte >------------------------|
3315 -- ---------------------------------------------------------------------------
3316 -- Description:
3317 -- Retrieves the assignment_fte of the position.
3318 --
3319 function person_asg_fte
3320 (p_person_id in number, p_position_id in number, p_effective_date in date, p_ex_assignment_id number default -1) return number is
3321 l_person_id number;
3322 l_assignment_fte number(15,2):=0;
3323 CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
3324 select nvl(sum(nvl(value,1)),0)
3325 from per_assignment_budget_values_f abv, per_all_assignments_f asn,
3326 per_assignment_status_types ast
3327 where abv.assignment_id(+) = asn.assignment_id
3328 and asn.position_id = p_position_id
3329 and asn.person_id = p_person_id
3330 and asn.assignment_id <> nvl(p_ex_assignment_id, -1)
3331 and p_effective_date between asn.effective_start_date and asn.effective_end_date
3332 and p_effective_date between abv.effective_start_date and abv.effective_end_date
3333 and asn.assignment_type in ('E', 'C')
3334 and abv.unit(+) = 'FTE'
3335 and asn.assignment_status_type_id = ast.assignment_status_type_id
3336 and ast.per_system_status <> 'TERM_ASSIGN';
3337 begin
3338 if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3339 open c_budgeted_fte(p_person_id, p_position_id);
3340 fetch c_budgeted_fte into l_assignment_fte;
3341 hr_utility.set_location('l_person_id : '||l_person_id, 630);
3342 hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
3343 close c_budgeted_fte;
3344 end if;
3345 return(l_assignment_fte);
3346 end;
3347 --
3348
3349 procedure pqh_poei_validate(p_position_id number,
3350 p_position_extra_info_id number, p_person_id number,
3351 p_start_date date, p_end_date date, p_poei_fte number) is
3352 l_person_asg_fte number;
3353 l_reserved_fte number;
3354 l_nonreserved_asg_fte number;
3355 l_poei_fte number;
3356 l_total_fte number;
3357 l_position_fte number;
3358 l_budgeted_fte number;
3359 l_business_group_id number;
3360 l_realloc_fte number;
3361 l_bgt_realloc number;
3362 --
3363 cursor c1(p_position_id number, p_position_extra_info_id number,
3364 p_start_date date, p_end_date date) is
3365 select abv.effective_start_date effective_date
3366 from per_assignment_budget_values_f abv, per_all_assignments_f asn
3367 where abv.assignment_id = asn.assignment_id
3368 and asn.position_id = p_position_id
3369 and abv.effective_start_date between asn.effective_start_date and asn.effective_end_date
3370 and abv.effective_start_date between
3371 p_start_date and nvl(p_end_date, hr_general.end_of_time)
3372 and asn.effective_start_date between
3373 p_start_date and nvl(p_end_date, hr_general.end_of_time)
3374 and asn.assignment_type in ('E', 'C')
3375 and abv.unit(+) = 'FTE'
3376 union
3377 select effective_start_date effective_date
3378 from per_all_assignments_f asg
3379 where position_id = p_position_id
3380 and asg.effective_start_date between
3381 p_start_date and nvl(p_end_date, hr_general.end_of_time)
3382 union
3383 select effective_start_date effective_date
3384 from hr_all_positions_f psf
3385 where position_id = p_position_id
3386 and psf.effective_start_date between
3387 p_start_date and nvl(p_end_date, hr_general.end_of_time)
3388 union
3389 select p_start_date effective_date
3390 from dual
3391 union
3392 select nvl(p_end_date, hr_general.end_of_time) effective_date
3393 from dual;
3394 --
3395 cursor c2(p_position_id number) is
3396 select business_group_id
3397 from hr_all_positions_f
3398 where position_id = p_position_id;
3399 --
3400 begin
3401 --
3402 open c2(p_position_id);
3403 fetch c2 into l_business_group_id;
3404 close c2;
3405 --
3406 --
3407 for r1 in c1(p_position_id, p_position_extra_info_id, p_start_date, p_end_date)
3408 loop
3409 --
3410 l_reserved_fte := pos_reserved_fte(p_position_id, r1.effective_date, p_position_extra_info_id);
3411 l_nonreserved_asg_fte := nonreserved_asg_fte(p_position_id, r1.effective_date,
3412 p_position_extra_info_id, p_person_id);
3413 l_person_asg_fte := pqh_psf_bus.person_asg_fte(p_person_id, p_position_id, r1.effective_date);
3414 l_poei_fte := greatest(l_person_asg_fte, p_poei_fte);
3415 l_position_fte := pqh_psf_bus.position_fte(p_position_id, r1.effective_date);
3416 --
3417 l_total_fte := nvl(l_reserved_fte,0) + nvl(l_nonreserved_asg_fte,0) + l_poei_fte;
3418 l_budgeted_fte := pqh_psf_bus.budgeted_fte(p_position_id, r1.effective_date);
3419 --
3420 l_realloc_fte := pqh_reallocation_pkg.get_reallocation(
3421 p_position_id => p_position_id
3422 ,p_start_date => r1.effective_date
3423 ,p_end_date => r1.effective_date
3424 ,p_effective_date => r1.effective_date
3425 ,p_system_budget_unit => 'FTE'
3426 ,p_business_group_id => l_business_group_id
3427 );
3428 --
3429 hr_utility.set_location('effective date : '||r1.effective_date, 10);
3430 hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3431 hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3432 hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3433 hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3434 hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3435 hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3436 hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3437 hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3438 --
3439 if l_budgeted_fte is not null or l_realloc_fte is not null then
3440 --
3441 l_bgt_realloc := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3442 --
3443 if l_total_fte > l_bgt_realloc then
3444 hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_BGT_FTE');
3445 --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3446 hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3447 hr_utility.raise_error;
3448 end if;
3449 else
3450 if l_total_fte > l_position_fte then
3451 hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3452 --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3453 hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3454 hr_utility.raise_error;
3455 end if;
3456 end if;
3457 end loop;
3458 end;
3459 --
3460 --
3461 function chk_reserved_fte(p_assignment_id number, p_person_id number,
3462 p_position_id number, p_position_type varchar2,
3463 p_effective_date date, p_default_asg_fte number default null)
3464 return boolean is
3465 l_available_fte number;
3466 l_assignment_fte number;
3467 l_dummy varchar2(10);
3468 l_position_type varchar2(30);
3469 l_overlap_period number;
3470 l_overlap_dates_present boolean;
3471 l_business_group_id number;
3472 --
3473 cursor c_pos_fte(p_position_id number, p_effective_date date) is
3474 select position_type, overlap_period, business_group_id
3475 from hr_all_positions_f
3476 where position_id = p_position_id
3477 and p_effective_date between effective_start_date and effective_end_date;
3478 ----
3479 /*** index hint added in the select statement of the inner
3480 query of the cursor as a fix of bug 5963148 **/
3481 cursor c_pos_reserved(p_position_id number, p_effective_date date) is
3482 select 'x'
3483 from (select /*+ INDEX(PER_POSITION_EXTRA_INFO PER_POSITION_EXTRA_INFO_N3)*/
3484 fnd_date.canonical_to_date(poei_information3) poei_information3,
3485 nvl(fnd_date.canonical_to_date(poei_information4),
3486 hr_general.end_of_time) poei_information4
3487 from per_position_extra_info
3488 where information_type = 'PER_RESERVED'
3489 and position_id = p_position_id)
3490 where p_effective_date between poei_information3 and poei_information4;
3491 --
3492 begin
3493 open c_pos_fte(p_position_id, p_effective_date);
3494 fetch c_pos_fte into l_position_type,
3495 l_overlap_period, l_business_group_id;
3496 close c_pos_fte;
3497 --
3498 hr_utility.set_location('chk_pos_fte_sum_asg_fte l_position_type'||l_position_type , 20);
3499 --
3500 --
3501 if l_position_type in ('SHARED', 'SINGLE') then
3502 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
3503 p_position_id => p_position_id,
3504 p_overlap_period => l_overlap_period,
3505 p_assignment_start_date => p_effective_date);
3506 if not l_overlap_dates_present then
3507
3508 hr_utility.set_location('Entering chk_reserved_fte', 100);
3509 --
3510 open c_pos_reserved(p_position_id, p_effective_date);
3511 fetch c_pos_reserved into l_dummy;
3512 if c_pos_reserved%notfound then
3513 hr_utility.set_location('Exiting chk_reserved_fte FALSE', 100);
3514 close c_pos_reserved;
3515 return false;
3516 else
3517 close c_pos_reserved;
3518 end if;
3519 l_available_fte := pqh_psf_bus.available_fte(p_person_id, p_position_id, p_effective_date);
3520 if l_position_type = 'SINGLE' then
3521 hr_utility.set_location('p_position_type is SINGLE ', 110);
3522 l_assignment_fte := 1;
3523 elsif p_assignment_id is not null then
3524 hr_utility.set_location('p_assignment_id is not null ', 111);
3525 l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0
3526 );
3527 else
3528 hr_utility.set_location('p_assignment_id is null and pos type SHARED', 112);
3529 l_assignment_fte := p_default_asg_fte;
3530 end if;
3531 hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3532 hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3533 hr_utility.set_location('p_default_asg_fte : '||p_default_asg_fte, 133);
3534 if (l_assignment_fte > l_available_fte) then
3535 hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3536 return true;
3537 else
3538 hr_utility.set_location('Exiting chk_reserved_fte FALSE', 100);
3539 return false;
3540 end if;
3541 end if;
3542 end if;
3543 hr_utility.set_location('Exiting chk_reserved_fte FALSE', 420);
3544 return false;
3545 end;
3546 --
3547 function chk_pos_reserve_exists(p_position_id number,
3548 p_effective_date date)
3549 return boolean is
3550 l_dummy varchar2(10);
3551 --
3552 /*** index hint added in the select statement of the inner
3553 query of the cursor as a fix of bug 6409206 **/
3554 cursor c_pos_reserved(p_position_id number,
3555 p_effective_date date) is
3556 select 'x'
3557 from (select /*+ INDEX(PER_POSITION_EXTRA_INFO PER_POSITION_EXTRA_INFO_N3)*/
3558 fnd_date.canonical_to_date(poei_information3) poei_information3,
3559 nvl(fnd_date.canonical_to_date(poei_information4),
3560 hr_general.end_of_time) poei_information4
3561 from per_position_extra_info
3562 where information_type = 'PER_RESERVED'
3563 and position_id = p_position_id)
3564 where p_effective_date <= poei_information4;
3565 --
3566 begin
3567 open c_pos_reserved(p_position_id, p_effective_date);
3568 fetch c_pos_reserved into l_dummy;
3569 if c_pos_reserved%found then
3570 hr_utility.set_location('Exiting chk_pos_reserve_exists TRUE', 100);
3571 close c_pos_reserved;
3572 return true;
3573 end if;
3574 hr_utility.set_location('Exiting chk_pos_reserve_exists FALSE', 100);
3575 close c_pos_reserved;
3576 return false;
3577 end;
3578 --
3579 --
3580 function chk_future_reserved_fte(p_assignment_id number, p_person_id number,
3581 p_position_id number, p_position_type varchar2,
3582 p_validation_start_date date, p_validation_end_date date,
3583 p_default_asg_fte number default null)
3584 return date is
3585 l_available_fte number;
3586 l_assignment_fte number;
3587 cursor c1(p_assignment_id number, p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3588 select effective_start_date
3589 from per_all_assignments_f
3590 where position_id = p_position_id
3591 and assignment_type in ('E', 'C')
3592 and effective_start_date between p_validation_start_date and p_validation_end_date
3593 union
3594 select abv.effective_start_date effective_start_date
3595 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3596 where abv.assignment_id = asg.assignment_id
3597 and asg.position_id = p_position_id
3598 and asg.assignment_type in ('E', 'C')
3599 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3600 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3601 and asg.business_group_id = abv.business_group_id
3602 union
3603 select effective_start_date
3604 from (select fnd_date.canonical_to_date(poei_information3) effective_start_date
3605 from per_position_extra_info
3606 where position_id = p_position_id
3607 and information_type = 'PER_RESERVED')
3608 where effective_start_date >= p_validation_start_date
3609 union
3610 select effective_start_date
3611 from per_all_assignments_f
3612 where assignment_id = p_assignment_id
3613 and assignment_type in ('E', 'C')
3614 and effective_start_date between p_validation_start_date and p_validation_end_date
3615 union
3616 select abv.effective_start_date effective_start_date
3617 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3618 where abv.assignment_id = asg.assignment_id
3619 and asg.assignment_id = p_assignment_id
3620 and asg.assignment_type in ('E', 'C')
3621 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3622 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3623 union
3624 select effective_start_date
3625 from hr_all_positions_f
3626 where position_id = p_position_id
3627 and effective_start_date between p_validation_start_date and p_validation_end_date;
3628 --
3629 cursor c2(p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3630 select effective_start_date
3631 from per_all_assignments_f
3632 where position_id = p_position_id
3633 and assignment_type in ('E', 'C')
3634 and effective_start_date between p_validation_start_date and p_validation_end_date
3635 union
3636 select abv.effective_start_date effective_start_date
3637 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3638 where abv.assignment_id = asg.assignment_id
3639 and asg.position_id = p_position_id
3640 and asg.assignment_type in ('E', 'C')
3641 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3642 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3643 union
3644 select effective_start_date
3645 from (select fnd_date.canonical_to_date(poei_information3) effective_start_date
3646 from per_position_extra_info
3647 where position_id = p_position_id
3648 and information_type = 'PER_RESERVED')
3649 where effective_start_date >= p_validation_start_date
3650 union
3651 select effective_start_date
3652 from hr_all_positions_f
3653 where position_id = p_position_id
3654 and effective_start_date between p_validation_start_date and p_validation_end_date;
3655 --
3656 begin
3657 hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 201);
3658 hr_utility.set_location('chk_future_reserved_fte p_validation_start_date: '
3659 ||p_validation_start_date, 202);
3660 hr_utility.set_location('chk_future_reserved_fte p_validation_end_date: '
3661 ||p_validation_end_date, 202);
3662 if (p_position_id is not null) then
3663 if chk_pos_reserve_exists(p_position_id,
3664 p_validation_start_date) then
3665 if (p_assignment_id is not null) then
3666 for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
3667 loop
3668 hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '
3669 ||r1.effective_start_date, 203);
3670 hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3671 hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3672 hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3673 hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3674 if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3675 r1.effective_start_date, p_default_asg_fte) then
3676 return r1.effective_start_date;
3677 end if;
3678 end loop;
3679 else
3680 for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
3681 loop
3682 hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3683 hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3684 hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3685 hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3686 hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3687 if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3688 r1.effective_start_date, p_default_asg_fte) then
3689 return r1.effective_start_date;
3690 end if;
3691 end loop;
3692 end if;
3693 end if;
3694 end if;
3695 return null;
3696 end;
3697 --
3698 --
3699 procedure chk_pos_fte_sum_asg_fte(p_assignment_id number, p_position_id number,
3700 p_effective_date date, p_default_asg_fte number default null,
3701 p_position_type out nocopy varchar2, p_organization_id out nocopy number,
3702 p_budgeted_fte out nocopy number, p_realloc_fte out nocopy number,
3703 p_position_fte out nocopy number, p_total_asg_fte out nocopy number) is
3704 --
3705 l_sum number;
3706 l_asg number;
3707 l_overlap_period number;
3708 l_overlap_dates_present boolean;
3709 l_business_group_id number;
3710 --
3711 cursor c_pos_fte(p_position_id number, p_effective_date date) is
3712 select position_type, fte, organization_id, overlap_period, business_group_id
3713 from hr_all_positions_f
3714 where position_id = p_position_id
3715 and p_effective_date between effective_start_date and effective_end_date;
3716 --
3717 begin
3718 open c_pos_fte(p_position_id, p_effective_date);
3719 fetch c_pos_fte into p_position_type, p_position_fte,
3720 p_organization_id, l_overlap_period, l_business_group_id;
3721 close c_pos_fte;
3722 --
3723 hr_utility.set_location('chk_pos_fte_sum_asg_fte p_position_type'||p_position_type , 20);
3724 --
3725 if p_position_type = 'SHARED' then
3726 --
3727 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
3728 p_position_id => p_position_id,
3729 p_overlap_period => l_overlap_period,
3730 p_assignment_start_date => p_effective_date);
3731 if not l_overlap_dates_present then
3732 hr_utility.set_location('chk_pos_fte_sum_asg_fte SHARED' , 20);
3733 p_budgeted_fte := budgeted_fte(p_position_id, p_effective_date);
3734 --
3735 p_realloc_fte := pqh_reallocation_pkg.get_reallocation(
3736 p_position_id => p_position_id
3737 ,p_start_date => p_effective_date
3738 ,p_end_date => p_effective_date
3739 ,p_effective_date => p_effective_date
3740 ,p_system_budget_unit =>'FTE'
3741 ,p_business_group_id => l_business_group_id
3742 );
3743 --
3744 --
3745 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
3746 if p_assignment_id is null then
3747 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
3748 p_total_asg_fte := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date) + p_default_asg_fte;
3749 else
3750 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
3751 l_sum := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date);
3752 l_asg := pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date);
3753 p_total_asg_fte := l_sum + l_asg;
3754 --
3755 hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
3756 hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
3757 hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
3758 end if;
3759 --
3760 hr_utility.set_location('chk_pos_fte_sum_asg_fte end SHARED' , 20);
3761 end if;
3762 elsif p_position_type = 'SINGLE' then
3763 --
3764 l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
3765 p_position_id => p_position_id,
3766 p_overlap_period => l_overlap_period,
3767 p_assignment_start_date => p_effective_date);
3768 if not l_overlap_dates_present then
3769 hr_utility.set_location('chk_pos_fte_sum_asg_fte SINGLE' , 20);
3770 --
3771 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
3772 if p_assignment_id is null then
3773 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
3774 p_total_asg_fte := pqh_psf_bus.no_assignments(p_position_id, p_effective_date) + 1;
3775 else
3776 hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
3777 l_sum := pqh_psf_bus.no_assignments(p_position_id, p_effective_date);
3778 l_asg := 1;
3779 p_total_asg_fte := l_sum + l_asg;
3780 --
3781 hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
3782 hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
3783 hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
3784 end if;
3785 --
3786 hr_utility.set_location('chk_pos_fte_sum_asg_fte end SINGLE' , 20);
3787 end if;
3788 end if;
3789 hr_utility.set_location('chk_pos_fte_sum_asg_fte end ' , 20);
3790 exception when others then
3791 p_position_type := null;
3792 p_organization_id := null;
3793 p_budgeted_fte := null;
3794 p_realloc_fte := null;
3795 p_position_fte := null;
3796 p_total_asg_fte := null;
3797 raise;
3798 end;
3799 --
3800 procedure chk_future_pos_asg_fte(p_assignment_id number,
3801 p_position_id number,
3802 p_validation_start_date date,
3803 p_validation_end_date date,
3804 p_default_asg_fte number default null) is
3805 --
3806 l_position_type varchar2(30);
3807 l_organization_id number;
3808 l_budgeted_fte number;
3809 l_realloc_fte number;
3810 l_bgt_and_realloc_fte number;
3811 l_position_fte number;
3812 l_total_asg_fte number;
3813 --
3814 cursor c1(p_assignment_id number, p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3815 select effective_start_date
3816 from per_all_assignments_f
3817 where position_id = p_position_id
3818 and assignment_type in ('E', 'C')
3819 and effective_start_date between p_validation_start_date and p_validation_end_date
3820 union
3821 select abv.effective_start_date effective_start_date
3822 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3823 where abv.assignment_id = asg.assignment_id
3824 and asg.position_id = p_position_id
3825 and asg.assignment_type in ('E', 'C')
3826 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3827 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3828 and asg.business_group_id = abv.business_group_id
3829 union
3830 select effective_start_date
3831 from per_all_assignments_f
3832 where assignment_id = p_assignment_id
3833 and assignment_type in ('E', 'C')
3834 and effective_start_date between p_validation_start_date and p_validation_end_date
3835 union
3836 select abv.effective_start_date effective_start_date
3837 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3838 where abv.assignment_id = asg.assignment_id
3839 and asg.assignment_id = p_assignment_id
3840 and asg.assignment_type in ('E', 'C')
3841 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3842 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3843 union
3844 select effective_start_date
3845 from hr_all_positions_f
3846 where position_id = p_position_id
3847 and effective_start_date between p_validation_start_date and p_validation_end_date;
3848 --
3849 cursor c2(p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3850 select effective_start_date
3851 from per_all_assignments_f
3852 where position_id = p_position_id
3853 and assignment_type in ('E', 'C')
3854 and effective_start_date between p_validation_start_date and p_validation_end_date
3855 union
3856 select abv.effective_start_date effective_start_date
3857 from per_assignment_budget_values_f abv, per_all_assignments_f asg
3858 where abv.assignment_id = asg.assignment_id
3859 and asg.position_id = p_position_id
3860 and asg.assignment_type in ('E', 'C')
3861 and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3862 and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3863 and asg.business_group_id = abv.business_group_id
3864 union
3865 select effective_start_date
3866 from hr_all_positions_f
3867 where position_id = p_position_id
3868 and effective_start_date between p_validation_start_date and p_validation_end_date;
3869 --
3870 begin
3871 hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 201);
3872 hr_utility.set_location('chk_future_pos_asg_fte p_validation_start_date: '||p_validation_start_date, 202);
3873 hr_utility.set_location('chk_future_pos_asg_fte p_validation_end_date: '||p_validation_end_date, 202);
3874 if (p_assignment_id is not null and p_position_id is not null) then
3875 for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
3876 loop
3877 hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3878 hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
3879 hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
3880 hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3881 --
3882 chk_pos_fte_sum_asg_fte(
3883 p_assignment_id => p_assignment_id,
3884 p_position_id => p_position_id,
3885 p_effective_date => r1.effective_start_date,
3886 p_default_asg_fte => p_default_asg_fte,
3887 p_position_type => l_position_type,
3888 p_organization_id => l_organization_id,
3889 p_budgeted_fte => l_budgeted_fte,
3890 p_realloc_fte => l_realloc_fte,
3891 p_position_fte => l_position_fte,
3892 p_total_asg_fte => l_total_asg_fte);
3893 --
3894 hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
3895 hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
3896 hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
3897 hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
3898 hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
3899 hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
3900 --
3901 if l_position_type = 'SHARED' then
3902 --
3903 if l_budgeted_fte is not null or l_realloc_fte is not null then
3904 --
3905 l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3906 --
3907 if l_bgt_and_realloc_fte < l_total_asg_fte then
3908 hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
3909 hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
3910 pqh_utility.set_message_level_cd('W');
3911 pqh_utility.raise_error;
3912 return;
3913 end if;
3914 else
3915 if l_position_fte < l_total_asg_fte then
3916 hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
3917 hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
3918 pqh_utility.set_message_level_cd('W');
3919 pqh_utility.raise_error;
3920 return;
3921 end if;
3922 end if;
3923 --
3924 elsif l_position_type = 'SINGLE' then
3925 --
3926 if l_total_asg_fte > 1 then
3927 hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
3928 hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
3929 pqh_utility.set_message_level_cd('W');
3930 pqh_utility.raise_error;
3931 return;
3932 end if;
3933 --
3934 end if;
3935 end loop;
3936 elsif (p_assignment_id is null and p_position_id is not null) then
3937 for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
3938 loop
3939 hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3940 hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
3941 hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
3942 hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3943 --
3944 chk_pos_fte_sum_asg_fte(
3945 p_assignment_id => p_assignment_id,
3946 p_position_id => p_position_id,
3947 p_effective_date => r1.effective_start_date,
3948 p_default_asg_fte => p_default_asg_fte,
3949 p_position_type => l_position_type,
3950 p_organization_id => l_organization_id,
3951 p_budgeted_fte => l_budgeted_fte,
3952 p_realloc_fte => l_realloc_fte,
3953 p_position_fte => l_position_fte,
3954 p_total_asg_fte => l_total_asg_fte);
3955 --
3956 hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
3957 hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
3958 hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
3959 hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
3960 hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
3961 hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
3962 --
3963 if l_position_type = 'SHARED' then
3964 --
3965 if l_budgeted_fte is not null or l_realloc_fte is not null then
3966 --
3967 l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3968 --
3969 if l_bgt_and_realloc_fte < l_total_asg_fte then
3970 hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
3971 hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
3972 pqh_utility.set_message_level_cd('W');
3973 pqh_utility.raise_error;
3974 return;
3975 end if;
3976 else
3977 if l_position_fte < l_total_asg_fte then
3978 hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
3979 hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
3980 pqh_utility.set_message_level_cd('W');
3981 pqh_utility.raise_error;
3982 return;
3983 end if;
3984 end if;
3985 --
3986 elsif l_position_type = 'SINGLE' then
3987 --
3988 if l_total_asg_fte > 1 then
3989 hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
3990 hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
3991 pqh_utility.set_message_level_cd('W');
3992 pqh_utility.raise_error;
3993 return;
3994 end if;
3995 --
3996 end if;
3997 end loop;
3998 end if;
3999 end;
4000 --
4001 procedure CHK_ABV_FTE_GT_POS_BGT_FTE
4002 (p_assignment_id number,
4003 p_position_id number,
4004 p_effective_date date,
4005 p_default_asg_fte number default null,
4006 p_bgt_lt_abv_fte out nocopy boolean
4007 ) is
4008 l_position_type varchar2(30);
4009 l_organization_id number;
4010 l_budgeted_fte number;
4011 l_realloc number;
4012 l_position_fte number;
4013 l_sum number;
4014 l_bgt_realloc number;
4015 begin
4016 --
4017 pqh_psf_bus.chk_pos_fte_sum_asg_fte(
4018 p_assignment_id => p_assignment_id,
4019 p_position_id => p_position_id,
4020 p_effective_date => p_effective_date,
4021 p_default_asg_fte => p_default_asg_fte,
4022 p_position_type => l_position_type,
4023 p_organization_id => l_organization_id,
4024 p_budgeted_fte => l_budgeted_fte,
4025 p_realloc_fte => l_realloc,
4026 p_position_fte => l_position_fte,
4027 p_total_asg_fte => l_sum);
4028 --
4029 --
4030 p_bgt_lt_abv_fte := false;
4031 --
4032 if l_budgeted_fte is not null or l_realloc is not null then
4033 --
4034 l_bgt_realloc := nvl(l_budgeted_fte,0) + nvl(l_realloc,0);
4035 --
4036 if l_bgt_realloc < l_sum then
4037 p_bgt_lt_abv_fte := true;
4038 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_BGT_FTE',l_organization_id);
4039 pqh_utility.raise_error;
4040 end if;
4041 else
4042 if l_position_fte < l_sum then
4043 p_bgt_lt_abv_fte := true;
4044 pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',l_organization_id);
4045 pqh_utility.raise_error;
4046 end if;
4047 end if;
4048 --
4049 exception when others then
4050 p_bgt_lt_abv_fte := null;
4051 raise;
4052 end;
4053 --
4054 function get_position_fte(p_position_id number, p_effective_date date) return number is
4055 --
4056 l_budgeted_fte number;
4057 l_realloc_fte number;
4058 l_pos_fte number;
4059 l_fte number;
4060 l_status varchar2(150);
4061 l_business_group_id number;
4062 --
4063 cursor c2(p_position_id number, p_effective_date date) is
4064 select fte, business_group_id
4065 from hr_all_positions_f
4066 where position_id = p_position_id
4067 and p_effective_date
4068 between effective_start_date and effective_end_date;
4069 begin
4070 if p_position_id is not null and p_effective_date is not null then
4071 --
4072 open c2(p_position_id, p_effective_date);
4073 fetch c2 into l_pos_fte, l_business_group_id;
4074 close c2;
4075 --
4076 l_budgeted_fte := pqh_psf_bus.budgeted_fte(p_position_id, p_effective_date);
4077 --
4078 --
4079 l_realloc_fte := pqh_reallocation_pkg.get_reallocation(
4080 p_position_id => p_position_id
4081 ,p_start_date => p_effective_date
4082 ,p_end_date => p_effective_date
4083 ,p_effective_date => p_effective_date
4084 ,p_system_budget_unit =>'FTE'
4085 ,p_business_group_id => l_business_group_id
4086 );
4087 --
4088 if l_budgeted_fte is not null or l_realloc_fte is not null then
4089 l_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4090 else
4091 hr_utility.set_location('l_pos_fte : '||l_pos_fte, 131);
4092 l_fte := nvl(l_pos_fte,0);
4093 end if;
4094 end if;
4095 hr_utility.set_location('l_fte : '||l_fte, 131);
4096 return l_fte;
4097 end;
4098 --
4099 --
4100 procedure reserved_error(p_assignment_id number, p_person_id number,
4101 p_position_id number, p_effective_start_date date,
4102 p_organization_id number,
4103 p_default_asg_fte number default 0) is
4104 --
4105 cursor c1 is
4106 select sum(to_number(poei_information6,'99999999.99'))
4107 from per_position_extra_info
4108 where information_type = 'PER_RESERVED'
4109 and position_id = p_position_id
4110 and p_effective_start_date
4111 between fnd_date.canonical_to_date(poei_information3)
4112 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
4113 and poei_information5 is null;
4114 --
4115 --
4116 CURSOR c_sum_asg_fte(p_position_id number) is
4117 select sum(value)
4118 from per_assignment_budget_values_f abv, per_all_assignments_f asn,
4119 per_assignment_status_types ast
4120 where abv.assignment_id(+) = asn.assignment_id
4121 and p_effective_start_date between asn.effective_start_date and asn.effective_end_date
4122 and p_effective_start_date between abv.effective_start_date and abv.effective_end_date
4123 and asn.position_id = p_position_id
4124 and asn.assignment_type in ('E', 'C')
4125 and abv.unit(+) = 'FTE'
4126 and asn.assignment_status_type_id = ast.assignment_status_type_id
4127 and ast.per_system_status <> 'TERM_ASSIGN'
4128 and not exists
4129 ( select null
4130 from per_position_extra_info poei
4131 where poei.information_type = 'PER_RESERVED'
4132 and poei.position_id = p_position_id
4133 and p_effective_start_date
4134 between fnd_date.canonical_to_date(poei.poei_information3)
4135 and nvl(fnd_date.canonical_to_date(poei.poei_information4),hr_general.end_of_time)
4136 and to_number(poei.poei_information5) = asn.person_id);
4137 --
4138 l_blank_res_pos_fte number := 0;
4139 l_unreserved_fte number := 0;
4140 l_reserved_overused number := 0;
4141 l_sum_asg_fte number := 0;
4142 l_asg_fte number := 0;
4143 l_total_asg_fte number := 0;
4144 --
4145 begin
4146 open c1;
4147 fetch c1 into l_blank_res_pos_fte;
4148 close c1;
4149 --
4150 l_unreserved_fte := unreserved_fte(p_position_id, p_effective_start_date);
4151 --
4152 l_reserved_overused := reserved_overused(p_position_id, p_effective_start_date);
4153
4154 open c_sum_asg_fte(p_position_id);
4155 fetch c_sum_asg_fte into l_sum_asg_fte;
4156 close c_sum_asg_fte;
4157 --
4158 l_asg_fte := assignment_fte(p_assignment_id, p_effective_start_date);
4159 --
4160 l_total_asg_fte := nvl(l_sum_asg_fte,0) + nvl(l_asg_fte,0)
4161 + l_reserved_overused + p_default_asg_fte;
4162 --
4163 --
4164 if (l_blank_res_pos_fte < 0) then
4165 hr_utility.set_location('POSITION RESERVED2', 114);
4166 pqh_utility.set_message(8302,'PQH_POS_RESERVED',p_organization_id);
4167 pqh_utility.raise_error;
4168 elsif ((l_total_asg_fte <= (l_blank_res_pos_fte + l_unreserved_fte))
4169 or (nvl(l_blank_res_pos_fte,0) >=
4170 nvl(l_asg_fte,0) + nvl(p_default_asg_fte,0))) then
4171 hr_utility.set_location('PQH_ANONYM_POS_RESERVED', 115);
4172 pqh_utility.set_message(8302,'PQH_ANONYM_POS_RESERVED',p_organization_id);
4173 pqh_utility.raise_error;
4174 else
4175 hr_utility.set_location('PQH_POS_RESERVED', 116);
4176 pqh_utility.set_message(8302,'PQH_POS_RESERVED',p_organization_id);
4177 pqh_utility.raise_error;
4178 end if;
4179 --
4180 end;
4181 --
4182 function get_pc_topnode (p_business_group_id in number,
4183 p_effective_date in date default null) return number is
4184 l_top_node number := -1;
4185 l_effective_date date;
4186 l_business_group_id number;
4187 l_pc_version number := -1;
4188
4189 cursor csr_top_node(p_pc_version in number) is
4190 select a.organization_id_parent organization_id
4191 from per_org_structure_elements a
4192 where a.org_structure_version_id = p_pc_version
4193 and not exists
4194 (select null
4195 from per_org_structure_elements b
4196 where b.org_structure_version_id = p_pc_version
4197 AND b.organization_id_child = a.organization_id_parent);
4198 begin
4199 l_pc_version := get_pc_str_version(p_business_group_id, p_effective_date);
4200 open csr_top_node(l_pc_version);
4201 fetch csr_top_node into l_top_node;
4202 close csr_top_node;
4203 return l_top_node;
4204 end get_pc_topnode;
4205 --
4206 function get_pc_str_version (p_business_group_id in number default null,
4207 p_effective_date in date default null) return number is
4208 l_pc_version number := -1;
4209 l_effective_date date;
4210 l_business_group_id number;
4211 cursor csr_pc_version (p_effective_date in date,
4212 p_business_group_id in number) is
4213 SELECT org_structure_version_id
4214 FROM per_organization_structures pos,
4215 per_org_structure_versions ver
4216 WHERE pos.organization_structure_id = ver.organization_structure_id
4217 AND p_effective_date BETWEEN ver.date_from AND NVL(ver.date_to, p_effective_date)
4218 AND NVL(pos.position_control_structure_flg,'N') = 'Y'
4219 AND pos.business_group_id = p_business_group_id;
4220 begin
4221 if p_business_group_id is null then
4222 l_business_group_id := NVL(hr_general.get_business_group_id,fnd_profile.value('PER_BUSINESS_GROUP_ID'));
4223 else
4224 l_business_group_id := p_business_group_id;
4225 end if;
4226 if p_effective_date is null then
4227 l_effective_date := hr_general.effective_date;
4228 else
4229 l_effective_date := p_effective_date;
4230 end if;
4231 open csr_pc_version(l_effective_date,l_business_group_id);
4232 fetch csr_pc_version into l_pc_version;
4233 close csr_pc_version;
4234 return l_pc_version;
4235 end;
4236
4237 --
4238 --
4239 /* Budgeted Salary check Enhancement procedures */
4240
4241 procedure get_assignment_info(
4242 p_assignment_id in number,
4243 p_effective_date in date,
4244 p_position_id out nocopy number,
4245 p_organization_id out nocopy number) is
4246
4247 --
4248 cursor c_assignment(p_assignment_id number, p_effective_date date) is
4249 select position_id, organization_id
4250 from per_all_assignments_f
4251 where assignment_id = p_assignment_id
4252 and p_effective_date between effective_start_date and effective_end_date;
4253 --
4254 begin
4255 open c_assignment(p_assignment_id, p_effective_date);
4256 fetch c_assignment into p_position_id, p_organization_id;
4257 close c_assignment;
4258 end;
4259
4260
4261 function get_cbr_rule_level(
4262 p_application_id in number,
4263 p_message_name in varchar2,
4264 p_organization_id in number) return varchar2 is
4265 l_rule_level_cd varchar2(1);
4266 begin
4267 pqh_utility.get_message_level_cd
4268 (p_organization_id => p_organization_id,
4269 p_application_id => p_application_id,
4270 p_message_name => p_message_name,
4271 p_rule_level_cd => l_rule_level_cd);
4272
4273 RETURN l_rule_level_cd;
4274 end;
4275
4276
4277 procedure get_position_pc_budget_info(
4278 p_position_id in number,
4279 p_budget_unit_cd in varchar2,
4280 p_effective_date in date,
4281 p_budget_id OUT nocopy number,
4282 p_budget_name OUT nocopy varchar2,
4283 p_business_group_id OUT nocopy number,
4284 p_budget_version_id OUT nocopy number,
4285 p_budget_start_date OUT nocopy date,
4286 p_budget_end_date OUT nocopy date,
4287 p_currency_code OUT nocopy varchar2) is
4288 --
4289 cursor c_pc_budget_info(p_position_id number,
4290 p_budget_unit_cd in varchar2,
4291 p_effective_date date) is
4292 select bgt.budget_id, bgt.budget_name, bgt.business_group_id,
4293 bver.budget_version_id,
4294 bgt.budget_start_date, bgt.budget_end_date, bgt.currency_code
4295 from pqh_budgets bgt,
4296 pqh_budget_versions bver,
4297 pqh_budget_details bdet
4298 where bgt.budget_id = bver.budget_id
4299 and bver.budget_version_id = bdet.budget_version_id
4300 and bdet.position_id = p_position_id
4301 and p_effective_date between bgt.budget_start_date and bgt.budget_end_date
4302 and p_effective_date between bver.date_from and bver.date_to
4303 and bgt.budgeted_entity_cd = 'POSITION'
4304 and bgt.position_control_flag = 'Y'
4305 and (hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit1_id) = p_budget_unit_cd
4306 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit2_id) = p_budget_unit_cd
4307 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit3_id) = p_budget_unit_cd);
4308 --
4309 begin
4310 open c_pc_budget_info(p_position_id, p_budget_unit_cd, p_effective_date);
4311 fetch c_pc_budget_info into
4312 p_budget_id, p_budget_name, p_business_group_id,
4313 p_budget_version_id,
4314 p_budget_start_date, p_budget_end_date, p_currency_code;
4315 close c_pc_budget_info;
4316 end;
4317
4318
4319 function does_appr_sal_proposal_exist(
4320 p_assignment_id number,
4321 p_effective_date date) return boolean is
4322 --
4323 cursor c_appr_sal_prop_exist(p_assignment_id number,
4324 p_effective_date date) is
4325 select 'x'
4326 from dual
4327 where exists (
4328 select null
4329 from per_pay_proposals
4330 where assignment_id = p_assignment_id
4331 and p_effective_date >= change_date
4332 and approved = 'Y'
4333 );
4334 --
4335 l_dummy varchar2(1);
4336 --
4337 begin
4338 open c_appr_sal_prop_exist(p_assignment_id, p_effective_date);
4339 fetch c_appr_sal_prop_exist into l_dummy;
4340 if c_appr_sal_prop_exist%notfound then
4341 return false;
4342 end if;
4343 return true;
4344 end;
4345
4346 procedure get_asg_salary_basis_info(
4347 p_assignment_id number,
4348 p_effective_date date,
4349 p_element_type_id OUT nocopy number,
4350 p_input_value_id OUT nocopy number) is
4351 --
4352 cursor c_pay_basis_info(p_assignment_id number,
4353 p_effective_date date) is
4354 select piv.element_type_id, ppb.input_value_id
4355 from per_all_assignments_f asg,
4356 per_pay_bases ppb,
4357 pay_input_values_f piv
4358 where assignment_id = p_assignment_id
4359 and asg.pay_basis_id = ppb.pay_basis_id
4360 and ppb.input_value_id = piv.input_value_id
4361 and p_effective_date between asg.effective_start_date and asg.effective_end_date
4362 and p_effective_date between piv.effective_start_date and piv.effective_end_date;
4363 --
4364 begin
4365 --
4366 open c_pay_basis_info(p_assignment_id, p_effective_date);
4367 fetch c_pay_basis_info into p_element_type_id, p_input_value_id;
4368 close c_pay_basis_info;
4369 --
4370 end;
4371
4372 function decode_element(p_element_type_id number) return varchar2 is
4373 cursor c_element_name(p_element_type_id number) is
4374 select pettl1.element_name
4375 from pay_element_types_f_tl pettl1
4376 where pettl1.element_type_id = p_element_type_id
4377 and pettl1.language = userenv('LANG');
4378 --
4379 l_element_name pay_element_types_f_tl.element_name%type;
4380 begin
4381 open c_element_name(p_element_type_id);
4382 fetch c_element_name into l_element_name;
4383 close c_element_name;
4384 return l_element_name;
4385 end;
4386
4387 function decode_input_value(p_input_value_id number) return varchar2 is
4388 cursor c_input_value_name(p_input_value_id number) is
4389 select pivtl.name
4390 from pay_input_values_f_tl pivtl
4391 where pivtl.input_value_id = p_input_value_id
4392 and pivtl.language = userenv('LANG');
4393 --
4394 l_input_value_name pay_input_values_f_tl.name%type;
4395 begin
4396 open c_input_value_name(p_input_value_id);
4397 fetch c_input_value_name into l_input_value_name;
4398 close c_input_value_name;
4399 return l_input_value_name;
4400 end;
4401
4402
4403 function is_budget_commt_element(p_budget_id number,
4404 p_element_type_id number) return boolean is
4405 l_check varchar2(10);
4406 begin
4407 hr_utility.set_location('budget id'||p_budget_id,1);
4408 hr_utility.set_location('element type id'||p_element_type_id,2);
4409 select 'X'
4410 into l_check
4411 from pqh_bdgt_cmmtmnt_elmnts
4412 where budget_id = p_budget_id
4413 and element_type_id = p_element_type_id
4414 and actual_commitment_type in ('COMMITMENT','BOTH');
4415
4416 if l_check IS NULL then
4417 return false;
4418 else
4419 return true;
4420 end if;
4421
4422 exception
4423 when no_data_found then
4424 hr_utility.set_location('no data',3);
4425 return false;
4426 when too_many_rows then
4427 return true;
4428 when others then
4429 return false;
4430 end;
4431
4432 procedure chk_position_budget(
4433 p_assignment_id in number,
4434 p_element_type_id in number default null,
4435 p_input_value_id in number default null,
4436 p_effective_date in date,
4437 p_called_from in varchar2, /* valid values 'ASG' or 'SAL' */
4438 p_old_position_id in number default null,
4439 p_new_position_id in number default null
4440 ) is
4441 --
4442 l_pos_budgeted_amt number;
4443 l_pos_actuals_amt number;
4444 l_pos_actual_cmmt_total_amt number;
4445 l_pos_commitment_amt number;
4446 l_pos_reallocated_out_amt number;
4447 l_pos_reallocated_in_amt number;
4448 l_pos_reserved_amt number;
4449 l_pos_under_budgeted_amt number;
4450 --
4451 l_asg_organization_id number;
4452 l_asg_position_id number;
4453 --
4454 l_budget_id number;
4455 l_budget_name pqh_budgets.budget_name%type;
4456 l_business_group_id number;
4457 l_budget_version_id number;
4458 l_budget_start_date date;
4459 l_budget_end_date date;
4460 l_currency_code pqh_budgets.currency_code%type;
4461 --
4462 l_salary_element_type_id number;
4463 l_salary_input_value_id number;
4464 --
4465 l_message_level varchar2(10);
4466 l_proc varchar2(72) := 'pqh_psf_bus.chk_position_budget';
4467 begin
4468 --
4469 hr_utility.set_location(l_proc||'Entering',1);
4470 get_assignment_info(p_assignment_id, p_effective_date, l_asg_position_id, l_asg_organization_id);
4471 --
4472 hr_utility.set_location(l_proc,2);
4473 --
4474 if (l_asg_position_id is null) then
4475 hr_utility.set_location(l_proc,3);
4476 return;
4477 end if;
4478 --
4479 --
4480 if (p_called_from = 'ASG' and p_old_position_id = l_asg_position_id ) then
4481 hr_utility.set_location(l_proc,4);
4482 return;
4483 end if;
4484 --
4485 --
4486 l_message_level := get_cbr_rule_level(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_asg_organization_id);
4487 if (l_message_level not in ('E','W')) then
4488 hr_utility.set_location(l_proc,5);
4489 return;
4490 end if;
4491 --
4492
4493 hr_utility.set_location(l_proc||l_asg_position_id,51);
4494 --
4495 get_position_pc_budget_info(
4496 p_position_id => l_asg_position_id,
4497 p_budget_unit_cd => 'MONEY',
4498 p_effective_date => p_effective_date,
4499 p_budget_id => l_budget_id,
4500 p_budget_name => l_budget_name,
4501 p_business_group_id => l_business_group_id,
4502 p_budget_version_id => l_budget_version_id,
4503 p_budget_start_date => l_budget_start_date,
4504 p_budget_end_date => l_budget_end_date,
4505 p_currency_code => l_currency_code);
4506 if ( l_budget_version_id is null ) then
4507
4508 hr_utility.set_location(l_proc,6);
4509 return;
4510 end if;
4511 hr_utility.set_location(l_proc,61);
4512 --
4513 --
4514
4515 --
4516 --
4517 if ((p_called_from = 'SAL') or
4518 (p_called_from = 'ASG'
4519 and does_appr_sal_proposal_exist(p_assignment_id, p_effective_date))) then
4520 --
4521 --
4522 if p_called_from = 'ASG' then
4523 get_asg_salary_basis_info(p_assignment_id, p_effective_date,
4524 l_salary_element_type_id, l_salary_input_value_id);
4525 elsif (p_called_from = 'SAL') then
4526 l_salary_element_type_id := p_element_type_id;
4527 l_salary_input_value_id := p_input_value_id;
4528 end if;
4529 --
4530 if (not is_budget_commt_element(l_budget_id, l_salary_element_type_id) ) then
4531 pqh_utility.set_message(8302,'PQH_SAL_NOT_COMMT_ELEMENT',l_asg_organization_id);
4532 pqh_utility.set_message_token('ELEMENT', decode_element(l_salary_element_type_id));
4533 pqh_utility.set_message_token('BUDGET', l_budget_name);
4534 pqh_utility.raise_error;
4535 return;
4536 end if;
4537 --
4538 hr_utility.set_location(l_proc,7);
4539 --
4540 end if;
4541 --
4542 --
4543
4544 --
4545 --
4546 hr_utility.set_location(l_proc,71);
4547 pqh_commitment_pkg.refresh_asg_ele_commitments (
4548 p_assignment_id,
4549 p_effective_date,
4550 p_element_type_id,
4551 p_input_value_id);
4552 hr_utility.set_location(l_proc,8);
4553 --
4554 --
4555
4556 --
4557 --
4558 l_pos_budgeted_amt := budgeted_money(l_asg_position_id,p_effective_date);
4559 --
4560 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_money_amounts
4561 (
4562 p_budget_version_id => l_budget_version_id,
4563 p_position_id => l_asg_position_id,
4564 p_start_date => l_budget_start_date,
4565 p_end_date => l_budget_end_date,
4566 p_actual_amount => l_pos_actuals_amt,
4567 p_commitment_amount => l_pos_commitment_amt,
4568 p_total_amount => l_pos_actual_cmmt_total_amt
4569 );
4570 hr_utility.set_location(l_proc,9);
4571 --
4572 --
4573 l_pos_reallocated_out_amt :=
4574 pqh_reallocation_pkg.get_reallocated_money(
4575 p_position_id => l_asg_position_id
4576 ,p_business_group_id => l_business_group_id
4577 ,p_type => 'DNTD'
4578 ,p_start_date => l_budget_start_date
4579 ,p_end_date => l_budget_end_date
4580 ,p_effective_date => p_effective_date);
4581 --
4582 --
4583 l_pos_reallocated_in_amt :=
4584 pqh_reallocation_pkg.get_reallocated_money(
4585 p_position_id => l_asg_position_id
4586 ,p_business_group_id => l_business_group_id
4587 ,p_type => 'RCVD'
4588 ,p_start_date => l_budget_start_date
4589 ,p_end_date => l_budget_end_date
4590 ,p_effective_date => p_effective_date);
4591 --
4592 --
4593 l_pos_reserved_amt :=
4594 pqh_reallocation_pkg.get_reallocated_money(
4595 p_position_id => l_asg_position_id
4596 ,p_business_group_id => l_business_group_id
4597 ,p_type => 'RSRVD'
4598 ,p_start_date => l_budget_start_date
4599 ,p_end_date => l_budget_end_date
4600 ,p_effective_date => p_effective_date);
4601 --
4602 --
4603
4604 --
4605 --
4606 l_pos_under_budgeted_amt := nvl(l_pos_budgeted_amt,0)
4607 - nvl(l_pos_actual_cmmt_total_amt,0)
4608 - nvl(l_pos_reallocated_out_amt,0)
4609 + nvl(l_pos_reallocated_in_amt,0)
4610 - nvl(l_pos_reserved_amt,0);
4611 --
4612 --
4613 hr_utility.set_location(l_proc,10);
4614 --
4615 hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
4616 hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
4617 hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
4618 hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
4619 hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
4620 hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
4621 --
4622 --
4623 if (l_pos_under_budgeted_amt < 0) then
4624 hr_utility.set_location(l_proc||'Leaving with error',11);
4625
4626 pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_asg_organization_id);
4627 -- pqh_utility.set_message_level_cd(l_message_level);
4628 pqh_utility.set_message_token('POSITION',
4629 hr_general.decode_position(l_asg_position_id));
4630 pqh_utility.set_message_token('UNDER_BUDGETED_AMT',
4631 to_char( -l_pos_under_budgeted_amt,
4632 fnd_currency.GET_FORMAT_MASK(nvl(l_currency_code,'USD'),length(trunc(l_pos_under_budgeted_amt))+15)
4633 ));
4634 /*
4635 pqh_utility.set_message_token('BUDGETED',nvl(l_pos_budgeted_amt,0));
4636 pqh_utility.set_message_token('ACTUALS',nvl(l_pos_actuals_amt,0));
4637 pqh_utility.set_message_token('COMMITMENT',nvl(l_pos_commitment_amt,0));
4638 pqh_utility.set_message_token('REALLOC_OUT',nvl(l_pos_reallocated_out_amt,0));
4639 pqh_utility.set_message_token('REALLOC_IN',nvl(l_pos_reallocated_in_amt,0));
4640 pqh_utility.set_message_token('RESERVED',nvl(l_pos_reserved_amt,0));
4641 */
4642 pqh_utility.raise_error;
4643 end if;
4644 --
4645 hr_utility.set_location(l_proc||'Leaving',12);
4646 --
4647 exception
4648 when others then
4649 hr_utility.set_location(substr(sqlerrm,1,120),13);
4650 raise;
4651 end;
4652
4653 end PQH_PSF_BUS;