...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
Locations | Active | locationStatus | convert from '1/0' to 'accepted/rejected' 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 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 'accepted' else 'rejected' 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!
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
Crates | Active | locationStatus | convert from '1/0' to 'accepted/rejects' 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 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 'accepted' else 'rejected' 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 locationNotelocationNotelocNote FROM crates c JOIN Locations l ON l.LocationID = c.LocationID ; |
...