Clinic Services Batch Processes

MPSC WIC MIS System Clinic Services Batch Processes

MPSC WIC MIS System

Detailed Functional Design Document

Revision Date: March 23, 2017

Maintained by:

Products and company names mentioned herein may be the trademarks or registered trademarks of their respective owners. Unmentioned brands or company names are also respected with their own registered trademarks. Some content may be a direct translation from their Web sites.

Document Revisions

Revision Date / Updated By / Requested By / Description of Revision
5/24/2013 / Ciber Team / MPSC / Amendment 7 Updates
4/29/14 / Ciber Team / MPSC / Release Version 3.7.43.0 Updates
12/24/14 / Ciber Team / MP User Group / Release 4.1 Construction Clarifications
12/28/15 / Ciber Team / MP User Group / Release 5.3 updates
11/2/2016 / Ciber Team / MP User Group / Release 5.06 Updates
3/23/17 / CDP Team / MP User Group / Cover page, header, and footer mods for new M&E contractor

Table of Contents

1 Change Pending Participants to Ineligible 6

1.1 Summary 6

1.2 Attributes 6

1.3 Processing Logic Details 6

2 Clear Expired Transfer Benefits (EBT Only) 7

2.1 Summary 7

2.2 Attributes 7

2.3 Processing Logic Details 7

3 Disqualify Future Dated Sanctions 8

3.1 Summary 8

3.2 Attributes 8

3.3 Processing Logic Details 8

4 Dual Participation Extract 9

4.1 Summary 9

4.2 Attributes 9

4.3 Processing Logic Details 9

4.4 Record Layouts 10

5 Immunization File (UT) 13

5.1 Summary 13

5.2 Attributes 13

5.3 Processing Logic Details 13

5.4 File Layout – File sent to USIIS 14

6 Infant to Child 18

6.1 Summary 18

6.2 Attributes 18

6.3 Processing Logic Details 18

7 Participant Characteristics Report / Minimum Data Set Extract 19

7.1 Summary 19

7.2 Attributes 19

7.3 Processing Logic Details 19

7.4 Record Layouts 21

7.5 Codes Table 51

7.6 Food Cross Reference 54

8 Pediatric Nutrition Surveillance System (PedNSS) Extract 56

8.1 Summary 56

8.2 Attributes 56

8.3 Processing Logic Details 57

8.4 Record Layouts 58

8.5 Codes Table 67

9 Pregnancy Nutrition Surveillance System – (PNSS) Extract 70

9.1 Summary 70

9.2 Attributes 70

9.3 Processing Logic Details 71

9.4 Record Layouts 72

9.5 Codes Table 92

10 Statewide Sketch 96

10.1 Summary 96

10.2 Attributes 96

10.3 Processing Logic Details 97

11 Terminate Categorical Eligibility End Date 99

11.1 Summary 99

11.2 Attributes 100

11.3 Processing Logic Details 100

12 Terminate Certification End Date Elapsed 102

12.1 Summary 102

12.2 Attributes 102

12.3 Processing Logic Details 102

13 Terminate Disqualified Participants 104

13.1 Summary 104

13.2 Attributes 104

13.3 Processing Logic Details 104

14 Terminate No FB Issuance 106

14.1 Summary 106

14.2 Attributes 106

14.3 Processing Logic Details 106

15 Terminate Provisional Certs 108

15.1 Summary 108

15.2 Attributes 108

15.3 Processing Logic Details 108

16 WICHealth.org 110

16.1 Summary 110

16.2 Attributes 110

16.1 Processing Logic Details 111

March 23, 2017 Table of Contents Page 3 of 113

MPSC WIC MIS System Clinic Services Batch Processes

1 Change Pending Participants to Ineligible

1.1 Summary

This batch process is used to change pending participants to ineligible after [system parameter] days.

1.2 Attributes

How Initiated / Batch
Run Frequency / Once monthly, last day of month
Affected Data / Application, ParticipantStatus
Staff / Central Office and Clinic staff
External Organizations / None
Period Covered / Recorded dates are compared to first day of current month
Inputs / Applicant records with a pending WIC Status
Outputs / ParticipantStatus records
Prerequisites
Effect on Other Processes / Ineligible Report

1.3 Processing Logic Details

Look for participants with ParticipantStatus.WICStatusCd = P.

Look at the Application.ModifyDt. Find records where this date is greater than the system parameter, Batch.NumberofDaysBeforeInactivatingPendingApplicants and the participant does not have a future appointment.

When the group has been determined…

