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.

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
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 /*+ push_pred(mea) */
  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 distinct
      mea.compile_designator
    , bset.organization_id
    , bset.component_item_id inventory_item_id
    , bset.top_item_id end_assembly_id
    , msiv.concatenated_segments end_assembly
    , msiv.description           end_assembly_desc 
   from   
      bom_small_expl_temp      bset   
    , mtl_system_items_vl      msiv
    , mrp_end_assemblies       mea
   where
       bset.organization_id     = msiv.organization_id
   and bset.top_item_id         = msiv.inventory_item_id 
   and bset.component_item_id  != bset.top_item_id
   and mea.compile_designator   = xxen_mrp_horizplan.get_compile_designator
   and mea.organization_id      = bset.organization_id
   and mea.end_assembly_id      = bset.top_item_id
   and mea.inventory_item_id    = bset.component_item_id
  ) 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
   
Blitz Report™

Blitz Report™ provides multiple benefits: