Generate_Table_Structure.sql

CREATE TABLE Specimen_Request (
TBR_REQUEST_TITLE varchar(255),
TBR_SOURCE_REQUEST varchar(255),
TBR_INSTITUTE_DESC varchar(255),
TBR_DEPT_DESC varchar(255),
TBR_REQUESTER_DESC varchar(255),
TBR_REQUEST_DT timestamp NULL DEFAULT NULL,
TBR_ILAB_NO varchar(255),
TBR_FINALIZE_FLAG varchar(255),
TBR_COST_CENTER bigint(20),
TBR_FUND_ID bigint(20),
TBR_MTA_FLAG varchar(255),
TBR_DISTRIBUTION_OPTION_DESC varchar(255),
TBR_HBC_ID varchar(255),
TBR_HBC_COMMITTEE_APPROVAL_DT timestamp NULL DEFAULT NULL,
TBR_MIN_UNIT bigint(20),
TBR_MTA_APPROVAL_DT timestamp NULL DEFAULT NULL,
TBR_PICKUP_ARRANGEMENT_DESC text,
TBR_PROSPECT_FLAG varchar(255),
TBR_TYPE_DESC varchar(255),
TBR_RESTROSPECT_FLAG varchar(255),
TBR_SPECIMEN_COLLECTION_METHOD text,
TBR_COMMENTS varchar(255),
TBR_CONTACT_NAME varchar(255),
TBR_SPECIMEN_USAGE_DESC text,
TBR_WAIVER_NO varchar(255),
TBR_MIN_SIZE_DESC varchar(255),
TBR_STS_DESC varchar(255),
TBR_SPECIAL_HANDLING_DESC varchar(255)
);

CREATE TABLE Specimen_Request_Details(
TBRD_SPECIMEN_TYPE_CD varchar(255),
TBRD_SITE_DESC varchar(255),
TBRD_SUB_SITE_DESC varchar(255),
TBRD_SUB2_SITE_DESC varchar(255),
TBRD_CATEGORY_DESC varchar(255),
TBRD_EXPECTED_AMT decimal(19,6),
TBRD_BILLING_AMT decimal(19,6),
TBRD_SOURCE_REQUEST varchar(255),
TBRD_HISTOLOGY_DESC varchar(255),
TBRD_HISTOLOGY_SUB_DESC varchar(255),
TBRD_HISTOLOGY_SUB2_DESC varchar(255),
TBRD_HISTOLOGY_SUB3_DESC varchar(255),
TBRD_QUALITY_DESC varchar(255),
TBRD_UNIT_DESC varchar(255),
TBRD_NOTES text
);

CREATE TABLE Distribution (
TBDS_SPECIMEN_REQUEST_ID varchar(255),
TBDS_DISTRIBUTION_DT timestamp NULL DEFAULT NULL,
TBDS_SOURCE_REQUEST varchar(255),
TBDS_BILLING_AMT decimal(19,6),
SPECIMEN_LABEL varchar(255),
TBDS_BILLING_DT timestamp NULL DEFAULT NULL
);


CREATE TABLE Accession(
TBA_CRDB_MRN varchar(50),
TBA_PT_DEIDENTIFICATION_ID bigint(20),
TBD_BANK_NUM varchar(255),
TBA_PROCUREMENT_DTE timestamp NULL DEFAULT NULL,
TBD_BANK_SUB_CD varchar(255),
TBA_DISEASE_DESC varchar(255),
TBA_ACCESSION_NUM varchar(255),
TBD_BANK_NOTE text,
TBA_DIAGNOSIS_NOTE varchar(255),
TBA_SURG_STRT_DT timestamp NULL DEFAULT NULL,
TBA_PATH_REVIEW_DT timestamp NULL DEFAULT NULL,
TBA_SURGEON_NAME varchar(255),
SURGICAL_PATH_REPORT text,
TBD_NUN_N varchar(255),
TBD_NUN_T varchar(255),
TBD_OCT_N varchar(255),
TBD_OCT_T varchar(255),
PARENT_SPECIMEN_LABEL varchar(255),
TBD_SPECIMEN_TYPE_DESC varchar(255),
TBA_SITE_DESC varchar(255),
TBA_SUB_SITE_DESC varchar(255),
TBA_SUB2_SITE_DESC varchar(255),
TBA_SITE_SIDE_DESC varchar(255),
TBA_TISSUE_TYPE_DESC varchar(255),
TBA_SITE_TEXT text,
TBA_RESECT_DT timestamp NULL DEFAULT NULL,
TBA_BIOBANK_RECEIPT_DT timestamp NULL DEFAULT NULL,
TBA_PART_NUM bigint(20),
TBA_SUB_PART_NUM bigint(20),
TBA_BIOBANK_TECH_NAME varchar(255),
TBA_TEMPERATURE_COND_DESC varchar(255),
TBA_BIOBANK_TEMPERATURE_COND_DESC varchar(255),
TBA_SITE_LOCATION_DESC varchar(255),
TBA_ACCESSION_RECEIPT_DT timestamp NULL DEFAULT NULL,
TBA_HISTOLOGY_DESC varchar(255),
TBA_HISTOLOGY_SUB_DESC varchar(255),
TBA_HISTOLOGY_SUB2_DESC varchar(255),
TBA_HISTOLOGY_SUB3_DESC varchar(255),
TBA_HARVEST_PA_NAME varchar(255)
);

CREATE TABLE Details(
PARENT_SPECIMEN_LABEL varchar(255),
ALIQUOT_LABEL varchar(255),
TBD_CATEGORY_DESC varchar(255),
TBD_SPECIMEN_TYPE_DESC varchar(255),
TBD_BANK_SEQ_NUM bigint(20),
TBD_VOL	decimal(19,6),
TBD_WEIGHT decimal(19,6),
TBD_SAMPLE_PROCESS_DT timestamp NULL DEFAULT NULL,
TBD_QUALITY_DESC varchar(255),
TBD_TIME_LAPSE_MIN varchar(255),
TBD_UNIT_DESC varchar(255),
TBD_SPECIAL_HANDLING_DESC varchar(255),
TBD_STERILE_CODE_DESC varchar(255),
TBD_BIOBANK_TECH_NAME varchar(255),
TBD_ADDTL_DETAILS text,
TBD_ADDTL_PROCESS_DT timestamp NULL DEFAULT NULL,
TBD_ADDTL_PROCESS_TECH_NAME varchar(255),
TBD_ADDTL_PROCESS_TEMPERATURE_DESC varchar(255)
);