Feature Specification

Feature Name: Distribution Resource Planning

Overview

The goal of this feature is to allow users of commercial editions of xTuple to be able to generate planned transfer orders between sites either manually or using netting logic contained in the Material Resource Planning (MRP) and Master Production Schedule (MPS) algorithms. Users will be able to manage and release planned transfer orders using the same tools that are currently provided to do these activities for other planned order types.

Planned order functionality and the MRP algorithm currently reside in the Manufacturing edition of xTuple, but will be moved to the Standard Edition including all related capabilities except Master Planning Schedules (MPS) and multi-level explosion. This means that MRP used for purchase order planning and single level work orders will be available in xTuple Standard Edition as a result of this development.

Functional Requirements

Implementing DRP will require the following:

• Transfer Orders will include new status categories at the header level for "Unreleased" and "Released."
  • Unreleased transfer orders will be eligible to be appended to by planned orders being released.
  • The releasing process will be similar to purchase order posting.
• The planning system identifier must be moved from the item record to the item site record.
• The item site record will allow a transfer order supply warehouse to be designated.
• Add the ability to create planned transfer orders manually.
• Add the ability to create planned transfer using MRP or MPS.
• The sequence in which sites are processed by MRP and MPS should be user defined.
• Planned transfer orders created as a result of MRP processing that may be managed like other MRP generated planned orders.
  • Planned transfer orders may be released in batches that will be aggregated together on transfer orders by common to and from site.
  • Logic for releasing transfer orders manually will be similar to releasing purchase requests.
    • Users will be offered an option to a append planned transfer order items to existing unreleased transfer orders.
    • A new transfer order will be generated and opened if one does not exist.
  • If the item is purchased or manufactured a planned transfer order may be converted to a planned purchase order or a planned work order.

New Terms and Definitions

• DRP

  • Distribution Resource Planning - An aspect of the MRP and MPS planned order generation process that nets demand at a site per planning settings and generates planned transfer orders for a designated supply site.

• Planned Transfer Order

  • An order record that may be created manually or by MRP and MPS runs recommending an transfer order of inventory from one site to another.

  Transfer Order Status

  • A transfer order may be in "Unreleased","Open" or "Closed" status. Unreleased means the order is eligible to be appended to by the releasing of planned transfer orders, but may not be shipped or received. A Transfer order may be released to "Open" status at which point it is eligible for shipping and receiving.

Related Existing Functionality

This functionality will draw heavily from existing functionality related to MRP to the extent that it will be drawing most MRP functionality from the manufacturing edition into the Standard Edition. The release of planned transfer orders will very closely mirror the process of releasing purchase requests.

Similar and Related Requests

• 8273 - Add Distribution Resource Processing (DRP) capability to Standard Edition
• 5597 - Small Changes to Provide DRP Functionality (MRP Planned TOs)

Conflicting Features

The MPS algorithm is embedded in the MRP algorithm. If we move the MRP algorithm to the standard edition package, we must separate out the MPS algorithm which should only be available in the Manufacturing Edition.

User-Level Functionality

Users will be able to designate item sites that are supplied by other sites. MRP or MPS will generate planned transfer orders based on the planning parameters and netting of the demand item site. Planned transfer orders and real transfer orders, in turn, may themselves be seen as demand by supplying sites which may in turn also generate planned orders.

Users will be able to release transfer orders from the Released Planned Orders window or the Planned Orders by Planner Code and by Item windows. When releasing a planned order the system will either create a new unreleased transfer order, or attach the items to existing unreleased planned transfer orders if any exist with the same from and to sites. This makes it possible to aggregate multiple planned transfer orders on a single transfer order.

Transfer Order

In order to make releasing planned transfer orders practical, we need to provide a mechanism to release multiple planned order records to the same transfer order. The existing paradigm for purchase request release provides the model for how this should be handled for transfer orders. When you release a purchase requisition to purchasing the application checks to see if there is an unposted purchase order for the selected Vendor, if so it prompts the user whether they want to add the item to that existing purchase order. In this way users can build a single purchase order from several independent purchase requests. In order for transfer orders to offer the same functionality, we must introduce a header status to designate whether transfer orders are in a unreleased or open status.

