[Home] [Help]
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
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
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
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
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: --
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
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
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;
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
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
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;
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
450: --
451: CURSOR c_eprules_bg_check is
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: --
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: --
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
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
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)
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
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)
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;
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
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)
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)
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;
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
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: --