MSC Plan Order Upload
Description
Categories: Enginatics
Repository: Github
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 ... more
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 ... more
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 -- ¬_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 |
|
LOV | |
Source Organiziation |
|
LOV | |
Category Set |
|
LOV | |
Category |
|
LOV | |
Item |
|
LOV | |
Planner |
|
LOV | |
Buyer |
|
LOV | |
Make/Buy |
|
LOV | |
Selected for Release |
|
LOV Oracle | |
Suggested Due From |
|
Date | |
Suggested Due To |
|
Date | |
Allow Release in Upload |
|
LOV |
Blitz Report™