line_item_reporting_view

Materialized: false

Definition

 WITH rendering AS (
         SELECT DISTINCT ON (claim_provider.claim_id) claim_provider.claim_id,
            claim_provider.provider_seen_id AS rendering_provider_id,
            concat_ws(' '::text, NULLIF(provider_seen.name_prefix, ''::text), NULLIF(provider_seen.name_first, ''::text), NULLIF(provider_seen.name_middle, ''::text), NULLIF(provider_seen.name_last_or_organization, ''::text), NULLIF(provider_seen.name_suffix, ''::text)) AS rendering_provider_name
           FROM claims.claim_provider
             JOIN claims.provider_seen USING (provider_seen_id)
          WHERE claim_provider.provider_type = 'Rendering Provider'::claims.entity_identifier_type
        ), triggers AS (
         SELECT DISTINCT ON (d.claim_id) d.claim_id,
            bool_or(cm.diagnosis_code IS NOT NULL) AS is_diagnosis_case_management_trigger,
            bool_or(sl.diagnosis_code_prefix IS NOT NULL) AS is_diagnosis_stop_loss_trigger,
            bool_or(cci.diagnosis_code IS NOT NULL) AS is_chronic_condition_indicator
           FROM claims.claim_diagnosis d
             LEFT JOIN claims.icd_diagnosis_lookup dl_1 USING (icd_diagnosis_id)
             LEFT JOIN claims.icd_diagnosis_stop_loss_trigger sl ON d.diagnosis_code ~~ (sl.diagnosis_code_prefix || '%'::text)
             LEFT JOIN claims.icd_diagnosis_horizon_blue_cross_case_management_lookup cm ON cm.diagnosis_code = d.diagnosis_code
             LEFT JOIN claims.hcup_diagnosis_chronic_condition_indicator_lookup cci ON cci.diagnosis_code = d.diagnosis_code AND cci.release_date = dl_1.release_date
          GROUP BY d.claim_id
        )
 SELECT l.claim_id,
    c.payer_claim_number,
    c.claim_number,
    l.line_number,
    l.plan_payment_amount,
    l.payment_to_provider,
    l.payment_date,
    c.claim_type,
    c.form_type,
        CASE
            WHEN c.form_type = 'HCFA'::claims.form_type THEN COALESCE(NULLIF(l.place_of_service, ''::text), c.facility_type)
            ELSE NULL::text
        END AS hcfa_place_of_service,
        CASE
            WHEN c.form_type = 'UB-04'::claims.form_type THEN c.facility_type
            ELSE NULL::text
        END AS ub_bill_type,
        CASE
            WHEN c.form_type = 'HCFA'::claims.form_type THEN COALESCE(NULLIF(l.place_of_service, ''::text), c.facility_type)
            ELSE bp.place_of_service_code
        END AS combined_place_of_service_code,
        CASE
            WHEN c.form_type = 'HCFA'::claims.form_type THEN pos.title
            ELSE bp.title
        END AS combined_place_of_service_title,
        CASE
            WHEN c.form_type = 'HCFA'::claims.form_type THEN pg.place_of_service_group_name
            ELSE bg.place_of_service_group_name
        END AS combined_place_of_service_group_name,
    l.procedure_code,
    pcl.short_description AS procedure_description,
    COALESCE(cd1.descriptor, claims.get_hcpcs_category(l.procedure_code), claims.get_user_defined_line_item_procedure_code_category(l.procedure_code)) AS procedure_group1,
    cd2.descriptor AS procedure_group2,
    cd3.descriptor AS procedure_group3,
    cd4.descriptor AS procedure_group4,
    cd5.descriptor AS procedure_group5,
    l.service_date_from,
    l.service_date_through,
    l.rev_code AS revenue_code,
    l.coinsurance_amount,
    l.copay_amount,
    l.deductible_amount,
    r.title AS revenue_code_description,
    r.revenue_code_group,
    rg.title AS revenue_code_group_description,
    ps.provider_seen_id,
    rp.rendering_provider_id,
    concat_ws(' '::text, NULLIF(ps.name_prefix, ''::text), NULLIF(ps.name_first, ''::text), NULLIF(ps.name_middle, ''::text), NULLIF(ps.name_last_or_organization, ''::text), NULLIF(ps.name_suffix, ''::text)) AS provider_name,
    rp.rendering_provider_name,
        CASE
            WHEN pat.subscriber_or_patient_id IS NOT NULL THEN concat_ws(' '::text, NULLIF(pat.name_prefix, ''::text), NULLIF(pat.name_first, ''::text), NULLIF(pat.name_middle, ''::text), NULLIF(pat.name_last, ''::text), NULLIF(pat.name_suffix, ''::text))
            ELSE concat_ws(' '::text, NULLIF(sbr.name_prefix, ''::text), NULLIF(sbr.name_first, ''::text), NULLIF(sbr.name_middle, ''::text), NULLIF(sbr.name_last, ''::text), NULLIF(sbr.name_suffix, ''::text))
        END AS patient_name,
    m.member_identification_code AS patient_identification_code,
    pd.diagnosis_code AS principal_diagnosis_code,
    dl.description AS principal_diagnosis_description,
    hd.description AS hcup_diagnosis_group,
    idh1.name AS icd_diagnosis_hierarchy_level1,
    idh2.name AS icd_diagnosis_hierarchy_level2,
    idh3.name AS icd_diagnosis_hierarchy_level3,
    idh4.name AS icd_diagnosis_hierarchy_level4,
    triggers.is_diagnosis_case_management_trigger,
    triggers.is_diagnosis_stop_loss_trigger,
    triggers.is_chronic_condition_indicator,
    pp.procedure_code AS principal_icd_procedure_code,
    pl.description AS principal_icd_procedure_description,
    iph1.name AS principal_icd_procedure_group1,
    iph2.name AS principal_icd_procedure_group2,
    iph3.name AS principal_icd_procedure_group3,
    iph4.name AS principal_icd_procedure_group4,
    iph5.name AS principal_icd_procedure_group5,
    drg.drg_code,
    ml.drg_description,
    ml.mdc,
    ml.mdc_description,
    ''::text AS drug_name,
    s.external_solution_id,
    s.solution_name,
    sponsor.external_plan_sponsor_id,
    sponsor.plan_sponsor_name,
    c.tpa_claim_status,
    tps.file_name
   FROM claims.line_item l
     JOIN claims.claim c USING (claim_id)
     JOIN claims.trading_partner_submission tps USING (trading_partner_submission_id)
     JOIN claims.trading_partner tp USING (trading_partner_id)
     JOIN claims.solution s USING (solution_id)
     LEFT JOIN claims.claim_member m USING (claim_member_id)
     LEFT JOIN claims.subscriber_or_patient sbr ON m.member_subscriber_or_patient_id = sbr.subscriber_or_patient_id
     LEFT JOIN claims.subscriber_or_patient pat ON m.patient_subscriber_or_patient_id = pat.subscriber_or_patient_id
     LEFT JOIN claims.coverage_group_or_plan cgop USING (group_or_policy_number)
     LEFT JOIN claims.plan_sponsor_match psm USING (trading_partner_id, coverage_group_or_plan_id)
     LEFT JOIN claims.plan_sponsor sponsor USING (plan_sponsor_id)
     LEFT JOIN claims.line_item_procedure_code_lookup pcl USING (line_item_procedure_code_id)
     LEFT JOIN claims.cpt_hierarchy ch ON pcl.line_item_procedure_code_id = ch.line_item_procedure_code_id
     LEFT JOIN claims.cpt_concept_description cd1 ON cd1.concept_id = ch.concept_hierarchy1_id AND cd1.release_date = ch.hierarchy_release_date
     LEFT JOIN claims.cpt_concept_description cd2 ON cd2.concept_id = ch.concept_hierarchy2_id AND cd2.release_date = ch.hierarchy_release_date
     LEFT JOIN claims.cpt_concept_description cd3 ON cd3.concept_id = ch.concept_hierarchy3_id AND cd3.release_date = ch.hierarchy_release_date
     LEFT JOIN claims.cpt_concept_description cd4 ON cd4.concept_id = ch.concept_hierarchy4_id AND cd4.release_date = ch.hierarchy_release_date
     LEFT JOIN claims.cpt_concept_description cd5 ON cd5.concept_id = ch.concept_hierarchy5_id AND cd5.release_date = ch.hierarchy_release_date
     LEFT JOIN claims.claim_provider cp ON cp.claim_id = c.claim_id AND cp.provider_type = 'Billing Provider'::claims.entity_identifier_type
     LEFT JOIN rendering rp ON rp.claim_id = c.claim_id
     LEFT JOIN claims.provider_seen ps ON ps.provider_seen_id = cp.provider_seen_id
     LEFT JOIN claims.claim_diagnosis pd ON pd.claim_id = c.claim_id AND pd.diagnosis_type = 'Principal'::claims.diagnosis_code_type
     LEFT JOIN claims.icd_diagnosis_lookup dl ON dl.icd_diagnosis_id = pd.icd_diagnosis_id
     LEFT JOIN claims.icd_diagnosis_hcup_group idhg ON idhg.icd_diagnosis_id = dl.icd_diagnosis_id AND (idhg.is_inpatient_default OR idhg.is_outpatient_default)
     LEFT JOIN claims.hcup_diagnosis_group_lookup hd ON hd.hcup_diagnosis_group_id = idhg.hcup_diagnosis_group_id
     LEFT JOIN claims.icd_diagnosis_hierarchy idh ON idh.icd_diagnosis_id = pd.icd_diagnosis_id
     LEFT JOIN claims.icd_diagnosis_hierarchy1_lookup idh1 USING (icd_diagnosis_hierarchy1_id)
     LEFT JOIN claims.icd_diagnosis_hierarchy2_lookup idh2 USING (icd_diagnosis_hierarchy2_id)
     LEFT JOIN claims.icd_diagnosis_hierarchy3_lookup idh3 USING (icd_diagnosis_hierarchy3_id)
     LEFT JOIN claims.icd_diagnosis_hierarchy4_lookup idh4 USING (icd_diagnosis_hierarchy4_id)
     LEFT JOIN triggers ON triggers.claim_id = c.claim_id
     LEFT JOIN claims.claim_procedure pp ON pp.claim_id = c.claim_id AND pp.is_principal
     LEFT JOIN claims.icd_procedure_lookup pl ON pl.icd_procedure_id = pp.icd_procedure_id
     LEFT JOIN claims.icd_procedure_hierarchy iph ON iph.icd_procedure_id = pp.icd_procedure_id
     LEFT JOIN claims.icd_procedure_hierarchy1_lookup iph1 USING (icd_procedure_hierarchy1_id)
     LEFT JOIN claims.icd_procedure_hierarchy2_lookup iph2 USING (icd_procedure_hierarchy2_id)
     LEFT JOIN claims.icd_procedure_hierarchy3_lookup iph3 USING (icd_procedure_hierarchy3_id)
     LEFT JOIN claims.icd_procedure_hierarchy4_lookup iph4 USING (icd_procedure_hierarchy4_id)
     LEFT JOIN claims.icd_procedure_hierarchy5_lookup iph5 USING (icd_procedure_hierarchy5_id)
     LEFT JOIN claims.claim_drg drg ON drg.claim_id = c.claim_id
     LEFT JOIN claims.msdrg_lookup ml ON ml.drg_id = drg.drg_id
     LEFT JOIN claims.nubc_bill_type_lookup b ON b.bill_type_code = c.facility_type
     LEFT JOIN claims.nubc_bill_type_to_place_of_service_crosswalk btp ON btp.bill_type_code = b.bill_type_code
     LEFT JOIN claims.place_of_service_lookup bp ON bp.place_of_service_code = btp.place_of_service_code
     LEFT JOIN claims.place_of_service_group_lookup bg ON bg.place_of_service_code = bp.place_of_service_code
     LEFT JOIN claims.nubc_revenue_code_lookup r ON r.revenue_code = l.rev_code
     LEFT JOIN claims.nubc_revenue_code_group_lookup rg ON rg.revenue_code_group = r.revenue_code_group
     LEFT JOIN claims.place_of_service_lookup pos ON pos.place_of_service_code = COALESCE(NULLIF(l.place_of_service, ''::text), c.facility_type)
     LEFT JOIN claims.place_of_service_group_lookup pg ON pg.place_of_service_code = pos.place_of_service_code;

