/******************************************************************************
   NAME:       load_outppharm_load4x_arms.sql
   PURPOSE:    This script will create Outpatient Pharmacy records that map to 
               VHIM version 4x 
   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        01/08/2008  M. Jacobsen      - remove common schema
   1.4        01/25/2008  M. Jacobsen      - collapse Patient Info, practitioner participation and medicine tables
   1.5        04/01/2008  M. Jacobsen      - break out patient_instruction fields into new table
   1.6        06/17/2008  M. Jacobsen      - change date number format to YYMMDD.hhmiss
   1.7        03/31/2009  M. Jacobsen      - v2.1 changes
   1.8        04/05/2010  M. Jacobsen      - v2.1.3 changes; add activity_log and pharmacy_instruction
   1.9        01/04/2012  M. Jacobsen      - v3.2 changes; change assigning facility and location to only load CHDR data
   1.10       03/01/2013  M. Jacobsen      - v3.4p changes; load VistA and CHDR; populate pr_medication_codsys with 99VHA_ERT
******************************************************************************/
set serveroutput on
DECLARE
   route                    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);
   outppharm_practpartseqid_val    NUMBER; 
   outppharm_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                  VARCHAR2(20);
   pract_vpid               VARCHAR2 (30);
   ppa_date                 VARCHAR2 (30);
   ppc_date                 VARCHAR2 (30);
   ppa1_date                VARCHAR2 (30);
   ppc1_date                VARCHAR2 (30);
   ppa2_date                VARCHAR2 (30);
   ppc2_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);
   med2_code_d              VARCHAR2 (100);
   med2_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);
   status2_code_d            VARCHAR2 (100);
   status2_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;
   r_pers_id          VARCHAR2(100);
   r_pers_fclty       VARCHAR2(100);
   r_pract_comments   VARCHAR2(4000);
   r_pract_tm         VARCHAR2(100);
   r_pract_tm_nbr     NUMBER;
   r_practidsrc       VARCHAR2(100);
   r_name_fam         VARCHAR2(100);
   p_pers_id          VARCHAR2(100);
   p_pers_fclty       VARCHAR2(100);
   p_pract_comments   VARCHAR2(4000);
   p_pract_tm         VARCHAR2(100);
   p_pract_tm_nbr     NUMBER;
   p_practidsrc       VARCHAR2(100);
   p_name_fam         VARCHAR2(100);
   f_pers_id          VARCHAR2(100);
   f_pers_fclty       VARCHAR2(100);
   f_pract_comments   VARCHAR2(4000);
   f_pract_tm         VARCHAR2(100);
   f_pract_tm_nbr     NUMBER;
   f_practidsrc       VARCHAR2(100);
   f_name_fam         VARCHAR2(100);
   c_pers_id          VARCHAR2(100);
   c_pers_fclty       VARCHAR2(100);
   c_pract_comments   VARCHAR2(4000);
   c_pract_tm         VARCHAR2(100);
   c_pract_tm_nbr     NUMBER;
   c_practidsrc       VARCHAR2(100);
   c_name_fam         VARCHAR2(100);
   c_discriminator    varchar2(1);
   c_outp_med_id      NUMBER;
   c_dsp_linenbr      number;
   v_pat_fac          varchar2(256);
   v_pat_auth         varchar2(256);
   
BEGIN

   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   '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
         
         -- 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 finisher practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));

         IF random_num = 1
         THEN
             f_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             f_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             f_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             f_pract_tm         := ppa_date;
             f_pract_tm_nbr     := (to_number(ppa_date,'99999999999999')-17000000000000)/1000000; 
--            f_pract_tm_nbr  := to_number(to_char(to_number(substr(ppa_date,1,4)-1700)) || substr(ppa_date,5,4) || '.' || substr(ppa_date,9,length(ppa_date)-8));
             f_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             f_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         ELSE
             f_pers_id          := NULL;
             f_pers_fclty       := NULL;
             f_pract_comments   := NULL;
             f_pract_tm         := NULL;
             f_pract_tm_nbr     := NULL;
             f_practidsrc       := NULL;
             f_name_fam         := NULL;
         END IF;

         -- get outpatient med promise seq id
         SELECT outpatient_med_prm_seq_id.NEXTVAL
           INTO outpmed_seq_id_val
           FROM DUAL;

         -- 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,
                ppa1_date,
                ppc2_date
           FROM DUAL;

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

         IF random_num = 1
         THEN
             p_pers_id          := NULL;
             p_pers_fclty       := NULL;
             p_pract_comments   := NULL;
             p_pract_tm         := NULL;
             p_pract_tm_nbr     := NULL;
             p_practidsrc       := NULL;
             p_name_fam         := NULL;
         ELSE
             p_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             p_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             p_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             p_pract_tm         := ppc2_date;
             p_pract_tm_nbr     := (to_number(ppc2_date,'99999999999999')-17000000000000)/1000000;
