    /******************************************************************************
   NAME:       new_allergies_load34_arms.sql
   PURPOSE:    This script will create Allergies records that map to 
               VHIM version 3.4  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        09/12/2006  M. Jacobsen      - Initial Release
******************************************************************************/
set serveroutput on
DECLARE
   random_num                   NUMBER          := 0;
   start_date                   NUMBER;
   literal_date                 VARCHAR2 (50);
   onset_start_date             NUMBER;
   onset_literal_date           VARCHAR2 (50);
   h_onset_start_date           NUMBER;
   h_onset_literal_date         VARCHAR2 (50);
   end_date                     NUMBER;
   patient_vpid                 VARCHAR2 (50);
   pract_vpid                   VARCHAR2 (30);
   info_src_d                   VARCHAR2 (100);
   info_src_v                   VARCHAR2 (20);
   mechanism_d                  VARCHAR2 (100);
   mechanism_v                  VARCHAR2 (20);
   agent_allerg_d               VARCHAR2 (100);
   agent_allerg_v               VARCHAR2 (20);
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (20);
   h_status_code_d              VARCHAR2 (100);
   h_status_code_v              VARCHAR2 (20);
   severity_cd_d                VARCHAR2 (100);
   severity_cd_v                VARCHAR2 (20);
   h_severity_cd_d              VARCHAR2 (100);
   h_severity_cd_v              VARCHAR2 (20);
   service_delivery             VARCHAR2 (50);
   reaction_cd_d                VARCHAR2 (100);
   reaction_cd_v                VARCHAR2 (20);
   h_reaction_cd_d              VARCHAR2 (100);
   h_reaction_cd_v              VARCHAR2 (20);
   o_reaction_cd_d              VARCHAR2 (100);
   o_reaction_cd_v              VARCHAR2 (20);
   h_o_reaction_cd_d            VARCHAR2 (100);
   h_o_reaction_cd_v            VARCHAR2 (20);
   pract_part_seq_id_val        NUMBER;
   allergy_seq_id_val           NUMBER;
   reaction_seq_id_val          NUMBER;
   starting_patient_vpid        NUMBER;
   ref_date			DATE;
BEGIN
   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'));

-- Set up a fixed reference date
   ref_date := to_date('01/01/2007','mm/dd/yyyy');


-- Set starting_patient_vpid
   IF &6 <= 0 
   THEN
       starting_patient_vpid :=1;
   ELSE
       starting_patient_vpid := &6 - 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 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 := '1015';
         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;
         
         -- insert severity record
         INSERT INTO severity
                        (  severity_id
                         , severity_extvalue
                         , severity_assignauthnm
                         , severity_rootvalue
                         , severity_nullflv
                         , severity_codeset
                         , severity_vuid
                         , severity_dsplynm
                        )
         VALUES (
                   SEVERITY_SEQ_ID.nextval
		         , null
		         , null
		         , 'test severity rootvalue ' || severity_seq_id.currval
		         , null
		         , null
                 , severity_cd_v
                 , severity_cd_d
                 );

         service_delivery := 'test_vlid_' || ROUND (DBMS_RANDOM.VALUE (1, 2001));
         random_num := DBMS_RANDOM.VALUE (-20, -30);

         SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
           INTO literal_date,
                start_date
           FROM DUAL;

-- set patient vpid
         IF (starting_patient_vpid + jj) > 500000
         THEN
             patient_vpid := 'test_vpid_' || (jj + starting_patient_vpid - 500000);
         ELSE
             patient_vpid := 'test_vpid_' || (jj + starting_patient_vpid);
         END IF; 
