   create table "ALLERGY"(
        "ALLERGY_ID" NUMBER not null,
       "VPID" VARCHAR(50) not null unique,
       "VLID" VARCHAR(50) not null unique,
       "SEVERITY_ID" NUMBER not null unique,
       "ALLERGY_EXTVALUE" VARCHAR(30),
       "ALLERGY_ASSIGNAUTHNM" VARCHAR(30),
       "ALLERGY_ROOTVALUE" VARCHAR(100) not null unique,
       "INFO_SOURCE_CATEGORY_CODESET" VARCHAR(30),
       "INFO_SOURCE_CATEGORY_VUID" VARCHAR(20),
       "INFO_SOURCE_CATEGORY_DSPLYNM" VARCHAR(100),
       "MECHANISM_NULLFLV" VARCHAR(20),
       "MECHANISM_CODESET" VARCHAR(30),
       "MECHANISM_VUID" VARCHAR(20),
       "MECHANISM_DSPLYNM" VARCHAR(100),
       "OBSERVATION_DT_LITERAL" VARCHAR(50),
       "OBSERVATION_DT_VALUE" NUMBER unique,
       "AGENT_ALLERGEN_CODESET" VARCHAR(30),
       "AGENT_ALLERGEN_VUID" VARCHAR(20),
       "AGENT_ALLERGEN_DSPLYNM" VARCHAR(100),
       "ALLERGY_STATUS_CODESET" VARCHAR(30),
       "ALLERGY_STATUS_VUID" VARCHAR(20),
       "ALLERGY_STATUS_DSPLYNM" VARCHAR(100),
       "LAST_UPDATED_DTTZ" TIMESTAMP default 'SYSTIMESTAMP' not null,
        constraint "ALLERGY_PK" primary key ("ALLERGY_ID")
    );
    comment on table "ALLERGY" is 'Adverse reactions are a subset of adverse events including those that are suspected of being related to the drug. A group of symptoms caused by allergic reaction to a drug (medication).  In general, adverse reactions to drugs are not uncommon, and almost any drug can cause an adverse reaction. Reactions range from irritating or mild side effects (such as nausea and vomiting), to allergic response including life-threatening anaphylaxis. Some drug reactions are idiosyncratic (unusual effects of the medication). For example, aspirin can cause nonallergic hives (no antibodies formed), or it may trigger asthma. Only a small proportion of these reactions are allergic in nature. Many individuals may confuse an uncomfortable but not serious side effect of a medicine, such as nausea, with a drug allergy, which can be life-threatening. Drug agents can be recorded at a drug class or as low as a specific drug ingredient.   In the VA the user can check from 4 different drug lists.  If non satisfy the specific drug then they can enter their own.  The 4 lists are: drug ingredients, drug classes, Local drugs, or the GMR Allergy.  The latter holds approved user entered drugs. Some common food allergies are milk, cheese, strawberries, peanuts or shell fish. Any other type of allergy other then a drug or food allergy will be categorized as unclassified.  Some common classifications are: pollen, dust, cat or dogs makup, etc.';
    comment on column "ALLERGY_ID" is 'Primary key to the Allergies table.  Uses sequence allergies_seq_id to populate.';
    comment on column "VPID" is 'Patient VHA Person Identifier';
    comment on column "VLID" is 'Service Location Identifier';
    comment on column "SEVERITY_ID" is 'Foreign Key to the Severity Table';
    comment on column "ALLERGY_EXTVALUE" is 'A character string as a unique identifier within the scope of the identifier root.';
    comment on column "ALLERGY_ASSIGNAUTHNM" is 'A human readable name or mnemonic for the assigning authority. The Assigning Authority Name has no computational value. The purpose of a Assigning Authority Name is to assist an unaided human interpreter of an II value to interpret the authority. Note: no automated processing must depend on the assigning authority name to be present in any form.';
    comment on column "ALLERGY_ROOTVALUE" is 'A unique identifier that guarantees the global uniqueness of the allergy_extvalue. The root alone may be the entire instance identifier. This string should be one of the following RUID, OID or UUID';
    comment on column "INFO_SOURCE_CATEGORY_CODESET" is 'Codeset of the info_source_category_vuid';
    comment on column "INFO_SOURCE_CATEGORY_VUID" is 'Code that indicates whether the Intolerance Condition was observed by a clinician (or was medically verified) or was merely reported by the patient.  Provides a measure of trustworthiness of the information. Note that in VistA, this is called Observed if observed by a clinician, or Historical if patient-reported.';
    comment on column "INFO_SOURCE_CATEGORY_DSPLYNM" is 'Provides a textual representation of a coded concept (info_source_category_vuid)';
    comment on column "MECHANISM_NULLFLV" is 'Why mechanism_vuid was null.';
    comment on column "MECHANISM_CODESET" is 'Codeset of the mechanism_vuid';
    comment on column "MECHANISM_VUID" is 'Code that the clinician will need to determine if the allergy can be categorized (mechanism) as either an allergy, an unknown or is a pharmacologic reaction.  Allergy: An allergy reaction must have symptoms (reaction)r consistent with an actual allergy - rash, hives, severe swelling, redness, itchiness, difficulty breathing (due to swelling around breathing areas), etc.  Unknown: An unknown reaction is a reaction experienced by the patient that cannot be explained by either of the other two mechanisms, for example, eating a pear and developing a cough or taking a blood pressure medication and developing right elbow pain. This type of reaction is consistent, disappears upon withdrawing the agent, and reappears when the agent or food item is restarted/eaten again. Pharmacologic A pharmacologic reaction is an adverse event rather then an allergy.  It is a reaction based on the pharmacologic properties of the drug and has to do with a reaction that can be expected due to the pharmacologic activity of the drug. It can occur when the patient takes the right amount or an amount greater or less than the recommended doses. Some examples are: an antidiarrheal agent (like Immodium) causing constipation, a laxative causing diarrhea, a beta blocker causing bradycardia (slow heart rate), furosemide (a diuretic) causing excessive urination, or a blood pressure medication causing low blood pressure (will be fully modeled in a subsequent phase).';
    comment on column "MECHANISM_DSPLYNM" is 'Provides a textual representation of a coded concept (mechanism_vuid)';
    comment on column "OBSERVATION_DT_LITERAL" is 'Literal representation of the observation date.';
    comment on column "OBSERVATION_DT_VALUE" is 'Date/time this allergy/adverse reaction was entered into the system in number format YYYYMMDDHH24MISS';
    comment on column "AGENT_ALLERGEN_CODESET" is 'Codeset of the agent_allergen_vuid';
    comment on column "AGENT_ALLERGEN_VUID" is 'Code with represents the drug, food, or other agent which caused the patient to have a reaction';
    comment on column "AGENT_ALLERGEN_DSPLYNM" is 'Provides a textual representation of a coded concept (agent_allergen_vuid)';
    comment on column "ALLERGY_STATUS_CODESET" is 'Codeset of the status_code_vuid';
    comment on column "ALLERGY_STATUS_VUID" is 'A code specifying the state of the Act. When the StatusCode is set to 'Nullified' iindicates the allergy was entered in error.';
    comment on column "ALLERGY_STATUS_DSPLYNM" is 'Provides a textual representation of a coded concept (allergy_status_vuid)';
    comment on column "LAST_UPDATED_DTTZ" is 'Timestamp to capture when the record was last inserted/updated.';

    alter table "ALLERGY"  
        add constraint "ALLERGY_SEVERITY_SVRTYID_FK" 
        foreign key ("SEVERITY_ID") 
        references "SEVERITY"("SEVERITY_ID");
    alter table "ALLERGY"  
        add constraint "ALLERGY_PERSON_VPID_FK" 
        foreign key ("VPID") 
        references "PERSON"("VPID");
    alter table "ALLERGY"  
        add constraint "ALLERGY_SRVLOC_VLID_FK" 
        foreign key ("VLID") 
        references "SERVICE_LOCATION"("VLID");
    create unique index "ALLERGY_ALRGYROOTVALUE_UNQ_I" on "ALLERGY"("ALLERGY_ROOTVALUE");
    create unique index "ALLERGY_PK" on "ALLERGY"("ALLERGY_ID");
    create unique index "ALLERGY_SEVERITY_SVRTYID_UNQ_I" on "ALLERGY"("SEVERITY_ID");
    create index "ALLERGY_LASTUPDTZ_NU_I" on "ALLERGY"("SYS_NC00024$");
    create index "ALLERGY_OBSDTVALU_NU_I" on "ALLERGY"("OBSERVATION_DT_VALUE");
    create index "ALLERGY_PERSON_VPID_FK_I" on "ALLERGY"("VPID");
    create index "ALLERGY_SRVLOC_VLID_FK_I" on "ALLERGY"("VLID");