[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_success_delete_msg VARCHAR2 (100):= 'This absence detail was successfully deleted in Absences module';
SELECT time_building_block_id,
object_version_number,
resource_id,
in_time,
out_time,
absence_attendance_type_id,
uom,
cost_allocation_keyflex_id,
absence_attendance_id,
day_start,
day_stop,
retrieval_status,
absences_action,
element_type_id,
link_time_building_block_id
FROM hxc_abs_ret_temp
WHERE resource_id = c_resource_id
AND TRUNC (day_start) >= TRUNC (c_tc_start)
AND TRUNC (day_stop) <= TRUNC (c_tc_stop)
ORDER BY absences_action ASC,
uom ASC,
absence_attendance_type_id ASC,
cost_allocation_keyflex_id ASC,
day_start ASC;
SELECT /*+ INDEX(htau HXC_TIME_ATTRIBUTE_USAGES_FK2)
INDEX(hta HXC_TIME_ATTRIBUTES_PK) */
temp.time_building_block_id time_building_block_id,
temp.object_version_number object_version_number,
hta.attribute1 attribute1, hta.attribute2 attribute2,
hta.attribute3 attribute3, hta.attribute4 attribute4,
hta.attribute5 attribute5, hta.attribute6 attribute6,
hta.attribute7 attribute7, hta.attribute8 attribute8,
hta.attribute9 attribute9, hta.attribute10 attribute10,
hta.attribute11 attribute11, hta.attribute12 attribute12,
hta.attribute13 attribute13, hta.attribute14 attribute14,
hta.attribute15 attribute15, hta.attribute16 attribute16,
hta.attribute17 attribute17, hta.attribute18 attribute18,
hta.attribute19 attribute19, hta.attribute20 attribute20,
hta.attribute21 attribute21, hta.attribute22 attribute22,
hta.attribute23 attribute23, hta.attribute24 attribute24,
hta.attribute25 attribute25, hta.attribute26 attribute26,
hta.attribute27 attribute27, hta.attribute28 attribute28,
hta.attribute29 attribute29, hta.attribute30 attribute30,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
0 cost_allocation_keyflex_id
FROM hxc_time_attributes hta,
hxc_time_attribute_usages htau,
hxc_abs_ret_temp temp
WHERE temp.time_building_block_id = htau.time_building_block_id
AND temp.object_version_number = htau.time_building_block_ovn
AND htau.time_attribute_id = hta.time_attribute_id
AND hta.attribute_category = 'Dummy Cost Context';
CURSOR c_delete_prepop_abs
IS
SELECT absco.absence_attendance_id,
absco.uom,
trunc(absco.start_date) start_date,
trunc(absco.end_date) end_date,
absco.time_building_block_id,
absco.object_version_number
FROM hxc_abs_co_details absco
WHERE absco.resource_id = g_resource_id
AND TRUNC (absco.start_time) >= TRUNC (g_tc_start)
AND TRUNC (absco.stop_time) <= TRUNC (g_tc_stop)
AND absco.stage <> 'RET'
AND
NOT EXISTS (SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
INDEX(ht HXC_TRANSACTIONS_PK)
INDEX(ht HXC_TRANSACTIONS_N3) */
1
FROM hxc_transaction_details htd, hxc_transactions ht
WHERE absco.time_building_block_id = htd.time_building_block_id
AND htd.transaction_id = ht.transaction_id
AND ht.transaction_process_id = g_retrieval_process_id
AND ht.TYPE = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND htd.status = 'SUCCESS')
ORDER BY uom ASC, absence_attendance_id ASC, start_date ASC;
CURSOR c_delete_ret_abs
IS
SELECT absence_attendance_id,
uom,
trunc(day_start) start_date,
trunc(day_stop) end_date,
time_building_block_id,
object_version_number
FROM hxc_abs_ret_temp
WHERE absence_attendance_id IS NOT NULL
AND absences_action = 'DELETE'
ORDER BY uom ASC, absence_attendance_id ASC, start_date ASC;
SELECT temp.time_building_block_id,
temp.object_version_number,
latest.object_version_number,
temp.absences_action,
detail.date_to
FROM hxc_abs_ret_temp temp,
hxc_latest_details latest,
hxc_time_building_blocks detail
WHERE temp.resource_id = c_resource_id
AND TRUNC (temp.day_start) >= TRUNC (c_tc_start)
AND TRUNC (temp.day_stop) <= TRUNC (c_tc_stop)
AND temp.retrieval_status = 'SUCCESS'
AND temp.time_building_block_id = latest.time_building_block_id
AND latest.time_building_block_id = detail.time_building_block_id
AND latest.object_version_number = detail.object_version_number
ORDER BY absences_action ASC;
TYPE t_delete_absences IS TABLE OF c_delete_prepop_abs%ROWTYPE
INDEX BY BINARY_INTEGER;
l_prepop_delete_absences t_delete_absences;
l_ret_delete_absences t_delete_absences;
l_delete_id_tab NUMTAB;
l_delete_ovn_tab NUMTAB;
l_call_delete BOOLEAN;
SELECT full_name, business_group_id
INTO l_emp_name, g_business_group_id
FROM per_all_people_f
WHERE person_id = g_resource_id
AND g_tc_start between effective_start_date and effective_end_date;
SELECT retrieval_process_id
INTO g_retrieval_process_id
FROM hxc_retrieval_processes
WHERE NAME = 'BEE Retrieval Process';
DELETE FROM hxc_pay_latest_details
WHERE (time_building_block_id,object_version_number)
IN ( SELECT co.time_building_block_id,
co.object_version_number
FROM hxc_abs_co_details co,
hxc_transaction_details htd,
hxc_transactions ht
WHERE co.resource_id = g_resource_id
AND co.start_time = g_tc_start
AND TRUNC(co.stop_time) = TRUNC(g_tc_stop)
AND htd.time_building_block_id = co.time_building_block_id
AND htd.time_building_block_ovn = co.object_version_number
AND htd.transaction_id = ht.transaction_id
AND ht.transaction_process_id = g_retrieval_process_id
AND action IS NULL );
OR (g_tc_status = 'DELETED')
THEN -- if2
IF g_debug THEN
hr_utility.set_location('ABS:Processing '||l_proc, 50);
INSERT INTO hxc_abs_ret_temp (
SELECT /*+ LEADING (latest HXC_LATEST_DETAILS_N1)
INDEX(detail HXC_TIME_BUILDING_BLOCKS_PK)
INDEX(htau HXC_TIME_ATTRIBUTE_USAGES_FK2)
INDEX(hta HXC_TIME_ATTRIBUTES_PK) */
detail.time_building_block_id, --time_building_block_id,
detail.object_version_number , --object_version_number,
detail.resource_id , --resource_id,
detail.start_time , --in_time,
detail.stop_time , --out_time,
hate.absence_attendance_type_id, --absence_attendance_type_id,
SUBSTR (hate.uom, 1, 1) , --uom,
0 , --cost_allocation_keyflex_id,
NULL , --absence_attendance_id,
latest.start_time , --day_start,
latest.stop_time , --day_stop,
NULL , --retrieval_status,
'CREATE' , --absences_action,
to_number(
SUBSTR(hta.attribute_category, 11)
) , --element_type_id,
NULL --link_time_building_block_id
FROM hxc_latest_details latest,
hxc_time_building_blocks detail,
hxc_time_attribute_usages htau,
hxc_time_attributes hta,
hxc_absence_type_elements hate
WHERE latest.resource_id = g_resource_id
AND TRUNC (latest.start_time) BETWEEN TRUNC (g_tc_start) AND TRUNC (g_tc_stop)
AND latest.time_building_block_id = detail.time_building_block_id
AND latest.object_version_number = detail.object_version_number
AND detail.time_building_block_id = htau.time_building_block_id
AND detail.object_version_number = htau.time_building_block_ovn
AND detail.date_to = hr_general.end_of_time
AND htau.time_attribute_id = hta.time_attribute_id
AND hta.attribute_category like 'ELEMENT - %'
AND to_number(SUBSTR(hta.attribute_category, 11)) = hate.element_type_id
AND NOT EXISTS (
SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
INDEX(ht HXC_TRANSACTIONS_PK)
INDEX(ht HXC_TRANSACTIONS_N3) */
1
FROM hxc_transaction_details htd, hxc_transactions ht
WHERE latest.time_building_block_id = htd.time_building_block_id
AND latest.object_version_number = htd.time_building_block_ovn
AND htd.transaction_id = ht.transaction_id
AND ht.transaction_process_id = g_retrieval_process_id
AND ht.TYPE = 'RETRIEVAL'
AND ht.status = 'SUCCESS'
AND htd.status = 'SUCCESS') );
INSERT INTO hxc_abs_ret_temp (
SELECT detail.time_building_block_id , --time_building_block_id,
detail.object_version_number , --object_version_number,
detail.resource_id , --resource_id,
detail.start_time , --in_time,
detail.stop_time , --out_time,
absatt.absence_attendance_type_id, --absence_attendance_type_id,
absco.uom , --uom,
0 , --cost_allocation_keyflex_id,
absco.absence_attendance_id , --absence_attendance_id,
TRUNC (DAY.start_time) , --day_start,
TRUNC (DAY.stop_time) , --day_stop,
NULL , --retrieval_status,
'DELETE' , --absences_action,
absco.element_type_id , --element_type_id,
NULL --link_time_building_block_id
FROM hxc_abs_co_details absco,
hxc_time_building_blocks detail,
hxc_time_building_blocks DAY,
per_absence_attendances absatt
WHERE absco.resource_id = g_resource_id
AND TRUNC (absco.start_time) = TRUNC (g_tc_start)
AND TRUNC (absco.stop_time) = TRUNC (g_tc_stop)
AND absco.absence_attendance_id IS NOT NULL
AND detail.time_building_block_id = absco.time_building_block_id
AND detail.object_version_number =
(SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
INDEX(ht HXC_TRANSACTIONS_PK)
INDEX(ht HXC_TRANSACTIONS_N3) */
MAX (htd.time_building_block_ovn)
FROM hxc_transactions ht, hxc_transaction_details htd
WHERE ht.transaction_process_id = g_retrieval_process_id
AND htd.time_building_block_id = absco.time_building_block_id
AND htd.transaction_id = ht.transaction_id
AND htd.status = 'SUCCESS'
AND ht.TYPE = 'RETRIEVAL'
AND ht.status = 'SUCCESS')
AND DAY.time_building_block_id = detail.parent_building_block_id
AND DAY.object_version_number = detail.parent_building_block_ovn
AND detail.date_to <> hr_general.end_of_time
AND absco.absence_attendance_id = absatt.absence_attendance_id ) ;
DELETE FROM hxc_pay_latest_details
WHERE (time_building_block_id,object_version_number)
IN ( SELECT time_building_block_id,
object_version_number
FROM hxc_abs_ret_temp );
hr_utility.TRACE ('ABS:Update TEMP table with cost information');
UPDATE hxc_abs_ret_temp
SET cost_allocation_keyflex_id = l_cost_segment (l_ix).cost_allocation_keyflex_id
WHERE time_building_block_id = l_cost_segment (l_ix).time_building_block_id
AND object_version_number = l_cost_segment (l_ix).object_version_number;
l_cost_segment.DELETE;
hr_utility.TRACE ('ABS:Process records with only ovn updates');
hr_utility.TRACE ('ABS:Delete records from temp having only OVN change'||l_id_tab.COUNT);
DELETE FROM hxc_abs_ret_temp
WHERE time_building_block_id = l_id_tab(l_ix);
UPDATE hxc_abs_co_details
SET object_version_number = l_ovn_tab (l_ix)
WHERE time_building_block_id = l_id_tab(l_ix)
AND stage = 'RET';
l_id_tab.DELETE;
l_ovn_tab.DELETE;
new_absences_tab.DELETE;
edited_abs_tab.DELETE;
all_abs_tab.DELETE;
g_all_id_tab.DELETE;
g_all_ovn_tab.DELETE;
l_call_delete := FALSE;
OPEN c_delete_prepop_abs;
FETCH c_delete_prepop_abs
BULK COLLECT INTO l_prepop_delete_absences;
CLOSE c_delete_prepop_abs;
hr_utility.TRACE( 'ABS:** Prepopulation ** Count of prepop rows updated = ' || l_prepop_delete_absences.COUNT);
IF (l_prepop_delete_absences.COUNT > 0)
THEN
l_abs_ix := l_prepop_delete_absences.FIRST;
l_edited_days(l_ed_days_ix).day_start := l_prepop_delete_absences (l_abs_ix).start_date;
l_edited_days(l_ed_days_ix).day_stop := l_prepop_delete_absences (l_abs_ix).end_date;
l_edited_days(l_ed_days_ix).time_building_block_id := l_prepop_delete_absences (l_abs_ix).time_building_block_id;
l_edited_days(l_ed_days_ix).object_version_number := l_prepop_delete_absences (l_abs_ix).object_version_number;
l_absence_attendance_id := l_prepop_delete_absences (l_abs_ix).absence_attendance_id;
l_uom := l_prepop_delete_absences (l_abs_ix).uom;
IF (l_prepop_delete_absences.EXISTS (l_abs_ix)) THEN
IF (l_prepop_delete_absences (l_abs_ix).absence_attendance_id <> l_absence_attendance_id) THEN
l_call_delete := TRUE;
l_call_delete := FALSE;
l_call_delete := TRUE;
IF l_call_delete THEN
IF g_debug THEN
hr_utility.TRACE( 'ABS:Process delete for Absence attendance id = '
|| l_absence_attendance_id );
delete_absences
(l_absence_attendance_id,
l_edited_days,
l_uom
);
hr_utility.TRACE( 'ABS:Completed delete for Absence attendance id = '
|| l_absence_attendance_id );
l_edited_days.DELETE;
EXIT WHEN NOT l_prepop_delete_absences.EXISTS (l_abs_ix);
l_prepop_delete_absences.DELETE;
l_edited_days.DELETE;
l_call_delete := FALSE;
OPEN c_delete_ret_abs;
FETCH c_delete_ret_abs
BULK COLLECT INTO l_ret_delete_absences;
CLOSE c_delete_ret_abs;
hr_utility.TRACE ( 'ABS:** Retrieved ** Count of retrieved rows updated = ' || l_ret_delete_absences.COUNT );
IF (l_ret_delete_absences.COUNT > 0)
THEN
l_abs_ix := l_ret_delete_absences.FIRST;
l_edited_days(l_ed_days_ix).day_start := l_ret_delete_absences (l_abs_ix).start_date;
l_edited_days(l_ed_days_ix).day_stop := l_ret_delete_absences (l_abs_ix).end_date;
l_edited_days(l_ed_days_ix).time_building_block_id := l_ret_delete_absences (l_abs_ix).time_building_block_id;
l_edited_days(l_ed_days_ix).object_version_number := l_ret_delete_absences (l_abs_ix).object_version_number;
l_absence_attendance_id := l_ret_delete_absences (l_abs_ix).absence_attendance_id;
l_uom := l_ret_delete_absences (l_abs_ix).uom;
IF (l_ret_delete_absences.EXISTS (l_abs_ix)) THEN
IF (l_ret_delete_absences (l_abs_ix).absence_attendance_id <> l_absence_attendance_id) THEN
l_call_delete := TRUE;
l_call_delete := FALSE;
l_call_delete := TRUE;
IF l_call_delete THEN
IF g_debug THEN
hr_utility.TRACE( 'ABS:Process delete for Absence attendance id = '
|| l_absence_attendance_id );
delete_absences
(l_absence_attendance_id,
l_edited_days,
l_uom
);
hr_utility.TRACE( 'ABS:Completed delete for Absence attendance id = '
|| l_absence_attendance_id );
l_edited_days.DELETE;
EXIT WHEN NOT l_ret_delete_absences.EXISTS (l_abs_ix);
l_ret_delete_absences.DELETE;
l_edited_days.DELETE;
UPDATE hxc_abs_ret_temp
SET link_time_building_block_id = days_tab (l_days_ix + 1).time_building_block_id
WHERE time_building_block_id = days_tab (l_days_ix).time_building_block_id
AND absences_action = 'CREATE';
days_tab.DELETE (l_days_ix);
l_delete_id_tab(l_h_ix) := l_temp_id_tab(l_ix);
l_delete_ovn_tab(l_h_ix) := l_latest_ovn_tab(l_ix);
l_delete_ovn_tab(l_h_ix) := l_temp_ovn_tab(l_ix);
hr_utility.trace('delete txn records - '||l_delete_id_tab.COUNT);
DELETE FROM hxc_abs_ret_temp
WHERE time_building_block_id = l_create_id_tab(l_tx_index);
l_create_id_tab.DELETE;
l_create_ovn_tab.DELETE;
IF (l_delete_id_tab.COUNT > 0)
THEN
IF g_debug THEN
hr_utility.TRACE ('ABS:CALLING create_transactions for DELETE records - '||l_delete_id_tab.COUNT);
create_transactions (l_delete_id_tab,
l_delete_ovn_tab,
'SUCCESS',
g_success_delete_msg
);
hr_utility.TRACE ('ABS:COMPLETED create_transactions for DELETE records');
FORALL l_tx_index IN l_delete_id_tab.FIRST .. l_delete_id_tab.LAST
DELETE FROM hxc_abs_ret_temp
WHERE time_building_block_id = l_delete_id_tab(l_tx_index);
l_delete_id_tab.DELETE;
l_delete_ovn_tab.DELETE;
IF p_tc_status <> 'DELETED' THEN -- Added for Bug 12533914
IF g_debug THEN
hr_utility.trace('ABS: Pick up timecard id to process transferred_to column');
SELECT timecard_id,
timecard_ovn
INTO l_timecard_id,
l_timecard_ovn
FROM hxc_timecard_summary
WHERE resource_id = p_resource_id
AND start_time = p_tc_start
AND stop_time = TRUNC(p_tc_stop) + 1 - (1/86400) ;
hr_utility.trace('ABS: call to hxc_timecard_summary_pkg.update_transferred_to');
hxc_timecard_summary_pkg.update_transferred_to(l_timecard_id,
l_timecard_ovn,
g_retrieval_process_id);
END IF; -- IF p_tc_status <> 'DELETED' THEN
DELETE FROM hxc_abs_ret_temp
WHERE resource_id = g_resource_id
AND trunc(day_start) >= trunc(g_tc_start)
AND trunc(day_stop) <= trunc(g_tc_stop);
SELECT time_building_block_id,
object_version_number,
trunc(day_start) day_start,
trunc(day_stop) day_stop
FROM hxc_abs_ret_temp temp
START WITH time_building_block_id = c_tbb_id
CONNECT BY PRIOR time_building_block_id = link_time_building_block_id;
UPDATE hxc_abs_ret_temp
SET retrieval_status = 'SUCCESS',
absence_attendance_id = l_absence_attendance_id
WHERE time_building_block_id = l_id_tab(l_index)
AND absence_attendance_id IS NULL;
hr_utility.TRACE ('ABS:updated temp table = ' || SQL%ROWCOUNT);
UPDATE hxc_abs_co_details
SET object_version_number = l_ovn_tab(l_index),
absence_type_id = l_absence_attendance_type_id,
absence_attendance_id = l_absence_attendance_id,
element_type_id = l_element_type_id,
uom = SUBSTR(p_uom,1,1),
measure = nvl(l_absence_hours, 1),
start_date = nvl(l_in_time, l_day_start_tab(l_index)),
end_date = nvl(l_out_time, l_day_stop_tab(l_index))
WHERE time_building_block_id = l_id_tab(l_index)
AND stage = 'RET';
UPDATE hxc_abs_co_details
SET absence_attendance_id = l_absence_attendance_id
WHERE time_building_block_id = l_id_tab(l_index)
AND stage <> 'RET';
hr_utility.TRACE ('ABS:Updated HXC_ABS_CO_DETAILS');
INSERT INTO hxc_abs_co_details
(TIME_BUILDING_BLOCK_ID,
OBJECT_VERSION_NUMBER ,
ABSENCE_TYPE_ID ,
ABSENCE_ATTENDANCE_ID ,
ELEMENT_TYPE_ID ,
UOM ,
MEASURE ,
START_DATE ,
END_DATE ,
STAGE ,
RESOURCE_ID ,
START_TIME ,
STOP_TIME)
(SELECT time_building_block_id,
object_version_number,
absence_attendance_type_id,
absence_attendance_id,
element_type_id,
uom,
nvl(l_absence_hours, 1),
NVL (in_time, TRUNC (day_start)),
NVL (out_time, TRUNC (day_stop)),
'RET',
g_resource_id,
g_tc_start,
g_tc_stop
FROM hxc_abs_ret_temp temp
WHERE temp.absence_attendance_id = l_absence_attendance_id
AND temp.retrieval_status = 'SUCCESS'
AND NOT EXISTS (
SELECT 1
FROM hxc_abs_co_details absco
WHERE absco.time_building_block_id = temp.time_building_block_id));
update_cost_center
(l_absence_attendance_id,
p_absences (l_abs_ix).cost_allocation_keyflex_id
);
UPDATE hxc_abs_co_details
SET absence_attendance_id = l_new_absence_attendance_id
WHERE TRUNC (start_date) BETWEEN TRUNC (l_date_start)
AND TRUNC (l_date_end)
AND absence_attendance_id = p_old_absence_attendance_id;
PROCEDURE delete_absences (
p_absence_attendance_id IN NUMBER,
p_edited_days IN hxc_abs_retrieval_pkg.t_edited_days,
p_uom IN VARCHAR2
)
AS
CURSOR get_absences_details_cur
IS
SELECT absatt.absence_attendance_type_id,
trunc(absatt.date_start),
trunc(absatt.date_end),
absatt.time_start,
absatt.time_end, absatt.person_id,
absatt.program_application_id,
hate.edit_flag
FROM per_absence_attendances absatt,
hxc_absence_type_elements hate
WHERE absatt.absence_attendance_id = p_absence_attendance_id
AND absatt.absence_attendance_type_id = hate.absence_attendance_type_id;
SELECT trunc(start_date) start_date,
trunc(end_date) end_date,
to_char(start_date, 'HH24:MI') time_start,
to_char(end_date, 'HH24:MI') time_end,
start_time,
stop_time
FROM hxc_abs_co_details absco
WHERE absco.absence_attendance_id = p_absence_attendance_id
AND absco.time_building_block_id > 0
AND NOT EXISTS(select 1 from hxc_abs_ret_temp temp
where temp.time_building_block_id = absco.time_building_block_id)
ORDER BY 1 asc ;
l_proc := g_package||'delete_absences';
hr_utility.TRACE ( 'ABS:Process DELETE for Absence attendance id = ' || p_absence_attendance_id );
hr_utility.TRACE ('ABS:Absence record already deleted from HR');
hr_utility.TRACE ('ABS:Call HR DELETE API for absence (hours) = ' || p_absence_attendance_id );
hr_person_absence_api.delete_person_absence
(p_validate => FALSE,
p_absence_attendance_id => p_absence_attendance_id,
p_object_version_number => NULL,
p_called_from => 809
);
UPDATE hxc_abs_co_details
SET absence_attendance_id = NULL
WHERE absence_attendance_id = p_absence_attendance_id;
hr_utility.TRACE ('ABS:Rows update (1) - ' || SQL%ROWCOUNT);
UPDATE hxc_abs_ret_temp
SET retrieval_status = 'SUCCESS'
WHERE absence_attendance_id = p_absence_attendance_id;
IF (l_edit_flag = 'N' and l_program_application_id = '800' and g_tc_status = 'DELETED')
THEN
IF g_debug THEN
hr_utility.set_location('ABS:Processing '||l_proc, 425);
hr_utility.trace('ABS:These prepop absences are set to View Only and not deleted from HR on timecard delete');
UPDATE hxc_abs_co_details
SET absence_attendance_id = NULL
WHERE absence_attendance_id = p_absence_attendance_id;
UPDATE hxc_abs_ret_temp
SET retrieval_status = 'SUCCESS'
WHERE absence_attendance_id = p_absence_attendance_id;
hr_utility.TRACE ('ABS:Call HR DELETE API for absence = ' || p_absence_attendance_id );
hr_person_absence_api.delete_person_absence
(p_validate => FALSE,
p_absence_attendance_id => p_absence_attendance_id,
p_object_version_number => NULL,
p_called_from => 809
);
UPDATE hxc_abs_co_details
SET absence_attendance_id = NULL
WHERE time_building_block_id IN (
SELECT time_building_block_id
FROM hxc_abs_ret_temp
WHERE absence_attendance_id = p_absence_attendance_id
AND absences_action = 'DELETE')
OR time_building_block_id < 0
AND absence_attendance_id = p_absence_attendance_id;
UPDATE hxc_abs_ret_temp
SET retrieval_status = 'SUCCESS'
WHERE absence_attendance_id = p_absence_attendance_id;
IF (g_tc_status = 'DELETED') THEN
IF g_debug THEN
hr_utility.trace('ABS:Absences with source HR are not allowed to be deleted on timecard delete');
UPDATE hxc_abs_co_details
SET absence_attendance_id = NULL
WHERE absence_attendance_id = p_absence_attendance_id;
UPDATE hxc_abs_ret_temp
SET retrieval_status = 'SUCCESS'
WHERE absence_attendance_id = p_absence_attendance_id;
hr_utility.TRACE ('ABS:EXCEPTION IN DELETE_ABSENCES - set_to_view_only_d');
hr_utility.TRACE ('ABS:EXCEPTION IN DELETE_ABSENCES - pref_changed_before_ret');
END delete_absences;
insert_audit_header (p_status,
p_description,
g_transaction_id);
insert_audit_details (p_tbb_id,
p_tbb_ovn,
p_status,
p_description,
g_transaction_id
);
PROCEDURE insert_audit_header (
p_status IN VARCHAR2,
p_description IN VARCHAR2,
p_transaction_id OUT NOCOPY hxc_transactions.transaction_id%TYPE
)
IS
CURSOR c_transaction_sequence
IS
SELECT hxc_transactions_s.NEXTVAL
FROM DUAL;
INSERT INTO hxc_transactions
(transaction_id,
transaction_date,
TYPE,
transaction_process_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
status,
exception_description,
data_set_id
)
VALUES (g_transaction_id,
SYSDATE,
'RETRIEVAL',
g_retrieval_process_id,
NULL,
SYSDATE,
NULL,
SYSDATE,
NULL,
p_status,
p_description,
NULL
);
END insert_audit_header;
PROCEDURE insert_audit_details (
p_tbb_id IN hxc_abs_retrieval_pkg.NUMTAB,
p_tbb_ovn IN hxc_abs_retrieval_pkg.NUMTAB,
p_status IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_transaction_id IN hxc_transactions.transaction_id%TYPE
)
IS
CURSOR c_transaction_detail_sequence
IS
SELECT hxc_transaction_details_s.NEXTVAL
FROM DUAL;
INSERT INTO hxc_transaction_details
(transaction_detail_id,
time_building_block_id,
transaction_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
time_building_block_ovn,
status,
exception_description,
data_set_id
)
VALUES (l_transaction_detail_id,
p_tbb_id (l_tx_index),
g_transaction_id,
NULL,
SYSDATE,
NULL,
SYSDATE,
NULL,
p_tbb_ovn (l_tx_index),
p_status,
p_description,
NULL
);
END insert_audit_details;
PROCEDURE update_cost_center (
p_absence_attendance_id NUMBER,
p_cost_allocation_keyflex_id NUMBER
)
AS
CURSOR get_element_entry_info
IS
SELECT peef.element_entry_id, peef.effective_start_date,
peef.effective_end_date,
peef.object_version_number,
pelf.costable_type
FROM pay_element_entries_f peef, pay_element_links_f pelf
WHERE peef.creator_id = p_absence_attendance_id
AND peef.element_link_id = pelf.element_link_id
AND peef.object_version_number =
(SELECT /*+NO_UNNEST*/
MAX (object_version_number)
FROM pay_element_entries_f
WHERE creator_id = p_absence_attendance_id);
l_update_warning BOOLEAN;
l_proc := g_package||'update_cost_center';
( 'ABS:Entered update_cost_center for absence_attendance_id = ' || p_absence_attendance_id );
py_element_entry_api.update_element_entry
(p_validate => FALSE,
p_datetrack_update_mode => hr_api.g_correction,
p_effective_date => TRUNC(l_effective_start_date),
p_business_group_id => g_business_group_id,
p_element_entry_id => l_element_entry_id,
p_object_version_number => l_ee_ovn,
p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_update_warning => l_update_warning
);
END update_cost_center;
SELECT edit_flag INTO l_edit_flag
FROM hxc_absence_type_elements
WHERE absence_attendance_type_id = p_absence_attendance_type_id;
SELECT cost_allocation_structure
INTO l_cost_alloc_struct
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
SELECT rrc.status
INTO l_ret_status
FROM hxc_retrieval_rule_comps rrc,
hxc_retrieval_rules rr,
hxc_retrieval_rule_grp_comps_v rrgc,
hxc_retrieval_rule_groups_v rrg
WHERE rrg.retrieval_rule_group_id = p_retrieval_rule_grp_id
AND rrgc.retrieval_rule_group_id = rrg.retrieval_rule_group_id
AND rrgc.retrieval_process_id = g_retrieval_process_id
AND rr.retrieval_rule_id = rrgc.retrieval_rule_id
AND rrc.retrieval_rule_id = rr.retrieval_rule_id;