--            p_pract_tm_nbr  := to_number(to_char(to_number(substr(ppc2_date,1,4)-1700)) || substr(ppc2_date,5,4) || '.' || substr(ppc2_date,9,length(ppc2_date)-8));
             p_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             p_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         END IF;
        
         -- 1.4 ADD SPECIAL DRUG TRACKING INFO
         -- 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));
      
         -- Set status_code_displayname
         random_num := round(DBMS_RANDOM.VALUE (1, 3));

         CASE random_num
            WHEN '1' THEN
               status2_code_d := 'ACTIVE';
               status2_code_v := '25';
            WHEN '2' THEN
               status2_code_d := 'OPEN';
               status2_code_v := '11';
            ELSE
               status2_code_d      := 'WAIT';
               status2_code_v      := '37';
         END CASE;
    
         -- set wbcobs values
         wbc_cnt         := TO_CHAR(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 outpatient_med_dsp_seq_id
         SELECT outpatient_med_dsp_seq_id.NEXTVAL
           INTO outpdsp_seq_id_val
           FROM DUAL;

         c_discriminator := 'O';
         c_outp_med_id  := 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;
         
         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 refill practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
             f_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             f_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             f_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             f_pract_tm         := refill_date;
             f_pract_tm_nbr     := (to_number(refill_date,'99999999999999')-17000000000000)/1000000;
--            f_pract_tm_nbr  := to_number(to_char(to_number(substr(refill_date,1,4)-1700)) || substr(refill_date,5,4) || '.' || substr(refill_date,9,length(refill_date)-8));
             f_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             f_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         ELSE
             f_pers_id          := NULL;
             f_pers_fclty       := NULL;
             f_pract_comments   := NULL;
             f_pract_tm         := NULL;
             f_pract_tm_nbr     := NULL;
             f_practidsrc       := NULL;
             f_name_fam         := NULL;
         END IF;
         
         -- get pharmacist practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
             c_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             c_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             c_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             c_pract_tm         := pharm_date;
             c_pract_tm_nbr     := (to_number(pharm_date,'99999999999999')-17000000000000)/1000000;
--            c_pract_tm_nbr  := to_number(to_char(to_number(substr(pharm_date,1,4)-1700)) || substr(pharm_date,5,4) || '.' || substr(pharm_date,9,length(pharm_date)-8));
             c_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             c_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         ELSE
             c_pers_id          := NULL;
             c_pers_fclty       := NULL;
             c_pract_comments   := NULL;
             c_pract_tm         := NULL;
             c_pract_tm_nbr     := NULL;
             c_practidsrc       := NULL;
             c_name_fam         := NULL;
         END IF;
         
         v_vpid := 'test_sourceid_' || to_char(jj + starting_patient_vpid);
		 
-- 1.10 mgj load VistA and CHDR data
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
			v_pat_fac   := '200CH';
			v_pat_auth  := 'USDOD';		 
         ELSE
			v_pat_fac   := '605';
			v_pat_auth  := 'USVHA';
		 END IF;
									   
         -- Load record into outpatient_med_prm table
         INSERT INTO OUTPATIENT_MED_PRM30_v (
                                          OUTPATIENT_MED_PRM_ID
                                        , PATIENT_IDENTITY
                                        , PATIENT_ASSIGNING_FACILITY
                                        , PATIENT_ASSIGNING_AUTHORITY
                                        , 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
                                        , TRANS_RX_BENEFIT_CODE
                                        , TRANS_RX_BENEFIT_DSPLYTXT
                                        , TRANS_RX_BENEFIT_CODSYS
                                        , CANCEL_DT
                                        , CANCEL_DT_NBR
                                        , PATIENT_NAME_PREFIX
                                        , PATIENT_NAME_GIVEN
                                        , PATIENT_NAME_FAMILY
                                        , PATIENT_NAME_SUFFIX
                                        , PATIENT_NAME_TITLE
                                        , PATIENT_NAME_MIDDLE
                                        , DATA_PERSON_IDENTITY           
                                        , DATA_PERSON_ASGNING_FACILITY
                                        , DATA_PERSON_ASGNING_AUTHORITY
                                        , DATA_PRACTITIONER_COMMENTS
                                        , DATA_PRACTITIONER_TM 
                                        , DATA_PRACTITIONER_TM_NBR
                                        , DATA_PRACTITIONER_ID_SRC_TBL
                                        , DATA_NAME_PREFIX
                                        , DATA_NAME_GIVEN
                                        , DATA_NAME_FAMILY
                                        , DATA_NAME_SUFFIX
                                        , DATA_NAME_TITLE
                                        , DATA_NAME_MIDDLE                   
                                        , FNSH_PERSON_IDENTITY               
                                        , FNSH_PERSON_ASGNING_FACILITY
                                        , FNSH_PERSON_ASGNING_AUTHORITY
                                        , FNSH_PRACTITIONER_COMMENTS
                                        , FNSH_PRACTITIONER_TM 
                                        , FNSH_PRACTITIONER_TM_NBR
                                        , FNSH_PRACTITIONER_ID_SRC_TBL
                                        , FNSH_NAME_PREFIX
                                        , FNSH_NAME_GIVEN
                                        , FNSH_NAME_FAMILY
                                        , FNSH_NAME_SUFFIX
                                        , FNSH_NAME_TITLE
                                        , FNSH_NAME_MIDDLE                    
                                        , 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
                                        , pr_CMOPDRUG_CODE                         
                                        , pr_CMOPDRUG_DSPLYTXT
                                        , pr_CMOPDRUG_CODSYS
                                        , pr_MEDICATION_CODE
                                        , pr_MEDICATION_DSPLYTXT
                                        , pr_MEDICATION_CODSYS
                                        , pr_MEDICATION_ALT_CODE
                                        , pr_MEDICATION_ALT_DSPLYTXT
                                        , pr_MEDICATION_ALT_CODSYS
                                        , pr_OTC_CODE
                                        , pr_OTC_DSPLYTXT
                                        , pr_OTC_CODSYS
                                        , pr_TRADE_NAME_CODE
                                        , pr_TRADE_NAME_DSPLYTXT
                                        , pr_TRADE_NAME_CODSYS
                                        , COSN_PERSON_IDENTITY                
                                        , COSN_PERSON_ASGNING_FACILITY
                                        , COSN_PERSON_ASGNING_AUTHORITY
                                        , COSN_PRACTITIONER_COMMENTS
                                        , COSN_PRACTITIONER_TM 
                                        , COSN_PRACTITIONER_TM_NBR
                                        , COSN_PRACTITIONER_ID_SRC_TBL
                                        , COSN_NAME_PREFIX
                                        , COSN_NAME_GIVEN
                                        , COSN_NAME_FAMILY
                                        , COSN_NAME_SUFFIX
                                        , COSN_NAME_TITLE
                                        , COSN_NAME_MIDDLE                    
                                        , 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
                                        , RECORDSOURCE_NAMESPACEID
                                        , RECORDSOURCE_UNIVERSALID
                                        , RECORDSOURCE_UNIVERSTYP
                                        , RECORD_VERSION
                                        , RECORD_UPDATE_TM
                                        , RECORD_UPDATE_TM_NBR
                                        , DIGITAL_SIGNATURE_CODE
                                        , DIGITAL_SIGNATURE_DSPLYTXT
                                        , DIGITAL_SIGNATURE_CODSYS
                                        )
                                 VALUES (
                                          outpmed_seq_id_val                                -- OUTPATIENT_MED_PRM_ID	
                                        , v_vpid                                            -- PATIENT_IDENTITY
                                        , v_pat_fac                                         -- PATIENT_ASSIGNING_FACILITY 
                                        , v_pat_auth                                        -- PATIENT_ASSIGNING_AUTHORITY  
                                        , 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')-17000000000000)/1000000              -- OUTPMED_EXPIRATION_DT_NBR
--                      , to_number(to_char(to_number(substr(EXP_DATE,1,4)-1700)) || substr(EXP_DATE,5,4) || '.' || substr(EXP_DATE,9,length(EXP_DATE)-8))
                                        , lastdisp_date                                     -- LAST_DISPENSE_DT	
                                        , (TO_NUMBER(lastdisp_date,'99999999999999')-17000000000000)/1000000         -- LAST_DISPENSE_DT_NBR
--                      , to_number(to_char(to_number(substr(lastdisp_date,1,4)-1700)) || substr(lastdisp_date,5,4) || '.' || substr(lastdisp_date,9,length(lastdisp_date)-8))
                                        , round(dbms_random.value(1,6))                     -- NUM_REFILLS_AUTHORIZED_LOW	
                                        , 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')-17000000000000)/1000000           -- CANCEL_DT_NBR
--                      , to_number(to_char(to_number(substr(cancel_date,1,4)-1700)) || substr(cancel_date,5,4) || '.' || substr(cancel_date,9,length(cancel_date)-8))
                                        , NULL                                                        -- PREFIX_NM	
                                        , NULL                                                        -- GIVEN_NM	
                                        , dbms_random.string ('U',trunc(dbms_random.value(8,30)))     -- FAMILY_NM	
                                        , NULL                                                        -- SUFFIX_NM	
                                        , NULL                                                        -- TITLE_NM	
                                        , NULL                                                        -- MIDDLE_NM	
                                        , 'test_id_' || to_char(round(dbms_random.value(1,1000)))       -- DATA_PERSON_IDENTITY  1.5 START
                                        , 'visn ' || to_char(round(dbms_random.value(1,21)))            -- DATA_PERSON_ASGNING_FACILITY
                                        , null                                                          -- DATA_PERSON_ASGNING_AUTHORITY
                                        , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )  -- DATA_practitioner_comments
                                        , ppa_date                                                      -- DATA_PRACTITIONER_TM
                                        , (to_number(ppa_date,'99999999999999')-17000000000000)/1000000                          -- DATA_PRACTITIONER_TM_NBR
--                      , to_number(to_char(to_number(substr(ppa_date,1,4)-1700)) || substr(ppa_date,5,4) || '.' || substr(ppa_date,9,length(ppa_date)-8))
                                        , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )   -- DATA_practitioner_id_src_tbl
                                        , NULL                                                          -- DATA_NAME_PREFIX	
                                        , NULL                                                          -- DATA_NAME_GIVEN		
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,40)))       -- DATA_NAME_FAMILY	
                                        , NULL                                                          -- DATA_NAME_SUFFIX	
                                        , NULL                                                          -- DATA_NAME_TITLE	
                                        , NULL                                                          -- DATA_NAME_MIDDLE	
                                        , f_pers_id                                                     -- FNSH_PERSON_IDENTITY  1.5 START
                                        , f_pers_fclty                                                  -- FNSH_PERSON_ASGNING_FACILITY
                                        , null                                                          -- FNSH_PERSON_ASGNING_AUTHORITY
                                        , f_pract_comments                                              -- FNSH_practitioner_comments                      
                                        , f_pract_tm                                                    -- FNSH_PRACTITIONER_TM
                                        , f_pract_tm_nbr                                                -- FNSH_PRACTITIONER_TM_NBR
                                        , f_practidsrc                                                  -- FNSH_practitioner_id_src_tbl
                                        , NULL                                                          -- FNSH_NAME_PREFIX	
                                        , NULL                                                          -- FNSH_NAME_GIVEN		
                                        , f_name_fam                                                    -- FNSH_NAME_FAMILY	
                                        , NULL                                                          -- FNSH_NAME_SUFFIX	
                                        , NULL                                                          -- FNSH_NAME_TITLE	
                                        , NULL                                                          -- FNSH_NAME_MIDDLE	
                                        , genord_date                                                    --  ORDER_DT	
                                        , (TO_NUMBER(genord_date,'99999999999999')-17000000000000)/1000000                                  --  ORDER_DT_NBR	
 --                      , to_number(to_char(to_number(substr(genord_date,1,4)-1700)) || substr(genord_date,5,4) || '.' || substr(genord_date,9,length(genord_date)-8))
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val --  IDENTIFIER_IDENTITY	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val --  IDENTIFIER_NAMESPACEID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_seq_id_val --  IDENTIFIER_UNIVERSALID	
                                        , 'VISN'||ROUND(DBMS_RANDOM.VALUE(1, 21))||'_'||ROUND(DBMS_RANDOM.VALUE (1,99999999))||'.'|| outpmed_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		                        
                                        , 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                                                             -- PR_MEDICATION_CODE	
                                        , med_code_d                                                             -- PR_MEDICATION_DSPLYTXT	
                                        , '99VHA_ERT'                                                            -- PR_MEDICATION_CODSYS
                                        , 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	
                                        , p_pers_id                                                     -- COSN_PERSON_IDENTITY  1.4 START
                                        , p_pers_fclty                                                  -- COSN_PERSON_ASGNING_FACILITY
                                        , null                                                          -- COSN_PERSON_ASGNING_AUTHORITY
                                        , p_pract_comments                                              -- COSN_practitioner_comments
                                        , p_pract_tm                                                    -- COSN_PRACTITIONER_TM
                                        , p_pract_tm_nbr                                                -- COSN_PRACTITIONER_TM_NBR
                                        , p_practidsrc                                                  -- COSN_practitioner_id_src_tbl
                                        , NULL                                                          -- COSN_NAME_PREFIX	
                                        , NULL                                                          -- COSN_NAME_GIVEN		
                                        , p_name_fam                                                    -- COSN_NAME_FAMILY	
                                        , NULL                                                          -- COSN_NAME_SUFFIX	
                                        , NULL                                                          -- COSN_NAME_TITLE	
                                        , NULL                                                          -- COSN_NAME_MIDDLE	
                                        , TO_CHAR(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')-17000000000000)/1000000       -- TESTDATE_TM_NBR	
                                        , '605'
                                        , NULL
                                        , NULL
                                        , 1                                                 -- RECORD_VERSION	
                                        , recupd_date                                       -- RECORD_UPDATE_TM	
                                        , (TO_NUMBER(RECUPD_DATE,'99999999999999')-17000000000000)/1000000           -- RECORD_UPDATE_TM		
--                      , to_number(to_char(to_number(substr(RECUPD_DATE,1,4)-1700)) || substr(RECUPD_DATE,5,4) || '.' || substr(RECUPD_DATE,9,length(RECUPD_DATE)-8))
                                        , ROUND(DBMS_RANDOM.VALUE (8771977,12987432))                    --  DIGITAL_SIGNATURE_CODE	
                                        , dbms_random.string ('U',trunc(dbms_random.value(1,50)))        --  DIGITAL_SIGNATURE_DSPLYTXT	
                                        , NULL                                                           --  DIGITAL_SIGNATURE_CODSYS		                        
                                        );

         -- load pharmacy_instruction table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR pharmi IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO PHARMACY_INSTRUCTION_V (
                                                     outpatient_med_prm_id
                                                   , PHARMACY_INSTRUCTIONS
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(1,500))) -- PHARMACY_INSTRUCTIONS	
                        , pharmi
                       );
            end loop;
         END IF;

         -- load activity_log table
         FOR actlog IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
         LOOP
             INSERT INTO ACTIVITY_LOG_V (
                                                  ACTIVITY_LOG_ID
                                                , OUTPATIENT_MED_PRM_ID
                                                , ACTIVITY_DT
                                                , ACTIVITY_DT_NBR
                                                , REASON_CODE
                                                , REASON_DSPLYTXT
                                                , REASON_CODSYS
                                                , INIT_PERSON_IDENTITY
                                                , INIT_PERSON_ASGNING_FACILITY
                                                , INIT_PERSON_ASGNING_AUTHORITY
                                                , INIT_PRACTITIONER_COMMENTS
                                                , INIT_PRACTITIONER_TM
                                                , INIT_PRACTITIONER_TM_NBR
                                                , INIT_PRACTITIONER_ID_SRC_TBL
                                                , INIT_NAME_PREFIX
                                                , INIT_NAME_GIVEN
                                                , INIT_NAME_FAMILY
                                                , INIT_NAME_SUFFIX
                                                , INIT_NAME_TITLE
                                                , INIT_NAME_MIDDLE
                                                , ACTIVITY_LOG_COMMENT
                                                , LINE_NBR
                                               ) 
             VALUES (
                       ACTIVITY_LOG_SEQ_ID.NEXTVAL
                     , outpmed_seq_id_val
                     , lastdisp_date                                                                -- ACTIVITY_DT	
                     , (TO_NUMBER(lastdisp_date,'99999999999999')-17000000000000)/1000000           -- ACTIVITY_DT_NBR
                     , ROUND(DBMS_RANDOM.VALUE (8771977,12987432))                                  --  REASON_CODE	
                     , dbms_random.string ('U',trunc(dbms_random.value(1,50)))                      --  REASON_DSPLYTXT	
                     , NULL                                                                         --  REASON_CODSYS		                        
                     , 'activity_id_' || to_char(round(dbms_random.value(1,1000)))                  -- INIT_PERSON_IDENTITY  1.5 START
                     , 'visn ' || to_char(round(dbms_random.value(1,21)))                           -- INIT_PERSON_ASGNING_FACILITY
                     , null                                                                         -- INIT_PERSON_ASGNING_AUTHORITY
                     , DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) )                 -- INIT_practitioner_comments
                     , ppa_date                                                                     -- INIT_PRACTITIONER_TM
                     , (to_number(ppa_date,'99999999999999')-17000000000000)/1000000                -- INIT_PRACTITIONER_TM_NBR
 --                      , to_number(to_char(to_number(substr(ppa_date,1,4)-1700)) || substr(ppa_date,5,4) || '.' || substr(ppa_date,9,length(ppa_date)-8))
                     , dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) )                  -- INIT_practitioner_id_src_tbl
                     , NULL                                                                         -- INIT_NAME_PREFIX	
                     , NULL                                                                         -- INIT_NAME_GIVEN		
                     , dbms_random.string ('U',trunc(dbms_random.value(1,40)))                      -- INIT_NAME_FAMILY	
                     , NULL                                                                         -- INIT_NAME_SUFFIX	
                     , NULL                                                                         -- INIT_NAME_TITLE	
                     , NULL                                                                         -- INIT_NAME_MIDDLE	
                     , dbms_random.string ('U',trunc(dbms_random.value(1,500)))                     -- ACTIVITY_LOG_COMMENT	
                     , actlog
                    );
         end loop;

         -- load patient_instruction table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR pati IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO PATIENT_INSTRUCTION_V (
                                                     PATIENT_INSTRUCTION_ID
                                                   , outpatient_med_prm_id
                                                   , PATIENT_INSTRUCTIONS
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          PATIENT_INSTRUCTION_SEQ_ID.NEXTVAL
                        , outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(1,500))) -- PATIENT_INSTRUCTIONS	
                        , pati
                       );
            end loop;
         END IF;

         -- load other_patient_instruction table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR opat IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO OTHER_PATIENT_INSTRUCTION_V (
                                                     OTHER_PATIENT_INSTRUCTION_ID
                                                   , outpatient_med_prm_id
                                                   , OTHER_PATIENT_INSTRUCTIONS
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          OTHER_PATIENT_INSTR_SEQ_ID.NEXTVAL
                        , outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(1,500))) -- OTHER_PATIENT_INSTRUCTIONS	
                        , opat
                       );
            end loop;
         END IF;

         -- load expanded_patient_instructions table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR exTi IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO EXPANDED_PATIENT_INSTRUCTION_V (
                                                     EXPAND_PATIENT_INSTRUCTION_ID
                                                   , outpatient_med_prm_id
                                                   , EXPAND_PATIENT_INSTRUCTIONS
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          EXPAND_PATIENT_INSTR_SEQ_ID.NEXTVAL
                        , outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(1,500))) -- EXTENDED_PATIENT_INSTRUCTIONS	
                        , exTi
                       );
            end loop;
         END IF;

         -- load cancel_comment table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR cc IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO CANCEL_COMMENT_V (
                                                     outpatient_med_prm_id
                                                   , CANCEL_COMMENTs
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(100,2000))) -- CANCEL_COMMENT	
                        , cc
                       );
            end loop;
         END IF;

         -- load outppharm_sig table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR os IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO OUTPPHARM_SIG_V (
                                                     outpatient_med_prm_id
                                                   , PRESCRIPTION_SIG_TXT
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          outpmed_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(1,100))) -- PRESCRIPTION_SIG_TXT	
                        , os
                       );
            end loop;
         END IF;
 
         -- process OUTPPHARM_TREATMENT_FACTOR table
         random_num := ROUND (DBMS_RANDOM.VALUE (0, 1));

         IF random_num = 0
         THEN
            FOR otf IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 1))
            LOOP
                
                -- get benefit_assignment seq id
                SELECT OUTPPHARM_TRMT_FCTR_SEQ_ID.NEXTVAL
                  INTO treat_seq_id_val
                  FROM DUAL;
                
                -- Insert benefit_assignment table
                INSERT INTO OUTPPHARM_TREATMENT_FACTOR_v (
                                                  OUTPPHARM_TREATMENT_FACTOR_ID
                                                , OUTPATIENT_MED_PRM_ID
                                                , FACTOR_CODE
                                                , FACTOR_DSPLYTXT
                                                , FACTOR_CODSYS
                                                , FACTOR_VAL_CODE
                                                , FACTOR_VAL_DSPLYTXT
                                                , FACTOR_VAL_CODSYS
                                                , LINE_NBR
                                               )
                                        VALUES (
                                                  treat_seq_id_val                                  -- OUTPPHARM_TREATMENT_FACTOR_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
                                                , otf
                                               );
            END LOOP;
         END IF;
         
         -- process medication_instruction table
         FOR mi IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
         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_v (
                                                  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
                                                , LINE_NBR
                                                )
                                         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
                                                 , TO_CHAR(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'	
                                                 , mi
                                                );
                             
         END LOOP;

         c_dsp_linenbr := 0;
         -- load Original dispense
         INSERT INTO OUTPATIENT_MED_DSP_v (
                                          OUTPATIENT_MED_DSP_ID
                                        , OUTPATIENT_MED_PRM_ID
                                        , DISPENSE_DT
                                        , DISPENSE_DT_NBR
                                        , FILL_DT
                                        , FILL_DT_NBR
                                        , 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
                                        , LOGIN_DT
                                        , LOGIN_DT_NBR
                                        , PHRM_PERSON_IDENTITY               
                                        , PHRM_PERSON_ASGNING_FACILITY
                                        , PHRM_PERSON_ASGNING_AUTHORITY
                                        , PHRM_PRACTITIONER_COMMENTS
                                        , PHRM_PRACTITIONER_TM 
                                        , PHRM_PRACTITIONER_TM_NBR
                                        , PHRM_PRACTITIONER_ID_SRC_TBL
                                        , PHRM_NAME_PREFIX
                                        , PHRM_NAME_GIVEN
                                        , PHRM_NAME_FAMILY
                                        , PHRM_NAME_SUFFIX
                                        , PHRM_NAME_TITLE
                                        , PHRM_NAME_MIDDLE                   
                                        , CPRV_PERSON_IDENTITY               
                                        , CPRV_PERSON_ASGNING_FACILITY
                                        , CPRV_PERSON_ASGNING_AUTHORITY
                                        , CPRV_PRACTITIONER_ID_SRC_TBL
                                        , CPRV_NAME_PREFIX
                                        , CPRV_NAME_GIVEN
                                        , CPRV_NAME_FAMILY
                                        , CPRV_NAME_SUFFIX
                                        , CPRV_NAME_TITLE
                                        , CPRV_NAME_MIDDLE                    -- 1.4 END
                                        , discriminator
                                        , line_nbr
                                       )
                                VALUES (
                                          outpdsp_seq_id_val                                -- OUTPATIENT_MED_DSP_ID	
                                        , outpmed_seq_id_val
                                        , disp_date                                         -- DISPENSE_DT	
                                        , (TO_NUMBER(DISP_DATE,'99999999999999')-17000000000000)/1000000             -- DISPENSE_DT_NBR	
--                      , to_number(to_char(to_number(substr(DISP_DATE,1,4)-1700)) || substr(DISP_DATE,5,4) || '.' || substr(DISP_DATE,9,length(DISP_DATE)-8))
                                        , prescription_date                                 -- FILL_DT	
                                        , (TO_NUMBER(prescription_date,'99999999999999')-17000000000000)/1000000     -- FILL_DT_NBR	
--                      , to_number(to_char(to_number(substr(prescription_date,1,4)-1700)) || substr(prescription_date,5,4) || '.' || substr(prescription_date,9,length(prescription_date)-8))
--                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- HX_QTY_DISPENSED	
                                        , TO_CHAR(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')-17000000000000)/1000000          -- RELEASE_DT_NBR
--                      , to_number(to_char(to_number(substr(release_date,1,4)-1700)) || substr(release_date,5,4) || '.' || substr(release_date,9,length(release_date)-8))
                                        , '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	
                                        , TO_CHAR(ROUND(DBMS_RANDOM.VALUE(5.50,560.34),2))           -- DRUG_UNITPRICE_VAL	
                                        , TO_CHAR(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')-17000000000000)/1000000             -- EXPIRATION_DT_LOW_NBR
--                      , to_number(to_char(to_number(substr(expl_date,1,4)-1700)) || substr(expl_date,5,4) || '.' || substr(expl_date,9,length(expl_date)-8))
                                        , 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
                                        , ppd_date
                                        , (to_number(ppd_date,'99999999999999')-17000000000000)/1000000 
--                      , to_number(to_char(to_number(substr(ppd_date,1,4)-1700)) || substr(ppd_date,5,4) || '.' || substr(ppd_date,9,length(ppd_date)-8))
                                        , f_pers_id                                                     -- PHRM_PERSON_IDENTITY  1.5 START
                                        , f_pers_fclty                                                  -- PHRM_PERSON_ASGNING_FACILITY
                                        , null                                                          -- PHRM_PERSON_ASGNING_AUTHORITY
                                        , f_pract_comments                                              -- PHRM_practitioner_comments
                                        , f_pract_tm                                                    -- PHRM_PRACTITIONER_TM
                                        , f_pract_tm_nbr                                                -- PHRM_PRACTITIONER_TM_NBR
                                        , f_practidsrc                                                  -- PHRM_practitioner_id_src_tbl
                                        , NULL                                                          -- PHRM_NAME_PREFIX	
                                        , NULL                                                          -- PHRM_NAME_GIVEN		
                                        , f_name_fam                                                    -- PHRM_NAME_FAMILY	
                                        , NULL                                                          -- PHRM_NAME_SUFFIX	
                                        , NULL                                                          -- PHRM_NAME_TITLE	
                                        , NULL                                                          -- PHRM_NAME_MIDDLE	
                                        , c_pers_id                                                     -- CPRV_PERSON_IDENTITY  1.5 START
                                        , c_pers_fclty                                                  -- CPRV_PERSON_ASGNING_FACILITY
                                        , null                                                          -- CPRV_PERSON_ASGNING_AUTHORITY
                                        , c_practidsrc                                                  -- CPRV_practitioner_id_src_tbl
                                        , NULL                                                          -- CPRV_NAME_PREFIX	
                                        , NULL                                                          -- CPRV_NAME_GIVEN		
                                        , c_name_fam                                                    -- CPRV_NAME_FAMILY	
                                        , NULL                                                          -- CPRV_NAME_SUFFIX	
                                        , NULL                                                          -- CPRV_NAME_TITLE	
                                        , NULL                                                          -- CPRV_NAME_MIDDLE	
                                        , c_discriminator                                               -- discriminator
                                        , c_dsp_linenbr
                                       );

         -- load DISPENSE_COMMENT table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR DC IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO DISPENSE_COMMENT_V (
                                                     outpatient_med_DSP_id
                                                   , DISPENSE_COMMENTs
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          outpdsp_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(50,200))) -- DISPENSE_COMMENT	
                        , DC
                       );
            end loop;
         END IF;

         -- load CURRENT_PROVIDER_COMMENT table
         random_num := round(DBMS_RANDOM.VALUE (1, 10));
         IF random_num < 9 
         THEN
            FOR CPC IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
            LOOP
                INSERT INTO CURRENT_PROVIDER_COMMENT_V (
                                                     outpatient_med_DSP_id
                                                   , PROVIDER_COMMENTs
                                                   , LINE_NBR
                                                  ) 
                VALUES (
                          outpdsp_seq_id_val
                        , dbms_random.string ('U',trunc(dbms_random.value(50,100))) -- PROVIDER_COMMENT	
                        , CPC
                       );
            end loop;
         END IF;

         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 refill practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
             f_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             f_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             f_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             f_pract_tm         := refill_date;
             f_pract_tm_nbr     := (to_number(refill_date,'99999999999999')-17000000000000)/1000000;
