APPS.HXC_ALIAS_UTILITY SQL Statements

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

Line: 13

g_alias_def_item.delete;

Line: 14

g_alias_val_att_to_match.delete;

Line: 15

g_alias_def_att_to_match.delete;

Line: 16

g_alias_def_att_rec.delete;

Line: 17

g_alias_def_val_att_rec.delete;

Line: 18

g_alias_definition_info.delete;

Line: 19

g_alias_att_info.delete;

Line: 20

g_alias_apps_tab_info.delete;

Line: 670

   		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: 786

  p_match_to_delete := TRUE;

Line: 879

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: 899

l_match_to_delete	BOOLEAN;

Line: 948

   		p_match_to_delete		=> l_match_to_delete);

Line: 951

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

Line: 982

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: 1002

l_match_to_delete	BOOLEAN;

Line: 1051

   		p_match_to_delete		=> l_match_to_delete);

Line: 1289

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

Line: 1396

 g_alias_def_item.delete;

Line: 1397

 g_comp_label.delete;

Line: 1634

      g_alias_def_item.delete;

Line: 1635

      g_comp_label.delete;

Line: 2252

      g_alias_def_item.delete;

Line: 2526

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: 2577

     select 	alias_definition_id
     into	l_alias_definition_id
     from 	hxc_alias_values
     where	alias_value_id = p_alias_value_id;

Line: 2609

    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: 2616

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

Line: 2627

    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: 2671

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

l_x_select	VARCHAR2(2000);

Line: 2680

l_x_select:=x_select;

Line: 2682

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: 2686

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: 2690

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: 2694

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: 2698

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: 2702

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: 2706

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: 2710

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: 2714

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: 2718

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: 2722

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: 2726

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: 2730

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: 2734

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: 2738

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: 2742

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: 2746

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: 2750

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: 2754

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: 2758

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: 2762

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

Line: 2766

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

Line: 2770

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

Line: 2774

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

Line: 2779

return l_x_select;

Line: 2796

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: 2829

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: 2838

l_select		VARCHAR2(200);

Line: 2858

  x_select  := make_stmt (l_reference_object,l_alias_type);

Line: 2868

END get_vset_table_type_select;

Line: 2876

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: 2884

l_select		VARCHAR2(200);

Line: 2904

  x_select := make_stmt (l_reference_object,l_alias_type);

Line: 2910

END get_vset_indep_type_select;

Line: 2950

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: 2958

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: 2974

l_select		VARCHAR2(200);

Line: 3012

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: 3021

l_select		VARCHAR2(200);

Line: 3045

      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: 3130

     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: 3212

     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: 3232

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

Line: 3235

END get_otl_an_context_type_select;

Line: 3298

FUNCTION query_invoice(p_select IN  VARCHAR2)
	RETURN VARCHAR2 IS

TYPE cur_typ IS REF CURSOR;

Line: 3311

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

Line: 3314

    OPEN c FOR p_select;

Line: 3339

  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: 3376

 select attribute3
 into l_sfl
 from hxc_alias_values
 where alias_value_id = p_alias_value_id;

Line: 3401

     				       ,p_att_to_delete		 OUT NOCOPY BOOLEAN) IS

l_alias_val_att_to_match	t_alias_val_att_to_match;

Line: 3406

l_select 			VARCHAR2(200) := NULL;

Line: 3421

    IS SELECT format_type
         FROM fnd_flex_value_sets
        WHERE flex_value_set_id = p_reference_object ;

Line: 3432

p_att_to_delete := FALSE;

Line: 3580

              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: 3584

	      l_value := query_invoice(p_select => l_select);

Line: 3628

         p_att_to_delete := TRUE;

Line: 3637

         p_att_to_delete := TRUE;

Line: 3662

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: 3745

  g_layout_attribute.delete;

Line: 3829

   l_alias_val_att_to_match.delete;

Line: 4002

l_index_to_delete 	NUMBER;

Line: 4004

l_to_delete BOOLEAN;

Line: 4013

    l_to_delete := TRUE;

Line: 4018

       l_to_delete := FALSE;

Line: 4020

       l_to_delete := FALSE;

Line: 4022

       l_to_delete := FALSE;

Line: 4024

       l_to_delete := FALSE;

Line: 4026

       l_to_delete := FALSE;

Line: 4028

       l_to_delete := FALSE;

Line: 4030

       l_to_delete := FALSE;

Line: 4032

       l_to_delete := FALSE;

Line: 4034

       l_to_delete := FALSE;

Line: 4036

       l_to_delete := FALSE;

Line: 4038

       l_to_delete := FALSE;

Line: 4040

       l_to_delete := FALSE;

Line: 4042

       l_to_delete := FALSE;

Line: 4044

       l_to_delete := FALSE;

Line: 4046

       l_to_delete := FALSE;

Line: 4048

       l_to_delete := FALSE;

Line: 4050

       l_to_delete := FALSE;

Line: 4052

       l_to_delete := FALSE;

Line: 4054

       l_to_delete := FALSE;

Line: 4056

       l_to_delete := FALSE;

Line: 4058

       l_to_delete := FALSE;

Line: 4060

       l_to_delete := FALSE;

Line: 4062

       l_to_delete := FALSE;

Line: 4064

       l_to_delete := FALSE;

Line: 4066

       l_to_delete := FALSE;

Line: 4068

       l_to_delete := FALSE;

Line: 4070

       l_to_delete := FALSE;

Line: 4072

       l_to_delete := FALSE;

Line: 4074

       l_to_delete := FALSE;

Line: 4076

       l_to_delete := FALSE;

Line: 4079

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

       l_to_delete := TRUE;

Line: 4083

    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: 4088

    IF l_to_delete THEN
      l_index_to_delete := l_index;

Line: 4095

    IF l_to_delete THEN
      p_attributes.delete(l_index_to_delete);

Line: 4932

l_pref_table.DELETE;

Line: 4954

        l_pref_value.delete;

Line: 5040

		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: 5101

  SELECT bld_blk_info_type_id
    FROM hxc_bld_blk_info_types
   WHERE bld_blk_info_type = p_type;

Line: 5273

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: 5313

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: 5370

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;