[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
application_set_id
from
hxc_time_building_blocks
where
time_building_block_id = p_timecard_id
and
object_version_number=p_ovn;
SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
a.bld_blk_info_type_id, a.object_version_number
FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
WHERE a.time_attribute_id = au.time_attribute_id
AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND (au.time_building_block_id, au.time_building_block_ovn) IN
(SELECT detail.time_building_block_id, detail.object_version_number
FROM hxc_time_building_blocks detail, hxc_time_building_blocks DAY
WHERE DAY.time_building_block_id = detail.parent_building_block_id
AND DAY.object_version_number = detail.parent_building_block_ovn
AND DAY.SCOPE = 'DAY'
AND detail.resource_id = l_resource_id
AND detail.SCOPE = 'DETAIL'
AND DAY.date_to = hr_general.end_of_time
AND detail.date_to = hr_general.end_of_time
AND DAY.parent_building_block_id = timecard_id
AND DAY.parent_building_block_ovn = timecard_ovn
AND DAY.resource_id = l_resource_id)
UNION
SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
a.bld_blk_info_type_id, a.object_version_number
FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
WHERE a.time_attribute_id = au.time_attribute_id
AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND (au.time_building_block_id, au.time_building_block_ovn) IN
(SELECT DAY.time_building_block_id, DAY.object_version_number
FROM hxc_time_building_blocks DAY
WHERE DAY.date_to = hr_general.end_of_time
AND DAY.SCOPE = 'DAY'
AND DAY.parent_building_block_id = timecard_id
AND DAY.parent_building_block_ovn = timecard_ovn
AND DAY.resource_id = l_resource_id)
UNION
SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
a.bld_blk_info_type_id, a.object_version_number
FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
WHERE a.time_attribute_id = au.time_attribute_id
AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND (au.time_building_block_id, au.time_building_block_ovn) IN
(SELECT time_building_block_id, object_version_number
FROM hxc_time_building_blocks htbb
WHERE htbb.date_to = hr_general.end_of_time
AND htbb.SCOPE = 'TIMECARD'
AND htbb.time_building_block_id = timecard_id
AND htbb.object_version_number = timecard_ovn
AND htbb.resource_id = l_resource_id)
ORDER BY time_building_block_id;
p_insert_detail IN hxc_timekeeper_process.t_time_info,
p_base_dff IN VARCHAR2,
p_att_tab IN hxc_alias_utility.t_alias_att_info
)
RETURN VARCHAR2 IS
att_dep_item NUMBER
/*ADVICE(416): NUMBER has no precision [315] */
;
SELECT d.default_context_field_name
FROM fnd_descriptive_flexs d, fnd_application a, fnd_product_installations z
WHERE d.application_id = a.application_id
AND z.application_id = a.application_id
AND a.application_short_name = 'PA'
AND z.status = 'I'
AND d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
hr_utility.trace('in detail is '||p_insert_detail.attr_id_3);
new_att_catg := p_insert_detail.attr_id_1;
new_att_catg := p_insert_detail.attr_id_2;
new_att_catg := p_insert_detail.attr_id_3;
new_att_catg := p_insert_detail.attr_id_4;
new_att_catg := p_insert_detail.attr_id_5;
new_att_catg := p_insert_detail.attr_id_6;
new_att_catg := p_insert_detail.attr_id_7;
new_att_catg := p_insert_detail.attr_id_8;
new_att_catg := p_insert_detail.attr_id_9;
new_att_catg := p_insert_detail.attr_id_10;
new_att_catg := p_insert_detail.attr_id_11;
new_att_catg := p_insert_detail.attr_id_11;
new_att_catg := p_insert_detail.attr_id_12;
new_att_catg := p_insert_detail.attr_id_13;
new_att_catg := p_insert_detail.attr_id_14;
new_att_catg := p_insert_detail.attr_id_15;
new_att_catg := p_insert_detail.attr_id_16;
new_att_catg := p_insert_detail.attr_id_17;
new_att_catg := p_insert_detail.attr_id_18;
new_att_catg := p_insert_detail.attr_id_19;
new_att_catg := p_insert_detail.attr_id_20;
SELECT DECODE (
SUBSTR (new_att_catg, INSTR (new_att_catg, 'ALIAS_SEPARATOR') + 15),
'OT', 'PAEXPITDFF - OT',
'PAEXPITDFF - ST'
)
INTO new_att_catg
FROM DUAL;
SELECT DECODE (
hxc_alias_utility.get_sfl_from_alias_value (new_att_catg),
'OT', 'PAEXPITDFF - OT',
'PAEXPITDFF - ST'
)
INTO new_att_catg
FROM DUAL;
SELECT hxc_deposit_wrapper_utilities.get_dupdff_code (
'PAEXPITDFF - '|| SUBSTR (new_att_catg, 1, INSTR (new_att_catg, 'ALIAS_SEPARATOR') - 1)
)
INTO new_att_catg
FROM DUAL;
SELECT decode(SUBSTR(new_att_catg,INSTR(new_att_catg,'ALIAS_SEPARATOR')+15),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
INTO new_att_catg
FROM DUAL;
select decode(hxc_alias_utility.get_sfl_from_alias_value(new_att_catg),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
INTO new_att_catg
FROM DUAL;
p_ord_timecard.DELETE;
SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(829): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (p_tc_frdt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr')
UNION
SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(839): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (p_tc_todt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr')
UNION
SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(849): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
MINUS
SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(859): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (start_time, 'dd-mm-rrrr') = TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr') = TO_DATE (p_tc_todt, 'dd-mm-rrrr')
ORDER BY 4;
emp_tc_info.DELETE;
select start_time,stop_time,to_date(start_time)
from hxc_time_building_blocks
where resource_id = p_resource_id
and scope='TIMECARD'
and date_to=hr_general.end_of_time
and to_date(start_time,'dd-mm-rrrr') >=to_date(p_tc_frdt,'dd-mm-rrrr') and
to_date(stop_time,'dd-mm-rrrr') <=to_date(p_tc_todt,'dd-mm-rrrr')
union
select tc_frdt,tc_todt,to_date(tc_frdt)
from dual
order by 3;
SELECT TO_DATE (start_time, 'dd-mm-rrrr hh24:mi:ss') start_time,
TO_DATE (stop_time, 'dd-mm-rrrr hh24:mi:ss') stop_time, TO_DATE (start_time, 'dd-mm-rrrr') orddt
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(922): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
UNION
SELECT TO_DATE (tc_frdt
/*ADVICE(930): Cursor references an external variable (use a parameter) [209] */
, 'dd-mm-rrrr hh24:mi:ss'), TO_DATE (tc_todt
/*ADVICE(932): Cursor references an external variable (use a parameter) [209] */
, 'dd-mm-rrrr hh24:mi:ss'),
TO_DATE (tc_frdt
/*ADVICE(935): Cursor references an external variable (use a parameter) [209] */
, 'dd-mm-rrrr')
FROM DUAL
ORDER BY 3;
emp_qry_tc_info.DELETE;
newtab.DELETE;
newtab.DELETE (emp_tab_index);
hr_utility.trace('in delete emptab ');
select max(date_start) from
per_periods_of_service
where person_id = p_resource_id; */
IS SELECT date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE(final_process_date,actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
UNION
SELECT date_start
FROM per_periods_of_placement
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
ORDER BY date_start ;
SELECT final_process_date, date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
union all
SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
FROM PER_PERIODS_OF_placement
WHERE person_id = p_resource_id
ORDER BY date_start DESC;
IS SELECT final_process_date,
date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE(final_process_date,actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
UNION
SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date,
date_start
FROM PER_PERIODS_OF_placement
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
ORDER BY date_start DESC;
SELECT TRUNC (start_time), TRUNC (stop_time)
FROM hxc_time_building_blocks
WHERE SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND resource_id = p_resource_id
AND resource_type = p_resource_type
AND stop_time >= p_first_start_date
AND start_time <= p_last_end_date;
SELECT hrp.period_type, hrp.duration_in_days, hrp.start_date
FROM hxc_recurring_periods hrp
WHERE hrp.recurring_period_id = p_recurring_period_id;
select max(date_start) from
per_periods_of_service
where person_id = p_resource_id;
/*SELECT MIN (effective_start_date)
FROM per_people_f
WHERE person_id = p_resource_id;*/
IS SELECT date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
AND COALESCE(final_process_date,actual_termination_date,
hr_general.end_of_time) > p_past_date
UNION
SELECT date_start
FROM per_periods_of_placement
WHERE person_id = p_resource_id
AND COALESCE(final_process_date,actual_termination_date,
hr_general.end_of_time) > p_past_date
ORDER BY date_start ;
SELECT final_process_date, date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
union all
select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
from per_periods_of_placement
where person_id = p_resource_id
ORDER BY date_start DESC;
IS SELECT final_process_date start_date,
NVL(LEAD(date_start) OVER (ORDER BY date_start)-1,hr_general.end_of_time) end_date
FROM ( SELECT date_start,
COALESCE(final_process_date+1,actual_termination_date+1,hr_general.end_of_time)
final_process_date
FROM per_periods_of_service
WHERE person_id = p_resource_id
UNION
SELECT date_start,
COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0))+1,actual_termination_date+1,hr_general.end_of_time)
final_process_date
FROM per_periods_of_placement
WHERE person_id = p_resource_id
)
ORDER BY start_date;
SELECT count(*)
FROM hxc_time_building_blocks
WHERE SCOPE = 'TIMECARD'
AND (date_to = hr_general.end_of_time or APPROVAL_STATUS='ERROR')
AND resource_id = p_resource_id
AND p_terminate_date between start_time and stop_time;
l_temp_periods.DELETE(l_index);
p_periodtab.DELETE (l_first_rowindex);
p_periodtab.DELETE (l_first_rowindex);
SELECT 1
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM hxc_transactions t, hxc_transaction_details td
WHERE td.time_building_block_id = p_timecard_id
/*ADVICE(1589): Cursor references an external variable (use a parameter) [209] */
AND t.transaction_id = td.transaction_id
AND t.TYPE = 'RETRIEVAL'
AND t.status = 'SUCCESS');
p_insert_data_details IN hxc_timekeeper_process.t_time_info,
p_old_value IN OUT NOCOPY VARCHAR2,
p_new_value IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_attribute_number = 1 THEN
p_new_value := p_insert_data_details.attr_id_1;
p_old_value := p_insert_data_details.attr_oldid_1;
p_new_value := p_insert_data_details.attr_id_2;
p_old_value := p_insert_data_details.attr_oldid_2;
p_new_value := p_insert_data_details.attr_id_3;
p_old_value := p_insert_data_details.attr_oldid_3;
p_new_value := p_insert_data_details.attr_id_4;
p_old_value := p_insert_data_details.attr_oldid_4;
p_new_value := p_insert_data_details.attr_id_5;
p_old_value := p_insert_data_details.attr_oldid_5;
p_new_value := p_insert_data_details.attr_id_6;
p_old_value := p_insert_data_details.attr_oldid_6;
p_new_value := p_insert_data_details.attr_id_7;
p_old_value := p_insert_data_details.attr_oldid_7;
p_new_value := p_insert_data_details.attr_id_8;
p_old_value := p_insert_data_details.attr_oldid_8;
p_new_value := p_insert_data_details.attr_id_9;
p_old_value := p_insert_data_details.attr_oldid_9;
p_new_value := p_insert_data_details.attr_id_10;
p_old_value := p_insert_data_details.attr_oldid_10;
p_new_value := p_insert_data_details.attr_id_11;
p_old_value := p_insert_data_details.attr_oldid_11;
p_new_value := p_insert_data_details.attr_id_12;
p_old_value := p_insert_data_details.attr_oldid_12;
p_new_value := p_insert_data_details.attr_id_13;
p_old_value := p_insert_data_details.attr_oldid_13;
p_new_value := p_insert_data_details.attr_id_14;
p_old_value := p_insert_data_details.attr_oldid_14;
p_new_value := p_insert_data_details.attr_id_15;
p_old_value := p_insert_data_details.attr_oldid_15;
p_new_value := p_insert_data_details.attr_id_16;
p_old_value := p_insert_data_details.attr_oldid_16;
p_new_value := p_insert_data_details.attr_id_17;
p_old_value := p_insert_data_details.attr_oldid_17;
p_new_value := p_insert_data_details.attr_id_18;
p_old_value := p_insert_data_details.attr_oldid_18;
p_new_value := p_insert_data_details.attr_id_19;
p_old_value := p_insert_data_details.attr_oldid_19;
p_new_value := p_insert_data_details.attr_id_20;
p_old_value := p_insert_data_details.attr_oldid_20;
p_insert_detail IN hxc_timekeeper_process.t_time_info,
p_measure IN OUT NOCOPY NUMBER,
p_detail_id IN OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
p_detail_ovn IN OUT NOCOPY NUMBER,
p_detail_time_in IN OUT NOCOPY DATE,
p_detail_time_out IN OUT NOCOPY DATE
) IS
l_attribute_found
/*ADVICE(1773): Unreferenced variable [553] */
BOOLEAN;
p_measure := p_insert_detail.day_1;
p_detail_id := p_insert_detail.detail_id_1;
p_detail_ovn := p_insert_detail.detail_ovn_1;
p_detail_time_in := p_insert_detail.time_in_1;
p_detail_time_out := p_insert_detail.time_out_1;
p_measure := p_insert_detail.day_2;
p_detail_id := p_insert_detail.detail_id_2;
p_detail_ovn := p_insert_detail.detail_ovn_2;
p_detail_time_in := p_insert_detail.time_in_2;
p_detail_time_out := p_insert_detail.time_out_2;
p_measure := p_insert_detail.day_3;
p_detail_id := p_insert_detail.detail_id_3;
p_detail_ovn := p_insert_detail.detail_ovn_3;
p_detail_time_in := p_insert_detail.time_in_3;
p_detail_time_out := p_insert_detail.time_out_3;
p_measure := p_insert_detail.day_4;
p_detail_id := p_insert_detail.detail_id_4;
p_detail_ovn := p_insert_detail.detail_ovn_4;
p_detail_time_in := p_insert_detail.time_in_4;
p_detail_time_out := p_insert_detail.time_out_4;
p_measure := p_insert_detail.day_5;
p_detail_id := p_insert_detail.detail_id_5;
p_detail_ovn := p_insert_detail.detail_ovn_5;
p_detail_time_in := p_insert_detail.time_in_5;
p_detail_time_out := p_insert_detail.time_out_5;
p_measure := p_insert_detail.day_6;
p_detail_id := p_insert_detail.detail_id_6;
p_detail_ovn := p_insert_detail.detail_ovn_6;
p_detail_time_in := p_insert_detail.time_in_6;
p_detail_time_out := p_insert_detail.time_out_6;
p_measure := p_insert_detail.day_7;
p_detail_id := p_insert_detail.detail_id_7;
p_detail_ovn := p_insert_detail.detail_ovn_7;
p_detail_time_in := p_insert_detail.time_in_7;
p_detail_time_out := p_insert_detail.time_out_7;
p_measure := p_insert_detail.day_8;
p_detail_id := p_insert_detail.detail_id_8;
p_detail_ovn := p_insert_detail.detail_ovn_8;
p_detail_time_in := p_insert_detail.time_in_8;
p_detail_time_out := p_insert_detail.time_out_8;
p_measure := p_insert_detail.day_9;
p_detail_id := p_insert_detail.detail_id_9;
p_detail_ovn := p_insert_detail.detail_ovn_9;
p_detail_time_in := p_insert_detail.time_in_9;
p_detail_time_out := p_insert_detail.time_out_9;
p_measure := p_insert_detail.day_10;
p_detail_id := p_insert_detail.detail_id_10;
p_detail_ovn := p_insert_detail.detail_ovn_10;
p_detail_time_in := p_insert_detail.time_in_10;
p_detail_time_out := p_insert_detail.time_out_10;
p_measure := p_insert_detail.day_11;
p_detail_id := p_insert_detail.detail_id_11;
p_detail_ovn := p_insert_detail.detail_ovn_11;
p_detail_time_in := p_insert_detail.time_in_11;
p_detail_time_out := p_insert_detail.time_out_11;
p_measure := p_insert_detail.day_12;
p_detail_id := p_insert_detail.detail_id_12;
p_detail_ovn := p_insert_detail.detail_ovn_12;
p_detail_time_in := p_insert_detail.time_in_12;
p_detail_time_out := p_insert_detail.time_out_12;
p_measure := p_insert_detail.day_13;
p_detail_id := p_insert_detail.detail_id_13;
p_detail_ovn := p_insert_detail.detail_ovn_13;
p_detail_time_in := p_insert_detail.time_in_13;
p_detail_time_out := p_insert_detail.time_out_13;
p_measure := p_insert_detail.day_14;
p_detail_id := p_insert_detail.detail_id_14;
p_detail_ovn := p_insert_detail.detail_ovn_14;
p_detail_time_in := p_insert_detail.time_in_14;
p_detail_time_out := p_insert_detail.time_out_14;
p_measure := p_insert_detail.day_15;
p_detail_id := p_insert_detail.detail_id_15;
p_detail_ovn := p_insert_detail.detail_ovn_15;
p_detail_time_in := p_insert_detail.time_in_15;
p_detail_time_out := p_insert_detail.time_out_15;
p_measure := p_insert_detail.day_16;
p_detail_id := p_insert_detail.detail_id_16;
p_detail_ovn := p_insert_detail.detail_ovn_16;
p_detail_time_in := p_insert_detail.time_in_16;
p_detail_time_out := p_insert_detail.time_out_16;
p_measure := p_insert_detail.day_17;
p_detail_id := p_insert_detail.detail_id_17;
p_detail_ovn := p_insert_detail.detail_ovn_17;
p_detail_time_in := p_insert_detail.time_in_17;
p_detail_time_out := p_insert_detail.time_out_17;
p_measure := p_insert_detail.day_18;
p_detail_id := p_insert_detail.detail_id_18;
p_detail_ovn := p_insert_detail.detail_ovn_18;
p_detail_time_in := p_insert_detail.time_in_18;
p_detail_time_out := p_insert_detail.time_out_18;
p_measure := p_insert_detail.day_19;
p_detail_id := p_insert_detail.detail_id_19;
p_detail_ovn := p_insert_detail.detail_ovn_19;
p_detail_time_in := p_insert_detail.time_in_19;
p_detail_time_out := p_insert_detail.time_out_19;
p_measure := p_insert_detail.day_20;
p_detail_id := p_insert_detail.detail_id_20;
p_detail_ovn := p_insert_detail.detail_ovn_20;
p_detail_time_in := p_insert_detail.time_in_20;
p_detail_time_out := p_insert_detail.time_out_20;
p_measure := p_insert_detail.day_21;
p_detail_id := p_insert_detail.detail_id_21;
p_detail_ovn := p_insert_detail.detail_ovn_21;
p_detail_time_in := p_insert_detail.time_in_21;
p_detail_time_out := p_insert_detail.time_out_21;
p_measure := p_insert_detail.day_22;
p_detail_id := p_insert_detail.detail_id_22;
p_detail_ovn := p_insert_detail.detail_ovn_22;
p_detail_time_in := p_insert_detail.time_in_22;
p_detail_time_out := p_insert_detail.time_out_22;
p_measure := p_insert_detail.day_23;
p_detail_id := p_insert_detail.detail_id_23;
p_detail_ovn := p_insert_detail.detail_ovn_23;
p_detail_time_in := p_insert_detail.time_in_23;
p_detail_time_out := p_insert_detail.time_out_23;
p_measure := p_insert_detail.day_24;
p_detail_id := p_insert_detail.detail_id_24;
p_detail_ovn := p_insert_detail.detail_ovn_24;
p_detail_time_in := p_insert_detail.time_in_24;
p_detail_time_out := p_insert_detail.time_out_24;
p_measure := p_insert_detail.day_25;
p_detail_id := p_insert_detail.detail_id_25;
p_detail_ovn := p_insert_detail.detail_ovn_25;
p_detail_time_in := p_insert_detail.time_in_25;
p_detail_time_out := p_insert_detail.time_out_25;
p_measure := p_insert_detail.day_26;
p_detail_id := p_insert_detail.detail_id_26;
p_detail_ovn := p_insert_detail.detail_ovn_26;
p_detail_time_in := p_insert_detail.time_in_26;
p_detail_time_out := p_insert_detail.time_out_26;
p_measure := p_insert_detail.day_27;
p_detail_id := p_insert_detail.detail_id_27;
p_detail_ovn := p_insert_detail.detail_ovn_27;
p_detail_time_in := p_insert_detail.time_in_27;
p_detail_time_out := p_insert_detail.time_out_27;
p_measure := p_insert_detail.day_28;
p_detail_id := p_insert_detail.detail_id_28;
p_detail_ovn := p_insert_detail.detail_ovn_28;
p_detail_time_in := p_insert_detail.time_in_28;
p_detail_time_out := p_insert_detail.time_out_28;
p_measure := p_insert_detail.day_29;
p_detail_id := p_insert_detail.detail_id_29;
p_detail_ovn := p_insert_detail.detail_ovn_29;
p_detail_time_in := p_insert_detail.time_in_29;
p_detail_time_out := p_insert_detail.time_out_29;
p_measure := p_insert_detail.day_30;
p_detail_id := p_insert_detail.detail_id_30;
p_detail_ovn := p_insert_detail.detail_ovn_30;
p_detail_time_in := p_insert_detail.time_in_30;
p_detail_time_out := p_insert_detail.time_out_30;
p_measure := p_insert_detail.day_31;
p_detail_id := p_insert_detail.detail_id_31;
p_detail_ovn := p_insert_detail.detail_ovn_31;
p_detail_time_in := p_insert_detail.time_in_31;
p_detail_time_out := p_insert_detail.time_out_31;
SELECT bld_blk_info_type_id
INTO l_bldtyp_id
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'Dummy Paexpitdff Context';
l_delete_index BINARY_INTEGER
/*ADVICE(2241): Consider using PLS_INTEGER instead of INTEGER and BINARY_INTEGER if on Oracle 7.3 or above
[302] */
;
l_delete_index := NULL;
l_delete_index := l_index;
IF l_delete_index IS NOT NULL THEN
--now delete the error as we have handled it
p_messages.DELETE (l_delete_index);
SELECT MIN (effective_start_date)
FROM per_people_f
WHERE person_id = p_resource_id;
SELECT final_process_date, date_start
FROM per_periods_of_service
WHERE person_id = p_resource_id
union all
select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
from per_periods_of_placement
where person_id = p_resource_id
ORDER BY date_start DESC;
l_query_append := ' in (select htgqc.criteria_id resource_id ';
l_pref_table.DELETE;
l_resource_table.DELETE;
g_start_stop_pref_cache.DELETE;
select recurring_period_id rec_period,rownum periodname_index from (
SELECT distinct c.recurring_period_id,c.name
FROM hxc_pref_hierarchies a, hxc_resource_rules b, hxc_recurring_periods c
WHERE a.attribute_category = 'TC_W_TCRD_PERIOD'
and a.attribute1 = c.recurring_period_id
AND a.top_level_parent_id = b.pref_hierarchy_id
AND p_business_group_id = NVL (a.business_group_id, p_business_group_id) order by c.name);
p_periodname_list.DELETE;
hxc_alias_utility.get_vset_table_type_select (
p_alias_definition_id => p_aliasid,
x_select => l_s_query,
p_id_type => l_colmtype
);
l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
hxc_alias_utility.get_vset_indep_type_select (
p_alias_definition_id => p_aliasid,
x_select => l_s_query
);
l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
hxc_alias_utility.get_otl_an_context_type_select (
p_alias_definition_id => p_aliasid,
p_timekeeper_person_type => p_person_type,
x_select => l_s_query
);
l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
SELECT typ.system_person_type
FROM per_person_types typ, per_person_type_usages_f ptu
WHERE typ.person_type_id = ptu.person_type_id
AND SYSDATE BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ptu.person_id = p_person_id;
SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
FROM hxc_time_building_blocks
WHERE resource_id
/*ADVICE(3110): Cursor references an external variable (use a parameter) [209] */
= p_resource_id
AND SCOPE = 'TIMECARD'
AND date_to = hr_general.end_of_time
AND TO_DATE (start_time, 'dd-mm-rrrr') < TO_DATE (p_tc_todt, 'dd-mm-rrrr')
AND TO_DATE (stop_time, 'dd-mm-rrrr') > TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
AND ( TO_DATE (start_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
OR TO_DATE (stop_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_todt, 'dd-mm-rrrr')
)
ORDER BY 4;
l_newtab.DELETE;
cursor csr_tk is select * from hxc_tk_detail_temp;
insert into hxc_tk_detail_temp
(RESOURCE_ID,
TIMECARD_ID,
DETAILID,
COMMENT_TEXT,
DFF_CATG ,
DETAIL_ACTION ,
DFF_ATTR1 ,
DFF_ATTR2 ,
DFF_ATTR3 ,
DFF_ATTR4 ,
DFF_ATTR5 ,
DFF_ATTR6 ,
DFF_ATTR7 ,
DFF_ATTR8 ,
DFF_ATTR9 ,
DFF_ATTR10 ,
DFF_ATTR11 ,
DFF_ATTR12 ,
DFF_ATTR13 ,
DFF_ATTR14 ,
DFF_ATTR15 ,
DFF_ATTR16 ,
DFF_ATTR17 ,
DFF_ATTR18 ,
DFF_ATTR19 ,
DFF_ATTR20 ,
DFF_ATTR21 ,
DFF_ATTR22 ,
DFF_ATTR23 ,
DFF_ATTR24 ,
DFF_ATTR25 ,
DFF_ATTR26 ,
DFF_ATTR27 ,
DFF_ATTR28 ,
DFF_ATTR29 ,
DFF_ATTR30 ,
DFF_OLDATTR1 ,
DFF_OLDATTR2 ,
DFF_OLDATTR3 ,
DFF_OLDATTR4 ,
DFF_OLDATTR5 ,
DFF_OLDATTR6 ,
DFF_OLDATTR7 ,
DFF_OLDATTR8 ,
DFF_OLDATTR9 ,
DFF_OLDATTR10 ,
DFF_OLDATTR11 ,
DFF_OLDATTR12 ,
DFF_OLDATTR13 ,
DFF_OLDATTR14 ,
DFF_OLDATTR15 ,
DFF_OLDATTR16 ,
DFF_OLDATTR17 ,
DFF_OLDATTR18 ,
DFF_OLDATTR19 ,
DFF_OLDATTR20 ,
DFF_OLDATTR21 ,
DFF_OLDATTR22 ,
DFF_OLDATTR23 ,
DFF_OLDATTR24 ,
DFF_OLDATTR25 ,
DFF_OLDATTR26 ,
DFF_OLDATTR27 ,
DFF_OLDATTR28 ,
DFF_OLDATTR29 ,
DFF_OLDATTR30 ,
DFF_OLDCATG ,
CHANGE_REASON ,
CHANGE_COMMENT ,
LATE_REASON ,
LATE_COMMENT ,
LATE_CHANGE ,
DESC_FLEX ,
ATTRIBUTE_CATEGORY ,
AUDIT_DATETIME ,
AUDIT_HISTORY ,
DISP_INDEX ,
OLD_CHANGE_REASON ,
OLD_CHANGE_COMMENT ,
OLD_LATE_REASON ,
OLD_LATE_COMMENT ,
OLD_AUDIT_HISTORY ,
OLD_LATE_CHANGE ,
OLD_AUDIT_DATETIME )
values
(
l_detail_blocks(indx).RESOURCE_ID,
l_detail_blocks(indx).TIMECARD_ID,
l_detail_blocks(indx).DETAILID,
l_detail_blocks(indx).COMMENT_TEXT,
l_detail_blocks(indx).DFF_CATG ,
l_detail_blocks(indx).DETAIL_ACTION ,
l_detail_blocks(indx).DFF_ATTR1 ,
l_detail_blocks(indx).DFF_ATTR2 ,
l_detail_blocks(indx).DFF_ATTR3 ,
l_detail_blocks(indx).DFF_ATTR4 ,
l_detail_blocks(indx).DFF_ATTR5 ,
l_detail_blocks(indx).DFF_ATTR6 ,
l_detail_blocks(indx).DFF_ATTR7 ,
l_detail_blocks(indx).DFF_ATTR8 ,
l_detail_blocks(indx).DFF_ATTR9 ,
l_detail_blocks(indx).DFF_ATTR10 ,
l_detail_blocks(indx).DFF_ATTR11 ,
l_detail_blocks(indx).DFF_ATTR12 ,
l_detail_blocks(indx).DFF_ATTR13 ,
l_detail_blocks(indx).DFF_ATTR14 ,
l_detail_blocks(indx).DFF_ATTR15 ,
l_detail_blocks(indx).DFF_ATTR16 ,
l_detail_blocks(indx).DFF_ATTR17 ,
l_detail_blocks(indx).DFF_ATTR18 ,
l_detail_blocks(indx).DFF_ATTR19 ,
l_detail_blocks(indx).DFF_ATTR20 ,
l_detail_blocks(indx).DFF_ATTR21 ,
l_detail_blocks(indx).DFF_ATTR22 ,
l_detail_blocks(indx).DFF_ATTR23 ,
l_detail_blocks(indx).DFF_ATTR24 ,
l_detail_blocks(indx).DFF_ATTR25 ,
l_detail_blocks(indx).DFF_ATTR26 ,
l_detail_blocks(indx).DFF_ATTR27 ,
l_detail_blocks(indx).DFF_ATTR28 ,
l_detail_blocks(indx).DFF_ATTR29 ,
l_detail_blocks(indx).DFF_ATTR30 ,
l_detail_blocks(indx).DFF_OLDATTR1 ,
l_detail_blocks(indx).DFF_OLDATTR2 ,
l_detail_blocks(indx).DFF_OLDATTR3 ,
l_detail_blocks(indx).DFF_OLDATTR4 ,
l_detail_blocks(indx).DFF_OLDATTR5 ,
l_detail_blocks(indx).DFF_OLDATTR6 ,
l_detail_blocks(indx).DFF_OLDATTR7 ,
l_detail_blocks(indx).DFF_OLDATTR8 ,
l_detail_blocks(indx).DFF_OLDATTR9 ,
l_detail_blocks(indx).DFF_OLDATTR10 ,
l_detail_blocks(indx).DFF_OLDATTR11 ,
l_detail_blocks(indx).DFF_OLDATTR12 ,
l_detail_blocks(indx).DFF_OLDATTR13 ,
l_detail_blocks(indx).DFF_OLDATTR14 ,
l_detail_blocks(indx).DFF_OLDATTR15 ,
l_detail_blocks(indx).DFF_OLDATTR16 ,
l_detail_blocks(indx).DFF_OLDATTR17 ,
l_detail_blocks(indx).DFF_OLDATTR18 ,
l_detail_blocks(indx).DFF_OLDATTR19 ,
l_detail_blocks(indx).DFF_OLDATTR20 ,
l_detail_blocks(indx).DFF_OLDATTR21 ,
l_detail_blocks(indx).DFF_OLDATTR22 ,
l_detail_blocks(indx).DFF_OLDATTR23 ,
l_detail_blocks(indx).DFF_OLDATTR24 ,
l_detail_blocks(indx).DFF_OLDATTR25 ,
l_detail_blocks(indx).DFF_OLDATTR26 ,
l_detail_blocks(indx).DFF_OLDATTR27 ,
l_detail_blocks(indx).DFF_OLDATTR28 ,
l_detail_blocks(indx).DFF_OLDATTR29 ,
l_detail_blocks(indx).DFF_OLDATTR30 ,
l_detail_blocks(indx).DFF_OLDCATG ,
l_detail_blocks(indx).CHANGE_REASON ,
l_detail_blocks(indx).CHANGE_COMMENT ,
l_detail_blocks(indx).LATE_REASON ,
l_detail_blocks(indx).LATE_COMMENT ,
l_detail_blocks(indx).LATE_CHANGE ,
l_detail_blocks(indx).DESC_FLEX ,
l_detail_blocks(indx).ATTRIBUTE_CATEGORY ,
l_detail_blocks(indx).AUDIT_DATETIME ,
l_detail_blocks(indx).AUDIT_HISTORY ,
l_detail_blocks(indx).DISP_INDEX ,
l_detail_blocks(indx).OLD_CHANGE_REASON ,
l_detail_blocks(indx).OLD_CHANGE_COMMENT ,
l_detail_blocks(indx).OLD_LATE_REASON ,
l_detail_blocks(indx).OLD_LATE_COMMENT ,
l_detail_blocks(indx).OLD_AUDIT_HISTORY ,
l_detail_blocks(indx).OLD_LATE_CHANGE ,
l_detail_blocks(indx).OLD_AUDIT_DATETIME );
SELECT attribute2
FROM hxc_alias_values
WHERE alias_value_id = p_id AND attribute_category = 'ELEMENTS_EXPENDITURE_SLF';
SELECT 'Y'
INTO l_dummy
FROM fnd_descr_flex_contexts
WHERE application_id = 809
AND descriptive_flexfield_name = 'OTC Information Types'
AND descriptive_flex_context_code LIKE p_context_prefix || '%GLOBAL%';
SELECT date_start,
final_process_date
FROM per_periods_of_service
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE(final_process_date,
actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
UNION
SELECT date_start,
(final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date
FROM per_periods_of_placement
WHERE person_id = p_resource_id
AND date_start <= p_tc_end_date
AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0))
,actual_termination_date,
hr_general.end_of_time) >= p_tc_start_date
ORDER BY date_start ;
hr_utility.trace (' SVG bfore l_detail_id select query');
SELECT time_building_block_id
INTO l_detail_id
FROM hxc_abs_co_details
WHERE start_time = p_start_period
AND trunc(stop_time) = trunc(p_end_period)
AND resource_id = p_resource_id
--AND nvl(absence_attendance_id,transaction_id) = p_absence_attendance_id
AND lock_rowid = p_lock_row_id
--AND stage = 'PREP'
AND ((stage='PREP' AND absence_attendance_id = p_absence_attendance_id)
OR
(stage='PREP-SS' AND transaction_id = p_transaction_id))
AND (
( UOM='D'
and measure = p_absence_duration
and start_date = end_date
and trunc(to_number(start_date - start_time)) = (l_day_value - 1)
)
OR
( UOM='H'
and start_date = p_absence_start_time
and end_date = p_absence_stop_time
))
AND sessionid = userenv('SESSIONID')
AND rownum<2;
hr_utility.trace (' SVG after l_detail_id select query');
SELECT hav.alias_value_id
FROM hxc_alias_values hav,
hxc_alias_definitions had,
hxc_alias_types hat
WHERE hav.attribute1=to_char(p_element_type_id) and
hav.enabled_flag='Y' and
hav.attribute_category='PAYROLL_ELEMENTS' and
--nvl(hav.date_from,hr_general.start_of_time) <= p_start_period and
--nvl(hav.date_to,hr_general.end_of_time) >= p_end_period and
nvl(hav.date_from,hr_general.start_of_time) <= p_end_period and
nvl(hav.date_to,hr_general.end_of_time) >= p_start_period and
hav.alias_definition_id=p_alias_definition_id and
hav.alias_definition_id=had.alias_definition_id and
had.alias_type_id=hat.alias_type_id and
hat.alias_type = 'OTL_ALT_DDF' and
hat.reference_object = 'PAYROLL_ELEMENTS'and
rownum < 2;
hxc_retrieve_absences.g_messages.DELETE;
if (g_abs_message_string = 'HXC_ABS_PEND_APPR_DELETE' OR
g_abs_message_string = 'HXC_ABS_PEND_APPR_ERROR' ) then
hr_utility.trace('Raising exception for '|| p_resource_id);