[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT /*+ INDEX (HXC HXC_TIME_BUILDING_BLOCKS_N2)*/
TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,UNIT_OF_MEASURE,
START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,PARENT_BUILDING_BLOCK_OVN,
SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,APPLICATION_SET_ID,DATA_SET_ID,
TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks hxc
WHERE data_set_id = p_data_set_id
AND scope = 'TIMECARD'
ORDER BY time_building_block_id;
IS SELECT temp1.resource_id,
temp1.start_time,
temp1.time_building_block_id tc_id,
temp2.thread_id||'('||temp2.chunk_number||')' detail
FROM hxc_ar_detail_log temp2,
hxc_ar_tc_ids_temp temp1
WHERE temp1.time_building_block_id = temp2.time_building_block_id
AND temp1.object_version_number = temp2.object_version_number
AND temp2.process_type NOT LIKE '%INCOMPLETE%'
ORDER BY temp2.thread_id,
temp2.chunk_number,
temp1.start_time,
temp1.resource_id ;
IS SELECT temp1.resource_id,
temp1.start_time,
temp1.time_building_block_id tc_id,
temp2.thread_id||'('||temp2.chunk_number||')' detail
FROM hxc_ar_detail_log temp2,
hxc_ar_tc_ids_temp temp1
WHERE temp1.time_building_block_id = temp2.time_building_block_id
AND temp1.object_version_number = temp2.object_version_number
AND temp2.process_type LIKE '%INCOMPLETE%'
ORDER BY temp2.thread_id,
temp2.chunk_number,
temp1.start_time,
temp1.resource_id ;
UPDATE hxc_data_sets
SET status = 'BACKUP_IN_PROGRESS'
WHERE data_set_id = p_data_set_id;
DELETE FROM hxc_ar_detail_log ;
DELETE FROM hxc_ar_tc_ids_temp;
DELETE FROM hxc_ar_trans_temp;
DELETE FROM hxc_data_set_details;
INSERT INTO hxc_ar_tc_ids_temp
--(TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
--SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
--APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
--PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,DATA_SET_ID,TRANSLATION_DISPLAY_KEY)
VALUES l_tc_tab(i) ;
SELECT COUNT(1)
INTO trans_count
FROM hxc_ar_trans_temp
WHERE rownum < 2;
UPDATE hxc_ar_trans_temp
SET thread_id = 0 ;
INSERT INTO hxc_transactions_ar
(DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
SELECT /*+ INDEX(bkuptxn hxc_transactions_pk) */
p_data_set_id,bkuptxn.TRANSACTION_ID,TRANSACTION_PROCESS_ID,
TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
TRANSACTION_CODE
FROM hxc_transactions bkuptxn
WHERE bkuptxn.transaction_id IN ( SELECT temp.transaction_id
FROM hxc_ar_trans_temp temp
WHERE thread_id = 0 )
AND bkuptxn.transaction_id NOT IN ( SELECT transaction_id
FROM hxc_transactions_ar hxc
WHERE bkuptxn.transaction_id = hxc.transaction_id)
;
DELETE FROM hxc_transactions ht
WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
FROM hxc_ar_trans_temp
WHERE thread_id = 0 )
AND NOT EXISTS( SELECT 1
FROM hxc_transaction_details htd
WHERE htd.transaction_id = ht.transaction_id);
DELETE FROM hxc_ar_trans_temp
WHERE thread_id = 0 ;
SELECT COUNT(1)
INTO trans_count
FROM hxc_data_set_details;
UPDATE hxc_data_sets
SET status = 'OFF_LINE', validation_status = ' '
WHERE data_set_id = p_data_set_id;
IS SELECT time_building_block_id,
object_version_number
FROM hxc_ar_tc_ids_temp
WHERE time_building_block_id >= p_from_id
AND time_building_block_id < p_to_id ;
IS SELECT /*+ LEADING(temp) */
transaction_detail_id,
transaction_id,
ROWIDTOCHAR(ar.ROWID)
FROM hxc_temp_timecard_chunks temp,
hxc_transaction_details ar
WHERE ar.time_building_block_id = temp.id
AND ar.time_building_block_ovn = temp.ref_ovn
AND thread_id = p_thread_id ;
IS SELECT /*+ LEADING(temp) */
transaction_detail_id,
transaction_id,
ROWIDTOCHAR(ar.ROWID)
FROM hxc_temp_timecard_chunks temp,
hxc_dep_transaction_details ar
WHERE ar.time_building_block_id = temp.id
AND ar.time_building_block_ovn = temp.ref_ovn
AND thread_id = p_thread_id ;
IS SELECT /*+ LEADING(temp) */
DISTINCT
time_attribute_usage_id,
time_attribute_id,
ROWIDTOCHAR(ar.ROWID)
FROM hxc_temp_timecard_chunks temp,
hxc_time_attribute_usages ar
WHERE ar.time_building_block_id = temp.id
AND thread_id = p_thread_id ;
IS SELECT ref_rowid
FROM hxc_temp_timecard_chunks
WHERE scope = p_scope
AND thread_id = p_thread_id ;
IS SELECT master_id,
MAX(ROWID)
FROM hxc_archive_temp
WHERE thread_id = p_thread_id
GROUP BY master_id ;
IS SELECT det.time_building_block_id,
ROWIDTOCHAR(det.ROWID)
FROM hxc_temp_timecard_chunks temp,
hxc_latest_details det
WHERE temp.scope = 'DETAIL'
AND temp.id = det.time_building_block_id ;
IS SELECT id,
max(ref_ovn)
FROM hxc_temp_timecard_chunks temp
WHERE scope = 'DETAIL'
AND thread_id = p_thread_id
GROUP by id ;
IS SELECT DISTINCT
sum.id,
sum.tim_id
FROM hxc_temp_timecard_chunks temp,
hxt_sum_hours_worked_f sum
WHERE temp.id = sum.time_building_block_id
AND temp.thread_id = p_thread_id;
INSERT INTO hxc_ar_detail_log
(time_building_block_id,
object_version_number,
process_type,
thread_id,
chunk_number)
VALUES (tc_id_tab(i),
tc_ovn_tab(i),
'ARCHIVE-INCOMPLETE',
p_thread_id,
l_chunk_no);
INSERT INTO hxc_data_set_details
(data_set_id,
scope,
table_name,
row_count)
VALUES (p_data_set_id,
p_scope,
p_thread_id,
l_chunk_no );
SELECT start_date,
end_date
INTO l_data_set_start_date,
l_data_set_end_date
FROM hxc_data_sets
WHERE data_set_id = p_data_set_id;
INSERT INTO hxc_temp_timecard_chunks
( id,
ref_ovn,
scope,
thread_id )
VALUES ( tc_id_tab(i),
tc_ovn_tab(i),
'TIMECARD',
p_thread_id );
INSERT INTO hxc_time_building_blocks_ar
(DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
TRANSLATION_DISPLAY_KEY)
SELECT /*+ INDEX(bkup HXC_TIME_BUILDING_BLOCKS_PK) */
bkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
bkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
TRANSLATION_DISPLAY_KEY
FROM hxc_temp_timecard_chunks temp,
hxc_time_building_blocks bkup
WHERE bkup.scope = 'TIMECARD'
AND bkup.time_building_block_id = temp.id
AND bkup.object_version_number = temp.ref_ovn
AND bkup.data_set_id = p_data_set_id
AND thread_id = p_thread_id ;
DELETE
FROM hxc_time_building_blocks
WHERE time_building_block_id = tc_id_tab(i)
AND object_version_number = tc_ovn_tab(i) ;
p_insert => l_tc_count,
p_delete => l_tc_del_count,
p_mismatch => data_mismatch
);
INSERT INTO hxc_ar_detail_log
(time_building_block_id,
object_version_number,
process_type,
thread_id,
chunk_number)
VALUES (tc_id_tab(i),
tc_ovn_tab(i),
'ARCHIVE',
p_thread_id,
l_chunk_no);
INSERT INTO hxc_temp_timecard_chunks
( id,
scope,
ref_rowid,
thread_id )
SELECT /*+ ORDERED */
DISTINCT talbkup.application_period_id,
'APPLICATION_PERIOD',
ROWIDTOCHAR(talbkup.ROWID),
thread_id
FROM hxc_temp_timecard_chunks temp,
hxc_tc_ap_links talbkup
WHERE temp.id = talbkup.timecard_id
AND temp.scope IN ('TIMECARD')
AND thread_id = p_thread_id ;
INSERT INTO hxc_tc_ap_links_ar
( timecard_id,
application_period_id)
SELECT timecard_id,
application_period_id
FROM hxc_temp_timecard_chunks temp,
hxc_tc_ap_links talbkup
WHERE temp.ref_rowid = talbkup.ROWID
AND temp.scope = ('APPLICATION_PERIOD')
AND thread_id = p_thread_id ;
DELETE FROM hxc_tc_ap_links
WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
FROM hxc_temp_timecard_chunks
WHERE scope = 'APPLICATION_PERIOD'
AND thread_id = p_thread_id );
p_insert => l_tal_count,
p_delete => l_tal_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_time_building_blocks_ar
(DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
SELECT /*+ ORDERED */
p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
appbkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,
APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks appbkup
WHERE appbkup.scope = 'APPLICATION_PERIOD'
AND appbkup.time_building_block_id IN ( SELECT id
FROM hxc_temp_timecard_chunks temp
WHERE temp.scope IN
('APPLICATION_PERIOD')
AND thread_id = p_thread_id ) ;
DELETE FROM hxc_time_building_blocks
WHERE time_building_block_id IN ( SELECT id
FROM hxc_temp_timecard_chunks
WHERE scope IN
('APPLICATION_PERIOD')
AND thread_id = p_thread_id );
p_insert => l_app_period_count,
p_delete => l_app_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_app_period_summary_ar
(APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,TIME_RECIPIENT_ID,
TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,RECIPIENT_SEQUENCE,
CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,APPROVER_ID,APPROVAL_COMP_ID,
APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,APPROVAL_ITEM_KEY,DATA_SET_ID)
SELECT APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,
TIME_RECIPIENT_ID,TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,
RECIPIENT_SEQUENCE,CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,
APPROVER_ID,APPROVAL_COMP_ID,APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,
APPROVAL_ITEM_KEY,p_data_set_id
FROM hxc_app_period_summary apsbkup
WHERE application_period_id IN (SELECT id
FROM hxc_temp_timecard_chunks
WHERE scope = 'APPLICATION_PERIOD'
AND thread_id = p_thread_id );
DELETE FROM hxc_app_period_summary
WHERE application_period_id IN (SELECT id
FROM hxc_temp_timecard_chunks
WHERE scope = 'APPLICATION_PERIOD'
AND thread_id = p_thread_id );
p_insert => l_app_period_sum_count,
p_delete => l_app_sum_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_temp_timecard_chunks
( id,
ref_ovn,
scope,
ref_rowid,
day_start_time,
day_stop_time,
thread_id )
SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BUILDING_BLOCKS_FK3) */
time_building_block_id,
object_version_number,
'DAY',
ROWIDTOCHAR(ar.ROWID),
ar.start_time,
ar.stop_time,
thread_id
FROM hxc_temp_timecard_chunks temp,
hxc_time_building_blocks ar
WHERE parent_building_block_id = temp.id
AND parent_building_block_ovn = temp.ref_ovn
AND temp.scope = 'TIMECARD'
AND thread_id = p_thread_id ;
INSERT INTO hxc_time_building_blocks_ar
(DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
TRANSLATION_DISPLAY_KEY
FROM hxc_temp_timecard_chunks temp,
hxc_time_building_blocks bkupday
WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
AND temp.scope = 'DAY'
AND thread_id = p_thread_id;
DELETE FROM hxc_time_building_blocks
WHERE ROWID = CHARTOROWID(rowid_tab(i));
rowid_tab.DELETE;
p_insert => l_day_count,
p_delete => l_day_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_temp_timecard_chunks
( id,
ref_ovn,
scope,
day_start_time,
day_stop_time,
ref_rowid,
thread_id )
SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BUILDING_BLOCKS_FK3) */
time_building_block_id,
object_version_number,
'DETAIL',
nvl(day_start_time,ar.start_time),
nvl(day_stop_time,ar.stop_time),
ROWIDTOCHAR(ar.ROWID),
thread_id
FROM hxc_temp_timecard_chunks temp,
hxc_time_building_blocks ar
WHERE parent_building_block_id = temp.id
AND parent_building_block_ovn = temp.ref_ovn
AND temp.scope = 'DAY'
AND thread_id = p_thread_id ;
INSERT INTO hxc_time_building_blocks_ar
(DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
TRANSLATION_DISPLAY_KEY
FROM hxc_temp_timecard_chunks temp,
hxc_time_building_blocks bkupday
WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
AND temp.scope = 'DETAIL'
AND thread_id = p_thread_id ;
DELETE FROM hxc_time_building_blocks
WHERE ROWID = CHARTOROWID(rowid_tab(i));
rowid_tab.DELETE;
p_insert => l_det_count,
p_delete => l_det_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_archive_temp
( detail_id,
master_id,
ref_rowid,
thread_id )
VALUES ( trans_detail_tab(i),
trans_tab(i),
td_rowid_tab(i),
p_thread_id );
INSERT INTO hxc_transaction_details_ar
(DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
p_data_set_id,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
FROM hxc_archive_temp temp,
hxc_transaction_details bkuptxnd
WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
AND thread_id = p_thread_id ;
DELETE FROM hxc_transaction_details
WHERE ROWID = CHARTOROWID(td_rowid_tab(i));
trans_detail_tab.DELETE;
trans_tab.DELETE;
td_rowid_tab.DELETE;
DELETE FROM hxc_archive_temp
WHERE master_id = trans_id_tab(i)
AND ROWID <> uniq_rowid_tab(i)
AND thread_id = p_thread_id ;
DELETE FROM hxc_archive_temp
WHERE EXISTS ( SELECT 1
FROM hxc_transactions_ar
WHERE transaction_id = master_id )
AND thread_id = p_thread_id ;
INSERT INTO hxc_transactions_ar
(DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
p_data_set_id,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
TRANSACTION_CODE
FROM hxc_transactions bkuptxn,
hxc_archive_temp temp
WHERE transaction_id = master_id
AND thread_id = p_thread_id
AND transaction_id NOT IN ( SELECT transaction_id
FROM hxc_transactions_ar hxc
WHERE bkuptxn.transaction_id = hxc.transaction_id)
AND type <> 'RETRIEVAL'
;
DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
FROM hxc_transactions bkuptxn
WHERE transaction_id IN ( SELECT master_id
FROM hxc_archive_temp temp
WHERE thread_id = p_thread_id)
AND type <> 'RETRIEVAL';
INSERT INTO hxc_ar_trans_temp
( transaction_id, data_set_id, thread_id, trans_rowid )
SELECT bkuptxn.transaction_id,
p_data_set_id,
p_thread_id,
ROWIDTOCHAR(bkuptxn.ROWID)
FROM hxc_transactions bkuptxn,
hxc_archive_temp temp
WHERE transaction_id = master_id
AND thread_id = p_thread_id
AND type = 'RETRIEVAL';
DELETE FROM hxc_archive_temp
WHERE thread_id = p_thread_id ;
p_insert => l_td_count,
p_delete => l_td_del_count,
p_mismatch => data_mismatch );
p_insert => l_trans_count,
p_delete => l_trans_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_archive_temp
( detail_id,
master_id,
ref_rowid,
thread_id )
VALUES ( trans_detail_tab(i),
trans_tab(i),
td_rowid_tab(i),
p_thread_id );
INSERT INTO hxc_dep_txn_details_ar
(DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
p_data_set_id,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
FROM hxc_archive_temp temp,
hxc_dep_transaction_details bkuptxnd
WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
AND thread_id = p_thread_id ;
DELETE FROM hxc_dep_transaction_details
WHERE ROWID = CHARTOROWID(td_rowid_tab(i));
trans_detail_tab.DELETE;
trans_tab.DELETE;
td_rowid_tab.DELETE;
DELETE FROM hxc_archive_temp
WHERE master_id = trans_id_tab(i)
AND ROWID <> uniq_rowid_tab(i)
AND thread_id = p_thread_id ;
DELETE FROM hxc_archive_temp
WHERE EXISTS ( SELECT 1
FROM hxc_dep_transactions_ar
WHERE transaction_id = master_id )
AND thread_id = p_thread_id ;
INSERT INTO hxc_dep_transactions_ar
(DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
p_data_set_id,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
TRANSACTION_CODE
FROM hxc_dep_transactions bkuptxn,
hxc_archive_temp temp
WHERE transaction_id = master_id
AND thread_id = p_thread_id
AND transaction_id NOT IN ( SELECT transaction_id
FROM hxc_dep_transactions_ar hxc
WHERE bkuptxn.transaction_id = hxc.transaction_id)
;
DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
FROM hxc_dep_transactions bkuptxn
WHERE transaction_id IN ( SELECT master_id
FROM hxc_archive_temp temp
WHERE thread_id = p_thread_id) ;
DELETE FROM hxc_archive_temp
WHERE thread_id = p_thread_id ;
p_insert => l_td_count,
p_delete => l_td_del_count,
p_mismatch => data_mismatch );
p_insert => l_trans_count,
p_delete => l_trans_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_archive_temp
( detail_id,
master_id,
ref_rowid,
thread_id)
VALUES ( usage_tab(i),
attribute_tab(i),
usage_rowid_tab(i),
p_thread_id );
INSERT INTO hxc_time_attribute_usages_ar
(DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN)
SELECT /*+ LEADING(temp) USE_NL(bkuptau) */
p_data_set_id,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN
FROM hxc_archive_temp temp,
hxc_time_attribute_usages bkuptau
WHERE bkuptau.ROWID = CHARTOROWID(temp.ref_rowid)
AND thread_id = p_thread_id ;
DELETE FROM hxc_time_attribute_usages
WHERE ROWID = CHARTOROWID(usage_rowid_tab(i)) ;
DELETE FROM hxc_archive_temp
WHERE EXISTS ( SELECT 1
FROM hxc_time_attributes_ar
WHERE time_attribute_id = master_id )
AND thread_id = p_thread_id ;
INSERT INTO hxc_time_attributes_ar
(ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
ATTRIBUTE14,CONSOLIDATED_FLAG,DATA_SET_ID)
SELECT /*+ LEADING(temp) USE_NL(bkupta) */
ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
ATTRIBUTE14,null,p_data_set_id
FROM hxc_time_attributes bkupta
WHERE bkupta.time_attribute_id in ( SELECT /*+ NO_INDEX(temp) */
master_id
FROM hxc_archive_temp temp
WHERE thread_id = p_thread_id );
DELETE /*+ LEADING(temp) USE_NL(bkupta) */
FROM hxc_time_attributes bkupta
WHERE time_attribute_id IN ( SELECT /*+ NO_INDEX(temp) */
master_id
FROM hxc_archive_temp temp
WHERE thread_id = p_thread_id ) ;
usage_tab.DELETE;
attribute_tab.DELETE;
usage_rowid_tab.DELETE;
DELETE FROM hxc_archive_temp
WHERE thread_id = p_thread_id ;
p_insert => l_tau_count,
p_delete => l_tau_del_count,
p_mismatch => data_mismatch );
p_insert => l_ta_count,
p_delete => l_ta_del_count,
p_mismatch => data_mismatch );
DELETE FROM hxc_temp_timecard_chunks
WHERE scope IN ( 'TIMECARD', 'DAY','APPLICATION_PERIOD')
AND thread_id = p_thread_id ;
INSERT INTO hxc_archive_temp
( detail_id,
master_id,
thread_id)
VALUES ( sum_id_tab(i),
tim_id_tab(i),
p_thread_id);
INSERT INTO hxt_sum_hours_worked_f_ar
( state_name, county_name, city_name, zip_code, ffv_cost_center_id,
ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
separate_check_flag, seqno, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, actual_time_in, actual_time_out,
job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
effective_end_date, 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, object_version_number, time_building_block_id,
time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
hours, time_in, time_out, element_type_id, fcl_earn_reason_code,data_set_id )
SELECT DISTINCT state_name, county_name, city_name, zip_code, ffv_cost_center_id,
ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
separate_check_flag, seqno, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, actual_time_in, actual_time_out,
job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
effective_end_date, 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, object_version_number, time_building_block_id,
time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
hours, time_in, time_out, element_type_id, fcl_earn_reason_code ,p_data_set_id
FROM hxt_sum_hours_worked_f sum,
hxc_archive_temp temp
WHERE temp.detail_id = sum.id
AND temp.thread_id = p_thread_id;
DELETE FROM hxt_sum_hours_worked_f
WHERE id = sum_id_tab(i);
INSERT INTO hxt_det_hours_worked_f_ar
( data_set_id, state_name, county_name, city_name, zip_code, id, parent_id, line_status,
tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number )
SELECT DISTINCT
p_data_set_id, state_name, county_name, city_name, zip_code, id, parent_id, line_status,
tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number
FROM hxc_archive_temp temp,
hxt_det_hours_worked_f det
WHERE temp.detail_id = det.parent_id
AND temp.thread_id = p_thread_id;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = sum_id_tab(i);
UPDATE hxt_timecards_f
SET data_set_id = p_data_set_id
WHERE id = tim_id_tab(i);
DELETE FROM hxc_archive_temp
WHERE thread_id = p_thread_id;
p_insert => l_hxt_sum_count,
p_delete => l_hxt_sum_del_count,
p_mismatch => data_mismatch );
p_insert => l_hxt_det_count,
p_delete => l_hxt_det_del_count,
p_mismatch => data_mismatch );
INSERT INTO hxc_ap_detail_links_ar
(application_period_id,
time_building_block_id,
time_building_block_ovn)
SELECT application_period_id, time_building_block_id, time_building_block_ovn
FROM hxc_temp_timecard_chunks temp,
hxc_ap_detail_links adlbkup
WHERE temp.id = adlbkup.time_building_block_id
AND temp.ref_ovn = adlbkup.time_building_block_ovn
AND temp.scope = ('DETAIL')
AND thread_id = p_thread_id ;
DELETE FROM hxc_ap_detail_links
WHERE (time_building_block_id,time_building_block_ovn)
IN ( SELECT id,
ref_ovn
FROM hxc_temp_timecard_chunks
WHERE scope = 'DETAIL'
AND thread_id = p_thread_id );
p_insert => l_adl_count,
p_delete => l_adl_del_count,
p_mismatch => data_mismatch );
DELETE FROM hxc_latest_details
WHERE ROWID = CHARTOROWID(latest_rowid_tab(i)) ;
latest_rowid_tab.DELETE;
latest_id_tab.DELETE;
tc_id_tab.DELETE;
tc_ovn_tab.DELETE;
p_insert IN NUMBER,
p_delete IN NUMBER,
p_mismatch IN OUT NOCOPY BOOLEAN)
IS
BEGIN
IF p_insert = p_delete
THEN
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' '||p_scope||' records moved : '||p_insert);
fnd_file.put_line(fnd_file.log, p_insert||' records were inserted into offline table ');
fnd_file.put_line(fnd_file.log, p_delete||' records were deleted from online table ');