ub04_detail_view

Materialized: false

Definition

 WITH cob AS (
         SELECT claim_1.claim_id,
            COALESCE(claim_member.payer_responsibility_sequence, 'Primary'::claims.payer_responsibility_sequence_type) AS payer_responsibility_sequence,
            insurer_payer.name,
            insurer_payer.payer_identification_code,
            NULL::numeric AS payer_amount_paid,
            NULL::numeric AS remaining_patient_liability
           FROM claims.claim claim_1
             JOIN claims.claim_member USING (claim_member_id)
             LEFT JOIN claims.insurer_payer USING (insurer_payer_id)
        UNION ALL
         SELECT claim_coordination_of_benefits.claim_id,
            claim_coordination_of_benefits.payer_responsibility_sequence,
            insurer_payer.name,
            insurer_payer.payer_identification_code,
            claim_coordination_of_benefits.payer_amount_paid,
            claim_coordination_of_benefits.remaining_patient_liability
           FROM claims.claim_coordination_of_benefits
             LEFT JOIN claims.insurer_payer USING (insurer_payer_id)
        ), other_providers_ranked AS (
         SELECT claim_1.claim_id,
                CASE cp.provider_type
                    WHEN 'Referring Provider'::claims.entity_identifier_type THEN 'DN'::text
                    WHEN 'Other Operating Physician'::claims.entity_identifier_type THEN 'ZZ'::text
                    WHEN 'Rendering Provider'::claims.entity_identifier_type THEN '82'::text
                    ELSE NULL::text
                END AS provider_type_qualifier,
            ps.provider_seen_id,
            ps.entity_type,
            ps.name_last_or_organization,
            ps.name_first,
            ps.name_middle,
            ps.name_prefix,
            ps.name_suffix,
            ps.primary_id_qualifier,
            ps.address_id,
            ps.npi,
            ps.tax_id,
            ps.state_license_number,
            ps.unique_provider_identification_number,
            ps.provider_commercial_number,
            ps.location_number,
            ps.contact_name,
            ps.email_address_id,
            ps.fax_number_id,
            ps.phone_number_id,
            ps.phone_extension,
            ps.relationship_to_npi,
            ps.corrected_npi,
            row_number() OVER (PARTITION BY claim_1.claim_id ORDER BY (
                CASE cp.provider_type
                    WHEN 'Referring Provider'::claims.entity_identifier_type THEN 1
                    WHEN 'Other Operating Physician'::claims.entity_identifier_type THEN 2
                    WHEN 'Rendering Provider'::claims.entity_identifier_type THEN 3
                    ELSE 4
                END)) AS rn
           FROM claims.claim claim_1
             LEFT JOIN claims.claim_provider cp ON cp.claim_id = claim_1.claim_id AND (cp.provider_type = ANY (ARRAY['Referring Provider'::claims.entity_identifier_type, 'Other Operating Physician'::claims.entity_identifier_type, 'Rendering Provider'::claims.entity_identifier_type]))
             LEFT JOIN claims.provider_seen ps ON ps.provider_seen_id = cp.provider_seen_id
          WHERE cp.provider_seen_id IS NOT NULL
        ), notes AS (
         SELECT claim_note.claim_id,
            claim_note.note,
            row_number() OVER (PARTITION BY claim_note.claim_id ORDER BY claim_note.note) AS rn
           FROM claims.claim_note
          WHERE claim_note.reference = 'Additional Information'::claims.note_reference_type
        )
 SELECT claim.claim_id,
    payer1.name AS payer1_name,
    payer2.name AS payer2_name,
    payer3.name AS payer3_name,
    payer1.payer_identification_code AS payer1_identification_code,
    payer2.payer_identification_code AS payer2_identification_code,
    payer3.payer_identification_code AS payer3_identification_code,
    claim.release_of_information AS payer1_release_of_information,
    NULL::text AS payer2_release_of_information,
    NULL::text AS payer3_release_of_information,
    claim.patient_assigned_benefits AS payer1_patient_assigned_benefits,
    NULL::text AS payer2_patient_assigned_benefits,
    NULL::text AS payer3_patient_assigned_benefits,
    payer1.payer_amount_paid AS payer1_prior_payment_amount,
    payer2.payer_amount_paid AS payer2_prior_payment_amount,
    payer3.payer_amount_paid AS payer3_prior_payment_amount,
    payer1.remaining_patient_liability AS payer1_amount_due,
    payer2.remaining_patient_liability AS payer2_amount_due,
    payer3.remaining_patient_liability AS payer3_amount_due,
    billprov.taxonomy AS billing_provider_taxonomy,
    billprovseen.provider_commercial_number AS billing_provider_commercial_number,
    billprovseen.location_number AS billing_provider_location_number,
    attendprovseen.npi AS attending_provider_npi,
        CASE
            WHEN NULLIF(attendprovseen.provider_commercial_number, ''::text) IS NOT NULL THEN 'G2'::text
            WHEN NULLIF(attendprovseen.state_license_number, ''::text) IS NOT NULL THEN '0B'::text
            WHEN NULLIF(attendprovseen.unique_provider_identification_number, ''::text) IS NOT NULL THEN '1G'::text
            WHEN NULLIF(attendprovseen.location_number, ''::text) IS NOT NULL THEN 'LU'::text
            ELSE NULL::text
        END AS attending_provider_other_id_qualifier,
    COALESCE(NULLIF(attendprovseen.provider_commercial_number, ''::text), NULLIF(attendprovseen.state_license_number, ''::text), NULLIF(attendprovseen.unique_provider_identification_number, ''::text), NULLIF(attendprovseen.location_number, ''::text)) AS attending_provider_other_id,
    attendprovseen.name_last_or_organization AS attending_provider_name_last,
    attendprovseen.name_first AS attending_provider_name_first,
    opprovseen.npi AS operating_physician_npi,
        CASE
            WHEN NULLIF(opprovseen.provider_commercial_number, ''::text) IS NOT NULL THEN 'G2'::text
            WHEN NULLIF(opprovseen.state_license_number, ''::text) IS NOT NULL THEN '0B'::text
            WHEN NULLIF(opprovseen.unique_provider_identification_number, ''::text) IS NOT NULL THEN '1G'::text
            WHEN NULLIF(opprovseen.location_number, ''::text) IS NOT NULL THEN 'LU'::text
            ELSE NULL::text
        END AS operating_physician_other_id_qualifier,
    COALESCE(NULLIF(opprovseen.provider_commercial_number, ''::text), NULLIF(opprovseen.state_license_number, ''::text), NULLIF(opprovseen.unique_provider_identification_number, ''::text), NULLIF(opprovseen.location_number, ''::text)) AS operating_physician_other_id,
    opprovseen.name_last_or_organization AS operating_physician_name_last,
    opprovseen.name_first AS operating_physician_name_first,
    othprovseen1.provider_type_qualifier AS other_provider1_provider_type_qualifier,
    othprovseen1.npi AS other_provider1_npi,
        CASE
            WHEN NULLIF(othprovseen1.provider_commercial_number, ''::text) IS NOT NULL THEN 'G2'::text
            WHEN NULLIF(othprovseen1.state_license_number, ''::text) IS NOT NULL THEN '0B'::text
            WHEN NULLIF(othprovseen1.unique_provider_identification_number, ''::text) IS NOT NULL THEN '1G'::text
            WHEN NULLIF(othprovseen1.location_number, ''::text) IS NOT NULL THEN 'LU'::text
            ELSE NULL::text
        END AS other_provider1_other_id_qualifier,
    COALESCE(NULLIF(othprovseen1.provider_commercial_number, ''::text), NULLIF(othprovseen1.state_license_number, ''::text), NULLIF(othprovseen1.unique_provider_identification_number, ''::text), NULLIF(othprovseen1.location_number, ''::text)) AS other_provider1_other_id,
    othprovseen1.name_last_or_organization AS other_provider1_name_last,
    othprovseen1.name_first AS other_provider1_name_first,
    othprovseen2.provider_type_qualifier AS other_provider2_provider_type_qualifier,
    othprovseen2.npi AS other_provider2_npi,
        CASE
            WHEN NULLIF(othprovseen2.provider_commercial_number, ''::text) IS NOT NULL THEN 'G2'::text
            WHEN NULLIF(othprovseen2.state_license_number, ''::text) IS NOT NULL THEN '0B'::text
            WHEN NULLIF(othprovseen2.unique_provider_identification_number, ''::text) IS NOT NULL THEN '1G'::text
            WHEN NULLIF(othprovseen2.location_number, ''::text) IS NOT NULL THEN 'LU'::text
            ELSE NULL::text
        END AS other_provider2_other_id_qualifier,
    COALESCE(NULLIF(othprovseen2.provider_commercial_number, ''::text), NULLIF(othprovseen2.state_license_number, ''::text), NULLIF(othprovseen2.unique_provider_identification_number, ''::text), NULLIF(othprovseen2.location_number, ''::text)) AS other_provider2_other_id,
    othprovseen2.name_last_or_organization AS other_provider2_name_last,
    othprovseen2.name_first AS other_provider2_name_first,
    note1.note AS claim_note1,
    note2.note AS claim_note2,
    note3.note AS claim_note3,
    note4.note AS claim_note4,
    tps.file_name AS source_file
   FROM claims.claim
     LEFT JOIN cob payer1 ON payer1.claim_id = claim.claim_id AND payer1.payer_responsibility_sequence = 'Primary'::claims.payer_responsibility_sequence_type
     LEFT JOIN cob payer2 ON payer2.claim_id = claim.claim_id AND payer2.payer_responsibility_sequence = 'Secondary'::claims.payer_responsibility_sequence_type
     LEFT JOIN cob payer3 ON payer3.claim_id = claim.claim_id AND payer3.payer_responsibility_sequence = 'Tertiary'::claims.payer_responsibility_sequence_type
     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.provider_seen billprovseen ON billprovseen.provider_seen_id = billprov.provider_seen_id
     LEFT JOIN claims.claim_provider attendprov ON attendprov.claim_id = claim.claim_id AND attendprov.provider_type = 'Attending Physician'::claims.entity_identifier_type
     LEFT JOIN claims.provider_seen attendprovseen ON attendprovseen.provider_seen_id = attendprov.provider_seen_id
     LEFT JOIN claims.claim_provider opprov ON opprov.claim_id = claim.claim_id AND opprov.provider_type = 'Operating Physician'::claims.entity_identifier_type
     LEFT JOIN claims.provider_seen opprovseen ON opprovseen.provider_seen_id = opprov.provider_seen_id
     LEFT JOIN other_providers_ranked othprovseen1 ON othprovseen1.claim_id = claim.claim_id AND othprovseen1.rn = 1
     LEFT JOIN other_providers_ranked othprovseen2 ON othprovseen2.claim_id = claim.claim_id AND othprovseen2.rn = 2
     LEFT JOIN notes note1 ON note1.claim_id = claim.claim_id AND note1.rn = 1
     LEFT JOIN notes note2 ON note2.claim_id = claim.claim_id AND note2.rn = 2
     LEFT JOIN notes note3 ON note3.claim_id = claim.claim_id AND note3.rn = 3
     LEFT JOIN notes note4 ON note4.claim_id = claim.claim_id AND note4.rn = 4
     JOIN claims.trading_partner_submission tps ON tps.trading_partner_submission_id = claim.trading_partner_submission_id
  WHERE claim.form_type = 'UB-04'::claims.form_type;

