claim

Information about a healthcare or prescription drug claim

Primary Key:

• claim_id

Foreign Keys:

• ambulance_dropoff_address_id => address_seen.address_id
• ambulance_pickup_address_id => address_seen.address_id
• claim_member_id => claim_member.claim_member_id
• pay_to_plan_id => claim_pay_to_plan.pay_to_plan_id
• trading_partner_submission_id => trading_partner_submission.trading_partner_submission_id

Indexes:

• claim_ambulance_dropoff_address_id_idx - ambulance_dropoff_address_id
• claim_ambulance_pickup_address_id_idx - ambulance_pickup_address_id
• claim_claim_member_id_idx - claim_member_id
• claim_pay_to_plan_id_idx - pay_to_plan_id
• claim_trading_partner_submission_id_idx - trading_partner_submission_id

Column	Data Type	Nullable	Indexed	Description
claim_id	bigint	No	Yes	Snowflake ID for claim.
tpa_adjudication_method	adjudication_method_type	Yes	No	Method used by TPA to adjudicate a claim (non-EDI)
tpa_claim_status	text	No	No	Status of the claim according to the TPA (non-EDI)
patient_control_number	text	No	No	Identifier used to track a claim from creation by the health care provider through payment (from CLM01 / UB04 FL03a / HCFA item 26)
claim_billed_amount	numeric	Yes	No	The total amount of all submitted charges of service segments for this claim (from CLM02 / HCFA item 28)
facility_type	text	No	No	Code identifying where services were, or may be, performed (from CLM05_01 / UB04 FL04)
claim_type	claim_type	Yes	No	Type of healthcare claim such as dental, medical, short-term disability, etc. (non-EDI)
form_type	form_type	No	No	Identifies the form the claim was submitted on such as UB-04 or HCFA (from CLM05_02)
claim_frequency_type	text	No	No	Code specifying the frequency (i.e. sequence of the claim in its billing/payment lifecycle) of the claim (from CLM05_03 / UB04 FL04 / HCFA item 22)
auto_accident_state	text	No	No	State where the auto accident occurred for property & casualty claims (From X12 EDI CLM11_04 [prof] or REF LU [inst] / UB04 FL29)
auto_accident_country	text	No	No	Country where the auto accident occurred on property & casualty claims (From X12 EDI CLM11_05 [prof])
earliest_service_date	date	Yes	No	Earliest service date among services, or statement date if not found
latest_service_date	date	Yes	No	Latest service date among services, or statement date if not found
statement_date_from	date	Yes	No	Begin statement date (From DTP 434 / UB04 FL06)
statement_date_through	date	Yes	No	End statement date (From DTP 434 / UB04 FL06)
discharge_date_or_hour	timestamp without time zone	Yes	No	Date (HCFA) or hour (UB-04) patient was discharged from the facility after treatment (From DTP 096 / UB04 FL16 / HCFA item 18)
initial_treatment_date	date	Yes	No	Date of the first treatment for the same condition as this claim (From DTP 454 / HCFA item 15 with qualifier 454)
onset_of_symptoms_or_illness_date	date	Yes	No	Date of initial medical service or visit performed when different than date of service (From DTP 431 / HCFA item 14 with qualifier 431)
accident_date	date	Yes	No	Date of accident (From DTP 439 / HCFA items 14 with qualifier 439 / NCPDP 434-DY)
last_seen_date	date	Yes	No	Date last seen for the same condition of the claim (From DTP 304 / HCFA item 15 with qualifier 304)
repricer_received_date	date	Yes	No	Date repricer received claim (From DTP 050)
tpa_received_date	date	Yes	No	Date TPA received claim (non-EDI)
facility_admission_type	text	No	No	Indicates the type of admission (from CL101 / UB04 FL14)
facility_admission_source	text	No	No	Indicates the source of admission (e.g. point of origin) (from CL102 / UB04 FL15)
facility_patient_status	text	No	No	Indicates patient status at the time of discharge (e.g. discharge status) (from CL103 / UB04 FL17)
patient_amount_paid	numeric	Yes	No	Amount paid by patient (From AMT F5 / HCFA item 29 / NCPDP 505-F5)
claim_number	text	No	No	Claim number according to the clearing house, van, or other intermediary (from REF D9)
payer_claim_number	text	No	No	Claim number according to the payer or other intermediary (from REF F8 / UB04 FL64 / HCFA item 22)
repriced_claim_number	text	No	No	Claim number according to the repricing organization or other intermediary (from REF 9A)
medical_record_number	text	No	No	Medical record number for the patient (from REF EA / UB04 FL03b)
icn_number	text	No	No	Internal control number (non-EDI)
its_number	text	No	No	ITS number (non-EDI)
prior_authorization_number	text	No	No	Prior authorization number (from REF G1 / HCFA item 23)
clia_number	text	No	No	CLIA lab performing lab services (from REF X4)
ambulance_transport_reason	ambulance_transport_reason_type	Yes	No	Reason for ambulance transport (from CR104)
ambulance_pickup_address_id	bigint	Yes	Yes	Ambulance pickup location (From NM1 PW loop)
ambulance_dropoff_address_id	bigint	Yes	Yes	Ambulance dropoff location (From NM1 45 loop)
condition_codes	text[]	No	No	NUBC condition codes (From HI BG / UB04 FL18-28 / HCFA item 10d)
reprice_methodology	pricing_methodology_type	Yes	No	Code specifying pricing methodology used for repricing (from HCP01)
reprice_allowed_amount	numeric	Yes	No	Allowed amount (From HCP02)
reprice_savings_amount	numeric	Yes	No	Savings amount (From HCP03)
reprice_organization_identifier	text	No	No	Code identifying the organization that has repriced the claim (from HCP04)
reprice_per_diem_or_flat_rate_amount	numeric	Yes	No	Per diem or flat rate amount (From HCP05)
reprice_approved_drg_or_apg_code	text	No	No	Code specifying the approved Diagnosis Related Group or Ambulatory Patient Group (from HCP06)
reprice_approved_drg_or_apg_amount	numeric	Yes	No	Approved DRG or APG amount (From HCP07)
reprice_reject_reason	reject_reason_type	Yes	No	Code assigned by issuer to identify reason for rejection (from HCP13)
reprice_policy_compliance	policy_compliance_type	Yes	No	Code specifying policy compliance (from HCP14)
reprice_exception	repricing_exception_type	Yes	No	Code specifying the exception reason for consideration of out-of-network health care services (from HCP15)
data_errors	text[]	No	No	-
claim_member_id	bigint	No	Yes	Information needed to identify the patient (from Loop 2000A and 2000B)
trading_partner_submission_id	bigint	No	Yes	Trading partner file submission details
pay_to_plan_id	bigint	Yes	Yes	Links to the plan requesting subrogation payment. Rarely used (From Loop 2010AC)
patient_death_date	date	Yes	No	Date of patient death (From PAT06)
acute_manifestation_date	date	Yes	No	Date of acute manifestation of a chronic condition (From DTP 453 / HCFA item 15 with qualifier 453)
last_menstrual_period_date	date	Yes	No	Date of last menstrual period (From DTP 484 / HCFA item 14 with qualifier 484)
last_x_ray_date	date	Yes	No	Date of last x-ray (From DTP 455 / HCFA item 15 with qualifier 455)
prescription_date	date	Yes	No	Date of hearing or vision prescription (From DTP 471 / HCFA item 15 with qualifier 471)
last_worked_date	date	Yes	No	Date last worked (From DTP 297)
return_to_work_date	date	Yes	No	Date authorized to return to work (From DTP 296)
assumed_care_start	date	Yes	No	Date post-operative care began (From DTP 090 / HCFA item 15 with qualifier 090)
relinquished_care_date	date	Yes	No	Date post-operative care ended (From DTP 091 / HCFA item 15 with qualifier 091)
first_visit_or_consultation_date	date	Yes	No	Date of first visit to provider for this condition (From DTP 444 / HCFA item 15 with qualifier 444)
orthodontic_treatment_months_total	numeric	Yes	No	Total months of orthodontic treatment (from DN101)
orthodontic_treatment_months_remaining	numeric	Yes	No	Remaining months of orthodontic treatment (from DN102)
contract_type	contract_type	Yes	No	Contract type (From CN101)
contract_amount	numeric	Yes	No	Contract amount (From CN102)
contract_billed_percentage	numeric	Yes	No	Contract billed percentage (From CN103)
contract_identification_code	text	No	No	Contract identification code (From CN104)
contract_version_identifier	text	No	No	Contract version identifier (From CN106)
estimated_patient_responsibility	numeric	Yes	No	Estimated patient responsibility (From AMT F3)
service_authorization_exception_code	text	No	No	Code to explain why authorization was not requested prior to care (from REF 4N)
mandatory_medicare_crossover_indicator	text	No	No	Describes when Medicare claim should crossover to Medigap or COB (from REF F5)
mammography_certification_number	text	No	No	Mammography certification number for a certified provider (from REF EW)
referral_number	text	No	No	Referral number (from REF 9F)
adjusted_repriced_claim_number	text	No	No	Adjusted claim number according to the repricing organization or other intermediary (from REF 9C)
investigational_device_exemption_number	text	No	No	FDA investigational device exemption number (from REF LX)
demonstration_project_identifier	text	No	No	Identifies demonstration or special project (from REF P4)
care_plan_oversight_number	text	No	No	Care plan oversight number (from REF 1J)
peer_review_organization_approval_number	text	No	No	Medical necessity approval from peer review organization (from REF G4)
ambulance_round_trip_purpose_description	text	No	No	Describes why a round trip transport was required (from CR109)
ambulance_stretcher_purpose_description	text	No	No	Describes why a stretcher was required (From CR110)
spinal_manipulation_condition	nature_of_condition_type	Yes	No	Spinal manipulation patient condition (From CR208)
spinal_manipulation_patient_condition_note1	text	No	No	Spinal manipulation patient condition description (From CR210)
spinal_manipulation_patient_condition_note2	text	No	No	Spinal manipulation patient condition description (From CR211)
is_homebound	boolean	Yes	No	Patient is homebound (From CRC01:75, CRC02:Y)
reprice_approved_quantity	numeric	Yes	No	Approved quantity (From HCP12)
transaction_time	timestamp without time zone	Yes	No	Datetime of the transaction (from BHT04 and BHT05)
ambulance_patient_count	smallint	Yes	No	Number of patients in ambulance ride (From QTY PT)
ambulance_pickup_zip_from_value_code	text	No	No	Ambulance pickup ZIP code (From HI with HIxx_01=BE and HIxx_02=A0. This is the value required in CMS Internet Only Manual)
dme_certification_type	certification_type	Yes	No	Type of DME certification (From CR301)
dme_need_duration_quantity	numeric	Yes	No	Length of time DME equipment is needed (From CR303)
vision_spectacle_lenses_applicable_conditions	condition_indicator_type[]	No	No	Conditions that apply to spectacle lens claim (From CRC01:E1, CRC02:Y)
vision_spectacle_lenses_not_applicable_conditions	condition_indicator_type[]	No	No	Conditions that do not apply to spectacle lens claim (From CRC01:E1, CRC02:N)
vision_spectacle_frames_applicable_conditions	condition_indicator_type[]	No	No	Conditions that apply to spectacle frame claim (From CRC01:E3, CRC02:Y)
vision_spectacle_frames_not_applicable_conditions	condition_indicator_type[]	No	No	Conditions that do not apply to spectacle frame claim (From CRC01:E3, CRC02:N)
vision_contacts_applicable_conditions	condition_indicator_type[]	No	No	Conditions that apply to contact lens claim (From CRC01:E2, CRC02:Y)
vision_contacts_not_applicable_conditions	condition_indicator_type[]	No	No	Conditions that do not apply to contact lens claim (From CRC01:E2, CRC02:N)
epsdt_referral_not_applicable_conditions	condition_indicator_type[]	No	No	Conditions that do not apply to EPSDT referral claim (From CRC01:ZZ, CRC02:N)
patient_weight_units	unit_type	Yes	No	Unit of measure for patient weight (From X12 EDI PAT07)
patient_weight_quantity	numeric	Yes	No	Patient Weight (From X12 EDI PAT08)
patient_assigned_benefits	yes_no_response_type	Yes	No	A 'Y' value indicates insured or authorized person authorizes benefits to be assigned to the provider; an 'N' value indicates benefits have not been assigned to the provider. (from CLM08 / UB04 FL53 / NCPDP 391-MT)
release_of_information	release_of_information_type	Yes	No	Code indicating whether the provider has on file a signed statement by the patient authorizing the release of medical data to other organizations (from CLM09 / UB04 FL52)
orthodontic_treatment_indicator	text	No	No	The only allowed value is 'Y' which indicates that services reported on this claim are for orthodontic purposes and that DN101 and DN102 were not submitted (from DN104)
contract_terms_discount_percent	numeric	Yes	No	Percentage discount available if paid before the discount due date (From CN105)
property_and_casualty_claim_number	text	No	No	Property and Casualty Claim Number (from Subscribers's REF Y4 / HCFA item 11b / NCPDP 435-DZ)
epsdt_referral_applicable_conditions	condition_indicator_type[]	No	No	Conditions that apply to EPSDT referral claim (From CRC01:ZZ, CRC02:Y)
reprice_approved_revenue_code	text	No	No	Approved revenue code (From HCP08)
usage_indicator	usage_indicator_type	Yes	No	Indicates the type of data being sent in the transaction (from ISA15)
transaction_purpose	transaction_set_purpose_type	Yes	No	Purpose of the transaction set (e.g. original, reissue) (from BHT02)
transaction_type	transaction_type	Yes	No	Type of transaction (e.g. reporting, subrogation, chargeable) (from BHT06 / NCPDP 103-A3)
receiver_identifier_qualifier	identification_type	Yes	No	Type of identifier used in "receiver_identifier" (from NM108)
receiver_identifier	text	No	No	Code used to identify the intended recipient of the data being sent (from NM109)
submitter_identifier_qualifier	identification_type	Yes	No	Type of identifier used in "submitter_identifier" (from NM108)
submitter_identifier	text	No	No	Code used to identify the sender of the data (from NM109)
submitter_name	text	No	No	Name of the person or organization sending the data (from NM103)
patient_is_pregnant	boolean	Yes	No	Indicates whether patient is pregnant (From X12 EDI PAT09 / NCPDP 335-2C)
provider_signature_on_file	yes_no_response_type	Yes	No	Code indicates whether a provider or supplier signature is on file (from CLM06 / HCFA item 31)
provider_accept_assignment	provider_accept_assignment_type	Yes	No	Code indicating whether the provider accepts assignment (from CLM07 / HCFA item 27 / NCPDP 361-2D)
patient_signature_source	patient_signature_source_type	Yes	No	[Professional claims only] Code indicating how the patient or subscriber authorization signatures were obtained and how they are being retained by the provider (from CLM10)
related_causes	related_cause_type[]	No	No	[Professional claims only] To identify one or more related causes (from CLM11_01 through CLM11_02 / HCFA items 10a-c)
special_program	special_program_type	Yes	No	Indicates when services were rendered under certain circumstances, programs, or projects (From CLM12)
claim_submission_reason	claim_submission_reason_type	Yes	No	Code identifying reason for claim submission (from CLM19)
delay_reason	delay_reason_type	Yes	No	The reason why a request was delayed (from CLM20 / NCPDP 357-NV)
admit_date_or_datetime	timestamp without time zone	Yes	No	Date patient was admitted to the facility for treatment (From DTP 435 / UB04 FL12-13 / HCFA item 18)
disability_start_date	date	Yes	No	Start date of disability (From DTP 314 or DTP 360 / HCFA item 16)
disability_end_date	date	Yes	No	End date of disability (From DTP 314 or DTP 361 / HCFA item 16)
service_date_from	date	Yes	No	Service date start (from DTP qualifier 472. Dental only)
service_date_through	date	Yes	No	Service date end (from DTP qualifier 472. Dental only)
appliance_placement_date	date	Yes	No	Date of orthodontic applicance placement (From DTP 452. Dental only)
predetermination_of_benefits_identifier	text	No	No	Predetermination of benefits identifier (from REF G3)
ambulance_transport_distance_units	unit_type	Yes	No	The units by which the patient's transportation was measured (From CR105)
ambulance_transport_distance_quantity	numeric	Yes	No	Distance that the patient was transported by ambulance (From CR105)
ambulance_applicable_conditions	condition_indicator_type[]	No	No	Ambulance certification of applicable patient conditions (From CRC01:07, CRC02:Y)
ambulance_not_applicable_conditions	condition_indicator_type[]	No	No	Ambulance certification of non-applicable patient conditions (From CRC01:07, CRC02:N)
dme_need_duration_units	unit_type	Yes	No	The units for dme_need_duration_quantity (From CR302)
dme_applicable_conditions	condition_indicator_type[]	No	No	DME certification of applicable patient conditions (From CRC01:09, CRC02:Y)
dme_not_applicable_conditions	condition_indicator_type[]	No	No	DME certification of not applicable patient conditions (From CRC01:09, CRC02:N)
reprice_approved_units	unit_type	Yes	No	Approved reprice units (From HCP11)
property_and_casualty_carrier_identifier	text	No	No	Carrier code assigned in a workers' compensation program (from NCPDP 327-CR)
other_coverage_indicator	text	No	No	Code indicating whether the patient has additional insurance coverage (from NCPDP 308-C8)
rx_bin	text	No	No	Bank Identification Number (from NCPDP 101-A1)
rx_processor_control_number	text	No	No	Together with the BIN number, the PCN identifies the specific pharmacy benefit plan (from NCPDP 104-A4)
rx_billing_type	rx_billing_type	Yes	No	Specifies the billing type for the claim (from NCPDP 455-EM)
rx_patient_amount_paid_submitted	numeric	Yes	No	Patient payment amount submitted by the pharmacy (from NCPDP 433-DX)
ambulance_patient_weight_units	unit_type	Yes	No	Unit of measure for patient weight (From X12 EDI CR101)
ambulance_patient_weight_quantity	numeric	Yes	No	Patient Weight (From X12 EDI CR102)
rx_authorization_number	text	No	No	Identifies a transaction for an Rx claim (from NCPDP 503-F3)
rx_original_authorization_number	text	No	No	Indicates the Rx transaction that is being reversed or rebilled (from NCPDP 503-F3)
rx_facility_is_part_d_qualified	boolean	Yes	No	Whether the patient is living in a facility that meets requirements for Medicare Part D coverage (from NCPDP 997-G2)
rx_transmission_accepted	boolean	Yes	No	Whether the NCPDP transmission was accepted or rejected (from NCPDP 501-F1)
rx_transaction_response_status	rx_transaction_response_status_type	Yes	No	Whether the NCPDP transaction was Approved, Captured, Rejected, etc. (from NCPDP 112-AN)
created_manually	boolean	Yes	No	Whether the claim was created manually