DBA Data[Home] [Help]

APPS.HXC_TIME_CATEGORY_UTILS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 89

SELECT  operator
FROM    hxc_time_categories
WHERE   time_category_id = p_time_category_id;
Line: 95

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;
Line: 115

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;
Line: 179

SELECT time_category_comp_sql_id tcc_sql_id
FROM   hxc_time_category_comp_sql
WHERE  time_category_comp_id = p_tcc_id;
Line: 184

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 );
Line: 217

SELECT	sql_string
FROM    hxc_time_category_comp_sql
WHERE   time_category_comp_id = p_tcc_id;
Line: 229

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;
Line: 234

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;
Line: 239

l_vs_last_update_date DATE;
Line: 252

OPEN  get_value_set_last_update_date ( p_vs_comp_rec.flex_value_set_id );
Line: 253

FETCH get_value_set_last_update_date INTO l_vs_last_update_date;
Line: 254

CLOSE get_value_set_last_update_date;
Line: 258

hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
Line: 259

hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
Line: 262

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;
Line: 283

	-- check table last update date

	OPEN  get_vset_tab_last_update_date ( p_vs_comp_rec.flex_value_set_id );
Line: 286

	FETCH get_vset_tab_last_update_date INTO l_vs_last_update_date;
Line: 287

	CLOSE get_vset_tab_last_update_date;
Line: 291

	hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
Line: 292

	hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
Line: 295

	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;
Line: 366

                        update_time_category_comp_sql ( p_rec        => l_rec );
Line: 370

			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;
Line: 588

			g_tc_cache.DELETE;
Line: 589

			g_tc_bb_ok_cache.DELETE;
Line: 839

l_sql   CLOB := 'select distinct ta.bb_id from hxc_tmp_atts ta where ';			-- Bug 15977687
Line: 869

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' );
Line: 942

  l_sql   := 'select distinct ta.bb_id from hxc_tmp_atts ta where '||l_parse_time_sql;
Line: 1011

		t_bb_id.DELETE;
Line: 1047

SELECT additional_where_clause
FROM   fnd_flex_validation_tables
WHERE  flex_value_set_id = p_flex_value_set_id;
Line: 1665

l_comps_t.DELETE;
Line: 1790

SELECT 'x'
FROM   dual
WHERE EXISTS ( select 'y'
from hxc_tmp_blks );
Line: 1848

	-- we did error here but on delete there are never
	-- going to be any blocks

ELSE

	CLOSE csr_chk_bld_blks_not_empty;
Line: 1913

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) );
Line: 1988

t_bb_id.delete;
Line: 1989

t_ta_id.delete;
Line: 1990

t_bld_blk_info_type_id.delete;
Line: 1991

t_attribute_category.delete;
Line: 1992

t_attribute1.delete;
Line: 1993

t_attribute2.delete;
Line: 1994

t_attribute3.delete;
Line: 1995

t_attribute4.delete;
Line: 1996

t_attribute5.delete;
Line: 1997

t_attribute6.delete;
Line: 1998

t_attribute7.delete;
Line: 1999

t_attribute8.delete;
Line: 2000

t_attribute9.delete;
Line: 2001

t_attribute10.delete;
Line: 2002

t_attribute11.delete;
Line: 2003

t_attribute12.delete;
Line: 2004

t_attribute13.delete;
Line: 2005

t_attribute14.delete;
Line: 2006

t_attribute15.delete;
Line: 2007

t_attribute16.delete;
Line: 2008

t_attribute17.delete;
Line: 2009

t_attribute18.delete;
Line: 2010

t_attribute19.delete;
Line: 2011

t_attribute20.delete;
Line: 2012

t_attribute21.delete;
Line: 2013

t_attribute22.delete;
Line: 2014

t_attribute23.delete;
Line: 2015

t_attribute24.delete;
Line: 2016

t_attribute25.delete;
Line: 2017

t_attribute26.delete;
Line: 2018

t_attribute27.delete;
Line: 2019

t_attribute28.delete;
Line: 2020