--dbms_output.put_line('what is patient_vpid?  ' || patient_vpid);		 

         INSERT INTO allergy
                     (  allergy_id
                      , vpid
                      , vlid
                      , severity_id
                      , allergy_extvalue 
                      , allergy_assignauthnm
                      , allergy_rootvalue
                      , info_source_category_codeset
                      , info_source_category_vuid
                      , info_SOURCE_category_dsplynm
                      , mechanism_nullflv
                      , mechanism_codeset
                      , mechanism_vuid
                      , mechanism_dsplynm
                      , observation_dt_literal
                      , observation_dt_value
                      , agent_allergen_codeset
                      , agent_allergen_vuid
                      , agent_allergen_dsplynm
                      , allergy_status_codeset
                      , allergy_status_vuid
                      , allergy_status_dsplynm
--                       , allergy_severity_nullflv
--                       , allergy_severity_codeset
--                       , allergy_severity_vuid
--                       , allergy_severity_dsplynm
--                       , allergy_severity_extvalue
--                       , allergy_severity_assignauthnm
--                       , allergy_severity_rootvalue
                      , last_updated_dttz
                     )
              VALUES (  allergy_seq_id.NEXTVAL                                                  -- ALLERGY_ID
                      , patient_vpid                                                            -- VPID
                      , service_delivery                                                        -- VLID
                      , severity_seq_id.CURRVAL                                                 -- SEVERITY_ID
                      , NULL                                                                    -- ALLERGY_EXTVALUE
                      , NULL                                                                    -- ALLERGY_ASSIGNAUTHNM
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id.currval -- ALLERGY_ROOTVALUE
                      , NULL                                                                    -- INFO_SOURCE_CATEGORY_CODESET
                      , info_src_v                                                              -- INFO_SOURCE_CATEGORY_VUID
                      , info_src_d                                                              -- INFO_SRC_CATEGORY_DSPLYNM
                      , NULL                                                                    -- MECHANISM_NULLFLAVOR
                      , NULL                                                                    -- MECHANISM_CODESET
                      , mechanism_v                                                             -- MECHANISM_VUID
                      , mechanism_d                                                             -- MECHANISM_DSPLYNM
                      , literal_date                                                            -- OBSERVATION_DT_LITERAL
                      , start_date                                                              -- OBSERVATION_DT_VALUE
                      , NULL                                                                    -- AGENT_ALLERGEN_CODESET
                      , agent_allerg_v                                                          -- AGENT_ALLERGEN_VUID
                      , agent_allerg_d                                                          -- AGENT_ALLERGEN_DSPLYNM
                      , NULL                                                                    -- ALLERGY_STATUS_CODESET
                      , status_code_v                                                           -- ALLERGY_STATUS_VUID
                      , status_code_d                                                           -- ALLERGY_STATUS_DSPLYNM
--                       , NULL                                                                    -- ALLERGY_SEVERITY_NULLFLAVOR
--                       , NULL                                                                    -- ALLERGY_SEVERITY_CODESET
--                       , severity_cd_v                                                           -- ALLERGY_SEVERITY_VUID
--                       , severity_cd_d                                                           -- ALLERGY_SEVERITY_DSPLYNM
--                       , NULL                                                                    -- ALLERGY_SEVERITY_EXTVALUE
--                       , NULL                                                                    -- ALLERGY_SEVERITY_ASSIGNAUTHNM
--                       , 'TEST SEVERITY ROOTVALUE ' || allergy_seq_id.currval                    -- ALLERGY_SEVERITY_ROOTVALUE
                      , SYSTIMESTAMP                                                            -- LAST_UPDATED_DTTZ
                     );

         -- load allergy status history
         random_num := ROUND(DBMS_RANDOM.VALUE (1,10));
         IF random_num = 1
         THEN
             -- set allergy status history
             CASE status_code_v
                WHEN '25' THEN
                   h_status_code_d := 'WAIT';
                   h_status_code_v := '37';
                WHEN '11' THEN
                   h_status_code_d := 'WAIT';
                   h_status_code_v := '37';
                ELSE
                   h_status_code_d := 'OPEN';
                   h_status_code_v := '11';
             END CASE;

             INSERT INTO ALLERGY_STATUS_HIST (  ALLERGY_STATUS_HIST_ID
                                              , ALLERGY_ID
                                              , ALLERGY_STATUS_HIST_CODESET
                                              , ALLERGY_STATUS_HIST_VUID
                                              , ALLERGY_STATUS_HIST_DSPLYNM
                                              , CHANGED_BY_VPID
                                              , CHANGED_DATE
                                              )
             VALUES (  ALLERGY_STATUS_HIST_SEQ_ID.NEXTVAL
                     , ALLERGY_SEQ_ID.CURRVAL
                     , NULL
                     , h_status_code_v
                     , h_status_code_d
                     , 'test_vpid_' || round(dbms_random.value(1,500000))
                     , to_number(to_char(sysdate,'yyyymmddhhmiss'))
                    );                                              
         END IF;
         
         -- load allergy severity history
         random_num := ROUND(DBMS_RANDOM.VALUE (1,10));
         IF random_num = 1
         THEN
             -- set allergy severity history
             CASE severity_cd_v
                WHEN '1055' THEN
                   h_severity_cd_d := 'SEVERE';
                   h_severity_cd_v := '1058';
                WHEN '1051' THEN
                   h_severity_cd_d := 'MILD';
                   h_severity_cd_v := '1055';
                ELSE
                   h_severity_cd_d := 'MODERATE';
                   h_severity_cd_v := '1051';
             END CASE;

             INSERT INTO SEVERITY_HIST (  SEVERITY_HIST_ID
                                        , SEVERITY_ID
                                        , SEVERITY_HIST_NULLFLV
                                        , SEVERITY_HIST_CODESET
                                        , SEVERITY_HIST_VUID
                                        , SEVERITY_HIST_DSPLYNM
                                        , CHANGED_BY_VPID
                                        , CHANGED_DATE
                                       )
             VALUES (  SEVERITY_HIST_SEQ_ID.NEXTVAL
                     , SEVERITY_SEQ_ID.CURRVAL
                     , NULL
                     , NULL
                     , h_severity_cd_v
                     , h_severity_cd_d
                     , 'test_vpid_' || round(dbms_random.value(1,500000))
                     , to_number(to_char(sysdate,'yyyymmddhhmiss'))
                    );                                              
         END IF;


         -- load several reactions
         IF &3 > 0
         THEN
            FOR j IN 1 .. &3
            LOOP
               -- Set agent_allergen_displayname
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 15));

               CASE random_num
                  WHEN '1' THEN
                     reaction_cd_d := 'ANAPHYLAXIS';
                     reaction_cd_v := '1013';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '2' THEN
                     reaction_cd_d := 'CHEST PAIN';
                     reaction_cd_v := '1011';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '3' THEN
                     reaction_cd_d := 'COUGHING';
                     reaction_cd_v := '1016';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '4' THEN
                     reaction_cd_d := 'DIARRHEA';
                     reaction_cd_v := '1007';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '5' THEN
                     reaction_cd_d := 'ECZEMA';
                     reaction_cd_v := '1014';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '6' THEN
                     reaction_cd_d := 'FEVER WITH BLISTERS';
                     reaction_cd_v := '1001';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '7' THEN
                     reaction_cd_d := 'HEADACHES';
                     reaction_cd_v := '1008';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '8' THEN
                     reaction_cd_d := 'HEARTBURN';
                     reaction_cd_v := '1006';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '9' THEN
                     reaction_cd_d := 'HIVES';
                     reaction_cd_v := '1003';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '10' THEN
                     reaction_cd_d := 'KIDNEY DAMAGE';
                     reaction_cd_v := '1020';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '11' THEN
                     reaction_cd_d := 'LOSE CONSCIOUSNESS';
                     reaction_cd_v := '1018';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '12' THEN
                     reaction_cd_d := 'SHOCK';
                     reaction_cd_v := '1017';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '13' THEN
                     reaction_cd_d := 'SWELLING OF THE AIRWAYS TO THE LUNGS';
                     reaction_cd_v := '1012';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  WHEN '14' THEN
                     reaction_cd_d := 'VOMITING';
                     reaction_cd_v := '1005';
                     o_reaction_cd_d := NULL;
                     o_reaction_cd_v := NULL;
                  ELSE
                     reaction_cd_d := NULL;
                     reaction_cd_v := NULL;
                     o_reaction_cd_d := 'SWELLING OF THE FINGERS';
                     o_reaction_cd_v := '1005001';
               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;
               
               -- insert severity record
               INSERT INTO severity
                              (  severity_id
                               , severity_extvalue
                               , severity_assignauthnm
                               , severity_rootvalue
                               , severity_nullflv
                               , severity_codeset
                               , severity_vuid
                               , severity_dsplynm
                              )
               VALUES (
                         SEVERITY_SEQ_ID.nextval
		               , null
		               , null
		               , 'test severity rootvalue ' || severity_seq_id.currval
		               , null
		               , null
                       , severity_cd_v
                       , severity_cd_d
                       );
               

               random_num := DBMS_RANDOM.VALUE (-20, -30);

               SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM'),
                      TO_NUMBER (TO_CHAR (ref_date + random_num,'yyyymmddhh24miss' ))
                 INTO literal_date,
                      start_date
                 FROM DUAL;

               random_num := random_num + .2;

               SELECT TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss' ) )
                 INTO end_date
                 FROM DUAL;

               random_num := DBMS_RANDOM.VALUE (-17, -19);

               SELECT TO_CHAR (ref_date + random_num,'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                      TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss' ) )
                 INTO onset_literal_date,
                      onset_start_date
                 FROM DUAL;

               random_num := random_num + .1;

               SELECT TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss' ) )
                 INTO end_date
                 FROM DUAL;

               -- set pract_vpid
               pract_vpid := 'test_vpid_' || round((DBMS_RANDOM.VALUE (1, 100)));

               SELECT pract_part_seq_id.NEXTVAL
                 INTO pract_part_seq_id_val
                 FROM DUAL;
                 
                 
               insert_practpart.insert_message( pract_vpid, 'Author', literal_date, start_date, end_date,
                                                'REACTION', 0, pract_part_seq_id_val );

               INSERT INTO reaction
                           (  reaction_id
                            , allergy_id
                            , practitioner_participation_id 
                            , severity_id
                            , reaction_extvalue
                            , reaction_assignauthnm
                            , reaction_rootvalue
                            , reaction_novalue_ind
                            , reaction_description
                            , reaction_status_codeset
                            , reaction_status_vuid
                            , reaction_status_dsplynm
                            , reaction_code_NULLFLV
                            , reaction_code_codeset
                            , reaction_code_vuid
                            , reaction_code_dsplynm
                            , other_code_value
                            , other_code_dsplynm
                            , reaction_onset_dt_value
                            , reaction_onset_dt_literal
--                             , reaction_severity_nullflv
--                             , reaction_severity_codeset
--                             , reaction_severity_vuid
--                             , reaction_severity_dsplynm
--                             , reaction_severity_extvalue
--                             , reaction_severity_assignauthnm
--                             , reaction_severity_rootvalue
                            , last_updated_dttz
                           )
                    VALUES (  reaction_seq_id.NEXTVAL                                           -- REACTION_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , pract_part_seq_id_val                                             -- PRACTITIONER_PARTICIPATION_ID
                            , severity_seq_id.CURRVAL                                           -- SEVERITY_ID
                            , NULL                                                              -- REACTION_EXTVALUE
                            , NULL                                                              -- REACTION_ASSIGNAUTHNM
                            , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||reaction_seq_id.currval -- REACTION_ROOTVALUE 
                            , 'F'                                                               -- REACTION_NOVALUE_IND                           
                            , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,80 ) ) )      -- REACTION_DESCRIPTION
                            , NULL                                                              -- REACTION_STATUS_CODESET
                            , status_code_v                                                     -- REACTION_STATUS_VUID
                            , status_code_d                                                     -- REACTION_STATUS_DSPLYNM
                            , NULL                                                              -- REACTION_CODE_NULLFLAVOR
                            , NULL                                                              -- REACTION_CODE_CODESET
                            , reaction_cd_v                                                     -- REACTION_CODE_VUID
                            , reaction_cd_d                                                     -- REACTION_CODE_DSPLYNM
                            , o_reaction_cd_v                                                   -- OTHER_CODE_VUID
                            , o_reaction_cd_d                                                   -- OTHER_CODE_DSPLYNM
                            , onset_start_date                                                  -- REACTION_ONSET_DT_VALUE
                            , onset_literal_date                                                -- REACTION_ONSET_DT_LITERAL
