NOTE: This procedure selects woman and child participants who exceeded age limits and were adjusted to categorically ineligible in a previous procedure.
Contents Show
Changes made to the SPIRIT WIC database in this release of the software are detailed below.
New Procedures
The following functions were converted from in-line SQL code and views to new stored procedures which were added to adjust, archive and purge participant records.
All procedures below determine and pass @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.
|
Adjust Process
This process adjusts Member records if the value of the EOD_PROCESSMEMBERADJUSTMENTS business rule equals "Y".
Adjust participants to Categorically Ineligible
The CheckMaximumChildWomanAge stored procedure was added to update the CATEGORICALINELIGIBILITYDATE for a list of participants who have exceeded the maximum ages based on the MAXIMUMCHILDAGE and MAXIMUMWOMANAGE business rules in the STATEBUSINESSRULES table and meet the following criteria:
Child participants where (@JobRunDate- MEMBER. DATEOFBIRTH) >= STATEBUSINESSRULES. CATEGORYID = MAXIMUMCHILDAGE
Woman participants where (@JobRunDate- MEMBER. DATEOFBIRTH) > = STATEBUSINESSRULES. CATEGORYID = MAXIMUMWOMANAGE
MEMBER. VALIDCERTIFICATION = N
MEMBER. CATEGORICALINELIGIBILITYDATE is NULL
Child participants where MEMBER. WICSTATUS = C (child)
Woman participants where MEMBER. WICSTATUS = B (Breastfeeding), N (Non-Breastfeeding), or P (Pregnant)
With this stored procedure, the following changes are made to the MEMBER table for all participants identified above:
CATEGORICALINELIGIBILITYDATE = @JobRunDate
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Adjust participants to Terminated
The CheckCertLimitWithPendingIDProof stored procedure was added to terminate a list of participants who have exceeded the maximum number of days to be certified without proof of ID, residency, or income based on the CERTLIMITWITHPENDINGIDPROOF, CERTLIMITWITHPENDINGRESIDENCYPROOF, and CERTLIMITWITHPENDINGINCOMEPROOF business rules in the STATEBUSINESSRULES table, and meet the following criteria:
Identify a list of participants who have exceeded the maximum number of days to be certified without proof of ID, Residency, or Income and meet the following criteria:
#AdjustParticipant temporary table uses the same existing logic that is in V_ADJUST_PARTICIPANT view to get participant data.
Select STATEWICID and CERTIFICATIONID from the #AdjustParticipant temporary table into the variable named @TerminateMemberAndCert using the filtering statements below:
For proof of ID WICSTATUS = P (Pregnant), B (Breastfeeding), N (Non-breastfeeding), I (Infant), or C (Child)
For proof of ID IDENTIFICATIONPROOF = ZZ
For proof of ID #AdjustParticipant.CertifiedDays > (value from STATEBUSINESSRULES. CATEGORYID = CERTLIMITWITHPENDINGIDPROOF)
For proof of Residency #AdjustParticipant.CertifiedDays > (value from STATEBUSINESSRULES. CERTLIMITWITHPENDINGRESIDENCYPROOF)
For proof of Residency RESIDENCYPROOF = STATEBUSINESSRULES. CATEGORYID = PENDINGRESIDENCYPROOFVALUE
For proof of Income INCOMEPENDINGPROOF = Y
For proof of Income #AdjustParticipant.CertifiedDays > (value from STATEBUSINESSRULES. CATEGORYID= CERTLIMITWITHPENDINGINCOMEPROOF)
For proof of ID or Residency HOMELESS = N OR HOMELESS is NULL
For proof of ID, Residency, or Income, TERMINATED = N
For proof of ID, Residency, or Income VOCDOCUMENT <> Y
This stored procedure terminates participants as described in Terminate Participants.
|
The CheckCategoricallyIneligibleParticipants stored procedure was added to terminate a list of categorically ineligible participants who meet the following criteria:
Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
MEMBER. CATEGORICALINELIGIBILITYDATE <= @JobRunDate
MEMBER. TERMINATED = N
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
|
NOTE: This procedure selects woman and child participants who exceeded age limits and were adjusted to categorically ineligible in a previous procedure. |
This stored procedure terminates participants as described in Terminate Participants.
|
NOTE: A participant may be terminated due to categorical ineligibility prior to their 31 days post certification end date termination. Example: A participant's Certification End Date is 7/15/2020.
|
|
The CheckRecertifyFailure stored procedure was added to terminate a list of participants who failed to re-certify for 31 days past their certification due date, are not in a new certification process, and meet the following criteria:
Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
TERMINATED = N
(value of @JobRunDate - MEMBER. CERTIFICATIONDUEDATE) > 30 days
MEMBER. PENDINGCERTIFICATIONID is NULL
This stored procedure terminates participants as described in Terminate Participants.
|
The CheckFailedToPickupFI stored procedure was added to terminate a list of participants who failed to pick up food instruments for two consecutive months and meet the following criteria:
Exclude mothers who are partially breastfeeding their infants at the External ID for Breastfeeding Amount of 5 who failed to pick up food instruments when the infant has picked up food instruments. Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
MEMBER. STATEWICID = CERTCONTACT. STATEWICID
MEMBER. CERTSTARTDATE = CERTCONTACT. CERTSTARTDATE
All rows from MEMBER table where MEMBER. STATEWICID = CHILD. STATEWICID OR MEMBER. WICSTATUS = B
CHILD. AMOUNTBREASTFEEDING = 5 (Partially BF> Max, OR Some BF, OR Some-Breastfeeding, etc. depends on agency)
CHILD. STATEWICID IN(POSTPARTUMINFANT. STATEWICID where POSTPARTUMINFANT. POSTPARTUMID = CERTCONTACT. POSTPARTUMID)
MEMBER. TERMINATED <> Y
MEMBER. VALIDCERTIFICATION = Y
CERTCONTACT. CERTEFFECTIVEDATE is NOT NULL
IF MEMBER. LASTCHECKLDTU is NULL or CERTCONTACT. CERTEFFECTIVEDATE > MEMBER. LASTCHECKLDTU Then DATEADD(MONTH, 2, cc. CERTEFFECTIVEDATE) ELSE DATEADD(MONTH, 2, m. LASTCHECKLDTU) END <= @JobRunDate
Terminate mothers who were excluded in the previous query and whose infants were terminated. Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
MEMBER. CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID AND MEMBER. PENDINGCERTIFICATIONID is NULL
MEMBER. STATEWICID = CHILD. MOTHERSTATEWICID
CHILD. AMOUNTBREASTFEEDING = 5 (Partially BF> Max, OR Some BF, OR Some-Breastfeeding, etc. depends on agency)
MEMBER. STATEWICID = CHILD. STATEWICID AND MEMBER. TERMINATED = Y
MEMBER. CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID AND CERTCONTACT. CERTTERMREASON is NOT NULL AND CERTCONTACT. CERTENDDATE >= CERTCONTACT. CERTEFFECTIVEDATE
MEMBER. TERMINATED <> Y
MEMBER. VALIDCERTIFICATION = Y
CERTCONTACT. CERTEFFECTIVEDATE is NOT NULL
MEMBER. WICSTATUS = B (Breastfeeding)
IF MEMBER. LASTCHECKLDTU is NULL or CERTCONTACT. CERTEFFECTIVEDATE > MEMBER. LASTCHECKLDTU Then DATEADD(MONTH, 2, cc. CERTEFFECTIVEDATE) ELSE DATEADD(MONTH, 2, m. LASTCHECKLDTU) END <= @JobRunDate
This stored procedure terminates participants as described in Terminate Participants.
|
The CheckCertLimitWithRF503NoHW stored procedure was added to terminate a list of participants who have been certified with risk factor 503 (Presumptive Eligibility) for more days than the value of the CERTLIMITWITHRF503NOHW business rule without a height/weight measurement contact and meet the following criteria:
Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
All rows from MEMBER table where MEMBER. CERTIFICATIONID = RISKFACTOR. CERTIFICATIONID and RISKFACTOR. RISKFACTORID = 503
All rows from MEMBER table where MEMBER. CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
All rows from MEMBER table where MEMBER. AGENCYID = SERVICESITE. AGENCYID AND MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
No record for the participant in ANTHROPCONTACT table
(value of @JobRunDate - MEMBER. CERTSTARTDATE) > value from STATEBUSINESSRULES where CATEGORYID = CERTLIMITWITHRF503NOHW
CERTCONTACT. VOCDOCUMENT is NULL OR <> Y
This stored procedure terminates participants as described in Terminate Participants.
|
The CheckCertLimitWithRF503NoBlood stored procedure was added to terminate a list of participants who have been certified with risk factor 503 (Presumptive Eligibility) for more days than the value of the CERTLIMITWITHRF503NOBLOOD business rule without a blood work contact and meet the following criteria:
Select STATEWICID and CERTIFICATIONID into the variable named @TerminateMemberAndCert using the filtering statements below:
All rows from MEMBER table where MEMBER. CERTIFICATIONID = RISKFACTOR. CERTIFICATIONID and RISKFACTOR. RISKFACTORID = 503
All rows from MEMBER table where MEMBER. CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
All rows from MEMBER table where MEMBER. AGENCYID = SERVICESITE. AGENCYID AND MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
No record for the participant in BLOODWORK table
(value of @JobRunDate - MEMBER. CERTSTARTDATE) > (value from STATEBUSINESSRULES where CATEGORYID = CERTLIMITWITHRF503NOBLOOD)
CERTCONTACT. VOCDOCUMENT is NULL OR <> Y
This stored procedure terminates participants as described in Terminate Participants.
|
The following changes are made for each stored procedure to adjust participant status to "Terminated".
With each of these stored procedures, the following changes are made to the MEMBER table for each participant identified above:
TERMINATEDDATE = @JobRunDate
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
UPDATERECORD = Y
VALIDCERTIFICATION = N
TERMINATED = Y
In addition, the following changes are made to the CERTCONTACT table for each participant identified above:
CERTTERMDATE = @JobRunDate
CERTTERMREASON = (Value based on reason for termination)
E (No Proof of ID Provided)
D (No Proof of Residency Provided)
G (No Proof of Income Provided)
0 (Categorically Ineligible)
C (Certification Expired)
6 (Non-participation (failed to pick up))
H (Presumptively Eligible No Height/Weight or Blood Work Provided)
UPDATERECORD = Y
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Adjust participants to Ineligible
The CheckIncompleteCertLimitPregnant stored procedure was added to identify a list of pregnant participants who have exceeded the maximum number of days to complete certification based on the INCOMPCERTLIMITPREGNANT, business rule in the STATEBUSINESSRULES table, and meet the following criteria:
Select STATEWICID and PENDINGCERTIFICATIONID from the MEMBER table into the variable named @AdjustEOD using the filtering statements below:
MEMBER. PENDINGCERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. WICSTATUS = P (Pregnant)
MEMBER. CERTSTARTDATE is NOT NULL
(value of @JobRunDate - MEMBER. CERTSTARTDATE) >= (value from STATEBUSINESSRULES. CATEGORYID = INCOMPCERTLIMITPREGNANT)
CERTCONTACT. VOCDOCUMENT is NULL OR <> Y
MEMBER. PENDINGCERTIFICATIONID is NOT NULL
CERTCONTACT. CERTINELIGIBLEREASON is NULL|
This stored procedure adjusts participants as described in Update Ineligible Participants.
|
The CheckIncompleteCertLimitMigrant stored procedure was added to identify a list of migrant participants who have exceeded the maximum number of days to complete certification based on the INCOMPCERTLIMITMIGRANT, business rule in the STATEBUSINESSRULES table, and meet the following criteria:
Select STATEWICID and PENDINGCERTIFICATIONID from the MEMBER table into the variable named @AdjustEOD using the filtering statements below:
MEMBER. PENDINGCERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. HOUSEHOLDID = HOUSEHOLD. HOUSEHOLDID
HOUSEHOLD. MIGRANT = Y
MEMBER. CERTSTARTDATE is NOT NULL
(value of @JobRunDate - MEMBER. CERTSTARTDATE) >= (value from STATEBUSINESSRULES. CATEGORYID = INCOMPCERTLIMITMIGRANT)
CERTCONTACT. VOCDOCUMENT is NULL OR <> Y
MEMBER. PENDINGCERTIFICATIONID is NOT NULL
CERTCONTACT. CERTINELIGIBLEREASON is NULL
This stored procedure adjusts participants as described in Update Ineligible Participants.
|
The CheckIncompleteCertLimitOther stored procedure was added to identify a list of pregnant participants who have exceeded the maximum number of days to complete certification based on the INCOMPCERTLIMITOTHER, business rule in the STATEBUSINESSRULES table, and meet the following criteria:
Select STATEWICID and PENDINGCERTIFICATIONID from the MEMBER table into the variable named @AdjustEOD using the filtering statements below:
MEMBER. PENDINGCERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. HOUSEHOLDID = HOUSEHOLD. HOUSEHOLDID
HOUSEHOLD. MIGRANT = N or NULL
MEMBER. WICSTATUS = I (Infant), C (Child), B (Breastfeeding), or N (Non-Breastfeeding),
MEMBER. CERTSTARTDATE is NOT NULL
(value of @JobRunDate - MEMBER. CERTSTARTDATE) >= (value from STATEBUSINESSRULES. CATEGORYID = INCOMPCERTLIMITOTHER)
CERTCONTACT. VOCDOCUMENT is NULL OR <> Y
MEMBER. PENDINGCERTIFICATIONID is NOT NULL
CERTCONTACT. CERTINELIGIBLEREASON is NULL
This stored procedure adjusts participants as described in Update Ineligible Participants.
|
Update Ineligible Participants
The following changes are made for each stored procedure to adjust participant status to "ineligible" and to queue an "Ineligibility Notice".
With each of these stored procedures, the following changes are made to the MEMBER table for each participant identified above:
QUEUEDINELIGIBILITYNOTICE = Y
QUEUEDINELIGIBILITYDATE = @JobRunDate
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
In addition, the following changes are made to the CERTCONTACT table for each participant identified above:
CERTINELIGIBLEDATE = @JobRunDate
CERTINELIGIBLEREASON = E (Certification attempt not completed in time)
UPDATERECORD = Y
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Adjust Infant participants to Child participants
The CheckChildFirstBirthday stored procedure was added to update records for infant participants who have turned one year old.
Identify a list of infant participants who have turned one year old (called Infant group) and meet the following criteria:
MEMBER. WICSTATUS = I (Infant)
MEMBER. PENDINGCERTIFICATIONID is NULL
MEMBER. DATEOFBIRTH + 1 year <= @JobRunDate
With this stored procedure, the WICSTATUS is changed to C (Child) in the MEMBER table to classify each infant identified above as a child.
|
The stored procedure creates a pseudo-certification record including all applicable risk factors.
Identify a subset list of Infant group participants named above who need a new pseudo-certification record (called pseudo-certification group) and meet the following criteria:
MEMBER. CERTIFICATIONDUEDATE > MEMBER. DATEOFBIRTH
MEMBER. VALIDCERTIFICATION = Y
CERTCONTACT. CERTTERMDATE is NULL or > @JobRunDate
CERTCONTACT. CERTSTARTDATE < @JobRunDate
CERTCONTACT. CERTENDDATE >= @JobRunDate
CERTCONTACT. CERTINELIGIBLEDATE is NULL
CERTCONTACT. STARTDATE <= CERTCONTACT. CERTEFFECTIVEDATE
CERTCONTACT. CERTEFFECTIVEDATE is not equal to @JobRunDate
In addition, the stored procedure updates the following tables for each pseudo-certification identified above:
The following changes are made to the CERTCONTACT table to create a new pseudo-certification for each child in the pseudo-certification group named above:
CERTSTARTDATE = same as Infant CERTSTARTDATE
CERTEFFECTIVEDATE = @JobRunDate
CERTENDDATE = same as Infant CERTENDDATE
CERTWICSTATUS = C (Child)
UPDATERECORD = N
HIGHRISK = N
RESOLVEHIGHRISK = 1
CREATEUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
CREATEDTTM = Current date and time
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
For each child in the pseudo-certification group named above, the child risks associated with their infant certification are carried over to the new child pseudo-certification record that meet the following criteria to select only risk factors that apply to children:
AGECATEGORY. WICSTATUS = C (Child)
AGECATEGORY. MINAGE >= 1
AGECATEGORY. MAXAGE <= 1
The following changes are made to the MEMBER table for each child in the pseudo-certification group named above:
CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
CERTSTARTDATE = CERTCONTACT. CERTSTARTDATE
CERTIFICATIONDUEDATE = CERTCONTACT. CERTENDDATE
PENDINGCERTIFICATIONID = NULL
The following changes are made to the CERTCONTACT table for each child in the pseudo-certification group named above:
CERTCONTACT. CERTENDDATE = @JobRunDate (to terminate the infant certification)
CERTCONTACT. CERTASSIGNEDPRIORITY = highest priority (lowest numeric value) child risk factor associated with the new child pseudo-certification (to associate the child risk factors carried over from the infant certification)
The following changes are made to the MIDCERTIFICATION table for each child in the pseudo-certification group named above:
Updates the MIDCERTIFICATION table to associate the new pseudo-certification ID, where the value of the CERTIFICATIONID column = the new CERTIFICATIONID value in the temporary PSEUDOCERT table.
The stored procedure retains any food prescriptions that were associated with participants in the pseudo-certification group named above. No food prescriptions are created for participants by this stored procedure.
|
Synchronize certification information
The SynchronizeCertificationInfo stored procedure was added to identify a list of participants whose certification information in the MEMBER table does not match their certification information in the CERTCONTACT table, and meet the following criteria:
MEMBER. CERTIFICATIONID = CERTCONTACT. CERTIFICATIONID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
MEMBER. AGENCYID = SERVICESITE. AGENCYID
CERTCONTACT. CERTSTARTDATE> MEMBER. CERTSTARTDATE
In addition, the following changes are made to the MEMBER table for each participant identified above:
CERTSTARTDATE = CERTCONTACT. CERTSTARTDATE
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Adjust Inactive participants
The CheckInactiveParticipants stored procedure was added to identify a list of participants whose certification due date has passed, and meet the following criteria:
MEMBER. CERTIFICATIONDUEDATE is NOT NULL
MEMBER. CERTIFICATIONDUEDATE < @JobRunDate
MEMBER. VALIDCERTIFICATION <> N
In addition, the following changes are made to the MEMBER table for each participant identified above:
VALIDCERTIFICATION = N
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Delete empty initial contact records
The CheckAndDeleteEmptyInitialContactRecords stored procedure was added to identify a list of participants with an initial contact records over 60 days old but no certification, and meet the following criteria:
MEMBER. CERTIFICATIONID = NULL
INITIALCONTACT. CREATEDTTM < (value @JobRunDate - 60 days)
In addition, the identified records are deleted from the INITIALCONTACT table for each participant identified above.
|
Adjust on premises time
The ResetOnPremisesTimes stored procedure was added to identify a list of participants who meet the following criteria:
MEMBER. ONPREMISESDTTM is NOT NULL
In addition, the following changes are made to the MEMBER table for each participant identified above:
ONPREMISESDTTM = NULL
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Archive Process
This process archives inactive participants by assigning them to Agency "88" and Clinic "88" if the value of the EOD_PROCESSMEMBERARCHIVES business rule equals "Y".
Archive participants
The UpdateArchiveInactiveParticipants stored procedure was added to update the AGENCYID and SERVICESITEID in the MEMBER table for a list of participants who are inactive and meet the following criteria:
For participants who have not been back for 60 days after applying for WIC, select STATEWICID and AGENCYID from the MEMBER table into the variable named @InactiveParticipants using the filtering statements below:
(value of @JobRunDate - MEMBER. APPLICATIONDATE ) > 60
No record exists for the participant in the CERTCONTACT table
MEMBER. AGENCYID <> 88
MEMBER. SERVICESITEID <> 88
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
For participants who were terminated more than 6 months ago and are not currently in a new certification process, select STATEWICID and AGENCYID from the MEMBER table into the variable named @InactiveParticipants using the filtering statements below:
MEMBER. TERMINATED = Y
(value of MEMBER. TERMINATEDDATE + 6 months) <= @JobRunDate
MEMBER. PENDINGCERTIFICATIONID is NULL
MEMBER. AGENCYID <> 88
MEMBER. SERVICESITEID <> 88
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
For participants who were ineligible at their last certification attempt more than 6 months ago, select STATEWICID and AGENCYID from the MEMBER table into the variable named @InactiveParticipants using the filtering statements below:
MEMBER. VALIDCERTIFICATION = N
(value of MEMBER. QUEUEDINELIGIBILITYDATE + 6 months) <= @JobRunDate
MEMBER. AGENCYID <> 88
MEMBER. SERVICESITEID <> 88
MEMBER. AGENCYID = SERVICESITE. AGENCYID
MEMBER. SERVICESITEID = SERVICESITE. SERVICESITEID
With this stored procedure, the following changes are made to the MEMBER table for all inactive participants identified above:
AGENCYID = 88
SERVICESITEID = 88
MODIFYUSERID = Code passes the @UserID of user running the End of Day Command Line Batch Processing
MODIFYDTTM = Current date and time
|
Purge Process
This process purges records based on specific lengths of time and purges all household lock records.
Purge participant records
This step purges the following records if the value of the EOD_PROCESSMEMBERPURGES business rule equals "Y".
The PurgeEventLogs14DaysOld stored procedure was added to delete event log records in the EVENTLOG table that are over 14 days old and meet the following criteria:
(value @JobRunDate - EVENTLOG. CREATEDTTM) >14
EVENTLOG. SERVICESITEID = SERVICESITE. SERVICESITEID
The PurgeBusinessHours90DaysOld stored procedure was added to delete business hour records in the BUSINESSHOUR table that are over 90 days old and meet the following criteria:
(value @JobRunDate - BUSINESSHOUR. BUSINESSDATE) >90
BUSINESSHOUR. SERVICESITEID = SERVICESITE. SERVICESITEID
The PurgeAppointments3MonthsOld stored procedure was added to delete appointment records in the APPOINTMENT table that are over 3 months old and meet the following criteria:
(value of APPOINTMENT. APPOINTMENTDATE + 3 months) < @JobRunDate
APPOINTMENT. SERVICESITEID = SERVICESITE. SERVICESITEID
The PurgeHouseholdAlertsWithNoMembers stored procedure was added to delete alert records in the ALERT table for households that have no participants and meet the following criteria:
No records exist for the household in the MEMBER table
The PurgeClassEnrollments7MonthsOld stored procedure was added to delete class enrollment records in the CLASSENROLLMENT table that are over 7 months old and meet the following criteria:
(value of CLASSSCHEDULE. CLASSDATE + 7 months) < @JobRunDate
CLASSSCHEDULE. CLASSSCHEDULEID = CLASSENROLLMENT. CLASSSCHEDULEID
The PurgeHolidays90DaysOld stored procedure was added to delete holiday records in the HOLIDAY table that are over 90 days old and meet the following criteria:
(value of @JobRunDate - HOLIDAY. HOLIDAYDATE > 90
|
Purge household lock records
This step purges household lock records. It does not depend on a business rule.
The PurgeHouseholdLock stored procedure was added to delete all records in the HOUSEHOLDLOCK table.
|
Modified Procedures
The following stored procedures were updated.
A new parameter "@bulkInsertCount" with the default value NULL was added to the GetNextParticipantConsignmentId stored procedures to bulk block IDs for the given value. This provides the option to increment the last used ID in the PARTICIPANTCONSIGNMENT table by one or in bulk numbers.
A new parameter "@JobRunDate" was added to the uspSetFoodInstrumentAsPaid stored procedure to identify a list of food instruments to be adjusted to paid based on the date the End of Day Command Line Batch Processing is run.
|
Other Modifications
The BatchProcessor.dll was modified to use a more standard convention for obtaining the process start times from the operating system. All services in the End of Day Command Line Batch Processing were changed to log process start time in the SCHEDULEDJOBLOG table.
The following modifications were made to improve date comparisons:
End of Day Processing archive and purge processes used Datediff (month) for date comparisons, which was triggered on month boundaries and had issues identifying some records for processing. The new stored procedures UpdateArchiveInactiveParticipants, PurgeAppointments3MonthsOld, and PurgeClassEnrollments7MonthsOld use the DATEADD function when comparing the number of months to improve identification in End of Day Command Line Batch Processing.
Date comparisons that were using date and time were modified to use only date when comparing the number of days or months.
|
The following function was converted from in-line SQL code and views to a new stored procedure which was added to update vendor probation data:
The ProcessProbationUpdates stored procedure was added to retrieve all the rows from the VENDOR table when all of the following conditions apply:
The CURRENTSTATUS column value in the VENDOR table =7 (Probation).
The VENDORID column value in the VENDOR table matches the VENDORID column value in the PROBATION table.
The value of the ENDDATE column in the PROBATION table is not NULL and is less than the @JobRunDate.
When End of Day Command Line Batch Processing is run before 6pm, sets @JobRunDate to today’s date.
When End of Day Command Line Batch Processing is run after 6pm, sets @JobRunDate to tomorrow’s date.
Updates the following columns in the VENDOR table for each vendor probation updated and sets Vendor Status equal to "Enrolled".
VENDOR. CURRENTSTATUS = 3 (Enrolled)
VENDOR. MODIFYUSERID = Code passes the @UserId of user running the End of Day Command Line Batch Processing
VENDOR. MODIFYDTTM = Current date and time
This process does not remove the Probation follow-up activity from the Event Log screen in the Vendor Folder within the Vendor application module.
The Release 2.37 upgrade ASQL process was modified to search the COMMREQUEST table for a record where WICEOD is the value in the PROCESSNAME column and "EBT RECONCILIATION" is the value in the SYSTEMNAME column and to add a new record if it does not currently exist. The following values are added to the new record:
PROCESSNAME = WICEOD
SYSTEMNAME = EBT RECONCILIATION
SENDEMAIL = Y