--            f_pract_tm_nbr  := to_number(to_char(to_number(substr(refill_date,1,4)-1700)) || substr(refill_date,5,4) || '.' || substr(refill_date,9,length(refill_date)-8));
             f_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             f_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         ELSE
             f_pers_id          := NULL;
             f_pers_fclty       := NULL;
             f_pract_comments   := NULL;
             f_pract_tm         := NULL;
             f_pract_tm_nbr     := NULL;
             f_practidsrc       := NULL;
             f_name_fam         := NULL;
         END IF;
         
         -- get pharmacist practitioner
         random_num := ROUND (DBMS_RANDOM.VALUE (1, 2));
         IF random_num = 1
         THEN
             c_pers_id          := 'test_id_' || to_char(round(dbms_random.value(1,1000)));
             c_pers_fclty       := 'visn ' || to_char(round(dbms_random.value(1,21)));
             c_pract_comments   := DBMS_RANDOM.STRING ('A',TRUNC (DBMS_RANDOM.VALUE (1,500) ) );
             c_pract_tm         := pharm_date;
             c_pract_tm_nbr     := (to_number(pharm_date,'99999999999999')-17000000000000)/1000000;
--            c_pract_tm_nbr  := to_number(to_char(to_number(substr(pharm_date,1,4)-1700)) || substr(pharm_date,5,4) || '.' || substr(pharm_date,9,length(pharm_date)-8));
             c_practidsrc       := dbms_random.string ('A',TRUNC (DBMS_RANDOM.VALUE (1,40) ) );
             c_name_fam         := dbms_random.string ('U',trunc(dbms_random.value(1,40)));
         ELSE
             c_pers_id          := NULL;
             c_pers_fclty       := NULL;
             c_pract_comments   := NULL;
             c_pract_tm         := NULL;
             c_pract_tm_nbr     := NULL;
             c_practidsrc       := NULL;
             c_name_fam         := NULL;
         END IF;
        
         IF round(dbms_random.value(0,20)) < 18
         THEN
             counter := round(dbms_random.value(1,4));
             
             FOR d in 1..counter
             LOOP
             
                 CASE d
                 WHEN '1' THEN
                    -- set refill dispense records
                    c_discriminator := 'R';
                 ELSE
                    -- set partial dispense records
                    c_discriminator := 'P';
                 END CASE;

                 -- get outpatient_med_dsp_seq_id
                 SELECT outpatient_med_dsp_seq_id.NEXTVAL
                   INTO outpdsp_seq_id_val
                   FROM DUAL;

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

                c_dsp_linenbr := c_dsp_linenbr + 1;
                -- Insert into outpatient_med_dsp table
                INSERT INTO OUTPATIENT_MED_DSP_v (
                                                  OUTPATIENT_MED_DSP_ID
                                                , OUTPATIENT_MED_PRM_ID
                                                , DISPENSE_DT
                                                , DISPENSE_DT_NBR
                                                , FILL_DT
                                                , FILL_DT_NBR
                                                , 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
                                                , LOGIN_DT
                                                , LOGIN_DT_NBR
                                                , PHRM_PERSON_IDENTITY               
                                                , PHRM_PERSON_ASGNING_FACILITY
                                                , PHRM_PERSON_ASGNING_AUTHORITY
                                                , PHRM_PRACTITIONER_COMMENTS
                                                , PHRM_PRACTITIONER_TM 
                                                , PHRM_PRACTITIONER_TM_NBR
                                                , PHRM_PRACTITIONER_ID_SRC_TBL
                                                , PHRM_NAME_PREFIX
                                                , PHRM_NAME_GIVEN
                                                , PHRM_NAME_FAMILY
                                                , PHRM_NAME_SUFFIX
                                                , PHRM_NAME_TITLE
                                                , PHRM_NAME_MIDDLE                   
                                                , CPRV_PERSON_IDENTITY               
                                                , CPRV_PERSON_ASGNING_FACILITY
                                                , CPRV_PERSON_ASGNING_AUTHORITY
                                                , CPRV_PRACTITIONER_ID_SRC_TBL
                                                , CPRV_NAME_PREFIX
                                                , CPRV_NAME_GIVEN
                                                , CPRV_NAME_FAMILY
                                                , CPRV_NAME_SUFFIX
                                                , CPRV_NAME_TITLE
                                                , CPRV_NAME_MIDDLE                    -- 1.4 END
                                                , discriminator
                                                , line_nbr
                                               )
                                        VALUES (
                                                  outpdsp_seq_id_val                                -- OUTPATIENT_MED_DSP_ID	
                                                , outpmed_seq_id_val
                                                , disp_date                                         -- DISPENSE_DT	
                                                , (TO_NUMBER(DISP_DATE,'99999999999999')-17000000000000)/1000000             -- DISPENSE_DT_NBR	
--                      , to_number(to_char(to_number(substr(DISP_DATE,1,4)-1700)) || substr(DISP_DATE,5,4) || '.' || substr(DISP_DATE,9,length(DISP_DATE)-8))
                                                , prescription_date                                 -- FILL_DT	
                                                , (TO_NUMBER(prescription_date,'99999999999999')-17000000000000)/1000000     -- FILL_DT_NBR	
--                      , to_number(to_char(to_number(substr(prescription_date,1,4)-1700)) || substr(prescription_date,5,4) || '.' || substr(prescription_date,9,length(prescription_date)-8))
    --                                            , ROUND(DBMS_RANDOM.VALUE(1, 100))                  -- HX_QTY_DISPENSED	
                                                , TO_CHAR(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')-17000000000000)/1000000          -- RELEASE_DT_NBR
--                      , to_number(to_char(to_number(substr(release_date,1,4)-1700)) || substr(release_date,5,4) || '.' || substr(release_date,9,length(release_date)-8))
                                                , '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	
                                                , TO_CHAR(ROUND(DBMS_RANDOM.VALUE(5.50,560.34),2))           -- DRUG_UNITPRICE_VAL	
                                                , TO_CHAR(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')-17000000000000)/1000000             -- EXPIRATION_DT_LOW_NBR
--                      , to_number(to_char(to_number(substr(expl_date,1,4)-1700)) || substr(expl_date,5,4) || '.' || substr(expl_date,9,length(expl_date)-8))
                                                , 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
                                                , ppd_date
                                                , (to_number(ppd_date,'99999999999999')-17000000000000)/1000000 
--                      , to_number(to_char(to_number(substr(ppd_date,1,4)-1700)) || substr(ppd_date,5,4) || '.' || substr(ppd_date,9,length(ppd_date)-8))
                                                , f_pers_id                                                     -- PHRM_PERSON_IDENTITY  1.5 START
                                                , f_pers_fclty                                                  -- PHRM_PERSON_ASGNING_FACILITY
                                                , null                                                          -- PHRM_PERSON_ASGNING_AUTHORITY
                                                , f_pract_comments                                              -- PHRM_practitioner_comments
                                                , f_pract_tm                                                    -- PHRM_PRACTITIONER_TM
                                                , f_pract_tm_nbr                                                -- PHRM_PRACTITIONER_TM_NBR
                                                , f_practidsrc                                                  -- PHRM_practitioner_id_src_tbl
                                                , NULL                                                          -- PHRM_NAME_PREFIX	
                                                , NULL                                                          -- PHRM_NAME_GIVEN		
                                                , f_name_fam                                                    -- PHRM_NAME_FAMILY	
                                                , NULL                                                          -- PHRM_NAME_SUFFIX	
                                                , NULL                                                          -- PHRM_NAME_TITLE	
                                                , NULL                                                          -- PHRM_NAME_MIDDLE	
                                                , c_pers_id                                                     -- CPRV_PERSON_IDENTITY  1.5 START
                                                , c_pers_fclty                                                  -- CPRV_PERSON_ASGNING_FACILITY
                                                , null                                                          -- CPRV_PERSON_ASGNING_AUTHORITY
                                                , c_practidsrc                                                  -- CPRV_practitioner_id_src_tbl
                                                , NULL                                                          -- CPRV_NAME_PREFIX	
                                                , NULL                                                          -- CPRV_NAME_GIVEN		
                                                , c_name_fam                                                    -- CPRV_NAME_FAMILY	
                                                , NULL                                                          -- CPRV_NAME_SUFFIX	
                                                , NULL                                                          -- CPRV_NAME_TITLE	
                                                , NULL                                                          -- CPRV_NAME_MIDDLE	
                                                , c_discriminator                                               -- discriminator
                                                , c_dsp_linenbr
                                               );

                -- load DISPENSE_COMMENT table
                random_num := round(DBMS_RANDOM.VALUE (1, 10));
                IF random_num < 9 
                THEN
                   FOR DC2 IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
                   LOOP
                       INSERT INTO DISPENSE_COMMENT_V (
                                                            outpatient_med_DSP_id
                                                          , DISPENSE_COMMENTs
                                                          , LINE_NBR
                                                         ) 
                       VALUES (
                                 outpdsp_seq_id_val
                               , dbms_random.string ('U',trunc(dbms_random.value(50,200))) -- DISPENSE_COMMENT	
                               , DC2
                              );
                   end loop;
                END IF;

                -- load CURRENT_PROVIDER_COMMENT table
                random_num := round(DBMS_RANDOM.VALUE (1, 10));
                IF random_num < 9 
                THEN
                   FOR CPC2 IN 0 .. ROUND (DBMS_RANDOM.VALUE (0, 2))
                   LOOP
                       INSERT INTO CURRENT_PROVIDER_COMMENT_V (
                                                            outpatient_med_DSP_id
                                                          , PROVIDER_COMMENTs
                                                          , LINE_NBR
                                                         ) 
                       VALUES (
                                 outpdsp_seq_id_val
                               , dbms_random.string ('U',trunc(dbms_random.value(50,100))) -- PROVIDER_COMMENT	
                               , CPC2
                              );
                   end loop;
                END IF;
            END LOOP;
         END IF;                            
         
       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;
/
