Versions Compared

Old Version 7

changes.mady.by.user John W Keller

Saved on

compared with

New Version Current

changes.mady.by.user Michael T. Black

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

...

TMS table name

TMS field name

CSpace field name

notes

Objects

ObjectNumber

exitNumber

prepended with 'EX'

ObjDeaccession

DispositionMethod

exitReason

FK to DispositionMethods

ObjDeaccession

SaleDate

exitDate

 

ObjDeaccession

Terms

exitNote

 

ObjDeaccession

ProceedsRcvdDate

exitNote

 

ObjDeaccession

Remarks

exitNote

 

sql query

view:  cs_objectexits (refreshed 2012-03-22)

Code Block
sql
sql
SELECT
 d.deaccessionID objectexitID,
 'EX' + o.ObjectNumber exitNumber,
 (CASE WHEN d.dispositionMethod NOT IN (0, 9, 10) THEN lower(m.dispositionMethod) END) exitReason,
FROM
CONVERT (VARCHAR,ObjDeaccession 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
 
 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
sql
sql

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

  • 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 Date extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjDeaccession

objectID SaleDate

reference to Object exitDate

 

ObjDeaccession ConXrefs

deaccessionID

reference to ObjectExit DateBegin OR DisplayDate

exitDate

 

sql query

view:  cs_objectexitobjectrelationshipscs_objectexitdates (refreshed 2012-03-22)

Code Block
sql
sql
SELECT
  CASTd.deaccessionID objectexitID,
 CONVERT (VARCHAR, d.saleDate, 101) exitDate
FROM
 ObjDeaccession d
WHERE
 d.objectID AS VARCHAR) 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 != FROM
 ObjDeaccession d
WHERE0 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
sql
sql
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
sql
sql

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