/******************************************************************************
   NAME:       new_allergies_load4x_arms.sql
   PURPOSE:    This script will create Allergies records that map to 
               VHIM version 4 
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        10/19/2007  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        10/30/2007  M. Talbot        - added hardcoding of PATIENT_ASSIGNING_FACILITY to 'DNS   -605'
   1.3        01/08/2008  M. Jacobsen      - remove common schema
   1.4        01/25/2008  M. Jacobsen      - collapse Patient Info into Allergy and Allergy Assessment.  Collapse 0..1 pract_part
                                             relationships into parent table
   1.5        06/17/2008  M. Jacobsen      - change dates in number format to YYYMMDD.hhmmss
   1.6        09/29/2010  M. Jacobsen      - change assigning facilty/authority to 200CH/USDOD
   1.7        10/14/2010  M. Jacobsen      - for some reason the trigger to load the sequence for comment_event is not working
   1.8        11/16/2012  M. Jacobsen      - comment out allergy_assessment insert
   1.9        03/01/2013  M. Jacobsen      - v3.4p changes; load VistA and CHDR; populate gmr_allergy_codsys with 99VHA_ERT
******************************************************************************/
set serveroutput on
DECLARE
   prev_num                     NUMBER          := 0;
   random_num                   NUMBER          := 0;
   random_num2                  NUMBER          := 0;
   random_numa                  NUMBER          := 0;
   random_numc                  NUMBER          := 0;
   random_numv                  NUMBER          := 0;
   random_numi                  NUMBER          := 0;
   random_numn                  NUMBER          := 0;
   random_nump                  NUMBER          := 0;
   birthnum                     NUMBER          := 0;
   birth_dt                     VARCHAR2 (30);
   nullify_author               NUMBER          := 0;
   authorppid                   NUMBER;
   chartppid                    NUMBER;
   verifyppid                   NUMBER;
   idbandppid                   NUMBER;
   rsnnulppid                   NUMBER;
   start_date                   NUMBER;
   comment_evnt_id              NUMBER;
   app_linenbr                  number;
   obs_date                     VARCHAR2 (30);
   upd_date                     VARCHAR2 (30);
   ppa_date                     VARCHAR2 (30);
   ppc_date                     VARCHAR2 (30);
   ppv_date                     VARCHAR2 (30);
   ppi_date                     VARCHAR2 (30);
   ppn_date                     VARCHAR2 (30);
   ppp_date                     VARCHAR2 (30);
   end_date                     NUMBER;
   allergy_description          VARCHAR2 (400);
   patient_id                   NUMBER;
   pract_vpid                   VARCHAR2 (30);
   info_src_d                   VARCHAR2 (100);
   info_src_v                   VARCHAR2 (20);
   mechanism_d                  VARCHAR2 (100);
   mechanism_v                  VARCHAR2 (20);
   allgytype_d                  VARCHAR2 (100);
   allgytype_v                  VARCHAR2 (20);
   agent_allerg_d               VARCHAR2 (100);
   agent_allerg_v               VARCHAR2 (20);
   status_code_d                VARCHAR2 (100);
   status_code_v                VARCHAR2 (20);
   severity_cd_d                VARCHAR2 (100);
   severity_cd_v                VARCHAR2 (20);
   reaction_cd_d                VARCHAR2 (100);
   reaction_cd_v                VARCHAR2 (20);
   hospital_cd_d                VARCHAR2 (100);
   hospital_cd_v                VARCHAR2 (20);
   nullify_cd_d                 VARCHAR2 (100);
   nullify_cd_v                 VARCHAR2 (20);
   drging_cd_d                  VARCHAR2 (100);
   drging_cd_v                  VARCHAR2 (20);
   drgcls_cd_d                  VARCHAR2 (100);
   drgcls_cd_v                  VARCHAR2 (20);
   allergy_seq_id_val           NUMBER;
   pract_part_seq_id_val        NUMBER;
   reaction_seq_id_val          NUMBER;
   known_alrgy_null             VARCHAR2 (20);
   known_alrgy_val              CHAR (1);
   patient_seq_id_val           NUMBER;
   gender_code_d                VARCHAR2 (100);
   gender_code_v                VARCHAR2 (30);
   race_code_d                  VARCHAR2 (100);
   race_code_v                  VARCHAR2 (30);
   ethnic_code_d                VARCHAR2 (100);
   ethnic_code_v                VARCHAR2 (30);
   gmr_alrgy_d                  varchar2(100);
   gmr_alrgy_v                  varchar2(30);
   alt_codsys_dc                VARCHAR2(100);
   alt_display_dc               VARCHAR2(100);
   alt_code_dc                  VARCHAR2(100);
   alrg_asmt_d                  varchar2(100);
   alrg_asmt_v                  varchar2(30);
   vrfy_pid                     varchar2(1000);
   vrfy_pfac                    varchar2(30);
   vrfy_cmts                    varchar2(500); 
   vrfy_dt                      varchar2(50);
   vrfy_dtn                     number;
   vrfy_src                     varchar2(50);
   vrfy_name                    varchar2(50);	
   v_vpid                       VARCHAR2 (30);
   starting_patient_vpid        NUMBER;
   fixed_date			        DATE;
   v_pat_fac                    varchar2(256);
   v_pat_auth                   varchar2(256); 

   
BEGIN
   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'Allergies 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');

   allergy_description := 'Provides a textual narrative of the Reaction observed for the intolerance event of the patient.';

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

