This document provides a detailed description of the interface that provides the Court Process Reporting System (CPRS) with case information for Georgia SHINES children in open INV and ONG stages.

The CPRS INV and ONG Stage Data Interface is used to provide information to CPRS for Principal children (Persons under the age of 18) in open INV and ONG stages within SHINES, for the purposes of recognizing children who are later listed on Court Orders or Court Appointed Special Advocates (CASA) Court Reports received by CPRS and sent to GA SHINES.

Georgia SHINES does not push any information to CPRS; CPRS always pulls (requests) information from Georgia SHINES using a series of web services that are created.

The following high-level scenarios are used to determine when to assess whether case information is provided to CPRS for children in open INV and ONG stages:

New INV or ONG stage is opened or reopened

New Person added to an open INV or ONG stage

Changes to the Legal County, Case County, First Name, Middle Name, Last Name, Suffix, Date of Birth, Gender, Person Id, Name of Primary Case Manager for the Stage (First Name, Middle Name or Last Name) or Case Id for a child whose initial data has sent across the interface

Person is removed from an open INV or ONG stage

Person Merge occurs

Case Merge occurs

Case Split occurs

INV or ONG stage is closed

A Person Split will not cause a change to the data that is sent to CPRS. When a split occurs, the original closed person is not automatically added to the stage.

When any of the above scenarios occur, an OUTBOUND table is updated with case data for the children and/or Primary Case Manager in question. The following information is stored within the OUTBOUND table, and is provided to CPRS by GA SHINES for Principal children in open INV and ONG stages:

Legal County

Case County

First Name (child or Primary Case Manager for the Stage)

Middle Name (child or Primary Case Manager for the Stage)

Last Name (child or Primary Case Manager for the Stage)

Date of Birth

Gender

Suffix (child of the Stage)

Person Id (child or Primary Case Manager for the Stage)

Stage Type (e.g. INV or ONG)

Stage Id

Case Id

Stage Closure Indicator (e.g. ‘Y’ or ‘N’)

Date Stage Closed

Case Merge/Split Indicator

Old Case Id

Merge or Split Date

Person Merge Indicator

New Person Id

Case Manager Indicator

Date Processed

The Date Last Update and Date Created are stored in the OUTBOUND table, but are not provided to CPRS.

The CPRS system invokes a web service (a “request”), that is part of this interface, to provide an XML-formatted request for cases that have been updated for a given County and date range. This interface queries the OUTBOUND table to retrieve the Case Ids that match the criteria that CPRS has specified and returns them to CPRS as part of an XML-formatted “response”. CPRS then invokes another web service (a “request”), which is part of this interface, passing in an XML-formatted list of the Case Ids received from the earlier request. This interface queries the OUTBOUND table to retrieve the entries for the given Case Id values that CPRS has specified and returns the aforementioned data to CPRS as part of an XML-formatted “response”. When any one of the aforementioned fields is modified then _all _of the aforementioned data is provided to CPRS.

If a Principal under the age of 18 is involved in multiple, open INV or ONG stages then his/her information will be sent across the interface multiple times – once for each open stage in which they’re involved.

Once the child has been removed from the stage, by clicking the ‘Delete’ button on the Person Detail page within the stage, then his/her case and person updates are no longer sent across this interface to CPRS.

Once the child turns 18, his/her case and person updates are sent across the interface to CPRS for no more than six months after the child’s date of birth indicates that they are 18 years old.

If a child meets the initial criteria for having his/her information sent (Principal under age 18 in an open INV or ONG stage) but later becomes a Collateral, then his/her case and person updates will continue to be sent across the interface.

If a Collateral under the age of 18 involved in an open INV or ONG stage later becomes a Principal, then he/she becomes eligible to be sent across the interface.

Legal Status values can be entered within any stage of a case and are case-specific (NOT stage-specific). Even though a Legal Status is entered into a single stage, it applies to all stages within the case. Therefore the most recent Legal Status, in terms of Effective Date, within the case is what is sent across the interface.

Once the INV or ONG stage closes, case and person updates for children within that stage are no longer sent across the interface to CPRS.

At the point at which these enhancements are deployed, the OUTBOUND table is pre-loaded with Person case data and information for the current open INV and ONG stages.

Figure 1: New INV or ONG Stage is Opened or Reopened

Once a new INV or ONG stage is opened, in SHINES, the stage is assessed to determine if there are Principals that meet the criteria to be sent across the interface to CPRS. Principals that meet the criteria are inserted into the OUTBOUND table. When a stage is reopened, if an entry matching the Person/Stage/Case already exists, then the ‘Stage Closed’ indicator on the entry in the OUTBOUND table is removed.

