APPS.HXC_ALIAS_UTILITY SQL Statements

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

Line: 48

select  hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
	hatc.component_type,hatc.component_name,bldu.building_block_category,
	reference_object
from hxc_mapping_components hmc,
     hxc_alias_types hat,
     hxc_alias_type_components hatc,
     hxc_alias_definitions had,
     hxc_bld_blk_info_type_usages bldu,
     hxc_bld_blk_info_types bld
where had.alias_definition_id = 1
and   had.alias_type_id = hat.alias_type_id
and   hatc.alias_type_id = hat.alias_type_id
and   hmc.mapping_component_id = hatc.mapping_component_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
order by hatc.component_name;

Line: 77

g_alias_def_item.delete;

Line: 78

g_alias_val_att_to_match.delete;

Line: 79

g_alias_def_att_to_match.delete;

Line: 80

g_alias_def_att_rec.delete;

Line: 81

g_alias_def_val_att_rec.delete;

Line: 82

g_alias_definition_info.delete;

Line: 83

g_alias_att_info.delete;

Line: 84

g_alias_apps_tab_info.delete;

Line: 734

   		p_match_to_delete	 OUT NOCOPY BOOLEAN)
   		IS

CURSOR csr_alias_value_attribute
IS
select 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
from hxc_alias_values
where alias_value_id = p_alias_value_id;

Line: 995

  p_match_to_delete := TRUE;

Line: 1088

select  hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
	hatc.component_type,hatc.component_name,bldu.building_block_category,
	reference_object
from hxc_mapping_components hmc,
     hxc_alias_types hat,
     hxc_alias_type_components hatc,
     hxc_alias_definitions had,
     hxc_bld_blk_info_type_usages bldu,
     hxc_bld_blk_info_types bld
where had.alias_definition_id = p_alias_definition_id
and   had.alias_type_id = hat.alias_type_id
and   hatc.alias_type_id = hat.alias_type_id
and   hmc.mapping_component_id = hatc.mapping_component_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
order by hatc.component_name;

Line: 1108

l_match_to_delete	BOOLEAN;

Line: 1169

           		p_match_to_delete		=> l_match_to_delete);

Line: 1172

           IF (l_match_to_delete = TRUE) THEN
             p_alias_val_att_to_match.delete(l_index);

Line: 1208

   		p_match_to_delete		=> l_match_to_delete);

Line: 1211

   IF (l_match_to_delete = TRUE) THEN
     p_alias_val_att_to_match.delete(l_index);

Line: 1244

select  hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
	hatc.component_type,hatc.component_name,bldu.building_block_category,
	reference_object
from hxc_mapping_components hmc,
     hxc_alias_types hat,
     hxc_alias_type_components hatc,
     hxc_alias_definitions had,
     hxc_bld_blk_info_type_usages bldu,
     hxc_bld_blk_info_types bld
where had.alias_definition_id = p_alias_definition_id
and   had.alias_type_id = hat.alias_type_id
and   hatc.alias_type_id = hat.alias_type_id
and   hmc.mapping_component_id = hatc.mapping_component_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
and   bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
order by hatc.component_name;

Line: 1264

l_match_to_delete	BOOLEAN;

Line: 1326

           		p_match_to_delete		=> l_match_to_delete);

Line: 1363

   		p_match_to_delete		=> l_match_to_delete);

Line: 1605

select layout_component_id, QUALIFIER_ATTRIBUTE24,
       QUALIFIER_ATTRIBUTE27,QUALIFIER_ATTRIBUTE26,LABEL
from  hxc_layout_components_v
where layout_id = p_layout_id;

Line: 1712

 g_alias_def_item.delete;

Line: 1713

 g_comp_label.delete;

Line: 1950

      g_alias_def_item.delete;

Line: 1951

      g_comp_label.delete;

Line: 2568

      g_alias_def_item.delete;

Line: 2842

select alias_type,reference_object,prompt
from hxc_alias_types hat
   , hxc_alias_definitions_tl hadtl
   , hxc_alias_definitions had
where hat.alias_type_id = had.alias_type_id
and   had.alias_definition_id = p_alias_definition_id
and   hadtl.language = userenv('LANG')
and   hadtl.alias_definition_id = p_alias_definition_id;

Line: 2893

     select 	alias_definition_id
     into	l_alias_definition_id
     from 	hxc_alias_values
     where	alias_value_id = p_alias_value_id;

