[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;
g_DT_UPDATE_MODE VARCHAR2(30);
PROCEDURE Delete_Details(p_location IN VARCHAR2
,p_error_code IN OUT NOCOPY NUMBER);
,p_LAST_UPDATED_BY IN NUMBER
,p_LAST_UPDATE_DATE IN DATE
,p_LAST_UPDATE_LOGIN IN NUMBER
,p_PERIOD_START_DATE IN DATE --SPR C389
,p_ROWIDIN IN VARCHAR2 --SIR012
,p_EFFECTIVE_START_DATE IN DATE --SIR012
,p_EFFECTIVE_END_DATE IN DATE --SIR012
,p_PROJECT_ID IN NUMBER --SIR022
,p_JOB_ID IN NUMBER --SIR015
,p_PAY_STATUS IN VARCHAR2 --SIR020
,p_PA_STATUS IN VARCHAR2 --SIR022
,p_RETRO_BATCH_ID IN NUMBER --SIR020
,p_DT_UPDATE_MODE IN VARCHAR2 --SIR020
,p_CALL_ADJUST_ABS IN VARCHAR2 DEFAULT 'Y'
,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
l_location VARCHAR2(120);
PROCEDURE insert_non_explodable_hrs
IS
l_object_version_number HXT_DET_HOURS_WORKED_F.OBJECT_VERSION_NUMBER%TYPE;
SELECT hxt_seqno.nextval next_id
FROM dual;
SELECT eltv.hxt_premium_type,
eltv.hxt_premium_amount,
eltv.hxt_processing_order
FROM hxt_pay_element_types_f_ddf_v eltv
WHERE eltv.hxt_earning_category NOT IN ('REG', 'ABS')
AND g_DATE_WORKED between eltv.effective_start_date
and eltv.effective_end_date
AND eltv.element_type_id = g_element_type_id
ORDER by eltv.hxt_processing_order;
hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
p_rowid => l_rowid,
p_id => l_id,
p_parent_id => g_id,
p_tim_id => g_tim_id,
p_date_worked => g_date_worked,
p_assignment_id => g_assignment_id,
p_hours => l_hours,
p_time_in => g_time_in,
p_time_out => g_time_out,
p_element_type_id => g_element_type_id,
p_fcl_earn_reason_code => g_fcl_earn_reason_code,
p_ffv_cost_center_id => l_ffv_cost_center_id,
p_ffv_labor_account_id => NULL,
p_tas_id => g_TAS_ID,
p_location_id => g_location_id,
p_sht_id => g_sht_id,
p_hrw_comment => g_hrw_comment,
p_ffv_rate_code_id => g_ffv_rate_code_id,
p_rate_multiple => l_rate_multiple,
p_hourly_rate => l_hourly_rate,
p_amount => l_amount,
p_fcl_tax_rule_code => g_fcl_tax_rule_code,
p_separate_check_flag => g_separate_check_flag,
p_seqno => g_seqno,
p_created_by => g_created_by,
p_creation_date => g_creation_date,
p_last_updated_by => g_last_updated_by,
p_last_update_date => g_last_update_date,
p_last_update_login => g_last_update_login,
p_actual_time_in => NULL,
p_actual_time_out => NULL,
p_effective_start_date => g_effective_start_date,
p_effective_end_date => g_effective_end_date,
p_project_id => g_project_id,
p_job_id => NULL,
p_earn_pol_id => NULL,
p_retro_batch_id => l_retro_batch_id,
p_pa_status => l_pa_status,
p_pay_status => l_pay_status,
--p_group_id => g_group_id,
p_object_version_number => l_object_version_number,
p_STATE_NAME => g_STATE_NAME,
p_COUNTY_NAME => g_COUNTY_NAME,
p_CITY_NAME => g_CITY_NAME,
p_ZIP_CODE => g_ZIP_CODE);
hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
END insert_non_explodable_hrs;
SELECT 'Y'
FROM hxt_add_elem_info_f
WHERE element_type_id = p_element_type_id
AND p_date_worked BETWEEN effective_start_date
AND effective_end_date
AND NVL(exclude_from_explosion, 'N') = 'Y';
g_LAST_UPDATED_BY := p_LAST_UPDATED_BY;
g_LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
g_LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
g_DT_UPDATE_MODE := p_DT_UPDATE_MODE;
SELECT start_date,period_type
INTO l_period_start_date,l_period_type
FROM hxc_recurring_periods
WHERE recurring_period_id = otl_recurring_period;
insert_non_explodable_hrs;
SELECT count(1)
INTO l_non_exp_elem_count
FROM hxt_add_elem_info_f hei
WHERE hei.element_type_id = g_element_type_id
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date
AND NVL(hei.exclude_from_explosion, 'N') = 'Y';
SELECT '1'
INTO error_code
FROM SYS.DUAL
WHERE EXISTS (SELECT hrw.id
FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.time_in IS NOT NULL
AND hrw.element_type_id = hei.element_type_id
AND hrw.element_type_id is not null
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date
AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
UNION
SELECT hrw.id
FROM hxt_sum_hours_worked hrw
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.time_in IS NOT NULL
AND hrw.element_type_id is null
);
SELECT '1'
INTO error_code
FROM SYS.DUAL
WHERE EXISTS (SELECT hrw.id
FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.time_in IS NULL
AND hrw.hours<>0
AND hrw.element_type_id = hei.element_type_id
AND hrw.element_type_id is not null
AND g_date_worked BETWEEN hei.effective_start_date
AND hei.effective_end_date
AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
UNION
SELECT hrw.id
FROM hxt_sum_hours_worked hrw
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.time_in IS NULL
AND hrw.hours<>0
AND hrw.element_type_id is null
);
PROCEDURE Delete_Details(p_location IN VARCHAR2
,p_error_code IN OUT NOCOPY NUMBER) IS
--Begins by deleting details for current summary record
CURSOR completed_time_card IS
SELECT 'Y'
FROM hxt_det_hours_worked_f
WHERE tim_id=g_tim_id
AND pay_status = 'C';
hr_utility.set_location('hxt_time_summary.Delete_Details',10);
IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
if g_debug then
hr_utility.set_location('hxt_time_summary.Delete_Details',20);
DELETE from hxt_det_hours_worked_f
WHERE parent_id = g_id;
DELETE from hxt_errors_f where --SPR C153
hrw_id = g_id; --SPR C153
hr_utility.set_location('hxt_time_summary.Delete_Details',30);
UPDATE hxt_det_hours_worked_f
SET effective_end_date = g_effective_start_date - 1
WHERE rowid in (
SELECT rowid
FROM hxt_det_hours_worked
WHERE parent_id = g_id);
UPDATE hxt_errors_f
SET effective_end_date = g_effective_start_date - 1
WHERE rowid in (
SELECT rowid
FROM hxt_errors
WHERE hrw_id = g_id);
hr_utility.set_location('hxt_time_summary.Delete_Details',40);
END; -- delete details
SELECT hs.standard_stop
FROM hxt_shifts hs
,hxt_work_shifts hws
,hxt_per_aei_ddf_v aeiv
,hxt_rotation_schedules rts
WHERE aeiv.assignment_id = p_ASSIGNMENT_ID
AND p_DATE_WORKED between aeiv.effective_start_date
and aeiv.effective_end_date
AND rts.rtp_id = aeiv.hxt_rotation_plan
AND rts.start_date = (SELECT MAX(start_date)
FROM hxt_rotation_schedules
WHERE rtp_id = rts.rtp_id
AND start_date <= p_DATE_WORKED
)
AND hws.tws_id = rts.tws_id
AND hws.week_day = to_char(p_DATE_WORKED,'DY')
AND hws.sht_id = hs.id;
SELECT hs.standard_start , hs.standard_stop
FROM hxt_per_aei_ddf_v aeiv
,hxt_rotation_schedules rts
,hxt_work_shifts hws
,hxt_shifts hs
WHERE aeiv.assignment_id = g_ASSIGNMENT_ID
AND g_DATE_WORKED between aeiv.effective_start_date
and aeiv.effective_end_date
AND rts.rtp_id = aeiv.hxt_rotation_plan
AND rts.start_date = (SELECT MAX(start_date)
FROM hxt_rotation_schedules
WHERE rtp_id = rts.rtp_id
AND start_date <= g_DATE_WORKED
)
AND hws.tws_id = rts.tws_id
AND hws.week_day = hxt_util.get_week_day(g_DATE_WORKED)
AND hws.sht_id = hs.id;
SELECT sdr.start_time
,sdr.stop_time
-- ,sdr.element_type_id
,sdr.carryover_time
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;
lv_insert_flag VARCHAR2(1) := 'Y';
lv_insert_flag := 'N';
if lv_insert_flag = 'Y' then
ln_next_index := segment_chunks.count + 1;
select sdr.start_time
,sdr.stop_time
,sdr.carryover_time
,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;
SELECT sdr.element_type_id
,sdr.carryover_time
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(segment_start_time, 'HH24MI'))
AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time)
-- sdr.start segment.start sdr.stop |
OR ((to_number(to_char(segment_start_time, 'HH24MI')) <= sdr.start_time)
AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time
AND sdr.start_time > sdr.stop_time)
-- sdr.start | segment.start sdr.stop
OR (sdr.start_time <= to_number(to_char(segment_start_time, 'HH24MI'))
AND sdr.start_time > sdr.stop_time) );
SELECT sdr.start_time
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(segment_start_time, 'HH24MI')) < sdr.start_time
AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
-- segment.start sdr.start segment.stop |
OR (to_number(to_char(segment_start_time, 'HH24MI')) > sdr.start_time
AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI'))
AND to_number(to_char(segment_start_time, 'HH24MI')) >
to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
-- segment.start | sdr.start segment.stop
OR ( to_number(to_char(segment_start_time, 'HH24MI')) < sdr.start_time
AND to_number(to_char(segment_start_time, 'HH24MI')) >
to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) ));
segment_chunks.delete;
sorted_chunks.delete;
SELECT 'Y'
INTO l_retcode
FROM hxt_earn_group_types egt
WHERE egt.FCL_EG_TYPE = 'INCLUDE'
AND p_date between egt.effective_start_date
and egt.effective_end_date
AND egt.id = p_egt_id
AND exists (SELECT 'x'
FROM hxt_earn_groups egr
WHERE egr.egt_id = p_egt_id -- SPR C150
AND egr.element_type_id = p_element_type_id
);
,g_LAST_UPDATED_BY
,g_LAST_UPDATE_DATE
,g_LAST_UPDATE_LOGIN
,g_start_day_of_week
,g_EFFECTIVE_START_DATE
,g_EFFECTIVE_END_DATE
,g_PROJECT_ID
,g_JOB_ID
,g_PAY_STATUS
,g_PA_STATUS
,g_RETRO_BATCH_ID
,g_PERIOD_START_DATE
,g_CALL_ADJUST_ABS
,g_STATE_NAME
,g_COUNTY_NAME
,g_CITY_NAME
,g_ZIP_CODE
--,g_GROUP_ID
);