MSC Supply and Demand Orders

➜ Back to Library
Description
Categories: Toolkit - Operations
Columns: Plan Instance, Plan Name, Planner, Buyer, Order Type, Product, Item Description, Using Assembly, Quantity Rate, Released Qty ...
ASCP: Export Supply and Demand Orders from the Planners Workbench.
select 
 mai.instance_code                               planning_instance,
 mp.compile_designator                           plan_name,
 mov.planner_code                                planner,
 mov.buyer_name                                  buyer_name,
 mov.order_type_text                             order_type,
 mov.item_segments                               product,
 mov.description                                 item_description,
 mov.using_assembly_segments                     using_assembly,
 mov.quantity_rate                               quantity_rate,
 mov.implemented_quantity                        released_qty,
 trunc(mov.new_start_date)                       suggested_plan_date,
 trunc(mov.new_due_date)                         suggested_due_date,
 mov.organization_code                           destination_organization,
 mcs.category_set_name                           category_set,
 mov.category_name                               category,
 mov.source_organization_code                    source_organization,
 mov.supplier_name                               supplier,
 mov.action                                      action_required,
 msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',mov.planning_make_buy_code)  "Make/Buy",
 mov.intransit_lead_time                         intransit_lt,
 trunc(mov.promise_date)                         promised_arrival_date,
 trunc(mov.need_by_date)                         need_by_date,
 mov.order_number                                order_number,
 (select trunc(msi.planning_time_fence_date)
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               planning_time_fence_date,
 (select msi.fixed_lead_time
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               fixed_lt,
 (select msi.variable_lead_time
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               variable_lt,
 (select msi.preprocessing_lead_time
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               pre_processing_lt,
 (select msi.postprocessing_lead_time
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               post_processing_lt,
 (select msi.full_lead_time
  from   msc_system_items@A2M_DBLINK     msi
  where msi.sr_instance_id    = mov.sr_instance_id
    and msi.plan_id           = mov.plan_id
    and msi.organization_id   = mov.organization_id
    and msi.inventory_item_id = mov.inventory_item_id
 )                                               full_lt,
 mov.days_from_today                             days_from_today,
 mov.schedule_compression_days                   schedule_compression_days,
 mov.organization_code                           inventory_organization,
 mov.subinventory_code                           subinventory,
 decode(mov.release_status
       , 2, null, 1, 'Yes', mov.release_status)  for_release,
 decode(nvl(mov.implemented_quantity, -999)
       , -999, null, 'Released')                 released_status,
 mov.abc_class                                   abc_class,
 mov.supplier_site_code                          source_supplier_site_code,
 mov.reschedule_days                             reschedule_days,
 decode(mov.firm_planned_type
    , 2, null
    , 1, 'Yes'
       ,mov.firm_planned_type)                   firm,
 trunc(mov.firm_date)                            new_firm_date,
 mov.firm_quantity                               new_firm_quantity,
 mov.demand_priority                             order_priority,
 mov.last_update_date                            last_update_date
from 
 msc_apps_instances@A2M_DBLINK   mai,
 msc_plans@A2M_DBLINK            mp,
 msc_orders_v@A2M_DBLINK         mov,
 msc_category_sets@A2M_DBLINK    mcs 
where 
    mai.instance_id       = mp.sr_instance_id 
and mp.sr_instance_id     = mov.sr_instance_id
and mp.plan_id            = mov.plan_id
and mov.category_set_id   = mcs.category_set_id
and mai.instance_code = :p_instance_code
and mp.compile_designator = :p_plan_name
and mcs.category_set_name = :p_cat_set_name
and 1=1
order by 
 mai.instance_code,
 mp.compile_designator,
 mov.planner_code,
 mov.organization_code,
 mov.order_type_text,
 mov.order_number,
 mov.item_segments
Parameter Name SQL text Validation
Plan 
 
 LOV
Planning Instance 
 
 LOV
Make/Buy 
msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',mov.planning_make_buy_code) = :makeorbuy
 LOV
Buyer 
mov.buyer_name = :buyer
 LOV
Category Set 
 
 LOV
Category 
mov.category_name = :cat
 LOV
Destination Organization 
mov.organization_code = :org_code
 LOV
Source Organiziation 
mov.source_organization_code = :src_org
 LOV
Item 
mov.item_segments=:item
 LOV
Planner 
mov.planner_code = :planner
 LOV
Order Type 
mov.order_type_text =:order_type
 LOV
instance_id|plan_id|organization_id|inventory_item_id 
select distinct
 'mai.instance_id = ' || xrrpv.value
from
(select substr(xrrpv.value,1,instr(xrrpv.value,'|',1,1)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs                      xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
           xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
   and rownum             <= 1
  ) xrrpv
) xrrpv
Suggested Start Date From 
(mov.new_start_date >= :sugg_start_date_from or mov.new_start_date is null)
 Date
Suggested Start Date To 
(mov.new_start_date<:sugg_start_date_to+1 or mov.new_start_date is null)
 Date
instance_id|plan_id|organization_id|inventory_item_id 
select distinct
 'mp.plan_id = ' || xrrpv.value
from
(select substr(xrrpv.value,instr(xrrpv.value,'|',1,1)+1,instr(xrrpv.value,'|',1,2)-instr(xrrpv.value,'|',1,1)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
   and rownum             <= 1
  ) xrrpv
) xrrpv
instance_id|plan_id|organization_id|inventory_item_id 
select distinct
 'mov.organization_id in (' || listagg(xrrpv.value,',') within group (order by xrrpv.value) || ')'
from
(select distinct substr(xrrpv.value,instr(xrrpv.value,'|',1,2)+1,instr(xrrpv.value,'|',1,3)-instr(xrrpv.value,'|',1,2)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
  ) xrrpv
) xrrpv
instance_id|plan_id|organization_id|inventory_item_id 
select distinct
 'mov.organization_id || ''|'' || mov.inventory_item_id in (' || listagg(xrrpv.value,',') within group (order by xrrpv.value) || ')'
from
(select distinct '''' || substr(xrrpv.value,instr(xrrpv.value,'|',1,2)+1,20) || ''''  value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
  ) xrrpv
) xrrpv
Download
 
Related Blitz Reports
MSC Vertical Plan MSC Pegging MSC Horizontal Plan MSC Exceptions CAC Inventory and Intransit Value (Period-End) CAC ICP PII Inventory and Intransit Value (Period-End) MSC Sourcing Rules (Plan Side) PO Purchase Requisition Interface PO RCV Interface
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: