/******************************************************************************
   NAME:       load_survey()
   PURPOSE:    This script will create survey records
   ADDITIONAL INFORMATION:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2/12/2013   M. Jacobsen      Initial release
   2.0        8/12/2013   M. Jacobsen      Add DMP surveys - rewrite for procedure
   
   PARAMETERS:
   1 - #records to generate 
   2 - flag to delete records
   3 - flag to indicate complete rebuild or just inserts
   4 - Start date
   5 - End date

******************************************************************************/

CREATE OR REPLACE PROCEDURE load_survey(
    p_num_records       IN NUMBER      DEFAULT 200,
    p_delete_records    IN BOOLEAN     DEFAULT FALSE,
    p_rebuild           IN BOOLEAN     DEFAULT TRUE,
    p_start_date        IN DATE        DEFAULT TO_DATE('01/01/1999','mm/dd/yyyy'),
    p_end_date          IN DATE        DEFAULT NULL
)
IS
    v_survey_question_id NUMBER;
    v_survey_choice_id   NUMBER;
    v_survey_id          NUMBER;
    v_line_nbr           NUMBER := 0;
    -- 2.0
    v_random_number      NUMBER;
    v_status             VARCHAR2(25);
    v_title              VARCHAR2(100);
    v_universalid        VARCHAR2(50);
    v_record_create_dt   VARCHAR2(25);
    v_device             VARCHAR2(100);
    v_end_date           DATE;
    v_facility           NUMBER;
    v_start_jdt          NUMBER;
    v_end_jdt            NUMBER;
    v_inactivation_dt    DATE;
    v_activation_dt      DATE;
    v_disenroll_dt       DATE;
    v_enroll_dt          DATE;
    v_schedule_dt        DATE;
   
    CURSOR c_survey_question (v_survey_id NUMBER) IS
      SELECT survey_question_id
      FROM survey s, survey_question q
      WHERE s.survey_id = v_survey_id
      AND s.survey_id = q.survey_id;
     
