Issue: Versioning for MedImpact Entities

0137

99MDI1, 99MDI2, 99MDI3, 99MDI4, 99MDI5

04 Oct 2023

Included in the October 2024 Data tips email

MedImpact is 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.

Flagging Duplicates:

Where PC001 in (‘99MDI1’,’99MDI2’,’99MDI3’,’99MDI4’,’99MDI5’), group claims records by the fields in the table below. When all values are the same in all grouped records, use the record with the largest value in PC004.

In the 23B build, 143,110 claims were found to have duplicates.

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) and

The final record is the record in the group with the latest Claims Processing date (PC966).

Possible code:

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

Versioning

EBD

Issue

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