...
Anchor | ||||
---|---|---|---|---|
|
Storage Location extract
The
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Locations | Active | locationStatus | convert from '1/0' to 'activeaccepted/inactiverejected' to used in dropdown in UI |
Locations | LocationID | shortIdentifier | numeric value, prepended with 'sl' |
Locations | LocationString | displayName |
|
Locations | l.Site+', '+l.Room | Parent | for use in create creating "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Locations | Description | locationNote locNote | prepended with "Description: " if not empty |
sql query
view: cs_storagelocations1 (refreshed 2012-04-26)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT 'sl'+ltrim(str(l.LocationID)) shortIdentifier, (case when l.active = 1 then 'activeaccepted' else 'inactiverejected' end) locationStatus, l.LocationString displayName, l.Site+', '+l.Room Parent, l.Site Site, l.Room Room, l.UnitType UnitType, l.UnitNumber UnitNumber, l.UnitPosition UnitPosition, (case when l.Description = '' then '' else 'Description: '+l.Description end) locationNotelocNote FROM locations l ; |
notes
- the five levels of the hierarchy will need to be mapped into some sort of CSpace hierarchy. The BT/NT relationships may be sufficent, but this remains to be demonstrated. At any rate the schema (and UI?) need to be modified to hold and render these values.
- there is probably some cleaning that can be identified and performed as part of this migration. At least the following should be done, either as part of this SQL or as part of other ETL:
- "Dedupe" the locations. Since Crates are now locations in their own right, they should be checked against the locations drawn from the Locations table. There seem to be some cases where Crates have been represented as locations in the Locations table.
- There are cases where the displayName for a location includes the name of the crate. Some of these cases are handled (see note below), but more investigation is warranted.
- More TBD!
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Crates | Active | locationStatus | convert from '1/0' to 'activeaccepted/inactiverejects' to used in dropdown in UI |
Crates | crateID | shortIdentifier | numeric value, prepended with 'cr' |
Locations | LocationString+CrateNumber | displayName | CrateNumber is appended to LocationString if not appended al |
Locations | LocationString | Parent | for use in create creating "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Crates | CrateNumber | locationNote locNote | prepended with "Crate: " |
sql query
view: cs_storagelocations2 (refreshed 2012-04-26)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT 'cr'+ltrim(str(c.crateID)) shortIdentifier, (case when c.active = 1 then 'activeaccepted' else 'inactiverejected' end) locationStatus, (case when CharIndex(replace(c.CrateNumber,'-',', '),l.LocationString) > 0 then l.LocationString else l.LocationString+', '+c.CrateNumber end) displayName, l.LocationString Parent, l.Site Site, l.Room Room, l.UnitType UnitType, l.UnitNumber UnitNumber, l.UnitPosition UnitPosition, 'CrateNumber: '+' '+c.CrateNumber CrateNumberlocNote FROM crates c JOIN Locations l ON l.LocationID = c.LocationID ; |
notes
- In some cases the CrateNumber crateNumber is already included in the locationString field, in which case there is no need to append it again to the display name. A little bit of tricky SQL checks for this case.
- Nevertheless, the CrateNumber crateNumber is being kept as a notelocatNote, just in case.