DROP PACKAGE BODY PATS.PKG_BOILERPLATE_TEXT;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_BOILERPLATE_TEXT" AS

 -- 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
 IS
    v_cursor t_cursor;
 BEGIN
   	OPEN v_cursor FOR
 	SELECT id, institution_fk, issue_code_fk
	     , text, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	  FROM boilerplate_resolution_text
		WHERE ((p_id IS NOT NULL) AND (id = p_id))
		   OR ((institution_fk = p_inst_id) AND (issue_code_fk = p_issue_code));
	RETURN v_cursor;
 END get_text;


 -- 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
 IS
   v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
   v_after_verdate DATE;
 BEGIN
  -- Update text field.
   UPDATE boilerplate_resolution_text
	  SET text = p_text, issue_code_fk = p_issue_code, ver = SYSDATE
		WHERE (id = p_id) AND (ver = v_before_verdate)
		RETURNING ver INTO v_after_verdate;
   -- If update failed, raise optimistic concurrency error.
   IF v_after_verdate IS NULL THEN
	  RAISE_APPLICATION_ERROR(-20000,'This boilerplate text was updated by another user. Please make your changes again.');
	  ROLLBACK;
   -- If update was successful, set new ROWVERSION, COMMIT data.
   ELSE
      p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
   END IF;
 EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
   RAISE;
 END upd_text;


 -- 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
 IS
 BEGIN
   INSERT INTO boilerplate_resolution_text (institution_fk, issue_code_fk, text, ver)
     VALUES (p_inst_id, p_issue_code, p_text, SYSDATE)
     RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
   COMMIT;
 END add_text;


 -- Delete an entry from the boilerplate_resolution_text table
 PROCEDURE delete_text (p_id IN NUMBER) -- id of entry in boilerplate_resolution_text table
  IS
 BEGIN
   DELETE FROM boilerplate_resolution_text
     WHERE id = p_id;
 END delete_text;


  -- Either list all boilerplate text entries using VLH, or list entries for a single issue category
 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
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR2(5) := ' ';
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of entries that meet the select criteria
   SELECT COUNT(*) INTO p_total_rowcount
   	 FROM boilerplate_resolution_text btxt
	   JOIN issue_code iss
	   ON btxt.issue_code_fk = iss.issue_code
   	 WHERE (btxt.institution_fk = p_inst_id)
	   AND (iss.inactivation_date IS NULL)
	   AND ((p_isscat IS NULL) OR (iss.issue_category_fk=p_isscat));

   -- Set v_startval to the starting sort order
   IF v_start > 0 THEN
      SELECT MAX(sort_code) INTO v_startval
      FROM (SELECT iss.sort_code FROM boilerplate_resolution_text btxt
	          JOIN issue_code iss
		      ON btxt.issue_code_fk = iss.issue_code
   	        WHERE (btxt.institution_fk = p_inst_id)
			  AND (iss.inactivation_date IS NULL)
			  AND ((p_isscat IS NULL) OR (iss.issue_category_fk=p_isscat))
            ORDER BY iss.sort_code)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, issue_code, text, ROWNUM
   	 FROM (SELECT btxt.id, issue_code, SUBSTR(btxt.text,1,80) text, iss.sort_code
	 	   FROM boilerplate_resolution_text btxt
	          JOIN issue_code iss
		      ON btxt.issue_code_fk = iss.issue_code
   	        WHERE (btxt.institution_fk = p_inst_id)
			  AND (iss.inactivation_date IS NULL)
			  AND ((p_isscat IS NULL) OR (iss.issue_category_fk=p_isscat))
            ORDER BY iss.sort_code)
     WHERE (sort_code > v_startval) AND (ROWNUM <= p_rowcount);

   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list;


END pkg_boilerplate_text;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_BUILD_PATS_STATIC_DATA" AS

--**** PRIVATE ****
 -- Execute code to reset sequences
 PROCEDURE reset_seq(p_seqname VARCHAR2, p_startwith INT)
  AS
  BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE ' || p_seqname;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE '|| p_seqname ||
       ' START WITH '|| p_startwith ||
       ' MAXVALUE 1E+28  MINVALUE 1  NOCYCLE  NOCACHE  NOORDER';
  END reset_seq;
--****

-- Set up National Pats Parameter record
PROCEDURE natpar
AS
 v_mydate DATE;
BEGIN
 pats.pkg_natl_pats_parameters.upd_param(7,v_mydate);
