WIP Discrete Job Shortage

Description

Categories: Enginatics
Repository: Github

WIP report that lists discrete job Open Requirements and/or Shortages.

Report Modes
==========
The report can be run in the following modes:
1. Open Requirements - Reports all Open Requirements
2. Shortage based on Net QOH - Reports requirements with shortages against Nettable Quantity Onhand
3. Shortage based on QOH - Reports requirements with shortages against Total Quanti ... more

WIP Discrete Job Shortage and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

with wip_detail as
  ( select
      mp.organization_code                           organization_code
    , wdj.organization_id                            organization_id
    , we.wip_entity_name                             job
    , we.wip_entity_id                               job_id
    , we.description                                 job_desc
    , trunc(wdj.scheduled_start_date)                job_scheduled_start
    , trunc(wdj.scheduled_completion_date)           job_scheduled_complete
    , trunc(wdj.date_released)                       job_released_date
    , trunc(wdj.date_completed)                      job_completed_date
    , trunc(wdj.date_closed)                         job_closed_date
    , wdj.start_quantity                             job_start_qty
    , xxen_util.meaning(wdj.job_type
                       ,'WIP_DISCRETE_JOB',700)      job_type
    , xxen_util.meaning(wdj.status_type
                       ,'WIP_JOB_STATUS',700)        job_status
    , ppa.segment1                                   project
    , mso.segment1                                   sales_order_reservation
    , wsg.schedule_group_name                        schedule_group_name
    , wdj.build_sequence                             build_sequence
    , wro.operation_seq_num                          operation_seq_num
    , wl.line_code                                   wip_line_code
    , msiv1.concatenated_segments                    assembly
    , msiv1.description                              assembly_desc
    , xxen_util.meaning(msiv1.item_type
                       ,'ITEM_TYPE',3)               assembly_type
    , (select
         mck.concatenated_segments
       from
         mtl_default_category_sets mdcs
       , mtl_item_categories       mic
       , mtl_categories_kfv        mck
       where
           mdcs.functional_area_id = 5
       and mic.category_set_id     = mdcs.category_set_id
       and mic.organization_id     = wdj.organization_id
       and mic.inventory_item_id   = wdj.primary_item_id
       and mck.category_id         = mic.category_id
       and rownum <= 1
      )                                              assembly_costing_category
    , msiv1.inventory_item_id                        assembly_id
    , trunc(wro.date_required)                       date_required
    , bd.department_code                             department
    , bd.description                                 department_desc
    , bcb.item_num                                   component_seq_num
    , msiv2.concatenated_segments                    component
    , msiv2.description                              component_desc
    , xxen_util.meaning(msiv2.item_type
                       ,'ITEM_TYPE',3)               component_type
    , msiv2.inventory_item_id                        component_id
    , xxen_util.meaning(msiv2.planning_make_buy_code
                       ,'MTL_PLANNING_MAKE_BUY',700) make_or_buy
    , xxen_util.meaning(wro.wip_supply_type
                       ,'WIP_SUPPLY',700)            wip_supply_type
    , xxen_util.meaning(wro.mrp_net_flag
                       ,'SYS_YES_NO',700)            mrp_net_flag
    , msiv2.planner_code                             planner
    , ppx.full_name                                  buyer
    , wro.supply_subinventory                        supply_subinventory
    , milk.concatenated_segments                     supply_locator
    , milk.inventory_location_id                     locator_id
    --
    , msiv2.primary_uom_code                         uom
    , wro.quantity_per_assembly                      wro_qty_per_assembly
    , wro.required_quantity                          wro_qty_required
    , wro.quantity_issued                            wro_qty_issued
    , greatest(wro.required_quantity - wro.quantity_issued,0)
                                                     wro_qty_open
    --
    , nvl(miqv1.quantity,0)                          total_qty
    , nvl(miqv1.nettable_quantity,0)                 net_total_qty
    , nvl(miqv2.quantity,0)                          subloc_total_qty
    from
      mtl_parameters              mp
    , wip_discrete_jobs           wdj
    , wip_entities                we
    , wip_lines                   wl
    , wip_requirement_operations  wro
    , wip_schedule_groups         wsg
    , bom_departments             bd
    , bom_components_b            bcb
    --
    , mtl_system_items_vl         msiv1
    , mtl_system_items_vl         msiv2
    --
    , ( select
          miqv.organization_id
        , miqv.inventory_item_id
        , nvl(sum(miqv.quantity),0) quantity
        , nvl(sum(miqv.quantity * decode(msi.availability_type,1,1,0))
             ,0)                    nettable_quantity
        from
          mtl_item_quantities_view    miqv
        , mtl_secondary_inventories   msi
        where
            msi.organization_id            = miqv.organization_id
        and msi.secondary_inventory_name   = miqv.subinventory_code
        and nvl(msi.disable_date,sysdate) >= sysdate
        group by
          miqv.organization_id
        , miqv.inventory_item_id
      ) miqv1
    , ( select
          miqv.organization_id
        , miqv.inventory_item_id
        , miqv.subinventory_code
        , miqv.locator_id
        , nvl(sum(miqv.quantity),0) quantity
        from
          mtl_item_quantities_view    miqv
        , mtl_secondary_inventories   msi
        where
            msi.organization_id            = miqv.organization_id
        and msi.secondary_inventory_name   = miqv.subinventory_code
        and nvl(msi.disable_date,sysdate) >= sysdate
        group by
          miqv.organization_id
        , miqv.inventory_item_id
        , miqv.subinventory_code
        , miqv.locator_id
      ) miqv2
    , mtl_item_locations_kfv      milk
    , mtl_reservations            mr
    , mtl_sales_orders            mso
    , per_people_x                ppx
    , pa_projects_all             ppa
    where
        2=2
    and wdj.organization_id              = mp.organization_id
    and wdj.status_type                 in (1,3,4,6)
    and we.wip_entity_id                 = wdj.wip_entity_id
    and we.organization_id               = wdj.organization_id
    and we.entity_type                  in (1,5)
    and wl.line_id                   (+) = wdj.line_id
    and wl.organization_id           (+) = wdj.organization_id
    and wro.wip_entity_id                = wdj.wip_entity_id
    and wro.organization_id              = wdj.organization_id
    and wro.wip_supply_type             != 6
    and wro.required_quantity            > 0
    and wsg.organization_id          (+) = wdj.organization_id
    and wsg.schedule_group_id        (+) = wdj.schedule_group_id
    and wro.required_quantity            > wro.quantity_issued
    and bd.organization_id           (+) = wro.organization_id
    and bd.department_id             (+) = wro.department_id
    and bcb.component_sequence_id    (+) = wro.component_sequence_id
    --
    and msiv1.organization_id        (+) = wdj.organization_id
    and msiv1.inventory_item_id      (+) = wdj.primary_item_id
    and msiv2.organization_id            = wro.organization_id
    and msiv2.inventory_item_id          = wro.inventory_item_id
    --
    and miqv1.organization_id        (+) = wro.organization_id
    and miqv1.inventory_item_id      (+) = wro.inventory_item_id
    and miqv2.organization_id        (+) = wro.organization_id
    and miqv2.inventory_item_id      (+) = wro.inventory_item_id
    and miqv2.subinventory_code      (+) = nvl(wro.supply_subinventory,'@@@')
    and nvl(miqv2.locator_id,-1)         = nvl(wro.supply_locator_id,-1)
    --
    and milk.organization_id         (+) = wro.organization_id
    and milk.inventory_location_id   (+) = wro.supply_locator_id
    --
    and mr.supply_source_header_id   (+) = we.wip_entity_id
    and mr.organization_id           (+) = we.organization_id
    and mr.supply_source_type_id     (+) = 5 -- job or schedule
    and mr.demand_source_type_id     (+) = 2 -- sales order
    and mso.sales_order_id           (+) = mr.demand_source_header_id
    --
    and ppx.person_id                (+) = msiv2.buyer_id
    --
    and ppa.project_id               (+) = wdj.project_id
  )
