MSC Plan Order Upload

Description
Categories: Enginatics
Repository: Github
Report: MSC Plan Order Upload
Description: ASCP: Upload to action Plan Order recommendations.

This upload can be used to either select for release or release Plan Order Recommendations. Additionally, it allows the user to amend the recommended Implement Date and/or Quantity.

In the generated Excel the user can amend the following columns:
- Implement Date
- Implement Quantit ... 
Report: MSC Plan Order Upload
Description: ASCP: Upload to action Plan Order recommendations.

This upload can be used to either select for release or release Plan Order Recommendations. Additionally, it allows the user to amend the recommended Implement Date and/or Quantity.

In the generated Excel the user can amend the following columns:
- Implement Date
- Implement Quantity
- Update Release Status

Update Release Status can be either
- Select for Release
- Release the Order

This is determined by the Report Parameter: Allow Release in Upload

If left null, then the user can only “Select for Release”
If set to Yes, then the user can only select “Release the Order”

‘Select for Release’ will update the implement date and quantity and flag the Plan Order for Release.
‘Release the Order’ will update the implement date and quantity and release the Plan Order

For Plan Orders not yet selected for release, to amend the implement date and/or implement quantity, the plan order must also be either selected for release or released.

If a plan order is already selected for release, then the user can amend the implement date and/or quantity in the generated excel without specifying a value in the Update Release Status column.

The upload is currently restricted to amending and releasing Planned Orders only.

However, it does also support amending and releasing Work Order, Purchase Order, Purchase Requisition Reschedule In/Reschedule Out recommendations.

This additional functionality can be enabled via the Order Type and Action report parameters which are currently hidden from display.

   more
