/******************************************************************************
   NAME:       new_vitals_load4x_arms_vista.sql
   PURPOSE:    This script will create Vitals records that map to 
               VHIM version 3.11.x  
   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 'DNS   -605'
   1.3        01/08/2008  M. Jacobsen      - remove common schema
   1.4        01/25/2008  M. Jacobsen      - collapse Patient Info into Vital
   1.5        01/28/2008  M. Jacobsen      - collapse practitioner participation info into vital
   1.6        06/17/2008  M. Jacobsen      - change format of date numbers to YYYMMDD.hhmiss    
   1.7        04/23/2013  M. Jacobsen      - use E or F for vital_status 
   1.8        09/02/2015  A. Kumar         - Change the RECORDSOURCE_NAMESPACEID to Vista     
******************************************************************************/
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;
   vital_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;
   v_namespace                  varchar2 (30);
   
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

   select max(to_number(substr(patient_identity,15,2))) into starting_patient_vpid from vital_v;


-- 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;
         
         -- 4/23 Change vital status to either an E or F
         -- Set status_code_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));

         CASE random_num
            WHEN '1' THEN
--               status_code_d := 'ACTIVE';
               status_code_d := 'E';
               status_code_v := '25';
--            WHEN '2' THEN
--               status_code_d := 'OPEN';
--               status_code_v := '11';
            ELSE
--               status_code_d      := 'WAIT';
               status_code_d      := 'F';
               status_code_v      := '37';
         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 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;

         SELECT vital_seq_id.NEXTVAL
           INTO vital_seq_id_val
           FROM DUAL;

         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               v_namespace  := 'Telehealth Viterion';
            WHEN '2' THEN
               v_namespace  := 'Telehealth Vitel Net';
            ELSE
               v_namespace  := 'Telehealth Health Hero';
         END CASE;
           
         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
         
                             
                     INSERT INTO vital30_v
                     (     
                          VITAL_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , 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
                        , HTH_VENDOR
                        , HTH_HOME_APPL
                        , HTH_MEASURE_DEVICE
                        , PATIENT_NAME_PREFIX
                        , PATIENT_NAME_GIVEN
                        , PATIENT_NAME_FAMILY
                        , PATIENT_NAME_SUFFIX
                        , PATIENT_NAME_TITLE
                        , PATIENT_NAME_MIDDLE
                        , AUTH_PERSON_IDENTITY                -- 1.5 BEGIN
                        , AUTH_PERSON_ASGNING_FACILITY
                        , AUTH_PERSON_ASGNING_AUTHORITY
                        , AUTH_PRACTITIONER_COMMENTS
                        , AUTH_PRACTITIONER_TM 
                        , AUTH_PRACTITIONER_TM_NBR
                        , AUTH_PRACTITIONER_ID_SRC_TBL
                        , AUTH_NAME_PREFIX
                        , AUTH_NAME_GIVEN
                        , AUTH_NAME_FAMILY
                        , AUTH_NAME_SUFFIX
                        , AUTH_NAME_TITLE
                        , AUTH_NAME_MIDDLE                    
                        , ERR_PERSON_IDENTITY                
                        , ERR_PERSON_ASGNING_FACILITY
                        , ERR_PERSON_ASGNING_AUTHORITY
                        , ERR_PRACTITIONER_COMMENTS
                        , ERR_PRACTITIONER_TM 
                        , ERR_PRACTITIONER_TM_NBR
                        , ERR_PRACTITIONER_ID_SRC_TBL
                        , ERR_NAME_PREFIX
                        , ERR_NAME_GIVEN
                        , ERR_NAME_FAMILY
                        , ERR_NAME_SUFFIX
                        , ERR_NAME_TITLE
                        , ERR_NAME_MIDDLE                    -- 1.5 END-NULLIFY
                        , RECORDSOURCE_NAMESPACEID
                        , RECORDSOURCE_UNIVERSALID
                        , RECORDSOURCE_UNIVERSTYP
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                     )
              VALUES (  
                          vital_seq_id_val                                                          -- VITAL_ID
                        , v_vpid                                                                    -- PATIENT_IDENTITY
                        , '660'                                                              -- PATIENT_ASSIGNING_FACILITY
                        , 'USVHA'                                                                   -- PATIENT_ASSIGNING_AUTHORITY
--1.4                        , patient_seq_id_val                                                        -- VITAL_PATIENT_INFO_ID  
--1.5                        , authorppid                                                                -- AUTHOR_PPID 
--                        , NULL                                                                      -- NULLIFIER_PPID 
                        , NULL                                                                      -- REQUEST_ID
                        , collection_tm                                                             -- COLLECTION_SESSION_TM  
                        , (TO_NUMBER(collection_tm,'99999999999999')-17000000000000)/1000000                                 -- COLLECTION_SESSION_TM  
--                      , to_number(to_char(to_number(substr(collection_tm,1,4)-1700)) || substr(collection_tm,5,4) || '.' || substr(collection_tm,9,length(collection_tm)-8))
                        , '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   
                        , v_namespace                                                                       -- 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')-17000000000000)/1000000                                -- VITAL_OBSERVATION_TM_NBR                    
--                      , to_number(to_char(to_number(substr(observation_tm,1,4)-1700)) || substr(observation_tm,5,4) || '.' || substr(observation_tm,9,length(observation_tm)-8))
                        , 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
                        , ' '                                                    
                        , ' '                                            
                        , ' ' 
                        , 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    
                        , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- AUTH_PERSON_IDENTITY  1.5 START
                        , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- AUTH_PERSON_ASGNING_FACILITY
                        , null                                                          -- AUTH_PERSON_ASGNING_AUTHORITY
                        , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )  -- AUTH_practitioner_comments
                        , ppa_date                                                      -- AUTH_PRACTITIONER_TM
                        , (to_number(ppa_date,'99999999999999')-17000000000000)/1000000                          -- AUTH_PRACTITIONER_TM_NBR
