[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
p_batch_id => l_valid_batch_id,
p_object_version_number => l_object_version_number,
p_batch_name => l_valid_batch_name
); /* For updating the batch_name of the newly created batch */
UPDATE hxt_batch_states
SET status = 'VV'
WHERE batch_id = l_valid_batch_id; /* For updating the status of the newly created batch */
pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
p_batch_id => l_warning_batch_id,
p_object_version_number => l_object_version_number,
p_batch_name => l_warning_batch_name
); /* For updating the batch_name of the newly created batch */
UPDATE hxt_batch_states
SET status = 'VW'
WHERE batch_id = l_warning_batch_id; /* For updating the status of the newly created batch */
pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
p_batch_id => l_error_batch_id,
p_object_version_number => l_object_version_number,
p_batch_name => l_error_batch_name
); /* For updating the batch_name of the newly created batch */
UPDATE hxt_batch_states
SET status = 'VE'
WHERE batch_id = l_error_batch_id; /* For updating the status of the newly created batch */
HXT_DML.UPDATE_HXT_TIMECARDS (p_rowid => p_merge_batches(l_loop_index1).tc_rowid,
p_id => p_merge_batches(l_loop_index1).tc_id,
p_for_person_id => p_merge_batches(l_loop_index1).for_person_id,
p_time_period_id => p_merge_batches(l_loop_index1).time_period_id,
p_auto_gen_flag => p_merge_batches(l_loop_index1).auto_gen_flag,
p_batch_id => l_temp_batch_upd_id,
p_approv_person_id => p_merge_batches(l_loop_index1).approv_person_id,
p_approved_timestamp => p_merge_batches(l_loop_index1).approved_timestamp,
p_created_by => p_merge_batches(l_loop_index1).created_by,
p_creation_date => p_merge_batches(l_loop_index1).creation_date,
p_last_updated_by => p_merge_batches(l_loop_index1).last_updated_by,
p_last_update_date => p_merge_batches(l_loop_index1).last_update_date,
p_last_update_login => p_merge_batches(l_loop_index1).last_update_login,
p_payroll_id => p_merge_batches(l_loop_index1).payroll_id,
p_status => p_merge_batches(l_loop_index1).status,
p_effective_start_date => p_merge_batches(l_loop_index1).effective_start_date,
p_effective_end_date => p_merge_batches(l_loop_index1).effective_end_date,
p_object_version_number => l_ovn
);
UPDATE hxt_det_hours_worked_f
SET retro_batch_id = l_temp_batch_upd_id,
object_version_number = object_version_number + 1
WHERE retro_batch_id = p_merge_batches(l_loop_index1).batch_id
AND tim_id = p_merge_batches(l_loop_index1).tc_id;
SELECT assignment_number
INTO l_assignment_no
FROM per_all_assignments_f
WHERE person_id = (SELECT for_person_id
FROM hxt_timecards_x
WHERE id = p_merge_batches(l_loop_index1).tc_id
)
AND sysdate between effective_start_date and effective_end_date;
SELECT pbh.batch_name
INTO l_parent_batch_name
FROM pay_batch_headers pbh
WHERE pbh.batch_id = (SELECT tc.batch_id
FROM hxt_timecards_x tc
WHERE tc.id = p_merge_batches(l_loop_index1).tc_id
);
LOOP /* To loop through empty batches left behind and delete them */
EXIT WHEN NOT p_del_empty_batches.exists(l_loop_index2);
pay_batch_element_entry_api.delete_batch_header (p_batch_id => p_del_empty_batches(l_loop_index2).batch_id,
p_object_version_number => p_del_empty_batches(l_loop_index2).batch_ovn
);
DELETE FROM hxt_batch_states
WHERE batch_id = p_del_empty_batches(l_loop_index2).batch_id;
SELECT pbh.batch_id,
hbs.status batch_status, --SIR020
pbh.batch_reference ,
pbh.object_version_number
FROM pay_batch_headers pbh, --GLOBAL
hxt_batch_states hbs --SIR020
WHERE pbh.business_group_id = p_bus_group_id --GLOBAL
AND hbs.batch_id = pbh.batch_id --SIR020
AND pbh.batch_id BETWEEN nvl(c_batch_num,0)
AND nvl(c_batch_num,999999999999)
AND (pbh.batch_reference LIKE nvl(c_reference_num , '%')
OR (pbh.batch_reference IS NULL
AND c_reference_num IS NULL))
AND exists (SELECT 'x'
FROM hxt_timecards_x tim --SIR017
WHERE tim.batch_id = pbh.batch_id
) --bug 5748118
AND pbh.batch_status = 'U' ; --bug 2709527
HXT_UTIL.DEBUG('A range has been selected by the user');-- debug only --HXT115
l_merge_batches(l_count).last_updated_by := p_merge_batches(l_loop_index).last_updated_by;
l_merge_batches(l_count).last_update_date := p_merge_batches(l_loop_index).last_update_date;
l_merge_batches(l_count).last_update_login := p_merge_batches(l_loop_index).last_update_login;
Insert_Pay_Batch_Errors( batch_rec.batch_id,
'W',
'', -- HXT11
l_return);
Insert_Pay_Batch_Errors( batch_rec.batch_id,
'VE', --SIR020
'', --HXT11
l_return);
Insert_Pay_Batch_Errors( batch_rec.batch_id,
'VE', --SIR020
'', -- HXT11
l_return);
Insert_Pay_Batch_Errors( batch_rec.batch_id,
'VE', --SIR020
'', -- HXT11
l_return);
Insert_Pay_Batch_Errors( batch_rec.batch_id,
'VE', --SIR020
'', -- HXT11
l_return);
END IF; -- end process selections
Insert_Pay_Batch_Errors( l_batch_id,
'VE',
sqlerrm,
l_return);
HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( l_batch_id,
'VE', -- RETROPAY
'',
l_return);
Insert_Pay_Batch_Errors( l_batch_id,
'VE',
sqlerrm,
l_return);
SELECT fcl.meaning
FROM hr_lookups fcl
WHERE fcl.lookup_code = p_code
AND fcl.lookup_type = p_type
AND fcl.enabled_flag = 'Y'
AND p_date BETWEEN nvl(fcl.start_date_active, p_date)
AND nvl(fcl.end_date_active, p_date);
SELECT lookup_code
FROM fnd_lookup_values
WHERE meaning = p_meaning
AND lookup_type = 'NAME_TRANSLATIONS'
AND enabled_flag = 'Y'
AND p_date_active BETWEEN nvl(start_date_active, p_date_active)
AND nvl(end_date_active, p_date_active);
SELECT distinct PIV.name -- PIV.display_sequence
FROM --pay_element_types_f PET
pay_input_values_f PIV
,pay_accrual_plans PAP
,pay_net_calculation_rules PNCR
WHERE--PET.element_type_id = cp_element_type_id
--AND PET.element_type_id = PIV.element_type_id
PIV.element_type_id = cp_element_type_id
AND cp_effective_date between PIV.effective_start_date
and PIV.effective_end_date
AND PNCR.date_input_value_id = PIV.input_value_id
AND PNCR.input_value_id <> PAP.pto_input_value_id
AND PNCR.input_value_id <> PAP.co_input_value_id
AND PNCR.accrual_plan_id = PAP.accrual_plan_id
AND PAP.accrual_plan_id IN
(SELECT PAPL.accrual_plan_id
FROM pay_accrual_plans PAPL
,pay_element_links_f PEL
,pay_element_entries_f PEE
WHERE PEL.element_type_id = PAPL.accrual_plan_element_type_id
AND cp_effective_date between PEL.effective_start_date
and PEL.effective_end_date
AND PEE.element_link_id = PEL.element_link_id
AND PEE.assignment_id = cp_assignment_id
AND cp_effective_date between PEE.effective_start_date
and PEE.effective_end_date
);
HXT_UTIL.DEBUG('Successful INSERT INTO pay_batch_lines'); --HXT115
UPDATE HXT_DET_HOURS_WORKED_F
set PBL_LINE_ID = l_batch_line_id
WHERE rowid = p_values_rec.hrw_rowid;
HXT_UTIL.DEBUG('Successful UPDATE hxt_det_hours_worked_f'); --HXT115
Insert_Pay_Batch_Errors( p_values_rec.batch_id, 'VE', '', l_return);
SELECT *
FROM hxt_batch_values_v
WHERE batch_id = p_batch_id ;
SELECT *
FROM hxt_batch_sum_hours_rollup_v
WHERE batch_id = p_batch_id;
SELECT *
FROM hxt_batch_sum_amounts_v
WHERE batch_id = p_batch_id;
HXT_UTIL.DEBUG('Error attempting to insert paymix information');
Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
HXT_UTIL.DEBUG(' back from calling insert_pay_batch_errors');
Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
SELECT fnd_date.date_to_chardate(end_date) end_date --SIR149 --FORMS60
FROM per_time_periods
WHERE time_period_id = g_time_period_id;
select 'PIPE' || userenv('sessionid')
into g_pipe_session
from dual;
Insert_Pay_Batch_Errors(
p_batch_id,
'VE',
'',
l_return);
UPDATE hxt_batch_states
SET date_earned = p_date_earned
WHERE batch_id = p_batch_id;
UPDATE hxt_batch_states
SET status = p_status
WHERE batch_id = p_batch_id;
SELECT status
INTO l_batch_status
FROM hxt_batch_states
WHERE batch_id = p_batch_id;
SELECT line.assignment_number, line.effective_date, line.batch_line_id,line.OBJECT_VERSION_NUMBER
FROM pay_batch_lines line
WHERE line.batch_id = p_batch_id;
select batch_line_id,object_version_number
from pay_batch_lines
where batch_id = p_batch_id;
SELECT distinct(hrw.retro_batch_id) retro_batch_id,pbh.object_version_number
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim,
pay_batch_headers pbh
WHERE hrw.tim_id = tim.id
AND tim.batch_id = pbh.batch_id
AND tim.batch_id = p_batch_id
AND hrw.retro_batch_id IS NOT NULL;
select object_version_number
from pay_batch_headers
where batch_id = p_batch_id;
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
(p_batch_line_id => l_rec.batch_line_id
,p_object_version_number => l_rec.object_version_number
);
UPDATE hxt_batch_states
SET status = 'H'
WHERE batch_id = p_batch_id;
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
(p_batch_line_id => l_line_rec.batch_line_id
,p_object_version_number => l_line_rec.object_version_number
);
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
(p_batch_id => l_rec.retro_batch_id
,p_object_version_number => l_rec.object_version_number
);
UPDATE hxt_det_hours_worked_f
SET retro_batch_id = NULL,
pay_status = 'P',
pbl_line_id = NULL
WHERE rowid in (
SELECT hrw.rowid
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim
WHERE hrw.tim_id = tim.id
AND tim.batch_id=p_batch_id);
UPDATE hxt_batch_states
SET date_earned = NULL
WHERE batch_id = p_batch_id;
SELECT business_group_id, batch_name, batch_reference
INTO l_business_group_id, l_batch_name, l_batch_reference
FROM pay_batch_headers
WHERE batch_id = p_batch_id;
SELECT DISTINCT(hrw.tim_id)
INTO l_tim_id
FROM hxt_det_hours_worked hrw, per_assignments_f asm --C421
WHERE asm.assignment_number = batch_line_rec.assignment_number
AND hrw.assignment_id = asm.assignment_id;
UPDATE hxt_timecards tim
SET tim.batch_id = l_new_batch
WHERE tim.batch_id = p_batch_id
AND tim.id = l_tim_id;
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
(p_batch_line_id => batch_line_rec.batch_line_id
,p_object_version_number => batch_line_rec.object_version_number
);
pay_batch_element_entry_api.delete_batch_header (
p_batch_id => p_batch_id,
p_object_version_number => l_batch_ovn
);
Insert_Pay_Batch_Errors( p_batch_id,
'VE',
l_sql_error,
l_return);
PROCEDURE Insert_Pay_Batch_Errors( p_batch_id IN NUMBER,
p_error_level IN VARCHAR2,
p_exception_details IN VARCHAR2,
p_return_code OUT NOCOPY NUMBER)IS
l_error_msg VARCHAR2(240);
END Insert_Pay_Batch_Errors;
SELECT effective_start_date,
effective_end_date
FROM HXT_TIMECARDS_X
WHERE id = p_tim_id;