MRP Horizontal Plan

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
Blitz Report™