MRP Horizontal Plan

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
MRP: Horizontal Plan from the Planners Workbench.
- The report can be run for multiple organizations and items within a selected plan
- The report can be used to explode BOMS and display the HP for all components
within the BOM
- Select a template to choose to either display the HP by individual components, or by
End Assemblies, Components.
Run MRP Horizontal Plan and other Oracle EBS reports with Blitz Report™ on our demo environment
with
mmp as
(
select
  mmp.plan_id
, mmp.organization_id
, mmp.compile_designator
, mmp.inventory_item_id
, mmp.item_segments
, mmp.horizontal_plan_type
, mmp.bucket_type
, mmp.horizontal_plan_type_text
, mmp.plan_organization_id
, mmp.organization_code
, case seq.column_value
  when 1  then (mwbd.date1)
  when 2  then (mwbd.date2)
  when 3  then (mwbd.date3)
  when 4  then (mwbd.date4)
  when 5  then (mwbd.date5)
  when 6  then (mwbd.date6)
  when 7  then (mwbd.date7)
  when 8  then (mwbd.date8)
  when 9  then (mwbd.date9)
  when 10 then (mwbd.date10)
  when 11 then (mwbd.date11)
  when 12 then (mwbd.date12)
  when 13 then (mwbd.date13)
  when 14 then (mwbd.date14)
  when 15 then (mwbd.date15)
  when 16 then (mwbd.date16)
  when 17 then (mwbd.date17)
  when 18 then (mwbd.date18)
  when 19 then (mwbd.date19)
  when 20 then (mwbd.date20)
  when 21 then (mwbd.date21)
  when 22 then (mwbd.date22)
  when 23 then (mwbd.date23)
  when 24 then (mwbd.date24)
  when 25 then (mwbd.date25)
  when 26 then (mwbd.date26)
  when 27 then (mwbd.date27)
  when 28 then (mwbd.date28)
  when 29 then (mwbd.date29)
  when 30 then (mwbd.date30)
  when 31 then (mwbd.date31)
  when 32 then (mwbd.date32)
  when 33 then (mwbd.date33)
  when 34 then (mwbd.date34)
  when 35 then (mwbd.date35)
  when 36 then (mwbd.date36)
  else to_date(null)
  end bucket_date
, to_number(xxen_mrp_horizplan.format_quantity
  (
   case seq.column_value
   when 1  then (mmp.quantity1)
   when 2  then (mmp.quantity2)
   when 3  then (mmp.quantity3)
   when 4  then (mmp.quantity4)
   when 5  then (mmp.quantity5)
   when 6  then (mmp.quantity6)
   when 7  then (mmp.quantity7)
   when 8  then (mmp.quantity8)
   when 9  then (mmp.quantity9)
   when 10 then (mmp.quantity10)
   when 11 then (mmp.quantity11)
   when 12 then (mmp.quantity12)
   when 13 then (mmp.quantity13)
   when 14 then (mmp.quantity14)
   when 15 then (mmp.quantity15)
   when 16 then (mmp.quantity16)
   when 17 then (mmp.quantity17)
   when 18 then (mmp.quantity18)
   when 19 then (mmp.quantity19)
   when 20 then (mmp.quantity20)
   when 21 then (mmp.quantity21)
   when 22 then (mmp.quantity22)
   when 23 then (mmp.quantity23)
   when 24 then (mmp.quantity24)
   when 25 then (mmp.quantity25)
   when 26 then (mmp.quantity26)
   when 27 then (mmp.quantity27)
   when 28 then (mmp.quantity28)
   when 29 then (mmp.quantity29)
   when 30 then (mmp.quantity30)
   when 31 then (mmp.quantity31)
   when 32 then (mmp.quantity32)
   when 33 then (mmp.quantity33)
   when 34 then (mmp.quantity34)
   when 35 then (mmp.quantity35)
   when 36 then (mmp.quantity36)
   else to_number(null)
   end
  )) bucket_quantity
from
  mrp_material_plans mmp
, mrp_workbench_bucket_dates mwbd
, table(xxen_util.rowgen(36)) seq
where
    mmp.plan_id                 = xxen_mrp_horizplan.get_hp_plan_id
and mmp.bucket_type             = xxen_mrp_horizplan.get_hp_bucket_type
and xxen_mrp_horizplan.hp_source_enabled(mmp.horizontal_plan_type) = 'Y'
and mwbd.compile_designator     = xxen_mrp_horizplan.get_compile_designator
and mwbd.organization_id        = xxen_mrp_horizplan.get_plan_organization_id
and nvl(mwbd.planned_organization
       ,mwbd.organization_id)   = xxen_mrp_horizplan.get_planned_organization_id
and mwbd.bucket_type            = xxen_mrp_horizplan.get_hp_bucket_type
)
--
--
select
  hp.*
from
(
select
  mmp.compile_designator                             plan
, mmp.organization_code                              organization_code
, nvl( mea.end_assembly
     , case when xxen_mrp_horizplan.is_end_assembly(mmp.compile_designator,mmp.organization_id,mmp.inventory_item_id)='Y'
       then msiv.concatenated_segments
       else null
       end
     )                                               end_assembly
, nvl( mea.end_assembly_desc
     , case when xxen_mrp_horizplan.is_end_assembly(mmp.compile_designator,mmp.organization_id,mmp.inventory_item_id)='Y'
       then msiv.description
       else null
       end
     )                                               end_assembly_description
, case when mea.end_assembly_id is not null
  then xxen_mrp_horizplan.get_bom_level(mmp.organization_id,mea.end_assembly_id,msiv.inventory_item_id)
  else case when xxen_mrp_horizplan.is_end_assembly(mmp.compile_designator,mmp.organization_id,mmp.inventory_item_id)='Y'
  then xxen_mrp_horizplan.get_bom_level(mmp.organization_id,msiv.inventory_item_id,msiv.inventory_item_id)
  else null
  end end                                            bom_level
, mmp.item_segments                                  item
, msiv.description                                   item_description
, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3)    item_type
, msiv.primary_unit_of_measure                       uom
, mck.concatenated_segments                          category
, msiv.preprocessing_lead_time
, msiv.full_lead_time
, msiv.postprocessing_lead_time
, msiv.cumulative_total_lead_time
, msiv.cum_manufacturing_lead_time                   cumulative_mfg_lead_time
, (select asu.vendor_name
   from   mrp_system_items msi
      ,   ap_suppliers asu
   where  msi.compile_designator = mmp.compile_designator
   and    msi.organization_id    = mmp.organization_id
   and    msi.inventory_item_id  = mmp.inventory_item_id
   and    asu.vendor_id          = msi.primary_vendor_id
  )                                                  primary_supplier
