[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE hxt_bg_message_insert(
P_PHASE IN VARCHAR2,
P_TEXT IN VARCHAR2 ) IS
--
PRAGMA AUTONOMOUS_TRANSACTION;
sql_insert VARCHAR2(300);
sql_insert := 'INSERT INTO HXT_UPGRADE_BG_MESSAGES VALUES (:1, :2)';
EXECUTE IMMEDIATE sql_insert USING p_phase, p_text;
SELECT DISTINCT
wws.id
, wws.name
FROM hxt_weekly_work_schedules wws;
SELECT DISTINCT
rtp.id
, rtp.name
from hxt_rotation_plans rtp;
SELECT DISTINCT
egt.id
, egt.name
FROM hxt_earn_group_types egt;
SELECT DISTINCT
pep.id
, pep.name
FROM hxt_prem_eligblty_policies pep;
SELECT DISTINCT
pip.id
, pip.name
FROM hxt_prem_interact_policies pip;
SELECT DISTINCT
hcl.id
, hcl.name
FROM hxt_holiday_calendars hcl;
SELECT DISTINCT
hep.id
, hep.name
, hep.hcl_id
, hep.pip_id
, hep.pep_id
, hep.egt_id
, nvl(hep.business_group_id,-1) bg_id
FROM hxt_earning_policies hep;
SELECT DISTINCT
sdp.id
, sdp.name
FROM hxt_shift_diff_policies sdp;
SELECT DISTINCT
aai.id id
, aai.assignment_id ass_id
, aai.rotation_plan rp_id
, aai.earning_policy ep_id
, epg.hcl_id ep_hcl
, epg.pip_id ep_pip
, epg.pep_id ep_pep
, epg.egt_id ep_egt
, nvl(epg.business_group_id,-1) ep_bg
, aai.shift_differential_policy sdp_id
, ass.business_group_id ass_bg
, nvl(hdp.business_group_id,-1) hdp_bg
FROM hxt_add_assign_info_f aai
, hxt_rotation_plans rpl
, hxt_earning_policies epg
, hxt_shift_diff_policies sdp
, hxt_hour_deduct_policies hdp
, per_assignments_f ass
WHERE aai.assignment_id = ass.assignment_id
AND aai.rotation_plan = rpl.id (+)
AND aai.earning_policy = epg.id
AND aai.shift_differential_policy = sdp.id (+)
AND aai.hour_deduction_policy = hdp.id (+);
SELECT DISTINCT
tim.id id
, ptp.period_name period
, ppf.business_group_id per_bg
, nvl(pbh.business_group_id,-1) batch_bg
, ppr.business_group_id pay_bg
FROM hxt_timecards tim
, per_people_f ppf
, pay_payrolls ppr
, pay_batch_headers pbh
, per_time_periods ptp
WHERE tim.for_person_id = ppf.person_id
AND tim.batch_id = pbh.batch_id (+)
AND tim.payroll_id = ppr.payroll_id
AND tim.time_period_id = ptp.time_period_id;
SELECT DISTINCT(pet.business_group_id) bg
FROM hxt_work_shifts wsh
, pay_element_types_f pet
WHERE wsh.tws_id = p_wp_id
AND wsh.shift_diff_ovrrd_id = pet.element_type_id
AND pet.business_group_id is not null
UNION
SELECT DISTINCT(pet.business_group_id) bg
FROM hxt_work_shifts ws
, pay_element_types_f pet
WHERE ws.tws_id = p_wp_id
AND ws.off_shift_prem_id = pet.element_type_id
AND pet.business_group_id is not null
UNION
SELECT business_group_id
FROM hxt_weekly_work_schedules
WHERE id = p_wp_id
AND business_group_id is not null;
hxt_bg_message_insert('V','Workplan '||p_wp_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_rotation_schedules rts
, hxt_weekly_work_schedules wws
, hxt_work_shifts wsh
, pay_element_types_f pet
where wws.id = rts.tws_id
and rts.rtp_id = p_rp_id
and wsh.tws_id = wws.id
and wsh.off_shift_prem_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct(pet.business_group_id) bg
from hxt_rotation_schedules rts
, hxt_weekly_work_schedules wws
, hxt_work_shifts wsh
, pay_element_types_f pet
where wws.id = rts.tws_id
and rts.rtp_id = p_rp_id
and wsh.tws_id = wws.id
and wsh.shift_diff_ovrrd_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct
wws.business_group_id
from hxt_weekly_work_schedules wws
, hxt_rotation_schedules rts
where wws.id = rts.tws_id
and rts.rtp_id = p_rp_id
and wws.business_group_id is not null;
hxt_bg_message_insert('V','Rotation Plan '||p_rp_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_earn_groups egr
, pay_element_types_f pet
where egr.egt_id = p_eg_id
and egr.element_type_id = pet.element_type_id
and pet.business_group_id is not null;
hxt_bg_message_insert('V','Earning Group '||p_eg_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_prem_eligblty_pol_rules epr
, pay_element_types_f pet
where epr.pep_id = p_pep_id
and epr.elt_base_id = pet.element_type_id
union
select distinct(pet.business_group_id) bg
from hxt_prem_eligblty_rules elr
, pay_element_types_f pet
where elr.pep_id = p_pep_id
and elr.elt_base_id = pet.element_type_id
union
select distinct(pet.business_group_id) bg
from hxt_prem_eligblty_rules elr
, pay_element_types_f pet
where elr.pep_id = p_pep_id
and elr.elt_premium_id = pet.element_type_id;
hxt_bg_message_insert('V','Premium Eligibility Policy '||p_pep_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_prem_interact_rules itr
, pay_element_types_f pet
where itr.pip_id = p_pip_id
and itr.elt_prior_prem_id = pet.element_type_id
union
select distinct(pet.business_group_id) bg
from hxt_prem_interact_rules itr
, pay_element_types_f pet
where itr.pip_id = p_pip_id
and itr.elt_earned_prem_id = pet.element_type_id
union
select distinct(pet.business_group_id) bg
from hxt_prem_interact_pol_rules ipr
, pay_element_types_f pet
where ipr.pip_id = p_pip_id
and ipr.elt_earned_prem_id = pet.element_type_id;
hxt_bg_message_insert('V','Premium Interaction Policy '||p_pip_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_holiday_calendars hcl
, pay_element_types_f pet
where hcl.id = p_hcl_id
and hcl.element_type_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct(hou.business_group_id) bg
from hxt_holiday_calendars hcl
, hr_organization_units hou
where hcl.id = p_hcl_id
and hcl.organization_id = hou.organization_id
and hou.business_group_id is not null;
hxt_bg_message_insert('V','Holiday Calendar '||p_hcl_name||' Is Invalid');
select distinct(pet.business_group_id) bg
from hxt_earning_rules her
, pay_element_types_f pet
where her.egp_id = p_epr_id
and her.element_type_id = pet.element_type_id
and pet.business_group_id is not null;
hxt_bg_message_insert('V','Earning Policy '||p_epr_name||' Has Invalid Rules');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: EPR');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: HCL');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: PIP');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: PEP');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' References Conflicting Business Groups. Ref: EGT');
hxt_bg_message_insert('V','Earnings Policy '||p_egp_name||' Has Invalid References');
select distinct(pet.business_group_id) bg
from hxt_shift_diff_rules sdr
, pay_element_types_f pet
where sdr.sdp_id = p_sdp_id
and sdr.element_type_id = pet.element_type_id
and pet.business_group_id is not null;
hxt_bg_message_insert('V','Shift Differential Policy '||p_sdp_name||' Is Invalid');
hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: HDP');
hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: RP');
hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: SDP');
hxt_bg_message_insert('V','Assisgnment ID '||p_ass_id||' References Conflicting Business Groups. Ref: EP');
hxt_bg_message_insert('V','Assignment ID '||p_ass_id||' Has Invalid References');
select distinct(pet.business_group_id) bg
from hxt_sum_hours_worked_f shw
, pay_element_types_f pet
where shw.tim_id = p_tim_id
and shw.element_type_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct(ass.business_group_id) bg
from hxt_sum_hours_worked_f shw
, per_assignments_f ass
where shw.tim_id = p_tim_id
and shw.assignment_id = ass.assignment_id;
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' Has Invalid Summary Hours References');
select distinct(pet.business_group_id) bg
from hxt_det_hours_worked_f dhw
, pay_element_types_f pet
where dhw.tim_id = p_tim_id
and dhw.element_type_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct(ass.business_group_id) bg
from hxt_det_hours_worked_f dhw
, per_assignments_f ass
where dhw.tim_id = p_tim_id
and dhw.assignment_id = ass.assignment_id;
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' Has Invalid Det Hours References');
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Pay Batch Is Invalid');
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Payroll Is Invalid');
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Summary Information References Invalid Business Group');
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Detail Information References Invalid Business Group');
hxt_bg_message_insert('V','Timecard ID '||p_tim_id||' For Period '||p_period||' Has Invalid References.');
PROCEDURE hxt_bg_workplans_update IS
--
g_rp_bg number := 0;
select distinct
wsh.tws_id id
, pet.business_group_id bg
from hxt_weekly_work_schedules wws
, hxt_work_shifts wsh
, pay_element_types_f pet
where wws.business_group_id is null
and wws.id = wsh.tws_id
and ((wsh.off_shift_prem_id = pet.element_type_id
and pet.business_group_id is not null)
or (wsh.shift_diff_ovrrd_id = pet.element_type_id
and pet.business_group_id is not null));
select distinct
wws.id wp_id
, rp_sub.rp_count rp_cnt
, ass_sub.ass_count ass_cnt
from hxt_weekly_work_schedules wws
, (select tws_id wp_id
, count(rtp_id) rp_count
from hxt_rotation_schedules
group by tws_id) rp_sub
, (select rts.tws_id wp_id
, count(distinct(ass.business_group_id)) ass_count
from per_assignments_f ass
, hxt_add_assign_info_f aai
, hxt_rotation_plans rtp
, hxt_rotation_schedules rts
where ass.assignment_id = aai.assignment_id
and aai.rotation_plan = rtp.id
and rtp.id = rts.rtp_id
group by rts.tws_id) ass_sub
where wws.business_group_id is null
and wws.id = ass_sub.wp_id (+)
and wws.id = rp_sub.wp_id (+)
and not exists(select 'X'
from hxt_work_shifts wsh
, pay_element_types_f pet
where wws.id = wsh.tws_id
and ((wsh.off_shift_prem_id = pet.element_type_id
and pet.business_group_id is not null)
or (wsh.shift_diff_ovrrd_id = pet.element_type_id
and pet.business_group_id is not null)));
select distinct(pet.business_group_id) rp_bg
from hxt_rotation_schedules rts
, hxt_weekly_work_schedules wws
, hxt_work_shifts wsh
, pay_element_types_f pet
where rts.tws_id = wws.id
and wws.id = wsh.tws_id
and ((wsh.off_shift_prem_id = pet.element_type_id
and pet.business_group_id is not null)
or (wsh.shift_diff_ovrrd_id = pet.element_type_id
and pet.business_group_id is not null))
and rts.rtp_id IN (select sub.rtp_id
from hxt_rotation_schedules sub
where sub.tws_id = p_wp_id)
union
select distinct(wws.business_group_id) rp_bg
from hxt_rotation_schedules rts
, hxt_weekly_work_schedules wws
where rts.tws_id = wws.id
and wws.business_group_id is not null
and rts.rtp_id IN (select sub.rtp_id
from hxt_rotation_schedules sub
where sub.tws_id = p_wp_id);
select distinct(ass.business_group_id) ass_bg
from per_assignments_f ass
, hxt_add_assign_info_f aai
, hxt_rotation_plans rtp
, hxt_rotation_schedules rts
where ass.assignment_id = aai.assignment_id
and aai.rotation_plan = rtp.id
and rtp.id = rts.rtp_id
and rts.tws_id = p_wp_id;
Procedure update_workplans
(p_wp_id IN number
,p_bg_id IN number
) is
--
l_wp_id number;
UPDATE hxt_weekly_work_schedules
SET business_group_id = l_bg_id
WHERE id = l_wp_id;
hxt_bg_message_insert('U','Updating Workplan ID '||l_wp_id||' With Business Group '||l_bg_id);
End update_workplans;
Procedure update_rp_refs
(p_bg IN number
,p_old_id IN number
,p_new_id IN number
) is
--
l_new_id number;
select aai.id id
, aai.assignment_id ass_id
, aai.effective_start_date esd
, aai.effective_end_date eed
from hxt_add_assign_info_f aai
, per_assignments_f ass
where ass.business_group_id = p_bg
and ass.assignment_id = aai.assignment_id
and aai.rotation_plan = p_old_id;
update hxt_add_assign_info_f
set rotation_plan = l_new_id
where id = l_aai_id
and effective_start_date = l_aai_esd
and effective_end_date = l_aai_eed;
hxt_bg_message_insert('U','Updating Assignment ID '||l_ass_id||' To Reference Rotation Plan ID '||l_new_id);
End update_rp_refs;
select distinct
pbg.business_group_id id
, to_char(pbg.business_group_id) name
from per_business_groups pbg;
select hxt_seqno.nextval
from dual;
select name
, start_day
, date_from
, description
, date_to
from hxt_weekly_work_schedules
where id = p_wp_id;
select sht_id
, week_day
, seq_no
, early_start
, late_stop
, off_shift_prem_id
, shift_diff_ovrrd_id
from hxt_work_shifts
where tws_id = p_wp_id;
hxt_bg_message_insert('U','Duplicating Workplan ID '||p_wp_id||' Across All Business Groups');
Insert Into hxt_weekly_work_schedules
( id
, name
, start_day
, date_from
, description
, date_to
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, business_group_id
)
Values
( l_id
, r_workplan_rec.name||'-'||busg.name
, r_workplan_rec.start_day
, r_workplan_rec.date_from
, r_workplan_rec.description
, r_workplan_rec.date_to
, -1
, sysdate
, -1
, sysdate
, -1
, busg.id
);
Insert into hxt_work_shifts
( sht_id
, tws_id
, week_day
, seq_no
, early_start
, late_stop
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, off_shift_prem_id
, shift_diff_ovrrd_id
)
Values
( shifts.sht_id
, l_id
, shifts.week_day
, shifts.seq_no
, shifts.early_start
, shifts.late_stop
, -1
, sysdate
, -1
, sysdate
, -1
, shifts.off_shift_prem_id
, shifts.shift_diff_ovrrd_id
);
delete from hxt_work_shifts
where tws_id = p_wp_id;
delete from hxt_weekly_work_schedules
where id = p_wp_id;
TYPE t_delete_recs is table of NUMBER INDEX BY BINARY_INTEGER;
l_delete_wp t_delete_recs;
l_delete_rp t_delete_recs;
select distinct
pbg.business_group_id id
, to_char(pbg.business_group_id) name
from per_business_groups pbg;
select hxt_seqno.nextval
from dual;
select distinct
wws.id
, wws.name
, wws.start_day
, wws.date_from
, wws.description
, wws.date_to
from hxt_weekly_work_schedules wws
, hxt_rotation_schedules rts
where wws.id = rts.tws_id
and rts.rtp_id = p_rp_id;
select distinct
sht_id
, week_day
, seq_no
, early_start
, late_stop
, off_shift_prem_id
, shift_diff_ovrrd_id
from hxt_work_shifts
where tws_id = p_wp_id;
select distinct
rtp.id
, rtp.name
, rtp.date_from
, rtp.description
, rtp.date_to
from hxt_rotation_plans rtp
, hxt_rotation_schedules rts
where rtp.id = rts.rtp_id
and rts.tws_id = p_wp_id;
select start_date
from hxt_rotation_schedules
where tws_id = p_wp_id
and rtp_id = p_rp_id;
select id
from hxt_weekly_work_schedules
where business_group_id = p_bg_id
and name = p_name;
hxt_bg_message_insert('U','Duplicating Rotation Plans For Workplan ID '||p_wp_id||' Across All Business Groups');
Insert into hxt_rotation_plans
( id
, name
, date_from
, description
, date_to
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
Values
( l_rp_id
, rotplans.name||'-'||busg.name
, rotplans.date_from
, rotplans.description
, rotplans.date_to
, -1
, sysdate
, -1
, sysdate
, -1
);
Insert Into hxt_weekly_work_schedules
( id
, name
, start_day
, date_from
, description
, date_to
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, business_group_id
)
Values
( l_wp_id
, r_workplan_rec.name||'-'||busg.name
, r_workplan_rec.start_day
, r_workplan_rec.date_from
, r_workplan_rec.description
, r_workplan_rec.date_to
, -1
, sysdate
, -1
, sysdate
, -1
, busg.id
);
Insert into hxt_work_shifts
( sht_id
, tws_id
, week_day
, seq_no
, early_start
, late_stop
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, off_shift_prem_id
, shift_diff_ovrrd_id
)
Values
( shifts.sht_id
, l_wp_id
, shifts.week_day
, shifts.seq_no
, shifts.early_start
, shifts.late_stop
, -1
, sysdate
, -1
, sysdate
, -1
, shifts.off_shift_prem_id
, shifts.shift_diff_ovrrd_id
);
Insert into hxt_rotation_schedules
( rtp_id
, tws_id
, start_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
Values
( l_rp_id
, l_wp_id
, rotsched.start_date
, -1
, sysdate
, -1
, sysdate
, -1
);
update_rp_refs(busg.id, rotplans.id, l_rp_id);
For delete_rp in c_rotplan_rec loop
l_rp_counter := l_rp_counter+1;
l_delete_rp(l_rp_counter) := delete_rp.id;
delete from hxt_rotation_schedules
where rtp_id = l_delete_rp(i);
delete from hxt_rotation_plans
where id = l_delete_rp(i);
delete from hxt_work_shifts
where tws_id = p_wp_id;
delete from hxt_weekly_work_schedules
where id = p_wp_id;
update_workplans(wpbg.id, wpbg.bg);
update_workplans(global.wp_id, g_rp_bg);
update_workplans(global.wp_id, g_ass_bg);
update_workplans(global.wp_id, g_rp_bg);
update_workplans(global.wp_id, g_ass_bg);
update_workplans(global.wp_id, g_ass_bg);
PROCEDURE hxt_bg_earnings_update IS
--
g_ass_bg number;
select distinct
hep.id ep_id
, count(distinct(ass.business_group_id)) count_bg
from hxt_earning_policies hep
, hxt_add_assign_info_f aai
, per_assignments_f ass
where hep.id = aai.earning_policy
and aai.assignment_id = ass.assignment_id
and hep.business_group_id is null
group by hep.id;
select hep.id ep_id
, hep.hcl_id hcl_id
, hep.pip_id pip_id
, hep.pep_id pep_id
, hep.egt_id egt_id
from hxt_earning_policies hep
where hep.business_group_id is null
and not exists(select 'X'
from hxt_add_assign_info_f aai
where aai.earning_policy = hep.id);
select distinct
ass.business_group_id bg_id
from hxt_add_assign_info_f aai
, per_assignments_f ass
where aai.assignment_id = ass.assignment_id
and aai.earning_policy = p_id;
select distinct
pet.business_group_id bg_id
from hxt_earn_groups egr
, hxt_earn_group_types egt
, pay_element_types_f pet
where egt.id = p_id
and egt.id = egr.egt_id
and egr.element_type_id = pet.element_type_id
and pet.business_group_id is not null;
select distinct
pet.business_group_id bg_id
from hxt_prem_eligblty_policies pep
, hxt_prem_eligblty_pol_rules epr
, pay_element_types_f pet
where pep.id = p_id
and pep.id = epr.pep_id
and epr.elt_base_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct
pet.business_group_id bg_id
from hxt_prem_eligblty_policies pep
, hxt_prem_eligblty_rules elr
, pay_element_types_f pet
where pep.id = p_id
and pep.id = elr.pep_id
and ((elr.elt_base_id = pet.element_type_id
and pet.business_group_id is not null)
or (elr.elt_premium_id = pet.element_type_id
and pet.business_group_id is not null));
select distinct
pet.business_group_id bg_id
from hxt_prem_interact_policies pip
, hxt_prem_interact_rules itr
, pay_element_types_f pet
where pip.id = p_id
and pip.id = itr.pip_id
and ((itr.elt_prior_prem_id = pet.element_type_id
and pet.business_group_id is not null)
or (itr.elt_earned_prem_id = pet.element_type_id
and pet.business_group_id is not null))
union
select distinct
pet.business_group_id bg_id
from hxt_prem_interact_policies pip
, hxt_prem_interact_pol_rules ipr
, pay_element_types_f pet
where pip.id = p_id
and pip.id = ipr.pip_id
and ipr.elt_earned_prem_id = pet.element_type_id
and pet.business_group_id is not null;
select distinct
pet.business_group_id bg_id
from hxt_holiday_calendars hcl
, pay_element_types_f pet
where hcl.id = p_id
and hcl.element_type_id = pet.element_type_id
and pet.business_group_id is not null
union
select distinct
hou.business_group_id bg_id
from hxt_holiday_calendars hcl
, hr_organization_units hou
where hcl.id = p_id
and hcl.organization_id = hou.organization_id
and hou.business_group_id is not null;
select distinct
pet.business_group_id bg_id
from hxt_earning_rules epr
, pay_element_types_f pet
where epr.egp_id = p_id
and epr.element_type_id = pet.element_type_id
and pet.business_group_id is not null;
Procedure update_earn_policies
(p_ep_id IN number
,p_bg_id IN number
) is
--
l_ep_id number;
UPDATE hxt_earning_policies
SET business_group_id = l_bg_id
WHERE id = l_ep_id;
hxt_bg_message_insert('U','Updating Earning Policy ID '||l_ep_id||' With Business Group ID '||l_bg_id);
End update_earn_policies;
Procedure update_ep_refs
(p_bg IN number
,p_old_id IN number
,p_new_id IN number
) is
--
l_new_id number;
select aai.id id
, aai.assignment_id ass_id
, aai.effective_start_date esd
, aai.effective_end_date eed
from hxt_add_assign_info_f aai
, per_assignments_f ass
where ass.business_group_id = p_bg
and ass.assignment_id = aai.assignment_id
and aai.earning_policy = p_old_id;
update hxt_add_assign_info_f
set earning_policy = l_new_id
where id = l_aai_id
and effective_start_date = l_aai_esd
and effective_end_date = l_aai_eed;
hxt_bg_message_insert('U','Updating Assignment ID '||l_ass_id||' To Reference Earning Policy ID '||l_new_id);
End update_ep_refs;
select distinct
pbg.business_group_id id
, to_char(pbg.business_group_id) name
from per_business_groups pbg;
select hxt_seqno.nextval
from dual;
select hcl_id
, fcl_earn_type
, name
, effective_start_date
, pip_id
, pep_id
, egt_id
, description
, effective_end_date
, organization_id
, round_up
, min_tcard_intvl
from hxt_earning_policies
where id = p_ep_id;
select element_type_id
, seq_no
, name
, egr_type
, hours
, effective_start_date
, days
, effective_end_date
from hxt_earning_rules
where egp_id = p_ep_id;
Insert into hxt_earning_policies
( id
, hcl_id
, fcl_earn_type
, name
, effective_start_date
, pip_id
, pep_id
, egt_id
, description
, effective_end_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, organization_id
, round_up
, min_tcard_intvl
, business_group_id
)
Values
( l_ep_id
, earnpols.hcl_id
, earnpols.fcl_earn_type
, earnpols.name||'-'||busg.name
, earnpols.effective_start_date
, earnpols.pip_id
, earnpols.pep_id
, earnpols.egt_id
, earnpols.description
, earnpols.effective_end_date
, -1
, sysdate
, -1
, sysdate
, -1
, earnpols.organization_id
, earnpols.round_up
, earnpols.min_tcard_intvl
, busg.id
);
Insert into hxt_earning_rules
( id
, element_type_id
, egp_id
, seq_no
, name
, egr_type
, hours
, effective_start_date
, days
, effective_end_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
Values
( l_er_id
, rules.element_type_id
, l_ep_id
, rules.seq_no
, rules.name
, rules.egr_type
, rules.hours
, rules.effective_start_date
, rules.days
, rules.effective_end_date
, -1
, sysdate
, -1
, sysdate
, -1
);
update_ep_refs(busg.id, p_ep_id, l_ep_id);
hxt_bg_message_insert('U','Duplicating Earning Policy '||l_name||' Across All Business Groups');
delete from hxt_earning_rules
where egp_id = p_ep_id;
delete from hxt_earning_policies
where id = p_ep_id;
update_earn_policies(epols.ep_id, g_ass_bg);
update_earn_policies(earpols.ep_id, g_nonass_bg);
update_earn_policies(earpols.ep_id, g_nonass_bg);
update_earn_policies(earpols.ep_id, g_nonass_bg);
update_earn_policies(earpols.ep_id, g_nonass_bg);
update_earn_policies(earpols.ep_id, g_nonass_bg);