/* *****************************************************************************
********************************************************************************

SCRIPT NAME:  Tables.sql 
PURPOSE:      Create or recreate views required by Clinical Trial Object Model. 
AUTHOR:       Hugh Reinhart, General Dynamics
DATE:         September 2007 
VERSION:      CTOM v0.53
NOTE:         This script is a MySql verson of the script for Oracle
              developed originally by ScenPro.

********************************************************************************
***************************************************************************** */

TEE VIEWS.LST;

CREATE OR REPLACE VIEW PERSON_VIEW
(ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, BIRTH_DATE, BIRTH_DATE_ORIG, 
 TELECOM_ADDRESS, ADMINISTRATIVE_GENDER_CODE, STREET_ADDRESS, CITY, STATE, ZIP_CODE, COUNTRY_CODE, PHONE, EDUCATION_LEVEL_CODE, ETHNIC_GROUP_CODE, 
 HOUSEHOLD_INCOME_CODE, MARITAL_STATUS_CODE, RACE_CODE, EMPLOYMENT_STATUS_CODE, EMPLOYMENT_STATUS_OTHER_TEXT, 
 SOURCE, SOURCE_EXTRACT_DATE, CTOM_INSERT_DATE, CTOM_UPDATE_DATE, SECURITY_KEY, SECURITY_ENABLED)
AS 
SELECT ID
       , LAST_NAME
       , FIRST_NAME
       , MIDDLE_NAME
       , BIRTH_DATE
       , BIRTH_DATE_ORIG
       , TELECOM_ADDRESS
       , ADMINISTRATIVE_GENDER_CODE
       , STREET_ADDRESS
       , CITY
       , STATE
       , ZIP_CODE
       , COUNTRY_CODE
       , PHONE
       , EDUCATION_LEVEL_CODE
       , ETHNIC_GROUP_CODE
       , HOUSEHOLD_INCOME_CODE
       , MARITAL_STATUS_CODE
       , RACE_CODE
       , EMPLOYMENT_STATUS_CODE
       , EMPLOYMENT_STATUS_OTHER_TEXT
       , SOURCE
       , SOURCE_EXTRACT_DATE
       , CTOM_INSERT_DATE
       , CTOM_UPDATE_DATE
       , SECURITY_KEY
       , 'F'
  FROM INVESTIGATOR
  UNION
  SELECT ID
       , LAST_NAME
       , FIRST_NAME
       , MIDDLE_NAME
       , BIRTH_DATE
       , BIRTH_DATE_ORIG
       , TELECOM_ADDRESS
       , ADMINISTRATIVE_GENDER_CODE
       , STREET_ADDRESS
       , CITY
       , STATE
       , ZIP_CODE
       , COUNTRY_CODE
       , PHONE
       , EDUCATION_LEVEL_CODE
       , ETHNIC_GROUP_CODE
       , HOUSEHOLD_INCOME_CODE
       , MARITAL_STATUS_CODE
       , RACE_CODE
       , EMPLOYMENT_STATUS_CODE
       , EMPLOYMENT_STATUS_OTHER_TEXT
       , SOURCE
       , SOURCE_EXTRACT_DATE
       , CTOM_INSERT_DATE
       , CTOM_UPDATE_DATE
       , SECURITY_KEY
       , 'T'
  FROM PARTICIPANT;


CREATE OR REPLACE VIEW ORGANIZATION_VIEW
(ID, NAME, DESCRIPTION_TEXT, STATUS_CODE, STATUS_DATE, 
 STATUS_DATE_ORIG, STREET_ADDRESS, CITY, STATE_CODE, POSTAL_CODE, 
 COUNTRY_CODE, TELECOM_ADDRESS, SOURCE, SOURCE_EXTRACT_DATE, CTOM_INSERT_DATE, 
 CTOM_UPDATE_DATE)
AS 
SELECT ID, CONCAT('(' , NCI_INSTITUTE_CODE, ')')
         , DESCRIPTION_TEXT, STATUS_CODE, STATUS_DATE, STATUS_DATE_ORIG
         , STREET_ADDRESS, CITY, STATE_CODE, POSTAL_CODE, COUNTRY_CODE, TELECOM_ADDRESS
         , SOURCE, SOURCE_EXTRACT_DATE, CTOM_INSERT_DATE, CTOM_UPDATE_DATE
FROM HEALTHCARE_SITE
WHERE NAME IS NULL
UNION
SELECT ID, NAME
         , DESCRIPTION_TEXT, STATUS_CODE, STATUS_DATE, STATUS_DATE_ORIG
         , STREET_ADDRESS, CITY, STATE_CODE, POSTAL_CODE, COUNTRY_CODE, TELECOM_ADDRESS
         , SOURCE, SOURCE_EXTRACT_DATE, CTOM_INSERT_DATE, CTOM_UPDATE_DATE
FROM HEALTHCARE_SITE
WHERE NAME IS NOT NULL
  AND NCI_INSTITUTE_CODE IS NULL
UNION
SELECT ID, CONCAT(NAME, ' (', NCI_INSTITUTE_CODE, ')')
         , DESCRIPTION_TEXT, STATUS_CODE, STATUS_DATE, STATUS_DATE_ORIG
         , STREET_ADDRESS, CITY, STATE_CODE, POSTAL_CODE, COUNTRY_CODE, TELECOM_ADDRESS
         , SOURCE, SOURCE_EXTRACT_DATE, CTOM_INSERT_DATE, CTOM_UPDATE_DATE
FROM HEALTHCARE_SITE
WHERE NAME IS NOT NULL
  AND NCI_INSTITUTE_CODE IS NOT NULL
  ;



NOTEE;