, (select ppx.full_name
   from   per_people_x ppx
   where ppx.person_id = msiv.buyer_id
   and   rownum=1
  )                                                  buyer
, xxen_util.meaning(msiv.planning_make_buy_code
                   ,'MTL_PLANNING_MAKE_BUY',700)     make_or_buy
, xxen_util.meaning(msiv.wip_supply_type
                   ,'WIP_SUPPLY',700)                wip_supply_type
, msiv.planner_code
, mpl.description                                    planner_description
, xxen_util.meaning(msiv.mrp_planning_code
                   ,'MRP_PLANNING_CODE',700)         planning_method
, xxen_util.meaning(msiv.ato_forecast_control
                   ,'MRP_ATO_FORECAST_CONTROL',700)  forecast_control
, xxen_util.meaning(msiv.end_assembly_pegging_flag
                   ,'ASSEMBLY_PEGGING_CODE',0)       pegging
, xxen_util.meaning(msiv.planning_time_fence_code
                   ,'MTL_TIME_FENCE',700)            planning_time_fence
, xxen_mrp_horizplan.get_end_assemblies(mmp.compile_designator,mmp.organization_id,mmp.inventory_item_id)
                                                     end_assemblies
, mmp.horizontal_plan_type_text                      source_type_text
, mmp.bucket_date
, mmp.bucket_quantity
, mmp.horizontal_plan_type                           source_type
, case when mea.end_assembly_id is not null
  then xxen_mrp_horizplan.get_bom_sort_order(mmp.organization_id,mea.end_assembly_id,msiv.inventory_item_id)
  else case when xxen_mrp_horizplan.is_end_assembly(mmp.compile_designator,mmp.organization_id,mmp.inventory_item_id)='Y'
  then xxen_mrp_horizplan.get_bom_sort_order(mmp.organization_id,msiv.inventory_item_id,msiv.inventory_item_id)
  else null
  end end                                            bom_sort_order
from
  mmp                 mmp
, mtl_system_items_vl  msiv
, mtl_item_categories  mic
, mtl_categories_kfv   mck
, mtl_planners         mpl
, ( select
      mea2.compile_designator
    , mea2.organization_id
    , mea2.inventory_item_id
    , mea2.end_assembly_id
    , msiv2.concatenated_segments end_assembly
    , msiv2.description           end_assembly_desc
    from
      mrp_end_assemblies   mea2
    , mtl_system_items_vl  msiv2
    where
        msiv2.organization_id   = mea2.organization_id
    and msiv2.inventory_item_id = mea2.end_assembly_id
    and msiv2.bom_item_type    in (1,2,3,4)
  )                    mea
where
    msiv.organization_id        = mmp.organization_id
and msiv.inventory_item_id      = mmp.inventory_item_id
and mic.category_set_id     (+) = xxen_mrp_horizplan.get_category_set_id
and mic.organization_id     (+) = mmp.organization_id
and mic.inventory_item_id   (+) = mmp.inventory_item_id
and mck.category_id         (+) = mic.category_id
and mpl.planner_code        (+) = msiv.planner_code
and mpl.organization_id     (+) = msiv.organization_id
and mea.compile_designator  (+) = mmp.compile_designator
and mea.organization_id     (+) = mmp.organization_id
and mea.inventory_item_id   (+) = mmp.inventory_item_id
)  hp
where
  1=1
order by
  hp.plan
, hp.organization_code
, hp.end_assembly
, hp.bom_sort_order
, hp.item
, hp.source_type
Parameter Name SQL text Validation
Plan 
 
 LOV
Current or Snapshot Data 
 
 LOV
Buckets 
 
 LOV
Organization Code 
hp.organization_code=:p_planned_organization_code
 LOV
Category Set 
 
 LOV
Category 
hp.category=:p_category
 LOV
Item Type 
hp.item_type=:p_item_type
 LOV
Exclude Item Type 
hp.item_type!=:p_exclude_item_type
 LOV
Item 
(hp.item=:p_planned_item_code or hp.end_assembly=:p_planned_item_code)
 LOV
Item From 
 
 LOV
Item To 
 
 LOV
Item Contains 
 
 LOV
Explode BOMs 
 
 LOV Oracle
Make or Buy 
 
 LOV
Planner 
hp.planner_code=:p_planner_code
 LOV
Buyer 
hp.buyer=:p_buyer
 LOV
Supply/Demand Source 
hp.source_type_text=:p_source_type
 LOV
Download
 
Related Blitz Reports
COPY OF: MRP Horizontal Plan MRP Pegging MRP Item Forecast MSC Horizontal Plan MSC Plan Orders INV Item Supply/Demand CAC Intercompany SO Price List vs. Item Cost Comparison MSC Vertical Plan Supply Chain Hub - MRP Release Diagnostics
Blitz Report™

Blitz Report™ provides multiple benefits: