/******************************************************************************
   NAME:       load_lab_load34_arms.sql
   PURPOSE:    This script will create lab records that map to 
               VHIM version 3.6.1  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        10/19/2007  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        11/05/2007  M. Jacobsen      - added hardcoding of PATIENT_ASSIGNING_FACILITY to 'DNS   -605'
   1.3        01/08/2008  M. Jacobsen      - remove common schema
******************************************************************************/
set serveroutput on
DECLARE
   prev_num                     NUMBER          := 0;
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_num3                  NUMBER          := 0;
   random_num4                  NUMBER          := 0;
   random_numa                  NUMBER          := 0;
   random_numv                  NUMBER          := 0;
   random_nump                  NUMBER          := 0;
   birthnum                     NUMBER          := 0;
   birth_dt                     VARCHAR2 (30);
   authorppid                   NUMBER;
   verifyppid                   NUMBER;
   performppid                  NUMBER;
   attenderppid                 NUMBER;
   upd_date                     VARCHAR2 (30);
   completion_date              VARCHAR2 (30);
   specimen_date                VARCHAR2 (30);
   collection_date              VARCHAR2 (30);
   ord_date                     VARCHAR2 (30);
   value_date                   VARCHAR2 (30);
   ppa_date                     VARCHAR2 (30);
   ppv_date                     VARCHAR2 (30);
   ppp_date                     VARCHAR2 (30);
   end_date                     NUMBER;
   patient_id                   NUMBER;
   pract_vpid                   VARCHAR2 (30);
   lab_test_prm_seq_id_val      NUMBER;
   lab_test_req_seq_id_val      NUMBER;
   lab_test_ord_seq_id_val      NUMBER;
   lab_test_cmt_seq_id_val      NUMBER;
   specimen_coll_seq_id_val     NUMBER;
   specimen_seq_id_val          NUMBER;
   chemistry_rslt_seq_id_val    NUMBER;
   lab_pract_part_seq_id_val        NUMBER;
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (20);
   ordsite_cd_d                 VARCHAR2 (100);
   ordsite_cd_v                 VARCHAR2 (20);
   reqinst_cd_d                 VARCHAR2 (100);
   reqinst_cd_v                 VARCHAR2 (20);
   poc_cd_d                     VARCHAR2 (100);
   poc_cd_v                     VARCHAR2 (20);
   ordprty_cd_d                 VARCHAR2 (100);
   ordprty_cd_v                 VARCHAR2 (20);
   test_cd_d                    VARCHAR2 (100);
   test_cd_v                    VARCHAR2 (20);
   alt_codsys_test              VARCHAR2 (50);
   alt_display_test             VARCHAR2 (100);
   alt_code_test                VARCHAR2 (30);
   alt_reason_test              VARCHAR2 (100);   
   comment_cd_d                 VARCHAR2 (100);
   comment_cd_v                 VARCHAR2 (20);
   colmeth_cd_d                 VARCHAR2 (100);
   colmeth_cd_v                 VARCHAR2 (20);
   colsite_cd_d                 VARCHAR2 (100);
   colsite_cd_v                 VARCHAR2 (20);
   snomed_cd_d                  VARCHAR2 (100);
   snomed_cd_v                  VARCHAR2 (20);
   spectyp_cd_d                 VARCHAR2 (100);
   spectyp_cd_v                 VARCHAR2 (20);
   refrange_cd_d                VARCHAR2 (100);
   refrange_cd_v                VARCHAR2 (20);
   method_cd_d                  VARCHAR2 (100);
   method_cd_v                  VARCHAR2 (20);
   value_cd_d                   VARCHAR2 (100);
   value_cd_v                   VARCHAR2 (20);
   value_1_string               VARCHAR2 (200);
   race_code_d                  VARCHAR2 (100);
   race_code_v                  VARCHAR2 (30);
   gender_code_d                VARCHAR2 (100);
   gender_code_v                VARCHAR2 (30);
   ethnic_code_d                VARCHAR2 (100);
   ethnic_code_v                VARCHAR2 (30);
   gmr_alrgy_d                  varchar2(100);
   gmr_alrgy_v                  varchar2(30);
   patient_seq_id_val           NUMBER;
   lab_patient_visit_seq_id_val     NUMBER;
   hosploc_cd_d                 varchar2(100);
   hosploc_cd_v                 varchar2(100);
   v_vpid                       VARCHAR2 (30);
   starting_patient_vpid        NUMBER;
   fixed_date			        DATE;
   