¨ Update the Application record by setting the Application.ApplicationEndDt to LastDayofLastMonth.

¨ Add a new ParticipantStatus record with WICStatusCd = I, RecordedDt = LastDayofLastMonth, ChangeReasonCd = I (Pending Status Expired).

2 Clear Expired Transfer Benefits (EBT Only)

2.1 Summary

This batch process is used to clear benefits periods that were available for transfer but have expired. These benefits are no longer available for transfer.

2.2 Attributes

How Initiated / Batch
Run Frequency / Daily
Affected Data / IncomeFamily
Staff / Clinics
External Organizations / None
Period Covered / Date comparison
Inputs / Income Family records with benefits available for transfer
Outputs / NA
Prerequisites / None
Effect on Other Processes / None

2.3 Processing Logic Details

Look for Economic units with IncomeFamily.TransferFirstUseDt is older than current date.

When the set has been determined…

¨ Update the IncomeFamily records by setting TransferAvailDtTm, TransferFirstUseDt and TransferLastUseDt to nulls.

3 Disqualify Future Dated Sanctions

3.1 Summary

This batch process is used to disqualify participants that have a future dated disqualification sanction that is now in the past.

3.2 Attributes

How Initiated / Batch
Run Frequency / Daily
Affected Data / Certification Record, Sanction Record, WIC Status
Staff / Central Office and Clinic staff
External Organizations / None
Period Covered / Recorded dates are compared to today’s date
Inputs / Participant records with a disqualification sanction start date less than today’s date and the disqualification sanction end date is greater than today’s date.
Outputs / Disqualification Report
Prerequisites / Future dated sanction record for disqualification
Effect on Other Processes / Disqualifications effect FB Issuance.

3.3 Processing Logic Details

Read transactional tables and disqualify any participants with a future dated disqualification sanction that is now in the past and the disqualification sanction end date is greater than today’s date. (ParticipantSanction.SanctionTypeCd = DISQ) Recorded dates are compared to today’s date.

Make the following updates:

Create a new WIC Status record.

ParticipantStatus.WICStatusCd = D
ParticipantStatus.RecordedDt = date batch job is run
ParticipantStatus.ChangeReasonCd = DQST

4 Dual Participation Extract

Design based on: MPSC design sessions and final design review, 12/2007

4.1 Summary

The Dual Participation Extract is a file used to identify individuals who are receiving benefits from the WIC program in multiple states. The file is generated by the state and shared with other state WIC programs. This document specifies the data elements included in the file, the format of the data, where the data is located in the database and the corresponding fields in the application user interface.

4.2 Attributes

How Initiated / Scheduled, Batch
Run Frequency / Quarterly
Affected Data / None
Staff / State Staff
External Organizations / Departments of Health in various other states
Period Covered / Snapshot in time (participant status at time of report)
Inputs / Infant, Child and Woman data
Outputs / Dual Participation report (ASCII flat file with fixed length fields)
Prerequisites / None
Effect on Other Processes / None

4.3 Processing Logic Details

On a quarterly basis, the state’s data system converts the result of a data query into a fixed length text file for other State WIC programs. The file contains information on all WIC participants who have a status of “active” at the time the query is executed. While the extract is scheduled as a quarterly report, all data elements are point-in-time (there is no requirement for summation or analysis of activity over the quarter). If a specific year and month are not designated in the extract parameters table then the extract is based on the prior month. When a specific month is designated it applies only to the next execution. The month parameter is then blanked so that the execution following that uses the default.

This batch process excludes investigation family related data.

The query to extract the data required for this report is contained in a stored procedure within the WIC database. The query populates a table within the WIC database. The data is then be copied to a text file, in fixed length format, through a SQL Server Integrations Services (SSIS) package.

All data fields are left justified and, when applicable, padded with spaces to fill out remaining positions in the fields. Data that does not fit in the allocated field lengths is truncated.

A SQL job is provided to allow automated creation of the report on the following dates:

Jan 10

April 10

July 10

Oct 10

Delivery of the file is done manually (by mailing a CD, transmitting the file via FTP, etc.).

The table below outlines the record layout, data elements, field types, the data system table and column that are used to generate the flat file and the location within the application user interface where the corresponding data can be found.

4.4 Record Layouts

