[Home] [Help]
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
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
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: --
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
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
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
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
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
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
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
1023: and rts.tws_id = p_wp_id;
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
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
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
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
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