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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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, 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.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_value_last
       ,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, 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.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.pn) period_value_sum
       ,sum(hp.value) over (partition by hp.planning_instance, hp.plan_name, hp.organization, hp.item, hp.supply_demand_code, hp.grp1, hp.grp2, hp.wn) week_value_sum
       ,sum(hp.value) 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_value_sum
       --
       ,hp.sr_instance_id
       ,hp.plan_id
       ,hp.organization_id
       ,hp.inventory_item_id
       ,hp.sr_inventory_item_id
       ,hp.grp1
       ,hp.grp2
       from
         (select distinct
           -- hp organization id will be -1 for all org aggregation
           -- 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 seq2.column_value not in (3,4,6,7) then decode(mmp.organization_id,-1,'All Orgs',mpo.organization_code) end organization
          ,case when seq2.column_value not in (3,4,6,7) then nvl(mtp.partner_name,'All Orgs') end organization_name                                         
          ,case when seq2.column_value not in (4,5) then mtp.operating_unit_name end operating_unit
          ,case when seq2.column_value < 5 
           then case sign(mmp.inventory_item_id)
                when -1 then 
                  (select mfq.char2
                   from   msc_form_query&a2m_dblink  mfq
                   where  mfq.query_id = mmp.query_id
                   and    mfq.number4  = mmp.plan_id
                   and    mfq.number3  = mmp.sr_instance_id
                   and    mfq.number6  = mmp.organization_id
                   and    mfq.number5  = mmp.inventory_item_id
                   and    rownum      <= 1
                  )
                else msi.item_name
                end
           end                           item
          ,case when seq2.column_value < 5 then msi.description end item_description
          ,mcs.category_set_name         category_set
          ,mic.category_name             category
          ,case when seq2.column_value < 5 then msc_get_name.lookup_meaning&a2m_dblink ('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) end make_buy
          ,case when seq2.column_value < 5 then msi.planner_code end planner
          ,case when seq2.column_value < 5 then msi.buyer_name end buyer
          ,case when seq2.column_value < 5 then msi.standard_cost end standard_cost
          ,case when seq2.column_value < 5 then mmp.item_segments end using_assemblies
          ,case when seq2.column_value = 1
           then 'Horizontal Plan'
           else 'PAB Days Stock Cover'
           end                          dashboard_metric
          ,case seq1.column_value
           when 1 then ml.lookup_code
           else 900       
           end                          supply_demand_code
          ,case seq1.column_value
           when 1 then ml.meaning
           else 'PAB Days Stock Cover'
           end                          supply_demand_type
          ,case seq2.column_value
           when 1 then null
           when 2 then 'Item: Organization'
           when 3 then 'Item: Operating Unit'
           when 4 then 'Item: All Organizations'
           when 5 then 'Category: Organization'
           when 6 then 'Category: Operating Unit'
           when 7 then 'Category: All Organizations'
           end                          aggregation_level
          ,substr(mmp.organization_code,1,instr(mmp.organization_code,'|',1,1)-1)  pn
          ,substr(mmp.organization_code,instr(mmp.organization_code,'|',1,1)+1,length(mmp.organization_code)) wn
          ,(dense_rank() over (order by mmp.bucket_date))-1 dn
          ,mmp.bucket_date bucket_date
          -- Quantity
          ,round(
           case ml.lookup_code
           when 10  then mmp.quantity1
           when 20  then mmp.quantity2
           when 25  then mmp.quantity3
           when 30  then mmp.quantity4
           when 40  then mmp.quantity5
           when 45  then mmp.quantity6
           when 50  then mmp.quantity7
           when 70  then 
             case seq2.column_value
             when 1 then mmp.quantity8
             when 2 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mmp.inventory_item_id) -- DSC Aggregation Dasboard: item x Organization
             when 3 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x OU
             when 4 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x All Orgs
             when 5 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mic.sr_category_id) -- DSC Aggregation Dasboard: category x Organization
             when 6 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mic.sr_category_id) -- DSC Aggregation Dasboard: category x OU
             when 7 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mic.sr_category_id) -- DSC Aggregation Dasboard: Category x All Orgs
             end
           when 81  then mmp.quantity9
           when 83  then mmp.quantity10
           when 85  then mmp.quantity11
           when 87  then mmp.quantity12
           when 89  then mmp.quantity13
           when 90  then mmp.quantity14
           when 95  then mmp.quantity15
           when 100 then mmp.quantity16
           when 105 then mmp.quantity17
           when 110 then 
             case seq1.column_value
             when 1 -- PAB values
             then
               case seq2.column_value
               when 1 then mmp.quantity18
               when 2 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mmp.inventory_item_id) -- DSC Aggregation Dasboard: item x Organization
               when 3 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x OU
               when 4 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x All Orgs
               when 5 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mic.sr_category_id) -- DSC Aggregation Dasboard: category x Organization
               when 6 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mic.sr_category_id) -- DSC Aggregation Dasboard: category x OU
               when 7 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mic.sr_category_id) -- DSC Aggregation Dasboard: Category x All Orgs
               end
             else -- PAB Days of Cover values
               case seq2.column_value
               when 1 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_organization_id=>mmp.organization_id,p_inventory_item_id=>mmp.inventory_item_id) -- DSC
               when 2 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_organization_id=>mmp.organization_id,p_inventory_item_id=>mmp.inventory_item_id) -- DSC Aggregation Dasboard: item x Organization
               when 3 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_operating_unit_id=>mtp.operating_unit,p_inventory_item_id=>mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x OU
               when 4 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_inventory_item_id=>mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x All Orgs
               when 5 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_organization_id=>mmp.organization_id,p_sr_category_id=>mic.sr_category_id) -- DSC Aggregation Dasboard: category x Organization
               when 6 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_operating_unit_id=>mtp.operating_unit,p_sr_category_id=>mic.sr_category_id) -- DSC Aggregation Dasboard: category x OU
               when 7 then xxen_msc_horizplan.get_pab_days_stock_cover(p_pab_date=>mmp.bucket_date,p_sr_category_id=>mic.sr_category_id) -- DSC Aggregation Dasboard: Category x All Orgs
               end
             end
           when 120 then mmp.quantity19
           when 180 then mmp.quantity19
           when 130 then mmp.quantity20
           when 140 then mmp.quantity21
           when 150 then mmp.quantity22
           when 160 then mmp.quantity23
           when 210 then mmp.quantity24
           when 175 then mmp.quantity25
           when 177 then mmp.quantity26
           when 190 then mmp.quantity27
           when 200 then mmp.quantity28
           when 220 then mmp.quantity29
           when 230 then mmp.quantity30
           when 240 then mmp.quantity31
           when 250 then mmp.quantity32
           when 260 then mmp.quantity33
           when 270 then mmp.quantity34
           when 280 then mmp.quantity35
           when 178 then mmp.quantity36
           when 183 then mmp.quantity37
           when 184 then mmp.quantity38
           when 185 then mmp.quantity39
           when 186 then mmp.quantity40
           when 290 then mmp.quantity41
           when 300 then mmp.quantity42
           -- columns 43,44 are not populated by msc_horizontal_plan_sc
           when 500 then mmp.quantity45
           when 295 then mmp.quantity46
           when 330 then mmp.quantity47
           when 305 then mmp.quantity48
           when 310 then mmp.quantity49
           when 315 then mmp.quantity50
           when 320 then mmp.quantity51
           when 325 then mmp.quantity52
           when 335 then mmp.quantity53
           when 340 then mmp.quantity54
           when 345 then mmp.quantity55
           when 350 then mmp.quantity56
           end,nvl(to_number('&p_decimal_places'),1))  quantity
          -- Value
          ,case ml.lookup_code
           when 10  then mmp.quantity1
           when 20  then mmp.quantity2
           when 25  then mmp.quantity3
           when 30  then mmp.quantity4
           when 40  then mmp.quantity5
           when 45  then mmp.quantity6
           when 50  then mmp.quantity7
           when 70  then 
             case seq2.column_value
             when 1 then mmp.quantity8
             when 2 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mmp.inventory_item_id) -- DSC Aggregation Dasboard: item x Organization
             when 3 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x OU
             when 4 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x All Orgs
             when 5 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mic.sr_category_id) -- DSC Aggregation Dasboard: category x Organization
             when 6 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mic.sr_category_id) -- DSC Aggregation Dasboard: category x OU
             when 7 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 70 then mmp.quantity8 else 0 end) over (partition by mmp.bucket_date,mic.sr_category_id) -- DSC Aggregation Dasboard: Category x All Orgs
             end
           when 81  then mmp.quantity9
           when 83  then mmp.quantity10
           when 85  then mmp.quantity11
           when 87  then mmp.quantity12
           when 89  then mmp.quantity13
           when 90  then mmp.quantity14
           when 95  then mmp.quantity15
           when 100 then mmp.quantity16
           when 105 then mmp.quantity17
           when 110 then 
             case seq1.column_value
             when 1 -- PAB values
             then
               case seq2.column_value
               when 1 then mmp.quantity18
               when 2 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mmp.inventory_item_id) -- DSC Aggregation Dasboard: item x Organization
               when 3 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mtp.operating_unit,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x OU
               when 4 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.inventory_item_id) -- DSC Aggregation Dasboard: Item x All Orgs
               when 5 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code = 110 then mmp.quantity18 else 0 end) over (partition by mmp.bucket_date,mmp.organization_id,mic.sr_category_id) -- DSC Aggregation Dasboard: category x Organization
               when 6 then sum(case when seq1.column_value = 1 and seq2.column_value=1 and ml.lookup_code
Parameter Name SQL text Validation
Dashboard Metric 
 
 LOV
Planning Instance 
 
 LOV
Plan 
 
 LOV
Organization 
mpo.organization_code = :org
 LOV
Category Set 
 
 LOV
Category 
 
 LOV
Item 
msi.item_name=:item
 LOV
Item Restriction Limit 
 
 Number
Buckets 
 
 LOV
Preference 
 
 LOV
Supply Demand Types 
((ml.meaning = :p_sd_type) or
 (:p_sd_type = 'PAB Days Stock Cover' and ml.lookup_code = 110 and seq1.column_value = 2)
)
 LOV
Aggregation Level 
 
 LOV
Show Item Descriptive Attributes 
select 
xxen_msc.get_item_dff_lexicals(mai.instance_id,'hp2.sr_inventory_item_id','hp2.organization_id')
from
(
select maaia.instance_id from mrp_ap_apps_instances_all maaia where maaia.instance_code = :p_instance_code union
select mai.instance_id from msc_apps_instances mai where mai.instance_code = :p_instance_code and not exists (select null from  mrp_ap_apps_instances_all maaia where maaia.instance_code = :p_instance_code)
) mai
 LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: