/******************************************************************************
   NAME:       new_allergies_load3x_arms.sql
   PURPOSE:    This script will create Allergies records that map to 
               VHIM version 3.6.2  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        10/19/2007  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        10/30/2007  M. Talbot        - added hardcoding of PATIENT_ASSIGNING_FACILITY to 'DNS   -605'
   1.3        01/08/2008  M. Jacobsen      - remove common schema
******************************************************************************/
set serveroutput on
DECLARE
   prev_num                     NUMBER          := 0;
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_numa                  NUMBER          := 0;
   random_numc                  NUMBER          := 0;
   random_numv                  NUMBER          := 0;
   random_numi                  NUMBER          := 0;
   random_numn                  NUMBER          := 0;
   random_nump                  NUMBER          := 0;
   birthnum                     NUMBER          := 0;
   birth_dt                     VARCHAR2 (30);
   nullify_author               NUMBER          := 0;
   authorppid                   NUMBER;
   chartppid                    NUMBER;
   verifyppid                   NUMBER;
   idbandppid                   NUMBER;
   rsnnulppid                   NUMBER;
   start_date                   NUMBER;
   comment_evnt_id              NUMBER;
   obs_date                     VARCHAR2 (30);
   upd_date                     VARCHAR2 (30);
   ppa_date                     VARCHAR2 (30);
   ppc_date                     VARCHAR2 (30);
   ppv_date                     VARCHAR2 (30);
   ppi_date                     VARCHAR2 (30);
   ppn_date                     VARCHAR2 (30);
   ppp_date                     VARCHAR2 (30);
   end_date                     NUMBER;
   allergy_description          VARCHAR2 (400);
   patient_id                   NUMBER;
   pract_vpid                   VARCHAR2 (30);
   info_src_d                   VARCHAR2 (100);
   info_src_v                   VARCHAR2 (20);
   mechanism_d                  VARCHAR2 (100);
   mechanism_v                  VARCHAR2 (20);
   allgytype_d                  VARCHAR2 (100);
   allgytype_v                  VARCHAR2 (20);
   agent_allerg_d               VARCHAR2 (100);
   agent_allerg_v               VARCHAR2 (20);
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (20);
   severity_cd_d                VARCHAR2 (100);
   severity_cd_v                VARCHAR2 (20);
   reaction_cd_d                VARCHAR2 (100);
   reaction_cd_v                VARCHAR2 (20);
   hospital_cd_d                VARCHAR2 (100);
   hospital_cd_v                VARCHAR2 (20);
   nullify_cd_d                 VARCHAR2 (100);
   nullify_cd_v                 VARCHAR2 (20);
   drging_cd_d                  VARCHAR2 (100);
   drging_cd_v                  VARCHAR2 (20);
   drgcls_cd_d                  VARCHAR2 (100);
   drgcls_cd_v                  VARCHAR2 (20);
   allergy_seq_id_val           NUMBER;
   pract_part_seq_id_val        NUMBER;
   reaction_seq_id_val          NUMBER;
   known_alrgy_null             VARCHAR2 (20);
   known_alrgy_val              CHAR (1);
   patient_seq_id_val           NUMBER;
   gender_code_d                VARCHAR2 (100);
   gender_code_v                VARCHAR2 (30);
   race_code_d                  VARCHAR2 (100);
   race_code_v                  VARCHAR2 (30);
   ethnic_code_d                VARCHAR2 (100);
   ethnic_code_v                VARCHAR2 (30);
   gmr_alrgy_d                  varchar2(100);
   gmr_alrgy_v                  varchar2(30);
   alt_codsys_dc                VARCHAR2(100);
   alt_display_dc               VARCHAR2(100);
   alt_code_dc                  VARCHAR2(100);
   alrg_asmt_d                  varchar2(100);
   alrg_asmt_v                  varchar2(30);
   v_vpid                       VARCHAR2 (30);
   starting_patient_vpid        NUMBER;
   fixed_date			        DATE;

   
BEGIN
   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'Allergies Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));

   fixed_date := TO_DATE('07-04-07 17:07:06','MM-DD-YY HH24:MI:SS');

   allergy_description := 'Provides a textual narrative of the Reaction observed for the intolerance event of the patient.';

-- Set starting_patient_vpid
   IF &4 <= 0 
   THEN
       starting_patient_vpid :=1;
   ELSE
       starting_patient_vpid := &4 - 1;
   END IF;

