The BIRTH_DATE column should instead be the is_over_18 column.

[kaladay]

The BIRTH_DATE column does not exist, but the is_over_18 does exist.

This is causing errors like:

org.camunda.bpm.engine.impl.pvm.PvmException: couldn't execute activity <serviceTask id="database_query_task_c8131f1d_49f7_4096_a06e_23173496b7c6" ...>: Invalid column name 'BIRTH_DATE'.
  ...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'BIRTH_DATE'.
  ...

Refactor the BIRTH_DATE date column uses into the is_over_18 boolean uses.

This affects the following workflows:

• e-resource
• patron

Additional note regarding the e-resource plugin (from PR #465):

This [e-resource] Job is not supposed to be scheduled to run. We do NOT want the view to be re-created each day etc. The SQL is here mainly as documentation for the query.
Which brings in the question on whether or not the e-resource workflow should be removed from QAs.

see:

• fw-registry/e-resource/nodes/updateView.json

  Line 9 in 21e01d3

  "query": "CREATE OR ALTER VIEW {{{e-resource-view}}} AS SELECT DISTINCT uin, CASE WHEN emp.tamu_netid IS NOT NULL THEN emp.tamu_netid ELSE emp.uin END as tamu_netid, CASE WHEN CAST(emp.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 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, CASE WHEN emp.tamu_netid IS NOT NULL THEN emp.tamu_netid ELSE emp.uin END as tamu_netid, CASE WHEN CAST(emp.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 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 uin, CASE WHEN emp.tamu_netid IS NOT NULL THEN emp.tamu_netid ELSE emp.uin END as tamu_netid, CASE WHEN CAST(emp.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.employees_retirees emp WHERE ((upper(emp.[TITLE]) LIKE '%ASSISTANT LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%ASSOCIATE LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%FULL LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%SENIOR LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%ASSISTANT UNIVERSITY LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%ASSOCIATE UNIVERSITY LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%EXECUTIVE ASSOCIATE UNIVERSITY LIBRARIAN%') OR (upper(emp.[TITLE]) LIKE '%LIBRARIAN DIRECTOR%') OR (upper(emp.[TITLE]) LIKE '%UNIVERSITY LIBRARIAN%')) AND (employment_status_name = 'Retired') AND (employee_type_name = 'Staff') AND upper(emp.adloc_system_member_name) IN ('TEXAS A&M UNIVERSITY', 'TEXAS A&M UNIVERSITY AT GALVESTON') UNION SELECT DISTINCT op.uin, CASE WHEN pi.tamu_netid IS NOT NULL THEN pi.tamu_netid ELSE pi.uin END as tamu_netid, CASE WHEN CAST(op.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.other_people op LEFT OUTER JOIN patron.person_identifiers pi ON op.uin = pi.uin WHERE op.data_provider IN ('HSCAFFILIATES', 'QATAR') AND op.affiliate_role IS NULL UNION SELECT DISTINCT op.uin, CASE WHEN pi.tamu_netid IS NOT NULL THEN pi.tamu_netid ELSE pi.uin END as tamu_netid, CASE WHEN CAST(op.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.other_people op LEFT OUTER JOIN patron.person_identifiers pi ON op.uin = pi.uin, patron.employees_retirees emp WHERE op.system_member = emp.adloc_system_member AND affiliate_role IN ('affiliate:remotecollaborator', '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, CASE WHEN stu.tamu_netid IS NOT NULL THEN stu.tamu_netid ELSE stu.uin END as tamu_netid, CASE WHEN CAST(stu.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.students stu WHERE enroll_status_name IN ('Enrolled', 'Not Enrolled') UNION SELECT DISTINCT oth.uin AS uin, CASE WHEN oth.tamu_netid IS NOT NULL THEN oth.tamu_netid ELSE oth.uin END as tamu_netid, CASE WHEN CAST(oth.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.library_other_people_exceptions oth UNION SELECT DISTINCT lib.uin AS uin, lib.tamu_netid AS tamu_netid, CASE WHEN CAST(lib.BIRTH_DATE AS DATE) <= DATEADD(YEAR, - 18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18 FROM patron.library_retirees lib;",

• fw-registry/patron/nodes/others.json

  Line 12 in 21e01d3

  "query": "SELECT DISTINCT CASE WHEN op.tamu_netid IS NOT NULL THEN op.tamu_netid ELSE op.uin END AS username, op.uin AS externalSystemId, CASE WHEN pi.id_card_num IS NOT NULL THEN pi.id_card_num ELSE op.uin END AS barcode, 'true' AS active, 'fast' AS patronGroup, op.last_name AS personal_lastName, op.first_name AS personal_firstName, op.middle_name AS personal_middleName, op.tamu_preferred_alias AS personal_email, op.office_phone AS personal_phone, NULL AS addresses_permanent_addressTypeId, NULL AS addresses_permanent_countryId, NULL AS addresses_permanent_addressLine1, NULL AS addresses_permanent_addressLine2, NULL AS addresses_permanent_city, NULL AS addresses_permanent_region, NULL AS addresses_permanent_postalCode, NULL AS addresses_temporary_addressTypeId, NULL AS addresses_temporary_addressLine2, NULL AS addresses_temporary_addressLine1, NULL AS addresses_temporary_city, NULL AS addresses_temporary_region, NULL AS addresses_temporary_postalCode, NULL AS departments_0, CASE WHEN CAST(op.BIRTH_DATE AS DATE) <= DATEADD(YEAR, -18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18, format(getdate() + 200, 'yyyy-MM-dd') AS expirationDate FROM itsql.cis.patron.other_people op LEFT OUTER JOIN itsql.cis.patron.person_identifiers pi ON op.uin = pi.uin LEFT OUTER JOIN itsql.cis.patron.students stu ON op.uin = stu.uin LEFT OUTER JOIN itsql.cis.patron.employees_retirees emp ON op.uin = emp.uin WHERE stu.uin IS NULL AND emp.uin IS NULL AND op.tamu_preferred_alias IS NOT NULL AND( ( affiliate_role IN ( 'affiliate:remotecollaborator', 'affiliate:continuingeducationstudent', 'affiliate:clinicaltrainee', 'affiliate:faculty:future', 'affiliate:graduateassistant:future', 'affiliate:librarian', 'affiliate:medicalresident', 'affiliate:regent', 'affiliate:staff:future', 'affiliate:usda', 'affiliate:veteransprogram', 'affiliate:visitingscholar', 'employee:faculty:retired', 'faculty:adjunct') AND system_member IN ( '01', '02', '06', '07', '09', '10', '11', '12', '20', '23', '26', '28' ) ) OR (op.data_provider IN ('HSCAFFILIATES', 'QATAR')) ) AND op.last_name IS NOT NULL UNION SELECT DISTINCT CASE WHEN op.tamu_netid IS NOT NULL THEN op.tamu_netid ELSE op.uin END AS username, op.uin AS externalSystemId, CASE WHEN pi.id_card_num IS NOT NULL THEN pi.id_card_num ELSE op.uin END AS barcode, 'true' AS active, 'fast' AS patronGroup, op.last_name AS personal_lastName, op.first_name AS personal_firstName, op.middle_name AS personal_middleName, op.tamu_preferred_alias AS personal_email, op.office_phone AS personal_phone, NULL AS addresses_permanent_addressTypeId, NULL AS addresses_permanent_countryId, NULL AS addresses_permanent_addressLine1, NULL AS addresses_permanent_addressLine2, NULL AS addresses_permanent_city, NULL AS addresses_permanent_region, NULL AS addresses_permanent_postalCode, NULL AS addresses_temporary_addressTypeId, NULL AS addresses_temporary_addressLine2, NULL AS addresses_temporary_addressLine1, NULL AS addresses_temporary_city, NULL AS addresses_temporary_region, NULL AS addresses_temporary_postalCode, NULL AS departments_0, CASE WHEN CAST(BIRTH_DATE AS DATE) <= DATEADD(YEAR, -18, CAST(GETDATE() AS DATE)) THEN 'true' ELSE 'false' END AS is_over_18, format(getdate() + 200, 'yyyy-MM-dd') AS expirationDate FROM itsql.cis.patron.library_other_people_exceptions op LEFT OUTER JOIN itsql.cis.patron.person_identifiers pi ON op.uin = pi.uin",

see also:

• The patron workflow should update the custom field Is Over 18 #457
• E-resource needs to be updated to work with MetaDB #423
• Ensure that the sql can run on mssql3 #465 (this PR will likely conflict with the necessary changes here)

Follow up Notes

There BIRTH_DATE might have been a temporary solution because the over 18 boolean might not have been working as expected at the time the code changes were made.
The reason why the database does not have this BIRTH_DATE is currently unclear.

The e-resource workflow is ssupposed to be an example and is not intended to work.
This leaves the patron workflow as the one of concern.