Technical Details
Orders
When an allocation proposal is confirmed, orders are created (1 order for each of the location that the products were allocated to), i.e. the dbo.orders, dbo.order_lines and dbo.order_line_calc_results tables are all populated. This means that as soon as users confirm an order they should be able to see it in the order grid like any other orders and those orders get cought by the dbo.v_orders_to_transfer and therefore feed into the ERP systems through the same pipe as any other orders.
View Levels
In the new product hierarchy we have Product Items and Product Item SKUs instead of item no and item ids. So essentially, the Product Item level is the same as item_no level and the SKU level is the same as item_id level (items down to location).
The views within the Allocation module are on different levels.
View levels refer to the depth of the data shown in a view. For example the view Incoming POs is shows a list of purchase orders coming into the warehouse and information about those POs, and is therefore on purchase order level. It is good to be aware of one complication within the allocation module, that is that the SKU level within allocation can be threefold; Store SKU level, Warehouse SKU level, SKU level.
- Store SKU level: Only contains SKUs on store locations
- Warehouse SKU level: Only contains SKUs on warehouse locations
- SKU level: Contains all SKUs
A Product Item always belongs to both a warehouse and different stores, it is ordered into the warehouse and then distributed to different stores. I.e. when the products come into the warehouse, the quantities belong to a warehouse SKU, but when they are sent to the stores, the quantities are distributed to the store SKUs. Therefore, we can always be sure that the SKUs within the purchase_order_line table are warehouse SKUs as they are being brought into the company, and the SKUs listed in the allocation_proposal table are store SKUs as allocation proposals are essentially just transfer orders and those are products are going to stores.
In the allocation module, there are three views on either Store SKU or Warehouse SKU level.
Warehouse Level Views:
- Create Allocation Proposal
- WH Stock
Store Level Views:
- Review & Confirm Allocation
Here the consultant needs to be aware that even though the warehouse and store level views are all on SKU level, they don’t share SKUs, i.e. the warehouse level views only contain warehouse SKUs, and store level views only contain store SKUs. The warehouse level views can display data from the purchase_order_line table but not the allocation_proposal table and the store level views can display data from the allocation_proposal table but not the purchase_order_line table. And for the same reason, it is not possible to create new views that contain data from both the purchase_order_line table and the allocation_proposal table.
Incoming vs. WH Stock POs
For the Warehouse Location, we have either an undelivered PO(incoming) or a delivered PO(WH stock) .
Undelivered/Incoming POs have not arrived to the warehouse. In the “Create Allocation Proposal” view these POs are called Incoming POs. There is also a special view “Incoming POs” which shows an overview of all expected incoming POs.
Delivered/WH Stock POs have arrived at the Warehouse. Therefore, they are no longer “incoming” and are then thought of as a part of the warehouse stock, categorised as “WH stock” POs.
To allocate the quantity from a delivered PO, you have to go the view WH Stock. There you choose the desired item and quantity and use the action Add WH Stock to Allocation.
Now your chosen item(s) have been added to the Create allocation proposal view as a “WH stock PO”, sometimes called Virtual PO. Those POs get a Purchase order NO like this “AGRXXXX”.
From there the user can create an actual allocation proposal.
Only the incoming/undelivered POs are a part of the tables alloc.purchase_order and alloc.purchase_order_line; because there is filter “where deliv_status = 0” (0 meaning the order is undelivered).
Allocation specific tables with additional data
Table alloc.item_group_info stores specific data used in Allocation on product_group level. That is the hold_back and minimum allocation quantity. The id column in the table is the same as the id in dbo.item_group_column.
Table alloc.item_info stores specific data down to product_item_sku_id and is particularly used in Allocation module. See view alloc.v_item_info how the data is gathered.
Customisations
You should try not to make changes to any alloc tables, views or procedures as that means your company isn’t in the upgrade-path anymore. The following tables and procedures should help you avoid making changes to the standard, if you feel anything is missing, please contact us or report an issue.
Extra Info Tables
The module contains extra_info tables that come empty and can be used to add custom columns to different levels.
alloc_cus.allocation_proposal_extra_infoalloc_cus.product_item_extra_infoalloc_cus.product_item_sku_extra_infoalloc_cus.purchase_order_line_extra_infoalloc_cus.transfer_order_route_extra_info
These tables include the relevant id columns and are already mapped in data_element_ref_tables_connections.
Custom procedures
For the procedure alloc.de_allocate_selected_PO_lines which calculates and creates a allocation proposal, there are two custom procedures, one which is invoked in the beginning of the procedure, alloc.pre_allocation_run, and one which is invoked in the end of the procedure, alloc.post_allocation_run. Those procedures come empty and can be used to customise the allocation calculations.
The same goes for the procedure alloc.de_confirm_allocation_proposal which confirms proposals and creates orders, alloc.pre_confirm_allocation_run is invoked in the beginning and alloc.post_confirm_allocation_run is invoked in the end. Those procedures come empty and can be used to customise the confirmation logic.
As the allocation module is still quite new and inexperienced, we would like to hear from you if you feel like you need more places for customisations.
Other
Allocation status
Both purchase orders and allocation proposals have a status called allocation_status. The allocation status represents where in the allocation process the order is. There are 3 different statuses (the ids and names of the statuses can be seen in the table alloc.v_po_allocation_status):
Status 0: When a purchase order has not been allocated yet, the purchase order and it’s purchase order lines will have status 0, and the allocation proposal hasn’t been created yet so there won’t be a row in the allocation_proposal table for that purchase order.
Status 1: The allocation proposal has been created but has not been confirmed yet. In this case the relevant purchase order lines will get status 1 as well as the allocation proposal that was created. However, the purchase order itself will only get status 1 if all of its purchase order lines have been allocated.
Status 2: The allocation proposal has been confirmed and an order created and transferred to the erp system. In this case the relevant purchase order lines will get status 2 as well as the allocation proposal that was confirmed. However, the purchase order itself will only get status 2 if all of its purchase order lines have been allocated and confirmed.