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-03-22)
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) borrower, --? 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