WebCOBRA.com Community Message Boards

blambrecht
Posts: 65
Since: 02/05/2008

Greetings,

we are gathering information on Payments and the portions of payments applied to invoices/coverages.

Presently I am linking the following tables:
AccountingPayment
AccountingRegisterPaymentDetails
AccountingInvoiceLineItem

Where payments are made in advance of an invoice being posted, there is no data within the AccountingRegisterPaymentDetails and AccountingInvoiceLineItem tables which makse sense.

So our report can show the payment and paid thru dates but no details for coverages. The business users are seeking to still get coverages on the report.

From my understanding of the database the only this is possible is when there is a posted invoice that the payment is applied to.

The question was raised about how does the system know how to update the Paid To and Expected Paid Through (QBGroupInformationCache table) for advance payments - was there another process that linked pre-applied payments to coverages?

I am guessing that the Paid To/Expected Paid Thru columns are calculated based upon other columns in the table, perhaps TotalMonthlyDue for example.

Can you please confirm my assumption or provide correct information - or if there is another process whereby we can link an advance payment to coverages point us in the right direction to get that information.

Thank you in advance for your assistance,
Bill Lambrecht
WageWorks, Inc

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Payments are only linked to invoices (and thereby benefit assignments) when the invoice is created on the 1st of the next month.

Any predictive modeling on future payments and how they will be applied to benefits in the future is speculation.

The system will refresh the information on the QBGroupInformationCache with every change made to the system (new payments, benefit changes etc). The expected paid thru column is calculated by using the same process as Build Payment Schedule with predicts future invoice prices (based on the information the system has at that time).

Does that answer your question? I may have given you more information, but if you need anything else, please let me know.

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

that was exactly the information I was looking for. As always, thanks for all your help!

blambrecht
Posts: 65
Since: 02/05/2008

Is there a way to link Benefit Plans with Tiers and costs without using QB data?

From my research I can only find a way to link TierID to a BenefitPlanID by going to QBBenefitAssignments or QBGroupInformationBenefitCache.

I would like to generate a list of Sponsors - Plans - Tiers - Cost - Cost + Admin Fee - independent of any QB's.

If that is possible would you mind posting the tables and/or a sample query to extract that data?

Thanks!
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

In certain circumstances, yes you could. For example, you could generate a list of Tier costs, and even Matrix costs, but not HRA or Flex or per QB costing plans.

For Tier costs, you'll want to link
Sponsor -> Benefit Plan -> BenefitRateStructures -> BenefitRateTiers

This would not include the Admin Fees; you'd need to get that parameter value for either the Federal or SS Extension period and calculate that.

The age table plans (matrix) are far more complicated; let me know if you need those.

blambrecht
Posts: 65
Since: 02/05/2008

Hi,

is it correct to assume that in table [BenefitRateStructures] when column [EndDate] has a value of 01/01/1900 it is an indication that there is no end date for that rate structure?

What is the difference between that and the ExpiresOn column in table [BenefitPlanRateExpiration]?

For a report showing all the sponors and plans that includes the start and end date of a plan, which table/columns would you recommend using?

Thank you,
Bill Lambrecht
WageWorks, Inc

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Yes, when a rate structure has an end date of 01/01/1900 that means it does not end.

The ExpiresOn column is used when a user wants to generate a letter to the Client/Employer reminding them to send the TPA their rates for the upcoming year. It's generally for record keeping only, and is not used in premium calculations.

To show the start and end date of a plan, I would do

select benefitplans.benefitplanid, benefitplans.nickname, benefitplans.description, min(benefitratestructures.begindate ), max(benefitratestructures.enddate) from benefitplans
inner join benefitratestructures on benefitratestructures.benefitplanid = benefitplans.benefitplanid
group by benefitplans.benefitplanid, benefitplans.nickname, benefitplans.description

Then you could replace the 01-01-1900 with a signifier that would mean unended to your users.

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

we are trying to locate in the WebCobra Relational API the table and field that would be the equivilant of the Desktop field [QMCSO] (medical support order) for dependents.

Could someone point us to the right direction? Thanks!

Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

You could utilize the User Defined Fields if you wanted to track the QMCSO's, but this field is not included in WebCOBRA.

