DROP PACKAGE PATS.PKG_BOILERPLATE_TEXT;

CREATE OR REPLACE PACKAGE PATS."PKG_BOILERPLATE_TEXT"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Return boilerplate text for a single issue_code/institution_id combination
 --   Must provide either p_id parameter, or both p_inst_id and p_issue_code parameters.
 FUNCTION get_text (p_id IN NUMBER -- id of entry in boilerplate_resolution_text table
                 , p_inst_id IN NUMBER -- id of entry in sdsadm.std_institution table
                 , p_issue_code IN VARCHAR2) -- issue code from issue_code table
	RETURN t_cursor;

 -- Update the boilerplate text for an issue_code/institution_id combination
 PROCEDURE upd_text (p_id IN NUMBER -- id of entry in boilerplate_resolution_text table
                 , p_issue_code IN VARCHAR2 -- issue code from issue_code table
                 , p_text IN VARCHAR2 -- boilerplate text
                 , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update

 -- Add a new entry to the boilerplate_resolution_text table
 PROCEDURE add_text (p_inst_id IN NUMBER -- id of entry in sdsadm.std_institution table
                 , p_issue_code IN VARCHAR2 -- issue code from issue_code table
                 , p_text IN VARCHAR2 -- boilerplate text
				 , p_id OUT NUMBER -- Id of new entry in boilerplate text table
                 , p_ver OUT VARCHAR2); -- rowversion of new row

 -- Delete an entry from the boilerplate_resolution_text table
 PROCEDURE delete_text (p_id IN NUMBER); -- id of entry in boilerplate_resolution_text table

 -- List all boilerplate text entries for a given station (and optionally issue category) using value list handler
 PROCEDURE list(p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
                  , p_isscat IN VARCHAR2 -- (Optional) Return only entries within a given issue code.
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries


END pkg_boilerplate_text;
 
/


GRANT EXECUTE ON PATS.PKG_BOILERPLATE_TEXT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_BUILD_PATS_STATIC_DATA;

CREATE OR REPLACE PACKAGE PATS."PKG_BUILD_PATS_STATIC_DATA" AS
  mysysdate DATE := SYSDATE;

 -- Build National PATS Parameters
 PROCEDURE natpar;

 -- Build Contacting Entities
 PROCEDURE ce;

 -- Build Method of Contact
 PROCEDURE moc;

 -- Build Treatment_status
 PROCEDURE ts;

 -- Build Issue Category data
 PROCEDURE isscat;

 -- Build Issue Code data
 PROCEDURE isscode;

 -- Rebuild ALL test data
 PROCEDURE build_all;


END pkg_build_pats_static_data;
 
/
DROP PACKAGE PATS.PKG_COMP;

CREATE OR REPLACE PACKAGE PATS."PKG_COMP"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from COMP table

 PROCEDURE list_comp(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single COMP, based on p_id
 FUNCTION get_comp (p_id IN NUMBER) RETURN t_cursor;

 -- Update the NAME for a single COMP,
 -- or INACTIVATE/ACTIVATE a single COMP, based on p_id
 PROCEDURE upd_comp (p_id IN NUMBER -- id of entry to update/activate/inactivate
                 , p_name IN VARCHAR2 -- new office_or_person_name
                 , p_is_active IN BINARY_INTEGER -- 1=Activate entry, 0=Inactivate entry
                 , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update

 -- Add a new COMP
 PROCEDURE add_comp (p_name IN VARCHAR2 -- office_or_person_name
                 , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
                 , p_Id OUT NUMBER -- id number of new row
                 , p_ver OUT VARCHAR2); -- rowversion of new row

END pkg_comp;
 
/


GRANT EXECUTE ON PATS.PKG_COMP TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_CONGRESSIONAL_CONTACT;

CREATE OR REPLACE PACKAGE PATS."PKG_CONGRESSIONAL_CONTACT"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from CONGRESSIONAL_CONTACT table

 PROCEDURE list_cc(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single CONGRESSIONAL_CONTACT, based on p_id
 FUNCTION get_cc (p_id IN NUMBER) RETURN t_cursor;

 -- Update the OFFICE_OR_PERSON_NAME for a single CONGRESSIONAL_CONTACT,
 -- or INACTIVATE/ACTIVATE a single CONGRESSIONAL_CONTACT, based on p_id
 PROCEDURE upd_cc (p_id IN NUMBER -- id of entry to update/activate/inactivate
                 , p_name IN VARCHAR2 -- new office_or_person_name
                 , p_is_active IN BINARY_INTEGER -- 1=Activate entry, 0=Inactivate entry
                 , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update

 -- Add a new CONGRESSIONAL CONTACT
 PROCEDURE add_cc (p_name IN VARCHAR2 -- office_or_person_name
                 , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                 , p_Id OUT NUMBER -- id number of new row
                 , p_ver OUT VARCHAR2); -- rowversion of new row

 -- Get the next available id from the CONGRESSIONAL_CONTACT_SEQ sequence
 FUNCTION get_cc_nextid RETURN NUMBER;

END pkg_congressional_contact;
 
/


GRANT EXECUTE ON PATS.PKG_CONGRESSIONAL_CONTACT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_CONTACTING_ENTITY;

CREATE OR REPLACE PACKAGE PATS."PKG_CONTACTING_ENTITY" AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from CONTACTING_ENTITY table
 PROCEDURE list_ce(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                 , p_cursor OUT t_cursor);


 -- Return all data for a single CONTACTING_ENTITY, based on p_id
 FUNCTION get_ce (p_id IN NUMBER) RETURN t_cursor;

   /* Insert record to contacting_entity table */
  PROCEDURE add_ce ( p_ce_name IN VARCHAR2 -- Name of new contacting entity
  				   , p_rollup_code IN VARCHAR2 -- 2 character code used during rollup to Austin.
				   , p_sort_order IN OUT INT -- used to order items for display to user
				   , p_id OUT INT -- id of new entry
				   , p_ver OUT VARCHAR2); -- rowversion of new row.

  /* Update, Activate or Inactivate Contacting Entity */
  PROCEDURE upd_ce (p_id IN INT -- Id of entry to be updated
  				  , p_ce_name IN VARCHAR2 -- Name of contacting entity
  				  , p_rollup_code IN VARCHAR2 -- 2 character code used during rollup to Austin.
				  , p_is_active IN BINARY_INTEGER -- 1=Activate Entry, 0=Inactivate Entry
				  , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update);

  /* Update the sort_order field for an entire list of contacting_entity records*/
 -- PROCEDURE upd_sortorder_list(p_sortorder_list IN pats.sortorder_table); -- Nested table of sortorder_object entries (id. sort_order)

  /* Update the sort_order field for an entire list of contacting_entity records*/
  PROCEDURE upd_sortorder_list(p_sortorder_list IN VARCHAR2); -- string containing ^ delimited list of id,sort_order pairs.


END pkg_contacting_entity;
 
/


GRANT EXECUTE ON PATS.PKG_CONTACTING_ENTITY TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_DELETE_LEGACY_DATA;

CREATE OR REPLACE PACKAGE PATS."PKG_DELETE_LEGACY_DATA" AS

 -- Delete ALL test data for a single computing station and it's child divisions
 PROCEDURE delete_all (p_computing_inst_id IN NUMBER); -- Computing institution id from SDS.STD_INSTITUTION table

END pkg_delete_legacy_data;
 
/


GRANT EXECUTE ON PATS.PKG_DELETE_LEGACY_DATA TO PATSHOST_ROLE;
DROP PACKAGE PATS.PKG_EMPLOYEE;

CREATE OR REPLACE PACKAGE PATS."PKG_EMPLOYEE"  AS
 TYPE t_cursor IS REF CURSOR;


 -- Return List of employee_involved records that are at least a partial match to last and first name parameters
 PROCEDURE list_empinv_by_name(p_lastname IN VARCHAR2
                  , p_firstname IN VARCHAR2
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries


END pkg_employee;
 
/


GRANT EXECUTE ON PATS.PKG_EMPLOYEE TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_FACILITY_SERV_OR_SECT;

CREATE OR REPLACE PACKAGE PATS."PKG_FACILITY_SERV_OR_SECT"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from FACILITY_SERVICE_OR_SECTION table

 PROCEDURE list_fss(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_visn_id IN INT -- foreign key reference to id of VISN entry in sds.sdt_institition table
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single FACILITY_SERVICE_OR_SECTION, based on p_id
 FUNCTION get_fss (p_id IN INT) RETURN t_cursor;

 -- Update the LOCATION_NAME for a single FACILITY_SERVICE_OR_SECTION,
 -- or INACTIVATE/ACTIVATE a single FACILITY_SERVICE_OR_SECTION, based on p_id
 PROCEDURE upd_fss (p_id IN INT -- id of entry to update/activate/inactivate
                 , p_name IN VARCHAR2 -- new location_name
                 , p_is_active IN BINARY_INTEGER -- 1=Activate entry, 0=Inactivate entry
                 , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update

 -- Add a new FACILITY_SERVICE_OR_SECTION
 PROCEDURE add_fss (p_name IN VARCHAR2 -- location_name
                 , p_visn_id IN INT -- foreign key reference to id of VISN entry in sds.sdt_institition table
                 , p_id OUT INT -- id number of new row
                 , p_ver OUT VARCHAR2); -- rowversion of new row

END pkg_facility_serv_or_sect;
 
/


GRANT EXECUTE ON PATS.PKG_FACILITY_SERV_OR_SECT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_HOSPITAL_LOCATION;

CREATE OR REPLACE PACKAGE PATS."PKG_HOSPITAL_LOCATION"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from HOSPITAL_LOCATION table

 PROCEDURE list_hl(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single HOSPITAL_LOCATION, based on p_id
 FUNCTION get_hl (p_id IN NUMBER) RETURN t_cursor;

 -- Update the LOCATION_NAME for a single HOSPITAL_LOCATION,
 -- or INACTIVATE/ACTIVATE a single HOSPITAL_LOCATION, based on p_id
 PROCEDURE upd_hl (p_id IN NUMBER -- id of entry to update/activate/inactivate
                 , p_name IN VARCHAR2 -- new location_name
                 , p_is_active IN BINARY_INTEGER -- 1=Activate entry, 0=Inactivate entry
                 , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update

 -- Add a new HOSPITAL_LOCATION
 PROCEDURE add_hl (p_name IN VARCHAR2 -- location_name
                 , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                 , p_Id OUT NUMBER -- id number of new row
                 , p_ver OUT VARCHAR2); -- rowversion of new row

END pkg_hospital_location;
 
/


GRANT EXECUTE ON PATS.PKG_HOSPITAL_LOCATION TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_ISSUE_CATEGORY;

CREATE OR REPLACE PACKAGE PATS.PKG_ISSUE_CATEGORY IS
  TYPE t_cursor IS REF CURSOR;

  /* Insert record to issue_category table */
  PROCEDURE add_isscat ( p_isscat_code IN OUT VARCHAR2 -- issue category code
  				   , p_isscat_name IN VARCHAR2 -- issue category name
				   , p_sortorder IN OUT INT -- Used to sort entries for display
				   , p_is_custserv IN BINARY_INTEGER -- 1=yes, 0=no
				   , p_ver OUT VARCHAR2); -- rowversion of new row.

  /* Update, Activate or Inactivate issue category */
  PROCEDURE upd_isscat (p_isscat_code IN VARCHAR2 -- issue category code
  				  , p_isscat_name IN VARCHAR2 -- issue category name
				  , p_is_custserv IN BINARY_INTEGER -- 1=yes, 0=no
				  , p_is_active IN BINARY_INTEGER -- 1=Activate Entry, 0=Inactivate Entry
				  , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update);

 /* Bring back the new 'n' entries from the issue category table */
 PROCEDURE list_isscat(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_custserv_flag IN CHAR -- 'c'=customer service std., 'n'=non-customer service std., b=both
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single issue category, based on issue_category_code
 PROCEDURE get_isscat (p_isscat_code IN VARCHAR2 -- Issue Category code to be selected
                     , p_return_children IN BINARY_INTEGER -- 1=yes, return issue codes within this issue category, 0=no
                     , p_isscat_cursor OUT t_cursor -- Cursor with all issue category data for a single issue category
					 , p_isscode_cursor OUT t_cursor); -- Cursor with issue codes and names within the issue category


 -- Update the sort_order field for an entire list of issue_category records
 --PROCEDURE upd_sortorder_list(p_sortorder_list IN pats.isscat_sortorder_table); -- Nested table of issue_category_code,sort_order
 PROCEDURE upd_sortorder_list(p_sortorder_list IN VARCHAR2); -- "^" delimited list of issue_category_code,sort_order pairs


END PKG_ISSUE_CATEGORY;
 
/


GRANT EXECUTE ON PATS.PKG_ISSUE_CATEGORY TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_ISSUE_CODE;

CREATE OR REPLACE PACKAGE PATS.PKG_ISSUE_CODE AS
  TYPE t_cursor IS REF CURSOR;

  /* Insert record to issue_code table */
  PROCEDURE add_isscode ( p_isscat_code IN VARCHAR2 -- issue category code
  				   , p_name IN VARCHAR2 -- issue code name
				   , p_desc IN VARCHAR2 -- issue code description
				   , p_issue_code OUT VARCHAR2 -- New issue code
				   , p_ver OUT VARCHAR2); -- rowversion of new row.

  /* Update, Activate or Inactivate issue code */
  PROCEDURE upd_isscode (p_issue_code IN VARCHAR2 -- issue code
  				  , p_name IN VARCHAR2 -- issue code name
				  , p_desc IN VARCHAR2 -- issue code description
				  , p_is_active IN BINARY_INTEGER -- 1=Activate Entry, 0=Inactivate Entry
				  , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update);

 /* Bring back the new 'n' entries from the issue code table */
 PROCEDURE list_isscode(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                  , p_isscat_code IN VARCHAR2 -- 'If not null, return only issue codes within this issue category
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return all data for a single issue code, based on issue_code
 PROCEDURE get_isscode (p_issue_code IN VARCHAR2 -- Issue code to be selected
					 , p_isscode_cursor OUT t_cursor); -- Cursor with issue code data


END PKG_ISSUE_CODE;
 
/


GRANT EXECUTE ON PATS.PKG_ISSUE_CODE TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_LOAD_LEGACY_DATA;

CREATE OR REPLACE PACKAGE PATS."PKG_LOAD_LEGACY_DATA"
    AS
 TYPE t_cursor IS REF CURSOR;


 /* Load a list of new hospital_location entries from legacy data. Pass a list of ^ delimited strings containing a
      station_number, hospital_location_name, VistAIen, and a placeholder for an ID field. For each entry on the list,
     find or insert a new record to hospital Location table.*/
 PROCEDURE load_hl (p_hl_list IN pats.string_list -- List of hospital locations
                 , p_hl_id_list OUT pats.string_list -- Output list of PATS Hospital Location IEN^Id values
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were updated, to 0 if not.

 /* Load a list of new pats_user entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the user to the pats_user table. Return a list of
	vista_ien^id entries. */
 PROCEDURE load_user (p_user_list IN pats.string_list -- List of PATS users
                 , p_user_id_list OUT pats.string_list -- Output list of PATS User IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.

 /* Load a list of new pats_patient entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the patient to the pats_patient table. Return a list of
	vista_ien^id entries. */
 PROCEDURE load_patient (p_patient_list IN pats.string_list -- List of Patients used within Patient Rep
                 , p_patient_id_list OUT pats.string_list -- Output list of pats_patient IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.

 /* Load a list of new congressional contact entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the record to the congressional_contact table. Return a list of
	vista_ien^id entries. */
 PROCEDURE load_cc (p_cc_list IN pats.string_list -- List of Congressional Contacts used within Patient Rep
                 , p_cc_id_list OUT pats.string_list -- Output list of congressional_contact IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.

  /* Load a list of new employee_involved entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the record to the table. Return a list of vista_ien^id entries. */
 PROCEDURE load_empinv (p_empinv_list IN pats.string_list -- List of employee_involved used within Patient Rep
                 , p_empinv_id_list OUT pats.string_list -- Output list of employee_involved IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.


 /* Load a list of new facility service_or_section entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the record to the table. Return a list of vista_ien^id entries. */
 PROCEDURE load_fsos (p_fsos_list IN pats.string_list -- List of facility_service_or_section used within Patient Rep
                 , p_fsos_id_list OUT pats.string_list -- Output list of facility_service_or_section IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.

 /* Load a list of ROCs (report_of_contact) entries from legacy data. Pass a list of ^ delimited strings.
    For each entry in the list, find or add the record to the table. Return a list of roc_number^id entries. */
 PROCEDURE load_roc (p_roc_list IN pats.string_list -- List of report_of_contact from Patient Rep
                 , p_roc_id_list OUT pats.string_list -- Output list of ROC number^Id values.
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were found/updated, to 0 if not.


 /* Load a list of issue_codes from legacy data. Pass a list of ^ delimited strings containing the code, name
      issue Category and definition. For each entry on the list, find or insert a new record to issue_code table.
	  This procedure is run just once from one site, since there is one list of national issue codes shared by all
	  of the sites.  */
 PROCEDURE load_iss (p_iss_list IN pats.string_list -- List of issue code data
                 , p_all_done OUT BINARY_INTEGER); -- Set to 1 if all entries were updated, to 0 if not.


 -- Return a list of counts of a single sites data.
 PROCEDURE counts(p_inst_id IN NUMBER  -- Id of Parent Station Number from SDSADM.STD_INSTITUTION materialized view
                , p_count_list OUT pats.string_list); -- Output counts array to display online



END pkg_load_legacy_data;
 
/


GRANT EXECUTE ON PATS.PKG_LOAD_LEGACY_DATA TO PATSHOST_ROLE;
DROP PACKAGE PATS.PKG_METHOD_OF_CONTACT;

CREATE OR REPLACE PACKAGE PATS."PKG_METHOD_OF_CONTACT"  AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from METHOD_OF_CONTACT table
 PROCEDURE list_moc(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                 , p_cursor OUT t_cursor);


 -- Return all data for a single METHOD_OF_CONTACT, based on p_id
 FUNCTION get_moc (p_id IN NUMBER) RETURN t_cursor;

  /* Insert record to METHOD_OF_CONTACT table */
  PROCEDURE add_moc ( p_moc_name IN VARCHAR2 -- Name of new method of contact
  				   , p_rollup_code IN CHAR -- 1 character code used to identify MOC during rollup to Austin.
				   , p_sort_order IN OUT INT -- used to order items for display to user
				   , p_id OUT INT -- id of new entry
				   , p_ver OUT VARCHAR2); -- rowversion of new row.

  /* Update, Activate or Inactivate Method of Contact */
  PROCEDURE upd_moc (p_id IN INT -- Id of entry to be updated
  				  , p_moc_name IN VARCHAR2 -- Name of method of contact
  				  , p_rollup_code IN CHAR -- 1 character code used to identify MOC during rollup to Austin.
				  , p_is_active IN BINARY_INTEGER -- 1=Activate Entry, 0=Inactivate Entry
				  , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update);

  /* Update the sort_order field for an entire list of method_of_contact records*/
  PROCEDURE upd_sortorder_list(p_sortorder_list IN VARCHAR2); -- string containing ^ delimited list of id,sort_order pairs.


END pkg_method_of_contact;
 
/


GRANT EXECUTE ON PATS.PKG_METHOD_OF_CONTACT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_NATL_PATS_PARAMETERS;

CREATE OR REPLACE PACKAGE PATS."PKG_NATL_PATS_PARAMETERS"
    AS

 -- Update the maximum number of days allowed to process a ROC.
 PROCEDURE upd_param (p_days IN INT -- Maximum number of days allowed to process a ROC
                    , p_date OUT DATE); -- Date record was updated

 -- Return Number of days before ROCs become overdue from National Parameters.
 FUNCTION  get_days_to_process_roc  RETURN INT;


END pkg_natl_pats_parameters;
 
/


GRANT EXECUTE ON PATS.PKG_NATL_PATS_PARAMETERS TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_NOTIFICATION;

CREATE OR REPLACE PACKAGE PATS."PKG_NOTIFICATION"
    AS
 TYPE t_cursor IS REF CURSOR;


 -- Add detail information for a notification
 PROCEDURE add_detail (p_notification_master_id IN INT -- Id value for notification_master record
                 , p_from_user_id IN INT -- Id value for user who sent the response (pointer to pats_user)
                 , p_to_user_id IN INT -- Id value for user to whom response was sent (pointer to pats_user)
				 , p_message IN VARCHAR2 -- Dialogue back and forth between PA and Employee
				 , p_det_id OUT INT); -- Id of new entry in notification_detail record


 -- Add a new NOTIFICATION
 PROCEDURE add_notif (p_rocno IN VARCHAR2 -- ROC Number
                 , p_pa_id IN INT -- Id of Patient Advocate sending the Notification (from pats_user table)
				 , p_empnot_id IN INT -- Id of Employee who is receiving the notification (from pats_user table)
				 , p_querystring VARCHAR2 -- 50 character randomly generated string used to uniquely identify a message
				 , p_message VARCHAR2 -- The message text
				 , p_expdate IN DATE -- Expiration Date
				 , p_notification_type IN VARCHAR2 -- Type of notification ("IN" or "ARN")
                 , p_id OUT NUMBER); -- id number of new notification

 -- Return a list of ARNs/INs for a given ROC
 PROCEDURE list_notif (p_rocno IN VARCHAR2 -- ROC Number
 				, p_status IN CHAR -- P=Pending only, C=Closed only, A or Null=ALL
 		   		, p_pa_id IN INT -- Id from pats_user table, for PA who initiated the notification.
				, p_type IN VARCHAR2 -- Set to IN or ARN
				, p_cursor OUT t_cursor); -- Cursor used to fill result set with row data.


 -- Return all data for a single ARN
 PROCEDURE get_arn (p_id IN OUT INT -- Optional lookup by the Id of the Notification
                , p_querystring IN OUT VARCHAR2 -- Optional lookup by querystring
				, p_rocno OUT VARCHAR2 -- ROC Number
				, p_date_initiated OUT DATE -- Date ARN was initiated
				, p_expiration_date OUT DATE -- Date ARN is due to expire
				, p_status OUT CHAR -- P=Pending, E=Expired, C=Complete
				, p_pa_id OUT INT -- Id from pats_user table for PA who initiated the notification
				, p_pa_name OUT VARCHAR2 -- Displayable name of PA who initiated the notification
				, p_emp_id OUT INT -- Id from pats_user table of Employee who was the recipient of the notification
				, p_emp_name OUT VARCHAR2 -- Displayable name of Employee who received the notification
				, p_cursor OUT t_cursor); -- Cursor used to fill result set with List of recipient/message details

 -- Return all data for a single Information Notification (IN)
 PROCEDURE get_in (p_id IN OUT INT -- Optional lookup by the Id of the Notification
                , p_querystring IN OUT VARCHAR2 -- Optional lookup by querystring
				, p_rocno OUT VARCHAR2 -- ROC Number
				, p_date_initiated OUT DATE -- Date ARN was initiated
				, p_pa_id OUT INT -- Id from pats_user table for PA who initiated the notification
				, p_pa_name OUT VARCHAR2 -- Displayable name of PA who initiated the notification
				, p_emp_id OUT INT -- Id from pats_user table of Employee who was the recipient of the notification
				, p_emp_name OUT VARCHAR2 -- Displayable name of Employee who received the notification
				, p_date_read OUT TIMESTAMP WITH TIME ZONE); -- Date first (should be the only) detail record was read.



 -- Update Expiration Date or status on an ARN
 PROCEDURE upd_arn (p_id IN INT -- Id of notification_master record
                , p_status IN CHAR -- P=Pending, C=Complete
                , p_date IN DATE); -- New expiration date.


 -- Move Comments for a single ARN to the Resolution Text on the ROC.
 PROCEDURE move_com (p_id IN INT); -- Id of notification master record


 -- Return a list of expired ARNs generated by a given user and division
 PROCEDURE list_exp_arn (p_pa_id IN INT -- Id from pats_user for PA who initiated the ARN
                , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
				, p_cursor OUT t_cursor); -- Cursor used to fill result set with row data.


 -- Update the date_read on all unread messages for a given user
 PROCEDURE upd_date_read (p_notif_id IN INT -- Id for Notification Master record
                , p_user_id IN INT); -- Id for message recipient (to_user_fk)


END pkg_notification;
 
/


GRANT EXECUTE ON PATS.PKG_NOTIFICATION TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_PATS_PATIENT;

CREATE OR REPLACE PACKAGE PATS."PKG_PATS_PATIENT"   AS
 TYPE t_cursor IS REF CURSOR;

 -- Insert or Update a Pats Patient
 PROCEDURE set_patient (p_ICN IN VARCHAR2
     , p_last_name IN VARCHAR2, p_first_name IN VARCHAR2
     , p_middle_name IN VARCHAR2, p_prefix IN VARCHAR2
     , p_suffix IN VARCHAR2, p_degree IN VARCHAR2
     , p_gender IN CHAR, p_dob IN DATE
     , p_ssn IN VARCHAR2 -- Social Security Number, 9 numeric digits
	 , p_is_pseudo_ssn IN BINARY_INTEGER -- 0=no, 1=yes
     , p_eligibility IN VARCHAR2
	 , p_category IN VARCHAR2 -- Category (Current Means Test Status)
	 , p_enroll_priority IN VARCHAR2
     , p_period_of_service IN VARCHAR2
     , p_is_svc_connected IN BINARY_INTEGER -- 1=yes, 0=no
     , p_svc_connect_pct IN INT -- Number between 0 and 100
	 , p_eth_id IN NUMBER -- ID from sdsadm.std_ethnicity table
	 , p_racelist IN VARCHAR2 -- Comma delimited list of ids from sdsadm.std_race table
     , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
     , p_vistaien IN NUMBER
     , p_id OUT INT);

 -- Return a single Pats Patient record based on their row id.
 FUNCTION get_patient(p_id IN NUMBER) RETURN t_cursor;

 -- Return List of Patients that are at least a partial match to last and first name parameters
 PROCEDURE list_patient_by_name(p_lastname IN VARCHAR2
                  , p_firstname IN VARCHAR2
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                  , p_rowcount IN INT -- number of rows to return from this call.
                  , p_initial_index IN INT -- number representing which block of p_rowcount entries to return
                  , p_cursor OUT t_cursor -- cursor used to fill result-set with row data
                  , p_total_rowcount OUT INT -- total number of rows query would return
                  , p_has_next_resultset OUT BINARY_INTEGER -- set to 1 if there are more rows to return
                  , p_number_of_indexes OUT INT); -- total number of blocks of p_rowcount entries

 -- Return List of Patients that match NSSN or an SSN
 PROCEDURE list_patient_by_ssn_nssn(p_nssn IN VARCHAR2
 		   		  , p_ssn IN VARCHAR2
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
                  , p_cursor OUT t_cursor); -- cursor used to fill result-set with row data


END pkg_pats_patient;
 
/


GRANT EXECUTE ON PATS.PKG_PATS_PATIENT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_PATS_USER;

CREATE OR REPLACE PACKAGE PATS."PKG_PATS_USER" AS
 TYPE t_cursor IS REF CURSOR;

 /* Note that in all these sprocs, p_user_id is the KAAJEE User_identifier, NOT the Id field.
    The pats_user table contains all users of the PATS system, employees involved in a ROC,
	and employee sent a notification. */

 -- Insert or Update a Pats User, Employee Involved, or Employee Notified
 PROCEDURE set_person (p_userid IN VARCHAR2
     , p_last_name IN VARCHAR2, p_first_name IN VARCHAR2
     , p_middle_name IN VARCHAR2, p_prefix IN VARCHAR2
     , p_suffix IN VARCHAR2, p_degree IN VARCHAR2
	 , p_title IN OUT VARCHAR2 -- (optional) Title for employees involved
	 , p_mail_code IN OUT VARCHAR2 -- (optional) Mail Code for employees involved
	 , p_email_address IN OUT VARCHAR2 -- (optional) for employees sent a notification
     , p_display_exp_arns IN OUT INT -- display expired arns? is set to 0 (false) or 1 (true)
     , p_phone IN OUT VARCHAR2 -- patient advocates phone number
     , p_is_508_accessible IN OUT INT -- Is user 508c? set to 0 (false) or 1 (true)
     , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution parent station
	 , p_nullability_flag IN NUMBER -- Set to 1 if null parameters should set fields to null on update.
	 , p_stationno_list IN VARCHAR2 -- Comma delimited list of station numbers to which user has access (from KAAJEE)
     , p_id OUT INT);


 -- Get a single user specified by either user_identifier field, or by id field.
 FUNCTION get_person (p_user_id IN VARCHAR2 -- User_Identifier field value from pats_user table
                  , p_id IN INT)-- Id field from pats_user table
     RETURN t_cursor;

END pkg_pats_user;
 
/


GRANT EXECUTE ON PATS.PKG_PATS_USER TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_REPAIR_ROLLUP_NATL_DATA;

CREATE OR REPLACE PACKAGE PATS."PKG_REPAIR_ROLLUP_NATL_DATA"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Build output data for rollup to National Reporting into pats_rollup_natl_data table
 -- This procedure is used to rebuild all data from the time the rollup failed.
 PROCEDURE rollup(p_fromdate IN DATE); -- Date last rollup successfully completed

END pkg_repair_rollup_natl_data;
 
/
DROP PACKAGE PATS.PKG_REPORT_OF_CONTACT;

CREATE OR REPLACE PACKAGE PATS."PKG_REPORT_OF_CONTACT" AS
  TYPE t_cursor IS REF CURSOR;


 -- Add a new REPORT OF CONTACT
 PROCEDURE add_roc(p_date IN DATE -- Date of Contact. If null, use system date of client
                  , p_infobyuserid IN INT -- Id from pats_user table
                  , p_enteredbyuserid IN INT -- Id from pats_user table
				  , p_ce_list IN VARCHAR2 -- "," delimited list of contacting entity id values
                  --, p_ce_list IN pats.id_list -- VARRAY with Ids from contacting_entity table
                  , p_issuetext IN VARCHAR2
                  , p_treatmentstatusid IN INT -- Id from treatment_status table
                  , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
                  , p_station_no IN VARCHAR2 -- Station Number needed to build next ROC number
				  , p_patientid IN INT -- Id from Pats_Patient table
                  , p_roc_number OUT VARCHAR2 -- Generated ROC number
                  , p_ver OUT VARCHAR2); -- rowversion of new row


 -- Update an existing REPORT OF CONTACT
 PROCEDURE upd_roc(p_roc_number IN VARCHAR2 -- ROC Number for entry to be edited
                  , p_date IN DATE -- Date of Contact. If null, use system date of client
                  , p_infobyuserid IN INT -- Id from pats_user table
                  , p_issuetext IN VARCHAR2
                  , p_treatmentstatusid IN INT -- Id from treatment_status table
                  , p_patientid IN INT -- Id from Pats_Patient table
                  , p_ccfk IN INT -- Id from congressional_contact table (can be null)
				  , p_ce_list IN VARCHAR2 -- "," delimited list of contacting entity id values
				  , p_moc_list IN VARCHAR2 -- "," delimited list of method of contact id values
				  , p_phone_fax_list IN VARCHAR2 -- "^" delimited list of desc^phone/fax number pairs
				  , p_compid IN INT -- Id from Comps table
				  , p_is_internal_appeal IN INT -- 1=true, 0=false
				  , p_resolution_text1 IN VARCHAR2 -- First 4000 characters of resolution text
				  , p_resolution_text2 IN VARCHAR2 -- Second 4000 characters of resolution text
				  , p_issue_list IN VARCHAR2 -- "," delimited list of issue code^hosp.loc.id^facility servsect id^employee involved id multiples
                  , p_ver IN OUT VARCHAR2 -- rowversion of new row
				  , p_infobyname OUT VARCHAR2); -- displayable info by user name (last, first middle suffix degree)


  /* Return a single ROC with ALL data, based on the ROC Number passed as an input parameter. */
  PROCEDURE get_roc (p_roc_number IN VARCHAR2  -- Roc Number
  				  , p_roc_cursor OUT t_cursor  -- Cursor referencing ROC Main data
				  , p_ce_cursor OUT t_cursor  -- Cursor referencing Contacting Entities associated with ROC
				  , p_moc_cursor OUT t_cursor  -- Cursor referencing Methods of Contact associated with ROC
				  , p_issues_cursor OUT t_cursor  -- Cursor referencing Issue code/hospital location/Facility Service or Section/Employees Involved
				  , p_pf_cursor OUT t_cursor); -- Cursor referencing phone/fax data associated with ROC


  /* Return a list of Open/Closed/Both ROCs for a single institution.
          If p_uid is not null, return all ROCs entered by the PA referenced by that user row id (info taken by)
		  If p_partial_rocno is entered, return all ROCs that match the partial ROC number.
		  If p_empid is entered, return all ROCs for which that person is an employee involved.
		  If p_startdate and p_enddate are entered, return ROCs with date of contact within date range.

		  NOTE: p_ocb and p_station_no should ALWAYS be passed.
		  p_uid can be used alone or in combination with any of the other parameters to follow.
		  Then, either p_partial_rocno, or p_empid, or both p_startdate and p_enddate should be passed */
  PROCEDURE list_roc_vlh (p_ocb IN CHAR  -- O=Open, C=Closed, B=Both
                    , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution(should ALWAYS be passed)
 		   			, p_user_identifier IN VARCHAR2  -- If not null, return only ROCs entered by this user. (KAAJEE User Name)
					, p_partial_rocno IN VARCHAR2 -- If not null, contains a partial ROC number to use for matching results.
					, p_empid IN INT -- If not null, return only ROCs for this employee_involved. (id from PATS_USER table)
					, p_startdate IN DATE -- If not null, return only ROCs whose date of contact is on or after this date
					, p_enddate IN DATE -- If not null, return only ROCs whose date of contact is on or before this date
                    , p_rowcount IN INT -- Maximum number of rows to return in this call
                    , p_initial_index IN INT  -- Which p_rowcount block of entries to return
                    , p_cursor OUT t_cursor  -- Cursor used to fill result_set with row data
                    , p_total_rowcount OUT INT  -- Total number of rows that meet the criteria
                    , p_has_next_resultset OUT BINARY_INTEGER  -- Set to 1 if there are more rows to return
                    , p_number_of_indexes OUT INT);  -- Total number of p_rowcount blocks in the table

 /* Mark a ROC Closed, or Re-Open a ROC. Closing a ROC sets date_closed and status to "C",
             re-Opening sets date_closed to NULL*/
 PROCEDURE open_close_roc (p_rocno IN VARCHAR2 -- ROC Number
 		   			, p_flag IN CHAR -- O=Open, C=Close
					, p_date_closed IN OUT DATE -- Optional date_closed. If not passed, ROC uses system date.
					, p_ver IN OUT VARCHAR2); -- Row Version Number

 /* Delete a ROC */
 PROCEDURE delete_roc (p_rocno IN VARCHAR2); -- ROC Number


 /* Return a list of Open/Closed/Both ROCs for a single institution that match the other criteria
           as seen beside the input parameters.
		   Used to return list of open ROCs, or to look up ROCs by Patient */
 PROCEDURE list_roc_no_vlh (p_ocb IN CHAR -- O=Open, C=Closed, B=Both
                     , p_inst_id IN NUMBER -- User's log-on station - foreign key reference to sds.std_institution
 		   			 , p_patient_ien IN INT -- If not null, return only ROCs with matching station number/VistA IEN combination.
					 , p_comp_facility_id IN NUMBER -- Computing Facility ID - foreign key reference to sds.std_institution
					 , p_overdue_flag IN INT -- 1=Yes, Overdue ROCs only, 0=No, don't check date_overdue
					 , p_userid IN INT -- If not null, return list of ROCs where this user is the info_taken_by_user_fk.
					 , p_cursor OUT t_cursor); -- Cursor used to fill result set with row data.

 /* Return a count of the number of ROCs overdue based on date passed, or current date*/
 FUNCTION  count_overdue (p_date IN DATE -- If not null, count is based on this date rather than current date.
                     , p_inst_id IN NUMBER) -- foreign key reference to sds.std_institution
                     RETURN INT;


END pkg_report_of_contact;
 
/


GRANT EXECUTE ON PATS.PKG_REPORT_OF_CONTACT TO PATSHOST_ROLE;

GRANT EXECUTE ON PATS.PKG_REPORT_OF_CONTACT TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_ROLLUP_NATL_DATA;

CREATE OR REPLACE PACKAGE PATS."PKG_ROLLUP_NATL_DATA"
    AS
 TYPE t_cursor IS REF CURSOR;

 -- Build output data for rollup to National Reporting into pats_rollup_natl_data table
 PROCEDURE rollup;

 -- Delete data from rollup file pats_rollup_natl_data
 PROCEDURE del;

END PKG_ROLLUP_NATL_DATA;
 
/
DROP PACKAGE PATS.PKG_TREATMENT_STATUS;

CREATE OR REPLACE PACKAGE PATS."PKG_TREATMENT_STATUS" AS
 TYPE t_cursor IS REF CURSOR;

 -- Return a list of all active/inactive/both entries, all fields from TREATMENT STATUS table
 PROCEDURE list_ts(p_aib IN CHAR -- 'a'=active only, 'i'=inactive only, 'b'=both active and inactive
                 , p_cursor OUT t_cursor);


 -- Return all data for a single TREATMENT STATUS, based on p_id
 FUNCTION get_ts (p_id IN NUMBER) RETURN t_cursor;

 /* Insert record to TREATMENT_STATUS table */
 PROCEDURE add_ts ( p_ts_name IN VARCHAR2 -- Name of new treatment status
  				   , p_rollup_code IN CHAR -- 1 character code used to identify MOC during rollup to Austin.
  				   , p_sort_order IN OUT INT -- used to order items for display to user
				   , p_id OUT INT -- id of new entry
				   , p_ver OUT VARCHAR2); -- rowversion of new row.

 /* Update, Activate or Inactivate Treatment Status */
 PROCEDURE upd_ts (p_id IN INT -- Id of entry to be updated
  				  , p_ts_name IN VARCHAR2 -- Name of treatment status
  				  , p_rollup_code IN CHAR -- 1 character code used to identify MOC during rollup to Austin.
				  , p_is_active IN BINARY_INTEGER -- 1=Activate Entry, 0=Inactivate Entry
				  , p_ver IN OUT VARCHAR2); -- rowversion before (in) and after (out) update);

  /* Update the sort_order field for an entire list of treatment_status records*/
  PROCEDURE upd_sortorder_list(p_sortorder_list IN VARCHAR2); -- string containing ^ delimited list of id,sort_order pairs.


END pkg_treatment_status;
 
/


GRANT EXECUTE ON PATS.PKG_TREATMENT_STATUS TO PATSUSER_ROLE;
DROP PACKAGE PATS.PKG_PAD_LOCATION;

CREATE OR REPLACE PACKAGE PATS."PKG_PAD_LOCATION"
  AS
-- Find the station number for the IRIS location
PROCEDURE find_station (p_location IN VARCHAR2 -- IRIS Location
  , p_station_number OUT VARCHAR2); -- Station number
 END pkg_pad_location;
/


GRANT EXECUTE ON PATS.PKG_PAD_LOCATION TO PATSUSER;

GRANT EXECUTE ON PATS.PKG_PAD_LOCATION TO PATSUSER_ROLE;
