1 Package body pqh_commitment_pkg as
2 /* $Header: pqbgtcom.pkb 120.11 2007/01/05 17:47:55 krajarat noship $ */
3 --
4 --
5 g_package varchar2(33) := 'pqh_commitment_pkg.';
6 --
7 type cmmtmnt_dt_rec is record (cmmtmnt_start_dt date,
8 actual_cmmtmnt_start_dt date,
9 cmmtmnt_end_dt date,
10 actual_cmmtmnt_end_dt date);
11
12 type cmmtmnt_dt_tab is table of cmmtmnt_dt_rec index by binary_integer;
13 --
14 type cmmtmnt_elmnts_rec is record (
15 Element_type_id pqh_bdgt_cmmtmnt_elmnts.element_type_id%type,
16 Formula_id pqh_bdgt_cmmtmnt_elmnts.formula_id%type,
17 Salary_basis_flag pqh_bdgt_cmmtmnt_elmnts.salary_basis_flag%type,
18 Element_input_value_id pqh_bdgt_cmmtmnt_elmnts.element_input_value_id%type,
19 dflt_elmnt_frequency pqh_bdgt_cmmtmnt_elmnts.dflt_elmnt_frequency%type,
20 overhead_percentage pqh_bdgt_cmmtmnt_elmnts.overhead_percentage%type);
21 --
22 type cmmtmnt_elmnts_tab is table of cmmtmnt_elmnts_rec index by binary_integer;
23 --
24 type bdgt_posn_rec is record(position_id pqh_budget_details.position_id%type);
25 type bdgt_orgn_rec is record(organization_id pqh_budget_details.organization_id%type);
26 type bdgt_job_rec is record(job_id pqh_budget_details.job_id%type);
27 type bdgt_grade_rec is record(grade_id pqh_budget_details.grade_id%type);
28 type bdgt_entity_rec is record(entity_id pqh_budget_details.position_id%type);
29
30 --
31 type bdgt_posn_tab is table of bdgt_posn_rec index by binary_integer;
32 type bdgt_orgn_tab is table of bdgt_orgn_rec index by binary_integer;
33 type bdgt_job_tab is table of bdgt_job_rec index by binary_integer;
34 type bdgt_grade_tab is table of bdgt_grade_rec index by binary_integer;
35
36 type bdgt_entity_tab is table of bdgt_entity_rec index by binary_integer;
37 --
38 g_budget_detail_status varchar2(30) := NULL;
39 g_budget_version_status varchar2(30) := NULL;
40 --
41 g_cmmtmnt_calc_dates cmmtmnt_dt_tab ;
42 g_bdgt_cmmtmnt_elmnts cmmtmnt_elmnts_tab;
43 g_budget_positions bdgt_posn_tab;
44 g_budget_orgs bdgt_orgn_tab;
45 g_budget_jobs bdgt_job_tab;
46 g_budget_grades bdgt_grade_tab;
47
48 g_budget_entities bdgt_entity_tab;
49 --
50 g_table_route_id_p_bgt number;
51 g_table_route_id_p_bdt number;
52 --
53 WEEKLY CONSTANT varchar2(1) := 'W';
54 MONTHLY CONSTANT varchar2(1) := 'M';
55 SEMIMONTHLY CONSTANT varchar2(1) := 'S';
56 --
57 ----------------------------------------------------------------------------
58 -- The Following section is specification for locally called procedures
59 -- functions.
60 ----------------------------------------------------------------------------
61 --
62 PROCEDURE calculate_money_cmmtmnts(p_budgeted_entity_cd in varchar2,
63 p_budget_version_id in number,
64 p_entity_id in number,
65 p_period_frequency in varchar2 default null);
66 --
67 PROCEDURE Validate_budget(p_budgeted_entity_cd in varchar2,
68 p_budget_version_id in number,
69 p_budget_id out nocopy number,
70 p_budget_name out nocopy varchar2,
71 p_period_set_name out nocopy varchar2,
72 p_bdgt_cal_frequency out nocopy varchar2,
73 p_budget_start_date out nocopy date,
74 p_budget_end_date out nocopy date);
75 --
76 PROCEDURE Validate_entity (p_budgeted_entity_cd in varchar2,
77 p_entity_id in number,
78 p_budget_version_id in number,
79 p_entity_name out nocopy varchar2);
80 --
81 PROCEDURE Validate_commitment_dates(p_period_frequency in varchar2,
82 p_cmmtmnt_start_dt in date,
83 p_cmmtmnt_end_dt in date,
84 p_budget_cal_freq in varchar2,
85 p_period_set_name in varchar2,
86 p_budget_start_date in date,
87 p_budget_end_date in date);
88 --
89 PROCEDURE get_table_route;
90 --
91 PROCEDURE fetch_bdgt_cmmtmnt_elmnts(p_budget_id in number,
92 p_bdgt_cmmtmnt_elmnts out nocopy cmmtmnt_elmnts_tab);
93 --
94 FUNCTION check_non_susp_assignment(p_commit_calculation_dt in date,
95 p_assignment_id in number )
96 RETURN BOOLEAN;
97 --
98 FUNCTION populate_commitment_table(p_budgeted_entity_cd in varchar2,
99 p_commit_calculation_dt in date,
100 p_actual_cmmtmnt_start_dt in date,
101 p_commit_end_dt in date,
102 p_actual_cmmtmnt_end_dt in date,
103 p_commitment_calc_frequency in varchar2,
104 p_budget_calendar_frequency in varchar2,
105 p_entity_id in number,
106 p_budget_id in number,
107 p_budget_version_id in number,
108 p_assignment_id in number default null)
109 RETURN NUMBER;
110 --
111 FUNCTION get_commitment_from_elmnt_type(p_commit_calculation_dt in date,
112 p_actual_cmmtmnt_start_dt in date,
113 p_commit_calculation_end_dt in date,
114 p_actual_cmmtmnt_end_dt in date,
115 p_commitment_calc_frequency in varchar2,
116 p_budget_calendar_frequency in varchar2,
117 p_dflt_elmnt_frequency in varchar2,
118 p_business_group_id in number,
119 p_assignment_id in number,
120 p_payroll_id in number,
121 p_pay_basis_id in number,
122 p_element_type_id in number,
123 p_element_input_value_id in number,
124 p_normal_hours in number,
125 p_asst_frequency in varchar2)
126 RETURN NUMBER ;
127 --
128 FUNCTION get_commitment_from_sal_basis(p_commit_calculation_dt in date,
129 p_actual_cmmtmnt_start_dt in date,
130 p_commit_calculation_end_dt in date,
131 p_actual_cmmtmnt_end_dt in date,
132 p_commitment_calc_frequency in varchar2,
133 p_element_type_id in number,
134 p_budget_calendar_frequency in varchar2,
135 p_dflt_elmnt_frequency in varchar2,
136 p_business_group_id in number,
137 p_assignment_id in number,
138 p_payroll_id in number,
139 p_pay_basis_id in number,
140 p_normal_hours in number,
141 p_asst_frequency in varchar2)
142 RETURN NUMBER;
143 --
144 FUNCTION get_payroll_period_type(p_payroll_id in number,
145 p_effective_dt in date)
146 RETURN varchar2 ;
147 --
148 FUNCTION Convert_Period_Type(p_bus_grp_id in NUMBER,
149 p_payroll_id in NUMBER,
150 p_asst_std_hours in NUMBER default NULL,
151 p_figure in NUMBER,
152 p_from_freq in VARCHAR2,
153 p_to_freq in VARCHAR2,
154 p_period_start_date in DATE default NULL,
155 p_period_end_date in DATE default NULL,
156 p_asst_std_freq in VARCHAR2 default NULL,
157 p_dflt_elmnt_frequency in VARCHAR2,
158 p_budget_calendar_frequency in VARCHAR2)
159 RETURN NUMBER ;
160 --
161 --------------------------------------------------------------------------
162 --
163 PROCEDURE Validate_budget(p_budgeted_entity_cd in varchar2,
164 p_budget_version_id in number,
165 p_budget_id out nocopy number,
166 p_budget_name out nocopy varchar2,
167 p_period_set_name out nocopy varchar2,
168 p_bdgt_cal_frequency out nocopy varchar2,
169 p_budget_start_date out nocopy date,
170 p_budget_end_date out nocopy date)
171 is
172 --
173 Cursor csr_bdgt is
174 Select bgt.budget_id,budget_name,period_set_name ,budgeted_entity_cd,
175 budget_start_date,budget_end_date
176 From pqh_budgets bgt
177 Where bgt.budget_id in (Select bvr.budget_id
178 From pqh_budget_versions bvr
179 Where bvr.budget_version_id = p_budget_version_id);
180 --
181 -- Obtain the frequency of the time periods for a calendar
182 --
183 Cursor csr_bdgt_cal_freq is
184 Select pc.actual_period_type
185 from pay_calendars pc
186 Where pc.period_set_name = p_period_set_name;
187 --
188 l_proc varchar2(72) := g_package || 'Validate_budget';
189 l_budgeted_entity_cd varchar2(30) := null;
190 --
191 Begin
192 --
193 hr_utility.set_location('Entering :'||l_proc,5);
194 --
195 -- VALIDATE IF THIS IS A VALID BUDGET IN PQH_BUDGETS
196 --
197 Open csr_bdgt;
198 --
199 Fetch csr_bdgt into p_budget_id,p_budget_name,p_period_set_name,l_budgeted_entity_cd,
200 p_budget_start_date,p_budget_end_date;
201 --
202 If csr_bdgt%notfound then
203 --
204 --Raise exception
205 --
206 Close csr_bdgt;
207 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BUDGET_VERSION');
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 --
210 Else
211 --
212 -- DETERMINE WHETHER SELETED P_BUDGETED_ENTITY_CD , L_BUDGETED_ENTITY_CD ARE SAME OR NOT.
213 --
214 If l_budgeted_entity_cd <> p_budgeted_entity_cd then
215 --
216 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BUDGET');
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 --
219 End if;
220 --
221 End if;
222 --
223 --
224
225 Close csr_bdgt;
226 --
227 -- DETERMINE THE CALENDAR FREQ OF THE BUDGET
228 --
229 Open csr_bdgt_cal_freq;
230 --
231 Fetch csr_bdgt_cal_freq into p_bdgt_cal_frequency;
232 --
233 If csr_bdgt_cal_freq%notfound then
234 --
235 --Raise exception
236 --
237 Close csr_bdgt_cal_freq;
238 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BDGT_CALENDAR');
239 APP_EXCEPTION.RAISE_EXCEPTION;
240 --
241 End if;
242 --
243 Close csr_bdgt_cal_freq;
244 --
245 hr_utility.set_location('Leaving :'||l_proc,10);
246 --
247 EXCEPTION
248 WHEN OTHERS THEN
249 p_budget_id := null;
250 p_budget_name := null;
251 p_period_set_name := null;
252 p_bdgt_cal_frequency := null;
253 p_budget_start_date := null;
254 p_budget_end_date := null;
255 raise;
256 --
257 End Validate_budget;
258 --
259 ----------------------------------------------------------------------------
260 PROCEDURE Validate_entity( p_budgeted_entity_cd in varchar2,
261 p_entity_id in number,
262 p_budget_version_id in number,
263 p_entity_name out nocopy varchar2)
264 is
265 --
266 Cursor csr_pos(l_position_id in number) is
267 Select name
268 From hr_all_positions_f_tl
269 Where position_id = l_position_id
270 and language = userenv('LANG');
271 --
272 Cursor csr_org(l_org_id in number) is
273 Select name
274 From hr_all_organization_units -- Bug 2471864
275 Where organization_id = l_org_id;
276 --
277 Cursor csr_job(l_job_id in number) is
278 Select name
279 From per_jobs_vl
280 Where job_id = l_job_id;
281 --
282 Cursor csr_grade(l_grade_id in number) is
283 Select name
284 From per_grades_vl
285 Where grade_id = l_grade_id;
286
287 --
288 -- The foll cursor checks if the passed position is present in the passed
289 -- budget version.
290 --
291 Cursor csr_positions_in_bdgt(l_position_id in number) is
292 Select Position_id
293 From pqh_budget_details bdt,pqh_budget_versions bvr
294 Where bvr.budget_version_id = p_budget_version_id
295 AND bvr.budget_version_id = bdt.budget_version_id
296 AND bdt.position_id IS NOT NULL
297 AND (bdt.position_id = l_position_id or l_position_id IS NULL);
298 --
299 Cursor csr_orgs_in_bdgt(l_organization_id in number) is
300 Select Organization_id
301 From pqh_budget_details bdt,pqh_budget_versions bvr
302 Where bvr.budget_version_id = p_budget_version_id
303 AND bvr.budget_version_id = bdt.budget_version_id
304 AND bdt.organization_id IS NOT NULL
305 AND (bdt.organization_id = l_organization_id or l_organization_id IS NULL);
306 --
307 Cursor csr_jobs_in_bdgt(l_job_id in number) is
308 Select Job_id
309 From pqh_budget_details bdt,pqh_budget_versions bvr
310 Where bvr.budget_version_id = p_budget_version_id
311 AND bvr.budget_version_id = bdt.budget_version_id
312 AND bdt.job_id IS NOT NULL
313 AND (bdt.job_id = l_job_id or l_job_id IS NULL);
314 --
315 Cursor csr_grades_in_bdgt(l_grade_id in number) is
316 Select Grade_id
317 From pqh_budget_details bdt,pqh_budget_versions bvr
318 Where bvr.budget_version_id = p_budget_version_id
319 AND bvr.budget_version_id = bdt.budget_version_id
320 AND bdt.grade_id IS NOT NULL
321 AND (bdt.grade_id = l_grade_id or l_grade_id IS NULL);
322 --
323
324 rec_no number(15) := 1;
325 l_dummy_tab bdgt_posn_tab;
326 l_dummy_tab_org bdgt_orgn_tab;
327 l_dummy_tab_job bdgt_job_tab;
328 l_dummy_tab_grade bdgt_grade_tab;
329 l_dummy_tab_entity bdgt_entity_tab;
330 --
331 l_proc varchar2(72) := g_package || 'Validate_entity';
332 --
333 Begin
334 --
335 hr_utility.set_location('Entering :'||l_proc,5);
336 hr_utility.set_location('p_budgeted_entity_cd'||p_budgeted_entity_cd||'p_entity_id'||p_entity_id,5);
337
338 --
339
340 If p_budgeted_entity_cd ='POSITION' then
341
342 If p_entity_id IS NOT NULL then
343 --
344 -- VALIDATE IF THIS IS A VALID POSITION IN HR_ALL_POSITIONS_F
345 --
346 Open csr_pos(p_entity_id);
347 --
348 Fetch csr_pos into p_entity_name;
349 --
350 If csr_pos%notfound then
351 --
352 --Raise exception
353 --
354 Close csr_pos;
355 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY');
356 APP_EXCEPTION.RAISE_EXCEPTION;
357 --
358 End if;
359 --
360 Close csr_pos;
361 --
362 End if; -- p_entity_id is not null
363 --
364 -- DETERMINE IF THE POSITION BELONGS TO THE BUDGET VERSION
365 --
366 -- g_budget_positions := l_dummy_tab;
367 g_budget_entities := l_dummy_tab_entity;
368 --
369 Open csr_positions_in_bdgt(p_entity_id);
370 --
371 --
372 Loop
373 --
374 Fetch csr_positions_in_bdgt into g_budget_entities(rec_no).entity_id;
375 --
376 hr_utility.set_location(' Fetch Position:'||l_proc,10);
377 --
378 If csr_positions_in_bdgt%notfound then
379 --
380 Exit;
381 --
382 End if;
383 rec_no := rec_no + 1;
384 --
385 End loop;
386 --
387 Close csr_positions_in_bdgt;
388 --
389 rec_no := rec_no - 1;
390 --
391 hr_utility.set_location(' Check rowcount :'||l_proc,15);
392 --
393 If rec_no = 0 then
394 --
395 If p_entity_id IS NULL then
396 --
397 FND_MESSAGE.SET_NAME('PQH','PQH_NO_ENTITIES_IN_BDGT_VER');
398 APP_EXCEPTION.RAISE_EXCEPTION;
399 --
400 Else
401 --
402 FND_MESSAGE.SET_NAME('PQH','PQH_ENTITY_NOT_IN_BDGT_VER');
403 APP_EXCEPTION.RAISE_EXCEPTION;
404 --
405 End if;
406 --
407 End If;
408 --
409 --
410 End if; -- p_budgeted_entity_cd='POSITION'
411 --
412 If p_budgeted_entity_cd ='ORGANIZATION' then
413
414 If p_entity_id IS NOT NULL then
415 --
416 -- VALIDATE IF THIS IS A VALID ORGANIZATION IN HR_ALL_ORGANIZATION_UNITS
417 --
418 Open csr_org(p_entity_id);
419 --
420 Fetch csr_org into p_entity_name;
421 --
422 If csr_org%notfound then
423 --
424 --Raise exception
425 --
426 Close csr_org;
427 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY');
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 --
430 End if;
431 --
432 Close csr_org;
433 Null;
434 --
435 End if; -- p_entity_id is not null
436 --
437 -- DETERMINE IF THE ORGANIZATION BELONGS TO THE BUDGET VERSION
438 --
439 --g_budget_orgs := l_dummy_tab_org;
440 g_budget_entities := l_dummy_tab_entity;
441 --
442 Open csr_orgs_in_bdgt(p_entity_id);
443 --
444 --
445 Loop
446 --
447 Fetch csr_orgs_in_bdgt into g_budget_entities(rec_no).entity_id;
448 --
449 hr_utility.set_location(' Fetch Position:'||l_proc,10);
450 --
451 If csr_orgs_in_bdgt%notfound then
452 --
453 Exit;
454 --
455 End if;
456 rec_no := rec_no + 1;
457 --
458 End loop;
459 --
460 Close csr_orgs_in_bdgt;
461 --
462 rec_no := rec_no - 1;
463 --
464 hr_utility.set_location(' Check rowcount :'||l_proc,15);
465 --
466 If rec_no = 0 then
467 --
468 If p_entity_id IS NULL then
469 --
470 FND_MESSAGE.SET_NAME('PQH','PQH_NO_ENTITIES_IN_BDGT_VER');
471 APP_EXCEPTION.RAISE_EXCEPTION;
472 --
473 Else
474 --
475 FND_MESSAGE.SET_NAME('PQH','PQH_ENTITY_NOT_IN_BDGT_VER');
476 APP_EXCEPTION.RAISE_EXCEPTION;
477 --
478 End if;
479 --
480 End If;
481 --
482 --
483 End if; -- p_budgeted_entity_cd='ORGANIZATION'
484 --
485 If p_budgeted_entity_cd ='JOB' then
486
487 hr_utility.set_location(' Job Strated...:'||l_proc,10);
488
489 If p_entity_id IS NOT NULL then
490 --
491 -- VALIDATE IF THIS IS A VALID JOB IN PER_JOBS
492 --
493 Open csr_job(p_entity_id);
494 --
495 Fetch csr_job into p_entity_name;
496 hr_utility.set_location(' Job Name :'||p_entity_name,10);
497 --
498 If csr_job%notfound then
499 --
500 --Raise exception
501 --
502 Close csr_job;
503 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY');
504 APP_EXCEPTION.RAISE_EXCEPTION;
505 --
506 End if;
507 --
508 Close csr_job;
509 hr_utility.set_location(' exited out nocopy .... csr_job :'||p_entity_name,10);
510 --
511 End if; -- p_entity_id is not null
512 --
513 -- DETERMINE IF THE JOB BELONGS TO THE BUDGET VERSION
514 --
515 -- g_budget_jobs := l_dummy_tab_job;
516 g_budget_entities := l_dummy_tab_entity;
517 --
518 Open csr_jobs_in_bdgt(p_entity_id);
519 --
520 --
521 Loop
522 --
523 Fetch csr_jobs_in_bdgt into g_budget_entities(rec_no).entity_id;
524 --
525 hr_utility.set_location(' Fetch Position:'||l_proc,10);
526 --
527 If csr_jobs_in_bdgt%notfound then
528 --
529 Exit;
530 --
531 End if;
532 rec_no := rec_no + 1;
533 --
534 End loop;
535 --
536 Close csr_jobs_in_bdgt;
537 --
538 rec_no := rec_no - 1;
539 --
540 hr_utility.set_location(' Check rowcount :'||l_proc,15);
541 --
542 If rec_no = 0 then
543 --
544 If p_entity_id IS NULL then
545 --
546 FND_MESSAGE.SET_NAME('PQH','PQH_NO_ENTITIES_IN_BDGT_VER');
547 APP_EXCEPTION.RAISE_EXCEPTION;
548 --
549 Else
550 --
551 FND_MESSAGE.SET_NAME('PQH','PQH_ENTITY_NOT_IN_BDGT_VER');
552 APP_EXCEPTION.RAISE_EXCEPTION;
553 --
554 End if;
555 --
556 End If;
557 --
558 --
559 End if; -- p_budgeted_entity_cd='JOB'
560
561 --
562 If p_budgeted_entity_cd ='GRADE' then
563
564 If p_entity_id IS NOT NULL then
565 --
566 -- VALIDATE IF THIS IS A VALID GRADE IN PER_GRADES
567 --
568 Open csr_grade(p_entity_id);
569 --
570 Fetch csr_grade into p_entity_name;
571 --
572 If csr_grade%notfound then
573 --
574 --Raise exception
575 --
576 Close csr_grade;
577 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_ENTITY');
578 APP_EXCEPTION.RAISE_EXCEPTION;
579 --
580 End if;
581 --
582 Close csr_grade;
583 Null;
584 --
585 End if; -- p_entity_id is not null
586 --
587 -- DETERMINE IF THE GRADE BELONGS TO THE BUDGET VERSION
588 --
589 --g_budget_grades := l_dummy_tab_grade;
590 g_budget_entities := l_dummy_tab_entity;
591 --
592 Open csr_grades_in_bdgt(p_entity_id);
593 --
594 --
595 Loop
596 --
597 Fetch csr_grades_in_bdgt into g_budget_entities(rec_no).entity_id;
598 --
599 hr_utility.set_location(' Fetch Position:'||l_proc,10);
600 --
601 If csr_grades_in_bdgt%notfound then
602 --
603 Exit;
604 --
605 End if;
606 rec_no := rec_no + 1;
607 --
608 End loop;
609 --
610 Close csr_grades_in_bdgt;
611 --
612 rec_no := rec_no - 1;
613 --
614 hr_utility.set_location(' Check rowcount :'||l_proc,15);
615 --
616 If rec_no = 0 then
617 --
618 If p_entity_id IS NULL then
619 --
620 FND_MESSAGE.SET_NAME('PQH','PQH_NO_ENTITIES_IN_BDGT_VER');
621 APP_EXCEPTION.RAISE_EXCEPTION;
622 --
623 Else
624 --
625 FND_MESSAGE.SET_NAME('PQH','PQH_ENTITY_NOT_IN_BDGT_VER');
626 APP_EXCEPTION.RAISE_EXCEPTION;
627 --
628 End if;
629 --
630 End If;
631 --
632 --
633 End if; -- p_budgeted_entity_cd='GRADE'
634 --
635 hr_utility.set_location('Leaving :'||l_proc,20);
636 --
637 EXCEPTION
638 WHEN OTHERS THEN
639 p_entity_name := null;
640 hr_utility.set_location('Exception :'||l_proc,25);
641 raise;
642 --
643 End Validate_entity;
644 --
645 ------------------------------------------------------------------------
646 --
647 PROCEDURE get_table_route is
648 --
649 CURSOR csr_table_route (p_table_alias IN varchar2 )IS
650 SELECT table_route_id
651 FROM pqh_table_route
652 WHERE table_alias = p_table_alias;
653 --
654 l_proc varchar2(72) := g_package || 'get_table_route';
655 --
656 BEGIN
657 --
658 hr_utility.set_location('Entering:'||l_proc, 5);
659
660 -- get table_route_id for all the tables
661
662 -- table_route_id for per_budgets
663 OPEN csr_table_route (p_table_alias => 'P_BGT');
664 FETCH csr_table_route INTO g_table_route_id_p_bgt;
665 CLOSE csr_table_route;
666
667 -- table_route_id for per_budget_versions
668 OPEN csr_table_route (p_table_alias => 'P_BVR');
669 FETCH csr_table_route INTO g_table_route_id_p_bdt;
670 CLOSE csr_table_route;
671 --
672 hr_utility.set_location('Leaving:'||l_proc, 10);
673 --
674 End;
675 --
676 -----------------------------------------------------------------------
677 --
678 procedure get_period_details (p_proc_period_type in varchar2,
679 p_base_period_type out nocopy varchar2,
680 p_multiple out nocopy number) is
681 --
682 proc_name varchar2(72) := 'get_period_details';
683 --
684 no_periods per_time_period_types.number_per_fiscal_year%type := NULL;
685 --
686 Cursor csr_period_types is
687 select tp.number_per_fiscal_year
688 from per_time_period_types tp
689 where tp.period_type = p_proc_period_type;
690 --
691 begin
692 hr_utility.set_location('Entering:'||proc_name, 5);
693 --
694 Open csr_period_types;
695 --
696 Fetch csr_period_types into no_periods;
697 --
698 Close csr_period_types;
699 --
700 -- Use the number of periods in a fiscal year to deduce the base
701 -- period and multiple.
702 --
703 if no_periods = 1 then -- Yearly
704 p_base_period_type := MONTHLY;
705 p_multiple := 12;
706 elsif no_periods = 2 then -- Semi yearly
707 p_base_period_type := MONTHLY;
708 p_multiple := 6;
709 elsif no_periods = 4 then -- Quarterly
710 p_base_period_type := MONTHLY;
711 p_multiple := 3;
712 elsif no_periods = 6 then -- Bi monthly
713 p_base_period_type := MONTHLY;
714 p_multiple := 2;
715 elsif no_periods = 12 then -- Monthly
716 p_base_period_type := MONTHLY;
717 p_multiple := 1;
718 elsif no_periods = 13 then -- Lunar monthly
719 p_base_period_type := WEEKLY;
720 p_multiple := 4;
721 elsif no_periods = 24 then -- Semi monthly
722 p_base_period_type := SEMIMONTHLY;
723 p_multiple := 1; -- Not used for semi-monthly
724 elsif no_periods = 26 then -- Fortnightly
725 p_base_period_type := WEEKLY;
726 p_multiple := 2;
727 elsif no_periods = 52 then -- Weekly
728 p_base_period_type := WEEKLY;
729 p_multiple := 1;
730 else
731 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_PERIOD_TYPE');
732 APP_EXCEPTION.RAISE_EXCEPTION;
733 end if;
734 --
735 hr_utility.set_location('Leaving:'||proc_name, 10);
736 --
737 Exception When others then
738 p_base_period_type := null;
739 p_multiple := null;
740 hr_utility.set_location('Exception:'||proc_name, 15);
741 raise;
742 end get_period_details;
743 --
744 ---------------------------------------------------------------------------------
745 -- Locally defined function that, given the end-date of a semi-month
746 -- period and the first period's end-date (p_fpe_date) returns
747 -- the end date of the following semi-monthly period.
748 --
749 function next_semi_month(p_semi_month_date in date,
750 p_fpe_date in date)
751 RETURN date is
752 --
753 day_of_month varchar2(2);
754 last_of_month date;
755 temp_day varchar2(2);
756 --
757 func_name CONSTANT varchar2(50) := 'next_semi_month';
758 begin
759 --
760 hr_utility.set_location(func_name, 1);
761 --
762 day_of_month := to_char(p_fpe_date,'DD');
763 --
764 if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
765 -- The first period's end-date is either the 15th or the end-of-month
766 if last_day(p_semi_month_date) = p_semi_month_date then
767 -- End of month: add 15 days
768 return(p_semi_month_date + 15);
769 else
770 -- 15th of month: return last day
771 return(last_day(p_semi_month_date));
772 end if;
773 else
774 -- The first period's end-date is neither the 15th nor the end-of-month
775 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
776 day_of_month := to_char(p_semi_month_date,'DD');
777
778 if day_of_month > '15' then
779 temp_day := day_of_month - 15;
780 else
781 temp_day := day_of_month ;
782 end if ;
783 --
784 if day_of_month between '01' AND '15' then
785 if last_day(p_semi_month_date+15) = last_day(p_semi_month_date) then
786 return(p_semi_month_date + 15);
787 else
788 -- for p_semi_month_date = Feb 14th, for example
789 return(last_day(p_semi_month_date));
790 end if;
791 else -- if on the 16th or later
792 return(to_date((temp_day ||'-'||
793 to_char(add_months(p_semi_month_date,1),'MM-RRRR')
794 ), 'DD-MM-RRRR'));
795 end if ;
796 end if ;
797 end next_semi_month;
798 --
799 --
800 ------------------------------------------------------------------------------
801 --
802 -- This function performs the date calculation according to the
803 -- base period type for the period type being considered.
804 -- Note that for WEEKLY base period, the calculation can be
805 -- performed by adding days ie. straightforward addition operation.
806 -- For MONTHLY base period, the add_months() function is used.
807 -- The exception to these general categories is semi-monthly,
808 -- which is handled explicitly by the SEMIMONTHLY base period.
809 --
810 function add_multiple_of_base (p_target_date in date,
811 p_base_period_type in varchar2,
812 p_multiple in number,
813 p_fpe_date in date)
814 return date is
815 --
816 l_proc varchar2(72) := g_package || 'add_multiple_of_base';
817 rest_of_date varchar2(9);
818 temp_date date;
819 --
820 begin
821 --
822 hr_utility.set_location('Entering:'||l_proc, 5);
823 --
824 -- Errors can occur when performing date manipulation.
825 if p_base_period_type = WEEKLY then
826 --
827 hr_utility.set_location('Base Type is Weekly :'||l_proc, 10);
828 return (p_target_date + (7 * p_multiple));
829 --
830 elsif p_base_period_type = MONTHLY then
831 --
832 hr_utility.set_location('Base Type is Monthly :'||l_proc, 15);
833 return (add_months(p_target_date, p_multiple));
834 --
835 else
836 --
837 -- Addition of one semi-month.
838 --
839 hr_utility.set_location('Base Type is Semi-Month :'||l_proc, 20);
840 return(next_semi_month(p_target_date, p_fpe_date));
841 --
842 end if;
843 --
844 end add_multiple_of_base;
845 --
846 -----------------------generate_cmmtmnt_calc_dates----------------------------
847 --
848 -- This function divides the period between the supplied commitment
849 -- start date and end date into smaller periods each of the input
850 -- period frequency length and stores the start and end dates of these
851 -- smaller periods in a pl/sql table
852 --
853 PROCEDURE generate_cmmtmnt_calc_dates(p_budget_start_date in date,
854 p_budget_end_date in date,
855 p_budget_cal_freq in varchar2,
856 p_period_set_name in varchar2,
857 p_period_frequency in varchar2,
858 p_cmmtmnt_start_dt in date,
859 p_cmmtmnt_end_dt in date)
860 is
861 --
862 l_base_period varchar2(100);
863 l_multiple number(15);
864 l_all_periods cmmtmnt_dt_tab;
865 l_dummy_tab cmmtmnt_dt_tab;
866 --
867 cnt number(15) :=0;
868 rec_no number(15) :=0;
869 --
870 l_fpe date;
871 l_curr_dt date;
872 l_next_start_dt date;
873 --
874 l_start_dt_valid varchar2(1) := 'N';
875 l_end_dt_valid varchar2(1) := 'N';
876 --
877 l_proc varchar2(72) := g_package || 'generate_cmmtmnt_calc_dates';
878 --
879 Cursor csr_bdgt_time_periods is
880 Select start_date,end_date
881 From per_time_periods
882 Where period_set_name = p_period_set_name
883 AND start_date between p_budget_start_date and p_budget_end_date
884 order by start_date;
885 --
886 l_periods_rec csr_bdgt_time_periods%rowtype;
887 --
888 Begin
889 --
890 hr_utility.set_location('Entering :'||l_proc,5);
891
892 hr_utility.set_location('period set name :'||p_period_set_name,5);
893 hr_utility.set_location('p_period_frequency: '||p_period_frequency,5);
894 hr_utility.set_location('p_budget_cal_freqe :'||p_budget_cal_freq,5);
895 --
896 -- Step 1: GENERATE SUB-PERIODS FOR THE ENTIRE BUDGET FISCAL YEAR AND
897 -- STORE IT IN A DUMMY PL/SQL TABLE.
898 --
899 -- If the passed period frequency is the same as the budget calendar
900 -- frequency , we can obtain the periods under the budget calendar
901 -- from per_time_periods. Else we have to generate the periods using the
902 -- input period frequency.
903 --
904 If p_period_frequency = p_budget_cal_freq then
905 --
906
907 For l_periods_rec in csr_bdgt_time_periods Loop
908 --
909 cnt := cnt + 1;
910 l_all_periods(cnt).cmmtmnt_start_dt := l_periods_rec.start_date;
911 l_all_periods(cnt).cmmtmnt_end_dt := l_periods_rec.end_date;
912 --
913 End loop;
914 --
915 Else
916 --
917 get_period_details (p_proc_period_type => p_period_frequency,
918 p_base_period_type => l_base_period,
919 p_multiple => l_multiple);
920 --
921 l_all_periods := l_dummy_tab;
922 cnt := 1;
923 --
924 l_fpe := add_months(p_budget_start_date,-1);
925 l_curr_dt := p_budget_start_date;
926 l_next_start_dt := p_budget_start_date;
927 --
928 While l_curr_dt <= p_budget_end_date loop
929 --
930 l_all_periods(cnt).cmmtmnt_start_dt := l_next_start_dt;
931 --
932 -- Calculate the end date of this period and the start date of the
933 -- next period.
934 --
935 l_next_start_dt := add_multiple_of_base
936 (p_target_date => l_curr_dt,
937 p_base_period_type => l_base_period,
938 p_multiple => l_multiple,
939 p_fpe_date => l_fpe);
940 --
941 l_all_periods(cnt).cmmtmnt_end_dt := l_next_start_dt - 1;
942 --
943 cnt := cnt + 1;
944 l_curr_dt := l_next_start_dt;
945 --
946 End Loop;
947 --
948 End if;
949 --
950 -- Step 2: NOW EXTRACT ONLY THE PERIODS THAT LIE WITHIN THE SUPPLIED
951 -- COMMITMENT START DATE AND END DATE AND STORE IT IN A GLOBAL PL/SQL
952 -- TABLE.ALSO, VALIDATE IF THE COMMITMENT START AND END DATE COINCIDE
953 -- WITH THE START AND END DATE OF ONE OF THE GENERATED PERIODS.
954 --
955 -- Initialise the global table;
956 g_cmmtmnt_calc_dates := l_dummy_tab;
957 --
958 For cnt in NVL(l_all_periods.FIRST,0) .. NVL(l_all_periods.LAST,-1) loop
959 --
960 -- Check if the periods start date is equal to the commitment start date.
961 -- If so , then the input commitment start date is valid.
962 --
963 hr_utility.set_location('Period :'||to_char(l_all_periods(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' - ' ||to_char(l_all_periods(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),10);
964 --
965 If p_cmmtmnt_start_dt between l_all_periods(cnt).cmmtmnt_start_dt and
966 l_all_periods(cnt).cmmtmnt_end_dt then
967 --
968 hr_utility.set_location('Valid Commitment Start Date',15);
969 --
970 -- The supplied commitment_start_dt is valid
971 --
972 l_start_dt_valid := 'Y' ;
973 --
974 End if;
975 --
976 --
977 If l_start_dt_valid = 'Y' then
978 --
979 -- Now start extracting the periods .
980 --
981 rec_no := rec_no + 1;
982 --
983 g_cmmtmnt_calc_dates(rec_no).cmmtmnt_start_dt := l_all_periods(cnt).cmmtmnt_start_dt;
984 g_cmmtmnt_calc_dates(rec_no).cmmtmnt_end_dt := l_all_periods(cnt).cmmtmnt_end_dt;
985 --
986 -- If the commitment start date falls within a period , we have to
987 -- register what the period start date was and what the actual
988 -- commitment start date was so that once we find the period we
989 -- can prorate this value from the commitment start date to the
990 -- period end date.
991 --
992 If p_cmmtmnt_start_dt > l_all_periods(cnt).cmmtmnt_start_dt then
993 --
994 g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_start_dt := p_cmmtmnt_start_dt;
995 --
996 Else
997 --
998 g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_start_dt :=l_all_periods(cnt).cmmtmnt_start_dt;
999 --
1000 End if;
1001 --
1002 -- If the commitment end date falls within a period , we have to
1003 -- register what the period end date was and what the actual
1004 -- commitment end date was so that once we find the period we
1005 -- can prorate this value from the period start date to the
1006 -- commitment end date.
1007 --
1008 If p_cmmtmnt_end_dt > l_all_periods(cnt).cmmtmnt_end_dt then
1009 --
1010 g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_end_dt := l_all_periods(cnt).cmmtmnt_end_dt;
1011 --
1012 Else
1013 --
1014 hr_utility.set_location('Valid Commitment End Date',20);
1015 --
1016 l_end_dt_valid := 'Y';
1017 g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_end_dt := p_cmmtmnt_end_dt;
1018 Exit;
1019 --
1020 End if;
1021 --
1022 End if; -- start date is valid
1023 --
1024 End Loop; -- Get next period
1025
1026 --
1027 If l_start_dt_valid <> 'Y' then
1028 --
1029 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_CMMTMNT_START_DT');
1030 APP_EXCEPTION.RAISE_EXCEPTION;
1031 --
1032 End if;
1033 --
1034 if l_end_dt_valid <> 'Y' then
1035 --
1036 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_CMMTMNT_END_DT');
1037 APP_EXCEPTION.RAISE_EXCEPTION;
1038 --
1039 End if;
1040 --
1041 hr_utility.set_location('Leaving :'||l_proc,25);
1042 --
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 --
1046 hr_utility.set_location('Exception :'||l_proc,30);
1047 raise;
1048 --
1049 End generate_cmmtmnt_calc_dates;
1050 --
1051 --
1052 --------------------------------Validate_commitment_dates-----------------------
1053 --
1054 PROCEDURE Validate_commitment_dates(
1055 p_period_frequency in varchar2,
1056 p_cmmtmnt_start_dt in date,
1057 p_cmmtmnt_end_dt in date,
1058 p_budget_cal_freq in varchar2,
1059 p_period_set_name in varchar2,
1060 p_budget_start_date in date,
1061 p_budget_end_date in date)
1062 is
1063 --
1064 l_proc varchar2(72) := g_package||'Validate_commitment_dates';
1065 --
1066 Begin
1067 --
1068 hr_utility.set_location('Entering:'||l_proc, 5);
1069 --
1070 -- 1) Check if p_cmmtmnt_end_dt > p_cmmtmnt_start_dt.
1071 --
1072 If p_cmmtmnt_end_dt < p_cmmtmnt_start_dt then
1073 --
1074 FND_MESSAGE.SET_NAME('PQH','PQH_END_DT_LESS_THAN_START_DT');
1075 APP_EXCEPTION.RAISE_EXCEPTION;
1076 --
1077 End if;
1078 --
1079 -- 2) Check if p_cmmtmnt_start_dt < p_budget_start_date
1080 --
1081 if p_cmmtmnt_start_dt < p_budget_start_date then
1082 --
1083 FND_MESSAGE.SET_NAME('PQH','PQH_CMT_START_BEF_BDGT_START');
1084 APP_EXCEPTION.RAISE_EXCEPTION;
1085 --
1086 End if;
1087 -- commented by Sumit Goyal
1088 -- for relieving commitments you may have to calculate commitments beyond the budget date range
1089 --
1090 -- 3) Check if p_cmmtmnt_start_dt > p_budget_end_date
1091 --
1092 if p_cmmtmnt_start_dt > p_budget_end_date then
1093 --
1094 FND_MESSAGE.SET_NAME('PQH','PQH_CMTMNT_START_AFT_BDGT_END');
1095 APP_EXCEPTION.RAISE_EXCEPTION;
1096 --
1097 End if;
1098 --
1099 -- 4) Check if p_cmmtmnt_end_dt > p_budget_end_date
1100 --
1101 if p_cmmtmnt_end_dt > p_budget_end_date then
1102 --
1103 FND_MESSAGE.SET_NAME('PQH','PQH_CMTMNT_END_AFT_BDGT_END');
1104 APP_EXCEPTION.RAISE_EXCEPTION;
1105 --
1106 End if;
1107 --
1108 -- THE FOLL FUNCTION DOES THE FOLL VALIDATIONS . GIVEN THE FREQUENCY AND THE
1109 -- FISCAL YEAR OF A BUDGET,THE COMMIMENT START DATE MUST START ON A PERIOD
1110 -- START DATE .ALSO END DATE SHOULD A PERIOD END DATE WITHIN THE BUDGETS END
1111 -- DATE OR THE ONE IMMEDIATELY AFTER .
1112 --
1113 generate_cmmtmnt_calc_dates(p_budget_start_date => p_budget_start_date,
1114 p_budget_end_date => p_budget_end_date,
1115 p_period_set_name => p_period_set_name,
1116 p_budget_cal_freq => p_budget_cal_freq,
1117 p_period_frequency => p_period_frequency,
1118 p_cmmtmnt_start_dt=> p_cmmtmnt_start_dt,
1119 p_cmmtmnt_end_dt => p_cmmtmnt_end_dt);
1120 --
1121 hr_utility.set_location('Leaving:'||l_proc, 10);
1122 --
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 raise;
1126 End Validate_commitment_dates;
1127 --
1128 ------------------------get_payroll_period_type-------------------------------
1129 --
1130 Function get_payroll_period_type(p_payroll_id in number,
1131 p_effective_dt in date) RETURN varchar2 is
1132 --
1133 l_period_type pay_payrolls_f.period_type%type;
1134 --
1135 -- Pick up the period type available for a payroll as of the commitment calculation date.
1136 --
1137 Cursor csr_period_type is
1138 SELECT PRL.period_type
1139 FROM pay_payrolls_f PRL
1140 WHERE PRL.payroll_id = p_payroll_id
1141 AND p_effective_dt BETWEEN PRL.effective_start_date AND PRL.effective_end_date;
1142 --
1143 l_proc varchar2(72) := g_package||'get_payroll_period_type';
1144 --
1145 Begin
1146 --
1147 hr_utility.set_location('Entering:'||l_proc,5);
1148 Open csr_period_type;
1149 Fetch csr_period_type into l_period_type;
1150 Close csr_period_type;
1151 hr_utility.set_location('period type is:'||l_period_type,8);
1152 hr_utility.set_location('Leaving:'||l_proc, 10);
1153 RETURN l_period_type;
1154 End;
1155 --
1156 -----------------------get_number_per_fiscal_year--------------------------------
1157 --
1158 -- This function returns the number of times a particular frequency
1159 -- occurs in a year.
1160 --
1161 Function get_number_per_fiscal_year(p_frequency in varchar2) RETURN NUMBER is
1162 --
1163 l_number_per_fiscal_year per_time_period_types.number_per_fiscal_year%type;
1164 --
1165 Cursor csr_period_type is
1166 SELECT PT.number_per_fiscal_year
1167 FROM per_time_period_types PT
1168 WHERE UPPER(PT.period_type) = UPPER(p_frequency);
1169
1170 l_check hr_lookups.meaning%type;
1171 l_proc varchar2(72) := g_package||'get_number_per_fiscal_year';
1172 Begin
1173 hr_utility.set_location('Entering:'||l_proc,5);
1174 Open csr_period_type;
1175 Fetch csr_period_type into l_number_per_fiscal_year;
1176 If csr_period_type%notfound then
1177 hr_utility.set_location('not a valid period type:'||l_proc,10);
1178 Close csr_period_type;
1179 -- Frequency passed is not a valid period type, checking the lookups
1180 l_check := hr_general.decode_lookup(p_lookup_type =>'PAY_BASIS',
1181 p_lookup_code => p_frequency);
1182 If l_check is null then
1183 hr_utility.set_location('not a valid sal basis :'||l_proc,20);
1184 l_check := hr_general.decode_lookup(p_lookup_type =>'FREQUENCY',
1185 p_lookup_code => p_frequency);
1186 If l_check is null then
1187 hr_utility.set_location('not a valid assignment frequency :'||l_proc,30);
1188 RETURN -1;
1189 Else
1190 if p_frequency ='W' then
1191 return 52;
1192 elsif p_frequency ='M' then
1193 return 12;
1194 elsif p_frequency ='D' then
1195 return 365;
1196 elsif p_frequency ='Y' then
1197 return 1;
1198 else
1199 return -1;
1200 end if;
1201 End if;
1202 Else
1203 if p_frequency ='ANNUAL' then
1204 return 1;
1205 elsif p_frequency ='MONTHLY' then
1206 return 12;
1207 else
1208 return -1;
1209 end if;
1210 End if;
1211 End if;
1212 Close csr_period_type;
1213 hr_utility.set_location('Leaving:'||l_proc, 10);
1214 RETURN l_number_per_fiscal_year;
1215 End;
1216 --
1217 --------------------get_commitment_from_formula-------------------------------
1218 --
1219 Function get_commitment_from_formula(
1220 p_formula_id in number,
1221 p_business_group_id in number default null,
1222 p_payroll_id in number default null,
1223 p_payroll_action_id in number default null,
1224 p_assignment_id in number default null,
1225 p_assignment_action_id in number default null,
1226 p_org_pay_method_id in number default null,
1227 p_per_pay_method_id in number default null,
1228 p_organization_id in number default null,
1229 p_tax_unit_id in number default null,
1230 p_jurisdiction_code in varchar2 default null,
1231 p_balance_date in date default null,
1232 p_element_entry_id in number default null,
1233 p_element_type_id in number default null,
1234 p_original_entry_id in number default null,
1235 p_tax_group in number default null,
1236 p_pgm_id in number default null,
1237 p_pl_id in number default null,
1238 p_pl_typ_id in number default null,
1239 p_opt_id in number default null,
1240 p_ler_id in number default null,
1241 p_communication_type_id in number default null,
1242 p_action_type_id in number default null,
1243 p_acty_base_rt_id in number default null,
1244 p_elig_per_elctbl_chc_id in number default null,
1245 p_enrt_bnft_id in number default null,
1246 p_regn_id in number default null,
1247 p_rptg_grp_id in number default null,
1248 p_cm_dlvry_mthd_cd in varchar2 default null,
1249 p_crt_ordr_typ_cd in varchar2 default null,
1250 p_enrt_ctfn_typ_cd in varchar2 default null,
1251 p_bnfts_bal_id in number default null,
1252 p_elig_per_id in number default null,
1253 p_per_cm_id in number default null,
1254 p_prtt_enrt_actn_id in number default null,
1255 p_effective_date in date,
1256 p_param1 in varchar2 default null,
1257 p_param1_value in varchar2 default null,
1258 p_param2 in varchar2 default null,
1259 p_param2_value in varchar2 default null,
1260 p_param3 in varchar2 default null,
1261 p_param3_value in varchar2 default null,
1262 p_param4 in varchar2 default null,
1263 p_param4_value in varchar2 default null,
1264 p_param5 in varchar2 default null,
1265 p_param5_value in varchar2 default null,
1266 p_param6 in varchar2 default null,
1267 p_param6_value in varchar2 default null,
1268 p_param7 in varchar2 default null,
1269 p_param7_value in varchar2 default null,
1270 p_param8 in varchar2 default null,
1271 p_param8_value in varchar2 default null,
1272 p_param9 in varchar2 default null,
1273 p_param9_value in varchar2 default null,
1274 p_param10 in varchar2 default null,
1275 p_param10_value in varchar2 default null,
1276 p_element_input_value_id in number default null,
1277 p_commitment_start_date in date default null,
1278 p_commitment_end_date in date default null)
1279 RETURN number is
1280 --
1281 l_inputs ff_exec.inputs_t;
1282 l_outputs ff_exec.outputs_t;
1283 l_commitment number;
1284 --
1285 l_proc varchar2(100) := g_package || 'get_commitment_from_formula';
1286 --
1287 l_input_count number;
1288 BEGIN
1289 --
1290 hr_utility.set_location ('Entering: '||l_proc,05);
1291 --
1292 begin
1293 /*
1294 * Insert row into fnd_sessions to allow use of global values
1295 */
1296 insert into fnd_sessions (session_id, effective_date) values (userenv('sessionid'),trunc(sysdate));
1297 exception
1298 when others then null;
1299 end;
1300 --
1301 -- Initialise the formula .
1302 --
1303 ff_exec.init_formula
1304 (p_formula_id => p_formula_id,
1305 p_effective_date => p_effective_date,
1306 p_inputs => l_inputs,
1307 p_outputs => l_outputs);
1308 --
1309 -- NOTE that we use special parameter values in order to state which
1310 -- array locations we put the values into, this is because of the caching
1311 -- mechanism that formula uses.
1312 -- Set the Context for the formula.
1313 --
1314 hr_utility.set_location ('Set Context '||l_proc,10);
1315 --
1316 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
1317 --
1318 if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
1319 --
1320 l_inputs(l_count).value := nvl(p_business_group_id, -1);
1321 --
1322 elsif l_inputs(l_count).name = 'PAYROLL_ID' then
1323 --
1324 l_inputs(l_count).value := nvl(p_bnfts_bal_id, nvl(p_rptg_grp_id, nvl(p_payroll_id,-1)));
1325 --
1326 elsif l_inputs(l_count).name = 'PAYROLL_ACTION_ID' then
1327 --
1328 l_inputs(l_count).value := nvl(p_acty_base_rt_id, nvl(p_payroll_action_id, -1));
1329 --
1330 elsif l_inputs(l_count).name = 'ASSIGNMENT_ID' then
1331 --
1332 l_inputs(l_count).value := nvl(p_assignment_id, -1);
1333 --
1334 elsif l_inputs(l_count).name = 'ASSIGNMENT_ACTION_ID' then
1335 --
1336 l_inputs(l_count).value := nvl(p_assignment_action_id, -1);
1337 --
1338 elsif l_inputs(l_count).name = 'ORG_PAY_METHOD_ID' then
1339 --
1340 l_inputs(l_count).value := nvl(p_per_cm_id,nvl(p_prtt_enrt_actn_id, nvl(p_enrt_bnft_id, nvl(p_org_pay_method_id, -1))));
1341 --
1342 elsif l_inputs(l_count).name = 'PER_PAY_METHOD_ID' then
1343 --
1344 l_inputs(l_count).value := nvl(p_elig_per_id, nvl(p_regn_id, nvl(p_per_pay_method_id, -1)));
1345 --
1346 elsif l_inputs(l_count).name = 'ORGANIZATION_ID' then
1347 --
1348 l_inputs(l_count).value := nvl(p_organization_id, -1);
1349 --
1350 elsif l_inputs(l_count).name = 'TAX_UNIT_ID' then
1351 --
1352 l_inputs(l_count).value := nvl(p_tax_unit_id, -1);
1353 --
1354 elsif l_inputs(l_count).name = 'JURISDICTION_CODE' then
1355 --
1356 l_inputs(l_count).value := nvl(p_cm_dlvry_mthd_cd, nvl(p_crt_ordr_typ_cd,nvl(p_jurisdiction_code, 'xx')));
1357 --
1358 elsif l_inputs(l_count).name = 'SOURCE_TEXT' then
1359 --
1360 l_inputs(l_count).value := nvl(p_enrt_ctfn_typ_cd, 'xx');
1361 --
1362 elsif l_inputs(l_count).name = 'BALANCE_DATE' then
1363 --
1364 l_inputs(l_count).value := fnd_date.date_to_canonical(p_balance_date);
1365 --
1366 elsif l_inputs(l_count).name = 'ELEMENT_TYPE_ID' then
1367 --
1368 l_inputs(l_count).value := nvl(p_element_type_id, -1);
1369 --
1370 elsif l_inputs(l_count).name = 'ELEMENT_ENTRY_ID' then
1371 --
1372 l_inputs(l_count).value := nvl(p_element_entry_id, -1);
1373 --
1374 elsif l_inputs(l_count).name = 'ORIGINAL_ENTRY_ID' then
1375 --
1376 l_inputs(l_count).value := nvl(p_original_entry_id, -1);
1377 --
1378 elsif l_inputs(l_count).name = 'TAX_GROUP' then
1379 --
1380 l_inputs(l_count).value := p_tax_group;
1381 --
1382 elsif l_inputs(l_count).name = 'PGM_ID' then
1383 --
1384 l_inputs(l_count).value := nvl(p_pgm_id,-1);
1385 --
1386 elsif l_inputs(l_count).name = 'PL_ID' then
1387 --
1388 l_inputs(l_count).value := nvl(p_pl_id,-1);
1389 --
1390 elsif l_inputs(l_count).name = 'PL_TYP_ID' then
1391 --
1392 l_inputs(l_count).value := nvl(p_pl_typ_id,-1);
1393 --
1394 elsif l_inputs(l_count).name = 'OPT_ID' then
1395 --
1396 l_inputs(l_count).value := nvl(p_opt_id,-1);
1397 --
1398 elsif l_inputs(l_count).name = 'LER_ID' then
1399 --
1400 l_inputs(l_count).value := nvl(p_ler_id,-1);
1401 --
1402 elsif l_inputs(l_count).name = 'COMM_TYP_ID' then
1403 --
1404 l_inputs(l_count).value := nvl(p_communication_type_id,-1);
1405 --
1406 elsif l_inputs(l_count).name = 'ACT_TYP_ID' then
1407 --
1408 l_inputs(l_count).value := nvl(p_action_type_id,-1);
1409 --
1410 elsif l_inputs(l_count).name = 'ELEMENT_INPUT_VALUE_ID' then
1411 --
1412 l_inputs(l_count).value := nvl(p_element_input_value_id,-1);
1413 --
1414 elsif l_inputs(l_count).name = 'COMMITMENT_START_DATE' then
1415 --
1416 l_inputs(l_count).value := fnd_date.date_to_canonical(p_commitment_start_date);
1417 --
1418 elsif l_inputs(l_count).name = 'COMMITMENT_END_DATE' then
1419 --
1420 l_inputs(l_count).value := fnd_date.date_to_canonical(p_commitment_end_date);
1421 --
1422 elsif l_inputs(l_count).name = p_param1 then
1423 --
1424 l_inputs(l_count).value := p_param1_value;
1425 --
1426 elsif l_inputs(l_count).name = p_param2 then
1427 --
1428 l_inputs(l_count).value := p_param2_value;
1429 --
1430 elsif l_inputs(l_count).name = p_param3 then
1431 --
1432 l_inputs(l_count).value := p_param3_value;
1433 --
1434 elsif l_inputs(l_count).name = p_param4 then
1435 --
1436 l_inputs(l_count).value := p_param4_value;
1437 --
1438 elsif l_inputs(l_count).name = p_param5 then
1439 --
1440 l_inputs(l_count).value := p_param5_value;
1441 --
1442 elsif l_inputs(l_count).name = p_param6 then
1443 --
1444 l_inputs(l_count).value := p_param6_value;
1445 --
1446 elsif l_inputs(l_count).name = p_param7 then
1447 --
1448 l_inputs(l_count).value := p_param7_value;
1449 --
1450 elsif l_inputs(l_count).name = p_param8 then
1451 --
1452 l_inputs(l_count).value := p_param8_value;
1453 --
1454 elsif l_inputs(l_count).name = p_param9 then
1455 --
1456 l_inputs(l_count).value := p_param9_value;
1457 --
1458 elsif l_inputs(l_count).name = p_param10 then
1459 --
1460 l_inputs(l_count).value := p_param10_value;
1461 --
1462 elsif l_inputs(l_count).name = 'DATE_EARNED' then
1463 --
1464 -- Note that you must pass the date as a string, that is because
1465 -- of the canonical date change of 11.5
1466 -- Still the fast formula does't accept the full canonical form.
1467 --
1468 -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
1469 l_inputs(l_count).value := to_char(p_effective_date, 'RRRR/MM/DD');
1470 --
1471 end if;
1472 --
1473 end loop;
1474 --
1475 hr_utility.set_location ('Run formula: '||l_proc,15);
1476 --
1477 -- We have loaded the input record . Now run the formula.
1478 --
1479 ff_exec.run_formula(p_inputs => l_inputs,
1480 p_outputs => l_outputs);
1481 --
1482 --
1483 -- Loop through the returned table and make sure that the returned
1484 -- values have been found
1485 --
1486 for l_count in NVL(l_outputs.first,0)..NVL(l_outputs.last,-1) loop
1487 --
1488 --
1489 if l_outputs(l_count).name = 'COMMITMENT' then
1490 --
1491 l_commitment := l_outputs(l_count).value;
1492 --
1493 Elsif l_outputs(l_count).name = 'MESSAGE' then
1494 null;
1495 end if;
1496 --
1497 end loop;
1498 --
1499 hr_utility.set_location ('Entering: '||l_proc,20);
1500 --
1501 Return l_commitment;
1502 --
1503 End;
1504 --
1505 ------------------------get_commitment_from_elmnt_type-----------------------------------
1506 --
1507 -- This function returns the commitment for 1 assignment for a given
1508 -- element type
1509 --
1510 FUNCTION get_commitment_from_elmnt_type(p_commit_calculation_dt in date,
1511 p_actual_cmmtmnt_start_dt in date,
1512 p_commit_calculation_end_dt in date,
1513 p_actual_cmmtmnt_end_dt in date,
1514 p_commitment_calc_frequency in varchar2,
1515 p_budget_calendar_frequency in varchar2,
1516 p_dflt_elmnt_frequency in varchar2,
1517 p_business_group_id in number,
1518 p_assignment_id in number,
1519 p_payroll_id in number,
1520 p_pay_basis_id in number,
1521 p_element_type_id in number,
1522 p_element_input_value_id in number,
1523 p_normal_hours in number,
1524 p_asst_frequency in varchar2)
1525 RETURN NUMBER is
1526 --
1527 -- This cursor selects all element entries for an assignment and the given
1528 -- element type.
1529 --
1530 Cursor csr_assignment_entries(p_commit_calculation_dt in date,
1531 p_assignment_id in number,
1532 p_element_type_id in number,
1533 p_commit_calculation_end_dt in date) is
1534 Select EE.element_entry_id, EE.creator_type,
1535 ee.effective_start_date, ee.effective_end_date
1536 from pay_element_entries_f EE, pay_element_links_f EL
1537 Where EL.element_type_id = p_element_type_id
1538 --AND p_commit_calculation_dt between EL.effective_start_date and EL.effective_end_date
1539 and EL.effective_start_date < p_commit_calculation_end_dt
1540 and EL.effective_end_date > p_commit_calculation_dt
1541 AND EL.element_link_id = EE.element_link_id
1542 AND EE.assignment_id = p_assignment_id
1543 --AND p_commit_calculation_dt between EE.effective_start_date and EE.effective_end_date;
1544 and EE.effective_start_date <= p_commit_calculation_end_dt
1545 and EE.effective_end_date >= p_commit_calculation_dt;
1546
1547 --
1548 -- This cursor selects the entry value for a given entry_id and
1549 -- input_value_id.
1550 --
1551 Cursor csr_elmnt_entry_value(p_commit_calculation_dt in date,
1552 p_input_value_id in number,
1553 p_element_entry_id in number,
1554 p_commit_calculation_end_dt in date) is
1555 Select screen_entry_value
1556 from pay_element_entry_values_f
1557 Where input_value_id = p_input_value_id
1558 AND element_entry_id = p_element_entry_id
1559 --AND p_commit_calculation_dt between effective_start_date and effective_end_date;
1560 and effective_start_date < p_commit_calculation_end_dt
1561 and effective_end_date > p_commit_calculation_dt;
1562
1563 -- This cursor get the input value name for a given input value id.
1564 Cursor csr_get_input_value_name( p_input_value_id in number,
1565 p_commit_calculation_dt in date,
1566 p_commit_calculation_end_dt in date) is
1567 select name from pay_input_values_f piv
1568 where piv.input_value_id = p_input_value_id
1569 --and p_commit_calculation_dt between piv.effective_start_date and piv.effective_start_date;
1570 and piv.effective_end_date > p_commit_calculation_dt
1571 and piv.effective_start_date <= p_commit_calculation_end_dt ;
1572 -- This cursor gets the pay basis frequency for a particular assignment.
1573 Cursor csr_get_pay_basis_freq (p_pay_basis_id in number) is
1574 select pay_basis
1575 from per_pay_bases ppb
1576 where ppb.pay_basis_id = p_pay_basis_id;
1577
1578 -- select pay_basis from per_pay_bases ppb, per_all_assignments_f paf
1579 -- where paf.pay_basis_id = ppb.pay_basis_id
1580 -- and paf.assignment_id = p_assignment_id
1581 --and p_commit_calculation_dt between paf.effective_start_date and paf.effective_end_date;
1582 -- and paf.effective_end_date >= p_commit_calculation_dt
1583 -- and paf.effective_start_date <= p_commit_calculation_end_dt
1584
1585 --
1586 --
1587 -- DECLARE all local variables.
1588 --
1589 --
1590 l_entry_value pay_element_entry_values.screen_entry_value%type;
1591 l_entry_effective_end_date pay_element_entries_f.effective_end_date%type;
1592 l_entry_effective_start_date pay_element_entries_f.effective_start_date%type;
1593 l_element_frequency pay_element_entry_values.screen_entry_value%type;
1594 l_element_entry_id pay_element_entries_f.element_entry_id%type;
1595 l_element_creator_type pay_element_entries_f.creator_type%type;
1596 --
1597 l_adjusted_start_dt pqh_element_commitments.commitment_start_date%type;
1598 l_adjusted_end_dt pqh_element_commitments.commitment_end_date%type;
1599 --
1600 l_entry_commitment number;
1601 l_converted_amt number;
1602 --
1603 l_input_value_name varchar2(250);
1604 --
1605 l_proc varchar2(72) := g_package||'get_commitment_from_elmnt_type';
1606 --
1607 --
1608 Begin
1609 --
1610 hr_utility.set_location('Entering:'||l_proc, 5);
1611 hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612 hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613 hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614 hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1615
1616 --
1617
1618 -- GET ALL ELEMENT ENTRIES FOR THIS ELEMENT TYPE , AND THIS
1619 -- ASSIGNMENT, EFFECTIVE AS OF COMMITMENT CALCULATION DATE
1620 -- FOR EACH ENRTY , CALCULATE COMMITMENT AND CONVERT IT TO REQD FREQUENCY
1621 --
1622 l_entry_commitment := 0;
1623 Open csr_assignment_entries
1624 (p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
1625 p_assignment_id => p_assignment_id,
1626 p_element_type_id => p_element_type_id,
1627 p_commit_calculation_end_dt => p_actual_cmmtmnt_end_dt);
1628 Loop
1629 Fetch csr_assignment_entries into l_element_entry_id,l_element_creator_type,l_entry_effective_start_date,l_entry_effective_end_date;
1630 If csr_assignment_entries%notfound then
1631 Close csr_assignment_entries;
1632 Exit;
1633 End if;
1634
1635 hr_utility.set_location('Entry exist for Assgnt '||l_proc,10);
1636 -- SELECT THE COMMITMENT AMOUNT FOR THIS ELEMENT ENTRY
1637 l_entry_value := 0;
1638 hr_utility.set_location('p_input_value_id '||p_element_input_value_id,11);
1639 hr_utility.set_location('l_element_entry_id '||l_element_entry_id,11);
1640 hr_utility.set_location('p_commit_calculation_dt '||to_char(p_commit_calculation_dt),11);
1641 Open csr_elmnt_entry_value
1642 (p_commit_calculation_dt => l_entry_effective_start_date,
1643 p_element_entry_id => l_element_entry_id,
1644 p_input_value_id => p_element_input_value_id,
1645 p_commit_calculation_end_dt => l_entry_effective_end_date);
1646 Fetch csr_elmnt_entry_value into l_entry_value;
1647
1648 hr_utility.set_location('Entry Value '||l_entry_value,11);
1649 --
1650 If csr_elmnt_entry_value%found then
1651 --
1652 -- Only if there is entry value , there is any point in getting the
1653 -- frequency for which it is available and converting it to reqd frequency.
1654 --
1655 hr_utility.set_location('Value is there for this entry',15);
1656
1657 Close csr_elmnt_entry_value;
1658 --
1659 -- SELECT THE FREQUENCY FOR THE ELEMENT ENTRY based on
1660 -- frequency input value id.Ideally it should be provided when
1661 -- element entry input value is provided. If it is not provided
1662 -- we will use budget element frequency as the elements frequency
1663 --
1664 -- Comment added by sgoyal
1665 -- We don't ask for frequency input value anymore on form, hence dflt_elmnt_freq will be used.
1666 hr_utility.set_location('Frequency is dflt Elmnt '||p_dflt_elmnt_frequency,30);
1667 --
1668 -- l_element_frequency := p_dflt_elmnt_frequency;
1669 --
1670 if p_dflt_elmnt_frequency is null then
1671 if l_element_creator_type = 'SP' then
1672 Open csr_get_input_value_name(
1673 p_input_value_id =>p_element_input_value_id,
1674 p_commit_calculation_dt =>p_actual_cmmtmnt_start_dt,
1675 p_commit_calculation_end_dt => p_actual_cmmtmnt_end_dt);
1676 Fetch csr_get_input_value_name into l_input_value_name;
1677 Close csr_get_input_value_name;
1678 If l_input_value_name = 'Pay Value' then
1679 l_element_frequency := p_dflt_elmnt_frequency;
1680 else
1681 Open csr_get_pay_basis_freq(
1682 p_pay_basis_id => p_pay_basis_id);
1683 Fetch csr_get_pay_basis_freq into l_element_frequency;
1684 Close csr_get_pay_basis_freq;
1685 end if;
1686 else
1687 l_element_frequency := p_dflt_elmnt_frequency;
1688 end if;
1689 else
1690 l_element_frequency := p_dflt_elmnt_frequency;
1691 end if;
1692
1693 hr_utility.set_location('Figure :' || l_entry_value,31);
1694 hr_utility.set_location('From :' || l_element_frequency,32);
1695 hr_utility.set_location('To :' || p_commitment_calc_frequency,33);
1696 --
1697 l_converted_amt := Convert_Period_Type(
1698 p_bus_grp_id => p_business_group_id,
1699 p_payroll_id => p_payroll_id,
1700 p_asst_std_hours => p_normal_hours,
1701 p_figure => fnd_number.canonical_to_number(l_entry_value),
1702 p_from_freq => l_element_frequency,
1703 p_to_freq => p_commitment_calc_frequency,
1704 p_period_start_date => p_commit_calculation_dt,
1705 p_period_end_date => p_commit_calculation_end_dt,
1706 p_asst_std_freq => p_asst_frequency,
1707 p_dflt_elmnt_frequency => p_dflt_elmnt_frequency,
1708 p_budget_calendar_frequency => p_budget_calendar_frequency
1709 );
1710 hr_utility.set_location('converted amt is'||l_converted_amt,34);
1711 --
1712 -- If the period for which we need to calculate commitment is lesser
1713 -- than the input period type , we have to further prorate the
1714 -- calculated commitment value for the no of days in the commitment
1715 -- calculation period . This can happen only for the last period for
1716 -- which commitment is generated.
1717 --
1718 If l_entry_effective_start_date > p_actual_cmmtmnt_start_dt then
1719 l_adjusted_start_dt := l_entry_effective_start_date;
1720 Else
1721 l_adjusted_start_dt := p_actual_cmmtmnt_start_dt;
1722 End if;
1723
1724 If l_entry_effective_end_date < p_actual_cmmtmnt_end_dt then
1725 l_adjusted_end_dt := l_entry_effective_end_date;
1726 Else
1727 l_adjusted_end_dt := p_actual_cmmtmnt_end_dt;
1728 End if;
1729 --
1730 hr_utility.set_location('Before Proration - Converted Amount: '||l_converted_amt,34);
1731 l_converted_amt := nvl(l_converted_amt,0) *
1732 (l_adjusted_end_dt - l_adjusted_start_dt + 1)/
1733 (p_commit_calculation_end_dt - p_commit_calculation_dt + 1);
1734 hr_utility.set_location('After Proration - Converted Amount: '||l_converted_amt,35);
1735 l_entry_commitment := nvl(l_entry_commitment,0) + nvl(l_converted_amt,0);
1736 Else
1737 Close csr_elmnt_entry_value;
1738 End if; /** There is a entry value,that needs to be converted **/
1739 End loop; /** element entry commitment calculation **/
1740 hr_utility.set_location('Leaving:'||l_proc, 40);
1741 RETURN l_entry_commitment;
1742 End;
1743 --
1744 ----------------------get_commitment_from_sal_basis--------------------------
1745 --
1746 -- This function returns the commitment for 1 assignment for a given
1747 -- element type
1748 --
1749 FUNCTION get_commitment_from_sal_basis(p_commit_calculation_dt in date,
1750 p_actual_cmmtmnt_start_dt in date,
1751 p_commit_calculation_end_dt in date,
1752 p_actual_cmmtmnt_end_dt in date,
1753 p_commitment_calc_frequency in varchar2,
1754 p_element_type_id in number,
1755 p_budget_calendar_frequency in varchar2,
1756 p_dflt_elmnt_frequency in varchar2,
1757 p_business_group_id in number,
1758 p_assignment_id in number,
1759 p_payroll_id in number,
1760 p_pay_basis_id in number,
1761 p_normal_hours in number,
1762 p_asst_frequency in varchar2)
1763 RETURN NUMBER is
1764 --
1765 -- DECLARE all local variables.
1766 --
1767 l_entry_value number;
1768 l_pay_basis per_pay_bases.pay_basis%type;
1769 l_adjusted_start_dt pqh_element_commitments.commitment_start_date%type;
1770 l_adjusted_end_dt pqh_element_commitments.commitment_end_date%type;
1771 --
1772 --
1773 l_converted_amt number;
1774 --
1775 -- If the salary basis flag = 'Y' for the element type , this cursor
1776 -- selects the salary amount and pay basis for an assignment.
1777 --
1778 -- We are filtering here , only those entries of the assignment that belong
1779 -- to the passed element type.
1780 --
1781 cursor csr_pay_basis (p_pay_basis_id in number,
1782 p_commit_calculation_dt in date,
1783 p_commit_calculation_end_dt in date) is
1784 Select ppb.pay_basis,ppb.input_value_id
1785 from per_pay_bases ppb
1786 , pay_input_values_f piv --To ensure that this input value id belongs to the passed element_type
1787 where ppb.pay_basis_id = p_pay_basis_id
1788 and piv.input_value_id = ppb.input_value_id
1789 and piv.element_type_id = p_element_type_id
1790 and piv.effective_start_date <= p_commit_calculation_end_dt
1791 and piv.effective_end_date >= p_commit_calculation_dt;
1792 -- and p_commit_calculation_dt
1793 -- between piv.effective_start_date and piv.effective_end_date;
1794 --
1795 Cursor csr_salary_basis(p_input_value_id in number,
1796 p_assignment_id in number,
1797 p_commit_calculation_dt in date,
1798 p_commit_calculation_end_dt in date) is
1799 Select fnd_number.canonical_to_number(pev.screen_entry_value),
1800 pev.effective_start_date, pev.effective_end_date
1801 from pay_element_entry_values_f pev
1802 , pay_element_entries_f pee
1803 where pee.assignment_id = p_assignment_id
1804 -- and p_commit_calculation_dt
1805 -- between pee.effective_start_date and pee.effective_end_date
1806 and pee.effective_start_date <= p_commit_calculation_end_dt
1807 and pee.effective_end_date >= p_commit_calculation_dt
1808 and pev.element_entry_id = pee.element_entry_id
1809 and pev.input_value_id = p_input_value_id
1810 and pev.effective_start_date <= p_commit_calculation_end_dt
1811 and pev.effective_end_date >= p_commit_calculation_dt;
1812
1813 -- and p_commit_calculation_dt
1814 -- between pev.effective_start_date and pev.effective_end_date;
1815 --
1816 l_proc varchar2(72) := g_package||'get_commitment_from_sal_basis';
1817 l_input_value_id number;
1818 l_eot date := to_date('31-12-4712','DD-MM-RRRR');
1819 --
1820 l_stDt date;
1821 l_edDt date;
1822 ad_stDt date;
1823 ad_edDt date;
1824 l_inter_amt number;
1825 Begin
1826 --
1827 --
1828 hr_utility.set_location('Entering:'||l_proc, 5);
1829 --
1830
1831 hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832 hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1833 hr_utility.set_location('p_commit_calculation_dt'||p_commit_calculation_dt,5);
1834 hr_utility.set_location('p_element_type_id'||p_element_type_id,5);
1835 l_entry_value := NULL;
1836 l_pay_basis := NULL;
1837 --
1838 Open csr_pay_basis(p_pay_basis_id => p_pay_basis_id,
1839 p_commit_calculation_dt => nvl(p_actual_cmmtmnt_start_dt,l_eot),
1840 p_commit_calculation_end_dt => p_actual_cmmtmnt_end_dt) ;
1841 Fetch csr_pay_basis into l_pay_basis,l_input_value_id;
1842 Close csr_pay_basis;
1843
1844 hr_utility.set_location('PB Input value is'||l_input_value_id,8);
1845 if l_input_value_id is null then
1846 return null;
1847 end if;
1848 Open csr_salary_basis(p_assignment_id => p_assignment_id,
1849 p_input_value_id => l_input_value_id,
1850 p_commit_calculation_dt => nvl(p_actual_cmmtmnt_start_dt,l_eot),
1851 p_commit_calculation_end_dt => p_commit_calculation_end_dt) ;
1852 --
1853 Loop
1854 Fetch csr_salary_basis into l_entry_value,l_stDt, l_edDt;
1855 Exit when csr_salary_basis%NotFound;
1856 --
1857 --
1858 -- CONVERT AMOUNT TO BUDGET_CALENDAR FREQUENCY
1859 --
1860 hr_utility.set_location('Figure :' || to_char(l_entry_value),10);
1861 hr_utility.set_location('From :' || l_pay_basis,15);
1862 hr_utility.set_location('To :' || p_commitment_calc_frequency,20);
1863
1864 l_inter_amt := Convert_Period_Type(
1865 p_bus_grp_id => p_business_group_id,
1866 p_payroll_id => p_payroll_id,
1867 p_asst_std_hours => p_normal_hours,
1868 p_figure => l_entry_value,
1869 p_from_freq => l_pay_basis,
1870 p_to_freq => p_commitment_calc_frequency,
1871 p_period_start_date => p_commit_calculation_dt,
1872 p_period_end_date => p_commit_calculation_end_dt,
1873 p_asst_std_freq => p_asst_frequency,
1874 p_dflt_elmnt_frequency => p_dflt_elmnt_frequency,
1875 p_budget_calendar_frequency => p_budget_calendar_frequency
1876 );
1877
1878 --
1879 If ( l_stDt > p_commit_calculation_dt ) then
1880 ad_stDt := l_stDt;
1881 else
1882 ad_stDt := p_commit_calculation_dt;
1883 end if;
1884
1885 IF ( l_edDt > p_commit_calculation_end_dt ) then
1886 ad_edDt := p_commit_calculation_end_dt;
1887 else
1888 ad_edDt :=l_edDt;
1889 end if;
1890
1891 --
1892 l_inter_amt := l_inter_amt * (ad_edDt - ad_stDt + 1)/365;
1893 --
1894 l_converted_amt := nvl(l_converted_amt,0) + nvl(l_inter_amt,0) ;
1895 --
1896 hr_utility.set_location('~~NS:Adjusted Start Date- End Date: '||ad_stDt||' - '||
1897 ad_edDt||' - '||l_inter_amt,8);
1898 hr_utility.set_location('~~NS:l_converted_amt: '||l_converted_amt,8);
1899
1900 End Loop;
1901 Close csr_salary_basis;
1902
1903 --
1904 If p_actual_cmmtmnt_start_dt <> p_commit_calculation_dt then
1905 --
1906 l_adjusted_start_dt := p_actual_cmmtmnt_start_dt;
1907 --
1908 Else
1909 l_adjusted_start_dt := p_commit_calculation_dt;
1910 --
1911 End if;
1912
1913 If p_actual_cmmtmnt_end_dt <> p_commit_calculation_end_dt then
1914 --
1915 -- If the period for which we need to calculate commitment is lesser
1916 -- than the input period type , we have to further prorate the
1917 -- calculated commitment value for the no of days in the commitment
1918 -- calculation period . This can happen only for the last period for
1919 -- which commitment is generated.
1920 --
1921 l_adjusted_end_dt := p_actual_cmmtmnt_end_dt;
1922 --
1923 Else
1924 l_adjusted_end_dt := p_commit_calculation_end_dt;
1925 --
1926 End if;
1927 --
1928 l_converted_amt := nvl(l_converted_amt,0) *
1929 (l_adjusted_end_dt - l_adjusted_start_dt + 1)/
1930 (p_commit_calculation_end_dt - p_commit_calculation_dt + 1);
1931 --
1932 l_converted_amt := round(l_converted_amt,2);
1933 --
1934 hr_utility.set_location('~~NS:Converted amount: '||l_converted_amt,8);
1935 hr_utility.set_location('Leaving:'||l_proc, 25);
1936 --
1937 RETURN l_converted_amt;
1938 --
1939 End;
1940 --
1941 ---------------------------Calculate_overhead---------------------------
1942 --
1943 FUNCTION Calculate_overhead(p_element_commitment in number,
1944 p_overhead_percentage in number,
1945 p_element_overhead out nocopy number)
1946 RETURN NUMBER
1947 is
1948 --
1949 l_message_text_out fnd_new_messages.message_text%TYPE;
1950 --
1951 l_proc varchar2(72) := g_package||'calculate_overhead';
1952 --
1953 Begin
1954 --
1955 hr_utility.set_location('Entering:'||l_proc, 5);
1956 --
1957 If p_overhead_percentage < 0 then
1958 -- Log error
1959 -- get message text for PQH_NEG_OVERHEAD_PERCENT
1960 --
1961 FND_MESSAGE.SET_NAME('PQH','PQH_NEG_OVERHEAD_PERCENT');
1962 l_message_text_out := FND_MESSAGE.GET;
1963 --
1964 pqh_process_batch_log.insert_log
1965 (
1966 p_message_type_cd => 'ERROR',
1967 p_message_text => l_message_text_out
1968 );
1969 RETURN -1;
1970 ElsIf p_overhead_percentage = 0 then
1971 p_element_overhead := 0;
1972 Else
1973 p_element_overhead := (p_overhead_percentage/100)*p_element_commitment;
1974 End if;
1975 --
1976 hr_utility.set_location('Leaving:'||l_proc, 10);
1977 --
1978 RETURN 0;
1979 --
1980 exception when others then
1981 return null;
1982 raise;
1983 End;
1984 --
1985 --------------------------------------------------------------------------------
1986 --
1987 PROCEDURE fetch_bdgt_cmmtmnt_elmnts(p_budget_id in number,
1988 p_bdgt_cmmtmnt_elmnts out nocopy cmmtmnt_elmnts_tab)
1989 is
1990 --
1991 cnt number(15) := 0;
1992 --
1993 -- This cursor selects all the element types for which commitment has
1994 -- to be calculated for a given budget.
1995 --
1996 Cursor csr_bdgt_commit_elmnts(p_budget_id in number) is
1997 Select Element_type_id,
1998 Formula_id,Salary_basis_flag,
1999 Element_input_value_id,
2000 dflt_elmnt_frequency,nvl(Overhead_percentage,0)
2001 From pqh_bdgt_cmmtmnt_elmnts
2002 Where budget_id = p_budget_id
2003 and actual_commitment_type in ('COMMITMENT','BOTH');
2004 --
2005 -- This cursor is used to get the budget versions if there are no elements
2006 -- defined for a budget.
2007 --
2008 Cursor csr_bdgt_version is
2009 select budget_version_id from pqh_budget_versions
2010 where budget_id = p_budget_id;
2011 --
2012 l_budget_version_id number;
2013 --
2014 l_proc varchar2(72) := g_package||'fetch_bdgt_cmmtmnt_elmnts';
2015 --
2016 Begin
2017 --
2018 hr_utility.set_location('Entering:'||l_proc, 5);
2019 --
2020 Open csr_bdgt_commit_elmnts(p_budget_id => p_budget_id);
2021 --
2022 -- CALCULATE COMMITMENT FOR EACH ELEMENT TYPE OF A POSITION
2023 --
2024 Loop
2025 --
2026 cnt := cnt + 1;
2027 --
2028 Fetch csr_bdgt_commit_elmnts into
2029 p_bdgt_cmmtmnt_elmnts(cnt).Element_type_id,
2030 p_bdgt_cmmtmnt_elmnts(cnt).Formula_id,
2031 p_bdgt_cmmtmnt_elmnts(cnt).Salary_basis_flag,
2032 p_bdgt_cmmtmnt_elmnts(cnt).Element_input_value_id,
2033 p_bdgt_cmmtmnt_elmnts(cnt).dflt_elmnt_frequency,
2034 p_bdgt_cmmtmnt_elmnts(cnt).Overhead_percentage;
2035 --
2036 If csr_bdgt_commit_elmnts%notfound then
2037 exit;
2038 End if;
2039 --
2040 End loop;
2041 --
2042 Close csr_bdgt_commit_elmnts;
2043 --
2044 cnt := cnt - 1;
2045 --
2046 If cnt = 0 then
2047 --
2048 -- Delete commitment value records if a budget does not have
2049 -- commitment elements.
2050 --
2051 Open csr_bdgt_version;
2052 Loop
2053 Fetch csr_bdgt_version into l_budget_version_id;
2054 --
2055 Delete from pqh_element_commitments
2056 where budget_version_id = l_budget_version_id;
2057 commit;
2058 --
2059 If csr_bdgt_version%notfound then
2060 exit;
2061 End if;
2062 End loop;
2063 --
2064 Close csr_bdgt_version;
2065 --
2066 FND_MESSAGE.SET_NAME('PQH','PQH_NO_BDGT_CMMTMNT_EMNTS');
2067 APP_EXCEPTION.RAISE_EXCEPTION;
2068 --
2069 End if;
2070 --
2071 hr_utility.set_location('Leaving:'||l_proc, 10);
2072 --
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 raise;
2076 --
2077 End;
2078 --
2079 FUNCTION check_non_susp_assignment(p_commit_calculation_dt in date,
2080 p_assignment_id in number )
2081 RETURN BOOLEAN
2082 is
2083 cursor curs_susp_chk is
2084 SELECT 1
2085 FROM per_assignments_f asg, per_assignment_status_types ast
2086 WHERE asg.assignment_id = p_assignment_id
2087 AND p_commit_calculation_dt between asg.effective_start_date and asg.effective_end_date
2088 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2089 AND ast.per_system_status <> 'TERM_ASSIGN'
2090 AND (ast.per_system_status <> 'SUSP_ASSIGN' OR (ast.per_system_status = 'SUSP_ASSIGN' AND ast.pay_system_status = 'P') );
2091
2092 l_dummy number;
2093 l_is_not_susp boolean := false;
2094 l_proc varchar2(72) := g_package||'check_non_susp_assignment';
2095 --
2096 Begin
2097 hr_utility.set_location('Entering:'||l_proc, 5);
2098 --
2099 --
2100 Open curs_susp_chk;
2101 Fetch curs_susp_chk into l_dummy;
2102 if (l_dummy = 1)
2103 THEN
2104 l_is_not_susp :=true;
2105 End if;
2106 Close curs_susp_chk;
2107 --
2108 hr_utility.set_location('Leaving:'||l_proc, 5);
2109 Return l_is_not_susp;
2110 Exception
2111 When others then
2112 raise;
2113 End check_non_susp_assignment;
2114
2115 -----------------------populate_commitment_table---------------------------------
2116 --
2117 FUNCTION populate_commitment_table(p_budgeted_entity_cd in varchar2,
2118 p_commit_calculation_dt in date,
2119 p_actual_cmmtmnt_start_dt in date,
2120 p_commit_end_dt in date,
2121 p_actual_cmmtmnt_end_dt in date,
2122 p_commitment_calc_frequency in varchar2,
2123 p_budget_calendar_frequency in varchar2,
2124 p_entity_id in number,
2125 p_budget_id in number,
2126 p_budget_version_id in number,
2127 p_assignment_id in number default null)
2128 RETURN NUMBER
2129 is
2130 --
2131 -- Cursor added for LD integration with Position Control
2132 --This cursor checks if Budget is a position controll or not
2133 --
2134 Cursor csr_posctrl_budget_chk IS
2135 Select 1
2136 From PQH_BUDGETS BGT
2137 Where BGT.BUDGET_ID =p_budget_id And
2138 BGT.POSITION_CONTROL_FLAG ='Y';
2139 /* BGT.budgeted_entity_cd='POSITION' And LD Integration is not limited for Position Control Budgets only
2140 BGT.transfer_to_grants_flags ='Y' And We will Check for ecumbrance even when Budget is not
2141 being transfered to grants
2142 */
2143 -- This cursor selects all active assignments for a position and the pay
2144 -- basis of the assignment.Also select business_group_id,payroll id,
2145 -- normal hours,frequency for the assignment.
2146 --
2147 Cursor csr_pos_assignments(p_commit_calculation_dt in date,
2148 p_commit_end_dt in date,
2149 p_position_id in number) is
2150 Select assignment_id,pay_basis_id,
2151 business_group_id,payroll_id,
2152 normal_hours,frequency,
2153 effective_start_date,
2154 effective_end_date
2155 From per_all_assignments_f
2156 Where position_id = p_position_id
2157 And p_commit_calculation_dt <= effective_end_date
2158 and p_commit_end_dt >= effective_start_date;
2159
2160
2161 Cursor csr_pos_single_assignment(p_assignment_id in number) is
2162 Select assignment_id,pay_basis_id,
2163 business_group_id,payroll_id,
2164 normal_hours,frequency,
2165 effective_start_date,
2166 effective_end_date
2167 From per_all_assignments_f
2168 Where assignment_id = p_assignment_id
2169 And p_commit_calculation_dt <= effective_end_date
2170 and p_commit_end_dt >= effective_start_date;
2171 --
2172 --
2173 Cursor csr_org_assignments(p_commit_calculation_dt in date,
2174 p_commit_end_dt in date,
2175 p_organization_id in number) is
2176 Select assignment_id,pay_basis_id,
2177 business_group_id,payroll_id,
2178 normal_hours,frequency,
2179 effective_start_date,
2180 effective_end_date
2181 From per_all_assignments_f
2182 Where organization_id = p_organization_id
2183 And p_commit_calculation_dt <= effective_end_date
2184 and p_commit_end_dt >= effective_start_date;
2185 --
2186 Cursor csr_job_assignments(p_commit_calculation_dt in date,
2187 p_commit_end_dt in date,
2188 p_job_id in number) is
2189 Select assignment_id,pay_basis_id,
2190 business_group_id,payroll_id,
2191 normal_hours,frequency,
2192 effective_start_date,
2193 effective_end_date
2194 From per_all_assignments_f
2195 Where job_id = p_job_id
2196 And p_commit_calculation_dt <= effective_end_date
2197 and p_commit_end_dt >= effective_start_date;
2198
2199 --
2200 Cursor csr_grade_assignments(p_commit_calculation_dt in date,
2201 p_commit_end_dt in date,
2202 p_grade_id in number) is
2203 Select assignment_id,pay_basis_id,
2204 business_group_id,payroll_id,
2205 normal_hours,frequency,
2206 effective_start_date,
2207 effective_end_date
2208 From per_all_assignments_f
2209 Where grade_id = p_grade_id
2210 And p_commit_calculation_dt <= effective_end_date
2211 and p_commit_end_dt >= effective_start_date;
2212
2213 --
2214 --
2215 -- DECLARE all local variables.
2216 --
2217 --define a record to store the details.
2218 type entity_assignment_rec is record (
2219 assignment_id per_all_assignments_f.assignment_id%type,
2220 pay_basis_id per_all_assignments_f.pay_basis_id%type,
2221 business_group_id per_all_assignments_f.business_group_id%type,
2222 payroll_id per_all_assignments_f.payroll_id%type,
2223 normal_hours per_all_assignments_f.normal_hours%type,
2224 frequency per_all_assignments_f.frequency%type,
2225 effective_start_date per_all_assignments_f.effective_start_date%type,
2226 effective_end_date per_all_assignments_f.effective_end_date%type
2227 );
2228
2229 type element_commitment_rec is record (
2230 assignment_id per_all_assignments_f.assignment_id%type,
2231 commitment number,
2232 element_type_id pqh_bdgt_cmmtmnt_elmnts.element_type_id%type
2233 );
2234
2235
2236
2237 --
2238 type entity_assignments_tab is table of entity_assignment_rec index by binary_integer;
2239 type element_commitments_tab is table of element_commitment_rec index by binary_integer;
2240
2241 --
2242 t_entity_assignments entity_assignments_tab;
2243 t_element_commitment element_commitments_tab;
2244 l_entity_rec_cnt NUMBER := 0;
2245
2246 --
2247 l_assignment_commitment number;
2248 l_entry_commitment number;
2249 l_element_overhead number;
2250 --
2251 l_message_text_out fnd_new_messages.message_text%TYPE;
2252 cnt number;
2253 --
2254 l_proc varchar2(72) := g_package||'populate_commitment_table';
2255 --
2256 l_status number;
2257 --
2258 -- Variables for LD integration with position control
2259 l_dummy number;
2260 ld_is_present boolean :=false;
2261 l_psp_asg_encumbered boolean :=false;
2262 ld_return_status varchar2(10);
2263 l_asg_detail_tab psp_pqh_integration.encumbrance_table_rec_col;
2264 ld_call_error exception;
2265 l_is_assign_susp boolean := false;
2266 already_exists_flag boolean := false;
2267 --
2268 Begin
2269 --
2270 hr_utility.set_location('Entering:'||l_proc, 5);
2271 --
2272 hr_utility.set_location('Checking whether Budget is a Position Control and Ld profile turned on', 6);
2273
2274 --
2275 --Check whether we need to take LD encumbrnace in to consideration.
2276 --If budget is a Control Budget and LD profile is turned on consider LD Assignment Encumbrance
2277 --
2278 Open csr_posctrl_budget_chk;
2279 Fetch csr_posctrl_budget_chk into l_dummy;
2280 if (csr_posctrl_budget_chk%FOUND AND fnd_profile.value('PSP_ENC_ENABLE_PQH')='Y') THEN
2281 ld_is_present :=true;
2282 End if;
2283 Close csr_posctrl_budget_chk;
2284 --
2285 hr_utility.set_location('CALCULATING FOR :'||to_char(p_actual_cmmtmnt_start_dt,'DD/MM/RRRR'),7);
2286 --
2287 --
2288 -- CALCULATE COMMITMENT FOR EACH ASSIGMENT IN A POSITION WHICH IS EFFECTIVE
2289 -- AS OF THE COMMIMENT CALCULATION DATE.
2290 --
2291 If p_entity_id is NOT NULL then
2292 --
2293 If p_budgeted_entity_cd ='POSITION' then
2294 ---
2295 if (p_assignment_id is null) then
2296 --
2297 hr_utility.set_location('Opening the assignements based on entity id ' ||p_entity_id ||' '||p_actual_cmmtmnt_start_dt||' '||p_actual_cmmtmnt_end_dt ,10);
2298
2299 Open csr_pos_assignments( p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2300 p_commit_end_dt => p_actual_cmmtmnt_end_dt,
2301 p_position_id => p_entity_id) ;
2302 --
2303 Else
2304 hr_utility.set_location('Opening the assignements based on assignment id ' ||p_assignment_id ,10);
2305 Open csr_pos_single_assignment(p_assignment_id => p_assignment_id) ;
2306 End if;
2307 --
2308 Elsif p_budgeted_entity_cd ='JOB' then
2309 ---
2310 hr_utility.set_location('Opening the assignements based on Job id ' ,10);
2311 Open csr_job_assignments(p_job_id => p_entity_id,
2312 p_commit_end_dt => p_actual_cmmtmnt_end_dt,
2313 p_commit_calculation_dt => p_actual_cmmtmnt_start_dt) ;
2314 --
2315 Elsif p_budgeted_entity_cd ='GRADE' then
2316 --
2317 hr_utility.set_location('Opening the assignements based on Grade id ' ,10);
2318 Open csr_grade_assignments(p_grade_id => p_entity_id,
2319 p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2320 p_commit_end_dt => p_actual_cmmtmnt_end_dt) ;
2321 --
2322 Elsif p_budgeted_entity_cd ='ORGANIZATION' then
2323 --
2324 hr_utility.set_location('Opening the assignements based on ORGANIZATION id ' ,10);
2325 Open csr_org_assignments(p_organization_id => p_entity_id,
2326 p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2327 p_commit_end_dt => p_actual_cmmtmnt_end_dt) ;
2328 --
2329 End if;
2330 --
2331 End if;
2332 --Loop
2333 --
2334
2335 hr_utility.set_location('--Next assignment--',10);
2336 savepoint assignment_level;
2337 --
2338 l_entity_rec_cnt := 0;
2339 If p_entity_id is NOT NULL then
2340 --
2341 If p_budgeted_entity_cd ='POSITION' then
2342 --
2343 If (p_assignment_id is null) then
2344 --
2345 loop
2346 l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2347 hr_utility.set_location('Fetching the assignements based on Position ' ||l_entity_rec_cnt ,10);
2348 Fetch csr_pos_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2349 t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2350 t_entity_assignments(l_entity_rec_cnt).business_group_id,
2351 t_entity_assignments(l_entity_rec_cnt).payroll_id,
2352 t_entity_assignments(l_entity_rec_cnt).normal_hours,
2353 t_entity_assignments(l_entity_rec_cnt).frequency,
2354 t_entity_assignments(l_entity_rec_cnt).effective_start_date,
2355 t_entity_assignments(l_entity_rec_cnt).effective_end_date;
2356 --
2357 If csr_pos_assignments%notfound then
2358 Close csr_pos_assignments;
2359 Exit;
2360 End if;
2361 end loop;
2362 --
2363 Else
2364 --
2365 loop
2366 l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2367 hr_utility.set_location('Fetching the assignements based on assignment id ' ||p_assignment_id ,10);
2368 Fetch csr_pos_single_assignment into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2369 t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2370 t_entity_assignments(l_entity_rec_cnt).business_group_id,
2371 t_entity_assignments(l_entity_rec_cnt).payroll_id,
2372 t_entity_assignments(l_entity_rec_cnt).normal_hours,
2373 t_entity_assignments(l_entity_rec_cnt).frequency,
2374 t_entity_assignments(l_entity_rec_cnt).effective_start_date,
2375 t_entity_assignments(l_entity_rec_cnt).effective_end_date;
2376 --
2377 If csr_pos_single_assignment%notfound then
2378 Close csr_pos_single_assignment;
2379 Exit;
2380 End if;
2381 end loop;
2382 --
2383 End if;
2384 --
2385 Elsif p_budgeted_entity_cd ='JOB' then
2386 ---
2387 loop
2388 l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2389 hr_utility.set_location('Fetching the assignements based on job ' ,10);
2390 Fetch csr_job_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2391 t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2392 t_entity_assignments(l_entity_rec_cnt).business_group_id,
2393 t_entity_assignments(l_entity_rec_cnt).payroll_id,
2394 t_entity_assignments(l_entity_rec_cnt).normal_hours,
2395 t_entity_assignments(l_entity_rec_cnt).frequency,
2396 t_entity_assignments(l_entity_rec_cnt).effective_start_date,
2397 t_entity_assignments(l_entity_rec_cnt).effective_end_date;
2398 --
2399 If csr_job_assignments%notfound then
2400 Close csr_job_assignments;
2401 Exit;
2402 End if;
2403 end loop;
2404 ---
2405 Elsif p_budgeted_entity_cd ='GRADE' then
2406 ---
2407 loop
2408 l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2409 hr_utility.set_location('Fetching the assignements based on Grade ' ,10);
2410 Fetch csr_grade_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2411 t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2412 t_entity_assignments(l_entity_rec_cnt).business_group_id,
2413 t_entity_assignments(l_entity_rec_cnt).payroll_id,
2414 t_entity_assignments(l_entity_rec_cnt).normal_hours,
2415 t_entity_assignments(l_entity_rec_cnt).frequency,
2416 t_entity_assignments(l_entity_rec_cnt).effective_start_date,
2417 t_entity_assignments(l_entity_rec_cnt).effective_end_date;
2418 --
2419 If csr_grade_assignments%notfound then
2420 Close csr_grade_assignments;
2421 Exit;
2422 End if;
2423 END loop;
2424 ---
2425 Elsif p_budgeted_entity_cd ='ORGANIZATION' then
2426 ---
2427 loop
2428 l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2429 hr_utility.set_location('Fetching the assignements based on Org ' ,10);
2430 Fetch csr_org_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2431 t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2432 t_entity_assignments(l_entity_rec_cnt).business_group_id,
2433 t_entity_assignments(l_entity_rec_cnt).payroll_id,
2434 t_entity_assignments(l_entity_rec_cnt).normal_hours,
2435 t_entity_assignments(l_entity_rec_cnt).frequency,
2436 t_entity_assignments(l_entity_rec_cnt).effective_start_date,
2437 t_entity_assignments(l_entity_rec_cnt).effective_end_date;
2438 --
2439 If csr_org_assignments%notfound then
2440 Close csr_org_assignments;
2441 Exit;
2442 End if;
2443 END loop ;
2444 End if;
2445 --
2446 End if;
2447 --
2448 --Check if LD encumbered for this Assignment during given period.
2449 --If yes then we need not calculate any commitments for this Assignment and Period;we will
2450 --delete any existing records from pqh_element_commitmetns for that Assignment and Period
2451
2452 hr_utility.set_location('Fetched all the records - Record Count '||t_entity_assignments.COUNT ,20);
2453 --
2454
2455 for assign_cnt in NVL(t_entity_assignments.FIRST,0)..NVL(t_entity_assignments.LAST,-1)
2456 loop
2457 hr_utility.set_location('t_entity_assignments(assign_cnt).effective_start_date '||t_entity_assignments(assign_cnt).effective_start_date ,20);
2458 hr_utility.set_location('p_actual_cmmtmnt_start_dt '||p_actual_cmmtmnt_start_dt ,20);
2459 hr_utility.set_location('t_entity_assignments(assign_cnt).effective_end_date '||t_entity_assignments(assign_cnt).effective_end_date ,20);
2460 hr_utility.set_location('p_actual_cmmtmnt_end_dt '||p_actual_cmmtmnt_end_dt ,20);
2461
2462 --Change the commitment_start_date and commitment_end_date as per the
2463 --assigment details. So that the commitments for the date tracked assigment is calculated correctly.
2464 if t_entity_assignments(assign_cnt).effective_start_date < p_actual_cmmtmnt_start_dt then
2465 hr_utility.set_location('Adjusting effective start date'||p_actual_cmmtmnt_start_dt ,20);
2466 t_entity_assignments(assign_cnt).effective_start_date := p_actual_cmmtmnt_start_dt ;
2467 end if;
2468
2469 if nvl(t_entity_assignments(assign_cnt).effective_end_date,to_date('31-12-4712','DD-MM-RRRR')) > p_actual_cmmtmnt_end_dt then
2470 hr_utility.set_location('Adjusting effective end date' ||p_actual_cmmtmnt_end_dt ,20);
2471 t_entity_assignments(assign_cnt).effective_end_date := p_actual_cmmtmnt_end_dt ;
2472 end if;
2473
2474 hr_utility.set_location('Assignment_id :'||to_char(t_entity_assignments(assign_cnt).assignment_id),12);
2475 l_psp_asg_encumbered :=false;
2476 IF (ld_is_present) THEN
2477 hr_utility.set_location('Caling GET_ASG_ENCUMBRANCES with following Params :',13);
2478 hr_utility.set_location('Encumbrace Start Date :'||to_char(p_actual_cmmtmnt_start_dt),14);
2479 hr_utility.set_location('Encumbrance End Date :'||to_char(p_actual_cmmtmnt_end_dt),15);
2480 PSP_PQH_INTEGRATION.GET_ASG_ENCUMBRANCES( P_ASSIGNMENT_ID =>t_entity_assignments(assign_cnt).assignment_id,
2481 P_ENCUMBRANCE_START_DATE => t_entity_assignments(assign_cnt).effective_start_date,
2482 P_ENCUMBRANCE_END_DATE => t_entity_assignments(assign_cnt).effective_end_date,
2483 P_ENCUMBRANCE_TABLE =>l_asg_detail_tab,
2484 P_ASG_PSP_ENCUMBERED =>l_psp_asg_encumbered,
2485 P_RETURN_STATUS =>ld_return_status);
2486
2487 IF(ld_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
2488 RAISE ld_call_error;
2489 END IF;
2490 IF(l_psp_asg_encumbered) THEN
2491 hr_utility.set_location('LD Encumbered this Assignment:'||to_char(t_entity_assignments(assign_cnt).assignment_id),16);
2492 END IF;
2493 END IF;
2494 l_is_assign_susp := check_non_susp_assignment(t_entity_assignments(assign_cnt).effective_start_date,
2495 t_entity_assignments(assign_cnt).assignment_id);
2496 --
2497 --Process this Assignment only if LD has not encumbered it
2498 --
2499 if (l_is_assign_susp = true) THEN
2500 if( l_psp_asg_encumbered= false) THEN
2501 hr_utility.set_location('Assignment not Encumbered by LD:'||to_char(t_entity_assignments(assign_cnt).assignment_id),18);
2502 --
2503 -- FOR EACH ASSIGNMENT CALCULATE ELEMENT LEVEL COMMITMENT
2504 --
2505 For cnt in NVL(g_bdgt_cmmtmnt_elmnts.FIRST,0)..NVL(g_bdgt_cmmtmnt_elmnts.LAST,-1)
2506 Loop
2507
2508 --
2509 hr_utility.set_location('ELMNT TYPE:'||to_char(g_bdgt_cmmtmnt_elmnts(cnt).element_type_id),19);
2510 --
2511 --
2512 l_entry_commitment := 0;
2513 --
2514 hr_utility.set_location('p_actual_cmmtmnt_start_dt'||p_actual_cmmtmnt_start_dt,20);
2515 hr_utility.set_location('p_actual_cmmtmnt_end_dt'||p_actual_cmmtmnt_end_dt,20);
2516 --
2517 If g_bdgt_cmmtmnt_elmnts(cnt).formula_id is not null then
2518 --
2519 hr_utility.set_location('Calculating for Formula',20);
2520 --
2521 l_entry_commitment := get_commitment_from_formula (p_formula_id => g_bdgt_cmmtmnt_elmnts(cnt).formula_id,
2522 p_business_group_id => t_entity_assignments(assign_cnt).business_group_id,
2523 p_assignment_id => t_entity_assignments(assign_cnt).assignment_id,
2524 p_payroll_id => t_entity_assignments(assign_cnt).payroll_id,
2525 p_element_type_id => g_bdgt_cmmtmnt_elmnts(cnt).element_type_id,
2526 p_effective_date => p_actual_cmmtmnt_start_dt,
2527 p_element_input_value_id => g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id,
2528 p_commitment_start_date => t_entity_assignments(assign_cnt).effective_start_date,
2529 p_commitment_end_date => t_entity_assignments(assign_cnt).effective_end_date);
2530 --
2531 ElsIf g_bdgt_cmmtmnt_elmnts(cnt).salary_basis_flag = 'Y' then
2532 --
2533 hr_utility.set_location('Calculating for Salary Basis',21);
2534 --
2535 -- The commitment for this element type is to be calculated
2536 -- from salary basis;
2537 --
2538 -- If pay basis id is available then calculate commitment based
2539 -- on salary basis.Ideally pay basis id should be available. If
2540 -- it is not available , we will try calculating commitment
2541 -- based on element input value id.
2542 --
2543 If t_entity_assignments(assign_cnt).pay_basis_id IS NOT NULL then
2544 --
2545 --
2546 hr_utility.set_location('Valid Pay Basis id',25);
2547 --
2548 l_entry_commitment := get_commitment_from_sal_basis( p_commit_calculation_dt => p_commit_calculation_dt,
2549 p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2550 p_commit_calculation_end_dt => p_commit_end_dt,
2551 p_actual_cmmtmnt_end_dt => t_entity_assignments(assign_cnt).effective_end_date,
2552 p_element_type_id => g_bdgt_cmmtmnt_elmnts(cnt).element_type_id,
2553 p_commitment_calc_frequency => p_commitment_calc_frequency,
2554 p_budget_calendar_frequency => p_budget_calendar_frequency,
2555 p_dflt_elmnt_frequency => g_bdgt_cmmtmnt_elmnts(cnt).dflt_elmnt_frequency,
2556 p_business_group_id => t_entity_assignments(assign_cnt).business_group_id,
2557 p_assignment_id => t_entity_assignments(assign_cnt).assignment_id,
2558 p_payroll_id => t_entity_assignments(assign_cnt).payroll_id,
2559 p_pay_basis_id => t_entity_assignments(assign_cnt).pay_basis_id,
2560 p_normal_hours => t_entity_assignments(assign_cnt).normal_hours,
2561 p_asst_frequency => t_entity_assignments(assign_cnt).frequency);
2562 --
2563 Else
2564 --
2565 hr_utility.set_location('Pay basis id is NULL',30);
2566 --
2567 If g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id IS NOT NULL then
2568 --
2569 hr_utility.set_location('Default to element type calc',35);
2570 --
2571 l_entry_commitment := get_commitment_from_elmnt_type( p_commit_calculation_dt => p_commit_calculation_dt,
2572 p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2573 p_commit_calculation_end_dt => p_commit_end_dt,
2574 p_actual_cmmtmnt_end_dt => t_entity_assignments(assign_cnt).effective_end_date,
2575 p_commitment_calc_frequency => p_commitment_calc_frequency,
2576 p_budget_calendar_frequency => p_budget_calendar_frequency,
2577 p_dflt_elmnt_frequency => g_bdgt_cmmtmnt_elmnts(cnt).dflt_elmnt_frequency,
2578 p_business_group_id => t_entity_assignments(assign_cnt).business_group_id,
2579 p_assignment_id => t_entity_assignments(assign_cnt).assignment_id,
2580 p_payroll_id => t_entity_assignments(assign_cnt).payroll_id,
2581 p_pay_basis_id => t_entity_assignments(assign_cnt).pay_basis_id,
2582 p_element_type_id => g_bdgt_cmmtmnt_elmnts(cnt).element_type_id,
2583 p_element_input_value_id => g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id,
2584 p_normal_hours => t_entity_assignments(assign_cnt).normal_hours,
2585 p_asst_frequency => t_entity_assignments(assign_cnt).frequency);
2586 --
2587 Else
2588 -- Log error
2589 -- get message text for PQH_ERROR_ELMNT_CMMTMNT
2590 --
2591 FND_MESSAGE.SET_NAME('PQH','PQH_ERROR_ELMNT_CMMTMNT');
2592 l_message_text_out := FND_MESSAGE.GET;
2593 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => l_message_text_out );
2594 --RETURN -1;
2595 rollback to assignment_level;
2596 if (p_assignment_id is Null) then
2597 Exit;
2598 end if;
2599 --
2600 End if;
2601 --
2602 End if;
2603 --
2604 Else
2605 --
2606 hr_utility.set_location('Calc from element type',40);
2607 --
2608 l_entry_commitment := get_commitment_from_elmnt_type( p_commit_calculation_dt =>p_commit_calculation_dt,
2609 p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2610 p_commit_calculation_end_dt => p_commit_end_dt,
2611 p_actual_cmmtmnt_end_dt => t_entity_assignments(assign_cnt).effective_end_date,
2612 p_commitment_calc_frequency => p_commitment_calc_frequency,
2613 p_budget_calendar_frequency => p_budget_calendar_frequency,
2614 p_dflt_elmnt_frequency => g_bdgt_cmmtmnt_elmnts(cnt).dflt_elmnt_frequency,
2615 p_business_group_id => t_entity_assignments(assign_cnt).business_group_id,
2616 p_assignment_id => t_entity_assignments(assign_cnt).assignment_id,
2617 p_payroll_id => t_entity_assignments(assign_cnt).payroll_id,
2618 p_pay_basis_id => t_entity_assignments(assign_cnt).pay_basis_id,
2619 p_element_type_id => g_bdgt_cmmtmnt_elmnts(cnt).element_type_id,
2620 p_element_input_value_id => g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id,
2621 p_normal_hours => t_entity_assignments(assign_cnt).normal_hours,
2622 p_asst_frequency => t_entity_assignments(assign_cnt).frequency);
2623 --
2624 --
2625 End if;
2626 --
2627 hr_utility.set_location('Entry commitment :'||to_char(nvl(l_entry_commitment,0)),45);
2628 --
2629 --
2630 l_status := Calculate_overhead (p_element_commitment => l_entry_commitment,
2631 p_overhead_percentage => g_bdgt_cmmtmnt_elmnts(cnt).Overhead_percentage,
2632 p_element_overhead => l_element_overhead);
2633 --
2634 hr_utility.set_location('Calculate_overhead finished:'||l_status,45);
2635 If l_status = -1 then
2636 rollback to assignment_level;
2637 Exit;
2638 End if;
2639 hr_utility.set_location('Calculate_overhead is success',45);
2640 --
2641 --
2642 -- If commitment is being re-generated for the same position and same
2643 -- dates we will delete the old record with the commitment values .
2644 -- Then we will insert a new record.
2645 --
2646 --Adding into the plsql table if the commitments are not already added otherwise updating and then performing insert to avoid duplicate datas.
2647
2648 if t_element_commitment.FIRST is null then
2649 hr_utility.set_location('First element is created in t_element_commitment',40);
2650 t_element_commitment(1).assignment_id := t_entity_assignments(assign_cnt).assignment_id ;
2651 t_element_commitment(1).element_type_id := g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ;
2652 t_element_commitment(1).commitment := (l_entry_commitment+l_element_overhead) ;
2653 else
2654 hr_utility.set_location('Its not the first element',40);
2655 already_exists_flag := false;
2656 for element_cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) loop
2657 hr_utility.set_location('inside for loop',40);
2658 hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2659 hr_utility.set_location('element type'||t_element_commitment(element_cnt).ELEMENT_TYPE_ID ||'x'||g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ,40);
2660 if (t_element_commitment(element_cnt).assignment_id = t_entity_assignments(assign_cnt).assignment_id and
2661 t_element_commitment(element_cnt).ELEMENT_TYPE_ID = g_bdgt_cmmtmnt_elmnts(cnt).element_type_id) then
2662 hr_utility.set_location('Already existing value is updated cnt is:'||element_cnt,40);
2663 t_element_commitment(element_cnt).commitment := t_element_commitment(element_cnt).commitment + l_entry_commitment+l_element_overhead ;
2664 already_exists_flag := true;
2665 EXit;
2666 end if;
2667 end loop;
2668 if (not already_exists_flag ) then
2669 declare
2670 element_cnt number;
2671 begin
2672 element_cnt := t_element_commitment.COUNT+1 ;
2673 hr_utility.set_location('new element is created @:'||(t_element_commitment.COUNT+1),40);
2674 t_element_commitment(element_cnt).assignment_id := t_entity_assignments(assign_cnt).assignment_id ;
2675 t_element_commitment(element_cnt).ELEMENT_TYPE_ID := g_bdgt_cmmtmnt_elmnts(cnt).element_type_id;
2676 t_element_commitment(element_cnt).commitment := l_entry_commitment+l_element_overhead ;
2677 end;
2678 end if;
2679 end if; --
2680 --
2681 --
2682 End loop;
2683 /** element type commitment calcultion **/
2684 --
2685 Else
2686 /** l_psp_asg_encumbered= true LD has encumbered **/
2687 -- LD Encumbered for this assignment , so there will be no PQH commitmens for this assignment
2688 --. Delete Assignment and Period records from pqh_commitment_elements
2689 --
2690 hr_utility.set_location('Delete LD encumbered commitments'||to_char(t_entity_assignments(assign_cnt).assignment_id),55);
2691 --
2692 DELETE
2693 FROM pqh_element_commitments
2694 WHERE budget_version_id = p_budget_version_id
2695 AND ASSIGNMENT_ID = t_entity_assignments(assign_cnt).assignment_id
2696 AND (COMMITMENT_START_DATE BETWEEN p_actual_cmmtmnt_start_dt AND p_actual_cmmtmnt_end_dt
2697 OR p_actual_cmmtmnt_start_dt BETWEEN COMMITMENT_START_DATE AND COMMITMENT_END_DATE);
2698 End if;
2699 /** End of check for LD encumbrance **/
2700 End if;
2701 /** End of check for suspended assignment **/
2702 End loop;
2703 hr_utility.set_location('Consolidated the values now proceeding to insert',48);
2704 -- Printing the content of the t_element_commitment
2705 FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2706 hr_utility.set_location(t_element_commitment(cnt).element_type_id||'x'||t_element_commitment(cnt).assignment_id||'x'||t_element_commitment(cnt).commitment,48);
2707 end loop;
2708 --end printing
2709 FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2710 hr_utility.set_location('Insert commitment'||cnt,48);
2711 hr_utility.set_location('ELEMENT_TYPE_ID: '|| t_element_commitment(cnt).element_type_id||'ASSIGNMENT_ID: '||t_element_commitment(cnt).assignment_id,48);
2712 hr_utility.set_location('BUDGET_VERSION_ID: '||p_BUDGET_VERSION_ID,48);
2713 --
2714 INSERT
2715 INTO pqh_element_commitments
2716 (
2717 ELEMENT_COMMITMENT_ID ,
2718 BUDGET_VERSION_ID,
2719 ASSIGNMENT_ID,
2720 ELEMENT_TYPE_ID,
2721 COMMITMENT_START_DATE,
2722 COMMITMENT_END_DATE,
2723 COMMITMENT_CALC_FREQUENCY,
2724 COMMITMENT_AMOUNT,
2725 CREATION_DATE,
2726 CREATED_BY
2727 )
2728 VALUES
2729 (
2730 pqh_element_commitments_s.nextval ,
2731 p_budget_version_id,
2732 t_element_commitment(cnt).assignment_id,
2733 t_element_commitment(cnt).element_type_id,
2734 p_actual_cmmtmnt_start_dt,
2735 p_actual_cmmtmnt_end_dt,
2736 p_commitment_calc_frequency,
2737 t_element_commitment(cnt).commitment,
2738 sysdate,
2739 -1
2740 )
2741 ;
2742 hr_utility.set_location('Insert commitment2',48);
2743 end loop;
2744
2745 /** assignment commitment calculation **/
2746 --
2747 hr_utility.set_location('Leaving:'||l_proc, 60);
2748 --
2749 RETURN 0;
2750 --
2751 Exception
2752 When ld_call_error then
2753 -- Log error
2754
2755 hr_utility.set_location('Exception raised when inserting record into elements table',1);
2756 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );
2757 RETURN -1;
2758 When others then
2759 -- Log error
2760
2761 hr_utility.set_location('Exception raised when inserting record into elements table',2);
2762 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );
2763 RETURN -1;
2764 End POPULATE_COMMITMENT_TABLE;
2765
2766
2767 --
2768 PROCEDURE relieve_commitment(
2769 errbuf out nocopy varchar2,
2770 retcode out nocopy varchar2,
2771 p_effective_date in varchar2,
2772 p_budgeted_entity_cd in varchar2,
2773 p_budget_version_id in number,
2774 p_post_to_period_name in varchar2) is
2775 begin
2776 null;
2777 end;
2778 ---------------------------calculate_commitment--------------------------
2779 --
2780 -- This is the main function that calculates commitment for a budget_version
2781 -- or position.Also commitment is calculated only for money . For other UOM's
2782 -- the commitment for the required period is already available in the
2783 -- assignment budget values and hence there is no calculation required.
2784 --
2785 -- 2288274 Added paramenters p_budgeted_entity_cd, p_entity_id
2786 -- p_budget_version_id depends on p_budged_entity_cd
2787 --
2788 PROCEDURE calculate_commitment(
2789 errbuf out nocopy varchar2,
2790 retcode out nocopy varchar2,
2791 p_budgeted_entity_cd in varchar2,
2792 p_budget_version_id in number,
2793 p_entity_id in number default null,
2794 /* p_cmmtmnt_start_dt in varchar2,
2795 p_cmmtmnt_end_dt in varchar2,*/
2796 p_period_frequency in varchar2 default null)
2797 IS
2798 --
2799 l_proc varchar2(72) := g_package||'calculate_commitment';
2800 --
2801 /*l_cmmtmnt_start_dt date;
2802 l_cmmtmnt_end_dt date;*/
2803 --
2804 Begin
2805 --
2806 hr_utility.set_location('Entering:'||l_proc, 5);
2807 --
2808 /* --Kmullapu : Removed these params as we are no longer using commitment date
2809 Commitment dates are defaulted to Budget Dates.
2810 l_cmmtmnt_start_dt := fnd_date.canonical_to_date(p_cmmtmnt_start_dt);
2811 l_cmmtmnt_end_dt := fnd_date.canonical_to_date(p_cmmtmnt_end_dt);
2812 */
2813 --
2814 --
2815 --
2816 --Since the commitments were recalculated for all the calendar periods for this budget.
2817 --we are deleting the old records
2818 delete from pqh_element_commitments where budget_version_id = p_budget_version_id ;
2819
2820 Calculate_money_cmmtmnts
2821 (p_budgeted_entity_cd => p_budgeted_entity_cd,
2822 p_budget_version_id => p_budget_version_id,
2823 p_entity_id => p_entity_id,
2824 /* p_cmmtmnt_start_dt => l_cmmtmnt_start_dt,
2825 p_cmmtmnt_end_dt => l_cmmtmnt_end_dt,*/
2826 p_period_frequency => p_period_frequency);
2827 --
2828 COMMIT;
2829 --
2830 hr_utility.set_location('Leaving:'||l_proc, 10);
2831 --
2832 End;
2833 --
2834 -----------------------calculate_money_cmmtmnts--------------------------------
2835 --
2836 PROCEDURE calculate_money_cmmtmnts
2837 (p_budgeted_entity_cd in varchar2,
2838 p_budget_version_id in number,
2839 p_entity_id in number,
2840 /*p_cmmtmnt_start_dt in date,
2841 p_cmmtmnt_end_dt in date,*/
2842 p_period_frequency in varchar2 default null)
2843 IS
2844 --
2845 l_proc varchar2(72) := g_package||'calculate_money_cmmtmnts';
2846 --
2847 l_position_name hr_all_positions_f.name%type := NULL;
2848 l_entity_name pqh_cmmtmnt_entities_v.entity_name%type := NULL;
2849 --
2850 l_budget_id pqh_budgets.budget_id%type := NULL;
2851 l_budget_name pqh_budgets.budget_name%type := NULL;
2852 l_period_set_name pqh_budgets.period_set_name%type := NULL;
2853 l_budget_cal_freq pay_calendars.actual_period_type%type;
2854 l_budget_start_date date;
2855 l_budget_end_date date;
2856 l_cmmtmnt_start_dt date;
2857 l_cmmtmnt_end_dt date;
2858 l_entity_id number(30);
2859 --
2860 l_dummy_tab cmmtmnt_elmnts_tab;
2861 --
2862 pos_cnt number(15);
2863 cnt number(15);
2864 --
2865 Cursor csrGetBudgetName(l_bdgt_id in number) Is
2866 Select budget_name
2867 From pqh_budgets
2868 Where budget_id = l_bdgt_id;
2869
2870 --
2871 l_status number(15);
2872 --
2873 l_context_level number(15);
2874 l_log_context pqh_process_log.log_context%TYPE;
2875 --
2876 Begin
2877 --
2878
2879 hr_utility.set_location('Entering:'||l_proc, 5);
2880 --
2881 -- CHECK IF THIS IS A VALID BUDGET VERSION.GET THE BUDGETS CALENDAR FREQ.
2882 --
2883 --
2884 Validate_budget(p_budgeted_entity_cd => p_budgeted_entity_cd,
2885 p_budget_version_id => p_budget_version_id,
2886 p_budget_id => l_budget_id,
2887 p_budget_name => l_budget_name,
2888 p_period_set_name => l_period_set_name,
2889 p_bdgt_cal_frequency => l_budget_cal_freq,
2890 p_budget_start_date => l_budget_start_date,
2891 p_budget_end_date => l_budget_end_date);
2892
2893 l_cmmtmnt_start_dt := l_budget_start_date;
2894 l_cmmtmnt_end_dt := l_budget_end_date;
2895
2896 --
2897 -- CHECK IF THIS IS A VALID POSITION . ALSO DOES THIS POSITION
2898 -- BELONG IN THE PASSED BUDGET VERSION.
2899 --
2900 --
2901 Validate_entity(p_budgeted_entity_cd => p_budgeted_entity_cd,
2902 p_budget_version_id => p_budget_version_id,
2903 p_entity_id => p_entity_id,
2904 p_entity_name => l_entity_name);
2905 --
2906 -- If the passed frequency is null , we will generate commitments for the
2907 -- budget calendar frequency .
2908 --
2909 --
2910 -- VALIDATE THE COMMTMNT_START_DATE and CMMTMNT_END_DATE
2911 --
2912
2913 Validate_commitment_dates
2914 (p_period_frequency => nvl(p_period_frequency,l_budget_cal_freq),
2915 p_budget_cal_freq => l_budget_cal_freq,
2916 p_period_set_name => l_period_set_name,
2917 p_cmmtmnt_start_dt => l_cmmtmnt_start_dt,
2918 p_cmmtmnt_end_dt => l_cmmtmnt_end_dt,
2919 p_budget_start_date => l_budget_start_date,
2920 p_budget_end_date => l_budget_end_date);
2921 --
2922 -- Fetch all the commitment elments for this budget.
2923 --
2924 g_bdgt_cmmtmnt_elmnts := l_dummy_tab;
2925 --
2926 fetch_bdgt_cmmtmnt_elmnts( p_budget_id => l_budget_id,
2927 p_bdgt_cmmtmnt_elmnts => g_bdgt_cmmtmnt_elmnts);
2928 --
2929 -- Calculate commitment for each position under the input budget version ,
2930 -- then each assignment under the position and populate commitment into
2931 -- pqh_element_commitments table.Commitment is calculated for the period
2932 -- generated using the supplied frequency.
2933 -- From this point onwards we will start logging errors into the Process log.
2934 -- Start the Log Process
2935 --
2936 get_table_route;
2937 --
2938 -- If p_entity_id IS NOT NULL then
2939 ---
2940 --
2941 pqh_process_batch_log.start_log
2942 (
2943 p_batch_id => l_budget_id,
2944 p_module_cd => 'BUDGET_COMMITMENT',
2945 p_log_context => l_budget_name
2946 );
2947 --
2948 l_context_level := 1;
2949 Open csrGetBudgetName(l_budget_id);
2950 Fetch csrGetBudgetName into l_log_context;
2951 Close csrGetBudgetName;
2952 l_log_context := l_log_context ;
2953 --
2954 pqh_process_batch_log.set_context_level
2955 (p_txn_id => l_budget_id,
2956 p_txn_table_route_id => g_table_route_id_p_bgt,
2957 p_level => l_context_level,
2958 p_log_context => l_log_context);
2959 -- End if;
2960 --
2961 l_context_level := l_context_level + 1;
2962 --
2963 g_budget_version_status := 'CALCULATION_SUCCESS';
2964 --
2965 hr_utility.set_location('Begin Processing positions', 6);
2966 --
2967 For pos_cnt in NVL(g_budget_entities.FIRST,0)..NVL(g_budget_entities.LAST,-1) Loop
2968 --
2969 If p_budgeted_entity_cd = 'POSITION' then
2970 l_log_context := HR_GENERAL.DECODE_POSITION_LATEST_NAME(g_budget_entities(pos_cnt).entity_id);
2971 elsif p_budgeted_entity_cd ='JOB' then
2972 l_log_context := HR_GENERAL.DECODE_JOB(g_budget_entities(pos_cnt).entity_id);
2973 elsif p_budgeted_entity_cd ='ORGANIZATION' then
2974 l_log_context := HR_GENERAL.DECODE_ORGANIZATION(g_budget_entities(pos_cnt).entity_id);
2975 elsif p_budgeted_entity_cd ='GRADE' then
2976 l_log_context := HR_GENERAL.DECODE_GRADE(g_budget_entities(pos_cnt).entity_id);
2977 end if;
2978
2979 -- l_log_context := l_log_context||' ('||hr_general.decode_lookup('PQH_BUDGET_ENTITY',p_budgeted_entity_cd)||' )';
2980 --
2981 pqh_process_batch_log.set_context_level
2982 (
2983 p_txn_id => g_budget_entities(pos_cnt).entity_id,
2984 p_txn_table_route_id => g_table_route_id_p_bdt,
2985 p_level => l_context_level,
2986 p_log_context => l_log_context
2987 );
2988 --
2989 hr_utility.set_location('Set Savepoint', 9);
2990 --
2991 -- Set Save point for each position processed . We will rollback even if
2992 -- one record for the position failed.
2993 --
2994 Savepoint ins_pos_commitment;
2995 --
2996 g_budget_detail_status := 'CALCULATION_SUCCESS';
2997 --
2998 FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
2999 --
3000 Loop
3001 hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3002 hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3003 --
3004 -- The foll function gets all element types for a budget for which
3005 -- commitment has to be calculated, Calculates the respective commitment
3006 -- and Stores it into the commitment table.
3007 --
3008 hr_utility.set_location('Position id :'||to_char(g_budget_entities(pos_cnt).entity_id),15);
3009 --
3010 l_entity_id := g_budget_entities(pos_cnt).entity_id;
3011 --
3012 l_status := populate_commitment_table
3013 (p_budgeted_entity_cd => p_budgeted_entity_cd,
3014 p_commit_calculation_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,
3015 p_actual_cmmtmnt_start_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,
3016 p_commit_end_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,
3017 p_actual_cmmtmnt_end_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,
3018 p_commitment_calc_frequency => nvl(p_period_frequency,l_budget_cal_freq),
3019 p_budget_calendar_frequency => l_budget_cal_freq,
3020 p_entity_id => l_entity_id,
3021 p_budget_id => l_budget_id,
3022 p_budget_version_id => p_budget_version_id);
3023 --
3024
3025 --
3026 -- Rollback for this position ,if there was any error.
3027 --
3028 If l_status = -1 then
3029 --
3030 Rollback to ins_pos_commitment;
3031 --
3032 -- Skip this position and process next position.
3033 --
3034 g_budget_version_status := 'CALCULATION_ERROR';
3035 g_budget_detail_status := 'CALCULATION_ERROR';
3036 --
3037 Exit;
3038 --
3039 End if;
3040 --
3041 End loop; /** Commitment Calculation dates **/
3042 --
3043 -- Save if commitment calculation status for this position.
3044 --
3045 hr_utility.set_location('PositionId '||g_budget_entities(pos_cnt).entity_id,13);
3046
3047 If p_budgeted_entity_cd = 'POSITION' then
3048
3049 Update pqh_budget_details
3050 set commitment_gl_status = g_budget_detail_status
3051 Where budget_version_id = p_budget_version_id
3052 and position_id = g_budget_entities(pos_cnt).entity_id;
3053
3054 elsif p_budgeted_entity_cd ='JOB' then
3055
3056 Update pqh_budget_details
3057 set commitment_gl_status = g_budget_detail_status
3058 Where budget_version_id = p_budget_version_id
3059 and job_id = g_budget_entities(pos_cnt).entity_id;
3060
3061 elsif p_budgeted_entity_cd ='ORGANIZATION' then
3062
3063 Update pqh_budget_details
3064 set commitment_gl_status = g_budget_detail_status
3065 Where budget_version_id = p_budget_version_id
3066 and organization_id = g_budget_entities(pos_cnt).entity_id;
3067
3068 elsif p_budgeted_entity_cd ='GRADE' then
3069 Update pqh_budget_details
3070 set commitment_gl_status = g_budget_detail_status
3071 Where budget_version_id = p_budget_version_id
3072 and grade_id = g_budget_entities(pos_cnt).entity_id;
3073
3074 end if;
3075 --
3076 COMMIT;
3077 --
3078 --
3079 End loop; /** All positions in budget as of passed effective date **/
3080 --
3081 --
3082 Update pqh_budget_versions
3083 set commitment_gl_status = g_budget_version_status
3084 where budget_version_id = p_budget_version_id;
3085 --
3086 --
3087 pqh_process_batch_log.end_log;
3088 --
3089 --
3090 hr_utility.set_location('Leaving:'||l_proc, 20);
3091 --
3092 EXCEPTION
3093 WHEN OTHERS THEN
3094 raise;
3095 --
3096 End calculate_money_cmmtmnts;
3097 --
3098 ---------------------------------------------------------------------------------
3099 --
3100 -- The following functions are borrowed from payroll and tailored for our needs.
3101 --
3102 --------------------Work_schedule_total_hours---------------------------------------
3103 --
3104 -- This function is borrowed from payroll . No changes
3105 -- have been made to it .
3106 --
3107 FUNCTION work_schedule_total_hours( p_bg_id in NUMBER,
3108 p_ws_name in VARCHAR2,
3109 p_range_start in DATE DEFAULT NULL,
3110 p_range_end in DATE DEFAULT NULL) RETURN NUMBER IS
3111
3112 -- local constants
3113 c_ws_tab_name VARCHAR2(80) := 'COMPANY WORK SCHEDULES';
3114
3115 -- local variables
3116 /* 353434, 368242 : Fixed number width for total hours */
3117 v_total_hours NUMBER(15,7) := 0;
3118 v_range_start DATE;
3119 v_range_end DATE;
3120 v_curr_date DATE;
3121 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
3122 v_ws_name VARCHAR2(80); -- Work Schedule Name.
3123 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
3124 -- Remember to FND_NUMBER.CANONICAL_TO_NUMBER result.
3125 v_fnd_sess_row VARCHAR2(1);
3126 l_exists VARCHAR2(1);
3127
3128 BEGIN -- work_schedule_total_hours
3129
3130 -- Set range to a single week if no dates are entered:
3131
3132 hr_utility.set_location('work_schedule_total_hours setting dates', 3);
3133 v_range_start := NVL(p_range_start, sysdate);
3134 v_range_end := NVL(p_range_end, sysdate + 6);
3135
3136 -- Check for valid range
3137 --
3138 hr_utility.set_location('work_schedule_total_hours', 5);
3139 --
3140 IF v_range_start > v_range_end THEN
3141 hr_utility.set_location('work_schedule_total_hours', 7);
3142 RETURN v_total_hours;
3143 END IF;
3144 --
3145 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
3146 -- record if one doe not already exist.
3147 --
3148 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
3149 INTO v_fnd_sess_row
3150 FROM fnd_sessions
3151 WHERE session_id = userenv('sessionid');
3152 --
3153 IF v_fnd_sess_row = 'N' THEN
3154 insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'),trunc(sysdate));
3155 END IF;
3156 --
3157 -- Track range dates:
3158 hr_utility.set_location('range start = '||to_char(v_range_start), 5);
3159 hr_utility.set_location('range end = '||to_char(v_range_end), 6);
3160 --
3161 -- Check if the work schedule is an id or a name. If the work
3162 -- schedule does not exist, then return 0.
3163 --
3164 BEGIN
3165 select 'Y'
3166 into l_exists
3167 from pay_user_columns PUC
3168 where PUC.USER_COLUMN_NAME = p_ws_name
3169 and NVL(business_group_id, p_bg_id) = p_bg_id
3170 and NVL(legislation_code,'US') = 'US';
3171
3172 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3173 END;
3174
3175 if l_exists = 'Y' then
3176 v_ws_name := p_ws_name;
3177 else
3178 BEGIN
3179 select PUC.USER_COLUMN_NAME
3180 into v_ws_name
3181 from pay_user_columns PUC
3182 where PUC.USER_COLUMN_ID = p_ws_name
3183 and NVL(business_group_id, p_bg_id) = p_bg_id
3184 and NVL(legislation_code,'US') = 'US';
3185
3186 EXCEPTION WHEN NO_DATA_FOUND THEN
3187 RETURN v_total_hours;
3188 END;
3189 end if;
3190 --
3191 v_curr_date := v_range_start;
3192 --
3193 hr_utility.set_location('work_schedule_total_hours curr_date = '||to_char(v_curr_date), 20);
3194 --
3195 LOOP
3196 v_curr_day := TO_CHAR(v_curr_date, 'DY');
3197 --
3198 hr_utility.set_location('curr_day = '||v_curr_day, 20);
3199 --
3200 hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
3201 v_total_hours := v_total_hours
3202 + FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value
3203 (p_bg_id,
3204 c_ws_tab_name,
3205 v_ws_name,
3206 v_curr_day));
3207 v_curr_date := v_curr_date + 1;
3208 --
3209 hr_utility.set_location('curr_date = '||to_char(v_curr_date), 20);
3210 --
3211 EXIT WHEN v_curr_date > v_range_end;
3212 --
3213 END LOOP;
3214 --
3215 RETURN v_total_hours;
3216 --
3217 END work_schedule_total_hours;
3218 --
3219 -----------------------Standard_hours_worked---------------------------------
3220 --
3221 -- The foll function was borrowed from payroll but has been revamped because of lookup
3222 -- issues.
3223 --
3224 FUNCTION standard_hours_worked( p_std_hrs in NUMBER,
3225 p_range_start in DATE,
3226 p_range_end in DATE,
3227 p_std_freq in VARCHAR2) RETURN NUMBER IS
3228 --
3229 v_wkdays NUMBER ;
3230 l_wkdays NUMBER ;
3231 --
3232 v_total_hours NUMBER(15,7) := 0;
3233 v_wrkday_hours NUMBER(15,7) := 0;-- std hrs/wk div by 5 workdays/wk
3234 v_curr_date DATE:= NULL;
3235 v_curr_day VARCHAR2(3):= NULL; -- 3 char abbrev for day of wk.
3236
3237 cursor csr_working_days is
3238 select information3
3239 from per_shared_types
3240 where lookup_type ='FREQUENCY'
3241 and system_type_cd = p_std_freq;
3242 BEGIN -- standard_hours_worked
3243 --
3244 -- Check for valid range. If range_end is NULL or range_end < range_start
3245 -- 0 hours will be returned.
3246 --
3247 hr_utility.set_location('standard_hours_worked', 5);
3248 --
3249 IF p_range_start > p_range_end THEN
3250 hr_utility.set_location('standard_hours_worked', 7);
3251 RETURN v_total_hours;
3252 END IF;
3253 --
3254 -- This portion calculates how may hours are worked in a day.
3255 -- from the share types, we are going to get how many working days in the assignment frequency
3256 -- if the value is null or 0 or share type is not there, we will assume it to be 1 day.
3257 --
3258 open csr_working_days ;
3259 fetch csr_working_days into v_wkdays;
3260 if csr_working_days%notfound then
3261 l_wkdays := 1;
3262 close csr_working_days;
3263 else
3264 if nvl(v_wkdays,0) = 0 then
3265 l_wkdays := 1;
3266 else
3267 l_wkdays := v_wkdays;
3268 end if;
3269 close csr_working_days;
3270 end if;
3271 v_wrkday_hours := p_std_hrs/l_wkdays;
3272 --
3273 v_curr_date := p_range_start;
3274 --
3275 hr_utility.set_location('standard_hours_worked', 10);
3276
3277 LOOP
3278 --
3279 -- Loop through all the working days in the range supplied and find
3280 -- how may hours were worked , during that period.
3281 --
3282 v_curr_day := TO_CHAR(v_curr_date, 'DY');
3283 --
3284 hr_utility.set_location('standard_hours_worked', 15);
3285 --
3286 IF UPPER(v_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
3287 v_total_hours := v_total_hours + v_wrkday_hours;
3288 hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
3289 END IF;
3290 --
3291 v_curr_date := v_curr_date + 1;
3292 --
3293 EXIT WHEN v_curr_date > p_range_end;
3294 --
3295 END LOOP;
3296 --
3297 RETURN v_total_hours;
3298 --
3299 END standard_hours_worked;
3300 --
3301 ---------------------Convert_period_Type--------------------------------------
3302 --
3303 -- The following function converts the passed p_figure from one
3304 -- frequency to another . i.e if the value for a year is passed , it
3305 -- will convert the value to the required frequency , say , MONTHLY
3306 --
3307 FUNCTION Convert_Period_Type(
3308 p_bus_grp_id in NUMBER,
3309 p_payroll_id in NUMBER,
3310 p_asst_std_hours in NUMBER default NULL,
3311 p_figure in NUMBER,
3312 p_from_freq in VARCHAR2 ,
3313 p_to_freq in VARCHAR2 ,
3314 p_period_start_date in DATE default NULL,
3315 p_period_end_date in DATE default NULL,
3316 p_asst_std_freq in VARCHAR2 default NULL,
3317 p_dflt_elmnt_frequency in VARCHAR2,
3318 p_budget_calendar_frequency in VARCHAR2)
3319 RETURN NUMBER IS
3320 --
3321 -- DECLARE local vars
3322 --
3323 v_converted_figure NUMBER;
3324 v_from_annualizing_factor NUMBER(10);
3325 v_to_annualizing_factor NUMBER(10);
3326
3327 -- LOCAL FUNCTION
3328 --
3329 -- This function determines the number of times a passed frequency
3330 -- occurs in one year eg. a frequency of MONTHLY occurs 12 times in a
3331 -- year
3332 --
3333 FUNCTION Get_Annualizing_Factor(p_bg in NUMBER,
3334 p_payroll in NUMBER,
3335 p_freq in VARCHAR2,
3336 p_asg_std_hrs in NUMBER,
3337 p_asg_std_freq in VARCHAR2)
3338 RETURN NUMBER IS
3339 --
3340 -- DECLARE local constants
3341 --
3342 c_weeks_per_year NUMBER(3):= 52;
3343 c_days_per_year NUMBER(3):= 200;
3344 c_months_per_year NUMBER(3):= 12;
3345 --
3346 -- DECLARE local vars
3347 --
3348 v_annualizing_factor NUMBER(30,7);
3349 v_periods_per_fiscal_yr NUMBER(5);
3350 v_hrs_per_wk NUMBER(15,7);
3351 v_hrs_per_range NUMBER(15,7);
3352 v_use_pay_basis NUMBER(1) := 0;
3353 v_pay_basis VARCHAR2(80);
3354 v_range_start DATE;
3355 v_range_end DATE;
3356 v_work_sched_name VARCHAR2(80);
3357 v_ws_id NUMBER(9);
3358 v_period_hours BOOLEAN;
3359 --
3360 l_payroll_period_type pay_payrolls_f.period_type%type;
3361 --
3362 BEGIN -- Get_Annualizing_Factor
3363 --
3364 --
3365 -- Check for use of salary admin (ie. pay basis) as frequency.
3366 -- Selecting "count" because we want to continue processing even if
3367 -- the from_freq is not a pay basis.
3368 --
3369 --
3370 hr_utility.set_location('Get_Annualizing_Factor', 5);
3371 --
3372 begin -- Is passes frequency , pay basis?
3373
3374 --
3375 -- Decode pay basis and set v_annualizing_factor accordingly.
3376 --
3377 hr_utility.set_location('Is it salary basis'||nvl(p_freq,'null'),10);
3378
3379 --
3380 --
3381 SELECT lookup_code
3382 INTO v_pay_basis
3383 FROM hr_lookups lkp
3384 WHERE lkp.application_id = 800
3385 AND lkp.lookup_type = 'PAY_BASIS'
3386 AND lkp.lookup_code = p_freq;
3387 --
3388 -- If the passed frequency , uses lookup PAY_BASIS , then the foll
3389 -- portion will be executed .Otherwise , exception NO DATA FOUND will
3390 -- be raised .
3391 --
3392 hr_utility.set_location('Get_Annualizing_Factor', 15);
3393 --
3394 v_use_pay_basis := 1;
3395 --
3396 -- The lookup PAY_BASIS uses 4 lookup_code's - MONTHLY , ANNUAL ,
3397 -- PERIOD , HOURLY. Get the annualizating factor for the passed
3398 -- frequency , if it is one of the above 4 lookup codes.
3399 --
3400 IF v_pay_basis = 'MONTHLY' THEN
3401 --
3402 hr_utility.set_location('Monthly salary basis ',20);
3403 --
3404 v_annualizing_factor := 12;
3405 --
3406 ELSIF v_pay_basis = 'HOURLY' THEN
3407 --
3408 hr_utility.set_location('Hourly salary basis',25);
3409 --
3410 IF p_period_start_date IS NOT NULL THEN
3411 v_range_start := p_period_start_date;
3412 v_range_end := p_period_end_date;
3413 v_period_hours := TRUE;
3414 ELSE
3415 v_range_start := sysdate;
3416 v_range_end := sysdate + 6;
3417 v_period_hours := FALSE;
3418 END IF;
3419 --
3420 -- If this is an Hourly employee and the work schedule name
3421 -- has not been provided , then the total hours worked during the
3422 -- given period is calculated using Standard Hours on the
3423 -- assignment.
3424 -- removed reference of work schedule as it was always passed as null
3425 --
3426 v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
3427 v_range_start,
3428 v_range_end,
3429 p_asg_std_freq);
3430 --
3431 --
3432 -- Once the number of hours worked in the given period have been
3433 -- calculated , calculate the number of hours worked in a year .
3434 -- We have calculated the no of hours in one budget calendar frequency
3435 -- period .We need to find the number of times the budget calendar
3436 -- frequency happens in a year.
3437 --
3438 IF v_period_hours THEN
3439 --
3440 v_periods_per_fiscal_yr := get_number_per_fiscal_year(p_budget_calendar_frequency);
3441 --
3442 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
3443 --
3444 ELSE
3445 --
3446 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
3447 --
3448 END IF;
3449 --
3450 ELSIF v_pay_basis = 'PERIOD' THEN
3451 --
3452 hr_utility.set_location('Period salary basis',40);
3453 --
3454 -- If the salary basis is PERIOD , then fetch the period type of the
3455 -- associated payroll . But as seen in the assignment form , it is
3456 -- possible to create an assignment with pay basis = PERIOD but the
3457 -- assignment may not be associated with a payroll. Under such
3458 -- circumstances , We will fall back to budget element frequency.
3459 --
3460 If p_payroll IS NOT NULL then
3461 --
3462 hr_utility.set_location('Payroll is Exists',45);
3463 --
3464 l_payroll_period_type := get_payroll_period_type
3465 (p_payroll_id => p_payroll,
3466 p_effective_dt => p_period_start_date);
3467 --
3468 v_annualizing_factor := get_number_per_fiscal_year(l_payroll_period_type);
3469 --
3470 Else
3471 -- If pay basis is PERIOD , and no payroll id has been associated with
3472 -- the assignment,we will use budget element frequency for calculating
3473 -- commitment . If budget element frequency is also NULL , we will
3474 -- use budget calendar frequency .
3475 --
3476 v_annualizing_factor := get_number_per_fiscal_year(p_dflt_elmnt_frequency);
3477 --
3478 End if;
3479
3480 hr_utility.set_location('Get_Annualizing_Factor', 50);
3481
3482 ELSIF v_pay_basis = 'ANNUAL' THEN
3483 --
3484 hr_utility.set_location('Annual salary basis',55);
3485 --
3486 v_annualizing_factor := 1;
3487 --
3488 ELSE
3489 --
3490 -- Did not recognize "pay basis"
3491 --
3492 hr_utility.set_location('Get_Annualizing_Factor', 60);
3493
3494 v_annualizing_factor := 0;
3495 RETURN v_annualizing_factor;
3496
3497 END IF;
3498
3499 EXCEPTION
3500 WHEN NO_DATA_FOUND THEN
3501 --
3502 hr_utility.set_location('Exception raised !',65);
3503 --
3504 -- The only reason why this exception should be raised , is when an invalid
3505 -- salary basis has been entered .
3506 --
3507 v_use_pay_basis := 0;
3508 --
3509 END;
3510
3511 IF v_use_pay_basis = 0 THEN
3512 --
3513 -- If Not using pay basis as frequency...
3514 -- Check if the frequency is in per_time_period_types.
3515 -- The Budget Calendar frequency is the period type of the budgets
3516 -- Calendar . The valid set of period types are available in
3517 -- per_time_period_types .
3518 -- This set will be used to validate if the input element frequency is
3519 -- valid.
3520 -- If input element frequency is not valid ,fall back on budget element frequency .
3521
3522 IF (p_freq IS NULL) OR
3523 (UPPER(p_freq) = 'PERIOD') OR
3524 (UPPER(p_freq) = 'NOT ENTERED') THEN
3525 --
3526 -- Get "annualizing factor" from period type of the payroll.
3527 --
3528 hr_utility.set_location('Get_Annualizing_Factor', 70);
3529 --
3530 If p_payroll IS NOT NULL then
3531 --
3532 l_payroll_period_type := get_payroll_period_type
3533 (p_payroll_id => p_payroll,
3534 p_effective_dt => p_period_start_date);
3535 --
3536 v_annualizing_factor := get_number_per_fiscal_year(l_payroll_period_type);
3537 --
3538 Else
3539 -- If FREQUENCY is PERIOD , and no payroll id has been associated with
3540 -- the assignment,we will use budget element frequency for calculating
3541 -- commitment .
3542 --
3543 v_annualizing_factor := get_number_per_fiscal_year(p_dflt_elmnt_frequency);
3544 --
3545 End if;
3546 --
3547 ELSIF UPPER(p_freq) <> 'HOURLY' THEN
3548 --
3549 -- This is an actual time period type from per_time_period_types.
3550 -- We know how to handle this.
3551 --
3552 --
3553 BEGIN
3554 --
3555 hr_utility.set_location('Get_Annualizing_Factor',75);
3556
3557 SELECT PT.number_per_fiscal_year
3558 INTO v_annualizing_factor
3559 FROM per_time_period_types PT
3560 WHERE UPPER(PT.period_type) = UPPER(p_freq);
3561
3562 hr_utility.set_location('Get_Annualizing_Factor',80);
3563 --
3564 exception when NO_DATA_FOUND then
3565 --
3566 -- Added as part of SALLY CLEANUP.
3567 -- Could have been passed in an ASG_FREQ dbi which might
3568 -- have the values of 'Day' or 'Month' which do not map to a
3569 -- time period type. So we'll do these by hand.
3570
3571 hr_utility.set_location('Get_Annualizing_Factor',85);
3572 IF UPPER(p_freq) = 'DAY' THEN
3573 v_annualizing_factor := c_days_per_year;
3574 ELSIF UPPER(p_freq) = 'MONTH' THEN
3575 v_annualizing_factor := c_months_per_year;
3576 Else
3577 null;
3578 END IF;
3579
3580 END;
3581
3582 ELSE -- Hourly employee...
3583 --
3584 hr_utility.set_location('Get_Annualizing_Factor', 90);
3585
3586 IF p_period_start_date IS NOT NULL THEN
3587 v_range_start := p_period_start_date;
3588 v_range_end := p_period_end_date;
3589 v_period_hours := TRUE;
3590 ELSE
3591 v_range_start := sysdate;
3592 v_range_end := sysdate + 6;
3593 v_period_hours := FALSE;
3594 END IF;
3595 hr_utility.set_location('getting std hours', 100);
3596 v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
3597 v_range_start,
3598 v_range_end,
3599 p_asg_std_freq);
3600 IF v_period_hours THEN
3601 v_periods_per_fiscal_yr := get_number_per_fiscal_year(p_to_freq);
3602 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
3603 ELSE
3604 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
3605 END IF;
3606 END IF;
3607 END IF; -- (v_use_pay_basis = 0)
3608 RETURN v_annualizing_factor;
3609 END Get_Annualizing_Factor;
3610 --
3611 ----The Convert_Period_Type function starts here --------
3612 --
3613 BEGIN
3614 hr_utility.set_location('Convert_Period_Type', 10);
3615 hr_utility.set_location('p_from_freq'||p_from_freq, 10);
3616 hr_utility.set_location('p_to_freq'||p_to_freq, 10);
3617 --
3618 -- If From_Freq and To_Freq are the same, then we're done.
3619 --
3620 hr_utility.set_location('Starting Conversion p_from_freq'||p_from_freq||' p_to_freq'||p_to_freq,15);
3621
3622 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
3623
3624 RETURN p_figure;
3625
3626 END IF;
3627
3628 v_from_annualizing_factor := Get_Annualizing_Factor(
3629 p_bg => p_bus_grp_id,
3630 p_payroll => p_payroll_id,
3631 p_freq => p_from_freq,
3632 p_asg_std_hrs => p_asst_std_hours,
3633 p_asg_std_freq => p_asst_std_freq);
3634
3635 v_to_annualizing_factor := Get_Annualizing_Factor(
3636 p_bg => p_bus_grp_id,
3637 p_payroll => p_payroll_id,
3638 p_freq => p_to_freq,
3639 p_asg_std_hrs => p_asst_std_hours,
3640 p_asg_std_freq => p_asst_std_freq);
3641 --
3642 -- Annualize "Figure" and convert to To_Freq.
3643 --
3644
3645 hr_utility.set_location('v_from_annualizing_factor '|| v_from_annualizing_factor, 20);
3646 hr_utility.set_location('v_to_annualizing_factor' || v_to_annualizing_factor, 20);
3647 hr_utility.set_location('p_figure' || p_figure, 20);
3648
3649 hr_utility.set_location('Convert_Period_Type', 20);
3650
3651 IF v_to_annualizing_factor = 0 OR
3652 v_to_annualizing_factor = -999 OR
3653 v_from_annualizing_factor = -999 THEN
3654
3655 hr_utility.set_location('Convert_Period_Type', 25);
3656
3657 v_converted_figure := 0;
3658 RETURN v_converted_figure;
3659
3660 ELSE
3661
3662 hr_utility.set_location('Convert_Period_Type', 30);
3663
3664 v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
3665
3666 END IF;
3667
3668 hr_utility.set_location('Leaving : Convert_Period_Type'||NVL(v_converted_figure,0), 35);
3669
3670 RETURN v_converted_figure;
3671
3672 END Convert_Period_Type;
3673 --
3674
3675 Procedure refresh_asg_ele_commitments (p_assignment_id Number,
3676 p_effective_date Date,
3677 p_element_type_id Number default Null,
3678 p_input_value_id Number default Null)
3679 IS
3680
3681 -- Control Budget (position - money) as of the effective date for the current BG
3682 Cursor csr_get_ctrl_bdgt IS
3683 Select bdgts.budget_id,budget_name,period_set_name ,
3684 budget_start_date,budget_end_date
3685 From PQH_BUDGETS bdgts,per_shared_types shtyps
3686 where p_effective_date between budget_start_date and budget_end_date
3687 and position_control_flag ='Y'
3688 and budgeted_entity_cd ='POSITION'
3689 and shtyps.shared_type_id = bdgts.budget_unit1_id
3690 and shtyps.system_type_cd ='MONEY'
3691 and bdgts.business_group_id = hr_general.get_business_group_id;
3692 --
3693 -- Obtain the frequency of the time periods for a calendar
3694 --
3695 Cursor csr_bdgt_cal_freq(p_period_set_name varchar2) is
3696 Select pc.actual_period_type
3697 from pay_calendars pc
3698 Where pc.period_set_name = p_period_set_name;
3699 --
3700 -- Get Position Id from the Current Assignment
3701 --
3702 Cursor csr_get_assignment IS
3703 Select position_id
3704 from per_all_assignments_f
3705 where assignment_id = p_assignment_id
3706 and p_effective_date between effective_start_date and effective_end_date;
3707 --
3708 -- Get the effective Budget version
3709 Cursor csr_get_bdgt_version(p_budget_id number) IS
3710 Select budget_version_id
3711 from pqh_budget_versions
3712 where budget_id = p_budget_id
3713 and p_effective_date between date_from and date_to;
3714 --
3715 --
3716 Cursor csr_get_period_frequency IS
3717 Select period_type
3718 from PAY_PAYROLLS_f
3719 where payroll_id = (Select payroll_id
3720 from per_all_assignments_f
3721 where assignment_id = p_assignment_id);
3722 ---
3723 Cursor csr_bdgt_commt_elmnt(p_budget_id number) IS
3724 Select Formula_id,
3725 Salary_basis_flag,
3726 dflt_elmnt_frequency,
3727 nvl(Overhead_percentage,0)
3728 From pqh_bdgt_cmmtmnt_elmnts
3729 Where budget_id = p_budget_id
3730 and element_type_id = p_element_type_id
3731 and element_input_value_id = p_input_value_id
3732 and actual_commitment_type in ('COMMITMENT','BOTH');
3733 --
3734 Cursor csr_pos_single_assignment is
3735 Select pay_basis_id,
3736 business_group_id,payroll_id,
3737 normal_hours,frequency
3738 From per_all_assignments_f
3739 Where assignment_id = p_assignment_id
3740 And p_effective_date between effective_start_date
3741 AND effective_end_date;
3742 Cursor csr_salary_basis(p_pay_basis_id number) is
3743 Select ppb.input_value_id
3744 from per_pay_bases ppb
3745 , pay_input_values_f piv --To ensure that this input value id belongs to the passed element_type
3746 where ppb.pay_basis_id = p_pay_basis_id
3747 and piv.input_value_id = ppb.input_value_id
3748 and piv.element_type_id = p_element_type_id
3749 and p_effective_date between piv.effective_start_date and piv.effective_end_date;
3750 --
3751 Cursor csrGetBudgetName(l_bdgt_id in number) Is
3752 Select budget_name
3753 From pqh_budgets
3754 Where budget_id = l_bdgt_id;
3755
3756 --
3757 l_formula_id Number;
3758 l_salary_basis_flag varchar2(10);
3759 l_dflt_elmnt_frequency varchar2(30);
3760 l_budget_id pqh_budgets.budget_id%type := NULL;
3761 l_budget_name pqh_budgets.budget_name%type := NULL;
3762 l_period_set_name pqh_budgets.period_set_name%type := NULL;
3763 l_budget_cal_freq pay_calendars.actual_period_type%type;
3764 l_budget_start_date date;
3765 l_budget_end_date date;
3766 l_cmmtmnt_start_dt date;
3767 l_entity_id Number;
3768 l_budget_version_id Number;
3769 l_cmmtmnt_end_dt date;
3770 l_dummy_tab cmmtmnt_elmnts_tab;
3771 l_status number(15);
3772 l_period_frequency PAY_PAYROLLS_f.period_type%type;
3773 l_pay_basis_id per_all_assignments_f.pay_basis_id%type;
3774 l_business_group_id per_all_assignments_f.business_group_id%type;
3775 l_payroll_id per_all_assignments_f.payroll_id%type;
3776 l_normal_hours per_all_assignments_f.normal_hours%type;
3777 l_frequency per_all_assignments_f.frequency%type;
3778 l_overhead_percentage Number;
3779 l_entry_commitment Number := 0;
3780 l_element_overhead Number(5,2) :=0;
3781 l_input_value_id Number := p_input_value_id;
3782 l_context_level number(15);
3783 l_log_context pqh_process_log.log_context%TYPE;
3784
3785 l_proc varchar2(72) := g_package || 'refresh_asg_ele_commitments';
3786 Begin
3787
3788 hr_utility.set_location('Entering:'||l_proc, 5);
3789
3790 -- Control Budget Id (position - money) as of the effective date for the current BG
3791 Open csr_get_ctrl_bdgt;
3792 Fetch csr_get_ctrl_bdgt into l_budget_id,l_budget_name,
3793 l_period_set_name,l_budget_start_date,l_budget_end_date;
3794 Close csr_get_ctrl_bdgt;
3795
3796 --
3797 -- DETERMINE THE CALENDAR FREQ OF THE BUDGET
3798 --
3799 Open csr_bdgt_cal_freq(l_period_set_name);
3800 --
3801 Fetch csr_bdgt_cal_freq into l_budget_cal_freq;
3802 --
3803 If csr_bdgt_cal_freq%notfound then
3804 --
3805 --Raise exception
3806 --
3807 Close csr_bdgt_cal_freq;
3808 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BDGT_CALENDAR');
3809 APP_EXCEPTION.RAISE_EXCEPTION;
3810 --
3811 End if;
3812 --
3813 Close csr_bdgt_cal_freq;
3814 --
3815
3816
3817 -- Generate Commtmnt Calculation Dates to compute the commitment periods
3818 -- p_period_frequency is assuming the same as p_budget_cal_freq
3819 --
3820 l_cmmtmnt_start_dt:= l_budget_start_date;
3821 l_cmmtmnt_end_dt := l_budget_end_date;
3822
3823 /* Open csr_get_period_frequency;
3824 Fetch csr_get_period_frequency into l_period_frequency;
3825 Close csr_get_period_frequency; */
3826
3827
3828 generate_cmmtmnt_calc_dates(p_budget_start_date => l_budget_start_date,
3829 p_budget_end_date => l_budget_end_date,
3830 p_period_set_name => l_period_set_name,
3831 p_budget_cal_freq => l_budget_cal_freq,
3832 p_period_frequency => l_budget_cal_freq,
3833 p_cmmtmnt_start_dt=> l_cmmtmnt_start_dt,
3834 p_cmmtmnt_end_dt => l_cmmtmnt_end_dt);
3835
3836 --
3837 Open csr_get_bdgt_version(l_budget_id);
3838 Fetch csr_get_bdgt_version into l_budget_version_id;
3839 Close csr_get_bdgt_version;
3840 --
3841 If p_element_type_id is Null and p_input_value_id is Null then
3842 --
3843 --
3844 -- Fetch all the commitment elments for this budget.
3845 --
3846 g_bdgt_cmmtmnt_elmnts := l_dummy_tab;
3847 --
3848 fetch_bdgt_cmmtmnt_elmnts( p_budget_id => l_budget_id,
3849 p_bdgt_cmmtmnt_elmnts => g_bdgt_cmmtmnt_elmnts);
3850 --
3851
3852 get_table_route;
3853
3854 Savepoint ins_pos_commitment;
3855 --
3856 Open csr_get_assignment;
3857 Fetch csr_get_assignment into l_entity_id;
3858 Close csr_get_assignment;
3859
3860 g_budget_detail_status := 'CALCULATION_SUCCESS';
3861 --
3862 FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
3863 --
3864 Loop
3865
3866 hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3867 hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3868 --
3869 -- The foll function gets all element types for a budget for which
3870 -- commitment has to be calculated, Calculates the respective commitment
3871 -- and Stores it into the commitment table.
3872 --
3873 -- l_entity_id is the Current Assignment's Position Id
3874 --
3875 -- if (p_effective_date between g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt and
3876 -- g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt ) then
3877 --
3878 If (p_effective_date between g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt and g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt) OR (p_effective_date <= g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt) Then
3879 --
3880 l_status := populate_commitment_table
3881 (p_budgeted_entity_cd => 'POSITION',
3882 p_commit_calculation_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,
3883 p_actual_cmmtmnt_start_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,
3884 p_commit_end_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,
3885 p_actual_cmmtmnt_end_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,
3886 p_commitment_calc_frequency => l_budget_cal_freq,
3887 p_budget_calendar_frequency => l_budget_cal_freq,
3888 p_entity_id => l_entity_id,
3889 p_budget_id => l_budget_id,
3890 p_budget_version_id => l_budget_version_id);
3891 --
3892 -- end if;
3893 --
3894 -- Rollback for this position ,if there was any error.
3895 --
3896 If l_status = -1 then
3897 --
3898 Rollback to ins_pos_commitment;
3899 --
3900 -- Skip this position and process next position.
3901 --
3902 g_budget_version_status := 'CALCULATION_ERROR';
3903 g_budget_detail_status := 'CALCULATION_ERROR';
3904 --
3905 Exit;
3906 --
3907 End if;
3908 --
3909 End if;
3910 --
3911 End loop; /** Commitment Calculation dates **/
3912 --
3913 Else
3914 -- If input value id is not null and element_type_id is not null then
3915
3916 Open csr_bdgt_commt_elmnt(l_budget_id);
3917 --
3918 Fetch csr_bdgt_commt_elmnt into l_formula_id,l_salary_basis_flag,
3919 l_dflt_elmnt_frequency,
3920 l_overhead_percentage;
3921 --
3922 Close csr_bdgt_commt_elmnt;
3923
3924 -- Fetch Assignment related information
3925 Open csr_pos_single_assignment;
3926 --
3927 Fetch csr_pos_single_assignment into l_pay_basis_id, l_business_group_id,
3928 l_payroll_id,l_normal_hours,l_frequency;
3929 --
3930 Close csr_pos_single_assignment;
3931 --
3932 --
3933 If (p_input_value_id is Not Null and p_element_type_id is Null ) then
3934 --
3935 -- PQH_INVALID_INPUTS_FOR_REF : Element type cannot be null. Please provide
3936 -- the Element Type id or Please pass input_value_id also null
3937 --
3938 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_INPUTS_FOR_REF');
3939 APP_EXCEPTION.RAISE_EXCEPTION;
3940 --
3941 End if;
3942
3943 -- If Element Type Id is provided, fetching the input_value_id
3944 --
3945 If (p_input_value_id is null and p_element_type_id is not null) then
3946 --
3947 Open csr_salary_basis(l_pay_basis_id);
3948 Fetch csr_salary_basis into l_input_value_id;
3949 Close csr_salary_basis;
3950 --
3951 End if;
3952 --
3953 get_table_route;
3954
3955 Open csrGetBudgetName(l_budget_id);
3956 Fetch csrGetBudgetName into l_budget_name;
3957 Close csrGetBudgetName;
3958
3959 pqh_process_batch_log.start_log
3960 (
3961 p_batch_id => l_budget_id,
3962 p_module_cd => 'BUDGET_COMMITMENT',
3963 p_log_context => l_budget_name
3964 );
3965 --
3966 l_context_level := 1;
3967
3968 l_log_context := l_budget_name ;
3969 --
3970 pqh_process_batch_log.set_context_level
3971 (p_txn_id => l_budget_id,
3972 p_txn_table_route_id => g_table_route_id_p_bgt,
3973 p_level => l_context_level,
3974 p_log_context => l_log_context);
3975 --
3976 -- Fetch input_value_id, if it is null
3977 --
3978
3979 FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
3980 --
3981 Loop
3982 --
3983 hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3984 hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3985
3986 savepoint salary_proposal_level;
3987 hr_utility.set_location('Default to element type calc',35);
3988 --
3989 -- If (p_effective_date between g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt and g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt) then
3990 If (p_effective_date between g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt and g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt) OR (p_effective_date <= g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt) Then
3991 --
3992 l_entry_commitment := get_commitment_from_elmnt_type(
3993 p_commit_calculation_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,
3994 p_actual_cmmtmnt_start_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,
3995 p_commit_calculation_end_dt => g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,
3996 p_actual_cmmtmnt_end_dt => g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,
3997 p_commitment_calc_frequency => l_budget_cal_freq,
3998 p_budget_calendar_frequency => l_budget_cal_freq,
3999 p_dflt_elmnt_frequency => l_dflt_elmnt_frequency,
4000 p_business_group_id => l_business_group_id,
4001 p_assignment_id => p_assignment_id,
4002 p_payroll_id => l_payroll_id,
4003 p_pay_basis_id => l_pay_basis_id,
4004 p_element_type_id => p_element_type_id,
4005 p_element_input_value_id => l_input_value_id,
4006 p_normal_hours => l_normal_hours,
4007 p_asst_frequency => l_frequency);
4008 --
4009 hr_utility.set_location('l_entry_commitment'||l_entry_commitment,44);
4010 l_status := Calculate_overhead
4011 (p_element_commitment => l_entry_commitment,
4012 p_overhead_percentage => l_overhead_percentage,
4013 p_element_overhead => l_element_overhead);
4014 hr_utility.set_location('l_entry_commitment: '||l_entry_commitment,44);
4015 hr_utility.set_location('l_element_overhead: '||nvl(l_element_overhead,0),44);
4016 hr_utility.set_location('l_status: '||l_status,45);
4017 If l_status = -1 then
4018 rollback to salary_proposal_level;
4019 End if;
4020
4021 hr_utility.set_location('Delete commitment',46);
4022 --
4023 Delete from pqh_element_commitments
4024 Where budget_version_id = l_budget_version_id
4025 AND ASSIGNMENT_ID = p_assignment_id
4026 AND ELEMENT_TYPE_ID = p_element_type_id
4027 AND (COMMITMENT_START_DATE between g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt
4028 and g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt OR
4029 g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt between COMMITMENT_START_DATE
4030 and COMMITMENT_END_DATE);
4031 --
4032 --
4033 hr_utility.set_location('Insert commitment',48);
4034 hr_utility.set_location('l_entry_commitment:'||l_entry_commitment,48);
4035 --
4036 Insert into pqh_element_commitments(
4037 ELEMENT_COMMITMENT_ID ,
4038 BUDGET_VERSION_ID,
4039 ASSIGNMENT_ID,
4040 ELEMENT_TYPE_ID,
4041 COMMITMENT_START_DATE,
4042 COMMITMENT_END_DATE,
4043 COMMITMENT_CALC_FREQUENCY,
4044 COMMITMENT_AMOUNT,
4045 CREATION_DATE,
4046 CREATED_BY)
4047 Values(
4048 pqh_element_commitments_s.nextval ,
4049 l_budget_version_id,
4050 p_assignment_id,
4051 p_element_type_id,
4052 g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,
4053 g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,
4054 l_budget_cal_freq,
4055 l_entry_commitment+nvl(l_element_overhead,0),
4056 sysdate,
4057 -1);
4058
4059 hr_utility.set_location('Insert commitment2'||(l_entry_commitment+l_element_overhead),48);
4060 --
4061 End if;
4062 --
4063 End Loop;
4064 --
4065 End if;
4066 Exception
4067 When others then
4068 -- Log error
4069 hr_utility.set_location(sqlerrm,1000);
4070 pqh_process_batch_log.insert_log
4071 (
4072 p_message_type_cd => 'ERROR',
4073 p_message_text => SQLERRM
4074 );
4075
4076 End refresh_asg_ele_commitments;
4077 --
4078 -------------------------------------------------------------------------------
4079 End;
[Home] [Help]