MSC Plan Order Upload

Description

MSC Plan Order Upload actions Oracle ASCP (Advanced Supply Chain Planning) plan-order recommendations from Excel – selecting them for release or releasing them, and optionally amending the implement date and implement quantity. It only updates existing recommendations; it never creates or deletes plan data.

When to use it

Release ASCP planned-order recommendations in bulk instead of one by one in the Planner Workbench.
Mass “select for release” many planned orders for later execution.
Apply Reschedule In / Reschedule Out recommendations on existing supply orders.
Override the suggested implement date and/or quantity before releasing.
Attach comments to planned orders while actioning them.

Before you start

Blitz Report is installed and you are signed in with access to the planning organizations.
An ASCP plan exists and is populated on a reachable planning instance.
Your user is defined on the planning instance with a responsibility that allows releasing planned orders there.
The recommendations are actionable (Reschedule In/Out or Release) and not already implemented.

Choose a template

Template	Use it for
Plan Order Recommendations (default)	Download the recommendations, then tick the rows you want to action.
Plan Order Recommendations – Automatic Release	Every downloaded recommendation arrives pre-flagged for automatic release (pre-sets Auto Populate Release Status = Yes and Upload Mode = Release the Order), so it can be saved and uploaded without hand-picking rows. Check its Planning Instance default before downloading.

Step 1 – Choose a template, set the parameters and download

In Blitz Report, open MSC Plan Order Upload, choose your template, and set the parameters:

Parameter	Purpose
Upload Mode	Select for Release (default) or Release the Order.
Auto Populate Release Status	Yes pre-fills the action column on every downloaded recommendation.
Planning Instance	The ASCP source instance (required).
Plan	The plan whose recommendations are downloaded (required).
Organization / Item / Category / Order Type / Action / dates and other filters	Restrict which recommendations are downloaded.

Run the upload to download and open the Excel file. Most columns are read-only and carry the recommendation details for identification.

Step 2 – Action the recommendations

For each row you want to action, set the Update Release Status (Select for Release or Release the Order). Optionally amend the Implement Date and Implement Quantity. For a plan order not yet selected for release, you must set Update Release Status and supply both Implement Date and Implement Quantity; for an order already selected, you can amend the date/quantity directly.

Step 3 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 4 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which selects and/or releases each actioned recommendation. When it finishes, a result report opens showing each row as success or error; released rows carry the release request id.

What’s produced

Plan orders selected for release and/or released, with any amended implement date/quantity.
A result report listing every row with a status (success or error) and a message.

Common questions

Does this create new planned orders?
No. It only actions recommendations that already exist in the plan; non-update rows are rejected.

What’s the difference between “Select for Release” and “Release the Order”?
Select for Release marks the order as selected and applies your implement date/quantity but does not execute it; Release the Order does that and then runs the release process (returning a request id).

I only want to change the Implement Date/Quantity – what do I enter?
If the order is already selected for release, just change the date/quantity. If it is not yet selected, also set Update Release Status, and supply both Implement Date and Implement Quantity.

Why is most of the sheet read-only?
Only Update Release Status, Implement Date and Implement Quantity are editable; everything else is downloaded for identification and reference.

How do I release everything without ticking rows?
Use the Automatic Release template, or set Auto Populate Release Status = Yes.

Troubleshooting

Message	Cause	What to do
User is not defined / does not have access to release planned orders in the ASCP instance	The EBS user has no planning-instance user or no release privilege there.	Have the planning admin set up the user / grant a responsibility with release access on the ASCP instance.
Both Implement Date and Implement Quantity must be specified to select or release a Plan Order	Selecting/releasing a not-yet-selected order without both values.	Fill in both Implement Date and Implement Quantity.
Update Release Status must be specified to update the Implement Date/Quantity	Changed implement date/quantity on a not-yet-selected order without choosing an action.	Set Update Release Status to Select for Release or Release the Order.
Implement Date cannot be in the past / Release Quantity must be >= 0	Invalid implement date or negative quantity.	Use today or a future date; enter a non-negative quantity.
Plan Order has already been implemented / the recommendation has changed	The plan was re-run or the order actioned since download, so the row is stale.	Re-download a fresh template and re-apply your changes.

Copy

