/******************************************************************************
   NAME:       new_allergies_load3x_arms.sql
   PURPOSE:    This script will create Allergies records that map to 
               VHIM version 3.6.2  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        07/11/2007  M. Jacobsen      - Initial Release
******************************************************************************/
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;
   nullify_author               NUMBER          := 0;
   authorppid                   NUMBER;
   chartppid                    NUMBER;
   verifyppid                   NUMBER;
   idbandppid                   NUMBER;
   rsnnulppid                   NUMBER;
   start_date                   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_seq_id_val           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);
   alt_codsys_at                VARCHAR2 (50);
   alt_display_at               VARCHAR2 (100);
   alt_code_at                  VARCHAR2 (30);
   alt_reason_at                VARCHAR2 (100);   
   alt_codsys_is                VARCHAR2 (50);
   alt_display_is               VARCHAR2 (100);
   alt_code_is                  VARCHAR2 (30);
   alt_reason_is                VARCHAR2 (100);
   alt_codsys_mc                VARCHAR2 (50);
   alt_display_mc               VARCHAR2 (100);
   alt_code_mc                  VARCHAR2 (30);
   alt_reason_mc                VARCHAR2 (100);
   alt_codsys_ag                VARCHAR2 (50);
   alt_display_ag               VARCHAR2 (100);
   alt_code_ag                  VARCHAR2 (30);
   alt_reason_ag                VARCHAR2 (100);
   alt_codsys_st                VARCHAR2 (50);
   alt_display_st               VARCHAR2 (100);
   alt_code_st                  VARCHAR2 (30);
   alt_reason_st                VARCHAR2 (100);
   alt_codsys_sv                VARCHAR2 (50);
   alt_display_sv               VARCHAR2 (100);
   alt_code_sv                  VARCHAR2 (30);
   alt_reason_sv                VARCHAR2 (100);
   alt_codsys_hs                VARCHAR2 (50);
   alt_display_hs               VARCHAR2 (100);
   alt_code_hs                  VARCHAR2 (30);
   alt_reason_hs                VARCHAR2 (100);
   alt_codsys_nl                VARCHAR2 (50);
   alt_display_nl               VARCHAR2 (100);
   alt_code_nl                  VARCHAR2 (30);
   alt_reason_nl                VARCHAR2 (100);
   alt_codsys_rn                VARCHAR2 (50);
   alt_display_rn               VARCHAR2 (100);
   alt_code_rn                  VARCHAR2 (30);
   alt_reason_rn                VARCHAR2 (100);
   alt_codsys_di                VARCHAR2 (50);
   alt_display_di               VARCHAR2 (100);
   alt_code_di                  VARCHAR2 (30);
   alt_reason_di                VARCHAR2 (100);
   alt_codsys_dc                VARCHAR2 (50);
   alt_display_dc               VARCHAR2 (100);
   alt_code_dc                  VARCHAR2 (30);
   alt_reason_dc                VARCHAR2 (100);
   starting_patient_vpid        NUMBER;
   v_vpid                       VARCHAR2 (30);
   v_gender                     VARCHAR2 (50);
   v_marital_status             VARCHAR2 (50);
   fixed_date			DATE;

   
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 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 := '1015';
         END CASE;

         -- Null alternate values
         alt_codsys_at  := NULL;
         alt_display_at := NULL;
         alt_code_at    := NULL;
         alt_reason_at  := NULL;
         
         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   allgytype_d    := NULL;
                   allgytype_v    := '0001';
                   alt_codsys_at  := 'HL7';
                   alt_display_at := 'UNKNOWN';
                   alt_code_at    := '549811235';
                   alt_reason_at  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   allgytype_d    := 'OTHER';
                   allgytype_v    := '98549';
               END IF;
         END CASE;
         
         -- Null alternate values
         alt_codsys_is  := NULL;
         alt_display_is := NULL;
         alt_code_is    := NULL;
         alt_reason_is  := NULL;
         
         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   info_src_d     := NULL;
                   info_src_v     := '0002';
                   alt_codsys_is  := 'HL7';
                   alt_display_is := 'UNKNOWN';
                   alt_code_is    := '216987984';
                   alt_reason_is  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   info_src_d     := 'HISTORICAL';
                   info_src_v     := '1003';
               END IF;
         END CASE;

         -- Null alternate values
         alt_codsys_mc  := NULL;
         alt_display_mc := NULL;
         alt_code_mc    := NULL;
         alt_reason_mc  := NULL;

         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   mechanism_d    := NULL;
                   mechanism_v    := NULL;
                   alt_codsys_mc  := 'HL7';
                   alt_display_mc := 'UNKNOWN';
                   alt_code_mc    := '158731598';
                   alt_reason_mc  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   mechanism_d    := 'UNKNOWN';
                   mechanism_v    := '1026';
               END IF;
         END CASE;

         -- Null alternate values
         alt_codsys_ag  := NULL;
         alt_display_ag := NULL;
         alt_code_ag    := NULL;
         alt_reason_ag  := NULL;

         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   agent_allerg_d     := NULL;
                   agent_allerg_v     := NULL;
                   alt_codsys_ag      := 'HL7';
                   alt_display_ag     := 'UNKNOWN';
                   alt_code_ag        := '9584135984';
                   alt_reason_ag      := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   agent_allerg_d     := 'TETRACYCLINE';
                   agent_allerg_v     := '1034';
               END IF;
         END CASE;

         -- Null alternate values
         alt_codsys_st  := NULL;
         alt_display_st := NULL;
         alt_code_st    := NULL;
         alt_reason_st  := NULL;

         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   status_code_d      := NULL;
                   status_code_v      := '0003';
                   alt_codsys_st      := 'HL7';
                   alt_display_st     := 'UNKNOWN';
                   alt_code_st        := '8432165987';
                   alt_reason_st      := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   status_code_d      := 'WAIT';
                   status_code_v      := '37';
               END IF;
         END CASE;

         -- Null alternate values
         alt_codsys_sv  := NULL;
         alt_display_sv := NULL;
         alt_code_sv    := NULL;
         alt_reason_sv  := NULL;

         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   severity_cd_d    := NULL;
                   severity_cd_v    := NULL;
                   alt_codsys_sv    := 'HL7';
                   alt_display_sv   := 'UNKNOWN';
                   alt_code_sv      := '32165498';
                   alt_reason_sv    := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   severity_cd_d    := 'SEVERE';
                   severity_cd_v    := '1058';
               END IF;
         END CASE;
         
         -- Null alternate values
         alt_codsys_hs  := NULL;
         alt_display_hs := NULL;
         alt_code_hs    := NULL;
         alt_reason_hs  := NULL;

         -- 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
               random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
               IF random_num2 = 1
               THEN
                   hospital_cd_d    := NULL;
                   hospital_cd_v    := NULL;
                   alt_codsys_hs    := 'HL7';
                   alt_display_hs   := 'UNKNOWN';
                   alt_code_hs      := '0226549681';
                   alt_reason_hs    := 'INCOMING FROM PRIVATE SECTOR';
               ELSE 
                   hospital_cd_d    := 'JONATHAN M. WAINWRIGHT MEMORIAL VA MEDICAL CENTER';
                   hospital_cd_v    := '687';
               END IF;
         END CASE;
         
         -- Set values to null as default
         nullify_cd_d   := NULL;
         nullify_cd_v   := NULL;
         alt_codsys_nl  := NULL;
         alt_display_nl := NULL;
         alt_code_nl    := NULL;
         alt_reason_nl  := NULL;
         nullify_author := 0;
            
         -- is the act nullified?
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 50));

         IF random_num = 1
         THEN
            nullify_author  := 1;
            random_num := ROUND (DBMS_RANDOM.VALUE (1, 100));
            
            IF random_num < 95
            THEN
                nullify_cd_d    := 'MEDIA FAILURE';
                nullify_cd_v    := '827327';
            ELSE
                alt_codsys_nl   := 'HL7';
                alt_display_nl  := 'UNKNOWN';
                alt_code_nl     := '0898465165';
                alt_reason_nl   := 'INCOMING FROM PRIVATE SECTOR';
            END IF;
         END IF;
         
         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);

         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, 'yyyymmddhh24miss')
           INTO obs_date,
                ppa_date,
                ppc_date,
                ppv_date,
                ppi_date,
                ppn_date,
                upd_date
           FROM DUAL;

         -- get author practitioner
         SELECT pract_part_seq_id.NEXTVAL
           INTO authorppid
           FROM DUAL;
         
         insert_practpart.insert_message( ppa_date, authorppid );
         
         -- get chart practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));

         -- set gender and marital status as well
         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO chartppid
               FROM DUAL;
             
             insert_practpart.insert_message( ppc_date, chartppid );
             v_gender := 'MALE';
             v_marital_status := 'DIVORCED';
         ELSIF random_num = 2
         THEN
             chartppid := NULL;
             v_gender := 'FEMALE';
             v_marital_status := 'SINGLE';
         ELSIF random_num = 3
         THEN
             chartppid := NULL;
             v_gender := 'MALE';
             v_marital_status := 'MARRIED';
         ELSE
             chartppid := NULL;
             v_gender := 'FEMALE';
             v_marital_status := 'MARRIED';
         END IF;
         
