WebCOBRA.com Community Message Boards

jwolgamott
Posts: 257
Since: 09/26/2005

If you need to send a list of currently enrolled participants to your insurance carrier, you could use the WebAPI. Here's how it could work:

1) You'd create an application and point it to our WebAPI address (https://app.webcobr a.com/exec/api.asm)
2) Query WebCOBRA by asking, "select firstname, lastname, qbid, ssn from qb where status = 'enrolled' and PremiumsPaidTo > '01/01/2007'"
3) You'll probably want to test to see if the BillingQBID == QB_ID (that way you won't report more than one QB in the same family)
3) Loop through the results, appending your EDI File
4) Send that EDI file to your carrier

cgomez
Posts: 18
Since: 02/22/2008

We used the simple query to return the information from your system. It was working fine until now. We currently have over 500 individuals in the system but the simple query is return less than 10. It was returning more than 10 records a few days ago. Why is this not working now? Is there a timing issue. These were records that were import early in the morning and we are running the query in the afternoon. If so that brings another issue, because some of the records that are showing are the ones we imported.

jwolgamott
Posts: 257
Since: 09/26/2005

Can you post the query that you're using? If you're using any WHERE clauses, it's best to replace the SSN or personally identifiable information with XXX.

We may move towards a system that updates its database nightly at midnight (or every 1-2 hours); we're experimenting with that right now. But that shouldn't be a cause of the symptoms you're seeing now.

cgomez
Posts: 18
Since: 02/22/2008

The query is in simple form:
SELECT Firstname, SSN, TotalMonthlyPremium FROM QB

There is no where clause, so I am expecting to get back all records as I did before. I check this morning and still see the same results and it has been more than 24 hours now.

jwolgamott
Posts: 257
Since: 09/26/2005

In order for us to check this out, I'll need to know your Admin Code and the username you're using during the logon process.

You can either post that here, or email it to spt@travisoft.com and include this page's link in the message body.

cgomez
Posts: 18
Since: 02/22/2008

I set the information to spt@travisoft.com.
I need a quick response.

cgomez
Posts: 18
Since: 02/22/2008

Is there any status to the information that I have sent.

jwolgamott
Posts: 257
Since: 09/26/2005

Charlie --

I looked into your data, and you only have between 5 and 8 groups in the system who aren't Employees. That would lead to the approximate 10 QBs coming across in your query.

I also see that many Groups (both regular and employees) were deleted in the last couple of days, so that could be the reason for the confusion.

If you can find an example of a QB who you can see in the system, but isn't coming through the API, we can diagnose that.

cgomez
Posts: 18
Since: 02/22/2008

There are a total of 8 people coming across the query. Three of them are not QB but DOLs. We are just starting with this company and we have loaded 35 groups into the system, which have 33 of them have been there for two weeks and the other two loaded within a couple of days. We have been testing on on one group called testing and other small group we refer to as BMA030, which was loaded to test our live information. We have loaded BMA030 and tested the query and it return all the data we have load termed or not individuals. It even returned the employees or DOLs in the query. We loaded 11 QB Groups before and return 11 QB Groups from the query. We deleted the QB Groups to do a full load after all the testing. Now we are returning a small portion, than what we loaded. We have loaded 11 QB groups and 487 DOLs, which I am expecting to return query all 498 individual as what was done in the testing. It looks like it return 3 DOLs from the testing group and only Active QB groups. One of the QBs that are not return for example is Luann Danley. I don't what to hear that it because he is termed, because he was being return before as termed. We started testing before 2/1/2008 to go live 2/1/2008, but that was delayed do to issues we werre having with your system. We move the date to 3/1/2008 with most of all Feb. on testing. As you can see we still had issue because the date has moved to 4/1/2008, which most of March having issue with the system again. I cannot move the live date a fourth time. Our Account is BMAI. Thank you.

jwolgamott
Posts: 257
Since: 09/26/2005

Ok, I think I have both your solution and an explanation.

If you run your query now, your DOL participants will show up.

It looks like you loaded your DOL people as having already been notified of their COBRA rights. This prevented the Correspondence Engine from evaluating the group and building the cache for them.

