OP Operations Reports

MPSC WIC MIS SystemOP Operations Reports

MPSC WIC MIS System

Detailed Functional Design Document

OP Operations Reports

Revision Date:May 22, 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/4/2013 / Ciber Team / MPSC / Amendment 7 Updates
4/23/2014 / Ciber Team / MP User Group / Release 4.0 Task Order 3
3/27/2015 / Ciber Team / MP User Group / Release 5.0 Task Order 4 draft
4/14/2015 / Ciber Team / MP User Group / Release 5.0 Task Order 4
5/22/17 / CDP Team / MP User Group / Cover page, header, and footer mods for new M&E contractor

Table of Contents

1Introduction

2EBT Operations Reports

2.1EBT Card LA/Clinic Adjustments Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

2.2EBT Card Local Agency Count Summary Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

2.3EBT Cards Issued Disposition Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

2.4EBT Replacement Cards Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

2.5Received Not Verified Card Inventory Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

2.6Vault Snapshot Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

3Inventory Operations Reports

3.1Non-Serialized Inventory Order Received Summary Report

3.2Non-Serialized Inventory Order Received Detail Report

3.3Serialized Inventory Report

4Monitoring Operations Reports

4.1Clinic Monitoring Report

4.2Clinic Profile Report

4.3C&A Tailored Package Usage Report

4.4Family Customer Service Log Report

4.5Interstate Dual Participation Report

4.6Local Agency Eligibility Performance Report

4.7Local Agency Profile Report

4.8No Show Report

4.9Participant Violations, Sanctions and Claims Report

5Time Study Operations Reports

5.1Time Study FTE Report

5.2Time Study Payroll Report

5.3Non Scheduled Activity Report

6WIC Outreach Operations Report

6.1WIC Outreach Activity Report

6.2WIC Outreach Mailing Labels

6.3WIC Outreach Organization Listing

May 22, 2017Table of ContentsPage 1 of 52

MPSC WIC MIS SystemOP Operations Reports

1Introduction

This reports DFDD contains the reports related to the Operations Management functional area. The following set of reports represents existing reports from the transfer system as well as new reports requested during the MPSC project’s design phase.

2EBT Operations Reports

These reports are for EBT States only.