-- Loop thru how many allergies you want per patient
   FOR z IN 1 .. &1
   LOOP
       -- Loop thru how many patients to assign allergies to
       FOR jj in 1 .. &2
	   LOOP

         -- Set allergy type
         random_num := round(DBMS_RANDOM.VALUE (1, 5));
         CASE random_num
            WHEN '1' THEN
               allgytype_d := 'FOOD';
               allgytype_v := '98547';
            WHEN '2' THEN
               allgytype_d := 'DRUG';
               allgytype_v := '98548';
            WHEN '3' THEN
               allgytype_d := 'FOOD/DRUG';
               allgytype_v := '98548';
            WHEN '4' THEN
               allgytype_d := 'OTHER';
               allgytype_v := '98549';
            ELSE
               allgytype_d    := 'OTHER';
               allgytype_v    := '98549';
         END CASE;
         
         -- Set gmr_alrgy
         random_num := round(DBMS_RANDOM.VALUE (1, 5));
         CASE random_num
            WHEN '1' THEN
               gmr_alrgy_d := 'HAYFEVER';
               gmr_alrgy_v := '879451';
            WHEN '2' THEN
               gmr_alrgy_d := 'COUGH';
               gmr_alrgy_v := '879452';
            WHEN '3' THEN
               gmr_alrgy_d := 'HIVES';
               gmr_alrgy_v := '879453';
            WHEN '4' THEN
               gmr_alrgy_d := 'RASH';
               gmr_alrgy_v := '879454';
            ELSE
               gmr_alrgy_d    := 'SWELLING';
               gmr_alrgy_v    := '879455';
         END CASE;
         
         -- Set information_source_category_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 2));
         CASE random_num
            WHEN '1' THEN
               info_src_d := 'OBSERVED';
               info_src_v := '1002';
            ELSE
               info_src_d     := 'HISTORICAL';
               info_src_v     := '1003';
         END CASE;

         -- Set mechanism_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               mechanism_d := 'ALLERGY';
               mechanism_v := '1027';
            WHEN '2' THEN
               mechanism_d := 'PHARMACOLOGIC';
               mechanism_v := '1028';
            ELSE
               mechanism_d    := 'UNKNOWN';
               mechanism_v    := '1026';
         END CASE;

         -- Set agent_allergen_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 14));
         CASE random_num
            WHEN '1' THEN
               agent_allerg_d := 'ANIMAL DANDER';
               agent_allerg_v := '1047';
            WHEN '2' THEN
               agent_allerg_d := 'ASPIRIN';
               agent_allerg_v := '1035';
            WHEN '3' THEN
               agent_allerg_d := 'BACLOFEN ORA';
               agent_allerg_v := '1029';
            WHEN '4' THEN
               agent_allerg_d := 'COUGH SYRUP AND SPINACH';
               agent_allerg_v := '1044';
            WHEN '5' THEN
               agent_allerg_d := 'DUST';
               agent_allerg_v := '1048';
            WHEN '6' THEN
               agent_allerg_d := 'GRASSES';
               agent_allerg_v := '1049';
            WHEN '7' THEN
               agent_allerg_d := 'HAND CLEANSER AND STRAWBERRIES';
               agent_allerg_v := '1043';
            WHEN '8' THEN
               agent_allerg_d := 'MAGNALOX PLUS';
               agent_allerg_v := '1032';
            WHEN '9' THEN
               agent_allerg_d := 'PEACHES';
               agent_allerg_v := '1036';
            WHEN '10' THEN
               agent_allerg_d := 'PEANUT BUTTER';
               agent_allerg_v := '1037';
            WHEN '11' THEN
               agent_allerg_d := 'SHRIMP';
               agent_allerg_v := '1040';
            WHEN '12' THEN
               agent_allerg_d := 'TREES';
               agent_allerg_v := '1050';
            WHEN '13' THEN
               agent_allerg_d := 'VIAGRA AND SHELLFISH';
               agent_allerg_v := '1042';
            ELSE
               agent_allerg_d     := 'TETRACYCLINE';
               agent_allerg_v     := '1034';
         END CASE;

         -- Set status_code_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               status_code_d := 'F';
               status_code_v := '25';
            WHEN '2' THEN
               status_code_d := 'OPEN';
               status_code_v := '11';
            ELSE
               status_code_d      := 'WAIT';
               status_code_v      := '37';
         END CASE;

         -- Set severity_code_displayname
--         random_num := round(DBMS_RANDOM.VALUE (1, 3));
--         CASE random_num
--            WHEN '1' THEN
--               severity_cd_d := 'MILD';
--               severity_cd_v := '1055';
--            WHEN '2' THEN
--               severity_cd_d := 'MODERATE';
--               severity_cd_v := '1051';
--            ELSE
--               severity_cd_d    := 'SEVERE';
--               severity_cd_v    := '1058';
--         END CASE;
         
         -- Set hospital code
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         CASE random_num
            WHEN '1' THEN
               hospital_cd_d := 'VA SALT LAKE CITY HEALTH CARE SYSTEM';
               hospital_cd_v := '660';
            WHEN '2' THEN
               hospital_cd_d := 'TUSCALOOSA VA MEDICAL CENTER';
               hospital_cd_v := '679';
            WHEN '3' THEN
               hospital_cd_d := 'CENTRAL TEXAS VETERANS HEALTH CARE SYSTEM';
               hospital_cd_v := '674';
            WHEN '4' THEN
               hospital_cd_d := 'PHILADELPHIA VA MEDICAL CENTER';
               hospital_cd_v := '642';
            WHEN '5' THEN
               hospital_cd_d := 'CINCINNATI VA MEDICAL CENTER';
               hospital_cd_v := '539';
            WHEN '6' THEN
               hospital_cd_d := 'JAMES J. PETERS VA MEDICAL CENTER';
               hospital_cd_v := '526';
            WHEN '7' THEN
               hospital_cd_d := 'VA GULF COAST VETERANS HEALTH CARE SYSTEM';
               hospital_cd_v := '520';
            WHEN '8' THEN
               hospital_cd_d := 'RICHARD L. ROUDEBUSH VA MEDICAL CENTER';
               hospital_cd_v := '583';
            WHEN '9' THEN
               hospital_cd_d := 'MIAMI VA HEALTHCARE SYSTEM';
               hospital_cd_v := '546';
            ELSE
               hospital_cd_d    := 'JONATHAN M. WAINWRIGHT MEMORIAL VA MEDICAL CENTER';
               hospital_cd_v    := '687';
         END CASE;
         
         random_num  := DBMS_RANDOM.VALUE (-20, -30);
         random_numa := DBMS_RANDOM.VALUE (-19, -25);
         random_numc := DBMS_RANDOM.VALUE (-18, -27);
         random_numv := DBMS_RANDOM.VALUE (-21, -26);
         random_numi := DBMS_RANDOM.VALUE (-15, -22);
         random_numn := DBMS_RANDOM.VALUE (-17, -29);
         birthnum    := DBMS_random.VALUE (-10950, -36500);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numa, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numc, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numv, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numi, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numn, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date, 'yyyymmddhh24miss')
           INTO obs_date,
                ppa_date,
                ppc_date,
                ppv_date,
                ppi_date,
                ppn_date,
                birth_dt,
                upd_date
           FROM DUAL;

         -- get verifier practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 3));
         IF random_num = 1
         THEN
            vrfy_pid  := 'vrfy_id_' || to_char(round(dbms_random.value(1,1000)));       -- VRFY_PERSON_IDENTITY  
            vrfy_pfac := 'visn ' || to_char(round(dbms_random.value(1,21)));            -- VRFY_PERSON_ASGNING_FACILITY
            vrfy_cmts := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );  -- VRFY_practitioner_comments
            vrfy_dt   := ppv_date;                                                      -- VRFY_PRACTITIONER_TM
            vrfy_dtn  := (to_number(ppv_date,'99999999999999')-17000000000000)/1000000;                          -- VRFY_PRACTITIONER_TM_NBR