BEGIN
   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'Lab Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));

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

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

--loop thru x times
   FOR z IN 1 .. &1
   LOOP
       -- Loop thru how many patients to assign allergies to
       FOR jj in 1 .. &2
	   LOOP
         
         random_num  := DBMS_RANDOM.VALUE (-10, -30);
         random_num2 := DBMS_RANDOM.VALUE (-1, -15);
         random_num3 := DBMS_RANDOM.VALUE (-3, -18);
         random_numv := DBMS_RANDOM.VALUE (-1, -5);
         birthnum    := DBMS_random.VALUE (-10950, -36500);

         SELECT TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numv, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss'),
                ROUND (DBMS_RANDOM.VALUE (1, 50000))
           INTO completion_date,
                specimen_date,
                ppv_date,
                upd_date,
                birth_dt,
                patient_id
           FROM DUAL;

         -- get verifier practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 3));

         IF random_num = 1
         THEN
             SELECT lab_pract_part_seq_id.NEXTVAL
               INTO verifyppid
               FROM DUAL;
             
             insert_practpart.LAB_INSERT_MESSAGE( ppv_date, verifyppid );
         ELSE
             verifyppid := NULL;
         END IF;
         
         SELECT lab_test_prm_seq_id.NEXTVAL
           INTO lab_test_prm_seq_id_val
           FROM DUAL;
               
         SELECT lab_test_req_seq_id.NEXTVAL
           INTO lab_test_req_seq_id_val
           FROM DUAL;

         -- get Patient seq id
         SELECT lab_patient_info_seq_id.NEXTVAL
           INTO patient_seq_id_val
           FROM DUAL;
         
         -- Set Ethnic Characteristic information
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
             ethnic_code_d    := 'HISPANIC';
             ethnic_code_v    := '1238923';
            WHEN '2' THEN
             ethnic_code_d    := 'SPANISH';
             ethnic_code_v    := '1238924';
            WHEN '3' THEN
             ethnic_code_d    := 'AFRICAN AMERICAN';
             ethnic_code_v    := '1238925';
            ELSE
             ethnic_code_d    := 'ORIENTAL';
             ethnic_code_v    := '1238926';
         END CASE;

         -- Set gender
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               gender_code_d := 'MALE';
               gender_code_v := '1209109';
            WHEN '2' THEN
               gender_code_d := 'FEMALE';
               gender_code_v := '1209110';
            ELSE
               gender_code_d := 'UNKNOWN';
               gender_code_v := '1209111';
         END CASE;
         
         -- Set race code
         random_num := round(DBMS_RANDOM.VALUE (1, 7));
         CASE random_num
            WHEN '1' THEN
               race_code_d  := 'WHITE';
               race_code_v  := '21389023';
            WHEN '2' THEN
               race_code_d  := 'AFRICAN AMERICAN';
               race_code_v  := '21389024';
            WHEN '3' THEN
               race_code_d  := 'AMERICAN INDIAN';
               race_code_v  := '21389025';
            WHEN '4' THEN
               race_code_d  := 'JAPANESE';
               race_code_v  := '21389029';
            WHEN '5' THEN
               race_code_d  := 'KOREAN';
               race_code_v  := '21389030';
            WHEN '6' THEN
               race_code_d  := 'SAMOAN';
               race_code_v  := '21389021';
            ELSE
               race_code_d  := 'OTHER WHITE';
               race_code_v  := '21389022';
         END CASE;
         
         -- Load Patient Record
         INSERT INTO LAB_PATIENT_INFO (  
                                              LAB_PATIENT_INFO_ID
                                            , GENDER_CODE
                                            , GENDER_DSPLYTXT
                                            , GENDER_CODSYS
                                            , BIRTHDATE_DT
                                            , BIRTHDATE_DT_NBR
                                            , ETHNIC_GRP_CODE
                                            , ETHNIC_GRP_DSPLYTXT
                                            , ETHNIC_GRP_CODSYS
                                            , NAME_PREFIX
                                            , NAME_GIVEN
                                            , NAME_FAMILY
                                            , NAME_SUFFIX
                                            , NAME_TITLE
                                            , NAME_MIDDLE
                                            , RACE_CODE
                                            , RACE_DSPLYTXT
                                            , RACE_CODSYS
                                            , ZIP_PLUS4
                              )
         VALUES (
                   patient_seq_id_val                                          -- PATIENT_ID
                 , gender_code_v
                 , gender_code_d
                 , NULL --GENDER CODSYS
                 , birth_dt                                                     -- BIRTHDATE_DT	
                 , to_number(birth_dt,'99999999999999')                         -- BIRTHDATE_DT_nbr
                 , ethnic_code_v
                 , ethnic_code_d
                 , NULL --ETHNIC CODSYS
                 , NULL                                                        -- PREFIX_NM	
                 , NULL                                                        -- GIVEN_NM	
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                 , NULL                                                        -- SUFFIX_NM	
                 , NULL                                                        -- TITLE_NM	
                 , NULL                                                        -- MIDDLE_NM	
                 , race_code_v
                 , race_code_d
                 , NULL --RACE CODSYS
                 , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))  --ZIP_PLUS4
                );

         -- Set hospital code
         random_num := round(DBMS_RANDOM.VALUE (1, 10));

         CASE random_num
            WHEN '1' THEN
               hosploc_cd_d := 'VA SALT LAKE CITY HEALTH CARE SYSTEM';
               hosploc_cd_v := '660';
            WHEN '2' THEN
               hosploc_cd_d := 'TUSCALOOSA VA MEDICAL CENTER';
               hosploc_cd_v := '679';
            WHEN '3' THEN
               hosploc_cd_d := 'CENTRAL TEXAS VETERANS HEALTH CARE SYSTEM';
               hosploc_cd_v := '674';
            WHEN '4' THEN
               hosploc_cd_d := 'PHILADELPHIA VA MEDICAL CENTER';
               hosploc_cd_v := '642';
            WHEN '5' THEN
               hosploc_cd_d := 'CINCINNATI VA MEDICAL CENTER';
               hosploc_cd_v := '539';
            WHEN '6' THEN
               hosploc_cd_d := 'JAMES J. PETERS VA MEDICAL CENTER';
               hosploc_cd_v := '526';
            WHEN '7' THEN
               hosploc_cd_d := 'VA GULF COAST VETERANS HEALTH CARE SYSTEM';
               hosploc_cd_v := '520';
            WHEN '8' THEN
               hosploc_cd_d := 'RICHARD L. ROUDEBUSH VA MEDICAL CENTER';
               hosploc_cd_v := '583';
            WHEN '9' THEN
               hosploc_cd_d := 'MIAMI VA HEALTHCARE SYSTEM';
               hosploc_cd_v := '546';
            ELSE
               hosploc_cd_d := 'JONATHAN M. WAINWRIGHT MEMORIAL VA MEDICAL CENTER';
               hosploc_cd_v := '687';
         END CASE;

         -- get ATTENDER practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 3));

         IF random_num = 1
         THEN
             SELECT lab_pract_part_seq_id.NEXTVAL
               INTO attenderppid
               FROM DUAL;
             
             insert_practpart.LAB_INSERT_MESSAGE( specimen_date, attenderppid );
         ELSE
             attenderppid := NULL;
         END IF;

         -- get Patient seq id
         SELECT lab_patient_visit_seq_id.NEXTVAL
           INTO lab_patient_visit_seq_id_val
           FROM DUAL;

         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
                
         INSERT INTO LAB_PATIENT_VISIT
                     (
                          LAB_PATIENT_VISIT_ID
                        , ATTENDER_PPID
                        , ADMISSION_DT
                        , ADMISSION_DT_NBR
                        , ALT_VISIT_IDENTITY
                        , ALT_VISIT_ASSIGNING_AUTHORITY
                        , CHARGE_PRICE_IND
                        , PATIENT_VISIT
                        , PATIENT_CLASS
                        , PATIENT_TYP
                        , SERVICING_FACILITY
                        , HOSPITAL_SRV_SPCLTY_CODE
                        , HOSPITAL_SRV_SPCLTY_DSPLYTXT
                        , HOSPITAL_SRV_SPCLTY_CODSYS
                        , HOSPITAL_LOC_POINT_OF_CARE
                        , HOSPITAL_LOC_ROOM
                        , HOSPITAL_LOC_BED
                        , HOSPITAL_LOC_FCLTY_NMSPCID
                        , HOSPITAL_LOC_FCLTY_UNVSID
                        , HOSPITAL_LOC_UNVSIDTYP
                        , HOSPITAL_LOC_PERS_LOC_TYP
                        , HOSPITAL_LOC_DESCRIPTION
                    )
         VALUES
                (
                          lab_patient_visit_seq_id_val                                              -- LAB_PATIENT_VISIT_ID
                        , attenderppid                                                          -- ATTENDER_PPID
                        , ppv_date                                                              -- ADMISSION_DT
                        , to_number(ppv_date,'99999999999999')                                  -- ADMISSION_DT_NBR
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- ALT_VISIT_IDENTITY
                        , dbms_random.string ('U',trunc(dbms_random.value(1,100)))              -- ALT_VISIT_ASSIGNING_AUTHORITY
                        , 'F'                                                                   -- CHARGE_PRICE_IND
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- PATIENT_VISIT
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- PATIENT_CLASS
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- PATIENT_TYP
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- SERVICING_FACILITY
                        , dbms_random.string ('U',trunc(dbms_random.value(1,30)))               -- HOSPITAL_SRV_SPCLTY_CODE
                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))               -- HOSPITAL_SRV_SPCLTY_DSPLYTXT
                        , null                                                                  -- HOSPITAL_SRV_SPCLTY_CODSYS
                        , hosploc_cd_d                                                          -- HOSPITAL_LOC_POINT_OF_CARE
                        , null -- room
                        , null -- bed
                        , hosploc_cd_d                                                          -- nmspcid             
                        , null -- unvisd
                        , null -- unvsidtyp
                        , null -- pers_loc_typ
                        , hosploc_cd_d                                                          -- description          
                );
         
         INSERT INTO LAB_TEST_PRM
                     (    
                          LAB_TEST_PRM_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , LAB_PATIENT_INFO_ID
                        , LAB_PATIENT_VISIT_ID
                        , LAB_TEST_REQ_ID
                        , VERIFIER_PPID
                        , REQUEST_ID
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , COMPLETION_DT
                        , COMPLETION_DT_NBR
                        , EXT_LAB_ADDR_LINE1
                        , EXT_LAB_ADDR_CTY
                        , EXT_LAB_ADDR_POSTAL_CD
                        , EXT_LAB_ADDR_CNTRY
                        , EXT_LAB_ADDR_ST
                        , EXT_LAB_ID_CODE
                        , EXT_LAB_ID_DSPLYTXT
                        , EXT_LAB_ID_CODSYS
                        , LABPATIENT_IDENTITY
                        , LABPATIENT_ASSIGNING_FACILITY
                        , LABPATIENT_ASSIGNING_AUTHORITY
                        , LAB_TYPE
                        , SPECIMEN_RECEIVE_DT
                        , SPECIMEN_RECEIVE_DT_NBR
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                     )
              VALUES (  
                        lab_test_prm_seq_id_val                                                     -- ALLERGY_ID
                      , v_vpid                                                                      -- PATIENT_IDENTITY
                      , 'DNS   -605'                                                                -- PATIENT_ASSIGNING_FACILITY
                      , 'USVHA'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                      , patient_seq_id_val                                                          -- lab_patient_info_ID
                      , lab_patient_visit_seq_id_val
                      , lab_test_req_seq_id_val                                                     -- LAB_TEST_REQ_ID	
                      , verifyppid                                                                  -- VERIFIER_PPID	
                      , NULL                                                                        -- REQUEST_ID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE(1,99999999))||'.'||lab_test_prm_seq_id_val -- RECORDIDENTIFIER_IDENTITY	
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE(1,99999999))||'.'||lab_test_prm_seq_id_val -- RECORDIDENTIFIER_NAMESPACEID	
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE(1,99999999))||'.'||lab_test_prm_seq_id_val -- RECORDIDENTIFIER_UNIVERSALID	
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE(1,99999999))||'.'||lab_test_prm_seq_id_val -- RECORDIDENTIFIER_UNIVERSTYP	
                      , completion_date                                                             -- COMPLETION_DT	
                      , to_number(completion_date,'99999999999999')                                 -- COMPLETION_DT_NBR	
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                     -- EXT_LAB_ADDR_LINE1	
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                     -- EXT_LAB_ADDR_CTY
                      , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))-- EXT_LAB_ADDR_POSTAL_CD
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                     -- EXT_LAB_ADDR_CNTRY
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                     -- EXT_LAB_ADDR_ST
                      , ROUND(DBMS_RANDOM.VALUE (877977,1987432))                                   -- EXT_LAB_ID_CODE
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                     -- EXT_LAB_ID_DSPLYTXT
                      , NULL                                                                        -- EXT_LAB_ID_CODSYS
                      , ROUND(DBMS_RANDOM.VALUE (45879,45890))                                      -- LABPATIENT_IDENTITY
                      , ROUND(DBMS_RANDOM.VALUE (3258,3269))                                        -- LABPATIENT_ASSIGNING_FACILITY
                      , 'USVHA'                                                                     -- LABPATIENT_ASSIGNING_AUTHORITY
                      , dbms_random.string ('U',trunc(dbms_random.value(1,2)))                      -- LAB_TYPE
                      , specimen_date                                                               -- SPECIMEN_RECEIVE_DT	
                      , to_number(specimen_date,'99999999999999')                                   -- SPECIMEN_RECEIVE_DT_NBR
                      , 1                                                                           -- RECORD_VERSION	
                      , upd_date                                                                    -- RECORD_UPDATE_TM	
                      , to_number(upd_date,'99999999999999')                                        -- RECORD_UPDATE_TM_NBR
                     );

         -- load lab_test_req

         ordsite_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,50)));
         ordsite_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));

         reqinst_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,50)));

         random_num  := DBMS_RANDOM.VALUE (-10, -30);
         random_num2 := DBMS_RANDOM.VALUE (-1, -15);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss')
           INTO ppa_date,
                ord_date
           FROM DUAL;
        
         SELECT lab_pract_part_seq_id.NEXTVAL
           INTO authorppid
           FROM DUAL;
        
         insert_practpart.LAB_INSERT_MESSAGE( ppa_date, authorppid );
 
         -- Insert into Lab_Test_Req table
         INSERT INTO LAB_TEST_REQ (
                                      LAB_TEST_REQ_ID
                                    , AUTHOR_PPID
                                    , TEST_CODE
                                    , TEST_DSPLYTXT
                                    , TEST_CODSYS
                                    , REQ_LOC_POINT_OF_CARE
                                    , REQ_LOC_ROOM
                                    , REQ_LOC_BED
                                    , REQ_LOC_FCLTY_NMSPCID
                                    , REQ_LOC_FCLTY_UNVSID
                                    , REQ_LOC_FCLTY_UNVSIDTYP
                                    , REQ_LOC_PERS_LOC_TYP
                                    , REQ_LOC_DESCRIPTION
                                    , REQ_INST_NAME
                                    , REQ_INST_IDENTITY
                                    , REQ_INST_ASSIGNING_AUTHORITY
                                    , ORD_SITE_NAME
                                    , ORD_SITE_IDENTITY
                                    , ORD_SITE_ASSIGNING_AUTHORITY
                                    , ORDER_IDENTIFIER_IDENTITY
                                    , ORDER_IDENTIFIER_NAMESPACEID
                                    , ORDER_IDENTIFIER_UNIVERSALID
                                    , ORDER_IDENTIFIER_UNIVERSTYP
                                  )
                           VALUES (
                                      lab_test_req_seq_id_val                                                       -- LAB_TEST_REQ_ID	
                                    , authorppid                                                                    -- AUTHOR_PPID	
                                    , ROUND(DBMS_RANDOM.VALUE (69888,70000))                                        -- TEST_CODE
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- TEST_DSPLYTXT
                                    , NULL                                                                          -- TEST_CODSYS
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- REQ_LOC_POINT_OF_CARE
                                    , dbms_random.string ('X',trunc(dbms_random.value(3,4)))                        -- REQ_LOC_ROOM
                                    , ROUND(DBMS_RANDOM.VALUE (1,4))                                                -- REQ_LOC_BED
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- REQ_LOC_FCLTY_NMSPCID
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- REQ_LOC_FCLTY_UNVSID
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- REQ_LOC_FCLTY_UNVSIDTYP
                                    ,  ROUND(DBMS_RANDOM.VALUE (1,4))                                               -- REQ_LOC_PERS_LOC_TYP
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                       -- REQ_LOC_DESCRIPTION
                                    , reqinst_cd_d                                                                  -- REQ_INST_NAME	
                                    , reqinst_cd_v                                                                  -- REQ_INST_IDENTITY	
                                    , NULL                                                                          -- REQ_INST_ASSIGNING_AUTHORITY	
                                    , ordsite_cd_d                                                                  -- ORD_SITE_NAME	
                                    , ordsite_cd_v                                                                  -- ORD_SITE_IDENTITY	
                                    , NULL                                                                          -- ORD_SITE_ASSIGNING_AUTHORITY	
                                    , 'ORDER'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||lab_test_req_seq_id_val  -- ORDER_IDENTIFIER_IDENTITY	
                                    , 'ORDER'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||lab_test_req_seq_id_val  -- ORDER_IDENTIFIER_NAMESPACEID	
                                    , 'ORDER'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||lab_test_req_seq_id_val  -- ORDER_IDENTIFIER_UNIVERSALID	
                                    , 'ORDER'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||lab_test_req_seq_id_val  -- ORDER_IDENTIFIER_UNIVERSTYP
                                  );

         -- load 0 or many comments
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 4));
         IF random_num = 0
         THEN
            FOR cmnt IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP

               comment_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               comment_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));

               SELECT LAB_COMMENT_EVENT_seq_id.NEXTVAL
                 INTO lab_test_cmt_seq_id_val
                 FROM DUAL;
        
               -- Insert into lab_test_comment
               INSERT INTO LAB_COMMENT_EVENT (
                                               LAB_COMMENT_EVENT_ID	
                                             , LAB_TEST_PRM_ID	
                                             , CHEMISTRY_RESULT_ID
                                             , COMMENT_TYP_CODE
                                             , COMMENT_TYP_DSPLYTXT	
                                             , COMMENT_TYP_CODSYS	
                                             , COMMENT_TXT
                                            )
                                     VALUES (
                                               lab_test_cmt_seq_id_val                                               -- LAB_COMMENT_EVENT_ID	
                                             , lab_test_prm_seq_id_val                                               -- LAB_TEST_PRM_ID	
                                             , NULL                                                             --CHEMISTRY_RESULT_ID
                                             , comment_cd_v                                                          -- COMMENT_TYP_VALSET_VUID	
                                             , comment_cd_d                                                          -- COMMENT_TYP_DSPLYTXT	
                                             , null                                                    -- COMMENT_TYP_ALT_CODSYS	
                                             , dbms_random.string ('U',trunc(dbms_random.value(25,400))) --COMMENT_TXT	
                                            );         
            END LOOP;          
         END IF;

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

         IF random_num = 0
         THEN
            FOR coll IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP

               SELECT specimen_collection_seq_id.NEXTVAL
                 INTO specimen_coll_seq_id_val
                 FROM DUAL;

               -- Insert into Specimen_Collection
               INSERT INTO SPECIMEN_COLLECTION (
                                                  SPECIMEN_COLLECTION_ID	
                                                , LAB_TEST_PRM_ID	
                                                , SPECIMEN_COLLECTION_TM	
                                                , SPECIMEN_COLLECTION_TM_NBR
                                               )
                                        VALUES (
                                                  specimen_coll_seq_id_val                                     -- SPECIMEN_COLLECTION_ID	
                                                , lab_test_prm_seq_id_val                                      -- LAB_TEST_PRM_ID	
                                                , collection_date                                              -- SPECIMEN_COLLECTION_TM	
                                                , to_number(collection_date,'99999999999999')                  -- SPECIMEN_COLLECTION_TM_NBR
                                               );               

               -- Load 1 to many specimen                                
               FOR spec IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
               LOOP

                   snomed_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   snomed_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));

                   spectyp_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   spectyp_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               
                   SELECT specimen_seq_id.NEXTVAL
                     INTO specimen_seq_id_val
                     FROM DUAL;

                   -- Insert into specimen table
                   INSERT INTO SPECIMEN (
                                           SPECIMEN_ID	
                                         , SPECIMEN_COLLECTION_ID	
                                         , SNOMED_CODE	
                                         , SNOMED_DSPLYTXT	
                                         , SNOMED_CODSYS	
                                         , SPECIMEN_TYP_CODE	
                                         , SPECIMEN_TYP_DSPLYTXT	
                                         , SPECIMEN_TYP_CODSYS	
                                        )
                                 VALUES (
                                           specimen_seq_id_val                                                          -- SPECIMEN_ID	
                                         , specimen_coll_seq_id_val                                                     -- SPECIMEN_COLLECTION_ID	
                                         , snomed_cd_v                                                                  -- SNOMED_VALSET_VUID	
                                         , snomed_cd_d                                                                  -- SNOMED_DSPLYTXT	
                                         , NULL                                                            -- SNOMED_ALT_CODSYS	
                                         , spectyp_cd_v                                                                 -- SPECIMEN_TYP_VALSET_VUID	
                                         , spectyp_cd_d                                                                 -- SPECIMEN_TYP_DSPLYTXT	
                                         , NULL                                                           -- SPECIMEN_TYP_ALT_CODSYS		
                                        );                   
               END LOOP;
            END LOOP;          
         END IF;

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

         IF random_num = 0
         THEN
            FOR chem IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP
            
               -- Null alternate values
               alt_codsys_test   := NULL;
               alt_display_test  := NULL;
               alt_code_test     := NULL;
               
               method_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               method_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));
  
               value_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               value_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));

               -- Set test code
               random_num := round(DBMS_RANDOM.VALUE (1, 100));

               CASE random_num
                  WHEN '1' THEN
                     test_cd_d         := NULL;
                     test_cd_v         := NULL;
                     alt_codsys_test   := 'HL7';
                     alt_display_test  := 'UNKNOWN';
                     alt_code_test     := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  ELSE
                     test_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                     test_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               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;
  
               -- null value fields then determine value
               value_1_string   := NULL;
               
               value_1_string  := dbms_random.string ('U',trunc(dbms_random.value(30,200)));

               random_num := DBMS_RANDOM.VALUE (-2, -10);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss')
                 INTO value_date
                 FROM DUAL;
  
               SELECT chemistry_result_seq_id.NEXTVAL
                 INTO chemistry_rslt_seq_id_val
                 FROM DUAL;
               
               -- insert into chemistry_result
               INSERT INTO CHEMISTRY_RESULT
                           (   
                              CHEMISTRY_RESULT_ID
                            , LAB_TEST_PRM_ID
                            , CHEMRSLT_INTERPRETATION
                            , METHOD_CODE
                            , METHOD_DSPLYTXT
                            , METHOD_CODSYS
                            , OBSERVATION_TM
                            , OBSERVATION_TM_NBR
                            , CHEMRSLT_STATUS
                            , TEST_IDENTIFIER_CODE
                            , TEST_IDENTIFIER_DSPLYTXT
                            , TEST_IDENTIFIER_CODSYS
                            , TEST_IDENTIFIER_CODSYS_VER
                            , TEST_IDENTIFIER_ALT_CODE
                            , TEST_IDENTIFIER_ALT_DSPLYTXT
                            , TEST_IDENTIFIER_ALT_CODSYS
                            , TEST_IDENTIFIER_ALT_CODSYS_VER
                            , TEST_IDENTIFIER_ORIGINAL_TXT
                            , REFERENCE_RANGE
                            , DEVICE_NAME
                            , DEVICE_IDENTITY
                            , DEVICE_NAMESPACEID
                            , DEVICE_UNIVERSALID
                            , DEVICE_UNIVERSTYP
                            , CHEM_HEM_RSLT_VAL
                            , CHEM_HEM_RSLT_UOFM_CODE
                            , CHEM_HEM_RSLT_UOFM_DSPLYTXT
                            , CHEM_HEM_RSLT_UOFM_CODSYS
                           )
                    VALUES ( 
                              chemistry_rslt_seq_id_val                                                             -- CHEMISTRY_RESULT_ID	
                            , lab_test_prm_seq_id_val                                                               -- LAB_TEST_PRM_ID	
                            , dbms_random.string ('U',trunc(dbms_random.value(50,200)))                             -- CHEMRSLT_INTERPRETATION	
                            , method_cd_v                                                                           -- METHOD_TYP_VALSET_VUID	
                            , method_cd_d                                                                           -- METHOD_TYP_DSPLYTXT	
                            , null                                                                                  -- METHOD_TYP_ALT_CODSYS	
                            , value_date                                                                            -- OBSERVATION_TM	
                            , TO_NUMBER(value_date,'99999999999999')                                                -- OBSERVATION_TM_NBR	
                            , status_code_d                                                                         -- CHEMRSLT_STATUS	
                            , test_cd_v                                                                             -- TEST_IDENTIFIER_CODE
                            , test_cd_d                                                                             -- TEST_IDENTIFIER_CODE display
                            , null                                                                                  -- test codsys
                            , 1                                                                                     -- test cosys ver
                            , alt_code_test                                                                         -- alt test
                            , alt_display_test                                                                      -- alt test display
                            , alt_codsys_test                                                                       -- alt test codsys
                            , 1                                                                                     -- alt test codsys ver
                            , dbms_random.string ('U',trunc(dbms_random.value(100,1000)))                           -- TEST_IDENTIFIER_ORIGINAL_TXT	
                            , ROUND(DBMS_RANDOM.VALUE(50,500))                                                      -- REFERENCE_RANGE	
                            , dbms_random.string ('U',trunc(dbms_random.value(10,100)))                             -- DEVICE_NAME	
                            , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICE_IDENTITY	
                            , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICE_NAMESPACEID	
                            , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICE_UNIVERSALID	
                            , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICE_UNIVERSTYP	
                            , value_1_string                                                                        -- CHEM_HEM_RSLT_VAL	
                            , value_cd_v                                                                            -- CHEM_HEM_RSLT_UOFM_CODE	
                            , value_cd_d                                                                            -- CHEM_HEM_RSLT_UOFM_DSPLYTXT	
                            , null                                                                                  -- CHEM_HEM_RSLT_UOFM_CODSYS
                           );

                    -- load 0 or many comments
                    random_num := ROUND (DBMS_RANDOM.VALUE (0, 4));
                    IF random_num = 0
                    THEN
                       FOR cmnt IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
                       LOOP

                          comment_cd_d         := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                          comment_cd_v         := ROUND(DBMS_RANDOM.VALUE (1,99999999));

                          SELECT LAB_COMMENT_EVENT_seq_id.NEXTVAL
                            INTO lab_test_cmt_seq_id_val
                            FROM DUAL;

                          -- Insert into lab_test_comment
                          INSERT INTO LAB_COMMENT_EVENT (
                                                          LAB_COMMENT_EVENT_ID	
                                                        , LAB_TEST_PRM_ID	
                                                        , CHEMISTRY_RESULT_ID
                                                        , COMMENT_TYP_CODE
                                                        , COMMENT_TYP_DSPLYTXT	
                                                        , COMMENT_TYP_CODSYS	
                                                        , COMMENT_TXT
                                                       )
                                                VALUES (
                                                          lab_test_cmt_seq_id_val                                   -- LAB_COMMENT_EVENT_ID	
                                                        , NULL                                                      -- LAB_TEST_PRM_ID	
                                                        , chemistry_rslt_seq_id_val                                 -- CHEMISTRY_RESULT_ID
                                                        , comment_cd_v                                              -- COMMENT_TYP_VALSET_VUID	
                                                        , comment_cd_d                                              -- COMMENT_TYP_DSPLYTXT	
                                                        , null                                                      -- COMMENT_TYP_ALT_CODSYS	
                                                        , dbms_random.string ('U',trunc(dbms_random.value(25,400))) -- COMMENT_TXT
                                                       );         
                       END LOOP;          
                    END IF;

            END LOOP;          
         END IF;

       END LOOP;
       
       COMMIT;
   END LOOP;

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