CREATE OR REPLACE FUNCTION GET_AV_QUERY( 
    p_av_name VARCHAR2, 
    p_depth   INT) 
    return varchar2 
AS 
  -- 
  v_sql         VARCHAR2(20000); 
  v_select_list VARCHAR2(2000); 
  v_meas_list   VARCHAR2(2000); 
  v_hier_list   VARCHAR2(2000); 
  v_where_list  VARCHAR2(2000); 
  v_order_by    VARCHAR2(2000); 
  v_hier_count  INT; 
  v_base_count  INT; 
  v_calc_count  INT; 
  v_i           INT; 
  -- 
  CURSOR c1 
  IS 
    SELECT a.analytic_view_name, 
      a.dimension_alias, 
      a.dimension_type, 
      b.hier_alias, 
      c.level_name, 
      c.order_num 
    FROM user_analytic_view_dimensions a, 
      user_analytic_view_hiers b, 
      user_analytic_view_levels c 
    WHERE a.dimension_alias  = b.dimension_alias 
    AND b.hier_alias         = c.hier_alias 
    AND b.is_default         ='Y' 
    AND a.analytic_view_name = upper(p_av_name) 
    AND b.analytic_view_name = upper(p_av_name) 
    AND c.analytic_view_name = upper(p_av_name); 
  -- 
  CURSOR c2 
  IS 
    SELECT measure_name 
    FROM user_analytic_view_base_meas 
    WHERE analytic_view_name = upper(p_av_name); 
  -- 
  CURSOR c3 
  IS 
    SELECT measure_name 
    FROM user_analytic_view_calc_meas 
    WHERE analytic_view_name = upper(p_av_name); 
  -- 
BEGIN 
  -- 
  v_select_list := ''; 
  v_hier_list   := ''; 
  -- 
  -- Get a count of the number of hierachies to be included in the query. 
  -- 
  v_hier_count := 0; 
  FOR v_c1 IN c1 
  LOOP 
    IF v_c1.order_num = p_depth THEN 
      v_hier_count   := v_hier_count + 1; 
    END IF; 
  END LOOP; 
  -- 
  -- Loop over hierarchies and build the select list, hierarchy list, where 
  -- clause and order by. 
  -- 
  v_i := 1; 
  -- 
  FOR v_c1 IN c1 
  LOOP 
    IF v_c1.order_num = p_depth THEN 
      -- 
      IF v_i < v_hier_count THEN 
        -- Not the last hierarchy, so add a comma. 
        v_select_list := v_select_list || chr(10) || '  ' || lower(v_c1.hier_alias) || '.member_name AS ' || v_c1.hier_alias || ','; 
        v_hier_list   := v_hier_list || chr(10) || '    ' || lower(v_c1.hier_alias) || ','; 
        v_order_by    := v_order_by || chr(10) || '  ' || lower(v_c1.hier_alias) || '.hier_order,'; 
      ELSE 
        -- The last hierarchy, so no comma for the hier list and order by. 
        v_select_list := v_select_list || chr(10) || '  ' || lower(v_c1.hier_alias) || '.member_name AS ' || v_c1.hier_alias || ','; 
        v_hier_list   := v_hier_list || chr(10) || '    ' || lower(v_c1.hier_alias); 
        v_order_by    := v_order_by || chr(10) || '  ' || lower(v_c1.hier_alias) || '.hier_order'; 
      END IF; 
      -- 
      -- 
      IF v_i = 1 THEN 
        -- First hierarchy does not include AND. 
        v_where_list := '  ' || lower(v_c1.hier_alias) || '.level_name = ' || '''' || v_c1.level_name || ''''; 
      ELSE 
        -- Alll but first hierachy includes AND 
        v_where_list := v_where_list || chr(10) ||'  AND ' || lower(v_c1.hier_alias) || '.level_name = ' || '''' || v_c1.level_name || ''''; 
      END IF; 
      -- 
      v_i := v_i + 1; 
      -- 
    END IF; 
  END LOOP; 
  -- 
  -- Get a count of base measures. 
  -- 
  v_base_count := 0; 
  FOR v_c2 IN c2 
  LOOP 
    v_base_count := v_base_count + 1; 
  END LOOP; 
  -- 
  -- Get a count of calc measures. 
  -- 
  v_calc_count := 0; 
  FOR v_c3 IN c3 
  LOOP 
    v_calc_count := v_calc_count + 1; 
  END LOOP; 
  -- 
  -- Build the list of base measures. 
  -- 
  v_i := 1; 
  FOR v_c2 IN c2 
  LOOP 
    IF v_i < v_base_count OR v_calc_count >= 1 THEN 
      -- Add comma at the end. 
      v_meas_list := v_meas_list || chr(10) || '  ' || lower(v_c2.measure_name) || ','; 
    ELSE 
      -- Do not add comma at the end. 
      v_meas_list := v_meas_list || chr(10) || '  ' || lower(v_c2.measure_name); 
    END IF; 
    v_i := v_i + 1; 
  END LOOP; 
  -- 
  -- Add calc measures to the measure list 
  -- 
  v_i := 1; 
  FOR v_c3 IN c3 
  LOOP 
    IF v_i < v_calc_count THEN 
      -- Add comma at the end. 
      v_meas_list := v_meas_list || chr(10) || '  ' || lower(v_c3.measure_name) || ','; 
    ELSE 
      -- Do not add comma at the end. 
      v_meas_list := v_meas_list || chr(10) || '  ' || lower(v_c3.measure_name); 
    END IF; 
    v_i := v_i + 1; 
  END LOOP; 
  -- 
  -- Assemble the SELECT statement. 
  -- 
  v_sql := 'SELECT' || v_select_list || v_meas_list || chr(10) || 'FROM' || chr(10) || '  ' || lower(p_av_name) || ' HIERARCHIES (' || v_hier_list || ')' || chr(10) || 'WHERE' || chr(10) || v_where_list || chr(10) || 'ORDER BY' || v_order_by; 
  -- 
--  dbms_output.put_line(v_sql || ';   
--'); 
return v_sql; 
  -- 
END GET_AV_QUERY;

SELECT get_av_query('SH_SALES_HISTORY_AV',0) FROM dual

SELECT 
  sh_times_calendar_hier.member_name AS SH_TIMES_CALENDAR_HIER, 
  sh_products_hier.member_name AS SH_PRODUCTS_HIER, 
  sh_customers_hier.member_name AS SH_CUSTOMERS_HIER, 
  sh_channels_hier.member_name AS SH_CHANNELS_HIER, 
  sh_promotions_hier.member_name AS SH_PROMOTIONS_HIER, 
  amount_sold, 
  quantity_sold 
FROM 
  sh_sales_history_av HIERARCHIES ( 
    sh_times_calendar_hier, 
    sh_products_hier, 
    sh_customers_hier, 
    sh_channels_hier, 
    sh_promotions_hier) 
WHERE 
  sh_times_calendar_hier.level_name = 'CALENDAR_YEAR' 
  AND sh_products_hier.level_name = 'CATEGORY' 
  AND sh_customers_hier.level_name = 'REGION' 
  AND sh_channels_hier.level_name = 'CHANNEL_CLASS' 
  AND sh_promotions_hier.level_name = 'CATEGORY' 
ORDER BY 
  sh_times_calendar_hier.hier_order, 
  sh_products_hier.hier_order, 
  sh_customers_hier.hier_order, 
  sh_channels_hier.hier_order, 
  sh_promotions_hier.hier_order