Line: 2925

    select 'select to_char('||value_column_name||') display_value, '||
           replace(id_column_name, ',',  ' || ''ALIAS_SEPARATOR'' || ')||' id_value '||
    	 ' from '||application_table_name
    into l_stmt
    from   fnd_flex_validation_tables t
    where  t.flex_value_set_id = p_vset_id;

Line: 2932

    select additional_where_clause
    into   l_where
    from   fnd_flex_validation_tables t
    where  t.flex_value_set_id = p_vset_id;

Line: 2943

    l_stmt := 'select flex_value_meaning display_value, '||
         '  flex_value_id  id_value  '||
    	 ' from fnd_flex_values_vl t'||
         ' where t.flex_value_set_id = '||p_vset_id||
         ' and t.enabled_flag=''Y''' ||
	 ' and nvl(t.start_date_active,hr_general.start_of_time) <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
	 ' and nvl(t.end_date_active,hr_general.end_of_time) >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ';

Line: 2987

 (x_select 			IN	VARCHAR2,
  p_block_name			IN	VARCHAR2)
 RETURN VARCHAR2
    IS

l_x_select	VARCHAR2(2000);

Line: 2996

l_x_select:=x_select;

Line: 2998

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_1'')', ':'||p_block_name||'ATTR_ID_1')
into l_x_select
from dual;

Line: 3002

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_2'')', ':'||p_block_name||'ATTR_ID_2')
into l_x_select
from dual;

Line: 3006

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_3'')', ':'||p_block_name||'ATTR_ID_3')
into l_x_select
from dual;

Line: 3010

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_4'')', ':'||p_block_name||'ATTR_ID_4')
into l_x_select
from dual;

Line: 3014

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_5'')', ':'||p_block_name||'ATTR_ID_5')
into l_x_select
from dual;

Line: 3018

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_6'')', ':'||p_block_name||'ATTR_ID_6')
into l_x_select
from dual;

Line: 3022

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_7'')', ':'||p_block_name||'ATTR_ID_7')
into l_x_select
from dual;

Line: 3026

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_8'')', ':'||p_block_name||'ATTR_ID_8')
into l_x_select
from dual;

Line: 3030

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_9'')', ':'||p_block_name||'ATTR_ID_9')
into l_x_select
from dual;

Line: 3034

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_10'')', ':'||p_block_name||'ATTR_ID_10')
into l_x_select
from dual;

Line: 3038

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_11'')', ':'||p_block_name||'ATTR_ID_11')
into l_x_select
from dual;

Line: 3042

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_12'')', ':'||p_block_name||'ATTR_ID_12')
into l_x_select
from dual;

Line: 3046

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_13'')', ':'||p_block_name||'ATTR_ID_13')
into l_x_select
from dual;

Line: 3050

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_14'')', ':'||p_block_name||'ATTR_ID_14')
into l_x_select
from dual;

Line: 3054

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_15'')', ':'||p_block_name||'ATTR_ID_15')
into l_x_select
from dual;

Line: 3058

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_16'')', ':'||p_block_name||'ATTR_ID_16')
into l_x_select
from dual;

Line: 3062

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_17'')', ':'||p_block_name||'ATTR_ID_17')
into l_x_select
from dual;

Line: 3066

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_18'')', ':'||p_block_name||'ATTR_ID_18')
into l_x_select
from dual;

Line: 3070

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_19'')', ':'||p_block_name||'ATTR_ID_19')
into l_x_select
from dual;

Line: 3074

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_20'')', ':'||p_block_name||'ATTR_ID_20')
into l_x_select
from dual;

Line: 3078

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID'')', ':'||'TIMECARD_INFO.'||'RESOURCE_ID')
into l_x_select
from dual;

Line: 3082

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')', ':'||'HXCTKSTA.TIMEKEEPER_ID')
into l_x_select
from dual;

Line: 3086

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_START_DATE'')', ':'||'HXCTKSTA.'||'FRDT')
into l_x_select
from dual;

Line: 3090

select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_END_DATE'')', ':'||'HXCTKSTA.'||'TODT')
into l_x_select
from dual;

Line: 3095

return l_x_select;

Line: 3112

select id_column_type,instr(id_column_name,'to_char')
into l_id_col_type, l_to_char_in_col_id
from   fnd_flex_validation_tables t
where  t.flex_value_set_id = p_vset_id;