There is currently an "Order Status" field on the transfer order header that displays "O" for open and "C" for closed. This should be converted to a combo box with hard coded status of Unreleased, Released and Closed. The default status for a new transfer order should be "Unreleased" with the user having the option of being able to change the order to any status if they have appropriate privileges. The privilege ReleaseTransferOrders will be required to change the status to released. Changing the order status to closed should close all line items.

List Transfer Orders

The List Transfer Orders window should be altered so that it may be filtered on all statuses or selected statuses that may include open or unreleased orders. A button and right click option should be added to the window that allows users to select and release orders. Also, to be consistent with other screens and its actual function, this screen should be retitled "List Open Transfer Orders" with the corresponding menu being labeled "List Open."

Release Transfer Order

A new window will be added to the Inventory > Transfer Order menue that allows users with ReleaseTransferOrder privileges to release transfer orders one at a time. The menu should include a separator between existing listings, and this new listing.

Release Transfer Orders by Agent

A new window will be added to the Inventory > Transfer Order menu that allows users with ReleaseTransferOrder privileges to release transfer orders in batches according to agent criteria selection.

Issue Stock to Shipping, Ship Shipment and Inventory Receipt

Transfer orders in an unreleased status may not be shipped or received. Checks should be added to the issue stock to shipping, ship shipment and order receiving windows to ensure that transfer orders can only be transacted when in an open status.

Configure Schedule

This window that was formerly only available in the Manufacturing Edition will now be available in Standard Edition. However, the option to "Enable Constraint Management" will continue to only be available in the Manufacturing Edition and should be hidden in Standard.

Site

A new spin box setting labeled "Schedule Sequence" will be added to the Site window. This sequence number will determine the order in which sites are processed when MRP or MPS is run against all sites.

Item Site and Update Item Site by Class Code

The Item Site and Update Item Site by Class Code windows will be modified in several ways to accommodate the ability to specify item sites that receive supply from other sites. Note the layout restructuring pictured below:

The "Sold from this Site" group box has been moved to the upper left most corner and the "Costing Method" settings to the upper right. The "Supplied at this Site" group has been re-labeled "Supply Rules" and moved beneath the "Sold from this Site" group. Also, the "Supply Rules" is no longer a checkable group. Allowed supply order types of purchase order and work order will now be explicitly defined. The new check box organization as pictured more accurately describes the various options available regarding supply rules. The options to create purchase requests and work orders should be disabled unless this site has been flagged to allow those types of supply at the site.

The planning tab is used to aggregate all settings related, but not limited to, MRP and MPS. The "Planning System" combo box will be moved from the Item window to the Item Site window. This will enable users to specify MPS planning at one site, and MRP planning at another. However, the MPS option should only be made available on Manufacturing Edition of the product. The planning system will always default to "None." If the planning system is changed from MRP or MPS to None, all planned orders should be deleted for that item site. A new group "Create Planned Transfer Orders" will be added to designate that planned transfer orders from another site will be created rather than purchase or work orders for this item site. When this group is checked users must select an existing supply site for the item. The "Create Planned Transfer Orders" group should only be visible when Multi-Site functionality is enabled by the metric "MultiWhs."

All the widgets in the Scheduling group on the Planning tab will be hidden, except for the lead time and safety stock, if the application is not the commercial Standard or Manufacturing editions of xTuple. The "Group Planned Orders", "First Group" and "Create Planned Transfer Orders" widgets should only be enabled if the planning system is MRP or MPS. The MPS Time Fence widgets should only be enabled when the planning system is MPS.

Item and Itemsites

These windows will need to run a pre-check when deleting an item site to make sure that the item site being deleted is not referenced by another as a default transfer order supply source.

Planner Code

The Planner Code window should be modified to remove or hide the options related to "Automatically Explode Planned Orders" except on the Manufacturing Edition of xTuple.

Scheduling Menu

Currently the scheduling menu is only available in the Manufacturing Edition of xTuple. The menuSchedule.cpp file will need to be updated to allow this menu and its contents to be available in the Standard Edition with the exception of the following items which will remain exclusive to the Manufacturing Edition:

