[Home] [Help]
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;
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);
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: --
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;
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: */
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: --
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: */
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: --
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(
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);
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;
171: end if;
172: --
173: --
174: --
175: hr_utility.set_location('Check FTE validation rule:', 630);
176: --
177: -- Check FTE Validation Rule
178: --
179: --
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: --
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);
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;
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;
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);
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: --
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: --
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 >--------------------|
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;
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;
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
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,
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;
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;
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;
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: --
322: l_default_asg_fte := 1;
323: end if;
324: --
325: if (not l_bgt_lt_abv_fte) and p_rec.assignment_type <> 'A' then -- fix for 10434560
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,
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;
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);
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);
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);
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;
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);
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,
367: p_rec.position_id,
368: p_rec.effective_start_date,
369: l_rec.organization_id,
370: l_default_asg_fte);
371: --hr_utility.set_location('POSITION RESERVED', 114);
372: --pqh_utility.set_message(8302,'PQH_POS_RESERVED',l_rec.organization_id);
373: --pqh_utility.raise_error;
374: else
375: l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
375: l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
376: p_rec.position_id, l_rec.position_type,
377: p_rec.effective_start_date, hr_general.end_of_time, l_default_asg_fte);
378: if l_future_res_date is not null then
379: hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
380: hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
381: pqh_utility.set_message_level_cd('W');
382: pqh_utility.raise_error;
383: end if;
376: p_rec.position_id, l_rec.position_type,
377: p_rec.effective_start_date, hr_general.end_of_time, l_default_asg_fte);
378: if l_future_res_date is not null then
379: hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
380: hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
381: pqh_utility.set_message_level_cd('W');
382: pqh_utility.raise_error;
383: end if;
384: end if;
383: end if;
384: end if;
385: end if;
386: end if;
387: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
388: elsif (l_rec.position_type = 'NONE') then
389: null;
390: end if;
391: --
389: null;
390: end if;
391: --
392: /*
393: hr_utility.set_location('Money Related Rule before chk_pos_budget', 135);
394: l_chk_pos_budget := chk_pos_budget(p_rec.position_id, p_rec.effective_start_date);
395: hr_utility.set_location('Money Related Rule after chk_pos_budget', 136);
396: if not l_chk_pos_budget then
397: hr_utility.set_location('Money Related Rule failed', 140);
391: --
392: /*
393: hr_utility.set_location('Money Related Rule before chk_pos_budget', 135);
394: l_chk_pos_budget := chk_pos_budget(p_rec.position_id, p_rec.effective_start_date);
395: hr_utility.set_location('Money Related Rule after chk_pos_budget', 136);
396: if not l_chk_pos_budget then
397: hr_utility.set_location('Money Related Rule failed', 140);
398: pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_rec.organization_id);
399: pqh_utility.raise_error;
393: hr_utility.set_location('Money Related Rule before chk_pos_budget', 135);
394: l_chk_pos_budget := chk_pos_budget(p_rec.position_id, p_rec.effective_start_date);
395: hr_utility.set_location('Money Related Rule after chk_pos_budget', 136);
396: if not l_chk_pos_budget then
397: hr_utility.set_location('Money Related Rule failed', 140);
398: pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_rec.organization_id);
399: pqh_utility.raise_error;
400: else
401: hr_utility.set_location('Money Related Rule success', 140);
397: hr_utility.set_location('Money Related Rule failed', 140);
398: pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_rec.organization_id);
399: pqh_utility.raise_error;
400: else
401: hr_utility.set_location('Money Related Rule success', 140);
402: end if;
403: */
404: else
405: close c_position;
405: close c_position;
406: end if;
407: end if;
408: --
409: hr_utility.set_location('per_asg_insert_validate End',400);
410: END;
411: --
412: -- ---------------------------------------------------------------------------
413: -- |-----------------< per_asg_bus_update_validate >--------------------|
451: where position_id = p_position_id
452: and effective_start_date > p_effective_date;
453: --
454: BEGIN
455: hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
456: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
457: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
458: hr_utility.set_location('p_effective_date '||p_effective_date, 101);
459: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
452: and effective_start_date > p_effective_date;
453: --
454: BEGIN
455: hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
456: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
457: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
458: hr_utility.set_location('p_effective_date '||p_effective_date, 101);
459: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
460:
453: --
454: BEGIN
455: hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
456: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
457: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
458: hr_utility.set_location('p_effective_date '||p_effective_date, 101);
459: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
460:
461: open c_position;
454: BEGIN
455: hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
456: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
457: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
458: hr_utility.set_location('p_effective_date '||p_effective_date, 101);
459: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
460:
461: open c_position;
462: fetch c_position into l_rec;
455: hr_utility.set_location('Entering Procedure PQH_PSF_BUS ', 100);
456: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 101);
457: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 101);
458: hr_utility.set_location('p_effective_date '||p_effective_date, 101);
459: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 101);
460:
461: open c_position;
462: fetch c_position into l_rec;
463:
511: --
512: --
513: -- Validate whether a SHARED position has FTE greater than the sum of the budgeted FTE's attached to the Position
514: --
515: hr_utility.set_location('Insert Validate: SHARED position has FTE greater than', 130);
516: if p_rec.position_id <> nvl(l_asg.position_id, -999)
517: or p_rec.assignment_type <> l_asg.assignment_type then -- fix for bug#10258636
518: if (l_rec.position_type = 'SHARED') then
519: --
516: if p_rec.position_id <> nvl(l_asg.position_id, -999)
517: or p_rec.assignment_type <> l_asg.assignment_type then -- fix for bug#10258636
518: if (l_rec.position_type = 'SHARED') then
519: --
520: hr_utility.set_location('before l_overlap_dates_present ', 100);
521: l_default_asg_fte := null;
522: --pqh_psf_bus.default_assignment_fte(p_rec.business_group_id);
523: --
524: pqh_psf_bus.CHK_ABV_FTE_GT_POS_BGT_FTE(
534: l_default_asg_fte := 1;
535: end if;
536: --
537: if (not l_bgt_lt_abv_fte) and p_rec.assignment_type <> 'A' then -- fix for 10434560
538: hr_utility.set_location('l_default_asg_fte :'||l_default_asg_fte, 135);
539: pqh_psf_bus.chk_future_pos_asg_fte(
540: p_assignment_id => p_rec.assignment_id,
541: p_position_id => p_rec.position_id,
542: p_validation_start_date => p_validation_start_date,
546: -- End changes for bug 10220040
547: p_default_asg_fte => l_default_asg_fte);
548: end if;
549: --
550: hr_utility.set_location('after chk_future_pos_asg_fte ', 100);
551: --
552: if (l_rec.position_type = 'SHARED') then
553: --
554: hr_utility.set_location('before PQH_FTE_NE_SHARED_POS_FTE_CAP ', 100);
550: hr_utility.set_location('after chk_future_pos_asg_fte ', 100);
551: --
552: if (l_rec.position_type = 'SHARED') then
553: --
554: hr_utility.set_location('before PQH_FTE_NE_SHARED_POS_FTE_CAP ', 100);
555: --
556: l_assignment_fte := pqh_psf_bus.assignment_fte(p_rec.assignment_id, p_validation_start_date);
557: if (l_rec.fte/l_rec.max_persons <> l_assignment_fte) then
558: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_rec.organization_id);
558: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_rec.organization_id);
559: pqh_utility.raise_error;
560: end if;
561: --
562: hr_utility.set_location('after PQH_FTE_NE_SHARED_POS_FTE_CAP ', 100);
563: --
564: end if;
565: --
566: --
573: pqh_utility.set_message(8302,'PQH_POOLED_POS_NOT_OPEN',l_rec.organization_id);
574: pqh_utility.raise_error;
575: end if;
576: elsif l_rec.position_type = 'SINGLE' or l_rec.position_type = 'SHARED' then
577: hr_utility.set_location('SINGLE OR SHARED', 111);
578: -- if pqh_psf_bus.open_status(l_rec.position_id, l_rec.effective_start_date) = 'OPEN' then -- Commented for Bug#13860076
579: if pqh_psf_bus.open_status(l_rec.position_id, p_effective_date) = 'OPEN' then -- Added for Bug#13860076
580: hr_utility.set_location('OPEN', 112);
581: if not l_bgt_lt_abv_fte then
576: elsif l_rec.position_type = 'SINGLE' or l_rec.position_type = 'SHARED' then
577: hr_utility.set_location('SINGLE OR SHARED', 111);
578: -- if pqh_psf_bus.open_status(l_rec.position_id, l_rec.effective_start_date) = 'OPEN' then -- Commented for Bug#13860076
579: if pqh_psf_bus.open_status(l_rec.position_id, p_effective_date) = 'OPEN' then -- Added for Bug#13860076
580: hr_utility.set_location('OPEN', 112);
581: if not l_bgt_lt_abv_fte then
582: hr_utility.set_location('NOT l_bgt_lt_abv_fte ', 113);
583: if chk_reserved_fte(p_rec.assignment_id, p_rec.person_id,
584: p_rec.position_id, l_rec.position_type,
578: -- if pqh_psf_bus.open_status(l_rec.position_id, l_rec.effective_start_date) = 'OPEN' then -- Commented for Bug#13860076
579: if pqh_psf_bus.open_status(l_rec.position_id, p_effective_date) = 'OPEN' then -- Added for Bug#13860076
580: hr_utility.set_location('OPEN', 112);
581: if not l_bgt_lt_abv_fte then
582: hr_utility.set_location('NOT l_bgt_lt_abv_fte ', 113);
583: if chk_reserved_fte(p_rec.assignment_id, p_rec.person_id,
584: p_rec.position_id, l_rec.position_type,
585: p_validation_start_date,
586: p_validation_end_date -- Added for bug 10220040
590: p_rec.position_id,
591: p_validation_start_date,
592: l_rec.organization_id);
593:
594: --hr_utility.set_location('POSITION RESERVED', 114);
595: --pqh_utility.set_message(8302,'PQH_POS_RESERVED',l_rec.organization_id);
596: --pqh_utility.raise_error;
597: else
598: l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
598: l_future_res_date := chk_future_reserved_fte(p_rec.assignment_id, p_rec.person_id,
599: p_rec.position_id, l_rec.position_type,
600: p_validation_start_date, hr_general.end_of_time);
601: if l_future_res_date is not null then
602: hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
603: hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
604: pqh_utility.set_message_level_cd('W');
605: pqh_utility.raise_error;
606: end if;
599: p_rec.position_id, l_rec.position_type,
600: p_validation_start_date, hr_general.end_of_time);
601: if l_future_res_date is not null then
602: hr_utility.set_message(8302,'PQH_POS_FUTURE_RESERVED');
603: hr_utility.set_message_token('FUTURE_RESERVED_DATE', l_future_res_date);
604: pqh_utility.set_message_level_cd('W');
605: pqh_utility.raise_error;
606: end if;
607: end if;
613: end if;
614: else
615: close c_position;
616: end if;
617: hr_utility.set_location('Exiting PQH_PSF_BUS', 130);
618: --
619: END;
620: --
621: -- ---------------------------------------------------------------------------
683: where position_id = p_position_id
684: and p_date between effective_start_date and effective_end_date;
685: --
686: begin
687: hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);
688: l_position_id := p_rec.position_id;
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
685: --
686: begin
687: hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);
688: l_position_id := p_rec.position_id;
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
686: begin
687: hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);
688: l_position_id := p_rec.position_id;
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
694: hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
687: hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);
688: l_position_id := p_rec.position_id;
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
694: hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
695: if l_position_id is null then
688: l_position_id := p_rec.position_id;
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
694: hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
695: if l_position_id is null then
696: open c_position_id(p_rec.assignment_id, p_effective_date);
689: hr_utility.set_location('l_position_id : ' || l_position_id,51);
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
694: hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
695: if l_position_id is null then
696: open c_position_id(p_rec.assignment_id, p_effective_date);
697: fetch c_position_id into l_position_id;
690: hr_utility.set_location('p_assignment_id : ' || p_rec.assignment_id,51);
691: hr_utility.set_location('p_effective_date : ' || p_effective_date,51);
692: hr_utility.set_location('p_validation_start_date : ' || p_validation_start_date,51);
693: hr_utility.set_location('p_validation_end_date : ' || p_validation_end_date,51);
694: hr_utility.set_location('p_datetrack_mode : ' || p_datetrack_mode,51);
695: if l_position_id is null then
696: open c_position_id(p_rec.assignment_id, p_effective_date);
697: fetch c_position_id into l_position_id;
698: close c_position_id;
696: open c_position_id(p_rec.assignment_id, p_effective_date);
697: fetch c_position_id into l_position_id;
698: close c_position_id;
699: end if;
700: hr_utility.set_location('l_position_id : ' || l_position_id,51);
701: --
702: if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
703: for r1 in c_changed_dates(l_position_id, p_validation_start_date, p_validation_end_date)
704: loop
701: --
702: if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
703: for r1 in c_changed_dates(l_position_id, p_validation_start_date, p_validation_end_date)
704: loop
705: hr_utility.set_location('Effective Start Date : ' || r1.effective_start_date,50);
706: --
707: open c_position_fte(l_position_id, r1.effective_start_date);
708: fetch c_position_fte into l_overlap_period,l_fte, l_organization_id;
709: close c_position_fte;
706: --
707: open c_position_fte(l_position_id, r1.effective_start_date);
708: fetch c_position_fte into l_overlap_period,l_fte, l_organization_id;
709: close c_position_fte;
710: hr_utility.set_location('l_overlap_period : ' || l_overlap_period,51);
711: --
712: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
713: p_position_id => l_position_id,
714: p_overlap_period => l_overlap_period,
733: l_sum1 := pqh_psf_bus.sum_assignment_fte(l_position_id, r1.effective_start_date, p_rec.assignment_id);
734: l_sum2 := pqh_psf_bus.assignment_fte(p_rec.assignment_id, r1.effective_start_date);
735: l_sum := l_sum1 + l_sum2;
736: --
737: hr_utility.set_location('l_bdgt '||l_bdgt, 101);
738: hr_utility.set_location('l_fte '||l_fte, 101);
739: hr_utility.set_location('l_sum1'||l_sum1, 101);
740: hr_utility.set_location('l_sum2'||l_sum2, 101);
741: hr_utility.set_location('l_sum '||l_sum, 101);
734: l_sum2 := pqh_psf_bus.assignment_fte(p_rec.assignment_id, r1.effective_start_date);
735: l_sum := l_sum1 + l_sum2;
736: --
737: hr_utility.set_location('l_bdgt '||l_bdgt, 101);
738: hr_utility.set_location('l_fte '||l_fte, 101);
739: hr_utility.set_location('l_sum1'||l_sum1, 101);
740: hr_utility.set_location('l_sum2'||l_sum2, 101);
741: hr_utility.set_location('l_sum '||l_sum, 101);
742:
735: l_sum := l_sum1 + l_sum2;
736: --
737: hr_utility.set_location('l_bdgt '||l_bdgt, 101);
738: hr_utility.set_location('l_fte '||l_fte, 101);
739: hr_utility.set_location('l_sum1'||l_sum1, 101);
740: hr_utility.set_location('l_sum2'||l_sum2, 101);
741: hr_utility.set_location('l_sum '||l_sum, 101);
742:
743: --
736: --
737: hr_utility.set_location('l_bdgt '||l_bdgt, 101);
738: hr_utility.set_location('l_fte '||l_fte, 101);
739: hr_utility.set_location('l_sum1'||l_sum1, 101);
740: hr_utility.set_location('l_sum2'||l_sum2, 101);
741: hr_utility.set_location('l_sum '||l_sum, 101);
742:
743: --
744: if l_bdgt is not null or l_realloc is not null then
737: hr_utility.set_location('l_bdgt '||l_bdgt, 101);
738: hr_utility.set_location('l_fte '||l_fte, 101);
739: hr_utility.set_location('l_sum1'||l_sum1, 101);
740: hr_utility.set_location('l_sum2'||l_sum2, 101);
741: hr_utility.set_location('l_sum '||l_sum, 101);
742:
743: --
744: if l_bdgt is not null or l_realloc is not null then
745: l_bgt_realloc := nvl(l_bdgt,0) + nvl(l_realloc,0);
759: end if;
760: end if;
761: end loop;
762: end if;
763: hr_utility.set_location('Exiting pqh_asg_bus_delete_validate', 400);
764: --
765: end;
766: --
767: -- ---------------------------------------------------------------------------
817: and abv.unit in ('F', 'FTE')
818: and asg.assignment_type in ('E','C'); -- bug 7008697
819:
820: begin
821: hr_utility.set_location('Entering Procedure '||l_proc, 100);
822: hr_utility.set_location('p_unit '||p_unit, 101);
823: hr_utility.set_location('p_assignment_id '||nvl(p_assignment_id,-1), 102);
824: hr_utility.set_location('p_effective_date '||p_effective_date, 102);
825: open c_asg;
818: and asg.assignment_type in ('E','C'); -- bug 7008697
819:
820: begin
821: hr_utility.set_location('Entering Procedure '||l_proc, 100);
822: hr_utility.set_location('p_unit '||p_unit, 101);
823: hr_utility.set_location('p_assignment_id '||nvl(p_assignment_id,-1), 102);
824: hr_utility.set_location('p_effective_date '||p_effective_date, 102);
825: open c_asg;
826: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type ; -- bug 7008697
819:
820: begin
821: hr_utility.set_location('Entering Procedure '||l_proc, 100);
822: hr_utility.set_location('p_unit '||p_unit, 101);
823: hr_utility.set_location('p_assignment_id '||nvl(p_assignment_id,-1), 102);
824: hr_utility.set_location('p_effective_date '||p_effective_date, 102);
825: open c_asg;
826: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type ; -- bug 7008697
827: close c_asg;
820: begin
821: hr_utility.set_location('Entering Procedure '||l_proc, 100);
822: hr_utility.set_location('p_unit '||p_unit, 101);
823: hr_utility.set_location('p_assignment_id '||nvl(p_assignment_id,-1), 102);
824: hr_utility.set_location('p_effective_date '||p_effective_date, 102);
825: open c_asg;
826: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type ; -- bug 7008697
827: close c_asg;
828: --
826: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type ; -- bug 7008697
827: close c_asg;
828: --
829:
830: hr_utility.set_location('l_position_id '||nvl(l_position_id,-1), 103);
831: --
832: IF l_assignment_type IN ('C','E') THEN -- Bug 7008697
833: if p_unit in ('FTE') then
834: hr_utility.set_location('Unit is FTE:'||l_proc, 110);
830: hr_utility.set_location('l_position_id '||nvl(l_position_id,-1), 103);
831: --
832: IF l_assignment_type IN ('C','E') THEN -- Bug 7008697
833: if p_unit in ('FTE') then
834: hr_utility.set_location('Unit is FTE:'||l_proc, 110);
835: open c_positions;
836: fetch c_positions into l_position_id, l_fte, l_max_persons,
837: l_position_type, l_organization_id, l_overlap_period;
838: close c_positions;
836: fetch c_positions into l_position_id, l_fte, l_max_persons,
837: l_position_type, l_organization_id, l_overlap_period;
838: close c_positions;
839: --
840: hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
841: l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
842: --
843: --
844: if l_position_type in ('SINGLE', 'SHARED') then
863: ,p_business_group_id => l_business_group_id
864: );
865: --
866: --
867: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
868: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
869: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
870: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
871: hr_utility.set_location('FTE: p_value:'||p_value, 150);
864: );
865: --
866: --
867: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
868: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
869: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
870: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
871: hr_utility.set_location('FTE: p_value:'||p_value, 150);
872: --
865: --
866: --
867: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
868: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
869: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
870: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
871: hr_utility.set_location('FTE: p_value:'||p_value, 150);
872: --
873: if l_pos_budget_fte is not null or l_realloc is not null then
866: --
867: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
868: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
869: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
870: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
871: hr_utility.set_location('FTE: p_value:'||p_value, 150);
872: --
873: if l_pos_budget_fte is not null or l_realloc is not null then
874: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
867: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
868: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
869: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
870: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
871: hr_utility.set_location('FTE: p_value:'||p_value, 150);
872: --
873: if l_pos_budget_fte is not null or l_realloc is not null then
874: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
875: --
891: if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
892: --
893: -- Check Reserved
894: --
895: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
896: if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
897: hr_utility.set_location('Insert Validate:OPEN', 130);
898: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
899: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
893: -- Check Reserved
894: --
895: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
896: if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
897: hr_utility.set_location('Insert Validate:OPEN', 130);
898: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
899: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
900: if (nvl(p_value,0) > l_available_fte) then
901: pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
895: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
896: if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
897: hr_utility.set_location('Insert Validate:OPEN', 130);
898: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
899: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
900: if (nvl(p_value,0) > l_available_fte) then
901: pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
902: l_position_id,
903: p_effective_date,
907: end if;
908: end if;
909: end if;
910: --
911: hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
912: l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
913: --
914: if (l_position_type = 'SHARED') and (l_fte/l_max_persons <> p_value) then
915: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
916: pqh_utility.raise_error;
917: end if;
918: end if;
919: END IF ; -- IF l_assignment_type IN ('C','E') THEN -- bug 7008697
920: hr_utility.set_location('Exiting Procedure '||l_proc, 200);
921: end;
922: --
923: -- ---------------------------------------------------------------------------
924: -- |----------------------< per_abv_update_validate >-------------------|
983: and ast.per_system_status <> 'TERM_ASSIGN' -- Condition added for Bug 8309035
984: and asg.assignment_type in ('E','C'); -- bug 7008697
985:
986: begin
987: hr_utility.set_location('Entering Procedure '||l_proc, 100);
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
984: and asg.assignment_type in ('E','C'); -- bug 7008697
985:
986: begin
987: hr_utility.set_location('Entering Procedure '||l_proc, 100);
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
992: hr_utility.set_location('p_unit '||p_unit, 100);
985:
986: begin
987: hr_utility.set_location('Entering Procedure '||l_proc, 100);
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
992: hr_utility.set_location('p_unit '||p_unit, 100);
993: if p_unit in ('FTE') then
986: begin
987: hr_utility.set_location('Entering Procedure '||l_proc, 100);
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
992: hr_utility.set_location('p_unit '||p_unit, 100);
993: if p_unit in ('FTE') then
994: open c_asg(p_assignment_id, p_effective_date);
987: hr_utility.set_location('Entering Procedure '||l_proc, 100);
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
992: hr_utility.set_location('p_unit '||p_unit, 100);
993: if p_unit in ('FTE') then
994: open c_asg(p_assignment_id, p_effective_date);
995: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type; -- Bug 7008697
988: hr_utility.set_location('p_effective_date '||p_effective_date, 100);
989: hr_utility.set_location('p_validation_start_date '||p_validation_start_date, 100);
990: hr_utility.set_location('p_validation_end_date '||p_validation_end_date, 100);
991: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode, 100);
992: hr_utility.set_location('p_unit '||p_unit, 100);
993: if p_unit in ('FTE') then
994: open c_asg(p_assignment_id, p_effective_date);
995: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type; -- Bug 7008697
996: close c_asg;
993: if p_unit in ('FTE') then
994: open c_asg(p_assignment_id, p_effective_date);
995: fetch c_asg into l_position_id, l_person_id, l_business_group_id, l_assignment_type; -- Bug 7008697
996: close c_asg;
997: hr_utility.set_location('Unit is FTE:'||l_proc, 110);
998: if l_assignment_type in ('C','E') then -- Bug 7008697
999: open c_positions(p_validation_start_date);
1000: fetch c_positions into l_position_id, l_fte, l_max_persons,
1001: l_position_type, l_organization_id, l_overlap_period;
998: if l_assignment_type in ('C','E') then -- Bug 7008697
999: open c_positions(p_validation_start_date);
1000: fetch c_positions into l_position_id, l_fte, l_max_persons,
1001: l_position_type, l_organization_id, l_overlap_period;
1002: hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
1003: l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
1004: if c_positions%found then
1005: close c_positions;
1006: hr_utility.set_location('c_positions found', 121);
1002: hr_utility.set_location('Position : FTE, Head Count, Pos Type:'||
1003: l_fte||' - '||l_max_persons ||' - '||l_position_type, 120);
1004: if c_positions%found then
1005: close c_positions;
1006: hr_utility.set_location('c_positions found', 121);
1007: --
1008: if l_position_type in ('SINGLE', 'SHARED') then
1009: hr_utility.set_location('pos type SINGLE OR SHARED', 122);
1010: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
1005: close c_positions;
1006: hr_utility.set_location('c_positions found', 121);
1007: --
1008: if l_position_type in ('SINGLE', 'SHARED') then
1009: hr_utility.set_location('pos type SINGLE OR SHARED', 122);
1010: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
1011: p_position_id => l_position_id,
1012: p_overlap_period => l_overlap_period,
1013: p_assignment_start_date => p_effective_date,
1012: p_overlap_period => l_overlap_period,
1013: p_assignment_start_date => p_effective_date,
1014: p_assignment_end_date => p_validation_end_date,
1015: p_assignment_id => p_assignment_id); --added for bug 10220040
1016: hr_utility.set_location('After chk_Overlap_dates', 123);
1017: if not l_overlap_dates_present then
1018: hr_utility.set_location('Overlap_dates not present', 124);
1019: --
1020: --Validate Position Budget values with sum of Assignment Budget Values
1014: p_assignment_end_date => p_validation_end_date,
1015: p_assignment_id => p_assignment_id); --added for bug 10220040
1016: hr_utility.set_location('After chk_Overlap_dates', 123);
1017: if not l_overlap_dates_present then
1018: hr_utility.set_location('Overlap_dates not present', 124);
1019: --
1020: --Validate Position Budget values with sum of Assignment Budget Values
1021: open c_sum_abv(l_position_id,p_assignment_id, p_validation_start_date);
1022: fetch c_sum_abv into l_sum_abv;
1021: open c_sum_abv(l_position_id,p_assignment_id, p_validation_start_date);
1022: fetch c_sum_abv into l_sum_abv;
1023: close c_sum_abv;
1024: --
1025: hr_utility.set_location('Before budgeted fte', 124);
1026: l_pos_budget_fte := budgeted_fte(l_position_id, p_validation_start_date);
1027: --
1028: l_realloc := pqh_reallocation_pkg.get_reallocation(
1029: p_position_id => l_position_id
1035: );
1036: --
1037: --
1038: --
1039: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1040: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1041: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1042: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1043: hr_utility.set_location('FTE: p_value:'||p_value, 150);
1036: --
1037: --
1038: --
1039: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1040: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1041: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1042: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1043: hr_utility.set_location('FTE: p_value:'||p_value, 150);
1044: --
1037: --
1038: --
1039: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1040: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1041: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1042: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1043: hr_utility.set_location('FTE: p_value:'||p_value, 150);
1044: --
1045: if l_pos_budget_fte is not null or l_realloc is not null then
1038: --
1039: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1040: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1041: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1042: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1043: hr_utility.set_location('FTE: p_value:'||p_value, 150);
1044: --
1045: if l_pos_budget_fte is not null or l_realloc is not null then
1046: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
1039: hr_utility.set_location('FTE: l_sum_abv:'||l_sum_abv, 130);
1040: hr_utility.set_location('FTE: l_pos_budget_fte:'||l_pos_budget_fte, 140);
1041: hr_utility.set_location('FTE: l_realloc:'||l_realloc, 143);
1042: hr_utility.set_location('FTE: l_fte:'||l_fte, 145);
1043: hr_utility.set_location('FTE: p_value:'||p_value, 150);
1044: --
1045: if l_pos_budget_fte is not null or l_realloc is not null then
1046: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
1047: --
1044: --
1045: if l_pos_budget_fte is not null or l_realloc is not null then
1046: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
1047: --
1048: hr_utility.set_location('l_pos_budget_fte is null', 151);
1049: if (l_bgt_realloc < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
1050: hr_utility.set_location('PQH_SUM_ABV_FTE_GT_POS_BGT_FTE', 152);
1051: l_abv_gt_fte := true;
1052: ---Position Budget FTE is less than the sum of the assignment budget FTE
1046: l_bgt_realloc := nvl(l_pos_budget_fte,0) + nvl(l_realloc,0);
1047: --
1048: hr_utility.set_location('l_pos_budget_fte is null', 151);
1049: if (l_bgt_realloc < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
1050: hr_utility.set_location('PQH_SUM_ABV_FTE_GT_POS_BGT_FTE', 152);
1051: l_abv_gt_fte := true;
1052: ---Position Budget FTE is less than the sum of the assignment budget FTE
1053: pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_BGT_FTE' ,l_organization_id);
1054: pqh_utility.raise_error;
1054: pqh_utility.raise_error;
1055: end if;
1056: else
1057: if (l_fte < nvl(l_sum_abv,0)+ nvl(p_value,0)) then
1058: hr_utility.set_location('PQH_SUM_ABV_FTE_GT_POS_FTE :'||l_organization_id, 153);
1059: l_abv_gt_fte := true;
1060: ---Position FTE is less than the sum of the assignment budget FTE
1061: pqh_utility.set_message(8302,'PQH_SUM_ABV_FTE_GT_POS_FTE',l_organization_id);
1062: pqh_utility.raise_error;
1062: pqh_utility.raise_error;
1063: end if;
1064: end if;
1065: --
1066: hr_utility.set_location('Before check reserved', 154);
1067: --
1068: -- Check Reserved
1069: --
1070: if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
1067: --
1068: -- Check Reserved
1069: --
1070: if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
1071: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
1072: --if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
1073: hr_utility.set_location('Insert Validate:OPEN', 130);
1074: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
1075: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0);
1069: --
1070: if (not l_abv_gt_fte) and (l_position_type = 'SHARED') then
1071: hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);
1072: --if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
1073: hr_utility.set_location('Insert Validate:OPEN', 130);
1074: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
1075: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0);
1076: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
1077: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 133);
1072: --if pqh_psf_bus.open_status(l_position_id, p_effective_date) = 'OPEN' then
1073: hr_utility.set_location('Insert Validate:OPEN', 130);
1074: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
1075: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0);
1076: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
1077: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 133);
1078: l_temp := l_available_fte + l_assignment_fte ;
1079: if (nvl(p_value,0) > l_temp ) then
1080: pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
1073: hr_utility.set_location('Insert Validate:OPEN', 130);
1074: l_available_fte := pqh_psf_bus.available_fte(l_person_id, l_position_id, p_effective_date);
1075: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0);
1076: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
1077: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 133);
1078: l_temp := l_available_fte + l_assignment_fte ;
1079: if (nvl(p_value,0) > l_temp ) then
1080: pqh_psf_bus.reserved_error(p_assignment_id, l_person_id,
1081: l_position_id,
1087: end if;
1088: end if;
1089: end if;
1090: --
1091: hr_utility.set_location('Before fte capacity', 155);
1092: --
1093: if (l_position_type = 'SHARED') and (l_fte/l_max_persons <> p_value) then
1094: hr_utility.set_location('PQH_FTE_NE_SHARED_POS_FTE_CAP', 156);
1095: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
1090: --
1091: hr_utility.set_location('Before fte capacity', 155);
1092: --
1093: if (l_position_type = 'SHARED') and (l_fte/l_max_persons <> p_value) then
1094: hr_utility.set_location('PQH_FTE_NE_SHARED_POS_FTE_CAP', 156);
1095: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
1096: pqh_utility.raise_error;
1097: end if;
1098: --
1095: pqh_utility.set_message(8302,'PQH_FTE_NE_SHARED_POS_FTE_CAP',l_organization_id);
1096: pqh_utility.raise_error;
1097: end if;
1098: --
1099: hr_utility.set_location('After fte capacity', 157);
1100: --
1101: end if;
1102: end if; -- if l_assignment_type in ('C','E') then -- bug 7008697
1103: end if; -- p_unit in ('FTE') then
1100: --
1101: end if;
1102: end if; -- if l_assignment_type in ('C','E') then -- bug 7008697
1103: end if; -- p_unit in ('FTE') then
1104: hr_utility.set_location('Exiting Procedure '||l_proc, 200);
1105: end;
1106: --
1107: --
1108: -- ---------------------------------------------------------------------------
1255: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
1256: if p_ex_assignment_id is null then
1257: open c_budgeted_fte(p_person_id, p_position_id);
1258: fetch c_budgeted_fte into l_assignment_fte;
1259: hr_utility.set_location('l_person_id : '||l_person_id, 630);
1260: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1261: close c_budgeted_fte;
1262: else
1263: open c_budgeted_fte_ex_asg(p_person_id, p_position_id, p_ex_assignment_id);
1256: if p_ex_assignment_id is null then
1257: open c_budgeted_fte(p_person_id, p_position_id);
1258: fetch c_budgeted_fte into l_assignment_fte;
1259: hr_utility.set_location('l_person_id : '||l_person_id, 630);
1260: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1261: close c_budgeted_fte;
1262: else
1263: open c_budgeted_fte_ex_asg(p_person_id, p_position_id, p_ex_assignment_id);
1264: fetch c_budgeted_fte_ex_asg into l_assignment_fte;
1261: close c_budgeted_fte;
1262: else
1263: open c_budgeted_fte_ex_asg(p_person_id, p_position_id, p_ex_assignment_id);
1264: fetch c_budgeted_fte_ex_asg into l_assignment_fte;
1265: hr_utility.set_location('l_person_id : '||l_person_id, 630);
1266: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1267: close c_budgeted_fte_ex_asg;
1268: end if;
1269: end if;
1262: else
1263: open c_budgeted_fte_ex_asg(p_person_id, p_position_id, p_ex_assignment_id);
1264: fetch c_budgeted_fte_ex_asg into l_assignment_fte;
1265: hr_utility.set_location('l_person_id : '||l_person_id, 630);
1266: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
1267: close c_budgeted_fte_ex_asg;
1268: end if;
1269: end if;
1270: return(l_assignment_fte);
1401: l_availability_status_id,l_business_group_id, l_position_type, l_pos_effective_date;
1402: close c_positions;
1403: l_availability_status :=
1404: hr_psf_shd.get_availability_status(l_availability_status_id,l_business_group_id);
1405: hr_utility.set_location('Open Status- l_availability_status:'||l_availability_status,
1406: 310);
1407: hr_utility.set_location('Open Status- l_position_type:'||l_position_type,
1408: 320);
1409: l_funded_status := funded_status(p_position_id);
1403: l_availability_status :=
1404: hr_psf_shd.get_availability_status(l_availability_status_id,l_business_group_id);
1405: hr_utility.set_location('Open Status- l_availability_status:'||l_availability_status,
1406: 310);
1407: hr_utility.set_location('Open Status- l_position_type:'||l_position_type,
1408: 320);
1409: l_funded_status := funded_status(p_position_id);
1410: --
1411: hr_utility.set_location('Open Status- l_funded_status:'||l_funded_status,
1407: hr_utility.set_location('Open Status- l_position_type:'||l_position_type,
1408: 320);
1409: l_funded_status := funded_status(p_position_id);
1410: --
1411: hr_utility.set_location('Open Status- l_funded_status:'||l_funded_status,
1412: 320);
1413:
1414: if (l_position_type in ( 'SINGLE', 'SHARED' )) then
1415: l_vacancy_status:= vacancy_status(p_position_id, p_effective_date);
1412: 320);
1413:
1414: if (l_position_type in ( 'SINGLE', 'SHARED' )) then
1415: l_vacancy_status:= vacancy_status(p_position_id, p_effective_date);
1416: hr_utility.set_location('Open Status- l_vacancy_status:'||l_vacancy_status,
1417: 320);
1418: if (l_pos_effective_date <= p_effective_date)
1419: and ( l_availability_status = 'ACTIVE' )
1420: and ( l_funded_status = 'Y')
1431: else
1432: l_open_status := 'NOT_OPEN';
1433: end if;
1434: end if;
1435: hr_utility.set_location('Open Status- l_open_status:'||l_open_status,
1436: 330);
1437: /*
1438: reserved_status(p_position_id, l_reserved_status, l_resrv_start_date, l_resrv_end_date, l_resrv_person_id, l_fte_reserved);
1439: if (l_reserved_status in ( 'NEW_HIRE', 'MANAGEMENT_DISCRETION'))
1518: where position_id = p_position_id
1519: and information_type = 'PER_RESERVED';
1520:
1521: begin
1522: hr_utility.set_location('inside reserved_status',10);
1523: open c1;
1524: fetch c1 into p_start_date, p_end_date, l_person_id, l_fte_reserved, l_reserved_status;
1525: close c1;
1526: hr_utility.set_location('inside reserved_status',20);
1522: hr_utility.set_location('inside reserved_status',10);
1523: open c1;
1524: fetch c1 into p_start_date, p_end_date, l_person_id, l_fte_reserved, l_reserved_status;
1525: close c1;
1526: hr_utility.set_location('inside reserved_status',20);
1527: p_reserved_status := l_reserved_status;
1528: p_person_id := l_person_id;
1529: p_fte_reserved := l_fte_reserved;
1530: exception when others then
1532: p_start_date := null;
1533: p_end_date := null;
1534: p_person_id := null;
1535: p_fte_reserved := null;
1536: hr_utility.set_location('inside reserved_status. Exception!',30);
1537: raise;
1538: end reserved_status;
1539:
1540: function chk_reserved(p_position_id number) return boolean is
1613: end if;
1614: close C_ASSIGNMENTS;
1615: return(l_vacancy_status);
1616: elsif (l_position_type = 'SHARED') then
1617: hr_utility.set_location('Shared',101);
1618: open C_ASSIGNMENTS;
1619: fetch C_ASSIGNMENTS into l_assignment_id;
1620: if (C_ASSIGNMENTS%NOTFOUND) then
1621: close C_ASSIGNMENTS;
1625: close C_ASSIGNMENTS;
1626: l_vacancy_status := 'FILLED';
1627: l_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
1628: l_sum_asg_fte := sum_assignment_fte(p_position_id, p_effective_date);
1629: hr_utility.set_location('l_fte' || l_fte,102);
1630: hr_utility.set_location('sum_asg_fte :' || l_sum_asg_fte,102);
1631: if l_fte is not null then
1632: if nvl(l_fte,0) > nvl(l_sum_asg_fte,0) then
1633: /*if nvl(l_fte,0) >
1626: l_vacancy_status := 'FILLED';
1627: l_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
1628: l_sum_asg_fte := sum_assignment_fte(p_position_id, p_effective_date);
1629: hr_utility.set_location('l_fte' || l_fte,102);
1630: hr_utility.set_location('sum_asg_fte :' || l_sum_asg_fte,102);
1631: if l_fte is not null then
1632: if nvl(l_fte,0) > nvl(l_sum_asg_fte,0) then
1633: /*if nvl(l_fte,0) >
1634: pqh_bdgt_actual_cmmtmnt_pkg.get_pos_budget_values(
1812: close csr_is_apl_asg;
1813:
1814: if l_asg_type in ('E','C') then
1815: --
1816: hr_utility.set_location( '*** entering application assignment check',555);
1817: --
1818: -- Changes end for the bug 13571263
1819: --
1820: hr_utility.set_location('Entering: PQH_PSF_BUS.chk_overlap_dates', 10);
1816: hr_utility.set_location( '*** entering application assignment check',555);
1817: --
1818: -- Changes end for the bug 13571263
1819: --
1820: hr_utility.set_location('Entering: PQH_PSF_BUS.chk_overlap_dates', 10);
1821:
1822: if p_overlap_period is not null
1823: and p_position_id is not null and p_assignment_start_date is not null then
1824:
1825: open c2;
1826: -- Start changes for bug 10220040
1827: -- fetch c2 into l_dummy;
1828:
1829: hr_utility.set_location('p_assignment_start_date'||p_assignment_start_date, 20);
1830: hr_utility.set_location('p_assignment_end_date'||p_assignment_end_date, 20);
1831:
1832: fetch c2 into l_ol_effective_start_date, l_ol_effective_end_date;
1833: close c2;
1826: -- Start changes for bug 10220040
1827: -- fetch c2 into l_dummy;
1828:
1829: hr_utility.set_location('p_assignment_start_date'||p_assignment_start_date, 20);
1830: hr_utility.set_location('p_assignment_end_date'||p_assignment_end_date, 20);
1831:
1832: fetch c2 into l_ol_effective_start_date, l_ol_effective_end_date;
1833: close c2;
1834: if l_ol_effective_start_date is not null then --c2
1831:
1832: fetch c2 into l_ol_effective_start_date, l_ol_effective_end_date;
1833: close c2;
1834: if l_ol_effective_start_date is not null then --c2
1835: hr_utility.set_location('Records found for cursor c2 ', 30);
1836: hr_utility.set_location('l_ol_effective_start_date'||l_ol_effective_start_date, 30);
1837: hr_utility.set_location('l_ol_effective_end_date'||l_ol_effective_end_date, 30);
1838:
1839: open c_position_type;
1832: fetch c2 into l_ol_effective_start_date, l_ol_effective_end_date;
1833: close c2;
1834: if l_ol_effective_start_date is not null then --c2
1835: hr_utility.set_location('Records found for cursor c2 ', 30);
1836: hr_utility.set_location('l_ol_effective_start_date'||l_ol_effective_start_date, 30);
1837: hr_utility.set_location('l_ol_effective_end_date'||l_ol_effective_end_date, 30);
1838:
1839: open c_position_type;
1840: fetch c_position_type into l_position_type;
1833: close c2;
1834: if l_ol_effective_start_date is not null then --c2
1835: hr_utility.set_location('Records found for cursor c2 ', 30);
1836: hr_utility.set_location('l_ol_effective_start_date'||l_ol_effective_start_date, 30);
1837: hr_utility.set_location('l_ol_effective_end_date'||l_ol_effective_end_date, 30);
1838:
1839: open c_position_type;
1840: fetch c_position_type into l_position_type;
1841: close c_position_type;
1848: fetch c3 into l_asg_number, l_asg_effective_start_date, l_asg_effective_end_date;
1849: close c3;
1850:
1851: if l_asg_effective_end_date is not null then --c4
1852: hr_utility.set_location('Records found for cursor c3 ', 35);
1853: hr_utility.set_location('l_asg_effective_start_date'||l_asg_effective_start_date, 35);
1854: hr_utility.set_location('l_asg_effective_end_date'||l_asg_effective_end_date, 35);
1855:
1856: if p_assignment_end_date > l_ol_effective_end_date and
1849: close c3;
1850:
1851: if l_asg_effective_end_date is not null then --c4
1852: hr_utility.set_location('Records found for cursor c3 ', 35);
1853: hr_utility.set_location('l_asg_effective_start_date'||l_asg_effective_start_date, 35);
1854: hr_utility.set_location('l_asg_effective_end_date'||l_asg_effective_end_date, 35);
1855:
1856: if p_assignment_end_date > l_ol_effective_end_date and
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1850:
1851: if l_asg_effective_end_date is not null then --c4
1852: hr_utility.set_location('Records found for cursor c3 ', 35);
1853: hr_utility.set_location('l_asg_effective_start_date'||l_asg_effective_start_date, 35);
1854: hr_utility.set_location('l_asg_effective_end_date'||l_asg_effective_end_date, 35);
1855:
1856: if p_assignment_end_date > l_ol_effective_end_date and
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1854: hr_utility.set_location('l_asg_effective_end_date'||l_asg_effective_end_date, 35);
1855:
1856: if p_assignment_end_date > l_ol_effective_end_date and
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1855:
1856: if p_assignment_end_date > l_ol_effective_end_date and
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1856: if p_assignment_end_date > l_ol_effective_end_date and
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1864: hr_utility.raise_error;
1857: p_assignment_start_date < l_asg_effective_end_date then--c5
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1864: hr_utility.raise_error;
1865: else
1858: hr_utility.set_message(800,'HR_449891_OTHER_ASG_EXISTS');
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1864: hr_utility.raise_error;
1865: else
1866: return (true);
1859: hr_utility.set_message_token('ASG_NUM',l_asg_number);
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1864: hr_utility.raise_error;
1865: else
1866: return (true);
1867: end if;
1860: hr_utility.set_message_token('OL_START_DATE',l_ol_effective_start_date);
1861: hr_utility.set_message_token('OL_END_DATE',l_ol_effective_end_date);
1862: hr_utility.set_message_token('ASG_START_DATE',l_asg_effective_start_date);
1863: hr_utility.set_message_token('ASG_END_DATE',l_asg_effective_end_date);
1864: hr_utility.raise_error;
1865: else
1866: return (true);
1867: end if;
1868: else
1871: else
1872: return(true);
1873: end if; --c3
1874: else
1875: hr_utility.set_location('No Records found for cursor c2 ', 40);
1876: return(false);
1877: end if; -- c2
1878:
1879: /*if c2%found then
1886: close c2; */
1887: -- End changes for bug 10220040
1888: end if;
1889:
1890: hr_utility.set_location('Leaving: PQH_PSF_BUS.chk_overlap_dates', 100);
1891:
1892: --
1893: -- Changes start for the bug 13571263
1894: --
1891:
1892: --
1893: -- Changes start for the bug 13571263
1894: --
1895: hr_utility.set_location('*** leaving application assignment check',666);
1896: --
1897: end if;
1898: -- Changes end for the bug 13571263
1899: --
2202: and grade_id = p_position_grade_id;
2203: begin
2204: if (p_position_grade_id is not null) then
2205: open c1;
2206: hr_utility.set_location('Entering:'||'chk_position_job_grade', 10);
2207: fetch c1 into l_dummy;
2208: if c1%notfound then
2209: close c1;
2210: return(false);
2326: open c_bus_grp_id(p_position_id);
2327: fetch c_bus_grp_id into l_business_group_id;
2328: close c_bus_grp_id;
2329: --
2330: hr_utility.set_location('l_business_group_id:' || l_business_group_id, 550
2331: );
2332:
2333: select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
2334: into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
2342: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'FTE'
2343: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'FTE'
2344: );
2345: --
2346: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2347: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2348: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2349: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2350: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2343: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'FTE'
2344: );
2345: --
2346: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2347: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2348: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2349: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2350: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2351: --
2344: );
2345: --
2346: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2347: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2348: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2349: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2350: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2351: --
2352: open c1(l_budget_unit1_id);
2345: --
2346: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2347: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2348: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2349: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2350: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2351: --
2352: open c1(l_budget_unit1_id);
2353: fetch c1 into l_unit1_name;
2346: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2347: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2348: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2349: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2350: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2351: --
2352: open c1(l_budget_unit1_id);
2353: fetch c1 into l_unit1_name;
2354: close c1;
2357: close c1;
2358: open c1(l_budget_unit3_id);
2359: fetch c1 into l_unit3_name;
2360: close c1;
2361: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2362: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2363: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2364: exception
2365: when others then
2358: open c1(l_budget_unit3_id);
2359: fetch c1 into l_unit3_name;
2360: close c1;
2361: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2362: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2363: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2364: exception
2365: when others then
2366: hr_utility.set_location('Error: ' || SQLERRM, 602);
2359: fetch c1 into l_unit3_name;
2360: close c1;
2361: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2362: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2363: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2364: exception
2365: when others then
2366: hr_utility.set_location('Error: ' || SQLERRM, 602);
2367: return l_budgeted_fte;
2362: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2363: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2364: exception
2365: when others then
2366: hr_utility.set_location('Error: ' || SQLERRM, 602);
2367: return l_budgeted_fte;
2368: end;
2369: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2370: for i in c2(l_budget_id) loop
2365: when others then
2366: hr_utility.set_location('Error: ' || SQLERRM, 602);
2367: return l_budgeted_fte;
2368: end;
2369: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2370: for i in c2(l_budget_id) loop
2371: -- row corresponding to the position is picked up
2372: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2373: --
2368: end;
2369: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2370: for i in c2(l_budget_id) loop
2371: -- row corresponding to the position is picked up
2372: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2373: --
2374: for j in c3(i.budget_detail_id) loop
2375: hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2376: if l_unit1_name ='FTE' then
2371: -- row corresponding to the position is picked up
2372: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2373: --
2374: for j in c3(i.budget_detail_id) loop
2375: hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2376: if l_unit1_name ='FTE' then
2377: l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit1_value,0);
2378: elsif l_unit2_name ='FTE' then
2379: l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit2_value,0);
2381: l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit3_value,0);
2382: end if;
2383: end loop;
2384: end loop;
2385: hr_utility.set_location('l_budgeted_fte:' || l_budgeted_fte, 605);
2386: return l_budgeted_fte;
2387: end;
2388: --
2389: -- Function to calculate position budgeted FTE/Headcount
2429:
2430: Begin
2431: for l_date in c_date
2432: loop
2433: hr_utility.set_location('get_position_budgeted_fte' , 500);
2434: hr_utility.set_location('p_start_date' || p_start_date, 501);
2435: hr_utility.set_location('p_end_date' || p_end_date, 502);
2436: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2437:
2430: Begin
2431: for l_date in c_date
2432: loop
2433: hr_utility.set_location('get_position_budgeted_fte' , 500);
2434: hr_utility.set_location('p_start_date' || p_start_date, 501);
2435: hr_utility.set_location('p_end_date' || p_end_date, 502);
2436: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2437:
2438: l_budgeted_fte := budgeted_fte(p_position_id => p_position_id
2431: for l_date in c_date
2432: loop
2433: hr_utility.set_location('get_position_budgeted_fte' , 500);
2434: hr_utility.set_location('p_start_date' || p_start_date, 501);
2435: hr_utility.set_location('p_end_date' || p_end_date, 502);
2436: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2437:
2438: l_budgeted_fte := budgeted_fte(p_position_id => p_position_id
2439: ,p_budget_entity => p_budget_entity
2432: loop
2433: hr_utility.set_location('get_position_budgeted_fte' , 500);
2434: hr_utility.set_location('p_start_date' || p_start_date, 501);
2435: hr_utility.set_location('p_end_date' || p_end_date, 502);
2436: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2437:
2438: l_budgeted_fte := budgeted_fte(p_position_id => p_position_id
2439: ,p_budget_entity => p_budget_entity
2440: ,p_effective_date => l_date.start_date
2444: p_budgeted_fte_date := l_date.start_date;
2445: return (l_budgeted_fte);
2446: end if;
2447: end loop;
2448: hr_utility.set_location('get_position_budgeted_fte '
2449: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2450: hr_utility.set_location('get_position_budgeted_fte '
2451: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2452: return (l_budgeted_fte);
2446: end if;
2447: end loop;
2448: hr_utility.set_location('get_position_budgeted_fte '
2449: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2450: hr_utility.set_location('get_position_budgeted_fte '
2451: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2452: return (l_budgeted_fte);
2453: exception when others then
2454: p_budgeted_fte_date := null;
2499: l_budgeted_fte number;
2500: Begin
2501: for l_date in c_date
2502: loop
2503: hr_utility.set_location('get_job_budgeted_fte ', 500);
2504: hr_utility.set_location('p_start_date' || p_start_date, 501);
2505: hr_utility.set_location('p_end_date' || p_end_date, 502);
2506: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2507:
2500: Begin
2501: for l_date in c_date
2502: loop
2503: hr_utility.set_location('get_job_budgeted_fte ', 500);
2504: hr_utility.set_location('p_start_date' || p_start_date, 501);
2505: hr_utility.set_location('p_end_date' || p_end_date, 502);
2506: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2507:
2508: l_budgeted_fte := budgeted_fte(
2501: for l_date in c_date
2502: loop
2503: hr_utility.set_location('get_job_budgeted_fte ', 500);
2504: hr_utility.set_location('p_start_date' || p_start_date, 501);
2505: hr_utility.set_location('p_end_date' || p_end_date, 502);
2506: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2507:
2508: l_budgeted_fte := budgeted_fte(
2509: p_job_id => p_job_id
2502: loop
2503: hr_utility.set_location('get_job_budgeted_fte ', 500);
2504: hr_utility.set_location('p_start_date' || p_start_date, 501);
2505: hr_utility.set_location('p_end_date' || p_end_date, 502);
2506: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2507:
2508: l_budgeted_fte := budgeted_fte(
2509: p_job_id => p_job_id
2510: ,p_budget_entity => p_budget_entity
2515: p_budgeted_fte_date := l_date.start_date;
2516: return (l_budgeted_fte);
2517: end if;
2518: end loop;
2519: hr_utility.set_location('get_job_budgeted_fte '||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2520: hr_utility.set_location('get_job_budgeted_fte '||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2521: return (l_budgeted_fte);
2522: exception when others then
2523: p_budgeted_fte_date := null;
2516: return (l_budgeted_fte);
2517: end if;
2518: end loop;
2519: hr_utility.set_location('get_job_budgeted_fte '||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2520: hr_utility.set_location('get_job_budgeted_fte '||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2521: return (l_budgeted_fte);
2522: exception when others then
2523: p_budgeted_fte_date := null;
2524: raise;
2569: l_budgeted_fte number;
2570: Begin
2571: for l_date in c_date
2572: loop
2573: hr_utility.set_location('get_org_budgeted_fte ', 500);
2574: hr_utility.set_location('p_start_date' || p_start_date, 501);
2575: hr_utility.set_location('p_end_date' || p_end_date, 502);
2576: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2577:
2570: Begin
2571: for l_date in c_date
2572: loop
2573: hr_utility.set_location('get_org_budgeted_fte ', 500);
2574: hr_utility.set_location('p_start_date' || p_start_date, 501);
2575: hr_utility.set_location('p_end_date' || p_end_date, 502);
2576: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2577:
2578: l_budgeted_fte := budgeted_fte(
2571: for l_date in c_date
2572: loop
2573: hr_utility.set_location('get_org_budgeted_fte ', 500);
2574: hr_utility.set_location('p_start_date' || p_start_date, 501);
2575: hr_utility.set_location('p_end_date' || p_end_date, 502);
2576: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2577:
2578: l_budgeted_fte := budgeted_fte(
2579: p_organization_id => p_organization_id
2572: loop
2573: hr_utility.set_location('get_org_budgeted_fte ', 500);
2574: hr_utility.set_location('p_start_date' || p_start_date, 501);
2575: hr_utility.set_location('p_end_date' || p_end_date, 502);
2576: hr_utility.set_location('p_effective_date' || l_date.start_date, 503);
2577:
2578: l_budgeted_fte := budgeted_fte(
2579: p_organization_id => p_organization_id
2580: ,p_budget_entity => p_budget_entity
2585: p_budgeted_fte_date := l_date.start_date;
2586: return (l_budgeted_fte);
2587: end if;
2588: end loop;
2589: hr_utility.set_location('get_org_budgeted_fte '
2590: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2591: hr_utility.set_location('get_org_budgeted_fte '
2592: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2593: return (l_budgeted_fte);
2587: end if;
2588: end loop;
2589: hr_utility.set_location('get_org_budgeted_fte '
2590: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2591: hr_utility.set_location('get_org_budgeted_fte '
2592: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2593: return (l_budgeted_fte);
2594: exception when others then
2595: p_budgeted_fte_date := null;
2640: l_budgeted_fte number;
2641: Begin
2642: for l_date in c_date
2643: loop
2644: hr_utility.set_location('p_start_date' || p_start_date, 500);
2645: hr_utility.set_location('p_end_date' || p_end_date, 500);
2646: hr_utility.set_location('p_effective_date' || l_date.start_date, 500);
2647:
2648: l_budgeted_fte := budgeted_fte(
2641: Begin
2642: for l_date in c_date
2643: loop
2644: hr_utility.set_location('p_start_date' || p_start_date, 500);
2645: hr_utility.set_location('p_end_date' || p_end_date, 500);
2646: hr_utility.set_location('p_effective_date' || l_date.start_date, 500);
2647:
2648: l_budgeted_fte := budgeted_fte(
2649: p_grade_id => p_grade_id
2642: for l_date in c_date
2643: loop
2644: hr_utility.set_location('p_start_date' || p_start_date, 500);
2645: hr_utility.set_location('p_end_date' || p_end_date, 500);
2646: hr_utility.set_location('p_effective_date' || l_date.start_date, 500);
2647:
2648: l_budgeted_fte := budgeted_fte(
2649: p_grade_id => p_grade_id
2650: ,p_budget_entity => p_budget_entity
2655: p_budgeted_fte_date := l_date.start_date;
2656: return (l_budgeted_fte);
2657: end if;
2658: end loop;
2659: hr_utility.set_location('get_grade_budgeted_fte '
2660: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2661: hr_utility.set_location('get_grade_budgeted_fte '
2662: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2663: return (l_budgeted_fte);
2657: end if;
2658: end loop;
2659: hr_utility.set_location('get_grade_budgeted_fte '
2660: ||'l_budgeted_fte: ' || l_budgeted_fte, 605);
2661: hr_utility.set_location('get_grade_budgeted_fte '
2662: ||'p_budgeted_fte_date: ' || p_budgeted_fte_date, 606);
2663: return (l_budgeted_fte);
2664: exception when others then
2665: p_budgeted_fte_date := null;
2795: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_unit_of_measure
2796: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure
2797: );
2798: --
2799: hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2796: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure
2797: );
2798: --
2799: hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2804: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2797: );
2798: --
2799: hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2804: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2805: --
2798: --
2799: hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2804: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2805: --
2806: open c1(l_budget_unit1_id);
2799: hr_utility.set_location('p_effective_date' || p_effective_date, 600);
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2804: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2805: --
2806: open c1(l_budget_unit1_id);
2807: fetch c1 into l_unit1_name;
2800: hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
2801: hr_utility.set_location('l_calendar:' || l_calendar, 600);
2802: hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
2803: hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
2804: hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
2805: --
2806: open c1(l_budget_unit1_id);
2807: fetch c1 into l_unit1_name;
2808: close c1;
2811: close c1;
2812: open c1(l_budget_unit3_id);
2813: fetch c1 into l_unit3_name;
2814: close c1;
2815: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2816: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2817: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2818: exception
2819: when others then
2812: open c1(l_budget_unit3_id);
2813: fetch c1 into l_unit3_name;
2814: close c1;
2815: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2816: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2817: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2818: exception
2819: when others then
2820: hr_utility.set_location('Error: ' || SQLERRM, 602);
2813: fetch c1 into l_unit3_name;
2814: close c1;
2815: hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
2816: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2817: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2818: exception
2819: when others then
2820: hr_utility.set_location('Error: ' || SQLERRM, 602);
2821: return l_budgeted_fte;
2816: hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
2817: hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
2818: exception
2819: when others then
2820: hr_utility.set_location('Error: ' || SQLERRM, 602);
2821: return l_budgeted_fte;
2822: end;
2823: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2824: for i in c2(l_budget_id) loop
2819: when others then
2820: hr_utility.set_location('Error: ' || SQLERRM, 602);
2821: return l_budgeted_fte;
2822: end;
2823: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2824: for i in c2(l_budget_id) loop
2825: -- row corresponding to the position is picked up
2826: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2827: --
2822: end;
2823: hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
2824: for i in c2(l_budget_id) loop
2825: -- row corresponding to the position is picked up
2826: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2827: --
2828: for j in c3(i.budget_detail_id) loop
2829: hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2830: if l_unit1_name = p_unit_of_measure then
2825: -- row corresponding to the position is picked up
2826: hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
2827: --
2828: for j in c3(i.budget_detail_id) loop
2829: hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
2830: if l_unit1_name = p_unit_of_measure then
2831: l_budgeted_fte := nvl(l_budgeted_fte,0) + nvl(j.budget_unit1_value,0);
2832:
2833: elsif l_unit2_name = p_unit_of_measure then
2838:
2839: end if;
2840: end loop;
2841: end loop;
2842: hr_utility.set_location('l_budgeted_fte:' || l_budgeted_fte, 605);
2843: return l_budgeted_fte;
2844: end;
2845: --
2846: --
2871: where p_effective_date between fnd_date.canonical_to_date(poei_information3)
2872: and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
2873:
2874: begin
2875: hr_utility.set_location('inside reserved_fte',10);
2876: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
2877: hr_utility.set_location('inside reserved_fte',20);
2878: open c1(p_person_id, p_position_id, p_effective_date);
2879: fetch c1 into l_fte;
2873:
2874: begin
2875: hr_utility.set_location('inside reserved_fte',10);
2876: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
2877: hr_utility.set_location('inside reserved_fte',20);
2878: open c1(p_person_id, p_position_id, p_effective_date);
2879: fetch c1 into l_fte;
2880: close c1;
2881: end if;
2878: open c1(p_person_id, p_position_id, p_effective_date);
2879: fetch c1 into l_fte;
2880: close c1;
2881: end if;
2882: hr_utility.set_location('inside reserved_fte. l_fte = '||l_fte,30);
2883: return l_fte;
2884: end;
2885: --
2886: function remain_reserved_fte(p_person_id number, p_position_id number, p_effective_date date) return number is
2921: and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
2922:
2923: --
2924: begin
2925: hr_utility.set_location('inside unreserved_fte',10);
2926: if p_position_id is not null and p_effective_date is not null then
2927: --
2928: hr_utility.set_location('inside unreserved_fte',20);
2929: l_pos_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
2924: begin
2925: hr_utility.set_location('inside unreserved_fte',10);
2926: if p_position_id is not null and p_effective_date is not null then
2927: --
2928: hr_utility.set_location('inside unreserved_fte',20);
2929: l_pos_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
2930: --
2931: hr_utility.set_location('inside unreserved_fte',30);
2932: open c1(p_position_id, p_effective_date);
2927: --
2928: hr_utility.set_location('inside unreserved_fte',20);
2929: l_pos_fte := pqh_psf_bus.get_position_fte(p_position_id, p_effective_date);
2930: --
2931: hr_utility.set_location('inside unreserved_fte',30);
2932: open c1(p_position_id, p_effective_date);
2933: fetch c1 into l_fte;
2934: close c1;
2935: end if;
2932: open c1(p_position_id, p_effective_date);
2933: fetch c1 into l_fte;
2934: close c1;
2935: end if;
2936: hr_utility.set_location('inside unreserved_fte ',40);
2937: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 50);
2938: hr_utility.set_location('l_fte : '||l_fte, 60);
2939: if l_pos_fte - nvl(l_fte,0) >=0 then
2940: hr_utility.set_location('inside unreserved_fte ',70);
2933: fetch c1 into l_fte;
2934: close c1;
2935: end if;
2936: hr_utility.set_location('inside unreserved_fte ',40);
2937: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 50);
2938: hr_utility.set_location('l_fte : '||l_fte, 60);
2939: if l_pos_fte - nvl(l_fte,0) >=0 then
2940: hr_utility.set_location('inside unreserved_fte ',70);
2941: return l_pos_fte - nvl(l_fte,0);
2934: close c1;
2935: end if;
2936: hr_utility.set_location('inside unreserved_fte ',40);
2937: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 50);
2938: hr_utility.set_location('l_fte : '||l_fte, 60);
2939: if l_pos_fte - nvl(l_fte,0) >=0 then
2940: hr_utility.set_location('inside unreserved_fte ',70);
2941: return l_pos_fte - nvl(l_fte,0);
2942: else
2936: hr_utility.set_location('inside unreserved_fte ',40);
2937: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 50);
2938: hr_utility.set_location('l_fte : '||l_fte, 60);
2939: if l_pos_fte - nvl(l_fte,0) >=0 then
2940: hr_utility.set_location('inside unreserved_fte ',70);
2941: return l_pos_fte - nvl(l_fte,0);
2942: else
2943: hr_utility.set_location('inside unreserved_fte ',80);
2944: return 0;
2939: if l_pos_fte - nvl(l_fte,0) >=0 then
2940: hr_utility.set_location('inside unreserved_fte ',70);
2941: return l_pos_fte - nvl(l_fte,0);
2942: else
2943: hr_utility.set_location('inside unreserved_fte ',80);
2944: return 0;
2945: end if;
2946: end;
2947: --
2951: l_reserved_fte number;
2952: begin
2953: l_person_fte := pqh_psf_bus.person_fte(p_person_id, p_position_id, p_effective_date, -1);
2954: l_reserved_fte := pqh_psf_bus.reserved_fte(p_person_id, p_position_id, p_effective_date);
2955: hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
2956: hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
2957: if l_reserved_fte>0 and l_person_fte - l_reserved_fte > 0then
2958: l_uu_fte := l_person_fte - l_reserved_fte;
2959: end if;
2952: begin
2953: l_person_fte := pqh_psf_bus.person_fte(p_person_id, p_position_id, p_effective_date, -1);
2954: l_reserved_fte := pqh_psf_bus.reserved_fte(p_person_id, p_position_id, p_effective_date);
2955: hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
2956: hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
2957: if l_reserved_fte>0 and l_person_fte - l_reserved_fte > 0then
2958: l_uu_fte := l_person_fte - l_reserved_fte;
2959: end if;
2960: return l_uu_fte;
2986: where p_effective_date between fnd_date.canonical_to_date(poei_information3)
2987: and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
2988:
2989: begin
2990: hr_utility.set_location('inside reserved_overused ',10);
2991: if p_position_id is not null and p_effective_date is not null then
2992: hr_utility.set_location('inside reserved_overused ',20);
2993: for r1 in c1(p_position_id, p_effective_date)
2994: loop
2988:
2989: begin
2990: hr_utility.set_location('inside reserved_overused ',10);
2991: if p_position_id is not null and p_effective_date is not null then
2992: hr_utility.set_location('inside reserved_overused ',20);
2993: for r1 in c1(p_position_id, p_effective_date)
2994: loop
2995: hr_utility.set_location('Processing for '||r1.person_id,30);
2996: l_reserved_person_overused := used_unreserved_fte(r1.person_id, p_position_id, p_effective_date);
2991: if p_position_id is not null and p_effective_date is not null then
2992: hr_utility.set_location('inside reserved_overused ',20);
2993: for r1 in c1(p_position_id, p_effective_date)
2994: loop
2995: hr_utility.set_location('Processing for '||r1.person_id,30);
2996: l_reserved_person_overused := used_unreserved_fte(r1.person_id, p_position_id, p_effective_date);
2997: l_reserved_overused := l_reserved_overused + l_reserved_person_overused;
2998: end loop;
2999: hr_utility.set_location('inside reserved_overused ',40);
2995: hr_utility.set_location('Processing for '||r1.person_id,30);
2996: l_reserved_person_overused := used_unreserved_fte(r1.person_id, p_position_id, p_effective_date);
2997: l_reserved_overused := l_reserved_overused + l_reserved_person_overused;
2998: end loop;
2999: hr_utility.set_location('inside reserved_overused ',40);
3000: end if;
3001: hr_utility.set_location('inside reserved_overused ',50);
3002: hr_utility.set_location(' l_reserved_overused '||l_reserved_overused,60);
3003: return l_reserved_overused;
2997: l_reserved_overused := l_reserved_overused + l_reserved_person_overused;
2998: end loop;
2999: hr_utility.set_location('inside reserved_overused ',40);
3000: end if;
3001: hr_utility.set_location('inside reserved_overused ',50);
3002: hr_utility.set_location(' l_reserved_overused '||l_reserved_overused,60);
3003: return l_reserved_overused;
3004: end;
3005: --
2998: end loop;
2999: hr_utility.set_location('inside reserved_overused ',40);
3000: end if;
3001: hr_utility.set_location('inside reserved_overused ',50);
3002: hr_utility.set_location(' l_reserved_overused '||l_reserved_overused,60);
3003: return l_reserved_overused;
3004: end;
3005: --
3006: -- This function is used for assignment cost defaulting and doesnot consider
3040: begin
3041: l_unreserved_fte := unreserved_fte(p_position_id, p_effective_date);
3042: l_nonreserved_asg_fte := nonreserved_asg_fte(p_position_id, p_effective_date);
3043: l_reserved_overused := reserved_overused(p_position_id, p_effective_date);
3044: hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
3045: hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
3046: hr_utility.set_location('uu: l_reserved_overused : '||l_reserved_overused, 131);
3047: l_temp := ((l_unreserved_fte - l_nonreserved_asg_fte) - l_reserved_overused);
3048: if l_temp > 0then
3041: l_unreserved_fte := unreserved_fte(p_position_id, p_effective_date);
3042: l_nonreserved_asg_fte := nonreserved_asg_fte(p_position_id, p_effective_date);
3043: l_reserved_overused := reserved_overused(p_position_id, p_effective_date);
3044: hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
3045: hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
3046: hr_utility.set_location('uu: l_reserved_overused : '||l_reserved_overused, 131);
3047: l_temp := ((l_unreserved_fte - l_nonreserved_asg_fte) - l_reserved_overused);
3048: if l_temp > 0then
3049: l_uu_fte := l_temp;
3042: l_nonreserved_asg_fte := nonreserved_asg_fte(p_position_id, p_effective_date);
3043: l_reserved_overused := reserved_overused(p_position_id, p_effective_date);
3044: hr_utility.set_location('uu: l_person_fte : '||l_person_fte, 131);
3045: hr_utility.set_location('uu: l_reserved_fte : '||l_reserved_fte, 131);
3046: hr_utility.set_location('uu: l_reserved_overused : '||l_reserved_overused, 131);
3047: l_temp := ((l_unreserved_fte - l_nonreserved_asg_fte) - l_reserved_overused);
3048: if l_temp > 0then
3049: l_uu_fte := l_temp;
3050: end if;
3055: l_remain_reserved_fte number;
3056: l_remain_unreserved_fte number;
3057: l_fte number:=0;
3058: begin
3059: hr_utility.set_location('p_person_id : '||p_person_id, 131);
3060: hr_utility.set_location('p_position_id : '||p_position_id, 131);
3061: hr_utility.set_location('p_effective_date : '||p_effective_date, 131);
3062: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3063: l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
3056: l_remain_unreserved_fte number;
3057: l_fte number:=0;
3058: begin
3059: hr_utility.set_location('p_person_id : '||p_person_id, 131);
3060: hr_utility.set_location('p_position_id : '||p_position_id, 131);
3061: hr_utility.set_location('p_effective_date : '||p_effective_date, 131);
3062: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3063: l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
3064: l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
3057: l_fte number:=0;
3058: begin
3059: hr_utility.set_location('p_person_id : '||p_person_id, 131);
3060: hr_utility.set_location('p_position_id : '||p_position_id, 131);
3061: hr_utility.set_location('p_effective_date : '||p_effective_date, 131);
3062: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3063: l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
3064: l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
3065: l_fte := l_remain_reserved_fte + l_remain_unreserved_fte;
3062: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3063: l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
3064: l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
3065: l_fte := l_remain_reserved_fte + l_remain_unreserved_fte;
3066: hr_utility.set_location('l_remain_reserved_fte : '||l_remain_reserved_fte, 131);
3067: hr_utility.set_location('l_remain_unreserved_fte : '||l_remain_unreserved_fte, 131);
3068: hr_utility.set_location('l_fte : '||l_fte, 131);
3069: end if;
3070: return l_fte;
3063: l_remain_reserved_fte := remain_reserved_fte(p_person_id, p_position_id, p_effective_date);
3064: l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
3065: l_fte := l_remain_reserved_fte + l_remain_unreserved_fte;
3066: hr_utility.set_location('l_remain_reserved_fte : '||l_remain_reserved_fte, 131);
3067: hr_utility.set_location('l_remain_unreserved_fte : '||l_remain_unreserved_fte, 131);
3068: hr_utility.set_location('l_fte : '||l_fte, 131);
3069: end if;
3070: return l_fte;
3071: end;
3064: l_remain_unreserved_fte := remain_unreserved_fte(p_position_id, p_effective_date);
3065: l_fte := l_remain_reserved_fte + l_remain_unreserved_fte;
3066: hr_utility.set_location('l_remain_reserved_fte : '||l_remain_reserved_fte, 131);
3067: hr_utility.set_location('l_remain_unreserved_fte : '||l_remain_unreserved_fte, 131);
3068: hr_utility.set_location('l_fte : '||l_fte, 131);
3069: end if;
3070: return l_fte;
3071: end;
3072: --
3099: and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
3100: and bdt.budget_version_id = bvr.budget_version_id
3101: and bdt.position_id = p_position_id;
3102: begin
3103: hr_utility.set_location('inside get_pos_budget',10);
3104: begin
3105: open c_bus_grp_id(p_position_id);
3106: fetch c_bus_grp_id into l_business_group_id;
3107: close c_bus_grp_id;
3105: open c_bus_grp_id(p_position_id);
3106: fetch c_bus_grp_id into l_business_group_id;
3107: close c_bus_grp_id;
3108: --
3109: hr_utility.set_location('l_business_group_id:' || l_business_group_id, 20);
3110: --
3111: select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
3112: into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
3113: from pqh_budgets
3119: hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
3120: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
3121: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3122: );
3123: hr_utility.set_location('budget is:'||l_budget_id,30);
3124: open c1(l_budget_unit1_id);
3125: fetch c1 into l_unit1_name;
3126: close c1;
3127: hr_utility.set_location('unit1 is:'||l_unit1_name,40);
3123: hr_utility.set_location('budget is:'||l_budget_id,30);
3124: open c1(l_budget_unit1_id);
3125: fetch c1 into l_unit1_name;
3126: close c1;
3127: hr_utility.set_location('unit1 is:'||l_unit1_name,40);
3128: open c1(l_budget_unit2_id);
3129: fetch c1 into l_unit2_name;
3130: close c1;
3131: hr_utility.set_location('unit2 is:'||l_unit2_name,50);
3127: hr_utility.set_location('unit1 is:'||l_unit1_name,40);
3128: open c1(l_budget_unit2_id);
3129: fetch c1 into l_unit2_name;
3130: close c1;
3131: hr_utility.set_location('unit2 is:'||l_unit2_name,50);
3132: open c1(l_budget_unit3_id);
3133: fetch c1 into l_unit3_name;
3134: close c1;
3135: hr_utility.set_location('unit3 is:'||l_unit3_name,60);
3131: hr_utility.set_location('unit2 is:'||l_unit2_name,50);
3132: open c1(l_budget_unit3_id);
3133: fetch c1 into l_unit3_name;
3134: close c1;
3135: hr_utility.set_location('unit3 is:'||l_unit3_name,60);
3136: exception
3137: when others then
3138: hr_utility.set_location('some error occured',65);
3139: return l_budgeted_money;
3134: close c1;
3135: hr_utility.set_location('unit3 is:'||l_unit3_name,60);
3136: exception
3137: when others then
3138: hr_utility.set_location('some error occured',65);
3139: return l_budgeted_money;
3140: end;
3141: for i in c2(l_budget_id) loop
3142: hr_utility.set_location('budget_detail_id:'||i.budget_detail_id, 80);
3138: hr_utility.set_location('some error occured',65);
3139: return l_budgeted_money;
3140: end;
3141: for i in c2(l_budget_id) loop
3142: hr_utility.set_location('budget_detail_id:'||i.budget_detail_id, 80);
3143: -- row corresponding to the position is picked up
3144: if l_unit1_name ='MONEY' then
3145: l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit1_value,0);
3146: elsif l_unit2_name ='MONEY' then
3147: l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit2_value,0);
3148: elsif l_unit3_name ='MONEY' then
3149: l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit3_value,0);
3150: end if;
3151: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 90);
3152: end loop;
3153: hr_utility.set_location('total budgeted_money is:'||l_budgeted_money, 100);
3154: return l_budgeted_money;
3155: exception
3149: l_budgeted_money := nvl(l_budgeted_money,0) + nvl(i.budget_unit3_value,0);
3150: end if;
3151: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 90);
3152: end loop;
3153: hr_utility.set_location('total budgeted_money is:'||l_budgeted_money, 100);
3154: return l_budgeted_money;
3155: exception
3156: when others then
3157: hr_utility.set_location('some error occured -2 ', 110);
3153: hr_utility.set_location('total budgeted_money is:'||l_budgeted_money, 100);
3154: return l_budgeted_money;
3155: exception
3156: when others then
3157: hr_utility.set_location('some error occured -2 ', 110);
3158: return l_budgeted_money;
3159: end;
3160: --
3161: function get_pos_actuals_commitment(
3186: cursor c1(p_unit_id number) is select system_type_cd from
3187: per_shared_types where shared_type_id = p_unit_id;
3188: --
3189: begin
3190: hr_utility.set_location('inside chk_pos_budget',10);
3191: hr_utility.set_location('position_id is '||p_position_id,20);
3192: hr_utility.set_location('effective_date is '||to_char(p_effective_date,'dd-MM-RRRR'),30);
3193: begin
3194: open c_bus_grp_id(p_position_id);
3187: per_shared_types where shared_type_id = p_unit_id;
3188: --
3189: begin
3190: hr_utility.set_location('inside chk_pos_budget',10);
3191: hr_utility.set_location('position_id is '||p_position_id,20);
3192: hr_utility.set_location('effective_date is '||to_char(p_effective_date,'dd-MM-RRRR'),30);
3193: begin
3194: open c_bus_grp_id(p_position_id);
3195: fetch c_bus_grp_id into l_business_group_id;
3188: --
3189: begin
3190: hr_utility.set_location('inside chk_pos_budget',10);
3191: hr_utility.set_location('position_id is '||p_position_id,20);
3192: hr_utility.set_location('effective_date is '||to_char(p_effective_date,'dd-MM-RRRR'),30);
3193: begin
3194: open c_bus_grp_id(p_position_id);
3195: fetch c_bus_grp_id into l_business_group_id;
3196: close c_bus_grp_id;
3194: open c_bus_grp_id(p_position_id);
3195: fetch c_bus_grp_id into l_business_group_id;
3196: close c_bus_grp_id;
3197: --
3198: hr_utility.set_location('l_business_group_id:' || l_business_group_id,40);
3199: --
3200: select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
3201: , budget_start_date, budget_end_date
3202: into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
3210: hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
3211: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
3212: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3213: );
3214: hr_utility.set_location('budget exists' || l_budget_id, 50);
3215: hr_utility.set_location('start_date is '||to_char(l_start_date,'dd-MM-RRRR'),55);
3216: hr_utility.set_location('end_date is '||to_char(l_end_date,'dd-MM-RRRR'),60);
3217: --
3218: select budget_version_id into l_budget_version_id
3211: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
3212: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3213: );
3214: hr_utility.set_location('budget exists' || l_budget_id, 50);
3215: hr_utility.set_location('start_date is '||to_char(l_start_date,'dd-MM-RRRR'),55);
3216: hr_utility.set_location('end_date is '||to_char(l_end_date,'dd-MM-RRRR'),60);
3217: --
3218: select budget_version_id into l_budget_version_id
3219: from pqh_budget_versions bvr
3212: or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3213: );
3214: hr_utility.set_location('budget exists' || l_budget_id, 50);
3215: hr_utility.set_location('start_date is '||to_char(l_start_date,'dd-MM-RRRR'),55);
3216: hr_utility.set_location('end_date is '||to_char(l_end_date,'dd-MM-RRRR'),60);
3217: --
3218: select budget_version_id into l_budget_version_id
3219: from pqh_budget_versions bvr
3220: where budget_id = l_budget_id
3218: select budget_version_id into l_budget_version_id
3219: from pqh_budget_versions bvr
3220: where budget_id = l_budget_id
3221: and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date);
3222: hr_utility.set_location('budget version exists' || l_budget_version_id, 70);
3223: open c1(l_budget_unit1_id);
3224: fetch c1 into l_unit1_name;
3225: close c1;
3226: hr_utility.set_location('unit1 is ' || l_unit1_name, 80);
3222: hr_utility.set_location('budget version exists' || l_budget_version_id, 70);
3223: open c1(l_budget_unit1_id);
3224: fetch c1 into l_unit1_name;
3225: close c1;
3226: hr_utility.set_location('unit1 is ' || l_unit1_name, 80);
3227: open c1(l_budget_unit2_id);
3228: fetch c1 into l_unit2_name;
3229: close c1;
3230: hr_utility.set_location('unit2 is ' || l_unit2_name, 90);
3226: hr_utility.set_location('unit1 is ' || l_unit1_name, 80);
3227: open c1(l_budget_unit2_id);
3228: fetch c1 into l_unit2_name;
3229: close c1;
3230: hr_utility.set_location('unit2 is ' || l_unit2_name, 90);
3231: open c1(l_budget_unit3_id);
3232: fetch c1 into l_unit3_name;
3233: close c1;
3234: hr_utility.set_location('unit3 is ' || l_unit3_name, 100);
3230: hr_utility.set_location('unit2 is ' || l_unit2_name, 90);
3231: open c1(l_budget_unit3_id);
3232: fetch c1 into l_unit3_name;
3233: close c1;
3234: hr_utility.set_location('unit3 is ' || l_unit3_name, 100);
3235: exception
3236: when others then
3237: hr_utility.set_location('some error occured', 110);
3238: return null;
3233: close c1;
3234: hr_utility.set_location('unit3 is ' || l_unit3_name, 100);
3235: exception
3236: when others then
3237: hr_utility.set_location('some error occured', 110);
3238: return null;
3239: end;
3240: --
3241: if l_unit1_name ='MONEY' then
3249: p_unit_of_measure_id =>l_budget_unit1_id,
3250: p_value_type =>'T',
3251: p_ex_assignment_id =>p_ex_assignment_id
3252: );
3253: hr_utility.set_location('unit1_amt is'||l_actual_commitment, 120);
3254: elsif l_unit2_name ='MONEY' then
3255: l_actual_commitment := l_actual_commitment +
3256: pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
3257: (
3262: p_unit_of_measure_id =>l_budget_unit2_id,
3263: p_value_type =>'T',
3264: p_ex_assignment_id =>p_ex_assignment_id
3265: );
3266: hr_utility.set_location('unit2_amt is'||l_actual_commitment, 120);
3267: elsif l_unit3_name ='MONEY' then
3268: l_actual_commitment := l_actual_commitment +
3269: pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
3270: (
3275: p_unit_of_measure_id =>l_budget_unit3_id,
3276: p_value_type =>'T',
3277: p_ex_assignment_id =>p_ex_assignment_id
3278: );
3279: hr_utility.set_location('unit3_amt is'||l_actual_commitment, 120);
3280: end if;
3281: return l_actual_commitment;
3282: exception
3283: when others then
3280: end if;
3281: return l_actual_commitment;
3282: exception
3283: when others then
3284: hr_utility.set_location('some error occured - 2', 120);
3285: return null;
3286: end;
3287: --
3288: function get_asg_actuals_commitment(
3318: open c_bus_grp_id(p_assignment_id);
3319: fetch c_bus_grp_id into l_business_group_id;
3320: close c_bus_grp_id;
3321: --
3322: hr_utility.set_location('l_business_group_id:' || l_business_group_id, 600
3323: );
3324: --
3325: select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
3326: , budget_start_date, budget_end_date
3405: function chk_pos_budget(p_position_id in number, p_effective_date in date) return boolean is
3406: l_budgeted_money number;
3407: l_pos_actuals_commitment number;
3408: begin
3409: hr_utility.set_location('inside chk_pos_budget', 30);
3410: l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3411: l_pos_actuals_commitment := get_pos_actuals_commitment(p_position_id,p_effective_date);
3412: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 10);
3413: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 20);
3408: begin
3409: hr_utility.set_location('inside chk_pos_budget', 30);
3410: l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3411: l_pos_actuals_commitment := get_pos_actuals_commitment(p_position_id,p_effective_date);
3412: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 10);
3413: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 20);
3414: if l_budgeted_money <> -1 and l_pos_actuals_commitment <> -1 then
3415: if l_pos_actuals_commitment >l_budgeted_money then
3416: hr_utility.set_location('actual> budget', 30);
3409: hr_utility.set_location('inside chk_pos_budget', 30);
3410: l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3411: l_pos_actuals_commitment := get_pos_actuals_commitment(p_position_id,p_effective_date);
3412: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 10);
3413: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 20);
3414: if l_budgeted_money <> -1 and l_pos_actuals_commitment <> -1 then
3415: if l_pos_actuals_commitment >l_budgeted_money then
3416: hr_utility.set_location('actual> budget', 30);
3417: return false;
3412: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 10);
3413: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 20);
3414: if l_budgeted_money <> -1 and l_pos_actuals_commitment <> -1 then
3415: if l_pos_actuals_commitment >l_budgeted_money then
3416: hr_utility.set_location('actual> budget', 30);
3417: return false;
3418: end if;
3419: end if;
3420: return true;
3429: l_pos_actuals_cmmt_ex_asg number;
3430: l_asg_actuals_commitment number;
3431: begin
3432: l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3433: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 100);
3434: if l_budgeted_money > -1 then
3435: l_asg_actuals_commitment := get_asg_actuals_commitment(p_ex_assignment_id,p_effective_date);
3436: hr_utility.set_location('l_asg_actuals_commitment:'||l_asg_actuals_commitment, 100);
3437: if l_asg_actuals_commitment > 0 then
3432: l_budgeted_money := budgeted_money(p_position_id,p_effective_date);
3433: hr_utility.set_location('l_budgeted_money:'||l_budgeted_money, 100);
3434: if l_budgeted_money > -1 then
3435: l_asg_actuals_commitment := get_asg_actuals_commitment(p_ex_assignment_id,p_effective_date);
3436: hr_utility.set_location('l_asg_actuals_commitment:'||l_asg_actuals_commitment, 100);
3437: if l_asg_actuals_commitment > 0 then
3438: l_pos_actuals_cmmt_ex_asg := get_pos_actuals_commitment(
3439: p_position_id,
3440: p_effective_date,
3440: p_effective_date,
3441: p_ex_assignment_id);
3442: l_pos_actuals_commitment := l_pos_actuals_cmmt_ex_asg+l_asg_actuals_commitment;
3443:
3444: hr_utility.set_location('l_pos_actuals_cmmt_ex_asg:'||l_pos_actuals_cmmt_ex_asg, 100);
3445: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 100);
3446: if l_pos_actuals_cmmt_ex_asg <> -1 then
3447: if ((l_pos_actuals_cmmt_ex_asg < l_budgeted_money)
3448: and (l_pos_actuals_commitment > l_budgeted_money)) then
3441: p_ex_assignment_id);
3442: l_pos_actuals_commitment := l_pos_actuals_cmmt_ex_asg+l_asg_actuals_commitment;
3443:
3444: hr_utility.set_location('l_pos_actuals_cmmt_ex_asg:'||l_pos_actuals_cmmt_ex_asg, 100);
3445: hr_utility.set_location('l_pos_actuals_commitment:'||l_pos_actuals_commitment, 100);
3446: if l_pos_actuals_cmmt_ex_asg <> -1 then
3447: if ((l_pos_actuals_cmmt_ex_asg < l_budgeted_money)
3448: and (l_pos_actuals_commitment > l_budgeted_money)) then
3449: return true;
3487: where p_effective_date between fnd_date.canonical_to_date(poei_information3)
3488: and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
3489:
3490: begin
3491: hr_utility.set_location('inside pos_reserved_fte',10);
3492: if p_position_id is not null and p_effective_date is not null then
3493: hr_utility.set_location('inside pos_reserved_fte',20);
3494: open c1(p_position_id, p_effective_date, p_ex_position_extra_info_id );
3495: fetch c1 into l_fte;
3489:
3490: begin
3491: hr_utility.set_location('inside pos_reserved_fte',10);
3492: if p_position_id is not null and p_effective_date is not null then
3493: hr_utility.set_location('inside pos_reserved_fte',20);
3494: open c1(p_position_id, p_effective_date, p_ex_position_extra_info_id );
3495: fetch c1 into l_fte;
3496: close c1;
3497: end if;
3494: open c1(p_position_id, p_effective_date, p_ex_position_extra_info_id );
3495: fetch c1 into l_fte;
3496: close c1;
3497: end if;
3498: hr_utility.set_location('inside pos_reserved_fte',30);
3499: hr_utility.set_location('l_fte '||l_fte,40);
3500: return l_fte;
3501: end;
3502: --
3495: fetch c1 into l_fte;
3496: close c1;
3497: end if;
3498: hr_utility.set_location('inside pos_reserved_fte',30);
3499: hr_utility.set_location('l_fte '||l_fte,40);
3500: return l_fte;
3501: end;
3502: --
3503: function poei_reserved_fte(p_position_extra_info_id number) return number is
3554: and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time) >= p_effective_date
3555: and poei_information5 = asn.person_id);
3556: l_nonreserved_fte number:=0;
3557: begin
3558: hr_utility.set_location('inside nonreserved_asg_fte',10);
3559: open c1(p_position_id, p_effective_date,
3560: p_ex_position_extra_info_id, p_ex_person_id);
3561: fetch c1 into l_nonreserved_fte;
3562: hr_utility.set_location('inside nonreserved_asg_fte',20);
3558: hr_utility.set_location('inside nonreserved_asg_fte',10);
3559: open c1(p_position_id, p_effective_date,
3560: p_ex_position_extra_info_id, p_ex_person_id);
3561: fetch c1 into l_nonreserved_fte;
3562: hr_utility.set_location('inside nonreserved_asg_fte',20);
3563: hr_utility.set_location('l_nonreserved_fte : '||l_nonreserved_fte, 30);
3564: return nvl(l_nonreserved_fte,0);
3565: end;
3566: --
3559: open c1(p_position_id, p_effective_date,
3560: p_ex_position_extra_info_id, p_ex_person_id);
3561: fetch c1 into l_nonreserved_fte;
3562: hr_utility.set_location('inside nonreserved_asg_fte',20);
3563: hr_utility.set_location('l_nonreserved_fte : '||l_nonreserved_fte, 30);
3564: return nvl(l_nonreserved_fte,0);
3565: end;
3566: --
3567: function position_fte(p_position_id number, p_effective_date date) return number is
3605: begin
3606: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3607: open c_budgeted_fte(p_person_id, p_position_id);
3608: fetch c_budgeted_fte into l_assignment_fte;
3609: hr_utility.set_location('l_person_id : '||l_person_id, 630);
3610: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
3611: close c_budgeted_fte;
3612: end if;
3613: return(l_assignment_fte);
3606: if p_person_id is not null and p_position_id is not null and p_effective_date is not null then
3607: open c_budgeted_fte(p_person_id, p_position_id);
3608: fetch c_budgeted_fte into l_assignment_fte;
3609: hr_utility.set_location('l_person_id : '||l_person_id, 630);
3610: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 630);
3611: close c_budgeted_fte;
3612: end if;
3613: return(l_assignment_fte);
3614: end;
3693: ,p_system_budget_unit => 'FTE'
3694: ,p_business_group_id => l_business_group_id
3695: );
3696: --
3697: hr_utility.set_location('effective date : '||r1.effective_date, 10);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3694: ,p_business_group_id => l_business_group_id
3695: );
3696: --
3697: hr_utility.set_location('effective date : '||r1.effective_date, 10);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3695: );
3696: --
3697: hr_utility.set_location('effective date : '||r1.effective_date, 10);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3696: --
3697: hr_utility.set_location('effective date : '||r1.effective_date, 10);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3697: hr_utility.set_location('effective date : '||r1.effective_date, 10);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3705: hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3698: hr_utility.set_location('l_total_fte : '|| l_total_fte, 20);
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3705: hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3706: --
3699: hr_utility.set_location('l_reserved_fte : '||l_reserved_fte, 30);
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3705: hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3706: --
3707: if l_budgeted_fte is not null or l_realloc_fte is not null then
3700: hr_utility.set_location('l_nonreserved_asg_fte : '||l_nonreserved_asg_fte, 40);
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3705: hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3706: --
3707: if l_budgeted_fte is not null or l_realloc_fte is not null then
3708: --
3701: hr_utility.set_location('l_person_asg_fte : '||l_person_asg_fte, 50);
3702: hr_utility.set_location('l_poei_fte : '||l_poei_fte, 60);
3703: hr_utility.set_location('l_position_fte : '||l_position_fte, 70);
3704: hr_utility.set_location('l_budgeted_fte : '||l_budgeted_fte, 80);
3705: hr_utility.set_location('l_realloc_fte : '||l_realloc_fte, 85);
3706: --
3707: if l_budgeted_fte is not null or l_realloc_fte is not null then
3708: --
3709: l_bgt_realloc := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3708: --
3709: l_bgt_realloc := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3710: --
3711: if l_total_fte > l_bgt_realloc then
3712: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_BGT_FTE');
3713: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3714: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3715: hr_utility.raise_error;
3716: end if;
3709: l_bgt_realloc := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
3710: --
3711: if l_total_fte > l_bgt_realloc then
3712: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_BGT_FTE');
3713: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3714: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3715: hr_utility.raise_error;
3716: end if;
3717: else
3710: --
3711: if l_total_fte > l_bgt_realloc then
3712: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_BGT_FTE');
3713: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3714: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3715: hr_utility.raise_error;
3716: end if;
3717: else
3718: if l_total_fte > l_position_fte then
3711: if l_total_fte > l_bgt_realloc then
3712: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_BGT_FTE');
3713: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3714: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3715: hr_utility.raise_error;
3716: end if;
3717: else
3718: if l_total_fte > l_position_fte then
3719: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3715: hr_utility.raise_error;
3716: end if;
3717: else
3718: if l_total_fte > l_position_fte then
3719: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3720: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3721: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3722: hr_utility.raise_error;
3723: end if;
3716: end if;
3717: else
3718: if l_total_fte > l_position_fte then
3719: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3720: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3721: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3722: hr_utility.raise_error;
3723: end if;
3724: end if;
3717: else
3718: if l_total_fte > l_position_fte then
3719: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3720: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3721: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3722: hr_utility.raise_error;
3723: end if;
3724: end if;
3725: end loop;
3718: if l_total_fte > l_position_fte then
3719: hr_utility.set_message(8302,'PQH_RES_FTE_GT_AVL_POS_FTE');
3720: --hr_utility.set_message_token('PERSON', hr_general.decode_person_name(p_person_id));
3721: hr_utility.set_message_token('EFFECTIVE_DATE', r1.effective_date);
3722: hr_utility.raise_error;
3723: end if;
3724: end if;
3725: end loop;
3726: end;
3790: -- End changes for bug 10262123
3791:
3792: --
3793: begin
3794: hr_utility.set_location('inside chk_reserved_fte',10);
3795: open c_pos_fte(p_position_id, p_effective_date);
3796: fetch c_pos_fte into l_position_type,
3797: l_overlap_period, l_business_group_id;
3798: close c_pos_fte;
3796: fetch c_pos_fte into l_position_type,
3797: l_overlap_period, l_business_group_id;
3798: close c_pos_fte;
3799: --
3800: hr_utility.set_location('inside chk_reserved_fte',20);
3801: hr_utility.set_location(' l_position_type'||l_position_type , 30);
3802: hr_utility.set_location(' l_overlap_period'||l_overlap_period , 40);
3803: hr_utility.set_location(' l_business_group_id'||l_business_group_id , 50);
3804: --
3797: l_overlap_period, l_business_group_id;
3798: close c_pos_fte;
3799: --
3800: hr_utility.set_location('inside chk_reserved_fte',20);
3801: hr_utility.set_location(' l_position_type'||l_position_type , 30);
3802: hr_utility.set_location(' l_overlap_period'||l_overlap_period , 40);
3803: hr_utility.set_location(' l_business_group_id'||l_business_group_id , 50);
3804: --
3805: --
3798: close c_pos_fte;
3799: --
3800: hr_utility.set_location('inside chk_reserved_fte',20);
3801: hr_utility.set_location(' l_position_type'||l_position_type , 30);
3802: hr_utility.set_location(' l_overlap_period'||l_overlap_period , 40);
3803: hr_utility.set_location(' l_business_group_id'||l_business_group_id , 50);
3804: --
3805: --
3806: if l_position_type in ('SHARED', 'SINGLE') then
3799: --
3800: hr_utility.set_location('inside chk_reserved_fte',20);
3801: hr_utility.set_location(' l_position_type'||l_position_type , 30);
3802: hr_utility.set_location(' l_overlap_period'||l_overlap_period , 40);
3803: hr_utility.set_location(' l_business_group_id'||l_business_group_id , 50);
3804: --
3805: --
3806: if l_position_type in ('SHARED', 'SINGLE') then
3807: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
3811: p_assignment_end_date => p_effective_end_date,
3812: p_assignment_id => p_assignment_id); --added for bug 10220040
3813: if not l_overlap_dates_present then
3814:
3815: hr_utility.set_location('Entering chk_reserved_fte', 100);
3816: --
3817: open c_pos_reserved(p_position_id, p_effective_date);
3818: fetch c_pos_reserved into l_dummy;
3819: if c_pos_reserved%notfound then
3816: --
3817: open c_pos_reserved(p_position_id, p_effective_date);
3818: fetch c_pos_reserved into l_dummy;
3819: if c_pos_reserved%notfound then
3820: hr_utility.set_location('Exiting chk_reserved_fte FALSE', 100);
3821: close c_pos_reserved;
3822: return false;
3823: else
3824: close c_pos_reserved;
3824: close c_pos_reserved;
3825: end if;
3826: l_available_fte := pqh_psf_bus.available_fte(p_person_id, p_position_id, p_effective_date);
3827: if l_position_type = 'SINGLE' then
3828: hr_utility.set_location('p_position_type is SINGLE ', 110);
3829: l_assignment_fte := 1;
3830: elsif p_assignment_id is not null then
3831: hr_utility.set_location('p_assignment_id is not null ', 111);
3832: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0
3827: if l_position_type = 'SINGLE' then
3828: hr_utility.set_location('p_position_type is SINGLE ', 110);
3829: l_assignment_fte := 1;
3830: elsif p_assignment_id is not null then
3831: hr_utility.set_location('p_assignment_id is not null ', 111);
3832: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0
3833: );
3834: else
3835: hr_utility.set_location('p_assignment_id is null and pos type SHARED', 112);
3831: hr_utility.set_location('p_assignment_id is not null ', 111);
3832: l_assignment_fte := nvl(pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date),0
3833: );
3834: else
3835: hr_utility.set_location('p_assignment_id is null and pos type SHARED', 112);
3836: l_assignment_fte := p_default_asg_fte;
3837: end if;
3838: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3839: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3834: else
3835: hr_utility.set_location('p_assignment_id is null and pos type SHARED', 112);
3836: l_assignment_fte := p_default_asg_fte;
3837: end if;
3838: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3839: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3840: hr_utility.set_location('p_default_asg_fte : '||p_default_asg_fte, 133);
3841: if (l_assignment_fte > l_available_fte) then
3842: hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3835: hr_utility.set_location('p_assignment_id is null and pos type SHARED', 112);
3836: l_assignment_fte := p_default_asg_fte;
3837: end if;
3838: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3839: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3840: hr_utility.set_location('p_default_asg_fte : '||p_default_asg_fte, 133);
3841: if (l_assignment_fte > l_available_fte) then
3842: hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3843: return true;
3836: l_assignment_fte := p_default_asg_fte;
3837: end if;
3838: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3839: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3840: hr_utility.set_location('p_default_asg_fte : '||p_default_asg_fte, 133);
3841: if (l_assignment_fte > l_available_fte) then
3842: hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3843: return true;
3844: else
3838: hr_utility.set_location('l_available_fte : '||l_available_fte, 131);
3839: hr_utility.set_location('l_assignment_fte : '||l_assignment_fte, 132);
3840: hr_utility.set_location('p_default_asg_fte : '||p_default_asg_fte, 133);
3841: if (l_assignment_fte > l_available_fte) then
3842: hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3843: return true;
3844: else
3845: hr_utility.set_location('Exiting chk_reserved_fte FALSE', 100);
3846: return false;
3841: if (l_assignment_fte > l_available_fte) then
3842: hr_utility.set_location('Exiting chk_reserved_fte TRUE', 100);
3843: return true;
3844: else
3845: hr_utility.set_location('Exiting chk_reserved_fte FALSE', 100);
3846: return false;
3847: end if;
3848: end if;
3849: end if;
3846: return false;
3847: end if;
3848: end if;
3849: end if;
3850: hr_utility.set_location('Exiting chk_reserved_fte FALSE', 420);
3851: return false;
3852: end;
3853: --
3854: function chk_pos_reserve_exists(p_position_id number,
3870: and position_id = p_position_id)
3871: where p_effective_date <= poei_information4;
3872: --
3873: begin
3874: hr_utility.set_location('inside chk_pos_reserve_exists',10);
3875: open c_pos_reserved(p_position_id, p_effective_date);
3876: fetch c_pos_reserved into l_dummy;
3877: hr_utility.set_location('inside chk_pos_reserve_exists',20);
3878: if c_pos_reserved%found then
3873: begin
3874: hr_utility.set_location('inside chk_pos_reserve_exists',10);
3875: open c_pos_reserved(p_position_id, p_effective_date);
3876: fetch c_pos_reserved into l_dummy;
3877: hr_utility.set_location('inside chk_pos_reserve_exists',20);
3878: if c_pos_reserved%found then
3879: hr_utility.set_location('Exiting chk_pos_reserve_exists TRUE', 100);
3880: close c_pos_reserved;
3881: return true;
3875: open c_pos_reserved(p_position_id, p_effective_date);
3876: fetch c_pos_reserved into l_dummy;
3877: hr_utility.set_location('inside chk_pos_reserve_exists',20);
3878: if c_pos_reserved%found then
3879: hr_utility.set_location('Exiting chk_pos_reserve_exists TRUE', 100);
3880: close c_pos_reserved;
3881: return true;
3882: end if;
3883: hr_utility.set_location('Exiting chk_pos_reserve_exists FALSE', 100);
3879: hr_utility.set_location('Exiting chk_pos_reserve_exists TRUE', 100);
3880: close c_pos_reserved;
3881: return true;
3882: end if;
3883: hr_utility.set_location('Exiting chk_pos_reserve_exists FALSE', 100);
3884: close c_pos_reserved;
3885: return false;
3886: end;
3887: --
3962: where position_id = p_position_id
3963: and effective_start_date between p_validation_start_date and p_validation_end_date;
3964: --
3965: begin
3966: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 201);
3967: hr_utility.set_location('chk_future_reserved_fte p_validation_start_date: '
3968: ||p_validation_start_date, 202);
3969: hr_utility.set_location('chk_future_reserved_fte p_validation_end_date: '
3970: ||p_validation_end_date, 202);
3963: and effective_start_date between p_validation_start_date and p_validation_end_date;
3964: --
3965: begin
3966: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 201);
3967: hr_utility.set_location('chk_future_reserved_fte p_validation_start_date: '
3968: ||p_validation_start_date, 202);
3969: hr_utility.set_location('chk_future_reserved_fte p_validation_end_date: '
3970: ||p_validation_end_date, 202);
3971: if (p_position_id is not null) then
3965: begin
3966: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 201);
3967: hr_utility.set_location('chk_future_reserved_fte p_validation_start_date: '
3968: ||p_validation_start_date, 202);
3969: hr_utility.set_location('chk_future_reserved_fte p_validation_end_date: '
3970: ||p_validation_end_date, 202);
3971: if (p_position_id is not null) then
3972: if chk_pos_reserve_exists(p_position_id,
3973: p_validation_start_date) then
3973: p_validation_start_date) then
3974: if (p_assignment_id is not null) then
3975: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
3976: loop
3977: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '
3978: ||r1.effective_start_date, 203);
3979: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3980: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3981: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3975: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
3976: loop
3977: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '
3978: ||r1.effective_start_date, 203);
3979: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3980: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3981: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3982: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3983: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3976: loop
3977: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '
3978: ||r1.effective_start_date, 203);
3979: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3980: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3981: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3982: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3983: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3984: r1.effective_start_date,
3977: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '
3978: ||r1.effective_start_date, 203);
3979: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3980: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3981: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3982: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3983: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3984: r1.effective_start_date,
3985: p_validation_end_date, -- Added for bug 10220040
3978: ||r1.effective_start_date, 203);
3979: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3980: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3981: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3982: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3983: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3984: r1.effective_start_date,
3985: p_validation_end_date, -- Added for bug 10220040
3986: p_default_asg_fte) then
3989: end loop;
3990: else
3991: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
3992: loop
3993: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3994: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3995: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3996: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3997: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3990: else
3991: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
3992: loop
3993: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3994: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3995: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3996: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3997: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3998: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3991: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
3992: loop
3993: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3994: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3995: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3996: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3997: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3998: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3999: r1.effective_start_date,
3992: loop
3993: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3994: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3995: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3996: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3997: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3998: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3999: r1.effective_start_date,
4000: p_validation_end_date, -- Added for bug 10220040
3993: hr_utility.set_location('chk_future_reserved_fte r1.effective_start_date : '||r1.effective_start_date, 203);
3994: hr_utility.set_location('chk_future_reserved_fte p_assignment_id : '||p_assignment_id, 203);
3995: hr_utility.set_location('chk_future_reserved_fte p_person_id : '||p_person_id, 203);
3996: hr_utility.set_location('chk_future_reserved_fte p_position_id : '||p_position_id, 203);
3997: hr_utility.set_location('chk_future_reserved_fte p_default_asg_fte : '||p_default_asg_fte, 203);
3998: if chk_reserved_fte(p_assignment_id, p_person_id, p_position_id, p_position_type,
3999: r1.effective_start_date,
4000: p_validation_end_date, -- Added for bug 10220040
4001: p_default_asg_fte) then
4067: fetch c_pos_fte into p_position_type, p_position_fte,
4068: p_organization_id, l_overlap_period, l_business_group_id;
4069: close c_pos_fte;
4070: --
4071: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_position_type'||p_position_type , 20);
4072: --
4073: if p_position_type = 'SHARED' then
4074: --
4075: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
4078: p_assignment_start_date => p_effective_date,
4079: p_assignment_end_date => p_effective_end_date,
4080: p_assignment_id => p_assignment_id); --added for bug 10220040
4081: if not l_overlap_dates_present then
4082: hr_utility.set_location('chk_pos_fte_sum_asg_fte SHARED' , 20);
4083: p_budgeted_fte := budgeted_fte(p_position_id, p_effective_date);
4084: --
4085: p_realloc_fte := pqh_reallocation_pkg.get_reallocation(
4086: p_position_id => p_position_id
4091: ,p_business_group_id => l_business_group_id
4092: );
4093: --
4094: --
4095: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
4096: if p_assignment_id is null then
4097: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4098: p_total_asg_fte := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date) + p_default_asg_fte;
4099: else
4093: --
4094: --
4095: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
4096: if p_assignment_id is null then
4097: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4098: p_total_asg_fte := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date) + p_default_asg_fte;
4099: else
4100: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
4101: l_sum := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date);
4096: if p_assignment_id is null then
4097: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4098: p_total_asg_fte := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date) + p_default_asg_fte;
4099: else
4100: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
4101: l_sum := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date);
4102: l_asg := pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date);
4103: p_total_asg_fte := l_sum + l_asg;
4104: --
4101: l_sum := pqh_psf_bus.sum_assignment_fte(p_position_id, p_effective_date);
4102: l_asg := pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date);
4103: p_total_asg_fte := l_sum + l_asg;
4104: --
4105: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4106: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4107: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4108: end if;
4109: --
4102: l_asg := pqh_psf_bus.assignment_fte(p_assignment_id, p_effective_date);
4103: p_total_asg_fte := l_sum + l_asg;
4104: --
4105: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4106: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4107: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4108: end if;
4109: --
4110: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SHARED' , 20);
4103: p_total_asg_fte := l_sum + l_asg;
4104: --
4105: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4106: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4107: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4108: end if;
4109: --
4110: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SHARED' , 20);
4111: end if;
4106: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4107: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4108: end if;
4109: --
4110: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SHARED' , 20);
4111: end if;
4112: elsif p_position_type = 'SINGLE' then
4113: --
4114: l_overlap_dates_present := pqh_psf_bus.chk_overlap_dates(
4117: p_assignment_start_date => p_effective_date,
4118: p_assignment_end_date => p_effective_end_date,
4119: p_assignment_id => p_assignment_id); --added for bug 10220040
4120: if not l_overlap_dates_present then
4121: hr_utility.set_location('chk_pos_fte_sum_asg_fte SINGLE' , 20);
4122: --
4123: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
4124: if p_assignment_id is null then
4125: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4119: p_assignment_id => p_assignment_id); --added for bug 10220040
4120: if not l_overlap_dates_present then
4121: hr_utility.set_location('chk_pos_fte_sum_asg_fte SINGLE' , 20);
4122: --
4123: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
4124: if p_assignment_id is null then
4125: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4126: p_total_asg_fte := pqh_psf_bus.no_assignments(p_position_id, p_effective_date) + 1;
4127: else
4121: hr_utility.set_location('chk_pos_fte_sum_asg_fte SINGLE' , 20);
4122: --
4123: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_null' , 20);
4124: if p_assignment_id is null then
4125: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4126: p_total_asg_fte := pqh_psf_bus.no_assignments(p_position_id, p_effective_date) + 1;
4127: else
4128: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
4129: l_sum := pqh_psf_bus.no_assignments(p_position_id, p_effective_date);
4124: if p_assignment_id is null then
4125: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is null' , 20);
4126: p_total_asg_fte := pqh_psf_bus.no_assignments(p_position_id, p_effective_date) + 1;
4127: else
4128: hr_utility.set_location('chk_pos_fte_sum_asg_fte before asg_is not null' , 20);
4129: l_sum := pqh_psf_bus.no_assignments(p_position_id, p_effective_date);
4130: l_asg := 1;
4131: p_total_asg_fte := l_sum + l_asg;
4132: --
4129: l_sum := pqh_psf_bus.no_assignments(p_position_id, p_effective_date);
4130: l_asg := 1;
4131: p_total_asg_fte := l_sum + l_asg;
4132: --
4133: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4134: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4135: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4136: end if;
4137: --
4130: l_asg := 1;
4131: p_total_asg_fte := l_sum + l_asg;
4132: --
4133: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4134: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4135: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4136: end if;
4137: --
4138: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SINGLE' , 20);
4131: p_total_asg_fte := l_sum + l_asg;
4132: --
4133: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_sum : '||l_sum, 20);
4134: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4135: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4136: end if;
4137: --
4138: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SINGLE' , 20);
4139: end if;
4134: hr_utility.set_location('chk_pos_fte_sum_asg_fte l_asg : '||l_asg, 20);
4135: hr_utility.set_location('chk_pos_fte_sum_asg_fte p_total_asg_fte : '||p_total_asg_fte, 20);
4136: end if;
4137: --
4138: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SINGLE' , 20);
4139: end if;
4140: end if;
4141: hr_utility.set_location('chk_pos_fte_sum_asg_fte end ' , 20);
4142: exception when others then
4137: --
4138: hr_utility.set_location('chk_pos_fte_sum_asg_fte end SINGLE' , 20);
4139: end if;
4140: end if;
4141: hr_utility.set_location('chk_pos_fte_sum_asg_fte end ' , 20);
4142: exception when others then
4143: p_position_type := null;
4144: p_organization_id := null;
4145: p_budgeted_fte := null;
4229: where position_id = p_position_id
4230: and effective_start_date between p_validation_start_date and p_validation_end_date;
4231: --
4232: begin
4233: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 201);
4234: hr_utility.set_location('chk_future_pos_asg_fte p_validation_start_date: '||p_validation_start_date, 202);
4235: hr_utility.set_location('chk_future_pos_asg_fte p_validation_end_date: '||p_validation_end_date, 202);
4236: if (p_assignment_id is not null and p_position_id is not null) then
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4230: and effective_start_date between p_validation_start_date and p_validation_end_date;
4231: --
4232: begin
4233: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 201);
4234: hr_utility.set_location('chk_future_pos_asg_fte p_validation_start_date: '||p_validation_start_date, 202);
4235: hr_utility.set_location('chk_future_pos_asg_fte p_validation_end_date: '||p_validation_end_date, 202);
4236: if (p_assignment_id is not null and p_position_id is not null) then
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4238: loop
4231: --
4232: begin
4233: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 201);
4234: hr_utility.set_location('chk_future_pos_asg_fte p_validation_start_date: '||p_validation_start_date, 202);
4235: hr_utility.set_location('chk_future_pos_asg_fte p_validation_end_date: '||p_validation_end_date, 202);
4236: if (p_assignment_id is not null and p_position_id is not null) then
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4238: loop
4239: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4235: hr_utility.set_location('chk_future_pos_asg_fte p_validation_end_date: '||p_validation_end_date, 202);
4236: if (p_assignment_id is not null and p_position_id is not null) then
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4238: loop
4239: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4240: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4241: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4242: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4243: --
4236: if (p_assignment_id is not null and p_position_id is not null) then
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4238: loop
4239: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4240: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4241: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4242: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4243: --
4244: chk_pos_fte_sum_asg_fte(
4237: for r1 in c1(p_assignment_id, p_position_id, p_validation_start_date, p_validation_end_date)
4238: loop
4239: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4240: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4241: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4242: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4243: --
4244: chk_pos_fte_sum_asg_fte(
4245: p_assignment_id => p_assignment_id,
4238: loop
4239: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4240: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4241: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4242: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4243: --
4244: chk_pos_fte_sum_asg_fte(
4245: p_assignment_id => p_assignment_id,
4246: p_position_id => p_position_id,
4253: p_realloc_fte => l_realloc_fte,
4254: p_position_fte => l_position_fte,
4255: p_total_asg_fte => l_total_asg_fte);
4256: --
4257: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4254: p_position_fte => l_position_fte,
4255: p_total_asg_fte => l_total_asg_fte);
4256: --
4257: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4262: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4255: p_total_asg_fte => l_total_asg_fte);
4256: --
4257: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4262: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4263: --
4256: --
4257: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4262: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4263: --
4264: if l_position_type = 'SHARED' then
4257: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4262: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4263: --
4264: if l_position_type = 'SHARED' then
4265: --
4258: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4259: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4260: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4261: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4262: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4263: --
4264: if l_position_type = 'SHARED' then
4265: --
4266: if l_budgeted_fte is not null or l_realloc_fte is not null then
4267: --
4268: l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4269: --
4270: if l_bgt_and_realloc_fte < l_total_asg_fte then
4271: hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
4272: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4273: pqh_utility.set_message_level_cd('W');
4274: pqh_utility.raise_error;
4275: return;
4268: l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4269: --
4270: if l_bgt_and_realloc_fte < l_total_asg_fte then
4271: hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
4272: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4273: pqh_utility.set_message_level_cd('W');
4274: pqh_utility.raise_error;
4275: return;
4276: end if;
4275: return;
4276: end if;
4277: else
4278: if l_position_fte < l_total_asg_fte then
4279: hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
4280: hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
4281: pqh_utility.set_message_level_cd('W');
4282: pqh_utility.raise_error;
4283: return;
4276: end if;
4277: else
4278: if l_position_fte < l_total_asg_fte then
4279: hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
4280: hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
4281: pqh_utility.set_message_level_cd('W');
4282: pqh_utility.raise_error;
4283: return;
4284: end if;
4286: --
4287: elsif l_position_type = 'SINGLE' then
4288: --
4289: if l_total_asg_fte > 1 then
4290: hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
4291: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4292: --- Fix For Bug # 10201563---
4293: -- pqh_utility.set_message_level_cd('E');
4294: if NVL(fnd_profile.value('GHR_POS_VALID_WARN_ERROR'),'E') = 'W' then
4287: elsif l_position_type = 'SINGLE' then
4288: --
4289: if l_total_asg_fte > 1 then
4290: hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
4291: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4292: --- Fix For Bug # 10201563---
4293: -- pqh_utility.set_message_level_cd('E');
4294: if NVL(fnd_profile.value('GHR_POS_VALID_WARN_ERROR'),'E') = 'W' then
4295: pqh_utility.set_message_level_cd('W');
4305: end loop;
4306: elsif (p_assignment_id is null and p_position_id is not null) then
4307: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
4308: loop
4309: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4310: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4311: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4312: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4313: --
4306: elsif (p_assignment_id is null and p_position_id is not null) then
4307: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
4308: loop
4309: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4310: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4311: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4312: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4313: --
4314: chk_pos_fte_sum_asg_fte(
4307: for r1 in c2(p_position_id, p_validation_start_date, p_validation_end_date)
4308: loop
4309: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4310: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4311: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4312: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4313: --
4314: chk_pos_fte_sum_asg_fte(
4315: p_assignment_id => p_assignment_id,
4308: loop
4309: hr_utility.set_location('chk_future_pos_asg_fte r1.effective_start_date : '||r1.effective_start_date, 203);
4310: hr_utility.set_location('chk_future_pos_asg_fte p_assignment_id : '||p_assignment_id, 203);
4311: hr_utility.set_location('chk_future_pos_asg_fte p_position_id : '||p_position_id, 203);
4312: hr_utility.set_location('chk_future_pos_asg_fte p_default_asg_fte : '||p_default_asg_fte, 203);
4313: --
4314: chk_pos_fte_sum_asg_fte(
4315: p_assignment_id => p_assignment_id,
4316: p_position_id => p_position_id,
4323: p_realloc_fte => l_realloc_fte,
4324: p_position_fte => l_position_fte,
4325: p_total_asg_fte => l_total_asg_fte);
4326: --
4327: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4324: p_position_fte => l_position_fte,
4325: p_total_asg_fte => l_total_asg_fte);
4326: --
4327: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4332: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4325: p_total_asg_fte => l_total_asg_fte);
4326: --
4327: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4332: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4333: --
4326: --
4327: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4332: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4333: --
4334: if l_position_type = 'SHARED' then
4327: hr_utility.set_location('chk_future_pos_asg_fte l_position_type : '||l_position_type, 204);
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4332: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4333: --
4334: if l_position_type = 'SHARED' then
4335: --
4328: hr_utility.set_location('chk_future_pos_asg_fte l_organization_id : '|| l_organization_id, 204);
4329: hr_utility.set_location('chk_future_pos_asg_fte l_budgeted_fte : '|| l_budgeted_fte, 204);
4330: hr_utility.set_location('chk_future_pos_asg_fte l_realloc_fte : '|| l_realloc_fte, 204);
4331: hr_utility.set_location('chk_future_pos_asg_fte l_position_fte : '|| l_position_fte, 204);
4332: hr_utility.set_location('chk_future_pos_asg_fte l_total_asg_fte : '|| l_total_asg_fte, 204);
4333: --
4334: if l_position_type = 'SHARED' then
4335: --
4336: if l_budgeted_fte is not null or l_realloc_fte is not null then
4337: --
4338: l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4339: --
4340: if l_bgt_and_realloc_fte < l_total_asg_fte then
4341: hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
4342: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4343: pqh_utility.set_message_level_cd('W');
4344: pqh_utility.raise_error;
4345: return;
4338: l_bgt_and_realloc_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4339: --
4340: if l_bgt_and_realloc_fte < l_total_asg_fte then
4341: hr_utility.set_message(8302,'PQH_SHARED_FUT_BFTE_LT_AFTE');
4342: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4343: pqh_utility.set_message_level_cd('W');
4344: pqh_utility.raise_error;
4345: return;
4346: end if;
4345: return;
4346: end if;
4347: else
4348: if l_position_fte < l_total_asg_fte then
4349: hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
4350: hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
4351: pqh_utility.set_message_level_cd('W');
4352: pqh_utility.raise_error;
4353: return;
4346: end if;
4347: else
4348: if l_position_fte < l_total_asg_fte then
4349: hr_utility.set_message(8302,'PQH_SHARED_FUT_PFTE_LT_AFTE');
4350: hr_utility.set_message_token('FUTURE_ASG_DATE',r1.effective_start_date );
4351: pqh_utility.set_message_level_cd('W');
4352: pqh_utility.raise_error;
4353: return;
4354: end if;
4356: --
4357: elsif l_position_type = 'SINGLE' then
4358: --
4359: if l_total_asg_fte > 1 then
4360: hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
4361: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4362: --- Fix For Bug # 10201563---
4363: -- pqh_utility.set_message_level_cd('E');
4364: if NVL(fnd_profile.value('GHR_POS_VALID_WARN_ERROR'),'E') = 'W' then
4357: elsif l_position_type = 'SINGLE' then
4358: --
4359: if l_total_asg_fte > 1 then
4360: hr_utility.set_message(8302,'PQH_SINGLE_POS_FUTURE_ASG');
4361: hr_utility.set_message_token('FUTURE_ASG_DATE', r1.effective_start_date);
4362: --- Fix For Bug # 10201563---
4363: -- pqh_utility.set_message_level_cd('E');
4364: if NVL(fnd_profile.value('GHR_POS_VALID_WARN_ERROR'),'E') = 'W' then
4365: pqh_utility.set_message_level_cd('W');
4465: --
4466: if l_budgeted_fte is not null or l_realloc_fte is not null then
4467: l_fte := nvl(l_budgeted_fte,0) + nvl(l_realloc_fte,0);
4468: else
4469: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 131);
4470: l_fte := nvl(l_pos_fte,0);
4471: end if;
4472: end if;
4473: hr_utility.set_location('l_fte : '||l_fte, 131);
4469: hr_utility.set_location('l_pos_fte : '||l_pos_fte, 131);
4470: l_fte := nvl(l_pos_fte,0);
4471: end if;
4472: end if;
4473: hr_utility.set_location('l_fte : '||l_fte, 131);
4474: return l_fte;
4475: end;
4476: --
4477: --
4522: l_asg_fte number := 0;
4523: l_total_asg_fte number := 0;
4524: --
4525: begin
4526: hr_utility.set_location('inside reserved_error',10);
4527: open c1;
4528: fetch c1 into l_blank_res_pos_fte;
4529: close c1;
4530: hr_utility.set_location('inside reserved_error',20);
4526: hr_utility.set_location('inside reserved_error',10);
4527: open c1;
4528: fetch c1 into l_blank_res_pos_fte;
4529: close c1;
4530: hr_utility.set_location('inside reserved_error',20);
4531: --
4532: l_unreserved_fte := unreserved_fte(p_position_id, p_effective_start_date);
4533: hr_utility.set_location('inside reserved_error',30);
4534: --
4529: close c1;
4530: hr_utility.set_location('inside reserved_error',20);
4531: --
4532: l_unreserved_fte := unreserved_fte(p_position_id, p_effective_start_date);
4533: hr_utility.set_location('inside reserved_error',30);
4534: --
4535: l_reserved_overused := reserved_overused(p_position_id, p_effective_start_date);
4536: hr_utility.set_location('inside reserved_error',40);
4537:
4532: l_unreserved_fte := unreserved_fte(p_position_id, p_effective_start_date);
4533: hr_utility.set_location('inside reserved_error',30);
4534: --
4535: l_reserved_overused := reserved_overused(p_position_id, p_effective_start_date);
4536: hr_utility.set_location('inside reserved_error',40);
4537:
4538: open c_sum_asg_fte(p_position_id);
4539: fetch c_sum_asg_fte into l_sum_asg_fte;
4540: close c_sum_asg_fte;
4538: open c_sum_asg_fte(p_position_id);
4539: fetch c_sum_asg_fte into l_sum_asg_fte;
4540: close c_sum_asg_fte;
4541: --
4542: hr_utility.set_location('inside reserved_error',50);
4543: l_asg_fte := assignment_fte(p_assignment_id, p_effective_start_date);
4544: --
4545: hr_utility.set_location('inside reserved_error',60);
4546: l_total_asg_fte := nvl(l_sum_asg_fte,0) + nvl(l_asg_fte,0)
4541: --
4542: hr_utility.set_location('inside reserved_error',50);
4543: l_asg_fte := assignment_fte(p_assignment_id, p_effective_start_date);
4544: --
4545: hr_utility.set_location('inside reserved_error',60);
4546: l_total_asg_fte := nvl(l_sum_asg_fte,0) + nvl(l_asg_fte,0)
4547: + l_reserved_overused + p_default_asg_fte;
4548: hr_utility.set_location('inside reserved_error',70);
4549: --
4544: --
4545: hr_utility.set_location('inside reserved_error',60);
4546: l_total_asg_fte := nvl(l_sum_asg_fte,0) + nvl(l_asg_fte,0)
4547: + l_reserved_overused + p_default_asg_fte;
4548: hr_utility.set_location('inside reserved_error',70);
4549: --
4550: --
4551: if (l_blank_res_pos_fte < 0) then
4552: hr_utility.set_location('POSITION RESERVED2', 114);
4548: hr_utility.set_location('inside reserved_error',70);
4549: --
4550: --
4551: if (l_blank_res_pos_fte < 0) then
4552: hr_utility.set_location('POSITION RESERVED2', 114);
4553: pqh_utility.set_message(8302,'PQH_POS_RESERVED',p_organization_id);
4554: pqh_utility.raise_error;
4555: elsif ((l_total_asg_fte <= (l_blank_res_pos_fte + l_unreserved_fte))
4556: or (nvl(l_blank_res_pos_fte,0) >=
4554: pqh_utility.raise_error;
4555: elsif ((l_total_asg_fte <= (l_blank_res_pos_fte + l_unreserved_fte))
4556: or (nvl(l_blank_res_pos_fte,0) >=
4557: nvl(l_asg_fte,0) + nvl(p_default_asg_fte,0))) then
4558: hr_utility.set_location('PQH_ANONYM_POS_RESERVED', 115);
4559: pqh_utility.set_message(8302,'PQH_ANONYM_POS_RESERVED',p_organization_id);
4560: pqh_utility.raise_error;
4561: else
4562: hr_utility.set_location('PQH_POS_RESERVED', 116);
4558: hr_utility.set_location('PQH_ANONYM_POS_RESERVED', 115);
4559: pqh_utility.set_message(8302,'PQH_ANONYM_POS_RESERVED',p_organization_id);
4560: pqh_utility.raise_error;
4561: else
4562: hr_utility.set_location('PQH_POS_RESERVED', 116);
4563: pqh_utility.set_message(8302,'PQH_POS_RESERVED',p_organization_id);
4564: pqh_utility.raise_error;
4565: end if;
4566: hr_utility.set_location('inside reserved_error',80);
4562: hr_utility.set_location('PQH_POS_RESERVED', 116);
4563: pqh_utility.set_message(8302,'PQH_POS_RESERVED',p_organization_id);
4564: pqh_utility.raise_error;
4565: end if;
4566: hr_utility.set_location('inside reserved_error',80);
4567: --
4568: end;
4569: --
4570: function get_pc_topnode (p_business_group_id in number,
4791: function is_budget_commt_element(p_budget_id number,
4792: p_element_type_id number) return boolean is
4793: l_check varchar2(10);
4794: begin
4795: hr_utility.set_location('budget id'||p_budget_id,1);
4796: hr_utility.set_location('element type id'||p_element_type_id,2);
4797: select 'X'
4798: into l_check
4799: from pqh_bdgt_cmmtmnt_elmnts
4792: p_element_type_id number) return boolean is
4793: l_check varchar2(10);
4794: begin
4795: hr_utility.set_location('budget id'||p_budget_id,1);
4796: hr_utility.set_location('element type id'||p_element_type_id,2);
4797: select 'X'
4798: into l_check
4799: from pqh_bdgt_cmmtmnt_elmnts
4800: where budget_id = p_budget_id
4808: end if;
4809:
4810: exception
4811: when no_data_found then
4812: hr_utility.set_location('no data',3);
4813: return false;
4814: when too_many_rows then
4815: return true;
4816: when others then
4853: l_message_level varchar2(10);
4854: l_proc varchar2(72) := 'pqh_psf_bus.chk_position_budget';
4855: begin
4856: --
4857: hr_utility.set_location(l_proc||'Entering',1);
4858: get_assignment_info(p_assignment_id, p_effective_date, l_asg_position_id, l_asg_organization_id);
4859: --
4860: hr_utility.set_location(l_proc,2);
4861: --
4856: --
4857: hr_utility.set_location(l_proc||'Entering',1);
4858: get_assignment_info(p_assignment_id, p_effective_date, l_asg_position_id, l_asg_organization_id);
4859: --
4860: hr_utility.set_location(l_proc,2);
4861: --
4862: if (l_asg_position_id is null) then
4863: hr_utility.set_location(l_proc,3);
4864: return;
4859: --
4860: hr_utility.set_location(l_proc,2);
4861: --
4862: if (l_asg_position_id is null) then
4863: hr_utility.set_location(l_proc,3);
4864: return;
4865: end if;
4866: --
4867: --
4865: end if;
4866: --
4867: --
4868: if (p_called_from = 'ASG' and p_old_position_id = l_asg_position_id ) then
4869: hr_utility.set_location(l_proc,4);
4870: return;
4871: end if;
4872: --
4873: --
4872: --
4873: --
4874: l_message_level := get_cbr_rule_level(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_asg_organization_id);
4875: if (l_message_level not in ('E','W')) then
4876: hr_utility.set_location(l_proc,5);
4877: return;
4878: end if;
4879: --
4880:
4877: return;
4878: end if;
4879: --
4880:
4881: hr_utility.set_location(l_proc||l_asg_position_id,51);
4882: --
4883: get_position_pc_budget_info(
4884: p_position_id => l_asg_position_id,
4885: p_budget_unit_cd => 'MONEY',
4892: p_budget_end_date => l_budget_end_date,
4893: p_currency_code => l_currency_code);
4894: if ( l_budget_version_id is null ) then
4895:
4896: hr_utility.set_location(l_proc,6);
4897: return;
4898: end if;
4899: hr_utility.set_location(l_proc,61);
4900: --
4895:
4896: hr_utility.set_location(l_proc,6);
4897: return;
4898: end if;
4899: hr_utility.set_location(l_proc,61);
4900: --
4901: --
4902:
4903: --
4922: pqh_utility.raise_error;
4923: return;
4924: end if;
4925: --
4926: hr_utility.set_location(l_proc,7);
4927: --
4928: end if;
4929: --
4930: --
4930: --
4931:
4932: --
4933: --
4934: hr_utility.set_location(l_proc,71);
4935: pqh_commitment_pkg.refresh_asg_ele_commitments (
4936: p_assignment_id,
4937: p_effective_date,
4938: p_element_type_id,
4936: p_assignment_id,
4937: p_effective_date,
4938: p_element_type_id,
4939: p_input_value_id);
4940: hr_utility.set_location(l_proc,8);
4941: --
4942: --
4943:
4944: --
4954: p_actual_amount => l_pos_actuals_amt,
4955: p_commitment_amount => l_pos_commitment_amt,
4956: p_total_amount => l_pos_actual_cmmt_total_amt
4957: );
4958: hr_utility.set_location(l_proc,9);
4959: --
4960: --
4961: l_pos_reallocated_out_amt :=
4962: pqh_reallocation_pkg.get_reallocated_money(
4997: + nvl(l_pos_reallocated_in_amt,0)
4998: - nvl(l_pos_reserved_amt,0);
4999: --
5000: --
5001: hr_utility.set_location(l_proc,10);
5002: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
4999: --
5000: --
5001: hr_utility.set_location(l_proc,10);
5002: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5000: --
5001: hr_utility.set_location(l_proc,10);
5002: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5001: hr_utility.set_location(l_proc,10);
5002: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5009: --
5002: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5009: --
5010: --
5003: hr_utility.set_location(l_proc || ' - Budgeted Amt : '||l_pos_budgeted_amt,10);
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5009: --
5010: --
5011: if (l_pos_under_budgeted_amt < 0) then
5004: hr_utility.set_location(l_proc || ' - Actual Amt : '||l_pos_actuals_amt,10);
5005: hr_utility.set_location(l_proc || ' - Commitment Amt : '||l_pos_commitment_amt,10);
5006: hr_utility.set_location(l_proc || ' - Realloc Out Amt : '||l_pos_reallocated_out_amt,10);
5007: hr_utility.set_location(l_proc || ' - Realloc In Amt : '||l_pos_reallocated_in_amt,10);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5009: --
5010: --
5011: if (l_pos_under_budgeted_amt < 0) then
5012: hr_utility.set_location(l_proc||'Leaving with error',11);
5008: hr_utility.set_location(l_proc || ' - Reserved Amt : '||l_pos_reserved_amt,10);
5009: --
5010: --
5011: if (l_pos_under_budgeted_amt < 0) then
5012: hr_utility.set_location(l_proc||'Leaving with error',11);
5013:
5014: pqh_utility.set_message(8302,'PQH_SUM_ASG_AMT_GT_BGT_AMT',l_asg_organization_id);
5015: -- pqh_utility.set_message_level_cd(l_message_level);
5016: pqh_utility.set_message_token('POSITION',
5029: */
5030: pqh_utility.raise_error;
5031: end if;
5032: --
5033: hr_utility.set_location(l_proc||'Leaving',12);
5034: --
5035: exception
5036: when others then
5037: hr_utility.set_location(substr(sqlerrm,1,120),13);
5033: hr_utility.set_location(l_proc||'Leaving',12);
5034: --
5035: exception
5036: when others then
5037: hr_utility.set_location(substr(sqlerrm,1,120),13);
5038: raise;
5039: end;
5040:
5041: end PQH_PSF_BUS;