Figure 2: __Person Data Updated in an Open INV or ONG Stage

When there are updates to Persons within an open INV or ONG stage, the updates are assessed to determine if there is existing information within the OUTBOUND that needs to be updated or if there are new entries to be inserted into the OUTBOUND table. Once the Person turns 18, updates continue to be sent across the CPRS interface for no more than six months after his/her 18th birthday.Figure 3: __Person Added to an Open INV or ONG Stage

When a new Person is added to an open INV or ONG stage, they are assessed to determine if they meet the criteria to be sent across the interface to CPRS. Principals that meet the criteria are inserted into the OUTBOUND table.Figure 4: __Person Removed from an Open INV or ONG Stage

When a Person is removed from an open INV or ONG stage, by clicking the ‘Delete’ button on the Person Detail page within the stage,their his/her entry is removed from the OUTBOUND table so that his/her information is no longer sent across the interface to CPRS. Figure 5: __Person Merge Occurs

When a Person merge occurs, the entry of the “closed” Person, if it exists, is modified within the OUTBOUND table. The value for the “New Person Id” field is added to the entry of the “forward” Person, and the Person Merge Indicator value of ‘Y’ is added. If the “to forward” Person is not already in the OUTBOUND table then they are treated like a Person Add and they are assessed to determine if they should be added to the OUTBOUND table. If the “forward” Person is already in the OUTBOUND table, then no additional changes are made. Any Person merges that occur before the Person exists in the OUTBOUND table are not reflected within the OUTBOUND table.[.line-through]# #Figure 6: Person Split Occurs

When a Person split occurs, there are no further changes made to the OUTBOUND table. The Person in question needs to be related back into the INV or ONG stage in order for their data to be sent across the interface.Figure 7: __Case Merge Occurs

When a Case Merge occurs, the “From” and “To” cases are assessed to determine if they contain an open INV or ONG stage. If so, determine if the stage in the “From” Case will be closed by the merge. If the “From” stage is to be closed, mark all records in the OUTBOUND table for the stage as closed due to merge, and add all persons that should be sent across the interface to CPRS to the corresponding “To” Stage. Instead of updating the existing records when the new stage when a merge occurs, the existing records are marked as "closed due to merge" and new records are added for the new stage (for historical purposes). If there are children in the “From” case that are not in the open stage of the “To” case, then the entry for this Person will be marked as closed due to merge. If the “From” Stage will remain open, then update the Case Id and merge fields for all of the Persons in the OUTBOUND table on the stage. Figure 8: __Case Split Occurs

When a Case Split occurs, each of the “To” cases is assessed to determine if they contain open INV or ONG stages. If so, then each of the Persons in the INV or ONG stage is assessed to determine if they should be sent across the interface to CPRS. Persons in the INV or ONG stage of the “To” case who are not already in the OUTBOUND table are added with the new case information (it is very unlikely/rare that we would run into a scenario where a Person in the “To” case qualifies to be in the OUTBOUND table but is not already there), while Persons in the INV or ONG stage of the “To” case who are already in the OUTBOUND are updated with the new case values. It may be possible that persons involved in a stage that was closed due to a merge were never added to the OUTBOUND table. Figure 9: INV or ONG Stage is __Closed

When an INV or ONG stage has been closed then update the stage closure indicator and Stage Close Date within the OUTBOUND table.

None

None

N/A

Attached is a spreadsheet that lists the data elements passed to CPRS. This document also includes the mapping from the Georgia SHINES database tables to the requested elements.

N/A

N/A

The CPRS is a J2EE application with an Oracle database.

The CPRS accesses and invokes the Georgia SHINES web services in real time using SOAP/XML as the transport mechanism. The return message from Georgia SHINES will also be packaged as SOAP/XML.

This interface uses an OUTBOUND table (CPRS_CPS_INSERT_OUTBOUND) that is updated with data for Persons involved in open INV or ONG stages.

Georgia SHINES exposes web services, which XFire makes available to the trusted client, CPRS. The Georgia SHINES web service parses the XML fields in the CPRS request and uses this information to search through the OUTBOUND table.

Georgia SHINES searches through the OUTBOUND table to package the INV and ONG case data into XML and return it to XFire, which then forwards the result set to CPRS. At this point, how CPRS manages the result data is transparent to Georgia SHINES.

There is a small set of data that is provided through these web services, which is limited to data for Principals, some Collaterals and a Primary Case Manager. Most of the data provided through these web services is related to updates that occur through the Person Detail page (i.e. First Name, Middle Name, Last Name, Suffix and Gender). Most of these data fields aren’t updated very often. Entries within the OUTBOUND table are created and updated based on a set of triggers in the database. When certain information is updated within the database, entries are either added to or updated within the OUTBOUND table. The OUTBOUND table only contains entries for children in, and Primary Case Managers of, open INV and ONG stages.

