PAHMA Object Inventory data mapping

This page contains information about PAHMA's data migration of Object Inventory and related info.
See parent page on PAHMA data migration ETL work.

Object Inventory

overview

Inasmuch as the migration of the locations of objects at PAHMA (and their history) is functionally similar to the (already implemented) PAHMA bar code scanner requirements inventory process implemented to support the use of barcode readers, it has been decided to "piggyback" the TMS to CSpace migration on this process.

Essentially, the Object Inventory crosswalk will be accomplished by creating file(s) of records in the "barcode file format" from TMS, and processing these records in the same way barcode files are processed.

An analysis of the existing TMS data reveals that a few more fields will need to be included; the barcode process will be extended to handle these additional fields.

field mapping, basic barcode format

TMS table name	TMS field name	CSpace field name	notes
ObjLocations	Handler	locationHandler	Constituents FK LocHandlers FK ObjLocations.Handler
ObjLocations	LocationString	currLocation	FK Locations.LocationID
ObjLocations	ObjectNumber	objectNumber	ObjComponents FK Objects FK ObjComponents.ComponentID
ObjLocations	TransDate	locationDate	Needs to become a real timestamp in CSpace

fields to be added to barcode format

TMS table name	TMS field name	CSpace field name	notes
LocPurposes	LocPurpose	reasonForMove
ObjLocations	AnticipEndDate	plannedRemovalDate
ObjLocations	DateOut	removalDate
ObjLocations	concat(Sublevel, SearchContainer, TempText, Approver)	currentLocationNote
ObjLocations	CrateID	currentLocationNote	(perhaps this should not be just a note...)

sql query

view: cs_objectlocations (refreshed 2012-04-05 [may still need work])

SELECT
 j.ObjectNumber objectNumber,
 l.LocationString currLocation,
 o.Handler locationHandler,
 o.TransDate locationDate,
 p.LocPurpose reasonForMove,
 o.AnticipEndDate plannedRemovalDate,
 o.DateOut removalDate,
 o.Sublevel+o.SearchContainer+o.TempText+o.Approver+str(o.CrateID) currentLocationNote,
 l.LocationID locationShortID,
 (case when o.Inactive = 1 then 'active' else 'deleted' end) miscStatus
FROM
 ObjLocations o
 JOIN Locations l ON l.LocationID = o.LocationID
 JOIN Objects j ON j.ObjectID = o.ComponentID
 JOIN LocPurposes p ON p.LocPurposeID = o.LocPurposeID
;

notes

Relationship records between the Objects and the Movement records will also need to be created, and (apparently) the other way around, too. The logic for this will be added here shortly.

Perhaps the barcode process should also use the notes field? And perhaps some details of the scanner being used might be included?

I noticed elsewhere that the barcode scanner seems not to be 100% accurate (sometimes the scanned items was incomplete or missing). Perhaps something could be included in the file to help detect this?

MB suggests that perhaps "inactive" records be included, and a "soft delete" performed on them. This makes sense, I think, in which case we would want to map that field to whatever CSpace uses to mark a deleted record.

Note that the field Timestamp is a Date (in CSpace); this is going to need to be addressed before we can make much more progress.

Note from MB, saved for posterity

 ---------------------------- Original Message ----------------------------
 Subject:  Storage location fields and barcode inventory fields
 From:     "Michael T. Black" <mtblack@berkeley.edu
 Date:     Thu, January 26, 2012 4:14 pm
 To:       jblowe@berkeley.edu
 --------------------------------------------------------------------------

First, here are the fields produced by barcode inventory:
 * Person's name
 * Storage location
 * Object number
 * Timestamp

And here are the fields we'll need to import from ObjLocations:
 * Handler (will give Person's name, when used as FK to LocHandlers and then to
Constituents)
 * LocationID (will give Storage location when used as FK to Locations.LocationID)
 * ComponentID (will give Object number when used as FK to ObjComponents and then to
Objects)
 * TransDate (will serve as Timestamp)
 * LocPurpose (to "Reason for move")
 * CrateID
 * AnticipEndDate (to "Planned removal date")
 * DateOut (to "Removal date")

Need to go somewhere:
 * Sublevel (notes, some crates)
 * SearchContainer (notes, some crates)
 * TempText (notes, some reasons)
 * Approver (notes, some reasons)

Not clear whether useful in CSpace:
 * Inactive (is working as a sort of soft delete)

Not needed in CSpace:
 * TempFlag
 * LoginID
 * EnteredDate
 * TempTicklerDate
 * LocLevel
 * PrevObjLocID
 * NextObjLocID
 * SchedObjLocID
 * RequestedBy
 * ShipmentID (at least not needed now/soon)
 * TransStatus (at least not needed now/soon; 1=completed; 2=pending)