--          IF random_num < 3
--          THEN
--          ELSE
--          END IF;
         
         -- get verifier practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 3));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO verifyppid
               FROM DUAL;
             
             insert_practpart.insert_message( ppv_date, verifyppid );
         ELSE
             verifyppid := NULL;
         END IF;
         
         -- get idbandppid practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO idbandppid
               FROM DUAL;
             
             insert_practpart.insert_message( ppi_date, idbandppid );
         ELSE
             idbandppid := NULL;
         END IF;
         
         -- get rsnnulppid practitioner
         IF nullify_author = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO rsnnulppid
               FROM DUAL;
             
             insert_practpart.insert_message( ppn_date, rsnnulppid );
         ELSE
             rsnnulppid := NULL;
         END IF;
         
         SELECT allergy_seq_id.NEXTVAL
           INTO allergy_seq_id_val
           FROM DUAL;

         -- get Patient seq id
         SELECT patient_information_seq_id.NEXTVAL
           INTO patient_seq_id_val
           FROM DUAL;
         
-- set patient vpid
--          IF (starting_patient_vpid + jj) > 50000
--          THEN
--              patient_id   := (jj + starting_patient_vpid - 50000);
--          ELSE
--              patient_id   := (jj + starting_patient_vpid);
--          END IF; 
--dbms_output.put_line('what is patient_id?  ' || patient_id);		 

         v_vpid := 'test_vpid_' || to_char(jj + starting_patient_vpid);
         
         -- Load Patient Record
         INSERT INTO PATIENT_information (  PATIENT_information_ID, 	
                                name_PREFIX, 	
                                name_GIVEN, 	
                                name_NICKNAME, 	
                                name_FAMILY, 	
                                name_SUFFIX, 	
                                name_TITLE, 	
                                name_FORMATTED, 	
                                name_MIDDLE, 	
                                birthdate,
                                gender,
                                marital_status,
                                mother_maiden_name,
                                religion,                           
                                LAST_UPDATED_DTTZ
                              )
         VALUES (
                   patient_seq_id_val                                          -- PATIENT_ID
                 , NULL                                                        -- PREFIX_NM	
                 , NULL                                                        -- GIVEN_NM	
                 , NULL                                                        -- NICKNAME_NM	
                 , dbms_random.string ('U',trunc(dbms_random.value(1,40)))     -- FAMILY_NM	
                 , NULL                                                        -- SUFFIX_NM	
                 , NULL                                                        -- TITLE_NM	
                 , NULL                                                        -- FORMATTED_NM	
                 , NULL                                                        -- MIDDLE_NM	
                 , fixed_date - (365*(dbms_random.value(30,100)))              -- birthdate	
                 , v_gender                                                    -- gender	
                 , v_marital_status                                            -- marital_status	
                 , dbms_random.string ('U',trunc(dbms_random.value(1,40)))     -- mother_maiden_name	
                 , null                                                        -- religion
                 , SYSTIMESTAMP                                                -- last_updated_dttz
                );
         
         INSERT INTO allergy
                     (   ALLERGY_ID	
                        ,PATIENT_INFORMATION_ID	
                        ,AUTHOR_PPID	
                        ,CHART_UPDATER_PPID	
                        ,VERIFIER_PPID	
                        ,ID_BAND_UPDATER_PPID	
                        ,NULLIFIER_PPID	
                        , patient_source_id
                        , patient_namespace_id
                        ,AGENT_NULLFLV	
                        ,AGENT_VALSET_VUID	
                        ,AGENT_CONCEPT_VUID	
                        ,AGENT_DSGNTN_VUID	
                        ,AGENT_DSPLYTXT	
                        ,AGENT_ALT_CODSYS	
                        ,AGENT_ALT_CODE	
                        ,AGENT_ALT_DSPLYTXT	
                        ,AGENT_ALT_REASON	
                        ,ALLERGY_TYPE_VALSET_VUID	
                        ,ALLERGY_TYPE_CONCEPT_VUID	
                        ,ALLERGY_TYPE_DSGNTN_VUID	
                        ,ALLERGY_TYPE_DSPLYTXT	
                        ,ALLERGY_TYPE_ALT_CODSYS	
                        ,ALLERGY_TYPE_ALT_CODE	
                        ,ALLERGY_TYPE_ALT_DSPLYTXT	
                        ,ALLERGY_TYPE_ALT_REASON	
                        ,DESCRIPTION	
                        ,IDENTITY_ENTITYID	
                        ,IDENTITY_NAMESPACEID	
                        ,IDENTITY_UNIVERSALID	
                        ,IDENTITY_UNIVERSALTYP	
                        ,INFO_SRC_CAT_VALSET_VUID	
                        ,INFO_SRC_CAT_CONCEPT_VUID	
                        ,INFO_SRC_CAT_DSGNTN_VUID	
                        ,INFO_SRC_CAT_DSPLYTXT	
                        ,INFO_SRC_CAT_ALT_CODSYS	
                        ,INFO_SRC_CAT_ALT_CODE	
                        ,INFO_SRC_CAT_ALT_DSPLYTXT	
                        ,INFO_SRC_CAT_ALT_REASON	
                        ,MECHANISM_NULLFLV	
                        ,MECHANISM_VALSET_VUID	
                        ,MECHANISM_CONCEPT_VUID	
                        ,MECHANISM_DSGNTN_VUID	
                        ,MECHANISM_DSPLYTXT	
                        ,MECHANISM_ALT_CODSYS	
                        ,MECHANISM_ALT_CODE	
                        ,MECHANISM_ALT_DSPLYTXT	
                        ,MECHANISM_ALT_REASON	
                        ,OBSERVATION_TM	
                        ,STATUS_VALSET_VUID	
                        ,STATUS_CONCEPT_VUID	
                        ,STATUS_DSGNTN_VUID	
                        ,STATUS_DSPLYTXT	
                        ,STATUS_ALT_CODSYS	
                        ,STATUS_ALT_CODE	
                        ,STATUS_ALT_DSPLYTXT	
                        ,STATUS_ALT_REASON	
                        ,RECORD_VERSION	
                        ,ALLERGY_IS_VERIFIED	
                        ,RECORD_UPDATE_TM	
                        ,SEVERITY_NULLFLV	
                        ,SEVERITY_VALSET_VUID	
                        ,SEVERITY_CONCEPT_VUID	
                        ,SEVERITY_DSGNTN_VUID	
                        ,SEVERITY_DSPLYTXT	
                        ,SEVERITY_ALT_CODSYS	
                        ,SEVERITY_ALT_CODE	
                        ,SEVERITY_ALT_DSPLYTXT	
                        ,SEVERITY_ALT_REASON	
                        ,HOSPITAL_LOC_VALSET_VUID	
                        ,HOSPITAL_LOC_CONCEPT_VUID	
                        ,HOSPITAL_LOC_DSGNTN_VUID	
                        ,HOSPITAL_LOC_DSPLYTXT	
                        ,HOSPITAL_LOC_ALT_CODSYS	
                        ,HOSPITAL_LOC_ALT_CODE	
                        ,HOSPITAL_LOC_ALT_DSPLYTXT	
                        ,HOSPITAL_LOC_ALT_REASON	
--                         ,NULLIFY_REASON_VALSET_VUID	
--                         ,NULLIFY_REASON_CONCEPT_VUID	
--                         ,NULLIFY_REASON_DSGNTN_VUID	
--                         ,NULLIFY_REASON_DSPLYTXT	
--                         ,NULLIFY_REASON_ALT_CODSYS	
--                         ,NULLIFY_REASON_ALT_CODE	
--                         ,NULLIFY_REASON_ALT_DSPLYTXT	
--                         ,NULLIFY_REASON_ALT_REASON	
                        ,LAST_UPDATED_DTTZ	
                     )
              VALUES (  allergy_seq_id_val                                                      -- ALLERGY_ID
                      , patient_seq_id_val                                                      -- PATIENT_INFORMATION_ID
                      , authorppid                                                              -- AUTHOR_PPID
                      , chartppid                                                               -- CHART_UPDATER_PPID
                      , verifyppid                                                              -- VERIFIER_PPID
                      , idbandppid                                                              -- ID_BAND_UPDATER_PPID
                      , rsnnulppid                                                              -- NULLIFIER_PPID	
                      , v_vpid                                                                  -- patient_source_id
                      , 'DNS   -605'                                                            -- patient_namespace_id
                      , NULL                                                                    -- AGENT_NULLFLV	
                      , agent_allerg_v                                                          -- AGENT_VALSET_VUID	
                      , NULL                                                                    -- AGENT_CONCEPT_VUID	
                      , NULL                                                                    -- AGENT_DSGNTN_VUID	
                      , agent_allerg_d                                                          -- AGENT_DSPLYTXT	
                      , alt_codsys_ag                                                           -- AGENT_ALT_CODSYS	
                      , alt_code_ag                                                             -- AGENT_ALT_CODE	
                      , alt_display_ag                                                          -- AGENT_ALT_DSPLYTXT	
                      , alt_reason_ag                                                           -- AGENT_ALT_REASON	
                      , allgytype_v                                                             -- ALLERGY_TYPE_VALSET_VUID	
                      , NULL                                                                    -- ALLERGY_TYPE_CONCEPT_VUID	
                      , NULL                                                                    -- ALLERGY_TYPE_DSGNTN_VUID	
                      , allgytype_d                                                             -- ALLERGY_TYPE_DSPLYTXT	
                      , alt_codsys_at                                                           -- ALLERGY_TYPE_ALT_CODSYS	
                      , alt_code_at                                                             -- ALLERGY_TYPE_ALT_CODE	
                      , alt_display_at                                                          -- ALLERGY_TYPE_ALT_DSPLYTXT	
                      , alt_reason_at                                                           -- ALLERGY_TYPE_ALT_REASON	
                      , allergy_description                                                     -- DESCRIPTION	
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- IDENTITY_ENTITYID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- IDENTITY_NAMESPACEID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- IDENTITY_UNIVERSALID
                      , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_seq_id_val -- IDENTITY_UNIVERSALTYP
                      , info_src_v                                                              -- INFO_SRC_CAT_VALSET_VUID	
                      , NULL                                                                    -- INFO_SRC_CAT_CONCEPT_VUID	
                      , NULL                                                                    -- INFO_SRC_CAT_DSGNTN_VUID	
                      , info_src_d                                                              -- INFO_SRC_CAT_DSPLYTXT	
                      , alt_codsys_is                                                           -- INFO_SRC_CAT_ALT_CODSYS	
                      , alt_code_is                                                             -- INFO_SRC_CAT_ALT_CODE	
                      , alt_display_is                                                          -- INFO_SRC_CAT_ALT_DSPLYTXT	
                      , alt_reason_is                                                           -- INFO_SRC_CAT_ALT_REASON	
                      , NULL                                                                    -- MECHANISM_NULLFLV	
                      , mechanism_v                                                             -- MECHANISM_VALSET_VUID	
                      , NULL                                                                    -- MECHANISM_CONCEPT_VUID	
                      , NULL                                                                    -- MECHANISM_DSGNTN_VUID	
                      , mechanism_d                                                             -- MECHANISM_DSPLYTXT	
                      , alt_codsys_mc                                                           -- MECHANISM_ALT_CODSYS	
                      , alt_code_mc                                                             -- MECHANISM_ALT_CODE	
                      , alt_display_mc                                                          -- MECHANISM_ALT_DSPLYTXT	
                      , alt_reason_mc                                                           -- MECHANISM_ALT_REASON	
                      , obs_date                                                                -- OBSERVATION_TM	
                      , status_code_v                                                           -- STATUS_VALSET_VUID	
                      , NULL                                                                    -- STATUS_CONCEPT_VUID	
                      , NULL                                                                    -- STATUS_DSGNTN_VUID	
                      , status_code_d                                                           -- STATUS_DSPLYTXT	
                      , alt_codsys_st                                                           -- STATUS_ALT_CODSYS	
                      , alt_code_st                                                             -- STATUS_ALT_CODE	
                      , alt_display_st                                                          -- STATUS_ALT_DSPLYTXT	
                      , alt_reason_st                                                           -- STATUS_ALT_REASON	
                      , 1                                                                       -- RECORD_VERSION	
                      , 'T'                                                                     -- ALLERGY_IS_VERIFIED	
                      , upd_date                                                                -- RECORD_UPDATE_TM	
                      , NULL                                                                    -- SEVERITY_NULLFLV	
                      , severity_cd_v                                                           -- SEVERITY_VALSET_VUID	
                      , NULL                                                                    -- SEVERITY_CONCEPT_VUID	
                      , NULL                                                                    -- SEVERITY_DSGNTN_VUID	
                      , severity_cd_d                                                           -- SEVERITY_DSPLYTXT	
                      , alt_codsys_sv                                                           -- SEVERITY_ALT_CODSYS	
                      , alt_code_sv                                                             -- SEVERITY_ALT_CODE	
                      , alt_display_sv                                                          -- SEVERITY_ALT_DSPLYTXT	
                      , alt_reason_sv                                                           -- SEVERITY_ALT_REASON	
                      , hospital_cd_v                                                           -- HOSPITAL_LOC_VALSET_VUID	
                      , NULL                                                                    -- HOSPITAL_LOC_CONCEPT_VUID	
                      , NULL                                                                    -- HOSPITAL_LOC_DSGNTN_VUID	
                      , hospital_cd_d                                                           -- HOSPITAL_LOC_DSPLYTXT	
                      , alt_codsys_hs                                                           -- HOSPITAL_LOC_ALT_CODSYS	
                      , alt_code_hs                                                             -- HOSPITAL_LOC_ALT_CODE	
                      , alt_display_hs                                                          -- HOSPITAL_LOC_ALT_DSPLYTXT	
                      , alt_reason_hs                                                           -- HOSPITAL_LOC_ALT_REASON	
                      , SYSTIMESTAMP                                                            -- LAST_UPDATED_DTTZ
                     );

         -- 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));
               
               alt_codsys_rn    := NULL;
               alt_display_rn   := NULL;
               alt_code_rn      := NULL;
               alt_reason_rn    := NULL;

               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      := NULL;
                     reaction_cd_v      := NULL;
                     alt_codsys_rn      := 'HL7';
                     alt_display_rn     := 'UNKNOWN';
                     alt_code_rn        := '084984853';
                     alt_reason_rn      := 'INCOMING FROM PRIVATE SECTOR';
               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'),
                      pract_part_seq_id.NEXTVAL, 
                      reaction_seq_id.NEXTVAL 
                 INTO obs_date,
                      ppp_date,
                      pract_part_seq_id_val, 
                      reaction_seq_id_val
                 FROM DUAL;                            

               insert_practpart.insert_message( ppp_date, pract_part_seq_id_val );

               INSERT INTO reaction
                           (   REACTION_ID	
                              ,ALLERGY_ID	
                              ,AUTHOR_PPID	
                              ,OBSERVATION_TM	
                              ,REACTION_NULLFLV	
                              ,REACTION_VALSET_VUID	
                              ,REACTION_CONCEPT_VUID	
                              ,REACTION_DSGNTN_VUID	
                              ,REACTION_DSPLYTXT	
                              ,REACTION_ALT_CODSYS	
                              ,REACTION_ALT_CODE	
                              ,REACTION_ALT_DSPLYTXT	
                              ,REACTION_ALT_REASON	
                              ,LAST_UPDATED_DTTZ	
                           )
                    VALUES (  reaction_seq_id_val                                               -- REACTION_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , pract_part_seq_id_val                                             -- AUTHOR_PPID
                            , obs_date                                                          -- OBSERVATION_TM 
                            , NULL                                                              -- REACTION_NULLFLV
                            , reaction_cd_v                                                     -- REACTION_VALSET_VUID
                            , NULL                                                              -- REACTION_CONCEPT_VUID
                            , NULL                                                              -- REACTION_DSGNTN_VUID
                            , reaction_cd_d                                                     -- REACTION_DSPLYTXT
                            , alt_codsys_rn                                                     -- REACTION_ALT_CODSYS
                            , alt_code_rn                                                       -- REACTION_ALT_CODE
                            , alt_display_rn                                                    -- REACTION_ALT_DSPLYTXT
                            , alt_reason_rn                                                     -- REACTION_ALT_REASON
                            , SYSTIMESTAMP                                                      -- last_updated_dttz
                           );
            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;

               -- Null alternate values
               alt_codsys_di  := NULL;
               alt_display_di := NULL;
               alt_code_di    := NULL;
               alt_reason_di  := NULL;

               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
                     random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
                     IF random_num2 = 1
                     THEN
                         drging_cd_d    := NULL;
                         drging_cd_v    := '0004';
                         alt_codsys_di  := 'HL7';
                         alt_display_di := 'UNKNOWN';
                         alt_code_di    := '6489416418';
                         alt_reason_di  := 'INCOMING FROM PRIVATE SECTOR';
                     ELSE 
                         drging_cd_d    := 'SODIUM VALEPROATE';
                         drging_cd_v    := '98465';
                     END IF;
               END CASE;
   
               INSERT INTO DRUG_INGREDIENT
                           (   DRUG_INGREDIENT_ID	
                              ,ALLERGY_ID	
                              ,DRUG_INGREDIENT_VALSET_VUID	
                              ,DRUG_INGREDIENT_CONCEPT_VUID	
                              ,DRUG_INGREDIENT_DSGNTN_VUID	
                              ,DRUG_INGREDIENT_DSPLYTXT	
                              ,DRUG_INGREDIENT_ALT_CODSYS	
                              ,DRUG_INGREDIENT_ALT_CODE	
                              ,DRUG_INGREDIENT_ALT_DSPLYTXT	
                              ,DRUG_INGREDIENT_ALT_REASON	
                              ,LAST_UPDATED_DTTZ	
                           )
                    VALUES (  drug_ingredient_seq_id.NEXTVAL                                    -- DRUG_INGREDIENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drging_cd_v                                                       -- DRUG_INGREDIENT_VALSET_VUID
                            , NULL                                                              -- DRUG_INGREDIENT_CONCEPT_VUID 
                            , NULL                                                              -- DRUG_INGREDIENT_DSGNTN_VUID
                            , drging_cd_d                                                       -- DRUG_INGREDIENT_DSPLYTXT
                            , alt_codsys_di                                                     -- DRUG_INGREDIENT_ALT_CODSYS
                            , alt_code_di                                                       -- DRUG_INGREDIENT_ALT_CODE
                            , alt_display_di                                                    -- DRUG_INGREDIENT_ALT_DSPLYTXT
                            , alt_reason_di                                                     -- DRUG_INGREDIENT_ALT_REASON
                            , SYSTIMESTAMP                                                      -- last_updated_dttz
                           );

            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, 2))
            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;
               alt_reason_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';
                         alt_reason_dc  := 'INCOMING FROM PRIVATE SECTOR';
                     ELSE 
                         drgcls_cd_d    := 'SWELLING OF THE FINGERS';
                         drgcls_cd_v    := '546846';
                     END IF;
               END CASE;
     
               INSERT INTO DRUG_CLASS
                           (   DRUG_CLASS_ID	
                              ,ALLERGY_ID	
                              ,DRUG_CLASS_VALSET_VUID	
                              ,DRUG_CLASS_CONCEPT_VUID	
                              ,DRUG_CLASS_DSGNTN_VUID	
                              ,DRUG_CLASS_DSPLYTXT	
                              ,DRUG_CLASS_ALT_CODSYS	
                              ,DRUG_CLASS_ALT_CODE	
                              ,DRUG_CLASS_ALT_DSPLYTXT	
                              ,DRUG_CLASS_ALT_REASON	
                              ,LAST_UPDATED_DTTZ	
                           )
                    VALUES (  drug_CLASS_seq_id.NEXTVAL                                         -- DRUG_CLASS_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , drgcls_cd_v                                                       -- DRUG_CLASS_VALSET_VUID
                            , NULL                                                              -- DRUG_CLASS_CONCEPT_VUID 
                            , NULL                                                              -- DRUG_CLASS_DSGNTN_VUID
                            , drgcls_cd_d                                                       -- DRUG_CLASS_DSPLYTXT
                            , alt_codsys_dc                                                     -- DRUG_CLASS_ALT_CODSYS
                            , alt_code_dc                                                       -- DRUG_CLASS_ALT_CODE
                            , alt_display_dc                                                    -- DRUG_CLASS_ALT_DSPLYTXT
                            , alt_reason_dc                                                     -- DRUG_CLASS_ALT_REASON
                            , SYSTIMESTAMP                                                      -- last_updated_dttz
                           );

            END LOOP;          
         END IF;


         -- 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, 2))
            LOOP
            
               random_num := DBMS_RANDOM.VALUE (-5, -11);

               SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                      pract_part_seq_id.NEXTVAL 
                 INTO ppp_date,
                      pract_part_seq_id_val
                 FROM DUAL;                            

               insert_practpart.insert_message( ppp_date, pract_part_seq_id_val );

               INSERT INTO COMMENT_EVENT
                           (   COMMENT_EVENT_ID	
                              ,ALLERGY_ID	
                              ,AUTHOR_PPID	
                              ,COMMENT_TYP_VALSET_VUID	
                              ,COMMENT_TYP_CONCEPT_VUID	
                              ,COMMENT_TYP_DSGNTN_VUID	
                              ,COMMENT_TYP_DSPLYTXT	
                              ,COMMENT_TYP_ALT_CODSYS	
                              ,COMMENT_TYP_ALT_CODE	
                              ,COMMENT_TYP_ALT_DSPLYTXT	
                              ,COMMENT_TYP_ALT_REASON	
                              ,COMMENT_TM	
                              ,COMMENT_TXT	
                              ,LAST_UPDATED_DTTZ	
                           )
                    VALUES (  COMMENT_EVENT_seq_id.NEXTVAL                                           -- COMMENT_EVENT_ID
                            , allergy_seq_id.CURRVAL                                            -- ALLERGY_ID
                            , pract_part_seq_id_val                                             -- AUTHOR_PPID
                            , '23892398'                                                        -- COMMENT_TYP_VALSET_VUID
                            , NULL                                                              -- COMMENT_TYP_CONCEPT_VUID 
                            , NULL                                                              -- COMMENT_TYP_DSGNTN_VUID
                            , 'NORMAL TEXT'                                                     -- COMMENT_TYP_DSPLYTXT
                            , NULL                                                              -- COMMENT_TYP_ALT_CODSYS
                            , NULL                                                              -- COMMENT_TYP_ALT_CODE
                            , NULL                                                              -- COMMENT_TYP_ALT_DSPLYTXT
                            , NULL                                                              -- COMMENT_TYP_ALT_REASON
                            , ppp_date                                                          -- COMMENT_TM
                            , dbms_random.string ('U',trunc(dbms_random.value(1,400)))          -- COMMENT_TXT
                            , SYSTIMESTAMP                                                      -- last_updated_dttz
                           );

            END LOOP;          
         END IF;

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

           insert_practpart.insert_message( ppp_date, pract_part_seq_id_val );
           
           -- Null alternate values
           alt_codsys_st  := NULL;
           alt_display_st := NULL;
           alt_code_st    := NULL;
           alt_reason_st  := NULL;

           -- 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
                 random_num2 := round(DBMS_RANDOM.VALUE (1, 10));
                 IF random_num2 = 1
                 THEN
                     status_code_d      := NULL;
                     status_code_v      := '0003';
                     alt_codsys_st      := 'HL7';
                     alt_display_st     := 'UNKNOWN';
                     alt_code_st        := '8432165987';
                     alt_reason_st      := 'INCOMING FROM PRIVATE SECTOR';
                 ELSE 
                     status_code_d      := 'WAIT';
                     status_code_v      := '37';
                 END IF;
           END CASE;
           
           -- Set has_known_allergies
           random_num := round(DBMS_RANDOM.VALUE (1, 3));

           CASE random_num
              WHEN '1' THEN
                 known_alrgy_null := NULL;
                 known_alrgy_val  := 'T';
              WHEN '2' THEN
                 known_alrgy_null := NULL;
                 known_alrgy_val  := 'F';
              ELSE
                 known_alrgy_null := 'PATIENT UNRESPONSIVE';
                 known_alrgy_val  := NULL;
           END CASE;

           -- get Patient seq id
           SELECT patient_information_seq_id.NEXTVAL
             INTO patient_seq_id_val
             FROM DUAL;

         v_vpid := 'test_vpid_' || to_char(asmt + starting_patient_vpid);
         
           -- Load Patient Record
         INSERT INTO PATIENT_information (  PATIENT_information_ID, 	
                                name_PREFIX, 	
                                name_GIVEN, 	
                                name_NICKNAME, 	
                                name_FAMILY, 	
                                name_SUFFIX, 	
                                name_TITLE, 	
                                name_FORMATTED, 	
                                name_MIDDLE, 	
                                birthdate,
                                gender,
                                marital_status,
                                mother_maiden_name,
                                religion,                           
                                LAST_UPDATED_DTTZ
                              )
         VALUES (
                   patient_seq_id_val                                          -- PATIENT_ID
                 , NULL                                                        -- PREFIX_NM	
                 , NULL                                                        -- GIVEN_NM	
                 , NULL                                                        -- NICKNAME_NM	
                 , dbms_random.string ('U',trunc(dbms_random.value(1,40)))     -- FAMILY_NM	
                 , NULL                                                        -- SUFFIX_NM	
                 , NULL                                                        -- TITLE_NM	
                 , NULL                                                        -- FORMATTED_NM	
                 , NULL                                                        -- MIDDLE_NM	
                 , sysdate - (365*(dbms_random.value(30,100)))                 -- birthdate	
                 , 'MALE'                                                      -- gender	
                 , 'MARRIED'                                                   -- marital_status	
                 , dbms_random.string ('U',trunc(dbms_random.value(1,40)))     -- mother_maiden_name	
                 , null                                                        -- religion
                 , SYSTIMESTAMP                                                -- last_updated_dttz
                );
           
           INSERT INTO ALLERGY_ASSESSMENT
                      (   ALLERGY_ASSESSMENT_ID	
                         ,PATIENT_INFORMATION_ID	
                         ,AUTHOR_PPID	
                         , patient_source_id
                         , patient_namespace_id
                         ,HAS_KNOWN_ALLERGIES_NULLFLV	
                         ,HAS_KNOWN_ALLERGIES_VAL	
                         ,STATUS_VALSET_VUID	
                         ,STATUS_CONCEPT_VUID	
                         ,STATUS_DSGNTN_VUID	
                         ,STATUS_DSPLYTXT	
                         ,STATUS_ALT_CODSYS	
                         ,STATUS_ALT_CODE	
                         ,STATUS_ALT_DSPLYTXT	
                         ,STATUS_ALT_REASON	
                         ,IDENTITY_ENTITYID	
                         ,IDENTITY_NAMESPACEID	
                         ,IDENTITY_UNIVERSALID	
                         ,IDENTITY_UNIVERSALTYP	
                         ,RECORD_VERSION	
                         ,RECORD_UPDATE_TM	
                         ,LAST_UPDATED_DTTZ	
                      )
               VALUES (  allergy_assessment_seq_id.NEXTVAL                                      -- ALLERGY_ASSESSMENT_ID
                       , patient_seq_id_val                                                     -- PATIENT_INFORMATION_ID	
                       , pract_part_seq_id_val                                                  -- AUTHOR_PPID	
                       , v_vpid                                                                 -- patient_source_id
                       , 'DNS   -605'                                                           -- patient_namespace_id
                       , known_alrgy_null                                                       -- HAS_KNOWN_ALLERGIES_NULLFLV	
                       , known_alrgy_val                                                        -- HAS_KNOWN_ALLERGIES_VAL	
                       , status_code_v                                                          -- STATUS_VALSET_VUID	
                       , NULL                                                                   -- STATUS_CONCEPT_VUID	
                       , NULL                                                                   -- STATUS_DSGNTN_VUID	
                       , status_code_d                                                          -- STATUS_DSPLYTXT	
                       , alt_codsys_st                                                          -- STATUS_ALT_CODSYS	
                       , alt_code_st                                                            -- STATUS_ALT_CODE	
                       , alt_display_st                                                         -- STATUS_ALT_DSPLYTXT	
                       , alt_reason_st                                                          -- STATUS_ALT_REASON	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- IDENTITY_ENTITYID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- IDENTITY_NAMESPACEID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- IDENTITY_UNIVERSALID	
                       , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'||allergy_assessment_seq_id.currval -- IDENTITY_UNIVERSALTYP	
                       , 1                                                                      -- RECORD_VERSION	
                       , upd_date                                                               -- RECORD_UPDATE_TM	
                       , SYSTIMESTAMP                                                           -- LAST_UPDATED_DTTZ
                      );
       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;
/