...
| Table of Contents | ||
|---|---|---|
|
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)
| 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 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 ; |
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
ObjDeaccession | objectID | reference to Object |
|
ObjDeaccession | deaccessionID | reference to ObjectExit |
|
sql query
view: cs_objectexitobjectrelationshipsobjectexitobjectrelationships (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_objectexitdates (refreshed 2012-03-22)
| Code Block | ||||
|---|---|---|---|---|
| ||||
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)
| 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
...
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)
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
;
|
...