[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asg.payroll_id,
asg.assignment_number,
asg.assignment_id,
asg.business_group_id
FROM per_assignments_f asg
--HXT11i1 per_people_f per
WHERE asg.person_id = i_employee_id --HXT11i1
--HXT11i1 per.employee_number = i_employee_number
--HXT11i1 AND per.person_id = asg.person_id
AND asg.assignment_type = 'E' --HXT11i1
AND asg.primary_flag = 'Y' --HXT11i1
--HXT11i1 AND i_calculation_date between per.effective_start_date
--HXT11i1 and per.effective_end_date
AND i_calculation_date between asg.effective_start_date
and asg.effective_end_date;
SELECT pap.accrual_category,
pap.accrual_plan_id
FROM pay_accrual_plans pap
WHERE pap.accrual_plan_name=p_accrual_plan_name
AND pap.business_group_id=p_business_group_id;
SELECT PAP.ACCRUAL_PLAN_NAME
FROM PAY_ACCRUAL_PLANS PAP,
PAY_ELEMENT_TYPES_F PETF,
HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV,
PAY_NET_CALCULATION_RULES PNC,
PAY_INPUT_VALUES_F PIV
WHERE PETF.ELEMENT_TYPE_ID = p_element_type_id
AND PETF.ELEMENT_TYPE_ID = ELTV.ELEMENT_TYPE_ID
AND ELTV.hxt_earning_category = 'ABS'
AND ((PNC.ACCRUAL_PLAN_ID = PAP.ACCRUAL_PLAN_ID)
AND ( PNC.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID)
AND ( PIV.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID)
AND (P_DATE_WORKED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE))
AND p_date_worked BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND p_date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
AND ELTV.EFFECTIVE_END_DATE
AND EXISTS
(SELECT 1
FROM PAY_ELEMENT_TYPES_F PETF1,
PAY_ELEMENT_CLASSIFICATIONS PEC,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_LINKS_F PELF,
PAY_ACCRUAL_PLANS PAP1
WHERE
PEEF.ASSIGNMENT_ID = p_assignment_id
AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
-- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
AND p_date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
AND PETF1.EFFECTIVE_END_DATE
AND p_date_worked BETWEEN PEEF.EFFECTIVE_START_DATE -- Bug fix for st.date
AND PEEF.EFFECTIVE_END_DATE -- and end.date ...
AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID
AND PAP1.ACCRUAL_PLAN_NAME=PAP.ACCRUAL_PLAN_NAME);
SELECT hours
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,PAY_ACCRUAL_PLANS pap
,PAY_NET_CALCULATION_RULES net
,PAY_INPUT_VALUES_F piv
,hxt_sum_hours_worked sm
,per_assignments_f asm
WHERE elt.element_type_id = eltv.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND sm.date_worked BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND sm.date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
AND ELTV.EFFECTIVE_END_DATE
AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
AND sm.element_type_id = elt.element_type_id
AND asm.assignment_id = sm.assignment_id
AND sm.date_worked between asm.effective_start_date
AND asm.effective_end_date
AND sm.tim_id = p_tim_id
-- Begin ER180, to find accrual plan assigned for an emp.
AND PAP.ACCRUAL_PLAN_NAME IN
(SELECT PAP1.ACCRUAL_PLAN_NAME
FROM PAY_ELEMENT_TYPES_F PETF1,
PAY_ELEMENT_CLASSIFICATIONS PEC,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_LINKS_F PELF,
PAY_ACCRUAL_PLANS PAP1
WHERE PEEF.ASSIGNMENT_ID = sm.assignment_id
AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
-- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
AND sm.date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
AND PETF1.EFFECTIVE_END_DATE
AND sm.date_worked BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE
AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID);
SELECT distinct pap.accrual_plan_name
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,PAY_ACCRUAL_PLANS pap
,PAY_NET_CALCULATION_RULES net
,PAY_INPUT_VALUES_F piv
,hxt_sum_hours_worked sm
,per_assignments_f asm
,per_people_f ppl -- ER180 Bug Fix
WHERE elt.element_type_id = eltv.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND sm.date_worked = p_date_worked
AND sm.date_worked BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND sm.date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
AND ELTV.EFFECTIVE_END_DATE
AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
AND sm.element_type_id = elt.element_type_id
AND asm.assignment_id = sm.assignment_id
AND sm.tim_id = p_tim_id
AND sm.date_worked between asm.effective_start_date
and asm.effective_end_date
AND asm.person_id = ppl.person_id
-- HXT11i1AND ppl.employee_number = p_empl_number -- ER180 Bug Fix
AND ppl.person_id = p_empl_id -- HXT11i1
AND sm.date_worked between ppl.effective_start_date-- ER180 Bug Fix
and ppl.effective_end_date -- ER180 Bug Fix
-- Begin ER180, to find accrual plan assigned for an emp
AND PAP.ACCRUAL_PLAN_NAME IN
(SELECT PAP1.ACCRUAL_PLAN_NAME
FROM PAY_ELEMENT_TYPES_F PETF1
,PAY_ELEMENT_CLASSIFICATIONS PEC
,PAY_ELEMENT_ENTRIES_F PEEF
,PAY_ELEMENT_LINKS_F PELF
,PAY_ACCRUAL_PLANS PAP1
WHERE PEEF.ASSIGNMENT_ID = sm.assignment_id
AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
-- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
AND sm.date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
AND PETF1.EFFECTIVE_END_DATE
AND sm.date_worked BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE
AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID);
SELECT sum(sm.hours*(-1)*(net.add_or_subtract))
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,PAY_ACCRUAL_PLANS pap
,PAY_NET_CALCULATION_RULES net
,PAY_INPUT_VALUES_F piv
,hxt_sum_hours_worked sm
,per_assignments_f asm
WHERE elt.element_type_id = eltv.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
AND pap.accrual_plan_name = p_accrual_plan_name
AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
AND sm.element_type_id = elt.element_type_id
AND asm.assignment_id = sm.assignment_id
AND sm.date_worked between asm.effective_start_date
and asm.effective_end_date
AND sm.date_worked between piv.effective_start_date
and piv.effective_end_date
AND sm.tim_id = p_tim_id;
select max(batch_id) from pay_batch_headers pbh
where pbh.batch_status='T'
and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
where tim_id=p_tim_id);
select batch_id from pay_batch_headers pbh
where pbh.batch_status='T'
and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
where tim_id=p_tim_id);
select null from pay_batch_headers pbh
where pbh.batch_status='T'
and pbh.batch_id in (select distinct batch_id from hxt_timecards_f
where id=p_tim_id);
SELECT nvl(sum(det.hours*(-1)*(net.add_or_subtract)),0)
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,PAY_ACCRUAL_PLANS pap
,PAY_NET_CALCULATION_RULES net
,PAY_INPUT_VALUES_F piv
,hxt_det_hours_worked_f det
,per_assignments_f asm
WHERE elt.element_type_id = eltv.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
AND pap.accrual_plan_name = p_accrual_plan_name
AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
AND det.element_type_id = elt.element_type_id
AND asm.assignment_id = det.assignment_id
AND det.date_worked between asm.effective_start_date
and asm.effective_end_date
AND det.date_worked between piv.effective_start_date
and piv.effective_end_date
AND det.tim_id = p_tim_id
and det.retro_batch_id=p_batch_id;
SELECT nvl(sum(det.hours*(-1)*(net.add_or_subtract)),0)
FROM hxt_pay_element_types_f_ddf_v eltv
,pay_element_types_f elt
,PAY_ACCRUAL_PLANS pap
,PAY_NET_CALCULATION_RULES net
,PAY_INPUT_VALUES_F piv
,hxt_det_hours_worked_f det
,per_assignments_f asm
WHERE elt.element_type_id = eltv.element_type_id
AND eltv.hxt_earning_category = 'ABS'
AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
AND pap.accrual_plan_name = p_accrual_plan_name
AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
AND det.element_type_id = elt.element_type_id
AND asm.assignment_id = det.assignment_id
AND det.date_worked between asm.effective_start_date
and asm.effective_end_date
AND det.date_worked between piv.effective_start_date
and piv.effective_end_date
AND det.tim_id = p_tim_id
and det.retro_batch_id is null;
SELECT date_worked
FROM hxt_det_hours_worked_f det,
hxt_pay_element_types_f_ddf_v eltv
WHERE det.tim_id=p_tim_id
AND eltv.hxt_earning_category = 'ABS'
AND det.element_type_id = eltv.element_type_id
ORDER BY det.date_worked;