CREATE OR REPLACE PACKAGE SURVEY.ht_survey_report AS
-- cds 3.4.0.7 add function count_surveys_national_dist, procedure count_national_dist_proc
-- cds 3.5.0.0 add count_dmps, count_dmps_national, count_dmps_visn, count_dmps_facility, count_dmps_proc
-- 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
    
    FUNCTION str2tbl
    ( 
        p_str in varchar2 
    ) 
    return myTableType;
          
    
    FUNCTION check_survey_version
    (
        p_survey_version    IN  varchar2
    )
    RETURN varchar2;
    
    
    FUNCTION check_survey_type
    (
        p_survey_type    IN  varchar2
    )
    RETURN varchar2;
    
    
    FUNCTION check_location
    (
        p_location    IN  varchar2
    )
    RETURN varchar2;
    
    
    FUNCTION check_status
    (
        p_status    IN  varchar2
    )
    RETURN varchar2;
    
    
    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;
    
    
    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;


    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;
    
    
    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;
   
    
    FUNCTION count_surveys_national_dist
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2
    )
    RETURN number;
    
    
    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;
   

    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;
    
    
    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;
    
    
    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;
    
      
    FUNCTION count_surveys_national_dist_mv
    (
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2,
        p_status         IN  varchar2
    )
    RETURN number;
           
           
    FUNCTION buildDynamicQuery
    (
        p_select IN  varchar2,
        p_from   IN  varchar2,
        p_where  IN  varchar2,
        p_group  IN  varchar2
    )
    RETURN varchar2;


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

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

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

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

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


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

    PROCEDURE vr12_data_mv
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_location   IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    );


    PROCEDURE vr12_data
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_location   IN  varchar2,
        p_start_date IN  date,
        p_end_date   IN  date
    );


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


    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
    );
    
    
    PROCEDURE vr12_data_national
    (
        p_avg_cursor OUT SYS_REFCURSOR,
        p_start_date IN  date,
        p_end_date   IN  date
    );


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


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


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


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


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


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


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


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


    PROCEDURE national_distribution_mv
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2
    );
    
    
    PROCEDURE national_distribution
    (
        p_avg_cursor     OUT SYS_REFCURSOR,
        p_survey_version IN  varchar2,
        p_survey_type    IN  varchar2
    );
    
    
    PROCEDURE dmp_titles_proc
    (
        p_title_cursor     OUT SYS_REFCURSOR
    );
    

END ht_survey_report;
/