CalMHSA 709 – County 270 Batch Extract Report

Report Description

This report is designed to generate a standardized batch file that county users can submit to DHCS for bulk eligibility verification. The report’s output is formatted to meet DHCS requirements for 270 eligibility transactions, allowing it to be exported and submitted with minimal modification.

DHCS limits bulk eligibility submissions to a maximum of 200 records per file. To accommodate this requirement, the report automatically segments the data into groups of 200 records when exported to Excel. Each 200 grouping is placed on a separate worksheet, allowing users to easily copy and save each worksheet as an individual file for submission.

Report Name

Menu Path

Client Based

Report RDL Name

CalMHSA 709 – County 270 Batch Extract Report

 CalMHSA 709 – County 270 Batch Extract Report (My Office)

N

RDLCALMHSA_709_CountyBatExtract

Parameters

Data Type

Hidden

Comments

Start Date (Within last 12 Months)

Date

N

A date filter that allows users to specify the beginning of the date range that will be used to pull Services records base on their DateOfService values.

Special Note: If the parameter is set to NULL then the report will default the beginning of the data range to be a year prior to the date of the report being ran.

End Date

Date

N

A date filter that allows users to specify the end of the date range that will be used to pull Services records base on their DateOfService values.

Special Note: If the parameter is set to NULL then the report will do one of the following:

·       If the Start Date parameter is also NULL then report will dynamically default the end of the date range to each Services’ DateOfServices (Basically will be pulling services whose DataOfService is on a future Date along with all services in the last year of the date of the report being ran)

·       If the Start Date parameter is not also NULL then report will default the end of the date range to be the date of the report being ran.

Status of Service

Single Select

N

A single select that allows users to to specify whether the report will display “Show” or “Scheduled” status services

Program

Multiple Select

N

A multiple select that allows users to specify which program association the report will use when pulling data from Services data

ClinicalDataAccess GroupId

Integer

Y

Passed by system at report run time based on currently logged in Staff

ExecutedByStaffId

Integer

Y

Passed by system at report run time based on currently logged in Staff

DataSets

Form(s)

CDAG enforced

Comments

CountyBatchExtractDataset

Service Notes

Y

This dataset is the primary dataset for the report. It is used to populate what is shown on the report.

Pulls data from the following tables:

·       Services

·       Clients

·       Programs

·       ClientCoveragePlans

·       CoveragePlans

Pulls data based on the following criteria:

·       All Services records being pulled are non-softdeleted

·       Only pulls Services records whose Status value matches what is selected in the Status parameter

·       Only pulls Services records whose ProgramId value is within what is selected in the Programs parameter

·       Only pulls Services records whose DateOfService falls within the defined date range

·       Only pulls Services records whose client are actively enrolled in the selected program(s) and DateOfService is within the actively enrollment period

Special Notes regarding the report:

The dataset restricts results to only Medi-Cal clients by evaluating ClientCoveragePlans and CoveragePlans:

·       Only CoveragePlans where MedicaidPlan = ‘Y’ are included

·       Only InsuredId values that:

·       Start with ‘9’

·       Have a letter in the 9th position

·       Do not start with ‘99999999’ (to exclude placeholder CINs)

·       The dataset selects only the most recent CIN per client using ROW_NUMBER logic (partitioned by ClientId and ordered by ClientCoveragePlanId descending)

RowGroup is calculated to group records into batches of 200:

·       Every 200 records are assigned the same RowGroup value

·       This is determined using ROW_NUMBER logic over SubscriberId

StartDateRestriction field indicates whether the StartDate parameter is valid:

·       1 = StartDate is outside the allowed 1-year range (invalid)

·       0 = StartDate is within the allowed range (valid)

The StartDate and EndDate interactions are the following:

·       If StartDate is NULL, defaults StartDate to 1 year prior to today

·       If both StartDate and EndDate are NULL, uses the Service’s DateOfService.

·       If StartDate is not NULL and EndDate is NULL, default EndDate as today’s date

ProgramDataSet

Programs (Admin)

Dataset is used to populate the Program parameter. Pulls data from the StaffClinicalDataAccessGroup table with LEFT OUTER JOINs to Programs and ClinicaldataAccessGroupPrograms table.

Pulls data base on the following criteria:

·       All records from all involve tables are non-softdeleted.

·       Only pull Programs records whose Active column value is within what is selected in the Active Program parameter.

·       Only pull StaffClinicalDataAccessGroup records whose ClinicalDataAccessGroupId matches with the value within ClinicalDataAccess GroupId parameter

·       Only pull StaffClinicalDataAccessGroup records whose StaffId matches with the values within the ExecutedByStaffId parameter

ServiceStatusDataSet

GlobalCodes (Admin)

Dataset is used to populate the Status of Service parameter. Pulls data from the GlobalCodes table.

Pulls data base on the following criteria:

·       Only pull GlobalCodes data whose codename is either “Show” or “Scheduled”

·       Only pull GlobalCodes data whose category is “SERVICESTATUS”

GetCountyLogo

N/A

N/A

County logo image for display on page header

Default User Roles