claim_validation_view

Materialized: false

Definition

 SELECT c.claim_id,
    c.trading_partner_submission_id,
    c.usage_indicator,
    c.transaction_purpose,
    c.transaction_date,
    c.transaction_type,
    c.receiver_id_qualifier,
    c.receiver_id,
    c.submitter_id_qualifier,
    c.submitter_id,
    c.submitter_name,
    c.pay_to_plan_id,
    c.claim_member_id,
    c.patient_death_date,
    c.patient_weight_units,
    c.patient_weight_quantity,
    c.patient_height_units,
    c.patient_height_quantity,
    c.patient_is_pregnant,
    c.tpa_adjudication_method,
    c.tpa_claim_status,
    c.patient_control_number,
    c.claim_billed_amount,
    c.facility_type,
    c.claim_type,
    c.form_type,
    c.claim_frequency_type,
    c.provider_signature_on_file,
    c.provider_accept_assignment,
    c.patient_assigned_benefits,
    c.release_of_information,
    c.patient_signature_source,
    c.related_causes,
    c.auto_accident_state,
    c.auto_accident_country,
    c.special_program,
    c.claim_submission_reason,
    c.delay_reason,
    c.earliest_service_date,
    c.latest_service_date,
    c.statement_date_from,
    c.statement_date_through,
    c.admit_date_or_datetime,
    c.discharge_date_or_hour,
    c.initial_treatment_date,
    c.onset_of_symptoms_or_illness_date,
    c.accident_date,
    c.last_seen_date,
    c.repricer_received_date,
    c.acute_manifestation_date,
    c.last_menstrual_period_date,
    c.last_x_ray_date,
    c.prescription_date,
    c.disability_start_date,
    c.disability_end_date,
    c.last_worked_date,
    c.return_to_work_date,
    c.assumed_care_start,
    c.relinquished_care_date,
    c.first_visit_or_consultation_date,
    c.tpa_received_date,
    c.service_date_from,
    c.service_date_through,
    c.appliance_placement_date,
    c.facility_admission_type,
    c.facility_admission_source,
    c.facility_patient_status,
    c.orthodontic_treatment_months_total,
    c.orthodontic_treatment_months_remaining,
    c.orthodontic_treatment_indicator,
    c.contract_type,
    c.contract_amount,
    c.contract_billed_percentage,
    c.contract_identification_code,
    c.contract_terms_discount_percent,
    c.contract_version_identifier,
    c.patient_amount_paid,
    c.estimated_patient_responsibility,
    c.predetermination_of_benefits_identifier,
    c.service_authorization_exception_code,
    c.mandatory_medicare_crossover_indicator,
    c.mammography_certification_number,
    c.referral_number,
    c.claim_number,
    c.payer_claim_number,
    c.repriced_claim_number,
    c.adjusted_repriced_claim_number,
    c.investigational_device_exemption_number,
    c.medical_record_number,
    c.demonstration_project_identifier,
    c.care_plan_oversight_number,
    c.its_number,
    c.icn_number,
    c.prior_authorization_number,
    c.clia_number,
    c.peer_review_organization_approval_number,
    c.property_and_casualty_claim_number,
    c.ambulance_transport_reason,
    c.ambulance_transport_distance_units,
    c.ambulance_transport_distance_quantity,
    c.ambulance_round_trip_purpose_description,
    c.ambulance_stretcher_purpose_description,
    c.ambulance_patient_count,
    c.ambulance_pickup_zip_from_value_code,
    c.ambulance_pickup_address_id,
    c.ambulance_dropoff_address_id,
    c.ambulance_applicable_conditions,
    c.ambulance_not_applicable_conditions,
    c.spinal_manipulation_condition,
    c.spinal_manipulation_patient_condition_note1,
    c.spinal_manipulation_patient_condition_note2,
    c.dme_certification_type,
    c.dme_need_duration_units,
    c.dme_need_duration_quantity,
    c.dme_applicable_conditions,
    c.dme_not_applicable_conditions,
    c.vision_spectacle_lenses_applicable_conditions,
    c.vision_spectacle_lenses_not_applicable_conditions,
    c.vision_spectacle_frames_applicable_conditions,
    c.vision_spectacle_frames_not_applicable_conditions,
    c.vision_contacts_applicable_conditions,
    c.vision_contacts_not_applicable_conditions,
    c.is_homebound,
    c.epsdt_referral_applicable_conditions,
    c.epsdt_referral_not_applicable_conditions,
    c.condition_codes,
    c.reprice_methodology,
    c.reprice_allowed_amount,
    c.reprice_savings_amount,
    c.reprice_organization_id,
    c.reprice_per_diem_or_flat_rate_amount,
    c.reprice_approved_drg_or_apg_code,
    c.reprice_approved_drg_or_apg_amount,
    c.reprice_approved_revenue_code,
    c.reprice_approved_units,
    c.reprice_approved_quantity,
    c.reprice_reject_reason,
    c.reprice_policy_compliance,
    c.reprice_exception,
    c.data_errors,
    s.solution_id,
    s.external_solution_id,
    s.solution_name
   FROM claims.claim c
     JOIN claims.trading_partner_submission submission USING (trading_partner_submission_id)
     JOIN claims.trading_partner tp ON tp.trading_partner_id = submission.trading_partner_id
     JOIN claims.solution s ON s.solution_id = tp.solution_id;

