[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pending_delete BOOLEAN := FALSE;
g_pending_delete := FALSE;
IF p_abs_tab(l_ind).modetype = 'DeleteMode'
THEN
g_pending_delete := TRUE;
IF g_pending_delete = TRUE
THEN
hxc_timecard_message_helper.addErrorToCollection
( g_messages
,'HXC_ABS_PEND_APPR_DELETE'
,hxc_timecard.c_error
,NULL
,NULL
,hxc_timecard.c_hxc
,NULL
,NULL
,NULL
,NULL
);
hr_utility.trace('ABS : This is a DELETE pending in SSHR ');
g_message_string := 'HXC_ABS_PEND_APPR_DELETE';
END IF; -- Pending Delete = TRUE
hr_person_absence_swi.delete_absences_in_tt(l_abs_inv(l_ind).transactionid);
'SELECT hav.attribute1,hav.attribute2
FROM hxc_alias_definitions had,
hxc_alias_values hav
WHERE had.alias_definition_id IN ALIASLIST
AND hav.alias_definition_id = had.alias_definition_id
AND hav.attribute_category = ''PAYROLL_ELEMENTS''
AND hav.attribute2 IS NOT NULL
ORDER BY hav.alias_definition_id,hav.alias_value_id DESC' ;
SELECT bld_blk_info_type_id
INTO g_dummy_bbit
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'Dummy Element Context';
SELECT bld_blk_info_type_id
INTO g_alias_bbit
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'ALTERNATE NAME IDENTIFIERS';
SELECT bld_blk_info_type_id
INTO g_layout_bbit
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'LAYOUT';
IS SELECT havtl.name
FROM per_absence_attendance_types hav,
per_abs_attendance_types_tl havtl
WHERE hav.absence_attendance_type_id = havtl.absence_attendance_type_id
AND hav.absence_attendance_type_id = p_element_id
AND language = userenv('LANG');
delete_other_sessions(p_person_id,
p_start_date,
p_end_date,
p_lock_rowid);
delete_other_sessions(p_person_id,
p_start_date,
p_end_date,
p_lock_rowid);
SELECT 'Y'
INTO l_timecard_exists
FROM hxc_timecard_summary
WHERE resource_id = p_person_id
AND trunc(start_time) = trunc(p_start_date)
AND trunc(stop_time) = trunc(p_end_date);
delete_other_sessions(p_person_id,
p_start_date,
p_end_date,
l_lock_rowid);
p_app_attributes(l_app_attribute_index).updated := l_attribute_array(l_index).CHANGED;
p_blocks.delete;
SELECT 'Y'
INTO l_timecard_exists
FROM hxc_timecard_summary
WHERE resource_id = l_person_id
AND trunc(start_time) = trunc(l_start_date)
AND trunc(stop_time) = trunc(l_end_date);
delete_other_sessions(l_person_id,
l_start_date,
l_end_date,
l_lock_rowid);
p_app_attributes(l_app_attribute_index).updated := l_attribute_array(l_index).CHANGED;
p_blocks.delete;
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 ,
sessionid ,
co_date ,
lock_rowid ,
resource_id,
start_time,
stop_time,
transaction_id,
action,
confirmed_flag )
VALUES ( p_bb_id ,
p_bb_ovn ,
p_abs_id ,
p_abs_att_id ,
p_element ,
p_uom ,
p_measure ,
p_start_date ,
p_end_date ,
p_stage ,
USERENV('SESSIONID'),
SYSDATE,
p_lock_rowid,
p_resource_id,
p_tc_start,
p_tc_stop,
p_transaction_id,
p_action,
p_conf );
PROCEDURE update_co_absences(p_old_bb_id IN NUMBER,
p_new_bb_id IN NUMBER,
p_start_time IN DATE,
p_stop_time IN DATE,
p_element_id IN NUMBER)
IS
l_rowid VARCHAR2(50);
SELECT ROWIDTOCHAR(ROWID),
TO_CHAR(start_date,'hh24miss'),
TO_CHAR(end_date,'hh24miss'),
element_type_id,
transaction_id
INTO l_rowid,
l_start,
l_stop,
l_element,
l_transaction_id
FROM hxc_abs_co_details
WHERE time_building_block_id = p_old_bb_id
AND resource_id = g_person_id
AND start_time = g_start_time
AND stage IN ('PREP','DEP','PREP-SS')
AND TRUNC(stop_time) = TRUNC(g_stop_time);
UPDATE hxc_abs_co_details
SET time_building_block_id = p_new_bb_id,
stage = 'DEP'
WHERE ROWID = CHARTOROWID(l_rowid);
END update_co_absences;
PROCEDURE update_co_absences_ovn(p_old_bb_id IN hxc_time_building_blocks.time_building_block_id%type,
p_new_ovn IN NUMBER,
p_start_time IN DATE,
p_stop_time IN DATE,
p_element_id IN NUMBER )
IS
l_rowid VARCHAR2(50);
SELECT ROWIDTOCHAR(ROWID),
TO_CHAR(start_date,'hh24miss'),
TO_CHAR(end_date,'hh24miss'),
element_type_id,
action
INTO l_rowid,
l_start,
l_stop,
l_element,
l_action
FROM hxc_abs_co_details
WHERE time_building_block_id = p_old_bb_id
AND stage IN ('PREP','DEP');
UPDATE hxc_abs_co_details
SET object_version_number = p_new_ovn,
stage = 'DEP'
WHERE ROWID = chartorowid(l_rowid);
END update_co_absences_ovn;
PROCEDURE delete_other_sessions ( p_resource_id IN NUMBER,
p_start_time IN DATE,
p_stop_time IN DATE,
p_lock_rowid IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND stage IN ( 'PREP','PREP-SS');
DELETE FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND TRUNC(stop_time) = TRUNC(p_stop_time);
END delete_other_sessions ;
PROCEDURE insert_audit_header ( p_resource_id IN NUMBER,
p_start_time IN DATE,
p_stop_time IN DATE,
p_transaction_id IN OUT NOCOPY NUMBER)
IS
l_transaction_id NUMBER;
IS SELECT hxc_transactions_s.NEXTVAL
FROM DUAL;
SELECT RETRIEVAL_PROCESS_ID
INTO l_RETRIEVAL_PROCESS_ID
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';
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
) VALUES
(l_transaction_id
,SYSDATE
,'RETRIEVAL'
,l_RETRIEVAL_PROCESS_ID
,NULL
,SYSDATE
,NULL
,SYSDATE
,NULL
,'SUCCESS'
,'This prepopulated transaction is already present in HR'
);
END insert_audit_header;
PROCEDURE insert_audit_details ( p_resource_id IN NUMBER,
p_detail_bb_id IN NUMBER,
p_detail_ovn IN NUMBER,
p_header_id IN NUMBER)
IS
l_index NUMBER;
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
) VALUES
(l_transaction_detail_id
,l_index
,p_header_id
,NULL
,SYSDATE
,NULL
,SYSDATE
,NULL
,g_detail_trans_tab(l_index)
,'SUCCESS'
,'This is a prepopulated record and is already present in HR'
);
END insert_audit_details;
IS SELECT transaction_id,
ROWIDTOCHAR(abs.ROWID)
FROM hxc_abs_co_details abs
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND stop_time = p_stop_time
AND transaction_id IS NOT NULL;
hr_utility.trace('ABS : Calling delete_absences_in_tt ');
hr_person_absence_swi.delete_absences_in_tt(l_trans_tab(i));
UPDATE hxc_abs_co_details
SET transaction_id = NULL
WHERE ROWID = CHARTOROWID(l_rowid_tab(i));
insert_audit_header(p_resource_id,
p_start_time,
p_stop_time,
l_transaction_id);
insert_audit_details ( p_resource_id => p_resource_id,
p_detail_bb_id => 1,
p_detail_ovn => 1,
p_header_id => l_transaction_id);
IS SELECT edit_flag,uom
FROM hxc_absence_type_elements
WHERE element_type_id = p_element_type;
SELECT time_building_block_id,
element_type_id,
DECODE(uom,'D',measure,NULL) measure,
DECODE(uom,'H',fnd_date.date_to_canonical(start_date),NULL) start_time,
DECODE(uom,'H',fnd_date.date_to_canonical(end_date),NULL) stop_time,
TRUNC(start_date) abs_date,
'N' validated
FROM hxc_abs_co_details
WHERE start_time = p_start_time
AND resource_id = p_resource_id
AND TRUNC(stop_time) = TRUNC(p_stop_time)
AND stage IN ( 'PREP-SS', 'PREP','DEP');
IS SELECT time_building_block_id,
action
FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND TRUNC(stop_time) = TRUNC(p_stop_time)
AND stage = 'PREP-SS';
IS SELECT /*+ LEADING(hxc) */
time_building_block_id
FROM hxc_abs_co_details hxc,
per_absence_attendances per
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND TRUNC(stop_time) = TRUNC(p_stop_time)
AND stage IN ( 'PREP-SS','PREP', 'DEP')
AND confirmed_flag = 'N'
AND transaction_id IS NULL
AND action IS NULL
AND per.absence_attendance_id = NVL(hxc.absence_attendance_id,0)
AND per.date_start IS NULL
AND per.date_end IS NULL ;
IS SELECT time_building_block_id,
element_type_id,
DECODE(uom,'D',measure,NULL) measure,
DECODE(uom,'H',fnd_date.date_to_canonical(start_date),NULL) start_time,
DECODE(uom,'H',fnd_date.date_to_canonical(end_date),NULL) stop_time,
TRUNC(start_date) abs_date,
'N' validated
FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND TRUNC(stop_time) = TRUNC(p_stop_time)
AND stage in ('DEP','PREP-SS')
AND absence_attendance_id IS NULL;
select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
IS SELECT name
FROM per_abs_attendance_types_tl
WHERE absence_attendance_type_id = p_abs_id
AND language = USERENV('LANG');
IS SELECT htd.time_building_block_ovn
FROM hxc_transaction_details htd,
hxc_transactions ht
WHERE htd.time_building_block_id = p_bb_id
AND htd.time_building_block_ovn <= p_bb_ovn
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.transaction_process_id = g_bee_retrieval
AND htd.status = 'SUCCESS'
ORDER BY time_building_block_id DESC;
IS SELECT REPLACE(hta.attribute_category,'ELEMENT - ')
FROM hxc_time_attribute_usages hau,
hxc_time_attributes hta
WHERE hau.time_building_block_id = p_bb_id
AND hau.time_building_block_ovn = p_bb_ovn
AND hta.time_attribute_id = hau.time_attribute_id
AND hta.bld_blk_info_type_id = g_bld_blk_info;
IS SELECT 1
FROM hxc_absence_type_elements
WHERE element_type_id = p_element;
IS SELECT NVL(date_start,date_projected_start),
NVL(date_end,date_projected_end)
FROM per_absence_attendances abs
WHERE person_id = p_resource_id
AND NVL(date_start,date_projected_start) <= p_stop_time
AND NVL(date_end,date_projected_end) >= p_start_time
AND NOT EXISTS ( SELECT /* INDEX(tc HXC_ABS_CO_DETAILS_FK4) */
1
FROM hxc_abs_co_details tc
WHERE tc.absence_attendance_id = abs.absence_attendance_id
AND tc.stage = 'RET'
AND tc.resource_id = p_resource_id
AND tc.start_time = p_start_time);
IS SELECT distinct TRUNC(start_date)
FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND TRUNC(stop_time) = TRUNC(p_stop_time)
AND stage in ('DEP','PREP-SS')
AND absence_attendance_id IS NULL;
l_tottab.DELETE;
l_usertab.DELETE;
l_usertab.DELETE(p_attributes(i).building_block_id);
l_cotab.DELETE;
'HXC_ABS_VIEW_ONLY_NO_DELETE',
l_cotab(j).time_building_block_id);
l_valtab.DELETE(l_cotab(j).time_building_block_id);
l_message := 'HXC_ABS_VIEW_ONLY_NO_DELETE';
l_usertab.DELETE(l_cotab(j).time_building_block_id);
l_usertab.DELETE(l_cotab(j).time_building_block_id);
hr_utility.trace('ABS> now invoke update_absence_summary_row');
update_absence_summary_row(l_resource_id,
l_start_time,
l_stop_time,
l_abs_days,
l_abs_hours
);
DELETE FROM hxc_abs_co_details
WHERE resource_id = p_resource_id
AND start_time = p_tc_start
AND TRUNC(stop_time) = TRUNC(p_tc_stop);
PROCEDURE insert_absence_summary_row
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT 'Y'
INTO l_abs_record_exist
FROM hxc_absence_summary_temp
WHERE resource_id = hxc_retrieve_absences.g_person_id
AND start_time = hxc_retrieve_absences.g_start_time
AND stop_time = hxc_retrieve_absences.g_stop_time;
INSERT INTO hxc_absence_summary_temp
(resource_id
,start_time
,stop_time
)
VALUES
(hxc_retrieve_absences.g_person_id
,hxc_retrieve_absences.g_start_time
,hxc_retrieve_absences.g_stop_time
);
END insert_absence_summary_row;
PROCEDURE update_absence_summary_row(p_resource_id IN NUMBER,
p_tc_start IN DATE,
p_tc_stop IN DATE,
p_abs_days IN NUMBER,
p_abs_hours IN NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE hxc_absence_summary_temp
SET absence_days = p_abs_days,
absence_hours = p_abs_hours
WHERE resource_id = p_resource_id
AND start_time = p_tc_start
AND stop_time = p_tc_stop;
END update_absence_summary_row;
DELETE FROM hxc_absence_summary_temp;
IS SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = p_lookup_type
AND language = USERENV('LANG')
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
SELECT hxc.absence_attendance_type_id,
per.increasing_or_decreasing_flag,
pertl.name,
per.hours_or_days
INTO l_abs_type_id,
l_inc_dec,
l_name,
l_uom
FROM hxc_absence_type_elements hxc,
per_absence_attendance_types per,
per_abs_attendance_types_tl pertl
WHERE hxc.element_type_id = p_element_type_id
AND hxc.absence_attendance_type_id = per.absence_attendance_type_id
AND per.absence_attendance_type_id = pertl.absence_attendance_type_id
AND pertl.language = USERENV('LANG');
IS SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND p_start_time BETWEEN effective_start_date
AND effective_end_date;
SELECT bld_blk_info_type_id
INTO g_bld_blk_info
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'Dummy Element Context';
SELECT retrieval_process_id
INTO g_bee_retrieval
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';
SELECT 'Y'
INTO p_absence_element_flag
FROM hxc_absence_type_elements
WHERE element_type_id in ( SELECT attribute1
FROM hxc_alias_values
WHERE alias_value_id = p_alias_value_id);