Versions Compared

Old Version 7

changes.mady.by.user John W Keller

Saved on

compared with

New Version 8

changes.mady.by.user John W Keller

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: add separate exit date and exit note extracts

...

Table of Contents
maxlevel3

Object

...

Exit 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

Code Block
sql
sql
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 concatenationdispositionMethod values of 0 ("not assigned"), 9 ("number not used"), and 10 ("not found") are not imported

ObjectExit-Object Relationships extract

...

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

ObjectExit 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:

Code Block
sql
sql

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

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
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

ObjectExit 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:

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