Page Comparison

...

In theory, it would be appropriate to generate Display Names via an algorithmic (and reversible) concatenation of the locations path in the hierarchy. In this case, the Display Name key would be locked and not directly editable -- editing would be done on the underlying hierarchy. This would also make it possible to support the "crate movement functionality". Caution would be needed in permitting editing, as a change to an upper level of the hierarchy would render all the barcodes for locations beneath that level unrecognizable. This would seem to dictate that the values for nodes in the hierarchy have an attribute (perhaps "immutable") for locations that could not or would not change (e.g. "buildings", or "aisles").

Also, it is necessary to create Storage Location Authority records for the Crate objects in TMS, which become Storage Locations in CSpace. This is done via a separate query based on the TMS Crates table which creates additional records in the same format as those pulled from the Locations Table.

For crates, their immediate parent is the displayName of the Location record they were keyed to. For immovable locations, their parent is Site+Room of their location.

...

sql query

...

view:  cs_storagelocations1  (refreshed 2012-04-26)

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) locNote
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

sql query

view:  cs_storagelocations2  (refreshed 2012-04-26)

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 locNote
FROM
  crates c
  JOIN Locations l ON l.LocationID = c.LocationID
;

notes

• In some cases the 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 is being kept as locatNote, just in case.