PAHMA Loans data mapping

This page contains information about PAHMA's data migration of Loans and related info.
See parent page on PAHMA data migration ETL work.

Loans Out extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

Loans

loanNumber

loanOutNumber

 

Loans

loanPurposeID

loanPurpose

FK to LoanPurposes

Loans

constituentID

borrower

FK to Constituents; reference to Person Authority

Loans

beginISODate

loanOutDate

 

Loans

endISODate

loanReturnDate

 

Loans

requestDate

loanStatusDate

with loanStatus of 'requested'

Loans

requestedBy

loanStatusNote

with loanStatus of 'requested'

Loans

description

loanOutNote

 

Loans

contact

loanOutNote

 

Loans

remarks

loanOutNote

 

sql query

view: cs_loansout (refreshed 2012-03-22)

SELECT
 l.loanID loanoutID,
 l.loanNumber loanOutNumber,
 (CASE WHEN l.loanPurposeID != 0 THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanpurpose):item:name(' + lower(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.loanPurpose, ' ', '_'), '-', '_'), ':', ''), '.', ''), ',', '')) + ')''' + p.loanPurpose + '''' END) loanPurpose,
 (CASE WHEN l.constituentID != -1 THEN l.constituentID END) borrower,
 l.beginISODate loanOutDate,
 l.endISODate loanReturnDate,
 (CASE WHEN l.requestDate IS NOT NULL OR l.requestedBy IS NOT NULL THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanstatus):item:name(requested)''requested''' END) loanStatus,
 CONVERT(VARCHAR, l.requestDate, 101) loanStatusDate,
 (CASE WHEN l.requestedBy IS NOT NULL THEN 'Requested by: ' + l.requestedBy END) loanStatusNote,
 l.description loanOutNote_1,
 (CASE WHEN l.contact IS NOT NULL THEN 'Contact: "' + l.contact + '"' END) loanOutNote_2,
 l.remarks loanOutNote_3
FROM
 Loans l
 JOIN LoanPurposes p ON l.loanPurposeID = p.loanPurposeID
WHERE
 l.loanin = 0
 ;

notes

  • NOTE: the loanOutNote_1, loanOutNote_2, and loanOutNote_3 fields in this query should be concatenated into a single field loanOutNote, separated by line breaks; this cannot be done in this query because fields of type TEXT do not allow concatenation

LoanOut-Object Relationships extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

LoanObjXrefs

objectID

reference to Object

 

LoanObjXrefs

loanID

reference to LoanOut

 

sql query

view: cs_loanoutobjectrelationships (refreshed 2012-03-22)

SELECT
 CAST (x.objectID AS VARCHAR) objectID,
 x.loanID loanoutID
FROM
 LoanObjXrefs x
 JOIN loans l ON x.loanID = l.loanID
WHERE
 l.loanin = 0
;

notes

LoanOut "Borrower's authorizer" extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

loanoutID

reference to LoanOut

 

ConXrefs

constituentID

borrowersAuthorizer

 

sql query

view: cs_loanoutborrowersauthorizers (refreshed 2012-03-22)

SELECT
 x.ID loanoutID,
 x.constituentID borrowersAuthorizer
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 0
 AND x.roleID = 71
;

notes

  • code 71 (Sponsor) is mapped to Borrower's authorizer field

LoanOut "Borrower's contact" extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

loanoutID

reference to LoanOut

 

ConXrefs

constituentID

borrowersContact

 

sql query

view: cs_loanoutborrowerscontacts (refreshed 2012-03-22)

SELECT
 x.ID loanoutID,
 x.constituentID borrowersContact
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 0
 AND x.roleID IN (79, 82)
;

notes

  • code 79 (Borrower's contact) and code 82 (Out-loan organizer) are mapped to Borrower's contact field

Loans In extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

Loans

loanNumber

loanInNumber

 

Loans

loanPurposeID

loanPurpose

FK to LoanPurposes

Loans

constituentID

lender

FK to Constituents; reference to Person Authority

Loans

beginISODate

loanInDate

 

Loans

endISODate

loanReturnDate

 

Loans

requestDate

loanStatusDate

with loanStatus of 'requested'

Loans

requestedBy

loanStatusNote

with loanStatus of 'requested'

Loans

description

loanInNote

 

Loans

contact

loanInNote

 

Loans

remarks

loanInNote

 

sql query

view: cs_loansin (refreshed 2012-04-10)

SELECT
 l.loanID loaninID,
 l.loanNumber loanInNumber,
 (CASE WHEN l.loanPurposeID != 0 THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanpurpose):item:name(' + lower(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.loanPurpose, ' ', '_'), '-', '_'), ':', ''), '.', ''), ',', '')) + ')''' + p.loanPurpose + '''' END) loanPurpose,
 (CASE WHEN l.constituentID != -1 THEN l.constituentID END) lender, --?
 l.beginISODate loanInDate,
 l.endISODate loanReturnDate,
 (CASE WHEN l.requestDate IS NOT NULL OR l.requestedBy IS NOT NULL THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanstatus):item:name(requested)''requested''' END) loanStatus,
 CONVERT(VARCHAR, l.requestDate, 101) loanStatusDate,
 (CASE WHEN l.requestedBy IS NOT NULL THEN 'Requested by: ' + l.requestedBy END) loanStatusNote,
 l.description loanInNote_1,
 (CASE WHEN l.contact IS NOT NULL THEN 'Contact: "' + l.contact + '"' END) loanInNote_2,
 l.remarks loanInNote_3
FROM
 Loans l
 JOIN LoanPurposes p ON l.loanPurposeID = p.loanPurposeID
WHERE
 l.loanin = 1
 ;

notes

  • NOTE: the loanInNote_1, loanInNote_2, and loanInNote_3 fields in this query should be concatenated into a single field loanInNote, separated by line breaks; this cannot be done in this query because fields of type TEXT do not allow concatenation

LoanIn-Object Relationships extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

LoanObjXrefs

objectID

reference to Object

 

LoanObjXrefs

loanID

reference to LoanIn

 

sql query

view: cs_loaninobjectrelationships (refreshed 2012-03-22)

SELECT
 CAST (x.objectID AS VARCHAR) objectID,
 x.loanID loaninID
FROM
 LoanObjXrefs x
 JOIN loans l ON x.loanID = l.loanID
WHERE
 l.loanin = 1
;

notes

LoanIn "Lender's contact" extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

loaninID

reference to LoanIn

 

ConXrefs

constituentID

lendersContact

 

sql query

view: cs_loaninlenderscontacts (refreshed 2012-03-22)

SELECT
 x.ID loaninID,
 x.constituentID lendersContact
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 1
 AND x.roleID IN (76, 77, 79)
;

notes

  • code 76 (In-loan organizer), code 77 (Lender's contact), and code 79 (Borrower's contact) are mapped to Lender's contact field