ub04_everything_view
Materialized: false
Definition
WITH cob AS (
SELECT claim_1.claim_id,
claim_1.payer_claim_number,
claim_1.prior_authorization_number,
COALESCE(mem.payer_responsibility_sequence, 'Primary'::claims.payer_responsibility_sequence_type) AS payer_responsibility_sequence,
ins.name AS payer_name,
ins.payer_identification_code,
concat_ws(' '::text, NULLIF(sub.name_first, ''::text), NULLIF(sub.name_middle, ''::text), NULLIF(sub.name_last, ''::text)) AS subscriber_name,
mem.relationship_to_subscriber,
mem.member_identification_code,
mem.group_name,
mem.group_or_policy_number,
NULL::numeric AS payer_amount_paid,
NULL::numeric AS remaining_patient_liability
FROM claims.claim claim_1
JOIN claims.claim_member mem USING (claim_member_id)
LEFT JOIN claims.insurer_payer ins USING (insurer_payer_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_claim_number,
cb.prior_authorization_number,
cb.payer_responsibility_sequence,
ins.name AS payer_name,
ins.payer_identification_code,
concat_ws(' '::text, NULLIF(sub.name_first, ''::text), NULLIF(sub.name_middle, ''::text), NULLIF(sub.name_last, ''::text)) AS subscriber_name,
cb.relationship_to_subscriber,
sub.member_identification_code,
cb.group_name,
cb.group_or_policy_number,
cb.payer_amount_paid,
cb.remaining_patient_liability
FROM claims.claim_coordination_of_benefits cb
LEFT JOIN claims.insurer_payer ins USING (insurer_payer_id)
LEFT JOIN claims.coordination_of_benefits_subscriber sub USING (coordination_of_benefits_subscriber_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
), services AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('line_number', line.line_number, 'rev_code', line.rev_code, 'rev_code_description', line.procedure_description, 'hcpcs_code', line.procedure_code, 'hcpcs_code_modifier1', line.procedure_modifiers[1], 'hcpcs_code_modifier2', line.procedure_modifiers[2], 'hcpcs_code_modifier3', line.procedure_modifiers[3], 'hcpcs_code_modifier4', line.procedure_modifiers[4], 'service_date_from', line.service_date_from, 'quantity', line.quantity, 'total_charges', line.billed_amount, 'reprice_methodology', line.reprice_methodology, 'reprice_allowed_amount', line.reprice_allowed_amount, 'reprice_savings_amount', line.reprice_savings_amount, 'adjusted_repriced_line_item_number', line.adjusted_repriced_line_item_number, 'non_covered_charges', line.facility_non_covered_amount, 'claim_total_charges', claim_1.claim_billed_amount, 'reprice_allowed_amount', claim_1.reprice_allowed_amount)) AS services_json
FROM claims.claim claim_1
JOIN claims.line_item line USING (claim_id)
GROUP BY claim_1.claim_id
), conditions AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('condition_number', t.condition_code_idx, 'condition_code', t.condition_code)) AS conditions_json
FROM claims.claim claim_1
CROSS JOIN LATERAL unnest(claim_1.condition_codes) WITH ORDINALITY t(condition_code, condition_code_idx)
WHERE claim_1.condition_codes IS NOT NULL
GROUP BY claim_1.claim_id
), occurrences_single AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('occurrence_number', occur.claim_occurrence_index, 'occurrence_code', occur.occurrence_code, 'date', occur.date)) AS occurrences_single_json
FROM claims.claim claim_1
JOIN claims.claim_occurrence occur USING (claim_id)
GROUP BY claim_1.claim_id
), occurrences_span AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('occurrence_span_number', span.claim_occurrence_span_index, 'occurrence_span_code', span.occurrence_span_code, 'start_date', span.start_date, 'end_date', span.end_date)) AS occurrences_span_json
FROM claims.claim claim_1
JOIN claims.claim_occurrence_span span USING (claim_id)
GROUP BY claim_1.claim_id
), vals AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('value_number', val.claim_value_index, 'value_code', val.value_code, 'value_amount', val.value_amount)) AS values_json
FROM claims.claim claim_1
JOIN claims.claim_value val USING (claim_id)
GROUP BY claim_1.claim_id
), diagnoses AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('diagnosis_code_qualifier',
CASE diag.diagnosis_type
WHEN 'Principal'::claims.diagnosis_code_type THEN 'Principal'::text
WHEN 'Admitting'::claims.diagnosis_code_type THEN 'Admitting'::text
WHEN 'Patient Reason for Visit'::claims.diagnosis_code_type THEN 'PR-'::text || diag.claim_diagnosis_index
WHEN 'External Cause of Injury'::claims.diagnosis_code_type THEN 'ECI-'::text || diag.claim_diagnosis_index
WHEN 'Other'::claims.diagnosis_code_type THEN 'OTH-'::text || diag.claim_diagnosis_index
ELSE NULL::text
END, 'diagnosis_code', diag.diagnosis_code, 'present_on_admission', diag.present_on_admission)) AS diagnoses_json
FROM claims.claim claim_1
JOIN claims.claim_diagnosis diag USING (claim_id)
GROUP BY claim_1.claim_id
), procedures AS (
SELECT claim_1.claim_id,
json_agg(json_build_object('procedure_type_qualifier',
CASE proc.is_principal
WHEN true THEN 'Principal'::text
ELSE 'OTH-'::text || proc.claim_procedure_index
END, 'procedure_code', proc.procedure_code, 'procedure_date', proc.procedure_date)) AS procedures_json
FROM claims.claim claim_1
JOIN claims.claim_procedure proc USING (claim_id)
GROUP BY claim_1.claim_id
)
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,
0 AS admit_hour,
claim.facility_admission_type AS type_of_visit,
claim.facility_admission_source AS source_of_admission,
0 AS discharge_hour,
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,
NULL::text AS employer1_name,
NULL::text AS employer2_name,
NULL::text AS employer3_name,
drg.drg_code,
cob1.payer_name AS payer1_name,
cob2.payer_name AS payer2_name,
cob3.payer_name AS payer3_name,
cob1.payer_identification_code AS payer1_identification_code,
cob2.payer_identification_code AS payer2_identification_code,
cob3.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,
cob1.payer_amount_paid AS payer1_prior_payment_amount,
cob2.payer_amount_paid AS payer2_prior_payment_amount,
cob3.payer_amount_paid AS payer3_prior_payment_amount,
cob1.remaining_patient_liability AS payer1_amount_due,
cob2.remaining_patient_liability AS payer2_amount_due,
cob3.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_or_organization,
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_or_organization,
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,
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.payer_name AS insured1_name,
cob2.payer_name AS insured2_name,
cob3.payer_name AS unsured3_name,
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,
COALESCE(services.services_json, '[]'::json) AS services,
COALESCE(conditions.conditions_json, '[]'::json) AS conditions,
COALESCE(occurrences_single.occurrences_single_json, '[]'::json) AS occurrences_single,
COALESCE(occurrences_span.occurrences_span_json, '[]'::json) AS occurrences_span,
COALESCE(vals.values_json, '[]'::json) AS "values",
COALESCE(diagnoses.diagnoses_json, '[]'::json) AS diagnoses,
COALESCE(procedures.procedures_json, '[]'::json) AS procedures
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.claim_provider attendprov ON attendprov.claim_id = claim.claim_id AND attendprov.provider_type = 'Attending Physician'::claims.entity_identifier_type
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 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.provider_seen attendprovseen ON attendprovseen.provider_seen_id = attendprov.provider_seen_id
LEFT JOIN claims.provider_seen opprovseen ON opprovseen.provider_seen_id = opprov.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
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
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 services ON services.claim_id = claim.claim_id
LEFT JOIN conditions ON conditions.claim_id = claim.claim_id
LEFT JOIN occurrences_single ON occurrences_single.claim_id = claim.claim_id
LEFT JOIN occurrences_span ON occurrences_span.claim_id = claim.claim_id
LEFT JOIN vals ON vals.claim_id = claim.claim_id
LEFT JOIN diagnoses ON diagnoses.claim_id = claim.claim_id
LEFT JOIN procedures ON procedures.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 |
admit_hour | integer | No |
type_of_visit | text | No |
source_of_admission | text | No |
discharge_hour | integer | 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 |
employer1_name | text | No |
employer2_name | text | No |
employer3_name | text | No |
drg_code | text | 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_or_organization | 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_or_organization | 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 |
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 |
insured1_name | text | No |
insured2_name | text | No |
unsured3_name | 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 |
services | json | No |
conditions | json | No |
occurrences_single | json | No |
occurrences_span | json | No |
values | json | No |
diagnoses | json | No |
procedures | json | No |