At the start of the release, the OUTBOUND table is initially pre-populated with Principal Children under the age of 18 who are in open INV and ONG stages, and the Primary Case Manager for these open stages.

When entries are added to the OUTBOUND table, the DT_CREATED field is updated with the current date. Similarly, when entries are updated within the OUTBOUND table, the DT_LAST_UPDATE field is updated with the current date. When CPRS requests an entry from the OUTBOUND table through invocation of the web service, the DT_PROCESS field in the OUTBOUND table is updated.

The service, request message and response message are defined in the following WSDLs.

For the CprsCpsCaseList web service, the request consists of three values:

County – This value is required to be submitted by CPRS. This is the only county in which to search for INV or ONG case data.

Start Date – This value is required to be submitted by CPRS. The search for INV or ONG data in the OUTBOUND table should only include Cases that have been updated since this date.

End Date – This is an optional value that can be provided by CPRS. This would allow the search of INV or ONG data in the OUTBOUND table which only include Cases that have not been updated after this date.

For the CprsCpsCaseData web service, the request consists of four values:

Case Count – This default value is ‘1’, but it is a required field that can be used by CPRS to limit the number of Cases that are returned in the response. An error message is generated, at the case level, if data is not available for a given case. This way, CPRS knows which case requests have failed.  Currently, the Case Count does not have any functionality behind it.

List of Case IDs – This is a required value. CPRS may provide one or more IDs of Cases for which they would like to retrieve INV and ONG data.

Start Date – This value is required to be submitted by CPRS. The search for INV or ONG data in the OUTBOUND table should only include Cases that have been updated since this date.

The request messages are as follows:

For the CprsCpsCaseList web service, the response returns a list of Cases and child names which match the criteria specified. The response message is as follows:

For the CprsCpsCaseData web service, the response returns INV and ONG data for the specified case. The response message is as follows:

In order to obtain INV and ONG case data, there are two web services that are exposed:

CprsCpsCaseData – retrieveCprsCpsCaseData

There is a web service containing a method that is used by the CPRS to retrieve a list of Case IDs and a list of children for the Case. CPRS must provide the County from which the Case IDs should be retrieved, as well as a start date. The web service retrieves all Cases in the OUTBOUND table having a DT_LAST_UPDATE greater than or equal to the start date provided by CPRS, effectively returning the list of all Cases in and the associated children for the given County that have been updated since the date provided.

CprsCpsCaseList – retrieveCprsCpsCaseList

There is a web service containing a method that is used by CPRS to retrieve the actual INV and ONG case data. CPRS may optionally specify the number of cases that they would like to have returned in the request. However, CPRS must provide the list of Case IDs for which they would like to receive INV and ONG case data and a start date must be provided. The web service retrieves all Cases in the OUTBOUND table matching the Case ID provided and having a DT_LAST_UPDATE greater than or equal to the start date provided by CPRS, effectively returning data for the given Case as long as it has been updated since the date provided.

Triggers

Triggers are used to monitor the Shines database tables to determine when actions in Shines have changed data that the interface is sending to CPRS. If a change is determined to be relevant to the interface, the records in the CPRS_CPS_Outbound table will be updated.

Procedures and Functions

The procedures and functions are used by the triggers, to allow for the reuse of common actions and to improve the efficiency in the data base.

UPDATE_CPRS_CPS_CASE

A function that inserts a record into the CPRS_CPS_OUTBOUND table. Records will only be inserted into the table if the records do not currently exist, and if the principal is under 18 years of age.

*PERSON_LEGAL_COUNTY *

A procedure that returns the current legal county and effective date for a person and stage.

CPRS_CPS_INSERT_OUTBOUND

A procedure that updates the Last Update Date for a Case in the CPRS_CPS_OUTBOUND table. A case record will only be updated if it has a IND_STAGE_CLOSED = ‘N’, IND_PERSON_MERGE = 'N', and if it is a principal’s record the age must be under 18 years and 6 months.

Case Merge Scenarios

These scenarios illustrate the state of the OUTBOUND table and interface response during different points in case merge and split activities.

Person Merge Scenarios

Scenarios showing the state of the OUTBOUND table and interface response during different points in person merge and split activities.

A script exists to load the OUTBOUND table with data for Principals under the age of 18 in open INV or ONG stage at the time of deployment. This script is run once, at deployment only.

The DBCR number used to execute this script is as follows: STGAP00018288.

Class Diagrams are attached as jpg files.

CprsCpsCaseList

<Insert Sequence Diagram here>

*<Method name here> *

<Method description here>

N/A

N/A