, wip_total as
  ( select
      wro.organization_id
    , wro.inventory_item_id
    , nvl(wro.supply_subinventory,'@@@') supply_subinventory
    , nvl(wro.supply_locator_id,-1)      supply_locator_id
    , sum(wro.required_quantity)
       - sum(wro.quantity_issued)
       &lp_wip_reservations_api_call open_quantity
    from
      wip_requirement_operations wro
    where
        3=3
    and wro.required_quantity > 0
    and wro.required_quantity > wro.quantity_issued
    and (   (    wro.repetitive_schedule_id is null
             and exists
               ( select null
                 from   wip_discrete_jobs wdj
                 where  wdj.wip_entity_id = wro.wip_entity_id
                 and    wdj.organization_id = wro.organization_id
                 and    wdj.status_type in (1,3,4,6)
               )
            )
         or (    wro.repetitive_schedule_id is not null
             and exists
               ( select null
                 from   wip_repetitive_schedules wrs
                 where  wrs.organization_id = wro.organization_id
                 and    wrs.repetitive_schedule_id = wro.repetitive_schedule_id
                 and wrs.status_type in (1,3,4,6)
               )
            )
        )
    group by
      wro.organization_id
    , wro.inventory_item_id
    , nvl(wro.supply_subinventory,'@@@')
    , nvl(wro.supply_locator_id,-1)
  )