with
q_selected_items as
(select /*+ materialize */distinct
  msi.sr_instance_id,
  msi.plan_id,
  msi.organization_id,
  msi.inventory_item_id
 from
  msc_system_items&a2m_dblink       msi,
  msc_item_categories&a2m_dblink    mic
 where
     msi.sr_instance_id      = mic.sr_instance_id
 and msi.organization_id     = mic.organization_id
 and msi.inventory_item_id   = mic.inventory_item_id
 and mic.category_set_id     = nvl(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where mcs.sr_instance_id = msi.sr_instance_id and mcs.default_flag = 1))
 and 1=1
),
q_sub_components as
(select distinct
  mbc.sr_instance_id,
  xxen_msc.get_plan_id&a2m_dblink(:p_instance_code,:p_plan_name) plan_id,
  mbc.organization_id,
  mbc.inventory_item_id
 from
  msc_boms&a2m_dblink mb,
  msc_bom_components&a2m_dblink mbc
 where
     mb.alternate_bom_designator is null
 and mb.sr_instance_id = mbc.sr_instance_id
 and mb.plan_id = mbc.plan_id
 and mb.organization_id = mbc.organization_id
 and mb.bill_sequence_id = mbc.bill_sequence_id
 connect by nocycle
     prior mbc.sr_instance_id = mb.sr_instance_id
 and prior mbc.plan_id = mb.plan_id
 and prior mbc.organization_id = mb.organization_id
 and prior mbc.inventory_item_id = mb.assembly_item_id
 start with
 (mb.sr_instance_id,mb.plan_id,mb.organization_id,mb.assembly_item_id) in
 (select
  msi.sr_instance_id,
  -1,
  msi.organization_id,
  msi.inventory_item_id
  from
  q_selected_items msi
  where
  nvl(:p_explode_boms,decode(nvl(:p_called_from_schub,'N'),'Y',decode(nvl(xxen_api.user_preference('BOM_EXPLOSION_DEFAULT','XXEN_SCHUB'),'expanded'),'expanded','Y',null),null)) = 'Y'
 )
),
q_all_items as
(select
  msi.sr_instance_id,
  msi.plan_id,
  msi.organization_id,
  msi.inventory_item_id
 from
  q_selected_items msi
 union
 select
  msi.sr_instance_id,
  msi.plan_id,
  msi.organization_id,
  msi.inventory_item_id
 from
  q_sub_components msi
)
--
-- Main Query
select /*+ driving_site(mov) */
mov.*,
-- custom attributes from lookup XXEN_MSC_CUSTOM_ATTRIBUTES
&lp_custom_attributes
-- item dff attributes
&lp_item_dff_cols
'.' last_col_flag
from
(
select
case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.action_meaning&a2m_dblink(xxen_upload.action_update&a2m_dblink) else null end action_,
case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.status_meaning&a2m_dblink(xxen_upload.status_new&a2m_dblink) else null end status_,
case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_util.description&a2m_dblink('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_,
null                                            request_id_,
null modified_columns_,
nvl(:p_instance_code,mai.instance_code)         instance,
nvl(:p_plan_name,mp.compile_designator)         plan,
mpo.organization_code                           organization,
decode(mov.project_id, null, null, msc_get_name.project&a2m_dblink(mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) project_number,
decode(mov.task_id, null, null, msc_get_name.task&a2m_dblink(mov.task_id, mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) task_number,
msi.planner_code                                planner,
msi.buyer_name                                  buyer_name,
--
msc_get_name.lookup_meaning&a2m_dblink('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&a2m_dblink mcs where mcs.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&a2m_dblink 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&a2m_dblink('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand,
decode(mp.plan_type
      , 8,decode(mov.order_type
                , 1, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',53))
                ,51, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                   , msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
                )
      ,msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
      ) order_type,
msc_get_name.supply_order_number&a2m_dblink(mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number,
trunc(mov.need_by_date)  need_by_date,
trunc(mov.promised_date)  promise_date,
trunc(mov.old_schedule_date)  old_due_date,
trunc(mov.new_schedule_date)  suggested_due_date,
nvl(mov.daily_rate,mov.new_order_quantity) quantity,
msc_get_name.action&a2m_dblink('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,
--
nvl(decode(mov.implement_as,null,null,msc_get_name.lookup_meaning&a2m_dblink('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as))
   ,xxen_util.meaning&a2m_dblink(xxen_msc_rel_plan_api.implement_as&a2m_dblink
     ( 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)
   )                                     implement_as,
nvl(mov.implement_date,
     xxen_msc_rel_plan_api.implement_date&a2m_dblink
     ( 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&a2m_dblink
           ( 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
    ))                      implement_date,
nvl(nvl(mov.implement_daily_rate,mov.implement_quantity),
     xxen_msc_rel_plan_api.implement_qty_rate&a2m_dblink
     ( 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
    ))                                    implement_quantity,
case when :p_autopopulate_release_status is not null then :p_upload_mode else null end 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,
--
msc_get_name.get_order_comments&a2m_dblink(mov.plan_id, 'SUPPLY', mov.transaction_id) comments,
decode(mov.firm_planned_type, 1, 'Y',null)      firm,
trunc(mov.firm_date)                            firm_date,
mov.firm_quantity                               firm_quantity,
--
msc_get_name.org_code&a2m_dblink(mov.source_organization_id,mov.source_sr_instance_id) source_organization,
msc_get_name.supplier&a2m_dblink(mov.supplier_id) supplier,
msc_get_name.supplier_site&a2m_dblink(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,
msi.sr_inventory_item_id,
mic.category_set_id category_set_id,
nvl(mov.release_status,2) release_status
from
msc_supplies&a2m_dblink           mov,
msc_apps_instances&a2m_dblink     mai,
msc_plans&a2m_dblink              mp,
msc_plan_organizations&a2m_dblink mpo,
msc_system_items&a2m_dblink       msi,
msc_item_categories&a2m_dblink    mic
where
2=2
and 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(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1))
and (mov.sr_instance_id,mov.plan_id,mov.organization_id,mov.inventory_item_id) in
    (select
     qai.sr_instance_id,
     qai.plan_id,
     qai.organization_id,
     qai.inventory_item_id
     from
     q_all_items qai
    )
and nvl(nvl(mov.daily_rate,mov.new_order_quantity),0) != 0
and nvl(mov.implemented_quantity,0) = 0
and (   xxen_util.lookup_code&a2m_dblink(msc_get_name.action&a2m_dblink('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 (nvl(:p_called_from_schub,'N') = 'N' or mov.order_type = 5)
and nvl('&m2a_dblink','?') = nvl('&m2a_dblink','?')
and nvl(:p_show_item_dff,'?') = nvl(:p_show_item_dff,'?')
) mov
where
3=3

with q_selected_items as (select /*+ materialize */distinct msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic where msi.sr_instance_id = mic.sr_instance_id and msi.organization_id = mic.organization_id and msi.inventory_item_id = mic.inventory_item_id and mic.category_set_id = nvl(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where mcs.sr_instance_id = msi.sr_instance_id and mcs.default_flag = 1)) and 1=1 ), q_sub_components as (select distinct mbc.sr_instance_id, xxen_msc.get_plan_id&a2m_dblink(:p_instance_code,:p_plan_name) plan_id, mbc.organization_id, mbc.inventory_item_id from msc_boms&a2m_dblink mb, msc_bom_components&a2m_dblink mbc where mb.alternate_bom_designator is null and mb.sr_instance_id = mbc.sr_instance_id and mb.plan_id = mbc.plan_id and mb.organization_id = mbc.organization_id and mb.bill_sequence_id = mbc.bill_sequence_id connect by nocycle prior mbc.sr_instance_id = mb.sr_instance_id and prior mbc.plan_id = mb.plan_id and prior mbc.organization_id = mb.organization_id and prior mbc.inventory_item_id = mb.assembly_item_id start with (mb.sr_instance_id,mb.plan_id,mb.organization_id,mb.assembly_item_id) in (select msi.sr_instance_id, -1, msi.organization_id, msi.inventory_item_id from q_selected_items msi where nvl(:p_explode_boms,decode(nvl(:p_called_from_schub,'N'),'Y',decode(nvl(xxen_api.user_preference('BOM_EXPLOSION_DEFAULT','XXEN_SCHUB'),'expanded'),'expanded','Y',null),null)) = 'Y' ) ), q_all_items as (select msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from q_selected_items msi union select msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from q_sub_components msi ) -- -- Main Query select /*+ driving_site(mov) */ mov.*, -- custom attributes from lookup XXEN_MSC_CUSTOM_ATTRIBUTES &lp_custom_attributes -- item dff attributes &lp_item_dff_cols '.' last_col_flag from ( select case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.action_meaning&a2m_dblink(xxen_upload.action_update&a2m_dblink) else null end action_, case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.status_meaning&a2m_dblink(xxen_upload.status_new&a2m_dblink) else null end status_, case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_util.description&a2m_dblink('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_, null request_id_, null modified_columns_, nvl(:p_instance_code,mai.instance_code) instance, nvl(:p_plan_name,mp.compile_designator) plan, mpo.organization_code organization, decode(mov.project_id, null, null, msc_get_name.project&a2m_dblink(mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) project_number, decode(mov.task_id, null, null, msc_get_name.task&a2m_dblink(mov.task_id, mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) task_number, msi.planner_code planner, msi.buyer_name buyer_name, -- msc_get_name.lookup_meaning&a2m_dblink('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&a2m_dblink mcs where mcs.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&a2m_dblink 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&a2m_dblink('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand, decode(mp.plan_type , 8,decode(mov.order_type , 1, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',53)) ,51, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) ,msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) order_type, msc_get_name.supply_order_number&a2m_dblink(mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number, trunc(mov.need_by_date) need_by_date, trunc(mov.promised_date) promise_date, trunc(mov.old_schedule_date) old_due_date, trunc(mov.new_schedule_date) suggested_due_date, nvl(mov.daily_rate,mov.new_order_quantity) quantity, msc_get_name.action&a2m_dblink('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, -- nvl(decode(mov.implement_as,null,null,msc_get_name.lookup_meaning&a2m_dblink('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as)) ,xxen_util.meaning&a2m_dblink(xxen_msc_rel_plan_api.implement_as&a2m_dblink ( 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) ) implement_as, nvl(mov.implement_date, xxen_msc_rel_plan_api.implement_date&a2m_dblink ( 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&a2m_dblink ( 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 )) implement_date, nvl(nvl(mov.implement_daily_rate,mov.implement_quantity), xxen_msc_rel_plan_api.implement_qty_rate&a2m_dblink ( 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 )) implement_quantity, case when :p_autopopulate_release_status is not null then :p_upload_mode else null end 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, -- msc_get_name.get_order_comments&a2m_dblink(mov.plan_id, 'SUPPLY', mov.transaction_id) comments, decode(mov.firm_planned_type, 1, 'Y',null) firm, trunc(mov.firm_date) firm_date, mov.firm_quantity firm_quantity, -- msc_get_name.org_code&a2m_dblink(mov.source_organization_id,mov.source_sr_instance_id) source_organization, msc_get_name.supplier&a2m_dblink(mov.supplier_id) supplier, msc_get_name.supplier_site&a2m_dblink(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, msi.sr_inventory_item_id, mic.category_set_id category_set_id, nvl(mov.release_status,2) release_status from msc_supplies&a2m_dblink mov, msc_apps_instances&a2m_dblink mai, msc_plans&a2m_dblink mp, msc_plan_organizations&a2m_dblink mpo, msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic where 2=2 and 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(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) and (mov.sr_instance_id,mov.plan_id,mov.organization_id,mov.inventory_item_id) in (select qai.sr_instance_id, qai.plan_id, qai.organization_id, qai.inventory_item_id from q_all_items qai ) and nvl(nvl(mov.daily_rate,mov.new_order_quantity),0) != 0 and nvl(mov.implemented_quantity,0) = 0 and ( xxen_util.lookup_code&a2m_dblink(msc_get_name.action&a2m_dblink('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 (nvl(:p_called_from_schub,'N') = 'N' or mov.order_type = 5) and nvl('&m2a_dblink','?') = nvl('&m2a_dblink','?') and nvl(:p_show_item_dff,'?') = nvl(:p_show_item_dff,'?') ) mov where 3=3

Parameter Name

SQL text

Validation

Upload Mode

LOV

Auto Populate Release Status

LOV

Planning Instance

LOV

Plan

LOV

Organization

msi.organization_id in (select mpo.organization_id from msc_plan_organizations&a2m_dblink  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&a2m_dblink  mpo where mpo.organization_code=:p_source_organization_code and mpo.sr_instance_id = :p_instance_id and mpo.plan_id = :p_plan_id)

LOV

Category Set

LOV