Search for:
Jan 6 2023
Configuration – Extract Table based value set information

Use below query to extract table based value set information from backend tables:

SELECT ffvs.flex_value_set_name
      ,ffvs.description vale_set_desc
      ,ffvs.validation_type
      ,ffvs.format_type
      ,ffvs.maximum_size
      ,ffvs.number_precision
      ,ffvs.alphanumeric_allowed_flag
      ,ffvs.uppercase_only_flag
      ,ffvt.value_column_name	  
      ,ffvt.value_column_type
      ,ffvt.value_column_size
      ,ffvt.meaning_column_name
      ,ffvt.meaning_column_type
      ,ffvt.meaning_column_size
      ,ffvt.id_column_name
      ,ffvt.id_column_type
      ,ffvt.id_column_size
      ,ffvt.enabled_column_name
      ,ffvt.start_date_column_name
      ,ffvt.end_date_column_name
      ,ffvt.summary_column_name
      ,ffvt.application_table_name
      ,ffvt.additional_where_clause
      ,ffvt.additional_quickpick_columns
  FROM fnd_flex_value_sets ffvs
      ,fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name LIKE 'XX%'
Mandeep Gupta 1 Comments
Jan 6 2023
HDL – Script to DELETE positions data

In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'Position' ||'|'||
       hapf.Position_Id||'|'||
       to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
       to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
  from hr_all_positions_f hapf
)
Mandeep Gupta 0 Comments