/******************************************************************************
   NAME:       load_outppharm_load4x_arms.sql
   PURPOSE:    This script will create Outpatient Pharmacy records that map to 
               VHIM version 4.0 c5a  
   ADDITIONAL INFORMATION:
    
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        10/19/2007  M. Jacobsen      - Initial Release for vhim 4.0
   1.2        11/05/2007  M. Jacobsen      - added hardcoding of PATIENT_ASSIGNING_FACILITY to 'VA.GOV-605'
   1.3        12/11/2007  M. Jacobsen      - updates to sync with vhim 4, c5a
******************************************************************************/
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;
   birthnum                 NUMBER          := 0;
   birth_dt                 VARCHAR2 (30);
   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;
   treat_seq_id_val         NUMBER;
   medinst_seq_id_val       NUMBER;
   outpdsp_seq_id_val       NUMBER;
   physppid                 NUMBER;
   authorppid               NUMBER;
   cosignppid               NUMBER;
   dataentppid              NUMBER;
   finishppid               NUMBER;
   cancelppid               NUMBER;
   renewppid                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);
   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);
   cmop_code_d              VARCHAR2 (100);
   cmop_code_v              VARCHAR2 (30);
   dosage_code_d            VARCHAR2 (100);
   dosage_code_v            VARCHAR2 (30);
   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);
   ndf_code_d               VARCHAR2 (100);
   ndf_code_v               VARCHAR2 (30);
   otc_code_d               VARCHAR2 (100);
   otc_code_v               VARCHAR2 (30);
   status_code_d            VARCHAR2 (100);
   status_code_v            VARCHAR2 (20);
   totdosl_code_d           VARCHAR2 (100);
   totdosl_code_v           VARCHAR2 (30);
   totdosh_code_d           VARCHAR2 (100);
   totdosh_code_v           VARCHAR2 (30);   
   anc_code_d               VARCHAR2 (100);
   anc_code_v               VARCHAR2 (30);
   anc_nullflv              VARCHAR2 (20);
   wbc_code_d               VARCHAR2 (100);
   wbc_code_v               VARCHAR2 (30);
   wbc_nullflv              VARCHAR2 (20);
   route_code_d             VARCHAR2 (100);
   route_code_v             VARCHAR2 (30);
   prior_code_d             VARCHAR2 (100);
   prior_code_v             VARCHAR2 (30);
   ordloc_code_d            VARCHAR2 (100);
   ordloc_code_v            VARCHAR2 (30);
   ordinst_code_d           VARCHAR2 (100);
   ordinst_code_v           VARCHAR2 (30);
   orditm_code_d            VARCHAR2 (100);
   orditm_code_v            VARCHAR2 (30);
   pattyp_code_d            VARCHAR2 (100);
   pattyp_code_v            VARCHAR2 (30);
   copay_code_d             VARCHAR2 (100);
   copay_code_v             VARCHAR2 (30);
   doslow_code_d            VARCHAR2 (100);
   doslow_code_v            VARCHAR2 (30);
   doshgh_code_d            VARCHAR2 (100);
   doshgh_code_v            VARCHAR2 (30);
   bencat_code_d            VARCHAR2 (100);
   bencat_code_v            VARCHAR2 (30);
   dispql_code_d            VARCHAR2 (100);
   dispql_code_v            VARCHAR2 (30);
   dispqh_code_d            VARCHAR2 (100);
   dispqh_code_v            VARCHAR2 (30);
   dosef_code_d             VARCHAR2 (100);
   dosef_code_v             VARCHAR2 (30);
   dosql_code_d             VARCHAR2 (100);
   dosql_code_v             VARCHAR2 (30);
   dosqh_code_d             VARCHAR2 (100);
   dosqh_code_v             VARCHAR2 (30);
   qtydis_code_d            VARCHAR2 (100);
   qtydis_code_v            VARCHAR2 (30);
   lvlund_code_d            VARCHAR2 (100);
   lvlund_code_v            VARCHAR2 (30);
   mailw_code_d             VARCHAR2 (100);
   mailw_code_v             VARCHAR2 (30);
   ncpdp_code_d             VARCHAR2 (100);
   ncpdp_code_v             VARCHAR2 (30);   
   patient_seq_id_val       NUMBER;
   gender_code_d                VARCHAR2 (100);
   gender_code_v                VARCHAR2 (30);
   race_code_d                  VARCHAR2 (100);
   race_code_v                  VARCHAR2 (30);
   ethnic_code_d                VARCHAR2 (100);
   ethnic_code_v                VARCHAR2 (30);
   v_vpid                       VARCHAR2 (30);
   starting_patient_vpid        NUMBER;
   fixed_date			DATE;
   
