We understand that your teams need to access data within the Cavo platform. We have a comprehensive API for interacting with the platform directly. API access alone is not efficient for accessing large amounts of historical data. To address this need, we've partnered with Snowflake.
We do not provision this access by default. Optics SQL Access is an added feature that must go through CAVO / Apello customer support and requires that your account have access to this feature.
Every 4 hours, your Cavo data flows into a siloed Snowflake schema. This is an important point to consider since the SQL data will not be complete up to the minute.
This is a new offering and schema access is limited. We are rolling out access to Itemized Bill data first and will gradually expand this offering as we better understand our clients' use cases.
Optics SQL Access
Once your organization has SQL access provisioned, we will need to know which users require access. Please submit the first name, last name and email address for each developer to support@cavo.io. Once access is provisioned, developers will receive an email with instructions on how to proceed. The email will include:
Your CAVO instance (acme.cavo.io)
Snowflake endpoint
Snowflake port
username
password
We only support read access to SQL tables. If you plan to connect a BI tool (Tableau, Looker, etc.), we strongly encourage a unique login for that BI tool. Provide an email address for the user in charge of adding those credentials to your BI tools.
Tables Included
IB_CHARGES
IB_ADJUSTMENTS
IB_CHARGES Schema
ID | VARCHAR | PRIMARY KEY |
FACILITY_ID | VARCHAR | CASE IDENTIFIER 1 |
ENCOUNTER_ID | VARCHAR | CASE IDENTIFIER 2 |
DOS | VARCHAR | DATE OF SERVICE |
REVCODE | VARCHAR | REVENUE CODE |
SERVICECODE | VARCHAR | SERVICE CODE |
NDC | VARCHAR |
|
DESCRIPTION | VARCHAR | LINE DESCRIPTION |
UNITS | NUMBER (38,0) | NUMBER OF UNITS |
UNITCOST | FLOAT | UNIT COST |
CHARGE | FLOAT | TOTAL LINE CHARGE |
DOC_ID | VARCHAR | CAVO DOC ID |
PAGE | NUMBER (38,0) | CAVO DOC PAGE NUMBER |
INDEX | NUMBER (38,0) | CAVO LINE INDEX |
TIMESTAMP |
| TIMESTAMP |
IB_ADJUSTMENTS Schema
IB_CHARGE_ID | VARCHAR | FOREIGN KEY TO CHARGE_ID |
FACILITY_ID | VARCHAR | CASE IDENTIFIER 1 |
ENCOUNTER_ID | VARCHAR | CASE IDENTIFIER 2 |
UNITS | NUMBER (38,0) | NUMBER OF UNITS ADJUSTED |
UNITCOST | FLOAT | UNIT COST |
REASON_CODE | VARCHAR | REASON CODE ID |
REASON_CODE_DESCRIPTION | VARCHAR | REASON CODE DESCRIPTION |
TIMESTAMP | VARCHAR | TIMESTAMP |
SAMPLE SQL
In an effort to get you up and running quickly, we've created a sample SQL query you can use to pull the charges and adjustments for a given case. In order to identify a case, you must specify the FACILITY_ID and ENCOUNTER_ID. Substitute actual values for a case in the "where" clause below:
where facility_id = 'FACILITY_ID' and encounter_id = 'ENCOUNTER_ID'
--**************************************
---UNION CHARGES & ADJUTSMENTS
--**************************************
with charge_adj as (
select '1' as ORD, 'CHARGES'as SOURCE,ID,FACILITY_ID,ENCOUNTER_ID,DOS,REVCODE,SERVICECODE,NDC,
DESCRIPTION,UNITS,UNITCOST,CHARGE,DOC_ID
from IB_CHARGES
union
select '2' as ORD, 'ADJUSTMENTS'as SOURCE,a.IB_CHARGE_ID as ID,a.FACILITY_ID,a.ENCOUNTER_ID,null as DOS,null as REVCODE,null as SERVICECODE,null as NDC,
a.reason_code_desc as DESCRIPTION,a.units,b.UNITCOST,(a.units * b.unitcost) as CHARGE,null as DOC_ID
from IB_ADJUSTMENTS a left join IB_CHARGES b on a.IB_CHARGE_ID = b.ID
)
select SOURCE,ID,FACILITY_ID,ENCOUNTER_ID,DOS,REVCODE,SERVICECODE,NDC,DESCRIPTION,UNITS,UNITCOST,CHARGE from charge_adj
where facility_id = 'FACILITY_ID' and encounter_id = 'ENCOUNTER_ID'
order by ID, FACILITY_ID,ENCOUNTER_ID, ORD;