[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'R', retro_batch_id
FROM hxt_det_hours_worked
WHERE tim_id = p_tim_id
AND pay_status = 'R';
SELECT 'R'
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pay_status = 'C';
SELECT 'R'
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pa_status = 'R';
SELECT 'R'
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pa_status = 'C';
SELECT 'R'
FROM hxt_batch_states tbs,
hxt_timecards_f tim
WHERE tim.id = p_tim_id
AND tbs.batch_id = tim.batch_id
AND tbs.status = 'VT'
AND NOT EXISTS (SELECT '1'
FROM hxt_det_hours_worked_f det
WHERE det.tim_id = tim.id
AND NVL(det.hours,0) > 0);
g_LAST_UPDATED_BY IN NUMBER,
g_LAST_UPDATE_DATE IN DATE,
g_LAST_UPDATE_LOGIN IN NUMBER,
g_EFFECTIVE_START_DATE IN DATE,
g_EFFECTIVE_END_DATE IN DATE,
g_PROJECT_ID IN NUMBER,
g_PAY_STATUS IN VARCHAR2,
g_PA_STATUS IN VARCHAR2,
g_RETRO_BATCH_ID IN NUMBER,
g_STATE_NAME IN VARCHAR2 DEFAULT NULL,
g_COUNTY_NAME IN VARCHAR2 DEFAULT NULL,
g_CITY_NAME IN VARCHAR2 DEFAULT NULL,
g_ZIP_CODE IN VARCHAR2 DEFAULT NULL
--g_GROUP_ID IN NUMBER
)
RETURN NUMBER IS
location VARCHAR2(120) := g_location||':PAY';
PROCEDURE INSERT_HRS( p_return_code OUT NOCOPY NUMBER,
p_id OUT NOCOPY NUMBER,
p_hours IN NUMBER,
p_time_in IN DATE,
p_time_out IN DATE,
p_element_type_id IN NUMBER,
p_seqno IN NUMBER,
p_location IN VARCHAR2 ) IS
-- Procedure INSERT_HRS
--
-- Purpose
-- Insert a record in HXT_HOURS_WORKED.
--
-- Returns
-- p_return_code - the record code (0 - no errors 2 - errors occured.
-- p_id - ID of record inserted
--
-- Arguments
-- base record columns - The values to be inserted.
-- Modifications
-- 2/15/96 Changed line_status field write to always be null as children
-- hours worked records do not need their parents' status. AVS
-- 4/23/97 Added the get_ovt_rates_cur to fetch premium types and amounts
-- which need to be inserted into hxt_det_hours_worked table.
-- Fixed under Oracle Bugs #465434 & #464850.
-- 1/07/98 SIR69 Cursor get_ovt_rates_cur now handles all premiums and
-- not just overtime. Was ignoring earn types of OTH etc.
-- 1/22/98 SIR092 Hours are not written to premium types of FIXED.
--
v_amount hxt_det_hours_worked.amount%type := null; --SIR029
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 = p_element_type_id
ORDER by eltv.hxt_processing_order;
hr_utility.set_location('hxt_time_pay.INSERT_HRS',10);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',20);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',30);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',40);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',50);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',60);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',70);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',80);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',90);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',100);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',110);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',120);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',130);
/* INSERT INTO hxt_det_hours_worked_f(id,
parent_id,
tim_id,
date_worked,
assignment_id,
hours,
time_in,
time_out,
element_type_id,
fcl_earn_reason_code,
ffv_cost_center_id,
tas_id,
location_id,
sht_id,
hrw_comment,
ffv_rate_code_id,
rate_multiple,
hourly_rate,
amount,
fcl_tax_rule_code,
separate_check_flag,
seqno,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
effective_start_date,
effective_end_date,
project_id,
pay_status,
pa_status,
retro_batch_id
--group_id
)
VALUES(p_id,
g_id,
g_tim_id,
g_date_worked,
g_assignment_id,
l_hours,
p_time_in,
p_time_out,
p_element_type_id,
g_fcl_earn_reason_code,
l_ffv_cost_center_id,
g_tas_id,
g_location_id,
g_sht_id,
g_hrw_comment,
g_ffv_rate_code_id,
l_rate_multiple,
l_hourly_rate,
l_amount,
g_fcl_tax_rule_code,
g_separate_check_flag,
p_seqno,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_effective_start_date,
g_effective_end_date,
g_project_id,
l_pay_status,
l_pa_status,
l_retro_batch_id
--g_group_id
); */
/* Call dml to insert hours into hxt_det_hours_worked_f */
if g_debug then
hr_utility.set_location('hxt_time_pay.INSERT_HRS',140);
hxt_dml.insert_HXT_DET_HOURS_WORKED(
p_rowid => l_rowid,
p_id => p_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 => p_time_in,
p_time_out => p_time_out,
p_element_type_id => p_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 => p_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_pay.INSERT_HRS',150);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',160);
hr_utility.set_location('hxt_time_pay.INSERT_HRS',170);
END INSERT_HRS;
SELECT /* +INDEX (ell pay_pk) +INDEX(per per_pk) */
per.elt_premium_id, eltt.element_name, eltv.hxt_earning_category,
eltv.hxt_premium_type, eltv.hxt_premium_amount,
eltv.hxt_processing_order
FROM pay_element_links_f ell,
hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
pay_element_types_f_tl eltt,
hxt_prem_eligblty_rules per,
per_all_assignments_f asm
WHERE asm.assignment_id = g_ASSIGNMENT_ID
AND g_DATE_WORKED between asm.effective_start_date
and asm.effective_end_date
AND per.pep_id = g_PEP_ID -- prem eligibility policy
AND per.elt_base_id = p_element_type_id -- base-hour earning
AND g_DATE_WORKED between per.effective_start_date
and per.effective_end_date
AND per.elt_premium_id = elt.element_type_id -- element type
AND g_DATE_WORKED between elt.effective_start_date
and elt.effective_end_date
AND elt.element_type_id = -- allow hol prem if holiday
decode(eltv.hxt_earning_category, 'HOL',
decode(g_HOL_YN,'Y',elt.element_type_id,-1),elt.element_type_id)
AND( elt.element_type_id = -- restrict to earnings passed, if any
decode(eltv.hxt_earning_category, 'SDF', p_sdf_id,
'OSP', g_OSP_ID,
elt.element_type_id)
-- Added the OR clause to check if the earning type
-- is the Override earning.
OR elt.element_type_id = -- restrict to earnings passed, if any
decode(eltv.hxt_earning_category, 'SDF', p_sdovr_id,
'OSP', g_OSP_ID,
elt.element_type_id) )
AND eltt.element_type_id = elt.element_type_id
AND eltt.language = userenv('LANG')
AND elt.element_type_id = eltv.element_type_id
AND g_DATE_WORKED between eltv.effective_start_date
and eltv.effective_end_date
AND eltv.hxt_earning_category NOT in('ABS','REG','OVT')
AND elt.element_type_id = ell.element_type_id
AND g_DATE_WORKED between ell.effective_start_date
and ell.effective_end_date
AND nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1)) =
nvl(asm.pay_basis_id,-1)
AND nvl(ell.employment_category,nvl(asm.employment_category,-1)) =
nvl(asm.employment_category,-1)
AND nvl(ell.payroll_id,nvl(asm.payroll_id,-1)) =
nvl(asm.payroll_id,-1)
AND nvl(ell.location_id,nvl(asm.location_id,-1)) =
nvl(asm.location_id,-1)
AND nvl(ell.grade_id,nvl(asm.grade_id,-1)) = nvl(asm.grade_id,-1)
AND nvl(ell.position_id,nvl(asm.position_id,-1)) = nvl(asm.position_id,-1)
AND (nvl(ell.job_id, nvl(asm.job_id,-1)) = nvl(asm.job_id,-1))
--
-- We need to link to pay_assignment_link_usages for people_group eligibility.
--
AND (ell.people_group_id is null
OR EXISTS ( SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
WHERE USAGE.ASSIGNMENT_ID = ASM.ASSIGNMENT_ID
AND USAGE.ELEMENT_LINK_ID = ELL.ELEMENT_LINK_ID
AND G_DATE_WORKED BETWEEN USAGE.EFFECTIVE_START_DATE
AND USAGE.EFFECTIVE_END_DATE))
AND nvl(ell.organization_id,nvl(asm.organization_id,-1)) =
nvl(asm.organization_id,-1)
AND nvl(ell.business_group_id,nvl(asm.business_group_id,-1)) =
nvl(asm.business_group_id,-1) -- link to assignment
UNION ALL
-- Second part of union handles possible overtime
SELECT elt.element_type_id
,eltt.element_name
,eltv.hxt_earning_category --FORMS60
,eltv.hxt_premium_type
,eltv.hxt_premium_amount
,eltv.hxt_processing_order
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,pay_element_types_f_tl eltt
WHERE elt.element_type_id = p_element_type_id
AND g_DATE_WORKED between elt.effective_start_date
and elt.effective_end_date
AND eltt.element_type_id = elt.element_type_id
AND eltt.language = userenv('LANG')
AND elt.element_type_id = eltv.element_type_id
AND g_DATE_WORKED between eltv.effective_start_date
and eltv.effective_end_date
AND eltv.hxt_earning_category = 'OVT' -- overtime only
ORDER by 6;
SELECT seqno
FROM hxt_det_hours_worked
WHERE id = p_BASE_ID;
SELECT nvl(min(hrw.seqno),9999)
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,hxt_det_hours_worked hrw
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.parent_id = g_ID
AND hrw.seqno > l_min_detail_seqno
AND elt.element_type_id = hrw.element_type_id
AND eltv.element_type_id = elt.element_type_id
AND g_DATE_WORKED BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND eltv.hxt_earning_category IN ('REG','OVT','ABS');
SELECT --+INDEX(pir pir_pk)
eltt.element_name,
eltv.hxt_premium_amount multiple,
hrw.element_type_id, --FORMS60
hrw.rowid hrwrowid,
eltv.hxt_earning_category,
eltv.hxt_premium_type
FROM hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt,
pay_element_types_f_tl eltt,
hxt_prem_interact_rules pir,
hxt_det_hours_worked hrw -- C421
WHERE hrw.tim_id = g_TIM_ID
AND hrw.date_worked = g_DATE_WORKED
AND hrw.parent_id = g_ID -- same parent as base record
AND pir.elt_prior_prem_id = hrw.element_type_id
AND pir.pip_id = g_PIP_ID -- prem interaction policy
AND pir.elt_earned_prem_id = p_earn_id -- driving premium
AND g_DATE_WORKED between pir.effective_start_date
and pir.effective_end_date
AND pir.apply_prior_prem_yn = 'Y'
AND pir.elt_prior_prem_id = elt.element_type_id -- element type
AND g_DATE_WORKED between elt.effective_start_date
and elt.effective_end_date
AND eltt.element_type_id = elt.element_type_id
AND eltt.language = userenv('LANG')
AND eltv.element_type_id = elt.element_type_id
AND g_DATE_WORKED between eltv.effective_start_date
and eltv.effective_end_date
AND eltv.hxt_processing_order < p_process_order -- ovt may exist already
AND hrw.seqno > l_min_detail_seqno
AND hrw.seqno < l_max_detail_seqno
ORDER BY eltv.hxt_processing_order;
SELECT aeiv.hxt_rotation_plan
,rts.tws_id
,hws.week_day
,hs.standard_start
,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;
UPDATE hxt_det_hours_worked_f hrw
SET rate_multiple = l_mult
WHERE hrw.rowid = hrw_rowid;
UPDATE hxt_det_hours_worked_f hrw
SET hourly_rate = l_rate
WHERE hrw.rowid = hrw_rowid;
/* INSERT into hxt_det_hours_worked_f
(ID,PARENT_ID,TIM_ID,
DATE_WORKED,ASSIGNMENT_ID,
HOURS,TIME_IN,TIME_OUT,
ELEMENT_TYPE_ID,FCL_EARN_REASON_CODE,
FFV_COST_CENTER_ID,
TAS_ID,LOCATION_ID,SHT_ID,
HRW_COMMENT,FFV_RATE_CODE_ID,
RATE_MULTIPLE,HOURLY_RATE,AMOUNT,
FCL_TAX_RULE_CODE,SEPARATE_CHECK_FLAG,
SEQNO,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PROJECT_ID,
PAY_STATUS,
PA_STATUS,
RETRO_BATCH_ID
--GROUP_ID
)
VALUES
(hxt_seqno.nextval, g_ID, g_TIM_ID,
g_DATE_WORKED, g_ASSIGNMENT_ID,
l_hours, -- SIR092
p_time_in, p_time_out,
elig_prem_rec.elt_premium_id, '',
l_ffv_cost_center_id,
g_TAS_ID, g_LOCATION_ID, g_SHT_ID,
g_HRW_COMMENT, g_FFV_RATE_CODE_ID,
decode(elig_prem_rec.hxt_premium_type, 'FACTOR',
NVL(g_RATE_MULTIPLE, elig_prem_rec.hxt_premium_amount),
g_RATE_MULTIPLE),
decode(elig_prem_rec.hxt_premium_type,
'RATE', NVL(g_HOURLY_RATE, elig_prem_rec.hxt_premium_amount),
g_HOURLY_RATE),
decode(elig_prem_rec.hxt_premium_type,
'FIXED', NVL(g_AMOUNT, elig_prem_rec.hxt_premium_amount),
g_AMOUNT),
g_FCL_TAX_RULE_CODE, g_SEPARATE_CHECK_FLAG,
l_seqno, g_CREATED_BY, g_CREATION_DATE,
g_LAST_UPDATED_BY, g_LAST_UPDATE_DATE,
g_LAST_UPDATE_LOGIN,
g_EFFECTIVE_START_DATE,
g_EFFECTIVE_END_DATE,
g_project_id,
l_pay_status,
l_pa_status,
l_retro_batch_id
--g_group_id
); */
/* Call dml to insert hours */
SELECT hxt_seqno.nextval,
decode(elig_prem_rec.hxt_premium_type, 'FACTOR',
NVL(g_RATE_MULTIPLE, elig_prem_rec.hxt_premium_amount),
g_RATE_MULTIPLE),
decode(elig_prem_rec.hxt_premium_type, 'RATE', NVL(g_HOURLY_RATE,
elig_prem_rec.hxt_premium_amount), g_HOURLY_RATE),
decode(elig_prem_rec.hxt_premium_type, 'FIXED', NVL(g_AMOUNT,
elig_prem_rec.hxt_premium_amount), g_AMOUNT)
INTO l_id,
l_rate_multiple,
l_hourly_rate,
l_amount
FROM dual;
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 => p_time_in,
p_time_out => p_time_out,
p_element_type_id => elig_prem_rec.elt_premium_id,
p_fcl_earn_reason_code => NULL,
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 => l_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);
SELECT nvl(max(seqno),0) next_seq
FROM hxt_det_hours_worked --C421
WHERE parent_id = g_id
AND tim_id = g_tim_id
AND date_worked = g_date_worked;
insert_hrs(pay_return_code,
new_record_id,
p_hours_to_pay,
p_time_in,
p_time_out,
p_pay_element_type_id,
next_seq,
location);