[Home] [Help]
PACKAGE BODY: APPS.PQH_MGMT_RPT_PKG
Source
1 PACKAGE BODY PQH_MGMT_RPT_PKG AS
2 /* $Header: pqmgtpkg.pkb 120.2 2006/05/11 14:53:19 nsanghal noship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' pqh_mgmt_rpt_pkg.'; -- Global package name
10
11 -------------------------------------------------------------------------------
12 FUNCTION get_budget_measurement_type
13 (
14 p_unit_of_measure_id in number
15 )
16 RETURN VARCHAR2
17 IS
18 /* This is a private function which returns the Budget Measurement Type for a particular
19 Budget Unit */
20 --
21 Cursor csr_budget_measurement_type is
22 Select system_type_cd
23 From per_shared_types
24 Where shared_type_id = p_unit_of_measure_id
25 AND lookup_type = 'BUDGET_MEASUREMENT_TYPE';
26 --
27 l_proc varchar2(72) := g_package||'get_budget_measurement_type';
28 --
29 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
30 --
31 Begin
32 --
33 hr_utility.set_location('Entering:'||l_proc, 5);
34 --
35 -- Check if the unit of measure exists in per_shared_types
36 --
37 Open csr_budget_measurement_type;
38 --
39 Fetch csr_budget_measurement_type into l_budget_measurement_type;
40 --
41 If csr_budget_measurement_type%notfound then
42 --
43 Close csr_budget_measurement_type;
44 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BUDGET_UOM');
45 APP_EXCEPTION.RAISE_EXCEPTION;
46 --
47 End if;
48 --
49 Close csr_budget_measurement_type;
50 --
51 hr_utility.set_location('Leaving:'||l_proc, 10);
52 --
53 RETURN l_budget_measurement_type;
54 Exception When others then
55 --
56 hr_utility.set_location('Exception:'||l_proc, 15);
57 RETURN l_budget_measurement_type;
58 --
59 END get_budget_measurement_type;
60 --
61 FUNCTION get_currency_cd(p_budget_version_id number) return varchar2 is
62 l_proc varchar2(72) := g_package||'get_currency_cd';
63 l_currency_cd varchar2(30);
64 cursor csr_bdgt(p_budget_version_id number) is
65 SELECT pqh_budget.get_currency_cd(bvr.budget_id) CURRENCY_CODE
66 FROM pqh_budget_versions bvr
67 WHERE bvr.budget_version_id = p_budget_version_id;
68 begin
69 hr_utility.set_location('Entering:'||l_proc, 5);
70 open csr_bdgt(p_budget_version_id);
71 fetch csr_bdgt into l_currency_cd;
72 close csr_bdgt;
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 RETURN l_currency_cd;
75 end;
76 ---------------------------------------------------------------------------------------------------------
77 FUNCTION get_posn_bdgt
78 (
79 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
80 p_start_date IN pqh_budgets.budget_start_date%TYPE,
81 p_end_date IN pqh_budgets.budget_end_date%TYPE,
82 p_position_id IN per_positions.position_id%TYPE,
83 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
84 )
85 RETURN NUMBER IS
86 /*
87 This is a private function which will get the budgeted amount for a position given a
88 budget_version_id and unit_of_measure_id
89 */
90 l_proc varchar2(72) := g_package||'get_posn_bdgt';
91 l_total_amt NUMBER := 0;
92 l_unit1_amt NUMBER := 0;
93 l_unit2_amt NUMBER := 0;
94 l_unit3_amt NUMBER := 0;
95
96 CURSOR csr_posn_bdgt_unit1 IS
97 SELECT bdt.budget_unit1_value
98 FROM
99 pqh_budgets bgt,
100 pqh_budget_versions bvr,
101 pqh_budget_details bdt
102 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
103 pqh_budget_periods bpr,
104 per_time_periods ptps,
105 per_time_periods ptpe
106 */
107 WHERE
108 bgt.budget_id = bvr.budget_id
109 AND bvr.budget_version_id = bdt.budget_version_id
110 /* changed for bug#3784023. Now budgeted values will be reported from budget details.
111 AND bdt.budget_detail_id = bpr.budget_detail_id
112 AND ptps.time_period_id = bpr.start_time_period_id
113 AND ptpe.time_period_id = bpr.end_time_period_id
114 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
115 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
116 */
117 AND bvr.budget_version_id = p_budget_version_id
118 AND bgt.budget_unit1_id = p_unit_of_measure_id
119 AND bgt.budgeted_entity_cd = 'POSITION'
120 AND bdt.position_id = p_position_id ;
121
122 CURSOR csr_posn_bdgt_unit2 IS
123 SELECT bdt.budget_unit2_value
124 FROM
125 pqh_budgets bgt,
126 pqh_budget_versions bvr,
127 pqh_budget_details bdt
128 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
129 pqh_budget_periods bpr,
130 per_time_periods ptps,
131 per_time_periods ptpe
132 */
133 WHERE
134 bgt.budget_id = bvr.budget_id
135 AND bvr.budget_version_id = bdt.budget_version_id
136 /* changed for bug#3784023. Now budgeted values will be reported from budget details.
137 AND bdt.budget_detail_id = bpr.budget_detail_id
138 AND ptps.time_period_id = bpr.start_time_period_id
139 AND ptpe.time_period_id = bpr.end_time_period_id
140 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
141 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
142 */
143 AND bvr.budget_version_id = p_budget_version_id
144 AND bgt.budget_unit2_id = p_unit_of_measure_id
145 AND bgt.budgeted_entity_cd = 'POSITION'
146 AND bdt.position_id = p_position_id ;
147
148
149 CURSOR csr_posn_bdgt_unit3 IS
150 SELECT bdt.budget_unit3_value
151 FROM
152 pqh_budgets bgt,
153 pqh_budget_versions bvr,
154 pqh_budget_details bdt
155 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
156
157 pqh_budget_periods bpr,
158 per_time_periods ptps,
159 per_time_periods ptpe */
160 WHERE
161 bgt.budget_id = bvr.budget_id
162 AND bvr.budget_version_id = bdt.budget_version_id
163 /*changed for bug#3784023.
164 AND bdt.budget_detail_id = bpr.budget_detail_id
165 AND ptps.time_period_id = bpr.start_time_period_id
166 AND ptpe.time_period_id = bpr.end_time_period_id
167 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
168 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
169 */
170 AND bvr.budget_version_id = p_budget_version_id
171 AND bgt.budget_unit3_id = p_unit_of_measure_id
172 AND bgt.budgeted_entity_cd = 'POSITION'
173 AND bdt.position_id = p_position_id ;
174 BEGIN
175 hr_utility.set_location('Entering: '||l_proc, 5);
176 -- get unit1 amt
177 OPEN csr_posn_bdgt_unit1;
178 FETCH csr_posn_bdgt_unit1 INTO l_unit1_amt;
179 CLOSE csr_posn_bdgt_unit1;
180 -- get unit2 amt
181 OPEN csr_posn_bdgt_unit2;
182 FETCH csr_posn_bdgt_unit2 INTO l_unit2_amt;
183 CLOSE csr_posn_bdgt_unit2;
184 -- get unit3 amt
185 OPEN csr_posn_bdgt_unit3;
186 FETCH csr_posn_bdgt_unit3 INTO l_unit3_amt;
187 CLOSE csr_posn_bdgt_unit3;
188 --
189 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
190 hr_utility.set_location('Leaving:'||l_proc, 1000);
191 --
192 RETURN l_total_amt;
193 EXCEPTION
194 WHEN OTHERS THEN
195 l_total_amt := 0;
196 RETURN l_total_amt;
197 END get_posn_bdgt;
198 --
199 --
200 ---------------------------------------------------------------------------------------------------------
201 FUNCTION get_entity_bdgt
202 (
203 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
204 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
205 p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
206 p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
207 p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
208 p_start_date IN pqh_budgets.budget_start_date%TYPE,
209 p_end_date IN pqh_budgets.budget_end_date%TYPE,
210 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
211 )
212 RETURN NUMBER IS
213 /*
214 This is a private function which will get the budgeted amount for an entity given a
215 budget_version_id and unit_of_measure_id
216 */
217 l_proc varchar2(72) := g_package||'get_entity_bdgt';
218 l_total_amt NUMBER := 0;
219 l_unit1_amt NUMBER := 0;
220 l_unit2_amt NUMBER := 0;
221 l_unit3_amt NUMBER := 0;
222
223 CURSOR csr_job_bdgt_unit1 IS
224 SELECT bdt.budget_unit1_value
225 FROM
226 pqh_budgets bgt,
227 pqh_budget_versions bvr,
228 pqh_budget_details bdt
229 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
230 pqh_budget_periods bpr,
231 per_time_periods ptps,
232 per_time_periods ptpe
233 */
234 WHERE
235 bgt.budget_id = bvr.budget_id
236 AND bvr.budget_version_id = bdt.budget_version_id
237 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
238 AND bdt.budget_detail_id = bpr.budget_detail_id
239 AND ptps.time_period_id = bpr.start_time_period_id
240 AND ptpe.time_period_id = bpr.end_time_period_id
241 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
242 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
243 */
244 AND bvr.budget_version_id = p_budget_version_id
245 AND bgt.budget_unit1_id = p_unit_of_measure_id
246 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
247 AND p_job_id = bdt.job_id;
248
249 CURSOR csr_job_bdgt_unit2 IS
250 SELECT bdt.budget_unit2_value
251 FROM
252 pqh_budgets bgt,
253 pqh_budget_versions bvr,
254 pqh_budget_details bdt
255 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
256 pqh_budget_periods bpr,
257 per_time_periods ptps,
258 per_time_periods ptpe
259 */
260 WHERE
261 bgt.budget_id = bvr.budget_id
262 AND bvr.budget_version_id = bdt.budget_version_id
263 /* changed for bug#3784023. Now budgeted values will be reported from budget details.
264 AND bdt.budget_detail_id = bpr.budget_detail_id
265 AND ptps.time_period_id = bpr.start_time_period_id
266 AND ptpe.time_period_id = bpr.end_time_period_id
267 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
268 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
269 */
270 AND bvr.budget_version_id = p_budget_version_id
271 AND bgt.budget_unit2_id = p_unit_of_measure_id
272 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
273 AND p_job_id = bdt.job_id;
274
275 CURSOR csr_job_bdgt_unit3 IS
276 SELECT bdt.budget_unit3_value
277 FROM
278 pqh_budgets bgt,
279 pqh_budget_versions bvr,
280 pqh_budget_details bdt
281 /*changed for bug#3784023. Now budgeted values will be reported from budget details
282 pqh_budget_periods bpr,
283 per_time_periods ptps,
284 per_time_periods ptpe
285 */
286 WHERE
287 bgt.budget_id = bvr.budget_id
288 AND bvr.budget_version_id = bdt.budget_version_id
289 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
290 AND bdt.budget_detail_id = bpr.budget_detail_id
291 AND ptps.time_period_id = bpr.start_time_period_id
292 AND ptpe.time_period_id = bpr.end_time_period_id
293 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
294 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
295 */
296 AND bvr.budget_version_id = p_budget_version_id
297 AND bgt.budget_unit3_id = p_unit_of_measure_id
298 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
299 AND p_job_id = bdt.job_id;
300
301 CURSOR csr_grade_bdgt_unit1 IS
302 SELECT bdt.budget_unit1_value
303 FROM
304 pqh_budgets bgt,
305 pqh_budget_versions bvr,
306 pqh_budget_details bdt
307 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
308 pqh_budget_periods bpr,
309 per_time_periods ptps,
310 per_time_periods ptpe
311 */
312 WHERE
313 bgt.budget_id = bvr.budget_id
314 AND bvr.budget_version_id = bdt.budget_version_id
315 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
316 AND bdt.budget_detail_id = bpr.budget_detail_id
317 AND ptps.time_period_id = bpr.start_time_period_id
318 AND ptpe.time_period_id = bpr.end_time_period_id
319 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
320 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
321 */
322 AND bvr.budget_version_id = p_budget_version_id
323 AND bgt.budget_unit1_id = p_unit_of_measure_id
324 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
325 AND p_grade_id = bdt.grade_id;
326
327 CURSOR csr_grade_bdgt_unit2 IS
328 SELECT bdt.budget_unit2_value
329 FROM
330 pqh_budgets bgt,
331 pqh_budget_versions bvr,
332 pqh_budget_details bdt
333 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
334 pqh_budget_periods bpr,
335 per_time_periods ptps,
336 per_time_periods ptpe
337 */
338 WHERE
339 bgt.budget_id = bvr.budget_id
340 AND bvr.budget_version_id = bdt.budget_version_id
341 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
342 AND bdt.budget_detail_id = bpr.budget_detail_id
343 AND ptps.time_period_id = bpr.start_time_period_id
344 AND ptpe.time_period_id = bpr.end_time_period_id
345 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
346 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
347 */
348 AND bvr.budget_version_id = p_budget_version_id
349 AND bgt.budget_unit2_id = p_unit_of_measure_id
350 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
351 AND p_grade_id = bdt.grade_id;
352
353 CURSOR csr_grade_bdgt_unit3 IS
354 SELECT bdt.budget_unit3_value
355 FROM
356 pqh_budgets bgt,
357 pqh_budget_versions bvr,
358 pqh_budget_details bdt
359 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
360 pqh_budget_periods bpr,
361 per_time_periods ptps,
362 per_time_periods ptpe
363 */
364 WHERE
365 bgt.budget_id = bvr.budget_id
366 AND bvr.budget_version_id = bdt.budget_version_id
367 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
368
369 AND bdt.budget_detail_id = bpr.budget_detail_id
370 AND ptps.time_period_id = bpr.start_time_period_id
371 AND ptpe.time_period_id = bpr.end_time_period_id
372 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
373 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
374 */
375 AND bvr.budget_version_id = p_budget_version_id
376 AND bgt.budget_unit3_id = p_unit_of_measure_id
377 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
378 AND p_grade_id = bdt.grade_id;
379
380 CURSOR csr_org_bdgt_unit1 IS
381 SELECT bdt.budget_unit1_value
382 FROM
383 pqh_budgets bgt,
384 pqh_budget_versions bvr,
385 pqh_budget_details bdt
386 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
387
388 pqh_budget_periods bpr,
389 per_time_periods ptps,
390 per_time_periods ptpe
391 */
392 WHERE
393 bgt.budget_id = bvr.budget_id
394 AND bvr.budget_version_id = bdt.budget_version_id
395 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
396 AND bdt.budget_detail_id = bpr.budget_detail_id
397 AND ptps.time_period_id = bpr.start_time_period_id
398 AND ptpe.time_period_id = bpr.end_time_period_id
399 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
400 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
401 */
402 AND bvr.budget_version_id = p_budget_version_id
403 AND bgt.budget_unit1_id = p_unit_of_measure_id
404 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
405 AND p_organization_id = bdt.organization_id;
406
407 CURSOR csr_org_bdgt_unit2 IS
408 SELECT bdt.budget_unit2_value
409 FROM
410 pqh_budgets bgt,
411 pqh_budget_versions bvr,
412 pqh_budget_details bdt
413 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
414 pqh_budget_periods bpr,
415 per_time_periods ptps,
416 per_time_periods ptpe
417 */
418 WHERE
419 bgt.budget_id = bvr.budget_id
420 AND bvr.budget_version_id = bdt.budget_version_id
421 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
422
423 AND bdt.budget_detail_id = bpr.budget_detail_id
424 AND ptps.time_period_id = bpr.start_time_period_id
425 AND ptpe.time_period_id = bpr.end_time_period_id
426 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
427 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
428 */
429 AND bvr.budget_version_id = p_budget_version_id
430 AND bgt.budget_unit2_id = p_unit_of_measure_id
431 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
432 AND p_organization_id = bdt.organization_id;
433
434 CURSOR csr_org_bdgt_unit3 IS
435 SELECT bdt.budget_unit3_value
436 FROM
437 pqh_budgets bgt,
438 pqh_budget_versions bvr,
439 pqh_budget_details bdt
440 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
441 pqh_budget_periods bpr,
442 per_time_periods ptps,
443 per_time_periods ptpe
444 */
445 WHERE
446 bgt.budget_id = bvr.budget_id
447 AND bvr.budget_version_id = bdt.budget_version_id
448 /*changed for bug#3784023. Now budgeted values will be reported from budget details.
449 AND bdt.budget_detail_id = bpr.budget_detail_id
450 AND ptps.time_period_id = bpr.start_time_period_id
451 AND ptpe.time_period_id = bpr.end_time_period_id
452 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
453 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
454 */
455 AND bvr.budget_version_id = p_budget_version_id
456 AND bgt.budget_unit3_id = p_unit_of_measure_id
457 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
458 AND p_organization_id = bdt.organization_id;
459
460 BEGIN
461 hr_utility.set_location('Entering: '||l_proc, 5);
462
463 if p_budgeted_entity_cd = 'JOB' then
464 -- get unit1 amt
465 OPEN csr_job_bdgt_unit1;
466 FETCH csr_job_bdgt_unit1 INTO l_unit1_amt;
467 CLOSE csr_job_bdgt_unit1;
468 -- get unit2 amt
469 OPEN csr_job_bdgt_unit2;
470 FETCH csr_job_bdgt_unit2 INTO l_unit2_amt;
471 CLOSE csr_job_bdgt_unit2;
472 -- get unit3 amt
473 OPEN csr_job_bdgt_unit3;
474 FETCH csr_job_bdgt_unit3 INTO l_unit3_amt;
475 CLOSE csr_job_bdgt_unit3;
476 elsif p_budgeted_entity_cd = 'GRADE' then
477 -- get unit1 amt
478 OPEN csr_grade_bdgt_unit1;
479 FETCH csr_grade_bdgt_unit1 INTO l_unit1_amt;
480 CLOSE csr_grade_bdgt_unit1;
481 -- get unit2 amt
482 OPEN csr_grade_bdgt_unit2;
483 FETCH csr_grade_bdgt_unit2 INTO l_unit2_amt;
484 CLOSE csr_grade_bdgt_unit2;
485 -- get unit3 amt
486 OPEN csr_grade_bdgt_unit3;
487 FETCH csr_grade_bdgt_unit3 INTO l_unit3_amt;
488 CLOSE csr_grade_bdgt_unit3;
489 elsif p_budgeted_entity_cd = 'ORGANIZATION' then
490 -- get unit1 amt
491 OPEN csr_org_bdgt_unit1;
492 FETCH csr_org_bdgt_unit1 INTO l_unit1_amt;
493 CLOSE csr_org_bdgt_unit1;
494 -- get unit2 amt
495 OPEN csr_org_bdgt_unit2;
496 FETCH csr_org_bdgt_unit2 INTO l_unit2_amt;
497 CLOSE csr_org_bdgt_unit2;
498 -- get unit3 amt
499 OPEN csr_org_bdgt_unit3;
500 FETCH csr_org_bdgt_unit3 INTO l_unit3_amt;
501 CLOSE csr_org_bdgt_unit3;
502 end if;
503
504
505 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
506
507 hr_utility.set_location('Leaving:'||l_proc, 1000);
508
509 RETURN l_total_amt;
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 l_total_amt := 0;
514 RETURN l_total_amt;
515 END get_entity_bdgt;
516 --
517 --
518 --
519 ---------------------------------------------------------------------------------------------------------
520 FUNCTION get_posn_element_bdgt
521 (
522 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
523 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
524 p_start_date IN pqh_budgets.budget_start_date%TYPE,
525 p_end_date IN pqh_budgets.budget_end_date%TYPE,
526 p_position_id IN per_positions.position_id%TYPE,
527 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
528 )
529 RETURN NUMBER IS
530 /*
531 This is a private function which will get the budgeted amount for a position given a
532 budget_version_id and unit_of_measure_id
533 */
534 l_proc varchar2(72) := g_package||'get_posn_bdgt';
535 l_total_amt NUMBER := 0;
536 l_unit1_amt NUMBER := 0;
537 l_unit2_amt NUMBER := 0;
538 l_unit3_amt NUMBER := 0;
539
540 CURSOR csr_posn_bdgt_unit1 IS
541 SELECT
542 SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
543 FROM
544 pqh_budgets bgt,
545 pqh_budget_versions bvr,
546 pqh_budget_details bdt,
547 pqh_budget_periods bpr,
548 per_time_periods ptps,
549 per_time_periods ptpe,
550 pqh_budget_sets bst,
551 pqh_budget_elements bel
552 WHERE
553 bgt.budget_id = bvr.budget_id
554 AND bvr.budget_version_id = bdt.budget_version_id
555 AND bdt.budget_detail_id = bpr.budget_detail_id
556 AND ptps.time_period_id = bpr.start_time_period_id
557 AND ptpe.time_period_id = bpr.end_time_period_id
558 AND bpr.budget_period_id = bst.budget_period_id
559 AND bst.budget_set_id = bel.budget_set_id
560 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
561 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
562 AND bvr.budget_version_id = p_budget_version_id
563 AND bgt.budget_unit1_id = p_unit_of_measure_id
564 AND bgt.budgeted_entity_cd = 'POSITION'
565 AND bel.element_type_id = p_element_type_id
566 AND bdt.position_id = p_position_id ;
567
568 CURSOR csr_posn_bdgt_unit2 IS
569 SELECT
570 SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
571 FROM
572 pqh_budgets bgt,
573 pqh_budget_versions bvr,
574 pqh_budget_details bdt,
575 pqh_budget_periods bpr,
576 per_time_periods ptps,
577 per_time_periods ptpe,
578 pqh_budget_sets bst,
579 pqh_budget_elements bel
580 WHERE
581 bgt.budget_id = bvr.budget_id
582 AND bvr.budget_version_id = bdt.budget_version_id
583 AND bdt.budget_detail_id = bpr.budget_detail_id
584 AND ptps.time_period_id = bpr.start_time_period_id
585 AND ptpe.time_period_id = bpr.end_time_period_id
586 AND bpr.budget_period_id = bst.budget_period_id
587 AND bst.budget_set_id = bel.budget_set_id
588 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
589 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
590 AND bvr.budget_version_id = p_budget_version_id
591 AND bgt.budget_unit2_id = p_unit_of_measure_id
592 AND bgt.budgeted_entity_cd = 'POSITION'
593 AND bel.element_type_id = p_element_type_id
594 AND bdt.position_id = p_position_id ;
595
596 CURSOR csr_posn_bdgt_unit3 IS
597 SELECT
598 SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
599 FROM
600 pqh_budgets bgt,
601 pqh_budget_versions bvr,
602 pqh_budget_details bdt,
603 pqh_budget_periods bpr,
604 per_time_periods ptps,
605 per_time_periods ptpe,
606 pqh_budget_sets bst,
607 pqh_budget_elements bel
608 WHERE
609 bgt.budget_id = bvr.budget_id
610 AND bvr.budget_version_id = bdt.budget_version_id
611 AND bdt.budget_detail_id = bpr.budget_detail_id
612 AND ptps.time_period_id = bpr.start_time_period_id
613 AND ptpe.time_period_id = bpr.end_time_period_id
614 AND bpr.budget_period_id = bst.budget_period_id
615 AND bst.budget_set_id = bel.budget_set_id
616 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
617 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
618 AND bvr.budget_version_id = p_budget_version_id
619 AND bgt.budget_unit3_id = p_unit_of_measure_id
620 AND bgt.budgeted_entity_cd = 'POSITION'
621 AND bel.element_type_id = p_element_type_id
622 AND bdt.position_id = p_position_id ;
623 BEGIN
624 hr_utility.set_location('Entering: '||l_proc, 5);
625 -- get unit1 amt
626 OPEN csr_posn_bdgt_unit1;
627 FETCH csr_posn_bdgt_unit1 INTO l_unit1_amt;
628 CLOSE csr_posn_bdgt_unit1;
629 -- get unit2 amt
630 OPEN csr_posn_bdgt_unit2;
631 FETCH csr_posn_bdgt_unit2 INTO l_unit2_amt;
632 CLOSE csr_posn_bdgt_unit2;
633 -- get unit3 amt
634 OPEN csr_posn_bdgt_unit3;
635 FETCH csr_posn_bdgt_unit3 INTO l_unit3_amt;
636 CLOSE csr_posn_bdgt_unit3;
637 --
638 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
639 hr_utility.set_location('Leaving:'||l_proc, 1000);
640 --
641 RETURN l_total_amt;
642 EXCEPTION
643 WHEN OTHERS THEN
644 l_total_amt := 0;
645 RETURN l_total_amt;
646 END get_posn_element_bdgt;
647 --
648 --
649 ---------------------------------------------------------------------------------------------------------
650 FUNCTION get_posn_bset_bdgt
651 (
652 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
653 p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
654 p_start_date IN pqh_budgets.budget_start_date%TYPE,
655 p_end_date IN pqh_budgets.budget_end_date%TYPE,
656 p_position_id IN per_positions.position_id%TYPE,
657 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
658 )
659 RETURN NUMBER IS
660 /*
661 This is a private function which will get the budgeted amount for a position given a
662 budget_version_id and unit_of_measure_id
663 */
664 l_proc varchar2(72) := g_package||'get_posn_bdgt';
665 l_total_amt NUMBER := 0;
666 l_unit1_amt NUMBER := 0;
667 l_unit2_amt NUMBER := 0;
668 l_unit3_amt NUMBER := 0;
669
670 CURSOR csr_posn_bdgt_unit1 IS
671 SELECT
672 SUM(bst.budget_unit1_value)
673 FROM
674 pqh_budgets bgt,
675 pqh_budget_versions bvr,
676 pqh_budget_details bdt,
677 pqh_budget_periods bpr,
678 per_time_periods ptps,
679 per_time_periods ptpe,
680 pqh_budget_sets bst
681 WHERE
682 bgt.budget_id = bvr.budget_id
683 AND bvr.budget_version_id = bdt.budget_version_id
684 AND bdt.budget_detail_id = bpr.budget_detail_id
685 AND ptps.time_period_id = bpr.start_time_period_id
686 AND ptpe.time_period_id = bpr.end_time_period_id
687 AND bpr.budget_period_id = bst.budget_period_id
688 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
689 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
690 AND bvr.budget_version_id = p_budget_version_id
691 AND bgt.budget_unit1_id = p_unit_of_measure_id
692 AND bgt.budgeted_entity_cd = 'POSITION'
693 AND bst.dflt_budget_set_id = p_budget_set_id
694 AND bdt.position_id = p_position_id ;
695
696 CURSOR csr_posn_bdgt_unit2 IS
697 SELECT
698 SUM(bst.budget_unit2_value )
699 FROM
700 pqh_budgets bgt,
701 pqh_budget_versions bvr,
702 pqh_budget_details bdt,
703 pqh_budget_periods bpr,
704 per_time_periods ptps,
705 per_time_periods ptpe,
706 pqh_budget_sets bst
707 WHERE
708 bgt.budget_id = bvr.budget_id
709 AND bvr.budget_version_id = bdt.budget_version_id
710 AND bdt.budget_detail_id = bpr.budget_detail_id
711 AND ptps.time_period_id = bpr.start_time_period_id
712 AND ptpe.time_period_id = bpr.end_time_period_id
713 AND bpr.budget_period_id = bst.budget_period_id
714 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
715 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
716 AND bvr.budget_version_id = p_budget_version_id
717 AND bgt.budget_unit2_id = p_unit_of_measure_id
718 AND bgt.budgeted_entity_cd = 'POSITION'
719 AND bst.dflt_budget_set_id = p_budget_set_id
720 AND bdt.position_id = p_position_id ;
721
722 CURSOR csr_posn_bdgt_unit3 IS
723 SELECT
724 SUM(bst.budget_unit3_value)
725 FROM
726 pqh_budgets bgt,
727 pqh_budget_versions bvr,
728 pqh_budget_details bdt,
729 pqh_budget_periods bpr,
730 per_time_periods ptps,
731 per_time_periods ptpe,
732 pqh_budget_sets bst
733 WHERE
734 bgt.budget_id = bvr.budget_id
735 AND bvr.budget_version_id = bdt.budget_version_id
736 AND bdt.budget_detail_id = bpr.budget_detail_id
737 AND ptps.time_period_id = bpr.start_time_period_id
738 AND ptpe.time_period_id = bpr.end_time_period_id
739 AND bpr.budget_period_id = bst.budget_period_id
740 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
741 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
742 AND bvr.budget_version_id = p_budget_version_id
743 AND bgt.budget_unit3_id = p_unit_of_measure_id
744 AND bgt.budgeted_entity_cd = 'POSITION'
745 AND bst.dflt_budget_set_id = p_budget_set_id
746 AND bdt.position_id = p_position_id ;
747 BEGIN
748 hr_utility.set_location('Entering: '||l_proc, 5);
749 -- get unit1 amt
750 OPEN csr_posn_bdgt_unit1;
751 FETCH csr_posn_bdgt_unit1 INTO l_unit1_amt;
752 CLOSE csr_posn_bdgt_unit1;
753 -- get unit2 amt
754 OPEN csr_posn_bdgt_unit2;
755 FETCH csr_posn_bdgt_unit2 INTO l_unit2_amt;
756 CLOSE csr_posn_bdgt_unit2;
757 -- get unit3 amt
758 OPEN csr_posn_bdgt_unit3;
759 FETCH csr_posn_bdgt_unit3 INTO l_unit3_amt;
760 CLOSE csr_posn_bdgt_unit3;
761 --
762 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
763 hr_utility.set_location('Leaving:'||l_proc, 1000);
764 --
765 RETURN l_total_amt;
766 EXCEPTION
767 WHEN OTHERS THEN
768 l_total_amt := 0;
769 RETURN l_total_amt;
770 END get_posn_bset_bdgt;
771 --
772 --
773 --
774 FUNCTION get_position_bdgt_ver_values
775 (
776 p_budget_version_id IN number,
777 p_budget_id IN number,
778 p_position_id IN number,
779 p_start_date IN date,
780 p_end_date IN date,
781 p_unit_of_measure_id IN number,
782 p_value_type IN varchar2 DEFAULT 'T',
783 p_currency_code IN varchar2,
784 p_budget_set_id IN number,
785 p_element_type_id IN number,
786 p_summarize_by IN varchar2,
787 p_budgeted_or_cmmt IN varchar2
788 )
789 RETURN NUMBER IS
790 l_curr_ver_tot number := 0;
791 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
792
793 BEGIN
794 IF (p_summarize_by in ('BUDGET', 'ELEMENT')) THEN
795 --
796 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
797 --
798 if (( l_budget_measurement_type <> 'MONEY') OR
799 ( l_budget_measurement_type = 'MONEY' and
800 nvl(p_currency_code,'X') = nvl(pqh_budget.get_currency_cd(p_budget_id),'X'))) then
801
802 if (p_summarize_by = 'BUDGET' and p_budgeted_or_cmmt = 'BUDGETED') then
803 l_curr_ver_tot := get_posn_bdgt
804 (
805 p_budget_version_id => p_budget_version_id,
806 p_start_date => p_start_date,
807 p_end_date => p_end_date,
808 p_position_id => p_position_id,
809 p_unit_of_measure_id => p_unit_of_measure_id
810 );
811 elsif (p_summarize_by = 'BUDGET' and p_budgeted_or_cmmt = 'CMMT') then
812 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
813 (
814 p_budget_version_id => p_budget_version_id,
815 p_position_id => p_position_id,
816 p_start_date => p_start_date,
817 p_end_date => p_end_date,
818 p_unit_of_measure_id => p_unit_of_measure_id,
819 p_value_type => p_value_type
820 );
821 elsif (p_summarize_by = 'ELEMENT' and p_budgeted_or_cmmt = 'BUDGETED') then
822 l_curr_ver_tot := get_posn_element_bdgt
823 (
824 p_budget_version_id => p_budget_version_id,
825 p_element_type_id => p_element_type_id,
826 p_start_date => p_start_date,
827 p_end_date => p_end_date,
828 p_position_id => p_position_id,
829 p_unit_of_measure_id => p_unit_of_measure_id
830 );
831 end if;
832 end if;
833 ELSIF (p_summarize_by = 'BSET') THEN
834 if (p_budgeted_or_cmmt = 'BUDGETED') then
835 l_curr_ver_tot := get_posn_bset_bdgt
836 (
837 p_budget_version_id => p_budget_version_id,
838 p_budget_set_id => p_budget_set_id,
839 p_start_date => p_start_date,
840 p_end_date => p_end_date,
841 p_position_id => p_position_id,
842 p_unit_of_measure_id => p_unit_of_measure_id
843 );
844 end if;
845 END IF;
846 return l_curr_ver_tot;
847 EXCEPTION
848 WHEN OTHERS THEN
849 l_curr_ver_tot := 0;
850 return l_curr_ver_tot;
851 END;
852 ---------------------------------------------------------------------------------------------------------
853 FUNCTION get_position_budgeted_or_cmmt
854 (
855 p_budget_version_id IN number DEFAULT NULL,
856 p_position_id IN number,
857 p_start_date IN date,
858 p_end_date IN date,
859 p_unit_of_measure_id IN number,
860 p_value_type IN varchar2 DEFAULT 'T',
861 p_currency_code IN varchar2 DEFAULT NULL,
862 p_budget_set_id IN number,
863 p_element_type_id IN number,
864 p_summarize_by IN varchar2,
865 p_budgeted_or_cmmt IN varchar2
866 )
867 RETURN NUMBER IS
868 l_proc varchar2(72) := g_package||'get_position_actual_cmmtmnts';
869 l_total_amt NUMBER := 0;
870 l_curr_ver_tot NUMBER := 0;
871 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
872 l_budget_id pqh_budget_versions.budget_id%TYPE;
873 l_business_group_id number;
874
875 cursor csr_bdgt(p_budget_version_id number) is
876 SELECT bvr.budget_id
877 FROM pqh_budget_versions bvr
878 WHERE bvr.budget_version_id = p_budget_version_id;
879
880 CURSOR csr_bdgt_positions(p_business_group_id number) IS
881 SELECT DISTINCT
882 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
883 bgt.budget_id
884 FROM
885 pqh_budgets bgt,
886 pqh_budget_versions bvr,
887 pqh_budget_details bdt
888 WHERE
889 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
890 AND bgt.budget_id = bvr.budget_id
891 AND bvr.budget_version_id = bdt.budget_version_id
892 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
893 bgt.budget_unit2_id = p_unit_of_measure_id or
894 bgt.budget_unit3_id = p_unit_of_measure_id)
895 AND bdt.position_id = p_position_id
896 AND bgt.budgeted_entity_cd = 'POSITION'
897 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
898 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
899 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
900
901 BEGIN
902 hr_utility.set_location('Entering: '||l_proc, 5);
903 --
904 if (p_budget_version_id is not null) then
905 if (p_summarize_by IN ('BUDGET', 'BSET', 'ELEMENT')) then
906 open csr_bdgt(p_budget_version_id);
907 fetch csr_bdgt into l_budget_id;
908 close csr_bdgt;
909 l_curr_ver_tot := get_position_bdgt_ver_values
910 (
911 p_budget_version_id => p_budget_version_id,
912 p_budget_id => l_budget_id,
913 p_position_id => p_position_id,
914 p_start_date => p_start_date,
915 p_end_date => p_end_date,
916 p_unit_of_measure_id => p_unit_of_measure_id,
917 p_value_type => p_value_type,
918 p_currency_code => p_currency_code,
919 p_budget_set_id => p_budget_set_id,
920 p_element_type_id => p_element_type_id,
921 p_summarize_by => p_summarize_by,
922 p_budgeted_or_cmmt => p_budgeted_or_cmmt
923 );
924 --
925 end if;
926 --
927 l_total_amt := l_total_amt + l_curr_ver_tot;
928 --
929 else
930 --
931 l_business_group_id := hr_general.get_business_group_id;
932 --
933 OPEN csr_bdgt_positions(l_business_group_id);
934 LOOP
935 FETCH csr_bdgt_positions INTO l_budget_version_id,l_budget_id;
936 EXIT WHEN csr_bdgt_positions%NOTFOUND;
937 --
938 if (p_summarize_by IN ('BUDGET', 'BSET', 'ELEMENT')) then
939 l_curr_ver_tot := get_position_bdgt_ver_values
940 (
941 p_budget_version_id => l_budget_version_id,
942 p_budget_id => l_budget_id,
943 p_position_id => p_position_id,
944 p_start_date => p_start_date,
945 p_end_date => p_end_date,
946 p_unit_of_measure_id => p_unit_of_measure_id,
947 p_value_type => p_value_type,
948 p_currency_code => p_currency_code,
949 p_budget_set_id => p_budget_set_id,
950 p_element_type_id => p_element_type_id,
951 p_summarize_by => p_summarize_by,
952 p_budgeted_or_cmmt => p_budgeted_or_cmmt
953 );
954 --
955 end if;
956 --
957 l_total_amt := l_total_amt + l_curr_ver_tot;
958 --
959 END LOOP;
960 CLOSE csr_bdgt_positions;
961 end if;
962 hr_utility.set_location('Leaving:'||l_proc, 1000);
963 RETURN l_total_amt;
964 EXCEPTION
965 WHEN OTHERS THEN
966 l_total_amt := 0;
967 RETURN l_total_amt;
968 END get_position_budgeted_or_cmmt;
969 --
970 --
971 ---------------------------------------------------------------------------------------------------------
972 FUNCTION get_position_actual_cmmtmnts
973 (
974 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
975 p_position_id IN per_positions.position_id%TYPE,
976 p_start_date IN pqh_budgets.budget_start_date%TYPE,
977 p_end_date IN pqh_budgets.budget_end_date%TYPE,
978 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
979 p_value_type IN varchar2 DEFAULT 'T',
980 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
981 )
982 RETURN NUMBER is
983 /*
984 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg. get_pos_actual_and_cmmtmnt as we did not want any errors
985 to be returned by the original function.
986 This function will return the actual or commitment for a position. If the budget version is
987 specified then it would return the actual or commitment for that budget version. If no budget version is
988 specified the it would return the actual or commitment for all the budget versions where the position is
989 budgeted between the start date and end date.
990 For Actuals : Value Type is 'A'
991 For commitments : Value Type is 'C'
992 Default for value type is 'T' which means both actual and commitments us returned
993 */
994 begin
995 return get_position_budgeted_or_cmmt
996 (
997 p_budget_version_id =>p_budget_version_id,
998 p_position_id =>p_position_id,
999 p_start_date =>p_start_date,
1000 p_end_date =>p_end_date,
1001 p_unit_of_measure_id =>p_unit_of_measure_id,
1002 p_value_type =>p_value_type,
1003 p_currency_code =>p_currency_code,
1004 p_budget_set_id =>NULL,
1005 p_element_type_id =>NULL,
1006 p_summarize_by =>'BUDGET',
1007 p_budgeted_or_cmmt =>'CMMT'
1008 );
1009 end;
1010 --
1011 --
1012 ---------------------------------------------------------------------------------------------------------
1013 FUNCTION get_entity_actual_cmmtmnts
1014 (
1015 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1016 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
1017 p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
1018 p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
1019 p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
1020 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1021 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1022 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1023 p_value_type IN varchar2 DEFAULT 'T',
1024 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1025 )
1026 RETURN NUMBER IS
1027 /*
1028 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg. get_ent_actual_and_cmmtmnt.
1029 This function will return the actual or commitment for an entity. If the budget version is
1030 specified then it would return the actual or commitment for that budget version. If no budget version is
1031 specified the it would return the actual or commitment for all the budget versions where the entity is
1032 budgeted between the start date and end date.
1033 For Actuals : Value Type is 'A'
1034 For commitments : Value Type is 'C'
1035 Default for value type is 'T' which means both actual and commitments us returned
1036 */
1037
1038 l_proc varchar2(72) := g_package||'get_entity_actual_cmmtmnts';
1039 l_total_amt NUMBER := 0;
1040 l_curr_ver_tot NUMBER := 0;
1041 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1042 l_currency_code fnd_currencies.currency_code%TYPE;
1043 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1044 l_business_group_id number;
1045
1046 CURSOR csr_bdgts_job(p_business_group_id number) IS
1047 SELECT
1048 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1049 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1050 FROM
1051 pqh_budgets bgt,
1052 pqh_budget_versions bvr,
1053 pqh_budget_details bdt
1054 WHERE
1055 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1056 AND bgt.budget_id = bvr.budget_id
1057 AND bvr.budget_version_id = bdt.budget_version_id
1058 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1059 bgt.budget_unit2_id = p_unit_of_measure_id or
1060 bgt.budget_unit3_id = p_unit_of_measure_id)
1061 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1062 AND bdt.job_id = p_job_id
1063 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1064 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1065 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1066 CURSOR csr_bdgts_grade(p_business_group_id number) IS
1067 SELECT
1068 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1069 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1070 FROM
1071 pqh_budgets bgt,
1072 pqh_budget_versions bvr,
1073 pqh_budget_details bdt
1074 WHERE
1075 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1076 AND bgt.budget_id = bvr.budget_id
1077 AND bvr.budget_version_id = bdt.budget_version_id
1078 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1079 bgt.budget_unit2_id = p_unit_of_measure_id or
1080 bgt.budget_unit3_id = p_unit_of_measure_id)
1081 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1082 AND bdt.grade_id = p_grade_id
1083 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1084 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1085 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1086 CURSOR csr_bdgts_org(p_business_group_id number) IS
1087 SELECT
1088 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1089 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1090 FROM
1091 pqh_budgets bgt,
1092 pqh_budget_versions bvr,
1093 pqh_budget_details bdt
1094 WHERE
1095 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1096 AND bgt.budget_id = bvr.budget_id
1097 AND bvr.budget_version_id = bdt.budget_version_id
1098 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1099 bgt.budget_unit2_id = p_unit_of_measure_id or
1100 bgt.budget_unit3_id = p_unit_of_measure_id)
1101 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1102 AND bdt.organization_id = p_organization_id
1103 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1104 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1105 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1106 procedure proc_job is
1107 BEGIN
1108
1109 if (( l_budget_measurement_type <> 'MONEY') OR
1110 ( l_budget_measurement_type = 'MONEY' and
1111 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
1112
1113 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
1114 (
1115 p_budget_version_id => l_budget_version_id,
1116 p_budgeted_entity_cd => p_budgeted_entity_cd,
1117 p_entity_id => p_job_id,
1118 p_start_date => p_start_date,
1119 p_end_date => p_end_date,
1120 p_unit_of_measure_id => p_unit_of_measure_id,
1121 p_value_type => p_value_type
1122 );
1123
1124 l_total_amt := l_total_amt + l_curr_ver_tot;
1125
1126 end if;
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 l_curr_ver_tot := 0;
1130 l_total_amt := l_total_amt + l_curr_ver_tot;
1131 END;
1132 procedure proc_grade is
1133 BEGIN
1134
1135 if (( l_budget_measurement_type <> 'MONEY') OR
1136 ( l_budget_measurement_type = 'MONEY' and
1137 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
1138 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
1139 (
1140 p_budget_version_id => l_budget_version_id,
1141 p_budgeted_entity_cd => p_budgeted_entity_cd,
1142 p_entity_id => p_grade_id,
1143 p_start_date => p_start_date,
1144 p_end_date => p_end_date,
1145 p_unit_of_measure_id => p_unit_of_measure_id,
1146 p_value_type => p_value_type
1147 );
1148
1149 l_total_amt := l_total_amt + l_curr_ver_tot;
1150 end if;
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 l_curr_ver_tot := 0;
1154 l_total_amt := l_total_amt + l_curr_ver_tot;
1155 END;
1156 procedure proc_org is
1157 BEGIN
1158
1159 if (( l_budget_measurement_type <> 'MONEY') OR
1160 ( l_budget_measurement_type = 'MONEY' and
1161 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
1162 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
1163 (
1164 p_budget_version_id => l_budget_version_id,
1165 p_budgeted_entity_cd => p_budgeted_entity_cd,
1166 p_entity_id => p_organization_id,
1167 p_start_date => p_start_date,
1168 p_end_date => p_end_date,
1169 p_unit_of_measure_id => p_unit_of_measure_id,
1170 p_value_type => p_value_type
1171 );
1172
1173 l_total_amt := l_total_amt + l_curr_ver_tot;
1174 end if;
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 l_curr_ver_tot := 0;
1178 l_total_amt := l_total_amt + l_curr_ver_tot;
1179 END;
1180 BEGIN
1181 hr_utility.set_location('Entering: '||l_proc, 5);
1182 --
1183 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
1184 l_business_group_id := hr_general.get_business_group_id;
1185 if (p_budget_version_id is not null) then
1186 l_budget_version_id := p_budget_version_id;
1187 l_currency_code := get_currency_cd(p_budget_version_id);
1188 If p_budgeted_entity_cd = 'JOB' Then
1189 proc_job;
1190 elsif p_budgeted_entity_cd = 'GRADE' Then
1191 proc_grade;
1192 elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
1193 proc_org;
1194 end if;
1195 else
1196 --
1197 If p_budgeted_entity_cd = 'JOB' Then
1198 OPEN csr_bdgts_job(l_business_group_id);
1199 LOOP
1200 FETCH csr_bdgts_job INTO l_budget_version_id,l_currency_code;
1201 EXIT WHEN csr_bdgts_job%NOTFOUND;
1202 proc_job;
1203 END LOOP;
1204 CLOSE csr_bdgts_job;
1205 elsif p_budgeted_entity_cd = 'GRADE' Then
1206 OPEN csr_bdgts_grade(l_business_group_id);
1207 LOOP
1208 FETCH csr_bdgts_grade INTO l_budget_version_id,l_currency_code;
1209 EXIT WHEN csr_bdgts_grade%NOTFOUND;
1210 proc_grade;
1211 END LOOP;
1212 CLOSE csr_bdgts_grade;
1213 elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
1214 OPEN csr_bdgts_org(l_business_group_id);
1215 LOOP
1216 FETCH csr_bdgts_org INTO l_budget_version_id,l_currency_code;
1217 EXIT WHEN csr_bdgts_org%NOTFOUND;
1218 proc_org;
1219 END LOOP;
1220 CLOSE csr_bdgts_org;
1221 end if;
1222 end if;
1223 hr_utility.set_location('Leaving:'||l_proc, 1000);
1224 RETURN l_total_amt;
1225
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 l_total_amt := 0;
1229 RETURN l_total_amt;
1230 END get_entity_actual_cmmtmnts;
1231 --
1232 --
1233
1234
1235 ---------------------------------------------------------------------------------------------------------
1236 FUNCTION get_assignment_actuals
1237 (
1238 p_assignment_id IN number,
1239 p_element_type_id IN number default NULL,
1240 p_actuals_start_date IN date,
1241 p_actuals_end_date IN date,
1242 p_unit_of_measure_id IN number
1243 )
1244 RETURN NUMBER IS
1245 /*
1246 This function is a wrapper to pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_actuals
1247 */
1248 l_proc varchar2(72) := g_package||'get_assignment_actuals';
1249 l_total_amt NUMBER := 0;
1250 l_last_payroll_date DATE;
1251
1252 BEGIN
1253 hr_utility.set_location('Entering: '||l_proc, 5);
1254
1255 l_total_amt := pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_actuals
1256 (
1257 p_assignment_id => p_assignment_id,
1258 p_element_type_id => p_element_type_id,
1259 p_actuals_start_date => p_actuals_start_date,
1260 p_actuals_end_date => p_actuals_end_date,
1261 p_unit_of_measure_id => p_unit_of_measure_id,
1262 p_last_payroll_dt => l_last_payroll_date
1263 );
1264
1265 hr_utility.set_location('Leaving:'||l_proc, 1000);
1266
1267 RETURN l_total_amt;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 l_total_amt := 0;
1272 RETURN l_total_amt;
1273 END get_assignment_actuals;
1274 --
1275 --
1276 ---------------------------------------------------------------------------------------------------------
1277 FUNCTION get_assignment_commitment
1278 (
1279 p_assignment_id IN number,
1280 p_budget_version_id IN number default NULL,
1281 p_period_start_date IN date,
1282 p_period_end_date IN date,
1283 p_unit_of_measure_id IN number
1284 )
1285 RETURN NUMBER IS
1286 /*
1287 This function is a wrapper to pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_commitment
1288 */
1289 l_proc varchar2(72) := g_package||'get_assignment_commitment';
1290 l_total_amt NUMBER := 0;
1291 l_last_payroll_date DATE;
1292
1293 BEGIN
1294 hr_utility.set_location('Entering: '||l_proc, 5);
1295
1296 l_total_amt := pqh_bdgt_actual_cmmtmnt_pkg.get_assignment_commitment
1297 (
1298 p_assignment_id => p_assignment_id,
1299 p_budget_version_id => p_budget_version_id,
1300 p_period_start_date => p_period_start_date,
1301 p_period_end_date => p_period_end_date,
1302 p_unit_of_measure_id => p_unit_of_measure_id
1303 );
1304
1305
1306
1307 hr_utility.set_location('Leaving:'||l_proc, 1000);
1308
1309 RETURN l_total_amt;
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 l_total_amt := 0;
1314 RETURN l_total_amt;
1315 END get_assignment_commitment;
1316 --
1317 --
1318 ---------------------------------------------------------------------------------------------------------
1319 FUNCTION get_position_budget_amt
1320 (
1321 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1322 p_position_id IN per_positions.position_id%TYPE,
1323 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1324 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1325 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1326 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1327 )
1328 RETURN NUMBER IS
1329 /*
1330 This function will return the budgeted amt for a position. If the budget version is
1331 specified then it would return the budgeted amt for that budget version. If no budget version is
1332 specified the it would return the budgeted amt for all the budget versions where the position is
1333 budgeted between the start date and end date.
1334 */
1335 BEGIN
1336 return get_position_budgeted_or_cmmt
1337 (
1338 p_budget_version_id =>p_budget_version_id,
1339 p_position_id =>p_position_id,
1340 p_start_date =>p_start_date,
1341 p_end_date =>p_end_date,
1342 p_unit_of_measure_id =>p_unit_of_measure_id,
1343 p_currency_code =>p_currency_code,
1344 p_budget_set_id =>NULL,
1345 p_element_type_id =>NULL,
1346 p_summarize_by =>'BUDGET',
1347 p_budgeted_or_cmmt =>'BUDGETED'
1348 );
1349 END get_position_budget_amt;
1350 --
1351 --
1352 ---------------------------------------------------------------------------------------------------------
1353 FUNCTION get_entity_budget_amt
1354 (
1355 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1356 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
1357 p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
1358 p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
1359 p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
1360 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1361 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1362 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1363 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1364 )
1365 RETURN NUMBER IS
1366
1367 /*
1368 This function will return the budgeted amt for an entity. If the budget version is
1369 specified then it would return the budgeted amt for that budget version. If no budget version is
1370 specified the it would return the budgeted amt for all the budget versions where the entity is
1371 budgeted between the start date and end date.
1372 */
1373
1374 l_proc varchar2(72) := g_package||'get_entity_budget_amt';
1375 l_total_amt NUMBER := 0;
1376 l_curr_ver_tot NUMBER := 0;
1377 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1378 l_currency_code fnd_currencies.currency_code%TYPE;
1379 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1380 l_business_group_id number;
1381
1382 CURSOR csr_bdgts_job(p_business_group_id number) IS
1383 SELECT
1384 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1385 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1386 FROM
1387 pqh_budgets bgt,
1388 pqh_budget_versions bvr,
1389 pqh_budget_details bdt
1390 WHERE
1391 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1392 AND bgt.budget_id = bvr.budget_id
1393 AND bvr.budget_version_id = bdt.budget_version_id
1394 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1395 bgt.budget_unit2_id = p_unit_of_measure_id or
1396 bgt.budget_unit3_id = p_unit_of_measure_id)
1397 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1398 AND bdt.job_id = p_job_id
1399 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1400 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1401 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1402
1403 CURSOR csr_bdgts_grade(p_business_group_id number) IS
1404 SELECT
1405 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1406 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1407 FROM
1408 pqh_budgets bgt,
1409 pqh_budget_versions bvr,
1410 pqh_budget_details bdt
1411 WHERE
1412 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1413 AND bgt.budget_id = bvr.budget_id
1414 AND bvr.budget_version_id = bdt.budget_version_id
1415 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1416 bgt.budget_unit2_id = p_unit_of_measure_id or
1417 bgt.budget_unit3_id = p_unit_of_measure_id)
1418 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1419 AND bdt.grade_id = p_grade_id
1420 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1421 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1422 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1423
1424 CURSOR csr_bdgts_org(p_business_group_id number) IS
1425 SELECT
1426 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1427 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1428 FROM
1429 pqh_budgets bgt,
1430 pqh_budget_versions bvr,
1431 pqh_budget_details bdt
1432 WHERE
1433 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1434 AND bgt.budget_id = bvr.budget_id
1435 AND bvr.budget_version_id = bdt.budget_version_id
1436 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1437 bgt.budget_unit2_id = p_unit_of_measure_id or
1438 bgt.budget_unit3_id = p_unit_of_measure_id)
1439 AND bgt.budgeted_entity_cd = p_budgeted_entity_cd
1440 AND bdt.organization_id = p_organization_id
1441 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1442 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1443 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1444
1445 procedure proc1 is
1446 begin
1447 if (( l_budget_measurement_type <> 'MONEY') OR
1448 ( l_budget_measurement_type = 'MONEY' and
1449 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
1450 l_curr_ver_tot := get_entity_bdgt
1451 (
1452 p_budget_version_id => l_budget_version_id,
1453 p_budgeted_entity_cd => p_budgeted_entity_cd,
1454 p_job_id => p_job_id,
1455 p_grade_id => p_grade_id,
1456 p_organization_id => p_organization_id,
1457 p_start_date => p_start_date,
1458 p_end_date => p_end_date,
1459 p_unit_of_measure_id => p_unit_of_measure_id
1460 );
1461 l_total_amt := l_total_amt + l_curr_ver_tot;
1462 end if;
1463 end;
1464 BEGIN
1465 hr_utility.set_location('Entering: '||l_proc, 5);
1466 --
1467 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
1468 if (p_budget_version_id is not null) then
1469 l_budget_version_id := p_budget_version_id;
1470 l_currency_code := get_currency_cd(p_budget_version_id);
1471 proc1;
1472 else
1473 l_business_group_id := hr_general.get_business_group_id;
1474 If p_budgeted_entity_cd = 'JOB' Then
1475 OPEN csr_bdgts_job(l_business_group_id);
1476 LOOP
1477 FETCH csr_bdgts_job INTO l_budget_version_id,l_currency_code;
1478 EXIT WHEN csr_bdgts_job%NOTFOUND;
1479 proc1;
1480 END LOOP;
1481 CLOSE csr_bdgts_job;
1482 elsif p_budgeted_entity_cd = 'GRADE' Then
1483 OPEN csr_bdgts_grade(l_business_group_id);
1484 LOOP
1485 FETCH csr_bdgts_grade INTO l_budget_version_id,l_currency_code;
1486 EXIT WHEN csr_bdgts_grade%NOTFOUND;
1487 proc1;
1488 END LOOP;
1489 CLOSE csr_bdgts_grade;
1490 elsif p_budgeted_entity_cd = 'ORGANIZATION' Then
1491 OPEN csr_bdgts_org(l_business_group_id);
1492 LOOP
1493 FETCH csr_bdgts_org INTO l_budget_version_id,l_currency_code;
1494 EXIT WHEN csr_bdgts_org%NOTFOUND;
1495 proc1;
1496 END LOOP;
1497 CLOSE csr_bdgts_org;
1498 end if;
1499 end if;
1500 hr_utility.set_location('Leaving:'||l_proc, 1000);
1501 RETURN l_total_amt;
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 l_total_amt := 0;
1506 RETURN l_total_amt;
1507 END get_entity_budget_amt;
1508 --
1509 --
1510
1511
1512 ---------------------------------------------------------------------------------------------------------
1513 FUNCTION get_posn_element_bdgt_amt
1514 (
1515 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1516 p_position_id IN per_positions.position_id%TYPE,
1517 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1518 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1519 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1520 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1521 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1522 )
1523 RETURN NUMBER IS
1524 BEGIN
1525 return get_position_budgeted_or_cmmt
1526 (
1527 p_budget_version_id =>p_budget_version_id,
1528 p_position_id =>p_position_id,
1529 p_start_date =>p_start_date,
1530 p_end_date =>p_end_date,
1531 p_unit_of_measure_id =>p_unit_of_measure_id,
1532 p_value_type =>null,
1533 p_currency_code =>p_currency_code,
1534 p_budget_set_id =>null,
1535 p_element_type_id =>p_element_type_id,
1536 p_summarize_by =>'ELEMENT',
1537 p_budgeted_or_cmmt =>'BUDGETED'
1538 );
1539 END get_posn_element_bdgt_amt;
1540 --
1541 --
1542 --
1543 FUNCTION get_job_element_bdgt
1544 (
1545 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1546 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1547 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1548 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1549 p_job_id IN per_jobs.job_id%TYPE,
1550 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
1551 )
1552 RETURN NUMBER IS
1553 /*
1554 This is a private function which will get the budgeted amount for a job given a
1555 budget_version_id and unit_of_measure_id
1556 */
1557 l_proc varchar2(72) := g_package||'get_job_element_bdgt';
1558 l_total_amt NUMBER := 0;
1559 l_unit1_amt NUMBER := 0;
1560 l_unit2_amt NUMBER := 0;
1561 l_unit3_amt NUMBER := 0;
1562
1563 CURSOR csr_job_bdgt_unit1 IS
1564 SELECT
1565 SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
1566 FROM
1567 pqh_budgets bgt,
1568 pqh_budget_versions bvr,
1569 pqh_budget_details bdt,
1570 pqh_budget_periods bpr,
1571 per_time_periods ptps,
1572 per_time_periods ptpe,
1573 pqh_budget_sets bst,
1574 pqh_budget_elements bel
1575
1576 WHERE
1577 bgt.budget_id = bvr.budget_id
1578 AND bvr.budget_version_id = bdt.budget_version_id
1579 AND bdt.budget_detail_id = bpr.budget_detail_id
1580 AND ptps.time_period_id = bpr.start_time_period_id
1581 AND ptpe.time_period_id = bpr.end_time_period_id
1582 AND bpr.budget_period_id = bst.budget_period_id
1583 AND bst.budget_set_id = bel.budget_set_id
1584 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1585 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1586 AND bvr.budget_version_id = p_budget_version_id
1587 AND bgt.budget_unit1_id = p_unit_of_measure_id
1588 AND bgt.budgeted_entity_cd = 'JOB'
1589 AND bel.element_type_id = p_element_type_id
1590 AND bdt.job_id = p_job_id ;
1591
1592 CURSOR csr_job_bdgt_unit2 IS
1593 SELECT
1594 SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
1595 FROM
1596 pqh_budgets bgt,
1597 pqh_budget_versions bvr,
1598 pqh_budget_details bdt,
1599 pqh_budget_periods bpr,
1600 per_time_periods ptps,
1601 per_time_periods ptpe,
1602 pqh_budget_sets bst,
1603 pqh_budget_elements bel
1604
1605
1606 WHERE
1607 bgt.budget_id = bvr.budget_id
1608 AND bvr.budget_version_id = bdt.budget_version_id
1609 AND bdt.budget_detail_id = bpr.budget_detail_id
1610 AND ptps.time_period_id = bpr.start_time_period_id
1611 AND ptpe.time_period_id = bpr.end_time_period_id
1612 AND bpr.budget_period_id = bst.budget_period_id
1613 AND bst.budget_set_id = bel.budget_set_id
1614 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1615 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1616 AND bvr.budget_version_id = p_budget_version_id
1617 AND bgt.budget_unit2_id = p_unit_of_measure_id
1618 AND bgt.budgeted_entity_cd = 'JOB'
1619 AND bel.element_type_id = p_element_type_id
1620 AND bdt.job_id = p_job_id ;
1621
1622
1623 CURSOR csr_job_bdgt_unit3 IS
1624 SELECT
1625 SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
1626 FROM
1627 pqh_budgets bgt,
1628 pqh_budget_versions bvr,
1629 pqh_budget_details bdt,
1630 pqh_budget_periods bpr,
1631 per_time_periods ptps,
1632 per_time_periods ptpe,
1633 pqh_budget_sets bst,
1634 pqh_budget_elements bel
1635
1636
1637 WHERE
1638 bgt.budget_id = bvr.budget_id
1639 AND bvr.budget_version_id = bdt.budget_version_id
1640 AND bdt.budget_detail_id = bpr.budget_detail_id
1641 AND ptps.time_period_id = bpr.start_time_period_id
1642 AND ptpe.time_period_id = bpr.end_time_period_id
1643 AND bpr.budget_period_id = bst.budget_period_id
1644 AND bst.budget_set_id = bel.budget_set_id
1645 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1646 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1647 AND bvr.budget_version_id = p_budget_version_id
1648 AND bgt.budget_unit3_id = p_unit_of_measure_id
1649 AND bgt.budgeted_entity_cd = 'JOB'
1650 AND bel.element_type_id = p_element_type_id
1651 AND bdt.job_id = p_job_id ;
1652
1653
1654
1655 BEGIN
1656 hr_utility.set_location('Entering: '||l_proc, 5);
1657
1658 -- get unit1 amt
1659 OPEN csr_job_bdgt_unit1;
1660 FETCH csr_job_bdgt_unit1 INTO l_unit1_amt;
1661 CLOSE csr_job_bdgt_unit1;
1662
1663 -- get unit2 amt
1664 OPEN csr_job_bdgt_unit2;
1665 FETCH csr_job_bdgt_unit2 INTO l_unit2_amt;
1666 CLOSE csr_job_bdgt_unit2;
1667
1668 -- get unit3 amt
1669 OPEN csr_job_bdgt_unit3;
1670 FETCH csr_job_bdgt_unit3 INTO l_unit3_amt;
1671 CLOSE csr_job_bdgt_unit3;
1672
1673
1674 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
1675
1676 hr_utility.set_location('Leaving:'||l_proc, 1000);
1677
1678 RETURN l_total_amt;
1679
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 l_total_amt := 0;
1683 RETURN l_total_amt;
1684 END get_job_element_bdgt;
1685 --
1686 -- This function picks the budget version and currency in which the passed job exists and
1687 -- calls another function which calculates the budgeted amount.
1688 --
1689 FUNCTION get_job_element_bdgt_amt
1690 (
1691 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1692 p_job_id IN per_jobs.job_id%TYPE,
1693 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1694 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1695 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1696 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1697 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1698 )
1699 RETURN NUMBER IS
1700 l_proc varchar2(72) := g_package||'get_job_element_bdgt_amt';
1701 l_total_amt NUMBER := 0;
1702 l_curr_ver_tot NUMBER := 0;
1703 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1704 l_currency_code fnd_currencies.currency_code%TYPE;
1705 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1706 l_business_group_id number;
1707
1708 CURSOR csr_bdgt_jobs IS
1709 SELECT
1710 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1711 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1712 FROM
1713 pqh_budgets bgt,
1714 pqh_budget_versions bvr,
1715 pqh_budget_details bdt
1716 WHERE
1717 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
1718 AND bgt.budget_id = bvr.budget_id
1719 AND bvr.budget_version_id = bdt.budget_version_id
1720 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1721 bgt.budget_unit2_id = p_unit_of_measure_id or
1722 bgt.budget_unit3_id = p_unit_of_measure_id)
1723 AND bdt.job_id = p_job_id
1724 AND bgt.budgeted_entity_cd = 'JOB'
1725 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1726 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1727 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1728 procedure proc1 is
1729 begin
1730 if (( l_budget_measurement_type <> 'MONEY') OR
1731 ( l_budget_measurement_type = 'MONEY' and
1732 nvl(l_currency_code,'X') = nvl(p_currency_code ,'X'))) then
1733 l_curr_ver_tot := get_job_element_bdgt
1734 (
1735 p_budget_version_id => l_budget_version_id,
1736 p_element_type_id => p_element_type_id,
1737 p_start_date => p_start_date,
1738 p_end_date => p_end_date,
1739 p_job_id => p_job_id,
1740 p_unit_of_measure_id => p_unit_of_measure_id
1741 );
1742 l_total_amt := l_total_amt + l_curr_ver_tot;
1743 end if;
1744 end;
1745 BEGIN
1746 hr_utility.set_location('Entering: '||l_proc, 5);
1747
1748 --
1749 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
1750 if (p_budget_version_id is not null) then
1751 l_budget_version_id := p_budget_version_id;
1752 l_currency_code := get_currency_cd(p_budget_version_id);
1753 proc1;
1754 else
1755 --
1756 l_business_group_id := hr_general.get_business_group_id;
1757 --
1758 OPEN csr_bdgt_jobs;
1759 LOOP
1760 FETCH csr_bdgt_jobs INTO l_budget_version_id,l_currency_code;
1761 EXIT WHEN csr_bdgt_jobs%NOTFOUND;
1762 proc1;
1763 END LOOP;
1764 CLOSE csr_bdgt_jobs;
1765 end if;
1766 hr_utility.set_location('Leaving:'||l_proc, 1000);
1767
1768 RETURN l_total_amt;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 l_total_amt := 0;
1773 RETURN l_total_amt;
1774 END get_job_element_bdgt_amt;
1775 --
1776 --
1777 --
1778 FUNCTION get_grde_element_bdgt
1779 (
1780 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1781 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1782 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1783 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1784 p_grade_id IN per_grades.grade_id%TYPE,
1785 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
1786 )
1787 RETURN NUMBER IS
1788 /*
1789 This is a private function which will get the budgeted amount for a grade given a
1790 budget_version_id and unit_of_measure_id
1791 */
1792 l_proc varchar2(72) := g_package||'get_grde_element_bdgt';
1793 l_total_amt NUMBER := 0;
1794 l_unit1_amt NUMBER := 0;
1795 l_unit2_amt NUMBER := 0;
1796 l_unit3_amt NUMBER := 0;
1797
1798 CURSOR csr_grade_bdgt_unit1 IS
1799 SELECT
1800 SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
1801 FROM
1802 pqh_budgets bgt,
1803 pqh_budget_versions bvr,
1804 pqh_budget_details bdt,
1805 pqh_budget_periods bpr,
1806 per_time_periods ptps,
1807 per_time_periods ptpe,
1808 pqh_budget_sets bst,
1809 pqh_budget_elements bel
1810
1811 WHERE
1812 bgt.budget_id = bvr.budget_id
1813 AND bvr.budget_version_id = bdt.budget_version_id
1814 AND bdt.budget_detail_id = bpr.budget_detail_id
1815 AND ptps.time_period_id = bpr.start_time_period_id
1816 AND ptpe.time_period_id = bpr.end_time_period_id
1817 AND bpr.budget_period_id = bst.budget_period_id
1818 AND bst.budget_set_id = bel.budget_set_id
1819 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1820 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1821 AND bvr.budget_version_id = p_budget_version_id
1822 AND bgt.budget_unit1_id = p_unit_of_measure_id
1823 AND bgt.budgeted_entity_cd = 'GRADE'
1824 AND bel.element_type_id = p_element_type_id
1825 AND bdt.grade_id = p_grade_id ;
1826
1827 CURSOR csr_grade_bdgt_unit2 IS
1828 SELECT
1829 SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
1830 FROM
1831 pqh_budgets bgt,
1832 pqh_budget_versions bvr,
1833 pqh_budget_details bdt,
1834 pqh_budget_periods bpr,
1835 per_time_periods ptps,
1836 per_time_periods ptpe,
1837 pqh_budget_sets bst,
1838 pqh_budget_elements bel
1839
1840
1841 WHERE
1842 bgt.budget_id = bvr.budget_id
1843 AND bvr.budget_version_id = bdt.budget_version_id
1844 AND bdt.budget_detail_id = bpr.budget_detail_id
1845 AND ptps.time_period_id = bpr.start_time_period_id
1846 AND ptpe.time_period_id = bpr.end_time_period_id
1847 AND bpr.budget_period_id = bst.budget_period_id
1848 AND bst.budget_set_id = bel.budget_set_id
1849 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1850 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1851 AND bvr.budget_version_id = p_budget_version_id
1852 AND bgt.budget_unit2_id = p_unit_of_measure_id
1853 AND bgt.budgeted_entity_cd = 'GRADE'
1854 AND bel.element_type_id = p_element_type_id
1855 AND bdt.grade_id = p_grade_id ;
1856
1857
1858 CURSOR csr_grade_bdgt_unit3 IS
1859 SELECT
1860 SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
1861 FROM
1862 pqh_budgets bgt,
1863 pqh_budget_versions bvr,
1864 pqh_budget_details bdt,
1865 pqh_budget_periods bpr,
1866 per_time_periods ptps,
1867 per_time_periods ptpe,
1868 pqh_budget_sets bst,
1869 pqh_budget_elements bel
1870
1871
1872 WHERE
1873 bgt.budget_id = bvr.budget_id
1874 AND bvr.budget_version_id = bdt.budget_version_id
1875 AND bdt.budget_detail_id = bpr.budget_detail_id
1876 AND ptps.time_period_id = bpr.start_time_period_id
1877 AND ptpe.time_period_id = bpr.end_time_period_id
1878 AND bpr.budget_period_id = bst.budget_period_id
1879 AND bst.budget_set_id = bel.budget_set_id
1880 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
1881 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
1882 AND bvr.budget_version_id = p_budget_version_id
1883 AND bgt.budget_unit3_id = p_unit_of_measure_id
1884 AND bgt.budgeted_entity_cd = 'GRADE'
1885 AND bel.element_type_id = p_element_type_id
1886 AND bdt.grade_id = p_grade_id ;
1887
1888
1889
1890 BEGIN
1891 hr_utility.set_location('Entering: '||l_proc, 5);
1892
1893 -- get unit1 amt
1894 OPEN csr_grade_bdgt_unit1;
1895 FETCH csr_grade_bdgt_unit1 INTO l_unit1_amt;
1896 CLOSE csr_grade_bdgt_unit1;
1897
1898 -- get unit2 amt
1899 OPEN csr_grade_bdgt_unit2;
1900 FETCH csr_grade_bdgt_unit2 INTO l_unit2_amt;
1901 CLOSE csr_grade_bdgt_unit2;
1902
1903 -- get unit3 amt
1904 OPEN csr_grade_bdgt_unit3;
1905 FETCH csr_grade_bdgt_unit3 INTO l_unit3_amt;
1906 CLOSE csr_grade_bdgt_unit3;
1907
1908
1909 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
1910
1911 hr_utility.set_location('Leaving:'||l_proc, 1000);
1912
1913 RETURN l_total_amt;
1914
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 l_total_amt := 0;
1918 RETURN l_total_amt;
1919 END get_grde_element_bdgt;
1920 --
1921 -- This function picks the budget version and currency in which the passed grade exists and
1922 -- calls another function which calculates the budgeted amount.
1923 --
1924 FUNCTION get_grde_element_bdgt_amt
1925 (
1926 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1927 p_grade_id IN per_grades.grade_id%TYPE,
1928 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1929 p_start_date IN pqh_budgets.budget_start_date%TYPE,
1930 p_end_date IN pqh_budgets.budget_end_date%TYPE,
1931 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1932 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
1933 )
1934 RETURN NUMBER IS
1935
1936 /*
1937 */
1938
1939 l_proc varchar2(72) := g_package||'get_grde_element_bdgt_amt';
1940 l_total_amt NUMBER := 0;
1941 l_curr_ver_tot NUMBER := 0;
1942 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1943 l_currency_code fnd_currencies.currency_code%TYPE;
1944 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1945 l_business_group_id number;
1946
1947 CURSOR csr_bdgt_grades IS
1948 SELECT
1949 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1950 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1951 FROM
1952 pqh_budgets bgt,
1953 pqh_budget_versions bvr,
1954 pqh_budget_details bdt
1955 WHERE
1956 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
1957 AND bgt.budget_id = bvr.budget_id
1958 AND bvr.budget_version_id = bdt.budget_version_id
1959 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
1960 bgt.budget_unit2_id = p_unit_of_measure_id or
1961 bgt.budget_unit3_id = p_unit_of_measure_id)
1962 AND bdt.grade_id = p_grade_id
1963 AND bgt.budgeted_entity_cd = 'GRADE'
1964 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
1965 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
1966 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
1967 procedure proc1 is
1968 begin
1969 if (( l_budget_measurement_type <> 'MONEY') OR
1970 ( l_budget_measurement_type = 'MONEY' and
1971 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
1972 l_curr_ver_tot := get_grde_element_bdgt
1973 (
1974 p_budget_version_id => l_budget_version_id,
1975 p_element_type_id => p_element_type_id,
1976 p_start_date => p_start_date,
1977 p_end_date => p_end_date,
1978 p_grade_id => p_grade_id,
1979 p_unit_of_measure_id => p_unit_of_measure_id
1980 );
1981 l_total_amt := l_total_amt + l_curr_ver_tot;
1982 end if;
1983 end;
1984 BEGIN
1985 hr_utility.set_location('Entering: '||l_proc, 5);
1986
1987 --
1988 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
1989 if (p_budget_version_id is not null) then
1990 l_budget_version_id := p_budget_version_id;
1991 l_currency_code := get_currency_cd(p_budget_version_id);
1992 proc1;
1993 else
1994 --
1995 l_business_group_id := hr_general.get_business_group_id;
1996 --
1997 OPEN csr_bdgt_grades;
1998 LOOP
1999 FETCH csr_bdgt_grades INTO l_budget_version_id,l_currency_code;
2000 EXIT WHEN csr_bdgt_grades%NOTFOUND;
2001 proc1;
2002 END LOOP;
2003 CLOSE csr_bdgt_grades;
2004 end if;
2005 hr_utility.set_location('Leaving:'||l_proc, 1000);
2006 RETURN l_total_amt;
2007
2008 EXCEPTION
2009 WHEN OTHERS THEN
2010 l_total_amt := 0;
2011 RETURN l_total_amt;
2012 END get_grde_element_bdgt_amt;
2013 --
2014 --
2015 ---------------------------------------------------------------------------------------------------------
2016 FUNCTION get_orgn_element_bdgt
2017 (
2018 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2019 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2020 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2021 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2022 p_organization_id IN hr_organization_units.organization_id%TYPE,
2023 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
2024 )
2025 RETURN NUMBER IS
2026 /*
2027 This is a private function which will get the budgeted amount for a organization given a
2028 budget_version_id and unit_of_measure_id
2029 */
2030 l_proc varchar2(72) := g_package||'get_grde_element_bdgt';
2031 l_total_amt NUMBER := 0;
2032 l_unit1_amt NUMBER := 0;
2033 l_unit2_amt NUMBER := 0;
2034 l_unit3_amt NUMBER := 0;
2035
2036 CURSOR csr_orgn_bdgt_unit1 IS
2037 SELECT
2038 SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
2039 FROM
2040 pqh_budgets bgt,
2041 pqh_budget_versions bvr,
2042 pqh_budget_details bdt,
2043 pqh_budget_periods bpr,
2044 per_time_periods ptps,
2045 per_time_periods ptpe,
2046 pqh_budget_sets bst,
2047 pqh_budget_elements bel
2048
2049 WHERE
2050 bgt.budget_id = bvr.budget_id
2051 AND bvr.budget_version_id = bdt.budget_version_id
2052 AND bdt.budget_detail_id = bpr.budget_detail_id
2053 AND ptps.time_period_id = bpr.start_time_period_id
2054 AND ptpe.time_period_id = bpr.end_time_period_id
2055 AND bpr.budget_period_id = bst.budget_period_id
2056 AND bst.budget_set_id = bel.budget_set_id
2057 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
2058 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
2059 AND bvr.budget_version_id = p_budget_version_id
2060 AND bgt.budget_unit1_id = p_unit_of_measure_id
2061 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
2062 AND bel.element_type_id = p_element_type_id
2063 AND bdt.organization_id = p_organization_id ;
2064
2065 CURSOR csr_orgn_bdgt_unit2 IS
2066 SELECT
2067 SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
2068 FROM
2069 pqh_budgets bgt,
2070 pqh_budget_versions bvr,
2071 pqh_budget_details bdt,
2072 pqh_budget_periods bpr,
2073 per_time_periods ptps,
2074 per_time_periods ptpe,
2075 pqh_budget_sets bst,
2076 pqh_budget_elements bel
2077
2078
2079 WHERE
2080 bgt.budget_id = bvr.budget_id
2081 AND bvr.budget_version_id = bdt.budget_version_id
2082 AND bdt.budget_detail_id = bpr.budget_detail_id
2083 AND ptps.time_period_id = bpr.start_time_period_id
2084 AND ptpe.time_period_id = bpr.end_time_period_id
2085 AND bpr.budget_period_id = bst.budget_period_id
2086 AND bst.budget_set_id = bel.budget_set_id
2087 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
2088 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
2089 AND bvr.budget_version_id = p_budget_version_id
2090 AND bgt.budget_unit2_id = p_unit_of_measure_id
2091 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
2092 AND bel.element_type_id = p_element_type_id
2093 AND bdt.organization_id = p_organization_id ;
2094
2095
2096 CURSOR csr_orgn_bdgt_unit3 IS
2097 SELECT
2098 SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
2099 FROM
2100 pqh_budgets bgt,
2101 pqh_budget_versions bvr,
2102 pqh_budget_details bdt,
2103 pqh_budget_periods bpr,
2104 per_time_periods ptps,
2105 per_time_periods ptpe,
2106 pqh_budget_sets bst,
2107 pqh_budget_elements bel
2108
2109
2110 WHERE
2111 bgt.budget_id = bvr.budget_id
2112 AND bvr.budget_version_id = bdt.budget_version_id
2113 AND bdt.budget_detail_id = bpr.budget_detail_id
2114 AND ptps.time_period_id = bpr.start_time_period_id
2115 AND ptpe.time_period_id = bpr.end_time_period_id
2116 AND bpr.budget_period_id = bst.budget_period_id
2117 AND bst.budget_set_id = bel.budget_set_id
2118 AND ( ptps.start_date BETWEEN p_start_date AND p_end_date
2119 OR p_start_date BETWEEN ptps.start_date AND ptpe.end_date )
2120 AND bvr.budget_version_id = p_budget_version_id
2121 AND bgt.budget_unit3_id = p_unit_of_measure_id
2122 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
2123 AND bel.element_type_id = p_element_type_id
2124 AND bdt.organization_id = p_organization_id ;
2125
2126
2127
2128 BEGIN
2129 hr_utility.set_location('Entering: '||l_proc, 5);
2130
2131 -- get unit1 amt
2132 OPEN csr_orgn_bdgt_unit1;
2133 FETCH csr_orgn_bdgt_unit1 INTO l_unit1_amt;
2134 CLOSE csr_orgn_bdgt_unit1;
2135
2136 -- get unit2 amt
2137 OPEN csr_orgn_bdgt_unit2;
2138 FETCH csr_orgn_bdgt_unit2 INTO l_unit2_amt;
2139 CLOSE csr_orgn_bdgt_unit2;
2140
2141 -- get unit3 amt
2142 OPEN csr_orgn_bdgt_unit3;
2143 FETCH csr_orgn_bdgt_unit3 INTO l_unit3_amt;
2144 CLOSE csr_orgn_bdgt_unit3;
2145
2146
2147 l_total_amt := NVL(l_unit1_amt,0) + NVL(l_unit2_amt,0) + NVL(l_unit3_amt,0);
2148
2149 hr_utility.set_location('Leaving:'||l_proc, 1000);
2150
2151 RETURN l_total_amt;
2152
2153 EXCEPTION
2154 WHEN OTHERS THEN
2155 l_total_amt := 0;
2156 RETURN l_total_amt;
2157 END get_orgn_element_bdgt;
2158 --
2159 -- This function picks the budget version and currency in which the passed grade exists and
2160 -- calls another function which calculates the budgeted amount.
2161 --
2162 FUNCTION get_orgn_element_bdgt_amt
2163 (
2164 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2165 p_organization_id IN hr_organization_units.organization_id%TYPE,
2166 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2167 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2168 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2169 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2170 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2171 )
2172 RETURN NUMBER IS
2173 l_proc varchar2(72) := g_package||'get_orgn_element_bdgt_amt';
2174 l_total_amt NUMBER := 0;
2175 l_curr_ver_tot NUMBER := 0;
2176 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2177 l_currency_code fnd_currencies.currency_code%TYPE;
2178 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2179 l_business_group_id number;
2180
2181 CURSOR csr_bdgt_orgs IS
2182 SELECT
2183 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2184 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2185 FROM
2186 pqh_budgets bgt,
2187 pqh_budget_versions bvr,
2188 pqh_budget_details bdt
2189 WHERE
2190 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2191 AND bgt.budget_id = bvr.budget_id
2192 AND bvr.budget_version_id = bdt.budget_version_id
2193 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2194 bgt.budget_unit2_id = p_unit_of_measure_id or
2195 bgt.budget_unit3_id = p_unit_of_measure_id)
2196 AND bdt.organization_id = p_organization_id
2197 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
2198 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2199 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2200 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2201 procedure proc1 is
2202 BEGIN
2203 if (( l_budget_measurement_type <> 'MONEY') OR
2204 ( l_budget_measurement_type = 'MONEY' and
2205 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2206 l_curr_ver_tot := get_orgn_element_bdgt
2207 (
2208 p_budget_version_id => l_budget_version_id,
2209 p_element_type_id => p_element_type_id,
2210 p_start_date => p_start_date,
2211 p_end_date => p_end_date,
2212 p_organization_id => p_organization_id,
2213 p_unit_of_measure_id => p_unit_of_measure_id
2214 );
2215 l_total_amt := l_total_amt + l_curr_ver_tot;
2216 end if;
2217 end;
2218 BEGIN
2219 hr_utility.set_location('Entering: '||l_proc, 5);
2220
2221 --
2222 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2223 if (p_budget_version_id is not null) then
2224 l_budget_version_id := p_budget_version_id;
2225 l_currency_code := get_currency_cd(p_budget_version_id);
2226 proc1;
2227 else
2228 --
2229 l_business_group_id := hr_general.get_business_group_id;
2230 --
2231 OPEN csr_bdgt_orgs;
2232 LOOP
2233 FETCH csr_bdgt_orgs INTO l_budget_version_id,l_currency_code;
2234 EXIT WHEN csr_bdgt_orgs%NOTFOUND;
2235 proc1;
2236 END LOOP;
2237 CLOSE csr_bdgt_orgs;
2238 end if;
2239 hr_utility.set_location('Leaving:'||l_proc, 1000);
2240
2241 RETURN l_total_amt;
2242
2243 EXCEPTION
2244 WHEN OTHERS THEN
2245 l_total_amt := 0;
2246 RETURN l_total_amt;
2247 END get_orgn_element_bdgt_amt;
2248 /****************************************/
2249
2250 --
2251 --
2252
2253
2254 ---------------------------------------------------------------------------------------------------------
2255 FUNCTION get_posn_bset_bdgt_amt
2256 (
2257 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2258 p_position_id IN per_positions.position_id%TYPE,
2259 p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
2260 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2261 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2262 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
2263 )
2264 RETURN NUMBER IS
2265 BEGIN
2266 return get_position_budgeted_or_cmmt
2267 (
2268 p_budget_version_id =>p_budget_version_id,
2269 p_position_id =>p_position_id,
2270 p_start_date =>p_start_date,
2271 p_end_date =>p_end_date,
2272 p_unit_of_measure_id =>p_unit_of_measure_id,
2273 p_value_type =>null,
2274 p_currency_code =>null,
2275 p_budget_set_id =>p_budget_set_id,
2276 p_element_type_id =>NULL,
2277 p_summarize_by =>'BSET',
2278 p_budgeted_or_cmmt =>'BUDGETED'
2279 );
2280 END get_posn_bset_bdgt_amt;
2281 --
2282 --
2283 ---------------------------------------------------------------------------------------------------------
2284 FUNCTION get_posn_elmnt_actual_cmmtmnts
2285 (
2286 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2287 p_position_id IN per_positions.position_id%TYPE,
2288 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2289 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2290 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2291 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2292 p_value_type IN varchar2 DEFAULT 'T',
2293 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2294 )
2295 RETURN NUMBER IS
2296
2297 /*
2298 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt as we did not want any errors
2299 to be returned by the original function.
2300 This function will return the actual or commitment for a position. If the budget version is
2301 specified then it would return the actual or commitment for that budget version. If no budget version is
2302 specified the it would return the actual or commitment for all the budget versions where the position is
2303 budgeted between the start date and end date.
2304 For Actuals : Value Type is 'A'
2305 For commitments : Value Type is 'C'
2306 Default for value type is 'T' which means both actual and commitments us returned
2307 */
2308
2309 l_proc varchar2(72) := g_package||'get_posn_elmnt_actual_cmmtmnts';
2310 l_total_amt NUMBER := 0;
2311 l_curr_ver_tot NUMBER := 0;
2312 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2313 l_currency_code fnd_currencies.currency_code%TYPE;
2314 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2315 l_business_group_id number;
2316
2317 CURSOR csr_bdgt_positions IS
2318 SELECT DISTINCT
2319 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2320 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2321 FROM
2322 pqh_budgets bgt,
2323 pqh_budget_versions bvr,
2324 pqh_budget_details bdt,
2325 pqh_budget_periods bpr,
2326 pqh_budget_sets bst,
2327 pqh_budget_elements bel
2328 WHERE
2329 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2330 AND bgt.budget_id = bvr.budget_id
2331 AND bvr.budget_version_id = bdt.budget_version_id
2332 AND bdt.budget_detail_id = bpr.budget_detail_id
2333 AND bpr.budget_period_id = bst.budget_period_id
2334 AND bst.budget_set_id = bel.budget_set_id
2335 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2336 bgt.budget_unit2_id = p_unit_of_measure_id or
2337 bgt.budget_unit3_id = p_unit_of_measure_id)
2338 AND bdt.position_id = p_position_id
2339 AND bel.element_type_id = p_element_type_id
2340 AND bgt.budgeted_entity_cd = 'POSITION'
2341 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2342 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2343 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2344 procedure proc1 is
2345 BEGIN
2346 if (( l_budget_measurement_type <> 'MONEY') OR
2347 ( l_budget_measurement_type = 'MONEY' and
2348 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2349
2350 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
2351 (
2352 p_budget_version_id => l_budget_version_id,
2353 p_budgeted_entity_cd => 'POSITION',
2354 p_entity_id => p_position_id,
2355 p_element_type_id => p_element_type_id,
2356 p_start_date => p_start_date,
2357 p_end_date => p_end_date,
2358 p_unit_of_measure_id => p_unit_of_measure_id,
2359 p_value_type => p_value_type
2360 );
2361
2362 l_total_amt := l_total_amt + l_curr_ver_tot;
2363 end if;
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 l_curr_ver_tot := 0;
2367 l_total_amt := l_total_amt + l_curr_ver_tot;
2368 END;
2369 BEGIN
2370 hr_utility.set_location('Entering: '||l_proc, 5);
2371 --
2372 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2373 if (p_budget_version_id is not null) then
2374 l_budget_version_id := p_budget_version_id;
2375 l_currency_code := get_currency_cd(p_budget_version_id);
2376 proc1;
2377 else
2378 --
2379 l_business_group_id := hr_general.get_business_group_id;
2380 --
2381 OPEN csr_bdgt_positions;
2382 LOOP
2383 FETCH csr_bdgt_positions INTO l_budget_version_id,l_currency_code;
2384 EXIT WHEN csr_bdgt_positions%NOTFOUND;
2385 proc1;
2386 END LOOP;
2387 CLOSE csr_bdgt_positions;
2388 end if;
2389 hr_utility.set_location('Leaving:'||l_proc, 1000);
2390
2391 RETURN l_total_amt;
2392
2393 EXCEPTION
2394 WHEN OTHERS THEN
2395 l_total_amt := 0;
2396 RETURN l_total_amt;
2397 END get_posn_elmnt_actual_cmmtmnts;
2398
2399 --
2400 -- JOB
2401 --
2402 ---------------------------------------------------------------------------------------------------------
2403 FUNCTION get_job_elmnt_actual_cmmtmnts
2404 (
2405 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2406 p_job_id IN per_jobs.job_id%TYPE,
2407 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2408 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2409 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2410 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2411 p_value_type IN varchar2 DEFAULT 'T',
2412 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2413 )
2414 RETURN NUMBER IS
2415
2416 /*
2417 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt as we did not want any errors
2418 to be returned by the original function.
2419 This function will return the actual or commitment for a job. If the budget version is
2420 specified then it would return the actual or commitment for that budget version. If no budget version is
2421 specified the it would return the actual or commitment for all the budget versions where the job is
2422 budgeted between the start date and end date.
2423 For Actuals : Value Type is 'A'
2424 For commitments : Value Type is 'C'
2425 Default for value type is 'T' which means both actual and commitments us returned
2426 */
2427
2428 l_proc varchar2(72) := g_package||'get_job_elmnt_actual_cmmtmnts';
2429 l_total_amt NUMBER := 0;
2430 l_curr_ver_tot NUMBER := 0;
2431 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2432 l_currency_code fnd_currencies.currency_code%TYPE;
2433 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2434 l_business_group_id number;
2435
2436 CURSOR csr_bdgt_jobs IS
2437 SELECT DISTINCT
2438 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2439 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2440 FROM
2441 pqh_budgets bgt,
2442 pqh_budget_versions bvr,
2443 pqh_budget_details bdt,
2444 pqh_budget_periods bpr,
2445 pqh_budget_sets bst,
2446 pqh_budget_elements bel
2447 WHERE
2448 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2449 AND bgt.budget_id = bvr.budget_id
2450 AND bvr.budget_version_id = bdt.budget_version_id
2451 AND bdt.budget_detail_id = bpr.budget_detail_id
2452 AND bpr.budget_period_id = bst.budget_period_id
2453 AND bst.budget_set_id = bel.budget_set_id
2454 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2455 bgt.budget_unit2_id = p_unit_of_measure_id or
2456 bgt.budget_unit3_id = p_unit_of_measure_id)
2457 AND bdt.job_id = p_job_id
2458 AND bel.element_type_id = p_element_type_id
2459 AND bgt.budgeted_entity_cd = 'JOB'
2460 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2461 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2462 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2463 procedure proc1 is
2464 BEGIN
2465 if (( l_budget_measurement_type <> 'MONEY') OR
2466 ( l_budget_measurement_type = 'MONEY' and
2467 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2468 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
2469 (
2470 p_budget_version_id => l_budget_version_id,
2471 p_budgeted_entity_cd => 'JOB',
2472 p_entity_id => p_job_id,
2473 p_element_type_id => p_element_type_id,
2474 p_start_date => p_start_date,
2475 p_end_date => p_end_date,
2476 p_unit_of_measure_id => p_unit_of_measure_id,
2477 p_value_type => p_value_type
2478 );
2479
2480 l_total_amt := l_total_amt + l_curr_ver_tot;
2481 end if;
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 l_curr_ver_tot := 0;
2485 l_total_amt := l_total_amt + l_curr_ver_tot;
2486 END;
2487 BEGIN
2488 hr_utility.set_location('Entering: '||l_proc, 5);
2489 --
2490 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2491 if (p_budget_version_id is not null) then
2492 l_budget_version_id := p_budget_version_id;
2493 l_currency_code := get_currency_cd(p_budget_version_id);
2494 proc1;
2495 else
2496 --
2497 l_business_group_id := hr_general.get_business_group_id;
2498 --
2499 OPEN csr_bdgt_jobs;
2500 LOOP
2501 FETCH csr_bdgt_jobs INTO l_budget_version_id,l_currency_code;
2502 EXIT WHEN csr_bdgt_jobs%NOTFOUND;
2503 proc1;
2504 END LOOP;
2505 CLOSE csr_bdgt_jobs;
2506 end if;
2507 hr_utility.set_location('Leaving:'||l_proc, 1000);
2508
2509 RETURN l_total_amt;
2510
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 l_total_amt := 0;
2514 RETURN l_total_amt;
2515 END get_job_elmnt_actual_cmmtmnts;
2516 --
2517 -- GRADE
2518 --
2519 ---------------------------------------------------------------------------------------------------------
2520 FUNCTION get_grde_elmnt_actual_cmmtmnts
2521 (
2522 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2523 p_grade_id IN per_grades.grade_id%TYPE,
2524 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2525 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2526 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2527 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2528 p_value_type IN varchar2 DEFAULT 'T',
2529 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2530 )
2531 RETURN NUMBER IS
2532
2533 /*
2534 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt as we did not want any errors
2535 to be returned by the original function.
2536 This function will return the actual or commitment for a grade. If the budget version is
2537 specified then it would return the actual or commitment for that budget version. If no budget version is
2538 specified the it would return the actual or commitment for all the budget versions where the grade is
2539 budgeted between the start date and end date.
2540 For Actuals : Value Type is 'A'
2541 For commitments : Value Type is 'C'
2542 Default for value type is 'T' which means both actual and commitments us returned
2543 */
2544
2545 l_proc varchar2(72) := g_package||'get_grde_elmnt_actual_cmmtmnts';
2546 l_total_amt NUMBER := 0;
2547 l_curr_ver_tot NUMBER := 0;
2548 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2549 l_currency_code fnd_currencies.currency_code%TYPE;
2550 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2551 l_business_group_id number;
2552
2553 CURSOR csr_bdgt_grades IS
2554 SELECT DISTINCT
2555 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2556 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2557 FROM
2558 pqh_budgets bgt,
2559 pqh_budget_versions bvr,
2560 pqh_budget_details bdt,
2561 pqh_budget_periods bpr,
2562 pqh_budget_sets bst,
2563 pqh_budget_elements bel
2564 WHERE
2565 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2566 AND bgt.budget_id = bvr.budget_id
2567 AND bvr.budget_version_id = bdt.budget_version_id
2568 AND bdt.budget_detail_id = bpr.budget_detail_id
2569 AND bpr.budget_period_id = bst.budget_period_id
2570 AND bst.budget_set_id = bel.budget_set_id
2571 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2572 bgt.budget_unit2_id = p_unit_of_measure_id or
2573 bgt.budget_unit3_id = p_unit_of_measure_id)
2574 AND bdt.grade_id = p_grade_id
2575 AND bel.element_type_id = p_element_type_id
2576 AND bgt.budgeted_entity_cd = 'GRADE'
2577 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2578 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2579 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2580 procedure proc1 is
2581 BEGIN
2582 if (( l_budget_measurement_type <> 'MONEY') OR
2583 ( l_budget_measurement_type = 'MONEY' and
2584 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2585 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
2586 (
2587 p_budget_version_id => l_budget_version_id,
2588 p_budgeted_entity_cd => 'GRADE',
2589 p_entity_id => p_grade_id,
2590 p_element_type_id => p_element_type_id,
2591 p_start_date => p_start_date,
2592 p_end_date => p_end_date,
2593 p_unit_of_measure_id => p_unit_of_measure_id,
2594 p_value_type => p_value_type
2595 );
2596
2597 l_total_amt := l_total_amt + l_curr_ver_tot;
2598 end if;
2599 EXCEPTION
2600 WHEN OTHERS THEN
2601 l_curr_ver_tot := 0;
2602 l_total_amt := l_total_amt + l_curr_ver_tot;
2603 END;
2604 BEGIN
2605 hr_utility.set_location('Entering: '||l_proc, 5);
2606 --
2607 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2608 if (p_budget_version_id is not null) then
2609 l_budget_version_id := p_budget_version_id;
2610 l_currency_code := get_currency_cd(p_budget_version_id);
2611 proc1;
2612 else
2613 --
2614 l_business_group_id := hr_general.get_business_group_id;
2615 --
2616 OPEN csr_bdgt_grades;
2617 LOOP
2618 FETCH csr_bdgt_grades INTO l_budget_version_id,l_currency_code;
2619 EXIT WHEN csr_bdgt_grades%NOTFOUND;
2620 proc1;
2621 END LOOP;
2622 CLOSE csr_bdgt_grades;
2623 end if;
2624 hr_utility.set_location('Leaving:'||l_proc, 1000);
2625 RETURN l_total_amt;
2626
2627 EXCEPTION
2628 WHEN OTHERS THEN
2629 l_total_amt := 0;
2630 RETURN l_total_amt;
2631 END get_grde_elmnt_actual_cmmtmnts;
2632 --
2633 -- ORGANIZATION
2634 --
2635 ---------------------------------------------------------------------------------------------------------
2636 FUNCTION get_orgn_elmnt_actual_cmmtmnts
2637 (
2638 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2639 p_organization_id IN hr_organization_units.organization_id%TYPE,
2640 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2641 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2642 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2643 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2644 p_value_type IN varchar2 DEFAULT 'T',
2645 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2646 )
2647 RETURN NUMBER IS
2648
2649 /*
2650 This function is a wrapper on pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt as we did not want any errors
2651 to be returned by the original function.
2652 This function will return the actual or commitment for a organization. If the budget version is
2653 specified then it would return the actual or commitment for that budget version. If no budget version is
2654 specified the it would return the actual or commitment for all the budget versions where the organization is
2655 budgeted between the start date and end date.
2656 For Actuals : Value Type is 'A'
2657 For commitments : Value Type is 'C'
2658 Default for value type is 'T' which means both actual and commitments us returned
2659 */
2660
2661 l_proc varchar2(72) := g_package||'get_orgn_elmnt_actual_cmmtmnts';
2662 l_total_amt NUMBER := 0;
2663 l_curr_ver_tot NUMBER := 0;
2664 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2665 l_currency_code fnd_currencies.currency_code%TYPE;
2666 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2667 l_business_group_id number;
2668
2669 CURSOR csr_bdgt_orgs IS
2670 SELECT DISTINCT
2671 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2672 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2673 FROM
2674 pqh_budgets bgt,
2675 pqh_budget_versions bvr,
2676 pqh_budget_details bdt,
2677 pqh_budget_periods bpr,
2678 pqh_budget_sets bst,
2679 pqh_budget_elements bel
2680 WHERE
2681 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2682 AND bgt.budget_id = bvr.budget_id
2683 AND bvr.budget_version_id = bdt.budget_version_id
2684 AND bdt.budget_detail_id = bpr.budget_detail_id
2685 AND bpr.budget_period_id = bst.budget_period_id
2686 AND bst.budget_set_id = bel.budget_set_id
2687 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2688 bgt.budget_unit2_id = p_unit_of_measure_id or
2689 bgt.budget_unit3_id = p_unit_of_measure_id)
2690 AND bdt.organization_id = p_organization_id
2691 AND bel.element_type_id = p_element_type_id
2692 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
2693 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2694 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2695 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2696
2697 procedure proc1 is
2698 BEGIN
2699 if (( l_budget_measurement_type <> 'MONEY') OR
2700 ( l_budget_measurement_type = 'MONEY' and
2701 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2702 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
2703 (
2704 p_budget_version_id => l_budget_version_id,
2705 p_budgeted_entity_cd => 'ORGANIZATION',
2706 p_entity_id => p_organization_id,
2707 p_element_type_id => p_element_type_id,
2708 p_start_date => p_start_date,
2709 p_end_date => p_end_date,
2710 p_unit_of_measure_id => p_unit_of_measure_id,
2711 p_value_type => p_value_type
2712 );
2713
2714 l_total_amt := l_total_amt + l_curr_ver_tot;
2715 end if;
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 l_curr_ver_tot := 0;
2719 l_total_amt := l_total_amt + l_curr_ver_tot;
2720 END;
2721 BEGIN
2722 hr_utility.set_location('Entering: '||l_proc, 5);
2723 --
2724 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2725 if (p_budget_version_id is not null) then
2726 l_budget_version_id := p_budget_version_id;
2727 l_currency_code := get_currency_cd(p_budget_version_id);
2728 proc1;
2729 else
2730 --
2731 l_business_group_id := hr_general.get_business_group_id;
2732 --
2733 OPEN csr_bdgt_orgs;
2734 LOOP
2735 FETCH csr_bdgt_orgs INTO l_budget_version_id,l_currency_code;
2736 EXIT WHEN csr_bdgt_orgs%NOTFOUND;
2737 proc1;
2738 END LOOP;
2739 CLOSE csr_bdgt_orgs;
2740 end if;
2741 hr_utility.set_location('Leaving:'||l_proc, 1000);
2742 RETURN l_total_amt;
2743
2744 EXCEPTION
2745 WHEN OTHERS THEN
2746 l_total_amt := 0;
2747 RETURN l_total_amt;
2748 END get_orgn_elmnt_actual_cmmtmnts;
2749 --
2750 --
2751
2752 ---------------------------------------------------------------------------------------------------------
2753 FUNCTION get_posn_bset_actual_cmmtmnts
2754 (
2755 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2756 p_position_id IN per_positions.position_id%TYPE,
2757 p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
2758 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2759 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2760 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2761 p_value_type IN varchar2 DEFAULT 'T'
2762 )
2763 RETURN NUMBER IS
2764
2765 l_proc varchar2(72) := g_package||'get_posn_bset_actual_cmmtmnts';
2766 l_total_amt NUMBER := 0;
2767 l_curr_ver_tot NUMBER := 0;
2768 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2769 l_element_type_id pqh_budget_elements.element_type_id%TYPE;
2770 l_business_group_id number;
2771
2772 CURSOR csr_bdgt_positions IS
2773 SELECT DISTINCT
2774 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID
2775 FROM
2776 pqh_budgets bgt,
2777 pqh_budget_versions bvr,
2778 pqh_budget_details bdt,
2779 pqh_budget_periods bpr,
2780 pqh_budget_sets bst
2781 WHERE
2782 bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2783 AND bgt.budget_id = bvr.budget_id
2784 AND bvr.budget_version_id = bdt.budget_version_id
2785 AND bdt.budget_detail_id = bpr.budget_detail_id
2786 AND bpr.budget_period_id = bst.budget_period_id
2787 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2788 bgt.budget_unit2_id = p_unit_of_measure_id or
2789 bgt.budget_unit3_id = p_unit_of_measure_id)
2790 AND bdt.position_id = p_position_id
2791 AND bst.dflt_budget_set_id = p_budget_set_id
2792 AND bgt.budgeted_entity_cd = 'POSITION'
2793 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2794 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2795 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2796
2797 CURSOR csr_bgt_elements IS
2798 SELECT element_type_id
2799 FROM pqh_dflt_budget_elements
2800 WHERE dflt_budget_set_id = p_budget_set_id;
2801
2802 procedure proc1 is
2803 BEGIN
2804 l_curr_ver_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
2805 (
2806 p_budget_version_id => l_budget_version_id,
2807 p_position_id => p_position_id,
2808 p_element_type_id => l_element_type_id,
2809 p_start_date => p_start_date,
2810 p_end_date => p_end_date,
2811 p_unit_of_measure_id => p_unit_of_measure_id,
2812 p_value_type => p_value_type
2813 );
2814
2815 l_total_amt := l_total_amt + l_curr_ver_tot;
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818 l_curr_ver_tot := 0;
2819 l_total_amt := l_total_amt + l_curr_ver_tot;
2820 END;
2821 BEGIN
2822 hr_utility.set_location('Entering: '||l_proc, 5);
2823 if (p_budget_version_id is not null) then
2824 l_budget_version_id := p_budget_version_id;
2825 proc1;
2826 else
2827 --
2828 l_business_group_id := hr_general.get_business_group_id;
2829 --
2830 OPEN csr_bdgt_positions;
2831 LOOP
2832 FETCH csr_bdgt_positions INTO l_budget_version_id;
2833 EXIT WHEN csr_bdgt_positions%NOTFOUND;
2834 --
2835 --
2836 OPEN csr_bgt_elements;
2837 LOOP
2838 FETCH csr_bgt_elements INTO l_element_type_id;
2839 EXIT WHEN csr_bgt_elements%NOTFOUND;
2840 proc1;
2841 END LOOP; -- bgt_elements
2842 CLOSE csr_bgt_elements;
2843 --
2844 --
2845 END LOOP;
2846 CLOSE csr_bdgt_positions;
2847 end if;
2848 hr_utility.set_location('Leaving:'||l_proc, 1000);
2849 RETURN l_total_amt;
2850
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 l_total_amt := 0;
2854 RETURN l_total_amt;
2855 END get_posn_bset_actual_cmmtmnts;
2856 ---------------------------------------------------------------------------------------------------------
2857 FUNCTION get_org_posn_budget_amt
2858 (
2859 p_organization_id IN pqh_budget_details.organization_id%TYPE,
2860 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2861 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2862 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2863 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2864 )
2865 RETURN NUMBER IS
2866 /*
2867 */
2868 l_proc varchar2(72) := g_package||'get_org_posn_budget_amt';
2869 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2870 l_position_id pqh_budget_details.position_id%TYPE;
2871 l_total_amt NUMBER := 0;
2872 l_curr_posn_tot NUMBER := 0;
2873
2874 l_currency_code fnd_currencies.currency_code%TYPE;
2875 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2876 l_business_group_id number;
2877
2878 CURSOR csr_bdgt_positions(p_business_group_id number) IS
2879 SELECT DISTINCT
2880 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2881 bdt.position_id position_id,
2882 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2883 FROM
2884 pqh_budgets bgt,
2885 pqh_budget_versions bvr,
2886 pqh_budget_details bdt
2887 WHERE
2888 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
2889 AND bgt.budget_id = bvr.budget_id
2890 AND bvr.budget_version_id = bdt.budget_version_id
2891 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2892 bgt.budget_unit2_id = p_unit_of_measure_id or
2893 bgt.budget_unit3_id = p_unit_of_measure_id)
2894 AND bdt.organization_id = p_organization_id
2895 AND bgt.budgeted_entity_cd = 'POSITION'
2896 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2897 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2898 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2899 procedure proc1 is
2900 BEGIN
2901 if (( l_budget_measurement_type <> 'MONEY') OR
2902 ( l_budget_measurement_type = 'MONEY' and
2903 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2904 l_curr_posn_tot := get_posn_bdgt
2905 (
2906 p_budget_version_id => l_budget_version_id,
2907 p_start_date => p_start_date,
2908 p_end_date => p_end_date,
2909 p_position_id => l_position_id,
2910 p_unit_of_measure_id => p_unit_of_measure_id
2911 );
2912
2913 l_total_amt := l_total_amt + l_curr_posn_tot;
2914 end if;
2915
2916 EXCEPTION
2917 WHEN OTHERS THEN
2918 l_curr_posn_tot := 0;
2919 l_total_amt := l_total_amt + l_curr_posn_tot;
2920 END;
2921 BEGIN
2922 hr_utility.set_location('Entering: '||l_proc, 5);
2923
2924 --
2925 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
2926 l_business_group_id := hr_general.get_business_group_id;
2927
2928 OPEN csr_bdgt_positions(l_business_group_id);
2929 LOOP
2930 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
2931 EXIT WHEN csr_bdgt_positions%NOTFOUND;
2932 proc1;
2933 END LOOP;
2934 CLOSE csr_bdgt_positions;
2935 hr_utility.set_location('Leaving:'||l_proc, 1000);
2936 RETURN l_total_amt;
2937
2938 EXCEPTION
2939 WHEN OTHERS THEN
2940 l_total_amt := 0;
2941 RETURN l_total_amt;
2942 END get_org_posn_budget_amt;
2943
2944 ---------------------------------------------------------------------------------------------------------
2945 FUNCTION get_org_posn_actual_cmmtmnts
2946 (
2947 p_organization_id IN pqh_budget_details.organization_id%TYPE,
2948 p_start_date IN pqh_budgets.budget_start_date%TYPE,
2949 p_end_date IN pqh_budgets.budget_end_date%TYPE,
2950 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
2951 p_value_type IN varchar2 DEFAULT 'T',
2952 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
2953 )
2954 RETURN NUMBER IS
2955 /*
2956 */
2957
2958 l_proc varchar2(72) := g_package||'get_org_posn_actual_cmmtmnts';
2959 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2960 l_position_id pqh_budget_details.position_id%TYPE;
2961 l_total_amt NUMBER := 0;
2962 l_curr_posn_tot NUMBER := 0;
2963
2964 l_currency_code fnd_currencies.currency_code%TYPE;
2965 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2966 l_business_group_id number;
2967
2968 CURSOR csr_bdgt_positions(p_business_group_id number) IS
2969 SELECT DISTINCT
2970 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2971 bdt.position_id position_id,
2972 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2973 FROM
2974 pqh_budgets bgt,
2975 pqh_budget_versions bvr,
2976 pqh_budget_details bdt
2977 WHERE
2978 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
2979 AND bgt.budget_id = bvr.budget_id
2980 AND bvr.budget_version_id = bdt.budget_version_id
2981 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
2982 bgt.budget_unit2_id = p_unit_of_measure_id or
2983 bgt.budget_unit3_id = p_unit_of_measure_id)
2984 AND bdt.organization_id = p_organization_id
2985 AND bgt.budgeted_entity_cd = 'POSITION'
2986 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
2987 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
2988 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
2989 procedure proc1 is
2990 BEGIN
2991 if (( l_budget_measurement_type <> 'MONEY') OR
2992 ( l_budget_measurement_type = 'MONEY' and
2993 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
2994 l_curr_posn_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
2995 (
2996 p_budget_version_id => l_budget_version_id,
2997 p_position_id => l_position_id,
2998 p_start_date => p_start_date,
2999 p_end_date => p_end_date,
3000 p_unit_of_measure_id => p_unit_of_measure_id,
3001 p_value_type => p_value_type
3002 );
3003
3004 l_total_amt := l_total_amt + l_curr_posn_tot;
3005 end if;
3006
3007 EXCEPTION
3008 WHEN OTHERS THEN
3009 l_curr_posn_tot := 0;
3010 l_total_amt := l_total_amt + l_curr_posn_tot;
3011 END;
3012 BEGIN
3013 hr_utility.set_location('Entering: '||l_proc, 5);
3014
3015 --
3016 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3017 l_business_group_id := hr_general.get_business_group_id;
3018
3019 OPEN csr_bdgt_positions(l_business_group_id);
3020 LOOP
3021 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
3022 EXIT WHEN csr_bdgt_positions%NOTFOUND;
3023 proc1;
3024 END LOOP;
3025 CLOSE csr_bdgt_positions;
3026 hr_utility.set_location('Leaving:'||l_proc, 1000);
3027 RETURN l_total_amt;
3028
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 l_total_amt := 0;
3032 RETURN l_total_amt;
3033 END get_org_posn_actual_cmmtmnts;
3034
3035 ---------------------------------------------------------------------------------------------------------
3036
3037 FUNCTION get_bgrp_posn_budget_amt
3038 (
3039 p_business_group_id IN pqh_budgets.business_group_id%TYPE,
3040 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3041 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3042 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3043 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3044 )
3045 RETURN NUMBER IS
3046
3047 l_proc varchar2(72) := g_package||'get_bgrp_posn_budget_amt';
3048 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3049 l_position_id pqh_budget_details.position_id%TYPE;
3050 l_total_amt NUMBER := 0;
3051 l_curr_posn_tot NUMBER := 0;
3052
3053 l_currency_code fnd_currencies.currency_code%TYPE;
3054 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3055
3056 CURSOR csr_bdgt_positions IS
3057 SELECT DISTINCT
3058 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3059 bdt.position_id position_id,
3060 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3061 FROM
3062 pqh_budgets bgt,
3063 pqh_budget_versions bvr,
3064 pqh_budget_details bdt
3065 WHERE bgt.business_group_id = p_business_group_id
3066 AND bgt.budget_id = bvr.budget_id
3067 AND bvr.budget_version_id = bdt.budget_version_id
3068 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3069 bgt.budget_unit2_id = p_unit_of_measure_id or
3070 bgt.budget_unit3_id = p_unit_of_measure_id)
3071 AND bgt.budgeted_entity_cd = 'POSITION'
3072 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3073 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3074 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3075
3076 BEGIN
3077 hr_utility.set_location('Entering: '||l_proc, 5);
3078 --
3079 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3080
3081
3082 OPEN csr_bdgt_positions;
3083 LOOP
3084 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
3085 EXIT WHEN csr_bdgt_positions%NOTFOUND;
3086
3087 BEGIN
3088 if (( l_budget_measurement_type <> 'MONEY') OR
3089 ( l_budget_measurement_type = 'MONEY' and
3090 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3091 l_curr_posn_tot := get_posn_bdgt
3092 (
3093 p_budget_version_id => l_budget_version_id,
3094 p_start_date => p_start_date,
3095 p_end_date => p_end_date,
3096 p_position_id => l_position_id,
3097 p_unit_of_measure_id => p_unit_of_measure_id
3098 );
3099
3100 l_total_amt := l_total_amt + l_curr_posn_tot;
3101 end if;
3102
3103 EXCEPTION
3104 WHEN OTHERS THEN
3105 l_curr_posn_tot := 0;
3106 END;
3107 END LOOP;
3108 CLOSE csr_bdgt_positions;
3109
3110 hr_utility.set_location('Leaving:'||l_proc, 1000);
3111
3112 RETURN l_total_amt;
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 RETURN 0;
3117 END get_bgrp_posn_budget_amt;
3118 ---------------------------------------------------------------------------------------------------------
3119 FUNCTION get_bgrp_posn_actual_cmmtmnts
3120 (
3121 p_business_group_id IN pqh_budgets.business_group_id%TYPE,
3122 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3123 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3124 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3125 p_value_type IN varchar2 DEFAULT 'T',
3126 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3127 )
3128 RETURN NUMBER IS
3129
3130 l_proc varchar2(72) := g_package||'get_bgrp_posn_actual_cmmtmnts';
3131 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3132 l_position_id pqh_budget_details.position_id%TYPE;
3133 l_total_amt NUMBER := 0;
3134 l_curr_posn_tot NUMBER := 0;
3135
3136 l_currency_code fnd_currencies.currency_code%TYPE;
3137 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3138
3139 CURSOR csr_bdgt_positions IS
3140 SELECT DISTINCT
3141 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3142 bdt.position_id position_id,
3143 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3144 FROM
3145 pqh_budgets bgt,
3146 pqh_budget_versions bvr,
3147 pqh_budget_details bdt
3148 WHERE
3149 bgt.business_group_id = p_business_group_id
3150 AND bgt.budget_id = bvr.budget_id
3151 AND bvr.budget_version_id = bdt.budget_version_id
3152 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3153 bgt.budget_unit2_id = p_unit_of_measure_id or
3154 bgt.budget_unit3_id = p_unit_of_measure_id)
3155 AND bgt.budgeted_entity_cd = 'POSITION'
3156 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3157 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3158 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3159
3160 BEGIN
3161 hr_utility.set_location('Entering: '||l_proc, 5);
3162 --
3163 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3164
3165
3166 OPEN csr_bdgt_positions;
3167 LOOP
3168 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
3169 EXIT WHEN csr_bdgt_positions%NOTFOUND;
3170
3171 BEGIN
3172 if (( l_budget_measurement_type <> 'MONEY') OR
3173 ( l_budget_measurement_type = 'MONEY' and
3174 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3175 l_curr_posn_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_pos_actual_and_cmmtmnt
3176 (
3177 p_budget_version_id => l_budget_version_id,
3178 p_position_id => l_position_id,
3179 p_start_date => p_start_date,
3180 p_end_date => p_end_date,
3181 p_unit_of_measure_id => p_unit_of_measure_id,
3182 p_value_type => p_value_type
3183 );
3184 l_total_amt := l_total_amt + l_curr_posn_tot;
3185 end if;
3186
3187 EXCEPTION
3188 WHEN OTHERS THEN
3189 l_curr_posn_tot := 0;
3190 END;
3191
3192 END LOOP;
3193 CLOSE csr_bdgt_positions;
3194
3195 hr_utility.set_location('Leaving:'||l_proc, 1000);
3196
3197 RETURN l_total_amt;
3198
3199 EXCEPTION
3200 WHEN OTHERS THEN
3201 RETURN 0;
3202 END get_bgrp_posn_actual_cmmtmnts;
3203 --
3204 --
3205 ---------------------------------------------------------------------------------------------------------
3206 FUNCTION get_elem_posn_budget_amt
3207 (
3208 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3209 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3210 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3211 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3212 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3213 )
3214 RETURN NUMBER IS
3215
3216 l_proc varchar2(72) := g_package||'get_elem_posn_budget_amt';
3217 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3218 l_position_id pqh_budget_details.position_id%TYPE;
3219 l_total_amt NUMBER := 0;
3220 l_curr_posn_tot NUMBER := 0;
3221
3222 l_currency_code fnd_currencies.currency_code%TYPE;
3223 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3224 l_business_group_id number;
3225
3226 CURSOR csr_bdgt_positions(p_business_group_id number) IS
3227 SELECT DISTINCT
3228 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3229 bdt.position_id,
3230 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3231 FROM
3232 pqh_budgets bgt,
3233 pqh_budget_versions bvr,
3234 pqh_budget_details bdt
3235 WHERE
3236 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3237 AND bgt.budget_id = bvr.budget_id
3238 AND bvr.budget_version_id = bdt.budget_version_id
3239 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3240 bgt.budget_unit2_id = p_unit_of_measure_id or
3241 bgt.budget_unit3_id = p_unit_of_measure_id)
3242 AND bgt.budgeted_entity_cd = 'POSITION'
3243 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3244 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3245 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3246
3247 BEGIN
3248 hr_utility.set_location('Entering: '||l_proc, 5);
3249 --
3250 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3251 l_business_group_id := hr_general.get_business_group_id;
3252
3253 OPEN csr_bdgt_positions(l_business_group_id);
3254 LOOP
3255 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
3256 EXIT WHEN csr_bdgt_positions%NOTFOUND;
3257
3258 --
3259 --
3260 BEGIN
3261 if (( l_budget_measurement_type <> 'MONEY') OR
3262 ( l_budget_measurement_type = 'MONEY' and
3263 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3264 l_curr_posn_tot := get_posn_element_bdgt_amt
3265 (
3266 p_budget_version_id => l_budget_version_id,
3267 p_position_id => l_position_id,
3268 p_element_type_id => p_element_type_id,
3269 p_start_date => p_start_date,
3270 p_end_date => p_end_date,
3271 p_unit_of_measure_id => p_unit_of_measure_id,
3272 p_currency_code => p_currency_code
3273 );
3274
3275 l_total_amt := l_total_amt + l_curr_posn_tot;
3276 end if;
3277 EXCEPTION
3278 WHEN OTHERS THEN
3279 l_curr_posn_tot := 0;
3280 l_total_amt := l_total_amt + l_curr_posn_tot;
3281 END;
3282 --
3283 --
3284
3285 END LOOP;
3286 CLOSE csr_bdgt_positions;
3287
3288
3289 hr_utility.set_location('Leaving:'||l_proc, 1000);
3290
3291 RETURN l_total_amt;
3292
3293 EXCEPTION
3294 WHEN OTHERS THEN
3295 l_total_amt := 0;
3296 RETURN l_total_amt;
3297 END get_elem_posn_budget_amt;
3298
3299 --
3300 -- JOB
3301 --
3302 FUNCTION get_elem_job_budget_amt
3303 (
3304 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3305 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3306 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3307 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3308 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3309 )
3310 RETURN NUMBER IS
3311
3312 l_proc varchar2(72) := g_package||'get_elem_job_budget_amt';
3313 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3314 l_job_id pqh_budget_details.job_id%TYPE;
3315 l_total_amt NUMBER := 0;
3316 l_curr_posn_tot NUMBER := 0;
3317
3318 l_currency_code fnd_currencies.currency_code%TYPE;
3319 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3320 l_business_group_id number;
3321
3322 CURSOR csr_bdgt_jobs(p_business_group_id number) IS
3323 SELECT DISTINCT
3324 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3325 bdt.job_id,
3326 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3327 FROM
3328 pqh_budgets bgt,
3329 pqh_budget_versions bvr,
3330 pqh_budget_details bdt
3331 WHERE
3332 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3333 AND bgt.budget_id = bvr.budget_id
3334 AND bvr.budget_version_id = bdt.budget_version_id
3335 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3336 bgt.budget_unit2_id = p_unit_of_measure_id or
3337 bgt.budget_unit3_id = p_unit_of_measure_id)
3338 AND bgt.budgeted_entity_cd = 'JOB'
3339 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3340 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3341 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3342
3343 BEGIN
3344 hr_utility.set_location('Entering: '||l_proc, 5);
3345 --
3346 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3347 l_business_group_id := hr_general.get_business_group_id;
3348
3349 OPEN csr_bdgt_jobs(l_business_group_id);
3350 LOOP
3351 FETCH csr_bdgt_jobs INTO l_budget_version_id, l_job_id,l_currency_code;
3352 EXIT WHEN csr_bdgt_jobs%NOTFOUND;
3353
3354 --
3355 --
3356 BEGIN
3357 if (( l_budget_measurement_type <> 'MONEY') OR
3358 ( l_budget_measurement_type = 'MONEY' and
3359 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3360 l_curr_posn_tot := get_job_element_bdgt_amt
3361 (
3362 p_budget_version_id => l_budget_version_id,
3363 p_job_id => l_job_id,
3364 p_element_type_id => p_element_type_id,
3365 p_start_date => p_start_date,
3366 p_end_date => p_end_date,
3367 p_unit_of_measure_id => p_unit_of_measure_id,
3368 p_currency_code => p_currency_code
3369 );
3370
3371 l_total_amt := l_total_amt + l_curr_posn_tot;
3372 end if;
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375 l_curr_posn_tot := 0;
3376 l_total_amt := l_total_amt + l_curr_posn_tot;
3377 END;
3378 --
3379 --
3380
3381 END LOOP;
3382 CLOSE csr_bdgt_jobs;
3383
3384
3385 hr_utility.set_location('Leaving:'||l_proc, 1000);
3386
3387 RETURN l_total_amt;
3388
3389 EXCEPTION
3390 WHEN OTHERS THEN
3391 l_total_amt := 0;
3392 RETURN l_total_amt;
3393 END get_elem_job_budget_amt;
3394
3395 ---------------------------------------------------------------------------------------------------------
3396 --
3397 -- GRADE
3398 --
3399 FUNCTION get_elem_grde_budget_amt
3400 (
3401 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3402 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3403 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3404 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3405 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3406 )
3407 RETURN NUMBER IS
3408
3409 l_proc varchar2(72) := g_package||'get_elem_grd_budget_amt';
3410 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3411 l_grade_id pqh_budget_details.grade_id%TYPE;
3412 l_total_amt NUMBER := 0;
3413 l_curr_posn_tot NUMBER := 0;
3414
3415 l_currency_code fnd_currencies.currency_code%TYPE;
3416 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3417 l_business_group_id number;
3418 CURSOR csr_bdgt_grades(p_business_group_id number) IS
3419 SELECT DISTINCT
3420 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3421 bdt.grade_id,
3422 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3423 FROM
3424 pqh_budgets bgt,
3425 pqh_budget_versions bvr,
3426 pqh_budget_details bdt
3427 WHERE
3428 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3429 AND bgt.budget_id = bvr.budget_id
3430 AND bvr.budget_version_id = bdt.budget_version_id
3431 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3432 bgt.budget_unit2_id = p_unit_of_measure_id or
3433 bgt.budget_unit3_id = p_unit_of_measure_id)
3434 AND bgt.budgeted_entity_cd = 'GRADE'
3435 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3436 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3437 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3438
3439 BEGIN
3440 hr_utility.set_location('Entering: '||l_proc, 5);
3441 --
3442 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3443 l_business_group_id := hr_general.get_business_group_id;
3444
3445 OPEN csr_bdgt_grades(l_business_group_id);
3446 LOOP
3447 FETCH csr_bdgt_grades INTO l_budget_version_id, l_grade_id,l_currency_code;
3448 EXIT WHEN csr_bdgt_grades%NOTFOUND;
3449
3450 --
3451 --
3452 BEGIN
3453 if (( l_budget_measurement_type <> 'MONEY') OR
3454 ( l_budget_measurement_type = 'MONEY' and
3455 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3456 l_curr_posn_tot := get_grde_element_bdgt_amt
3457 (
3458 p_budget_version_id => l_budget_version_id,
3459 p_grade_id => l_grade_id,
3460 p_element_type_id => p_element_type_id,
3461 p_start_date => p_start_date,
3462 p_end_date => p_end_date,
3463 p_unit_of_measure_id => p_unit_of_measure_id,
3464 p_currency_code => p_currency_code
3465 );
3466
3467 l_total_amt := l_total_amt + l_curr_posn_tot;
3468 end if;
3469 EXCEPTION
3470 WHEN OTHERS THEN
3471 l_curr_posn_tot := 0;
3472 l_total_amt := l_total_amt + l_curr_posn_tot;
3473 END;
3474 --
3475 --
3476
3477 END LOOP;
3478 CLOSE csr_bdgt_grades;
3479
3480
3481 hr_utility.set_location('Leaving:'||l_proc, 1000);
3482
3483 RETURN l_total_amt;
3484
3485 EXCEPTION
3486 WHEN OTHERS THEN
3487 l_total_amt := 0;
3488 RETURN l_total_amt;
3489 END get_elem_grde_budget_amt;
3490 ---------------------------
3491 --
3492 -- ORGANIZATION
3493 --
3494 FUNCTION get_elem_orgn_budget_amt
3495 (
3496 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3497 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3498 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3499 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3500 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3501 )
3502 RETURN NUMBER IS
3503
3504 l_proc varchar2(72) := g_package||'get_elem_org_budget_amt';
3505 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3506 l_organization_id pqh_budget_details.organization_id%TYPE;
3507 l_total_amt NUMBER := 0;
3508 l_curr_posn_tot NUMBER := 0;
3509
3510 l_currency_code fnd_currencies.currency_code%TYPE;
3511 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3512 l_business_group_id number;
3513
3514 CURSOR csr_bdgt_orgs(p_business_group_id number) IS
3515 SELECT DISTINCT
3516 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3517 bdt.organization_id,
3518 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3519 FROM
3520 pqh_budgets bgt,
3521 pqh_budget_versions bvr,
3522 pqh_budget_details bdt
3523 WHERE
3524 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3525 AND bgt.budget_id = bvr.budget_id
3526 AND bvr.budget_version_id = bdt.budget_version_id
3527 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3528 bgt.budget_unit2_id = p_unit_of_measure_id or
3529 bgt.budget_unit3_id = p_unit_of_measure_id)
3530 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
3531 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3532 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3533 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3534
3535 BEGIN
3536 hr_utility.set_location('Entering: '||l_proc, 5);
3537 --
3538 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3539 l_business_group_id := hr_general.get_business_group_id;
3540
3541 OPEN csr_bdgt_orgs(l_business_group_id);
3542 LOOP
3543 FETCH csr_bdgt_orgs INTO l_budget_version_id, l_organization_id,l_currency_code;
3544 EXIT WHEN csr_bdgt_orgs%NOTFOUND;
3545
3546 --
3547 --
3548 BEGIN
3549 if (( l_budget_measurement_type <> 'MONEY') OR
3550 ( l_budget_measurement_type = 'MONEY' and
3551 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3552 l_curr_posn_tot := get_orgn_element_bdgt_amt
3553 (
3554 p_budget_version_id => l_budget_version_id,
3555 p_organization_id => l_organization_id,
3556 p_element_type_id => p_element_type_id,
3557 p_start_date => p_start_date,
3558 p_end_date => p_end_date,
3559 p_unit_of_measure_id => p_unit_of_measure_id,
3560 p_currency_code => p_currency_code
3561 );
3562
3563 l_total_amt := l_total_amt + l_curr_posn_tot;
3564 end if;
3565 EXCEPTION
3566 WHEN OTHERS THEN
3567 l_curr_posn_tot := 0;
3568 l_total_amt := l_total_amt + l_curr_posn_tot;
3569 END;
3570 --
3571 --
3572
3573 END LOOP;
3574 CLOSE csr_bdgt_orgs;
3575
3576
3577 hr_utility.set_location('Leaving:'||l_proc, 1000);
3578
3579 RETURN l_total_amt;
3580
3581 EXCEPTION
3582 WHEN OTHERS THEN
3583 l_total_amt := 0;
3584 RETURN l_total_amt;
3585 END get_elem_orgn_budget_amt;
3586 ----------------------------
3587
3588 ---------------------------------------------------------------------------------------------------------
3589 FUNCTION get_elem_posn_actual_cmmtmnts
3590 (
3591 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3592 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3593 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3594 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3595 p_value_type IN varchar2 DEFAULT 'T',
3596 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3597 )
3598 RETURN NUMBER IS
3599 /*
3600 */
3601
3602 l_proc varchar2(72) := g_package||'get_elem_posn_actual_cmmtmnts';
3603 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3604 l_position_id pqh_budget_details.position_id%TYPE;
3605 l_total_amt NUMBER := 0;
3606 l_curr_posn_tot NUMBER := 0;
3607
3608 l_currency_code fnd_currencies.currency_code%TYPE;
3609 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3610 l_business_group_id number;
3611
3612 CURSOR csr_bdgt_positions(p_business_group_id number) IS
3613 SELECT DISTINCT
3614 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3615 bdt.position_id,
3616 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3617 FROM
3618 pqh_budgets bgt,
3619 pqh_budget_versions bvr,
3620 pqh_budget_details bdt,
3621 pqh_budget_periods bpr,
3622 pqh_budget_sets bst,
3623 pqh_budget_elements bel
3624 WHERE
3625 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3626 AND bgt.budget_id = bvr.budget_id
3627 AND bvr.budget_version_id = bdt.budget_version_id
3628 AND bdt.budget_detail_id = bpr.budget_detail_id
3629 AND bpr.budget_period_id = bst.budget_period_id
3630 AND bst.budget_set_id = bel.budget_set_id
3631 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3632 bgt.budget_unit2_id = p_unit_of_measure_id or
3633 bgt.budget_unit3_id = p_unit_of_measure_id)
3634 AND bel.element_type_id = p_element_type_id
3635 AND bgt.budgeted_entity_cd = 'POSITION'
3636 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3637 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3638 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3639
3640 BEGIN
3641 hr_utility.set_location('Entering: '||l_proc, 5);
3642 --
3643 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3644 l_business_group_id := hr_general.get_business_group_id;
3645
3646 OPEN csr_bdgt_positions(l_business_group_id);
3647 LOOP
3648 FETCH csr_bdgt_positions INTO l_budget_version_id, l_position_id,l_currency_code;
3649 EXIT WHEN csr_bdgt_positions%NOTFOUND;
3650
3651 --
3652 --
3653 BEGIN
3654 if (( l_budget_measurement_type <> 'MONEY') OR
3655 ( l_budget_measurement_type = 'MONEY' and
3656 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3657 l_curr_posn_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
3658 (
3659 p_budget_version_id => l_budget_version_id,
3660 p_budgeted_entity_cd => 'POSITION',
3661 p_element_type_id => p_element_type_id, /* Bug Fix 2719170 */
3662 p_entity_id => l_position_id,
3663 p_start_date => p_start_date,
3664 p_end_date => p_end_date,
3665 p_unit_of_measure_id => p_unit_of_measure_id,
3666 p_value_type => p_value_type
3667 );
3668
3669 l_total_amt := l_total_amt + l_curr_posn_tot;
3670 end if;
3671 EXCEPTION
3672 WHEN OTHERS THEN
3673 l_curr_posn_tot := 0;
3674 l_total_amt := l_total_amt + l_curr_posn_tot;
3675 END;
3676 --
3677 --
3678
3679 END LOOP;
3680 CLOSE csr_bdgt_positions;
3681
3682
3683
3684 hr_utility.set_location('Leaving:'||l_proc, 1000);
3685
3686 RETURN l_total_amt;
3687
3688 EXCEPTION
3689 WHEN OTHERS THEN
3690 l_total_amt := 0;
3691 RETURN l_total_amt;
3692 END get_elem_posn_actual_cmmtmnts;
3693
3694 --
3695 -- JOB
3696 --
3697 FUNCTION get_elem_job_actual_cmmtmnts
3698 (
3699 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3700 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3701 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3702 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3703 p_value_type IN varchar2 DEFAULT 'T',
3704 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3705 )
3706 RETURN NUMBER IS
3707 /*
3708 */
3709
3710 l_proc varchar2(72) := g_package||'get_elem_job_actual_cmmtmnts';
3711 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3712 l_job_id pqh_budget_details.job_id%TYPE;
3713 l_total_amt NUMBER := 0;
3714 l_curr_job_tot NUMBER := 0;
3715
3716 l_currency_code fnd_currencies.currency_code%TYPE;
3717 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3718 l_business_group_id number;
3719
3720 CURSOR csr_bdgt_jobs(p_business_group_id number) IS
3721 SELECT DISTINCT
3722 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3723 bdt.job_id,
3724 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3725 FROM
3726 pqh_budgets bgt,
3727 pqh_budget_versions bvr,
3728 pqh_budget_details bdt,
3729 pqh_budget_periods bpr,
3730 pqh_budget_sets bst,
3731 pqh_budget_elements bel
3732 WHERE
3733 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3734 AND bgt.budget_id = bvr.budget_id
3735 AND bvr.budget_version_id = bdt.budget_version_id
3736 AND bdt.budget_detail_id = bpr.budget_detail_id
3737 AND bpr.budget_period_id = bst.budget_period_id
3738 AND bst.budget_set_id = bel.budget_set_id
3739 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3740 bgt.budget_unit2_id = p_unit_of_measure_id or
3741 bgt.budget_unit3_id = p_unit_of_measure_id)
3742 AND bel.element_type_id = p_element_type_id
3743 AND bgt.budgeted_entity_cd = 'JOB'
3744 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3745 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3746 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3747
3748 BEGIN
3749 hr_utility.set_location('Entering: '||l_proc, 5);
3750 --
3751 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3752 l_business_group_id := hr_general.get_business_group_id;
3753 OPEN csr_bdgt_jobs(l_business_group_id);
3754 LOOP
3755 FETCH csr_bdgt_jobs INTO l_budget_version_id, l_job_id,l_currency_code;
3756 EXIT WHEN csr_bdgt_jobs%NOTFOUND;
3757
3758 --
3759 --
3760 BEGIN
3761 if (( l_budget_measurement_type <> 'MONEY') OR
3762 ( l_budget_measurement_type = 'MONEY' and
3763 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3764 l_curr_job_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
3765 (
3766 p_budget_version_id => l_budget_version_id,
3767 p_budgeted_entity_cd => 'JOB',
3768 p_element_type_id => p_element_type_id, /* Bug Fix 2719170 */
3769 p_entity_id => l_job_id,
3770 p_start_date => p_start_date,
3771 p_end_date => p_end_date,
3772 p_unit_of_measure_id => p_unit_of_measure_id,
3773 p_value_type => p_value_type
3774 );
3775
3776 l_total_amt := l_total_amt + l_curr_job_tot;
3777 end if;
3778 EXCEPTION
3779 WHEN OTHERS THEN
3780 l_curr_job_tot := 0;
3781 l_total_amt := l_total_amt + l_curr_job_tot;
3782 END;
3783 --
3784 --
3785
3786 END LOOP;
3787 CLOSE csr_bdgt_jobs;
3788
3789
3790
3791 hr_utility.set_location('Leaving:'||l_proc, 1000);
3792
3793 RETURN l_total_amt;
3794
3795 EXCEPTION
3796 WHEN OTHERS THEN
3797 l_total_amt := 0;
3798 RETURN l_total_amt;
3799 END get_elem_job_actual_cmmtmnts;
3800
3801 --
3802 -- GRADE
3803 --
3804 FUNCTION get_elem_grde_actual_cmmtmnts
3805 (
3806 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3807 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3808 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3809 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3810 p_value_type IN varchar2 DEFAULT 'T',
3811 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3812 )
3813 RETURN NUMBER IS
3814 /*
3815 */
3816
3817 l_proc varchar2(72) := g_package||'get_elem_grde_actual_cmmtmnts';
3818 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3819 l_grade_id pqh_budget_details.grade_id%TYPE;
3820 l_total_amt NUMBER := 0;
3821 l_curr_grade_tot NUMBER := 0;
3822
3823 l_currency_code fnd_currencies.currency_code%TYPE;
3824 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3825 l_business_group_id number;
3826
3827 CURSOR csr_bdgt_grades(p_business_group_id number) IS
3828 SELECT DISTINCT
3829 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3830 bdt.grade_id,
3831 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3832 FROM
3833 pqh_budgets bgt,
3834 pqh_budget_versions bvr,
3835 pqh_budget_details bdt,
3836 pqh_budget_periods bpr,
3837 pqh_budget_sets bst,
3838 pqh_budget_elements bel
3839 WHERE
3840 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3841 AND bgt.budget_id = bvr.budget_id
3842 AND bvr.budget_version_id = bdt.budget_version_id
3843 AND bdt.budget_detail_id = bpr.budget_detail_id
3844 AND bpr.budget_period_id = bst.budget_period_id
3845 AND bst.budget_set_id = bel.budget_set_id
3846 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3847 bgt.budget_unit2_id = p_unit_of_measure_id or
3848 bgt.budget_unit3_id = p_unit_of_measure_id)
3849 AND bel.element_type_id = p_element_type_id
3850 AND bgt.budgeted_entity_cd = 'GRADE'
3851 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3852 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3853 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3854
3855 BEGIN
3856 hr_utility.set_location('Entering: '||l_proc, 5);
3857 --
3858 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3859 l_business_group_id := hr_general.get_business_group_id;
3860
3861 OPEN csr_bdgt_grades(l_business_group_id);
3862 LOOP
3863 FETCH csr_bdgt_grades INTO l_budget_version_id, l_grade_id,l_currency_code;
3864 EXIT WHEN csr_bdgt_grades%NOTFOUND;
3865
3866 --
3867 --
3868 BEGIN
3869 if (( l_budget_measurement_type <> 'MONEY') OR
3870 ( l_budget_measurement_type = 'MONEY' and
3871 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3872 l_curr_grade_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
3873 (
3874 p_budget_version_id => l_budget_version_id,
3875 p_budgeted_entity_cd => 'GRADE',
3876 p_element_type_id => p_element_type_id, /* Bug Fix 2719170 */
3877 p_entity_id => l_grade_id,
3878 p_start_date => p_start_date,
3879 p_end_date => p_end_date,
3880 p_unit_of_measure_id => p_unit_of_measure_id,
3881 p_value_type => p_value_type
3882 );
3883
3884 l_total_amt := l_total_amt + l_curr_grade_tot;
3885 end if;
3886 EXCEPTION
3887 WHEN OTHERS THEN
3888 l_curr_grade_tot := 0;
3889 l_total_amt := l_total_amt + l_curr_grade_tot;
3890 END;
3891 --
3892 --
3893
3894 END LOOP;
3895 CLOSE csr_bdgt_grades;
3896
3897
3898
3899 hr_utility.set_location('Leaving:'||l_proc, 1000);
3900
3901 RETURN l_total_amt;
3902
3903 EXCEPTION
3904 WHEN OTHERS THEN
3905 l_total_amt := 0;
3906 RETURN l_total_amt;
3907 END get_elem_grde_actual_cmmtmnts;
3908 --
3909 -- ORGANIZATION
3910 --
3911 FUNCTION get_elem_orgn_actual_cmmtmnts
3912 (
3913 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
3914 p_start_date IN pqh_budgets.budget_start_date%TYPE,
3915 p_end_date IN pqh_budgets.budget_end_date%TYPE,
3916 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
3917 p_value_type IN varchar2 DEFAULT 'T',
3918 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
3919 )
3920 RETURN NUMBER IS
3921 /*
3922 */
3923
3924 l_proc varchar2(72) := g_package||'get_elem_orgn_actual_cmmtmnts';
3925 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3926 l_organization_id pqh_budget_details.organization_id%TYPE;
3927 l_total_amt NUMBER := 0;
3928 l_curr_orgn_tot NUMBER := 0;
3929
3930 l_currency_code fnd_currencies.currency_code%TYPE;
3931 l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
3932 l_business_group_id number;
3933
3934 CURSOR csr_bdgt_orgs(p_business_group_id number) IS
3935 SELECT DISTINCT
3936 bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
3937 bdt.organization_id,
3938 pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3939 FROM
3940 pqh_budgets bgt,
3941 pqh_budget_versions bvr,
3942 pqh_budget_details bdt,
3943 pqh_budget_periods bpr,
3944 pqh_budget_sets bst,
3945 pqh_budget_elements bel
3946 WHERE
3947 bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3948 AND bgt.budget_id = bvr.budget_id
3949 AND bvr.budget_version_id = bdt.budget_version_id
3950 AND bdt.budget_detail_id = bpr.budget_detail_id
3951 AND bpr.budget_period_id = bst.budget_period_id
3952 AND bst.budget_set_id = bel.budget_set_id
3953 AND (bgt.budget_unit1_id = p_unit_of_measure_id or
3954 bgt.budget_unit2_id = p_unit_of_measure_id or
3955 bgt.budget_unit3_id = p_unit_of_measure_id)
3956 AND bel.element_type_id = p_element_type_id
3957 AND bgt.budgeted_entity_cd = 'ORGANIZATION'
3958 AND hr_general.effective_date BETWEEN bvr.date_from AND bvr.date_to
3959 AND ( bgt.budget_start_date BETWEEN p_start_date AND p_end_date
3960 OR p_start_date BETWEEN bgt.budget_start_date AND bgt.budget_end_date );
3961
3962 BEGIN
3963 hr_utility.set_location('Entering: '||l_proc, 5);
3964 --
3965 l_budget_measurement_type := get_budget_measurement_type(p_unit_of_measure_id);
3966 l_business_group_id := hr_general.get_business_group_id;
3967
3968 OPEN csr_bdgt_orgs(l_business_group_id);
3969 LOOP
3970 FETCH csr_bdgt_orgs INTO l_budget_version_id, l_organization_id,l_currency_code;
3971 EXIT WHEN csr_bdgt_orgs%NOTFOUND;
3972
3973 --
3974 --
3975 BEGIN
3976 if (( l_budget_measurement_type <> 'MONEY') OR
3977 ( l_budget_measurement_type = 'MONEY' and
3978 nvl(l_currency_code,'X') = nvl(p_currency_code,'X') )) then
3979 l_curr_orgn_tot := pqh_bdgt_actual_cmmtmnt_pkg.get_ent_actual_and_cmmtmnt
3980 (
3981 p_budget_version_id => l_budget_version_id,
3982 p_budgeted_entity_cd => 'ORGANIZATION',
3983 p_element_type_id => p_element_type_id, /* Bug Fix 2719170 */
3984 p_entity_id => l_organization_id,
3985 p_start_date => p_start_date,
3986 p_end_date => p_end_date,
3987 p_unit_of_measure_id => p_unit_of_measure_id,
3988 p_value_type => p_value_type
3989 );
3990
3991 l_total_amt := l_total_amt + l_curr_orgn_tot;
3992 end if;
3993 EXCEPTION
3994 WHEN OTHERS THEN
3995 l_curr_orgn_tot := 0;
3996 l_total_amt := l_total_amt + l_curr_orgn_tot;
3997 END;
3998 --
3999 --
4000
4001 END LOOP;
4002 CLOSE csr_bdgt_orgs;
4003
4004
4005
4006 hr_utility.set_location('Leaving:'||l_proc, 1000);
4007
4008 RETURN l_total_amt;
4009
4010 EXCEPTION
4011 WHEN OTHERS THEN
4012 l_total_amt := 0;
4013 RETURN l_total_amt;
4014 END get_elem_orgn_actual_cmmtmnts;
4015
4016
4017 ---------------------------------------------------------------------------------------------------------
4018 FUNCTION get_position_type
4019 (
4020 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4021 p_position_id IN per_positions.position_id%TYPE,
4022 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4023 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4024 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4025 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4026 )
4027 RETURN VARCHAR2 IS
4028 /*
4029 This function will return
4030 U => Under budgeted Position ( balance less then zero )
4031 O => Over budgeted Position ( balance more then zero )
4032 A => balance is zero
4033 */
4034 l_proc varchar2(72) := g_package||'get_position_type';
4035 l_posn_type varchar2(10);
4036 l_budgeted_amt NUMBER := 0;
4037 l_actual_amt NUMBER := 0;
4038 l_cmmtmnt_amt NUMBER := 0;
4039 l_projected_amt NUMBER := 0;
4040 l_balance_amt NUMBER := 0;
4041 l_shared_type_cd per_shared_types.system_type_cd%TYPE;
4042 BEGIN
4043 hr_utility.set_location('Entering: '||l_proc, 5);
4044
4045 l_budgeted_amt := pqh_mgmt_rpt_pkg.get_position_budget_amt
4046 (
4047 p_budget_version_id => p_budget_version_id,
4048 p_position_id => p_position_id,
4049 p_start_date => p_start_date,
4050 p_end_date => p_end_date,
4051 p_unit_of_measure_id => p_unit_of_measure_id,
4052 p_currency_code => p_currency_code
4053 );
4054
4055 hr_utility.set_location('Budgeted Amt : '||NVL(l_budgeted_amt,0),10);
4056
4057 l_actual_amt := pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
4058 (
4059 p_budget_version_id => p_budget_version_id,
4060 p_position_id => p_position_id,
4061 p_start_date => p_start_date,
4062 p_end_date => p_end_date,
4063 p_unit_of_measure_id => p_unit_of_measure_id,
4064 p_value_type => 'A',
4065 p_currency_code => p_currency_code
4066 );
4067
4068 hr_utility.set_location('Actual Amt : '||NVL(l_actual_amt,0),20);
4069
4070 l_cmmtmnt_amt := pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
4071 (
4072 p_budget_version_id => p_budget_version_id,
4073 p_position_id => p_position_id,
4074 p_start_date => p_start_date,
4075 p_end_date => p_end_date,
4076 p_unit_of_measure_id => p_unit_of_measure_id,
4077 p_value_type => 'C',
4078 p_currency_code => p_currency_code
4079 );
4080 hr_utility.set_location('Commitment Amt : '||NVL(l_cmmtmnt_amt,0),30);
4081
4082 l_shared_type_cd := get_budget_measurement_type(p_unit_of_measure_id);
4083 IF l_shared_type_cd = 'MONEY' THEN
4084 l_projected_amt := NVL(l_actual_amt,0) + NVL(l_cmmtmnt_amt,0);
4085 ELSE
4086 l_projected_amt := NVL(l_actual_amt,0);
4087 END IF;
4088
4089 hr_utility.set_location('Projected Amt : '||NVL(l_projected_amt,0),40);
4090
4091 l_balance_amt := NVL(l_budgeted_amt,0) - NVL(l_projected_amt,0);
4092 hr_utility.set_location('Balance Amt : '||NVL(l_balance_amt,0),50);
4093
4094 IF NVL(l_balance_amt,0) > 0 THEN
4095 -- Over budgeted
4096 l_posn_type := 'O';
4097 ELSIF NVL(l_balance_amt,0) < 0 THEN
4098 -- Under Budgeted
4099 l_posn_type := 'U';
4100 ELSE
4101 -- just right
4102 l_posn_type := 'A';
4103 END IF;
4104 hr_utility.set_location('Leaving:'||l_proc, 1000);
4105 RETURN l_posn_type;
4106 EXCEPTION
4107 WHEN OTHERS THEN
4108 l_posn_type := 'A';
4109 RETURN l_posn_type;
4110 END get_position_type;
4111
4112 ---------------------------------------------------------------------------------------------------------
4113 --
4114 --
4115 FUNCTION check_pos_type_and_variance
4116 (
4117 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4118 p_position_id IN per_positions.position_id%TYPE,
4119 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4120 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4121 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4122 p_position_type IN VARCHAR2 DEFAULT 'Y',
4123 p_variance_prcnt IN NUMBER DEFAULT 0,
4124 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4125 )
4126 RETURN VARCHAR2 IS
4127 /*
4128 This function will return
4129 U => Under budgeted Position ( balance less then zero )
4130 O => Over budgeted Position ( balance more then zero )
4131 A => balance is zero
4132
4133 */
4134
4135 l_proc varchar2(72) := g_package||'get_position_type';
4136 l_posn_type varchar2(10);
4137 l_budgeted_amt NUMBER := 0;
4138 l_actual_amt NUMBER := 0;
4139 l_cmmtmnt_amt NUMBER := 0;
4140 l_projected_amt NUMBER := 0;
4141 l_balance_amt NUMBER := 0;
4142 l_variance_prcnt NUMBER := 0;
4143 l_shared_type_cd per_shared_types.system_type_cd%TYPE;
4144
4145 BEGIN
4146 hr_utility.set_location('Entering: '||l_proc, 5);
4147 IF p_position_type = 'A' THEN
4148 RETURN 'A';
4149 END IF;
4150
4151 l_budgeted_amt := pqh_mgmt_rpt_pkg.get_position_budget_amt
4152 (
4153 p_budget_version_id => p_budget_version_id,
4154 p_position_id => p_position_id,
4155 p_start_date => p_start_date,
4156 p_end_date => p_end_date,
4157 p_unit_of_measure_id => p_unit_of_measure_id,
4158 p_currency_code => p_currency_code
4159 );
4160
4161 hr_utility.set_location('Budgeted Amt : '||NVL(l_budgeted_amt,0),10);
4162
4163 l_actual_amt := pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
4164 (
4165 p_budget_version_id => p_budget_version_id,
4166 p_position_id => p_position_id,
4167 p_start_date => p_start_date,
4168 p_end_date => p_end_date,
4169 p_unit_of_measure_id => p_unit_of_measure_id,
4170 p_value_type => 'A',
4171 p_currency_code => p_currency_code
4172 );
4173
4174 hr_utility.set_location('Actual Amt : '||NVL(l_actual_amt,0),20);
4175
4176 l_cmmtmnt_amt := pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
4177 (
4178 p_budget_version_id => p_budget_version_id,
4179 p_position_id => p_position_id,
4180 p_start_date => p_start_date,
4181 p_end_date => p_end_date,
4182 p_unit_of_measure_id => p_unit_of_measure_id,
4183 p_value_type => 'C',
4184 p_currency_code => p_currency_code
4185 );
4186
4187 hr_utility.set_location('Commitment Amt : '||NVL(l_cmmtmnt_amt,0),30);
4188
4189 l_shared_type_cd := get_budget_measurement_type(p_unit_of_measure_id);
4190 IF l_shared_type_cd = 'MONEY' THEN
4191 l_projected_amt := NVL(l_actual_amt,0) + NVL(l_cmmtmnt_amt,0);
4192 ELSE
4193 l_projected_amt := NVL(l_actual_amt,0);
4194 END IF;
4195
4196 hr_utility.set_location('Projected Amt : '||NVL(l_projected_amt,0),40);
4197
4198 l_balance_amt := NVL(l_budgeted_amt,0) - NVL(l_projected_amt,0);
4199
4200 hr_utility.set_location('Balance Amt : '||NVL(l_balance_amt,0),50);
4201
4202 IF l_budgeted_amt = 0 THEN
4203 l_variance_prcnt := 0;
4204 ELSE
4205 l_variance_prcnt := ABS(l_balance_amt / l_budgeted_amt * 100) ;
4206 END IF;
4207
4208 IF p_position_type = 'E' THEN
4209 IF l_variance_prcnt >= p_variance_prcnt THEN
4210 RETURN 'E';
4211 ELSE
4212 RETURN 'N';
4213 END IF;
4214 END IF;
4215
4216 IF NVL(l_balance_amt,0) > 0 THEN
4217 IF l_variance_prcnt >= p_variance_prcnt THEN
4218 -- Over budgeted
4219 l_posn_type := 'O';
4220 ELSE
4221 l_posn_type := 'N'; -- Such records are not to be selected.
4222 END IF;
4223 ELSIF NVL(l_balance_amt,0) < 0 THEN
4224 IF l_variance_prcnt >= p_variance_prcnt THEN
4225 -- Under Budgeted
4226 l_posn_type := 'U';
4227 ELSE
4228 l_posn_type := 'N'; -- Such records are not to be selected.
4229 END IF;
4230 ELSE
4231 -- just right
4232 l_posn_type := 'N';
4233 END IF;
4234
4235 hr_utility.set_location('Leaving:'||l_proc, 1000);
4236
4237 RETURN l_posn_type;
4238
4239 EXCEPTION
4240 WHEN OTHERS THEN
4241 l_posn_type := 'A';
4242 RETURN l_posn_type;
4243
4244 END check_pos_type_and_variance;
4245
4246 --
4247 -- This function checks whether a job is Under or Over budgeted and returns the type 'U' or 'O' or 'A'
4248 --
4249 FUNCTION check_job_type_and_variance
4250 (
4251 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4252 p_job_id IN per_jobs.job_id%TYPE,
4253 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4254 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4255 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4256 p_entity_type IN VARCHAR2 DEFAULT 'Y',
4257 p_variance_prcnt IN NUMBER DEFAULT 0,
4258 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4259 )
4260 RETURN VARCHAR2 IS
4261 /*
4262 This function will return
4263 U => Under budgeted job ( balance less then zero )
4264 O => Over budgeted job ( balance more then zero )
4265 A => balance is zero
4266
4267 */
4268
4269 l_proc varchar2(72) := g_package||'check_job_type';
4270 l_entity_type varchar2(10);
4271 l_budgeted_amt NUMBER := 0;
4272 l_actual_amt NUMBER := 0;
4273 l_cmmtmnt_amt NUMBER := 0;
4274 l_projected_amt NUMBER := 0;
4275 l_balance_amt NUMBER := 0;
4276 l_variance_prcnt NUMBER := 0;
4277 l_shared_type_cd Per_Shared_Types.System_Type_Cd%TYPE;
4278
4279 BEGIN
4280 hr_utility.set_location('Entering: '||l_proc, 5);
4281 IF p_entity_type = 'A' THEN
4282 RETURN 'A';
4283 END IF;
4284
4285 l_budgeted_amt := pqh_mgmt_rpt_pkg.get_entity_budget_amt
4286 (
4287 p_budget_version_id => p_budget_version_id,
4288 p_budgeted_entity_cd => 'JOB',
4289 p_job_id => p_job_id,
4290 p_start_date => p_start_date,
4291 p_end_date => p_end_date,
4292 p_unit_of_measure_id => p_unit_of_measure_id,
4293 p_currency_code => p_currency_code
4294 );
4295
4296 hr_utility.set_location('Budgeted Amt : '||NVL(l_budgeted_amt,0),10);
4297
4298 l_actual_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4299 (
4300 p_budget_version_id => p_budget_version_id,
4301 p_budgeted_entity_cd => 'JOB',
4302 p_job_id => p_job_id,
4303 p_start_date => p_start_date,
4304 p_end_date => p_end_date,
4305 p_unit_of_measure_id => p_unit_of_measure_id,
4306 p_value_type => 'A',
4307 p_currency_code => p_currency_code
4308 );
4309
4310 hr_utility.set_location('Actual Amt : '||NVL(l_actual_amt,0),20);
4311
4312 l_cmmtmnt_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4313 (
4314 p_budget_version_id => p_budget_version_id,
4315 p_budgeted_entity_cd => 'JOB',
4316 p_job_id => p_job_id,
4317 p_start_date => p_start_date,
4318 p_end_date => p_end_date,
4319 p_unit_of_measure_id => p_unit_of_measure_id,
4320 p_value_type => 'C',
4321 p_currency_code => p_currency_code
4322 );
4323
4324 hr_utility.set_location('Commitment Amt : '||NVL(l_cmmtmnt_amt,0),30);
4325
4326 l_shared_type_cd := get_budget_measurement_type(p_unit_of_measure_id);
4327 IF l_shared_type_cd = 'MONEY' THEN
4328 l_projected_amt := NVL(l_actual_amt,0) + NVL(l_cmmtmnt_amt,0);
4329 ELSE
4330 l_projected_amt := NVL(l_actual_amt,0);
4331 END IF;
4332
4333 hr_utility.set_location('Projected Amt : '||NVL(l_projected_amt,0),40);
4334
4335 l_balance_amt := NVL(l_budgeted_amt,0) - NVL(l_projected_amt,0);
4336
4337 hr_utility.set_location('Balance Amt : '||NVL(l_balance_amt,0),50);
4338
4339 IF l_budgeted_amt = 0 THEN
4340 l_variance_prcnt := 0;
4341 ELSE
4342 l_variance_prcnt := ABS(l_balance_amt / l_budgeted_amt * 100) ;
4343 END IF;
4344
4345 IF p_entity_type = 'E' THEN
4346 IF l_variance_prcnt >= p_variance_prcnt THEN
4347 RETURN 'E';
4348 ELSE
4349 RETURN 'N';
4350 END IF;
4351 END IF;
4352
4353 IF NVL(l_balance_amt,0) > 0 THEN
4354 IF l_variance_prcnt >= p_variance_prcnt THEN
4355 -- Over budgeted
4356 l_entity_type := 'O';
4357 ELSE
4358 l_entity_type := 'N'; -- Such records are not to be selected.
4359 END IF;
4360 ELSIF NVL(l_balance_amt,0) < 0 THEN
4361 IF l_variance_prcnt >= p_variance_prcnt THEN
4362 -- Under Budgeted
4363 l_entity_type := 'U';
4364 ELSE
4365 l_entity_type := 'N'; -- Such records are not to be selected.
4366 END IF;
4367 ELSE
4368 -- just right
4369 l_entity_type := 'N';
4370 END IF;
4371
4372 hr_utility.set_location('Leaving:'||l_proc, 1000);
4373
4374 RETURN l_entity_type;
4375
4376 EXCEPTION
4377 WHEN OTHERS THEN
4378 l_entity_type := 'A';
4379 RETURN l_entity_type;
4380
4381 END check_job_type_and_variance;
4382
4383
4384 --
4385 -- This function checks whether a grade is Under or Over budgeted and returns the type 'U' or 'O' or 'A'
4386 --
4387
4388 FUNCTION check_grde_type_and_variance
4389 (
4390 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4391 p_grade_id IN per_grades.grade_id%TYPE,
4392 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4393 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4394 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4395 p_entity_type IN VARCHAR2 DEFAULT 'Y',
4396 p_variance_prcnt IN NUMBER DEFAULT 0,
4397 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4398 )
4399 RETURN VARCHAR2 IS
4400 /*
4401 This function will return
4402 U => Under budgeted grade ( balance less then zero )
4403 O => Over budgeted grade ( balance more then zero )
4404 A => balance is zero
4405
4406 */
4407
4408 l_proc varchar2(72) := g_package||'check_grde_type';
4409 l_entity_type varchar2(10);
4410 l_budgeted_amt NUMBER := 0;
4411 l_actual_amt NUMBER := 0;
4412 l_cmmtmnt_amt NUMBER := 0;
4413 l_projected_amt NUMBER := 0;
4414 l_balance_amt NUMBER := 0;
4415 l_variance_prcnt NUMBER := 0;
4416 l_shared_type_cd Per_Shared_Types.System_Type_Cd%TYPE;
4417
4418 BEGIN
4419 hr_utility.set_location('Entering: '||l_proc, 5);
4420 IF p_entity_type = 'A' THEN
4421 RETURN 'A';
4422 END IF;
4423
4424 l_budgeted_amt := pqh_mgmt_rpt_pkg.get_entity_budget_amt
4425 (
4426 p_budget_version_id => p_budget_version_id,
4427 p_budgeted_entity_cd => 'GRADE',
4428 p_grade_id => p_grade_id,
4429 p_start_date => p_start_date,
4430 p_end_date => p_end_date,
4431 p_unit_of_measure_id => p_unit_of_measure_id,
4432 p_currency_code => p_currency_code
4433 );
4434
4435 hr_utility.set_location('Budgeted Amt : '||NVL(l_budgeted_amt,0),10);
4436
4437 l_actual_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4438 (
4439 p_budget_version_id => p_budget_version_id,
4440 p_budgeted_entity_cd => 'GRADE',
4441 p_grade_id => p_grade_id,
4442 p_start_date => p_start_date,
4443 p_end_date => p_end_date,
4444 p_unit_of_measure_id => p_unit_of_measure_id,
4445 p_value_type => 'A',
4446 p_currency_code => p_currency_code
4447 );
4448
4449 hr_utility.set_location('Actual Amt : '||NVL(l_actual_amt,0),20);
4450
4451 l_cmmtmnt_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4452 (
4453 p_budget_version_id => p_budget_version_id,
4454 p_budgeted_entity_cd => 'GRADE',
4455 p_grade_id => p_grade_id,
4456 p_start_date => p_start_date,
4457 p_end_date => p_end_date,
4458 p_unit_of_measure_id => p_unit_of_measure_id,
4459 p_value_type => 'C',
4460 p_currency_code => p_currency_code
4461 );
4462
4463 hr_utility.set_location('Commitment Amt : '||NVL(l_cmmtmnt_amt,0),30);
4464
4465 l_shared_type_cd := get_budget_measurement_type(p_unit_of_measure_id);
4466 IF l_shared_type_cd = 'MONEY' THEN
4467 l_projected_amt := NVL(l_actual_amt,0) + NVL(l_cmmtmnt_amt,0);
4468 ELSE
4469 l_projected_amt := NVL(l_actual_amt,0);
4470 END IF;
4471
4472 hr_utility.set_location('Projected Amt : '||NVL(l_projected_amt,0),40);
4473
4474 l_balance_amt := NVL(l_budgeted_amt,0) - NVL(l_projected_amt,0);
4475
4476 hr_utility.set_location('Balance Amt : '||NVL(l_balance_amt,0),50);
4477
4478 IF l_budgeted_amt = 0 THEN
4479 l_variance_prcnt := 0;
4480 ELSE
4481 l_variance_prcnt := ABS(l_balance_amt / l_budgeted_amt * 100) ;
4482 END IF;
4483
4484 IF p_entity_type = 'E' THEN
4485 IF l_variance_prcnt >= p_variance_prcnt THEN
4486 RETURN 'E';
4487 ELSE
4488 RETURN 'N';
4489 END IF;
4490 END IF;
4491
4492 IF NVL(l_balance_amt,0) > 0 THEN
4493 IF l_variance_prcnt >= p_variance_prcnt THEN
4494 -- Over budgeted
4495 l_entity_type := 'O';
4496 ELSE
4497 l_entity_type := 'N'; -- Such records are not to be selected.
4498 END IF;
4499 ELSIF NVL(l_balance_amt,0) < 0 THEN
4500 IF l_variance_prcnt >= p_variance_prcnt THEN
4501 -- Under Budgeted
4502 l_entity_type := 'U';
4503 ELSE
4504 l_entity_type := 'N'; -- Such records are not to be selected.
4505 END IF;
4506 ELSE
4507 -- just right
4508 l_entity_type := 'N';
4509 END IF;
4510
4511 hr_utility.set_location('Leaving:'||l_proc, 1000);
4512
4513 RETURN l_entity_type;
4514
4515 EXCEPTION
4516 WHEN OTHERS THEN
4517 l_entity_type := 'A';
4518 RETURN l_entity_type;
4519
4520 END check_grde_type_and_variance;
4521
4522 --
4523 -- This function checks whether an organization is Under or Over budgeted and returns the type 'U' or 'O' or 'A'
4524 --
4525 FUNCTION check_orgn_type_and_variance
4526 (
4527 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4528 p_organization_id IN hr_organization_units.organization_id%TYPE,
4529 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4530 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4531 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4532 p_entity_type IN VARCHAR2 DEFAULT 'Y',
4533 p_variance_prcnt IN NUMBER DEFAULT 0,
4534 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4535 )
4536 RETURN VARCHAR2 IS
4537 /*
4538 This function will return
4539 U => Under budgeted organization ( balance less then zero )
4540 O => Over budgeted organization ( balance more then zero )
4541 A => balance is zero
4542
4543 */
4544
4545 l_proc varchar2(72) := g_package||'check_orgn_type';
4546 l_entity_type varchar2(10);
4547 l_budgeted_amt NUMBER := 0;
4548 l_actual_amt NUMBER := 0;
4549 l_cmmtmnt_amt NUMBER := 0;
4550 l_projected_amt NUMBER := 0;
4551 l_balance_amt NUMBER := 0;
4552 l_variance_prcnt NUMBER := 0;
4553 l_shared_type_cd per_shared_types.system_type_cd%TYPE;
4554
4555 BEGIN
4556 hr_utility.set_location('Entering: '||l_proc, 5);
4557 IF p_entity_type = 'A' THEN
4558 RETURN 'A';
4559 END IF;
4560
4561 l_budgeted_amt := pqh_mgmt_rpt_pkg.get_entity_budget_amt
4562 (
4563 p_budget_version_id => p_budget_version_id,
4564 p_budgeted_entity_cd => 'ORGANIZATION',
4565 p_organization_id => p_organization_id,
4566 p_start_date => p_start_date,
4567 p_end_date => p_end_date,
4568 p_unit_of_measure_id => p_unit_of_measure_id,
4569 p_currency_code => p_currency_code
4570 );
4571
4572 hr_utility.set_location('Budgeted Amt : '||NVL(l_budgeted_amt,0),10);
4573
4574 l_actual_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4575 (
4576 p_budget_version_id => p_budget_version_id,
4577 p_budgeted_entity_cd => 'ORGANIZATION',
4578 p_organization_id => p_organization_id,
4579 p_start_date => p_start_date,
4580 p_end_date => p_end_date,
4581 p_unit_of_measure_id => p_unit_of_measure_id,
4582 p_value_type => 'A',
4583 p_currency_code => p_currency_code
4584 );
4585
4586 hr_utility.set_location('Actual Amt : '||NVL(l_actual_amt,0),20);
4587
4588 l_cmmtmnt_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
4589 (
4590 p_budget_version_id => p_budget_version_id,
4591 p_budgeted_entity_cd => 'ORGANIZATION',
4592 p_organization_id => p_organization_id,
4593 p_start_date => p_start_date,
4594 p_end_date => p_end_date,
4595 p_unit_of_measure_id => p_unit_of_measure_id,
4596 p_value_type => 'C',
4597 p_currency_code => p_currency_code
4598 );
4599
4600 hr_utility.set_location('Commitment Amt : '||NVL(l_cmmtmnt_amt,0),30);
4601
4602 l_shared_type_cd := get_budget_measurement_type(p_unit_of_measure_id);
4603 IF l_shared_type_cd = 'MONEY' THEN
4604 l_projected_amt := NVL(l_actual_amt,0) + NVL(l_cmmtmnt_amt,0);
4605 ELSE
4606 l_projected_amt := NVL(l_actual_amt,0);
4607 END IF;
4608
4609 hr_utility.set_location('Projected Amt : '||NVL(l_projected_amt,0),40);
4610
4611 l_balance_amt := NVL(l_budgeted_amt,0) - NVL(l_projected_amt,0);
4612
4613 hr_utility.set_location('Balance Amt : '||NVL(l_balance_amt,0),50);
4614
4615 IF l_budgeted_amt = 0 THEN
4616 l_variance_prcnt := 0;
4617 ELSE
4618 l_variance_prcnt := ABS(l_balance_amt / l_budgeted_amt * 100) ;
4619 END IF;
4620
4621 IF p_entity_type = 'E' THEN
4622 IF l_variance_prcnt >= p_variance_prcnt THEN
4623 RETURN 'E';
4624 ELSE
4625 RETURN 'N';
4626 END IF;
4627 END IF;
4628
4629 IF NVL(l_balance_amt,0) > 0 THEN
4630 IF l_variance_prcnt >= p_variance_prcnt THEN
4631 -- Over budgeted
4632 l_entity_type := 'O';
4633 ELSE
4634 l_entity_type := 'N'; -- Such records are not to be selected.
4635 END IF;
4636 ELSIF NVL(l_balance_amt,0) < 0 THEN
4637 IF l_variance_prcnt >= p_variance_prcnt THEN
4638 -- Under Budgeted
4639 l_entity_type := 'U';
4640 ELSE
4641 l_entity_type := 'N'; -- Such records are not to be selected.
4642 END IF;
4643 ELSE
4644 -- just right
4645 l_entity_type := 'N';
4646 END IF;
4647
4648 hr_utility.set_location('Leaving:'||l_proc, 1000);
4649
4650 RETURN l_entity_type;
4651
4652 EXCEPTION
4653 WHEN OTHERS THEN
4654 l_entity_type := 'A';
4655 RETURN l_entity_type;
4656
4657 END check_orgn_type_and_variance;
4658
4659 --
4660 -- This function is a wrapper which calls other functions depending on the budgeted entity code.
4661 --
4662 FUNCTION check_ent_type_and_variance
4663 (
4664 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4665 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4666 p_entity_id IN pqh_budget_details.position_id%TYPE,
4667 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4668 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4669 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4670 p_entity_type IN VARCHAR2 DEFAULT 'Y',
4671 p_variance_prcnt IN NUMBER DEFAULT 0,
4672 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4673 )
4674 RETURN VARCHAR2 IS
4675
4676 l_entity_type varchar2(10);
4677
4678 l_proc varchar2(72) := g_package||'chk_ent_type';
4679
4680 BEGIN
4681
4682 --hr_utility.set_location('Entering: '||l_proc,1000);
4683
4684 IF p_budgeted_entity_cd = 'POSITION' THEN
4685
4686 l_entity_type := check_pos_type_and_variance
4687 (
4688 p_budget_version_id => p_budget_version_id,
4689 p_position_id => p_entity_id,
4690 p_start_date => p_start_date,
4691 p_end_date => p_end_date,
4692 p_unit_of_measure_id => p_unit_of_measure_id,
4693 p_position_type => p_entity_type,
4694 p_variance_prcnt => p_variance_prcnt,
4695 p_currency_code => p_currency_code
4696 );
4697 RETURN l_entity_type;
4698
4699 ELSIF p_budgeted_entity_cd = 'JOB' THEN
4700
4701
4702 l_entity_type := check_job_type_and_variance
4703 (
4704 p_budget_version_id => p_budget_version_id,
4705 p_job_id => p_entity_id,
4706 p_start_date => p_start_date,
4707 p_end_date => p_end_date,
4708 p_unit_of_measure_id => p_unit_of_measure_id,
4709 p_entity_type => p_entity_type,
4710 p_variance_prcnt => p_variance_prcnt,
4711 p_currency_code => p_currency_code
4712 );
4713 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
4714
4715 l_entity_type := check_grde_type_and_variance
4716 (
4717 p_budget_version_id => p_budget_version_id,
4718 p_grade_id => p_entity_id,
4719 p_start_date => p_start_date,
4720 p_end_date => p_end_date,
4721 p_unit_of_measure_id => p_unit_of_measure_id,
4722 p_entity_type => p_entity_type,
4723 p_variance_prcnt => p_variance_prcnt,
4724 p_currency_code => p_currency_code
4725 );
4726 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
4727
4728 l_entity_type := check_orgn_type_and_variance
4729 (
4730 p_budget_version_id => p_budget_version_id,
4731 p_organization_id => p_entity_id,
4732 p_start_date => p_start_date,
4733 p_end_date => p_end_date,
4734 p_unit_of_measure_id => p_unit_of_measure_id,
4735 p_entity_type => p_entity_type,
4736 p_variance_prcnt => p_variance_prcnt,
4737 p_currency_code => p_currency_code
4738 );
4739 END IF;
4740
4741 hr_utility.set_location('Leaving: '||l_proc,1000);
4742
4743 RETURN l_entity_type;
4744
4745 EXCEPTION
4746 WHEN OTHERS THEN
4747 l_entity_type := 'A';
4748 RETURN l_entity_type;
4749
4750 END;
4751
4752 --
4753 -- This is a wrapper function which calls all other functions depending on the budgeted entity code
4754 -- and returns the budgeted amount for an entity and element type
4755 --
4756
4757 FUNCTION get_ent_element_bdgt_amt
4758 (
4759 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4760 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4761 p_entity_id IN pqh_budget_details.position_id%TYPE,
4762 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
4763 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4764 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4765 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4766 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4767 )
4768
4769 RETURN NUMBER IS
4770
4771 l_curr_ent_tot NUMBER :=0;
4772
4773 l_proc varchar2(72) := g_package||'get_ent_elmt_bdgt_amt';
4774
4775 BEGIN
4776
4777 hr_utility.set_location('Entering: '||l_proc,1000);
4778
4779 IF p_budgeted_entity_cd = 'POSITION' THEN
4780
4781 l_curr_ent_tot := get_posn_element_bdgt_amt
4782 (
4783 p_budget_version_id => p_budget_version_id,
4784 p_position_id => p_entity_id,
4785 p_element_type_id => p_element_type_id,
4786 p_start_date => p_start_date,
4787 p_end_date => p_end_date,
4788 p_unit_of_measure_id => p_unit_of_measure_id,
4789 p_currency_code => p_currency_code
4790 );
4791 ELSIF p_budgeted_entity_cd = 'JOB' THEN
4792
4793 l_curr_ent_tot := get_job_element_bdgt_amt
4794 (
4795 p_budget_version_id => p_budget_version_id,
4796 p_job_id => p_entity_id,
4797 p_element_type_id => p_element_type_id,
4798 p_start_date => p_start_date,
4799 p_end_date => p_end_date,
4800 p_unit_of_measure_id => p_unit_of_measure_id,
4801 p_currency_code => p_currency_code
4802 );
4803 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
4804
4805 l_curr_ent_tot := get_grde_element_bdgt_amt
4806 (
4807 p_budget_version_id => p_budget_version_id,
4808 p_grade_id => p_entity_id,
4809 p_element_type_id => p_element_type_id,
4810 p_start_date => p_start_date,
4811 p_end_date => p_end_date,
4812 p_unit_of_measure_id => p_unit_of_measure_id,
4813 p_currency_code => p_currency_code
4814 );
4815 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
4816
4817 l_curr_ent_tot := get_orgn_element_bdgt_amt
4818 (
4819 p_budget_version_id => p_budget_version_id,
4820 p_organization_id => p_entity_id,
4821 p_element_type_id => p_element_type_id,
4822 p_start_date => p_start_date,
4823 p_end_date => p_end_date,
4824 p_unit_of_measure_id => p_unit_of_measure_id,
4825 p_currency_code => p_currency_code
4826 );
4827 END IF;
4828
4829 hr_utility.set_location('Leaving: '||l_proc,1000);
4830
4831
4832 RETURN l_curr_ent_tot;
4833
4834 EXCEPTION
4835 WHEN OTHERS THEN
4836 l_curr_ent_tot := 0;
4837 RETURN l_curr_ent_tot;
4838
4839 END get_ent_element_bdgt_amt;
4840 --
4841 -- ENTITY
4842 --
4843 FUNCTION get_ent_elmnt_actual_cmmtmnts
4844 (
4845 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4846 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4847 p_entity_id IN pqh_budget_details.position_id%TYPE,
4848 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
4849 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4850 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4851 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4852 p_value_type IN varchar2 DEFAULT 'T',
4853 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4854 )
4855 RETURN NUMBER IS
4856
4857 l_curr_ent_tot NUMBER :=0;
4858 l_proc varchar2(72) := g_package||'get_ent_elmt_act_cmmt';
4859
4860 BEGIN
4861
4862 hr_utility.set_location('Entering: '||l_proc,1000);
4863
4864 IF p_budgeted_entity_cd = 'POSITION' THEN
4865
4866 l_curr_ent_tot := get_posn_elmnt_actual_cmmtmnts
4867 (
4868 p_budget_version_id => p_budget_version_id,
4869 p_position_id => p_entity_id,
4870 p_element_type_id => p_element_type_id,
4871 p_start_date => p_start_date,
4872 p_end_date => p_end_date,
4873 p_unit_of_measure_id => p_unit_of_measure_id,
4874 p_value_type => p_value_type,
4875 p_currency_code => p_currency_code
4876 );
4877 ELSIF p_budgeted_entity_cd = 'JOB' THEN
4878
4879 l_curr_ent_tot := get_job_elmnt_actual_cmmtmnts
4880 (
4881 p_budget_version_id => p_budget_version_id,
4882 p_job_id => p_entity_id,
4883 p_element_type_id => p_element_type_id,
4884 p_start_date => p_start_date,
4885 p_end_date => p_end_date,
4886 p_unit_of_measure_id => p_unit_of_measure_id,
4887 p_value_type => p_value_type,
4888 p_currency_code => p_currency_code
4889 );
4890 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
4891
4892 l_curr_ent_tot := get_grde_elmnt_actual_cmmtmnts
4893 (
4894 p_budget_version_id => p_budget_version_id,
4895 p_grade_id => p_entity_id,
4896 p_element_type_id => p_element_type_id,
4897 p_start_date => p_start_date,
4898 p_end_date => p_end_date,
4899 p_unit_of_measure_id => p_unit_of_measure_id,
4900 p_value_type => p_value_type,
4901 p_currency_code => p_currency_code
4902 );
4903 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
4904
4905 l_curr_ent_tot := get_orgn_elmnt_actual_cmmtmnts
4906 (
4907 p_budget_version_id => p_budget_version_id,
4908 p_organization_id => p_entity_id,
4909 p_element_type_id => p_element_type_id,
4910 p_start_date => p_start_date,
4911 p_end_date => p_end_date,
4912 p_unit_of_measure_id => p_unit_of_measure_id,
4913 p_value_type => p_value_type,
4914 p_currency_code => p_currency_code
4915 );
4916 END IF;
4917
4918 hr_utility.set_location('Leaving: '||l_proc,1000);
4919
4920 RETURN l_curr_ent_tot;
4921
4922 EXCEPTION
4923 WHEN OTHERS THEN
4924 l_curr_ent_tot := 0;
4925
4926 RETURN l_curr_ent_tot;
4927
4928
4929 END get_ent_elmnt_actual_cmmtmnts;
4930
4931 --
4932 -- ENTITY
4933 --
4934 FUNCTION get_elem_ent_budget_amt
4935 (
4936 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
4937 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4938 p_start_date IN pqh_budgets.budget_start_date%TYPE,
4939 p_end_date IN pqh_budgets.budget_end_date%TYPE,
4940 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4941 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
4942 )
4943 RETURN NUMBER IS
4944
4945 l_curr_ent_tot NUMBER :=0;
4946
4947 BEGIN
4948 IF p_budgeted_entity_cd = 'POSITION' THEN
4949
4950
4951 l_curr_ent_tot := get_elem_posn_budget_amt
4952 (
4953 p_element_type_id => p_element_type_id,
4954 p_start_date => p_start_date,
4955 p_end_date => p_end_date,
4956 p_unit_of_measure_id => p_unit_of_measure_id,
4957 p_currency_code => p_currency_code
4958 );
4959 ELSIF p_budgeted_entity_cd = 'JOB' THEN
4960
4961 l_curr_ent_tot := get_elem_job_budget_amt
4962 (
4963 p_element_type_id => p_element_type_id,
4964 p_start_date => p_start_date,
4965 p_end_date => p_end_date,
4966 p_unit_of_measure_id => p_unit_of_measure_id,
4967 p_currency_code => p_currency_code
4968 );
4969 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
4970
4971 l_curr_ent_tot := get_elem_grde_budget_amt
4972 (
4973 p_element_type_id => p_element_type_id,
4974 p_start_date => p_start_date,
4975 p_end_date => p_end_date,
4976 p_unit_of_measure_id => p_unit_of_measure_id,
4977 p_currency_code => p_currency_code
4978 );
4979 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
4980
4981 l_curr_ent_tot := get_elem_orgn_budget_amt
4982 (
4983 p_element_type_id => p_element_type_id,
4984 p_start_date => p_start_date,
4985 p_end_date => p_end_date,
4986 p_unit_of_measure_id => p_unit_of_measure_id,
4987 p_currency_code => p_currency_code
4988 );
4989 END IF;
4990
4991 RETURN l_curr_ent_tot;
4992
4993 EXCEPTION
4994 WHEN OTHERS THEN
4995 l_curr_ent_tot := 0;
4996
4997 RETURN l_curr_ent_tot;
4998
4999
5000 END get_elem_ent_budget_amt;
5001 --
5002 --
5003 --
5004 FUNCTION get_elem_ent_actual_cmmtmnts
5005 (
5006 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
5007 p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
5008 p_start_date IN pqh_budgets.budget_start_date%TYPE,
5009 p_end_date IN pqh_budgets.budget_end_date%TYPE,
5010 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
5011 p_value_type IN varchar2 DEFAULT 'T',
5012 p_currency_code IN fnd_currencies.currency_code%TYPE DEFAULT NULL
5013 )
5014 RETURN NUMBER IS
5015
5016 l_curr_ent_tot NUMBER :=0;
5017
5018 BEGIN
5019 IF p_budgeted_entity_cd = 'POSITION' THEN
5020
5021
5022 l_curr_ent_tot := get_elem_posn_actual_cmmtmnts
5023 (
5024 p_element_type_id => p_element_type_id,
5025 p_start_date => p_start_date,
5026 p_end_date => p_end_date,
5027 p_unit_of_measure_id => p_unit_of_measure_id,
5028 p_value_type => p_value_type,
5029 p_currency_code => p_currency_code
5030 );
5031
5032 ELSIF p_budgeted_entity_cd = 'JOB' THEN
5033
5034 l_curr_ent_tot := get_elem_job_actual_cmmtmnts
5035 (
5036 p_element_type_id => p_element_type_id,
5037 p_start_date => p_start_date,
5038 p_end_date => p_end_date,
5039 p_unit_of_measure_id => p_unit_of_measure_id,
5040 p_value_type => p_value_type,
5041 p_currency_code => p_currency_code
5042 );
5043
5044 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
5045
5046 l_curr_ent_tot := get_elem_grde_actual_cmmtmnts
5047 (
5048 p_element_type_id => p_element_type_id,
5049 p_start_date => p_start_date,
5050 p_end_date => p_end_date,
5051 p_unit_of_measure_id => p_unit_of_measure_id,
5052 p_value_type => p_value_type,
5053 p_currency_code => p_currency_code
5054 );
5055
5056 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
5057
5058 l_curr_ent_tot := get_elem_orgn_actual_cmmtmnts
5059 (
5060 p_element_type_id => p_element_type_id,
5061 p_start_date => p_start_date,
5062 p_end_date => p_end_date,
5063 p_unit_of_measure_id => p_unit_of_measure_id,
5064 p_value_type => p_value_type,
5065 p_currency_code => p_currency_code
5066 );
5067
5068 END IF;
5069
5070 RETURN l_curr_ent_tot;
5071
5072 EXCEPTION
5073 WHEN OTHERS THEN
5074 l_curr_ent_tot := 0;
5075
5076 RETURN l_curr_ent_tot;
5077
5078 END get_elem_ent_actual_cmmtmnts;
5079 --
5080 --
5081 FUNCTION get_pos_org
5082 (
5083 p_position_id IN hr_all_positions_f.position_id%TYPE
5084 )
5085 RETURN VARCHAR2 is
5086
5087 Cursor Cr_Org_name is
5088 Select Org.Name Org_name
5089 from Hr_all_Positions_F pos, hr_all_organization_units_tl Org
5090 where position_id = p_position_id
5091 and org.organization_id = pos.organization_id
5092 and language = userenv('LANG');
5093
5094 l_org_name hr_all_organization_units_tl.Name%TYPE := NULL;
5095 Begin
5096 If p_position_id is NOT NULL Then
5097 Open Cr_Org_Name;
5098 Fetch Cr_Org_name into l_org_name;
5099 Close Cr_Org_name;
5100 Return l_org_name;
5101 End If;
5102 End get_pos_org;
5103 --
5104 --
5105 Function GET_ENTITY_BUDGET_AMT( p_budgeted_entity_cd IN varchar2,
5106 p_entity_id IN Number,
5107 p_budget_version_id IN Number,
5108 p_start_date IN DATE,
5109 p_end_date IN DATE,
5110 p_unit_of_measure_id IN Number) RETURN NUMBER
5111 IS
5112 l_amt NUMBER;
5113 BEGIN
5114 IF p_budgeted_entity_cd = 'POSITION' THEN
5115 l_amt := get_position_budget_amt(p_budget_version_id => p_budget_version_id,
5116 p_position_id => p_entity_id,
5117 p_start_date=> p_start_date,
5118 p_end_date => p_end_date,
5119 p_unit_of_measure_id => p_unit_of_measure_id);
5120 ELSIF p_budgeted_entity_cd = 'GRADE' THEN
5121 l_amt := get_entity_budget_amt(p_budget_version_id => p_budget_version_id,
5122 p_budgeted_entity_cd => p_budgeted_entity_cd,
5123 p_grade_id => p_entity_id,
5124 p_start_date => p_start_date,
5125 p_end_date => p_end_date,
5126 p_unit_of_measure_id => p_unit_of_measure_id);
5127 ELSIF p_budgeted_entity_cd = 'JOB' THEN
5128 l_amt := get_entity_budget_amt(p_budget_version_id => p_budget_version_id,
5129 p_budgeted_entity_cd => p_budgeted_entity_cd,
5130 p_job_id => p_entity_id,
5131 p_start_date => p_start_date,
5132 p_end_date => p_end_date,
5133 p_unit_of_measure_id => p_unit_of_measure_id);
5134 ELSIF p_budgeted_entity_cd = 'ORGANIZATION' THEN
5135 l_amt := get_entity_budget_amt(p_budget_version_id => p_budget_version_id,
5136 p_budgeted_entity_cd => p_budgeted_entity_cd,
5137 p_organization_id => p_entity_id,
5138 p_start_date => p_start_date,
5139 p_end_date => p_end_date,
5140 p_unit_of_measure_id => p_unit_of_measure_id);
5141 END IF;
5142 RETURN l_amt;
5143 Exception
5144 When Others THen
5145 Return 0;
5146 END GET_ENTITY_BUDGET_AMT;
5147 END pqh_mgmt_rpt_pkg;