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