Skip to main content

Unbilled Services By Guarantor widget

  1. Last updated
  2. Save as PDF

The Unbilled Services by Guarantor widget displays, by guarantor, the number of services and the expected revenue amount for all UNBILLED services.

The following fields should be displayed in the widget:

  • Guarantor Name
  • Guarantor ID
  • Number of Unbilled Services
  • Expected Revenue

The Expected Revenue will be calculated for each service as the lesser of the following:

  • (billing.tx.charge.detail.guarantor_liability – billing.tx.charge.detail.guarantor_total_payments)
  • Maximum Amount to Distribute from Service Fee/Cross Reference definition for the applicable guarantor.

If the Maximum Amount to Distribute is blank, then use standard fee.

The items will be listed in order by Guarantor Name.

Widget SQL Statement

Due to the complexity of this widget, the SQL was added to the nightly compile. The SQL below is the query run during the nightly compile.

SELECT billing_guar_table.guarantor_name As "Guarantor Name", billing_guar_table.GUARANTOR_ID As "Guarantor ID", SUM(collection1.Collection_Value) As "Number of Unbilled Services", {fn CHAR(36)} || SUM(collection3.Collection_Value) As "Expected Revenue" FROM (WIDGET.collection_values collection1 INNER JOIN SYSTEM.billing_guar_table billing_guar_table ON (billing_guar_table.FACILITY = collection1.FACILITY AND billing_guar_table.GUARANTOR_ID = collection1.Collection_Key)) LEFT OUTER JOIN WIDGET.collection_values collection3ON (collection1.Collection_Key = collection3.Collection_Key AND collection1.Collection_Date = collection3.Collection_Date AND collection1.Collection_Freq = collection3.Collection_Freq AND collection1.FACILITY = collection3.FACILITY) WHERE collection1.Collection_Freq='M' AND collection1.Collection_Type = 'CountByPayorUnbilledService' AND collection3.Collection_Type = 'TotalExpectedRevenueByPayorUnbilledService' GROUP BY collection1.Collection_Key ORDER BY billing_guar_table.guarantor_name