수익 주기 관리 데이터 템플릿

WITH RelevantEncounters AS (
  SELECT 
    e.billing_event_id
  FROM ENCOUNTER e
  WHERE e.reg_dt_tm BETWEEN TO_DATE('[START_DATE]', 'YYYY-MM-DD') AND TO_DATE('[END_DATE]', 'YYYY-MM-DD')
)
SELECT
  e.billing_event_id AS "BillingEvent",
  'Patient Encounter Created' AS "ActivityName",
  e.reg_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  e.total_account_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM ENCOUNTER e
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON e.reg_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON e.reg_facility_org_id = org.organization_id
LEFT JOIN PAYER pyr ON e.primary_payer_id = pyr.payer_id
UNION ALL
SELECT
  cd.billing_event_id AS "BillingEvent",
  'Charges Captured' AS "ActivityName",
  cd.charge_entry_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  cd.charge_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM CHARGE_DETAIL cd
JOIN ENCOUNTER e ON cd.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON cd.entry_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON cd.performing_dept_org_id = org.organization_id
LEFT JOIN PAYER pyr ON e.primary_payer_id = pyr.payer_id
UNION ALL
SELECT
  ch.billing_event_id AS "BillingEvent",
  'Charges Coded' AS "ActivityName",
  ch.coded_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  e.total_account_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM CODING_HISTORY ch
JOIN ENCOUNTER e ON ch.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ch.coder_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON p.default_org_id = org.organization_id
LEFT JOIN PAYER pyr ON e.primary_payer_id = pyr.payer_id
UNION ALL
SELECT
  cl.billing_event_id AS "BillingEvent",
  'Claim Generated' AS "ActivityName",
  cl.create_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  cl.claim_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM CLAIM cl
JOIN ENCOUNTER e ON cl.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON cl.create_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON cl.billing_entity_org_id = org.organization_id
LEFT JOIN PAYER pyr ON cl.payer_id = pyr.payer_id
UNION ALL
SELECT
  csl.billing_event_id AS "BillingEvent",
  'Claim Submitted To Payer' AS "ActivityName",
  csl.submission_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  cl.claim_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM CLAIM_SUBMISSION_LOG csl
JOIN CLAIM cl ON csl.claim_id = cl.claim_id
JOIN ENCOUNTER e ON cl.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON csl.submit_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON cl.billing_entity_org_id = org.organization_id
LEFT JOIN PAYER pyr ON cl.payer_id = pyr.payer_id
WHERE csl.submission_type = 'INITIAL'
UNION ALL
SELECT
  ra.billing_event_id AS "BillingEvent",
  'Remittance Received' AS "ActivityName",
  ra.remit_received_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  e.total_account_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM REMITTANCE_ADVICE ra
JOIN ENCOUNTER e ON ra.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ra.processed_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON ra.processing_org_id = org.organization_id
LEFT JOIN PAYER pyr ON ra.payer_id = pyr.payer_id
UNION ALL
SELECT
  ft.billing_event_id AS "BillingEvent",
  'Payment Posted' AS "ActivityName",
  ft.transaction_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  ft.ending_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM FINANCIAL_TRANSACTION ft
JOIN ENCOUNTER e ON ft.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ft.post_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON ft.post_dept_org_id = org.organization_id
LEFT JOIN PAYER pyr ON ft.payer_id = pyr.payer_id
WHERE ft.transaction_type_code = 'PAYMENT'
UNION ALL
SELECT
  rd.billing_event_id AS "BillingEvent",
  'Denial Received' AS "ActivityName",
  ra.remit_received_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  e.total_account_balance AS "OutstandingBalance",
  rd.denial_reason_code AS "DenialReasonCode"
FROM REMITTANCE_DETAIL rd
JOIN REMITTANCE_ADVICE ra ON rd.remit_id = ra.remit_id
JOIN ENCOUNTER e ON ra.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ra.processed_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON ra.processing_org_id = org.organization_id
LEFT JOIN PAYER pyr ON ra.payer_id = pyr.payer_id
WHERE rd.denial_reason_code IS NOT NULL
UNION ALL
SELECT
  at.billing_event_id AS "BillingEvent",
  'Denial Appealed' AS "ActivityName",
  at.appeal_filed_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  e.total_account_balance AS "OutstandingBalance",
  at.related_denial_code AS "DenialReasonCode"
FROM APPEAL_TRACKING at
JOIN ENCOUNTER e ON at.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON at.appeal_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON p.default_org_id = org.organization_id
LEFT JOIN PAYER pyr ON at.payer_id = pyr.payer_id
UNION ALL
SELECT
  csl.billing_event_id AS "BillingEvent",
  'Corrected Claim Submitted' AS "ActivityName",
  csl.submission_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  cl.claim_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM CLAIM_SUBMISSION_LOG csl
JOIN CLAIM cl ON csl.claim_id = cl.claim_id
JOIN ENCOUNTER e ON cl.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON csl.submit_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON cl.billing_entity_org_id = org.organization_id
LEFT JOIN PAYER pyr ON cl.payer_id = pyr.payer_id
WHERE csl.submission_type = 'CORRECTED'
UNION ALL
SELECT
  psl.billing_event_id AS "BillingEvent",
  'Patient Statement Sent' AS "ActivityName",
  psl.statement_sent_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  NULL AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  psl.statement_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM PATIENT_STATEMENT_LOG psl
JOIN ENCOUNTER e ON psl.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON psl.sent_by_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON p.default_org_id = org.organization_id
UNION ALL
SELECT
  ash.billing_event_id AS "BillingEvent",
  'Collection Activity Started' AS "ActivityName",
  ash.status_change_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  NULL AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  ash.account_balance AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM ACCOUNT_STATUS_HISTORY ash
JOIN ENCOUNTER e ON ash.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ash.change_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON ash.responsible_org_id = org.organization_id
WHERE ash.new_status_code = 'COLLECTIONS'
UNION ALL
SELECT
  ft.billing_event_id AS "BillingEvent",
  'Account Adjusted' AS "ActivityName",
  ft.transaction_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  pyr.payer_name AS "PayerName",
  e.patient_class_code AS "PatientClass",
  ft.transaction_amount AS "AdjustmentAmount",
  ft.ending_balance AS "OutstandingBalance",
  ft.adjustment_reason_code AS "DenialReasonCode"
FROM FINANCIAL_TRANSACTION ft
JOIN ENCOUNTER e ON ft.encntr_id = e.encntr_id
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON ft.post_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON ft.post_dept_org_id = org.organization_id
LEFT JOIN PAYER pyr ON ft.payer_id = pyr.payer_id
WHERE ft.transaction_type_code = 'ADJUSTMENT'
UNION ALL
SELECT
  e.billing_event_id AS "BillingEvent",
  'Account Closed' AS "ActivityName",
  e.account_closed_dt_tm AS "EventTimestamp",
  p.name_full_formatted AS "UserPerformingAction",
  org.organization_name AS "BillingDepartment",
  NULL AS "PayerName",
  e.patient_class_code AS "PatientClass",
  NULL AS "AdjustmentAmount",
  0 AS "OutstandingBalance",
  NULL AS "DenialReasonCode"
FROM ENCOUNTER e
JOIN RelevantEncounters re ON e.billing_event_id = re.billing_event_id
LEFT JOIN PERSONNEL p ON e.closed_by_prsnl_id = p.person_id
LEFT JOIN ORGANIZATION org ON e.reg_facility_org_id = org.organization_id
WHERE e.total_account_balance = 0 AND e.account_closed_dt_tm IS NOT NULL;