[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM (SELECT iv_data.*, ROWNUM r
FROM (SELECT inv.uom, inv.lookup_type, inv.value_set_id,
etp.input_currency_code
FROM pay_input_values_f inv,
pay_element_types_f etp
WHERE inv.element_type_id = p_element_type_id
AND etp.element_type_id = p_element_type_id
AND p_session_date
BETWEEN inv.effective_start_date
AND inv.effective_end_date
AND p_session_date
BETWEEN etp.effective_start_date
AND etp.effective_end_date
ORDER BY inv.display_sequence, inv.NAME) iv_data
WHERE ROWNUM < (p_iv_number + 1))
WHERE r > (p_iv_number - 1);
SELECT *
FROM (SELECT iv_data.*, ROWNUM r
FROM (SELECT inv.uom, inv.lookup_type, inv.value_set_id,
etp.input_currency_code
FROM pay_input_values_f inv,
pay_element_types_f etp
WHERE inv.element_type_id = p_element_type_id
AND etp.element_type_id = p_element_type_id
AND p_session_date
BETWEEN inv.effective_start_date
AND inv.effective_end_date
AND p_session_date
BETWEEN etp.effective_start_date
AND etp.effective_end_date
ORDER BY inv.display_sequence, inv.NAME) iv_data
WHERE ROWNUM < (p_iv_number + 1))
WHERE r > (p_iv_number - 1);
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = p_lookup_type
AND hl.lookup_code = p_lookup_code;
{in (select peo.person_id
from per_all_people_f peo
where peo.business_group_id = '
|| p_bg_id
|| ')}';
{in (select paa.person_id
from per_all_assignments_f paa
where paa.business_group_id = '
|| p_bg_id
|| l_person
|| l_payroll
|| l_location
|| l_org
|| ')}';
{in (select paa.person_id
from per_all_assignments_f paa,
hr_soft_coding_keyflex hsk
where paa.business_group_id = '
|| p_bg_id
|| l_person
|| l_payroll
|| l_location
|| ' and paa.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = '''
|| p_gre_id
|| ''')}';
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => p_process_name,
p_status => p_status,
p_exception_description => SUBSTR
(p_exception,
1,
g_max_message_size
),
p_rollback => FALSE
);
SELECT COUNT(*)
INTO l_dup_count
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'Time Store'
AND ROWNUM < 2 ;
p_action_if_exists => g_insert_if_exist,
p_batch_reference => p_batch_reference,
p_batch_source => p_batch_source,
p_batch_id => l_new_batch,
p_object_version_number => l_object_version_number
);
IF NOT (hxt_interface_utilities.is_deleted
(p_tbb_tbl (l_det_tbb_idx)
)
)
THEN -- must be an update to an existing BEE entry
IF chk_intg_enabled( p_tbb_tbl(l_det_tbb_idx).resource_id,
TRUNC( p_tbb_tbl(l_det_tbb_idx).start_time))
THEN
g_pay_pa_link_tab(l_det_tbb_idx).object_version_number
:= p_tbb_tbl(l_det_tbb_idx).ovn;
IF NOT (hxt_interface_utilities.is_deleted
(p_tbb_tbl (l_det_tbb_idx)
)
)
THEN
bee_batch_line (p_bg_id => p_bg_id,
p_tbb_rec => p_tbb_tbl
(l_det_tbb_idx),
p_det_tbb_idx => l_det_tbb_idx,
p_attr_tbl => p_attr_tbl,
p_attr_tbl_idx => l_det_attr_idx,
p_bee_rec => l_bee_rec,
p_cost_flex_id => l_cost_flex_id
);
INSERT INTO hxc_pay_pa_link_all
VALUES g_pay_pa_link_tab(i);
INSERT INTO hxc_pay_pa_link_all
values g_old_pay_pa_link_tab(i);
g_pay_pa_link_tab.DELETE;
g_old_pay_pa_link_tab.DELETE;
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => 'BEE Retrieval Process',
p_status => 'SUCCESS',
p_exception_description => NULL
);
SELECT COUNT(*)
INTO l_dup_count
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'OTM'
AND ROWNUM < 2;
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => g_bee_retrieval_process,
p_status => 'ERRORS',
p_exception_description => SUBSTR
(SQLERRM,
1,
g_max_message_size
),
p_rollback => FALSE
);
p_batch_selection IN VARCHAR2 DEFAULT NULL,
p_is_old IN VARCHAR2 DEFAULT NULL,
p_old_batch_ref IN VARCHAR2 DEFAULT NULL,
p_new_batch_ref IN VARCHAR2 DEFAULT NULL,
p_new_specified IN VARCHAR2 DEFAULT NULL,
p_status_in_bee IN VARCHAR2,
p_otlr_to_bee IN VARCHAR2,
p_since_date IN VARCHAR2
)
AS
l_where_clause hxt_interface_utilities.max_varchar;
SELECT time_building_block_id,
object_Version_number,
type,
DECODE(type,'MEASURE',measure,'RANGE',(stop_time-start_time)*24),
start_time,
stop_time,
parent_building_block_id ,
scope,
resource_type,
comment_text,
unit_of_measure,
'N',
'N'
FROM hxc_time_building_blocks
WHERE (time_building_block_id,object_version_number)
IN ( SELECT detail_bb_id,
detail_bb_ovn
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'Time Store');
SELECT detail_bb_id,
type,
-1*hours, -- To create reverse entries, you need negative hours.
TRUNC(NVL(start_time,date_earned)),
TRUNC(NVL(stop_time,date_earned)),
parent_bb_id ,
scope,
resource_id,
resource_type,
comment_text,
uom,
detail_bb_ovn,
changed,
deleted,
timecard_id,
timecard_ovn
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'Time Store'
order by detail_bb_id
;
UPDATE hxc_bee_pref_adj_lines
SET type = t_bb_details(i).type,
scope = t_bb_details(i).scope,
hours = t_bb_details(i).measure ,
start_time = t_bb_details(i).start_time ,
stop_time = t_bb_details(i).stop_time ,
resource_type = t_bb_details(i).resource_type ,
uom = t_bb_details(i).uom ,
changed = t_bb_details(i).changed ,
deleted = t_bb_details(i).deleted ,
comment_text = t_bb_details(i).comment_text ,
parent_bb_id = t_bb_details(i).parent_bb_id
WHERE detail_bb_id = t_bb_details(i).bb_id
AND detail_bb_ovn = t_bb_details(i).ovn;
SELECT hat.attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
hau.time_building_block_id,
hau.time_building_block_ovn,
hat.bld_blk_info_type_id
FROM hxc_time_attribute_usages hau,
hxc_time_attributes hat
WHERE hau.time_attribute_id = hat.time_attribute_id
AND (hau.time_building_block_id,
hau.time_building_block_ovn) IN ( SELECT detail_bb_id, detail_bb_ovn
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'Time Store')
ORDER BY hau.time_building_block_id,
hat.bld_blk_info_type_id;
DELETE FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'Time Store';
PROCEDURE delete_non_transferred_hours
IS
BEGIN
DELETE FROM hxt_det_hours_worked_F
WHERE ( date_worked,
assignment_id )
in ( SELECT date_earned,
assignment_id
FROM hxc_bee_pref_adj_lines,
per_all_assignments_f paf,
per_assignment_status_types pas
WHERE resource_id = person_id
AND batch_source = 'OTM'
AND paf.effective_end_date = hr_general.end_of_time
AND paf.effective_start_date <= date_earned
AND paf.primary_flag = 'Y'
AND paf.assignment_status_type_id =
pas.assignment_status_type_id
AND pas.per_system_status = 'ACTIVE_ASSIGN')
AND pay_status <> 'C'
AND effective_end_date = hr_general.end_of_time;
SELECT *
FROM hxt_batch_values_v
WHERE (date_worked ,assignment_id) in
( SELECT date_earned,
paf.assignment_id
FROM hxc_bee_pref_adj_lines hoa,
per_all_assignments_f paf
WHERE hoa.resource_id = paf.person_id
AND hoa.date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y');
delete_non_transferred_hours;
SELECT COUNT(*)
INTO l_rec_count
FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'OTM'
AND rownum < 2;
DELETE FROM hxc_bee_pref_adj_lines
WHERE batch_source = 'OTM';
INSERT INTO hxc_retrieval_batches_all
(batch_id,
request_id,
user_id,
request_date,
batch_status)
VALUES ( TO_NUMBER(l_index),
FND_GLOBAL.conc_request_id,
FND_GLOBAL.user_id,
TRUNC(SYSDATE),
'ELIGIBLE');