/******************************************************************************
   NAME:       new_healthadapter_load.sql
   PURPOSE:    This script will create dummy records in the health_adapter schema
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        03/19/2012  M. Jacobsen      - Initial Release
******************************************************************************/
set serveroutput on
DECLARE
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_num3                  NUMBER          := 0;
   random_num4                  NUMBER          := 0;
   vital_seq_id_val             NUMBER;
   patient_id                   NUMBER;
   collection_tm                TIMESTAMP(6);
   collection_tm_string         VARCHAR2 (30);
   observation_tm               TIMESTAMP(6);
   observation_tm_string        VARCHAR2 (30);
   observation_dt               TIMESTAMP(6);
   observation_dt_string        VARCHAR2 (30);
   contact_tm                   TIMESTAMP(6);
   contact_tm_string            VARCHAR2 (30);
   start_dt                     TIMESTAMP(6);
   start_dt_string              VARCHAR2 (30);
   stop_dt                      TIMESTAMP(6);
   stop_dt_string               VARCHAR2 (30);
   remind_dt                    TIMESTAMP(6);
   remind_dt_string             VARCHAR2 (30);
   meal_tm                      TIMESTAMP(6);
   meal_tm_string               VARCHAR2 (30);
   obschar_code_d               VARCHAR2 (100);
   obschar_code_v               VARCHAR2 (30);
   obsmeth_code_d               VARCHAR2 (100);
   obsmeth_code_v               VARCHAR2 (30);
   observation_code_d           VARCHAR2 (100);
   observation_code_v           VARCHAR2 (30);
   internation_code_d           VARCHAR2 (100);
   internation_code_v           VARCHAR2 (30);
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (30);
   qualifier_value              VARCHAR2 (100);
   qualifier_code               VARCHAR2 (100);
   patient_seq_id_val           NUMBER;
   flow_code_d                  VARCHAR2 (100);
   flow_code_v                  VARCHAR2 (30);
   conc_code_d                  VARCHAR2 (100);
   conc_code_v                  VARCHAR2 (30);
   act_dur_code_d               VARCHAR2 (100);
   act_dur_code_v               VARCHAR2 (30);
   act_dist_code_d              VARCHAR2 (100);
   act_dist_code_v              VARCHAR2 (30);
   act_typ_code_d               VARCHAR2 (100);
   act_typ_code_v               VARCHAR2 (30);
   exer_int_code_d              VARCHAR2 (100);
   exer_int_code_v              VARCHAR2 (30);
   contact_meth_d               VARCHAR2 (100);
   contact_meth_v               VARCHAR2 (30);
   calories_d                   VARCHAR2 (100);
   calories_v                   VARCHAR2 (30);
   fat_d                        VARCHAR2 (100);
   fat_v                        VARCHAR2 (30);
   carbs_d                      VARCHAR2 (100);
   carbs_v                      VARCHAR2 (30);
   protein_d                    VARCHAR2 (100);
   protein_v                    VARCHAR2 (30);
   mood_d                       VARCHAR2 (100);
   mood_v                       VARCHAR2 (30);
   v_vpid                       VARCHAR2 (30);
   meal_typ                     VARCHAR2 (30);
   mime_typ                     VARCHAR2 (30);
   attach_name                  VARCHAR2 (100);
   category_text                VARCHAR2 (100);
   skill_type                   VARCHAR2 (100);
   goal_name_txt                VARCHAR2 (100);
   starting_patient_vpid        NUMBER;
   fixed_date			        DATE;
   lBlob                        BLOB;
   lFile                        BFILE := BFILENAME('EXPDIR','VA_Logo.gif');
   
   