blambrecht
Posts: 65
Since: 02/05/2008

Ok, if we go that direction can that user defined field be uploaded via the CSV and/or XML file import?

Thanks again,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

No, sorry, neither the CSV nor the XML allow you to import User Defined Fields.

I recommend requesting this as a future enhancement (both the Field to track the qualified support order and the ability to import it via CSV/XML)

Alternatively you could track this outside of WebCOBRA in the meantime.

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

we were wondering if when a QB is reinstated (QBDateTracking where QBDateTypeID = 16), is their status (QBGroups QBGroupStatusID) automatically reset to 4 or 10?

Can you please clarify how that process may work - thank you.

Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

In most cases, when a QB is reinstated, they'll go back to Status 4 (enrolled).

If the QB is enrolled, but has subsidies that would completely pay for their benefits, the QB will become "Enrolled with 100% subsidy" (status 10)

Also, If you're trying to put together a status history, you should take a look at our relatively new table call QBGroupStatusHistory that details out a timeline of every QB Status Change.

blambrecht
Posts: 65
Since: 02/05/2008

Can you please help us in locating where in the database the QB Department Code may be? I did some examination of the ParameterModuleData table but could not find the values I was looking for.

Thank you,
-bl

jwolgamott
Posts: 257
Since: 09/26/2005

Bill ---

QB Groups are members of departments through the table QBGroupMembers. And Departments reside in the table qbgroupassociations

So here will fetch you a list of QB groups and their department status.

Note:
* Departments are an optional association
* The Data model allows for multiple Department membership, but the application only allowed for 1 QB Group -> Department association.

select
companies.companyname, companies.companycode,
QBGroups.description, QBGroups.nickname,
qbgroupassociations.description as 'Department Name', qbgroupassociations.nickname as 'Department Code'
from qbgroups
left outer join QBGroupMembers on QBGroupMembers.QBGroupID = QBGroups.QBGroupID
left outer join qbgroupassociations on qbgroupassociations.qbgroupassociat ionid = QBGroupMembers.qbgroupassociationid
inner join companies on companies.companyid = qbgroups.companyid

blambrecht
Posts: 65
Since: 02/05/2008

This is extremely helpful, thanks again!
-bl

blambrecht
Posts: 65
Since: 02/05/2008

Can you help us identify where the Client options are stored in the database for reporting purposes?

Thank you,
Bill Lambrecht

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

can we get clarification on the columns [CurrentAssignment] and [SecondaryEvent] in table QBEventAssignments?

We are trying to put together a report and are wondering in instances where there are two records for the same event with those columns being the only difference.

(Row 1 - CurrentAssignment = True, SecondaryEvent = False)
(Row 2 - CurrentAssignment = False, SecondaryEvent = True)
In most cases the EventID for both rows are 1.

Hopefully I explained this correctly!

Thanks,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill,

This signified a Secondary Event, usually one that will give a QB 36 months of coverage instead of the usual 18 months.

The CurrentAssignment=false record will generally be the original event that brought the QB onto COBRA, and the currentassignment=true is the secondary event that extended the coverage to 36 months.

Hope that helps!

jwolgamott
Posts: 257
Since: 09/26/2005

"Can you help us identify where the Client options are stored in the database for reporting purposes?"

Bill,

The Client/Employer options are stored in the parametermoduledata table. Much like the centralized Contact Information system, the Options system is centralized.

Here's a query to get you started

select companies.companyname, parametertypes.parametername, parametermoduledata.parameter from companies
inner join parametermoduledata on parametermoduledata.moduleident = companies.companyid and parametermoduledata.moduletypeid = 1
inner join parametertypes on parametermoduledata.parametertypeid = parametertypes.parametertypeid

blambrecht
Posts: 65
Since: 02/05/2008

Hi,

business users were questioning how a QBStatusID gets set to a value of 11 (Not Specified/Other). (Migration, user entry, file upload, etc) in hopes of being able to reduce the number of QB's with that status.

Thank you,
-bl

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

More than likely you're referring to the QB Relationship type [qb.qbtypeid] which is specified in the QBTypes table. That's where the not-specified / other is set.