The API did not anticipate the need to load employees and terminated QBs (where the system would never generate letters for them).

The normal case is the either manual entry or integration of records where WebCOBRA controls the premiums and notification of their rights.

Is this what you need the WebAPI to do (report on employees and terminated records)?

cgomez
Posts: 18
Since: 02/22/2008

Now it is working. We just want the query to report what is in the system, regardless of the letter has been sent or if they have been notified. We are using this to synchronize the data we have in our system. We will be moving the data to our system for reporting so the data should be the same information. This something that is runned on a daily basis.

Now we have a lot more to load are we going to have the same issue going forward. We are loading in the history and current database from a previous cobra software, so this is a conversion.

jwolgamott
Posts: 257
Since: 09/26/2005

After you do your mass conversion then, post to here and we'll manually schedule the terminated QB groups and notified employees for calculation.

I'm glad we got to the bottom of this. Good luck!

cgomez
Posts: 18
Since: 02/22/2008

Is it possible to get the UserDefinedKey to be returned as a field in the benefit plan and sponsor tables. This complete the circle.

cgomez
Posts: 18
Since: 02/22/2008

Running into a couple of more issues. I am missing the Qualifying Event Date, Initial COBRA Letter Sent Date, Qualifying Event Letter Sent Date,Disability,Qualifying Event Reason, and Acceptance Date. How can I get this information from your WebAPI, since these fields are on your website.

jwolgamott
Posts: 257
Since: 09/26/2005

I've uploaded a new version of the Simple Query sample application -- I updated the web reference to reflect our new additions (Event Date, etc), and it now knows about the QB_Correspondence table.

We had only refreshed the Access Data Loader (the more popularly used sample application) in the last release.

You should be good to go now.

cgomez
Posts: 18
Since: 02/22/2008

Since we loaded the information into history there is no correspondence. I am still missing Disability and Initial COBRA sent date that I thought was recorded in the Event Table. Also on the website the not all the field name match with what is in the API which helps a lot. Here is the web page that is incorrect: http://devcenter.webcobra.com/web_a pi_field_listing.aspx

jwolgamott
Posts: 257
Since: 09/26/2005

For Initial COBRA Sent Date, if WebCOBRA did not actually send the letter, then this information is not currently available to you viathe WebAPI. It would be available in the RelationalAPI under the QBDateTracking table.

If you do need this, I recommend filling out an enhancement request form (either through support or the Self Service Portal).

The SocialSecurityEndDate is the Social Security Disability End Date -- if its not 1/1/1900, then you can tell if:
a) QB is eligible for SS extension
b) QB is on SS now (versus will be at some point)

cgomez
Posts: 18
Since: 02/22/2008

I requested the QBDateTracking table to be put in the WebAPI on 3/27/2008 thru your support team as you requested. Do you have a timeline of when this will be completed?

jwolgamott
Posts: 257
Since: 09/26/2005

No, sorry, I don't have an update for you regarding when/if the enhancement will be made available.

The Support team would be your best bet for that information.

cgomez
Posts: 18
Since: 02/22/2008

Can the OriginalCoverageDate,AssignmentStar tDate, and AssignmentEndDate columns be added to the QB_Benefit_Plan in the WebAPI process for the Simple Query. I need the max date of coverage for cobra, which I hope is the AssignmentEndDate.

jwolgamott
Posts: 257
Since: 09/26/2005

I can see how you'd be able to make use of those dates. To answer your questions:

- Yes, they could be added
- The AssignmentEndDate is the date the benefit plan was dropped from coverage for the QB

Your end of continuation for a QB would be the max of:
- DayPQBNoLongerCovered
- NoLongerOnStateCoverage
- SocialSecurityEndDate

These three dates are available in the RelationalAPI. To add them into the WebAPI, please contact support for an enhancement request.

cgomez
Posts: 18
Since: 02/22/2008

Can you add the AssignmentEndDate. The RelationalAPI won't work for us, because we are requesting the data right every hour.

jwolgamott
Posts: 257
Since: 09/26/2005

Charlie --

It would be possible to add, yes. If you haven't submitted an enhancement request for this field, I recommend you do so.