ub04_patient_view

Materialized: false

Definition

 WITH cob AS NOT MATERIALIZED (
         SELECT claim_1.claim_id,
            COALESCE(mem.payer_responsibility_sequence, 'Primary'::claims.payer_responsibility_sequence_type) AS payer_responsibility_sequence,
            concat_ws(' '::text, NULLIF(sub.name_first, ''::text), NULLIF(sub.name_middle, ''::text), NULLIF(sub.name_last, ''::text)) AS name,
            mem.relationship_to_subscriber,
            mem.member_identification_code,
            mem.group_name,
            mem.group_or_policy_number
           FROM claims.claim claim_1
             JOIN claims.claim_member mem USING (claim_member_id)
             LEFT JOIN claims.subscriber_or_patient sub ON sub.subscriber_or_patient_id = mem.member_subscriber_or_patient_id
        UNION ALL
         SELECT cb.claim_id,
            cb.payer_responsibility_sequence,
            concat_ws(' '::text, NULLIF(sub.name_first, ''::text), NULLIF(sub.name_middle, ''::text), NULLIF(sub.name_last, ''::text)) AS name,
            cb.relationship_to_subscriber,
            sub.member_identification_code,
            cb.group_name,
            cb.group_or_policy_number
           FROM claims.claim_coordination_of_benefits cb
             LEFT JOIN claims.coordination_of_benefits_subscriber sub USING (coordination_of_benefits_subscriber_id)
        )
 SELECT claim.claim_id,
    claim.patient_control_number,
    claim.medical_record_number,
    member.member_ssn,
    concat_ws(' '::text, NULLIF(patient.name_first, ''::text), NULLIF(patient.name_middle, ''::text), NULLIF(patient.name_last, ''::text)) AS patient_name,
    concat_ws(' '::text, NULLIF(pataddr.address1, ''::text), NULLIF(pataddr.address2, ''::text)) AS patient_address,
    pataddr.city AS patient_city,
    pataddr.state_or_province AS patient_state,
    pataddr.postal_code AS patient_zip,
    COALESCE(NULLIF(pataddr.country_code, ''::text), 'US'::text) AS patient_country,
    patient.date_of_birth AS patient_birth_date,
    patient.gender_or_sex AS patient_sex,
    concat_ws(', '::text, NULLIF(concat_ws(' '::text, NULLIF(subscriber.name_first, ''::text), NULLIF(subscriber.name_middle, ''::text), NULLIF(subscriber.name_last, ''::text)), ''::text), NULLIF(concat_ws(' '::text, subaddr.address1, subaddr.address2), ''::text), NULLIF(concat_ws(' '::text, concat_ws(', '::text, NULLIF(subaddr.city, ''::text), NULLIF(subaddr.state_or_province, ''::text)), NULLIF(subaddr.postal_code, ''::text)), ''::text)) AS subscriber_name_and_address,
    cob1.name AS insured_name1,
    cob2.name AS insured_name2,
    cob3.name AS insured_name3,
    cob1.relationship_to_subscriber AS patient_relationship1,
    cob2.relationship_to_subscriber AS patient_relationship2,
    cob3.relationship_to_subscriber AS patient_relationship3,
    cob1.member_identification_code AS insured_identification_code1,
    cob2.member_identification_code AS insured_identification_code2,
    cob3.member_identification_code AS insured_identification_code3,
    cob1.group_name AS insured_group_name1,
    cob2.group_name AS insured_group_name2,
    cob3.group_name AS insured_group_name3,
    cob1.group_or_policy_number AS insured_group_number1,
    cob2.group_or_policy_number AS insured_group_number2,
    cob3.group_or_policy_number AS insured_group_number3
   FROM claims.claim
     LEFT JOIN claims.claim_member member ON member.claim_member_id = claim.claim_member_id
     LEFT JOIN claims.subscriber_or_patient patient ON patient.subscriber_or_patient_id = COALESCE(member.patient_subscriber_or_patient_id, member.member_subscriber_or_patient_id)
     LEFT JOIN claims.address_seen pataddr ON pataddr.address_id = patient.address_id
     LEFT JOIN claims.subscriber_or_patient subscriber ON subscriber.subscriber_or_patient_id = member.member_subscriber_or_patient_id
     LEFT JOIN claims.address_seen subaddr ON subaddr.address_id = subscriber.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
  WHERE claim.form_type = 'UB-04'::claims.form_type;

Column	Data Type	Indexed
claim_id	bigint	No
patient_control_number	text	No
medical_record_number	text	No
member_ssn	text	No
patient_name	text	No
patient_address	text	No
patient_city	text	No
patient_state	text	No
patient_zip	text	No
patient_country	text	No
patient_birth_date	date	No
patient_sex	gender_or_sex_type	No
subscriber_name_and_address	text	No
insured_name1	text	No
insured_name2	text	No
insured_name3	text	No
patient_relationship1	individual_relationship_type	No
patient_relationship2	individual_relationship_type	No
patient_relationship3	individual_relationship_type	No
insured_identification_code1	text	No
insured_identification_code2	text	No
insured_identification_code3	text	No
insured_group_name1	text	No
insured_group_name2	text	No
insured_group_name3	text	No
insured_group_number1	text	No
insured_group_number2	text	No
insured_group_number3	text	No