[Home] [Help]
PACKAGE BODY: APPS.HXT_CHK_BG_AND_UPGRADE_PKG
Source
1 package body HXT_CHK_BG_AND_UPGRADE_PKG as
2 /* $Header: hxtbgupg.pkb 120.0 2005/05/29 06:00:46 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 PROCEDURE hxt_bg_message_insert(
6 P_PHASE IN VARCHAR2,
7 P_TEXT IN VARCHAR2 ) IS
8 --
9 PRAGMA AUTONOMOUS_TRANSACTION;
10 --
11 sql_insert VARCHAR2(300);
12 --
13 Begin
14 sql_insert := 'INSERT INTO HXT_UPGRADE_BG_MESSAGES VALUES (:1, :2)';
15 EXECUTE IMMEDIATE sql_insert USING p_phase, p_text;
16 --
17 COMMIT;
18 --
19 End;
20 -------------------------------------------------------------------------------
21 FUNCTION hxt_bg_checker RETURN boolean IS
22 --
23 -- Declare global variables
24 --
25 g_valid boolean := True;
26 g_sub_valid boolean := True;
27 g_sub_bg number := -1;
28 g_token varchar2(150);
29 --
30 -- Declare cursors
31 --
32 -- cursor to get all Workplans.
33 --
34 CURSOR c_get_workplans IS
35 SELECT DISTINCT
36 wws.id
37 , wws.name
38 FROM hxt_weekly_work_schedules wws;
39 --
40 -- cursor to get all rotation plans.
41 --
42 CURSOR c_get_rotation_plans IS
43 SELECT DISTINCT
44 rtp.id
45 , rtp.name
46 from hxt_rotation_plans rtp;
47 --
48 -- cursor to get all Earning Groups.
49 --
50 CURSOR c_get_earn_groups IS
51 SELECT DISTINCT
52 egt.id
53 , egt.name
54 FROM hxt_earn_group_types egt;
55 --
56 -- cursor to get all Premium Eligibility Policies.
57 --
58 CURSOR c_get_peps IS
59 SELECT DISTINCT
60 pep.id
61 , pep.name
62 FROM hxt_prem_eligblty_policies pep;
63 --
64 -- cursor to get all Premium Interaction Policies.
65 --
66 CURSOR c_get_pips IS
67 SELECT DISTINCT
68 pip.id
69 , pip.name
70 FROM hxt_prem_interact_policies pip;
71 --
72 -- cursor to get all Holiday Calendars.
73 --
74 CURSOR c_get_hol_cals IS
75 SELECT DISTINCT
76 hcl.id
77 , hcl.name
78 FROM hxt_holiday_calendars hcl;
79 --
80 -- cursor to get all Earning Policies
81 --
82 CURSOR c_get_earn_pols IS
83 SELECT DISTINCT
84 hep.id
85 , hep.name
86 , hep.hcl_id
87 , hep.pip_id
88 , hep.pep_id
89 , hep.egt_id
90 , nvl(hep.business_group_id,-1) bg_id
91 FROM hxt_earning_policies hep;
92 --
93 -- cursor to get all Shift Differential Policies.
94 --
95 CURSOR c_get_shift_diffs IS
96 SELECT DISTINCT
97 sdp.id
98 , sdp.name
99 FROM hxt_shift_diff_policies sdp;
100 --
101 -- cursor to get all Additional Assignment Info.
102 --
103 CURSOR c_get_add_ass_info IS
104 SELECT DISTINCT
105 aai.id id
106 , aai.assignment_id ass_id
107 , aai.rotation_plan rp_id
108 , aai.earning_policy ep_id
109 , epg.hcl_id ep_hcl
110 , epg.pip_id ep_pip
111 , epg.pep_id ep_pep
112 , epg.egt_id ep_egt
113 , nvl(epg.business_group_id,-1) ep_bg
114 , aai.shift_differential_policy sdp_id
115 , ass.business_group_id ass_bg
116 , nvl(hdp.business_group_id,-1) hdp_bg
117 FROM hxt_add_assign_info_f aai
118 , hxt_rotation_plans rpl
119 , hxt_earning_policies epg
120 , hxt_shift_diff_policies sdp
121 , hxt_hour_deduct_policies hdp
122 , per_assignments_f ass
123 WHERE aai.assignment_id = ass.assignment_id
124 AND aai.rotation_plan = rpl.id (+)
125 AND aai.earning_policy = epg.id
126 AND aai.shift_differential_policy = sdp.id (+)
127 AND aai.hour_deduction_policy = hdp.id (+);
128 --
129 -- cursor to get all Timecards.
130 --
131 CURSOR c_get_timecards IS
132 SELECT DISTINCT
133 tim.id id
134 , ptp.period_name period
135 , ppf.business_group_id per_bg
136 , nvl(pbh.business_group_id,-1) batch_bg
137 , ppr.business_group_id pay_bg
138 FROM hxt_timecards tim
139 , per_people_f ppf
140 , pay_payrolls ppr
141 , pay_batch_headers pbh
142 , per_time_periods ptp
143 WHERE tim.for_person_id = ppf.person_id
144 AND tim.batch_id = pbh.batch_id (+)
145 AND tim.payroll_id = ppr.payroll_id
146 AND tim.time_period_id = ptp.time_period_id;
147 --
148 -- Declare local procedures
149 --
150 -- Workplans Validation Procedure.
151 --
152 PROCEDURE chk_workplans
153 (p_wp_id IN number
154 ,p_wp_name IN varchar2
155 ,p_wp_bg OUT NOCOPY number
156 ,p_valid OUT NOCOPY boolean
157 ) IS
158 --
159 CURSOR c_wp_bg_check IS
160 SELECT DISTINCT(pet.business_group_id) bg
161 FROM hxt_work_shifts wsh
162 , pay_element_types_f pet
163 WHERE wsh.tws_id = p_wp_id
164 AND wsh.shift_diff_ovrrd_id = pet.element_type_id
165 AND pet.business_group_id is not null
166 UNION
167 SELECT DISTINCT(pet.business_group_id) bg
168 FROM hxt_work_shifts ws
169 , pay_element_types_f pet
170 WHERE ws.tws_id = p_wp_id
171 AND ws.off_shift_prem_id = pet.element_type_id
172 AND pet.business_group_id is not null
173 UNION
174 SELECT business_group_id
175 FROM hxt_weekly_work_schedules
176 WHERE id = p_wp_id
177 AND business_group_id is not null;
178 --
179 l_count number := 0;
180 l_valid boolean := True;
181 l_bg number := -1;
182 --
183 Begin
184 for wpbg in c_wp_bg_check loop
185 l_count := l_count +1;
186 if l_count > 1 then
187 hxt_bg_message_insert('V','Workplan '||p_wp_name||' Is Invalid');
188 l_valid := False;
189 l_bg := -1;
190 else
191 l_bg := wpbg.bg;
192 end if;
193 end loop;
194 --
195 p_valid := l_valid;
196 p_wp_bg := l_bg;
197 --
198 End chk_workplans;
199 --
200 -- Rotation Plans Validation Procedure.
201 --
202 Procedure chk_rotplans
203 (p_rp_id IN number
204 ,p_rp_name IN varchar2
205 ,p_rp_bg OUT NOCOPY number
206 ,p_valid OUT NOCOPY boolean
207 ) is
208 --
209 CURSOR c_rp_bg_check is
210 select distinct(pet.business_group_id) bg
211 from hxt_rotation_schedules rts
212 , hxt_weekly_work_schedules wws
213 , hxt_work_shifts wsh
214 , pay_element_types_f pet
215 where wws.id = rts.tws_id
216 and rts.rtp_id = p_rp_id
217 and wsh.tws_id = wws.id
218 and wsh.off_shift_prem_id = pet.element_type_id
219 and pet.business_group_id is not null
220 union
221 select distinct(pet.business_group_id) bg
222 from hxt_rotation_schedules rts
223 , hxt_weekly_work_schedules wws
224 , hxt_work_shifts wsh
225 , pay_element_types_f pet
226 where wws.id = rts.tws_id
227 and rts.rtp_id = p_rp_id
228 and wsh.tws_id = wws.id
229 and wsh.shift_diff_ovrrd_id = pet.element_type_id
230 and pet.business_group_id is not null
231 union
232 select distinct
233 wws.business_group_id
234 from hxt_weekly_work_schedules wws
235 , hxt_rotation_schedules rts
236 where wws.id = rts.tws_id
237 and rts.rtp_id = p_rp_id
238 and wws.business_group_id is not null;
239 --
240 l_bg number := -1;
241 l_valid boolean := True;
242 l_count number := 0;
243 --
244 Begin
245 for rpbg in c_rp_bg_check loop
246 l_count := l_count +1;
247 if l_count > 1 then
248 hxt_bg_message_insert('V','Rotation Plan '||p_rp_name||' Is Invalid');
249 l_valid := False;
250 l_bg := -1;
251 else
252 l_bg := rpbg.bg;
253 end if;
254 end loop;
255 --
256 p_valid := l_valid;
257 p_rp_bg := l_bg;
258 --
259 End chk_rotplans;
260 --
261 -- Earning Groups Validation Procedure.
262 --
263 Procedure chk_earn_groups
264 (p_eg_id IN number
265 ,p_eg_name IN varchar2
266 ,p_eg_bg OUT NOCOPY number
267 ,p_valid OUT NOCOPY boolean
268 ) is
269 --
270 CURSOR c_eg_bg_check is
271 select distinct(pet.business_group_id) bg
272 from hxt_earn_groups egr
273 , pay_element_types_f pet
274 where egr.egt_id = p_eg_id
275 and egr.element_type_id = pet.element_type_id
276 and pet.business_group_id is not null;
277 --
278 l_bg number := -1;
279 l_valid boolean := True;
280 l_count number := 0;
281 --
282 Begin
283 for earns in c_eg_bg_check loop
284 l_count := l_count +1;
285 if l_count > 1 then
286 hxt_bg_message_insert('V','Earning Group '||p_eg_name||' Is Invalid');
287 l_valid := False;
288 l_bg := -1;
289 else
290 l_bg := earns.bg;
291 end if;
292 end loop;
293 --
294 p_valid := l_valid;
295 p_eg_bg := l_bg;
296 --
297 End chk_earn_groups;
298 --
299 -- Premium Eligibility Policies Validation Procedure.
300 --
301 Procedure chk_prem_elig_pols
302 (p_pep_id IN number
303 ,p_pep_name IN varchar2
304 ,p_pep_bg OUT NOCOPY number
305 ,p_valid OUT NOCOPY boolean
306 ) is
307 --
308 CURSOR c_pep_bg_check is
309 select distinct(pet.business_group_id) bg
310 from hxt_prem_eligblty_pol_rules epr
311 , pay_element_types_f pet
312 where epr.pep_id = p_pep_id
313 and epr.elt_base_id = pet.element_type_id
314 union
315 select distinct(pet.business_group_id) bg
316 from hxt_prem_eligblty_rules elr
317 , pay_element_types_f pet
318 where elr.pep_id = p_pep_id
319 and elr.elt_base_id = pet.element_type_id
320 union
321 select distinct(pet.business_group_id) bg
322 from hxt_prem_eligblty_rules elr
323 , pay_element_types_f pet
324 where elr.pep_id = p_pep_id
325 and elr.elt_premium_id = pet.element_type_id;
326 --
327 l_bg number := -1;
328 l_valid boolean := True;
329 l_count number := 0;
330 --
331 Begin
332 for pepbg in c_pep_bg_check loop
333 l_count := l_count +1;
334 if l_count > 1 then
335 hxt_bg_message_insert('V','Premium Eligibility Policy '||p_pep_name||' Is Invalid');
336 l_valid := False;
337 l_bg := -1;
338 else
339 l_bg := pepbg.bg;
340 end if;
341 end loop;
342 --
343 p_valid := l_valid;
344 p_pep_bg := l_bg;
345 --
346 End chk_prem_elig_pols;
347 --
348 -- Premium Interaction Policies Validation Procedure
349 --
350 Procedure chk_prem_inter_pols
351 (p_pip_id IN number
352 ,p_pip_name IN varchar2
353 ,p_pip_bg OUT NOCOPY number
354 ,p_valid OUT NOCOPY boolean
355 ) is
356 --
357 CURSOR c_pip_bg_check is
358 select distinct(pet.business_group_id) bg
359 from hxt_prem_interact_rules itr
360 , pay_element_types_f pet
361 where itr.pip_id = p_pip_id
362 and itr.elt_prior_prem_id = pet.element_type_id
363 union
364 select distinct(pet.business_group_id) bg
365 from hxt_prem_interact_rules itr
366 , pay_element_types_f pet
367 where itr.pip_id = p_pip_id
368 and itr.elt_earned_prem_id = pet.element_type_id
369 union
370 select distinct(pet.business_group_id) bg
371 from hxt_prem_interact_pol_rules ipr
372 , pay_element_types_f pet
373 where ipr.pip_id = p_pip_id
374 and ipr.elt_earned_prem_id = pet.element_type_id;
375 --
376 l_bg number := -1;
377 l_valid boolean := True;
378 l_count number := 0;
379 --
380 Begin
381 for pipbg in c_pip_bg_check loop
382 l_count := l_count +1;
383 if l_count > 1 then
384 hxt_bg_message_insert('V','Premium Interaction Policy '||p_pip_name||' Is Invalid');
385 l_valid := False;
386 l_bg := -1;
387 else
388 l_bg := pipbg.bg;
389 end if;
390 end loop;
391 --
392 p_valid := l_valid;
393 p_pip_bg := l_bg;
394 --
395 End chk_prem_inter_pols;
396 --
397 -- Holiday Calendars Validation Procedure.
398 --
399 Procedure chk_holiday_cals
400 (p_hcl_id IN number
401 ,p_hcl_name IN varchar2
402 ,p_hcl_bg OUT NOCOPY number
403 ,p_valid OUT NOCOPY boolean
404 ) is
405 --
406 CURSOR c_hcl_bg_check is
407 select distinct(pet.business_group_id) bg
408 from hxt_holiday_calendars hcl
409 , pay_element_types_f pet
410 where hcl.id = p_hcl_id
411 and hcl.element_type_id = pet.element_type_id
412 and pet.business_group_id is not null
413 union
414 select distinct(hou.business_group_id) bg
415 from hxt_holiday_calendars hcl
416 , hr_organization_units hou
417 where hcl.id = p_hcl_id
418 and hcl.organization_id = hou.organization_id
419 and hou.business_group_id is not null;
420 --
421 l_count number := 0;
422 l_valid boolean := True;
423 l_bg number := -1;
424 --
425 Begin
426 for holcal in c_hcl_bg_check loop
427 l_count := l_count +1;
428 if l_count > 1 then
429 hxt_bg_message_insert('V','Holiday Calendar '||p_hcl_name||' Is Invalid');
430 l_valid := False;
431 l_bg := -1;
432 else
433 l_bg := holcal.bg;
434 end if;
435 end loop;
436 --
437 p_valid := l_valid;
438 p_hcl_bg := l_bg;
439 --
440 End chk_holiday_cals;
441 --
442 -- Earning Rules Validation Procedure.
443 --
444 Procedure chk_earn_rules
445 (p_epr_id IN number
446 ,p_epr_name IN varchar2
447 ,p_epr_bg OUT NOCOPY number
451 CURSOR c_eprules_bg_check is
448 ,p_valid OUT NOCOPY boolean
449 ) is
450 --
452 select distinct(pet.business_group_id) bg
453 from hxt_earning_rules her
454 , pay_element_types_f pet
455 where her.egp_id = p_epr_id
456 and her.element_type_id = pet.element_type_id
457 and pet.business_group_id is not null;
458 --
459 l_count number := 0;
460 l_valid boolean := True;
461 l_bg number := -1;
462 --
463 Begin
464 for eprules in c_eprules_bg_check loop
465 l_count := l_count +1;
466 if l_count > 1 then
467 hxt_bg_message_insert('V','Earning Policy '||p_epr_name||' Has Invalid Rules');
468 l_valid := False;
469 l_bg := -1;
470 else
471 l_bg := eprules.bg;
472 end if;
473 end loop;
474 --
475 p_valid := l_valid;
476 p_epr_bg := l_bg;
477 --
478 End chk_earn_rules;
479 --
480 -- Earnings Policy Validation Procedure.
481 --
482 Procedure chk_earn_pols
483 (p_egp_id IN number
484 ,p_egp_name IN varchar2
485 ,p_hcl_id IN number
486 ,p_pip_id IN number
487 ,p_pep_id IN number
488 ,p_egt_id IN number
489 ,p_in_bg IN number
490 ,p_egp_bg OUT NOCOPY number
491 ,p_valid OUT NOCOPY boolean
492 ) is
493 --
494 l_valid boolean := True;
495 l_ret_valid boolean := True;
496 l_hcl_bg number := -1;
497 l_pip_bg number := -1;
498 l_pep_bg number := -1;
499 l_egt_bg number := -1;
500 l_epr_bg number := -1;
501 l_bg number := -1;
502 --
503 Begin
504 --
505 l_bg := p_in_bg;
506 --
507 chk_earn_rules(p_egp_id, g_token, l_epr_bg, l_valid);
508 if not l_valid then
509 l_ret_valid := False;
510 elsif l_bg = -1 and l_epr_bg <> -1 then
511 l_bg := l_epr_bg;
512 elsif l_bg <> -1 and l_epr_bg <> -1 and l_bg <> l_epr_bg then
513 l_ret_valid := False;
514 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: EPR');
515 end if;
516 --
517 chk_holiday_cals(p_hcl_id, g_token, l_hcl_bg, l_valid);
518 if not l_valid then
519 l_ret_valid := False;
520 elsif l_bg = -1 and l_hcl_bg <> -1 then
521 l_bg := l_hcl_bg;
522 elsif l_bg <> -1 and l_hcl_bg <> -1 and l_bg <> l_hcl_bg then
523 l_ret_valid := False;
524 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: HCL');
525 end if;
526 --
527 if p_pip_id is not null then
528 chk_prem_inter_pols(p_pip_id, g_token, l_pip_bg, l_valid);
529 if not l_valid then
530 l_ret_valid := False;
531 elsif l_bg = -1 and l_pip_bg <> -1 then
532 l_bg := l_pip_bg;
533 elsif l_bg <> -1 and l_pip_bg <> -1 and l_bg <> l_pip_bg then
534 l_ret_valid := False;
535 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: PIP');
536 end if;
537 end if;
538 --
539 if p_pep_id is not null then
540 chk_prem_elig_pols(p_pep_id, g_token, l_pep_bg, l_valid);
541 if not l_valid then
542 l_ret_valid := False;
543 elsif l_bg = -1 and l_pep_bg <> -1 then
544 l_bg := l_pep_bg;
545 elsif l_bg <> -1 and l_pep_bg <> -1 and l_bg <> l_pep_bg then
546 l_ret_valid := False;
547 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: PEP');
548 end if;
549 end if;
550 --
551 if p_egt_id is not null then
552 chk_earn_groups(p_egt_id, g_token, l_egt_bg, l_valid);
553 if not l_valid then
554 l_ret_valid := False;
555 elsif l_bg = -1 and l_egt_bg <> -1 then
556 l_bg := l_egt_bg;
557 elsif l_bg <> -1 and l_egt_bg <> -1 and l_bg <> l_egt_bg then
558 l_ret_valid := False;
559 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: EGT');
560 end if;
561 end if;
562 --
563 if l_ret_valid = False then
564 hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' Has Invalid References');
565 end if;
566 --
567 p_valid := l_ret_valid;
568 p_egp_bg := l_bg;
569 --
570 End chk_earn_pols;
571 --
572 -- Shift Differential Policies Validation Procedure.
573 --
574 Procedure chk_shift_diffs
575 (p_sdp_id IN number
576 ,p_sdp_name IN varchar2
577 ,p_sdp_bg OUT NOCOPY number
578 ,p_valid OUT NOCOPY boolean
579 ) is
580 --
581 CURSOR c_sdp_bg_check is
582 select distinct(pet.business_group_id) bg
583 from hxt_shift_diff_rules sdr
584 , pay_element_types_f pet
585 where sdr.sdp_id = p_sdp_id
586 and sdr.element_type_id = pet.element_type_id
587 and pet.business_group_id is not null;
588 --
589 l_bg number := -1;
590 l_valid boolean := True;
591 l_count number := 0;
592 --
593 Begin
594 for diffs in c_sdp_bg_check loop
598 l_valid := False;
595 l_count := l_count +1;
596 if l_count > 1 then
597 hxt_bg_message_insert('V','Shift Differential Policy '||p_sdp_name||' Is Invalid');
599 l_bg := -1;
600 else
601 l_bg := diffs.bg;
602 end if;
603 end loop;
604 --
605 p_valid := l_valid;
606 p_sdp_bg := l_bg;
607 --
608 End chk_shift_diffs;
609 --
610 -- Additional Assignment Info Validation Procedure.
611 --
612 Procedure chk_add_assign_info
613 (p_aai_id IN number
614 ,p_ass_id IN number
615 ,p_rp_id IN number
616 ,p_ep_id IN number
617 ,p_ep_hcl IN number
618 ,p_ep_pip IN number
619 ,p_ep_pep IN number
620 ,p_ep_egt IN number
621 ,p_ep_bg IN number
622 ,p_sdp_id IN number
623 ,p_ass_bg IN number
624 ,p_hdp_bg IN number
625 ,p_valid OUT NOCOPY boolean
626 ) is
627 --
628 l_valid boolean := True;
629 l_ret_valid boolean := True;
630 l_rp_bg number := -1;
631 l_ep_bg number := -1;
632 l_sdp_bg number := -1;
633 l_hdp_bg number := -1;
634 l_ass_bg number := -1;
635 l_bg number := -1;
636 --
637 Begin
638 l_ass_bg := p_ass_bg;
639 l_hdp_bg := p_hdp_bg;
640 l_bg := l_ass_bg;
641 --
642 if l_hdp_bg <> -1 and l_hdp_bg <> l_bg then
643 l_ret_valid := False;
644 hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: HDP');
645 end if;
646 --
647 if p_rp_id is not null then
648 chk_rotplans(p_rp_id, g_token, l_rp_bg, l_valid);
649 if not l_valid then
650 l_ret_valid := False;
651 elsif l_rp_bg <> -1 and l_rp_bg <> l_bg then
652 l_ret_valid := False;
653 hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: RP');
654 end if;
655 end if;
656 --
657 if p_sdp_id is not null then
658 chk_shift_diffs(p_sdp_id, g_token, l_sdp_bg, l_valid);
659 if not l_valid then
660 l_ret_valid := False;
661 elsif l_sdp_bg <> -1 and l_bg <> l_sdp_bg then
662 l_ret_valid := False;
663 hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: SDP');
664 end if;
665 end if;
666 --
667 chk_earn_pols(p_ep_id, g_token, p_ep_hcl, p_ep_pip, p_ep_pep, p_ep_egt, p_ep_bg, l_ep_bg, l_valid);
668 if not l_valid then
669 l_ret_valid := False;
670 elsif l_ep_bg <> -1 and l_bg <> l_ep_bg then
671 l_ret_valid := False;
672 hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: EP');
673 end if;
674 --
675 if l_ret_valid = False then
676 hxt_bg_message_insert('V','Assignment ID '||p_ass_id||' Has Invalid References');
677 end if;
678 --
679 p_valid := l_ret_valid;
680 --
681 End chk_add_assign_info;
682 --
683 -- Sum Hours Worked Validation Procedure.
684 --
685 Procedure chk_sum_hours
686 (p_tim_id IN number
687 ,p_period IN varchar2
688 ,p_sum_bg OUT NOCOPY number
689 ,p_valid OUT NOCOPY boolean
690 ) is
691 --
692 CURSOR c_shw_bg_check is
693 select distinct(pet.business_group_id) bg
694 from hxt_sum_hours_worked_f shw
695 , pay_element_types_f pet
696 where shw.tim_id = p_tim_id
697 and shw.element_type_id = pet.element_type_id
698 and pet.business_group_id is not null
699 union
700 select distinct(ass.business_group_id) bg
701 from hxt_sum_hours_worked_f shw
702 , per_assignments_f ass
703 where shw.tim_id = p_tim_id
704 and shw.assignment_id = ass.assignment_id;
705 --
706 l_count number := 0;
707 l_valid boolean := True;
708 l_bg number := -1;
709 --
710 Begin
711 for sumhrs in c_shw_bg_check loop
712 l_count := l_count +1;
713 if l_count > 1 then
714 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' Has Invalid Summary Hours References');
715 l_valid := False;
716 l_bg := -1;
717 else
718 l_bg := sumhrs.bg;
719 end if;
720 end loop;
721 --
722 p_valid := l_valid;
723 p_sum_bg := l_bg;
724 --
725 End chk_sum_hours;
726 --
727 -- Det Hours Worked Validation Procedure.
728 --
729 Procedure chk_det_hours
730 (p_tim_id IN number
731 ,p_period IN varchar2
732 ,p_det_bg OUT NOCOPY number
733 ,p_valid OUT NOCOPY boolean
734 ) is
735 --
736 CURSOR c_dhw_bg_check is
737 select distinct(pet.business_group_id) bg
738 from hxt_det_hours_worked_f dhw
739 , pay_element_types_f pet
740 where dhw.tim_id = p_tim_id
741 and dhw.element_type_id = pet.element_type_id
742 and pet.business_group_id is not null
743 union
744 select distinct(ass.business_group_id) bg
745 from hxt_det_hours_worked_f dhw
749 --
746 , per_assignments_f ass
747 where dhw.tim_id = p_tim_id
748 and dhw.assignment_id = ass.assignment_id;
750 l_count number := 0;
751 l_valid boolean := True;
752 l_bg number := -1;
753 --
754 Begin
755 for dethrs in c_dhw_bg_check loop
756 l_count := l_count +1;
757 if l_count > 1 then
758 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' Has Invalid Det Hours References');
759 l_valid := False;
760 l_bg := -1;
761 else
762 l_bg := dethrs.bg;
763 end if;
764 end loop;
765 --
766 p_valid := l_valid;
767 p_det_bg := l_bg;
768 --
769 End chk_det_hours;
770 --
771 -- Timecards Validation Procedure.
772 --
773 Procedure chk_timecards
774 (p_tim_id IN number
775 ,p_period IN varchar2
776 ,p_per_bg IN number
777 ,p_batch_bg IN number
778 ,p_pay_bg IN number
779 ,p_valid OUT NOCOPY boolean
780 ) is
781 --
782 l_valid boolean := True;
783 l_ret_valid boolean := True;
784 l_per_bg number := -1;
785 l_batch_bg number := -1;
786 l_pay_bg number := -1;
787 l_sum_bg number := -1;
788 l_det_bg number := -1;
789 l_bg number := -1;
790 --
791 Begin
792 l_per_bg := p_per_bg;
793 l_batch_bg := p_batch_bg;
794 l_pay_bg := p_pay_bg;
795 l_bg := l_per_bg;
796 --
797 if l_batch_bg <> -1 and l_batch_bg <> l_bg then
798 l_ret_valid := False;
799 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Pay Batch Is Invalid');
800 end if;
801 --
802 if l_pay_bg <> -1 and l_pay_bg <> l_bg then
803 l_ret_valid := False;
804 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Payroll Is Invalid');
805 end if;
806 --
807 chk_sum_hours(p_tim_id, p_period, l_sum_bg, l_valid);
808 if not l_valid then
809 l_ret_valid := False;
810 elsif l_sum_bg <> -1 and l_sum_bg <> l_bg then
811 l_ret_valid := False;
812 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Summary Information References Invalid Business Group');
813 end if;
814 --
815 chk_det_hours(p_tim_id, p_period, l_det_bg, l_valid);
816 if not l_valid then
817 l_ret_valid := False;
818 elsif l_det_bg <> -1 and l_det_bg <> l_bg then
819 l_ret_valid := False;
820 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Detail Information References Invalid Business Group');
821 end if;
822 --
823 if l_ret_valid = False then
824 hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Has Invalid References.');
825 end if;
826 --
827 p_valid := l_ret_valid;
828 --
829 End chk_timecards;
830 --
831 -- Begin main function processing
832 --
833 Begin
834 --
835 --
836 --
837 -- Check workplans.
838 --
839 for wkplans in c_get_workplans loop
840 chk_workplans(wkplans.id, wkplans.name, g_sub_bg, g_sub_valid);
841 if g_sub_valid = False then
842 g_valid := False;
843 end if;
844 end loop;
845 --
846 -- Check rotation plans.
847 --
848 for rotplans in c_get_rotation_plans loop
849 chk_rotplans(rotplans.id, rotplans.name, g_sub_bg, g_sub_valid);
850 if g_sub_valid = False then
851 g_valid := False;
852 end if;
853 end loop;
854 --
855 -- Check earning groups.
856 --
857 for earngrps in c_get_earn_groups loop
858 chk_earn_groups(earngrps.id, earngrps.name, g_sub_bg, g_sub_valid);
859 if g_sub_valid = False then
860 g_valid := False;
861 end if;
862 end loop;
863 --
864 -- Check premium eligibility policies.
865 --
866 for peps in c_get_peps loop
867 chk_prem_elig_pols(peps.id, peps.name, g_sub_bg, g_sub_valid);
868 if g_sub_valid = False then
869 g_valid := False;
870 end if;
871 end loop;
872 --
873 -- Check premium interaction policies.
874 --
875 for pips in c_get_pips loop
876 chk_prem_inter_pols(pips.id, pips.name, g_sub_bg, g_sub_valid);
877 if g_sub_valid = False then
878 g_valid := False;
879 end if;
880 end loop;
881 --
882 -- Check holiday calendars.
883 --
884 for hols in c_get_hol_cals loop
885 chk_holiday_cals(hols.id, hols.name, g_sub_bg, g_sub_valid);
886 if g_sub_valid = False then
887 g_valid := False;
888 end if;
889 end loop;
890 --
891 -- Check earning policies.
892 --
893 for epols in c_get_earn_pols loop
894 g_token := 'For Earnings Policy '||epols.name;
895 chk_earn_pols(epols.id, epols.name, epols.hcl_id, epols.pip_id, epols.pep_id, epols.egt_id, epols.bg_id, g_sub_bg, g_sub_valid);
896 if g_sub_valid = False then
897 g_valid := False;
898 end if;
899 end loop;
900 --
904 chk_shift_diffs(sdiffs.id, sdiffs.name, g_sub_bg, g_sub_valid);
901 -- Check shift differential policies.
902 --
903 for sdiffs in c_get_shift_diffs loop
905 if g_sub_valid = False then
906 g_valid := False;
907 end if;
908 end loop;
909 --
910 -- Check additional assignment information.
911 --
912 for addass in c_get_add_ass_info loop
913 g_token := 'For Assignment ID '||addass.ass_id;
914 chk_add_assign_info(addass.id, addass.ass_id, addass.rp_id, addass.ep_id, addass.ep_hcl, addass.ep_pip, addass.ep_pep,
915 addass.ep_egt, addass.ep_bg, addass.sdp_id, addass.ass_bg, addass.hdp_bg, g_sub_valid);
916 if g_sub_valid = False then
917 g_valid := False;
918 end if;
919 end loop;
920 --
921 -- Check timecards.
922 --
923 for times in c_get_timecards loop
924 chk_timecards(times.id, times.period, times.per_bg, times.batch_bg, times.pay_bg, g_sub_valid);
925 if g_sub_valid = False then
926 g_valid := False;
927 end if;
928 end loop;
929 --
930 --
931 -- Return Validation Status
932 --
933 return(g_valid);
934 --
935 End;
936 -------------------------------------------------------------------------------
937 PROCEDURE hxt_bg_workplans_update IS
938 --
939 g_rp_bg number := 0;
940 g_ass_bg number := 0;
941 g_rp_counter number := 0;
942 --
943 CURSOR c_bg_workplans IS
944 select distinct
945 wsh.tws_id id
946 , pet.business_group_id bg
947 from hxt_weekly_work_schedules wws
948 , hxt_work_shifts wsh
949 , pay_element_types_f pet
950 where wws.business_group_id is null
951 and wws.id = wsh.tws_id
952 and ((wsh.off_shift_prem_id = pet.element_type_id
953 and pet.business_group_id is not null)
954 or (wsh.shift_diff_ovrrd_id = pet.element_type_id
955 and pet.business_group_id is not null));
956 --
957 CURSOR c_global_workplans is
958 select distinct
959 wws.id wp_id
960 , rp_sub.rp_count rp_cnt
961 , ass_sub.ass_count ass_cnt
962 from hxt_weekly_work_schedules wws
963 , (select tws_id wp_id
964 , count(rtp_id) rp_count
965 from hxt_rotation_schedules
966 group by tws_id) rp_sub
967 , (select rts.tws_id wp_id
968 , count(distinct(ass.business_group_id)) ass_count
969 from per_assignments_f ass
970 , hxt_add_assign_info_f aai
971 , hxt_rotation_plans rtp
972 , hxt_rotation_schedules rts
973 where ass.assignment_id = aai.assignment_id
974 and aai.rotation_plan = rtp.id
975 and rtp.id = rts.rtp_id
976 group by rts.tws_id) ass_sub
977 where wws.business_group_id is null
978 and wws.id = ass_sub.wp_id (+)
979 and wws.id = rp_sub.wp_id (+)
980 and not exists(select 'X'
981 from hxt_work_shifts wsh
982 , pay_element_types_f pet
983 where wws.id = wsh.tws_id
984 and ((wsh.off_shift_prem_id = pet.element_type_id
985 and pet.business_group_id is not null)
986 or (wsh.shift_diff_ovrrd_id = pet.element_type_id
987 and pet.business_group_id is not null)));
988 --
989 CURSOR c_get_rp_bg(p_wp_id number) is
990 select distinct(pet.business_group_id) rp_bg
991 from hxt_rotation_schedules rts
992 , hxt_weekly_work_schedules wws
993 , hxt_work_shifts wsh
994 , pay_element_types_f pet
995 where rts.tws_id = wws.id
996 and wws.id = wsh.tws_id
997 and ((wsh.off_shift_prem_id = pet.element_type_id
998 and pet.business_group_id is not null)
999 or (wsh.shift_diff_ovrrd_id = pet.element_type_id
1000 and pet.business_group_id is not null))
1001 and rts.rtp_id IN (select sub.rtp_id
1002 from hxt_rotation_schedules sub
1003 where sub.tws_id = p_wp_id)
1004 union
1005 select distinct(wws.business_group_id) rp_bg
1006 from hxt_rotation_schedules rts
1007 , hxt_weekly_work_schedules wws
1008 where rts.tws_id = wws.id
1009 and wws.business_group_id is not null
1010 and rts.rtp_id IN (select sub.rtp_id
1011 from hxt_rotation_schedules sub
1012 where sub.tws_id = p_wp_id);
1013 --
1014 CURSOR c_get_ass_bg(p_wp_id number) is
1015 select distinct(ass.business_group_id) ass_bg
1016 from per_assignments_f ass
1017 , hxt_add_assign_info_f aai
1018 , hxt_rotation_plans rtp
1019 , hxt_rotation_schedules rts
1020 where ass.assignment_id = aai.assignment_id
1021 and aai.rotation_plan = rtp.id
1022 and rtp.id = rts.rtp_id
1026 --
1023 and rts.tws_id = p_wp_id;
1024 --
1025 -- Workplan Update Procedure.
1027 Procedure update_workplans
1028 (p_wp_id IN number
1029 ,p_bg_id IN number
1030 ) is
1031 --
1032 l_wp_id number;
1033 l_bg_id number;
1034 --
1035 Begin
1036 l_wp_id := p_wp_id;
1037 l_bg_id := p_bg_id;
1038 --
1039 UPDATE hxt_weekly_work_schedules
1040 SET business_group_id = l_bg_id
1041 WHERE id = l_wp_id;
1042 --
1043 hxt_bg_message_insert('U','Updating Workplan ID '||l_wp_id||' With Business Group '||l_bg_id);
1044 --
1045 End update_workplans;
1046 --
1047 -- Rotation Plan References Update Procedure.
1048 --
1049 Procedure update_rp_refs
1050 (p_bg IN number
1051 ,p_old_id IN number
1052 ,p_new_id IN number
1053 ) is
1054 --
1055 l_new_id number;
1056 l_aai_id number;
1057 l_ass_id number;
1058 l_aai_esd date;
1059 l_aai_eed date;
1060 --
1061 CURSOR c_get_ass_info is
1062 select aai.id id
1063 , aai.assignment_id ass_id
1064 , aai.effective_start_date esd
1065 , aai.effective_end_date eed
1066 from hxt_add_assign_info_f aai
1067 , per_assignments_f ass
1068 where ass.business_group_id = p_bg
1069 and ass.assignment_id = aai.assignment_id
1070 and aai.rotation_plan = p_old_id;
1071 --
1072 Begin
1073 l_new_id := p_new_id;
1074 --
1075 For assign in c_get_ass_info loop
1076 --
1077 l_aai_id := assign.id;
1078 l_ass_id := assign.ass_id;
1079 l_aai_esd := assign.esd;
1080 l_aai_eed := assign.eed;
1081 --
1082 update hxt_add_assign_info_f
1083 set rotation_plan = l_new_id
1084 where id = l_aai_id
1085 and effective_start_date = l_aai_esd
1086 and effective_end_date = l_aai_eed;
1087 --
1088 hxt_bg_message_insert('U','Updating Assignment ID '||l_ass_id||' To Reference Rotation Plan ID '||l_new_id);
1089 --
1090 end loop;
1091 --
1092 End update_rp_refs;
1093 --
1094 -- Workplan Duplication Procedure.
1095 --
1096 Procedure duplicate_workplans (p_wp_id IN number) is
1097 --
1098 l_id number;
1099 --
1100 CURSOR c_all_bg is
1101 select distinct
1102 pbg.business_group_id id
1103 , to_char(pbg.business_group_id) name
1104 from per_business_groups pbg;
1105 --
1106 CURSOR c_seqno is
1107 select hxt_seqno.nextval
1108 from dual;
1109 --
1110 CURSOR c_workplan_rec is
1111 select name
1112 , start_day
1113 , date_from
1114 , description
1115 , date_to
1116 from hxt_weekly_work_schedules
1117 where id = p_wp_id;
1118 --
1119 CURSOR c_workshift_rec is
1120 select sht_id
1121 , week_day
1122 , seq_no
1123 , early_start
1124 , late_stop
1125 , off_shift_prem_id
1126 , shift_diff_ovrrd_id
1127 from hxt_work_shifts
1128 where tws_id = p_wp_id;
1129 --
1130 Begin
1131 --
1132 hxt_bg_message_insert('U','Duplicating Workplan ID '||p_wp_id||' Across All Business Groups');
1133 --
1134 For busg in c_all_bg loop
1135 --
1136 For r_workplan_rec in c_workplan_rec loop
1137 --
1138 l_id := 0;
1139 --
1140 open c_seqno;
1141 fetch c_seqno into l_id;
1142 close c_seqno;
1143 --
1144 Insert Into hxt_weekly_work_schedules
1145 ( id
1146 , name
1147 , start_day
1148 , date_from
1149 , description
1150 , date_to
1151 , created_by
1152 , creation_date
1153 , last_updated_by
1154 , last_update_date
1155 , last_update_login
1156 , business_group_id
1157 )
1158 Values
1159 ( l_id
1160 , r_workplan_rec.name||'-'||busg.name
1161 , r_workplan_rec.start_day
1162 , r_workplan_rec.date_from
1163 , r_workplan_rec.description
1164 , r_workplan_rec.date_to
1165 , -1
1166 , sysdate
1167 , -1
1168 , sysdate
1169 , -1
1170 , busg.id
1171 );
1172 --
1173 For shifts in c_workshift_rec loop
1174 Insert into hxt_work_shifts
1175 ( sht_id
1176 , tws_id
1177 , week_day
1178 , seq_no
1179 , early_start
1180 , late_stop
1181 , created_by
1182 , creation_date
1183 , last_updated_by
1184 , last_update_date
1185 , last_update_login
1186 , off_shift_prem_id
1187 , shift_diff_ovrrd_id
1188 )
1189 Values
1190 ( shifts.sht_id
1191 , l_id
1192 , shifts.week_day
1193 , shifts.seq_no
1194 , shifts.early_start
1195 , shifts.late_stop
1196 , -1
1197 , sysdate
1198 , -1
1199 , sysdate
1200 , -1
1201 , shifts.off_shift_prem_id
1205 --
1202 , shifts.shift_diff_ovrrd_id
1203 );
1204 end loop;
1206 end loop;
1207 --
1208 end loop;
1209 --
1210 delete from hxt_work_shifts
1211 where tws_id = p_wp_id;
1212 --
1213 delete from hxt_weekly_work_schedules
1214 where id = p_wp_id;
1215 --
1216 End duplicate_workplans;
1217 --
1218 -- Rotation Plan Duplication Procedure.
1219 --
1220 Procedure duplicate_rotation_plans
1221 (p_wp_id IN number
1222 ,p_refs IN varchar2
1223 ) is
1224 --
1225 l_wp_id number;
1226 l_rp_id number;
1227 l_rp_counter number := 0;
1228 --
1229 TYPE t_delete_recs is table of NUMBER INDEX BY BINARY_INTEGER;
1230 --
1231 l_delete_wp t_delete_recs;
1232 l_delete_rp t_delete_recs;
1233 --
1234 CURSOR c_all_bg is
1235 select distinct
1236 pbg.business_group_id id
1237 , to_char(pbg.business_group_id) name
1238 from per_business_groups pbg;
1239 --
1240 CURSOR c_seqno is
1241 select hxt_seqno.nextval
1242 from dual;
1243 --
1244 CURSOR c_workplan_rec(p_rp_id number) is
1245 select distinct
1246 wws.id
1247 , wws.name
1248 , wws.start_day
1249 , wws.date_from
1250 , wws.description
1251 , wws.date_to
1252 from hxt_weekly_work_schedules wws
1253 , hxt_rotation_schedules rts
1254 where wws.id = rts.tws_id
1255 and rts.rtp_id = p_rp_id;
1256 --
1257 CURSOR c_workshift_rec(p_wp_id number) is
1258 select distinct
1259 sht_id
1260 , week_day
1261 , seq_no
1262 , early_start
1263 , late_stop
1264 , off_shift_prem_id
1265 , shift_diff_ovrrd_id
1266 from hxt_work_shifts
1267 where tws_id = p_wp_id;
1268 --
1269 CURSOR c_rotplan_rec is
1270 select distinct
1271 rtp.id
1272 , rtp.name
1273 , rtp.date_from
1274 , rtp.description
1275 , rtp.date_to
1276 from hxt_rotation_plans rtp
1277 , hxt_rotation_schedules rts
1278 where rtp.id = rts.rtp_id
1279 and rts.tws_id = p_wp_id;
1280 --
1281 CURSOR c_rotschedule_rec(p_wp_id number, p_rp_id number) is
1282 select start_date
1283 from hxt_rotation_schedules
1284 where tws_id = p_wp_id
1285 and rtp_id = p_rp_id;
1286 --
1287 CURSOR c_dup_check(p_bg_id number, p_name varchar2) is
1288 select id
1289 from hxt_weekly_work_schedules
1290 where business_group_id = p_bg_id
1291 and name = p_name;
1292 --
1293 Begin
1294 --
1295 hxt_bg_message_insert('U','Duplicating Rotation Plans For Workplan ID '||p_wp_id||' Across All Business Groups');
1296 --
1297 For busg in c_all_bg loop
1298 --
1299 For rotplans in c_rotplan_rec loop
1300 --
1301 l_rp_id := 0;
1302 --
1303 open c_seqno;
1304 fetch c_seqno into l_rp_id;
1305 close c_seqno;
1306 --
1307 Insert into hxt_rotation_plans
1308 ( id
1309 , name
1310 , date_from
1311 , description
1312 , date_to
1313 , created_by
1314 , creation_date
1315 , last_updated_by
1316 , last_update_date
1317 , last_update_login
1318 )
1319 Values
1320 ( l_rp_id
1321 , rotplans.name||'-'||busg.name
1322 , rotplans.date_from
1323 , rotplans.description
1324 , rotplans.date_to
1325 , -1
1326 , sysdate
1327 , -1
1328 , sysdate
1329 , -1
1330 );
1331 --
1332 For r_workplan_rec in c_workplan_rec(rotplans.id) loop
1333 --
1334 open c_dup_check(busg.id, r_workplan_rec.name||'-'||busg.name);
1335 fetch c_dup_check into l_wp_id;
1336 if c_dup_check%notfound then
1337 close c_dup_check;
1338
1339 open c_seqno;
1340 fetch c_seqno into l_wp_id;
1341 close c_seqno;
1342 --
1343 Insert Into hxt_weekly_work_schedules
1344 ( id
1345 , name
1346 , start_day
1347 , date_from
1348 , description
1349 , date_to
1350 , created_by
1351 , creation_date
1352 , last_updated_by
1353 , last_update_date
1354 , last_update_login
1355 , business_group_id
1356 )
1357 Values
1358 ( l_wp_id
1359 , r_workplan_rec.name||'-'||busg.name
1360 , r_workplan_rec.start_day
1361 , r_workplan_rec.date_from
1362 , r_workplan_rec.description
1363 , r_workplan_rec.date_to
1364 , -1
1365 , sysdate
1366 , -1
1367 , sysdate
1368 , -1
1369 , busg.id
1370 );
1371 --
1372 For shifts in c_workshift_rec(r_workplan_rec.id) loop
1373 Insert into hxt_work_shifts
1374 ( sht_id
1375 , tws_id
1376 , week_day
1377 , seq_no
1381 , creation_date
1378 , early_start
1379 , late_stop
1380 , created_by
1382 , last_updated_by
1383 , last_update_date
1384 , last_update_login
1385 , off_shift_prem_id
1386 , shift_diff_ovrrd_id
1387 )
1388 Values
1389 ( shifts.sht_id
1390 , l_wp_id
1391 , shifts.week_day
1392 , shifts.seq_no
1393 , shifts.early_start
1394 , shifts.late_stop
1395 , -1
1396 , sysdate
1397 , -1
1398 , sysdate
1399 , -1
1400 , shifts.off_shift_prem_id
1401 , shifts.shift_diff_ovrrd_id
1402 );
1403 end loop;
1404 --
1405 else close c_dup_check;
1406 end if;
1407 --
1408 For rotsched in c_rotschedule_rec(r_workplan_rec.id, rotplans.id) loop
1409 Insert into hxt_rotation_schedules
1410 ( rtp_id
1411 , tws_id
1412 , start_date
1413 , created_by
1414 , creation_date
1415 , last_updated_by
1416 , last_update_date
1417 , last_update_login
1418 )
1419 Values
1420 ( l_rp_id
1421 , l_wp_id
1422 , rotsched.start_date
1423 , -1
1424 , sysdate
1425 , -1
1426 , sysdate
1427 , -1
1428 );
1429 end loop;
1430 --
1431 end loop;
1432 --
1433 if p_refs = 'Y' then
1434 update_rp_refs(busg.id, rotplans.id, l_rp_id);
1435 end if;
1436 --
1437 end loop;
1438 --
1439 end loop;
1440 --
1441 For delete_rp in c_rotplan_rec loop
1442 l_rp_counter := l_rp_counter+1;
1443 l_delete_rp(l_rp_counter) := delete_rp.id;
1444 end loop;
1445 --
1446 For i in 1..l_rp_counter loop
1447 delete from hxt_rotation_schedules
1448 where rtp_id = l_delete_rp(i);
1449 end loop;
1450 --
1451 For i in 1..l_rp_counter loop
1452 delete from hxt_rotation_plans
1453 where id = l_delete_rp(i);
1454 end loop;
1455 --
1456 delete from hxt_work_shifts
1457 where tws_id = p_wp_id;
1458 --
1459 delete from hxt_weekly_work_schedules
1460 where id = p_wp_id;
1461 --
1462 End duplicate_rotation_plans;
1463 --
1464 -- Begin Main Processing.
1465 --
1466 Begin
1467 --
1468 --
1469 For wpbg in c_bg_workplans loop
1470 update_workplans(wpbg.id, wpbg.bg);
1471 end loop;
1472 --
1473 For global in c_global_workplans loop
1474 --
1475 g_rp_bg := 0;
1476 g_ass_bg := 0;
1477 g_rp_counter := 0;
1478 --
1479 if global.rp_cnt is null then
1480 --
1481 duplicate_workplans(global.wp_id);
1482 --
1483 elsif global.rp_cnt = 1 and global.ass_cnt is null then
1484 --
1485 open c_get_rp_bg(global.wp_id);
1486 fetch c_get_rp_bg into g_rp_bg;
1487 if c_get_rp_bg%notfound then
1488 duplicate_rotation_plans(global.wp_id,'N');
1489 else
1490 update_workplans(global.wp_id, g_rp_bg);
1491 end if;
1492 close c_get_rp_bg;
1493 --
1494 elsif global.rp_cnt = 1 and global.ass_cnt = 1 then
1495 --
1496 open c_get_ass_bg(global.wp_id);
1497 fetch c_get_ass_bg into g_ass_bg;
1498 update_workplans(global.wp_id, g_ass_bg);
1499 close c_get_ass_bg;
1500 --
1501 elsif global.rp_cnt = 1 and global.ass_cnt > 1 then
1502 --
1503 duplicate_rotation_plans(global.wp_id,'Y');
1504 --
1505 elsif global.rp_cnt > 1 and global.ass_cnt is null then
1506 --
1507 for rotplans in c_get_rp_bg(global.wp_id) loop
1508 g_rp_counter := g_rp_counter +1;
1509 g_rp_bg := rotplans.rp_bg;
1510 end loop;
1511 if g_rp_counter = 0 then
1512 duplicate_rotation_plans(global.wp_id,'N');
1513 elsif g_rp_counter = 1 then
1514 update_workplans(global.wp_id, g_rp_bg);
1515 else
1516 duplicate_rotation_plans(global.wp_id,'N');
1517 end if;
1518 --
1519 elsif global.rp_cnt > 1 and global.ass_cnt = 1 then
1520 --
1521 for rotplans in c_get_rp_bg(global.wp_id) loop
1522 g_rp_counter := g_rp_counter +1;
1523 g_rp_bg := rotplans.rp_bg;
1524 end loop;
1525 open c_get_ass_bg(global.wp_id);
1526 fetch c_get_ass_bg into g_ass_bg;
1527 close c_get_ass_bg;
1528 if g_rp_counter = 0 then
1529 update_workplans(global.wp_id, g_ass_bg);
1530 elsif g_rp_counter = 1 and g_rp_bg = g_ass_bg then
1531 update_workplans(global.wp_id, g_ass_bg);
1532 else
1533 duplicate_rotation_plans(global.wp_id,'Y');
1534 end if;
1535 --
1536 elsif global.rp_cnt > 1 and global.ass_cnt > 1 then
1537 --
1538 duplicate_rotation_plans(global.wp_id,'Y');
1539 --
1540 end if;
1541 --
1542 end loop;
1546 -------------------------------------------------------------------------------
1543 --
1544 --
1545 End;
1547 PROCEDURE hxt_bg_earnings_update IS
1548 --
1549 g_ass_bg number;
1550 g_nonass_bg number;
1551 --
1552 CURSOR c_get_ass_epols is
1553 select distinct
1554 hep.id ep_id
1555 , count(distinct(ass.business_group_id)) count_bg
1556 from hxt_earning_policies hep
1557 , hxt_add_assign_info_f aai
1558 , per_assignments_f ass
1559 where hep.id = aai.earning_policy
1560 and aai.assignment_id = ass.assignment_id
1561 and hep.business_group_id is null
1562 group by hep.id;
1563 --
1564 CURSOR c_get_nonass_epols is
1565 select hep.id ep_id
1566 , hep.hcl_id hcl_id
1567 , hep.pip_id pip_id
1568 , hep.pep_id pep_id
1569 , hep.egt_id egt_id
1570 from hxt_earning_policies hep
1571 where hep.business_group_id is null
1572 and not exists(select 'X'
1573 from hxt_add_assign_info_f aai
1574 where aai.earning_policy = hep.id);
1575 --
1576 CURSOR c_get_ass_bg(p_id number) is
1577 select distinct
1578 ass.business_group_id bg_id
1579 from hxt_add_assign_info_f aai
1580 , per_assignments_f ass
1581 where aai.assignment_id = ass.assignment_id
1582 and aai.earning_policy = p_id;
1583 --
1584 CURSOR c_get_egt_bg(p_id number) is
1585 select distinct
1586 pet.business_group_id bg_id
1587 from hxt_earn_groups egr
1588 , hxt_earn_group_types egt
1589 , pay_element_types_f pet
1590 where egt.id = p_id
1591 and egt.id = egr.egt_id
1592 and egr.element_type_id = pet.element_type_id
1593 and pet.business_group_id is not null;
1594 --
1595 CURSOR c_get_pep_bg(p_id number) is
1596 select distinct
1597 pet.business_group_id bg_id
1598 from hxt_prem_eligblty_policies pep
1599 , hxt_prem_eligblty_pol_rules epr
1600 , pay_element_types_f pet
1601 where pep.id = p_id
1602 and pep.id = epr.pep_id
1603 and epr.elt_base_id = pet.element_type_id
1604 and pet.business_group_id is not null
1605 union
1606 select distinct
1607 pet.business_group_id bg_id
1608 from hxt_prem_eligblty_policies pep
1609 , hxt_prem_eligblty_rules elr
1610 , pay_element_types_f pet
1611 where pep.id = p_id
1612 and pep.id = elr.pep_id
1613 and ((elr.elt_base_id = pet.element_type_id
1614 and pet.business_group_id is not null)
1615 or (elr.elt_premium_id = pet.element_type_id
1616 and pet.business_group_id is not null));
1617 --
1618 CURSOR c_get_pip_bg(p_id number) is
1619 select distinct
1620 pet.business_group_id bg_id
1621 from hxt_prem_interact_policies pip
1622 , hxt_prem_interact_rules itr
1623 , pay_element_types_f pet
1624 where pip.id = p_id
1625 and pip.id = itr.pip_id
1626 and ((itr.elt_prior_prem_id = pet.element_type_id
1627 and pet.business_group_id is not null)
1628 or (itr.elt_earned_prem_id = pet.element_type_id
1629 and pet.business_group_id is not null))
1630 union
1631 select distinct
1632 pet.business_group_id bg_id
1633 from hxt_prem_interact_policies pip
1634 , hxt_prem_interact_pol_rules ipr
1635 , pay_element_types_f pet
1636 where pip.id = p_id
1637 and pip.id = ipr.pip_id
1638 and ipr.elt_earned_prem_id = pet.element_type_id
1639 and pet.business_group_id is not null;
1640 --
1641 CURSOR c_get_hcl_bg(p_id number) is
1642 select distinct
1643 pet.business_group_id bg_id
1644 from hxt_holiday_calendars hcl
1645 , pay_element_types_f pet
1646 where hcl.id = p_id
1647 and hcl.element_type_id = pet.element_type_id
1648 and pet.business_group_id is not null
1649 union
1650 select distinct
1651 hou.business_group_id bg_id
1652 from hxt_holiday_calendars hcl
1653 , hr_organization_units hou
1654 where hcl.id = p_id
1655 and hcl.organization_id = hou.organization_id
1656 and hou.business_group_id is not null;
1657 --
1658 CURSOR c_get_epr_bg(p_id number) is
1659 select distinct
1660 pet.business_group_id bg_id
1661 from hxt_earning_rules epr
1662 , pay_element_types_f pet
1663 where epr.egp_id = p_id
1664 and epr.element_type_id = pet.element_type_id
1665 and pet.business_group_id is not null;
1666 --
1667 -- Update Earning Policies Procedure.
1668 --
1669 Procedure update_earn_policies
1670 (p_ep_id IN number
1671 ,p_bg_id IN number
1672 ) is
1673 --
1674 l_ep_id number;
1675 l_bg_id number;
1676 --
1677 Begin
1678 l_ep_id := p_ep_id;
1679 l_bg_id := p_bg_id;
1680 --
1684 --
1681 UPDATE hxt_earning_policies
1682 SET business_group_id = l_bg_id
1683 WHERE id = l_ep_id;
1685 hxt_bg_message_insert('U','Updating Earning Policy ID '||l_ep_id||' With Business Group ID '||l_bg_id);
1686 --
1687 End update_earn_policies;
1688 --
1689 -- Earnings Policy References Update Procedure.
1690 --
1691 Procedure update_ep_refs
1692 (p_bg IN number
1693 ,p_old_id IN number
1694 ,p_new_id IN number
1695 ) is
1696 --
1697 l_new_id number;
1698 l_aai_id number;
1699 l_ass_id number;
1700 l_aai_esd date;
1701 l_aai_eed date;
1702 --
1703 CURSOR c_get_ass_info is
1704 select aai.id id
1705 , aai.assignment_id ass_id
1706 , aai.effective_start_date esd
1707 , aai.effective_end_date eed
1708 from hxt_add_assign_info_f aai
1709 , per_assignments_f ass
1710 where ass.business_group_id = p_bg
1711 and ass.assignment_id = aai.assignment_id
1712 and aai.earning_policy = p_old_id;
1713 --
1714 Begin
1715 l_new_id := p_new_id;
1716 --
1717 For assign in c_get_ass_info loop
1718 --
1719 l_aai_id := assign.id;
1720 l_ass_id := assign.ass_id;
1721 l_aai_esd := assign.esd;
1722 l_aai_eed := assign.eed;
1723 --
1724 update hxt_add_assign_info_f
1725 set earning_policy = l_new_id
1726 where id = l_aai_id
1727 and effective_start_date = l_aai_esd
1728 and effective_end_date = l_aai_eed;
1729 --
1730 hxt_bg_message_insert('U','Updating Assignment ID '||l_ass_id||' To Reference Earning Policy ID '||l_new_id);
1731 --
1732 end loop;
1733 --
1734 End update_ep_refs;
1735 --
1736 -- Earning Policies Duplication Procedure.
1737 --
1738 Procedure duplicate_earn_policies
1739 (p_ep_id IN number
1740 ,p_refs IN varchar2
1741 ) is
1742 --
1743 l_ep_id number;
1744 l_er_id number;
1745 l_counter number := 0;
1746 l_name varchar2(100);
1747 --
1748 CURSOR c_all_bg is
1749 select distinct
1750 pbg.business_group_id id
1751 , to_char(pbg.business_group_id) name
1752 from per_business_groups pbg;
1753 --
1754 CURSOR c_seqno is
1755 select hxt_seqno.nextval
1756 from dual;
1757 --
1758 CURSOR c_earnpol_rec is
1759 select hcl_id
1760 , fcl_earn_type
1761 , name
1762 , effective_start_date
1763 , pip_id
1764 , pep_id
1765 , egt_id
1766 , description
1767 , effective_end_date
1768 , organization_id
1769 , round_up
1770 , min_tcard_intvl
1771 from hxt_earning_policies
1772 where id = p_ep_id;
1773 --
1774 CURSOR c_earnrule_rec is
1775 select element_type_id
1776 , seq_no
1777 , name
1778 , egr_type
1779 , hours
1780 , effective_start_date
1781 , days
1782 , effective_end_date
1783 from hxt_earning_rules
1784 where egp_id = p_ep_id;
1785 --
1786 Begin
1787 --
1788 For busg in c_all_bg loop
1789 --
1790 l_ep_id := 0;
1791 l_er_id := 0;
1792 l_counter := 0;
1793 --
1794 open c_seqno;
1795 fetch c_seqno into l_ep_id;
1796 close c_seqno;
1797 --
1798 For earnpols in c_earnpol_rec loop
1799 --
1800 l_counter := l_counter +1;
1801 --
1802 Insert into hxt_earning_policies
1803 ( id
1804 , hcl_id
1805 , fcl_earn_type
1806 , name
1807 , effective_start_date
1808 , pip_id
1809 , pep_id
1810 , egt_id
1811 , description
1812 , effective_end_date
1813 , created_by
1814 , creation_date
1815 , last_updated_by
1816 , last_update_date
1817 , last_update_login
1818 , organization_id
1819 , round_up
1820 , min_tcard_intvl
1821 , business_group_id
1822 )
1823 Values
1824 ( l_ep_id
1825 , earnpols.hcl_id
1826 , earnpols.fcl_earn_type
1827 , earnpols.name||'-'||busg.name
1828 , earnpols.effective_start_date
1829 , earnpols.pip_id
1830 , earnpols.pep_id
1831 , earnpols.egt_id
1832 , earnpols.description
1833 , earnpols.effective_end_date
1834 , -1
1835 , sysdate
1836 , -1
1837 , sysdate
1838 , -1
1839 , earnpols.organization_id
1840 , earnpols.round_up
1841 , earnpols.min_tcard_intvl
1842 , busg.id
1843 );
1844 --
1845 if l_counter = 1 then
1846 For rules in c_earnrule_rec loop
1847 --
1848 open c_seqno;
1849 fetch c_seqno into l_er_id;
1850 close c_seqno;
1851 --
1852 Insert into hxt_earning_rules
1853 ( id
1854 , element_type_id
1855 , egp_id
1856 , seq_no
1857 , name
1861 , days
1858 , egr_type
1859 , hours
1860 , effective_start_date
1862 , effective_end_date
1863 , created_by
1864 , creation_date
1865 , last_updated_by
1866 , last_update_date
1867 , last_update_login
1868 )
1869 Values
1870 ( l_er_id
1871 , rules.element_type_id
1872 , l_ep_id
1873 , rules.seq_no
1874 , rules.name
1875 , rules.egr_type
1876 , rules.hours
1877 , rules.effective_start_date
1878 , rules.days
1879 , rules.effective_end_date
1880 , -1
1881 , sysdate
1882 , -1
1883 , sysdate
1884 , -1
1885 );
1886 --
1887 end loop;
1888 --
1889 end if;
1890 --
1891 l_name := earnpols.name;
1892 --
1893 end loop;
1894 --
1895 if p_refs = 'Y' then
1896 update_ep_refs(busg.id, p_ep_id, l_ep_id);
1897 end if;
1898 --
1899 end loop;
1900 --
1901 hxt_bg_message_insert('U','Duplicating Earning Policy '||l_name||' Across All Business Groups');
1902 --
1903 delete from hxt_earning_rules
1904 where egp_id = p_ep_id;
1905 --
1906 delete from hxt_earning_policies
1907 where id = p_ep_id;
1908 --
1909 End duplicate_earn_policies;
1910 --
1911 -- Begin Main Processing.
1912 --
1913 BEGIN
1914 --
1915 For epols in c_get_ass_epols loop
1916 --
1917 g_ass_bg := -1;
1918 --
1919 if epols.count_bg = 1 then
1920 open c_get_ass_bg(epols.ep_id);
1921 fetch c_get_ass_bg into g_ass_bg;
1922 close c_get_ass_bg;
1923 update_earn_policies(epols.ep_id, g_ass_bg);
1924 else
1925 duplicate_earn_policies(epols.ep_id,'Y');
1926 end if;
1927 --
1928 end loop;
1929 --
1930 For earpols in c_get_nonass_epols loop
1931 --
1932 g_nonass_bg := -1;
1933 --
1934 open c_get_hcl_bg(earpols.hcl_id);
1935 fetch c_get_hcl_bg into g_nonass_bg;
1936 if c_get_hcl_bg%FOUND then
1937 update_earn_policies(earpols.ep_id, g_nonass_bg);
1938 close c_get_hcl_bg;
1939 else
1940 close c_get_hcl_bg;
1941 open c_get_egt_bg(earpols.egt_id);
1942 fetch c_get_egt_bg into g_nonass_bg;
1943 if c_get_egt_bg%FOUND then
1944 update_earn_policies(earpols.ep_id, g_nonass_bg);
1945 close c_get_egt_bg;
1946 else
1947 close c_get_egt_bg;
1948 open c_get_pep_bg(earpols.pep_id);
1949 fetch c_get_pep_bg into g_nonass_bg;
1950 if c_get_pep_bg%FOUND then
1951 update_earn_policies(earpols.ep_id, g_nonass_bg);
1952 close c_get_pep_bg;
1953 else
1954 close c_get_pep_bg;
1955 open c_get_pip_bg(earpols.pip_id);
1956 fetch c_get_pip_bg into g_nonass_bg;
1957 if c_get_pip_bg%FOUND then
1958 update_earn_policies(earpols.ep_id, g_nonass_bg);
1959 close c_get_pip_bg;
1960 else
1961 close c_get_pip_bg;
1962 open c_get_epr_bg(earpols.ep_id);
1963 fetch c_get_epr_bg into g_nonass_bg;
1964 if c_get_epr_bg%FOUND then
1965 update_earn_policies(earpols.ep_id, g_nonass_bg);
1966 close c_get_epr_bg;
1967 else
1968 close c_get_epr_bg;
1969 duplicate_earn_policies(earpols.ep_id,'N');
1970 end if;
1971 end if;
1972 end if;
1973 end if;
1974 end if;
1975 --
1976 end loop;
1977 --
1978 --
1979 End;
1980 -------------------------------------------------------------------------------
1981 end HXT_CHK_BG_AND_UPGRADE_PKG;