ub04_header_view

Materialized: false

Definition

 WITH cob AS NOT MATERIALIZED (
         SELECT claim_1.claim_id,
            COALESCE(claim_member.payer_responsibility_sequence, 'Primary'::claims.payer_responsibility_sequence_type) AS payer_responsibility_sequence,
            claim_1.payer_claim_number,
            claim_1.prior_authorization_number
           FROM claims.claim claim_1
             JOIN claims.claim_member USING (claim_member_id)
        UNION ALL
         SELECT claim_coordination_of_benefits.claim_id,
            claim_coordination_of_benefits.payer_responsibility_sequence,
            claim_coordination_of_benefits.payer_claim_number,
            claim_coordination_of_benefits.prior_authorization_number
           FROM claims.claim_coordination_of_benefits
        )
 SELECT claim.claim_id,
    tps.submit_time,
    billprovseen.name_last_or_organization AS billing_provider_name_last_or_organization,
    concat_ws(' '::text, billaddr.address1, billaddr.address2) AS billing_provider_address,
    concat_ws(' '::text, concat_ws(', '::text, NULLIF(billaddr.city, ''::text), NULLIF(billaddr.state_or_province, ''::text)), NULLIF(billaddr.postal_code, ''::text)) AS billing_provider_city_state_zip,
    concat_ws(' '::text, NULLIF(billphone.phone_number, ''::text), NULLIF(billfax.fax_number, ''::text), COALESCE(NULLIF(billaddr.country_code, ''::text), 'US'::text)) AS billing_provider_phone_fax_country,
    paytoprovseen.name_last_or_organization AS pay_to_provider_name_last_or_organization,
    concat_ws(' '::text, paytoaddr.address1, paytoaddr.address2) AS pay_to_provider_address,
    concat_ws(' '::text, concat_ws(', '::text, NULLIF(paytoaddr.city, ''::text), NULLIF(paytoaddr.state_or_province, ''::text)), NULLIF(paytoaddr.postal_code, ''::text)) AS pay_to_provider_city_state_zip,
    claim.facility_type || claim.claim_frequency_type AS type_of_bill,
    COALESCE(paytoprovseen.tax_id, billprovseen.tax_id) AS tax_id,
    claim.statement_date_from,
    claim.statement_date_through,
    claim.admit_date_or_datetime::date AS admit_date,
    claim.facility_admission_type AS type_of_visit,
    claim.facility_admission_source AS source_of_admission,
    claim.facility_patient_status AS patient_discharge_status,
    claim.auto_accident_state,
    billprovseen.npi AS billing_provider_npi,
    claim.claim_billed_amount AS claim_total_charges,
    claim.reprice_methodology,
    claim.reprice_allowed_amount,
    claim.reprice_savings_amount,
    claim.reprice_organization_id,
    claim.reprice_exception,
    cob1.prior_authorization_number AS prior_auth_number1,
    cob2.prior_authorization_number AS prior_auth_number2,
    cob3.prior_authorization_number AS prior_auth_number3,
    cob1.payer_claim_number AS document_control_number1,
    claim.repriced_claim_number,
    claim.adjusted_repriced_claim_number,
    cob2.payer_claim_number AS document_control_number2,
    cob3.payer_claim_number AS document_control_number3,
    drg.drg_code
   FROM claims.claim
     JOIN claims.trading_partner_submission tps ON tps.trading_partner_submission_id = claim.trading_partner_submission_id
     LEFT JOIN claims.claim_provider billprov ON billprov.claim_id = claim.claim_id AND billprov.provider_type = 'Billing Provider'::claims.entity_identifier_type
     LEFT JOIN claims.claim_provider paytoprov ON paytoprov.claim_id = claim.claim_id AND paytoprov.provider_type = 'Pay-to Provider'::claims.entity_identifier_type
     LEFT JOIN claims.provider_seen billprovseen ON billprovseen.provider_seen_id = billprov.provider_seen_id
     LEFT JOIN claims.provider_seen paytoprovseen ON paytoprovseen.provider_seen_id = paytoprov.provider_seen_id
     LEFT JOIN claims.address_seen billaddr ON billaddr.address_id = billprovseen.address_id
     LEFT JOIN claims.phone billphone ON billphone.phone_id = billprovseen.phone_number_id
     LEFT JOIN claims.fax billfax ON billfax.fax_id = billprovseen.fax_number_id
     LEFT JOIN claims.address_seen paytoaddr ON paytoaddr.address_id = paytoprovseen.address_id
     LEFT JOIN cob cob1 ON cob1.claim_id = claim.claim_id AND cob1.payer_responsibility_sequence = 'Primary'::claims.payer_responsibility_sequence_type
     LEFT JOIN cob cob2 ON cob2.claim_id = claim.claim_id AND cob2.payer_responsibility_sequence = 'Secondary'::claims.payer_responsibility_sequence_type
     LEFT JOIN cob cob3 ON cob3.claim_id = claim.claim_id AND cob3.payer_responsibility_sequence = 'Tertiary'::claims.payer_responsibility_sequence_type
     LEFT JOIN claims.claim_drg drg ON drg.claim_id = claim.claim_id
  WHERE claim.form_type = 'UB-04'::claims.form_type;

Column	Data Type	Indexed
claim_id	bigint	No
submit_time	timestamp	No
billing_provider_name_last_or_organization	text	No
billing_provider_address	text	No
billing_provider_city_state_zip	text	No
billing_provider_phone_fax_country	text	No
pay_to_provider_name_last_or_organization	text	No
pay_to_provider_address	text	No
pay_to_provider_city_state_zip	text	No
type_of_bill	text	No
tax_id	text	No
statement_date_from	date	No
statement_date_through	date	No
admit_date	date	No
type_of_visit	text	No
source_of_admission	text	No
patient_discharge_status	text	No
auto_accident_state	text	No
billing_provider_npi	bigint	No
claim_total_charges	numeric	No
reprice_methodology	pricing_methodology_type	No
reprice_allowed_amount	numeric	No
reprice_savings_amount	numeric	No
reprice_organization_id	text	No
reprice_exception	repricing_exception_type	No
prior_auth_number1	text	No
prior_auth_number2	text	No
prior_auth_number3	text	No
document_control_number1	text	No
repriced_claim_number	text	No
adjusted_repriced_claim_number	text	No
document_control_number2	text	No
document_control_number3	text	No
drg_code	text	No