Billed Services By Guarantor (Current Month) widget
The Billed Services by Guarantor (Current Month) widget displays the number of claims and the expected revenue amount, by guarantor, for services billed during the current calendar month.
The following fields are displayed in this widget:
- Guarantor Name
- Guarantor ID
- Number of Services
- Expected Revenue
- (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.
Note: 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 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 collection3 ON (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 = 'CountByPayorClaimedService' AND collection3.Collection_Type = 'TotalExpectedRevenueByPayorClaimedService' AND collection1.Collection_Date > CONVERT(DATE,DATEADD(day,-1*(DATEPART(day,GETDATE())+1),GETDATE()),1) GROUP BY collection1.Collection_Key ORDER BY billing_guar_table.guarantor_name