[Home] [Help]
PACKAGE BODY: APPS.PQH_WKS_BUDGET
Source
1 package body pqh_wks_budget as
2 /* $Header: pqwksbud.pkb 120.4 2011/02/11 09:39:51 apjaiswa ship $ */
3 g_package varchar2(100) := 'PQH_WKS_BUDGET.' ;
4
5 function get_currency_precision(p_currency_code in varchar2) return number is
6 cursor c1 is select precision
7 from fnd_currencies
8 where currency_code = p_currency_code;
9 l_precision number;
10 begin
11 open c1;
12 fetch c1 into l_precision;
13 close c1;
14 return l_precision;
15 end;
16 function valid_grade(p_position_id in number default null,
17 p_job_id in number default null,
18 p_grade_id in number) return varchar2 is
19 /*
20 cursor c1 is select 'x' from per_valid_grades
21 where (position_id = p_position_id or position_id is null)
22 and (job_id = p_job_id or job_id is null)
23 and grade_id = p_grade_id ;
24 */
25 l_check varchar2(1);
26 begin
27 return 'TRUE';
28 /*
29 -- Commented the grade code to return everything as valid for the time being
30 if p_position_id is null and p_job_id is null then
31 hr_utility.set_location('All grades valid',10);
32 return 'TRUE';
33 else
34 open c1;
35 fetch c1 into l_check;
36 if c1%notfound then
37 hr_utility.set_location('invalid grade:'||p_grade_id||' for pos:'||p_position_id||' and job:'||p_job_id,20);
38 return 'FALSE';
39 else
40 hr_utility.set_location('valid grade:'||p_grade_id||' for pos:'||p_position_id||' and job:'||p_job_id,30);
41 return 'TRUE';
42 end if;
43 end if;
44 */
45 end;
46
47 function can_apply(p_worksheet_detail_id in number) return varchar2 is
48 cursor c1 is select parent_worksheet_detail_id from pqh_worksheet_details
49 where worksheet_detail_id = p_worksheet_detail_id;
50 l_apply varchar2(30) := 'Y';
51 begin
52 for i in c1 loop
53 if i.parent_worksheet_detail_id is null then
54 l_apply := 'Y' ;
55 else
56 l_apply := 'N' ;
57 end if;
58 end loop;
59 return l_apply;
60 end;
61
62 function can_approve(p_worksheet_detail_id in number) return varchar2 is
63 cursor c1 is select status from pqh_worksheet_details
64 where action_cd ='D'
65 and parent_worksheet_detail_id = p_worksheet_detail_id;
66 l_approve varchar2(30) := 'YES';
67 -- as discussed with dinesh, right now, we will return force when there is any delegated worksheet
68 -- which is not approved else YES will be returned. No option can be used in future.
69 begin
70 for i in c1 loop
71 if i.status not in ('APPROVED') then
72 l_approve := 'FORCE';
73 end if;
74 end loop;
75 return l_approve;
76 end;
77 procedure get_all_unit_desc(p_worksheet_detail_id in number,
78 p_unit1_desc out nocopy varchar2,
79 p_unit2_desc out nocopy varchar2,
80 p_unit3_desc out nocopy varchar2) is
81 begin
82 pqh_utility.get_all_unit_desc(p_worksheet_detail_id => p_worksheet_detail_id,
83 p_unit1_desc => p_unit1_desc,
84 p_unit2_desc => p_unit2_desc,
85 p_unit3_desc => p_unit3_desc);
86 exception when others then
87 p_unit1_desc := null;
88 p_unit2_desc := null;
89 p_unit3_desc := null;
90 raise;
91 end;
92
93 /*
94 this procedure commented out nocopy here and code being to pqh_utility package
95
96
97 Procedure get_all_unit_desc(p_worksheet_detail_id in number,
98 p_unit1_desc out nocopy varchar2,
99 p_unit2_desc out nocopy varchar2,
100 p_unit3_desc out nocopy varchar2) is
101 cursor c1 is select budget_unit1_id,budget_unit2_id,budget_unit3_id
102 from pqh_budgets bgt,pqh_worksheets wks, pqh_worksheet_details wkd
103 where wkd.worksheet_id = wks.worksheet_id
104 and wks.budget_id = bgt.budget_id
105 and wkd.worksheet_detail_id = p_worksheet_detail_id;
106 l_budget_unit1_id pqh_budgets.budget_unit1_id%type;
107 l_budget_unit2_id pqh_budgets.budget_unit1_id%type;
108 l_budget_unit3_id pqh_budgets.budget_unit1_id%type;
109 begin
110 if p_worksheet_detail_id is not null then
111 begin
112 open c1;
113 fetch c1 into l_budget_unit1_id,l_budget_unit2_id,l_budget_unit3_id;
114 close c1;
115 exception
116 when others then
117 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
118 hr_utility.raise_error;
119 end;
120 p_unit1_desc := get_unit_desc(l_budget_unit1_id);
121 if l_budget_unit2_id is not null then
122 p_unit2_desc := get_unit_desc(l_budget_unit2_id);
123 else
124 p_unit2_desc := null;
125 end if;
126 if l_budget_unit3_id is not null then
127 p_unit3_desc := get_unit_desc(l_budget_unit3_id);
128 else
129 p_unit3_desc := null;
130 end if;
131 else
132 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
133 hr_utility.raise_error;
134 end if;
135 exception
136 when others then
137 p_unit1_desc := null;
138 p_unit2_desc := null;
139 p_unit3_desc := null;
140 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
141 hr_utility.raise_error;
142 end get_all_unit_desc;
143 */
144
145 function get_org_hier(p_org_structure_version_id in number) return varchar2 is
146 cursor c1 is select ors.name
147 from per_org_structure_versions osv, per_organization_structures ors
148 where osv.organization_structure_id = ors.organization_structure_id
149 and osv.org_structure_version_id = p_org_structure_version_id;
150 l_org_hier varchar2(100);
151 begin
152 open c1;
153 fetch c1 into l_org_hier;
154 close c1;
155 return l_org_hier;
156 end;
157
158 function get_unit_type(p_unit_id in number) return varchar2 is
159 cursor c1 is select system_type_cd
160 from per_shared_types
161 where lookup_type ='BUDGET_MEASUREMENT_TYPE'
162 and shared_type_id = p_unit_id;
163 l_system_type_cd per_shared_types_vl.system_type_cd%type;
164 begin
165 open c1;
166 fetch c1 into l_system_type_cd;
167 close c1;
168 return l_system_type_cd;
169 exception
170 when others then
171 hr_utility.set_message(8302,'PQH_INVALID_UNIT_ENTERED');
172 hr_utility.raise_error;
173 end get_unit_type;
174
175 function get_unit_desc(p_unit_id in number) return varchar2 is
176 l_unit_name per_shared_types_vl.shared_type_name%type;
177 begin
178 l_unit_name := pqh_utility.get_unit_desc(p_unit_id);
179 return l_unit_name;
180 end;
181 /*
182 This function moved from here to shared aru component package pqh_utility
183 so that process log form could use it.
184
185 function get_unit_desc(p_unit_id in number) return varchar2 is
186 cursor c1 is select shared_type_name
187 from per_shared_types_vl
188 where lookup_type ='BUDGET_MEASUREMENT_TYPE'
189 and shared_type_id = p_unit_id;
190 l_shared_type_name per_shared_types_vl.shared_type_name%type;
191 begin
192 open c1;
193 fetch c1 into l_shared_type_name;
194 close c1;
195 return l_shared_type_name;
196 exception
197 when others then
198 hr_utility.set_message(8302,'PQH_INVALID_UNIT_ENTERED');
199 hr_utility.raise_error;
200 end get_unit_desc;
201 */
202 function get_parent_value(p_worksheet_detail_id in number,
203 p_worksheet_propagate_code in varchar2) return varchar2 is
204 cursor c1(p_worksheet_detail_id number) is
205 select parent_worksheet_detail_id,worksheet_id
206 from pqh_worksheet_details
207 where worksheet_detail_id = p_worksheet_detail_id ;
208 cursor c2(p_worksheet_detail_id number) is
209 select propagation_method,worksheet_detail_id
210 from pqh_worksheet_details
211 where worksheet_detail_id = p_worksheet_detail_id ;
212 cursor c3(p_worksheet_id number) is
213 select propagation_method
214 from pqh_worksheets
215 where worksheet_id = p_worksheet_id ;
216 l_worksheet_detail_id number;
217 l_parent_worksheet_detail_id number;
218 l_worksheet_id number;
219 l_code varchar2(3);
220 l_proc varchar2(100) := g_package||'get_parent_value' ;
221 begin
222 hr_utility.set_location('entering '||l_proc,10);
223 if p_worksheet_detail_id is not null then
224 begin
225 open c1(p_worksheet_detail_id);
226 fetch c1 into l_parent_worksheet_detail_id,l_worksheet_id;
227 close c1;
228 exception
229 when others then
230 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
231 hr_utility.raise_error;
232 end;
233 if l_parent_worksheet_detail_id is null then
234 hr_utility.set_location('parent null '||l_proc,20);
235 begin
236 open c3(l_worksheet_id);
237 fetch c3 into l_code;
238 close c3;
239 exception
240 when others then
241 hr_utility.set_message(8302,'PQH_INVALID_WKS_PASSED');
242 hr_utility.raise_error;
243 end;
244 return l_code;
245 else
246 hr_utility.set_location('parent not null '||l_parent_worksheet_detail_id||l_proc,30);
247 begin
248 open c2(l_parent_worksheet_detail_id);
249 fetch c2 into l_code,l_worksheet_detail_id;
250 close c2;
251 exception
252 when others then
253 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
254 hr_utility.raise_error;
255 end;
256 if l_code = 'PC' then
257 l_code := get_parent_value(l_worksheet_detail_id,p_worksheet_propagate_code);
258 end if;
259 hr_utility.set_location('l_code is '||l_code||l_proc,40);
260 return l_code;
261 end if;
262 else
263 l_code := p_worksheet_propagate_code;
264 hr_utility.set_location('l_code is '||l_code||l_proc,50);
265 return l_code;
266 end if;
267 end;
268 function get_value(p_worksheet_detail_id in number,
269 p_worksheet_propagate_code in varchar2,
270 code in varchar2) return varchar2 is
271 l_code varchar2(3);
272 l_meaning varchar2(80);
273 l_meaning1 varchar2(80);
274 l_proc varchar2(100) := g_package||'get_value' ;
275 begin
276 hr_utility.set_location('entering '||code||l_proc,10);
277 if code = 'PC' then
278 l_code := get_parent_value(p_worksheet_detail_id,p_worksheet_propagate_code);
279 l_meaning := hr_general.decode_lookup('PQH_WORKSHEET_PROPAGATE_METHOD','PC');
280 l_meaning1 := hr_general.decode_lookup('PQH_WORKSHEET_PROPAGATE_METHOD',l_code);
281 l_meaning := l_meaning||'('||l_meaning1||')' ;
282 elsif code in ('RV','RP','UE') then
283 l_meaning := hr_general.decode_lookup('PQH_WORKSHEET_PROPAGATE_METHOD',code);
284 else
285 hr_utility.set_message(8302,'PQH_INVALID_PROPAGATION_METHOD');
286 hr_utility.raise_error;
287 end if;
288 hr_utility.set_location('exiting with meaning'||l_meaning||l_proc,50);
289 return l_meaning;
290 end get_value;
291 function lookup_desc(p_lookup_type in varchar2,
292 p_lookup_code in varchar2) return varchar2 is
293 cursor c1 is select nvl(description,meaning) description
294 from hr_lookups
295 where lookup_type = p_lookup_type
296 and lookup_code = p_lookup_code;
297 l_desc varchar2(240);
298 begin
299 for i in c1 loop
300 l_desc := i.description;
301 exit;
302 end loop;
303 return l_desc;
304 end lookup_desc;
305 procedure wkd_propagation_method(p_worksheet_detail_id in number,
306 p_propagation_method out nocopy varchar2 ) is
307 cursor c0 is select worksheet_id,propagation_method from pqh_worksheet_details
308 where worksheet_detail_id = p_worksheet_detail_id ;
309 l_change_mode varchar2(3);
310 l_wks_change_mode varchar2(3);
311 l_effective_change_mode varchar2(3);
312 l_worksheet_id number;
313 cursor c1 is select propagation_method from pqh_worksheets
314 where worksheet_id = l_worksheet_id ;
315 begin
316 begin
317 open c0;
318 fetch c0 into l_worksheet_id,l_change_mode ;
319 close c0;
320 exception
321 when others then
322 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
323 hr_utility.raise_error;
324 end;
325 if l_change_mode = 'PC' then
326 open c1;
327 fetch c1 into l_wks_change_mode;
328 close c1;
329 l_effective_change_mode := get_parent_value(p_worksheet_detail_id,l_wks_change_mode);
330 p_propagation_method := l_effective_change_mode;
331 else
332 p_propagation_method := l_change_mode;
333 end if;
334 exception when others then
335 p_propagation_method := null;
336 raise;
337 end wkd_propagation_method;
338 procedure get_bgt_unit_precision(p_budget_id in number,
339 p_unit1_precision out nocopy number,
340 p_unit2_precision out nocopy number,
341 p_unit3_precision out nocopy number ) is
342 cursor c1 is select currency_code,budget_unit1_id,budget_unit2_id,budget_unit3_id
343 from pqh_budgets
344 where budget_id = p_budget_id;
345 l_currency_code varchar2(15);
346 l_unit1_id number;
347 l_unit2_id number;
348 l_unit3_id number;
349 l_unit1_type varchar2(30);
350 l_unit2_type varchar2(30);
351 l_unit3_type varchar2(30);
352 begin
353 open c1;
354 fetch c1 into l_currency_code,l_unit1_id,l_unit2_id,l_unit3_id;
355 close c1;
356 if l_unit1_id is not null then
357 l_unit1_type := get_unit_type(l_unit1_id);
358 if l_unit1_type ='MONEY' then
359 p_unit1_precision := get_currency_precision(p_currency_code => l_currency_code);
360 else
361 p_unit1_precision := 2;
362 end if;
363 end if;
364 if l_unit2_id is not null then
365 l_unit2_type := get_unit_type(l_unit2_id);
366 if l_unit2_type ='MONEY' then
367 p_unit2_precision := get_currency_precision(p_currency_code => l_currency_code);
368 else
369 p_unit2_precision := 2;
370 end if;
371 end if;
372 if l_unit3_id is not null then
373 l_unit3_type := get_unit_type(l_unit3_id);
374 if l_unit3_type ='MONEY' then
375 p_unit3_precision := get_currency_precision(p_currency_code => l_currency_code);
376 else
377 p_unit3_precision := 2;
378 end if;
379 end if;
380 exception when others then
381 p_unit1_precision := null;
382 p_unit2_precision := null;
383 p_unit3_precision := null;
384 raise;
385 end get_bgt_unit_precision;
386 procedure get_wks_unit_precision(p_worksheet_id in number,
387 p_unit1_precision out nocopy number,
388 p_unit2_precision out nocopy number,
389 p_unit3_precision out nocopy number ) is
390 cursor c1 is select budget_id
391 from pqh_worksheets
392 where worksheet_id = p_worksheet_id;
393 l_budget_id number;
394 begin
395 open c1;
396 fetch c1 into l_budget_id;
397 close c1;
398 get_bgt_unit_precision(p_budget_id => l_budget_id,
399 p_unit1_precision => p_unit1_precision,
400 p_unit2_precision => p_unit2_precision,
401 p_unit3_precision => p_unit3_precision);
402 exception when others then
403 p_unit1_precision := null;
404 p_unit2_precision := null;
405 p_unit3_precision := null;
406 raise;
407 end get_wks_unit_precision;
408 procedure get_wkd_unit_precision(p_worksheet_detail_id in number,
409 p_unit1_precision out nocopy number,
410 p_unit2_precision out nocopy number,
411 p_unit3_precision out nocopy number ) is
412 l_budget_id number;
413 cursor c1 is select wks.budget_id
414 from pqh_worksheet_details wkd, pqh_worksheets wks
415 where wkd.worksheet_detail_id = p_worksheet_detail_id
416 and wkd.worksheet_id = wks.worksheet_id;
417 begin
418 open c1;
419 fetch c1 into l_budget_id;
420 close c1;
421 get_bgt_unit_precision(p_budget_id => l_budget_id,
422 p_unit1_precision => p_unit1_precision,
423 p_unit2_precision => p_unit2_precision,
424 p_unit3_precision => p_unit3_precision);
425 exception when others then
426 p_unit1_precision := null;
427 p_unit2_precision := null;
428 p_unit3_precision := null;
429 raise;
430 end;
431 procedure get_wks_unit_aggregate(p_worksheet_id in number,
432 p_unit1_aggregate out nocopy varchar2,
433 p_unit2_aggregate out nocopy varchar2,
434 p_unit3_aggregate out nocopy varchar2 ) is
435 cursor c1 is select bgt.budget_unit1_aggregate,bgt.budget_unit2_aggregate,bgt.budget_unit3_aggregate
436 from pqh_worksheets wks, pqh_budgets bgt
437 where wks.worksheet_id = p_worksheet_id
438 and wks.budget_id = bgt.budget_id;
439 begin
440 for i in c1 loop
441 p_unit1_aggregate := i.budget_unit1_aggregate;
442 p_unit2_aggregate := i.budget_unit2_aggregate;
443 p_unit3_aggregate := i.budget_unit3_aggregate;
444 end loop;
445 exception when others then
446 p_unit1_aggregate := null;
447 p_unit2_aggregate := null;
448 p_unit3_aggregate := null;
449 raise;
450 end get_wks_unit_aggregate;
451 procedure get_wkd_unit_aggregate(p_worksheet_detail_id in number,
452 p_unit1_aggregate out nocopy varchar2,
453 p_unit2_aggregate out nocopy varchar2,
454 p_unit3_aggregate out nocopy varchar2 ) is
455 cursor c1 is select bgt.budget_unit1_aggregate,bgt.budget_unit2_aggregate,bgt.budget_unit3_aggregate
456 from pqh_worksheets wks, pqh_worksheet_details wkd, pqh_budgets bgt
457 where wks.worksheet_id = wkd.worksheet_id
458 and wks.budget_id = bgt.budget_id
459 and wkd.worksheet_detail_id = p_worksheet_detail_id;
460 begin
461 for i in c1 loop
462 p_unit1_aggregate := i.budget_unit1_aggregate;
463 p_unit2_aggregate := i.budget_unit2_aggregate;
464 p_unit3_aggregate := i.budget_unit3_aggregate;
465 end loop;
466 exception when others then
467 p_unit1_aggregate := null;
468 p_unit2_aggregate := null;
469 p_unit3_aggregate := null;
470 raise;
471 end get_wkd_unit_aggregate;
472 procedure insert_budgetset(p_dflt_budget_set_id number,
473 p_worksheet_budget_set_id number) IS
474 cursor c1 is select dflt_budget_element_id,element_type_id,dflt_dist_percentage
475 from pqh_dflt_budget_elements pbe
476 where dflt_budget_set_id = p_dflt_budget_set_id ;
477 cursor c2(p_dflt_budget_element_id number) is
478 select project_id, award_id, task_id,
479 organization_id, expenditure_type,
480 cost_allocation_keyflex_id,dflt_dist_percentage
481 from pqh_dflt_fund_srcs
482 where dflt_budget_element_id = p_dflt_budget_element_id ;
483 l_worksheet_bdgt_elmnt_id number(15) ;
484 l_worksheet_fund_src_id number(15) ;
485 l_object_version_number number;
486 l_count number(15) ;
487 BEGIN
488 if p_worksheet_budget_set_id is not null then
489 if p_dflt_budget_set_id is null then
490 hr_utility.set_message(8302,'PQH_WKS_MIG_INV_SET');
491 hr_utility.raise_error;
492 else
493 select count(*) into l_count from pqh_worksheet_bdgt_elmnts
494 where worksheet_budget_set_id = p_worksheet_budget_set_id ;
495 if l_count = 0 then
496 for i in c1 loop
497 pqh_worksheet_bdgt_elmnts_api.create_worksheet_bdgt_elmnt(
498 p_validate => FALSE
499 ,p_worksheet_budget_set_id => p_worksheet_budget_set_id
500 ,p_worksheet_bdgt_elmnt_id => l_worksheet_bdgt_elmnt_id
501 ,p_element_type_id => i.element_type_id
502 ,p_object_version_number => l_object_version_number
503 ,p_distribution_percentage => i.dflt_dist_percentage
504 );
505 for j in c2(i.dflt_budget_element_id) loop
506 pqh_worksheet_fund_srcs_api.create_worksheet_fund_src(
507 p_validate => FALSE
508 ,p_worksheet_fund_src_id => l_worksheet_fund_src_id
509 ,p_worksheet_bdgt_elmnt_id => l_worksheet_bdgt_elmnt_id
510 ,p_cost_allocation_keyflex_id => j.cost_allocation_keyflex_id
511 ,p_project_id => j.project_id
512 ,p_award_id => j.award_id
513 ,p_task_id => j.task_id
514 ,p_organization_id => j.organization_id
515 ,p_expenditure_type => j.expenditure_type
516 ,p_object_version_number => l_object_version_number
517 ,p_distribution_percentage => j.dflt_dist_percentage
518 );
519 end loop;
520 end loop;
521 end if;
522 end if;
523 end if;
524 end insert_budgetset;
525 procedure insert_budgetset(p_dflt_budget_set_id number,
526 p_budget_set_id number) IS
527 cursor c1 is select dflt_budget_element_id,element_type_id,dflt_dist_percentage
528 from pqh_dflt_budget_elements pbe
529 where dflt_budget_set_id = p_dflt_budget_set_id ;
530 cursor c2(p_dflt_budget_element_id number) is
531 select project_id, award_id, task_id,
532 organization_id, expenditure_type,
533 cost_allocation_keyflex_id,dflt_dist_percentage
534 from pqh_dflt_fund_srcs
535 where dflt_budget_element_id = p_dflt_budget_element_id ;
536 l_budget_element_id number(15) ;
537 l_budget_fund_src_id number(15) ;
538 l_count number(15) ;
539 l_object_version_number number;
540 BEGIN
541 if p_budget_set_id is not null then
542 if p_dflt_budget_set_id is null then
543 hr_utility.set_message(8302,'PQH_WKS_MIG_INV_SET');
544 hr_utility.raise_error;
545 else
546 select count(*) into l_count from pqh_budget_elements
547 where budget_set_id = p_budget_set_id ;
548 if l_count = 0 then
549 for i in c1 loop
550 pqh_budget_elements_api.create_budget_element(
551 p_validate => FALSE
552 ,p_budget_set_id => p_budget_set_id
553 ,p_budget_element_id => l_budget_element_id
554 ,p_element_type_id => i.element_type_id
555 ,p_object_version_number => l_object_version_number
556 ,p_distribution_percentage => i.dflt_dist_percentage
557 );
558 for j in c2(i.dflt_budget_element_id) loop
559 pqh_budget_fund_srcs_api.create_budget_fund_src(
560 p_validate => FALSE
561 ,p_budget_fund_src_id => l_budget_fund_src_id
562 ,p_budget_element_id => l_budget_element_id
563 ,p_cost_allocation_keyflex_id => j.cost_allocation_keyflex_id
564 ,p_project_id => j.project_id
565 ,p_award_id => j.award_id
566 ,p_task_id => j.task_id
567 ,p_organization_id => j.organization_id
568 ,p_expenditure_type => j.expenditure_type
569 ,p_object_version_number => l_object_version_number
570 ,p_distribution_percentage => j.dflt_dist_percentage
571 );
572 end loop;
573 end loop;
574 end if;
575 end if;
576 end if;
577 end insert_budgetset;
578 procedure delegating_org (p_worksheet_detail_id in number,
579 p_forwarded_by_user_id in number,
580 p_member_cd in varchar,
581 p_action_date in date,
582 p_transaction_category_id in number) is
583 cursor c1 is select worksheet_detail_id,user_id,status,defer_flag,object_version_number
584 from pqh_worksheet_details
585 where action_cd ='D'
586 and parent_worksheet_detail_id = p_worksheet_detail_id
587 and nvl(defer_flag,'N') = 'N'
588 and user_id is not null
589 and organization_id is not null
590 and status = 'DELEGATE'
591 for update of status;
592 cursor c2(p_user_id number) is
593 select user_name
594 from fnd_user
595 where user_id = p_user_id ;
596 l_proc varchar2(200) := g_package||'Delegating org' ;
597 l_user_name varchar2(100);
598 l_object_version_number number;
599 l_transaction_name varchar2(200);
600 l_apply_error_mesg varchar2(200);
601 l_apply_error_num varchar2(30);
602 begin
603 hr_utility.set_location('entering '||l_proc,10);
604 for i in c1 loop
605 hr_utility.set_location('inside loop '||l_proc,11);
606 begin
607 open c2(i.user_id);
608 fetch c2 into l_user_name;
609 close c2;
610 exception
611 when others then
612 hr_utility.set_location('user name fetch raised error '||l_proc,20);
613 raise;
614 end;
615 hr_utility.set_location('user name is '||l_user_name||l_proc,30);
616 hr_utility.set_location('calling process user action'||l_proc,40);
617 begin
618 l_transaction_name := get_transaction_name(p_worksheet_detail_id => i.worksheet_detail_id);
619 pqh_wf.process_user_action(p_transaction_category_id => p_transaction_category_id,
620 p_transaction_id => i.worksheet_detail_id,
621 p_route_to_user => l_user_name,
622 p_forwarded_to_user_id => i.user_id,
623 p_forwarded_by_user_id => p_forwarded_by_user_id,
624 p_effective_date => p_action_date,
625 p_member_cd => p_member_cd,
626 p_user_action_cd => 'DELEGATE',
627 p_transaction_name => l_transaction_name,
628 p_apply_error_mesg => l_apply_error_mesg,
629 p_apply_error_num => l_apply_error_num);
630 exception
631 when others then
632 hr_utility.set_location('process user action raised error'||l_proc,50);
633 raise;
634 end;
635 hr_utility.set_location('going to update status'||l_proc,60);
636 l_object_version_number := i.object_version_number;
637 pqh_budget.update_worksheet_detail(
638 p_worksheet_detail_id => i.worksheet_detail_id,
639 p_effective_date => trunc(sysdate),
640 p_object_version_number => l_object_version_number,
641 p_status => 'DELEGATED'
642 );
643 hr_utility.set_location('updated status'||l_proc,60);
644 end loop;
645 hr_utility.set_location('leaving '||l_proc,1000);
646 end delegating_org;
647 procedure wks_date_validation( p_worksheet_mode in varchar2,
648 p_budget_id in number,
649 p_budget_version_id in number default null,
650 p_wks_start_date in date,
651 p_wks_end_date in date,
652 p_wks_ll_date out nocopy date,
653 p_wks_ul_date out nocopy date,
654 p_status out nocopy varchar2) is
655 l_max_version number;
656 cursor c0 is select max(version_number) from pqh_budget_versions
657 where budget_id = p_budget_id ;
658 cursor c1 is select 'x' from pqh_budget_versions
659 where budget_version_id = p_budget_version_id
660 and budget_id = p_budget_id;
661 cursor c2 is select date_to from pqh_budget_versions
662 where version_number = l_max_version
663 and budget_id = p_budget_id;
664 cursor c3 is select version_number from pqh_budget_versions
665 where budget_version_id = p_budget_version_id;
666 l_max_end_date date;
667 l_ver_chk varchar2(15);
668 l_version_number number;
669 l_proc varchar2(61) := g_package ||'wks_date_validation' ;
670 begin
671 hr_utility.set_location('entering '||l_proc,10);
672 -- mode N is edit and make new version
673 -- mode S is start from scratch
674 -- mode O is copy and edit version
675 if p_worksheet_mode not in ('N','S','O') then
676 hr_utility.set_message(8302,'PQH_INVALID_WORKSHEET_MODE');
677 hr_utility.raise_error;
678 elsif p_budget_id is null then
679 hr_utility.set_message(8302,'PQH_INVALID_BUDGET');
680 hr_utility.raise_error;
681 elsif p_worksheet_mode in ('O','N') and p_budget_version_id is null then
682 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_VERSION');
683 hr_utility.raise_error;
684 elsif p_wks_start_date is null then
685 hr_utility.set_message(8302,'PQH_START_DT_NULL');
686 hr_utility.raise_error;
687 elsif p_wks_end_date is null then
688 hr_utility.set_message(8302,'PQH_END_DT_NULL');
689 hr_utility.raise_error;
690 elsif p_wks_start_date > p_wks_end_date then
691 hr_utility.set_message(8302,'PQH_INVALID_END_DT');
692 /* Commented and added as a part of Bug#10239077 Starts
693 hr_utility.set_message_token('STARTDATE',fnd_date.date_to_chardate(p_wks_start_date));
694 hr_utility.set_message_token('ENDDATE',fnd_date.date_to_chardate(p_wks_end_date)); */
695 hr_utility.set_message_token('STARTDATE',fnd_date.date_to_chardate(p_wks_start_date, calendar_aware => FND_DATE.calendar_aware_alt));
696 hr_utility.set_message_token('ENDDATE',fnd_date.date_to_chardate(p_wks_end_date, calendar_aware => FND_DATE.calendar_aware_alt));
697 /* Commented and added as a part of Bug#10239077 Ends*/
698 hr_utility.raise_error;
699 end if;
700 if p_budget_version_id is not null then
701 open c1;
702 fetch c1 into l_ver_chk;
703 if c1%notfound then
704 close c1;
705 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_VER');
706 hr_utility.raise_error;
707 end if;
708 close c1;
709 end if;
710 hr_utility.set_location('wks_mode is '||p_worksheet_mode||l_proc,20);
711 if p_worksheet_mode in ('S','N') then
712 -- in the case of start from scratch, worksheet dates should be greater than all the existing
713 -- version dates as it is going to make a new version in all cases, if the dates
714 -- are not highest in that case status is returned as error
715 -- budget_version_id may be null but budget_id should be there.
716 -- gaps are also ok
717 open c0;
718 fetch c0 into l_max_version;
719 close c0;
720 hr_utility.set_location('max_version is '||l_max_version||l_proc,30);
721 open c2;
722 fetch c2 into l_max_end_date;
723 if c2%notfound then
724 hr_utility.set_location('max_end_date notfound '||l_proc,40);
725 if p_worksheet_mode ='S' then
726 -- no dates in the budget version as it is a initial case so
727 p_status := 'SUCCESS' ;
728 else
729 -- mode is correction but no records
730 p_status := 'ERROR' ;
731 end if;
732 else
733 hr_utility.set_location('max_end_date is '||l_max_end_date||l_proc,50);
734 if l_max_end_date is not null then
735 if p_wks_start_date = l_max_end_date + 1 then
736 -- start date is valid as it is not overlapping and nor giving any gap.
737 p_wks_ll_date := l_max_end_date+1 ;
738 p_wks_ul_date := l_max_end_date+1 ;
739 p_status := 'SUCCESS' ;
740 elsif p_wks_start_date >= l_max_end_date +1 then
741 -- gaps will be there but no overlapping
742 p_wks_ll_date := l_max_end_date+1 ;
743 p_status := 'SUCCESS' ;
744 else
745 -- invalid start date
746 p_wks_ll_date := l_max_end_date+1 ;
747 p_status := 'ERROR' ;
748 end if;
749 else
750 p_status := 'SUCCESS' ;
751 end if;
752 end if;
753 close c2;
754 hr_utility.set_location('end of validation with status'||p_status||l_proc,60);
755 else
756 open c3;
757 fetch c3 into l_version_number;
758 close c3;
759 pqh_bdgt.bgv_date_validation( p_budget_id => p_budget_id,
760 p_version_number => l_version_number ,
761 p_date_from => p_wks_start_date,
762 p_date_to => p_wks_end_date,
763 p_bgv_ll_date => p_wks_ll_date,
764 p_bgv_ul_date => p_wks_ul_date,
765 p_status => p_status ) ;
766 hr_utility.set_location('end of validation with status'||p_status||l_proc,170);
767 end if;
768 exception when others then
769 p_wks_ll_date := null;
770 p_wks_ul_date := null;
771 p_status := 'ERROR';
772 raise;
773 end wks_date_validation;
774
775 procedure propagate_bottom_up(p_worksheet_detail_id in number,
776 p_budget_unit1_value in out nocopy number,
777 p_budget_unit2_value in out nocopy number,
778 p_budget_unit3_value in out nocopy number,
779 p_status out nocopy varchar2
780 ) is
781
782
783 init_budget_unit1_value number := p_budget_unit1_value;
784 init_budget_unit2_value number := p_budget_unit2_value;
785 init_budget_unit3_value number := p_budget_unit3_value;
786
787 cursor c1 is select worksheet_detail_id,object_version_number,
788 budget_unit1_value,budget_unit2_value,budget_unit3_value,
789 old_unit1_value,old_unit2_value,old_unit3_value
790 from pqh_worksheet_details
791 where parent_worksheet_detail_id = p_worksheet_detail_id
792 and action_cd ='D';
793 l_object_version_number number;
794 l_budget_unit1_value number;
795 l_budget_unit2_value number;
796 l_budget_unit3_value number;
797 l_lck_success boolean := FALSE;
798 l_status varchar2(30) ;
799 begin
800 hr_utility.set_location('entering bootom_up for wkd'||p_worksheet_detail_id,05);
801 for i in c1 loop
802 hr_utility.set_location('inside the loop for wkd'||i.worksheet_detail_id,10);
803 begin
804 hr_utility.set_location('going to lock'||i.worksheet_detail_id,20);
805 pqh_wdt_shd.lck(p_worksheet_detail_id => i.worksheet_detail_id,
806 p_object_version_number => i.object_version_number );
807 l_lck_success := TRUE;
808 hr_utility.set_location('lock success',30);
809 exception
810 when others then
811 hr_utility.set_location('lock failed',40);
812 l_lck_success := FALSE;
813 if p_status <> 'LOCK' then
814 p_status := 'LOCK';
815 end if;
816 end;
817 if l_lck_success then
818 hr_utility.set_location('going for propagation',50);
819 l_object_version_number := i.object_version_number;
820 l_budget_unit1_value := i.budget_unit1_value;
821 l_budget_unit2_value := i.budget_unit2_value;
822 l_budget_unit3_value := i.budget_unit3_value;
823 hr_utility.set_location('calling propagate bottom_up',60);
824 begin
825 propagate_bottom_up(p_worksheet_detail_id => i.worksheet_detail_id,
826 p_budget_unit1_value => l_budget_unit1_value,
827 p_budget_unit2_value => l_budget_unit2_value,
828 p_budget_unit3_value => l_budget_unit3_value,
829 p_status => l_status);
830 end;
831 if nvl(l_status,'X') = 'LOCK' then
832 p_status := 'LOCK';
833 end if;
834 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(l_budget_unit1_value,0) ;
835 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(l_budget_unit1_value,0) ;
836 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(i.budget_unit1_value,0) ;
837 pqh_budget.update_worksheet_detail(
838 p_worksheet_detail_id => i.worksheet_detail_id,
839 p_object_version_number => l_object_version_number,
840 p_effective_date => trunc(sysdate),
841 p_budget_unit1_value => l_budget_unit1_value,
842 p_budget_unit2_value => l_budget_unit2_value,
843 p_budget_unit3_value => l_budget_unit3_value,
844 p_old_unit1_value => l_budget_unit1_value,
845 p_old_unit2_value => l_budget_unit2_value,
846 p_old_unit3_value => l_budget_unit3_value);
847 end if;
848 hr_utility.set_location('end of the loop for wkd'||i.worksheet_detail_id,120);
849 end loop;
850 hr_utility.set_location('exiting propagate_bottom_up for wkd'||p_worksheet_detail_id,130);
851 exception when others then
852 p_budget_unit1_value := init_budget_unit1_value;
853 p_budget_unit2_value := init_budget_unit2_value;
854 p_budget_unit3_value := init_budget_unit3_value;
855 p_status := null;
856 raise;
857 end propagate_bottom_up;
858 procedure populate_bud_grades(p_budget_version_id in number,
859 p_business_group_id in number,
860 p_rows_inserted out nocopy number) is
861 l_budget_start_date date;
862 l_budget_end_date date;
863 l_valid_grade_flag pqh_budgets.valid_grade_reqd_flag%type;
864 l_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%type;
865
866 cursor c0 is select budget_start_date,budget_end_date,valid_grade_reqd_flag,budgeted_entity_cd
867 from pqh_budgets bgt, pqh_budget_versions bgv
868 where bgv.budget_id = bgt.budget_id
869 and bgv.budget_version_id = p_budget_version_id;
870 cursor c1 is select grade_id from per_grades a
871 where business_group_id = p_business_group_id
872 and ((nvl(l_valid_grade_flag,'N') = 'Y' and l_budgeted_entity_cd = 'GRADE' and
873 a.grade_id in (select b.grade_id from per_valid_grades b
874 where b.date_from < l_budget_end_date
875 and (b.date_to > l_budget_start_date or b.date_to is null)))
876 or (nvl(l_valid_grade_flag,'N') = 'N' and date_from < l_budget_end_date
877 and (date_to > l_budget_start_date or date_to is null)))
878 and pqh_budget.already_budgeted_grd(a.grade_id) = 'FALSE' ;
879 l_budget_detail_id number;
880 l_rows_inserted number := 0;
881 l_object_version_number number := 1;
882 l_proc varchar2(100) := g_package||'populate_bud_grades' ;
883 begin
884 hr_utility.set_location('entering '||l_proc,10);
885 open c0;
886 fetch c0 into l_budget_start_date,l_budget_end_date,l_valid_grade_flag,l_budgeted_entity_cd;
887 close c0;
888 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
889 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
890 for i in c1 loop
891 l_rows_inserted := l_rows_inserted + 1;
892 pqh_budget_details_api.create_budget_detail(
893 p_validate => FALSE
894 ,p_budget_detail_id => l_budget_detail_id
895 ,p_budget_version_id => p_budget_version_id
896 ,p_organization_id => ''
897 ,p_position_id => ''
898 ,p_job_id => ''
899 ,p_grade_id => i.grade_id
900 ,p_budget_unit1_value => ''
901 ,p_budget_unit1_percent => ''
902 ,p_budget_unit1_available => ''
903 ,p_budget_unit1_value_type_cd => ''
904 ,p_budget_unit2_value => ''
905 ,p_budget_unit2_percent => ''
906 ,p_budget_unit2_available => ''
907 ,p_budget_unit2_value_type_cd => ''
908 ,p_budget_unit3_value => ''
909 ,p_budget_unit3_percent => ''
910 ,p_budget_unit3_available => ''
911 ,p_budget_unit3_value_type_cd => ''
912 ,p_object_version_number => l_object_version_number
913 );
914 pqh_budget.insert_grd_is_bud(i.grade_id);
915 end loop;
916 p_rows_inserted := l_rows_inserted;
917 hr_utility.set_location('exiting '||l_proc,1000);
918 exception when others then
919 p_rows_inserted := null;
920 raise;
921 end populate_bud_grades;
922
923 procedure populate_bud_jobs(p_budget_version_id in number,
924 p_business_group_id in number,
925 p_rows_inserted out nocopy number) is
926 l_budget_start_date date;
927 l_budget_end_date date;
928 cursor c0 is select budget_start_date,budget_end_date
929 from pqh_budgets bgt, pqh_budget_versions bgv
930 where bgv.budget_id = bgt.budget_id
931 and bgv.budget_version_id = p_budget_version_id;
932 cursor c1 is select job_id from per_jobs job, per_job_groups jgr
933 where job.job_group_id = jgr.job_group_id and jgr.internal_name = 'HR_' || job.business_group_id
934 and job.business_group_id = p_business_group_id
935 and date_from < l_budget_end_date
936 and (date_to > l_budget_start_date or date_to is null)
937 and pqh_budget.already_budgeted_job(job_id) = 'FALSE';
938 l_budget_detail_id number;
939 l_rows_inserted number := 0;
940 l_object_version_number number := 1;
941 l_proc varchar2(100) := g_package||'populate_bud_jobs' ;
942 begin
943 hr_utility.set_location('entering '||l_proc,10);
944 open c0;
945 fetch c0 into l_budget_start_date,l_budget_end_date;
946 close c0;
947 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
948 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
949 for i in c1 loop
950 l_rows_inserted := l_rows_inserted + 1;
951 pqh_budget_details_api.create_budget_detail(
952 p_validate => FALSE
953 ,p_budget_detail_id => l_budget_detail_id
954 ,p_budget_version_id => p_budget_version_id
955 ,p_organization_id => ''
956 ,p_position_id => ''
957 ,p_job_id => i.job_id
958 ,p_grade_id => ''
959 ,p_budget_unit1_value => ''
960 ,p_budget_unit1_percent => ''
961 ,p_budget_unit1_available => ''
962 ,p_budget_unit1_value_type_cd => ''
963 ,p_budget_unit2_value => ''
964 ,p_budget_unit2_percent => ''
965 ,p_budget_unit2_available => ''
966 ,p_budget_unit2_value_type_cd => ''
967 ,p_budget_unit3_value => ''
968 ,p_budget_unit3_percent => ''
969 ,p_budget_unit3_available => ''
970 ,p_budget_unit3_value_type_cd => ''
971 ,p_object_version_number => l_object_version_number
972 );
973 pqh_budget.insert_job_is_bud(i.job_id);
974 end loop;
975 p_rows_inserted := l_rows_inserted;
976 hr_utility.set_location('exiting '||l_proc,1000);
977 exception when others then
978 p_rows_inserted := null;
979 raise;
980 end populate_bud_jobs;
981 procedure populate_bud_positions(p_budget_version_id in number,
982 p_org_hier_ver in number,
983 p_start_organization_id in number,
984 p_business_group_id in number,
985 p_rows_inserted out nocopy number) is
986 l_budget_start_date date;
987 l_budget_end_date date;
988 cursor c0 is select budget_start_date,budget_end_date
989 from pqh_budgets bgt, pqh_budget_versions bgv
990 where bgv.budget_id = bgt.budget_id
991 and bgv.budget_version_id = p_budget_version_id;
992 cursor c1 is select position_id,job_id,pos.organization_id organization_id
993 from hr_positions pos,hr_organization_units org
994 where org.business_group_id = p_business_group_id
995 and pos.business_group_id = p_business_group_id
996 and pos.organization_id = org.organization_id
997 and pos.effective_start_date < l_budget_end_date
998 and pos.effective_end_date > l_budget_start_date
999 and pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
1000 and get_position_budget_flag(pos.availability_status_id) = 'Y';
1001 cursor c2 is select distinct --Added Distinct to eliminate duplicates from the cursor to fix the bug#10284825.
1002 position_id,job_id,organization_id
1003 from ( select organization_id_child from pqh_worksheet_organizations_v
1004 where org_structure_version_id = p_org_hier_ver
1005 connect by prior organization_id_child = organization_id_parent
1006 and org_structure_version_id = p_org_hier_ver
1007 start with organization_id_parent = p_start_organization_id
1008 and org_structure_version_id = p_org_hier_ver
1009 union
1010 select p_start_organization_id organization_id_child from dual )x,
1011 hr_positions_f
1012 where pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
1013 and get_position_budget_flag(availability_status_id) = 'Y'
1014 and effective_start_date < l_budget_end_date
1015 and effective_end_date > l_budget_start_date
1016 and organization_id = x.organization_id_child ;
1017 l_budget_detail_id number;
1018 l_rows_inserted number := 0;
1019 l_object_version_number number := 1;
1020 l_proc varchar2(100) := g_package||'populate_budget_positions' ;
1021 begin
1022 hr_utility.set_location('entering '||l_proc,10);
1023 hr_utility.set_location('business_group_id is '||p_business_group_id||l_proc,11);
1024 hr_utility.set_location('org_hier is '||p_org_hier_ver||l_proc,13);
1025 hr_utility.set_location('start organization is '||p_start_organization_id||l_proc,15);
1026 open c0;
1027 fetch c0 into l_budget_start_date, l_budget_end_date;
1028 close c0;
1029 if p_org_hier_ver is null then
1030 hr_utility.set_location('Business group cursor selected '||l_proc,20);
1031 for i in c1 loop
1032 l_rows_inserted := l_rows_inserted + 1;
1033 pqh_budget_details_api.create_budget_detail(
1034 p_validate => FALSE
1035 ,p_budget_detail_id => l_budget_detail_id
1036 ,p_budget_version_id => p_budget_version_id
1037 ,p_organization_id => i.organization_id
1038 ,p_position_id => i.position_id
1039 ,p_job_id => i.job_id
1040 ,p_grade_id => ''
1041 ,p_budget_unit1_value => ''
1042 ,p_budget_unit1_percent => ''
1043 ,p_budget_unit1_available => ''
1044 ,p_budget_unit1_value_type_cd => ''
1045 ,p_budget_unit2_value => ''
1046 ,p_budget_unit2_percent => ''
1047 ,p_budget_unit2_available => ''
1048 ,p_budget_unit2_value_type_cd => ''
1049 ,p_budget_unit3_value => ''
1050 ,p_budget_unit3_percent => ''
1051 ,p_budget_unit3_available => ''
1052 ,p_budget_unit3_value_type_cd => ''
1053 ,p_object_version_number => l_object_version_number
1054 );
1055 pqh_budget.insert_pos_is_bud(i.position_id);
1056 hr_utility.set_location('position inserted '||i.position_id||l_proc,40);
1057 end loop;
1058 else
1059 hr_utility.set_location('Org hierarchy cursor selected '||l_proc,45);
1060 for i in c2 loop
1061 l_rows_inserted := l_rows_inserted + 1;
1062 pqh_budget_details_api.create_budget_detail(
1063 p_validate => FALSE
1064 ,p_budget_detail_id => l_budget_detail_id
1065 ,p_budget_version_id => p_budget_version_id
1066 ,p_organization_id => i.organization_id
1067 ,p_position_id => i.position_id
1068 ,p_job_id => i.job_id
1069 ,p_grade_id => ''
1070 ,p_budget_unit1_value => ''
1071 ,p_budget_unit1_percent => ''
1072 ,p_budget_unit1_available => ''
1073 ,p_budget_unit1_value_type_cd => ''
1074 ,p_budget_unit2_value => ''
1075 ,p_budget_unit2_percent => ''
1076 ,p_budget_unit2_available => ''
1077 ,p_budget_unit2_value_type_cd => ''
1078 ,p_budget_unit3_value => ''
1079 ,p_budget_unit3_percent => ''
1080 ,p_budget_unit3_available => ''
1081 ,p_budget_unit3_value_type_cd => ''
1082 ,p_object_version_number => l_object_version_number
1083 );
1084 pqh_budget.insert_pos_is_bud(i.position_id);
1085 hr_utility.set_location('position inserted '||i.position_id||l_proc,50);
1086 end loop;
1087 end if;
1088 p_rows_inserted := l_rows_inserted;
1089 hr_utility.set_location('exiting '||l_proc,90);
1090 exception when others then
1091 p_rows_inserted := null;
1092 raise;
1093 end populate_bud_positions;
1094 procedure populate_bud_organizations(p_budget_version_id in number,
1095 p_org_hier_ver in number,
1096 p_start_organization_id in number,
1097 p_business_group_id in number,
1098 p_rows_inserted out nocopy number) is
1099 l_budget_start_date date;
1100 l_budget_end_date date;
1101 cursor c0 is select budget_start_date,budget_end_date
1102 from pqh_budgets bgt, pqh_budget_versions bgv
1103 where bgv.budget_id = bgt.budget_id
1104 and bgv.budget_version_id = p_budget_version_id;
1105 cursor c1 is select organization_id
1106 from hr_all_organization_units
1107 where business_group_id = p_business_group_id
1108 and date_from < l_budget_end_date
1109 and (date_to > l_budget_start_date or date_to is null)
1110 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1111 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', ORGANIZATION_ID))='TRUE'
1112 --and decode(hr_general.get_xbg_profile,'Y', business_group_id , hr_general.get_business_group_id) = business_group_id
1113 and pqh_budget.already_budgeted_org(organization_id) = 'FALSE';
1114 cursor c2 is select w.organization_id_child organization_id
1115 from pqh_worksheet_organizations_v w
1116 where org_structure_version_id = p_org_hier_ver
1117 and pqh_budget.already_budgeted_org(w.organization_id_child) = 'FALSE'
1118 and exists
1119 (select null
1120 from hr_all_organization_units hao
1121 where organization_id = w.organization_id_child
1122 and date_from < l_budget_end_date
1123 and (date_to > l_budget_start_date or date_to is null)
1124 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1125 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' )
1126 --AND decode(hr_general.get_xbg_profile,'Y', hao.business_group_id , hr_general.get_business_group_id) = hao.business_group_id)
1127 connect by prior organization_id_child = organization_id_parent
1128 and org_structure_version_id = p_org_hier_ver
1129 start with organization_id_parent = p_start_organization_id
1130 and org_structure_version_id = p_org_hier_ver
1131 union
1132 select organization_id
1133 from hr_all_organization_units hao
1134 where organization_id = p_start_organization_id
1135 and pqh_budget.already_budgeted_org(p_start_organization_id) = 'FALSE'
1136 and date_from < l_budget_end_date
1137 and (date_to > l_budget_start_date or date_to is null)
1138 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1139 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' ;
1140 --AND decode(hr_general.get_xbg_profile,'Y', hao.business_group_id ,
1141 -- hr_general.get_business_group_id) = hao.business_group_id;
1142
1143 l_budget_detail_id number;
1144 l_object_version_number number := 1;
1145 l_rows_inserted number := 0;
1146 l_proc varchar2(100) := g_package||'populate_bud_orgs' ;
1147 begin
1148 hr_utility.set_location('entering '||l_proc,10);
1149 open c0;
1150 fetch c0 into l_budget_start_date,l_budget_end_date;
1151 close c0;
1152 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
1153 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
1154 if p_org_hier_ver is null then
1155 hr_utility.set_location('bg is used '||l_proc,20);
1156 for i in c1 loop
1157 hr_utility.set_location('in loop for '||i.organization_id||l_proc,25);
1158 l_rows_inserted := l_rows_inserted + 1;
1159 pqh_budget_details_api.create_budget_detail(
1160 p_validate => FALSE
1161 ,p_budget_detail_id => l_budget_detail_id
1162 ,p_budget_version_id => p_budget_version_id
1163 ,p_organization_id => i.organization_id
1164 ,p_position_id => ''
1165 ,p_job_id => ''
1166 ,p_grade_id => ''
1167 ,p_budget_unit1_value => ''
1168 ,p_budget_unit1_percent => ''
1169 ,p_budget_unit1_available => ''
1170 ,p_budget_unit1_value_type_cd => ''
1171 ,p_budget_unit2_value => ''
1172 ,p_budget_unit2_percent => ''
1173 ,p_budget_unit2_available => ''
1174 ,p_budget_unit2_value_type_cd => ''
1175 ,p_budget_unit3_value => ''
1176 ,p_budget_unit3_percent => ''
1177 ,p_budget_unit3_available => ''
1178 ,p_budget_unit3_value_type_cd => ''
1179 ,p_object_version_number => l_object_version_number
1180 );
1181 hr_utility.set_location('inserting '||i.organization_id||l_proc,30);
1182 pqh_budget.insert_org_is_bud(i.organization_id);
1183 end loop;
1184 else
1185 hr_utility.set_location('oh is used '||l_proc,40);
1186 for i in c2 loop
1187 l_rows_inserted := l_rows_inserted + 1;
1188 pqh_budget_details_api.create_budget_detail(
1189 p_validate => FALSE
1190 ,p_budget_detail_id => l_budget_detail_id
1191 ,p_budget_version_id => p_budget_version_id
1192 ,p_organization_id => i.organization_id
1193 ,p_position_id => ''
1194 ,p_job_id => ''
1195 ,p_grade_id => ''
1196 ,p_budget_unit1_value => ''
1197 ,p_budget_unit1_percent => ''
1198 ,p_budget_unit1_available => ''
1199 ,p_budget_unit1_value_type_cd => ''
1200 ,p_budget_unit2_value => ''
1201 ,p_budget_unit2_percent => ''
1202 ,p_budget_unit2_available => ''
1203 ,p_budget_unit2_value_type_cd => ''
1204 ,p_budget_unit3_value => ''
1205 ,p_budget_unit3_percent => ''
1206 ,p_budget_unit3_available => ''
1207 ,p_budget_unit3_value_type_cd => ''
1208 ,p_object_version_number => l_object_version_number
1209 );
1210 hr_utility.set_location('inserting '||i.organization_id||l_proc,50);
1211 pqh_budget.insert_org_is_bud(i.organization_id);
1212 end loop;
1213 end if;
1214 p_rows_inserted := l_rows_inserted;
1215 hr_utility.set_location('entering '||l_proc,60);
1216 exception when others then
1217 p_rows_inserted := null;
1218 raise;
1219 end populate_bud_organizations;
1220 function get_wks_budget( p_worksheet_id in number) return number is
1221 cursor c1 is select budget_id from pqh_worksheets
1222 where worksheet_id = p_worksheet_id;
1223 l_budget_id number;
1224 begin
1225 open c1;
1226 fetch c1 into l_budget_id ;
1227 close c1;
1228 return l_budget_id;
1229 end get_wks_budget;
1230 function get_wkd_budget( p_worksheet_detail_id in number) return number is
1231 cursor c1 is select worksheet_id from pqh_worksheet_details
1232 where worksheet_detail_id = p_worksheet_detail_id;
1233 l_worksheet_id number;
1234 l_budget_id number;
1235 begin
1236 open c1;
1237 fetch c1 into l_worksheet_id ;
1238 close c1;
1239 l_budget_id := get_wks_budget(p_worksheet_id => l_worksheet_id);
1240 return l_budget_id;
1241 end get_wkd_budget;
1242 function get_bgd_budget( p_budget_detail_id in number) return number is
1243 cursor c1 is select budget_id
1244 from pqh_budget_versions bgv, pqh_budget_details bgd
1245 where bgd.budget_detail_id = p_budget_detail_id
1246 and bgd.budget_version_id = bgv.budget_version_id ;
1247 l_budget_id number;
1248 begin
1249 open c1;
1250 fetch c1 into l_budget_id ;
1251 close c1;
1252 return l_budget_id;
1253 end get_bgd_budget;
1254 procedure insert_default_period(p_worksheet_detail_id in number,
1255 p_wkd_ovn in out nocopy number,
1256 p_worksheet_unit1_value in number default null,
1257 p_worksheet_unit2_value in number default null,
1258 p_worksheet_unit3_value in number default null,
1259 p_worksheet_period_id out nocopy number,
1260 p_wpr_ovn out nocopy number) is
1261 l_wkd_ovn number := p_wkd_ovn;
1262 l_budget_id number;
1263 l_calendar varchar2(30);
1264 l_budget_start_date date;
1265 l_budget_end_date date;
1266 l_period_start_date date;
1267 l_period_end_date date;
1268 l_start_time_period_id number;
1269 l_end_time_period_id number;
1270 cursor c1 is
1271 select time_period_id,start_date
1272 from per_time_periods
1273 where period_set_name = l_calendar
1274 and start_date >= l_budget_start_date
1275 and start_date < l_budget_end_date
1276 order by start_date;
1277 cursor c2 is
1278 select time_period_id,end_date
1279 from per_time_periods
1280 where period_set_name = l_calendar
1281 and end_date > l_budget_start_date
1282 and end_date <= l_budget_end_date
1283 and end_date > l_period_start_date
1284 order by end_date desc;
1285 l_proc varchar2(100) := g_package||'insert_default_period' ;
1286 begin
1287 hr_utility.set_location('entering '||l_proc,10);
1288 hr_utility.set_location('unit1_value '||p_worksheet_unit1_value||l_proc,11);
1289 hr_utility.set_location('unit2_value '||p_worksheet_unit2_value||l_proc,12);
1290 hr_utility.set_location('unit3_value '||p_worksheet_unit3_value||l_proc,13);
1291 l_budget_id := get_wkd_budget(p_worksheet_detail_id);
1292 hr_utility.set_location('budget id is '||l_budget_id||l_proc,20);
1293 select period_set_name,budget_start_date,budget_end_date
1294 into l_calendar,l_budget_start_date,l_budget_end_date
1295 from pqh_budgets
1296 where budget_id = l_budget_id;
1297 hr_utility.set_location('calendar id is '||l_calendar||l_proc,30);
1298 hr_utility.set_location('budget start date is '||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,40);
1299 hr_utility.set_location('budget_end date id is '||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,50);
1300 open c1;
1301 fetch c1 into l_start_time_period_id,l_period_start_date;
1302 hr_utility.set_location('period_start date id is '||to_char(l_period_start_date,'DD/MM/RRRR')||l_proc,60);
1303 if c1%found then
1304 open c2;
1305 fetch c2 into l_end_time_period_id,l_period_end_date;
1306 hr_utility.set_location('period_end date id is '||to_char(l_period_end_date,'DD/MM/RRRR')||l_proc,70);
1307 close c2;
1308 end if;
1309 close c1;
1310 if l_end_time_period_id is null then
1311 hr_utility.set_location('no period lies during budget life'||l_proc,80);
1312 pqh_budget.update_worksheet_detail(
1313 p_worksheet_detail_id => p_worksheet_detail_id,
1314 p_object_version_number => p_wkd_ovn,
1315 p_effective_date => trunc(sysdate),
1316 p_budget_unit1_available => p_worksheet_unit1_value,
1317 p_budget_unit2_available => p_worksheet_unit1_value,
1318 p_budget_unit3_available => p_worksheet_unit1_value);
1319 hr_utility.set_location('ovn of wkd after is'||p_wkd_ovn||l_proc,90);
1320 else
1321 hr_utility.set_location('inserting worksheet period '||l_proc,100);
1322 pqh_worksheet_periods_api.create_worksheet_period
1323 (
1324 p_worksheet_period_id => p_worksheet_period_id
1325 ,p_end_time_period_id => l_end_time_period_id
1326 ,p_worksheet_detail_id => p_worksheet_detail_id
1327 ,p_budget_unit1_percent => 100
1328 ,p_budget_unit2_percent => 100
1329 ,p_budget_unit3_percent => 100
1330 ,p_budget_unit1_value => p_worksheet_unit1_value
1331 ,p_budget_unit2_value => p_worksheet_unit2_value
1332 ,p_budget_unit3_value => p_worksheet_unit3_value
1333 ,p_object_version_number => p_wpr_ovn
1334 ,p_budget_unit1_value_type_cd => 'P'
1335 ,p_budget_unit2_value_type_cd => 'P'
1336 ,p_budget_unit3_value_type_cd => 'P'
1337 ,p_start_time_period_id => l_start_time_period_id
1338 ,p_budget_unit1_available => p_worksheet_unit1_value
1339 ,p_budget_unit2_available => p_worksheet_unit2_value
1340 ,p_budget_unit3_available => p_worksheet_unit3_value
1341 ,p_effective_date => trunc(sysdate)
1342 );
1343 end if;
1344 hr_utility.set_location('exiting '||l_proc,1000);
1345 exception when others then
1346 p_wkd_ovn := l_wkd_ovn;
1347 p_worksheet_period_id := null;
1348 p_wpr_ovn := null;
1349 raise;
1350 end insert_default_period;
1351
1352 procedure apply_wks(p_transaction_id in number,
1353 p_transaction_category_id in number,
1354 p_wkd_ovn out nocopy number,
1355 p_wks_ovn out nocopy number) IS
1356 l_transaction_status varchar2(30);
1357 l_wkd_ovn number;
1358 l_wks_ovn number;
1359 l_proc varchar2(61) := g_package||'apply_wks' ;
1360 BEGIN
1361 hr_utility.set_location('entering '||l_proc,10);
1362 approve_wks(p_transaction_id => p_transaction_id,
1363 p_transaction_category_id => p_transaction_category_id,
1364 p_wkd_ovn => l_wkd_ovn,
1365 p_wks_ovn => l_wks_ovn);
1366 hr_utility.set_location(l_proc||'wks_ovn is '||l_wks_ovn,20);
1367 hr_utility.set_location(l_proc||'wkd_ovn is '||l_wkd_ovn,30);
1368 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1369 p_transaction_category_id => p_transaction_category_id,
1370 p_result_status => 'SUBMITTED',
1371 p_wkd_object_version_number => p_wkd_ovn,
1372 p_wks_object_version_number => p_wks_ovn);
1373 hr_utility.set_location(l_proc||'wks_ovn is '||l_wks_ovn,40);
1374 hr_utility.set_location(l_proc||'wkd_ovn is '||l_wkd_ovn,50);
1375 exception when others then
1376 p_wkd_ovn := null;
1377 p_wks_ovn := null;
1378 raise;
1379 END;
1380 procedure pending_wks(p_transaction_id in number,
1381 p_transaction_category_id in number,
1382 p_wkd_ovn out nocopy number,
1383 p_wks_ovn out nocopy number) IS
1384 l_user varchar2(100);
1385 l_transaction_status varchar2(30);
1386 l_status varchar2(30);
1387 l_working_users varchar2(2000);
1388 l_proc varchar2(61) := g_package||'pending_wks' ;
1389 BEGIN
1390 hr_utility.set_location('entering'||l_proc,10);
1391 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1392 p_transaction_category_id => p_transaction_category_id,
1393 p_result_status => 'PENDING',
1394 p_wkd_object_version_number => p_wkd_ovn,
1395 p_wks_object_version_number => p_wks_ovn);
1396 hr_utility.set_location(l_proc||'wks_ovn is '||p_wks_ovn,40);
1397 hr_utility.set_location(l_proc||'wkd_ovn is '||p_wkd_ovn,50);
1398 exception when others then
1399 p_wkd_ovn := null;
1400 p_wks_ovn := null;
1401 raise;
1402 END;
1403 procedure approve_wks(p_transaction_id in number,
1404 p_transaction_category_id in number,
1405 p_wkd_ovn out nocopy number,
1406 p_wks_ovn out nocopy number) IS
1407 l_user varchar2(100);
1408 l_transaction_status varchar2(30);
1409 l_status varchar2(30);
1410 l_working_users varchar2(2000);
1411 l_proc varchar2(61) := g_package||'approve_wks' ;
1412 BEGIN
1413 hr_utility.set_location('entering'||l_proc,10);
1414 pqh_budget.lock_all_children(p_worksheet_detail_id => p_transaction_id,
1415 p_transaction_category_id => p_transaction_category_id,
1416 p_status => l_status,
1417 p_working_users => l_working_users);
1418 hr_utility.set_location('child locked'||l_proc,20);
1419 if nvl(l_status,'Y') ='Y' then
1420 -- close notifications and change status
1421 hr_utility.set_location('changing status'||l_proc,30);
1422 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1423 p_transaction_category_id => p_transaction_category_id,
1424 p_result_status => 'APPROVED',
1425 p_wkd_object_version_number => p_wkd_ovn,
1426 p_wks_object_version_number => p_wks_ovn);
1427 hr_utility.set_location('status changed'||l_proc,40);
1428 else
1429 hr_utility.set_message(8302,'PQH_WKS_CHILD_WORKING');
1430 hr_utility.set_message_token('USERS',l_working_users);
1431 hr_utility.raise_error;
1432 end if;
1433 hr_utility.set_location('wks out nocopy ovn is'||p_wks_ovn||l_proc,90);
1434 hr_utility.set_location('wkd out nocopy ovn is'||p_wkd_ovn||l_proc,100);
1435 exception when others then
1436 p_wkd_ovn := null;
1437 p_wks_ovn := null;
1438 raise;
1439 END;
1440 procedure reject_wks(p_transaction_id in number,
1441 p_transaction_category_id in number,
1442 p_wkd_ovn out nocopy number,
1443 p_wks_ovn out nocopy number) IS
1444 l_user varchar2(100);
1445 l_transaction_status varchar2(30);
1446 l_status varchar2(30);
1447 l_working_users varchar2(2000);
1448 l_proc varchar2(61) := g_package||'reject_wks' ;
1449 BEGIN
1450 hr_utility.set_location('entering'||l_proc,10);
1451 -- if the current user is the initiator of the txn then mark the status
1452 -- as reject else status remains the same and notification will be sent to initiator.
1453 l_user := pqh_wf.get_requestor(p_transaction_category_id => p_transaction_category_id,
1454 p_transaction_id => p_transaction_id);
1455 hr_utility.set_location('requestor is'||l_user||l_proc,20);
1456 -- change the status of the delegated rows to reject
1457 -- and all open notifications to be killed
1458 -- depending upon the initator of the delegated row
1459 if l_user is null or l_user = fnd_profile.value('USERNAME') then
1460 hr_utility.set_location('going for lock'||l_proc,30);
1461 pqh_budget.lock_all_children(p_worksheet_detail_id => p_transaction_id,
1462 p_transaction_category_id => p_transaction_category_id,
1463 p_status => l_status,
1464 p_working_users => l_working_users);
1465 hr_utility.set_location('locked'||l_proc,40);
1466 if nvl(l_status,'Y') ='Y' then
1467 -- notifications are to be closed and change status
1468 hr_utility.set_location('changing status'||l_proc,50);
1469 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1470 p_transaction_category_id => p_transaction_category_id,
1471 p_result_status => 'REJECT',
1472 p_wkd_object_version_number => p_wkd_ovn,
1473 p_wks_object_version_number => p_wks_ovn);
1474 hr_utility.set_location('status changed'||l_proc,60);
1475 else
1476 hr_utility.set_message(8302,'PQH_WKS_CHILD_WORKING');
1477 hr_utility.set_message_token('USERS',l_working_users);
1478 hr_utility.raise_error;
1479 end if;
1480 else
1481 hr_utility.set_location('changing status'||l_proc,70);
1482 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1483 p_transaction_category_id => p_transaction_category_id,
1484 p_result_status => 'PENDING',
1485 p_wkd_object_version_number => p_wkd_ovn,
1486 p_wks_object_version_number => p_wks_ovn);
1487 hr_utility.set_location('status changed'||l_proc,80);
1488 end if;
1489 hr_utility.set_location('wks out nocopy ovn is'||p_wks_ovn||l_proc,90);
1490 hr_utility.set_location('wkd out nocopy ovn is'||p_wkd_ovn||l_proc,100);
1491 exception when others then
1492 p_wkd_ovn := null;
1493 p_wks_ovn := null;
1494 raise;
1495 END;
1496 function get_transaction_name(p_worksheet_detail_id in number) return varchar2 is
1497 l_worksheet_name varchar2(240);
1498 l_org_name hr_all_organization_units.name%type;
1499 l_org_id number;
1500 l_transaction_name varchar2(300);
1501 cursor c1 is
1502 select wks.worksheet_name,wkd.organization_id
1503 from pqh_worksheets wks, pqh_worksheet_details wkd
1504 where wkd.worksheet_id = wks.worksheet_id
1505 and worksheet_detail_id = p_worksheet_detail_id
1506 and nvl(action_cd,'D') ='D';
1507 begin
1508 open c1;
1509 fetch c1 into l_worksheet_name,l_org_id;
1510 close c1;
1511 --
1512 if l_org_id is not null then
1513 l_org_name := hr_general.decode_organization(l_org_id);
1514 l_transaction_name := l_worksheet_name||'('||l_org_name||')';
1515 else
1516 l_transaction_name := l_worksheet_name;
1517 end if;
1518 return l_transaction_name;
1519 end;
1520
1521 Function check_job_pos_for_valid_grd(p_position_id number default null,
1522 p_job_id number default null,
1523 p_grade_id number default null,
1524 p_valid_grade_flag varchar2 default null)
1525 Return varchar2 is
1526 Cursor C_position is select valid_grade_id
1527 from per_valid_grades
1528 where position_id = p_position_id
1529 and grade_id = p_grade_id
1530 and rownum < 2;
1531
1532 Cursor C_job is select valid_grade_id
1533 from per_valid_grades
1534 where job_id = p_job_id
1535 and grade_id = p_grade_id
1536 and rownum < 2;
1537
1538 l_valid_grade_id per_valid_grades.valid_grade_id%type;
1539 Begin
1540 If nvl(p_grade_id,0) <> 0 then
1541 If nvl(p_valid_grade_flag,'N') = 'Y' then
1542 If p_position_id is not null then
1543 Open C_position;
1544 Fetch C_position into l_valid_grade_id;
1545 Close C_position;
1546 Elsif p_job_id is not null then
1547 Open C_job;
1548 Fetch C_job into l_valid_grade_id;
1549 Close C_job;
1550 End if;
1551 If l_valid_grade_id is null then
1552 Return 'FALSE';
1553 Else
1554 Return 'TRUE';
1555 End If;
1556 Else
1557 Return 'TRUE';
1558 End If;
1559 Else
1560 Return 'TRUE';
1561 End if;
1562 End;
1563
1564 Function get_valid_grade(p_position_id number default null,
1565 p_job_id number default null,
1566 p_grade_id number default null,
1567 p_start_bud_date date,
1568 p_end_bud_date date)
1569 Return varchar2 is
1570 l_job_id per_valid_grades.job_id%type :=p_job_id;
1571 Cursor C_job is select valid_grade_id
1572 from per_valid_grades
1573 where job_id = l_job_id
1574 and grade_id = p_grade_id
1575 and date_from < p_end_bud_date
1576 and (date_to > p_start_bud_date or date_to is null)
1577 and rownum < 2;
1578
1579 Cursor C_position is select valid_grade_id
1580 from per_valid_grades
1581 where position_id = p_position_id
1582 and grade_id = p_grade_id
1583 and date_from < p_end_bud_date
1584 and (date_to > p_start_bud_date or date_to is null)
1585 and rownum < 2;
1586
1587 Cursor C2 is select valid_grade_id
1588 from per_valid_grades
1589 where grade_id = p_grade_id
1590 and date_from < p_end_bud_date
1591 and (date_to > p_start_bud_date or date_to is null)
1592 and rownum < 2;
1593 l_valid_grade_id per_valid_grades.valid_grade_id%type;
1594 Begin
1595 If p_position_id is not null then
1596 Open C_position;
1597 Fetch C_position into l_valid_grade_id;
1598 Close C_position;
1599 Elsif l_job_id is not null then
1600 Open C_job;
1601 Fetch C_job into l_valid_grade_id;
1602 Close C_job;
1603 Elsif l_job_id is null and p_position_id is null then
1604 Open C2;
1605 Fetch C2 into l_valid_grade_id;
1606 Close C2;
1607 End If;
1608 If l_valid_grade_id is null then
1609 Return 'FALSE';
1610 Else
1611 Return 'TRUE';
1612 End If;
1613 End;
1614
1615 Function get_position_budget_flag(p_availability_status_id in number)
1616 return varchar2 is
1617
1618 l_budget_flag varchar2(150) := 'Y';
1619 --
1620 -- Get the budget flag value stored in the information1 column
1621 --
1622 Cursor c_budget_flag is
1623 Select nvl(information1,'Y')
1624 from per_shared_types
1625 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
1626 and shared_type_id = p_availability_status_id;
1627
1628 Begin
1629 --
1630 -- Fetch the Budget Flag value
1631 --
1632 Open c_budget_flag;
1633 Fetch c_budget_flag into l_budget_flag;
1634 Close c_budget_flag;
1635
1636 Return l_budget_flag;
1637
1638 End;
1639
1640 /*
1641 function valid_position_txn(p_position_transaction_id in number,
1642 p_budget_start_date in date,
1643 p_budget_end_date in date) is
1644 l_org_id number;
1645 l_job_id number;
1646 l_pos_start_date date;
1647 l_pos_end_date date;
1648 l_org_start_date date;
1649 l_org_end_date date;
1650 l_job_start_date date;
1651 l_job_end_date date;
1652 begin
1653 select organization_id,job_id,effective_start_date,effective_end_date
1654 into l_org_id,l_job_id,l_pos_start_date,l_pos_end_date
1655 from pqh_position_transactions
1656 where position_transaction_id = p_position_transaction_id;
1657 if l_org_id is not null then
1658 end if;
1659 if l_job_id is not null then
1660 end if;
1661 end valid_position_txn;
1662 */
1663 procedure update_wkd_pot(p_worksheet_detail_id number) is
1664 begin
1665 update pqh_worksheet_details
1666 set position_transaction_id = null
1667 where worksheet_detail_id = p_worksheet_detail_id;
1668 end update_wkd_pot;
1669
1670 procedure purge_wkd(p_worksheet_detail_id in number,
1671 p_budget_style_cd in varchar2) is
1672 cursor c_worksheet_periods is select worksheet_period_id,object_version_number
1673 from pqh_worksheet_periods where worksheet_detail_id = p_worksheet_detail_id;
1674 cursor c_worksheet_budget_sets(p_worksheet_period_id number) is
1675 select worksheet_budget_set_id,object_version_number
1676 from pqh_worksheet_budget_sets where worksheet_period_id = p_worksheet_period_id;
1677 cursor c_worksheet_bdgt_elmnts (p_worksheet_budget_set_id number) is
1678 select worksheet_bdgt_elmnt_id,object_version_number
1679 from pqh_worksheet_bdgt_elmnts where worksheet_budget_set_id = p_worksheet_budget_set_id;
1680 cursor c_worksheet_fund_srcs (p_worksheet_bdgt_elmnt_id number) is
1681 select worksheet_fund_src_id,object_version_number
1682 from pqh_worksheet_fund_srcs where worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
1683 l_parent_wkd_id number;
1684 l_budget_unit1_value number;
1685 l_budget_unit2_value number;
1686 l_budget_unit3_value number;
1687 l_object_version_number number;
1688 begin
1689 for i in c_worksheet_periods loop
1690 for j in c_worksheet_budget_sets(i.worksheet_period_id) loop
1691 for k in c_worksheet_bdgt_elmnts(j.worksheet_budget_set_id) loop
1692 for l in c_worksheet_fund_srcs(k.worksheet_bdgt_elmnt_id) loop
1693 pqh_worksheet_fund_srcs_api.DELETE_WORKSHEET_FUND_SRC(
1694 P_WORKSHEET_FUND_SRC_ID => l.worksheet_fund_src_id,
1695 P_OBJECT_VERSION_NUMBER => l.object_version_number);
1696 end loop;
1697 pqh_worksheet_bdgt_elmnts_api.DELETE_WORKSHEET_BDGT_ELMNT(
1698 P_WORKSHEET_BDGT_ELMNT_ID => k.worksheet_bdgt_elmnt_id,
1699 P_OBJECT_VERSION_NUMBER => k.object_version_number);
1700 end loop;
1701 pqh_worksheet_budget_sets_api.DELETE_WORKSHEET_BUDGET_SET(
1702 P_WORKSHEET_BUDGET_SET_ID => j.worksheet_budget_set_id,
1703 P_EFFECTIVE_DATE => trunc(sysdate),
1704 P_OBJECT_VERSION_NUMBER => j.object_version_number);
1705 end loop;
1706 pqh_worksheet_periods_api.DELETE_WORKSHEET_PERIOD(
1707 P_WORKSHEET_PERIOD_ID => i.worksheet_period_id,
1708 P_EFFECTIVE_DATE => trunc(sysdate),
1709 P_OBJECT_VERSION_NUMBER => i.object_version_number);
1710 end loop;
1711
1712 select parent_worksheet_detail_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,object_version_number
1713 into l_parent_wkd_id,l_budget_unit1_value,l_budget_unit2_value,l_budget_unit3_value,l_object_version_number
1714 from pqh_worksheet_details where worksheet_detail_id = p_worksheet_detail_id;
1715
1716 if p_budget_style_cd ='TOP' then
1717 update pqh_worksheet_details
1718 set budget_unit1_available = nvl(budget_unit1_available,0) - nvl(l_budget_unit1_value,0),
1719 budget_unit2_available = nvl(budget_unit2_available,0) - nvl(l_budget_unit2_value,0),
1720 budget_unit3_available = nvl(budget_unit3_available,0) - nvl(l_budget_unit3_value,0)
1721 where worksheet_detail_id = l_parent_wkd_id;
1722 else
1723 update pqh_worksheet_details
1724 set budget_unit1_value = nvl(budget_unit1_value,0) - nvl(l_budget_unit1_value,0),
1725 budget_unit2_value = nvl(budget_unit2_value,0) - nvl(l_budget_unit2_value,0),
1726 budget_unit3_value = nvl(budget_unit3_value,0) - nvl(l_budget_unit3_value,0)
1727 where worksheet_detail_id = l_parent_wkd_id;
1728 end if;
1729 pqh_worksheet_details_api.DELETE_WORKSHEET_DETAIL(
1730 P_WORKSHEET_DETAIL_ID => p_worksheet_detail_id,
1731 P_EFFECTIVE_DATE => trunc(sysdate),
1732 P_OBJECT_VERSION_NUMBER => l_object_version_number);
1733 end purge_wkd;
1734 procedure delete_wkd(p_worksheet_detail_id in number,
1735 p_object_version_number in number) is
1736 l_proc varchar2(100) := g_package||'delete_wkd' ;
1737 l_budget_id number;
1738 l_position_id number;
1739 l_budgeted_entity_cd varchar2(80);
1740 l_budget_style_cd varchar2(80);
1741 l_worksheet_detail_id number;
1742 cursor c_worksheet_detail is
1743 select position_id,worksheet_detail_id
1744 from pqh_worksheet_details
1745 where worksheet_detail_id = p_worksheet_detail_id;
1746 begin
1747 /*
1748 ---------- logic of the program --------------------------------
1749 check whether worksheet detail exist in the system or not, if yes then do these
1750 if primary budget entity is Position
1751 if Position_id is there then
1752 Position transaction should be updated to null
1753 else
1754 delete the dependent records of worksheet period, budgetsets etc.
1755 delete the worksheet_detail
1756 update the parent worksheet_detail balances
1757 end if;
1758 else
1759 delete the dependent records of worksheet period, budgetsets etc.
1760 delete the worksheet_detail
1761 update the parent worksheet_detail balances
1762 end if;
1763 */
1764 ---------- actual program --------------------------------
1765 open c_worksheet_detail;
1766 fetch c_worksheet_detail into l_position_id,l_worksheet_detail_id;
1767 if c_worksheet_detail%found then
1768 l_budget_id := get_wkd_budget(p_worksheet_detail_id);
1769 select budgeted_entity_cd,budget_style_cd into l_budgeted_entity_cd,l_budget_style_cd
1770 from pqh_budgets where budget_id = l_budget_id;
1771 if l_budgeted_entity_cd ='POSITION' then
1772 if l_position_id is not null then
1773 update_wkd_pot(p_worksheet_detail_id => p_worksheet_detail_id);
1774 else
1775 purge_wkd(p_worksheet_detail_id => p_worksheet_detail_id,
1776 p_budget_style_cd => l_budget_style_cd);
1777 end if;
1778 else
1779 purge_wkd(p_worksheet_detail_id => p_worksheet_detail_id,
1780 p_budget_style_cd => l_budget_style_cd);
1781 end if;
1782 end if;
1783 close c_worksheet_detail;
1784 end delete_wkd;
1785
1786 Function PQH_CHECK_GMS_INSTALLED
1787 RETURN varchar2 IS
1788 cursor gms is select a.status, a.application_id, b.application_short_name
1789 from
1790 fnd_product_installations a, fnd_application b
1791 where
1792 a.application_id = b.application_id
1793 and
1794 b.application_short_name = 'GMS' and status = 'I';
1795 stat varchar2(10) := 'N';
1796 BEGIN
1797 for gms_rec in gms loop
1798 stat := gms_rec.status;
1799 end loop;
1800 RETURN stat;
1801 EXCEPTION
1802 WHEN others THEN
1803 RETURN stat;
1804 END; -- Function PQH_CHECK_GMS_INSTALL
1805 end pqh_wks_budget;