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. Generated through expression:
tpa_adjudication_method	adjudication_method_type	Yes	No	Method used by TPA to adjudicate a claim (non-EDI) Generated through expression:
tpa_claim_status	text	No	No	Status of the claim according to the TPA (non-EDI) Generated through expression:
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) Generated through expression:
claim_billed_amount	numeric	Yes	No	The total amount of all submitted charges of service segments for this claim (from CLM02 / HCFA item 28) Generated through expression:
facility_type	text	No	No	Code identifying where services were, or may be, performed (from CLM05_01 / UB04 FL04) Generated through expression:
claim_type	claim_type	Yes	No	Type of healthcare claim such as dental, medical, short-term disability, etc. (non-EDI) Generated through expression:
form_type	form_type	No	No	Identifies the form the claim was submitted on such as UB-04 or HCFA (from CLM05_02) Generated through expression:
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) Generated through expression:
auto_accident_state	text	No	No	State where the auto accident occurred for property & casualty claims (From CLM11_04 + REF LU / UB04 FL29) Generated through expression:
auto_accident_country	text	No	No	Country where the auto accident occurred on property & casualty claims (From CLM11_05) Generated through expression:
earliest_service_date	date	Yes	No	Earliest service date among services, or statement date if not found Generated through expression:
latest_service_date	date	Yes	No	Latest service date among services, or statement date if not found Generated through expression:
statement_date_from	date	Yes	No	Begin statement date (From DTP 434 / UB04 FL06) Generated through expression:
statement_date_through	date	Yes	No	End statement date (From DTP 434 / UB04 FL06) Generated through expression:
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) Generated through expression:
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) Generated through expression:
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) Generated through expression:
accident_date	date	Yes	No	Date of accident (From DTP 439 / HCFA items 14 with qualifier 439) Generated through expression:
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) Generated through expression:
repricer_received_date	date	Yes	No	Date repricer received claim (From DTP 050) Generated through expression:
tpa_received_date	date	Yes	No	Date TPA received claim (non-EDI) Generated through expression:
facility_admission_type	text	No	No	Indicates the type of admission (from CL101 / UB04 FL14) Generated through expression:
facility_admission_source	text	No	No	Indicates the source of admission (e.g. point of origin) (from CL102 / UB04 FL15) Generated through expression:
facility_patient_status	text	No	No	Indicates patient status at the time of discharge (e.g. discharge status) (from CL103 / UB04 FL17) Generated through expression:
patient_amount_paid	numeric	Yes	No	Amount paid by patient (From AMT F5 / HCFA item 29) Generated through expression:
claim_number	text	No	No	Claim number according to the clearing house, van, or other intermediary (from REF D9) Generated through expression:
payer_claim_number	text	No	No	Claim number according to the payer or other intermediary (from REF F8 / UB04 FL64 / HCFA item 22) Generated through expression:
repriced_claim_number	text	No	No	Claim number according to the repricing organization or other intermediary (from REF 9A) Generated through expression:
medical_record_number	text	No	No	Medical record number for the patient (from REF EA / UB04 FL03b) Generated through expression:
icn_number	text	No	No	Internal control number (non-EDI) Generated through expression:
its_number	text	No	No	ITS number (non-EDI) Generated through expression:
prior_authorization_number	text	No	No	Prior authorization number (from REF G1 / HCFA item 23) Generated through expression:
clia_number	text	No	No	CLIA lab performing lab services (from REF X4) Generated through expression:
ambulance_transport_reason	ambulance_transport_reason_type	Yes	No	Reason for ambulance transport (from CR104) Generated through expression:
ambulance_pickup_address_id	bigint	Yes	Yes	Ambulance pickup location (From NM1 PW loop) Generated through expression:
ambulance_dropoff_address_id	bigint	Yes	Yes	Ambulance dropoff location (From NM1 45 loop) Generated through expression:
condition_codes	text[]	No	No	NUBC condition codes (From HI BG / UB04 FL18-28 / HCFA item 10d) Generated through expression:
reprice_methodology	pricing_methodology_type	Yes	No	Code specifying pricing methodology used for repricing (from HCP01) Generated through expression:
reprice_allowed_amount	numeric	Yes	No	Allowed amount (From HCP02) Generated through expression:
reprice_savings_amount	numeric	Yes	No	Savings amount (From HCP03) Generated through expression:
reprice_organization_id	text	No	No	Code identifying the organization that has repriced the claim (from HCP04) Generated through expression:
reprice_per_diem_or_flat_rate_amount	numeric	Yes	No	Per diem or flat rate amount (From HCP05) Generated through expression:
reprice_approved_drg_or_apg_code	text	No	No	Code specifying the approved Diagnosis Related Group or Ambulatory Patient Group (from HCP06) Generated through expression:
reprice_approved_drg_or_apg_amount	numeric	Yes	No	Approved DRG or APG amount (From HCP07) Generated through expression:
reprice_reject_reason	reject_reason_type	Yes	No	Code assigned by issuer to identify reason for rejection (from HCP13) Generated through expression:
reprice_policy_compliance	policy_compliance_type	Yes	No	Code specifying policy compliance (from HCP14) Generated through expression:
reprice_exception	repricing_exception_type	Yes	No	Code specifying the exception reason for consideration of out-of-network health care services (from HCP15) Generated through expression:
data_errors	text[]	No	No	- Generated through expression:
claim_member_id	bigint	No	Yes	Information needed to identify the patient (from Loop 2000A and 2000B) Generated through expression:
trading_partner_submission_id	bigint	No	Yes	Trading partner file submission details Generated through expression:
pay_to_plan_id	bigint	Yes	Yes	Links to the plan requesting subrogation payment. Rarely used (From Loop 2010AC) Generated through expression:
patient_death_date	date	Yes	No	Date of patient death (From PAT06) Generated through expression:
acute_manifestation_date	date	Yes	No	Date of acute manifestation of a chronic condition (From DTP 453 / HCFA item 15 with qualifier 453) Generated through expression:
last_menstrual_period_date	date	Yes	No	Date of last menstrual period (From DTP 484 / HCFA item 14 with qualifier 484) Generated through expression:
last_x_ray_date	date	Yes	No	Date of last x-ray (From DTP 455 / HCFA item 15 with qualifier 455) Generated through expression:
prescription_date	date	Yes	No	Date of hearing or vision prescription (From DTP 471 / HCFA item 15 with qualifier 471) Generated through expression:
last_worked_date	date	Yes	No	Date last worked (From DTP 297) Generated through expression:
return_to_work_date	date	Yes	No	Date authorized to return to work (From DTP 296) Generated through expression:
assumed_care_start	date	Yes	No	Date post-operative care began (From DTP 090 / HCFA item 15 with qualifier 090) Generated through expression:
relinquished_care_date	date	Yes	No	Date post-operative care ended (From DTP 091 / HCFA item 15 with qualifier 091) Generated through expression:
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) Generated through expression:
orthodontic_treatment_months_total	numeric	Yes	No	Total months of orthodontic treatment (from DN101) Generated through expression:
orthodontic_treatment_months_remaining	numeric	Yes	No	Remaining months of orthodontic treatment (from DN102) Generated through expression:
contract_type	contract_type	Yes	No	Contract type (From CN101) Generated through expression:
contract_amount	numeric	Yes	No	Contract amount (From CN102) Generated through expression:
contract_billed_percentage	numeric	Yes	No	Contract billed percentage (From CN103) Generated through expression:
contract_identification_code	text	No	No	Contract identification code (From CN104) Generated through expression:
contract_version_identifier	text	No	No	Contract version identifier (From CN106) Generated through expression:
estimated_patient_responsibility	numeric	Yes	No	Estimated patient responsibility (From AMT F3) Generated through expression:
service_authorization_exception_code	text	No	No	Code to explain why authorization was not requested prior to care (from REF 4N) Generated through expression:
mandatory_medicare_crossover_indicator	text	No	No	Describes when Medicare claim should crossover to Medigap or COB (from REF F5) Generated through expression:
mammography_certification_number	text	No	No	Mammography certification number for a certified provider (from REF EW) Generated through expression:
referral_number	text	No	No	Referral number (from REF 9F) Generated through expression:
adjusted_repriced_claim_number	text	No	No	Adjusted claim number according to the repricing organization or other intermediary (from REF 9C) Generated through expression:
investigational_device_exemption_number	text	No	No	FDA investigational device exemption number (from REF LX) Generated through expression:
demonstration_project_identifier	text	No	No	Identifies demonstration or special project (from REF P4) Generated through expression:
care_plan_oversight_number	text	No	No	Care plan oversight number (from REF 1J) Generated through expression:
peer_review_organization_approval_number	text	No	No	Medical necessity approval from peer review organization (from REF G4) Generated through expression:
ambulance_round_trip_purpose_description	text	No	No	Describes why a round trip transport was required (from CR109) Generated through expression:
ambulance_stretcher_purpose_description	text	No	No	Describes why a stretcher was required (From CR110) Generated through expression:
spinal_manipulation_condition	nature_of_condition_type	Yes	No	Spinal manipulation patient condition (From CR208) Generated through expression:
spinal_manipulation_patient_condition_note1	text	No	No	Spinal manipulation patient condition description (From CR210) Generated through expression:
spinal_manipulation_patient_condition_note2	text	No	No	Spinal manipulation patient condition description (From CR211) Generated through expression:
is_homebound	boolean	Yes	No	Patient is homebound (From CRC01:75, CRC02:Y) Generated through expression:
reprice_approved_quantity	numeric	Yes	No	Approved quantity (From HCP12) Generated through expression:
transaction_date	date	Yes	No	Date of the transaction (from BHT04) Generated through expression:
ambulance_patient_count	smallint	Yes	No	Number of patients in ambulance ride (From QTY PT) Generated through expression:
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) Generated through expression:
dme_certification_type	certification_type	Yes	No	Type of DME certification (From CR301) Generated through expression:
dme_need_duration_quantity	numeric	Yes	No	Length of time DME equipment is needed (From CR303) Generated through expression:
vision_spectacle_lenses_applicable_conditions	text[]	No	No	Conditions that apply to spectacle lens claim (From CRC01:E1, CRC02:Y) Generated through expression:
vision_spectacle_lenses_not_applicable_conditions	text[]	No	No	Conditions that do not apply to spectacle lens claim (From CRC01:E1, CRC02:N) Generated through expression:
vision_spectacle_frames_applicable_conditions	text[]	No	No	Conditions that apply to spectacle frame claim (From CRC01:E3, CRC02:Y) Generated through expression:
vision_spectacle_frames_not_applicable_conditions	text[]	No	No	Conditions that do not apply to spectacle frame claim (From CRC01:E3, CRC02:N) Generated through expression:
vision_contacts_applicable_conditions	text[]	No	No	Conditions that apply to contact lens claim (From CRC01:E2, CRC02:Y) Generated through expression:
vision_contacts_not_applicable_conditions	text[]	No	No	Conditions that do not apply to contact lens claim (From CRC01:E2, CRC02:N) Generated through expression:
epsdt_referral_not_applicable_conditions	text[]	No	No	Conditions that do not apply to EPSDT referral claim (From CRC01:ZZ, CRC02:N) Generated through expression:
patient_weight_units	unit_type	Yes	No	Unit of measure for patient weight (From claim level PAT07, HI value code A8 [always "Kilograms"], CR101, or line item MEA qualifier HT [always "Unitless"]) Generated through expression:
patient_weight_quantity	numeric	Yes	No	Patient Weight (From claim level PAT08, HI value code A8, CR102, or line item MEA qualifier HT) Generated through expression:
patient_height_units	unit_type	Yes	No	Unit of measure for patient weight (From claim HI value code A9 [always "Centimeter"], or line item MEA qualifier WT [always "Unitless"] or CR101) Generated through expression:
patient_height_quantity	numeric	Yes	No	Patient Height (From claim HI value code A9, or from line item MEA qualifier WT or CR102) Generated through expression:
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) Generated through expression:
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) Generated through expression:
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) Generated through expression:
contract_terms_discount_percent	numeric	Yes	No	Percentage discount available if paid before the discount due date (From CN105) Generated through expression:
property_and_casualty_claim_number	text	No	No	Property and Casualty Claim Number (from Subscribers's REF Y4 / HCFA item 11b) Generated through expression:
epsdt_referral_applicable_conditions	text[]	No	No	Conditions that apply to EPSDT referral claim (From CRC01:ZZ, CRC02:Y) Generated through expression:
reprice_approved_revenue_code	text	No	No	Approved revenue code (From HCP08) Generated through expression:
usage_indicator	usage_indicator_type	Yes	No	Indicates the type of data being sent in the transaction (from ISA15) Generated through expression:
transaction_purpose	transaction_set_purpose_type	Yes	No	Purpose of the transaction set (e.g. original, reissue) (from BHT02) Generated through expression:
transaction_type	transaction_type	Yes	No	Type of transaction (e.g. reporting, subrogation, chargeable) (from BHT06) Generated through expression:
receiver_id_qualifier	identification_type	Yes	No	Type of identifier used in "receiver_id" (from NM108) Generated through expression:
receiver_id	text	No	No	Code used to identify the intended recipient of the data being sent (from NM109) Generated through expression:
submitter_id_qualifier	identification_type	Yes	No	Type of identifier used in "submitter_id" (from NM108) Generated through expression:
submitter_id	text	No	No	Code used to identify the sender of the data (from NM109) Generated through expression:
submitter_name	text	No	No	Name of the person or organization sending the data (from NM103) Generated through expression:
patient_is_pregnant	boolean	Yes	No	Indicates whether patient is pregnant (From PAT09) Generated through expression:
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) Generated through expression:
provider_accept_assignment	provider_accept_assignment_type	Yes	No	Code indicating whether the provider accepts assignment (from CLM07 / HCFA item 27) Generated through expression:
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) Generated through expression:
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) Generated through expression:
special_program	special_program_type	Yes	No	Indicates when services were rendered under certain circumstances, programs, or projects (From CLM12) Generated through expression:
claim_submission_reason	claim_submission_reason_type	Yes	No	Code identifying reason for claim submission (from CLM19) Generated through expression:
delay_reason	delay_reason_type	Yes	No	Code indicating the reason why a request was delayed (from CLM20) Generated through expression:
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) Generated through expression:
disability_start_date	date	Yes	No	Start date of disability (From DTP 314 or DTP 360 / HCFA item 16) Generated through expression:
disability_end_date	date	Yes	No	End date of disability (From DTP 314 or DTP 361 / HCFA item 16) Generated through expression:
service_date_from	date	Yes	No	Service date start (from DTP qualifier 472. Dental only) Generated through expression:
service_date_through	date	Yes	No	Service date end (from DTP qualifier 472. Dental only) Generated through expression:
appliance_placement_date	date	Yes	No	Date of orthodontic applicance placement (From DTP 452. Dental only) Generated through expression:
predetermination_of_benefits_identifier	text	No	No	Predetermination of benefits identifier (from REF G3) Generated through expression:
ambulance_transport_distance_units	unit_type	Yes	No	The units by which the patient's transportation was measured (From CR105) Generated through expression:
ambulance_transport_distance_quantity	numeric	Yes	No	Distance that the patient was transported by ambulance (From CR105) Generated through expression:
ambulance_applicable_conditions	text[]	No	No	Ambulance certification of applicable patient conditions (From CRC01:07, CRC02:Y) Generated through expression:
ambulance_not_applicable_conditions	text[]	No	No	Ambulance certification of non-applicable patient conditions (From CRC01:07, CRC02:N) Generated through expression:
dme_need_duration_units	unit_type	Yes	No	The units for dme_need_duration_quantity (From CR302) Generated through expression:
dme_applicable_conditions	condition_indicator_type[]	No	No	DME certification of applicable patient conditions (From CRC01:09, CRC02:Y) Generated through expression:
dme_not_applicable_conditions	condition_indicator_type[]	No	No	DME certification of not applicable patient conditions (From CRC01:09, CRC02:N) Generated through expression:
reprice_approved_units	unit_type	Yes	No	Approved reprice units (From HCP11) Generated through expression: