[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);
g_hol_LAST_UPDATED_BY NUMBER;
g_hol_LAST_UPDATE_DATE DATE;
g_hol_LAST_UPDATE_LOGIN NUMBER;
g_hol_DT_UPDATE_MODE VARCHAR2(30);
PROCEDURE Delete_Details(p_location IN VARCHAR2
,p_error_code IN OUT NOCOPY NUMBER);
g_hol_LAST_UPDATED_BY := g_LAST_UPDATED_BY;
g_hol_LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
g_hol_LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
g_hol_DT_UPDATE_MODE := g_DT_UPDATE_MODE;
IS SELECT 1
FROM hxt_sum_hours_worked_f sum
WHERE tim_id = g_hol_tim_id
AND g_sum_session_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND ( date_worked > g_hol_date_worked
OR ( date_worked = g_hol_date_worked
AND NVL(element_type_id,99999999999) > NVL(g_hol_element_type_id,99999999999)
)
OR ( date_worked = g_hol_date_worked
AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
AND NVL(time_in,hr_general.end_of_time) > NVL(g_hol_time_in,hr_general.end_of_time)
)
OR (date_worked = g_hol_date_worked
AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
AND NVL(time_in,hr_general.end_of_time) = NVL(g_hol_time_in,hr_general.end_of_time)
AND seqno > g_hol_seqno
)
OR (date_worked = g_hol_date_worked
AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
AND NVL(time_in,hr_general.end_of_time) = NVL(g_hol_time_in,hr_general.end_of_time)
AND seqno = g_hol_seqno
AND id > g_hol_pk_id
)
);
hr_utility.trace('All the duplicate details can be deleted ');
DELETE FROM hxt_det_hours_worked_f
WHERE (tim_id,
id,
parent_id) IN
( SELECT dup.tim_id,
dup.det_id,
dup.parent_id
FROM hxt_det_duplicate_regular dup
WHERE dup.tim_id = g_hol_tim_id);
DELETE FROM hxt_det_duplicate_regular
WHERE tim_id = g_hol_tim_id;
g_hol_LAST_UPDATED_BY := NULL;
g_hol_LAST_UPDATE_DATE := NULL;
g_hol_LAST_UPDATE_LOGIN := NULL;
g_hol_DT_UPDATE_MODE := NULL;
IS SELECT hours,
hours- NVL(LAG(hours) OVER (ORDER BY hours) ,0) cap,
element_type_id
FROM hxt_earning_policies ep,
hxt_earning_rules er
WHERE ep.id = p_ep_id
AND er.egp_id = ep.id
AND g_sum_session_date BETWEEN er.effective_start_date
AND er.effective_end_date
AND er.egr_type = 'HOL'
ORDER BY hours;
IS SELECT DISTINCT
er.element_type_id
FROM hxt_earning_rules er,
hxt_pay_element_types_f_ddf_v elem
WHERE er.egp_id = p_ep_id
AND p_date BETWEEN er.effective_start_date
AND er.effective_end_date
AND g_sum_session_date BETWEEN er.effective_start_date
AND er.effective_end_date
AND elem.element_type_id = er.element_type_id
AND elem.hxt_earning_category = 'REG';
IS SELECT 1
FROM hxt_sum_hours_worked_f sum
WHERE seqno > p_seq_no
AND tim_id = g_hol_tim_id
AND g_sum_session_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND date_worked = p_date
AND element_type_id IS NULL
AND ROWNUM < 2;
IS SELECT 1
FROM hxt_sum_hours_worked_f sum
WHERE time_in > p_time_in
AND g_sum_session_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND tim_id = g_hol_tim_id
AND date_worked = p_date
AND element_type_id IS NULL
AND ROWNUM < 2;
SELECT NVL(SUM(det.hours),0)
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxt_earning_rules er,
hxt_pay_element_types_f_ddf_v elem
WHERE det.tim_id = g_hol_tim_id
AND sum.id = det.parent_id
AND g_sum_session_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND g_sum_session_date BETWEEN det.effective_start_date
AND det.effective_end_date
AND g_sum_session_date BETWEEN elem.effective_start_date
AND elem.effective_end_date
AND g_sum_session_date BETWEEN er.effective_start_date
AND er.effective_end_date
AND sum.date_worked = g_hol_date_worked
AND sum.element_type_id IS NULL
AND er.egp_id = g_hol_ep_id
AND er.egr_type NOT IN ('HOL')
AND er.element_type_id = det.element_type_id
AND elem.element_type_id = det.element_type_id
AND elem.hxt_earning_category = 'OVT' ;
SELECT det.id,
det.time_in,
det.time_out,
det.element_type_id,
0 del_y_n
FROM hxt_det_hours_worked det,
hxt_add_elem_info_f elem
WHERE det.parent_id = p_id
AND det.element_type_id = elem.element_type_id
AND elem.earning_category = 'SDF'
ORDER BY det.element_type_id,
det.time_in,
det.time_out;
IS SELECT 1
FROM hxt_sum_hours_worked sum,
hxt_holiday_days hhd
WHERE tim_id = p_tim_id
AND sum.date_worked > p_date_worked
AND hhd.hcl_id = p_hol_id
AND sum.date_worked = hhd.holiday_date;
'SELECT NVL(sum(sum.hours),0)
FROM hxt_sum_hours_worked_f SUM,
hxt_det_hours_worked_f DET
WHERE sum.tim_id = :tim_id
AND sum.id = det.parent_id
AND sum.element_type_id IS NULL
AND sum.date_worked = FND_date.CANONICAL_TO_DATE(:date_worked)
AND det.element_type_id IN ELEMENT_LIST
AND FND_DATE.CANONICAL_TO_DATE(:session_date1) BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND FND_DATE.CANONICAL_TO_DATE(:session_date2) BETWEEN det.effective_start_date
AND det.effective_end_date' ;
SELECT 1
INTO l_hol_true
FROM hxt_holiday_days
WHERE hcl_id = g_hol_hol_id
AND holiday_date = g_hol_date_worked;
hr_utility.trace('Selecting Regular Elements ');
SELECT det.id det_id,
ROWIDTOCHAR(det.rowid),
det.tim_id,
det.parent_id
BULK COLLECT INTO l_det_tab
FROM hxt_sum_hours_worked_f sum,
hxt_det_hours_worked_f det
WHERE sum.element_type_id IS NULL
AND sum.id = det.parent_id
AND SYSDATE BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND SYSDATE BETWEEN det.effective_start_date
AND det.effective_end_Date
AND det.tim_id = g_hol_tim_id
AND det.element_type_id = l_reg
AND sum.date_worked = g_hol_date_worked;
INSERT INTO hxt_det_duplicate_regular
( det_id,
det_rowid,
tim_id,
parent_id)
VALUES ( g_det_tab(g_index).det_id,
g_det_tab(g_index).det_rowid,
g_det_tab(g_index).tim_id,
g_det_tab(g_index).parent_id);
l_holidays.DELETE(l_ind);
g_LAST_UPDATED_BY := g_hol_LAST_UPDATED_BY;
g_LAST_UPDATE_DATE := g_hol_LAST_UPDATE_DATE;
g_LAST_UPDATE_LOGIN := g_hol_LAST_UPDATE_LOGIN;
g_DT_UPDATE_MODE := g_hol_DT_UPDATE_MODE;
hr_utility.trace(' Iteration 1 to find those to be updated ');
hr_utility.trace(' Iteration 2 to find those to be deleted ');
UPDATE hxt_det_hours_worked_f
SET time_in = l_time_in_tab(i),
time_out = l_time_out_tab(i),
hours = (l_time_out_tab(i) - l_time_in_tab(i))*24
WHERE id = l_id_tab(i)
AND l_del_tab(i) = 2;
DELETE FROM hxt_det_hours_worked_f
WHERE id = l_id_tab(i)
AND l_del_tab(i) = 1;
,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);
IS SELECT /*+ ORDERED */
prev.id
FROM hxt_timecards_f tim,
per_time_periods ptp,
hxt_timecards_f prev
WHERE tim.id = p_tim_id
AND p_session_date BETWEEN tim.effective_start_date
AND tim.effective_end_date
AND tim.payroll_id = ptp.payroll_id
AND p_week_start BETWEEN ptp.start_date
AND ptp.end_date
AND prev.time_period_id = ptp.time_period_id
AND prev.for_person_id = p_person_id
AND p_session_date BETWEEN prev.effective_start_date
AND prev.effective_end_date
AND prev.id <> tim.id ;
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_det_hours_worked_f
SET effective_end_date = g_effective_start_date - 1
WHERE parent_id = g_id
AND g_sum_session_date BETWEEN effective_start_date
AND effective_end_date ;
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
);
IS SELECT sum(hours)
FROM hxt_sum_hours_worked_f sum
WHERE p_session_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND date_worked = p_date_worked
AND tim_id = p_tim_id
AND id <> p_id;
IS SELECT max(hours)
FROM hxt_earning_policies ep,
hxt_earning_rules er
WHERE ep.id = p_ep_id
AND g_sum_session_date BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND ep.fcl_earn_type = 'SPECIAL'
AND ep.id = er.egp_id
AND g_sum_session_date BETWEEN er.effective_start_date
AND er.effective_end_date
AND er.egr_type = 'DAY'
AND ep.use_points_assigned = 'Y';
,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
);