Column	Data Type	Indexed
claim_id	bigint	No
payer1_name	text	No
payer2_name	text	No
payer3_name	text	No
payer1_identification_code	text	No
payer2_identification_code	text	No
payer3_identification_code	text	No
payer1_release_of_information	release_of_information_type	No
payer2_release_of_information	text	No
payer3_release_of_information	text	No
payer1_patient_assigned_benefits	yes_no_response_type	No
payer2_patient_assigned_benefits	text	No
payer3_patient_assigned_benefits	text	No
payer1_prior_payment_amount	numeric	No
payer2_prior_payment_amount	numeric	No
payer3_prior_payment_amount	numeric	No
payer1_amount_due	numeric	No
payer2_amount_due	numeric	No
payer3_amount_due	numeric	No
billing_provider_taxonomy	text	No
billing_provider_commercial_number	text	No
billing_provider_location_number	text	No
attending_provider_npi	bigint	No
attending_provider_other_id_qualifier	text	No
attending_provider_other_id	text	No
attending_provider_name_last	text	No
attending_provider_name_first	text	No
operating_physician_npi	bigint	No
operating_physician_other_id_qualifier	text	No
operating_physician_other_id	text	No
operating_physician_name_last	text	No
operating_physician_name_first	text	No
other_provider1_provider_type_qualifier	text	No
other_provider1_npi	bigint	No
other_provider1_other_id_qualifier	text	No
other_provider1_other_id	text	No
other_provider1_name_last	text	No
other_provider1_name_first	text	No
other_provider2_provider_type_qualifier	text	No
other_provider2_npi	bigint	No
other_provider2_other_id_qualifier	text	No
other_provider2_other_id	text	No
other_provider2_name_last	text	No
other_provider2_name_first	text	No
claim_note1	text	No
claim_note2	text	No
claim_note3	text	No
claim_note4	text	No
source_file	text	No