END natpar;

  -- Build Contacting Entities
 PROCEDURE ce
 AS
    mysysdate DATE := SYSDATE;
 BEGIN
	DELETE FROM contacting_entity;
	COMMIT;
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (1, 'Patient', 1, 'PA', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (2, 'Relative', 2, 'RE',mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (3, 'Friend', 3, 'FR', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (4, 'Congressional', 4, 'CO', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver, inactivation_date)
      VALUES (5, 'VISN/HQ', 12, 'VH', mysysdate, mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (6, 'Veterans Service Organization', 7, 'VO', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (7, 'Attorney/Legal Guardian/Conservator/Trustee', 8, 'AT', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (8, 'Director''s Office', 9, 'DI', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (9, 'Staff - VAMC', 10, 'ST', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (10, 'Other', 11, 'OT', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (11, 'VISN', 5, 'VI', mysysdate);
    INSERT INTO contacting_entity (id, contacting_entity_name, sort_order, rollup_code, ver)
      VALUES (12, 'Central Office', 6, 'HQ', mysysdate);
	COMMIT;
	reset_seq('pats.contacting_entity_seq',13);
 END ce;

 -- Method of Contact
 PROCEDURE moc
 AS
     mysysdate DATE := SYSDATE;
 BEGIN
 	DELETE FROM method_of_contact;
	COMMIT;
    INSERT INTO method_of_contact (id, method_of_contact_name, sort_order, rollup_code, ver)
      VALUES (1, 'Phone', 1, 'P', mysysdate);
    INSERT INTO method_of_contact (id, method_of_contact_name, sort_order, rollup_code, ver)
      VALUES (2, 'Visit', 2, 'V', mysysdate);
    INSERT INTO method_of_contact (id, method_of_contact_name, sort_order, rollup_code, ver)
      VALUES (3, 'Internet', 3, 'I', mysysdate);
    INSERT INTO method_of_contact (id, method_of_contact_name, sort_order, rollup_code, ver)
      VALUES (4, 'Letter', 4, 'L', mysysdate);
    INSERT INTO method_of_contact (id, method_of_contact_name, sort_order, rollup_code, ver)
      VALUES (5, 'Survey', 5, 'S', mysysdate);
	COMMIT;
	reset_seq('pats.method_of_contact_seq',6);
 END moc;


 -- Treatment Status
 PROCEDURE ts
 AS
   mysysdate DATE := SYSDATE;
 BEGIN
 	DELETE FROM treatment_status;
	COMMIT;
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver)
      VALUES (1, 'Outpatient', 1, 'O', mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver)
      VALUES (2, 'Inpatient', 2, 'I', mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver)
      VALUES (3, 'Long Term Care', 3, 'E', mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver)
      VALUES (4, 'Not Registered at Site', 4, NULL, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver)
      VALUES (5, 'No Patient Associated', 5, NULL, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (6, 'Pat Rep - Inpatient', 6, 'I', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (7, 'Pat Rep - Outpatient', 7, 'O', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (8, 'Pat Rep - Domiciliary', 8, 'E', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (9, 'Pat Rep - NHCU', 9, 'E', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (10, 'Pat Rep - Long Term Psych', 10, 'E', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (11, 'Pat Rep - Extend/Intermed.Care', 11, 'E', mysysdate, mysysdate);
    INSERT INTO treatment_status (id, treatment_status, sort_order, rollup_code, ver, inactivation_date)
      VALUES (12, 'Pat Rep - HBHC', 12, 'E', mysysdate, mysysdate);
	COMMIT;
	reset_seq('pats.treatment_status_seq',13);
 END ts;

 -- Issue Category
 PROCEDURE isscat
 AS
   mysysdate DATE := SYSDATE;
 BEGIN
    DELETE FROM issue_code;
 	DELETE FROM issue_category;
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('SC', 'Staff Courtesy', 1, 1, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('AC', 'Access/Timeliness', 1, 2, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('OP', 'One Provider', 1, 3, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('PR', 'Decisions/Preferences', 1, 4, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('EM', 'Emotional Needs', 1, 5, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('CO', 'Coordination of Care', 1, 6, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('ED', 'Patient Education', 1, 7, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
     VALUES ('FI', 'Family Involvement', 1, 8, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
       VALUES ('PC', 'Physical Comfort', 1, 9, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('TR', 'Transitions', 1, 10, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('RI', 'Risk Management Complaints', 0, 11, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('RE', 'Medical Records Issues', 0, 12, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('LL', 'Eligibility Issues', 0, 13, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('EV', 'Environmental Issues', 0, 14, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('RG', 'Regulation Issues', 0, 15, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('IF', 'Requests for Information', 0, 16, mysysdate);
    INSERT INTO issue_category (issue_category_code, issue_category_name, is_customer_service_standard, sort_order, ver)
      VALUES ('CP', 'Compliments', 0, 17, mysysdate);
	COMMIT;
 END isscat;

 PROCEDURE isscode
 AS
   mysysdate DATE := SYSDATE;
 BEGIN
  DELETE FROM issue_code;
  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('SC01','Patient not Treated w/Dignity and Respect/Perceived Rudeness','SC','a.  Patients perceived they were not treated with dignity by the staff. b.  Behaviors occurred during the patient visit or hospitalization that were perceived as disrespectful, rude, or inconsiderate of the patient or family. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('SC02','Perceived Retaliation for Expressing Concerns','SC','Patients perceived that since they made a complaint the staff is not as responsive or helpful. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC01','Excessive Wait at Facility for a Scheduled Appointment','AC','Patient perceives his wait is too long for scheduled appointment. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC02','Excessive Wait at Facility for an Unscheduled Appointment','AC','Patient perceives wait is too long for an unscheduled appointment. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC03','Excessive Delay in Scheduling or Rescheduling Appointment','AC','a. Rescheduled or scheduled appointment is for what the patient perceives as an unreasonable length of time. b. Delay in scheduling or rescheduling is creating hardship for patient/family. c.  Patients perceive an excessive wait for referrals to a specialty physician or clinic.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC04','Delay/Postponement in Scheduled Test/Procedures or Surgery','AC','a. The test or procedure was delayed for what the patient perceives as unreasonable length of time.  b.  No communication to patient/family that surgery is delayed and length of time for delay.  c.  Surgery is postponed but patient was not informed in reasonable timeframe. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC05','Delay in Receiving Test Results','AC','a. Patient has requested results of tests but results not available. b. Staff does not provide results of tests that are available.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC06','Excessive Wait for Care','AC','a. Excessive waits for nursing response to call bell. b. Patient perceives the waiting time was excessive for assistance with bathing, toileting or eating.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC07','Excessive Wait for Equipment','AC','For items such as wheelchair, bed, prescription eyeglasses, hearing aides, etc. the delay creates hardships for patient as well as caregiver.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC08','Delay Getting Pain Medications','AC','Patient perceives long wait following request for pain medication prescription as an outpatient.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC09','Delay Getting Other Medications','AC','a.	Patient complains of long wait to initiate medications following admission. b. The physician tells the patient about medications he will order but patient perceives a long delay in receiving the medication. c. Prescribed time for certain medications is not adhered to by staff (e.g.insulin).' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC10','Excessive Wait in Pharmacy','AC','Delay getting meds in pharmacy. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC11','Excessive Wait for Pharmacy Mailings','AC','Patient complains of excessive wait to receive medications that are to be sent (either new or refill scrips).' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('AC12','Phone Calls not Returned/Letters not Answered','AC','a. Patients complain they call physician, clinics, various support services (x-ray, lab) and a message is left for a return call which does not occur. b. Phone rings over long period of time but no one answers/put on hold too long. c. Patient family complain they wrote a letter and have not received a response.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('OP01','Patient Does Not Have One Provider','OP','Patient complains of not having one provider or team in charge of his/her care.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('OP02','Patient Does Not Know Who is his/her Provider ','OP','Patient perceives not being informed of the name of his/her treating physician. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PR01','Patient/Family not Included in Planning Care','PR','a. The patient perceives treatment and care is performed to him, not with him.  Patient is not asked for input and/or no one listens to patient. b. The patient has had previous health care situations that were difficult: he wants to avoid a repetition by having the opportunity to voice his concern. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PR02','Patient/Family Disagrees About Decisions on Care','PR','a.	Patient disagrees with treatment. b. Patient is admitted to Mental Health/Medical and believes his mental/medical problems are not being addressed appropriately. c. Patient voices complaints about being placed in nursing home, foster home, domiciliary, etc. d. Dissatisfied with medication or lack of medication. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PR03','Lack of Confidence or Trust in Caregiver','PR','Patient expresses lack of confidence or trust in caregiver. Caregiver can include physician, nurse or anyone providing hands on care/treatment to the patient. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PR04','Request for Non-Formulary Medicine','PR','a. Patient requests a non-formulary medication that may be appropriate treatment. b. Patient is not satisfied with a similar medication currently on formulary. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EM01','Emotional Needs not Met','EM','The patient has a medical or surgical diagnosis that results in the patient experiencing a gamut of emotions - fear, depression, anxiety, etc., which are not addressed or referred by the staff. The focus is only on the presenting medical/surgical problem. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EM02','Practioner''s English is Difficult to Understand','EM','Patient/family concerned that when the doctor talks to them they have difficulty understanding what is being said because the English is broken and not clear. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EM03','Lack of Privacy','EM','a. Privacy was not provided during examinations, procedures and activities of daily living. b. Privacy not provided for confidential data collection - e.g. Means Test. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('CO01','Dissatisfied with Referral Outcome','CO','a. The patient/family is not satisfied with the clinic to which the patient has been referred. b. The patient/family is not satisfied with the physician to whom the patient has been assigned. c. Patient is not satisfied with the outcome and/or processes of the referral.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('CO02','Patient Perceives Care is not Coordinated','CO','a.	The patient''s appointments are not scheduled in any visit but over several visits. b. Delivery of care is fragmented, perceived as lacking continuity. c. Sequence of tests is inappropriate resulting in either repeat of a test or another appointment for return visit. d. Follow-up appointment not given. e. Treatment or tests physician stated would be done as follow-up was not completed. f. Patient expectations of follow-up were not met. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('CO03','Inconsistency in Information','CO','a. Patient/family indicates the information the doctor gave them is not the same as the information the nurse is giving them. b. Information the patient understands from a specialty referral differs from information he is receiving from primary physician. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('CO04','Appointment Date/Time Concerns','CO','a.	The patient arrives for the appointment indicated on his/her date card but the computer appointment is for a different time. b. Patient misread or misunderstood appointment time. c. Administrative error in setting appointing or in advising patient. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('ED01','Diagnosis / care / prevention','ED','During the hospitalization, at the time of discharge, or at the conclusion of a clinic appointment, the patient believes he has received very limited or no education related to his diagnosis, care or prevention. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('ED02','Purpose/side effects of medication','ED','The patient perceives there has been limited or no explanation concerning the purpose, side effects or medications, and/or information on when he/she should be able to see results. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('FI01','Family not Involved in Patient''s Care','FI','Caregivers or family members with power of attorney for health care, or immediate family are not included in decisions about certain treatments or medications, about certain aspects of length of stay or allowed to share specific information that would have an impact on the patient''s care or recovery. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PC01','Hygiene, Diet, Feeding, Therapy or Ambulation Concerns','PC','a. Patient/family perceives neglect with bathing, oral hygiene or cleanliness of clothing, pajamas and bed linen. b. Patient is upset with diet ordered. c. Patient/family has concerns about or refuses nasogastric feeding. d. Food served was not appropriate temperature. e. Patient does not receive assistance with the meal. f. Upset that appropriate therapy has not been implemented or is not implemented as ordered. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('PC02','Problems with Pain','PC','a. Pain medication not ordered following a painful procedure. b. Patient requesting pain medication because he has been taking it over long periods of time - physician is not ordering the medication as patient requests and he is in pain. c. Pain medication ordered is not effective. d. Patient upset that alternative pain management is ordered in place of pain  medications - e.g. referral to pain management clinic.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('TR01','Lack of Coordination Between Inpatient and Outpatient Care','TR','This may involve lack of follow-up appointments, communications or instructions. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RI01','Missing Personal Property','RI','Patient''s articles of clothing, eyeglasses, dentures, hearing aids or other personal effects or money are missing. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RI02','Allegations of Negligence/Malpractice','RI','Patient/family perceives that the professional who administered care (physician, nurse, technician) did so in a manner that resulted in an adverse reaction or condition or misdiagnosis.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RI03','Allegations of Abuse','RI','Patient/family perceive certain staff behaviors or practice is abusive to the patient. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RI04','Medication Error','RI','Patient/family is upset patient was given the wrong medication.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RI05','Issue Related to Safety','RI','Patient/family identify problems that are safety hazards for patients/visitors.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RE01','Complaints Concerning Medical Records','RE','a.	Patient''s expectation of time frame to receive copies was not met.  Time frame was not communicated to the patient regarding when to expect copies. b. Record is missing from previous treating Medical Center.  Record not available in treating Medical Center.  Certain segments of record not available. c. Disagrees with documentation in medical record. d. Patient/family perceives a break in confidentiality of information they shared with staff.  Patient/family perceives break in confidentiality of information relating to the diagnosis. Information from record shared without patient''s permission.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('LL01','Eligibility for Medical Care','LL','Medical care includes clinic treatment, follow-up, hospital, extended care, nursing home care. Patient is upset that he is considered ineligible for clinic treatment or follow-up. a. The patient is not eligible due to income status. b. The patient is eligible for hospital admission but was denied due to error. c. The patient was denied nursing home admission due to not meeting the criteria for admission. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('LL02','Dental, Prosthetics or Travel Eligibility Issues','LL','The patient complains he is ineligible for dental care, prosthetics or travel, or that he was denied in error. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('LL03','Ambulance/Private Hospital/Private Care Payment Eligibility','LL','a. The patient received a bill for ambulance service and/or perceives the VA as being responsible for payment. b. The patient had to seek emergency care and/or be admitted to private hospital and received bills, but perceives the VA as being responsible for payment. c. The patient was billed for authorized care. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('LL04','VA Billing for Service/Pharmacy Co-Payment','LL','a.	Patient did not receive the service for which he was billed. b.	Patient complains of excessive charges. c. Patient not informed charges would be made for service. d. The patient is inappropriately charged co-payment. e. Co-pay charges incorrect. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EV01','Complaints Concerning Canteen Cafeteria/Store/Vending Areas','EV','a. Hours of business do not meet needs of patients/families. b. Limited stock available. c. Gender specific items not available. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EV02','Difficulty Finding Parking','EV','a. Lack of handicap parking in clinic areas for patients/visitors. b. Limited/no parking available for patients when they come for appointments. c. Visitor parking limited/not available. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('EV03','Cleanliness/Temperature Concerns','EV','a. Patients complain concerning cleanliness of room/bath room. b. Patients complain concerning hygiene of staff. c. Patient/family complain of cleanliness of waiting areas, canteen, vending, etc. d. Complains of uncomfortable temperature in building.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RG01','Medical Center Regulations','RG','The patient complains about a Medical Center regulation or policy (e.g. getting a prescription form a LMD filled). ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RG02','VA Regional Office and/or Compensation and Pension Issues','RG','' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('RG03','Receiving Personal Monies ','RG','The patient is complaining about how his money is being handled.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF01','Application for Care/Eligibility for Medical Benefits','IF','a. Patient is requesting information about procedure for obtaining health care in the VA Medical Center. b. Patient/family requests information or explanation about the various benefits offered by the VA and the criteria for eligibility (e.g. inpatient, outpatient, dental care, prosthetics, fee basis and unauthorized claims). ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF02','VA Billing for Service','IF','Patient/family request information or explanation related to billing for inpatient, outpatient, pharmacy co-pay or charges for private insurance carriers. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF04','Advance Directives','IF','a. Patient/family request information about initiating an advance directive. b. Patient/family requests information about what the advance directive should include. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF05','Referral Issues (Internal/Community)','IF','a. Patient presents a situation requiring a referral for either answers to the questions or assistance with a problem that can be resolved within the Medical Center. b. Patient presents a problem that can be resolved by a community agency, for which the Patient Representative either makes contact and/or a referral. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF06','Medical Center Regulations','IF','The patient does not understand a Medical Center regulation.  These are reviewed and explained by the Patient Representative.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF07','Obtaining Copies of Medical Records/Completion of Forms','IF','a. Patient requests information about procedure for obtaining copies of medical records. b. Patient requests information about forms that need to be completed by medical personnel. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF08','VA Regional Office Questions re: Compensation, Pension, etc ','IF','a.	Patient requests information about application for compensation or pension, etc. b. Patient received letter from VA concerning compensation or pension and does not understand what is to be done. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF09','Legal Issues','IF','a. Patient/family asks questions about process to follow for legal help with wills, Power of Attorney, etc. b. Patient/family asks for legal assistance from VA. c. Patient/family discusses concerns about health care practice and legalities. d. Questions/concerns related to legal issues pertaining to permits for invasive procedures.' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('IF10','Patient Rights/Responsibilities','IF','Patient requests copy or specific information about his/her rights as a patient. ' , mysysdate);

  INSERT INTO issue_code (issue_code, issue_code_name, issue_category_fk, description, ver)
  VALUES ('CP01','Compliment Received From Patient or Family Member','CP',' ' , mysysdate);

 END isscode;



 -- Rebuild ALL test data
 PROCEDURE build_all
 AS
 BEGIN
	natpar;
	ce;
	moc;
	ts;
	isscat;
	isscode;
 END build_all;

END pkg_build_pats_static_data;
/
DROP PACKAGE BODY PATS.PKG_COMP;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_COMP" AS

 -- Return a list of all comps, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_comp (p_aib IN CHAR, p_inst_id IN NUMBER
                    , p_rowcount IN INT
                    , p_initial_index IN INT
                    , p_cursor OUT t_cursor
                    , p_total_rowcount OUT INT
                    , p_has_next_resultset OUT BINARY_INTEGER
                    , p_number_of_indexes OUT INT)
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR2(30) := ' ';
	v_aib CHAR := UPPER(p_aib);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of Comp Records that meet the select criteria
   SELECT COUNT(*) INTO p_total_rowcount
   	 FROM comps
   	 WHERE (institution_fk = p_inst_id)
       AND (((v_aib='A') AND (inactivation_date IS NULL))
		 OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		 OR (v_aib='B')) ;

   -- Set v_startval to the starting comp_name value
   IF v_start > 0 THEN
      SELECT MAX(upper_case_name) INTO v_startval
      FROM (SELECT upper_case_name FROM comps
   	        WHERE (institution_fk = p_inst_id)
		      AND (((v_aib='A') AND (inactivation_date IS NULL))
		        OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			    OR (v_aib='B'))
            ORDER BY upper_case_name)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, comp_name, inactivation_date, ROWNUM
   	 FROM (SELECT id, comp_name, upper_case_name, inactivation_date
	 	   FROM comps
   	       WHERE (institution_fk = p_inst_id)
		     AND (((v_aib='A') AND (inactivation_date IS NULL))
		       OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			   OR (v_aib='B'))
	 	   ORDER BY upper_case_name)
     WHERE (upper_case_name > v_startval) AND (ROWNUM <= p_rowcount);

   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_comp;


 -- Return a single comp
 FUNCTION get_comp (p_id IN NUMBER)
 RETURN t_cursor
 IS
    v_cursor t_cursor;
 BEGIN
   	OPEN v_cursor FOR
 	SELECT comp_name, inactivation_date
	      , institution_fk, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 		FROM comps
			WHERE id = p_id;
	RETURN v_cursor;
 END get_comp;


 -- Update comp_name, or activate/inactivate a single comp entry
 PROCEDURE upd_comp (p_id IN NUMBER
                 , p_name IN VARCHAR2
                 , p_is_active IN BINARY_INTEGER
                 , p_ver IN OUT VARCHAR2)
 IS
 v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
 v_after_verdate DATE := NULL;
 v_nullparams BINARY_INTEGER := 0;
 v_idcount NUMBER(5,0);
 v_sysdate DATE := SYSDATE;
 BEGIN
  -- Set a variable that we can use later on to determine whether update failed due to null params.
  IF (p_name IS NULL) AND (p_is_active IS NULL)
     THEN v_nullparams := 1;
  END IF;
  -- Update name field.
  IF p_name IS NOT NULL
     THEN UPDATE comps
		   SET comp_name = p_name, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
		   v_before_verdate := v_after_verdate;
  END IF;
  -- Inactivate or Reactivate record.
  IF p_is_active = 0 THEN
      UPDATE comps
		   SET inactivation_date = v_sysdate, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
  ELSIF p_is_active = 1 THEN
	    UPDATE comps
	    SET inactivation_date = NULL, ver = v_sysdate
	    WHERE (id = p_id) AND (ver = v_before_verdate)
	    RETURNING ver INTO v_after_verdate;
  END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
  IF p_id IS NOT NULL THEN
      SELECT COUNT(*) INTO v_idcount FROM comps WHERE id=p_id;
  END IF;
  IF (p_id IS NULL) OR (v_idcount=0) THEN
      RAISE_APPLICATION_ERROR(-20001,'This Comp does not exist.');
	  ROLLBACK;
  -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
  ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	  RAISE_APPLICATION_ERROR(-20000,'This Comp was updated by another user. Please make your changes again.');
	  ROLLBACK;
  -- If update was successful, set new ROWVERSION, COMMIT data.
  ELSE
      p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
  END IF;
 END upd_comp;


 -- insert a new row into the comps table.
 PROCEDURE add_comp (p_name IN VARCHAR2, p_inst_id IN NUMBER
           ,p_Id OUT NUMBER, p_ver OUT VARCHAR2)
 IS
 BEGIN
  INSERT INTO comps (comp_name, institution_fk, ver)
    VALUES (p_name, p_inst_id, SYSDATE)
    RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
  COMMIT;
 END add_comp;


END pkg_comp;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_CONGRESSIONAL_CONTACT" AS

 -- Return a list of all congressional contacts, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_cc (p_aib IN CHAR, p_inst_id IN NUMBER
                    , p_rowcount IN INT
                    , p_initial_index IN INT
                    , p_cursor OUT t_cursor
                    , p_total_rowcount OUT INT
                    , p_has_next_resultset OUT BINARY_INTEGER
                    , p_number_of_indexes OUT INT)
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR(70) := ' ';
	v_aib CHAR := UPPER(p_aib);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of CC Records that meet the select criteria
   SELECT COUNT(id) INTO p_total_rowcount
   	 FROM congressional_contact
   	 WHERE (institution_fk = p_inst_id)
       AND (((v_aib='A') AND (inactivation_date IS NULL))
		 OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		 OR (v_aib='B')) ;

   -- Set v_startval to the starting upper case office_or_person_name value
   IF v_start > 0 THEN
      SELECT MAX(upper_case_name) INTO v_startval
      FROM (SELECT upper_case_name FROM congressional_contact
   	        WHERE (institution_fk = p_inst_id)
		      AND (((v_aib='A') AND (inactivation_date IS NULL))
		        OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			    OR (v_aib='B'))
           ORDER BY upper_case_name)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, office_or_person_name, inactivation_date, ROWNUM
   	 FROM (SELECT id, office_or_person_name, inactivation_date, upper_case_name
	 	   FROM congressional_contact
   	       WHERE (institution_fk = p_inst_id)
		     AND (((v_aib='A') AND (inactivation_date IS NULL))
		       OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			   OR (v_aib='B'))
	 	   ORDER BY upper_case_name)
   WHERE (upper_case_name > v_startval) AND (ROWNUM <= p_rowcount);


   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_cc;


 -- Return a single congressional contact
 FUNCTION get_cc (p_id IN NUMBER)
 RETURN t_cursor
 IS
    v_cursor t_cursor;
 BEGIN
   	OPEN v_cursor FOR
 	SELECT office_or_person_name, inactivation_date
	      , institution_fk AS "institution_id", TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 		FROM congressional_contact
			WHERE id = p_id;
	RETURN v_cursor;
 END get_cc;


 -- Update office_or_person_name, or activate/inactivate a single congressional contact entry
 PROCEDURE upd_cc (p_id IN NUMBER
                 , p_name IN VARCHAR2
                 , p_is_active IN BINARY_INTEGER
                 , p_ver IN OUT VARCHAR2)
 IS
 v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
 v_after_verdate DATE := NULL;
 v_nullparams BINARY_INTEGER := 0;
 v_idcount NUMBER(5,0);
 v_sysdate DATE := SYSDATE;
 BEGIN
  -- Set a variable that we can use later on to determine whether update failed due to null params.
  IF (p_name IS NULL) AND (p_is_active IS NULL)
     THEN v_nullparams := 1;
  END IF;
  -- Update name field.
  IF p_name IS NOT NULL
     THEN UPDATE congressional_contact
		   SET office_or_person_name = p_name, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
		   v_before_verdate := v_after_verdate;
  END IF;
  -- Inactivate or Reactivate record.
  IF p_is_active = 0 THEN
      UPDATE congressional_contact
		   SET inactivation_date = v_sysdate, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
  ELSIF p_is_active = 1 THEN
	    UPDATE congressional_contact
	    SET inactivation_date = NULL, ver = v_sysdate
	    WHERE (id = p_id) AND (ver = v_before_verdate)
	    RETURNING ver INTO v_after_verdate;
  END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
  IF p_id IS NOT NULL THEN
      SELECT COUNT(*) INTO v_idcount FROM congressional_contact WHERE id=p_id;
  END IF;
  IF (p_id IS NULL) OR (v_idcount=0) THEN
      RAISE_APPLICATION_ERROR(-20001,'This Congressional Contact does not exist.');
	  ROLLBACK;
  -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
  ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	  RAISE_APPLICATION_ERROR(-20000,'This Congressional Contact was updated by another user. Please make your changes again.');
	  ROLLBACK;
  -- If update was successful, set new ROWVERSION, COMMIT data.
  ELSE
      p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
  END IF;
 END upd_cc;


 -- insert a new row into the congressional_contact table.
 PROCEDURE add_cc (p_name IN VARCHAR2, p_inst_id IN NUMBER
           ,p_Id OUT NUMBER, p_ver OUT VARCHAR2)
 IS
 BEGIN
  INSERT INTO congressional_contact (office_or_person_name, institution_fk, ver)
    VALUES (p_name, p_inst_id, SYSDATE)
    RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
  COMMIT;
 END add_cc;


 -- Get next available id from congressional_contact_SEQ sequence.
 FUNCTION get_cc_nextid RETURN NUMBER
 IS
  v_nextid NUMBER;
 BEGIN
   SELECT congressional_contact_seq.NEXTVAL
   INTO v_nextid
   FROM DUAL;
	RETURN v_nextid;
 END get_cc_nextid;

END pkg_congressional_contact;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_CONTACTING_ENTITY" AS

 -- Return a list of all contacting entities, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_ce (p_aib IN CHAR
                  , p_cursor OUT t_cursor)
 IS
   v_cursor t_cursor;
 BEGIN
   IF UPPER(p_aib) = 'A' THEN
     OPEN v_cursor FOR
       SELECT id, contacting_entity_name, rollup_code, inactivation_date, sort_order
        FROM pats.contacting_entity
        WHERE inactivation_date IS NULL
	     ORDER BY sort_order;
   ELSIF UPPER(p_aib) = 'I' THEN
     OPEN v_cursor FOR
       SELECT id, contacting_entity_name, rollup_code, inactivation_date, sort_order
        FROM pats.contacting_entity
        WHERE inactivation_date IS NOT NULL
	     ORDER BY sort_order;
   ELSE
     OPEN v_cursor FOR
       SELECT id, contacting_entity_name, rollup_code, inactivation_date, sort_order
        FROM pats.contacting_entity
	     ORDER BY sort_order;
   END IF;
   p_cursor := v_cursor;
 END list_ce;


 -- Return a single contacting entity
 FUNCTION get_ce (p_id IN NUMBER)
 RETURN t_cursor
 IS
   v_cursor t_cursor;
 BEGIN
   OPEN v_cursor FOR
     SELECT contacting_entity_name, rollup_code, inactivation_date
              , sort_order
	      , TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	FROM pats.contacting_entity
	WHERE id = p_id;
   RETURN v_cursor;
 END get_ce;


   /* 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 -- Return Id of new entry.
				   , p_ver OUT VARCHAR2) -- rowversion of new row.
  IS
    v_id INT(10,0);
  BEGIN
    -- If caller didn't pass a sort order, assign the next available number
    IF p_sort_order IS NULL THEN
	  SELECT MAX(sort_order) INTO p_sort_order
	    FROM contacting_entity;
	  p_sort_order := p_sort_order + 1;
	-- Otherwise, if the passed sort order is already in use, increment the sort order
	--   on the records above the passed sort order.
	ELSE
	  UPDATE contacting_entity
	    SET sort_order = sort_order + 1
	    WHERE sort_order >= p_sort_order;
	END IF;
    INSERT INTO contacting_entity (contacting_entity_name
		   						 , rollup_code
								 , sort_order
								 , ver)
	VALUES (p_ce_name, p_rollup_code, p_sort_order, SYSDATE)
	RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
	COMMIT;
  END add_ce;


   /* 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);
  IS
    v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
	v_after_verdate DATE := NULL;
    v_nullparams BINARY_INTEGER := 0;
    v_idcount NUMBER(5,0);
	v_sysdate DATE := SYSDATE;
  BEGIN
    -- Set a variable that we can use later on to determine whether update failed due to null params.
    IF (p_ce_name IS NULL) AND (p_rollup_code IS NULL) AND (p_is_active IS NULL)
       THEN v_nullparams := 1;
    END IF;
    -- Update name and sort orcer fields.
    IF (p_ce_name IS NOT NULL)
	THEN
      UPDATE contacting_entity
	  SET contacting_entity_name = p_ce_name
		, rollup_code = p_rollup_code
		, ver = v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	  v_before_verdate := v_after_verdate;
	END IF;
	-- Reactivate or Inactivate a record.
	IF p_is_active = 1
	THEN
	  UPDATE contacting_entity
	  SET inactivation_date = NULL, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 0
	THEN
	  UPDATE contacting_entity
	  SET inactivation_date = v_sysdate, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	END IF;
    -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
    IF p_id IS NOT NULL THEN
        SELECT COUNT(*) INTO v_idcount FROM contacting_entity WHERE id=p_id;
    END IF;
    IF (p_id IS NULL) OR (v_idcount=0) THEN
        RAISE_APPLICATION_ERROR(-20001,'This Contacting Entity does not exist.');
	    ROLLBACK;
    -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
    ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	    RAISE_APPLICATION_ERROR(-20000,'This Contacting Entity was updated by another user. Please make your changes again.');
	    ROLLBACK;
    -- If update was successful, set new ROWVERSION, COMMIT data.
    ELSE
        p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
        COMMIT;
    END IF;
  END upd_ce;


  /* 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)
  IS
   v_sortord_obj pats.sortorder_object;
   v_index INT;
   v_count_in INT;
   v_count_out INT;
  BEGIN
   -- Update the sort_order field for all contacting_entitys in the table
   v_count_in := p_sortorder_list.COUNT;
   v_count_out := 0;
   v_index := p_sortorder_list.FIRST;
   LOOP
     IF p_sortorder_list.EXISTS(v_index) THEN
       v_sortord_obj := p_sortorder_list(v_index);
	   UPDATE contacting_entity
	     SET sort_order = v_sortord_obj.sort_order
	     WHERE id = v_sortord_obj.id;
	   IF SQL%ROWCOUNT > 0 THEN v_count_out := v_count_out + 1;
	   END IF;
	   v_index := p_sortorder_list.NEXT(v_index);
     ELSE
	   EXIT;
     END IF;
    END LOOP;
	IF v_count_in = v_count_out THEN
       COMMIT;
	ELSE
	   ROLLBACK;
	   RAISE_APPLICATION_ERROR(-20008,'The new sort sequence for the Contacting Entities was not updated');
	END IF;
 END upd_sortorder_list;*/

   /* 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.
  IS
   v_str1 VARCHAR2(1000) := p_sortorder_list||'^';
   v_str2 VARCHAR2(30);
   v_id INT;
   v_sortorder INT;
   v_end INT;
  BEGIN
   -- Update the sort_order field for all contacting_entitys in the table
   LOOP
      v_end := INSTR(v_str1,'^');
	  v_str2 := SUBSTR(v_str1,1,v_end-1);
	  v_str1 := SUBSTR(v_str1,v_end+1);
      IF v_str2 IS NOT NULL THEN
	    v_end := INSTR(v_str2,',');
	    v_id := TO_NUMBER(SUBSTR(v_str2,1,v_end-1));
	    v_sortorder := TO_NUMBER(SUBSTR(v_str2,v_end+1));
	    UPDATE contacting_entity
	      SET sort_order = v_sortorder
	      WHERE id = v_id;
	  ELSE
	    EXIT;
      END IF;
   END LOOP;
   COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
	  ROLLBACK;
	  RAISE_APPLICATION_ERROR(-20008,'The new sort sequence for the Contacting Entity was not updated');
 END upd_sortorder_list;


END pkg_contacting_entity;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_DELETE_LEGACY_DATA" AS


 -- Delete Congressional Contacts - PRIVATE
 PROCEDURE cc (p_inst_id IN NUMBER) -- Computing Institution id from SDS.STD_INSTITUTION table
 AS
 BEGIN
    DELETE FROM congressional_contact
	  WHERE institution_fk = p_inst_id;
	COMMIT;
 END cc;


 -- Delete Hospital Locations for a single institution - PRIVATE
 PROCEDURE hl (p_inst_id IN NUMBER) -- Institution id from SDS.STD_INSTITUTION table
 AS
 BEGIN
 	DELETE FROM hospital_location
	  WHERE institution_fk = p_inst_id;
	COMMIT;
END hl;


 -- Delete Pats_Patients - PRIVATE
 PROCEDURE patient (p_inst_id IN NUMBER) -- Computing Institution id from SDS.STD_INSTITUTION table
 AS
   v_maxid NUMBER(10,0);
   v_cnt NUMBER(10,0) := 0;
 BEGIN
   SELECT MAX(id) INTO v_maxid FROM pats.pats_patient
     WHERE institution_fk = p_inst_id;
   LOOP
     EXIT WHEN v_maxid IS NULL;
     v_cnt := v_cnt+50;
     DELETE FROM pats.pats_patient
	    WHERE (id<v_cnt) AND (institution_fk = p_inst_id);
	 COMMIT;
	 EXIT WHEN v_cnt > v_maxid;
   END LOOP;
 END patient;


 -- Delete Pats_Users - PRIVATE
 PROCEDURE patsuser (p_inst_id IN NUMBER) -- Computing Institution id from SDS.STD_INSTITUTION table
 AS
 BEGIN
    DELETE FROM pats.pats_user
	  WHERE parent_institution_fk = p_inst_id;
   COMMIT;
 END patsuser;


 -- Delete all ROCs for a single institution - PRIVATE
PROCEDURE roc (p_inst_id IN NUMBER) -- Institution id from SDS.STD_INSTITUTION table
AS
   v_maxdate DATE;
   v_date DATE;
   v_startroc VARCHAR2(8);
BEGIN
   SELECT stationnumber INTO v_startroc
     FROM sdsadm.std_institution WHERE id=p_inst_id;
   v_startroc := v_startroc||'.%';
   SELECT MIN(date_of_contact) INTO v_date
	 FROM report_of_contact WHERE (institution_fk = p_inst_id) OR (roc_number LIKE v_startroc);
   SELECT MAX(date_of_contact) INTO v_maxdate
	 FROM report_of_contact WHERE (institution_fk = p_inst_id) OR (roc_number LIKE v_startroc);
   LOOP
	 EXIT WHEN v_date IS NULL;
     v_date := v_date+30;
	 DELETE FROM report_of_contact
	   WHERE (date_of_contact < v_date)
	    AND ((institution_fk = p_inst_id) OR (roc_number LIKE v_startroc));
	 COMMIT;
     EXIT WHEN v_date > v_maxdate;
   END LOOP;
END roc;


 -- 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
 AS
   v_current_instid NUMBER(20,0);
   v_index BINARY_INTEGER;
   v_station VARCHAR2(7);
   v_cnt NUMBER := 0;
   cursor cur_child IS
     select id from sdsadm.std_institution
     where stationnumber like v_station||'%';
 BEGIN
   -- Get station number for computing institution
   select stationnumber into v_station
     from sdsadm.std_institution
	 where id=p_computing_inst_id;
   -- Delete ROCs and hospital locations for Computing and child institutions
   roc(p_computing_inst_id);
   hl(p_computing_inst_id);
   OPEN cur_child;
     LOOP
       FETCH cur_child into v_current_instid;
	   EXIT WHEN cur_child%NOTFOUND;
	   roc(v_current_instid);
	   hl(v_current_instid);
	   v_cnt := v_cnt + 1;
     END LOOP;
   CLOSE cur_child;
 -- Delete all records belonging to the Computing Institution.
   cc(p_computing_inst_id);
   patient(p_computing_inst_id);
   patsuser(p_computing_inst_id);
 END delete_all;

END pkg_delete_legacy_data;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_EMPLOYEE"  AS


  -- 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
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR(72) := ' ';
	v_lastname_std VARCHAR2(35);
	v_firstname_std VARCHAR2(25);
 BEGIN
   -- Standardize lookup name values (remove punctuation and spaces, make uppercase)
   v_lastname_std := standard_name(p_lastname,35,NULL);
   IF p_firstname IS NOT NULL THEN
      v_firstname_std := standard_name(p_firstname, 25, NULL);
   END IF;
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of pats_user Records that meet the select criteria
   SELECT COUNT(DISTINCT U.id) INTO p_total_rowcount
   	 FROM pats_user U
	   INNER JOIN roc_issue RISS
	    ON RISS.employee_involved_fk = U.id
   	 WHERE (U.parent_institution_fk = p_inst_id)
       AND (U.std_name_for_lookup LIKE v_lastname_std||'%')
	   AND ((v_firstname_std IS NULL) OR (U.std_first_name LIKE v_firstname_std||'%'));

   -- Get starting value for pats_user name
   IF v_start > 0 THEN
      SELECT MAX(std_name_for_lookup) INTO v_startval
      FROM (SELECT DISTINCT std_name_for_lookup FROM pats_user U
	         INNER JOIN roc_issue RISS
			  ON RISS.employee_involved_fk = U.id
   	        WHERE (U.parent_institution_fk = p_inst_id)
              AND (U.std_name_for_lookup LIKE v_lastname_std||'%')
        	  AND ((v_firstname_std IS NULL) OR (U.std_first_name LIKE v_firstname_std||'%'))
           ORDER BY U.std_name_for_lookup)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, last_name, first_name, middle_name
	   ,name_prefix, name_suffix, academic_degree
       ,employee_name, title, mail_code
	   ,ROWNUM
   	 FROM (SELECT DISTINCT U.id,U.last_name, U.first_name, U.middle_name
	          ,U.name_prefix, U.name_suffix, U.academic_degree
              ,DISPLAYABLE_NAME(U.last_name, U.first_name, U.middle_name,
				     U.name_suffix, U.academic_degree, NULL, 60,2) employee_name
	          ,U.title ,U.mail_code
			  ,U.std_name_for_lookup
	 	   FROM pats_user U
	        INNER JOIN roc_issue RISS
			 ON RISS.employee_involved_fk = U.id
   	        WHERE (U.parent_institution_fk = p_inst_id)
              AND (U.std_name_for_lookup LIKE v_lastname_std||'%')
        	  AND ((v_firstname_std IS NULL) OR (U.std_first_name LIKE v_firstname_std||'%'))
	 	    ORDER BY U.std_name_for_lookup)
    WHERE (std_name_for_lookup > v_startval) AND (ROWNUM <= p_rowcount);

   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_empinv_by_name;


END pkg_employee;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_FACILITY_SERV_OR_SECT" AS

 -- Return a list of all facility service or sections, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_fss (p_aib IN CHAR, p_visn_id IN INT
                    , p_rowcount IN INT
                    , p_initial_index IN INT
                    , p_cursor OUT t_cursor
                    , p_total_rowcount OUT INT
                    , p_has_next_resultset OUT BINARY_INTEGER
                    , p_number_of_indexes OUT INT)
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR(50) := ' ';
	v_aib CHAR := UPPER(p_aib);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of Hospital Location records that meet the select criteria
   SELECT COUNT(id) INTO p_total_rowcount
   	 FROM facility_service_or_section
   	 WHERE (visn_fk = p_visn_id)
       AND (((v_aib='A') AND (inactivation_date IS NULL))
		 OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		 OR (v_aib='B')) ;

   -- Set v_startval to the starting upper case service_or_section_name value
   IF v_start > 0 THEN
      SELECT MAX(upper_case_name) INTO v_startval
      FROM (SELECT upper_case_name FROM facility_service_or_section
   	        WHERE (visn_fk = p_visn_id)
		      AND (((v_aib='A') AND (inactivation_date IS NULL))
		        OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			    OR (v_aib='B'))
           ORDER BY upper_case_name)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, service_or_section_name, inactivation_date, ROWNUM
   	 FROM (SELECT id, service_or_section_name, inactivation_date, upper_case_name
	 	   FROM facility_service_or_section
   	       WHERE (visn_fk = p_visn_id)
		     AND (((v_aib='A') AND (inactivation_date IS NULL))
		       OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			   OR (v_aib='B'))
	 	   ORDER BY upper_case_name)
   WHERE (upper_case_name > v_startval) AND (ROWNUM <= p_rowcount);


   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_fss;


 -- Return a single facility service or section
 FUNCTION get_fss (p_id IN INT)
 RETURN t_cursor
 IS
    v_cursor t_cursor;
 BEGIN
   	OPEN v_cursor FOR
 	SELECT service_or_section_name, inactivation_date
	      , visn_fk AS "visn_id", TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 		FROM facility_service_or_section
			WHERE id = p_id;
	RETURN v_cursor;
 END get_fss;


 -- Update service_or_section_name, or activate/inactivate a single facility service or section entry
 PROCEDURE upd_fss (p_id IN INT
                 , p_name IN VARCHAR2
                 , p_is_active IN BINARY_INTEGER
                 , p_ver IN OUT VARCHAR2)
 IS
   v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
   v_after_verdate DATE := NULL;
   v_nullparams BINARY_INTEGER := 0;
   v_idcount NUMBER(5,0);
   v_sysdate DATE := SYSDATE;
 BEGIN
  -- Set a variable that we can use later on to determine whether update failed due to null params.
  IF (p_name IS NULL) AND (p_is_active IS NULL) THEN
     v_nullparams := 1;
  END IF;
  -- Update name field.
  IF p_name IS NOT NULL
     THEN UPDATE facility_service_or_section
		   SET service_or_section_name = p_name, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
		   v_before_verdate := v_after_verdate;
	END IF;
	-- Inactivate or Reactivate record.
 	IF p_is_active = 0 THEN
      UPDATE facility_service_or_section
		   SET inactivation_date = v_sysdate, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 1 THEN
	    UPDATE facility_service_or_section
	    SET inactivation_date = NULL, ver = v_sysdate
	    WHERE (id = p_id) AND (ver = v_before_verdate)
	    RETURNING ver INTO v_after_verdate;
	END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
  IF p_id IS NOT NULL THEN
      SELECT COUNT(*) INTO v_idcount FROM facility_service_or_section WHERE id=p_id;
  END IF;
  IF (p_id IS NULL) OR (v_idcount=0) THEN
      RAISE_APPLICATION_ERROR(-20001,'This Facility Service or Section does not exist.');
	  ROLLBACK;
  -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
  ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	  RAISE_APPLICATION_ERROR(-20000,'This Facility Service or Section was updated by another user. Please make your changes again.');
	  ROLLBACK;
  -- If update was successful, set new ROWVERSION, COMMIT data.
  ELSE
      p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
  END IF;
 END upd_fss;


 -- insert a new row into the facility_service_or_section table.
 PROCEDURE add_fss (p_name IN VARCHAR2
                , p_visn_id IN INT
                , p_id OUT INT, p_ver OUT VARCHAR2)
 IS
 BEGIN
  INSERT INTO facility_service_or_section (service_or_section_name, visn_fk, ver)
    VALUES (p_name, p_visn_id, SYSDATE)
    RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
  COMMIT;
 END add_fss;


END pkg_facility_serv_or_sect;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_HOSPITAL_LOCATION" AS

 -- Return a list of all hospital locations, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_hl (p_aib IN CHAR
                    , p_inst_id IN NUMBER
                    , p_rowcount IN INT
                    , p_initial_index IN INT
                    , p_cursor OUT t_cursor
                    , p_total_rowcount OUT INT
                    , p_has_next_resultset OUT BINARY_INTEGER
                    , p_number_of_indexes OUT INT)
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR(30) := ' ';
	v_aib CHAR := UPPER(p_aib);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of Hospital Location records that meet the select criteria
   SELECT COUNT(id) INTO p_total_rowcount
   	 FROM hospital_location
   	 WHERE (institution_fk = p_inst_id)
       AND (((v_aib='A') AND (inactivation_date IS NULL))
		 OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		 OR (v_aib='B')) ;

   -- Set v_startval to the starting upper case location_name value
   IF v_start > 0 THEN
      SELECT MAX(upper_case_name) INTO v_startval
      FROM (SELECT upper_case_name FROM hospital_location
   	        WHERE (institution_fk = p_inst_id)
		      AND (((v_aib='A') AND (inactivation_date IS NULL))
		        OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			    OR (v_aib='B'))
           ORDER BY upper_case_name)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, location_name, inactivation_date, ROWNUM
   	 FROM (SELECT id, location_name, inactivation_date, upper_case_name
	 	   FROM hospital_location
   	       WHERE (institution_fk = p_inst_id)
		     AND (((v_aib='A') AND (inactivation_date IS NULL))
		       OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			   OR (v_aib='B'))
	 	   ORDER BY upper_case_name)
   WHERE (upper_case_name > v_startval) AND (ROWNUM <= p_rowcount);


   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_hl;


 -- Return a single hospital location
 FUNCTION get_hl (p_id IN NUMBER)
 RETURN t_cursor
 IS
    v_cursor t_cursor;
 BEGIN
   	OPEN v_cursor FOR
 	SELECT location_name, inactivation_date
	      , institution_fk, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 		FROM hospital_location
			WHERE id = p_id;
	RETURN v_cursor;
 END get_hl;


 -- Update location_name, or activate/inactivate a single hospital location entry
 PROCEDURE upd_hl (p_id IN NUMBER
                 , p_name IN VARCHAR2
                 , p_is_active IN BINARY_INTEGER
                 , p_ver IN OUT VARCHAR2)
 IS
   v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
   v_after_verdate DATE := NULL;
   v_nullparams BINARY_INTEGER := 0;
   v_idcount NUMBER(5,0);
   v_sysdate DATE := SYSDATE;
 BEGIN
  -- Set a variable that we can use later on to determine whether update failed due to null params.
  IF (p_name IS NULL) AND (p_is_active IS NULL)
     THEN v_nullparams := 1;
  END IF;
  -- Update name field.
  IF p_name IS NOT NULL
     THEN UPDATE hospital_location
		   SET location_name = p_name, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
		   v_before_verdate := v_after_verdate;
  END IF;
  -- Inactivate or Activate record
  IF p_is_active = 0 THEN
      UPDATE hospital_location
		   SET inactivation_date = v_sysdate, ver = v_sysdate
		   WHERE (id = p_id) AND (ver = v_before_verdate)
		   RETURNING ver INTO v_after_verdate;
  ELSIF p_is_active = 1 THEN
	    UPDATE hospital_location
	    SET inactivation_date = NULL, ver = v_sysdate
	    WHERE (id = p_id) AND (ver = v_before_verdate)
	    RETURNING ver INTO v_after_verdate;
  END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
  IF p_id IS NOT NULL THEN
      SELECT COUNT(*) INTO v_idcount FROM hospital_location WHERE id=p_id;
  END IF;
  IF (p_id IS NULL) OR (v_idcount=0) THEN
      RAISE_APPLICATION_ERROR(-20001,'This Hospital Location does not exist.');
	  ROLLBACK;
  -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
  ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	  RAISE_APPLICATION_ERROR(-20000,'This Hospital Location was updated by another user. Please make your changes again.');
	  ROLLBACK;
  -- If update was successful, set new ROWVERSION, COMMIT data.
  ELSE
      p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
  END IF;
 END upd_hl;


 -- insert a new row into the hospital_location table.
 PROCEDURE add_hl (p_name IN VARCHAR2, p_inst_id IN NUMBER
           ,p_Id OUT NUMBER, p_ver OUT VARCHAR2)
 IS
 BEGIN
  INSERT INTO hospital_location (location_name, institution_fk, ver)
    VALUES (p_name, p_inst_id, SYSDATE)
    RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
  COMMIT;
 END add_hl;


END pkg_hospital_location;
/


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

CREATE OR REPLACE PACKAGE BODY PATS.PKG_ISSUE_CATEGORY AS

  /* 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
  IS
	v_code VARCHAR2(2);
  BEGIN
    -- If caller didn't pass a sort order, assign the next available number
    IF p_sortorder IS NULL THEN
	  SELECT MAX(sort_order) INTO p_sortorder
	    FROM issue_category;
	  p_sortorder := p_sortorder + 1;
	-- Otherwise, if the passed sort order is already in use, increment the sort order
	--   on the records above the passed sort order.
	ELSE
	  UPDATE issue_category
	    SET sort_order = sort_order + 1
	    WHERE sort_order >= p_sortorder;
	END IF;
    INSERT INTO issue_category (issue_category_code
		   					    , issue_category_name
								, sort_order
		   						, is_customer_service_standard
								, ver)
	VALUES (p_isscat_code, p_isscat_name, p_sortorder, p_is_custserv, SYSDATE)
	RETURNING issue_category_code, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_isscat_code, p_ver;
	COMMIT;
  END add_isscat;

   /* Update, Activate or Inactivate Contacting Entity */
  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)
  IS
    v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
	v_after_verdate DATE := NULL;
    v_nullparams BINARY_INTEGER := 0;
    v_reccount NUMBER(5,0);
	v_sysdate DATE := SYSDATE;
  BEGIN
    -- Set a variable that we can use later on to determine whether update failed due to null params.
    IF (p_isscat_name IS NULL) AND (p_is_active IS NULL) THEN
       v_nullparams := 1;
    END IF;
  -- Update name , description or is_customer_service flag.
    IF p_isscat_name IS NOT NULL
	THEN
      UPDATE issue_category
	  SET issue_category_name = p_isscat_name
		, is_customer_service_standard = p_is_custserv
		, ver = v_sysdate
	  WHERE (issue_category_code = p_isscat_code) AND (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	  v_before_verdate := v_after_verdate;
	END IF;
	-- Reactivate or Inactivate the record.
	IF p_is_active = 1
	THEN
	  UPDATE issue_category
	  SET inactivation_date = NULL, ver=v_sysdate
	  WHERE (issue_category_code = p_isscat_code) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 0
	THEN
	  UPDATE issue_category
	  SET inactivation_date = v_sysdate, ver=v_sysdate
	  WHERE (issue_category_code = p_isscat_code) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	END IF;
    -- Determine whether a valid issue category code was passed. If not, or if itis null, raise an error.
    IF p_isscat_code IS NOT NULL THEN
        SELECT COUNT(*) INTO v_reccount FROM issue_category WHERE issue_category_code = p_isscat_code;
    END IF;
    IF (p_isscat_code IS NULL) OR (v_reccount=0) THEN
        RAISE_APPLICATION_ERROR(-20001,'This Issue Category does not exist.');
	    ROLLBACK;
    -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
    ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	    RAISE_APPLICATION_ERROR(-20000,'This Issue Category was updated by another user. Please make your changes again.');
	    ROLLBACK;
    -- If update was successful, set new ROWVERSION, COMMIT data.
    ELSE
        p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	    COMMIT;
    END IF;
  END upd_isscat;


 /* 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
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval INT := 0;
	v_aib CHAR := UPPER(p_aib);
	v_custserv CHAR:= UPPER(p_custserv_flag);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of issue categories that meet the select criteria
   SELECT COUNT(*) INTO p_total_rowcount
   	 FROM issue_category
   	 WHERE ((v_custserv='C' AND is_customer_service_standard=1)
	     OR (v_custserv='N' AND is_customer_service_standard=0)
		 OR (v_custserv='B'))
       AND (((v_aib='A') AND (inactivation_date IS NULL))
		 OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		 OR (v_aib='B')) ;

   -- Set v_startval to the starting sort order
   IF v_start > 0 THEN
      SELECT MAX(sort_order) INTO v_startval
      FROM (SELECT sort_order FROM issue_category
   	        WHERE ((v_custserv='C' AND is_customer_service_standard=1)
	            OR (v_custserv='N' AND is_customer_service_standard=0)
		        OR (v_custserv='B'))
		      AND (((v_aib='A') AND (inactivation_date IS NULL))
		        OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			    OR (v_aib='B'))
           ORDER BY sort_order)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT issue_category_code, issue_category_name, is_customer_service_standard, inactivation_date, sort_order, ROWNUM
   	 FROM (SELECT issue_category_code, issue_category_name, is_customer_service_standard, inactivation_date, sort_order
	 	   FROM issue_category
   	       WHERE ((v_custserv='C' AND is_customer_service_standard=1)
	            OR (v_custserv='N' AND is_customer_service_standard=0)
		        OR (v_custserv='B'))
		     AND (((v_aib='A') AND (inactivation_date IS NULL))
		       OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			   OR (v_aib='B'))
	 	   ORDER BY sort_order)
   WHERE (sort_order > v_startval) AND (ROWNUM <= p_rowcount);


   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_isscat;


 -- 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
 IS
 BEGIN
   	OPEN p_isscat_cursor FOR
 	SELECT issue_category_code, issue_category_name
	     , sort_order
	     , is_customer_service_standard, inactivation_date
	     , TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 	FROM issue_category
		WHERE issue_category_code = p_isscat_code;
	IF p_return_children = 1 THEN
	  OPEN p_isscode_cursor FOR
	  SELECT issue_code, issue_code_name
	      FROM issue_code
		  WHERE (issue_category_fk = p_isscat_code)
		    AND (inactivation_date IS NULL);
	ELSE
	  OPEN p_isscode_cursor FOR
	  SELECT issue_code, issue_code_name
	      FROM issue_code
		  WHERE issue_code IS NULL;
	END IF;
 END get_isscat;


 -- Update the sort_order field for an entire list of issue_category records
 PROCEDURE upd_sortorder_list(p_sortorder_list IN VARCHAR2) -- "^" delimited list of issue_category_code,sort_order pairs
 IS
   v_str1 VARCHAR2(4000) := p_sortorder_list||'^';
   v_str2 VARCHAR2(30);
   v_isscat VARCHAR2(2);
   v_sortorder INT;
   v_end INT;
 BEGIN
   -- Update the sort_order field for all issue_categories in the table
   LOOP
      v_end := INSTR(v_str1,'^');
	  -- Extract the next Issue Category Code,Sort Order pair on the "^" delimiter
	  v_str2 := SUBSTR(v_str1,1,v_end-1);
	  v_str1 := SUBSTR(v_str1,v_end+1);
	  -- If not at the end, parse apart the comma delimited Issue Category Code and Sort Order
      IF v_str2 IS NOT NULL THEN
	    v_end := INSTR(v_str2,',');
	    v_isscat := SUBSTR(v_str2,1,v_end-1);
	    v_sortorder := TO_NUMBER(SUBSTR(v_str2,v_end+1));
	    -- Update the Sort Order field for this issue category record.
	    UPDATE issue_category
	      SET sort_order = v_sortorder
	       WHERE issue_category_code = v_isscat;
	  -- If the piece we parsed out on the "^" is null, we're at the end, so we exit the loop.
	  ELSE
	    EXIT;
      END IF;
   END LOOP;
   COMMIT;
 END upd_sortorder_list;



END PKG_ISSUE_CATEGORY;
/


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

CREATE OR REPLACE PACKAGE BODY PATS.PKG_ISSUE_CODE AS

 /* Insert record to issue_category 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.
 IS
  v_max VARCHAR2(5);
  v_shortcode VARCHAR2(4);
  nxtnum INT(3);
 BEGIN
    -- Get next available issue code based on issue category
	SELECT MAX(TO_NUMBER(SUBSTR(sort_code,3,3))) INTO nxtnum
	   FROM issue_code
	   WHERE issue_code LIKE p_isscat_code||'%';
	IF nxtnum IS NULL THEN nxtnum := 1;
	ELSE nxtnum := nxtnum+1;
    END IF;
	IF nxtnum<100 THEN
	   p_issue_code := p_isscat_code||SUBSTR(TO_CHAR(nxtnum,'09'),2,2);
	ELSE
	   p_issue_code := p_isscat_code||SUBSTR(TO_CHAR(nxtnum,'099'),2,3);
	END IF;
	-- Insert the new issue code record
    INSERT INTO issue_code (issue_code
		   					    , issue_code_name
								, issue_category_fk
		   						, description
								, ver)
	VALUES (p_issue_code, p_name, p_isscat_code, p_desc, SYSDATE)
	RETURNING TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_ver;
	COMMIT;
 END add_isscode;


 /* 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);
 IS
    v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
	v_after_verdate DATE := NULL;
    v_nullparams BINARY_INTEGER := 0;
    v_reccount NUMBER(5,0);
	v_sysdate DATE := SYSDATE;
 BEGIN
    -- Set a variable that we can use later on to determine whether update failed due to null params.
    IF (p_name IS NULL) AND (p_is_active IS NULL) THEN
       v_nullparams := 1;
    END IF;
    -- Update name or description
    IF p_name IS NOT NULL
	THEN
      UPDATE issue_code
	  SET issue_code_name = p_name
		, description = p_desc
		, ver = v_sysdate
	  WHERE (issue_code = p_issue_code) AND (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	  v_before_verdate := v_after_verdate;
	END IF;
	-- Reactivate or Inactivate the record.
	IF p_is_active = 1
	THEN
	  UPDATE issue_code
	  SET inactivation_date = NULL, ver=v_sysdate
	  WHERE (issue_code = p_issue_code) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 0
	THEN
	  UPDATE issue_code
	  SET inactivation_date = v_sysdate, ver=v_sysdate
	  WHERE (issue_code = p_issue_code) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	END IF;
    -- Determine whether a valid issue category code was passed. If not, or if itis null, raise an error.
    IF p_issue_code IS NOT NULL THEN
        SELECT COUNT(*) INTO v_reccount FROM issue_code WHERE issue_code = p_issue_code;
    END IF;
    IF (p_issue_code IS NULL) OR (v_reccount=0) THEN
        RAISE_APPLICATION_ERROR(-20001,'This Issue Code does not exist.');
	    ROLLBACK;
    -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
    ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
	    RAISE_APPLICATION_ERROR(-20000,'This Issue Code was updated by another user. Please make your changes again.');
	    ROLLBACK;
    -- If update was successful, set new ROWVERSION, COMMIT data.
    ELSE
        p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	    COMMIT;
    END IF;
 END upd_isscode;


 /* Bring back the new 'n' entries from the issue category 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
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR2(5) := ' ';
	v_aib CHAR := UPPER(p_aib);
 BEGIN
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of issue codesthat meet the select criteria
   SELECT COUNT(*) INTO p_total_rowcount
   	 FROM issue_code
   	 WHERE ((p_isscat_code IS NULL) OR (issue_category_fk = p_isscat_code))
	    AND (((v_aib='A') AND (inactivation_date IS NULL))
		   OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
		   OR (v_aib='B'));

   -- Set v_startval to the starting sort order
   IF v_start > 0 THEN
      SELECT MAX(sort_code) INTO v_startval
      FROM (SELECT sort_code FROM issue_code
   	        WHERE ((p_isscat_code IS NULL) OR (issue_category_fk = p_isscat_code))
			  AND (((v_aib='A') AND (inactivation_date IS NULL))
		         OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			     OR (v_aib='B'))
            ORDER BY sort_code)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT issue_code, issue_code_name, issue_category_fk, inactivation_date, ROWNUM
   	 FROM (SELECT issue_code, issue_code_name, issue_category_fk, inactivation_date, sort_code
	 	   FROM issue_code
   	        WHERE ((p_isscat_code IS NULL) OR (issue_category_fk = p_isscat_code))
			  AND (((v_aib='A') AND (inactivation_date IS NULL))
		         OR ((v_aib='I') AND (inactivation_date IS NOT NULL))
			     OR (v_aib='B'))
	 	   ORDER BY sort_code)
   WHERE (sort_code > v_startval) AND (ROWNUM <= p_rowcount);

   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_isscode;


 -- Return all data for a single issue code, identified by p_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
 IS
 BEGIN
   	OPEN p_isscode_cursor FOR
 	SELECT issue_code, issue_code_name
	     , description
	     , issue_category_fk, inactivation_date
	     , TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	 	FROM issue_code
		WHERE issue_code = p_issue_code;
 END get_isscode;



END PKG_ISSUE_CODE;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_LOAD_LEGACY_DATA" AS

 -- PRIVATE  -  Load name parts from an up-arrow delimited string into a pats.string_list array.
 PROCEDURE parse_name (p_str IN OUT VARCHAR2 -- Coming in, contains name parts, going out, names parts removed
                   , p_name_field_list IN OUT pats.string_list) -- Name parts
 IS
   v_end INT;
 BEGIN
   FOR I IN 1..6
   LOOP
         v_end := INSTR(p_str,'^');
         p_name_field_list(I) := SUBSTR(p_str,1,v_end-1);
		 p_str := SUBSTR(p_str,v_end+1,200);
   END LOOP;
 END;

 -- PRIVATE  -  Return KAAJEE User Identifier
 PROCEDURE kaajee_user_id (p_vista_ien IN NUMBER
                         , p_server_station IN VARCHAR2
						 , p_kaajee_userid OUT VARCHAR2)
 IS
 BEGIN
   p_kaajee_userid := 'PATS_DUZ_'||TO_CHAR(p_vista_ien)||'~CMPSYS_'||p_server_station;
 END;

 -- PRIVATE  -  Insert or update record to main report_of_contact table, roc_phone_fax and roc_contacting_entity.
 PROCEDURE setroc (p_rocno IN VARCHAR2 -- ROC number
 		   		  , p_str IN VARCHAR2 -- String containing ^ delimited main ROC data
				  , p_instid IN INT -- Id of Parent Station Number from SDSADM.STD_INSTITUTION materialized view
				  , p_server_station IN VARCHAR2 -- Station No.from Server
				  , p_last_stationno IN OUT VARCHAR2 -- Last station number processed
				  , p_last_instid IN OUT INT -- Id from SDSADM.STD_INSTITUTION for last station processed.
				  , p_issue_text IN OUT VARCHAR2 -- Issue Text
				  , p_restext_list IN OUT pats.long_string_list -- Resolution Text
				  , p_rocno_out OUT VARCHAR2 -- If insert/select is successful, set to ROC number
				  , p_is_new_roc OUT INT) -- Set to 1 if this is a new ROC, to 0 otherwise.
 IS
   v_count INT;
   v_end INT;
   v_str VARCHAR2(300) := p_str;
   v_field_list pats.string_list := pats.string_list();
   v_patient_id INT;
   v_infoby_id INT;
   v_entby_id INT;
   v_user_id VARCHAR2(60);
   v_cc_id INT;
   v_sysdate DATE := SYSDATE;
   v_count_restext INT := p_restext_list.COUNT;
   v_errmsg VARCHAR2(50);
 BEGIN
   v_errmsg := 'Main Roc Data -';
   SELECT COUNT(*) INTO v_count FROM report_of_contact
	 WHERE roc_number = p_rocno;
   -- If the entry is not yet in the table, parse out all the fields and add a new entry to report_or_contact
   IF v_count = 0 THEN
     p_is_new_roc := 1;
     v_field_list.EXTEND(16);
	 FOR I IN 1..16
	  LOOP
          v_end := INSTR(v_str,'^');
          v_field_list(I) := SUBSTR(v_str,1,v_end-1);
		  v_str := SUBSTR(v_str,v_end+1,300);
	  END LOOP;
	 -- Get ID for Patient
	 v_errmsg := 'Invalid Patient IEN '||TO_CHAR(v_field_list(2))||' -';
	 v_patient_id := NULL;
	 IF v_field_list(2) IS NOT NULL THEN
	   SELECT id INTO v_patient_id FROM pats_patient
	     WHERE (vista_ien = v_field_list(2))
		   AND (institution_fk = p_instid);
	 END IF;
	 -- Get ID for Info Taken By
	 v_errmsg := 'Invalid Info Taker IEN '||TO_CHAR(v_field_list(3))||' -';
	 v_infoby_id := NULL;
	 IF v_field_list(3) IS NOT NULL THEN
	   kaajee_user_id(v_field_list(3), p_server_station, v_user_id);
	   SELECT id INTO v_infoby_id FROM pats_user
	     WHERE user_identifier = v_user_id;
	 END IF;
	 -- Get ID for Entered By
	 v_errmsg := 'Invalid Entered By IEN '||TO_CHAR(v_field_list(4))||' -';
	 v_entby_id := NULL;
	 IF v_field_list(4) IS NOT NULL THEN
	   kaajee_user_id(v_field_list(4), p_server_station, v_user_id);
	   SELECT id INTO v_entby_id FROM pats_user
	     WHERE user_identifier = v_user_id;
	 END IF;
	 -- Get ID for Congressional Contact
	 v_errmsg := 'Invalid Congress.Contact '||SUBSTR(v_field_list(6),1,20)||' -';
	 v_cc_id := NULL;
	 IF v_field_list(6) IS NOT NULL THEN
	   SELECT id INTO v_cc_id FROM congressional_contact
	     WHERE (upper_case_name = UPPER(v_field_list(6)))
		   AND (institution_fk = p_instid);
	 END IF;
	 -- Get ID for Institution on this ROC
	 v_errmsg := 'Invalid ROC Station Number '||v_field_list(8)||' -';
	 IF p_last_stationno <> v_field_list(8) THEN
	   p_last_stationno := v_field_list(8);
	   SELECT id INTO p_last_instid FROM SDSADM.STD_INSTITUTION
		 WHERE stationnumber = p_last_stationno;
	   END IF;
	 -- Update report_or_contact
	 v_errmsg := 'Updating Main ROC Data -';
     INSERT INTO pats.report_of_contact (roc_number
              , date_of_contact, patient_fk
              , info_taken_by_user_fk, entered_by_user_fk
		      , treatment_status_fk, congressional_contact_fk
			  , is_internal_appeal, status, institution_fk
			  , issue_text, date_closed
			  , resolution_text1, resolution_text2, eligibility_status, category
			  , rollup_to_natl_reports_status, ver)
     VALUES (p_rocno
	          , TO_DATE(v_field_list(1),'MM/DD/YYYY'), v_patient_id
		      , v_infoby_id, v_entby_id
			  , v_field_list(5), v_cc_id
		      , v_field_list(13), v_field_list(7), p_last_instid
              , p_issue_text, TO_DATE(v_field_list(9),'MM/DD/YYYY')
              , p_restext_list(1), p_restext_list(2), v_field_list(14), v_field_list(15)
			  , v_field_list(16), v_sysdate)
     RETURNING roc_number INTO p_rocno_out;
	 -- Update phone/fax data
	 v_errmsg := 'Updating Phone/Fax Data - ';
	 IF v_field_list(11) IS NOT NULL THEN
	   INSERT INTO pats.roc_phone_fax
	    (roc_fk, name_or_description, phone_fax_number)
	   VALUES (p_rocno, v_field_list(10), v_field_list(11));
	 END IF;
	 -- Update contacting entity data
	 v_errmsg := 'Updating Contacting Entity -';
	 INSERT INTO pats.roc_contacting_entity
	   (roc_fk, contacting_entity_fk)
	 VALUES (p_rocno, v_field_list(12));
   ELSE
     p_is_new_roc := 0;
	 -- If the entry is already in the table, just get the id value.
	 SELECT roc_number INTO p_rocno_out FROM report_of_contact
	   WHERE roc_number = p_rocno;
   END IF;
    -- Clear out place where issue text and resolution text were built.
   p_issue_text := NULL;
   FOR I IN 1..v_count_restext LOOP
     p_restext_list(I) := NULL;
   END LOOP;
   v_errmsg := 'Parsing Incoming ROC Data -';
 EXCEPTION
   WHEN OTHERS THEN
	 RAISE_APPLICATION_ERROR(-20011, 'Contact (ROC):'||p_rocno||' was not updated.   Error: '||v_errmsg||SQLERRM);
  END setroc;


   -- PRIVATE  -  Insert records to roc_issue table
 PROCEDURE setiss (p_rocno IN VARCHAR2 -- ROC number
 		   		  , p_str IN VARCHAR2 -- String containing ^ delimited issues data
				  , p_no_fsos_id IN INT -- Id for 'Not Reported' entry in FSOS table.
				  , p_visn_id IN INT -- Id for entry in pats_administrative_unit table (i.e., the VISN).
				  , p_lastiss_stationno IN OUT VARCHAR2 -- Last station number processed
				  , p_lastiss_instid IN OUT INT -- Id from SDSADM.STD_INSTITUTION for last station processed.
				  , p_server_station IN VARCHAR2) -- Station number on M VistA Server
 IS
   v_end INT;
   v_str VARCHAR(110) := p_str;
   v_hlid INT;
   v_empid INT;
   v_user_id VARCHAR2(60);
   v_fsosid INT;
   v_field_list pats.string_list := pats.string_list();
   v_notrep_id INT;
   v_errmsg VARCHAR2(50);
 BEGIN
   -- v_field_list will hold a single issue entry from the array.
   v_errmsg := 'Parsing Out Issue Code Data -';
   v_field_list.EXTEND(5);
   FOR I IN 1..5
	LOOP
       v_end := INSTR(v_str,'^');
       v_field_list(I) := SUBSTR(v_str,1,v_end-1);
	   v_str := SUBSTR(v_str,v_end+1,300);
	END LOOP;
	 -- Get ID for Facility Service or Section.
	 -- If null, set it to the 'Not Reported' entry's id.
	 v_errmsg := 'Invalid FSOS '||SUBSTR(v_field_list(2),1,20)||' -';
	 IF v_field_list(2) IS NOT NULL THEN
	   SELECT id INTO v_fsosid FROM facility_service_or_section
	     WHERE (upper_case_name = UPPER(v_field_list(2)))
		   AND (visn_fk = p_visn_id);
	 ELSE
	    v_fsosid := p_no_fsos_id;
	 END IF;
	 -- Get ID for Employee Involved in the ROC from pats_user table.
	 v_errmsg := 'Invalid Employee Involved IEN '||TO_CHAR(v_field_list(3))||' -';
	 v_empid := NULL;
	 IF v_field_list(3) IS NOT NULL THEN
	   kaajee_user_id(v_field_list(3), p_server_station, v_user_id);
	   SELECT id INTO v_empid FROM pats_user
	     WHERE user_identifier = v_user_id;
	 END IF;
	 -- Get ID for Hospital Location
	 v_errmsg := 'Invalid Hospital Location '||SUBSTR(v_field_list(4),1,20)||' -';
	 v_hlid := NULL;
	 IF v_field_list(4) IS NOT NULL THEN
	   IF p_lastiss_stationno <> v_field_list(5) THEN
	     p_lastiss_stationno := v_field_list(5);
	     SELECT id INTO p_lastiss_instid FROM SDSADM.STD_INSTITUTION
		   WHERE stationnumber = p_lastiss_stationno;
	   END IF;
	   SELECT id INTO v_hlid FROM hospital_location
	     WHERE (upper_case_name = UPPER(v_field_list(4)))
		   AND (institution_fk = p_lastiss_instid);
	 END IF;
	 -- Update roc_issue data
   v_errmsg := 'Updating Issue Code Data -';
   INSERT INTO pats.roc_issue (roc_fk, issue_code_fk, hospital_location_fk
              , facility_servsect_fk, employee_involved_fk)
     VALUES (p_rocno
		      , v_field_list(1), v_hlid
			  , v_fsosid, v_empid);
 EXCEPTION
   WHEN OTHERS THEN
	 RAISE_APPLICATION_ERROR(-20011, 'Contact (ROC):'||p_rocno||' was not updated.   Error: '||v_errmsg||SQLERRM);
 END setiss;



 -- 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 and put Id number into the return list.
 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.
 IS
   v_index INT;
   v_str VARCHAR2(70);
   v_hlname VARCHAR2(30);
   v_hlname_upper VARCHAR2(30);
   v_station_no VARCHAR2(7);
   v_current_station VARCHAR2(7) := 0;
   v_instid NUMBER(20,0);
   v_count INT;
   v_end INT;
   v_vistaien NUMBER(18,6);
   v_id INT;
   v_sysdate DATE := SYSDATE;
   v_hl_id_list pats.string_list := pats.string_list();
 BEGIN
   -- Set number of entries in output array
   v_hl_id_list.EXTEND(p_hl_list.COUNT+1);
   -- First entry in output array indicates data table=hospital_location.
   v_hl_id_list(1) := 'H';
   p_all_done := 0;
   v_index := p_hl_list.FIRST;
   -- Loop through the entries in the list and load each one into v_str, then parse out fields.
   LOOP
     IF p_hl_list.EXISTS(v_index) THEN
	    v_str := p_hl_list(v_index);
        v_end := INSTR(v_str,'^');
		v_vistaien := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
		v_end := INSTR(v_str,'^');
        v_hlname := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
        v_end := INSTR(v_str,'^');
        v_station_no := SUBSTR(v_str,1,v_end-1);
		v_hlname_upper := UPPER(v_hlname);
		-- Get Institution Id from STD_INSTITUTION materialized view
		IF v_station_no <> v_current_station THEN
		  v_current_station := v_station_no;
          SELECT id INTO v_instid
            FROM SDSADM.std_institution
	        WHERE stationnumber = v_station_no;
		  --v_instid := SDSADM.get_institution_id(v_station_no);
		END IF;
		-- See whether this hospital location already exists on the table.
		SELECT COUNT(*) INTO v_count FROM hospital_location
		  WHERE institution_fk = v_instid
		    AND upper_case_name = v_hlname_upper;
	    IF v_count = 0 THEN
           INSERT INTO hospital_location (location_name, institution_fk, inactivation_date, ver)
            VALUES (v_hlname, v_instid, v_sysdate, v_sysdate)
		   RETURNING id INTO v_id;
		ELSE
		   SELECT id INTO v_id FROM hospital_location
		   WHERE institution_fk = v_instid
		     AND upper_case_name = v_hlname_upper;
		END IF;
		v_hl_id_list(v_index+1) := TO_CHAR(v_vistaien)||'^'||TO_CHAR(v_id);
	    v_index := p_hl_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   COMMIT;
   -- move the array containing IEN^ID values into output parameter
   p_hl_id_list := v_hl_id_list;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
     COMMIT;
     p_hl_id_list := v_hl_id_list;
	 p_all_done := 0;
	 IF (v_vistaien IS NOT NULL) OR (v_hlname IS NOT NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Hospital Location '||v_hlname||'  IEN:'||v_vistaien||' was not updated.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_hl;


 -- Load a list of pats_user entries - The station number in the data is assumed to be a parent station.
 PROCEDURE load_user (p_user_list IN pats.string_list -- List of user data
                 , p_user_id_list OUT pats.string_list -- List of user IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER) -- Set to 1 if all entries were updated, to 0 if not.
 IS
   v_index INT;
   v_str VARCHAR2(200);
   v_station_no VARCHAR2(7);
   v_current_station VARCHAR2(7) := '0';
   v_instid NUMBER(20,0);
   v_server_station VARCHAR2(7);
   v_vistaien VARCHAR2(18);
   v_title VARCHAR2(30);
   v_mailcode VARCHAR2(10);
   v_userid VARCHAR2(60);
   v_count INT;
   v_end INT;
   v_id INT;
   v_stdname VARCHAR2(72);
   v_std_firstname VARCHAR2(25);
   v_sysdate DATE := SYSDATE;
   v_user_list_out pats.string_list := pats.string_list();
   v_name_list pats.string_list := pats.string_list();
 BEGIN
   -- Set number of entries in output array the same as number from input array.
   v_user_list_out.EXTEND(p_user_list.COUNT);
   v_user_list_out(1) := 'U';
   p_all_done := 0;
   v_name_list.EXTEND(6);
   -- Get the station number for the server (DUZ(2)) from the first entry in the incoming list.
   v_index := p_user_list.FIRST;
   IF p_user_list.EXISTS(v_index) THEN
       v_server_station := p_user_list(v_index);
	   v_index := p_user_list.NEXT(v_index);
   END IF;
   -- Loop through the entries in the list and load each one into v_str.
   LOOP
     IF p_user_list.EXISTS(v_index) THEN
	    v_str := p_user_list(v_index)||'^';
        -- Parse out the different pieces from a single entry into local variables
        v_end := INSTR(v_str,'^');
        v_vistaien := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
        v_end := INSTR(v_str,'^');
        v_station_no := SUBSTR(v_str,1,v_end-1);
		-- Get Institution Id from STD_INSTITUTION materialized view
		IF v_station_no <> v_current_station THEN
		  v_current_station := v_station_no;
          SELECT id INTO v_instid
            FROM SDSADM.std_institution
	        WHERE stationnumber = v_station_no;
		END IF;
		-- Build the KAAJEE User Idenfitier
	    kaajee_user_id(v_vistaien, v_server_station, v_userid);
		-- See whether the entry already exists in the table.
		SELECT COUNT(*) INTO v_count FROM pats_user
		  WHERE user_identifier = v_userid;
		-- If the entry is not yet in the table, parse out all the fields and add a new entry.
		IF v_count = 0 THEN
		  -- Parse out all of the name components, build values used to lookup users by name.
		  v_str := SUBSTR(v_str,v_end+1,200);
		  parse_name(v_str, v_name_list);
          v_stdname := pats.standard_name(v_name_list(1),35,NULL)||','||
	                   pats.standard_name(v_name_list(2),25,NULL)||
					   UPPER(SUBSTR(v_name_list(3),1,1));
          v_std_firstname := pats.standard_name(v_name_list(2),25,NULL);
		  -- Parse out the title and mail code (only on employees-involved)
          v_end := INSTR(v_str,'^');
          v_title := SUBSTR(v_str,1,v_end-1);
		  v_str := SUBSTR(v_str,v_end+1,200);
          v_end := INSTR(v_str,'^');
          v_mailcode := SUBSTR(v_str,1,v_end-1);
		  -- Add a new record.
          INSERT INTO pats.pats_user (user_identifier
                   , last_name, first_name, middle_name
                   , name_prefix, name_suffix, academic_degree
				   , std_name_for_lookup, std_first_name
				   , mail_code, title
				   , parent_institution_fk)
             VALUES (v_userid
				   , v_name_list(1), v_name_list(2), v_name_list(3)
				   , v_name_list(4), v_name_list(5), v_name_list(6)
				   , ' ', v_std_firstname
				   , v_mailcode, v_title
				   , v_instid)
          RETURNING id INTO v_id;
          UPDATE pats.pats_user
             SET std_name_for_lookup = v_stdname||v_id
             WHERE id = v_id;
		-- If the entry is already in the table, just get the id value.
	    ELSE SELECT id INTO v_id FROM pats_user
		  WHERE user_identifier = v_userid;
        END IF;
		-- Set the IEN and Id values into the output table, increment running count and index
	    v_user_list_out(v_index) := v_vistaien||'^'||TO_CHAR(v_id);
	    v_index := p_user_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   COMMIT;
   -- Move the array containing IEN^ID values into the output parameter
   p_user_id_list := v_user_list_out;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
     COMMIT;
     p_user_id_list := v_user_list_out;
	 p_all_done := 0;
	 IF (v_vistaien IS NOT NULL) OR (v_name_list(1) IS NOT NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'User Last Name:'||v_name_list(1)||'  IEN:'||v_vistaien||' was not updated.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_user;


  -- Load a list of pats_patient entries - The station number in the data is assumed to be a parent station.
 PROCEDURE load_patient (p_patient_list IN pats.string_list -- List of patient data
                 , p_patient_id_list OUT pats.string_list -- List of patient IEN^Id values.
                 , p_all_done OUT BINARY_INTEGER) -- Set to 1 if all entries were updated, to 0 if not.
 IS
   v_index INT;
   v_str VARCHAR2(200);
   v_recno CHAR(1);
   v_vistaien NUMBER(18,6);
   v_station_no VARCHAR2(7);
   v_current_station VARCHAR2(7) := '0';
   v_instid NUMBER(20,0);
   v_icn VARCHAR2(29);
   v_lastname VARCHAR2(35);
   v_nssn VARCHAR2(5);
   v_stdname VARCHAR2(72);
   v_std_firstname VARCHAR2(25);
   v_ethnicity_abbr VARCHAR2(5);
   v_ethunk_id NUMBER(20);
   v_raceunk_id NUMBER(20);
   v_raceunk_count NUMBER(10);
   v_race_hl7 VARCHAR2(8);
   v_reid NUMBER(20);
   v_field_list pats.string_list := pats.string_list();
   v_count INT;
   v_end INT;
   v_id INT;
   v_patient_list_out pats.string_list := pats.string_list();
   v_outcount NUMBER(10,0) := 1;
   v_ptcount NUMBER(10,0) := 0;
 BEGIN
   -- Get UNKNOWN id values for Race and Ethnicity
   SELECT id INTO v_ethunk_id FROM sdsadm.std_ethnicity WHERE abbreviation = 'U';
   SELECT id INTO v_raceunk_id FROM sdsadm.std_race WHERE hl7code = 'UNK';
   -- The number of fields in a single array entry will up to 15, not counting
   -- the initial record number, and, in record 1, the Station and IEN which we parse separately.
   v_field_list.EXTEND(16);
   -- Set number of entries in output array to the number of patient in input array.
   IF p_patient_list.COUNT > 0 THEN
     v_patient_list_out.EXTEND((p_patient_list.COUNT/2)+1);
   ELSE v_patient_list_out.EXTEND(1);
   END IF;
   v_patient_list_out(1) := 'P';
   p_all_done := 0;
   -- Loop through the entries in the list and load each one into v_str., parse out field values, update
   --   the pats_patient file, and update output list of patient ids.
   --   Each patient will have two records to hold all of their data.
   v_index := p_patient_list.FIRST;
   LOOP
     IF p_patient_list.EXISTS(v_index) THEN
	    v_str := p_patient_list(v_index)||'^';
        -- First, parse out the record number
        v_end := INSTR(v_str,'^');
        v_recno := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1);
		-- Process record type 1
		IF v_recno=1 THEN
          -- Parse out the Station Number
          v_end := INSTR(v_str,'^');
          v_station_no := SUBSTR(v_str,1,v_end-1);
		  v_str := SUBSTR(v_str,v_end+1);
          -- Then, parse out the VistA IEN.
          v_end := INSTR(v_str,'^');
          v_vistaien := TO_NUMBER(SUBSTR(v_str,1,v_end-1));
		  v_str := SUBSTR(v_str,v_end+1);
		  -- Get Institution Id from STD_INSTITUTION materialized view
		  IF v_station_no <> v_current_station THEN
		    v_current_station := v_station_no;
            SELECT id INTO v_instid
              FROM SDSADM.std_institution
	          WHERE stationnumber = v_station_no;
		  END IF;
		  -- See whether the entry already exists in the table.
		  SELECT COUNT(*) INTO v_count FROM pats_patient
		    WHERE institution_fk = v_instid AND vista_ien = v_vistaien;
		END IF;
		-- If the entry is not yet in the table, parse out all the fields and add a new entry to pats_patient.
		IF v_count = 0 THEN
		  FOR I IN 1..15
		  LOOP
            v_end := INSTR(v_str,'^');
            v_field_list(I) := SUBSTR(v_str,1,v_end-1);
		    v_str := SUBSTR(v_str,v_end+1);
		  END LOOP;
		  -- If processing record number 1, add a new entry to the pats_patient table.
		  IF v_recno=1 THEN
            v_nssn := SUBSTR(v_field_list(2),1,1)||SUBSTR(v_field_list(10),6,4);
			v_lastname := v_field_list(2);
            v_stdname := pats.standard_name(v_lastname,35,NULL)||','||
	                   pats.standard_name(v_field_list(3),25,NULL)||
					   UPPER(SUBSTR(v_field_list(4),1,1));
            v_std_firstname := pats.standard_name(v_field_list(3),25,NULL);
			v_icn := v_field_list(1);
			IF v_icn = '-1' THEN
			  v_icn := NULL;
			END IF;
            INSERT INTO pats.pats_patient (integration_control_number
                   , last_name, first_name, middle_name
                   , name_suffix, name_prefix, academic_degree
				   , gender, date_of_birth
				   , social_security_number, is_pseudo_ssn
				   , eligibility_code, enrollment_priority
				   , nssn_lookup_value, std_name_for_lookup, std_first_name
				   , institution_fk, vista_ien)
            VALUES  (v_icn
		           , v_field_list(2), v_field_list(3), v_field_list(4)
				   , v_field_list(5), v_field_list(6), v_field_list(7)
				   , v_field_list(8), TO_DATE(v_field_list(9),'MM/DD/YYYY')
				   , v_field_list(10), TO_NUMBER(v_field_list(11))
                   , v_field_list(12), v_field_list(13)
				   , v_nssn, ' ', v_std_firstname
                   , v_instid, v_vistaien)
            RETURNING id INTO v_id;
			-- Set the IEN and Id values into the output table, increment running count
			v_outcount := v_outcount+1;
	        v_patient_list_out(v_outcount) := TO_CHAR(v_vistaien)||'^'||TO_CHAR(v_id);
		  ELSIF (v_recno=2) THEN
		    -- Get id field for patient ethnicity. If not found, set to id field for UNKNOWN.
			v_reid := v_ethunk_id;
			IF v_field_list(5) IS NOT NULL THEN
			  SELECT COUNT(*) INTO v_reid FROM sdsadm.std_ethnicity
			    WHERE abbreviation = v_field_list(5);
			  IF v_reid = 1 THEN
			    SELECT id INTO v_reid FROM sdsadm.std_ethnicity
			      WHERE abbreviation = v_field_list(5);
			  ELSE v_reid := v_ethunk_id;
			  END IF;
			END IF;
			-- Update additional fields on pats_patient table
            UPDATE pats.pats_patient
               SET std_name_for_lookup = v_stdname||v_id,
			     period_of_service = v_field_list(1),
				 is_service_connected = TO_NUMBER(v_field_list(2)),
				 service_connected_percent = TO_NUMBER(v_field_list(3)),
				 category = v_field_list(4),
				 ethnicity_fk = v_reid
            WHERE id = v_id;
			-- Update race entries for the patient
			v_raceunk_count := 0;
			FOR I IN 6..15
		    LOOP
		      -- Get id field for patient race. If not found, set to id field for UNKNOWN.
			  IF v_field_list(I) IS NOT NULL THEN
			    SELECT COUNT(*) INTO v_reid FROM sdsadm.std_race
			    WHERE hl7code = v_field_list(I);
			    IF v_reid = 1 THEN
			      SELECT id INTO v_reid FROM sdsadm.std_race
			        WHERE hl7code = v_field_list(I);
			    ELSE v_reid := v_raceunk_id;
				     v_raceunk_count := v_raceunk_count + 1;
			    END IF;
				IF (v_reid <> v_raceunk_id) OR (v_raceunk_count = 1) THEN
				  INSERT INTO pats.pats_patient_race (patient_race_fk, patient_fk)
				  VALUES (v_reid, v_id);
				END IF;
			  ELSE EXIT;
			  END IF;
		    END LOOP;
			-- Commit after 50 patients have been successfully inserted.
			v_ptcount := v_ptcount + 1;
			IF v_ptcount = 50 THEN
			  COMMIT;
			  v_ptcount := 0;
			END IF;
		  END IF;
		-- If the entry is already in the table, just get the id value.
		ELSE
		  -- Add the id value to the output array.
		  IF v_recno = 1 THEN
		    SELECT id INTO v_id FROM pats_patient
		    WHERE institution_fk = v_instid AND vista_ien = v_vistaien;
	        -- Set the IEN and Id values into the output table, increment running count
		    v_outcount := v_outcount+1;
	        v_patient_list_out(v_outcount) := TO_CHAR(v_vistaien)||'^'||TO_CHAR(v_id);
		  END IF;
        END IF;
		-- Increment index
	    v_index := p_patient_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   -- Commit the last group of patient records inserted.
   COMMIT;
   -- Move the array containing IEN^ID values into the output parameter
   p_patient_id_list := v_patient_list_out;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
     COMMIT;
     p_patient_id_list := v_patient_list_out;
	 p_all_done := 0;
	 IF (v_vistaien IS NOT NULL) OR (v_field_list(2) IS NOT NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Patient Last Name:'||v_lastname||'  IEN:'||v_vistaien||' was not updated.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_patient;


  /* 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.  - The station number in the data is assumed to be a parent station. */
 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.
 IS
   v_index INT;
   v_str VARCHAR2(100);
   v_ccname VARCHAR2(60);
   v_ccname_upper VARCHAR2(60);
   v_station_no VARCHAR2(7);
   v_current_station VARCHAR2(7) := '0';
   v_instid NUMBER(20,0);
   v_count INT;
   v_end INT;
   v_vistaien NUMBER(18,6);
   v_id INT;
   v_inactive_flag INT(1,0);
   v_inactive_date DATE;
   v_sysdate DATE := SYSDATE;
   v_cc_id_list pats.string_list := pats.string_list();
 BEGIN
   -- Set number of entries in output array
   v_cc_id_list.EXTEND(p_cc_list.COUNT+1);
   v_cc_id_list(1) := 'C';
   p_all_done := 0;
   v_index := p_cc_list.FIRST;
   -- Loop through the entries in the list and load each one into v_str.
   LOOP
     IF p_cc_list.EXISTS(v_index) THEN
	    v_str := p_cc_list(v_index)||'^';
        v_end := INSTR(v_str,'^');
		v_vistaien := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
		v_end := INSTR(v_str,'^');
        v_station_no := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
        v_end := INSTR(v_str,'^');
		v_ccname := SUBSTR(v_str,1,v_end-1);
		v_ccname_upper := UPPER(v_ccname);
		-- Get Institution Id from STD_INSTITUTION materialized view
		IF v_station_no <> v_current_station THEN
		  v_current_station := v_station_no;
          SELECT id INTO v_instid
            FROM SDSADM.std_institution
	        WHERE stationnumber = v_station_no;
		END IF;
		SELECT COUNT(*) INTO v_count FROM congressional_contact
		  WHERE institution_fk = v_instid
		    AND upper_case_name = v_ccname_upper;
	    IF v_count = 0 THEN
		   v_str := SUBSTR(v_str,v_end+1,200);
           v_end := INSTR(v_str,'^');
		   v_inactive_flag := TO_NUMBER(SUBSTR(v_str,1,v_end-1));
		   IF v_inactive_flag = 1 THEN v_inactive_date := v_sysdate;
		   ELSE v_inactive_date := NULL;
		   END IF;
           INSERT INTO congressional_contact (office_or_person_name, institution_fk, inactivation_date, ver)
            VALUES (v_ccname, v_instid, v_inactive_date, v_sysdate)
		   RETURNING id INTO v_id;
		ELSE
		   SELECT id INTO v_id FROM congressional_contact
		   WHERE institution_fk = v_instid
		     AND upper_case_name = v_ccname_upper;
		END IF;
		v_cc_id_list(v_index+1) := TO_CHAR(v_vistaien)||'^'||TO_CHAR(v_id);
	    v_index := p_cc_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   COMMIT;
   -- move the array containing IEN^ID values into output parameter
   p_cc_id_list := v_cc_id_list;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
     COMMIT;
     p_cc_id_list := v_cc_id_list;
	 p_all_done := 0;
	 IF (v_vistaien IS NOT NULL) OR (v_ccname IS NOT NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Congressional Contact '||v_ccname||'  IEN:'||v_vistaien||' was not updated.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_cc;


  /* 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.
 IS
   v_empinv_list_out pats.string_list := pats.string_list();
 BEGIN
   load_user(p_empinv_list, v_empinv_list_out, p_all_done);
   -- Move the array containing IEN^ID values into the output parameter
   v_empinv_list_out(1) := 'E';
   p_empinv_id_list := v_empinv_list_out;
   p_all_done := 1;
 END load_empinv;


  /* 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.
 IS
   v_index INT;
   v_str VARCHAR2(100);
   v_fsosname VARCHAR2(50);
   v_fsosname_upper VARCHAR2(50);
   v_visn_name VARCHAR2(30);
   v_visn_id NUMBER(20,0);
   v_vistaien VARCHAR2(20);
   v_count INT;
   v_end INT;
   v_id INT;
   v_sysdate DATE := SYSDATE;
   v_fsos_id_list pats.string_list := pats.string_list();
 BEGIN
   -- Set number of entries in output array
   v_fsos_id_list.EXTEND(p_fsos_list.COUNT);
   v_fsos_id_list(1) := 'F';
   p_all_done := 0;
   v_index := p_fsos_list.FIRST;
   -- Get the VISN from the first entry in the incoming list.
   IF p_fsos_list.EXISTS(v_index) THEN
       v_visn_name := p_fsos_list(v_index);
       SELECT id INTO v_visn_id
         FROM SDSADM.std_institution
	     WHERE vistaname = v_visn_name;
	   v_index := p_fsos_list.NEXT(v_index);
   -- Add an entry to accomodate null FSOS in incoming legacy data.
       SELECT COUNT(*) INTO v_count FROM facility_service_or_section
	     WHERE visn_fk = v_visn_id
    	   AND service_or_section_name = 'Not Reported in Patient Rep';
       IF v_count = 0 THEN
         INSERT INTO facility_service_or_section (service_or_section_name, visn_fk
	                                , inactivation_date, ver)
           VALUES ('Not Reported in Patient Rep', v_visn_id, v_sysdate, v_sysdate);
       END IF;
   END IF;
   -- Loop through the entries in the list and load each one into v_str.
   LOOP
     IF p_fsos_list.EXISTS(v_index) THEN
	    v_str := p_fsos_list(v_index)||'^';
        v_end := INSTR(v_str,'^');
		v_vistaien := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,200);
		v_end := INSTR(v_str,'^');
        v_fsosname := SUBSTR(v_str,1,v_end-1);
		v_fsosname_upper := UPPER(v_fsosname);
		SELECT COUNT(*) INTO v_count FROM facility_service_or_section
		  WHERE v_visn_id = visn_fk
		    AND v_fsosname_upper = upper_case_name;
	    IF v_count = 0 THEN
           INSERT INTO facility_service_or_section (service_or_section_name, visn_fk
		                                 , inactivation_date, ver)
            VALUES (v_fsosname, v_visn_id, v_sysdate, v_sysdate)
		    RETURNING id INTO v_id;
		ELSE
		   SELECT id INTO v_id FROM facility_service_or_section
		    WHERE v_visn_id = visn_fk
		      AND v_fsosname_upper = upper_case_name;
		END IF;
		v_fsos_id_list(v_index) := v_vistaien||'^'||TO_CHAR(v_id);
	    v_index := p_fsos_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   COMMIT;
   -- move the array containing IEN^ID values into output parameter
   p_fsos_id_list := v_fsos_id_list;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
     COMMIT;
     p_fsos_id_list := v_fsos_id_list;
	 p_all_done := 0;
	 IF (v_vistaien IS NOT NULL) OR (v_fsosname IS NOT NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Facility Service or Section:'||v_fsosname||'  IEN:'||v_vistaien||' was not updated.   Error: '||SQLERRM);
	 ELSIF (v_visn_name IS NOT NULL) AND (v_visn_id IS NULL) THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Facility Service or Section - VISN Name:'||v_visn_name||' is invalid.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_fsos;


 /* 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.
 IS
   v_index INT;
   v_str VARCHAR2(300);
   v_visn_name VARCHAR2(30);
   v_visn_id NUMBER(20,0);
   v_parent_station VARCHAR2(7);
   v_instid NUMBER(20,0);
   v_server_station VARCHAR2(7);
   v_str_main VARCHAR2(300);
   v_rocno VARCHAR2(16);
   v_last_rocno VARCHAR2(16);
   v_type VARCHAR2(4);
   v_last_type VARCHAR2(4);
   v_count INT;
   v_roc_count INT := 0;
   v_rocno_out VARCHAR2(16);
   v_is_new_roc INT;
   v_end INT;
   v_id INT;
   v_updmain INT(1) := 0;
   v_issue_text VARCHAR2(4000);
   v_temp_text VARCHAR2(4000); -- Holds the next issue or resolution text to be appended
   v_restext_list pats.long_string_list := pats.long_string_list(); -- Resolution Text
   v_restext_number INT := 2; -- Number of resolution text fields in ROC
   v_current_resno INT; -- Piece of resolution text we're currently filling.
   v_error_resolutiontext EXCEPTION;
   v_roc_id_list pats.string_list := pats.string_list();
   v_issue_list pats.string_list := pats.string_list();
   v_errmsg VARCHAR2(50) := ' ';
   v_no_fsos_id INT;
   v_lastiss_stationno VARCHAR2(7);
   v_lastiss_instid NUMBER(20,0);
   v_last_stationno VARCHAR2(7);
   v_last_instid NUMBER(20,0);
   v_textlength INT;
 BEGIN
   v_errmsg := 'Initialization phase - ';
   -- Initialize the list of resolution text fields
   v_restext_list.EXTEND(v_restext_number);
   -- The number of fields in the issue code cluster will be 4 after we parse out the ROC number and type.
   v_issue_list.EXTEND(4);
   p_all_done := 0;
   -- Get the VISN, parent and server station number from the first entry in the incoming list.
   v_errmsg := 'Parsing First Entry - ';
   v_index := p_roc_list.FIRST;
   IF p_roc_list.EXISTS(v_index) THEN
	   v_str := p_roc_list(v_index)||'^';
       v_end := INSTR(v_str,'^');
       v_visn_name := SUBSTR(v_str,1,v_end-1);
	   v_str := SUBSTR(v_str,v_end+1,300);
       v_end := INSTR(v_str,'^');
       v_parent_station := SUBSTR(v_str,1,v_end-1);
	   v_str := SUBSTR(v_str,v_end+1,300);
       v_end := INSTR(v_str,'^');
       v_server_station := SUBSTR(v_str,1,v_end-1);
	   -- Get the id numbers for the VISN and Parent Station from SDSADM.STD_INSTITUTION materialized view
	   v_errmsg := 'VISN Name Invalid -';
       SELECT id INTO v_visn_id
         FROM SDSADM.std_institution
	     WHERE vistaname = v_visn_name;
       --v_visn_id := SDSADM.get_visn_id(v_visn_name);
	   v_errmsg := 'Parent Station No. Invalid -';
       SELECT id INTO v_instid
         FROM SDSADM.std_institution
	     WHERE stationnumber = v_parent_station;
	   -- Set last station number and institution id for use in updating issue multiple and ROC.
	   v_lastiss_stationno := v_parent_station;
	   v_last_stationno := v_parent_station;
	   v_lastiss_instid := v_instid;
	   v_last_instid := v_instid;
	   v_index := p_roc_list.NEXT(v_index);
   ELSE RAISE_APPLICATION_ERROR(-20011, 'No Data Sent to load_ROC Routine');
   END IF;
   -- If the FSOS in issue multiple is null, we'll point it to the 'Not Reported' FSOS
   v_errmsg := '''Not Reported'' entry not in FSOS table';
   SELECT id INTO v_no_fsos_id FROM facility_service_or_section
	  WHERE (service_or_section_name = 'Not Reported in Patient Rep')
		AND (visn_fk = v_visn_id);
   -- Now loop through the ROC data array and add the ROCs.
   LOOP
     v_errmsg := 'Parsing Incoming ROC Data - ';
     IF p_roc_list.EXISTS(v_index) THEN
	    v_str := p_roc_list(v_index)||'^';
        -- First, parse out the ROC Number and the type of ROC data.
        v_end := INSTR(v_str,'^');
        v_rocno := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,300);
        v_end := INSTR(v_str,'^');
        v_type := SUBSTR(v_str,1,v_end-1);
		v_str := SUBSTR(v_str,v_end+1,300);
		-- Process the main data for the current ROC.
		IF v_type = 'MAIN' THEN
		   -- If previous ROC not updated, v_updmain=1--update previous ROC.
		   IF v_updmain = 1 THEN
		     setroc(v_last_rocno, v_str_main, v_instid, v_server_station, v_last_stationno, v_last_instid
			       , v_issue_text, v_restext_list, v_rocno_out, v_is_new_roc);
		   END IF;
		   v_updmain := 1;
		   v_current_resno := 1;
		   v_str_main := v_str;
		-- Process issue text
		ELSIF v_type = 'ITXT' THEN
	       v_errmsg := 'Appending Issue Text -';
		   v_end := INSTR(v_str,'^');
		   v_temp_text := SUBSTR(v_str,1,v_end-1);
		   IF v_temp_text = ' ' THEN
		      v_temp_text := CHR(10);
		   END IF;
           v_issue_text := v_issue_text||v_temp_text;
		-- Process resolution text
		ELSIF v_type = 'RTXT' THEN
		   v_errmsg := 'Appending Resolution Text -';
		   v_end := INSTR(v_str,'^');
		   v_temp_text := SUBSTR(v_str,1,v_end-1);
		   IF v_temp_text = ' ' THEN
		      v_temp_text := CHR(10);
		   END IF;
		   -- Determine which resolution text field to append the new text.
		   v_textlength := LENGTH(v_temp_text);
		   IF (LENGTH(v_restext_list(v_current_resno)) + v_textlength) > 3999 THEN
			  v_textlength := 4000 - LENGTH(v_restext_list(v_current_resno));
              v_restext_list(v_current_resno) := v_restext_list(v_current_resno)||SUBSTR(v_temp_text,1,v_textlength);
			  v_temp_text := SUBSTR(v_temp_text,v_textlength+1);
		   	  IF v_current_resno = v_restext_number THEN
		         RAISE v_error_resolutiontext;
			  ELSE v_current_resno := v_current_resno + 1;
			  END IF;
		   END IF;
           v_restext_list(v_current_resno) := v_restext_list(v_current_resno)||v_temp_text;
		-- Process other multiples in the main ROC.
		ELSE
		   -- Update the main ROC data, phone/fax and contacting entity.
		   IF v_updmain = 1 THEN
		      v_updmain := 0;
		      setroc(v_rocno, v_str_main, v_instid, v_server_station, v_last_stationno, v_last_instid,
			         v_issue_text, v_restext_list, v_rocno_out, v_is_new_roc);
		   END IF;
		   -- Update roc_issue (Issue Code) Multiple
		   IF (v_is_new_roc = 1) AND (v_type = 'ISS') THEN
		      v_errmsg := '  Updating Issue Data - ';
		      setiss(v_rocno, v_str, v_no_fsos_id, v_visn_id, v_lastiss_stationno, v_lastiss_instid, v_server_station);
		   -- Update roc_method_of_contact multiple
		   ELSIF (v_is_new_roc = 1) AND (v_type = 'MOC') THEN
		      v_end := INSTR(v_str,'^');
              v_str := SUBSTR(v_str,1,v_end-1);
			  v_errmsg := '  Method of Contact Data - ';
              INSERT INTO roc_method_of_contact
		             (method_of_contact_fk, roc_fk)
		      VALUES (v_str, v_rocno);
		   END IF;
		END IF;
		-- The first time through, set v_last_rocno to the current ROC number
		IF v_last_rocno IS NULL THEN
		   v_last_rocno := v_rocno;
		END IF;
		-- Move the updated ROC number into the output list.
		IF v_rocno <> v_last_rocno THEN
		    COMMIT;
			v_roc_count := v_roc_count + 1;
			v_roc_id_list.EXTEND;
			v_roc_id_list(v_roc_count) := v_last_rocno;
			v_last_rocno := v_rocno;
		END IF;
	    v_index := p_roc_list.NEXT(v_index);
	  ELSE
	      -- If the last ROC had no Issue Code or Method of Contact data, we need to update the last ROC.
		IF v_updmain = 1 THEN
		   setroc(v_rocno, v_str_main, v_instid, v_server_station, v_last_stationno, v_last_instid,
		          v_issue_text, v_restext_list, v_rocno_out, v_is_new_roc);
		END IF;
		COMMIT;
		v_roc_id_list.EXTEND;
		v_roc_id_list(v_roc_count+1) := v_last_rocno;
	    EXIT;
	  END IF;
   END LOOP;
   -- Move the array containing IEN^ID values into the output parameter
   p_roc_id_list := v_roc_id_list;
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
	 ROLLBACK;
     p_roc_id_list := v_roc_id_list;
	 p_all_done := 0;
	 IF (SQLCODE != -20011) THEN
	    IF v_rocno IS NOT NULL THEN
	      RAISE_APPLICATION_ERROR(-20011, 'Contact (ROC):'||v_rocno||' was not updated.   Error: '||v_errmsg||SQLERRM);
	    ELSE
		  RAISE_APPLICATION_ERROR(-20011, v_errmsg||SQLERRM);
		END IF;
	 END IF;
	 RAISE;
 END load_roc;



  /* Load a list of issue_codes from legacy data. Pass a list of ^ delimited strings containing the code, name
      and definition. For each entry on the list, find or insert a new record to issue_code table.*/
 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.
 IS
   v_index INT;
   v_str VARCHAR2(300);
   v_code VARCHAR2(5);
   v_isscat VARCHAR2(2);
   v_name VARCHAR2(60);
   v_desc VARCHAR2(1000);
   v_last_code VARCHAR2(5) := ' ';
   v_add_record INT(1) := 0;
   v_count INT;
   v_end INT;
   v_sysdate DATE := SYSDATE;
 BEGIN
   -- Set number of entries in output array
   p_all_done := 0;
   v_index := p_iss_list.FIRST;
   -- Loop through the entries in the list and load each one into v_str.
   LOOP
     IF p_iss_list.EXISTS(v_index) THEN
	    v_str := p_iss_list(v_index)||'^';
        v_end := INSTR(v_str,'^');
		v_code := SUBSTR(v_str,1,v_end-1);
		-- Process a new issue code
		IF v_code <> v_last_code THEN
		   -- v_add_record=1 to indicate we need to add the previous record.
		   IF v_add_record = 1 THEN
              INSERT INTO issue_code (issue_code, issue_code_name
		              ,issue_category_fk, description, ver)
               VALUES (v_last_code, v_name, v_isscat, v_desc, v_sysdate);
			  COMMIT;
			  v_add_record := 0;
		   END IF;
           -- If record does not already exist, then Load the main issue_code data into local variables.
		   SELECT COUNT(*) INTO v_count FROM issue_code
		     WHERE issue_code = v_code;
		   IF v_count=0 THEN
		       v_add_record := 1;
        	   v_str := SUBSTR(v_str,v_end+1,300);
		       v_end := INSTR(v_str,'^');
               v_name := SUBSTR(v_str,1,v_end-1);
		       v_str := SUBSTR(v_str,v_end+1,300);
		       v_end := INSTR(v_str,'^');
               v_isscat := SUBSTR(v_str,1,v_end-1);
			   v_desc := NULL;
		   ELSE v_add_record := 0;
		   END IF;
		   -- Set the indicator telling us we've processed the main node for this issue code
		   v_last_code := v_code;
		 -- If we will need to add the current issue code, append all the nodes containing the description
		 --   into a single variable.
		 ELSIF v_add_record = 1 THEN
           v_str := SUBSTR(v_str,v_end+1,300);
		   v_end := INSTR(v_str,'^');
           v_desc := v_desc || SUBSTR(v_str,1,v_end-1);
		 END IF;
	     v_index := p_iss_list.NEXT(v_index);
	  ELSE
	    EXIT;
	  END IF;
   END LOOP;
   -- Insert the final issue category if necessary.
   IF v_add_record = 1 THEN
      INSERT INTO issue_code (issue_code, issue_code_name
		      ,issue_category_fk, description, ver)
       VALUES (v_last_code, v_name, v_isscat, v_desc, v_sysdate);
   END IF;
   COMMIT;
   -- Set the flag indicating all entries processed normally
   p_all_done := 1;
 EXCEPTION
   WHEN OTHERS THEN
	 p_all_done := 0;
	 IF v_code IS NOT NULL THEN
	   RAISE_APPLICATION_ERROR(-20011, 'Issue Code:'||v_code||' was not updated.   Error: '||SQLERRM);
	 END IF;
	 RAISE;
 END load_iss;



  -- 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
 AS
 v_cnt INT;
 v_station_no VARCHAR2(7);
 v_count_list pats.string_list := pats.string_list();
 BEGIN
 v_count_list.EXTEND(7);
 -- Get station number for current institution id
 SELECT stationnumber INTO v_station_no
  FROM SDSADM.STD_INSTITUTION
  WHERE id=p_inst_id;

 SELECT COUNT(*) INTO v_cnt FROM report_of_contact
  WHERE roc_number LIKE v_station_no||'%';
 v_count_list(1) := 'ROC:                   '||TO_CHAR(v_cnt);

 SELECT COUNT(DISTINCT(HL.id)) INTO v_cnt FROM pats.hospital_location HL
	JOIN pats.roc_issue ROCISS
	ON ROCISS.hospital_location_fk = HL.id
  WHERE (ROCISS.roc_fk LIKE v_station_no||'%')
    AND (HL.inactivation_date IS NOT NULL);
 v_count_list(2) := 'Hospital Location:     '||TO_CHAR(v_cnt);

 SELECT COUNT(*) INTO v_cnt FROM pats_user
  WHERE user_identifier LIKE '%~CMPSYS_'||v_station_no||'%';
 v_count_list(3) := 'PATS Users/Employees:  '||TO_CHAR(v_cnt);

 SELECT COUNT(*) INTO v_cnt FROM pats_patient
  WHERE institution_fk = p_inst_id;
 v_count_list(4) := 'Patient:               '||TO_CHAR(v_cnt);

 SELECT COUNT(*) INTO v_cnt FROM congressional_contact
  WHERE institution_fk = p_inst_id;
 v_count_list(5) := 'Congressional Contact: '||TO_CHAR(v_cnt);

 SELECT COUNT(DISTINCT(FSOS.id)) INTO v_cnt FROM pats.facility_service_or_section FSOS
	JOIN pats.roc_issue ROCISS
	ON ROCISS.facility_servsect_fk = FSOS.id
  WHERE (ROCISS.roc_fk LIKE v_station_no||'%')
     AND (FSOS.inactivation_date IS NOT NULL);
 v_count_list(6) := 'Facility Serv or Sect: '||TO_CHAR(v_cnt);

 p_count_list := v_count_list;
 END counts;



END pkg_load_legacy_data;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_METHOD_OF_CONTACT"
    AS

 -- Return a list of all methods of contact, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_moc (p_aib IN CHAR
                  , p_cursor OUT t_cursor)
 IS
   v_cursor t_cursor;
 BEGIN
   IF UPPER(p_aib) = 'A' THEN
     OPEN v_cursor FOR
       SELECT id, method_of_contact_name, rollup_code, inactivation_date, sort_order
        FROM pats.method_of_contact
        WHERE inactivation_date IS NULL
	     ORDER BY sort_order;
   ELSIF UPPER(p_aib) = 'I' THEN
     OPEN v_cursor FOR
       SELECT id, method_of_contact_name, rollup_code, inactivation_date, sort_order
        FROM pats.method_of_contact
        WHERE inactivation_date IS NOT NULL
	     ORDER BY sort_order;
   ELSE
     OPEN v_cursor FOR
       SELECT id, method_of_contact_name, rollup_code, inactivation_date, sort_order
        FROM pats.method_of_contact
	     ORDER BY sort_order;
	 END IF;
   p_cursor := v_cursor;
 END list_moc;


 -- Return a single method of contact
 FUNCTION get_moc (p_id IN NUMBER)
 RETURN t_cursor
 IS
   v_cursor t_cursor;
 BEGIN
   OPEN v_cursor FOR
     SELECT method_of_contact_name, rollup_code, inactivation_date
              , sort_order
	      , TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	FROM pats.method_of_contact
	WHERE id = p_id;
   RETURN v_cursor;
 END get_moc;

  /* 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 -- Return Id of new entry.
				   , p_ver OUT VARCHAR2) -- rowversion of new row.
  IS
    v_id INT(10,0);
  BEGIN
    -- If caller didn't pass a sort order, assign the next available number
    IF p_sort_order IS NULL THEN
	  SELECT MAX(sort_order) INTO p_sort_order
	    FROM method_of_contact;
	  p_sort_order := p_sort_order + 1;
	-- Otherwise, if the passed sort order is already in use, increment the sort order
	--   on the records above the passed sort order.
	ELSE
	  UPDATE method_of_contact
	    SET sort_order = sort_order + 1
	    WHERE sort_order >= p_sort_order;
	END IF;
    INSERT INTO METHOD_OF_CONTACT (method_of_contact_name
		   						 , rollup_code
								 , sort_order
								 , ver)
	VALUES (p_moc_name, p_rollup_code, p_sort_order, SYSDATE)
	RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
	COMMIT;
  END add_moc;

   /* 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);
  IS
    v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
	v_after_verdate DATE := NULL;
    v_nullparams BINARY_INTEGER := 0;
    v_idcount NUMBER(5,0);
	v_sysdate DATE := SYSDATE;
  BEGIN
    -- Set a variable that we can use later on to determine whether update failed due to null params.
    IF (p_moc_name IS NULL) AND (p_is_active IS NULL)
       THEN v_nullparams := 1;
    END IF;
    -- Update name or sort_order
    IF (p_moc_name IS NOT NULL)
	THEN
      UPDATE METHOD_OF_CONTACT
	  SET method_of_contact_name = p_moc_name
	    , rollup_code = p_rollup_code
		, ver = v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	  v_before_verdate := v_after_verdate;
	END IF;
	-- Reactivate or Inactivate the record.
	IF p_is_active = 1
	THEN
	  UPDATE METHOD_OF_CONTACT
	  SET inactivation_date = NULL, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 0
	THEN
	  UPDATE METHOD_OF_CONTACT
	  SET inactivation_date = v_sysdate, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
    IF p_id IS NOT NULL THEN
        SELECT COUNT(*) INTO v_idcount FROM method_of_contact WHERE id=p_id;
    END IF;
    IF (p_id IS NULL) OR (v_idcount=0) THEN
        RAISE_APPLICATION_ERROR(-20001,'This Method of Contact does not exist.');
	    ROLLBACK;
    -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
    ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
        RAISE_APPLICATION_ERROR(-20000,'This Method of Contact was updated by another user. Please make your changes again.');
	    ROLLBACK;
    -- If update was successful, set new ROWVERSION, COMMIT data.
    ELSE
        p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	    COMMIT;
    END IF;
  END upd_moc;



  /* 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.
  IS
   v_str1 VARCHAR2(1000) := p_sortorder_list||'^';
   v_str2 VARCHAR2(30);
   v_id INT;
   v_sortorder INT;
   v_end INT;
  BEGIN
   -- Update the sort_order field for all method_of_contact in the table
   LOOP
	  -- Extract the next Method of Contact Id,Sort Order pair on the "^" delimiter
      v_end := INSTR(v_str1,'^');
	  v_str2 := SUBSTR(v_str1,1,v_end-1);
	  v_str1 := SUBSTR(v_str1,v_end+1);
	  -- If not at the end, parse apart the comma delimited MOC Id and Sort Order
      IF v_str2 IS NOT NULL THEN
	     v_end := INSTR(v_str2,',');
	     v_id := TO_NUMBER(SUBSTR(v_str2,1,v_end-1));
	     v_sortorder := TO_NUMBER(SUBSTR(v_str2,v_end+1));
	    -- Update the Sort Order field for this Method of Contact record.
	     UPDATE method_of_contact
	       SET sort_order = v_sortorder
	       WHERE id = v_id;
	  -- If the piece we parsed out on the "^" is null, we're at the end, so we exit the loop.
	  ELSE
	    EXIT;
      END IF;
    END LOOP;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
	  ROLLBACK;
	  RAISE_APPLICATION_ERROR(-20008,'The new sort sequence for the Method of Contact was not updated');
 END upd_sortorder_list;

END pkg_method_of_contact;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_NATL_PATS_PARAMETERS" AS

 -- Update comp_name, or activate/inactivate a single comp entry
 -- 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

 IS
    vcount INT;
 BEGIN
   SELECT COUNT(*) INTO vcount FROM national_pats_parameters;
   IF vcount = 0 THEN
      INSERT INTO national_pats_parameters (id, days_to_process_roc, date_edited)
        VALUES (1, p_days, SYSDATE)
	    RETURNING date_edited INTO p_date;
   ELSE
      UPDATE national_pats_parameters
	    SET days_to_process_roc = p_days
	      , date_edited = SYSDATE
       WHERE id=1
       RETURNING date_edited INTO p_date;
   END IF;
   COMMIT;
 END upd_param;



  /* Return the number of days to process a ROC from the National PATS parameters */
 FUNCTION  get_days_to_process_roc
 RETURN INT
 IS
   v_days INT;
 BEGIN
   SELECT days_to_process_roc INTO v_days FROM national_pats_parameters
     WHERE id=1;
   RETURN v_days;
 END get_days_to_process_roc;


END pkg_natl_pats_parameters;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_NOTIFICATION" AS


 -- 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
 IS
    v_date_sent TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
 BEGIN
  INSERT INTO notification_detail (notification_master_fk, from_user_fk, to_user_fk
                                  ,date_sent, message)
	VALUES (p_notification_master_id, p_from_user_id, p_to_user_id
	        ,v_date_sent, p_message)
	RETURNING id INTO p_det_id;
 END add_detail;


 -- 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
				 , 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
 IS
  v_index BINARY_INTEGER;
  v_detail_object pats.notification_detail_object;
  v_detail_id INT;
  v_status CHAR(1) := 'P';
  v_sysdate DATE := TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY');
  v_expdate DATE := TO_DATE(TO_CHAR(p_expdate,'MM/DD/YYYY'),'MM/DD/YYYY');
  v_date_sent TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
 BEGIN
  IF p_notification_type = 'IN' THEN
    v_expdate := NULL;
	v_status := NULL;
  END IF;
  -- Add an entry to the notification_master
  INSERT INTO notification_master (roc_fk, patient_advocate_user_fk, date_initiated
                     , status, expiration_date, notification_type
			         , querystring, employee_notified_fk)
      VALUES (p_rocno, p_pa_id, v_sysdate
	                     , v_status, v_expdate, p_notification_type
						 , p_querystring, p_empnot_id)
      RETURNING id INTO p_id;
  -- Add an entry to the notification_detail table.
  INSERT INTO notification_detail (notification_master_fk, from_user_fk, to_user_fk
                                  ,date_sent, message)
	  VALUES (p_id, p_pa_id, p_empnot_id, v_date_sent, p_message);
  COMMIT;
 END add_notif;


 -- 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, E=Expired only, C=Closed only, A or Null=ALL
 		   		, p_pa_id IN INT -- Id from pats_patient 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.

  IS
   v_status CHAR(1) := UPPER(p_status);
   v_sysdate DATE := TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY');
 BEGIN
   IF v_status IS NULL THEN v_status := 'A';
   END IF;
   OPEN p_cursor FOR
   SELECT MAS.id, MAS.date_initiated
            , DISPLAYABLE_NAME(PA.last_name, PA.first_name, PA.middle_name,
			     PA.name_suffix, PA.academic_degree, NULL, 60,2) pa_name
			, DISPLAYABLE_NAME(EMP.last_name, EMP.first_name, EMP.middle_name,
			     EMP.name_suffix, EMP.academic_degree, NULL, 60,2) recipient_name
			, MAS.status, MAS.expiration_date
			,CASE
			   WHEN expiration_date > v_sysdate THEN 0
			   ELSE 1
			 END is_expired
			,last_recipient_date_read
   FROM pats.notification_master MAS
   LEFT OUTER JOIN pats.pats_user PA
	 ON MAS.patient_advocate_user_fk = PA.id
   LEFT OUTER JOIN pats.pats_user EMP
     ON MAS.employee_notified_fk = EMP.id
   WHERE (MAS.roc_fk = p_rocno)
      AND (MAS.notification_type = p_type)
	  AND ((v_status = 'A') OR (MAS.status = v_status))
      AND ((p_pa_id IS NULL) OR (MAS.patient_advocate_user_fk = p_pa_id))
   ORDER BY MAS.notification_type, MAS.date_initiated DESC, EMP.last_name, EMP.first_name;
 END list_notif;



 -- 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
IS
  v_notif_type VARCHAR2(3);
BEGIN
  -- Retrieve the output parameter data to be displayed for any type of notification.
  SELECT MAS.id, MAS.querystring, MAS.roc_fk, MAS.date_initiated, MAS.expiration_date
        , MAS.status, MAS.patient_advocate_user_fk
        , DISPLAYABLE_NAME(PA.last_name, PA.first_name, PA.middle_name, PA.name_suffix, PA.academic_degree, NULL, 60,2)
		, MAS.employee_notified_fk
		, DISPLAYABLE_NAME(EMP.last_name, EMP.first_name, EMP.middle_name, EMP.name_suffix, EMP.academic_degree, NULL, 60,2)
		, MAS.notification_type
    INTO p_id, p_querystring, p_rocno, p_date_initiated, p_expiration_date, p_status
	   , p_pa_id, p_pa_name, p_emp_id, p_emp_name, v_notif_type
    FROM notification_master MAS
     LEFT OUTER JOIN pats_user PA
	   ON MAS.patient_advocate_user_fk = PA.id
	 LEFT OUTER JOIN pats_user EMP
	   ON MAS.employee_notified_fk = EMP.id
    WHERE ((p_id IS NULL) OR (MAS.id = p_id))
	   AND ((p_querystring IS NULL) OR (MAS.querystring = p_querystring));
	IF v_notif_type <> 'ARN' THEN
	   RAISE_APPLICATION_ERROR(-20013, 'You selected an IN instead of an ARN');
	END IF;
    OPEN p_cursor FOR
      SELECT DET.id, DET.date_sent, DET.date_read
            , DISPLAYABLE_NAME(F.last_name, F.first_name, F.middle_name,
			     F.name_suffix, F.academic_degree, NULL, 60,2) from_name
			, DISPLAYABLE_NAME(T.last_name, T.first_name, T.middle_name,
			     T.name_suffix, T.academic_degree, NULL, 60,2) to_name
			, DET.message
      FROM pats.notification_detail DET
       LEFT OUTER JOIN pats.pats_user F
	    ON DET.from_user_fk = F.id
       LEFT OUTER JOIN pats.pats_user T
        ON DET.to_user_fk = T.id
     WHERE (DET.notification_master_fk = p_id)
     ORDER BY DET.date_sent;
END get_arn;

 -- Return all data for a single 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.
IS
  v_notif_type VARCHAR2(3);
BEGIN
  -- Retrieve the output parameter data to be displayed for any type of notification.
  SELECT MAS.id, MAS.querystring, MAS.roc_fk, MAS.date_initiated
        , MAS.patient_advocate_user_fk
        , DISPLAYABLE_NAME(PA.last_name, PA.first_name, PA.middle_name, PA.name_suffix, PA.academic_degree, NULL, 60,2)
		, MAS.employee_notified_fk
		, DISPLAYABLE_NAME(EMP.last_name, EMP.first_name, EMP.middle_name, EMP.name_suffix, EMP.academic_degree, NULL, 60,2)
		, last_recipient_date_read, notification_type
    INTO p_id, p_querystring, p_rocno, p_date_initiated
	   , p_pa_id, p_pa_name, p_emp_id, p_emp_name, p_date_read, v_notif_type
    FROM notification_master MAS
     LEFT OUTER JOIN pats_user PA
	   ON MAS.patient_advocate_user_fk = PA.id
	 LEFT OUTER JOIN pats_user EMP
	   ON MAS.employee_notified_fk = EMP.id
    WHERE ((p_id IS NULL) OR (MAS.id = p_id))
	   AND ((p_querystring IS NULL) OR (MAS.querystring = p_querystring));
	IF v_notif_type <> 'IN' THEN
	   RAISE_APPLICATION_ERROR(-20013, 'You selected an ARN instead of an IN');
	END IF;
END get_in;



 -- Update Expiration Date 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.
 IS
   v_expdate DATE := NULL;
 BEGIN
   IF p_date IS NOT NULL THEN
     v_expdate := TO_DATE(TO_CHAR(p_date,'MM/DD/YYYY'),'MM/DD/YYYY');
   END IF;
   UPDATE notification_master
     SET expiration_date = NVL(v_expdate,expiration_date)
	   , status = NVL(p_status,status)
	 WHERE id = p_id;
   COMMIT;
 END upd_arn;


 -- 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
 IS
   v_rocno VARCHAR2(16);
   v_cur pats.pkg_notification.t_cursor;
   v_detid INT;
   v_date_sent TIMESTAMP WITH TIME ZONE;
   v_date_read TIMESTAMP WITH TIME ZONE;
   v_from VARCHAR2(60);
   v_to VARCHAR2(60);
   v_message VARCHAR2(4000);
   v_hdr VARCHAR2(200);
   v_total_number INT := 2; -- Total number of VARCHAR2 fields used to build resolution text
   v_max_length INT; -- maximum length of Resolution Text
   v_total_length INT := 0; -- Total length of Resolution Text
   v_current_section INT := 1; -- Part of the resolution text to which we're currently adding new text
   v_current_length INT := 0; -- Length of the part to which we're currently adding new text.
   v_list pats.long_string_list := pats.long_string_list(''); -- Place where we'll build the new resolution text.
   v_newtext VARCHAR2(4000); -- New text to be appended to old text.
   v_newlength INT; -- Length of new text to be appended to old text.
 BEGIN
   v_max_length := 4000 * v_total_number;
   v_list.EXTEND(v_total_number);
   -- Get the ROC Number from the ROC associated with this ARN
   SELECT roc_fk INTO v_rocno FROM notification_master WHERE id = p_id;
   -- Put the old resolution text into local variables
   SELECT resolution_text1, resolution_text2 INTO v_list(1), v_list(2)
     FROM report_of_contact
	 WHERE roc_number = v_rocno;
   -- Calculate current total length of resolution text, determine which part we'll be adding to.
   FOR I IN 1..v_total_number LOOP
     IF v_list(I) IS NOT NULL
	   THEN v_total_length := v_total_length + LENGTH(v_list(I));
	   v_current_section := I;
	   v_current_length := LENGTH(v_list(I));
	 END IF;
   END LOOP;
   -- Create a cursor to read through the notification detail (dialogue) for this ARN
   OPEN v_cur FOR
        SELECT DET.id, DET.date_sent, DET.date_read
            , DISPLAYABLE_NAME(F.last_name, F.first_name, F.middle_name,
			     F.name_suffix, F.academic_degree, NULL, 60,2)
			, DISPLAYABLE_NAME(T.last_name, T.first_name, T.middle_name,
			     T.name_suffix, T.academic_degree, NULL, 60,2)
			, DET.message
        FROM pats.notification_detail DET
         LEFT OUTER JOIN pats.pats_user F
	      ON DET.from_user_fk = F.id
         LEFT OUTER JOIN pats.pats_user T
          ON DET.to_user_fk = T.id
        WHERE (DET.notification_master_fk = p_id)
	    ORDER BY date_sent;
   -- Read through the cursor to append the notification dialogue to the resolution text.
   LOOP
 	   FETCH v_cur INTO
	     v_detid, v_date_sent, v_date_read, v_from, v_to, v_message;
		 EXIT WHEN v_cur%NOTFOUND;
		 IF v_message IS NOT NULL
		    THEN
		    -- Build a header for each comment
		    v_hdr := '  *** Sent: '||TO_CHAR(v_date_sent,'MM/DD/YYYY HH:MI:SS AM')
			          ||' '||displayable_time_zone(v_date_sent)
			          ||'    From: '||v_from||'    To: '||v_to;
		    IF v_date_read IS NOT NULL THEN
		       v_hdr := v_hdr || '    Read: '||TO_CHAR(v_date_read,'MM/DD/YYYY HH:MI:SS AM')
			          ||' '||displayable_time_zone(v_date_read);
		    END IF;
		    v_hdr := v_hdr || ' ***';
			-- If resolution text is not null, add a couple of line feeds before the next comment.
            IF v_total_length > 0 THEN
		       v_hdr := CHR(10)||CHR(10)||v_hdr;
		    END IF;
			-- Build new text that we want to add to our existing resolution text.
			v_newtext := v_hdr||CHR(10)||v_message||CHR(10)||'  *** End of Response ***';
			v_newlength := LENGTH(v_newtext);
		    -- Check to make sure total resolution text is not too long.
		    IF (v_total_length+v_newlength) > 7950 THEN
		       RAISE_APPLICATION_ERROR(-20012, 'Resolution Text cannot be longer than '||TO_CHAR(v_max_length)||' characters.');
		    END IF;
			-- Now append new text to the part of the resolution text we're currently updating.
			IF (v_current_length + v_newlength) > 3950 THEN
			   IF v_current_section = v_total_number THEN
		          RAISE_APPLICATION_ERROR(-20012, 'Resolution Text cannot be longer than '||TO_CHAR(v_max_length)||' characters.');
			   END IF;
			   v_current_section := v_current_section + 1;
			   v_current_length := 0;
			END IF;
			v_list(v_current_section) := v_list(v_current_section)||v_newtext;
			v_total_length := v_total_length + v_newlength;
			v_current_length := v_current_length + v_newlength;
         END IF;
    END LOOP;
	CLOSE v_cur;
	-- Move messages into resolution text.
	UPDATE report_of_contact
	  SET resolution_text1 = v_list(1), resolution_text2 = v_list(2)
	  WHERE roc_number=v_rocno;
   COMMIT;
 END move_com;


 -- 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.
 IS
--   v_sysdate DATE := TO_DATE(TO_CHAR(CURRENT_DATE,'MM/DD/YYYY'),'MM/DD/YYYY');
   v_sysdate DATE := SYSDATE;
 BEGIN
   OPEN p_cursor FOR
   SELECT MAS.id, MAS.roc_fk, MAS.date_initiated
            , DISPLAYABLE_NAME(PA.last_name, PA.first_name, PA.middle_name,
			     PA.name_suffix, PA.academic_degree, NULL, 60,2) pa_name
			, DISPLAYABLE_NAME(EMP.last_name, EMP.first_name, EMP.middle_name,
			     EMP.name_suffix, EMP.academic_degree, NULL, 60,2) recipient_name
			, MAS.status, MAS.expiration_date
   FROM pats.notification_master MAS
   LEFT OUTER JOIN pats.report_of_contact ROC
     ON ROC.roc_number = MAS.roc_fk
   LEFT OUTER JOIN pats.pats_user PA
	 ON MAS.patient_advocate_user_fk = PA.id
   LEFT OUTER JOIN pats.pats_user EMP
     ON MAS.employee_notified_fk = EMP.id
   WHERE (MAS.patient_advocate_user_fk = p_pa_id)
      AND (ROC.institution_fk = p_inst_id)
      AND (ROC.status = 'O')
      AND (MAS.notification_type = 'ARN')
	  AND (MAS.status = 'P')
      AND (MAS.expiration_date <= v_sysdate)
   ORDER BY MAS.roc_fk DESC, MAS.date_initiated DESC, EMP.last_name, EMP.first_name;
 END list_exp_arn;


  -- 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 person who read the messages (to_user_fk)
 IS
   v_date_read TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
   v_recipient_id INT;
 BEGIN
/*   SELECT CURRENT_DATE INTO v_date_read
     FROM DUAL;*/
   UPDATE notification_detail
     SET date_read = v_date_read
     WHERE (notification_master_fk = p_notif_id)
       AND (date_read IS NULL)
	   AND (to_user_fk = p_user_id);
   /* If at least one date_read was set in a detail record, and
        if the current user is the original recipient of the message
		, set the last date read in the master record to the current date as well.*/
   IF SQL%ROWCOUNT > 0 THEN
     SELECT employee_notified_fk INTO v_recipient_id
       FROM notification_master
	   WHERE id = p_notif_id;
     IF v_recipient_id = p_user_id
       THEN UPDATE notification_master
	      SET last_recipient_date_read = v_date_read
		  WHERE id = p_notif_id;
     END IF;
   END IF;
   COMMIT;
 END upd_date_read;



END pkg_notification;
/


GRANT EXECUTE ON PATS.PKG_NOTIFICATION TO PATSUSER_ROLE;
DROP PACKAGE BODY PATS.PKG_PAD_LOCATION;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_PAD_LOCATION" AS
        PROCEDURE find_station (p_location IN VARCHAR2 -- IRIS Location
                 , p_station_number OUT VARCHAR2) -- Station number
        IS
        BEGIN
           SELECT loc.PATS_STATION into p_station_number from PATS.PAD_LOCATION loc WHERE loc.IRIS_LOCATION = p_location;
        END find_station;
END pkg_pad_location;
/


GRANT EXECUTE ON PATS.PKG_PAD_LOCATION TO PATSUSER;

GRANT EXECUTE ON PATS.PKG_PAD_LOCATION TO PATSUSER_ROLE;
DROP PACKAGE BODY PATS.PKG_PATS_PATIENT;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_PATS_PATIENT"   AS

 --  PRIVATE - Update list of races for a patient
 PROCEDURE upd_races (p_id IN NUMBER -- patient id
                  , p_racelist IN VARCHAR2) -- List of patient race ids
 IS
   CURSOR v_cur_new IS SELECT column_value race_id
     FROM TABLE(return_id_table(p_racelist));
   CURSOR v_cur_old IS SELECT patient_race_fk
     FROM pats_patient_race
	 WHERE patient_fk = p_id;
   v_old_raceid NUMBER(20);
   v_new_raceid NUMBER(20);
   v_old_racestr VARCHAR2(22);
   v_count NUMBER(10);
   v_racelist VARCHAR2(200) := ','||p_racelist||',';
 BEGIN
   -- If patient has no entries in the incoming race list, just delete any old race entries
   --  for that patient.
   IF p_racelist IS NULL THEN
     DELETE FROM pats_patient_race
	   WHERE patient_fk=p_id;
   ELSE
     -- Insert any new races into the pats_patient_race_table
     OPEN v_cur_new;
     LOOP
   	   FETCH v_cur_new INTO v_new_raceid;
  	   EXIT WHEN v_cur_new%NOTFOUND;
  	   IF v_new_raceid IS NOT NULL THEN
  	     SELECT COUNT(*) INTO v_count
	       FROM pats_patient_race
	       WHERE patient_fk=p_id AND patient_race_fk=v_new_raceid;
	     IF v_count = 0 THEN
	       INSERT INTO pats_patient_race (patient_fk, patient_race_fk)
	         VALUES (p_id, v_new_raceid);
	     END IF;
	   END IF;
     END LOOP;
     -- If any of the old races in pats_patient_table are no longer valid, delete them.
     OPEN v_cur_old;
     LOOP
 	   FETCH v_cur_old INTO v_old_raceid;
	   EXIT WHEN v_cur_old%NOTFOUND;
	   v_old_racestr := ','||v_old_raceid||',';
	   v_count := INSTR(v_racelist,v_old_racestr);
	   IF v_count = 0 THEN
	     DELETE FROM pats_patient_race
	     WHERE patient_fk=p_id AND patient_race_fk=v_old_raceid;
	   END IF;
     END LOOP;
     CLOSE v_cur_new;
     CLOSE v_cur_old;
   END IF;
 END upd_races;

 -- Insert or Update data on a single pats_patient entry
 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, p_is_pseudo_ssn IN BINARY_INTEGER
                  , p_eligibility IN VARCHAR2
				  , p_category IN VARCHAR2
				  , p_enroll_priority IN VARCHAR2
                  , p_period_of_service IN VARCHAR2
                  , p_is_svc_connected IN BINARY_INTEGER
                  , p_svc_connect_pct IN INT
				  , p_eth_id IN NUMBER
				  , p_racelist IN VARCHAR2
                  , p_inst_id IN NUMBER
                  , p_vistaien IN NUMBER
                  , p_id OUT INT)
 IS
   v_count INT := 0;
   v_nssn VARCHAR2(5) := SUBSTR(p_last_name,1,1)||SUBSTR(p_ssn,6,4);
   v_stdname VARCHAR2(72) := pats.standard_name(p_last_name,35,NULL)||','||
	                         pats.standard_name(p_first_name,25,NULL)||
						     UPPER(SUBSTR(p_middle_name,1,1));
   v_std_firstname VARCHAR2(25) := pats.standard_name(p_first_name,25,NULL);
 BEGIN
   p_id := 0;
   -- look for patient using Institution Id and VistA IEN.
   SELECT COUNT(*) INTO v_count
      FROM pats.pats_patient
      WHERE (institution_fk = p_inst_id)
        AND (vista_ien = p_vistaien);
   -- If a matching patient was found, update ALL of the patient data from the parameters
   IF v_count = 1 THEN
      UPDATE pats.pats_patient
		    SET    integration_control_number = p_icn
		           , last_name = p_last_name
                   , first_name = p_first_name
                   , middle_name = p_middle_name
                   , name_prefix = p_prefix
                   , name_suffix = p_suffix
                   , academic_degree = p_degree
                   , gender = UPPER(p_gender)
                   , date_of_birth = p_dob
                   , social_security_number = p_ssn
                   , is_pseudo_ssn = p_is_pseudo_ssn
                   , eligibility_code = p_eligibility
				   , category = p_category
                   , enrollment_priority = p_enroll_priority
                   , period_of_service = p_period_of_service
                   , is_service_connected = p_is_svc_connected
                   , service_connected_percent = p_svc_connect_pct
				   , ethnicity_fk = p_eth_id
				   , nssn_lookup_value = v_nssn
				   , std_first_name = v_std_firstname
		   WHERE (institution_fk = p_inst_id)
          AND (vista_ien = p_vistaien)
       RETURNING id INTO p_id;
   ELSE
       INSERT INTO pats.pats_patient (integration_control_number
                   , last_name, first_name, middle_name
                   , name_prefix, name_suffix, academic_degree
                   , gender, date_of_birth
                   , social_security_number, is_pseudo_ssn
                   , eligibility_code, category
				   , enrollment_priority
                   , period_of_service
                   , is_service_connected, service_connected_percent
				   , ethnicity_fk
                   , nssn_lookup_value, std_name_for_lookup, std_first_name
				   , institution_fk, vista_ien)
            VALUES (p_icn
                   , p_last_name, p_first_name, p_middle_name
                   , p_prefix, p_suffix, p_degree
                   , UPPER(p_gender), p_dob
                   , p_ssn, p_is_pseudo_ssn
                   , p_eligibility, p_category
				   , p_enroll_priority
                   , p_period_of_service
                   , p_is_svc_connected, p_svc_connect_pct
				   , p_eth_id
                   , v_nssn, ' ', v_std_firstname
				   , p_inst_id, p_vistaien)
       RETURNING id INTO p_id;
   END IF;
   UPDATE pats.pats_patient
   	   SET std_name_for_lookup = v_stdname||p_id
       WHERE id = p_id;
   -- Update list of patients races
   upd_races(p_id, p_racelist);
   COMMIT;
 END set_patient;

 -- Return a single pats_patient with the given id number.
 FUNCTION get_patient (p_id IN NUMBER)
 RETURN t_cursor
 IS
  v_cursor t_cursor;
 BEGIN
  OPEN v_cursor FOR
    SELECT id, integration_control_number
	   ,last_name, first_name, middle_name
	   ,name_prefix, name_suffix, academic_degree
	   ,gender, date_of_birth
	   ,social_security_number, is_pseudo_ssn
	   ,eligibility_code , category
	   ,enrollment_priority, period_of_service
	   ,is_service_connected, service_connected_percent
	   ,institution_fk, vista_ien
	 FROM pats.pats_patient
	 WHERE id = p_id;
  RETURN v_cursor;
 END get_patient;


 PROCEDURE list_patient_by_name(p_lastname IN VARCHAR2
                  , p_firstname IN VARCHAR2
                  , p_inst_id IN NUMBER -- Reference to id in sds.std_institution 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
 IS
    v_cursor t_cursor;
    v_start INT;
    v_startval VARCHAR(72) := ' ';
	v_lastname_std VARCHAR2(35);
	v_firstname_std VARCHAR2(25);
 BEGIN
   -- Standardize lookup name values (remove punctuation and spaces, make uppercase)
   v_lastname_std := standard_name(p_lastname,35,NULL);
   IF p_firstname IS NOT NULL THEN
      v_firstname_std := standard_name(p_firstname, 25, NULL);
   END IF;
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;

   -- Set p_total_rowcount to the total number of Patient Records that meet the select criteria
   SELECT COUNT(id) INTO p_total_rowcount
   	 FROM pats_patient
   	 WHERE (institution_fk = p_inst_id)
       AND (std_name_for_lookup LIKE v_lastname_std||'%')
	   AND ((v_firstname_std IS NULL) OR (std_first_name LIKE v_firstname_std||'%'));

   -- Get starting value for patient name
   IF v_start > 0 THEN
      SELECT MAX(std_name_for_lookup) INTO v_startval
      FROM (SELECT std_name_for_lookup FROM pats_patient
   	        WHERE (institution_fk = p_inst_id)
              AND (std_name_for_lookup LIKE v_lastname_std||'%')
        	  AND ((v_firstname_std IS NULL) OR (std_first_name LIKE v_firstname_std||'%'))
           ORDER BY std_name_for_lookup)
      WHERE ROWNUM <= v_start;
   END IF;

  -- Finally select the next p_rowcount records into the cursor.
   OPEN v_cursor FOR
   	 SELECT id, integration_control_number
	   ,last_name, first_name, middle_name
	   ,name_prefix, name_suffix, academic_degree
       ,patient_name, gender, date_of_birth
	   ,eligibility_code, period_of_service, ROWNUM
   	 FROM (SELECT id, integration_control_number
	          ,last_name, first_name, middle_name
	          ,name_prefix, name_suffix, academic_degree
              ,DISPLAYABLE_NAME(last_name, first_name, middle_name,
				     name_suffix, academic_degree, NULL, 60,2) patient_name
	          ,gender, date_of_birth
	          ,eligibility_code, period_of_service
			  ,std_name_for_lookup
	 	   FROM pats_patient
   	       WHERE (institution_fk = p_inst_id)
              AND (std_name_for_lookup LIKE v_lastname_std||'%')
        	  AND ((v_firstname_std IS NULL) OR (std_first_name LIKE v_firstname_std||'%'))
	 	   ORDER BY std_name_for_lookup)
   WHERE (std_name_for_lookup > v_startval) AND (ROWNUM <= p_rowcount);

   -- Set p_cursor to the selected records in the cursor,
   -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
   -- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_patient_by_name;



 -- 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 -- Reference to id in sds.std_institution table
                  , p_cursor OUT t_cursor) -- cursor used to fill result-set with row data
 IS
 BEGIN
   OPEN p_cursor FOR
   	 SELECT id, integration_control_number
	        ,last_name, first_name, middle_name
	        ,name_prefix, name_suffix, academic_degree
            ,DISPLAYABLE_NAME(last_name, first_name, middle_name,
				     name_suffix, academic_degree, NULL, 60,2) patient_name
	        ,gender, date_of_birth
	        ,eligibility_code, period_of_service
	 FROM pats_patient
   	   WHERE (institution_fk = p_inst_id)
         AND (((p_ssn IS NULL) AND (nssn_lookup_value = p_nssn))
		  OR ((p_nssn IS NULL) AND (social_security_number = p_ssn)))
	 ORDER BY std_name_for_lookup, UPPER(middle_name);
 END list_patient_by_ssn_nssn;


END pkg_pats_patient;
/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_PATS_USER" AS


 -- Insert or Update data on a single pats_user entry
 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)
 IS
   v_count INT(10,0) := 0;
   p_ok BINARY_INTEGER := 0;
   v_stdname VARCHAR2(72) := pats.standard_name(p_last_name,35,NULL)||','||
	                         pats.standard_name(p_first_name,25,NULL)||
						     UPPER(SUBSTR(p_middle_name,1,1));
   v_std_firstname VARCHAR2(25) := pats.standard_name(p_first_name,25,NULL);
 BEGIN
   -- Determine whether this user already exists on pats_user table
   SELECT COUNT(*) INTO v_count
      FROM pats.pats_user
      WHERE user_identifier = p_userid;
   -- If the user already exists, update all fields.
   IF v_count = 1 THEN
      -- If p_nullability_flag=1, preset null fields.
	  IF (p_nullability_flag IS NOT NULL) and (p_nullability_flag=1) THEN
	      UPDATE pats.pats_user
		    SET e_mail_address = p_email_address
			  , advocate_phone_number = p_phone
		    WHERE user_identifier = p_userid;
		  COMMIT;
	  END IF;
      UPDATE pats.pats_user
		    SET last_name = p_last_name
                   , first_name = p_first_name
                   , middle_name = p_middle_name
                   , name_prefix = p_prefix
                   , name_suffix = p_suffix
                   , academic_degree = p_degree
				   , title = NVL(p_title, title)
				   , mail_code = NVL(p_mail_code, mail_code)
				   , e_mail_address = NVL(p_email_address, e_mail_address)
				   , display_expired_arns = NVL(p_display_exp_arns, display_expired_arns)
				   , advocate_phone_number = NVL(p_phone, advocate_phone_number)
				   , is_508_accessible = NVL(p_is_508_accessible, is_508_accessible)
				   , std_first_name = v_std_firstname
				   , parent_institution_fk = p_inst_id
		   WHERE user_identifier = p_userid
		   RETURNING id, title, mail_code, e_mail_address,
		           display_expired_arns, advocate_phone_number, is_508_accessible
		       INTO p_id, p_title, p_mail_code, p_email_address,
			       p_display_exp_arns, p_phone, p_is_508_accessible;
   ELSE
	   -- If the user does not already exist, add them to pats_user
      INSERT INTO pats.pats_user (user_identifier
                   , last_name, first_name, middle_name
                   , name_prefix, name_suffix, academic_degree
				   , title, mail_code, e_mail_address
				   , display_expired_arns, advocate_phone_number
				   , is_508_accessible , parent_institution_fk
				   , std_name_for_lookup, std_first_name)
      VALUES (p_userid
                   , p_last_name, p_first_name, p_middle_name
                   , p_prefix, p_suffix, p_degree
				   , p_title, p_mail_code, p_email_address
				   , p_display_exp_arns, p_phone
				   , p_is_508_accessible, p_inst_id
				   , ' ', v_std_firstname)
      RETURNING id INTO p_id;
    END IF;
    UPDATE pats.pats_user
   	     SET std_name_for_lookup = v_stdname||p_id
	     WHERE id = p_id;
	COMMIT;
   -- Update the list of users in the PATSRPTS schema, used for screening ad hoc
   -- report data.
   patsrpts.set_ad_hoc_user(p_userid, p_stationno_list);
 END set_person;


 -- 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
 IS
  v_cursor t_cursor;
 BEGIN
  OPEN v_cursor FOR
    SELECT id, user_identifier
	   ,last_name, first_name, middle_name
	   ,name_prefix, name_suffix, academic_degree
	   ,e_mail_address, title , mail_code
	   ,display_expired_arns
	   ,parent_institution_fk
	   ,inactivation_date, advocate_phone_number
	   ,is_508_accessible
	 FROM pats.pats_user
	 WHERE ((p_user_id IS NOT NULL) AND (user_identifier = p_user_id))
	    OR ((p_id IS NOT NULL) AND (id = p_id));
  RETURN v_cursor;
 END get_person;

END pkg_pats_user;


/


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

CREATE OR REPLACE PACKAGE BODY PATS."PKG_REPAIR_ROLLUP_NATL_DATA" AS

 -- PRIVATE - Delete data from rollup file pats_rollup_natl_data
 PROCEDURE del
 IS
   CURSOR v_id_cur IS
      SELECT id FROM pats_rollup_natl_data;
   v_cnt INT := 0;
   v_id INT;
 BEGIN
    -- Delete all the data from the rollup file
   OPEN v_id_cur;
   LOOP
     FETCH v_id_cur INTO v_id;
       EXIT WHEN v_id_cur%NOTFOUND;
     DELETE from pats_rollup_natl_data
       WHERE id = v_id;
     v_cnt := v_cnt + 1;
     IF v_cnt = 50 THEN
       COMMIT;
       v_cnt := 0;
     END IF;
   END LOOP;
   CLOSE v_id_cur;
   -- Clean up any remaining records
   DELETE FROM pats_rollup_natl_data;
   COMMIT;
   -- Reset the sequence number
   EXECUTE IMMEDIATE 'DROP SEQUENCE pats_rollup_to_natl_seq';
   EXECUTE IMMEDIATE 'CREATE SEQUENCE pats_rollup_to_natl_seq START WITH 1 MAXVALUE 1E+28  MINVALUE 1  NOCYCLE  NOCACHE  NOORDER';
 END del;



 -- PRIVATE - Build a string of ROC Main Data
 PROCEDURE rocmain(p_rocno IN VARCHAR2, p_date IN DATE)
 IS
   v_str VARCHAR2(1000);
   v_date_of_contact VARCHAR2(8);
   v_ssn VARCHAR2(10);
   v_pseudo CHAR(1);
   v_sex CHAR(1);
   v_dob VARCHAR2(8);
   v_status CHAR(1);
   v_resdate VARCHAR2(8);
   v_tscode CHAR(1);
   v_pos VARCHAR2(25);
   v_station VARCHAR2(7);
   v_days_to_res NUMBER(10,0) := 0;
   v_elig VARCHAR2(30);
   v_ce VARCHAR2(2);
   v_visn VARCHAR2(2);
   v_is_clin_appeal INTEGER(1,0);
   v_icn VARCHAR2(29);
   v_infoby VARCHAR2(60);
   v_cc_name VARCHAR2(60);
   v_compgiven CHAR(1) := '0';
   v_comp_name VARCHAR2(30);
   v_category VARCHAR2(30);
   v_ethnicity VARCHAR2(30);
  BEGIN
    -- Select data from report_of_contact and referenced tables
    SELECT TO_CHAR(roc.date_of_contact,'MMDDYYYY'), roc.status, TO_CHAR(roc.date_closed,'MMDDYYYY')
	     , ts.rollup_code, inst.stationnumber, SUBSTR(visn.vistaname,6,2)
		 , roc.eligibility_status, roc.is_internal_appeal
		 , cc.office_or_person_name
		 , comps.comp_name, roc.category
		 , pt.social_security_number, pt.is_pseudo_ssn, pt.gender, TO_CHAR(pt.date_of_birth,'MMDDYYYY')
		 , pt.period_of_service, pt.integration_control_number
		 , PATS.DISPLAYABLE_NAME(iby.last_name,iby.first_name,iby.middle_name,iby.name_suffix,iby.academic_degree,NULL,60,2)
		 , eth.name
	  INTO v_date_of_contact, v_status, v_resdate
	     , v_tscode, v_station, v_visn, v_elig, v_is_clin_appeal
		 , v_cc_name, v_comp_name, v_category
		 , v_ssn, v_pseudo, v_sex, v_dob, v_pos, v_icn
		 , v_infoby, v_ethnicity
	  FROM pats.REPORT_OF_CONTACT roc
	    LEFT OUTER JOIN pats.pats_patient pt ON roc.patient_fk = pt.id
		LEFT OUTER JOIN pats.treatment_status ts ON roc.treatment_status_fk = ts.id
		LEFT OUTER JOIN sdsadm.std_institution inst ON roc.institution_fk = inst.id
		LEFT OUTER JOIN sdsadm.std_institution visn ON inst.visn_id = visn.id
		LEFT OUTER JOIN pats.pats_user iby ON roc.info_taken_by_user_fk = iby.id
		LEFT OUTER JOIN pats.congressional_contact cc ON roc.congressional_contact_fk = cc.id
		LEFT OUTER JOIN pats.comps ON roc.comp_fk = comps.id
		LEFT OUTER JOIN sdsadm.std_ethnicity eth ON pt.ethnicity_fk = eth.id
	  WHERE roc.roc_number = p_rocno;
	-- Get patient ssn
	  IF v_pseudo=1 THEN v_ssn := v_ssn||'P';
	  END IF;
	-- Calculate Days to Resolution
	IF v_resdate IS NOT NULL THEN
	  v_days_to_res := TO_DATE(v_resdate,'MMDDYYYY') - TO_DATE(v_date_of_contact,'MMDDYYYY');
	  IF v_days_to_res > 999
	     THEN v_days_to_res := 999;
	  END IF;
	END IF;
	-- Get COMPs information
	IF v_comp_name IS NOT NULL THEN
	  v_compgiven := '1';
	END IF;
	-- If Eligibility Status is null, set it to 'UNK'
	IF v_elig IS NULL THEN
	  v_elig := 'UNK';
	END IF;
	-- If Ethnicity is null, set it to 'UNK'
	IF (v_ethnicity IS NULL) OR (v_ethnicity = 'Unknown') THEN
	  v_ethnicity := 'UNK';
	END IF;
	-- Build output string for ROC Main Data
	v_str := p_rocno||'^ROC^'||v_date_of_contact||'^'||v_status||'^'||v_resdate
	  ||'^'||v_tscode||'^'||v_station||'^'||TO_CHAR(v_days_to_res)||'^'||v_visn
	  ||'^'||v_is_clin_appeal||'^'||v_infoby||'^'||v_cc_name
	  ||'^'||v_compgiven||'^'||v_comp_name||'^';
    -- Update table with ROC main data
    INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
    VALUES (p_rocno, v_str, p_date);
	-- Build output string for Patient Data
	v_str := p_rocno||'^PT^'||v_ssn||'^'||v_sex||'^'||v_dob||'^'||v_pos||'^'
	  ||v_elig||'^'||v_icn||'^'||v_category||'^'||v_ethnicity||'^';
    -- Update table with Patient data
    INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
    VALUES (p_rocno, v_str, p_date);
  EXCEPTION
    WHEN OTHERS THEN
	  RAISE;
  END rocmain;



 -- Build output data for rollup to National Reporting into pats_rollup_natl_data table
 PROCEDURE rollup(p_fromdate IN DATE) -- Date last rollup successfully completed
 IS
    v_rocno VARCHAR2(16);
    CURSOR v_roc_cur IS
	  SELECT roc_number, ver FROM pats.report_of_contact
	    WHERE (status='O')
          OR (date_of_contact >= p_fromdate)
          OR (date_closed >= p_fromdate)
		ORDER BY roc_number;
	CURSOR v_iss_cur IS
	  SELECT DISTINCT iss.issue_code, isscat.issue_category_code, isscat.is_customer_service_standard
	        ,hl.location_name, fsos.service_or_section_name
	    FROM pats.roc_issue rociss
	      LEFT OUTER JOIN pats.issue_code iss ON rociss.issue_code_fk = iss.issue_code
		  LEFT OUTER JOIN pats.issue_category isscat ON iss.issue_category_fk = isscat.issue_category_code
		  LEFT OUTER JOIN pats.hospital_location hl ON hl.id=rociss.hospital_location_fk
		  LEFT OUTER JOIN pats.facility_service_or_section fsos ON fsos.id=rociss.facility_servsect_fk
	    WHERE rociss.roc_fk = v_rocno
		ORDER BY isscat.is_customer_service_standard DESC, isscat.issue_category_code, iss.issue_code
		     ,hl.location_name, fsos.service_or_section_name;
	CURSOR v_ce_cur IS
	  SELECT ce.rollup_code
	    FROM pats.roc_contacting_entity rocce
	      LEFT OUTER JOIN pats.contacting_entity ce ON rocce.contacting_entity_fk = ce.id
	    WHERE rocce.roc_fk = v_rocno
		ORDER BY ce.sort_order;
	CURSOR v_moc_cur IS
	  SELECT moc.rollup_code
	    FROM pats.roc_method_of_contact rocmoc
	      LEFT OUTER JOIN pats.method_of_contact moc ON rocmoc.method_of_contact_fk = moc.id
	    WHERE rocmoc.roc_fk = v_rocno
		ORDER BY moc.sort_order;
	CURSOR v_race_cur IS
	  SELECT race.name
	    FROM pats.report_of_contact roc
		  LEFT OUTER JOIN pats.pats_patient pt ON roc.patient_fk = pt.id
		  LEFT OUTER JOIN pats.pats_patient_race ptrc ON ptrc.patient_fk = pt.id
	      LEFT OUTER JOIN sdsadm.std_race race ON ptrc.patient_race_fk = race.id
	    WHERE roc.roc_number = v_rocno AND race.name IS NOT NULL;
    CURSOR v_rollup_cur IS
	  SELECT DISTINCT roc_number FROM pats.pats_rollup_natl_data;
	v_ver DATE;
	v_str VARCHAR2(1000);
	v_int INTEGER;
	v_date DATE := SYSDATE;
    v_isscode VARCHAR2(5);
    v_isscat VARCHAR2(2);
    v_isscust NUMBER(1,0);
	v_ce_abbr VARCHAR2(2);
	v_moc_abbr VARCHAR2(1);
	v_hl_name VARCHAR2(30);
	v_fsos_name VARCHAR2(50);
	v_race_name VARCHAR2(45);
	v_err VARCHAR2(100);
 BEGIN
    -- Delete data from the previous rollup.
	v_err := 'Rollup of National Report Data failed at the beginning.';
    SELECT COUNT(*) INTO v_int FROM pats_rollup_natl_data;
	IF v_int > 0 THEN
	   v_err := 'Deletion of old data failed - Rollup of National Report Data Incomplete';
	   del;
	END IF;
    -- Read through the list of ROCs and update strings of data for output.
    OPEN v_roc_cur;
	LOOP
	   FETCH v_roc_cur INTO v_rocno, v_ver;
	   EXIT WHEN v_roc_cur%NOTFOUND;
	   -- Make sure ROC has at least one issue code. If not, we won't roll it up.
	   SELECT COUNT(*) INTO v_int FROM roc_issue
	     WHERE roc_fk = v_rocno;
       IF v_int>0 THEN
	     -- ** Build string of ROC main data
	     v_err := 'ROC Main Data for ROC Number '||v_rocno||' Failed - Rollup Incomplete - ';
	     rocmain(v_rocno, v_date);
	     -- ** Read through the list of Issue Codes for the current ROC and update strings of data for output.
	     v_err := 'Error in rollup of Issue Code data for ROC '||v_rocno||' - Rollup Incomplete - ';
         OPEN v_iss_cur;
	     v_str := NULL;
	     LOOP
	        FETCH v_iss_cur INTO v_isscode, v_isscat, v_isscust, v_hl_name, v_fsos_name;
	        EXIT WHEN v_iss_cur%NOTFOUND;
	        -- Insert the previous issue multiple into the rollup table.
	        IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                VALUES (v_rocno, v_str, v_date);
            END IF;
	        -- Build string of ROC issue data
	        v_str := v_rocno||'^ISSC^'||v_isscode||'^'||v_isscat||'^'||v_isscust||'^'||v_hl_name||'^'||v_fsos_name||'^';
	     END LOOP;
	     CLOSE v_iss_cur;
	     -- If there were no issues, set one Issues record.
	     IF v_str IS NULL THEN
	        v_str := v_rocno||'^ISSC^';
	     END IF;
	     -- Insert the last issue multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
	     -- ** Read through the list of Contacting Entities for the current ROC and update strings of data for output.
	     v_err := 'Error in rollup of Contacting Entity data for ROC '||v_rocno||' - Rollup Incomplete - ';
	     v_str := NULL;
	     OPEN v_ce_cur;
	     LOOP
	        FETCH v_ce_cur INTO v_ce_abbr;
	        EXIT WHEN v_ce_cur%NOTFOUND;
	        -- Insert the previous contacting entity multiple into the rollup table.
	        IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
		    -- Set data for the current CE multiple
	        v_str := v_rocno||'^CE^'||v_ce_abbr||'^';
	     END LOOP;
	     CLOSE v_ce_cur;
	     -- If there were no CEs, set one CE record.
	     IF v_str IS NULL THEN
	        v_str := v_rocno||'^CE^';
	     END IF;
         -- Insert the last Contacting Entity multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
	     -- ** Read through the list of Methods of Contact for the current ROC and update strings of data for output.
	     v_err := 'Error in rollup of Method of Contact data for ROC '||v_rocno||' - Rollup Incomplete - ';
	     v_str := NULL;
         OPEN v_moc_cur;
	     LOOP
	        FETCH v_moc_cur INTO v_moc_abbr;
	        EXIT WHEN v_moc_cur%NOTFOUND;
	        -- Insert the previous method of contact multiple into the rollup table.
	        IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
		    -- Set data for the current MOC.
	        v_str := v_rocno||'^MOC^'||v_moc_abbr||'^';
	     END LOOP;
	     CLOSE v_moc_cur;
	     -- If there were no MOCs, set one MOC record.
	     IF v_str IS NULL THEN
	        v_str := v_rocno||'^MOC^';
	     END IF;
         -- Insert the last Method of Contact multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
           VALUES (v_rocno, v_str, v_date);
	     -- ** Read through the list of Patient Races for the current ROC and update strings of data for output.
	     v_err := 'Error in rollup of Patient Race data for ROC '||v_rocno||' - Rollup Incomplete - ';
	     v_str := NULL;
         OPEN v_race_cur;
	     LOOP
	        FETCH v_race_cur INTO v_race_name;
	        EXIT WHEN v_race_cur%NOTFOUND;
		    IF v_race_name = 'Unknown' THEN v_race_name := 'UNK';
		    END IF;
	        -- Insert the previous patient race multiple into the rollup table.
	        IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
		    -- Set data for the current race
	        v_str := v_rocno||'^RACE^'||v_race_name||'^';
	     END LOOP;
	     CLOSE v_race_cur;
	     -- If there were no Patient Races, set one MOC record.
	     IF v_str IS NULL THEN
	        v_str := v_rocno||'^RACE^';
	     END IF;
         -- Insert the last Patient Race multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
         -- Update flag on ROC to indicate that rollup data has been created
	     v_err := 'Error in setting rollup flag on ROC '||v_rocno||' - Rollup Incomplete - ';
	     UPDATE pats.report_of_contact
	        SET rollup_to_natl_reports_status=0
		    WHERE (roc_number = v_rocno)
		      AND (ver = v_ver);
	     COMMIT;
       END IF;
    -- End of ROC Loop
    END LOOP;
	CLOSE v_roc_cur;
	  -- Insert an end-of-message symbol on the last string
	/*v_err := 'Error setting end of message flag on rollup data - Rollup Incomplete - ';
	SELECT MAX(id) INTO v_int FROM pats_rollup_natl_data;
	UPDATE pats_rollup_natl_data
	  SET output_data_string = output_data_string||'#'
	  WHERE id = v_int;*/
	COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
	  ROLLBACK;
	  -- If an error occurred, reset the rollup flag on all ROCs and delete data from rollup file.
      OPEN v_rollup_cur;
	  LOOP
	    FETCH v_rollup_cur INTO v_rocno;
	    EXIT WHEN v_rollup_cur%NOTFOUND;
	    -- Update flag on ROCs to indicate that rollup still needs to be done.
	    UPDATE pats.report_of_contact
	      SET rollup_to_natl_reports_status=1
		  WHERE (roc_number = v_rocno);
	  END LOOP;
	  CLOSE v_rollup_cur;
      COMMIT;
	  -- Delete data from rollup table
	  del;
	  RAISE_APPLICATION_ERROR(-20010, v_err||SQLERRM);
	  RAISE;
END rollup;




 END pkg_repair_rollup_natl_data;
/
DROP PACKAGE BODY PATS.PKG_REPORT_OF_CONTACT;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_REPORT_OF_CONTACT" AS


 -- PRIVATE-- Insert records to the ROC_CONTACTING_ENTITY table
 PROCEDURE set_roc_contacting_entity (p_roc_number IN VARCHAR2
                    , p_ce_list IN VARCHAR2)
 AS
   v_ceid INT := 0;
   v_end INT;
   v_ce_str VARCHAR2(1000) := p_ce_list||',';
 BEGIN
   -- Parse apart "," delimited Contacting Entity Id values and insert contacting entities.
   LOOP
	  -- Extract the next Contacting Entity Id value using the "," delimiter
      v_end := INSTR(v_ce_str,',');
	  v_ceid := TO_NUMBER(SUBSTR(v_ce_str,1,v_end-1));
	  v_ce_str := SUBSTR(v_ce_str,v_end+1);
	  -- If we have a valid CE Id, insert a new record to the roc_contacting_entity table
      IF v_ceid IS NOT NULL THEN
        INSERT INTO pats.roc_contacting_entity
          (roc_fk, contacting_entity_fk)
        VALUES (p_roc_number, v_ceid);
	  -- If the piece we parsed out on the "," is null, we're at the end, so we exit the loop.
	  ELSE
	    EXIT;
      END IF;
   END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20003, 'Report of Contact must have at least one Contacting Entity');
 END set_roc_contacting_entity;


  -- PRIVATE-- Insert records to the roc_method_of_contact table
 PROCEDURE set_roc_method_of_contact (p_roc_number IN VARCHAR2
                    , p_moc_list IN VARCHAR2)
 AS
   v_mocid INT := 0;
   v_end INT;
   v_moc_str VARCHAR2(1000) := p_moc_list||',';
 BEGIN
   -- Parse apart "," delimited Method of Contact Id values and insert methods of contact
    LOOP
	  -- Extract the next Method of Contact Id value using the "," delimiter
      v_end := INSTR(v_moc_str,',');
	  v_mocid := TO_NUMBER(SUBSTR(v_moc_str,1,v_end-1));
	  v_moc_str := SUBSTR(v_moc_str,v_end+1);
	  -- If we have a valid MOC Id, insert a new record to the roc_method_of_contact table
      IF v_mocid IS NOT NULL THEN
        INSERT INTO pats.roc_method_of_contact
           (roc_fk, method_of_contact_fk)
        VALUES (p_roc_number, v_mocid);
	  -- If the piece we parsed out on the ","is null, we're at the end, so we exit the loop.
      ELSE
        EXIT;
      END IF;
   END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR(-20005,'The Methods of Contact List for this Report of Contact had an invalid entry.');
 END set_roc_method_of_contact;


   -- PRIVATE-- Insert records to the roc_phone_fax table
 PROCEDURE set_roc_phone_fax (p_roc_number IN VARCHAR2
                    , p_phone_fax_list IN VARCHAR2)
 AS
  v_end INT;
  v_pf_str VARCHAR2(3999) := p_phone_fax_list||'^';
  v_pfnumber VARCHAR2(50);
  v_pfdesc VARCHAR2(50);
  v_int INT;
 BEGIN
  LOOP
	-- Extract the next phone/fax description, and phone/fax number from the string
     v_end := INSTR(v_pf_str,'^');
	 v_pfdesc := SUBSTR(v_pf_str,1,v_end-1);
	 v_pf_str := SUBSTR(v_pf_str,v_end+1);
     v_end := INSTR(v_pf_str,'^');
	 v_pfnumber := SUBSTR(v_pf_str,1,v_end-1);
	 v_pf_str := SUBSTR(v_pf_str,v_end+1);
	 -- If we have a valid phone/fax number, insert a new record to the roc_method_of_contact table
     IF v_pfnumber IS NOT NULL THEN
       INSERT INTO pats.roc_phone_fax
         (roc_fk, name_or_description, phone_fax_number)
        VALUES (p_roc_number, v_pfdesc, v_pfnumber);
	 -- If the phone/fax number is null, we're at the end, so we exit the loop.
     ELSE
       EXIT;
     END IF;
  END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20005,'The Phone/Fax List for this Report of Contact had an invalid entry.');
 END set_roc_phone_fax;


   -- PRIVATE-- Insert records to the roc_issue table
 PROCEDURE set_roc_issue (p_roc_number IN VARCHAR2
                    , p_issue_list IN VARCHAR2)
 AS
  v_end INT;
  v_str VARCHAR2(3999) := p_issue_list||',';
  v_iss_str VARCHAR2(50);
  v_isscode VARCHAR2(5);
  v_hlid NUMBER(10,0);
  v_fsosid NUMBER(10,0);
  v_empinvid NUMBER(10,0);
  v_int INT;
 BEGIN
  LOOP
	-- Extract the next issue code cluster using the "," delimiter
    v_end := INSTR(v_str,',');
	v_iss_str := SUBSTR(v_str,1,v_end-1)||'^';
	v_str := SUBSTR(v_str,v_end+1);
	-- Now extract the issue code from the issue cluster
	v_end := INSTR(v_iss_str,'^');
	v_isscode := SUBSTR(v_iss_str,1,v_end-1);
	v_iss_str := SUBSTR(v_iss_str,v_end+1);
	-- If the issue code is not null, extract the hospital location, facility servsect and employee involved id values
	--   and insert a record to the roc_issue table.
	IF v_isscode IS NOT NULL THEN
	  v_end := INSTR(v_iss_str,'^');
	  v_hlid := TO_NUMBER(SUBSTR(v_iss_str,1,v_end-1));
	  v_iss_str := SUBSTR(v_iss_str,v_end+1);
	  v_end := INSTR(v_iss_str,'^');
	  v_fsosid := TO_NUMBER(SUBSTR(v_iss_str,1,v_end-1));
	  v_iss_str := SUBSTR(v_iss_str,v_end+1);
	  v_end := INSTR(v_iss_str,'^');
	  v_empinvid := TO_NUMBER(SUBSTR(v_iss_str,1,v_end-1));
	  INSERT INTO roc_issue(roc_fk, issue_code_fk, hospital_location_fk
	               , facility_servsect_fk, employee_involved_fk)
	     VALUES  (p_roc_number, v_isscode, v_hlid, v_fsosid, v_empinvid);
	-- If the issue code is null, we're at the end, so we exit the loop.
    ELSE
	  EXIT;
	END IF;
  END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20005,'The Issues List for this Report of Contact had an invalid entry.');
 END set_roc_issue;



 -- Insert a record to the Report_of_Contact table
 PROCEDURE add_roc (p_date IN DATE
                   , p_infobyuserid IN INT
                   , p_enteredbyuserid IN INT
				   , p_ce_list IN VARCHAR2
                  --, p_ce_list IN pats.id_list -- VARRAY with Ids from contacting_entity table
                   , p_issuetext IN VARCHAR2
                   , p_treatmentstatusid IN INT
                   , 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
                   , p_roc_number OUT VARCHAR2
                   , p_ver OUT VARCHAR2)
 AS
   v_rocno VARCHAR2(16);
   v_date DATE := p_date;
   v_date_overdue DATE;
   v_ceid INT := 0;
   v_end INT;
   v_ce_str VARCHAR2(100) := p_ce_list||',';
   v_eligstat VARCHAR2(30);
   v_category VARCHAR2(30);
   v_sysdate DATE := SYSDATE;
   ex_noadd EXCEPTION;
 BEGIN
   -- If list of contacting entities is null, raise an error.
   IF p_ce_list IS NULL THEN
        RAISE_APPLICATION_ERROR(-20003, 'Report of Contact must have at least one Contacting Entity.');
   END IF;
   -- If no date is passed, Date of Contact will be set to current date in the Servers timezone.
   IF v_date IS NULL THEN v_date := v_sysdate;
   END IF;
   -- Calculate date_overdue
   v_date_overdue := pats.calculate_roc_date_overdue(v_date);
   -- Generate a new ROC number, based on station number, and fiscal year for this date of contact.
   v_rocno := pats.new_roc_number(p_station_no, v_date);
   -- If patient id is not null, get the eligibility_status and category data from pats_patient
   IF p_patientid IS NOT NULL THEN
     SELECT eligibility_code, category INTO v_eligstat, v_category
	 FROM pats_patient
	 WHERE id = p_patientid;
   END IF;
   -- Create a new ROC with just the data required during initial entry.
   INSERT INTO pats.report_of_contact
        (roc_number, date_of_contact, info_taken_by_user_fk
        , issue_text, entered_by_user_fk, patient_fk
        , treatment_status_fk, status, institution_fk
		, date_overdue, is_internal_appeal, eligibility_status
		, category, rollup_to_natl_reports_status, ver)
     VALUES (v_rocno, v_date, p_infobyuserid
        , p_issuetext, p_enteredbyuserid, p_patientid
        , p_treatmentstatusid, 'O', p_inst_id
		, v_date_overdue, 0, v_eligstat
		, v_category, 1, SYSDATE)
     RETURNING roc_number, TO_CHAR(ver,'DD-Mon-YYYY HH24:MI:SS')
	    INTO p_roc_number, p_ver;
   -- Pass Contacting Entity list to routine to add at least one CE.
   set_roc_contacting_entity(p_roc_number, p_ce_list);
   COMMIT;
 EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
	 RAISE;
 END add_roc;



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

 AS
   v_before_verdate DATE := TO_DATE(p_ver,'DD-Mon-YYYY HH24:MI:SS');
   v_after_verdate DATE := NULL;
   v_date_overdue DATE;
   v_index BINARY_INTEGER;
   v_old_patientid INT(10,0);
   v_eligstat VARCHAR2(30);
   v_category VARCHAR2(30);
 BEGIN
   -- If list of contacting entities is null, raise an error.
   IF p_ce_list IS NULL THEN
        RAISE_APPLICATION_ERROR(-20003, 'Report of Contact must have at least one Contacting Entity.');
   END IF;
   -- Recalculate date ROC is overdue
   v_date_overdue := pats.calculate_roc_date_overdue(p_date);
   -- If patient id has changed, get eligibility_status and category for new patient
   IF p_patientid IS NOT NULL THEN
     SELECT patient_fk, eligibility_status, category
	   INTO v_old_patientid, v_eligstat, v_category
       FROM pats.report_of_contact
	   WHERE roc_number = p_roc_number;
	 IF (v_old_patientid IS NULL) OR (v_old_patientid <> p_patientid)
	   THEN
       SELECT eligibility_code, category INTO v_eligstat, v_category
         FROM pats_patient
	     WHERE id = p_patientid;
	 END IF;
   END IF;
   -- Update single-valued fields on the Report Of Contact table.
   UPDATE pats.report_of_contact
   SET date_of_contact = p_date
      , info_taken_by_user_fk = p_infobyuserid
      , issue_text = p_issuetext
      , treatment_status_fk = p_treatmentstatusid
      , patient_fk = p_patientid
      , congressional_contact_fk = p_ccfk
	  , is_internal_appeal = p_is_internal_appeal
	  , comp_fk = p_compid
	  , resolution_text1 = p_resolution_text1
	  , resolution_text2 = p_resolution_text2
	  , date_overdue = v_date_overdue
	  , eligibility_status = v_eligstat
	  , category = v_category
	  , rollup_to_natl_reports_status = 1
      , ver = SYSDATE
   WHERE (roc_number = p_roc_number) AND (ver = v_before_verdate)
   RETURNING ver INTO v_after_verdate;
   -- If main ROC fields updated successfully, update related tables
   IF v_after_verdate IS NOT NULL THEN
      -- Update contacting entity list, deleting existing entries first.
      DELETE FROM pats.roc_contacting_entity
         WHERE roc_fk = p_roc_number;
      set_roc_contacting_entity(p_roc_number, p_ce_list);
      -- Set p_infobyname output parameter to displayable 'info taken by user' name.
      SELECT DISPLAYABLE_NAME(last_name, first_name, middle_name,
				     name_suffix, academic_degree, NULL, 60,2)
		INTO p_infobyname
		FROM pats.pats_user
		WHERE p_infobyuserid = id;
      -- Update Method of Contact List. First delete all the existing entries for the ROC.
      DELETE FROM pats.roc_method_of_contact
         WHERE roc_fk = p_roc_number;
	  set_roc_method_of_contact(p_roc_number, p_moc_list);
      -- Update Phone/Fax List. First delete existing entries for the ROC.
      DELETE FROM pats.roc_phone_fax
         WHERE roc_fk = p_roc_number;
	  set_roc_phone_fax(p_roc_number, p_phone_fax_list);
      -- Update issue code/hospital location/facility service or section/employee involved combinations
      DELETE FROM pats.roc_issue
         WHERE roc_fk = p_roc_number;
	  set_roc_issue(p_roc_number, p_issue_list);
   END IF;
   IF v_after_verdate IS NULL THEN
      ROLLBACK;
	  RAISE_APPLICATION_ERROR(-20000,'This Report of Contact was updated by another user.');
   ELSE
      p_ver := TO_CHAR(v_after_verdate,'DD-Mon-YYYY HH24:MI:SS');
	  COMMIT;
   END IF;
 EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
	 RAISE;
 END upd_roc;



  /* Return all data for the ROC referencec by p_roc_number (ROC Number) parameter */
  PROCEDURE get_roc (p_roc_number IN VARCHAR2
  				   , p_roc_cursor OUT t_cursor
				   , p_ce_cursor OUT t_cursor
				   , p_moc_cursor OUT t_cursor
				   , p_issues_cursor OUT t_cursor
				   , p_pf_cursor OUT t_cursor)
  IS
  BEGIN
    -- Get main ROC data
    OPEN p_roc_cursor FOR
	  SELECT ROC.date_of_contact
	      , ROC.info_taken_by_user_fk
		  , DISPLAYABLE_NAME(IT.last_name, IT.first_name, IT.middle_name,
				     IT.name_suffix, IT.academic_degree, NULL, 60,2) info_taker_name
		  , ROC.entered_by_user_fk
		  , ROC.patient_fk
		  , ROC.eligibility_status
		  , ROC.category
		  , ROC.treatment_status_fk
		  , ROC.congressional_contact_fk
		  , ROC.is_internal_appeal
		  , ROC.issue_text
		  , ROC.status
		  , ROC.comp_fk
		  , comps.comp_name
		  , ROC.resolution_text1
		  , ROC.resolution_text2
		  , ROC.date_closed
		  , ROC.institution_fk
		  , TO_CHAR(ROC.ver,'DD-Mon-YYYY HH24:MI:SS') ver
	   FROM pats.report_of_contact ROC
	     LEFT OUTER JOIN pats_user IT
		   ON info_taken_by_user_fk = IT.id
		 LEFT OUTER JOIN comps
		   ON comp_fk = comps.id
	   WHERE roc_number = p_roc_number;
	 -- Get Contacting Entities
	 OPEN p_ce_cursor FOR
	   SELECT contacting_entity_fk
	   FROM pats.roc_contacting_entity
	   WHERE roc_fk = p_roc_number;
	 -- Get Methods of Contact
	 OPEN p_moc_cursor FOR
	   SELECT method_of_contact_fk
	   FROM pats.roc_method_of_contact
	   WHERE roc_fk = p_roc_number;
	 -- Get Phone/Fax Data
	 OPEN p_pf_cursor FOR
	   SELECT id, name_or_description
	        , phone_fax_number
	   FROM pats.roc_phone_fax
	   WHERE roc_fk = p_roc_number;
	 -- Get Issue Code combinations data
	 OPEN p_issues_cursor FOR
	   SELECT issue_code_fk
	        , hospital_location_fk
			, HL.location_name hospital_location_name
			, facility_servsect_fk
			, FSS.service_or_section_name
	        , ISS.employee_involved_fk
	        , DISPLAYABLE_NAME(E.last_name, E.first_name, E.middle_name,
			     E.name_suffix, E.academic_degree, NULL, 60,2) employee_name
	    FROM pats.roc_issue ISS
		  LEFT OUTER JOIN pats.hospital_location HL
		    ON ISS.hospital_location_fk = HL.Id
		  LEFT OUTER JOIN pats.facility_service_or_section FSS
		    ON ISS.facility_servsect_fk = FSS.Id
		  LEFT OUTER JOIN pats.pats_user E
		    ON ISS.employee_involved_fk = E.Id
		WHERE ISS.roc_fk = p_roc_number
		ORDER BY E.last_name, E.first_name;
  END get_roc;



  /* Return the next p_rowcount ROCs that meet input criteria */
  PROCEDURE list_roc_vlh (p_ocb IN CHAR
                    , p_inst_id IN NUMBER -- foreign key reference to sds.std_institution
 		   			, p_user_identifier IN VARCHAR2
					, p_partial_rocno IN VARCHAR2
					, p_empid IN INT
					, p_startdate IN DATE
					, p_enddate IN DATE
                    , p_rowcount IN INT
                    , p_initial_index IN INT
                    , p_cursor OUT t_cursor
                    , p_total_rowcount OUT INT
                    , p_has_next_resultset OUT BINARY_INTEGER
                    , p_number_of_indexes OUT INT)
 IS
    v_cursor t_cursor;
	v_uid INT;
    v_start INT;
	v_startdate DATE;
    v_startroc VARCHAR(17);
	v_startstatus CHAR(1);
	v_status CHAR(1) := UPPER(p_ocb);
	v_beg_date DATE;
	v_end_date DATE;
 BEGIN
   -- Get id field from pats_user, using KAAJEE User Name as the identifier
   IF p_user_identifier IS NOT NULL THEN
     SELECT id INTO v_uid FROM pats_user
	   WHERE user_identifier = p_user_identifier;
   END IF;
   -- Initialize date range parameters if passed
   IF p_startdate IS NOT NULL THEN
     v_beg_date := TO_DATE(TO_CHAR(p_startdate,'MM/DD/YYYY')||' 00:00:00','MM/DD/YYYY HH24:MI:SS');
   END IF;
   IF p_enddate IS NOT NULL THEN
     v_end_date := TO_DATE(TO_CHAR(p_enddate+1,'MM/DD/YYYY')||' 00:00:00','MM/DD/YYYY HH24:MI:SS');
   END IF;
   -- Initialize v_start to highest row number from the previous index,
   v_start := (p_initial_index - 1) * p_rowcount;
   -- Set p_total_rowcount to the total number of ROCs that meet select criteria.
   IF p_empid IS NULL THEN
     SELECT COUNT(*) INTO p_total_rowcount
   	 FROM pats.report_of_contact
   	       WHERE (institution_fk = p_inst_id)
		     AND ((v_status = 'B') OR (status = v_status))
		     AND ((v_uid IS NULL) OR (info_taken_by_user_fk = v_uid))
		     AND ((p_partial_rocno IS NULL) OR (roc_number LIKE p_partial_rocno||'%'))
			 AND ((v_beg_date IS NULL) OR ((date_of_contact = v_beg_date) OR (date_of_contact > v_beg_date)))
			 AND ((v_end_date IS NULL) OR (date_of_contact < v_end_date));
   ELSE
     SELECT COUNT(*) INTO p_total_rowcount
	 FROM (SELECT DISTINCT ROC.roc_number
   	     FROM pats.report_of_contact ROC
		 JOIN pats.roc_issue ISS
		   ON ISS.roc_fk = ROC.roc_number
   	     WHERE (ROC.institution_fk = p_inst_id)
		   AND ((v_status = 'B') OR (ROC.status = v_status))
		   AND ((v_uid IS NULL) OR (ROC.info_taken_by_user_fk = v_uid))
		   AND (ISS.employee_involved_fk = p_empid));
   END IF;

   -- Establish the starting date of contact and ROC number for the next 'n' entries
   -- Note that since we're sorting in descending order, we use the MIN function.
   IF (v_start > 0) AND (p_empid IS NULL) THEN
     SELECT MIN(date_of_contact), MIN(roc_number)
	 INTO v_startdate, v_startroc
     FROM (SELECT date_of_contact, roc_number FROM pats.report_of_contact
   	       WHERE (institution_fk = p_inst_id)
			 AND ((v_status = 'B') OR (status = v_status))
		     AND ((v_uid IS NULL) OR (info_taken_by_user_fk = v_uid))
			 AND ((p_partial_rocno IS NULL) OR (roc_number LIKE p_partial_rocno||'%'))
			 AND ((v_beg_date IS NULL) OR ((date_of_contact = v_beg_date) OR (date_of_contact > v_beg_date)))
			 AND ((v_end_date IS NULL) OR (date_of_contact < v_end_date))
           ORDER BY date_of_contact DESC, roc_number DESC)
     WHERE ROWNUM <= v_start;
   ELSIF (v_start > 0) THEN
     SELECT MIN(date_of_contact), MIN(roc_number)
	 INTO v_startdate, v_startroc
     FROM (SELECT DISTINCT ROC.date_of_contact, ROC.roc_number
		   FROM pats.report_of_contact ROC
		   JOIN pats.roc_issue ISS
		     ON ISS.roc_fk = ROC.roc_number
   	       WHERE (ROC.institution_fk = p_inst_id)
			 AND ((v_status = 'B') OR (ROC.status = v_status))
		     AND ((v_uid IS NULL) OR (ROC.info_taken_by_user_fk = v_uid))
		     AND (ISS.employee_involved_fk = p_empid)
		     ORDER BY date_of_contact DESC, roc_number DESC)
     WHERE ROWNUM <= v_start;
   END IF;
   -- Select the next p_rowcount Open or Closed ROCs into the cursor
   IF p_empid IS NULL THEN
     OPEN v_cursor FOR
       SELECT roc_number, date_of_contact, pname, status
	     , itext, username, ROWNUM
   	   FROM (SELECT roc_number, date_of_contact
	        , DISPLAYABLE_NAME(P.last_name, P.first_name, P.middle_name,
			     P.name_suffix, P.academic_degree, NULL, 60,2) pname
			, status
			, PATS.ABBR_ISSUE_TEXT(issue_text) itext
			, DISPLAYABLE_NAME(U.last_name, U.first_name, U.middle_name,
			     U.name_suffix, U.academic_degree, NULL, 60,2) username
	 	   FROM pats.report_of_contact ROC
			 LEFT OUTER JOIN pats.pats_patient P
	           ON ROC.patient_fk = P.id
			 LEFT OUTER JOIN pats.pats_user U
			   ON ROC.info_taken_by_user_fk = U.id
   	         WHERE (ROC.institution_fk = p_inst_id)
			   AND ((v_status = 'B') OR (ROC.status = v_status))
		       AND ((v_uid IS NULL) OR (ROC.info_taken_by_user_fk = v_uid))
			   AND ((p_partial_rocno IS NULL) OR (ROC.roc_number LIKE p_partial_rocno||'%'))
			   AND ((v_beg_date IS NULL) OR ((date_of_contact = v_beg_date) OR (date_of_contact > v_beg_date)))
			   AND ((v_end_date IS NULL) OR (date_of_contact < v_end_date))
	 		 ORDER BY date_of_contact DESC, roc_number DESC)
       WHERE ((v_startdate IS NULL) OR (date_of_contact <= v_startdate))
		   AND ((v_startroc IS NULL)
		      OR (date_of_contact < v_startdate)
			  OR ((date_of_contact = v_startdate) AND (roc_number < v_startroc)))
		   AND (ROWNUM <= p_rowcount);
   ELSE
     OPEN v_cursor FOR
       SELECT roc_number, date_of_contact, pname, status
	     , itext, username, ROWNUM
   	   FROM (SELECT DISTINCT ROC.roc_number, ROC.date_of_contact
	        , DISPLAYABLE_NAME(P.last_name, P.first_name, P.middle_name,
			     P.name_suffix, P.academic_degree, NULL, 60,2) pname
			, ROC.status
			, PATS.ABBR_ISSUE_TEXT(issue_text) itext
			, DISPLAYABLE_NAME(U.last_name, U.first_name, U.middle_name,
			     U.name_suffix, U.academic_degree, NULL, 60,2) username
	 	     FROM pats.report_of_contact ROC
			 LEFT OUTER JOIN pats.pats_patient P
	           ON ROC.patient_fk = P.id
			 LEFT OUTER JOIN pats.pats_user U
			   ON ROC.info_taken_by_user_fk = U.id
		     JOIN pats.roc_issue ISS
		       ON ISS.roc_fk = ROC.roc_number
   	         WHERE (ROC.institution_fk = p_inst_id)
			   AND ((v_status = 'B') OR (ROC.status = v_status))
		       AND ((v_uid IS NULL) OR (ROC.info_taken_by_user_fk = v_uid))
		       AND (ISS.employee_involved_fk = p_empid)
	 		 ORDER BY date_of_contact DESC, roc_number DESC)
	    WHERE ((v_startdate IS NULL) OR (date_of_contact <= v_startdate))
		   AND ((v_startroc IS NULL)
		      OR (date_of_contact < v_startdate)
			  OR ((date_of_contact = v_startdate) AND (roc_number < v_startroc)))
		   AND (ROWNUM <= p_rowcount);
   END IF;

  -- Set p_cursor to the selected records in the cursor,
  -- p_number_of_indexes to the total number of blocks of p_rowcount records in the file.
   p_cursor := v_cursor;
   p_number_of_indexes := TRUNC(p_total_rowcount / p_rowcount);
   IF MOD(p_total_rowcount, p_rowcount) > 0
   	    THEN p_number_of_indexes := p_number_of_indexes + 1;
   END IF;
 	-- Set p_has_next_resultset to 1 if there are more records, 0 if this is the last group.
   IF p_initial_index < p_number_of_indexes
   	    THEN p_has_next_resultset := 1;
   ELSE p_has_next_resultset := 0;
   END IF;
 END list_roc_vlh;


 -- Close or Re-Open a ROC.
 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
 IS
   v_before_verdate DATE := TO_DATE(p_ver,'DD-Mon-YYYY HH24:MI:SS');
   v_after_verdate DATE := NULL;
   v_flag CHAR(1) := UPPER(p_flag);
   v_cursor t_cursor;
   v_masid INT;
   v_sysdate DATE := SYSDATE;
 BEGIN
   -- If ROC is closed, date_closed set to server date. If re-opened, date_closed is set to NULL.
   IF (v_flag = 'C') AND (p_date_closed IS NULL) THEN
      p_date_closed := v_sysdate;
   ELSIF v_flag = 'O' THEN
      p_date_closed := NULL;
   END IF;
   UPDATE pats.report_of_contact
	 SET date_closed = p_date_closed
	   , status = v_flag
	   , ver = v_sysdate
   WHERE (roc_number = p_rocno) AND (ver = v_before_verdate)
   RETURNING ver INTO v_after_verdate;
   IF v_after_verdate IS NULL THEN
      ROLLBACK;
	  RAISE_APPLICATION_ERROR(-20000,'This Report of Contact was updated by another user.');
   ELSE
      p_ver := TO_CHAR(v_after_verdate,'DD-Mon-YYYY HH24:MI:SS');
	  -- Delete all of the ARN comments associated with this ROC
	  OPEN v_cursor FOR
	    SELECT id FROM pats.notification_master
		  WHERE roc_fk = p_rocno;
      LOOP
        FETCH v_cursor INTO v_masid;
		EXIT WHEN v_cursor%NOTFOUND;
		UPDATE notification_detail
	       SET message = NULL
		   WHERE notification_master_fk = v_masid;
      END LOOP;
	  COMMIT;
   END IF;
  EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
 END open_close_roc;


 --Delete a ROC
 PROCEDURE delete_roc (p_rocno IN VARCHAR2) -- ROC Number
 IS
 BEGIN
   DELETE FROM report_of_contact
     WHERE roc_number = p_rocno;
 COMMIT;
 END delete_roc;


 -- List ROCs according to input parameters.
 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.
 IS
   v_date_overdue DATE := TO_DATE(SYSDATE+1,'DD-Mon-YYYY');
   v_overdue_flag INT := p_overdue_flag;
   v_patientid INT;
   v_status CHAR(1) := UPPER(p_ocb);
 BEGIN
   IF (v_overdue_flag IS NULL) OR (v_overdue_flag <> 1) THEN
     v_overdue_flag := 0;
   END IF;
   IF (p_patient_ien IS NOT NULL) AND (p_comp_facility_id IS NOT NULL) THEN
     SELECT id INTO v_patientid FROM pats_patient
	   WHERE (institution_fk = p_comp_facility_id)
	     AND (vista_ien = p_patient_ien);
   END IF;
   OPEN p_cursor FOR
   SELECT ROC.roc_number, ROC.date_of_contact
            , DISPLAYABLE_NAME(P.last_name, P.first_name, P.middle_name,
			     P.name_suffix, P.academic_degree, NULL, 60,2) pname
			, ROC.status
			, PATS.ABBR_ISSUE_TEXT(issue_text) itext
			, DISPLAYABLE_NAME(U.last_name, U.first_name, U.middle_name,
			     U.name_suffix, U.academic_degree, NULL, 60,2) username
   FROM pats.report_of_contact ROC
   LEFT OUTER JOIN pats.pats_patient P
	 ON ROC.patient_fk = P.id
   LEFT OUTER JOIN pats.pats_user U
	 ON ROC.info_taken_by_user_fk = U.id
   WHERE (ROC.institution_fk = p_inst_id)
	  AND ((v_status = 'B') OR (ROC.status = v_status))
      AND ((v_patientid IS NULL) OR (ROC.patient_fk = v_patientid))
	  AND ((v_overdue_flag=0) OR (TO_DATE(ROC.date_overdue,'DD-Mon-YYYY') = v_date_overdue))
	  AND ((p_userid IS NULL) OR (ROC.info_taken_by_user_fk = p_userid))
   ORDER BY date_of_contact DESC, roc_number DESC;
 END list_roc_no_vlh;


  /* Return a count of the number of ROCs overdue based on date passed, or current server 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
 IS
   v_count INT;
   v_date DATE := p_date+1;
 BEGIN
   IF v_date IS NULL THEN
     v_date := SYSDATE+1;
   END IF;
   v_date := TO_DATE(TO_CHAR(v_date,'MM/DD/YYYY'),'MM/DD/YYYY');
   SELECT COUNT(*) INTO v_count FROM report_of_contact
     WHERE ((p_inst_id IS NULL) OR (institution_fk = p_inst_id))
	   AND (date_overdue < v_date)
	   AND (status = 'O');
   RETURN v_count;
 END count_overdue;


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 BODY PATS.PKG_ROLLUP_NATL_DATA;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_ROLLUP_NATL_DATA" AS

 -- PRIVATE - Build a string of ROC Main Data
 PROCEDURE rocmain(p_rocno IN VARCHAR2, p_date IN DATE)
 IS
   v_str VARCHAR2(1000);
   v_date_of_contact VARCHAR2(8);
   v_ssn VARCHAR2(10);
   v_pseudo CHAR(1);
   v_sex CHAR(1);
   v_dob VARCHAR2(8);
   v_status CHAR(1);
   v_resdate VARCHAR2(8);
   v_tscode CHAR(1);
   v_pos VARCHAR2(25);
   v_station VARCHAR2(7);
   v_days_to_res NUMBER(10,0) := 0;
   v_elig VARCHAR2(30);
   v_ce VARCHAR2(2);
   v_visn VARCHAR2(2);
   v_is_clin_appeal INTEGER(1,0);
   v_icn VARCHAR2(29);
   v_infoby VARCHAR2(60);
   v_cc_name VARCHAR2(60);
   v_compgiven CHAR(1) := '0';
   v_comp_name VARCHAR2(30);
   v_category VARCHAR2(30);
   v_ethnicity VARCHAR2(30);
  BEGIN
    -- Select data from report_of_contact and referenced tables
    SELECT TO_CHAR(roc.date_of_contact,'MMDDYYYY'), roc.status, TO_CHAR(roc.date_closed,'MMDDYYYY')
      , ts.rollup_code, inst.stationnumber, SUBSTR(visn.vistaname,6,2)
   , roc.eligibility_status, roc.is_internal_appeal
   , cc.office_or_person_name
   , comps.comp_name, roc.category
   , pt.social_security_number, pt.is_pseudo_ssn, pt.gender, TO_CHAR(pt.date_of_birth,'MMDDYYYY')
   , pt.period_of_service, pt.integration_control_number
   , PATS.DISPLAYABLE_NAME(iby.last_name,iby.first_name,iby.middle_name,iby.name_suffix,iby.academic_degree,NULL,60,2)
   , eth.name
   INTO v_date_of_contact, v_status, v_resdate
      , v_tscode, v_station, v_visn, v_elig, v_is_clin_appeal
   , v_cc_name, v_comp_name, v_category
   , v_ssn, v_pseudo, v_sex, v_dob, v_pos, v_icn
   , v_infoby, v_ethnicity
   FROM pats.REPORT_OF_CONTACT roc
     LEFT OUTER JOIN pats.pats_patient pt ON roc.patient_fk = pt.id
  LEFT OUTER JOIN pats.treatment_status ts ON roc.treatment_status_fk = ts.id
  LEFT OUTER JOIN sdsadm.std_institution inst ON roc.institution_fk = inst.id
  LEFT OUTER JOIN sdsadm.std_institution visn ON inst.visn_id = visn.id
  LEFT OUTER JOIN pats.pats_user iby ON roc.info_taken_by_user_fk = iby.id
  LEFT OUTER JOIN pats.congressional_contact cc ON roc.congressional_contact_fk = cc.id
  LEFT OUTER JOIN pats.comps ON roc.comp_fk = comps.id
  LEFT OUTER JOIN sdsadm.std_ethnicity eth ON pt.ethnicity_fk = eth.id
   WHERE roc.roc_number = p_rocno;
 -- Get patient ssn
   IF v_pseudo=1 THEN v_ssn := v_ssn||'P';
   END IF;
 -- Calculate Days to Resolution
 IF v_resdate IS NOT NULL THEN
   v_days_to_res := TO_DATE(v_resdate,'MMDDYYYY') - TO_DATE(v_date_of_contact,'MMDDYYYY');
   IF v_days_to_res > 999
      THEN v_days_to_res := 999;
   END IF;
 END IF;
 -- Get COMPs information
 IF v_comp_name IS NOT NULL THEN
   v_compgiven := '1';
 END IF;
 -- If Eligibility Status is null, set it to 'UNK'
 IF v_elig IS NULL THEN
   v_elig := 'UNK';
 END IF;
 -- If Ethnicity is null, set it to 'UNK'
 IF (v_ethnicity IS NULL) OR (v_ethnicity = 'Unknown') THEN
   v_ethnicity := 'UNK';
 END IF;
 -- Build output string for ROC Main Data
 v_str := p_rocno||'^ROC^'||v_date_of_contact||'^'||v_status||'^'||v_resdate
   ||'^'||v_tscode||'^'||v_station||'^'||TO_CHAR(v_days_to_res)||'^'||v_visn
   ||'^'||v_is_clin_appeal||'^'||v_infoby||'^'||v_cc_name
   ||'^'||v_compgiven||'^'||v_comp_name||'^';
    -- Update table with ROC main data
    INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
    VALUES (p_rocno, v_str, p_date);
 -- Build output string for Patient Data
 v_str := p_rocno||'^PT^'||v_ssn||'^'||v_sex||'^'||v_dob||'^'||v_pos||'^'
   ||v_elig||'^'||v_icn||'^'||v_category||'^'||v_ethnicity||'^';
    -- Update table with Patient data
    INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
    VALUES (p_rocno, v_str, p_date);
  EXCEPTION
    WHEN OTHERS THEN
   RAISE;
  END rocmain;



 -- Build output data for rollup to National Reporting into pats_rollup_natl_data table
 PROCEDURE rollup
 IS
    v_rocno VARCHAR2(16);
    CURSOR v_roc_cur IS
   SELECT roc_number, ver FROM pats.report_of_contact
     WHERE rollup_to_natl_reports_status=1
  ORDER BY roc_number;
 CURSOR v_iss_cur IS
   SELECT DISTINCT rociss.id, iss.issue_code, isscat.issue_category_code, isscat.is_customer_service_standard
         ,hl.location_name, fsos.service_or_section_name
     FROM pats.roc_issue rociss
       LEFT OUTER JOIN pats.issue_code iss ON rociss.issue_code_fk = iss.issue_code
    LEFT OUTER JOIN pats.issue_category isscat ON iss.issue_category_fk = isscat.issue_category_code
    LEFT OUTER JOIN pats.hospital_location hl ON hl.id=rociss.hospital_location_fk
    LEFT OUTER JOIN pats.facility_service_or_section fsos ON fsos.id=rociss.facility_servsect_fk
     WHERE rociss.roc_fk = v_rocno
  ORDER BY isscat.is_customer_service_standard DESC, isscat.issue_category_code, iss.issue_code
       ,hl.location_name, fsos.service_or_section_name;
 CURSOR v_ce_cur IS
   SELECT ce.rollup_code
     FROM pats.roc_contacting_entity rocce
       LEFT OUTER JOIN pats.contacting_entity ce ON rocce.contacting_entity_fk = ce.id
     WHERE rocce.roc_fk = v_rocno
  ORDER BY ce.sort_order;
 CURSOR v_moc_cur IS
   SELECT moc.rollup_code
     FROM pats.roc_method_of_contact rocmoc
       LEFT OUTER JOIN pats.method_of_contact moc ON rocmoc.method_of_contact_fk = moc.id
     WHERE rocmoc.roc_fk = v_rocno
  ORDER BY moc.sort_order;
 CURSOR v_race_cur IS
   SELECT race.name
     FROM pats.report_of_contact roc
    LEFT OUTER JOIN pats.pats_patient pt ON roc.patient_fk = pt.id
    LEFT OUTER JOIN pats.pats_patient_race ptrc ON ptrc.patient_fk = pt.id
       LEFT OUTER JOIN sdsadm.std_race race ON ptrc.patient_race_fk = race.id
     WHERE roc.roc_number = v_rocno AND race.name IS NOT NULL;
    CURSOR v_rollup_cur IS
   SELECT DISTINCT roc_number FROM pats.pats_rollup_natl_data;
 v_ver DATE;
 v_str VARCHAR2(1000);
 v_int INTEGER;
 v_date DATE := SYSDATE;
    v_rocissid VARCHAR(16);
    v_isscode VARCHAR2(5);
    v_isscat VARCHAR2(2);
    v_isscust NUMBER(1,0);
 v_ce_abbr VARCHAR2(2);
 v_moc_abbr VARCHAR2(1);
 v_hl_name VARCHAR2(30);
 v_fsos_name VARCHAR2(50);
 v_race_name VARCHAR2(45);
 v_err VARCHAR2(100);
 BEGIN
    -- Delete data from the previous rollup.
 v_err := 'Rollup of National Report Data failed at the beginning.';
    SELECT COUNT(*) INTO v_int FROM pats_rollup_natl_data;
 IF v_int > 0 THEN
    v_err := 'Deletion of old data failed - Rollup of National Report Data Incomplete';
    del;
 END IF;
    -- Read through the list of ROCs and update strings of data for output.
    OPEN v_roc_cur;
 LOOP
    FETCH v_roc_cur INTO v_rocno, v_ver;
    EXIT WHEN v_roc_cur%NOTFOUND;
    -- Make sure ROC has at least one issue code. If not, we won't roll it up.
    SELECT COUNT(*) INTO v_int FROM roc_issue
    WHERE roc_fk = v_rocno;
      -- ** Build string of ROC main data
      v_err := 'ROC Main Data for ROC Number '||v_rocno||' Failed - Rollup Incomplete - ';
      rocmain(v_rocno, v_date);
      -- ** Read through the list of Issue Codes for the current ROC and update strings of data for output.
      v_err := 'Error in rollup of Issue Code data for ROC '||v_rocno||' - Rollup Incomplete - ';
         OPEN v_iss_cur;
      v_str := NULL;
      LOOP
         FETCH v_iss_cur INTO v_rocissid, v_isscode, v_isscat, v_isscust, v_hl_name, v_fsos_name;
         EXIT WHEN v_iss_cur%NOTFOUND;
         -- Insert the previous issue multiple into the rollup table.
         IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                VALUES (v_rocno, v_str, v_date);
            END IF;
         -- Build string of ROC issue data
                v_str := v_rocno||'^ISSC^'||v_rocissid||'^'||v_isscode||'^'||v_isscat||'^'||v_isscust||'^'||v_hl_name||'^'||v_fsos_name||'^';
      END LOOP;
      CLOSE v_iss_cur;
      -- If there were no issues, set one Issues record.
      IF v_str IS NULL THEN
         v_str := v_rocno||'^ISSC^';
      END IF;
      -- Insert the last issue multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
      -- ** Read through the list of Contacting Entities for the current ROC and update strings of data for output.
      v_err := 'Error in rollup of Contacting Entity data for ROC '||v_rocno||' - Rollup Incomplete - ';
      v_str := NULL;
      OPEN v_ce_cur;
      LOOP
         FETCH v_ce_cur INTO v_ce_abbr;
         EXIT WHEN v_ce_cur%NOTFOUND;
         -- Insert the previous contacting entity multiple into the rollup table.
         IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
      -- Set data for the current CE multiple
         v_str := v_rocno||'^CE^'||v_ce_abbr||'^';
      END LOOP;
      CLOSE v_ce_cur;
      -- If there were no CEs, set one CE record.
      IF v_str IS NULL THEN
         v_str := v_rocno||'^CE^';
      END IF;
         -- Insert the last Contacting Entity multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
      -- ** Read through the list of Methods of Contact for the current ROC and update strings of data for output.
      v_err := 'Error in rollup of Method of Contact data for ROC '||v_rocno||' - Rollup Incomplete - ';
      v_str := NULL;
         OPEN v_moc_cur;
      LOOP
         FETCH v_moc_cur INTO v_moc_abbr;
         EXIT WHEN v_moc_cur%NOTFOUND;
         -- Insert the previous method of contact multiple into the rollup table.
         IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
      -- Set data for the current MOC.
         v_str := v_rocno||'^MOC^'||v_moc_abbr||'^';
      END LOOP;
      CLOSE v_moc_cur;
      -- If there were no MOCs, set one MOC record.
      IF v_str IS NULL THEN
         v_str := v_rocno||'^MOC^';
      END IF;
         -- Insert the last Method of Contact multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
           VALUES (v_rocno, v_str, v_date);
      -- ** Read through the list of Patient Races for the current ROC and update strings of data for output.
      v_err := 'Error in rollup of Patient Race data for ROC '||v_rocno||' - Rollup Incomplete - ';
      v_str := NULL;
         OPEN v_race_cur;
      LOOP
         FETCH v_race_cur INTO v_race_name;
         EXIT WHEN v_race_cur%NOTFOUND;
      IF v_race_name = 'Unknown' THEN v_race_name := 'UNK';
      END IF;
         -- Insert the previous patient race multiple into the rollup table.
         IF v_str IS NOT NULL THEN
              INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
                 VALUES (v_rocno, v_str, v_date);
            END IF;
      -- Set data for the current race
         v_str := v_rocno||'^RACE^'||v_race_name||'^';
      END LOOP;
      CLOSE v_race_cur;
      -- If there were no Patient Races, set one MOC record.
      IF v_str IS NULL THEN
         v_str := v_rocno||'^RACE^';
      END IF;
         -- Insert the last Patient Race multiple for the ROC
         INSERT INTO pats_rollup_natl_data (roc_number, output_data_string, date_generated)
            VALUES (v_rocno, v_str, v_date);
         -- Update flag on ROC to indicate that rollup data has been created
      v_err := 'Error in setting rollup flag on ROC '||v_rocno||' - Rollup Incomplete - ';
      UPDATE pats.report_of_contact
         SET rollup_to_natl_reports_status=0
      WHERE (roc_number = v_rocno)
        AND (ver = v_ver);
      COMMIT;
    -- End of ROC Loop
    END LOOP;
 CLOSE v_roc_cur;
   -- Insert an end-of-message symbol on the last string
 /*v_err := 'Error setting end of message flag on rollup data - Rollup Incomplete - ';
 SELECT MAX(id) INTO v_int FROM pats_rollup_natl_data;
 UPDATE pats_rollup_natl_data
   SET output_data_string = output_data_string||'#'
   WHERE id = v_int;*/
 COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
   ROLLBACK;
   -- If an error occurred, reset the rollup flag on all ROCs and delete data from rollup file.
      OPEN v_rollup_cur;
   LOOP
     FETCH v_rollup_cur INTO v_rocno;
     EXIT WHEN v_rollup_cur%NOTFOUND;
     -- Update flag on ROCs to indicate that rollup still needs to be done.
     UPDATE pats.report_of_contact
       SET rollup_to_natl_reports_status=1
    WHERE (roc_number = v_rocno);
   END LOOP;
   CLOSE v_rollup_cur;
      COMMIT;
   -- Delete data from rollup table
   del;
   RAISE_APPLICATION_ERROR(-20010, v_err||SQLERRM);
   RAISE;
END rollup;


 -- Delete data from rollup file pats_rollup_natl_data
 PROCEDURE del
 IS
 BEGIN
   -- Reset the sequence number
   EXECUTE IMMEDIATE 'DROP SEQUENCE pats_rollup_to_natl_seq';
   EXECUTE IMMEDIATE 'TRUNCATE TABLE pats_rollup_natl_data';
   EXECUTE IMMEDIATE 'CREATE SEQUENCE pats_rollup_to_natl_seq START WITH 1 MAXVALUE 1E+28  MINVALUE 1  NOCYCLE  NOCACHE  NOORDER';
 END del;


 END PKG_ROLLUP_NATL_DATA;
/
DROP PACKAGE BODY PATS.PKG_TREATMENT_STATUS;

CREATE OR REPLACE PACKAGE BODY PATS."PKG_TREATMENT_STATUS" AS

 -- Return a list of all treatment statuses, p_aib='A' (active), 'I' (inactive), 'B' (both)
 PROCEDURE list_ts (p_aib IN CHAR
                  , p_cursor OUT t_cursor)
 IS
   v_cursor t_cursor;
 BEGIN
   IF UPPER(p_aib) = 'A' THEN
     OPEN v_cursor FOR
       SELECT id, treatment_status, rollup_code, inactivation_date, sort_order
        FROM pats.treatment_status
        WHERE inactivation_date IS NULL
	     ORDER BY sort_order;
   ELSIF UPPER(p_aib) = 'I' THEN
     OPEN v_cursor FOR
       SELECT id, treatment_status, rollup_code, inactivation_date, sort_order
        FROM pats.treatment_status
        WHERE inactivation_date IS NOT NULL
	     ORDER BY sort_order;
   ELSE
     OPEN v_cursor FOR
       SELECT id, treatment_status, rollup_code, inactivation_date, sort_order
        FROM pats.treatment_status
	     ORDER BY sort_order;
	 END IF;
   p_cursor := v_cursor;
 END list_ts;


 -- Return a single treatment status
 FUNCTION get_ts (p_id IN NUMBER)
 RETURN t_cursor
 IS
   v_cursor t_cursor;
 BEGIN
   OPEN v_cursor FOR
     SELECT treatment_status, rollup_code, inactivation_date
              , sort_order
	      , TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') "ver"
	FROM pats.treatment_status
	WHERE id = p_id;
   RETURN v_cursor;
 END get_ts;


  /* 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 -- Return Id of new entry.
				   , p_ver OUT VARCHAR2) -- rowversion of new row.
  IS
    v_id INT(10,0);
  BEGIN
    -- If caller didn't pass a sort order, assign the next available number
    IF p_sort_order IS NULL THEN
	  SELECT MAX(sort_order) INTO p_sort_order
	    FROM treatment_status;
	  p_sort_order := p_sort_order + 1;
	-- Otherwise, if the passed sort order is already in use, increment the sort order
	--   on the records above the passed sort order.
	ELSE
	  UPDATE treatment_status
	    SET sort_order = sort_order + 1
	    WHERE sort_order >= p_sort_order;
	END IF;
    INSERT INTO TREATMENT_STATUS (treatment_status
		   						 , rollup_code
								 , sort_order
								 , ver)
	VALUES (p_ts_name, p_rollup_code, p_sort_order, SYSDATE)
	RETURNING id, TO_CHAR(ver, 'DD-Mon-YYYY HH24:MI:SS') INTO p_id, p_ver;
	COMMIT;
  END add_ts;


   /* 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);
  IS
    v_before_verdate DATE := TO_DATE(p_ver, 'DD-Mon-YYYY HH24:MI:SS');
	v_after_verdate DATE := NULL;
    v_nullparams BINARY_INTEGER := 0;
    v_idcount NUMBER(5,0);
	v_sysdate DATE := SYSDATE;
  BEGIN
    -- Set a variable that we can use later on to determine whether update failed due to null params.
    IF (p_ts_name IS NULL) AND (p_is_active IS NULL)
       THEN v_nullparams := 1;
    END IF;
	-- Update the name and sort order
    IF (p_ts_name IS NOT NULL)
	THEN
      UPDATE TREATMENT_STATUS
	  SET treatment_status = p_ts_name
	    , rollup_code = p_rollup_code
		, ver = v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	  v_before_verdate := v_after_verdate;
	END IF;
	-- Reactivate or Inactivate a record
	IF p_is_active = 1
	THEN
	  UPDATE TREATMENT_STATUS
	  SET inactivation_date = NULL, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	ELSIF p_is_active = 0
	THEN
	  UPDATE TREATMENT_STATUS
	  SET inactivation_date = v_sysdate, ver=v_sysdate
	  WHERE (id=p_id) and (ver=v_before_verdate)
	  RETURNING ver INTO v_after_verdate;
	END IF;
  -- Determine whether a valid id was passed. If not, or if id is null, raise an error.
    IF p_id IS NOT NULL THEN
        SELECT COUNT(*) INTO v_idcount FROM treatment_status WHERE id=p_id;
    END IF;
    IF (p_id IS NULL) OR (v_idcount=0) THEN
        RAISE_APPLICATION_ERROR(-20001,'This Treatment Status does not exist.');
	    ROLLBACK;
    -- If update failed for reasons other than a data problem, raise optimistic concurrency error.
    ELSIF (v_nullparams = 0) AND (v_after_verdate IS NULL) THEN
        RAISE_APPLICATION_ERROR(-20000,'This Treatment Status was updated by another user. Please make your changes again.');
	    ROLLBACK;
    -- If update was successful, set new ROWVERSION, COMMIT data.
    ELSE
        p_ver := TO_CHAR(v_after_verdate, 'DD-Mon-YYYY HH24:MI:SS');
	    COMMIT;
    END IF;
  END upd_ts;



  /* 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.
  IS
   v_str1 VARCHAR2(1000) := p_sortorder_list||'^';
   v_str2 VARCHAR2(30);
   v_id INT;
   v_sortorder INT;
   v_end INT;
  BEGIN
   -- Update the sort_order field for all treatment_status in the table
   LOOP
	  -- Extract the next Treatment Status Id,Sort Order pair on the "^" delimiter
      v_end := INSTR(v_str1,'^');
	  v_str2 := SUBSTR(v_str1,1,v_end-1);
	  v_str1 := SUBSTR(v_str1,v_end+1);
	  -- If not at the end, parse apart the comma delimited TS Id and Sort Order
      IF v_str2 IS NOT NULL THEN
	     v_end := INSTR(v_str2,',');
	     v_id := TO_NUMBER(SUBSTR(v_str2,1,v_end-1));
	     v_sortorder := TO_NUMBER(SUBSTR(v_str2,v_end+1));
	    -- Update the Sort Order field for this Treatment Status record.
	     UPDATE treatment_status
	       SET sort_order = v_sortorder
	       WHERE id = v_id;
	  -- If the piece we parsed out on the "^" is null, we're at the end, so we exit the loop.
	  ELSE
	    EXIT;
      END IF;
    END LOOP;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
	  ROLLBACK;
	  RAISE_APPLICATION_ERROR(-20008,'The new sort sequence for the Treatment Status was not updated');
 END upd_sortorder_list;


END pkg_treatment_status;
/


GRANT EXECUTE ON PATS.PKG_TREATMENT_STATUS TO PATSUSER_ROLE;
