CREATE OR REPLACE PACKAGE BODY SURVEY.ht_survey_report AS
-- cds 3.4.0.7 add function count_surveys_national_dist, procedure count_national_dist_proc
-- cds 3.4.0.9 modify function national_distribution; change format of the date being passed back
-- cds 3.4.0.11 modify function national_distribution
-- cds 3.5.0.0 add count_dmps, count_dmps_national, count_dmps_visn, count_dmps_facility, count_dmps_proc; update date format of HH:MM:SS to HH:MI:SS
-- cds 3.5.0.3 add vendor to count_dmps, count_dmps_national, count_dmps_visn, count_dmps_facility, count_dmps_proc functions
-- cds 3.5.0.7 add dmp_titles_proc
-- cds 3.5.0.8 add new functions: check_survey_version, check_survey_type, check_location, check_status
-- CDS 3.5.1.0 add Materialized view support for pre-aggregating the data
-- per request from developer...uncomment 4 lines of code around line 600

    FUNCTION str2tbl( p_str in varchar2 ) 
        RETURN myTableType

    IS
        l_str long default p_str || ',';
        l_n number;
        l_data myTableType := myTabletype();
    BEGIN

        dbms_output.put_line( 'FUNCTION str2tbl  p_str: ' || p_str );
        loop
            l_n := instr( l_str, ',' );
            exit when (nvl(l_n,0) = 0);

            l_data.extend;
            l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));

            l_str := substr( l_str, l_n+1 );
        END loop;

        return l_data;

    END;


    FUNCTION check_survey_version
    (
        p_survey_version    IN  varchar2
    )
    RETURN varchar2
    IS
        nn_survey_version varchar2(128) := trim( p_survey_version );
    BEGIN

        dbms_output.put_line( 'FUNCTION check_survey_version' );
        dbms_output.put_line( 'survey version: ''' || p_survey_version || '''' );
    
        IF ( nn_survey_version is null )
        THEN
            BEGIN
                nn_survey_version := 'All Versions';
                dbms_output.put_line( 'RESET survey version: ' || nn_survey_version );
            END;
        END IF;
        
        RETURN nn_survey_version;

    END;
        
    
    FUNCTION check_survey_type
    (
        p_survey_type    IN  varchar2
    )
    RETURN varchar2
    IS
        nn_survey_type varchar2(128) := trim( p_survey_type );
    BEGIN

        dbms_output.put_line( 'FUNCTION check_survey_type' );
        dbms_output.put_line( 'survey type: ''' || p_survey_type || '''' );
    
        IF ( nn_survey_type is null )
        THEN
            BEGIN
                nn_survey_type := 'All Surveys';
                dbms_output.put_line( 'RESET survey type: ' || nn_survey_type );
            END;
        END IF;
        
        RETURN nn_survey_type;

    END;
        
    
    FUNCTION check_location
    (
        p_location    IN  varchar2
    )
    RETURN varchar2
    IS
        nn_location varchar2(128) := trim( p_location );
    BEGIN

        dbms_output.put_line( 'FUNCTION check_location' );
        dbms_output.put_line( 'location: ''' || p_location  || '''' );
    
        IF ( nn_location is null )
        THEN
            BEGIN
                nn_location := 'National';
                dbms_output.put_line( 'RESET location: ' || nn_location );
            END;
        END IF;
        
        RETURN nn_location;

    END;
        
    
    FUNCTION check_status
    (
        p_status    IN  varchar2
    )
    RETURN varchar2
    IS
        nn_status varchar2(128) := trim( p_status );
    BEGIN

        dbms_output.put_line( 'FUNCTION check_status' );
        dbms_output.put_line( 'status: ''' || p_status || '''' );
    
        IF ( nn_status is null )
        THEN
            BEGIN
                nn_status := 'All Statuses';
                dbms_output.put_line( 'RESET status: ' || nn_status );
            END;
        END IF;
        
        RETURN nn_status;

    END;
                
    
    FUNCTION count_surveys
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
        
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        nn_location varchar2(128) := check_location( p_location );
    BEGIN

        dbms_output.put_line( 'FUNCTION count_surveys' );
        dbms_output.put_line( 'survey version' || p_survey_version || ', survey type: ' || p_survey_type || ', location: ' || p_location || ', status: ' || p_status );
        
        IF ( substr(nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                record_count := count_surveys_national( nn_survey_version, nn_survey_type, p_status, p_start_date, p_end_date );
            END;
        ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
        THEN
            BEGIN
                record_count := count_surveys_visn( nn_survey_version, nn_survey_type, nn_location, p_status, p_start_date, p_end_date );
            END;
        ELSE
            BEGIN
                record_count := count_surveys_facility( nn_survey_version, nn_survey_type, nn_location, p_status, p_start_date, p_end_date );
            END;
        END IF;
        
        RETURN record_count;

    END;


    FUNCTION count_surveys_national
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN

        dbms_output.put_line( 'FUNCTION count_surveys_national' );
        dbms_output.put_line( 'survey version' || p_survey_version || ', survey type: ' || p_survey_type || ', status: ' || p_status );
        
        IF ( p_survey_version = 'All Versions' )
        THEN
            BEGIN
                IF ( p_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        SELECT   count(*) 
                        INTO     record_count
                        FROM     survey_response sr
                        WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                ELSE
                    BEGIN
                        SELECT   count(*) 
                        INTO     record_count
                        FROM     survey_response sr, survey s 
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.TITLE = p_survey_type
                        AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( p_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        SELECT   count(*) 
                        INTO     record_count
                        FROM     survey_response sr, survey s 
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                        AND      s.version = p_survey_version  
                        AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                ELSE
                    BEGIN
                        SELECT   count(*) 
                        INTO     record_count
                        FROM     survey_response sr, survey s 
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                        AND      s.TITLE = p_survey_type
                        AND      s.version = p_survey_version  
                        AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                END IF;
            END;
        END IF;
    
        RETURN record_count;

    END;


    FUNCTION count_surveys_visn
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_visn           IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN

        dbms_output.put_line( 'FUNCTION count_surveys_visn' );
        dbms_output.put_line( 'survey version' || p_survey_version || ', survey type: ' || p_survey_type || ', visn: ' || p_visn || ', status: ' || p_status );
        
        IF ( p_visn = 'All VISNs' )
        THEN
            BEGIN
                record_count := count_surveys_national( p_survey_version, p_survey_type, p_status, p_start_date, p_end_date );
            END;
        ELSE
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.version = p_survey_version 
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.version = p_survey_version 
                                AND      s.TITLE = p_survey_type
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
        
        RETURN record_count;

    END;
    
    
    FUNCTION count_surveys_facility
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_station_no     IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN
    
        dbms_output.put_line( 'FUNCTION count_surveys_facility' );
        
        IF ( substr( p_station_no, 1, 9 ) = 'All VistA' )
        THEN
            BEGIN
                record_count := count_surveys_national( p_survey_version, p_survey_type, p_status, p_start_date, p_end_date );
            END;
        ELSE
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.version = p_survey_version 
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(*) 
                                INTO     record_count
                                FROM     survey_response sr, survey s, facility sf
                                WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.version = p_survey_version 
                                AND      s.TITLE = p_survey_type
                                AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        END IF;
                    END;
                END IF;
            END;

        END IF;

        RETURN record_count;

    END;


    FUNCTION count_surveys_national_dist_mv
    (
       p_survey_version IN  varchar2,
       p_survey_type    IN  varchar2,
       p_status         IN  varchar2
    )
       RETURN number
    IS
       record_count number := 0;
               
       nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
       nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
    BEGIN
       
       dbms_output.put_line( 'FUNCTION count_surveys_national_dist_mv' );
       dbms_output.put_line( 'survey version' || p_survey_version || ', survey type: ' || p_survey_type || ', status: ' || p_status );
               
       IF ( nn_survey_version = 'All Versions' )
       THEN
           BEGIN
               IF ( nn_survey_type = 'All Surveys' )
               THEN
                   BEGIN
                       SELECT   nvl( sum( record_count ), 0 )
                       INTO     record_count
                       FROM     surveys_mv_so.surveys_survey_type_mv
                       WHERE    SURVEY_ACCEPTED_STATUS = p_status;
                   END;
               ELSE
                   BEGIN
                       SELECT   nvl( sum( record_count ), 0 )
                       INTO     record_count
                       FROM     surveys_mv_so.surveys_survey_type_mv
                       WHERE    TITLE = nn_survey_type
                       AND      SURVEY_ACCEPTED_STATUS = p_status;
                   END;
               END IF;
           END;
       ELSE
           BEGIN
               IF ( nn_survey_type = 'All Surveys' )
               THEN
                   BEGIN
                       SELECT   nvl( sum( record_count ), 0 )
                       INTO     record_count
                       FROM     surveys_mv_so.surveys_survey_type_mv
                       WHERE    VERSION = nn_survey_version
                       AND      SURVEY_ACCEPTED_STATUS = p_status;
                  END;
               ELSE
                   BEGIN
                       SELECT   nvl( sum( record_count ), 0 )
                       INTO     record_count
                       FROM     surveys_mv_so.surveys_survey_type_mv
                       WHERE    TITLE = nn_survey_type
                       AND      VERSION = nn_survey_version
                       AND      SURVEY_ACCEPTED_STATUS = p_status;
                   END;
               END IF;
           END;
       END IF;
       
       RETURN record_count;
       
    END;
       
       
    FUNCTION count_surveys_national_dist
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2
    )
        RETURN number
    IS
        record_count number := 0;
        
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
    BEGIN

        dbms_output.put_line( 'FUNCTION count_surveys_national_dist' );
        dbms_output.put_line( 'survey version' || p_survey_version || ', survey type: ' || p_survey_type || ', status: ' || p_status );
        
        IF ( nn_survey_version = 'All Versions' )
        THEN
            BEGIN
                IF ( nn_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        SELECT   count(*)
                        INTO     record_count
                        FROM     survey_response sr
                        WHERE    sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                ELSE
                    BEGIN
                        SELECT   count(*)
                        INTO     record_count
                        FROM     survey_response sr, survey s
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.TITLE = nn_survey_type
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( nn_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        SELECT   count(*)
                        INTO     record_count
                        FROM     survey_response sr, survey s
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.version = nn_survey_version
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status;
                   END;
                ELSE
                    BEGIN
                        SELECT   count(*)
                        INTO     record_count
                        FROM     survey_response sr, survey s
                        WHERE    sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.version = nn_survey_version
                        AND      sr.SURVEY_ACCEPTED_STATUS = p_status
                        AND      s.TITLE = nn_survey_type;
                    END;
                END IF;
            END;
        END IF;

        RETURN record_count;

    END;
    
    
    FUNCTION count_dmps
    (
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_vendor         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
        
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        nn_location varchar2(128) := check_location( p_location );
    BEGIN

        dbms_output.put_line( 'FUNCTION count_dmps' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || chr(9) || 'location: ' || p_location || chr(9) || 'vendor: ' || p_vendor );
        dbms_output.put_line( 'start date: ' || p_start_date || chr(9) || 'end date: ' || p_end_date );
        
        IF ( substr( nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                record_count := count_dmps_national( nn_survey_type, p_vendor, p_start_date, p_end_date );
            END;
        ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
        THEN
            BEGIN
                record_count := count_dmps_visn( nn_survey_type, nn_location, p_vendor, p_start_date, p_end_date );
            END;
-- uncomment these 4 lines per Laury Allred on 10/21
        ELSE
            BEGIN
                record_count := count_dmps_facility( nn_survey_type, nn_location, p_vendor, p_start_date, p_end_date );
            END;
        END IF;
        
        RETURN record_count;

    END;


    FUNCTION count_dmps_national
    (
        p_survey_type    IN  varchar2,
        p_vendor         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN

        dbms_output.put_line( 'FUNCTION count_dmps_national' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || chr(9) || 'vendor: ' || p_vendor );
        
        IF ( p_survey_type = 'All Surveys' )
        THEN
            BEGIN
                IF ( p_vendor is null )
                THEN
                    BEGIN
                        SELECT   count(distinct(icn)) 
                        INTO     record_count
                        FROM     dmp_response sr
                        WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date;
                    END;
                ELSE
                    BEGIN
                        SELECT   count(distinct(icn)) 
                        INTO     record_count
                        FROM     dmp_response sr, vendor v
                        WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.vendor_id = v.vendor_id
                        AND      v.VENDOR_FACILITY = p_vendor;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( p_vendor is null )
                THEN
                    BEGIN
                        SELECT   count(distinct(icn)) 
                        INTO     record_count
                        FROM     dmp_response sr, survey s 
                        WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.TITLE = p_survey_type;
                    END;
                ELSE
                    BEGIN
                        SELECT   count(distinct(icn)) 
                        INTO     record_count
                        FROM     dmp_response sr, survey s, vendor v 
                        WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                        AND      sr.SURVEY_ID = s.SURVEY_ID
                        AND      s.TITLE = p_survey_type
                        AND      sr.vendor_id = v.vendor_id
                        AND      v.VENDOR_FACILITY = p_vendor;
                    END;
                END IF;
            END;
        END IF;
    
        RETURN record_count;

    END;
    
    
    FUNCTION count_dmps_visn
    (
        p_survey_type    IN  varchar2,
        p_visn           IN  varchar2,
        p_vendor         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN

        dbms_output.put_line( 'FUNCTION count_dmps_visn' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || chr(9) || 'visn: ' || p_visn || chr(9) || 'vendor: ' || p_vendor  );
        
        IF ( p_visn = 'All VISNs' )
        THEN
            BEGIN
                record_count := count_dmps_national( p_survey_type, p_vendor, p_start_date, p_end_date );
            END;
        ELSE
            BEGIN
                IF ( p_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        IF ( p_vendor is null )
                        THEN
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, facility sf, vendor v
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn)
                                AND      sr.vendor_id = v.vendor_id
                                AND      v.VENDOR_FACILITY = p_vendor;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( p_vendor is null )
                        THEN
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, survey s, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn);
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, survey s, facility sf, vendor v
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.VISN = to_number(p_visn)
                                AND      sr.vendor_id = v.vendor_id
                                AND      v.VENDOR_FACILITY = p_vendor;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
        
        RETURN record_count;

    END;
    

    FUNCTION count_dmps_facility
    (
        p_survey_type    IN  varchar2,
        p_station_no     IN  varchar2,
        p_vendor         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
        RETURN number
    IS
        record_count number := 0;
    BEGIN
    
        dbms_output.put_line( 'FUNCTION count_dmps_facility' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || chr(9) || 'station: ' || p_station_no || chr(9) || 'vendor: ' || p_vendor );
        
        IF ( substr( p_station_no, 1, 9 ) = 'All VistA' )
        THEN
            BEGIN
                record_count := count_dmps_national( p_survey_type, p_vendor, p_start_date, p_end_date );
            END;
        ELSE
            BEGIN
                IF ( p_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        IF ( p_vendor is null )
                        THEN
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, facility sf, vendor v
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no
                                AND      sr.vendor_id = v.vendor_id
                                AND      v.VENDOR_FACILITY = p_vendor;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( p_vendor is null )
                        THEN
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, survey s, facility sf
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no;
                            END;
                        ELSE
                            BEGIN
                                SELECT   count(distinct(icn)) 
                                INTO     record_count
                                FROM     dmp_response sr, survey s, facility sf, vendor v
                                WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                                AND      sr.SURVEY_ID = s.SURVEY_ID
                                AND      s.TITLE = p_survey_type
                                AND      sr.FACILITY_ID = sf.FACILITY_ID
                                AND      sf.STATION_NO = p_station_no
                                AND      sr.vendor_id = v.vendor_id
                                AND      v.VENDOR_FACILITY = p_vendor;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;

        RETURN record_count;

    END;


    FUNCTION buildDynamicQuery
    (
        p_select IN  varchar2,
        p_from   IN  varchar2,
        p_where  IN  varchar2,
        p_group  IN  varchar2
    )
        RETURN varchar2
    IS
        d_query  varchar2(2048);
    BEGIN
    
        dbms_output.put_line( 'FUNCTION buildDynamicQuery' );
        
        d_query := p_select;
        d_query := CONCAT( d_query, p_from );
        d_query := CONCAT( d_query, p_where );
        d_query := CONCAT( d_query, p_group );
        
        RETURN d_query;
    END;


    PROCEDURE count_dmps_proc
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_vendor         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
    BEGIN
        OPEN p_cnt_cursor FOR         
        SELECT count_dmps( p_survey_type, p_location, p_vendor, p_start_date, p_end_date ) record_count, p_location location
        FROM dual;

    END;


    PROCEDURE count_surveys_proc
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
    BEGIN
        OPEN p_cnt_cursor FOR         
        SELECT count_surveys( p_survey_version, p_survey_type, p_location, p_status, p_start_date, p_end_date ) records_considered
        FROM dual;

    END;


    PROCEDURE count_national_dist_mv
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2
    )
    IS
    BEGIN
        OPEN p_cnt_cursor FOR
        SELECT count_surveys_national_dist_mv( p_survey_version, p_survey_type, p_status ) records_considered
        FROM dual;

    END;

    
    PROCEDURE count_national_dist_proc
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2
    )
    IS
    BEGIN
        OPEN p_cnt_cursor FOR
        SELECT count_surveys_national_dist( p_survey_version, p_survey_type, p_status ) records_considered
        FROM dual;

    END;

    
    PROCEDURE count_surveys_proc_national
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
        v_cnt_cursor SYS_REFCURSOR;
        
        v_version  varchar2(128)  := check_survey_version( p_survey_version );
        v_type     varchar2(128)  := check_survey_type( p_survey_type );
        v_location varchar2(128)  := check_location( p_location );
        v_status   varchar2(128)  := check_status( p_status );
        
        v_record_count number := 0;
    BEGIN
    
        dbms_output.put_line( 'PROCEDURE count_surveys_proc_national' );

        ht_survey_report.count_surveys_proc_new( v_cnt_cursor, v_version, v_type, v_location, v_status, p_start_date, p_end_date );
        
        loop
            fetch v_cnt_cursor
            into  v_location, v_record_count;
            exit when v_cnt_cursor%notfound;
            dbms_output.put_line('Location: ' || v_location || '  Record Count: ' || v_record_count );
        end loop;
        close v_cnt_cursor;
        
        OPEN p_cnt_cursor FOR         
        SELECT v_record_count records_considered
        FROM dual;
   END;

    
    PROCEDURE count_surveys_proc_new
    (
        p_cnt_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_status         IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
        d_query    varchar2(1024);
        d_select   varchar2(256) := ' SELECT   :sloc location, count(*) records_considered ';
        d_from     varchar2(128) := ' FROM     survey_response sr, survey s ';
        d_where    varchar2(512) := ' WHERE    sr.SURVEY_ID = s.SURVEY_ID AND      sr.COMPLETED_DT BETWEEN :start_date AND :end_date ';
        d_group    varchar2(512) := ' ';
        
        v_start    date := p_start_date;
        v_end      date := p_end_date;
    BEGIN
       
        dbms_output.put_line( 'PROCEDURE count_surveys_proc_new' );
        dbms_output.put_line( 'survey version: ' || p_survey_version || ', survey type: ' || p_survey_type || ', location: ' || p_location || ', status: ' || p_status || ', start_date: ' || p_start_date || ', end_date: ' || p_end_date );

        IF (  p_start_date is null )
        THEN
            BEGIN
                v_start := to_date ('1800/01/01', 'yyyy/mm/dd');
            END;
        END IF;
        
        IF (  p_end_date is null )
        THEN
            BEGIN
                v_end := to_date ('3000/01/01', 'yyyy/mm/dd');
            END;
        END IF;
        
        IF ( ( p_survey_version = 'All Versions' ) AND ( p_survey_type = 'All Surveys' ) ) 
        THEN
            BEGIN
                d_from  := ' FROM     survey_response sr ';
                d_where := ' WHERE    sr.COMPLETED_DT BETWEEN :start_date AND :end_date ';
            END;
        END IF;
        
        IF NOT ( p_survey_version = 'All Versions' )
        THEN
            BEGIN
                d_where := REPLACE( d_where, 'sr.COMPLETED_DT', 's.version = :survey_version AND      sr.COMPLETED_DT' );
            END;
        END IF;
        
        IF NOT ( p_survey_type = 'All Surveys' )
        THEN
            BEGIN
                d_where := REPLACE( d_where, 'sr.COMPLETED_DT', 's.TITLE = :survey_type  AND      sr.COMPLETED_DT' );
            END;
        END IF;
        
        IF NOT ( p_status = 'All Statuses' )
        THEN
            BEGIN
                d_where := REPLACE( d_where, 'sr.COMPLETED_DT', 'sr.SURVEY_ACCEPTED_STATUS = :status  AND      sr.COMPLETED_DT' );
            END;
        END IF;
        
        IF ( p_location = 'National' )
        THEN
            BEGIN
                d_select := REPLACE( d_select, ':sloc', '''National''' );
                d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
            END;
        ELSE
            BEGIN
                d_from  := CONCAT( d_from, ', facility sf ' );
                d_where := CONCAT( d_where, ' AND      sr.FACILITY_ID = sf.FACILITY_ID ' );
                IF ( p_location = 'All VISNs' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_group  := ' GROUP BY sf.VISN  ORDER BY sf.VISN ';
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF ( substr( p_location, 1, 9 ) = 'All VistA' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_group  := ' GROUP BY sf.STATION_NO  ORDER BY sf.STATION_NO ';
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF REGEXP_LIKE( p_location,'^[[:digit:]]{1,2}$' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_where  := CONCAT( d_where, ' AND      sf.VISN = to_number(:location) ' );
                        d_group  := ' GROUP BY sf.VISN ';
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSE
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_where  := CONCAT( d_where, ' AND      sf.STATION_NO = :location ' );
                        d_group  := ' GROUP BY sf.STATION_NO ';
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                END IF;
            END;
        END IF;
                        
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( substr (p_location, 1, 4 ) = 'All ' ) OR ( p_location = 'National' )
        THEN
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( p_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING v_start, v_end;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_status, v_start, v_end;
                                    END;
                                END IF;
                            END;
                        ELSE
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_type, v_start, v_end;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_type, p_status, v_start, v_end;
                                    END;
                                END IF;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( p_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, v_start, v_end;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_status, v_start, v_end;
                                    END;
                                END IF;
                            END;
                        ELSE
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_survey_type, v_start, v_end;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_survey_type, p_status, v_start, v_end;
                                    END;
                                END IF;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( p_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING v_start, v_end, p_location;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_status, v_start, v_end, p_location;
                                    END;
                                END IF;
                            END;
                        ELSE
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_type, v_start, v_end, p_location;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_type, p_status, v_start, v_end, p_location;
                                    END;
                                END IF;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( p_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, v_start, v_end, p_location;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_status, v_start, v_end, p_location;
                                    END;
                                END IF;
                            END;
                        ELSE
                            BEGIN
                                IF ( p_status = 'All Statuses' )
                                THEN
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_survey_type, v_start, v_end, p_location;
                                    END;
                                ELSE
                                    BEGIN
                                        OPEN p_cnt_cursor FOR d_query USING p_survey_version, p_survey_type, p_status, v_start, v_end, p_location;
                                    END;
                                END IF;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
                
    END;


    PROCEDURE vr12_data_mv
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_location   IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    )
    IS
        nn_location varchar2(128) := check_location( p_location );
        
        d_query  varchar2(1024);
        
       --d_select varchar2(512) := ' SELECT   :sloc location, nvl( sum( srmv.record_count ), 0 ) record_count, (nvl(sum(srmv.MENTAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)) mental_score_average, (nvl(sum(srmv.PHYSICAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)) physical_score_average ';
       --d_select varchar2(512) := ' SELECT   :sloc location, nvl( sum( srmv.record_count ), 0 ) record_count, (round(nvl(sum(srmv.MENTAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)), 3)) mental_score_average, (round(nvl(sum(srmv.PHYSICAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)), 3)) physical_score_average ';

        d_select varchar2(512) := ' SELECT   :sloc location, nvl( sum( srmv.record_count ), 0 ) record_count, round((nvl(sum(srmv.MENTAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)), 3) mental_score_average, round((nvl(sum(srmv.PHYSICAL_SCORE_SUM), 0)/nvl(sum( srmv.record_count ), 1)), 3) physical_score_average ';        
        d_from   varchar2(128) := ' FROM     surveys_mv_so.surveys_responses_mv srmv ';
        d_where  varchar2(256) := ' WHERE    srmv.TITLE = ''VR-12 ADL'' AND      srmv.SURVEY_DATE BETWEEN :start_date AND :end_date ';
        d_group  varchar2(128)  := '';
    BEGIN

        dbms_output.put_line( 'PROCEDURE vr12_data_mv' );
        dbms_output.put_line( 'location: ' || nn_location);
        
        IF ( nn_location = 'National' )
        THEN
            BEGIN
                d_select := REPLACE( d_select, ':sloc', '''National''' );
                d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
            END;
        ELSE
            BEGIN
                IF ( nn_location = 'All VISNs' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'srmv.VISN' );
                        d_group  := CONCAT( d_group, ' GROUP BY srmv.VISN ORDER BY srmv.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF ( substr( nn_location, 1, 9 ) = 'All VistA' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'srmv.STATION_NO' );
                        d_group  := CONCAT( d_group, ' GROUP BY srmv.STATION_NO ORDER BY srmv.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'srmv.VISN' );
                        d_where  := CONCAT( d_where, ' AND      srmv.VISN = to_number(:location) ' );
                        d_group  := CONCAT( d_group, ' GROUP BY srmv.VISN ORDER BY srmv.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSE
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'srmv.STATION_NO' );
                        d_where  := CONCAT( d_where, ' AND      srmv.STATION_NO = :location ' );
                        d_group  := CONCAT( d_group, ' GROUP BY srmv.STATION_NO ORDER BY srmv.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                END IF;
            END;
        END IF;
                        
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( substr(nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date;
            END;
        ELSE
            BEGIN
                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_location;
            END;
        END IF;
                
    END;


    PROCEDURE vr12_data
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_location   IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    )
    IS
        nn_location varchar2(128) := check_location( p_location );
        
        d_query  varchar2(1024);
        d_select varchar2(256) := ' SELECT   :sloc location, count(*) record_count, round(nvl(avg(sr.MENTAL_SCORE), 0), 3) mental_score_average, round(nvl(avg(sr.PHYSICAL_SCORE), 0), 3) physical_score_average ';
        d_from   varchar2(128) := ' FROM     survey_response sr, survey s ';
        d_where  varchar2(512) := ' WHERE    sr.SURVEY_ID = s.SURVEY_ID AND      s.TITLE = ''VR-12 ADL'' AND      sr.MENTAL_SCORE IS NOT NULL AND      sr.PHYSICAL_SCORE IS NOT NULL AND      sr.COMPLETED_DT BETWEEN :start_date AND :end_date AND      sr.SURVEY_ACCEPTED_STATUS = ''ACCEPTED'' ';
        d_group  varchar2(128)  := '';
    BEGIN

        dbms_output.put_line( 'PROCEDURE vr12_data' );
        dbms_output.put_line( 'location: ' || nn_location);
        
        IF ( nn_location = 'National' )
        THEN
            BEGIN
                d_select := REPLACE( d_select, ':sloc', '''National''' );
                d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
            END;
        ELSE
            BEGIN
                d_from  := CONCAT( d_from, ', facility sf ' );
                d_where := CONCAT( d_where, ' AND      sr.FACILITY_ID = sf.FACILITY_ID ' );
                IF ( nn_location = 'All VISNs' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_group  := CONCAT( d_group, ' GROUP BY sf.VISN ORDER BY sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF ( substr( nn_location, 1, 9 ) = 'All VistA' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_group  := CONCAT( d_group, ' GROUP BY sf.STATION_NO ORDER BY sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_where  := CONCAT( d_where, ' AND      sf.VISN = to_number(:location) ' );
                        d_group  := CONCAT( d_group, ' GROUP BY sf.VISN ORDER BY sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSE
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_where  := CONCAT( d_where, ' AND      sf.STATION_NO = :location ' );
                        d_group  := CONCAT( d_group, ' GROUP BY sf.STATION_NO ORDER BY sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                END IF;
            END;
        END IF;
                        
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( substr(nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date;
            END;
        ELSE
            BEGIN
                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_location;
            END;
        END IF;
                
    END;


    PROCEDURE distribution
    (
        p_avg_cursor  OUT SYS_REFCURSOR,
        p_survey_type IN  varchar2,
        p_location    IN  varchar2,
        p_start_date  IN  date,
        p_end_date    IN  date
    )
    IS
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        nn_location varchar2(128) := check_location( p_location );
        
        d_query  varchar2(1024);
        d_select varchar2(256) := ' SELECT   (v.vendor_facility || :sloc) key, :sloc location, v.vendor_facility, count(*) record_count ';
        d_from   varchar2(128) := ' FROM     survey_response sr, vendor v ';
        d_where  varchar2(512) := ' WHERE    sr.COMPLETED_DT BETWEEN :start_date AND :end_date AND      sr.SURVEY_ACCEPTED_STATUS = ''ACCEPTED'' AND      sr.vendor_id = v.vendor_id ';
        d_group  varchar2(512) := ' GROUP BY v.vendor_facility';
    BEGIN

        dbms_output.put_line( 'PROCEDURE distribution' );
        dbms_output.put_line( 'survey type: ' || nn_survey_type || ', location: ' || nn_location);

        IF ( NOT ( nn_survey_type = 'All Surveys' ) )
        THEN
            BEGIN
                d_from  := CONCAT( d_from, ', survey s ' );
                d_where := CONCAT( d_where, ' AND      sr.SURVEY_ID = s.SURVEY_ID AND      s.TITLE = :survey_type ' );
            END;
        END IF;
        
        IF ( nn_location = 'National' )
        THEN
            BEGIN
                d_select := REPLACE( d_select, ':sloc', '''National''' );
                d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
            END;
        ELSE
            BEGIN
                d_from  := CONCAT( d_from, ', facility sf ' );
                d_where := CONCAT( d_where, ' AND      sr.FACILITY_ID = sf.FACILITY_ID ' );
                IF ( nn_location = 'All VISNs' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_group  := CONCAT( d_group, ', sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF ( substr( nn_location, 1, 9 ) = 'All VistA' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_group  := CONCAT( d_group, ', sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_where  := CONCAT( d_where, ' AND      sf.VISN = to_number(:location) ' );
                        d_group  := CONCAT( d_group, ', sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSE
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_where  := CONCAT( d_where, ' AND      sf.STATION_NO = :location ' );
                        d_group  := CONCAT( d_group, ', sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                END IF;
            END;
        END IF;
                        
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( substr(nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                IF ( nn_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_survey_type;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( nn_survey_type = 'All Surveys' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_location;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_survey_type, nn_location;
                    END;
                END IF;
            END;
        END IF;
                
    END;


    PROCEDURE patient_satisfaction_data (p_avg_cursor     OUT SYS_REFCURSOR,
                                         p_survey_version IN  varchar2,
                                         p_questions      IN  varchar2,
                                         p_location       IN  varchar2,
                                         p_start_date     IN  DATE,
                                         p_end_date       IN  DATE)
    IS
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_location varchar2(128) := check_location( p_location );
        
        d_maxChoice   NUMBER := 6;
        d_query       varchar2 (2048);
        d_select      varchar2 (400)  := ' SELECT   (to_char(sr.COMPLETED_DT,''yyyy'') || to_char(sr.COMPLETED_DT,''mm'') || sq.QUESTION_NUMBER || :sloc) key, :sloc location, to_char(sr.COMPLETED_DT,''yyyy'') year, to_char(sr.COMPLETED_DT,''mm'') month, sq.QUESTION_NUMBER, sum(sc.NORMALIZED_VALUE) question_sum, count(*) record_count, ((sum(sc.NORMALIZED_VALUE) / count(*) / (:maxChoice - 1)) * 100) score ';
        d_from        varchar2 (256)  := ' FROM     survey_response sr, survey s, survey_question_answer sa, survey_choice sc, survey_question sq ';
        d_where       varchar2 (1024) := ' WHERE    sr.SURVEY_ID = s.SURVEY_ID AND      s.TITLE = ''Patient Satisfaction'' AND      s.version = :version AND      sr.COMPLETED_DT BETWEEN :start_date AND :end_date AND      sr.SURVEY_ACCEPTED_STATUS = ''ACCEPTED'' AND      sr.SURVEY_RESPONSE_ID = sa.SURVEY_RESPONSE_ID AND      sa.SURVEY_CHOICE_ID = sc.SURVEY_CHOICE_ID AND      sc.SURVEY_QUESTION_ID = sq.SURVEY_QUESTION_ID AND      sq.QUESTION_NUMBER in (SELECT * FROM THE (SELECT CAST (ht_survey_report.str2tbl( :questions ) AS MYTABLETYPE) FROM DUAL))  AND      sc.CHOICE_NUMBER != :maxChoice ';
        d_group       varchar2 (512)  := ' GROUP BY to_char(sr.COMPLETED_DT,''yyyy''), to_char(sr.COMPLETED_DT,''mm''), sq.QUESTION_NUMBER ORDER BY to_char(sr.COMPLETED_DT,''yyyy''), to_char(sr.COMPLETED_DT,''mm''), sq.QUESTION_NUMBER';
    BEGIN
    
       dbms_output.put_line( 'PROCEDURE Patient_Satisfaction_data' );
       dbms_output.put_line( 'survey version: ' || nn_survey_version || ',  survey questions: ' || p_questions || ',  location: ' || nn_location);

       IF ( nn_survey_version = '2.0' )
       THEN
          BEGIN
             d_maxChoice := 5;
          END;
       END IF;

       IF ( SUBSTR( nn_location, 1, 4 ) = 'All ' ) OR ( nn_location = 'National' )
       THEN
          BEGIN
             d_select := REPLACE( d_select, ':sloc', '''National''' );
             d_query := buildDynamicQuery ( d_select, d_from, d_where, d_group );
          END;
       ELSE
          BEGIN
             d_from := CONCAT( d_from, ', facility sf ' );
             d_where := CONCAT( d_where, ' AND      sr.FACILITY_ID = sf.FACILITY_ID ' );

             IF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
             THEN
                BEGIN
                   d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                   d_where := CONCAT( d_where, ' AND      sf.VISN = to_number(:location) ' );
                   d_group := REPLACE( d_group, 'GROUP BY ', 'GROUP BY sf.VISN, ' );
                   d_group := REPLACE( d_group, 'ORDER BY ', 'ORDER BY sf.VISN, ' );
                   d_query := buildDynamicQuery ( d_select, d_from, d_where, d_group );
                END;
             ELSE
                BEGIN
                   d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                   d_where := CONCAT( d_where, ' AND      sf.STATION_NO = :location ' );
                   d_group := REPLACE( d_group, 'GROUP BY ', 'GROUP BY sf.STATION_NO, ' );
                   d_group := REPLACE( d_group, 'ORDER BY ', 'ORDER BY sf.STATION_NO, ' );
                   d_query := buildDynamicQuery( d_select, d_from, d_where, d_group );
                END;
             END IF;
          END;
       END IF;

       dbms_output.put_line( 'QUERY: ' || d_query );

       IF ( SUBSTR( nn_location, 1, 4 ) = 'All ' ) OR ( nn_location = 'National' )
       THEN
          BEGIN
             OPEN p_avg_cursor FOR d_query USING d_maxChoice, nn_survey_version, p_start_date, p_end_date, p_questions, d_maxChoice;
          END;
       ELSE
          BEGIN
             OPEN p_avg_cursor FOR d_query USING d_maxChoice, nn_survey_version, p_start_date, p_end_date, p_questions, d_maxChoice, nn_location;
          END;
       END IF;
    END;


    PROCEDURE trends
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_location       IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        nn_location varchar2(128) := check_location( p_location );
        
        d_query  varchar2(1024);
        d_select varchar2(256) := ' SELECT   :sloc location, count(*) record_count ';
        d_from   varchar2(128) := ' FROM     survey_response sr, survey s ';
        d_where  varchar2(512) := ' WHERE    sr.SURVEY_ID = s.SURVEY_ID AND      sr.COMPLETED_DT BETWEEN :start_date AND :end_date AND      sr.SURVEY_ACCEPTED_STATUS = ''ACCEPTED'' ';
        d_group  varchar2(512) := ' ';
    BEGIN

        dbms_output.put_line( 'PROCEDURE trends' );
        dbms_output.put_line( 'survey version: ' || nn_survey_version || ', survey type: ' || nn_survey_type || ', location: ' || nn_location);

        IF ( nn_survey_version = 'All Versions' ) AND ( nn_survey_type = 'All Surveys' )  
        THEN
            BEGIN
                d_from  := ' FROM     survey_response sr ';
                d_where := ' WHERE    sr.COMPLETED_DT BETWEEN :start_date AND :end_date AND      sr.SURVEY_ACCEPTED_STATUS = ''ACCEPTED'' ';
            END;
        END IF;
        
        IF NOT ( nn_survey_version = 'All Versions' )
        THEN
            BEGIN
                d_where := REPLACE( d_where, 's.SURVEY_ID', 's.SURVEY_ID AND      s.version = :survey_version ' );
            END;
        END IF;
        
        IF ( NOT ( nn_survey_type = 'All Surveys' ) )
        THEN
            BEGIN
                d_where := CONCAT( d_where, ' AND      s.TITLE = :nn_survey_type ' );
            END;
        END IF;
        
        IF ( nn_location = 'National' )
        THEN
            BEGIN
                d_select := REPLACE( d_select, ':sloc', '''National''' );
                d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
            END;
        ELSE
            BEGIN
                d_from  := CONCAT( d_from, ', facility sf ' );
                d_where := CONCAT( d_where, ' AND      sr.FACILITY_ID = sf.FACILITY_ID ' );
                IF ( nn_location = 'All VISNs' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_group  := CONCAT( d_group, 'GROUP BY sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF ( substr( nn_location, 1, 9 ) = 'All VistA' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_group  := CONCAT( d_group, 'GROUP BY sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSIF REGEXP_LIKE( nn_location,'^[[:digit:]]{1,2}$' )
                THEN
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.VISN' );
                        d_where  := CONCAT( d_where, ' AND      sf.VISN = to_number(:location) ' );
                        d_group  := CONCAT( d_group, 'GROUP BY sf.VISN' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                ELSE
                    BEGIN
                        d_select := REPLACE( d_select, ':sloc', 'sf.STATION_NO' );
                        d_where  := CONCAT( d_where, ' AND      sf.STATION_NO = :location ' );
                        d_group  := CONCAT( d_group, 'GROUP BY sf.STATION_NO' );
                        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
                    END;
                END IF;
            END;
        END IF;
                        
        dbms_output.put_line( 'QUERY: ' || d_query );
        
        IF ( substr( nn_location, 1, 4) = 'All ' ) OR ( nn_location = 'National' )
        THEN
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( nn_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_survey_type;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( nn_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING nn_survey_version, p_start_date, p_end_date;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING nn_survey_version, p_start_date, p_end_date, nn_survey_type;
                            END;
                        END IF;
                    END;
                END IF;

            END;
        ELSE
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( nn_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_location;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING p_start_date, p_end_date, nn_survey_type, nn_location;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( nn_survey_type = 'All Surveys' )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING nn_survey_version, p_start_date, p_end_date, nn_location;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR d_query USING nn_survey_version, p_start_date, p_end_date, nn_survey_type, nn_location;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
                
    END;


    PROCEDURE vr12_data_national
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_start_date IN  date,
        p_end_date   IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE vr12_data_national' );
        
        OPEN p_avg_cursor FOR
            SELECT   'National' location, count(*) record_count, avg(sr.MENTAL_SCORE) mental_score_average, avg(sr.PHYSICAL_SCORE) physical_score_average
            FROM     survey_response sr, survey s
            WHERE    sr.SURVEY_ID = s.SURVEY_ID
            AND      s.TITLE = 'VR-12 ADL'
            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED';
        
    END;


    PROCEDURE vr12_data_visn
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_visn       IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE vr12_data_visn' );
        dbms_output.put_line( 'visn: ' || p_visn );
        
        IF ( p_visn = 'All VISNs' )
        THEN
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   sf.VISN location, count(*) record_count, avg(sr.MENTAL_SCORE) mental_score_average, avg(sr.PHYSICAL_SCORE) physical_score_average
                    FROM     survey_response sr, facility sf, survey s
                    WHERE    sr.SURVEY_ID = s.SURVEY_ID
                    AND      s.TITLE = 'VR-12 ADL'
                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.FACILITY_ID = sf.FACILITY_ID
                    GROUP BY sf.VISN;
            END;
        ELSE
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   sf.VISN location, count(*) record_count, avg(sr.MENTAL_SCORE) mental_score_average, avg(sr.PHYSICAL_SCORE) physical_score_average
                    FROM     survey_response sr, facility sf, survey s
                    WHERE    sr.SURVEY_ID = s.SURVEY_ID
                    AND      s.TITLE = 'VR-12 ADL'
                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.FACILITY_ID = sf.FACILITY_ID
                    AND      sf.VISN = to_number(p_visn)
                    GROUP BY sf.VISN;
            END;
        END IF;

    END;


    PROCEDURE vr12_data_facility
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_station_no IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE vr12_data_facility' );
        dbms_output.put_line( 'station: ' || p_station_no );
        
        IF ( substr( p_station_no, 1, 9 ) = 'All VistA' )
        THEN
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   sf.STATION_NO location, count(*) record_count, avg(sr.MENTAL_SCORE) mental_score_average, avg(sr.PHYSICAL_SCORE) physical_score_average
                    FROM     survey_response sr, facility sf, survey s
                   WHERE    sr.SURVEY_ID = s.SURVEY_ID
                    AND      s.TITLE = 'VR-12 ADL'
                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.FACILITY_ID = sf.FACILITY_ID
                    GROUP BY sf.STATION_NO;
            END;
        ELSE
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   sf.STATION_NO location, count(*) record_count, avg(sr.MENTAL_SCORE) mental_score_average, avg(sr.PHYSICAL_SCORE) physical_score_average
                    FROM     survey_response sr, facility sf, survey s
                    WHERE    sr.SURVEY_ID = s.SURVEY_ID
                    AND      s.TITLE = 'VR-12 ADL'
                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.FACILITY_ID = sf.FACILITY_ID
                    AND      sf.STATION_NO = p_station_no
                    GROUP BY sf.STATION_NO;
            END;
        END IF;

    END;
    
    
    PROCEDURE distribution_national
    (
        p_avg_cursor  OUT SYS_REFCURSOR,
        p_survey_type IN  varchar2,
        p_start_date  IN  date,
        p_end_date    IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE distribution_national' );
        dbms_output.put_line( 'survey type: ' || p_survey_type );
        
        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
        THEN
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   'National' location, v.vendor_facility, count(*) record_count
                    FROM     survey_response sr, vendor v 
                    WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.vendor_id = v.vendor_id
                    GROUP BY v.vendor_facility;
            END;
        ELSE
            BEGIN
                OPEN p_avg_cursor FOR
                    SELECT   'National' location, v.vendor_facility, count(*) record_count
                    FROM     survey_response sr, survey s, vendor v 
                    WHERE    sr.SURVEY_ID = s.SURVEY_ID
                    AND      s.TITLE = p_survey_type
                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                    AND      sr.vendor_id = v.vendor_id
                    GROUP BY v.vendor_facility;
            END;
        END IF;
    END;
    

    PROCEDURE distribution_visn
    (
        p_avg_cursor  OUT SYS_REFCURSOR,
        p_survey_type IN  varchar2,
        p_visn        IN  varchar2,
        p_start_date  IN  date,
        p_end_date    IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE distribution_visn' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || ', visn: ' || p_visn );
        
        IF ( p_visn = 'All VISNs' )
        THEN
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.VISN location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, vendor v
                            WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            GROUP BY v.VENDOR_FACILITY, sf.VISN ;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.VISN location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, survey s, vendor v
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            GROUP BY v.VENDOR_FACILITY, sf.VISN ;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.VISN location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, vendor v
                            WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            AND      sf.VISN = to_number(p_visn)
                            GROUP BY v.VENDOR_FACILITY, sf.VISN ;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.VISN location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, survey s, vendor v
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            AND      sf.VISN = to_number(p_visn)
                            GROUP BY v.VENDOR_FACILITY, sf.VISN ;
                    END;
                END IF;
            END;
        END IF;

    END;


    PROCEDURE distribution_facility
    (
        p_avg_cursor  OUT SYS_REFCURSOR,
        p_survey_type IN  varchar2,
        p_station_no  IN  varchar2,
        p_start_date  IN  date,
        p_end_date    IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE distribution_facility' );
        dbms_output.put_line( 'survey type: ' || p_survey_type || ', station: ' || p_station_no );
        
        IF ( substr( p_station_no, 1, 9 ) = 'All VistA' )
        THEN
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.STATION_NO location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, vendor v
                            WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            GROUP BY v.VENDOR_FACILITY, sf.STATION_NO;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.STATION_NO location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, survey s, vendor v
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            GROUP BY v.VENDOR_FACILITY, sf.STATION_NO;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.STATION_NO location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, vendor v
                            WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            AND      sf.STATION_NO = p_station_no
                            GROUP BY v.VENDOR_FACILITY, sf.STATION_NO;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   sf.STATION_NO location, v.VENDOR_FACILITY, count(*) record_count
                            FROM     survey_response sr, facility sf, survey s, vendor v
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'
                            AND      sr.FACILITY_ID = sf.FACILITY_ID
                            AND      sr.VENDOR_ID = v.VENDOR_ID
                            AND      sf.STATION_NO = p_station_no
                            GROUP BY v.VENDOR_FACILITY, sf.STATION_NO;
                    END;
                END IF;
            END;
        END IF;

    END;


    PROCEDURE trends_national
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE trends_national' );
        dbms_output.put_line( 'survey version: ' || p_survey_version || ', survey type: ' || p_survey_type );
        
        IF ( p_survey_version = 'All Versions' )
        THEN
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR
                            SELECT   'National' location, count(*) record_count
                            FROM     survey_response sr 
                            WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED';
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR 
                            SELECT   'National' location, count(*) record_count
                            FROM     survey_response sr, survey s  
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED';
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR 
                            SELECT   'National' location, count(*) record_count
                            FROM     survey_response sr, survey s
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID
                            AND      s.version = p_survey_version  
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED';
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR 
                            SELECT   'National' location, count(*) record_count
                            FROM     survey_response sr, survey s  
                            WHERE    sr.SURVEY_ID = s.SURVEY_ID  
                            AND      s.version = p_survey_version
                            AND      s.TITLE = p_survey_type
                            AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date
                            AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED';
                    END;
                END IF;
            END;
        END IF;
        
    END;


    PROCEDURE trends_visn
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_visn           IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE trends_visn' );
        dbms_output.put_line( 'survey version: ' || p_survey_version || ', survey type: ' || p_survey_type || ', visn: ' || p_visn );
        
        IF ( substr(p_visn, 1, 4) = 'All ' ) OR ( p_visn = 'National' )
        THEN
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr , facility sf  
                                    WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.VISN;                               
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf 
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.VISN;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.VISN;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED' 
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.VISN;
                            END;
                        END IF;
                    END;
                END IF;

            END;
        ELSE
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr , facility sf  
                                    WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.VISN = to_number(p_visn)  
                                    GROUP BY sf.VISN;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf 
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.VISN = to_number(p_visn)  
                                    GROUP BY sf.VISN;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.VISN = to_number(p_visn)  
                                    GROUP BY sf.VISN;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.VISN location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.VISN = to_number(p_visn)  
                                    GROUP BY sf.VISN;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
        
    END;


    PROCEDURE trends_facility
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_station_no     IN  varchar2,
        p_start_date     IN  date,
        p_end_date       IN  date
    )
    IS
    BEGIN

        dbms_output.put_line( 'PROCEDURE trends_facility' );
        dbms_output.put_line( 'survey version: ' || p_survey_version || ', survey type: ' || p_survey_type || ', station: ' || p_station_no );
        
        IF ( substr( p_station_no, 1, 4) = 'All ' ) OR ( p_station_no = 'National' )
        THEN
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr , facility sf  
                                    WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.STATION_NO;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.STATION_NO;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.STATION_NO;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    GROUP BY sf.STATION_NO;
                            END;
                        END IF;
                    END;
                END IF;

            END;
        ELSE
            BEGIN
                IF ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr , facility sf  
                                    WHERE    sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.STATION_NO = p_station_no  
                                    GROUP BY sf.STATION_NO;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.STATION_NO = p_station_no  
                                    GROUP BY sf.STATION_NO;
                            END;
                        END IF;
                    END;
                ELSE
                    BEGIN
                        IF ( ( p_survey_type = 'All Surveys' ) OR ( p_survey_type is null ) )
                        THEN
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID 
                                    AND      s.version = p_survey_version  
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.STATION_NO = p_station_no  
                                    GROUP BY sf.STATION_NO;
                            END;
                        ELSE
                            BEGIN
                                OPEN p_avg_cursor FOR 
                                    SELECT   sf.STATION_NO location, count(*) record_count  
                                    FROM     survey_response sr, survey s , facility sf  
                                    WHERE    sr.SURVEY_ID = s.SURVEY_ID
                                    AND      s.version = p_survey_version
                                    AND      sr.COMPLETED_DT BETWEEN p_start_date AND p_end_date 
                                    AND      sr.SURVEY_ACCEPTED_STATUS = 'ACCEPTED'  
                                    AND      s.TITLE = p_survey_type
                                    AND      sr.FACILITY_ID = sf.FACILITY_ID  
                                    AND      sf.STATION_NO = p_station_no  
                                    GROUP BY sf.STATION_NO;
                            END;
                        END IF;
                    END;
                END IF;
            END;
        END IF;
                
    END;


    PROCEDURE national_distribution_mv
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2
    )
    IS
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        
        d_query  varchar2(512);
        d_select varchar2(256) := ' SELECT   ''National'' location, v.vendor_facility, max( v.last_survey_date ) last_survey_date, nvl( sum( v.record_count ), 0 ) record_count ';
        d_from   varchar2(64)  := ' FROM     surveys_mv_so.surveys_vendor_mv v ';
        d_where  varchar2(128);
        d_group  varchar2(64)  := ' GROUP BY vendor_facility ORDER BY vendor_facility ';
    BEGIN

        dbms_output.put_line( 'PROCEDURE national_distribution_mv' );
        dbms_output.put_line( 'survey version: ' || nn_survey_version || ',  survey type: ' || nn_survey_type );

        IF ( nn_survey_type = 'All Surveys' )
        THEN
            BEGIN
                IF NOT ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        d_where := ' WHERE    VERSION = :nn_survey_version ';
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                d_where := ' WHERE    TITLE = :nn_survey_type ';
                IF NOT ( p_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        d_where := CONCAT( d_where, ' AND      VERSION = :nn_survey_version ' );
                    END;
                END IF;
            END;
        END IF;
        
        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( nn_survey_type = 'All Surveys' )
        THEN
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_version;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_type;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_type, nn_survey_version;
                    END;
                END IF;
            END;
        END IF;

    END;
    
    
    PROCEDURE national_distribution
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2
    )
    IS
        nn_survey_version varchar2(128) := check_survey_version( p_survey_version );
        nn_survey_type varchar2(128) := check_survey_type( p_survey_type );
        
        d_query  varchar2(1024);
-- cds 3.4.0.9        d_select varchar2(256) := ' SELECT   ''National'' location, v.vendor_facility, to_char(max(sr.COMPLETED_DT), ''dd/mm/yyyy'' ) last_survey_date, count(*) record_count ';
-- cds 3.4.0.11        d_select varchar2(256) := ' SELECT   ''National'' location, v.vendor_facility, to_char(max(sr.COMPLETED_DT), ''YYYY-MM-DD HH24:MM:SS'' ) last_survey_date, count(*) record_count ';
-- cds 3.5.1.2        d_select varchar2(256) := ' SELECT   ''National'' location, v.vendor_facility, to_char(max(sr.COMPLETED_DT), ''YYYY-MM-DD HH24:MM:SS TZHTZM'' ) last_survey_date, count(*) record_count ';
        d_select varchar2(256) := ' SELECT   ''National'' location, v.vendor_facility, to_char(max(sr.COMPLETED_DT), ''YYYY-MM-DD HH24:MI:SS TZHTZM'' ) last_survey_date, count(*) record_count ';
        d_from   varchar2(128) := ' FROM     survey_response sr, vendor v ';
        d_where  varchar2(512) := ' WHERE    sr.vendor_id = v.vendor_id ';
        d_group  varchar2(512) := ' GROUP BY v.vendor_facility ORDER BY v.vendor_facility ';
    BEGIN

        dbms_output.put_line( 'PROCEDURE national_distribution' );
        dbms_output.put_line( 'survey version: ' || nn_survey_version || ',  survey type: ' || nn_survey_type );

        IF ( nn_survey_type = 'All Surveys' )
        THEN
            BEGIN
                IF NOT ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        d_from  := CONCAT( d_from, ', survey s ' );
                        d_where := CONCAT( d_where, ' AND      sr.SURVEY_ID = s.SURVEY_ID AND      s.VERSION = :nn_survey_version ' );
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                d_from  := CONCAT( d_from, ', survey s ' );
                d_where := CONCAT( d_where, ' AND      sr.SURVEY_ID = s.SURVEY_ID AND      s.TITLE = :nn_survey_type ' );
                IF NOT ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        d_where := CONCAT( d_where, ' AND      s.VERSION = :nn_survey_version ' );
                    END;
                END IF;
            END;
        END IF;
        
        d_query  := buildDynamicQuery( d_select, d_from, d_where, d_group );
        dbms_output.put_line('QUERY: ' || d_query);
        
        IF ( nn_survey_type = 'All Surveys' )
        THEN
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_version;
                    END;
                END IF;
            END;
        ELSE
            BEGIN
                IF ( nn_survey_version = 'All Versions' )
                THEN
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_type;
                    END;
                ELSE
                    BEGIN
                        OPEN p_avg_cursor FOR d_query USING nn_survey_type, nn_survey_version;
                    END;
                END IF;
            END;
        END IF;

    END;
    
    
    PROCEDURE dmp_titles_proc
    (
        p_title_cursor     OUT SYS_REFCURSOR
    )
    IS
    BEGIN
        dbms_output.put_line( 'PROCEDURE dmp_titles_proc' );

        OPEN p_title_cursor FOR         
        SELECT distinct (s.title || s.version) key, s.title title, s.version
        FROM survey s
        WHERE s.type='DMP';

    END;


END ht_survey_report;
/
