[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Cursor to select each timecard within
the payroll/time-period requested
****************************************/
CURSOR l_timecard_cur(c_payroll_id NUMBER, c_time_period_id NUMBER)IS
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 = 'R') ;
DELETE
FROM hxt_errors_f errf
WHERE errf.tim_id = l_timecard_rec.id
AND (errf.location LIKE 'hxt_retro_pa%'
OR errf.location LIKE 'hxt_pa_user_exits.p_a_%');
UPDATE hxt_det_hours_worked_f
SET pa_status = 'C'
WHERE rowid IN (SELECT d.rowid
FROM hxt_det_hours_worked_x d
WHERE d.pa_status = 'R'
AND d.tim_id = l_timecard_rec.id);
note: retro rows that have been updated with new retro rows by the user
will remain in a retro status permenantly becacuse of the effective_end_date
A - adjusted: this row had an adjusting entry sent to Project Accounting.
The adjustment was an hours or amount change only, representing
the difference between this previously sent detail/PA row and the new changes
B - backed out: the details related to this row that were previously sent to
Project Accouning had to be backed out nocopy (Insert negative hours to PA interface).
PA rows are backed out nocopy when newer (retro) entries have invalidated
them through changes to data vital to Project Accounting.
D - dead row: this row has been replaced by a newer(retro) detail row with identical information.
This can happen when the retro explosion process creates rows where data has not
changed. These newer retro rows are not sent to PA because an entry
already exists there representing the amount.
**************************************************************************************************/
FUNCTION retro_non_ewr_transfer(i_timecard_id IN NUMBER,
i_ending_date IN DATE,
i_annual_pay_periods IN NUMBER,
i_employee_number IN VARCHAR2,
o_location OUT NOCOPY VARCHAR2,
o_error_text OUT NOCOPY VARCHAR2,
o_system_text OUT NOCOPY VARCHAR2) RETURN NUMBER IS
/****************************************************************
Summary cursor to select each summmary row associated
with the timecard being processed.
****************************************************************/
CURSOR l_sum_cur(c_timecard_id NUMBER) IS
SELECT id,
effective_start_date,
effective_end_date
FROM hxt_sum_hours_worked_x
WHERE tim_id = c_timecard_id;
Cursor to select all detail rows associated with the timecard
that are not associated with Effective Wage Rate calculations.
Each retro detail row will be passed to Project Accounting.
*********************************************************************/
CURSOR l_non_cur(c_summary_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,
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.ffv_cost_center_id,
det.job_id,
det.tas_id,
det.project_id,
prj.name project_name,
prj.segment1,
task.task_number,
task.task_name,
org.name,
payt.element_name,
asg.organization_id,
asg.assignment_id,
asg.assignment_number,
det.parent_id,
det.element_type_id
FROM hxt_det_hours_worked_x det,
per_pay_proposals pro,
per_pay_bases ppb,
hr_lookups fcl,
hr_lookups fcl2,
per_assignments_f asg,
hr_organization_units_v org,
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 /*Bug 16391367*/
WHERE det.parent_id = c_summary_id
AND det.pa_status = 'R'
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 det.assignment_id = pro.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 payd.effective_start_date
AND payd.effective_end_date
AND det.date_worked BETWEEN pay.effective_start_date
AND pay.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(+);
Cursor to select any expired rows where only the hours have changed.
*************************************************************************/
CURSOR l_expired_cur(c_parent_id NUMBER,
c_project_id NUMBER,
c_task_id NUMBER,
c_element_type_id NUMBER,
c_hourly_rate NUMBER,
c_amount NUMBER,
c_ffv_cost_center_id NUMBER,
c_job_id NUMBER)IS
SELECT detf.hours,
detf.id,
detf.rowid row_id
FROM hxt_det_hours_worked_f detf
WHERE detf.parent_id = c_parent_id
AND detf.project_id = c_project_id
AND NVL(detf.tas_id,-1) = NVL(c_task_id,-1)
AND detf.element_type_id = c_element_type_id
AND NVL(detf.hourly_rate,-1) = NVL(c_hourly_rate,-1)
AND NVL(detf.amount,-1) = NVL(c_amount, -1)
AND detf.pa_status = 'C'
AND NVL(detf.ffv_cost_center_id,-1) = NVL(c_ffv_cost_center_id, -1)
AND NVL(detf.job_id,-1) = NVL(c_job_id, -1);
Cursor to select details rows for backout transactions.
Select all unprocessed rows 'C' that have been expired.
***********************************************************/
CURSOR l_backout_cur(c_summary_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,
det.rowid row_id,
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,
det.project_id,
prj.name project_name,
prj.segment1,
task.task_number,
task.task_name,
org.name,
payt.element_name, --FORMS60
asg.organization_id,
det.element_type_id, --SIR162
asg.assignment_id,
asg.assignment_number
FROM hxt_det_hours_worked_f det,
per_pay_proposals pro,
per_pay_bases ppb,
hr_lookups fcl, --FORMS60
hr_lookups fcl2, --FORMS60
per_assignments_f asg,
hr_organization_units_v org,
pay_element_types_f pay,
pay_element_types_f_tl payt, --FORMS60
hxt_pay_element_types_f_ddf_v payd,
pa_projects_all prj,
pa_tasks_expend_v task /*Bug 16391367*/
WHERE det.parent_id = c_summary_id
AND det.pa_status = 'C'
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 asg.assignment_id = pro.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 payt.element_type_id = pay.element_type_id
AND payt.language = userenv('LANG')
AND pay.element_type_id = payd.element_type_id
AND payd.element_type_id = det.element_type_id
AND det.date_worked BETWEEN payd.effective_start_date
AND payd.effective_end_date
AND det.date_worked BETWEEN pay.effective_start_date
AND pay.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(+)
AND det.effective_end_date < hr_general.end_of_time;
UPDATE hxt_det_hours_worked_f
SET pa_status = 'A'
WHERE rowid = l_expired_rec.row_id;
UPDATE hxt_det_hours_worked_f
SET pa_status = 'D'
WHERE rowid = l_expired_rec.row_id;
UPDATE hxt_det_hours_worked_f
SET pa_status = 'A'
WHERE rowid = l_expired_rec.row_id;
UPDATE hxt_det_hours_worked_f
SET pa_status = 'D'
WHERE rowid = l_expired_rec.row_id;
UPDATE hxt_det_hours_worked_f
SET pa_status = 'B'
WHERE rowid = l_backout_rec.row_id;