Tip: Versioning for 71420 and 79413D
- Trang Riley (Unlicensed)
- Kenley Money
- Kathleen Hart (Unlicensed)
Number | 0017 |
|---|---|
Submitting Entities | 71420,79413D |
Add Date | Mar 1, 2019 |
Update Date | Mar 28, 2022 Oct 12, 2023 May 17, 2024 |
Update | Included in May 2024 Data Tips email |
Description | SEs 71420 and 79413D utilize a custom versioning application that does NOT replace claim lines with changes or replacements. |
Action / Resolution | To find total costs for the claim, all claim lines must be aggregated. The user would sum all values in the cost fields across all rows with the same claim number (MC004) and claim line (MC005) to get total claim line cost. The example below illustrates this approach. Claim line 1 for claim OEB00053171 has two rows, one with paid date 9/4/2018 and the second with paid date 2/28/2019. The paid amount for the claim line where paid date = 9/4/2019 is $2067.38. The paid amount for the claim line where paid date = 2/28/2019 is ($2067.38). Sum these two paid amounts to get the overall claim line paid amount of $0.00. Claim status (MC138) appears to indicate adjustments however it cannot be relied upon consistently to indicate adjustments. The same approach should be used for all $ fields. The example below includes co-pay and deductible, too. UPDATE: added code that can be used to sum $ fields. WITH Claim_Aggregates AS(
select
MC004 --Claim Number
,MC005 --Claim Line
,SUM(MC062) as MC062_Sum --Sum of Charge Amount
,SUM(MC063) as MC063_Sum --Sum of Paid Amount
,SUM(MC063C) as MC063C_Sum --Sum of Withhold Amount
,SUM(MC064) as MC064_Sum --Sum of Capitation Amount
,SUM(MC065) as MC065_Sum --Sum of CoPay Amount
,SUM(MC066) as MC066_Sum --Sum of Coinsurance Amount
,SUM(MC067) as MC067_Sum --Sum of Deductible Amount
,SUM(MC095) as MC095_Sum --Sum of COB Amount
,SUM(MC098) as MC098_Sum --Sum of Allowed Amount
,SUM(MC099) as MC099_Sum --Sum of Non-Covered Amount
,SUM(MC121) as MC121_Sum --Sum of Out of Pocket Amount
FROM [data request claims table]
where MC001='71420' AND RecordStatus != -1 -- Include only if RecordStatus is included with data.
-- Typically, only records with RecordStatus = 1 are given to data requesters
group by
MC004 --Claim Number
,MC005 --Claim Line
)
select
SC.MC001 -- SE ID
,SC.MC004 --Claim Number
,SC.MC005 --Claim Line
,SC.MC017 --Paid Date
,SC.MC062 --Charge Amount
,SC.MC063 --Paid Amount
,SC.MC063C --Withhold Amount
,SC.MC064 --Capitation Amount
,SC.MC065 --Copay Amount
,SC.MC066 --Coinsurance Amount
,SC.MC067 --Deductible Amount
,SC.MC095 --COB Amount
,SC.MC098 --Allowed Amount
,SC.MC099 --Non-Covered Amount
,SC.MC121 --Out of Pocket Amount
,CA.MC062_Sum --Sum of Charge Amount
,CA.MC063_Sum --Sum of Paid Amount
,CA.MC063C_Sum --Sum of Withhold Amount
,CA.MC064_Sum --Sum of Capitation Amount
,CA.MC065_Sum --Sum of CoPay Amount
,CA.MC066_Sum --Sum of Coinsurance Amount
,CA.MC067_Sum --Sum of Deductible Amount
,CA.MC095_Sum --Sum of COB Amount
,CA.MC098_Sum --Sum of Allowed Amount
,CA.MC099_Sum --Sum of Non-Covered Amount
,CA.MC121_Sum --Sum of Out of Pocket Amount
FROM [data request claims table] SC
join Claim_Aggregates CA ON CA.MC004 = SC.MC004 and CA.MC005 = SC.MC005
where SC.MC001='71420'
AND RecordStatus != -1 -- Include only if RecordStatus is included with data.
-- Typically, only records with RecordStatus = 1 are given to data requesters
select * from #tmp_71420_base2 where MC004 in ('ATL000000359800','ATL000000360100') order by MC004
select MC706, count(*)
FROM [APCD_DW].[dbo].[StageClaim]
where MC001='71420'
group by MC706
--MC706 = 116 - This versioning approach was created for submitting entities that have processing systems without claims versioning.
-- In this approach, when claim lines are changed, new version numbers are not created. All records are kept.
-- |
Category 1 | DSG |
Category 2 | Versioning |
Tip / Issue | Tip |
Example:
Claims |
|
|
|
|
| |
Claim Number (MC004) | Claim Line (MC005) | Paid Date (MC017) | Paid Amount (MC063) | Co-Pay (MC065) | Deductible (MC067) | Claim Status (MC138) |
OEB000531711700 | 1 | 9/4/2018 | $2,067.38 | $0.00 | $0.00 | O |
OEB000531711700 | 1 | 2/28/2019 | $(2,067.38) | $0.00 | $0.00 | A |
Claim Line Total | $0.00 | $0.00 | $0.00 |
| ||
OEB001380589000 | 13 | 9/12/2019 | $3,711.55 | $200.00 | $0.00 | O |
OEB001380589000 | 13 | 11/26/2019 | $(3,711.55) | $(200.00) | $0.00 | A |
Claim Line Total | $0.00 | $0.00 | $0.00 |
| ||
ATL000000359800 | 1 | 5/7/2020 | $(38.90) | $0.00 | $0.00 | A |
ATL000000359800 | 1 | 1/23/2020 | $ 73.94 | $40.00 | $0.00 | O |
Claim Line Total | $35.04 | $40.00 | $0.00 |
| ||
ATL002502087200 | 1 | 2/6/2020 | $0.49 | $0.00 | $49.28 | O |
ATL002502087200 | 1 | 4/13/2020 | $(0.49) | $0.00 | $(49.28) | A |
Claim Line Total | $0.00 | $0.00 | $0.0 |
| ||
OEB000476134000 | 12 | 8/3/2018 | $1,575.14 | $200.00 | $279.90 | O |
OEB000476134000 | 12 | 10/11/2018 | $(1575.14) | $(200.00) | $(279.90) | A |
Claim Line Total | $0.00 | $0.00 | $0.00 |
| ||