--            vrfy_dtn  := to_number(to_char(to_number(substr(ppv_date,1,4)-1700)) || substr(ppv_date,5,4) || '.' || substr(ppv_date,9,length(ppv_date)-8));
            vrfy_src  := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );   -- VRFY_practitioner_id_src_tbl
            vrfy_name := dbms_random.string ('U',trunc(dbms_random.value(1,40)));       -- VRFY_NAME_FAMILY	

         ELSE
            vrfy_pid  := null;   
            vrfy_pfac := null;       
            vrfy_cmts := null;
            vrfy_dt   := null;                                                  
            vrfy_dtn  := null;                         
            vrfy_src  := null;   
            vrfy_name := null;      	
         END IF;         
         
         SELECT allergy_seq_id.NEXTVAL
           INTO allergy_seq_id_val
           FROM DUAL;
         
         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);

		 -- 1.10 mgj load VistA and CHDR data
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
			v_pat_fac   := '200CH';
			v_pat_auth  := 'USDOD';		 
         ELSE
			v_pat_fac   := '605';
			v_pat_auth  := 'USVHA';
		 END IF;
                  
         INSERT INTO allergy30_v
                     (   
                          ALLERGY_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , REQUEST_ID
                        , AGENT_CODE
                        , AGENT_DSPLYTXT
                        , AGENT_CODSYS
                        , ALLERGY_TYP_CODE
                        , ALLERGY_TYP_DSPLYTXT
                        , ALLERGY_TYP_CODSYS
                        , GMR_ALLERGY_CODE
                        , GMR_ALLERGY_DSPLYTXT
                        , GMR_ALLERGY_CODSYS
                        , GMR_ALLERGY_ALT_CODE
                        , GMR_ALLERGY_ALT_DSPLYTXT
                        , GMR_ALLERGY_ALT_CODSYS
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , INFO_SRC_CAT_CODE
                        , INFO_SRC_CAT_DSPLYTXT
                        , INFO_SRC_CAT_CODSYS
                        , ALRGY_OBSERVATION_TM
                        , ALRGY_OBSERVATION_TM_NBR
                        , MECHANISM_CODE
                        , MECHANISM_DSPLYTXT
                        , MECHANISM_CODSYS
                        , ALLERGY_STATUS
                        , ALLERGY_IS_VERIFIED
                        , FACILITY_NAME
                        , FACILITY_IDENTITY
                        , FACILITY_ASSIGNING_AUTHORITY
                        , PATIENT_NAME_PREFIX
                        , PATIENT_NAME_GIVEN
                        , PATIENT_NAME_FAMILY
                        , PATIENT_NAME_SUFFIX
                        , PATIENT_NAME_TITLE
                        , PATIENT_NAME_MIDDLE
                        , AUTH_PERSON_IDENTITY                -- 1.4 BEGIN
                        , AUTH_PERSON_ASGNING_FACILITY
                        , AUTH_PERSON_ASGNING_AUTHORITY
                        , AUTH_PRACTITIONER_COMMENTS
                        , AUTH_PRACTITIONER_TM 
                        , AUTH_PRACTITIONER_TM_NBR
                        , AUTH_PRACTITIONER_ID_SRC_TBL
                        , AUTH_NAME_PREFIX
                        , AUTH_NAME_GIVEN
                        , AUTH_NAME_FAMILY
                        , AUTH_NAME_SUFFIX
                        , AUTH_NAME_TITLE
                        , AUTH_NAME_MIDDLE                    
                        , VRFY_PERSON_IDENTITY                
                        , VRFY_PERSON_ASGNING_FACILITY
                        , VRFY_PERSON_ASGNING_AUTHORITY
                        , VRFY_PRACTITIONER_COMMENTS
                        , VRFY_PRACTITIONER_TM 
                        , VRFY_PRACTITIONER_TM_NBR
                        , VRFY_PRACTITIONER_ID_SRC_TBL
                        , VRFY_NAME_PREFIX
                        , VRFY_NAME_GIVEN
                        , VRFY_NAME_FAMILY
                        , VRFY_NAME_SUFFIX
                        , VRFY_NAME_TITLE
                        , VRFY_NAME_MIDDLE                           
                        , ERR_PERSON_IDENTITY                
                        , ERR_PERSON_ASGNING_FACILITY
                        , ERR_PERSON_ASGNING_AUTHORITY
                        , ERR_PRACTITIONER_COMMENTS
                        , ERR_PRACTITIONER_TM 
                        , ERR_PRACTITIONER_TM_NBR
                        , ERR_PRACTITIONER_ID_SRC_TBL
                        , ERR_NAME_PREFIX
                        , ERR_NAME_GIVEN
                        , ERR_NAME_FAMILY
                        , ERR_NAME_SUFFIX
                        , ERR_NAME_TITLE
                        , ERR_NAME_MIDDLE                                   -- 1.4 END-NULLIFY
                        , RECORDSOURCE_NAMESPACEID
                        , RECORDSOURCE_UNIVERSALID
                        , RECORDSOURCE_UNIVERSTYP
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                     )
              VALUES (  
                        allergy_seq_id_val                                                      -- ALLERGY_ID
                      , v_vpid                                                                  -- PATIENT_IDENTITY
                      , v_pat_fac                                                               -- PATIENT_ASSIGNING_FACILITY 
                      , v_pat_auth                                                              -- PATIENT_ASSIGNING_AUTHORITY
                      , null                                                                    -- REQUEST_ID
                      , agent_allerg_v                                                          -- AGENT_VALSET_VUID	
                      , agent_allerg_d                                                          -- AGENT_DSPLYTXT	
                      , NULL                                                           -- AGENT_ALT_CODSYS	
                      , allgytype_v                                                             -- ALLERGY_TYPE_VALSET_VUID	
                      , allgytype_d                                                             -- ALLERGY_TYPE_DSPLYTXT	
                      , NULL                                                           -- ALLERGY_TYPE_ALT_CODSYS	
                      , gmr_alrgy_v -- gmr_allergy_code
                      , gmr_alrgy_d -- gmr_allergy_dsplytxt
                      , '99VA' -- gmr_allergy_codsys
                      , null
                      , null
                      , null
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_IDENTITY
--                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_NAMESPACEID
                      , '605_120.8'                                                                                                  -- RECORDIDENTIFIER_NAMESPACEID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_UNIVERSALID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- RECORDIDENTIFIER_UNIVERSTYP
                      , info_src_v                                                              -- INFO_SRC_CAT_VALSET_VUID	
                      , info_src_d                                                              -- INFO_SRC_CAT_DSPLYTXT	
                      , null                                                           -- INFO_SRC_CAT_ALT_CODSYS	
                      , obs_date                                                                -- ALRGY_OBSERVATION_TM	
                      , (to_number(obs_date,'99999999999999')-17000000000000)/1000000                                    -- ALRGY_OBSERVATION_Tm_nbr
--                      , to_number(to_char(to_number(substr(obs_date,1,4)-1700)) || substr(obs_date,5,4) || '.' || substr(obs_date,9,length(obs_date)-8))
                      , mechanism_v                                                             -- MECHANISM_VALSET_VUID	
                      , mechanism_d                                                             -- MECHANISM_DSPLYTXT	
                      , null                                                           -- MECHANISM_ALT_CODSYS	
                      , status_code_d                                                           -- STATUS_DSPLYTXT	
                      , 'T'                                                                     -- ALLERGY_IS_VERIFIED	
                      , hospital_cd_d                                                           -- HOSPITAL_LOC_DSPLYTXT	
                      , hospital_cd_v                                                           -- HOSPITAL_LOC_VALSET_VUID	
                      , NULL                                                                    -- facilty	
                      , NULL                                                        -- PREFIX_NM	
                      , NULL                                                        -- GIVEN_NM	
                      , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                      , NULL                                                        -- SUFFIX_NM	
                      , NULL                                                        -- TITLE_NM	
                      , NULL                                                        -- MIDDLE_NM	
                      , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- AUTH_PERSON_IDENTITY  1.4 START
                      , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- AUTH_PERSON_ASGNING_FACILITY
                      , null                                                          -- AUTH_PERSON_ASGNING_AUTHORITY
                      , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )  -- AUTH_practitioner_comments
                      , ppa_date                                                      -- AUTH_PRACTITIONER_TM
                      , (to_number(ppa_date,'99999999999999'))                          -- AUTH_PRACTITIONER_TM_NBR
