Issue: Member/Claim Linkage for 99HSM1





Number

0035

Submitting Entity (SE)

99HSM1

Add Date

Aug 30, 2019

Update Date

Jun 19, 2020 Dec 15, 2020 Sep 4, 2024 Oct 25, 2024 Dec 9, 2024

Update

Included November 2024 Data Tips email.

Added SQL code example.

This issue has been resolved moving forward (from the update date) however it still exists within data pulled from past versions of the APCD. This issue will remain active until previously fulfilled data files are no longer being used. Also, the original title referenced submitting entity 99HSM1.

Description

UPDATE for Build 24B forward:

Member IDs on Medical (MC137) and Pharmacy (PC107) Claims have been corrected and now match Member (ME107) data.

Subscriber IDs on Member (ME117) data and Pharmacy (PC108) Claims have been corrected and now all have matches.

Subscriber IDs on Member (ME117) data and Medical (MC141) Claims have been corrected and now all have matches.

For data requests pulled from APCD builds 24A and earlier:

Carrier specific unique member id values are formatted differently on enrollment data and medical/pharmacy claims data for Submitter 99HSM1.  Enrollment data and claims data cannot be linked together at this time using the carrier specific unique member ID. However, there is a workaround that can be employed to match member data to claims data: Concatenate MC006 (PC006) with MC137 (PC107), separating the values with a hyphen to create the new claims level member id.  Join this to the member table on ME107 to find matches.

 The Arkansas APCD team is working with the submitter to resolve this issue.

Example joining member to pharmacy claims or medical claims data.

SELECT COUNT(DISTINCT ME107) FROM APCD_DW.dbo.Stagemember A INNER JOIN APCD_DW.dbo.StageClaim B ON A.ME107 = MC006+'-'+MC137 AND A.ME001 = B.MC001 WHERE MC137 IS NOT NULL AND ME001 = '99HSM1' SELECT COUNT(DISTINCT ME107) FROM APCD_DW.dbo.Stagemember A INNER JOIN APCD_DW.dbo.StagePharmacy B ON A.ME107 = PC006+'-'+PC107 AND A.ME001 = B.PC001 WHERE PC107 IS NOT NULL AND ME001 = '99HSM1'

Active / Resolved

Active

Action / Resolution

Data issues were partially corrected.

Category 1

Member/Subscriber IDs

Category 2

Member to Claim Linkage

Tip / Issue

Issue