BEGIN
   dbms_output.enable(NULL);
   DBMS_OUTPUT.put_line (   'Survey Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));

    -- set end date if wasn't set
    IF p_end_date IS NULL THEN
        IF SYSDATE > p_start_date THEN
            v_end_date := SYSDATE;
        ELSE
            v_end_date := p_start_date + 1;
        END IF;
    ELSE
            v_end_date := p_end_date;
    END IF;
    
    -- set j dates
    SELECT TO_NUMBER(TO_CHAR(p_start_date,'J')), TO_NUMBER(TO_CHAR(v_end_date,'J'))
    INTO v_start_jdt, v_end_jdt
    FROM DUAL;
    
    -- if delete flag set, delete only the records between the start and end dates
    IF p_delete_records THEN
        DELETE FROM survey_question_answer WHERE survey_response_id IN ( SELECT survey_response_id FROM survey_response WHERE completed_dt BETWEEN p_start_date AND v_end_date);
        DELETE FROM survey_response WHERE completed_dt BETWEEN p_start_date AND v_end_date;
        DELETE FROM dmp_question_answer WHERE dmp_response_id IN ( SELECT dmp_response_id FROM dmp_response WHERE completed_dt BETWEEN p_start_date AND v_end_date);
        DELETE FROM dmp_response WHERE completed_dt BETWEEN p_start_date AND v_end_date;
        COMMIT;
    ELSIF p_rebuild THEN
        DELETE FROM survey_question_answer;
        DELETE FROM survey_response;
        DELETE FROM dmp_question_answer;
        DELETE FROM dmp_response;
        COMMIT;
        DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.  can only set this when deleting all data and starting from scratch otherwise it will produce a unique constraint violation
        insert_practpart.RESET_SEQUENCES('SURVEY_QUESTION_ANSWER_SEQ_ID');
        insert_practpart.RESET_SEQUENCES('SURVEY_RESPONSE_SEQ_ID');
        insert_practpart.RESET_SEQUENCES('DMP_RESPONSE_SEQ_ID');
        insert_practpart.RESET_SEQUENCES('DMP_QUESTION_ANSWER_SEQ_ID');
    ELSE
        NULL;  -- don't delete any data
    END IF;
    
    -- Load SURVEY data
    FOR z IN 1 .. p_num_records
    LOOP
        SELECT facility_id
        INTO v_facility
        FROM  (SELECT facility_id FROM facility ORDER BY DBMS_RANDOM.value) WHERE rownum = 1;
        
        SELECT survey_id
        INTO v_survey_id
        FROM (SELECT survey_id FROM survey WHERE type = 'SURVEY' ORDER BY DBMS_RANDOM.value) WHERE rownum = 1;
        
        -- Set title and record creation date
        SELECT title, TO_CHAR(SYSDATE-(DBMS_RANDOM.value(1,10)),'yyyymmddhhmiss')
        INTO v_title, v_record_create_dt
        FROM survey
        WHERE survey_id = v_survey_id;
        
        -- Set universal_id
        v_random_number := ROUND(DBMS_RANDOM.value(1,4));
        CASE v_random_number
            WHEN '1' THEN
                v_universalid := 'DNS   WEBMON1.VHAURL        ';
            WHEN '2' THEN
                v_universalid := 'DNS   VPDEV3.VHAURL        ';
            WHEN '3' THEN
                v_universalid := 'DNS   WEBMON1.VHAURL        ';
            ELSE
                v_universalid := '      EHTDB.VHAURL        ';
        END CASE;
        
        -- Set status
        v_random_number := ROUND(DBMS_RANDOM.value(1,100));
        CASE v_random_number
            WHEN '1' THEN
                v_status := 'DISENROLLED';
            WHEN '2' THEN
                v_status := 'REFUSED-TIME-OUT';
            WHEN '3' THEN
                v_status := 'REFUSED';
            WHEN '4' THEN
                v_status := 'DELAYED';
            ELSE
                v_status := 'ACCEPTED';
        END CASE;
        
        -- Set device_name
        v_random_number := ROUND(DBMS_RANDOM.value(1,950592));
           CASE
              WHEN v_random_number < 9 THEN
                 v_device := 'N/A';
              WHEN v_random_number BETWEEN 9 AND 49 THEN
                 v_device := 'PSDmiLife';
              WHEN v_random_number BETWEEN 50 AND 199 THEN
                 v_device := 'PSDLifeView';
              WHEN v_random_number BETWEEN 200 AND 359 THEN
                 v_device := 'No current Device';
              WHEN v_random_number BETWEEN 360 AND 1709 THEN
                 v_device := 'Viterion 500';
              WHEN v_random_number BETWEEN 1710 AND 12159 THEN
                 v_device := 'Health Buddy appliance Version 1';
              WHEN v_random_number BETWEEN 12160 AND 22671 THEN
                 v_device := 'PSDinLife';
              WHEN v_random_number BETWEEN 22672 AND 44946 THEN
                 v_device := 'Viterion 100';
              WHEN v_random_number BETWEEN 44947 AND 145445 THEN
                 v_device := 'Health Buddy appliance Version 2';
              WHEN v_random_number BETWEEN 145446 AND 380049 THEN
                 v_device := NULL;
              ELSE
                 v_device := 'Health Buddy appliance Version 3';
           END CASE;

        -- Set enrollment_dt and initialize the other dates to null
        v_enroll_dt       := TO_DATE(TRUNC(DBMS_RANDOM.value(2433283, 2456659)), 'J');
        v_disenroll_dt    := NULL;
        v_activation_dt   := NULL;
        v_inactivation_dt := NULL;
        
        -- Set activation_dt
        v_random_number := ROUND(DBMS_RANDOM.value(1,100));
        IF v_random_number < 11 THEN
            v_activation_dt := NULL;
        ELSE
            v_random_number := ROUND(DBMS_RANDOM.value(1,100));
            CASE
                WHEN v_random_number = 1 THEN
                    v_activation_dt := v_enroll_dt - TRUNC(DBMS_RANDOM.value(1,100));
                WHEN v_random_number BETWEEN 2 AND 39 THEN
                    v_activation_dt := v_enroll_dt;
                ELSE
                    v_activation_dt := v_enroll_dt + TRUNC(DBMS_RANDOM.value(1,100));
            END CASE;
        END IF;
        
        -- Set inactivation_dt and disenrment_dt
        IF v_activation_dt IS NULL THEN
            v_inactivation_dt := NULL;
            v_random_number := ROUND(DBMS_RANDOM.value(1,1000000));
            IF v_random_number > 256 THEN
                v_disenroll_dt := NULL;
            ELSE
                v_disenroll_dt := v_enroll_dt + TRUNC(DBMS_RANDOM.value(1,100));
            END IF;		
        ELSE
            v_random_number := ROUND(DBMS_RANDOM.value(1,1000));
            IF v_random_number <= 15 THEN
			    v_random_number := ROUND(DBMS_RANDOM.value(1,7));
				IF v_random_number < 5 THEN
					v_inactivation_dt := v_activation_dt + TRUNC(DBMS_RANDOM.value(1,100));
				    v_random_number := ROUND(DBMS_RANDOM.value(1,400));
					IF v_random_number > 10 THEN
						v_disenroll_dt := v_inactivation_dt;
					ELSE
						v_disenroll_dt := v_inactivation_dt + TRUNC(DBMS_RANDOM.value(1,100));
					END IF;
				ELSE
				    v_random_number := ROUND(DBMS_RANDOM.value(1,5));
					IF v_random_number = 1 THEN
					    v_inactivation_dt := v_activation_dt + TRUNC(DBMS_RANDOM.value(1,100));
						v_disenroll_dt    := NULL;
					ELSE
						v_inactivation_dt := NULL;
					    v_disenroll_dt    := v_enroll_dt + TRUNC(DBMS_RANDOM.value(1,100));
					END IF;
				END IF;
            ELSE
                v_inactivation_dt := NULL;
            END IF;
        END IF;
        
        -- set scheduled_dt 
        v_random_number := ROUND(DBMS_RANDOM.value(1,100));
        CASE
            WHEN v_random_number = 1 THEN
                v_schedule_dt := v_enroll_dt;
            WHEN v_random_number BETWEEN 2 AND 76 THEN
                v_schedule_dt := v_enroll_dt - TRUNC(DBMS_RANDOM.value(1,100));
            ELSE
                v_schedule_dt := NULL;
        END CASE;
 
        -- load record into survey_response table
        INSERT INTO survey_response (
          survey_response_id, 
          survey_id, 
          facility_id, 
          vendor_id, 
          survey_patient_id, 
          care_coordinator_id,
          request_id, 
          recordsource_namespaceid, 
          recordsource_universalid, 
          recordsource_universtyp,
          patient_identity, 
          patient_assigning_facility, 
          patient_assigning_authority, 
          patient_name_prefix,
          patient_name_given, 
          patient_name_family, 
          patient_name_suffix, 
          patient_name_title,
          patient_name_middle, 
          vendor_mrn, 
          scheduled_dt, 
          completed_dt, 
          current_dialog_name,
          program_name, 
          device_name, 
          survey_accepted_status, 
          mental_score, 
          physical_score,
          disenrment_dt, 
          inactivation_dt, 
          activation_dt, 
          enrollment_dt, 
          record_creation_tm,
          record_creation_tm_nbr
        ) VALUES (
          survey_response_seq_id.NEXTVAL,                                                                                                       -- survey_response_id
          v_survey_id,                                                                                                                          -- survey_id
          v_facility,                                                                                                                           -- facility_id
          ( SELECT vendor_id FROM (SELECT vendor_id FROM vendor ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                                 -- vendor_id
          ( SELECT survey_patient_id FROM (SELECT survey_patient_id FROM survey_patient ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),         -- survey_patient_id
          ( SELECT care_coordinator_id FROM (SELECT care_coordinator_id FROM care_coordinator ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),   -- care_coordinator_id
          '200HD.' || ROUND(DBMS_RANDOM.value(100000,999999)) || '.' || (select to_char(sysdate,'yyyymmddhhmiss') from dual) || '-0' || ROUND(DBMS_RANDOM.value(4,5)),  -- request_id
          ( SELECT vendor_facility FROM (SELECT vendor_facility FROM vendor ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                     -- recordsource_namespaceid
          v_universalid,                                                                                                                        -- recordsource_universalid
          'DNS',                                                                                                                                -- recordsource_universtyp
          '00000000' || ROUND(DBMS_RANDOM.value(1000,9999)),                                                                                    -- patient_identity
          ( SELECT station_no FROM (SELECT station_no FROM facility ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                             -- patient_assigning_facility
          'USVHA',                                                                                                                              -- patient_assigning_authority
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 10)),  1, 'Ms',  2, 'Miss',  3, 'Mrs',  4, 'Mr',  5, 'Master',  ' ') prefix FROM DUAL),  -- patient_name_prefix
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (1, 23)))),                                                                -- patient_name_given
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (2, 25)))),                                                                -- patient_name_family
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 50)),  1, 'J.D.',  2, 'M.D.',  3, 'D.O.',  4, 'D.C.',  5, 'Ph.D.',  DECODE (ROUND (DBMS_RANDOM.value (1, 28)),  1, 'Sr',  2, 'I', 3, 'Jr', 4, 'II',  5, 'III',  6, 'IV',  7, 'V', ' ')) FROM DUAL), -- patient_name_suffix
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 100)),  1, 'Rev',  2, 'Fr',  3, 'Dr',  4, 'Atty',  5, 'Prof',  6, 'Hon',  7, 'Pres',  8, 'Gov',  9, 'Coach',  10, 'Ofc',  ' ') FROM DUAL), --patient_name_title
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (0, 13)))),                                                                -- patient_name_middle
          DBMS_RANDOM.string('X', DBMS_RANDOM.value(4, 17)),                                                                                    -- vendor_mrn
          v_schedule_dt,                                                                                                                        -- scheduled_dt
          TO_DATE(TRUNC(DBMS_RANDOM.value(v_start_jdt, v_end_jdt)), 'J')+dbms_random.value(0,1),                                                -- completed_dt
          DECODE (TRUNC(DBMS_RANDOM.VALUE (1, 10)), 1, DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)), NULL), -- current_dialog_name
          DECODE (TRUNC(DBMS_RANDOM.VALUE (1, 4)), 1, NULL, DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30))),  -- program_name
          v_device,                                                                                                                             -- device_name
          v_status,                                                                                                                             -- survey_accepted_status
          DECODE(v_title,'VR-12 ADL',round(DBMS_RANDOM.value(4, 80.00),6),null),                                                                -- mental_score
          DECODE(v_title,'VR-12 ADL',round(DBMS_RANDOM.value(4, 80.00),6),null),                                                                -- physical_score
          v_disenroll_dt,                                                                                                                       -- disnrment_dt
          v_inactivation_dt,                                                                                                                    -- inactivation_dt
          v_activation_dt,                                                                                                                      -- activation_dt
          v_enroll_dt,                                                                                                                          -- enrollment_dt
          v_record_create_dt || '-0600',                                                                                                        -- record_creation_tm
          TO_NUMBER(TO_CHAR(TO_NUMBER(SUBSTR(v_record_create_dt,1,4) - 1700)) || SUBSTR(v_record_create_dt,5,4) || '.' || SUBSTR(v_record_create_dt,9,6))  -- record_creation_tm_nbr
        );
        
        OPEN c_survey_question (v_survey_id);
        LOOP
            FETCH c_survey_question INTO v_survey_question_id;
            EXIT WHEN c_survey_question%NOTFOUND;
            
            SELECT survey_choice_id 
            INTO v_survey_choice_id
            FROM ( SELECT survey_choice_id FROM survey_choice WHERE survey_question_id = v_survey_question_id ORDER BY DBMS_RANDOM.value ) 
            where rownum = 1;
            
            INSERT INTO survey_question_answer ( survey_question_answer_id, survey_response_id, survey_choice_id, line_nbr )
            VALUES ( survey_question_answer_seq_id.NEXTVAL, survey_response_seq_id.CURRVAL, v_survey_choice_id, v_line_nbr );
            
            v_line_nbr := v_line_nbr + 1;
            
        END LOOP;
        COMMIT;
        CLOSE c_survey_question;
        
        v_line_nbr    := 0;
       
    END LOOP;
    COMMIT;
    
