[Home] [Help]
PACKAGE BODY: APPS.PQH_BUDGET_ANALYSIS_PKG
Source
1 Package body pqh_budget_analysis_pkg as
2 /* $Header: pqbgtanl.pkb 120.0 2005/05/29 01:30:54 appldev noship $ */
3 --
4 --
5 procedure salary_analysis(
6 errbuf out nocopy varchar2
7 , retcode out nocopy varchar2
8 , p_batch_name varchar2
9 , p_effective_date varchar2
10 , p_start_org_id number
11 , p_start_date varchar2
12 , p_end_date varchar2
13 , p_business_group_id number
14 ) is
15 --
16 l_proc_name varchar2(30) := 'SALARY_ANALYSIS';
17 --
18 l_dummy varchar2(10);
19 l_effective_date date;
20 l_start_date date;
21 l_end_date date;
22 l_error_msg varchar2(1000);
23 l_parameter1_value varchar2(100);
24 l_parameter2_value varchar2(100);
25 l_parameter3_value varchar2(100);
26 l_parameter4_value varchar2(100);
27 l_parameter5_value varchar2(100);
28 l_parameter6_value varchar2(100);
29 l_parameter7_value varchar2(100);
30 l_parameter8_value varchar2(100);
31 l_parameter9_value varchar2(100);
32 --
33 -- Cursor to fetch Positions for the given Organization
34 --
35 -- PMFLETCH Now selects from MLS view
36 cursor c_positions(p_organization_id number, p_effective_date date) is
37 select
38 pos.position_id, pos.job_id, name
39 from
40 hr_all_positions_f_vl pos
41 where
42 pos.organization_id = p_organization_id
43 and p_effective_date between pos.effective_start_date and pos.effective_end_date
44 order by position_id;
45
46 --
47 -- Cursor to Fetch the Organization Structure Version
48 --
49 cursor c_org_version(p_effective_date date) is
50 select
51 ver.org_structure_version_id
52 from
53 per_organization_structures str
54 , per_org_structure_versions ver
55 where
56 str.position_control_structure_flg = 'Y'
57 and str.business_group_id = p_business_group_id
58 and ver.business_group_id = p_business_group_id
59 and str.organization_structure_id = ver.organization_structure_id
60 and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
61
62 --
63 -- Cursor toFetch the Organizations for the given Organization Hierarchy
64 --
65 cursor c_org(p_org_structure_version_id number, p_start_org_id number) is
66 SELECT
67 0 rn,
68 0 level1,
69 ORGANIZATION_ID
70 FROM HR_ALL_ORGANIZATION_UNITS u
71 WHERE ORGANIZATION_ID = p_start_org_id
72 and business_group_id = p_business_group_id
73 and exists
74 (select null from per_org_structure_elements e
75 where e.org_structure_version_id = p_org_structure_version_id
76 and (e.organization_id_child = p_start_org_id
77 or e.organization_id_parent = p_start_org_id) )
78 UNION
79 SELECT
80 rownum rn,
81 level level1,
82 organization_id_child organization_id
83 FROM PER_ORG_STRUCTURE_ELEMENTS A
84 start with
85 organization_id_parent = p_start_org_id
86 and ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
87 connect by
88 organization_id_parent = prior organization_id_child
89 and ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id;
90 --
91 --
92 -- Cursor that checks the batch existance
93 --
94 cursor check_batch_name(p_batch_name varchar2) is
95 select
96 'x'
97 from
98 pqh_process_log
99 where
100 log_context=p_batch_name;
101
102 --
103 -- Cursor to get the next batch Id for the Process Log
104 --
105 cursor c_batch is
106 select
107 pqh_process_log_s.nextval
108 from
109 dual;
110
111 --
112 -- Cursor to fetch the table_route_id of the table_alias
113 --
114 cursor c_table_route(p_table_alias varchar2) is
115 SELECT
116 table_route_id
117 from
118 pqh_table_route
119 where
120 table_alias = p_table_alias;
121
122 --
123 -- Cursor to select workflow sequence no
124 --
125 cursor c_wf_seq_no is
126 select pqh_wf_notifications_s.nextval
127 from dual;
128
129 --
130 -- Cursor to select user name
131 --
132 cursor c_user_name(p_position_id number) is
133 select user_name
134 from fnd_user
135 where employee_id =
136 (select psf.supervisor_id
137 from hr_all_positions_f psf
138 where psf.position_id = p_position_id
139 and l_effective_date >= psf.effective_start_date
140 and l_effective_date <= psf.effective_end_date
141 );
142 --
143 -- Local Variables
144 --
145 l_org_structure_version_id number;
146 --
147 l_budgeted_sal number;
148 l_reallocation_sal number;
149 l_actual_sal number;
150 l_commitment_sal number;
151 l_actual_commitment_sal number;
152 --
153 l_actuals_status number;
154 l_batch_id number;
155 l_table_route_id number;
156 --
157 l_transaction_category_id number;
158 l_workflow_seq_no number;
159 l_user_name varchar2(30);
160 l_apply_error_mesg varchar2(100);
161 l_apply_error_num varchar2(100);
162 --
163 l_message_type_cd varchar2(10);
164 l_message_type varchar2(100);
165 l_message varchar2(1000);
166 --
167 l_currency_code varchar2(40);
168 --
169 begin
170 --
171 hr_utility.set_location('Entering'|| l_proc_name, 10);
172 retcode := 0;
173 --
174 hr_utility.set_location('Entering'|| l_proc_name, 11);
175 --
176 open check_batch_name(p_batch_name);
177 fetch check_batch_name into l_dummy;
178 if check_batch_name%found then
179 retcode := -1;
180 fnd_message.set_name('PQH', 'PQH_PLG_DUP_BATCH');
181 fnd_message.set_token('BATCH_NAME', p_batch_name);
182 errbuf := fnd_message.get;
183 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
184 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
185 return;
186 end if;
187 --
188 hr_utility.set_location('Entering'|| l_proc_name, 13);
189 --
190 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
191 l_start_date := fnd_date.canonical_to_date(p_start_date);
192 l_end_date := fnd_date.canonical_to_date(p_end_date);
193 --
194 -- Fetch the Organization Structure Version
195 --
196 open c_org_version(l_effective_date);
197 fetch c_org_version into l_org_structure_version_id;
198 close c_org_version;
199 --
200 --
201 hr_utility.set_location('Entering'|| l_proc_name, 14);
202 --
203 --
204 -- Fetch the batch Id into the l_batch_id
205 --
206 open c_batch;
207 fetch c_batch into l_batch_id;
208 close c_batch;
209 --
210 hr_utility.set_location('l_batch_id : '||l_batch_id ||' - ' || l_proc_name, 15);
211 --
212 -- Create the start record into the Process Log
213 --
214 pqh_process_batch_log.start_log
215 (
216 p_batch_id =>l_batch_id,
217 p_module_cd =>'POSITION_BUDGET_ANALYSIS',
218 p_log_context =>p_batch_name,
219 p_information3 =>p_effective_date,
220 p_information4 =>p_start_org_id,
221 p_information5 =>p_start_date,
222 p_information6 =>p_end_date
223 );
224 --
225 --
226 hr_utility.set_location('organization Structure Version : '||l_org_structure_version_id ||' '|| l_proc_name, 100);
227 hr_utility.set_location('start organization : '||p_start_org_id ||' '|| l_proc_name, 100);
228
229 --
230 -- Fetch the Organizations from the Organization Hierarchy
231 --
232 for l_organization in c_org(l_org_structure_version_id, p_start_org_id)
233 -- Analyse the Positions of each Organization
234 loop
235
236 hr_utility.set_location('organization : '||l_organization.organization_id ||' '|| l_proc_name, 101);
237 --
238 -- Fetch table route Id for the Organization table(ORU)
239 --
240 open c_table_route('ORU');
241 fetch c_table_route into l_table_route_id;
242 -- Set table route id to null if the table route is not defined for ORU
243 if c_table_route%notfound then
244 l_table_route_id := null;
245 end if;
246 --
247 close c_table_route;
248
249 hr_utility.set_location('l_table_route_id : '||l_table_route_id ||' '|| l_proc_name, 102);
250 --
251 -- Check for the type of the cofigurable message(PQH_UNDER_BGT_POSITIONS)
252 --
253 pqh_utility.set_message(8302,'PQH_UNDER_BGT_POSITIONS', l_organization.organization_id);
254 --
255 hr_utility.set_location('after pqh_utility.set_message : '|| l_proc_name, 103);
256 --
257 l_message_type_cd := pqh_utility.get_message_type_cd;
258 pqh_utility.set_message_token('UOM',
259 hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE','MONEY'));
260 pqh_utility.set_message_token('ENTITY',
261 hr_general.decode_lookup('PQH_BUDGET_ENTITY','POSITION'));
262
263 l_message := pqh_utility.get_message;
264 --
265 hr_utility.set_location('after pqh_utility.get_message : '||l_message_type_cd|| l_proc_name, 104);
266 --
267 if l_message_type_cd in ('E','W') then
268 if l_message_type_cd = 'E' then
269 l_message_type := 'ERROR';
270 else
271 l_message_type := 'WARNING';
272 end if;
273 hr_utility.set_location('before pqh_process_batch_log.set_context_level : '||
274 l_message_type_cd||l_proc_name, 105);
275 hr_utility.set_location('before pqh.set_context_level organization_id: '||
276 l_organization.organization_id, 105);
277 hr_utility.set_location('before pqh.set_context_level l_table_route_id: '||
278 l_table_route_id, 105);
279 hr_utility.set_location('l_orglevel1: '||
280 l_organization.level1, 105);
281 hr_utility.set_location('org name: '||
282 hr_general.decode_organization(l_organization.organization_id), 105);
283 --
284 -- Set the Process Log Context level for the Organization
285 --
286 pqh_process_batch_log.set_context_level
287 (
288 p_txn_id =>l_organization.organization_id,
289 p_txn_table_route_id =>l_table_route_id,
290 p_level =>l_organization.level1 + 1,
291 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
292 );
293 --
294 hr_utility.set_location('Organization : '||l_organization.organization_id
295 || ' ' ||l_proc_name, 110);
296
297 --
298 -- Fetch Positions for the organization
299 --
300 for l_position in c_positions(l_organization.organization_id, l_effective_date)
301 -- Analyse the Position Budgeted Salary
302 loop
303
304 hr_utility.set_location('l_position_id : '||l_position.position_id
305 || ' - ' || substr(l_position.name,1,40) , 110);
306 --
307 -- Get the Budgeted Salary of the Position for the given start date and end date
308 --
309 l_budgeted_sal := pqh_budgeted_salary_pkg.get_pc_budgeted_salary(
310 P_POSITION_ID => l_position.position_id
311 ,p_budget_entity => 'POSITION'
312 ,p_start_date => l_start_date
313 ,p_end_date => l_end_date
314 ,p_effective_date => l_effective_date
315 ,p_business_group_id => p_business_group_id
316 );
317 --
318 -- Get the Reallocation amount(Money) of the Position between the given start date and end date
319 --
320 l_reallocation_sal := pqh_reallocation_pkg.get_reallocation(
321 P_POSITION_ID => l_position.position_id
322 ,p_start_date => l_start_date
323 ,p_end_date => l_end_date
324 ,p_effective_date => l_effective_date
325 ,p_system_budget_unit => 'MONEY'
326 ,p_budget_entity => 'POSITION'
327 ,p_business_group_id => p_business_group_id
328 );
329 pqh_budget_analysis_pkg.get_pos_actual_commit_amt(
330 p_position_id => l_position.position_id,
331 p_start_date => l_start_date,
332 p_end_date => l_end_date,
333 p_effective_date => l_effective_date,
334 p_actual_amount => l_actual_sal,
335 p_commitment_amount => l_commitment_sal,
336 p_total_amount => l_actual_commitment_sal
337 );
338
339 l_currency_code := get_budget_currency(
340 P_POSITION_ID => l_position.position_id
341 ,p_budget_entity => 'POSITION'
342 ,p_start_date => l_start_date
343 ,p_end_date => l_end_date
344 ,p_effective_date => l_effective_date
345 ,p_business_group_id => p_business_group_id
346 );
347 --
348 -- Print the details of the Position
349 --
350 hr_utility.set_location('Position : '||l_position.position_id
351 ||' '||l_proc_name, 130);
352 hr_utility.set_location('Budgeted Salary : '||nvl(l_budgeted_sal,0)
353 ||' '||l_proc_name, 140);
354 hr_utility.set_location('Budget Reallocation : '||nvl(l_reallocation_sal,0)
355 ||' '||l_proc_name, 140);
356 hr_utility.set_location('Actual Salary : '||nvl(l_actual_sal,0)
357 ||' '||l_proc_name, 150);
358 hr_utility.set_location('Commitment Salary : '||nvl(l_commitment_sal,0)
359 ||' '||l_proc_name, 160);
360 hr_utility.set_location('Actual + Commitment Salary : '||nvl(l_actual_commitment_sal,0)
361 ||' '||l_proc_name, 160);
362 --
363 -- Check, whether the Position is Under Budgeted
364 --
365 if (nvl(l_budgeted_sal,0) + nvl(l_reallocation_sal,0) < nvl(l_actual_commitment_sal,0)) then
366 --
367 -- If Under Budgeted
368 --
369
370 --
371 -- Fetch table route Id for the Position table(PSF)
372 --
373 open c_table_route('PSF');
374 fetch c_table_route into l_table_route_id;
375 -- Set table route id to null if the table route is not defined for PSF
376 if c_table_route%notfound then
377 l_table_route_id := null;
378 end if;
379 --
380 close c_table_route;
381 --
382 l_user_name := null;
383 --
384 open c_user_name(l_position.position_id);
385 fetch c_user_name into l_user_name;
386 close c_user_name;
387 --
388 --
389 -- Set the Process Log Context Level for the Position
390 --
391 pqh_process_batch_log.set_context_level
392 (
393 p_txn_id =>l_position.position_id,
394 p_txn_table_route_id =>l_table_route_id,
395 p_level =>l_organization.level1+2,
396 p_log_context =>hr_general.decode_position_latest_name(l_position.position_id)
397 );
398
399 --
400 -- Insert the Log for the position
401 --
402 pqh_process_batch_log.insert_log
403 (
404 p_message_type_cd =>l_message_type,
405 p_message_text =>l_message,
406 p_information3 =>l_budgeted_sal,
407 p_information4 =>l_reallocation_sal,
408 p_information5 =>l_actual_sal,
409 p_information6 =>l_commitment_sal,
410 p_information7 =>l_user_name,
411 p_information13 =>l_currency_code
412 );
413
414 --
415 -- Fetch the FYI Notification Info
416 --
417 l_transaction_category_id :=
418 pqh_workflow.get_txn_cat('POSITION_TRANSACTION', p_business_group_id);
419 --
420 open c_wf_seq_no;
421 fetch c_wf_seq_no into l_workflow_seq_no;
422 close c_wf_seq_no;
423 --
424 hr_utility.set_location('l_position.position_id : '|| l_position.position_id, 1111);
425 hr_utility.set_location('l_user_name : '|| l_user_name, 1111);
426 --
427 if l_user_name is not null then
428 --
429 hr_utility.set_location('l_user_name : '|| l_user_name, 1112);
430 --
431 l_parameter1_value :=
432 hr_general.decode_position_latest_name(l_position.position_id);
433 l_parameter2_value := p_batch_name;
434 l_parameter3_value := l_effective_date;
435 l_parameter4_value :=
436 hr_general.decode_organization(l_organization.organization_id);
437 l_parameter5_value :=
438 hr_general.decode_job(l_position.job_id);
439 l_parameter6_value := l_budgeted_sal;
440 l_parameter7_value := l_reallocation_sal;
441 l_parameter8_value := l_actual_sal;
442 l_parameter9_value := l_commitment_sal;
443 --
444 -- FYI Notifications call
445 --
446 PQH_WF.process_user_action(
447 p_transaction_category_id => l_transaction_category_id
448 , p_transaction_id => l_position.position_id
449 , p_workflow_seq_no => l_workflow_seq_no
450 , p_user_action_cd => 'FYI_NOT'
451 , p_route_to_user => l_user_name
452 , p_parameter1_value => l_parameter1_value
453 , p_parameter2_value => l_parameter2_value
454 , p_parameter3_value => l_parameter3_value
455 , p_parameter4_value => l_parameter4_value
456 , p_parameter5_value => l_parameter5_value
457 , p_parameter6_value => l_parameter6_value
458 , p_parameter7_value => l_parameter7_value
459 , p_parameter8_value => l_parameter8_value
460 , p_parameter9_value => l_parameter9_value
461 , p_apply_error_mesg => l_apply_error_mesg
462 , p_apply_error_num => l_apply_error_num
463 );
464 end if;
465 --
466 hr_utility.set_location(l_position.position_id || ' is under budgeted'
467 ||' '||l_proc_name, 170);
468 --
469 end if;
470 --
471 end loop;
472 --
473 end if;
474 --
475 end loop;
476 --
477 -- End the Process Log
478 --
479 pqh_process_batch_log.end_log;
480 hr_utility.set_location('End Process'
481 ||' '||l_proc_name, 180);
482 commit;
483 exception
484 when others then
485 retcode := -1;
486 --hr_utility.set_location('Error '||sqlerrm,190);
487 --
488 end;
489 --
490 /*
491 FUNCTION get_position_commitment(p_position_id in number,
492 p_start_date in date,
493 p_end_date in date) RETURN NUMBER
494 is
495 --
496 cursor c_budgets(p_start_date date, p_end_date date) is
497 select budget_id, budget_start_date, budget_end_date
498 from pqh_budgets
499 where
500 nvl(position_control_flag,'X') = 'Y'
501 and budgeted_entity_cd = 'POSITION'
502 and ((p_start_date <= budget_start_date
503 and p_end_date >= budget_end_date
504 ) or
505 (p_start_date between budget_start_date and budget_end_date) or
506 (p_end_date between budget_start_date and budget_end_date)
507 )
508 and (
509 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
510 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
511 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
512 );
513 --
514 calc_start_date date;
515 calc_end_date date;
516 l_budget_pos_cmmtmnt number := 0;
517 l_pos_cmmtmnt number := 0;
518 --
519 begin
520 --
521 for l_budgets in c_budgets(p_start_date, p_end_date)
522 loop
523 calc_start_date := greatest(l_budgets.budget_start_date, p_start_date);
524 calc_end_date := least(l_budgets.budget_end_date, p_end_date);
525 --
526 l_budget_pos_cmmtmnt := pqh_commitment_pkg.get_position_commitment(
527 p_position_id => p_position_id,
528 p_budget_id => l_budgets.budget_id,
529 p_frequency => null,
530 p_period_start_date => calc_start_date,
531 p_period_end_date => calc_end_date
532 );
533 --
534 l_pos_cmmtmnt := nvl(l_pos_cmmtmnt,0) + nvl(l_budget_pos_cmmtmnt,0);
535 --
536 end loop;
537 --
538 return(l_pos_cmmtmnt);
539 --
540 end;
541 */
542 --
543 PROCEDURE get_pos_actual_commit_amt(p_position_id in number,
544 p_start_date in date,
545 p_end_date in date,
546 p_effective_date in date,
547 p_actual_amount OUT nocopy number,
548 p_commitment_amount OUT nocopy number,
549 p_total_amount OUT nocopy number
550 ) is
551 --
552 cursor c_budgets(p_start_date date, p_end_date date, p_effective_date date) is
553 select bgt.budget_id, budget_version_id, budget_start_date, budget_end_date
554 from pqh_budgets bgt, pqh_budget_versions ver
555 where
556 bgt.budget_id = ver.budget_id
557 and (p_effective_date between date_from and date_to)
558 and nvl(position_control_flag,'X') = 'Y'
559 and budgeted_entity_cd = 'POSITION'
560 and ((p_start_date <= budget_start_date
561 and p_end_date >= budget_end_date
562 ) or
563 (p_start_date between budget_start_date and budget_end_date) or
564 (p_end_date between budget_start_date and budget_end_date)
565 )
566 and (
567 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
568 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
569 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
570 );
571 --
572 calc_start_date date;
573 calc_end_date date;
574 l_budget_pos_cmmtmnt number := 0;
575 l_pos_cmmtmnt number := 0;
576 l_budget_pos_actuals number := 0;
577 l_pos_actuals number := 0;
578 l_budget_pos_total_amt number := 0;
579 l_pos_total_amt number := 0;
580 --
581 begin
582 --
583 for l_budgets in c_budgets(p_start_date, p_end_date, p_effective_date)
584 loop
585 calc_start_date := greatest(l_budgets.budget_start_date, p_start_date);
586 calc_end_date := least(l_budgets.budget_end_date, p_end_date);
587 --
588 pqh_bdgt_actual_cmmtmnt_pkg.get_pos_money_amounts
589 (
590 p_budget_version_id => l_budgets.budget_version_id,
591 p_position_id => p_position_id,
592 p_start_date => calc_start_date,
593 p_end_date => calc_end_date,
594 p_actual_amount => l_budget_pos_actuals,
595 p_commitment_amount => l_budget_pos_cmmtmnt,
596 p_total_amount => l_budget_pos_total_amt
597 );
598 --
599 l_pos_actuals := nvl(l_pos_actuals,0) + nvl(l_budget_pos_actuals,0);
600 l_pos_cmmtmnt := nvl(l_pos_cmmtmnt,0) + nvl(l_budget_pos_cmmtmnt,0);
601 l_pos_total_amt := nvl(l_pos_total_amt,0) + nvl(l_budget_pos_total_amt,0);
602 --
603 end loop;
604 --
605 p_actual_amount := trunc(l_pos_actuals,2);
606 p_commitment_amount := trunc(l_pos_cmmtmnt,2);
607 p_total_amount := trunc(l_pos_total_amt,2);
608 --
609 --exception section added as part of nocopy changes
610 exception
611 when others then
612 p_actual_amount := Null;
613 p_commitment_amount := Null;
614 p_total_amount := Null;
615 Raise;
616 --
617 end;
618 --
619 FUNCTION fyi_notification (p_transaction_id in number) RETURN varchar2
620 is
621 l_document varchar2(4000);
622 l_proc varchar2(61) := 'fyi_notification' ;
623 l_position_name varchar2(1000);
624 BEGIN
625 hr_utility.set_location('inside fyi notification'||l_proc,10);
626 fnd_message.set_name('PQH','PQH_FYI_UNDER_BDGT_POS');
627 fnd_message.set_token('POSITION',l_position_name);
628 l_document := fnd_message.get;
629 return l_document;
630 END fyi_notification;
631 --
632 --
633 --
634 procedure org_pos_temp(p_organization_id number
635 ,p_level1 number
636 ,p_batch_name varchar2
637 ,p_unit_of_measure varchar2
638 ,p_business_group_id number
639 ,p_effective_date date
640 ,p_start_date date
641 ,p_end_date date
642 ) is
643 l_proc_name varchar2(61) := 'org_pos_analysis' ;
644 l_org_name hr_all_organization_units.name%type;
645 --
646 l_parameter1_value varchar2(100);
647 l_parameter2_value varchar2(100);
648 l_parameter3_value varchar2(100);
649 l_parameter4_value varchar2(100);
650 l_parameter5_value varchar2(100);
651 l_parameter6_value varchar2(100);
652 l_parameter7_value varchar2(100);
653 l_parameter8_value varchar2(100);
654 l_parameter9_value varchar2(100);
655 --
656 -- Cursor to fetch Organization name
657 cursor c_org_name(p_org_id number) is
658 select name
659 from hr_all_organization_units u
660 where organization_id = p_org_id;
661 -- Cursor to fetch the table_route_id of the table_alias
662 cursor c_table_route(p_table_alias varchar2) is
663 SELECT
664 table_route_id
665 from
666 pqh_table_route
667 where
668 table_alias = p_table_alias;
669 --
670 -- Cursor to fetch Positions for the given Organization
671 --
672 -- PMFLETCH Now selects from MLS view
673 cursor c_positions(p_organization_id number, p_effective_date date) is
674 select pos.position_id, pos.job_id, name
675 from hr_all_positions_f_vl pos
676 where pos.organization_id = p_organization_id
677 and p_effective_date between pos.effective_start_date and pos.effective_end_date
678 order by position_id;
679 --
680 -- Cursor to select user name
681 --
682 cursor c_user_name(p_position_id number) is
683 select user_name
684 from fnd_user
685 where employee_id =
686 (select psf.supervisor_id
687 from hr_all_positions_f psf
688 where psf.position_id = p_position_id
689 and p_effective_date >= psf.effective_start_date
690 and p_effective_date <= psf.effective_end_date
691 );
692 --
693 -- Cursor to select workflow sequence no
694 --
695 cursor c_wf_seq_no is
696 select pqh_wf_notifications_s.nextval
697 from dual;
698 --
699 -- Local Variables
700 --
701 l_org_structure_version_id number;
702 --
703 l_budgeted_val number;
704 l_reallocation_val number;
705 l_actual_val number;
706 l_commitment_val number;
707 l_actual_commitment_val number;
708 l_under_budget_val number;
709 l_budgeted_fte_date date;
710 --
711 l_actuals_status number;
712 l_batch_id number;
713 l_table_route_id number;
714 --
715 l_transaction_category_id number;
716 l_workflow_seq_no number;
717 l_user_name varchar2(30);
718 l_apply_error_mesg varchar2(100);
719 l_apply_error_num varchar2(100);
720 --
721 l_message_type_cd varchar2(10);
722 l_message_type varchar2(100);
723 l_message varchar2(1000);
724 l_under_bgt_date varchar2(100);
725 --
726 l_currency_code varchar2(40);
727 --
728 begin
729 hr_utility.set_location('organization : '||p_organization_id ||' '|| l_proc_name, 101);
730 --
731 --
732 open c_org_name(p_organization_id);
733 fetch c_org_name into l_org_name;
734 close c_org_name;
735 --
736 -- Fetch table route Id for the Organization table(ORU)
737 --
738 open c_table_route('ORU');
739 fetch c_table_route into l_table_route_id;
740 -- Set table route id to null if the table route is not defined for ORU
741 if c_table_route%notfound then
742 l_table_route_id := null;
743 end if;
744 --
745 close c_table_route;
746
747 hr_utility.set_location('l_table_route_id : '||l_table_route_id ||' '|| l_proc_name, 102);
748 --
749 -- Check for the type of the cofigurable message(PQH_UNDER_BGT_POSITIONS)
750 --
751 pqh_utility.set_message(8302,'PQH_UNDER_BGT_POSITIONS', p_organization_id);
752 --
753 pqh_utility.set_message_token('UOM',
754 hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE',p_unit_of_measure));
755 pqh_utility.set_message_token('ENTITY',
756 hr_general.decode_lookup('PQH_BUDGET_ENTITY','POSITION'));
757
758 hr_utility.set_location('after pqh_utility.set_message : '|| l_proc_name, 103);
759 --
760 l_message_type_cd := pqh_utility.get_message_type_cd;
761 l_message := pqh_utility.get_message;
762 --
763 hr_utility.set_location('after pqh_utility.get_message : '||l_message_type_cd|| l_proc_name, 104);
764 --
765 if l_message_type_cd in ('E','W') then
766 if l_message_type_cd = 'E' then
767 l_message_type := 'ERROR';
768 else
769 l_message_type := 'WARNING';
770 end if;
771 hr_utility.set_location('before pqh_process_batch_log.set_context_level : '||
772 l_message_type_cd||l_proc_name, 105);
773 hr_utility.set_location('before pqh.set_context_level organization_id: '||
774 p_organization_id, 105);
775 hr_utility.set_location('before pqh.set_context_level l_table_route_id: '||
776 l_table_route_id, 105);
777 hr_utility.set_location('l_orglevel1: '||
778 p_level1, 105);
779 hr_utility.set_location('org name: '||
780 hr_general.decode_organization(p_organization_id), 105);
781 --
782 -- Set the Process Log Context level for the Organization
783 --
784 pqh_process_batch_log.set_context_level
785 (
786 p_txn_id =>p_organization_id,
787 p_txn_table_route_id =>l_table_route_id,
788 p_level =>p_level1 + 1,
789 p_log_context =>hr_general.decode_organization(p_organization_id)
790 );
791 --
792 hr_utility.set_location('Organization : '||p_organization_id
793 || ' ' ||l_proc_name, 110);
794 --
795 fnd_file.put_line(FND_FILE.LOG,'Primary Entity => ''POSITION''' || ' Unit of Measure => '||p_unit_of_measure);
796 fnd_file.put_line(FND_FILE.LOG,' ');
797 fnd_file.put_line(FND_FILE.LOG,'Organization => '|| l_org_name);
798 fnd_file.put_line(FND_FILE.LOG,' ');
799 fnd_file.put_line(FND_FILE.LOG,'Name Budgeted Value Reallocated Value Actual Value Commitment Value Under Budgeted Value Under Budgeted Date');
800 fnd_file.put_line(FND_FILE.LOG,' ');
801
802 --
803 -- Fetch Positions for the organization
804 --
805 for l_position in c_positions(p_organization_id, p_effective_date)
806 -- Analyse the Position Budgeted Salary
807 loop
808
809 hr_utility.set_location('l_position_id : '||l_position.position_id
810 || ' - ' || substr(l_position.name,1,40) , 110);
811
812 if p_unit_of_measure = 'MONEY' then
813 --
814 -- Get the Budgeted Salary of the Position for the given start date and end date
815 --
816 l_budgeted_val := pqh_budgeted_salary_pkg.get_pc_budgeted_salary(
817 P_POSITION_ID => l_position.position_id
818 ,p_budget_entity => 'POSITION'
819 ,p_start_date => p_start_date
820 ,p_end_date => p_end_date
821 ,p_effective_date => p_effective_date
822 ,p_business_group_id => p_business_group_id
823 );
824 hr_utility.set_location('After get_pc_budgeted_salary', 120);
825 --
826 -- Get the Reallocation amount(Money) of the Position between the given start date and end date
827 --
828 l_reallocation_val := pqh_reallocation_pkg.get_reallocation(
829 P_POSITION_ID => l_position.position_id
830 ,p_start_date => p_start_date
831 ,p_end_date => p_end_date
832 ,p_effective_date => p_effective_date
833 ,p_budget_entity => 'POSITION'
834 ,p_system_budget_unit => 'MONEY'
835 ,p_business_group_id => p_business_group_id
836 );
837 hr_utility.set_location('After get_reallocation', 130);
838
839 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt(
840 p_position_id => l_position.position_id
841 , p_start_date => p_start_date
842 , p_end_date => p_end_date
843 , p_effective_date => p_effective_date
844 , p_budget_entity => 'POSITION'
845 , p_actual_value => l_actual_val
846 , p_commt_value => l_commitment_val
847 -- p_total_amount => l_actual_commitment_sal
848 , p_unit_of_measure => 'MONEY'
849 , p_business_group_id=> p_business_group_id
850 );
851 --
852 l_currency_code := get_budget_currency(
853 P_POSITION_ID => l_position.position_id
854 ,p_budget_entity => 'POSITION'
855 ,p_start_date => p_start_date
856 ,p_end_date => p_end_date
857 ,p_effective_date => p_effective_date
858 ,p_business_group_id => p_business_group_id
859 );
860 --
861 -- Print the details of the Position
862 --
863 hr_utility.set_location('Position : '||l_position.position_id
864 ||' '||l_proc_name, 130);
865 hr_utility.set_location('Budgeted Salary : '||l_budgeted_val
866 ||' '||l_proc_name, 140);
867 hr_utility.set_location('Budget Reallocation : '||nvl(l_reallocation_val,0)
868 ||' '||l_proc_name, 140);
869 hr_utility.set_location('Actual Salary : '||nvl(l_actual_val,0)
870 ||' '||l_proc_name, 150);
871 hr_utility.set_location('Commitment Salary : '||nvl(l_commitment_val,0)
872 ||' '||l_proc_name, 160);
873 hr_utility.set_location('Actual + Commitment Salary : '||nvl(l_actual_commitment_val,0)
874 ||' '||l_proc_name, 160);
875 if l_budgeted_val is not null then
876 --
877 -- Check, whether the Position is Under Budgeted
878 --
879 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0) < nvl(l_actual_val,0) + nvl(l_commitment_val,0)) then
880 --
881 -- If Under Budgeted
882 --
883 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - (nvl(l_actual_val,0) + nvl(l_commitment_val,0));
884 --
885 -- Fetch table route Id for the Position table(PSF)
886 --
887 open c_table_route('PSF');
888 fetch c_table_route into l_table_route_id;
889 -- Set table route id to null if the table route is not defined for PSF
890 if c_table_route%notfound then
891 l_table_route_id := null;
892 end if;
893 --
894 close c_table_route;
895 --
896 l_user_name := null;
897 --
898 open c_user_name(l_position.position_id);
899 fetch c_user_name into l_user_name;
900 close c_user_name;
901 --
902 --
903 fnd_file.put_line(FND_FILE.LOG,l_position.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||l_commitment_val||' '||l_under_budget_val||' ');
904 --
905 -- Set the Process Log Context Level for the Position
906 --
907 pqh_process_batch_log.set_context_level
908 (
909 p_txn_id =>l_position.position_id,
910 p_txn_table_route_id =>l_table_route_id,
911 p_level =>p_level1+2,
912 p_log_context =>hr_general.decode_position_latest_name(l_position.position_id)
913 );
914
915 --
916 -- Insert the Log for the position
917 --
918 pqh_process_batch_log.insert_log
919 (
920 p_message_type_cd =>l_message_type,
921 p_message_text =>l_message,
922 p_information3 =>round(l_budgeted_val,2),
923 p_information4 =>round(l_reallocation_val,2),
924 p_information5 =>round(l_actual_val,2),
925 p_information6 =>round(l_commitment_val,2),
926 p_information7 =>l_user_name,
927 p_information8 =>'POSITION',
928 p_information9 =>p_unit_of_measure,
929 p_information10 =>l_under_bgt_date,
930 p_information11 =>p_batch_name,
931 p_information12 =>'REPORT',
932 p_information13 =>l_currency_code
933
934 );
935
936 --
937 -- Fetch the FYI Notification Info
938 --
939 l_transaction_category_id :=
940 pqh_workflow.get_txn_cat('POSITION_TRANSACTION', p_business_group_id);
941 --
942 open c_wf_seq_no;
943 fetch c_wf_seq_no into l_workflow_seq_no;
944 close c_wf_seq_no;
945 --
946 hr_utility.set_location('l_position.position_id : '|| l_position.position_id, 1111);
947 hr_utility.set_location('l_user_name : '|| l_user_name, 1111);
948 --
949 if l_user_name is not null then
950 --
951 hr_utility.set_location('l_user_name : '|| l_user_name, 1112);
952 --
953 l_parameter1_value :=
954 hr_general.decode_position_latest_name(l_position.position_id);
955 l_parameter2_value := p_batch_name;
956 l_parameter3_value := p_effective_date;
957 l_parameter4_value :=
958 hr_general.decode_organization(p_organization_id);
959 l_parameter5_value :=
960 hr_general.decode_job(l_position.job_id);
961 l_parameter6_value := l_budgeted_val;
962 l_parameter7_value := l_reallocation_val;
963 l_parameter8_value := l_actual_val;
964 l_parameter9_value := l_commitment_val;
965 --
966 -- FYI Notifications call
967 --
968 PQH_WF.process_user_action(
969 p_transaction_category_id => l_transaction_category_id
970 , p_transaction_id => l_position.position_id
971 , p_workflow_seq_no => l_workflow_seq_no
972 , p_user_action_cd => 'FYI_NOT'
973 , p_route_to_user => l_user_name
974 , p_parameter1_value => l_parameter1_value
975 , p_parameter2_value => l_parameter2_value
976 , p_parameter3_value => l_parameter3_value
977 , p_parameter4_value => l_parameter4_value
978 , p_parameter5_value => l_parameter5_value
979 , p_parameter6_value => l_parameter6_value
980 , p_parameter7_value => l_parameter7_value
981 , p_parameter8_value => l_parameter8_value
982 , p_parameter9_value => l_parameter9_value
983 , p_apply_error_mesg => l_apply_error_mesg
984 , p_apply_error_num => l_apply_error_num
985 );
986 end if;
987 --
988 hr_utility.set_location(l_position.position_id || ' is under budgeted'
989 ||' '||l_proc_name, 170);
990 --
991 end if;--for under budget
992 end if; -- 14/05/02
993
994 elsif p_unit_of_measure = 'HOURS' then
995 --
996 -- Get the Budgeted hours of the Position for the given start date and end date
997 --
998 l_budgeted_val := pqh_budgeted_salary_pkg.get_budgeted_hours
999 (P_POSITION_ID => l_position.position_id
1000 ,p_start_date => p_start_date
1001 ,p_end_date => p_end_date
1002 ,p_effective_date => p_effective_date
1003 ,p_budget_entity => 'POSITION'
1004 ,p_business_group_id => p_business_group_id
1005 );
1006
1007 --
1008 -- Get the Reallocation hours of the Position between the given start date and end date
1009 --
1010 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
1011 (P_POSITION_ID => l_position.position_id
1012 ,p_start_date => p_start_date
1013 ,p_end_date => p_end_date
1014 ,p_effective_date => p_effective_date
1015 ,p_budget_entity => 'POSITION'
1016 ,p_system_budget_unit => 'HOURS'
1017 ,p_business_group_id => p_business_group_id
1018 );
1019 --
1020 --Get the Actual hours of the position between the given start date and end date
1021 --
1022 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1023 ( p_position_id => l_position.position_id
1024 ,p_start_date => p_start_date
1025 ,p_end_date => p_end_date
1026 ,p_effective_date => p_effective_date
1027 ,p_budget_entity => 'POSITION'
1028 ,p_unit_of_measure => 'HOURS'
1029 ,p_business_group_id => p_business_group_id
1030 ,p_actual_value => l_actual_val
1031 ,p_commt_value => l_commitment_val
1032 );
1033 --
1034
1035 --
1036 -- Print the details of the Position
1037 --
1038 hr_utility.set_location('Position : '||l_position.position_id
1039 ||' '||l_proc_name, 130);
1040 hr_utility.set_location('Budgeted Hours : '||l_budgeted_val
1041 ||' '||l_proc_name, 140);
1042 hr_utility.set_location('Reallocation Hours: '||nvl(l_reallocation_val,0)
1043 ||' '||l_proc_name, 140);
1044 hr_utility.set_location('Actual Hours : '||nvl(l_actual_val,0)
1045 ||' '||l_proc_name, 150);
1046
1047 if l_budgeted_val is not null then
1048 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
1049
1050 --
1051 -- If Under Budgeted
1052 --
1053 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
1054 --
1055 -- Fetch table route Id for the Position table(PSF)
1056 --
1057 open c_table_route('PSF');
1058 fetch c_table_route into l_table_route_id;
1059 -- Set table route id to null if the table route is not defined for PSF
1060 if c_table_route%notfound then
1061 l_table_route_id := null;
1062 end if;
1063 --
1064 close c_table_route;
1065 --
1066 l_user_name := null;
1067 --
1068 open c_user_name(l_position.position_id);
1069 fetch c_user_name into l_user_name;
1070 close c_user_name;
1071 --
1072 --
1073 fnd_file.put_line(FND_FILE.LOG,l_position.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' ');
1074 --
1075 -- Set the Process Log Context Level for the Position
1076 --
1077 pqh_process_batch_log.set_context_level
1078 (
1079 p_txn_id =>l_position.position_id,
1080 p_txn_table_route_id =>l_table_route_id,
1081 p_level =>p_level1+2,
1082 p_log_context =>hr_general.decode_position_latest_name(l_position.position_id)
1083 );
1084
1085 --
1086 -- Insert the Log for the position
1087 --
1088 pqh_process_batch_log.insert_log
1089 (
1090 p_message_type_cd =>l_message_type,
1091 p_message_text =>l_message,
1092 p_information3 =>round(l_budgeted_val,2),
1093 p_information4 =>round(l_reallocation_val,2),
1094 p_information5 =>round(l_actual_val,2),
1095 p_information7 =>l_user_name,
1096 p_information8 =>'POSITION',
1097 p_information9 =>p_unit_of_measure,
1098 p_information10 =>l_under_bgt_date,
1099 p_information11 =>p_batch_name,
1100 p_information12 =>'REPORT'
1101 );
1102
1103 --
1104 -- Fetch the FYI Notification Info
1105 --
1106 l_transaction_category_id :=
1107 pqh_workflow.get_txn_cat('POSITION_TRANSACTION', p_business_group_id);
1108 --
1109 open c_wf_seq_no;
1110 fetch c_wf_seq_no into l_workflow_seq_no;
1111 close c_wf_seq_no;
1112 --
1113 hr_utility.set_location('l_position.position_id : '|| l_position.position_id, 1111);
1114 hr_utility.set_location('l_user_name : '|| l_user_name, 1111);
1115 --
1116 if l_user_name is not null then
1117 --
1118 hr_utility.set_location('l_user_name : '|| l_user_name, 1112);
1119 --
1120 l_parameter1_value :=
1121 hr_general.decode_position_latest_name(l_position.position_id);
1122 l_parameter2_value := p_batch_name;
1123 l_parameter3_value := p_effective_date;
1124 l_parameter4_value :=
1125 hr_general.decode_organization(p_organization_id);
1126 l_parameter5_value :=
1127 hr_general.decode_job(l_position.job_id);
1128 l_parameter6_value := l_budgeted_val;
1129 l_parameter7_value := l_reallocation_val;
1130 l_parameter8_value := l_actual_val;
1131 --
1132 -- FYI Notifications call
1133 --
1134 PQH_WF.process_user_action(
1135 p_transaction_category_id => l_transaction_category_id
1136 ,p_transaction_id => l_position.position_id
1137 ,p_workflow_seq_no => l_workflow_seq_no
1138 ,p_user_action_cd => 'FYI_NOT'
1139 ,p_route_to_user => l_user_name
1140 ,p_parameter1_value => l_parameter1_value
1141 ,p_parameter2_value => l_parameter2_value
1142 ,p_parameter3_value => l_parameter3_value
1143 ,p_parameter4_value => l_parameter4_value
1144 ,p_parameter5_value => l_parameter5_value
1145 ,p_parameter6_value => l_parameter6_value
1146 ,p_parameter7_value => l_parameter7_value
1147 ,p_parameter8_value => l_parameter8_value
1148 ,p_apply_error_mesg => l_apply_error_mesg
1149 ,p_apply_error_num => l_apply_error_num
1150 );
1151 end if;
1152 --
1153 hr_utility.set_location(l_position.position_id || ' is under budgeted'
1154 ||' '||l_proc_name, 170);
1155 --
1156
1157 end if;
1158 end if; --14/05/02
1159
1160 else -- p_unit_of_measure is 'FTE' or 'Headcount' etc
1161
1162 --
1163 -- Get the Budgeted FTE or Headcount of the Position for the given start date and end date
1164 --
1165 l_budgeted_val := pqh_psf_bus.get_budgeted_fte
1166 (p_position_id => l_position.position_id
1167 ,p_start_date => p_start_date
1168 ,p_end_date => p_end_date
1169 ,p_budget_entity => 'POSITION'
1170 ,p_unit_of_measure => p_unit_of_measure
1171 ,p_business_group_id => p_business_group_id
1172 ,p_budgeted_fte_date => l_budgeted_fte_date
1173 );
1174
1175 --
1176 -- Get the Reallocation FTE or Headcount of the Position between the given start date and end date
1177 --
1178 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
1179 (P_POSITION_ID => l_position.position_id
1180 ,p_start_date => p_start_date
1181 ,p_end_date => p_end_date
1182 ,p_effective_date => p_effective_date
1183 ,p_budget_entity => 'POSITION'
1184 ,p_system_budget_unit => p_unit_of_measure
1185 ,p_business_group_id => p_business_group_id
1186 );
1187 --
1188 --Get the Actual FTE or Headcount of the position between the given start date and end date
1189 --
1190 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1191 ( p_position_id => l_position.position_id
1192 ,p_start_date => p_start_date
1193 ,p_end_date => p_end_date
1194 ,p_effective_date => p_effective_date
1195 ,p_budget_entity => 'POSITION'
1196 ,p_unit_of_measure => p_unit_of_measure
1197 ,p_business_group_id => p_business_group_id
1198 ,p_actual_value => l_actual_val
1199 ,p_commt_value => l_commitment_val
1200 );
1201
1202 --
1203 -- Print the details of the Position
1204 --
1205 hr_utility.set_location('Position : '||l_position.position_id
1206 ||' '||l_proc_name, 130);
1207 hr_utility.set_location('Unit of measure : '||p_unit_of_measure
1208 ||' '||l_proc_name, 130);
1209 hr_utility.set_location('Budgeted : '||l_budgeted_val
1210 ||' '||l_proc_name, 140);
1211 hr_utility.set_location('Reallocation: '||nvl(l_reallocation_val,0)
1212 ||' '||l_proc_name, 140);
1213 hr_utility.set_location('Actual : '||nvl(l_actual_val,0)
1214 ||' '||l_proc_name, 150);
1215
1216 if l_budgeted_val is not null then
1217 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
1218
1219 --
1220 -- If Under Budgeted
1221 --
1222 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
1223 --
1224 -- Fetch table route Id for the Position table(PSF)
1225 --
1226 --
1227 open c_table_route('PSF');
1228 fetch c_table_route into l_table_route_id;
1229 -- Set table route id to null if the table route is not defined for PSF
1230 if c_table_route%notfound then
1231 l_table_route_id := null;
1232 end if;
1233 --
1234 close c_table_route;
1235 --
1236 l_user_name := null;
1237 --
1238 open c_user_name(l_position.position_id);
1239 fetch c_user_name into l_user_name;
1240 close c_user_name;
1241 --
1242 --
1243 fnd_file.put_line(FND_FILE.LOG,l_position.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' '||l_budgeted_fte_date);
1244 --
1245 -- Set the Process Log Context Level for the Position
1246 --
1247 pqh_process_batch_log.set_context_level
1248 (
1249 p_txn_id =>l_position.position_id,
1250 p_txn_table_route_id =>l_table_route_id,
1251 p_level =>p_level1+2,
1252 p_log_context =>hr_general.decode_position_latest_name(l_position.position_id)
1253 );
1254
1255 --
1256 -- Insert the Log for the position
1257 --
1258 pqh_process_batch_log.insert_log
1259 (
1260 p_message_type_cd =>l_message_type,
1261 p_message_text =>l_message,
1262 p_information3 =>round(l_budgeted_val,2),
1263 p_information4 =>round(l_reallocation_val,2),
1264 p_information5 =>round(l_actual_val,2),
1265 p_information7 =>l_user_name,
1266 p_information8 =>'POSITION',
1267 p_information9 =>p_unit_of_measure,
1268 p_information10 =>to_char(l_budgeted_fte_date,'YYYY/MM/DD'),
1269 p_information11 =>p_batch_name,
1270 p_information12 =>'REPORT'
1271 );
1272
1273 --
1274 -- Fetch the FYI Notification Info
1275 --
1276 l_transaction_category_id :=
1277 pqh_workflow.get_txn_cat('POSITION_TRANSACTION', p_business_group_id);
1278 --
1279 open c_wf_seq_no;
1280 fetch c_wf_seq_no into l_workflow_seq_no;
1281 close c_wf_seq_no;
1282 --
1283 hr_utility.set_location('l_position.position_id : '|| l_position.position_id, 1111);
1284 hr_utility.set_location('l_user_name : '|| l_user_name, 1111);
1285 --
1286 if l_user_name is not null then
1287 --
1288 hr_utility.set_location('l_user_name : '|| l_user_name, 1112);
1289 --
1290 l_parameter1_value :=
1291 hr_general.decode_position_latest_name(l_position.position_id);
1292 l_parameter2_value := p_batch_name;
1293 l_parameter3_value := p_effective_date;
1294 l_parameter4_value :=
1295 hr_general.decode_organization(p_organization_id);
1296 l_parameter5_value :=
1297 hr_general.decode_job(l_position.job_id);
1298 l_parameter6_value := l_budgeted_val;
1299 l_parameter7_value := l_reallocation_val;
1300 l_parameter8_value := l_actual_val;
1301 --
1302 -- FYI Notifications call
1303 --
1304 PQH_WF.process_user_action(
1305 p_transaction_category_id => l_transaction_category_id
1306 , p_transaction_id => l_position.position_id
1307 , p_workflow_seq_no => l_workflow_seq_no
1308 , p_user_action_cd => 'FYI_NOT'
1309 , p_route_to_user => l_user_name
1310 , p_parameter1_value => l_parameter1_value
1311 , p_parameter2_value => l_parameter2_value
1312 , p_parameter3_value => l_parameter3_value
1313 , p_parameter4_value => l_parameter4_value
1314 , p_parameter5_value => l_parameter5_value
1315 , p_parameter6_value => l_parameter6_value
1316 , p_parameter7_value => l_parameter7_value
1317 , p_parameter8_value => l_parameter8_value
1318 , p_apply_error_mesg => l_apply_error_mesg
1319 , p_apply_error_num => l_apply_error_num
1320 );
1321 end if;
1322 --
1323 hr_utility.set_location(l_position.position_id || ' is under budgeted'
1324 ||' '||l_proc_name, 170);
1325 --
1326 end if;
1327 end if; --14/05/02
1328
1329 end if;--for uom
1330 --
1331 end loop;
1332 --
1333 end if;
1334 --
1335 end;
1336 --
1337 --
1338 --POSITIONS
1339 --To calculate the Under budgeted positions for all units of measure...
1340 --
1341 Procedure position_analysis(
1342 errbuf out nocopy varchar2
1343 , retcode out nocopy varchar2
1344 , p_batch_name varchar2
1345 , p_effective_date varchar2
1346 , p_start_org_id number
1347 , p_org_structure_id number
1348 , p_start_date varchar2
1349 , p_end_date varchar2
1350 , p_unit_of_measure varchar2
1351 , p_business_group_id number
1352 ) is
1353 --
1354 l_proc_name varchar2(30) := 'POSITION_ANALYSIS';
1355 --
1356 l_dummy varchar2(50);
1357 l_effective_date date;
1358 l_start_date date;
1359 l_end_date date;
1360 l_start_org_id number;
1361 --
1362 -- Cursor to Fetch the Organization Structure Version
1363 --
1364 cursor c_org_version(p_effective_date date) is
1365 select ver.org_structure_version_id
1366 from per_organization_structures str
1367 , per_org_structure_versions ver
1368 where str.position_control_structure_flg = 'Y'
1369 and str.organization_structure_id = p_org_structure_id
1370 and str.business_group_id = p_business_group_id
1371 and ver.business_group_id = p_business_group_id
1372 and str.organization_structure_id = ver.organization_structure_id
1373 and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
1374 --
1375 -- Cursor to fetch the top Org of Hierarchy
1376 --
1377 cursor c_top_org(p_org_structure_version_id number) is
1378 select organization_id_parent organization_id
1379 from per_org_structure_elements a
1380 where org_structure_version_id = p_org_structure_version_id
1381 and not exists (
1382 select organization_id_child organization_id
1383 from per_org_structure_elements b
1384 where org_structure_version_id = p_org_structure_version_id
1385 and b.organization_id_child = a.organization_id_parent
1386 )
1387 and rownum <2;
1388
1389 --
1390 -- Cursor to Fetch the Organizations for the given Organization Hierarchy
1391 --
1392 -- Bug Fix : 2464692 Change : Cursor exteded with new parameter p_effective_date
1393 --
1394 cursor c_org(p_org_structure_version_id number, p_start_org_id number,p_effective_date date) is
1395 select 0 rn,
1396 0 level1,
1397 organization_id
1398 from hr_all_organization_units u
1399 where organization_id = p_start_org_id
1400 and business_group_id = p_business_group_id
1401 and exists
1402 (select null from per_org_structure_elements e
1403 where e.org_structure_version_id = p_org_structure_version_id
1404 and (e.organization_id_child = p_start_org_id
1405 or e.organization_id_parent = p_start_org_id ) )
1406 union
1407 select rownum rn,
1408 level level1,
1409 organization_id_child organization_id
1410 from per_org_structure_elements a
1411 start with
1412 organization_id_parent = p_start_org_id
1413 and org_structure_version_id = p_org_structure_version_id
1414 connect by
1415 organization_id_parent = prior organization_id_child
1416 and org_structure_version_id = p_org_structure_version_id;
1417 --
1418 --
1419 -- Bug Fix : 2464692
1420 -- Retrives all Internal Organizations under the given business group
1421 -- as on that effective date in case of p_start_org_id and
1422 -- p_org_structure_id are null
1423 --
1424 cursor c_all_org(p_business_group_id number, p_effective_date date) is
1425 select rownum rn,
1426 0 level1,
1427 organization_id
1428 from hr_all_organization_units
1429 where business_group_id = p_business_group_id
1430 and INTERNAL_EXTERNAL_FLAG ='INT'
1431 and p_effective_date between date_from and nvl(date_to, hr_general.end_of_time);
1432 --
1433 -- Cursor that checks the batch existance
1434 --
1435 cursor check_batch_name(p_batch_name varchar2) is
1436 select 'x'
1437 from pqh_process_log
1438 where log_context=p_batch_name;
1439 --
1440 -- Cursor to get the next batch Id for the Process Log
1441 --
1442 cursor c_batch is
1443 select pqh_process_log_s.nextval
1444 from dual;
1445 --
1446 -- Local Variables
1447 --
1448 l_org_structure_version_id number;
1449 --
1450 l_batch_id number;
1451 --
1452 begin
1453 --
1454 hr_utility.set_location('Entering'|| l_proc_name, 10);
1455 retcode := 0;
1456 --
1457 hr_utility.set_location('Entering'|| l_proc_name, 11);
1458 --
1459 open check_batch_name(p_batch_name);
1460 fetch check_batch_name into l_dummy;
1461 if check_batch_name%found then
1462 retcode := -1;
1463 fnd_message.set_name('PQH', 'PQH_PLG_DUP_BATCH');
1464 fnd_message.set_token('BATCH_NAME', p_batch_name);
1465 errbuf := fnd_message.get;
1466 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
1468 return;
1469 end if;
1470 --
1471 hr_utility.set_location('Entering'|| l_proc_name, 13);
1472 --
1473 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1474 l_start_date := fnd_date.canonical_to_date(p_start_date);
1475 l_end_date := fnd_date.canonical_to_date(p_end_date);
1476 --
1477 -- Fetch the Organization Structure Version
1478 --
1479 open c_org_version(l_effective_date);
1480 fetch c_org_version into l_org_structure_version_id;
1481 close c_org_version;
1482 --
1483 --
1484 hr_utility.set_location('Entering'|| l_proc_name, 14);
1485 --
1486 --
1487 -- Fetch the batch Id into the l_batch_id
1488 --
1489 open c_batch;
1490 fetch c_batch into l_batch_id;
1491 close c_batch;
1492 --
1493 hr_utility.set_location('l_batch_id : '||l_batch_id ||' - ' || l_proc_name, 15);
1494 --
1495 -- Create the start record into the Process Log
1496 --
1497 pqh_process_batch_log.start_log
1498 (
1499 p_batch_id =>l_batch_id,
1500 p_module_cd =>'POSITION_BUDGET_ANALYSIS',
1501 p_log_context =>p_batch_name,
1502 p_information3 =>p_effective_date,
1503 p_information4 =>p_start_org_id,
1504 p_information5 =>p_start_date,
1505 p_information6 =>p_end_date,
1506 p_information7 =>p_org_structure_id,
1507 p_information8 =>'POSITION',
1508 p_information9 =>p_unit_of_measure
1509 );
1510 --
1511 --
1512 hr_utility.set_location('Organization Structure Version : '||l_org_structure_version_id ||' '|| l_proc_name, 100);
1513 hr_utility.set_location('start organization : '||p_start_org_id ||' '|| l_proc_name, 100);
1514
1515 if (l_org_structure_version_id is not null ) then
1516 l_start_org_id := p_start_org_id;
1517 -- Bug Fix :2481824 ,get Top Org in Hierarchy as p_start_org_id
1518 if (p_start_org_id is null) then
1519 open c_top_org(l_org_structure_version_id);
1520 fetch c_top_org into l_start_org_id;
1521 close c_top_org;
1522 end if;
1523 --
1524 if l_start_org_id is not null then
1525 --
1526 -- Fetch the Organizations from the Organization Hierarchy
1527 for l_organization in c_org(l_org_structure_version_id, l_start_org_id,l_effective_date)
1528 -- Analyse the Positions of each Organization
1529 loop
1530 --
1531 hr_utility.set_location('organization : '||l_organization.organization_id ||' '|| l_proc_name, 101);
1532 --
1533 org_pos_temp(p_organization_id => l_organization.organization_id
1534 ,p_level1 => l_organization.level1
1535 ,p_batch_name => p_batch_name
1536 ,p_unit_of_measure => p_unit_of_measure
1537 ,p_business_group_id=> p_business_group_id
1538 ,p_effective_date => l_effective_date
1539 ,p_start_date => l_start_date
1540 ,p_end_date => l_end_date
1541 );
1542 --
1543 end loop;
1544 end if;
1545 elsif (p_start_org_id is null)
1546 then
1547 for l_organization in c_all_org(p_business_group_id, l_effective_date)
1548 -- Analyse the Positions of each Organization
1549 loop
1550 --
1551 hr_utility.set_location('organization : '||l_organization.organization_id ||' '|| l_proc_name, 101);
1552 --
1553 org_pos_temp(p_organization_id => l_organization.organization_id
1554 ,p_level1 => l_organization.level1
1555 ,p_batch_name => p_batch_name
1556 ,p_unit_of_measure => p_unit_of_measure
1557 ,p_business_group_id=> p_business_group_id
1558 ,p_effective_date => l_effective_date
1559 ,p_start_date => l_start_date
1560 ,p_end_date => l_end_date
1561 );
1562 --
1563 end loop;
1564 end if;
1565 --
1566 -- End the Process Log
1567 --
1568 pqh_process_batch_log.end_log;
1569 hr_utility.set_location('End Process'
1570 ||' '||l_proc_name, 180);
1571 commit;
1572 exception
1573 when others then
1574 retcode := -1;
1575 --
1576 end;
1577
1578
1579 ---*******************-----
1580 --JOBS
1581 --To calculate the Under budgeted jobs for all units of measure...
1582 --
1583 Procedure job_analysis(
1584 errbuf out nocopy varchar2
1585 , retcode out nocopy varchar2
1586 , p_batch_name varchar2
1587 , p_effective_date varchar2
1588 , p_start_date varchar2
1589 , p_end_date varchar2
1590 , p_unit_of_measure varchar2
1591 , p_business_group_id number
1592 ) is
1593 --
1594 l_proc_name varchar2(30) := 'JOB_ANALYSIS';
1595 --
1596 l_dummy varchar2(50);
1597 l_effective_date date;
1598 l_start_date date;
1599 l_end_date date;
1600 l_error_msg varchar2(1000);
1601 l_parameter1_value varchar2(100);
1602 l_parameter2_value varchar2(100);
1603 l_parameter3_value varchar2(100);
1604 l_parameter4_value varchar2(100);
1605 l_parameter5_value varchar2(100);
1606 l_parameter6_value varchar2(100);
1607 l_parameter7_value varchar2(100);
1608 l_parameter8_value varchar2(100);
1609 l_parameter9_value varchar2(100);
1610 --
1611 -- Cursor to fetch Jobs
1612 --
1613
1614 cursor c_jobs(p_effective_date date, p_start_date1 date, p_end_date1 date) is
1615 select distinct bdet.job_id, job.name
1616 from pqh_budgets bud,
1617 pqh_budget_versions bver,
1618 pqh_budget_details bdet,
1619 per_jobs_tl job
1620 where bud.business_group_id = p_business_group_id
1621 and bud.position_control_flag = 'Y'
1622 and bud.budgeted_entity_cd = 'JOB'
1623 and (p_start_date1 <= bud.budget_end_date
1624 and p_end_date1 >= bud.budget_start_date)
1625 and bver.budget_id = bud.budget_id
1626 and bver.budget_version_id = bdet.budget_version_id
1627 and bdet.job_id = job.job_id
1628 and job.language = userenv('LANG');
1629
1630 --
1631 -- Cursor that checks the batch existance
1632 --
1633 cursor check_batch_name(p_batch_name varchar2) is
1634 select 'x'
1635 from pqh_process_log
1636 where log_context=p_batch_name;
1637
1638 --
1639 -- Cursor to get the next batch Id for the Process Log
1640 --
1641 cursor c_batch is
1642 select
1643 pqh_process_log_s.nextval
1644 from
1645 dual;
1646
1647 --
1648 -- Cursor to fetch the table_route_id of the table_alias
1649 --
1650 cursor c_table_route(p_table_alias varchar2) is
1651 SELECT
1652 table_route_id
1653 from
1654 pqh_table_route
1655 where
1656 table_alias = p_table_alias;
1657
1658 --
1659 -- Local Variables
1660 --
1661 l_budgeted_val number;
1662 l_reallocation_val number;
1663 l_actual_val number;
1664 l_commitment_val number;
1665 l_actual_commitment_val number;
1666 l_under_budget_val number;
1667 l_budgeted_fte_date date;
1668 --
1669 l_actuals_status number;
1670 l_batch_id number;
1671 l_table_route_id number;
1672 --
1673 l_apply_error_mesg varchar2(100);
1674 l_apply_error_num varchar2(100);
1675 --
1676 l_message_type_cd varchar2(10);
1677 l_message_type varchar2(100);
1678 l_message varchar2(1000);
1679 l_under_bgt_date varchar2(100);
1680 --
1681 l_currency_code varchar2(40);
1682 --
1683 begin
1684 --
1685 hr_utility.set_location('Entering'|| l_proc_name, 10);
1686 retcode := 0;
1687 --
1688 hr_utility.set_location('Entering'|| l_proc_name, 11);
1689 --
1690 open check_batch_name(p_batch_name);
1691 fetch check_batch_name into l_dummy;
1692 if check_batch_name%found then
1693 retcode := -1;
1694 fnd_message.set_name('PQH', 'PQH_PLG_DUP_BATCH');
1695 fnd_message.set_token('BATCH_NAME', p_batch_name);
1696 errbuf := fnd_message.get;
1697 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1698 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
1699 return;
1700 end if;
1701 --
1702 hr_utility.set_location('Entering'|| l_proc_name, 13);
1703 --
1704 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1705 l_start_date := fnd_date.canonical_to_date(p_start_date);
1706 l_end_date := fnd_date.canonical_to_date(p_end_date);
1707 --
1708 -- Fetch the batch Id into the l_batch_id
1709 --
1710 open c_batch;
1711 fetch c_batch into l_batch_id;
1712 close c_batch;
1713 --
1714 hr_utility.set_location('l_batch_id : '||l_batch_id ||' - ' || l_proc_name, 15);
1715
1716 hr_utility.set_location('l_effective_date: '||to_char(l_effective_date), 155);
1717 hr_utility.set_location('l_start_date: '||to_char(l_start_date), 156);
1718 hr_utility.set_location('l_end_date: '||to_char(l_end_date), 157);
1719 --
1720 -- Create the start record into the Process Log
1721 --
1722 pqh_process_batch_log.start_log
1723 (
1724 p_batch_id =>l_batch_id,
1725 p_module_cd =>'POSITION_BUDGET_ANALYSIS',
1726 p_log_context =>p_batch_name,
1727 p_information3 =>p_effective_date,
1728 p_information5 =>p_start_date,
1729 p_information6 =>p_end_date,
1730 p_information8 =>'JOB',
1731 p_information9 =>p_unit_of_measure
1732 );
1733 --
1734 --
1735 -- Fetch table route Id for the Job table(JOB)
1736 --
1737 open c_table_route('JOB');
1738 fetch c_table_route into l_table_route_id;
1739 -- Set table route id to null if the table route is not defined for JOB
1740 if c_table_route%notfound then
1741 l_table_route_id := null;
1742 end if;
1743 --
1744 close c_table_route;
1745
1746 hr_utility.set_location('l_table_route_id : '||l_table_route_id ||' '|| l_proc_name, 102);
1747 --
1748 -- Check for the type of the cofigurable message(PQH_UNDER_BGT_POSITIONS)
1749 --
1750 pqh_utility.set_message(8302,'PQH_UNDER_BGT_POSITIONS', 200);
1751 --
1752 pqh_utility.set_message_token('UOM',
1753 hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE',p_unit_of_measure));
1754 pqh_utility.set_message_token('ENTITY',
1755 hr_general.decode_lookup('PQH_BUDGET_ENTITY','JOB'));
1756
1757 hr_utility.set_location('after pqh_utility.set_message : '|| l_proc_name, 103);
1758 --
1759 l_message_type_cd := pqh_utility.get_message_type_cd;
1760 l_message := pqh_utility.get_message;
1761 --
1762 hr_utility.set_location('after pqh_utility.get_message : '||l_message_type_cd|| l_proc_name, 104);
1763 --
1764 if l_message_type_cd in ('E','W') then
1765 if l_message_type_cd = 'E' then
1766 l_message_type := 'ERROR';
1767 else
1768 l_message_type := 'WARNING';
1769 end if;
1770 hr_utility.set_location('before pqh_process_batch_log.set_context_level : '||
1771 l_message_type_cd||l_proc_name, 105);
1772 hr_utility.set_location('before pqh.set_context_level l_table_route_id: '||
1773 l_table_route_id, 105);
1774 --
1775 -- Set the Process Log Context level....What should it be?
1776 --
1777 /*pqh_process_batch_log.set_context_level
1778 (
1779 p_txn_id =>l_organization.organization_id,
1780 p_txn_table_route_id =>l_table_route_id,
1781 p_level =>l_organization.level1 + 1,
1782 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
1783 ); */
1784 --
1785 -- Fetch Jobs
1786 --
1787 -- Print the output on concurrent log
1788 --
1789 fnd_file.put_line(FND_FILE.LOG,'Primary Entity => ''JOB''' || ' Unit of Measure => '||p_unit_of_measure);
1790 fnd_file.put_line(FND_FILE.LOG,' ');
1791 fnd_file.put_line(FND_FILE.LOG,'Name Budgeted Value Reallocated Value Actual Value Commitment Value Under Budgeted Value Under Budgeted Date');
1792 fnd_file.put_line(FND_FILE.LOG,' ');
1793
1794 for l_job in c_jobs(l_effective_date, l_start_date, l_end_date)
1795 loop
1796
1797 hr_utility.set_location('l_job_id : '||l_job.job_id , 110);
1798
1799 if p_unit_of_measure = 'MONEY' then
1800 --
1801 -- Get the Budgeted Salary of the Job for the given start date and end date
1802 --
1803 l_budgeted_val := pqh_budgeted_salary_pkg.get_pc_budgeted_salary(
1804 p_job_id => l_job.job_id
1805 ,p_start_date => l_start_date
1806 ,p_end_date => l_end_date
1807 ,p_effective_date => l_effective_date
1808 ,p_business_group_id => p_business_group_id
1809 ,p_budget_entity => 'JOB'
1810 );
1811 --
1812 -- Get the Reallocation amount(Money) of the Job between the given start date and end date
1813 --
1814 l_reallocation_val := pqh_reallocation_pkg.get_reallocation(
1815 p_job_id => l_job.job_id
1816 ,p_start_date => l_start_date
1817 ,p_end_date => l_end_date
1818 ,p_effective_date => l_effective_date
1819 ,p_budget_entity => 'JOB'
1820 ,p_system_budget_unit => 'MONEY'
1821 ,p_business_group_id => p_business_group_id
1822 );
1823
1824 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt(
1825 p_job_id => l_job.job_id
1826 ,p_start_date => l_start_date
1827 ,p_end_date => l_end_date
1828 ,p_effective_date => l_effective_date
1829 ,p_budget_entity => 'JOB'
1830 , p_actual_value => l_actual_val
1831 , p_commt_value => l_commitment_val
1832 -- p_total_amount => l_actual_commitment_sal --to be checked
1833 ,p_unit_of_measure => 'MONEY'
1834 ,p_business_group_id => p_business_group_id
1835 );
1836 --
1837 l_currency_code := get_budget_currency(
1838 p_job_id => l_job.job_id
1839 ,p_budget_entity => 'JOB'
1840 ,p_start_date => l_start_date
1841 ,p_end_date => l_end_date
1842 ,p_effective_date => l_effective_date
1843 ,p_business_group_id => p_business_group_id
1844 );
1845 --
1846 -- Print the details of the job
1847 --
1848 hr_utility.set_location('Job : '||l_job.job_id
1849 ||' '||l_proc_name, 130);
1850 hr_utility.set_location('Budgeted Value : '||l_budgeted_val
1851 ||' '||l_proc_name, 140);
1852 hr_utility.set_location('Reallocation Value : '||nvl(l_reallocation_val,0)
1853 ||' '||l_proc_name, 140);
1854 hr_utility.set_location('Actual Value : '||nvl(l_actual_val,0)
1855 ||' '||l_proc_name, 150);
1856 hr_utility.set_location('Commitment Value : '||nvl(l_commitment_val,0)
1857 ||' '||l_proc_name, 160);
1858 hr_utility.set_location('Actual + Commitment Value : '||nvl(l_actual_commitment_val,0)
1859 ||' '||l_proc_name, 160);
1860 --
1861 -- Check, whether the Job is Under Budgeted
1862 --
1863 if l_budgeted_val is not null then
1864 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0) < nvl(l_actual_val,0) + nvl(l_commitment_val,0)) then
1865 --
1866 -- If Under Budgeted
1867 --
1868
1869 --
1870 -- Fetch table route Id for the Position table(PSF)
1871 --
1872 /* open c_table_route('PSF');
1873 fetch c_table_route into l_table_route_id;
1874 -- Set table route id to null if the table route is not defined for PSF
1875 if c_table_route%notfound then
1876 l_table_route_id := null;
1877 end if;
1878 --
1879 close c_table_route;
1880
1881 l_user_name := null;
1882 --
1883 open c_user_name(l_position.position_id);
1884 fetch c_user_name into l_user_name;
1885 close c_user_name; */
1886
1887 --
1888 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - (nvl(l_actual_val,0) + nvl(l_commitment_val,0));
1889 --
1890 --
1891 fnd_file.put_line(FND_FILE.LOG,l_job.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||l_commitment_val||' '||l_under_budget_val||' ');
1892 --
1893 -- Set the Process Log Context Level for the job
1894 --
1895 pqh_process_batch_log.set_context_level
1896 (
1897 p_txn_id =>l_job.job_id,
1898 p_txn_table_route_id =>l_table_route_id, -- later
1899 p_level =>1,
1900 p_log_context =>hr_general.decode_job(l_job.job_id)
1901 );
1902
1903 --
1904 -- Insert the Log for the job
1905 --
1906 pqh_process_batch_log.insert_log
1907 (
1908 p_message_type_cd =>l_message_type,
1909 p_message_text =>l_message,
1910 p_information3 =>round(l_budgeted_val,2),
1911 p_information4 =>round(l_reallocation_val,2),
1912 p_information5 =>round(l_actual_val,2),
1913 p_information6 =>round(l_commitment_val,2), -- p_information7 =>l_user_name
1914 p_information8 =>'JOB',
1915 p_information9 =>p_unit_of_measure,
1916 p_information10 =>l_under_bgt_date,
1917 p_information11 =>p_batch_name,
1918 p_information12 =>'REPORT',
1919 p_information13 => l_currency_code
1920 );
1921
1922 --
1923 hr_utility.set_location(l_job.job_id || ' is under budgeted'
1924 ||' '||l_proc_name, 170);
1925 --
1926 end if;--for under budget
1927 end if; --14/05/02
1928
1929 elsif p_unit_of_measure = 'HOURS' then
1930 --
1931 -- Get the Budgeted hours of the Job for the given start date and end date
1932 --
1933 l_budgeted_val := pqh_budgeted_salary_pkg.get_budgeted_hours
1934 (p_job_id => l_job.job_id
1935 ,p_start_date => l_start_date
1936 ,p_end_date => l_end_date
1937 ,p_effective_date => l_effective_date
1938 ,p_budget_entity => 'JOB'
1939 ,p_business_group_id => p_business_group_id
1940 );
1941
1942 --
1943 -- Get the Reallocation hours of the Job between the given start date and end date
1944 --
1945 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
1946 (p_job_id => l_job.job_id
1947 ,p_start_date => l_start_date
1948 ,p_end_date => l_end_date
1949 ,p_effective_date => l_effective_date
1950 ,p_budget_entity => 'JOB'
1951 ,p_system_budget_unit => 'HOURS'
1952 ,p_business_group_id => p_business_group_id
1953 );
1954 --
1955 --Get the Actual hours of the job between the given start date and end date
1956 --
1957 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1958 ( p_job_id => l_job.job_id
1959 ,p_start_date => l_start_date
1960 ,p_end_date => l_end_date
1961 ,p_effective_date => l_effective_date
1962 ,p_unit_of_measure => 'HOURS'
1963 ,p_budget_entity => 'JOB'
1964 ,p_business_group_id => p_business_group_id
1965 , p_actual_value => l_actual_val
1966 , p_commt_value => l_commitment_val
1967 );
1968
1969 --
1970 -- Print the details of the Job
1971 --
1972 hr_utility.set_location('Job : '||l_job.job_id
1973 ||' '||l_proc_name, 130);
1974 hr_utility.set_location('Budgeted Hours : '||l_budgeted_val
1975 ||' '||l_proc_name, 140);
1976 hr_utility.set_location('Reallocation Hours: '||nvl(l_reallocation_val,0)
1977 ||' '||l_proc_name, 140);
1978 hr_utility.set_location('Actual Hours : '||nvl(l_actual_val,0)
1979 ||' '||l_proc_name, 150);
1980
1981 if l_budgeted_val is not null then
1982 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
1983
1984 --
1985 -- If Under Budgeted
1986 --
1987 -- New table route --pqcptca.ldt---also change in Process log form
1988 --
1989 -- Fetch table route Id for the Job table
1990 --
1991 /*open c_table_route('PSF');
1992 fetch c_table_route into l_table_route_id;
1993 -- Set table route id to null if the table route is not defined for PSF
1994 if c_table_route%notfound then
1995 l_table_route_id := null;
1996 end if;
1997 --
1998 close c_table_route;
1999 --
2000 l_user_name := null;
2001 --
2002 open c_user_name(l_position.position_id);
2003 fetch c_user_name into l_user_name;
2004 close c_user_name; */
2005 --
2006 --
2007 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
2008
2009 fnd_file.put_line(FND_FILE.LOG,l_job.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' ');
2010 --
2011 --
2012 -- Set the Process Log Context Level for the Job
2013 --
2014 pqh_process_batch_log.set_context_level
2015 (
2016 p_txn_id =>l_job.job_id,
2017 p_txn_table_route_id =>l_table_route_id,
2018 p_level =>1,
2019 p_log_context =>hr_general.decode_job(l_job.job_id)
2020 );
2021
2022 --
2023 -- Insert the Log for the Job
2024 --
2025 pqh_process_batch_log.insert_log
2026 (
2027 p_message_type_cd =>l_message_type,
2028 p_message_text =>l_message,
2029 p_information3 =>round(l_budgeted_val,2),
2030 p_information4 =>round(l_reallocation_val,2),
2031 p_information5 =>round(l_actual_val,2),
2032 p_information7 =>null,
2033 p_information8 =>'JOB',
2034 p_information9 =>p_unit_of_measure,
2035 p_information10 =>l_under_bgt_date,
2036 p_information11 =>p_batch_name,
2037 p_information12 =>'REPORT'
2038 );
2039
2040
2041 end if;
2042 end if;
2043
2044 else -- p_unit_of_measure is 'FTE' or 'Headcount' etc
2045
2046 --
2047 -- Get the Budgeted FTE or Headcount of the Job for the given start date and end date
2048 --
2049
2050 l_budgeted_val := pqh_psf_bus.get_budgeted_fte
2051 (p_job_id => l_job.job_id
2052 ,p_start_date => l_start_date
2053 ,p_end_date => l_end_date
2054 ,p_budget_entity => 'JOB'
2055 ,p_unit_of_measure => p_unit_of_measure
2056 ,p_business_group_id => p_business_group_id
2057 ,p_budgeted_fte_date => l_budgeted_fte_date
2058 );
2059
2060 --
2061 -- Get the Reallocation FTE or Headcount of the Job between the given start date and end date
2062 --
2063 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
2064 (p_job_id => l_job.job_id
2065 ,p_start_date => l_start_date
2066 ,p_end_date => l_end_date
2067 ,p_effective_date => l_effective_date
2068 ,p_budget_entity => 'JOB'
2069 ,p_system_budget_unit => p_unit_of_measure
2070 ,p_business_group_id => p_business_group_id
2071 );
2072 --
2073 --Get the Actual FTE or Headcount of the job between the given start date and end date
2074 --
2075 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
2076 ( p_job_id => l_job.job_id
2077 , p_start_date => l_start_date
2078 , p_end_date => l_end_date
2079 ,p_effective_date => l_effective_date
2080 ,p_budget_entity => 'JOB'
2081 , p_unit_of_measure => p_unit_of_measure
2082 , p_business_group_id => p_business_group_id
2083 , p_actual_value => l_actual_val
2084 , p_commt_value => l_commitment_val
2085 );
2086
2087 --
2088 -- Print the details of the Job
2089 --
2090 hr_utility.set_location('Job : '||l_job.job_id
2091 ||' '||l_proc_name, 130);
2092 hr_utility.set_location('Unit of measure : '||p_unit_of_measure
2093 ||' '||l_proc_name, 130);
2094 hr_utility.set_location('Budgeted : '||l_budgeted_val
2095 ||' '||l_proc_name, 140);
2096 hr_utility.set_location('Reallocation: '||nvl(l_reallocation_val,0)
2097 ||' '||l_proc_name, 140);
2098 hr_utility.set_location('Actual : '||nvl(l_actual_val,0)
2099 ||' '||l_proc_name, 150);
2100
2101 if l_budgeted_val is not null then
2102 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
2103
2104 --
2105 -- If Under Budgeted
2106 --
2107
2108 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
2109 --
2110 -- Fetch table route Id for the Position table(PSF)
2111 --
2112 /*open c_table_route('PSF');
2113 fetch c_table_route into l_table_route_id;
2114 -- Set table route id to null if the table route is not defined for PSF
2115 if c_table_route%notfound then
2116 l_table_route_id := null;
2117 end if;
2118 --
2119 close c_table_route;
2120 --
2121 l_user_name := null;
2122 --
2123 open c_user_name(l_position.position_id);
2124 fetch c_user_name into l_user_name;
2125 close c_user_name; */
2126 --
2127 -- Print the details on concurrent log
2128 --
2129
2130 fnd_file.put_line(FND_FILE.LOG,l_job.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' '||l_budgeted_fte_date);
2131
2132 --
2133 -- Set the Process Log Context Level for the job
2134 --
2135 pqh_process_batch_log.set_context_level
2136 (
2137 p_txn_id =>l_job.job_id,
2138 p_txn_table_route_id =>l_table_route_id,
2139 p_level =>1,
2140 p_log_context =>hr_general.decode_job(l_job.job_id)
2141 );
2142
2143 --
2144 -- Insert the Log for the job
2145 --
2146 pqh_process_batch_log.insert_log
2147 (
2148 p_message_type_cd =>l_message_type,
2149 p_message_text =>l_message,
2150 p_information3 =>round(l_budgeted_val,2),
2151 p_information4 =>round(l_reallocation_val,2),
2152 p_information5 =>round(l_actual_val,2),
2153 p_information7 =>null,
2154 p_information8 =>'JOB',
2155 p_information9 =>p_unit_of_measure,
2156 p_information10 =>to_char(l_budgeted_fte_date,'RRRR/MM/DD'),--l_under_bgt_date,
2157 p_information11 =>p_batch_name,
2158 p_information12 =>'REPORT'
2159
2160 );
2161
2162 --
2163 hr_utility.set_location(l_job.job_id || ' is under budgeted'
2164 ||' '||l_proc_name, 170);
2165 --
2166 end if;--FYI
2167 end if; --14/05/02
2168
2169 end if;--for uom
2170 --
2171 end loop;
2172 --
2173 end if;
2174 --
2175 --
2176 -- End the Process Log
2177 --
2178 pqh_process_batch_log.end_log;
2179 hr_utility.set_location('End Process'
2180 ||' '||l_proc_name, 180);
2181 commit;
2182 exception
2183 when others then
2184 retcode := -1;
2185 -- hr_utility.set_location('Error '||sqlerrm,190);
2186 --
2187 end;
2188 --
2189 -- GRADES
2190 --To calculate the Under budgeted grades for all units of measure...
2191 --
2192 Procedure grade_analysis(
2193 errbuf out nocopy varchar2
2194 , retcode out nocopy varchar2
2195 , p_batch_name varchar2
2196 , p_effective_date varchar2
2197 , p_start_date varchar2
2198 , p_end_date varchar2
2199 , p_unit_of_measure varchar2
2200 , p_business_group_id number
2201 ) is
2202 --
2203 l_proc_name varchar2(30) := 'GRADE_ANALYSIS';
2204 --
2205 l_dummy varchar2(50);
2206 l_effective_date date;
2207 l_start_date date;
2208 l_end_date date;
2209 l_error_msg varchar2(1000);
2210 l_parameter1_value varchar2(100);
2211 l_parameter2_value varchar2(100);
2212 l_parameter3_value varchar2(100);
2213 l_parameter4_value varchar2(100);
2214 l_parameter5_value varchar2(100);
2215 l_parameter6_value varchar2(100);
2216 l_parameter7_value varchar2(100);
2217 l_parameter8_value varchar2(100);
2218 l_parameter9_value varchar2(100);
2219 --
2220 -- Cursor to fetch Grades
2221 --
2222
2223 cursor c_grades(p_effective_date date, p_start_date1 date, p_end_date1 date) is
2224 select distinct bdet.grade_id, grd.name
2225 from pqh_budgets bud,
2226 pqh_budget_versions bver,
2227 pqh_budget_details bdet,
2228 per_grades_tl grd
2229 where bud.business_group_id = p_business_group_id
2230 and bud.position_control_flag = 'Y'
2231 and bud.budgeted_entity_cd = 'GRADE'
2232 and (p_start_date1 <= bud.budget_end_date
2233 and p_end_date1 >= bud.budget_start_date)
2234 and bver.budget_id = bud.budget_id
2235 and bver.budget_version_id = bdet.budget_version_id
2236 and bdet.grade_id = grd.grade_id
2237 and grd.language = userenv('LANG');
2238
2239 --
2240 -- Cursor that checks the batch existance
2241 --
2242 cursor check_batch_name(p_batch_name varchar2) is
2243 select 'x'
2244 from pqh_process_log
2245 where log_context=p_batch_name;
2246
2247 --
2248 -- Cursor to get the next batch Id for the Process Log
2249 --
2250 cursor c_batch is
2251 select
2252 pqh_process_log_s.nextval
2253 from
2254 dual;
2255
2256 --
2257 -- Cursor to fetch the table_route_id of the table_alias
2258 --
2259 cursor c_table_route(p_table_alias varchar2) is
2260 SELECT
2261 table_route_id
2262 from
2263 pqh_table_route
2264 where
2265 table_alias = p_table_alias;
2266
2267 --
2268 -- Local Variables
2269 --
2270 l_budgeted_val number;
2271 l_reallocation_val number;
2272 l_actual_val number;
2273 l_commitment_val number;
2274 l_actual_commitment_val number;
2275 l_under_budget_val number;
2276 l_budgeted_fte_date date;
2277 --
2278 l_actuals_status number;
2279 l_batch_id number;
2280 l_table_route_id number;
2281 --
2282 l_apply_error_mesg varchar2(100);
2283 l_apply_error_num varchar2(100);
2284 --
2285 l_message_type_cd varchar2(10);
2286 l_message_type varchar2(100);
2287 l_message varchar2(1000);
2288 l_under_bgt_date varchar2(100);
2289 --
2290 l_currency_code varchar2(40);
2291 --
2292 begin
2293 --
2294 hr_utility.set_location('Entering'|| l_proc_name, 10);
2295 retcode := 0;
2296 --
2297 hr_utility.set_location('Entering'|| l_proc_name, 11);
2298 --
2299 open check_batch_name(p_batch_name);
2300 fetch check_batch_name into l_dummy;
2301 if check_batch_name%found then
2302 retcode := -1;
2303 fnd_message.set_name('PQH', 'PQH_PLG_DUP_BATCH');
2304 fnd_message.set_token('BATCH_NAME', p_batch_name);
2305 errbuf := fnd_message.get;
2306 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
2307 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
2308 return;
2309 end if;
2310 --
2311 hr_utility.set_location('Entering'|| l_proc_name, 13);
2312 --
2313 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
2314 l_start_date := fnd_date.canonical_to_date(p_start_date);
2315 l_end_date := fnd_date.canonical_to_date(p_end_date);
2316 --
2317 -- Fetch the batch Id into the l_batch_id
2318 --
2319 open c_batch;
2320 fetch c_batch into l_batch_id;
2321 close c_batch;
2322 --
2323 hr_utility.set_location('l_batch_id : '||l_batch_id ||' - ' || l_proc_name, 15);
2324 --
2325 -- Create the start record into the Process Log
2326 --
2327 pqh_process_batch_log.start_log
2328 (
2329 p_batch_id =>l_batch_id,
2330 p_module_cd =>'POSITION_BUDGET_ANALYSIS',
2331 p_log_context =>p_batch_name,
2332 p_information3 =>p_effective_date,
2333 p_information5 =>p_start_date,
2334 p_information6 =>p_end_date,
2335 p_information8 =>'GRADE',
2336 p_information9 =>p_unit_of_measure
2337 );
2338 --
2339 --
2340 -- Fetch table route Id for the Grade table(GRD)
2341 --
2342 open c_table_route('GRD');
2343 fetch c_table_route into l_table_route_id;
2344 -- Set table route id to null if the table route is not defined for GRD
2345 if c_table_route%notfound then
2346 l_table_route_id := null;
2347 end if;
2348 --
2349 close c_table_route;
2350
2351 hr_utility.set_location('l_table_route_id : '||l_table_route_id ||' '|| l_proc_name, 102);
2352 --
2353 -- Check for the type of the cofigurable message(PQH_UNDER_BGT_POSITIONS)
2354 --
2355 pqh_utility.set_message(8302,'PQH_UNDER_BGT_POSITIONS',200);
2356 pqh_utility.set_message_token('UOM',
2357 hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE',p_unit_of_measure));
2358 pqh_utility.set_message_token('ENTITY',
2359 hr_general.decode_lookup('PQH_BUDGET_ENTITY','GRADE'));
2360
2361 --
2362 hr_utility.set_location('after pqh_utility.set_message : '|| l_proc_name, 103);
2363 --
2364 l_message_type_cd := pqh_utility.get_message_type_cd;
2365 l_message := pqh_utility.get_message;
2366 --
2367 hr_utility.set_location('after pqh_utility.get_message : '||l_message_type_cd|| l_proc_name, 104);
2368 --
2369 if l_message_type_cd in ('E','W') then
2370 if l_message_type_cd = 'E' then
2371 l_message_type := 'ERROR';
2372 else
2373 l_message_type := 'WARNING';
2374 end if;
2375 hr_utility.set_location('before pqh_process_batch_log.set_context_level : '||
2376 l_message_type_cd||l_proc_name, 105);
2377 hr_utility.set_location('before pqh.set_context_level l_table_route_id: '||
2378 l_table_route_id, 105);
2379 --
2380 -- Set the Process Log Context level....What should it be?
2381 --
2382 /*pqh_process_batch_log.set_context_level
2383 (
2384 p_txn_id =>l_organization.organization_id,
2385 p_txn_table_route_id =>l_table_route_id,
2386 p_level =>l_organization.level1 + 1,
2387 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
2388 ); */
2389 --
2390 --
2391 fnd_file.put_line(FND_FILE.LOG,'Primary Entity => ''GRADE''' || ' Unit of Measure => '||p_unit_of_measure);
2392 fnd_file.put_line(FND_FILE.LOG,' ');
2393 fnd_file.put_line(FND_FILE.LOG,'Name Budgeted Value Reallocated Value Actual Value Commitment Value Under Budgeted Value Under Budgeted Date');
2394 fnd_file.put_line(FND_FILE.LOG,' ');
2395 --
2396 -- Fetch Grades
2397 --
2398 for l_grade in c_grades(l_effective_date, l_start_date, l_end_date)
2399 loop
2400
2401 hr_utility.set_location('l_grade_id : '||l_grade.grade_id|| ' - ' || substr(l_grade.name,1,40) , 110);
2402
2403 if p_unit_of_measure = 'MONEY' then
2404 --
2405 -- Get the Budgeted Salary of the Grade for the given start date and end date
2406 --
2407 l_budgeted_val := pqh_budgeted_salary_pkg.get_pc_budgeted_salary(
2408 p_grade_id => l_grade.grade_id
2409 ,p_start_date => l_start_date
2410 ,p_end_date => l_end_date
2411 ,p_effective_date => l_effective_date
2412 ,p_business_group_id => p_business_group_id
2413 ,p_budget_entity => 'GRADE'
2414 );
2415 --
2416 -- Get the Reallocation amount(Money) of the Grade between the given start date and end date
2417 --
2418 l_reallocation_val := pqh_reallocation_pkg.get_reallocation(
2419 p_grade_id => l_grade.grade_id
2420 ,p_start_date => l_start_date
2421 ,p_end_date => l_end_date
2422 ,p_effective_date => l_effective_date
2423 ,p_budget_entity => 'GRADE'
2424 ,p_system_budget_unit => 'MONEY'
2425 ,p_business_group_id => p_business_group_id
2426 );
2427 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt(
2428 p_grade_id => l_grade.grade_id
2429 ,p_start_date => l_start_date
2430 ,p_end_date => l_end_date
2431 ,p_effective_date => l_effective_date
2432 ,p_budget_entity => 'GRADE'
2433 , p_actual_value => l_actual_val
2434 , p_commt_value => l_commitment_val
2435 -- p_total_amount => l_actual_commitment_sal --to be checked
2436 ,p_unit_of_measure => 'MONEY'
2437 , p_business_group_id => p_business_group_id
2438 );
2439 --
2440 l_currency_code := get_budget_currency(
2441 p_grade_id => l_grade.grade_id
2442 ,p_budget_entity => 'GRADE'
2443 ,p_start_date => l_start_date
2444 ,p_end_date => l_end_date
2445 ,p_effective_date => l_effective_date
2446 ,p_business_group_id => p_business_group_id
2447 );
2448 --
2449 -- Print the details of the grade
2450 --
2451 hr_utility.set_location('Grade : '||l_grade.grade_id
2452 ||' '||l_proc_name, 130);
2453 hr_utility.set_location('Budgeted Value : '||l_budgeted_val
2454 ||' '||l_proc_name, 140);
2455 hr_utility.set_location('Reallocation Value : '||nvl(l_reallocation_val,0)
2456 ||' '||l_proc_name, 140);
2457 hr_utility.set_location('Actual Value : '||nvl(l_actual_val,0)
2458 ||' '||l_proc_name, 150);
2459 hr_utility.set_location('Commitment Value : '||nvl(l_commitment_val,0)
2460 ||' '||l_proc_name, 160);
2461 hr_utility.set_location('Actual + Commitment Value : '||nvl(l_actual_commitment_val,0)
2462 ||' '||l_proc_name, 160);
2463 --
2464 -- Check, whether the Grade is Under Budgeted
2465 --
2466 if l_budgeted_val is not null then
2467 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0) < nvl(l_actual_val,0) + nvl(l_commitment_val,0)) then
2468 --
2469 -- If Under Budgeted
2470 --
2471
2472 --
2473 -- Fetch table route Id for the Position table(PSF)
2474 --
2475 /* open c_table_route('PSF');
2476 fetch c_table_route into l_table_route_id;
2477 -- Set table route id to null if the table route is not defined for PSF
2478 if c_table_route%notfound then
2479 l_table_route_id := null;
2480 end if;
2481 --
2482 close c_table_route;
2483
2484 l_user_name := null;
2485 --
2486 open c_user_name(l_position.position_id);
2487 fetch c_user_name into l_user_name;
2488 close c_user_name; */
2489 --
2490 --
2491 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - (nvl(l_actual_val,0) + nvl(l_commitment_val,0));
2492 --
2493 fnd_file.put_line(FND_FILE.LOG,l_grade.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||l_commitment_val||' '||l_under_budget_val||' ');
2494 --
2495 -- Set the Process Log Context Level for the grade
2496 --
2497 pqh_process_batch_log.set_context_level
2498 (
2499 p_txn_id =>l_grade.grade_id,
2500 p_txn_table_route_id =>l_table_route_id, -- later
2501 p_level =>1,
2502 p_log_context =>hr_general.decode_grade(l_grade.grade_id)
2503 );
2504
2505 --
2506 -- Insert the Log for the grade
2507 --
2508 pqh_process_batch_log.insert_log
2509 (
2510 p_message_type_cd =>l_message_type,
2511 p_message_text =>l_message,
2512 p_information3 =>round(l_budgeted_val,2),
2513 p_information4 =>round(l_reallocation_val,2),
2514 p_information5 =>round(l_actual_val,2),
2515 p_information6 =>round(l_commitment_val,2),
2516 p_information8 =>'GRADE',
2517 p_information9 =>p_unit_of_measure,
2518 p_information10 =>l_under_bgt_date,
2519 p_information11 =>p_batch_name,
2520 p_information12 =>'REPORT',
2521 p_information13 => l_currency_code
2522 );
2523
2524 --
2525 hr_utility.set_location(l_grade.grade_id || ' is under budgeted'
2526 ||' '||l_proc_name, 170);
2527 --
2528 end if;--for under budget
2529 end if; --14/05/02
2530
2531 elsif p_unit_of_measure = 'HOURS' then
2532 --
2533 -- Get the Budgeted hours of the Grade for the given start date and end date
2534 --
2535 l_budgeted_val := pqh_budgeted_salary_pkg.get_budgeted_hours
2536 (p_grade_id => l_grade.grade_id
2537 ,p_start_date => l_start_date
2538 ,p_end_date => l_end_date
2539 ,p_effective_date => l_effective_date
2540 ,p_budget_entity => 'GRADE'
2541 ,p_business_group_id => p_business_group_id
2542 );
2543
2544 --
2545 -- Get the Reallocation hours of the Grade between the given start date and end date
2546 --
2547 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
2548 (p_grade_id => l_grade.grade_id
2549 ,p_start_date => l_start_date
2550 ,p_end_date => l_end_date
2551 ,p_effective_date => l_effective_date
2552 ,p_budget_entity => 'GRADE'
2553 ,p_system_budget_unit => 'HOURS'
2554 ,p_business_group_id => p_business_group_id
2555 );
2556 --
2557 --Get the Actual hours of the Grade between the given start date and end date
2558 --
2559 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
2560 ( p_grade_id => l_grade.grade_id
2561 , p_start_date => l_start_date
2562 , p_end_date => l_end_date
2563 , p_effective_date => l_effective_date
2564 , p_budget_entity => 'GRADE'
2565 , p_unit_of_measure => 'HOURS'
2566 , p_business_group_id => p_business_group_id
2567 , p_actual_value => l_actual_val
2568 , p_commt_value => l_commitment_val
2569 );
2570
2571 --
2572 -- Print the details of the Grade
2573 --
2574 hr_utility.set_location('Grade : '||l_grade.grade_id
2575 ||' '||l_proc_name, 130);
2576 hr_utility.set_location('Budgeted Hours : '||l_budgeted_val
2577 ||' '||l_proc_name, 140);
2578 hr_utility.set_location('Reallocation Hours: '||nvl(l_reallocation_val,0)
2579 ||' '||l_proc_name, 140);
2580 hr_utility.set_location('Actual Hours : '||nvl(l_actual_val,0)
2581 ||' '||l_proc_name, 150);
2582
2583 if l_budgeted_val is not null then
2584 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
2585
2586 --
2587 -- If Under Budgeted
2588 --
2589 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
2590 --
2591 -- New table route --pqcptca.ldt---also change in Process log form
2592 --
2593 -- Fetch table route Id for the Job table
2594 --
2595 /*open c_table_route('PSF');
2596 fetch c_table_route into l_table_route_id;
2597 -- Set table route id to null if the table route is not defined for PSF
2598 if c_table_route%notfound then
2599 l_table_route_id := null;
2600 end if;
2601 --
2602 close c_table_route;
2603 --
2604 l_user_name := null;
2605 --
2606 open c_user_name(l_position.position_id);
2607 fetch c_user_name into l_user_name;
2608 close c_user_name; */
2609 --
2610 --
2611 --
2612 fnd_file.put_line(FND_FILE.LOG,l_grade.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' ');
2613 --
2614 -- Set the Process Log Context Level for the Grade
2615 --
2616 pqh_process_batch_log.set_context_level
2617 (
2618 p_txn_id =>l_grade.grade_id,
2619 p_txn_table_route_id =>l_table_route_id,
2620 p_level =>1,
2621 p_log_context =>hr_general.decode_grade(l_grade.grade_id)
2622 );
2623
2624 --
2625 -- Insert the Log for the Grade
2626 --
2627 pqh_process_batch_log.insert_log
2628 (
2629 p_message_type_cd =>l_message_type,
2630 p_message_text =>l_message,
2631 p_information3 =>round(l_budgeted_val,2),
2632 p_information4 =>round(l_reallocation_val,2),
2633 p_information5 =>round(l_actual_val,2),
2634 p_information7 =>null,
2635 p_information8 =>'GRADE',
2636 p_information9 =>p_unit_of_measure,
2637 p_information10 =>l_under_bgt_date,
2638 p_information11 =>p_batch_name,
2639 p_information12 =>'REPORT'
2640
2641 );
2642
2643
2644 end if;
2645 end if; --14/05/02
2646
2647 else -- p_unit_of_measure is 'FTE' or 'Headcount' etc
2648
2649 --
2650 -- Get the Budgeted FTE or Headcount of the Grade for the given start date and end date
2651 --
2652
2653 l_budgeted_val := pqh_psf_bus.get_budgeted_fte
2654 (p_grade_id => l_grade.grade_id
2655 ,p_start_date => l_start_date
2656 ,p_end_date => l_end_date
2657 ,p_budget_entity => 'GRADE'
2658 ,p_unit_of_measure => p_unit_of_measure
2659 ,p_business_group_id => p_business_group_id
2660 ,p_budgeted_fte_date => l_budgeted_fte_date
2661 );
2662
2663 --
2664 -- Get the Reallocation FTE or Headcount of the Grade between the given start date and end date
2665 --
2666 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
2667 (p_grade_id => l_grade.grade_id
2668 ,p_start_date => l_start_date
2669 ,p_end_date => l_end_date
2670 ,p_effective_date => l_effective_date
2671 ,p_budget_entity => 'GRADE'
2672 ,p_system_budget_unit => p_unit_of_measure
2673 ,p_business_group_id => p_business_group_id
2674 );
2675 --
2676 --Get the Actual FTE or Headcount of the Grade between the given start date and end date
2677 --
2678 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
2679 (p_grade_id => l_grade.grade_id
2680 , p_start_date => l_start_date
2681 , p_end_date => l_end_date
2682 , p_effective_date => l_effective_date
2683 , p_budget_entity => 'GRADE'
2684 , p_unit_of_measure => p_unit_of_measure
2685 , p_business_group_id => p_business_group_id
2686 , p_actual_value => l_actual_val
2687 , p_commt_value => l_commitment_val
2688 );
2689
2690 --
2691 -- Print the details of the Grade
2692 --
2693 hr_utility.set_location('Grade : '||l_grade.grade_id
2694 ||' '||l_proc_name, 130);
2695 hr_utility.set_location('Unit of measure : '||p_unit_of_measure
2696 ||' '||l_proc_name, 130);
2697 hr_utility.set_location('Budgeted : '||l_budgeted_val
2698 ||' '||l_proc_name, 140);
2699 hr_utility.set_location('Reallocation: '||nvl(l_reallocation_val,0)
2700 ||' '||l_proc_name, 140);
2701 hr_utility.set_location('Actual : '||nvl(l_actual_val,0)
2702 ||' '||l_proc_name, 150);
2703
2704 if l_budgeted_val is not null then
2705 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
2706
2707 --
2708 -- If Under Budgeted
2709 --
2710 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
2711 --
2712 -- Fetch table route Id for the Position table(PSF)
2713 --
2714 /*open c_table_route('PSF');
2715 fetch c_table_route into l_table_route_id;
2716 -- Set table route id to null if the table route is not defined for PSF
2717 if c_table_route%notfound then
2718 l_table_route_id := null;
2719 end if;
2720 --
2721 close c_table_route;
2722 --
2723 l_user_name := null;
2724 --
2725 open c_user_name(l_position.position_id);
2726 fetch c_user_name into l_user_name;
2727 close c_user_name; */
2728 --
2729 --
2730 --
2731 fnd_file.put_line(FND_FILE.LOG,l_grade.name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||0||' '||l_under_budget_val||' '||l_budgeted_fte_date);
2732 --
2733 -- Set the Process Log Context Level for the grade
2734 --
2735 pqh_process_batch_log.set_context_level
2736 (
2737 p_txn_id =>l_grade.grade_id,
2738 p_txn_table_route_id =>l_table_route_id,
2739 p_level =>1,
2740 p_log_context =>hr_general.decode_grade(l_grade.grade_id)
2741 );
2742
2743 --
2744 -- Insert the Log for the grade
2745 --
2746 pqh_process_batch_log.insert_log
2747 (
2748 p_message_type_cd =>l_message_type,
2749 p_message_text =>l_message,
2750 p_information3 =>round(l_budgeted_val,2),
2751 p_information4 =>round(l_reallocation_val,2),
2752 p_information5 =>round(l_actual_val,2),
2753 p_information7 =>null,
2754 p_information8 =>'GRADE',
2755 p_information9 =>p_unit_of_measure,
2756 p_information10 =>to_char(l_budgeted_fte_date,'RRRR/MM/DD'),
2757 p_information11 =>p_batch_name,
2758 p_information12 =>'REPORT'
2759 );
2760
2761 --
2762 hr_utility.set_location(l_grade.grade_id || ' is under budgeted'
2763 ||' '||l_proc_name, 170);
2764 --
2765 end if;
2766 end if; --14/05/02
2767
2768
2769 end if;--for uom
2770 --
2771 end loop;
2772 --
2773 end if;
2774 --
2775 --
2776 -- End the Process Log
2777 --
2778 pqh_process_batch_log.end_log;
2779 hr_utility.set_location('End Process'
2780 ||' '||l_proc_name, 180);
2781 commit;
2782 exception
2783 when others then
2784 retcode := -1;
2785 --
2786 end;
2787 --
2788 -- ORGANIZATION TEMP
2789 --
2790 procedure org_temp(p_organization_id number
2791 ,p_level1 number
2792 ,p_batch_name varchar2
2793 ,p_unit_of_measure varchar2
2794 ,p_business_group_id number
2795 ,p_effective_date date
2796 ,p_start_date date
2797 ,p_end_date date
2798 ) is
2799 l_proc_name varchar2(61) := 'org_temp' ;
2800 l_org_name hr_all_organization_units.name%type;
2801 --
2802 -- Cursor to fetch Organization name
2803 --
2804 cursor c_org_name(p_org_id number) is
2805 select name
2806 from hr_all_organization_units u
2807 where organization_id = p_org_id;
2808 --
2809 --
2810 -- Cursor to fetch the table_route_id of the table_alias
2811 --
2812 cursor c_table_route(p_table_alias varchar2) is
2813 SELECT
2814 table_route_id
2815 from
2816 pqh_table_route
2817 where
2818 table_alias = p_table_alias;
2819 --
2820 -- Local Variables
2821 --
2822 l_budgeted_val number;
2823 l_reallocation_val number;
2824 l_actual_val number;
2825 l_commitment_val number;
2826 l_actual_commitment_val number;
2827 l_under_budget_val number;
2828 l_budgeted_fte_date date;
2829 --
2830 l_table_route_id number;
2831 l_user_name varchar2(30);
2832 --
2833 l_message_type_cd varchar2(10);
2834 l_message_type varchar2(100);
2835 l_message varchar2(1000);
2836 l_under_bgt_date varchar2(100);
2837 --
2838 l_currency_code varchar2(40);
2839 --
2840 BEGIN
2841 hr_utility.set_location('organization : '||p_organization_id ||' '|| l_proc_name, 101);
2842
2843 open c_org_name(p_organization_id);
2844 fetch c_org_name into l_org_name;
2845 close c_org_name;
2846 --
2847 -- Fetch table route Id for the Organization table(ORU)
2848 --
2849 open c_table_route('ORU');
2850 fetch c_table_route into l_table_route_id;
2851 -- Set table route id to null if the table route is not defined for ORU
2852 if c_table_route%notfound then
2853 l_table_route_id := null;
2854 end if;
2855 --
2856 close c_table_route;
2857
2858 hr_utility.set_location('l_table_route_id : '||l_table_route_id ||' '|| l_proc_name, 102);
2859 --
2860 -- Check for the type of the cofigurable message(PQH_UNDER_BGT_POSITIONS)
2861 --
2862 pqh_utility.set_message(8302,'PQH_UNDER_BGT_POSITIONS', p_organization_id);
2863 --
2864 pqh_utility.set_message_token('UOM',
2865 hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE',p_unit_of_measure));
2866 pqh_utility.set_message_token('ENTITY',
2867 hr_general.decode_lookup('PQH_BUDGET_ENTITY','ORGANIZATION'));
2868
2869 hr_utility.set_location('after pqh_utility.set_message : '|| l_proc_name, 103);
2870 --
2871 l_message_type_cd := pqh_utility.get_message_type_cd;
2872 l_message := pqh_utility.get_message;
2873 --
2874 hr_utility.set_location('after pqh_utility.get_message : '||l_message_type_cd|| l_proc_name, 104);
2875 --
2876 if l_message_type_cd in ('E','W') then
2877 if l_message_type_cd = 'E' then
2878 l_message_type := 'ERROR';
2879 else
2880 l_message_type := 'WARNING';
2881 end if;
2882 hr_utility.set_location('before pqh_process_batch_log.set_context_level : '||
2883 l_message_type_cd||l_proc_name, 105);
2884 hr_utility.set_location('before pqh.set_context_level organization_id: '||
2885 p_organization_id, 105);
2886 hr_utility.set_location('before pqh.set_context_level l_table_route_id: '||
2887 l_table_route_id, 105);
2888 hr_utility.set_location('l_orglevel1: '||
2889 p_level1, 105);
2890 hr_utility.set_location('org name: '||
2891 hr_general.decode_organization(p_organization_id), 105);
2892 --
2893 -- Set the Process Log Context level for the Organization
2894 --
2895 pqh_process_batch_log.set_context_level
2896 (
2897 p_txn_id =>p_organization_id,
2898 p_txn_table_route_id =>l_table_route_id,
2899 p_level =>p_level1 + 1,
2900 p_log_context =>hr_general.decode_organization(P_organization_id)
2901 );
2902 --
2903 hr_utility.set_location('Organization : '||p_organization_id
2904 || ' ' ||l_proc_name, 110);
2905
2906 if p_unit_of_measure = 'MONEY' then
2907 --
2908 -- Get the Budgeted Salary of the organization for the given start date and end date
2909 --
2910 l_budgeted_val := pqh_budgeted_salary_pkg.get_pc_budgeted_salary(
2911 p_organization_id => p_organization_id
2912 ,p_budget_entity => 'ORGANIZATION'
2913 ,p_start_date => p_start_date
2914 ,p_end_date => p_end_date
2915 ,p_effective_date => p_effective_date
2916 ,p_business_group_id => p_business_group_id
2917 );
2918 --
2919 -- Get the Reallocation amount(Money) of the organization between the given start date and end date
2920 --
2921 l_reallocation_val := pqh_reallocation_pkg.get_reallocation(
2922 p_organization_id => p_organization_id
2923 ,p_start_date => p_start_date
2924 ,p_end_date => p_end_date
2925 ,p_effective_date => p_effective_date
2926 ,p_budget_entity => 'ORGANIZATION'
2927 ,p_system_budget_unit => 'MONEY'
2928 ,p_business_group_id => p_business_group_id
2929 );
2930 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt(
2931 p_organization_id => p_organization_id
2932 ,p_start_date => p_start_date
2933 ,p_end_date => p_end_date
2934 ,p_effective_date => p_effective_date
2935 ,p_budget_entity => 'ORGANIZATION'
2936 ,p_actual_value => l_actual_val
2937 ,p_commt_value => l_commitment_val
2938 -- p_total_amount => l_actual_commitment_sal
2939 ,p_unit_of_measure => 'MONEY'
2940 ,p_business_group_id => p_business_group_id
2941 );
2942 --
2943 l_currency_code := get_budget_currency(
2944 p_organization_id => p_organization_id
2945 ,p_budget_entity => 'ORGANIZATION'
2946 ,p_start_date => p_start_date
2947 ,p_end_date => p_end_date
2948 ,p_effective_date => p_effective_date
2949 ,p_business_group_id => p_business_group_id
2950 );
2951 --
2952 -- Print the details of the organization
2953 --
2954 hr_utility.set_location('Organization : '||p_organization_id
2955 ||' '||l_proc_name, 130);
2956 hr_utility.set_location('Budgeted Salary : '||l_budgeted_val
2957 ||' '||l_proc_name, 140);
2958 hr_utility.set_location('Budget Reallocation : '||nvl(l_reallocation_val,0)
2959 ||' '||l_proc_name, 140);
2960 hr_utility.set_location('Actual Salary : '||nvl(l_actual_val,0)
2961 ||' '||l_proc_name, 150);
2962 hr_utility.set_location('Commitment Salary : '||nvl(l_commitment_val,0)
2963 ||' '||l_proc_name, 160);
2964 hr_utility.set_location('Actual + Commitment Salary : '||nvl(l_actual_commitment_val,0)
2965 ||' '||l_proc_name, 160);
2966 --
2967 -- Check, whether the organization is Under Budgeted
2968 --
2969 if l_budgeted_val is not null then
2970 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0) < nvl(l_actual_val,0) + nvl(l_commitment_val,0)) then
2971 --
2972 -- If Under Budgeted
2973 --
2974 --
2975 -- Fetch table route Id for the organization table(PSF)
2976 --
2977 --
2978 l_under_budget_val := (nvl(l_budgeted_val,0) +
2979 nvl(l_reallocation_val,0)) - (nvl(l_actual_val,0)
2980 + nvl(l_commitment_val,0));
2981 --
2982 --
2983 fnd_file.put_line(FND_FILE.LOG,l_org_name||' '||l_budgeted_val||
2984 ' '||l_reallocation_val||' '||l_actual_val||' '||
2985 l_commitment_val||' '||l_under_budget_val||' ');
2986 --
2987 -- Set the Process Log Context Level for the Organization
2988 --
2989 /*
2990 pqh_process_batch_log.set_context_level
2991 (
2992 p_txn_id =>l_organization.organization_id,
2993 p_txn_table_route_id =>l_table_route_id,
2994 p_level =>l_organization.level1+2,
2995 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
2996 );
2997 */
2998 --
2999 -- Insert the Log for the organization
3000 --
3001 pqh_process_batch_log.insert_log
3002 (
3003 p_message_type_cd =>l_message_type,
3004 p_message_text =>l_message,
3005 p_information3 =>round(l_budgeted_val,2),
3006 p_information4 =>round(l_reallocation_val,2),
3007 p_information5 =>round(l_actual_val,2),
3008 p_information6 =>round(l_commitment_val,2),
3009 p_information7 =>l_user_name,
3010 p_information8 =>'ORGANIZATION',
3011 p_information9 =>p_unit_of_measure,
3012 p_information10 =>l_under_bgt_date,
3013 p_information11 =>p_batch_name,
3014 p_information12 =>'REPORT',
3015 p_information13 => l_currency_code
3016 );
3017 --
3018 hr_utility.set_location(p_organization_id ||
3019 ' is under budgeted' ||' '||l_proc_name, 170);
3020 --
3021 end if;--for under budget
3022 end if;
3023
3024 elsif p_unit_of_measure = 'HOURS' then
3025 --
3026 -- Get the Budgeted hours of the organization for the given start date and end date
3027 --
3028 l_budgeted_val := pqh_budgeted_salary_pkg.get_budgeted_hours
3029 (p_organization_id => p_organization_id
3030 ,p_start_date => p_start_date
3031 ,p_end_date => p_end_date
3032 ,p_effective_date => p_effective_date
3033 ,p_budget_entity => 'ORGANIZATION'
3034 ,p_business_group_id => p_business_group_id
3035 );
3036
3037 --
3038 -- Get the Reallocation hours of the organization between the given start date and end date
3039 --
3040 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
3041 (p_organization_id => p_organization_id
3042 ,p_start_date => p_start_date
3043 ,p_end_date => p_end_date
3044 ,p_effective_date => p_effective_date
3045 ,p_budget_entity => 'ORGANIZATION'
3046 ,p_system_budget_unit => 'HOURS'
3047 ,p_business_group_id => p_business_group_id
3048 );
3049 --
3050 --Get the Actual hours of the organization between the given start date and end date
3051 --
3052 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
3053 ( p_organization_id => p_organization_id
3054 ,p_start_date => p_start_date
3055 ,p_end_date => p_end_date
3056 ,p_effective_date => p_effective_date
3057 ,p_budget_entity => 'ORGANIZATION'
3058 ,p_unit_of_measure => 'HOURS'
3059 ,p_business_group_id => p_business_group_id
3060 ,p_actual_value => l_actual_val
3061 ,p_commt_value => l_commitment_val
3062 );
3063
3064 --
3065 -- Print the details of the organization
3066 --
3067 hr_utility.set_location('Organization : '||p_organization_id
3068 ||' '||l_proc_name, 130);
3069 hr_utility.set_location('Budgeted Hours : '||l_budgeted_val
3070 ||' '||l_proc_name, 140);
3071 hr_utility.set_location('Reallocation Hours: '||nvl(l_reallocation_val,0)
3072 ||' '||l_proc_name, 140);
3073 hr_utility.set_location('Actual Hours : '||nvl(l_actual_val,0)
3074 ||' '||l_proc_name, 150);
3075
3076 if l_budgeted_val is not null then
3077 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0))
3078 < nvl(l_actual_val,0) then
3079
3080 --
3081 -- If Under Budgeted
3082 --
3083 l_under_budget_val := (nvl(l_budgeted_val,0)
3084 + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
3085 --
3086 fnd_file.put_line(FND_FILE.LOG,l_org_name||' '||
3087 l_budgeted_val||' '||l_reallocation_val||' '||
3088 l_actual_val||' '||l_commitment_val||' '||
3089 l_under_budget_val||' ');
3090 --
3091 -- Set the Process Log Context Level for the organization
3092 --
3093 /*
3094 pqh_process_batch_log.set_context_level
3095 (
3096 p_txn_id =>l_organization.organization_id,
3097 p_txn_table_route_id =>l_table_route_id,
3098 p_level =>l_organization.level1+2,
3099 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
3100 );
3101 */
3102 --
3103 -- Insert the Log for the organization
3104 --
3105 pqh_process_batch_log.insert_log
3106 (
3107 p_message_type_cd =>l_message_type,
3108 p_message_text =>l_message,
3109 p_information3 =>round(l_budgeted_val,2),
3110 p_information4 =>round(l_reallocation_val,2),
3111 p_information5 =>round(l_actual_val,2),
3112 p_information7 =>l_user_name,
3113 p_information8 =>'ORGANIZATION',
3114 p_information9 =>p_unit_of_measure,
3115 p_information10 =>l_under_bgt_date,
3116 p_information11 =>p_batch_name,
3117 p_information12 =>'REPORT'
3118 );
3119
3120 hr_utility.set_location(p_organization_id ||
3121 ' is under budgeted' ||' '||l_proc_name, 170);
3122 --
3123
3124 end if;
3125 end if;
3126
3127 else -- p_unit_of_measure is 'FTE' or 'Headcount' etc
3128
3129 --
3130 -- Get the Budgeted FTE or Headcount of the organization for the given start date and end date
3131 --
3132
3133 l_budgeted_val := pqh_psf_bus.get_budgeted_fte
3134 (p_organization_id => p_organization_id
3135 ,p_start_date => p_start_date
3136 ,p_end_date => p_end_date
3137 ,p_budget_entity => 'ORGANIZATION'
3138 ,p_unit_of_measure => p_unit_of_measure
3139 ,p_business_group_id => p_business_group_id
3140 ,p_budgeted_fte_date => l_budgeted_fte_date
3141 );
3142
3143 --
3144 -- Get the Reallocation FTE or Headcount of the organization between the given start date and end date
3145 --
3146 l_reallocation_val := pqh_reallocation_pkg.get_reallocation
3147 (p_organization_id => p_organization_id
3148 ,p_start_date => p_start_date
3149 ,p_end_date => p_end_date
3150 ,p_effective_date => p_effective_date
3151 ,p_budget_entity => 'ORGANIZATION'
3152 ,p_system_budget_unit => p_unit_of_measure
3153 ,p_business_group_id => p_business_group_id
3154 );
3155 --
3156 --Get the Actual FTE or Headcount of the organization between the given start date and end date
3157 --
3158 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
3159 ( p_organization_id => p_organization_id
3160 , p_start_date => p_start_date
3161 , p_end_date => p_end_date
3162 , p_effective_date => p_effective_date
3163 , p_budget_entity => 'ORGANIZATION'
3164 , p_unit_of_measure => p_unit_of_measure
3165 , p_business_group_id => p_business_group_id
3166 , p_actual_value => l_actual_val
3167 , p_commt_value => l_commitment_val
3168 );
3169
3170 --
3171 -- Print the details of the organization
3172 --
3173 hr_utility.set_location('Organization : '||p_organization_id
3174 ||' '||l_proc_name, 130);
3175 hr_utility.set_location('Unit of measure : '||p_unit_of_measure
3176 ||' '||l_proc_name, 130);
3177 hr_utility.set_location('Budgeted : '||l_budgeted_val
3178 ||' '||l_proc_name, 140);
3179 hr_utility.set_location('Reallocation: '||nvl(l_reallocation_val,0)
3180 ||' '||l_proc_name, 140);
3181 hr_utility.set_location('Actual : '||nvl(l_actual_val,0)
3182 ||' '||l_proc_name, 150);
3183
3184 if l_budgeted_val is not null then
3185 if (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) < nvl(l_actual_val,0) then
3186
3187 --
3188 -- If Under Budgeted
3189 l_under_budget_val := (nvl(l_budgeted_val,0) + nvl(l_reallocation_val,0)) - nvl(l_actual_val,0);
3190 --
3191 --
3192 fnd_file.put_line(FND_FILE.LOG,l_org_name||' '||l_budgeted_val||' '||l_reallocation_val||' '||l_actual_val||' '||l_commitment_val||' '||l_under_budget_val||' '||l_budgeted_fte_date);
3193 --
3194 -- Set the Process Log Context Level for the organization
3195 --
3196 /*
3197 pqh_process_batch_log.set_context_level
3198 (
3199 p_txn_id =>l_organization.organization_id,
3200 p_txn_table_route_id =>l_table_route_id,
3201 p_level =>l_organization.level1+2,
3202 p_log_context =>hr_general.decode_organization(l_organization.organization_id)
3203 );
3204 */
3205 --
3206 -- Insert the Log for the organization
3207 --
3208 pqh_process_batch_log.insert_log
3209 (
3210 p_message_type_cd =>l_message_type,
3211 p_message_text =>l_message,
3212 p_information3 =>round(l_budgeted_val,2),
3213 p_information4 =>round(l_reallocation_val,2),
3214 p_information5 =>round(l_actual_val,2),
3215 p_information7 =>l_user_name,
3216 p_information8 =>'ORGANIZATION',
3217 p_information9 =>p_unit_of_measure,
3218 p_information10 =>to_char(l_budgeted_fte_date,'YYYY/MM/DD'), --l_under_bgt_date,
3219 p_information11 =>p_batch_name,
3220 p_information12 =>'REPORT'
3221 );
3222 --
3223 hr_utility.set_location(p_organization_id ||
3224 ' is under budgeted' ||' '||l_proc_name, 170);
3225 --
3226 end if;
3227 end if;
3228 end if;--for uom
3229 --
3230 end if;
3231 --
3232 end;
3233
3234
3235 --
3236 -- ORGANIZATION
3237 --
3238 --
3239 --To calculate the Under budgeted positions for all units of measure...
3240 --
3241 Procedure organization_analysis(
3242 errbuf out nocopy varchar2
3243 , retcode out nocopy varchar2
3244 , p_batch_name varchar2
3245 , p_effective_date varchar2
3246 , p_start_org_id number
3247 , p_org_structure_id number
3248 , p_start_date varchar2
3249 , p_end_date varchar2
3250 , p_unit_of_measure varchar2
3251 , p_business_group_id number
3252 ) is
3253 --
3254 l_proc_name varchar2(30) := 'ORGANIZATION_ANALYSIS';
3255 --
3256 l_dummy varchar2(50);
3257 l_effective_date date;
3258 l_start_date date;
3259 l_end_date date;
3260 l_start_org_id number;
3261 l_error_msg varchar2(1000);
3262 l_parameter1_value varchar2(100);
3263 l_parameter2_value varchar2(100);
3264 l_parameter3_value varchar2(100);
3265 l_parameter4_value varchar2(100);
3266 l_parameter5_value varchar2(100);
3267 l_parameter6_value varchar2(100);
3268 l_parameter7_value varchar2(100);
3269 l_parameter8_value varchar2(100);
3270 l_parameter9_value varchar2(100);
3271 --
3272 -- Cursor to Fetch the Organization Structure Version
3273 --
3274 cursor c_org_version(p_effective_date date) is
3275 select ver.org_structure_version_id
3276 from per_organization_structures str
3277 , per_org_structure_versions ver
3278 where str.position_control_structure_flg = 'Y'
3279 and str.organization_structure_id = p_org_structure_id
3280 and str.business_group_id = p_business_group_id
3281 and ver.business_group_id = p_business_group_id
3282 and str.organization_structure_id = ver.organization_structure_id
3283 and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
3284 --
3285 -- Cursor to fetch the top Org of Hierarchy
3286 --
3287 cursor c_top_org(p_org_structure_version_id number) is
3288 select organization_id_parent organization_id
3289 from per_org_structure_elements a
3290 where org_structure_version_id = p_org_structure_version_id
3291 and not exists (
3292 select organization_id_child organization_id
3293 from per_org_structure_elements b
3294 where org_structure_version_id = p_org_structure_version_id
3295 and b.organization_id_child = a.organization_id_parent
3296 )
3297 and rownum <2;
3298 --
3299 -- Cursor to Fetch the Organizations for the given Organization Hierarchy
3300 --
3301 -- Bug Fix : 2464692 : Change : added p_effective_date parameter
3302 --
3303 cursor c_org(p_org_structure_version_id number, p_start_org_id number,p_effective_date date) is
3304 select 0 rn,
3305 0 level1,
3306 organization_id
3307 from hr_all_organization_units u
3308 where organization_id = p_start_org_id
3309 and business_group_id = p_business_group_id
3310 and exists
3311 (select null from per_org_structure_elements e
3312 where e.org_structure_version_id = p_org_structure_version_id
3313 and (e.organization_id_child = p_start_org_id
3314 or e.organization_id_parent = p_start_org_id) )
3315 union
3316 select rownum rn,
3317 level level1,
3318 organization_id_child organization_id
3319 from per_org_structure_elements a
3320 start with
3321 organization_id_parent = p_start_org_id
3322 and org_structure_version_id = p_org_structure_version_id
3323 connect by
3324 organization_id_parent = prior organization_id_child
3325 and org_structure_version_id = p_org_structure_version_id;
3326 --
3327 -- Bug Fix : 2464692
3328 -- Retrives all Internal Organizations under the given business group
3329 -- as on that effective date in case of p_start_org_id and
3330 -- p_org_structure_id are null
3331 --
3332 cursor c_all_org(p_business_group_id number, p_effective_date date) is
3333 select rownum rn,
3334 0 level1,
3335 organization_id
3336 from hr_all_organization_units
3337 where business_group_id = p_business_group_id
3338 and INTERNAL_EXTERNAL_FLAG ='INT'
3339 and p_effective_date between date_from and nvl(date_to, hr_general.end_of_time);
3340 --
3341 -- Cursor that checks the batch existance
3342 --
3343 cursor check_batch_name(p_batch_name varchar2) is
3344 select 'x'
3345 from pqh_process_log
3346 where log_context=p_batch_name;
3347
3348 --
3349 -- Cursor to get the next batch Id for the Process Log
3350 --
3351 cursor c_batch is
3352 select
3353 pqh_process_log_s.nextval
3354 from
3355 dual;
3356 --
3357 -- Cursor to select workflow sequence no
3358 --
3359 cursor c_wf_seq_no is
3360 select pqh_wf_notifications_s.nextval
3361 from dual;
3362 --
3363 -- Local Variables
3364 --
3365 l_org_structure_version_id number;
3366 --
3367 l_batch_id number;
3368 --
3369 l_workflow_seq_no number;
3370 l_apply_error_mesg varchar2(100);
3371 l_apply_error_num varchar2(100);
3372 --
3373 begin
3374 --
3375 hr_utility.set_location('Entering'|| l_proc_name, 10);
3376 retcode := 0;
3377 --
3378 hr_utility.set_location('Entering'|| l_proc_name, 11);
3379 --
3380 open check_batch_name(p_batch_name);
3381 fetch check_batch_name into l_dummy;
3382 if check_batch_name%found then
3383 retcode := -1;
3384 fnd_message.set_name('PQH', 'PQH_PLG_DUP_BATCH');
3385 fnd_message.set_token('BATCH_NAME', p_batch_name);
3386 errbuf := fnd_message.get;
3387 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
3388 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
3389 return;
3390 end if;
3391 --
3392 hr_utility.set_location('Entering'|| l_proc_name, 13);
3393 --
3394 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
3395 l_start_date := fnd_date.canonical_to_date(p_start_date);
3396 l_end_date := fnd_date.canonical_to_date(p_end_date);
3397 --
3398 -- Fetch the Organization Structure Version
3399 --
3400 open c_org_version(l_effective_date);
3401 fetch c_org_version into l_org_structure_version_id;
3402 close c_org_version;
3403 --
3404 --
3405 hr_utility.set_location('Entering'|| l_proc_name, 14);
3406 --
3407 --
3408 -- Fetch the batch Id into the l_batch_id
3409 --
3410 open c_batch;
3411 fetch c_batch into l_batch_id;
3412 close c_batch;
3413 --
3414 hr_utility.set_location('l_batch_id : '||l_batch_id ||' - ' || l_proc_name, 15);
3415 --
3416 -- Create the start record into the Process Log
3417 --
3418 pqh_process_batch_log.start_log
3419 (
3420 p_batch_id =>l_batch_id,
3421 p_module_cd =>'POSITION_BUDGET_ANALYSIS',
3422 p_log_context =>p_batch_name,
3423 p_information3 =>p_effective_date,
3424 p_information4 =>p_start_org_id,
3425 p_information5 =>p_start_date,
3426 p_information6 =>p_end_date,
3427 p_information7 =>p_org_structure_id,
3428 p_information8 =>'ORGANIZATION',
3429 p_information9 =>p_unit_of_measure
3430 );
3431 --
3432 --
3433 hr_utility.set_location('Organization Structure Version : '||l_org_structure_version_id ||' '|| l_proc_name, 100);
3434 hr_utility.set_location('start organization : '||p_start_org_id ||' '|| l_proc_name, 100);
3435 --
3436 --
3437 fnd_file.put_line(FND_FILE.LOG,'Primary Entity => ''ORGANIZATION''' || ' Unit of Measure => '||p_unit_of_measure);
3438 fnd_file.put_line(FND_FILE.LOG,' ');
3439 fnd_file.put_line(FND_FILE.LOG,'Name Budgeted Value Reallocated Value Actual Value Commitment Value Under Budgeted Value Under Budgeted Date');
3440 fnd_file.put_line(FND_FILE.LOG,' ');
3441
3442 if (l_org_structure_version_id is not null ) then
3443 l_start_org_id := p_start_org_id;
3444 -- Bug Fix :2481824 ,get Top Org in Hierarchy as p_start_org_id
3445 if (p_start_org_id is null) then
3446 open c_top_org(l_org_structure_version_id);
3447 fetch c_top_org into l_start_org_id;
3448 close c_top_org;
3449 end if;
3450 --
3451 if l_start_org_id is not null then
3452 --
3453 -- Fetch the Organizations from the Organization Hierarchy
3454 for l_organization in c_org(l_org_structure_version_id, l_start_org_id,l_effective_date)
3455 loop
3456 --
3457 hr_utility.set_location('organization : '||l_organization.organization_id ||' '|| l_proc_name, 101);
3458 --
3459 org_temp( p_organization_id => l_organization.organization_id
3460 ,p_level1 => l_organization.level1
3461 ,p_batch_name => p_batch_name
3462 ,p_unit_of_measure => p_unit_of_measure
3463 ,p_business_group_id=> p_business_group_id
3464 ,p_effective_date => l_effective_date
3465 ,p_start_date => l_start_date
3466 ,p_end_date => l_end_date
3467 );
3468 end loop;
3469 --
3470 end if;
3471 elsif (p_start_org_id is null)
3472 then
3473 for l_organization in c_all_org(p_business_group_id, l_effective_date)
3474 loop
3475 --
3476 hr_utility.set_location('organization : '||l_organization.organization_id ||' '|| l_proc_name, 101);
3477 --
3478 org_temp( p_organization_id => l_organization.organization_id
3479 ,p_level1 => l_organization.level1
3480 ,p_batch_name => p_batch_name
3481 ,p_unit_of_measure => p_unit_of_measure
3482 ,p_business_group_id=> p_business_group_id
3483 ,p_effective_date => l_effective_date
3484 ,p_start_date => l_start_date
3485 ,p_end_date => l_end_date
3486 );
3487 end loop;
3488 end if;
3489 --
3490 -- End the Process Log
3491 --
3492 pqh_process_batch_log.end_log;
3493 hr_utility.set_location('End Process'
3494 ||' '||l_proc_name, 180);
3495 commit;
3496 exception
3497 when others then
3498 retcode := -1;
3499 --
3500 end;
3501 --
3502 --
3503 ---*******************-----
3504 --Procedure to calculate Under Budgeted values for all primary entities
3505
3506 Procedure get_entity(errbuf OUT nocopy varchar2
3507 , retcode OUT nocopy varchar2
3508 , p_batch_name IN varchar2
3509 , p_effective_date IN varchar2
3510 , p_start_date IN varchar2
3511 , p_end_date IN varchar2
3512 , p_entity_code IN varchar2
3513 , p_unit_of_measure IN varchar2
3514 , p_business_group_id IN number
3515 , p_start_org_id IN number default null
3516 , p_org_structure_id IN number default null
3517 ) Is
3518 Begin
3519 --fnd_file.put_line(FND_FILE.LOG,'p_batch_name '||p_batch_name||'-')
3520
3521 --
3522 -- Bug fix : 2483240
3523 --
3524 /* Commented for Bug Fix : 2464692
3525
3526 If p_entity_code in ('POSITION','ORGANIZATION','ALL') then
3527 --
3528 If (nvl(p_start_org_id,0) =0 or nvl(p_org_structure_id,0)=0 )
3529 then
3530 --
3531 fnd_message.set_name('PQH', 'PQH_ENTITY_REQUIRED');
3532
3533
3534 If p_entity_code ='POSITION' then
3535 --
3536 fnd_message.set_token('ENTITY_NAME',hr_general.decode_lookup('PQH_BUDGET_ENTITY',p_entity_code)||'s');
3537 --
3538 Elsif p_entity_code ='ORGANIZATION' then
3539 --
3540 fnd_message.set_token('ENTITY_NAME',hr_general.decode_lookup('PQH_BUDGET_ENTITY',p_entity_code)||'s');
3541 --
3542 Else
3543 fnd_message.set_token('ENTITY_NAME',hr_general.decode_lookup('UNDER_BDGT_EXTRA_TYPES',p_entity_code));
3544 End if;
3545 --
3546 errbuf := fnd_message.get;
3547 retcode := -1;
3548 fnd_file.put_line(FND_FILE.LOG,errbuf);
3549 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, errbuf);
3550 return;
3551 End if;
3552 --
3553 End if;
3554 */
3555
3556 If p_entity_code = 'POSITION' then
3557 position_analysis(p_batch_name => p_batch_name
3558 , p_effective_date => p_effective_date
3559 , p_start_org_id => p_start_org_id
3560 , p_org_structure_id => p_org_structure_id
3561 , p_start_date => p_start_date
3562 , p_end_date => p_end_date
3563 , p_business_group_id => p_business_group_id
3564 , p_unit_of_measure => p_unit_of_measure
3565 , errbuf => errbuf
3566 , retcode => retcode
3567 );
3568 Elsif p_entity_code = 'JOB' then
3569 job_analysis(p_batch_name => p_batch_name
3570 , p_effective_date => p_effective_date
3571 , p_start_date => p_start_date
3572 , p_end_date => p_end_date
3573 , p_business_group_id => p_business_group_id
3574 , p_unit_of_measure => p_unit_of_measure
3575 , errbuf => errbuf
3576 , retcode => retcode
3577 );
3578
3579 Elsif p_entity_code = 'GRADE' then
3580 grade_analysis(p_batch_name => p_batch_name
3581 , p_effective_date => p_effective_date
3582 , p_start_date => p_start_date
3583 , p_end_date => p_end_date
3584 , p_business_group_id => p_business_group_id
3585 , p_unit_of_measure => p_unit_of_measure
3586 , errbuf => errbuf
3587 , retcode => retcode
3588 );
3589 Elsif p_entity_code = 'ORGANIZATION' then
3590 organization_analysis(p_batch_name => p_batch_name
3591 , p_effective_date => p_effective_date
3592 , p_start_org_id => p_start_org_id
3593 , p_org_structure_id => p_org_structure_id
3594 , p_start_date => p_start_date
3595 , p_end_date => p_end_date
3596 , p_business_group_id => p_business_group_id
3597 , p_unit_of_measure => p_unit_of_measure
3598 , errbuf => errbuf
3599 , retcode => retcode
3600 );
3601 Elsif p_entity_code = 'OPEN' then
3602 --
3603
3604 fnd_file.put_line(FND_FILE.LOG,'Budgets cannot be controlled with Primary Entity OPEN');
3605
3606 --
3607 Elsif nvl(p_entity_code,'ALL') = 'ALL' then
3608 --batch name will be appended by the entity code
3609
3610 position_analysis(p_batch_name => p_batch_name || ' - Position'
3611 , p_effective_date => p_effective_date
3612 , p_start_org_id => p_start_org_id
3613 , p_org_structure_id => p_org_structure_id
3614 , p_start_date => p_start_date
3615 , p_end_date => p_end_date
3616 , p_business_group_id => p_business_group_id
3617 , p_unit_of_measure => p_unit_of_measure
3618 , errbuf => errbuf
3619 , retcode => retcode
3620 );
3621
3622 organization_analysis
3623 (p_batch_name => p_batch_name ||' - Organization'
3624 , p_effective_date => p_effective_date
3625 , p_start_org_id => p_start_org_id
3626 , p_org_structure_id => p_org_structure_id
3627 , p_start_date => p_start_date
3628 , p_end_date => p_end_date
3629 , p_business_group_id => p_business_group_id
3630 , p_unit_of_measure => p_unit_of_measure
3631 , errbuf => errbuf
3632 , retcode => retcode
3633 );
3634
3635
3636 job_analysis(p_batch_name => p_batch_name ||' - Job'
3637 , p_effective_date => p_effective_date
3638 , p_start_date => p_start_date
3639 , p_end_date => p_end_date
3640 , p_business_group_id => p_business_group_id
3641 , p_unit_of_measure => p_unit_of_measure
3642 , errbuf => errbuf
3643 , retcode => retcode
3644 );
3645
3646 grade_analysis(p_batch_name => p_batch_name || ' - Grade'
3647 , p_effective_date => p_effective_date
3648 , p_start_date => p_start_date
3649 , p_end_date => p_end_date
3650 , p_business_group_id => p_business_group_id
3651 , p_unit_of_measure => p_unit_of_measure
3652 , errbuf => errbuf
3653 , retcode => retcode
3654 );
3655 End if;
3656 --Exception section added as part of nocopy changes
3657 Exception
3658 When Others Then
3659 retcode := -1;
3660
3661 End;
3662
3663 function get_budget_currency( p_position_id in number default null
3664 ,p_job_id in number default null
3665 ,p_grade_id in number default null
3666 ,p_organization_id in number default null
3667 ,p_budget_entity in varchar2
3668 ,p_start_date in date default sysdate
3669 ,p_end_date in date default sysdate
3670 ,p_effective_date in date default sysdate
3671 ,p_business_group_id in number
3672 ) return varchar2 is
3673 --
3674 --
3675 --
3676 -- Cursor to fetch the Budgeted Currency on the given dates
3677 --
3678 cursor c_currency is
3679 select bud.currency_code
3680 from
3681 pqh_budgets bud,
3682 pqh_budget_versions bver,
3683 pqh_budget_details bdet,
3684 pqh_budget_periods bper,
3685 per_time_periods stp,
3686 per_time_periods etp,
3687 pqh_budget_sets bsets,
3688 pqh_budget_elements bele,
3689 pqh_bdgt_cmmtmnt_elmnts bcl
3690 where nvl(bud.position_control_flag,'X') = 'Y'
3691 and bud.budgeted_entity_cd = p_budget_entity
3692 and bud.business_group_id = p_business_group_id
3693 and ((p_start_date <= bud.budget_start_date
3694 and p_end_date >= bud.budget_end_date)
3695 or
3696 (p_start_date between bud.budget_start_date and bud.budget_end_date) or
3697 (p_end_date between bud.budget_start_date and bud.budget_end_date)
3698 )
3699 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
3700 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
3701 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
3702 )
3703 and bud.budget_id = bver.budget_id
3704 and trunc(p_effective_date) between trunc(bver.date_from) and trunc(bver.date_to)
3705 and nvl(p_organization_id, nvl(bdet.organization_id, -1)) =
3706 nvl(bdet.organization_id, -1)
3707 and nvl(p_job_id, nvl(bdet.job_id, -1)) =
3708 nvl(bdet.job_id, -1)
3709 and nvl(p_position_id, nvl(bdet.position_id, -1)) =
3710 nvl(bdet.position_id, -1)
3711 and nvl(p_grade_id, nvl(bdet.grade_id, -1)) =
3712 nvl(bdet.grade_id, -1)
3713 and bver.budget_version_id = bdet.budget_version_id
3714 and bper.budget_detail_id = bdet.budget_detail_id
3715 and bper.start_time_period_id = stp.time_period_id
3716 and bper.end_time_period_id = etp.time_period_id
3717 and etp.end_date >= p_start_date
3718 and stp.start_date <= p_end_date
3719 and bsets.budget_period_id = bper.budget_period_id
3720 and bele.budget_set_id = bsets.budget_set_id
3721 and bud.budget_id = bcl.budget_id
3722 and bele.element_type_id = bcl.element_type_id;
3723
3724 cursor c_currency_code is
3725 select currency_code
3726 from per_business_groups
3727 where business_group_id = p_business_group_id;
3728 --
3729 --
3730 -- Local Variables
3731 --
3732 l_currency_code varchar2(40);
3733 --
3734 begin
3735 --
3736 --
3737 for l_currency in c_currency
3738 loop
3739 /* open c_currency;
3740 fetch c_currency into l_currency_code;
3741 close c_currency; */
3742 l_currency_code := l_currency.currency_code;
3743
3744 end loop;
3745
3746
3747 --
3748 if l_currency_code is null then
3749 open c_currency_code;
3750 fetch c_currency_code into l_currency_code;
3751 close c_currency_code;
3752 end if;
3753 --
3754 -- Return the currency code
3755 --
3756 return(l_currency_code);
3757 --
3758 end;
3759
3760
3761 End;