, mrp_supply as
  ( select
      mgr.disposition_id
    , mgr.organization_id
    , mgr.inventory_item_id
    , mosv.order_type_text           supply_type
    , mosv.buyer_name                supply_buyer_name
    , mosv.order_number              supply_order_number
    , mosv.source_vendor_name        supply_vendor_name
    , mosv.source_vendor_site_code   supply_vendor_site
    , trunc(mfp.supply_date)         supply_date
    , mfp.supply_quantity            supply_qty
    , mfp.allocated_quantity         allocated_qty
    from
      mrp_gross_requirements mgr
    , mrp_full_pegging       mfp
    , mrp_orders_sc_v        mosv
    where
        mgr.origination_type   in (2,3,17,25,26)
    and mfp.demand_id           = mgr.demand_id
    and mfp.compile_designator  = :p_compile_designator
    and mosv.transaction_id     = mfp.transaction_id
    and mosv.order_type         = mfp.supply_type
    and mosv.source_table       = 'MRP_RECOMMENDATIONS'
  )
--
-- Main Query Starts here
--
select
  wip_dtl.organization_code
, wip_dtl.job
, wip_dtl.job_desc
, wip_dtl.job_scheduled_start
, wip_dtl.job_scheduled_complete
, wip_dtl.job_released_date
, wip_dtl.job_completed_date
, wip_dtl.job_closed_date
, wip_dtl.job_start_qty
, wip_dtl.job_type
, wip_dtl.job_status
, wip_dtl.project
, wip_dtl.sales_order_reservation
, wip_dtl.schedule_group_name
, wip_dtl.build_sequence
, wip_dtl.operation_seq_num
, wip_dtl.wip_line_code
, wip_dtl.assembly
, wip_dtl.assembly_desc
, wip_dtl.assembly_type
, wip_dtl.assembly_costing_category
, wip_dtl.date_required
, wip_dtl.department
, wip_dtl.department_desc
, wip_dtl.component_seq_num
, wip_dtl.component
, wip_dtl.component_desc
, wip_dtl.component_type
, wip_dtl.make_or_buy
, wip_dtl.wip_supply_type
, wip_dtl.mrp_net_flag
, wip_dtl.planner
, wip_dtl.buyer
, wip_dtl.supply_subinventory
, wip_dtl.supply_locator
, wip_dtl.uom
, wip_dtl.wro_qty_per_assembly
, wip_dtl.wro_qty_issued
, wip_dtl.wro_qty_required
, wip_dtl.wro_qty_open
--
, wip_dtl.total_open_qty          component_open_qty
, wip_dtl.total_qty               component_onhand_qty
, wip_dtl.component_short_qty
, wip_dtl.date_req_cum_short_qty
--
, wip_dtl.net_total_qty           component_net_onhand_qty
, wip_dtl.component_net_short_qty
, wip_dtl.date_req_cum_net_short_qty
--
, case when wip_dtl.supply_subinventory is not null
  then    wip_dtl.subloc_total_open_qty
  else null
  end                             sub_loc_open_qty
, case when wip_dtl.supply_subinventory is not null
  then wip_dtl.subloc_total_qty
  else null
  end                             sub_loc_onhand_qty
, case when wip_dtl.supply_subinventory is not null
  then wip_dtl.sub_loc_short_qty
  else null
  end                             sub_loc_short_qty
, case when wip_dtl.supply_subinventory is not null
  then wip_dtl.date_req_cum_sub_loc_short_qty
  else null
  end                             date_req_cum_sub_loc_short_qty
