APPS.HXC_MAPPING_UTILITIES SQL Statements

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

Line: 10

SELECT
	bb.time_building_block_id bb_id
,	bb.object_Version_number bb_ovn
,	bb.scope
,	bb.type
,	bb.start_time
,	bb.stop_time
,	bb.measure
,	bb.date_to
,       bb.comment_text
FROM
	hxc_time_building_blocks bb
WHERE
	bb.object_version_number = (
	SELECT	MAX(bb1.object_version_number)
	FROM	hxc_time_building_blocks bb1
	WHERE	bb1.time_building_block_id = bb.time_building_block_id )
AND
((
p_start_date BETWEEN
DECODE ( bb.type, 'RANGE', bb.start_time,
	( hxc_mapping_utilities.get_day_date
        ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
AND
DECODE ( bb.type, 'RANGE', bb.stop_time,
	( hxc_mapping_utilities.get_day_date
	( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
OR
p_end_date BETWEEN
DECODE ( bb.type, 'RANGE', bb.start_time,
	( hxc_mapping_utilities.get_day_date
        ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
AND
DECODE ( bb.type, 'RANGE', bb.stop_time,
	( hxc_mapping_utilities.get_day_date
	( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) ) )
OR (
DECODE ( bb.type, 'RANGE', bb.start_time,
	( hxc_mapping_utilities.get_day_date
        ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
BETWEEN p_start_date AND p_end_date
OR
DECODE ( bb.type, 'RANGE', bb.stop_time,
	( hxc_mapping_utilities.get_day_date
	( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
BETWEEN p_start_date AND p_end_date ))
START WITH bb.time_building_block_id = p_timecard_bb_id
AND	   bb.object_version_number  = p_timecard_ovn
CONNECT BY PRIOR bb.time_building_block_id = bb.parent_building_block_id
AND        PRIOR bb.object_version_number  = bb.parent_building_block_ovn;

Line: 74

SELECT
	mpc.bld_blk_info_type_id
,	UPPER(mpc.field_name)
,	mpc.segment
,	bbit.bld_blk_info_type context
,	bbitu.building_block_category category
FROM
	hxc_bld_blk_info_type_usages bbitu
,	hxc_bld_blk_info_types bbit
,	hxc_mapping_components mpc
,	hxc_mapping_comp_usages mcu
,	hxc_mappings map
WHERE	map.mapping_id		= p_mapping_id
AND
	mcu.mapping_id		= map.mapping_id
AND
	mpc.mapping_component_id= mcu.mapping_component_id
AND
	bbit.bld_blk_info_type_id	= mpc.bld_blk_info_type_id
AND
	bbitu.bld_blk_info_type_id	= bbit.bld_blk_info_type_id
ORDER BY 1;

Line: 149

SELECT	start_time
FROM	hxc_time_building_blocks bb
WHERE	bb.time_building_block_id = p_bb_id
AND	bb.object_version_number  = p_bb_ovn;

Line: 155

SELECT	stop_time
FROM	hxc_time_building_blocks bb
WHERE	bb.time_building_block_id = p_bb_id
AND	bb.object_version_number  = p_bb_ovn;

Line: 688

SELECT
	ta.time_attribute_id
,	bb.time_building_block_id
,       bbit.bld_blk_info_type
,	ta.attribute_category
,	ta.attribute1
,	ta.attribute2
,	ta.attribute3
,	ta.attribute4
,	ta.attribute5
,	ta.attribute6
,	ta.attribute7
,	ta.attribute8
,	ta.attribute9
,	ta.attribute10
,	ta.attribute11
,	ta.attribute12
,	ta.attribute13
,	ta.attribute14
,	ta.attribute15
,	ta.attribute16
,	ta.attribute17
,	ta.attribute18
,	ta.attribute19
,	ta.attribute20
,	ta.attribute21
,	ta.attribute22
,	ta.attribute23
,	ta.attribute24
,	ta.attribute25
,	ta.attribute26
,	ta.attribute27
,	ta.attribute28
,	ta.attribute29
,	ta.attribute30
,	ta.bld_blk_info_type_id
,	ta.object_version_number
,	'X' dummy1
,	'X' dummy2
,       'X' process
FROM
	hxc_time_building_blocks bb
,	hxc_time_attributes ta
,	hxc_time_attribute_usages tau
,       hxc_bld_blk_info_types bbit
WHERE
	bb.time_building_block_id	= p_bb_id AND
	bb.object_version_number	= p_bb_ovn
AND
	tau.time_building_block_id(+)   = bb.time_building_block_id AND
	tau.time_building_block_ovn(+)  = bb.object_version_number
AND
	ta.time_attribute_id(+)	    = tau.time_attribute_id AND
	ta.object_version_number(+) = 1 -- gaz
AND
        bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
ORDER BY
	ta.bld_blk_info_type_id;

Line: 773

SELECT
	ta.time_attribute_id
,	bb.time_building_block_id
,       bbit.bld_blk_info_type
,	ta.attribute_category
,	ta.attribute1
,	ta.attribute2
,	ta.attribute3
,	ta.attribute4
,	ta.attribute5
,	ta.attribute6
,	ta.attribute7
,	ta.attribute8
,	ta.attribute9
,	ta.attribute10
,	ta.attribute11
,	ta.attribute12
,	ta.attribute13
,	ta.attribute14
,	ta.attribute15
,	ta.attribute16
,	ta.attribute17
,	ta.attribute18
,	ta.attribute19
,	ta.attribute20
,	ta.attribute21
,	ta.attribute22
,	ta.attribute23
,	ta.attribute24
,	ta.attribute25
,	ta.attribute26
,	ta.attribute27
,	ta.attribute28
,	ta.attribute29
,	ta.attribute30
,	ta.bld_blk_info_type_id
,	ta.object_version_number
,	'X'
,	'X'
,       'X'
FROM
	hxc_time_attributes ta
,	hxc_time_attribute_usages tau
,	hxc_time_building_blocks bb
,       hxc_bld_blk_info_types bbit
WHERE
	bb.time_building_block_id	= p_bb_id AND
	bb.object_version_number	= p_decr_ovn AND
	bb.approval_status		= p_status
AND
	tau.time_building_block_id(+)   = bb.time_building_block_id AND
	tau.time_building_block_ovn(+)  = bb.object_Version_number
AND
	ta.time_attribute_id(+)		= tau.time_attribute_id AND
	ta.object_version_number(+)     = 1 -- gaz
AND
        bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
ORDER BY
	ta.bld_blk_info_type_id;

Line: 1041

			p_time_attributes.DELETE(l_del_index);

Line: 1067

			p_time_attributes.DELETE(l_del_index);

Line: 1077

		p_time_attributes.DELETE(l_del_index);

Line: 1091

p_time_attributes.DELETE;

Line: 1241

t_old_attributes.delete;

Line: 1242

t_new_attributes.delete;

Line: 1416

t_old_attributes.delete;

Line: 1417

t_new_attributes.delete;

Line: 1496

SELECT
	bb.time_building_block_id bb_id
,	bb.object_Version_number bb_ovn
,	bb.scope
,	bb.type
,	bb.start_time
,	bb.stop_time
,	bb.measure
,       bb.date_to
,       bb.comment_text
FROM
	hxc_time_building_blocks bb
WHERE
	bb.time_building_block_id = p_bb_id   AND
        bb.object_version_number  = p_bb_ovn  AND
	bb.approval_status        = p_status;

Line: 1643

	-- if we found a prior del bld blk check to see if it was deleted
	-- if it was deleted or we did not find one then do nothing

	IF ( csr_get_bld_blk%FOUND )
	THEN
		IF ( r_old_del_bld_blks.date_to <> hr_general.end_of_time )
		THEN
			if g_debug then
				hr_utility.set_location('Processing '||l_proc, 110);

Line: 1921

  select hmc.segment
  from hxc_mapping_components hmc,
       hxc_bld_blk_info_types hit
  where hit.descriptive_flexfield_name = p_descriptive_flexfield_name
  and   hit.bld_blk_info_type_id       = hmc.bld_blk_info_type_id
  and   hit.bld_blk_info_type          = p_bld_blk_info_type
  and   hmc.field_name                 = p_field_name;

Line: 1985

  select distinct hmc.segment, hit.bld_blk_info_type
  from hxc_mapping_components  hmc
  ,    hxc_mapping_comp_usages hmu
  ,    hxc_mappings            hmp
  ,    hxc_retrieval_processes hrp
  ,    hxc_bld_blk_info_types  hit
  where hmu.mapping_id           = hmp.mapping_id
  and   hmp.mapping_id           = hrp.mapping_id
  and   hmc.bld_blk_info_type_id = hit.bld_blk_info_type_id
  and   hrp.retrieval_process_id = p_process_id
  and   hmc.mapping_component_id = hmu.mapping_component_id
  and   hmc.field_name           = p_field_name;

Line: 1999

  select distinct hmc.segment, hit.bld_blk_info_type
  from hxc_mapping_components  hmc
  ,    hxc_mapping_comp_usages hmu
  ,    hxc_mappings            hmp
  ,    hxc_deposit_processes   hdp
  ,    hxc_bld_blk_info_types  hit
  where hmu.mapping_id           = hmp.mapping_id
  and   hmp.mapping_id           = hdp.mapping_id
  and   hmc.bld_blk_info_type_id = hit.bld_blk_info_type_id
  and   hdp.deposit_process_id   = p_process_id
  and   hmc.mapping_component_id = hmu.mapping_component_id
  and   hmc.field_name           = p_field_name;

Line: 2069

SELECT	segment
,	bld_blk_info_type_id
FROM	hxc_mapping_components_v
WHERE	bld_blk_info_type	= p_bld_blk_info_type
AND	field_name		= p_field_name;

Line: 2186

SELECT  'Y'
FROM    fnd_product_installations pi
WHERE   pi.application_id = 809
AND     pi.status in ( 'S', 'I' );

Line: 2193

SELECT  ret.retrieval_process_id
FROM 	hxc_retrieval_processes ret
WHERE	ret.name = p_retrieval_process_name;

Line: 2348

	SELECT  ''Y''
	FROM    dual
	WHERE EXISTS (
		SELECT  1
		FROM    hxc_time_building_blocks tbb
		WHERE   tbb.scope = :p_scope AND
			tbb.object_version_number = (
			   SELECT MAX ( tbb1.object_version_number )
			   FROM   hxc_time_building_blocks tbb1
			   WHERE  tbb1.time_building_block_id = tbb.time_building_block_id )';

Line: 2364

	SELECT  ''Y''
	FROM    dual
	WHERE EXISTS (
		SELECT  1
		FROM    hxc_latest_details tbb
		WHERE 1=1';

Line: 2391

							select  /*+ LEADING(ta1) INDEX(ta1)
							            INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
							tau1.time_building_block_id,tau1.time_building_block_ovn from
							hxc_time_attribute_usages tau1,
							hxc_time_attributes ta1
							where   tau1.time_building_block_id      = tbb.time_building_block_id
							AND	tau1.time_building_block_ovn     = tbb.object_version_number
							AND     tau1.time_attribute_id           = ta1.time_attribute_id
							AND     ta1.bld_blk_info_type_id         = '||l_bld_block_info_id_outer||
							' AND     ta1.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;

Line: 2407

							select   /*+ LEADING(ta1) INDEX(ta1)
							             INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
							tau1.time_building_block_id,tau1.time_building_block_ovn from
							hxc_time_attribute_usages tau1,
							hxc_time_attributes ta1
								where   tau1.time_building_block_id      = tbb.time_building_block_id
								AND	tau1.time_building_block_ovn     = tbb.object_version_number
								AND     tau1.time_attribute_id           = ta1.time_attribute_id
								AND     ta1.bld_blk_info_type_id         =  '||l_bld_block_info_id_outer||
								' AND     ta1.'||l_segment_outer||'		is null ';

Line: 2443

				t_consolidated_info.delete(l_index_inner);

Line: 2473

		   select ''Y''
		   from  hxc_time_building_blocks detbb,
                         hxc_time_building_blocks daybb,
                         hxc_timecard_summary     time_status
		   where tbb.time_building_Block_id      = detbb.time_building_block_id
                     and tbb.object_version_number       = detbb.object_version_number
                     and detbb.parent_building_block_id  = daybb.time_building_block_id
		     and detbb.parent_building_block_ovn = daybb.object_version_number
                     and time_status.timecard_id         = daybb.parent_building_block_id
		     and detbb.date_to                   = hr_general.end_of_time
                     and time_status.approval_status    IN '||l_status_list||' ) ';

Line: 2499

                   select 1 from hxc_time_building_blocks daybb1
		   where tbb.parent_building_block_id = daybb1.time_building_block_id
		     and tbb.parent_building_block_ovn = daybb1.object_version_number
		     and daybb1.stop_time >= :p_end_date) ';

Line: 2627

	SELECT  ''Y''
	FROM    dual
	WHERE EXISTS (
		SELECT  1
		FROM    hxc_time_building_blocks tbb
		WHERE   tbb.scope = :p_scope AND
			tbb.object_version_number = (
			   SELECT MAX ( tbb1.object_version_number )
			   FROM   hxc_time_building_blocks tbb1
			   WHERE  tbb1.time_building_block_id = tbb.time_building_block_id )
		AND NOT EXISTS (
			  SELECT  1
			  FROM    hxc_transactions tx
			  ,       hxc_transaction_details txd
			  WHERE   tx.transaction_process_id = :p_ret_id
			  AND     tx.status = ''SUCCESS''
			  AND     txd.transaction_id = tx.transaction_id
			  AND     txd.status = ''SUCCESS''
			  AND     txd.time_building_block_id = tbb.time_building_block_id
			  AND     txd.time_building_block_ovn = tbb.object_version_number ) ';

Line: 2653

	SELECT  ''Y''
	FROM    dual
	WHERE EXISTS (
		SELECT  1
		FROM  hxc_latest_details tbb
		WHERE NOT EXISTS (
			  SELECT  1
			  FROM    hxc_transactions tx
			  ,       hxc_transaction_details txd
			  WHERE   tx.transaction_process_id = :p_ret_id
			  AND     tx.status = ''SUCCESS''
			  AND     txd.transaction_id = tx.transaction_id
			  AND     txd.status = ''SUCCESS''
			  AND     txd.time_building_block_id = tbb.time_building_block_id
			  AND     txd.time_building_block_ovn = tbb.object_version_number ) ';

Line: 2825

SELECT  'Y'
FROM    fnd_product_installations pi
WHERE   pi.application_id = 809
AND     pi.status in ( 'S', 'I' );

Line: 2842

t_consolidated_info.delete;

Line: 2960

	select sum(ta.'||l_segment1||')
	from   	hxc_time_attributes ta,
        	hxc_time_attribute_usages tau,
		hxc_time_building_blocks tbb
	where
    	        tbb.scope = :p_scope
    	AND     tbb.resource_id = :p_resource_id
	AND     tbb.object_version_number = (
			   SELECT MAX ( tbb1.object_version_number )
			   FROM   hxc_time_building_blocks tbb1
			   WHERE  tbb1.time_building_block_id = tbb.time_building_block_id )
	AND	tau.time_building_block_id  = tbb.time_building_block_id
	AND	tau.time_building_block_ovn = tbb.object_version_number
	AND 	tau.time_attribute_id       = ta.time_attribute_id
	AND     tbb.date_to                 = hr_general.end_of_time
	And     ta.bld_blk_info_type_id     = :l_bld_blk_info_type_id';

Line: 2993

				l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
											hxc_time_attributes ta2
										where   tau2.time_building_block_id      = tbb.time_building_block_id
										AND 	tau2.time_building_block_ovn     = tbb.object_version_number
										AND     tau2.time_attribute_id           = ta2.time_attribute_id
										AND     ta2.bld_blk_info_type_id         = '||l_bld_block_info_id_outer||
										' AND     ta2.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;

Line: 3005

				l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
											hxc_time_attributes ta2
										where   tau2.time_building_block_id      = tbb.time_building_block_id
										AND 	tau2.time_building_block_ovn     = tbb.object_version_number
										AND     tau2.time_attribute_id           = ta2.time_attribute_id
										AND     ta2.bld_blk_info_type_id         = '||l_bld_block_info_id_outer||
										' AND     ta2.'||l_segment_outer||'		is null ';

Line: 3042

					t_consolidated_info.delete(l_index_inner);

Line: 3079

		   select ''Y''
		   from hxc_time_building_blocks daybb,
			hxc_time_building_blocks timebb,
                        hxc_timecard_summary time_status
		   where tbb.parent_building_block_id = daybb.time_building_block_id
		     and tbb.parent_building_block_ovn = daybb.object_version_number
		     and daybb.parent_building_block_id = timebb.time_building_block_id
		     and daybb.parent_building_block_ovn = timebb.object_version_number
                     and time_status.timecard_id  = timebb.time_building_block_id
                     and time_status.approval_status IN '||l_status_list||' ) ';