• Production Plan

• Scheduling > Run MPS

• Constraint Management
• Capacity Planning

• Reports > MPS Detail

• Reports > Rough Cut Capacity Plan

• Reports > Time phased Rough Cut Capacity Plan

• Master Information > Site Week

• Master Information > Site Calendar Exceptions

• Master Information > Work Center

Run MRP by Planner Code and Run MPS

The queries run by these windows should be altered to ensure that warehouse are processed in the order dictated by the site sequence settings.

Planned Order

The planned order window, used to manually generate planned orders, will be altered to allow users to define what supply type the planned order will be.

The enabled state and default type selection should be based on a combination of item and item site settings as follows:

• The type group will disabled until an item and site are selected.
• Purchased will be enabled as an option if the item type is purchased, an outside process or manufactured and the selected itemsite is flagged to allow items to be purchased. Otherwise it will be disabled.
• Work Order will enabled as an option if the item type is purchased or manufactured and the selected item site is flagged to allow items to be manufactured, otherwise it will be disabled.
• Transfer Order will be enabled if valid item sites exist from which the item may be transferred, otherwise it will be disabled. The from site will only be enabled if transfer order is selected. The from site should default to the be the same as the demand site when it is disabled.
• Purchase Order will be selected as the default option for purchased items unless the item site is flagged to create transfer orders.
• Work Order will be selected as the default option for manufactured items unless the item site is flagged to create transfer orders, or is not a designated manufacturing item site.
• Transfer Order will be selected as the default option for item sites which have been flagged to create planned transfer orders. The default transfer order supply site will be selected as the default when enabled.

The planned order window will also become a window used to edit planned orders which may include changing the order type, date or quantity. As such the "Create" button will be relabeled to "Save." Underlying code changes will need be made at the developer's discretion to support the notion of editing a planned order from this window.

Planned Orders by Planner Code and Item Displays

The planned order release process for transfer orders will behave very much like other planned orders. The Planned Orders by Planner Code and by Item displays will need to modified so that the existing "Site" column is re-labeled as "To Site." A new column will be created labeled "From Site." All pre-existing order types will have both columns populated with the same site information while planned transfer orders will specify corresponding from and to sites.

Right click options on the planned order list will be as follows:

• "Firm Order..." will firm the order, the same as it does today.
• "Change Order Type" will be replaced by "Edit Order" which when selected will open the planned order in edit mode as described in the Planned Order section above.
• "Released Order" will look for existing unreleased transfer orders with matching from and to site information. If it finds one or more it will prompt the user asking them whether they would like to append to the existing order or create a new one. After the user makes their choice, he or she will be prompted with a transfer order item window automatically populated. When the user clicks save the transfer order window will open populated by the transfer order that was created or appended to, the planned order will be deleted and the planned order window refreshed. This behavior is modeled after the process flow of releasing purchase requisitions which should be used as a reference.
• "Delete Order" will operate as it does today.

Release Planned Orders By Planner Code

The Release Planned Orders by Planner Code window provides a mechanism to release planned orders in batches. With regards to the release of transfer orders it will follow the same rules as the manual process above by aggregating all planned transfer orders specifying the same from and to site on to a single transfer order. In the manual process the user is prompted with an option as to whether to append to an existing unreleased transfer order if one is found. In this window that option will be handled by a new check box added to "Append planned transfer orders to existing unreleased transfer orders." If the option is checked the release process will first check to see if any unreleased transfer orders exist for a given from and to site combination. If one is found, planned transfer orders will be appended as line items to that order. If not, or if the option is not selected, a new transfer order will be created for which all items on the given from/to site combination will be appended.

Report Changes

The following reports should be moved from the openmfgserver repository to the standardserver repository:

• PlannedRevenueExpensesByPlannerCode

• PlannedOrdersByPlannerCode

• PlannedOrdersByItem

• MRPDetail

The following report should be moved from the xtupleserver repository to the standardserver repository:

• ExpediteExceptionsByPlannerCode

