Pats 0 Systems Management Guide

5.4 Roles

Role	Description
PATSUSER_ROLE	Controls access to objects used by the PATS application. This is granted to the PATSUSER user. This role is granted EXECUTE privileges on all of the packages in the PATS schema except for those used during migration of legacy data. This role is also granted EXECUTE privileges on the pkg_list (used in standard reports) and set_ad_hoc_user (used in ad hoc reporting) packages in the PATSRPTS schema.
PATSHOST_ROLE	Controls access to objects in the PATS schema used for Data Migration. This is granted to the PATSHOST user. This role is granted EXECUTE privileges on all of the packages and functions used to migrate legacy data into PATS. It is also granted select, insert, update and delete access on the DMLOG table used to track data migration errors.

5.5 Tablespaces

Tablespace	Description
PATS_DATA	Used to store tables and data in the PATS schema
PATS_NDX	Used to store indices on tables in the PATS schema
PATSRPTS_DATA	Used to store tables and data in the PATSRPTS schema
PATSRPTS_NDX	Used to store indices on tables in the PATSRPTS schema

5.6 Tables

5.6.1 PATS Schema

All of the data tables used in the PATS application are in the PATS schema. Refer to the data model in the PATS Software Architecture Document, located in the TSPR.

Table Name	Description
BOILERPLATE_RESOLUTION_TEXT	Contains standard boilerplate text that can be used to describe the resolution for a given issue code. Foreign keys reference the sdsadm.std_institution table, and the issue_code table. The institution_fk field references the division for which the entry was created; an entry can only be selected or edited when a user is logged on under that division.

Table Name	Description
COMPS	Contains a list of comps that can be given to a patient as part of the resolution of a ROC. A foreign key references the sdsadm.std_institution table. The institution_fk field references the division for which the entry was created; an entry can only be selected or edited when a user is logged on under that division.
CONGRESSIONAL_CONTACT	Contains a list of names of congressional persons or offices that can be selected when the contacting entity on a ROC is set to congressional. A foreign key references the sdsadm.std_institution table. The institution_fk field references the computing facility; an entry entered for one division can be selected or edited by all divisions within the facility.
CONTACTING_ENTITY	Contains a nationally managed list of contacting entities used to categorize the entity that contacted the patient advocate and caused a ROC to be created. Examples are: patient, relative, friend.
DMLOG	Used during migration of legacy data to track errors.
FACILITY_SERVICE_OR_SECTION	Contains a list of facility service or sections defined at the VISN level. This is a way of tracking standard organizational entities within every medical center in a division. An FSOS is entered as part of the issue code cluster for a ROC. A foreign key references the sdsadm.std_institution table for the VISN that owns the entry; an entry entered for one division can be selected or edited by a VISN-level user logging on to any division within the same VISN.
HOSPITAL_LOCATION	Contains a list of physical locations within a medical center. A hospital location can be entered as part of the issue code cluster for a ROC. A foreign key references the sdsadm.std_institution table. The institution_fk field references the division for which the entry was created; an entry can only be selected or edited when a user is logged on under that division. This table is not related to the HOSPITAL LOCATION file in VistA.
ISSUE_CATEGORY	Contains a nationally maintained list of codes used to group types of issues for reporting. Each issue code falls within a single issue category.
Table Name	Description
ISSUE_CODE	Contains a nationally maintained list of codes used to categorize the types of complaints or compliments on ROCs for reporting. An issue code is entered as part of the issue code cluster for a ROC. A foreign key references the issue_category table.
METHOD_OF_CONTACT	Contains a nationally managed list of ways a person can report a complaint or compliment that causes a ROC to be created. Examples are: phone, letter, visit.
NATIONAL_PATS_PARAMETERS	This nationally maintained table contains just one record. It holds the value of national parameters that control the behavior of PATS.
NOTIFICATION_DETAIL	This contains dialogue between the patient advocate and an employee when an Action Request Notification (ARN) is sent to the employee. Foreign keys reference the associated notification_master record, and the pats_user table entries for both the sender and recipient of each message.
NOTIFICATION_MASTER	This contains information about a single Action Request Notification (ARN) or Information Notification (IN) sent from a patient advocate to an employee. Foreign keys reference the associated ROC in the report_of_contact table, and the pats_user table for both the originating PA and the employee who received the notification.
PATS_PATIENT	This contains a list of patients who are involved in at least one ROC. It contains minimal demographics information about each patient used for reporting and identification. An entry on this table can be uniquely identified either by a combination of the institution and the VistA IEN (internal entry number), or by the Patient ICN. PATS users are not allowed to edit this table. This table is populated using data returned by Person Service methods. Foreign keys reference the sdsadm.std_institution table and the sdsadm.std_ethnicity table. The institution_fk field references the default institution.

