line_item_view

Materialized: false

Definition

 SELECT line_item.line_item_id,
    line_item.claim_id,
    line_item.line_number,
    line_item.rev_code,
    line_item.product_service_code_qualifier,
    line_item.product_service_code,
    line_item.procedure_modifiers,
    line_item.procedure_description,
    line_item.product_service_id,
    line_item.billed_amount,
    line_item.quantity,
    line_item.units,
    line_item.place_of_service,
    line_item.diagnosis_code_pointers,
    line_item.facility_non_covered_amount,
    line_item.is_emergency_professional,
    line_item.is_epsdt_professional,
    line_item.is_family_planning_professional,
    line_item.copay_status,
    line_item.dental_oral_cavity_designations,
    line_item.dental_prosthesis_crown_or_inlay_placement,
    line_item.dme_length_of_medical_necessity_units,
    line_item.dme_length_of_medical_necessity_quantity,
    line_item.dme_rental_price,
    line_item.dme_purchase_price,
    line_item.dme_rental_billing_frequency,
    line_item.ambulance_patient_weight_units,
    line_item.ambulance_patient_weight_quantity,
    line_item.ambulance_transport_reason,
    line_item.ambulance_transport_distance_units,
    line_item.ambulance_transport_distance_quantity,
    line_item.ambulance_round_trip_purpose_description,
    line_item.ambulance_stretcher_purpose_description,
    line_item.ambulance_pickup_address_id,
    line_item.ambulance_dropoff_address_id,
    line_item.ambulance_applicable_conditions,
    line_item.ambulance_not_applicable_conditions,
    line_item.ambulance_patient_count,
    line_item.dme_certification_type,
    line_item.dme_need_duration_units,
    line_item.dme_need_duration_quantity,
    line_item.dme_applicable_conditions,
    line_item.dme_not_applicable_conditions,
    line_item.dme_certification_date,
    line_item.rendering_provider_is_hospice_employee,
    line_item.service_date_from,
    line_item.service_date_through,
    line_item.tpa_processed_date,
    line_item.prescription_date,
    line_item.begin_therapy_date,
    line_item.last_certification_date,
    line_item.last_seen_date,
    line_item.most_recent_hemoglobin_or_hematocrit_date,
    line_item.most_recent_serum_creatine_date,
    line_item.shipped_date,
    line_item.last_x_ray_date,
    line_item.initial_treatment_date,
    line_item.estimated_prior_placement_date,
    line_item.prior_placement_date,
    line_item.appliance_placement_date,
    line_item.replacement_date,
    line_item.treatment_start_date,
    line_item.treatment_completion_date,
    line_item.obstetric_anesthesia_additional_units,
    line_item.contract_type,
    line_item.contract_amount,
    line_item.contract_billed_percentage,
    line_item.contract_identification_code,
    line_item.contract_terms_discount_percent,
    line_item.contract_version_identifier,
    line_item.repriced_line_item_number,
    line_item.adjusted_repriced_line_item_number,
    line_item.line_item_prior_authorization_number,
    line_item.line_item_provider_control_number,
    line_item.intermediary_authorization_type,
    line_item.intermediary_authorization_identifier,
    line_item.mammography_certification_number,
    line_item.clia_number,
    line_item.referring_clia_number,
    line_item.immunization_batch_number,
    line_item.referral_number,
    line_item.predetermination_of_benefits_identifier,
    line_item.tpa_network_identifier,
    line_item.tpa_network_name,
    line_item.tpa_network_status,
    line_item.tpa_line_item_status,
    line_item.sales_tax_amount,
    line_item.service_tax_amount,
    line_item.facility_tax_amount,
    line_item.postage_amount,
    line_item.purchased_service_provider_identifier,
    line_item.purchased_service_billed_amount,
    line_item.reprice_methodology,
    line_item.reprice_allowed_amount,
    line_item.reprice_savings_amount,
    line_item.reprice_organization_identifier,
    line_item.reprice_per_diem_or_flat_rate_amount,
    line_item.reprice_approved_drg_or_apg_code,
    line_item.reprice_approved_drg_or_apg_amount,
    line_item.reprice_approved_revenue_code,
    line_item.reprice_approved_product_service_code_qualifier,
    line_item.reprice_approved_product_service_code,
    line_item.reprice_approved_units,
    line_item.reprice_approved_quantity,
    line_item.reprice_reject_reason,
    line_item.reprice_policy_compliance,
    line_item.reprice_exception,
    line_item.drug_ndc,
    line_item.drug_quantity,
    line_item.drug_units,
    line_item.drug_link_sequence_number,
    line_item.prescription_reference_number,
    line_item.associated_prescription_reference_number,
    line_item.associated_prescription_service_date,
    line_item.rx_fill_number,
    line_item.rx_days_supply,
    line_item.rx_dispense_as_written,
    line_item.rx_refills_authorized,
    line_item.prescription_origin,
    line_item.rx_submission_clarifications,
    line_item.rx_special_packaging,
    line_item.originally_prescribed_product_service_code_qualifier,
    line_item.originally_prescribed_product_service_code,
    line_item.originally_prescribed_quantity,
    line_item.scheduled_prescription_id_number,
    line_item.rx_level_of_service,
    line_item.prior_authorization_type,
    line_item.rx_dispensing_status,
    line_item.rx_quantity_intended,
    line_item.rx_days_supply_intended,
    line_item.rx_route_of_administration,
    line_item.rx_compound_type,
    line_item.pharmacy_service_type,
    line_item.rx_ingredient_cost_submitted,
    line_item.rx_ingredient_cost_paid,
    line_item.rx_ingredient_cost_basis_submitted,
    line_item.rx_ingredient_reimbursement_basis,
    line_item.rx_dispensing_fee_submitted,
    line_item.rx_dispensing_fee_paid,
    line_item.rx_dispensing_fee_basis_of_calculation,
    line_item.rx_incentive_amount_submitted,
    line_item.rx_incentive_amount_paid,
    line_item.rx_professional_service_fee_submitted,
    line_item.rx_professional_service_fee_paid,
    line_item.rx_delivery_cost_submitted,
    line_item.rx_delivery_amount_paid,
    line_item.rx_shipping_cost_submitted,
    line_item.rx_shipping_amount_paid,
    line_item.rx_postage_cost_submitted,
    line_item.rx_postage_amount_paid,
    line_item.rx_administrative_cost_submitted,
    line_item.rx_administrative_amount_paid,
    line_item.rx_compound_preparation_cost_submitted,
    line_item.rx_compound_preparation_amount_paid,
    line_item.rx_medication_administration_cost_submitted,
    line_item.rx_medication_administration_amount_paid,
    line_item.rx_medication_synchronization_cost_submitted,
    line_item.rx_medication_synchronization_amount_paid,
    line_item.rx_adherence_packaging_cost_submitted,
    line_item.rx_adherence_packaging_amount_paid,
    line_item.rx_flat_sales_tax_submitted,
    line_item.rx_flat_sales_tax_paid,
    line_item.rx_flat_sales_tax_basis_of_calculation,
    line_item.rx_percentage_sales_tax_submitted,
    line_item.rx_percentage_sales_tax_paid,
    line_item.rx_percentage_sales_tax_rate_submitted,
    line_item.rx_percentage_sales_tax_rate_paid,
    line_item.rx_percentage_sales_tax_basis_submitted,
    line_item.rx_percentage_sales_tax_basis_paid,
    line_item.rx_percentage_sales_tax_basis_of_calculation,
    line_item.rx_tax_exempt_indicator,
    line_item.rx_usual_and_customary_charge,
    line_item.rx_cob_paid_amount_recognized,
    line_item.rx_total_amount_paid,
    line_item.rx_amount_attributed_to_sales_tax,
    line_item.rx_accumulated_deductible_amount,
    line_item.rx_remaining_deductible_amount,
    line_item.rx_remaining_benefit_amount,
    line_item.rx_amount_applied_to_deductible,
    line_item.rx_amount_exceeding_benefit_max,
    line_item.rx_copay_basis_of_calculation,
    line_item.rx_coinsurance_amount_basis_of_calculation,
    line_item.rx_amount_attributed_to_processor_fee,
    line_item.rx_patient_sales_tax_amount,
    line_item.rx_plan_sales_tax_amount,
    line_item.rx_estimated_generic_savings,
    line_item.rx_spending_account_amount_remaining,
    line_item.rx_health_plan_funded_assistance_amount,
    line_item.rx_amount_attributed_to_provider_network_selection,
    line_item.rx_amount_attributed_to_brand_selection,
    line_item.rx_amount_attributed_to_non_preferred_formulary_selection,
    line_item.rx_amount_attributed_to_brand_non_preferred_formulary_selection,
    line_item.rx_amount_attributed_to_coverage_gap,
    line_item.rx_ingredient_cost_reimbursable_amount,
    line_item.rx_dispensing_fee_reimbursable_amount,
    line_item.coupon_type,
    line_item.coupon_number,
    line_item.coupon_value_amount,
    line_item.rx_compound_dosage_form,
    line_item.rx_compound_dispensing_unit,
    line_item.rx_generic_equivalent_product_service_code_qualifier,
    line_item.rx_generic_equivalent_product_service_code,
    line_item.data_errors,
    line_item.payment_limit,
    line_item.billed_adjustment,
    line_item.allowed_amount,
    line_item.not_covered_amount,
    line_item.allowed_adjustment_amount,
    line_item.plan_payment_amount,
    line_item.cob_paid_amount,
    line_item.copay_amount,
    line_item.coinsurance_amount,
    line_item.deductible_amount,
    line_item.payment_identification_code,
    line_item.payment_method_type,
    line_item.payment_date,
    line_item.payment_to_provider,
    line_item.payment_to_member,
    nubc_revenue_code_lookup.title AS revenue_code_title,
    nubc_revenue_code_group_lookup.title AS revenue_code_group_title,
    product_service_code_lookup.long_description AS product_service_long_description,
    product_service_code_lookup.short_description AS product_service_short_description,
    product_service_code_lookup.medium_description AS product_service_medium_description,
    product_service_code_lookup.consumer_friendly_description AS product_service_consumer_friendly_description,
    hcup_hcpcs_group_lookup.hcup_hcpcs_group_code,
    hcup_hcpcs_group_lookup.description AS hcup_hcpcs_group_description
   FROM claims.line_item
     LEFT JOIN claims.product_service_code_lookup ON product_service_code_lookup.product_service_id = line_item.product_service_id
     LEFT JOIN claims.hcpcs_hcup_group ON hcpcs_hcup_group.product_service_id = line_item.product_service_id
     LEFT JOIN claims.nubc_revenue_code_lookup ON nubc_revenue_code_lookup.revenue_code = line_item.rev_code
     LEFT JOIN claims.nubc_revenue_code_group_lookup ON nubc_revenue_code_group_lookup.revenue_code_group = nubc_revenue_code_lookup.revenue_code_group
     LEFT JOIN claims.cpt_hcup_group ON cpt_hcup_group.product_service_id = line_item.product_service_id
     LEFT JOIN claims.hcup_hcpcs_group_lookup ON hcup_hcpcs_group_lookup.hcup_hcpcs_group_id = COALESCE(hcpcs_hcup_group.hcup_hcpcs_group_id, cpt_hcup_group.hcup_hcpcs_group_id);