Through manual data entry you and your client/logins can choose Not-Specified, generally when they don't know how the QB relates to the former-employee.

This is also the default set during file integrations, but you should be able to set the dependent/spouse relationship.

Hopefully that answers your question!

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

there are times when there are no records in the AccountingInvoiceLineItem table for a corresponding invoice in AccountingInvoice table. It looks like the records missing do not have a payment posted against that invoice yet. Would that be the case or am I doing something wrong with my query?

I get the InvoiceNumber from the AccountingRegister table by QBGroupID. I then query the AccountingInvoice table where InvoiceID = InvoiceNumber. I then query AccountingInvoiceLineItem also where InvoiceID = the InvoiceNumber I got from AccountingRegister.

Thank you,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

That condition would also exist when there is nothing to bill the QB for that month. For example, the QB may have terminated before the invoice date, or dropped all benefit plans for that month.

If that's not the case, you should notify support so they can investigate why the QB's invoice is blank.

blambrecht
Posts: 65
Since: 02/05/2008

So it is correct to say that if there is no payment there will be no invoice line item records? We are looking to run a report of invoices and the line items (to show the coverage plans) but without a payment it seems that we cannot link all the data together, is that the case?

Bill Lambrecht

blambrecht
Posts: 65
Since: 02/05/2008

...and on a slightly different note: I have noticed that there are not always records in the QBGroupInformationCache table for all QBGroupID's, can you clarify when that would be the case?

Thanks again,
Bill

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

we are having an issue with a termination report we are running and was wondering if you could advise us.

We are running a query that pulls records where the [DateAdded] field in the [QBTermination] table falls within a date range.

An issue came up where a QB was on the report because a record appeared in the QBTermination table within the specified range, but their status is (correctly) Enrolled as they were reinstated.

In this event, it seems that the QBTermination table does not get updated to reflect that the QB was reinstated. I beleive I need to not just rely on the existence of the row in the table, but also check their status as of the report runtime and exclude those which are showing as active.

Does that make sense or do you have an alternitive method you would recommend for us?

Thanks - and sorry, it seems like I am "spamming" you today with questions!

Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

The QBTermination table does show each time a QB was terminated from coverage; this has both benefits and drawbacks.

Depending on the case, you may want to join to QBGroups with a status of 5 (terminated). This will exclude out currently enrolled participants who termed but later reinstated. I think this is exactly the approach you were advocating .. so I agree with you!

Also -- since a QB can terminate twice, you may want to only select the TOP 1 record (ordering by DateAdded Desc) ... this will help with the QB's who go on and off coverage.

jwolgamott
Posts: 257
Since: 09/26/2005

--------
So it is correct to say that if there is no payment there will be no invoice line item records?
--------
Yes, if a QB never enrolls (never makes a payment), then there will never be any entries in any of the Accounting tables (invoice, register, payment, etc).

--------
I have noticed that there are not always records in the QBGroupInformationCache table for all QBGroupID's, can you clarify when that would be the case?
--------
A QB who was recently added, or is Added_Not_Notified generally will not have a InformationCache entry. Also employees, and QuickCert's won't have entries there either.

Once the Qb's letters have been sent, then their enrollment period starts, and that's when they can have a cache entry.

blambrecht
Posts: 65
Since: 02/05/2008

Greetings,

Business is requesting a report on the correspondence sent for a given time period. Can I get some help on where I can find the following information please:

1) Correspondence types (they may wish to filter on types of letters sent)

2) Correspondence linked to creation and mailing dates.

3) Correspondence linked to QB (or QB Group)

Thank you in advance,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

No problem. Correspondence Types are set by the Letter Template Type, which is located in all 3 types of correspondence that can be stored (Customer, Company, and QB).

The LetterTemplateType table will list out the name of each letter than can be sent by the system, and then if you join that on CorrespondenceStorage and then QB, you can get a list of each letter sent to each QB.

CorrespondenceStorateCustomer and CorrespondenceStorageCompany store the grouped PDFs, and will prove to be pretty useless in this regard.

blambrecht
Posts: 65
Since: 02/05/2008

A couple more on a slightly different topic:

