line_item_everything_view

Materialized: false

Definition

 SELECT c.claim_id,
    c.claim_member_id,
    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.earliest_service_date,
    c.latest_service_date,
    c.statement_date_from,
    c.statement_date_through,
    c.admit_date_or_datetime,
    c.facility_admission_type,
    c.facility_admission_source,
    c.facility_patient_status,
    c.referral_number,
    c.claim_number,
    c.payer_claim_number,
    c.repriced_claim_number,
    c.adjusted_repriced_claim_number,
    c.medical_record_number,
    c.its_number,
    c.icn_number,
    c.prior_authorization_number,
    l.line_item_id,
    l.line_number,
    l.rev_code,
    l.revenue_code_title,
    l.revenue_code_group_title,
    l.procedure_qualifier,
    l.procedure_code,
    l.procedure_modifiers,
    l.procedure_description,
    l.procedure_long_description,
    l.procedure_short_description,
    l.procedure_medium_description,
    l.procedure_consumer_friendly_description,
    l.hcup_hcpcs_group_code,
    l.hcup_hcpcs_group_description,
    l.billed_amount,
    l.quantity,
    l.units,
    l.place_of_service,
    COALESCE(l.service_date_from, c.service_date_from) AS service_date_from,
    COALESCE(l.service_date_through, c.service_date_through) AS service_date_through,
    l.line_item_provider_control_number,
    l.line_item_prior_authorization_number,
    l.tpa_network_identifier,
    l.tpa_network_name,
    l.tpa_network_status,
    l.payment_limit,
    l.billed_adjustment,
    l.allowed_amount,
    l.not_covered_amount,
    l.allowed_adjustment_amount,
    l.plan_payment_amount,
    l.cob_paid_amount,
    l.copay_amount,
    l.coinsurance_amount,
    l.deductible_amount,
    l.payment_identification_code,
    l.payment_method_type,
    l.payment_date,
    l.payment_to_provider,
    l.payment_to_member
   FROM claims.claim_validation_wide_view c
     LEFT JOIN claims.line_item_view l ON l.claim_id = c.claim_id;

Column	Data Type	Indexed
claim_id	bigint	No
claim_member_id	bigint	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
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
facility_admission_type	text	No
facility_admission_source	text	No
facility_patient_status	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
medical_record_number	text	No
its_number	text	No
icn_number	text	No
prior_authorization_number	text	No
line_item_id	bigint	No
line_number	text	No
rev_code	text	No
revenue_code_title	text	No
revenue_code_group_title	text	No
procedure_qualifier	product_service_id_type	No
procedure_code	text	No
procedure_modifiers	text[]	No
procedure_description	text	No
procedure_long_description	text	No
procedure_short_description	text	No
procedure_medium_description	text	No
procedure_consumer_friendly_description	text	No
hcup_hcpcs_group_code	smallint	No
hcup_hcpcs_group_description	text	No
billed_amount	numeric	No
quantity	numeric	No
units	text	No
place_of_service	text	No
service_date_from	date	No
service_date_through	date	No
line_item_provider_control_number	text	No
line_item_prior_authorization_number	text	No
tpa_network_identifier	text	No
tpa_network_name	text	No
tpa_network_status	text	No
payment_limit	numeric	No
billed_adjustment	numeric	No
allowed_amount	numeric	No
not_covered_amount	numeric	No
allowed_adjustment_amount	numeric	No
plan_payment_amount	numeric	No
cob_paid_amount	numeric	No
copay_amount	numeric	No
coinsurance_amount	numeric	No
deductible_amount	numeric	No
payment_identification_code	text	No
payment_method_type	payment_method_type	No
payment_date	date	No
payment_to_provider	numeric	No
payment_to_member	numeric	No