--                      , to_number(to_char(to_number(substr(ppa_date,1,4)-1700)) || substr(ppa_date,5,4) || '.' || substr(ppa_date,9,length(ppa_date)-8))
                      , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )   -- AUTH_practitioner_id_src_tbl
                      , NULL                                                          -- AUTH_NAME_PREFIX	
                      , NULL                                                          -- AUTH_NAME_GIVEN		
                      , dbms_random.string ('U',trunc(dbms_random.value(1,40)))       -- AUTH_NAME_FAMILY	
                      , NULL                                                          -- AUTH_NAME_SUFFIX	
                      , NULL                                                          -- AUTH_NAME_TITLE	
                      , NULL                                                          -- AUTH_NAME_MIDDLE	
                      , vrfy_pid                                                      -- VRFY_PERSON_IDENTITY  
                      , vrfy_pfac                                                     -- VRFY_PERSON_ASGNING_FACILITY
                      , null                                                          -- VRFY_PERSON_ASGNING_authoRITY          
                      , vrfy_cmts                                                     -- VRFY_practitioner_comments
                      , vrfy_dt                                                       -- VRFY_PRACTITIONER_TM
                      , vrfy_dtn                                                      -- VRFY_PRACTITIONER_TM_NBR
                      , vrfy_src                                                      -- VRFY_practitioner_id_src_tbl
                      , NULL                                                          -- VRFY_NAME_PREFIX         
                      , NULL                                                          -- VRFY_NAME_GIVEN	
                      , vrfy_name                                                     -- VRFY_NAME_FAMILY	
                      , NULL                                                          -- VRFY_NAME_SUFFIX
                      , NULL                                                          -- VRFY_NAME_TITLE	
                      , NULL                                                          -- VRFY_NAME_MIDDLE
                      , NULL                                                          -- ERR_PERSON_IDENTITY
                      , NULL                                                          -- ERR_PERSON_ASGNING_FACILITY
                      , NULL                                                          -- ERR_PERSON_ASGNING_AUTHORITY
                      , NULL                                                          -- ERR_practitioner_comments
                      , NULL                                                          -- ERR_PRACTITIONER_TM
                      , NULL                                                          -- ERR_PRACTITIONER_TM_NBR
                      , NULL                                                          -- ERR_practitioner_id_src_tbl
                      , NULL                                                          -- ERR_NAME_PREFIX	
                      , NULL                                                          -- ERR_NAME_GIVEN		
                      , NULL                                                          -- ERR_NAME_FAMILY	
                      , NULL                                                          -- ERR_NAME_SUFFIX	
                      , NULL                                                          -- ERR_NAME_TITLE	
                      , NULL                                                          -- ERR_NAME_MIDDLE	1.4 END
                      , '605'                                                                   -- RECORDSOURCE_NAMESPACEID
                      , NULL                                                                    -- RECORDSOURCE_UNIVERSALID
                      , null                                                                    -- RECORDSOURCE_UNIVERSTYP
                      , 1                                                                       -- RECORD_VERSION	
                      , upd_date                                                                -- RECORD_UPDATE_TM	
                      , (to_number(upd_date,'99999999999999')-17000000000000)/1000000                                    -- RECORD_UPDATE_TM_NBR	