Business is requesting a report on payment information, they mention a report available on WebCobra with a field [PaymentStatus], and would like to have that in this report. Is this a stored field within WebCobra or is it derrived from other elements? I could not find it

Also, for payments, what is the best source for reporting voided payments - AccountingRegister.Void or AccountingPayment.Void? If AccountingPayment.Void, does an empty column also indicate FALSE?

Thank you!
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill ---

Can you ask Business what report they're referring to about the PaymentStatus? It's most likely calculated in that specific report, but it could also just be an alias.

For voided payments -- if its NULL (likely) or FALSE (less likely) then the payment isn't voided. I would do something like ISNULL(AccountingRegister.Void, FALSE)

Hopefully that helps --- I'm unsure about the .Void? b/c I think that'll be specific to your reporting platform or programming language.

blambrecht
Posts: 65
Since: 02/05/2008

Jesse, the report they are referring to is:
Electronic Payments Report.

Thank you,
Bill

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

The information about electronic payments are located in

select * from dbo.ElectronicTransactions
select * from dbo.ElectronicTransactionStatusType s

You can link up payments via the AccountingPaymentID, and show the status for those payments that were electronic payments (via ACH or credit card).

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

I was wondering if when a QB has is terminated and that event is recorded in the QBDateTracking table, do the corresponding records in QBBenefitAssignment.AssignmentEndDa te get updated at that time automatically?

We are trying to determine how to utilize the data for carrier files.

Thank you,
-bl

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

is marital status stored in WebCobra? If so please provide table/field names.

Thank you,
-bl

chadada
Posts: 24
Since: 08/07/2007

bill-
the QBBenefitAssignment.AssignmentEndDa te is not updated when the qb is terminated.

chadada
Posts: 24
Since: 08/07/2007

bill-
we do not store marital status. you could try to divine it by any dependents relationship to the qb (ie. spouse, domestic partner) but that could be problematic.

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

is there a way to find when a dependent's coverage terminates, for example if the dependent is no longer eligible because he/she turned 18 or their student status changes?

It looks like that would not be recorded in the QBDateTracking table, would it be recorded in a different table?

Thank you,
-bl

blambrecht
Posts: 65
Since: 02/05/2008

Greetings,

could someone help us with our question posted 10/13 regarding dependent coverage dropping.

In addition, would the AssignmentEndDate in the QBBenefitAssignments table get updated if a participant dropped one plan but was still active in others?

For example: A participant is enrolled in Medical and Dental, during the COBRA period the participant gets married and takes Medical from their spouse, but Dental is not available. So the participant drops Medical but keeps Dental. Would we be able to pick that up from the AssignmentEndDate? If not where could we find that for reporting to a carrier?

Thank you,
-bl

chadada
Posts: 24
Since: 08/07/2007

bill-
The assignmentenddate would reflect the plan drop date for the plan that was dropped. The other plans would continue and would be reflected as such in the qbbenefitassignments table.

chadada
Posts: 24
Since: 08/07/2007

bill-
you can't specifically determine if a plan was dropped because a dependent is no longer eligible. all you can tell is that the plan dropped and what day/time it occurred.

kotradov
Posts: 45
Since: 08/25/2008

Hello,
I am looking to retrieve some information for the Groups from the Relational API. First I am looking for the Groups Address information and how it relates to the Group table. I am also looking for the contacts information associated with each group.

I am also looking for the Plan information associated with each group. This info would include Plan Type, Coverage Type, Carrier, Coverage Ends Type, Status, and Rate Type. Information on where this data is on the Relational Database and how it links back to the Group records would be great.

Finally, I am looking to get the Rate information as well. This should include Effective and Term dates of the rate, Type of Rate(Standard, Individually Rated, Age Rated), Premium and age ranges. As above, information on where this data is on the Relational Database and how it links back to the Group records would be great.

kotradov
Posts: 45
Since: 08/25/2008

I have not gotten a response on this yet. Can you please assist?

chadada
Posts: 24
Since: 08/07/2007

For your first paragraph, this has already been discussed. See http://devcenter.webcobra.com/conve rsations/63.aspx You should be able to search for Contact on the page and find it easily.

For your second paragraph, you can just search this thread for 'sponsor' to see an explanation of how plans link to carriers.

