Pats 0 Systems Management Guide

5.10 Techniques used in PATS Procedures

Technique	Description
Packages	Packages allow for the definition of package level output cursors that can be referenced as result sets (arrays) in Java Code. Within a package, a developer can define private procedures that can be called from within the package, but can’t be referenced from outside the package. Packages allow code used to maintain a single table or related tables to be consolidated.
Result Sets	REF Cursor datatypes are used to return Result Sets (arrays of data records). These REF Cursors are always defined within the Package Specification rather than within the Package Body, so that they are public and can be referenced by the Java Code. Any package that contains either list or get procedures contains code in the Package Specification that looks something like: TYPE t_cursor IS REF CURSOR.
Optimistic Concurrency	In order to maintain optimistic concurrency, any table whose entries might be edited by more than one user simultaneously, has a date field named VER. When a record is selected for update, the VER field is converted to VARCHAR2 and returned with the other data. When calling the update procedure, the calling routine must pass the VER field, along with the data to be updated. VER is converted back to a date, then is compared against the VER field in the record to be updated. If VER has not changed, no other user has updated the record since it was selected, so the update is done. Otherwise, the update does not occur and an error is displayed: “This table_name was updated by another user. Please make your changes again.”
Value List Handler Pattern	The value list handler pattern is used for any of the procedures that return lists of data that might be large. This pattern allows the user to specify that they want to see the next n records. The input parameters always include the number of records to return n and which set of records to return. Output parameters help the caller determine how many sets of n records there are on the table.

5.11 Scheduled Jobs

Job	Description
PATS.BUILD_ROLLUP_DATA_JOB	Scheduled to run once weekly. This job builds the rollup data which the Austin VSSC fetches once weekly in order to create the national VSSC reports. This job calls the pats.pkg_rollup_natl_data.rollup procedure described above. More details can be found in Chapter 7, Rollup PATS Data to VSSC.
PATSRPTS.BUILD_ROC_ADHOC_DATA_JOB	Scheduled to run once daily at 3:00 am, based on the time zone of the server where the Oracle database resides (EHS can adjust the time). This job builds the data that underlies the WEBI Universe used for ad hoc reporting. The job calls the patsrpts.build_roc_adhoc_ data procedure described above.
PATSRPTS.BLD_STD_REPORT_DATA_JOB	Scheduled to run once daily at 3:30 am, based on the time zone of the server where the Oracle database resides (HSI can adjust the time). This job builds the data that underlies standard reporting. The job calls the package entry point patsrpts.Pkg_bld_std_report_data.bldall described above.

5.12 Developer Workstation Setup

TOAD Professional version 8.5.3.2, with DBA Module and Knowledge Expert – This is from Quest Software. Used for table and other Oracle entity maintenance, to maintain stored procedures and to execute SQL code. The DBA module is used to build the scripts to deploy the Oracle part of the PATS application. You can also do debugging with this tool (set breakpoints and watchpoints, look at the value of local variables, etc.) TOAD can be used by a DBA to manage the Oracle database.
The Quest Knowledge Expert gives help on PL/SQL syntax, and on interpreting and correcting Oracle database errors.
The database developer for PATS has found the Quest tools to be intuitive and easy to use.

Quest offers classes and their support is excellent!

Oracle Enterprise Manager Console – This comes with the Oracle Installation. This is mainly a tool for the DBA. The database developer for PATS used it for managing tablespaces, adding new users and assigning them privileges.

5.13 External Relations

Standard Data Services (SDS): PATS has subscribed to the SDS group to load their standard data tables into the SDSADM schema on the PATS Oracle database. The data will automatically be refreshed by the SDS application. PATS uses supported SDS APIs to extract data from the tables as needed. For reporting and weekly rollup to the VSSC system in Austin, PATS creates local tables and uses joins with the SDS tables in order to extract data into the tables. Because PATS is accessing the SDS tables directly rather than using the supported calls to retrieve the data in this case, integration agreements have been requested and granted for the reporting and rollup needs. The tables used are std_institution, std_race and std_ethnicity.

5.14 Database Integration Agreements

PATS has the following Private integration agreements with SDS (Custodial package term).

Integration Agreement	Description
4887 – Use of STD_INSTITUTION table in PATS application	Describes PATS foreign key constraints referencing the std_institution table and granting of REFERENCES and SELECT privileges to PATS users. Also describes how PATS uses the data in the procedure for rolling data up to Austin to support the VSSC reports and in building data to support local reporting. Columns used are listed and described.
4888 – Use of STD_INSTITUTION table in PATS Data Migration	Describes how PATS uses data migrated from the VistA system to look up values in the std_institution table and populate foreign key values in the PATS tables. Describes how the contingency option to delete all data for an institution uses std_institution data. Columns used are listed and described.
4889 – Use of STD_RACE table in PATS application	Describes PATS foreign key constraint in the pats_patient_race table, which references the std_race table. Also describes how PATS uses the data in the procedure for rolling data up to Austin to support the VSSC reports. Columns used are listed and described.
4890 – Use of STD_ETHNICITY table in PATS application	Describes PATS foreign key constraint in the pats_patient table, which references the std_ethnicity table. Also describes how PATS uses the data in the procedure for rolling data up to Austin to support the VSSC reports. Columns used are listed and described.
4891 – Use of STD_RACE table in PATS Data Migration	Describes how PATS uses the HL7 VALUE field from the VistA system to look up values in the std_race table and populate foreign key values in the pats_patient_race table. Columns used are listed and described.
4892 – Use of STD_ETHNICITY table in PATS Data Migration	Describes how PATS uses the ABBREVIATION field from the VistA system to look up values in the std_ethnicity table and populate foreign key values in the pats_patient table. Columns used are listed and described.