Contents Show
Changes made to the SPIRIT WIC database in this release of the software are detailed below.
A new GetNextFoodInstrumentDetailIdValue stored procedure was added, which retrieves the next ID value in a range for both EBT and Summer EBT. This new stored procedure retrieves the following parameters:
@KEYCODE [varchar](30)
@MODIFYUSERID [varchar](25)
@newvalue [numeric](10, 0) output
@range [numeric](10, 0) = 1 /* Optional Parameter To Allow For Quicker Batch Updates*/
A new FOODINSTRUMENTDETAILITEMCONSIGNMENT table was created, which includes the following columns:
Column Name |
Data Type |
Constraint |
KEYCODE |
[varchar](30) |
NOT NULL |
LASTNUMBERUSED |
[numeric](10, 0) |
NULL |
MODIFYUSERID |
[varchar](20) |
NULL |
MODIFYDTTM |
[datetime] |
NULL |
The TEMPFOODINSTRUMENTDETAILCONSIGNMENT temporary table that was previously being used during benefit issuance was dropped and the benefit issuance process now adds details to the new FOODINSTRUMENTITEMDETAILCONSIGNMENT table.
Insert queries were added to insert data into new FOODINSTRUMENTDETAILITEMCONSIGNMENT table.
The following columns were added to the FOODINSTRUMENTITEMREDEEMED table (existing rows will be given a value of NULL):
Column Name |
Data Type |
Constraint |
Default |
EBTISSUANCENBR |
Int |
NULL |
NULL |
PROCESSED |
Bit |
NULL |
1 |
The following columns were added to the SUMEBTFOODINSTRUMENTITEMREDEEMED table (existing rows will be given a value of NULL):
Column Name |
Data Type |
Constraint |
EBTISSUANCENBR |
Int |
NULL |
PROCESSED |
Bit |
NULL |
The following new stored procedures were created:
A new InsertFIItemRedeemed stored procedure was added to update the following data:
The FOODINSTRUMENTID from the FOODINSTRUMENT table based on the issuance number provided.
The DISTRIBUTIONITEMID from the FOODDISTRIBUTIONITEM table based on the FOODINSTRUMENTID, FOODCATEGORYCD, and FOODSUBCATEGORYCD.
The REDEEMEDITEMID from the FOODINSTRUMENTITEMREDEEMED table.
The InsertFIItemRedeemed stored procedure also performs the following tasks:
Sets the PAIDDATE of the FOODINSTRUMENTITEMREDEEMED table to the parameter PAIDDATE.
Updates the PROCESSED column of the FOODINSTRUMENTITEMREDEEMED table to the value if 1 for the transaction code = '411', and if there is no matching purchase transaction found for a Year. If the purchase date <= DATEADD(YEAR, -1 , SYSDATETIME().
A new InsertFIItemRedeemedAggregated stored procedure was added to perform the following tasks:
Inserts records from the FOODINSTRUMENTITEMREDEEMED table that do not have a transaction code of '411'or '903'.
Finds matching purchase transaction(s) (Transaction code 305) for voided transactions (Transaction code 411) and previously-unprocessed 411 transactions and delete the matching purchase transaction(s) (Transaction code 305) from the FOODINSTRUMENTITEMREDEEMED_AGGREGATED table.
Returns the list of VOID transactions within the current file that have no matching purchase transactions.
A new RemovePreviousRedemptionImport stored procedure was added to remove redeemed records that contain the @Filename from the FOODINSTRUMENTITEMREDEEMED table if @IsSummerEBT = 0 or if SUMEBTFOODINSTRUMENTITEMREDEEMED if @IsSummerEBT = 1 and return the number of rows.
A new GetEbtConnectionInfoParameters stored procedure was added to return all EBT connection information parameters.
A new GetNextRedeemedItemId stored procedure was added to get the maximum REDEEMEDITEMID value from the FOODINSTRUMENTITEMREDEEMED table if @IsSummerEBT = 0 or SUMEBTFOODINSTRUMENTITEMREDEEMED if @IsSummerEBT = 1 and return the value.
A new InsertCurrentlyExecuting stored procedure was added to insert CURRENTLYEXECUTING table data.
A new DeleteCurrentlyExecuting stored procedure was added to remove CURRENTLYEXECUTING table data.
A new GetCurrentlyExecuting stored procedure was added to retrieve information.
A new InsertScheduledJobStatus stored procedure was added to insert SCHEDULEDJOBSTATUS table data.
The following new stored procedures were created:
A new CalculateMAR stored procedure was added to calculate the Maximum Allowed Reimbursement (MAR) values for all Universal Product Code (UPC) codes that currently exist within the SPIRIT database. See the CalculateMAR Stored Procedure topic in the Scheduled Job Administration DFDD for more information.
A new GetNewMARCats stored procedure was added to retrieve a list of new MAR categories. Additionally, previous in-line SQL code contained a pg.PEERGROUP column, and that column was removed when moving calculations to the new CalculateMAR stored procedure.
A new InsertScheduledLog stored procedure was added to insert records into the SCHEDULEDJOBLOG table.
A new InsertExceptionLog stored procedure was added to insert records into the EXCEPTIONLOG table.
A new GetEBTNotificationList stored procedure was added to retrieve Electronic Benefit Transfer (EBT) notifications based on Process Type.