For your third paragraph, just search on this thread for benefitratestructures and you will see an explanation of how rates and qbgroups fit together.

kotradov
Posts: 45
Since: 08/25/2008

Thank you for the info to easily find what I was looking for. I was wondering if Web Cobra stored a couple of items. For the Group info, does it store a Next Renewal Data.

For the Contacts, does it store or have a way to flag contacts based on their role, i.e, if they should get statements, fee invoices, if they are the elig contact or if they should be the contact for premiums.

For the Carrier/Sponser, is there address info for these. Can you also let me know if there is a place to store if the carrier requires wiating periods, child age limits, student age limits, etc.

Finally, with the Rates, can you explain how Age rated rates are stored.

Thanks,
Kirk

chadada
Posts: 24
Since: 08/07/2007

kirk-
i don't know what you mean by 'Next Renewal Date.' if you could clarify what that is, i might be able to help.

as far as the flagging of contacts, the qbgroup table has a DesignatedGroupContact that is a link to the qbautoid of the qb that is billed.

for the carrier/sponsors, they have address information just like the qbgroups do. it will just have a different moduletypeid on it, because it is a carrier/sponsor and not a qbgroup. i will refer you to http://devcenter.webcobra.com/conve rsations/63.aspx and look at the post made by jwolgamott at 03/26/2008 08:03 AM for the details. the place that stores parameters for what a carrier does is addressed there too.

for age rated tables, you want to look in the tables that start with MatrixCosting. you will find five tables that contain all the info for age rated plans. you can find the ratestructureid from the benefitplan, that you can use to get the matrixid from matrixcostingtiers, that will then give you everything else from the other tables.

kotradov
Posts: 45
Since: 08/25/2008

Hello,
By next renewal date, I was wondering if Web Cobra offered a field to store the next renewal date of the group, for rates and so forth.

As far as the flagging, is what you describe, on the QB level? I was wondering more on the group level for someone at the group who would be forwarded particular types of correspondence.

I am still looking at the other two items, but thank you for the info.

chadada
Posts: 24
Since: 08/07/2007

kirk,
The DesignatedGroupContact is on the QBGroup and singles out which qb in the group gets the correspondence, that's why it's in the QBGroup table, and points to the QB table.

for the renewal date, are you looking to see when rates will expire for a plan, or are you looking for when a rate is going to expire for a qb on a plan?

kotradov
Posts: 45
Since: 08/25/2008

This would be a field that would hold the date the group is going to renew their plan next. This is more of a admin type field on the group level. On the Plans, does any current plan have a term date?

kotradov
Posts: 45
Since: 08/25/2008

Can you let me know where the "Coverage Termination Method" is stored in the Relational API?

kotradov
Posts: 45
Since: 08/25/2008

We are also looking for the "Waiting Period Formula" in the Plan information as well.

kotradov
Posts: 45
Since: 08/25/2008

The other field I am having problems finding is under Client Employer List – Options – Coverage Termination Method.

Thanks,

jwolgamott
Posts: 257
Since: 09/26/2005

@kotradov the setup options you're looking for are called Parameters. The Coverage Termination Method field would be stored in ParameterTypes, and then as that is applied to an object (benefit plan, QB, company), that will be stored in the ParameterModuleData table.

So, to list out the parameters for a company with CompanyID 1580 you would use:

select companies.companyid, companies.companyname, parametertypes.parametername, ParameterModuleData.parameter
from ParameterModuleData
inner join companies on companies.companyid = ParameterModuleData.moduleident and ParameterModuleData.moduletypeid = 1
inner join parametertypes on parametertypes.parametertypeid = ParameterModuleData.parametertypeid
where companies.companyid = 1580

To list out for a benefit plan:
select benefitplans.nickname, benefitplans.description, benefitplans.benefitplannumber, parametertypes.parametername, ParameterModuleData.parameter
from ParameterModuleData
inner join benefitplans on benefitplans.benefitplanid = ParameterModuleData.moduleident and ParameterModuleData.moduletypeid = 2
inner join parametertypes on parametertypes.parametertypeid = ParameterModuleData.parametertypeid
where benefitplanid = 8624

