[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM (
select iv.input_value_id,
ivtl.name,
iv.lookup_type,
iv.uom,
TO_NuMBER(REPLACE(fdfcu.application_column_name,'ATTRIBUTE')) otl_sequence,
iv.display_sequence
from pay_input_values_f iv,
pay_input_values_f_tl ivtl,
fnd_descr_flex_column_usages fdfcu
where p_effective_date between iv.effective_start_date
and iv.effective_end_date
and iv.element_type_id = p_element_type_id
and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
and ivtl.LANGUAGE = userenv('LANG')
and fdfcu.application_id = 809
and fdfcu.descriptive_flexfield_name = 'OTC Information Types'
and fdfcu.descriptive_flex_context_code = 'ELEMENT - '||to_char(p_element_type_id)
and fdfcu.end_user_column_name = iv.name
UNION
select iv.input_value_id,
ivtl.name,
iv.lookup_type,
iv.uom,
NULL otl_sequence,
iv.display_sequence
from pay_input_values_f iv,
pay_input_values_f_tl ivtl
--
where p_effective_date between iv.effective_start_date
and iv.effective_end_date
and iv.element_type_id = p_element_type_id
and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
and ivtl.LANGUAGE = userenv('LANG')
and mandatory_flag = 'X'
)
order by display_sequence, name;
select iv.input_value_id,
ivtl.name,
iv.lookup_type,
iv.uom,
rank() over (partition by iv.element_type_id order by iv.display_sequence, iv.name) otl_sequence,
iv.display_sequence
from pay_input_values_f iv,
pay_input_values_f_tl ivtl
--
where p_effective_date between iv.effective_start_date
and iv.effective_end_date
and iv.element_type_id = p_element_type_id
and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
and ivtl.LANGUAGE = userenv('LANG')
order by iv.display_sequence, iv.name;
IS SELECT 1
FROM fnd_descr_flex_contexts
WHERE application_id = 809
AND descriptive_flexfield_name = 'OTC Information Types'
AND descriptive_flex_context_code = 'ELEMENT - '||TO_CHAR(p_element_type_id);
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;
UPDATE hxc_ret_pay_latest_details
SET batch_id = l_temp_batch_upd_id
WHERE batch_id = p_merge_batches(l_loop_index1).batch_id
RETURNING timecard_id
BULK COLLECT INTO l_tc_tab;
hr_utility.trace('List of timecards updated in hxc_ret_pay_latest_details ');
UPDATE hxc_ret_pay_details
SET batch_id = l_temp_batch_upd_id
WHERE batch_id = p_merge_batches(l_loop_index1).batch_id
RETURNING timecard_id
BULK COLLECT INTO l_tc_tab;
hr_utility.trace('List of timecards updated in hxc_ret_pay_details ');
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 NOT EXISTS ( SELECT 1
FROM hxc_data_sets hds,
hxt_timecards_f tim
WHERE tim.batch_id = pbh.batch_id
AND hds.data_set_id = tim.data_set_id
AND hds.status IN ('OFF_LINE','ARCHIVE_IN_PROGRESS','RESTORE_IN_PROGRESS')
)
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
);
IS SELECT
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM hxt_sum_hours_worked_f
WHERE id = p_id;
IS SELECT hrw_rowid
FROM hxt_batch_values_v
WHERE element_type_id = p_element_type_id
AND assignment_id = p_assignment_id
AND batch_id = p_batch_id
AND assignment_number = p_assignment_number
AND element_name = p_element_name
AND ( cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
OR cost_allocation_keyflex_id IS NULL)
AND ( concatenated_segments = p_concatenated_segments
OR concatenated_segments IS NULL)
AND ( reason = p_meaning
OR reason IS NULL)
AND ( segment1 = p_segment1
OR segment1 IS NULL)
AND ( segment2 = p_segment2
OR segment2 IS NULL)
AND ( segment3 = p_segment3
OR segment3 IS NULL)
AND ( segment4 = p_segment4
OR segment4 IS NULL)
AND ( segment5 = p_segment5
OR segment5 IS NULL)
AND ( segment6 = p_segment6
OR segment6 IS NULL)
AND ( segment7 = p_segment7
OR segment7 IS NULL)
AND ( segment8 = p_segment8
OR segment8 IS NULL)
AND ( segment9 = p_segment9
OR segment9 IS NULL)
AND ( segment10 = p_segment10
OR segment10 IS NULL)
AND ( segment11 = p_segment11
OR segment11 IS NULL)
AND ( segment12 = p_segment12
OR segment12 IS NULL)
AND ( segment13 = p_segment13
OR segment13 IS NULL)
AND ( segment14 = p_segment14
OR segment14 IS NULL)
AND ( segment15 = p_segment15
OR segment15 IS NULL)
AND ( segment16 = p_segment16
OR segment16 IS NULL)
AND ( segment17 = p_segment17
OR segment17 IS NULL)
AND ( segment18 = p_segment18
OR segment18 IS NULL)
AND ( segment19 = p_segment19
OR segment19 IS NULL)
AND ( segment20 = p_segment20
OR segment20 IS NULL)
AND ( segment21 = p_segment21
OR segment21 IS NULL)
AND ( segment22 = p_segment22
OR segment22 IS NULL)
AND ( segment23 = p_segment23
OR segment23 IS NULL)
AND ( segment24 = p_segment24
OR segment24 IS NULL)
AND ( segment25 = p_segment25
OR segment25 IS NULL)
AND ( segment26 = p_segment26
OR segment26 IS NULL)
AND ( segment27 = p_segment27
OR segment27 IS NULL)
AND ( segment28 = p_segment28
OR segment28 IS NULL)
AND ( segment29 = p_segment29
OR segment29 IS NULL)
AND ( segment30 = p_segment30
OR segment30 IS NULL)
AND ( amount = p_amount
OR amount IS NULL)
AND ( rate_multiple = p_rate_multiple
OR rate_multiple IS NULL)
AND ( hourly_rate = p_hourly_rate
OR hourly_rate IS NULL)
AND ( rate_code = p_flex_value
OR rate_code IS NULL)
AND ( location_code = p_location_code
OR location_code IS NULL)
AND ( fcl_tax_rule_code = p_fcl_tax_rule_code
OR fcl_tax_rule_code IS NULL)
AND ( separate_check_flag = p_separate_check_flag
OR separate_check_flag IS NULL)
AND ( state_name = p_state_name
OR state_name IS NULL)
AND ( county_name = p_county_name
OR county_name IS NULL)
AND ( city_name = p_city_name
OR city_name IS NULL)
AND ( zip_code = p_zip_code
OR zip_code IS NULL) ;
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;
hr_utility.trace('Updated HXT_DET_HOURS_WORKED_F with line id '||l_batch_line_id);
UPDATE hxt_det_hours_worked_f
SET pbl_line_id = l_batch_line_id
WHERE rowid = CHARTOROWID(l_rowtab(i));
hr_utility.trace('Printing Rowids being updated with batch line '||l_batch_line_id);
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 distinct(hrw.retro_batch_id) retro_batch_id,pbh.object_version_number,
pbh.batch_status
FROM hxt_det_hours_worked_f hrw,
hxt_timecards_f tim,
pay_batch_headers pbh
WHERE hrw.tim_id = tim.id
AND hrw.retro_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;
hr_utility.trace( 'updated hxt_batch_states for this batch ');
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);
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
(p_batch_id => l_retro_tab(i).retro_batch_id
,p_object_version_number => l_retro_tab(i).retro_ovn
);
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
);
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;
IS SELECT business_group_id
FROM pay_batch_headers
WHERE batch_id = p_batch_id;
IS SELECT id
FROM hxt_timecards_f
WHERE batch_id = p_batch_id;
SELECT /*+ INDEX(det HXT_DET_HOURS_WORKED_F_SUM_FK) */
ret.resource_id,
ret.time_building_block_id,
ret.approval_status,
ret.start_time,
ret.stop_time,
ret.org_id,
ret.business_group_id,
ret.timecard_id,
ret.application_set_id,
det.element_type_id,
ret.attribute2,
ret.attribute3,
det.hours,
ret.object_version_number,
ret.old_ovn,
ret.old_measure,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
det.pbl_line_id,
ret.retro_pbl_id,
ret.old_pbl_id,
FND_GLOBAL.conc_request_id,
ret.old_request_id,
p_batch_id,
ret.retro_batch_id,
ret.old_batch_id,
ROWIDTOCHAR(ret.rowid),
ret.measure,
ret.attribute1,
ret.attribute2,
ret.attribute3,
ret.request_id
FROM hxt_sum_hours_worked_f sum,
hxt_det_hours_worked_f det,
hxc_ret_pay_latest_details ret
WHERE sum.tim_id = p_tim_id
AND sum.id = det.parent_id
AND ret.time_building_block_id = sum.time_building_block_id
AND ret.object_version_number = sum.time_building_block_ovn;
DELETE FROM hxc_ret_pay_latest_details
WHERE ROWID = CHARTOROWID(rowid_tab(i));
INSERT INTO hxc_ret_pay_latest_details
( resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
application_set_id, -- Bug 14267796
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
old_ovn,
old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
pbl_id,
retro_pbl_id,
old_pbl_id,
request_id,
old_request_id,
batch_id,
retro_batch_id,
old_batch_id,
hxt_element_id,
hxt_attribute2,
hxt_attribute3,
hxt_measure,
hxt_request_id,
hxt_batch_id)
VALUES ( resource_id_tab(i),
time_building_block_id_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
org_id_tab(i),
business_group_id_tab(i),
timecard_id_tab(i),
app_set_id_tab(i), -- Bug 14267796
attribute1_tab(i),
attribute2_tab(i),
attribute3_tab(i),
measure_tab(i),
object_version_number_tab(i),
old_ovn_tab(i),
old_measure_tab(i),
old_attribute1_tab(i),
old_attribute2_tab(i),
old_attribute3_tab(i),
pbl_id_tab(i),
retro_pbl_id_tab(i),
old_pbl_id_tab(i),
request_id_tab(i),
old_request_id_tab(i),
batch_id_tab(i),
retro_batch_id_tab(i),
old_batch_id_tab(i),
hxt_att1_tab(i),
hxt_att2_tab(i),
hxt_att3_tab(i),
hxt_measure_tab(i),
hxt_request_tab(i),
p_batch_id
);