Issue: Versioning for MedImpact Entities
- Kenley Money
Number | 0137 |
|---|---|
Submitting Entity (SE) | 99MDI1, 99MDI2, 99MDI3, 99MDI4, 99MDI5 |
Add Date | Oct 4, 2023 |
Update Date |
|
Update | Included in the October 2024 Data tips email |
Description | 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, PC966, COUNT(DISTINCT PC004) AS CntOf,
SUM(COUNT(DISTINCT PC004)) OVER () AS CntOfTotalAffectedRecords
FROM [database].[dbo].[stagePharmacy]
WHERE PC001 LIKE '99MDI%'
GROUP BY PC001, PC107, PC018, PC026, PC058, PC966
HAVING COUNT(DISTINCT PC004) > 1
ORDER BY PC001, PC107, PC018, PC026, PC058, PC966 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 |