BEGIN

   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   '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_med       := NULL;
         alt_display_med      := NULL;
         alt_code_med         := NULL;
         
         cmop_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
         cmop_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));

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

         otc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
         otc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
         
         SELECT medicine_seq_id.NEXTVAL
           INTO medicine_seq_id_val
           FROM DUAL;
         
         -- insert record into medicine table
         INSERT INTO medicine
                     (    
                          MEDICINE_ID
                        , CMOPDRUG_CODE
                        , CMOPDRUG_DSPLYTXT
                        , CMOPDRUG_CODSYS
                        , MANUFACTURER_NAME
                        , MEDICATION_CODE
                        , MEDICATION_DSPLYTXT
                        , MEDICATION_CODSYS
                        , MEDICATION_ALT_CODE
                        , MEDICATION_ALT_DSPLYTXT
                        , MEDICATION_ALT_CODSYS
                        , OTC_CODE
                        , OTC_DSPLYTXT
                        , OTC_CODSYS
                        , TRADE_NAME_CODE
                        , TRADE_NAME_DSPLYTXT
                        , TRADE_NAME_CODSYS
                        , MANUFACTURER_ORG_NAME
                        , LAST_UPDATED_DTTZ
                     )
              VALUES (   
                         medicine_seq_id_val                                                    -- MEDICINE_ID	
                       , cmop_code_v                                                            -- CMOPDRUG_VALSET_VUID	
                       , cmop_code_d                                                            -- CMOPDRUG_DSPLYTXT	
                       , NULL                                                        -- CMOPDRUG_ALT_CODSYS	
                       , dbms_random.string ('U',trunc(dbms_random.value(1,100)))               -- MANUFACTURER_NAME	
                       , med_code_v                                                             -- MEDICATION_VALSET_VUID	
                       , med_code_d                                                             -- MEDICATION_DSPLYTXT	
                       , NULL
                       , alt_code_med                                                           -- MEDICATION_ALT_CODE	
                       , alt_display_med                                                        -- MEDICATION_ALT_DSPLYTXT	
                       , alt_codsys_med                                                         -- MEDICATION_ALT_CODSYS	
                       , otc_code_v                                                             -- NDC_VALSET_VUID	
                       , otc_code_d                                                             -- NDC_DSPLYTXT	
                       , null                                                         -- NDC_ALT_CODSYS	
                       , ROUND(DBMS_RANDOM.VALUE (1,99999999))                              -- TRADE_NAME_code	
                       , dbms_random.string ('U',trunc(dbms_random.value(1,100)))               -- TRADE_NAME_DSPLYTXT	
                       , NULL                                                                   -- TRADE_NAME_CODSYS	
                       , dbms_random.string ('U',trunc(dbms_random.value(1,100)))               -- manufacturer_org_name	
                       , 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;
            
            -- Set status_code_displayname
            random_num := round(DBMS_RANDOM.VALUE (1, 3));

            CASE random_num
               WHEN '1' THEN
                  status_code_d := 'ACTIVE';
                  status_code_v := '25';
               WHEN '2' THEN
                  status_code_d := 'OPEN';
                  status_code_v := '11';
               ELSE
                  status_code_d      := 'WAIT';
                  status_code_v      := '37';
            END CASE;
            
            -- set ancobs values
            anc_cnt         := round(dbms_random.value (400,2000));
            anc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
            anc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
    
            -- set wbcobs values
            wbc_cnt         := round(dbms_random.value (3000,11000));
            wbc_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
            wbc_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
 
            -- get last rx date           
            random_num2 := DBMS_RANDOM.VALUE (-1, -25);
  
            SELECT TO_CHAR (fixed_date + random_num2, 'yyyymmddhh24miss')
              INTO pp_date
              FROM DUAL;

            -- 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
                                                , TOT_DAILY_DOSAGE_VAL
                                                , TOT_DAILY_DOSAGE_UOM_CODE
                                                , TOT_DAILY_DOSAGE_UOM_DSPLYTXT
                                                , TOT_DAILY_DOSAGE_UOM_CODSYS
                                                , WBC_OBS_CNT_VAL
                                                , WBC_OBS_CNTUOM_CODE
                                                , WBC_OBS_CNTUOM_DSPLYTXT
                                                , WBC_OBS_CNTUOM_CODSYS
                                                , TESTDATE_TM
                                                , TESTDATE_TM_NBR
                                                , ANC_OBS_CNT_VAL
                                                , ANC_OBS_CNTUOM_CODE
                                                , ANC_OBS_CNTUOM_DSPLYTXT
                                                , ANC_OBS_CNTUOM_CODSYS
                                                , LAST_UPDATED_DTTZ
                                              )
                                       VALUES (
                                                  sdt_seq_id_val                            -- SPECIAL_DRUG_TRACKING_ID	
                                                , medicine_seq_id_val                       -- MEDICINE_ID	
                                                , ROUND(DBMS_RANDOM.VALUE (1,100))          -- TOT_DAILY_DOSAGE_VAL	
                                                , totdosl_code_v                            -- TOT_DAILY_DOSAGE_UOM_CODE	
                                                , totdosl_code_d                            -- TOT_DAILY_DOSAGE_UOM_DSPLYTXT	
                                                , NULL                                      -- TOT_DAILY_DOSAGE_UOM_CODSYS	
                                                , wbc_cnt                                   -- WBC_OBS_CNT_VAL	
                                                , wbc_code_v                                -- WBC_OBS_CNTUOM_VALSET_VUID	
                                                , wbc_code_d                                -- WBC_OBS_CNTUOM_DSPLYTXT	
                                                , NULL                                      -- WBC_OBS_CNTUOM_ALT_CODSYS	
                                                , pp_date                                   -- TESTDATE_TM	
                                                , TO_NUMBER(pp_date,'99999999999999')       -- TESTDATE_TM_NBR	
                                                , anc_cnt                                   -- ANC_OBS_CNT_VAL	
                                                , anc_code_v                                -- ANC_OBS_CNTUOM_VALSET_VUID	
                                                , anc_code_d                                -- ANC_OBS_CNTUOM_DSPLYTXT	
                                                , NULL                                      -- ANC_OBS_CNTUOM_ALT_CODSYS	
                                                , SYSTIMESTAMP                              -- LAST_UPDATED_DTTZ	
                                              );
         ELSE
            -- go by way of pharmacy request
            sdt_seq_id_val      := 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
                  status_code_d      := 'WAIT';
                  status_code_v      := '37';
            END CASE;

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

            orditm_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
            orditm_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
                        
            -- 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
                                            , AUTHOR_PPID
                                            , COSIGNER_PPID
                                            , ORDER_DT
                                            , ORDER_DT_NBR
                                            , IDENTIFIER_IDENTITY
                                            , IDENTIFIER_NAMESPACEID
                                            , IDENTIFIER_UNIVERSALID
                                            , IDENTIFIER_UNIVERSTYP
                                            , PHARMACY_REQ_STATUS
                                            , STATUS_MODIFIER_CODE
                                            , STATUS_MODIFIER_DSPLYTXT
                                            , STATUS_MODIFIER_CODSYS
                                            , ORDER_LOC_POINT_OF_CARE
                                            , ORDER_LOC_ROOM
                                            , ORDER_LOC_BED
                                            , ORDER_LOC_FCLTY_NMSPCID
                                            , ORDER_LOC_FCLTY_UNVSID
                                            , ORDER_LOC_FCLTY_UNVSIDTYP
                                            , ORDER_LOC_PERS_LOC_TYP
                                            , ORDER_LOC_DESCRIPTION
                                            , ORD_INST_NAME
                                            , ORD_INST_IDENTITY
                                            , ORD_INST_ASSIGNING_AUTHORITY
                                            , ITEM_CODE
                                            , ITEM_DSPLYTXT
                                            , ITEM_CODSYS
                                            , DOSAGE_FORM_CODE
                                            , DOSAGE_FORM_DSPLYTXT
                                            , DOSAGE_FORM_CODSYS
                                            , LAST_UPDATED_DTTZ
                                         )
                                  VALUES (
                                             pharmreq_seq_id_val                                            --  PHARMACY_REQUEST_ID	
                                           , medicine_seq_id_val                                            --  MEDICINE_ID	
                                           , authorppid                                                     --  GENERIC_AUTHOR_PPID	
                                           , cosignppid                                                     --  COSIGNER_PPID	
                                           , genord_date                                                    --  ORDER_DT	
                                           , TO_NUMBER(genord_date,'99999999999999')                                  --  ORDER_DT_NBR	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  IDENTIFIER_IDENTITY	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  IDENTIFIER_NAMESPACEID	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  IDENTIFIER_UNIVERSALID	
                                           , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| pharmreq_seq_id_val --  IDENTIFIER_UNIVERSTYP	
                                           , status_code_d                                                  --  GENERIC_STATUS_DSPLYTXT	
                                           , status_code_v                                                  --  GENERIC_STATUS_VALSET_VUID	
                                           , status_code_d                                                  --  GENERIC_STATUS_DSPLYTXT	
                                           , NULL                                                           --  GENERIC_STATUS_ALT_CODSYS	
                                           , ordloc_code_d                                                  --  ORD_LOCATION_VALSET_VUID	
                                           , NULL                                                           --  ORD_LOCATION_CONCEPT_VUID	
                                           , NULL                                                           --  ORD_LOCATION_DSGNTN_VUID	
                                           , ordloc_code_d                                                  --  ORD_LOCATION_DSPLYTXT	
                                           , NULL                                                           --  ORD_LOCATION_ALT_CODSYS	
                                           , NULL                                                           --  ORD_LOCATION_ALT_CODE	
                                           , NULL                                                           --  ORD_LOCATION_ALT_DSPLYTXT	
                                           , ordloc_code_d                                                  --  ORD_LOCATION_ALT_REASON	
                                           , ordinst_code_d                                                 --  ORD_INST_NAME	
                                           , ordinst_code_v                                                 --  ORD_INST_IDENTITY	
                                           , NULL                                                           --  ORD_INST_ASSIGNING_AUTHORITY	
                                           , orditm_code_v                                                  --  ITEM_CODE	
                                           , orditm_code_d                                                  --  ITEM_DSPLYTXT	
                                           , NULL                                                           --  ITEM_CODSYS	
                                           , ROUND(DBMS_RANDOM.VALUE (877977,1987432))                      --  DOSAGE_FORM_CODE	
                                           , dbms_random.string ('U',trunc(dbms_random.value(1,50)))        --  DOSAGE_FORM_DSPLYTXT	
                                           , NULL                                                           --  DOSAGE_FORM_CODSYS	
                                           , SYSTIMESTAMP                                                   --  LAST_UPDATED_DTTZ	                        
                                         );  
         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);
         birthnum    := DBMS_random.VALUE (-10950, -36500);

         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 + birthnum, '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,
                birth_dt,
                ppv_date 
           FROM DUAL;
        
         -- get DATAENTERER practitioner
         SELECT pract_part_seq_id.NEXTVAL
           INTO dataentppid
           FROM DUAL;
        
         insert_practpart.insert_message( ppd_date, dataentppid );

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

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

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

         IF random_num = 1
         THEN
             SELECT pract_part_seq_id.NEXTVAL
               INTO cancelppid
               FROM DUAL;
            
             insert_practpart.insert_message( ppv_date, cancelppid );
         ELSE
             cancelppid := 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 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 Ethnic Characteristic information
         random_num := round(DBMS_RANDOM.VALUE (1, 4));
         CASE random_num
            WHEN '1' THEN
             ethnic_code_d    := 'HISPANIC';
             ethnic_code_v    := '1238923';
            WHEN '2' THEN
             ethnic_code_d    := 'SPANISH';
             ethnic_code_v    := '1238924';
            WHEN '3' THEN
             ethnic_code_d    := 'AFRICAN AMERICAN';
             ethnic_code_v    := '1238925';
            ELSE
             ethnic_code_d    := 'ORIENTAL';
             ethnic_code_v    := '1238926';
         END CASE;

         -- Set gender
         random_num := round(DBMS_RANDOM.VALUE (1, 3));
         CASE random_num
            WHEN '1' THEN
               gender_code_d := 'MALE';
               gender_code_v := '1209109';
            WHEN '2' THEN
               gender_code_d := 'FEMALE';
               gender_code_v := '1209110';
            ELSE
               gender_code_d := 'UNKNOWN';
               gender_code_v := '1209111';
         END CASE;
         
         -- Set race code
         random_num := round(DBMS_RANDOM.VALUE (1, 7));
         CASE random_num
            WHEN '1' THEN
               race_code_d  := 'WHITE';
               race_code_v  := '21389023';
            WHEN '2' THEN
               race_code_d  := 'AFRICAN AMERICAN';
               race_code_v  := '21389024';
            WHEN '3' THEN
               race_code_d  := 'AMERICAN INDIAN';
               race_code_v  := '21389025';
            WHEN '4' THEN
               race_code_d  := 'JAPANESE';
               race_code_v  := '21389029';
            WHEN '5' THEN
               race_code_d  := 'KOREAN';
               race_code_v  := '21389030';
            WHEN '6' THEN
               race_code_d  := 'SAMOAN';
               race_code_v  := '21389021';
            ELSE
               race_code_d  := 'OTHER WHITE';
               race_code_v  := '21389022';
         END CASE;
         
         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
         
         -- Load Patient Record
         INSERT INTO PATIENT_information (  
                                              PATIENT_INFORMATION_ID
                                            , GENDER_CODE
                                            , GENDER_DSPLYTXT
                                            , GENDER_CODSYS
                                            , BIRTHDATE_DT
                                            , BIRTHDATE_DT_NBR
                                            , ETHNIC_GRP_CODE
                                            , ETHNIC_GRP_DSPLYTXT
                                            , ETHNIC_GRP_CODSYS
                                            , NAME_PREFIX
                                            , NAME_GIVEN
                                            , NAME_FAMILY
                                            , NAME_SUFFIX
                                            , NAME_TITLE
                                            , NAME_MIDDLE
                                            , RACE_CODE
                                            , RACE_DSPLYTXT
                                            , RACE_CODSYS
                                            , ZIP_PLUS4
                                            , LAST_UPDATED_DTTZ
                              )
         VALUES (
                   patient_seq_id_val                                          -- PATIENT_ID
                 , gender_code_v
                 , gender_code_d
                 , NULL --GENDER CODSYS
                 , birth_dt                                                     -- BIRTHDATE_DT	
                 , to_number(birth_dt,'99999999999999')                         -- BIRTHDATE_DT_nbr
                 , ethnic_code_v
                 , ethnic_code_d
                 , NULL --ETHNIC CODSYS
                 , NULL                                                        -- PREFIX_NM	
                 , NULL                                                        -- GIVEN_NM	
                 , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                 , NULL                                                        -- SUFFIX_NM	
                 , NULL                                                        -- TITLE_NM	
                 , NULL                                                        -- MIDDLE_NM	
                 , race_code_v
                 , race_code_d
                 , NULL --RACE CODSYS
                 , ROUND(DBMS_RANDOM.VALUE(10000,99999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999))  --ZIP_PLUS4
                 , SYSTIMESTAMP                                                -- last_updated_dttz
                );
         
         -- Load record into outpatient_med_prm table
         INSERT INTO OUTPATIENT_MED_PRM (
                                          OUTPATIENT_MED_PRM_ID
                                        , PATIENT_IDENTITY
                                        , PATIENT_ASSIGNING_FACILITY
                                        , PATIENT_ASSIGNING_AUTHORITY
                                        , PATIENT_INFORMATION_ID
                                        , PHARMACY_REQUEST_ID
                                        , SPECIAL_DRUG_TRACKING_ID
                                        , DATA_ENTERER_PPID
                                        , RENEWER_PPID
                                        , FINISHER_PPID
                                        , CANCELLER_PPID
                                        , ORIGINAL_DISPENSE_OPMDSPID
                                        , REQUEST_ID
                                        , RECORDIDENTIFIER_IDENTITY
                                        , RECORDIDENTIFIER_NAMESPACEID
                                        , RECORDIDENTIFIER_UNIVERSALID
                                        , RECORDIDENTIFIER_UNIVERSTYP
                                        , PDTS_PRESCRIPTION_ID
                                        , PRESCRIPTION_ID
                                        , PHARMACY_PATIENT_TYP
                                        , COPAY_TX_TYP_CODE
                                        , COPAY_TX_TYP_DSPLYTXT
                                        , COPAY_TX_TYP_CODSYS
                                        , OUTPMED_EXPIRATION_DT
                                        , OUTPMED_EXPIRATION_DT_NBR
                                        , LAST_DISPENSE_DT
                                        , LAST_DISPENSE_DT_NBR
                                        , NUM_REFILLS_AUTHORIZED
                                        , EXPANDED_PATIENT_INSTRUCTIONS
                                        , OTHER_PATIENT_INSTRUCTIONS
                                        , PATIENT_INSTRUCTIONS
                                        , PRESCRIPTION_SIG_TXT
                                        , TRANS_RX_BENEFIT_CODE
                                        , TRANS_RX_BENEFIT_DSPLYTXT
                                        , TRANS_RX_BENEFIT_CODSYS
                                        , CANCEL_DT
                                        , CANCEL_DT_NBR
                                        , CANCEL_COMMENT
                                        , RECORD_VERSION
                                        , RECORD_UPDATE_TM
                                        , RECORD_UPDATE_TM_NBR
                                        , LAST_UPDATED_DTTZ
                                        )
                                 VALUES (
                                          outpmed_seq_id_val                                -- OUTPATIENT_MED_PRM_ID	
                                        , v_vpid                                            -- PATIENT_IDENTITY
                                        , 'VA.GOV-605'                                      -- PATIENT_ASSIGNING_FACILITY
                                        , 'USVHA'                                           -- PATIENT_ASSIGNING_AUTHORITY
                                        , 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	
                                        , renewppid                                         -- RENEWER_PPID	
                                        , finishppid                                        -- FINISHER_PPID
                                        , cancelppid                                        -- cancel_PPID	
                                        , NULL                                              -- ORIGINAL_DISPENSE_OPMDSPID
                                        , NULL                                              -- REQUEST_ID
                                        , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- RECORDIDENTIFIER_IDENTITY	
                                        , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- RECORDIDENTIFIER_NAMESPACEID	
                                        , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- RECORDIDENTIFIER_UNIVERSALID	
                                        , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val -- RECORDIDENTIFIER_UNIVERSTYP	
                                        , dbms_random.string ('X',trunc(dbms_random.value(10,50))) -- PDTS_PRESCRIPTION_ID	
                                        , dbms_random.string ('U',trunc(dbms_random.value(10,30))) -- PRESCRIPTION_ID	
                                        , dbms_random.string ('U',trunc(dbms_random.value(10,30))) -- PHARMACY_PATIENT_TYP	
                                        , copay_code_v                                      -- COPAY_TX_TYP_VALSET_VUID	
                                        , copay_code_d                                      -- COPAY_TX_TYP_DSPLYTXT	
                                        , NULL                                  -- COPAY_TX_TYP_ALT_CODSYS	
                                        , exp_date                                          -- OUTPMED_EXPIRATION_DT	
                                        , TO_NUMBER(EXP_DATE,'99999999999999')              -- OUTPMED_EXPIRATION_DT_NBR
                                        , lastdisp_date                                     -- LAST_DISPENSE_DT	
                                        , TO_NUMBER(lastdisp_date,'99999999999999')         -- LAST_DISPENSE_DT_NBR
                                        , round(dbms_random.value(1,6))                     -- NUM_REFILLS_AUTHORIZED_LOW	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,1000))) -- EXPANDED_PATIENT_INSTRUCTIONS	
                                        , 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	
                                        , round(dbms_random.value(250004,350003))
                                        , dbms_random.string ('U',trunc(dbms_random.value(10,30)))
                                        , NULL
                                        , cancel_date                                       -- CANCEL_DT	
                                        , TO_NUMBER(cancel_date,'99999999999999')           -- CANCEL_DT_NBR
                                        , dbms_random.string ('U',trunc(dbms_random.value(100,2000))) -- CANCEL_COMMENT	
                                        , 1                                                 -- RECORD_VERSION	
                                        , recupd_date                                       -- RECORD_UPDATE_TM	
                                        , TO_NUMBER(RECUPD_DATE,'99999999999999')           -- RECORD_UPDATE_TM	
                                        , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                        );
         
         -- process TREATMENT_FACTOR 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
                
                -- get benefit_assignment seq id
                SELECT treatment_factor_seq_id.NEXTVAL
                  INTO treat_seq_id_val
                  FROM DUAL;
                
                -- Insert benefit_assignment table
                INSERT INTO TREATMENT_FACTOR (
                                                  TREATMENT_FACTOR_ID
                                                , OUTPATIENT_MED_PRM_ID
                                                , FACTOR_CODE
                                                , FACTOR_DSPLYTXT
                                                , FACTOR_CODSYS
                                                , FACTOR_VAL_CODE
                                                , FACTOR_VAL_DSPLYTXT
                                                , FACTOR_VAL_CODSYS
                                                , LAST_UPDATED_DTTZ
                                               )
                                        VALUES (
                                                  treat_seq_id_val                                 -- BENEFIT_ASSIGNMENT_ID	
                                                , outpmed_seq_id_val                                -- OUTPATIENT_MED_PRM_ID
                                                , round(dbms_random.value(250004,250008))
                                                , dbms_random.string ('U',trunc(dbms_random.value(10,50)))	
                                                , NULL
                                                , round(dbms_random.value(150304,150308))
                                                , dbms_random.string ('U',trunc(dbms_random.value(5,25)))	
                                                , NULL
                                                , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                               );
            END LOOP;
         END IF;
         
         -- process medication_instruction table
         FOR j IN 1 .. ROUND (DBMS_RANDOM.VALUE (1, 3))
         LOOP
             -- 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
                                                , CONJUNCTION_CODE
                                                , CONJUNCTION_DSPLYTXT
                                                , CONJUNCTION_CODSYS
                                                , DISPENSE_UNITS_PER_DOSE
                                                , DOSAGE_ORDERED
                                                , NOUN_CODE
                                                , NOUN_DSPLYTXT
                                                , NOUN_CODSYS
                                                , ROUTE_CODE
                                                , ROUTE_DSPLYTXT
                                                , ROUTE_CODSYS
                                                , SCHEDULE_CODE
                                                , SCHEDULE_DSPLYTXT
                                                , SCHEDULE_CODSYS
                                                , VERB_CODE
                                                , VERB_DSPLYTXT
                                                , VERB_CODSYS
                                                , GIVE_UNITS_CODE
                                                , GIVE_UNITS_DSPLYTXT
                                                , GIVE_UNITS_CODSYS
                                                , INTENDED_DURATION
                                                , LAST_UPDATED_DTTZ
                                                )
                                         VALUES (
                                                   medinst_seq_id_val                               -- MEDICATION_INSTRUCTION_ID	
                                                 , outpmed_seq_id_val                               -- OUTPATIENT_MED_PRM_ID
                                                 , round(dbms_random.value(50304,50308))  -- CONJUCTION CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , ROUND(DBMS_RANDOM.VALUE(10,100)) -- DISPENSE_UNITS_PER_DOSE
                                                 , ROUND(DBMS_RANDOM.VALUE(100,500)) -- DOSAGE_ORDERED
                                                 , round(dbms_random.value(4877,4880))  -- NOUN CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , round(dbms_random.value(8990,8999))  -- ROUTE CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , round(dbms_random.value(124578,124589))  -- SCHEDULE CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , round(dbms_random.value(3333,3344))  -- VERB CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , round(dbms_random.value(5656,5665))  -- GIVE UNITS CODE
                                                 , dbms_random.string ('U',trunc(dbms_random.value(5,30)))	
                                                 , NULL
                                                 , '100 DAYS'
                                                 , 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;
             
         qtydis_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
         qtydis_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             
         lvlund_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
         lvlund_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));
             
         mailw_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
             
         ncpdp_code_d      := dbms_random.string ('U',trunc(dbms_random.value(1,100)));
         ncpdp_code_v      := ROUND(DBMS_RANDOM.VALUE (1,99999999));

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

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

            -- get 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
                                            , CURRENT_PROVIDER_PPID
                                            , COMMENTS
                                            , DISPENSE_DT
                                            , DISPENSE_DT_NBR
                                            , FILL_DT
                                            , FILL_DT_NBR
                                            , HX_QTY_DISPENSED
                                            , QTYDISPENSED_VAL
                                            , QTYDISPENSED_UOM_CODE
                                            , QTYDISPENSED_UOM_DSPLYTXT
                                            , QTYDISPENSED_UOM_CODSYS
                                            , DAYS_SUPPLY
                                            , IDENTIFIER_IDENTITY
                                            , IDENTIFIER_NAMESPACEID
                                            , IDENTIFIER_UNIVERSALID
                                            , IDENTIFIER_UNIVERSTYP
                                            , LVLUNDERSTANDING_CODE
                                            , LVLUNDERSTANDING_DSPLYTXT
                                            , LVLUNDERSTANDING_CODSYS
                                            , MAIL_WINDOW
                                            , RELEASE_DT
                                            , RELEASE_DT_NBR
                                            , PATIENT_COUNSELED_CODE
                                            , PATIENT_COUNSELED_DSPLYTXT
                                            , PATIENT_COUNSELED_CODSYS
                                            , CMOP_EVNT_STATUS_CODE
                                            , CMOP_EVNT_STATUS_DSPLYTXT
                                            , CMOP_EVNT_STATUS_CODSYS
                                            , DIVISION_ID_CODE
                                            , DIVISION_ID_DSPLYTXT
                                            , DIVISION_ID_CODSYS
                                            , DIV_NCPDPID_CODE
                                            , DIV_NCPDPID_DSPLYTXT
                                            , DIV_NCPDPID_CODSYS
                                            , DRUG_UNITPRICE_VAL
                                            , DRUG_UNITPRICE_CODE
                                            , DRUG_UNITPRICE_DSPLYTXT
                                            , DRUG_UNITPRICE_CODSYS
                                            , EXPIRATION_DT
                                            , EXPIRATION_DT_NBR
                                            , LOT_ID
                                            , NDC_CODE
                                            , NDC_DSPLYTXT
                                            , NDC_CODSYS
                                            , 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                                        -- CURRENT_PROVIDER_PPID	
                                            , round(dbms_random.value(20,500))                  -- COMMENTS	
                                            , disp_date                                         -- DISPENSE_DT	
                                            , TO_NUMBER(DISP_DATE,'99999999999999')             -- DISPENSE_DT_NBR	
                                            , prescription_date                                 -- FILL_DT	
                                            , TO_NUMBER(prescription_date,'99999999999999')     -- FILL_DT_NBR	
                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- HX_QTY_DISPENSED	
                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- QTYDISPENSED_VAL	
                                            , qtydis_code_v                                     -- QTYDISPENSED_UOM_VALSET_VUID	
                                            , qtydis_code_d                                     -- QTYDISPENSED_UOM_DSPLYTXT	
                                            , NULL                                 -- QTYDISPENSED_UOM_ALT_CODSYS	
                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- DAYS_SUPPLY	
                                            , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTIFIER_IDENTITY	
                                            , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTIFIER_NAMESPACEID	
                                            , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTIFIER_UNIVERSALID	
                                            , 'FILLER'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpdsp_seq_id_val -- IDENTIFIER_UNIVERSTYP	
                                            , lvlund_code_v                                     -- LVLUNDERSTANDING_VALSET_VUID	
                                            , lvlund_code_d                                     -- LVLUNDERSTANDING_DSPLYTXT	
                                            , NULL                                 -- LVLUNDERSTANDING_ALT_CODSYS	
                                            , mailw_code_d                                      -- MAIL_WINDOW_DSPLYTXT	
                                            , release_date                                      -- RELEASE_DT	
                                            , TO_NUMBER(release_date,'99999999999999')          -- RELEASE_DT_NBR
                                            , '200'
                                            , 'T'                                               -- WAS_PATIENT_COUNSELED_VAL	
                                            , NULL                                              -- WAS_PATIENT_COUNSELED_NULLFLV	
                                            , status_code_v                                     --STATUS
                                            , status_code_d
                                            , NULL
                                            , ROUND(DBMS_RANDOM.VALUE(785,7888)) -- DIVISIONID
                                            , dbms_random.string ('U',trunc(dbms_random.value(5,30)))
                                            , NULL
                                            , ncpdp_code_v                                      -- DIV_NCPDPID_VALSET_VUID	
                                            , ncpdp_code_d                                      -- DIV_NCPDPID_DSPLYTXT	
                                            , NULL                                  -- DIV_NCPDPID_ALT_CODSYS	
                                            , ROUND(DBMS_RANDOM.VALUE(5.50,560.34),2)           -- DRUG_UNITPRICE_VAL	
                                            , ROUND(DBMS_RANDOM.VALUE(785,7888)) -- DRUG UNITPRICE
                                            , dbms_random.string ('U',trunc(dbms_random.value(5,30)))
                                            , NULL
                                            , expl_date                                         -- EXPIRATION_DT_LOW	
                                            , TO_NUMBER(expl_date,'99999999999999')             -- EXPIRATION_DT_LOW_NBR
                                            , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- LOT_ID	
                                            , ROUND(DBMS_RANDOM.VALUE(8232,8289)) --NDC CODE
                                            , dbms_random.string ('U',trunc(dbms_random.value(1,70))) -- MANUFACTURER_CONTROL_NBR	
                                            , NULL
                                            , SYSTIMESTAMP                                      -- LAST_UPDATED_DTTZ	
                                           );
                                           
            IF outpat_med_dsp = 1 
            THEN
               UPDATE OUTPATIENT_MED_PRM SET ORIGINAL_DISPENSE_OPMDSPID = outpdsp_seq_id_val, last_updated_dttz = systimestamp
               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;
/