/* version 2.0 */
   -- Load DMP data
    FOR x IN 1 .. p_num_records
    LOOP
        SELECT facility_id
        INTO v_facility
        FROM  (SELECT facility_id FROM facility ORDER BY DBMS_RANDOM.value) WHERE rownum = 1;
        
        SELECT survey_id, TO_CHAR(SYSDATE-(DBMS_RANDOM.value(1,10)),'yyyymmddhhmiss')
        INTO v_survey_id, v_record_create_dt
        FROM (SELECT survey_id FROM survey WHERE type = 'DMP' ORDER BY DBMS_RANDOM.value) WHERE rownum = 1;
        
        -- Set status
        v_random_number := round(DBMS_RANDOM.value(1,100));
        CASE v_random_number
            WHEN '1' THEN
                v_status := 'DISENROLLED';
            WHEN '2' THEN
                v_status := 'REFUSED-TIME-OUT';
            WHEN '3' THEN
                v_status := 'REFUSED';
            WHEN '4' THEN
                v_status := 'DELAYED';
            ELSE
                v_status := 'ACCEPTED';
        END CASE;
        
        -- Set device_name
        v_random_number := ROUND(DBMS_RANDOM.value(1,950592));
           CASE
              WHEN v_random_number < 9 THEN
                 v_device := 'N/A';
              WHEN v_random_number BETWEEN 9 AND 49 THEN
                 v_device := 'PSDmiLife';
              WHEN v_random_number BETWEEN 50 AND 199 THEN
                 v_device := 'PSDLifeView';
              WHEN v_random_number BETWEEN 200 AND 359 THEN
                 v_device := 'No current Device';
              WHEN v_random_number BETWEEN 360 AND 1709 THEN
                 v_device := 'Viterion 500';
              WHEN v_random_number BETWEEN 1710 AND 12159 THEN
                 v_device := 'Health Buddy appliance Version 1';
              WHEN v_random_number BETWEEN 12160 AND 22671 THEN
                 v_device := 'PSDinLife';
              WHEN v_random_number BETWEEN 22672 AND 44946 THEN
                 v_device := 'Viterion 100';
              WHEN v_random_number BETWEEN 44947 AND 145445 THEN
                 v_device := 'Health Buddy appliance Version 2';
              WHEN v_random_number BETWEEN 145446 AND 380049 THEN
                 v_device := NULL;
              ELSE
                 v_device := 'Health Buddy appliance Version 3';
           END CASE;

        -- load data into dmp_response table
        INSERT INTO dmp_response (
            dmp_response_id,
            survey_id,
            facility_id,
            vendor_id,
            request_id,
            recordsource_namespaceid,
            recordsource_universalid,
            recordsource_universtyp,
            patient_identity,
            patient_assigning_facility,
            patient_assigning_authority,
            patient_name_prefix,
            patient_name_given,
            patient_name_family,
            patient_name_suffix,
            patient_name_title,
            patient_name_middle,
            vendor_mrn,
            scheduled_dt,
            completed_dt,
            program_name,
            device_name,
            record_creation_tm,
            record_creation_tm_nbr,
            icn,
            xml_version,
            response_status
        ) VALUES (
          dmp_response_seq_id.NEXTVAL,                                                                                                          -- dmp_response_id
          v_survey_id,                                                                                                                          -- survey_id
          v_facility,                                                                                                                           -- facility_id
          ( SELECT vendor_id FROM (SELECT vendor_id FROM vendor ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                                 -- vendor_id
          '200HD.' || ROUND(DBMS_RANDOM.value(100000,999999)) || '.' || (SELECT TO_CHAR(SYSDATE,'yyyymmddhhmiss') FROM dual) || '-0' || ROUND(DBMS_RANDOM.value(4,5)),  -- request_id
          ( SELECT vendor_facility FROM (SELECT vendor_facility FROM vendor ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                     -- recordsource_namespaceid
          'LOCALHOST',                                                                                                                          -- recordsource_universalid
          'DNS',                                                                                                                                -- recordsource_universtyp
          '00000000' || ROUND(DBMS_RANDOM.value(1000,9999)),                                                                                    -- patient_identity
          ( SELECT station_no FROM (SELECT station_no FROM facility ORDER BY DBMS_RANDOM.value) WHERE rownum = 1 ),                             -- patient_assigning_facility
          'USVHA',                                                                                                                              -- patient_assigning_authority
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 10)),  1, 'Ms',  2, 'Miss',  3, 'Mrs',  4, 'Mr',  5, 'Master',  ' ') prefix FROM DUAL),  -- patient_name_prefix
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (1, 23)))),                                                                -- patient_name_given
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (2, 25)))),                                                                -- patient_name_family
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 50)),  1, 'J.D.',  2, 'M.D.',  3, 'D.O.',  4, 'D.C.',  5, 'Ph.D.',  DECODE (ROUND (DBMS_RANDOM.value (1, 28)),  1, 'Sr',  2, 'I', 3, 'Jr', 4, 'II',  5, 'III',  6, 'IV',  7, 'V', ' ')) FROM DUAL), -- patient_name_suffix
          (SELECT DECODE (ROUND (DBMS_RANDOM.value (1, 100)),  1, 'Rev',  2, 'Fr',  3, 'Dr',  4, 'Atty',  5, 'Prof',  6, 'Hon',  7, 'Pres',  8, 'Gov',  9, 'Coach',  10, 'Ofc',  ' ') FROM DUAL), --patient_name_title
          INITCAP (DBMS_RANDOM.string ('L', TRUNC (DBMS_RANDOM.VALUE (0, 13)))),                                                                -- patient_name_middle
          DBMS_RANDOM.string('X', DBMS_RANDOM.value(4, 17)),                                                                                    -- vendor_mrn
          TO_DATE(TRUNC(DBMS_RANDOM.value(2433283, 2456659)), 'J'),                                                                             -- scheduled_dt
          TO_DATE(TRUNC(DBMS_RANDOM.value(v_start_jdt, v_end_jdt)), 'J')+dbms_random.value(0,1),                                                -- completed_dt
          DECODE (TRUNC(DBMS_RANDOM.VALUE (1, 4)), 1, NULL, DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30)) || ' ' || DBMS_RANDOM.string ('A', DBMS_RANDOM.VALUE (1, 30))),  -- program_name
          v_device,                                                                                                                             -- device_name
          v_record_create_dt || '-0600',                                                                                                        -- record_creation_tm
          TO_NUMBER(TO_CHAR(TO_NUMBER(SUBSTR(v_record_create_dt,1,4) - 1700)) || SUBSTR(v_record_create_dt,5,4) || '.' || SUBSTR(v_record_create_dt,9,6)),  -- record_creation_tm_nbr
          ROUND(DBMS_RANDOM.value(1000000000,9999999999)) || 'V' || ROUND(DBMS_RANDOM.value(100000,999999)),                                    -- ICN
          DBMS_RANDOM.string('A', DBMS_RANDOM.value(1, 12)),                                                                                    -- XML_version
          v_status                                                                                                                              -- response_status
        );

        FOR i IN 1 .. 3
        LOOP
            
            INSERT INTO dmp_question_answer ( dmp_question_answer_id, dmp_response_id, question, answer, score, line_nbr )
            VALUES ( dmp_question_answer_seq_id.NEXTVAL, dmp_response_seq_id.CURRVAL, i, 0, NULL, v_line_nbr );
            
            v_line_nbr := v_line_nbr + 1;
            
        END LOOP;
        COMMIT;
        v_line_nbr := 0;
        
    END LOOP;
    COMMIT; 

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