t_attribute29.delete;
Line: 2021

t_attribute30.delete;
Line: 2044

SELECT *
from hxc_tmp_blks;
Line: 2048

SELECT *
from hxc_tmp_atts;
Line: 2147

l_trunc_blks VARCHAR2(30) := 'delete from hxc_tmp_blks';
Line: 2148

l_trunc_atts VARCHAR2(30) := 'delete from hxc_tmp_atts';
Line: 2193

	-- 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);
Line: 2293

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) );
Line: 2312

t_bb_id.delete;
Line: 2313

t_measure.delete;
Line: 2314

t_type.delete;
Line: 2315

t_start_time.delete;
Line: 2316

t_stop_time.delete;
Line: 2317

t_comment_text.delete;
Line: 2318

t_scope.delete;
Line: 2365

l_select VARCHAR2(75) := '
SELECT DISTINCT ta.bb_id
FROM  hxc_tmp_atts ta
WHERE  ';
Line: 2371

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  ';
Line: 2380

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  ';
Line: 2479

          l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_ok_string)||' ) ';
Line: 2480

	  p_tc_bb_ok_tab.DELETE;
Line: 2483

	  l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_not_ok_string)||' ) ';
Line: 2486

	  l_sql := l_select || l_parse_time_sql;
Line: 2505

		p_tc_bb_ok_tab.DELETE;
Line: 2537

		p_tc_bb_ok_tab.DELETE;
Line: 2751

	t_bb_id.DELETE;
Line: 2798

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;
Line: 2914

	l_vs_sql := ' SELECT ' || r_valueset.table_info.id_column_name || ' FROM ' ||
                          r_valueset.table_info.table_name || ' ' ||
                          l_where_clause || ' ) ';
