Transcript Detailed F134 Presentation

Downloading and
Formatting
DHCS F134 Files
Presentation by Butte County Department of
Behavioral Health
6/7/16
1
Initial Setup
To begin the initial setup, open and
save a new Access database
Adding Julian calendar conversion table
2
Importing the Julian Calendar-1
With the Access
database open,
import the Julian
Calendar. Click
'External Data'
tab, ‘Excel' - Use
the Import
Wizard. Browse
for the file and
select it, use
“Import” option,
then click “OK.”
3
Importing the Julian Calendar-2
Select the
“Show
Named
Ranges"
option and
the tab to
import, then
click “Next."
4
Importing the Julian Calendar-3
Check mark
the “First
Row
Contains
Column
Headings”
option, then
click “Next.”
5
Importing the Julian Calendar-4
Set the Data
Type for the
“Date” column
as “Date With
Time”; then the
“Day Number”
and “Julian
Date” columns,
as “Short Text”,
then click
“Next.”
6
Importing the Julian Calendar-5
On the
"Primary
Key" screen select “No
primary key",
then click
“Next” again.
7
Importing the Julian Calendar-6
On the "Import
to Table"
screen, rename
the table as
“FY1516 Julian
Table, then
click on “Finish”
and click on
“Close” on the
next screen.
8
Importing the Julian Calendar-7
Remember to update your Julian calendar
information each year
9
Additional Setup
Creating an import specification in Access
10
Creating an Import Specification in Access-1
With the Access
database open,
click 'External
Data' tab, and
select import
‘Text File'.
Browse for the
F134 file and
select it, then
select “Import”
option, and click
“OK.”
11
Creating an Import Specification in Access-2
Click on the
"Advanced"
button, then
click on the
"Specs"
button to
create your
import spec.
12
Creating an Import Specification in Access-3
From the Excel
Import
Specification
workbook,
copy all the
data below the
header row.
Right click in
the corner of
the field data
and perform a
Ctrl + V to
paste the data.
13
Creating an Import Specification in Access-4
An warning
message will
appear asking if
you want to paste
a certain number
of records. If it
matches the
number of rows
you copied from
Excel, Click “Yes.”
Make sure the
“English and OEM
United States”
options are
selected.
14
Creating an Import Specification in Access-5
Click “Save As”
and name your
Import
Specification,
then click “OK”
to close the
naming. Then
click “OK” to
use the Import
Specification.
15
Additional Setup
Creating a “Make Table” query to format the F134
file data in Access
16
Creating the Conversion Query-1
Click ‘Create'
tab, select the
‘Query Design'
option. Click
“Close” on the
“Show Table”
window , then
click on the
“SQL” option
under “View.”
17
Creating the Conversion Query-2
Copy this
SQL to be
pasted into
the “SQL”
view of the
query.
SELECT [New F134].[PROV ZIP], [New F134].[PROV COUNTY], [New F134].[CLM CTL #], [New
F134].[CLM TYPE], [New F134].[CLM DISP], [New F134].[ADJ REASN], [New F134].[MC COUNTY],
[New F134].[MC AID], [New F134].[MC SS#], [New F134].XXX, [New F134].[LAST NAME], [New
F134].[FIRST NAME], [New F134].[B DATE], [New F134].SEX, [New F134].RACE, [New F134].[AID
CAT], [New F134].[SPEC AID 1], [New F134].[SPEC AID 2], [New F134].[OTH COV], [New F134].[PROV
#], [New F134].[PROV NAME], [New F134].START, [New F134].END, [New F134].[PROG TYP], [New
F134].[PCCM PROV #], [New F134].[REFER MD], [New F134].[TAR CTL #], [New F134].[PRI DIAG-ICD
10], [New F134].[PRI DIAG-ICD 9], [New F134].[SEC DIAG-ICD 10], [New F134].[SEC DIAG-ICD 9],
[New F134].[EMRG IND], [New F134].[ADJ STAT], [New F134].[DENY REAS1], [New F134].[DENY
REAS2], [New F134].[DENY REAS3], FormatCurrency(([New F134]![TOTAL CHRG]/100),2) AS
[TOTAL CHRG], FormatCurrency(([New F134]![OTHER PAID]/100),2) AS [OTHER PAID],
FormatCurrency(([New F134]![PATIENT LIAB]/100),2) AS [PATIENT LIAB], FormatCurrency(([New
F134]![STATE PAID]/100),2) AS [STATE PAID], [New F134].[PMT DATE], [FY1516 Julian
Table].[Date], [New F134].[CHECK #], [New F134].[PMT EXP CODE], [New F134].[CLM FRM], [New
F134].[# LINES], [New F134].[ADMIT TYPE], [New F134].[ADMIT SRCE], [New F134].[ADMIT DATE],
[New F134].[DSCG DATE], [New F134].[PAT STATUS], [New F134].[TOT ACCOM DAYS], [New
F134].[DAYS AUTH], [New F134].[EXT AUTH], [New F134].[CLAIM LINES FOLLOW], [New F134].[LINE
#], [New F134].[ACCOM CODE], [New F134].[CC TYPE], [New F134].[CC UNITS],
FormatCurrency(([New F134]![CC RATE]/100),2) AS [CC RATE], FormatCurrency(([New F134]![CC
CHRG]/100),2) AS [CC CHRG], FormatCurrency(([New F134]![CC ALLWD]/100),2) AS [CC ALLWD],
[New F134].[CUT RSN], [New F134].[PROCSS CODE], [New F134].[NON COV UNITS],
FormatCurrency(([New F134]![NON COV CHRG]/100),2) AS [NON COV CHRG], [New F134].FILLER,
[New F134].[CLAIM LINE 2], [New F134].[CLAIM LINE 3], [New F134].[CLAIM LINE 4], [New
F134].[CLAIM LINE 5], [New F134].[CLAIM LINE 7], [New F134].[CLAIM LINE 8], [New F134].[CLAIM
LINE 6], [New F134].[CLAIM LINE 9], [New F134].[CLAIM LINE 10], [New F134].[CLAIM LINE 11], [New
F134].[CLAIM LINE 12], [New F134].[CLAIM LINE 13], [New F134].[CLAIM LINE 14] INTO
F1340501_Formatted
FROM [New F134] INNER JOIN [FY1516 Julian Table] ON [New F134].[PMT DATE] = [FY1516 Julian
Table].[Julian Date];
18
Creating the Conversion Query-3
Highlight
over any
existing text
in the SQL
view of the
query and
paste the
copied SQL
into the
query field.
19
Creating the Conversion Query-4
Click on the
“Save” or “Save
As” button and a
“Save As”
window will
appear to name
your query, (Ex:
“Convert and
Format Currency
Fields in F134”),
then click “OK.”
Close the query
after naming it.
20
Monthly
Processing: Step 1
Download F134 file from the Medi-Cal website
21
Logging into the Medi-Cal Website
Log in To
DHCS
Transactions
Website:
http://www.
medical.ca.gov/
http://www.medi-cal.ca.gov/
Must complete the Medi-Cal website Managed Care Plan/Insurance Carrier Agreement Form
22
Downloading the Transactions
Select F134
Files from
the County
Mental
Health
Transactions
Page and
Save the
Files.
• The O046 files are weekly processing files that show the records for TARS received by EDS.
• The F134 files are monthly payment files that show the payments to the providers.
23
Extracting the Zipped Files
Right click the
zipped files select "7 Zip File
Manager",
extract file,
saving to the
appropriate
folder, and enter
your DHCS
password. This
will save the file
as a CMH
extension.
24
Changing the File Type from .CMH to .txt
Open the CMH
file, which
usually opens
with NotePad
or WordPad,
and save the
file as a text
file. (Or
rename file
with “.txt”
extension per
next slide.)
25
Settings to Rename File Extensions
To rename the
file instead, select
the "Organize"
option within a
folder, select
"Folder and
search options“,
on the "View"
tab, uncheck the
"Hide extensions
for known file
types" option and
click "OK."
26
Monthly
Processing: Step 2
Using the import specification to import F134 files
into your Access Database
27
Using the Import Specification-1
With the Access
database open,
click 'External
Data' tab, and
select import
‘Text File'.
Browse for the
F134 file and
select it, then
select “Import”
option, and click
“OK.”
28
Using the Import Specification-2
Click on the
"Advanced"
button, then
click on the
"Specs"
button to
create your
import spec.
29
Using the Import Specification-3
Once you’ve
saved the Import
Spec, you can use
it for future
imports. Follow
the steps on
slides 8 and 9,
then click on
“Specs”, select
the Spec you
saved, and click
“Open” to load
the import spec,
then “OK.”
30
Using the Import Specification-4
You’ll be taken
back to the
original screen
where you
clicked on
Advanced.
Click on “Next”
to go to the
next screen.
31
Using the Import Specification-5
Click on
“Next” again.
32
Using the Import Specification-6
Click on
“Next” again.
33
Using the Import Specification-7
On the
"Primary
Key" screen select “No
primary key",
then click
“Next” again.
34
Using the Import Specification-8
On the
"Import to
Table"
screen,
rename the
table as
“New F134,
then click on
“Finish.”
35
Using the Import Specification-9
Then click
“Close”.
36
Monthly
Processing: Step 3
Correctly formatting and using F134 file data
37
Running the Conversion Query-1
Right click on
the “Make
Table” query
you created
in the initial
set-up and
open it in
“Design
View.”
38
Running the Conversion Query-2
Click on the
“Make Table”
option under the
“DESIGN” tab
and type a name
for your new
month’s F134
table and click
“OK.”
“F1340501_Form
atted” is an
example for
May’s F134.
39
Running the Conversion Query-3
Click on the
“Run” option
under the
“DESIGN” tab
and a pop up
window will
appear warning
how many
records you will
paste into a new
table. Click “Yes”
if the count is
correct.
40
Running the Conversion Query-4
The new
formatted F134
table will
appear in the
Navigation
Pane under
“Tables” with
the name you
selected in the
previous step.
41
Reconciliation
Tips for reconciling F134 file to realignment offsets
42
Important Note Regarding TAR Reconciliation
• Monthly F134 files cannot simply be balanced to a
monthly realignment offset because these files cover
different date ranges.
• Monthly realignment offsets are based on SCO checkwrite timeframes, which run from mid-month to midmonth (usually the 16th to the 15th). They are indicated
next to the amount of Managed Care Offset.
• Each monthly realignment offset can contain payments
from multiple F134 files to cover the date range of the
Managed Care Offset.
• If you have trouble reconciling these files, you can request
a state check-write report for your county from DHCS
Accounting Section.
43