--                      , to_number(to_char(to_number(substr(upd_date,1,4)-1700)) || substr(upd_date,5,4) || '.' || substr(upd_date,9,length(upd_date)-8))
                     );
                     
         -- check to see if we want to load a NULLIFIER
         random_num := round(dbms_random.value(1,100));
         IF random_num = 1 
         THEN 
            -- update vital nullifier id
            UPDATE allergy30_v SET ERR_PERSON_IDENTITY = 'ERR_id_' || to_char(round(dbms_random.value(1,1000))) ,              
                                 ERR_PERSON_ASGNING_FACILITY = 'visn ' || to_char(round(dbms_random.value(1,21))),
                                 ERR_PRACTITIONER_COMMENTS = DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) ),
                                 ERR_PRACTITIONER_TM = ppn_date, 
                                 ERR_PRACTITIONER_TM_NBR = (to_number(ppn_date,'99999999999999')-17000000000000)/1000000,
                                 ERR_PRACTITIONER_ID_SRC_TBL = dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) ),
                                 ERR_NAME_FAMILY = dbms_random.string ('U',trunc(dbms_random.value(1,40)))           
            WHERE allergy_id = allergy_seq_id_val;
                
         END IF;

         app_linenbr := 0;
         -- get chart practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
         IF random_num = 1
         THEN
             random_num := ROUND(DBMS_RANDOM.value(1,2));
             FOR yy IN 1..random_num
             LOOP
                 SELECT allergy_pract_part_seq_id.NEXTVAL
                   INTO chartppid
                   FROM DUAL;
                 
                 insert_practpart.ALLERGY_INSERT_MESSAGE( ppc_date, chartppid, allergy_seq_id_val, 0, app_linenbr );
                 app_linenbr := app_linenbr + 1;
             END LOOP;
         ELSE
             chartppid := NULL;
         END IF;
         
         -- get idbandppid practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));
         IF random_num = 1
         THEN
             random_num := ROUND(DBMS_RANDOM.value(1,2));
             FOR zz IN 1..random_num
             LOOP
                 SELECT allergy_pract_part_seq_id.NEXTVAL
                   INTO idbandppid
                   FROM DUAL;
                 
                 insert_practpart.ALLERGY_INSERT_MESSAGE( ppi_date, idbandppid, 0, allergy_seq_id_val, app_linenbr );
                 app_linenbr := app_linenbr + 1;
             END LOOP;
         ELSE
             idbandppid := NULL;
         END IF;

         -- load several reactions
         IF &3 > 0
         THEN
            FOR j IN 1 .. &3
            LOOP

               -- Set agent_allergen_displayname
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 14));
               
               CASE random_num
                  WHEN '1' THEN
                     reaction_cd_d      := 'ANAPHYLAXIS';
                     reaction_cd_v      := '1013';
                  WHEN '2' THEN
                     reaction_cd_d      := 'CHEST PAIN';
                     reaction_cd_v      := '1011';
                  WHEN '3' THEN
                     reaction_cd_d      := 'COUGHING';
                     reaction_cd_v      := '1016';
                  WHEN '4' THEN
                     reaction_cd_d      := 'DIARRHEA';
                     reaction_cd_v      := '1007';
                  WHEN '5' THEN
                     reaction_cd_d      := 'ECZEMA';
                     reaction_cd_v      := '1014';
                  WHEN '6' THEN
                     reaction_cd_d      := 'FEVER WITH BLISTERS';
                     reaction_cd_v      := '1001';
                  WHEN '7' THEN
                     reaction_cd_d      := 'HEADACHES';
                     reaction_cd_v      := '1008';
                  WHEN '8' THEN
                     reaction_cd_d      := 'HEARTBURN';
                     reaction_cd_v      := '1006';
                  WHEN '9' THEN
                     reaction_cd_d      := 'HIVES';
                     reaction_cd_v      := '1003';
                  WHEN '10' THEN
                     reaction_cd_d      := 'KIDNEY DAMAGE';
                     reaction_cd_v      := '1020';
                  WHEN '11' THEN
                     reaction_cd_d      := 'LOSE CONSCIOUSNESS';
                     reaction_cd_v      := '1018';
                  WHEN '12' THEN
                     reaction_cd_d      := 'SHOCK';
                     reaction_cd_v      := '1017';
                  WHEN '13' THEN
                     reaction_cd_d      := 'SWELLING OF THE AIRWAYS TO THE LUNGS';
                     reaction_cd_v      := '1012';
                  WHEN '14' THEN
                     reaction_cd_d      := 'VOMITING';
                     reaction_cd_v      := '1005';
                  ELSE
                     reaction_cd_d      := 'OTHER';
                     reaction_cd_v      := '1229';
               END CASE;

               -- Set severity_code_displayname
               random_num := round(DBMS_RANDOM.VALUE (1, 3));
               CASE random_num
                  WHEN '1' THEN
                     severity_cd_d := 'MILD';
                     severity_cd_v := '1055';
                  WHEN '2' THEN
                     severity_cd_d := 'MODERATE';
                     severity_cd_v := '1051';
                  ELSE
                     severity_cd_d    := 'SEVERE';
                     severity_cd_v    := '1058';
               END CASE;

               random_num := DBMS_RANDOM.VALUE (-14, -21);
               random_nump := DBMS_RANDOM.VALUE (-19, -25);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                      TO_CHAR (fixed_date + random_nump, 'yyyymmddhh24miss'),
-- 1.4                      allergy_pract_part_seq_id.NEXTVAL, 
                      reaction_seq_id.NEXTVAL 
                 INTO obs_date,
                      ppp_date,
-- 1.4                      pract_part_seq_id_val,
                      reaction_seq_id_val
                 FROM DUAL;                            

