hismith
commented
3 years ago Here's the sql for this task. This sql is run against the same registry database as the DivIT registry patron update. Short term, the only thing that needs to be done is to put the sql someplace from which it can be run successfully against the registry database and giving me access to edit it if needed. Long term you need an effective way for someone to edit the sql when changes are needed, unless DI is going to retain that responsibility.
//
/ Reflects changes to authorization lists based on 2015 review of /
/ appropriate user groups. /
//
set escape on
create or replace view library_eresources as
select distinct uin, tamu_netid
from patron.employees_retirees emp
where upper(emp.adloc_system_member_name) in (
'TEXAS A\&M AGRILIFE EXTENSION SERVICE',
'TEXAS A\&M AGRILIFE RESEARCH',
'TEXAS A\&M ENGINEERING EXPERIMENT STATION',
'TEXAS A\&M ENGINEERING EXTENSION SERVICE',
'TEXAS A\&M FOREST SERVICE',
'TEXAS A\&M HEALTH',
'TEXAS A\&M SYSTEM OFFICES',
'TEXAS A\&M SYSTEM SHARED SERVICE CENTER',
'TEXAS A\&M SYSTEM SPONSORED RESEARCH SERVICES',
'TEXAS A\&M SYSTEM TECHNOLOGY COMMERCIALIZATION',
'TEXAS A\&M TRANSPORTATION INSTITUTE',
'TEXAS A\&M UNIVERSITY',
'TEXAS A\&M UNIVERSITY AT GALVESTON',
'TEXAS A\&M VETERINARY MEDICAL DIAGNOSTIC LABORATORY'
)
and employment_status_name in ('Active', 'Working Retiree','Leave of Absence','Graduate Fellow','Future')
union
select distinct uin, tamu_netid
from patron.employees_retirees emp
where upper(emp.adloc_system_member_name) in (
'TEXAS A\&M AGRILIFE EXTENSION SERVICE',
'TEXAS A\&M AGRILIFE RESEARCH',
'TEXAS A\&M ENGINEERING EXPERIMENT STATION',
'TEXAS A\&M ENGINEERING EXTENSION SERVICE',
'TEXAS A\&M FOREST SERVICE',
'TEXAS A\&M HEALTH',
'TEXAS A\&M SYSTEM OFFICES',
'TEXAS A\&M SYSTEM SHARED SERVICE CENTER',
'TEXAS A\&M SYSTEM SPONSORED RESEARCH SERVICES',
'TEXAS A\&M SYSTEM TECHNOLOGY COMMERCIALIZATION',
'TEXAS A\&M TRANSPORTATION INSTITUTE',
'TEXAS A\&M UNIVERSITY',
'TEXAS A\&M UNIVERSITY AT GALVESTON',
'TEXAS A\&M VETERINARY MEDICAL DIAGNOSTIC LABORATORY'
)
and employment_status_name = 'Retired'
and employee_type_name = 'Faculty'
union
select distinct op.uin, pi.tamu_netid
from patron.other_people op,
patron.person_identifiers pi
where op.uin = pi.uin(+)
and op.data_provider in ('HSCAFFILIATES','QATAR')
and op.affiliate_role is null
union
select distinct op.uin, pi.tamu_netid
from patron.other_people op,
patron.person_identifiers pi,
employees_retirees emp
where op.uin = pi.uin(+)
and op.system_member = emp.adloc_system_member
and affiliate_role in (
'affiliate:continuingeducationstudent',
'affiliate:clinicaltrainee',
'affiliate:faculty:future',
'affiliate:graduateassistant:future',
'affiliate:hsc',
'affiliate:librarian',
'affiliate:medicalresident',
'affiliate:regent',
'affiliate:staff:future',
'affiliate:usda',
'affiliate:veteransprogram',
'affiliate:visitingscholar',
'employee:faculty:retired',
'faculty:adjunct'
)
and upper(emp.adloc_system_member_name) in (
'TEXAS A\&M AGRILIFE EXTENSION SERVICE',
'TEXAS A\&M AGRILIFE RESEARCH',
'TEXAS A\&M ENGINEERING EXPERIMENT STATION',
'TEXAS A\&M ENGINEERING EXTENSION SERVICE',
'TEXAS A\&M FOREST SERVICE',
'TEXAS A\&M HEALTH',
'TEXAS A\&M SYSTEM OFFICES',
'TEXAS A\&M SYSTEM SHARED SERVICE CENTER',
'TEXAS A\&M SYSTEM SPONSORED RESEARCH SERVICES',
'TEXAS A\&M SYSTEM TECHNOLOGY COMMERCIALIZATION',
'TEXAS A\&M TRANSPORTATION INSTITUTE',
'TEXAS A\&M UNIVERSITY',
'TEXAS A\&M UNIVERSITY AT GALVESTON',
'TEXAS A\&M VETERINARY MEDICAL DIAGNOSTIC LABORATORY'
)
union
select distinct stu.uin, tamu_netid
from students stu
where enroll_status_name in ('Enrolled','Not Enrolled')
/
SQL (ringle.tamu.edu:/export/home/voyager/sql/am/library_eresources/library_eresources.sql), runs against DivIT registry database, to create a list of users who are authorized to access electronic resources licensed by TAMU Libraries.
API to perform the query. On-Demand.