[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM hxc_data_sets
WHERE p_stop_date BETWEEN START_DATE AND END_DATE
AND STATUS IN ( 'OFF_LINE', 'RESTORE_IN_PROGRESS', 'BACKUP_IN_PROGRESS' );
p_return_periods.delete(l_found_index);
SELECT START_TIME,
STOP_TIME,
APPROVAL_STATUS
FROM hxc_time_building_blocks
WHERE SCOPE = 'TIMECARD'
AND DATE_TO = hr_general.end_of_time
AND RESOURCE_ID = p_resource_id
AND RESOURCE_TYPE = p_resource_type
AND STOP_TIME >= p_first_start_date
AND START_TIME <= p_last_end_date
ORDER BY START_TIME;
IS SELECT COUNT(1)
FROM hxc_timecard_summary
START WITH resource_id = p_resource_id
AND start_time = p_start_time
CONNECT BY resource_id = p_resource_id
AND PRIOR TRUNC(stop_time) + 1 = start_time;
IS SELECT COUNT(1)
FROM hxc_timecard_summary
START WITH resource_id = p_resource_id
AND start_time < p_stop_time
AND stop_time = p_stop_time
CONNECT BY resource_id = p_resource_id
AND start_time < PRIOR stop_time
AND PRIOR start_time - (1/(24*60*60)) = stop_time;
SELECT pas.ASSIGNMENT_ID,
pas.EFFECTIVE_START_DATE,
NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
FROM PER_ALL_ASSIGNMENTS_F pas,
per_assignment_status_types typ
,per_periods_of_service ppos --14596859
WHERE pas.PERSON_ID = p_resource_id
AND pas.ASSIGNMENT_TYPE in ('E','C')
AND pas.PRIMARY_FLAG = 'Y'
AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
-- AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK') -- 5922228
AND DECODE(typ.PER_SYSTEM_STATUS,'ACTIVE_ASSIGN',1,
'ACTIVE_CWK', 1,
0 ) >= p_assign_period_limit
-- AND pas.EFFECTIVE_START_DATE <= SYSDATE
AND ppos.person_id = pas.person_id
AND sysdate between trunc(DATE_START)
and trunc(NVL(ppos.ACTUAL_TERMINATION_DATE,hr_general.end_of_time))
AND pas.period_of_service_id = ppos.period_of_service_id
ORDER BY EFFECTIVE_START_DATE;*/
SELECT pas.assignment_id
, pas.effective_start_date
, nvl (pas.effective_end_date, hr_general.end_of_time) end_date
FROM per_all_assignments_f pas
, per_assignment_status_types typ
WHERE pas.person_id = p_resource_id
AND pas.assignment_type IN ('E', 'C')
AND pas.primary_flag = 'Y'
AND pas.assignment_status_type_id = typ.assignment_status_type_id
AND decode (typ.per_system_status, 'ACTIVE_ASSIGN'
, 1, 'ACTIVE_CWK'
, 1, 0) >= p_assign_period_limit
AND (
EXISTS
(
SELECT 1
FROM per_periods_of_service ppos
WHERE ppos.person_id = pas.person_id
AND trunc (sysdate)
BETWEEN trunc (date_start)
AND decode (p_assign_period_limit, 0
, trunc (nvl (ppos.final_process_date, hr_general.end_of_time)), trunc (nvl (ppos.actual_termination_date, hr_general.end_of_time)))
AND pas.period_of_service_id = ppos.period_of_service_id
)
OR EXISTS
(
SELECT 1
FROM per_periods_of_placement ppop
WHERE ppop.person_id = pas.person_id
AND trunc (sysdate)
BETWEEN date_start
AND decode (p_assign_period_limit, 0
, nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
AND pas.period_of_placement_date_start
BETWEEN ppop.date_start
AND decode (p_assign_period_limit, 0
, nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
)
)
ORDER BY effective_start_date;
SELECT pas.ASSIGNMENT_ID,
pas.EFFECTIVE_START_DATE,
NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
FROM PER_ALL_ASSIGNMENTS_F pas,
per_assignment_status_types typ
WHERE pas.PERSON_ID = p_resource_id
AND pas.ASSIGNMENT_TYPE = 'E'
AND pas.PRIMARY_FLAG = 'Y'
AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
AND typ.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
-- AND pas.EFFECTIVE_START_DATE <= SYSDATE
ORDER BY EFFECTIVE_START_DATE;
SELECT rp.period_type,
rp.duration_in_days,
rp.start_date
FROM hxc_recurring_periods rp,
per_time_period_types p
WHERE p.period_type (+) = rp.period_type
AND hxc_preference_evaluation.resource_preferences(
p_resource_id,'TC_W_TCRD_PERIOD|1|') = rp.recurring_period_id;
select hrp.period_type,
hrp.duration_in_days,
hrp.start_date
from hxc_recurring_periods hrp
where hrp.recurring_period_id = p_recurring_period_id;
g_assignment_periods.delete;
SELECT pap.accrual_plan_id
FROM pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
WHERE pap.accrual_plan_element_type_id = pet.element_type_id
AND pet.element_type_id = pel.element_type_id
AND pee.effective_start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pee.effective_start_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pap.accrual_plan_name = p_plan_name;
select
TIME_BUILDING_BLOCK_ID
,TYPE
,MEASURE
,UNIT_OF_MEASURE
,START_TIME
,STOP_TIME
,PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,SCOPE
,OBJECT_VERSION_NUMBER
,APPROVAL_STATUS
,RESOURCE_ID
,RESOURCE_TYPE
,APPROVAL_STYLE_ID
,DATE_FROM
,DATE_TO
,COMMENT_TEXT
,PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,APPLICATION_SET_ID
,TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks
where time_building_block_id = p_timecard_id
and resource_id = p_resource_id
and scope = 'TIMECARD';
SELECT
TIME_BUILDING_BLOCK_ID
,TYPE
,MEASURE
,UNIT_OF_MEASURE
,START_TIME
,STOP_TIME
,PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,SCOPE
,OBJECT_VERSION_NUMBER
,APPROVAL_STATUS
,RESOURCE_ID
,RESOURCE_TYPE
,APPROVAL_STYLE_ID
,DATE_FROM
,DATE_TO
,COMMENT_TEXT
,PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,APPLICATION_SET_ID
,TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks
WHERE resource_id = p_resource_id
AND parent_building_block_id = p_parent_building_block_id
AND parent_building_block_ovn = p_parent_ovn
AND SCOPE = 'DAY';
SELECT
TIME_BUILDING_BLOCK_ID
,TYPE
,MEASURE
,UNIT_OF_MEASURE
,START_TIME
,STOP_TIME
,PARENT_BUILDING_BLOCK_ID
,'N' PARENT_IS_NEW
,SCOPE
,OBJECT_VERSION_NUMBER
,APPROVAL_STATUS
,RESOURCE_ID
,RESOURCE_TYPE
,APPROVAL_STYLE_ID
,DATE_FROM
,DATE_TO
,COMMENT_TEXT
,PARENT_BUILDING_BLOCK_OVN
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,APPLICATION_SET_ID
,TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks
WHERE resource_id = p_resource_id
AND parent_building_block_id = p_parent_building_block_id
AND parent_building_block_ovn = p_parent_ovn
AND SCOPE = 'DETAIL'
order by OBJECT_VERSION_NUMBER;
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
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a
where a.time_attribute_id = au.time_attribute_id
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
and (au.time_building_block_id,au.time_building_block_ovn) in
(select detail.time_building_block_id,detail.object_version_number
from hxc_time_building_blocks detail,
hxc_time_building_blocks day
where day.time_building_block_id = detail.parent_building_block_id
and day.object_version_number = detail.parent_building_block_ovn
and day.scope = 'DAY'
and detail.resource_id = l_resource_id
and detail.scope = 'DETAIL'
--and day.date_to = hr_general.end_of_time
--and detail.date_to = hr_general.end_of_time
and day.parent_building_block_id = timecard_id
and day.parent_building_block_ovn = timecard_ovn
and day.resource_id = l_resource_id)
UNION
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
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a
where a.time_attribute_id = au.time_attribute_id
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
and (au.time_building_block_id,au.time_building_block_ovn) in
(select day.time_building_block_id,day.object_version_number
from hxc_time_building_blocks day
where -- day.date_to = hr_general.end_of_time
day.scope = 'DAY'
and day.parent_building_block_id = timecard_id
and day.parent_building_block_ovn = timecard_ovn
and day.resource_id = l_resource_id)
UNION
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
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a
where a.time_attribute_id = au.time_attribute_id
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
and (au.time_building_block_id,au.time_building_block_ovn) in
(select time_building_block_id,object_version_number
from hxc_time_building_blocks htbb
where --htbb.date_to = hr_general.end_of_time
htbb.scope = 'TIMECARD'
and htbb.time_building_block_id = timecard_id
and htbb.object_version_number = timecard_ovn
and htbb.resource_id = l_resource_id)
order by time_building_block_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
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a,
hxc_time_building_blocks htbb
where a.time_attribute_id = au.time_attribute_id
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
and au.time_building_block_id = htbb.time_building_block_id
and au.time_building_block_ovn = htbb.object_version_number
and htbb.scope = 'DETAIL'
and htbb.time_building_block_id = detail_id
and htbb.object_version_number = detail_ovn
and htbb.resource_id = l_resource_id;
l_timecard_block.delete;
l_day_block.delete;
l_detail_block.delete;
l_detail_attribute.delete;
l_alias_block.delete;
SELECT distinct
to_char(app.resource_id) app_resource_id
, regexp_replace(fnd_date.date_to_canonical(trunc(app.start_time)),' .*') app_start_date
, regexp_replace(fnd_date.date_to_canonical(trunc(app.stop_time)),' .*') app_end_date
, fad.entity_name
, fad.pk1_value
, fad.pk2_value
, fad.pk3_value
, fad.created_by
, fad.last_update_login
, fad.program_application_id
, fad.program_id
, fad.request_id
, fad.automatically_added_flag
, fad.category_id
FROM hxc_time_building_blocks app
,hxc_tc_ap_links htal
,hxc_timecard_summary hts
,fnd_attached_documents fad
WHERE app.scope = 'APPLICATION_PERIOD'
AND app.date_to = hr_general.end_of_time
AND app.resource_id = to_number(p_resource_id)
AND trunc(app.start_time) < c_tc_end_date
AND trunc(app.stop_time) > c_tc_start_date
AND trunc(app.start_time) <> c_tc_start_date
AND trunc(app.stop_time) <> c_tc_end_date
AND app.time_building_block_id = htal.application_period_id
AND htal.timecard_id = hts.timecard_id
AND hts.resource_id = to_number(fad.pk1_value)
AND regexp_replace(fnd_date.date_to_canonical(trunc(hts.start_time)),' .*') = fad.pk2_value
AND regexp_replace(fnd_date.date_to_canonical(trunc(hts.stop_time)),' .*') = fad.pk3_value
AND fad.entity_name = 'HXC_TIMECARD_SUMMARY'
;
l_from_last_update_login_tab num_tab;
, l_from_last_update_login_tab
, l_from_program_app_id_tab
, l_from_program_id_tab
, l_from_request_id_tab
, l_automatically_added_flag_tab
, l_from_category_id_tab;
hr_utility.trace('call to delete approval period attachments...fnd_attached_documents2_pkg.delete_attachments');
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name => 'HXC_TIMECARD_SUMMARY'
, X_pk1_value => l_app_resource_id_tab(l_app_resource_id_tab.FIRST)
, X_pk2_value => l_app_start_date_tab(l_app_resource_id_tab.FIRST)
, X_pk3_value => l_app_end_date_tab(l_app_resource_id_tab.FIRST)
, X_delete_document_flag => 'Y'
);
X_last_update_login => l_from_last_update_login_tab(i),
X_program_application_id => l_from_program_app_id_tab(i),
X_program_id => l_from_program_id_tab(i),
X_request_id => l_from_request_id_tab(i),
X_automatically_added_flag => l_automatically_added_flag_tab(i),
X_from_category_id => l_from_category_id_tab(i),
X_to_category_id => l_from_category_id_tab(i)
);