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
Objects	ObjectNumber	exitNumber	prepended with 'EX'
ObjDeaccession	DispositionMethod	exitReason	FK to DispositionMethods

sql query

view: cs_objectexits (refreshed 2012-03-22)

SELECT
 d.deaccessionID objectexitID,
 'EX' + o.ObjectNumber exitNumber,
 (CASE WHEN d.dispositionMethod NOT IN (0, 9, 10) THEN lower(m.dispositionMethod) END) exitReason
FROM
 ObjDeaccession d
 JOIN Objects o ON d.objectID = o.objectID
 JOIN DispositionMethods m ON d.dispositionMethod = m.dispMethodID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
;

notes

dispositionMethod values of 0 ("not assigned"), 9 ("number not used"), and 10 ("not found") are not imported

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 (refreshed 2012-03-22)

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

notes

Exit Date extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDeaccession	SaleDate	exitDate
ConXrefs	DateBegin OR DisplayDate	exitDate

sql query

view: cs_objectexitdates (refreshed 2012-03-22)

SELECT
 d.deaccessionID objectexitID,
 CONVERT (VARCHAR, d.saleDate, 101) exitDate
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.saleDate IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 CONVERT (VARCHAR, CASE WHEN x.dateBegin != 0 THEN x.dateBegin WHEN x.displayDate IS NOT NULL AND LEN(x.displayDate) > 0 THEN substring(x.displayDate, len(x.displayDate)-4, 5) END) exitDate
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
 AND (x.dateBegin != 0 OR x.displayDate IS NOT NULL)
;

notes

New Owner extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDeaccession	RecipientConID	currentOwner	reference to Person/Org Authority
ConXrefs	ConstituentID	currentOwner	reference to Person/Org Authority

sql query

view: cs_newobjectowners (refreshed 2012-03-22)

SELECT
 d.deaccessionID objectexitID,
 (CASE WHEN d.recipientConID != -1 THEN d.recipientConID END) currentOwner
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.recipientConID != -1
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 x.constituentID currentOwner
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
;

notes

Exit Note extract

field mapping

TMS table name	TMS field name	CSpace field name
ObjDeaccession	Terms	exitNote
ObjDeaccession	ProceedsRcvdDate	exitNote
ObjDeaccession	Remarks	exitNote
ConXrefs	Remarks	exitNote

sql query

view: cs_objectexitnotes (refreshed 2012-03-22)

SELECT
 d.deaccessionID objectexitID,
 d.terms exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.terms IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 (CASE WHEN d.proceedsRcvdDate IS NOT NULL THEN 'TMS ProceedsRcvdDate: ' + CONVERT (VARCHAR, d.proceedsRcvdDate, 101) END) exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.proceedsRcvdDate IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 d.remarks exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 'TMS DeaccessionRecipient: ' + x.remarks exitNote
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
 AND x.remarks IS NOT NULL
;

notes

NOTE: multiple exitNote values per ObjectExit record should be concatenated into a single field, separated by line breaks