-- mrp pegged suply
, mrp_supply.supply_type
, mrp_supply.supply_buyer_name
, mrp_supply.supply_order_number
, mrp_supply.supply_vendor_name
, mrp_supply.supply_vendor_site
, mrp_supply.supply_date
, mrp_supply.supply_qty
, mrp_supply.allocated_qty
from
  ( select
      wip_dtl2.*
    , wip_dtl2.total_open_qty - wip_dtl2.total_qty  component_short_qty
    , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row)
       - wip_dtl2.total_qty date_req_cum_short_qty
    , wip_dtl2.total_open_qty - wip_dtl2.net_total_qty component_net_short_qty
    , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row)
       - wip_dtl2.net_total_qty date_req_cum_net_short_qty
    , wip_dtl2.subloc_total_open_qty - wip_dtl2.subloc_total_qty sub_loc_short_qty
    , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.supply_subinventory,wip_dtl2.supply_locator,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row)
       - wip_dtl2.subloc_total_qty date_req_cum_sub_loc_short_qty
    from
      ( select
         wip_dtl3.*
       --
       , (select nvl(sum(wip_tot.open_quantity),0)
          from   wip_total wip_tot
          where  wip_tot.organization_id     = wip_dtl3.organization_id
          and    wip_tot.inventory_item_id   = wip_dtl3.component_id
         )   total_open_qty
       , (select nvl(sum(wip_tot.open_quantity),0)
          from   wip_total wip_tot
          where  wip_tot.organization_id     = wip_dtl3.organization_id
          and    wip_tot.inventory_item_id   = wip_dtl3.component_id
          and    wip_tot.supply_subinventory = wip_dtl3.supply_subinventory
          and    wip_tot.supply_locator_id   = nvl(wip_dtl3.locator_id,-1)
         )   subloc_total_open_qty
        from
          wip_detail   wip_dtl3
      ) wip_dtl2
    )           wip_dtl
, mrp_supply
where
    mrp_supply.disposition_id    (+) = wip_dtl.job_id
and mrp_supply.organization_id   (+) = wip_dtl.organization_id
and mrp_supply.inventory_item_id (+) = wip_dtl.component_id
and 1=1
order by
  wip_dtl.organization_code
, wip_dtl.job
, wip_dtl.operation_seq_num
, wip_dtl.component