Table Name	Description
PATS_PATIENT_RACE	This contains a list of the race values associated with a patient. This table is refreshed along with the pats_patient table above. Foreign keys reference the pats_patient table, and the sdsadm.std_race table.
PATS_ROLLUP_NATL_DATA	This table contains data that is ready to be rolled up to the VSSC system in Austin for national reporting. Every week a scheduled job runs. The old data in the table is deleted and new data is inserted, from the report_of_contact table and other data, for every ROC that has been entered or edited since the previous week. The selection of which ROCs to rollup is based on the rollup_to_natl_reports_status flag on the report_of_contact table entries.
PATS_USER	This contains a list of all users who have signed on to the PATS application, persons who are employees involved in a ROC, or persons who have been sent a notification. The table has an id field that is the primary key, but there is also a field user_identifier that contains the KAAJEE user identifier. This identifier contains the station number and the VistA IEN from the NEW PERSON file. There is a foreign key referencing the sdsadm.std_institution table. The parent_institution_fk field references the default institution.
REPORT_OF_CONTACT	This is the table that contains the main record of a complaint or compliment and its resolution. It contains foreign keys referencing the pats_user table, for both the information taker for the ROC and the person who entered the ROC. Foreign keys reference the congressional_contact, treatment_status, pats_patient and sdsadm.std_institution tables. The institution_fk field references the division for which the entry was created; an entry can only be selected or edited when a user is logged on under that division.
ROC_CONTACTING_ENTITY	This table contains the contacting entity(s) associated with a ROC. Foreign keys reference the report_of_contact and contacting_entity tables.

Table Name	Description
ROC_ISSUE	This table contains the issue cluster(s) associated with a ROC. Foreign keys reference the report_of_contact, issue_code, hospital_location, facility_service_or_section and pats_user (employee involved in ROC) tables.
ROC_METHOD_OF_CONTACT	This table contains the method(s) of contact associated with a ROC. Foreign keys reference the report_of_contact and method_of_contact tables.
ROC_PHONE_FAX	This table contains phone/fax information associated with a ROC. A foreign key references the report_of_contact table.
TREATMENT_STATUS	Contains a nationally managed list of values describing the treatment status of a patient. Examples are: inpatient, outpatient, long term care.
PAD_LOCATION	This table is a static lookup reference table used by the PAD servlet to map incoming location names to PATS institution/station names.

5.6.2 PATSRPTS Schema

Separate tables are created and populated to support both standard and ad hoc reporting in PATS. The data in the tables is refreshed from the production tables once daily using Oracle scheduled jobs.

Table Name	Description
CONTACTING_ENTITY_VIEW	Supports standard reports. Contains a list of all Contacting Entities. Created so that all contacting entities can be reported on spreadsheet reports, not just those that are referenced by ROCs.
ISSUE_CATEGORY_VIEW	Supports standard reports. Contains a list of all Issue Categories. Created so that all Issue Categories can be reported on the spreadsheet reports, not just those that are referenced by ROCs.
ISSUE_CODE_VIEW	Supports standard reports. Contains a list of all Issue Codes. Created so that all Issue Codes can be reported on the spreadsheet reports, not just those that are referenced by ROCs.
METHOD_OF_CONTACT_VIEW	Supports standard reports. Contains a list of all Methods of Contact. Created so that all Methods of Contact can be reported on the spreadsheet reports, not just those that are referenced by ROCs.

Table Name	Description
ROC_COMBO_DATA_VIEW	Supports ad hoc reporting. Contains the data used to support the WEBI Universe used for Ad Hoc reporting.
ROC_CONTACTING_ENTITY_VIEW	Supports standard reports. Contains all contacting entity data associated with ROCs in the ROC_MAIN_DATA_VIEW.
ROC_ISSUE_DATA_VIEW	Supports standard reports. Contains all issue cluster data associated with ROCs in the ROC_MAIN_DATA_VIEW.
ROC_MAIN_DATA_VIEW	Supports standard reports. Contains all ROC data for ROCs with dates of contact in the current or prior two fiscal years.
ROC_METHOD_OF_CONTACT_VIEW	Supports standard reports. Contains all method of contact data associated with ROCs in the ROC_MAIN_DATA_VIEW.
ROC_NOTIFICATION_VIEW	Supports standard reports. Contains all notification data associated with ROCs in the ROC_MAIN_DATA_VIEW.
ROC_PHONE_DATA_VIEW	Supports standard reports. Contains all phone/fax data associated with ROCs in the ROC_MAIN_DATA_VIEW.
TREATMENT_STATUS_VIEW	Supports standard reports. Contains a list of all Treatment Statuses. Created so that all Treatment Statuses can be reported on the spreadsheet reports, not just those that are referenced by ROCs.
USER_STATION_LIST_FOR_AD_HOC	Supports ad hoc reporting. Contains a list of station numbers and users within that station. When a user signs on to the PATS application, a procedure is called that replaces all current entries for that user matching on KAAJEE user identifier, with a current list of accessible station number(s) for that user. This is used to filter the data during ad hoc reporting so that the user can access only data for their station(s).