[Home] [Help]
PACKAGE BODY: APPS.PQH_WKS_BUDGET
Source
1 package body pqh_wks_budget as
2 /* $Header: pqwksbud.pkb 120.1 2005/08/17 11:26:20 nsanghal noship $ */
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 hr_utility.set_message_token('STARTDATE',fnd_date.date_to_chardate(p_wks_start_date));
693 hr_utility.set_message_token('ENDDATE',fnd_date.date_to_chardate(p_wks_end_date));
694 hr_utility.raise_error;
695 end if;
696 if p_budget_version_id is not null then
697 open c1;
698 fetch c1 into l_ver_chk;
699 if c1%notfound then
700 close c1;
701 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_VER');
702 hr_utility.raise_error;
703 end if;
704 close c1;
705 end if;
706 hr_utility.set_location('wks_mode is '||p_worksheet_mode||l_proc,20);
707 if p_worksheet_mode in ('S','N') then
708 -- in the case of start from scratch, worksheet dates should be greater than all the existing
709 -- version dates as it is going to make a new version in all cases, if the dates
710 -- are not highest in that case status is returned as error
711 -- budget_version_id may be null but budget_id should be there.
712 -- gaps are also ok
713 open c0;
714 fetch c0 into l_max_version;
715 close c0;
716 hr_utility.set_location('max_version is '||l_max_version||l_proc,30);
717 open c2;
718 fetch c2 into l_max_end_date;
719 if c2%notfound then
720 hr_utility.set_location('max_end_date notfound '||l_proc,40);
721 if p_worksheet_mode ='S' then
722 -- no dates in the budget version as it is a initial case so
723 p_status := 'SUCCESS' ;
724 else
725 -- mode is correction but no records
726 p_status := 'ERROR' ;
727 end if;
728 else
729 hr_utility.set_location('max_end_date is '||l_max_end_date||l_proc,50);
730 if l_max_end_date is not null then
731 if p_wks_start_date = l_max_end_date + 1 then
732 -- start date is valid as it is not overlapping and nor giving any gap.
733 p_wks_ll_date := l_max_end_date+1 ;
734 p_wks_ul_date := l_max_end_date+1 ;
735 p_status := 'SUCCESS' ;
736 elsif p_wks_start_date >= l_max_end_date +1 then
737 -- gaps will be there but no overlapping
738 p_wks_ll_date := l_max_end_date+1 ;
739 p_status := 'SUCCESS' ;
740 else
741 -- invalid start date
742 p_wks_ll_date := l_max_end_date+1 ;
743 p_status := 'ERROR' ;
744 end if;
745 else
746 p_status := 'SUCCESS' ;
747 end if;
748 end if;
749 close c2;
750 hr_utility.set_location('end of validation with status'||p_status||l_proc,60);
751 else
752 open c3;
753 fetch c3 into l_version_number;
754 close c3;
755 pqh_bdgt.bgv_date_validation( p_budget_id => p_budget_id,
756 p_version_number => l_version_number ,
757 p_date_from => p_wks_start_date,
758 p_date_to => p_wks_end_date,
759 p_bgv_ll_date => p_wks_ll_date,
760 p_bgv_ul_date => p_wks_ul_date,
761 p_status => p_status ) ;
762 hr_utility.set_location('end of validation with status'||p_status||l_proc,170);
763 end if;
764 exception when others then
765 p_wks_ll_date := null;
766 p_wks_ul_date := null;
767 p_status := 'ERROR';
768 raise;
769 end wks_date_validation;
770
771 procedure propagate_bottom_up(p_worksheet_detail_id in number,
772 p_budget_unit1_value in out nocopy number,
773 p_budget_unit2_value in out nocopy number,
774 p_budget_unit3_value in out nocopy number,
775 p_status out nocopy varchar2
776 ) is
777
778
779 init_budget_unit1_value number := p_budget_unit1_value;
780 init_budget_unit2_value number := p_budget_unit2_value;
781 init_budget_unit3_value number := p_budget_unit3_value;
782
783 cursor c1 is select worksheet_detail_id,object_version_number,
784 budget_unit1_value,budget_unit2_value,budget_unit3_value,
785 old_unit1_value,old_unit2_value,old_unit3_value
786 from pqh_worksheet_details
787 where parent_worksheet_detail_id = p_worksheet_detail_id
788 and action_cd ='D';
789 l_object_version_number number;
790 l_budget_unit1_value number;
791 l_budget_unit2_value number;
792 l_budget_unit3_value number;
793 l_lck_success boolean := FALSE;
794 l_status varchar2(30) ;
795 begin
796 hr_utility.set_location('entering bootom_up for wkd'||p_worksheet_detail_id,05);
797 for i in c1 loop
798 hr_utility.set_location('inside the loop for wkd'||i.worksheet_detail_id,10);
799 begin
800 hr_utility.set_location('going to lock'||i.worksheet_detail_id,20);
801 pqh_wdt_shd.lck(p_worksheet_detail_id => i.worksheet_detail_id,
802 p_object_version_number => i.object_version_number );
803 l_lck_success := TRUE;
804 hr_utility.set_location('lock success',30);
805 exception
806 when others then
807 hr_utility.set_location('lock failed',40);
808 l_lck_success := FALSE;
809 if p_status <> 'LOCK' then
810 p_status := 'LOCK';
811 end if;
812 end;
813 if l_lck_success then
814 hr_utility.set_location('going for propagation',50);
815 l_object_version_number := i.object_version_number;
816 l_budget_unit1_value := i.budget_unit1_value;
817 l_budget_unit2_value := i.budget_unit2_value;
818 l_budget_unit3_value := i.budget_unit3_value;
819 hr_utility.set_location('calling propagate bottom_up',60);
820 begin
821 propagate_bottom_up(p_worksheet_detail_id => i.worksheet_detail_id,
822 p_budget_unit1_value => l_budget_unit1_value,
823 p_budget_unit2_value => l_budget_unit2_value,
824 p_budget_unit3_value => l_budget_unit3_value,
825 p_status => l_status);
826 end;
827 if nvl(l_status,'X') = 'LOCK' then
828 p_status := 'LOCK';
829 end if;
830 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(l_budget_unit1_value,0) ;
831 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(l_budget_unit1_value,0) ;
832 p_budget_unit1_value := nvl(p_budget_unit1_value,0) - nvl(i.old_unit1_value,0) + nvl(i.budget_unit1_value,0) ;
833 pqh_budget.update_worksheet_detail(
834 p_worksheet_detail_id => i.worksheet_detail_id,
835 p_object_version_number => l_object_version_number,
836 p_effective_date => trunc(sysdate),
837 p_budget_unit1_value => l_budget_unit1_value,
838 p_budget_unit2_value => l_budget_unit2_value,
839 p_budget_unit3_value => l_budget_unit3_value,
840 p_old_unit1_value => l_budget_unit1_value,
841 p_old_unit2_value => l_budget_unit2_value,
842 p_old_unit3_value => l_budget_unit3_value);
843 end if;
844 hr_utility.set_location('end of the loop for wkd'||i.worksheet_detail_id,120);
845 end loop;
846 hr_utility.set_location('exiting propagate_bottom_up for wkd'||p_worksheet_detail_id,130);
847 exception when others then
848 p_budget_unit1_value := init_budget_unit1_value;
849 p_budget_unit2_value := init_budget_unit2_value;
850 p_budget_unit3_value := init_budget_unit3_value;
851 p_status := null;
852 raise;
853 end propagate_bottom_up;
854 procedure populate_bud_grades(p_budget_version_id in number,
855 p_business_group_id in number,
856 p_rows_inserted out nocopy number) is
857 l_budget_start_date date;
858 l_budget_end_date date;
859 l_valid_grade_flag pqh_budgets.valid_grade_reqd_flag%type;
860 l_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%type;
861
862 cursor c0 is select budget_start_date,budget_end_date,valid_grade_reqd_flag,budgeted_entity_cd
863 from pqh_budgets bgt, pqh_budget_versions bgv
864 where bgv.budget_id = bgt.budget_id
865 and bgv.budget_version_id = p_budget_version_id;
866 cursor c1 is select grade_id from per_grades a
867 where business_group_id = p_business_group_id
868 and ((nvl(l_valid_grade_flag,'N') = 'Y' and l_budgeted_entity_cd = 'GRADE' and
869 a.grade_id in (select b.grade_id from per_valid_grades b
870 where b.date_from < l_budget_end_date
871 and (b.date_to > l_budget_start_date or b.date_to is null)))
872 or (nvl(l_valid_grade_flag,'N') = 'N' and date_from < l_budget_end_date
873 and (date_to > l_budget_start_date or date_to is null)))
874 and pqh_budget.already_budgeted_grd(a.grade_id) = 'FALSE' ;
875 l_budget_detail_id number;
876 l_rows_inserted number := 0;
877 l_object_version_number number := 1;
878 l_proc varchar2(100) := g_package||'populate_bud_grades' ;
879 begin
880 hr_utility.set_location('entering '||l_proc,10);
881 open c0;
882 fetch c0 into l_budget_start_date,l_budget_end_date,l_valid_grade_flag,l_budgeted_entity_cd;
883 close c0;
884 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
885 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
886 for i in c1 loop
887 l_rows_inserted := l_rows_inserted + 1;
888 pqh_budget_details_api.create_budget_detail(
889 p_validate => FALSE
890 ,p_budget_detail_id => l_budget_detail_id
891 ,p_budget_version_id => p_budget_version_id
892 ,p_organization_id => ''
893 ,p_position_id => ''
894 ,p_job_id => ''
895 ,p_grade_id => i.grade_id
896 ,p_budget_unit1_value => ''
897 ,p_budget_unit1_percent => ''
898 ,p_budget_unit1_available => ''
899 ,p_budget_unit1_value_type_cd => ''
900 ,p_budget_unit2_value => ''
901 ,p_budget_unit2_percent => ''
902 ,p_budget_unit2_available => ''
903 ,p_budget_unit2_value_type_cd => ''
904 ,p_budget_unit3_value => ''
905 ,p_budget_unit3_percent => ''
906 ,p_budget_unit3_available => ''
907 ,p_budget_unit3_value_type_cd => ''
908 ,p_object_version_number => l_object_version_number
909 );
910 pqh_budget.insert_grd_is_bud(i.grade_id);
911 end loop;
912 p_rows_inserted := l_rows_inserted;
913 hr_utility.set_location('exiting '||l_proc,1000);
914 exception when others then
915 p_rows_inserted := null;
916 raise;
917 end populate_bud_grades;
918
919 procedure populate_bud_jobs(p_budget_version_id in number,
920 p_business_group_id in number,
921 p_rows_inserted out nocopy number) is
922 l_budget_start_date date;
923 l_budget_end_date date;
924 cursor c0 is select budget_start_date,budget_end_date
925 from pqh_budgets bgt, pqh_budget_versions bgv
926 where bgv.budget_id = bgt.budget_id
927 and bgv.budget_version_id = p_budget_version_id;
928 cursor c1 is select job_id from per_jobs job, per_job_groups jgr
929 where job.job_group_id = jgr.job_group_id and jgr.internal_name = 'HR_' || job.business_group_id
930 and job.business_group_id = p_business_group_id
931 and date_from < l_budget_end_date
932 and (date_to > l_budget_start_date or date_to is null)
933 and pqh_budget.already_budgeted_job(job_id) = 'FALSE';
934 l_budget_detail_id number;
935 l_rows_inserted number := 0;
936 l_object_version_number number := 1;
937 l_proc varchar2(100) := g_package||'populate_bud_jobs' ;
938 begin
939 hr_utility.set_location('entering '||l_proc,10);
940 open c0;
941 fetch c0 into l_budget_start_date,l_budget_end_date;
942 close c0;
943 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
944 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
945 for i in c1 loop
946 l_rows_inserted := l_rows_inserted + 1;
947 pqh_budget_details_api.create_budget_detail(
948 p_validate => FALSE
949 ,p_budget_detail_id => l_budget_detail_id
950 ,p_budget_version_id => p_budget_version_id
951 ,p_organization_id => ''
952 ,p_position_id => ''
953 ,p_job_id => i.job_id
954 ,p_grade_id => ''
955 ,p_budget_unit1_value => ''
956 ,p_budget_unit1_percent => ''
957 ,p_budget_unit1_available => ''
958 ,p_budget_unit1_value_type_cd => ''
959 ,p_budget_unit2_value => ''
960 ,p_budget_unit2_percent => ''
961 ,p_budget_unit2_available => ''
962 ,p_budget_unit2_value_type_cd => ''
963 ,p_budget_unit3_value => ''
964 ,p_budget_unit3_percent => ''
965 ,p_budget_unit3_available => ''
966 ,p_budget_unit3_value_type_cd => ''
967 ,p_object_version_number => l_object_version_number
968 );
969 pqh_budget.insert_job_is_bud(i.job_id);
970 end loop;
971 p_rows_inserted := l_rows_inserted;
972 hr_utility.set_location('exiting '||l_proc,1000);
973 exception when others then
974 p_rows_inserted := null;
975 raise;
976 end populate_bud_jobs;
977 procedure populate_bud_positions(p_budget_version_id in number,
978 p_org_hier_ver in number,
979 p_start_organization_id in number,
980 p_business_group_id in number,
981 p_rows_inserted out nocopy number) is
982 l_budget_start_date date;
983 l_budget_end_date date;
984 cursor c0 is select budget_start_date,budget_end_date
985 from pqh_budgets bgt, pqh_budget_versions bgv
986 where bgv.budget_id = bgt.budget_id
987 and bgv.budget_version_id = p_budget_version_id;
988 cursor c1 is select position_id,job_id,pos.organization_id organization_id
989 from hr_positions pos,hr_organization_units org
990 where org.business_group_id = p_business_group_id
991 and pos.business_group_id = p_business_group_id
992 and pos.organization_id = org.organization_id
993 and pos.effective_start_date < l_budget_end_date
994 and pos.effective_end_date > l_budget_start_date
995 and pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
996 and get_position_budget_flag(pos.availability_status_id) = 'Y';
997 cursor c2 is select position_id,job_id,organization_id
998 from ( select organization_id_child from pqh_worksheet_organizations_v
999 where org_structure_version_id = p_org_hier_ver
1000 connect by prior organization_id_child = organization_id_parent
1001 and org_structure_version_id = p_org_hier_ver
1002 start with organization_id_parent = p_start_organization_id
1003 and org_structure_version_id = p_org_hier_ver
1004 union
1005 select p_start_organization_id organization_id_child from dual )x,
1006 hr_positions_f
1007 where pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
1008 and get_position_budget_flag(availability_status_id) = 'Y'
1009 and effective_start_date < l_budget_end_date
1010 and effective_end_date > l_budget_start_date
1011 and organization_id = x.organization_id_child ;
1012 l_budget_detail_id number;
1013 l_rows_inserted number := 0;
1014 l_object_version_number number := 1;
1015 l_proc varchar2(100) := g_package||'populate_budget_positions' ;
1016 begin
1017 hr_utility.set_location('entering '||l_proc,10);
1018 hr_utility.set_location('business_group_id is '||p_business_group_id||l_proc,11);
1019 hr_utility.set_location('org_hier is '||p_org_hier_ver||l_proc,13);
1020 hr_utility.set_location('start organization is '||p_start_organization_id||l_proc,15);
1021 open c0;
1022 fetch c0 into l_budget_start_date, l_budget_end_date;
1023 close c0;
1024 if p_org_hier_ver is null then
1025 hr_utility.set_location('Business group cursor selected '||l_proc,20);
1026 for i in c1 loop
1027 l_rows_inserted := l_rows_inserted + 1;
1028 pqh_budget_details_api.create_budget_detail(
1029 p_validate => FALSE
1030 ,p_budget_detail_id => l_budget_detail_id
1031 ,p_budget_version_id => p_budget_version_id
1032 ,p_organization_id => i.organization_id
1033 ,p_position_id => i.position_id
1034 ,p_job_id => i.job_id
1035 ,p_grade_id => ''
1036 ,p_budget_unit1_value => ''
1037 ,p_budget_unit1_percent => ''
1038 ,p_budget_unit1_available => ''
1039 ,p_budget_unit1_value_type_cd => ''
1040 ,p_budget_unit2_value => ''
1041 ,p_budget_unit2_percent => ''
1042 ,p_budget_unit2_available => ''
1043 ,p_budget_unit2_value_type_cd => ''
1044 ,p_budget_unit3_value => ''
1045 ,p_budget_unit3_percent => ''
1046 ,p_budget_unit3_available => ''
1047 ,p_budget_unit3_value_type_cd => ''
1048 ,p_object_version_number => l_object_version_number
1049 );
1050 pqh_budget.insert_pos_is_bud(i.position_id);
1051 hr_utility.set_location('position inserted '||i.position_id||l_proc,40);
1052 end loop;
1053 else
1054 hr_utility.set_location('Org hierarchy cursor selected '||l_proc,45);
1055 for i in c2 loop
1056 l_rows_inserted := l_rows_inserted + 1;
1057 pqh_budget_details_api.create_budget_detail(
1058 p_validate => FALSE
1059 ,p_budget_detail_id => l_budget_detail_id
1060 ,p_budget_version_id => p_budget_version_id
1061 ,p_organization_id => i.organization_id
1062 ,p_position_id => i.position_id
1063 ,p_job_id => i.job_id
1064 ,p_grade_id => ''
1065 ,p_budget_unit1_value => ''
1066 ,p_budget_unit1_percent => ''
1067 ,p_budget_unit1_available => ''
1068 ,p_budget_unit1_value_type_cd => ''
1069 ,p_budget_unit2_value => ''
1070 ,p_budget_unit2_percent => ''
1071 ,p_budget_unit2_available => ''
1072 ,p_budget_unit2_value_type_cd => ''
1073 ,p_budget_unit3_value => ''
1074 ,p_budget_unit3_percent => ''
1075 ,p_budget_unit3_available => ''
1076 ,p_budget_unit3_value_type_cd => ''
1077 ,p_object_version_number => l_object_version_number
1078 );
1079 pqh_budget.insert_pos_is_bud(i.position_id);
1080 hr_utility.set_location('position inserted '||i.position_id||l_proc,50);
1081 end loop;
1082 end if;
1083 p_rows_inserted := l_rows_inserted;
1084 hr_utility.set_location('exiting '||l_proc,90);
1085 exception when others then
1086 p_rows_inserted := null;
1087 raise;
1088 end populate_bud_positions;
1089 procedure populate_bud_organizations(p_budget_version_id in number,
1090 p_org_hier_ver in number,
1091 p_start_organization_id in number,
1092 p_business_group_id in number,
1093 p_rows_inserted out nocopy number) is
1094 l_budget_start_date date;
1095 l_budget_end_date date;
1096 cursor c0 is select budget_start_date,budget_end_date
1097 from pqh_budgets bgt, pqh_budget_versions bgv
1098 where bgv.budget_id = bgt.budget_id
1099 and bgv.budget_version_id = p_budget_version_id;
1100 cursor c1 is select organization_id
1101 from hr_all_organization_units
1102 where business_group_id = p_business_group_id
1103 and date_from < l_budget_end_date
1104 and (date_to > l_budget_start_date or date_to is null)
1105 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1106 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', ORGANIZATION_ID))='TRUE'
1107 --and decode(hr_general.get_xbg_profile,'Y', business_group_id , hr_general.get_business_group_id) = business_group_id
1108 and pqh_budget.already_budgeted_org(organization_id) = 'FALSE';
1109 cursor c2 is select w.organization_id_child organization_id
1110 from pqh_worksheet_organizations_v w
1111 where org_structure_version_id = p_org_hier_ver
1112 and pqh_budget.already_budgeted_org(w.organization_id_child) = 'FALSE'
1113 and exists
1114 (select null
1115 from hr_all_organization_units hao
1116 where organization_id = w.organization_id_child
1117 and date_from < l_budget_end_date
1118 and (date_to > l_budget_start_date or date_to is null)
1119 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1120 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' )
1121 --AND decode(hr_general.get_xbg_profile,'Y', hao.business_group_id , hr_general.get_business_group_id) = hao.business_group_id)
1122 connect by prior organization_id_child = organization_id_parent
1123 and org_structure_version_id = p_org_hier_ver
1124 start with organization_id_parent = p_start_organization_id
1125 and org_structure_version_id = p_org_hier_ver
1126 union
1127 select organization_id
1128 from hr_all_organization_units hao
1129 where organization_id = p_start_organization_id
1130 and pqh_budget.already_budgeted_org(p_start_organization_id) = 'FALSE'
1131 and date_from < l_budget_end_date
1132 and (date_to > l_budget_start_date or date_to is null)
1133 and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
1134 HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' ;
1135 --AND decode(hr_general.get_xbg_profile,'Y', hao.business_group_id ,
1136 -- hr_general.get_business_group_id) = hao.business_group_id;
1137
1138 l_budget_detail_id number;
1139 l_object_version_number number := 1;
1140 l_rows_inserted number := 0;
1141 l_proc varchar2(100) := g_package||'populate_bud_orgs' ;
1142 begin
1143 hr_utility.set_location('entering '||l_proc,10);
1144 open c0;
1145 fetch c0 into l_budget_start_date,l_budget_end_date;
1146 close c0;
1147 hr_utility.set_location('budget_start_date is'||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,11);
1148 hr_utility.set_location('budget_end_date is'||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,12);
1149 if p_org_hier_ver is null then
1150 hr_utility.set_location('bg is used '||l_proc,20);
1151 for i in c1 loop
1152 hr_utility.set_location('in loop for '||i.organization_id||l_proc,25);
1153 l_rows_inserted := l_rows_inserted + 1;
1154 pqh_budget_details_api.create_budget_detail(
1155 p_validate => FALSE
1156 ,p_budget_detail_id => l_budget_detail_id
1157 ,p_budget_version_id => p_budget_version_id
1158 ,p_organization_id => i.organization_id
1159 ,p_position_id => ''
1160 ,p_job_id => ''
1161 ,p_grade_id => ''
1162 ,p_budget_unit1_value => ''
1163 ,p_budget_unit1_percent => ''
1164 ,p_budget_unit1_available => ''
1165 ,p_budget_unit1_value_type_cd => ''
1166 ,p_budget_unit2_value => ''
1167 ,p_budget_unit2_percent => ''
1168 ,p_budget_unit2_available => ''
1169 ,p_budget_unit2_value_type_cd => ''
1170 ,p_budget_unit3_value => ''
1171 ,p_budget_unit3_percent => ''
1172 ,p_budget_unit3_available => ''
1173 ,p_budget_unit3_value_type_cd => ''
1174 ,p_object_version_number => l_object_version_number
1175 );
1176 hr_utility.set_location('inserting '||i.organization_id||l_proc,30);
1177 pqh_budget.insert_org_is_bud(i.organization_id);
1178 end loop;
1179 else
1180 hr_utility.set_location('oh is used '||l_proc,40);
1181 for i in c2 loop
1182 l_rows_inserted := l_rows_inserted + 1;
1183 pqh_budget_details_api.create_budget_detail(
1184 p_validate => FALSE
1185 ,p_budget_detail_id => l_budget_detail_id
1186 ,p_budget_version_id => p_budget_version_id
1187 ,p_organization_id => i.organization_id
1188 ,p_position_id => ''
1189 ,p_job_id => ''
1190 ,p_grade_id => ''
1191 ,p_budget_unit1_value => ''
1192 ,p_budget_unit1_percent => ''
1193 ,p_budget_unit1_available => ''
1194 ,p_budget_unit1_value_type_cd => ''
1195 ,p_budget_unit2_value => ''
1196 ,p_budget_unit2_percent => ''
1197 ,p_budget_unit2_available => ''
1198 ,p_budget_unit2_value_type_cd => ''
1199 ,p_budget_unit3_value => ''
1200 ,p_budget_unit3_percent => ''
1201 ,p_budget_unit3_available => ''
1202 ,p_budget_unit3_value_type_cd => ''
1203 ,p_object_version_number => l_object_version_number
1204 );
1205 hr_utility.set_location('inserting '||i.organization_id||l_proc,50);
1206 pqh_budget.insert_org_is_bud(i.organization_id);
1207 end loop;
1208 end if;
1209 p_rows_inserted := l_rows_inserted;
1210 hr_utility.set_location('entering '||l_proc,60);
1211 exception when others then
1212 p_rows_inserted := null;
1213 raise;
1214 end populate_bud_organizations;
1215 function get_wks_budget( p_worksheet_id in number) return number is
1216 cursor c1 is select budget_id from pqh_worksheets
1217 where worksheet_id = p_worksheet_id;
1218 l_budget_id number;
1219 begin
1220 open c1;
1221 fetch c1 into l_budget_id ;
1222 close c1;
1223 return l_budget_id;
1224 end get_wks_budget;
1225 function get_wkd_budget( p_worksheet_detail_id in number) return number is
1226 cursor c1 is select worksheet_id from pqh_worksheet_details
1227 where worksheet_detail_id = p_worksheet_detail_id;
1228 l_worksheet_id number;
1229 l_budget_id number;
1230 begin
1231 open c1;
1232 fetch c1 into l_worksheet_id ;
1233 close c1;
1234 l_budget_id := get_wks_budget(p_worksheet_id => l_worksheet_id);
1235 return l_budget_id;
1236 end get_wkd_budget;
1237 function get_bgd_budget( p_budget_detail_id in number) return number is
1238 cursor c1 is select budget_id
1239 from pqh_budget_versions bgv, pqh_budget_details bgd
1240 where bgd.budget_detail_id = p_budget_detail_id
1241 and bgd.budget_version_id = bgv.budget_version_id ;
1242 l_budget_id number;
1243 begin
1244 open c1;
1245 fetch c1 into l_budget_id ;
1246 close c1;
1247 return l_budget_id;
1248 end get_bgd_budget;
1249 procedure insert_default_period(p_worksheet_detail_id in number,
1250 p_wkd_ovn in out nocopy number,
1251 p_worksheet_unit1_value in number default null,
1252 p_worksheet_unit2_value in number default null,
1253 p_worksheet_unit3_value in number default null,
1254 p_worksheet_period_id out nocopy number,
1255 p_wpr_ovn out nocopy number) is
1256 l_wkd_ovn number := p_wkd_ovn;
1257 l_budget_id number;
1258 l_calendar varchar2(30);
1259 l_budget_start_date date;
1260 l_budget_end_date date;
1261 l_period_start_date date;
1262 l_period_end_date date;
1263 l_start_time_period_id number;
1264 l_end_time_period_id number;
1265 cursor c1 is
1266 select time_period_id,start_date
1267 from per_time_periods
1268 where period_set_name = l_calendar
1269 and start_date >= l_budget_start_date
1270 and start_date < l_budget_end_date
1271 order by start_date;
1272 cursor c2 is
1273 select time_period_id,end_date
1274 from per_time_periods
1275 where period_set_name = l_calendar
1276 and end_date > l_budget_start_date
1277 and end_date <= l_budget_end_date
1278 and end_date > l_period_start_date
1279 order by end_date desc;
1280 l_proc varchar2(100) := g_package||'insert_default_period' ;
1281 begin
1282 hr_utility.set_location('entering '||l_proc,10);
1283 hr_utility.set_location('unit1_value '||p_worksheet_unit1_value||l_proc,11);
1284 hr_utility.set_location('unit2_value '||p_worksheet_unit2_value||l_proc,12);
1285 hr_utility.set_location('unit3_value '||p_worksheet_unit3_value||l_proc,13);
1286 l_budget_id := get_wkd_budget(p_worksheet_detail_id);
1287 hr_utility.set_location('budget id is '||l_budget_id||l_proc,20);
1288 select period_set_name,budget_start_date,budget_end_date
1289 into l_calendar,l_budget_start_date,l_budget_end_date
1290 from pqh_budgets
1291 where budget_id = l_budget_id;
1292 hr_utility.set_location('calendar id is '||l_calendar||l_proc,30);
1293 hr_utility.set_location('budget start date is '||to_char(l_budget_start_date,'DD/MM/RRRR')||l_proc,40);
1294 hr_utility.set_location('budget_end date id is '||to_char(l_budget_end_date,'DD/MM/RRRR')||l_proc,50);
1295 open c1;
1296 fetch c1 into l_start_time_period_id,l_period_start_date;
1297 hr_utility.set_location('period_start date id is '||to_char(l_period_start_date,'DD/MM/RRRR')||l_proc,60);
1298 if c1%found then
1299 open c2;
1300 fetch c2 into l_end_time_period_id,l_period_end_date;
1301 hr_utility.set_location('period_end date id is '||to_char(l_period_end_date,'DD/MM/RRRR')||l_proc,70);
1302 close c2;
1303 end if;
1304 close c1;
1305 if l_end_time_period_id is null then
1306 hr_utility.set_location('no period lies during budget life'||l_proc,80);
1307 pqh_budget.update_worksheet_detail(
1308 p_worksheet_detail_id => p_worksheet_detail_id,
1309 p_object_version_number => p_wkd_ovn,
1310 p_effective_date => trunc(sysdate),
1311 p_budget_unit1_available => p_worksheet_unit1_value,
1312 p_budget_unit2_available => p_worksheet_unit1_value,
1313 p_budget_unit3_available => p_worksheet_unit1_value);
1314 hr_utility.set_location('ovn of wkd after is'||p_wkd_ovn||l_proc,90);
1315 else
1316 hr_utility.set_location('inserting worksheet period '||l_proc,100);
1317 pqh_worksheet_periods_api.create_worksheet_period
1318 (
1319 p_worksheet_period_id => p_worksheet_period_id
1320 ,p_end_time_period_id => l_end_time_period_id
1321 ,p_worksheet_detail_id => p_worksheet_detail_id
1322 ,p_budget_unit1_percent => 100
1323 ,p_budget_unit2_percent => 100
1324 ,p_budget_unit3_percent => 100
1325 ,p_budget_unit1_value => p_worksheet_unit1_value
1326 ,p_budget_unit2_value => p_worksheet_unit2_value
1327 ,p_budget_unit3_value => p_worksheet_unit3_value
1328 ,p_object_version_number => p_wpr_ovn
1329 ,p_budget_unit1_value_type_cd => 'P'
1330 ,p_budget_unit2_value_type_cd => 'P'
1331 ,p_budget_unit3_value_type_cd => 'P'
1332 ,p_start_time_period_id => l_start_time_period_id
1333 ,p_budget_unit1_available => p_worksheet_unit1_value
1334 ,p_budget_unit2_available => p_worksheet_unit2_value
1335 ,p_budget_unit3_available => p_worksheet_unit3_value
1336 ,p_effective_date => trunc(sysdate)
1337 );
1338 end if;
1339 hr_utility.set_location('exiting '||l_proc,1000);
1340 exception when others then
1341 p_wkd_ovn := l_wkd_ovn;
1342 p_worksheet_period_id := null;
1343 p_wpr_ovn := null;
1344 raise;
1345 end insert_default_period;
1346
1347 procedure apply_wks(p_transaction_id in number,
1348 p_transaction_category_id in number,
1349 p_wkd_ovn out nocopy number,
1350 p_wks_ovn out nocopy number) IS
1351 l_transaction_status varchar2(30);
1352 l_wkd_ovn number;
1353 l_wks_ovn number;
1354 l_proc varchar2(61) := g_package||'apply_wks' ;
1355 BEGIN
1356 hr_utility.set_location('entering '||l_proc,10);
1357 approve_wks(p_transaction_id => p_transaction_id,
1358 p_transaction_category_id => p_transaction_category_id,
1359 p_wkd_ovn => l_wkd_ovn,
1360 p_wks_ovn => l_wks_ovn);
1361 hr_utility.set_location(l_proc||'wks_ovn is '||l_wks_ovn,20);
1362 hr_utility.set_location(l_proc||'wkd_ovn is '||l_wkd_ovn,30);
1363 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1364 p_transaction_category_id => p_transaction_category_id,
1365 p_result_status => 'SUBMITTED',
1366 p_wkd_object_version_number => p_wkd_ovn,
1367 p_wks_object_version_number => p_wks_ovn);
1368 hr_utility.set_location(l_proc||'wks_ovn is '||l_wks_ovn,40);
1369 hr_utility.set_location(l_proc||'wkd_ovn is '||l_wkd_ovn,50);
1370 exception when others then
1371 p_wkd_ovn := null;
1372 p_wks_ovn := null;
1373 raise;
1374 END;
1375 procedure pending_wks(p_transaction_id in number,
1376 p_transaction_category_id in number,
1377 p_wkd_ovn out nocopy number,
1378 p_wks_ovn out nocopy number) IS
1379 l_user varchar2(100);
1380 l_transaction_status varchar2(30);
1381 l_status varchar2(30);
1382 l_working_users varchar2(2000);
1383 l_proc varchar2(61) := g_package||'pending_wks' ;
1384 BEGIN
1385 hr_utility.set_location('entering'||l_proc,10);
1386 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1387 p_transaction_category_id => p_transaction_category_id,
1388 p_result_status => 'PENDING',
1389 p_wkd_object_version_number => p_wkd_ovn,
1390 p_wks_object_version_number => p_wks_ovn);
1391 hr_utility.set_location(l_proc||'wks_ovn is '||p_wks_ovn,40);
1392 hr_utility.set_location(l_proc||'wkd_ovn is '||p_wkd_ovn,50);
1393 exception when others then
1394 p_wkd_ovn := null;
1395 p_wks_ovn := null;
1396 raise;
1397 END;
1398 procedure approve_wks(p_transaction_id in number,
1399 p_transaction_category_id in number,
1400 p_wkd_ovn out nocopy number,
1401 p_wks_ovn out nocopy number) IS
1402 l_user varchar2(100);
1403 l_transaction_status varchar2(30);
1404 l_status varchar2(30);
1405 l_working_users varchar2(2000);
1406 l_proc varchar2(61) := g_package||'approve_wks' ;
1407 BEGIN
1408 hr_utility.set_location('entering'||l_proc,10);
1409 pqh_budget.lock_all_children(p_worksheet_detail_id => p_transaction_id,
1410 p_transaction_category_id => p_transaction_category_id,
1411 p_status => l_status,
1412 p_working_users => l_working_users);
1413 hr_utility.set_location('child locked'||l_proc,20);
1414 if nvl(l_status,'Y') ='Y' then
1415 -- close notifications and change status
1416 hr_utility.set_location('changing status'||l_proc,30);
1417 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1418 p_transaction_category_id => p_transaction_category_id,
1419 p_result_status => 'APPROVED',
1420 p_wkd_object_version_number => p_wkd_ovn,
1421 p_wks_object_version_number => p_wks_ovn);
1422 hr_utility.set_location('status changed'||l_proc,40);
1423 else
1424 hr_utility.set_message(8302,'PQH_WKS_CHILD_WORKING');
1425 hr_utility.set_message_token('USERS',l_working_users);
1426 hr_utility.raise_error;
1427 end if;
1428 hr_utility.set_location('wks out nocopy ovn is'||p_wks_ovn||l_proc,90);
1429 hr_utility.set_location('wkd out nocopy ovn is'||p_wkd_ovn||l_proc,100);
1430 exception when others then
1431 p_wkd_ovn := null;
1432 p_wks_ovn := null;
1433 raise;
1434 END;
1435 procedure reject_wks(p_transaction_id in number,
1436 p_transaction_category_id in number,
1437 p_wkd_ovn out nocopy number,
1438 p_wks_ovn out nocopy number) IS
1439 l_user varchar2(100);
1440 l_transaction_status varchar2(30);
1441 l_status varchar2(30);
1442 l_working_users varchar2(2000);
1443 l_proc varchar2(61) := g_package||'reject_wks' ;
1444 BEGIN
1445 hr_utility.set_location('entering'||l_proc,10);
1446 -- if the current user is the initiator of the txn then mark the status
1447 -- as reject else status remains the same and notification will be sent to initiator.
1448 l_user := pqh_wf.get_requestor(p_transaction_category_id => p_transaction_category_id,
1449 p_transaction_id => p_transaction_id);
1450 hr_utility.set_location('requestor is'||l_user||l_proc,20);
1451 -- change the status of the delegated rows to reject
1452 -- and all open notifications to be killed
1453 -- depending upon the initator of the delegated row
1454 if l_user is null or l_user = fnd_profile.value('USERNAME') then
1455 hr_utility.set_location('going for lock'||l_proc,30);
1456 pqh_budget.lock_all_children(p_worksheet_detail_id => p_transaction_id,
1457 p_transaction_category_id => p_transaction_category_id,
1458 p_status => l_status,
1459 p_working_users => l_working_users);
1460 hr_utility.set_location('locked'||l_proc,40);
1461 if nvl(l_status,'Y') ='Y' then
1462 -- notifications are to be closed and change status
1463 hr_utility.set_location('changing status'||l_proc,50);
1464 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1465 p_transaction_category_id => p_transaction_category_id,
1466 p_result_status => 'REJECT',
1467 p_wkd_object_version_number => p_wkd_ovn,
1468 p_wks_object_version_number => p_wks_ovn);
1469 hr_utility.set_location('status changed'||l_proc,60);
1470 else
1471 hr_utility.set_message(8302,'PQH_WKS_CHILD_WORKING');
1472 hr_utility.set_message_token('USERS',l_working_users);
1473 hr_utility.raise_error;
1474 end if;
1475 else
1476 hr_utility.set_location('changing status'||l_proc,70);
1477 pqh_budget.complete_workflow(p_worksheet_detail_id => p_transaction_id,
1478 p_transaction_category_id => p_transaction_category_id,
1479 p_result_status => 'PENDING',
1480 p_wkd_object_version_number => p_wkd_ovn,
1481 p_wks_object_version_number => p_wks_ovn);
1482 hr_utility.set_location('status changed'||l_proc,80);
1483 end if;
1484 hr_utility.set_location('wks out nocopy ovn is'||p_wks_ovn||l_proc,90);
1485 hr_utility.set_location('wkd out nocopy ovn is'||p_wkd_ovn||l_proc,100);
1486 exception when others then
1487 p_wkd_ovn := null;
1488 p_wks_ovn := null;
1489 raise;
1490 END;
1491 function get_transaction_name(p_worksheet_detail_id in number) return varchar2 is
1492 l_worksheet_name varchar2(240);
1493 l_org_name hr_all_organization_units.name%type;
1494 l_org_id number;
1495 l_transaction_name varchar2(300);
1496 cursor c1 is
1497 select wks.worksheet_name,wkd.organization_id
1498 from pqh_worksheets wks, pqh_worksheet_details wkd
1499 where wkd.worksheet_id = wks.worksheet_id
1500 and worksheet_detail_id = p_worksheet_detail_id
1501 and nvl(action_cd,'D') ='D';
1502 begin
1503 open c1;
1504 fetch c1 into l_worksheet_name,l_org_id;
1505 close c1;
1506 --
1507 if l_org_id is not null then
1508 l_org_name := hr_general.decode_organization(l_org_id);
1509 l_transaction_name := l_worksheet_name||'('||l_org_name||')';
1510 else
1511 l_transaction_name := l_worksheet_name;
1512 end if;
1513 return l_transaction_name;
1514 end;
1515
1516 Function check_job_pos_for_valid_grd(p_position_id number default null,
1517 p_job_id number default null,
1518 p_grade_id number default null,
1519 p_valid_grade_flag varchar2 default null)
1520 Return varchar2 is
1521 Cursor C_position is select valid_grade_id
1522 from per_valid_grades
1523 where position_id = p_position_id
1524 and grade_id = p_grade_id
1525 and rownum < 2;
1526
1527 Cursor C_job is select valid_grade_id
1528 from per_valid_grades
1529 where job_id = p_job_id
1530 and grade_id = p_grade_id
1531 and rownum < 2;
1532
1533 l_valid_grade_id per_valid_grades.valid_grade_id%type;
1534 Begin
1535 If nvl(p_grade_id,0) <> 0 then
1536 If nvl(p_valid_grade_flag,'N') = 'Y' then
1537 If p_position_id is not null then
1538 Open C_position;
1539 Fetch C_position into l_valid_grade_id;
1540 Close C_position;
1541 Elsif p_job_id is not null then
1542 Open C_job;
1543 Fetch C_job into l_valid_grade_id;
1544 Close C_job;
1545 End if;
1546 If l_valid_grade_id is null then
1547 Return 'FALSE';
1548 Else
1549 Return 'TRUE';
1550 End If;
1551 Else
1552 Return 'TRUE';
1553 End If;
1554 Else
1555 Return 'TRUE';
1556 End if;
1557 End;
1558
1559 Function get_valid_grade(p_position_id number default null,
1560 p_job_id number default null,
1561 p_grade_id number default null,
1562 p_start_bud_date date,
1563 p_end_bud_date date)
1564 Return varchar2 is
1565 l_job_id per_valid_grades.job_id%type :=p_job_id;
1566 Cursor C_job is select valid_grade_id
1567 from per_valid_grades
1568 where job_id = l_job_id
1569 and grade_id = p_grade_id
1570 and date_from < p_end_bud_date
1571 and (date_to > p_start_bud_date or date_to is null)
1572 and rownum < 2;
1573
1574 Cursor C_position is select valid_grade_id
1575 from per_valid_grades
1576 where position_id = p_position_id
1577 and grade_id = p_grade_id
1578 and date_from < p_end_bud_date
1579 and (date_to > p_start_bud_date or date_to is null)
1580 and rownum < 2;
1581
1582 Cursor C2 is select valid_grade_id
1583 from per_valid_grades
1584 where grade_id = p_grade_id
1585 and date_from < p_end_bud_date
1586 and (date_to > p_start_bud_date or date_to is null)
1587 and rownum < 2;
1588 l_valid_grade_id per_valid_grades.valid_grade_id%type;
1589 Begin
1590 If p_position_id is not null then
1591 Open C_position;
1592 Fetch C_position into l_valid_grade_id;
1593 Close C_position;
1594 Elsif l_job_id is not null then
1595 Open C_job;
1596 Fetch C_job into l_valid_grade_id;
1597 Close C_job;
1598 Elsif l_job_id is null and p_position_id is null then
1599 Open C2;
1600 Fetch C2 into l_valid_grade_id;
1601 Close C2;
1602 End If;
1603 If l_valid_grade_id is null then
1604 Return 'FALSE';
1605 Else
1606 Return 'TRUE';
1607 End If;
1608 End;
1609
1610 Function get_position_budget_flag(p_availability_status_id in number)
1611 return varchar2 is
1612
1613 l_budget_flag varchar2(150) := 'Y';
1614 --
1615 -- Get the budget flag value stored in the information1 column
1616 --
1617 Cursor c_budget_flag is
1618 Select nvl(information1,'Y')
1619 from per_shared_types
1620 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
1621 and shared_type_id = p_availability_status_id;
1622
1623 Begin
1624 --
1625 -- Fetch the Budget Flag value
1626 --
1627 Open c_budget_flag;
1628 Fetch c_budget_flag into l_budget_flag;
1629 Close c_budget_flag;
1630
1631 Return l_budget_flag;
1632
1633 End;
1634
1635 /*
1636 function valid_position_txn(p_position_transaction_id in number,
1637 p_budget_start_date in date,
1638 p_budget_end_date in date) is
1639 l_org_id number;
1640 l_job_id number;
1641 l_pos_start_date date;
1642 l_pos_end_date date;
1643 l_org_start_date date;
1644 l_org_end_date date;
1645 l_job_start_date date;
1646 l_job_end_date date;
1647 begin
1648 select organization_id,job_id,effective_start_date,effective_end_date
1649 into l_org_id,l_job_id,l_pos_start_date,l_pos_end_date
1650 from pqh_position_transactions
1651 where position_transaction_id = p_position_transaction_id;
1652 if l_org_id is not null then
1653 end if;
1654 if l_job_id is not null then
1655 end if;
1656 end valid_position_txn;
1657 */
1658 procedure update_wkd_pot(p_worksheet_detail_id number) is
1659 begin
1660 update pqh_worksheet_details
1661 set position_transaction_id = null
1662 where worksheet_detail_id = p_worksheet_detail_id;
1663 end update_wkd_pot;
1664
1665 procedure purge_wkd(p_worksheet_detail_id in number,
1666 p_budget_style_cd in varchar2) is
1667 cursor c_worksheet_periods is select worksheet_period_id,object_version_number
1668 from pqh_worksheet_periods where worksheet_detail_id = p_worksheet_detail_id;
1669 cursor c_worksheet_budget_sets(p_worksheet_period_id number) is
1670 select worksheet_budget_set_id,object_version_number
1671 from pqh_worksheet_budget_sets where worksheet_period_id = p_worksheet_period_id;
1672 cursor c_worksheet_bdgt_elmnts (p_worksheet_budget_set_id number) is
1673 select worksheet_bdgt_elmnt_id,object_version_number
1674 from pqh_worksheet_bdgt_elmnts where worksheet_budget_set_id = p_worksheet_budget_set_id;
1675 cursor c_worksheet_fund_srcs (p_worksheet_bdgt_elmnt_id number) is
1676 select worksheet_fund_src_id,object_version_number
1677 from pqh_worksheet_fund_srcs where worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
1678 l_parent_wkd_id number;
1679 l_budget_unit1_value number;
1680 l_budget_unit2_value number;
1681 l_budget_unit3_value number;
1682 l_object_version_number number;
1683 begin
1684 for i in c_worksheet_periods loop
1685 for j in c_worksheet_budget_sets(i.worksheet_period_id) loop
1686 for k in c_worksheet_bdgt_elmnts(j.worksheet_budget_set_id) loop
1687 for l in c_worksheet_fund_srcs(k.worksheet_bdgt_elmnt_id) loop
1688 pqh_worksheet_fund_srcs_api.DELETE_WORKSHEET_FUND_SRC(
1689 P_WORKSHEET_FUND_SRC_ID => l.worksheet_fund_src_id,
1690 P_OBJECT_VERSION_NUMBER => l.object_version_number);
1691 end loop;
1692 pqh_worksheet_bdgt_elmnts_api.DELETE_WORKSHEET_BDGT_ELMNT(
1693 P_WORKSHEET_BDGT_ELMNT_ID => k.worksheet_bdgt_elmnt_id,
1694 P_OBJECT_VERSION_NUMBER => k.object_version_number);
1695 end loop;
1696 pqh_worksheet_budget_sets_api.DELETE_WORKSHEET_BUDGET_SET(
1697 P_WORKSHEET_BUDGET_SET_ID => j.worksheet_budget_set_id,
1698 P_EFFECTIVE_DATE => trunc(sysdate),
1699 P_OBJECT_VERSION_NUMBER => j.object_version_number);
1700 end loop;
1701 pqh_worksheet_periods_api.DELETE_WORKSHEET_PERIOD(
1702 P_WORKSHEET_PERIOD_ID => i.worksheet_period_id,
1703 P_EFFECTIVE_DATE => trunc(sysdate),
1704 P_OBJECT_VERSION_NUMBER => i.object_version_number);
1705 end loop;
1706
1707 select parent_worksheet_detail_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,object_version_number
1708 into l_parent_wkd_id,l_budget_unit1_value,l_budget_unit2_value,l_budget_unit3_value,l_object_version_number
1709 from pqh_worksheet_details where worksheet_detail_id = p_worksheet_detail_id;
1710
1711 if p_budget_style_cd ='TOP' then
1712 update pqh_worksheet_details
1713 set budget_unit1_available = nvl(budget_unit1_available,0) - nvl(l_budget_unit1_value,0),
1714 budget_unit2_available = nvl(budget_unit2_available,0) - nvl(l_budget_unit2_value,0),
1715 budget_unit3_available = nvl(budget_unit3_available,0) - nvl(l_budget_unit3_value,0)
1716 where worksheet_detail_id = l_parent_wkd_id;
1717 else
1718 update pqh_worksheet_details
1719 set budget_unit1_value = nvl(budget_unit1_value,0) - nvl(l_budget_unit1_value,0),
1720 budget_unit2_value = nvl(budget_unit2_value,0) - nvl(l_budget_unit2_value,0),
1721 budget_unit3_value = nvl(budget_unit3_value,0) - nvl(l_budget_unit3_value,0)
1722 where worksheet_detail_id = l_parent_wkd_id;
1723 end if;
1724 pqh_worksheet_details_api.DELETE_WORKSHEET_DETAIL(
1725 P_WORKSHEET_DETAIL_ID => p_worksheet_detail_id,
1726 P_EFFECTIVE_DATE => trunc(sysdate),
1727 P_OBJECT_VERSION_NUMBER => l_object_version_number);
1728 end purge_wkd;
1729 procedure delete_wkd(p_worksheet_detail_id in number,
1730 p_object_version_number in number) is
1731 l_proc varchar2(100) := g_package||'delete_wkd' ;
1732 l_budget_id number;
1733 l_position_id number;
1734 l_budgeted_entity_cd varchar2(80);
1735 l_budget_style_cd varchar2(80);
1736 l_worksheet_detail_id number;
1737 cursor c_worksheet_detail is
1738 select position_id,worksheet_detail_id
1739 from pqh_worksheet_details
1740 where worksheet_detail_id = p_worksheet_detail_id;
1741 begin
1742 /*
1743 ---------- logic of the program --------------------------------
1744 check whether worksheet detail exist in the system or not, if yes then do these
1745 if primary budget entity is Position
1746 if Position_id is there then
1747 Position transaction should be updated to null
1748 else
1749 delete the dependent records of worksheet period, budgetsets etc.
1750 delete the worksheet_detail
1751 update the parent worksheet_detail balances
1752 end if;
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 */
1759 ---------- actual program --------------------------------
1760 open c_worksheet_detail;
1761 fetch c_worksheet_detail into l_position_id,l_worksheet_detail_id;
1762 if c_worksheet_detail%found then
1763 l_budget_id := get_wkd_budget(p_worksheet_detail_id);
1764 select budgeted_entity_cd,budget_style_cd into l_budgeted_entity_cd,l_budget_style_cd
1765 from pqh_budgets where budget_id = l_budget_id;
1766 if l_budgeted_entity_cd ='POSITION' then
1767 if l_position_id is not null then
1768 update_wkd_pot(p_worksheet_detail_id => p_worksheet_detail_id);
1769 else
1770 purge_wkd(p_worksheet_detail_id => p_worksheet_detail_id,
1771 p_budget_style_cd => l_budget_style_cd);
1772 end if;
1773 else
1774 purge_wkd(p_worksheet_detail_id => p_worksheet_detail_id,
1775 p_budget_style_cd => l_budget_style_cd);
1776 end if;
1777 end if;
1778 close c_worksheet_detail;
1779 end delete_wkd;
1780
1781 Function PQH_CHECK_GMS_INSTALLED
1782 RETURN varchar2 IS
1783 cursor gms is select a.status, a.application_id, b.application_short_name
1784 from
1785 fnd_product_installations a, fnd_application b
1786 where
1787 a.application_id = b.application_id
1788 and
1789 b.application_short_name = 'GMS' and status = 'I';
1790 stat varchar2(10) := 'N';
1791 BEGIN
1792 for gms_rec in gms loop
1793 stat := gms_rec.status;
1794 end loop;
1795 RETURN stat;
1796 EXCEPTION
1797 WHEN others THEN
1798 RETURN stat;
1799 END; -- Function PQH_CHECK_GMS_INSTALL
1800 end pqh_wks_budget;