--                      , to_number(to_char(to_number(substr(ppa_date,1,4)-1700)) || substr(ppa_date,5,4) || '.' || substr(ppa_date,9,length(ppa_date)-8))
                        , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )   -- AUTH_practitioner_id_src_tbl
                        , NULL                                                          -- AUTH_NAME_PREFIX    
                        , NULL                                                          -- AUTH_NAME_GIVEN        
                        , dbms_random.string ('U',trunc(dbms_random.value(1,40)))       -- AUTH_NAME_FAMILY    
                        , NULL                                                          -- AUTH_NAME_SUFFIX    
                        , NULL                                                          -- AUTH_NAME_TITLE    
                        , NULL                                                          -- AUTH_NAME_MIDDLE    
                        , NULL                                                          -- ERR_PERSON_IDENTITY
                        , NULL                                                          -- ERR_PERSON_ASGNING_FACILITY
                        , NULL                                                          -- ERR_PERSON_ASGNING_AUTHORITY
                        , NULL                                                          -- ERR_practitioner_comments
                        , NULL                                                          -- ERR_PRACTITIONER_TM
                        , NULL                                                          -- ERR_PRACTITIONER_TM_NBR
                        , NULL                                                          -- ERR_practitioner_id_src_tbl
                        , NULL                                                          -- ERR_NAME_PREFIX    
                        , NULL                                                          -- ERR_NAME_GIVEN        
                        , NULL                                                          -- ERR_NAME_FAMILY    
                        , NULL                                                          -- ERR_NAME_SUFFIX    
                        , NULL                                                          -- ERR_NAME_TITLE    
                        , NULL                                                          -- ERR_NAME_MIDDLE    1.5 END
                        , '660'
                        , NULL
                        , NULL
                        , 1                                                                         -- RECORD_VERSION                
                        , upd_date                                                                  -- RECORD_UPDATE_TM                  
                        , (TO_NUMBER(UPD_DATE,'99999999999999')-17000000000000)/1000000                                      -- RECORD_UPDATE_TM_NBR        
--                      , to_number(to_char(to_number(substr(upd_date,1,4)-1700)) || substr(upd_date,5,4) || '.' || substr(upd_date,9,length(upd_date)-8))
                     );

         -- 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 ERROR_REASON_v (
                                              ERROR_REASON_ID
                                            , VITAL_ID
                                            , ERROR_REASON_code
                                            , ERROR_REASON_DSPLYTXT
                                            , ERROR_REASON_CODSYS
                                            , LINE_NBR
                                           )
                                    VALUES (
                                              ERROR_REASON_seq_id.NEXTVAL                         --  ERROR_REASON_ID
                                            , vital_seq_id_val                                      -- VITAL_ID
                                            , nullrsn_code_v                                        -- ERROR_REASON_code
                                            , nullrsn_code_d                                        -- ERROR_REASON_DSPLYTXT
                                            , null                                                  -- ERROR_REASON_CODSYS
                                            , rr-1
                                           );
            END LOOP; 

            -- get author practitioner
--1.5            SELECT vital_pract_part_seq_id.NEXTVAL
--              INTO nullifyppid
--              FROM DUAL;
--             
--            insert_practpart.VITAL_INSERT_MESSAGE( ppn_date, nullifyppid );
                
            -- update vital nullifier id
            UPDATE vital30_v SET ERR_PERSON_IDENTITY = 'test_id_' || to_char(round(dbms_random.value(1,1000))) ,              
                               ERR_PERSON_ASGNING_FACILITY = 'visn ' || to_char(round(dbms_random.value(1,21))),
                               ERR_PRACTITIONER_COMMENTS = DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) ),
                               ERR_PRACTITIONER_TM = ppn_date, 
                               ERR_PRACTITIONER_TM_NBR  = (to_number(ppn_date,'99999999999999')-17000000000000)/1000000,
                               ERR_PRACTITIONER_ID_SRC_TBL = dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) ),
                               ERR_NAME_FAMILY = dbms_random.string ('U',trunc(dbms_random.value(1,40)))           
            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_v
                              (   QUALIFIER_ID
                                , VITAL_ID
                                , QUALIFIER_CODE
                                , QUALIFIER_DSPLYTXT
                                , QUALIFIER_CODSYS
                                , LINE_NBR
                              )
               VALUES (
                         qualifier_seq_id.NEXTVAL                                               -- QUALIFIER_ID 
                       , vital_seq_id_val                                                       -- VITAL_ID
                       , qualifier_code --qualifier_code
                       , qualifier_value                                                        -- QUALIFIER_dsplytxt
                       , NULL  --qualifier_codsys
                       , j-1
                       );
               

            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;
/