If the parameter is a drop-down, such as coverage termination method, then you'll need to use the ParameterOptions table as well.

kotradov
Posts: 45
Since: 08/25/2008

Jesse,
Thanks for the info.

For the QB data in the Relational database. As I am looking through it, I am wondering if you can help me find some fields. First, where is the Department information stored?

Second, can you tell me where the Subsidy or Severance information is stored for the QB?

Third, where is the information on the HIPAA Cert being printed stored? I am basically just looking for the date it was printed.

Finally, when a QB terminates, is their coverage end date appear in QBBenefitAssignments.NoLongerOnStat eCoverage?

Thanks,
Kirk Otradovec

kotradov
Posts: 45
Since: 08/25/2008

Jesse,
After all of that, I forgot one more, if there was any Waiting Period Information. Start date, how long?

Thanks,
Kirk

jwolgamott
Posts: 257
Since: 09/26/2005

Kirk -

_Departments_
In the database these are called QBGroupAssociations and QBGroups are assigned to QBGroupAssociations through the QBGroupMembers table

_Subsidy / Severance_
Subsidy entries are made in the BenefitSubsidy table (qbgroupid and benefitplanid)

_HIPAA Cert Print Date_
Each letter that is printed is stored in the CorrespondenceStorage table, so you would like from a QB to CorrespondenceStorage with TemplateTypeID 7. Other template types are stored in the lettertemplatetype table

_QB Terminates_
The NoLongerOnState and NoLongerOnFederal dates are the QB's last possible date to be eligible for continuation coverage, not the date they are actually covered through. You'll want to take the QB's QBTermination table into account, and take the minimum of QBTermination.EffectiveDate or the QBBenefitAssignment's AssignedEndDate.

_Waiting Period Information_
This isn't stored in the database -- the application calculates it when printing the HIPAA Cert. This is calculated from looking at the benefit plan's parameter for waiting period (parametertypeid = 243). You would then have to take that formula and get the qb's hire date and add those number of days.

However, if you can just show the QB had a 90 day waiting period (rather than needing to show a specific date their waiting period expired), you could do that in SQL.

kotradov
Posts: 45
Since: 08/25/2008

We are looking for a couple of items in the Relational API database. Can you please tell me where I can find the items and how their relationships are?

Department information – located under client/Employer, “departments” tab

If rates include 2% fee – located under Client/Employer, “options” tab, go down the page to “benefit plan options” COBRA Administrative Fee

Notes – located under Qualified Beneficiary – view all, click name and the notes are under the “Notes” tab

kotradov
Posts: 45
Since: 08/25/2008

I believe I found what I was looking for above, but I have just 1 question. For the 2% fee, it looks like this can be in the parameters file for both the group/company and the benefit plan. If that is the case, does the value in the benefit plan over-ride the group value?

jwolgamott
Posts: 257
Since: 09/26/2005

@kotradov: Yes, the benefit plan is what's actually used. The company parameter gets used as a defacto default.

For notes, check the Notes table, but you'll want to link for ModuleTypeID and ModuleIdent... since you can link notes to many objects. For QBGroups, use ModuleTypeID = 11

Departments are called QBGroupAssociations, and are linked to QBGroups by QBgroupMembers. This query should show you QBs and departments:
select * from qbgroups
left outer join qbgroupmembers on qbgroupmembers.qbgroupid = qbgroups.qbgroupid
left outer join qbgroupassociations on qbgroupassociations.qbgroupassociat ionid = qbgroupmembers.qbgroupassociationid

kotradov
Posts: 45
Since: 08/25/2008

For the 2% fee, clarify if there is a specific field that identifies if the rate listed in the BenefitRateTiers.Cost field already contains the 2% or is it calculated in addition to what is in this field?

jwolgamott
Posts: 257
Since: 09/26/2005

@kotradov: BenefitRateTiers.Cost is only the premiums, not the admin fee.

blambrecht
Posts: 65
Since: 02/05/2008

Will the 65% subsidy amount as part of ARRA/Stimulus Bill be a stored field or will we need to calculate it? We have numerous report requests looking for the Premium, ARRA Subsidy 65%, and then the Participants' 35%.

Thank you,
-bl

jwolgamott
Posts: 257
Since: 09/26/2005

