[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER;
g_last_update_date DATE;
g_last_update_login NUMBER;
SELECT 'x'
FROM hxt_earn_groups erg, hxt_earning_policies egp
WHERE egp.ID = g_ep_id
AND erg.egt_id = egp.egt_id
AND erg.element_type_id = g_element_type_id;
SELECT 'x'
FROM hxt_earn_group_types egt
WHERE egt.NAME = 'OTLR 7th Day Hours';
SELECT hrw.ROWID prem_row_id, hrw.seqno, hrw.time_in prem_time_in,
hrw.time_out prem_time_out, hrw.hours prem_hours,
elt.element_name
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked hrw
WHERE hrw.tim_id = a_tim_id
AND hrw.parent_id = a_parent_id
AND hrw.seqno > a_seqno
-- The sub select below tries to keep this cursor bound to premiums
-- that fall between the REG and OVT pay elements that may already
-- exist on the detail table so that we don't get the wrong premium
-- when adjusting the hours on premiums rows
AND ( -- Be sure we stay with premiums for the detail record being
-- dealt with
(hrw.seqno <
(SELECT MIN (det.seqno)
FROM hxt_pay_element_types_f_ddf_v eltv2,
pay_element_types_f elt2,
hxt_det_hours_worked det
WHERE det.tim_id = a_tim_id
AND det.parent_id = a_parent_id
AND det.seqno > a_seqno
AND det.element_type_id = elt2.element_type_id
AND eltv2.hxt_earning_category IN ('REG', 'OVT')
AND det.date_worked BETWEEN elt2.effective_start_date
AND elt2.effective_end_date
AND eltv2.element_type_id = elt2.element_type_id
AND det.date_worked BETWEEN eltv2.effective_start_date
AND eltv2.effective_end_date)
)
OR -- Proceed if no REG and OVT elements exist for other
-- detail records
(NOT EXISTS (
SELECT det.seqno
FROM hxt_pay_element_types_f_ddf_v eltv2,
pay_element_types_f elt2,
hxt_det_hours_worked det
WHERE det.tim_id = a_tim_id
AND det.parent_id = a_parent_id
AND det.seqno > a_seqno
AND det.element_type_id = elt2.element_type_id
AND eltv2.hxt_earning_category IN ('REG', 'OVT')
AND det.date_worked BETWEEN elt2.effective_start_date
AND elt2.effective_end_date
AND eltv2.element_type_id = elt2.element_type_id
AND det.date_worked BETWEEN eltv2.effective_start_date
AND eltv2.effective_end_date)
)
)
AND elt.element_type_id = hrw.element_type_id
AND eltv.hxt_earning_category NOT IN ('REG', 'OVT')
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND NVL (hrw.hours, 0) <> 0
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
FUNCTION delete_zero_hour_details (
a_tim_id NUMBER,
a_ep_id NUMBER,
a_osp_id NUMBER,
a_date_worked DATE
)
RETURN NUMBER;
SELECT COUNT (1)
INTO l_count_holiday_rules
FROM hxt_earning_rules
WHERE egp_id = p_ep_id AND egr_type = c_holiday_rule
AND p_date_worked between effective_start_date and effective_end_date;
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
p_start_day_of_week IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_project_id IN NUMBER, -- PROJACCT
p_job_id IN NUMBER, -- TA35
p_pay_status IN VARCHAR2, -- RETROPAY
p_pa_status IN VARCHAR2, -- PROJACCT
p_retro_batch_id IN NUMBER, -- RETROPAY
p_period_start_date IN DATE,
p_call_adjust_abs IN VARCHAR2,
p_state_name IN VARCHAR2 DEFAULT NULL,
p_county_name IN VARCHAR2 DEFAULT NULL,
p_city_name IN VARCHAR2 DEFAULT NULL,
p_zip_code IN VARCHAR2 DEFAULT NULL
)
-- p_GROUP_ID IN NUMBER) -- HXT11i1
RETURN NUMBER
IS
BEGIN
g_debug := hr_utility.debug_enabled;
g_last_updated_by := p_last_updated_by;
g_last_update_date := p_last_update_date;
g_last_update_login := p_last_update_login;
SELECT er.hours, er.element_type_id, er.days
FROM hxt_earning_rules er
WHERE er.egr_type = 'SPC'
AND er.days <= i_days
AND er.days IS NOT NULL
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.days DESC, er.hours ASC;
SELECT er.hours, er.element_type_id, er.days
FROM hxt_earning_rules er
WHERE er.egr_type = i_egr_type
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT daily_hours
FROM hxt_daily_hours_worked_v
WHERE work_date || '' = cursor_day_worked AND tim_id = cursor_tim_id;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_earning_policies egp,
hxt_per_aei_ddf_v asmv,
per_assignments_f asm,
hxt_det_hours_worked hrw,
hxt_timecards tim
WHERE hrw.parent_id > 0
AND hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
start_day_of_week
)
AND (g_date_worked - 1)
AND asm.assignment_id = hrw.assignment_id
AND eltv.hxt_earning_category LIKE NVL (cp_earn_category, '%')
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND asm.assignment_id = asmv.assignment_id
AND hrw.date_worked BETWEEN asmv.effective_start_date
AND asmv.effective_end_date
-- Commented out for OTLR Recurrring Period Preference support.
-- AND tim_id = g_tim_id
-- Added the following for OTLR Recurrring Period Preference support.
AND tim.for_person_id = g_person_id
AND tim.ID = hrw.tim_id
AND elt.element_type_id = hrw.element_type_id
AND ( -- If absence, only include earnings to be counted toward
-- hours to be worked before being eligible for overtime.
--USEEARNGROUP -- include ANY earnings, not just absences. RTF
( EXISTS (
SELECT 1
FROM hxt_earn_groups erg
WHERE erg.egt_id = egp.egt_id
AND erg.element_type_id = elt.element_type_id)
AND egp.egt_id IS NOT NULL
)
)
AND hrw.date_worked BETWEEN egp.effective_start_date
AND egp.effective_end_date
-- next line changed to use override earning policy.
-- AND egp.id = asmv.hxt_earning_policy
AND egp.ID = g_ep_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_earn_groups erg,
hxt_earning_policies egp,
hxt_per_aei_ddf_v asmv,
per_assignments_f asm,
hxt_det_hours_worked hrw,
hxt_timecards tim
WHERE hrw.parent_id > 0
AND hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
start_day_of_week
)
AND (g_date_worked)
AND asm.assignment_id = hrw.assignment_id
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND asm.assignment_id = asmv.assignment_id
AND hrw.date_worked BETWEEN asmv.effective_start_date
AND asmv.effective_end_date
AND tim_id = g_tim_id
AND tim.ID = hrw.tim_id
AND elt.element_type_id = hrw.element_type_id
AND egp.egt_id IS NOT NULL
AND hrw.date_worked BETWEEN egp.effective_start_date
AND egp.effective_end_date
-- next line changed to use override earning policy.
-- AND egp.id = asmv.hxt_earning_policy
AND egp.ID = g_ep_id
AND hrw.element_type_id = erg.element_type_id
AND erg.egt_id = egp.egt_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_earning_policies egp,
hxt_per_aei_ddf_v asmv,
per_assignments_f asm,
hxt_det_hours_worked hrw,
hxt_timecards tim
WHERE hrw.parent_id > 0
AND hrw.date_worked = g_date_worked
AND asm.assignment_id = hrw.assignment_id
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND asm.assignment_id = asmv.assignment_id
AND hrw.date_worked BETWEEN asmv.effective_start_date
AND asmv.effective_end_date
AND tim_id = g_tim_id
AND tim.ID = hrw.tim_id
AND elt.element_type_id = hrw.element_type_id
AND ( -- If absence, only include earnings to be counted toward
-- hours to be worked before being eligible for overtime.
( EXISTS (
SELECT 1
FROM hxt_earn_groups erg
WHERE erg.egt_id = egp.egt_id
AND erg.element_type_id =
elt.element_type_id)
AND eltv.hxt_earning_category = 'ABS'
AND egp.egt_id IS NOT NULL
)
OR eltv.hxt_earning_category = 'REG'
OR eltv.hxt_earning_category = 'OVT'
-- OR
-- to_char(hrw.element_type_id) LIKE
-- NVL(to_char(special_and_weekly_base), '%')
)
AND hrw.date_worked BETWEEN egp.effective_start_date
AND egp.effective_end_date
-- next line changed to use override earning policy.
-- AND egp.id = asmv.hxt_earning_policy
AND egp.ID = g_ep_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT hrw.ROWID hrw_rowid, hrw.hours
-- FROM pay_element_types_f_dfv eltv,
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked hrw, --C421
hxt_timecards tim
WHERE hrw.date_worked = g_date_worked
AND tim_id = g_tim_id
AND tim.ID = hrw.tim_id
AND elt.element_type_id = hrw.element_type_id
AND eltv.hxt_earning_category = 'OVT'
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'DAY'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
UPDATE hxt_det_hours_worked_f
SET hours = l_hours_to_adjust - l_delta
WHERE ROWID = l_rowid;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_earning_policies egp,
hxt_per_aei_ddf_v asmv,
per_assignments_f asm,
hxt_det_hours_worked hrw,
hxt_timecards tim
WHERE hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
start_day_of_week
)
AND g_date_worked
AND asm.assignment_id = hrw.assignment_id
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND asm.assignment_id = asmv.assignment_id
AND hrw.date_worked BETWEEN asmv.effective_start_date
AND asmv.effective_end_date
-- Commented out for OTLR Recurrring Period Preference support.
-- and tim_id = g_tim_id
-- Added the following for OTLR Recurrring Period Preference support.
AND tim.for_person_id = g_person_id
AND tim.ID = hrw.tim_id
AND elt.element_type_id = hrw.element_type_id
AND (
-- If absence, only include earnings to be counted toward
-- hours to be worked before being eligible for overtime.
--USEEARNGROUP -- include ANY earnings, not just absences. RTF
( EXISTS (
SELECT 1
FROM hxt_earn_groups erg
WHERE erg.egt_id = egp.egt_id
AND erg.element_type_id = elt.element_type_id)
AND egp.egt_id IS NOT NULL
)
)
AND hrw.date_worked BETWEEN egp.effective_start_date
AND egp.effective_end_date
AND egp.ID = g_ep_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT sdr.element_type_id
FROM hxt_shift_diff_rules sdr
WHERE sdr.sdp_id = g_sdp_id
AND g_date_worked BETWEEN sdr.effective_start_date
AND sdr.effective_end_date
AND ( ( sdr.start_time <=
TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI'
)
)
AND TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) < sdr.stop_time
)
OR ( (TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI'
)
) <= sdr.start_time
)
AND TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) < sdr.stop_time
AND sdr.start_time > sdr.stop_time
)
OR ( sdr.start_time <=
TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI'
)
)
AND sdr.start_time > sdr.stop_time
)
);
SELECT sdr.element_type_id
FROM hxt_shift_diff_rules sdr
WHERE sdr.sdp_id = g_sdp_id
AND g_date_worked BETWEEN sdr.effective_start_date
AND sdr.effective_end_date
AND ( ( TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) < sdr.start_time
AND sdr.start_time <
TO_NUMBER (TO_CHAR (p_segment_stop_time,
'HH24MI'
)
)
)
OR ( TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) > sdr.start_time
AND sdr.start_time <
TO_NUMBER (TO_CHAR (p_segment_stop_time,
'HH24MI'
)
)
AND TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) >
TO_NUMBER (TO_CHAR (p_segment_stop_time,
'HH24MI'
)
)
)
OR ( TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) < sdr.start_time
AND TO_NUMBER (TO_CHAR (p_segment_start_time,
'HH24MI')
) >
TO_NUMBER (TO_CHAR (p_segment_stop_time,
'HH24MI'
)
)
)
);
SELECT dhw.ROWID dhw_rowid, dhw.parent_id, dhw.tim_id,
dhw.hours, dhw.time_in, dhw.time_out, dhw.seqno
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw
WHERE dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND dhw.hours > c_hours_short
AND elt.element_type_id = dhw.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG', 'OVT')
ORDER BY dhw.date_worked DESC, dhw.time_in DESC, dhw.seqno DESC;
UPDATE hxt_det_hours_worked
SET hours = l_hours_to_adjust,
time_out = time_out - (p_hours_short / 24)
WHERE ROWID = l_prem.prem_row_id;
UPDATE hxt_det_hours_worked
SET hours = l_hours_to_adjust,
time_out = time_out - (p_hours_short / 24)
WHERE ROWID = current_dtl_row.dhw_rowid;
SELECT det.ROWID det_rowid, det.hours, det.date_worked,
det.parent_id, det.assignment_id,
det.fcl_earn_reason_code, det.ffv_cost_center_id,
det.tas_id, det.location_id, det.sht_id,
det.hrw_comment, det.ffv_rate_code_id,
det.rate_multiple, det.hourly_rate, det.amount,
det.fcl_tax_rule_code, det.separate_check_flag,
det.seqno, det.time_in, det.time_out, det.project_id,
det.element_type_id, det.effective_end_date,
-- det.group_id,
det.earn_pol_id, det.state_name, det.county_name,
det.city_name, det.zip_code
FROM hxt_det_hours_worked det
WHERE det.element_type_id = c_ovt_element --SIR 397
AND det.date_worked <= c_date_worked
AND det.tim_id = c_tim_id
AND det.hours <> 0
ORDER BY det.date_worked DESC, det.time_out DESC, det.ID DESC;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = c_earn_policy
AND c_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked hrw
WHERE hrw.date_worked <= c_date_worked
AND hrw.tim_id = c_tim_id
AND hrw.element_type_id = c_second_elem;
SELECT NVL (hxt_premium_amount, 0)
FROM hxt_pay_element_types_f_ddf_v eltv
WHERE c_date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.element_type_id = c_elem;
UPDATE hxt_det_hours_worked_f
SET hours = (hours - l_hours_left),
time_out =
time_in
+ ((l_ovt_hrs_cur.hours - l_hours_left) / 24)
WHERE ROWID = l_ovt_hrs_cur.det_rowid;
UPDATE hxt_det_hours_worked_f
--SIR382 SET hours = (hours - l_hours_left),
--SIR382 time_out = time_in + (l_hours_left/24)
SET hours = 0,
time_out = time_in
WHERE ROWID = l_ovt_hrs_cur.det_rowid;
UPDATE hxt_det_hours_worked_f
SET hours = (hours - l_hours_left),
time_out =
time_in
+ ((l_ovt_hrs_cur.hours - l_hours_left) / 24
)
WHERE ROWID = l_prem.prem_row_id;
UPDATE hxt_det_hours_worked_f
SET hours = 0,
time_out = time_in
WHERE ROWID = l_prem.prem_row_id;
SELECT sdr.start_time, sdr.stop_time, sdr.carryover_time,
elt.element_name
FROM hxt_shift_diff_rules sdr, pay_element_types_f elt
WHERE sdr.sdp_id = g_sdp_id
AND sdr.element_type_id = elt.element_type_id
AND trunc(p_sum_time_in) BETWEEN elt.effective_start_date
AND elt.effective_end_date;
IF adjust_by = 'UPDATE'
THEN
FOR l_prem IN related_prem (dtl_parent_id,
dtl_tim_id,
dtl_seqno,
dtl_hours
)
LOOP
IF l_prem.element_name = l_sdp_rule.element_name
THEN
UPDATE hxt_det_hours_worked
SET hours = l_prem.prem_hours - deduct_premium,
time_out =
time_in
+ ((l_prem.prem_hours - deduct_premium) / 24
)
WHERE ROWID = l_prem.prem_row_id;
ELSIF adjust_by = 'DELETE'
THEN
FOR l_prem IN related_prem (dtl_parent_id,
dtl_tim_id,
dtl_seqno,
dtl_hours
)
LOOP
IF l_prem.element_name = l_sdp_rule.element_name
THEN
DELETE FROM hxt_det_hours_worked
WHERE ROWID = l_prem.prem_row_id;
SELECT dhw.ROWID dhw_rowid, dhw.parent_id, dhw.tim_id,
dhw.hours, dhw.time_in, dhw.time_out, dhw.seqno
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw
WHERE dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND elt.element_type_id = dhw.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG', 'OVT')
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y' /* Bug: 4489952 */
ORDER BY dhw.date_worked DESC,
dhw.time_in DESC,
dhw.seqno DESC,
dhw.parent_id DESC;
SELECT dhw.ROWID dhw_rowid, dhw.parent_id, dhw.tim_id,
dhw.hours, dhw.time_in, dhw.time_out, dhw.seqno
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw
WHERE dhw.tim_id = g_tim_id
AND dhw.parent_id = g_id
AND dhw.date_worked = g_date_worked
AND elt.element_type_id = dhw.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND elt.element_type_id = g_osp_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'OSP'
AND eltv.hxt_premium_type <> 'FIXED'
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y' /* Bug: 4489952 */
ORDER BY dhw.date_worked DESC,
dhw.time_in DESC,
dhw.seqno DESC,
dhw.parent_id DESC;
SELECT dhw.ROWID dhw_rowid, dhw.parent_id, dhw.tim_id,
dhw.hours, dhw.time_in, dhw.time_out, dhw.seqno
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw
WHERE dhw.parent_id = g_id
AND dhw.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'OTH'
AND eltv.hxt_premium_type <> 'FIXED'
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y' /* Bug: 4489952 */
ORDER BY dhw.date_worked DESC,
dhw.time_in DESC,
dhw.seqno DESC,
dhw.parent_id DESC;
SELECT NVL (SUM (dhw.hours), 0) det_hours
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw,
hxt_sum_hours_worked shw
WHERE shw.ID = g_id
AND shw.ID = dhw.parent_id
AND shw.element_type_id IS NULL
AND dhw.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'SDF'
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */
SELECT NVL (SUM (dhw.hours), 0) det_hours
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw,
hxt_sum_hours_worked shw
WHERE shw.ID = g_id
AND shw.ID = dhw.parent_id
AND shw.element_type_id IS NULL
AND dhw.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'OTH'
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */
SELECT hours, time_period
FROM hxt_hour_deduction_rules thdr
WHERE thdr.hdp_id = g_hdp_id
AND g_date_worked BETWEEN thdr.effective_start_date
AND thdr.effective_end_date;
SELECT NVL (SUM (dhw.hours), 0) det_hours
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw,
hxt_sum_hours_worked shw
WHERE shw.ID = g_id
AND shw.ID = dhw.parent_id
AND shw.element_type_id IS NULL
AND dhw.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG', 'OVT')
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */
SELECT NVL (SUM (dhw.hours), 0) det_hours
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw,
hxt_sum_hours_worked shw --<-- New Table
WHERE elt.element_type_id = dhw.element_type_id
AND shw.ID = dhw.parent_id --<-- New Join
AND shw.element_type_id IS NULL
--<-- New check: No Hours Override
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG', 'OVT')
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */
SELECT NVL (SUM (shw.hours), 0) sum_hours,
COUNT(*) sum_count /*** 4969936 ***/
FROM hxt_sum_hours_worked shw
WHERE tim_id = g_tim_id AND shw.date_worked = g_date_worked
AND not exists ( SELECT 'X' /* Bug: 4489952 */
FROM hxt_add_elem_info_f hei, hxt_det_hours_worked dhw
WHERE hei.element_type_id = dhw.element_type_id
AND nvl(hei.exclude_from_explosion, 'N') = 'Y'
AND dhw.parent_id = shw.id
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date);
SELECT hours, time_in, time_out
FROM hxt_sum_hours_worked shw
WHERE ID = g_id
AND not exists ( SELECT 'X' /* Bug: 4489952 */
FROM hxt_add_elem_info_f hei, hxt_det_hours_worked dhw
WHERE hei.element_type_id = dhw.element_type_id
AND nvl(hei.exclude_from_explosion, 'N') = 'Y'
AND dhw.parent_id = shw.id
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date);
SELECT shw.ID, shw.hours, shw.time_in, shw.time_out
FROM hxt_sum_hours_worked shw
WHERE shw.tim_id = p_tim_id
AND shw.date_worked = p_date_worked
AND (( shw.time_in IS NOT NULL
AND shw.time_out IS NOT NULL
AND shw.time_in <> shw.time_out
)
)
AND not exists ( SELECT 'X' /* Bug: 4489952 */
FROM hxt_add_elem_info_f hei, hxt_det_hours_worked dhw
WHERE hei.element_type_id = dhw.element_type_id
AND nvl(hei.exclude_from_explosion, 'N') = 'Y'
AND dhw.parent_id = shw.id
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date)
ORDER BY shw.date_worked,
shw.element_type_id,
shw.time_in,
shw.seqno,
shw.ID;
SELECT (NVL (sum_hours, 0) - NVL (det_hours, 0))
FROM hxt_hdp_sum_hours_worked_v shw,
hxt_hdp_det_hours_worked_v dhw
WHERE dhw.det_date = g_date_worked
AND dhw.det_tim_id = g_tim_id
AND shw.sum_date = dhw.det_date
AND shw.sum_tim_id = dhw.det_tim_id;
SELECT SUM(thdr.time_period) time_period,
COUNT(*) hdp_count
FROM hxt_hour_deduction_rules thdr
WHERE thdr.hdp_id = g_hdp_id
AND g_date_worked BETWEEN thdr.effective_start_date
AND thdr.effective_end_date;
'UPDATE',
l_prem_adjusted
);
'DELETE',
l_prem_adjusted
);
UPDATE hxt_det_hours_worked
SET hours = osp_dtl_row.hours - deduct_hours,
time_out =
time_in
+ ((osp_dtl_row.hours - deduct_hours) / 24
)
WHERE ROWID = osp_dtl_row.dhw_rowid;
UPDATE hxt_det_hours_worked
SET hours = oth_dtl_row.hours - deduct_hours,
time_out = time_in
+ ((oth_dtl_row.hours - deduct_hours) / 24)
WHERE ROWID = oth_dtl_row.dhw_rowid;
DELETE FROM hxt_det_hours_worked
WHERE ROWID = current_dtl_row.dhw_rowid;
'UPDATE',
l_prem_adjusted
);
'UPDATE',
l_prem_adjusted
);
UPDATE hxt_det_hours_worked
SET hours = osp_dtl_row.hours - deduct_hours,
time_out =
time_in
+ ((osp_dtl_row.hours - deduct_hours) / 24
)
WHERE ROWID = osp_dtl_row.dhw_rowid;
SELECT hours
INTO test_hours
FROM hxt_det_hours_worked
WHERE ROWID = osp_dtl_row.dhw_rowid;
UPDATE hxt_det_hours_worked
SET hours = oth_dtl_row.hours - deduct_hours,
time_out = time_in
+ ((oth_dtl_row.hours - deduct_hours) / 24)
WHERE ROWID = oth_dtl_row.dhw_rowid;
UPDATE hxt_det_hours_worked
SET hours = current_dtl_row.hours - deduct_hours,
time_out =
time_in
+ ((current_dtl_row.hours - deduct_hours) / 24)
WHERE ROWID = current_dtl_row.dhw_rowid;
SELECT hours, time_period
FROM hxt_hour_deduction_rules thdr
WHERE thdr.hdp_id = g_hdp_id
AND g_date_worked BETWEEN thdr.effective_start_date
AND thdr.effective_end_date;
SELECT NVL (SUM (dhw.hours), 0) det_hours
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked dhw,
hxt_sum_hours_worked shw --<-- New Table
WHERE elt.element_type_id = dhw.element_type_id
AND shw.ID = dhw.parent_id --<-- New Join
AND shw.element_type_id IS NULL
--<-- New check: No Hours Override
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG', 'OVT')
AND dhw.tim_id = g_tim_id
AND dhw.date_worked = g_date_worked;
SELECT (NVL (sum_hours, 0) - NVL (det_hours, 0))
FROM hxt_hdp_sum_hours_worked_v shw,
hxt_hdp_det_hours_worked_v dhw
WHERE dhw.det_date = g_date_worked
AND dhw.det_tim_id = g_tim_id
AND shw.sum_date = dhw.det_date
AND shw.sum_tim_id = dhw.det_tim_id;
SELECT time_in, time_out
FROM hxt_sum_hours_worked
WHERE ID = g_id;
SELECT (COUNT (DISTINCT work_date) + 1
) -- 1 added for current day which may not
-- be in db
INTO consecutive_days
FROM hxt_daily_hours_worked_v
WHERE work_date BETWEEN g_date_worked
- (p_consecutive_days_limit - 1
) -- start day
AND g_date_worked - 1 -- yesterday
AND work_date >= NEXT_DAY (g_date_worked - 7, start_day_of_week)
AND tim_id = g_tim_id;
SELECT sm.date_worked
FROM hxt_sum_hours_worked sm
--Added this join to support the OTLR Recurring Preiod Preference
, hxt_timecards tim
--WHERE sm.tim_id = c_tim_id
--Changed the above where clause as follows to support the
--OTLR Recurring Preiod Preference.
WHERE tim.for_person_id = g_person_id
AND sm.tim_id = tim.ID
AND sm.hours > 0
AND sm.date_worked = c_date_worked
AND sm.date_worked >=
NEXT_DAY (a_date_worked - 7, start_day_of_week);
SELECT sm.date_worked
FROM hxt_sum_hours_worked sm, hxt_timecards tim
WHERE tim.for_person_id = g_person_id
AND sm.tim_id = tim.ID
AND sm.hours > 0
AND sm.date_worked = c_date_worked
AND sm.date_worked >=
NEXT_DAY (a_date_worked - 7, start_day_of_week);
SELECT element_type_id
FROM hxt_earn_groups eg, hxt_earn_group_types egt
WHERE eg.egt_id = egt.ID AND egt.NAME = 'OTLR 7th Day Hours';
SELECT det.element_type_id
FROM hxt_det_hours_worked det, hxt_timecards tim
WHERE det.tim_id = tim.ID
AND tim.for_person_id = g_person_id
AND det.hours > 0
AND det.date_worked = a_date_start;
SELECT hrw.ROWID hrw_rowid, hrw.hours, hrw.date_worked,
hrw.parent_id, hrw.assignment_id,
hrw.fcl_earn_reason_code, hrw.ffv_cost_center_id,
hrw.tas_id, hrw.location_id, hrw.sht_id,
hrw.hrw_comment, hrw.ffv_rate_code_id,
hrw.rate_multiple, hrw.hourly_rate, hrw.amount,
hrw.fcl_tax_rule_code, hrw.separate_check_flag,
hrw.seqno, hrw.time_in, hrw.time_out, hrw.project_id,
shr.earn_pol_id, hrw.tim_id, hrw.element_type_id,
hrw.created_by, hrw.creation_date, hrw.last_updated_by,
hrw.last_update_date, hrw.last_update_login,
hrw.effective_start_date, hrw.effective_end_date,
hrw.job_id, hrw.state_name, hrw.county_name,
hrw.city_name, hrw.zip_code
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_sum_hours_worked shr,
hxt_det_hours_worked hrw
WHERE hrw.date_worked BETWEEN NEXT_DAY (a_date_worked - 7,
start_day_of_week
)
AND a_date_worked
AND hrw.tim_id = a_tim_id
AND elt.element_type_id = hrw.element_type_id
AND eltv.hxt_earning_category = 'REG'
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.parent_id = shr.ID
AND shr.element_type_id IS NULL
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
ORDER BY hrw.date_worked DESC, hrw.time_in DESC, hrw.ID DESC;
SELECT hrw.ROWID
FROM hxt_det_hours_worked hrw,
pay_element_types_f elt,
hxt_pay_element_types_f_ddf_v eltv
WHERE hrw.parent_id = c_parent_id
AND hrw.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND elt.element_type_id = eltv.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'REG';
SELECT hrw.ROWID hrw_rowid, hrw.hours, hrw.date_worked,
hrw.parent_id, hrw.assignment_id, hrw.fcl_earn_reason_code,
hrw.ffv_cost_center_id, hrw.tas_id, hrw.location_id,
hrw.sht_id, hrw.hrw_comment, hrw.ffv_rate_code_id,
hrw.rate_multiple, hrw.hourly_rate, hrw.amount,
hrw.fcl_tax_rule_code, hrw.separate_check_flag, hrw.seqno,
hrw.time_in, hrw.time_out, hrw.project_id, shr.earn_pol_id,
hrw.tim_id, hrw.element_type_id, hrw.created_by,
hrw.creation_date, hrw.last_updated_by,
hrw.last_update_date, hrw.last_update_login,
hrw.effective_start_date, hrw.effective_end_date,
hrw.job_id, hrw.state_name, hrw.county_name, hrw.city_name,
hrw.zip_code
FROM hxt_det_hours_worked hrw,
hxt_sum_hours_worked shr,
pay_element_types_f elt,
hxt_pay_element_types_f_ddf_v eltv
WHERE hrw.parent_id = c_parent_id
AND hrw.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND elt.element_type_id = eltv.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.parent_id = shr.ID
AND eltv.hxt_earning_category IN ('REG', 'OVT');
l_update_rowid ROWID;
l_delete NUMBER;
l_delete_det NUMBER;
l_delete := g_parent_to_re_explode.FIRST;
EXIT WHEN NOT g_parent_to_re_explode.EXISTS (l_delete);
g_parent_to_re_explode (l_delete).parent_id := NULL;
l_delete := g_parent_to_re_explode.NEXT (l_delete);
g_parent_to_re_explode.DELETE;
SELECT COUNT (*)
INTO l_abs_count
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_sum_hours_worked shr,
hxt_timecards tim
WHERE shr.date_worked BETWEEN NEXT_DAY (c_date_worked - 7,
start_day_of_week
)
AND c_date_worked
AND shr.tim_id = c_tim_id
AND elt.element_type_id = shr.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND shr.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND shr.element_type_id IS NOT NULL
AND eltv.element_type_id = elt.element_type_id
AND shr.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
DELETE FROM hxt_det_hours_worked
WHERE ROWID = l_del_rowid;
l_reg_hrs_cur.last_updated_by,
l_reg_hrs_cur.last_update_date,
l_reg_hrs_cur.last_update_login,
g_period_start_date,
NULL -- rowid
,
l_reg_hrs_cur.effective_start_date,
l_reg_hrs_cur.effective_end_date,
l_reg_hrs_cur.project_id,
l_reg_hrs_cur.job_id,
NULL,
NULL,
NULL,
'CORRECTION',
'N',
l_reg_hrs_cur.state_name,
l_reg_hrs_cur.county_name,
l_reg_hrs_cur.city_name,
l_reg_hrs_cur.zip_code
);
l_reg_hrs_cur.last_updated_by,
l_reg_hrs_cur.last_update_date,
l_reg_hrs_cur.last_update_login,
g_period_start_date,
NULL -- rowid
,
l_reg_hrs_cur.effective_start_date,
l_reg_hrs_cur.effective_end_date,
l_reg_hrs_cur.project_id,
l_reg_hrs_cur.job_id,
NULL,
NULL,
NULL,
'CORRECTION',
'N',
l_reg_hrs_cur.state_name,
l_reg_hrs_cur.county_name,
l_reg_hrs_cur.city_name,
l_reg_hrs_cur.zip_code
);
l_delete_det := g_re_explode_detail.FIRST;
EXIT WHEN NOT g_re_explode_detail.EXISTS (l_delete_det);
g_re_explode_detail (l_delete_det).earn_pol_id := NULL;
g_re_explode_detail (l_delete_det).parent_id := NULL;
g_re_explode_detail (l_delete_det).tim_id := NULL;
g_re_explode_detail (l_delete_det).date_worked := NULL;
g_re_explode_detail (l_delete_det).assignment_id := NULL;
g_re_explode_detail (l_delete_det).hours := NULL;
g_re_explode_detail (l_delete_det).time_in := NULL;
g_re_explode_detail (l_delete_det).time_out := NULL;
g_re_explode_detail (l_delete_det).element_type_id := NULL;
g_re_explode_detail (l_delete_det).fcl_earn_reason_code :=
NULL;
g_re_explode_detail (l_delete_det).ffv_cost_center_id :=
NULL;
g_re_explode_detail (l_delete_det).tas_id := NULL;
g_re_explode_detail (l_delete_det).location_id := NULL;
g_re_explode_detail (l_delete_det).sht_id := NULL;
g_re_explode_detail (l_delete_det).hrw_comment := NULL;
g_re_explode_detail (l_delete_det).ffv_rate_code_id := NULL;
g_re_explode_detail (l_delete_det).rate_multiple := NULL;
g_re_explode_detail (l_delete_det).hourly_rate := NULL;
g_re_explode_detail (l_delete_det).amount := NULL;
g_re_explode_detail (l_delete_det).fcl_tax_rule_code :=
NULL;
g_re_explode_detail (l_delete_det).separate_check_flag :=
NULL;
g_re_explode_detail (l_delete_det).seqno := NULL;
g_re_explode_detail (l_delete_det).created_by := NULL;
g_re_explode_detail (l_delete_det).creation_date := NULL;
g_re_explode_detail (l_delete_det).last_updated_by := NULL;
g_re_explode_detail (l_delete_det).last_update_date := NULL;
g_re_explode_detail (l_delete_det).last_update_login :=
NULL;
g_re_explode_detail (l_delete_det).effective_start_date :=
NULL;
g_re_explode_detail (l_delete_det).effective_end_date :=
NULL;
g_re_explode_detail (l_delete_det).project_id := NULL;
g_re_explode_detail (l_delete_det).job_id := NULL;
g_re_explode_detail (l_delete_det).state_name := NULL;
g_re_explode_detail (l_delete_det).county_name := NULL;
g_re_explode_detail (l_delete_det).city_name := NULL;
g_re_explode_detail (l_delete_det).zip_code := NULL;
l_delete_det := g_re_explode_detail.NEXT (l_delete_det);
g_re_explode_detail.DELETE;
g_re_explode_detail (l_next_index).last_updated_by :=
l_re_explode_details.last_updated_by;
g_re_explode_detail (l_next_index).last_update_date :=
l_re_explode_details.last_update_date;
g_re_explode_detail (l_next_index).last_update_login :=
l_re_explode_details.last_update_login;
DELETE FROM hxt_det_hours_worked
WHERE parent_id = g_parent_to_re_explode (j).parent_id;
( 'g_re_explode_detail(k).last_updated_by:'
|| g_re_explode_detail (k).last_updated_by
);
( 'g_re_explode_detail(k).last_update_date:'
|| g_re_explode_detail (k).last_update_date
);
( 'g_re_explode_detail(k).last_update_login:'
|| g_re_explode_detail (k).last_update_login
);
( 'g_re_explode_detail(l_re_explode).last_updated_by:'
|| g_re_explode_detail (l_re_explode).last_updated_by
);
( 'g_re_explode_detail(l_re_explode).last_update_date:'
|| g_re_explode_detail (l_re_explode).last_update_date
);
( 'g_re_explode_detail(l_re_explode).last_update_login:'
|| g_re_explode_detail (l_re_explode).last_update_login
);
g_re_explode_detail (l_re_explode).last_updated_by,
g_re_explode_detail (l_re_explode).last_update_date,
g_re_explode_detail (l_re_explode).last_update_login,
g_period_start_date,
NULL -- rowid
,
g_re_explode_detail (l_re_explode).effective_start_date,
g_re_explode_detail (l_re_explode).effective_end_date,
g_re_explode_detail (l_re_explode).project_id,
g_re_explode_detail (l_re_explode).job_id,
NULL,
NULL,
NULL,
'CORRECTION',
'N',
g_re_explode_detail (l_re_explode).state_name,
g_re_explode_detail (l_re_explode).county_name,
g_re_explode_detail (l_re_explode).city_name,
g_re_explode_detail (l_re_explode).zip_code
);
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'DAY'
AND er.egp_id = c_earn_policy
AND c_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = c_earn_policy
AND c_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked hrw,
hxt_timecards tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp,
hxt_add_elem_info_f aei
WHERE tim.for_person_id = g_person_id
AND hrw.tim_id = tim.ID
AND erp.ID = g_ep_id
AND egt.ID = erp.egt_id
AND erg.egt_id = egt.ID
AND erg.element_type_id = hrw.element_type_id
AND hrw.element_type_id = aei.element_type_id
AND ( -- get weekly total to date
(hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
start_day_of_week
)
AND (g_date_worked - 1)
)
OR
-- get any hours worked on this day that were entered before the
-- current row, i.e., parent_id of the rest of the rows entered
-- for this day will be less than the current row.
-- i.e., for example when entering regular as well as vac hrs on
-- the same day but in two different rows, then get the hrs for the
-- rows that were entered before the current row that is being
-- processed for the day
( -- AND aei.earning_category <> 'ABS'
hrw.date_worked = g_date_worked
AND hrw.parent_id < g_id
)
)
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked hrw,
hxt_timecards tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp,
hxt_add_elem_info_f aei
WHERE tim.for_person_id = g_person_id
AND hrw.tim_id = tim.ID
AND erp.ID = g_ep_id
AND egt.ID = erp.egt_id
AND erg.egt_id = egt.ID
AND erg.element_type_id = hrw.element_type_id
AND hrw.element_type_id = aei.element_type_id
AND -- get weekly total to date
hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
start_day_of_week
)
AND g_date_worked
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date
AND hrw.date_worked BETWEEN aei.effective_start_date
AND aei.effective_end_date
AND sysdate BETWEEN aei.effective_start_date
AND aei.effective_end_date; /* Bug: 6674738 */
PROCEDURE select_weekly_hours (
p_rule_to_pay OUT NOCOPY VARCHAR2,
p_hours_to_pay_this_rule OUT NOCOPY NUMBER,
p_element_type_id_to_pay OUT NOCOPY NUMBER
)
IS
BEGIN
IF g_debug
THEN
hr_utility.set_location ('select_weekly_hours', 1);
hr_utility.set_location ('select_weekly_hours', 2);
END select_weekly_hours;
PROCEDURE select_hol_weekly_hours (
p_rule_to_pay OUT NOCOPY VARCHAR2,
p_hours_to_pay_this_rule OUT NOCOPY NUMBER,
p_element_type_id_to_pay OUT NOCOPY NUMBER
)
IS
BEGIN
IF g_debug
THEN
hr_utility.set_location ('select_hol_weekly_hours', 10);
hr_utility.set_location ('select_hol_weekly_hours', 20);
END select_hol_weekly_hours;
PROCEDURE select_rule_and_hours (
p_error_code OUT NOCOPY NUMBER,
p_rule_to_pay OUT NOCOPY VARCHAR2,
p_hours_to_pay_this_rule OUT NOCOPY NUMBER,
p_element_type_id OUT NOCOPY NUMBER
)
IS
-- Selects the rule type, hours, and earning element id to be paid on the
-- current sub-segment.
-- Daily rules are paid until either weekly or special cap is hit.
-- Special rule is only applicable if consecutive days have been reached.
-- It then acts like a daily rule in that its base can be overridden by the
-- weekly cap until the special hours cap is hit.
-- returns 0 for success, 2 for error
srh_rule VARCHAR2 (4);
SELECT er.hours
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
hr_utility.set_location ('select_rule_and_hours', 10);
hr_utility.set_location ('select_rule_and_hours', 20);
hr_utility.set_location ('Select_rule_and_hours', 30);
hr_utility.set_location ('select_rule_and_hours', 40);
hr_utility.set_location('select_rule_and_hours',40);
hr_utility.set_location('select_rule_and_hours',50);
hr_utility.set_location('select_rule_and_hours',60);
hr_utility.set_location ('select_rule_and_hours', 70);
hr_utility.set_location ('select_rule_and_hours', 80);
hr_utility.set_location ('select_rule_and_hours', 90);
hr_utility.set_location ('select_rule_and_hours', 91);
hr_utility.set_location ('select_rule_and_hours',
100);
hr_utility.set_location ('select_rule_and_hours',
101
);
('select_rule_and_hours',
102
);
hr_utility.set_location ('select_rule_and_hours',
105);
('select_rule_and_hours',
110
);
('select_rule_and_hours',
115
);
hr_utility.set_location ('select_rule_and_hours',
116
);
hr_utility.set_location ('select_rule_and_hours',
120
);
hr_utility.set_location ('select_rule_and_hours',
125
);
('select_rule_and_hours',
130
);
hr_utility.set_location ('select_rule_and_hours', 135);
hr_utility.set_location('select_rule_and_hours',120);
hr_utility.set_location('select_rule_and_hours',130);
hr_utility.set_location ('select_rule_and_hours', 140);
hr_utility.set_location ('select_rule_and_hours', 150);
hr_utility.set_location ('select_rule_and_hours', 160);
hr_utility.set_location ('select_rule_and_hours', 170);
hr_utility.set_location ('select_rule_and_hours', 180);
hr_utility.set_location ('select_rule_and_hours', 190);
PROCEDURE use_points_to_select_rule_hrs (
p_error_code OUT NOCOPY NUMBER,
p_rule_to_pay OUT NOCOPY VARCHAR2,
p_hours_to_pay_this_rule OUT NOCOPY NUMBER,
p_element_type_id OUT NOCOPY NUMBER
)
IS
CURSOR special_earning_rules (
i_earn_policy NUMBER,
i_days NUMBER,
spc_hrs_paid NUMBER
)
IS
SELECT er.hours, er.element_type_id, er.days
FROM hxt_earning_rules er
WHERE er.egr_type = 'SPC'
AND er.days = i_days
AND er.days IS NOT NULL
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
AND er.hours > spc_hrs_paid
ORDER BY er.days DESC, er.hours ASC;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'DAY'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'DAY'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
AND er.hours > daily_cap
ORDER BY er.seq_no;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
ORDER BY er.seq_no;
SELECT er.hours, er.element_type_id
FROM hxt_earning_rules er
WHERE er.egr_type = 'WKL'
AND er.egp_id = i_earn_policy
AND g_date_worked BETWEEN er.effective_start_date
AND er.effective_end_date
AND er.hours > weekly_cap
ORDER BY er.seq_no;
SELECT eg.element_type_id
FROM hxt_earn_groups eg
WHERE eg.egt_id = (SELECT ep.egt_id
FROM hxt_earning_policies ep
WHERE ep.ID = g_ep_id);
SELECT daily_hours
FROM hxt_daily_hours_worked_v
WHERE work_date || '' = cursor_day_worked
AND tim_id = cursor_tim_id;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt2,
hxt_det_hours_worked hrw,
hxt_timecards tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
WHERE tim.for_person_id = g_person_id
AND hrw.tim_id = tim.ID
AND hrw.date_worked BETWEEN NEXT_DAY (g_date_worked - 7,
g_start_day_of_week
)
AND g_date_worked
AND erp.ID = g_ep_id
AND egt.ID = erp.egt_id
AND erg.egt_id = egt.ID
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date
AND hrw.element_type_id = elt2.element_type_id
AND hrw.date_worked BETWEEN elt2.effective_start_date
AND elt2.effective_end_date
AND elt2.element_type_id = eltv.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
l_proc := 'hxt_time_detail.use_points_to_select_rule_hrs';
SELECT NVL (points_assigned, 0)
INTO l_points_assigned
FROM hxt_add_elem_info_f aei
WHERE aei.element_type_id = daily_element_type_id
AND g_date_worked BETWEEN aei.effective_start_date
AND aei.effective_end_date;
SELECT NVL (points_assigned, 0)
INTO l_points_assigned
FROM hxt_add_elem_info_f aei
WHERE aei.element_type_id = weekly_element_type_id
AND g_date_worked BETWEEN aei.effective_start_date
AND aei.effective_end_date;
SELECT NVL (points_assigned, 0)
INTO l_points_assigned
FROM hxt_add_elem_info_f aei
WHERE aei.element_type_id = weekly_element_type_id
AND g_date_worked BETWEEN aei.effective_start_date
AND aei.effective_end_date;
SELECT NVL (points_assigned, 0)
INTO l_points_assigned
FROM hxt_add_elem_info_f aei
WHERE aei.element_type_id = l_combo_elem_id
AND g_date_worked BETWEEN aei.effective_start_date
AND aei.effective_end_date;
hr_utility.TRACE ('Deleted g_special_explosion PL/SQL table');
g_special_explosion.DELETE;
hr_utility.TRACE ('Deleted combo PL/SQL table');
g_dy_wk_combo_explosion.DELETE;
hr_utility.TRACE ('Deleted daily PL/SQL table');
g_daily_explosion.DELETE;
hr_utility.TRACE ('Deleted weekly PL/SQL table');
g_weekly_explosion.DELETE;
hr_utility.TRACE ('Deleted g_daily_earn_category PL/SQL table');
g_daily_earn_category.DELETE;
hr_utility.TRACE ('Deleted g_weekly_earn_category PL/SQL table');
g_weekly_earn_category.DELETE;
select_weekly_hours (rule_to_pay,
hours_to_pay_this_rule,
element_type_id_to_pay
);
select_hol_weekly_hours (rule_to_pay,
hours_to_pay_this_rule,
element_type_id_to_pay
);
SELECT use_points_assigned
INTO l_use_points_assigned
FROM hxt_earning_policies
WHERE ID = g_ep_id;
select_rule_and_hours (ERROR_CODE,
rule_to_pay,
hours_to_pay_this_rule,
element_type_id_to_pay
);
use_points_to_select_rule_hrs (ERROR_CODE,
rule_to_pay,
hours_to_pay_this_rule,
element_type_id_to_pay
);
(delete_zero_hour_details (g_tim_id, g_ep_id, g_osp_id,
g_date_worked)
);
FUNCTION delete_zero_hour_details (
a_tim_id NUMBER,
a_ep_id NUMBER,
a_osp_id NUMBER,
a_date_worked DATE
)
RETURN NUMBER
IS
/* SIR538 This cursor was deleting detail rows that had amounts, but zero hours
so it was re-written. PWM 03-MAR-00 */
CURSOR zero_hrs_cur (c_tim_id NUMBER, c_date_worked DATE)
IS
SELECT hrw.ROWID hrw_rowid
FROM hxt_det_hours_worked hrw
WHERE hrw.date_worked <= c_date_worked
AND hrw.tim_id = c_tim_id
AND ( (
-- To take care of errorneous records created after the records
-- have been transferred to OTLR/BEE and then TC modified to zero
-- hrs(i.e., basically to delete TC) in SS
(NVL (hrw.hours, 0) = 0)
AND (NVL (hrw.amount, 0) = 0)
AND (hrw.time_in = hrw.time_out)
AND (hrw.time_in IS NOT NULL)
AND (hrw.time_out IS NOT NULL)
AND (hrw.retro_batch_id IS NOT NULL)
)
OR ( (NVL (hrw.hours, 0) = 0)
AND (NVL (hrw.amount, 0) = 0)
-- Commented out the following in order to delete the erroneous
-- records generated when updating a TC already transferred to BEE.
AND (hrw.retro_batch_id IS NULL
) -- Put it back for 3536182
-- Added this OR condition to delete erroneously generated
-- Shift diff Premium records where amount is not null and
-- time_in is equal to time_out.
OR ( (NVL (hrw.hours, 0) = 0)
AND NVL (hrw.amount, 0) <> 0
AND hrw.time_in = hrw.time_out
AND hrw.retro_batch_id IS NULL
)
)
);
SELECT dhw.ROWID dhw_rowid, dhw.ID, dhw.parent_id, dhw.tim_id,
dhw.hours, dhw.time_in, dhw.time_out, dhw.seqno,
eltv.hxt_premium_amount, eltv.hxt_earning_category,
dhw.element_type_id
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
hxt_det_hours_worked_f dhw
WHERE dhw.tim_id = c_tim_id
AND dhw.date_worked = c_date_worked
AND elt.element_type_id = dhw.element_type_id
AND dhw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND elt.element_type_id = c_osp_id
AND dhw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category = 'OSP'
AND eltv.hxt_premium_type = 'FIXED'
AND dhw.ID >
(SELECT /*+ NO_UNNEST */
MIN (hdw.ID)
FROM hxt_det_hours_worked_f hdw
WHERE hdw.tim_id = dhw.tim_id
AND hdw.date_worked = c_date_worked
AND hdw.element_type_id = dhw.element_type_id
AND SYSDATE BETWEEN hdw.effective_start_date and hdw.effective_end_date)
ORDER BY dhw.date_worked DESC,
dhw.time_in DESC,
dhw.seqno DESC,
dhw.parent_id DESC;
hr_utility.set_location ('hxt_time_detail.delete_zero_hour_details',
10
);
LOOP -- through detail rows and delete zero hour rows
IF g_debug
THEN
hr_utility.set_location
('hxt_time_detail.delete_zero_hour_details',
20
);
DELETE FROM hxt_det_hours_worked
WHERE ROWID = zero_rec.hrw_rowid;
('hxt_time_detail.delete_zero_hour_details',
30
);
DELETE FROM hxt_det_hours_worked
WHERE ROWID = dup_flat_amt_rec.dhw_rowid;
hr_utility.set_location ('hxt_time_detail.delete_zero_hour_details',
40
);
fnd_message.set_name ('HXT', 'HXT_39579_DELETE_DETAIL_ERR');
'HXT_39579_DELETE_DETAIL_ERR',
NULL,
LOCATION,
'',
SQLERRM
);
END delete_zero_hour_details;
SELECT DISTINCT element_type_id
FROM hxt_det_hours_worked hrw
WHERE hrw.parent_id = a_parent_id;
SELECT hrw.ROWID hrw_rowid, hrw.time_in, hrw.time_out, hrw.hours,
hrw.amount, hrw.hourly_rate, hrw.rate_multiple,
hrw.prev_wage_code
FROM hxt_det_hours_worked hrw
WHERE hrw.parent_id = a_parent_id
AND hrw.element_type_id = a_element_type_id
ORDER BY time_in, time_out;
DELETE FROM hxt_det_hours_worked
WHERE ROWID = row_id1;
UPDATE hxt_det_hours_worked
SET hours = ln_hours
WHERE ROWID = ln_row_id;
DELETE FROM hxt_det_hours_worked
WHERE ROWID = row_id1;
UPDATE hxt_det_hours_worked
SET time_out = ln_end_time,
hours = ln_hours_worked
WHERE ROWID = ln_row_id;
SELECT hws.off_shift_prem_id
FROM hxt_shifts hs,
hxt_work_shifts hws,
hxt_per_aei_ddf_v aeiv,
hxt_rotation_schedules hrs
WHERE aeiv.assignment_id = p_assignment_id
AND p_date_worked BETWEEN aeiv.effective_start_date
AND aeiv.effective_end_date
AND hrs.rtp_id = aeiv.hxt_rotation_plan
AND hrs.start_date =
(SELECT MAX (start_date)
FROM hxt_rotation_schedules
WHERE rtp_id = hrs.rtp_id AND start_date <= p_date_worked)
AND hws.tws_id = hrs.tws_id
AND hws.week_day = TO_CHAR (p_date_worked, 'DY')
AND hws.sht_id = hs.ID;
SELECT /*+ ORDERED
INDEX(hrs hxt_rotation_schedules_pk)
USE_NL(aeiv hrs)
USE_NL(hrs hws) */
hws.off_shift_prem_id
FROM hxt_per_aei_ddf_v aeiv,
hxt_rotation_schedules hrs,
hxt_work_shifts hws,
hxt_shifts hs
WHERE aeiv.assignment_id = p_assignment_id
AND p_date_worked BETWEEN aeiv.effective_start_date
AND aeiv.effective_end_date
AND hrs.rtp_id = aeiv.hxt_rotation_plan
AND hrs.start_date <= p_date_worked
AND hws.tws_id = hrs.tws_id
AND hws.week_day = TO_CHAR (p_date_worked, 'DY')
AND hws.sht_id = hs.ID
ORDER BY hrs.start_date DESC;
SELECT count(*)
INTO l_det_count
FROM hxt_det_hours_worked_f
WHERE date_worked = a_date_worked
AND assignment_id = a_assignment_id;
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_effective_start_date,
g_effective_end_date, -- C431
a_project_id,
g_pay_status, -- RETROPAY
g_pa_status, -- PROJACCT
g_retro_batch_id, -- RETROPAY
g_state_name => a_state_name,
g_county_name => a_county_name,
g_city_name => a_city_name,
g_zip_code => a_zip_code
-- a_GROUP_ID -- HXT11i1
);
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_effective_start_date,
g_effective_end_date,
a_project_id,
g_pay_status,
g_pa_status,
g_retro_batch_id,
g_state_name => a_state_name,
g_county_name => a_county_name,
g_city_name => a_city_name,
g_zip_code => a_zip_code
-- a_GROUP_ID
);
SELECT hrw.hours, hrw.element_type_id, eltv.hxt_earning_category
FROM hxt_det_hours_worked hrw,
hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt
WHERE elt.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.tim_id = p_tim_id
AND hrw.date_worked = p_date_worked
AND ( ( hrw.time_in IS NOT NULL
AND hrw.time_out IS NOT NULL
AND hrw.time_in <> hrw.time_out
)
OR (hrw.time_in IS NULL AND hrw.time_out IS NULL)
)
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */
SELECT 1
FROM hxt_earn_group_types hegt, hxt_earn_groups heg
WHERE hegt.ID = p_egt_id
AND heg.egt_id = p_egt_id
AND heg.element_type_id = p_element_type_id;
SELECT hrw.hours, hrw.element_type_id, eltv.hxt_earning_category
FROM hxt_sum_hours_worked hrw,
hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt
WHERE elt.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.tim_id = p_tim_id
AND hrw.date_worked = p_date_worked
AND ( ( hrw.time_in IS NOT NULL
AND hrw.time_out IS NOT NULL
AND hrw.time_in <> hrw.time_out
)
OR (hrw.time_in IS NULL AND hrw.time_out IS NULL)
)
-- Following condition to get total for override hrs only
AND hrw.element_type_id IS NOT NULL
AND nvl(eltv.exclude_from_explosion, 'N') <> 'Y'; /* Bug: 4489952 */