[Home] [Help]
PACKAGE BODY: APPS.PQH_BDGT
Source
4
1 package body pqh_bdgt as
2 /* $Header: pqbudget.pkb 120.2 2011/02/11 09:43:25 apjaiswa ship $ */
3 g_package varchar2(100) := 'PQH_BDGT.' ;
5 function get_bgv_budget( p_budget_version_id in number) return number is
6 cursor c1 is select budget_id
7 from pqh_budget_versions
8 where budget_version_id = p_budget_version_id ;
9 l_budget_id number;
10 begin
11 open c1;
12 fetch c1 into l_budget_id ;
13 close c1;
14 return l_budget_id;
15 end get_bgv_budget;
16 procedure propagate_version_changes (p_change_mode in varchar2,
17 p_budget_version_id in number,
18 p_budget_style_cd in varchar2,
19 p_new_bgv_unit1_value in number,
20 p_new_bgv_unit2_value in number,
21 p_new_bgv_unit3_value in number,
22 p_unit1_precision in number,
23 p_unit2_precision in number,
24 p_unit3_precision in number,
25 p_unit1_aggregate in varchar2,
26 p_unit2_aggregate in varchar2,
27 p_unit3_aggregate in varchar2,
28 p_budget_version_status in out nocopy varchar2,
29 p_bgv_unit1_available in out nocopy number,
30 p_bgv_unit2_available in out nocopy number,
31 p_bgv_unit3_available in out nocopy number
32 )is
33 cursor c1 is select budget_detail_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,
34 budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
35 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
36 budget_unit1_available,budget_unit2_available,budget_unit3_available,
37 object_version_number,gl_status
38 from pqh_budget_details
39 where budget_version_id = p_budget_version_id
40 for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
41 budget_unit1_available,budget_unit2_available,budget_unit3_available,
42 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent ;
43
44 l_budget_unit1_value number;
45 l_budget_unit2_value number;
46 l_budget_unit3_value number;
47 l_budget_unit1_percent number;
48 l_budget_unit2_percent number;
49 l_budget_unit3_percent number;
50 l_budget_unit1_available number;
51 l_budget_unit2_available number;
52 l_budget_unit3_available number;
53 ini_budget_unit1_available number := p_bgv_unit1_available;
54 ini_budget_unit2_available number := p_bgv_unit2_available;
55 ini_budget_unit3_available number := p_bgv_unit3_available;
56 l_object_version_number number;
57 l_bgd_status varchar2(30);
58 l_proc varchar2(100) := g_package||'propagate_version_changes' ;
59 l_code varchar2(30) := p_change_mode;
60 begin
61 hr_utility.set_location('entering '||l_proc,10);
62 hr_utility.set_location('entering with bgv'||p_budget_version_id||l_proc,11);
63 hr_utility.set_location('l_code is'||l_code||l_proc,35);
64 for i in c1 loop
65 hr_utility.set_location('for each budgeted row '||l_proc,40);
66 if l_code = 'RV' then
67 hr_utility.set_location('unit1 for RV'||l_proc,45);
68 if nvl(p_new_bgv_unit1_value,0) <> 0 then
69 l_budget_unit1_percent := round((i.budget_unit1_value * 100)/p_new_bgv_unit1_value,2) ;
70 else
71 l_budget_unit1_percent := null;
72 end if;
73 l_budget_unit1_value := i.budget_unit1_value;
74 l_budget_unit1_available := i.budget_unit1_available;
75 elsif l_code = 'RP' then
76 hr_utility.set_location('unit1 for RP'||l_proc,50);
77 if nvl(p_new_bgv_unit1_value,0) <> 0 then
78 l_budget_unit1_value := round(p_new_bgv_unit1_value * nvl(i.budget_unit1_percent,0)/100,p_unit1_precision) ;
79 l_budget_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_budget_unit1_value,0) - nvl(i.budget_unit1_value,0);
80 p_bgv_unit1_available := nvl(p_bgv_unit1_available,0) - nvl(l_budget_unit1_value,0) + nvl(i.budget_unit1_value,0);
81 else
82 l_budget_unit1_value := i.budget_unit1_value;
83 l_budget_unit1_available := i.budget_unit1_available;
84 end if;
85 l_budget_unit1_percent := i.budget_unit1_percent;
86 else
87 hr_utility.set_location('unit1 for UE'||l_proc,55);
88 if nvl(p_new_bgv_unit1_value,0) <> 0 then
89 if i.budget_unit1_value_type_cd = 'P' then
90 l_budget_unit1_value := round(p_new_bgv_unit1_value * nvl(i.budget_unit1_percent,0)/100,p_unit1_precision) ;
91 l_budget_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_budget_unit1_value,0) - nvl(i.budget_unit1_value,0);
92 p_bgv_unit1_available := nvl(p_bgv_unit1_available,0) - nvl(l_budget_unit1_value,0) + nvl(i.budget_unit1_value,0);
93 l_budget_unit1_percent := i.budget_unit1_percent;
94 else
95 l_budget_unit1_value := i.budget_unit1_value;
96 l_budget_unit1_available := i.budget_unit1_available;
97 l_budget_unit1_percent := round((i.budget_unit1_value * 100)/p_new_bgv_unit1_value,2) ;
98 end if;
99 else
100 l_budget_unit1_value := i.budget_unit1_value;
101 l_budget_unit1_available := i.budget_unit1_available;
102 l_budget_unit1_percent := null;
103 end if;
104 end if;
105
106 if l_code ='RV' then
110 else
107 hr_utility.set_location('unit2 for RV'||l_proc,60);
108 if nvl(p_new_bgv_unit2_value,0) <> 0 then
109 l_budget_unit2_percent := round((i.budget_unit2_value * 100)/p_new_bgv_unit2_value,2) ;
111 l_budget_unit2_percent := null;
112 end if;
113 l_budget_unit2_value := i.budget_unit2_value;
114 l_budget_unit2_available := i.budget_unit2_available;
115 elsif l_code ='RP' then
116 hr_utility.set_location('unit2 for RP'||l_proc,65);
117 if nvl(p_new_bgv_unit2_value,0) <> 0 then
118 l_budget_unit2_value := round(p_new_bgv_unit2_value * nvl(i.budget_unit2_percent,0)/100,p_unit2_precision) ;
119 l_budget_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_budget_unit2_value,0) - nvl(i.budget_unit2_value,0);
120 p_bgv_unit2_available := nvl(p_bgv_unit2_available,0) - nvl(l_budget_unit2_value,0) + nvl(i.budget_unit2_value,0);
121 else
122 l_budget_unit2_value := i.budget_unit2_value;
123 l_budget_unit2_available := i.budget_unit2_available;
124 end if;
125 l_budget_unit2_percent := i.budget_unit2_percent;
126 else
127 hr_utility.set_location('unit2 for UE'||l_proc,70);
128 if nvl(p_new_bgv_unit2_value,0) <> 0 then
129 if i.budget_unit2_value_type_cd = 'P' then
130 l_budget_unit2_value := round(p_new_bgv_unit2_value * nvl(i.budget_unit2_percent,0)/100,p_unit2_precision) ;
131 l_budget_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_budget_unit2_value,0) - nvl(i.budget_unit2_value,0);
132 p_bgv_unit2_available := nvl(p_bgv_unit2_available,0) - nvl(l_budget_unit2_value,0) + nvl(i.budget_unit2_value,0);
133 l_budget_unit2_percent := i.budget_unit2_percent;
134 else
135 l_budget_unit2_value := i.budget_unit2_value;
136 l_budget_unit2_available := i.budget_unit2_available;
137 l_budget_unit2_percent := round((i.budget_unit2_value * 100)/p_new_bgv_unit2_value,2) ;
138 end if;
139 else
140 l_budget_unit2_value := i.budget_unit2_value;
141 l_budget_unit2_available := i.budget_unit2_available;
142 l_budget_unit2_percent := null;
143 end if;
144 end if;
145
146 if l_code ='RV' then
147 hr_utility.set_location('unit3 for RV'||l_proc,75);
148 if nvl(p_new_bgv_unit3_value,0) <> 0 then
149 l_budget_unit3_percent := round((i.budget_unit3_value * 100)/p_new_bgv_unit3_value,2) ;
150 else
151 l_budget_unit3_percent := null;
152 end if;
153 l_budget_unit3_value := i.budget_unit3_value;
154 l_budget_unit3_available := i.budget_unit3_available;
155 elsif l_code ='RP' then
156 hr_utility.set_location('unit3 for RP'||l_proc,80);
157 if nvl(p_new_bgv_unit3_value,0) <> 0 then
158 l_budget_unit3_value := round(p_new_bgv_unit3_value * nvl(i.budget_unit3_percent,0)/100,p_unit3_precision) ;
159 l_budget_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_budget_unit3_value,0) - nvl(i.budget_unit3_value,0);
160 p_bgv_unit3_available := nvl(p_bgv_unit3_available,0) - nvl(l_budget_unit3_value,0) + nvl(i.budget_unit3_value,0);
161 else
162 l_budget_unit3_value := i.budget_unit3_value;
163 l_budget_unit3_available := i.budget_unit3_available;
164 end if;
165 l_budget_unit3_percent := i.budget_unit3_percent;
166 else
167 hr_utility.set_location('unit3 for UE'||l_proc,85);
168 if nvl(p_new_bgv_unit3_value,0) <> 0 then
169 if i.budget_unit3_value_type_cd = 'P' then
170 l_budget_unit3_value := round(p_new_bgv_unit3_value * nvl(i.budget_unit3_percent,0)/100,p_unit3_precision) ;
171 l_budget_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_budget_unit3_value,0) - nvl(i.budget_unit3_value,0);
172 p_bgv_unit3_available := nvl(p_bgv_unit3_available,0) - nvl(l_budget_unit3_value,0) + nvl(i.budget_unit3_value,0);
173 l_budget_unit3_percent := i.budget_unit3_percent;
174 else
175 l_budget_unit3_value := i.budget_unit3_value;
176 l_budget_unit3_available := i.budget_unit3_available;
177 l_budget_unit3_percent := round((i.budget_unit3_value * 100)/p_new_bgv_unit3_value,2) ;
178 end if;
179 else
180 l_budget_unit3_value := i.budget_unit3_value;
181 l_budget_unit3_available := i.budget_unit3_available;
182 l_budget_unit3_percent := null;
183 end if;
184 end if;
185 hr_utility.set_location('before calling propagate_budget_changes'||l_proc,90);
186 hr_utility.set_location('values passed are'||l_proc,95);
187 hr_utility.set_location('unit1_value'||l_budget_unit1_value||l_proc,100);
188 hr_utility.set_location('unit2_value'||l_budget_unit2_value||l_proc,101);
189 hr_utility.set_location('unit3_value'||l_budget_unit3_value||l_proc,102);
190 hr_utility.set_location('unit1_available'||l_budget_unit1_available||l_proc,103);
191 hr_utility.set_location('unit2_available'||l_budget_unit2_available||l_proc,104);
192 hr_utility.set_location('unit3_available'||l_budget_unit3_available||l_proc,105);
193 l_object_version_number := i.object_version_number;
194 propagate_budget_changes (p_change_mode => l_code,
195 p_budget_detail_id => i.budget_detail_id,
196 p_new_bgt_unit1_value => l_budget_unit1_value,
197 p_new_bgt_unit2_value => l_budget_unit2_value,
198 p_new_bgt_unit3_value => l_budget_unit3_value,
199 p_unit1_precision => p_unit1_precision,
200 p_unit2_precision => p_unit2_precision,
201 p_unit3_precision => p_unit3_precision,
202 p_unit1_aggregate => p_unit1_aggregate,
203 p_unit2_aggregate => p_unit2_aggregate,
207 p_bgt_unit3_available => l_budget_unit3_available);
204 p_unit3_aggregate => p_unit3_aggregate,
205 p_bgt_unit1_available => l_budget_unit1_available,
206 p_bgt_unit2_available => l_budget_unit2_available,
208 hr_utility.set_location('values returned are'||l_proc,110);
209 hr_utility.set_location('unit1_available'||l_budget_unit1_available||l_proc,113);
210 hr_utility.set_location('unit2_available'||l_budget_unit2_available||l_proc,114);
211 hr_utility.set_location('unit3_available'||l_budget_unit3_available||l_proc,115);
212 if nvl(p_budget_version_status,'X') in ('POST','ERROR') then
213 l_bgd_status := 'ERROR';
214 p_budget_version_status := 'ERROR';
215 else
216 l_bgd_status := '';
217 end if;
218 update_budget_detail
219 (
220 p_budget_detail_id => i.budget_detail_id,
221 p_budget_unit1_percent => l_budget_unit1_percent,
222 p_budget_unit1_value => l_budget_unit1_value,
223 p_budget_unit2_percent => l_budget_unit2_percent,
224 p_budget_unit2_value => l_budget_unit2_value,
225 p_budget_unit3_percent => l_budget_unit3_percent,
226 p_budget_unit3_value => l_budget_unit3_value,
227 p_budget_unit1_available => l_budget_unit1_available,
228 p_budget_unit2_available => l_budget_unit2_available,
229 p_budget_unit3_available => l_budget_unit3_available,
230 p_gl_status => l_bgd_status,
231 p_object_version_number => l_object_version_number
232 );
233 hr_utility.set_location('budget row updated '||l_proc,120);
234 end loop;
235 hr_utility.set_location('values passed out nocopy are'||l_proc,270);
236 p_bgv_unit1_available := round(p_bgv_unit1_available,p_unit1_precision);
237 p_bgv_unit2_available := round(p_bgv_unit2_available,p_unit2_precision);
238 p_bgv_unit3_available := round(p_bgv_unit3_available,p_unit3_precision);
239 hr_utility.set_location('unit1_available'||p_bgv_unit1_available||l_proc,273);
240 hr_utility.set_location('unit2_available'||p_bgv_unit2_available||l_proc,274);
241 hr_utility.set_location('unit3_available'||p_bgv_unit3_available||l_proc,275);
242 hr_utility.set_location('exiting '||l_proc,1000);
243 exception when others then
244 p_bgv_unit1_available := ini_budget_unit1_available;
245 p_bgv_unit2_available := ini_budget_unit2_available;
246 p_bgv_unit3_available := ini_budget_unit3_available;
247 raise;
248 end propagate_version_changes;
249
250 procedure propagate_budget_changes (p_change_mode in varchar2,
251 p_budget_detail_id in number,
252 p_new_bgt_unit1_value in number,
253 p_new_bgt_unit2_value in number,
254 p_new_bgt_unit3_value in number,
255 p_unit1_precision in number,
256 p_unit2_precision in number,
257 p_unit3_precision in number,
258 p_unit1_aggregate in varchar2,
259 p_unit2_aggregate in varchar2,
260 p_unit3_aggregate in varchar2,
261 p_bgt_unit1_available in out nocopy number,
262 p_bgt_unit2_available in out nocopy number,
263 p_bgt_unit3_available in out nocopy number
264 )is
265 cursor c1 is select budget_period_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,
266 budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
267 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
268 budget_unit1_available,budget_unit2_available,budget_unit3_available
269 from pqh_budget_periods
270 where budget_detail_id = p_budget_detail_id
271 for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
272 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
273 budget_unit1_available,budget_unit2_available,budget_unit3_available ;
274
275 l_period_unit1_value number;
276 l_period_unit2_value number;
277 l_period_unit3_value number;
278 l_period_unit1_percent number;
279 l_period_unit2_percent number;
280 l_period_unit3_percent number;
281 l_period_unit1_available number;
282 l_period_unit2_available number;
283 l_period_unit3_available number;
284 ini_budget_unit1_available number := p_bgt_unit1_available;
285 ini_budget_unit2_available number := p_bgt_unit2_available;
286 ini_budget_unit3_available number := p_bgt_unit3_available;
287 x_unit1_max number;
288 x_unit2_max number;
289 x_unit3_max number;
290 x_unit1_avg number;
291 x_unit2_avg number;
292 x_unit3_avg number;
293 x_unit1_sum number;
294 x_unit2_sum number;
295 x_unit3_sum number;
296 l_proc varchar2(100) := g_package||'propagate_budget_changes' ;
297 begin
298 hr_utility.set_location('entering '||l_proc,10);
299 if p_change_mode not in ('RP','RV','UE') then
300 hr_utility.set_message(8302,'PQH_WKS_PROPAGATION_METHOD_ERR');
301 hr_utility.raise_error;
302 end if;
303
304 /* make a call to sub_budgetrow to subtract the all period info. from the table*/
305 pqh_budget.sub_budgetrow(p_budget_detail_id => p_budget_detail_id,
306 p_unit1_aggregate => p_unit1_aggregate,
307 p_unit2_aggregate => p_unit2_aggregate,
308 p_unit3_aggregate => p_unit3_aggregate);
309
310 for i in c1 loop
311 hr_utility.set_location('for each period '||l_proc,20);
312 if p_change_mode ='RV' then
313 hr_utility.set_location('unit1 for RV '||l_proc,30);
314 if nvl(p_new_bgt_unit1_value,0) <> 0 then
315 l_period_unit1_percent := round((i.budget_unit1_value * 100)/p_new_bgt_unit1_value,2) ;
316 else
317 l_period_unit1_percent := null;
318 end if;
319 l_period_unit1_value := i.budget_unit1_value;
320 l_period_unit1_available := i.budget_unit1_available;
321 elsif p_change_mode ='RP' then
322 hr_utility.set_location('unit1 for RP '||l_proc,35);
323 if nvl(p_new_bgt_unit1_value,0) <> 0 then
324 l_period_unit1_value := round(p_new_bgt_unit1_value * nvl(i.budget_unit1_percent,0)/100,2) ;
325 l_period_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_period_unit1_value,0) - nvl(i.budget_unit1_value,0);
326 p_bgt_unit1_available := nvl(p_bgt_unit1_available,0) - nvl(l_period_unit1_value,0) + nvl(i.budget_unit1_value,0);
327 else
328 l_period_unit1_value := i.budget_unit1_value;
329 l_period_unit1_available := i.budget_unit1_available;
330 end if;
331 l_period_unit1_percent := i.budget_unit1_percent;
332 else
333 hr_utility.set_location('unit1 for UE '||l_proc,40);
334 if nvl(p_new_bgt_unit1_value,0) <> 0 then
335 if i.budget_unit1_value_type_cd = 'P' then
336 l_period_unit1_value := round(p_new_bgt_unit1_value * nvl(i.budget_unit1_percent,0)/100,2) ;
337 l_period_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_period_unit1_value,0) - nvl(i.budget_unit1_value,0);
338 p_bgt_unit1_available := nvl(p_bgt_unit1_available,0) - nvl(l_period_unit1_value,0) + nvl(i.budget_unit1_value,0);
339 l_period_unit1_percent := i.budget_unit1_percent;
340 else
341 l_period_unit1_value := i.budget_unit1_value;
342 l_period_unit1_available := i.budget_unit1_available;
343 l_period_unit1_percent := round((i.budget_unit1_value * 100)/p_new_bgt_unit1_value,2) ;
344 end if;
345 else
346 l_period_unit1_value := i.budget_unit1_value;
347 l_period_unit1_available := i.budget_unit1_available;
348 l_period_unit1_percent := null;
349 end if;
350 end if;
351
352 if p_change_mode ='RV' then
353 hr_utility.set_location('unit2 for RV '||l_proc,50);
354 if nvl(p_new_bgt_unit2_value,0) <> 0 then
355 l_period_unit2_percent := round((i.budget_unit2_value * 100)/p_new_bgt_unit2_value,2) ;
356 else
357 l_period_unit2_percent := null;
358 end if;
359 l_period_unit2_value := i.budget_unit2_value;
360 l_period_unit2_available := i.budget_unit2_available;
361 elsif p_change_mode ='RP' then
362 hr_utility.set_location('unit2 for RP '||l_proc,60);
363 if nvl(p_new_bgt_unit2_value,0) <> 0 then
364 l_period_unit2_value := round(p_new_bgt_unit2_value * nvl(i.budget_unit2_percent,0)/100,2) ;
365 l_period_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_period_unit2_value,0) - nvl(i.budget_unit2_value,0);
366 p_bgt_unit2_available := nvl(p_bgt_unit2_available,0) - nvl(l_period_unit2_value,0) + nvl(i.budget_unit2_value,0);
367 else
368 l_period_unit2_value := i.budget_unit2_value;
369 l_period_unit2_available := i.budget_unit2_available;
370 end if;
371 l_period_unit2_percent := i.budget_unit2_percent;
372 else
373 hr_utility.set_location('unit2 for UE '||l_proc,70);
374 if nvl(p_new_bgt_unit2_value,0) <> 0 then
375 if i.budget_unit2_value_type_cd = 'P' then
376 l_period_unit2_value := round(p_new_bgt_unit2_value * nvl(i.budget_unit2_percent,0)/100,2) ;
377 l_period_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_period_unit2_value,0) - nvl(i.budget_unit2_value,0);
378 p_bgt_unit2_available := nvl(p_bgt_unit2_available,0) - nvl(l_period_unit2_value,0) + nvl(i.budget_unit2_value,0);
379 l_period_unit2_percent := i.budget_unit2_percent;
380 else
381 l_period_unit2_value := i.budget_unit2_value;
382 l_period_unit2_available := i.budget_unit2_available;
383 l_period_unit2_percent := round((i.budget_unit2_value * 100)/p_new_bgt_unit2_value,2) ;
384 end if;
385 else
386 l_period_unit2_value := i.budget_unit2_value;
387 l_period_unit2_available := i.budget_unit2_available;
388 l_period_unit2_percent := null;
389 end if;
390 end if;
391
392 if p_change_mode ='RV' then
393 hr_utility.set_location('unit3 for RV '||l_proc,80);
394 if nvl(p_new_bgt_unit3_value,0) <> 0 then
395 l_period_unit3_percent := round((i.budget_unit3_value * 100)/p_new_bgt_unit3_value,2) ;
396 else
397 l_period_unit3_percent := null;
398 end if;
399 l_period_unit3_value := i.budget_unit3_value;
400 l_period_unit3_available := i.budget_unit3_available;
401 elsif p_change_mode ='RP' then
402 hr_utility.set_location('unit3 for RP '||l_proc,90);
403 if nvl(p_new_bgt_unit3_value,0) <> 0 then
404 l_period_unit3_value := round(p_new_bgt_unit3_value * nvl(i.budget_unit3_percent,0)/100,2) ;
405 l_period_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_period_unit3_value,0) - nvl(i.budget_unit3_value,0);
406 p_bgt_unit3_available := nvl(p_bgt_unit3_available,0) - nvl(l_period_unit3_value,0) + nvl(i.budget_unit3_value,0);
407 else
408 l_period_unit3_value := i.budget_unit3_value;
409 l_period_unit3_available := i.budget_unit3_available;
410 end if;
411 l_period_unit3_percent := i.budget_unit3_percent;
412 else
413 hr_utility.set_location('unit3 for UE '||l_proc,100);
414 if nvl(p_new_bgt_unit3_value,0) <> 0 then
415 if i.budget_unit3_value_type_cd = 'P' then
416 l_period_unit3_value := round(p_new_bgt_unit3_value * nvl(i.budget_unit3_percent,0)/100,2) ;
417 l_period_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_period_unit3_value,0) - nvl(i.budget_unit3_value,0);
418 p_bgt_unit3_available := nvl(p_bgt_unit3_available,0) - nvl(l_period_unit3_value,0) + nvl(i.budget_unit3_value,0);
419 l_period_unit3_percent := i.budget_unit3_percent;
420 else
421 l_period_unit3_value := i.budget_unit3_value;
422 l_period_unit3_available := i.budget_unit3_available;
423 l_period_unit3_percent := round((i.budget_unit3_value * 100)/p_new_bgt_unit3_value,2) ;
424 end if;
425 else
426 l_period_unit3_value := i.budget_unit3_value;
427 l_period_unit3_available := i.budget_unit3_available;
428 l_period_unit3_percent := null;
429 end if;
430 end if;
431 hr_utility.set_location('calling period changes with values '||l_proc,110);
432 hr_utility.set_location('unit1_value is '||l_period_unit1_value||l_proc,120);
433 hr_utility.set_location('unit2_value is '||l_period_unit2_value||l_proc,121);
434 hr_utility.set_location('unit3_value is '||l_period_unit3_value||l_proc,122);
435 hr_utility.set_location('unit1_available is '||l_period_unit1_available||l_proc,123);
436 hr_utility.set_location('unit2_available is '||l_period_unit2_available||l_proc,124);
437 hr_utility.set_location('unit3_available is '||l_period_unit3_available||l_proc,125);
438 propagate_period_changes (p_change_mode => p_change_mode,
439 p_budget_period_id => i.budget_period_id,
440 p_new_prd_unit1_value => l_period_unit1_value,
441 p_new_prd_unit2_value => l_period_unit2_value,
442 p_new_prd_unit3_value => l_period_unit3_value,
443 p_unit1_precision => p_unit1_precision,
444 p_unit2_precision => p_unit2_precision,
445 p_unit3_precision => p_unit3_precision,
446 p_prd_unit1_available => l_period_unit1_available,
447 p_prd_unit2_available => l_period_unit2_available,
448 p_prd_unit3_available => l_period_unit3_available);
449 hr_utility.set_location('after period changes values '||l_proc,130);
450 hr_utility.set_location('unit1_available is '||l_period_unit1_available||l_proc,133);
451 hr_utility.set_location('unit2_available is '||l_period_unit2_available||l_proc,134);
452 hr_utility.set_location('unit3_available is '||l_period_unit3_available||l_proc,135);
453 update pqh_budget_periods
454 set budget_unit1_value = l_period_unit1_value,
455 budget_unit2_value = l_period_unit2_value,
456 budget_unit3_value = l_period_unit3_value,
457 budget_unit1_percent = l_period_unit1_percent,
458 budget_unit2_percent = l_period_unit2_percent,
459 budget_unit3_percent = l_period_unit3_percent,
460 budget_unit1_available = l_period_unit1_available,
461 budget_unit2_available = l_period_unit2_available,
462 budget_unit3_available = l_period_unit3_available
463 where current of c1;
464 hr_utility.set_location('after period updated '||l_proc,140);
465 end loop;
466
467 /* make a call to add_budgetrow to add the all period info. from the table
468 and then get the available figures using each unit to be passed on to budget*/
469
470 pqh_budget.add_budgetrow(p_budget_detail_id => p_budget_detail_id,
471 p_unit1_aggregate => p_unit1_aggregate,
472 p_unit2_aggregate => p_unit2_aggregate,
473 p_unit3_aggregate => p_unit3_aggregate);
474 pqh_budget.chk_unit_sum(p_unit1_sum_value => x_unit1_sum,
475 p_unit2_sum_value => x_unit2_sum,
476 p_unit3_sum_value => x_unit3_sum);
477 pqh_budget.chk_unit_max(p_unit1_max_value => x_unit1_max,
478 p_unit2_max_value => x_unit2_max,
479 p_unit3_max_value => x_unit3_max);
480 pqh_budget.chk_unit_avg(p_unit1_avg_value => x_unit1_avg,
481 p_unit2_avg_value => x_unit2_avg,
482 p_unit3_avg_value => x_unit3_avg);
483 if p_unit1_aggregate ='ACCUMULATE' then
484 p_bgt_unit1_available := nvl(p_new_bgt_unit1_value,0) - nvl(x_unit1_sum,0);
485 elsif p_unit1_aggregate='MAXIMUM' then
486 p_bgt_unit1_available := nvl(p_new_bgt_unit1_value,0) - nvl(x_unit1_max,0);
487 elsif p_unit1_aggregate='AVERAGE' then
488 p_bgt_unit1_available := nvl(p_new_bgt_unit1_value,0) - nvl(x_unit1_avg,0);
489 end if;
490 if p_unit2_aggregate ='ACCUMULATE' then
491 p_bgt_unit2_available := nvl(p_new_bgt_unit2_value,0) - nvl(x_unit2_sum,0);
492 elsif p_unit2_aggregate='MAXIMUM' then
493 p_bgt_unit2_available := nvl(p_new_bgt_unit2_value,0) - nvl(x_unit2_max,0);
494 elsif p_unit2_aggregate='AVERAGE' then
495 p_bgt_unit2_available := nvl(p_new_bgt_unit2_value,0) - nvl(x_unit2_avg,0);
496 end if;
497 if p_unit3_aggregate ='ACCUMULATE' then
498 p_bgt_unit3_available := nvl(p_new_bgt_unit3_value,0) - nvl(x_unit3_sum,0);
499 elsif p_unit3_aggregate='MAXIMUM' then
500 p_bgt_unit3_available := nvl(p_new_bgt_unit3_value,0) - nvl(x_unit3_max,0);
501 elsif p_unit3_aggregate='AVERAGE' then
502 p_bgt_unit3_available := nvl(p_new_bgt_unit3_value,0) - nvl(x_unit3_avg,0);
503 end if;
504 hr_utility.set_location('values passed out nocopy are'||l_proc,150);
505 p_bgt_unit1_available := round(p_bgt_unit1_available,p_unit1_precision);
506 p_bgt_unit2_available := round(p_bgt_unit2_available,p_unit2_precision);
507 p_bgt_unit3_available := round(p_bgt_unit3_available,p_unit3_precision);
508 hr_utility.set_location('unit1_available is '||p_bgt_unit1_available||l_proc,153);
509 hr_utility.set_location('unit2_available is '||p_bgt_unit2_available||l_proc,154);
510 hr_utility.set_location('unit3_available is '||p_bgt_unit3_available||l_proc,155);
511 hr_utility.set_location('exiting '||l_proc,1000);
512 exception when others then
513 p_bgt_unit1_available := ini_budget_unit1_available;
514 p_bgt_unit2_available := ini_budget_unit2_available;
515 p_bgt_unit3_available := ini_budget_unit3_available;
516 raise;
517 end propagate_budget_changes;
518
519 procedure propagate_period_changes (p_change_mode in varchar2,
520 p_budget_period_id in number,
521 p_new_prd_unit1_value in number,
522 p_new_prd_unit2_value in number,
523 p_new_prd_unit3_value in number,
524 p_unit1_precision in number,
525 p_unit2_precision in number,
526 p_unit3_precision in number,
527 p_prd_unit1_available in out nocopy number,
528 p_prd_unit2_available in out nocopy number,
529 p_prd_unit3_available in out nocopy number
530 )is
531 cursor c1 is select budget_unit1_value,budget_unit2_value,budget_unit3_value,
532 budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
533 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
534 budget_unit1_available,budget_unit2_available,budget_unit3_available
535 from pqh_budget_sets
536 where budget_period_id = p_budget_period_id
537 for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
538 budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
539 budget_unit1_available,budget_unit2_available,budget_unit3_available ;
540
541 l_budgetset_unit1_value number;
542 l_budgetset_unit2_value number;
543 l_budgetset_unit3_value number;
544 l_budgetset_unit1_available number;
545 l_budgetset_unit2_available number;
546 l_budgetset_unit3_available number;
547 l_prd_unit1_available number := p_prd_unit1_available;
548 l_prd_unit2_available number := p_prd_unit2_available;
549 l_prd_unit3_available number := p_prd_unit3_available;
550 l_budgetset_unit1_percent number;
551 l_budgetset_unit2_percent number;
552 l_budgetset_unit3_percent number;
553
554 l_proc varchar2(100) := g_package||'propagate_period_changes' ;
555 begin
556 hr_utility.set_location('entering '||l_proc,10);
557 if p_change_mode not in ('RP','RV','UE') then
558 hr_utility.set_message(8302,'PQH_WKS_PROPAGATION_METHOD_ERR');
559 hr_utility.raise_error;
560 end if;
561 for i in c1 loop
562 if p_change_mode ='RV' then
563 hr_utility.set_location('unit1 for RV '||l_proc,20);
564 if nvl(p_new_prd_unit1_value,0) <> 0 then
565 l_budgetset_unit1_percent := round((i.budget_unit1_value * 100)/p_new_prd_unit1_value,2) ;
566 else
567 l_budgetset_unit1_percent := null;
568 end if;
569 l_budgetset_unit1_value := i.budget_unit1_value;
570 l_budgetset_unit1_available := i.budget_unit1_available;
571 elsif p_change_mode ='RP' then
572 hr_utility.set_location('unit1 for RP '||l_proc,30);
573 if nvl(p_new_prd_unit1_value,0) <> 0 then
574 l_budgetset_unit1_value := round(p_new_prd_unit1_value * nvl(i.budget_unit1_percent,0)/100,p_unit1_precision) ;
575 l_budgetset_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_budgetset_unit1_value,0) - nvl(i.budget_unit1_value,0);
576 p_prd_unit1_available := nvl(p_prd_unit1_available,0) - nvl(l_budgetset_unit1_value,0) + nvl(i.budget_unit1_value,0);
577 else
578 l_budgetset_unit1_value := i.budget_unit1_value;
579 l_budgetset_unit1_available := i.budget_unit1_available;
580 end if;
581 l_budgetset_unit1_percent := i.budget_unit1_percent;
582 else
583 hr_utility.set_location('unit1 for UE '||l_proc,40);
584 if nvl(p_new_prd_unit1_value,0) <> 0 then
585 if i.budget_unit1_value_type_cd = 'P' then
586 l_budgetset_unit1_value := round(p_new_prd_unit1_value * nvl(i.budget_unit1_percent,0)/100,p_unit1_precision) ;
587 l_budgetset_unit1_available := nvl(i.budget_unit1_available,0) + nvl(l_budgetset_unit1_value,0) - nvl(i.budget_unit1_value,0);
588 p_prd_unit1_available := nvl(p_prd_unit1_available,0) - nvl(l_budgetset_unit1_value,0) + nvl(i.budget_unit1_value,0);
589 l_budgetset_unit1_percent := i.budget_unit1_percent;
590 else
591 l_budgetset_unit1_percent := round((i.budget_unit1_value * 100)/p_new_prd_unit1_value,2) ;
592 l_budgetset_unit1_value := i.budget_unit1_value;
593 l_budgetset_unit1_available := i.budget_unit1_available;
594 end if;
595 else
596 l_budgetset_unit1_value := i.budget_unit1_value;
597 l_budgetset_unit1_available := i.budget_unit1_available;
598 l_budgetset_unit1_percent := null;
599 end if;
600 end if;
601
602 if p_change_mode ='RV' then
603 hr_utility.set_location('unit2 for RV '||l_proc,50);
604 if nvl(p_new_prd_unit2_value,0) <> 0 then
605 l_budgetset_unit2_percent := round((i.budget_unit2_value * 100)/p_new_prd_unit2_value,2) ;
606 else
607 l_budgetset_unit2_percent := null;
608 end if;
609 l_budgetset_unit2_value := i.budget_unit2_value;
610 l_budgetset_unit2_available := i.budget_unit2_available;
611 elsif p_change_mode ='RP' then
615 l_budgetset_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_budgetset_unit2_value,0) - nvl(i.budget_unit2_value,0);
612 hr_utility.set_location('unit2 for RP '||l_proc,60);
613 if nvl(p_new_prd_unit2_value,0) <> 0 then
614 l_budgetset_unit2_value := round(p_new_prd_unit2_value * nvl(i.budget_unit2_percent,0)/100,p_unit2_precision) ;
616 p_prd_unit2_available := nvl(p_prd_unit2_available,0) - nvl(l_budgetset_unit2_value,0) + nvl(i.budget_unit2_value,0);
617 else
618 l_budgetset_unit2_value := i.budget_unit2_value;
619 l_budgetset_unit2_available := i.budget_unit2_available;
620 end if;
621 l_budgetset_unit2_percent := i.budget_unit2_percent;
622 else
623 hr_utility.set_location('unit2 for UE '||l_proc,70);
624 if nvl(p_new_prd_unit2_value,0) <> 0 then
625 if i.budget_unit2_value_type_cd = 'P' then
626 l_budgetset_unit2_value := round(p_new_prd_unit2_value * nvl(i.budget_unit2_percent,0)/100,p_unit2_precision) ;
627 l_budgetset_unit2_available := nvl(i.budget_unit2_available,0) + nvl(l_budgetset_unit2_value,0) - nvl(i.budget_unit2_value,0);
628 p_prd_unit2_available := nvl(p_prd_unit2_available,0) - nvl(l_budgetset_unit2_value,0) + nvl(i.budget_unit2_value,0);
629 l_budgetset_unit2_percent := i.budget_unit2_percent;
630 else
631 l_budgetset_unit2_value := i.budget_unit2_value;
632 l_budgetset_unit2_available := i.budget_unit2_available;
633 l_budgetset_unit2_percent := round((i.budget_unit2_value * 100)/p_new_prd_unit2_value,2) ;
634 end if;
635 else
636 l_budgetset_unit2_value := i.budget_unit2_value;
637 l_budgetset_unit2_available := i.budget_unit2_available;
638 l_budgetset_unit2_percent := null;
639 end if;
640 end if;
641
642 if p_change_mode ='RV' then
643 hr_utility.set_location('unit3 for RV '||l_proc,80);
644 if nvl(p_new_prd_unit3_value,0) <> 0 then
645 l_budgetset_unit3_percent := round((i.budget_unit3_value * 100)/p_new_prd_unit3_value,2) ;
646 else
647 l_budgetset_unit3_percent := null;
648 end if;
649 l_budgetset_unit3_value := i.budget_unit3_value;
650 l_budgetset_unit3_available := i.budget_unit3_available;
651 elsif p_change_mode ='RP' then
652 hr_utility.set_location('unit3 for RP '||l_proc,90);
653 if nvl(p_new_prd_unit3_value,0) <> 0 then
654 l_budgetset_unit3_value := round(p_new_prd_unit3_value * nvl(i.budget_unit3_percent,0)/100,p_unit3_precision) ;
655 l_budgetset_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_budgetset_unit3_value,0) - nvl(i.budget_unit3_value,0);
656 p_prd_unit3_available := nvl(p_prd_unit3_available,0) - nvl(l_budgetset_unit3_value,0) + nvl(i.budget_unit3_value,0);
657 else
658 l_budgetset_unit3_value := i.budget_unit3_value;
659 l_budgetset_unit3_available := i.budget_unit3_available;
660 end if;
661 l_budgetset_unit3_percent := i.budget_unit3_percent;
662 else
663 hr_utility.set_location('unit3 for UE '||l_proc,100);
664 if nvl(p_new_prd_unit3_value,0) <> 0 then
665 if i.budget_unit3_value_type_cd = 'P' then
666 l_budgetset_unit3_value := round(p_new_prd_unit3_value * nvl(i.budget_unit3_percent,0)/100,p_unit3_precision) ;
667 l_budgetset_unit3_available := nvl(i.budget_unit3_available,0) + nvl(l_budgetset_unit3_value,0) - nvl(i.budget_unit3_value,0);
668 p_prd_unit3_available := nvl(p_prd_unit3_available,0) - nvl(l_budgetset_unit3_value,0) + nvl(i.budget_unit3_value,0);
669 l_budgetset_unit3_percent := i.budget_unit3_percent;
670 else
671 l_budgetset_unit3_value := i.budget_unit3_value;
672 l_budgetset_unit3_available := i.budget_unit3_available;
673 l_budgetset_unit3_percent := round((i.budget_unit3_value * 100)/p_new_prd_unit3_value,2) ;
674 end if;
675 else
676 l_budgetset_unit3_value := i.budget_unit3_value;
677 l_budgetset_unit3_available := i.budget_unit3_available;
678 l_budgetset_unit3_percent := null;
679 end if;
680 end if;
681 hr_utility.set_location('before update values passed are '||l_proc,110);
682 hr_utility.set_location('unit1_value '||l_budgetset_unit1_value||l_proc,120);
683 hr_utility.set_location('unit2_value '||l_budgetset_unit2_value||l_proc,121);
684 hr_utility.set_location('unit3_value '||l_budgetset_unit3_value||l_proc,122);
685 hr_utility.set_location('unit1_percent '||l_budgetset_unit1_percent||l_proc,123);
686 hr_utility.set_location('unit2_percent '||l_budgetset_unit2_percent||l_proc,124);
687 hr_utility.set_location('unit3_percent '||l_budgetset_unit3_percent||l_proc,125);
688 hr_utility.set_location('unit1_available '||l_budgetset_unit1_available||l_proc,126);
689 hr_utility.set_location('unit2_available '||l_budgetset_unit2_available||l_proc,127);
690 hr_utility.set_location('unit3_available '||l_budgetset_unit3_available||l_proc,128);
691 update pqh_budget_sets
692 set budget_unit1_value = l_budgetset_unit1_value,
693 budget_unit2_value = l_budgetset_unit2_value,
694 budget_unit3_value = l_budgetset_unit3_value,
695 budget_unit1_percent = l_budgetset_unit1_percent,
696 budget_unit2_percent = l_budgetset_unit2_percent,
697 budget_unit3_percent = l_budgetset_unit3_percent,
698 budget_unit1_available = l_budgetset_unit1_available,
699 budget_unit2_available = l_budgetset_unit2_available,
700 budget_unit3_available = l_budgetset_unit3_available
701 where current of c1;
702 end loop;
703 hr_utility.set_location('after update out nocopy values passed are '||l_proc,130);
704 p_prd_unit1_available := round(p_prd_unit1_available,p_unit1_precision);
708 hr_utility.set_location('unit2_available '||p_prd_unit2_available||l_proc,137);
705 p_prd_unit2_available := round(p_prd_unit2_available,p_unit2_precision);
706 p_prd_unit3_available := round(p_prd_unit3_available,p_unit3_precision);
707 hr_utility.set_location('unit1_available '||p_prd_unit1_available||l_proc,136);
709 hr_utility.set_location('unit3_available '||p_prd_unit3_available||l_proc,138);
710 hr_utility.set_location('exiting '||l_proc,1000);
711 exception when others then
712 p_prd_unit1_available := l_prd_unit1_available;
713 p_prd_unit2_available := l_prd_unit2_available;
714 p_prd_unit3_available := l_prd_unit3_available;
715 raise;
716 end propagate_period_changes;
717
718 procedure insert_budget_detail(
719 p_budget_version_id in number,
720 p_organization_id in number default null,
721 p_job_id in number default null,
722 p_position_id in number default null,
723 p_grade_id in number default null,
724 p_budget_unit1_percent in number default null,
725 p_budget_unit1_value in number default null,
726 p_budget_unit2_percent in number default null,
727 p_budget_unit2_value in number default null,
728 p_budget_unit3_percent in number default null,
729 p_budget_unit3_value in number default null,
730 p_budget_unit1_value_type_cd in varchar2 default null,
731 p_budget_unit2_value_type_cd in varchar2 default null,
732 p_budget_unit3_value_type_cd in varchar2 default null,
733 p_gl_status in varchar2 default null,
734 p_budget_unit1_available in number default null,
735 p_budget_unit2_available in number default null,
736 p_budget_unit3_available in number default null,
737 p_budget_detail_id out nocopy number
738 ) is
739 l_object_version_number number;
740 begin
741 pqh_budget_details_api.create_budget_detail(
742 p_validate => FALSE
743 ,p_budget_detail_id => p_budget_detail_id
744 ,p_budget_version_id => p_budget_version_id
745 ,p_organization_id => p_organization_id
746 ,p_position_id => p_position_id
747 ,p_job_id => p_job_id
748 ,p_grade_id => p_grade_id
749 ,p_gl_status => p_gl_status
750 ,p_budget_unit1_value => p_budget_unit1_value
751 ,p_budget_unit1_percent => p_budget_unit1_percent
752 ,p_budget_unit1_available => p_budget_unit1_available
753 ,p_budget_unit1_value_type_cd => p_budget_unit1_value_type_cd
754 ,p_budget_unit2_value => p_budget_unit2_value
755 ,p_budget_unit2_percent => p_budget_unit2_percent
756 ,p_budget_unit2_available => p_budget_unit2_available
757 ,p_budget_unit2_value_type_cd => p_budget_unit2_value_type_cd
758 ,p_budget_unit3_value => p_budget_unit3_value
759 ,p_budget_unit3_percent => p_budget_unit3_percent
760 ,p_budget_unit3_available => p_budget_unit3_available
761 ,p_budget_unit3_value_type_cd => p_budget_unit3_value_type_cd
762 ,p_object_version_number => l_object_version_number
763 );
764 exception when others then
765 p_budget_detail_id := null;
766 raise;
767 end insert_budget_detail;
768
769 Procedure update_budget_detail
770 (
771 p_budget_detail_id in number,
772 p_budget_version_id in number default hr_api.g_number,
773 p_organization_id in number default hr_api.g_number,
774 p_job_id in number default hr_api.g_number,
775 p_position_id in number default hr_api.g_number,
776 p_grade_id in number default hr_api.g_number,
777 p_budget_unit1_percent in number default hr_api.g_number,
778 p_budget_unit1_value in number default hr_api.g_number,
779 p_budget_unit2_percent in number default hr_api.g_number,
780 p_budget_unit2_value in number default hr_api.g_number,
781 p_budget_unit3_percent in number default hr_api.g_number,
782 p_budget_unit3_value in number default hr_api.g_number,
783 p_object_version_number in out nocopy number,
784 p_budget_unit1_value_type_cd in varchar2 default hr_api.g_varchar2,
785 p_budget_unit2_value_type_cd in varchar2 default hr_api.g_varchar2,
786 p_budget_unit3_value_type_cd in varchar2 default hr_api.g_varchar2,
787 p_gl_status in varchar2 default hr_api.g_varchar2,
788 p_budget_unit1_available in number default hr_api.g_number,
789 p_budget_unit2_available in number default hr_api.g_number,
790 p_budget_unit3_available in number default hr_api.g_number
791 ) is
792 l_proc varchar2(61) := g_package||'Update_bgd';
793 l_object_version_number number := p_object_version_number;
794 begin
795 hr_utility.set_location('entering'||l_proc,10);
796 hr_utility.set_location('bgd id is'||p_budget_detail_id||l_proc,11);
797 hr_utility.set_location('ovn is'||p_object_version_number||l_proc,12);
798 pqh_budget_details_api.update_budget_detail(
799 p_validate => FALSE
800 ,p_budget_detail_id => p_budget_detail_id
801 ,p_budget_version_id => p_budget_version_id
802 ,p_organization_id => p_organization_id
803 ,p_position_id => p_position_id
804 ,p_job_id => p_job_id
805 ,p_grade_id => p_grade_id
809 ,p_budget_unit1_value_type_cd => p_budget_unit1_value_type_cd
806 ,p_budget_unit1_value => p_budget_unit1_value
807 ,p_budget_unit1_percent => p_budget_unit1_percent
808 ,p_budget_unit1_available => p_budget_unit1_available
810 ,p_budget_unit2_value => p_budget_unit2_value
811 ,p_budget_unit2_percent => p_budget_unit2_percent
812 ,p_budget_unit2_available => p_budget_unit2_available
813 ,p_budget_unit2_value_type_cd => p_budget_unit2_value_type_cd
814 ,p_budget_unit3_value => p_budget_unit3_value
815 ,p_budget_unit3_percent => p_budget_unit3_percent
816 ,p_budget_unit3_available => p_budget_unit3_available
817 ,p_budget_unit3_value_type_cd => p_budget_unit3_value_type_cd
818 ,p_gl_status => p_gl_status
819 ,p_object_version_number => p_object_version_number
820 );
821 hr_utility.set_location('wkd id is'||p_budget_detail_id||l_proc,20);
822 hr_utility.set_location('ovn is'||p_object_version_number||l_proc,30);
823 hr_utility.set_location('exiting'||l_proc,100);
824 exception when others then
825 p_object_version_number := l_object_version_number;
826 raise;
827 end update_budget_detail;
828 procedure bgv_date_validation( p_budget_id in number,
829 p_version_number in number ,
830 p_date_from in date,
831 p_date_to in date,
832 p_bgv_ll_date out nocopy date,
833 p_bgv_ul_date out nocopy date,
834 p_status out nocopy varchar2) is
835 l_max_version number;
836 l_min_version number;
837 cursor c0 is select max(version_number) from pqh_budget_versions
838 where budget_id = p_budget_id ;
839 cursor c1 is select min(version_number) from pqh_budget_versions
840 where budget_id = p_budget_id ;
841 -- cursor to fetch the end_date of the last_version
842 cursor c2 is select date_to from pqh_budget_versions
843 where version_number = l_max_version
844 and budget_id = p_budget_id;
845 -- cursor to fetch next version from the current version
846 cursor c5 is select date_from from pqh_budget_versions
847 where budget_id = p_budget_id
848 and version_number = (select min(version_number)
849 from pqh_budget_versions
850 where budget_id = p_budget_id
851 and version_number > p_version_number) ;
852 -- cursor to fetch previous version from the current version
853 cursor c6 is select date_to from pqh_budget_versions
854 where budget_id = p_budget_id
855 and version_number = (select max(version_number)
856 from pqh_budget_versions
857 where budget_id = p_budget_id
858 and version_number < p_version_number) ;
859 l_max_end_date date;
860 l_min_start_date date;
861 l_ver_end_date date;
862 l_next_ver_start_date date;
863 l_prev_ver_end_date date;
864 l_ver_chk varchar2(15);
865 l_ver_start_date date;
866 l_proc varchar2(61) := g_package ||'bgv_date_validation' ;
867 begin
868 hr_utility.set_location('entering '||l_proc,10);
869 hr_utility.set_location('ver # entered is '||p_version_number||l_proc,10);
870 hr_utility.set_location('start_date is '||p_date_from||l_proc,10);
871 hr_utility.set_location('end_date is '||p_date_to||l_proc,10);
872 if p_budget_id is null then
873 hr_utility.set_message(8302,'PQH_INVALID_BUDGET');
874 hr_utility.raise_error;
875 elsif p_version_number is null then
876 hr_utility.set_message(8302,'PQH_INVALID_VERSION_FOR_BDGT');
877 hr_utility.raise_error;
878 elsif p_date_from is null then
879 hr_utility.set_message(8302,'PQH_START_DT_NULL');
880 hr_utility.raise_error;
881 elsif p_date_to is null then
882 hr_utility.set_message(8302,'PQH_END_DT_NULL');
883 hr_utility.raise_error;
884 elsif p_date_from > p_date_to then
885 hr_utility.set_message(8302,'PQH_INVALID_END_DT');
886 /* Commented and added as a part of Bug#10239077 Starts
887 hr_utility.set_message_token('STARTDATE',fnd_date.date_to_chardate(p_date_from));
888 hr_utility.set_message_token('ENDDATE',fnd_date.date_to_chardate(p_date_to)); */
889 hr_utility.set_message_token('STARTDATE',fnd_date.date_to_chardate(p_date_from, calendar_aware => FND_DATE.calendar_aware_alt));
890 hr_utility.set_message_token('ENDDATE',fnd_date.date_to_chardate(p_date_to, calendar_aware => FND_DATE.calendar_aware_alt));
891 /* Commented and added as a part of Bug#10239077 Ends */
892 hr_utility.raise_error;
893 end if;
894 -- we are here to correct a existing version or checking for the new version to be entered.
895 open c0;
896 fetch c0 into l_max_version;
897 close c0;
898 hr_utility.set_location('max_version is'||l_max_version||l_proc,70);
899 open c1;
900 fetch c1 into l_min_version;
901 close c1;
902 hr_utility.set_location('min_version is'||l_min_version||l_proc,80);
903 hr_utility.set_location('max_end_date is'||l_max_end_date||l_proc,90);
904 hr_utility.set_location('min_start_date is'||l_min_start_date||l_proc,100);
905 if p_version_number = l_max_version then
906 open c6;
907 fetch c6 into l_prev_ver_end_date;
908 close c6;
909 hr_utility.set_location('version_number = max_version'||l_proc,140);
910 -- last version is getting corrected
911 if p_version_number = l_min_version then
912 hr_utility.set_location('version_number = min version'||l_proc,142);
913 -- There is only one version and we are working on it.so any date user enters is okay.
914 p_status := 'SUCCESS' ;
915 else
916 hr_utility.set_location('Lower limit should be > '||l_prev_ver_end_date||l_proc,162);
917 if p_date_from > l_prev_ver_end_date then
918 hr_utility.set_location('between valid dates '||l_proc,145);
919 p_status := 'SUCCESS' ;
920 else
921 hr_utility.set_location('not between valid dates '||l_proc,148);
922 p_bgv_ll_date := l_prev_ver_end_date;
923 p_status := 'ERROR' ;
924 end if;
925 end if;
926 elsif p_version_number > l_max_version then
927 -- new version is getting added
928 open c2;
929 fetch c2 into l_max_end_date;
930 close c2;
931 hr_utility.set_location('version_number > max_version'||l_proc,150);
932 hr_utility.set_location('Lower limit should be > '||l_max_end_date||l_proc,162);
933 if p_date_from > l_max_end_date then
934 hr_utility.set_location('between valid dates '||l_proc,155);
935 p_status := 'SUCCESS' ;
936 else
937 hr_utility.set_location('not between valid dates '||l_proc,158);
938 p_bgv_ll_date := l_max_end_date;
939 p_status := 'ERROR' ;
940 end if;
941 elsif p_version_number = l_min_version then
942 open c5;
943 fetch c5 into l_next_ver_start_date;
944 close c5;
945 -- first version is getting corrected
946 -- but end date should be equal to ver_end_date
947 hr_utility.set_location('version_number = min_version'||l_proc,160);
948 hr_utility.set_location('Upper limit should be < '||l_next_ver_start_date||l_proc,162);
949 if p_date_to < l_next_ver_start_date then
950 hr_utility.set_location('between valid dates '||l_proc,165);
951 p_status := 'SUCCESS' ;
952 else
953 hr_utility.set_location('not between valid dates '||l_proc,168);
954 p_bgv_ul_date := l_next_ver_start_date;
955 p_status := 'ERROR' ;
956 end if;
957 else
958 open c5;
959 fetch c5 into l_next_ver_start_date;
960 close c5;
961 open c6;
962 fetch c6 into l_prev_ver_end_date;
963 close c6;
964 hr_utility.set_location('version_number in middle '||l_proc,170);
965 -- a version in middle is getting corrected
966 -- in this case start date of the worksheet should be less than or equal to start date
967 -- of the version and worksheet end date should be greater than or equal to version
968 -- and also version start date should be between last_ver_end_date and next_ver_start_date
969 -- and also version_end_date should be between last_ver_end date and next_ver_start_date
970 if p_date_from between l_prev_ver_end_date+1 and l_next_ver_start_date-1
971 and p_date_to between l_prev_ver_end_date+1 and l_next_ver_start_date-1 then
972 hr_utility.set_location('between valid dates '||l_proc,175);
973 p_status := 'SUCCESS' ;
974 else
975 hr_utility.set_location('not between valid dates '||l_proc,178);
976 p_bgv_ll_date := l_prev_ver_end_date+1;
977 p_bgv_ul_date := l_next_ver_start_date-1;
978 p_status := 'ERROR' ;
979 end if;
980 end if;
981 hr_utility.set_location('end of validation with status'||p_status||l_proc,270);
982 exception when others then
983 p_bgv_ll_date := null;
984 p_bgv_ul_date := null;
985 p_status := null;
986 raise;
987 end bgv_date_validation;
988
989 function gl_post(p_budget_version_id in number) return number is
990 l_req number := -1;
991 begin
992 l_req := fnd_request.submit_request(application => 'PQH',
993 program => 'PQHGLPOST',
994 argument1 => p_budget_version_id);
995 return l_req;
996 end gl_post;
997
998 end pqh_bdgt;