--                             , NULL                                                              -- REACTION_SEVERITY_NULLFLAVOR
--                             , NULL                                                              -- REACTION_SEVERITY_CODESET
--                             , severity_cd_v                                                     -- REACTION_SEVERITY_VUID
--                             , severity_cd_d                                                     -- REACTION_SEVERITY_DSPLYNM
--                             , NULL                                                              -- REACTION_SEVERITY_EXTVALUE
--                             , NULL                                                              -- REACTION_SEVERITY_ASSIGNAUTHNM
--                             , 'REACTION TEST SEVERITY ROOTVALUE ' || reaction_seq_id.currval    -- REACTION_SEVERITY_ROOTVALUE
                            , SYSTIMESTAMP                                                      -- last_updated_dttz
                           );
            END LOOP;
         END IF;

         -- load reaction severity history
         random_num := ROUND(DBMS_RANDOM.VALUE (1,10));
         IF random_num = 1
         THEN
             -- set reaction severity history
             CASE severity_cd_v
                WHEN '1055' THEN
                   h_severity_cd_d := 'SEVERE';
                   h_severity_cd_v := '1058';
                WHEN '1051' THEN
                   h_severity_cd_d := 'MILD';
                   h_severity_cd_v := '1055';
                ELSE
                   h_severity_cd_d := 'MODERATE';
                   h_severity_cd_v := '1051';
             END CASE;

             INSERT INTO SEVERITY_HIST (  SEVERITY_HIST_ID
                                        , SEVERITY_ID
                                        , SEVERITY_HIST_NULLFLV
                                        , SEVERITY_HIST_CODESET
                                        , SEVERITY_HIST_VUID
                                        , SEVERITY_HIST_DSPLYNM
                                        , CHANGED_BY_VPID
                                        , CHANGED_DATE
                                       )
             VALUES (  SEVERITY_HIST_SEQ_ID.NEXTVAL
                     , SEVERITY_SEQ_ID.CURRVAL
                     , NULL
                     , NULL
                     , h_severity_cd_v
                     , h_severity_cd_d
                     , 'test_vpid_' || round(dbms_random.value(1,500000))
                     , to_number(to_char(sysdate,'yyyymmddhhmiss'))
                    );                                              
         END IF;

         -- load reaction ONSET history
         random_num := ROUND(DBMS_RANDOM.VALUE (1,10));
         IF random_num = 1
         THEN
             -- set reaction onset history
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 15));

               CASE reaction_cd_v
                  WHEN '1011' THEN
                     h_reaction_cd_d := 'ANAPHYLAXIS';
                     h_reaction_cd_v := '1013';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1016' THEN
                     h_reaction_cd_d := 'CHEST PAIN';
                     h_reaction_cd_v := '1011';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1007' THEN
                     h_reaction_cd_d := 'COUGHING';
                     h_reaction_cd_v := '1016';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1014' THEN
                     h_reaction_cd_d := 'DIARRHEA';
                     h_reaction_cd_v := '1007';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1001' THEN
                     h_reaction_cd_d := 'ECZEMA';
                     h_reaction_cd_v := '1014';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1008' THEN
                     h_reaction_cd_d := 'FEVER WITH BLISTERS';
                     h_reaction_cd_v := '1001';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1006' THEN
                     h_reaction_cd_d := 'HEADACHES';
                     h_reaction_cd_v := '1008';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1003' THEN
                     h_reaction_cd_d := 'HEARTBURN';
                     h_reaction_cd_v := '1006';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1020' THEN
                     h_reaction_cd_d := 'HIVES';
                     h_reaction_cd_v := '1003';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1018' THEN
                     h_reaction_cd_d := 'KIDNEY DAMAGE';
                     h_reaction_cd_v := '1020';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1017' THEN
                     h_reaction_cd_d := 'LOSE CONSCIOUSNESS';
                     h_reaction_cd_v := '1018';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1012' THEN
                     h_reaction_cd_d := 'SHOCK';
                     h_reaction_cd_v := '1017';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1005' THEN
                     h_reaction_cd_d := 'SWELLING OF THE AIRWAYS TO THE LUNGS';
                     h_reaction_cd_v := '1012';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  WHEN '1013' THEN
                     h_reaction_cd_d := 'VOMITING';
                     h_reaction_cd_v := '1005';
                     h_o_reaction_cd_d := NULL;
                     h_o_reaction_cd_v := NULL;
                  ELSE
                     h_reaction_cd_d := NULL;
                     h_reaction_cd_v := NULL;
                     h_o_reaction_cd_d := 'SWELLING OF THE TOES';
                     h_o_reaction_cd_v := '10060011';
               END CASE;

             random_num := DBMS_RANDOM.VALUE (-17, -19);

             SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                    TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
               INTO h_onset_literal_date,
                    h_onset_start_date
               FROM DUAL;

             INSERT INTO REACTION_ONSET_HIST (  REACTION_ONSET_HIST_ID
                                              , REACTION_ID
                                              , REACTION_ONSET_HIST_DT_LITERAL
                                              , REACTION_ONSET_HIST_DT_VALUE
                                              , REACTION_CODE_HIST_NULLFLV
                                              , REACTION_CODE_HIST_CODESET
                                              , REACTION_CODE_HIST_VUID
                                              , REACTION_CODE_HIST_DSPLYNM
                                              , OTHER_CODE_HIST_VALUE
                                              , OTHER_CODE_HIST_DSPLYNM
                                              , CHANGED_BY_VPID
                                              , CHANGED_DATE
                                             )
             VALUES (  REACTION_ONSET_HIST_SEQ_ID.NEXTVAL
                     , REACTION_SEQ_ID.CURRVAL
                     , h_onset_literal_date
                     , h_onset_start_date
                     , NULL
                     , NULL
                     , h_reaction_cd_v
                     , h_reaction_cd_d
                     , h_o_reaction_cd_v
                     , h_o_reaction_cd_d
                     , 'test_vpid_' || round(dbms_random.value(1,500000))
                     , to_number(to_char(sysdate,'yyyymmddhhmiss'))
                    );                                              
         END IF;

         -- Set variables to load AUTHOR for Reaction
         random_num := DBMS_RANDOM.VALUE (-17, -19);

         SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
           INTO literal_date,
                start_date
           FROM DUAL;

         random_num := random_num + .2;

         SELECT TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
           INTO end_date
           FROM DUAL;
           
         -- set pract_vpid
         pract_vpid := 'test_vpid_' || round((DBMS_RANDOM.VALUE (1, 100)));
               

         SELECT pract_part_seq_id.NEXTVAL
           INTO pract_part_seq_id_val
           FROM DUAL;
         
         SELECT allergy_seq_id.CURRVAL
           INTO allergy_seq_id_val
           FROM DUAL;
                 
         insert_practpart.insert_message( pract_vpid, 'Author', literal_date, start_date, end_date,
                                          'ALLERGY', allergy_seq_id_val, pract_part_seq_id_val );
               
         -- load 0 or 1 DataEnterer providers
         IF &4 > 0
         THEN
             random_num := DBMS_RANDOM.VALUE (-17, -19);

             SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                    TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
               INTO literal_date,
                    start_date
               FROM DUAL;

             random_num := random_num + .2;

             SELECT TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss'))
               INTO end_date
               FROM DUAL;
               
             -- set pract_vpid
             pract_vpid := 'test_vpid_' || round((DBMS_RANDOM.VALUE (1, 100)));

             SELECT pract_part_seq_id.NEXTVAL
               INTO pract_part_seq_id_val
               FROM DUAL;
             
             SELECT allergy_seq_id.CURRVAL
               INTO allergy_seq_id_val
               FROM DUAL;
                     
             insert_practpart.insert_message( pract_vpid, 'DataEnterer', literal_date, start_date, end_date,
                                              'ALLERGY', allergy_seq_id_val, pract_part_seq_id_val );
         END IF;

         -- load 0-2 verifiers
         IF &5 > 0
         THEN
            FOR r IN 1 .. &5
            LOOP
               random_num := DBMS_RANDOM.VALUE (-17, -19);

               SELECT TO_CHAR (ref_date + random_num, 'fmMonth ddth, YYYY  fmhh:mi:ss AM' ),
                      TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss' ) )
                 INTO literal_date,
                      start_date
                 FROM DUAL;

               random_num := random_num + .1;

               SELECT TO_NUMBER (TO_CHAR (ref_date + random_num, 'yyyymmddhh24miss' ) )
                 INTO end_date
                 FROM DUAL;
                 
               -- set pract_vpid
               pract_vpid := 'test_vpid_' || round((DBMS_RANDOM.VALUE (1, 100)));

                SELECT pract_part_seq_id.NEXTVAL
                  INTO pract_part_seq_id_val
                  FROM DUAL;
                 
                SELECT allergy_seq_id.CURRVAL
                  INTO allergy_seq_id_val
                  FROM DUAL;
                         
                insert_practpart.insert_message( pract_vpid, 'Verifier', literal_date, start_date, end_date,
                                                 'ALLERGY', allergy_seq_id_val, pract_part_seq_id_val );
            END LOOP;
         END IF;

         COMMIT;
       END LOOP;
   END LOOP;

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