Contents Show
Changes made to the SPIRIT WIC database in this release of the software are detailed below.
A new sql_server_2017_compatibility_level.sql file was created and distributed with this release. The new sql_server_2017_compatibility_level.sql file sets the compatibility level for SQL Server 2017 environments.
The sql_server_2017_compatibility_level.sql must be run manually by the user, and the user who runs the script must have SQL ALTER permissions on the database. However, only those State Agencies that are upgrading to SQL Server 2017 need to run the script on their SPIRIT database after upgrading to SQL Server 2017. Any modifications to the provided script are done at the State Agency's own risk.
A new RF142_311_pull_off_script.sql file was created and distributed with this release. The new RF142_311_pull_off_script.sql file deletes the outdated risk factors on the target (Production) database and re-inserts the risk factors from the source (UAT) database. Run the script on the source (UAT) database to generate and insert the scripts, then copy the generated scripts and run them on the target (Production) database. When run, the new RF142_311_pull_off_script.sql file performs the following tasks:
Changes all currently-existing RISKFACTORID 142 columns in the RISKFACTORREFERENCE table to "142Z" and sets the expiration date to yesterday’s date to deactivate these records.
Changes all currently-existing RISKFACTORID 142 columns in the RISKFACTOR table to "142Z" and sets the expiration date to yesterday’s date to deactivate these records.
Creates and populates new RISKFACTORID 142 columns in the RISKFACTORREFERENCE table and updates the following columns:
Sets the DESCRIPTION to "Preterm or Early Term Delivery".
Sets the PRIORITY to "1".
Sets the EFFECTIVEDATE to today's date.
Sets the DFPPRIORITY to NULL.
Sets the FOODPACKAGEID to NULL.
Changes all currently-existing RISKFACTORID 311 columns in the RISKFACTORREFERENCE table to "311Z" and sets the expiration date to yesterday’s date to deactivate these records.
Changes all currently-existing RISKFACTORID 311 columns in the RISKFACTOR table to "311Z" and sets the expiration date to yesterday’s date to deactivate these records.
Creates and populates new RISKFACTORID 311 columns in the RISKFACTORREFERENCE table and updates the following columns:
Sets the DESCRIPTION to "History of Preterm or Early Term Delivery".
Sets the PRIORITY to "1".
Sets the EFFECTIVEDATE to today's date.
Sets the DFPPRIORITY to NULL.
Sets the FOODPACKAGEID to NULL.
The RF142_311_pull_off_script.sql file must be run manually by the user, and the user who runs the script must have SQL object create permissions on the server. Before running the script, ensure the SSMS environment is set up (SSMS needs to be set up to create the script). CDP also recommends a backup of the RISKFACTORREFERENCE table before running the script, and any modifications to the generated script are done at the State Agency's own risk.
Complete the following steps to create the script (by creating a file):
1. Open SSMS.
2. Go to Tools.
3. Go to Options.
4. Go to Query results.
5. In the Default destination for results, change to results to file.
6. Go to Results to Text and change Maximum number of characters displayed in each column to 8192.
Complete the following steps to execute the script:
1. Open the pull off script in SSMS.
2. Run the script.
3. Save as an .sql file.
Complete the following steps to run the results script on Production:
1. Open SSMS.
2. Run the results script.
OUT OF MEMORY ERRORS
If an out of memory error occurs due to a large amount of data being processed, execute the following steps using a 64-bit sqlcmd prompt:
1. Open a command prompt in this location (drive letter may be different).
{drive letter}:\Program Files\Microsoft SQL Server\100\Tools\Binn
2. Run the following command:
sqlcmd -S .\ -d databasename -U username -P userpassword -i script location/name.sql
Ex. sqlcmd -S .\ -d MODevDatabase -U jsmith -P admin1234 -i C:\UATpulloff.sql
3. Save as an .sql file.
The following new stored procedures were created:
A new GetMarOverride3.sql was added to populate the actual data in the data grid based on the specified filter criteria.
A new GetMarsOverrideInitialDataset.sql was added to populate the options available for selection in the filter criteria drop-down list boxes.
A new uspEBTMARDeclined.sql was added to populate the MARs that have been manually declined.
A new uspDeleteEBTMARDeclined.sql was added to delete matching MARs from the EBTMARDECLINED table when a purchase occurs after the calculated date from the EBT MAR is declined.
On the EBTMAR table, a data check was performed to determine whether the PEERGROUP, UPCCD and ACTIVEDATE columns are set as Primary Keys to ensure data accuracy. If the columns are already set as Primary Keys, no changes are made to the EBTMAR table during the software upgrade. If the columns are not already set as Primary Keys, a PK_EBT_MAR Primary Key constraint was added to correctly update the Primary Keys in the EBTMAR table.
On the EBTMARHISTORY table, a data check was performed to determine whether the PEERGROUP, UPCCD, ACTIVEDATE, and CHANGEDDTTM columns are set as Primary Keys to ensure data accuracy. If the columns are already set as Primary Keys, no changes are made to the EBTMARHISTORY table during the software upgrade. If the columns are not already set as Primary Keys, a PK_EBTMAR_HISTORY Primary Key constraint was added to correctly update the Primary Keys in the EBTMARHISTORY table.
A new EBTMARDECLINED table was created, which includes the following columns:
Column Name |
Data Type |
Constraint |
UPCCD |
[varchar](20) |
NOT NULL |
PEERGROUP |
[tinyint] |
NOT NULL |
CALCULATEDATE |
[datetime] |
NOT NULL |
CREATEUSERID |
[varchar](20) |
NULL |
CREATEDTTM |
[datetime] |
NULL |
MODIFYUSERID |
[varchar](20) |
NULL |
MODIFYDTTM |
[datetime] |
NULL |
A new EBTMARDECLINEDHISTORY table was created, which includes the following columns:
Column Name |
Data Type |
Constraint |
UPCCD |
[varchar](20) |
NOT NULL |
PEERGROUP |
[tinyint] |
NOT NULL |
CHANGEDDTTM |
[datetime] |
NOT NULL |
CALCULATEDATE |
[datetime] |
NOT NULL |
CREATEUSERID |
[varchar](20) |
NULL |
CREATEDTTM |
[datetime] |
NULL |
MODIFYUSERID |
[varchar](20) |
NULL |
MODIFYDTTM |
[datetime] |
NULL |
A new uspGetEBTFoodSetsByHousehold.sql stored procedure was added to retrieve all of the food benefit sets for households.
The uspGetBaseCatLimitInfo.sql stored procedure was modified to include alternate FPIII values for formula.
The V_EBTFoodInstruments view was modified to return WICSTATUS data.
The following row was added to the STATEBUSINESSRULES table:
Column Name |
Row Value |
CATEGORYID |
CLN_ReturnInfantFood |
VALUE |
N |
DESCRIPTION |
Enable/disable the ability to return infant food items. |