In this session we are going to see how to fetch Oracle Cloud HCM User Defined Table data using SQL. Please replace the table name and
column name based on your requirements.
SQL Text:
with udt_table_vw1 as ( select fuc.user_column_name ,fur.row_name exact ,fuci.value from ff_user_tables_vl fut ,ff_user_columns_vl fuc ,ff_user_rows_vl fur ,ff_user_column_instances_f fuci where 1 = 1 and fuc.user_table_id = fut.user_table_id and fur.user_table_id = fut.user_table_id and fuci.user_column_id = fuc.user_column_id and fuci.user_row_id = fur.user_row_id and fut.user_table_name in ('TABLE_NAME') and :p_eff_dt between fuci.effective_start_date and fuci.effective_end_date ) ,udt_table_vw2 as ( select * from udt_table_vw1 pivot (max(value) for user_column_name in ( 'COLUMN1' COLUMN1 ,'COLUMN2' COLUMN2 )) ) select * from udt_table_vw2
with udt_table_vw1 as ( select fuc.user_column_name ,fur.row_name exact ,fuci.value from ff_user_tables_vl fut ,ff_user_columns_vl fuc ,ff_user_rows_vl fur ,ff_user_column_instances_f fuci where 1 = 1 and fuc.user_table_id = fut.user_table_id and fur.user_table_id = fut.user_table_id and fuci.user_column_id = fuc.user_column_id and fuci.user_row_id = fur.user_row_id and fut.user_table_name in ('TABLE_NAME') and :p_eff_dt between fuci.effective_start_date and fuci.effective_end_date ) ,udt_table_vw2 as ( select * from udt_table_vw1 pivot (max(value) for user_column_name in ( 'COLUMN1' COLUMN1 ,'COLUMN2' COLUMN2 )) ) select * from udt_table_vw2
Comments
Post a Comment