-- 1.4               insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );

               INSERT INTO reaction_v
                           (    
                                REACTION_ID	
                              , ALLERGY_ID	
                              , OBSERVATION_TM	
                              , OBSERVATION_TM_NBR
                              , REACTION_CODE
                              , REACTION_DSPLYTXT	
                              , REACTION_CODSYS	
                              , AUTH_PERSON_IDENTITY                -- 1.4 BEGIN
                              , AUTH_PERSON_ASGNING_FACILITY
                              , AUTH_PERSON_ASGNING_AUTHORITY
                              , AUTH_PRACTITIONER_COMMENTS
                              , AUTH_PRACTITIONER_TM 
                              , AUTH_PRACTITIONER_TM_NBR
                              , AUTH_PRACTITIONER_ID_SRC_TBL
                              , AUTH_NAME_PREFIX
                              , AUTH_NAME_GIVEN
                              , AUTH_NAME_FAMILY
                              , AUTH_NAME_SUFFIX
                              , AUTH_NAME_TITLE
                              , AUTH_NAME_MIDDLE    
                              , LINE_NBR                
                           )
                    VALUES (  
                              reaction_seq_id_val                                               -- REACTION_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , obs_date                                                          -- OBSERVATION_TM 
                            , (to_number(obs_date,'99999999999999')-17000000000000)/1000000                              -- OBSERVATION_TM_NBR
--                      , to_number(to_char(to_number(substr(obs_date,1,4)-1700)) || substr(obs_date,5,4) || '.' || substr(obs_date,9,length(obs_date)-8))
                            , reaction_cd_v                                                     -- REACTION_VALSET_VUID
                            , reaction_cd_d                                                     -- REACTION_DSPLYTXT
                            , NULL                                                              -- REACTION_ALT_CODSYS
                            , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- AUTH_PERSON_IDENTITY  1.4 START
                            , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- AUTH_PERSON_ASGNING_FACILITY
                            , null                                                          -- AUTH_PERSON_ASGNING_AUTHORITY
                            , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )  -- AUTH_practitioner_comments
                            , ppp_date                                                      -- AUTH_PRACTITIONER_TM
                            , (to_number(ppp_date,'99999999999999')-17000000000000)/1000000                          -- AUTH_PRACTITIONER_TM_NBR
--                      , to_number(to_char(to_number(substr(ppp_date,1,4)-1700)) || substr(ppp_date,5,4) || '.' || substr(ppp_date,9,length(ppp_date)-8))
                            , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )   -- AUTH_practitioner_id_src_tbl
                            , NULL                                                          -- AUTH_NAME_PREFIX	
                            , NULL                                                          -- AUTH_NAME_GIVEN		
                            , dbms_random.string ('U',trunc(dbms_random.value(1,40)))       -- AUTH_NAME_FAMILY	
                            , NULL                                                          -- AUTH_NAME_SUFFIX	
                            , NULL                                                          -- AUTH_NAME_TITLE	
                            , NULL                                                          -- AUTH_NAME_MIDDLE	
                            , j-1
                           );

            END LOOP;
         END IF;

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

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

               -- Set drug ingredient vuid and text
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 6));
               
               if prev_num = random_num then
                  if prev_num = 6 then
                     random_num := 1;
                  else
                     random_num := random_num +1;
                  end if;
               end if;
               
               prev_num := random_num;

               CASE random_num
                  WHEN '1' THEN
                     drging_cd_d := 'WARFARIN';
                     drging_cd_v := '98460';
                  WHEN '2' THEN
                     drging_cd_d := 'ASCORBIC ACID';
                     drging_cd_v := '98461';
                  WHEN '3' THEN
                     drging_cd_d := 'TIZANIDINE HYDROCHLORIDE';
                     drging_cd_v := '98462';
                  WHEN '4' THEN
                     drging_cd_d := 'SIBUTRAMINE';
                     drging_cd_v := '98463';
                  WHEN '5' THEN
                     drging_cd_d := 'TENOXICAM';
                     drging_cd_v := '98464';
                  ELSE
                     drging_cd_d    := 'SODIUM VALEPROATE';
                     drging_cd_v    := '98465';
               END CASE;
   
               INSERT INTO DRUG_INGREDIENT_v
                           (    
                                DRUG_INGREDIENT_ID	
                              , ALLERGY_ID	
                              , DRUG_INGREDIENT_CODE	
                              , DRUG_INGREDIENT_DSPLYTXT	
                              , DRUG_INGREDIENT_CODSYS	
                              , LINE_NBR
                           )
                    VALUES (  
                              drug_ingredient_seq_id.NEXTVAL                                    -- DRUG_INGREDIENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drging_cd_v                                                       -- DRUG_INGREDIENT_VALSET_VUID
                            , drging_cd_d                                                       -- DRUG_INGREDIENT_DSPLYTXT
                            , NULL                                                              -- DRUG_INGREDIENT_ALT_CODSYS
                            , drgi-1
                           );

            END LOOP;          
         END IF;

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

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

               -- Set drug class vuid and text
               random_num := ROUND(DBMS_RANDOM.VALUE (1, 6));
               
               if prev_num = random_num then
                  if prev_num = 6 then
                     random_num := 1;
                  else
                     random_num := random_num +1;
                  end if;
               end if;
               
               prev_num := random_num;

               -- Null alternate values
               alt_codsys_dc  := NULL;
               alt_display_dc := NULL;
               alt_code_dc    := NULL;
               
               CASE random_num
                  WHEN '1' THEN
                     drgcls_cd_d := 'GENERAL  ANALGESIC';
                     drgcls_cd_v := '1720';
                  WHEN '2' THEN
                     drgcls_cd_d := 'NARCOTIC ANALGESI';
                     drgcls_cd_v := '1721';
                  WHEN '3' THEN
                     drgcls_cd_d := 'ANTIMIGRAINE/HEADACHE';
                     drgcls_cd_v := '1723';
                  WHEN '4' THEN
                     drgcls_cd_d := 'CENTRAL PAIN SYNDROME';
                     drgcls_cd_v := '1726';
                  WHEN '5' THEN
                     drgcls_cd_d := 'NONSTEROIDAL ANTI-INFLAMMATORY DRUGS';
                     drgcls_cd_v := '1727';
                  ELSE
                     random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
                     IF random_num2 = 1
                     THEN
                         drgcls_cd_d    := NULL;
                         drgcls_cd_v    := '0005';
                         alt_codsys_dc  := 'HL7';
                         alt_display_dc := 'UNKNOWN';
                         alt_code_dc    := '984135684';
                     ELSE 
                         drgcls_cd_d    := 'SWELLING OF THE FINGERS';
                         drgcls_cd_v    := '546846';
                     END IF;
               END CASE;
     
               INSERT INTO DRUG_CLASS_v
                           (   DRUG_CLASS_ID	
                              ,ALLERGY_ID	
                              ,DRUG_CLASS_CODE	
                              ,DRUG_CLASS_DSPLYTXT	
                              ,DRUG_CLASS_CODSYS	
                              ,DRUG_CLASS_ALT_CODE	
                              ,DRUG_CLASS_ALT_DSPLYTXT	
                              ,DRUG_CLASS_ALT_CODSYS
                              ,LINE_NBR
                           )
                    VALUES (  drug_CLASS_seq_id.NEXTVAL                                         -- DRUG_CLASS_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drgcls_cd_v                                                       -- DRUG_CLASS_VALSET_VUID
                            , drgcls_cd_d                                                       -- DRUG_CLASS_DSPLYTXT
                            , NULL  -- DRUG CLASS CODSYS
                            , alt_code_dc                                                       -- DRUG_CLASS_ALT_CODE
                            , alt_display_dc                                                    -- DRUG_CLASS_ALT_DSPLYTXT
                            , alt_codsys_dc                                                     -- DRUG_CLASS_ALT_CODSYS
                            , drgc-1
                           );

            END LOOP;          
         END IF;

         -- load 0 or many comment events
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 2));

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

               random_num := DBMS_RANDOM.VALUE (-5, -11);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss')
