[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select QUALIFIER_ATTRIBUTE26,UPPER(QUALIFIER_ATTRIBUTE27)
from HXC_LAYOUT_COMP_QUALIFIERS qualifiers
where
exists
(select LAYOUT_COMPONENT_ID from HXC_LAYOUT_COMPONENTS COMPONENTS where
layout_id =P_LAYOUT_ID and COMPONENTS.LAYOUT_COMPONENT_ID = qualifiers.LAYOUT_COMPONENT_ID )
and UPPER(QUALIFIER_ATTRIBUTE27) LIKE 'ATTRIBUTE%' and QUALIFIER_ATTRIBUTE_CATEGORY<>'HIDDEN_FIELD';
p_timecard_attributes.delete(l_attribute_index);
l_timecard_detail_attributes.delete;
l_zero_hrs_temp_dtl_attr.delete;
p_zero_template_blocks.DELETE(l_index);
END IF; -- attribute BB_ID = Deleted Time BB_ID
p_zero_template_attributes.DELETE(l_index);
END IF; -- attribute BB_ID = Deleted Time BB_ID
-- Delete Attribute Row
p_attribute_array.DELETE(l_index);
l_del_bb_ids.DELETE;
l_detail_attributes.DELETE;
l_detail_sub_attributes.DELETE;
-- If yes, delete the SUB Detail Row
IF l_det_row_found
THEN
-- Check to see if the correct TBB is obtained
/* IF g_debug THEN
hr_utility.trace(p_block_array(l_index_detail).time_building_block_id||' | '||p_block_array(l_sub_index_detail).time_building_block_id||' | ');
p_block_array.DELETE(l_sub_index_detail);
-- Now, for all Building Blocks deleted, also delete the attributes
--
IF (l_del_bb_ids.COUNT > 0)
THEN
-- Loop thru the l_del_bb_ids table
FOR x IN
l_del_bb_ids.FIRST .. l_del_bb_ids.LAST
LOOP
-- Now for every TBB ID in l_del_bb_ids loop thru the attributes
-- and delete if found
-- Find attributes of deleted l_del_bb_ids (x).time_building_block_id
l_index :=NULL;
-- Delete Attribute Row
p_attribute_array.DELETE(l_index);
END IF; -- attribute BB_ID = Deleted Time BB_ID
SELECT mc.bld_blk_info_type_id
FROM hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_retrieval_processes rp
WHERE upper(mc.field_name) = upper(p_field_name)
AND rp.mapping_id = m.mapping_id
AND rp.retrieval_process_id = p_retrieval_process_id
AND m.mapping_id = mcu.mapping_id
AND mcu.mapping_component_id = mc.mapping_component_id;
SELECT bld_blk_info_type_id
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = p_bld_blk_info_type;
SELECT bld_blk_info_type
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type_id = p_bld_blk_info_type_id;
select mc.segment
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_retrieval_processes rp
,hxc_bld_blk_info_types bbit
,hxc_bld_blk_info_type_usages bbui
where rp.mapping_id = m.mapping_id
AND mc.field_name = p_field_name
and rp.retrieval_process_id = p_retrieval_process_id
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
AND bbit.bld_blk_info_type = p_attribute_category;
IF NVL(p_app_attributes(l_att_count).UPDATED, 'N') <> 'Y'
AND p_app_attributes(l_att_count).time_attribute_id = p_block_attribute.TIME_ATTRIBUTE_ID
THEN
l_info_type_id :=
get_info_type_id(
p_field_name => p_app_attributes(l_att_count).ATTRIBUTE_NAME
,p_retrieval_process_id => p_process_id
);
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
IF NVL(l_app_attributes(l_app_attribute_index).UPDATED, 'N') <> 'Y'
THEN
-- build a new time attribute record
l_block_attributes.extend;
PROCEDURE update_blocks(
p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
,p_approval_status IN hxc_time_building_blocks.approval_status%TYPE
,p_approval_style IN hxc_time_building_blocks.approval_style_id%TYPE
,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
,p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
,p_timecard_found OUT NOCOPY BOOLEAN
)
IS
l_block_index NUMBER;
END update_blocks;
p_block_array.delete(p_block_array.LAST);
l_complete_attributes.delete(l_existing_att_index);
p_attributes.delete(l_attr_index);
p_blocks.delete(p_block_index);
select htr.appl_dynamic_template_process
from hxc_time_recipients htr,
fnd_application fa
where fa.application_short_name = p_dyn_template_app
and htr.application_id = fa.application_id;
SELECT time_building_block_id
FROM (
SELECT time_building_block_id
FROM hxc_time_building_blocks tbb1
WHERE tbb1.resource_id = p_resource_id
AND tbb1.resource_type = p_resource_type
AND tbb1.scope = 'TIMECARD'
AND to_char(tbb1.stop_time,'YYYY/MM/DD') < p_start_time
AND date_to = hr_general.end_of_time
ORDER BY tbb1.stop_time desc
)
WHERE rownum = 1;
SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = p_process_name;
SELECT hrp.retrieval_process_id
FROM hxc_retrieval_processes hrp,
hxc_time_recipients htr
WHERE htr.appl_dynamic_template_process = p_dyn_template_process
AND htr.time_recipient_id = hrp.time_recipient_id;
update_blocks(
p_resource_id => p_resource_id
,p_approval_status => p_approval_status
,p_approval_style => p_approval_style
,p_blocks => l_blocks
,p_timecard_id => l_timecard_id
,p_timecard_found => l_timecard_found
);
select a.time_attribute_id
,au.time_building_block_id
,bbit.bld_blk_info_type
,a.attribute_category
,a.attribute1
,a.attribute2
,a.attribute3
,a.attribute4
,a.attribute5
,a.attribute6
,a.attribute7
,a.attribute8
,a.attribute9
,a.attribute10
,a.attribute11
,a.attribute12
,a.attribute13
,a.attribute14
,a.attribute15
,a.attribute16
,a.attribute17
,a.attribute18
,a.attribute19
,a.attribute20
,a.attribute21
,a.attribute22
,a.attribute23
,a.attribute24
,a.attribute25
,a.attribute26
,a.attribute27
,a.attribute28
,a.attribute29
,a.attribute30
,a.bld_blk_info_type_id
,a.object_version_number
,'N' NEW
,'N' CHANGED
from hxc_time_attributes a,
hxc_time_attribute_usages au,
hxc_bld_blk_info_types bbit
where au.time_building_block_id = p_building_block_id
and au.time_building_block_ovn = p_ovn
and au.time_attribute_id = a.time_attribute_id
and (not (a.attribute_category = 'SECURITY'))
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
select a.time_attribute_id
,au.time_building_block_id
,bbit.bld_blk_info_type
,a.attribute_category
,a.attribute1
,a.attribute2
,a.attribute3
,a.attribute4
,a.attribute5
,a.attribute6
,a.attribute7
,a.attribute8
,a.attribute9
,a.attribute10
,a.attribute11
,a.attribute12
,a.attribute13
,a.attribute14
,a.attribute15
,a.attribute16
,a.attribute17
,a.attribute18
,a.attribute19
,a.attribute20
,a.attribute21
,a.attribute22
,a.attribute23
,a.attribute24
,a.attribute25
,a.attribute26
,a.attribute27
,a.attribute28
,a.attribute29
,a.attribute30
,a.bld_blk_info_type_id
,a.object_version_number
,'N' NEW
,'N' CHANGED
,'N' PROCESS
from hxc_time_attributes a,
hxc_time_attribute_usages au,
hxc_bld_blk_info_types bbit
where au.time_building_block_id = p_building_block_id
and au.time_building_block_ovn = p_ovn
and au.time_attribute_id = a.time_attribute_id
and (not (a.attribute_category = 'SECURITY'))
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
,hxc_time_building_blocks tc
where tbb1.date_to = hr_general.end_of_time
and tbb1.resource_id = p_resource_id
and tbb1.resource_type = p_resource_type
and tbb1.scope = 'DAY'
and tbb1.parent_building_block_id = tc.time_building_block_id
and tbb1.parent_building_block_ovn = tc.object_version_number
and tc.scope = 'TIMECARD'
and tc.date_to = hr_general.end_of_time
and to_char(tbb1.start_time,'YYYY/MM/DD') >= p_start_time
and to_char(tbb1.start_time,'YYYY/MM/DD') <= p_stop_time
and tbb1.date_to = hr_general.end_of_time
order by tbb1.start_time asc;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
,hxc_time_building_blocks tc
,hxc_time_building_blocks days
where tbb1.date_to = hr_general.end_of_time
and tbb1.resource_id = p_resource_id
and tbb1.resource_type = p_resource_type
and tbb1.scope = 'DETAIL'
and tbb1.parent_building_block_id = days.time_building_block_id
and tbb1.parent_building_block_ovn = days.object_version_number
and days.scope = 'DAY'
and to_char(days.start_time,'YYYY/MM/DD') >= p_start_time
and to_char(days.start_time,'YYYY/MM/DD') <= p_stop_time
and days.date_to = hr_general.end_of_time
and days.parent_building_block_id = tc.time_building_block_id
and days.parent_building_block_ovn = tc.object_version_number
and tc.date_to = hr_general.end_of_time
and tc.scope = 'TIMECARD'
order by tbb1.start_time asc;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
where tbb1.date_to = hr_general.end_of_time
and tbb1.resource_id = p_resource_id
and tbb1.resource_type = p_resource_type
and tbb1.scope = 'TIMECARD'
and to_char(tbb1.start_time,'YYYY/MM/DD') >= p_start_time
and to_char(tbb1.start_time,'YYYY/MM/DD') <= p_stop_time;
select object_version_number
from hxc_time_building_blocks
where time_building_block_id = p_timecard_id
and date_to = hr_general.end_of_time;
SELECT full_name
FROM per_all_people_f ppf
WHERE person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT timecards.comment_text
FROM hxc_tc_ap_links links
,hxc_time_building_blocks timecards
WHERE links.application_period_id = p_app_period_id
AND links.timecard_id = timecards.time_building_block_id
AND timecards.date_to = hr_general.end_of_time;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
,hxc_time_building_blocks tc
where tbb1.date_to = hr_general.end_of_time
and tbb1.resource_id = p_resource_id
and tbb1.resource_type = p_resource_type
and tbb1.scope = 'DAY'
and tbb1.start_time >= p_start_time
and tbb1.start_time <= p_stop_time
and tbb1.parent_building_block_id = tc.time_building_block_id
and tbb1.parent_building_block_ovn = tc.object_version_number
and tc.scope = 'TIMECARD'
and tc.date_to = hr_general.end_of_time
order by tbb1.start_time asc;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
where tbb1.time_building_block_id = p_app_period_id
and tbb1.date_to = hr_general.end_of_time;
select
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
from hxc_time_building_blocks tbb1
where tbb1.date_to = hr_general.end_of_time
and tbb1.resource_id = p_resource_id
and tbb1.resource_type = p_resource_type
and tbb1.scope = 'TIMECARD'
and p_start_time <= tbb1.stop_time
and p_stop_time >= tbb1.start_time;
SELECT
tbb1.TIME_BUILDING_BLOCK_ID
,tbb1.TYPE
,tbb1.MEASURE
,tbb1.UNIT_OF_MEASURE
,tbb1.START_TIME
,tbb1.STOP_TIME
,tbb1.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,tbb1.SCOPE
,tbb1.OBJECT_VERSION_NUMBER
,tbb1.APPROVAL_STATUS
,tbb1.RESOURCE_ID
,tbb1.RESOURCE_TYPE
,tbb1.APPROVAL_STYLE_ID
,tbb1.DATE_FROM
,tbb1.DATE_TO
,tbb1.COMMENT_TEXT
,tbb1.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,tbb1.application_set_id
,tbb1.translation_display_key
FROM hxc_ap_detail_links adlinks
,hxc_time_building_blocks tbb1
WHERE adlinks.application_period_id = p_app_period_id
AND adlinks.time_building_block_id = tbb1.time_building_block_id
AND adlinks.time_building_block_ovn = tbb1.object_version_number
AND tbb1.date_to = hr_general.end_of_time;
SELECT
details.TIME_BUILDING_BLOCK_ID
,details.TYPE
,details.MEASURE
,details.UNIT_OF_MEASURE
,details.START_TIME
,details.STOP_TIME
,details.PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,details.SCOPE
,details.OBJECT_VERSION_NUMBER
,details.APPROVAL_STATUS
,details.RESOURCE_ID
,details.RESOURCE_TYPE
,details.APPROVAL_STYLE_ID
,details.DATE_FROM
,details.DATE_TO
,details.COMMENT_TEXT
,details.PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,details.application_set_id
,details.translation_display_key
from hxc_time_building_blocks timecard,
hxc_time_building_blocks details,
hxc_time_building_blocks days,
hxc_tc_ap_links hal
where
days.time_building_block_id = details.parent_building_block_id
and days.object_version_number = details.parent_building_block_ovn
and hal.APPLICATION_PERIOD_ID = p_app_period_id
and hal.timecard_id = timecard.time_building_block_id
and days.parent_building_block_id = timecard.time_building_block_id
and days.parent_building_block_ovn = timecard.object_version_number
and details.date_to = hr_general.end_of_time
and days.start_time <= p_stop_time
and days.stop_time >= p_start_time
order by details.translation_display_key;
SELECT hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
,favtl.application_name
,apsum.approver_id
,apsum.time_category_id
FROM hxc_app_period_summary apsum
,fnd_application_tl favtl
,hxc_time_recipients htr
WHERE apsum.application_period_id = p_app_period_id
AND favtl.application_id = htr.application_id
AND htr.time_recipient_id = apsum.time_recipient_id
AND favtl.language = userenv('LANG');
select 'Y'
from wf_notification_attributes wna,
wf_notification_attributes wnb
where wna.notification_id = wnb.notification_id
and wna.notification_id = p_notif_id
and wna.name = 'FYI_ACTION_CODE'
and wna.text_value = hxc_app_comp_notifications_api.c_action_request_approval
and wnb.name ='FYI_RECIPIENT_CODE'
and wnb.text_value = hxc_app_comp_notifications_api.c_recipient_supervisor;
SELECT 'Y'
FROM hxc_app_period_summary
WHERE application_period_id = p_block_id;
select timecard_id
from hxc_timecard_summary
where resource_id = p_resource_id
and trunc(start_time) = trunc(p_start_time)
and trunc(stop_time) = trunc(p_stop_time);
hxc_block_attribute_update.replace_ids
(p_blocks => p_block_array
,p_attributes => p_attribute_array,
p_duplicate_template => TRUE
);
SELECT tal.application_period_id,
ts.start_time,
ts.stop_time
FROM hxc_tc_ap_links tal, hxc_timecard_summary ts
WHERE ts.timecard_id = p_timecard_id
and ts.timecard_id = tal.timecard_id;
select htr.appl_dynamic_template_process
from hxc_time_recipients htr,
fnd_application fa
where fa.application_short_name = p_dyn_template_app
and htr.application_id = fa.application_id;
SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = p_process_name;
SELECT hrp.retrieval_process_id
FROM hxc_retrieval_processes hrp,
hxc_time_recipients htr
WHERE htr.appl_dynamic_template_process = p_dyn_template_process
AND htr.time_recipient_id = hrp.time_recipient_id;
SELECT /*+ ordered */
lat.time_building_block_id,
lat.object_version_number
FROM hxc_timecard_summary sum,
hxc_time_building_blocks day,
hxc_time_building_blocks det,
hxc_latest_details lat
WHERE sum.timecard_id = l_timecard_id
AND sum.timecard_ovn = l_timecard_ovn
AND day.parent_building_block_id = sum.timecard_id
AND day.parent_building_block_ovn = sum.timecard_ovn
AND det.parent_building_block_id = day.time_building_block_id
AND det.parent_building_block_ovn = day.object_version_number
AND(det.measure IS NOT NULL OR(det.start_time IS NOT NULL AND det.stop_time IS NOT NULL))
AND lat.time_building_block_id = det.time_building_block_id ;
SELECT ht.transaction_id,
ht.transaction_process_id
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND htd.status = 'SUCCESS'
AND htd.time_building_block_id = l_bb_id
AND htd.time_building_block_ovn = l_bb_ovn;
SELECT retrieval_process_id
INTO l_transaction_process_id_tab(l_index)
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';
SELECT time_recipient_name
INTO l_time_recipient_name
FROM hxc_application_set_comps_v
WHERE application_set_id IN
(SELECT application_set_id
FROM hxc_time_building_blocks
WHERE time_building_block_id = l_timecard_id
and object_version_number = l_timecard_ovn
)
AND time_recipient_id IN
(SELECT time_recipient_id
FROM hxc_retrieval_processes
WHERE retrieval_process_id = l_transaction_process_id_tab(l_index));