Run MSC Plan Order Upload and other Oracle EBS reports with Blitz Report™ on our demo environment
select
mov.*
from
(
select
 null                                            processing_action,
 null                                            processing_status,
 null                                            processing_message,
 null                                            process_request_id,
 nvl(:p_instance_code,mai.instance_code)         instance,
 nvl(:p_plan_name,mp.compile_designator)         plan,
 mpo.organization_code                           organization,
 msi.planner_code                                planner,
 msi.buyer_name                                  buyer_name,
 --
 msc_get_name.lookup_meaning ('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) make_buy,
 msi.item_name item,
 msi.description item_description,
 nvl(:p_category_set_name,(select mcs.category_set_name from msc_category_sets mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) category_set,
 mic.category_name category,
 (select distinct
    max(mss.safety_stock_quantity) keep (dense_rank last order by mss.period_start_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock
  from
   msc_safety_stocks mss
  where
      mss.sr_instance_id     = mov.sr_instance_id
  and mss.plan_id            = mov.plan_id
  and mss.organization_id    = mov.organization_id
  and mss.inventory_item_id  = mov.inventory_item_id
  and mss.period_start_date <= sysdate
 ) safety_stock,
 --
 xxen_util.meaning('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand,
 decode(mp.plan_type
       , 8,decode(mov.order_type
                 , 1, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                 , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',53))
                 ,51, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                    , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
                 )
       ,msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
       ) order_type,
 msc_get_name.supply_order_number (mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number,
 to_char(trunc(mov.need_by_date),'DD-Mon-YYYY')  need_by_date,
 to_char(trunc(mov.promised_date),'DD-Mon-YYYY')  promise_date,
 to_char(trunc(mov.old_schedule_date),'DD-Mon-YYYY')  old_due_date,
 to_char(trunc(mov.new_schedule_date),'DD-Mon-YYYY')  suggested_due_date,
 nvl(mov.daily_rate,mov.new_order_quantity) quantity,
 msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id) action,
 --
 to_char(nvl(mov.implement_date,nvl2(:p_default_impl_cols,
      xxen_msc_rel_plan_api.implement_date
      ( mov.sr_instance_id
      , mov.plan_id
      , mp.plan_type
      , mov.organization_id
      , 'MSC_SUPPLIES'
      , mov.transaction_id
      , mov.order_type
      , -- implement_as
        nvl(mov.implement_as
           ,xxen_msc_rel_plan_api.implement_as
            ( mov.sr_instance_id
            , mp.plan_type
            , 'MSC_SUPPLIES'
            , mov.order_type
            , mov.organization_id
            , msi.purchasing_enabled_flag
            , msi.planning_make_buy_code
            , msi.build_in_wip_flag
            , msi.repetitive_type
            , mov.source_sr_instance_id
            , mov.source_organization_id
            , mov.source_supplier_id
            , null -- dest_inst_id
            , null -- dest_org_id
            ))
      , mov.new_schedule_date
      , mov.last_unit_completion_date
      , mov.firm_date
      , mov.new_ship_date
      , null -- dmd_satisfied_date
     ),null)),'DD-Mon-YYYY')                      implement_date,
 nvl(nvl(mov.implement_daily_rate,mov.implement_quantity),nvl2(:p_default_impl_cols,
      xxen_msc_rel_plan_api.implement_qty_rate
      ( mov.sr_instance_id
      , mov.plan_id
      , 'MSC_SUPPLIES'
      , mov.transaction_id
      , mov.order_type
      , mov.disposition_status_type
      , mov.original_quantity
      , nvl(mov.daily_rate,mov.new_order_quantity)
      , mov.firm_quantity
      , mov.quantity_in_process
      , mov.implemented_quantity
     ),null))                                    implement_quantity,
 nvl(decode(mov.implement_as,null,null,msc_get_name.lookup_meaning('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as))
    ,nvl2(:p_default_impl_cols,
      xxen_util.meaning(xxen_msc_rel_plan_api.implement_as
      ( mov.sr_instance_id
      , mp.plan_type
      , 'MSC_SUPPLIES'
      , mov.order_type
      , mov.organization_id
      , msi.purchasing_enabled_flag
      , msi.planning_make_buy_code
      , msi.build_in_wip_flag
      , msi.repetitive_type
      , mov.source_sr_instance_id
      , mov.source_organization_id
      , mov.source_supplier_id
      , null -- dest_inst_id
      , null -- dest_org_id
      ),'MRP_WORKBENCH_IMPLEMENT_AS',700)
     ,null))                                     implement_as,
 null                                            update_release_status,
 --
 case
 when  mov.release_status = 1
 then  'Selected'
 when  mov.reschedule_flag = 1
 and   nvl(mov.implement_daily_rate,mov.implement_quantity) = 0
 then  'Cancelled'
 when  mov.reschedule_flag = 1
 then  'Recheduled'
 when  mov.implemented_quantity is not null
 then  'Released'
 else  null
 end                                             released_status,
 mov.implemented_quantity                        implemented_quantity,
 --
 decode(mov.firm_planned_type, 1, 'Y',null)      firm,
 to_char(trunc(mov.firm_date),'DD-Mon-YYYY')     firm_date,
 mov.firm_quantity                               firm_quantity,
 --
 msc_get_name.org_code(mov.source_organization_id,mov.source_sr_instance_id) source_organization,
 msc_get_name.supplier(mov.supplier_id) supplier,
 msc_get_name.supplier_site(mov.supplier_site_id) supplier_site,
 --
 mov.reschedule_days                             reschedule_days,
 decode(mp.plan_type, 5, to_number(null), mov.schedule_priority) order_priority,
 --
 'SUPPLY' supply_demand_code,
 mov.transaction_id,
 mov.sr_instance_id,
 mov.plan_id,
 mov.organization_id,
 mov.inventory_item_id,
 mic.category_set_id category_set_id,
 mov.release_status
from
 msc_supplies           mov,
 msc_apps_instances     mai,
 msc_plans              mp,
 msc_plan_organizations mpo,
 msc_system_items       msi,
 msc_item_categories    mic
where
    mov.sr_instance_id              = mai.instance_id
and mov.plan_id                     = mp.plan_id
and mov.sr_instance_id              = mpo.sr_instance_id
and mov.plan_id                     = mpo.plan_id
and mov.organization_id             = mpo.organization_id
and mov.sr_instance_id              = msi.sr_instance_id
and mov.plan_id                     = msi.plan_id
and mov.organization_id             = msi.organization_id
and mov.inventory_item_id           = msi.inventory_item_id
and mov.sr_instance_id              =  mic.sr_instance_id
and mov.inventory_item_id           = mic.inventory_item_id
and mov.organization_id             = mic.organization_id
and mic.category_set_id             = nvl(:p_category_set_id,(select mcs.category_set_id from msc_category_sets mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1))
 
and nvl(nvl(mov.daily_rate,mov.new_order_quantity),0) != 0
and nvl(mov.implemented_quantity,0) = 0
and (   xxen_util.lookup_code(msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id)
                             ,'MRP_ACTIONS',700) in (2,3,4) -- Reschedule In, Reschedule Out, Release
     or mov.release_status = 1
    )
and 1=1
) mov
where
2=2
--
&not_use_first_block
&report_table_select &report_table_name &report_table_where_clause &success_records1 &success_records2
&processed_run
--
order by
 instance,
 plan,
 organization,
 planner,
 item,
 suggested_due_date,
 order_type,
 order_number
Parameter Name SQL text Validation
Planning Instance
 
LOV
Plan
 
LOV
Organization
mov.organization_id in (select mpo.organization_id from msc_plan_organizations  mpo where mpo.organization_code=:p_organization_code and mpo.sr_instance_id = :p_instance_id and mpo.plan_id = :p_plan_id)
LOV
Source Organiziation
mov.source_organization_id in (select mpo.organization_id from msc_plan_organizations  mpo where mpo.organization_code=:p_organization_code and mpo.sr_instance_id = :p_instance_id and mpo.plan_id = :p_plan_id)
LOV
Category Set
:p_category_set_name=:p_category_set_name
LOV
Category
mic.category_name = :p_category
LOV
Item
msi.item_name=:p_item
LOV
Planner
msi.planner_code = :p_planner
LOV
Buyer
msi.buyer_name = :p_buyer
LOV
Make/Buy
msc_get_name.lookup_meaning ('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) = :p_makeorbuy
LOV
Selected for Release
mov.release_status = decode(:p_release_status,'Y',1,2)
LOV Oracle
Suggested Due From
mov.new_schedule_date >= :p_sugg_due_date_fr
Date
Suggested Due To
mov.new_schedule_date<:p_sugg_due_date_to+1
Date
Allow Release in Upload
nvl(:p_allow_release,'N')=nvl(:p_allow_release,'N')
LOV
Blitz Report™