--                      allergy_pract_part_seq_id.NEXTVAL 
                 INTO ppp_date
--                      pract_part_seq_id_val
                 FROM DUAL;                            
--
--               insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );

               SELECT ALLERGY_COMMENT_EVENT_SEQ_ID.NEXTVAL
               INTO comment_evnt_id
               FROM DUAL;

               INSERT INTO ALLERGY_COMMENT_EVENT_v
                           (   
                              ALLERGY_COMMENT_EVENT_ID
                            , ALLERGY_ID
--                            , AUTHOR_PPID
                            , COMMENT_TM
                            , COMMENT_TM_NBR
                            , COMMENT_TYP_CODE
                            , COMMENT_TYP_DSPLYTXT
                            , COMMENT_TYP_CODSYS
--                            , COMMENT_TXT
                              , AUTH_PERSON_IDENTITY                -- 1.4 BEGIN
                              , 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      
                              , LINE_NBR              
                           )
                    VALUES (  
                              comment_evnt_id                                                   -- ALLERGY_COMMENT_EVENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
--                            , pract_part_seq_id_val                                             -- AUTHOR_PPID
                            , ppp_date                                                          -- COMMENT_TM
                            , (to_number(ppp_date,'99999999999999')-17000000000000)/1000000                              -- COMMENT_TM_NBR
--                      , to_number(to_char(to_number(substr(ppp_date,1,4)-1700)) || substr(ppp_date,5,4) || '.' || substr(ppp_date,9,length(ppp_date)-8))
                            , '23892398'                                                        -- COMMENT_TYP_VALSET_VUID
                            , 'NORMAL TEXT'                                                     -- COMMENT_TYP_DSPLYTXT
                            , NULL                                                              -- COMMENT_TYP_ALT_CODSYS
--                            , dbms_random.string ('U',trunc(dbms_random.value(1,400)))          -- COMMENT_TXT
                            , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- AUTH_PERSON_IDENTITY  1.4 START
                            , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- AUTH_PERSON_ASGNING_FACILITY
                            , null                                                          -- AUTH_PERSON_ASGNING_AUTHORITY
                            , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,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	
                            , cmnte-1
                           );

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

                IF random_num = 0
                THEN
                   FOR cmnt IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 6))
                   LOOP
-- 1.7 add allergy_comment_id
                       INSERT INTO ALLERGY_COMMENT_v
                                   (  allergy_comment_id
                                    , ALLERGY_COMMENT_EVENT_ID
                                    , COMMENT_TXT
                                    , LINE_NBR
                                   )
                            VALUES (
                                      ALLERGY_COMMENT_SEQ_ID.nextval
                                    , comment_evnt_id
                                    , dbms_random.string ('U',trunc(dbms_random.value(1,400)))          -- COMMENT_TXT
                                    , cmnt-1
                                   );

                   END LOOP;
                END IF;          

            END LOOP;          
         END IF;

       END LOOP;
       
       FOR asmt in 1 .. &1
	   LOOP
           random_num := DBMS_RANDOM.VALUE (-14, -21);
           birthnum    := DBMS_random.VALUE (-10950, -36500);
       
           -- load author
           SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                  TO_CHAR (fixed_date, 'yyyymmddhh24miss'),
                  TO_CHAR (fixed_date + birthnum, 'yyyymmddhh24miss')
-- 1.4                  allergy_pract_part_seq_id.NEXTVAL
             INTO obs_date,
                  upd_date,
                  birth_dt
-- 1.4                  pract_part_seq_id_val
             FROM DUAL;                            

-- 1.4           insert_practpart.ALLERGY_INSERT_MESSAGE( ppp_date, pract_part_seq_id_val );
           
           -- 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;
           
           -- get Patient seq id
--           SELECT ALLERGY_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;
        
           -- Set allergy assesment
           random_num := round(DBMS_RANDOM.VALUE (1, 7));
           CASE random_num
              WHEN '1' THEN
                 alrg_asmt_d  := 'SEAFOOD';
                 alrg_asmt_v  := '159748';
              WHEN '2' THEN
                 alrg_asmt_d  := 'PET DANDER';
                 alrg_asmt_v  := '159749';
              WHEN '3' THEN
                 alrg_asmt_d  := 'SEASONAL - SPRING';
                 alrg_asmt_v  := '159750';
              WHEN '4' THEN
                 alrg_asmt_d  := 'DUST';
                 alrg_asmt_v  := '159751';
              WHEN '5' THEN
                 alrg_asmt_d  := 'PENICILAN';
                 alrg_asmt_v  := '159752';
              WHEN '6' THEN
                 alrg_asmt_d  := 'SEASONAL - FALL';
                 alrg_asmt_v  := '159753';
              ELSE
                 alrg_asmt_d  := 'OTHER';
                 alrg_asmt_v  := '1597454';
           END CASE;

           v_vpid := 'test_sourceid_' || to_char(asmt + starting_patient_vpid);
        
           -- Load Patient Record
           
