/******************************************************************************
   NAME:       new_vitals_load4x_arms.sql
   PURPOSE:    This script will create Vitals records that map to 
               VHIM version 4.0  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        10/19/2007  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        11/05/2007  M. Jacobsen      - added hardcoding of PATIENT_ASSIGNING_FACILITY to 'VA.GOV-605'
   1.3        12/11/2007  M. Jacobsen      - updates to sync with vhim 4, c5a
******************************************************************************/
set serveroutput on
DECLARE
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_num3                  NUMBER          := 0;
   random_num4                  NUMBER          := 0;
   birthnum                     NUMBER          := 0;
   birth_dt                     VARCHAR2 (30);
   r_loop                       NUMBER;
   nullifyppid                  NUMBER;
   authorppid                   NUMBER;
   vital_seq_id_val             NUMBER;
   pract_part_seq_id_val        NUMBER;
   qualifier_seq_id_val         NUMBER;
   nullrsn_seq_id_val           NUMBER;
   patient_id                   NUMBER;
   ppa_date                     VARCHAR2 (30);
   ppn_date                     VARCHAR2 (30);
   upd_date                     VARCHAR2 (30);
   collection_tm                VARCHAR2 (30);
   observation_tm               VARCHAR2 (30);
   obschar_code_d               VARCHAR2 (100);
   obschar_code_v               VARCHAR2 (30);
   obsmeth_code_d               VARCHAR2 (100);
   obsmeth_code_v               VARCHAR2 (30);
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (30);
   hosploc_cd_d                 VARCHAR2 (100);
   hosploc_cd_v                 VARCHAR2 (30);
   nullrsn_code_d               VARCHAR2 (100);
   nullrsn_code_v               VARCHAR2 (30);
   qualifier_value              VARCHAR2 (100);
   qualifier_code               VARCHAR2 (100);
   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);
   flow_code_d                  VARCHAR2 (100);
   flow_code_v                  VARCHAR2 (30);
   conc_code_d                  VARCHAR2 (100);
   conc_code_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 (   'Vitals 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');

