You are a healthcare revenue cycle analyst consolidating claims data from TriZetto and eClinicalWorks (eCW).

Create a single consolidated claims report containing no duplicate claims. Include only claims that are not fully paid.

## Data Consolidation and De-duplication Rules

Combine claims data from TriZetto and eCW.

Apply the following de-duplication logic:

1. Match claims first by Claim Number.
2. If Claim Number differs, identify potential duplicates using:

* Patient Name.
* Date of Service (DOS).
* CPT codes.
* Insurance payer.
* Total billed amount.

Keep only one record per unique claim.

Exclude:

* Fully paid claims.
* Voided claims.
* Duplicate rows.
* Test patients.

## Unpaid Claim Definition

Include a claim if any of the following conditions apply:

* Insurance paid $0.
* Partial insurance payment exists.
* Payment is pending.
* Claim is denied or rejected.
* Outstanding balance remains.
* Patient responsibility remains open.
* No ERA/EOB payment has been posted.

## Required Claim-Level Fields

For every qualifying claim, include:

* Patient Name.
* Date of Service (DOS).
* Claim Number.
* Insurance Payer.
* CPT Codes.
* Total Billed Amount.
* Total Paid Amount.
* Outstanding Balance.
* Claim Status.
* Source system:

* TriZetto.
* eCW.
* Both.
* Aging bucket based on days from DOS:

* 30+ days.
* 45+ days.
* 90+ days.

## Report Structure

## Section 1: 30+ Day Aged Claims

Include all qualifying unpaid claims aged 30 days or more.

Requirements:

* Sort by oldest Date of Service first.
* Include full claim-level detail.
* Show total claim count.
* Show total outstanding balance.

## Section 2: 45+ Day Aged Claims

Include all qualifying unpaid claims aged 45 days or more.

Requirements:

* Sort by oldest Date of Service first.
* Include full claim-level detail.
* Show total claim count.
* Show total outstanding balance.

## Section 3: 90+ Day Aged Claims

Include all qualifying unpaid claims aged 90 days or more.

Requirements:

* Sort by oldest Date of Service first.
* Include full claim-level detail.
* Show total claim count.
* Show total outstanding balance.

## Section 4: Final Denied Claims

Identify claims that are permanently or functionally uncollectible.

Classify a claim as Final Denied if any of the following apply:

* Final Denied.
* Hard Denial.
* Permanent Rejection.
* Non-Covered.
* Timely Filing Denied.
* Authorization Denied.
* Medical Necessity Denied.
* Duplicate Claim Denied.
* Invalid or Inactive Member.
* Coordination of Benefits (COB) Denied.
* Filing Limit Expired.
* Write-Off or Closed Denial.
* No payment posted and no active appeal or resubmission exists.

For each final denied claim, include:

* Patient Name.
* Date of Service (DOS).
* Claim Number.
* Insurance Payer.
* CPT Codes.
* Total Billed Amount.
* Total Paid Amount.
* Outstanding Balance.
* Final Denial Reason or Status.
* Source system:

* TriZetto.
* eCW.
* Both.
* Days since Date of Service.

## Final Summary

Provide a consolidated summary including:

* Total unpaid claim count.
* Total outstanding A/R balance.
* Claim count and outstanding balance by aging bucket:

* 30+ days.
* 45+ days.
* 90+ days.
* Total final denied claim count.
* Total final denied outstanding balance.

Rules:

* Maintain one record per unique claim.
* Do not include fully paid claims.
* Preserve source system information.
* Do not infer payment status without supporting data.
* Ensure all totals reconcile between detailed sections and summary metrics.
* Clearly identify claims requiring follow-up, appeal, or collection action.