[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT status
FROM hxc_upgrade_definitions
WHERE upg_type = c_upg_type;
IS SELECT /*+ FIRST_ROWS */
time_building_block_id
FROM hxc_latest_details
WHERE org_id IS NULL
ORDER BY 1;
IS SELECT transaction_id
FROM hxc_transactions
WHERE type = 'DEPOSIT'
ORDER BY 1 asc;
IS SELECT hld.time_building_block_id
FROM hxc_latest_details hld
WHERE NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE hld.time_building_block_id = htd.time_building_block_id
AND hld.object_version_number = htd.time_building_block_ovn
AND htd.status = 'SUCCESS'
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND ht.transaction_process_id IN (p_retr_id, -1))
AND NOT EXISTS ( SELECT 1
FROM hxc_pay_latest_details hpl
WHERE hpl.time_building_block_id = hld.time_building_block_id
AND hpl.object_version_number = hld.object_version_number)
AND hld.application_set_id IN ( SELECT application_set_id
FROM hxc_application_set_comps_v
-- Bug 12609169
-- Added HR here
WHERE time_recipient_name IN ( 'Payroll','Human Resources'))
ORDER BY hld.time_building_block_id ;
IS SELECT hld.time_building_block_id
FROM hxc_latest_details hld
WHERE NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE hld.time_building_block_id = htd.time_building_block_id
AND hld.object_version_number = htd.time_building_block_ovn
AND htd.status = 'SUCCESS'
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND ht.transaction_process_id = p_retr_id)
AND NOT EXISTS ( SELECT 1
FROM hxc_pa_latest_details hpl
WHERE hpl.time_building_block_id = hld.time_building_block_id
AND hpl.object_version_number = hld.object_version_number)
AND hld.application_set_id IN ( SELECT application_set_id
FROM hxc_application_set_comps_v
WHERE time_recipient_name = 'Projects')
ORDER BY hld.time_building_block_id ;
IS SELECT DISTINCT sum.id
FROM hxc_time_building_blocks_ar ar,
hxt_sum_hours_worked_f sum
WHERE ar.time_building_block_id = sum.time_building_block_id
AND ar.object_version_number = sum.time_building_block_ovn
AND ar.scope = 'DETAIL';
IS SELECT timecard_id,
timecard_ovn
FROM hxc_timecard_summary
WHERE transferred_to IS NULL
ORDER BY timecard_id;
DELETE FROM HXC_UPGRADE_STATUS;
insert_into_upg_defn(p_type);
DELETE FROM hxc_upg_details_temp;
DELETE FROM hxc_upg_master_temp;
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upg_master_temp
(master_id,
master_ovn,
thread_id)
VALUES
(l_id_tab(i),
l_ovn_tab(i),
l_ind);
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
INSERT INTO hxc_upgrade_status
(parent_id,
child_id,
child_status)
VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
l_reqtab(l_ind).request_id,
'INCOMPLETE');
SELECT 1
INTO l_exists
FROM hxc_upgrade_status
WHERE child_status = 'INCOMPLETE'
AND ROWNUM < 2 ;
UPDATE hxc_upgrade_definitions
SET status = 'COMPLETE',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE upg_type = p_type;
IS SELECT /*+ LEADING(hld)
ORDERED
INDEX(hta hxc_time_attributes_pk) */
attribute1,
attribute2,
rowidtochar(hld.rowid)
FROM hxc_latest_details hld,
hxc_time_attribute_usages hau,
hxc_time_attributes hta
WHERE hld.time_building_block_id BETWEEN p_start
AND p_end
AND hld.org_id IS NULL
AND hau.time_building_block_id = hld.time_building_block_id
AND hau.time_building_block_ovn = hld.object_version_number
AND hta.time_attribute_id = hau.time_attribute_id
AND hta.attribute_category = 'SECURITY';
IS SELECT transaction_id
FROM hxc_transactions
WHERE transaction_id BETWEEN p_start
AND p_end
AND type = 'DEPOSIT'
ORDER BY 1 asc;
IS SELECT hld.business_group_id,
hld.org_id,
hld.resource_id,
hld.time_building_block_id,
hld.object_version_number,
hld.approval_status,
hld.start_time,
hld.stop_time,
hld.application_set_id,
hld.last_update_date,
hld.resource_type,
hld.comment_text,
tc.time_building_block_id
FROM hxc_latest_details hld,
hxc_time_building_blocks det,
hxc_time_building_blocks day,
hxc_time_building_blocks tc
WHERE hld.time_building_block_id BETWEEN p_start
AND p_end
AND hld.time_building_block_id = det.time_building_block_id
AND hld.object_version_number = det.object_version_number
AND day.time_building_block_id = det.parent_building_block_id
AND day.object_version_number = det.parent_building_block_ovn
AND tc.time_building_block_id = day.parent_building_block_id
AND tc.object_version_number = day.parent_building_block_ovn
AND NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.time_building_block_id = hld.time_building_block_id
AND htd.time_building_block_ovn = hld.object_version_number
AND htd.transaction_id = ht.transaction_id
AND htd.status = 'SUCCESS'
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND ht.transaction_process_id IN (p_ret_id,-1))
AND NOT EXISTS ( SELECT 1
FROM hxc_pay_latest_details hpd
WHERE hpd.time_building_block_id = hld.time_building_block_id
AND hpd.object_version_number = hld.object_version_number)
AND hld.application_set_id IN ( SELECT application_set_id
FROM hxc_application_set_comps_v
-- Bug 12609169
-- Added HR here
WHERE time_recipient_name IN ( 'Payroll','Human Resources'))
;
IS SELECT hld.business_group_id,
hld.org_id,
hld.resource_id,
hld.time_building_block_id,
hld.object_version_number,
hld.approval_status,
hld.start_time,
hld.stop_time,
hld.application_set_id,
hld.last_update_date,
hld.resource_type,
hld.comment_text,
tc.time_building_block_id
FROM hxc_latest_details hld,
hxc_time_building_blocks det,
hxc_time_building_blocks day,
hxc_time_building_blocks tc
WHERE hld.time_building_block_id BETWEEN p_start
AND p_end
AND hld.time_building_block_id = det.time_building_block_id
AND hld.object_version_number = det.object_version_number
AND day.time_building_block_id = det.parent_building_block_id
AND day.object_version_number = det.parent_building_block_ovn
AND tc.time_building_block_id = day.parent_building_block_id
AND tc.object_version_number = day.parent_building_block_ovn
AND NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.time_building_block_id = hld.time_building_block_id
AND htd.time_building_block_ovn = hld.object_version_number
AND htd.transaction_id = ht.transaction_id
AND htd.status = 'SUCCESS'
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND ht.transaction_process_id = p_ret_id )
AND NOT EXISTS ( SELECT 1
FROM hxc_pa_latest_details hpd
WHERE hpd.time_building_block_id = hld.time_building_block_id
AND hpd.object_version_number = hld.object_version_number)
AND hld.application_set_id IN ( SELECT application_set_id
FROM hxc_application_set_comps_v
WHERE time_recipient_name = 'Projects')
;
IS SELECT DISTINCT sum.id,
sum.tim_id,
ar.data_set_id
FROM hxt_sum_hours_worked_f sum,
hxc_time_building_blocks_ar ar
WHERE sum.id BETWEEN p_start
AND p_end
AND sum.time_building_block_id = ar.time_building_block_id
AND sum.time_building_block_ovn = ar.object_version_number ;
IS SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';
IS SELECT master_id
FROM hxc_upg_master_temp temp
WHERE thread_id = p_start
ORDER BY master_id ;
IS SELECT DISTINCT
temp.master_id,
temp.master_ovn,
hld.time_building_block_id,
hld.object_version_number,
DECODE(hrp.NAME, 'HR Retrieval Process', p_bee_id,
hrp.retrieval_process_id),
DECODE(detail.date_to, hr_general.end_of_time,NULL,'D')
FROM hxc_upg_master_temp temp,
hxc_time_building_blocks day,
hxc_time_building_blocks detail,
hxc_latest_details hld,
hxc_application_set_comps_v hasc,
hxc_retrieval_processes hrp
WHERE temp.master_id = day.parent_building_block_id
AND temp.master_ovn = day.parent_building_block_ovn
AND day.time_building_block_id = detail.parent_building_block_id
AND day.object_version_number = detail.parent_building_block_ovn
AND detail.time_building_block_id = hld.time_building_block_id
AND detail.object_version_number = hld.object_version_number
AND hld.application_set_id = hasc.application_set_id
AND hasc.time_recipient_id = hrp.time_recipient_id
AND thread_id = p_thread
AND temp.master_id BETWEEN p_start
AND p_end ;
CURSOR get_deleted_entries(p_thread_id NUMBER,
p_bee_id NUMBER)
IS SELECT ROWIDTOCHAR(temp.rowid)
FROM hxc_upg_details_temp temp
WHERE temp.thread_id = p_thread_id
AND temp.detail_attribute2 = 'D'
AND NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.time_building_block_id = temp.detail_id
AND htd.time_building_block_ovn < temp.detail_ovn
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND htd.status = 'SUCCESS'
AND DECODE(ht.transaction_process_id,-1,p_bee_id,
ht.transaction_process_id) = TO_NUMBER(temp.detail_attribute1)) ;
IS SELECT temp.master_id,
TO_NUMBER(temp.detail_attribute1)
FROM hxc_upg_details_temp temp
WHERE temp.thread_id = p_thread_id
AND NOT EXISTS ( SELECT 1
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.time_building_block_id = temp.detail_id
AND htd.time_building_block_ovn = temp.detail_ovn
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND htd.status = 'SUCCESS'
AND DECODE(ht.transaction_process_id,-1,p_bee_id,
ht.transaction_process_id) = TO_NUMBER(temp.detail_attribute1)) ;
IS SELECT DISTINCT master_id,
master_ovn,
htr.name
FROM hxc_upg_details_temp temp,
hxc_retrieval_processes hrp,
hxc_time_recipients htr
WHERE TO_NUMBER(temp.detail_attribute1) = hrp.retrieval_process_id
AND hrp.time_recipient_id = htr.time_recipient_id
AND temp.thread_id = p_thread_id
ORDER BY master_id,
htr.name;
last_update_date_tab DATETABLE;
UPDATE hxc_latest_details
SET org_id = orgtab(i),
business_group_id = bgtab(i)
WHERE rowid = CHARTOROWID(rowtab(i));
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
last_update_date_tab,
resource_type_tab,
comment_text_tab,
timecard_tab LIMIT 500;
INSERT INTO hxc_pay_latest_details
(business_group_id,
org_id,
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
resource_type,
comment_text,
timecard_id)
VALUES (business_group_tab(i),
org_tab(i),
resource_tab(i),
time_building_block_tab(i),
ovn_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
application_set_tab(i),
last_update_date_tab(i),
resource_type_tab(i),
comment_text_tab(i),
timecard_tab(i));
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
last_update_date_tab,
resource_type_tab,
comment_text_tab,
timecard_tab LIMIT 500;
INSERT INTO hxc_pa_latest_details
(business_group_id,
org_id,
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
resource_type,
comment_text,
timecard_id)
VALUES (business_group_tab(i),
org_tab(i),
resource_tab(i),
time_building_block_tab(i),
ovn_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
application_set_tab(i),
last_update_date_tab(i),
resource_type_tab(i),
comment_text_tab(i),
timecard_tab(i));
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
INSERT INTO hxc_archive_temp
(detail_id,master_id,thread_id)
VALUES (sumidtab(i),timidtab(i),dsidtab(i));
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 ,thread_id
FROM hxt_sum_hours_worked_f sum,
hxc_archive_temp temp
WHERE temp.detail_id = sum.id ;
DELETE FROM hxt_sum_hours_worked_f
WHERE id = sumidtab(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
thread_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 ;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = sumidtab(i);
UPDATE hxt_timecards_f
SET data_set_id = dsidtab(i)
WHERE id = timidtab(i);
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
INSERT INTO hxc_upg_details_temp
( master_id,
master_ovn,
detail_id,
detail_ovn,
detail_attribute1,
detail_attribute2,
thread_id)
VALUES ( tcidtab(i),
tcovntab(i),
detidtab(i),
detovntab(i),
rtidtab(i),
deltab(i),
l_thread_id);
OPEN get_deleted_entries(l_thread_id,
l_bee_id);
FETCH get_deleted_entries BULK COLLECT INTO ROWTAB LIMIT 500;
DELETE FROM hxc_upg_details_temp
WHERE ROWID = CHARTOROWID(rowtab(i));
CLOSE get_deleted_entries;
DELETE FROM hxc_upg_details_temp
WHERE master_id = msttab(i)
AND detail_attribute1 = rettab(i);
UPDATE hxc_timecard_summary
SET transferred_to = LTRIM(NVL(transferred_to,',')||','||xfertab(i),',')
WHERE timecard_id = tcsumidtab(i)
AND timecard_ovn = tcsumovntab(i);
DELETE FROM hxc_upg_details_temp
WHERE thread_id = l_thread_id;
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
-- Insert into new table HXC_DEP_TRANSACTIONS
FORALL i IN txnidtab.FIRST..txnidtab.LAST
INSERT
INTO hxc_dep_transactions
(SELECT transaction_id,
transaction_process_id,
transaction_date,
type,
status,
transaction_code,
exception_description,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
data_set_id
FROM hxc_transactions
WHERE transaction_id = txnidtab(i)
AND type = 'DEPOSIT') ;
DELETE
FROM hxc_transactions
WHERE transaction_id = txnidtab(i) ;
put_log('Deleted migrated records from HXC_TRANSACTIONS');
-- Insert into new table HXC_DEP_TRANSACTION_DETAILS
FORALL i IN txnidtab.FIRST..txnidtab.LAST
INSERT INTO hxc_dep_transaction_details
(SELECT 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,
data_set_id
FROM hxc_transaction_details
WHERE transaction_id = txnidtab(i)) ;
DELETE
FROM hxc_transaction_details
WHERE transaction_id = txnidtab(i) ;
put_log('Deleted migrated records from HXC_TRANSACTION_DETAILS');
UPDATE hxc_upgrade_status
SET child_status = 'COMPLETE'
WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT 1
INTO l_upgrade
FROM hxc_upgrade_definitions
WHERE upg_type = 'LATEST_DETAILS'
AND status = 'COMPLETE';
SELECT 1
INTO l_upgrade
FROM hxc_upgrade_definitions
WHERE upg_type = 'DEPOSIT_TRANSACTIONS'
AND status = 'COMPLETE';
PROCEDURE insert_into_upg_defn(p_upg_type IN VARCHAR2)
IS
BEGIN
INSERT INTO HXC_UPGRADE_DEFINITIONS
( UPG_TYPE,
STATUS,
last_updated_by,
last_update_date)
VALUES
( p_upg_type,
'INCOMPLETE',
FND_GLOBAL.user_id,
SYSDATE);
END insert_into_upg_defn;
IS SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = p_process_name;
IS SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'HXC_UPGRADE_DEFINITIONS'
AND lookup_code = p_lookup_code
AND language = USERENV('LANG');
SELECT 1
INTO l_upgrade
FROM hxc_upgrade_definitions
WHERE upg_type = p_upg_type
AND status = 'COMPLETE';