MSC Horizontal Plan

Description
Categories: Enginatics
Repository: Github
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 MSC Horizontal Plan and other Oracle EBS reports with Blitz Report™ on our demo environment
with
  mfg_sd_lookups as
  ( select
     ml.lookup_code,
     ml.meaning 
    from
     msc_apps_instances&a2m_dblink  mai,
     msc_plans&a2m_dblink           mp,
     mfg_lookups&a2m_dblink         ml
    where
        mp.sr_instance_id = mai.instance_id
    and mp.compile_designator = :p_plan_name
    and mai.instance_code = :p_instance_code
    and (
             (    mp.plan_type != 4 -- ASCP Plan
              and ml.lookup_type = 'MRP_HORIZONTAL_PLAN_TYPE_SC'
              and ml.lookup_code not in (97,82,125,170,180,190)
              and ((    nvl(fnd_profile.value&a2m_dblink ('MSC_ASCP_IGNORE_CMRO_EAM_WO'),1) = 1 
                    and ml.lookup_code not in (295,330,305,310,315,320,325,335,340,345,350)
                   )
                   or
                   (nvl(fnd_profile.value&a2m_dblink ('MSC_ASCP_IGNORE_CMRO_EAM_WO'),1) = 2
                   )
                  )
              and ((    fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is null
                    and ml.lookup_code not in (500) -- custom user defined
                   )
                   or
                   (fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is not null
                   )
                  ) 
              and NVL(xxen_msc_horizplan.get_pref_option(mp.plan_type,:p_pref_name,ml.lookup_type,ml.lookup_code),'Y') = 'Y'
             ) 
          or
             (    mp.plan_type = 4 -- IO Plan
              and ml.lookup_type = 'MSC_HORIZONTAL_PLAN_TYPE_IO'
              and ((    fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is null
                    and ml.lookup_code not in (500) -- custom user defined
                   )
                   or
                   (fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is not null
                   )
                  )
              and NVL(xxen_msc_horizplan.get_pref_option(mp.plan_type,:p_pref_name,ml.lookup_type,ml.lookup_code),'Y') = 'Y'
             )
        )
    union
    select
     900 lookup_code,
     'PAB Days Stock Cover' meaning
    from
     dual&a2m_dblink
    where
     nvl(:p_metric,'Horizontal Plan') = 'PAB Days Stock Cover'
  ),
  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.item_uom,
     hp2.report_uom,
     hp2.uom_conversion,
     hp2.make_buy "Make/Buy",
     hp2.planner,
     hp2.buyer,
     hp2.dashboard_metric,
     hp2.supply_demand_code,
     hp2.supply_demand_type,
     hp2.aggregation_level,
     hp2.aggregation_label,
     hp2.bucket_date,
     hp2.pn period_num,
     hp2.wn week_num,
     hp2.dn day_num,
     --
     -- quantities
     --
     hp2.quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.week_qty_last
     when 120 then hp2.week_qty_last
     when 130 then hp2.week_qty_last
     when 150 then hp2.week_qty_last
     when 175 then hp2.week_qty_last
     when 177 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 240 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
     when 900 then hp2.week_qty_last
     else hp2.week_qty_sum
     end week_quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.period_qty_last
     when 120 then hp2.period_qty_last
     when 130 then hp2.period_qty_last
     when 150 then hp2.period_qty_last
     when 175 then hp2.period_qty_last
     when 177 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 240 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
     when 900 then hp2.period_qty_last
     else hp2.period_qty_sum
     end period_quantity,
     --
     case hp2.supply_demand_code
     when 110 then hp2.year_qty_last
     when 120 then hp2.year_qty_last
     when 130 then hp2.year_qty_last
     when 150 then hp2.year_qty_last
     when 175 then hp2.year_qty_last
     when 177 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 240 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
     when 900 then hp2.year_qty_last
     else hp2.year_qty_sum
     end year_quantity,
     --
     -- values
     --
     hp2.standard_cost,
     hp2.value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.week_value_last
     when 120 then hp2.week_value_last
     when 130 then hp2.week_value_last
     when 150 then hp2.week_value_last
     when 175 then hp2.week_value_last
     when 177 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 240 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
     when 900 then hp2.week_value_last
     else hp2.week_value_sum
     end week_value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.period_value_last
     when 120 then hp2.period_value_last
     when 130 then hp2.period_value_last
     when 150 then hp2.period_value_last
     when 175 then hp2.period_value_last
     when 177 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 240 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
     when 900 then hp2.period_value_last
     else hp2.period_value_sum
     end period_value,
     --
     case hp2.supply_demand_code
     when 110 then hp2.year_value_last
     when 120 then hp2.year_value_last
     when 130 then hp2.year_value_last
     when 150 then hp2.year_value_last
     when 175 then hp2.year_value_last
     when 177 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 240 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
     when 900 then hp2.year_value_last
     else hp2.year_value_sum
     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,
     --
     case substr(:p_summary_level,1,1)
     when 'A' 
     then case 
          when hp2.wn <  xxen_msc_horizplan.get_week_bucket_cutoff_wn
          then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn || ' D:' || hp2.dn
          when hp2.wn >= xxen_msc_horizplan.get_week_bucket_cutoff_wn
          and  hp2.wn  < xxen_msc_horizplan.get_period_bucket_cutoff_wn
          then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' W:' || hp2.wn || ' P:' || hp2.pn
          else to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn
          end
     when 'P' then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn
     when 'W' then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn
              else to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn || ' D:' || hp2.dn
     end bucket_label,
     --
     to_char(hp2.bucket_date,'YYYY') year_chart_label,
     'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY') period_chart_label,
     case substr(:p_summary_level,1,1)
     when 'A'
     then case when hp2.wn >= xxen_msc_horizplan.get_period_bucket_cutoff_wn
          then 'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY')
          else 'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
          end 
     else  'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
     end week_chart_label,
     case substr(:p_summary_level,1,1)
     when 'A'
     then case 
          when hp2.wn < xxen_msc_horizplan.get_week_bucket_cutoff_wn
          then 'D:' || ltrim(to_char(hp2.dn,'000')) || ' ' || to_char(hp2.bucket_date,'DD-MON-YY') 
          when hp2.wn >= xxen_msc_horizplan.get_week_bucket_cutoff_wn
          and  hp2.wn  < xxen_msc_horizplan.get_period_bucket_cutoff_wn
          then 'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
          else 'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY')
          end 
     else  'D:' || ltrim(to_char(hp2.dn,'000')) || ' ' || to_char(hp2.bucket_date,'DD-MON-YY')
     end day_chart_label,
     --
     row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.aggregation_level,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.aggregation_level,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.aggregation_level,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.item_uom
       ,hp.report_uom
       ,hp.uom_conversion
       ,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
       ,case hp.horizontal_plan_type_text
        when 'IU' then hp.operating_unit || ' - ' || hp.item
        when 'IA' then hp.item
        when 'CO' then replace(hp.organization,hp.planning_instance || ':','') || ' - ' || hp.category
        when 'CU' then hp.operating_unit || ' - ' || hp.category
        when 'CA' then hp.category
                  else replace(hp.organization,hp.planning_instance || ':','') || ' - ' || hp.item
        end  aggregation_label
       ,hp.bucket_date
       --
       ,hp.pn
       ,hp.wn
       ,hp.dn
       ,first_value(hp.bucket_date) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,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.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_date
       --
       ,hp.quantity
       ,last_value(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,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.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,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.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,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.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn) period_qty_sum
       ,sum(hp.quantity)        over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn) week_qty_sum
       ,sum(hp.quantity)        over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,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.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_value_last
       ,last_value(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_value_last
       ,last_value(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY') order by hp.bucket_date range between unbounded preceding and unbounded following) year_value_last
       ,sum(hp.value)        over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn) period_value_sum
       ,sum(hp.value)        over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn) week_value_sum
       ,sum(hp.value)        over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY')) year_value_sum
       --
       ,hp.sr_instance_id
       ,hp.plan_id
       ,hp.organization_id
       ,hp.inventory_item_id
       ,hp.sr_inventory_item_id 
       from
         (select
           -- hp organization id would be -1 for all org aggregation, however not using this opton in this code as we always pass the list of Org IDs even for all orgs
           -- hp inventory item id will be negative for product family details when display product family details = True
           mai.instance_code             planning_instance
          ,mp.compile_designator         plan_name
          ,case when mmp.horizontal_plan_type_text in