[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.7.12020000.3 2012/07/18 09:17:12 pathota ship $ */
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.effective_date between tp.start_date and tp.end_date --Modified for bug#14174386
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.effective_date between tp.start_date and tp.end_date --Modified for bug#14174386
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.effective_date between tp.start_date and tp.end_date --Fix for bug9300557,Modified for bug#14174386
940 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
941 AND AAC.ASSIGNMENT_ID = p_assignment_id ORDER BY 3 ASC) 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 Loop --Added for bug#12633243
1159 fetch curs into p_ele_id,p_ele_inv_id,p_balance_type_id;
1160 EXIT WHEN curs%NOTFOUND;
1161 if p_balance_type_id is not null then
1162 open csr_bal(p_balance_type_id, '_ASG_LTD');
1163 fetch csr_bal into l_temp;
1164 if csr_bal%found then
1165 /* Commenting this part of the code for now. May be reused once
1166 use of pay_balance_pkg issue is resolved */
1167 /* open csr_asg_action(p_assignment_id,p_actuals_start_date);
1168 fetch csr_asg_action into l_assign_action_id;
1169 l_assignment_start_actuals := pay_balance_pkg.get_value(
1170 l_temp, l_assign_action_id);
1171 close csr_asg_action;
1172 open csr_asg_action(p_assignment_id,p_actuals_end_date);
1173 fetch csr_asg_action into l_assign_action_id;
1174 l_assignment_end_actuals := pay_balance_pkg.get_value(
1175 l_temp, l_assign_action_id);
1176 close csr_asg_action; */
1177 open csr_act_value(p_assignment_id,l_temp,p_actuals_start_date);
1178 fetch csr_act_value into l_assignment_start_actuals;
1179 close csr_act_value;
1180
1181 open csr_act_value(p_assignment_id,l_temp,p_actuals_end_date);
1182 fetch csr_act_value into l_assignment_end_actuals;
1183 close csr_act_value;
1184
1185 l_assignment_actuals := nvl(l_assignment_end_actuals,0) - nvl(l_assignment_start_actuals,0);
1186 l_last_payroll_dt := get_last_payroll_dt(p_assignment_id,
1187 p_actuals_start_date, p_actuals_end_date);
1188 /* get_payroll_run_date(p_assignment_id => p_assignment_id,
1189 p_payroll_id => p_payroll_id,
1190 p_actuals_start_date => p_actuals_start_date,
1191 p_actuals_end_date => p_actuals_end_date,
1192 p_last_payroll_dt => p_last_payroll_dt);
1193 p_last_payroll_dt := p_last_payroll_dt; */
1194 end if;
1195 close csr_bal;
1196 elsif (p_ele_id is not null) then
1197 l_assignment_actuals := nvl(l_assignment_actuals, 0) + nvl(get_element_actuals(p_assignment_id => p_assignment_id,
1198 p_legislation_code => p_legislation_code,
1199 p_payroll_id => p_payroll_id,
1200 p_element_type_id => p_ele_id,
1201 p_actuals_start_date => p_actuals_start_date,
1202 p_actuals_end_date => p_actuals_end_date,
1203 p_ele_input_value_id => p_ele_inv_id,
1204 p_last_payroll_dt => l_last_payroll_dt),0);
1205
1206 -- p_last_payroll_dt := p_last_payroll_dt;
1207 end if;
1208 END LOOP; --Added for bug#12633243
1209 close curs;
1210 else
1211 /* open curs;
1212 fetch curs into p_ele_id,p_ele_inv_id,p_balance_type_id; */
1213 l_assignment_actuals := nvl(l_assignment_actuals,0) + (get_sum_actuals(p_assignment_id => p_assignment_id,
1214 p_legislation_code => p_legislation_code,
1215 p_payroll_id => p_payroll_id,
1216 p_element_type_id => p_element_type_id,
1217 p_actuals_start_date => p_actuals_start_date,
1218 p_actuals_end_date => p_actuals_end_date,
1219 p_last_payroll_dt => l_last_payroll_dt));
1220
1221 -- p_last_payroll_dt := p_last_payroll_dt;
1222 end if;
1223 close csr_actual_exists;
1224 else
1225 l_assignment_actuals := get_sum_actuals(p_assignment_id => p_assignment_id,
1226 p_legislation_code => p_legislation_code,
1227 p_payroll_id => p_payroll_id,
1228 p_element_type_id => p_element_type_id,
1229 p_actuals_start_date => p_actuals_start_date,
1230 p_actuals_end_date => p_actuals_end_date,
1231 p_last_payroll_dt => l_last_payroll_dt);
1232
1233 -- p_last_payroll_dt := p_last_payroll_dt;
1234 end if;
1235
1236 p_last_payroll_dt := l_last_payroll_dt;
1237 hr_utility.set_location('Leaving :'||l_proc,20);
1238 RETURN l_assignment_actuals;
1239 --
1240 Exception When others then
1241 if (csr_actual_exists%isopen) then
1242 close csr_actual_exists;
1243 end if;
1244 p_last_payroll_dt := null;
1245 hr_utility.set_location('Exception:'||l_proc, 25);
1246 raise;
1247 End get_actuals;
1248 --
1249 -- ********************
1250 ------------------------------------------------------------------------
1251 -- get actuals
1252 -- This function calculates the actual expenditure for an assignment
1253 -- for a given period and for a given element type id . If the
1254
1255 -- element type id is not input , then all element types are taken into
1256 -- consideration while computing the actual expenditure of the assignment.
1257
1258 FUNCTION get_actuals(p_assignment_id in number,
1259 p_legislation_code in varchar2,
1260 p_payroll_id in number,
1261 p_element_type_id in number,
1262 p_actuals_start_date in date,
1263 p_actuals_end_date in date,
1264 p_last_payroll_dt out nocopy date)
1265 RETURN NUMBER
1266 IS
1267 --
1268 -- This cursor will find the classification ids.
1269 --
1270 cursor curs is
1271 select classification_id
1272 from pay_element_classifications
1273 where classification_name in ('Employer Liabilities', 'Earnings','Supplemental Earnings')
1274 and legislation_code = p_legislation_code;
1275 cl_id number;
1276
1277 --
1278 -- This cursor returns the actual expenditure for
1279 -- each element type that belongs to each assignment action .
1280 --
1281 Cursor csr_assg_actuals1(p_start_date DATE ,p_assignment_action_id NUMBER,
1282 p_element_type_id number) IS
1283 SELECT sum(RRV.RESULT_VALUE) result_value
1284 FROM
1285 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
1286 PAY_ELEMENT_TYPES_F PET,
1287 PAY_RUN_RESULTS RES
1288 WHERE RES.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1289 AND RES.STATUS IN ( 'P','PA' )
1290 AND PET.CLASSIFICATION_ID = cl_id
1291 AND PET.ELEMENT_TYPE_ID = p_element_type_id
1292 AND p_start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
1293 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
1294 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1295 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1296 AND INV.NAME = 'Pay Value'
1297 AND p_start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
1298 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
1299 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
1300 --
1301 Cursor csr_assg_actuals2(p_start_date DATE ,p_assignment_action_id NUMBER) IS
1302 SELECT sum(RRV.RESULT_VALUE) result_value
1303 FROM
1304 PAY_INPUT_VALUES_F INV,PAY_RUN_RESULT_VALUES RRV,
1305 PAY_ELEMENT_TYPES_F PET,
1306 PAY_RUN_RESULTS RES
1307 WHERE RES.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1308 AND RES.STATUS IN ( 'P','PA' )
1309 AND PET.CLASSIFICATION_ID = cl_id
1310 AND p_start_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
1311 AND PET.ELEMENT_TYPE_ID = RES.ELEMENT_TYPE_ID
1312 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1313 AND RES.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
1314 AND INV.NAME = 'Pay Value'
1315 AND p_start_date BETWEEN INV.EFFECTIVE_START_DATE AND INV.EFFECTIVE_END_DATE
1316 AND RRV.RUN_RESULT_ID = RES.RUN_RESULT_ID
1317 AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID;
1318 --
1319 --
1320 --Cursor to find Assignment Action Id and Time period of Payroll
1321 --
1322 Cursor csr_asg_time_periods is
1323 Select /*+ ORDERED */
1324 tp.start_date,tp.end_date, aac.assignment_action_id
1325 From per_time_periods tp,
1326 pay_payroll_actions ppa,
1327 PAY_ASSIGNMENT_ACTIONS AAC
1328 Where tp.payroll_id=p_payroll_id
1329 AND (tp.start_date <= p_actuals_end_date
1330 and
1331 tp.end_date >= p_actuals_start_date)
1332 and tp.payroll_id = ppa.payroll_id
1333 and ppa.payroll_id = p_payroll_id
1334 and tp.time_period_id = ppa.time_period_id
1335 and ppa.date_earned between tp.start_date and tp.end_date
1336 AND PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
1337 AND AAC.ASSIGNMENT_ID = p_assignment_id
1338 order by tp.end_date;
1339
1340 l_from_period_start_dt date;
1341 l_from_period_end_dt date;
1342 l_to_period_start_dt date;
1343 l_to_period_end_dt date;
1344 --
1345 l_result_value pay_run_result_values.result_value%type;
1346 l_element_type_id pay_run_results.element_type_id%type;
1347 --
1348
1349 l_converted_amt number;
1350 l_assignment_actuals number;
1351 --
1352 l_proc varchar2(72) := g_package ||'get_actuals';
1353 --
1354 l_dummy number;
1355 --
1356 cursor csr_asg_act_exists is
1357 select 1
1358 from dual
1359 where exists
1360 (select null
1361 from pay_assignment_actions
1362 where assignment_id = p_assignment_id);
1363 Begin
1364 --
1365 hr_utility.set_location('Entering :'||l_proc,5);
1366 --
1367 l_assignment_actuals := 0;
1368 --
1369 open csr_asg_act_exists;
1370 fetch csr_asg_act_exists into l_dummy;
1371 if csr_asg_act_exists%found then
1372 -- This function returns the actual expenditure of one assignment record
1373 -- for a given period.
1374 For ctp in csr_asg_time_periods loop
1375 --
1376 l_from_period_start_dt := ctp.start_date;
1377 l_from_period_end_dt := ctp.end_date;
1378 --
1379 --
1380 p_last_payroll_dt := l_from_period_end_dt;
1381 --
1382 -- Sum up the actual expenditure for all the elements to arrive
1383 -- at the actual expenditure of the assignment.
1384 --
1385 -- Actuals start date lies between start and end date of a time period.
1386 -- Hence we need to compute the value from the actuals start date
1387 -- to the end date of the time period.
1388 --
1389 l_to_period_start_dt := greatest(p_actuals_start_date,l_from_period_start_dt);
1390 l_to_period_end_dt := least(p_actuals_end_date,l_from_period_end_dt);
1391 --
1392 for classif in curs loop
1393 cl_id := classif.classification_id;
1394 --
1395 if (p_element_type_id is not null) then
1396 open csr_assg_actuals1(l_from_period_start_dt,
1397 ctp.Assignment_action_id,
1398 p_element_type_id);
1399 fetch csr_assg_actuals1 into l_result_value;
1400 close csr_assg_actuals1;
1401 else
1402 open csr_assg_actuals2(l_from_period_start_dt,
1403 ctp.Assignment_action_id);
1404 fetch csr_assg_actuals2 into l_result_value;
1405 close csr_assg_actuals2;
1406 end if;
1407 l_converted_amt := Convert_actuals(
1408 p_figure => fnd_number.canonical_to_number(l_result_value),
1409 p_from_start_date => l_from_period_start_dt,
1410 p_from_end_date => l_from_period_end_dt,
1411 p_to_start_date => l_to_period_start_dt,
1412 p_to_end_date => l_to_period_end_dt);
1413 l_assignment_actuals := l_assignment_actuals + nvl(l_converted_amt,0);
1414 End loop; /** Clasifications */
1415 End loop; /** csr_asg_time_periods */
1416 End if;
1417 close csr_asg_act_exists;
1418 hr_utility.set_location('Leaving :'||l_proc,20);
1419 --
1420 RETURN l_assignment_actuals;
1421 Exception When others then
1422 if (csr_asg_act_exists%isopen) then
1423 close csr_asg_act_exists;
1424 end if;
1425 p_last_payroll_dt := null;
1426 hr_utility.set_location('Exception:'||l_proc, 25);
1427 raise;
1428 End get_actuals;
1429 --
1430
1431 --------------------------------------------------------------------------
1432 FUNCTION get_assign_money_actuals(p_budget_id in number,
1433 p_assignment_id in number,
1434 p_element_type_id in number,
1435 p_actuals_start_date in date,
1436 p_actuals_end_date in date,
1437 p_last_payroll_dt out nocopy date)
1438 RETURN number is
1439 --
1440 -- Per_all_assignments_f is a date-tracked table. There may be more than
1441 -- one record that is effective in the actuals calculation period
1442 -- with different payrolls.
1443 --
1444 Cursor csr_assg is
1445 Select ASSG.assignment_id,
1446 ASSG.payroll_id,
1447 ASSG.business_group_id,
1448 ASSG.effective_start_date,
1449 ASSG.effective_end_date
1450 From per_all_assignments_f ASSG
1451 Where ASSG.assignment_id = p_assignment_id
1452 AND ASSG.effective_end_date >= p_actuals_start_date
1453 AND ASSG.effective_start_date <= p_actuals_end_date ;
1454 --
1455 l_assignment_id per_all_assignments_f.assignment_id%type;
1456 l_effective_start_date per_all_assignments_f.effective_start_date%type;
1457 l_effective_end_date per_all_assignments_f.effective_end_date%type;
1458 l_payroll_id per_all_assignments_f.payroll_id%type;
1459 l_business_group_id number;
1460 l_legislation_code per_business_groups.legislation_code%type;
1461 --
1462 l_assignment_actuals number;
1463 l_actuals number;
1464 --
1465 l_actuals_sub_start_dt date;
1466 l_actuals_sub_end_dt date;
1467 --
1468 l_proc varchar2(72) := g_package||'get_assign_money_actuals';
1469 --
1470 Begin
1471 --
1472 hr_utility.set_location('Entering :'||l_proc,5);
1473 --
1474 l_assignment_actuals := 0;
1475 --
1476 Open csr_assg;
1477 --
1478 Loop
1479 -- Fetch next record for assignment
1480 Fetch csr_assg into l_assignment_id,l_payroll_id,l_business_group_id,
1481 l_effective_start_date,l_effective_end_date;
1482 --
1483 If csr_assg%notfound then
1484 exit;
1485 End if;
1486 if l_legislation_code is null then
1487 l_legislation_code := get_bg_legislation_code(l_business_group_id);
1488 end if;
1489 --
1490 hr_utility.set_location('Assignment :'||to_char(l_assignment_id),10);
1491 hr_utility.set_location('Payroll :'||to_char(l_payroll_id),15);
1492 --
1493 l_actuals := 0;
1494 --
1495 If l_payroll_id IS NOT NULL then
1496 --
1497 -- Check if the effective start date of this assignment is
1498 -- lesser than the actuals start date . If so , we are interested
1499 -- in calculating actuals only from the actuals start date .
1500 -- Else we will try to calculate actuals from the effective
1501 -- start date .
1502 --
1503 l_actuals_sub_start_dt := greatest(p_actuals_start_date,l_effective_start_date);
1504 --
1505 -- Check if the effective end date of this assignment record is
1506 -- lesser than the actuals end date . If so , we are interested
1507 -- in calculating actuals only upto the effective end date of the
1508 -- assignment . Else we will try to calculate actuals upto from the
1509 -- actuals end date .
1510 --
1511 l_actuals_sub_end_dt := least(p_actuals_end_date,l_effective_end_date);
1512 --
1513 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);
1514 /* l_actuals := get_sum_actuals(p_assignment_id => l_assignment_id,
1515 p_legislation_code => l_legislation_code,
1516 p_payroll_id => l_payroll_id,
1517 p_element_type_id => p_element_type_id,
1518 p_actuals_start_date => l_actuals_sub_start_dt,
1519 p_actuals_end_date => l_actuals_sub_end_dt,
1520 p_last_payroll_dt => p_last_payroll_dt); */
1521 /*2716884*/
1522 l_actuals := get_actuals(p_budget_id => p_budget_id,
1523 p_assignment_id => l_assignment_id,
1524 p_legislation_code => l_legislation_code,
1525 p_payroll_id => l_payroll_id,
1526 p_element_type_id => p_element_type_id,
1527 p_actuals_start_date => l_actuals_sub_start_dt,
1528 p_actuals_end_date => l_actuals_sub_end_dt,
1529 p_last_payroll_dt => p_last_payroll_dt);
1530 End if;
1531 --
1532 l_assignment_actuals := nvl(l_actuals,0) + l_assignment_actuals;
1533 --
1534 End loop;
1535 --
1536 Close csr_assg;
1537 --
1538 -- At this point , we will have an assignments actuals for a given period.
1539 --
1540 hr_utility.set_location('Leaving :'||l_proc,25);
1541 --
1542 RETURN l_assignment_actuals;
1543 --
1544 Exception When others then
1545 p_last_payroll_dt := null;
1546 hr_utility.set_location('Exception:'||l_proc, 30);
1547 raise;
1548 End get_assign_money_actuals;
1549 --
1550 ------------------------------------------------------------------------
1551 --
1552 --
1553 FUNCTION get_assign_money_cmmtmnt(p_assignment_id in number,
1554 p_budget_version_id in number,
1555 p_element_type_id in number default null,
1556 p_period_start_date in date,
1557 p_period_end_date in date)
1558 RETURN NUMBER
1559 IS
1560 --
1561 Cursor csr_assg_commitment is
1562 Select nvl(commitment_amount,0),
1563 commitment_start_date,commitment_end_date
1564 From pqh_element_commitments
1565 Where budget_version_id = p_budget_version_id
1566 AND assignment_id = p_assignment_id
1567 AND element_type_id = nvl(p_element_type_id,element_type_id)
1568 AND commitment_start_date <= p_period_end_date
1569 AND commitment_end_date >= p_period_start_date;
1570 --
1571 l_prorate_start_dt date;
1572 l_prorate_end_dt date;
1573 --
1574 l_commitment_start_date pqh_element_commitments.commitment_start_date%type;
1575 l_commitment_end_date pqh_element_commitments.commitment_end_date%type;
1576 --
1577 l_prorate_amt number ;
1578 l_assign_cmmtmnt number ;
1579 l_amount number;
1580 --
1581 l_proc varchar2(72) := g_package||'get_assign_money_cmmtmnt';
1582 --
1583 Begin
1584 --
1585 hr_utility.set_location('Entering:'||l_proc, 5);
1586 --
1587 l_assign_cmmtmnt := 0;
1588 --
1589 Open csr_assg_commitment;
1590 --
1591
1592 Loop
1593 --
1594 Fetch csr_assg_commitment into l_amount,l_commitment_start_date,
1595 l_commitment_end_date;
1596 --
1597 If csr_assg_commitment%notfound then
1598 exit;
1599 End if;
1600 --
1601 l_prorate_start_dt := greatest(p_period_start_date,l_commitment_start_date);
1602 l_prorate_end_dt := least(p_period_end_date,l_commitment_end_date);
1603 --
1604 hr_utility.set_location('Dates used for evaluating prorated amount: '||l_prorate_start_dt||' - '||l_prorate_end_dt, 66);
1605 l_prorate_amt := l_amount *
1606 (
1607 (l_prorate_end_dt - l_prorate_start_dt +1)/
1608 (l_commitment_end_date - l_commitment_start_date + 1)
1609 );
1610 --
1611 hr_utility.set_location('Amount Found: '||l_amount||' Prorated amount: '||l_prorate_amt, 67);
1612 l_assign_cmmtmnt := l_assign_cmmtmnt + l_prorate_amt;
1613 --
1614 End loop;
1615 --
1616 Close csr_assg_commitment;
1617 hr_utility.set_location('Assignment Commitment returned '||l_assign_cmmtmnt, 68);
1618 --
1619 hr_utility.set_location('Leaving:'||l_proc, 10);
1620 --
1621 RETURN l_assign_cmmtmnt;
1622 --
1623 Exception When others then
1624 --
1625 hr_utility.set_location('Exception:'||l_proc, 15);
1626 raise;
1627 --
1628 End get_assign_money_cmmtmnt;
1629 --
1630 ------------------------------------------------------------------------
1631 --
1632 FUNCTION get_assignment_actuals
1633 (p_assignment_id in number,
1634 p_element_type_id in number default NULL,
1635 p_actuals_start_date in date,
1636 p_actuals_end_date in date,
1637 p_unit_of_measure_id in number,
1638 p_last_payroll_dt out nocopy date)
1639 RETURN NUMBER
1640 is
1641
1642 --
1643 l_unit_of_measure per_shared_types.system_type_cd%type;
1644 l_actuals number := 0;
1645 --
1646 l_proc varchar2(72) := g_package ||'get_assignment_actuals';
1647 --
1648 Begin
1649 --
1650 hr_utility.set_location('Entering :'||l_proc,5);
1651 --
1652 -- Check if input unit of measure is valid in per_shared_types.
1653 --
1654 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
1655 p_unit_of_measure_desc => l_unit_of_measure);
1656 --
1657 -- 1) Check if p_actuals_end_dt > p_actuals_start_dt.
1658 --
1659 If p_actuals_end_date < p_actuals_start_date then
1660 --
1661 FND_MESSAGE.SET_NAME('PQH','PQH_END_DT_LESS_THAN_START_DT');
1662 APP_EXCEPTION.RAISE_EXCEPTION;
1663 --
1664 End if;
1665
1666 --
1667 --
1668 -- Check if this is a valid assignment
1669 --
1670 if p_assignment_id is not null then
1671 Validate_assignment(p_assignment_id => p_assignment_id);
1672 end if;
1673 --
1674 --
1675 -- Check if this is a valid element type, if the element_type is input.
1676 --
1677 Validate_element_type(p_element_type_id => p_element_type_id);
1678 --
1679 -- We have finished doing a basic validation of all the inputs .We can
1680 -- Now,Call the respective function that returns the actuals for a
1681 -- assignment and for the unit of measure.
1682 --
1683 If l_unit_of_measure = 'MONEY' then
1684 --
1685 l_actuals := get_assign_money_actuals
1686 (p_budget_id => null, /*2716884*/
1687 p_assignment_id => p_assignment_id,
1688 p_element_type_id => p_element_type_id,
1689 p_actuals_start_date => p_actuals_start_date,
1690 p_actuals_end_date => p_actuals_end_date,
1691 p_last_payroll_dt => p_last_payroll_dt);
1692
1693 --
1694 Else
1695 --
1696 -- Call get_assignment_budget_values
1697 --
1698 l_actuals := get_assignment_budget_values
1699 (p_assignment_id => p_assignment_id,
1700 p_period_start_dt => p_actuals_start_date,
1701 p_period_end_dt => p_actuals_end_date,
1702 p_unit_of_measure => l_unit_of_measure);
1703 p_last_payroll_dt := NULL;
1704 --
1705 End if;
1706 --
1707 --
1708 hr_utility.set_location('Leaving :'||l_proc,10);
1709 --
1710 RETURN l_actuals;
1711 --
1712 Exception When others then
1713 p_last_payroll_dt := null;
1714 --
1715 hr_utility.set_location('Exception:'||l_proc, 15);
1716 raise;
1717
1718 --
1719 End get_assignment_actuals;
1720 --
1721 --------------------------------------------------------------------------
1722 PROCEDURE get_version_from_cmmtmnt_table
1723 (p_assignment_id in number,
1724 p_start_date in date,
1725 p_end_date in date,
1726 p_budget_version_id out nocopy number)
1727 is
1728 --
1729 -- We want to pick up only the budget versions that lie between the passed
1730 -- dates
1731 --
1732 Cursor csr_version is
1733 Select budget_version_id
1734 From pqh_element_commitments
1735 Where assignment_id = p_assignment_id
1736 AND (p_start_date <= commitment_end_date AND
1737 commitment_start_date <= p_end_date);
1738
1739 --
1740 l_proc varchar2(72) := g_package ||'get_version_from_cmmtmnt_table';
1741
1742 --
1743 Begin
1744 --
1745 hr_utility.set_location('Entering :'||l_proc,5);
1746 --
1747 Open csr_version;
1748 --
1749 -- Selecting the first budget version
1750 --
1751 Fetch csr_version into p_budget_version_id;
1752 --
1753 If csr_version%notfound then
1754 --
1755 p_budget_version_id := NULL;
1756 --
1757 End if;
1758 --
1759 Close csr_version;
1760 --
1761 hr_utility.set_location('Leaving :'||l_proc,10);
1762 --
1763 Exception When others then
1764 p_budget_version_id := null;
1765 --
1766
1767 hr_utility.set_location('Exception:'||l_proc, 15);
1768 raise;
1769 --
1770 End get_version_from_cmmtmnt_table;
1771 --
1772 --------------------------------------------------------------------------
1773 --
1774 FUNCTION get_assignment_commitment(p_assignment_id in number,
1775 p_budget_version_id in number default null,
1776 p_element_type_id in number default null,
1777 p_period_start_date in date,
1778 p_period_end_date in date,
1779 p_unit_of_measure_id in number)
1780 RETURN NUMBER
1781 IS
1782 --
1783 l_assign_commitment number := 0;
1784 l_assign_actuals number := 0;
1785 l_last_payroll_dt per_time_periods.end_date%type := NULL;
1786 --
1787 l_budget_id pqh_budgets.budget_id%type := NULL;
1788 l_budget_version_id pqh_budget_versions.budget_version_id%type;
1789 --
1790
1791 l_unit_of_measure per_shared_types.system_type_cd%type;
1792 --
1793 --
1794 l_proc varchar2(72) := g_package||'get_assignment_commitment';
1795 --
1796 Begin
1797 --
1798 hr_utility.set_location('Entering:'||l_proc, 5);
1799 --
1800 --
1801 -- Check if this is a valid assignment
1802 --
1803 if p_assignment_id is not null then
1804 Validate_assignment(p_assignment_id => p_assignment_id);
1805 end if;
1806 --
1807 --
1808 -- Validate if the budget_version_id is a valid one
1809 --
1810 If p_budget_version_id IS NOT NULL then
1811 --
1812 Validate_budget(p_budget_version_id => p_budget_version_id,
1813 p_budget_id => l_budget_id);
1814 --
1815 l_budget_version_id := p_budget_version_id;
1816
1817 --
1818 Else
1819 --
1820 get_version_from_cmmtmnt_table(p_assignment_id => p_assignment_id,
1821 p_start_date => p_period_start_date,
1822 p_end_date => p_period_end_date,
1823 p_budget_version_id => l_budget_version_id);
1824 --
1825 -- If we are unable to get the budget versions for the given dates , we
1826 -- will just return 0 commitment for assignment.
1827 --
1828 If l_budget_version_id IS NULL then
1829 --
1830 Return 0;
1831 --
1832 End if;
1833 --
1834 End if;
1835 --
1836 -- Validate if the unit of measure is valid in per_shared_types
1837 -- Also , check if the unit has been budgeted for in the budget.
1838 --
1839 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
1840 p_unit_of_measure_desc => l_unit_of_measure);
1841 --
1842 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
1843 p_budget_id => l_budget_id);
1844 --
1845 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
1846 p_cmmtmnt_end_dt => p_period_end_date,
1847 p_budget_id => l_budget_id);
1848 --
1849 l_assign_commitment := 0;
1850 --
1851 -- If the UOM is money, obtain commitment from pqh_elements_commitment
1852 -- table . Else get commitment from assignment_budget_values.
1853 --
1854 If l_unit_of_measure = 'MONEY' then
1855 --
1856 -- We need to determine the last payroll date of the assignment before
1857 -- we start calculating the commitment.We must calculate commitment
1858 -- from the next day of last payroll run
1859 --
1860 l_assign_actuals := get_assign_money_actuals
1861 (p_budget_id => l_budget_id, /*2716884*/
1862 p_assignment_id => p_assignment_id,
1863 p_element_type_id => p_element_type_id,
1864 p_actuals_start_date=> p_period_start_date,
1865 p_actuals_end_date => p_period_end_date,
1866 p_last_payroll_dt => l_last_payroll_dt);
1867 --
1868 --
1869 If l_last_payroll_dt IS NULL OR
1870 l_last_payroll_dt <= p_period_start_date then
1871 --
1872 l_assign_commitment := get_assign_money_cmmtmnt
1873 (p_budget_version_id => l_budget_version_id,
1874 p_assignment_id => p_assignment_id,
1875 p_element_type_id => p_element_type_id,
1876 p_period_start_date => p_period_start_date,
1877 p_period_end_date => p_period_end_date);
1878 --
1879 Elsif l_last_payroll_dt > p_period_end_date then
1880 l_assign_commitment := 0;
1881 Else
1882 l_assign_commitment := get_assign_money_cmmtmnt
1883 (p_budget_version_id => l_budget_version_id,
1884 p_assignment_id => p_assignment_id,
1885 p_element_type_id => p_element_type_id,
1886 p_period_start_date => l_last_payroll_dt + 1,
1887 p_period_end_date => p_period_end_date);
1888 --
1889 End if;
1890 --
1891 Else
1892 --
1893 l_assign_commitment := get_assignment_budget_values
1894 (p_assignment_id => p_assignment_id,
1895 p_period_start_dt => p_period_start_date,
1896 p_period_end_dt => p_period_end_date,
1897 p_unit_of_measure => l_unit_of_measure);
1898 End if;
1899 --
1900 hr_utility.set_location('Leaving:'||l_proc, 10);
1901 --
1902 RETURN l_assign_commitment;
1903 --
1904 Exception When others then
1905 --
1906 hr_utility.set_location('Exception:'||l_proc, 15);
1907
1908 raise;
1909 --
1910 End get_assignment_commitment;
1911 --
1912 -----------------------------------------------------------------------
1913 --
1914 -- This function calculates only money actuals for a position
1915 -- It is called from get budget commitment and get budget actuals
1916 --
1917 PROCEDURE get_pos_money_amounts
1918 (
1919 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
1920 p_position_id IN per_positions.position_id%TYPE,
1921 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1922 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1923 p_actual_amount OUT NOCOPY number,
1924 p_commitment_amount OUT NOCOPY number,
1925 p_total_amount OUT NOCOPY number
1926 ) IS
1927 --
1928 --
1929 CURSOR csr_pos_assg is
1930 Select distinct ASSG.assignment_id
1931 From per_all_assignments_f ASSG
1932 Where ASSG.position_id = p_position_id
1933 AND ASSG.effective_end_date >= p_start_date
1934 AND ASSG.effective_start_date <= p_end_date;
1935 --
1936 l_position_id pqh_budget_details.position_id%type;
1937 l_position_name hr_all_positions_f.name%type := NULL;
1938 l_assignment_id per_all_assignments_f.assignment_id%type;
1939 l_last_actuals_date per_time_periods.end_date%type;
1940 --
1941 l_budget_id pqh_budgets.budget_id%type := NULL;
1942 --
1943 l_unit_of_measure per_shared_types.system_type_cd%type;
1944 --
1945 l_assignment_actuals number := 0;
1946 l_assignment_commitment number := 0;
1947 l_assignment_total number := 0;
1948 --
1949 l_position_actuals number := 0;
1950 l_position_commitment number := 0;
1951 l_position_total number := 0;
1952 --
1953
1954 l_proc varchar2(72) := g_package||'get_pos_money_amounts';
1955 --
1956 BEGIN
1957 --
1958 hr_utility.set_location('Entering:'||l_proc, 5);
1959 --
1960 --
1961 /*2716884*/
1962 If p_budget_version_id IS NOT NULL then
1963 --
1964 Validate_budget(p_budget_version_id => p_budget_version_id,
1965 p_budget_id => l_budget_id);
1966 End if;
1967
1968 --
1969 -- Use get_pos_money_total for Total
1970 --
1971 --
1972 l_position_actuals :=
1973 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_money_total9
1974 (
1975 p_budget_version_id =>p_budget_version_id,
1976 p_position_id =>p_position_id,
1977 p_actuals_start_date =>p_start_date,
1978 p_actuals_end_date =>p_end_date
1979 -- p_ex_assignment_id =>p_ex_assignment_id
1980 );
1981 --
1982
1983
1984 for i in csr_pos_assg loop
1985 l_assignment_id := i.assignment_id;
1986 --
1987 l_assignment_total := 0;
1988 -- l_assignment_actuals := 0;
1989 l_assignment_commitment := 0;
1990 l_last_actuals_date := NULL;
1991 --
1992 -- get actuals for the assignment and the last payroll run date
1993
1994 -- for the assignment.
1995 --
1996 --
1997 -- Use get_pos_money_total for Total
1998 --
1999 --
2000 /* l_assignment_actuals := get_assign_money_actuals
2001 ( p_budget_id => l_budget_id, -- 2716884
2002 p_assignment_id => l_assignment_id,
2003 p_element_type_id => NULL,
2004 p_actuals_start_date => p_start_date,
2005 p_actuals_end_date => p_end_date,
2006 p_last_payroll_dt => l_last_actuals_date
2007 ); */
2008 --
2009 l_last_actuals_date := get_last_payroll_dt(l_assignment_id,
2010 p_start_date, p_end_date);
2011
2012 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2013 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2014 --
2015 IF l_last_actuals_date IS NULL OR
2016 l_last_actuals_date <= p_start_date THEN
2017 --
2018 -- payroll has never been run for the assignemnr. So actual is zero
2019 --
2020 l_assignment_actuals := 0;
2021 --
2022 l_assignment_commitment := get_assign_money_cmmtmnt
2023 (p_assignment_id => l_assignment_id ,
2024 p_budget_version_id => p_budget_version_id,
2025 p_element_type_id => NULL,
2026 p_period_start_date => p_start_date,
2027 p_period_end_date => p_end_date
2028 );
2029 --
2030 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2031 --
2032 ELSIF l_last_actuals_date >= p_end_date then
2033 --
2034 -- Actuals is available beyond the required period end date .
2035 -- So commitment is 0.
2036 --
2037 l_assignment_commitment := 0;
2038 --
2039 ELSE
2040 --
2041 -- payroll has been run for the position. calculate commitment
2042 -- from the next day of the last payroll run.
2043 --
2044 l_assignment_commitment := get_assign_money_cmmtmnt
2045 (p_assignment_id => l_assignment_id ,
2046 p_budget_version_id => p_budget_version_id,
2047 p_element_type_id => NULL,
2048 p_period_start_date => l_last_actuals_date + 1,
2049 p_period_end_date => p_end_date
2050 );
2051 --
2052 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2053 --
2054 END IF;
2055 --
2056 -- Total up assignment commitment and actual information.
2057 --
2058 -- l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2059 --
2060 -- Total up position commitment and actuals info
2061 --
2062 l_position_commitment := l_position_commitment + NVL(l_assignment_commitment,0);
2063 --
2064 --l_position_actuals := l_position_actuals + NVL(l_assignment_actuals,0);
2065 --
2066 --l_position_total := l_position_total + l_assignment_total;
2067 --
2068 End Loop;
2069 l_position_total := l_position_actuals + l_position_commitment;
2070 --
2071 hr_utility.set_location('All assignments processed',50);
2072 -- Return the actuals , commitment and total
2073 --
2074 p_total_amount := l_position_total;
2075 --
2076 p_commitment_amount := l_position_commitment;
2077 --
2078 p_actual_amount := l_position_actuals;
2079 --
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 p_actual_amount := null;
2083 p_commitment_amount := null;
2084 p_total_amount := null;
2085 hr_utility.set_location('Exception :'||l_proc,60);
2086 hr_utility.raise_error;
2087 --
2088 End;
2089 --
2090 -----------------------------------------------------------------------
2091 function get_actual_hours(p_assignment_id in number
2092 ,p_asg_start_date in date default sysdate
2093 ,p_start_date in date default sysdate
2094 ,p_end_date in date default sysdate
2095 ,p_effective_date in date default sysdate
2096 ) RETURN NUMBER IS
2097 ---
2098 l_total_weeks number := 0;
2099 l_hours_per_day number := 0;
2100 l_total_hours number := 0;
2101 l_remaining_days number(1) := 0;
2102 l_position_id number;
2103 l_organization_id number;
2104 l_bg_id number;
2105 l_proc varchar2(60) := g_package||'get_actual_hours';
2106
2107 Cursor c_pos_freq(p_position_id number) Is
2108 Select frequency , working_hours
2109 From hr_all_positions_f
2110 Where p_effective_date between effective_start_date and effective_end_date
2111 And position_id = p_position_id;
2112
2113 --frequency, workinig_hours of organization
2114 Cursor c_org_freq(p_organization_id number) Is
2115 SELECT O2.ORG_INFORMATION4 , O2.ORG_INFORMATION3
2116 FROM HR_ORGANIZATION_INFORMATION O2
2117 WHERE O2.ORG_INFORMATION_CONTEXT = 'Work Day Information'
2118 AND O2.organization_id = p_organization_id;
2119
2120 Cursor c_bg_freq(p_bg_id number) Is
2121 SELECT O2.ORG_INFORMATION4 , O2.ORG_INFORMATION3
2122 FROM HR_ORGANIZATION_INFORMATION O2
2123 WHERE O2.ORG_INFORMATION_CONTEXT = 'Work Day Information'
2124 AND O2.organization_id = p_bg_id;
2125
2126 cursor c_assg is
2127 select effective_start_date, effective_end_date,frequency, normal_hours, time_normal_start, time_normal_finish,
2128 position_id,organization_id,business_group_id
2129 from per_all_assignments_f
2130 where p_assignment_id = assignment_id
2131 and effective_start_date = p_asg_start_date;
2132
2133 l_frequency per_all_assignments_f.frequency%Type;
2134 l_normal_hours per_all_assignments_f.normal_hours%Type;
2135 l_assignment_id per_all_assignments_f.assignment_id%Type;
2136 l_bg_normal_day_hours per_all_assignments_f.normal_hours%Type;
2137 l_normal_day_hours per_all_assignments_f.normal_hours%Type;
2138 l_actual_start_date date;
2139 l_actual_end_date date;
2140 l_start_date date;
2141 l_end_date date;
2142 l_assg_start_time varchar2(5);
2143 l_assg_end_time varchar2(5);
2144 l_day_of_date varchar2(1);
2145 l_days_removed number(1) := 0;
2146
2147 Begin
2148 hr_utility.set_location('Entering:'||l_proc, 5);
2149 Open c_assg;
2150 Fetch c_assg Into l_start_date, l_end_date, l_frequency,
2151 l_normal_hours, l_assg_start_time, l_assg_end_time,
2152 l_position_id, l_organization_id, l_bg_id;
2153 if c_assg%notfound then
2154 close c_assg;
2155 return null;
2156 end if;
2157 close c_assg;
2158 hr_utility.set_location('l_assignment_id:'||l_assignment_id, 6);
2159
2160 l_actual_start_date := greatest(l_start_date, p_start_date);
2161 l_actual_end_date := least(l_end_date, p_end_date);
2162
2163 hr_utility.set_location('l_normal_day_hours:'||l_normal_day_hours, 7);
2164 -- Get the total number of weeks in the date range
2165 l_total_weeks := trunc(((l_actual_end_date - l_actual_start_date) + 1)/7);
2166 -- Get the remaining days
2167 l_remaining_days := mod((l_actual_end_date - l_actual_start_date) + 1, 7);
2168 -- Get the total weekend days in the remaining days
2169
2170 For i in 1..l_remaining_days Loop
2171 l_day_of_date := to_char(l_actual_end_date - (i - 1),'D');
2172 -- Sundays and Saturdays are not considered in the remaining days
2173 If l_day_of_date in ('1','7') Then
2174 l_days_removed := nvl(l_days_removed,0) + 1;
2175 End if;
2176 End Loop;
2177
2178 l_remaining_days := (l_remaining_days - l_days_removed);
2179
2180 If l_frequency is null and l_normal_hours is null Then
2181 if l_position_id is not null then
2182 Open c_pos_freq(l_position_id);
2183 Fetch c_pos_freq Into l_frequency, l_normal_hours;
2184 Close c_pos_freq;
2185 end if;
2186 If l_frequency is null and l_normal_hours is null Then
2187 Open c_org_freq(l_organization_id);
2188 Fetch c_org_freq Into l_frequency, l_normal_hours;
2189 Close c_org_freq;
2190 end if;
2191 If l_frequency is null and l_normal_hours is null Then
2192 Open c_bg_freq(l_bg_id);
2193 Fetch c_bg_freq Into l_frequency, l_normal_hours;
2194 Close c_bg_freq;
2195 end if;
2196 end If;
2197 hr_utility.set_location('l_frequency:'||l_frequency ||' l_normal_hours:'||l_normal_hours, 8);
2198 If (l_frequency is not null and l_normal_hours is not null) Then
2199 If l_frequency in ('HO','H') Then
2200 l_hours_per_day := (l_normal_hours * 8); -- taking 8 hrs/day
2201 Elsif l_frequency = 'D' Then
2202 l_hours_per_day := l_normal_hours;
2203 Elsif l_frequency = 'W' Then
2204 l_hours_per_day := (l_normal_hours/5); -- taking 5 days per week
2205 Elsif l_frequency = 'M' Then
2206 l_hours_per_day := (l_normal_hours/160); -- taking 160 hours per month
2207 Else
2208 l_hours_per_day := 0;
2209 End If;
2210 -- Again convert days into weeks to take care of weekends.
2211 l_total_hours := (l_hours_per_day * 5) * l_total_weeks + (l_hours_per_day * l_remaining_days);
2212 Else
2213 l_total_hours := (8 * 5 * l_total_weeks) + (8 * l_remaining_days);
2214 -- taking 8 hours per day as default.
2215 End If;
2216 hr_utility.set_location('l_total_hours:'||l_total_hours, 8);
2217 RETURN (l_total_hours);
2218 Exception
2219 when others then
2220 hr_utility.set_location('Exception :'||l_proc,70);
2221 hr_utility.raise_error;
2222 End;
2223 --- Function to get the actual hours for an entity
2224 ---
2225 FUNCTION get_actual_hours(p_business_grp_id in number
2226 ,p_position_id in number default null
2227 ,p_job_id in number default null
2228 ,p_grade_id in number default null
2229 ,p_organization_id in number default null
2230 ,p_start_date in date default sysdate
2231 ,p_end_date in date default sysdate
2232 ,p_effective_date in date default sysdate
2233 ) RETURN NUMBER IS
2234
2235 l_total_hours number := 0;
2236 l_asg_hours number := 0;
2237 l_proc varchar2(60) := g_package||'get_actual_hours';
2238 --
2239 -- if organization_id is passed, index will be used
2240 --
2241 cursor c_org_assg is
2242 select assignment_id, effective_start_date
2243 from per_all_assignments_f
2244 where organization_id = p_organization_id
2245 and business_group_id = p_business_grp_id
2246 and effective_end_date >= p_start_date
2247 and effective_start_date <= p_end_date ;
2248
2249 cursor c_position_assg is
2250 select assignment_id, effective_start_date
2251 from per_all_assignments_f
2252 where p_position_id = position_id
2253 and effective_end_date >= p_start_date
2254 and effective_start_date <= p_end_date ;
2255
2256 cursor c_job_assg is
2257 select assignment_id, effective_start_date
2258 from per_all_assignments_f
2259 where p_job_id = job_id
2260 and effective_end_date >= p_start_date
2261 and effective_start_date <= p_end_date ;
2262
2263 cursor c_grade_assg is
2264 select assignment_id, effective_start_date
2265 from per_all_assignments_f
2266 where p_grade_id = grade_id
2267 and effective_end_date >= p_start_date
2268 and effective_start_date <= p_end_date ;
2269
2270 Begin
2271 hr_utility.set_location('Entering:'||l_proc, 5);
2272 if p_organization_id is not null then
2273 for i in c_org_assg loop
2274 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2275 p_asg_start_date => i.effective_start_date,
2276 p_start_date => p_start_date,
2277 p_end_date => p_end_date,
2278 p_effective_date => p_effective_date);
2279 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2280 end loop;
2281 elsif p_job_id is not null then
2282 for i in c_job_assg loop
2283 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2284 p_asg_start_date => i.effective_start_date,
2285 p_start_date => p_start_date,
2286 p_end_date => p_end_date,
2287 p_effective_date => p_effective_date);
2288 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2289 end loop;
2290 elsif p_position_id is not null then
2291 for i in c_position_assg loop
2292 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2293 p_asg_start_date => i.effective_start_date,
2294 p_start_date => p_start_date,
2295 p_end_date => p_end_date,
2296 p_effective_date => p_effective_date);
2297 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2298 end loop;
2299 elsif p_grade_id is not null then
2300 for i in c_grade_assg loop
2301 l_asg_hours := get_actual_hours (p_assignment_id => i.assignment_id,
2302 p_asg_start_date => i.effective_start_date,
2303 p_start_date => p_start_date,
2304 p_end_date => p_end_date,
2305 p_effective_date => p_effective_date);
2306 l_total_hours := nvl(l_total_hours,0) + nvl(l_asg_hours,0);
2307 end loop;
2308 end if;
2309 RETURN (l_total_hours);
2310 Exception
2311 when others then
2312 hr_utility.set_location('Exception :'||l_proc,70);
2313 hr_utility.raise_error;
2314 End;
2315 ----------------------------------------------------------------
2316 --
2317 -- This function calculates commitment / actuals / total for a position.
2318 -- It is called from get budget commitment and get budget actuals
2319 --
2320 Function get_pos_actual_and_cmmtmnt
2321 (
2322 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
2323 p_position_id IN per_positions.position_id%TYPE,
2324 p_element_type_id IN number default NULL,
2325 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2326 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2327 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2328 p_value_type IN varchar2,
2329 p_ex_assignment_id IN number default -1,
2330 p_validate IN varchar2 default 'Y'
2331 )
2332 RETURN NUMBER IS
2333 --
2334 --
2335 CURSOR csr_pos_assg is
2336 Select distinct ASSG.assignment_id
2337 From per_all_assignments_f ASSG
2338 Where ASSG.position_id = p_position_id
2339 AND ASSG.effective_end_date >= p_start_date
2340 AND ASSG.effective_start_date <= p_end_date
2341 AND ASSG.assignment_id <> p_ex_assignment_id;
2342 --
2343 l_position_id pqh_budget_details.position_id%type;
2344 l_position_name hr_all_positions_f.name%type := NULL;
2345 l_assignment_id per_all_assignments_f.assignment_id%type;
2346 l_last_actuals_date per_time_periods.end_date%type;
2347 --
2348 l_budget_id pqh_budgets.budget_id%type := NULL;
2349 --
2350 l_unit_of_measure per_shared_types.system_type_cd%type;
2351 --
2352 l_assignment_actuals number := 0;
2353 l_assignment_commitment number := 0;
2354 l_assignment_total number := 0;
2355 --
2356 l_position_actuals number := 0;
2357 l_position_commitment number := 0;
2358 l_position_total number := 0;
2359
2360 -- Following two Variable Declerations done by vevenkat for bug 2628563
2361 l_business_group_id hr_all_organization_units.Business_group_id%TYPE := hr_general.get_business_group_id;
2362 l_effective_date Date := hr_general.effective_date;
2363 --
2364
2365 l_proc varchar2(72) := g_package||'get_pos_actual_and_cmmtmnt';
2366 --
2367 BEGIN
2368 --
2369 hr_utility.set_location('Entering:'||l_proc, 5);
2370 IF ( p_validate = 'Y') THEN
2371 --
2372 --
2373 -- CHECK IF THIS IS A VALID BUDGET .
2374 --
2375 Validate_budget(p_budget_version_id => p_budget_version_id,
2376 p_budget_id => l_budget_id);
2377 --
2378 -- CHECK IF THIS IS A VALID POSITION . ALSO DOES THIS POSITION
2379 -- BELONG IN THE PASSED BUDGET.
2380 --
2381 Validate_position(p_budget_version_id => p_budget_version_id,
2382 p_position_id => p_position_id);
2383 --
2384 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
2385 p_budget_id => l_budget_id);
2386 --
2387 -- Validate if valid dates have been passed.
2388 --
2389 If p_value_type = 'A' OR p_value_type = 'T' then
2390 --
2391 Validate_actuals_dates(p_actuals_start_dt => p_start_date,
2392 p_actuals_end_dt => p_end_date,
2393 p_budget_id => l_budget_id);
2394 --
2395 ElsIf p_value_type = 'C' OR p_value_type = 'T' then
2396 --
2397 hr_utility.set_location('Dates for Commitment Calc: '||p_start_date||' - '||p_end_date, 61);
2398 Validate_commitment_dates(p_cmmtmnt_start_dt => p_start_date,
2399 p_cmmtmnt_end_dt => p_end_date,
2400 p_budget_id => l_budget_id);
2401 --
2402 Else
2403 --
2404 Return 0;
2405 --
2406
2407 End if;
2408 END IF;
2409 --
2410 --
2411 -- Validate unit of measure.
2412 --
2413 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
2414 p_unit_of_measure_desc => l_unit_of_measure);
2415 --
2416 --
2417 If l_unit_of_measure = 'MONEY' then
2418 --
2419 -- calculate actuals and commitment for each assignment.
2420 --
2421 for i in csr_pos_assg Loop
2422 l_assignment_id := i.assignment_id;
2423 --
2424 hr_utility.set_location('Assignments found: '||l_assignment_id, 62);
2425 l_assignment_total := 0;
2426 l_assignment_actuals := 0;
2427 l_assignment_commitment := 0;
2428 l_last_actuals_date := NULL;
2429 --
2430 -- get actuals for the assignment and the last payroll run date
2431
2432 -- for the assignment.
2433 --
2434 If p_value_type = 'A' OR p_value_type = 'T' then
2435 l_assignment_actuals := get_assign_money_actuals
2436 ( p_budget_id => l_budget_id, /*2716884*/
2437 p_assignment_id => l_assignment_id,
2438 p_element_type_id => p_element_type_id,
2439 p_actuals_start_date => p_start_date,
2440 p_actuals_end_date => p_end_date,
2441 p_last_payroll_dt => l_last_actuals_date
2442 );
2443 --
2444 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2445 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2446 --
2447 l_position_actuals := l_position_actuals + NVL(l_assignment_actuals,0);
2448 End If;
2449
2450 If p_value_type in ('C','T') then
2451 -- Get last_actuals_date only if 'C' (in case of T, it is already evaluated
2452 if ( p_value_type = 'C') then
2453 l_last_actuals_date := get_last_payroll_dt (l_assignment_id, p_start_date, p_end_date);
2454 end if;
2455 hr_utility.set_location('Last Actual Date: '||to_char(l_last_actuals_date)||' - '||to_char(p_start_date), 63);
2456 IF (l_last_actuals_date IS NULL OR
2457 l_last_actuals_date <= p_start_date ) THEN
2458 --
2459 -- payroll has never been run for the position. So actual is zero
2460 --
2461 l_assignment_actuals := 0;
2462 --
2463 l_assignment_commitment := get_assign_money_cmmtmnt
2464 (p_assignment_id => l_assignment_id ,
2465 p_budget_version_id => p_budget_version_id,
2466 p_element_type_id => p_element_type_id,
2467 p_period_start_date => p_start_date,
2468 p_period_end_date => p_end_date
2469 );
2470 --
2471 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2472 --
2473 ELSIF l_last_actuals_date >= p_end_date then
2474 --
2475 -- Actuals is available beyond the required period end date .
2476 -- So commitment is 0.
2477 --
2478 l_assignment_commitment := 0;
2479 --
2480 ELSE
2481 hr_utility.set_location('Payroll has run..: '||to_char(l_last_actuals_date+1)||' - '||to_char(p_end_date), 64);
2482 --
2483 -- payroll has been run for the position. calculate commitment
2484 -- from the next day of the last payroll run.
2485 --
2486 l_assignment_commitment := get_assign_money_cmmtmnt
2487 (p_assignment_id => l_assignment_id ,
2488 p_budget_version_id => p_budget_version_id,
2489 p_element_type_id => p_element_type_id,
2490 p_period_start_date => l_last_actuals_date + 1,
2491 p_period_end_date => p_end_date
2492 );
2493 --
2494 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2495 --
2496 END IF;
2497 l_position_commitment := l_position_commitment + NVL(l_assignment_commitment,0);
2498 End If;
2499
2500 If p_value_type = 'T' then
2501 --
2502 l_position_total := l_position_total + NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2503 --
2504 End If;
2505 End Loop;
2506 --
2507 hr_utility.set_location('All assignments processed',50);
2508
2509 --
2510 --
2511 -- Return the actuals or commitment or total
2512 -- depending on what value type was passed.
2513 --
2514 If p_value_type = 'T' then
2515 --
2516 RETURN l_position_total;
2517 --
2518 Elsif p_value_type = 'C' then
2519 --
2520 hr_utility.set_location('Returning Commitment amount: '||l_position_commitment,65);
2521 RETURN l_position_commitment;
2522 --
2523 Elsif p_value_type = 'A' then
2524 --
2525 RETURN l_position_actuals;
2526 --
2527 Else
2528 --
2529 RETURN 0;
2530 --
2531 End if;
2532
2533 --
2534 -- Added the If conditions for HOURS for bug 2628563
2535 Elsif l_unit_of_measure = 'HOURS' then
2536
2537 l_position_total := get_actual_hours( p_business_grp_id => l_business_group_id
2538 ,p_position_id => p_position_id
2539 ,p_job_id => to_number(NULL)
2540 ,p_grade_id => to_number(NULL)
2541 ,p_organization_id => to_number(NULL)
2542 ,p_start_date => p_start_date
2543 ,p_end_date => p_end_date
2544 ,p_effective_date => l_effective_date);
2545
2546 Else
2547 --
2548 l_position_total := get_pos_budget_values
2549 (p_position_id => p_position_id,
2550 p_period_start_dt => p_start_date,
2551 p_period_end_dt => p_end_date,
2552 p_unit_of_measure => l_unit_of_measure);
2553 --
2554 End if;
2555 --
2556 RETURN l_position_total;
2557 --
2558
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 hr_utility.set_location('Exception :'||l_proc,60);
2562 hr_utility.set_location(sqlerrm,99);
2563 hr_utility.raise_error;
2564
2565 End;
2566
2567 --
2568 --
2569 -- This function calculates commitment / actuals / total for an entity.
2570 -- It is called from get budget commitment and get budget actuals
2571 --
2572 Function get_ent_actual_and_cmmtmnt
2573 (
2574 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
2575 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
2576 p_entity_id IN pqh_budget_details.position_id%TYPE,
2577 p_element_type_id IN number default NULL,
2578 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2579 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2580 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2581 p_value_type IN varchar2
2582 )
2583 RETURN NUMBER IS
2584 --
2585 --
2586 CURSOR csr_pos_assg (p_business_group_id number) is
2587 Select distinct ASSG.assignment_id
2588 From per_all_assignments_f ASSG
2589 Where ASSG.position_id = p_entity_id
2590 and business_group_id = p_business_group_id
2591 AND ASSG.effective_end_date >= p_start_date
2592 AND ASSG.effective_start_date <= p_end_date;
2593 --
2594 CURSOR csr_job_assg(p_business_group_id number) is
2595 Select distinct ASSG.assignment_id
2596 From per_all_assignments_f ASSG
2597 Where ASSG.job_id = p_entity_id
2598 and business_group_id = p_business_group_id
2599 AND ASSG.effective_end_date >= p_start_date
2600 AND ASSG.effective_start_date <= p_end_date;
2601 --
2602 CURSOR csr_grade_assg(p_business_group_id number) is
2603 Select distinct ASSG.assignment_id
2604 From per_all_assignments_f ASSG
2605 Where ASSG.grade_id = p_entity_id
2606 and business_group_id = p_business_group_id
2607 AND ASSG.effective_end_date >= p_start_date
2608 AND ASSG.effective_start_date <= p_end_date ;
2609 --
2610 CURSOR csr_org_assg(p_business_group_id number) is
2611 Select distinct ASSG.assignment_id
2612 From per_all_assignments_f ASSG
2613 Where ASSG.organization_id = p_entity_id
2614 and business_group_id = p_business_group_id
2615 AND ASSG.effective_end_date >= p_start_date
2616 AND ASSG.effective_start_date <= p_end_date;
2617 --
2618
2619 l_assignment_id per_all_assignments_f.assignment_id%type;
2620 l_business_group_id per_all_assignments_f.business_group_id%type;
2621 l_last_actuals_date per_time_periods.end_date%type;
2622 --
2623 l_budget_id pqh_budgets.budget_id%type := NULL;
2624 --
2625 l_unit_of_measure per_shared_types.system_type_cd%type;
2626 --
2627 l_assignment_actuals number := 0;
2628 l_assignment_commitment number := 0;
2629 l_assignment_total number := 0;
2630 --
2631 l_entity_actuals number := 0;
2632 l_entity_commitment number := 0;
2633 l_entity_total number := 0;
2634 l_effective_date date;
2635
2636 CURSOR csr_bg_id is
2637 Select business_group_id
2638 From pqh_budgets
2639 Where budget_id = l_budget_id;
2640
2641 --
2642 l_proc varchar2(72) := g_package||'get_ent_actual_and_cmmtmnt';
2643 --
2644 BEGIN
2645 --
2646 hr_utility.set_location('Entering:'||l_proc, 5);
2647 --
2648 --
2649 -- CHECK IF THIS IS A VALID BUDGET .
2650 --
2651 Validate_budget(p_budget_version_id => p_budget_version_id,
2652 p_budget_id => l_budget_id);
2653
2654 If p_budgeted_entity_cd = 'POSITION' Then
2655 --
2656 -- CHECK IF THIS IS A VALID POSITION AND ALSO DOES THIS POSITION
2657 -- BELONG IN THE PASSED BUDGET.
2658 --
2659 Validate_position(p_budget_version_id => p_budget_version_id,
2660 p_position_id => p_entity_id);
2661
2662
2663 Elsif p_budgeted_entity_cd = 'JOB' Then
2664 --
2665 -- CHECK IF THIS IS A VALID JOB AND ALSO DOES THIS JOB
2666 -- BELONG IN THE PASSED BUDGET.
2667 --
2668 Validate_job(p_budget_version_id => p_budget_version_id,
2669 p_job_id => p_entity_id);
2670
2671 Elsif p_budgeted_entity_cd = 'GRADE' Then
2672 --
2673 -- CHECK IF THIS IS A VALID GRADE AND ALSO DOES THIS GRADE
2674 -- BELONG IN THE PASSED BUDGET.
2675 --
2676 Validate_grade(p_budget_version_id => p_budget_version_id,
2677 p_grade_id => p_entity_id);
2678
2679 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
2680 --
2681 -- CHECK IF THIS IS A VALID ORGANIZATION AND ALSO DOES THIS ORGANIZATION
2682 -- BELONG IN THE PASSED BUDGET.
2683 --
2684 Validate_organization(p_budget_version_id => p_budget_version_id,
2685 p_organization_id => p_entity_id);
2686
2687 End if;
2688 --
2689 --
2690 -- Validate unit of measure.
2691 --
2692 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
2693
2694 p_unit_of_measure_desc => l_unit_of_measure);
2695 --
2696 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
2697 p_budget_id => l_budget_id);
2698 --
2699 -- Validate if valid dates have been passed.
2700 --
2701 If p_value_type = 'A' OR p_value_type = 'T' then
2702 --
2703 Validate_actuals_dates(p_actuals_start_dt => p_start_date,
2704 p_actuals_end_dt => p_end_date,
2705 p_budget_id => l_budget_id);
2706 --
2707 ElsIf p_value_type = 'C' OR p_value_type = 'T' then
2708 --
2709 Validate_commitment_dates(p_cmmtmnt_start_dt => p_start_date,
2710 p_cmmtmnt_end_dt => p_end_date,
2711 p_budget_id => l_budget_id);
2712 --
2713 Else
2714 --
2715 Return 0;
2716 --
2717 End if;
2718 l_effective_date := hr_general.effective_date;
2719 l_business_group_id := hr_general.get_business_group_id;
2720 If l_business_group_id is null Then
2721 Open csr_bg_id;
2722 Fetch csr_bg_id into l_business_group_id;
2723 Close csr_bg_id;
2724 End if;
2725 --
2726 --
2727 If l_unit_of_measure = 'MONEY' then
2728 --
2729 -- calculate actuals and commitment for each assignment.
2730 --
2731 If p_budgeted_entity_cd = 'POSITION' Then
2732 for i in csr_pos_assg(l_business_group_id) loop
2733 l_assignment_id := i.assignment_id;
2734 --
2735 l_assignment_total := 0;
2736 l_assignment_actuals := 0;
2737 l_assignment_commitment := 0;
2738 l_last_actuals_date := NULL;
2739 --
2740 -- get actuals for the assignment and the last payroll run date
2741
2742 -- for the assignment.
2743 --
2744 l_assignment_actuals := get_assign_money_actuals
2745 ( p_budget_id => l_budget_id, /*2716884*/
2746 p_assignment_id => l_assignment_id,
2747 p_element_type_id => p_element_type_id,
2748 p_actuals_start_date => p_start_date,
2749 p_actuals_end_date => p_end_date,
2750 p_last_payroll_dt => l_last_actuals_date
2751 );
2752 --
2753 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2754 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2755 --
2756 IF l_last_actuals_date IS NULL OR
2757 l_last_actuals_date <= p_start_date THEN
2758 --
2759 -- payroll has never been run for the position. So actual is zero
2760 --
2761 l_assignment_actuals := 0;
2762 --
2763 l_assignment_commitment := get_assign_money_cmmtmnt
2764 (p_assignment_id => l_assignment_id ,
2765 p_budget_version_id => p_budget_version_id,
2766 p_element_type_id => p_element_type_id,
2767 p_period_start_date => p_start_date,
2768 p_period_end_date => p_end_date
2769 );
2770 --
2771 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2772 --
2773 ELSIF l_last_actuals_date >= p_end_date then
2774 --
2775 -- Actuals is available beyond the required period end date .
2776 -- So commitment is 0.
2777 --
2778 l_assignment_commitment := 0;
2779 --
2780 ELSE
2781 --
2782 -- payroll has been run for the position. calculate commitment
2783 -- from the next day of the last payroll run.
2784 --
2785 l_assignment_commitment := get_assign_money_cmmtmnt
2786 (p_assignment_id => l_assignment_id ,
2787 p_budget_version_id => p_budget_version_id,
2788 p_element_type_id => p_element_type_id,
2789 p_period_start_date => l_last_actuals_date + 1,
2790 p_period_end_date => p_end_date
2791 );
2792 --
2793 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2794 --
2795 END IF;
2796 --
2797 -- Total up assignment commitment and actual information.
2798 --
2799 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2800 --
2801 -- Total up position commitment and actuals info
2802 --
2803 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2804 --
2805 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2806 --
2807 l_entity_total := l_entity_total + l_assignment_total;
2808 --
2809 End Loop;
2810 --
2811 hr_utility.set_location('All assignments processed',50);
2812 Elsif p_budgeted_entity_cd = 'JOB' Then
2813 for i in csr_job_assg(l_business_group_id) loop
2814 l_assignment_id := i.assignment_id;
2815 --
2816 l_assignment_total := 0;
2817 l_assignment_actuals := 0;
2818 l_assignment_commitment := 0;
2819 l_last_actuals_date := NULL;
2820 --
2821 -- get actuals for the assignment and the last payroll run date
2822
2823 -- for the assignment.
2824 --
2825 l_assignment_actuals := get_assign_money_actuals
2826 ( p_budget_id => l_budget_id, /*2716884*/
2827 p_assignment_id => l_assignment_id,
2828 p_element_type_id => p_element_type_id,
2829 p_actuals_start_date => p_start_date,
2830 p_actuals_end_date => p_end_date,
2831 p_last_payroll_dt => l_last_actuals_date
2832 );
2833 --
2834 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2835 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2836 --
2837 IF l_last_actuals_date IS NULL OR
2838 l_last_actuals_date <= p_start_date THEN
2839 --
2840 -- payroll has never been run for the job. So actual is zero
2841 --
2842 l_assignment_actuals := 0;
2843 --
2844 l_assignment_commitment := get_assign_money_cmmtmnt
2845 (p_assignment_id => l_assignment_id ,
2846 p_budget_version_id => p_budget_version_id,
2847 p_element_type_id => p_element_type_id,
2848 p_period_start_date => p_start_date,
2849 p_period_end_date => p_end_date
2850 );
2851 --
2852 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2853 --
2854 ELSIF l_last_actuals_date >= p_end_date then
2855 --
2856 -- Actuals is available beyond the required period end date .
2857 -- So commitment is 0.
2858 --
2859 l_assignment_commitment := 0;
2860 --
2861 ELSE
2862 --
2863 -- payroll has been run for the job. calculate commitment
2864 -- from the next day of the last payroll run.
2865 --
2866 l_assignment_commitment := get_assign_money_cmmtmnt
2867 (p_assignment_id => l_assignment_id ,
2868 p_budget_version_id => p_budget_version_id,
2869 p_element_type_id => p_element_type_id,
2870 p_period_start_date => l_last_actuals_date + 1,
2871 p_period_end_date => p_end_date
2872 );
2873 --
2874 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2875 --
2876 END IF;
2877 --
2878 -- Total up assignment commitment and actual information.
2879 --
2880 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2881 --
2882 -- Total up job commitment and actuals info
2883 --
2884 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2885 --
2886 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2887 --
2888 l_entity_total := l_entity_total + l_assignment_total;
2889 --
2890 End Loop;
2891 --
2892 hr_utility.set_location('All assignments processed',50);
2893 Elsif p_budgeted_entity_cd = 'GRADE' Then
2894 for i in csr_grade_assg(l_business_group_id) loop
2895 l_assignment_id := i.assignment_id;
2896 --
2897 l_assignment_total := 0;
2898 l_assignment_actuals := 0;
2899 l_assignment_commitment := 0;
2900 l_last_actuals_date := NULL;
2901 --
2902 -- get actuals for the assignment and the last payroll run date
2903
2904 -- for the assignment.
2905 --
2906 l_assignment_actuals := get_assign_money_actuals
2907 ( p_budget_id => l_budget_id, /*2716884*/
2908 p_assignment_id => l_assignment_id,
2909 p_element_type_id => p_element_type_id,
2910 p_actuals_start_date => p_start_date,
2911 p_actuals_end_date => p_end_date,
2912 p_last_payroll_dt => l_last_actuals_date
2913 );
2914 --
2915 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2916 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2917 --
2918 IF l_last_actuals_date IS NULL OR
2919 l_last_actuals_date <= p_start_date THEN
2920 --
2921 -- payroll has never been run for the grade. So actual is zero
2922 --
2923 l_assignment_actuals := 0;
2924 --
2925 l_assignment_commitment := get_assign_money_cmmtmnt
2926 (p_assignment_id => l_assignment_id ,
2927 p_budget_version_id => p_budget_version_id,
2928 p_element_type_id => p_element_type_id,
2929 p_period_start_date => p_start_date,
2930 p_period_end_date => p_end_date
2931 );
2932 --
2933 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
2934 --
2935 ELSIF l_last_actuals_date >= p_end_date then
2936 --
2937 -- Actuals is available beyond the required period end date .
2938 -- So commitment is 0.
2939 --
2940 l_assignment_commitment := 0;
2941 --
2942 ELSE
2943 --
2944 -- payroll has been run for the grade. calculate commitment
2945 -- from the next day of the last payroll run.
2946 --
2947 l_assignment_commitment := get_assign_money_cmmtmnt
2948 (p_assignment_id => l_assignment_id ,
2949 p_budget_version_id => p_budget_version_id,
2950 p_element_type_id => p_element_type_id,
2951 p_period_start_date => l_last_actuals_date + 1,
2952 p_period_end_date => p_end_date
2953 );
2954 --
2955 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
2956 --
2957 END IF;
2958 --
2959 -- Total up assignment commitment and actual information.
2960 --
2961 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
2962 --
2963 -- Total up grade commitment and actuals info
2964 --
2965 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
2966 --
2967 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
2968 --
2969 l_entity_total := l_entity_total + l_assignment_total;
2970 --
2971 End Loop;
2972 --
2973 hr_utility.set_location('All assignments processed',50);
2974 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
2975 for i in csr_org_assg(l_business_group_id) loop
2976 l_assignment_id := i.assignment_id;
2977
2978 --
2979 l_assignment_total := 0;
2980 l_assignment_actuals := 0;
2981 l_assignment_commitment := 0;
2982 l_last_actuals_date := NULL;
2983 --
2984 -- get actuals for the assignment and the last payroll run date
2985
2986 -- for the assignment.
2987 --
2988 l_assignment_actuals := get_assign_money_actuals
2989 ( p_budget_id => l_budget_id, /*2716884*/
2990 p_assignment_id => l_assignment_id,
2991 p_element_type_id => p_element_type_id,
2992 p_actuals_start_date => p_start_date,
2993 p_actuals_end_date => p_end_date,
2994 p_last_payroll_dt => l_last_actuals_date
2995 );
2996 --
2997 hr_utility.set_location('Assignment Actual:'||l_assignment_actuals,10);
2998 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
2999 --
3000 IF l_last_actuals_date IS NULL OR
3001 l_last_actuals_date <= p_start_date THEN
3002 --
3003 -- payroll has never been run for the organization. So actual is zero
3004 --
3005 l_assignment_actuals := 0;
3006 --
3007 l_assignment_commitment := get_assign_money_cmmtmnt
3008 (p_assignment_id => l_assignment_id ,
3009 p_budget_version_id => p_budget_version_id,
3010 p_element_type_id => p_element_type_id,
3011 p_period_start_date => p_start_date,
3012 p_period_end_date => p_end_date
3013 );
3014 --
3015 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 30);
3016 --
3017 ELSIF l_last_actuals_date >= p_end_date then
3018 --
3019 -- Actuals is available beyond the required period end date .
3020 -- So commitment is 0.
3021 --
3022 l_assignment_commitment := 0;
3023 --
3024 ELSE
3025 --
3026 -- payroll has been run for the organization. calculate commitment
3027 -- from the next day of the last payroll run.
3028 --
3029 l_assignment_commitment := get_assign_money_cmmtmnt
3030 (p_assignment_id => l_assignment_id ,
3031 p_budget_version_id => p_budget_version_id,
3032 p_element_type_id => p_element_type_id,
3033 p_period_start_date => l_last_actuals_date + 1,
3034 p_period_end_date => p_end_date
3035 );
3036 --
3037 hr_utility.set_location('Assignment Commitment : '||l_assignment_commitment, 40);
3038 --
3039 END IF;
3040 --
3041 -- Total up assignment commitment and actual information.
3042 --
3043 l_assignment_total := NVL(l_assignment_actuals,0) + NVL(l_assignment_commitment,0);
3044 --
3045 -- Total up organization commitment and actuals info
3046 --
3047 l_entity_commitment := l_entity_commitment + NVL(l_assignment_commitment,0);
3048 --
3049 l_entity_actuals := l_entity_actuals + NVL(l_assignment_actuals,0);
3050 --
3051 l_entity_total := l_entity_total + l_assignment_total;
3052 --
3053 End Loop;
3054 --
3055 hr_utility.set_location('All assignments processed',50);
3056 End if;
3057 --
3058 -- Return the actuals or commitment or total
3059 -- depending on what value type was passed.
3060 --
3061 If p_value_type = 'T' then
3062 --
3063 RETURN l_entity_total;
3064 --
3065 Elsif p_value_type = 'C' then
3066 --
3067 RETURN l_entity_commitment;
3068 --
3069 Elsif p_value_type = 'A' then
3070 --
3071 RETURN l_entity_actuals;
3072 --
3073 Else
3074 --
3075 RETURN 0;
3076 --
3077 End if;
3078 --
3079 Elsif l_unit_of_measure = 'HOURS' then
3080 If p_budgeted_entity_cd = 'POSITION' Then
3081 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3082 ,p_position_id => p_entity_id
3083 ,p_job_id => to_number(NULL)
3084 ,p_grade_id => to_number(NULL)
3085 ,p_organization_id => to_number(NULL)
3086 ,p_start_date => p_start_date
3087 ,p_end_date => p_end_date
3088 ,p_effective_date => l_effective_date);
3089 Elsif p_budgeted_entity_cd = 'JOB' Then
3090 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3091 ,p_job_id => p_entity_id
3092 ,p_grade_id => to_number(NULL)
3093 ,p_organization_id => to_number(NULL)
3094 ,p_position_id => to_number(NULL)
3095 ,p_start_date => p_start_date
3096 ,p_end_date => p_end_date
3097 ,p_effective_date => l_effective_date);
3098 Elsif p_budgeted_entity_cd = 'GRADE' Then
3099 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3100 ,p_grade_id => p_entity_id
3101 ,p_position_id => to_number(NULL)
3102 ,p_organization_id => to_number(NULL)
3103 ,p_job_id => to_number(NULL)
3104 ,p_start_date => p_start_date
3105 ,p_end_date => p_end_date
3106 ,p_effective_date => l_effective_date);
3107 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3108 l_entity_total := get_actual_hours( p_business_grp_id => l_business_group_id
3109 ,p_organization_id => p_entity_id
3110 ,p_grade_id => to_number(NULL)
3111 ,p_position_id => to_number(NULL)
3112 ,p_job_id => to_number(NULL)
3113 ,p_start_date => p_start_date
3114 ,p_end_date => p_end_date
3115 ,p_effective_date => l_effective_date);
3116 End if;
3117 Else
3118 If p_budgeted_entity_cd = 'POSITION' Then
3119 l_entity_total := hr_discoverer.get_actual_budget_values
3120 (p_bus_group_id => l_business_group_id,
3121 p_position_id => p_entity_id,
3122 p_job_id => NULL,
3123 p_grade_id => NULL,
3124 p_organization_id => NULL,
3125 p_start_date => p_start_date,
3126 p_end_date => p_end_date,
3127 p_unit => l_unit_of_measure,
3128 p_actual_val => NULL);
3129 Elsif p_budgeted_entity_cd = 'JOB' Then
3130 l_entity_total := hr_discoverer.get_actual_budget_values
3131 (p_bus_group_id => l_business_group_id,
3132 p_job_id => p_entity_id,
3133 p_grade_id => NULL,
3134 p_organization_id => NULL,
3135 p_position_id => NULL,
3136 p_start_date => p_start_date,
3137 p_end_date => p_end_date,
3138 p_unit => l_unit_of_measure,
3139 p_actual_val => NULL);
3140 Elsif p_budgeted_entity_cd = 'GRADE' Then
3141 l_entity_total := hr_discoverer.get_actual_budget_values
3142 (p_bus_group_id => l_business_group_id,
3143 p_grade_id => p_entity_id,
3144 p_position_id => NULL,
3145 p_organization_id => NULL,
3146 p_job_id => NULL,
3147 p_start_date => p_start_date,
3148 p_end_date => p_end_date,
3149 p_unit => l_unit_of_measure,
3150 p_actual_val => NULL);
3151 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3152 l_entity_total := hr_discoverer.get_actual_budget_values
3153 (p_bus_group_id => l_business_group_id,
3154 p_organization_id => p_entity_id,
3155 p_grade_id => NULL,
3156 p_position_id => NULL,
3157 p_job_id => NULL,
3158 p_start_date => p_start_date,
3159 p_end_date => p_end_date,
3160 p_unit => l_unit_of_measure,
3161 p_actual_val => NULL);
3162 End if;
3163 --
3164 --
3165 End if;
3166 --
3167 RETURN l_entity_total;
3168 --
3169 EXCEPTION
3170 WHEN OTHERS THEN
3171 hr_utility.set_location('Exception :'||l_proc,60);
3172 hr_utility.raise_error;
3173 End;
3174 --
3175 ------------------------------------------------------------------------
3176 -- The foll two functions return actuals and commitment for a budget
3177 -- version respectively.
3178 ------------------------------------------------------------------------
3179 --
3180 -- This function returns the actuals of a budget version.
3181 --
3182 FUNCTION get_budget_actuals(p_budget_version_id in number,
3183 p_period_start_date in date,
3184 p_period_end_date in date,
3185 p_unit_of_measure_id in number)
3186 RETURN NUMBER IS
3187 --
3188 l_position_actuals number;
3189 l_budget_actuals number;
3190 l_position_id pqh_budget_details.position_id%type;
3191 --
3192 l_budget_id pqh_budgets.budget_id%type := NULL;
3193 l_unit_of_measure per_shared_types.system_type_cd%type;
3194 --
3195 Cursor csr_positions_in_bdgt is
3196 Select Position_id
3197 From pqh_budget_details bdt,pqh_budget_versions bvr
3198 Where bvr.budget_version_id = p_budget_version_id
3199 AND bvr.budget_version_id = bdt.budget_version_id
3200 AND bdt.position_id IS NOT NULL;
3201 --
3202 l_proc varchar2(72) := g_package||'get_budget_actuals';
3203 --
3204 Begin
3205 --
3206 hr_utility.set_location('Entering:'||l_proc, 5);
3207 --
3208 -- Validate if the budget version is valid .
3209 --
3210 Validate_budget(p_budget_version_id => p_budget_version_id,
3211 p_budget_id => l_budget_id);
3212 --
3213 -- Check if the input unit of measure is valid in per_shared_types.
3214 -- Also , check if the uom has been budgeted for in the budget.
3215 --
3216 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3217 p_unit_of_measure_desc => l_unit_of_measure);
3218
3219
3220 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3221 p_budget_id => l_budget_id);
3222 --
3223 Validate_actuals_dates(p_actuals_start_dt => p_period_start_date,
3224 p_actuals_end_dt => p_period_end_date,
3225 p_budget_id => l_budget_id);
3226 --
3227 l_budget_actuals := 0;
3228 --
3229 -- Break budget version into its comprising positions .Calculate the
3230 -- actuals for each position and sum it up to obtain budget actuals.
3231 --
3232 for i in csr_positions_in_bdgt loop
3233 l_position_actuals := get_pos_actual_and_cmmtmnt
3234 (p_budget_version_id => p_budget_version_id,
3235 p_position_id => i.position_id,
3236 p_start_date => p_period_start_date,
3237 p_end_date => p_period_end_date,
3238 p_unit_of_measure_id => p_unit_of_measure_id,
3239 p_value_type => 'A');
3240 l_budget_actuals := nvl(l_budget_actuals,0) + nvl(l_position_actuals,0);
3241 End Loop;
3242 --
3243 hr_utility.set_location('Leaving:'||l_proc, 10);
3244 --
3245 RETURN l_budget_actuals;
3246 --
3247 Exception When others then
3248 hr_utility.set_location('Exception:'||l_proc, 15);
3249 raise ;
3250 --
3251 End;
3252 --
3253 --------------------------------------------------------------------
3254 --
3255 -- This function returns the commitment of a budget version.
3256 --
3257 FUNCTION get_budget_commitment(p_budget_version_id in number,
3258 p_period_start_date in date,
3259 p_period_end_date in date,
3260 p_unit_of_measure_id in number)
3261 RETURN NUMBER
3262 IS
3263 --
3264 l_position_commitment number;
3265 l_budget_commitment number;
3266 l_position_id pqh_budget_details.position_id%type;
3267 --
3268 l_budget_id pqh_budgets.budget_id%type := NULL;
3269 l_unit_of_measure per_shared_types.system_type_cd%type;
3270 --
3271
3272 Cursor csr_positions_in_bdgt is
3273 Select Position_id
3274 From pqh_budget_details bdt,pqh_budget_versions bvr
3275 Where bvr.budget_version_id = p_budget_version_id
3276 AND bvr.budget_version_id = bdt.budget_version_id
3277 AND bdt.position_id IS NOT NULL;
3278 --
3279 l_proc varchar2(72) := g_package||'get_budget_commitment';
3280 --
3281 Begin
3282 --
3283 hr_utility.set_location('Entering:'||l_proc, 5);
3284 --
3285 -- Validate if the budget version is valid .
3286 --
3287 Validate_budget(p_budget_version_id => p_budget_version_id,
3288 p_budget_id => l_budget_id);
3289 --
3290 -- Check if the input unit of measure is valid in per_shared_types.
3291 -- Also , check if the uom has been budgeted for in the budget.
3292 --
3293 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3294 p_unit_of_measure_desc => l_unit_of_measure);
3295
3296
3297 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3298 p_budget_id => l_budget_id);
3299 --
3300 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
3301 p_cmmtmnt_end_dt => p_period_end_date,
3302 p_budget_id => l_budget_id);
3303 --
3304 l_budget_commitment := 0;
3305 --
3306 -- Break budget version into its comprising positions .Calculate the
3307 -- commitment for each position and sum it up to obtain budget commitment.
3308 --
3309 for i in csr_positions_in_bdgt loop
3310 l_position_commitment := get_pos_actual_and_cmmtmnt
3311 (p_budget_version_id => p_budget_version_id,
3312 p_position_id => i.position_id,
3313 p_start_date => p_period_start_date,
3314 p_end_date => p_period_end_date,
3315 p_unit_of_measure_id => p_unit_of_measure_id,
3316 p_value_type => 'C');
3317 --
3318 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_position_commitment,0);
3319 --
3320 End Loop;
3321 hr_utility.set_location('Leaving:'||l_proc, 10);
3322 --
3323 RETURN l_budget_commitment;
3324 --
3325 Exception When others then
3326 hr_utility.set_location('Exception:'||l_proc, 15);
3327 raise;
3328 End;
3329
3330 -----------------------------------------------------------------------------
3331
3332 ------------------------------------------------------------------------
3333 -- The foll two overloaded functions return actuals and commitment for
3334 -- a budget version respectively and for all entity types.
3335 ------------------------------------------------------------------------
3336 --
3337 -- This function returns the actuals of a budget version.
3338 --
3339 FUNCTION get_budget_actuals(p_budget_version_id in number,
3340 p_budgeted_entity_cd in varchar2,
3341 p_period_start_date in date,
3342 p_period_end_date in date,
3343 p_unit_of_measure_id in number)
3344 RETURN NUMBER
3345 IS
3346 --
3347 l_entity_actuals number;
3348 l_budget_actuals number;
3349 l_position_id pqh_budget_details.position_id%type;
3350 l_job_id pqh_budget_details.position_id%type;
3351 l_grade_id pqh_budget_details.position_id%type;
3352 l_organization_id pqh_budget_details.position_id%type;
3353 --
3354 l_budget_id pqh_budgets.budget_id%type := NULL;
3355 l_unit_of_measure per_shared_types.system_type_cd%type;
3356 --
3357
3358 Cursor csr_positions_in_bdgt is
3359 Select Position_id
3360 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3361 Where bvr.budget_id = bgt.budget_id
3362 AND bvr.budget_version_id = p_budget_version_id
3363 AND bvr.budget_version_id = bdt.budget_version_id
3364 AND bgt.budgeted_entity_cd = 'POSITION';
3365 --
3366 Cursor csr_jobs_in_bdgt is
3367 Select job_id
3368 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3369 Where bvr.budget_id = bgt.budget_id
3370 AND bvr.budget_version_id = p_budget_version_id
3371 AND bvr.budget_version_id = bdt.budget_version_id
3372 AND bgt.budgeted_entity_cd = 'JOB';
3373 --
3374 Cursor csr_grades_in_bdgt is
3375 Select grade_id
3376 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3377 Where bvr.budget_id = bgt.budget_id
3378 AND bvr.budget_version_id = p_budget_version_id
3379 AND bvr.budget_version_id = bdt.budget_version_id
3380 AND bgt.budgeted_entity_cd = 'GRADE';
3381 --
3382 Cursor csr_orgs_in_bdgt is
3383 Select organization_id
3384 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3385 Where bvr.budget_id = bgt.budget_id
3386 AND bvr.budget_version_id = p_budget_version_id
3387 AND bvr.budget_version_id = bdt.budget_version_id
3388 AND bgt.budgeted_entity_cd = 'ORGANIZATION';
3389
3390 --
3391 l_proc varchar2(72) := g_package||'get_budget_actuals';
3392 --
3393 Begin
3394 --
3395 hr_utility.set_location('Entering:'||l_proc, 5);
3396 --
3397 -- Validate if the budget version is valid .
3398 --
3399 Validate_budget(p_budget_version_id => p_budget_version_id,
3400 p_budget_id => l_budget_id);
3401 --
3402 -- Check if the input unit of measure is valid in per_shared_types.
3403 -- Also , check if the uom has been budgeted for in the budget.
3404 --
3405 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3406 p_unit_of_measure_desc => l_unit_of_measure);
3407
3408
3409 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3410 p_budget_id => l_budget_id);
3411 --
3412 Validate_actuals_dates(p_actuals_start_dt => p_period_start_date,
3413 p_actuals_end_dt => p_period_end_date,
3414 p_budget_id => l_budget_id);
3415 --
3416 l_budget_actuals := 0;
3417 --
3418 -- Break budget version into its comprising positions .Calculate the
3419 -- actuals for each position and sum it up to obtain budget actuals.
3420 If p_budgeted_entity_cd = 'POSITION' Then
3421 --
3422 Open csr_positions_in_bdgt;
3423 --
3424 Loop
3425 --
3426 Fetch csr_positions_in_bdgt into l_position_id;
3427 --
3428 If csr_positions_in_bdgt%notfound then
3429 Exit;
3430 End if;
3431 --
3432
3433 l_entity_actuals := 0;
3434
3435 --
3436 l_entity_actuals := get_ent_actual_and_cmmtmnt
3437 (p_budget_version_id => p_budget_version_id,
3438 p_budgeted_entity_cd => p_budgeted_entity_cd,
3439 p_entity_id => l_position_id,
3440 p_start_date => p_period_start_date,
3441 p_end_date => p_period_end_date,
3442 p_unit_of_measure_id => p_unit_of_measure_id,
3443 p_value_type => 'A');
3444 --
3445 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3446 --
3447 End Loop;
3448 --
3449 Close csr_positions_in_bdgt;
3450
3451 Elsif p_budgeted_entity_cd = 'JOB' Then
3452 --
3453 Open csr_jobs_in_bdgt;
3454 --
3455 Loop
3456 --
3457 Fetch csr_jobs_in_bdgt into l_job_id;
3458 --
3459 If csr_jobs_in_bdgt%notfound then
3460 Exit;
3461 End if;
3462 --
3463
3464 l_entity_actuals := 0;
3465
3466 --
3467 l_entity_actuals := get_ent_actual_and_cmmtmnt
3468 (p_budget_version_id => p_budget_version_id,
3469 p_budgeted_entity_cd => p_budgeted_entity_cd,
3470 p_entity_id => l_job_id,
3471 p_start_date => p_period_start_date,
3472 p_end_date => p_period_end_date,
3473 p_unit_of_measure_id => p_unit_of_measure_id,
3474 p_value_type => 'A');
3475 --
3476 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3477 --
3478 End Loop;
3479 --
3480 Close csr_jobs_in_bdgt;
3481 --
3482 Elsif p_budgeted_entity_cd = 'GRADE' Then
3483 --
3484 Open csr_grades_in_bdgt;
3485 --
3486 Loop
3487 --
3488 Fetch csr_grades_in_bdgt into l_grade_id;
3489 --
3490 If csr_grades_in_bdgt%notfound then
3491 Exit;
3492 End if;
3493 --
3494
3495 l_entity_actuals := 0;
3496
3497 --
3498 l_entity_actuals := get_ent_actual_and_cmmtmnt
3499 (p_budget_version_id => p_budget_version_id,
3500 p_budgeted_entity_cd => p_budgeted_entity_cd,
3501 p_entity_id => l_grade_id,
3502 p_start_date => p_period_start_date,
3503 p_end_date => p_period_end_date,
3504 p_unit_of_measure_id => p_unit_of_measure_id,
3505 p_value_type => 'A');
3506 --
3507 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3508 --
3509 End Loop;
3510 --
3511 Close csr_grades_in_bdgt;
3512 --
3513 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3514 --
3515 Open csr_orgs_in_bdgt;
3516 --
3517 Loop
3518 --
3519 Fetch csr_orgs_in_bdgt into l_organization_id;
3520 --
3521 If csr_orgs_in_bdgt%notfound then
3522 Exit;
3523 End if;
3524 --
3525
3526 l_entity_actuals := 0;
3527
3528 --
3529 l_entity_actuals := get_ent_actual_and_cmmtmnt
3530 (p_budget_version_id => p_budget_version_id,
3531 p_budgeted_entity_cd => p_budgeted_entity_cd,
3532 p_entity_id => l_organization_id,
3533 p_start_date => p_period_start_date,
3534 p_end_date => p_period_end_date,
3535 p_unit_of_measure_id => p_unit_of_measure_id,
3536 p_value_type => 'A');
3537 --
3538 l_budget_actuals := l_budget_actuals + l_entity_actuals;
3539 --
3540 End Loop;
3541 --
3542 Close csr_orgs_in_bdgt;
3543 --
3544 End If;
3545 --
3546 hr_utility.set_location('Leaving:'||l_proc, 10);
3547 --
3548 RETURN l_budget_actuals;
3549 --
3550 Exception When others then
3551 --
3552
3553 hr_utility.set_location('Exception:'||l_proc, 15);
3554 raise;
3555 --
3556 End;
3557 --
3558 --------------------------------------------------------------------
3559 --
3560 -- This function returns the commitment of a budget version.
3561 --
3562 FUNCTION get_budget_commitment(p_budget_version_id in number,
3563 p_budgeted_entity_cd in varchar2,
3564 p_period_start_date in date,
3565 p_period_end_date in date,
3566 p_unit_of_measure_id in number)
3567 RETURN NUMBER
3568 IS
3569 --
3570 l_entity_commitment number;
3571 l_budget_commitment number;
3572 l_position_id pqh_budget_details.position_id%type;
3573 l_job_id pqh_budget_details.position_id%type;
3574 l_grade_id pqh_budget_details.position_id%type;
3575 l_organization_id pqh_budget_details.position_id%type;
3576 --
3577 l_budget_id pqh_budgets.budget_id%type := NULL;
3578 l_unit_of_measure per_shared_types.system_type_cd%type;
3579 --
3580
3581 Cursor csr_positions_in_bdgt is
3582 Select Position_id
3583 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3584 Where bvr.budget_id = bgt.budget_id
3585 AND bvr.budget_version_id = p_budget_version_id
3586 AND bvr.budget_version_id = bdt.budget_version_id
3587 AND bgt.budgeted_entity_cd = 'POSITION';
3588 --
3589 Cursor csr_jobs_in_bdgt is
3590 Select job_id
3591 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3592 Where bvr.budget_id = bgt.budget_id
3593 AND bvr.budget_version_id = p_budget_version_id
3594 AND bvr.budget_version_id = bdt.budget_version_id
3595 AND bgt.budgeted_entity_cd = 'JOB';
3596 --
3597 Cursor csr_grades_in_bdgt is
3598 Select grade_id
3599 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3600 Where bvr.budget_id = bgt.budget_id
3601 AND bvr.budget_version_id = p_budget_version_id
3602 AND bvr.budget_version_id = bdt.budget_version_id
3603 AND bgt.budgeted_entity_cd = 'GRADE';
3604 --
3605 Cursor csr_orgs_in_bdgt is
3606 Select organization_id
3607 From pqh_budgets bgt,pqh_budget_details bdt,pqh_budget_versions bvr
3608 Where bvr.budget_id = bgt.budget_id
3609 AND bvr.budget_version_id = p_budget_version_id
3610 AND bvr.budget_version_id = bdt.budget_version_id
3611 AND bgt.budgeted_entity_cd = 'ORGANIZATION';
3612
3613 --
3614 l_proc varchar2(72) := g_package||'get_budget_commitment';
3615 --
3616 Begin
3617 --
3618 hr_utility.set_location('Entering:'||l_proc, 5);
3619 --
3620 -- Validate if the budget version is valid .
3621 --
3622 Validate_budget(p_budget_version_id => p_budget_version_id,
3623 p_budget_id => l_budget_id);
3624 --
3625 -- Check if the input unit of measure is valid in per_shared_types.
3626 -- Also , check if the uom has been budgeted for in the budget.
3627 --
3628 Validate_unit_of_measure(p_unit_of_measure_id => p_unit_of_measure_id,
3629 p_unit_of_measure_desc => l_unit_of_measure);
3630
3631
3632 Validate_uom_in_budget(p_unit_of_measure_id => p_unit_of_measure_id,
3633 p_budget_id => l_budget_id);
3634 --
3635 Validate_commitment_dates(p_cmmtmnt_start_dt => p_period_start_date,
3636 p_cmmtmnt_end_dt => p_period_end_date,
3637 p_budget_id => l_budget_id);
3638 --
3639 l_budget_commitment := 0;
3640 --
3641 -- Break budget version into its comprising positions .Calculate the
3642 -- commitment for each position and sum it up to obtain budget commitment.
3643 --
3644 If p_budgeted_entity_cd = 'POSITION' Then
3645 for i in csr_positions_in_bdgt loop
3646 l_entity_commitment := get_ent_actual_and_cmmtmnt
3647 (p_budget_version_id => p_budget_version_id,
3648 p_budgeted_entity_cd => p_budgeted_entity_cd,
3649 p_entity_id => i.position_id,
3650 p_start_date => p_period_start_date,
3651 p_end_date => p_period_end_date,
3652 p_unit_of_measure_id => p_unit_of_measure_id,
3653 p_value_type => 'C');
3654 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3655 End Loop;
3656 Elsif p_budgeted_entity_cd = 'JOB' Then
3657 for i in csr_jobs_in_bdgt loop
3658 l_entity_commitment := get_ent_actual_and_cmmtmnt
3659 (p_budget_version_id => p_budget_version_id,
3660 p_budgeted_entity_cd => p_budgeted_entity_cd,
3661 p_entity_id => i.job_id,
3662 p_start_date => p_period_start_date,
3663 p_end_date => p_period_end_date,
3664 p_unit_of_measure_id => p_unit_of_measure_id,
3665 p_value_type => 'C');
3666 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3667 End Loop;
3668 Elsif p_budgeted_entity_cd = 'GRADE' Then
3669 for i in csr_grades_in_bdgt loop
3670 l_entity_commitment := get_ent_actual_and_cmmtmnt
3671 (p_budget_version_id => p_budget_version_id,
3672 p_budgeted_entity_cd => p_budgeted_entity_cd,
3673 p_entity_id => i.grade_id,
3674 p_start_date => p_period_start_date,
3675 p_end_date => p_period_end_date,
3676 p_unit_of_measure_id => p_unit_of_measure_id,
3677 p_value_type => 'C');
3678 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3679 End Loop;
3680 Elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
3681 for i in csr_orgs_in_bdgt loop
3682 l_entity_commitment := get_ent_actual_and_cmmtmnt
3683 (p_budget_version_id => p_budget_version_id,
3684 p_budgeted_entity_cd => p_budgeted_entity_cd,
3685 p_entity_id => i.organization_id,
3686 p_start_date => p_period_start_date,
3687 p_end_date => p_period_end_date,
3688 p_unit_of_measure_id => p_unit_of_measure_id,
3689 p_value_type => 'C');
3690 l_budget_commitment := nvl(l_budget_commitment,0) + nvl(l_entity_commitment,0);
3691 End Loop;
3692 End If;
3693 --
3694 hr_utility.set_location('Leaving:'||l_proc, 10);
3695 --
3696 RETURN l_budget_commitment;
3697 --
3698 Exception When others then
3699 hr_utility.set_location('Exception:'||l_proc, 15);
3700 raise;
3701 End;
3702 --
3703 procedure get_asg_actual_cmmt(p_assignment_id number
3704 ,p_budget_version_id number
3705 ,p_element_type_id number
3706 ,p_start_date date
3707 ,p_end_date date
3708 ,p_assignment_actuals out nocopy number
3709 ,p_assignment_commitment out nocopy number
3710 ,p_assignment_total out nocopy number
3711 ) is
3712 l_last_actuals_date per_time_periods.end_date%type;
3713 l_budget_id pqh_budgets.budget_id%type := NULL;
3714 --
3715 begin
3716 --
3717 p_assignment_total := 0;
3718 p_assignment_actuals := 0;
3719 p_assignment_commitment := 0;
3720 l_last_actuals_date := NULL;
3721 --
3722
3723 /*2716884*/
3724 If p_budget_version_id IS NOT NULL then
3725 --
3726 Validate_budget(p_budget_version_id => p_budget_version_id,
3727 p_budget_id => l_budget_id);
3728 End if;
3729
3730 --
3731 -- get actuals for the assignment and the last payroll run date
3732
3733 -- for the assignment.
3734 --
3735 p_assignment_actuals := get_assign_money_actuals
3736 ( p_budget_id => l_budget_id, /*2716884*/
3737 p_assignment_id => p_assignment_id,
3738 p_element_type_id => p_element_type_id,--Later
3739 p_actuals_start_date => p_start_date,
3740 p_actuals_end_date => p_end_date,
3741 p_last_payroll_dt => l_last_actuals_date
3742 );
3743 --
3744 hr_utility.set_location('Assignment Actual:'||p_assignment_actuals,10);
3745 hr_utility.set_location('Last Actual Date :'||l_last_actuals_date, 20);
3746 --
3747 IF l_last_actuals_date IS NULL OR
3748 l_last_actuals_date <= p_start_date THEN
3749 --
3750 -- payroll has never been run for the position. So actual is zero
3751 --
3752 p_assignment_actuals := 0;
3753 --
3754 p_assignment_commitment := get_assign_money_cmmtmnt
3755 (p_assignment_id => p_assignment_id,
3756 p_budget_version_id => p_budget_version_id,
3757 p_element_type_id => p_element_type_id,
3758 p_period_start_date => p_start_date,
3759 p_period_end_date => p_end_date
3760 );
3761 --
3762 hr_utility.set_location('Assignment Commitment : '||p_assignment_commitment, 30);
3763 --
3764 ELSIF l_last_actuals_date >= p_end_date then
3765 --
3766 -- Actuals is available beyond the required period end date .
3767 -- So commitment is 0.
3768 --
3769 p_assignment_commitment := 0;
3770 --
3771 ELSE
3772 --
3773 -- payroll has been run for the position. calculate commitment
3774 -- from the next day of the last payroll run.
3775 --
3776 p_assignment_commitment := get_assign_money_cmmtmnt
3777 (p_assignment_id => p_assignment_id ,
3778 p_budget_version_id => p_budget_version_id,
3779 p_element_type_id => p_element_type_id,
3780 p_period_start_date => l_last_actuals_date + 1,
3781 p_period_end_date => p_end_date
3782 );
3783 --
3784 hr_utility.set_location('Assignment Commitment : '||p_assignment_commitment, 40);
3785 --
3786 END IF;
3787 --
3788 -- Total up assignment commitment and actual information.
3789 --
3790 p_assignment_total := NVL(p_assignment_actuals,0) + NVL(p_assignment_commitment,0);
3791 --
3792 end;
3793 ------------------------------------------------------------------------------
3794
3795 ------------------------------------------------------------------------------
3796 --
3797 -- Sreevijay - This is used to calculate actual and commitment totals for a budget entity
3798 -- and unit of measurement.
3799 --
3800 Procedure get_actual_and_cmmtmnt( p_position_id in number default null
3801 ,p_job_id in number default null
3802 ,p_grade_id in number default null
3803 ,p_organization_id in number default null
3804 ,p_budget_entity in varchar2
3805 ,p_element_type_id in number default null
3806 ,p_start_date in date default sysdate
3807 ,p_end_date in date default sysdate
3808 ,p_effective_date in date default sysdate
3809 ,p_unit_of_measure in varchar2
3810 ,p_business_group_id in number
3811 ,p_actual_value out nocopy number
3812 ,p_commt_value out nocopy number
3813 ) is
3814
3815 --
3816 -- Cursor to fetch budgets and budget versions
3817 -- Single or multiple controlled budgets
3818 --
3819 cursor c_budgets is
3820 select bgt.budget_id, budget_version_id, budget_start_date, budget_end_date
3821 from pqh_budgets bgt, pqh_budget_versions ver
3822 where bgt.budget_id = ver.budget_id
3823 and (p_effective_date between date_from and date_to)
3824 and position_control_flag = 'Y'
3825 and budgeted_entity_cd = p_budget_entity
3826 and business_group_id = p_business_group_id -- Line added Bug Fix : 2432715
3827 and (p_start_date <= budget_end_date
3828 and p_end_date >= budget_start_date)
3829 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = p_unit_of_measure
3830 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_unit_of_measure
3831 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure);
3832 --
3833 -- Cursors to fetch assignments
3834 --
3835 cursor csr_position_assg is
3836 select distinct assg.assignment_id
3837 from per_all_assignments_f assg
3838 where business_group_id = p_business_group_id
3839 and p_position_id = assg.position_id
3840 and assg.effective_end_date >= p_start_date
3841 and assg.effective_start_date <= p_end_date;
3842 --
3843 cursor csr_job_assg is
3844 select distinct assg.assignment_id
3845 from per_all_assignments_f assg
3846 where business_group_id = p_business_group_id
3847 and p_job_id = assg.job_id
3848 and assg.effective_end_date >= p_start_date
3849 and assg.effective_start_date <= p_end_date;
3850 --
3851 cursor csr_org_assg is
3852 select distinct assg.assignment_id
3853 from per_all_assignments_f assg
3854 where p_organization_id = assg.organization_id
3855 and business_group_id = p_business_group_id
3856 and assg.effective_end_date >= p_start_date
3857 and assg.effective_start_date <= p_end_date;
3858 --
3859 cursor csr_grade_assg is
3860 select distinct assg.assignment_id
3861 from per_all_assignments_f assg
3862 where business_group_id = p_business_group_id
3863 and p_grade_id = assg.grade_id
3864 and assg.effective_end_date >= p_start_date
3865 and assg.effective_start_date <= p_end_date;
3866 --
3867 l_position_id pqh_budget_details.position_id%type;
3868 l_position_name hr_all_positions_f.name%type := NULL;
3869 l_assignment_id per_all_assignments_f.assignment_id%type;
3870 l_last_actuals_date per_time_periods.end_date%type;
3871 --
3872 l_budget_id pqh_budgets.budget_id%type := NULL;
3873 --
3874 l_unit_of_measure per_shared_types.system_type_cd%type;
3875 --
3876 l_assignment_actuals number := 0;
3877 l_assignment_commitment number := 0;
3878 l_assignment_total number := 0;
3879 --
3880 l_entity_actuals number := 0;
3881 l_entity_commitment number := 0;
3882 l_total_actuals number := 0;
3883 l_total_commitment number := 0;
3884 l_assignment_value number := 0;
3885
3886 --
3887 l_proc varchar2(72) := g_package||'get_actual_and_cmmtmnt';
3888
3889
3890 Begin
3891 hr_utility.set_location('Entering:'||l_proc, 5);
3892 hr_utility.set_location('l_unit_of_measure:'||p_unit_of_measure, 5);
3893 --
3894 --
3895 If p_unit_of_measure = 'MONEY' then
3896 --
3897 -- calculate actuals and commitment for each assignment.
3898 --
3899 hr_utility.set_location('~~NS:Before Loop Position: '||p_position_id, 8);
3900 hr_utility.set_location('~~NS:p_effective_date: '||p_effective_date, 8);
3901 hr_utility.set_location('~~NS:p_business_group_id: '||p_business_group_id, 8);
3902 hr_utility.set_location('~~NS:p_budget_entity: '||p_budget_entity, 8);
3903 hr_utility.set_location('~~NS:p_start_date: '||p_start_date, 8);
3904 hr_utility.set_location('~~NS:p_end_date: '||p_end_date, 8);
3905
3906
3907 For l_budget in c_budgets
3908 Loop
3909 hr_utility.set_location('~~NS:Budget Version: '||l_budget.budget_version_id, 8);
3910 -- modified by svorugan Bug Fix : 2432715
3911 l_assignment_total :=0;
3912 l_entity_commitment :=0;
3913 l_entity_actuals :=0;
3914 -- upto here
3915 if p_position_id is not null then
3916 for i in csr_position_assg loop
3917 l_assignment_id := i.assignment_id;
3918 hr_utility.set_location('~~NS:Before Calling get asg actual cmmt: '||p_position_id, 8);
3919 get_asg_actual_cmmt(l_assignment_id
3920 ,l_budget.budget_version_id
3921 ,p_element_type_id
3922 ,p_start_date
3923 ,p_end_date
3924 ,l_assignment_actuals
3925 ,l_assignment_commitment
3926 ,l_assignment_total);
3927 -- Total up entity commitment and actuals info
3928 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3929 --
3930 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3931 --
3932 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3933 --
3934 End Loop; -- csr_assg
3935 elsif p_organization_id is not null then
3936 for i in csr_org_assg loop
3937 l_assignment_id := i.assignment_id;
3938 get_asg_actual_cmmt(l_assignment_id
3939 ,l_budget.budget_version_id
3940 ,p_element_type_id
3941 ,p_start_date
3942 ,p_end_date
3943 ,l_assignment_actuals
3944 ,l_assignment_commitment
3945 ,l_assignment_total);
3946 -- Total up entity commitment and actuals info
3947 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3948 --
3949 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3950 --
3951 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3952 --
3953 End Loop; -- csr_assg
3954 elsif p_job_id is not null then
3955 for i in csr_job_assg loop
3956 l_assignment_id := i.assignment_id;
3957 get_asg_actual_cmmt(l_assignment_id
3958 ,l_budget.budget_version_id
3959 ,p_element_type_id
3960 ,p_start_date
3961 ,p_end_date
3962 ,l_assignment_actuals
3963 ,l_assignment_commitment
3964 ,l_assignment_total);
3965 -- Total up entity commitment and actuals info
3966 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3967 --
3968 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3969 --
3970 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3971 --
3972 End Loop; -- csr_assg
3973 elsif p_grade_id is not null then
3974 for i in csr_grade_assg loop
3975 l_assignment_id := i.assignment_id;
3976 get_asg_actual_cmmt(l_assignment_id
3977 ,l_budget.budget_version_id
3978 ,p_element_type_id
3979 ,p_start_date
3980 ,p_end_date
3981 ,l_assignment_actuals
3982 ,l_assignment_commitment
3983 ,l_assignment_total);
3984 -- Total up entity commitment and actuals info
3985 l_entity_commitment := nvl(l_entity_commitment,0) + NVL(l_assignment_commitment,0);
3986 --
3987 l_entity_actuals := nvl(l_entity_actuals,0) + NVL(l_assignment_actuals,0);
3988 --
3989 hr_utility.set_location('svorugan..2'||l_entity_actuals,50);
3990 --
3991 End Loop; -- csr_assg
3992 end if;
3993 --
3994 hr_utility.set_location('All assignments processed',50);
3995 l_total_actuals := nvl(l_total_actuals,0) + nvl(l_entity_actuals,0);
3996 l_total_commitment := nvl(l_total_commitment,0) + nvl(l_entity_commitment,0);
3997
3998 hr_utility.set_location('Totals: Commitment | Actuals'||l_total_commitment||' | '||l_total_actuals,50);
3999
4000 End Loop; -- csr_budgets
4001
4002 p_actual_value := l_total_actuals;
4003 p_commt_value := l_total_commitment;
4004
4005 Elsif p_unit_of_measure = 'HOURS' then
4006 -- calculate actual value for an entity if the unit is HOURS
4007 p_actual_value := get_actual_hours(p_business_grp_id => p_business_group_id
4008 ,p_position_id => p_position_id
4009 ,p_job_id => p_job_id
4010 ,p_grade_id => p_grade_id
4011 ,p_organization_id => p_organization_id
4012 ,p_start_date => p_start_date
4013 ,p_end_date => p_end_date
4014 ,p_effective_date => p_effective_date);
4015 p_commt_value := 0;
4016 hr_utility.set_location('Actual Hours:'||p_actual_value, 10);
4017 Else
4018 -- Calculate the actual value for an entity if the unit is FTE/HEADCOUNT etc.
4019 p_actual_value := hr_discoverer.get_actual_budget_values
4020 (p_bus_group_id => p_business_group_id,
4021 p_position_id => p_position_id,
4022 p_job_id => p_job_id,
4023 p_grade_id => p_grade_id,
4024 p_organization_id => p_organization_id,
4025 p_start_date => p_start_date,
4026 p_end_date => p_end_date,
4027 p_unit => p_unit_of_measure,
4028 p_actual_val => NULL);
4029 p_commt_value := 0;
4030 --
4031 End if;
4032 Exception
4033 when others then
4034 p_actual_value := null;
4035 p_commt_value := null;
4036 hr_utility.set_location('Exception :'||l_proc,60);
4037 hr_utility.raise_error;
4038 End get_actual_and_cmmtmnt;
4039 --
4040 --
4041 FUNCTION get_pos_money_total9(
4042 p_position_id number,
4043 p_budget_version_id number,
4044 p_actuals_start_date date,
4045 p_actuals_end_date date)
4046 RETURN NUMBER
4047 IS
4048
4049 cursor csr_actual_exists(p_budget_id number) is
4050 select 1
4051 from pqh_bdgt_cmmtmnt_elmnts
4052 where actual_commitment_type in ('ACTUAL','BOTH')
4053 and budget_id = p_budget_id;
4054
4055 --
4056 Cursor csr_pos_cost(
4057 p_position_id number,
4058 p_actuals_start_date date,
4059 p_actuals_end_date date,
4060 p_budget_id number
4061 ) is
4062
4063 Select sum(pc.costed_value)
4064 From
4065 (select distinct assignment_id, payroll_id from per_all_assignments_f assg
4066 where ASSG.effective_end_date >= p_actuals_start_date
4067 and ASSG.effective_start_date <= p_actuals_end_date
4068 AND assg.position_id = p_position_id
4069 -- and assg.assignment_id <> p_ex_assignment_id
4070 ) a,
4071 pay_payroll_actions ppa,
4072 PAY_ASSIGNMENT_ACTIONS AAC,
4073 PAY_COSTS pc,
4074 PAY_ELEMENT_TYPES_F PET,
4075 PAY_INPUT_VALUES_F INV,
4076 pqh_bdgt_cmmtmnt_elmnts pbce
4077 Where PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
4078 AND PPA.PAYROLL_ID = A.PAYROLL_ID
4079 AND ppa.action_type IN ('Q','R','V','B')
4080 AND ppa.date_earned BETWEEN p_actuals_start_date AND p_actuals_end_date
4081 AND aac.run_type_id IS not NULL
4082 and AAC.ASSIGNMENT_ID = a.assignment_id
4083 and aac.assignment_action_id = pc.assignment_action_id
4084 and pbce.actual_commitment_type in ('ACTUAL','BOTH')
4085 and pbce.budget_id = p_budget_id
4086 and pc.input_value_id = inv.input_value_id
4087 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
4088 and pbce.element_type_id = INV.element_type_id
4089 --AND INV.NAME = 'Pay Value'
4090 AND INV.INPUT_VALUE_ID = pbce.element_input_value_id--'Pay Value'
4091 --AND (PET.CLASSIFICATION_ID = p_cl_id_1 or PET.CLASSIFICATION_ID = p_cl_id_2)
4092 AND BALANCE_OR_COST = 'C';
4093 --
4094 --
4095 cursor curs1(p_position_id number, p_actuals_start_date date) is
4096 select classification_id
4097 from pay_element_classifications,
4098 hr_all_positions_f pos,
4099 HR_ORGANIZATION_INFORMATION O3
4100 where classification_name in ('Earnings', 'Employer Liabilities')
4101 and legislation_code = O3.ORG_INFORMATION9
4102 and pos.position_id = p_position_id
4103 and p_actuals_start_date between pos.effective_start_date and pos.effective_end_date
4104 and pos.business_group_id = O3.ORGANIZATION_ID
4105 and O3.ORG_INFORMATION_CONTEXT = 'Business Group Information';
4106 --
4107 cl_id_1 number;
4108 cl_id_2 number;
4109 --
4110 --
4111 --Cursor to find position costed value
4112 --
4113 Cursor csr_pos_cost1(
4114 p_position_id number,
4115 p_actuals_start_date date,
4116 p_actuals_end_date date,
4117 p_cl_id_1 number,
4118 p_cl_id_2 number) is
4119 Select sum(pc.costed_value)
4120 From
4121 (select distinct assignment_id, payroll_id from per_all_assignments_f assg
4122 where ASSG.effective_end_date >= p_actuals_start_date
4123 and ASSG.effective_start_date <= p_actuals_end_date
4124 AND assg.position_id = p_position_id
4125 -- and assg.assignment_id <> p_ex_assignment_id
4126 ) a,
4127 pay_payroll_actions ppa,
4128 PAY_ASSIGNMENT_ACTIONS AAC,
4129 PAY_COSTS pc,
4130 PAY_ELEMENT_TYPES_F PET,
4131 PAY_INPUT_VALUES_F INV
4132 Where PPA.PAYROLL_ACTION_ID = AAC.PAYROLL_ACTION_ID
4133 AND PPA.PAYROLL_ID = A.PAYROLL_ID
4134 AND ppa.action_type IN ('Q','R','V','B')
4135 AND ppa.date_earned BETWEEN p_actuals_start_date AND p_actuals_end_date
4136 AND aac.run_type_id IS not NULL
4137 and AAC.ASSIGNMENT_ID = a.assignment_id
4138 and aac.assignment_action_id = pc.assignment_action_id
4139 and pc.input_value_id = inv.input_value_id
4140 AND PET.ELEMENT_TYPE_ID = INV.ELEMENT_TYPE_ID
4141 AND INV.NAME = 'Pay Value'
4142 AND (PET.CLASSIFICATION_ID = p_cl_id_1 or PET.CLASSIFICATION_ID = p_cl_id_2)
4143 AND BALANCE_OR_COST = 'C';
4144 --
4145 --
4146 l_from_period_start_dt date;
4147 l_from_period_end_dt date;
4148 l_to_period_start_dt date;
4149 l_to_period_end_dt date;
4150 --
4151 l_result_value pay_run_result_values.result_value%type;
4152 l_element_type_id pay_run_results.element_type_id%type;
4153 --
4154
4155 l_converted_amt number;
4156 l_position_actuals number;
4157 l_assignment_cmmt number;
4158 --
4159 l_proc varchar2(72) := g_package ||'get_pos_money_total9';
4160 --
4161 l_dummy number;
4162 l_last_payroll_dt date;
4163 l_budget_id pqh_budgets.budget_id%type := NULL;
4164 --
4165 Begin
4166
4167 hr_utility.set_location('Entering :'||l_proc,5);
4168
4169 l_position_actuals := 0;
4170
4171 If p_budget_version_id IS NOT NULL then
4172 --
4173 Validate_budget(p_budget_version_id => p_budget_version_id,
4174 p_budget_id => l_budget_id);
4175 End if;
4176 If (l_budget_id is not null) then
4177 open csr_actual_exists(l_budget_id);
4178 fetch csr_actual_exists into l_dummy;
4179 if csr_actual_exists%found then
4180 -- This function returns the actual expenditure of one assignment record
4181 -- for a given period.
4182 open csr_pos_cost(p_position_id,
4183 p_actuals_start_date,
4184 p_actuals_end_date,
4185 l_budget_id);
4186 fetch csr_pos_cost into l_position_actuals;
4187 close csr_pos_cost;
4188 --
4189 l_position_actuals := nvl(l_position_actuals,0);
4190 --
4191 else
4192
4193 open curs1(p_position_id , p_actuals_start_date);
4194 fetch curs1 into cl_id_1;
4195 fetch curs1 into cl_id_2;
4196 close curs1;
4197 --
4198 if (cl_id_1 is not null or cl_id_2 is not null) then
4199 -- This function returns the actual expenditure of one assignment record
4200 -- for a given period.
4201 open csr_pos_cost1(p_position_id,
4202 p_actuals_start_date,
4203 p_actuals_end_date,
4204 cl_id_1, cl_id_2);
4205 fetch csr_pos_cost1 into l_position_actuals;
4206 close csr_pos_cost1;
4207 --
4208 l_position_actuals := nvl(l_position_actuals,0);
4209 --
4210 end if;
4211 end if;
4212 else
4213 open curs1(p_position_id , p_actuals_start_date);
4214 fetch curs1 into cl_id_1;
4215 fetch curs1 into cl_id_2;
4216 close curs1;
4217 --
4218 if (cl_id_1 is not null or cl_id_2 is not null) then
4219 -- This function returns the actual expenditure of one assignment record -- for a given period.
4220 open csr_pos_cost1(p_position_id,
4221 p_actuals_start_date,
4222 p_actuals_end_date,
4223 cl_id_1, cl_id_2);
4224 fetch csr_pos_cost1 into l_position_actuals;
4225 close csr_pos_cost1;
4226 --
4227 l_position_actuals := nvl(l_position_actuals,0);
4228 --
4229 end if;
4230 end if;
4231 --
4232 hr_utility.set_location('Leaving :'||l_proc,20);
4233 --
4234 RETURN nvl(l_position_actuals,0);
4235 Exception When others then
4236 hr_utility.set_location('Exception:'||l_proc, 25);
4237 RAISE;
4238 End get_pos_money_total9;
4239 --
4240
4241 End pqh_bdgt_actual_cmmtmnt_pkg;