The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT operator
FROM hxc_time_categories
WHERE time_category_id = p_time_category_id;
SELECT
bbit.bld_blk_info_type context
, bbit.bld_blk_info_type_id
, mpc.segment
, NVL(tcc.value_id, DECODE(tcc.is_null, 'N', '', '')) value_id
, tcc.ref_time_category_id
, tcc.flex_value_set_id
, tcc.equal_to
FROM
hxc_bld_blk_info_types bbit
, hxc_mapping_components mpc
, hxc_time_category_comps tcc
WHERE tcc.time_category_id = p_time_category_id AND
tcc.type = 'MC'
AND
mpc.mapping_component_id (+) = tcc.component_type_id
AND
bbit.bld_blk_info_type_id (+) = mpc.bld_blk_info_type_id;
SELECT
bbit.bld_blk_info_type context
, bbit.bld_blk_info_type_id
, DECODE( bbit.bld_blk_info_type,
'Dummy Cost Context', REPLACE( mpc.segment, 'CostSegment', 'ATTRIBUTE' ),
'Dummy Grp Context', REPLACE( mpc.segment, 'GrpSegment', 'ATTRIBUTE' ),
'Dummy Job Context', REPLACE( mpc.segment, 'JobSegment', 'ATTRIBUTE' ),
'Dummy Pos Context', REPLACE( mpc.segment, 'PosSegment', 'ATTRIBUTE' ),
'Dummy Paexpitdff Context', REPLACE( mpc.segment, 'PADFFAttribute', 'ATTRIBUTE' ),
mpc.segment ) segment
, mpc.name
, atc.component_type application_column_name
, av.attribute1
, av.attribute2
, av.attribute3
, av.attribute4
, av.attribute5
, av.attribute6
, av.attribute7
, av.attribute8
, av.attribute9
, av.attribute10
, av.attribute11
, av.attribute12
, av.attribute13
, av.attribute14
, av.attribute15
, av.attribute16
, av.attribute17
, av.attribute18
, av.attribute19
, av.attribute20
, av.attribute21
, av.attribute22
, av.attribute23
, av.attribute24
, av.attribute25
, av.attribute26
, av.attribute27
, av.attribute28
, av.attribute29
, av.attribute30
FROM
hxc_bld_blk_info_types bbit
, hxc_mapping_components mpc
, hxc_alias_type_components atc
, hxc_alias_types hat
, hxc_alias_definitions ad
, hxc_alias_values av
WHERE
av.alias_value_id = p_alias_value_id
AND
ad.alias_definition_id = av.alias_definition_id
AND
hat.alias_type_id = ad.alias_type_id
AND
atc.alias_type_id = hat.alias_type_id
AND
mpc.mapping_component_id = atc.mapping_component_id
AND
bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id;
SELECT time_category_comp_sql_id tcc_sql_id
FROM hxc_time_category_comp_sql
WHERE time_category_comp_id = p_tcc_id;
SELECT htc.time_sql
, htc.operator
FROM hxc_time_categories htc
WHERE htc.time_category_id = p_time_category_id
AND EXISTS ( select 'x'
from hxc_time_category_comps tcc
where tcc.time_category_id = htc.time_category_id );
SELECT sql_string
FROM hxc_time_category_comp_sql
WHERE time_category_comp_id = p_tcc_id;
CURSOR get_value_set_last_update_date ( p_flex_value_set_id NUMBER ) IS
SELECT vs.last_update_date
FROM fnd_flex_value_sets vs
WHERE vs.flex_value_set_id = p_flex_value_set_id;
CURSOR get_vset_tab_last_update_date ( p_flex_value_set_id NUMBER ) IS
SELECT vst.last_update_date
FROM fnd_flex_validation_tables vst
WHERE vst.flex_value_set_id = p_flex_value_set_id;
l_vs_last_update_date DATE;
OPEN get_value_set_last_update_date ( p_vs_comp_rec.flex_value_set_id );
FETCH get_value_set_last_update_date INTO l_vs_last_update_date;
CLOSE get_value_set_last_update_date;
hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
THEN
-- value set definition changed since time category comp
-- created
p_rec.time_category_comp_id := p_vs_comp_rec.time_category_comp_id;
-- check table last update date
OPEN get_vset_tab_last_update_date ( p_vs_comp_rec.flex_value_set_id );
FETCH get_vset_tab_last_update_date INTO l_vs_last_update_date;
CLOSE get_vset_tab_last_update_date;
hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
THEN
-- value set definition changed since time category comp
-- created
p_rec.time_category_comp_id := p_vs_comp_rec.time_category_comp_id;
update_time_category_comp_sql ( p_rec => l_rec );
UPDATE hxc_time_category_comps tcc
SET time_category_comp_id = l_rec.time_category_comp_id
WHERE time_category_comp_id = l_rec.time_category_comp_id;
g_tc_cache.DELETE;
g_tc_bb_ok_cache.DELETE;
l_sql CLOB := 'select distinct ta.bb_id from hxc_tmp_atts ta where '; -- Bug 15977687
INSERT INTO hxc_tmp_atts (
ta_id
, bb_id
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
, attribute21
, attribute22
, attribute23
, attribute24
, attribute25
, attribute26
, attribute27
, attribute28
, attribute29
, attribute30
, bld_blk_info_type_id
, attribute_category )
VALUES (
1
, 2
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 'Dummy'
, 1
, 'Dummy' );
l_sql := 'select distinct ta.bb_id from hxc_tmp_atts ta where '||l_parse_time_sql;
t_bb_id.DELETE;
SELECT additional_where_clause
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_flex_value_set_id;
l_comps_t.DELETE;
SELECT 'x'
FROM dual
WHERE EXISTS ( select 'y'
from hxc_tmp_blks );
-- we did error here but on delete there are never
-- going to be any blocks
ELSE
CLOSE csr_chk_bld_blks_not_empty;
INSERT INTO hxc_tmp_atts (
ta_id
, bb_id
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
, attribute21
, attribute22
, attribute23
, attribute24
, attribute25
, attribute26
, attribute27
, attribute28
, attribute29
, attribute30
, bld_blk_info_type_id
, attribute_category )
VALUES (
t_ta_id(attx)
, t_bb_id(attx)
, t_attribute1(attx)
, t_attribute2(attx)
, t_attribute3(attx)
, t_attribute4(attx)
, t_attribute5(attx)
, t_attribute6(attx)
, t_attribute7(attx)
, t_attribute8(attx)
, t_attribute9(attx)
, t_attribute10(attx)
, t_attribute11(attx)
, t_attribute12(attx)
, t_attribute13(attx)
, t_attribute14(attx)
, t_attribute15(attx)
, t_attribute16(attx)
, t_attribute17(attx)
, t_attribute18(attx)
, t_attribute19(attx)
, t_attribute20(attx)
, t_attribute21(attx)
, t_attribute22(attx)
, t_attribute23(attx)
, t_attribute24(attx)
, t_attribute25(attx)
, t_attribute26(attx)
, t_attribute27(attx)
, t_attribute28(attx)
, t_attribute29(attx)
, t_attribute30(attx)
, t_bld_blk_info_type_id(attx)
, t_attribute_category(attx) );
t_bb_id.delete;
t_ta_id.delete;
t_bld_blk_info_type_id.delete;
t_attribute_category.delete;
t_attribute1.delete;
t_attribute2.delete;
t_attribute3.delete;
t_attribute4.delete;
t_attribute5.delete;
t_attribute6.delete;
t_attribute7.delete;
t_attribute8.delete;
t_attribute9.delete;
t_attribute10.delete;
t_attribute11.delete;
t_attribute12.delete;
t_attribute13.delete;
t_attribute14.delete;
t_attribute15.delete;
t_attribute16.delete;
t_attribute17.delete;
t_attribute18.delete;
t_attribute19.delete;
t_attribute20.delete;
t_attribute21.delete;
t_attribute22.delete;
t_attribute23.delete;
t_attribute24.delete;
t_attribute25.delete;
t_attribute26.delete;
t_attribute27.delete;
t_attribute28.delete;
t_attribute29.delete;
t_attribute30.delete;
SELECT *
from hxc_tmp_blks;
SELECT *
from hxc_tmp_atts;
l_trunc_blks VARCHAR2(30) := 'delete from hxc_tmp_blks';
l_trunc_atts VARCHAR2(30) := 'delete from hxc_tmp_atts';
-- only copy blocks which are NOT deleted
IF ( FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).date_to) = hr_general.end_of_time )
THEN
IF ( g_debug ) THEN
hr_utility.trace('Scope : start time '||p_blocks(l_ind).scope||' : '||p_blocks(l_ind).start_time);
INSERT INTO hxc_tmp_blks (
bb_id
, measure
, type
, start_time
, stop_time
, scope
, comment_text )
VALUES (
t_bb_id(blkx)
, t_measure(blkx)
, t_type(blkx)
, t_start_time(blkx)
, t_stop_time(blkx)
, t_scope(blkx)
, t_comment_text(blkx) );
t_bb_id.delete;
t_measure.delete;
t_type.delete;
t_start_time.delete;
t_stop_time.delete;
t_comment_text.delete;
t_scope.delete;
l_select VARCHAR2(75) := '
SELECT DISTINCT ta.bb_id
FROM hxc_tmp_atts ta
WHERE ';
SELECT DISTINCT tau.time_building_block_id bb_id
FROM hxc_time_attributes ta
, hxc_time_attribute_usages tau
WHERE tau.time_building_block_id = :p_tbb_id AND
tau.time_building_block_ovn = :p_tbb_ovn
AND ta.time_attribute_id = tau.time_attribute_id
AND ';
SELECT DISTINCT detail.time_building_block_id bb_id
from hxc_time_attributes ta,
hxc_time_attribute_usages tau,
hxc_latest_details tbb_latest,
hxc_time_building_blocks detail,
hxc_time_building_blocks day
where day.parent_building_block_id = :p_tbb_id
and day.parent_building_block_ovn = :p_tbb_ovn
and detail.parent_building_block_id =
day.time_building_block_id
and detail.parent_building_block_ovn =
day.object_version_number
and detail.date_to = hr_general.end_of_time
and tbb_latest.time_building_block_id = detail.time_building_Block_id
and tbb_latest.object_version_number = detail.object_version_number
and tau.time_building_block_id = tbb_latest.time_building_block_id
and tau.time_building_block_ovn = tbb_latest.object_Version_number
AND ta.time_attribute_id = tau.time_attribute_id
AND ';
l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_ok_string)||' ) ';
p_tc_bb_ok_tab.DELETE;
l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_not_ok_string)||' ) ';
l_sql := l_select || l_parse_time_sql;
p_tc_bb_ok_tab.DELETE;
p_tc_bb_ok_tab.DELETE;
t_bb_id.DELETE;
SELECT
bbit.bld_blk_info_type context
, bbit.bld_blk_info_type_id
, mpc.segment
FROM
hxc_bld_blk_info_types bbit
, hxc_mapping_components mpc
WHERE
mpc.mapping_component_id = p_mapping_component_id
AND
bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id;
l_vs_sql := ' SELECT ' || r_valueset.table_info.id_column_name || ' FROM ' ||
r_valueset.table_info.table_name || ' ' ||
l_where_clause || ' ) ';
l_select VARCHAR2(500) := '
SELECT SUM( DECODE( tbb.type, ''RANGE'',
FUNCTION((((tbb.stop_time)-(tbb.start_time))*24),PRECISION) + power(ZERO,PRECISION),
NVL(tbb.measure, 0) )) hrs
FROM hxc_tmp_blks tbb
WHERE tbb.scope = ''DETAIL'' AND
tbb.start_time < :p_period_end AND
tbb.start_time >= :p_period_start AND
tbb.bb_id IN ( ';
l_select_null VARCHAR2(500) := '
SELECT SUM( DECODE( tbb.type, ''RANGE'',
FUNCTION((((tbb.stop_time)-(tbb.start_time))*24),PRECISION) + power(ZERO,PRECISION),
NVL(tbb.measure, 0) )) hrs
FROM hxc_tmp_blks tbb
WHERE tbb.scope = ''DETAIL'' AND
tbb.start_time < :p_period_end AND
tbb.start_time >= :p_period_start ';
l_select := REPLACE(l_select,'FUNCTION','TRUNC');
l_select := REPLACE(l_select,'ZERO','0.1');
l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
l_select_null := REPLACE(l_select_null,'ZERO','0.1');
l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
l_select := REPLACE(l_select,'FUNCTION','TRUNC');
l_select := REPLACE(l_select,'ZERO','0');
l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
l_select_null := REPLACE(l_select_null,'ZERO','0');
l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
l_select := REPLACE(l_select,'FUNCTION','ROUND');
l_select := REPLACE(l_select,'ZERO','0');
l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
l_select_null := REPLACE(l_select_null,'ZERO','0');
l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
l_select := REPLACE(l_select,'FUNCTION','ROUND');
l_select := REPLACE(l_select,'ZERO','0');
l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
l_select_null := REPLACE(l_select_null,'ZERO','0');
l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
--l_sql := l_select || p_tc_bb_ok_string || ' ) ';
l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
l_sql := l_select_null;
l_select VARCHAR2(450) := '
SELECT SUM( DECODE( tbb.type, ''RANGE'',
(((tbb.stop_time)-(tbb.start_time))*24),
NVL(tbb.measure, 0) )) hrs
FROM hxc_time_building_blocks tbb
, hxc_latest_details hld
WHERE tbb.time_building_block_id = hld.time_building_block_id
AND tbb.object_version_number = hld.object_version_number
AND hld.time_building_block_id IN ( ';
--l_sql := l_select || p_tc_bb_ok_string || ' ) ';
l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
l_select VARCHAR2(700) := 'SELECT SUM( HXC_FIND_NOTIFY_APRS_PKG.apply_round_rule('||''''||
p_rounding_rule||''''||','||''''||
p_decimal_precision||''''||',
(DECODE( tbb.type, ''RANGE'',
(((tbb.stop_time)-(tbb.start_time))*24),
NVL(tbb.measure, 0) ))
)) hrs
FROM hxc_time_building_blocks tbb
, hxc_latest_details hld
WHERE tbb.time_building_block_id = hld.time_building_block_id
AND tbb.object_version_number = hld.object_version_number
AND hld.time_building_block_id IN ( ';
l_sql := l_select || p_tc_bb_ok_string || ' ) ';
SELECT
tcc.time_category_id
, tcc.time_category_comp_id
, tcc.type
, bbit.bld_blk_info_type_id
, mpc.segment
, tcc.component_type_id
, tcc.ref_time_category_id
, tcc.flex_value_set_id
, tcc.value_id
, tcc.is_null
, tcc.equal_to
, tccs.sql_string
, tcc.last_update_date
FROM
hxc_time_category_comp_sql tccs
, hxc_bld_blk_info_types bbit
, hxc_mapping_components mpc
, hxc_time_category_comps tcc
WHERE tcc.time_category_id = p_time_category_id AND
tcc.type <> 'MC'
AND
mpc.mapping_component_id (+) = tcc.component_type_id
AND
bbit.bld_blk_info_type_id (+) = mpc.bld_blk_info_type_id
AND
tccs.time_category_comp_id (+) = tcc.time_category_comp_id;
SELECT bb_id,
scope
FROM hxc_tmp_blks;
SELECT detail.time_building_block_id bb_id,
detail.scope
FROM hxc_latest_details tbb_latest,
hxc_time_building_blocks detail,
hxc_time_building_blocks day
where day.parent_building_block_id = p_bb_id
and day.parent_building_block_ovn = p_bb_ovn
and detail.parent_building_block_id =
day.time_building_block_id
and detail.parent_building_block_ovn =
day.object_version_number
and tbb_latest.time_building_block_id = detail.time_building_Block_id
and tbb_latest.object_version_number = detail.object_version_number
and detail.date_to = hr_general.end_of_time;
l_vs_comp_tab.DELETE;
l_an_comp_tab.DELETE;
l_tc_comp_tab.DELETE;
l_vs_comp_tab(l_vs_ind).last_update_date := l_time_category_comps.last_update_date;
-- changed since the tc comp row was updated
-- if so - then call value_set_string and
-- maintain tccs again
IF ( g_debug ) THEN
hr_utility.trace('Evaluating MC_VS Loop');
PROCEDURE insert_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
l_proc VARCHAR2(72);
l_proc := g_package||'insert_time_category_comp_sql';
hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
INSERT INTO hxc_time_category_comp_sql (
time_category_comp_sql_id
, time_category_comp_id
, sql_string )
VALUES (
hxc_time_category_comp_sql_s.nextval
, p_rec.time_category_comp_id
, l_sql );
END insert_time_category_comp_sql;
PROCEDURE update_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
l_proc VARCHAR2(72);
l_proc := g_package||'update_time_category_comp_sql';
UPDATE hxc_time_category_comp_sql
SET sql_string = l_sql
WHERE time_category_comp_id = p_rec.time_category_comp_id;
-- TCC SQL therefore delete redundant row
DELETE from hxc_time_category_comp_sql
WHERE time_category_comp_sql_id = l_tcc_sql.tcc_sql_id;
insert_time_category_comp_sql ( p_rec );
END update_time_category_comp_sql;
PROCEDURE delete_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
l_proc VARCHAR2(72);
l_proc := g_package||'delete_time_category_comp_sql';
DELETE from hxc_time_category_comp_sql
WHERE time_category_comp_sql_id = tcc_sql.tcc_sql_id;
END delete_time_category_comp_sql;
l_select_clause VARCHAR2(2000);
select 'SELECT ' ||
l_valueset_r.table_info.value_column_name ||
decode(l_valueset_r.table_info.meaning_column_name,null,',NULL ',
','||l_valueset_r.table_info.meaning_column_name)||
decode(l_valueset_r.table_info.id_column_name,null,',NULL ',
','||l_valueset_r.table_info.id_column_name)||
' FROM ' ||
l_valueset_r.table_info.table_name || ' ' ||
l_valueset_r.table_info.where_clause
into l_sql_text
from dual;
l_select_clause := 'SELECT '||l_valueset_r.table_info.
meaning_column_name||' ';
l_select_clause := 'SELECT '||l_valueset_r.table_info.
value_column_name||' ';
l_sql_text_id := l_select_clause||l_from_where;
l_sql_text_id := l_select_clause||l_from_where ||'WHERE '||l_valueset_r.table_info.id_column_name||' = ';
l_sql_text_id := l_select_clause||l_from_where ||' and '||l_valueset_r.table_info.id_column_name||' = ';
l_sql_text_id := 'SELECT FLEX_VALUE'||
' FROM FND_FLEX_VALUES_VL'||
' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
' AND ENABLED_FLAG = ''Y'''||
' AND '''||P_SESSION_DATE||''' BETWEEN'||
' NVL(START_DATE_ACTIVE,'''||
P_SESSION_DATE||''')'||
' AND NVL(END_DATE_ACTIVE,'''||
P_SESSION_DATE||''')'||
' AND FLEX_VALUE = ';
select pett.element_name Display_Value
from pay_element_types_f_tl pett
where pett.element_type_id = p_element_type_id
and pett.language = USERENV('LANG');
SELECT htc.time_category_id
FROM hxc_time_categories htc
WHERE htc.time_category_name = p_time_category_name;
g_tc_bb_ok_tab.delete;
SELECT SUM( DECODE( detail.type, 'RANGE',
(((detail.stop_time)-(detail.start_time))*24),
NVL(detail.measure, 0) )) hrs
FROM hxc_latest_details tbb_latest,
hxc_time_building_blocks detail,
hxc_time_building_blocks day
where day.parent_building_block_id = p_tbb_id
and day.parent_building_block_ovn = p_tbb_ovn
and detail.parent_building_block_id =
day.time_building_block_id
and detail.parent_building_block_ovn =
day.object_version_number
and tbb_latest.time_building_block_id = detail.time_building_Block_id
and tbb_latest.object_version_number = detail.object_version_number
and detail.date_to = hr_general.end_of_time;
SELECT DECODE( detail.type, 'RANGE',
nvl((((detail.stop_time)-(detail.start_time))*24),0),
NVL(detail.measure, 0) ) hrs
FROM hxc_latest_details tbb_latest,
hxc_time_building_blocks detail,
hxc_time_building_blocks day
where day.parent_building_block_id = p_tbb_id
and day.parent_building_block_ovn = p_tbb_ovn
and detail.parent_building_block_id =
day.time_building_block_id
and detail.parent_building_block_ovn =
day.object_version_number
and tbb_latest.time_building_block_id = detail.time_building_Block_id
and tbb_latest.object_version_number = detail.object_version_number
and detail.date_to = hr_general.end_of_time;
SELECT tbb.resource_id
FROM hxc_time_building_blocks tbb
WHERE tbb.time_building_block_id = p_tc_bbid
AND tbb.object_version_number = p_tbb_ovn;
SELECT tbb.start_time,tbb.stop_time
FROM hxc_time_building_blocks tbb
WHERE tbb.time_building_block_id = p_tc_bbid
AND tbb.object_version_number = p_tc_ovnid;
select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
SELECT
SUM(NVL(tbb.measure,0) +
((( NVL(tbb.stop_time,sysdate) - NVL(tbb.start_time,sysdate))*24)))
FROM
hxc_time_building_blocks tbb
WHERE
tbb.time_building_block_id = p_tbb_id AND
tbb.object_version_number = p_tbb_ovn;
SELECT NVL( hours_worked, 0 )
FROM hxc_app_period_total_time_v
WHERE resource_id = p_resource_id
AND start_date BETWEEN p_period_start_time AND p_period_stop_time
AND stop_date BETWEEN p_period_start_time AND p_period_stop_time
AND application_period_id = p_application_period_id;
SELECT
htc.time_category_name
, tcc.time_category_comp_id
, tcc.time_category_id
, tcc.ref_time_category_id
, tcc.component_type_id
, tcc.flex_value_set_id
, tcc.value_id
, tcc.is_null
, tcc.equal_to
, tcc.type
, tcc.object_version_number
FROM hxc_time_categories htc
, hxc_time_category_comps tcc
WHERE tcc.time_category_id = htc.time_category_id
AND tcc.type = 'AN'
AND tcc.component_type_id = p_alias_value_id
ORDER BY htc.time_category_id;
IF ( p_action = 'DELETE' )
THEN
IF ( l_time_category_name IS NULL )
THEN
l_time_category_name := tc.time_category_name;
-- must be update
l_rec.time_category_comp_id := tc.time_category_comp_id;
update_time_category_comp_sql ( l_rec );
SELECT htc.time_category_name
FROM hxc_time_categories htc
, hxc_time_category_comps tcc
, hxc_alias_values av
, hxc_alias_definitions ad
WHERE
ad.alias_definition_id = p_alias_definition_id
AND
av.alias_definition_id = ad.alias_definition_id
AND
tcc.component_type_id = av.alias_value_id AND
tcc.type = 'AN'
AND
tcc.time_category_id = htc.time_category_id;
SELECT
tcc.time_category_comp_id
, tcc.time_category_id
, tcc.ref_time_category_id
, tcc.component_type_id
, tcc.flex_value_set_id
, tcc.value_id
, tcc.is_null
, tcc.equal_to
, tcc.type
, tcc.object_version_number
FROM hxc_time_category_comps tcc
, hxc_alias_values av
, hxc_alias_definitions ad
, hxc_alias_types hat
WHERE
hat.alias_type_id = p_alias_type_id
AND
ad.alias_type_id = hat.alias_type_id
AND
av.alias_definition_id = ad.alias_definition_id
AND
tcc.component_type_id = av.alias_value_id AND
tcc.type = 'AN';
hr_utility.trace('about to call update');
update_time_category_comp_sql ( l_rec );
g_tc_cache.delete;
g_tc_bb_ok_cache.delete;
PROCEDURE insert_tc_comps2(p_tc_id IN NUMBER,
p_tcc_id IN NUMBER,
p_type IN VARCHAR2,
p_value_id IN VARCHAR2,
p_component_type_id IN NUMBER)
IS
l_bbit_id NUMBER;
IS SELECT hmc.bld_blk_info_type_id,
hmc.segment,
bbit.bld_blk_info_type
FROM hxc_mapping_components hmc,
hxc_bld_blk_info_types bbit
WHERE hmc.mapping_component_id = p_comp_id
AND hmc.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
IS SELECT hmc.bld_blk_info_type_id,
hmc.segment,
bbit.bld_blk_info_type,
decode(hatc.component_type,'ATTRIBUTE1',hav.attribute1,
'ATTRIBUTE2',hav.attribute2,
'ATTRIBUTE1',hav.attribute1,
'ATTRIBUTE2',hav.attribute2,
'ATTRIBUTE3',hav.attribute3,
'ATTRIBUTE4',hav.attribute4,
'ATTRIBUTE5',hav.attribute5,
'ATTRIBUTE6',hav.attribute6,
'ATTRIBUTE7',hav.attribute7,
'ATTRIBUTE8',hav.attribute8,
'ATTRIBUTE9',hav.attribute9,
'ATTRIBUTE10',hav.attribute10,
'ATTRIBUTE11',hav.attribute11,
'ATTRIBUTE12',hav.attribute12,
'ATTRIBUTE13',hav.attribute13,
'ATTRIBUTE14',hav.attribute14,
'ATTRIBUTE15',hav.attribute15,
'ATTRIBUTE16',hav.attribute16,
'ATTRIBUTE17',hav.attribute17,
'ATTRIBUTE18',hav.attribute18,
'ATTRIBUTE19',hav.attribute19,
'ATTRIBUTE20',hav.attribute20,
'ATTRIBUTE21',hav.attribute21,
'ATTRIBUTE22',hav.attribute22,
'ATTRIBUTE23',hav.attribute23,
'ATTRIBUTE24',hav.attribute24,
'ATTRIBUTE25',hav.attribute25,
'ATTRIBUTE26',hav.attribute26,
'ATTRIBUTE27',hav.attribute27,
'ATTRIBUTE28',hav.attribute28,
'ATTRIBUTE29',hav.attribute29,
'ATTRIBUTE30',hav.attribute30 ) value
FROM hxc_alias_values hav,
hxc_alias_definitions had,
hxc_alias_type_components hatc,
hxc_mapping_components hmc,
hxc_bld_blk_info_types bbit
WHERE alias_value_id = p_comp_id
AND hav.alias_definition_id = had.alias_definition_id
AND had.alias_type_id = hatc.alias_type_id
AND hatc.mapping_component_id = hmc.mapping_component_id
AND hmc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
ORDER BY hmc.segment ;
'UPDATE hxc_time_category_comps2
SET COLUMN = :1,
bld_blk_info_type_id = :2,
attribute_category = :3
WHERE rowid = :4 ';
'UPDATE hxc_time_category_comps2
SET attribute_category = :1,
bld_blk_info_type_id = :2
WHERE rowid = :3';
DELETE FROM hxc_time_category_comps2
WHERE time_category_id = p_tc_id
AND time_category_comp_id = p_tcc_id;
INSERT INTO hxc_time_category_comps2
(time_category_id,
time_category_comp_id,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
attribute_category
)
VALUES ( p_tc_id,
p_tcc_id,
'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',
'NULL')
RETURNING rowid INto l_rowid;
END insert_tc_comps2;
PROCEDURE delete_tc_comps2(p_tcc_id IN NUMBER)
IS
BEGIN
DELETE FROM hxc_time_category_comps2
WHERE time_category_comp_id = p_tcc_id;
END delete_tc_comps2;