/******************************************************************************
   NAME:       load_outppharm_load3x_arms.sql
   PURPOSE:    This script will create Outpatient Pharmacy records that map to 
               VHIM version 3.6  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        07/11/2007  M. Jacobsen      - Initial Release
******************************************************************************/
--set serveroutput on
DECLARE
   route                    PLS_INTEGER;
   outpat_med_dsp           PLS_INTEGER;
   counter                  PLS_INTEGER;
   random_num               NUMBER          := 0;
   random_numa              NUMBER          := 0;
   random_numd              NUMBER          := 0;
   random_numc              NUMBER          := 0;
   random_numf              NUMBER          := 0;
   random_numr              NUMBER          := 0;
   random_numv              NUMBER          := 0;
   random_num1              NUMBER          := 0;
   random_num2              NUMBER          := 0;
   random_num3              NUMBER          := 0;
   random_num4              NUMBER          := 0;
   pract_part_seq_id_val    NUMBER; 
   medicine_seq_id_val      NUMBER;
   patrptmed_seq_id_val     NUMBER;
   sdt_seq_id_val           NUMBER;
   pharmreq_seq_id_val      NUMBER;
   outpmed_seq_id_val       NUMBER;
   cmop_seq_id_val          NUMBER;
   benass_seq_id_val        NUMBER;
   medinst_seq_id_val       NUMBER;
   outpdsp_seq_id_val       NUMBER;
   patient_seq_id_val       NUMBER;
   physppid                 NUMBER;
   authorppid               NUMBER;
   cosignppid               NUMBER;
   dataentppid              NUMBER;
   attendppid               NUMBER;
   verifyppid               NUMBER;
   renewppid                NUMBER;
   finishppid               NUMBER;
   refillppid               NUMBER;
   pharmppid                NUMBER;
   orig_pid                 NUMBER;
   partial_pid              NUMBER;
   refill_pid               NUMBER;
   anc_cnt                  NUMBER;
   wbc_cnt                  NUMBER;
   pract_vpid               VARCHAR2 (30);
   ppa_date                 VARCHAR2 (30);
   ppc_date                 VARCHAR2 (30);
   ppd_date                 VARCHAR2 (30);
   ppf_date                 VARCHAR2 (30);
   ppr_date                 VARCHAR2 (30);
   ppv_date                 VARCHAR2 (30);
   rx_date                  VARCHAR2 (30);
   pp_date                  VARCHAR2 (30);
   start_date               VARCHAR2 (30);
   stop_date                VARCHAR2 (30);
   genord_date              VARCHAR2 (30);
   exp_date                 VARCHAR2 (30);
   lastdisp_date            VARCHAR2 (30);
   cancel_date              VARCHAR2 (30);
   recupd_date              VARCHAR2 (30);
   discont_date             VARCHAR2 (30);
   ship_date                VARCHAR2 (30);
   disp_date                VARCHAR2 (30);
   prescription_date        varchar2(30);
   release_date             VARCHAR2 (30);
   expl_date                VARCHAR2 (30);
   exph_date                VARCHAR2 (30);
   refill_date              VARCHAR2 (30);
   pharm_date               VARCHAR2 (30);
   alt_codsys_cmop          VARCHAR2 (50);
   alt_display_cmop         VARCHAR2 (100);
   alt_code_cmop            VARCHAR2 (30);
   alt_reason_cmop          VARCHAR2 (100);   
   cmop_code_d              VARCHAR2 (100);
   cmop_code_v              VARCHAR2 (30);
   alt_codsys_dosage        VARCHAR2 (50);
   alt_display_dosage       VARCHAR2 (100);
   alt_code_dosage          VARCHAR2 (30);
   alt_reason_dosage        VARCHAR2 (100);   
   dosage_code_d            VARCHAR2 (100);
   dosage_code_v            VARCHAR2 (30);
   alt_codsys_drugsc        VARCHAR2 (50);
   alt_display_drugsc       VARCHAR2 (100);
   alt_code_drugsc          VARCHAR2 (30);
   alt_reason_drugsc        VARCHAR2 (100);   
   drugsc_code_d            VARCHAR2 (100);
   drugsc_code_v            VARCHAR2 (30);
   alt_codsys_med           VARCHAR2 (50);
   alt_display_med          VARCHAR2 (100);
   alt_code_med             VARCHAR2 (30);
   alt_reason_med           VARCHAR2 (100);   
   med_code_d               VARCHAR2 (100);
   med_code_v               VARCHAR2 (30);
   alt_codsys_ndf           VARCHAR2 (50);
   alt_display_ndf          VARCHAR2 (100);
   alt_code_ndf             VARCHAR2 (30);
   alt_reason_ndf           VARCHAR2 (100);   
   ndf_code_d               VARCHAR2 (100);
   ndf_code_v               VARCHAR2 (30);
   alt_codsys_ndc           VARCHAR2 (50);
   alt_display_ndc          VARCHAR2 (100);
   alt_code_ndc             VARCHAR2 (30);
   alt_reason_ndc           VARCHAR2 (100);   
   ndc_code_d               VARCHAR2 (100);
   ndc_code_v               VARCHAR2 (30);
   alt_codsys_st            VARCHAR2 (50);
   alt_display_st           VARCHAR2 (100);
   alt_code_st              VARCHAR2 (30);
   alt_reason_st            VARCHAR2 (100);
   status_code_d            VARCHAR2 (100);
   status_code_v            VARCHAR2 (20);
   alt_codsys_totdosl       VARCHAR2 (50);
   alt_display_totdosl      VARCHAR2 (100);
   alt_code_totdosl         VARCHAR2 (30);
   alt_reason_totdosl       VARCHAR2 (100);   
   totdosl_code_d           VARCHAR2 (100);
   totdosl_code_v           VARCHAR2 (30);
   alt_codsys_totdosh       VARCHAR2 (50);
   alt_display_totdosh      VARCHAR2 (100);
   alt_code_totdosh         VARCHAR2 (30);
   alt_reason_totdosh       VARCHAR2 (100);   
   totdosh_code_d           VARCHAR2 (100);
   totdosh_code_v           VARCHAR2 (30);   
   alt_codsys_anc           VARCHAR2 (50);
   alt_display_anc          VARCHAR2 (100);
   alt_code_anc             VARCHAR2 (30);
   alt_reason_anc           VARCHAR2 (100);   
   anc_code_d               VARCHAR2 (100);
   anc_code_v               VARCHAR2 (30);
   anc_nullflv              VARCHAR2 (20);
   alt_codsys_wbc           VARCHAR2 (50);
   alt_display_wbc          VARCHAR2 (100);
   alt_code_wbc             VARCHAR2 (30);
   alt_reason_wbc           VARCHAR2 (100);   
   wbc_code_d               VARCHAR2 (100);
   wbc_code_v               VARCHAR2 (30);
   wbc_nullflv              VARCHAR2 (20);
   alt_codsys_route         VARCHAR2 (50);
   alt_display_route        VARCHAR2 (100);
   alt_code_route           VARCHAR2 (30);
   alt_reason_route         VARCHAR2 (100);   
   route_code_d             VARCHAR2 (100);
   route_code_v             VARCHAR2 (30);
   alt_codsys_prior         VARCHAR2 (50);
   alt_display_prior        VARCHAR2 (100);
   alt_code_prior           VARCHAR2 (30);
   alt_reason_prior         VARCHAR2 (100);   
   prior_code_d             VARCHAR2 (100);
   prior_code_v             VARCHAR2 (30);
   alt_codsys_ordloc        VARCHAR2 (50);
   alt_display_ordloc       VARCHAR2 (100);
   alt_code_ordloc          VARCHAR2 (30);
   alt_reason_ordloc        VARCHAR2 (100);   
   ordloc_code_d            VARCHAR2 (100);
   ordloc_code_v            VARCHAR2 (30);
   alt_codsys_ordinst       VARCHAR2 (50);
   alt_display_ordinst      VARCHAR2 (100);
   alt_code_ordinst         VARCHAR2 (30);
   alt_reason_ordinst       VARCHAR2 (100);   
   ordinst_code_d           VARCHAR2 (100);
   ordinst_code_v           VARCHAR2 (30);
   alt_codsys_orditm        VARCHAR2 (50);
   alt_display_orditm       VARCHAR2 (100);
   alt_code_orditm          VARCHAR2 (30);
   alt_reason_orditm        VARCHAR2 (100);   
   orditm_code_d            VARCHAR2 (100);
   orditm_code_v            VARCHAR2 (30);
   alt_codsys_pattyp        VARCHAR2 (50);
   alt_display_pattyp       VARCHAR2 (100);
   alt_code_pattyp          VARCHAR2 (30);
   alt_reason_pattyp        VARCHAR2 (100);   
   pattyp_code_d            VARCHAR2 (100);
   pattyp_code_v            VARCHAR2 (30);
   alt_codsys_copay         VARCHAR2 (50);
   alt_display_copay        VARCHAR2 (100);
   alt_code_copay           VARCHAR2 (30);
   alt_reason_copay         VARCHAR2 (100);   
   copay_code_d             VARCHAR2 (100);
   copay_code_v             VARCHAR2 (30);
   alt_codsys_doslow        VARCHAR2 (50);
   alt_display_doslow       VARCHAR2 (100);
   alt_code_doslow          VARCHAR2 (30);
   alt_reason_doslow        VARCHAR2 (100);   
   doslow_code_d            VARCHAR2 (100);
   doslow_code_v            VARCHAR2 (30);
   alt_codsys_doshgh        VARCHAR2 (50);
   alt_display_doshgh       VARCHAR2 (100);
   alt_code_doshgh          VARCHAR2 (30);
   alt_reason_doshgh        VARCHAR2 (100);   
   doshgh_code_d            VARCHAR2 (100);
   doshgh_code_v            VARCHAR2 (30);
   alt_codsys_bencat        VARCHAR2 (50);
   alt_display_bencat       VARCHAR2 (100);
   alt_code_bencat          VARCHAR2 (30);
   alt_reason_bencat        VARCHAR2 (100);   
   bencat_code_d            VARCHAR2 (100);
   bencat_code_v            VARCHAR2 (30);
   alt_codsys_dispql        VARCHAR2 (50);
   alt_display_dispql       VARCHAR2 (100);
   alt_code_dispql          VARCHAR2 (30);
   alt_reason_dispql        VARCHAR2 (100);   
   dispql_code_d            VARCHAR2 (100);
   dispql_code_v            VARCHAR2 (30);
   alt_codsys_dispqh        VARCHAR2 (50);
   alt_display_dispqh       VARCHAR2 (100);
   alt_code_dispqh          VARCHAR2 (30);
   alt_reason_dispqh        VARCHAR2 (100);   
   dispqh_code_d            VARCHAR2 (100);
   dispqh_code_v            VARCHAR2 (30);
   alt_codsys_dosef         VARCHAR2 (50);
   alt_display_dosef        VARCHAR2 (100);
   alt_code_dosef           VARCHAR2 (30);
   alt_reason_dosef         VARCHAR2 (100);   
   dosef_code_d             VARCHAR2 (100);
   dosef_code_v             VARCHAR2 (30);
   alt_codsys_dosql         VARCHAR2 (50);
   alt_display_dosql        VARCHAR2 (100);
   alt_code_dosql           VARCHAR2 (30);
   alt_reason_dosql         VARCHAR2 (100);   
   dosql_code_d             VARCHAR2 (100);
   dosql_code_v             VARCHAR2 (30);
   alt_codsys_dosqh         VARCHAR2 (50);
   alt_display_dosqh        VARCHAR2 (100);
   alt_code_dosqh           VARCHAR2 (30);
   alt_reason_dosqh         VARCHAR2 (100);   
   dosqh_code_d             VARCHAR2 (100);
   dosqh_code_v             VARCHAR2 (30);
   alt_codsys_qtydis        VARCHAR2 (50);
   alt_display_qtydis       VARCHAR2 (100);
   alt_code_qtydis          VARCHAR2 (30);
   alt_reason_qtydis        VARCHAR2 (100);   
   qtydis_code_d            VARCHAR2 (100);
   qtydis_code_v            VARCHAR2 (30);
   alt_codsys_lvlund        VARCHAR2 (50);
   alt_display_lvlund       VARCHAR2 (100);
   alt_code_lvlund          VARCHAR2 (30);
   alt_reason_lvlund        VARCHAR2 (100);   
   lvlund_code_d            VARCHAR2 (100);
   lvlund_code_v            VARCHAR2 (30);
   alt_codsys_mailw         VARCHAR2 (50);
   alt_display_mailw        VARCHAR2 (100);
   alt_code_mailw           VARCHAR2 (30);
   alt_reason_mailw         VARCHAR2 (100);   
   mailw_code_d             VARCHAR2 (100);
   mailw_code_v             VARCHAR2 (30);
   alt_codsys_ncpdp         VARCHAR2 (50);
   alt_display_ncpdp        VARCHAR2 (100);
   alt_code_ncpdp           VARCHAR2 (30);
   alt_reason_ncpdp         VARCHAR2 (100);   
   ncpdp_code_d             VARCHAR2 (100);
   ncpdp_code_v             VARCHAR2 (30);   
   starting_patient_vpid    NUMBER;
   v_vpid                   VARCHAR2 (30);
   fixed_date			DATE;
   
