[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_timecards is
cursor c_timecards is
select ts.timecard_id
,ts.timecard_ovn
from hxc_timecard_summary ts
where not exists (select 'Y'
from hxc_time_building_blocks tbb
where tbb.time_building_block_id = ts.timecard_id and
tbb.object_version_number = ts.timecard_ovn and
tbb.scope = 'TIMECARD' and
tbb.date_to = hr_general.end_of_time);
cursor c_deleted_timecards is
select ts.timecard_id
,ts.timecard_ovn
from hxc_timecard_summary ts
where exists (select 'Y'
from hxc_time_building_blocks tbb
where tbb.time_building_block_id = ts.timecard_id and
tbb.object_version_number = ts.timecard_ovn and
tbb.scope = 'TIMECARD' and
tbb.date_to <> hr_general.end_of_time);
hxc_timecard_summary_api.delete_timecard(timecard_rec.timecard_id);
for timecard_rec in c_deleted_timecards loop
hxc_timecard_summary_api.delete_timecard(timecard_rec.timecard_id);
End delete_timecards;
procedure delete_templates is
cursor c_templates is
select ts.template_id
,ts.template_ovn
from hxc_template_summary ts
where not exists (select 'Y'
from hxc_time_building_blocks tbb
where tbb.time_building_block_id = ts.template_id and
tbb.object_version_number = ts.template_ovn and
tbb.scope = 'TIMECARD_TEMPLATE' and
tbb.date_to = hr_general.end_of_time);
cursor c_deleted_templates is
select ts.template_id
,ts.template_ovn
from hxc_template_summary ts
where exists (select 'Y'
from hxc_time_building_blocks tbb
where tbb.time_building_block_id = ts.template_id and
tbb.object_version_number = ts.template_ovn and
tbb.scope = 'TIMECARD_TEMPLATE' and
tbb.date_to <> hr_general.end_of_time);
hxc_template_summary_api.delete_template(template_rec.template_id);
for template_rec in c_deleted_templates loop
hxc_template_summary_api.delete_template(template_rec.template_id);
End delete_templates;
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.resource_id = p_rid and
tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD' and
not exists
(select 'Y'
from hxc_timecard_summary
where timecard_id = tbb.time_building_block_id and
timecard_ovn = tbb.object_version_number);
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.resource_id = p_rid and
tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD_TEMPLATE'
and resource_id <>-1 and
not exists
(select 'Y'
from hxc_template_summary
where template_id = tbb.time_building_block_id and
template_ovn = tbb.object_version_number);
select distinct tbb.time_building_block_id
from hxc_time_building_blocks tbb
,hxc_time_attribute_usages tau
,hxc_time_attributes ta
where tbb.scope = 'APPLICATION_PERIOD' and
tbb.date_to = hr_general.end_of_time and
tbb.resource_id = p_resource_id and
tau.time_building_block_id = tbb.time_building_block_id and
tau.time_building_block_ovn = tbb.object_version_number and
ta.attribute_category = 'APPROVAL' and
ta.time_attribute_id = tau.time_attribute_id and
not exists
(select 'Y'
from hxc_app_period_summary aps
where aps.application_period_id = tbb.time_building_block_id and
aps.application_period_ovn = tbb.object_version_number)
order by 1;
select distinct resource_id
from hxc_time_building_blocks tbb
where tbb.scope = 'TIMECARD' and
tbb.date_to = hr_general.end_of_time and
not exists
(select 'Y'
from hxc_timecard_summary ts
where tbb.time_building_block_id = ts.timecard_id and
tbb.object_version_number = ts.timecard_ovn);
select 1 from dual
where exists ( select 'x' from hxc_timecard_summary);
select 1 from dual
where exists ( select 'x' from hxc_template_summary);
select distinct resource_id
from hxc_time_building_blocks tbb
where tbb.scope = 'TIMECARD_TEMPLATE' and
tbb.date_to = hr_general.end_of_time and
not exists
(select 'Y'
from hxc_template_summary ts
where tbb.time_building_block_id = ts.template_id and
tbb.object_version_number = ts.template_ovn);
delete_timecards;
delete_templates;
PROCEDURE update_appl_set_id(p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
,p_start_date in date
,p_end_date in date
,p_resource_id in hxc_time_building_blocks.resource_id%type
,p_object_version_number in hxc_time_building_blocks.object_version_number%type
,p_upg_count out nocopy number) IS
CURSOR C_Time_BB_Ids(p_Building_Block_Id number, p_Object_Version_Number number) is
SELECT tbb1.time_building_block_id
,tbb1.object_version_number
,tbb1.scope
FROM hxc_time_building_blocks tbb1
WHERE scope IN ('TIMECARD', 'DAY', 'DETAIL') AND
application_set_id IS NULL
START WITH ((tbb1.time_building_block_id = p_building_block_id) AND
(tbb1.object_version_number = p_object_version_number))
CONNECT BY PRIOR tbb1.time_building_block_id =
tbb1.parent_building_block_id AND
PRIOR tbb1.object_version_number =
tbb1.parent_building_block_ovn;
SELECT a
FROM (SELECT application_set_id a
,COUNT(*) cnt
FROM hxc_application_set_comps_v
WHERE time_recipient_id IN
(SELECT DISTINCT attribute1
FROM hxc_time_attributes
WHERE time_attribute_id IN
(SELECT time_attribute_id
FROM hxc_time_attribute_usages
WHERE time_building_block_id IN
(SELECT htb2.time_building_block_id
FROM hxc_time_building_blocks htb2
WHERE htb2.scope = 'APPLICATION_PERIOD' AND
htb2.resource_id = p_rec_id AND
TRUNC(htb2.start_time) =
p_start_time AND
TRUNC(htb2.stop_time) =
TRUNC(p_stop_time) AND
htb2.object_version_number =
(SELECT MAX(hb.object_version_number)
FROM hxc_time_building_blocks hb
WHERE hb.time_building_block_id =
htb2.time_building_block_id AND
hb.start_time =
htb2.start_time AND
hb.stop_time =
htb2.stop_time AND
hb.resource_id =
htb2.resource_id AND
hb.scope =
'APPLICATION_PERIOD'))) AND
attribute_category = 'APPROVAL')
GROUP BY application_set_id)
WHERE cnt =
(SELECT COUNT(distinct attribute1)
FROM hxc_time_attributes
WHERE time_attribute_id IN
(SELECT time_attribute_id
FROM hxc_time_attribute_usages
WHERE time_building_block_id IN
(SELECT htb2.time_building_block_id
FROM hxc_time_building_blocks htb2
WHERE htb2.scope = 'APPLICATION_PERIOD' AND
htb2.resource_id = p_rec_id AND
TRUNC(htb2.start_time) = p_start_time AND
TRUNC(htb2.stop_time) =
TRUNC(p_stop_time) AND
htb2.object_version_number =
(SELECT MAX(hb.object_version_number)
FROM hxc_time_building_blocks hb
WHERE hb.time_building_block_id =
htb2.time_building_block_id AND
hb.start_time = htb2.start_time AND
hb.stop_time = htb2.stop_time AND
hb.resource_id = htb2.resource_id AND
hb.scope = 'APPLICATION_PERIOD'))) AND
attribute_category = 'APPROVAL') AND
a NOT IN
(SELECT application_set_id a
FROM hxc_application_set_comps_v
WHERE time_recipient_id NOT IN
(SELECT DISTINCT attribute1
FROM hxc_time_attributes
WHERE time_attribute_id IN
(SELECT time_attribute_id
FROM hxc_time_attribute_usages
WHERE time_building_block_id IN
(SELECT htb2.time_building_block_id
FROM hxc_time_building_blocks htb2
WHERE htb2.scope = 'APPLICATION_PERIOD' AND
htb2.resource_id = p_rec_id AND
TRUNC(htb2.start_time) =
p_start_time AND
TRUNC(htb2.stop_time) =
TRUNC(p_stop_time) AND
htb2.object_version_number =
(SELECT MAX(hb.object_version_number)
FROM hxc_time_building_blocks hb
WHERE hb.time_building_block_id =
htb2.time_building_block_id AND
hb.start_time =
htb2.start_time AND
hb.stop_time =
htb2.stop_time AND
hb.resource_id =
htb2.resource_id AND
hb.scope =
'APPLICATION_PERIOD'))) AND
attribute_category = 'APPROVAL'));
update hxc_time_building_blocks
set application_set_id = l_application_set_id
where time_building_block_id = C2.time_building_block_id and
object_version_number = C2.object_version_number;
UPDATE hxc_latest_details hld
SET hld.application_set_id = l_application_set_id
WHERE hld.time_building_block_id =
C2.time_building_block_id AND
hld.object_version_number = C2.object_version_number;
End update_appl_set_id;
SELECT DISTINCT htb.time_building_block_id
,htb.start_time
,htb.stop_time
,htb.resource_id
,htb.object_version_number
FROM hxc_time_building_blocks htb
WHERE htb.scope = 'TIMECARD' AND
htb.application_set_id IS NULL AND
htb.object_version_number =
(SELECT MAX(hb.object_version_number)
FROM hxc_time_building_blocks hb
WHERE hb.time_building_block_id = htb.time_building_block_id AND
hb.start_time = htb.start_time AND
hb.stop_time = htb.stop_time AND
hb.resource_id = htb.resource_id AND
hb.scope = 'TIMECARD' AND
hb.application_set_id IS NULL) and
htb.start_time >= nvl(p_start_date, htb.start_time) and
htb.stop_time <= nvl(p_end_date, htb.stop_time) and
exists
(select 1
from per_all_people_f per
where htb.resource_id = per.person_id);
SELECT DISTINCT htb.time_building_block_id
,htb.start_time
,htb.stop_time
,htb.resource_id
,htb.object_version_number
FROM hxc_time_building_blocks htb
WHERE htb.scope = 'TIMECARD' AND
htb.application_set_id IS NULL AND
htb.object_version_number =
(SELECT MAX(hb.object_version_number)
FROM hxc_time_building_blocks hb
WHERE hb.time_building_block_id = htb.time_building_block_id AND
hb.start_time = htb.start_time AND
hb.stop_time = htb.stop_time AND
hb.resource_id = htb.resource_id AND
hb.scope = 'TIMECARD' AND
hb.application_set_id IS NULL) and
htb.start_time >= nvl(p_start_date, htb.start_time) and
htb.stop_time <= nvl(p_end_date, htb.stop_time) and
exists
(select 1
from per_all_people_f per
where htb.resource_id = per.person_id and
per.business_group_id = p_business_group_id);
update_appl_set_id(p_time_building_block_id =>c1.time_building_block_id
,p_start_date => c1.start_time
,p_end_date => c1.stop_time
,p_resource_id => c1.resource_id
,p_object_version_number => c1.object_version_number
,p_upg_count =>p_upg_count);
update_appl_set_id(p_time_building_block_id =>c1.time_building_block_id
,p_start_date => c1.start_time
,p_end_date => c1.stop_time
,p_resource_id => c1.resource_id
,p_object_version_number => c1.object_version_number
,p_upg_count =>p_upg_count);
SELECT DISTINCT tbb.resource_id
,tbb.time_building_Block_id
,tbb.object_version_number
,tbb.approval_status
,tbb.application_set_id
,tbb.last_update_date
,tbb.resource_type
,tbb.comment_text
,tbb_day.start_time
,tbb_day.stop_time
FROM hxc_time_building_blocks tbb
,hxc_time_building_Blocks tbb_day
WHERE tbb.scope = 'DETAIL' AND
tbb.object_Version_number =
(SELECT /*+ no_unnest */
MAX(dovn.object_version_number)
FROM hxc_time_building_blocks dovn
WHERE dovn.time_building_block_id =
tbb.time_building_block_id) AND
tbb_day.time_building_block_id = tbb.parent_building_block_id AND
NOT EXISTS
(SELECT 'x'
FROM hxc_latest_details hld
WHERE hld.time_building_block_id = tbb.time_building_block_id) AND
tbb_day.object_Version_number =
(SELECT /*+ no_unnest */
MAX(dovn1.object_version_number)
FROM hxc_time_building_blocks dovn1
WHERE dovn1.time_building_block_id =
tbb_day.time_building_block_id) and
tbb_day.start_time >= nvl(p_start_date, tbb_day.start_time) and
tbb_day.stop_time <= nvl(p_end_date, tbb_day.stop_time) and
EXISTS
( SELECT 'x'
FROM hxc_time_building_blocks tbb_timecard
WHERE tbb_timecard.time_building_block_id = tbb_day.parent_building_block_id
AND scope = 'TIMECARD' );
TYPE last_update_date_tab IS TABLE OF hxc_time_building_blocks.last_update_date%TYPE INDEX BY BINARY_INTEGER;
t_last_update_date last_update_date_tab;
t_last_update_date,
t_resource_type,
t_comment_text,
t_start_time,
t_stop_time LIMIT 100;
INSERT INTO hxc_latest_details
(resource_id
,time_building_block_id
,object_version_number
,approval_status
,application_set_id
,last_update_date
,resource_type
,comment_text
,start_time
,stop_time)
VALUES
(t_resource_id(x)
,t_tbb_id(x)
,t_ovn(x)
,t_approval_status(x)
,t_application_set_id(x)
,t_last_update_date(x)
,t_resource_type(x)
,t_comment_text(x)
,t_start_time(x)
,t_stop_time(x));
t_resource_id.DELETE;
t_tbb_id.DELETE;
t_ovn.DELETE;
t_approval_status.DELETE;
t_application_set_id.DELETE;
t_last_update_date.DELETE;
t_resource_type.DELETE;
t_comment_text.DELETE;
t_start_time.DELETE;
t_stop_time.DELETE;
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD_TEMPLATE'
and resource_id <>-1 and
not exists
(select 'Y'
from hxc_template_summary
where template_id = tbb.time_building_block_id and
template_ovn = tbb.object_version_number) and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
exists
(select 1
from per_all_people_f per
where tbb.resource_id = per.person_id);
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD_TEMPLATE'
and resource_id <>-1 and
not exists
(select 'Y'
from hxc_template_summary
where template_id = tbb.time_building_block_id and
template_ovn = tbb.object_version_number) and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
exists
(select 1
from per_all_people_f per
where tbb.resource_id = per.person_id and
per.business_group_id = p_business_group_id);
insert into hxc_temp_timecards
(time_building_block_id
,object_version_number
,scope
,worker_id)
values
(l_tbb_id_tab(x)
,l_ovn_tab(x)
,'TIMECARD_TEMPLATE'
,l_req_id(i));
l_tbb_id_tab.delete;
l_ovn_tab.delete;
insert into hxc_temp_timecards
(TIME_BUILDING_BLOCK_ID
,scope
,object_version_number
,worker_id
,processed)
values
(null
,'COMPLETED'
,null
,l_request_id
,'Y');
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD' and
not exists
(select 'Y'
from hxc_timecard_summary
where timecard_id = tbb.time_building_block_id and
timecard_ovn = tbb.object_version_number) and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
exists
(select 1
from per_all_people_f per
where tbb.resource_id = per.person_id);
select tbb.time_building_block_id
,tbb.object_version_number
from hxc_time_building_blocks tbb
where tbb.date_to = hr_general.end_of_time and
tbb.scope = 'TIMECARD' and
not exists
(select 'Y'
from hxc_timecard_summary
where timecard_id = tbb.time_building_block_id and
timecard_ovn = tbb.object_version_number) and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
exists
(select 1
from per_all_people_f per
where tbb.resource_id = per.person_id and
per.business_group_id = p_business_group_id);
select distinct tbb.time_building_block_id
from hxc_time_building_blocks tbb
,hxc_time_attribute_usages tau
,hxc_time_attributes ta
where tbb.scope = 'APPLICATION_PERIOD' and
tbb.date_to = hr_general.end_of_time and
tau.time_building_block_id = tbb.time_building_block_id and
tau.time_building_block_ovn = tbb.object_version_number and
ta.attribute_category = 'APPROVAL' and
ta.time_attribute_id = tau.time_attribute_id and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
not exists
(select 'Y'
from hxc_app_period_summary aps
where aps.application_period_id = tbb.time_building_block_id and
aps.application_period_ovn = tbb.object_version_number) and
exists
(select 1
from per_all_people_f per
where tbb.resource_id = person_id )
order by 1;
select distinct tbb.time_building_block_id
from hxc_time_building_blocks tbb
,hxc_time_attribute_usages tau
,hxc_time_attributes ta
where tbb.scope = 'APPLICATION_PERIOD' and
tbb.date_to = hr_general.end_of_time and
tau.time_building_block_id = tbb.time_building_block_id and
tau.time_building_block_ovn = tbb.object_version_number and
ta.attribute_category = 'APPROVAL' and
ta.time_attribute_id = tau.time_attribute_id and
TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
not exists
(select 'Y'
from hxc_app_period_summary aps
where aps.application_period_id = tbb.time_building_block_id and
aps.application_period_ovn = tbb.object_version_number) and
exists
(select 1
from per_all_people_f per
where tbb.resource_id = person_id and
per.business_group_id =
p_business_group_id)
order by 1;
insert into hxc_temp_timecards
(time_building_block_id
,object_version_number
,scope
,worker_id)
values
(l_tbb_id_tab(x)
,l_ovn_tab(x)
,'TIMECARD'
,l_req_id(i));
l_tbb_id_tab.delete;
l_ovn_tab.delete;
insert into hxc_temp_timecards
(time_building_block_id
,scope
,worker_id)
values
(l_app_bb_id_tab(x)
,'APPLICATION_PERIOD'
,l_req_id(i));
l_app_bb_id_tab.delete;
insert into hxc_temp_timecards
(TIME_BUILDING_BLOCK_ID
,scope
,object_version_number
,worker_id
,processed)
values
(null
,'COMPLETED'
,null
,l_request_id
,'Y');
delete_timecards;
if (hr_update_utility.isUpdateComplete(p_app_shortname => 'HXC',
p_function_name => NULL,
p_business_group_id => p_business_group_id,
p_update_name => 'HXCTCMIGRATE'
) = 'FALSE')
then
hr_update_utility.setUpdateProcessing(p_update_name => 'HXCTCMIGRATE' );
/* changed from FND_REQUEST.SUBMIT_REQUEST to hr_update_utility.submitRequest in order to reflect the
status in DTR report */
hr_update_utility.submitRequest(p_app_shortname => 'HXC'
,p_update_name => 'HXCTCMIGRATE'
,p_validate_proc => 'hxc_tcsummary_migrate.check_hxt_installed'
,p_business_group_id => p_business_group_id
--,p_legislation_code in varchar2 default null
,p_argument1 =>p_business_group_id
,p_argument2 => p_start_date
,p_argument3 => p_end_date
,p_argument4 => p_stop_time
,p_argument5 => p_batch_size
,p_argument6 => p_num_workers
,p_argument7 =>'TIMECARD'
,p_request_id => l_timecard_req_id) ;
/* changed from FND_REQUEST.SUBMIT_REQUEST to hr_update_utility.submitRequest in order to reflect the
status in DTR report */
hr_update_utility.submitRequest(p_app_shortname => 'HXC'
,p_update_name => 'HXCTCMIGRATE'
,p_validate_proc => 'hxc_tcsummary_migrate.check_hxt_installed'
,p_business_group_id => p_business_group_id
--,p_legislation_code in varchar2 default null
,p_argument1 =>p_business_group_id
,p_argument2 => p_start_date
,p_argument3 => p_end_date
,p_argument4 => p_stop_time
,p_argument5 => p_batch_size
,p_argument6 => p_num_workers
,p_argument7 =>'TEMPLATE'
,p_request_id => l_template_req_id) ;
AND hr_update_utility.isUpdateComplete(p_app_shortname => 'HXC',
p_function_name => NULL,
p_business_group_id => p_business_group_id,
p_update_name => 'HXCTCMIGRATE'
) = 'FALSE'
)
then
hr_update_utility.setUpdateComplete(p_update_name => 'HXCTCMIGRATE' );
select time_building_block_id
,object_version_number
from hxc_temp_timecards
where worker_id = p_worker_id and
scope = p_scope and
processed = 'Y';
select 'Y'
from hxc_temp_timecards
where worker_id = p_worker_id and
scope = 'COMPLETED';
delete from hxc_temp_timecards where worker_id = l_worker_id;
update hxc_temp_timecards
set processed = 'Y'
where worker_id = l_worker_id and
scope in ('TIMECARD', 'APPLICATION_PERIOD');
delete from hxc_temp_timecards
where worker_id = l_worker_id and
scope in ('TIMECARD', 'APPLICATION_PERIOD') and
processed = 'Y';
delete from hxc_temp_timecards where worker_id = l_worker_id;
update hxc_temp_timecards
set processed = 'Y'
where worker_id = l_worker_id and
scope in ('TIMECARD_TEMPLATE');
delete from hxc_temp_timecards
where worker_id = l_worker_id and
scope in ('TIMECARD_TEMPLATE') and
processed = 'Y';
select status
from fnd_product_installations
where application_id = PSP_APPLICATION_ID;