[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_batch_ref (l_batch_id NUMBER, a_reference_num VARCHAR2); --SIR71
SELECT ppl.person_id, ppl.last_name, ppl.first_name,
asm.assignment_id, asm.business_group_id,
asm.assignment_number, asm.time_normal_start normal_start,
asm.time_normal_finish normal_finish, asm.normal_hours -- delete C243 by BC
--, asmv.hxt_work_plan tws_id
,
NULL osp_id, NULL sdf_id, aeiv.hxt_rotation_plan rtp_id,
aeiv.hxt_earning_policy egp_id,
aeiv.hxt_hour_deduction_policy hdp_id,
aeiv.hxt_shift_differential_policy sdp_id
-- -- use the latest of ppd_start and asm_start
-- use the latest of ptp_start, asm_start, and aeiv_start
,
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
)
start_date -- use the earliest of ppd_end, asm_end, aeiv_end
,
LEAST (
ptp.end_date,
asm.effective_end_date,
aeiv.effective_end_date
) end_date,
tim.id tim_id, tim.batch_id, tim.auto_gen_flag,
egp.fcl_earn_type egp_type, egp.egt_id, egp.hcl_id, egp.pep_id,
egp.pip_id, ptp.ROWID ptp_rowid, egp.effective_start_date,
egp.effective_end_date
FROM per_time_periods ptp,
hxt_timecards tim,
per_people_f ppl,
hxt_earning_policies egp,
hxt_per_aei_ddf_v aeiv,
per_assignment_status_types ast,
per_assignments_f asm
WHERE ptp.payroll_id = asm.payroll_id
AND ast.assignment_status_type_id = asm.assignment_status_type_id
AND ast.pay_system_status = 'P' -- Check payroll status
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND aeiv.assignment_id = asm.assignment_id
AND aeiv.effective_start_date <= ptp.end_date
AND aeiv.effective_end_date >= ptp.start_date
AND aeiv.hxt_autogen_hours_yn = 'Y'
AND egp.id(+) = aeiv.hxt_earning_policy
-- get policy valid for this time frame
AND ( ( egp.effective_start_date <=
LEAST (
ptp.end_date,
asm.effective_end_date,
aeiv.effective_end_date
)
AND egp.effective_end_date >=
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
)
AND egp.id IS NOT NULL
)
OR egp.id IS NULL
)
AND ppl.person_id = asm.person_id
-- use ptp.end_date - may be hired after start_date --
AND ptp.end_date BETWEEN ppl.effective_start_date
AND ppl.effective_end_date
AND tim.for_person_id(+) = asm.person_id
AND tim.time_period_id(+) = a_time_period_id
-- ignore timecards that have been manually entered, we will report to the user on these later
-- get all assignments valid sometime during pay period --
AND asm.effective_start_date <= ptp.end_date
AND asm.effective_end_date >= ptp.start_date
AND ptp.time_period_id = a_time_period_id
AND ptp.payroll_id = a_payroll_id
AND not exists -- added 2772781
( -- added 2772781
select '1' from HXT_BATCH_STATES -- added 2772781
where (hxt_batch_states.STATUS ='VT' and hxt_batch_states.batch_id=tim.batch_id) -- added 2772781
) -- added 2772781
ORDER BY tim.batch_id,
ppl.person_id,
asm.assignment_number,
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
);
SELECT ppl.person_id, ppl.last_name, ppl.first_name,
asm.assignment_id, asm.business_group_id,
asm.assignment_number, asm.time_normal_start normal_start,
asm.time_normal_finish normal_finish, asm.normal_hours -- delete C243 by BC
--, asmv.hxt_work_plan tws_id
,
NULL osp_id, NULL sdf_id, aeiv.hxt_rotation_plan rtp_id,
aeiv.hxt_earning_policy egp_id,
aeiv.hxt_hour_deduction_policy hdp_id,
aeiv.hxt_shift_differential_policy sdp_id,
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
)
start_date -- use the earliest of ppd_end, asm_end, aeiv_end
,
LEAST (
ptp.end_date,
asm.effective_end_date,
aeiv.effective_end_date
) end_date,
tim.id tim_id, tim.batch_id, tim.auto_gen_flag,
egp.fcl_earn_type egp_type, egp.egt_id, egp.hcl_id, egp.pep_id,
egp.pip_id, ptp.ROWID ptp_rowid, egp.effective_start_date,
egp.effective_end_date
FROM per_time_periods ptp,
hxt_timecards_f tim,
per_people_f ppl,
hxt_earning_policies egp,
hxt_per_aei_ddf_v aeiv,
per_assignment_status_types ast,
per_assignments_f asm
WHERE ptp.payroll_id = asm.payroll_id
AND session_date BETWEEN tim.effective_start_date(+)
AND tim.effective_end_date(+)
AND ast.assignment_status_type_id = asm.assignment_status_type_id
AND ast.pay_system_status = 'P' -- Check payroll status
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND aeiv.assignment_id = asm.assignment_id
AND aeiv.effective_start_date <= ptp.end_date
AND aeiv.effective_end_date >= ptp.start_date
AND aeiv.hxt_autogen_hours_yn = 'Y'
AND egp.id(+) = aeiv.hxt_earning_policy
-- get policy valid for this time frame
AND ( ( egp.effective_start_date <=
LEAST (
ptp.end_date,
asm.effective_end_date,
aeiv.effective_end_date
)
AND egp.effective_end_date >=
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
)
AND egp.id IS NOT NULL
)
OR egp.id IS NULL
)
AND ppl.person_id = asm.person_id
-- use ptp.end_date - may be hired after start_date --
AND ptp.end_date BETWEEN ppl.effective_start_date
AND ppl.effective_end_date
AND tim.for_person_id(+) = asm.person_id
AND tim.time_period_id(+) = a_time_period_id
AND asm.effective_start_date <= ptp.end_date
AND asm.effective_end_date >= ptp.start_date
AND ptp.time_period_id = a_time_period_id
AND ptp.payroll_id = a_payroll_id
AND not exists -- added 2772781
( -- added 2772781
select '1' from HXT_BATCH_STATES -- added 2772781
where (hxt_batch_states.STATUS ='VT' and hxt_batch_states.batch_id=tim.batch_id) -- added 2772781
) -- added 2772781
ORDER BY tim.batch_id,
ppl.person_id,
asm.assignment_number,
GREATEST (
ptp.start_date,
asm.effective_start_date,
aeiv.effective_start_date
);
g_sub_loc := 'Update_Batch_Ref';
update_batch_ref (l_batch_id, l_reference_number);
DELETE FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID');
DELETE FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID');
DELETE FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID');
SELECT wsh.off_shift_prem_id, wsh.shift_diff_ovrrd_id,
sht.standard_start, sht.standard_stop, sht.early_start,
sht.late_stop, sht.hours
FROM hxt_shifts sht,
hxt_weekly_work_schedules wws,
hxt_work_shifts wsh
WHERE wsh.week_day = hxt_util.get_week_day(a_date)
AND wws.id = wsh.tws_id
AND a_date BETWEEN wws.date_from AND NVL (wws.date_to, a_date)
AND wws.id = a_work_id
AND sht.id = wsh.sht_id;
SELECT rt1.tws_id,
-- Use the latest of rotation plan start dates or assignment start date
TRUNC (
DECODE (
SIGN ( rt1.start_date
- c_start),
-1, c_start,
rt1.start_date
)
) start_date,
-- Use the earliest of rotation plan end dates or assignment end date
NVL (
TRUNC (
DECODE (
SIGN ( MIN ( rt2.start_date
- 1)
- c_end),
-1, MIN ( rt2.start_date
- 1),
c_end
)
),
hr_general.end_of_time
) end_date
FROM hxt_rotation_schedules rt1, hxt_rotation_schedules rt2
WHERE rt1.rtp_id = rt2.rtp_id(+)
AND rt2.start_date(+) > rt1.start_date
AND rt1.rtp_id = c_rtp_id
AND c_end >= rt1.start_date
GROUP BY rt1.tws_id, rt1.start_date
HAVING c_start <=
NVL ( MIN (rt2.start_date)
- 1, hr_general.end_of_time)
ORDER BY rt1.start_date;
g_sub_loc := 'INSERT INTO pay_batch_headers';
--update the batch name
pay_batch_element_entry_api.update_batch_header (
p_session_date=> g_sysdatetime,
p_batch_id=> l_batch_id,
p_object_version_number=> l_object_version_number,
p_action_if_exists=> 'I',
p_batch_name=> l_batch_name,
p_batch_reference=> a_reference_num,
p_batch_source=> 'OTM',
p_batch_status=> 'U',
p_purge_after_transfer=> 'N',
p_reject_if_future_changes=> 'N'
);
/* INSERT INTO pay_batch_headers
(batch_id, business_group_id, batch_name, batch_status,
action_if_exists, batch_reference, batch_source,
purge_after_transfer, reject_if_future_changes,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
VALUES (l_batch_id, g_bus_group_id, l_batch_name, 'U',
'I', a_reference_num, 'OTM',
'N', 'N',
g_user_id, g_sysdatetime, g_user_id,
g_sysdatetime, g_login_id);*/
PROCEDURE update_batch_ref (l_batch_id NUMBER, a_reference_num VARCHAR2)
IS
CURSOR c_ovn is
Select object_version_number
From pay_batch_headers
Where batch_id = l_batch_id;
pay_batch_element_entry_api.update_batch_header (
p_session_date => g_sysdatetime,
p_batch_id=> l_batch_id,
p_batch_reference=> a_reference_num,
p_object_version_number => l_object_version_number
);
END update_batch_ref;
g_sub_loc := 'INSERT into hxt_timecards';
INSERT INTO hxt_timecards_f
(id, for_person_id, payroll_id,
time_period_id, batch_id, auto_gen_flag, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login, effective_start_date,
effective_end_date)
VALUES (l_tim_id, g_asm_rec.person_id, g_payroll_id,
g_time_period_id, a_batch_id, 'A', g_user_id,
g_sysdatetime, g_user_id, g_sysdatetime,
g_login_id, g_sysdate,
hr_general.end_of_time);
g_sub_loc := 'INSERT into hxt_hours_worked';
INSERT INTO hxt_sum_hours_worked_f
(id, tim_id, date_worked, seqno,
hours, assignment_id, element_type_id, time_in, time_out
-- , created_by
-- , creation_date
-- , last_updated_by
-- , last_update_date
-- , last_update_login
,
effective_start_date, effective_end_date, earn_pol_id)
-- , group_id)
VALUES (l_hrw_id, g_asm_rec.tim_id, a_date_worked, l_seqno,
l_hours, a_assignment_id, l_elt_id, l_time_in, l_time_out
-- , g_user_id
-- , g_sysdatetime
-- , g_user_id
-- , g_sysdatetime
-- , g_login_id
,
g_sysdate, hr_general.end_of_time, g_asm_rec.egp_id);
SELECT ROWID
INTO l_rowid
FROM hxt_sum_hours_worked_f
WHERE id = l_hrw_id;
g_user_id -- LAST_UPDATED_BY
,
g_sysdatetime -- LAST_UPDATE_DATE
,
g_login_id -- LAST_UPDATE_LOGIN
,
a_start -- START DATE SPR C389
,
l_rowid,
g_sysdate,
hr_general.end_of_time,
NULL -- PROJACCT Project_id
,
NULL -- TA35 Job_id
,
'P' -- RETROPAY Pay_Status
,
'P' -- PROJACCT PA_Status
,
NULL -- RETROPAY Retro_Batch_Id
,
'CORRECTION' -- RETROPAY DT_UPDATE_MODE
-- , l_group_id -- HXT11i1
);
SELECT hxt_seqno.NEXTVAL
FROM DUAL;
SELECT pay_batch_headers_s.NEXTVAL
INTO l_nextval
FROM DUAL; --SPR C166 BY BC
SELECT id FROM hxt_timecards_f tim
WHERE tim.auto_gen_flag = 'A'
AND tim.time_period_id = g_time_period_id
AND exists -- added 2772781
(
select '1' from HXT_BATCH_STATES -- added 2772781
where (hxt_batch_states.STATUS <>'VT' and hxt_batch_states.batch_id=tim.batch_id) -- added 2772781
OR tim.batch_id is null -- added 2772781
)
AND NOT EXISTS (
SELECT 'x'
FROM per_time_periods ptp,
hxt_per_aei_ddf_v aeiv,
per_assignments_f asm
WHERE asm.person_id = tim.for_person_id
AND ptp.start_date
BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND aeiv.assignment_id = asm.assignment_id --ORACLE
AND ptp.start_date
BETWEEN aeiv.effective_start_date
AND aeiv.effective_end_date
AND aeiv.hxt_autogen_hours_yn = 'Y' --ORACLE
AND ptp.time_period_id = tim.time_period_id);
g_sub_loc := 'DELETE from hxt_timecards';
DELETE FROM hxt_det_hours_worked_f
WHERE tim_id = l_record.id;
DELETE FROM hxt_sum_hours_worked_f
WHERE tim_id = l_record.id;
DELETE FROM hxt_timecards_f
WHERE id = l_record.id;
SELECT auto_gen_flag, for_person_id
FROM hxt_timecards_f tim
WHERE tim.auto_gen_flag <> 'A'
AND tim.for_person_id = a_person_id
AND tim.time_period_id = a_time_period_id
AND tim.payroll_id = a_payroll_id
-- MV: 03-DEC-2002
-- I did some investigation with regards to this query and I do not see why
-- we need the exists statement in this query. It checks if the autogen flag
-- is set for the assignment, but it will always be set because the driving query
-- g_cur_asm already has this check.
-- For now we leave it in because from experience we know that drastic changes
-- in OTM like this have a knock on effect on other code in OTM which is dificult
-- to spot. We only change the statement to be in line with the driving cursor
-- so it can handle mid period hiring and firing
-- Again I do not think that this is the correct solution because it will
-- return all assignment records within a payroll period, this could be
-- multiple records, one for every datetrack update on the assignment or aei
-- so there might still be an issue when an assignment switches from autogen
-- to no-autogen mid-period. We will need to look at this later.
AND EXISTS ( SELECT 'x'
FROM per_time_periods ptp,
hxt_per_aei_ddf_v asmv,
per_assignments_f asm
WHERE asm.person_id = tim.for_person_id
-- next 2 lines copied from g_cur_asm
AND asm.effective_start_date <= ptp.end_date
AND asm.effective_end_date >= ptp.start_date
/* AND ptp.start_date BETWEEN asm.effective_start_date
AND asm.effective_end_date */
AND asmv.assignment_id = asm.assignment_id
-- next 2 lines copied from g_cur_asm
AND asmv.effective_start_date <= ptp.end_date
AND asmv.effective_end_date >= ptp.start_date
/* AND ptp.start_date BETWEEN asmv.effective_start_date
AND asmv.effective_end_date */
AND asmv.hxt_autogen_hours_yn = 'Y'
AND ptp.time_period_id = tim.time_period_id);
g_sub_loc := 'DELETE from hxt_det_hours_worked';
DELETE FROM hxt_det_hours_worked_f --C421
WHERE tim_id = a_tim_id;
DELETE FROM hxt_sum_hours_worked
WHERE tim_id = a_tim_id; --C421
DELETE FROM hxt_sum_hours_worked_f
WHERE tim_id = a_tim_id
AND g_gen_session_date BETWEEN effective_start_date
AND effective_end_date; --C421
DELETE FROM hxt_errors
WHERE tim_id = a_tim_id
AND ( location LIKE 'Autogen%'
OR location LIKE 'AUTO%'
); --C336
SELECT DECODE (hdy.hours, NULL, NULL, hcl.element_type_id), hdy.hours -- SPR C332 by BC
FROM hxt_holiday_calendars hcl, hxt_holiday_days hdy
WHERE hdy.holiday_date = c_date
AND hcl.id = hdy.hcl_id
AND c_date BETWEEN hcl.effective_start_date
AND hcl.effective_end_date
AND hcl.id = c_hcl_id;
SELECT HXT_GROUP_ID_S.NEXTVAL
FROM SYS.DUAL;
SELECT '1'
FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID')
AND effective_date < TRUNC ( SYSDATE
- 1);
SELECT '1'
FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID')
AND TRUNC (effective_date) BETWEEN TRUNC ( SYSDATE
- 1)
AND SYSDATE;
DELETE FROM fnd_sessions
WHERE session_id = USERENV ('SESSIONID')
AND effective_date < TRUNC ( SYSDATE
- 1);
INSERT INTO fnd_sessions
(session_id, effective_date)
VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));