Vendor Management Reports General Reports Vendor List Description List all WIC-authorized active vendors in the selected Local Agency to be given to new clients so they know where to shop. Include Store name and address, phone number, and store type. Parameters Chain, Vendor Status (All, Active or Inactive), Local Agency, City, Peer Group, Store Type, Date, Sort By (‘Vendor ID’, ‘Vendor Name’ or City), Group By (‘Local Agency’ or ‘Peer Group’) Note: City is disabled the Local Agency value(s) is selected. After the Local Agency value(s) is selected, the City values that are available are presented based on the physical address of the vendors assigned to the selected Local Agencies. Record Selection / Filtering / Calculations Return all vendors associated with the selection parameters. Type = TIP type. Date = Use when determining selection for Vendor Status and Store Type. Columns If Group By = ‘Local Agency’ Local Agency – Vendor.LA_ID If Group By = ‘Peer Group’ Peer – VendorPGFactor.PG_ID based on date Any By parameter Vendor ID – Vendor.FFVendorID Vendor – Vendor.StoreName Street Address – VendorAddress.StreetAddrLine1 and StreetAddrLine2 (Physical Address) Location – VendorAddress.Location (Physical Address) City – VendorAddress.City (Physical Address) Zip – VendorAddress.ZipCode + ZipPlue4 (Physical Address) – Vendor.GeneralStoreAreaCd + Vendor. GeneralStorePhoneNr Store Type – VendorOperations.TIPStoreTypeCd based on date
Total Count – Count of vendors within the APL Type group. Detail Line Vendor ID – Vendor.FFVendorID. Vendor Name – Vendor.StoreName. Vendor Status – VendorStatusCode.StatusCodeDc VendorStatusCode.VendorStatusCd = the most recent VendorStatus.VendorStatusCd and VendorStatus.Vend_ID = Vendor.ID. Local Agency – LocalAgency.FFLocalAgencyID concatenated with LocalAgency.Name where LocalAgency.ID = Vendor.LA_ID. Sort Order APL Type, Vendor ID Primary Group (page break) APL Type Additional By N/A
Sort Order Peer Group Number, Count Reject %, Reject Reason % Total / Count Columns See columns State Totals Yes Primary Group (page break) None Additional Group By Peer Group Vendor Vendor Special Notes If a Vendor Chain is found in more than one peer group, then the Vendors under the Vendor Chain appear according to its peer group. This means that that a Vendor Chain may appear multiple times in this report.
Claim Submission Errors Report (Smart Card EBT / Online Xerox EBT / Online CDP EBT) Description This report allows WIC state office personnel to respond to questions regarding vendor claims where errors were found that affected paid amounts. Parameters Chain Based – Vendor Status Selection (Chain, Vendor Status and Vendors) Date, End Date Sort By (values of ‘Vendor’ or ‘Error’) Subtitle on EBT Transactions from <Start Date> through <End Date> Record Selection / Filtering / Calculations start and end date parameters, select the ARF files based on ARF settlement date (ARF.SettlementDt). Select any D4 record (with its E2 records) if the D4 or E2 has a non-zero MRC (ARFD4.MRCCd and ARFE2.MRCCd).
UPC/PLU List for Vendors This report is for EBT States only. Description This report contains the active UPCs/PLUs within a given time period. Parameters Category (All, Select One or more) – Active only (categories that have an Effective Date (FoodCategory.EffectiveDt) and End Date (FoodCategory.EndDt) within the Start and End Date parameters (inclusive)) Subcategory (All, Select One or more) – Do not show any 000 subcategories – Active only (subcategories that have an Effective Date (Product.EffectiveDt) and End Date (Product.EndDt) within the Start and End Date parameters (inclusive)) Start Date End Date Sub Titles Active from <Start Date> through <End Date> Record Selection / Filtering / Calculations Based on the entered subcategories, select all UPC/PLU (DetailProduct) that were effective for any time within the entered time period. This is based on the UPC/PLU dates (DetailProduct.EffectiveDt and DetailProduct.EndDt). Columns Category / Subcategory line: Category / Subcategory (Formatted as 99 XXX / 999 XXX) number> <category name> + ‘/‘ + <subcategory number> <subcategory name> on DP_ID, get FoodCategory.CategoryNr, FoodCategory.Name, Product.SubCategoryNr, Product.Name). Detail line: UPC/PLU – DetailProduct.UPC (formatted form) UPC Description – DetailProduct.Description. Effective Date – DetailProduct.EffectiveDt End Dates – DetailProduct.EndDt Sort Order Category / Subcategory Number, UPC/PLU Number Total / Count Columns None Totals None Primary Group (page break) None Additional Group By None Notes None
Print Columns Category / Subcategory line: Category / Subcategory (Formatted as 99 XXX / 999 XXX) number> <category name> + ‘/‘ + <subcategory number> <subcategory name> on DP_ID, get FoodCategory.CategoryNr, FoodCategory.Name, Product.SubCategoryNr, Product.Name). Detail line: UPC/PLU – DetailProduct.UPC (formatted number) UPC/PLU Description – DetalProduct.Description UOM – (width 6) DetailProduct.UnitofMeasureCd (USDA UOM Description) Units – DetailedProduct.QuantityBenefit Quantity – DetailProduct.BenefitQty – format to 2 decimal places. Active – See calculation Last Used – See calculation above. Average Price – See calculation above. Purchased Quantity – See calculation above. Total Paid – See calculation above. Sort Order Category / SubcategoryUPC/PLU Total / Count Columns None Totals None Primary Group (page break) None Additional Group By Category / Subcategory Notes Due to amount of displayed information (125% of normal report); the standard report font is not used. A smaller font is used. This small mockup may need to be viewed in MS Word’s zoom mode.
Average Amount - Sum of ARFE2.ClaimAmt / Sum of ARFE2.PurchasedUnits for all UPC/PLUs for the Units group. Detail line: UPC/PLU – DetailProduct.UPC UPC/PLU Description - DetailProduct.Description UOM – Display the Description from List Management where the LM Code = Code = ‘Container Size’ and the LM value = ProdContainer.ContainerSizeCd Units - DetailProduct.Quantity Target Price – Current DetailProductPeerGroup.PriceAmt Lift Factor – Current DetailProductPeerGroup.Factor NTE Price – Current DetailProductPeerGroup.PriceAmt times current DetailProductPeerGroup.Factor Units Purchased – Sum of ARFE2.PurchasedUnits Average Claim Amount – Sum of ARFE2.ClaimAmt / Sum of ARFE2.PurchasedUnits Claim / Target Price – (Average Claim Amount / Target Price) – 1.0 Claim / NTE Price – (Average Claim Amount / NTE Price) – 1.0 Sort Order Peer Group, Category, Subcategory, Units (rounded values), UPC sequence. Total /Count Columns None State Totals None Primary Group (page break) Peer Group Additional Group By Category, Subcategory, Units (the rounded values)