set serveroutput on;
declare
CURSOR building_block_id_csr (c_resource_id NUMBER,
c_start_time DATE,
c_stop_time DATE) IS
select time_building_block_id
from hxc_time_building_blocks
start with resource_id = c_resource_id
and trunc(start_time) = c_start_time
and trunc(stop_time) = c_stop_time
connect by prior time_building_block_id = parent_building_block_id
and prior object_version_number = parent_building_block_ovn
order by time_building_block_id;
CURSOR app_period_csr (c_resource_id NUMBER,
c_start_time DATE,
c_stop_time DATE) IS
select time_building_block_id
from hxc_time_building_blocks
where scope = 'APPLICATION_PERIOD'
and resource_id in (c_resource_id)
and trunc(start_time) = c_start_time
and trunc(stop_time) = c_stop_time ;
TYPE numtab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
tbb_id_tab numtab;
app_period_tab numtab;
l_retrieval_count NUMBER;
l_resource_id NUMBER;
l_start_time DATE;
l_stop_time DATE;
l_item_key wf_items.item_key%TYPE;
begin
dbms_output.put_line('Start processing...');
l_resource_id := 17555; /* This is person id*/
l_start_time := '31-OCT-2020';
l_stop_time := '31-OCT-2020';
/* l_resource_id := &1;
l_start_time := &2;
l_stop_time := &3; */
DELETE
FROM hxc_transaction_details
WHERE time_building_block_id IN
(
SELECT time_building_block_id
FROM hxc_latest_details
WHERE resource_id IN (l_resource_id)
AND trunc (start_time) = l_start_time
);
select count(*)
into l_retrieval_count
from hxc_transactions ht,
hxc_transaction_details htd
where ht.transaction_id = htd.transaction_id
and ht.type = 'RETRIEVAL'
and ht.status = 'SUCCESS'
and htd.status = 'SUCCESS'
and htd.time_building_block_id in
(select time_building_block_id from hxc_latest_details
where resource_id in (l_resource_id)
and trunc(start_time) = l_start_time) ;
dbms_output.put_line('l_retrieval_count = '||l_retrieval_count);
IF (l_retrieval_count <> 0) THEN
dbms_output.put_line('**********************************************************');
dbms_output.put_line('This timecard has been successfully retrieved atleast once');
dbms_output.put_line('DO NOT USE THIS SCRIPT TO DELETE DATA FOR THIS TIMECARD');
dbms_output.put_line('Exiting .....');
dbms_output.put_line('**********************************************************');
ELSE
OPEN app_period_csr (l_resource_id, l_start_time, l_stop_time);
FETCH app_period_csr BULK COLLECT INTO
app_period_tab;
CLOSE app_period_csr;
OPEN building_block_id_csr (l_resource_id, l_start_time, l_stop_time);
FETCH building_block_id_csr BULK COLLECT INTO
tbb_id_tab;
CLOSE building_block_id_csr;
dbms_output.put_line('tbb_id_tab.COUNT = '||tbb_id_tab.COUNT);
-- Process TIMECARD, DAY, DETAIL scope building block ids
IF (tbb_id_tab.COUNT > 0) THEN
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE FROM hxc_time_building_blocks
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_time_building_blocks... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE FROM hxc_time_attributes
WHERE time_attribute_id IN
(select time_attribute_id from hxc_time_attribute_usages
where time_building_block_id = tbb_id_tab(i));
dbms_output.put_line('Deleted from hxc_time_attributes... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE FROM hxc_time_attribute_usages
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_time_attribute_usages... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from hxc_tc_ap_links
WHERE timecard_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_tc_ap_links... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE FROM hxc_latest_details
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_latest_details... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE FROM hxc_ap_detail_links
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_ap_detail_links... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from hxc_transactions
WHERE transaction_id in
(select transaction_id from hxc_transaction_details
where time_building_block_id = tbb_id_tab(i));
dbms_output.put_line('Deleted from hxc_transactions... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from hxc_transaction_details
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_transaction_details... ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from HXC_PA_LATEST_DETAILS
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from HXC_PA_LATEST_DETAILS ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from HXC_PAY_LATEST_DETAILS
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from HXC_PAY_LATEST_DETAILS ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from HXC_RET_PA_LATEST_DETAILS
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from HXC_RET_PA_LATEST_DETAILS ');
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from HXC_RET_PAY_LATEST_DETAILS
WHERE time_building_block_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from HXC_RET_PAY_LATEST_DETAILS ');
BEGIN
-- abort the workflows
FOR i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
LOOP
SELECT approval_item_key
INTO l_item_key
FROM hxc_timecard_summary
WHERE timecard_id = tbb_id_tab(i);
wf_engine.abortprocess('HXCEMP', l_item_key);
wf_purge.items('HXCEMP',l_item_key,SYSDATE,FALSE);
END LOOP;
EXCEPTION
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('There is no workflow found');
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Workflow not found');
END;
FORALL i IN tbb_id_tab.FIRST..tbb_id_tab.LAST
DELETE from hxc_timecard_summary
WHERE timecard_id = tbb_id_tab(i);
dbms_output.put_line('Deleted from hxc_timecard_summary... ');
END IF;
dbms_output.put_line('Process the application periods... ');
-- Process the application period scope building block ids
dbms_output.put_line('app_period_tab.COUNT = '||app_period_tab.COUNT);
IF (app_period_tab.COUNT > 0) THEN
-- abort the workflows
BEGIN
FOR i IN app_period_tab.FIRST..app_period_tab.LAST
LOOP
SELECT approval_item_key
INTO l_item_key
FROM hxc_app_period_summary
WHERE application_period_id = app_period_tab(i);
wf_engine.abortprocess('HXCEMP', l_item_key);
wf_purge.items('HXCEMP',l_item_key,SYSDATE,FALSE);
END LOOP;
EXCEPTION
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('There is no workflow found1');
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Workflow not found1');
END;
FORALL i IN app_period_tab.FIRST..app_period_tab.LAST
DELETE FROM hxc_app_period_summary
WHERE application_period_id = app_period_tab(i);
dbms_output.put_line('Deleted from hxc_app_period_summary... ');
END IF;
END IF;
end;
/