Line: 2962

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 ( ';
Line: 2973

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 ';
Line: 3032

l_select := REPLACE(l_select,'FUNCTION','TRUNC');
Line: 3033

l_select := REPLACE(l_select,'ZERO','0.1');
Line: 3034

l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3036

l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
Line: 3037

l_select_null := REPLACE(l_select_null,'ZERO','0.1');
Line: 3038

l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3044

l_select := REPLACE(l_select,'FUNCTION','TRUNC');
Line: 3045

l_select := REPLACE(l_select,'ZERO','0');
Line: 3046

l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3048

l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
Line: 3049

l_select_null := REPLACE(l_select_null,'ZERO','0');
Line: 3050

l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3056

l_select := REPLACE(l_select,'FUNCTION','ROUND');
Line: 3057

l_select := REPLACE(l_select,'ZERO','0');
Line: 3058

l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3060

l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
Line: 3061

l_select_null := REPLACE(l_select_null,'ZERO','0');
Line: 3062

l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3067

l_select := REPLACE(l_select,'FUNCTION','ROUND');
Line: 3068

l_select := REPLACE(l_select,'ZERO','0');
Line: 3069

l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3071

l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
Line: 3072

l_select_null := REPLACE(l_select_null,'ZERO','0');
Line: 3073

l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
Line: 3081

  hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
Line: 3082

  hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
Line: 3090

	--l_sql := l_select || p_tc_bb_ok_string || ' ) ';
Line: 3091

	l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
Line: 3135

	l_sql := l_select_null;
Line: 3169

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 ( ';
Line: 3193

	--l_sql := l_select || p_tc_bb_ok_string || ' ) ';
Line: 3195

	l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
Line: 3265

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 ( ';
Line: 3288

	l_sql := l_select || p_tc_bb_ok_string || ' ) ';
Line: 3349

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;
Line: 3378

SELECT bb_id,
       scope
FROM   hxc_tmp_blks;
Line: 3384

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;
Line: 3737

	l_vs_comp_tab.DELETE;
Line: 3738

	l_an_comp_tab.DELETE;
Line: 3739

	l_tc_comp_tab.DELETE;
Line: 3768

		l_vs_comp_tab(l_vs_ind).last_update_date      := l_time_category_comps.last_update_date;
Line: 3870

		-- 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');
Line: 4142

PROCEDURE insert_time_category_comp_sql ( p_rec  hxc_tcc_shd.g_rec_type ) IS

l_proc	VARCHAR2(72);
Line: 4154

	l_proc := g_package||'insert_time_category_comp_sql';
Line: 4157

	hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
Line: 4190

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 );
Line: 4203

END insert_time_category_comp_sql;
Line: 4206

PROCEDURE update_time_category_comp_sql ( p_rec            hxc_tcc_shd.g_rec_type ) IS

l_proc	VARCHAR2(72);
Line: 4220

	l_proc := g_package||'update_time_category_comp_sql';
Line: 4266

		UPDATE hxc_time_category_comp_sql
		SET    sql_string  = l_sql
		WHERE  time_category_comp_id = p_rec.time_category_comp_id;
Line: 4273

		-- 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;
Line: 4287

		insert_time_category_comp_sql ( p_rec );
Line: 4301

END update_time_category_comp_sql;
Line: 4304

PROCEDURE delete_time_category_comp_sql ( p_rec  hxc_tcc_shd.g_rec_type ) IS

l_proc	VARCHAR2(72);
Line: 4313

	l_proc := g_package||'delete_time_category_comp_sql';
Line: 4323

	DELETE from hxc_time_category_comp_sql
	WHERE  time_category_comp_sql_id = tcc_sql.tcc_sql_id;
Line: 4329

END delete_time_category_comp_sql;
Line: 4359

   l_select_clause VARCHAR2(2000);
Line: 4402

      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;
Line: 4439

        l_select_clause := 'SELECT '||l_valueset_r.table_info.
                                                    meaning_column_name||' ';
Line: 4442

        l_select_clause := 'SELECT '||l_valueset_r.table_info.
                                                      value_column_name||' ';
Line: 4446

     l_sql_text_id := l_select_clause||l_from_where;
Line: 4451

     l_sql_text_id   := l_select_clause||l_from_where ||'WHERE '||l_valueset_r.table_info.id_column_name||' = ';
Line: 4455

     l_sql_text_id   := l_select_clause||l_from_where ||' and '||l_valueset_r.table_info.id_column_name||' = ';
Line: 4470

         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 = ';
Line: 4667

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');
Line: 4744

SELECT htc.time_category_id
FROM   hxc_time_categories htc
WHERE  htc.time_category_name = p_time_category_name;
Line: 4779

g_tc_bb_ok_tab.delete;
Line: 4847

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;
Line: 4940

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;
Line: 4960

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;
Line: 4970

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;
Line: 4976

select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
Line: 5170

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;
Line: 5255

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;
Line: 5373

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;
Line: 5401

	IF ( p_action = 'DELETE' )
	THEN

		IF ( l_time_category_name IS NULL )
		THEN

			l_time_category_name := tc.time_category_name;
Line: 5417

		-- must be update

		l_rec.time_category_comp_id	:= tc.time_category_comp_id;
Line: 5430

		update_time_category_comp_sql ( l_rec );
Line: 5453

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;
Line: 5511

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';
Line: 5561

		hr_utility.trace('about to call update');
Line: 5564

	update_time_category_comp_sql ( l_rec );
Line: 5579

    g_tc_cache.delete;
Line: 5580

    g_tc_bb_ok_cache.delete;
Line: 5593

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;
Line: 5610

      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;
Line: 5620

       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 ;
Line: 5668

'UPDATE hxc_time_category_comps2
    SET COLUMN = :1,
        bld_blk_info_type_id = :2,
        attribute_category = :3
  WHERE rowid = :4 ';
Line: 5676

'UPDATE hxc_time_category_comps2
    SET attribute_category = :1,
        bld_blk_info_type_id = :2
   WHERE rowid = :3';
Line: 5688

    DELETE FROM hxc_time_category_comps2
          WHERE time_category_id = p_tc_id
            AND time_category_comp_id = p_tcc_id;
Line: 5708

             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;
Line: 5850

END insert_tc_comps2;
Line: 5854

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;
Line: 5861

END delete_tc_comps2;