Line: 3145

PROCEDURE get_vset_table_type_select
 (p_alias_definition_id		IN	NUMBER,
  x_select 		 OUT NOCOPY VARCHAR2,
  p_id_type		 OUT NOCOPY VARCHAR2) IS

l_alias_type 		hxc_alias_types.alias_type%TYPE;

Line: 3154

l_select		VARCHAR2(200);

Line: 3174

  x_select  := make_stmt (l_reference_object,l_alias_type);

Line: 3184

END get_vset_table_type_select;

Line: 3192

PROCEDURE get_vset_indep_type_select
 (p_alias_definition_id		IN	NUMBER,
  x_select 		 OUT NOCOPY VARCHAR2) IS

l_alias_type 		hxc_alias_types.alias_type%TYPE;

Line: 3200

l_select		VARCHAR2(200);

Line: 3220

  x_select := make_stmt (l_reference_object,l_alias_type);

Line: 3226

END get_vset_indep_type_select;

Line: 3266

select format_type,maximum_size,minimum_value,maximum_value,number_precision
from fnd_flex_value_sets
where validation_type = 'N'
and flex_value_set_id = p_value_set_id;

Line: 3274

SELECT format_type,
       maximum_size,
      DECODE(format_type,'N',fnd_number.canonical_to_number(minimum_value),
              NULL),
      DECODE(format_type,'N',fnd_number.canonical_to_number(maximum_value),
              NULL),
       number_precision
  FROM fnd_flex_value_sets
 WHERE validation_type = 'N'
   AND flex_value_set_id = p_value_set_id;

Line: 3290

l_select		VARCHAR2(200);

Line: 3328

PROCEDURE get_otl_an_context_type_select
 (p_alias_definition_id		IN	NUMBER,
  p_timekeeper_person_type	IN	VARCHAR2 DEFAULT NULL,
  x_select 		 OUT NOCOPY VARCHAR2) IS

l_alias_type 		hxc_alias_types.alias_type%TYPE;

Line: 3337

l_select		VARCHAR2(200);

