...
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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 |
...
RecipientConID
...
currentOwner
...
FK to Constituents; reference to Person/Org Authority
...
ObjDeaccession
...
SaleDate
...
exitDate
...
...
ObjDeaccession
...
Terms
...
exitNote
...
...
ObjDeaccession
...
ProceedsRcvdDate
...
exitNote
...
...
ObjDeaccession
...
Remarks
...
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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_objectexitscs_objectexitdates (refreshed 2012-03-22)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT d.deaccessionID objectexitID, CONVERT (CASE WHENVARCHAR, d.saleDate, 101) exitDate FROM ObjDeaccession d WHERE d.dispositionMethod NOTobjectID 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.proceedsRcvdDateSELECT 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) 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 ; |
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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
- 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
...
Exit Note extract
field mapping
TMS table name | TMS field name | CSpace field name | notes | ||
---|---|---|---|---|---|
ObjDeaccession | Terms | exitNote |
| ||
ObjDeaccession | objectID ProceedsRcvdDate | reference to Object exitNote |
| ||
ObjDeaccession | deaccessionID | reference to ObjectExit | Remarks | exitNote |
|
ConXrefs | Remarks | exitNote |
|
sql query
view: cs_objectexitobjectrelationshipscs_objectexitnotes (refreshed 2012-03-22)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT d.deaccessionID objectexitID, d.terms exitNote FROM ObjDeaccession d WHERE d.objectID IN (SELECT CAST (d.objectID AS VARCHAR) objectID,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