[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tim.id,
tim.batch_id,
tim.time_period_id,
tim.rowid,
tim.effective_start_date,
tim.effective_end_date,
ptp.end_date,
ptpt.number_per_fiscal_year,
ppbh.batch_status,
ppf.employee_number
FROM hxt_timecards_x tim,
per_time_periods ptp,
per_time_period_types ptpt,
pay_batch_headers ppbh,
per_people_f ppf
WHERE tim.payroll_id = c_payroll_id
AND ptp.time_period_id BETWEEN NVL(c_time_period_id,1)
AND NVL(c_time_period_id,999999999)
AND tim.time_period_id = ptp.time_period_id
AND ptp.period_type = ptpt.period_type
AND tim.batch_id = ppbh.batch_id
AND ppbh.batch_status <> 'U'
AND ppf.person_id = tim.for_person_id
AND ppf.effective_start_date =
(select MAX (ppf2.effective_start_date) -- to handle mid pay period changes
from per_people_f ppf2
where ppf2.person_id = tim.for_person_id
and ppf2.effective_end_date >= ptp.START_date
and ppf2.effective_start_date <= ptp.END_date)
AND EXISTS (SELECT 'Y'
FROM hxt_det_hours_worked_x det
WHERE det.tim_id = tim.id
AND det.pa_status = 'P');
HXT_UTIL.DEBUG('Selecting Cursor'); --DEBUG ONLY --HXT115
DELETE
FROM hxt_errors_f errf
WHERE errf.tim_id = l_timecard_rec.id
AND (errf.location LIKE 'hxt_tran%'
OR errf.location LIKE 'hxt_pa_user_exits.p_a_%');
Cursor to select all detail rows associated with the timecard
that are not associated with Effective Wage Rate calculations.
Each hourly detail row will be passed to Project Accounting.
*********************************************************************/
CURSOR l_non_cur(c_timecard_id NUMBER)IS
SELECT NVL(pro.proposed_salary_N,0) proposed_salary,
ppb.pay_basis,
fcl2.meaning emp_cat_description,
payd.hxt_earning_category,
payd.hxt_premium_type,
NVL(payd.hxt_premium_amount,0) hxt_premium_amount,
shw.id sum_id,
shw.effective_start_date,
shw.effective_end_date,
det.id,
det.hours,
det.amount,
det.date_worked,
det.effective_start_date det_effective_start,
det.effective_end_date det_effective_end,
det.hourly_rate,
det.rate_multiple,
det.tas_id,
det.ffv_cost_center_id,
det.job_id,
prj.project_id,
prj.name project_name,
prj.segment1,
task.task_number,
task.task_name,
org.name,
payt.element_name,
asg.organization_id,
det.element_type_id,
asg.assignment_id,
asg.assignment_number
FROM hxt_det_hours_worked_x det,
hxt_sum_hours_worked_x shw,
per_assignments_f asg,
per_pay_bases ppb,
per_pay_proposals pro,
hr_organization_units_v org,
hr_lookups fcl,
hr_lookups fcl2,
pay_element_types_f pay,
pay_element_types_f_tl payt,
hxt_pay_element_types_f_ddf_v payd,
pa_projects_all prj,
pa_tasks_expend_v task
WHERE det.tim_id = c_timecard_id
AND det.parent_id = shw.id
AND det.pa_status = 'P'
AND det.assignment_id = asg.assignment_id
AND det.date_worked BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.organization_id = org.organization_id
AND pro.assignment_id = det.assignment_id
AND pro.approved = 'Y'
AND pro.change_date = (SELECT MAX(pro2.change_date)
FROM per_pay_proposals pro2
WHERE pro2.assignment_id = det.assignment_id
AND pro2.approved = 'Y'
AND det.date_worked >= pro2.change_date)
AND det.element_type_id = pay.element_type_id
AND payt.element_type_id = pay.element_type_id
AND payt.language = userenv('LANG')
AND pay.element_type_id = payd.element_type_id
AND det.date_worked BETWEEN pay.effective_start_date
AND pay.effective_end_date
AND det.date_worked BETWEEN payd.effective_start_date
AND payd.effective_end_date
AND payd.hxt_earning_category = fcl.lookup_code
AND fcl.lookup_type = 'HXT_EARNING_CATEGORY'
AND fcl.application_id = 808
AND asg.employment_category = fcl2.lookup_code
AND fcl2.lookup_type = 'EMP_CAT'
AND fcl2.application_id = 800
AND det.project_id = prj.project_id
AND det.tas_id = task.task_id(+);
UPDATE hxt_det_hours_worked_f detf
SET detf.pa_status = 'C'
WHERE detf.rowid = (SELECT det2.rowid
FROM hxt_det_hours_worked_x det2
WHERE l_non_rec.id = det2.id);