Field Seq. / Field Size / Field Position / Field Name / Field Type / Data System Source / Where Collected in the UI /
1 / 2 / 1-2 / Participation Category / A / ParticipantType.ParticipantTypeCD
Acceptable values:
I=Infant
C=Child
P=Pregnant woman
B=Breastfeeding woman
N=Non breastfeeding woman / Participant Category field
2 / 30 / 3-32 / Last name / A / FamilyMember.LastName / Member screen, Last Name text box.
3 / 30 / 33-62 / First name / A / FamilyMember.FirstName / Member screen, First Name text box.
4 / 30 / 63-92 / Street address / A/N / FamilyAddress.StreetAdddrLine1
Parse field and report first line only (up to first <br>) / Contact/Address. “Address Line 1” from the most current of Physical Address group or Mailing Address group. The most current address is determined by the Effective date field. Future addresses are ignored.
5 / 30 / 93-122 / City address / A / FamilyAddress.City / Contact/Address. “City” from the most current of Physical Address group or Mailing Address group. The most current address is determined by the Effective date field. Future addresses are ignored.
6 / 2 / 123-124 / State / A / FamilyAddress.State / Contact/Address. “State” from the most current of Physical Address group or Mailing Address group. The most current address is determined by the Effective date field. Future addresses are ignored.
7 / 2 / 125-126 / County / N / County.CountyNumber / The County Number is not displayed in the UI. The County name (used to cross reference the number) is found on
Contact/Address screen. “County” from the most current of Physical Address group or Mailing Address group. The most current address is determined by the Effective date field. Future addresses are ignored.
8 / 8 / 127-134 / Date of birth / N / FamilyMember.BirthDt
(MMDDYYYY) / Member screen designated in the UI as “Date of Birth”
9 / 1 / 135 / Sex / A / Participant.SexCd
Acceptable values:
F=Female
M=Male / Member screen, Field labeled “Sex”
10 / 8 / 136-143 / Certification date / N / Certification.StartDt
(MMDDYYYY) / Certification, the most recent date listed in the drop-down box labeled “Certification Dates”
11 / 8 / 144-151 / Last food benefit issue date / N / FI.FirstUseDt
(MMDDYYYY) / FI History, most recent FI Number in list. “Detail for FI# xxxx” form appears. “First Day to Use” contains the date provided in this field.
12 / 30 / 152-181 / Guardian last name / A / FamilyMember.LastName where ParentGuardianIn = 1
Only complete for Infants and Children. For all others leave blank. / Member screen for Endorser, “Last Name” text box.
13 / 30 / 182-211 / Guardian first name / A / FamilyMember.FirstName where ParentGuardianIn = 1
Only complete for Infants and Children. For all others leave blank. / Member screen for Endorser, “First Name” text box.

5 Immunization File (UT)

Design based on: File sent to USIIS (Utah State Immunization System), Utah WIC Program, 11/21/2006, with file updates 8/25/2008

5.1 Summary

This file is created daily to be shared with USIIS. This file contains active infants and children whose records were created or updated through the WIC data system. This file is created through a batch process and is saved to a standard location. The file is named patient.mmddyyyy.dat. UT staff are responsible for moving this file to the FTP site where it can then be shared with USIIS.

This file is supplied using double quotes and is comma delimited.

5.2 Attributes

How Initiated / Batch process
Run Frequency / Daily
Affected Data / Data defined in file layout
Staff / State office
External Organizations / USIIS
Period Covered / NA
Inputs / Newly created or updated active infant and child records, excluding all investigator family participants
Outputs / File
Prerequisites
Effect on Other Processes

5.3 Processing Logic Details

On a daily basis, the Utah WIC data system creates a file to be shared with the Department of Health Immunization program. This file contains information on all active infants and children whose records were created or updated through the WIC data system not previously in the table for this batch process since it was last modified (Participant.ModifyDt is greater than the batch job Start Date). All data elements are point in time.

This batch process excludes investigation family related data.

The query to extract the data required for this report is contained in a stored procedure within the OLTP database. The query populates a table within the OLTP database. The data is then copied to a text file, in double quote, comma delimited format, through SQL Server Integration Services (SSIS).

Delivery of the file is done manually transmitting the file via FTP.

5.4 File Layout – File sent to USIIS

Field Seq. / Field Size / Field Position / Field Name / Field Type / Data System Source / Where Collected in the UI /
1 / 10 / NA / Patient ID / N / Person ID
FamilyMember.FFMemberID / Family Screen
2 / 7 / NA / Provider ID / N / Always fill as “825” / Family Screen
3 / 30 / NA / First Name / A/N / Participant First Name
FamilyMember.FirstName / Member Screen
4 / 1 / NA / Initial / A/N / Participant Middle Initial
FamilyMember.MiddleName (first letter only) / Member Screen