DROP PROCEDURE PATS.DELETE_ALL_DATA;

CREATE OR REPLACE PROCEDURE PATS."DELETE_ALL_DATA"
 AS
   mycnt INT;
 BEGIN
   /* For protection against accidently deleting data from OIFODEV, count the ROCs and if greater
       than 30, quit. I must comment out this line in order to delete all the data from OIFODEV.*/
   SELECT COUNT(*) INTO mycnt FROM report_of_contact;
   IF mycnt>30 THEN
     RAISE_APPLICATION_ERROR(-20005,'You may be in OIFODEV. Check and comment lines in DELETE_ALL_DATA if necessary!!');
   END IF;
   /* Deleting ROC also deletes notification_master, notification_detail,
       roc_contacting_entity, roc_issue, roc_method_of_contact and roc_phone_fax*/
   DELETE FROM report_of_contact;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM report_of_contact;
   DBMS_OUTPUT.PUT_LINE('Count of ROC='||mycnt);
   DELETE FROM comps;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM comps;
   DBMS_OUTPUT.PUT_LINE('Count of Comps='||mycnt);
   DELETE FROM hospital_location;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM hospital_location;
   DBMS_OUTPUT.PUT_LINE('Count of HL='||mycnt);
   DELETE FROM congressional_contact;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM congressional_contact;
   DBMS_OUTPUT.PUT_LINE('Count of CCs='||mycnt);
   DELETE FROM pats_patient;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM pats_patient;
   DBMS_OUTPUT.PUT_LINE('Count of Patients'||mycnt);
   DELETE FROM pats_user;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM pats_user;
   DBMS_OUTPUT.PUT_LINE('Count of Users='||mycnt);
   DELETE FROM facility_service_or_section;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM facility_service_or_section;
   DBMS_OUTPUT.PUT_LINE('Count of FSOS='||mycnt);
   DELETE FROM boilerplate_resolution_text;
   COMMIT;
   SELECT COUNT(*) INTO mycnt FROM boilerplate_resolution_text;
   DBMS_OUTPUT.PUT_LINE('Count of Boilerplate Restext='||mycnt);
   --
   SELECT COUNT(*) INTO mycnt FROM national_pats_parameters;
   DBMS_OUTPUT.PUT_LINE('Count of Natl.PATS Params='||mycnt);
   SELECT COUNT(*) INTO mycnt FROM contacting_entity;
   DBMS_OUTPUT.PUT_LINE('Count of CE='||mycnt);
   SELECT COUNT(*) INTO mycnt FROM method_of_contact;
   DBMS_OUTPUT.PUT_LINE('Count of MOC='||mycnt);
   SELECT COUNT(*) INTO mycnt FROM treatment_status;
   DBMS_OUTPUT.PUT_LINE('Count of TS='||mycnt);
   SELECT COUNT(*) INTO mycnt FROM issue_category;
   DBMS_OUTPUT.PUT_LINE('Count of Issue Cat='||mycnt);
   SELECT COUNT(*) INTO mycnt FROM issue_code;
   DBMS_OUTPUT.PUT_LINE('Count of Issue Code='||mycnt);

END delete_all_data;
 
/
DROP PROCEDURE PATS.RETURN_STATION_LIST;

CREATE OR REPLACE PROCEDURE PATS."RETURN_STATION_LIST"
        (p_station IN VARCHAR2 -- Computing Station Number
       , p_cursor IN OUT pkg_load_legacy_data.t_cursor) -- Output)
AS
BEGIN
  OPEN p_cursor FOR
	SELECT stationnumber
     FROM sdsadm.std_institution
     WHERE stationnumber like p_station||'%'
     ORDER BY stationnumber;

END return_station_list;
 
/


GRANT EXECUTE ON PATS.RETURN_STATION_LIST TO PATSHOST_ROLE;
