Get UDT Information from SQL

                                            Get UDT(USER DEFINED TABLE) Information from SQL

 

UDT Table:

SELECT

 fut.user_table_name

,fut.base_user_table_name

,fut.range_or_match

,fut.user_key_units

,fut.user_table_id p_user_table_id

,ldg.name ldg_name

,'UserDefinedTable' metadata

FROM

 ff_user_tables_vl fut

,per_legislative_data_groups_vl ldg

WHERE 1=1

--AND fut.legislative_data_group_id = ldg.legislative_data_group_id

AND fut.user_table_id = :P_UDT

 

UDT Column:

SELECT

 fuc.user_column_name

,fuc.base_user_column_name

,fuc.data_type

,fuc.formula_id

,'UserDefinedTableColumn' metadata

FROM

 ff_user_columns_vl fuc

WHERE 1=1

AND fuc.user_table_id = :P_USER_TABLE_ID

 

 

 

UDT Rows:

SELECT

 fur.row_name

,fur.display_sequence

,TO_CHAR(fur.effective_start_date,'YYYY/MM/DD') row_effective_start_date

,TO_CHAR(fur.effective_end_date,'YYYY/MM/DD') row_effective_end_date

-- ,fur.row_high_range

-- ,fur.row_low_range_or_name

 

,DECODE(fut.range_or_match,'M','',fur.row_high_range)              row_high_range

,DECODE(fut.range_or_match,'M',fur.row_name,fur.row_low_range_or_name)              row_low_range_or_name

 

,'UserDefinedTableRow' metadata

FROM

ff_user_tables_vl fut

,ff_user_rows_vl fur

WHERE 1=1

AND fur.user_table_id = :P_USER_TABLE_ID

AND fut.user_table_id = fur.user_table_id

 

Column Instance:

SELECT

'UserDefinedTableColumnInstance' metadata

,fut.user_table_name

,fut.base_user_table_name

,fut.range_or_match

,fut.user_key_units

,display_sequence

,fur.row_name

,TO_CHAR(fur.effective_start_date,'YYYY/MM/DD') row_effective_start_date

,TO_CHAR(fur.effective_end_date,'YYYY/MM/DD') row_effective_end_date

,fur.row_high_range

,fur.row_low_range_or_name

,fuc.user_column_name

,fuc.base_user_column_name

,fuc.data_type

,fuc.formula_id

,fuci.value udt_value

,TO_CHAR(fuci.effective_start_date,'YYYY/MM/DD') col_inst_eff_start_date

,TO_CHAR(fuci.effective_end_date,'YYYY/MM/DD') col_inst_eff_end_date

FROM

 fusion.ff_user_tables_vl fut

,fusion.ff_user_columns_vl fuc

,fusion.ff_user_rows_vl fur

,fusion.ff_user_column_instances_f fuci

WHERE 1=1

--AND (fut.legislative_data_group_id = :P_LDG OR :P_LDG  IS NULL)

AND fut.user_table_id = :P_USER_TABLE_ID

AND fut.user_table_id = fuc.user_table_id

AND fut.user_table_id = fur.user_table_id

AND TRUNC(SYSDATE) BETWEEN fur.effective_start_date AND fur.effective_end_date

AND fuc.user_column_id = fuci.user_column_id

AND fur.user_row_id = fuci.user_row_id

AND TRUNC(SYSDATE) BETWEEN fuci.effective_start_date AND fuci.effective_end_date

 

Parameter(P_USER_TABLE_ID) LOV SQL for above SQL:

SELECT

user_table_name

,user_table_id

FROM

ff_user_tables_vl

 

UDT HDL Format Report:

 https://drive.google.com/file/d/1gnkepEqUgAQj657U0K20Owl-l_Ui4BZV/view?usp=sharing 

It may helpful for you. please visit the blog for more information and share your ideas.

Thanks

Sainath