@blambrecht: The actual subsidies created will be available in the AccountingPayments table, and the AccountingRegister table.

If you need a simple entry in the QBGroupInforamtionCache table that lists out the QB's current subsidy amount, I recommend you add that to the IdeaBook (http://ideabook.travisoft.com)

blambrecht
Posts: 65
Since: 02/05/2008

Hi,

is there a way to identify that a participant is "ARRA Enrolled"? The QBGroupStatus table does not have this designation, nor could I identify anything with QBDateTrackingTypes.

I did a simple query on the ParamaterTypes table to see if there were any {like '%ARRA%'} with no results.

Thank you,
-bl

jwolgamott
Posts: 257
Since: 09/26/2005

@blambrecht

There are several new QB Date Tracking dates that deal with ARRA, I pasted them below.

You'll want to find QB Groups that have a Date Type of 42 (ARRA Authorized).

-- You could also restrict by the Opt-out date is a QB is on ARRA for a period of time but then opts out at a later date due to the income restrictions.

QB DateTypeID Name Description
*42 QB Stimulus Authorized QB has been authorized to receive Premium Assistance per the ARRA 2009 Stimulus.
*43 QB Stimulus Opt Out QB has opted out of receiving the ARRA Stimulus Subsidy from this date forward.
*44 QB Stimulus Notified Date QB was sent COBRA Eligibility Notice for ARRA 2009 Stimulus.
*45 ARRA Re-Notification QB has been re-notified for COBRA coverage and will get a chance to sign up even if they have terminated already and get the ARRA Stimulus Premium Reduction Subsidy.
*47 ARRA Denial Date Date that ARRA Stimulus for a QB was denied

blambrecht
Posts: 65
Since: 02/05/2008

Can you please assist in finding where in the database we can get the Sponsor name - not the code and description as in the Sponsors table, but the full name like "Blue Cross Blue Shield of TN".

Thank you,
-bl

blambrecht
Posts: 65
Since: 02/05/2008

Can you please help us in identifying the table and column that stores the ARRA Opt Out Reason, if it is available.

Thank you,
-bl

jwolgamott
Posts: 257
Since: 09/26/2005

@Blambrecht --- Sponsors.Description in the table becomes the Sponsor Name in the system

For the ARRA opt-out reason -- This would transfer over on the letter itself, but we're not storing the reason. Not a bad idea though -- I recommend adding it to our Ideabook http://ideabook.travisoft.com

blambrecht
Posts: 65
Since: 02/05/2008

Thanks Jesse,
but I do not see a Description field in the Sponsors table.
There is a Name and Nickname, but they do not contain the full name we were looking for.

Thank you,
-bl

blambrecht
Posts: 65
Since: 02/05/2008

I noticed that the column DoNotTerm has been added to the QBGroupInformationCache table. Does this reflect the same data element as what is in the ParameterModuleData table, or is the ParameterModuleData version being dropped going forward? (For ours it is ParameterTypeID = 176 and ModuleTypeID = 3)

Thanks,
Bill Lambrecht

travis
Posts: 44
Since: 04/22/2009

@blambrecht -- It does reflect the same data element in the parametermoduledata table. The QBGroupInformationCache just added it for an easier way to get it (likely for a report).

So you can use either one, the parameter storage isn't going away.

blambrecht
Posts: 65
Since: 02/05/2008

I have some questions regarding the Actions table:

1) Does this table log actions that are only done within the UI, or does it reflect activity from file loads as well?

2) Would it be possible to get a list of the ActionTypeID and ActionRunLocationID values?

Thank you,
-bl

travis
Posts: 44
Since: 04/22/2009

@blambrecht --

1) The actions table is a set of actions the system needs to take to generate letters.

2) sure:
-- select * from actiontypes
-- select * from ActionRunLocations

I'm pretty sure it'll be useless to you for a report on users activity.

blambrecht
Posts: 65
Since: 02/05/2008

We are looking to report on changes to participant information, for example, the table shows for a participant there was a change in their city and zip code. We were wondering if there was a correlation between when this table gets updated and when the Addresses table ChangedOn is updated.