Column	Data Type	Indexed
claim_id	bigint	No
payer_claim_number	text	No
claim_number	text	No
line_number	text	No
plan_payment_amount	numeric	No
payment_to_provider	numeric	No
payment_date	date	No
claim_type	claim_type	No
form_type	form_type	No
hcfa_place_of_service	text	No
ub_bill_type	text	No
combined_place_of_service_code	text	No
combined_place_of_service_title	text	No
combined_place_of_service_group_name	text	No
procedure_code	text	No
procedure_description	text	No
procedure_group1	text	No
procedure_group2	text	No
procedure_group3	text	No
procedure_group4	text	No
procedure_group5	text	No
service_date_from	date	No
service_date_through	date	No
revenue_code	text	No
coinsurance_amount	numeric	No
copay_amount	numeric	No
deductible_amount	numeric	No
revenue_code_description	text	No
revenue_code_group	text	No
revenue_code_group_description	text	No
provider_seen_id	bigint	No
rendering_provider_id	bigint	No
provider_name	text	No
rendering_provider_name	text	No
patient_name	text	No
patient_identification_code	text	No
principal_diagnosis_code	text	No
principal_diagnosis_description	text	No
hcup_diagnosis_group	text	No
icd_diagnosis_hierarchy_level1	text	No
icd_diagnosis_hierarchy_level2	text	No
icd_diagnosis_hierarchy_level3	text	No
icd_diagnosis_hierarchy_level4	text	No
is_diagnosis_case_management_trigger	boolean	No
is_diagnosis_stop_loss_trigger	boolean	No
is_chronic_condition_indicator	boolean	No
principal_icd_procedure_code	text	No
principal_icd_procedure_description	text	No
principal_icd_procedure_group1	text	No
principal_icd_procedure_group2	text	No
principal_icd_procedure_group3	text	No
principal_icd_procedure_group4	text	No
principal_icd_procedure_group5	text	No
drg_code	text	No
drg_description	text	No
mdc	smallint	No
mdc_description	text	No
drug_name	text	No
external_solution_id	text	No
solution_name	text	No
external_plan_sponsor_id	text	No
plan_sponsor_name	text	No
tpa_claim_status	text	No
file_name	text	No