Issue: Versioning for MedImpact Entities

Number	0137
Submitting Entity (SE)	99MDI1, 99MDI2, 99MDI3, 99MDI4, 99MDI5
Add Date	Oct 4, 2023
Update Date
Update	Included in the November 2024 Data tips email
Description	MedImpact was a new pharmacy submitter in the 23B build. No versioning rules were provided. The MedImpact system creates a new claim number for every transaction, whether it is a duplicate, reversal, or back-out. This methodology will help manage MedImpact versioning until it is incorporated into a future database build. Manual Versioning (updated 11/12/2024): Manual versioning rules have been updated by the submitter. Group on the following fields: Member ID (PC107), Pharmacy number (PC018), Drug Name (PC026), script number (PC058). The final record is the record in the group with the latest Claims Processing date (PC966). Possible code to find the versioned records: `SELECT PC001, PC107, PC018, PC026, PC058, COUNT(DISTINCT PC966) AS CntOf, SUM(COUNT(DISTINCT PC966)) OVER () AS CntOfTotalAffectedRecords FROM [database].[dbo].[stagePharmacy] WHERE PC001 LIKE '99MDI%' GROUP BY PC001, PC107, PC018, PC026, PC058 HAVING COUNT(DISTINCT PC966) > 1 ORDER BY PC001, PC107, PC018, PC026, PC058` Flagging Reversals: Where PC001 in (‘99MDI1’,’99MDI2’,’99MDI3’,’99MDI4’,’99MDI5’) group claims records by the fields in the table below but do not include PC017 - PAID Date (sometimes reversals come after initial payment so that value is different) When the sum of the dollar fields (PC036, PC037, PC039, PC040, PC041, PC042, PC069) across all claim lines in the group = 0, don’t use any of the claim lines for that grouping. In other words, if a group has 2 claim lines, the sum of PC036 between the claim lines must = 0, the sum of PC037 between the claim lines must = 0, etc. In the 23B build, 2622 claims were found to have duplicates. Possible code: SELECT PC107, PC018, PC026, PC028, PC033, PC034, PC032, PC058, count(distinct concat(PC036, PC037, PC039, PC040, PC041, PC042, PC069)) as CntOfDistPdAmt, SUM(COUNT (distinct concat(PC036, PC037, PC039, PC040, PC041, PC042, PC069))) OVER () as CntOfTotalDistPaidDate FROM [database].[dbo].[Pharmacy] WHERE PC001 LIKE '99MDI%' GROUP BY PC107, PC018, PC026, PC028, PC033, PC034, PC032, PC058 HAVING count(distinct PC036) > 1 ORDER BY PC107, PC018, PC026, PC028, PC033, PC034, PC032, PC058 --2622 groups, 5346 affected records.
Active/Resolved	Active
Action / Resolution
Category 1	Versioning
Category 2	EBD
Tip / Issue	Issue

Grouping Fields

PC018	Pharmacy Number
PC017	Paid Date
PC026	Drug Code
PC058	Script Number
PC032	Date Prescription Filled
PC033	Quantity Dispensed
PC034	Days Supply
PC035	Charge Amount
PC036	Paid Amount
PC037	Ingredient Cost/List Price
PC039	Dispensing Fee
PC040	Copay Amount
PC041	Coinsurance Amount
PC042	Deductible Amount
PC069	Member Total Out of Pocket Amount
PC107	Member ID