We have a report that grabs address changes using the ChangedOn date in the Addresses table. Users are looking at that report and comparing it to the UI History tab and asking why someone on this report is not showing the address change on their history.

I do not see the ActionTypes and ActionRunLocations tables in the API Refresh Access database.

Thank you,
-bl

blambrecht
Posts: 65
Since: 02/05/2008

One minor correction - it seems I had the wrong table, the QBHistoryDetails as opposed to Actions. Same question though, if we wanted to do a custom address change report, is the ChangedOn field in the Addresses table the criteria to use or should we utilize the QBHistoryDetails?

And does this QBHistoryDetails table include any changes (demographic, participant status, benefit coverages) submitted by file and via the web app, or just the web app? Sorry if that seems like a dumb question, my exposure to the web app is very limited so I am not aware of all the activities that can be done via the app and/or file loads. I just need to make sure we are not missing anything for the reports.

Thanks again,
-bl

travis
Posts: 44
Since: 04/22/2009

@blambrecht Yes, it'll include any changes through whatever mechanism -- it's driven by triggers in the table, so any change will cause it to appear.

You're on the right track with the QBHistoryDetail table.

blambrecht
Posts: 65
Since: 02/05/2008

One follow-up question, what causes the ChangedOn date to be updated in QB and related (QBGroups, Addresses, as an example)?

Is there a direct correlation between that field and the QBHistoryDetail table?

Thank you!

travis
Posts: 44
Since: 04/22/2009

@blambrecht the ChangedOn table will likely (not assured in all cases) be updated when the table itself is updated. So if a user changed the QB.SSN field, the QB.ChangedOn field would then be updated.

Changes to the QBBenefitAssignment table (a child of QB) would not affect the QB.ChangedOn field.

blambrecht
Posts: 65
Since: 02/05/2008

Using table QB as an example, if the SSN is updated, will the trigger writing to QBHistoryDetail log each field as having changed, showing the old value = new value for columns not updated? Or will it only write the SSN field?

For example, if participant Jethro Tull has his SSN updated (for whatever reason), will the QBHistoryDetail table contain rows as follows:

SSN Old Value = 000-00-1111 New Value = 000-01-1111
LastName Old Value = Tull New Value = Tull
FirstName Old Value = Jethro New Value = Jethro

Or will it only have one row for the SSN? It looks like it writes one row per field in the table affected from the sampling I have done.

The reason for all the questions on the QB History table is we have been asked to produce a series of reports indicating specific changes, such as Address or Last Name. We are looking at what our best options are for identifying changed rows. I beleive in most cases using the ChangedOn field is the correct approach. Of the QBHistoryDetail table, about 85% of the rows contain Original and New values that are either blank or identical, so I am not sure if that is the best table to use to identify data that changed within the reporting period.

Any input from your team would be most appreciated.

Regards,
Bill Lambrecht

travis
Posts: 44
Since: 04/22/2009

@blambrecht: Yes, a change to QB.SSN will write out a trigger to the QBHistoryDetail table, and will also update the QB.ChangedOn table.

One recommendation we have is to simply exclude from the QBHistoryDetail entries that are null and/or equal to each other. I believe we do that when building a QB's history page.

blambrecht
Posts: 65
Since: 02/05/2008

I am trying to write a report that includes the monthly contribution amount to an FSA plan - can I use the field:
QBBenefitAssignments.FlatFee

Or is there another table/column I should be using?

Thank you.

travis
Posts: 44
Since: 04/22/2009

@blambrecht - that is the correct field to use. FSA's have a BenefitPlanCostingTypeID of 3.

That field is also used on per-QB plan costing and is used in the calculation of life insurance / STD / LTD plans.

blambrecht
Posts: 65
Since: 02/05/2008

Hello,
where in the database could I find ARRA Exclusions as viewed on the QB Benefit Subsidy Exclusion Management page?

We are trying to create a report with that information, at a minimum we would like to be able to get the QBGroupID and related BenefitPlanID and amounts.

Thank you,
Bill Lambrecht

sszelei
Posts: 53
Since: 09/02/2009

Bill,

What you are looking for should be in the BenefitExclusion table. This table holds the QBGroupID, the BenefitPlanID and the ExclusionAmount

Regs,

Steven