MSC Plan Order Upload

Description

MSC Plan Order Upload actions Oracle ASCP (Advanced Supply Chain Planning) plan recommendations from Excel. It downloads the planned orders and reschedule recommendations of a plan, lets you amend the implement date and implement quantity, and then selects them for release or releases them, the same as ticking For Release and running Release in the Planner Workbench. It only updates existing recommendations; it never creates or deletes plan data.

When to use it

Release ASCP planned orders in bulk instead of one by one in the Planner Workbench.
Mark many planned orders as selected for release, so a planner can review and release them later.
Act on Reschedule In / Reschedule Out recommendations for existing supply orders.
Override the suggested implement date or quantity before releasing.

Before you start

The plan has been run and its recommendations are visible in the Planner Workbench.
Your user has a responsibility with update access to the Planner Workbench for the organization (not a view-only workbench). The upload checks this and uses it to select and release the orders.
In a distributed setup, where planning runs on a separate instance, your user must also exist on the planning instance with access to release planned orders there.

Choose a template

Template	Use it for
Plan Order Recommendations (default)	Download the recommendations and choose the rows to action yourself.
Plan Order Recommendations – Automatic Release	Pre-sets Upload Mode to Release the Order and Auto Populate Release Status to Yes, so every downloaded recommendation is already marked for release and the file can be saved and uploaded without picking rows. Check the Planning Instance before running.

Step 1 – Set the parameters

Open MSC Plan Order Upload in Blitz Report and set the parameters:

Parameter	Meaning
Upload Mode	Select for Release (default) only marks the orders as selected for release; Release the Order selects and releases them. It also limits the value you can choose in the Update Release Status column.
Auto Populate Release Status	Yes fills Update Release Status with the Upload Mode on every downloaded row and marks the rows for upload.
Planning Instance	The planning instance code (required).
Plan	The plan whose recommendations are downloaded (required).
Organization / Source Organiziation	Restrict the download to a planned organization or a source organization.
Category Set / Category	The category set shown in the file (required, defaults to the planning default) and an optional category filter.
Item	Restricts the download to one item.
Explode Assemblies	Yes also downloads the recommendations for the components of the selected items’ bills of material.
Project / Planner / Buyer / Make/Buy	Further filters on the item or order.
Order Type	Defaults to Planned order. Clear it or choose another order type to download reschedule recommendations for existing supplies.
Action	Restricts the download to one recommendation, for example Release or Reschedule In.
Selected for Release	Downloads only orders that are, or are not, already selected for release.
Suggested Due From / Suggested Due To	Restrict the download to a suggested due date range.
Show Item Descriptive Attributes	Yes adds the item descriptive flexfield columns.

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per recommendation that can be actioned: orders with a Release, Reschedule In or Reschedule Out recommendation, and orders already selected for release. Most columns are read-only and identify the order.

Step 3 – Enter or change the data

For each order you want to action, choose Update Release Status (Select for Release or Release the Order, depending on the Upload Mode) and check the Implement Date and Implement Quantity. Both must be filled in, and the Implement Date cannot be in the past. For an order that is already selected for release (Released Status Selected), you can change the Implement Date or Implement Quantity without setting Update Release Status. Only these three columns can be changed.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which checks each order against the current plan, applies the implement date and quantity, selects the order for release and, for Release the Order, runs Oracle’s release process.

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message. Selected orders show Released Status Selected with the new implement date and quantity.

What’s produced

Plan orders selected for release, with the amended implement date and quantity.
For Release the Order: released recommendations, created by Oracle’s standard release process (for example purchase requisitions or discrete jobs for planned orders, and rescheduled existing orders).
A result report listing every row with a status (success or error) and a message.

Common questions

What is the difference between Select for Release and Release the Order?
Select for Release marks the order as selected and stores your implement date and quantity, the same as ticking For Release in the Planner Workbench, but does not release it. Release the Order does the same and then releases the order.

Does it create new planned orders?
No. It only updates recommendations that already exist in the plan.

I only want to change the implement date or quantity. What do I enter?
If the order is already selected for release, just change the Implement Date or Implement Quantity. If it is not, also set Update Release Status.

How do I release everything without choosing rows?
Use the Automatic Release template, or set Upload Mode to Release the Order and Auto Populate Release Status to Yes.

Why are my reschedule recommendations missing?
Order Type defaults to Planned order. Clear it or choose the order type of the existing supply, for example a purchase order.

Can I change the comments or Implement As?
No. Comments and Implement As are shown for reference only.

Troubleshooting

Message	Cause	What to do
User <user> does not have access to release planned orders in the ASCP instance	None of your responsibilities has update access to the Planner Workbench for the organization.	Ask your administrator for a planning responsibility with update access to the workbench for this organization.
User <user> is not defined in the ASCP instance	Distributed setup: your user does not exist on the planning instance or may not release there.	Have the planning administrator create the user and assign release access on the planning instance.
Update Release Status must be specified to update the Implement Date/Quantity	The implement date or quantity was changed on an order that is not selected for release.	Set Update Release Status.
Both Implement Date and Implement Quantity must be specified to select or release a Plan Order	One of the two values is empty.	Fill in both columns.
Implement Date cannot be in the past	The Implement Date is before today. Recommendations of older plans can carry past dates.	Enter today or a later date.
Release Quantity must be >= 0	Negative Implement Quantity.	Enter zero or a positive quantity.
Plan Order has already been implemented. Qty=<quantity>	The order was released after the download.	Download the recommendations again.
Plan Order Release Status has changed / Plan Order recommendation has changed. Action=<action>	The order was selected, released or re-planned after the download.	Download the recommendations again and reapply your changes.
Invalid Planning Instance / Invalid Plan	The instance or plan in the file no longer exists, for example after a plan was renamed or purged.	Download the recommendations again for an existing plan.

SQL 273 lines

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.default_flag = 1 and rownum=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,
to_number(null) upload_row
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,
--
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.default_flag = 1 and rownum=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  'Rescheduled'
when  mov.implemented_quantity is not null
then  'Released'
else  null
end                                             released_status,
mov.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,
--
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,
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,
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 mcs.default_flag = 1 and rownum=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.default_flag = 1 and rownum=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, to_number(null) upload_row 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, -- 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.default_flag = 1 and rownum=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 'Rescheduled' when mov.implemented_quantity is not null then 'Released' else null end released_status, mov.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, -- 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, 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, 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 mcs.default_flag = 1 and rownum=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

Parameters 20

Parameter

Type

List of values SQL

Upload Mode

LOV

Auto Populate Release Status

LOV

Planning Instance

LOV

Plan

LOV

Organization

LOV

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)

Source Organization

LOV

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)

Category Set

LOV