ERP locations are mapped into raw.LOCATION and the table also includes custom locations created in raw.LOCATION_EXTRA.
If a location with the same NO as a custom location arrives from the ERP then that location takes over the custom one. This is done so that a location can be created in AGR ahead of time before it is created in the ERP.
Basic location setup is done using the core.location_mapping_setup table and the core_include_all_locations_by_default (default: true) setting in core.setting. This table can be used to control what locations from raw.LOCATION are mapped and also combine two or more locations.
core.location_mapping_setup[location_no] [NVARCHAR](255) NOT NULL: This references the location_no in raw.LOCATION
[location_no_target] [NVARCHAR](255) NOT NULL: This references the target location_no in raw.LOCATION
[location_type] [NVARCHAR](10) NULL: location type, ST (store) or WH (warehouse)
[include] [BIT] NOT NULL: Should the entry be included (1) or excluded (0)
If the table is left empty and core_include_all_locations_by_default is set to true, then all locations that appear in raw.LOCATION are mapped trough to the prep layer.
Examples:
If you want to exclude one or more locations but still include all others you can add an entry like this with core_include_all_locations_by_default is set to true.
INSERT core.location_mapping_setup ([location_no],[location_no_target],[include])
SELECT 'B910','B910',0;
Include only one location you can add an entry like this with core_include_all_locations_by_default’ is set to false.
INSERT core.location_mapping_setup ([location_no],[location_no_target],[include])
SELECT 'B200','B200',1;
If you want to combine two locations (note that the target location must also be a location listed in raw.LOCATION)
INSERT core.location_mapping_setup ([location_no],[location_no_target],[include])
VALUES ('B900','COMBINED_WAREHOUSE',1),('B910','COMBINED_WAREHOUSE',1) ;
These locations are available for inventory and all other modules to use.
In combination with the core_location_type_default setting you can set location types using the location_type column.
Setting the core_location_type_default to ST you only have to specify the location type for the warehouse locations if no location type data is provided by the erp.
INSERT core.location_mapping_setup ([location_no],[location_no_target],[location_type],[include])
VALUES ('B900','B910','WH',1),('B910','B910','WH',1) ;
If location types are provided in the erp data mapping location_type column can be left as NULL if using core.location_mapping_setup for other location setup.
For Inventory, it is possible to change how transactional data is mapped to each and every location.
By default all locations are mapped “to themselves”, with the default settings: sale = true, transfer_as_sale = false, stock = true, undelivered = true, reserved = true
inv.config_location_setup[id] [INT] IDENTITY(1,1) NOT NULL: Identity field and primary key
[location_no] [NVARCHAR](128) NOT NULL: This references the location_no in inv.locations
[type_id] [INT] NOT NULL: Config type, see inv.config_location_setup_type
[location_list] [NVARCHAR](MAX) NULL: If using location_list config type the you must provide a comma separated list of [no] from [prep].[location_and_vendor]
[sale] [BIT] NOT NULL: Map sales, affects [inv].[histories_sale]
[transfer_as_sale] [BIT] NOT NULL: Map transfer transactions as sale, affects [inv].[histories_sale]
[stock] [BIT] NOT NULL: Map stock, affects [inv].[histories_stock], [inv].[stocks]
[undelivered] [BIT] NOT NULL: Map undelivered, affects [inv].[undelivered]
[reserved] [BIT] NOT NULL: Map reserved, affects [inv].[reserved]
The default priority of the different types of location setup entries is in the type_id order (type 1 takes priority over type 2 and so on).
If you want to change how transactional data is mapped within a location then add an entry with type 1.
Example:
Here we want to not include B910 sales or transfers as sales for B910.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'B910',1,NULL,0,0,1,1,1;
If you want to map transactional data from a single or a list of locations then add an entry with type 2.
Example:
Here we want the items in location B910 to include sales from B103, B104, B105, and B110.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'B910',2,'B103,B104,B105,B110',1,0,0,0,0;
If you want to map transactional data for items from locations that use the current location as an order route then add an entry with type 3.
Example:
Here we want the items in location B910 to include sales from all locations that have items with order route from B910. This includes both primary and secondary order routes.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'B910',3,NULL,1,0,0,0,0;
If you want to map transactional data from all Warehouse locations then add an entry with type 4.
Example:
Here we want the items in a new custom sum-location to include sales, stock, and undelivered from all warehouse locations.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'custom_warehouse',4,NULL,1,1,0,1,0;
If you want to map transactional data from all Store locations then add an entry with type 5.
Example:
Here we want the items in a new custom sum-location to include sales, stock, and undelivered from all store locations.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'custom_store',5,NULL,1,1,0,1,0;
If you want to map transactional data from all Locations then add an entry with type 6.
Example:
Here we want the items in a new custom sum-location to include sales, stock, and undelivered from all locations.
INSERT inv.config_location_setup ([location_no],[type_id],[location_list],[sale],[transfer_as_sale],[stock],[undelivered],[reserved])
SELECT 'custom_location',6,NULL,1,1,0,1,0;
The output is a list of connections between item_ids and their settings in the table inv.item_map. If a more specific transactional data mapping between locations is needed, it is possible to create a custom procedure that populates the inv.item_map as needed.