/* mgj 1.8   1.9 uncomment*/  
           INSERT INTO ALLERGY_ASSESSMENT30_v
                      (   
                          ALLERGY_ASSESSMENT_ID
                        , PATIENT_IDENTITY
                        , PATIENT_ASSIGNING_FACILITY
                        , PATIENT_ASSIGNING_AUTHORITY
                        , REQUEST_ID
                        , ASSESSMENT_VAL_CODE
                        , ASSESSMENT_VAL_DSPLYTXT
                        , ASSESSMENT_VAL_CODSYS
                        , RECORDIDENTIFIER_IDENTITY
                        , RECORDIDENTIFIER_NAMESPACEID
                        , RECORDIDENTIFIER_UNIVERSALID
                        , RECORDIDENTIFIER_UNIVERSTYP
                        , ALGYASMT_OBSERVATION_TM
                        , ALGYASMT_OBSERVATION_TM_NBR
                        , ALLERGY_ASSESSMENT_STATUS
                        , PATIENT_NAME_PREFIX
                        , PATIENT_NAME_GIVEN
                        , PATIENT_NAME_FAMILY
                        , PATIENT_NAME_SUFFIX
                        , PATIENT_NAME_TITLE
                        , PATIENT_NAME_MIDDLE
                        , AUTH_PERSON_IDENTITY                -- 1.4 BEGIN
                        , AUTH_PERSON_ASGNING_FACILITY
                        , AUTH_PERSON_ASGNING_AUTHORITY
                        , AUTH_PRACTITIONER_COMMENTS
                        , AUTH_PRACTITIONER_TM 
                        , AUTH_PRACTITIONER_TM_NBR
                        , AUTH_PRACTITIONER_ID_SRC_TBL
                        , AUTH_NAME_PREFIX
                        , AUTH_NAME_GIVEN
                        , AUTH_NAME_FAMILY
                        , AUTH_NAME_SUFFIX
                        , AUTH_NAME_TITLE
                        , AUTH_NAME_MIDDLE                    
                        , RECORDSOURCE_NAMESPACEID
                        , RECORDSOURCE_UNIVERSALID
                        , RECORDSOURCE_UNIVERSTYP
                        , RECORD_VERSION
                        , RECORD_UPDATE_TM
                        , RECORD_UPDATE_TM_NBR
                      )
               VALUES (  
                         allergy_assessment_seq_id.NEXTVAL                                      -- ALLERGY_ASSESSMENT_ID
                       , v_vpid              -- PATIENT_IDENTITY
                       , '660'                                                                  -- PATIENT_ASSIGNING_FACILITY  1.6
                       , 'USVHA'                                                                -- PATIENT_ASSIGNING_AUTHORITY   1.6
                       , null                                                                   -- REQUEST_ID
                       , alrg_asmt_v   --allergy assessment
                       , alrg_asmt_d
                       , null
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_IDENTITY	
--                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_NAMESPACEID	
                       , '605_120.86'                                                                                                                -- RECORDIDENTIFIER_NAMESPACEID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_UNIVERSALID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- RECORDIDENTIFIER_UNIVERSTYP	
                       , obs_date                                                               -- ALGYASMT_OBSERVATION_TIME
                       , (to_number(obs_date,'99999999999999')-17000000000000)/1000000                                   -- ALGYASMT_OBSERVATION_TIME_NBR
--                      , to_number(to_char(to_number(substr(obs_date,1,4)-1700)) || substr(obs_date,5,4) || '.' || substr(obs_date,9,length(obs_date)-8))
                       , status_code_d                                                          -- STATUS_DSPLYTXT	
                       , NULL                                                        -- PREFIX_NM	
                       , NULL                                                        -- GIVEN_NM	
                       , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                       , NULL                                                        -- SUFFIX_NM	
                       , NULL                                                        -- TITLE_NM	
                       , NULL                                                        -- MIDDLE_NM	
                       , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- AUTH_PERSON_IDENTITY  1.4 START
                       , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- AUTH_PERSON_ASGNING_FACILITY
                       , null                                                          -- AUTH_PERSON_ASGNING_AUTHORITY
                       , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )  -- AUTH_practitioner_comments
                       , obs_date                                                      -- AUTH_PRACTITIONER_TM
                       , (to_number(obs_date,'99999999999999')-17000000000000)/1000000                                   -- RECORD_UPDATE_TM_NBR
--                      , to_number(to_char(to_number(substr(obs_date,1,4)-1700)) || substr(obs_date,5,4) || '.' || substr(obs_date,9,length(obs_date)-8))
                       , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )   -- AUTH_practitioner_id_src_tbl
                       , NULL                                                          -- AUTH_NAME_PREFIX	
                       , NULL                                                          -- AUTH_NAME_GIVEN		
                       , dbms_random.string ('U',trunc(dbms_random.value(1,40)))       -- AUTH_NAME_FAMILY	
                       , NULL                                                          -- AUTH_NAME_SUFFIX	
                       , NULL                                                          -- AUTH_NAME_TITLE	
                       , NULL                                                          -- AUTH_NAME_MIDDLE	
                      , '605'                                                                   -- RECORDSOURCE_NAMESPACEID
                      , NULL                                                                    -- RECORDSOURCE_UNIVERSALID
                      , null                                                                    -- RECORDSOURCE_UNIVERSTYP
                       , 1                                                                      -- RECORD_VERSION	
                       , upd_date                                                               -- RECORD_UPDATE_TM	
                       , (to_number(upd_date,'99999999999999')-17000000000000)/1000000                                   -- RECORD_UPDATE_TM_NBR
--                      , to_number(to_char(to_number(substr(upd_date,1,4)-1700)) || substr(upd_date,5,4) || '.' || substr(upd_date,9,length(upd_date)-8))
                      );
/*1.8 end */                      
       END LOOP;
       COMMIT;
   END LOOP;

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