PAHMA Object Exit data mapping

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

Object Exits extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDeaccession	DispositionMethod	exitReason	FK to DispositionMethods
ObjDeaccession	RecipientConID	currentOwner	FK to Constituents; reference to Person/Org Authority
ObjDeaccession	SaleDate	exitDate
ObjDeaccession	Terms	exitNote
ObjDeaccession	ProceedsRcvdDate	exitNote
ObjDeaccession	Remarks	exitNote

sql query

view: cs_objectexits

SELECT
 d.deaccessionID objectexitID,
 (CASE WHEN d.dispositionMethod NOT IN (0, 9, 10) THEN lower(m.dispositionMethod) END) exitReason,
 (CASE WHEN d.recipientConID != -1 THEN d.recipientConID END) currentOwner,
 CONVERT (VARCHAR, d.saleDate, 101) exitDate,
 d.terms exitNote_1,
 (CASE WHEN d.proceedsRcvdDate IS NOT NULL THEN 'TMS ProceedsRcvdDate: ' + CONVERT (VARCHAR, d.proceedsRcvdDate, 101) END) exitNote_2,
 d.remarks exitNote_3
FROM
 ObjDeaccession d
 JOIN DispositionMethods m ON d.dispositionMethod = m.dispMethodID
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE  o.IsVirtual = 0 AND o.IsTemplate = 0)
;

notes

NOTE: the exitNote_1, exitNote_2, and exitNote_3 fields in this query should be concatenated into a single field exitNote, separated by line breaks; this cannot be done in this query because fields of type TEXT do not allow concatenation

ObjectExit-Object Relationships extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDeaccession	objectID	reference to Object
ObjDeaccession	deaccessionID	reference to ObjectExit

sql query

view: cs_objectexitobjectrelationships

SELECT
 CAST (d.objectID AS VARCHAR) objectID,
 d.deaccessionID objectexitID
FROM
 ObjDeaccession d
WHERE d.objectID IN (SELECT o.objectid FROM objects o WHERE  o.IsVirtual = 0 AND o.IsTemplate = 0)
;