EC: MSC Horizontal Plan

➜ Back to Library
Description
ASCP: Horizontal Plan from the Planners Workbench.

Note:
The number of Items included in the HP is restricted by the parameter ‘Item Restriction Limit’. This parameter defaults from the profile option ‘MSC: HP Maximum Displayed Item Count’ in the ASCP Planning Instance. The value set in the Item Restriction Limit parameter will override the value specified in profile option.
 ...  ASCP: Horizontal Plan from the Planners Workbench.

Note:
The number of Items included in the HP is restricted by the parameter ‘Item Restriction Limit’. This parameter defaults from the profile option ‘MSC: HP Maximum Displayed Item Count’ in the ASCP Planning Instance. The value set in the Item Restriction Limit parameter will override the value specified in profile option.
   more
Run EC: MSC Horizontal Plan and other Oracle EBS reports with Blitz Report™ on our demo environment
with
  hp3 as
  ( select
     hp2.planning_instance,
     hp2.plan_name,
     hp2.organization,
     hp2.organization_name,  
     hp2.operating_unit,
     hp2.item,
     hp2.item_description,
     hp2.category_set,
     hp2.category,
     hp2.make_buy "Make/Buy",
     hp2.planner,
     hp2.buyer,
     hp2.dashboard_metric,
     hp2.supply_demand_code,
     hp2.supply_demand_type,
     hp2.aggregation_level,
     decode(substr(:p_summary_level,1,1),'P',hp2.period_date,'W',hp2.week_date,hp2.bucket_date) bucket_date,
     hp2.pn period_num,
     hp2.wn week_num,
     hp2.dn day_num,
     --
     -- quantities
     --
     case substr(:p_summary_level,1,1)
     when 'P' then case hp2.supply_demand_code
                   when 110 then hp2.period_qty_last
                   when 130 then hp2.period_qty_last
                   when 150 then hp2.period_qty_last
                   when 178 then hp2.period_qty_last
                   when 180 then hp2.period_qty_last
                   when 183 then hp2.period_qty_last
                   when 184 then hp2.period_qty_last
                   when 185 then hp2.period_qty_last
                   when 186 then hp2.period_qty_last
                   when 190 then hp2.period_qty_last
                   when 200 then hp2.period_qty_last
                   when 210 then hp2.period_qty_last
                   when 220 then hp2.period_qty_last
                   when 230 then hp2.period_qty_last
                   when 250 then hp2.period_qty_last
                   when 260 then hp2.period_qty_last
                   when 270 then hp2.period_qty_last
                   when 280 then hp2.period_qty_last
                   when 290 then hp2.period_qty_last
                   when 300 then hp2.period_qty_last
                   else case when hp2.supply_demand_code >= 900
                        then hp2.period_qty_last
                        else hp2.period_qty_sum
                        end
                   end
     when 'W' then case hp2.supply_demand_code
                   when 110 then hp2.week_qty_last
                   when 130 then hp2.week_qty_last
                   when 150 then hp2.week_qty_last
                   when 178 then hp2.week_qty_last
                   when 180 then hp2.week_qty_last
                   when 183 then hp2.week_qty_last
                   when 184 then hp2.week_qty_last
                   when 185 then hp2.week_qty_last
                   when 186 then hp2.week_qty_last
                   when 190 then hp2.week_qty_last
                   when 200 then hp2.week_qty_last
                   when 210 then hp2.week_qty_last
                   when 220 then hp2.week_qty_last
                   when 230 then hp2.week_qty_last
                   when 250 then hp2.week_qty_last
                   when 260 then hp2.week_qty_last
                   when 270 then hp2.week_qty_last
                   when 280 then hp2.week_qty_last
                   when 290 then hp2.week_qty_last
                   when 300 then hp2.week_qty_last
                   else case when hp2.supply_demand_code >= 900
                        then hp2.week_qty_last
                        else hp2.week_qty_sum
                        end
                   end
     else hp2.quantity
     end  quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.week_qty_last
     when 130 then hp2.week_qty_last
     when 150 then hp2.week_qty_last
     when 178 then hp2.week_qty_last
     when 180 then hp2.week_qty_last
     when 183 then hp2.week_qty_last
     when 184 then hp2.week_qty_last
     when 185 then hp2.week_qty_last
     when 186 then hp2.week_qty_last
     when 190 then hp2.week_qty_last
     when 200 then hp2.week_qty_last
     when 210 then hp2.week_qty_last
     when 220 then hp2.week_qty_last
     when 230 then hp2.week_qty_last
     when 250 then hp2.week_qty_last
     when 260 then hp2.week_qty_last
     when 270 then hp2.week_qty_last
     when 280 then hp2.week_qty_last
     when 290 then hp2.week_qty_last
     when 300 then hp2.week_qty_last
     else case when hp2.supply_demand_code >= 900
          then hp2.week_qty_last
          else hp2.week_qty_sum
          end
     end week_quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.period_qty_last
     when 130 then hp2.period_qty_last
     when 150 then hp2.period_qty_last
     when 178 then hp2.period_qty_last
     when 180 then hp2.period_qty_last
     when 183 then hp2.period_qty_last
     when 184 then hp2.period_qty_last
     when 185 then hp2.period_qty_last
     when 186 then hp2.period_qty_last
     when 190 then hp2.period_qty_last
     when 200 then hp2.period_qty_last
     when 210 then hp2.period_qty_last
     when 220 then hp2.period_qty_last
     when 230 then hp2.period_qty_last
     when 250 then hp2.period_qty_last
     when 260 then hp2.period_qty_last
     when 270 then hp2.period_qty_last
     when 280 then hp2.period_qty_last
     when 290 then hp2.period_qty_last
     when 300 then hp2.period_qty_last
     else case when hp2.supply_demand_code >= 900
          then hp2.period_qty_last
          else hp2.period_qty_sum
          end
     end period_quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.year_qty_last
     when 130 then hp2.year_qty_last
     when 150 then hp2.year_qty_last
     when 178 then hp2.year_qty_last
     when 180 then hp2.year_qty_last
     when 183 then hp2.year_qty_last
     when 184 then hp2.year_qty_last
     when 185 then hp2.year_qty_last
     when 186 then hp2.year_qty_last
     when 190 then hp2.year_qty_last
     when 200 then hp2.year_qty_last
     when 210 then hp2.year_qty_last
     when 220 then hp2.year_qty_last
     when 230 then hp2.year_qty_last
     when 250 then hp2.year_qty_last
     when 260 then hp2.year_qty_last
     when 270 then hp2.year_qty_last
     when 280 then hp2.year_qty_last
     when 290 then hp2.year_qty_last
     when 300 then hp2.year_qty_last
     else case when hp2.supply_demand_code >= 900
          then hp2.year_qty_last
          else hp2.year_qty_sum
          end
     end year_quantity,
     --
     -- values
     --
     hp2.standard_cost,
     case substr(:p_summary_level,1,1)
     when 'P' then case hp2.supply_demand_code
                   when 110 then hp2.period_value_last
                   when 130 then hp2.period_value_last
                   when 150 then hp2.period_value_last
                   when 178 then hp2.period_value_last
                   when 180 then hp2.period_value_last
                   when 183 then hp2.period_value_last
                   when 184 then hp2.period_value_last
                   when 185 then hp2.period_value_last
                   when 186 then hp2.period_value_last
                   when 190 then hp2.period_value_last
                   when 200 then hp2.period_value_last
                   when 210 then hp2.period_value_last
                   when 220 then hp2.period_value_last
                   when 230 then hp2.period_value_last
                   when 250 then hp2.period_value_last
                   when 260 then hp2.period_value_last
                   when 270 then hp2.period_value_last
                   when 280 then hp2.period_value_last
                   when 290 then hp2.period_value_last
                   when 300 then hp2.period_value_last
                   else case when hp2.supply_demand_code >= 900
                        then hp2.period_value_last
                        else hp2.period_value_sum
                        end
                   end
     when 'W' then case hp2.supply_demand_code
                   when 110 then hp2.week_value_last
                   when 130 then hp2.week_value_last
                   when 150 then hp2.week_value_last
                   when 178 then hp2.week_value_last
                   when 180 then hp2.week_value_last
                   when 183 then hp2.week_value_last
                   when 184 then hp2.week_value_last
                   when 185 then hp2.week_value_last
                   when 186 then hp2.week_value_last
                   when 190 then hp2.week_value_last
                   when 200 then hp2.week_value_last
                   when 210 then hp2.week_value_last
                   when 220 then hp2.week_value_last
                   when 230 then hp2.week_value_last
                   when 250 then hp2.week_value_last
                   when 260 then hp2.week_value_last
                   when 270 then hp2.week_value_last
                   when 280 then hp2.week_value_last
                   when 290 then hp2.week_value_last
                   when 300 then hp2.week_value_last
                   else case when hp2.supply_demand_code >= 900
                        then hp2.week_value_last
                        else hp2.week_value_sum
                        end
                   end
     else hp2.value
     end  value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.week_value_last
     when 130 then hp2.week_value_last
     when 150 then hp2.week_value_last
     when 178 then hp2.week_value_last
     when 180 then hp2.week_value_last
     when 183 then hp2.week_value_last
     when 184 then hp2.week_value_last
     when 185 then hp2.week_value_last
     when 186 then hp2.week_value_last
     when 190 then hp2.week_value_last
     when 200 then hp2.week_value_last
     when 210 then hp2.week_value_last
     when 220 then hp2.week_value_last
     when 230 then hp2.week_value_last
     when 250 then hp2.week_value_last
     when 260 then hp2.week_value_last
     when 270 then hp2.week_value_last
     when 280 then hp2.week_value_last
     when 290 then hp2.week_value_last
     when 300 then hp2.week_value_last
     else case when hp2.supply_demand_code >= 900
          then hp2.week_value_last
          else hp2.week_value_sum
          end
     end week_value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.period_value_last
     when 130 then hp2.period_value_last
     when 150 then hp2.period_value_last
     when 178 then hp2.period_value_last
     when 180 then hp2.period_value_last
     when 183 then hp2.period_value_last
     when 184 then hp2.period_value_last
     when 185 then hp2.period_value_last
     when 186 then hp2.period_value_last
     when 190 then hp2.period_value_last
     when 200 then hp2.period_value_last
     when 210 then hp2.period_value_last
     when 220 then hp2.period_value_last
     when 230 then hp2.period_value_last
     when 250 then hp2.period_value_last
     when 260 then hp2.period_value_last
     when 270 then hp2.period_value_last
     when 280 then hp2.period_value_last
     when 290 then hp2.period_value_last
     when 300 then hp2.period_value_last
     else case when hp2.supply_demand_code >= 900
          then hp2.period_value_last
          else hp2.period_value_sum
          end
     end period_value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.year_value_last
     when 130 then hp2.year_value_last
     when 150 then hp2.year_value_last
     when 178 then hp2.year_value_last
     when 180 then hp2.year_value_last
     when 183 then hp2.year_value_last
     when 184 then hp2.year_value_last
     when 185 then hp2.year_value_last
     when 186 then hp2.year_value_last
     when 190 then hp2.year_value_last
     when 200 then hp2.year_value_last
     when 210 then hp2.year_value_last
     when 220 then hp2.year_value_last
     when 230 then hp2.year_value_last
     when 250 then hp2.year_value_last
     when 260 then hp2.year_value_last
     when 270 then hp2.year_value_last
     when 280 then hp2.year_value_last
     when 290 then hp2.year_value_last
     when 300 then hp2.year_value_last
     else case when hp2.supply_demand_code >= 900
          then hp2.year_value_last
          else hp2.year_value_sum
          end
     end year_value,
     --
     --
     hp2.using_assemblies,
     -- item dff attributes
     &lp_item_dff_cols
     --
     --
     ltrim(to_char(hp2.supply_demand_code,'000')) || ' ' || hp2.supply_demand_type supply_demand_label,
     to_char(decode(substr(:p_summary_level,1,1),'P',hp2.period_date,'W',hp2.week_date,hp2.bucket_date),'YYYY/MM/DD')
     || ' P:' || hp2.pn
     || decode(substr(:p_summary_level,1,1),'P',null,' W:' || hp2.wn)
     || decode(substr(:p_summary_level,1,1),'D',' D:' || hp2.dn) bucket_label,
     to_char(hp2.bucket_date,'YYYY') year_char,
     'P:' || ltrim(to_char(hp2.pn,'00')) || ' ' ||
     case when hp2.pn > 0
     then
        (select
         cal.period_name
         from
         msc_period_start_dates&a2m_dblink cal,
         msc_trading_partners&a2m_dblink mtp
         where
         mtp.sr_tp_id = hp2.organization_id and
         mtp.sr_instance_id  = hp2.sr_instance_id and
         mtp.partner_type    = 3 and
         mtp.calendar_code   = cal.calendar_code and
         mtp.sr_instance_id  = cal.sr_instance_id and
         mtp.calendar_exception_set_id = cal.exception_set_id and
         trunc(hp2.period_date) between cal.period_start_date and cal.next_date-1
        )
     else null
     end period_char,
     'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY') week_char,
     row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,to_char(hp2.bucket_date,'YYYY') order by hp2.bucket_date) yn_seq,
     row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,hp2.pn order by hp2.bucket_date) pn_seq,
     row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,hp2.wn order by hp2.bucket_date) wn_seq
    from
      (select
        hp.planning_instance
       ,hp.plan_name
       ,hp.organization
       ,hp.organization_name 
       ,hp.operating_unit
       ,hp.item
       ,hp.item_description
       ,hp.category_set
       ,hp.category
       ,hp.make_buy
       ,hp.planner
       ,hp.buyer
       ,hp.standard_cost
       ,hp.using_assemblies
       ,hp.dashboard_metric
       ,hp.supply_demand_code
       ,hp.supply_demand_type
       ,hp.aggregation_level
       ,hp.bucket_date
       --
       ,hp.pn
       ,hp.wn
       ,hp.dn
       ,first_value(hp.bucket_date) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_date
       ,first_value(hp.bucket_date) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_date
       ,last_value(hp.bucket_date) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_date_last
       ,last_value(hp.bucket_date) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_date_last
       --
       ,hp.quantity
       ,last_value(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_qty_last
       ,last_value(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_qty_last
       ,last_value(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, to_char(hp.bucket_date,'YYYY') order by hp.bucket_date range between unbounded preceding and unbounded following) year_qty_last
       ,sum(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.pn) period_qty_sum
       ,sum(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.wn) week_qty_sum
       ,sum(hp.quantity) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, to_char(hp.bucket_date,'YYYY')) year_qty_sum
       --
       ,hp.value
       ,last_value(hp.value) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2