-- Loop thru how many allergies you want per patient
   FOR z IN 1 .. &1
   LOOP
       -- Loop thru how many patients to assign allergies to
       FOR jj in 1 .. &2
	   LOOP

         -- Set allergy type
         random_num := round(DBMS_RANDOM.VALUE (1, 5));
         CASE random_num
            WHEN '1' THEN
               allgytype_d := 'FOOD';
               allgytype_v := '98547';
            WHEN '2' THEN
               allgytype_d := 'DRUG';
               allgytype_v := '98548';
            WHEN '3' THEN
               allgytype_d := 'FOOD/DRUG';
               allgytype_v := '98548';
            WHEN '4' THEN
               allgytype_d := 'OTHER';
               allgytype_v := '98549';
            ELSE
               allgytype_d    := 'OTHER';
               allgytype_v    := '98549';
         END CASE;
         
         -- Set gmr_alrgy
         random_num := round(DBMS_RANDOM.VALUE (1, 5));
         CASE random_num
            WHEN '1' THEN
               gmr_alrgy_d := 'HAYFEVER';
               gmr_alrgy_v := '879451';
            WHEN '2' THEN
               gmr_alrgy_d := 'COUGH';
               gmr_alrgy_v := '879452';
            WHEN '3' THEN
               gmr_alrgy_d := 'HIVES';
               gmr_alrgy_v := '879453';
            WHEN '4' THEN
               gmr_alrgy_d := 'RASH';
               gmr_alrgy_v := '879454';
            ELSE
               gmr_alrgy_d    := 'SWELLING';
               gmr_alrgy_v    := '879455';
         END CASE;
         
         -- Set information_source_category_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 2));
         CASE random_num
            WHEN '1' THEN
               info_src_d := 'OBSERVED';
               info_src_v := '1002';
            ELSE
               info_src_d     := 'HISTORICAL';
               info_src_v     := '1003';
         END CASE;

         -- Set mechanism_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               mechanism_d := 'ALLERGY';
               mechanism_v := '1027';
            WHEN '2' THEN
               mechanism_d := 'PHARMACOLOGIC';
               mechanism_v := '1028';
            ELSE
               mechanism_d    := 'UNKNOWN';
               mechanism_v    := '1026';
         END CASE;

         -- Set agent_allergen_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 14));
         CASE random_num
            WHEN '1' THEN
               agent_allerg_d := 'ANIMAL DANDER';
               agent_allerg_v := '1047';
            WHEN '2' THEN
               agent_allerg_d := 'ASPIRIN';
               agent_allerg_v := '1035';
            WHEN '3' THEN
               agent_allerg_d := 'BACLOFEN ORA';
               agent_allerg_v := '1029';
            WHEN '4' THEN
               agent_allerg_d := 'COUGH SYRUP AND SPINACH';
               agent_allerg_v := '1044';
            WHEN '5' THEN
               agent_allerg_d := 'DUST';
               agent_allerg_v := '1048';
            WHEN '6' THEN
               agent_allerg_d := 'GRASSES';
               agent_allerg_v := '1049';
            WHEN '7' THEN
               agent_allerg_d := 'HAND CLEANSER AND STRAWBERRIES';
               agent_allerg_v := '1043';
            WHEN '8' THEN
               agent_allerg_d := 'MAGNALOX PLUS';
               agent_allerg_v := '1032';
            WHEN '9' THEN
               agent_allerg_d := 'PEACHES';
               agent_allerg_v := '1036';
            WHEN '10' THEN
               agent_allerg_d := 'PEANUT BUTTER';
               agent_allerg_v := '1037';
            WHEN '11' THEN
               agent_allerg_d := 'SHRIMP';
               agent_allerg_v := '1040';
            WHEN '12' THEN
               agent_allerg_d := 'TREES';
               agent_allerg_v := '1050';
            WHEN '13' THEN
               agent_allerg_d := 'VIAGRA AND SHELLFISH';
               agent_allerg_v := '1042';
            ELSE
               agent_allerg_d     := 'TETRACYCLINE';
               agent_allerg_v     := '1034';
         END CASE;

         -- Set status_code_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               status_code_d := 'ACTIVE';
               status_code_v := '25';
            WHEN '2' THEN
               status_code_d := 'OPEN';
               status_code_v := '11';
            ELSE
               status_code_d      := 'WAIT';
               status_code_v      := '37';
         END CASE;

         -- Set severity_code_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               severity_cd_d := 'MILD';
               severity_cd_v := '1055';
            WHEN '2' THEN
               severity_cd_d := 'MODERATE';
               severity_cd_v := '1051';
            ELSE
               severity_cd_d    := 'SEVERE';
               severity_cd_v    := '1058';
         END CASE;
         
         -- Set hospital code
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         CASE random_num
            WHEN '1' THEN
               hospital_cd_d := 'VA SALT LAKE CITY HEALTH CARE SYSTEM';
               hospital_cd_v := '660';
            WHEN '2' THEN
               hospital_cd_d := 'TUSCALOOSA VA MEDICAL CENTER';
               hospital_cd_v := '679';
            WHEN '3' THEN
               hospital_cd_d := 'CENTRAL TEXAS VETERANS HEALTH CARE SYSTEM';
               hospital_cd_v := '674';
            WHEN '4' THEN
               hospital_cd_d := 'PHILADELPHIA VA MEDICAL CENTER';
               hospital_cd_v := '642';
            WHEN '5' THEN
               hospital_cd_d := 'CINCINNATI VA MEDICAL CENTER';
               hospital_cd_v := '539';
            WHEN '6' THEN
               hospital_cd_d := 'JAMES J. PETERS VA MEDICAL CENTER';
               hospital_cd_v := '526';
            WHEN '7' THEN
               hospital_cd_d := 'VA GULF COAST VETERANS HEALTH CARE SYSTEM';
               hospital_cd_v := '520';
            WHEN '8' THEN
               hospital_cd_d := 'RICHARD L. ROUDEBUSH VA MEDICAL CENTER';
               hospital_cd_v := '583';
            WHEN '9' THEN
               hospital_cd_d := 'MIAMI VA HEALTHCARE SYSTEM';
               hospital_cd_v := '546';
            ELSE
               hospital_cd_d    := 'JONATHAN M. WAINWRIGHT MEMORIAL VA MEDICAL CENTER';
               hospital_cd_v    := '687';
         END CASE;
         
         -- Set values to null as default
         nullify_cd_d   := NULL;
         nullify_cd_v   := NULL;
         nullify_author := 0;
            
         -- is the act nullified?
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 50));
         IF random_num = 1
         THEN
            nullify_author  := 1;
            nullify_cd_d    := 'MEDIA FAILURE';
            nullify_cd_v    := '827327';
         END IF;
         
         random_num  := DBMS_RANDOM.VALUE (-20, -30);
         random_numa := DBMS_RANDOM.VALUE (-19, -25);
         random_numc := DBMS_RANDOM.VALUE (-18, -27);
         random_numv := DBMS_RANDOM.VALUE (-21, -26);
         random_numi := DBMS_RANDOM.VALUE (-15, -22);
         random_numn := DBMS_RANDOM.VALUE (-17, -29);
         birthnum    := DBMS_random.VALUE (-10950, -36500);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numa, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numc, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numv, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numi, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numn, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date, 'yyyymmddhh24miss')
           INTO obs_date,
                ppa_date,
                ppc_date,
                ppv_date,
                ppi_date,
                ppn_date,
                birth_dt,
                upd_date
           FROM DUAL;

         -- get author practitioner
         SELECT allergy_pract_part_seq_id.NEXTVAL
           INTO authorppid
           FROM DUAL;
         
         insert_practpart.ALLERGY_INSERT_MESSAGE( ppa_date, authorppid );
         
         -- get verifier practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 3));
         IF random_num = 1
         THEN
             SELECT allergy_pract_part_seq_id.NEXTVAL
               INTO verifyppid
               FROM DUAL;
             
             insert_practpart.ALLERGY_INSERT_MESSAGE( ppv_date, verifyppid );
         ELSE
             verifyppid := NULL;
         END IF;         
         
         SELECT allergy_seq_id.NEXTVAL
           INTO allergy_seq_id_val
           FROM DUAL;
         
         -- get chart practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
         IF random_num = 1
         THEN
             random_num := ROUND(DBMS_RANDOM.value(1,2));
             FOR yy IN 1..random_num
             LOOP
                 SELECT allergy_pract_part_seq_id.NEXTVAL
                   INTO chartppid
                   FROM DUAL;
                 
                 insert_practpart.ALLERGY_INSERT_MESSAGE( ppc_date, chartppid, allergy_seq_id_val );
             END LOOP;
         ELSE
             chartppid := NULL;
         END IF;
         
         -- get idbandppid practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
         IF random_num = 1
         THEN
             random_num := ROUND(DBMS_RANDOM.value(1,2));
             FOR zz IN 1..random_num
             LOOP
                 SELECT allergy_pract_part_seq_id.NEXTVAL
                   INTO idbandppid
                   FROM DUAL;
                 
                 insert_practpart.ALLERGY_INSERT_MESSAGE( ppi_date, idbandppid, 0, allergy_seq_id_val );
             END LOOP;
         ELSE
             idbandppid := NULL;
         END IF;

         -- get Patient seq id
         SELECT ALLERGY_PATIENT_INFO_SEQ_ID.NEXTVAL
           INTO patient_seq_id_val
           FROM DUAL;

         -- Set Ethnic Characteristic information
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
             ethnic_code_d    := 'HISPANIC';
             ethnic_code_v    := '1238923';
            WHEN '2' THEN
             ethnic_code_d    := 'SPANISH';
             ethnic_code_v    := '1238924';
            WHEN '3' THEN
             ethnic_code_d    := 'AFRICAN AMERICAN';
             ethnic_code_v    := '1238925';
            ELSE
             ethnic_code_d    := 'ORIENTAL';
             ethnic_code_v    := '1238926';
         END CASE;

         -- Set gender
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               gender_code_d := 'MALE';
               gender_code_v := '1209109';
            WHEN '2' THEN
               gender_code_d := 'FEMALE';
               gender_code_v := '1209110';
            ELSE
               gender_code_d := 'UNKNOWN';
               gender_code_v := '1209111';
         END CASE;
         
         -- Set race code
         random_num := round(DBMS_RANDOM.VALUE (1, 7));
         CASE random_num
            WHEN '1' THEN
               race_code_d  := 'WHITE';
               race_code_v  := '21389023';
            WHEN '2' THEN
               race_code_d  := 'AFRICAN AMERICAN';
               race_code_v  := '21389024';
            WHEN '3' THEN
               race_code_d  := 'AMERICAN INDIAN';
               race_code_v  := '21389025';
            WHEN '4' THEN
               race_code_d  := 'JAPANESE';
               race_code_v  := '21389029';
            WHEN '5' THEN
               race_code_d  := 'KOREAN';
               race_code_v  := '21389030';
            WHEN '6' THEN
               race_code_d  := 'SAMOAN';
               race_code_v  := '21389021';
            ELSE
               race_code_d  := 'OTHER WHITE';
               race_code_v  := '21389022';
         END CASE;

         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
         
         -- Load Patient Record
         INSERT INTO ALLERGY_PATIENT_INFO (  
                                              ALLERGY_PATIENT_INFO_ID
                                            , GENDER_CODE
                                            , GENDER_DSPLYTXT
                                            , GENDER_CODSYS
                                            , BIRTHDATE_DT
                                            , BIRTHDATE_DT_NBR
                                            , ETHNIC_GRP_CODE
                                            , ETHNIC_GRP_DSPLYTXT
                                            , ETHNIC_GRP_CODSYS
                                            , NAME_PREFIX
                                            , NAME_GIVEN
                                            , NAME_FAMILY
                                            , NAME_SUFFIX
                                            , NAME_TITLE
                                            , NAME_MIDDLE
                                            , RACE_CODE
                                            , RACE_DSPLYTXT
                                            , RACE_CODSYS
                                            , ZIP_PLUS4
                              )
         VALUES (
                   patient_seq_id_val                                          -- PATIENT_ID
                 , gender_code_v
                 , gender_code_d
                 , NULL --GENDER CODSYS
                 , birth_dt                                                     -- BIRTHDATE_DT	
                 , to_number(birth_dt,'99999999999999')                         -- BIRTHDATE_DT_nbr
                 , ethnic_code_v
                 , ethnic_code_d
                 , NULL --ETHNIC CODSYS
                 , NULL                                                        -- PREFIX_NM	
                 , NULL                                                        -- GIVEN_NM	
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                 , NULL                                                        -- SUFFIX_NM	
                 , NULL                                                        -- TITLE_NM	
                 , NULL                                                        -- MIDDLE_NM	
                 , race_code_v
                 , race_code_d
                 , NULL --RACE CODSYS
                 , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))  --ZIP_PLUS4
                );
         
         INSERT INTO allergy
                     (   
                          ALLERGY_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , ALLERGY_PATIENT_INFO_ID
                        , AUTHOR_PPID
                        , VERIFIER_PPID
                        , NULLIFIER_PPID
                        , REQUEST_ID
                        , AGENT_CODE
                        , AGENT_DSPLYTXT
                        , AGENT_CODSYS
                        , ALLERGY_TYP_CODE
                        , ALLERGY_TYP_DSPLYTXT
                        , ALLERGY_TYP_CODSYS
                        , DESCRIPTION
                        , GMR_ALLERGY_CODE
                        , GMR_ALLERGY_DSPLYTXT
                        , GMR_ALLERGY_CODSYS
                        , GMR_ALLERGY_ALT_CODE
                        , GMR_ALLERGY_ALT_DSPLYTXT
                        , GMR_ALLERGY_ALT_CODSYS
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , INFO_SRC_CAT_CODE
                        , INFO_SRC_CAT_DSPLYTXT
                        , INFO_SRC_CAT_CODSYS
                        , ALRGY_OBSERVATION_TM
                        , ALRGY_OBSERVATION_TM_NBR
                        , MECHANISM_CODE
                        , MECHANISM_DSPLYTXT
                        , MECHANISM_CODSYS
                        , ALLERGY_STATUS
                        , ALLERGY_IS_VERIFIED
                        , FACILITY_NAME
                        , FACILITY_IDENTITY
                        , FACILITY_ASSIGNING_AUTHORITY
                        , SEVERITY_CODE
                        , SEVERITY_DSPLYTXT
                        , SEVERITY_CODSYS
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                     )
              VALUES (  
                        allergy_seq_id_val                                                      -- ALLERGY_ID
                      , v_vpid                                                                  -- PATIENT_IDENTITY
                      , 'DNS   -605'                                                            -- PATIENT_ASSIGNING_FACILITY
                      , 'USVHA'                                                                 -- PATIENT_ASSIGNING_AUTHORITY
                      , patient_seq_id_val                                                      -- ALLERGY_PATIENT_INFO_ID
                      , authorppid                                                              -- AUTHOR_PPID
                      , verifyppid                                                              -- VERIFIER_PPID
                      , rsnnulppid                                                              -- NULLIFIER_PPID	
                      , null                                                                    -- REQUEST_ID
                      , agent_allerg_v                                                          -- AGENT_VALSET_VUID	
                      , agent_allerg_d                                                          -- AGENT_DSPLYTXT	
                      , NULL                                                           -- AGENT_ALT_CODSYS	
                      , allgytype_v                                                             -- ALLERGY_TYPE_VALSET_VUID	
                      , allgytype_d                                                             -- ALLERGY_TYPE_DSPLYTXT	
                      , NULL                                                           -- ALLERGY_TYPE_ALT_CODSYS	
                      , allergy_description                                                     -- DESCRIPTION	
                      , gmr_alrgy_v -- gmr_alrgy
                      , gmr_alrgy_d -- gmr_alrgy
                      , null -- grm_alrgy
                      , null
                      , null
                      , null
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_IDENTITY
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_NAMESPACEID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_UNIVERSALID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_UNIVERSTYP
                      , info_src_v                                                              -- INFO_SRC_CAT_VALSET_VUID	
                      , info_src_d                                                              -- INFO_SRC_CAT_DSPLYTXT	
                      , null                                                           -- INFO_SRC_CAT_ALT_CODSYS	
                      , obs_date                                                                -- ALRGY_OBSERVATION_TM	
                      , to_number(obs_date,'99999999999999')                                    -- ALRGY_OBSERVATION_Tm_nbr
                      , mechanism_v                                                             -- MECHANISM_VALSET_VUID	
                      , mechanism_d                                                             -- MECHANISM_DSPLYTXT	
                      , null                                                           -- MECHANISM_ALT_CODSYS	
                      , status_code_d                                                           -- STATUS_DSPLYTXT	
                      , 'T'                                                                     -- ALLERGY_IS_VERIFIED	
                      , hospital_cd_d                                                           -- HOSPITAL_LOC_DSPLYTXT	
                      , hospital_cd_v                                                           -- HOSPITAL_LOC_VALSET_VUID	
                      , NULL                                                                    -- facilty	
                      , severity_cd_v                                                           -- SEVERITY_VALSET_VUID	
                      , severity_cd_d                                                           -- SEVERITY_DSPLYTXT	
                      , null                                                           -- SEVERITY_ALT_CODSYS	
                      , 1                                                                       -- RECORD_VERSION	
                      , upd_date                                                                -- RECORD_UPDATE_TM	
                      , to_number(upd_date,'99999999999999')                                    -- RECORD_UPDATE_TM_NBR	
                     );

         -- load several reactions
         IF &3 > 0
         THEN
            FOR j IN 1 .. &3
            LOOP
               -- Set agent_allergen_displayname
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 14));
               
               CASE random_num
                  WHEN '1' THEN
                     reaction_cd_d      := 'ANAPHYLAXIS';
                     reaction_cd_v      := '1013';
                  WHEN '2' THEN
                     reaction_cd_d      := 'CHEST PAIN';
                     reaction_cd_v      := '1011';
                  WHEN '3' THEN
                     reaction_cd_d      := 'COUGHING';
                     reaction_cd_v      := '1016';
                  WHEN '4' THEN
                     reaction_cd_d      := 'DIARRHEA';
                     reaction_cd_v      := '1007';
                  WHEN '5' THEN
                     reaction_cd_d      := 'ECZEMA';
                     reaction_cd_v      := '1014';
                  WHEN '6' THEN
                     reaction_cd_d      := 'FEVER WITH BLISTERS';
                     reaction_cd_v      := '1001';
                  WHEN '7' THEN
                     reaction_cd_d      := 'HEADACHES';
                     reaction_cd_v      := '1008';
                  WHEN '8' THEN
                     reaction_cd_d      := 'HEARTBURN';
                     reaction_cd_v      := '1006';
                  WHEN '9' THEN
                     reaction_cd_d      := 'HIVES';
                     reaction_cd_v      := '1003';
                  WHEN '10' THEN
                     reaction_cd_d      := 'KIDNEY DAMAGE';
                     reaction_cd_v      := '1020';
                  WHEN '11' THEN
                     reaction_cd_d      := 'LOSE CONSCIOUSNESS';
                     reaction_cd_v      := '1018';
                  WHEN '12' THEN
                     reaction_cd_d      := 'SHOCK';
                     reaction_cd_v      := '1017';
                  WHEN '13' THEN
                     reaction_cd_d      := 'SWELLING OF THE AIRWAYS TO THE LUNGS';
                     reaction_cd_v      := '1012';
                  WHEN '14' THEN
                     reaction_cd_d      := 'VOMITING';
                     reaction_cd_v      := '1005';
                  ELSE
                     reaction_cd_d      := 'OTHER';
                     reaction_cd_v      := '1229';
               END CASE;

               -- Set severity_code_displayname
               random_num := round(DBMS_RANDOM.VALUE (1, 3));
               CASE random_num
                  WHEN '1' THEN
                     severity_cd_d := 'MILD';
                     severity_cd_v := '1055';
                  WHEN '2' THEN
                     severity_cd_d := 'MODERATE';
                     severity_cd_v := '1051';
                  ELSE
                     severity_cd_d    := 'SEVERE';
                     severity_cd_v    := '1058';
               END CASE;

               random_num := DBMS_RANDOM.VALUE (-14, -21);
               random_nump := DBMS_RANDOM.VALUE (-19, -25);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                      TO_CHAR (fixed_date + random_nump, 'yyyymmddhh24miss'),
                      allergy_pract_part_seq_id.NEXTVAL, 
                      reaction_seq_id.NEXTVAL 
                 INTO obs_date,
                      ppp_date,
                      pract_part_seq_id_val, 
                      reaction_seq_id_val
                 FROM DUAL;                            

               insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );

               INSERT INTO reaction
                           (    
                                REACTION_ID	
                              , ALLERGY_ID	
                              , AUTHOR_PPID	
                              , OBSERVATION_TM	
                              , OBSERVATION_TM_NBR
                              , REACTION_CODE
                              , REACTION_DSPLYTXT	
                              , REACTION_CODSYS	
                              , SEVERITY_CODE	
                              , SEVERITY_DSPLYTXT	
                              , SEVERITY_CODSYS	
                           )
                    VALUES (  
                              reaction_seq_id_val                                               -- REACTION_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , pract_part_seq_id_val                                             -- AUTHOR_PPID
                            , obs_date                                                          -- OBSERVATION_TM 
                            , to_number(obs_date,'99999999999999')                              -- OBSERVATION_TM_NBR
                            , reaction_cd_v                                                     -- REACTION_VALSET_VUID
                            , reaction_cd_d                                                     -- REACTION_DSPLYTXT
                            , NULL                                                              -- REACTION_ALT_CODSYS
                            , severity_cd_v                                                     -- SEVERITY
                            , severity_cd_d                                                     -- SEVERITY
                            , NULL                                                              -- SEVERITY
                           );
            END LOOP;
         END IF;

         -- load 0 or many drug ingredients
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 1));
         prev_num := 0;

         IF random_num = 0
         THEN
            FOR drgi IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP
               -- Set drug ingredient vuid and text
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 6));
               
               if prev_num = random_num then
                  if prev_num = 6 then
                     random_num := 1;
                  else
                     random_num := random_num +1;
                  end if;
               end if;
               
               prev_num := random_num;

               CASE random_num
                  WHEN '1' THEN
                     drging_cd_d := 'WARFARIN';
                     drging_cd_v := '98460';
                  WHEN '2' THEN
                     drging_cd_d := 'ASCORBIC ACID';
                     drging_cd_v := '98461';
                  WHEN '3' THEN
                     drging_cd_d := 'TIZANIDINE HYDROCHLORIDE';
                     drging_cd_v := '98462';
                  WHEN '4' THEN
                     drging_cd_d := 'SIBUTRAMINE';
                     drging_cd_v := '98463';
                  WHEN '5' THEN
                     drging_cd_d := 'TENOXICAM';
                     drging_cd_v := '98464';
                  ELSE
                     drging_cd_d    := 'SODIUM VALEPROATE';
                     drging_cd_v    := '98465';
               END CASE;
   
               INSERT INTO DRUG_INGREDIENT
                           (    
                                DRUG_INGREDIENT_ID	
                              , ALLERGY_ID	
                              , DRUG_INGREDIENT_CODE	
                              , DRUG_INGREDIENT_DSPLYTXT	
                              , DRUG_INGREDIENT_CODSYS	
                           )
                    VALUES (  
                              drug_ingredient_seq_id.NEXTVAL                                    -- DRUG_INGREDIENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drging_cd_v                                                       -- DRUG_INGREDIENT_VALSET_VUID
                            , drging_cd_d                                                       -- DRUG_INGREDIENT_DSPLYTXT
                            , NULL                                                              -- DRUG_INGREDIENT_ALT_CODSYS
                           );

            END LOOP;          
         END IF;

         -- load 0 or many drug classes
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 1));
         prev_num := 0;

         IF random_num = 0
         THEN
            FOR drgc IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP
            
               -- Set drug class vuid and text
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 6));
               
               if prev_num = random_num then
                  if prev_num = 6 then
                     random_num := 1;
                  else
                     random_num := random_num +1;
                  end if;
               end if;
               
               prev_num := random_num;

               -- Null alternate values
               alt_codsys_dc  := NULL;
               alt_display_dc := NULL;
               alt_code_dc    := NULL;
               
               CASE random_num
                  WHEN '1' THEN
                     drgcls_cd_d := 'GENERAL  ANALGESIC';
                     drgcls_cd_v := '1720';
                  WHEN '2' THEN
                     drgcls_cd_d := 'NARCOTIC ANALGESI';
                     drgcls_cd_v := '1721';
                  WHEN '3' THEN
                     drgcls_cd_d := 'ANTIMIGRAINE/HEADACHE';
                     drgcls_cd_v := '1723';
                  WHEN '4' THEN
                     drgcls_cd_d := 'CENTRAL PAIN SYNDROME';
                     drgcls_cd_v := '1726';
                  WHEN '5' THEN
                     drgcls_cd_d := 'NONSTEROIDAL ANTI-INFLAMMATORY DRUGS';
                     drgcls_cd_v := '1727';
                  ELSE
                     random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
                     IF random_num2 = 1
                     THEN
                         drgcls_cd_d    := NULL;
                         drgcls_cd_v    := '0005';
                         alt_codsys_dc  := 'HL7';
                         alt_display_dc := 'UNKNOWN';
                         alt_code_dc    := '984135684';
                     ELSE 
                         drgcls_cd_d    := 'SWELLING OF THE FINGERS';
                         drgcls_cd_v    := '546846';
                     END IF;
               END CASE;
     
               INSERT INTO DRUG_CLASS
                           (   DRUG_CLASS_ID	
                              ,ALLERGY_ID	
                              ,DRUG_CLASS_CODE	
                              ,DRUG_CLASS_DSPLYTXT	
                              ,DRUG_CLASS_CODSYS	
                              ,DRUG_CLASS_ALT_CODE	
                              ,DRUG_CLASS_ALT_DSPLYTXT	
                              ,DRUG_CLASS_ALT_CODSYS
                           )
                    VALUES (  drug_CLASS_seq_id.NEXTVAL                                         -- DRUG_CLASS_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drgcls_cd_v                                                       -- DRUG_CLASS_VALSET_VUID
                            , drgcls_cd_d                                                       -- DRUG_CLASS_DSPLYTXT
                            , NULL  -- DRUG CLASS CODSYS
                            , alt_code_dc                                                       -- DRUG_CLASS_ALT_CODE
                            , alt_display_dc                                                    -- DRUG_CLASS_ALT_DSPLYTXT
                            , alt_codsys_dc                                                     -- DRUG_CLASS_ALT_CODSYS
                           );

            END LOOP;          
         END IF;

         -- load 0 or many comments
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 2));

         IF random_num = 0
         THEN
            FOR cmnt IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP
            
               random_num := DBMS_RANDOM.VALUE (-5, -11);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                      allergy_pract_part_seq_id.NEXTVAL 
                 INTO ppp_date,
                      pract_part_seq_id_val
                 FROM DUAL;                            

               insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );

               SELECT ALLERGY_COMMENT_EVENT_SEQ_ID.NEXTVAL
               INTO comment_evnt_id
               FROM DUAL;

               INSERT INTO ALLERGY_COMMENT_EVENT
                           (   
                              ALLERGY_COMMENT_EVENT_ID
                            , ALLERGY_ID
                            , AUTHOR_PPID
                            , COMMENT_TM
                            , COMMENT_TM_NBR
                            , COMMENT_TYP_CODE
                            , COMMENT_TYP_DSPLYTXT
                            , COMMENT_TYP_CODSYS
                            , COMMENT_TXT
                           )
                    VALUES (  
                              comment_evnt_id                                                   -- ALLERGY_COMMENT_EVENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , pract_part_seq_id_val                                             -- AUTHOR_PPID
                            , ppp_date                                                          -- COMMENT_TM
                            , to_number(ppp_date,'99999999999999')                              -- COMMENT_TM_NBR
                            , '23892398'                                                        -- COMMENT_TYP_VALSET_VUID
                            , 'NORMAL TEXT'                                                     -- COMMENT_TYP_DSPLYTXT
                            , NULL                                                              -- COMMENT_TYP_ALT_CODSYS
                            , dbms_random.string ('U',trunc(dbms_random.value(1,400)))          -- COMMENT_TXT
                           );

            END LOOP;          
         END IF;

       END LOOP;
       
       FOR asmt in 1 .. &1
	   LOOP
           random_num := DBMS_RANDOM.VALUE (-14, -21);
           birthnum    := DBMS_random.VALUE (-10950, -36500);
       
           -- load author
           SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                  TO_CHAR (fixed_date, 'yyyymmddhh24miss'),
                  TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss'),
                  allergy_pract_part_seq_id.NEXTVAL
             INTO obs_date,
                  upd_date,
                  birth_dt,
                  pract_part_seq_id_val
             FROM DUAL;                            

           insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );
           
           -- Set status_code_displayname
           random_num := round(DBMS_RANDOM.VALUE (1, 3));
           CASE random_num
              WHEN '1' THEN
                 status_code_d := 'ACTIVE';
                 status_code_v := '25';
              WHEN '2' THEN
                 status_code_d := 'OPEN';
                 status_code_v := '11';
              ELSE
                 status_code_d      := 'WAIT';
                 status_code_v      := '37';
           END CASE;
           
           -- get Patient seq id
           SELECT ALLERGY_PATIENT_INFO_seq_id.NEXTVAL
               INTO patient_seq_id_val
               FROM DUAL;

           -- Set Ethnic Characteristic information
           random_num := round(DBMS_RANDOM.VALUE (1, 4));
           CASE random_num
              WHEN '1' THEN
               ethnic_code_d    := 'HISPANIC';
               ethnic_code_v    := '1238923';
              WHEN '2' THEN
               ethnic_code_d    := 'SPANISH';
               ethnic_code_v    := '1238924';
              WHEN '3' THEN
               ethnic_code_d    := 'AFRICAN AMERICAN';
               ethnic_code_v    := '1238925';
              ELSE
               ethnic_code_d    := 'ORIENTAL';
               ethnic_code_v    := '1238926';
           END CASE;

           -- Set gender
           random_num := round(DBMS_RANDOM.VALUE (1, 3));
           CASE random_num
              WHEN '1' THEN
                 gender_code_d := 'MALE';
                 gender_code_v := '1209109';
              WHEN '2' THEN
                 gender_code_d := 'FEMALE';
                 gender_code_v := '1209110';
              ELSE
                 gender_code_d := 'UNKNOWN';
                 gender_code_v := '1209111';
           END CASE;
        
           -- Set race code
           random_num := round(DBMS_RANDOM.VALUE (1, 7));
           CASE random_num
              WHEN '1' THEN
                 race_code_d  := 'WHITE';
                 race_code_v  := '21389023';
              WHEN '2' THEN
                 race_code_d  := 'AFRICAN AMERICAN';
                 race_code_v  := '21389024';
              WHEN '3' THEN
                 race_code_d  := 'AMERICAN INDIAN';
                 race_code_v  := '21389025';
              WHEN '4' THEN
                 race_code_d  := 'JAPANESE';
                 race_code_v  := '21389029';
              WHEN '5' THEN
                 race_code_d  := 'KOREAN';
                 race_code_v  := '21389030';
              WHEN '6' THEN
                 race_code_d  := 'SAMOAN';
                 race_code_v  := '21389021';
              ELSE
                 race_code_d  := 'OTHER WHITE';
                 race_code_v  := '21389022';
           END CASE;
        
           -- Set allergy assesment
           random_num := round(DBMS_RANDOM.VALUE (1, 7));
           CASE random_num
              WHEN '1' THEN
                 alrg_asmt_d  := 'SEAFOOD';
                 alrg_asmt_v  := '159748';
              WHEN '2' THEN
                 alrg_asmt_d  := 'PET DANDER';
                 alrg_asmt_v  := '159749';
              WHEN '3' THEN
                 alrg_asmt_d  := 'SEASONAL - SPRING';
                 alrg_asmt_v  := '159750';
              WHEN '4' THEN
                 alrg_asmt_d  := 'DUST';
                 alrg_asmt_v  := '159751';
              WHEN '5' THEN
                 alrg_asmt_d  := 'PENICILAN';
                 alrg_asmt_v  := '159752';
              WHEN '6' THEN
                 alrg_asmt_d  := 'SEASONAL - FALL';
                 alrg_asmt_v  := '159753';
              ELSE
                 alrg_asmt_d  := 'OTHER';
                 alrg_asmt_v  := '1597454';
           END CASE;

           v_vpid := 'test_sourceid_' || to_char(asmt + starting_patient_vpid);
        
           -- Load Patient Record
           INSERT INTO ALLERGY_PATIENT_INFO (  
                                                ALLERGY_PATIENT_INFO_ID
                                              , GENDER_CODE
                                              , GENDER_DSPLYTXT
                                              , GENDER_CODSYS
                                              , BIRTHDATE_DT
                                              , BIRTHDATE_DT_NBR
                                              , ETHNIC_GRP_CODE
                                              , ETHNIC_GRP_DSPLYTXT
                                              , ETHNIC_GRP_CODSYS
                                              , NAME_PREFIX
                                              , NAME_GIVEN
                                              , NAME_FAMILY
                                              , NAME_SUFFIX
                                              , NAME_TITLE
                                              , NAME_MIDDLE
                                              , RACE_CODE
                                              , RACE_DSPLYTXT
                                              , RACE_CODSYS
                                              , ZIP_PLUS4
                                )
           VALUES (
                     patient_seq_id_val                                          -- PATIENT_ID
                   , gender_code_v
                   , gender_code_d
                   , NULL --GENDER CODSYS
                   , birth_dt                                                     -- BIRTHDATE_DT	
                   , to_number(birth_dt,'99999999999999')                         -- BIRTHDATE_DT_nbr
                   , ethnic_code_v
                   , ethnic_code_d
                   , NULL --ETHNIC CODSYS
                   , NULL                                                        -- PREFIX_NM	
                   , NULL                                                        -- GIVEN_NM	
                   , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                   , NULL                                                        -- SUFFIX_NM	
                   , NULL                                                        -- TITLE_NM	
                   , NULL                                                        -- MIDDLE_NM	
                   , race_code_v
                   , race_code_d
                   , NULL --RACE CODSYS
                   , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))  --ZIP_PLUS4
                  );
           
           INSERT INTO ALLERGY_ASSESSMENT
                      (   
                          ALLERGY_ASSESSMENT_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , ALLERGY_PATIENT_INFO_ID
                        , AUTHOR_PPID
                        , REQUEST_ID
                        , ASSESSMENT_VAL_CODE
                        , ASSESSMENT_VAL_DSPLYTXT
                        , ASSESSMENT_VAL_CODSYS
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , ALGYASMT_OBSERVATION_TM
                        , ALGYASMT_OBSERVATION_TM_NBR
                        , ALLERGY_ASSESSMENT_STATUS
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                      )
               VALUES (  
                         allergy_assessment_seq_id.NEXTVAL                                      -- ALLERGY_ASSESSMENT_ID
                       , v_vpid              -- PATIENT_IDENTITY
                       , 'DNS   -605'                                                           -- PATIENT_ASSIGNING_FACILITY
                       , 'USVHA'                                                                -- PATIENT_ASSIGNING_AUTHORITY
                       , patient_seq_id_val                                                     -- ALLERGY_PATIENT_INFO_ID	
                       , pract_part_seq_id_val                                                  -- AUTHOR_PPID	
                       , null                                                                   -- REQUEST_ID
                       , alrg_asmt_v   --allergy assessment
                       , alrg_asmt_d
                       , null
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_IDENTITY	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_NAMESPACEID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_UNIVERSALID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_UNIVERSTYP	
                       , obs_date                                                               -- ALGYASMT_OBSERVATION_TIME
                       , to_number(obs_date,'99999999999999')                                   -- ALGYASMT_OBSERVATION_TIME_NBR
                       , status_code_d                                                          -- STATUS_DSPLYTXT	
                       , 1                                                                      -- RECORD_VERSION	
                       , upd_date                                                               -- RECORD_UPDATE_TM	
                       , to_number(upd_date,'99999999999999')                                   -- RECORD_UPDATE_TM_NBR
                      );
       END LOOP;
       COMMIT;
   END LOOP;

   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line (   'Allergies End Time := ' || TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss') );
END;
/