[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asm.payroll_id,
asm.assignment_id,
asm.business_group_id, --GLOBAL
asm.effective_start_date,
asm.effective_end_date,
aeiv.hxt_rotation_plan, --SIR344
egp.hcl_id
FROM hxt_earning_policies egp,
--ORACLE per_assignments_f_dfv asmv,
hxt_per_aei_ddf_v aeiv,--ORACLE
per_assignment_status_types ast,
per_assignments_f asm
WHERE c_person_id = asm.person_id
AND ast.assignment_status_type_id = asm.assignment_status_type_id
AND ast.pay_system_status = 'P' -- Check payroll status
--ORACLE AND asmv.row_id = asm.rowid
AND aeiv.assignment_id = asm.assignment_id --ORACLE
AND l_date_worked BETWEEN aeiv.effective_start_date /* GLOBAL */
AND aeiv.effective_end_date /* GLOBAL */
/*GLOBAL AND aeiv.information_type = 'HXT_ASS_INFO' --ORACLE */
--ORACLE AND egp.id(+) = asmv.hxt_earning_policy;
select business_group_id
from per_assignments_f asm
where asm.assignment_id = p_asg_id;
select 'Y'
from fnd_sessions
where session_id = userenv('sessionid')
and trunc(effective_date) = trunc(sysdate);
insert into fnd_sessions
(effective_date, session_id)
values (sysdate, userenv('sessionid'));
HXT_UTIL.DEBUG('Inserted a row in fnd session '); --HXT115
/*Insert hours to the hxt_hours_worked table and call generate details*/
l_retcode := record_hours_worked( FALSE,
l_timecard_id,
l_emp_rec.assignment_id,
l_person_id,
l_date_worked,
NULL,
l_hours,
start_time,
end_time,
l_start_date);
SELECT time_period_id,
start_date,
end_date
INTO o_time_period,
o_start_date,
o_end_date
FROM per_time_periods
WHERE payroll_id = i_payroll_id
AND TRUNC(i_date_worked) BETWEEN TRUNC(start_date) AND TRUNC(end_date);
SELECT id,
auto_gen_flag,
batch_id
INTO o_timecard_id,
o_auto_gen_flag,
g_batch_err_id
FROM hxt_timecards
WHERE for_person_id = i_person_id
AND time_period_id = i_time_period_id;
/* Insert new timecard info to hxt_timecards */
--SIR012 INSERT into hxt_timecards
INSERT into hxt_timecards_f --SIR012
( id,
for_person_id,
payroll_id,
time_period_id,
batch_id,
auto_gen_flag,
created_by,
creation_date
, effective_start_date --SIR012
, effective_end_date) --SIR012
VALUES
( l_timecard_id,
i_person_id,
i_payroll_id,
i_time_period_id,
l_batch_id,
'T',
g_user_id,
g_sysdate
, g_sysdate --SIR012
,hr_general.end_of_time); --SIR149 --FORMS60
pay_batch_element_entry_api.update_batch_header (
p_session_date => g_sysdate,
p_batch_id=> l_batch_id,
p_object_version_number=> l_object_version_number,
p_batch_name=> l_batch_name
);
SELECT MAX(bat.batch_id)
INTO o_batch_id
FROM pay_batch_headers bat
WHERE bat.batch_status = 'U'
AND bat.batch_reference like 'C_%'
AND not exists (SELECT 'x'
FROM hxt_timecards tim
WHERE tim.batch_id = bat.batch_id
AND (tim.time_period_id <> i_time_period_id
OR tim.payroll_id <> i_payroll_id))
AND l_max_batches > (SELECT COUNT(tim2.id)
FROM hxt_timecards tim2
WHERE tim2.batch_id = bat.batch_id);
SELECT hcl.element_type_id,
hdy.hours,
hdy.holiday_date
FROM hxt_holiday_calendars hcl,
hxt_holiday_days hdy
WHERE TRUNC(hdy.holiday_date,'DD') BETWEEN TRUNC(c_start_date,'DD') AND TRUNC(c_end_date,'DD')
AND hcl.id = hdy.hcl_id
AND hdy.holiday_date BETWEEN hcl.effective_start_date AND hcl.effective_end_date
AND hcl.id = c_hcl_id;
SELECT
aeiv.hxt_earning_policy,
aeiv.hxt_shift_differential_policy,
aeiv.hxt_hour_deduction_policy,
aeiv.hxt_rotation_plan,
egp.fcl_earn_type,
egp.egt_id,
egp.pep_id,
egp.pip_id
FROM hxt_earning_policies egp,
hxt_per_aei_ddf_v aeiv,
per_assignment_status_types past,
per_assignments_f paf
WHERE c_assignment_id = paf.assignment_id
AND c_date_worked BETWEEN paf.effective_start_date AND paf.effective_end_date
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND past.pay_system_status = 'P' -- Check payroll status
AND aeiv.assignment_id = paf.assignment_id
AND c_date_worked BETWEEN aeiv.effective_start_date
AND aeiv.effective_end_date
AND egp.id = aeiv.hxt_earning_policy
AND c_date_worked BETWEEN egp.effective_start_date
AND egp.effective_end_date;
SELECT hws.off_shift_prem_id, hws.shift_diff_ovrrd_id
FROM hxt_rotation_plans hrp,
hxt_rotation_schedules hrs,
hxt_weekly_work_schedules hwws,
hxt_work_shifts hws
WHERE hrp.id = c_hxt_rotation_plan
AND c_date_worked BETWEEN hrp.date_from
AND NVL (hrp.date_to, c_date_worked )
AND hrs.rtp_id = hrp.id
AND c_date_worked >= hrs.start_date
AND hwws.id = hrs.tws_id
AND c_date_worked BETWEEN hwws.date_from
AND NVL (hwws.date_to, c_date_worked)
AND hws.tws_id = hwws.id
AND hws.week_day = hxt_util.get_week_day(c_date_worked);
INSERT INTO hxt_sum_hours_worked_f --SIR012
( id,
tim_id,
-- C421 parent_id,
date_worked,
seqno,
hours,
assignment_id,
element_type_id,
time_in,
time_out,
created_by,
creation_date
, earn_pol_id --SIR012
, effective_start_date --SIR012
, effective_end_date) --SIR012
VALUES
( l_hours_worked_id,
i_timecard_id,
-- C421 0,
i_date_worked,
l_sequence_number,
i_hours,
i_assignment_id,
i_element_id,
i_start_time,
i_end_time,
g_user_id,
g_sysdate
, l_det_rec.hxt_earning_policy --SIR012
, g_sysdate --SIR012
, hr_general.end_of_time); --SIR149 --FORMS60
HXT_UTIL.DEBUG('Hours Worked Successfully inserted');--DEBUG ONLY --HXT115
select rowid --SIR012
into l_rowid --SIR012
from HXT_SUM_HOURS_WORKED --SIR012
where id = l_hours_worked_id; --SIR012
NULL, --last_updated_by
NULL, --last_update_date
NULL, --last_update_login
i_start_date,
l_rowid, --SIR012
trunc(sysdate), --SIR012
hr_general.end_of_time, --SIR149 --FORMS60
NULL, --SIR012
NULL, --SIR012
'P', --SIR012
'P', --SIR012
NULL, --SIR012
'C'); --SIR012
SELECT hxt_seqno.nextval
INTO l_error_seqno
FROM DUAL;