1 Package body pqh_budgeted_salary_pkg as
2 /* $Header: pqbgtsal.pkb 120.0 2005/05/29 01:32:01 appldev noship $ */
3 --
4 --Added extra parameters
5 --
6 function get_pc_budgeted_salary( p_position_id in number default null
7 ,p_job_id in number default null
8 ,p_grade_id in number default null
9 ,p_organization_id in number default null
10 ,p_budget_entity in varchar2
11 ,p_start_date in date default sysdate
12 ,p_end_date in date default sysdate
13 ,p_effective_date in date default sysdate
14 ,p_business_group_id in number
15 ) return number is
16 --
17 --
18 l_budget_detail_id number(20);
19 --
20 -- Cursor to fetch the Budgeted Salary or the given dates
21 --
22 cursor c_budget_elements is
23 select
24 stp.start_date,
25 etp.end_date, bud.period_set_name,
26 (decode('MONEY',
27 pqh_psf_bus.get_system_shared_type(bud.budget_unit1_id),bsets.budget_unit1_value,
28 pqh_psf_bus.get_system_shared_type(bud.budget_unit2_id),bsets.budget_unit2_value,
29 pqh_psf_bus.get_system_shared_type(bud.budget_unit3_id),bsets.budget_unit3_value,
30 0))
31 * nvl(bele.distribution_percentage ,0)/100 budget_element_value
32 from
33 pqh_budgets bud,
34 pqh_budget_versions bver,
35 pqh_budget_details bdet,
36 pqh_budget_periods bper,
37 per_time_periods stp,
38 per_time_periods etp,
39 pqh_budget_sets bsets,
40 pqh_budget_elements bele,
41 pqh_bdgt_cmmtmnt_elmnts bcl
42 where nvl(bud.position_control_flag,'X') = 'Y'
43 and bud.budgeted_entity_cd = p_budget_entity
44 and bud.business_group_id = p_business_group_id
45 -- and trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
46 and ((p_start_date <= bud.budget_start_date
47 and p_end_date >= bud.budget_end_date)
48 or
49 (p_start_date between bud.budget_start_date and bud.budget_end_date) or
50 (p_end_date between bud.budget_start_date and bud.budget_end_date)
51 )
52 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
53 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
54 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
55 )
56 and bud.budget_id = bver.budget_id
57 and trunc(p_effective_date) between trunc(bver.date_from) and trunc(bver.date_to)
58 and nvl(p_organization_id, nvl(bdet.organization_id, -1)) =
59 nvl(bdet.organization_id, -1)
60 and nvl(p_job_id, nvl(bdet.job_id, -1)) =
61 nvl(bdet.job_id, -1)
62 and nvl(p_position_id, nvl(bdet.position_id, -1)) =
63 nvl(bdet.position_id, -1)
64 and nvl(p_grade_id, nvl(bdet.grade_id, -1)) =
65 nvl(bdet.grade_id, -1)
66 and bver.budget_version_id = bdet.budget_version_id
67 and bper.budget_detail_id = bdet.budget_detail_id
68 and bper.start_time_period_id = stp.time_period_id
69 and bper.end_time_period_id = etp.time_period_id
70 and etp.end_date >= p_start_date
71 and stp.start_date <= p_end_date
72 and bsets.budget_period_id = bper.budget_period_id
73 and bele.budget_set_id = bsets.budget_set_id
74 and bud.budget_id = bcl.budget_id
75 and bele.element_type_id = bcl.element_type_id;
76
77 --
78 --
79 -- Local Variables
80 --
81 l_salary number;
82 calc_start_date date;
83 calc_end_date date;
84 l_temp_bdgt_element_value number := 0;
85 --
86 begin
87 --
88 -- Prorate the records if start date / end date are between the specified dates
89 -- Add all the salary between the specified dates
90 --
91 hr_utility.set_location('Entering get_pc_budgeted_salary' , 100);
92
93 for l_budget_elements in c_budget_elements
94 loop
95
96 --
97 --
98 -- Prorate the records if start date / end date are between the specified dates
99 --
100 if p_start_date between l_budget_elements.start_date and l_budget_elements.end_date
101 or p_end_date between l_budget_elements.start_date and l_budget_elements.end_date then
102 --
103 calc_start_date := greatest(l_budget_elements.start_date, p_start_date);
104 calc_end_date := least(l_budget_elements.end_date, p_end_date);
105 --
106 l_temp_bdgt_element_value := nvl(l_budget_elements.budget_element_value,0) *
107 get_prorate_ratio(calc_start_date , calc_end_date , l_budget_elements.period_set_name
108 , l_budget_elements.start_date , l_budget_elements.end_date );
109 else
110 l_temp_bdgt_element_value := l_budget_elements.budget_element_value;
111 end if;
112 --
113 l_salary := nvl(l_salary,0) + l_temp_bdgt_element_value;
114 --
115 --
116 end loop;
117 hr_utility.set_location('l_salary '||l_salary , 101);
118 --
119 -- Return the Total salary
120 --
121 return(trunc(l_salary,2));
122 --
123 end;
124
125 --
126 -- Sreevijay- Function to calculate the budgeted hours
127 --
128 Function get_budgeted_hours( p_position_id in number default null
129 ,p_job_id in number default null
130 ,p_grade_id in number default null
131 ,p_organization_id in number default null
132 ,p_budget_entity in varchar2
133 ,p_start_date in date default sysdate
134 ,p_end_date in date default sysdate
135 ,p_effective_date in date default sysdate
136 ,p_business_group_id in number
137 ) return number is
138
139 cursor c_budget_periods is
140 select
141 stp.start_date,
142 etp.end_date, bud.period_set_name,
143 (decode('HOURS',
144 pqh_psf_bus.get_system_shared_type(bud.budget_unit1_id),bper.budget_unit1_value,
145 pqh_psf_bus.get_system_shared_type(bud.budget_unit2_id),bper.budget_unit2_value,
146 pqh_psf_bus.get_system_shared_type(bud.budget_unit3_id),bper.budget_unit3_value,
147 0)) budget_period_value
148 from
149 pqh_budgets bud,
150 pqh_budget_versions bver,
151 pqh_budget_details bdet,
152 pqh_budget_periods bper,
153 per_time_periods stp,
154 per_time_periods etp
155 where nvl(bud.position_control_flag,'X') = 'Y'
156 and bud.budgeted_entity_cd = p_budget_entity
157 and bud.business_group_id = p_business_group_id
158 and ((p_start_date <= bud.budget_start_date
159 and p_end_date >= bud.budget_end_date)
160 or
161 (p_start_date between bud.budget_start_date and bud.budget_end_date) or
162 (p_end_date between bud.budget_start_date and bud.budget_end_date)
163 )
164 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'HOURS'
165 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'HOURS'
166 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'HOURS'
167 )
168 and bud.budget_id = bver.budget_id
169 and trunc(p_effective_date) between trunc(bver.date_from) and trunc(bver.date_to)
170 and nvl(p_organization_id, nvl(bdet.organization_id, -1)) =
171 nvl(bdet.organization_id, -1)
172 and nvl(p_job_id, nvl(bdet.job_id, -1)) =
173 nvl(bdet.job_id, -1)
174 and nvl(p_position_id, nvl(bdet.position_id, -1)) =
175 nvl(bdet.position_id, -1)
176 and nvl(p_grade_id, nvl(bdet.grade_id, -1)) =
177 nvl(bdet.grade_id, -1)
178 and bver.budget_version_id = bdet.budget_version_id
179 and bper.budget_detail_id = bdet.budget_detail_id
180 and bper.start_time_period_id = stp.time_period_id
181 and bper.end_time_period_id = etp.time_period_id
182 and etp.end_date >= p_start_date
183 and stp.start_date <= p_end_date;
184
185 --
186 -- Local Variables
187 --
188 l_hours number;
189 calc_start_date date;
190 calc_end_date date;
191 l_temp_bdgt_period_value number := 0;
192
193
194 Begin
195
196 for l_budget_periods in c_budget_periods
197 loop
198 --
199 --
200 -- Prorate the records if start date / end date are between the specified dates
201 --
202 if p_start_date between l_budget_periods.start_date and l_budget_periods.end_date
203 or p_end_date between l_budget_periods.start_date and l_budget_periods.end_date then
204 --
205 calc_start_date := greatest(l_budget_periods.start_date, p_start_date);
206 calc_end_date := least(l_budget_periods.end_date, p_end_date);
207 --
208 l_temp_bdgt_period_value := nvl(l_budget_periods.budget_period_value,0) *
209 get_prorate_ratio(calc_start_date , calc_end_date , l_budget_periods.period_set_name
210 , l_budget_periods.start_date , l_budget_periods.end_date );
211 else
212 l_temp_bdgt_period_value := nvl(l_budget_periods.budget_period_value,0);
213 end if;
214 --
215 l_hours := nvl(l_hours,0) + l_temp_bdgt_period_value;
216 --
217 --
218 end loop;
219 hr_utility.set_location('l_hours '|| l_hours , 101);
220 --
221 -- Return the Total Hours
222 --
223 return(trunc(l_hours,2));
224 --
225 End;
226
227
228 --
229 -- Function GET_PRORATE_RATIO which returns the prorate ratio of the parameters passed
230 --
231 function get_prorate_ratio(
232 p_start_date date,
233 p_end_date date,
234 p_period_set_name varchar2,
235 p_period_start_date date,
236 p_period_end_date date)
237 return number is
238 --
239 -- Cursor to fetch the number of periods in a Calendar(p_period_set_name) between p_start_date and p_end_date
240 --
241 cursor no_periods_tp(p_period_set_name varchar2, p_start_date date, p_end_date date) is
242 select
243 count(*)
244 from
245 per_time_periods
246 where
247 period_set_name = p_period_set_name
248 and p_start_date <= start_date
249 and p_end_date >= end_date;
250 --
251 -- Cursor to fetch the start_date and end_date of the period which contains p_date
252 -- from calendar p_period_set_name
253 --
254 cursor c_period(p_period_set_name varchar2, p_start_date date, p_end_date date) is
255 select
256 start_date,
257 end_date
258 from
259 per_time_periods
260 where
261 p_period_set_name = period_set_name
262 and ((p_start_date <= start_date
263 and p_end_date >= end_date
264 ) or
265 (p_start_date between start_date and end_date) or
266 (p_end_date between start_date and end_date)
267 );
268 --
269 l_no_total_periods number := 0;
270 calc_start_date date;
271 calc_end_date date;
272 l_temp_ratio number := 0;
273 l_total_ratio number := 0;
274 begin
275 --
276 -- Fetch the Total no of periods between p_period_start_date, p_period_end_date
277 --
278 open no_periods_tp(p_period_set_name, p_period_start_date, p_period_end_date);
279 fetch no_periods_tp into l_no_total_periods;
280 close no_periods_tp;
281 --
282 hr_utility.set_location('l_no_total_periods = ' || l_no_total_periods, 100);
283 --
284 --
285 -- Fetch the start_date and end_date of the period which contains p_start_date
286 -- and p_end_date from calendar p_period_set_name
287 --
288 for l_periods in c_period(p_period_set_name , p_start_date, p_end_date)
289 loop
290 --
291 -- Prorate the period if p_start_date between period dates
292 -- or p_end_date between period_dates
293 --
294 if p_start_date between l_periods.start_date and l_periods.end_date
295 or p_end_date between l_periods.start_date and l_periods.end_date then
296 -- Calculate the calc_start_date and calc_end_date
297 calc_start_date := greatest(l_periods.start_date, p_start_date);
298 calc_end_date := least(l_periods.end_date, p_end_date);
299 -- Calculate the l_temp_ratio ( Prorate )
300 l_temp_ratio :=
301 ((calc_end_date - calc_start_date) + 1)/((l_periods.end_date-l_periods.start_date) + 1);
302 --
303 else
304 -- otherwise 1
305 l_temp_ratio := 1;
306 --
307 end if;
308 --
309 hr_utility.set_location('l_temp_ratio = ' || l_temp_ratio, 110);
310 l_total_ratio := l_total_ratio + l_temp_ratio;
311 end loop;
312 --
313 hr_utility.set_location('l_total_ratio = ' || l_total_ratio, 120);
314 --
315 return((l_total_ratio)/l_no_total_periods);
316 --
317 end;
318 --
319 end;
[Home] [Help]