...
TMS table name | TMS field name | CSpace field name | notes | ||||
|---|---|---|---|---|---|---|---|
ObjDeaccession | DispositionMethod | exitReason | FK to DispositionMethods | ||||
ObjDeaccession | RecipientConID | currentOwner | FK to Constituents; reference to Person/Org Authority | ObjDeaccession | SaleDate | exitDate |
|
ObjDeaccession | Terms | exitNote |
| ||||
ObjDeaccession | ProceedsRcvdDate | exitNote |
| ||||
ObjDeaccession | Remarks | exitNote |
|
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
d.deaccessionID objectexitID,
(CASE WHEN d.dispositionMethod NOT 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.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)
;
|
...
| 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
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:
| 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') ; |