[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT ptp.start_date,
ptp.end_date,
tim.for_person_id
FROM hxt_timecards_f tim,
per_time_periods ptp
WHERE tim.id = p_id
AND tim.time_period_id = ptp.time_period_id ;
o_dt_update_mod OUT NOCOPY VARCHAR2,
o_error_message OUT NOCOPY VARCHAR2,
o_return_code OUT NOCOPY NUMBER,
p_parent_id IN hxt_det_hours_worked_f.parent_id%TYPE,
p_do_intg_check IN VARCHAR2
)
IS
-- Bug 7359347
-- Changed the below cursor to pick up session date from global variable
-- instead of fnd_sessions table.
/*
CURSOR csr_not_transferred (
v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
)
IS
SELECT 1
FROM hxt_det_hours_worked hdhw
WHERE hdhw.tim_id = v_tim_id
AND hdhw.pay_status = 'P' -- Bug 6067007
AND hdhw.pa_status = 'P' -- Bug 6067007
AND NOT EXISTS ( SELECT 1
FROM hxt_det_hours_worked_f hdhw2
WHERE hdhw.tim_id = hdhw2.tim_id
AND hdhw2.effective_start_date >
hdhw.effective_start_date);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND v_session_date BETWEEN hdhw.effective_start_date
AND hdhw.effective_end_date
AND hdhw.pay_status = 'P' -- Bug 6067007
AND hdhw.pa_status = 'P' -- Bug 6067007
AND NOT EXISTS ( SELECT 1
FROM hxt_det_hours_worked_f hdhw2
WHERE hdhw.tim_id = hdhw2.tim_id
AND hdhw2.effective_start_date >
hdhw.effective_start_date);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND ( hdhw.pay_status = 'R'
OR hdhw.pa_status = 'R'
);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND ( hdhw.pay_status = 'C'
OR hdhw.pa_status = 'C'
);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND trunc(hdhw.effective_start_date)
= trunc(v_session_dt);
SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
hdhw.effective_start_date
FROM hxt_det_hours_worked hdhw
WHERE hdhw.tim_id = v_tim_id;
SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
hdhw.effective_start_date
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND v_sess_date BETWEEN hdhw.effective_start_date
AND hdhw.effective_end_date;
o_dt_update_mod := g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode;
o_dt_update_mod := 'CORRECTION';
THEN -- TC undergoing RETRO now, only UPDATE allowed.
if g_debug then
hr_utility.set_location ('Do UPDATE', 70);
o_dt_update_mod := 'UPDATE';
THEN -- Was it already updated today?
OPEN csr_changed_today (p_tim_id, p_parent_id, p_session_date);
THEN -- DT will not allow an update so error
o_return_code := 1; -- this means that an error should be raised
o_dt_update_mod := NULL;
ELSE -- We can allow an DT UPDATE because that will keep the history
if g_debug then
hr_utility.set_location ('Do UPDATE', 70);
o_dt_update_mod := 'UPDATE';
ELSE -- We can allow an DT UPDATE because that will keep the history
if g_debug then
hr_utility.set_location ('Do UPDATE', 80);
o_dt_update_mod := 'UPDATE';
o_dt_update_mod := 'CORRECTION';
g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode := o_dt_update_mod ;
o_dt_update_mod OUT NOCOPY VARCHAR2,
o_error_message OUT NOCOPY VARCHAR2,
o_return_code OUT NOCOPY NUMBER,
p_parent_id IN hxt_det_hours_worked_f.parent_id%TYPE
)
IS
-- Bug 7359347
-- Changed the below cursor to pick up session date from global variable
-- instead of fnd_sessions table.
/*
CURSOR csr_not_transferred (
v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
)
IS
SELECT 1
FROM hxt_det_hours_worked hdhw
WHERE hdhw.tim_id = v_tim_id
AND hdhw.pay_status = 'P' -- Bug 6067007
AND hdhw.pa_status = 'P' -- Bug 6067007
AND NOT EXISTS ( SELECT 1
FROM hxt_det_hours_worked_f hdhw2
WHERE hdhw.tim_id = hdhw2.tim_id
AND hdhw2.effective_start_date >
hdhw.effective_start_date);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND v_session_date BETWEEN hdhw.effective_start_date
AND hdhw.effective_end_date
AND hdhw.pay_status = 'P' -- Bug 6067007
AND hdhw.pa_status = 'P' -- Bug 6067007
AND NOT EXISTS ( SELECT 1
FROM hxt_det_hours_worked_f hdhw2
WHERE hdhw.tim_id = hdhw2.tim_id
AND hdhw2.effective_start_date >
hdhw.effective_start_date);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND ( hdhw.pay_status = 'R'
OR hdhw.pa_status = 'R'
);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND ( hdhw.pay_status = 'C'
OR hdhw.pa_status = 'C'
);
SELECT 1
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND trunc(hdhw.effective_start_date)
= trunc(v_session_dt);
SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
hdhw.effective_start_date
FROM hxt_det_hours_worked hdhw
WHERE hdhw.tim_id = v_tim_id;
SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
hdhw.effective_start_date
FROM hxt_det_hours_worked_f hdhw
WHERE hdhw.tim_id = v_tim_id
AND v_sess_date BETWEEN hdhw.effective_start_date
AND hdhw.effective_end_date;
o_dt_update_mod := g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode;
o_dt_update_mod := 'CORRECTION';
THEN -- TC undergoing RETRO now, only UPDATE allowed.
if g_debug then
hr_utility.set_location ('Do UPDATE', 70);
o_dt_update_mod := 'UPDATE';
THEN -- Was it already updated today?
OPEN csr_changed_today (p_tim_id, p_parent_id, p_session_date);
THEN -- DT will not allow an update so error
o_return_code := 1; -- this means that an error should be raised
o_dt_update_mod := NULL;
ELSE -- We can allow an DT UPDATE because that will keep the history
if g_debug then
hr_utility.set_location ('Do UPDATE', 70);
o_dt_update_mod := 'UPDATE';
ELSE -- We can allow an DT UPDATE because that will keep the history
if g_debug then
hr_utility.set_location ('Do UPDATE', 80);
o_dt_update_mod := 'UPDATE';
o_dt_update_mod := 'CORRECTION';
g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode := o_dt_update_mod ;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked hrw, --C421
hxt_timecards tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
-- WHERE tim.id = a_tim_id
-- AND hrw.tim_id = a_tim_id
-- Changed the above where clause as follows for supporting the
-- OTLR Recurring Period Preference.
WHERE tim.for_person_id = a_for_person_id
AND hrw.tim_id = tim.id
-- This has been changed back to get total weekly hours between
-- start_day_of_week and (g_date_worked) as it was not calculating the totals
-- correctly when entering hours on more than one summary row for the same day
-- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
-- and (a_date_worked - 1)
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
-- only include earnings to be counted toward
-- hours to be worked before being eligible for overtime.
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date; */
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
WHERE tim.for_person_id = a_for_person_id
AND session_date between hrw.effective_start_date
and hrw.effective_end_Date
AND session_date between tim.effective_start_date
and tim.effective_end_Date
AND hrw.tim_id = tim.id
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
WHERE tim.id IN (p_tim_id, p_prev_id)
AND session_date between hrw.effective_start_date
and hrw.effective_end_Date
AND session_date between tim.effective_start_date
and tim.effective_end_Date
AND hrw.tim_id = tim.id
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked hrw, --C421
hxt_timecards tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
-- WHERE tim.id = a_tim_id
-- AND hrw.tim_id = a_tim_id
-- Changed the above where clause as follows for supporting the
-- OTLR Recurring Period Preference.
WHERE tim.for_person_id = a_for_person_id
AND hrw.tim_id = tim.id
-- This has been changed back to get total weekly hours between
-- start_day_of_week and (g_date_worked) as it was not calculating the totals
-- correctly when entering hours on more than one summary row for the same day
-- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
-- and (a_date_worked - 1)
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
- 1
-- only include earnings to be counted toward
-- hours to be worked before being eligible for overtime.
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked_f hrw, --C421
hxt_timecards_f tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
WHERE tim.for_person_id = a_for_person_id
AND hrw.tim_id = tim.id
AND session_date BETWEEN hrw.effective_start_date
AND hrw.effective_end_date
AND session_date BETWEEN tim.effective_start_date
AND tim.effective_end_date
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
- 1
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT NVL (SUM (hrw.hours), 0)
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim,
hxt_earn_groups erg,
hxt_earn_group_types egt,
hxt_earning_policies erp
WHERE tim.id IN (p_tim_id, p_prev_id)
AND session_date between hrw.effective_start_date
and hrw.effective_end_Date
AND session_date between tim.effective_start_date
and tim.effective_end_Date
AND hrw.tim_id = tim.id
AND hrw.date_worked BETWEEN NEXT_DAY (
a_date_worked
- 7,
a_start_day_of_week
)
AND a_date_worked
- 1
AND erp.id = a_ep_id
AND egt.id = erp.egt_id
AND erg.egt_id = egt.id
AND erg.element_type_id = hrw.element_type_id
AND hrw.date_worked BETWEEN erp.effective_start_date
AND erp.effective_end_date;
SELECT 1
INTO returned_element
FROM hxt_earn_group_types egt
WHERE egt.fcl_eg_type = 'INCLUDE'
AND a_date_worked BETWEEN egt.effective_start_date
AND egt.effective_end_date
AND egt.id = a_earn_group
AND EXISTS ( SELECT 'x'
FROM hxt_earn_groups egr
WHERE egr.egt_id = egt.id
AND egr.element_type_id = a_element_type);
SELECT 'C'
FROM hxt_sum_hours_worked_f hrwp
WHERE hrwp.id = a_hrw_id
AND session_date BETWEEN hrwp.effective_start_date
AND hrwp.effective_end_date
AND ( creation_date <> last_update_date
OR EXISTS ( SELECT '1'
FROM hxt_sum_hours_worked_f hrw
WHERE hrw.id = a_hrw_id
AND hrw.effective_start_date <>
hrwp.effective_start_date)
);
** Modify Load_Error_Status to select the Value stored in ERR_TYPE from **
** HXT_ERRORS rather then selecting a hard coded 'E' if the record exists. **
***************************************************************************/
FUNCTION load_error_status (a_hrw_id IN NUMBER)
RETURN VARCHAR2
IS
returned_status VARCHAR2 (03) := '';
SELECT err_type
FROM hxt_errors err
WHERE err.hrw_id = a_hrw_id;
SELECT petv.hxt_premium_amount
FROM hxt_pay_element_types_f_ddf_v petv
WHERE petv.hxt_earning_category = 'OTH'
AND petv.hxt_premium_type = 'FIXED'
AND petv.element_type_id = c_element_type_id
AND petv.effective_start_date <= p_effective_start_date
AND petv.effective_end_date >= p_effective_end_date;
SELECT pro.proposed_salary, ppb.pay_basis
FROM per_pay_proposals pro,
per_pay_bases ppb,
per_assignments_f asg
WHERE pro.assignment_id = p_assignment_id
AND ppb.pay_basis_id = asg.pay_basis_id
AND asg.assignment_id = pro.assignment_id
AND p_eff_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND pro.approved = 'Y'
AND pro.change_date = (SELECT MAX (pro2.change_date)
FROM per_pay_proposals pro2
WHERE pro2.assignment_id =
p_assignment_id
AND pro2.approved = 'Y'
AND p_eff_date >= pro2.change_date);
SELECT ptpt.number_per_fiscal_year
FROM per_time_periods ptp, per_time_period_types ptpt
WHERE p_ptp_id = ptp.time_period_id
AND ptp.period_type = ptpt.period_type;
* Select and return the value ERR_TYPE from the Table *
* HXT_ERRORS where the tim_id passed in = HXT_ERRORS.TIM_ID. *
* *
* Returns *
* returned_status - Value of ERR_TYPE where tim_id passed in *
* equals HXT_ERRORS.TIM_ID.0 *
* *
* Arguments *
* p_tim_id - Time Card ID *
* *
*****************************************************************/
FUNCTION load_tim_error_status (p_tim_id IN NUMBER)
RETURN VARCHAR2
IS
returned_status VARCHAR2 (3) := '';
SELECT err_type
FROM hxt_errors err
WHERE err.tim_id = p_tim_id
ORDER BY err_type; -- So that 'ERR' has precedence over 'WRN'
SELECT SUM (hours)
FROM hxt_sum_hours_worked
WHERE tim_id = p_tim_id -- AND GROUP_ID <> NVL(p_hrw_group_id, 0)
AND date_worked = p_date_worked;
SELECT SUM (hours)
FROM hxt_sum_hours_worked_f
WHERE tim_id = p_tim_id -- AND GROUP_ID <> NVL(p_hrw_group_id, 0)
AND date_worked = p_date_worked
AND g_td_session_date BETWEEN effective_start_date
AND effective_end_date;
SELECT hrw.time_in, hrw.time_out, hrw.hours, hrw.element_type_id,
hrw.date_worked, eltv.hxt_earning_category
FROM hxt_det_hours_worked hrw,
hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt
WHERE elt.element_type_id = hrw.element_type_id
AND eltv.hxt_earning_category IN ('REG', 'OVT', 'ABS')
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.tim_id = c_tim_id
AND hrw.time_out = c_current_time_in
AND hrw.time_in <> hrw.time_out; --FIX endless loop PWM 01/28/99 SIR282
SELECT hrw.time_in, hrw.time_out, hrw.hours, hrw.element_type_id,
hrw.date_worked, eltv.hxt_earning_category
FROM hxt_det_hours_worked_f hrw,
hxt_pay_element_types_f_ddf_v eltv,
pay_element_types_f elt
WHERE elt.element_type_id = hrw.element_type_id
AND eltv.hxt_earning_category IN ('REG', 'OVT', 'ABS')
AND c_session_date BETWEEN hrw.effective_start_date
AND hrw.effective_end_date
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND eltv.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.tim_id = c_tim_id
AND hrw.time_out = c_current_time_in
AND hrw.time_in <> hrw.time_out; --FIX endless loop PWM 01/28/99 SIR282