CalMHSA 703 – Annual Void Report

Report Description

This report is used for generating the Annual Void Report to analyze voided claims, payment recoveries, and provider details efficiently.

Report Name

Menu Path

Client Based

Report RDL Name

CalMHSA 703 – Annual Void Report

CalMHSA 703 – Annual Void Report (My Office)

No

RDLCALMHSA_703_AnnualVoid

Parameters

Data Type

Hidden

Comments

Start Date

Date

N

A Date filter that allows users to specify the beginning of the data range of the ERClaimLineItems processed dates for the report to use

End Date

Date

N

A Date filter that allows users to specify the end of the data range of the ERClaimLineItems processed dates for the report to use

ShowHeader/Footer

Single Select Dropdown

N

A single select dropdown that allows users to choose whether to have the report include the report’s header and footer or not. This function mainly allows users to export the report in a more “cleaner” format.

Recovery Type

Single Select Dropdown

N

A single select dropdown that allows users determine whether the column “Recovery Type” will display “42 CFR, section 438.608(d)” or “All other Medi-Cal”.

Service Area

Single Select Dropdown

N

A single select dropdown that allows users to choose whether to have the report only pull MH or DMC programs

DataSets

Form(s)

CDAG enforced

Comments

Primary

Charges/Claims

N

This SQL query retrieves and aggregates data related to voided claims for reporting purposes. It joins multiple tables to compile detailed information about voided claim line items, associated payments, provider NPIs, client identifiers (CINs), and service details.

Breakdown of Query Components:

Voided Claims (VoidedClaims_CTE)

·       Extracts claim line items that have been voided.

·       Excludes original claims to prevent duplicates.

Provider NPI Mapping (Provider_NPI_CTE)

·       Links provider staff IDs to their corresponding National Provider Identifiers (NPIs).

·       Filters out inactive records and non-relevant email domains.

Claim Payment Categorization (ERClaimSpplAmounts_CTE)

·       Retrieves claims payment data and categorizes it into coverage amount, allowed amount, federal funding (FFP), state general funds, county portions, and other payments.

·       Uses a pivot table to structure different funding sources.

Client to CIN Mapping (Cin_xref_CTE)

·       Matches clients to their corresponding Client Identification Numbers (CINs).

·       Ensures CINs are valid (9-character length and formatted correctly).

Final Data Selection and Joins:

·       Retrieves voided claim data along with ER claim details, service area information, provider NPIs, payment categories, and client CINs.

·       Filters results based on date range, service area, and claim status.

·       Orders results by service area and claim line item ID.

Key Filters and Conditions:

·       Only includes voided claims (VoidedClaim = ‘Y’).

·       Considers only processed claims (ClaimStatusCode = 1).

·       Limits results to a specified date range (ProcessedDate BETWEEN @StartDate AND @EndDate).

·       Filters by a specific service area (ServiceAreaId = @ServiceArea).

Output Fields:

·       Claim line item details (Billing Code, Charge Amount, Voided Claim status).

·       ER claim details (Processed Date, Paid Amount, Claim Status Code, Payer Claim Number).

·       Provider NPI information.

·       Service details (Service ID, Date of Service).

·       Categorized claim payments (Coverage, Allowed, Federal/State/County portions, etc.).

·       Client Identification Number (CIN).

Sorting Order:

·       Sorted by Service Area ID, Claim Line Item ID, and ER Claim Line Item ID.

ServiceArea

N/A

N/A

This query is used to populate the “Service Area” filter. It pulls ServiceAreaId and ServiceAreaName from the ServiceAreas table.

GetCountyLogo

N/A

N/A

County logo image for display on page header

Default User Roles