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 |
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, 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) ;
notes
ObjectExit Current 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:
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') ;