Column	Data Type	Indexed
claim_id	bigint	No
trading_partner_submission_id	bigint	No
usage_indicator	usage_indicator_type	No
transaction_purpose	transaction_set_purpose_type	No
transaction_date	date	No
transaction_type	transaction_type	No
receiver_id_qualifier	identification_type	No
receiver_id	text	No
submitter_id_qualifier	identification_type	No
submitter_id	text	No
submitter_name	text	No
pay_to_plan_id	bigint	No
claim_member_id	bigint	No
patient_death_date	date	No
patient_weight_units	unit_type	No
patient_weight_quantity	numeric	No
patient_height_units	unit_type	No
patient_height_quantity	numeric	No
patient_is_pregnant	boolean	No
tpa_adjudication_method	adjudication_method_type	No
tpa_claim_status	text	No
patient_control_number	text	No
claim_billed_amount	numeric	No
facility_type	text	No
claim_type	claim_type	No
form_type	form_type	No
claim_frequency_type	text	No
provider_signature_on_file	yes_no_response_type	No
provider_accept_assignment	provider_accept_assignment_type	No
patient_assigned_benefits	yes_no_response_type	No
release_of_information	release_of_information_type	No
patient_signature_source	patient_signature_source_type	No
related_causes	related_cause_type[]	No
auto_accident_state	text	No
auto_accident_country	text	No
special_program	special_program_type	No
claim_submission_reason	claim_submission_reason_type	No
delay_reason	delay_reason_type	No
earliest_service_date	date	No
latest_service_date	date	No
statement_date_from	date	No
statement_date_through	date	No
admit_date_or_datetime	timestamp	No
discharge_date_or_hour	timestamp	No
initial_treatment_date	date	No
onset_of_symptoms_or_illness_date	date	No
accident_date	date	No
last_seen_date	date	No
repricer_received_date	date	No
acute_manifestation_date	date	No
last_menstrual_period_date	date	No
last_x_ray_date	date	No
prescription_date	date	No
disability_start_date	date	No
disability_end_date	date	No
last_worked_date	date	No
return_to_work_date	date	No
assumed_care_start	date	No
relinquished_care_date	date	No
first_visit_or_consultation_date	date	No
tpa_received_date	date	No
service_date_from	date	No
service_date_through	date	No
appliance_placement_date	date	No
facility_admission_type	text	No
facility_admission_source	text	No
facility_patient_status	text	No
orthodontic_treatment_months_total	numeric	No
orthodontic_treatment_months_remaining	numeric	No
orthodontic_treatment_indicator	text	No
contract_type	contract_type	No
contract_amount	numeric	No
contract_billed_percentage	numeric	No
contract_identification_code	text	No
contract_terms_discount_percent	numeric	No
contract_version_identifier	text	No
patient_amount_paid	numeric	No
estimated_patient_responsibility	numeric	No
predetermination_of_benefits_identifier	text	No
service_authorization_exception_code	text	No
mandatory_medicare_crossover_indicator	text	No
mammography_certification_number	text	No
referral_number	text	No
claim_number	text	No
payer_claim_number	text	No
repriced_claim_number	text	No
adjusted_repriced_claim_number	text	No
investigational_device_exemption_number	text	No
medical_record_number	text	No
demonstration_project_identifier	text	No
care_plan_oversight_number	text	No
its_number	text	No
icn_number	text	No
prior_authorization_number	text	No
clia_number	text	No
peer_review_organization_approval_number	text	No
property_and_casualty_claim_number	text	No
ambulance_transport_reason	ambulance_transport_reason_type	No
ambulance_transport_distance_units	unit_type	No
ambulance_transport_distance_quantity	numeric	No
ambulance_round_trip_purpose_description	text	No
ambulance_stretcher_purpose_description	text	No
ambulance_patient_count	smallint	No
ambulance_pickup_zip_from_value_code	text	No
ambulance_pickup_address_id	bigint	No
ambulance_dropoff_address_id	bigint	No
ambulance_applicable_conditions	text[]	No
ambulance_not_applicable_conditions	text[]	No
spinal_manipulation_condition	nature_of_condition_type	No
spinal_manipulation_patient_condition_note1	text	No
spinal_manipulation_patient_condition_note2	text	No
dme_certification_type	certification_type	No
dme_need_duration_units	unit_type	No
dme_need_duration_quantity	numeric	No
dme_applicable_conditions	condition_indicator_type[]	No
dme_not_applicable_conditions	condition_indicator_type[]	No
vision_spectacle_lenses_applicable_conditions	text[]	No
vision_spectacle_lenses_not_applicable_conditions	text[]	No
vision_spectacle_frames_applicable_conditions	text[]	No
vision_spectacle_frames_not_applicable_conditions	text[]	No
vision_contacts_applicable_conditions	text[]	No
vision_contacts_not_applicable_conditions	text[]	No
is_homebound	boolean	No
epsdt_referral_applicable_conditions	text[]	No
epsdt_referral_not_applicable_conditions	text[]	No
condition_codes	text[]	No
reprice_methodology	pricing_methodology_type	No
reprice_allowed_amount	numeric	No
reprice_savings_amount	numeric	No
reprice_organization_id	text	No
reprice_per_diem_or_flat_rate_amount	numeric	No
reprice_approved_drg_or_apg_code	text	No
reprice_approved_drg_or_apg_amount	numeric	No
reprice_approved_revenue_code	text	No
reprice_approved_units	unit_type	No
reprice_approved_quantity	numeric	No
reprice_reject_reason	reject_reason_type	No
reprice_policy_compliance	policy_compliance_type	No
reprice_exception	repricing_exception_type	No
data_errors	text[]	No
solution_id	bigint	No
external_solution_id	text	No
solution_name	text	No