The following displays and accompanying reports should be altered to indicate planned orders that are transfer orders. Columns should be added to designate from and to site. The title for the site group being filtered on should be set as "To Site" and underlying queries should continue to be designed to filter on the demand site.

• dspExpediteExceptionsByPlannerCode
• dspPlannedOrdersByItem
• dspPlannedOrdersByPlannerCode
• dspPlannedRevenueExpensesByPlannerCode

On these reports the site label may remain the same but the from/to site data will be populated in the Source/Destination column:

• dspRunningAvailability
• itemAvailabilityWorkbench

The following displays and related reports will need to be altered to accommodate the new item site order supply rules:

• dspInvalidBillsOfMaterials
• dspInventoryAvailabilityByCustomerType
• dspInventoryAvailabilityBySalesOrder
• dspInventoryAvailabilityByWorkOrder

• FrozenItemSites

• ItemSitesByItem

• ItemSitesByParameterList

Batch Manager Changes

Batch manager has a hard coded query to run the MRP process. This query will need to be altered to process sites in the sequence dictated by the site schedule sequence setting. It would probably be wise as well to convert the query batch manager uses to a MetaSQL query in the database shared by it and the Run MRP windows.

Usability Considerations

Users who want to take advantage of MRP planned transfer orders will need to carefully plan the execution sequence by site. Consider a hub and spoke supply chain where site A is a manufacturing site, site B is a hub distribution center supplied by site A, and sites C, D and E are spoke distribution sites supplied by site B. Assume all sites are MRP driven. If a user simply runs a MRP against all sites and all sites have the same sequence number, there is no guarantee in what sequences the warehouses will be processed. If the manufacturing site is processed before the spoke sites, then new demand created by the spoke sites will not be considered by the manufacturing site MRP run. To properly drive this supply chain, MRP would have to be run site by site in this sequence:

1. Sites C, D and E
2. Site B
3. Site A

In this sequence sites C-E create planned transfer orders first, which are seen as demand that site B will take into consideration. Site B will generate planned transfer orders that site A will consider in its calculations.

Problems and Alternatives

To be determined.

Internal Design

Basic Algorithms

The only major change to planned order logic will actually be in an overload function for the createplannedorder function. The current lowest level overload will be broken out such that its main job is to determine the supply type and fetch the supply itemsite id used for transfer orders if applicable:

CREATE OR REPLACE FUNCTION createPlannedOrder(INTEGER, INTEGER, INTEGER, NUMERIC, DATE, DATE, BOOLEAN, BOOLEAN, INTEGER, TEXT) RETURNS NUMERIC AS '
DECLARE
  pPlanordid ALIAS FOR $1;
  pPlanNumber ALIAS FOR $2;
  pItemsiteid ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pCheckExplosion ALIAS FOR $7;
  pMPSPlanned ALIAS FOR $8;
  pPschitemid ALIAS FOR $9;
  pItemType ALIAS FOR $10;....

  Get itemsite_supply_itemsite_id and determine whether this will be a transfer order
  If not, determine what other type of supply order
  Use supply type determination to call overload createplannedorder with specific supply type and supply itemsite if applicable

A new overload createplannedorder function will be created that accepts supply type and supply item site as a arguments:

CREATE OR REPLACE FUNCTION createPlannedOrder(INTEGER, INTEGER, INTEGER, NUMERIC, DATE, DATE, BOOLEAN, BOOLEAN, INTEGER, TEXT, TEXT, INTEGER) RETURNS NUMERIC AS '
DECLARE
  pPlanordid ALIAS FOR $1;
  pPlanNumber ALIAS FOR $2;
  pItemsiteid ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pCheckExplosion ALIAS FOR $7;
  pMPSPlanned ALIAS FOR $8;
  pPschitemid ALIAS FOR $9;
  pSupplyType ALIAS FOR $10;
  pSupplyItemSite ALIAS FOR $11....

...
If the supply type is transfer order then
  Validate the supply itemsite:  Make sure it is active.
  If valid
    Insert a planned transfer order record from the supply item site to the demand item site
Else
  Continue with existing logic using pSupplyType to determine what kind of planned order to make
  (Make sure to include item site id on both demand and supply for other planned order types)
