Contents Show
Changes made to the SPIRIT WIC database in this release of the software are detailed below.
The following new stored procedures were added:
The GetDuplicateParticipantConfiguration stored procedure was added to return a list of rows from the DUPLICATEPARTICIPANTCONFIGURATION table.
The GetNonDuplicateParticipants stored procedure was added to return a list of rows from the DUPLICATEPARTICIPANTS table where the ISDUPLICATE value is not equal to "NULL".
The RemoveDuplicateParticipant stored procedure was added to remove all rows from the DUPLICATEPARTICIPANTS table where the ISDUPLICATE value is "NULL".
The GetMemberPhoneticNullValues stored procedure was added to retrieve all the rows from the MEMBER table when any one or more of the following values is "NULL" or empty, and update with the double metaphonic key and alternate key values:
PHONETIC_FIRST_NAME_PRIM
PHONETIC_FIRST_NAME_SEC
PHONETIC_LAST_NAME_PRIM
PHONETIC_LAST_NAME_SEC
The MEMBER table was modified as follows:
The following columns were added:
PHONETIC_FIRST_NAME_PRIM
PHONETIC_FIRST_NAME_SEC
PHONETIC_LAST_NAME_PRIM
PHONETIC_LAST_NAME_SEC
The following triggers were added:
If the FIRSTNAME value is updated, then set the PHONETIC_FIRST_NAME_PRIM and PHONETIC_FIRST_NAME_SEC values to "NULL".
If the LASTNAME value is updated, then set the PHONETIC_LAST_NAME_PRIM and PHONETIC_LAST_NAME_SEC values to "NULL".
The following new stored procedures were added:
The GetStateBusinessRuleParameters stored procedure was added to return a list of rows from the STATEBUSINESSRULES table and retrieve the value of the APT_REMINDEREMAILENABLED business rule.
The GetAppointmentReminders stored procedure was added to retrieve the appointment reminder date based on the 6pm rule.
When End of Day Command Line Batch Processing is run before 6pm, sets the @ReminderDate to today’s date.
When End of Day Command Line Batch Processing is run after 6pm, sets the @ReminderDate to tomorrow’s date.
The GetReferenceDictionary stored procedure was added to load the REFERENCEDICTIONARY table and retrieve the parameters needed to process appointment reminder e-mails.
The GetNextHealthNoteId stored procedure was added to retrieve the maximum HEALTHNOTEID value from the HEALTHNOTE table.
The following tables were modified:
The Appointment Reminder Service adds a new record with the “WICEOD” value for PROCESSNAME and the "WICEOD" value for SYSTEMNAME to the COMMREQUEST table if one does not exist.
Four new values were added to the EBTCONNECTIONINFO table.
Appointment Reminder Template English (ApptReminderTemplateEng)
This value represents the folder location and filename for the English e-mail template. This value is required. Example: C:\Program Files (x86)\Covansys Inc. - BPDS\Wic\Templates\AN005.txt
Appointment Reminder Template Spanish (ApptReminderTemplateSpanish)
This value represents the folder location and filename for the Spanish e-mail template. This value is required. Example: C:\Program Files (x86)\Covansys Inc. - BPDS\Wic\Templates\AN006.txt
Appointment Reminder Batch Timeout (ApptReminderBatchTimeout)
This value is used to control the timeout period of the bulk insert of health notes. The default for this value is 60 seconds. This value is required.
Appointment Reminder Batch Record Count (ApptReminderBatchRecordCount)
This value is used to control the number of health note rows to bulk load during each part of a single transmission. The default for this value is 5000 rows. This value is required.
The UpdatePendingVendorDisqualifications stored procedure was added to perform the following when the values of both the EOD_PROCESSPENDINGDISQUALIFICATION and the EOD_RUNVENDORPROCESSES business rule equal "Y",:
Returns a list of vendors if the vendor grace period has expired, the vendor is in a Pending Disqualification status, and the PENDINGDISQUALDATE is not equal to "NULL" and is less than or equal to the @JobRunDate based on the 6pm rule.
When End of Day Command Line Batch Processing is run before 6pm, sets the @JobRunDate to today’s date.
When End of Day Command Line Batch Processing is run after 6pm, sets the @JobRunDate to tomorrow’s date.
Retrieve the Max Termination Date from the TERMDISQUALIFICATION table for each vendor disqualification processed and update the DEACTBANKEFFECTIVEDATE value in the VENDORSTAMP tabletable.
Updates the following columns in the VENDOR table for each vendor disqualification processed and sets Vendor Status equal to "Disqualified".
VENDOR. CURRENTSTATUS = 5 (Disqualified)
VENDOR. REINSTATEDATE = The REINSTATEDATE is calculated by adding the DAYSDISQUALIFIED in the TERMDISQUALIFICATION table to the current system date for the applicable record.
VENDOR. PENDINGDISQUALDATE = NULL
VENDOR. LASTSTATUSCHANGEDATE = Date of End of Day Command Line Batch Processing.
When End of Day Command Line Batch Processing is run before 6pm, sets to today’s date.
When End of Day Command Line Batch Processing is run after 6pm, sets to tomorrow’s date.
VENDOR. CURRENTAPPLICATION = If the value equals "Y", sets to empty. Otherwise, does not change.
VENDOR. CURRENTLYENROLLED = N
VENDOR. MODIFYUSERID = Code passes the @UserId of user running the End of Day Command Line Batch Processing
VENDOR. MODIFYDTTM = Current date and time
Updates the following columns in the VENDORSTAMP table to terminate all vendor stamps for each vendor disqualification processed.
VENDORSTAMP. DEACTBANKEFFECTIVEDATE = Max Termination Date from the TERMDISQUALIFICATION table
VENDORSTAMP.MODIFYDTTM = Current date and time
VENDORSTAMP.MODIFYUSERID = Code passes the @UserId of user running the End of Day Command Line Batch Processing
Inserts the following columns into the STATUSHISTORY table for each vendor disqualification processed.
STATUSHISTORY. STATUSHISTORYID = Max STATUSHISTORYID +1
STATUSHISTORY. VENDORID = VENDORID for each vendor disqualification processed
STATUSHISTORY. STATUS = 5 (Disqualified)
STATUSHISTORY. DATEOFCHANGE = Date of End of Day Command Line Batch Processing.
When End of Day Command Line Batch Processing is run before 6pm, sets to today’s date.
When End of Day Command Line Batch Processing is run after 6pm, sets to tomorrow’s date.
STATUSHISTORY. UPDATERECORD = U
STATUSHISTORY. CREATEUSERID = Code passes the @UserId of user running the End of Day Command Line Batch Processing
STATUSHISTORY. CREATEDTTM = Current date and time
STATUSHISTORY. MODIFYUSERID = Code passes the @UserId of user running the End of Day Command Line Batch Processing
STATUSHISTORY. MODIFYDTTM = Current date and time
Updates the LASTNUMBERUSED column in the SEQNUMBERS table with the Max STATUSHISTORYID value for the STATUSHISTORY KEYCODE record.
The new ProcessSanctionPoints stored procedure was added to update the following columns in the EVENT and FOLLOWUPACTIVITY tables when both the EOD_PROCESSSANCTIONPOINTS and EOD_RUNVENDORPROCESSES business rules equal "Y":
MODIFYDTTM = Date on which End of Day Command Line Batch Processing ran.
MODIFYUSERID = Code passes the @UserId of the user running the End of Day Command Line Batch Processing.
SANCTIONPOINTS = 0
The following new stored procedures were added:
The RemovePreviousExpiredBenefits stored procedure was added to remove expired benefits records that contain the @Filename from the FOODINSTRUMENTITEMDETAIL table and return the number of rows.
The GetFoodInstrumentId stored procedure was added to return the FOODINSTRUMENTID value from the FOODINSTRUMENT table that corresponds to the EBTISSUANCENBR value in the EBTISSUANCECROSSREF table.
The following tables were modified:
If the SUMebtFOODINSTRUMENTITEMDETAIL table existed in the database, it was renamed to zRetired_SUMebtFOODINSTRUMENTITEMDETAIL for Summer EBT.
Two new values were added to the EBTCONNECTIONINFO table.
Expired Benefits Batch Timeout (ExpiredBenefitsBatchTimeout)
This value is used to control the timeout period of the bulk insert of initial records. The default for this value is 60 seconds. This value is required.
Expired Benefits Batch Record Count (ExpiredBeneBatchRecordCount)
This value represents the maximum number of rows to bulk load during each part of a single transmission. The default for this value is 5000 rows. This value is required. If, for any reason, the batch record count isn't available, a default of 2000 rows is used.
The InsertFIItemRedeemedAggregated stored procedure was modified to select all purchase transaction(s) (Transaction code 305) from the FOODINSTRUMENTITEMDETAIL table instead of only distinct transaction(s) before it matches voided transactions (Transaction code 411) and previously-unprocessed 411 transactions and deletes the matching purchase transaction(s) (Transaction code 305) from the FOODINSTRUMENTITEMREDEEMED_AGGREGATED table.