[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
t_timecard_deleted DBMS_SQL.varchar2_table;
t_detail_deleted DBMS_SQL.varchar2_table;
SELECT hxc_transactions_s.NEXTVAL
FROM SYS.DUAL;
SELECT /*+ INDEX_FFS(TXD HXC_TRANSACTION_DETAILS_PK) *
hxc_transaction_details_s.NEXTVAL
FROM hxc_transaction_details txd
WHERE ROWNUM <= p_max;
SELECT hxc_transaction_details_s.NEXTVAL
FROM SYS.DUAL
CONNECT BY LEVEL <= p_max ;
PROCEDURE insert_query (p_query LONG
/*PROFILER(457): LONG (advise: migrate to LOB) is Oracle 7.0 (deprecated in 8.1.5) */
, p_type VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM hxc_debug_text
WHERE process = 'RETRIEVAL' AND TYPE = p_type;
INSERT INTO hxc_debug_text
(process, TYPE, text
)
VALUES ('RETRIEVAL', p_type, p_query
);
END insert_query;
EXISTS ( select 1 from hxc_time_attribute_usages usage,
hxc_time_attributes att
where usage.time_building_Block_id = detail_block.time_building_block_id AND
usage.time_building_block_ovn = detail_block.object_version_number
and
att.time_attribute_id = usage.time_attribute_id and
att.';
EXISTS ( select 1 from hxc_time_attribute_usages usage,
hxc_time_attributes att
where usage.time_building_Block_id = timecard_block.time_building_block_id AND
usage.time_building_block_ovn = timecard_block.object_version_number
and
att.time_attribute_id = usage.time_attribute_id and
att.';
SELECT rr.retrieval_range_id, rr.range_start, rr.range_stop
FROM hxc_retrieval_ranges rr
WHERE rr.retrieval_process_id = p_process_id
AND rr.transaction_id = 0
AND ( rr.where_clause = g_params.p_where_clause
OR rr.where_clause IS NULL
)
ORDER BY rr.seq
FOR UPDATE OF transaction_id NOWAIT;
SELECT TYPE
FROM hxc_debug_text
WHERE process = p_range;
IS SELECT has.application_set_id
FROM hxc_retrieval_processes hrp,
hxc_application_set_comps_v has
WHERE hrp.name = DECODE(p_process,
'Apply Schedule Rules','BEE Retrieval Process',
p_process)
AND hrp.time_recipient_id = has.time_recipient_id;
SELECT DISTINCT tbb.resource_id
FROM hxc_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
SELECT DISTINCT tbb.resource_id
FROM hxc_pa_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
SELECT DISTINCT tbb.resource_id
FROM hxc_pay_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
SELECT DISTINCT tbb.resource_id
FROM hxc_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
SELECT DISTINCT tbb.resource_id
FROM hxc_pa_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
SELECT DISTINCT tbb.resource_id
FROM hxc_pay_latest_details tbb
WHERE tbb.last_update_date > :p_since_date ';
' INSERT INTO hxc_rdb_process_timecards
(timecard_id,
resource_id,
start_time,
stop_time,
stage,
request_id,
ret_user_id,
process)
SELECT /*+ INDEX(tbb LATEST_DETAILS_N2)
INDEX(sum HXC_TIME_BUILDING_BLOCKS_PK) */
DISTINCT tbb.timecard_id,
sum.resource_id,
sum.start_time,
TRUNC(sum.stop_time),
''PENDING'',
fnd_global.conc_request_id,
fnd_global.user_id,
RETRIEVALPROCESS
FROM LATEST_DETAILS tbb,
hxc_time_building_blocks sum
WHERE sum.resource_id = tbb.resource_id
AND sum.time_building_block_id = tbb.timecard_id
AND tbb.last_update_date > :p_since_date';
IS SELECT requested_start_date
FROM fnd_concurrent_requests
WHERE request_id = FND_GLOBAL.conc_request_id;
INSERT INTO HXC_RDB_PENDING_PROCESSES
( request_id,
concurrent_program_id,
user_id,
status)
VALUES ( FND_GLOBAL.conc_request_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.user_id,
'PENDING_SNAPSHOT');
INSERT INTO hxc_retrieval_requests_all
( request_id,
conc_program_id,
user_id,
request_date,
ret_trans_code)
VALUES ( FND_GLOBAL.conc_request_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.user_id,
l_request_date,
g_params.p_transaction_code);
insert_query (l_dynamic_sql, 'RANGE');
PROCEDURE insert_rr_resources (
p_resource_list IN DBMS_SQL.number_table,
p_rr_id IN OUT NOCOPY NUMBER
)
IS
CURSOR csr_get_rr_id
IS
SELECT hxc_retrieval_ranges_s.NEXTVAL
FROM DUAL;
l_proc := g_package || '.insert_rr_resources';
INSERT INTO hxc_retrieval_range_resources
(retrieval_range_id, resource_id
)
VALUES (l_rr_id, p_resource_list (rrx)
);
END insert_rr_resources;
SELECT 'x'
FROM hxc_retrieval_ranges rr
WHERE rr.retrieval_process_id = p_process_id
AND ( rr.where_clause IS NULL
OR rr.where_clause = g_params.p_where_clause
)
AND rr.transaction_id = 0;
SELECT 'x'
FROM hxc_transactions tx
WHERE tx.status = 'IN PROGRESS'
AND tx.TYPE = 'RETRIEVAL'
AND tx.transaction_process_id = p_process_id
AND EXISTS (SELECT 'y'
FROM hxc_retrieval_ranges rr
WHERE rr.transaction_id = tx.transaction_id
AND ( rr.where_clause IS NULL
OR rr.where_clause = g_params.p_where_clause)
);
hr_utility.TRACE ('Inserting rows');
insert_rr_resources (p_resource_list => t_resource_id,
p_rr_id => l_rr_id
);
t_resource_id.DELETE;
INSERT INTO hxc_retrieval_ranges
(retrieval_range_id,
retrieval_process_id,
range_start, range_stop,
seq, transaction_id, where_clause,
unique_params, conc_request_id
)
VALUES (t_rr_id (rrx),
t_retrieval_process_id (rrx),
t_range_start (rrx), t_range_stop (rrx),
t_seq (rrx), 0, t_where_clause (rrx),
g_params.p_unique_params, g_conc_request_id
);
hr_utility.TRACE ('After bulk insert');
UPDATE hxc_retrieval_ranges rr
SET rr.transaction_id =
hxc_generic_retrieval_pkg.g_transaction_id,
rr.conc_request_id = g_conc_request_id
WHERE rr.retrieval_range_id = r_range.retrieval_range_id;
SELECT TO_CHAR (time_building_block_id) tbb_id,
TO_CHAR (object_version_number) tbb_ovn
FROM hxc_retrieval_range_blks;
l_select_from VARCHAR2 (250)
:= '
SELECT /*+ ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number insert_latest
FROM hxc_retrieval_ranges rr
, hxc_retrieval_range_resources rrr
, hxc_latest_details tbb_latest ';
l_select_from_pa VARCHAR2 (250)
:= '
SELECT /*+ ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number insert_latest
FROM hxc_retrieval_ranges rr
, hxc_retrieval_range_resources rrr
, hxc_pa_latest_details tbb_latest ';
l_select_from_pay VARCHAR2 (250)
:= '
SELECT /*+ ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number insert_latest
FROM hxc_retrieval_ranges rr
, hxc_retrieval_range_resources rrr
, hxc_pay_latest_details tbb_latest ';
tbb_latest.last_update_date >= :p_since_date ';
AND NOT EXISTS (select /*+ INDEX(tx HXC_TRANSACTIONS_PK)
INDEX(txd HXC_TRANSACTION_DETAILS_FK1)*/
''x''
FROM hxc_transactions tx
, hxc_transaction_details txd
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number) ';
l_select_from := l_select_from_pa;
l_select_from := l_select_from_pay;
hxc_generic_retrieval_pkg.g_detail_skipped.DELETE(i);
update_rdb_status(g_temp_tc_list,
'PENDING',
'SKIPPED');
g_temp_tc_list.DELETE;
INSERT INTO hxc_rdb_process_details
( timecard_id,
detail_id,
detail_ovn,
skipped_reason,
skip_level,
ret_user_id,
request_id ,
process)
VALUES ( l_skipped_tc_id(i),
l_skipped_bb_id (i),
l_skipped_bb_ovn(i),
l_skipped_desc(i),
'OTL_PROC',
FND_GLOBAL.user_ID,
FND_GLOBAL.conc_request_id,
g_params.p_process);
g_res_list.DELETE(l_ind);
hr_utility.TRACE ('About to delete rows');
DELETE FROM hxc_retrieval_range_blks;
l_select_from
|| l_where
|| l_not_exists
|| l_app_set
|| l_ret_criteria_clause;
l_select_from
|| l_where
|| l_day_ex
|| l_not_exists
|| l_app_set
|| l_ret_criteria_clause;
l_select_from
|| l_where
|| l_day_in
|| l_not_exists
|| l_app_set
|| l_ret_criteria_clause;
insert_query (l_ret_range_query, 'RET_RANGE_BLKS');
INSERT INTO hxc_retrieval_range_blks
(time_building_block_id, object_version_number
)
VALUES (t_tbb_id (rrx), t_tbb_ovn (rrx)
);
t_tbb_id.DELETE;
t_tbb_ovn.DELETE;
SELECT time_building_block_id || ':' || max_ovn ovn
FROM hxc_max_ovn;
SELECT /*+ ORDERED
INDEX(TXD HXC_TRANSACTION_DETAILS_FK1)
INDEX(TX HXC_TRANSACTIONS_PK)
USE_NL(TXD, TX) */
txd.time_building_block_id,
NVL (MAX (txd.time_building_block_ovn), 0)
FROM hxc_retrieval_range_blks rrb,
hxc_transaction_details txd,
hxc_transactions tx
WHERE tx.transaction_process_id = p_retrieval_process_id
AND tx.TYPE = 'RETRIEVAL'
AND tx.status = 'SUCCESS'
AND tx.transaction_id = txd.transaction_id
AND txd.status = 'SUCCESS'
AND rrb.time_building_block_id = txd.time_building_block_id
AND rrb.object_version_number > txd.time_building_block_ovn
GROUP BY txd.time_building_block_id;
SELECT txd.time_building_block_id,
NVL (MAX (txd.time_building_block_ovn), 0)
FROM hxc_transaction_details txd, hxc_transactions tx
WHERE tx.transaction_process_id = p_retrieval_process_id
AND tx.TYPE = 'RETRIEVAL'
AND tx.status = 'SUCCESS'
AND tx.transaction_id = txd.transaction_id
AND txd.status = 'SUCCESS'
AND EXISTS (
SELECT 'x'
FROM hxc_latest_details tbb_det
WHERE tbb_det.start_time <= p_end_date
AND tbb_det.stop_time >= p_start_date
AND tbb_det.last_update_date > p_since_date
AND tbb_det.time_building_block_id =
txd.time_building_block_id
AND tbb_det.object_version_number >
txd.time_building_block_ovn
AND tbb_det.resource_id BETWEEN l_pkg_range_start
AND l_pkg_range_stop)
GROUP BY txd.time_building_block_id;
SELECT txd.time_building_block_id,
NVL (MAX (txd.time_building_block_ovn), 0)
FROM hxc_transaction_details txd, hxc_transactions tx
WHERE tx.transaction_process_id = p_retrieval_process_id
AND tx.TYPE = 'RETRIEVAL'
AND tx.status = 'SUCCESS'
AND tx.transaction_id = txd.transaction_id
AND txd.status = 'SUCCESS'
AND EXISTS (
SELECT 'x'
FROM hxc_latest_details tbb_det
WHERE tbb_det.time_building_block_id =
txd.time_building_block_id
AND tbb_det.last_update_date > p_since_date
AND tbb_det.object_version_number >
txd.time_building_block_ovn
AND tbb_det.resource_id BETWEEN l_pkg_range_start
AND l_pkg_range_stop)
GROUP BY txd.time_building_block_id;
DELETE FROM hxc_max_ovn;
INSERT INTO hxc_max_ovn
(time_building_block_id, max_ovn
)
VALUES (t_max_ovn_bb_id (x), t_max_ovn (x)
);
t_max_ovn_bb_id.DELETE;
t_max_ovn.DELETE;
INSERT INTO hxc_max_ovn
(time_building_block_id, max_ovn
)
VALUES (t_max_ovn_bb_id (x), t_max_ovn (x)
);
t_max_ovn_bb_id.DELETE;
t_max_ovn.DELETE;
hr_utility.TRACE ('inserting');
INSERT INTO hxc_max_ovn
(time_building_block_id, max_ovn
)
VALUES (t_max_ovn_bb_id (x), t_max_ovn (x)
);
t_max_ovn_bb_id.DELETE;
t_max_ovn.DELETE;
IF (p_mode = 'I') -- insert transactions
THEN
-- check to see if header already inserted
IF (hxc_generic_retrieval_pkg.g_transaction_id IS NOT NULL)
THEN
IF g_debug
THEN
hr_utility.set_location ('Processing ' || l_proc, 7);
UPDATE hxc_transactions
SET status = p_status,
exception_description = p_description
WHERE transaction_id = hxc_generic_retrieval_pkg.g_transaction_id;
INSERT INTO hxc_transactions
(transaction_id,
transaction_process_id, transaction_date,
transaction_code,
TYPE, status, exception_description
)
VALUES (hxc_generic_retrieval_pkg.g_transaction_id,
p_transaction_process_id, SYSDATE,
NVL (g_params.p_transaction_code,
TO_CHAR (SYSDATE, 'DD/MM/YYYY')
),
'RETRIEVAL', p_status, p_description
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_error_transaction_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn
(tx_error),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_error_status
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_exception
(tx_error)
);
hxc_generic_retrieval_pkg.t_tx_error_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
ELSIF (p_mode = 'U') -- update transactions
THEN
IF g_debug
THEN
hr_utility.set_location ('Processing ' || l_proc, 110);
INSERT INTO hxc_transactions
(transaction_id, transaction_process_id,
transaction_date, TYPE, status,
exception_description
)
VALUES (l_tx_id, p_transaction_process_id,
SYSDATE, 'RETRIEVAL_STATUS_UPDATE', p_status,
p_description
);
UPDATE hxc_transactions
SET status = p_status
WHERE transaction_id = hxc_generic_retrieval_pkg.g_transaction_id;
UPDATE hxc_transactions
SET status = p_status,
exception_description = p_description
WHERE transaction_id = hxc_generic_retrieval_pkg.g_transaction_id;
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_time_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_time_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_exception
(tx_detail)
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_day_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_day_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_exception
(tx_detail)
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_detail_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_exception
(tx_detail)
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_error_transaction_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn
(tx_error),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_error_status
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_exception
(tx_error)
);
g_temp_tc_list.DELETE;
UPDATE hxc_rdb_process_details
SET skipped_reason = SUBSTR(hxc_generic_retrieval_pkg.t_tx_error_exception
(tx_error),1,149),
skip_level = 'REC_PROC'
WHERE detail_id = hxc_generic_retrieval_pkg.t_tx_error_bb_id
(tx_error)
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND process = g_params.p_process
AND ret_user_id = FND_global.user_id
RETURNING timecard_id
BULK COLLECT INTO g_temp_tc_list ;
UPDATE hxc_rdb_process_timecards
SET stage = 'ERRORED'
WHERE timecard_id = g_temp_tc_list(i)
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND process = g_params.p_process;
hxc_generic_retrieval_pkg.t_tx_error_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
SELECT -1, rtr.time_recipient_id, rtr.mapping_id
FROM hxc_retrieval_processes rtr
WHERE rtr.NAME = 'BEE Retrieval Process';
SELECT rtr.retrieval_process_id, rtr.time_recipient_id,
rtr.mapping_id
FROM hxc_retrieval_processes rtr
WHERE rtr.NAME = p_retrieval_process;
t_bb (l_ind).time_deleted := t_timecard_deleted (x);
t_bb (l_ind).detail_deleted := t_detail_deleted (x);
t_timecard_bb_id.DELETE;
t_timecard_ovn.DELETE;
t_timecard_max_ovn.DELETE;
t_timecard_start_time.DELETE;
t_timecard_stop_time.DELETE;
t_timecard_comment_text.DELETE;
t_timecard_deleted.DELETE;
t_day_bb_id.DELETE;
t_day_start_time.DELETE;
t_day_stop_time.DELETE;
t_day_ovn.DELETE;
t_day_max_ovn.DELETE;
t_detail_bb_id.DELETE;
t_detail_parent_id.DELETE;
t_detail_resource_type.DELETE;
t_detail_resource_id.DELETE;
t_detail_comment_text.DELETE;
t_detail_start_time.DELETE;
t_detail_stop_time.DELETE;
t_detail_measure.DELETE;
t_detail_scope.DELETE;
t_detail_type.DELETE;
t_detail_ovn.DELETE;
t_detail_deleted.DELETE;
t_detail_max_ovn.DELETE;
t_detail_uom.DELETE;
t_detail_date_from.DELETE;
t_detail_date_to.DELETE;
t_detail_approval_status.DELETE;
t_detail_approval_style_id.DELETE;
t_detail_ta_id.DELETE;
t_detail_bld_blk_info_type_id.DELETE;
t_detail_attribute1.DELETE;
t_detail_attribute2.DELETE;
t_detail_attribute3.DELETE;
t_detail_attribute4.DELETE;
t_detail_attribute5.DELETE;
t_detail_attribute6.DELETE;
t_detail_attribute7.DELETE;
t_detail_attribute8.DELETE;
t_detail_attribute9.DELETE;
t_detail_attribute10.DELETE;
t_detail_attribute11.DELETE;
t_detail_attribute12.DELETE;
t_detail_attribute13.DELETE;
t_detail_attribute14.DELETE;
t_detail_attribute15.DELETE;
t_detail_attribute16.DELETE;
t_detail_attribute17.DELETE;
t_detail_attribute18.DELETE;
t_detail_attribute19.DELETE;
t_detail_attribute20.DELETE;
t_detail_attribute21.DELETE;
t_detail_attribute22.DELETE;
t_detail_attribute23.DELETE;
t_detail_attribute24.DELETE;
t_detail_attribute25.DELETE;
t_detail_attribute26.DELETE;
t_detail_attribute27.DELETE;
t_detail_attribute28.DELETE;
t_detail_attribute29.DELETE;
t_detail_attribute30.DELETE;
t_detail_attribute_category.DELETE;
SELECT COUNT (*)
FROM hxc_time_building_blocks
WHERE SCOPE = 'TIMECARD';
SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
UPDATE hxc_rdb_process_timecards
SET stage = 'SKIPPED'
WHERE ret_user_id = FND_GLOBAL.user_id
AND request_id = FND_GLOBAL.conc_request_id
AND process = g_params.p_process
AND stage = 'PENDING';
hxc_generic_retrieval_pkg.t_detail_bld_blks (l_bld_blk_index).deleted :=
t_bb (p_cnt).detail_deleted;
c_tab => t_detail_deleted,
cnt => l_max_array_size,
lower_bound => 1
);
c_tab => t_timecard_deleted,
cnt => l_max_array_size,
lower_bound => 1
);
c_tab => t_detail_deleted
);
c_tab => t_timecard_deleted
);
l_prefs.DELETE;
l_ret_rules.DELETE;
l_rtr_outcomes.DELETE;
t_time_bld_blk_info.DELETE;
t_day_bld_blk_info.DELETE;
t_detail_bld_blk_info.DELETE;
IF ( t_bb (cnt).detail_deleted = 'Y'
AND t_bb (cnt).detail_max_ovn = 0
)
THEN
l_day_retrieve := FALSE;
'Y', -- Bug 8366309; Deleted = 'Y'
p_bb_deleted => t_bb (cnt).time_deleted,
p_bb_start_time => t_bb (cnt).day_start_time,
p_bb_stop_time => t_bb (cnt).day_stop_time,
p_bb_id => t_bb (cnt).detail_bb_id,
p_bb_ovn => t_bb (cnt).detail_ovn,
p_attribute_category => l_detail_attribute_category, -- Bug 8779478
p_process => g_params.p_process,
p_prefs => l_prefs,
p_ret_rules => l_ret_rules,
p_rtr_outcomes => l_rtr_outcomes,
p_tc_bb_id => t_bb (cnt).time_bb_id,
p_tc_bb_ovn => t_bb (cnt).time_ovn,
p_timecard_retrieve => l_timecard_retrieve,
p_day_retrieve => l_day_retrieve,
p_tc_locked => l_tc_locked,
p_tc_first_lock => l_tc_first_lock,
p_bb_skipped_reason => l_bb_skipped_reason -- Bug 7595581
);
END IF; -- chk deleted and not transferred
l_bb_skipped_reason := 'Block is deleted and was not transferred earlier';
t_bb.DELETE;
hxc_generic_retrieval_utils.g_resources.DELETE;
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'WARNINGS',
p_description => 'HXC_0012_GNRET_NO_TIMECARDS'
);
t_bb.DELETE;
hxc_generic_retrieval_utils.g_resources.DELETE;
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'WARNINGS',
p_description => 'HXC_0013_GNRET_NO_BLD_BLKS'
);
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'WARNINGS',
p_description => 'HXC_0013_GNRET_NO_BLD_BLKS'
);
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'WARNINGS',
p_description => SUBSTR (SQLERRM,
1,
2000
)
);
SELECT 'x'
FROM hxc_tmp_bld_blks;
SELECT /*+ ORDERED INDEX(TBB) INDEX(DAY) USE_NL(TBB, DAY) */
tbb.time_building_block_id, tbb.object_version_number,
tbb.parent_building_block_id, tbb.resource_type,
tbb.resource_id, tbb.comment_text,
DECODE (tbb.TYPE,
'MEASURE', DAY.start_time,
tbb.start_time
),
DECODE (tbb.TYPE, 'MEASURE', DAY.stop_time, tbb.stop_time),
DECODE (tbb.TYPE,
'MEASURE', tbb.measure,
(tbb.stop_time - tbb.start_time
) * 24
),
tbb.SCOPE, tbb.TYPE,
DECODE (tbb.TYPE, 'MEASURE', tbb.unit_of_measure, 'HOURS'),
tbb.date_from, tbb.date_to, tbb.approval_status,
tbb.approval_style_id
FROM hxc_tmp_bld_blks tmp,
hxc_time_building_blocks tbb,
hxc_time_building_blocks DAY
WHERE tbb.time_building_block_id = tmp.time_building_block_id
AND tbb.object_version_number = tmp.time_building_block_ovn
AND DAY.time_building_block_id = tbb.parent_building_block_id
AND DAY.object_version_number = tbb.parent_building_block_ovn
ORDER BY tmp.seq;
SELECT /*+ ORDERED INDEX(TAU) INDEX(TA) USE_NL(TAU, TA) */
tmp.time_building_block_id, ta.bld_blk_info_type_id,
ta.attribute_category, ta.attribute1, ta.attribute2,
ta.attribute3, ta.attribute4, ta.attribute5, ta.attribute6,
ta.attribute7, ta.attribute8, ta.attribute9, ta.attribute10,
ta.attribute11, ta.attribute12, ta.attribute13,
ta.attribute14, ta.attribute15, ta.attribute16,
ta.attribute17, ta.attribute18, ta.attribute19,
ta.attribute20, ta.attribute21, ta.attribute22,
ta.attribute23, ta.attribute24, ta.attribute25,
ta.attribute26, ta.attribute27, ta.attribute28,
ta.attribute29, ta.attribute30
FROM hxc_tmp_bld_blks tmp,
hxc_time_attribute_usages tau,
hxc_time_attributes ta
WHERE tau.time_building_block_id = tmp.time_building_block_id
AND tau.time_building_block_ovn = tmp.time_building_block_ovn
AND ta.time_attribute_id = tau.time_attribute_id
ORDER BY tmp.seq;
INSERT INTO hxc_tmp_bld_blks
(seq, time_building_block_id,
time_building_block_ovn
)
VALUES (t_old_detail_seq (x), t_old_detail_bb_id (x),
t_old_detail_ovn (x)
);
t_bb.DELETE;
t_old_bb_id.DELETE;
t_old_att_bb_id.DELETE;
t_old_parent_id.DELETE;
t_old_resource_type.DELETE;
t_old_resource_id.DELETE;
t_old_comment_text.DELETE;
t_old_start_time.DELETE;
t_old_stop_time.DELETE;
t_old_measure.DELETE;
t_old_scope.DELETE;
t_old_type.DELETE;
t_old_uom.DELETE;
t_old_date_from.DELETE;
t_old_date_to.DELETE;
t_old_approval_status.DELETE;
t_old_approval_style_id.DELETE;
t_old_bld_blk_info_type_id.DELETE;
t_old_attribute1.DELETE;
t_old_attribute2.DELETE;
t_old_attribute3.DELETE;
t_old_attribute4.DELETE;
t_old_attribute5.DELETE;
t_old_attribute6.DELETE;
t_old_attribute7.DELETE;
t_old_attribute8.DELETE;
t_old_attribute9.DELETE;
t_old_attribute10.DELETE;
t_old_attribute11.DELETE;
t_old_attribute12.DELETE;
t_old_attribute13.DELETE;
t_old_attribute14.DELETE;
t_old_attribute15.DELETE;
t_old_attribute16.DELETE;
t_old_attribute17.DELETE;
t_old_attribute18.DELETE;
t_old_attribute19.DELETE;
t_old_attribute20.DELETE;
t_old_attribute21.DELETE;
t_old_attribute22.DELETE;
t_old_attribute23.DELETE;
t_old_attribute24.DELETE;
t_old_attribute25.DELETE;
t_old_attribute26.DELETE;
t_old_attribute27.DELETE;
t_old_attribute28.DELETE;
t_old_attribute29.DELETE;
t_old_attribute30.DELETE;
SELECT 'Y'
FROM hxc_debug
WHERE process = 'RETRIEVAL' AND TRUNC (debug_date) =
TRUNC (SYSDATE);
SELECT /*+ ordered use_nl( tbb
detail_block detail_usage detail_att
day_block timecard_block
detail_max_ovn ) */ ';
l_incremental_select VARCHAR2 (1860)
:= '
timecard_block.time_building_block_id
, timecard_block.object_version_number
, day_block.time_building_block_id
, day_block.object_version_number
, day_block.start_time
, day_block.stop_time
, detail_block.time_building_block_id
, detail_block.object_version_number
, detail_block.parent_building_block_id
, detail_block.resource_type
, detail_block.resource_id
, detail_block.comment_text
, detail_block.start_time
, detail_block.stop_time
, detail_block.measure
, detail_block.scope
, detail_block.type
, detail_att.time_attribute_id
, detail_att.bld_blk_info_type_id
, detail_att.attribute1
, detail_att.attribute2
, detail_att.attribute3
, detail_att.attribute4
, detail_att.attribute5
, detail_att.attribute6
, detail_att.attribute7
, detail_att.attribute8
, detail_att.attribute9
, detail_att.attribute10
, detail_att.attribute11
, detail_att.attribute12
, detail_att.attribute13
, detail_att.attribute14
, detail_att.attribute15
, detail_att.attribute16
, detail_att.attribute17
, detail_att.attribute18
, detail_att.attribute19
, detail_att.attribute20
, detail_att.attribute21
, detail_att.attribute22
, detail_att.attribute23
, detail_att.attribute24
, detail_att.attribute25
, detail_att.attribute26
, detail_att.attribute27
, detail_att.attribute28
, detail_att.attribute29
, detail_att.attribute30
, detail_block.date_from
, detail_block.date_to
, detail_block.approval_status
, detail_block.approval_style_id
, DECODE ( detail_block.date_to, hr_general.end_of_time, ''N'', ''Y'' )
, detail_att.attribute_category
, 1
, 1
, NVL(detail_max_ovn.max_ovn, 0)
, detail_block.unit_of_measure
, timecard_block.start_time
, timecard_block.stop_time
, timecard_block.comment_text
, DECODE ( timecard_block.date_to, hr_general.end_of_time, ''N'', ''Y'' ) ';
l_select VARCHAR2 (1945)
:= '
SELECT /*+ ordered use_nl(
detail_block detail_usage detail_att
day_block timecard_block
detail_max_ovn ) */
timecard_block.time_building_block_id
, timecard_block.object_version_number
, day_block.time_building_block_id
, day_block.object_version_number
, day_block.start_time
, day_block.stop_time
, detail_block.time_building_block_id
, detail_block.object_version_number
, detail_block.parent_building_block_id
, detail_block.resource_type
, detail_block.resource_id
, detail_block.comment_text
, detail_block.start_time
, detail_block.stop_time
, detail_block.measure
, detail_block.scope
, detail_block.type
, detail_att.time_attribute_id
, detail_att.bld_blk_info_type_id
, detail_att.attribute1
, detail_att.attribute2
, detail_att.attribute3
, detail_att.attribute4
, detail_att.attribute5
, detail_att.attribute6
, detail_att.attribute7
, detail_att.attribute8
, detail_att.attribute9
, detail_att.attribute10
, detail_att.attribute11
, detail_att.attribute12
, detail_att.attribute13
, detail_att.attribute14
, detail_att.attribute15
, detail_att.attribute16
, detail_att.attribute17
, detail_att.attribute18
, detail_att.attribute19
, detail_att.attribute20
, detail_att.attribute21
, detail_att.attribute22
, detail_att.attribute23
, detail_att.attribute24
, detail_att.attribute25
, detail_att.attribute26
, detail_att.attribute27
, detail_att.attribute28
, detail_att.attribute29
, detail_att.attribute30
, detail_block.date_from
, detail_block.date_to
, detail_block.approval_status
, detail_block.approval_style_id
, DECODE ( detail_block.date_to, hr_general.end_of_time, ''N'', ''Y'' )
, detail_att.attribute_category
, 1
, 1
, detail_block.object_version_number -1
, detail_block.unit_of_measure
, timecard_block.start_time
, timecard_block.stop_time
, timecard_block.comment_text
, DECODE ( timecard_block.date_to, hr_general.end_of_time, ''N'', ''Y'' ) ';
(select /*+ no_merge ordered */
time_building_block_id,
object_version_number
from Hxc_latest_details tbb_latest
where tbb_latest.resource_id BETWEEN :p_lower_range and :p_upper_range
and tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
(select /*+ no_merge ordered */
time_building_block_id,
object_version_number
from Hxc_pa_latest_details tbb_latest
where tbb_latest.resource_id BETWEEN :p_lower_range and :p_upper_range
and tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
(select /*+ no_merge ordered */
time_building_block_id,
object_version_number
from Hxc_pay_latest_details tbb_latest
where tbb_latest.resource_id BETWEEN :p_lower_range and :p_upper_range
and tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
(select /*+ no_merge ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number
from hxc_retrieval_ranges rr,
hxc_retrieval_range_resources rrr,
hxc_latest_details tbb_latest
where rr.retrieval_range_id = :p_rr_id AND
rr.retrieval_range_id = rrr.retrieval_range_id AND
tbb_latest.resource_id = rrr.resource_id AND
tbb_latest.last_update_date > :p_since_date
AND tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
(select /*+ no_merge ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number
from hxc_retrieval_ranges rr,
hxc_retrieval_range_resources rrr,
hxc_pa_latest_details tbb_latest
where rr.retrieval_range_id = :p_rr_id AND
rr.retrieval_range_id = rrr.retrieval_range_id AND
tbb_latest.resource_id = rrr.resource_id AND
tbb_latest.last_update_date > :p_since_date
AND tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
(select /*+ no_merge ordered */
tbb_latest.time_building_block_id,
tbb_latest.object_version_number
from hxc_retrieval_ranges rr,
hxc_retrieval_range_resources rrr,
hxc_pay_latest_details tbb_latest
where rr.retrieval_range_id = :p_rr_id AND
rr.retrieval_range_id = rrr.retrieval_range_id AND
tbb_latest.resource_id = rrr.resource_id AND
tbb_latest.last_update_date > :p_since_date
AND tbb_latest.approval_status <> ''ERROR''
AND NOT EXISTS (select ''x''
FROM hxc_transaction_details txd
, hxc_transactions tx
WHERE tx.transaction_process_id = :p_process_id
AND tx.type = ''RETRIEVAL''
AND tx.status = ''SUCCESS''
AND tx.transaction_id = txd.transaction_id
AND txd.status = ''SUCCESS''
AND txd.time_building_block_id = tbb_latest.time_building_block_id
AND txd.time_building_block_ovn = tbb_latest.object_version_number
)
';
SELECT /*+ no_unnest */ MAX ( tovn.object_version_number )
FROM hxc_time_building_blocks tovn
WHERE tovn.time_building_block_id = timecard_block.time_building_block_id ) ';
SELECT /*+ no_unnest */ MAX ( tovn.object_version_number )
FROM hxc_time_building_blocks tovn
WHERE tovn.time_building_block_id = timecard_block.time_building_block_id )
AND
timecard_block.start_time
BETWEEN :p_start_date AND :p_end_date AND
timecard_block.stop_time
BETWEEN :p_start_date AND :p_end_date ';
SELECT /*+ no_unnest */ MAX ( tovn.object_version_number )
FROM hxc_time_building_blocks tovn
WHERE tovn.time_building_block_id = timecard_block.time_building_block_id )
AND
:p_start_date <= timecard_block.stop_time AND
:p_end_date >= timecard_block.start_time ';
SELECT /*+ no_unnest */ MAX ( dyovn.object_version_number )
FROM hxc_time_building_blocks dyovn
WHERE dyovn.time_building_block_id = day_block.time_building_block_id ) ';
l_time_store_query := l_select || l_rerun || l_order_by;
|| l_incremental_select
|| l_incremental_from
|| l_time_store_query
|| l_not_exists
|| l_latest_double_check
|| l_order_by;
|| l_incremental_select
|| l_incremental_from
|| l_time_store_query
|| p_where_clause_blk
|| p_where_clause_att
|| l_not_exists
|| l_latest_double_check
|| l_order_by;
|| l_incremental_select
|| l_inline_view_range
|| l_inline_day
|| l_app_set
|| p_where_clause_blk
|| l_noloop_from
|| l_time_store_query
|| p_where_clause_att
|| l_not_exists
|| l_order_by;
l_select
|| l_from
|| l_time_store_query
|| l_app_set
|| p_where_clause_blk
|| p_where_clause_att
|| l_order_by;
SELECT transaction_id
FROM hxc_transactions tx
WHERE transaction_process_id = p_process_id
AND status = 'IN PROGRESS';
SELECT 'x'
FROM hxc_retrieval_ranges rr
WHERE rr.transaction_id = p_transaction_id
AND ( rr.where_clause = p_where_clause
OR (p_where_clause IS NULL AND rr.where_clause IS NULL)
)
AND ( rr.unique_params = p_unique_params
OR (p_unique_params IS NULL AND rr.unique_params IS NULL
)
);
SELECT TO_CHAR (SYSDATE, 'HH24:MI:SS DD-MON-YY')
FROM hxc_retrieval_ranges
WHERE transaction_id = p_transaction_id;
SELECT 'x'
FROM hxc_data_sets hds
WHERE hds.start_date <= p_end_date
AND hds.end_date >= p_start_date
AND hds.status IN
('OFF_LINE', 'RESTORE_IN_PROGRESS',
'BACKUP_IN_PROGRESS');
SELECT mpc.bld_blk_info_type_id, UPPER (mpc.field_name),
mpc.SEGMENT, bbit.bld_blk_info_type CONTEXT,
bbitu.building_block_category CATEGORY
FROM hxc_bld_blk_info_type_usages bbitu,
hxc_bld_blk_info_types bbit,
hxc_mapping_components mpc,
hxc_mapping_comp_usages mcu,
hxc_mappings MAP
WHERE MAP.mapping_id = p_mapping_id
AND mcu.mapping_id = MAP.mapping_id
AND mpc.mapping_component_id = mcu.mapping_component_id
AND bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id
AND bbitu.bld_blk_info_type_id = bbit.bld_blk_info_type_id
ORDER BY 1, 2, 3;
hxc_generic_retrieval_pkg.t_detail_bld_blks.DELETE;
hxc_generic_retrieval_pkg.t_old_detail_bld_blks.DELETE;
hxc_generic_retrieval_pkg.t_detail_attributes.DELETE;
hxc_generic_retrieval_pkg.t_old_detail_attributes.DELETE;
hxc_generic_retrieval_pkg.t_time_bld_blks.DELETE;
t_old_detail_seq.DELETE;
t_old_detail_bb_id.DELETE;
t_old_detail_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_time_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_time_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_time_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_time_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_time_exception.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_parent_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_day_exception.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_parent_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_detail_exception.DELETE;
hxc_generic_retrieval_pkg.t_detail_rec_lines.DELETE;
hxc_generic_retrieval_pkg.t_old_detail_rec_lines.DELETE;
hxc_generic_retrieval_pkg.g_old_bb_ids.DELETE;
hxc_generic_retrieval_pkg.t_bb.DELETE;
hxc_generic_retrieval_utils.g_resources.DELETE;
hxc_generic_retrieval_pkg.g_detail_skipped.DELETE;
g_field_mappings_table.DELETE;
SELECT DISTINCT application_set_id
FROM hxc_application_set_comps_v
WHERE time_recipient_id IN
(p_ret_tr_id_1, p_ret_tr_id_2);
SELECT tr.time_recipient_id
FROM hxc_time_recipients tr
WHERE tr.application_id = p_application_id;
SELECT TO_NUMBER (l_since_date)
INTO l_since_date
FROM DUAL;
SELECT rp.retrieval_process_id
INTO l_ret_id
FROM hxc_retrieval_processes rp
WHERE rp.NAME = 'BEE Retrieval Process';
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'IN PROGRESS',
p_description => ''
);
insert_query (l_dynamic_query, 'QUERY');
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'ERRORS',
p_description => SUBSTR (SQLERRM,
1,
2000
)
);
update_rdb_status(g_temp_tc_list,
'PENDING',
'SKIPPED');
g_temp_tc_list.DELETE;
INSERT INTO hxc_rdb_process_details
( timecard_id,
detail_id,
detail_ovn,
skipped_reason,
skip_level,
ret_user_id,
request_id,
process)
VALUES ( l_skipped_tc_id(i),
l_skipped_bb_id (i),
l_skipped_bb_ovn(i),
l_skipped_desc(i),
'OTL_PROC',
FND_GLOBAL.user_ID,
FND_GLOBAL.conc_request_id,
g_params.p_process);
update_rdb_status(g_temp_tc_list,
'PENDING',
'PROCESSING');
update_rdb_status(g_temp_tc_list,
'SKIPPED',
'PROCESSING_PARTIAL');
g_temp_tc_list.DELETE;
INSERT INTO hxc_rdb_process_details
( timecard_id,
detail_id,
detail_ovn,
skipped_reason,
skip_level,
ret_user_id,
request_id,
process)
VALUES ( l_skipped_tc_id(i),
l_skipped_bb_id (i),
l_skipped_bb_ovn(i),
l_skipped_desc(i),
'OTL_PROC',
FND_GLOBAL.user_ID,
FND_GLOBAL.conc_request_id,
g_params.p_process);
(p_mode => 'I' -- Insert
,
p_transaction_process_id => g_retrieval_process_id,
p_status => 'ERRORS',
p_description => SUBSTR (SQLERRM,
1,
2000
)
);
PROCEDURE delete_retrieval_ranges (p_transaction_id IN hxc_transactions.transaction_id%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM HXC_RETRIEVAL_RANGE_RESOURCES
WHERE RETRIEVAL_RANGE_ID IN
( SELECT RETRIEVAL_RANGE_ID
FROM HXC_RETRIEVAL_RANGES
WHERE CONC_REQUEST_ID = g_conc_request_id
AND TRANSACTION_ID = p_transaction_id);
DELETE FROM HXC_RETRIEVAL_RANGES
WHERE CONC_REQUEST_ID = g_conc_request_id
AND TRANSACTION_ID = p_transaction_id;
END delete_retrieval_ranges;
PROCEDURE update_transaction_status (
p_process hxc_retrieval_processes.NAME%TYPE,
p_status hxc_transactions.status%TYPE,
p_exception_description hxc_transactions.exception_description%TYPE,
p_rollback BOOLEAN DEFAULT FALSE
)
IS
-- going to call the chk_retrieval_process procedure - do not need mapping id
l_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
l_proc := g_package || 'update_transaction_status';
hr_utility.TRACE ('In Update Transaction Status');
DELETE FROM hxc_bee_pref_adj_lines
WHERE detail_bb_id = t_tx_detail_bb_id(i)
AND batch_source = DECODE(g_params.p_process,
'BEE Retrieval Process', 'OTM',
'Apply Schedule Rules' , 'Time Store')
AND t_tx_detail_status(i) = 'ERRORS' ;
audit_transaction (p_mode => 'U' -- update
,
p_transaction_process_id => l_process_id,
p_status => p_status,
p_description => p_exception_description,
p_rollback => p_rollback
);
INSERT INTO hxc_transactions
(transaction_id, transaction_process_id,
transaction_date, TYPE, status,
exception_description
)
VALUES (l_tx_id, l_process_id,
SYSDATE, 'RETRIEVAL_STATUS_UPDATE', p_status,
p_exception_description
);
UPDATE hxc_transactions
SET status = p_status,
exception_description = p_exception_description
WHERE transaction_id = hxc_generic_retrieval_pkg.g_transaction_id;
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_time_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_time_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_time_exception
(tx_detail)
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_day_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_day_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_day_exception
(tx_detail)
);
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_detail_transaction_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_bb_id
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_bb_ovn
(tx_detail),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_detail),
hxc_generic_retrieval_pkg.t_tx_detail_exception
(tx_detail)
);
g_tc_ovn_array.DELETE(g_timecards_array(hxc_generic_retrieval_pkg.t_tx_detail_bb_id(i)));
g_timecards_array.DELETE(hxc_generic_retrieval_pkg.t_tx_detail_bb_id(i));
DELETE FROM hxc_pa_latest_details
WHERE time_building_block_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'SUCCESS'
RETURNING
resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number
BULK
COLLECT INTO
t_l_resource_id,
t_l_time_building_block_id,
t_l_approval_status,
t_l_start_time,
t_l_stop_time,
t_l_org_id,
t_l_business_group_id,
t_l_timecard_id,
t_l_attribute1,
t_l_attribute2,
t_l_attribute3,
t_l_measure,
t_l_object_version_number;
UPDATE /*+ INDEX(ret HXC_RET_PA_LATEST_DETAILS_FK1) */
hxc_ret_pa_latest_details ret
SET old_attribute1 = attribute1,
old_attribute2 = attribute2,
old_attribute3 = attribute3,
old_measure = measure,
old_ovn = object_version_number,
old_pei_id = pei_id,
old_exp_group = exp_group,
pei_id = NULL,
exp_group = NULL,
measure = t_l_measure(i),
attribute1 = t_l_attribute1(i),
attribute2 = t_l_attribute2(i),
attribute3 = t_l_attribute3(i),
object_version_number = t_l_object_version_number(i),
business_group_id = t_l_business_group_id(i),
org_id = t_l_org_id(i),
approval_status = t_l_approval_status(i),
old_request_id = request_id,
request_id = FND_GLOBAL.conc_request_id
WHERE time_building_block_id = t_l_time_building_block_id(i)
RETURNING time_building_block_id
BULK COLLECT INTO l_old_tbb;
INSERT INTO hxc_ret_pa_latest_details
(resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
request_id
)
VALUES (
l_resource_id(i),
l_time_building_block_id(i),
l_approval_status(i),
l_start_time(i),
l_stop_time(i),
l_org_id(i),
l_business_group_id(i),
l_timecard_id(i),
l_attribute1(i),
l_attribute2(i),
l_attribute3(i),
l_measure(i),
l_object_version_number(i),
FND_GLOBAL.conc_request_id);
g_temp_tc_list.DELETE;
DELETE FROM hxc_rdb_process_details
WHERE detail_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'SUCCESS'
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND ret_user_id = FND_global.user_id
AND process = g_params.p_process
RETURNING timecard_id
BULK
COLLECT INTO l_success_tc ;
UPDATE hxc_rdb_process_details
SET skip_level = 'REC_PROC',
skipped_reason = SUBSTR(hxc_generic_retrieval_pkg.t_tx_detail_exception
(tx_error),1,149)
WHERE detail_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'ERRORS'
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND ret_user_id = FND_global.user_id
AND process = g_params.p_process
RETURNING timecard_id
BULK
COLLECT INTO l_error_tc ;
update_rdb_status((l_success_tc MULTISET EXCEPT l_error_tc),
'PROCESSING',
'PROCESSED');
update_rdb_status((l_success_tc MULTISET EXCEPT l_error_tc),
'PROCESSING_PARTIAL',
'PROCESSED_PARTIALLY');
update_rdb_status((l_error_tc MULTISET EXCEPT l_success_tc),
'PROCESSING',
'ERRORED');
update_rdb_status((l_error_tc MULTISET EXCEPT l_success_tc),
'PROCESSING_PARTIAL',
'ERRORED');
update_rdb_status((l_error_tc MULTISET INTERSECT l_success_tc),
'PROCESSING',
'PROCESSED_PARTIALLY');
update_rdb_status((l_error_tc MULTISET INTERSECT l_success_tc),
'PROCESSING_PARTIAL',
'PROCESSED_PARTIALLY');
g_temp_tc_list.DELETE;
DELETE FROM hxc_pay_latest_details
WHERE time_building_block_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'SUCCESS'
RETURNING
resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
application_set_id
BULK COLLECT INTO
t_l_resource_id,
t_l_time_building_block_id,
t_l_approval_status,
t_l_start_time,
t_l_stop_time,
t_l_org_id,
t_l_business_group_id,
t_l_timecard_id,
t_l_attribute1,
t_l_attribute2,
t_l_attribute3,
t_l_measure,
t_l_object_version_number,
t_l_application_set_id;
UPDATE hxc_ret_pay_latest_details
SET old_attribute1 = attribute1,
old_attribute2 = attribute2,
old_attribute3 = attribute3,
old_measure = measure,
old_ovn = object_version_number,
measure = t_l_measure(i),
attribute1 = t_l_attribute1(i),
attribute2 = t_l_attribute2(i),
attribute3 = t_l_attribute3(i),
object_version_number = t_l_object_version_number(i),
business_group_id = t_l_business_group_id(i),
org_id = t_l_org_id(i),
approval_status = t_l_approval_status(i),
old_request_id = request_id,
old_batch_id = batch_id,
request_id = FND_global.conc_request_id,
application_set_id = t_l_application_set_id(i)
WHERE time_building_block_id = t_l_time_building_block_id(i)
AND old_batch_id IS NULL
RETURNING time_building_block_id
BULK COLLECT INTO l_old_tbb;
UPDATE hxc_ret_pay_latest_details
SET old_attribute1 = attribute1,
old_attribute2 = attribute2,
old_attribute3 = attribute3,
old_measure = measure,
old_ovn = object_version_number,
measure = t_l_measure(i),
attribute1 = t_l_attribute1(i),
attribute2 = t_l_attribute2(i),
attribute3 = t_l_attribute3(i),
object_version_number = t_l_object_version_number(i),
business_group_id = t_l_business_group_id(i),
org_id = t_l_org_id(i),
approval_status = t_l_approval_status(i),
old_request_id = request_id,
old_batch_id = batch_id,
request_id = FND_global.conc_request_id,
application_set_id = t_l_application_set_id(i)
WHERE time_building_block_id = t_l_time_building_block_id(i)
RETURNING time_building_block_id
BULK COLLECT INTO l_old_tbb;
INSERT INTO hxc_ret_pay_latest_details
(resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
request_id,
application_set_id
)
VALUES (
l_resource_id(i),
l_time_building_block_id(i),
l_approval_status(i),
l_start_time(i),
l_stop_time(i),
l_org_id(i),
l_business_group_id(i),
l_timecard_id(i),
l_attribute1(i),
l_attribute2(i),
l_attribute3(i),
l_measure(i),
l_object_version_number(i),
FND_GLOBAL.conc_request_id,
l_application_set_id(i));
INSERT INTO hxc_ret_pay_details
(resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
request_id
)
VALUES (
t_l_resource_id(i),
t_l_time_building_block_id(i),
t_l_approval_status(i),
t_l_start_time(i),
t_l_stop_time(i),
t_l_org_id(i),
t_l_business_group_id(i),
t_l_timecard_id(i),
t_l_attribute1(i),
t_l_attribute2(i),
t_l_attribute3(i),
t_l_measure(i),
t_l_object_version_number(i),
FND_GLOBAL.conc_request_id);
UPDATE hxc_ret_pay_latest_details
SET old_pbl_id = pbl_id,
pbl_id = l_rec_id(i),
batch_id = l_batch_id(i)
WHERE time_building_block_id = l_rec_bb_id(i)
AND object_version_number = l_rec_ovn(i);
UPDATE hxc_ret_pay_details
SET pbl_id = l_rec_id(i),
batch_id = l_batch_id(i)
WHERE time_building_block_id = l_rec_bb_id(i)
AND object_version_number = l_rec_ovn(i);
INSERT INTO hxc_pay_trans_code_all
( transaction_code,
batch_id,
request_id)
VALUES ( g_params.p_transaction_code,
l_batch_id(i),
FND_GLOBAL.conc_request_id);
UPDATE hxc_ret_pay_latest_details
SET retro_pbl_id = l_rec_id(i),
retro_batch_id = l_batch_id(i)
WHERE time_building_block_id = l_rec_bb_id(i)
AND old_ovn = l_rec_ovn(i);
UPDATE hxc_ret_pay_details
SET retro_pbl_id = l_rec_id(i),
retro_batch_id = l_batch_id(i),
old_ovn = l_rec_ovn(i)
WHERE time_building_block_id = l_rec_bb_id(i)
AND request_id = FND_GLOBAL.conc_request_id ;
INSERT INTO hxc_pay_trans_code_all
( transaction_code,
batch_id,
request_id)
VALUES ( g_params.p_transaction_code,
l_batch_id(i),
FND_GLOBAL.conc_request_id);
DELETE FROM hxc_rdb_process_details
WHERE detail_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'SUCCESS'
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND ret_user_id = FND_global.user_id
AND process = g_params.p_process
RETURNING timecard_id
BULK
COLLECT INTO l_success_tc ;
UPDATE hxc_ld_rollback_timecards_all
SET request_id = FND_Global.conc_request_id
WHERE timecard_id = l_success_tc(rlbk)
RETURNING timecard_id BULK COLLECT INTO l_rlbk_elig_tc1;
INSERT INTO hxc_ld_rollback_timecards_all
(timecard_id, request_id)
VALUES (l_rlbk_elig_tc2(rlbk),FND_GLOBAL.conc_request_id);
UPDATE hxc_rdb_process_details
SET skip_level = 'REC_PROC',
skipped_reason = SUBSTR(hxc_generic_retrieval_pkg.t_tx_detail_exception
(tx_error),1,149)
WHERE detail_id = hxc_generic_retrieval_pkg.t_tx_detail_bb_id(tx_error)
AND hxc_generic_retrieval_pkg.t_tx_detail_status
(tx_error) = 'ERRORS'
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND ret_user_id = FND_global.user_id
AND process = g_params.p_process
RETURNING timecard_id
BULK
COLLECT INTO l_error_tc ;
update_rdb_status((l_success_tc MULTISET EXCEPT l_error_tc),
'PROCESSING',
'PROCESSED');
update_rdb_status((l_success_tc MULTISET EXCEPT l_error_tc),
'PROCESSING_PARTIAL',
'PROCESSED_PARTIALLY');
update_rdb_status((l_error_tc MULTISET EXCEPT l_success_tc),
'PROCESSING',
'ERRORED');
update_rdb_status((l_error_tc MULTISET EXCEPT l_success_tc),
'PROCESSING_PARTIAL',
'ERRORED');
update_rdb_status((l_error_tc MULTISET INTERSECT l_success_tc),
'PROCESSING',
'PROCESSED_PARTIALLY');
update_rdb_status((l_error_tc MULTISET INTERSECT l_success_tc),
'PROCESSING_PARTIAL',
'PROCESSED_PARTIALLY');
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
time_building_block_ovn,
transaction_id,
status,
exception_description
)
VALUES (hxc_generic_retrieval_pkg.t_tx_error_transaction_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_id
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn
(tx_error),
hxc_generic_retrieval_pkg.g_transaction_id,
hxc_generic_retrieval_pkg.t_tx_error_status
(tx_error),
hxc_generic_retrieval_pkg.t_tx_error_exception
(tx_error)
);
hxc_generic_retrieval_pkg.t_tx_error_transaction_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_id.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_bb_ovn.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_status.DELETE;
hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
UPDATE hxc_retrieval_ranges
SET transaction_id = -1
WHERE retrieval_process_id = l_process_id AND transaction_id = -1;
hxc_timecard_summary_pkg.update_transferred_to(i,
g_tc_ovn_array(i),
l_process_id );
g_tc_ovn_array.DELETE;
-- DO NOT DELETE FOR 'IN PROGRESS' TRANSACTIONS
THEN
IF g_debug
THEN
hr_utility.TRACE ('g_conc_request_id is ' || g_conc_request_id);
delete_retrieval_ranges (hxc_generic_retrieval_pkg.g_transaction_id);
hr_utility.TRACE ('Leaving Update Transaction Status');
END update_transaction_status;
PROCEDURE update_rdb_status ( p_tc_list NUMBERTABLE,
p_from_status VARCHAR2,
p_to_status VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE hxc_rdb_process_timecards
SET stage = p_to_status
WHERE timecard_id = l_tctab(i)
AND stage = p_from_status
AND process = g_params.p_process;
END update_rdb_status;
PROCEDURE update_rdb_status ( p_tc_list NUMTABLE,
p_from_status VARCHAR2,
p_to_status VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE hxc_rdb_process_timecards
SET stage = p_to_status
WHERE timecard_id = p_tc_list(i)
AND stage = p_from_status
AND process = g_params.p_process;
END update_rdb_status;