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