Column	Data Type	Indexed
line_item_id	bigint	No
claim_id	bigint	No
line_number	text	No
rev_code	text	No
product_service_code_qualifier	product_service_code_type	No
product_service_code	text	No
procedure_modifiers	text[]	No
procedure_description	text	No
product_service_id	bigint	No
billed_amount	numeric	No
quantity	numeric	No
units	text	No
place_of_service	text	No
diagnosis_code_pointers	smallint[]	No
facility_non_covered_amount	numeric	No
is_emergency_professional	boolean	No
is_epsdt_professional	boolean	No
is_family_planning_professional	boolean	No
copay_status	copay_status_type	No
dental_oral_cavity_designations	dental_oral_cavity_designation_type[]	No
dental_prosthesis_crown_or_inlay_placement	dental_prosthesis_crown_or_inlay_placement_type	No
dme_length_of_medical_necessity_units	unit_type	No
dme_length_of_medical_necessity_quantity	numeric	No
dme_rental_price	numeric	No
dme_purchase_price	numeric	No
dme_rental_billing_frequency	frequency_code	No
ambulance_patient_weight_units	unit_type	No
ambulance_patient_weight_quantity	numeric	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_pickup_address_id	bigint	No
ambulance_dropoff_address_id	bigint	No
ambulance_applicable_conditions	condition_indicator_type[]	No
ambulance_not_applicable_conditions	condition_indicator_type[]	No
ambulance_patient_count	smallint	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
dme_certification_date	date	No
rendering_provider_is_hospice_employee	boolean	No
service_date_from	date	No
service_date_through	date	No
tpa_processed_date	date	No
prescription_date	date	No
begin_therapy_date	date	No
last_certification_date	date	No
last_seen_date	date	No
most_recent_hemoglobin_or_hematocrit_date	date	No
most_recent_serum_creatine_date	date	No
shipped_date	date	No
last_x_ray_date	date	No
initial_treatment_date	date	No
estimated_prior_placement_date	date	No
prior_placement_date	date	No
appliance_placement_date	date	No
replacement_date	date	No
treatment_start_date	date	No
treatment_completion_date	date	No
obstetric_anesthesia_additional_units	numeric	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
repriced_line_item_number	text	No
adjusted_repriced_line_item_number	text	No
line_item_prior_authorization_number	text	No
line_item_provider_control_number	text	No
intermediary_authorization_type	text	No
intermediary_authorization_identifier	text	No
mammography_certification_number	text	No
clia_number	text	No
referring_clia_number	text	No
immunization_batch_number	text	No
referral_number	text	No
predetermination_of_benefits_identifier	text	No
tpa_network_identifier	text	No
tpa_network_name	text	No
tpa_network_status	text	No
tpa_line_item_status	text	No
sales_tax_amount	numeric	No
service_tax_amount	numeric	No
facility_tax_amount	numeric	No
postage_amount	numeric	No
purchased_service_provider_identifier	text	No
purchased_service_billed_amount	numeric	No
reprice_methodology	pricing_methodology_type	No
reprice_allowed_amount	numeric	No
reprice_savings_amount	numeric	No
reprice_organization_identifier	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_product_service_code_qualifier	product_service_code_type	No
reprice_approved_product_service_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
drug_ndc	text	No
drug_quantity	numeric	No
drug_units	unit_type	No
drug_link_sequence_number	text	No
prescription_reference_number	text	No
associated_prescription_reference_number	text	No
associated_prescription_service_date	date	No
rx_fill_number	integer	No
rx_days_supply	numeric	No
rx_dispense_as_written	rx_dispense_as_written_type	No
rx_refills_authorized	numeric	No
prescription_origin	prescription_origin_type	No
rx_submission_clarifications	rx_submission_clarification_type[]	No
rx_special_packaging	rx_special_packaging_type	No
originally_prescribed_product_service_code_qualifier	product_service_code_type	No
originally_prescribed_product_service_code	text	No
originally_prescribed_quantity	numeric	No
scheduled_prescription_id_number	text	No
rx_level_of_service	rx_level_of_service_type	No
prior_authorization_type	prior_authorization_type	No
rx_dispensing_status	rx_dispensing_status_type	No
rx_quantity_intended	numeric	No
rx_days_supply_intended	numeric	No
rx_route_of_administration	text	No
rx_compound_type	rx_compound_type	No
pharmacy_service_type	pharmacy_service_type	No
rx_ingredient_cost_submitted	numeric	No
rx_ingredient_cost_paid	numeric	No
rx_ingredient_cost_basis_submitted	rx_ingredient_cost_basis_type	No
rx_ingredient_reimbursement_basis	rx_ingredient_cost_basis_type	No
rx_dispensing_fee_submitted	numeric	No
rx_dispensing_fee_paid	numeric	No
rx_dispensing_fee_basis_of_calculation	basis_of_calculation_type	No
rx_incentive_amount_submitted	numeric	No
rx_incentive_amount_paid	numeric	No
rx_professional_service_fee_submitted	numeric	No
rx_professional_service_fee_paid	numeric	No
rx_delivery_cost_submitted	numeric	No
rx_delivery_amount_paid	numeric	No
rx_shipping_cost_submitted	numeric	No
rx_shipping_amount_paid	numeric	No
rx_postage_cost_submitted	numeric	No
rx_postage_amount_paid	numeric	No
rx_administrative_cost_submitted	numeric	No
rx_administrative_amount_paid	numeric	No
rx_compound_preparation_cost_submitted	numeric	No
rx_compound_preparation_amount_paid	numeric	No
rx_medication_administration_cost_submitted	numeric	No
rx_medication_administration_amount_paid	numeric	No
rx_medication_synchronization_cost_submitted	numeric	No
rx_medication_synchronization_amount_paid	numeric	No
rx_adherence_packaging_cost_submitted	numeric	No
rx_adherence_packaging_amount_paid	numeric	No
rx_flat_sales_tax_submitted	numeric	No
rx_flat_sales_tax_paid	numeric	No
rx_flat_sales_tax_basis_of_calculation	basis_of_calculation_type	No
rx_percentage_sales_tax_submitted	numeric	No
rx_percentage_sales_tax_paid	numeric	No
rx_percentage_sales_tax_rate_submitted	numeric	No
rx_percentage_sales_tax_rate_paid	numeric	No
rx_percentage_sales_tax_basis_submitted	rx_percentage_sales_tax_basis_type	No
rx_percentage_sales_tax_basis_paid	rx_percentage_sales_tax_basis_type	No
rx_percentage_sales_tax_basis_of_calculation	basis_of_calculation_type	No
rx_tax_exempt_indicator	rx_tax_exempt_indicator_type	No
rx_usual_and_customary_charge	numeric	No
rx_cob_paid_amount_recognized	numeric	No
rx_total_amount_paid	numeric	No
rx_amount_attributed_to_sales_tax	numeric	No
rx_accumulated_deductible_amount	numeric	No
rx_remaining_deductible_amount	numeric	No
rx_remaining_benefit_amount	numeric	No
rx_amount_applied_to_deductible	numeric	No
rx_amount_exceeding_benefit_max	numeric	No
rx_copay_basis_of_calculation	basis_of_calculation_type	No
rx_coinsurance_amount_basis_of_calculation	basis_of_calculation_type	No
rx_amount_attributed_to_processor_fee	numeric	No
rx_patient_sales_tax_amount	numeric	No
rx_plan_sales_tax_amount	numeric	No
rx_estimated_generic_savings	numeric	No
rx_spending_account_amount_remaining	numeric	No
rx_health_plan_funded_assistance_amount	numeric	No
rx_amount_attributed_to_provider_network_selection	numeric	No
rx_amount_attributed_to_brand_selection	numeric	No
rx_amount_attributed_to_non_preferred_formulary_selection	numeric	No
rx_amount_attributed_to_brand_non_preferred_formulary_selection	numeric	No
rx_amount_attributed_to_coverage_gap	numeric	No
rx_ingredient_cost_reimbursable_amount	numeric	No
rx_dispensing_fee_reimbursable_amount	numeric	No
coupon_type	coupon_type	No
coupon_number	text	No
coupon_value_amount	text	No
rx_compound_dosage_form	rx_compound_dosage_form_type	No
rx_compound_dispensing_unit	unit_type	No
rx_generic_equivalent_product_service_code_qualifier	product_service_code_type	No
rx_generic_equivalent_product_service_code	text	No
data_errors	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
revenue_code_title	text	No
revenue_code_group_title	text	No
product_service_long_description	text	No
product_service_short_description	text	No
product_service_medium_description	text	No
product_service_consumer_friendly_description	text	No
hcup_hcpcs_group_code	smallint	No
hcup_hcpcs_group_description	text	No