End if

Custom Widget Changes

The warehouseCluster will need to be modified to handle the separation of the item site supply boolean between work orders and purchase orders.

Schema Changes

Addition to the whsinfo table

Addition to the itemsite table

An update script must be written to port data in the itemsite_supply column to itemsite_wosupply and itemsite_posupply columns, then drop itemsite_supply.

An update script must be written to port planning system data from the item table to the itemsite table. The column item_planning_type should be dropped from the item table.

Addition to the planord table

An update script should be written to populate the planord_supply_itemsite_id with the same value in the planord_itemsite_id column.

Some tables will need to be moved from OpenMFG edition to Standard Edition. This will need to be accomplished by creating a temporary function in the upgrade scripts that creates the tables. The function will check to see if the application is Standard or OpenMFG edition. If standard it will create the tables, if not, it will not create the tables. The function should be created, run, and deleted by the script. The tables that will need to be added to Standard Edition are the following:

• mpsmrpwork
• planord
• planreq

The following privileges existing in the Manufacturing Edition will need to be added to the Standard Edition using an upgrade function similar to the one used for tables:

• !ConfigureMS

• DeletePlannedOrders

• FirmPlannedOrders

• ViewPlannedOrders

• ReleasePlannedOrders

• SoftenPlannedOrders

The site, item and itemsite API views should be updated to reflect the new and removed data fields described above.

The plannedorder API view will need to altered to accommodate the functional changes as reflected on the Planned Order window cited above. Also this view will need to be transferred from openmfgserver to the standardserver repository.

Stored Procedure Changes

The explodeplannedorder function should be removed from from xtupleserver on SVN and dropped from PostBooks and Standard Edition databases where it serves no function, and should be moved to the openmfgserver repository.

The following functions should be moved from the openmfgserver repository to the standardserver repository:

• createplannedorder
• createplannedorders
• currentplannumber
• deletempsmrpworkset
• deleteplannedorder
• fetchplannumber
• getplanordid
• mpsreport
• mrpreport
• nextplansubnumber
• plannedcost
• plannedrevenue
• qtyfirmed
• qtyfirmedallocated
• qtyplanned
• qtyplanneddemand
• releaseplannedorder
• releaseplannumber
• updateplannedorder

The createplannedorder and updateplannedorder functions will need to be updated to handle the new order type selection as cited in the Planned Order window and Basic Algorithm sections above.

The createplannedorders function will need to be modified to get the planning system data from the itemsite record instead of the item record. It will need to reference the itemsite_wosupply and itemsite_posupply fields in lieu of the itemsite_supply field. There is also a large section in the createplannedorders function for processing cumulative MPS schedules. This section should be extracted into a separate function that is stored in the openmfgserver repository. In Standard Edition this new MPS function would never be called as it will not be possible to run MPS scheduling in that product. Also the createplannedorders function should be otherwise reviewed to ensure that no MPS related functions might be called that would not exist on a Standard Edition database.

The copyitemsite function will need to updated to handle the new item site structure.

The qtyplanned function should be altered to include planned transfer orders where the supply itemsite matches itemsite id being queried.

The itemsitetrigger should be altered to consider the new supply rules.

Performance Considerations

None expected.

Error Handling

To be determined.

QA Considerations

There should be thorough testing of MRP functionality in Standard Edition to make sure that no functions, screens or reports are missing, and that no Manufacturing Edition specific functionality exists in the Standard Edition. Specifically manufacturing specific features include functionality relating to MPS, multi-level explosion and labor tracking.

All windows, displays and reports mentioned in this document that will be affected by changes described above should be thoroughly tested.

Documentation Considerations

Currently documentation that is Standard Edition specific or Manufacturing Edition specific are labeled as such. Manufacturing Edition specific labels will need to be review and changed to Standard Edition where applicable.

Otherwise, documentation will need to be updated per the user functionality and report changes sections above.

Release Considerations

This feature is scheduled to be released in version 3.3 of xTuple.

There is also a development plan in version 3.3 to convert the Manufacturing Edition into an extension packge. Changes described in this specification should be completed before that effort is begun.