Line: 3361

      x_select := 'select distinct(havtl.alias_value_name) display_value, havtl.alias_value_id id_value '||
		  'from hxc_alias_values_tl havtl,'||
		  'hxc_alias_values hav,'||
		  'hxc_alias_definitions had,'||
		  'pa_online_expenditure_types_v pa,'||
		  'PAY_ELEMENT_TYPES_F_TL  ELEMENTTL, '||
		  'PAY_ELEMENT_TYPES_F  ELEMENT, '||
		  'PAY_ELEMENT_CLASSIFICATIONS_TL CLASSIFICATIONTL, '||
		  'PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION, '||
		  'BEN_BENEFIT_CLASSIFICATIONS BENEFIT, '||
		  'PAY_ELEMENT_LINKS_F  LINK, '||
		  'PER_ALL_ASSIGNMENTS_F  ASGT, '||
		  'PER_PERIODS_OF_SERVICE  SERVICE_PERIOD '||
		  'where hav.alias_definition_id = had.alias_definition_id '||
		  'and havtl.alias_value_id = hav.alias_value_id '||
		  'and havtl.language = userenv(''LANG'') '||
		  'and hav.attribute2 = pa.expenditure_type '||
		  'and hav.attribute3 = pa.system_linkage_function '||
  		  'and hav.attribute1 = ELEMENT.element_type_id '||
  		  'and hav.enabled_flag=''Y'' '||
  		  'and ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID '||
  		  'AND ELEMENTTL.LANGUAGE = USERENV(''LANG'') '||
  		  'AND CLASSIFICATION.CLASSIFICATION_ID = CLASSIFICATIONTL.CLASSIFICATION_ID (+) '||
  		  'AND DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',CLASSIFICATIONTL.LANGUAGE) = '||
      		  'DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',USERENV(''LANG'')) '||
  		  'AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID '||
  		  'AND  ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID '||
  		  'AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+) '||
  		  'AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID '||
  		  'AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID '||
  		  'AND ELEMENT.INDIRECT_ONLY_FLAG = ''N'' '||
  		  'AND UPPER (ELEMENT.ELEMENT_NAME) <> ''VERTEX'' '||
  		  'AND not exists '||
      		  '(select 1 '||
         		  'from HR_ORGANIZATION_INFORMATION HOI, '||
              		  'PAY_LEGISLATION_RULES PLR '||
        		  'WHERE  plr.rule_type in '||
             		  '(''ADVANCE'',''ADVANCE_INDICATOR'',''ADV_DEDUCTION'', '||
              		  '''PAY_ADVANCE_INDICATOR'',''ADV_CLEARUP'',''DEFER_PAY'') '||
          		  'AND   plr.rule_mode = to_char(element.element_type_id) '||
          		  'AND  plr.legislation_code = hoi.org_information9 '||
          		  'AND   HOI.ORGANIZATION_ID =  ASGT.ORGANIZATION_ID '||
      		  ') '||
		  'AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = ''N'' '||
 		  'AND ELEMENT.ADJUSTMENT_ONLY_FLAG = ''N'' '||
 		  'AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID) '||
      		  'OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''Y'' AND ASGT.PAYROLL_ID IS NOT NULL) '||
  		  'OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''N'')) '||
 		  'AND  (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL) '||
 		  'AND  (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL) '||
 		  'AND  (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL) '||
 		  'AND  (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL) '||
 		  'AND  (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL) '||
 		  'AND  (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL) '||
 		  'AND  (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR '||
 		  'LINK.EMPLOYMENT_CATEGORY IS NULL) '||
 		  'AND  (ELEMENT.PROCESSING_TYPE = ''R'' OR ASGT.PAYROLL_ID IS NOT NULL) '||
 		  'and asgt.person_id = nvl(FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID''),FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')) '||
		  'and '||p_alias_definition_id ||' = had.alias_definition_id '||

		  'AND ELEMENT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
		  'AND ELEMENT.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
		  'AND date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
		  'AND nvl(date_to,hr_general.end_of_time) >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
		  'AND ASGT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
		  'AND ASGT.EFFECTIVE_END_DATE >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
                  'AND LINK.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
                  'AND LINK.EFFECTIVE_END_DATE >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
                  'AND  (LINK.PEOPLE_GROUP_ID IS NULL '||
		  '   OR EXISTS ( '||
		  '    SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE '||
		  '    WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID '||
		  '    AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID '||
		  '    AND USAGE.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
		  '    AND USAGE.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE''))) '||
		  '  AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL '||
		  '   OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL '||
		  '   AND FND_PROFILE.VALUE(''OTL_TK_START_DATE'') <= DECODE(ELEMENT.POST_TERMINATION_RULE, '||
		  '      ''L'', SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE, '||
		  '      ''F'', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE, '||
		  '       hr_general.end_of_time), '||
		  '      SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))) ';

Line: 3446

     x_select := 'select   distinct(havt.alias_value_name)        Display_Value,'||
                 '         hav.alias_value_id            id_value '||
		 'from     hxc_alias_values              hav, '||
		 '         hxc_alias_values_tl          havt, '||
		 '         hxc_alias_definitions         had, '||
		 '         PAY_ELEMENT_TYPES_F_TL  ELEMENTTL, '||
		 '         PAY_ELEMENT_TYPES_F  ELEMENT, '||
		 '         PAY_ELEMENT_CLASSIFICATIONS_TL CLASSIFICATIONTL, '||
		 '         PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION, '||
		 '         BEN_BENEFIT_CLASSIFICATIONS BENEFIT, '||
		 '         PAY_ELEMENT_LINKS_F  LINK, '||
		 '         PER_ALL_ASSIGNMENTS_F  ASGT, '||
		 '         PER_PERIODS_OF_SERVICE  SERVICE_PERIOD '||
		 'where asgt.person_id = nvl(FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID''),FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')) '||
		 '  and hav.attribute_category=''PAYROLL_ELEMENTS'' '||
		 '  and hav.attribute1 = ELEMENT.element_type_id '||
		 '  and hav.enabled_flag=''Y'' '||
		 '  and had.alias_definition_id = hav.alias_definition_id '||
		 '  and had.alias_definition_id = '||p_alias_definition_id||
		 '  AND ELEMENT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
		 '  AND ELEMENT.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
		 '  and havt.language = USERENV(''LANG'') '||
		 '  and havt.alias_value_id = hav.alias_value_id '||
		 '  and hav.date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'')  '||
		 '  and nvl(hav.date_to,hr_general.end_of_time) >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'')  '||
		 '  and ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID '||
		 '  AND ELEMENTTL.LANGUAGE = USERENV(''LANG'') '||
		 '  AND CLASSIFICATION.CLASSIFICATION_ID = CLASSIFICATIONTL.CLASSIFICATION_ID (+) '||
		 '  AND DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',CLASSIFICATIONTL.LANGUAGE) = '||
		 '      DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',USERENV(''LANG'')) '||
		 '  AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID '||
		 '  AND  ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID '||
		 '  AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+) '||
		 '  AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID '||
		 '  AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID '||
		 '  AND ASGT.EFFECTIVE_START_DATE  <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'')  '||
		 '  AND ASGT.EFFECTIVE_END_DATE  >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'')  '||
		 '  AND LINK.EFFECTIVE_START_DATE  <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'')  '||
		 '  AND LINK.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'')  '||
		 '  AND ELEMENT.INDIRECT_ONLY_FLAG = ''N'' '||
		 '  AND UPPER (ELEMENT.ELEMENT_NAME) <> ''VERTEX'' '||
		 '  AND not exists '||
		 '      (select 1 '||
		 '         from HR_ORGANIZATION_INFORMATION HOI, '||
		 '              PAY_LEGISLATION_RULES PLR '||
		 '        WHERE  plr.rule_type in '||
		 '             (''ADVANCE'',''ADVANCE_INDICATOR'',''ADV_DEDUCTION'', '||
		 '              ''PAY_ADVANCE_INDICATOR'',''ADV_CLEARUP'',''DEFER_PAY'') '||
		 '          AND   plr.rule_mode = to_char(element.element_type_id) '||
		 '          AND  plr.legislation_code = hoi.org_information9 '||
		 '          AND   HOI.ORGANIZATION_ID =  ASGT.ORGANIZATION_ID '||
		 '      ) '||
		 'AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = ''N'' '||
		 ' AND ELEMENT.ADJUSTMENT_ONLY_FLAG = ''N'' '||
		 ' AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID) '||
		 '      OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''Y'' AND ASGT.PAYROLL_ID IS NOT NULL) '||
		 '  OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''N'')) '||
		 ' AND  (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL) '||
		 ' AND  (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL) '||
		 ' AND  (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL) '||
		 ' AND  (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL) '||
		 ' AND  (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL) '||
		 ' AND  (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL) '||
		 ' AND  (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR '||
		 ' LINK.EMPLOYMENT_CATEGORY IS NULL) '||
		 ' AND  (LINK.PEOPLE_GROUP_ID IS NULL '||
		 '  OR EXISTS ( '||
		 '   SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE '||
		 '   WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID '||
		 '   AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID '||
		 '   AND (USAGE.EFFECTIVE_START_DATE  <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'')  '||
		 '    AND USAGE.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ))) '||
		 ' AND  (ELEMENT.PROCESSING_TYPE = ''R'' OR ASGT.PAYROLL_ID IS NOT NULL) '||
		 ' AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL '||
		 '  OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL '||
		 '  AND FND_PROFILE.VALUE(''OTL_TK_START_DATE'')   <= DECODE(ELEMENT.POST_TERMINATION_RULE, '||
		 '     ''L'', NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE, hr_general.end_of_time),'||
		 '     ''F'', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE, '||
		 '      hr_general.end_of_time), '||
		 '     SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))) ';

Line: 3528

     x_select := 'select   havt.alias_value_name         Display_Value,'||
                 '         hav.alias_value_id            id_value '||
		 'from     hxc_alias_values              hav, '||
		 '         hxc_alias_values_tl          havt, '||
		 '         hxc_alias_definitions         had '||
		 'where hav.enabled_flag=''Y'' '||
		 '  and had.alias_definition_id = hav.alias_definition_id '||
		 '  and had.alias_definition_id = '||p_alias_definition_id||
		 '  and havt.language = USERENV(''LANG'') '||
		 '  and havt.alias_value_id = hav.alias_value_id '||
		 '  and hav.date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'')  '||
		 '  and nvl(hav.date_to,hr_general.end_of_time) >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ';

Line: 3548

	--hr_utility.trace('x_select'||x_select);

Line: 3551

END get_otl_an_context_type_select;

Line: 3614

FUNCTION query_invoice(p_select IN  VARCHAR2)
	RETURN VARCHAR2 IS

TYPE cur_typ IS REF CURSOR;

Line: 3627

	--hr_utility.trace('p_select '||p_select);

Line: 3630

    OPEN c FOR p_select;

Line: 3655

  select application_table_name
  into l_apps_table
  from   fnd_flex_validation_tables t,
  	 hxc_alias_types h,
  	 hxc_alias_definitions hd
  where  t.flex_value_set_id = h.reference_object
  and    hd.alias_definition_id = p_alias_definition_id
  and    hd.alias_type_id	= h.alias_type_id;

Line: 3692

 select attribute3
 into l_sfl
 from hxc_alias_values
 where alias_value_id = p_alias_value_id;

Line: 3717

     				       ,p_att_to_delete		 OUT NOCOPY BOOLEAN) IS

l_alias_val_att_to_match	t_alias_val_att_to_match;

Line: 3722

l_select 			VARCHAR2(200) := NULL;

Line: 3737

    IS SELECT format_type
         FROM fnd_flex_value_sets
        WHERE flex_value_set_id = p_reference_object ;

Line: 3748

p_att_to_delete := FALSE;

Line: 3896

              l_select := 'select '||l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_NAME||
                          ' from '||l_apps_table||
                          ' where '||l_where_clause;

Line: 3900

	      l_value := query_invoice(p_select => l_select);

Line: 3944

         p_att_to_delete := TRUE;

Line: 3953

         p_att_to_delete := TRUE;

Line: 3978

select
 a.time_attribute_id
,au.time_building_block_id
,null 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
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_time_attribute_usages au,
hxc_time_attributes a,
hxc_time_building_blocks htbb
where 	a.time_attribute_id         = au.time_attribute_id
and     au.time_building_block_id   = htbb.time_building_block_id
and     au.time_building_block_ovn  = htbb.object_version_number
and     htbb.scope		    = 'TIMECARD'
and     htbb.time_building_block_id     = p_timecard_id
and     htbb.object_version_number      = p_timecard_ovn
and     htbb.resource_id		= p_resource_id
and     a.attribute_category = 'LAYOUT';

Line: 4061

  g_layout_attribute.delete;

Line: 4145

   l_alias_val_att_to_match.delete;

Line: 4318

l_index_to_delete 	NUMBER;

Line: 4320

l_to_delete BOOLEAN;

Line: 4329

    l_to_delete := TRUE;

Line: 4334

       l_to_delete := FALSE;

Line: 4336

       l_to_delete := FALSE;

Line: 4338

       l_to_delete := FALSE;

Line: 4340

       l_to_delete := FALSE;

Line: 4342

       l_to_delete := FALSE;

Line: 4344

       l_to_delete := FALSE;

Line: 4346

       l_to_delete := FALSE;

Line: 4348

       l_to_delete := FALSE;

Line: 4350

       l_to_delete := FALSE;

Line: 4352

       l_to_delete := FALSE;

Line: 4354

       l_to_delete := FALSE;

Line: 4356

       l_to_delete := FALSE;

Line: 4358

       l_to_delete := FALSE;

Line: 4360

       l_to_delete := FALSE;

Line: 4362

       l_to_delete := FALSE;

Line: 4364

       l_to_delete := FALSE;

Line: 4366

       l_to_delete := FALSE;

Line: 4368

       l_to_delete := FALSE;

Line: 4370

       l_to_delete := FALSE;

Line: 4372

       l_to_delete := FALSE;

Line: 4374

       l_to_delete := FALSE;

Line: 4376

       l_to_delete := FALSE;

Line: 4378

       l_to_delete := FALSE;

Line: 4380

       l_to_delete := FALSE;

Line: 4382

       l_to_delete := FALSE;

Line: 4384

       l_to_delete := FALSE;

Line: 4386

       l_to_delete := FALSE;

Line: 4388

       l_to_delete := FALSE;

Line: 4390

       l_to_delete := FALSE;

Line: 4392

       l_to_delete := FALSE;

Line: 4395

    IF l_to_delete = FALSE AND p_attributes(l_index).ATTRIBUTE_CATEGORY is null THEN

       l_to_delete := TRUE;

Line: 4399

    ELSIF l_to_delete AND p_attributes(l_index).BLD_BLK_INFO_TYPE like 'Dummy%'
       AND p_attributes(l_index).BLD_BLK_INFO_TYPE <> 'Dummy Paexpitdff Context' THEN
       l_to_delete := FALSE;

Line: 4404

    IF l_to_delete THEN
      l_index_to_delete := l_index;

Line: 4411

    IF l_to_delete THEN
      p_attributes.delete(l_index_to_delete);

Line: 5248

l_pref_table.DELETE;

Line: 5270

        l_pref_value.delete;

Line: 5356

		select reference_object
  		into l_alias_type
		from hxc_alias_definitions had,hxc_alias_types hat
		where hat.alias_type_id =had.alias_type_id
		and alias_definition_id =
  			l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID;

Line: 5417

  SELECT bld_blk_info_type_id
    FROM hxc_bld_blk_info_types
   WHERE bld_blk_info_type = p_type;

Line: 5589

select
 timecard.TIME_BUILDING_BLOCK_ID 	tc_TIME_BUILDING_BLOCK_ID
,timecard.START_TIME 			tc_START_TIME
,timecard.STOP_TIME 			tc_STOP_TIME
,timecard.PARENT_BUILDING_BLOCK_ID 	tc_PARENT_BUILDING_BLOCK_ID
,timecard.PARENT_BUILDING_BLOCK_OVN 	tc_PARENT_BUILDING_BLOCK_OVN
,timecard.SCOPE 			tc_SCOPE
,timecard.OBJECT_VERSION_NUMBER		tc_OBJECT_VERSION_NUMBER
,day.TIME_BUILDING_BLOCK_ID 		day_TIME_BUILDING_BLOCK_ID
,day.START_TIME 			day_START_TIME
,day.STOP_TIME 				day_STOP_TIME
,day.PARENT_BUILDING_BLOCK_ID 		day_PARENT_BUILDING_BLOCK_ID
,day.PARENT_BUILDING_BLOCK_OVN 		day_PARENT_BUILDING_BLOCK_OVN
,day.SCOPE 				day_SCOPE
,day.OBJECT_VERSION_NUMBER 		day_OBJECT_VERSION_NUMBER
,detail.TIME_BUILDING_BLOCK_ID 		detail_TIME_BUILDING_BLOCK_ID
,detail.START_TIME 			detail_START_TIME
,detail.STOP_TIME 			detail_STOP_TIME
,detail.PARENT_BUILDING_BLOCK_ID 	detail_PARENT_BB_ID
,detail.PARENT_BUILDING_BLOCK_OVN 	detail_PARENT_BB_OVN
,detail.SCOPE 				detail_SCOPE
,detail.OBJECT_VERSION_NUMBER 		detail_OBJECT_VERSION_NUMBER
FROM hxc_time_building_blocks timecard,
     hxc_time_building_blocks day,
     hxc_time_building_blocks detail
where detail.time_building_block_id = p_detail_bb_id
and   detail.object_version_number  = p_detail_bb_ovn
and   detail.resource_id = p_resource_id
and   detail.scope = 'DETAIL'
and   day.scope = 'DAY'
and   day.resource_id = p_resource_id
and   detail.parent_building_block_id = day.time_building_block_id
and   detail.parent_building_block_ovn = day.object_version_number
and   timecard.scope = 'TIMECARD'
and   timecard.resource_id = p_resource_id
and   day.parent_building_block_id = timecard.time_building_block_id
and   day.parent_building_block_ovn = timecard.object_version_number;

Line: 5629

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
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a,
hxc_time_building_blocks htbb
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 = htbb.time_building_block_id
and  au.time_building_block_ovn = htbb.object_version_number
and  htbb.scope			= 'TIMECARD'
and  htbb.time_building_block_id     = p_timecard_id
and  htbb.object_version_number      = p_timecard_ovn
and  htbb.resource_id		     = p_resource_id;

Line: 5686

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
,'N' NEW
,'N' CHANGED
,'N' PROCESS
,au.time_building_block_ovn BUILDING_BLOCK_OVN
from hxc_bld_blk_info_types bbit,
hxc_time_attribute_usages au,
hxc_time_attributes a,
hxc_time_building_blocks htbb
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 = htbb.time_building_block_id
and  au.time_building_block_ovn = htbb.object_version_number
and  htbb.scope			= 'DETAIL'
and  htbb.time_building_block_id     = p_detail_bb_id
and  htbb.object_version_number      = p_detail_bb_ovn
and  htbb.resource_id		     = p_resource_id;