with wip_detail as ( select mp.organization_code organization_code , wdj.organization_id organization_id , we.wip_entity_name job , we.wip_entity_id job_id , we.description job_desc , trunc(wdj.scheduled_start_date) job_scheduled_start , trunc(wdj.scheduled_completion_date) job_scheduled_complete , trunc(wdj.date_released) job_released_date , trunc(wdj.date_completed) job_completed_date , trunc(wdj.date_closed) job_closed_date , wdj.start_quantity job_start_qty , xxen_util.meaning(wdj.job_type ,'WIP_DISCRETE_JOB',700) job_type , xxen_util.meaning(wdj.status_type ,'WIP_JOB_STATUS',700) job_status , ppa.segment1 project , mso.segment1 sales_order_reservation , wsg.schedule_group_name schedule_group_name , wdj.build_sequence build_sequence , wro.operation_seq_num operation_seq_num , wl.line_code wip_line_code , msiv1.concatenated_segments assembly , msiv1.description assembly_desc , xxen_util.meaning(msiv1.item_type ,'ITEM_TYPE',3) assembly_type , (select mck.concatenated_segments from mtl_default_category_sets mdcs , mtl_item_categories mic , mtl_categories_kfv mck where mdcs.functional_area_id = 5 and mic.category_set_id = mdcs.category_set_id and mic.organization_id = wdj.organization_id and mic.inventory_item_id = wdj.primary_item_id and mck.category_id = mic.category_id and rownum <= 1 ) assembly_costing_category , msiv1.inventory_item_id assembly_id , trunc(wro.date_required) date_required , bd.department_code department , bd.description department_desc , bcb.item_num component_seq_num , msiv2.concatenated_segments component , msiv2.description component_desc , xxen_util.meaning(msiv2.item_type ,'ITEM_TYPE',3) component_type , msiv2.inventory_item_id component_id , xxen_util.meaning(msiv2.planning_make_buy_code ,'MTL_PLANNING_MAKE_BUY',700) make_or_buy , xxen_util.meaning(wro.wip_supply_type ,'WIP_SUPPLY',700) wip_supply_type , xxen_util.meaning(wro.mrp_net_flag ,'SYS_YES_NO',700) mrp_net_flag , msiv2.planner_code planner , ppx.full_name buyer , wro.supply_subinventory supply_subinventory , milk.concatenated_segments supply_locator , milk.inventory_location_id locator_id -- , msiv2.primary_uom_code uom , wro.quantity_per_assembly wro_qty_per_assembly , wro.required_quantity wro_qty_required , wro.quantity_issued wro_qty_issued , greatest(wro.required_quantity - wro.quantity_issued,0) wro_qty_open -- , nvl(miqv1.quantity,0) total_qty , nvl(miqv1.nettable_quantity,0) net_total_qty , nvl(miqv2.quantity,0) subloc_total_qty from mtl_parameters mp , wip_discrete_jobs wdj , wip_entities we , wip_lines wl , wip_requirement_operations wro , wip_schedule_groups wsg , bom_departments bd , bom_components_b bcb -- , mtl_system_items_vl msiv1 , mtl_system_items_vl msiv2 -- , ( select miqv.organization_id , miqv.inventory_item_id , nvl(sum(miqv.quantity),0) quantity , nvl(sum(miqv.quantity * decode(msi.availability_type,1,1,0)) ,0) nettable_quantity from mtl_item_quantities_view miqv , mtl_secondary_inventories msi where msi.organization_id = miqv.organization_id and msi.secondary_inventory_name = miqv.subinventory_code and nvl(msi.disable_date,sysdate) >= sysdate group by miqv.organization_id , miqv.inventory_item_id ) miqv1 , ( select miqv.organization_id , miqv.inventory_item_id , miqv.subinventory_code , miqv.locator_id , nvl(sum(miqv.quantity),0) quantity from mtl_item_quantities_view miqv , mtl_secondary_inventories msi where msi.organization_id = miqv.organization_id and msi.secondary_inventory_name = miqv.subinventory_code and nvl(msi.disable_date,sysdate) >= sysdate group by miqv.organization_id , miqv.inventory_item_id , miqv.subinventory_code , miqv.locator_id ) miqv2 , mtl_item_locations_kfv milk , mtl_reservations mr , mtl_sales_orders mso , per_people_x ppx , pa_projects_all ppa where 2=2 and wdj.organization_id = mp.organization_id and wdj.status_type in (1,3,4,6) and we.wip_entity_id = wdj.wip_entity_id and we.organization_id = wdj.organization_id and we.entity_type in (1,5) and wl.line_id (+) = wdj.line_id and wl.organization_id (+) = wdj.organization_id and wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id and wro.wip_supply_type != 6 and wro.required_quantity > 0 and wsg.organization_id (+) = wdj.organization_id and wsg.schedule_group_id (+) = wdj.schedule_group_id and wro.required_quantity > wro.quantity_issued and bd.organization_id (+) = wro.organization_id and bd.department_id (+) = wro.department_id and bcb.component_sequence_id (+) = wro.component_sequence_id -- and msiv1.organization_id (+) = wdj.organization_id and msiv1.inventory_item_id (+) = wdj.primary_item_id and msiv2.organization_id = wro.organization_id and msiv2.inventory_item_id = wro.inventory_item_id -- and miqv1.organization_id (+) = wro.organization_id and miqv1.inventory_item_id (+) = wro.inventory_item_id and miqv2.organization_id (+) = wro.organization_id and miqv2.inventory_item_id (+) = wro.inventory_item_id and miqv2.subinventory_code (+) = nvl(wro.supply_subinventory,'@@@') and nvl(miqv2.locator_id,-1) = nvl(wro.supply_locator_id,-1) -- and milk.organization_id (+) = wro.organization_id and milk.inventory_location_id (+) = wro.supply_locator_id -- and mr.supply_source_header_id (+) = we.wip_entity_id and mr.organization_id (+) = we.organization_id and mr.supply_source_type_id (+) = 5 -- job or schedule and mr.demand_source_type_id (+) = 2 -- sales order and mso.sales_order_id (+) = mr.demand_source_header_id -- and ppx.person_id (+) = msiv2.buyer_id -- and ppa.project_id (+) = wdj.project_id ) , wip_total as ( select wro.organization_id , wro.inventory_item_id , nvl(wro.supply_subinventory,'@@@') supply_subinventory , nvl(wro.supply_locator_id,-1) supply_locator_id , sum(wro.required_quantity) - sum(wro.quantity_issued) &lp_wip_reservations_api_call open_quantity from wip_requirement_operations wro where 3=3 and wro.required_quantity > 0 and wro.required_quantity > wro.quantity_issued and ( ( wro.repetitive_schedule_id is null and exists ( select null from wip_discrete_jobs wdj where wdj.wip_entity_id = wro.wip_entity_id and wdj.organization_id = wro.organization_id and wdj.status_type in (1,3,4,6) ) ) or ( wro.repetitive_schedule_id is not null and exists ( select null from wip_repetitive_schedules wrs where wrs.organization_id = wro.organization_id and wrs.repetitive_schedule_id = wro.repetitive_schedule_id and wrs.status_type in (1,3,4,6) ) ) ) group by wro.organization_id , wro.inventory_item_id , nvl(wro.supply_subinventory,'@@@') , nvl(wro.supply_locator_id,-1) ) , mrp_supply as ( select mgr.disposition_id , mgr.organization_id , mgr.inventory_item_id , mosv.order_type_text supply_type , mosv.buyer_name supply_buyer_name , mosv.order_number supply_order_number , mosv.source_vendor_name supply_vendor_name , mosv.source_vendor_site_code supply_vendor_site , trunc(mfp.supply_date) supply_date , mfp.supply_quantity supply_qty , mfp.allocated_quantity allocated_qty from mrp_gross_requirements mgr , mrp_full_pegging mfp , mrp_orders_sc_v mosv where mgr.origination_type in (2,3,17,25,26) and mfp.demand_id = mgr.demand_id and mfp.compile_designator = :p_compile_designator and mosv.transaction_id = mfp.transaction_id and mosv.order_type = mfp.supply_type and mosv.source_table = 'MRP_RECOMMENDATIONS' ) -- -- Main Query Starts here -- select wip_dtl.organization_code , wip_dtl.job , wip_dtl.job_desc , wip_dtl.job_scheduled_start , wip_dtl.job_scheduled_complete , wip_dtl.job_released_date , wip_dtl.job_completed_date , wip_dtl.job_closed_date , wip_dtl.job_start_qty , wip_dtl.job_type , wip_dtl.job_status , wip_dtl.project , wip_dtl.sales_order_reservation , wip_dtl.schedule_group_name , wip_dtl.build_sequence , wip_dtl.operation_seq_num , wip_dtl.wip_line_code , wip_dtl.assembly , wip_dtl.assembly_desc , wip_dtl.assembly_type , wip_dtl.assembly_costing_category , wip_dtl.date_required , wip_dtl.department , wip_dtl.department_desc , wip_dtl.component_seq_num , wip_dtl.component , wip_dtl.component_desc , wip_dtl.component_type , wip_dtl.make_or_buy , wip_dtl.wip_supply_type , wip_dtl.mrp_net_flag , wip_dtl.planner , wip_dtl.buyer , wip_dtl.supply_subinventory , wip_dtl.supply_locator , wip_dtl.uom , wip_dtl.wro_qty_per_assembly , wip_dtl.wro_qty_issued , wip_dtl.wro_qty_required , wip_dtl.wro_qty_open -- , wip_dtl.total_open_qty component_open_qty , wip_dtl.total_qty component_onhand_qty , wip_dtl.component_short_qty , wip_dtl.date_req_cum_short_qty -- , wip_dtl.net_total_qty component_net_onhand_qty , wip_dtl.component_net_short_qty , wip_dtl.date_req_cum_net_short_qty -- , case when wip_dtl.supply_subinventory is not null then wip_dtl.subloc_total_open_qty else null end sub_loc_open_qty , case when wip_dtl.supply_subinventory is not null then wip_dtl.subloc_total_qty else null end sub_loc_onhand_qty , case when wip_dtl.supply_subinventory is not null then wip_dtl.sub_loc_short_qty else null end sub_loc_short_qty , case when wip_dtl.supply_subinventory is not null then wip_dtl.date_req_cum_sub_loc_short_qty else null end date_req_cum_sub_loc_short_qty -- mrp pegged suply , mrp_supply.supply_type , mrp_supply.supply_buyer_name , mrp_supply.supply_order_number , mrp_supply.supply_vendor_name , mrp_supply.supply_vendor_site , mrp_supply.supply_date , mrp_supply.supply_qty , mrp_supply.allocated_qty from ( select wip_dtl2.* , wip_dtl2.total_open_qty - wip_dtl2.total_qty component_short_qty , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row) - wip_dtl2.total_qty date_req_cum_short_qty , wip_dtl2.total_open_qty - wip_dtl2.net_total_qty component_net_short_qty , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row) - wip_dtl2.net_total_qty date_req_cum_net_short_qty , wip_dtl2.subloc_total_open_qty - wip_dtl2.subloc_total_qty sub_loc_short_qty , sum(wip_dtl2.wro_qty_open) over (partition by wip_dtl2.organization_code,wip_dtl2.supply_subinventory,wip_dtl2.supply_locator,wip_dtl2.component order by wip_dtl2.date_required,wip_dtl2.job rows between unbounded preceding and current row) - wip_dtl2.subloc_total_qty date_req_cum_sub_loc_short_qty from ( select wip_dtl3.* -- , (select nvl(sum(wip_tot.open_quantity),0) from wip_total wip_tot where wip_tot.organization_id = wip_dtl3.organization_id and wip_tot.inventory_item_id = wip_dtl3.component_id ) total_open_qty , (select nvl(sum(wip_tot.open_quantity),0) from wip_total wip_tot where wip_tot.organization_id = wip_dtl3.organization_id and wip_tot.inventory_item_id = wip_dtl3.component_id and wip_tot.supply_subinventory = wip_dtl3.supply_subinventory and wip_tot.supply_locator_id = nvl(wip_dtl3.locator_id,-1) ) subloc_total_open_qty from wip_detail wip_dtl3 ) wip_dtl2 ) wip_dtl , mrp_supply where mrp_supply.disposition_id (+) = wip_dtl.job_id and mrp_supply.organization_id (+) = wip_dtl.organization_id and mrp_supply.inventory_item_id (+) = wip_dtl.component_id and 1=1 order by wip_dtl.organization_code , wip_dtl.job , wip_dtl.operation_seq_num , wip_dtl.component