2.1EBT Card LA/Clinic Adjustments Report(Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / The EBT Card LA/Clinic Adjustments Report contains counts and details regarding EBT card inventory adjustments made by inventory points. These inventory adjustments track cards that were accepted into their inventory but were not available for use because something happened (i.e. lost, stolen, damaged). Inventory points are determined by the state during implementation. Inventory points may be set up at the local agency level, the clinic level or a combination of both.
Parameters / LA/Clinic (one or all) – Values determined based on a state’s setup of inventory points
Start and End Dates
State Totals (Yes or No)
Subtitle / <start date> - <end date>
Record Selection / Filtering / Calculations / Select all the adjustment transactions within the selection parameters. LA/Clinic comparison uses EBTStockLocalAdjustment.LA_ID and Cln_ID. Date comparision uses EBTStockLocalAdjustment.AdjustmentDt.
If State Totals = ‘Yes’
Select all the adjustment transactions based on date parameters so that counts can be done.
Print Columns / LA/Clinic (width 25): Depending on transaction
<Local Agency> (Format Z9 XXX) based on LA_ID (LocalAgency table)
<Clinic> (Format Z9 XXX / Z9 XXX) based on LA_ID and Cln_ID (first
is LocalAgencytable , second is Clinic table)
Reason (width 8): English of EBTStockLocalAdjustment.AdjustmentReasonCd
Count: EBTStockLocalAdjustment.AdjustmentQty
Date: EBTStockLocalAdjustment.AdjustmentDt
Start PAN (Mask of 99999999-9999999-9):
EBTStockLocalAdjustment.BeginCardNr
End PAN (Mask of 99999999-9999999-9):
Calculate based on EBTStockLocalAdjustment.BeginCardNr and
EBTStockLocalAdjustment.AdjustmentQty
Explanation (width 30): EBTStockLocalAdjustment.Explanation
Sort Order / LA/Clinic
Date
Total / Count Columns / LA/Clinic (if greater than 0)
Total: Count of all adjusted cards
Damaged: Count of damaged cards
Lost: Count of lost cards
Other: Count of cards with reason of ‘Other’
Stolen: Count of stolen cards
State Totals / Yes; same as LA/Clinic
Primary Group (page break) / None
Additional Group By / None

2.2EBT Card Local Agency Count Summary Report(Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / The EBT Card Local Agency Count Summary Report contains counts regarding how local agencies use their cards. Issued and Replaced are where the activity happened, not where the family currently is.
Parameters / Local Agency (select one, many or all)
Start and End Dates
Subtitle / <Start date> - <End Date>
Record Selection / Filtering / Calculations / Based on the local agency(s):
Total Cards Issued: Count all the card transactions (EBTCardTransaction) who have a card transaction within the parameter dates with have been initially or replacement issued (TransactionTypeCd of SETP or REPL).
Adjusted Stock: Sum quantities (AdjustmentQty) for all the inventory point (within local agency) adjustment transactions (EBTStockLocalAdjustment) who have a transaction type are either Damaged (DAMG), Stolen (STOL), Lost (LOST) or Other (OTHR).
Total Replaced Cards – WIC System Initiated: Count all the card transactions (EBTCardTransaction) with a transaction date within the parameter dates and a transaction type of replacement (TransactionTypeCd of REPL) and the EBT Host user ID is null (InitiatingUserID).
Total Replaced Cards – EBT Host Initiated: Count all the card transactions (EBTCardTransaction) with a transaction date within the parameter dates and a transaction type of replacement (TransactionTypeCd of REPL) and the EBT Host user ID is not null (InitiatingUserID).
Note: Replaced cards are counted into groups based on their reason (HotCardReasonCd).

• (Smart Card EBT) In the HotCardReason table (where the codes are selected from), GroupCd contains either Participant Damaged (P), Lost or Stolen (SL) or Other (O).
• (Online Xerox EBT) In the HotCardReason table (where the codes are selected from), GroupCd contains either Damaged (P) or Lost or Stolen (SL).

(Smart Card EBT) Permanent Hot Card Reasons (with grouping):
3000 Lost (SL)
3001 Stolen (SL)
3003 Counterfeit Card - PAN Confirmed (O)
3700 Failed to Function Prior to Issuance (O)
3702 Damaged - Delivered but Prior to Issuance (O)
3703 Damaged - After Issuance (P)
3704 Failed to Function after Issuance (P)
9999 MPSC System Use Only (O)
(Online Xerox EBT) Deactivated Card Reasons (with grouping):
C001 Lost (SL)
C002 Stolen (SL)
C003 Damaged (P)
(Online CDP EBT) Deactivated Card Reasons (with grouping):
C001 Lost (SL)
C002 Stolen (SL)
C003 Damaged (P)
C004 Returned (P)
C012 Failed to function prior issuance (O)
C014 Failed to function after issuance (O)
C015 Dual Participation (O)
Print Columns / Local Agency: LA ID + Local Agency Name
Total columns (labels):
Total Cards Used:
Total Cards Issued:
Adjusted Stock:
Total Replaced Cards – WIC System Initiated:
Total Replaced Cards – EBT Host Initiated:
Count:
Total Cards Used: Total Cards Issued + Adjusted Stock
Total Cards Issued: See above
Adjusted Stock: See above
Total Replaced Cards – WIC System Initiated: See above
Total Replaced Cards – EBT Host Initiated: See Above
Reasons for Replacement
Participant Damaged: See above
Lost: See above
Other: See above
Sort Order / Local Agency ID
Total / Count Columns / NA
State Totals / Yes
Primary Group (page break) / Local Agency (no page break)
Additional Group By / None

2.3EBT Cards Issued Disposition Report(Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / The EBT Card Issued Disposition Report shows the EBT cards issued based on selection parameters. Issued and Replaced are where the activity happened, not where the family currently is.
Parameters / Local Agency (one or all)
Clinic (one or all)
Start and End Dates
Subtitle / <start date> - <end date>
Record Selection / Filtering / Calculations / Select all the cards that have been issued (EBTCardTransaction.EBTTransactionTypeCd = ‘SETP’ or ‘REPL’) within the selection parameters.
Count all the cards that have been issued (EBTCardTransaction.EBTTransactionTypeCd = ‘SETP’ or ‘REPL’) based on the selection parameters.
This report excludes investigation family related data.
Print Columns / Local Agency: Local Agency of the Clinic
Clinic: Clinic of family of the card owner
# of Cards Issued: Calculated
PAN (Mask of 99999999-9999999-9): EBTCard.CardNr based on EBTCardTransaction.EBTC_ID
Economic Unit Name (width of 20): IncomeFamily.Description based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID
Economic Unit ID: IncomeFamily.EconomicUnitNr based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID
Foster Child (‘No’ or ‘Yes’): Participant.FosterChildIn based on junction record of ParticipantIncFamily based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID – If FosterChildIn true then ‘Yes’; Else ‘No’
Issue Type: (‘Initial’ or ‘Replace’): EBTCardTransaction.EBTTransactionTypeCd – When SETP then ‘Initial’; When REPL then ‘Replace’
Issued Date: EBTCardTransaction.TransactionDt
Issuing System:

• Display ‘WIC System’ if Issue Type is ‘Initial’
• Display ‘WIC System’ if Issue Type is ‘Replace’ and EBTCardTransaction.InitiatingUserID is null
• Display ‘EBT Host’ if Issue Type is ‘Replace’ and EBTCardTransaction.InitiatingUserID is not null

Sort Order / Local Agency ID
Clinic ID
Card Number
Total / Count Columns / Local Agency
# of Cards Issued: Count for Local Agency
# of Cards Issued: Count for Clinic
State Totals / # of Cards Issued: Count for State
Primary Group (page break) / Local Agency
Additional Group By / Clinic

2.4EBT Replacement Cards Report(Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / This is for EBT only states
The EBT Replacement Cards Report shows the details of replaced EBT cards with a comparision summary of active cards against issued and replaced based on selection parameters. Issued and Replaced are where the activity happened, not where the family currently is.
Note: An active card is one defined to have food with an FDTU that is within the date parameters. This implies that for a static month state, the first of a month will always be needed for the report to have meaning.
Parameters / Local Agency (one or all)
Clinics (One or all)
Start and End Dates
Subtitle / <start date> - <end date>
Record Selection / Filtering / Calculations / Select all the cards that have been replaced (EBTCardTransaction.EBTTransactionTypeCd = ‘REPL’) within the selection parameters.
Count all the active EBT cards. An active EBT Card has food issued (EBTIssuedFood.FDTU) based on the selection parameters.
Count all the EBT cards that were issued (EBTCardTransaction.EBTTransactionTypeCd = ‘SETP’ + ‘REPL’) based on the selection parameters.
Count all the EBT cards that were replaced (EBTCardTransaction.EBTTransactionTypeCd = ‘REPL’) based on the selection parameters.
This report excludes investigation family related data.
Print Columns / Local Agency: Local Agency of the Clinic
Clinic: Clinic of family of the card owner
<no label>: This is for the totals and percentages.
PAN (Mask of 99999999-9999999-9): EBTCard.CardNr based on EBTCardTransaction.EBTC_ID
Economic Unit Name (width of 20): IncomeFamily.Description based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID
Economic Unit ID: IncomeFamily.EconomicUnitNr based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID
Foster Child (‘No’ or ‘Yes’): Participant.FosterChildIn based on junction record of ParticipantIncFamily based on EBTCard.IncF_ID based on EBTCardTransaction.EBTC_ID – If FosterChildIn true then ‘Yes’; Else ‘No’
Reason (width of 25): EBTCardTransaction.HotCardReasonCd english translation using the HotCardReason table
Replace Date: EBTCardTransaction.TransactionDt
Issuing System:

• Display ‘WIC System’ if Issue Type is ‘Replace’ and EBTCardTransaction.InitiatingUserID is null
• Display ‘EBT Host’ if Issue Type is ‘Replace’ and EBTCardTransaction.InitiatingUserID is not null

Sort Order / Local Agency
Clinic ID
Card Number
Total / Count Columns / Local Agency
Total Active: Count of active cards
Total Issued: Count of issued cards
Issued %: Total Issued / Total Active
Replaced Cards: Count of replaced cards
Replaced %: Total Replaced / Total Active
Clinic
Total Active: Count of active cards
Total Issued: Count of issued cards
Issued %: Total Issued / Total Active
Replaced Cards: Count of replaced cards
Replaced %: Total Replaced / Total Active
State Totals / Yes;same lines as Local Agency and Clinic
Primary Group (page break) / Local Agency
Additional Group By / Clinic

2.5Received Not Verified Card InventoryReport (Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / This is for EBT only states
The Received Not Verified Card Inventory Report is used to show the state personnel what shipments were not verified by users at the EBT Card inventory points. Inventory points are determined by the state during implementation. Inventory points may be set up at the local agency level, the clinic level or a combination of both.
Parameters / None
Subtitle / None
Record Selection / Filtering / Calculations / Select all the shipments that have been received but are not verified. Select EBTStockStateShipment records where the VerifyStfpID = nulland the Receiveddt is not null’.
Print Columns / LA/Clinic (width 25): Depending on transaction
<Local Agency> (Format Z9 XXX) based on LA_ID (LocalAgency table)
<Clinic> (Format Z9 XXX / Z9 XXX) based on LA_ID and Cln_ID (first
is LocalAgencytable , second is Clinic table)
Shipped Date: EBTStockStateShipment.ShippedDt
Received Date: EBTStockStateShipment.ReceivedDt
Received by: StaffPerson.FirstName +’ ‘ MiddleInitial +’ ‘ + Last Name + ‘ ‘ + LastNameSuffix based on EBTStockStateShipment.ModifySrtpfID Quantity: EBTStockStateShipment.Quantity
Start PAN (Mask of 99999999-9999999-9):
EBTStockStateShipment.BeginCardNr
End PAN (Mask of 99999999-9999999-9):
Calculate based on EBTStockStateShipment.BeginCardNr and
EBTStockStateShipment.Quantity
Sort Order / LA/Clinic
Shipped Date
Total / Count Columns / LA/Clinic: Sum of cards
State Totals / None
Primary Group (page break) / None
Additional Group By / None

2.6Vault Snapshot Report(Smart Card EBT / Online Xerox EBT / Online CDP EBT)

Description / This is for EBT only states.
The Vault Snapshot Report contains counts and details regarding the cards that are found in an inventory point’s vault. There are two sections to this report. The firstsection contains the inventory point’s unassigned EBT WIC card inventory. The second section contains the outside cards that have been placed in the vault until they are removed according to the state’s procedures.
Parameters / Inventory Point (select one) - Values determined based on a state’s setup of inventory points
Subtitle / Inventory point
Record Selection / Filtering / Calculations / First section: Based on the inventory point, select all the card transactions (EBTCardTransaction) whose latest status is type (EBTTransactionTypeCd) of Received (“RECV”) or Re-Add (“RADD”).
The PANs (EBTC_ID to EBTCard.CardNr) are processed back into sets of continuously numbered cards. For instance (using a 2 digit card number): cards 15, 16, 17, 18, 19, 20, 23,24 would show as cards 15–20 (count of 6) and cards 23-24 (count of 2).
Second section: Based on the inventory point, select all the outside card records (EBTStockLocalOutsideCard).
Print Columns / Unassigned WIC EBT PANs (section):
Start PAN: (format 99999999-9999999-9) First PAN in a sequenced
set.
End PAN: (format 99999999-9999999-9) Last PAN in a sequenced set.
Count: Count of PAN’s in sequenced set
Outside Card (section):
Card ID: EBTStockLocalOutsideCard.CardID
Card Type: EBTStockLocalOutsideCard.TypeCd (English)
Count: blank (for total line)
Entry Date: EBTStockLocalOutsideCard.EntryDt
Sort Order / Unassigned WIC EBT PANs (section): Start PAN
Outside Card (section): Card ID
Total / Count Columns / Unassigned WIC EBT PANs (section): Count
Outside Card (section): Count
State Totals / None
Primary Group (page break) / None
Additional Group By / None

3Inventory Operations Reports

3.1Non-Serialized Inventory Order Received Summary Report

Description / The Non-Serialized Inventory Order Receipt Summary Report is used to track the amount of non-serialized inventory received at the clinics.
Parameters / Start Date
End Date
Category (one, multiple or all)
Record Selection / Filtering / Calculations / Select all the Non-Serialized Inventory Received records that match the category(s) selected and whose Received Date is within the start and end date inclusively. Sum the Amount Received for the Inventory Items.
Print Columns / Category
Inventory Item
Count
Sort Order / Category (code display sequence)
Inventory Item (display sequence)
Total / Count Columns / Count
State Totals / The detail records are statewide counts.
Primary Group (page break) / None
Additional Group By / Category

3.2Non-Serialized Inventory Order Received Detail Report

Description / The Non-Serialized Inventory Order Received Detail Report is used to track the amount of non-serialized inventory received at each clinic with clinic totals.
Parameters / Start Date
End Date
Category (one, multiple or all)
Record Selection / Filtering / Calculations / Select all the Non-Serialized Inventory Received records that match the category(s) selected and whose Received Date is within the start and end date inclusively. Sum the Amount Received for the Inventory Items for each clinic.
Print Columns / Category (Inventory Category Name)
Inventory Item (Inventory Name)
Clinic (LA ID, Clinic ID and Clinic Name)
Clinic Total
State Total
Sort Order / Category (code display sequence)
Inventory Item (display sequence)
Clinic (LA ID / Clinic ID)
Total / Count Columns / Clinic Total (total for an inventory items within a clinic)
State Total (total for an inventory item)
State Totals / Yes
Primary Group (page break) / None
Additional Group By / Inventory Item