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

notes

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