BEGIN
   dbms_output.enable(NULL);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'HealthAdapter Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));

   fixed_date := TO_DATE('03-19-12 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 records you want per patient
   FOR z IN 1 .. &1
   LOOP
       -- Loop thru how many patients to assign vital_sign 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 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 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;
         
         -- Set observation code
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               observation_code_d  := 'UNITS';
               observation_code_v  := '351800';
            WHEN '2' THEN
               observation_code_d  := 'LITERS';
               observation_code_v  := '351801';
            ELSE
               observation_code_d  := 'MILLILETERS';
               observation_code_v  := '351802';
         END CASE;
         
         -- Set international code
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               internation_code_d  := 'UNITS';
               internation_code_v  := '351800';
            WHEN '2' THEN
               internation_code_d  := 'LITERS';
               internation_code_v  := '351801';
            ELSE
               internation_code_d  := 'MILLILETERS';
               internation_code_v  := '351802';
         END CASE;
         
         random_num     := DBMS_RANDOM.VALUE (-2, -5);
         random_num2    := DBMS_RANDOM.VALUE (-2, -5);

         collection_tm  := fixed_date + random_num;
         observation_tm := fixed_date + random_num2; 
         
         observation_tm_string := to_char(observation_tm-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         collection_tm_string  := to_char(collection_tm-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         patient_id     := ROUND (DBMS_RANDOM.VALUE (1, 50000));

         SELECT vital_sign_seq_id.NEXTVAL
           INTO vital_seq_id_val
           FROM DUAL;
           
         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
         
         INSERT INTO vital_sign
                     (     
                        VITAL_SIGN_ID
                      , RECORDIDENTIFIER_NAMESPACEID
                      , RECORDIDENTIFIER_UNIVERSALID
                      , RECORDIDENTIFIER_UNIVERSTYP
                      , PATIENT_IDENTITY
                      , PATIENT_ASSIGNING_FACILITY
                      , PATIENT_ASSIGNING_AUTHORITY
                      , REQUEST_ID
                      , VITAL_OBSERVATION_DT
                      , VITAL_OBSERVATION_DT_UTC
                      , OBSERVATION_METHOD_CODE
                      , OBSERVATION_METHOD_DSPLYTXT
                      , OBSERVATION_METHOD_CODSYS
                      , OBSERVED_CHAR_CODE
                      , OBSERVED_CHAR_DSPLYTXT
                      , OBSERVED_CHAR_CODSYS
                      , COLLECTION_SESSION_TM
                      , COLLECTION_SESSION_TM_UTC
                      , VITAL_STATUS
                      , PATIENT_NAME_PREFIX
                      , PATIENT_NAME_GIVEN
                      , PATIENT_NAME_FAMILY
                      , PATIENT_NAME_SUFFIX
                      , PATIENT_NAME_TITLE
                      , PATIENT_NAME_MIDDLE
                      , 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
                      , OBSERVATION_VAL
                      , OBSERVATION_UOFM_CODE
                      , OBSERVATION_UOFM_DSPLYTXT
                      , OBSERVATION_UOFM_CODSYS
                      , INTERNATIONAL_VAL
                      , INTERNATIONAL_UOFM_CODE
                      , INTERNATIONAL_UOFM_DSPLYTXT
                      , INTERNATIONAL_UOFM_CODSYS
                      , COMMENTS
                      , DATA_ENTERER
                      )
              VALUES 
                     (  
                        vital_seq_id_val                                                           -- VITAL_ID
                      , '605_120.5'                                                                -- RECORDIDENTIFIER_NAMESPACEID   
                      , NULL                                                                       -- RECORDIDENTIFIER_UNIVERSALID   
                      , NULL                                                                       -- RECORDIDENTIFIER_UNIVERSTYP  
                      , v_vpid                                                                     -- PATIENT_IDENTITY
                      , '200DOD'                                                                   -- PATIENT_ASSIGNING_FACILITY
                      , 'USDOD'                                                                    -- PATIENT_ASSIGNING_AUTHORITY
                      , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                   -- REQUEST_ID
                      , observation_tm_string                                                      -- VITAL_OBSERVATION_TM                    
                      , observation_tm                                                             -- VITAL_OBSERVATION_TM_UTC                    
                      , obsmeth_code_v                                                             -- obs uofm code
                      , obsmeth_code_d                                                             -- obs uofm display
                      , NULL                                                                       -- obs uofm codsys
                      , obschar_code_v                                                             -- OBSERVED_CHAR_CODE         
                      , obschar_code_d                                                             -- OBSERVED_CHAR_DSPLYTXT            
                      , NULL                                                                       -- OBSERVED_CHAR_CODSYS         
                      , collection_tm_string                                                       -- COLLECTION_SESSION_TM
                      , collection_tm                                                              -- COLLECTION_SESSION_TM_UTC  
                      , status_code_d                                                              -- STATUS_DSPLYTXT                   
                      , NULL                                                                       -- PATIENT_NAME_PREFIX	
                      , NULL                                                                       -- PATIENT_NAME_GIVEN	
                      , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                    -- PATIENT_NAME_FAMILY	
                      , NULL                                                                       -- PATIENT_NAME_SUFFIX	
                      , NULL                                                                       -- PATIENT_NAME_TITLE	
                      , NULL                                                                       -- PATIENT_NAME_MIDDLE	
                      , 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
                      , 'HOME TELEHEALTH VENDOR'                                                   -- HTH_VENDOR
                      , 'HOME TELEHEALTH HOME APPLIANCE'                                           -- HTH_HOME_APPL
                      , 'HOME TELEHEALTH MEASUREMENT DEVICE'                                       -- HTH_MEASUREMENT_DEVICE
                      , dbms_random.string ('U',trunc(dbms_random.value(10,100)))                  -- observation_val
                      , observation_code_v                                                         -- observation_code
                      , observation_code_d                                                         -- observation_dsplytxt
                      , NULL                                                                       -- observation_codsys
                      , dbms_random.string ('U',trunc(dbms_random.value(10,100)))                  -- international_val
                      , internation_code_v                                                         -- INTERNATIONAL_UOFM_CODE
                      , internation_code_d                                                         -- INTERNATIONAL_UOFM_DSPLYTXT
                      , NULL                                                                       -- INTERNATIONAL_UOFM_CODSYS
                      , dbms_random.string ('U',trunc(dbms_random.value(50,500)))                  -- COMMENTS
                      , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                   -- DATA_ENTERER
                     );

         -- load 0 to many qualifiers
         random_num := round(dbms_random.value(0,2));
         IF random_num = 0 
         THEN 
         
            random_num3 := ROUND(DBMS_RANDOM.VALUE (1, 12));
               
            random_num2 := round(dbms_random.value(1,3));
            FOR j IN 1 .. random_num2
            LOOP
            
               -- Set body_site

               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;

               random_num3 := random_num3 + 1;
               
               -- insert body_site record
               INSERT INTO QUALIFIER
                              (   
                                 QUALIFIER_ID
                               , VITAL_SIGN_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;

     -- Load exercise journal data
         -- set activity type
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               act_typ_code_d := 'RUNNING';
               act_typ_code_v := '684984';
            WHEN '2' THEN
               act_typ_code_d := 'WALKING';
               act_typ_code_v := '684615';
            ELSE
               act_typ_code_d := 'WATCHING TV';
               act_typ_code_v := '8651874';
         END CASE;
         
         -- set exercise intensity type
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               exer_int_code_d := 'RUNNING';
               exer_int_code_v := '684984';
            WHEN '2' THEN
               exer_int_code_d := 'WALKING';
               exer_int_code_v := '684615';
            ELSE
               exer_int_code_d := 'WATCHING TV';
               exer_int_code_v := '8651874';
         END CASE;
         
         -- set activity duration
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               act_dur_code_d := 'SECONDS';
               act_dur_code_v := '15689841';
            WHEN '2' THEN
               act_dur_code_d := 'MINUTES';
               act_dur_code_v := '68616511';
            ELSE
               act_dur_code_d := 'HOURS';
               act_dur_code_v := '15681300';
         END CASE;
         
         -- set activity distance
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               act_dur_code_d := 'METERS';
               act_dur_code_v := '89118770';
            WHEN '2' THEN
               act_dur_code_d := 'YARDS';
               act_dur_code_v := '16879845';
            ELSE
               act_dur_code_d := 'MILES';
               act_dur_code_v := '68794123';
         END CASE;
         
         random_num3    := DBMS_RANDOM.VALUE (-1, -2);
         observation_dt := fixed_date + random_num3;

         observation_dt_string := to_char(observation_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO EXERCISE_JOURNAL 
                    (
                       EXERCISE_JOURNAL_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
                     , ACTIVITY_TYPE_CODE
                     , ACTIVITY_TYPE_DSPLYTXT
                     , ACTIVITY_TYPE_CODSYS
                     , EXERCISE_INTENSITY_CODE
                     , EXERCISE_INTENSITY_DSPLYTXT
                     , EXERCISE_INTENSITY_CODSYS
                     , ACTIVITY_DURATION_VAL
                     , ACTIVITY_DURATION_CODE
                     , ACTIVITY_DURATION_DSPLYTXT
                     , ACTIVITY_DURATION_CODSYS
                     , ACTIVITY_DISTANCE_VAL
                     , ACTIVITY_DISTANCE_CODE
                     , ACTIVITY_DISTANCE_DSPLYTXT
                     , ACTIVITY_DISTANCE_CODSYS
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , JOURNAL_ENTRY_TYP
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , OBSERVATION_DT_UTC
                     , OBSERVATION_DT           
                     , DATA_ENTERER
                    )
             VALUES 
                    (
                       exercise_journal_seq_id.nextval                                             -- EXERCISE_JOURNAL_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , act_typ_code_v                                                              -- ACTIVITY_TYPE_CODE
                     , act_typ_code_d                                                              -- ACTIVITY_TYPE_DSPLYTXT
                     , NULL                                                                        -- ACTIVITY_TYPE_CODSYS
                     , exer_int_code_v                                                             -- EXERCISE_INTENSITY_CODE
                     , exer_int_code_d                                                             -- EXERCISE_INTENSITY_DSPLYTXT
                     , NULL                                                                        -- EXERCISE_INTENSITY_CODSYS
                     , round(DBMS_RANDOM.VALUE (1, 1000))                                          -- ACTIVITY_DURATION_VAL
                     , act_dur_code_v                                                              -- ACTIVITY_DURATION_CODE
                     , act_dur_code_d                                                              -- ACTIVITY_DURATION_DSPLYTXT
                     , NULL                                                                        -- ACTIVITY_DURATION_CODSYS
                     , round(DBMS_RANDOM.VALUE (1, 1000))                                          -- ACTIVITY_DISTANCE_VAL
                     , act_dist_code_v                                                             -- ACTIVITY_DISTANCE_CODE
                     , act_dist_code_d                                                             -- ACTIVITY_DISTANCE_DSPLYTXT
                     , NULL                                                                        -- ACTIVITY_DISTANCE_CODSYS
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,10)))                     -- JOURNAL_ENTRY_TYP
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(80,1000)))                  -- COMMENTS
                     , observation_dt                                                              -- OBSERVATION_DT_UTC
                     , observation_dt_string                                                       -- OBSERVATION_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                    );
                    
     FOR exerj IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
     loop
         -- Set MIME type 
         random_num := round(DBMS_RANDOM.VALUE (1, 6));
         CASE random_num
            WHEN '1' THEN
             mime_typ := 'BMP';
            WHEN '2' THEN
             mime_typ := 'GIF';
            WHEN '3' THEN
             mime_typ := 'JPEG';
            WHEN '4' THEN
             mime_typ := 'PNG';
            WHEN '5' THEN
             mime_typ := 'X-PICT';
            ELSE
             mime_typ := 'TIFF';
         END CASE;
         
         attach_name := dbms_random.string ('U',trunc(dbms_random.value(1,50)))|| '.' || mime_typ;
     
         INSERT INTO EXERCISE_ATTACHMENT (
                                              EXERCISE_ATTACHMENT_ID
                                            , EXERCISE_JOURNAL_ID
                                            , MIME_TYPE
                                            , ATTACHMENT_NAME
                                            , BINARY_ATTACHMENT
                                           ) 
         VALUES (
                   EXERCISE_ATTACHMENT_SEQ_ID.nextval
                 , exercise_journal_seq_id.currval
                 , mime_typ                                                                        -- MIME_TYPE
                 , attach_name                                                                     -- ATTACHMENT_NAME
                 , empty_blob()                                                                    -- BINARY_ATTACHMENT
                ) RETURNING binary_attachment INTO lBlob;      
                
         DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

         DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

         DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                               SRC_LOB  => lFile,
                               AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

         DBMS_LOB.CLOSE(lFile);
         DBMS_LOB.CLOSE(lBlob);
             
     end loop;
                    
     -- Load contact log journal data
         -- set contact method
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
               contact_meth_d := 'PHONE';
               contact_meth_v := '13265498';
            WHEN '2' THEN
               contact_meth_d := 'EMAIL';
               contact_meth_v := '13254687';
            WHEN '2' THEN
               contact_meth_d := 'POSTAL';
               contact_meth_v := '12398999';
            ELSE
               contact_meth_d := 'IN PERSON';
               contact_meth_v := '12365987';
         END CASE;
         
         random_num3     := DBMS_RANDOM.VALUE (-1, -4);
         random_num4     := DBMS_RANDOM.VALUE (-1, -4);
         contact_tm      := fixed_date + random_num3;
         observation_dt  := fixed_date + random_num4;

         contact_tm_string     := to_char(contact_tm-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         observation_dt_string := to_char(observation_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO CONTACT_LOG_JOURNAL
                    (
                       CONTACT_LOG_JOURNAL_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
                     , CONTACT_METHOD_CODE
                     , CONTACT_METHOD_DSPLYTXT
                     , CONTACT_METHOD_CODSYS
                     , RELATED_TASK_OR_ITEM
                     , PERSON_CONTACTED
                     , CONTACT_TM_UTC
                     , CONTACT_TM
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , JOURNAL_ENTRY_TYP
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , OBSERVATION_DT_UTC
                     , OBSERVATION_DT
                     , DATA_ENTERER
                    )
             VALUES 
                    (
                       contact_log_journal_seq_id.nextval                                          -- CONTACT_LOG_JOURNAL_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , contact_meth_v                                                              -- CONTACT_METHOD_CODE
                     , contact_meth_d                                                              -- CONTACT_METHOD_DSPLYTXT
                     , NULL                                                                        -- CONTACT_METHOD_CODSYS
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- RELATED_TASK_OR_ITEM
                     , 'YES'                                                                       -- PERSON_CONTACTED
                     , contact_tm                                                                  -- CONTACT_TM_UTC
                     , contact_tm_string                                                           -- CONTACT_TM
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,10)))                     -- JOURNAL_ENTRY_TYP
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(80,1000)))                  -- COMMENTS
                     , observation_dt                                                              -- OBSERVATION_DT_UTC
                     , observation_dt_string                                                       -- OBSERVATION_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                    );
     FOR contlogj IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
     loop
         -- Set MIME type 
         random_num := round(DBMS_RANDOM.VALUE (1, 6));
         CASE random_num
            WHEN '1' THEN
             mime_typ := 'BMP';
            WHEN '2' THEN
             mime_typ := 'GIF';
            WHEN '3' THEN
             mime_typ := 'JPEG';
            WHEN '4' THEN
             mime_typ := 'PNG';
            WHEN '5' THEN
             mime_typ := 'X-PICT';
            ELSE
             mime_typ := 'TIFF';
         END CASE;

         attach_name := dbms_random.string ('U',trunc(dbms_random.value(1,50)))|| '.' || mime_typ;

         INSERT INTO CONTACT_LOG_ATTACHMENT (
                                              CONTACT_LOG_ATTACHMENT_ID
                                            , CONTACT_LOG_JOURNAL_ID
                                            , MIME_TYPE
                                            , ATTACHMENT_NAME
                                            , BINARY_ATTACHMENT
                                           ) 
         VALUES (
                   CONTACT_LOG_ATTACHMENT_SEQ_ID.nextval
                 , contact_log_journal_seq_id.currval
                 , mime_typ                                                                        -- MIME_TYPE
                 , attach_name                                                                     -- ATTACHMENT_NAME
                 , empty_blob()                                                                    -- BINARY_ATTACHMENT	
                ) RETURNING binary_attachment INTO lBlob;      
                
         DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

         DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

         DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                               SRC_LOB  => lFile,
                               AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

         DBMS_LOB.CLOSE(lFile);
         DBMS_LOB.CLOSE(lBlob);
     end loop;

     -- Load diet journal data
         -- set meal type
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
               meal_typ := 'BREAKFAST';
            WHEN '2' THEN
               meal_typ := 'LUNCH';
            WHEN '3' THEN
               meal_typ := 'DINNER';
            ELSE
               meal_typ := 'SNACK';
         END CASE;

         -- set calories
         random_num := round(DBMS_RANDOM.VALUE (1, 2));
         CASE random_num
            WHEN '1' THEN
               calories_d := 'FULL';
               calories_v := '45698712';
            ELSE
               calories_d := 'EMPTY';
               calories_v := '44569218';
         END CASE;
         
         -- set fat
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
               fat_d := 'SATURATED';
               fat_v := '789458';
            WHEN '2' THEN
               fat_d := 'TRANS';
               fat_v := '733158';
            WHEN '3' THEN
               fat_d := 'MONOUNSATURATED';
               fat_v := '798111';
            ELSE
               fat_d := 'POLYUNSATURATED';
               fat_v := '712368';
         END CASE;
         
         -- set carbohydrates
         random_num := round(DBMS_RANDOM.VALUE (1, 5));
         CASE random_num
            WHEN '1' THEN
               carbs_d := 'MONOSACCHARIDES';
               carbs_v := '6294';
            WHEN '2' THEN
               carbs_d := 'DISACCHARIDES';
               carbs_v := '651839';
            WHEN '3' THEN
               carbs_d := 'OLIGOSACCHARIDES';
               carbs_v := '365984';
            WHEN '4' THEN
               carbs_d := 'POLYSACCHARIDES';
               carbs_v := '641118';
            ELSE
               carbs_d := 'NUCLEOTIDES';
               carbs_v := '684885';
         END CASE;
         
         -- set protein
         random_num := round(DBMS_RANDOM.VALUE (1, 13));
         CASE random_num
            WHEN '1' THEN
               protein_d := 'MEAT';
               protein_v := '321598';
            WHEN '2' THEN
               protein_d := 'POULTRY';
               protein_v := '3126584';
            WHEN '3' THEN
               protein_d := 'FISH';
               protein_v := '368847';
            WHEN '4' THEN
               protein_d := 'EGGS';
               protein_v := '3156987';
            WHEN '5' THEN
               protein_d := 'DAIRY';
               protein_v := '1111115';
            WHEN '6' THEN
               protein_d := 'LEGUMES';
               protein_v := '444448';
            WHEN '7' THEN
               protein_d := 'CEREAL';
               protein_v := '2222211';
            WHEN '8' THEN
               protein_d := 'BEANS';
               protein_v := '333302';
            WHEN '9' THEN
               protein_d := 'PULSES';
               protein_v := '1000000555';
            WHEN '10' THEN
               protein_d := 'GRAINS';
               protein_v := '90259877';
            WHEN '11' THEN
               protein_d := 'NUTS';
               protein_v := '1505552';
            WHEN '12' THEN
               protein_d := 'SEEDS';
               protein_v := '58770048';
            ELSE
               protein_d := 'SOYA PRODUCTS';
               protein_v := '90807060';
         END CASE;
         
         random_num3     := DBMS_RANDOM.VALUE (-1, -4);
         random_num4     := DBMS_RANDOM.VALUE (-1, -4);
         meal_tm         := fixed_date + random_num3;
         observation_dt  := fixed_date + random_num4;

         meal_tm_string        := to_char(meal_tm-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         observation_dt_string := to_char(observation_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO DIET_JOURNAL
                    (
                       DIET_JOURNAL_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
                     , MEAL_TYP
                     , MEAL_TM_UTC
                     , MEAL_TM
--                     , MEAL_DESCRIPTION
                     , CALORIES_VAL
                     , CALORIES_CODE
                     , CALORIES_DSPLYTXT
                     , CALORIES_CODSYS
                     , FAT_VAL
                     , FAT_CODE
                     , FAT_DSPLYTXT
                     , FAT_CODSYS
                     , CARBOHYDRATES_VAL
                     , CARBOHYDRATES_CODE
                     , CARBOHYDRATES_DSPLYTXT
                     , CARBOHYDRATES_CODSYS
                     , PROTEIN_VAL
                     , PROTEIN_CODE
                     , PROTEIN_DSPLYTXT
                     , PROTEIN_CODSYS
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , JOURNAL_ENTRY_TYP
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , OBSERVATION_DT_UTC
                     , OBSERVATION_DT
                     , DATA_ENTERER
                    )
             VALUES
                    (
                       diet_journal_seq_id.nextval                                                 -- DIET_JOURNAL_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , meal_typ                                                                    -- MEAL_TYP
                     , meal_tm                                                                     -- MEAL_TM_UTC
                     , meal_tm_string                                                              -- MEAL_TM
--                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- MEAL_DESCRIPTION
                     , round(DBMS_RANDOM.VALUE (20, 2000))                                         -- CALORIES_VAL
                     , calories_v                                                                  -- CALORIES_CODE
                     , calories_d                                                                  -- CALORIES_DSPLYTXT
                     , NULL                                                                        -- CALORIES_CODSYS
                     , round(DBMS_RANDOM.VALUE (1, 100))                                           -- FAT_VAL
                     , fat_v                                                                       -- FAT_CODE
                     , fat_d                                                                       -- FAT_DSPLYTXT
                     , NULL                                                                        -- FAT_CODSYS
                     , round(DBMS_RANDOM.VALUE (1, 100))                                           -- CARBOHYDRATES_VAL
                     , carbs_v                                                                     -- CARBOHYDRATES_CODE
                     , carbs_d                                                                     -- CARBOHYDRATES_DSPLYTXT
                     , NULL                                                                        -- CARBOHYDRATES_CODSYS
                     , round(DBMS_RANDOM.VALUE (1, 100))                                           -- PROTEIN_VAL
                     , protein_v                                                                   -- PROTEIN_CODE
                     , protein_d                                                                   -- PROTEIN_DSPLYTXT
                     , NULL                                                                        -- PROTEIN_CODSYS
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,10)))                     -- JOURNAL_ENTRY_TYP
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(80,1000)))                  -- COMMENTS
                     , observation_dt                                                              -- OBSERVATION_DT_UTC
                     , observation_dt_string                                                       -- OBSERVATION_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                    );

         FOR dietj IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
         loop
         -- Set MIME type 
             random_num := round(DBMS_RANDOM.VALUE (1, 6));
             CASE random_num
                WHEN '1' THEN
                 mime_typ := 'BMP';
                WHEN '2' THEN
                 mime_typ := 'GIF';
                WHEN '3' THEN
                 mime_typ := 'JPEG';
                WHEN '4' THEN
                 mime_typ := 'PNG';
                WHEN '5' THEN
                 mime_typ := 'X-PICT';
                ELSE
                 mime_typ := 'TIFF';
             END CASE;
             
             attach_name := dbms_random.string ('U',trunc(dbms_random.value(1,50)))|| '.' || mime_typ;
             
             INSERT INTO DIET_ATTACHMENT (
                                                  DIET_ATTACHMENT_ID
                                                , DIET_JOURNAL_ID
                                                , MIME_TYPE
                                                , ATTACHMENT_NAME
                                                , BINARY_ATTACHMENT
                                               ) 
             VALUES (
                       DIET_ATTACHMENT_SEQ_ID.nextval
                     , diet_journal_seq_id.currval
                     , mime_typ                                                                    -- MIME_TYPE
                     , attach_name                                                                 -- ATTACHMENT_NAME
                     , empty_blob()                                                                -- BINARY_ATTACHMENT	
                    ) RETURNING binary_attachment INTO lBlob;      
                
             DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

             DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

             DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                                   SRC_LOB  => lFile,
                                   AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

             DBMS_LOB.CLOSE(lFile);
             DBMS_LOB.CLOSE(lBlob);
         end loop;
         
     -- Load daily event journal data
         random_num4    := DBMS_RANDOM.VALUE (-1, -4);
         observation_dt := fixed_date + random_num4;

         observation_dt_string := to_char(observation_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO DAILY_EVENT_JOURNAL
                    (
                       DAILY_EVENT_JOURNAL_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
                     , EVENT_TITLE
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , JOURNAL_ENTRY_TYP
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , OBSERVATION_DT_UTC
                     , OBSERVATION_DT
                     , DATA_ENTERER
                    )
             VALUES
                    (
                       daily_event_journal_seq_id.nextval                                          -- DAILY_EVENT_JOURNAL_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , dbms_random.string ('U',trunc(dbms_random.value(10,250)))                   -- EVENT_TITLE
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,10)))                     -- JOURNAL_ENTRY_TYP
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(80,1000)))                  -- COMMENTS
                     , observation_dt                                                              -- OBSERVATION_DT_UTC
                     , observation_dt_string                                                       -- OBSERVATION_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                    );

         FOR dayevntj IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
         loop
             -- Set MIME type 
             random_num := round(DBMS_RANDOM.VALUE (1, 6));
             CASE random_num
                WHEN '1' THEN
                 mime_typ := 'BMP';
                WHEN '2' THEN
                 mime_typ := 'GIF';
                WHEN '3' THEN
                 mime_typ := 'JPEG';
                WHEN '4' THEN
                 mime_typ := 'PNG';
                WHEN '5' THEN
                 mime_typ := 'X-PICT';
                ELSE
                 mime_typ := 'TIFF';
             END CASE;
         
             attach_name := dbms_random.string ('U',trunc(dbms_random.value(1,50)))|| '.' || mime_typ;
             
             INSERT INTO DAILY_EVENT_ATTACHMENT (
                                                  DAILY_EVENT_ATTACHMENT_ID
                                                , DAILY_EVENT_JOURNAL_ID
                                                , MIME_TYPE
                                                , ATTACHMENT_NAME
                                                , BINARY_ATTACHMENT
                                               ) 
             VALUES (
                       DAILY_EVENT_ATTACHMENT_SEQ_ID.nextval
                     , daily_event_journal_seq_id.currval
                     , mime_typ                                                                    -- MIME_TYPE
                     , attach_name                                                                 -- ATTACHMENT_NAME
                     , empty_blob()                                                                -- BINARY_ATTACHMENT	
                    ) RETURNING binary_attachment INTO lBlob;      
                
             DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

             DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

             DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                                   SRC_LOB  => lFile,
                                   AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

             DBMS_LOB.CLOSE(lFile);
             DBMS_LOB.CLOSE(lBlob);
         end loop;
                    
     -- Load mood journal data
         -- set mood
         random_num := round(DBMS_RANDOM.VALUE (1, 11));
         CASE random_num
            WHEN '1' THEN
               mood_d := 'HAPPY';
               mood_v := '0';
            WHEN '2' THEN
               mood_d := 'SAD';
               mood_v := '1';
            WHEN '3' THEN
               mood_d := 'GRUMPY';
               mood_v := '2';
            WHEN '4' THEN
               mood_d := 'ANGRY';
               mood_v := '3';
            WHEN '5' THEN
               mood_d := 'ANXIETY';
               mood_v := '4';
            WHEN '6' THEN
               mood_d := 'LOVE';
               mood_v := '5';
            WHEN '7' THEN
               mood_d := 'GREED';
               mood_v := '6';
            WHEN '8' THEN
               mood_d := 'FEAR';
               mood_v := '7';
            WHEN '9' THEN
               mood_d := 'JEALOUS';
               mood_v := '8';
            WHEN '10' THEN
               mood_d := 'HOPEFUL';
               mood_v := '9';
            ELSE
               mood_d := 'DEPRESSED';
               mood_v := '10';
         END CASE;
         
         random_num4    := DBMS_RANDOM.VALUE (-1, -4);
         observation_dt := fixed_date + random_num4;
         
         observation_dt_string := to_char(observation_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO MOOD_JOURNAL
                    (
                       MOOD_JOURNAL_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
--                     , MOOD_CODE
                     , MOOD_TXT
--                     , MOOD_CODSYS
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , JOURNAL_ENTRY_TYP
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , OBSERVATION_DT_UTC
                     , OBSERVATION_DT
                     , DATA_ENTERER
                    )
             VALUES
                    (
                       mood_journal_seq_id.nextval                                                 -- MOOD_JOURNAL_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                      , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                   -- REQUEST_ID
--                     , mood_v                                                                      -- MOOD_CODE
                     , mood_d                                                                      -- MOOD_TXT
--                     , NULL                                                                        -- MOOD_CODSYS
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,10)))                     -- JOURNAL_ENTRY_TYP
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(80,1000)))                  -- COMMENTS
                     , observation_dt                                                              -- OBSERVATION_DT_UTC
                     , observation_dt_string                                                       -- OBSERVATION_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                    );

         FOR moodj IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
         loop
             -- Set MIME type 
             random_num := round(DBMS_RANDOM.VALUE (1, 6));
             CASE random_num
                WHEN '1' THEN
                 mime_typ := 'BMP';
                WHEN '2' THEN
                 mime_typ := 'GIF';
                WHEN '3' THEN
                 mime_typ := 'JPEG';
                WHEN '4' THEN
                 mime_typ := 'PNG';
                WHEN '5' THEN
                 mime_typ := 'X-PICT';
                ELSE
                 mime_typ := 'TIFF';
             END CASE;
         
             attach_name := dbms_random.string ('U',trunc(dbms_random.value(1,50)))|| '.' || mime_typ;
   
             INSERT INTO MOOD_ATTACHMENT (
                                                  MOOD_ATTACHMENT_ID
                                                , MOOD_JOURNAL_ID
                                                , MIME_TYPE
                                                , ATTACHMENT_NAME
                                                , BINARY_ATTACHMENT
                                               ) 
             VALUES (
                       MOOD_ATTACHMENT_SEQ_ID.nextval
                     , mood_journal_seq_id.currval
                     , mime_typ                                                                    -- MIME_TYPE
                     , attach_name                                                                 -- ATTACHMENT_NAME
                     , empty_blob()                                                                -- BINARY_ATTACHMENT	
                    ) RETURNING binary_attachment INTO lBlob;      
                
             DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

             DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

             DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                                   SRC_LOB  => lFile,
                                   AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

             DBMS_LOB.CLOSE(lFile);
             DBMS_LOB.CLOSE(lBlob);
         end loop;
         
     -- Load calendar event data
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (-4, -6);
         start_dt    := fixed_date + random_num2;
         stop_dt     := start_dt + DBMS_RANDOM.VALUE (.1, 3);
         remind_dt   := stop_dt + DBMS_RANDOM.VALUE (.1, 3);
         
         start_dt_string  := to_char(start_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         stop_dt_string   := to_char(stop_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         remind_dt_string := to_char(remind_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO CALENDAR_EVENT 
                    (
                       CALENDAR_EVENT_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REQUEST_ID
                     , CALENDAR_EVENT_TITLE
                     , CALENDAR_EVENT_START_DT_UTC  
                     , CALENDAR_EVENT_START_DT
                     , CALENDAR_EVENT_STOP_DT_UTC
                     , CALENDAR_EVENT_STOP_DT
                     , CALENDAR_EVENT_REMIND_DT_UTC
                     , CALENDAR_EVENT_REMIND_dt
                     , CALENDAR_EVENT_NOTE
                     , REPEAT_FREQUENCY
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                    )
             VALUES
                    (
                       calendar_event_seq_id.nextval                                               -- CALENDAR_EVENT_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CALENDAR_EVENT_TITLE
                     , start_dt                                                                    -- CALENDAR_EVENT_START_DT_UTC
                     , start_dt_string                                                             -- CALENDAR_EVENT_START_DT
                     , stop_dt                                                                     -- CALENDAR_EVENT_STOP_DT_UTC
                     , stop_dt_string                                                              -- CALENDAR_EVENT_STOP_DT
                     , remind_dt                                                                   -- CALENDAR_EVENT_REMIND_DT_UTC
                     , remind_dt_string                                                            -- CALENDAR_EVENT_REMIND_DT
                     , dbms_random.string ('U',trunc(dbms_random.value(40,250)))                   -- CALENDAR_EVENT_NOTE
                     , round(dbms_random.value(1,10))                                              -- REPEAT_FREQUENCY
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , NULL                                                                        -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                    );
     -- Load assessment_total_score data
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (0, -2);
         start_dt    := fixed_date + random_num2;
         
         start_dt_string  := to_char(start_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO ASSESSMENT_TOTAL_SCORE 
                    (
                       ASSESSMENT_TOTAL_SCORE_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , CATEGORY_TXT
                     , SCORE_NBR
                     , ASSESSMENT_SCORE_DT
                     , ASSESSMENT_SCORE_DT_UTC
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                    )
             VALUES
                    (
                       ASSESSMENT_TOTAL_SCORE_SEQ_ID.nextval                                       -- ASSESSMENT_TOTAL_SCORE_ID
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CATEGORY_TXT
                     , round(dbms_random.value(1,100))                                             -- SCORE_NBR
                     , start_dt_string                                                             -- ASSESSMENT_SCORE_DT
                     , start_dt                                                                    -- ASSESSMENT_SCORE_DT_UTC
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                    );
        
      -- load assessment_document
         random_num := round(DBMS_RANDOM.VALUE (1, 2));
         CASE random_num
            WHEN '1' THEN
             category_text := 'Baseline Pain Assessment';
            ELSE
             category_text := 'Pain Reassessment';
         END CASE;
          
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (0, -2);
         start_dt    := fixed_date + random_num2;
         
         start_dt_string  := to_char(start_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         
         INSERT INTO ASSESSMENT_DOCUMENT 
                (
                       ASSESSMENT_DOCUMENT_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , CATEGORY_TXT
                     , CATEGORY_VERSION
                     , DATA_ENTERER
                     , ASSESSMENT_DT
                     , ASSESSMENT_DT_UTC
                     , ASSESSMENT_SCORE
                     , ASSESSMENT_RESPONSES
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                )
         VALUES (
                       ASSESSMENT_DOCUMENT_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , category_text                                                               -- CATEGORY_TXT
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- CATEGORY_VERSION
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- DATA_ENTERER
                     , start_dt_string                                                             -- ASSESSMENT_SCORE_DT
                     , start_dt                                                                    -- ASSESSMENT_SCORE_DT_UTC
                     , round(dbms_random.value(1,100))                                             -- ASSESSMENT_SCORE
                     , NULL                                                                        -- ASSESSMENT_RESPONSES
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                );
                
      -- load goal tracking
         random_num := round(DBMS_RANDOM.VALUE (1, 7));
         CASE random_num
            WHEN '1' THEN
             goal_name_txt := 'To do all the exercises assigned to me by the therapist daily';
            WHEN '2' THEN
             goal_name_txt := 'To reduce my pain medication intake by 50% in 30 days';
            WHEN '3' THEN
             goal_name_txt := 'To have at least 6 hours of uninterrupted sleep every night';
            WHEN '4' THEN
             goal_name_txt := 'I want to feel less stressed';
            WHEN '5' THEN
             goal_name_txt := 'I want to get rid of the pain in my legs before the end of July';
            WHEN '6' THEN
             goal_name_txt := 'Walk daily';
            ELSE
             goal_name_txt := 'Breathe Deeply Every 2 hours';
         END CASE;

         INSERT INTO GOAL_TRACKING 
                (
                       GOAL_TRACKING_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , ENTRY_DT
                     , ENTRY_DT_UTC
                     , COMPLETION_PERCENT
                     , GOAL_DETAILS
                     , GOAL_NAME
                     , GOAL_TYPE
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , COMMENTS
                     , DATA_ENTERER
                )
         VALUES (
                       GOAL_TRACKING_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , start_dt_string                                                             -- ENTRY_DT
                     , start_dt                                                                    -- ENTRY_DT_UTC
                     , round(DBMS_RANDOM.VALUE (1, 100))                                           -- COMPLETION_PERCENT
                     , dbms_random.string ('U',trunc(dbms_random.value(25,500)))                   -- GOAL_DETAILS
                     , goal_name_txt                                                               -- GOAL_NAME
                     , dbms_random.string ('U',trunc(dbms_random.value(3,10)))                     -- GOAL_TYPE
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(25,500)))                   -- COMMENTS
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                );
        
         -- set dates 
         random_num  := DBMS_RANDOM.VALUE (-2, -6);
         random_num2 := DBMS_RANDOM.VALUE (-2, -6);
         start_dt    := fixed_date + random_num;
         stop_dt     := fixed_date + random_num2;
         
         start_dt_string  := to_char(start_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
         stop_dt_string   := to_char(stop_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
                
         INSERT INTO PAIN_REASSESSMENT 
                (
                       PAIN_REASSESSMENT_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , REASSESSMENT_DT
                     , REASSESSMENT_DT_UTC
                     , ASSESSMENT_DT
                     , ASSESSMENT_DT_UTC
                     , IMPRESSION_OF_CHANGE_RATING
                     , IMPRESSION_OF_CHANGE_QUESTION
                     , DECREASE_IN_SOCIAL_RATING
                     , DECREASE_IN_SOCIAL_QUESTION
                     , MIN_PAIN_LEVEL_RATING
                     , MIN_PAIN_LEVEL_QUESTION
                     , MAX_PAIN_LEVEL_RATING
                     , MAX_PAIN_LEVEL_QUESTION
                     , AVG_PAIN_LEVEL_RATING
                     , AVG_PAIN_LEVEL_QUESTION
                     , CONCERN_PAIN_ENDING_RATING
                     , CONCERN_PAIN_ENDING_QUESTION
                     , CONCERN_PAIN_TLRNC_RATING
                     , CONCERN_PAIN_TLRNC_QUESTION
                     , CONCERN_EXCRSZ_INJRY_RATING
                     , CONCERN_EXCRSZ_INJRY_QUESTION
                     , DEPRESSION_LEVEL_RATING
                     , DEPRESSION_LEVEL_QUESTION
                     , PAIN_IMPACT_ON_MOOD_RATING
                     , PAIN_IMPACT_ON_MOOD_QUESTION
                     , PAIN_IMPACT_ON_WORK_RATING
                     , PAIN_IMPACT_ON_WORK_QUESTION
                     , PAIN_INTERFERENCE_RATING
                     , PAIN_INTERFERENCE_QUESTION
                     , SLEEP_REFRESHMENT_RATING
                     , SLEEP_REFRESHMENT_QUESTION
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , DATA_ENTERER                
                )
         VALUES (
                       PAIN_REASSESSMENT_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , start_dt_string                                                             -- REASSESSMENT_DT
                     , start_dt                                                                    -- REASSESSMENT_DT_UTC
                     , stop_dt_string                                                              -- ASSESSMENT_DT
                     , stop_dt                                                                     -- ASSESSMENT_DT_UTC
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- IMPRESSION_OF_CHANGE_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- IMPRESSION_OF_CHANGE_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 5))                                             -- DECREASE_IN_SOCIAL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- DECREASE_IN_SOCIAL_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 5))                                             -- MIN_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- MIN_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (6, 10))                                            -- MAX_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- MAX_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- AVG_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- AVG_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_PAIN_ENDING_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_PAIN_ENDING_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_PAIN_TLRNC_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_PAIN_TLRNC_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_EXCRSZ_INJRY_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_EXCRSZ_INJRY_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- DEPRESSION_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- DEPRESSION_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_IMPACT_ON_MOOD_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_IMPACT_ON_MOOD_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_IMPACT_ON_WORK_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_IMPACT_ON_WORK_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_INTERFERENCE_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_INTERFERENCE_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- SLEEP_REFRESHMENT_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(10,75)))                    -- SLEEP_REFRESHMENT_QUESTION
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                );
        
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (-2, -6);
         stop_dt     := fixed_date + random_num2;
         
         stop_dt_string   := to_char(stop_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
                
         INSERT INTO PAIN_ASSESSMENT 
                (
                       PAIN_ASSESSMENT_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , ASSESSMENT_DT
                     , ASSESSMENT_DT_UTC
                     , DECREASE_IN_SOCIAL_RATING
                     , DECREASE_IN_SOCIAL_QUESTION
                     , MIN_PAIN_LEVEL_RATING
                     , MIN_PAIN_LEVEL_QUESTION
                     , MAX_PAIN_LEVEL_RATING
                     , MAX_PAIN_LEVEL_QUESTION
                     , AVG_PAIN_LEVEL_RATING
                     , AVG_PAIN_LEVEL_QUESTION
                     , CONCERN_PAIN_ENDING_RATING
                     , CONCERN_PAIN_ENDING_QUESTION
                     , CONCERN_PAIN_TLRNC_RATING
                     , CONCERN_PAIN_TLRNC_QUESTION
                     , CONCERN_EXCRSZ_INJRY_RATING
                     , CONCERN_EXCRSZ_INJRY_QUESTION
                     , DEPRESSION_LEVEL_RATING
                     , DEPRESSION_LEVEL_QUESTION
                     , PAIN_IMPACT_ON_MOOD_RATING
                     , PAIN_IMPACT_ON_MOOD_QUESTION
                     , PAIN_IMPACT_ON_WORK_RATING
                     , PAIN_IMPACT_ON_WORK_QUESTION
                     , PAIN_INTERFERENCE_RATING
                     , PAIN_INTERFERENCE_QUESTION
                     , SLEEP_REFRESHMENT_RATING
                     , SLEEP_REFRESHMENT_QUESTION
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , DATA_ENTERER
                )
         VALUES (
                       PAIN_ASSESSMENT_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , stop_dt_string                                                              -- ASSESSMENT_DT
                     , stop_dt                                                                     -- ASSESSMENT_DT_UTC
                     , round(DBMS_RANDOM.VALUE (1, 5))                                             -- DECREASE_IN_SOCIAL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- DECREASE_IN_SOCIAL_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 5))                                             -- MIN_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- MIN_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (6, 10))                                            -- MAX_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- MAX_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- AVG_PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- AVG_PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_PAIN_ENDING_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_PAIN_ENDING_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_PAIN_TLRNC_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_PAIN_TLRNC_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- CONCERN_EXCRSZ_INJRY_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- CONCERN_EXCRSZ_INJRY_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- DEPRESSION_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- DEPRESSION_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_IMPACT_ON_MOOD_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_IMPACT_ON_MOOD_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_IMPACT_ON_WORK_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_IMPACT_ON_WORK_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- PAIN_INTERFERENCE_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,50)))                    -- PAIN_INTERFERENCE_QUESTION
                     , round(DBMS_RANDOM.VALUE (2, 8))                                             -- SLEEP_REFRESHMENT_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(10,75)))                    -- SLEEP_REFRESHMENT_QUESTION
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER     
                );

      -- load skill tracking
         -- skill type
         random_num := round(DBMS_RANDOM.VALUE (1, 11));
         CASE random_num
            WHEN '1' THEN
             skill_type := 'Obtaining restful sleep';
            WHEN '2' THEN
             skill_type := 'Reducing stress, Exercise';
            WHEN '3' THEN
             skill_type := 'Stretching';
            WHEN '4' THEN
             skill_type := 'Deep breathing';
            WHEN '5' THEN
             skill_type := 'Relation exercises';
            WHEN '6' THEN
             skill_type := 'Activity Pacing';
            WHEN '7' THEN
             skill_type := 'Pleasant Activity Scheduling';
            WHEN '8' THEN
             skill_type := 'Using brainpower to affect pain';
            WHEN '9' THEN
             skill_type := 'Distraction';
            WHEN '10' THEN
             skill_type := 'Icing and counter-stimulation';
            ELSE
             skill_type := 'Other';
         END CASE;
                 
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (-1, -3);
         stop_dt     := fixed_date + random_num2;
         
         stop_dt_string   := to_char(stop_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
                
         INSERT INTO SKILL_TRACKING 
                (
                       SKILL_TRACKING_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , ENTRY_DT
                     , ENTRY_DT_UTC
                     , SKILL_SUCCESS_LEVEL
                     , SKILL_TYPE
                     , SKILL_FREQUENCY
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , DATA_ENTERER                
                     , COMMENTS
                )
         VALUES (
                       SKILL_TRACKING_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , stop_dt_string                                                              -- ENTRY_DT
                     , stop_dt                                                                     -- ENTRY_DT_UTC
                     , round(DBMS_RANDOM.VALUE (1, 5))                                             -- SKILL_SUCCESS_LEVEL
                     , skill_type                                                                  -- SKILL_TYPE
                     , dbms_random.string ('U',trunc(dbms_random.value(4,10)))                     -- SKILL_FREQUENCY
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                     , dbms_random.string ('U',trunc(dbms_random.value(25,250)))                   -- COMMENTS
                );
        
         -- set dates 
         random_num2 := DBMS_RANDOM.VALUE (-1, -3);
         stop_dt     := fixed_date + random_num2;
         
         stop_dt_string   := to_char(stop_dt-(300/1440),'YYYYMMDDHH24MISS') || '-0500';
                
         INSERT INTO PAIN_EVENT 
                (
                       PAIN_EVENT_ID
                     , RECORDIDENTIFIER_NAMESPACEID
                     , RECORDIDENTIFIER_UNIVERSALID
                     , RECORDIDENTIFIER_UNIVERSTYP
                     , REQUEST_ID
                     , PATIENT_IDENTITY
                     , PATIENT_ASSIGNING_FACILITY
                     , PATIENT_ASSIGNING_AUTHORITY
                     , ENTRY_DT
                     , ENTRY_DT_UTC
                     , PAIN_TYPE
                     , TREATMENT_USEFULNESS_RATING
                     , TREATMENT_USEFULNESS_QUESTION
                     , PAIN_LEVEL_RATING
                     , PAIN_LEVEL_QUESTION
                     , PAIN_IMPACT_ON_MOOD_RATING
                     , PAIN_IMPACT_ON_MOOD_QUESTION
                     , PAIN_INTERFERENCE_RATING
                     , PAIN_INTERFERENCE_QUESTION
                     , SLEEP_REFRESHMENT_RATING
                     , SLEEP_REFRESHMENT_QUESTION
                     , DEPRESSION_LEVEL_RATING
                     , DEPRESSION_LEVEL_QUESTION
                     , IMPACT_ON_FUNCTION_RATING
                     , IMPACT_ON_FUNCTION_QUESTION
                     , COMMENTS
                     , PAIN_WORSENED_BY
                     , PAIN_IMPROVED_BY
                     , PAIN_ACTIVATED_BY
                     , PATIENT_NAME_PREFIX
                     , PATIENT_NAME_GIVEN
                     , PATIENT_NAME_FAMILY
                     , PATIENT_NAME_SUFFIX
                     , PATIENT_NAME_TITLE
                     , PATIENT_NAME_MIDDLE
                     , ALLOW_ACCESS_BY_CLIN_STAFF
                     , DATA_ENTERER
                )
         VALUES (
                       PAIN_EVENT_SEQ_ID.nextval
                     , NULL                                                                        -- RECORDIDENTIFIER_NAMESPACEID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSALID
                     , NULL                                                                        -- RECORDIDENTIFIER_UNIVERSTYP
                     , dbms_random.string ('U',trunc(dbms_random.value(10,12)))                    -- REQUEST_ID
                     , v_vpid                                                                      -- PATIENT_IDENTITY
                     , '200DOD'                                                                    -- PATIENT_ASSIGNING_FACILITY
                     , 'USDOD'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                     , stop_dt_string                                                              -- ENTRY_DT
                     , stop_dt                                                                     -- ENTRY_DT_UTC
                     , dbms_random.string ('U',trunc(dbms_random.value(4,10)))                     -- PAIN_TYPE
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- TREATMENT_USEFULNESS_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- TREATMENT_USEFULNESS_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- PAIN_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- PAIN_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- PAIN_IMPACT_ON_MOOD_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- PAIN_IMPACT_ON_MOOD_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- PAIN_INTERFERENCE_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- PAIN_INTERFERENCE_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- SLEEP_REFRESHMENT_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- SLEEP_REFRESHMENT_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- DEPRESSION_LEVEL_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- DEPRESSION_LEVEL_QUESTION
                     , round(DBMS_RANDOM.VALUE (1, 10))                                            -- IMPACT_ON_FUNCTION_RATING
                     , dbms_random.string ('U',trunc(dbms_random.value(25,100)))                   -- IMPACT_ON_FUNCTION_QUESTION
                     , dbms_random.string ('U',trunc(dbms_random.value(25,300)))                   -- COMMENTS
                     , dbms_random.string ('U',trunc(dbms_random.value(10,30)))                    -- PAIN_WORSENED_BY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,30)))                    -- PAIN_IMPROVED_BY
                     , dbms_random.string ('U',trunc(dbms_random.value(10,30)))                    -- PAIN_ACTIVATED_BY
                     , NULL                                                                        -- PATIENT_NAME_PREFIX	
                     , dbms_random.string ('U',trunc(dbms_random.value(5,20)))                     -- PATIENT_NAME_GIVEN	
                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                     -- PATIENT_NAME_FAMILY	
                     , NULL                                                                        -- PATIENT_NAME_SUFFIX	
                     , NULL                                                                        -- PATIENT_NAME_TITLE	
                     , NULL                                                                        -- PATIENT_NAME_MIDDLE	
                     , 'T'                                                                         -- ALLOW_ACCESS_BY_CLIN_STAFF
                     , dbms_random.string ('U',trunc(dbms_random.value(10,20)))                    -- DATA_ENTERER
                );

         -- load 0-2 pain quality
         random_num2 := round(dbms_random.value(0,2));
         FOR j IN 1 .. random_num2
         LOOP
             INSERT INTO PAIN_QUALITY_TODAY 
                    (
                          PAIN_QUALITY_TODAY_ID
                        , PAIN_EVENT_ID
                        , PAIN_QUALITY_TXT
                    )
             VALUES (
                          pain_quality_today_seq_id.nextval
                        , pain_event_seq_id.currval
                        , dbms_random.string ('U',trunc(dbms_random.value(50,300)))                -- PAIN_QUALITY_TXT
                    );
         END LOOP;
         
         -- load 0-3 pain locations
         random_num2 := round(dbms_random.value(0,3));
--         FOR j IN 1 .. random_num2
--         LOOP
--             INSERT INTO PAIN_LOCATIONS 
--                    (
--                          PAIN_LOCATIONS_ID
--                        , PAIN_EVENT_ID
--                        , PAIN_LOCATION_TXT
--                    )
--             VALUES (
--                          PAIN_LOCATIONS_seq_id.nextval
--                        , pain_event_seq_id.currval
--                        , dbms_random.string ('U',trunc(dbms_random.value(50,300)))                -- PAIN_QUALITY_TXT
--                    );
--         END LOOP;
             
         -- load 0-2 treatment
         random_num2 := round(dbms_random.value(0,2));
         FOR j IN 1 .. random_num2
         LOOP
             INSERT INTO TREATMENT 
                    (
                          TREATMENT_ID
                        , PAIN_EVENT_ID
                        , TREATMENT_TXT
                    )
             VALUES (
                          TREATMENT_seq_id.nextval
                        , pain_event_seq_id.currval
                        , dbms_random.string ('U',trunc(dbms_random.value(50,300)))                -- PAIN_QUALITY_TXT
                    );
         END LOOP; 
       END LOOP;
       COMMIT;
   END LOOP;

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