Description | MedImpact is 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. Flagging Duplicates: Where PC001 in (‘99MCD1’,’99MCD2’,’99MCD3’,’99MCD4’,’99MCD5’), 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.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: | Code Block |
|---|
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 (‘99MCD1’‘99MDI1’,’99MCD2’’99MDI2’,’99MCD3’’99MDI3’,’99MCD4’’99MDI4’,’99MCD5’’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: | Code Block |
|---|
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.
|
|
|---|
