Demantra- Query to get Worksheet info

Query to get Worksheet info


SELECT *
FROM
  (SELECT DISTINCT q.query_name Worksheet_Name ,
    gt.table_label "Aggrigation Selected levels" ,
    g.GORDER Aggregation_Position ,
    DECODE(q.tf_type_id,1,'Specify as Fixed Dates' ,2,'Spicify Relative to Today' ,3,'Specify Relative to Last Sales Date') "Time Window" ,
    q.Time_Filter_Before "Time Periods Before" ,
    q.time_filter_after "Time Periods After" ,
    (SELECT table_label
    FROM group_tables
    WHERE group_table_id = f.group_table_id
    ) "Selected filter levels" ,
    DECODE(
    (SELECT table_label FROM group_tables WHERE group_table_id = f.group_table_id
    ),'Demand Class',
    (SELECT ebs_demand_class
    FROM t_ep_ebs_demand_class
    WHERE t_ep_ebs_demand_class_ep_id=fv.filter_value
    ) ,'Organization',
    (SELECT organization
    FROM t_ep_organization
    WHERE t_ep_organization_ep_id=fv.filter_value
    ) ,'Customer',
    (SELECT ebs_customer
    FROM t_ep_ebs_customer
    WHERE t_ep_ebs_customer_ep_id=fv.filter_value
    ) ,'Forecaster Name',
    (SELECT i_att_4 FROM t_ep_i_att_4 WHERE t_ep_i_att_4_ep_id=fv.filter_value
    ) ,'Item Type',
    (SELECT i_att_5 FROM t_ep_i_att_5 WHERE t_ep_i_att_5_ep_id=fv.filter_value
    ) ,'Site',
    (SELECT site FROM t_ep_site WHERE t_ep_site_ep_id=fv.filter_value
    ) ,'Customer',
    (SELECT ebs_customer
    FROM t_ep_ebs_customer
    WHERE T_EP_EBS_CUSTOMER_EP_ID=fv.filter_value
    ) ,'Item Description',
    (SELECT I_ATT_1 FROM t_ep_I_ATT_1 WHERE T_EP_I_ATT_1_EP_ID=fv.filter_value
    ) ,'Engine Model',
    (SELECT ENGINE_MODEL
    FROM t_ep_engine_model
    WHERE T_EP_ENGINE_MODEL_EP_ID=fv.filter_value
    ) ,'Item',
    (SELECT ITEM FROM t_ep_item WHERE T_EP_ITEM_EP_ID=fv.filter_value
    ) ,'Lead Part',
    (SELECT LEAD_PART
    FROM t_ep_lead_part
    WHERE T_EP_LEAD_PART_EP_ID=fv.filter_value
    ) ,'Engine Family',
    (SELECT ENGINE_FAMILY
    FROM t_ep_engine_family
    WHERE T_EP_ENGINE_FAMILY_EP_ID=fv.filter_value
    ) ) "Selected Members"-- Add Specific levels with filter value
    ,
    cf.computed_title "Series Name"
  FROM active_series a ,
    queries q ,
    groups g ,
    group_tables gt ,
    filters f ,
    filter_values fv ,
    computed_fields cf
  WHERE 1               =1
  AND a.query_id        = q.query_id
  AND g.query_id        =q.query_id
  AND g.group_table_id  = gt.group_table_id
  AND f.query_id        = q.query_id
  AND f.filter_id       =fv.filter_id
  AND a.forecast_type_id=cf.forecast_type_id
  AND q.query_name      = (<Worksheet Name>)
  )
ORDER BY Worksheet_Name,
  Aggregation_Position ;