-- Set starting_patient_vpid
   IF &3 <= 0 
   THEN
       starting_patient_vpid :=1;
   ELSE
       starting_patient_vpid := &3 - 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 Observered Characteristic information
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
             obschar_code_d := 'WEIGHT';
             obschar_code_v := 30000;
            WHEN '2' THEN
             obschar_code_d := 'TEMPERATURE';
             obschar_code_v := 30001;
            WHEN '3' THEN
             obschar_code_d := 'BLOOD PRESSURE';
             obschar_code_v := 30002;
            ELSE
             obschar_code_d := 'PULSE';
             obschar_code_v := 30003;
         END CASE;
         
         -- 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 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 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 hospital code
         random_num := round(DBMS_RANDOM.VALUE (1, 10));

         CASE random_num
            WHEN '1' THEN
               hosploc_cd_d := 'VA SALT LAKE CITY HEALTH CARE SYSTEM';
               hosploc_cd_v := '660';
            WHEN '2' THEN
               hosploc_cd_d := 'TUSCALOOSA VA MEDICAL CENTER';
               hosploc_cd_v := '679';
            WHEN '3' THEN
               hosploc_cd_d := 'CENTRAL TEXAS VETERANS HEALTH CARE SYSTEM';
               hosploc_cd_v := '674';
            WHEN '4' THEN
               hosploc_cd_d := 'PHILADELPHIA VA MEDICAL CENTER';
               hosploc_cd_v := '642';
            WHEN '5' THEN
               hosploc_cd_d := 'CINCINNATI VA MEDICAL CENTER';
               hosploc_cd_v := '539';
            WHEN '6' THEN
               hosploc_cd_d := 'JAMES J. PETERS VA MEDICAL CENTER';
               hosploc_cd_v := '526';
            WHEN '7' THEN
               hosploc_cd_d := 'VA GULF COAST VETERANS HEALTH CARE SYSTEM';
               hosploc_cd_v := '520';
            WHEN '8' THEN
               hosploc_cd_d := 'RICHARD L. ROUDEBUSH VA MEDICAL CENTER';
               hosploc_cd_v := '583';
            WHEN '9' THEN
               hosploc_cd_d := 'MIAMI VA HEALTHCARE SYSTEM';
               hosploc_cd_v := '546';
            ELSE
               hosploc_cd_d := 'JONATHAN M. WAINWRIGHT MEMORIAL VA MEDICAL CENTER';
               hosploc_cd_v := '687';
         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 obs method code
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               obsmeth_code_d  := 'UNITS';
               obsmeth_code_v  := '351800';
            WHEN '2' THEN
               obsmeth_code_d  := 'LITERS';
               obsmeth_code_v  := '351801';
            ELSE
               obsmeth_code_d  := 'MILLILETERS';
               obsmeth_code_v  := '351802';
         END CASE;
         
         -- Set flow code
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               flow_code_d  := 'UNITS';
               flow_code_v  := '351800';
            WHEN '2' THEN
               flow_code_d  := 'LITERS';
               flow_code_v  := '351801';
            ELSE
               flow_code_d  := 'MILLILETERS';
               flow_code_v  := '351802';
         END CASE;
         
         -- Set conc code
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               conc_code_d  := 'UNITS';
               conc_code_v  := '351800';
            WHEN '2' THEN
               conc_code_d  := 'LITERS';
               conc_code_v  := '351801';
            ELSE
               conc_code_d  := 'MILLILETERS';
               conc_code_v  := '351802';
         END CASE;
         
         random_num  := DBMS_RANDOM.VALUE (-2, -5);
         random_num2 := DBMS_RANDOM.VALUE (-2, -5);
         random_num3 := DBMS_RANDOM.VALUE (-2, -5);
         random_num4 := DBMS_RANDOM.VALUE (-2, -5);
         birthnum    := DBMS_random.VALUE (-10950, -36500);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num4, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss'),
                ROUND (DBMS_RANDOM.VALUE (1, 50000))
           INTO ppa_date,
                ppn_date,
                collection_tm,
                observation_tm,
                upd_date,
                birth_dt,
                patient_id
           FROM DUAL;

         -- get author practitioner
         SELECT pract_part_seq_id.NEXTVAL
           INTO authorppid
           FROM DUAL;
         
         insert_practpart.insert_message( ppa_date, authorppid );

         SELECT vital_seq_id.NEXTVAL
           INTO vital_seq_id_val
           FROM DUAL;
           
         -- get Patient seq id
         SELECT patient_information_seq_id.NEXTVAL
           INTO patient_seq_id_val
           FROM DUAL;

         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);

         -- Load Patient Record
         INSERT INTO PATIENT_information (  
                                              PATIENT_INFORMATION_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
                                            , LAST_UPDATED_DTTZ
                              )
         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
                 , SYSTIMESTAMP                                                -- last_updated_dttz
                );
         
         INSERT INTO vital
                     (     
                          VITAL_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , PATIENT_INFORMATION_ID
                        , AUTHOR_PPID
                        , NULLIFIER_PPID
                        , REQUEST_ID
                        , COLLECTION_SESSION_TM
                        , COLLECTION_SESSION_TM_NBR
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , OBSERVATION_METHOD_CODE
                        , OBSERVATION_METHOD_DSPLYTXT
                        , OBSERVATION_METHOD_CODSYS
                        , VITAL_OBSERVATION_TM
                        , VITAL_OBSERVATION_TM_NBR
                        , OBSERVED_CHAR_CODE
                        , OBSERVED_CHAR_DSPLYTXT
                        , OBSERVED_CHAR_CODSYS
                        , VITAL_STATUS
                        , HOSPITAL_LOC_POINT_OF_CARE
                        , HOSPITAL_LOC_ROOM
                        , HOSPITAL_LOC_BED
                        , HOSPITAL_LOC_FCLTY_NMSPCID
                        , HOSPITAL_LOC_FCLTY_UNVSID
                        , HOSPITAL_LOC_FCLTY_UNVSIDTYP
                        , HOSPITAL_LOC_PERS_LOC_TYP
                        , HOSPITAL_LOC_DESCRIPTION
                        , DIVISION_CODE
                        , DIVISION_DSPLYTXT
                        , DIVISION_CODSYS
                        , STOP_CODE
                        , STOP_DSPLYTXT
                        , STOP_CODSYS
                        , ORD_FCLTY_NAME
                        , ORD_FCLTY_IDENTITY
                        , ORD_FCLTY_ASSIGNING_AUTHORITY
                        , OBSERVATION_VAL
                        , OBSERVATION_UOFM_CODE
                        , OBSERVATION_UOFM_DSPLYTXT
                        , OBSERVATION_UOFM_CODSYS
                        , FLOW_RATE_VAL
                        , FLOW_RATE_UOFM_CODE
                        , FLOW_RATE_UOFM_DSPLYTXT
                        , FLOW_RATE_UOFM_CODSYS
                        , CONCENTRATION_VAL
                        , CONCENTRATION_UOFM_CODE
                        , CONCENTRATION_UOFM_DSPLYTXT
                        , CONCENTRATION_UOFM_CODSYS
                        , HOMEAPPL_NAME
                        , HOMEAPPL_IDENTITY
                        , HOMEAPPL_NAMESPACEID
                        , HOMEAPPL_UNIVERSALID
                        , HOMEAPPL_UNIVERSTYP
                        , MEASDEVICE_NAME
                        , MEASDEVICE_IDENTITY
                        , MEASDEVICE_NAMESPACEID
                        , MEASDEVICE_UNIVERSALID
                        , MEASDEVICE_UNIVERSTYP
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                        , LAST_UPDATED_DTTZ
                     )
              VALUES (  
                          vital_seq_id_val                                                          -- VITAL_ID
                        , v_vpid                                                                    -- PATIENT_IDENTITY
                        , 'VA.GOV-605'                                                              -- PATIENT_ASSIGNING_FACILITY
                        , 'USVHA'                                                                   -- PATIENT_ASSIGNING_AUTHORITY
                        , patient_seq_id_val                                                        -- PATIENT_information_ID  
                        , authorppid                                                                -- AUTHOR_PPID 
                        , NULL                                                                      -- NULLIFIER_PPID 
                        , NULL                                                                      -- REQUEST_ID
                        , collection_tm                                                             -- COLLECTION_SESSION_TM  
                        , TO_NUMBER(collection_tm,'99999999999999')                                 -- COLLECTION_SESSION_TM  
                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- RECORDIDENTIFIER_IDENTITY      
                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- RECORDIDENTIFIER_NAMESPACEID   
                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- RECORDIDENTIFIER_UNIVERSALID   
                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- RECORDIDENTIFIER_UNIVERSTYP  
                        , null -- observation_method_code
                        , null
                        , null
                        , observation_tm                                                            -- VITAL_OBSERVATION_TM                    
                        , TO_NUMBER(observation_tm,'99999999999999')                                -- VITAL_OBSERVATION_TM_NBR                    
                        , obschar_code_v                                                            -- OBSERVED_CHAR_VALSET_VUID         
                        , obschar_code_d                                                            -- OBSERVED_CHAR_DSPLYTXT            
                        , NULL                                                                      -- OBSERVED_CHAR_DSGNTN_VUID         
                        , status_code_d                                                             -- STATUS_DSPLYTXT                   
                        , hosploc_cd_d                                                              -- HOSPITAL_LOC_DSPLYTXT             
                        , null -- room
                        , null -- bed
                        , hosploc_cd_d                                                              -- nmspcid             
                        , null -- unvisd
                        , null -- unvsidtyp
                        , null -- pers_loc_typ
                        , hosploc_cd_d                                                              -- description             
                        , round(dbms_random.value(1,21)) -- division code
                        , dbms_random.string ('U',trunc(dbms_random.value(10,50))) -- division display
                        , null -- division codsys
                        , round(dbms_random.value(1,21)) -- stop code
                        , dbms_random.string ('U',trunc(dbms_random.value(10,50))) -- stop display
                        , null -- stop codsys
                        , dbms_random.string ('U',trunc(dbms_random.value(10,50)))                  -- ORD_FCLTY_NAME
                        , round(dbms_random.value(1,21))                                            -- ORD_FCLTY_IDENTITY
                        , null                                                                      -- ORD_FCLTY_ASSIGNING_AUTHORITY
                        , dbms_random.string ('U',trunc(dbms_random.value(10,100)))                 -- observation_val
                        , obsmeth_code_v -- obs uofm code
                        , obsmeth_code_d -- obs uofm display
                        , null -- obs uofm codsys
                        , round(dbms_random.value(40,200)) -- flow rate
                        , flow_code_v -- flow uofm code
                        , flow_code_d -- flow uofm display
                        , null -- flow uofm codsys
                        , round(dbms_random.value(4,10)) -- concentration val
                        , conc_code_v -- conc uofm code
                        , conc_code_d -- conc uofm display
                        , null -- conc uofm codsys
                        , dbms_random.string ('U',trunc(dbms_random.value(1,100)))                  -- HOMEAPPL_NAME                     
                        , 'HOME'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- HOMEAPPL_IDENTITY     
                        , 'HOME'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- HOMEAPPL_NAMESPACEID  
                        , 'HOME'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- HOMEAPPL_UNIVERSALID  
                        , 'HOME'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- HOMEAPPL_UNIVERSTYP 
                        , dbms_random.string ('U',trunc(dbms_random.value(1,100)))                  -- MEASDEVICE_NAME                   
                        , 'MEASURE'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- MEASDEVICE_IDENTITY   
                        , 'MEASURE'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- MEASDEVICE_NAMESPACEID
                        , 'MEASURE'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- MEASDEVICE_UNIVERSALID
                        , 'MEASURE'||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||vital_seq_id_val -- MEASDEVICE_UNIVERSTYP
                        , 1                                                                         -- RECORD_VERSION                
                        , upd_date                                                                  -- RECORD_UPDATE_TM                  
                        , TO_NUMBER(UPD_DATE,'99999999999999')                                      -- RECORD_UPDATE_TM_NBR                 
                        , SYSTIMESTAMP                                                              -- LAST_UPDATED_DTTZ
                     );

         -- check to see if we want to load a NULLIFIER
         random_num := round(dbms_random.value(1,100));
         IF random_num = 1 
         THEN 
            random_num2 := round(dbms_random.value(1,100));
            IF random_num2 > 94
            THEN
                r_loop := 2;
            ELSE
                r_loop := 1;
            END IF;
            
            FOR rr in 1..r_loop
            LOOP   
                IF rr = 1
                THEN
                    nullrsn_code_d      := 'WRONG PATIENT';
                    nullrsn_code_v      := '239082398547';
                ELSE
                    nullrsn_code_d      := NULL;
                    nullrsn_code_v      := NULL;
                END IF;
                
                -- load r_loop nullify reasons
                INSERT INTO NULLIFY_REASON (
                                              NULLIFY_REASON_ID
                                            , VITAL_ID
                                            , NULLIFY_REASON_code
                                            , NULLIFY_REASON_DSPLYTXT
                                            , NULLIFY_REASON_CODSYS
                                            , LAST_UPDATED_DTTZ
                                           )
                                    VALUES (
                                              nullify_reason_seq_id.NEXTVAL                         --  NULLIFY_REASON_ID
                                            , vital_seq_id_val                                      -- VITAL_ID
                                            , nullrsn_code_v                                        -- NULLIFY_REASON_code
                                            , nullrsn_code_d                                        -- NULLIFY_REASON_DSPLYTXT
                                            , null                                                  -- NULLIFY_REASON_CODSYS
                                            , SYSTIMESTAMP                                          -- LAST_UPDATED_DTTZ
                                           );
            END LOOP; 

            -- get author practitioner
            SELECT pract_part_seq_id.NEXTVAL
              INTO nullifyppid
              FROM DUAL;
             
            insert_practpart.insert_message( ppn_date, nullifyppid );
                
            -- update vital nullifier id
            UPDATE vital SET NULLIFIER_PPID = nullifyppid, last_updated_dttz = systimestamp
            WHERE vital_id = vital_seq_id_val;
                
         END IF;
         
         -- load 0 to many qualifiers
         random_num := round(dbms_random.value(0,2));
         IF random_num = 0 
         THEN 
            random_num2 := round(dbms_random.value(1,3));
            FOR j IN 1 .. random_num2
            LOOP
               -- Set body_site
               random_num3 := ROUND(DBMS_RANDOM.VALUE (1, 15));

               CASE random_num3
                  WHEN '1' THEN
                     qualifier_value := 'DOPPLER';
                     qualifier_code  := '389290';
                  WHEN '2' THEN
                     qualifier_value := 'LEFT';
                     qualifier_code  := '389291';
                  WHEN '3' THEN
                     qualifier_value := 'RIGHT';
                     qualifier_code  := '389292';
                  WHEN '4' THEN
                     qualifier_value := 'ARM';
                     qualifier_code  := '389293';
                  WHEN '5' THEN
                     qualifier_value := 'LEG';
                     qualifier_code  := '389294';
                  WHEN '6' THEN
                     qualifier_value := 'BUTTOCKS';
                     qualifier_code  := '389295';
                  WHEN '7' THEN
                     qualifier_value := 'STANDING';
                     qualifier_code  := '389296';
                  WHEN '8' THEN
                     qualifier_value := 'SITTING';
                     qualifier_code  := '389297';
                  WHEN '9' THEN
                     qualifier_value := 'PRONE';
                     qualifier_code  := '389298';
                  WHEN '10' THEN
                     qualifier_value := 'KNEE';
                     qualifier_code  := '389299';
                  WHEN '11' THEN
                     qualifier_value := 'FOREARM';
                     qualifier_code  := '389300';
                  WHEN '12' THEN
                     qualifier_value := 'FOOT';
                     qualifier_code  := '389301';
                  WHEN '13' THEN
                     qualifier_value := 'HAND';
                     qualifier_code  := '389302';
                  WHEN '14' THEN
                     qualifier_value := 'KNEELING';
                     qualifier_code  := '389303';
                  ELSE
                     qualifier_value := 'STOMACH';
                     qualifier_code  := '389304';
               END CASE;

               
               -- insert body_site record
               INSERT INTO QUALIFIER
                              (   QUALIFIER_ID
                                , VITAL_ID
                                , QUALIFIER_CODE
                                , QUALIFIER_DSPLYTXT
                                , QUALIFIER_CODSYS
                                , LAST_UPDATED_DTTZ   
                              )
               VALUES (
                         qualifier_seq_id.NEXTVAL                                               -- QUALIFIER_ID 
                       , vital_seq_id_val                                                       -- VITAL_ID
                       , qualifier_code --qualifier_code
                       , qualifier_value                                                        -- QUALIFIER_dsplytxt
                       , NULL  --qualifier_codsys
                       , SYSTIMESTAMP                                                           -- LAST_UPDATED_DTTZ
                       );
               

            END LOOP;
         END IF;

       END LOOP;
       COMMIT;
   END LOOP;

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