BEGIN

   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'Outpatient Pharmacy Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));
   fixed_date := TO_DATE('07-04-07 17:07:06','MM-DD-YY HH24:MI:SS');

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

--loop thru x times
   FOR z IN 1 .. &1
   LOOP
       -- Loop thru how many patients to assign outpatient pharmacy to
       FOR jj in 1 .. &2
	   LOOP

         -- Null medicine alternate values
         alt_codsys_cmop      := NULL;
         alt_display_cmop     := NULL;
         alt_code_cmop        := NULL;
         alt_reason_cmop      := NULL;  
         alt_codsys_dosage    := NULL;
         alt_display_dosage   := NULL;
         alt_code_dosage      := NULL;
         alt_reason_dosage    := NULL; 
         alt_codsys_drugsc    := NULL;
         alt_display_drugsc   := NULL;
         alt_code_drugsc      := NULL;
         alt_reason_drugsc    := NULL;   
         alt_codsys_med       := NULL;
         alt_display_med      := NULL;
         alt_code_med         := NULL;
         alt_reason_med       := NULL;  
         alt_codsys_ndf       := NULL;
         alt_display_ndf      := NULL;
         alt_code_ndf         := NULL;
         alt_reason_ndf       := NULL;   
         alt_codsys_ndc       := NULL;
         alt_display_ndc      := NULL;
         alt_code_ndc         := NULL;
         alt_reason_ndc       := NULL;
         
         -- Set cmop
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               cmop_code_d      := NULL;
               cmop_code_v      := NULL;
               alt_codsys_cmop  := 'HL7';
               alt_display_cmop := 'UNKNOWN';
               alt_code_cmop    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_cmop  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               cmop_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               cmop_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;

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

         CASE random_num
            WHEN '1' THEN
               dosage_code_d      := NULL;
               dosage_code_v      := NULL;
               alt_codsys_dosage  := 'HL7';
               alt_display_dosage := 'UNKNOWN';
               alt_code_dosage    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_dosage  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               dosage_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               dosage_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;

         -- Set drug schedule
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               drugsc_code_d      := NULL;
               drugsc_code_v      := NULL;
               alt_codsys_drugsc  := 'HL7';
               alt_display_drugsc := 'UNKNOWN';
               alt_code_drugsc    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_drugsc  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               drugsc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               drugsc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;

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

         CASE random_num
            WHEN '1' THEN
               med_code_d      := NULL;
               med_code_v      := NULL;
               alt_codsys_med  := 'HL7';
               alt_display_med := 'UNKNOWN';
               alt_code_med    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_med  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               med_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               med_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;

         -- Set national drug file
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               ndf_code_d      := NULL;
               ndf_code_v      := NULL;
               alt_codsys_ndf  := 'HL7';
               alt_display_ndf := 'UNKNOWN';
               alt_code_ndf    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_ndf  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               ndf_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               ndf_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;

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

         CASE random_num
            WHEN '1' THEN
               ndc_code_d      := NULL;
               ndc_code_v      := NULL;
               alt_codsys_ndc  := 'HL7';
               alt_display_ndc := 'UNKNOWN';
               alt_code_ndc    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_ndc  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               ndc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               ndc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;
         
         SELECT medicine_seq_id.NEXTVAL
           INTO medicine_seq_id_val
           FROM DUAL;
         
         -- insert record into medicine table
         INSERT INTO medicine
                     (    MEDICINE_ID	
                        , CMOPDRUG_VALSET_VUID	
                        , CMOPDRUG_CONCEPT_VUID	
                        , CMOPDRUG_DSGNTN_VUID	
                        , CMOPDRUG_DSPLYTXT	
                        , CMOPDRUG_ALT_CODSYS	
                        , CMOPDRUG_ALT_CODE	
                        , CMOPDRUG_ALT_DSPLYTXT	
                        , CMOPDRUG_ALT_REASON	
                        , DOSAGEFORM_VALSET_VUID	
                        , DOSAGEFORM_CONCEPT_VUID	
                        , DOSAGEFORM_DSGNTN_VUID	
                        , DOSAGEFORM_DSPLYTXT	
                        , DOSAGEFORM_ALT_CODSYS	
                        , DOSAGEFORM_ALT_CODE	
                        , DOSAGEFORM_ALT_DSPLYTXT	
                        , DOSAGEFORM_ALT_REASON	
                        , DRUGSCHEDULE_VALSET_VUID	
                        , DRUGSCHEDULE_CONCEPT_VUID	
                        , DRUGSCHEDULE_DSGNTN_VUID	
                        , DRUGSCHEDULE_DSPLYTXT	
                        , DRUGSCHEDULE_ALT_CODSYS	
                        , DRUGSCHEDULE_ALT_CODE	
                        , DRUGSCHEDULE_ALT_DSPLYTXT	
                        , DRUGSCHEDULE_ALT_REASON	
                        , GENERIC_NAME	
                        , MEDICATION_VALSET_VUID	
                        , MEDICATION_CONCEPT_VUID	
                        , MEDICATION_DSGNTN_VUID	
                        , MEDICATION_DSPLYTXT	
                        , MEDICATION_ALT_CODSYS	
                        , MEDICATION_ALT_CODE	
                        , MEDICATION_ALT_DSPLYTXT	
                        , MEDICATION_ALT_REASON	
                        , NATIONALDRUGFILE_VALSET_VUID	
                        , NATIONALDRUGFILE_CONCEPT_VUID	
                        , NATIONALDRUGFILE_DSGNTN_VUID	
                        , NATIONALDRUGFILE_DSPLYTXT	
                        , NATIONALDRUGFILE_ALT_CODSYS	
                        , NATIONALDRUGFILE_ALT_CODE	
                        , NATIONALDRUGFILE_ALT_DSPLYTXT	
                        , NATIONALDRUGFILE_ALT_REASON	
                        , NDC_VALSET_VUID	
                        , NDC_CONCEPT_VUID	
                        , NDC_DSGNTN_VUID	
                        , NDC_DSPLYTXT	
                        , NDC_ALT_CODSYS	
                        , NDC_ALT_CODE	
                        , NDC_ALT_DSPLYTXT	
                        , NDC_ALT_REASON
                        , TRADE_NAME_VALSET_VUID	
                        , TRADE_NAME_CONCEPT_VUID	
                        , TRADE_NAME_DSGNTN_VUID	
                        , TRADE_NAME_DSPLYTXT	
                        , TRADE_NAME_ALT_CODSYS	
                        , TRADE_NAME_ALT_CODE	
                        , TRADE_NAME_ALT_DSPLYTXT	
                        , TRADE_NAME_ALT_REASON	
                        , LAST_UPDATED_DTTZ	
                     )
              VALUES (   
                         medicine_seq_id_val                                                    -- MEDICINE_ID	
                       , cmop_code_v                                                            -- CMOPDRUG_VALSET_VUID	
                       , NULL                                                                   -- CMOPDRUG_CONCEPT_VUID	
                       , NULL                                                                   -- CMOPDRUG_DSGNTN_VUID	
                       , cmop_code_d                                                            -- CMOPDRUG_DSPLYTXT	
                       , alt_codsys_cmop                                                        -- CMOPDRUG_ALT_CODSYS	
                       , alt_code_cmop                                                          -- CMOPDRUG_ALT_CODE	
                       , alt_display_cmop                                                       -- CMOPDRUG_ALT_DSPLYTXT	
                       , alt_reason_cmop                                                        -- CMOPDRUG_ALT_REASON	
                       , dosage_code_v                                                          -- DOSAGEFORM_VALSET_VUID	
                       , NULL                                                                   -- DOSAGEFORM_CONCEPT_VUID	
                       , NULL                                                                   -- DOSAGEFORM_DSGNTN_VUID	
                       , dosage_code_d                                                          -- DOSAGEFORM_DSPLYTXT	
                       , alt_codsys_dosage                                                      -- DOSAGEFORM_ALT_CODSYS	
                       , alt_code_dosage                                                        -- DOSAGEFORM_ALT_CODE	
                       , alt_display_dosage                                                     -- DOSAGEFORM_ALT_DSPLYTXT	
                       , alt_reason_dosage                                                      -- DOSAGEFORM_ALT_REASON	
                       , drugsc_code_v                                                          -- DRUGSCHEDULE_VALSET_VUID	
                       , NULL                                                                   -- DRUGSCHEDULE_CONCEPT_VUID	
                       , NULL                                                                   -- DRUGSCHEDULE_DSGNTN_VUID	
                       , drugsc_code_d                                                          -- DRUGSCHEDULE_DSPLYTXT	
                       , alt_codsys_drugsc                                                      -- DRUGSCHEDULE_ALT_CODSYS	
                       , alt_code_drugsc                                                        -- DRUGSCHEDULE_ALT_CODE	
                       , alt_display_drugsc                                                     -- DRUGSCHEDULE_ALT_DSPLYTXT	
                       , alt_reason_drugsc                                                      -- DRUGSCHEDULE_ALT_REASON	
                       , dbms_random.string ('U',trunc(dbms_random.value(1,100)))               -- GENERIC_NAME	
                       , med_code_v                                                             -- MEDICATION_VALSET_VUID	
                       , NULL                                                                   -- MEDICATION_CONCEPT_VUID	
                       , NULL                                                                   -- MEDICATION_DSGNTN_VUID	
                       , med_code_d                                                             -- MEDICATION_DSPLYTXT	
                       , alt_codsys_med                                                         -- MEDICATION_ALT_CODSYS	
                       , alt_code_med                                                           -- MEDICATION_ALT_CODE	
                       , alt_display_med                                                        -- MEDICATION_ALT_DSPLYTXT	
                       , alt_reason_med                                                         -- MEDICATION_ALT_REASON	
                       , ndf_code_v                                                             -- NATIONALDRUGFILE_VALSET_VUID	
                       , NULL                                                                   -- NATIONALDRUGFILE_CONCEPT_VUID	
                       , NULL                                                                   -- NATIONALDRUGFILE_DSGNTN_VUID	
                       , ndf_code_d                                                             -- NATIONALDRUGFILE_DSPLYTXT	
                       , alt_codsys_ndf                                                         -- NATIONALDRUGFILE_ALT_CODSYS	
                       , alt_code_ndf                                                           -- NATIONALDRUGFILE_ALT_CODE	
                       , alt_display_ndf                                                        -- NATIONALDRUGFILE_ALT_DSPLYTXT	
                       , alt_reason_ndf                                                         -- NATIONALDRUGFILE_ALT_REASON	
                       , ndc_code_v                                                             -- NDC_VALSET_VUID	
                       , NULL                                                                   -- NDC_CONCEPT_VUID	
                       , NULL                                                                   -- NDC_DSGNTN_VUID	
                       , ndc_code_d                                                             -- NDC_DSPLYTXT	
                       , alt_codsys_ndc                                                         -- NDC_ALT_CODSYS	
                       , alt_code_ndc                                                           -- NDC_ALT_CODE	
                       , alt_display_ndc                                                        -- NDC_ALT_DSPLYTXT	
                       , alt_reason_ndc                                                         -- NDC_ALT_REASON	
                       , '84686461'                                                             -- TRADE_NAME_VALSET_VUID	
                       , NULL                                                                   -- TRADE_NAME_CONCEPT_VUID	
                       , NULL                                                                   -- TRADE_NAME_DSGNTN_VUID	
                       , dbms_random.string ('U',trunc(dbms_random.value(1,100)))               -- TRADE_NAME_DSPLYTXT	
                       , NULL                                                                   -- TRADE_NAME_ALT_CODSYS	
                       , NULL                                                                   -- TRADE_NAME_ALT_CODE	
                       , NULL                                                                   -- TRADE_NAME_ALT_DSPLYTXT	
                       , NULL                                                                   -- TRADE_NAME_ALT_REASON	
                       , SYSTIMESTAMP                                                           -- LAST_UPDATED_DTTZ
                     );

         -- Set path
         route := round(DBMS_RANDOM.VALUE (1, 3));

         CASE route
         WHEN '1' THEN
            -- go by way of special drug tracking
            pharmreq_seq_id_val := NULL;
            
            -- Null alternate values
            alt_codsys_st       := NULL;
            alt_display_st      := NULL;
            alt_code_st         := NULL;
            alt_reason_st       := NULL;
            alt_codsys_totdosl  := NULL;
            alt_display_totdosl := NULL;
            alt_code_totdosl    := NULL;
            alt_reason_totdosl  := NULL;
            alt_codsys_totdosh  := NULL;
            alt_display_totdosh := NULL;
            alt_code_totdosh    := NULL;
            alt_reason_totdosh  := NULL;
            alt_codsys_anc      := NULL;
            alt_display_anc     := NULL;
            alt_code_anc        := NULL;
            alt_reason_anc      := NULL;
            alt_codsys_wbc      := NULL;
            alt_display_wbc     := NULL;
            alt_code_wbc        := NULL;
            alt_reason_wbc      := 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      := NULL;
                   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 total dosage low values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  totdosl_code_d      := NULL;
                  totdosl_code_v      := NULL;
                  alt_codsys_totdosl  := 'HL7';
                  alt_display_totdosl := 'UNKNOWN';
                  alt_code_totdosl    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_totdosl  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  totdosl_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  totdosl_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set total dosage high values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  totdosh_code_d      := NULL;
                  totdosh_code_v      := NULL;
                  alt_codsys_totdosh  := 'HL7';
                  alt_display_totdosh := 'UNKNOWN';
                  alt_code_totdosh    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_totdosh  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  totdosh_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  totdosh_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set ancobs values
            anc_nullflv := NULL;
            anc_cnt     := round(dbms_random.value (400,2000));
            random_num  := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  anc_code_d      := NULL;
                  anc_code_v      := NULL;
                  alt_codsys_anc  := 'HL7';
                  alt_display_anc := 'UNKNOWN';
                  alt_code_anc    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_anc  := 'INCOMING FROM PRIVATE SECTOR';
               WHEN '2' THEN
                  anc_nullflv     := 'NO REASON GIVEN';
                  anc_cnt         := NULL;
                  anc_code_d      := NULL;
                  anc_code_v      := NULL;
                  alt_codsys_anc  := NULL;
                  alt_display_anc := NULL;
                  alt_code_anc    := NULL;
                  alt_reason_anc  := NULL;
               ELSE
                  anc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  anc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
    
            -- set wbcobs values
            wbc_nullflv := NULL;
            wbc_cnt     := round(dbms_random.value (3000,11000));
            random_num  := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  wbc_code_d      := NULL;
                  wbc_code_v      := NULL;
                  alt_codsys_wbc  := 'HL7';
                  alt_display_wbc := 'UNKNOWN';
                  alt_code_wbc    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_wbc  := 'INCOMING FROM PRIVATE SECTOR';
               WHEN '2' THEN
                  wbc_nullflv     := 'NO REASON GIVEN';
                  wbc_cnt         := NULL;
                  wbc_code_d      := NULL;
                  wbc_code_v      := NULL;
                  alt_codsys_wbc  := NULL;
                  alt_display_wbc := NULL;
                  alt_code_wbc    := NULL;
                  alt_reason_wbc  := NULL;
               ELSE
                  wbc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  wbc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
 
            -- get last rx date           
            random_num  := DBMS_RANDOM.VALUE (-20, -300);
            random_num2 := DBMS_RANDOM.VALUE (-1, -25);
  
            SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                   TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss')
              INTO rx_date,
                   pp_date
              FROM DUAL;

            -- get responsible physician practitioner
            random_num := ROUND (DBMS_RANDOM.VALUE (1, 4));

            IF random_num = 1
            THEN
                physppid := NULL;
            ELSE
                SELECT pract_part_seq_id.NEXTVAL
                  INTO physppid
                  FROM DUAL;
                
                insert_practpart.insert_message( pp_date, physppid );
            END IF;
            
            -- get special drug tracking seq id
            SELECT special_drug_tracking_seq_id.NEXTVAL
              INTO sdt_seq_id_val
              FROM DUAL;
            
            -- insert into special drug tracking table
            INSERT INTO SPECIAL_DRUG_TRACKING (
                                                  SPECIAL_DRUG_TRACKING_ID	
                                                , MEDICINE_ID	
                                                , RESPONSIBLE_PHYS_PPID	
                                                , STATUS_VALSET_VUID	
                                                , STATUS_CONCEPT_VUID	
                                                , STATUS_DSGNTN_VUID	
                                                , STATUS_DSPLYTXT	
                                                , STATUS_ALT_CODSYS	
                                                , STATUS_ALT_CODE	
                                                , STATUS_ALT_DSPLYTXT	
                                                , STATUS_ALT_REASON	
                                                , LAST_RX_DT	
                                                , TOTDOSAGE_LOW_VAL	
                                                , TOTDOSAGE_LOWUOM_VALSET_VUID	
                                                , TOTDOSAGE_LOWUOM_CONCEPT_VUID	
                                                , TOTDOSAGE_LOWUOM_DSGNTN_VUID	
                                                , TOTDOSAGE_LOWUOM_DSPLYTXT	
                                                , TOTDOSAGE_LOWUOM_ALT_CODSYS	
                                                , TOTDOSAGE_LOWUOM_ALT_CODE	
                                                , TOTDOSAGE_LOWUOM_ALT_DSPLYTXT	
                                                , TOTDOSAGE_LOWUOM_ALT_REASON	
                                                , TOTDOSAGE_HGH_VAL	
                                                , TOTDOSAGE_HGHUOM_VALSET_VUID	
                                                , TOTDOSAGE_HGHUOM_CONCEPT_VUID	
                                                , TOTDOSAGE_HGHUOM_DSGNTN_VUID	
                                                , TOTDOSAGE_HGHUOM_DSPLYTXT	
                                                , TOTDOSAGE_HGHUOM_ALT_CODSYS	
                                                , TOTDOSAGE_HGHUOM_ALT_CODE	
                                                , TOTDOSAGE_HGHUOM_ALT_DSPLYTXT	
                                                , TOTDOSAGE_HGHUOM_ALT_REASON	
                                                , ANC_OBS_CNT_NULLFLV	
                                                , ANC_OBS_CNT_VAL	
                                                , ANC_OBS_CNTUOM_VALSET_VUID	
                                                , ANC_OBS_CNTUOM_CONCEPT_VUID	
                                                , ANC_OBS_CNTUOM_DSGNTN_VUID	
                                                , ANC_OBS_CNTUOM_DSPLYTXT	
                                                , ANC_OBS_CNTUOM_ALT_CODSYS	
                                                , ANC_OBS_CNTUOM_ALT_CODE	
                                                , ANC_OBS_CNTUOM_ALT_DSPLYTXT	
                                                , ANC_OBS_CNTUOM_ALT_REASON	
                                                , WBC_OBS_CNT_NULLFLV	
                                                , WBC_OBS_CNT_VAL	
                                                , WBC_OBS_CNTUOM_VALSET_VUID	
                                                , WBC_OBS_CNTUOM_CONCEPT_VUID	
                                                , WBC_OBS_CNTUOM_DSGNTN_VUID	
                                                , WBC_OBS_CNTUOM_DSPLYTXT	
                                                , WBC_OBS_CNTUOM_ALT_CODSYS	
                                                , WBC_OBS_CNTUOM_ALT_CODE	
                                                , WBC_OBS_CNTUOM_ALT_DSPLYTXT	
                                                , WBC_OBS_CNTUOM_ALT_REASON	
                                                , WBC_OBS_DT	
                                                , LAST_UPDATED_DTTZ	
                                              )
                                       VALUES (
                                                  sdt_seq_id_val                            -- SPECIAL_DRUG_TRACKING_ID	
                                                , medicine_seq_id_val                       -- MEDICINE_ID	
                                                , physppid                                  -- RESPONSIBLE_PHYS_PPID	
                                                , 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	
                                                , rx_date                                   -- LAST_RX_DT	
                                                , ROUND(DBMS_RANDOM.VALUE (1,100))          -- TOTDOSAGE_LOW_VAL	
                                                , totdosl_code_v                            -- TOTDOSAGE_LOWUOM_VALSET_VUID	
                                                , NULL                                      -- TOTDOSAGE_LOWUOM_CONCEPT_VUID	
                                                , NULL                                      -- TOTDOSAGE_LOWUOM_DSGNTN_VUID	
                                                , totdosl_code_d                            -- TOTDOSAGE_LOWUOM_DSPLYTXT	
                                                , alt_codsys_totdosl                        -- TOTDOSAGE_LOWUOM_ALT_CODSYS	
                                                , alt_code_totdosl                          -- TOTDOSAGE_LOWUOM_ALT_CODE	
                                                , alt_display_totdosl                       -- TOTDOSAGE_LOWUOM_ALT_DSPLYTXT	
                                                , alt_reason_totdosl                        -- TOTDOSAGE_LOWUOM_ALT_REASON	
                                                , ROUND(DBMS_RANDOM.VALUE (200,1000))       -- TOTDOSAGE_HGH_VAL	
                                                , totdosh_code_v                            -- TOTDOSAGE_HGHUOM_VALSET_VUID	
                                                , NULL                                      -- TOTDOSAGE_HGHUOM_CONCEPT_VUID	
                                                , NULL                                      -- TOTDOSAGE_HGHUOM_DSGNTN_VUID	
                                                , totdosh_code_d                            -- TOTDOSAGE_HGHUOM_DSPLYTXT	
                                                , alt_codsys_totdosh                        -- TOTDOSAGE_HGHUOM_ALT_CODSYS	
                                                , alt_code_totdosh                          -- TOTDOSAGE_HGHUOM_ALT_CODE	
                                                , alt_code_totdosh                          -- TOTDOSAGE_HGHUOM_ALT_DSPLYTXT	
                                                , alt_reason_totdosh                        -- TOTDOSAGE_HGHUOM_ALT_REASON	
                                                , anc_nullflv                               -- ANC_OBS_CNT_NULLFLV	
                                                , anc_cnt                                   -- ANC_OBS_CNT_VAL	
                                                , anc_code_v                                -- ANC_OBS_CNTUOM_VALSET_VUID	
                                                , NULL                                      -- ANC_OBS_CNTUOM_CONCEPT_VUID	
                                                , NULL                                      -- ANC_OBS_CNTUOM_DSGNTN_VUID	
                                                , anc_code_d                                -- ANC_OBS_CNTUOM_DSPLYTXT	
                                                , alt_codsys_anc                            -- ANC_OBS_CNTUOM_ALT_CODSYS	
                                                , alt_code_anc                              -- ANC_OBS_CNTUOM_ALT_CODE	
                                                , alt_display_anc                           -- ANC_OBS_CNTUOM_ALT_DSPLYTXT	
                                                , alt_reason_anc                            -- ANC_OBS_CNTUOM_ALT_REASON	
                                                , wbc_nullflv                               -- WBC_OBS_CNT_NULLFLV	
                                                , wbc_cnt                                   -- WBC_OBS_CNT_VAL	
                                                , wbc_code_v                                -- WBC_OBS_CNTUOM_VALSET_VUID	
                                                , NULL                                      -- WBC_OBS_CNTUOM_CONCEPT_VUID	
                                                , NULL                                      -- WBC_OBS_CNTUOM_DSGNTN_VUID	
                                                , wbc_code_d                                -- WBC_OBS_CNTUOM_DSPLYTXT	
                                                , alt_codsys_wbc                            -- WBC_OBS_CNTUOM_ALT_CODSYS	
                                                , alt_code_wbc                              -- WBC_OBS_CNTUOM_ALT_CODE	
                                                , alt_display_wbc                           -- WBC_OBS_CNTUOM_ALT_DSPLYTXT	
                                                , alt_reason_wbc                            -- WBC_OBS_CNTUOM_ALT_REASON	
                                                , pp_date                                   -- WBC_OBS_DT	
                                                , SYSTIMESTAMP                              -- LAST_UPDATED_DTTZ	
                                              );
         WHEN '2' THEN
            -- go by way of pharmacy request
            sdt_seq_id_val      := NULL;
            
            -- Null alternate values
            alt_codsys_st       := NULL;
            alt_display_st      := NULL;
            alt_code_st         := NULL;
            alt_reason_st       := NULL;
            alt_codsys_route    := NULL;
            alt_display_route   := NULL;
            alt_code_route      := NULL;
            alt_reason_route    := NULL;
            alt_codsys_prior    := NULL;
            alt_display_prior   := NULL;
            alt_code_prior      := NULL;
            alt_reason_prior    := NULL;
            alt_codsys_ordloc   := NULL;
            alt_display_ordloc  := NULL;
            alt_code_ordloc     := NULL;
            alt_reason_ordloc   := NULL;
            alt_codsys_ordinst  := NULL;
            alt_display_ordinst := NULL;
            alt_code_ordinst    := NULL;
            alt_reason_ordinst  := NULL;
            alt_codsys_orditm   := NULL;
            alt_display_orditm  := NULL;
            alt_code_orditm     := NULL;
            alt_reason_orditm   := 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      := NULL;
                   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 route values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  route_code_d      := NULL;
                  route_code_v      := NULL;
                  alt_codsys_route  := 'HL7';
                  alt_display_route := 'UNKNOWN';
                  alt_code_route    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_route  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  route_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  route_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set priority values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  prior_code_d      := NULL;
                  prior_code_v      := NULL;
                  alt_codsys_prior  := 'HL7';
                  alt_display_prior := 'UNKNOWN';
                  alt_code_prior    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_prior  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  prior_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  prior_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set order location values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  ordloc_code_d      := NULL;
                  ordloc_code_v      := NULL;
                  alt_codsys_ordloc  := 'HL7';
                  alt_display_ordloc := 'UNKNOWN';
                  alt_code_ordloc    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_ordloc  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  ordloc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  ordloc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set order institution values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  ordinst_code_d      := NULL;
                  ordinst_code_v      := NULL;
                  alt_codsys_ordinst  := 'HL7';
                  alt_display_ordinst := 'UNKNOWN';
                  alt_code_ordinst    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_ordinst  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  ordinst_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  ordinst_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- set ordered item values
            random_num := round(DBMS_RANDOM.VALUE (1, 100));

            CASE random_num
               WHEN '1' THEN
                  orditm_code_d      := NULL;
                  orditm_code_v      := NULL;
                  alt_codsys_orditm  := 'HL7';
                  alt_display_orditm := 'UNKNOWN';
                  alt_code_orditm    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                  alt_reason_orditm  := 'INCOMING FROM PRIVATE SECTOR';
               ELSE
                  orditm_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                  orditm_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
            END CASE;            
            
            -- get dates           
            random_num  := DBMS_RANDOM.VALUE (-10, -12);
            random_num2 := DBMS_RANDOM.VALUE (-8, -9);
            random_num3 := DBMS_RANDOM.VALUE (-1, -25);
            random_numa := DBMS_RANDOM.VALUE (-19, -25);
            random_numc := DBMS_RANDOM.VALUE (-18, -27);

            SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                   TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                   TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                   TO_CHAR (fixed_date + random_numa, 'yyyymmddhh24miss'),
                   TO_CHAR (fixed_date + random_numc, 'yyyymmddhh24miss')
              INTO start_date,
                   stop_date,
                   genord_date,
                   ppa_date,
                   ppc_date
              FROM DUAL;

            -- get cosigner practitioner
            random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

            IF random_num = 1
            THEN
                cosignppid := NULL;
            ELSE
                SELECT pract_part_seq_id.NEXTVAL
                  INTO cosignppid
                  FROM DUAL;
                
                insert_practpart.insert_message( ppc_date, cosignppid );
            END IF;
            
            -- get author practitioner
            SELECT pract_part_seq_id.NEXTVAL
              INTO authorppid
              FROM DUAL;
            
            insert_practpart.insert_message( ppa_date, authorppid );
            
            -- get pharmacy request seq id
            SELECT pharmacy_request_seq_id.NEXTVAL
              INTO pharmreq_seq_id_val
              FROM DUAL;
            
            -- insert into pharmacy_request table
            INSERT INTO PHARMACY_REQUEST (
                                             PHARMACY_REQUEST_ID	
                                           , MEDICINE_ID	
                                           , COSIGNER_PPID	
                                           , GENERIC_AUTHOR_PPID	
                                           , ROUTE_VALSET_VUID	
                                           , ROUTE_CONCEPT_VUID	
                                           , ROUTE_DSGNTN_VUID	
                                           , ROUTE_DSPLYTXT	
                                           , ROUTE_ALT_CODSYS	
                                           , ROUTE_ALT_CODE	
                                           , ROUTE_ALT_DSPLYTXT	
                                           , ROUTE_ALT_REASON	
                                           , START_DT	
                                           , STOP_DT	
                                           , SCHEDULED_UNITS	
                                           , GENERIC_ORDER_DT	
                                           , GENERIC_PLACER_ID_ENTITYID	
                                           , GENERIC_PLACER_ID_NAMESPACEID	
                                           , GENERIC_PLACER_ID_UNIVERSALID	
                                           , GENERIC_PLACER_ID_UNIVERSALTYP	
                                           , GENERIC_PRIORITY_VALSET_VUID	
                                           , GENERIC_PRIORITY_CONCEPT_VUID	
                                           , GENERIC_PRIORITY_DSGNTN_VUID	
                                           , GENERIC_PRIORITY_DSPLYTXT	
                                           , GENERIC_PRIORITY_ALT_CODSYS	
                                           , GENERIC_PRIORITY_ALT_CODE	
                                           , GENERIC_PRIORITY_ALT_DSPLYTXT	
                                           , GENERIC_PRIORITY_ALT_REASON	
                                           , GENERIC_STATUS_VALSET_VUID	
                                           , GENERIC_STATUS_CONCEPT_VUID	
                                           , GENERIC_STATUS_DSGNTN_VUID	
                                           , GENERIC_STATUS_DSPLYTXT	
                                           , GENERIC_STATUS_ALT_CODSYS	
                                           , GENERIC_STATUS_ALT_CODE	
                                           , GENERIC_STATUS_ALT_DSPLYTXT	
                                           , GENERIC_STATUS_ALT_REASON	
                                           , ORD_LOCATION_VALSET_VUID	
                                           , ORD_LOCATION_CONCEPT_VUID	
                                           , ORD_LOCATION_DSGNTN_VUID	
                                           , ORD_LOCATION_DSPLYTXT	
                                           , ORD_LOCATION_ALT_CODSYS	
                                           , ORD_LOCATION_ALT_CODE	
                                           , ORD_LOCATION_ALT_DSPLYTXT	
                                           , ORD_LOCATION_ALT_REASON	
                                           , ORD_INSTITUTION_VALSET_VUID	
                                           , ORD_INSTITUTION_CONCEPT_VUID	
                                           , ORD_INSTITUTION_DSGNTN_VUID	
                                           , ORD_INSTITUTION_DSPLYTXT	
                                           , ORD_INSTITUTION_ALT_CODSYS	
                                           , ORD_INSTITUTION_ALT_CODE	
                                           , ORD_INSTITUTION_ALT_DSPLYTXT	
                                           , ORD_INSTITUTION_ALT_REASON	
                                           , ORDERED_ITEM_VALSET_VUID	
                                           , ORDERED_ITEM_CONCEPT_VUID	
                                           , ORDERED_ITEM_DSGNTN_VUID	
                                           , ORDERED_ITEM_DSPLYTXT	
                                           , ORDERED_ITEM_ALT_CODSYS	
                                           , ORDERED_ITEM_ALT_CODE	
                                           , ORDERED_ITEM_ALT_DSPLYTXT	
                                           , ORDERED_ITEM_ALT_REASON	
                                           , ORDERED_ITEM_NAME	
                                           , LAST_UPDATED_DTTZ	
                                         )
                                  VALUES (
                                             pharmreq_seq_id_val                   --  PHARMACY_REQUEST_ID	
                                           , medicine_seq_id_val                   --  MEDICINE_ID	
                                           , cosignppid                            --  COSIGNER_PPID	
                                           , authorppid                            --  GENERIC_AUTHOR_PPID	
                                           , route_code_v                          --  ROUTE_VALSET_VUID	
                                           , NULL                                  --  ROUTE_CONCEPT_VUID	
                                           , NULL                                  --  ROUTE_DSGNTN_VUID	
                                           , route_code_d                          --  ROUTE_DSPLYTXT	
                                           , alt_codsys_route                      --  ROUTE_ALT_CODSYS	
                                           , alt_code_route                        --  ROUTE_ALT_CODE	
                                           , alt_display_route                     --  ROUTE_ALT_DSPLYTXT	
                                           , alt_reason_route                      --  ROUTE_ALT_REASON	
                                           , start_date                            --  START_DT	
                                           , stop_date                             --  STOP_DT	
                                           , round(dbms_random.value(1,100))       --  SCHEDULED_UNITS	
                                           , genord_date                           --  GENERIC_ORDER_DT	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  GENERIC_PLACER_ID_ENTITYID	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  GENERIC_PLACER_ID_NAMESPACEID	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  GENERIC_PLACER_ID_UNIVERSALID	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  GENERIC_PLACER_ID_UNIVERSALTYP	
                                           , prior_code_v                          --  GENERIC_PRIORITY_VALSET_VUID	
                                           , NULL                                  --  GENERIC_PRIORITY_CONCEPT_VUID	
                                           , NULL                                  --  GENERIC_PRIORITY_DSGNTN_VUID	
                                           , prior_code_d                          --  GENERIC_PRIORITY_DSPLYTXT	
                                           , alt_codsys_prior                      --  GENERIC_PRIORITY_ALT_CODSYS	
                                           , alt_code_prior                        --  GENERIC_PRIORITY_ALT_CODE	
                                           , alt_display_prior                     --  GENERIC_PRIORITY_ALT_DSPLYTXT	
                                           , alt_reason_prior                      --  GENERIC_PRIORITY_ALT_REASON	
                                           , status_code_v                         --  GENERIC_STATUS_VALSET_VUID	
                                           , NULL                                  --  GENERIC_STATUS_CONCEPT_VUID	
                                           , NULL                                  --  GENERIC_STATUS_DSGNTN_VUID	
                                           , status_code_d                         --  GENERIC_STATUS_DSPLYTXT	
                                           , alt_codsys_st                         --  GENERIC_STATUS_ALT_CODSYS	
                                           , alt_code_st                           --  GENERIC_STATUS_ALT_CODE	
                                           , alt_display_st                        --  GENERIC_STATUS_ALT_DSPLYTXT	
                                           , alt_reason_st                         --  GENERIC_STATUS_ALT_REASON	
                                           , ordloc_code_v                         --  ORD_LOCATION_VALSET_VUID	
                                           , NULL                                  --  ORD_LOCATION_CONCEPT_VUID	
                                           , NULL                                  --  ORD_LOCATION_DSGNTN_VUID	
                                           , ordloc_code_d                         --  ORD_LOCATION_DSPLYTXT	
                                           , alt_codsys_ordloc                     --  ORD_LOCATION_ALT_CODSYS	
                                           , alt_code_ordloc                       --  ORD_LOCATION_ALT_CODE	
                                           , alt_display_ordloc                    --  ORD_LOCATION_ALT_DSPLYTXT	
                                           , alt_reason_ordloc                     --  ORD_LOCATION_ALT_REASON	
                                           , ordinst_code_v                        --  ORD_INSTITUTION_VALSET_VUID	
                                           , NULL                                  --  ORD_INSTITUTION_CONCEPT_VUID	
                                           , NULL                                  --  ORD_INSTITUTION_DSGNTN_VUID	
                                           , ordinst_code_d                        --  ORD_INSTITUTION_DSPLYTXT	
                                           , alt_codsys_ordinst                    --  ORD_INSTITUTION_ALT_CODSYS	
                                           , alt_code_ordinst                      --  ORD_INSTITUTION_ALT_CODE	
                                           , alt_display_ordinst                   --  ORD_INSTITUTION_ALT_DSPLYTXT	
                                           , alt_reason_ordinst                    --  ORD_INSTITUTION_ALT_REASON	
                                           , orditm_code_v                         --  ORDERED_ITEM_VALSET_VUID	
                                           , NULL                                  --  ORDERED_ITEM_CONCEPT_VUID	
                                           , NULL                                  --  ORDERED_ITEM_DSGNTN_VUID	
                                           , orditm_code_d                         --  ORDERED_ITEM_DSPLYTXT	
                                           , alt_codsys_orditm                     --  ORDERED_ITEM_ALT_CODSYS	
                                           , alt_code_orditm                       --  ORDERED_ITEM_ALT_CODE	
                                           , alt_display_orditm                    --  ORDERED_ITEM_ALT_DSPLYTXT	
                                           , alt_reason_orditm                     --  ORDERED_ITEM_ALT_REASON	
                                           , dbms_random.string ('U',trunc(dbms_random.value(1,100))) --  ORDERED_ITEM_NAME	
                                           , SYSTIMESTAMP                          --  LAST_UPDATED_DTTZ	                        
                                         );  
         ELSE
            -- go by way of patient reported med...need outpatient med promise prior
            pharmreq_seq_id_val := NULL;
            sdt_seq_id_val      := NULL;
         END CASE;
         
         -- Null alternate values
         alt_codsys_pattyp      := NULL;
         alt_display_pattyp     := NULL;
         alt_code_pattyp        := NULL;
         alt_reason_pattyp      := NULL;
         alt_codsys_copay       := NULL;
         alt_display_copay      := NULL;
         alt_code_copay         := NULL;
         alt_reason_copay       := NULL;
                     
         -- set patient type values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               pattyp_code_d      := NULL;
               pattyp_code_v      := NULL;
               alt_codsys_pattyp  := 'HL7';
               alt_display_pattyp := 'UNKNOWN';
               alt_code_pattyp    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_pattyp  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               pattyp_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               pattyp_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            

         -- set copay values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               copay_code_d      := NULL;
               copay_code_v      := NULL;
               alt_codsys_copay  := 'HL7';
               alt_display_copay := 'UNKNOWN';
               alt_code_copay    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_copay  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               copay_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               copay_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            
        
         -- get dates           
         random_num  := DBMS_RANDOM.VALUE (-1, -2);
         random_num2 := DBMS_RANDOM.VALUE (-38, -89);
         random_num3 := DBMS_RANDOM.VALUE (-8, -25);
         random_num4 := DBMS_RANDOM.VALUE (-1, -25);
         random_numa := DBMS_RANDOM.VALUE (-19, -25);
         random_numc := DBMS_RANDOM.VALUE (-18, -27);
         random_numd := DBMS_RANDOM.VALUE (-18, -27);
         random_numf := DBMS_RANDOM.VALUE (-18, -27);
         random_numr := DBMS_RANDOM.VALUE (-18, -27);
         random_numv := DBMS_RANDOM.VALUE (-18, -27);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num4, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numa, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numc, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numd, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numf, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numr, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numv, 'yyyymmddhh24miss')
           INTO recupd_date,
                exp_date,
                lastdisp_date,
                cancel_date,
                ppa_date,
                ppc_date,
                ppd_date,
                ppf_date,
                ppr_date,
                ppv_date 
           FROM DUAL;
        
         -- get author practitioner
         SELECT pract_part_seq_id.NEXTVAL
           INTO dataentppid
           FROM DUAL;
        
         insert_practpart.insert_message( ppd_date, dataentppid );

         -- get attending physician practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO attendppid
               FROM DUAL;
            
             insert_practpart.insert_message( ppa_date, attendppid );
         ELSE
             attendppid := NULL;
         END IF;

         -- get verify practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         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;

         cancel_date := NULL;

         -- get renew practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 40));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO renewppid
               FROM DUAL;
            
             insert_practpart.insert_message( ppr_date, renewppid );
         ELSE
             renewppid := NULL;
         END IF;
                

         -- get finish practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 40));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO finishppid
               FROM DUAL;
            
             insert_practpart.insert_message( ppf_date, finishppid );
         ELSE
             finishppid := NULL;
         END IF;
                
         -- get outpatient med promise seq id
         SELECT outpatient_med_prm_seq_id.NEXTVAL
           INTO outpmed_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; 

         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	
                 , 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
                );
         
         -- Load record into outpatient_med_prm table
         INSERT INTO OUTPATIENT_MED_PRM (
                                          OUTPATIENT_MED_PRM_ID	
                                        , PATIENT_INFORMATION_ID	
                                        , PHARMACY_REQUEST_ID	
                                        , SPECIAL_DRUG_TRACKING_ID	
                                        , DATA_ENTERER_PPID	
                                        , ATTENDING_PHYSICIAN_PPID	
                                        , VERIFYING_PHARMACIST_PPID	
                                        , RENEWER_PPID	
                                        , FINISHER_PPID	
                                        , ORIGINAL_DISPENSE_OPMDSPID
                                        , patient_source_id
                                        , patient_namespace_id
                                        , CHDR_ENTITYID	
                                        , CHDR_NAMESPACEID	
                                        , CHDR_UNIVERSALID	
                                        , CHDR_UNIVERSALTYP	
                                        , VISTA_REC_ENTITYID	
                                        , VISTA_REC_NAMESPACEID	
                                        , VISTA_REC_UNIVERSALID	
                                        , VISTA_REC_UNIVERSALTYP	
                                        , IDENTITY_ENTITYID	
                                        , IDENTITY_NAMESPACEID	
                                        , IDENTITY_UNIVERSALID	
                                        , IDENTITY_UNIVERSALTYP	
                                        , PATIENT_TYP_VALSET_VUID	
                                        , PATIENT_TYP_CONCEPT_VUID	
                                        , PATIENT_TYP_DSGNTN_VUID	
                                        , PATIENT_TYP_DSPLYTXT	
                                        , PATIENT_TYP_ALT_CODSYS	
                                        , PATIENT_TYP_ALT_CODE	
                                        , PATIENT_TYP_ALT_DSPLYTXT	
                                        , PATIENT_TYP_ALT_REASON	
                                        , RECORD_VERSION	
                                        , RECORD_UPDATE_TM	
                                        , COPAY_TX_TYP_VALSET_VUID	
                                        , COPAY_TX_TYP_CONCEPT_VUID	
                                        , COPAY_TX_TYP_DSGNTN_VUID	
                                        , COPAY_TX_TYP_DSPLYTXT	
                                        , COPAY_TX_TYP_ALT_CODSYS	
                                        , COPAY_TX_TYP_ALT_CODE	
                                        , COPAY_TX_TYP_ALT_DSPLYTXT	
                                        , COPAY_TX_TYP_ALT_REASON	
                                        , EXPANDED_PATIENT_INSTRUCTIONS	
                                        , EXPIRATION_DT	
                                        , EXTERNAL_PLACERID_ENTITYID	
                                        , EXTERNAL_PLACERID_NAMESPACEID	
                                        , EXTERNAL_PLACERID_UNIVERSALID	
                                        , EXTERNAL_PLACERID_UNIVERSALTYP	
                                        , IS_TPBRX_NULLFLV	
                                        , IS_TPBRX_VAL	
                                        , LAST_DISPENSE_DT	
                                        , NUM_REFILLS_AUTHORIZED_LOW	
                                        , NUM_REFILLS_AUTHORIZED_HIGH	
                                        , OTHER_PATIENT_INSTRUCTIONS	
                                        , PATIENT_INSTRUCTIONS	
                                        , PRESCRIPTION_SIG_TXT	
                                        , CANCEL_DT	
                                        , CANCEL_COMMENT
                                        , LAST_UPDATED_DTTZ	
                                        )
                                 VALUES (
                                          outpmed_seq_id_val                                -- OUTPATIENT_MED_PRM_ID	
                                        , patient_seq_id_val                                -- PATIENT_INFORMATION_ID	
                                        , pharmreq_seq_id_val                               -- PHARMACY_REQUEST_ID	
                                        , sdt_seq_id_val                                    -- SPECIAL_DRUG_TRACKING_ID	
                                        , dataentppid                                       -- DATA_ENTERER_PPID	
                                        , attendppid                                        -- ATTENDING_PHYSICIAN_PPID	
                                        , verifyppid                                        -- VERIFYING_PHARMACIST_PPID	
                                        , renewppid                                         -- RENEWER_PPID	
                                        , finishppid                                        -- FINISHER_PPID
                                        , NULL                                              -- ORIGINAL_DISPENSE_OPMDSPID
                                        , v_vpid                                            -- patient_source_id
                                        , 'DNS   -605'                                      -- patient_namespace_id
                                        , 'CHDR'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- CHDR_ENTITYID	
                                        , 'CHDR'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- CHDR_NAMESPACEID	
                                        , 'CHDR'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- CHDR_UNIVERSALID	
                                        , 'CHDR'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- CHDR_UNIVERSALTYP	
                                        , 'VISTAREC'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- VISTA_REC_ENTITYID	
                                        , 'VISTAREC'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- VISTA_REC_NAMESPACEID	
                                        , 'VISTAREC'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- VISTA_REC_UNIVERSALID	
                                        , 'VISTAREC'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- VISTA_REC_UNIVERSALTYP	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- IDENTITY_ENTITYID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- IDENTITY_NAMESPACEID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- IDENTITY_UNIVERSALID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- IDENTITY_UNIVERSALTYP	
                                        , pattyp_code_v                                     -- PATIENT_TYP_VALSET_VUID	
                                        , NULL                                              -- PATIENT_TYP_CONCEPT_VUID	
                                        , NULL                                              -- PATIENT_TYP_DSGNTN_VUID	
                                        , pattyp_code_d                                     -- PATIENT_TYP_DSPLYTXT	
                                        , alt_codsys_pattyp                                 -- PATIENT_TYP_ALT_CODSYS	
                                        , alt_code_pattyp                                   -- PATIENT_TYP_ALT_CODE	
                                        , alt_display_pattyp                                -- PATIENT_TYP_ALT_DSPLYTXT	
                                        , alt_reason_pattyp                                 -- PATIENT_TYP_ALT_REASON	
                                        , 1                                                 -- RECORD_VERSION	
                                        , recupd_date                                       -- RECORD_UPDATE_TM	
                                        , copay_code_v                                      -- COPAY_TX_TYP_VALSET_VUID	
                                        , NULL                                              -- COPAY_TX_TYP_CONCEPT_VUID	
                                        , NULL                                              -- COPAY_TX_TYP_DSGNTN_VUID	
                                        , copay_code_d                                      -- COPAY_TX_TYP_DSPLYTXT	
                                        , alt_codsys_copay                                  -- COPAY_TX_TYP_ALT_CODSYS	
                                        , alt_code_copay                                    -- COPAY_TX_TYP_ALT_CODE	
                                        , alt_display_copay                                 -- COPAY_TX_TYP_ALT_DSPLYTXT	
                                        , alt_reason_copay                                  -- COPAY_TX_TYP_ALT_REASON	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,1000))) -- EXPANDED_PATIENT_INSTRUCTIONS	
                                        , exp_date                                          -- EXPIRATION_DT	
                                        , 'PLACER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- EXTERNAL_PLACERID_ENTITYID	
                                        , 'PLACER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- EXTERNAL_PLACERID_NAMESPACEID	
                                        , 'PLACER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- EXTERNAL_PLACERID_UNIVERSALID	
                                        , 'PLACER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- EXTERNAL_PLACERID_UNIVERSALTYP	
                                        , NULL                                              -- IS_TPBRX_NULLFLV	
                                        , 'T'                                               -- IS_TPBRX_VAL	
                                        , lastdisp_date                                     -- LAST_DISPENSE_DT	
                                        , round(dbms_random.value(1,4))                     -- NUM_REFILLS_AUTHORIZED_LOW	
                                        , round(dbms_random.value(5,10))                    -- NUM_REFILLS_AUTHORIZED_HIGH	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,1000))) -- OTHER_PATIENT_INSTRUCTIONS	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,1000))) -- PATIENT_INSTRUCTIONS	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,100))) -- PRESCRIPTION_SIG_TXT	
                                        , cancel_date                                       -- CANCEL_DT	
                                        , dbms_random.string ('U',trunc(dbms_random.value(100,2000))) -- CANCEL_COMMENT	
                                        , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                        );
         
         -- check if need to load patient reported medication
         IF route = 3 THEN
             -- Loop 1 to x times
             random_num := DBMS_RANDOM.VALUE (1, 3);
             
             FOR rr IN 1..1
             LOOP
                 -- Null alternate values
                 alt_codsys_doslow      := NULL;
                 alt_display_doslow     := NULL;
                 alt_code_doslow        := NULL;
                 alt_reason_doslow      := NULL;
                 alt_codsys_doshgh      := NULL;
                 alt_display_doshgh     := NULL;
                 alt_code_doshgh        := NULL;
                 alt_reason_doshgh      := NULL;
                 
                 random_num  := DBMS_RANDOM.VALUE (100, 365);
                 
                 SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss')
                   INTO discont_date 
                   FROM DUAL;
                     
                 -- set dosage low values
                 random_num := round(DBMS_RANDOM.VALUE (1, 100));

                 CASE random_num
                    WHEN '1' THEN
                       doslow_code_d      := NULL;
                       doslow_code_v      := NULL;
                       alt_codsys_doslow  := 'HL7';
                       alt_display_doslow := 'UNKNOWN';
                       alt_code_doslow    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                       alt_reason_doslow  := 'INCOMING FROM PRIVATE SECTOR';
                    ELSE
                       doslow_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                       doslow_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                 END CASE;            
                     
                 -- set dosage high values
                 random_num := round(DBMS_RANDOM.VALUE (1, 100));

                 CASE random_num
                    WHEN '1' THEN
                       doshgh_code_d      := NULL;
                       doshgh_code_v      := NULL;
                       alt_codsys_doshgh  := 'HL7';
                       alt_display_doshgh := 'UNKNOWN';
                       alt_code_doshgh    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                       alt_reason_doshgh  := 'INCOMING FROM PRIVATE SECTOR';
                    ELSE
                       doshgh_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                       doshgh_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                 END CASE;            
                
                 -- get outpatient med promise seq id
                 SELECT patient_rpt_med_seq_id.NEXTVAL
                   INTO patrptmed_seq_id_val
                   FROM DUAL;
             
                 -- Load patient reported meds
                 INSERT INTO PATIENT_RPT_MED (
                                                PATIENT_RPT_MED_ID	
                                              , OUTPATIENT_MED_PRM_ID	
                                              , MEDICINE_ID	
                                              , DISCONTINUE_DT	
                                              , DOSAGE_LOW_VAL	
                                              , DOSAGE_LOWUOM_VALSET_VUID	
                                              , DOSAGE_LOWUOM_CONCEPT_VUID	
                                              , DOSAGE_LOWUOM_DSGNTN_VUID	
                                              , DOSAGE_LOWUOM_DSPLYTXT	
                                              , DOSAGE_LOWUOM_ALT_CODSYS	
                                              , DOSAGE_LOWUOM_ALT_CODE	
                                              , DOSAGE_LOWUOM_ALT_DSPLYTXT	
                                              , DOSAGE_LOWUOM_ALT_REASON	
                                              , DOSAGE_HGH_VAL	
                                              , DOSAGE_HGHUOM_VALSET_VUID	
                                              , DOSAGE_HGHUOM_CONCEPT_VUID	
                                              , DOSAGE_HGHUOM_DSGNTN_VUID	
                                              , DOSAGE_HGHUOM_DSPLYTXT	
                                              , DOSAGE_HGHUOM_ALT_CODSYS	
                                              , DOSAGE_HGHUOM_ALT_CODE	
                                              , DOSAGE_HGHUOM_ALT_DSPLYTXT	
                                              , DOSAGE_HGHUOM_ALT_REASON	
                                              , LAST_UPDATED_DTTZ	
                                             )
                                      VALUES (
                                                patrptmed_seq_id_val                                -- PATIENT_RPT_MED_ID	
                                              , outpmed_seq_id_val                                  -- OUTPATIENT_MED_PRM_ID	
                                              , medicine_seq_id_val                                 -- MEDICINE_ID	
                                              , discont_date                                        -- DISCONTINUE_DT	
                                              , round(dbms_random.VALUE (1,10))                    -- DOSAGE_LOW_VAL	
                                              , doslow_code_v                                       -- DOSAGE_LOWUOM_VALSET_VUID	
                                              , NULL                                                -- DOSAGE_LOWUOM_CONCEPT_VUID	
                                              , NULL                                                -- DOSAGE_LOWUOM_DSGNTN_VUID	
                                              , doslow_code_d                                       -- DOSAGE_LOWUOM_DSPLYTXT	
                                              , alt_codsys_doslow                                   -- DOSAGE_LOWUOM_ALT_CODSYS	
                                              , alt_code_doslow                                     -- DOSAGE_LOWUOM_ALT_CODE	
                                              , alt_display_doslow                                  -- DOSAGE_LOWUOM_ALT_DSPLYTXT	
                                              , alt_reason_doslow                                   -- DOSAGE_LOWUOM_ALT_REASON	
                                              , round(dbms_random.VALUE (10,100))                  -- DOSAGE_HGH_VAL	
                                              , doshgh_code_v                                       -- DOSAGE_HGHUOM_VALSET_VUID	
                                              , NULL                                                -- DOSAGE_HGHUOM_CONCEPT_VUID	
                                              , NULL                                                -- DOSAGE_HGHUOM_DSGNTN_VUID	
                                              , doshgh_code_d                                       -- DOSAGE_HGHUOM_DSPLYTXT	
                                              , alt_codsys_doshgh                                   -- DOSAGE_HGHUOM_ALT_CODSYS	
                                              , alt_code_doshgh                                     -- DOSAGE_HGHUOM_ALT_CODE	
                                              , alt_display_doshgh                                  -- DOSAGE_HGHUOM_ALT_DSPLYTXT	
                                              , alt_reason_doshgh                                   -- DOSAGE_HGHUOM_ALT_REASON	
                                              , SYSTIMESTAMP                                        -- LAST_UPDATED_DTTZ	                                  
                                             );
             END LOOP;
         END IF;
         
         -- process cmop_event table
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 1));

         IF random_num = 0
         THEN
            FOR j IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 3))
            LOOP
                 -- 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      := NULL;
                   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;
                
                -- get dates           
                random_num  := DBMS_RANDOM.VALUE (-10, -12);

                SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss')
                  INTO ship_date
                  FROM DUAL;
                
                -- get cmop_event seq id
                SELECT cmop_event_seq_id.NEXTVAL
                  INTO cmop_seq_id_val
                  FROM DUAL;
                
                -- load cmop_event table
                INSERT INTO CMOP_EVENT (
                                          CMOP_EVENT_ID	
                                        , OUTPATIENT_MED_PRM_ID	
                                        , SHIPPED_DT	
                                        , IDENTITY_ENTITYID	
                                        , IDENTITY_NAMESPACEID	
                                        , IDENTITY_UNIVERSALID	
                                        , IDENTITY_UNIVERSALTYP	
                                        , STATUS_VALSET_VUID	
                                        , STATUS_CONCEPT_VUID	
                                        , STATUS_DSGNTN_VUID	
                                        , STATUS_DSPLYTXT	
                                        , STATUS_ALT_CODSYS	
                                        , STATUS_ALT_CODE	
                                        , STATUS_ALT_DSPLYTXT	
                                        , STATUS_ALT_REASON	
                                        , TRACKING_NBR	
                                        , LAST_UPDATED_DTTZ	
                                       )
                                VALUES (
                                          cmop_seq_id_val                                           -- CMOP_EVENT_ID	
                                        , outpmed_seq_id_val                                        -- OUTPATIENT_MED_PRM_ID	
                                        , ship_date                                                 -- SHIPPED_DT	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| cmop_seq_id_val -- IDENTITY_ENTITYID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| cmop_seq_id_val -- IDENTITY_NAMESPACEID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| cmop_seq_id_val -- IDENTITY_UNIVERSALID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| cmop_seq_id_val -- IDENTITY_UNIVERSALTYP	
                                        , 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	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,30)))   -- TRACKING_NBR	
                                        , SYSTIMESTAMP                                              -- LAST_UPDATED_DTTZ	                                        
                                       );
                
            END LOOP;
         END IF;
         
         -- process benefit_assignment table
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 1));

         IF random_num = 0
         THEN
            FOR j IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 2))
            LOOP
                -- Null alternate values
                alt_codsys_bencat      := NULL;
                alt_display_bencat     := NULL;
                alt_code_bencat        := NULL;
                alt_reason_bencat      := NULL;

                -- set benefit category values
                random_num := round(DBMS_RANDOM.VALUE (1, 100));

                CASE random_num
                   WHEN '1' THEN
                      bencat_code_d      := NULL;
                      bencat_code_v      := NULL;
                      alt_codsys_bencat  := 'HL7';
                      alt_display_bencat := 'UNKNOWN';
                      alt_code_bencat    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                      alt_reason_bencat  := 'INCOMING FROM PRIVATE SECTOR';
                   ELSE
                      bencat_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                      bencat_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                END CASE;            
                
                -- get benefit_assignment seq id
                SELECT benefit_assignment_seq_id.NEXTVAL
                  INTO benass_seq_id_val
                  FROM DUAL;
                
                -- Insert benefit_assignment table
                INSERT INTO BENEFIT_ASSIGNMENT (
                                                  BENEFIT_ASSIGNMENT_ID	
                                                , OUTPATIENT_MED_PRM_ID	
                                                , BENEFIT_CATG_VALSET_VUID	
                                                , BENEFIT_CATG_CONCEPT_VUID	
                                                , BENEFIT_CATG_DSGNTN_VUID	
                                                , BENEFIT_CATG_DSPLYTXT	
                                                , BENEFIT_CATG_ALT_CODSYS	
                                                , BENEFIT_CATG_ALT_CODE	
                                                , BENEFIT_CATG_ALT_DSPLYTXT	
                                                , BENEFIT_CATG_ALT_REASON	
                                                , LAST_UPDATED_DTTZ	
                                               )
                                        VALUES (
                                                  benass_seq_id_val                                 -- BENEFIT_ASSIGNMENT_ID	
                                                , outpmed_seq_id_val                                -- OUTPATIENT_MED_PRM_ID	
                                                , bencat_code_v                                     -- BENEFIT_CATG_VALSET_VUID	
                                                , NULL                                              -- BENEFIT_CATG_CONCEPT_VUID	
                                                , NULL                                              -- BENEFIT_CATG_DSGNTN_VUID	
                                                , bencat_code_d                                     -- BENEFIT_CATG_DSPLYTXT	
                                                , alt_codsys_bencat                                    -- BENEFIT_CATG_ALT_CODSYS	
                                                , alt_code_bencat                                   -- BENEFIT_CATG_ALT_CODE	
                                                , alt_display_bencat                                -- BENEFIT_CATG_ALT_DSPLYTXT	
                                                , alt_reason_bencat                                 -- BENEFIT_CATG_ALT_REASON	
                                                , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                               );
            END LOOP;
         END IF;
         
         -- process medication_instruction table
         FOR j IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 3))
         LOOP
             -- Null alternate values
             alt_codsys_dispql      := NULL;
             alt_display_dispql     := NULL;
             alt_code_dispql        := NULL;
             alt_reason_dispql      := NULL;
             alt_codsys_dispqh      := NULL;
             alt_display_dispqh     := NULL;
             alt_code_dispqh        := NULL;
             alt_reason_dispqh      := NULL;   
             alt_codsys_dosef       := NULL;
             alt_display_dosef      := NULL;
             alt_code_dosef         := NULL;
             alt_reason_dosef       := NULL;  
             alt_codsys_dosql       := NULL;
             alt_display_dosql      := NULL;
             alt_code_dosql         := NULL;
             alt_reason_dosql       := NULL;             
             alt_codsys_dosqh       := NULL;
             alt_display_dosqh      := NULL;
             alt_code_dosqh         := NULL;
             alt_reason_dosqh       := NULL;            
             alt_codsys_route       := NULL;
             alt_display_route      := NULL;
             alt_code_route         := NULL;
             alt_reason_route       := NULL;    
             
             -- set dispense quantity low values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   dispql_code_d      := NULL;
                   dispql_code_v      := NULL;
                   alt_codsys_dispql  := 'HL7';
                   alt_display_dispql := 'UNKNOWN';
                   alt_code_dispql    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_dispql  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   dispql_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   dispql_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            
             
             -- set dispense quantity high values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   dispqh_code_d      := NULL;
                   dispqh_code_v      := NULL;
                   alt_codsys_dispqh  := 'HL7';
                   alt_display_dispqh := 'UNKNOWN';
                   alt_code_dispqh    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_dispqh  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   dispqh_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   dispqh_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            
             
             -- set dose form values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   dosef_code_d      := NULL;
                   dosef_code_v      := NULL;
                   alt_codsys_dosef  := 'HL7';
                   alt_display_dosef := 'UNKNOWN';
                   alt_code_dosef    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_dosef  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   dosef_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   dosef_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            
             
             -- set dose quantity low values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   dosql_code_d      := NULL;
                   dosql_code_v      := NULL;
                   alt_codsys_dosql  := 'HL7';
                   alt_display_dosql := 'UNKNOWN';
                   alt_code_dosql    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_dosql  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   dosql_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   dosql_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            
             
             -- set dose quantity high values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   dosqh_code_d      := NULL;
                   dosqh_code_v      := NULL;
                   alt_codsys_dosqh  := 'HL7';
                   alt_display_dosqh := 'UNKNOWN';
                   alt_code_dosqh    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_dosqh  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   dosqh_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   dosqh_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            
             
             -- set route values
             random_num := round(DBMS_RANDOM.VALUE (1, 100));

             CASE random_num
                WHEN '1' THEN
                   route_code_d      := NULL;
                   route_code_v      := NULL;
                   alt_codsys_route  := 'HL7';
                   alt_display_route := 'UNKNOWN';
                   alt_code_route    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                   alt_reason_route  := 'INCOMING FROM PRIVATE SECTOR';
                ELSE
                   route_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
                   route_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             END CASE;            

             -- get medication_instruction seq id
             SELECT medication_instruction_seq_id.NEXTVAL
               INTO medinst_seq_id_val
               FROM DUAL;
               
             -- Insert into medication_instruction table
             INSERT INTO MEDICATION_INSTRUCTION (
                                                   MEDICATION_INSTRUCTION_ID	
                                                 , OUTPATIENT_MED_PRM_ID	
                                                 , DISPQTY_LOW_VAL	
                                                 , DISPQTY_LOWUOM_VALSET_VUID	
                                                 , DISPQTY_LOWUOM_CONCEPT_VUID	
                                                 , DISPQTY_LOWUOM_DSGNTN_VUID	
                                                 , DISPQTY_LOWUOM_DSPLYTXT	
                                                 , DISPQTY_LOWUOM_ALT_CODSYS	
                                                 , DISPQTY_LOWUOM_ALT_CODE	
                                                 , DISPQTY_LOWUOM_ALT_DSPLYTXT	
                                                 , DISPQTY_LOWUOM_ALT_REASON	
                                                 , DISPQTY_HGH_VAL	
                                                 , DISPQTY_HGHUOM_VALSET_VUID	
                                                 , DISPQTY_HGHUOM_CONCEPT_VUID	
                                                 , DISPQTY_HGHUOM_DSGNTN_VUID	
                                                 , DISPQTY_HGHUOM_DSPLYTXT	
                                                 , DISPQTY_HGHUOM_ALT_CODSYS	
                                                 , DISPQTY_HGHUOM_ALT_CODE	
                                                 , DISPQTY_HGHUOM_ALT_DSPLYTXT	
                                                 , DISPQTY_HGHUOM_ALT_REASON	
                                                 , DOSE_FORM_VALSET_VUID	
                                                 , DOSE_FORM_CONCEPT_VUID	
                                                 , DOSE_FORM_DSGNTN_VUID	
                                                 , DOSE_FORM_DSPLYTXT	
                                                 , DOSE_FORM_ALT_CODSYS	
                                                 , DOSE_FORM_ALT_CODE	
                                                 , DOSE_FORM_ALT_DSPLYTXT	
                                                 , DOSE_FORM_ALT_REASON	
                                                 , DOSE_QTY_LOW_VAL	
                                                 , DOSE_QTY_LOWUOM_VALSET_VUID	
                                                 , DOSE_QTY_LOWUOM_CONCEPT_VUID	
                                                 , DOSE_QTY_LOWUOM_DSGNTN_VUID	
                                                 , DOSE_QTY_LOWUOM_DSPLYTXT	
                                                 , DOSE_QTY_LOWUOM_ALT_CODSYS	
                                                 , DOSE_QTY_LOWUOM_ALT_CODE	
                                                 , DOSE_QTY_LOWUOM_ALT_DSPLYTXT	
                                                 , DOSE_QTY_LOWUOM_ALT_REASON	
                                                 , DOSE_QTY_HGH_VAL	
                                                 , DOSE_QTY_HGHUOM_VALSET_VUID	
                                                 , DOSE_QTY_HGHUOM_CONCEPT_VUID	
                                                 , DOSE_QTY_HGHUOM_DSGNTN_VUID	
                                                 , DOSE_QTY_HGHUOM_DSPLYTXT	
                                                 , DOSE_QTY_HGHUOM_ALT_CODSYS	
                                                 , DOSE_QTY_HGHUOM_ALT_CODE	
                                                 , DOSE_QTY_HGHUOM_ALT_DSPLYTXT	
                                                 , DOSE_QTY_HGHUOM_ALT_REASON	
                                                 , ROUTE_VALSET_VUID	
                                                 , ROUTE_CONCEPT_VUID	
                                                 , ROUTE_DSGNTN_VUID	
                                                 , ROUTE_DSPLYTXT	
                                                 , ROUTE_ALT_CODSYS	
                                                 , ROUTE_ALT_CODE	
                                                 , ROUTE_ALT_DSPLYTXT	
                                                 , ROUTE_ALT_REASON	
                                                 , VISTA_CONJ_VALSET_VUID
                                                 , VISTA_CONJ_CONCEPT_VUID
                                                 , VISTA_CONJ_DSGNTN_VUID
                                                 , VISTA_CONJ_DSPLYTXT
                                                 , VISTA_CONJ_ALT_CODSYS
                                                 , VISTA_CONJ_ALT_CODE
                                                 , VISTA_CONJ_ALT_DSPLYTXT
                                                 , VISTA_CONJ_ALT_REASON	
                                                 , VISTA_DOSE_UNIT	
                                                 , VISTA_INTENDED_DURATION	
                                                 , VISTA_SCHEDULE	
                                                 , VISTA_VERB	
                                                 , VISTA_NOUN	
                                                 , LAST_UPDATED_DTTZ	
                                                )
                                         VALUES (
                                                   medinst_seq_id_val                               -- MEDICATION_INSTRUCTION_ID	
                                                 , outpmed_seq_id_val                               -- OUTPATIENT_MED_PRM_ID	
                                                 , round(dbms_random.value(1,10))                   -- DISPQTY_LOW_VAL	
                                                 , dispql_code_v                                    -- DISPQTY_LOWUOM_VALSET_VUID	
                                                 , NULL                                             -- DISPQTY_LOWUOM_CONCEPT_VUID	
                                                 , NULL                                             -- DISPQTY_LOWUOM_DSGNTN_VUID	
                                                 , dispql_code_d                                    -- DISPQTY_LOWUOM_DSPLYTXT	
                                                 , alt_codsys_dispql                                -- DISPQTY_LOWUOM_ALT_CODSYS	
                                                 , alt_code_dispql                                  -- DISPQTY_LOWUOM_ALT_CODE	
                                                 , alt_display_dispql                               -- DISPQTY_LOWUOM_ALT_DSPLYTXT	
                                                 , alt_reason_dispql                                -- DISPQTY_LOWUOM_ALT_REASON	
                                                 , round(dbms_random.value(20,50))                  -- DISPQTY_HGH_VAL	
                                                 , dispqh_code_v                                    -- DISPQTY_HGHUOM_VALSET_VUID	
                                                 , NULL                                             -- DISPQTY_HGHUOM_CONCEPT_VUID	
                                                 , NULL                                             -- DISPQTY_HGHUOM_DSGNTN_VUID	
                                                 , dispqh_code_d                                    -- DISPQTY_HGHUOM_DSPLYTXT	
                                                 , alt_codsys_dispqh                                -- DISPQTY_HGHUOM_ALT_CODSYS	
                                                 , alt_code_dispqh                                  -- DISPQTY_HGHUOM_ALT_CODE	
                                                 , alt_display_dispqh                               -- DISPQTY_HGHUOM_ALT_DSPLYTXT	
                                                 , alt_reason_dispqh                                -- DISPQTY_HGHUOM_ALT_REASON	
                                                 , dosef_code_v                                     -- DOSE_FORM_VALSET_VUID	
                                                 , NULL                                             -- DOSE_FORM_CONCEPT_VUID	
                                                 , NULL                                             -- DOSE_FORM_DSGNTN_VUID	
                                                 , dosef_code_d                                     -- DOSE_FORM_DSPLYTXT	
                                                 , alt_codsys_dosef                                 -- DOSE_FORM_ALT_CODSYS	
                                                 , alt_code_dosef                                   -- DOSE_FORM_ALT_CODE	
                                                 , alt_display_dosef                                -- DOSE_FORM_ALT_DSPLYTXT	
                                                 , alt_reason_dosef                                 -- DOSE_FORM_ALT_REASON	
                                                 , round(dbms_random.value(30,50))                  -- DOSE_QTY_LOW_VAL	
                                                 , dosql_code_v                                     -- DOSE_QTY_LOWUOM_VALSET_VUID	
                                                 , NULL                                             -- DOSE_QTY_LOWUOM_CONCEPT_VUID	
                                                 , NULL                                             -- DOSE_QTY_LOWUOM_DSGNTN_VUID	
                                                 , dosql_code_d                                     -- DOSE_QTY_LOWUOM_DSPLYTXT	
                                                 , alt_codsys_dosql                                 -- DOSE_QTY_LOWUOM_ALT_CODSYS	
                                                 , alt_code_dosql                                   -- DOSE_QTY_LOWUOM_ALT_CODE	
                                                 , alt_display_dosql                                -- DOSE_QTY_LOWUOM_ALT_DSPLYTXT	
                                                 , alt_reason_dosql                                 -- DOSE_QTY_LOWUOM_ALT_REASON	
                                                 , round(dbms_random.value(75,200))                 -- DOSE_QTY_HGH_VAL	
                                                 , dosqh_code_v                                     -- DOSE_QTY_HGHUOM_VALSET_VUID	
                                                 , NULL                                             -- DOSE_QTY_HGHUOM_CONCEPT_VUID	
                                                 , NULL                                             -- DOSE_QTY_HGHUOM_DSGNTN_VUID	
                                                 , dosqh_code_d                                     -- DOSE_QTY_HGHUOM_DSPLYTXT	
                                                 , alt_codsys_dosqh                                 -- DOSE_QTY_HGHUOM_ALT_CODSYS	
                                                 , alt_code_dosqh                                   -- DOSE_QTY_HGHUOM_ALT_CODE	
                                                 , alt_display_dosqh                                -- DOSE_QTY_HGHUOM_ALT_DSPLYTXT	
                                                 , alt_reason_dosqh                                 -- DOSE_QTY_HGHUOM_ALT_REASON	
                                                 , route_code_v                                     -- ROUTE_VALSET_VUID	
                                                 , NULL                                             -- ROUTE_CONCEPT_VUID	
                                                 , NULL                                             -- ROUTE_DSGNTN_VUID	
                                                 , route_code_d                                     -- ROUTE_DSPLYTXT	
                                                 , alt_codsys_route                                 -- ROUTE_ALT_CODSYS	
                                                 , alt_code_route                                   -- ROUTE_ALT_CODE	
                                                 , alt_display_route                                -- ROUTE_ALT_DSPLYTXT	
                                                 , alt_reason_route                                 -- ROUTE_ALT_REASON	
                                                 , ROUND(DBMS_RANDOM.VALUE (1,99999999))            -- VISTA_CONJ_VALSET_VUID	
                                                 , NULL                                             -- VISTA_CONJ_CONCEPT_VUID	
                                                 , NULL                                             -- VISTA_CONJ_DSGNTN_VUID	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,100))) -- VISTA_CONJ_DSPLYTXT	
                                                 , NULL                                             -- VISTA_CONJ_ALT_CODSYS	
                                                 , NULL                                             -- VISTA_CONJ_ALT_CODE	
                                                 , NULL                                             -- VISTA_CONJ_ALT_DSPLYTXT	
                                                 , NULL                                             -- VISTA_CONJ_ALT_REASON	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- VISTA_DOSE_UNIT	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- VISTA_INTENDED_DURATION	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- VISTA_SCHEDULE	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- VISTA_VERB	
                                                 , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- VISTA_NOUN	
                                                 , SYSTIMESTAMP                                     -- LAST_UPDATED_DTTZ	
                                                );
                             
         END LOOP;
         
         -- process outpatient_med_dsp table
         outpat_med_dsp := ROUND (DBMS_RANDOM.VALUE (1, 3));

         CASE outpat_med_dsp
         WHEN '1' THEN
            -- set original dispense record
            orig_pid    := outpmed_seq_id_val;
            refill_pid  := NULL;
            partial_pid := NULL;
         WHEN '2' THEN
            -- set refill dispense records
            orig_pid    := NULL;
            refill_pid  := outpmed_seq_id_val;
            partial_pid := NULL;
         ELSE
            -- set partial dispense records
            orig_pid    := NULL;
            refill_pid  := NULL;
            partial_pid := outpmed_seq_id_val;
         END CASE;
             
         -- set quantity dispensed values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               qtydis_code_d      := NULL;
               qtydis_code_v      := NULL;
               alt_codsys_qtydis  := 'HL7';
               alt_display_qtydis := 'UNKNOWN';
               alt_code_qtydis    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_qtydis  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               qtydis_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               qtydis_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            
             
         -- set level of understanding values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               lvlund_code_d      := NULL;
               lvlund_code_v      := NULL;
               alt_codsys_lvlund  := 'HL7';
               alt_display_lvlund := 'UNKNOWN';
               alt_code_lvlund    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_lvlund  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               lvlund_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               lvlund_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            
             
         -- set mail window values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               mailw_code_d      := NULL;
               mailw_code_v      := NULL;
               alt_codsys_mailw  := 'HL7';
               alt_display_mailw := 'UNKNOWN';
               alt_code_mailw    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_mailw  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               mailw_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               mailw_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            
             
         -- set ncpdp values
         random_num := round(DBMS_RANDOM.VALUE (1, 100));

         CASE random_num
            WHEN '1' THEN
               ncpdp_code_d      := NULL;
               ncpdp_code_v      := NULL;
               alt_codsys_ncpdp  := 'HL7';
               alt_display_ncpdp := 'UNKNOWN';
               alt_code_ncpdp    := ROUND(DBMS_RANDOM.VALUE (1,99999999));
               alt_reason_ncpdp  := 'INCOMING FROM PRIVATE SECTOR';
            ELSE
               ncpdp_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
               ncpdp_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         END CASE;            
        
         -- get dates           
         random_num  := DBMS_RANDOM.VALUE (-1, -2);
         random_num1 := DBMS_RANDOM.VALUE (-1, -2);
         random_num2 := DBMS_RANDOM.VALUE (-1, -2);
         random_num3 := DBMS_RANDOM.VALUE (30, 35);
         random_num4 := DBMS_RANDOM.VALUE (60, 180);
         random_numa := DBMS_RANDOM.VALUE (-2, -5);
         random_numc := DBMS_RANDOM.VALUE (-1, -5);
         random_numd := DBMS_RANDOM.VALUE (-1, -8);

         SELECT TO_CHAR (fixed_date + random_num, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num1, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num3, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_num4, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numa, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numc, 'yyyymmddhh24miss'),
                TO_CHAR (fixed_date + random_numd, 'yyyymmddhh24miss')
           INTO disp_date,
                prescription_date,
                release_date,
                expl_date,
                exph_date,
                ppd_date,
                refill_date,
                pharm_date
           FROM DUAL;
         
         -- get data enterer practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO dataentppid
               FROM DUAL;
            
             insert_practpart.insert_message( ppd_date, dataentppid );
         ELSE
             dataentppid := NULL;
         END IF;
         
         -- get refill practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO refillppid
               FROM DUAL;
            
             insert_practpart.insert_message( refill_date, refillppid );
         ELSE
             refillppid := NULL;
         END IF;
         
         -- get pharmacist practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO pharmppid
               FROM DUAL;
            
             insert_practpart.insert_message( pharm_date, pharmppid );
         ELSE
             pharmppid := NULL;
         END IF;
         
         IF outpat_med_dsp = 1 
         THEN
            counter := 1;
         ELSE
            counter := round(dbms_random.value(1,3));
         END IF;
         
         FOR d in 1..counter
         LOOP

            -- get medication_instruction seq id
            SELECT outpatient_med_dsp_seq_id.NEXTVAL
              INTO outpdsp_seq_id_val
              FROM DUAL;

            -- Insert into outpatient_med_dsp table
            INSERT INTO OUTPATIENT_MED_DSP (
                                              OUTPATIENT_MED_DSP_ID	
                                            , PARTIAL_DISPENSE_OPMPID	
                                            , REFILL_DISPENSE_OPMPID	
                                            , DATA_ENTERER_PPID	
                                            , PHARMACIST_PPID	
                                            , FILLER_PPID	
                                            , QTYDISPENSED_VAL	
                                            , QTYDISPENSED_UOM_VALSET_VUID	
                                            , QTYDISPENSED_UOM_CONCEPT_VUID	
                                            , QTYDISPENSED_UOM_DSGNTN_VUID	
                                            , QTYDISPENSED_UOM_DSPLYTXT	
                                            , QTYDISPENSED_UOM_ALT_CODSYS	
                                            , QTYDISPENSED_UOM_ALT_CODE	
                                            , QTYDISPENSED_UOM_ALT_DSPLYTXT	
                                            , QTYDISPENSED_UOM_ALT_REASON	
                                            , prescription_dt
                                            , DISPENSE_DT	
                                            , RECORD_VERSION	
                                            , DAYS_SUPPLY	
                                            , IDENTITY_ENTITYID	
                                            , IDENTITY_NAMESPACEID	
                                            , IDENTITY_UNIVERSALID	
                                            , IDENTITY_UNIVERSALTYP	
                                            , LVLUNDERSTANDING_VALSET_VUID	
                                            , LVLUNDERSTANDING_CONCEPT_VUID	
                                            , LVLUNDERSTANDING_DSGNTN_VUID	
                                            , LVLUNDERSTANDING_DSPLYTXT	
                                            , LVLUNDERSTANDING_ALT_CODSYS	
                                            , LVLUNDERSTANDING_ALT_CODE	
                                            , LVLUNDERSTANDING_ALT_DSPLYTXT	
                                            , LVLUNDERSTANDING_ALT_REASON	
                                            , MAIL_WINDOW_VALSET_VUID	
                                            , MAIL_WINDOW_CONCEPT_VUID	
                                            , MAIL_WINDOW_DSGNTN_VUID	
                                            , MAIL_WINDOW_DSPLYTXT	
                                            , MAIL_WINDOW_ALT_CODSYS	
                                            , MAIL_WINDOW_ALT_CODE	
                                            , MAIL_WINDOW_ALT_DSPLYTXT	
                                            , MAIL_WINDOW_ALT_REASON	
                                            , RELEASE_DT	
                                            , WAS_PATIENT_COUNSELED_NULLFLV	
                                            , WAS_PATIENT_COUNSELED_VAL	
                                            , DIV_NCPDPID_VALSET_VUID	
                                            , DIV_NCPDPID_CONCEPT_VUID	
                                            , DIV_NCPDPID_DSGNTN_VUID	
                                            , DIV_NCPDPID_DSPLYTXT	
                                            , DIV_NCPDPID_ALT_CODSYS	
                                            , DIV_NCPDPID_ALT_CODE	
                                            , DIV_NCPDPID_ALT_DSPLYTXT	
                                            , DIV_NCPDPID_ALT_REASON	
                                            , DRUG_UNITPRICE_NULLFLV	
                                            , DRUG_UNITPRICE_VAL	
                                            , EXPIRATION_DT_LOW	
                                            , EXPIRATION_DT_HIGH	
                                            , EXPIRATION_DT_WDTH	
                                            , EXPIRATION_DT_WDTHUNT	
                                            , LOT_ID	
                                            , MANUFACTURER_CONTROL_NBR	
                                            , LAST_UPDATED_DTTZ	
                                           )
                                    VALUES (
                                              outpdsp_seq_id_val                                -- OUTPATIENT_MED_DSP_ID	
                                            , partial_pid                                       -- PARTIAL_DISPENSE_OPMPID	
                                            , refill_pid                                        -- REFILL_DISPENSE_OPMPID	
                                            , dataentppid                                       -- DATA_ENTERER_PPID	
                                            , pharmppid                                         -- PHARMACIST_PPID	
                                            , refillppid                                        -- FILLER_PPID	
                                            , round(dbms_random.value(20,500))                  -- QTYDISPENSED_VAL	
                                            , qtydis_code_v                                     -- QTYDISPENSED_UOM_VALSET_VUID	
                                            , NULL                                              -- QTYDISPENSED_UOM_CONCEPT_VUID	
                                            , NULL                                              -- QTYDISPENSED_UOM_DSGNTN_VUID	
                                            , qtydis_code_d                                     -- QTYDISPENSED_UOM_DSPLYTXT	
                                            , alt_codsys_qtydis                                 -- QTYDISPENSED_UOM_ALT_CODSYS	
                                            , alt_code_qtydis                                   -- QTYDISPENSED_UOM_ALT_CODE	
                                            , alt_display_qtydis                                -- QTYDISPENSED_UOM_ALT_DSPLYTXT	
                                            , alt_reason_qtydis                                 -- QTYDISPENSED_UOM_ALT_REASON	
                                            , prescription_date                                         -- DISPENSE_DT	
                                            , disp_date                                         -- DISPENSE_DT	
                                            , 1                                                 -- RECORD_VERSION	
                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- DAYS_SUPPLY	
                                            , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTITY_ENTITYID	
                                            , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTITY_NAMESPACEID	
                                            , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTITY_UNIVERSALID	
                                            , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTITY_UNIVERSALTYP	
                                            , lvlund_code_v                                     -- LVLUNDERSTANDING_VALSET_VUID	
                                            , NULL                                              -- LVLUNDERSTANDING_CONCEPT_VUID	
                                            , NULL                                              -- LVLUNDERSTANDING_DSGNTN_VUID	
                                            , lvlund_code_d                                     -- LVLUNDERSTANDING_DSPLYTXT	
                                            , alt_codsys_lvlund                                 -- LVLUNDERSTANDING_ALT_CODSYS	
                                            , alt_code_lvlund                                   -- LVLUNDERSTANDING_ALT_CODE	
                                            , alt_display_lvlund                                -- LVLUNDERSTANDING_ALT_DSPLYTXT	
                                            , alt_reason_lvlund                                 -- LVLUNDERSTANDING_ALT_REASON	
                                            , mailw_code_v                                      -- MAIL_WINDOW_VALSET_VUID	
                                            , NULL                                              -- MAIL_WINDOW_CONCEPT_VUID	
                                            , NULL                                              -- MAIL_WINDOW_DSGNTN_VUID	
                                            , mailw_code_d                                      -- MAIL_WINDOW_DSPLYTXT	
                                            , alt_codsys_mailw                                  -- MAIL_WINDOW_ALT_CODSYS	
                                            , alt_code_mailw                                    -- MAIL_WINDOW_ALT_CODE	
                                            , alt_display_mailw                                 -- MAIL_WINDOW_ALT_DSPLYTXT	
                                            , alt_reason_mailw                                  -- MAIL_WINDOW_ALT_REASON	
                                            , release_date                                      -- RELEASE_DT	
                                            , NULL                                              -- WAS_PATIENT_COUNSELED_NULLFLV	
                                            , 'T'                                               -- WAS_PATIENT_COUNSELED_VAL	
                                            , ncpdp_code_v                                      -- DIV_NCPDPID_VALSET_VUID	
                                            , NULL                                              -- DIV_NCPDPID_CONCEPT_VUID	
                                            , NULL                                              -- DIV_NCPDPID_DSGNTN_VUID	
                                            , ncpdp_code_d                                      -- DIV_NCPDPID_DSPLYTXT	
                                            , alt_codsys_ncpdp                                  -- DIV_NCPDPID_ALT_CODSYS	
                                            , alt_code_ncpdp                                    -- DIV_NCPDPID_ALT_CODE	
                                            , alt_display_ncpdp                                 -- DIV_NCPDPID_ALT_DSPLYTXT	
                                            , alt_reason_ncpdp                                  -- DIV_NCPDPID_ALT_REASON	
                                            , NULL                                              -- DRUG_UNITPRICE_NULLFLV	
                                            , ROUND(DBMS_RANDOM.VALUE(5.50,560.34),2)           -- DRUG_UNITPRICE_VAL	
                                            , expl_date                                         -- EXPIRATION_DT_LOW	
                                            , exph_date                                         -- EXPIRATION_DT_HIGH	
                                            , 90                                                -- EXPIRATION_DT_WDTH	
                                            , 'DAYS'                                            -- EXPIRATION_DT_WDTHUNT	
                                            , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- LOT_ID	
                                            , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- MANUFACTURER_CONTROL_NBR	
                                            , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                           );
                                           
            IF outpat_med_dsp = 1 
            THEN
               UPDATE OUTPATIENT_MED_PRM SET ORIGINAL_DISPENSE_OPMDSPID = outpdsp_seq_id_val
               WHERE OUTPATIENT_MED_PRM_ID = outpmed_seq_id_val;
            END IF;

         END LOOP;
         
       END LOOP;
       COMMIT;
   END LOOP;

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