[Home] [Help]
PACKAGE BODY: APPS.PQH_BDGT_ACTUAL_CMMTMNT_PKG
Source
1 Package body pqh_bdgt_actual_cmmtmnt_pkg as
2 /* $Header: pqbgtact.pkb 120.6 2007/01/05 16:08:47 krajarat noship $ */
3 --
4 -- Throughout this Package 0 is returned if in the validating functions
5 -- the validation is succesful. -1 means error;
6 --
7 --------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := 'pqh_bdgt_actual_cmmtmnt_pkg.';
10 --
11 --
12 function get_factor( p_from_start_date in date,
13 p_from_end_date in date,
14 p_to_start_date in date,
15 p_to_end_date in date )
16 RETURN NUMBER
17 IS
18 begin
19 If p_from_start_date = p_to_start_date AND p_from_end_date = p_to_end_date then
20 RETURN 1;
21 End if;
22
23 Return ( (p_to_end_date - p_to_start_date + 1) /
24 (p_from_end_date - p_from_start_date + 1) );
25 end;
26
27 FUNCTION get_last_payroll_dt (
28 p_assignment_id NUMBER,
29 p_start_date DATE,
30 p_end_date DATE ) RETURN Date IS
31
32 /*
33 Original Cursor, simply returns the last payroll run date
34 for the payroll id found associated with the assignment
35
36 cursor csr_last_dt is
37 Select NVL( max( tp.end_date), p_start_date )
38 From per_time_periods tp,
39 pay_payroll_actions ppa,
40 per_all_assignments_f aaf
41 Where aaf.assignment_id = p_assignment_id
42 and (aaf.effective_end_date >= p_start_date and aaf.effective_start_date <= p_end_date)
43 and ppa.payroll_id = aaf.payroll_id
44 and (ppa.date_earned between tp.start_date and tp.end_date )
45 AND tp.payroll_id = aaf.payroll_id
46 AND (tp.start_date <= least(p_end_date,aaf.effective_end_date)
47 and tp.end_date >= greatest(p_start_date,aaf.effective_start_date) )
48 and tp.time_period_id = ppa.time_period_id;
49 */
50
51 /*
52 New Cursor uses the latest sequenced payroll action performed on the assignment
53 */
54 cursor csr_last_dt is
55 Select NVL( tp.end_date, p_start_date )
56 from pay_assignment_actions paa,
57 per_all_assignments_f paf,
58 pay_payroll_actions ppa,
59 pay_action_classifications pac,
60 per_time_periods tp
61 where paf.assignment_id = p_assignment_id
62 and (paf.effective_end_date >= p_start_date
63 and paf.effective_start_date <= p_end_date)
64 and paa.assignment_id = paf.assignment_id
65 and ppa.payroll_action_id = paa.payroll_action_id
66 and ppa.effective_date +0 between
67 greatest(p_start_date,paf.effective_start_date)
68 and least(p_end_date,paf.effective_end_date)
69 and pac.action_type = ppa.action_type
70 and pac.classification_name = 'SEQUENCED'
71 and ((nvl(paa.run_type_id, ppa.run_type_id) is null and
72 paa.source_action_id is null)
73 or (nvl(paa.run_type_id, ppa.run_type_id) is not null and
74 paa.source_action_id is not null )
75 or (ppa.action_type = 'V' and ppa.run_type_id is null and
76 paa.run_type_id is not null and
77 paa.source_action_id is null))
78 and tp.time_period_id = ppa.time_period_id
79 order by paa.action_sequence desc;
80
81 l_last_date date;
82
83 BEGIN
84 hr_utility.set_location('Payroll calculation, dates passed: '||p_start_date ||' - '||p_end_date||' > '||p_assignment_id, 71);
85 OPEN csr_last_dt;
86 FETCH csr_last_dt INTO l_last_date;
87 CLOSE csr_last_dt;
88
89 return l_last_date;
90
91 END get_last_payroll_dt;
92
93 PROCEDURE Validate_budget(p_budget_version_id in number,
94 p_budget_id out nocopy number)
95 is
96 --
97 Cursor csr_bdgt is
98 Select bvr.budget_id
99 From pqh_budget_versions bvr
100 Where bvr.budget_version_id = p_budget_version_id;
101 --
102 --
103 l_proc varchar2(72) := g_package || 'Validate_budget';
104 --
105 Begin
106 --
107 hr_utility.set_location('Entering :'||l_proc,5);
108 --
109 -- VALIDATE IF THIS IS A VALID BUDGET IN PQH_BUDGETS
110 --
111 Open csr_bdgt;
112 Fetch csr_bdgt into p_budget_id;
113 If csr_bdgt%notfound then
114 --Raise exception
115 --
116 Close csr_bdgt;
117 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BUDGET_VERSION');
118 APP_EXCEPTION.RAISE_EXCEPTION;
119 --
120 End if;
121 --
122 Close csr_bdgt;
123 --
124 hr_utility.set_location('Leaving :'||l_proc,10);
125 EXCEPTION
126 WHEN OTHERS THEN
127 p_budget_id := null;
128 hr_utility.set_location('Exception :'||l_proc,15);
129 raise;
130 End;
131 ------------------------------------------------------------------------------
132 PROCEDURE Validate_position(p_position_id in number,
133 p_budget_version_id in number)
134 is
135 --
136 Cursor csr_pos is
137 Select null
138 From hr_all_positions_f
139 Where position_id = p_position_id;
140 --
141 --
142 -- The foll cursor checks if the passed position is present in the passed
143 -- budget version.
144 --
145 Cursor csr_positions_in_bdgt is
146 Select null
147 From pqh_budget_details bdt,pqh_budget_versions bvr
148 Where bvr.budget_version_id = p_budget_version_id
149 AND bvr.budget_version_id = bdt.budget_version_id
150 AND bdt.position_id = p_position_id;
151 --
152 l_dummy varchar2(1);
153 --
154 l_proc varchar2(72) := g_package ||'Validate_position';
155 --
156 Begin
157 --
158 hr_utility.set_location('Entering :'||l_proc,5);
159 --
160 --
161 -- validate if the position is in hr_all_positions_f
162 --
163 Open csr_pos;
164 Fetch csr_pos into l_dummy;
165 If csr_pos%notfound then
166 Close csr_pos;
167 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_POSITION');
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 End if;
170 Close csr_pos;
171 --
172 -- Also check if the position belongs to the passed budget version
173 -- and return the budget id of the budget version
174 Open csr_positions_in_bdgt;
175 Fetch csr_positions_in_bdgt into l_dummy;
176 If csr_positions_in_bdgt%notfound then
177 Close csr_positions_in_bdgt;
178 FND_MESSAGE.SET_NAME('PQH','PQH_POSITION_NOT_IN_BDGT_VER');
179 APP_EXCEPTION.RAISE_EXCEPTION;
180 End if;
181 --
182 Close csr_positions_in_bdgt;
183 --
184 hr_utility.set_location('Leaving :'||l_proc,10);
185 EXCEPTION
186 WHEN OTHERS THEN
187 hr_utility.set_location('Exception :'||l_proc,15);
188 raise;
189 End;
190 --------------------------------------------------------------------------
191 PROCEDURE Validate_job(p_job_id in number,
192 p_budget_version_id in number)
193 is
194 --
195 Cursor csr_job is
196 Select null
197 From per_jobs
198 Where job_id = p_job_id;
199 --
200 -- The foll cursor checks if the passed job is present in the passed
201 -- budget version.
202 --
203 Cursor csr_jobs_in_bdgt is
204 Select null
205 From pqh_budget_details bdt,pqh_budget_versions bvr
206 Where bvr.budget_version_id = p_budget_version_id
207 AND bvr.budget_version_id = bdt.budget_version_id
208 AND bdt.job_id = p_job_id;
209 --
210 l_dummy varchar2(1);
211 --
212 l_proc varchar2(72) := g_package ||'Validate_job';
213 --
214 Begin
215 --
216 hr_utility.set_location('Entering :'||l_proc,5);
217 --
218 --
219 -- validate if the job is in per_jobs
220 --
221 Open csr_job;
222 Fetch csr_job into l_dummy;
223 If csr_job%notfound then
224 --Write into error log
225 Close csr_job;
226 --
227 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY_TYPE');
228 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','JOB'));
229 APP_EXCEPTION.RAISE_EXCEPTION;
230 --
231 End if;
232 --
233 Close csr_job;
234 --
235 -- Also check if the job belongs to the passed budget version
236 -- and return the budget id of the budget version
237 --
238 Open csr_jobs_in_bdgt;
239 Fetch csr_jobs_in_bdgt into l_dummy;
240 If csr_jobs_in_bdgt%notfound then
241 Close csr_jobs_in_bdgt;
242 FND_MESSAGE.SET_NAME('PQH','PQH_ENT_TYPE_NOT_IN_BDGT_VER');
243 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','JOB'));
244 APP_EXCEPTION.RAISE_EXCEPTION;
245 End if;
246 Close csr_jobs_in_bdgt;
247 hr_utility.set_location('Leaving :'||l_proc,10);
248 --
249 EXCEPTION
250 WHEN OTHERS THEN
251 hr_utility.set_location('Exception :'||l_proc,15);
252 raise;
253 End;
254 --------------------------------------------------------------------------
255 PROCEDURE Validate_grade(p_grade_id in number,
256 p_budget_version_id in number)
257 is
258 --
259 Cursor csr_grade is
260 Select null
261 From per_grades
262 Where grade_id = p_grade_id;
263 --
264 -- The foll cursor checks if the passed grade is present in the passed
265 -- budget version.
266 --
267 Cursor csr_grades_in_bdgt is
268 Select null
269 From pqh_budget_details bdt,pqh_budget_versions bvr
270 Where bvr.budget_version_id = p_budget_version_id
271 AND bvr.budget_version_id = bdt.budget_version_id
272 AND bdt.grade_id = p_grade_id;
273 --
274 l_dummy varchar2(1);
275 --
276 l_proc varchar2(72) := g_package ||'Validate_grade';
277 --
278 Begin
279 --
280 hr_utility.set_location('Entering :'||l_proc,5);
281 --
282 -- validate if the grade is in per_grades
283 --
284 Open csr_grade;
285 Fetch csr_grade into l_dummy;
286 If csr_grade%notfound then
287 --Write into error log
288 Close csr_grade;
289 --
290 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY_TYPE');
291 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','GRADE'));
292 APP_EXCEPTION.RAISE_EXCEPTION;
293 --
294 End if;
295 Close csr_grade;
296 -- Also check if the grade belongs to the passed budget version
297 -- and return the budget id of the budget version
298 --
299 Open csr_grades_in_bdgt;
300 Fetch csr_grades_in_bdgt into l_dummy;
301 If csr_grades_in_bdgt%notfound then
302 Close csr_grades_in_bdgt;
303 FND_MESSAGE.SET_NAME('PQH','PQH_ENT_TYPE_NOT_IN_BDGT_VER');
304 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','GRADE'));
305 APP_EXCEPTION.RAISE_EXCEPTION;
306 End if;
307 Close csr_grades_in_bdgt;
308 --
309 hr_utility.set_location('Leaving :'||l_proc,10);
310 --
311 EXCEPTION
312 WHEN OTHERS THEN
313 hr_utility.set_location('Exception :'||l_proc,15);
314 raise;
315 End;
316 --------------------------------------------------------------------------
317 PROCEDURE Validate_organization(p_organization_id in number,
318 p_budget_version_id in number)
319 is
320 --
321 Cursor csr_organization is
322 Select null
323 From hr_organization_units
324 Where organization_id = p_organization_id;
325 --
326 --
327 -- The foll cursor checks if the passed organization is present in the passed
328 -- budget version.
329 --
330 Cursor csr_orgs_in_bdgt is
331 Select null
332 From pqh_budget_details bdt,pqh_budget_versions bvr
333 Where bvr.budget_version_id = p_budget_version_id
334 AND bvr.budget_version_id = bdt.budget_version_id
335 AND bdt.organization_id = p_organization_id;
336 --
337 l_dummy varchar2(1);
338 --
339 l_proc varchar2(72) := g_package ||'Validate_organization';
340 --
341 Begin
342 --
343 hr_utility.set_location('Entering :'||l_proc,5);
344 --
345 -- validate if the organization is in hr_organization_units
346 --
347 Open csr_organization;
348 Fetch csr_organization into l_dummy;
349 If csr_organization%notfound then
350 --Write into error log
351 Close csr_organization;
352 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY_TYPE');
353 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','ORGANIZATION'));
354 APP_EXCEPTION.RAISE_EXCEPTION;
355 --
356 End if;
357 Close csr_organization;
358 -- Also check if the organization belongs to the passed budget version
359 -- and return the budget id of the budget version
360 --
361 Open csr_orgs_in_bdgt;
362 Fetch csr_orgs_in_bdgt into l_dummy;
363 If csr_orgs_in_bdgt%notfound then
364 Close csr_orgs_in_bdgt;
365 FND_MESSAGE.SET_NAME('PQH','PQH_ENT_TYPE_NOT_IN_BDGT_VER');
366 FND_MESSAGE.SET_TOKEN('ENTITY',hr_general.decode_lookup('PQH_BUDGET_ENTITY','ORGANIZATION'));
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 End if;
369 Close csr_orgs_in_bdgt;
370 --
371 hr_utility.set_location('Leaving :'||l_proc,10);
372 --
373 EXCEPTION
374 WHEN OTHERS THEN
375 hr_utility.set_location('Exception :'||l_proc,15);
376 raise;
377 End;
378 --------------------------------------------------------------------------
379 PROCEDURE Validate_assignment(p_assignment_id in number)
380 is
381 --
382 Cursor csr_assg is
383 Select null
384 From per_all_assignments_f
385 Where assignment_id = p_assignment_id;
386 --
387 l_dummy varchar2(1);
388 l_proc varchar2(72) := g_package ||'Validate_assignment';
389 --
390 Begin
391 --
392 hr_utility.set_location('Entering :'||l_proc,5);
393 --
394 Open csr_assg;
395 Fetch csr_assg into l_dummy;
396 If csr_assg%notfound then
397 --Raise error
398 Close csr_assg;
399 FND_MESSAGE.SET_NAME('PQH','PQH_ACT_INVALID_ASSIGNMENT');
400 APP_EXCEPTION.RAISE_EXCEPTION;
401 --
402 End if;
403 hr_utility.set_location('Leaving :'||l_proc,10);
404 Close csr_assg;
405 --
406 EXCEPTION
407 WHEN OTHERS THEN
408 hr_utility.set_location('Exception :'||l_proc,15);
409 raise;
410 End;
411 --------------------------------------------------------------------------
412 PROCEDURE Validate_element_type(p_element_type_id in number)
413 is
414 --
415 Cursor csr_elmnt_type is
416 Select null From pay_element_types_f
417 Where element_type_id = p_element_type_id;
418 --
419 l_dummy varchar2(1);
420 l_proc varchar2(72) := g_package ||'Validate_element_type';
421 --
422 Begin
423 --
424 hr_utility.set_location('Entering :'||l_proc,5);
425 --
426 if p_element_type_id is not null then
427 Open csr_elmnt_type;
428 Fetch csr_elmnt_type into l_dummy;
429 If csr_elmnt_type%notfound then
430 Close csr_elmnt_type;
431 FND_MESSAGE.SET_NAME('PQH','PQH_ACT_INVALID_ELMNT_TYPE');
432 APP_EXCEPTION.RAISE_EXCEPTION;
433 End if;
434 Close csr_elmnt_type;
435 else
436 FND_MESSAGE.SET_NAME('PQH','PQH_ACT_INVALID_ELMNT_TYPE');
437 APP_EXCEPTION.RAISE_EXCEPTION;
438 end if;
439 --
440 hr_utility.set_location('Leaving :'||l_proc,10);
441 EXCEPTION
442 WHEN OTHERS THEN
443 hr_utility.set_location('Leaving :'||l_proc,15);
444 raise;
445 End;
446 -------------------------------------------------------------------------------
447 Procedure Validate_unit_of_measure(p_unit_of_measure_id in number,
448 p_unit_of_measure_desc out nocopy varchar2)
449 is
450 --
451 Cursor csr_uom is
452 Select system_type_cd
453 From per_shared_types
454 Where shared_type_id = p_unit_of_measure_id
455 AND lookup_type = 'BUDGET_MEASUREMENT_TYPE';
456 --
457 l_proc varchar2(72) := g_package||'Validate_unit_of_measure';
458 Begin
459 hr_utility.set_location('Entering:'||l_proc, 5);
460 -- Check if the unit of measure exists in per_shared_types
461 Open csr_uom;
462 Fetch csr_uom into p_unit_of_measure_desc;
463 If csr_uom%notfound then
464 Close csr_uom;
465 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BUDGET_UOM');
466 APP_EXCEPTION.RAISE_EXCEPTION;
467 End if;
468 Close csr_uom;
469 hr_utility.set_location('Leaving:'||l_proc, 10);
470 --
471 Exception
472 When others then
473 p_unit_of_measure_desc := null;
474 hr_utility.set_location('Exception:'||l_proc, 15);
475 raise;
476 End;
477 -----------------------------------------------------------------------
478 Procedure Validate_uom_in_budget(p_unit_of_measure_id in number,
479 p_budget_id in number)
480 is
481 --
482 Cursor csr_uom_in_bdgt is
483 Select null
484 From pqh_budgets
485 Where budget_id = p_budget_id
486 AND (budget_unit1_id = p_unit_of_measure_id OR
487 budget_unit2_id = p_unit_of_measure_id OR
488 budget_unit3_id = p_unit_of_measure_id);
489 --
490 l_proc varchar2(72) := g_package||'Validate_uom_in_budget';
491 l_dummy varchar2(1);
492 --
493 Begin
494 hr_utility.set_location('Entering:'||l_proc, 5);
495 -- Check if the budget includes the unit of measure
496 Open csr_uom_in_bdgt;
497 Fetch csr_uom_in_bdgt into l_dummy;
498 If csr_uom_in_bdgt%notfound then
499 Close csr_uom_in_bdgt;
500 FND_MESSAGE.SET_NAME('PQH','PQH_UOM_NOT_IN_BDGT');
501 APP_EXCEPTION.RAISE_EXCEPTION;
502 End if;
503 Close csr_uom_in_bdgt;
504 hr_utility.set_location('Leaving:'||l_proc, 10);
505 Exception When others then
506 hr_utility.set_location('Exception:'||l_proc, 15);
507 raise;
508 End;
509 -------------------------------------------------------------------------------
510 PROCEDURE Validate_actuals_dates(p_actuals_start_dt in date,
511 p_actuals_end_dt in date,
512 p_budget_id in number) is
513 --
514 l_budget_start_date pqh_budgets.budget_start_date%type;
515 l_budget_end_date pqh_budgets.budget_end_date%type;
516 --
517 l_proc varchar2(72) := g_package||'Validate_actuals_dates';
518 --
519 Cursor csr_bdgt is
520 Select budget_start_date,budget_end_date
521 From pqh_budgets
522 Where budget_id = p_budget_id;
523 --
524 Begin
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 -- 1) Check if p_actuals_end_dt > p_actuals_start_dt.
527 If p_actuals_end_dt < p_actuals_start_dt then
528 FND_MESSAGE.SET_NAME('PQH','PQH_END_DT_LESS_THAN_START_DT');
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 End if;
531 Open csr_bdgt;
532 Fetch csr_bdgt into l_budget_start_date,l_budget_end_date;
533 Close csr_bdgt;
534 -- 2) Check if p_actuals_start_dt < l_budget_start_date
535 -- 3) Check if p_actual_start_dt > l_budget_end_date
536 if p_actuals_start_dt < l_budget_start_date
537 OR p_actuals_start_dt > l_budget_end_date then
538 --
539 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ACTUALS_START_DT');
540 APP_EXCEPTION.RAISE_EXCEPTION;
541 End if;
542 -- 4) Check if p_actuals_end_dt > l_budget_end_date
543 if p_actuals_end_dt > l_budget_end_date then
544 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ACTUALS_END_DT');
545 APP_EXCEPTION.RAISE_EXCEPTION;
546 End if;
547 hr_utility.set_location('Leaving:'||l_proc, 10);
548 Exception When others then
549 hr_utility.set_location('Exception:'||l_proc, 15);
550 raise;
551 End;
552 -------------------------------------------------------------------------
553 PROCEDURE Validate_commitment_dates(p_cmmtmnt_start_dt in date,
554 p_cmmtmnt_end_dt in date,
555 p_budget_id in number) is
556 --
557 l_budget_start_date pqh_budgets.budget_start_date%type;
558 l_budget_end_date pqh_budgets.budget_end_date%type;
559 --
560 l_proc varchar2(72) := g_package||'Validate_commitment_dates';
561 --
562 Cursor csr_bdgt is
563 Select budget_start_date,budget_end_date
564 From pqh_budgets
565 Where budget_id = p_budget_id;
566 --
567 Begin
568 --
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 If p_cmmtmnt_end_dt < p_cmmtmnt_start_dt then
572 FND_MESSAGE.SET_NAME('PQH','PQH_END_DT_LESS_THAN_START_DT');
573 APP_EXCEPTION.RAISE_EXCEPTION;
574 End if;
575 Open csr_bdgt;
576 Fetch csr_bdgt into l_budget_start_date,l_budget_end_date;
577 Close csr_bdgt;
578 -- 2) Check if p_cmmtmnt_start_dt < p_budget_start_date
579 if p_cmmtmnt_start_dt < l_budget_start_date then
580 FND_MESSAGE.SET_NAME('PQH','PQH_CMT_START_BEF_BDGT_START');
581 APP_EXCEPTION.RAISE_EXCEPTION;
582 End if;
583 -- 3) Check if p_cmmtmnt_start_dt > l_budget_end_date
584 if p_cmmtmnt_start_dt > l_budget_end_date then
585 FND_MESSAGE.SET_NAME('PQH','PQH_CMTMNT_START_AFT_BDGT_END');
586 APP_EXCEPTION.RAISE_EXCEPTION;
587 End if;
588 -- 4) Check if p_cmmtmnt_end_dt > l_budget_end_date
589 if p_cmmtmnt_end_dt > l_budget_end_date then
590 FND_MESSAGE.SET_NAME('PQH','PQH_CMTMNT_END_AFT_BDGT_END');
591 APP_EXCEPTION.RAISE_EXCEPTION;
592 End if;
593 hr_utility.set_location('Leaving:'||l_proc, 10);
594 Exception When others then
595 hr_utility.set_location('Exception:'||l_proc, 15);
596 raise;
597 End;
598 --------------------------------------------------------------------------
599 FUNCTION get_bg_legislation_code (p_business_group_id in number)
600 RETURN varchar2 IS
601 --
602 Cursor csr_lc is
603 SELECT O3.ORG_INFORMATION9 legislation_code
604 FROM HR_ORGANIZATION_INFORMATION O3
605 WHERE O3.ORGANIZATION_ID = p_business_group_id
606 AND O3.ORG_INFORMATION_CONTEXT = 'Business Group Information';
607 --
608 l_legislation_code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%type;
609 --
610 BEGIN
611 --
612 Open csr_lc;
613 Fetch csr_lc into l_legislation_code;
614 Close csr_lc;
615 --
616 RETURN l_legislation_code;
617 --
618 End;
619
620 ----------------------------------------------------------------------------
621 Function Convert_actuals(p_figure in number,
622 p_from_start_date in date,
623 p_from_end_date in date,
624 p_to_start_date in date,
625 p_to_end_date in date )
626 RETURN NUMBER
627 IS
628 --
629 l_calc_start_date date;
630 l_calc_end_date date;
631 --
632 l_days_in_from_period number(10);
633 l_days_in_to_period number(10);
634 l_converted_amt number;
635 --
636 l_proc varchar2(72) := g_package || 'Convert_actuals';
637 --
638 Begin
639 --
640 hr_utility.set_location('Entering :'||l_proc,5);
641 -- No conversion needed if the periods are the same.
642 If p_from_start_date = p_to_start_date
643 AND p_from_end_date = p_to_end_date then
644 RETURN p_figure;
645 End if;
646 -- Find the days between p_actuals_end_date,p_actuals_start_date
647 l_days_in_from_period := (p_from_end_date - p_from_start_date) + 1;
648 -- Find the days between l_calc_end_date,l_calc_start_date
649 l_days_in_to_period := (p_to_end_date - p_to_start_date) + 1;
650 --
651 hr_utility.set_location('Convert :'||to_char(p_figure) || ' For ' || to_char(l_days_in_from_period
652 ) ||
653 ' days ' || ' To ' || to_char(l_days_in_to_period),10);
654 --
655 l_converted_amt := p_figure * l_days_in_to_period/ l_days_in_from_period ;
656 --
657 hr_utility.set_location('Leaving :'||l_proc,10);
658 RETURN l_converted_amt;
659 Exception When others then
660 hr_utility.set_location('Exception:'||l_proc, 15);
661 raise;
662 End;
663 --------------------------------------------------------------------------
664 FUNCTION get_pos_budget_values(p_position_id in number,
665 p_period_start_dt in date,
666 p_period_end_dt in date,
667 p_unit_of_measure in varchar2)
668 RETURN number is
669 --
670 l_business_group_id hr_all_positions_f.business_group_id%type;
671 l_pbv number;
672 --
673 Cursor csr_pos is
674 Select psf.business_group_id
675 from hr_all_positions_f psf
676 Where psf.position_id = p_position_id
677 and rownum < 2;
678 --
679 l_proc varchar2(72) := g_package||'get_pos_budget_values';
680 --
681 Begin
682 --
683 hr_utility.set_location('Entering:'||l_proc, 5);
684 -- Obtain the business group of the position.
685 Open csr_pos;
686 Fetch csr_pos into l_business_group_id;
687 Close csr_pos;
688 --
689 -- Call function that returns commitment.
690 --
691 l_pbv := hr_discoverer.get_actual_budget_values
692 (p_unit => p_unit_of_measure,
693 p_bus_group_id => l_business_group_id ,
694 p_organization_id => NULL ,
695 p_job_id => NULL ,
696 p_position_id => p_position_id ,
697 p_grade_id => NULL ,
698 p_start_date => p_period_start_dt ,
699 p_end_date => p_period_end_dt ,
700 p_actual_val => NULL
701 );
702 hr_utility.set_location('Leaving:'||l_proc, 10);
703 RETURN l_pbv;
704 Exception When others then
705 hr_utility.set_location('Exception:'||l_proc, 15);
706 raise;
707 End;
708 ---------------------------------------------------------------------
709 FUNCTION get_assignment_budget_values(p_assignment_id in number,
710 p_period_start_dt in date,
711 p_period_end_dt in date,
712 p_unit_of_measure in varchar2)
713 RETURN number is
714 --
715 CURSOR CSR_ABV IS
716 SELECT NVL(SUM(ABV.VALUE),0)
717 FROM per_assignment_budget_values_f abv
718 WHERE abv.assignment_id = p_assignment_id
719 AND abv.unit = p_unit_of_measure
720 AND (p_period_end_dt BETWEEN abv.effective_start_date AND
721 abv.effective_end_date)
722 AND abv.assignment_id =
723 (select assignment_id
724 from per_all_assignments_f asg,
725 per_assignment_status_types ast
726 where asg.assignment_id = p_assignment_id
727 AND asg.assignment_type = 'E'
728 AND (p_period_end_dt BETWEEN asg.effective_start_date AND
729 asg.effective_end_date)
730 AND asg.assignment_status_type_id = ast.assignment_status_type_id
731 AND ast.per_system_status <> 'TERM_ASSIGN');
732 --
733 l_abv number;
734 l_proc varchar2(72) := g_package||'get_assignment_budget_values';
735 --
736 Begin
737 --
738 hr_utility.set_location('Entering:'||l_proc, 5);
739 --
740 OPEN CSR_ABV;
741 FETCH CSR_ABV INTO l_abv;
742 IF (CSR_ABV%NOTFOUND) THEN
743 l_abv := 0;
744 END IF;
745 CLOSE CSR_ABV;
746 hr_utility.set_location('Leaving:'||l_proc, 10);
747 RETURN l_abv;
748 Exception When others then
749 hr_utility.set_location('Exception:'||l_proc, 15);
750 raise;
751 End;
752 --
753 -- get_sum_actuals
754 -- this method is same as get_actuals except that it does not fetch
755 -- values in a loop, instead sums the values
756 -- ********************
757
758 FUNCTION get_sum_actuals(p_assignment_id in number,
759 p_legislation_code in varchar2,
760 p_payroll_id in number,
761 p_element_type_id in number,
762 p_actuals_start_date in date,
763 p_actuals_end_date in date,
764 p_last_payroll_dt out nocopy date)
765 RETURN NUMBER IS
766
767 cursor curs is
768 select classification_id
769 from pay_element_classifications
770 where classification_name in ('Employer Liabilities', 'Earnings','Supplemental Earnings')
771 and legislation_code = p_legislation_code;
772
773 cl_id number;
774
775 Cursor csr_assg_actuals1(p_element_type_id number) IS
776 SELECT max(aa.end_Date) last_payroll_dt, sum(RRV.RESULT_VALUE * get_factor(aa.start_date, aa.end_date,
777 greatest(p_actuals_start_date,aa.start_date), least(p_actuals_end_date,aa.end_date))) result_value
778 FROM
779 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
780 PAY_ELEMENT_TYPES_F PET,
781 PAY_RUN_RESULTS RES,
782 (
783 Select tp.start_date, tp.end_date, aac.assignment_action_id
784 From per_time_periods tp,
785 pay_payroll_actions ppa,
786 PAY_ASSIGNMENT_ACTIONS AAC
787 Where tp.payroll_id=p_payroll_id
788 AND (tp.start_date <= p_actuals_end_date and tp.end_date >= p_actuals_start_date)
789 and tp.payroll_id = ppa.payroll_id
790 and ppa.payroll_id = p_payroll_id
791 and tp.time_period_id = ppa.time_period_id
792 and ppa.date_earned between tp.start_date and tp.end_date
793 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
794 AND AAC.ASSIGNMENT_ID = p_assignment_id) AA
795 WHERE RES.ASSIGNMENT_ACTION_ID = aa.assignment_action_id
796 AND RES.STATUS IN ( 'P','PA' )
797 AND PET.CLASSIFICATION_ID in (
798 select classification_id
799 from pay_element_classifications
800 where classification_name in ('Employer Liabilities', 'Earnings','Supplemental Earnings')
801 and legislation_code = 'US'
802 )
803 AND PET.ELEMENT_TYPE_ID = p_element_type_id
804 AND aa.start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
805 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
806 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
807 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
808 AND INV.NAME = 'Pay Value'
809 AND aa.start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
810 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
811 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
812
813 Cursor csr_assg_actuals2 IS
814 SELECT max(aa.end_Date) last_payroll_dt, sum(RRV.RESULT_VALUE * get_factor(aa.start_date, aa.end_date,
815 greatest(p_actuals_start_date,aa.start_date), least(p_actuals_end_date,aa.end_date))) result_value
816 FROM
817 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
818 PAY_ELEMENT_TYPES_F PET,
819 PAY_RUN_RESULTS RES,
820 (
821 Select tp.start_date, tp.end_date, aac.assignment_action_id
822 From per_time_periods tp,
823 pay_payroll_actions ppa,
824 PAY_ASSIGNMENT_ACTIONS AAC
825 Where tp.payroll_id=p_payroll_id
826 AND (tp.start_date <= p_actuals_end_date and tp.end_date >= p_actuals_start_date)
827 and tp.payroll_id = ppa.payroll_id
828 and ppa.payroll_id = p_payroll_id
829 and tp.time_period_id = ppa.time_period_id
830 and ppa.date_earned between tp.start_date and tp.end_date
831 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
832 AND AAC.ASSIGNMENT_ID = p_assignment_id) AA
833 WHERE RES.ASSIGNMENT_ACTION_ID = aa.assignment_action_id
834 AND RES.STATUS IN ( 'P','PA' )
835 AND PET.CLASSIFICATION_ID in (
836 select classification_id
837 from pay_element_classifications
838 where classification_name in ('Employer Liabilities', 'Earnings','Supplemental Earnings')
839 and legislation_code = 'US' )
840 AND aa.start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
841 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
842 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
843 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
844 AND INV.NAME = 'Pay Value'
845 AND aa.start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
846 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
847 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
848
849 l_from_period_start_dt date;
850 l_from_period_end_dt date;
851 l_to_period_start_dt date;
852 l_to_period_end_dt date;
853 l_result_value pay_run_result_values.result_value%type;
854 l_element_type_id pay_run_results.element_type_id%type;
855
856 l_converted_amt number;
857 l_assignment_actuals number;
858 l_proc varchar2(72) := g_package ||'get_sum_actuals';
859 l_dummy number;
860 l_last_payroll_dt date;
861 cursor csr_asg_act_exists is
862 select 1
863 from dual
864 where exists
865 (select null
866 from pay_assignment_actions
867 where assignment_id = p_assignment_id);
868
869 Begin
870 hr_utility.set_location('Entering :'||l_proc,5);
871 l_assignment_actuals := 0;
872
873 open csr_asg_act_exists;
874 fetch csr_asg_act_exists into l_dummy;
875 if csr_asg_act_exists%found then
876 hr_utility.set_location('** Exists, element_Type'||p_element_type_id,5);
877 if (p_element_type_id is not null) then
878 open csr_assg_actuals1(p_element_type_id);
879 fetch csr_assg_actuals1 into l_last_payroll_dt, l_result_value;
880 close csr_assg_actuals1;
881 else
882 open csr_assg_actuals2;
883 fetch csr_assg_actuals2 into l_last_payroll_dt, l_result_value;
884 close csr_assg_actuals2;
885 hr_utility.set_location('** Opened cursor 2, date:'||l_last_payroll_dt||' Result: '||l_result_value,5);
886 end if;
887 l_last_payroll_dt := get_last_payroll_dt(p_assignment_id,
888 p_actuals_start_date, p_actuals_end_date);
889 p_last_payroll_dt := l_last_payroll_dt;
890 End if;
891 close csr_asg_act_exists;
892
893
894
895 hr_utility.set_location('Leaving :'||l_proc,20);
896 RETURN l_result_value;
897 Exception When others then
898 if (csr_asg_act_exists%isopen) then
899 close csr_asg_act_exists;
900 end if;
901 p_last_payroll_dt := null;
902 hr_utility.set_location('Exception:'||l_proc, 25);
903 raise;
904 End get_sum_actuals;
905 ------------------------------------------------------------------------
906 --
907 -- get_element_actuals
908 -- This method is same as get_sum_actuals except that it does not restrict the
909 -- elements to be of classification 'Employee Liability' or 'Earnings.
910 ------------------------------------------------------------------------
911
912 FUNCTION get_element_actuals(p_assignment_id in number,
913 p_legislation_code in varchar2,
914 p_payroll_id in number,
915 p_element_type_id in number,
916 p_actuals_start_date in date,
917 p_actuals_end_date in date,
918 p_ele_input_value_id in number default null,
919 p_last_payroll_dt out nocopy date)
920 RETURN NUMBER IS
921
922 Cursor csr_assg_actuals(p_element_type_id number) IS
923 SELECT max(aa.end_Date) last_payroll_dt, sum(RRV.RESULT_VALUE * get_factor(aa.start_date, aa.end_date,
924 greatest(p_actuals_start_date,aa.start_date), least(p_actuals_end_date,aa.end_date))) result_value
925 FROM
926 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
927 PAY_ELEMENT_TYPES_F PET,
928 PAY_RUN_RESULTS RES,
929 (
930 Select tp.start_date, tp.end_date, aac.assignment_action_id
931 From per_time_periods tp,
932 pay_payroll_actions ppa,
933 PAY_ASSIGNMENT_ACTIONS AAC
934 Where tp.payroll_id=p_payroll_id
935 AND (tp.start_date <= p_actuals_end_date and tp.end_date >= p_actuals_start_date)
936 and tp.payroll_id = ppa.payroll_id
937 and ppa.payroll_id = p_payroll_id
938 and tp.time_period_id = ppa.time_period_id
939 and ppa.date_earned between tp.start_date and tp.end_date
940 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
941 AND AAC.ASSIGNMENT_ID = p_assignment_id) AA
942 WHERE RES.ASSIGNMENT_ACTION_ID = aa.assignment_action_id
943 AND RES.STATUS IN ( 'P','PA' )
944 AND PET.ELEMENT_TYPE_ID = p_element_type_id
945 AND aa.start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
946 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
947 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
948 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
949 -- AND INV.INPUT_VALUE_ID = p_ele_input_value_id --'Pay Value'
950 AND INV.NAME = 'Pay Value'
951 AND aa.start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
952 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
953 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
954
955 l_from_period_start_dt date;
956 l_from_period_end_dt date;
957 l_to_period_start_dt date;
958 l_to_period_end_dt date;
959 l_result_value pay_run_result_values.result_value%type;
960 l_element_type_id pay_run_results.element_type_id%type;
961
962 l_converted_amt number;
963 l_assignment_actuals number;
964 l_proc varchar2(72) := g_package ||'get_element_actuals';
965 l_dummy number;
966 l_last_payroll_dt date;
967 cursor csr_asg_act_exists is
968 select 1
969 from dual
970 where exists
971 (select null
972 from pay_assignment_actions
973 where assignment_id = p_assignment_id);
974
975 Begin
976 hr_utility.set_location('Entering :'||l_proc,5);
977 l_assignment_actuals := 0;
978
979 open csr_asg_act_exists;
980 fetch csr_asg_act_exists into l_dummy;
981 if csr_asg_act_exists%found then
982 hr_utility.set_location('** Exists, element_Type'||p_element_type_id,5);
983 if (p_element_type_id is not null) then
984 open csr_assg_actuals(p_element_type_id);
985 fetch csr_assg_actuals into l_last_payroll_dt, l_result_value;
986 close csr_assg_actuals;
987 end if;
988 l_last_payroll_dt := get_last_payroll_dt(p_assignment_id,
989 p_actuals_start_date, p_actuals_end_date);
990 p_last_payroll_dt := l_last_payroll_dt;
991 End if;
992 close csr_asg_act_exists;
993 hr_utility.set_location('Leaving :'||l_proc,20);
994 RETURN l_result_value;
995 Exception When others then
996 if (csr_asg_act_exists%isopen) then
997 close csr_asg_act_exists;
998 end if;
999 p_last_payroll_dt := null;
1000 hr_utility.set_location('Exception:'||l_proc, 25);
1001 raise;
1002 End get_element_actuals;
1003 --
1004 ------------------------------------------------------------------------
1005 -- get_payroll_run_date
1006 -- This method gets the most recent payroll run date for the assignment.
1007 ------------------------------------------------------------------------
1008 PROCEDURE get_payroll_run_date(p_assignment_id in number,
1009 p_payroll_id in number,
1010 p_actuals_start_date in date,
1011 p_actuals_end_date in date,
1012 p_last_payroll_dt out nocopy date)
1013 IS
1014
1015 Cursor csr_pay_date Is
1016 Select max(tp.end_date)
1017 From per_time_periods tp,
1018 pay_payroll_actions ppa,
1019 PAY_ASSIGNMENT_ACTIONS AAC
1020 Where tp.payroll_id=p_payroll_id
1021 and (tp.start_date <= p_actuals_end_date and tp.end_date >= p_actuals_start_date)
1022 and tp.payroll_id = ppa.payroll_id
1023 and ppa.payroll_id = p_payroll_id
1024 and tp.time_period_id = ppa.time_period_id
1025 and ppa.date_earned between tp.start_date and tp.end_date
1026 and ppa.payroll_action_id = aac.payroll_action_id
1027 and aac.assignment_id = p_assignment_id;
1028
1029 l_proc varchar2(72) := g_package ||'get_payroll_run_date';
1030 l_dummy number;
1031 l_last_payroll_dt date;
1032 cursor csr_asg_act_exists is
1033 select 1
1034 from dual
1035 where exists
1036 (select null
1037 from pay_assignment_actions
1038 where assignment_id = p_assignment_id);
1039
1040 Begin
1041 hr_utility.set_location('Entering :'||l_proc,5);
1042
1043 open csr_asg_act_exists;
1044 fetch csr_asg_act_exists into l_dummy;
1045 if csr_asg_act_exists%found then
1046 open csr_pay_date;
1047 fetch csr_pay_date into l_last_payroll_dt;
1048 close csr_pay_date;
1049 p_last_payroll_dt := l_last_payroll_dt;
1050 End if;
1051 close csr_asg_act_exists;
1052 hr_utility.set_location('Leaving :'||l_proc,20);
1053 Exception When others then
1054 if (csr_asg_act_exists%isopen) then
1055 close csr_asg_act_exists;
1056 end if;
1057 p_last_payroll_dt := null;
1058 hr_utility.set_location('Exception:'||l_proc, 25);
1059 raise;
1060 End get_payroll_run_date;
1061 --
1062 ------------------------------------------------------------------------
1063
1064 -- get actuals
1065
1066 -- This function is an overloaded function. It checks if an element is defined
1067 -- for actuals. If an elemnt is defined and it has a balance type also defined
1068 -- then it calculates the actual expenditure for an assignment from
1069 -- pay_run_balances. If no balance is defined, then it calls the
1070 -- get_element_actuals procedure to get the actual values. If not it uses
1071 -- the default get_sum_actuals procedure to get the actual value
1072 -- for a given period and for a given element type id . If the
1073 -- element type id is not input , then all element types are taken into
1074 -- consideration while computing the actual expenditure of the assignment
1075 -- in the get_sum_actual procedure.
1076 -------------------------------------------------------------------------
1077
1078
1079 FUNCTION get_actuals(p_budget_id in number default null,
1080 p_assignment_id in number,
1081 p_legislation_code in varchar2,
1082 p_payroll_id in number,
1083 p_element_type_id in number,
1084 p_actuals_start_date in date,
1085 p_actuals_end_date in date,
1086 p_last_payroll_dt out nocopy date)
1087 RETURN NUMBER
1088 IS
1089 cursor csr_actual_exists is
1090 select 1
1091 from pqh_bdgt_cmmtmnt_elmnts
1092 where actual_commitment_type in ('ACTUAL','BOTH')
1093 and budget_id = p_budget_id;
1094
1095 cursor curs is
1096 select element_type_id,element_input_value_id, balance_type_id
1097 from pqh_bdgt_cmmtmnt_elmnts
1098 where actual_commitment_type in ('ACTUAL','BOTH') and
1099 element_type_id = nvl(p_element_type_id,element_type_id)
1100 and budget_id = p_budget_id;
1101
1102 cursor csr_bal(p_balance_type_id number, p_dim_suf VARCHAR2) is
1103 select defined_balance_id
1104 from pay_defined_balances def, pay_balance_dimensions dim
1105 where def.balance_type_id = p_balance_type_id
1106 and def.balance_dimension_id = dim.balance_dimension_id
1107 and dim.database_item_suffix = p_dim_suf
1108 and save_run_balance = 'Y';
1109 -- and RUN_BALANCE_STATUS = 'V';
1110
1111 cursor csr_asg_action(c_assignment_id number, c_effective_date date) is
1112 select paa.assignment_action_id
1113 from pay_assignment_actions paa, pay_payroll_actions ppa
1114 where paa.payroll_action_id = ppa.payroll_action_id
1115 and paa.source_action_id is null
1116 and paa.assignment_id = c_assignment_id
1117 and ppa.effective_date = (select max(effective_date) from pay_payroll_actions ppa1
1118 where ppa1.payroll_action_id = paa.payroll_action_id
1119 and paa.assignment_id = c_assignment_id
1120 and ppa.effective_date <= c_effective_date);
1121
1122 cursor csr_act_value(c_assignment_id number,c_defined_balance_id number,c_effective_date date) is
1123 select prb.balance_value
1124 from pay_run_balances prb, pay_assignment_actions paa
1125 where prb.assignment_id = c_assignment_id
1126 and prb.defined_balance_id = c_defined_balance_id
1127 and prb.assignment_action_id = paa.assignment_action_id
1128 and paa.source_action_id is not null
1129 and prb.effective_date =
1130 (select max(effective_date)
1131 from pay_run_balances prb1
1132 where prb1.effective_date <= c_effective_date
1133 and prb1.assignment_id = c_assignment_id and prb1.defined_balance_id = c_defined_balance_id);
1134
1135 p_ele_id number;
1136 p_ele_inv_id number;
1137 p_balance_type_id number;
1138 l_dummy number;
1139 l_temp number;
1140 l_assignment_start_actuals number;
1141 l_assignment_end_actuals number;
1142 l_assignment_actuals number;
1143 l_assign_action_id number;
1144
1145 l_proc varchar2(72) := g_package || 'get_actuals';
1146 l_last_payroll_dt date;
1147 Begin
1148
1149 hr_utility.set_location('Entering :'||l_proc,5);
1150
1151 /* If ((p_budget_id is not null) AND (p_element_type_id is not null)) then */
1152 --Bug Fix 3717620
1153 If (p_budget_id is not null) then
1154 open csr_actual_exists;
1155 fetch csr_actual_exists into l_dummy;
1156 if csr_actual_exists%found then
1157 open curs;
1158 fetch curs into p_ele_id,p_ele_inv_id,p_balance_type_id;
1159 if p_balance_type_id is not null then
1160 open csr_bal(p_balance_type_id, '_ASG_LTD');
1161 fetch csr_bal into l_temp;
1162 if csr_bal%found then
1163 /* Commenting this part of the code for now. May be reused once
1164 use of pay_balance_pkg issue is resolved */
1165 /* open csr_asg_action(p_assignment_id,p_actuals_start_date);
1166 fetch csr_asg_action into l_assign_action_id;
1167 l_assignment_start_actuals := pay_balance_pkg.get_value(
1168 l_temp, l_assign_action_id);
1169 close csr_asg_action;
1170 open csr_asg_action(p_assignment_id,p_actuals_end_date);
1171 fetch csr_asg_action into l_assign_action_id;
1172 l_assignment_end_actuals := pay_balance_pkg.get_value(
1173 l_temp, l_assign_action_id);
1174 close csr_asg_action; */
1175 open csr_act_value(p_assignment_id,l_temp,p_actuals_start_date);
1176 fetch csr_act_value into l_assignment_start_actuals;
1177 close csr_act_value;
1178
1179 open csr_act_value(p_assignment_id,l_temp,p_actuals_end_date);
1180 fetch csr_act_value into l_assignment_end_actuals;
1181 close csr_act_value;
1182
1183 l_assignment_actuals := nvl(l_assignment_end_actuals,0) - nvl(l_assignment_start_actuals,0);
1184 l_last_payroll_dt := get_last_payroll_dt(p_assignment_id,
1185 p_actuals_start_date, p_actuals_end_date);
1186 /* get_payroll_run_date(p_assignment_id => p_assignment_id,
1187 p_payroll_id => p_payroll_id,
1188 p_actuals_start_date => p_actuals_start_date,
1189 p_actuals_end_date => p_actuals_end_date,
1190 p_last_payroll_dt => p_last_payroll_dt);
1191 p_last_payroll_dt := p_last_payroll_dt; */
1192 end if;
1193 close csr_bal;
1194 elsif (p_ele_id is not null) then
1195 l_assignment_actuals := get_element_actuals(p_assignment_id => p_assignment_id,
1196 p_legislation_code => p_legislation_code,
1197 p_payroll_id => p_payroll_id,
1198 p_element_type_id => p_ele_id,
1199 p_actuals_start_date => p_actuals_start_date,
1200 p_actuals_end_date => p_actuals_end_date,
1201 p_ele_input_value_id => p_ele_inv_id,
1202 p_last_payroll_dt => l_last_payroll_dt);
1203
1204 -- p_last_payroll_dt := p_last_payroll_dt;
1205 end if;
1206 close curs;
1207 else
1208 /* open curs;
1209 fetch curs into p_ele_id,p_ele_inv_id,p_balance_type_id; */
1210 l_assignment_actuals := get_sum_actuals(p_assignment_id => p_assignment_id,
1211 p_legislation_code => p_legislation_code,
1212 p_payroll_id => p_payroll_id,
1213 p_element_type_id => p_element_type_id,
1214 p_actuals_start_date => p_actuals_start_date,
1215 p_actuals_end_date => p_actuals_end_date,
1216 p_last_payroll_dt => l_last_payroll_dt);
1217
1218 -- p_last_payroll_dt := p_last_payroll_dt;
1219 end if;
1220 close csr_actual_exists;
1221 else
1222 l_assignment_actuals := get_sum_actuals(p_assignment_id => p_assignment_id,
1223 p_legislation_code => p_legislation_code,
1224 p_payroll_id => p_payroll_id,
1225 p_element_type_id => p_element_type_id,
1226 p_actuals_start_date => p_actuals_start_date,
1227 p_actuals_end_date => p_actuals_end_date,
1228 p_last_payroll_dt => l_last_payroll_dt);
1229
1230 -- p_last_payroll_dt := p_last_payroll_dt;
1231 end if;
1232
1233 p_last_payroll_dt := l_last_payroll_dt;
1234 hr_utility.set_location('Leaving :'||l_proc,20);
1235 RETURN l_assignment_actuals;
1236 --
1237 Exception When others then
1238 if (csr_actual_exists%isopen) then
1239 close csr_actual_exists;
1240 end if;
1241 p_last_payroll_dt := null;
1242 hr_utility.set_location('Exception:'||l_proc, 25);
1243 raise;
1244 End get_actuals;
1245 --
1246 -- ********************
1247 ------------------------------------------------------------------------
1248 -- get actuals
1249 -- This function calculates the actual expenditure for an assignment
1250 -- for a given period and for a given element type id . If the
1251
1252 -- element type id is not input , then all element types are taken into
1253 -- consideration while computing the actual expenditure of the assignment.
1254
1255 FUNCTION get_actuals(p_assignment_id in number,
1256 p_legislation_code in varchar2,
1257 p_payroll_id in number,
1258 p_element_type_id in number,
1259 p_actuals_start_date in date,
1260 p_actuals_end_date in date,
1261 p_last_payroll_dt out nocopy date)
1262 RETURN NUMBER
1263 IS
1264 --
1265 -- This cursor will find the classification ids.
1266 --
1267 cursor curs is
1268 select classification_id
1269 from pay_element_classifications
1270 where classification_name in ('Employer Liabilities', 'Earnings','Supplemental Earnings')
1271 and legislation_code = p_legislation_code;
1272 cl_id number;
1273
1274 --
1275 -- This cursor returns the actual expenditure for
1276 -- each element type that belongs to each assignment action .
1277 --
1278 Cursor csr_assg_actuals1(p_start_date DATE ,p_assignment_action_id NUMBER,
1279 p_element_type_id number) IS
1280 SELECT sum(RRV.RESULT_VALUE) result_value
1281 FROM
1282 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
1283 PAY_ELEMENT_TYPES_F PET,
1284 PAY_RUN_RESULTS RES
1285 WHERE RES.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1286 AND RES.STATUS IN ( 'P','PA' )
1287 AND PET.CLASSIFICATION_ID = cl_id
1288 AND PET.ELEMENT_TYPE_ID = p_element_type_id
1289 AND p_start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
1290 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
1291 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1292 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1293 AND INV.NAME = 'Pay Value'
1294 AND p_start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
1295 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
1296 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
1297 --
1298 Cursor csr_assg_actuals2(p_start_date DATE ,p_assignment_action_id NUMBER) IS
1299 SELECT sum(RRV.RESULT_VALUE) result_value
1300 FROM
1301 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
1302 PAY_ELEMENT_TYPES_F PET,
1303 PAY_RUN_RESULTS RES
1304 WHERE RES.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1305 AND RES.STATUS IN ( 'P','PA' )
1306 AND PET.CLASSIFICATION_ID = cl_id
1307 AND p_start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
1308 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
1309 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1310 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1311 AND INV.NAME = 'Pay Value'
1312 AND p_start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
1313 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
1314 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
1315 --
1316 --
1317 --Cursor to find Assignment Action Id and Time period of Payroll
1318 --
1319 Cursor csr_asg_time_periods is
1320 Select /*+ ORDERED */
1321 tp.start_date,tp.end_date, aac.assignment_action_id
1322 From per_time_periods tp,
1323 pay_payroll_actions ppa,
1324 PAY_ASSIGNMENT_ACTIONS AAC
1325 Where tp.payroll_id=p_payroll_id
1326 AND (tp.start_date <= p_actuals_end_date
1327 and
1328 tp.end_date >= p_actuals_start_date)
1329 and tp.payroll_id = ppa.payroll_id
1330 and ppa.payroll_id = p_payroll_id
1331 and tp.time_period_id = ppa.time_period_id
1332 and ppa.date_earned between tp.start_date and tp.end_date
1333 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
1334 AND AAC.ASSIGNMENT_ID = p_assignment_id
1335 order by tp.end_date;
1336
1337 l_from_period_start_dt date;
1338 l_from_period_end_dt date;
1339 l_to_period_start_dt date;
1340 l_to_period_end_dt date;
1341 --
1342 l_result_value pay_run_result_values.result_value%type;
1343 l_element_type_id pay_run_results.element_type_id%type;
1344 --
1345
1346 l_converted_amt number;
1347 l_assignment_actuals number;
1348 --
1349 l_proc varchar2(72) := g_package ||'get_actuals';
1350 --
1351 l_dummy number;
1352 --
1353 cursor csr_asg_act_exists is
1354 select 1
1355 from dual
1356 where exists
1357 (select null
1358 from pay_assignment_actions
1359 where assignment_id = p_assignment_id);
1360 Begin
1361 --
1362 hr_utility.set_location('Entering :'||l_proc,5);
1363 --
1364 l_assignment_actuals := 0;
1365 --
1366 open csr_asg_act_exists;
1367 fetch csr_asg_act_exists into l_dummy;
1368 if csr_asg_act_exists%found then
1369 -- This function returns the actual expenditure of one assignment record
1370 -- for a given period.
1371 For ctp in csr_asg_time_periods loop
1372 --
1373 l_from_period_start_dt := ctp.start_date;
1374 l_from_period_end_dt := ctp.end_date;
1375 --
1376 --
1377 p_last_payroll_dt := l_from_period_end_dt;
1378 --
1379 -- Sum up the actual expenditure for all the elements to arrive
1380 -- at the actual expenditure of the assignment.
1381 --
1382 -- Actuals start date lies between start and end date of a time period.
1383 -- Hence we need to compute the value from the actuals start date
1384 -- to the end date of the time period.
1385 --
1386 l_to_period_start_dt := greatest(p_actuals_start_date,l_from_period_start_dt);
1387 l_to_period_end_dt := least(p_actuals_end_date,l_from_period_end_dt);
1388 --
1389 for classif in curs loop
1390 cl_id := classif.classification_id;
1391 --
1392 if (p_element_type_id is not null) then
1393 open csr_assg_actuals1(l_from_period_start_dt,
1394 ctp.Assignment_action_id,
1395 p_element_type_id);
1396 fetch csr_assg_actuals1 into l_result_value;
1397 close csr_assg_actuals1;
1398 else
1399 open csr_assg_actuals2(l_from_period_start_dt,
1400 ctp.Assignment_action_id);
1401 fetch csr_assg_actuals2 into l_result_value;
1402 close csr_assg_actuals2;
1403 end if;
1404 l_converted_amt := Convert_actuals(
1405 p_figure => fnd_number.canonical_to_number(l_result_value),
1406 p_from_start_date => l_from_period_start_dt,
1407 p_from_end_date => l_from_period_end_dt,
1408 p_to_start_date => l_to_period_start_dt,
1409 p_to_end_date => l_to_period_end_dt);
1410 l_assignment_actuals := l_assignment_actuals + nvl(l_converted_amt,0);
1411 End loop; /** Clasifications */
1412 End loop; /** csr_asg_time_periods */
1413 End if;
1414 close csr_asg_act_exists;
1415 hr_utility.set_location('Leaving :'||l_proc,20);
1416 --
1417 RETURN l_assignment_actuals;
1418 Exception When others then
1419 if (csr_asg_act_exists%isopen) then
1420 close csr_asg_act_exists;
1421 end if;
1422 p_last_payroll_dt := null;
1423 hr_utility.set_location('Exception:'||l_proc, 25);
1424 raise;
1425 End get_actuals;
1426 --
1427
1428 --------------------------------------------------------------------------
1429 FUNCTION get_assign_money_actuals(p_budget_id in number,
1430 p_assignment_id in number,
1431 p_element_type_id in number,
1432 p_actuals_start_date in date,
1433 p_actuals_end_date in date,
1434 p_last_payroll_dt out nocopy date)
1435 RETURN number is
1436 --
1437 -- Per_all_assignments_f is a date-tracked table. There may be more than
1438 -- one record that is effective in the actuals calculation period
1439 -- with different payrolls.
1440 --
1441 Cursor csr_assg is
1442 Select ASSG.assignment_id,
1443 ASSG.payroll_id,
1444 ASSG.business_group_id,
1445 ASSG.effective_start_date,
1446 ASSG.effective_end_date
1447 From per_all_assignments_f ASSG
1448 Where ASSG.assignment_id = p_assignment_id
1449 AND ASSG.effective_end_date >= p_actuals_start_date
1450 AND ASSG.effective_start_date <= p_actuals_end_date ;
1451 --
1452 l_assignment_id per_all_assignments_f.assignment_id%type;
1453 l_effective_start_date per_all_assignments_f.effective_start_date%type;
1454 l_effective_end_date per_all_assignments_f.effective_end_date%type;
1455 l_payroll_id per_all_assignments_f.payroll_id%type;
1456 l_business_group_id number;
1457 l_legislation_code per_business_groups.legislation_code%type;
1458 --
1459 l_assignment_actuals number;
1460 l_actuals number;
1461 --
1462 l_actuals_sub_start_dt date;
1463 l_actuals_sub_end_dt date;
1464 --
1465 l_proc varchar2(72) := g_package||'get_assign_money_actuals';
1466 --
1467 Begin
1468 --
1469 hr_utility.set_location('Entering :'||l_proc,5);
1470 --
1471 l_assignment_actuals := 0;
1472 --
1473 Open csr_assg;
1474 --
1475 Loop
1476 -- Fetch next record for assignment
1477 Fetch csr_assg into l_assignment_id,l_payroll_id,l_business_group_id,
1478 l_effective_start_date,l_effective_end_date;
1479 --
1480 If csr_assg%notfound then
1481 exit;
1482 End if;
1483 if l_legislation_code is null then
1484 l_legislation_code := get_bg_legislation_code(l_business_group_id);
1485 end if;
1486 --
1487 hr_utility.set_location('Assignment :'||to_char(l_assignment_id),10);
1488 hr_utility.set_location('Payroll :'||to_char(l_payroll_id),15);
1489 --
1490 l_actuals := 0;
1491 --
1492 If l_payroll_id IS NOT NULL then
1493 --
1494 -- Check if the effective start date of this assignment is
1495 -- lesser than the actuals start date . If so , we are interested
1496 -- in calculating actuals only from the actuals start date .
1497 -- Else we will try to calculate actuals from the effective
1498 -- start date .
1499 --
1500 l_actuals_sub_start_dt := greatest(p_actuals_start_date,l_effective_start_date);
1501 --
1502 -- Check if the effective end date of this assignment record is
1503 -- lesser than the actuals end date . If so , we are interested
1504 -- in calculating actuals only upto the effective end date of the
1505 -- assignment . Else we will try to calculate actuals upto from the
1506 -- actuals end date .
1507 --
1508 l_actuals_sub_end_dt := least(p_actuals_end_date,l_effective_end_date);
1509 --
1510 hr_utility.set_location('Calculating for :'||to_char(l_actuals_sub_start_dt,'DD/MM/RRRR') || ' to ' || to_char(l_actuals_sub_end_dt,'DD/MM/RRRR'),20);
1511 /* l_actuals := get_sum_actuals(p_assignment_id => l_assignment_id,
1512 p_legislation_code => l_legislation_code,
1513 p_payroll_id => l_payroll_id,
1514 p_element_type_id => p_element_type_id,
1515 p_actuals_start_date => l_actuals_sub_start_dt,
1516 p_actuals_end_date => l_actuals_sub_end_dt,
1517 p_last_payroll_dt => p_last_payroll_dt); */
1518 /*2716884*/
1519 l_actuals := get_actuals(p_budget_id => p_budget_id,
1520 p_assignment_id => l_assignment_id,
1521 p_legislation_code => l_legislation_code,
1522 p_payroll_id => l_payroll_id,
1523 p_element_type_id => p_element_type_id,
1524 p_actuals_start_date => l_actuals_sub_start_dt,
1525 p_actuals_end_date => l_actuals_sub_end_dt,
1526 p_last_payroll_dt => p_last_payroll_dt);
1527 End if;
1528 --
1529 l_assignment_actuals := nvl(l_actuals,0) + l_assignment_actuals;
1530 --
1531 End loop;
1532 --
1533 Close csr_assg;
1534 --
1535 -- At this point , we will have an assignments actuals for a given period.
1536 --
1537 hr_utility.set_location('Leaving :'||l_proc,25);
1538 --
1539 RETURN l_assignment_actuals;
1540 --
1541 Exception When others then
1542 p_last_payroll_dt := null;
1543 hr_utility.set_location('Exception:'||l_proc, 30);
1544 raise;
1545 End get_assign_money_actuals;
1546 --
1547 ------------------------------------------------------------------------
1548 --
1549 --
1550 FUNCTION get_assign_money_cmmtmnt(p_assignment_id in number,
1551 p_budget_version_id in number,
1552 p_element_type_id in number default null,
1553 p_period_start_date in date,
1554 p_period_end_date in date)
1555 RETURN NUMBER
1556 IS
1557 --
1558 Cursor csr_assg_commitment is
1559 Select nvl(commitment_amount,0),
1560 commitment_start_date,commitment_end_date
1561 From pqh_element_commitments
1562 Where budget_version_id = p_budget_version_id
1563 AND assignment_id = p_assignment_id
1564 AND element_type_id = nvl(p_element_type_id,element_type_id)
1565 AND commitment_start_date <= p_period_end_date
1566 AND commitment_end_date >= p_period_start_date;
1567 --
1568 l_prorate_start_dt date;
1569 l_prorate_end_dt date;
1570 --
1571 l_commitment_start_date pqh_element_commitments.commitment_start_date%type;
1572 l_commitment_end_date pqh_element_commitments.commitment_end_date%type;
1573 --
1574 l_prorate_amt number ;
1575 l_assign_cmmtmnt number ;
1576 l_amount number;
1577 --
1578 l_proc varchar2(72) := g_package||'get_assign_money_cmmtmnt';
1579 --
1580 Begin
1581 --
1582 hr_utility.set_location('Entering:'||l_proc, 5);
1583 --
1584 l_assign_cmmtmnt := 0;
1585 --
1586 Open csr_assg_commitment;
1587 --
1588
1589 Loop
1590 --
1591 Fetch csr_assg_commitment into l_amount,l_commitment_start_date,
1592 l_commitment_end_date;
1593 --
1594 If csr_assg_commitment%notfound then
1595 exit;
1596 End if;
1597 --
1598 l_prorate_start_dt := greatest(p_period_start_date,l_commitment_start_date);
1599 l_prorate_end_dt := least(p_period_end_date,l_commitment_end_date);
1600 --
1601 hr_utility.set_location('Dates used for evaluating prorated amount: '||l_prorate_start_dt||' - '||l_prorate_end_dt, 66);
1602 l_prorate_amt := l_amount *
1603 (
1604 (l_prorate_end_dt - l_prorate_start_dt +1)/
1605 (l_commitment_end_date - l_commitment_start_date + 1)
1606 );
1607 --
1608 hr_utility.set_location('Amount Found: '||l_amount||' Prorated amount: '||l_prorate_amt, 67);
1609 l_assign_cmmtmnt := l_assign_cmmtmnt + l_prorate_amt;
1610 --
1611 End loop;
1612 --
1613 Close csr_assg_commitment;
1614 hr_utility.set_location('Assignment Commitment returned '||l_assign_cmmtmnt, 68);
1615 --
1616 hr_utility.set_location('Leaving:'||l_proc, 10);
1617 --
1618 RETURN l_assign_cmmtmnt;
1619 --
1620 Exception When others then
1621 --
1622 hr_utility.set_location('Exception:'||l_proc, 15);
1623 raise;
1624 --
1625 End get_assign_money_cmmtmnt;
1626 --
1627 ------------------------------------------------------------------------
1628 --
1629 FUNCTION get_assignment_actuals
1630 (p_assignment_id in number,
1631 p_element_type_id in number default NULL,
1632 p_actuals_start_date in date,
1633 p_actuals_end_date in date,
1634 p_unit_of_measure_id in number,
1635 p_last_payroll_dt out nocopy date)
1636 RETURN NUMBER
1637 is
1638
1639 --
1640 l_unit_of_measure per_shared_types.system_type_cd%type;
1641 l_actuals number := 0;
1642 --
1643 l_proc varchar2(72) := g_package ||'get_assignment_actuals';
1644 --
1645 Begin
1646 --
1647 hr_utility.set_location('Entering :'||l_proc,5);
1648 --
1649 -- Check if input unit of measure is valid in per_shared_types.
1650 --
1651 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
1652 p_unit_of_measure_desc => l_unit_of_measure);
1653 --
1654 -- 1) Check if p_actuals_end_dt > p_actuals_start_dt.
1655 --
1656 If p_actuals_end_date < p_actuals_start_date then
1657 --
1658 FND_MESSAGE.SET_NAME('PQH','PQH_END_DT_LESS_THAN_START_DT');
1659 APP_EXCEPTION.RAISE_EXCEPTION;
1660 --
1661 End if;
1662
1663 --
1664 --
1665 -- Check if this is a valid assignment
1666 --
1667 if p_assignment_id is not null then
1668 Validate_assignment(p_assignment_id => p_assignment_id);
1669 end if;
1670 --
1671 --
1672 -- Check if this is a valid element type, if the element_type is input.
1673 --
1674 Validate_element_type(p_element_type_id => p_element_type_id);
1675 --
1676 -- We have finished doing a basic validation of all the inputs .We can
1677 -- Now,Call the respective function that returns the actuals for a
1678 -- assignment and for the unit of measure.
1679 --
1680 If l_unit_of_measure = 'MONEY' then
1681 --
1682 l_actuals := get_assign_money_actuals
1683 (p_budget_id => null, /*2716884*/
1684 p_assignment_id => p_assignment_id,
1685 p_element_type_id => p_element_type_id,
1686 p_actuals_start_date => p_actuals_start_date,
1687 p_actuals_end_date => p_actuals_end_date,
1688 p_last_payroll_dt => p_last_payroll_dt);
1689
1690 --
1691 Else
1692 --
1693 -- Call get_assignment_budget_values
1694 --
1695 l_actuals := get_assignment_budget_values
1696 (p_assignment_id => p_assignment_id,
1697 p_period_start_dt => p_actuals_start_date,
1698 p_period_end_dt => p_actuals_end_date,
1699 p_unit_of_measure => l_unit_of_measure);
1700 p_last_payroll_dt := NULL;
1701 --
1702 End if;
1703 --
1704 --
1705 hr_utility.set_location('Leaving :'||l_proc,10);
1706 --
1707 RETURN l_actuals;
1708 --
1709 Exception When others then
1710 p_last_payroll_dt := null;
1711 --
1712 hr_utility.set_location('Exception:'||l_proc, 15);
1713 raise;
1714
1715 --
1716 End get_assignment_actuals;
1717 --
1718 --------------------------------------------------------------------------
1719 PROCEDURE get_version_from_cmmtmnt_table
1720 (p_assignment_id in number,
1721 p_start_date in date,
1722 p_end_date in date,
1723 p_budget_version_id out nocopy number)
1724 is
1725 --
1726 -- We want to pick up only the budget versions that lie between the passed
1727 -- dates
1728 --
1729 Cursor csr_version is
1730 Select budget_version_id
1731 From pqh_element_commitments
1732 Where assignment_id = p_assignment_id
1733 AND (p_start_date <= commitment_end_date AND
1734 commitment_start_date <= p_end_date);
1735
1736 --
1737 l_proc varchar2(72) := g_package ||'get_version_from_cmmtmnt_table';
1738
1739 --
1740 Begin
1741 --
1742 hr_utility.set_location('Entering :'||l_proc,5);
1743 --
1744 Open csr_version;
1745 --
1746 -- Selecting the first budget version
1747 --
1748 Fetch csr_version into p_budget_version_id;
1749 --
1750 If csr_version%notfound then
1751 --
1752 p_budget_version_id := NULL;
1753 --
1754 End if;
1755 --
1756 Close csr_version;
1757 --
1758 hr_utility.set_location('Leaving :'||l_proc,10);
1759 --
1760 Exception When others then
1761 p_budget_version_id := null;
1762 --
1763
1764 hr_utility.set_location('Exception:'||l_proc, 15);
1765 raise;
1766 --
1767 End get_version_from_cmmtmnt_table;
1768 --
1769 --------------------------------------------------------------------------
1770 --
1771 FUNCTION get_assignment_commitment(p_assignment_id in number,
1772 p_budget_version_id in number default null,
1773 p_element_type_id in number default null,
1774 p_period_start_date in date,
1775 p_period_end_date in date,
1776 p_unit_of_measure_id in number)
1777 RETURN NUMBER
1778 IS
1779 --
1780 l_assign_commitment number := 0;
1781 l_assign_actuals number := 0;
1782 l_last_payroll_dt per_time_periods.end_date%type := NULL;
1783 --
1784 l_budget_id pqh_budgets.budget_id%type := NULL;
1785 l_budget_version_id pqh_budget_versions.budget_version_id%type;
1786 --
1787
1788 l_unit_of_measure per_shared_types.system_type_cd%type;
1789 --
1790 --
1791 l_proc varchar2(72) := g_package||'get_assignment_commitment';
1792 --
1793 Begin
1794 --
1795 hr_utility.set_location('Entering:'||l_proc, 5);
1796 --
1797 --
1798 -- Check if this is a valid assignment
1799 --
1800 if p_assignment_id is not null then
1801 Validate_assignment(p_assignment_id => p_assignment_id);
1802 end if;
1803 --
1804 --
1805 -- Validate if the budget_version_id is a valid one
1806 --
1807 If p_budget_version_id IS NOT NULL then
1808 --
1809 Validate_budget(p_budget_version_id => p_budget_version_id,
1810 p_budget_id => l_budget_id);
1811 --
1812 l_budget_version_id := p_budget_version_id;
1813
1814 --
1815 Else
1816 --
1817 get_version_from_cmmtmnt_table(p_assignment_id => p_assignment_id,
1818 p_start_date => p_period_start_date,
1819 p_end_date => p_period_end_date,
1820 p_budget_version_id => l_budget_version_id);
1821 --
1822 -- If we are unable to get the budget versions for the given dates , we
1823 -- will just return 0 commitment for assignment.
1824 --
1825 If l_budget_version_id IS NULL then
1826 --
1827 Return 0;
1828 --
1829 End if;
1830 --
1831 End if;
1832 --
1833 -- Validate if the unit of measure is valid in per_shared_types
1834 -- Also , check if the unit has been budgeted for in the budget.
1835 --
1836 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
1837 p_unit_of_measure_desc => l_unit_of_measure);
1838 --
1839 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
1840 p_budget_id => l_budget_id);
1841 --
1842 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
1843 p_cmmtmnt_end_dt => p_period_end_date,
1844 p_budget_id => l_budget_id);
1845 --
1846 l_assign_commitment := 0;
1847 --
1848 -- If the UOM is money, obtain commitment from pqh_elements_commitment
1849 -- table . Else get commitment from assignment_budget_values.
1850 --
1851 If l_unit_of_measure = 'MONEY' then
1852 --
1853 -- We need to determine the last payroll date of the assignment before
1854 -- we start calculating the commitment.We must calculate commitment
1855 -- from the next day of last payroll run
1856 --
1857 l_assign_actuals := get_assign_money_actuals
1858 (p_budget_id => l_budget_id, /*2716884*/
1859 p_assignment_id => p_assignment_id,
1860 p_element_type_id => p_element_type_id,
1861 p_actuals_start_date=> p_period_start_date,
1862 p_actuals_end_date => p_period_end_date,
1863 p_last_payroll_dt => l_last_payroll_dt);
1864 --
1865 --
1866 If l_last_payroll_dt IS NULL OR
1867 l_last_payroll_dt <= p_period_start_date then
1868 --
1869 l_assign_commitment := get_assign_money_cmmtmnt
1870 (p_budget_version_id => l_budget_version_id,
1871 p_assignment_id => p_assignment_id,
1872 p_element_type_id => p_element_type_id,
1873 p_period_start_date => p_period_start_date,
1874 p_period_end_date => p_period_end_date);
1875 --
1876 Elsif l_last_payroll_dt > p_period_end_date then
1877 l_assign_commitment := 0;
1878 Else
1879 l_assign_commitment := get_assign_money_cmmtmnt
1880 (p_budget_version_id => l_budget_version_id,
1881 p_assignment_id => p_assignment_id,
1882 p_element_type_id => p_element_type_id,
1883 p_period_start_date => l_last_payroll_dt + 1,
1884 p_period_end_date => p_period_end_date);
1885 --
1886 End if;
1887 --
1888 Else
1889 --
1890 l_assign_commitment := get_assignment_budget_values
1891 (p_assignment_id => p_assignment_id,
1892 p_period_start_dt => p_period_start_date,
1893 p_period_end_dt => p_period_end_date,
1894 p_unit_of_measure => l_unit_of_measure);
1895 End if;
1896 --
1897 hr_utility.set_location('Leaving:'||l_proc, 10);
1898 --
1899 RETURN l_assign_commitment;
1900 --
1901 Exception When others then
1902 --
1903 hr_utility.set_location('Exception:'||l_proc, 15);
1904
1905 raise;
1906 --
1907 End get_assignment_commitment;
1908 --
1909 -----------------------------------------------------------------------
1910 --
1911 -- This function calculates only money actuals for a position
1912 -- It is called from get budget commitment and get budget actuals
1913 --
1914 PROCEDURE get_pos_money_amounts
1915 (
1916 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
1917 p_position_id IN per_positions.position_id%TYPE,
1918 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1919 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1920 p_actual_amount OUT NOCOPY number,
1921 p_commitment_amount OUT NOCOPY number,
1922 p_total_amount OUT NOCOPY number
1923 ) IS
1924 --
1925 --
1926 CURSOR csr_pos_assg is
1927 Select distinct ASSG.assignment_id
1928 From per_all_assignments_f ASSG
1929 Where ASSG.position_id = p_position_id
1930 AND ASSG.effective_end_date >= p_start_date
1931 AND ASSG.effective_start_date <= p_end_date;
1932 --
1933 l_position_id pqh_budget_details.position_id%type;
1934 l_position_name hr_all_positions_f.name%type := NULL;
1935 l_assignment_id per_all_assignments_f.assignment_id%type;
1936 l_last_actuals_date per_time_periods.end_date%type;
1937 --
1938 l_budget_id pqh_budgets.budget_id%type := NULL;
1939 --
1940 l_unit_of_measure per_shared_types.system_type_cd%type;
1941 --
1942 l_assignment_actuals number := 0;
1943 l_assignment_commitment number := 0;
1944 l_assignment_total number := 0;
1945 --
1946 l_position_actuals number := 0;
1947 l_position_commitment number := 0;
1948 l_position_total number := 0;
1949 --
1950
1951 l_proc varchar2(72) := g_package||'get_pos_money_amounts';
1952 --
1953 BEGIN
1954 --
1955 hr_utility.set_location('Entering:'||l_proc, 5);
1956 --
1957 --
1958 /*2716884*/
1959 If p_budget_version_id IS NOT NULL then
1960 --
1961 Validate_budget(p_budget_version_id => p_budget_version_id,
1962 p_budget_id => l_budget_id);
1963 End if;
1964
1965 --
1966 -- Use get_pos_money_total for Total
1967 --
1968 --
1969 l_position_actuals :=
1970 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_money_total9
1971 (
1972 p_budget_version_id =>p_budget_version_id,
1973 p_position_id =>p_position_id,
1974 p_actuals_start_date =>p_start_date,
1975 p_actuals_end_date =>p_end_date
1976 -- p_ex_assignment_id =>p_ex_assignment_id
1977 );
1978 --
1979
1980
1981 for i in csr_pos_assg loop
1982 l_assignment_id := i.assignment_id;
1983 --
1984 l_assignment_total := 0;
1985 -- l_assignment_actuals := 0;
1986 l_assignment_commitment := 0;
1987 l_last_actuals_date := NULL;
1988 --
1989 -- get actuals for the assignment and the last payroll run date
1990
1991 -- for the assignment.
1992 --
1993 --
1994 -- Use get_pos_money_total for Total
1995 --
1996 --
1997 /* l_assignment_actuals := get_assign_money_actuals
1998 ( p_budget_id => l_budget_id, -- 2716884
1999 p_assignment_id => l_assignment_id,
2000 p_element_type_id => NULL,
2001 p_actuals_start_date => p_start_date,
2002 p_actuals_end_date => p_end_date,
2003 p_last_payroll_dt => l_last_actuals_date
2004 ); */
2005 --
2006 l_last_actuals_date := get_last_payroll_dt(l_assignment_id,
2007 p_start_date, p_end_date);
2008
2009 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2010 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2011 --
2012 IF l_last_actuals_date IS NULL OR
2013 l_last_actuals_date <= p_start_date THEN
2014 --
2015 -- payroll has never been run for the assignemnr. So actual is zero
2016 --
2017 l_assignment_actuals := 0;
2018 --
2019 l_assignment_commitment := get_assign_money_cmmtmnt
2020 (p_assignment_id => l_assignment_id ,
2021 p_budget_version_id => p_budget_version_id,
2022 p_element_type_id => NULL,
2023 p_period_start_date => p_start_date,
2024 p_period_end_date => p_end_date
2025 );
2026 --
2027 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2028 --
2029 ELSIF l_last_actuals_date >= p_end_date then
2030 --
2031 -- Actuals is available beyond the required period end date .
2032 -- So commitment is 0.
2033 --
2034 l_assignment_commitment := 0;
2035 --
2036 ELSE
2037 --
2038 -- payroll has been run for the position. calculate commitment
2039 -- from the next day of the last payroll run.
2040 --
2041 l_assignment_commitment := get_assign_money_cmmtmnt
2042 (p_assignment_id => l_assignment_id ,
2043 p_budget_version_id => p_budget_version_id,
2044 p_element_type_id => NULL,
2045 p_period_start_date => l_last_actuals_date + 1,
2046 p_period_end_date => p_end_date
2047 );
2048 --
2049 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2050 --
2051 END IF;
2052 --
2053 -- Total up assignment commitment and actual information.
2054 --
2055 -- l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2056 --
2057 -- Total up position commitment and actuals info
2058 --
2059 l_position_commitment := l_position_commitment + NVL(l_assignment_commitment,0);
2060 --
2061 --l_position_actuals := l_position_actuals + NVL(l_assignment_actuals,0);
2062 --
2063 --l_position_total := l_position_total + l_assignment_total;
2064 --
2065 End Loop;
2066 l_position_total := l_position_actuals + l_position_commitment;
2067 --
2068 hr_utility.set_location('All assignments processed',50);
2069 -- Return the actuals , commitment and total
2070 --
2071 p_total_amount := l_position_total;
2072 --
2073 p_commitment_amount := l_position_commitment;
2074 --
2075 p_actual_amount := l_position_actuals;
2076 --
2077 EXCEPTION
2078 WHEN OTHERS THEN
2079 p_actual_amount := null;
2080 p_commitment_amount := null;
2081 p_total_amount := null;
2082 hr_utility.set_location('Exception :'||l_proc,60);
2083 hr_utility.raise_error;
2084 --
2085 End;
2086 --
2087 -----------------------------------------------------------------------
2088 function get_actual_hours(p_assignment_id in number
2089 ,p_asg_start_date in date default sysdate
2090 ,p_start_date in date default sysdate
2091 ,p_end_date in date default sysdate
2092 ,p_effective_date in date default sysdate
2093 ) RETURN NUMBER IS
2094 ---
2095 l_total_weeks number := 0;
2096 l_hours_per_day number := 0;
2097 l_total_hours number := 0;
2098 l_remaining_days number(1) := 0;
2099 l_position_id number;
2100 l_organization_id number;
2101 l_bg_id number;
2102 l_proc varchar2(60) := g_package||'get_actual_hours';
2103
2104 Cursor c_pos_freq(p_position_id number) Is
2105 Select frequency , working_hours
2106 From hr_all_positions_f
2107 Where p_effective_date between effective_start_date and effective_end_date
2108 And position_id = p_position_id;
2109
2110 --frequency, workinig_hours of organization
2111 Cursor c_org_freq(p_organization_id number) Is
2112 SELECT O2.ORG_INFORMATION4 , O2.ORG_INFORMATION3
2113 FROM HR_ORGANIZATION_INFORMATION O2
2114 WHERE O2.ORG_INFORMATION_CONTEXT = 'Work Day Information'
2115 AND O2.organization_id = p_organization_id;
2116
2117 Cursor c_bg_freq(p_bg_id number) Is
2118 SELECT O2.ORG_INFORMATION4 , O2.ORG_INFORMATION3
2119 FROM HR_ORGANIZATION_INFORMATION O2
2120 WHERE O2.ORG_INFORMATION_CONTEXT = 'Work Day Information'
2121 AND O2.organization_id = p_bg_id;
2122
2123 cursor c_assg is
2124 select effective_start_date, effective_end_date,frequency, normal_hours, time_normal_start, time_normal_finish,
2125 position_id,organization_id,business_group_id
2126 from per_all_assignments_f
2127 where p_assignment_id = assignment_id
2128 and effective_start_date = p_asg_start_date;
2129
2130 l_frequency per_all_assignments_f.frequency%Type;
2131 l_normal_hours per_all_assignments_f.normal_hours%Type;
2132 l_assignment_id per_all_assignments_f.assignment_id%Type;
2133 l_bg_normal_day_hours per_all_assignments_f.normal_hours%Type;
2134 l_normal_day_hours per_all_assignments_f.normal_hours%Type;
2135 l_actual_start_date date;
2136 l_actual_end_date date;
2137 l_start_date date;
2138 l_end_date date;
2139 l_assg_start_time varchar2(5);
2140 l_assg_end_time varchar2(5);
2141 l_day_of_date varchar2(1);
2142 l_days_removed number(1) := 0;
2143
2144 Begin
2145 hr_utility.set_location('Entering:'||l_proc, 5);
2146 Open c_assg;
2147 Fetch c_assg Into l_start_date, l_end_date, l_frequency,
2148 l_normal_hours, l_assg_start_time, l_assg_end_time,
2149 l_position_id, l_organization_id, l_bg_id;
2150 if c_assg%notfound then
2151 close c_assg;
2152 return null;
2153 end if;
2154 close c_assg;
2155 hr_utility.set_location('l_assignment_id:'||l_assignment_id, 6);
2156
2157 l_actual_start_date := greatest(l_start_date, p_start_date);
2158 l_actual_end_date := least(l_end_date, p_end_date);
2159
2160 hr_utility.set_location('l_normal_day_hours:'||l_normal_day_hours, 7);
2161 -- Get the total number of weeks in the date range
2162 l_total_weeks := trunc(((l_actual_end_date - l_actual_start_date) + 1)/7);
2163 -- Get the remaining days
2164 l_remaining_days := mod((l_actual_end_date - l_actual_start_date) + 1, 7);
2165 -- Get the total weekend days in the remaining days
2166
2167 For i in 1..l_remaining_days Loop
2168 l_day_of_date := to_char(l_actual_end_date - (i - 1),'D');
2169 -- Sundays and Saturdays are not considered in the remaining days
2170 If l_day_of_date in ('1','7') Then
2171 l_days_removed := nvl(l_days_removed,0) + 1;
2172 End if;
2173 End Loop;
2174
2175 l_remaining_days := (l_remaining_days - l_days_removed);
2176
2177 If l_frequency is null and l_normal_hours is null Then
2178 if l_position_id is not null then
2179 Open c_pos_freq(l_position_id);
2180 Fetch c_pos_freq Into l_frequency, l_normal_hours;
2181 Close c_pos_freq;
2182 end if;
2183 If l_frequency is null and l_normal_hours is null Then
2184 Open c_org_freq(l_organization_id);
2185 Fetch c_org_freq Into l_frequency, l_normal_hours;
2186 Close c_org_freq;
2187 end if;
2188 If l_frequency is null and l_normal_hours is null Then
2189 Open c_bg_freq(l_bg_id);
2190 Fetch c_bg_freq Into l_frequency, l_normal_hours;
2191 Close c_bg_freq;
2192 end if;
2193 end If;
2194 hr_utility.set_location('l_frequency:'||l_frequency ||' l_normal_hours:'||l_normal_hours, 8);
2195 If (l_frequency is not null and l_normal_hours is not null) Then
2196 If l_frequency in ('HO','H') Then
2197 l_hours_per_day := (l_normal_hours * 8); -- taking 8 hrs/day
2198 Elsif l_frequency = 'D' Then
2199 l_hours_per_day := l_normal_hours;
2200 Elsif l_frequency = 'W' Then
2201 l_hours_per_day := (l_normal_hours/5); -- taking 5 days per week
2202 Elsif l_frequency = 'M' Then
2203 l_hours_per_day := (l_normal_hours/160); -- taking 160 hours per month
2204 Else
2205 l_hours_per_day := 0;
2206 End If;
2207 -- Again convert days into weeks to take care of weekends.
2208 l_total_hours := (l_hours_per_day * 5) * l_total_weeks + (l_hours_per_day * l_remaining_days);
2209 Else
2210 l_total_hours := (8 * 5 * l_total_weeks) + (8 * l_remaining_days);
2211 -- taking 8 hours per day as default.
2212 End If;
2213 hr_utility.set_location('l_total_hours:'||l_total_hours, 8);
2214 RETURN (l_total_hours);
2215 Exception
2216 when others then
2217 hr_utility.set_location('Exception :'||l_proc,70);
2218 hr_utility.raise_error;
2219 End;
2220 --- Function to get the actual hours for an entity
2221 ---
2222 FUNCTION get_actual_hours(p_business_grp_id in number
2223 ,p_position_id in number default null
2224 ,p_job_id in number default null
2225 ,p_grade_id in number default null
2226 ,p_organization_id in number default null
2227 ,p_start_date in date default sysdate
2228 ,p_end_date in date default sysdate
2229 ,p_effective_date in date default sysdate
2230 ) RETURN NUMBER IS
2231
2232 l_total_hours number := 0;
2233 l_asg_hours number := 0;
2234 l_proc varchar2(60) := g_package||'get_actual_hours';
2235 --
2236 -- if organization_id is passed, index will be used
2237 --
2238 cursor c_org_assg is
2239 select assignment_id, effective_start_date
2240 from per_all_assignments_f
2241 where organization_id = p_organization_id
2242 and business_group_id = p_business_grp_id
2243 and effective_end_date >= p_start_date
2244 and effective_start_date <= p_end_date ;
2245
2246 cursor c_position_assg is
2247 select assignment_id, effective_start_date
2248 from per_all_assignments_f
2249 where p_position_id = position_id
2250 and effective_end_date >= p_start_date
2251 and effective_start_date <= p_end_date ;
2252
2253 cursor c_job_assg is
2254 select assignment_id, effective_start_date
2255 from per_all_assignments_f
2256 where p_job_id = job_id
2257 and effective_end_date >= p_start_date
2258 and effective_start_date <= p_end_date ;
2259
2260 cursor c_grade_assg is
2261 select assignment_id, effective_start_date
2262 from per_all_assignments_f
2263 where p_grade_id = grade_id
2264 and effective_end_date >= p_start_date
2265 and effective_start_date <= p_end_date ;
2266
2267 Begin
2268 hr_utility.set_location('Entering:'||l_proc, 5);
2269 if p_organization_id is not null then
2270 for i in c_org_assg loop
2271 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2272 p_asg_start_date => i.effective_start_date,
2273 p_start_date => p_start_date,
2274 p_end_date => p_end_date,
2275 p_effective_date => p_effective_date);
2276 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2277 end loop;
2278 elsif p_job_id is not null then
2279 for i in c_job_assg loop
2280 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2281 p_asg_start_date => i.effective_start_date,
2282 p_start_date => p_start_date,
2283 p_end_date => p_end_date,
2284 p_effective_date => p_effective_date);
2285 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2286 end loop;
2287 elsif p_position_id is not null then
2288 for i in c_position_assg loop
2289 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2290 p_asg_start_date => i.effective_start_date,
2291 p_start_date => p_start_date,
2292 p_end_date => p_end_date,
2293 p_effective_date => p_effective_date);
2294 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2295 end loop;
2296 elsif p_grade_id is not null then
2297 for i in c_grade_assg loop
2298 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2299 p_asg_start_date => i.effective_start_date,
2300 p_start_date => p_start_date,
2301 p_end_date => p_end_date,
2302 p_effective_date => p_effective_date);
2303 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2304 end loop;
2305 end if;
2306 RETURN (l_total_hours);
2307 Exception
2308 when others then
2309 hr_utility.set_location('Exception :'||l_proc,70);
2310 hr_utility.raise_error;
2311 End;
2312 ----------------------------------------------------------------
2313 --
2314 -- This function calculates commitment / actuals / total for a position.
2315 -- It is called from get budget commitment and get budget actuals
2316 --
2317 Function get_pos_actual_and_cmmtmnt
2318 (
2319 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
2320 p_position_id IN per_positions.position_id%TYPE,
2321 p_element_type_id IN number default NULL,
2322 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2323 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2324 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2325 p_value_type IN varchar2,
2326 p_ex_assignment_id IN number default -1,
2327 p_validate IN varchar2 default 'Y'
2328 )
2329 RETURN NUMBER IS
2330 --
2331 --
2332 CURSOR csr_pos_assg is
2333 Select distinct ASSG.assignment_id
2334 From per_all_assignments_f ASSG
2335 Where ASSG.position_id = p_position_id
2336 AND ASSG.effective_end_date >= p_start_date
2337 AND ASSG.effective_start_date <= p_end_date
2338 AND ASSG.assignment_id <> p_ex_assignment_id;
2339 --
2340 l_position_id pqh_budget_details.position_id%type;
2341 l_position_name hr_all_positions_f.name%type := NULL;
2342 l_assignment_id per_all_assignments_f.assignment_id%type;
2343 l_last_actuals_date per_time_periods.end_date%type;
2344 --
2345 l_budget_id pqh_budgets.budget_id%type := NULL;
2346 --
2347 l_unit_of_measure per_shared_types.system_type_cd%type;
2348 --
2349 l_assignment_actuals number := 0;
2350 l_assignment_commitment number := 0;
2351 l_assignment_total number := 0;
2352 --
2353 l_position_actuals number := 0;
2354 l_position_commitment number := 0;
2355 l_position_total number := 0;
2356
2357 -- Following two Variable Declerations done by vevenkat for bug 2628563
2358 l_business_group_id hr_all_organization_units.Business_group_id%TYPE := hr_general.get_business_group_id;
2359 l_effective_date Date := hr_general.effective_date;
2360 --
2361
2362 l_proc varchar2(72) := g_package||'get_pos_actual_and_cmmtmnt';
2363 --
2364 BEGIN
2365 --
2366 hr_utility.set_location('Entering:'||l_proc, 5);
2367 IF ( p_validate = 'Y') THEN
2368 --
2369 --
2370 -- CHECK IF THIS IS A VALID BUDGET .
2371 --
2372 Validate_budget(p_budget_version_id => p_budget_version_id,
2373 p_budget_id => l_budget_id);
2374 --
2375 -- CHECK IF THIS IS A VALID POSITION . ALSO DOES THIS POSITION
2376 -- BELONG IN THE PASSED BUDGET.
2377 --
2378 Validate_position(p_budget_version_id => p_budget_version_id,
2379 p_position_id => p_position_id);
2380 --
2381 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
2382 p_budget_id => l_budget_id);
2383 --
2384 -- Validate if valid dates have been passed.
2385 --
2386 If p_value_type = 'A' OR p_value_type = 'T' then
2387 --
2388 Validate_actuals_dates(p_actuals_start_dt => p_start_date,
2389 p_actuals_end_dt => p_end_date,
2390 p_budget_id => l_budget_id);
2391 --
2392 ElsIf p_value_type = 'C' OR p_value_type = 'T' then
2393 --
2394 hr_utility.set_location('Dates for Commitment Calc: '||p_start_date||' - '||p_end_date, 61);
2395 Validate_commitment_dates(p_cmmtmnt_start_dt => p_start_date,
2396 p_cmmtmnt_end_dt => p_end_date,
2397 p_budget_id => l_budget_id);
2398 --
2399 Else
2400 --
2401 Return 0;
2402 --
2403
2404 End if;
2405 END IF;
2406 --
2407 --
2408 -- Validate unit of measure.
2409 --
2410 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
2411 p_unit_of_measure_desc => l_unit_of_measure);
2412 --
2413 --
2414 If l_unit_of_measure = 'MONEY' then
2415 --
2416 -- calculate actuals and commitment for each assignment.
2417 --
2418 for i in csr_pos_assg Loop
2419 l_assignment_id := i.assignment_id;
2420 --
2421 hr_utility.set_location('Assignments found: '||l_assignment_id, 62);
2422 l_assignment_total := 0;
2423 l_assignment_actuals := 0;
2424 l_assignment_commitment := 0;
2425 l_last_actuals_date := NULL;
2426 --
2427 -- get actuals for the assignment and the last payroll run date
2428
2429 -- for the assignment.
2430 --
2431 If p_value_type = 'A' OR p_value_type = 'T' then
2432 l_assignment_actuals := get_assign_money_actuals
2433 ( p_budget_id => l_budget_id, /*2716884*/
2434 p_assignment_id => l_assignment_id,
2435 p_element_type_id => p_element_type_id,
2436 p_actuals_start_date => p_start_date,
2437 p_actuals_end_date => p_end_date,
2438 p_last_payroll_dt => l_last_actuals_date
2439 );
2440 --
2441 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2442 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2443 --
2444 l_position_actuals := l_position_actuals + NVL(l_assignment_actuals,0);
2445 End If;
2446
2447 If p_value_type in ('C','T') then
2448 -- Get last_actuals_date only if 'C' (in case of T, it is already evaluated
2449 if ( p_value_type = 'C') then
2450 l_last_actuals_date := get_last_payroll_dt (l_assignment_id, p_start_date, p_end_date);
2451 end if;
2452 hr_utility.set_location('Last Actual Date: '||to_char(l_last_actuals_date)||' - '||to_char(p_start_date), 63);
2453 IF (l_last_actuals_date IS NULL OR
2454 l_last_actuals_date <= p_start_date ) THEN
2455 --
2456 -- payroll has never been run for the position. So actual is zero
2457 --
2458 l_assignment_actuals := 0;
2459 --
2460 l_assignment_commitment := get_assign_money_cmmtmnt
2461 (p_assignment_id => l_assignment_id ,
2462 p_budget_version_id => p_budget_version_id,
2463 p_element_type_id => p_element_type_id,
2464 p_period_start_date => p_start_date,
2465 p_period_end_date => p_end_date
2466 );
2467 --
2468 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2469 --
2470 ELSIF l_last_actuals_date >= p_end_date then
2471 --
2472 -- Actuals is available beyond the required period end date .
2473 -- So commitment is 0.
2474 --
2475 l_assignment_commitment := 0;
2476 --
2477 ELSE
2478 hr_utility.set_location('Payroll has run..: '||to_char(l_last_actuals_date+1)||' - '||to_char(p_end_date), 64);
2479 --
2480 -- payroll has been run for the position. calculate commitment
2481 -- from the next day of the last payroll run.
2482 --
2483 l_assignment_commitment := get_assign_money_cmmtmnt
2484 (p_assignment_id => l_assignment_id ,
2485 p_budget_version_id => p_budget_version_id,
2486 p_element_type_id => p_element_type_id,
2487 p_period_start_date => l_last_actuals_date + 1,
2488 p_period_end_date => p_end_date
2489 );
2490 --
2491 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2492 --
2493 END IF;
2494 l_position_commitment := l_position_commitment + NVL(l_assignment_commitment,0);
2495 End If;
2496
2497 If p_value_type = 'T' then
2498 --
2499 l_position_total := l_position_total + NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2500 --
2501 End If;
2502 End Loop;
2503 --
2504 hr_utility.set_location('All assignments processed',50);
2505
2506 --
2507 --
2508 -- Return the actuals or commitment or total
2509 -- depending on what value type was passed.
2510 --
2511 If p_value_type = 'T' then
2512 --
2513 RETURN l_position_total;
2514 --
2515 Elsif p_value_type = 'C' then
2516 --
2517 hr_utility.set_location('Returning Commitment amount: '||l_position_commitment,65);
2518 RETURN l_position_commitment;
2519 --
2520 Elsif p_value_type = 'A' then
2521 --
2522 RETURN l_position_actuals;
2523 --
2524 Else
2525 --
2526 RETURN 0;
2527 --
2528 End if;
2529
2530 --
2531 -- Added the If conditions for HOURS for bug 2628563
2532 Elsif l_unit_of_measure = 'HOURS' then
2533
2534 l_position_total := get_actual_hours( p_business_grp_id => l_business_group_id
2535 ,p_position_id => p_position_id
2536 ,p_job_id => to_number(NULL)
2537 ,p_grade_id => to_number(NULL)
2538 ,p_organization_id => to_number(NULL)
2539 ,p_start_date => p_start_date
2540 ,p_end_date => p_end_date
2541 ,p_effective_date => l_effective_date);
2542
2543 Else
2544 --
2545 l_position_total := get_pos_budget_values
2546 (p_position_id => p_position_id,
2547 p_period_start_dt => p_start_date,
2548 p_period_end_dt => p_end_date,
2549 p_unit_of_measure => l_unit_of_measure);
2550 --
2551 End if;
2552 --
2553 RETURN l_position_total;
2554 --
2555
2556 EXCEPTION
2557 WHEN OTHERS THEN
2558 hr_utility.set_location('Exception :'||l_proc,60);
2559 hr_utility.set_location(sqlerrm,99);
2560 hr_utility.raise_error;
2561
2562 End;
2563
2564 --
2565 --
2566 -- This function calculates commitment / actuals / total for an entity.
2567 -- It is called from get budget commitment and get budget actuals
2568 --
2569 Function get_ent_actual_and_cmmtmnt
2570 (
2571 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
2572 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
2573 p_entity_id IN pqh_budget_details.position_id%TYPE,
2574 p_element_type_id IN number default NULL,
2575 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2576 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2577 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2578 p_value_type IN varchar2
2579 )
2580 RETURN NUMBER IS
2581 --
2582 --
2583 CURSOR csr_pos_assg (p_business_group_id number) is
2584 Select distinct ASSG.assignment_id
2585 From per_all_assignments_f ASSG
2586 Where ASSG.position_id = p_entity_id
2587 and business_group_id = p_business_group_id
2588 AND ASSG.effective_end_date >= p_start_date
2589 AND ASSG.effective_start_date <= p_end_date;
2590 --
2591 CURSOR csr_job_assg(p_business_group_id number) is
2592 Select distinct ASSG.assignment_id
2593 From per_all_assignments_f ASSG
2594 Where ASSG.job_id = p_entity_id
2595 and business_group_id = p_business_group_id
2596 AND ASSG.effective_end_date >= p_start_date
2597 AND ASSG.effective_start_date <= p_end_date;
2598 --
2599 CURSOR csr_grade_assg(p_business_group_id number) is
2600 Select distinct ASSG.assignment_id
2601 From per_all_assignments_f ASSG
2602 Where ASSG.grade_id = p_entity_id
2603 and business_group_id = p_business_group_id
2604 AND ASSG.effective_end_date >= p_start_date
2605 AND ASSG.effective_start_date <= p_end_date ;
2606 --
2607 CURSOR csr_org_assg(p_business_group_id number) is
2608 Select distinct ASSG.assignment_id
2609 From per_all_assignments_f ASSG
2610 Where ASSG.organization_id = p_entity_id
2611 and business_group_id = p_business_group_id
2612 AND ASSG.effective_end_date >= p_start_date
2613 AND ASSG.effective_start_date <= p_end_date;
2614 --
2615
2616 l_assignment_id per_all_assignments_f.assignment_id%type;
2617 l_business_group_id per_all_assignments_f.business_group_id%type;
2618 l_last_actuals_date per_time_periods.end_date%type;
2619 --
2620 l_budget_id pqh_budgets.budget_id%type := NULL;
2621 --
2622 l_unit_of_measure per_shared_types.system_type_cd%type;
2623 --
2624 l_assignment_actuals number := 0;
2625 l_assignment_commitment number := 0;
2626 l_assignment_total number := 0;
2627 --
2628 l_entity_actuals number := 0;
2629 l_entity_commitment number := 0;
2630 l_entity_total number := 0;
2631 l_effective_date date;
2632
2633 CURSOR csr_bg_id is
2634 Select business_group_id
2635 From pqh_budgets
2636 Where budget_id = l_budget_id;
2637
2638 --
2639 l_proc varchar2(72) := g_package||'get_ent_actual_and_cmmtmnt';
2640 --
2641 BEGIN
2642 --
2643 hr_utility.set_location('Entering:'||l_proc, 5);
2644 --
2645 --
2646 -- CHECK IF THIS IS A VALID BUDGET .
2647 --
2648 Validate_budget(p_budget_version_id => p_budget_version_id,
2649 p_budget_id => l_budget_id);
2650
2651 If p_budgeted_entity_cd = 'POSITION' Then
2652 --
2653 -- CHECK IF THIS IS A VALID POSITION AND ALSO DOES THIS POSITION
2654 -- BELONG IN THE PASSED BUDGET.
2655 --
2656 Validate_position(p_budget_version_id => p_budget_version_id,
2657 p_position_id => p_entity_id);
2658
2659
2660 Elsif p_budgeted_entity_cd = 'JOB' Then
2661 --
2662 -- CHECK IF THIS IS A VALID JOB AND ALSO DOES THIS JOB
2663 -- BELONG IN THE PASSED BUDGET.
2664 --
2665 Validate_job(p_budget_version_id => p_budget_version_id,
2666 p_job_id => p_entity_id);
2667
2668 Elsif p_budgeted_entity_cd = 'GRADE' Then
2669 --
2670 -- CHECK IF THIS IS A VALID GRADE AND ALSO DOES THIS GRADE
2671 -- BELONG IN THE PASSED BUDGET.
2672 --
2673 Validate_grade(p_budget_version_id => p_budget_version_id,
2674 p_grade_id => p_entity_id);
2675
2676 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
2677 --
2678 -- CHECK IF THIS IS A VALID ORGANIZATION AND ALSO DOES THIS ORGANIZATION
2679 -- BELONG IN THE PASSED BUDGET.
2680 --
2681 Validate_organization(p_budget_version_id => p_budget_version_id,
2682 p_organization_id => p_entity_id);
2683
2684 End if;
2685 --
2686 --
2687 -- Validate unit of measure.
2688 --
2689 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
2690
2691 p_unit_of_measure_desc => l_unit_of_measure);
2692 --
2693 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
2694 p_budget_id => l_budget_id);
2695 --
2696 -- Validate if valid dates have been passed.
2697 --
2698 If p_value_type = 'A' OR p_value_type = 'T' then
2699 --
2700 Validate_actuals_dates(p_actuals_start_dt => p_start_date,
2701 p_actuals_end_dt => p_end_date,
2702 p_budget_id => l_budget_id);
2703 --
2704 ElsIf p_value_type = 'C' OR p_value_type = 'T' then
2705 --
2706 Validate_commitment_dates(p_cmmtmnt_start_dt => p_start_date,
2707 p_cmmtmnt_end_dt => p_end_date,
2708 p_budget_id => l_budget_id);
2709 --
2710 Else
2711 --
2712 Return 0;
2713 --
2714 End if;
2715 l_effective_date := hr_general.effective_date;
2716 l_business_group_id := hr_general.get_business_group_id;
2717 If l_business_group_id is null Then
2718 Open csr_bg_id;
2719 Fetch csr_bg_id into l_business_group_id;
2720 Close csr_bg_id;
2721 End if;
2722 --
2723 --
2724 If l_unit_of_measure = 'MONEY' then
2725 --
2726 -- calculate actuals and commitment for each assignment.
2727 --
2728 If p_budgeted_entity_cd = 'POSITION' Then
2729 for i in csr_pos_assg(l_business_group_id) loop
2730 l_assignment_id := i.assignment_id;
2731 --
2732 l_assignment_total := 0;
2733 l_assignment_actuals := 0;
2734 l_assignment_commitment := 0;
2735 l_last_actuals_date := NULL;
2736 --
2737 -- get actuals for the assignment and the last payroll run date
2738
2739 -- for the assignment.
2740 --
2741 l_assignment_actuals := get_assign_money_actuals
2742 ( p_budget_id => l_budget_id, /*2716884*/
2743 p_assignment_id => l_assignment_id,
2744 p_element_type_id => p_element_type_id,
2745 p_actuals_start_date => p_start_date,
2746 p_actuals_end_date => p_end_date,
2747 p_last_payroll_dt => l_last_actuals_date
2748 );
2749 --
2750 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2751 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2752 --
2753 IF l_last_actuals_date IS NULL OR
2754 l_last_actuals_date <= p_start_date THEN
2755 --
2756 -- payroll has never been run for the position. So actual is zero
2757 --
2758 l_assignment_actuals := 0;
2759 --
2760 l_assignment_commitment := get_assign_money_cmmtmnt
2761 (p_assignment_id => l_assignment_id ,
2762 p_budget_version_id => p_budget_version_id,
2763 p_element_type_id => p_element_type_id,
2764 p_period_start_date => p_start_date,
2765 p_period_end_date => p_end_date
2766 );
2767 --
2768 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2769 --
2770 ELSIF l_last_actuals_date >= p_end_date then
2771 --
2772 -- Actuals is available beyond the required period end date .
2773 -- So commitment is 0.
2774 --
2775 l_assignment_commitment := 0;
2776 --
2777 ELSE
2778 --
2779 -- payroll has been run for the position. calculate commitment
2780 -- from the next day of the last payroll run.
2781 --
2782 l_assignment_commitment := get_assign_money_cmmtmnt
2783 (p_assignment_id => l_assignment_id ,
2784 p_budget_version_id => p_budget_version_id,
2785 p_element_type_id => p_element_type_id,
2786 p_period_start_date => l_last_actuals_date + 1,
2787 p_period_end_date => p_end_date
2788 );
2789 --
2790 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2791 --
2792 END IF;
2793 --
2794 -- Total up assignment commitment and actual information.
2795 --
2796 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2797 --
2798 -- Total up position commitment and actuals info
2799 --
2800 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2801 --
2802 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2803 --
2804 l_entity_total := l_entity_total + l_assignment_total;
2805 --
2806 End Loop;
2807 --
2808 hr_utility.set_location('All assignments processed',50);
2809 Elsif p_budgeted_entity_cd = 'JOB' Then
2810 for i in csr_job_assg(l_business_group_id) loop
2811 l_assignment_id := i.assignment_id;
2812 --
2813 l_assignment_total := 0;
2814 l_assignment_actuals := 0;
2815 l_assignment_commitment := 0;
2816 l_last_actuals_date := NULL;
2817 --
2818 -- get actuals for the assignment and the last payroll run date
2819
2820 -- for the assignment.
2821 --
2822 l_assignment_actuals := get_assign_money_actuals
2823 ( p_budget_id => l_budget_id, /*2716884*/
2824 p_assignment_id => l_assignment_id,
2825 p_element_type_id => p_element_type_id,
2826 p_actuals_start_date => p_start_date,
2827 p_actuals_end_date => p_end_date,
2828 p_last_payroll_dt => l_last_actuals_date
2829 );
2830 --
2831 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2832 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2833 --
2834 IF l_last_actuals_date IS NULL OR
2835 l_last_actuals_date <= p_start_date THEN
2836 --
2837 -- payroll has never been run for the job. So actual is zero
2838 --
2839 l_assignment_actuals := 0;
2840 --
2841 l_assignment_commitment := get_assign_money_cmmtmnt
2842 (p_assignment_id => l_assignment_id ,
2843 p_budget_version_id => p_budget_version_id,
2844 p_element_type_id => p_element_type_id,
2845 p_period_start_date => p_start_date,
2846 p_period_end_date => p_end_date
2847 );
2848 --
2849 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2850 --
2851 ELSIF l_last_actuals_date >= p_end_date then
2852 --
2853 -- Actuals is available beyond the required period end date .
2854 -- So commitment is 0.
2855 --
2856 l_assignment_commitment := 0;
2857 --
2858 ELSE
2859 --
2860 -- payroll has been run for the job. calculate commitment
2861 -- from the next day of the last payroll run.
2862 --
2863 l_assignment_commitment := get_assign_money_cmmtmnt
2864 (p_assignment_id => l_assignment_id ,
2865 p_budget_version_id => p_budget_version_id,
2866 p_element_type_id => p_element_type_id,
2867 p_period_start_date => l_last_actuals_date + 1,
2868 p_period_end_date => p_end_date
2869 );
2870 --
2871 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2872 --
2873 END IF;
2874 --
2875 -- Total up assignment commitment and actual information.
2876 --
2877 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2878 --
2879 -- Total up job commitment and actuals info
2880 --
2881 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2882 --
2883 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2884 --
2885 l_entity_total := l_entity_total + l_assignment_total;
2886 --
2887 End Loop;
2888 --
2889 hr_utility.set_location('All assignments processed',50);
2890 Elsif p_budgeted_entity_cd = 'GRADE' Then
2891 for i in csr_grade_assg(l_business_group_id) loop
2892 l_assignment_id := i.assignment_id;
2893 --
2894 l_assignment_total := 0;
2895 l_assignment_actuals := 0;
2896 l_assignment_commitment := 0;
2897 l_last_actuals_date := NULL;
2898 --
2899 -- get actuals for the assignment and the last payroll run date
2900
2901 -- for the assignment.
2902 --
2903 l_assignment_actuals := get_assign_money_actuals
2904 ( p_budget_id => l_budget_id, /*2716884*/
2905 p_assignment_id => l_assignment_id,
2906 p_element_type_id => p_element_type_id,
2907 p_actuals_start_date => p_start_date,
2908 p_actuals_end_date => p_end_date,
2909 p_last_payroll_dt => l_last_actuals_date
2910 );
2911 --
2912 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2913 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2914 --
2915 IF l_last_actuals_date IS NULL OR
2916 l_last_actuals_date <= p_start_date THEN
2917 --
2918 -- payroll has never been run for the grade. So actual is zero
2919 --
2920 l_assignment_actuals := 0;
2921 --
2922 l_assignment_commitment := get_assign_money_cmmtmnt
2923 (p_assignment_id => l_assignment_id ,
2924 p_budget_version_id => p_budget_version_id,
2925 p_element_type_id => p_element_type_id,
2926 p_period_start_date => p_start_date,
2927 p_period_end_date => p_end_date
2928 );
2929 --
2930 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2931 --
2932 ELSIF l_last_actuals_date >= p_end_date then
2933 --
2934 -- Actuals is available beyond the required period end date .
2935 -- So commitment is 0.
2936 --
2937 l_assignment_commitment := 0;
2938 --
2939 ELSE
2940 --
2941 -- payroll has been run for the grade. calculate commitment
2942 -- from the next day of the last payroll run.
2943 --
2944 l_assignment_commitment := get_assign_money_cmmtmnt
2945 (p_assignment_id => l_assignment_id ,
2946 p_budget_version_id => p_budget_version_id,
2947 p_element_type_id => p_element_type_id,
2948 p_period_start_date => l_last_actuals_date + 1,
2949 p_period_end_date => p_end_date
2950 );
2951 --
2952 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2953 --
2954 END IF;
2955 --
2956 -- Total up assignment commitment and actual information.
2957 --
2958 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2959 --
2960 -- Total up grade commitment and actuals info
2961 --
2962 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2963 --
2964 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2965 --
2966 l_entity_total := l_entity_total + l_assignment_total;
2967 --
2968 End Loop;
2969 --
2970 hr_utility.set_location('All assignments processed',50);
2971 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
2972 for i in csr_org_assg(l_business_group_id) loop
2973 l_assignment_id := i.assignment_id;
2974
2975 --
2976 l_assignment_total := 0;
2977 l_assignment_actuals := 0;
2978 l_assignment_commitment := 0;
2979 l_last_actuals_date := NULL;
2980 --
2981 -- get actuals for the assignment and the last payroll run date
2982
2983 -- for the assignment.
2984 --
2985 l_assignment_actuals := get_assign_money_actuals
2986 ( p_budget_id => l_budget_id, /*2716884*/
2987 p_assignment_id => l_assignment_id,
2988 p_element_type_id => p_element_type_id,
2989 p_actuals_start_date => p_start_date,
2990 p_actuals_end_date => p_end_date,
2991 p_last_payroll_dt => l_last_actuals_date
2992 );
2993 --
2994 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2995 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2996 --
2997 IF l_last_actuals_date IS NULL OR
2998 l_last_actuals_date <= p_start_date THEN
2999 --
3000 -- payroll has never been run for the organization. So actual is zero
3001 --
3002 l_assignment_actuals := 0;
3003 --
3004 l_assignment_commitment := get_assign_money_cmmtmnt
3005 (p_assignment_id => l_assignment_id ,
3006 p_budget_version_id => p_budget_version_id,
3007 p_element_type_id => p_element_type_id,
3008 p_period_start_date => p_start_date,
3009 p_period_end_date => p_end_date
3010 );
3011 --
3012 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
3013 --
3014 ELSIF l_last_actuals_date >= p_end_date then
3015 --
3016 -- Actuals is available beyond the required period end date .
3017 -- So commitment is 0.
3018 --
3019 l_assignment_commitment := 0;
3020 --
3021 ELSE
3022 --
3023 -- payroll has been run for the organization. calculate commitment
3024 -- from the next day of the last payroll run.
3025 --
3026 l_assignment_commitment := get_assign_money_cmmtmnt
3027 (p_assignment_id => l_assignment_id ,
3028 p_budget_version_id => p_budget_version_id,
3029 p_element_type_id => p_element_type_id,
3030 p_period_start_date => l_last_actuals_date + 1,
3031 p_period_end_date => p_end_date
3032 );
3033 --
3034 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
3035 --
3036 END IF;
3037 --
3038 -- Total up assignment commitment and actual information.
3039 --
3040 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
3041 --
3042 -- Total up organization commitment and actuals info
3043 --
3044 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
3045 --
3046 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
3047 --
3048 l_entity_total := l_entity_total + l_assignment_total;
3049 --
3050 End Loop;
3051 --
3052 hr_utility.set_location('All assignments processed',50);
3053 End if;
3054 --
3055 -- Return the actuals or commitment or total
3056 -- depending on what value type was passed.
3057 --
3058 If p_value_type = 'T' then
3059 --
3060 RETURN l_entity_total;
3061 --
3062 Elsif p_value_type = 'C' then
3063 --
3064 RETURN l_entity_commitment;
3065 --
3066 Elsif p_value_type = 'A' then
3067 --
3068 RETURN l_entity_actuals;
3069 --
3070 Else
3071 --
3072 RETURN 0;
3073 --
3074 End if;
3075 --
3076 Elsif l_unit_of_measure = 'HOURS' then
3077 If p_budgeted_entity_cd = 'POSITION' Then
3078 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3079 ,p_position_id => p_entity_id
3080 ,p_job_id => to_number(NULL)
3081 ,p_grade_id => to_number(NULL)
3082 ,p_organization_id => to_number(NULL)
3083 ,p_start_date => p_start_date
3084 ,p_end_date => p_end_date
3085 ,p_effective_date => l_effective_date);
3086 Elsif p_budgeted_entity_cd = 'JOB' Then
3087 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3088 ,p_job_id => p_entity_id
3089 ,p_grade_id => to_number(NULL)
3090 ,p_organization_id => to_number(NULL)
3091 ,p_position_id => to_number(NULL)
3092 ,p_start_date => p_start_date
3093 ,p_end_date => p_end_date
3094 ,p_effective_date => l_effective_date);
3095 Elsif p_budgeted_entity_cd = 'GRADE' Then
3096 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3097 ,p_grade_id => p_entity_id
3098 ,p_position_id => to_number(NULL)
3099 ,p_organization_id => to_number(NULL)
3100 ,p_job_id => to_number(NULL)
3101 ,p_start_date => p_start_date
3102 ,p_end_date => p_end_date
3103 ,p_effective_date => l_effective_date);
3104 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3105 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3106 ,p_organization_id => p_entity_id
3107 ,p_grade_id => to_number(NULL)
3108 ,p_position_id => to_number(NULL)
3109 ,p_job_id => to_number(NULL)
3110 ,p_start_date => p_start_date
3111 ,p_end_date => p_end_date
3112 ,p_effective_date => l_effective_date);
3113 End if;
3114 Else
3115 If p_budgeted_entity_cd = 'POSITION' Then
3116 l_entity_total := hr_discoverer.get_actual_budget_values
3117 (p_bus_group_id => l_business_group_id,
3118 p_position_id => p_entity_id,
3119 p_job_id => NULL,
3120 p_grade_id => NULL,
3121 p_organization_id => NULL,
3122 p_start_date => p_start_date,
3123 p_end_date => p_end_date,
3124 p_unit => l_unit_of_measure,
3125 p_actual_val => NULL);
3126 Elsif p_budgeted_entity_cd = 'JOB' Then
3127 l_entity_total := hr_discoverer.get_actual_budget_values
3128 (p_bus_group_id => l_business_group_id,
3129 p_job_id => p_entity_id,
3130 p_grade_id => NULL,
3131 p_organization_id => NULL,
3132 p_position_id => NULL,
3133 p_start_date => p_start_date,
3134 p_end_date => p_end_date,
3135 p_unit => l_unit_of_measure,
3136 p_actual_val => NULL);
3137 Elsif p_budgeted_entity_cd = 'GRADE' Then
3138 l_entity_total := hr_discoverer.get_actual_budget_values
3139 (p_bus_group_id => l_business_group_id,
3140 p_grade_id => p_entity_id,
3141 p_position_id => NULL,
3142 p_organization_id => NULL,
3143 p_job_id => NULL,
3144 p_start_date => p_start_date,
3145 p_end_date => p_end_date,
3146 p_unit => l_unit_of_measure,
3147 p_actual_val => NULL);
3148 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3149 l_entity_total := hr_discoverer.get_actual_budget_values
3150 (p_bus_group_id => l_business_group_id,
3151 p_organization_id => p_entity_id,
3152 p_grade_id => NULL,
3153 p_position_id => NULL,
3154 p_job_id => NULL,
3155 p_start_date => p_start_date,
3156 p_end_date => p_end_date,
3157 p_unit => l_unit_of_measure,
3158 p_actual_val => NULL);
3159 End if;
3160 --
3161 --
3162 End if;
3163 --
3164 RETURN l_entity_total;
3165 --
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168 hr_utility.set_location('Exception :'||l_proc,60);
3169 hr_utility.raise_error;
3170 End;
3171 --
3172 ------------------------------------------------------------------------
3173 -- The foll two functions return actuals and commitment for a budget
3174 -- version respectively.
3175 ------------------------------------------------------------------------
3176 --
3177 -- This function returns the actuals of a budget version.
3178 --
3179 FUNCTION get_budget_actuals(p_budget_version_id in number,
3180 p_period_start_date in date,
3181 p_period_end_date in date,
3182 p_unit_of_measure_id in number)
3183 RETURN NUMBER IS
3184 --
3185 l_position_actuals number;
3186 l_budget_actuals number;
3187 l_position_id pqh_budget_details.position_id%type;
3188 --
3189 l_budget_id pqh_budgets.budget_id%type := NULL;
3190 l_unit_of_measure per_shared_types.system_type_cd%type;
3191 --
3192 Cursor csr_positions_in_bdgt is
3193 Select Position_id
3194 From pqh_budget_details bdt,pqh_budget_versions bvr
3195 Where bvr.budget_version_id = p_budget_version_id
3196 AND bvr.budget_version_id = bdt.budget_version_id
3197 AND bdt.position_id IS NOT NULL;
3198 --
3199 l_proc varchar2(72) := g_package||'get_budget_actuals';
3200 --
3201 Begin
3202 --
3203 hr_utility.set_location('Entering:'||l_proc, 5);
3204 --
3205 -- Validate if the budget version is valid .
3206 --
3207 Validate_budget(p_budget_version_id => p_budget_version_id,
3208 p_budget_id => l_budget_id);
3209 --
3210 -- Check if the input unit of measure is valid in per_shared_types.
3211 -- Also , check if the uom has been budgeted for in the budget.
3212 --
3213 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3214 p_unit_of_measure_desc => l_unit_of_measure);
3215
3216
3217 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3218 p_budget_id => l_budget_id);
3219 --
3220 Validate_actuals_dates(p_actuals_start_dt => p_period_start_date,
3221 p_actuals_end_dt => p_period_end_date,
3222 p_budget_id => l_budget_id);
3223 --
3224 l_budget_actuals := 0;
3225 --
3226 -- Break budget version into its comprising positions .Calculate the
3227 -- actuals for each position and sum it up to obtain budget actuals.
3228 --
3229 for i in csr_positions_in_bdgt loop
3230 l_position_actuals := get_pos_actual_and_cmmtmnt
3231 (p_budget_version_id => p_budget_version_id,
3232 p_position_id => i.position_id,
3233 p_start_date => p_period_start_date,
3234 p_end_date => p_period_end_date,
3235 p_unit_of_measure_id => p_unit_of_measure_id,
3236 p_value_type => 'A');
3237 l_budget_actuals := nvl(l_budget_actuals,0) + nvl(l_position_actuals,0);
3238 End Loop;
3239 --
3240 hr_utility.set_location('Leaving:'||l_proc, 10);
3241 --
3242 RETURN l_budget_actuals;
3243 --
3244 Exception When others then
3245 hr_utility.set_location('Exception:'||l_proc, 15);
3246 raise ;
3247 --
3248 End;
3249 --
3250 --------------------------------------------------------------------
3251 --
3252 -- This function returns the commitment of a budget version.
3253 --
3254 FUNCTION get_budget_commitment(p_budget_version_id in number,
3255 p_period_start_date in date,
3256 p_period_end_date in date,
3257 p_unit_of_measure_id in number)
3258 RETURN NUMBER
3259 IS
3260 --
3261 l_position_commitment number;
3262 l_budget_commitment number;
3263 l_position_id pqh_budget_details.position_id%type;
3264 --
3265 l_budget_id pqh_budgets.budget_id%type := NULL;
3266 l_unit_of_measure per_shared_types.system_type_cd%type;
3267 --
3268
3269 Cursor csr_positions_in_bdgt is
3270 Select Position_id
3271 From pqh_budget_details bdt,pqh_budget_versions bvr
3272 Where bvr.budget_version_id = p_budget_version_id
3273 AND bvr.budget_version_id = bdt.budget_version_id
3274 AND bdt.position_id IS NOT NULL;
3275 --
3276 l_proc varchar2(72) := g_package||'get_budget_commitment';
3277 --
3278 Begin
3279 --
3280 hr_utility.set_location('Entering:'||l_proc, 5);
3281 --
3282 -- Validate if the budget version is valid .
3283 --
3284 Validate_budget(p_budget_version_id => p_budget_version_id,
3285 p_budget_id => l_budget_id);
3286 --
3287 -- Check if the input unit of measure is valid in per_shared_types.
3288 -- Also , check if the uom has been budgeted for in the budget.
3289 --
3290 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3291 p_unit_of_measure_desc => l_unit_of_measure);
3292
3293
3294 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3295 p_budget_id => l_budget_id);
3296 --
3297 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
3298 p_cmmtmnt_end_dt => p_period_end_date,
3299 p_budget_id => l_budget_id);
3300 --
3301 l_budget_commitment := 0;
3302 --
3303 -- Break budget version into its comprising positions .Calculate the
3304 -- commitment for each position and sum it up to obtain budget commitment.
3305 --
3306 for i in csr_positions_in_bdgt loop
3307 l_position_commitment := get_pos_actual_and_cmmtmnt
3308 (p_budget_version_id => p_budget_version_id,
3309 p_position_id => i.position_id,
3310 p_start_date => p_period_start_date,
3311 p_end_date => p_period_end_date,
3312 p_unit_of_measure_id => p_unit_of_measure_id,
3313 p_value_type => 'C');
3314 --
3315 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_position_commitment,0);
3316 --
3317 End Loop;
3318 hr_utility.set_location('Leaving:'||l_proc, 10);
3319 --
3320 RETURN l_budget_commitment;
3321 --
3322 Exception When others then
3323 hr_utility.set_location('Exception:'||l_proc, 15);
3324 raise;
3325 End;
3326
3327 -----------------------------------------------------------------------------
3328
3329 ------------------------------------------------------------------------
3330 -- The foll two overloaded functions return actuals and commitment for
3331 -- a budget version respectively and for all entity types.
3332 ------------------------------------------------------------------------
3333 --
3334 -- This function returns the actuals of a budget version.
3335 --
3336 FUNCTION get_budget_actuals(p_budget_version_id in number,
3337 p_budgeted_entity_cd in varchar2,
3338 p_period_start_date in date,
3339 p_period_end_date in date,
3340 p_unit_of_measure_id in number)
3341 RETURN NUMBER
3342 IS
3343 --
3344 l_entity_actuals number;
3345 l_budget_actuals number;
3346 l_position_id pqh_budget_details.position_id%type;
3347 l_job_id pqh_budget_details.position_id%type;
3348 l_grade_id pqh_budget_details.position_id%type;
3349 l_organization_id pqh_budget_details.position_id%type;
3350 --
3351 l_budget_id pqh_budgets.budget_id%type := NULL;
3352 l_unit_of_measure per_shared_types.system_type_cd%type;
3353 --
3354
3355 Cursor csr_positions_in_bdgt is
3356 Select Position_id
3357 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3358 Where bvr.budget_id = bgt.budget_id
3359 AND bvr.budget_version_id = p_budget_version_id
3360 AND bvr.budget_version_id = bdt.budget_version_id
3361 AND bgt.budgeted_entity_cd = 'POSITION';
3362 --
3363 Cursor csr_jobs_in_bdgt is
3364 Select job_id
3365 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3366 Where bvr.budget_id = bgt.budget_id
3367 AND bvr.budget_version_id = p_budget_version_id
3368 AND bvr.budget_version_id = bdt.budget_version_id
3369 AND bgt.budgeted_entity_cd = 'JOB';
3370 --
3371 Cursor csr_grades_in_bdgt is
3372 Select grade_id
3373 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3374 Where bvr.budget_id = bgt.budget_id
3375 AND bvr.budget_version_id = p_budget_version_id
3376 AND bvr.budget_version_id = bdt.budget_version_id
3377 AND bgt.budgeted_entity_cd = 'GRADE';
3378 --
3379 Cursor csr_orgs_in_bdgt is
3380 Select organization_id
3381 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3382 Where bvr.budget_id = bgt.budget_id
3383 AND bvr.budget_version_id = p_budget_version_id
3384 AND bvr.budget_version_id = bdt.budget_version_id
3385 AND bgt.budgeted_entity_cd = 'ORGANIZATION';
3386
3387 --
3388 l_proc varchar2(72) := g_package||'get_budget_actuals';
3389 --
3390 Begin
3391 --
3392 hr_utility.set_location('Entering:'||l_proc, 5);
3393 --
3394 -- Validate if the budget version is valid .
3395 --
3396 Validate_budget(p_budget_version_id => p_budget_version_id,
3397 p_budget_id => l_budget_id);
3398 --
3399 -- Check if the input unit of measure is valid in per_shared_types.
3400 -- Also , check if the uom has been budgeted for in the budget.
3401 --
3402 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3403 p_unit_of_measure_desc => l_unit_of_measure);
3404
3405
3406 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3407 p_budget_id => l_budget_id);
3408 --
3409 Validate_actuals_dates(p_actuals_start_dt => p_period_start_date,
3410 p_actuals_end_dt => p_period_end_date,
3411 p_budget_id => l_budget_id);
3412 --
3413 l_budget_actuals := 0;
3414 --
3415 -- Break budget version into its comprising positions .Calculate the
3416 -- actuals for each position and sum it up to obtain budget actuals.
3417 If p_budgeted_entity_cd = 'POSITION' Then
3418 --
3419 Open csr_positions_in_bdgt;
3420 --
3421 Loop
3422 --
3423 Fetch csr_positions_in_bdgt into l_position_id;
3424 --
3425 If csr_positions_in_bdgt%notfound then
3426 Exit;
3427 End if;
3428 --
3429
3430 l_entity_actuals := 0;
3431
3432 --
3433 l_entity_actuals := get_ent_actual_and_cmmtmnt
3434 (p_budget_version_id => p_budget_version_id,
3435 p_budgeted_entity_cd => p_budgeted_entity_cd,
3436 p_entity_id => l_position_id,
3437 p_start_date => p_period_start_date,
3438 p_end_date => p_period_end_date,
3439 p_unit_of_measure_id => p_unit_of_measure_id,
3440 p_value_type => 'A');
3441 --
3442 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3443 --
3444 End Loop;
3445 --
3446 Close csr_positions_in_bdgt;
3447
3448 Elsif p_budgeted_entity_cd = 'JOB' Then
3449 --
3450 Open csr_jobs_in_bdgt;
3451 --
3452 Loop
3453 --
3454 Fetch csr_jobs_in_bdgt into l_job_id;
3455 --
3456 If csr_jobs_in_bdgt%notfound then
3457 Exit;
3458 End if;
3459 --
3460
3461 l_entity_actuals := 0;
3462
3463 --
3464 l_entity_actuals := get_ent_actual_and_cmmtmnt
3465 (p_budget_version_id => p_budget_version_id,
3466 p_budgeted_entity_cd => p_budgeted_entity_cd,
3467 p_entity_id => l_job_id,
3468 p_start_date => p_period_start_date,
3469 p_end_date => p_period_end_date,
3470 p_unit_of_measure_id => p_unit_of_measure_id,
3471 p_value_type => 'A');
3472 --
3473 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3474 --
3475 End Loop;
3476 --
3477 Close csr_jobs_in_bdgt;
3478 --
3479 Elsif p_budgeted_entity_cd = 'GRADE' Then
3480 --
3481 Open csr_grades_in_bdgt;
3482 --
3483 Loop
3484 --
3485 Fetch csr_grades_in_bdgt into l_grade_id;
3486 --
3487 If csr_grades_in_bdgt%notfound then
3488 Exit;
3489 End if;
3490 --
3491
3492 l_entity_actuals := 0;
3493
3494 --
3495 l_entity_actuals := get_ent_actual_and_cmmtmnt
3496 (p_budget_version_id => p_budget_version_id,
3497 p_budgeted_entity_cd => p_budgeted_entity_cd,
3498 p_entity_id => l_grade_id,
3499 p_start_date => p_period_start_date,
3500 p_end_date => p_period_end_date,
3501 p_unit_of_measure_id => p_unit_of_measure_id,
3502 p_value_type => 'A');
3503 --
3504 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3505 --
3506 End Loop;
3507 --
3508 Close csr_grades_in_bdgt;
3509 --
3510 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3511 --
3512 Open csr_orgs_in_bdgt;
3513 --
3514 Loop
3515 --
3516 Fetch csr_orgs_in_bdgt into l_organization_id;
3517 --
3518 If csr_orgs_in_bdgt%notfound then
3519 Exit;
3520 End if;
3521 --
3522
3523 l_entity_actuals := 0;
3524
3525 --
3526 l_entity_actuals := get_ent_actual_and_cmmtmnt
3527 (p_budget_version_id => p_budget_version_id,
3528 p_budgeted_entity_cd => p_budgeted_entity_cd,
3529 p_entity_id => l_organization_id,
3530 p_start_date => p_period_start_date,
3531 p_end_date => p_period_end_date,
3532 p_unit_of_measure_id => p_unit_of_measure_id,
3533 p_value_type => 'A');
3534 --
3535 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3536 --
3537 End Loop;
3538 --
3539 Close csr_orgs_in_bdgt;
3540 --
3541 End If;
3542 --
3543 hr_utility.set_location('Leaving:'||l_proc, 10);
3544 --
3545 RETURN l_budget_actuals;
3546 --
3547 Exception When others then
3548 --
3549
3550 hr_utility.set_location('Exception:'||l_proc, 15);
3551 raise;
3552 --
3553 End;
3554 --
3555 --------------------------------------------------------------------
3556 --
3557 -- This function returns the commitment of a budget version.
3558 --
3559 FUNCTION get_budget_commitment(p_budget_version_id in number,
3560 p_budgeted_entity_cd in varchar2,
3561 p_period_start_date in date,
3562 p_period_end_date in date,
3563 p_unit_of_measure_id in number)
3564 RETURN NUMBER
3565 IS
3566 --
3567 l_entity_commitment number;
3568 l_budget_commitment number;
3569 l_position_id pqh_budget_details.position_id%type;
3570 l_job_id pqh_budget_details.position_id%type;
3571 l_grade_id pqh_budget_details.position_id%type;
3572 l_organization_id pqh_budget_details.position_id%type;
3573 --
3574 l_budget_id pqh_budgets.budget_id%type := NULL;
3575 l_unit_of_measure per_shared_types.system_type_cd%type;
3576 --
3577
3578 Cursor csr_positions_in_bdgt is
3579 Select Position_id
3580 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3581 Where bvr.budget_id = bgt.budget_id
3582 AND bvr.budget_version_id = p_budget_version_id
3583 AND bvr.budget_version_id = bdt.budget_version_id
3584 AND bgt.budgeted_entity_cd = 'POSITION';
3585 --
3586 Cursor csr_jobs_in_bdgt is
3587 Select job_id
3588 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3589 Where bvr.budget_id = bgt.budget_id
3590 AND bvr.budget_version_id = p_budget_version_id
3591 AND bvr.budget_version_id = bdt.budget_version_id
3592 AND bgt.budgeted_entity_cd = 'JOB';
3593 --
3594 Cursor csr_grades_in_bdgt is
3595 Select grade_id
3596 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3597 Where bvr.budget_id = bgt.budget_id
3598 AND bvr.budget_version_id = p_budget_version_id
3599 AND bvr.budget_version_id = bdt.budget_version_id
3600 AND bgt.budgeted_entity_cd = 'GRADE';
3601 --
3602 Cursor csr_orgs_in_bdgt is
3603 Select organization_id
3604 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3605 Where bvr.budget_id = bgt.budget_id
3606 AND bvr.budget_version_id = p_budget_version_id
3607 AND bvr.budget_version_id = bdt.budget_version_id
3608 AND bgt.budgeted_entity_cd = 'ORGANIZATION';
3609
3610 --
3611 l_proc varchar2(72) := g_package||'get_budget_commitment';
3612 --
3613 Begin
3614 --
3615 hr_utility.set_location('Entering:'||l_proc, 5);
3616 --
3617 -- Validate if the budget version is valid .
3618 --
3619 Validate_budget(p_budget_version_id => p_budget_version_id,
3620 p_budget_id => l_budget_id);
3621 --
3622 -- Check if the input unit of measure is valid in per_shared_types.
3623 -- Also , check if the uom has been budgeted for in the budget.
3624 --
3625 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3626 p_unit_of_measure_desc => l_unit_of_measure);
3627
3628
3629 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3630 p_budget_id => l_budget_id);
3631 --
3632 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
3633 p_cmmtmnt_end_dt => p_period_end_date,
3634 p_budget_id => l_budget_id);
3635 --
3636 l_budget_commitment := 0;
3637 --
3638 -- Break budget version into its comprising positions .Calculate the
3639 -- commitment for each position and sum it up to obtain budget commitment.
3640 --
3641 If p_budgeted_entity_cd = 'POSITION' Then
3642 for i in csr_positions_in_bdgt loop
3643 l_entity_commitment := get_ent_actual_and_cmmtmnt
3644 (p_budget_version_id => p_budget_version_id,
3645 p_budgeted_entity_cd => p_budgeted_entity_cd,
3646 p_entity_id => i.position_id,
3647 p_start_date => p_period_start_date,
3648 p_end_date => p_period_end_date,
3649 p_unit_of_measure_id => p_unit_of_measure_id,
3650 p_value_type => 'C');
3651 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3652 End Loop;
3653 Elsif p_budgeted_entity_cd = 'JOB' Then
3654 for i in csr_jobs_in_bdgt loop
3655 l_entity_commitment := get_ent_actual_and_cmmtmnt
3656 (p_budget_version_id => p_budget_version_id,
3657 p_budgeted_entity_cd => p_budgeted_entity_cd,
3658 p_entity_id => i.job_id,
3659 p_start_date => p_period_start_date,
3660 p_end_date => p_period_end_date,
3661 p_unit_of_measure_id => p_unit_of_measure_id,
3662 p_value_type => 'C');
3663 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3664 End Loop;
3665 Elsif p_budgeted_entity_cd = 'GRADE' Then
3666 for i in csr_grades_in_bdgt loop
3667 l_entity_commitment := get_ent_actual_and_cmmtmnt
3668 (p_budget_version_id => p_budget_version_id,
3669 p_budgeted_entity_cd => p_budgeted_entity_cd,
3670 p_entity_id => i.grade_id,
3671 p_start_date => p_period_start_date,
3672 p_end_date => p_period_end_date,
3673 p_unit_of_measure_id => p_unit_of_measure_id,
3674 p_value_type => 'C');
3675 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3676 End Loop;
3677 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3678 for i in csr_orgs_in_bdgt loop
3679 l_entity_commitment := get_ent_actual_and_cmmtmnt
3680 (p_budget_version_id => p_budget_version_id,
3681 p_budgeted_entity_cd => p_budgeted_entity_cd,
3682 p_entity_id => i.organization_id,
3683 p_start_date => p_period_start_date,
3684 p_end_date => p_period_end_date,
3685 p_unit_of_measure_id => p_unit_of_measure_id,
3686 p_value_type => 'C');
3687 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3688 End Loop;
3689 End If;
3690 --
3691 hr_utility.set_location('Leaving:'||l_proc, 10);
3692 --
3693 RETURN l_budget_commitment;
3694 --
3695 Exception When others then
3696 hr_utility.set_location('Exception:'||l_proc, 15);
3697 raise;
3698 End;
3699 --
3700 procedure get_asg_actual_cmmt(p_assignment_id number
3701 ,p_budget_version_id number
3702 ,p_element_type_id number
3703 ,p_start_date date
3704 ,p_end_date date
3705 ,p_assignment_actuals out nocopy number
3706 ,p_assignment_commitment out nocopy number
3707 ,p_assignment_total out nocopy number
3708 ) is
3709 l_last_actuals_date per_time_periods.end_date%type;
3710 l_budget_id pqh_budgets.budget_id%type := NULL;
3711 --
3712 begin
3713 --
3714 p_assignment_total := 0;
3715 p_assignment_actuals := 0;
3716 p_assignment_commitment := 0;
3717 l_last_actuals_date := NULL;
3718 --
3719
3720 /*2716884*/
3721 If p_budget_version_id IS NOT NULL then
3722 --
3723 Validate_budget(p_budget_version_id => p_budget_version_id,
3724 p_budget_id => l_budget_id);
3725 End if;
3726
3727 --
3728 -- get actuals for the assignment and the last payroll run date
3729
3730 -- for the assignment.
3731 --
3732 p_assignment_actuals := get_assign_money_actuals
3733 ( p_budget_id => l_budget_id, /*2716884*/
3734 p_assignment_id => p_assignment_id,
3735 p_element_type_id => p_element_type_id,--Later
3736 p_actuals_start_date => p_start_date,
3737 p_actuals_end_date => p_end_date,
3738 p_last_payroll_dt => l_last_actuals_date
3739 );
3740 --
3741 hr_utility.set_location('Assignment Actual:'||p_assignment_actuals,10);
3742 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
3743 --
3744 IF l_last_actuals_date IS NULL OR
3745 l_last_actuals_date <= p_start_date THEN
3746 --
3747 -- payroll has never been run for the position. So actual is zero
3748 --
3749 p_assignment_actuals := 0;
3750 --
3751 p_assignment_commitment := get_assign_money_cmmtmnt
3752 (p_assignment_id => p_assignment_id,
3753 p_budget_version_id => p_budget_version_id,
3754 p_element_type_id => p_element_type_id,
3755 p_period_start_date => p_start_date,
3756 p_period_end_date => p_end_date
3757 );
3758 --
3759 hr_utility.set_location('Assignment Commitment : '||p_assignment_commitment, 30);
3760 --
3761 ELSIF l_last_actuals_date >= p_end_date then
3762 --
3763 -- Actuals is available beyond the required period end date .
3764 -- So commitment is 0.
3765 --
3766 p_assignment_commitment := 0;
3767 --
3768 ELSE
3769 --
3770 -- payroll has been run for the position. calculate commitment
3771 -- from the next day of the last payroll run.
3772 --
3773 p_assignment_commitment := get_assign_money_cmmtmnt
3774 (p_assignment_id => p_assignment_id ,
3775 p_budget_version_id => p_budget_version_id,
3776 p_element_type_id => p_element_type_id,
3777 p_period_start_date => l_last_actuals_date + 1,
3778 p_period_end_date => p_end_date
3779 );
3780 --
3781 hr_utility.set_location('Assignment Commitment : '||p_assignment_commitment, 40);
3782 --
3783 END IF;
3784 --
3785 -- Total up assignment commitment and actual information.
3786 --
3787 p_assignment_total := NVL(p_assignment_actuals,0) + NVL(p_assignment_commitment,0);
3788 --
3789 end;
3790 ------------------------------------------------------------------------------
3791
3792 ------------------------------------------------------------------------------
3793 --
3794 -- Sreevijay - This is used to calculate actual and commitment totals for a budget entity
3795 -- and unit of measurement.
3796 --
3797 Procedure get_actual_and_cmmtmnt( p_position_id in number default null
3798 ,p_job_id in number default null
3799 ,p_grade_id in number default null
3800 ,p_organization_id in number default null
3801 ,p_budget_entity in varchar2
3802 ,p_element_type_id in number default null
3803 ,p_start_date in date default sysdate
3804 ,p_end_date in date default sysdate
3805 ,p_effective_date in date default sysdate
3806 ,p_unit_of_measure in varchar2
3807 ,p_business_group_id in number
3808 ,p_actual_value out nocopy number
3809 ,p_commt_value out nocopy number
3810 ) is
3811
3812 --
3813 -- Cursor to fetch budgets and budget versions
3814 -- Single or multiple controlled budgets
3815 --
3816 cursor c_budgets is
3817 select bgt.budget_id, budget_version_id, budget_start_date, budget_end_date
3818 from pqh_budgets bgt, pqh_budget_versions ver
3819 where bgt.budget_id = ver.budget_id
3820 and (p_effective_date between date_from and date_to)
3821 and position_control_flag = 'Y'
3822 and budgeted_entity_cd = p_budget_entity
3823 and business_group_id = p_business_group_id -- Line added Bug Fix : 2432715
3824 and (p_start_date <= budget_end_date
3825 and p_end_date >= budget_start_date)
3826 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = p_unit_of_measure
3827 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_unit_of_measure
3828 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure);
3829 --
3830 -- Cursors to fetch assignments
3831 --
3832 cursor csr_position_assg is
3833 select distinct assg.assignment_id
3834 from per_all_assignments_f assg
3835 where business_group_id = p_business_group_id
3836 and p_position_id = assg.position_id
3837 and assg.effective_end_date >= p_start_date
3838 and assg.effective_start_date <= p_end_date;
3839 --
3840 cursor csr_job_assg is
3841 select distinct assg.assignment_id
3842 from per_all_assignments_f assg
3843 where business_group_id = p_business_group_id
3844 and p_job_id = assg.job_id
3845 and assg.effective_end_date >= p_start_date
3846 and assg.effective_start_date <= p_end_date;
3847 --
3848 cursor csr_org_assg is
3849 select distinct assg.assignment_id
3850 from per_all_assignments_f assg
3851 where p_organization_id = assg.organization_id
3852 and business_group_id = p_business_group_id
3853 and assg.effective_end_date >= p_start_date
3854 and assg.effective_start_date <= p_end_date;
3855 --
3856 cursor csr_grade_assg is
3857 select distinct assg.assignment_id
3858 from per_all_assignments_f assg
3859 where business_group_id = p_business_group_id
3860 and p_grade_id = assg.grade_id
3861 and assg.effective_end_date >= p_start_date
3862 and assg.effective_start_date <= p_end_date;
3863 --
3864 l_position_id pqh_budget_details.position_id%type;
3865 l_position_name hr_all_positions_f.name%type := NULL;
3866 l_assignment_id per_all_assignments_f.assignment_id%type;
3867 l_last_actuals_date per_time_periods.end_date%type;
3868 --
3869 l_budget_id pqh_budgets.budget_id%type := NULL;
3870 --
3871 l_unit_of_measure per_shared_types.system_type_cd%type;
3872 --
3873 l_assignment_actuals number := 0;
3874 l_assignment_commitment number := 0;
3875 l_assignment_total number := 0;
3876 --
3877 l_entity_actuals number := 0;
3878 l_entity_commitment number := 0;
3879 l_total_actuals number := 0;
3880 l_total_commitment number := 0;
3881 l_assignment_value number := 0;
3882
3883 --
3884 l_proc varchar2(72) := g_package||'get_actual_and_cmmtmnt';
3885
3886
3887 Begin
3888 hr_utility.set_location('Entering:'||l_proc, 5);
3889 hr_utility.set_location('l_unit_of_measure:'||p_unit_of_measure, 5);
3890 --
3891 --
3892 If p_unit_of_measure = 'MONEY' then
3893 --
3894 -- calculate actuals and commitment for each assignment.
3895 --
3896 hr_utility.set_location('~~NS:Before Loop Position: '||p_position_id, 8);
3897 hr_utility.set_location('~~NS:p_effective_date: '||p_effective_date, 8);
3898 hr_utility.set_location('~~NS:p_business_group_id: '||p_business_group_id, 8);
3899 hr_utility.set_location('~~NS:p_budget_entity: '||p_budget_entity, 8);
3900 hr_utility.set_location('~~NS:p_start_date: '||p_start_date, 8);
3901 hr_utility.set_location('~~NS:p_end_date: '||p_end_date, 8);
3902
3903
3904 For l_budget in c_budgets
3905 Loop
3906 hr_utility.set_location('~~NS:Budget Version: '||l_budget.budget_version_id, 8);
3907 -- modified by svorugan Bug Fix : 2432715
3908 l_assignment_total :=0;
3909 l_entity_commitment :=0;
3910 l_entity_actuals :=0;
3911 -- upto here
3912 if p_position_id is not null then
3913 for i in csr_position_assg loop
3914 l_assignment_id := i.assignment_id;
3915 hr_utility.set_location('~~NS:Before Calling get asg actual cmmt: '||p_position_id, 8);
3916 get_asg_actual_cmmt(l_assignment_id
3917 ,l_budget.budget_version_id
3918 ,p_element_type_id
3919 ,p_start_date
3920 ,p_end_date
3921 ,l_assignment_actuals
3922 ,l_assignment_commitment
3923 ,l_assignment_total);
3924 -- Total up entity commitment and actuals info
3925 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3926 --
3927 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3928 --
3929 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3930 --
3931 End Loop; -- csr_assg
3932 elsif p_organization_id is not null then
3933 for i in csr_org_assg loop
3934 l_assignment_id := i.assignment_id;
3935 get_asg_actual_cmmt(l_assignment_id
3936 ,l_budget.budget_version_id
3937 ,p_element_type_id
3938 ,p_start_date
3939 ,p_end_date
3940 ,l_assignment_actuals
3941 ,l_assignment_commitment
3942 ,l_assignment_total);
3943 -- Total up entity commitment and actuals info
3944 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3945 --
3946 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3947 --
3948 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3949 --
3950 End Loop; -- csr_assg
3951 elsif p_job_id is not null then
3952 for i in csr_job_assg loop
3953 l_assignment_id := i.assignment_id;
3954 get_asg_actual_cmmt(l_assignment_id
3955 ,l_budget.budget_version_id
3956 ,p_element_type_id
3957 ,p_start_date
3958 ,p_end_date
3959 ,l_assignment_actuals
3960 ,l_assignment_commitment
3961 ,l_assignment_total);
3962 -- Total up entity commitment and actuals info
3963 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3964 --
3965 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3966 --
3967 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3968 --
3969 End Loop; -- csr_assg
3970 elsif p_grade_id is not null then
3971 for i in csr_grade_assg loop
3972 l_assignment_id := i.assignment_id;
3973 get_asg_actual_cmmt(l_assignment_id
3974 ,l_budget.budget_version_id
3975 ,p_element_type_id
3976 ,p_start_date
3977 ,p_end_date
3978 ,l_assignment_actuals
3979 ,l_assignment_commitment
3980 ,l_assignment_total);
3981 -- Total up entity commitment and actuals info
3982 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3983 --
3984 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3985 --
3986 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3987 --
3988 End Loop; -- csr_assg
3989 end if;
3990 --
3991 hr_utility.set_location('All assignments processed',50);
3992 l_total_actuals := nvl(l_total_actuals,0) + nvl(l_entity_actuals,0);
3993 l_total_commitment := nvl(l_total_commitment,0) + nvl(l_entity_commitment,0);
3994
3995 hr_utility.set_location('Totals: Commitment | Actuals'||l_total_commitment||' | '||l_total_actuals,50);
3996
3997 End Loop; -- csr_budgets
3998
3999 p_actual_value := l_total_actuals;
4000 p_commt_value := l_total_commitment;
4001
4002 Elsif p_unit_of_measure = 'HOURS' then
4003 -- calculate actual value for an entity if the unit is HOURS
4004 p_actual_value := get_actual_hours(p_business_grp_id => p_business_group_id
4005 ,p_position_id => p_position_id
4006 ,p_job_id => p_job_id
4007 ,p_grade_id => p_grade_id
4008 ,p_organization_id => p_organization_id
4009 ,p_start_date => p_start_date
4010 ,p_end_date => p_end_date
4011 ,p_effective_date => p_effective_date);
4012 p_commt_value := 0;
4013 hr_utility.set_location('Actual Hours:'||p_actual_value, 10);
4014 Else
4015 -- Calculate the actual value for an entity if the unit is FTE/HEADCOUNT etc.
4016 p_actual_value := hr_discoverer.get_actual_budget_values
4017 (p_bus_group_id => p_business_group_id,
4018 p_position_id => p_position_id,
4019 p_job_id => p_job_id,
4020 p_grade_id => p_grade_id,
4021 p_organization_id => p_organization_id,
4022 p_start_date => p_start_date,
4023 p_end_date => p_end_date,
4024 p_unit => p_unit_of_measure,
4025 p_actual_val => NULL);
4026 p_commt_value := 0;
4027 --
4028 End if;
4029 Exception
4030 when others then
4031 p_actual_value := null;
4032 p_commt_value := null;
4033 hr_utility.set_location('Exception :'||l_proc,60);
4034 hr_utility.raise_error;
4035 End get_actual_and_cmmtmnt;
4036 --
4037 --
4038 FUNCTION get_pos_money_total9(
4039 p_position_id number,
4040 p_budget_version_id number,
4041 p_actuals_start_date date,
4042 p_actuals_end_date date)
4043 RETURN NUMBER
4044 IS
4045
4046 cursor csr_actual_exists(p_budget_id number) is
4047 select 1
4048 from pqh_bdgt_cmmtmnt_elmnts
4049 where actual_commitment_type in ('ACTUAL','BOTH')
4050 and budget_id = p_budget_id;
4051
4052 --
4053 Cursor csr_pos_cost(
4054 p_position_id number,
4055 p_actuals_start_date date,
4056 p_actuals_end_date date,
4057 p_budget_id number
4058 ) is
4059
4060 Select sum(pc.costed_value)
4061 From
4062 (select distinct assignment_id, payroll_id from per_all_assignments_f assg
4063 where ASSG.effective_end_date >= p_actuals_start_date
4064 and ASSG.effective_start_date <= p_actuals_end_date
4065 AND assg.position_id = p_position_id
4066 -- and assg.assignment_id <> p_ex_assignment_id
4067 ) a,
4068 pay_payroll_actions ppa,
4069 PAY_ASSIGNMENT_ACTIONS AAC,
4070 PAY_COSTS pc,
4071 PAY_ELEMENT_TYPES_F PET,
4072 PAY_INPUT_VALUES_F INV,
4073 pqh_bdgt_cmmtmnt_elmnts pbce
4074 Where PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
4075 AND PPA.PAYROLL_ID = A.PAYROLL_ID
4076 AND ppa.action_type IN ('Q','R','V','B')
4077 AND ppa.date_earned BETWEEN p_actuals_start_date AND p_actuals_end_date
4078 AND aac.run_type_id IS not NULL
4079 and AAC.ASSIGNMENT_ID = a.assignment_id
4080 and aac.assignment_action_id = pc.assignment_action_id
4081 and pbce.actual_commitment_type in ('ACTUAL','BOTH')
4082 and pbce.budget_id = p_budget_id
4083 and pc.input_value_id = inv.input_value_id
4084 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
4085 and pbce.element_type_id = INV.element_type_id
4086 --AND INV.NAME = 'Pay Value'
4087 AND INV.INPUT_VALUE_ID = pbce.element_input_value_id--'Pay Value'
4088 --AND (PET.CLASSIFICATION_ID = p_cl_id_1 or PET.CLASSIFICATION_ID = p_cl_id_2)
4089 AND BALANCE_OR_COST = 'C';
4090 --
4091 --
4092 cursor curs1(p_position_id number, p_actuals_start_date date) is
4093 select classification_id
4094 from pay_element_classifications,
4095 hr_all_positions_f pos,
4096 HR_ORGANIZATION_INFORMATION O3
4097 where classification_name in ('Earnings', 'Employer Liabilities')
4098 and legislation_code = O3.ORG_INFORMATION9
4099 and pos.position_id = p_position_id
4100 and p_actuals_start_date between pos.effective_start_date and pos.effective_end_date
4101 and pos.business_group_id = O3.ORGANIZATION_ID
4102 and O3.ORG_INFORMATION_CONTEXT = 'Business Group Information';
4103 --
4104 cl_id_1 number;
4105 cl_id_2 number;
4106 --
4107 --
4108 --Cursor to find position costed value
4109 --
4110 Cursor csr_pos_cost1(
4111 p_position_id number,
4112 p_actuals_start_date date,
4113 p_actuals_end_date date,
4114 p_cl_id_1 number,
4115 p_cl_id_2 number) is
4116 Select sum(pc.costed_value)
4117 From
4118 (select distinct assignment_id, payroll_id from per_all_assignments_f assg
4119 where ASSG.effective_end_date >= p_actuals_start_date
4120 and ASSG.effective_start_date <= p_actuals_end_date
4121 AND assg.position_id = p_position_id
4122 -- and assg.assignment_id <> p_ex_assignment_id
4123 ) a,
4124 pay_payroll_actions ppa,
4125 PAY_ASSIGNMENT_ACTIONS AAC,
4126 PAY_COSTS pc,
4127 PAY_ELEMENT_TYPES_F PET,
4128 PAY_INPUT_VALUES_F INV
4129 Where PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
4130 AND PPA.PAYROLL_ID = A.PAYROLL_ID
4131 AND ppa.action_type IN ('Q','R','V','B')
4132 AND ppa.date_earned BETWEEN p_actuals_start_date AND p_actuals_end_date
4133 AND aac.run_type_id IS not NULL
4134 and AAC.ASSIGNMENT_ID = a.assignment_id
4135 and aac.assignment_action_id = pc.assignment_action_id
4136 and pc.input_value_id = inv.input_value_id
4137 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
4138 AND INV.NAME = 'Pay Value'
4139 AND (PET.CLASSIFICATION_ID = p_cl_id_1 or PET.CLASSIFICATION_ID = p_cl_id_2)
4140 AND BALANCE_OR_COST = 'C';
4141 --
4142 --
4143 l_from_period_start_dt date;
4144 l_from_period_end_dt date;
4145 l_to_period_start_dt date;
4146 l_to_period_end_dt date;
4147 --
4148 l_result_value pay_run_result_values.result_value%type;
4149 l_element_type_id pay_run_results.element_type_id%type;
4150 --
4151
4152 l_converted_amt number;
4153 l_position_actuals number;
4154 l_assignment_cmmt number;
4155 --
4156 l_proc varchar2(72) := g_package ||'get_pos_money_total9';
4157 --
4158 l_dummy number;
4159 l_last_payroll_dt date;
4160 l_budget_id pqh_budgets.budget_id%type := NULL;
4161 --
4162 Begin
4163
4164 hr_utility.set_location('Entering :'||l_proc,5);
4165
4166 l_position_actuals := 0;
4167
4168 If p_budget_version_id IS NOT NULL then
4169 --
4170 Validate_budget(p_budget_version_id => p_budget_version_id,
4171 p_budget_id => l_budget_id);
4172 End if;
4173 If (l_budget_id is not null) then
4174 open csr_actual_exists(l_budget_id);
4175 fetch csr_actual_exists into l_dummy;
4176 if csr_actual_exists%found then
4177 -- This function returns the actual expenditure of one assignment record
4178 -- for a given period.
4179 open csr_pos_cost(p_position_id,
4180 p_actuals_start_date,
4181 p_actuals_end_date,
4182 l_budget_id);
4183 fetch csr_pos_cost into l_position_actuals;
4184 close csr_pos_cost;
4185 --
4186 l_position_actuals := nvl(l_position_actuals,0);
4187 --
4188 else
4189
4190 open curs1(p_position_id , p_actuals_start_date);
4191 fetch curs1 into cl_id_1;
4192 fetch curs1 into cl_id_2;
4193 close curs1;
4194 --
4195 if (cl_id_1 is not null or cl_id_2 is not null) then
4196 -- This function returns the actual expenditure of one assignment record
4197 -- for a given period.
4198 open csr_pos_cost1(p_position_id,
4199 p_actuals_start_date,
4200 p_actuals_end_date,
4201 cl_id_1, cl_id_2);
4202 fetch csr_pos_cost1 into l_position_actuals;
4203 close csr_pos_cost1;
4204 --
4205 l_position_actuals := nvl(l_position_actuals,0);
4206 --
4207 end if;
4208 end if;
4209 else
4210 open curs1(p_position_id , p_actuals_start_date);
4211 fetch curs1 into cl_id_1;
4212 fetch curs1 into cl_id_2;
4213 close curs1;
4214 --
4215 if (cl_id_1 is not null or cl_id_2 is not null) then
4216 -- This function returns the actual expenditure of one assignment record -- for a given period.
4217 open csr_pos_cost1(p_position_id,
4218 p_actuals_start_date,
4219 p_actuals_end_date,
4220 cl_id_1, cl_id_2);
4221 fetch csr_pos_cost1 into l_position_actuals;
4222 close csr_pos_cost1;
4223 --
4224 l_position_actuals := nvl(l_position_actuals,0);
4225 --
4226 end if;
4227 end if;
4228 --
4229 hr_utility.set_location('Leaving :'||l_proc,20);
4230 --
4231 RETURN nvl(l_position_actuals,0);
4232 Exception When others then
4233 hr_utility.set_location('Exception:'||l_proc, 25);
4234 RAISE;
4235 End get_pos_money_total9;
4236 --
4237
4238 End pqh_bdgt_actual_cmmtmnt_pkg;