/******************************************************************************
   NAME:       load_lab_load4x_arms.sql
   PURPOSE:    This script will create lab records that map to 
               VHIM version 4  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        09/18/2008  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        02/26/2009  M. Jacobsen      move procedure_code from lab_test_prm
                                           to lab_test
******************************************************************************/
set serveroutput on
DECLARE
   prev_num                     NUMBER          := 0;
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_num3                  NUMBER          := 0;
   random_num4                  NUMBER          := 0;
   upd_date                     VARCHAR2 (30);
   completion_date              VARCHAR2 (30);
   specimen_date                VARCHAR2 (30);
   specimen_date2               VARCHAR2 (30);
   admin_date                   VARCHAR2 (30);
   collection_date              VARCHAR2 (30);
   ord_date                     VARCHAR2 (30);
   value_date                   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;
   CHEMRSLT_cmt_seq_id_val      NUMBER;
   LAB_OBSMETH_seq_id_val       NUMBER;
   lab_patient_seq_id_val       NUMBER;
   lce_linenbr                  number;
   lt_linenbr                   number;
   cr_linenbr                   number;
   cce_linenbr                  number;
   lom_linenbr                  number;
   ordsite_cd_d                 VARCHAR2 (100);
   ordsite_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);
   value_cd_d                   VARCHAR2 (100);
   value_cd_v                   VARCHAR2 (20);
   value_1_string               VARCHAR2 (200);
   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-08 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_num4 := DBMS_RANDOM.VALUE (-3, -18);

         SELECT TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num4, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date, 'yyyymmddhh24miss')
           INTO completion_date,
                specimen_date,
                specimen_date2,
                admin_date,
                upd_date
           FROM DUAL;

         SELECT lab_test_prm_seq_id.NEXTVAL
           INTO lab_test_prm_seq_id_val
           FROM DUAL;
                       
         -- 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;

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

         select lab_patient_visit_seq_id.nextval into lab_patient_seq_id_val from dual;

         INSERT INTO lab_patient_visit_v (
                            LAB_PATIENT_VISIT_ID
                          , PATIENT_CLASS
                          , ASSIGN_LOC_POINT_OF_CARE
                          , ASSIGN_LOC_ROOM
                          , ASSIGN_LOC_BED
                          , ASSIGN_LOC_FCLTY_NMSPCID
                          , ASSIGN_LOC_FCLTY_UNVSID
                          , ASSIGN_LOC_FCLTY_UNVSIDTYP
                          , ASSIGN_LOC_PERS_LOC_TYP
                          , ASSIGN_LOC_DESCRIPTION
                          , PRIOR_LOC_POINT_OF_CARE
                          , PRIOR_LOC_ROOM
                          , PRIOR_LOC_BED
                          , PRIOR_LOC_FCLTY_NMSPCID
                          , PRIOR_LOC_FCLTY_UNVSID
                          , PRIOR_LOC_FCLTY_UNVSIDTYP
                          , PRIOR_LOC_PERS_LOC_TYP
                          , PRIOR_LOC_DESCRIPTION
                          , AUTH_PERSON_IDENTITY
                          , AUTH_PERSON_ASGNING_FACILITY
                          , AUTH_PERSON_ASGNING_AUTHORITY
                          , AUTH_PRACTITIONER_ID_SRC_TBL
                          , AUTH_NAME_PREFIX
                          , AUTH_NAME_GIVEN
                          , AUTH_NAME_FAMILY
                          , AUTH_NAME_SUFFIX
                          , AUTH_NAME_TITLE
                          , AUTH_NAME_MIDDLE
                          , HOSPITAL_SERVICE
                          , PATIENT_TYP
                          , CHARGE_PRICE_IND
                          , DISCHARGE_DISPOSITION
                          , SERVICE_FACILITY
                          , ADMISSION_DT
                          , ADMISSION_DT_NBR
                          , ALTERNATE_VISIT_ID 
                )
         VALUES (
                   lab_patient_seq_id_val                                             -- LAB_PATIENT_VISIT_ID
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PATIENT_CLASS
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_POINT_OF_CARE
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_ROOM
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_BED
                 , hosploc_cd_d                                                                 -- ASSIGN_LOC_FCLTY_NMSPCID
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_FCLTY_UNVSID
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_FCLTY_UNVSIDTYP
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_PERS_LOC_TYP
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ASSIGN_LOC_DESCRIPTION
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_POINT_OF_CARE
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_ROOM
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_BED
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_FCLTY_NMSPCID
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_FCLTY_UNVSID
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_FCLTY_UNVSIDTYP
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_PERS_LOC_TYP
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PRIOR_LOC_DESCRIPTION
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- AUTH_PERSON_IDENTITY
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- AUTH_PERSON_ASGNING_FACILITY
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- AUTH_PERSON_ASGNING_AUTHORITY
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- AUTH_PRACTITIONER_ID_SRC_TBL
                 , NULL                                                                         -- AUTH_NAME_PREFIX
                 , NULL                                                                         -- AUTH_NAME_GIVEN
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- AUTH_NAME_FAMILY
                 , NULL                                                                         -- AUTH_NAME_SUFFIX
                 , NULL                                                                         -- AUTH_NAME_TITLE
                 , NULL                                                                         -- AUTH_NAME_MIDDLE
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- HOSPITAL_SERVICE
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- PATIENT_TYP
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- CHARGE_PRICE_IND
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- DISCHARGE_DISPOSITION
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- SERVICE_FACILITY
                 , admin_date                                                                   -- ADMISSION_DT
                 , (to_number(admin_date,'99999999999999')-17000000000000)/1000000                                       -- ADMISSION_DT_NBR
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))                      -- ALTERNATE_VISIT_ID 
         );


         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
        
         INSERT INTO LAB_TEST_PRM_V
                     (    
                          LAB_TEST_PRM_ID
                        , LAB_PATIENT_VISIT_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , REQUEST_ID
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , PATIENT_NAME_PREFIX
                        , PATIENT_NAME_GIVEN
                        , PATIENT_NAME_FAMILY
                        , PATIENT_NAME_SUFFIX
                        , PATIENT_NAME_TITLE
                        , PATIENT_NAME_MIDDLE
                        , PLACER_ACCESSION_REF
                        , FILLER_IEN_REF
                        , REPORT_COMPLETE_DT
                        , REPORT_COMPLETE_DT_NBR
                        , LAB_SUBSCRIPT
                        , LAB_PRIORITY
                        , LAB_STATUS
                        , SPECIMEN_SRC_CODE
                        , SPECIMEN_SRC_DSPLYTXT
                        , SPECIMEN_SRC_CODSYS
                        , SPECIMEN_SRC_CODSYS_VER
                        , SPECIMEN_SRC_ALT_CODE
                        , SPECIMEN_SRC_ALT_DSPLYTXT
                        , SPECIMEN_SRC_ALT_CODSYS
                        , SPECIMEN_SRC_ALT_CODSYS_VER
                        , SPECIMEN_SRC_ORIG_TXT
                        , SPECIMEN_TAKEN_DT
                        , SPECIMEN_TAKEN_DT_NBR
                        , SPECIMEN_RECEIVED_DT
                        , SPECIMEN_RECEIVED_DT_NBR
                        , SPECIMEN_ACTION
                        , AUTH_PERSON_IDENTITY
                        , AUTH_PERSON_ASGNING_FACILITY
                        , AUTH_PERSON_ASGNING_AUTHORITY
                        , AUTH_PRACTITIONER_ID_SRC_TBL
                        , AUTH_NAME_PREFIX
                        , AUTH_NAME_GIVEN
                        , AUTH_NAME_FAMILY
                        , AUTH_NAME_SUFFIX
                        , AUTH_NAME_TITLE
                        , AUTH_NAME_MIDDLE
                        , REQUEST_LOC_POINT_OF_CARE
                        , REQUEST_LOC_ROOM
                        , REQUEST_LOC_BED
                        , REQUEST_LOC_FCLTY_NMSPCID
                        , REQUEST_LOC_FCLTY_UNVSID
                        , REQUEST_LOC_FCLTY_UNVSIDTYP
                        , REQUEST_LOC_PERS_LOC_TYP
                        , REQUEST_LOC_DESCRIPTION
                        , REQUEST_ORG_CODE
                        , REQUEST_ORG_DSPLYTXT
                        , REQUEST_ORG_CODSYS
                        , ORD_FCLTY_IDENTITY
                        , ORD_FCLTY_NAME
                        , ORD_FCLTY_ASSIGNING_AUTHORITY
                        , ORD_FCLTY_NAME_TYP_CODE
                        , ORD_FCLTY_IDENTITY_TYP_CODE
                        , ORD_FCLTY_NAME_REP
                        , ORD_FCLTY_STATION_NUMBER
                        , ORD_FCLTY_ADDR_LINE1
                        , ORD_FCLTY_ADDR_LINE2
                        , ORD_FCLTY_ADDR_CITY
                        , ORD_FCLTY_ADDR_STATE
                        , ORD_FCLTY_ADDR_POSTAL_CODE
                        , ORD_FCLTY_ADDR_COUNTRY
                        , RECORDSOURCE_NAMESPACEID
                        , RECORDSOURCE_UNIVERSALID
                        , RECORDSOURCE_UNIVERSTYP
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                     )
              VALUES (  
                        lab_test_prm_seq_id_val                                                     -- LAB_TEST_PRM_ID
                      , lab_patient_seq_id_val                                            -- LAB_PATIENT_VISIT_ID
                      , v_vpid                                                                      -- PATIENT_IDENTITY
                      , '605'                                                                -- PATIENT_ASSIGNING_FACILITY
                      , 'USVHA'                                                                     -- PATIENT_ASSIGNING_AUTHORITY
                      , 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	
                      , 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	
                      , dbms_random.string ('U',trunc(dbms_random.value(1,250)))    -- PLACER_ACCESSION_REF
                      , dbms_random.string ('U',trunc(dbms_random.value(1,150)))    -- FILLER_IEN_REF
                      , completion_date                                             -- REPORT_COMPLETE_DT	
                      , (to_number(completion_date,'99999999999999')-17000000000000)/1000000                 -- REPORT_COMPLETE_DT_NBR	
                      , 'CH'                                                          -- LAB_SUBSCRIPT
--                      , ROUND(DBMS_RANDOM.VALUE (59874,98422))                      -- PROCEDURE_CODE
--                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- PROCEDURE_DSPLYTXT
--                      , NULL                                                        -- PROCEDURE_CODSYS
--                      , NULL                                                        -- PROCEDURE_ALT_CODE
--                      , NULL                                                        -- PROCEDURE_ALT_DSPLYTXT
--                      , NULL                                                        -- PROCEDURE_ALT_CODSYS
                      , NULL                                                        -- LAB_PRIORITY
                      , dbms_random.string ('U',trunc(dbms_random.value(1,100)))    -- LAB_STATUS
                      , ROUND(DBMS_RANDOM.VALUE (78945,89899))                      -- SPECIMEN_SRC_CODE
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- SPECIMEN_SRC_DSPLYTXT
                      , NULL                                                        -- SPECIMEN_SRC_CODSYS
                      , '1'                                                         -- SPECIMEN_SRC_CODSYS_VER
                      , NULL                                                        -- SPECIMEN_SRC_ALT_CODE
                      , NULL                                                        -- SPECIMEN_SRC_ALT_DSPLYTXT
                      , NULL                                                        -- SPECIMEN_SRC_ALT_CODSYS
                      , NULL                                                        -- SPECIMEN_SRC_ALT_CODSYS_VER
                      , dbms_random.string ('U',trunc(dbms_random.value(1,100)))    -- SPECIMEN_SRC_ORIG_TXT
                      , specimen_date2                                              -- SPECIMEN_TAKEN_DT
                      , (to_number(specimen_date2,'99999999999999')-17000000000000)/1000000                  -- SPECIMEN_TAKEN_DT_NBR
                      , specimen_date                                               -- SPECIMEN_RECEIVED_DT
                      , (to_number(specimen_date,'99999999999999')-17000000000000)/1000000                   -- SPECIMEN_RECEIVED_DT_NBR
                      , NULL                                                        -- SPECIMEN_ACTION
                      , 'test_id_' || to_char(round(dbms_random.value(1,1000)))     -- AUTH_PERSON_IDENTITY 
                      , 'visn ' || to_char(round(dbms_random.value(1,21)))          -- AUTH_PERSON_ASGNING_FACILITY
                      , null                                                        -- AUTH_PERSON_ASGNING_AUTHORITY
                      , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) ) -- AUTH_practitioner_id_src_tbl
                      , NULL                                                        -- AUTH_NAME_PREFIX	
                      , NULL                                                        -- AUTH_NAME_GIVEN		
                      , dbms_random.string ('U',trunc(dbms_random.value(1,40)))     -- AUTH_NAME_FAMILY	
                      , NULL                                                        -- AUTH_NAME_SUFFIX	
                      , NULL                                                        -- AUTH_NAME_TITLE	
                      , NULL                                                        -- AUTH_NAME_MIDDLE	
                      , 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
                      , ROUND(DBMS_RANDOM.VALUE (1598,5898))                        -- REQUEST_ORG_CODE
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- REQUEST_ORG_DSPLYTXT
                      , null                                                        -- REQUEST_ORG_CODSYS
                      , ordsite_cd_v                                                -- ORD_FCLTY_IDENTITY	
                      , ordsite_cd_d                                                -- ORD_FCLTY_NAME
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ASSIGNING_AUTHORITY	
                      , NULL                                                        -- ORD_FCLTY_NAME_TYP_CODE
                      , NULL                                                        -- ORD_FCLTY_IDENTITY_TYP_CODE
                      , NULL                                                        -- ORD_FCLTY_NAME_REP
                      , ROUND(DBMS_RANDOM.VALUE (200,900))                          -- ORD_FCLTY_STATION_NUMBER
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ADDR_LINE1    
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ADDR_LINE2
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ADDR_CITY
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ADDR_STATE
                      , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))-- ORD_FCLTY_ADDR_POSTAL_CODE
                      , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- ORD_FCLTY_ADDR_COUNTRY
                      ,'605'
                      ,NULL
                      ,NULL
                      , 1                                                           -- RECORD_VERSION	
                      , upd_date                                                    -- RECORD_UPDATE_TM	
                      , (to_number(upd_date,'99999999999999')-17000000000000)/1000000                        -- RECORD_UPDATE_TM_NBR
                     );
 
         -- load 0 or many comments
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
         IF random_num > 1
         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_V (
                                                  LAB_COMMENT_EVENT_ID
                                                , LAB_TEST_PRM_ID
                                                , COMMENT_TYP_CODE
                                                , COMMENT_TYP_DSPLYTXT
                                                , COMMENT_TYP_CODSYS
                                                , LINE_NBR
                                            )
                                     VALUES (
                                               lab_test_cmt_seq_id_val                                      -- LAB_COMMENT_EVENT_ID	
                                             , lab_test_prm_seq_id_val                                      -- LAB_TEST_PRM_ID	
                                             , comment_cd_v                                                 -- COMMENT_TYP_CODE	
                                             , comment_cd_d                                                 -- COMMENT_TYP_DSPLYTXT	
                                             , null                                                         -- COMMENT_TYP_CODSYS	
                                             , cmnt-1
                                            );

               -- LOOP TO ENTER TEXT
               FOR txt IN 1 .. ROUND (dbms_random.value(1,3))
               LOOP
                    INSERT INTO LAB_COMMENT_V (
                                                  LAB_COMMENT_EVENT_ID
                                                , LINE_NBR
                                                , COMMENT_TXT
                                               )
                                       VALUES (
                                                  LAB_COMMENT_EVENT_SEQ_ID.CURRVAL                          -- lab_comment_event_id
                                                , txt-1                                                       -- line_nbr
                                                , dbms_random.string ('U',trunc(dbms_random.value(25,400))) -- COMMENT_TXT
                                              );	
               END LOOP;
            END LOOP;          
         END IF;

         -- load 0 to many lab_test
         random_num := ROUND (dbms_random.value(1,4));
         IF random_num > 1
         THEN
           FOR labtst IN 1 .. ROUND (dbms_random.value (1,2))
           LOOP
            
             INSERT INTO LAB_TEST_V (
                                       LAB_TEST_ID
                                     , LAB_TEST_PRM_ID
                                     , ORDERED_TEST_CODE
                                     , ORDERED_TEST_DSPLYTXT
                                     , ORDERED_TEST_CODSYS
                                     , ORDERED_TEST_ALT_CODE
                                     , ORDERED_TEST_ALT_DSPLYTXT
                                     , ORDERED_TEST_ALT_CODSYS
                                     , FILLER_ACCESSION_REF    
                                     , PROCEDURE_CODE
                                     , PROCEDURE_DSPLYTXT
                                     , PROCEDURE_CODSYS
                                     , PROCEDURE_ALT_CODE
                                     , PROCEDURE_ALT_DSPLYTXT
                                     , PROCEDURE_ALT_CODSYS
                                     , LINE_NBR
                                    )
                             VALUES (
                                       LAB_TEST_SEQ_ID.NEXTVAL                                  -- LAB_TEST_ID
                                     , lab_test_prm_seq_id_val                                  -- LAB_TEST_PRM_ID
                                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))  -- ORDERED_TEST_CODE
                                     , dbms_random.string ('U',trunc(dbms_random.value(8,30)))  -- ORDERED_TEST_DSPLYTXT
                                     , NULL                                                     -- ORDERED_TEST_CODSYS
                                     , NULL                                                     -- ORDERED_TEST_ALT_CODE
                                     , NULL                                                     -- ORDERED_TEST_ALT_DSPLYTXT
                                     , NULL                                                     -- ORDERED_TEST_ALT_CODSYS
                                     , NULL                                                     -- FILLER_ACCESSION_REF    
                                     , ROUND(DBMS_RANDOM.VALUE (59874,98422))                      -- PROCEDURE_CODE
                                     , dbms_random.string ('U',trunc(dbms_random.value(1,50)))     -- PROCEDURE_DSPLYTXT
                                     , NULL                                                        -- PROCEDURE_CODSYS
                                     , NULL                                                        -- PROCEDURE_ALT_CODE
                                     , NULL                                                        -- PROCEDURE_ALT_DSPLYTXT
                                     , NULL                                                        -- PROCEDURE_ALT_CODSYS
                                     , labtst-1
                                    );
                                    
             -- load 0 or many chemistry_result
             random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));

             IF random_num > 1
             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;
                   
                   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;

                   -- 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, -20);
                   random_num2 := DBMS_RANDOM.VALUE (-2, -20);

                   SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                          TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss')
                     INTO value_date,
                          ord_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_V
                               (   
                                  CHEMISTRY_RESULT_ID
                                , LAB_TEST_ID
                                , VALUE_INTERPRETATION
                                , OBSERVATION_DT
                                , OBSERVATION_DT_NBR
                                , OBSERVATION_STATUS
                                , TEST_ID_CODE
                                , TEST_ID_DSPLYTXT
                                , TEST_ID_CODSYS
                                , TEST_ID_CODSYS_VER
                                , TEST_ID_ALT_CODE
                                , TEST_ID_ALT_DSPLYTXT
                                , TEST_ID_ALT_CODSYS
                                , TEST_ID_ALT_CODSYS_VER
                                , TEST_ID_ORIG_TXT
                                , REFERENCE_RANGE
                                , OBSERVATION_VALUE
                                , OBSERVATION_VALUE_TYP
                                , OBSERVATION_UNIT_CODE
                                , OBSERVATION_UNIT_DSPLYTXT
                                , OBSERVATION_UNIT_CODSYS
                                , DEVICEIDENTIFIER_IDENTITY
                                , DEVICEIDENTIFIER_NAMESPACEID
                                , DEVICEIDENTIFIER_UNIVERSALID
                                , DEVICEIDENTIFIER_UNIVERSTYP
                                , PRODUCER_CODE
                                , PRODUCER_DSPLYTXT
                                , PRODUCER_CODSYS
                                , PRODUCER_ALT_CODE
                                , PRODUCER_ALT_DSPLYTXT
                                , PRODUCER_ALT_CODSYS
                                , VRFY_IDENTITY
                                , VRFY_ASSIGNING_FACILITY
                                , VRFY_ASSIGNING_AUTHORITY
                                , VRFY_PRACTITIONER_ID_SRC_TBL
                                , VRFY_NAME_PREFIX
                                , VRFY_NAME_GIVEN
                                , VRFY_NAME_FAMILY
                                , VRFY_NAME_SUFFIX
                                , VRFY_NAME_TITLE
                                , VRFY_NAME_MIDDLE
                                , ANALYSIS_DT
                                , ANALYSIS_DT_NBR
                                , PERF_ORG_IDENTITY
                                , PERF_ORG_NAME
                                , PERF_ORG_ASSIGNING_AUTHORITY
                                , PERF_ORG_NAME_TYP_CODE
                                , PERF_ORG_IDENTITY_TYP_CODE
                                , PERF_ORG_NAME_REP
                                , PERF_ORG_STATION_NUMBER
                                , PERF_ORG_ADDR_LINE1
                                , PERF_ORG_ADDR_LINE2
                                , PERF_ORG_ADDR_CITY
                                , PERF_ORG_ADDR_STATE
                                , PERF_ORG_ADDR_POSTAL_CODE
                                , PERF_ORG_ADDR_COUNTRY
                                , PERF_ORG_MED_DIR_PREFIX
                                , PERF_ORG_MED_DIR_GIVEN
                                , PERF_ORG_MED_DIR_MIDDLE
                                , PERF_ORG_MED_DIR_FAMILY
                                , PERF_ORG_MED_DIR_SUFFIX
                                , PERF_ORG_MED_DIR_TITLE
                                , LINE_NBR
                               )
                        VALUES ( 
                                  chemistry_rslt_seq_id_val                                                             -- CHEMISTRY_RESULT_ID	
                                , lab_test_seq_id.currval                                                               -- LAB_TEST_ID	
                                , dbms_random.string ('U',trunc(dbms_random.value(50,200)))                             -- VALUE_INTERPRETATION	
                                , value_date                                                                            -- OBSERVATION_DT	
                                , (TO_NUMBER(value_date,'99999999999999')-17000000000000)/1000000                                                -- OBSERVATION_DT_NBR	
                                , 'OPEN'                                                                                -- OBSERVATION_STATUS
                                , test_cd_v                                                                             -- TEST_ID_CODE
                                , test_cd_d                                                                             -- TEST_ID_DSPLYTXT
                                , null                                                                                  -- TEST_ID_CODSYS
                                , 1                                                                                     -- TEST_ID_CODSYS_VER
                                , alt_code_test                                                                         -- TEST_ID_ALT_CODE
                                , alt_display_test                                                                      -- TEST_ID_ALT_DSPLYTXT
                                , alt_codsys_test                                                                       -- TEST_ID_ALT_CODSYS
                                , 1                                                                                     -- TEST_ID_ALT_CODSYS_VER
                                , dbms_random.string ('U',trunc(dbms_random.value(50,200)))                             -- TEST_ID_ORIG_TXT
                                , ROUND(DBMS_RANDOM.VALUE(50,500))                                                      -- REFERENCE_RANGE	
                                , value_1_string                                                                        -- OBSERVATION_VALUE
                                , null                                                                                  -- OBSERVATION_VALUE_TYP
                                , value_cd_v                                                                            -- OBSERVATION_UNIT_CODE
                                , value_cd_d                                                                            -- OBSERVATION_UNIT_DSPLYTXT
                                , null                                                                                  -- OBSERVATION_UNIT_CODSYS
                                , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICEIDENTIFIER_IDENTITY	
                                , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICEIDENTIFIER_NAMESPACEID	
                                , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICEIDENTIFIER_UNIVERSALID	
                                , 'DEVICE'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||chemistry_rslt_seq_id_val       -- DEVICEIDENTIFIER_UNIVERSTYP	
                                , ROUND(DBMS_RANDOM.VALUE (1,99999999))                                                 -- PRODUCER_CODE
                                , dbms_random.string ('U',trunc(dbms_random.value(30,200)))                             -- PRODUCER_DSPLYTXT
                                , null                                                                                  -- PRODUCER_CODSYS
                                , null                                                                                  -- PRODUCER_ALT_CODE
                                , null                                                                                  -- PRODUCER_ALT_DSPLYTXT
                                , null                                                                                  -- PRODUCER_ALT_CODSYS
                                , 'test_id_' || to_char(round(dbms_random.value(1,1000)))                               -- VRFY_IDENTITY
                                , 'visn ' || to_char(round(dbms_random.value(1,21)))                                    -- VRFY_ASSIGNING_FACILITY
                                , null                                                                                  -- VRFY_ASSIGNING_AUTHORITY
                                , NULL                                                                                  -- VRFY_PRACTITIONER_ID_SRC_TBL
                                , NULL                                                                                  -- VRFY_NAME_PREFIX
                                , DBMS_RANDOM.STRING ('U',TRUNC(DBMS_RANDOM.VALUE(20,30)))                              -- VRFY_NAME_GIVEN
                                , NULL                                                                                  -- VRFY_NAME_FAMILY
                                , NULL                                                                                  -- VRFY_NAME_SUFFIX
                                , NULL                                                                                  -- VRFY_NAME_TITLE
                                , NULL                                                                                  -- VRFY_NAME_MIDDLE
                                , ord_date                                                                              -- ANALYSIS_DT
                                , (TO_NUMBER(ord_date,'99999999999999')-17000000000000)/1000000                                                  -- ANALYSIS_DT_NBR
                                , 'performing_org_id_' || to_char(round(dbms_random.value(100,9999)))                   -- PERF_ORG_IDENTITY
                                , 'PERF_ORG_NAME'                                                                       -- PERF_ORG_NAME
                                , null                                                                                  -- PERF_ORG_ASSIGNING_AUTHORITY
                                , null                                                                                  -- PERF_ORG_NAME_TYP_CODE
                                , null                                                                                  -- PERF_ORG_IDENTITY_TYP_CODE
                                , 'CDS'                                                                                 -- PERF_ORG_NAME_REP
                                , '200HDRII'                                                                            -- PERF_ORG_STATION_NUMBER                                 , '550 E FOOTHILL BLVD   SUITE 400'                                                     -- PERF_ORG_ADDR_LINE1
                                , '550 E FOOTHILL BLVD'                                                                 -- PERF_ORG_ADDR_LINE1
                                , 'SUITE 400'                                                                           -- PERF_ORG_ADDR_LINE2
                                , 'SLC'                                                                                 -- PERF_ORG_ADDR_CITY
                                , 'UTAH'                                                                                -- PERF_ORG_ADDR_STATE
                                , '84123'                                                                               -- PERF_ORG_ADDR_POSTAL_CODE
                                , 'USA'                                                                                 -- PERF_ORG_ADDR_COUNTRY
                                , 'SIR'                                                                                 -- PERF_ORG_MED_DIR_PREFIX
                                , 'STEVEN'                                                                              -- PERF_ORG_MED_DIR_GIVEN
                                , 'MR IMS'                                                                              -- PERF_ORG_MED_DIR_MIDDLE
                                , 'BRUSH'                                                                               -- PERF_ORG_MED_DIR_FAMILY
                                , null                                                                                  -- PERF_ORG_MED_DIR_SUFFIX
                                , 'JAVA GURU EXTRAORDINAIRE'                                                            -- PERF_ORG_MED_DIR_TITLE
                                , chem-1
                               );

                        -- load 0 or many comments
                        random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
                        IF random_num > 1
                        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 CHEMRSLT_CMNT_EVENT_SEQ_ID.NEXTVAL
                                INTO CHEMRSLT_cmt_seq_id_val
                                FROM DUAL;

                              -- Insert into lab_test_comment
                              INSERT INTO CHEMRSLT_COMMENT_EVENT_V (
                                                                 CHEMRSLT_COMMENT_EVENT_ID
                                                               , CHEMISTRY_RESULT_ID
                                                               , COMMENT_TYP_CODE
                                                               , COMMENT_TYP_DSPLYTXT
                                                               , COMMENT_TYP_CODSYS
                                                               , LINE_NBR
                                                           )
                                                    VALUES (
                                                              CHEMRSLT_cmt_seq_id_val                                   -- CHEMRSLT_COMMENT_EVENT_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	
                                                            , cmnt-1
                                                           );         
                               -- LOOP TO ENTER TEXT
                               FOR ltxt IN 1 .. ROUND (dbms_random.value(1,3))
                               LOOP
                                    INSERT INTO CHEMRSLT_COMMENT_V (
                                                                  CHEMRSLT_COMMENT_EVENT_ID
                                                                , LINE_NBR
                                                                , COMMENT_TXT
                                                               )
                                                       VALUES (
                                                                  CHEMRSLT_cmt_seq_id_val                                   -- CHEMRSLT_COMMENT_EVENT_ID
                                                                , ltxt-1                                                      -- line_nbr
                                                                , dbms_random.string ('U',trunc(dbms_random.value(25,400))) -- COMMENT_TXT
                                                              );	
                               END LOOP;
                           END LOOP;          
                        END IF;

                        -- load 0 or many LAB_OBSERVATION_METHOD
                        random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
                        IF random_num > 1
                        THEN
                           FOR lobm 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_OBSERVATION_METHOD_SEQ_ID.NEXTVAL
                                INTO LAB_OBSMETH_seq_id_val
                                FROM DUAL;

                              -- Insert into lab_test_comment
                              INSERT INTO LAB_OBSERVATION_METHOD_V (
                                                                      LAB_OBSERVATION_METHOD_ID
                                                                    , CHEMISTRY_RESULT_ID
                                                                    , OBSERVATION_MTHD_CODE
                                                                    , OBSERVATION_MTHD_DSPLYTXT
                                                                    , OBSERVATION_MTHD_CODSYS
                                                                    , OBSERVATION_MTHD_ALT_CODE
                                                                    , OBSERVATION_MTHD_ALT_DSPLYTXT
                                                                    , OBSERVATION_MTHD_ALT_CODSYS
                                                                    , LINE_NBR
                                                           )
                                                    VALUES (
                                                              LAB_OBSMETH_seq_id_val                                        -- LAB_OBSERVATION_METHOD_ID	
                                                            , chemistry_rslt_seq_id_val                                     -- CHEMISTRY_RESULT_ID	
                                                            , comment_cd_v                                                  -- OBSERVATION_MTHD_CODE	
                                                            , comment_cd_d                                                  -- OBSERVATION_MTHD_DSPLYTXT
                                                            , NULL                                                          -- OBSERVATION_MTHD_CODSYS
                                                            , null                                                          -- OBSERVATION_MTHD_ALT_CODE
                                                            , null                                                          -- OBSERVATION_MTHD_ALT_DSPLYTXT	
                                                            , NULL                                                          -- OBSERVATION_MTHD_ALT_CODSYS	
                                                            , lobm-1
                                                           );         
                           END LOOP;          
                        END IF;

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