Parameter Name

SQL text

Validation

Organization Code

wip_dtl.organization_code=:p_organization_code

LOV

Report Mode

(wip_dtl.total_open_qty - wip_dtl.total_qty) > 0

LOV

Include Bulk

wro.wip_supply_type != 4

LOV Oracle

Include Supplier

wro.wip_supply_type != 5

LOV Oracle

Job From

wip_dtl.job>=:p_from_job

LOV

Job To

wip_dtl.job<=:p_from_job

LOV

Assembly From

wip_dtl.assembly>=:p_from_assembly

LOV

Assembly To

wip_dtl.assembly<=:p_to_assembly

LOV

Component From

wip_dtl.component>=:p_from_comp

LOV

Component To

wip_dtl.component<=:p_to_comp

LOV

Date Required From

wip_dtl.date_required>=:p_from_date_required

Date

Date Required To

wip_dtl.date_required<:p_to_date_required+1

Date

Department From

wip_dtl.department>=:p_from_dept

LOV

Department To

wip_dtl.department<=:p_to_dept

LOV

Planner From

wip_dtl.planner>=:p_from_planner

LOV

Planner To

wip_dtl.planner<=:p_to_planner

LOV

Buyer From

wip_dtl.buyer>=:p_from_buyer

LOV

Buyer To

wip_dtl.buyer<=:p_to_buyer

LOV

Make or Buy

wip_dtl.make_or_buy=xxen_util.meaning(:p_make_buy,'MTL_PLANNING_MAKE_BUY',700)

LOV Oracle

Schedule Group

wip_dtl.schedule_group_name=:p_schedule_group

LOV

Project

wip_dtl.project=:project

LOV

Requirement End Date

wro.date_required < :p_req_end_date + 1

Date

MRP Plan for Pegged Supply

LOV

Download

Related Blitz Reports

GMF GL Item Cost Detail BOM Bill of Material Structure XXE4ARW Excel4apps Inventory Items GW Items INV Inventory Items CST Period Close Subinventory Value PO Receiving Account Distribution BOM Bill of Material Loop Report GUI BOM Structure Report for Lead Times

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