DevCenter for WebCOBRA Message Board Feed

Post by: travis | RE: ARRA Tables in Database Not Correct

Wednesday, 15 Jul 2009 08:39 CST

No there's no limit. There does not seem anything wrong with your query, so I'll explain some of the payment application process.

If I have $100 in invoices for March and April, and I pay two payments of $60 each, there will likely be a total of three payment applications:
* $60 March application for 1st payment
* $40 March application for 2nd payment
* $20 April application for 2nd payment

It might look weird when looking at the data only; I recommend looking at the Ledger through the system to get an idea of what is payment for what.

For us to help any further will require a professional services consulting engagement. If you decide you want to proceed down that level, and have a consultant go over your specific data and help dive into it, please ask Support to put you in contact with Professional Services.

Post by: dandrews | RE: ARRA Tables in Database Not Correct

Wednesday, 15 Jul 2009 01:52 CST

I have another view (vwQBPaymentApplication) that it will not let me post up. Is there a limit to how many posts I can have in a time period?

Post by: dandrews | RE: ARRA Tables in Database Not Correct

Wednesday, 15 Jul 2009 01:50 CST

I will have to post it in pieces then.

vwQBPayment
===========================
SELECT
ar.[PaymentHistoryID],
s.[NickName] AS SponsorCode,
bp.[Nickname] AS PlanCode,
bp.[Description] AS BenefitPlan,
ai.[DueDate],
ailit.[Name] AS LineItemType,
arpd.[LineItemAmount],
arpd.[AmountAppliedToLineItemFromP ayment]

FROM

[AccountingRegister] ar
INNER JOIN [AccountingRegisterPaymentDetails] arpd ON ar.[AccountingRegisterID] = arpd.[AccountingRegisterID]
INNER JOIN [AccountingInvoiceLineItem] aili ON arpd.[AccountingInvoicelineItemID] = aili.[AccountingInvoiceLineItemID]
INNER JOIN [AccountingInvoiceLineItemTypes] ailit ON aili.[AccountingInvoiceLineItemType ID] = ailit.[AccountingInvoiceLineItemTypeID]
INNER JOIN [AccountingInvoice] ai ON aili.[InvoiceID] = ai.[InvoiceID]
INNER JOIN [BenefitPlans] bp ON aili.[BenefitPlanID] = bp.[BenefitPlanID]
INNER JOIN [Sponsors] s ON bp.[SponsorID] = s.[SponsorID]

WHERE

ar.[Void] <> 'True' AND
aili.[Void] <> 'True' AND
ai.[Void] <> 'True'

Post by: travis | RE: Premium Detail Report

Tuesday, 14 Jul 2009 08:21 CST

I think this will work for you. I added several tables; the AccountingRegister table is now not needed, but I left in so you could see the difference.

The reason its extra now is that you seem to be looking to show the amount _billed_ to a QB for a month. It gets more complicated when you need to show amount paid, or amount applied in a month.

New tables and their meanings:
* AccountingXRF - links the QBGroupID to Accounting via the ModuleIdent field
* AccountingInvoice - The idea of an invoice for a month to a QB
* AccountingInvoiceLineItem - Individual rows in the invoice. Premium for Medical would be 1, Admin Fee for Medical is 2, etc
* AccountingInvoiceLineItemTypes - either Admin Fee, Premium, Subsidy, refund, adjustment. Possibly more.

Let me know if this works out for you.

SELECT Companies.CompanyName, QB.LastName, QB.FirstName,
QB.SSN, AccountingRegister.EnteredOn,
BillingTimePeriods.FirstFullDay, BillingTimePeriods.LastFullDay,
BillingTimePeriods.FirstFullDay,
AccountingInvoiceLineItem.LineItemA mount,
BenefitPlans.Description,
AccountingInvoiceLineItemTypes.Name ,
[AccountingRegister].Amount* 0.02 AS Expr1,
[AccountingRegister].[Amount] AS Expr2,
QBGroupAssociations.Description, AccountingRegister.Type
FROM (QB INNER JOIN ((((BillingTimePeriods INNER JOIN AccountingRegister ON BillingTimePeriods.BillingTimePeriodID = AccountingRegister.BillingTimePeriodID) INNER JOIN QBGroups ON AccountingRegister.QBGroupID = QBGroups.QBGroupID) LEFT JOIN QBGroupMembers ON QBGroups.QBGroupID = QBGroupMembers.QBGroupMemberID) LEFT JOIN QBGroupAssociations ON QBGroupMembers.QBGroupAssociationID = QBGroupAssociations.QBGroupAssociationID) ON QB.QBAutoID = QBGroups.DesignatedGroupContact) INNER JOIN Companies ON QBGroups.CompanyID = Companies.CompanyID

inner join AccountingXRF on AccountingXRF.ModuleIdent = QBGroups.QBGroupID
inner join AccountingInvoice on AccountingXRF.AccountingXRFID = AccountingInvoice.AccountingXRFID and AccountingInvoice.BillingTimePeriodID = BillingTimePeriods.BillingTimePeriodID
inner join AccountingInvoiceLineItem on AccountingInvoice.InvoiceID = AccountingInvoiceLineItem.InvoiceID
inner join AccountingInvoiceLineItemTypes on AccountingInvoiceLineItem.AccountingInvoiceLineItemTypeID = AccountingInvoiceLineItemTypes.AccountingInvoiceLineItemTypeID
inner join BenefitPlans on BenefitPlans.BenefitPlanID = AccountingInvoiceLineItem.BenefitPlanID

WHERE (((Companies.CompanyName)='Carter Companies') AND ((BillingTimePeriods.FirstFullDay)>='5/1/2009' And (BillingTimePeriods.FirstFullDay)<'6/1/2009') AND ((AccountingRegister.Type)=1))
ORDER BY Companies.CompanyName, QB.LastName, QB.FirstName, BillingTimePeriods.FirstFullDay DESC;

Post by: dhazzard | RE: Premium Detail Report

Tuesday, 14 Jul 2009 01:32 CST

From previous posts I was able to generate a query that has the total premium for a given month for each employee (below). I think now I just need to break each employees benefit out separately. Any suggestions?

SELECT Companies.CompanyName, QB.LastName, QB.FirstName, QB.SSN, AccountingRegister.EnteredOn, BillingTimePeriods.FirstFullDay, BillingTimePeriods.LastFullDay, BillingTimePeriods.FirstFullDay, Format([AccountingRegister.Amount]* 0.02,"Standard") AS Expr1, Format([AccountingRegister].[Amount ],"Standard") AS Expr2, QBGroupAssociations.Description, AccountingRegister.Type
FROM (QB INNER JOIN ((((BillingTimePeriods INNER JOIN AccountingRegister ON BillingTimePeriods.BillingTimePeriodID = AccountingRegister.BillingTimePeriodID) INNER JOIN QBGroups ON AccountingRegister.QBGroupID = QBGroups.QBGroupID) LEFT JOIN QBGroupMembers ON QBGroups.QBGroupID = QBGroupMembers.QBGroupMemberID) LEFT JOIN QBGroupAssociations ON QBGroupMembers.QBGroupAssociationID = QBGroupAssociations.QBGroupAssociationID) ON QB.QBAutoID = QBGroups.DesignatedGroupContact) INNER JOIN Companies ON QBGroups.CompanyID = Companies.CompanyID
WHERE (((Companies.CompanyName)="XYZ Company") AND ((BillingTimePeriods.FirstFullDay)>=#5/1/2009# And (BillingTimePeriods.FirstFullDay)<#6/1/2009#) AND ((AccountingRegister.Type)=1))
ORDER BY Companies.CompanyName, QB.LastName, QB.FirstName, BillingTimePeriods.FirstFullDay DESC;

Post by: travis | RE: Premium Detail Report

Monday, 13 Jul 2009 08:45 CST

Sounds good and do-able. How far have you gotten?

Post by: dhazzard | Premium Detail Report

Monday, 13 Jul 2009 03:51 CST

We are trying to develop a query to export to excel that would be ran each month with data similar to the premium receipt report. It needs to include the following data:

-Company Name
-Department Name
-Employee First Name
-Employee Last Name
-Employee SSN
-Premium Paid Date
-Premium Start Date
-Premium End Date
-Admin Fee
-Premium Amount
-Description (Plan Name)

We would like for each benefit/premium per person on it's own line, similar to the expanded plan detail entry on the employee's ledger.

Post by: travis | RE: ARRA Tables in Database Not Correct

Thursday, 09 Jul 2009 09:27 CST

Sorry, no; please post to here or we can get you in touch with our coordinator for professional services.

Post by: dandrews | RE: ARRA Tables in Database Not Correct

Thursday, 09 Jul 2009 04:49 CST

I am trying to post up the sql that makes up the views we use but the message board program is giving me an error.

Can I email the views to you?

Post by: travis | RE: ARRA Tables in Database Not Correct

Thursday, 09 Jul 2009 01:06 CST

It looks like you also have a view created, vwQBPaymentApplication

if you post the query for the view as well we might be able to see something.

Post by: dandrews | RE: ARRA Tables in Database Not Correct

Thursday, 09 Jul 2009 09:14 CST

This is a stored procedure. We pass in MinProcessDate, MaxProcessDate, a comma-separated list of company IDs to process, and a command-separated list of payment methods to include.

What is happening is that the payment applications are showing an application of a fee of 8.17 that we do not understand. That is the data coming from the Travis db.
Only a single company has the issue, and only in the date range 6/1/2009 - 6/30/2009. All others are fine.

SELECT
p.[CompanyID],
p.[CompanyCode],
p.[CompanyName],
p.[QBGroupID],
p.[QBGroupStatusID],
p.[GroupDescription],
p.[QBAutoID],
p.[QBTypeID],
p.[QBFullName],
p.[SSN],
p.[BranchCode],
p.[BranchName],
p.[DateEntered],
p.[CheckDate],
p.[TransactionIdent],
p.[AmountPayed],
p.[PaymentMethod],
pa.[SponsorCode],
pa.[PlanCode],
pa.[BenefitPlan],
pa.[DueDate],
pa.[LineItemType],
pa.[LineItemAmount],
pa.[AmountAppliedToLineItemFromPay ment]
FROM

[vwQBPayment] p
LEFT OUTER JOIN [vwQBPaymentApplication] pa ON p.[PaymentHistoryID] = pa.[PaymentHistoryID]

WHERE

p.[DateEntered] BETWEEN @MinProcessDate AND @MaxProcessDate AND
p.[PaymentMethodID] IN (SELECT TableId FROM dbo.fnIntegerTable(@PaymentTypeList , ',')) AND
p.[CompanyID] IN (SELECT TableId FROM dbo.fnIntegerTable(@CompanyList, ','))

ORDER BY

p.[CompanyName],
p.[BranchName],
p.[QBFullName],
p.[SSN],
pa.[SponsorCode],
pa.[PlanCode]

Post by: travis | RE: ARRA Tables in Database Not Correct

Thursday, 09 Jul 2009 08:02 CST

@dandrews -- why don't you try posting in the query you're using to pull the data and we'll see what we can do.

Note: We only deal in the aggregate here on the DevCenter, so we'll only look at things that help the population as a whole. You may be directed to Professional Services for help in constructing your report if the Query looks ok.

Post by: dandrews | ARRA Tables in Database Not Correct

Wednesday, 08 Jul 2009 05:35 CST

I need some urgent technical support on a specific client of ours. We have custom reports that pull payment information from the database for our monthly billing. Two individuals within this client have ARRA, but the amounts are not pulling correctly. I've already spoken with our programmers, and the information is pulled directly out of the database. The records in Web Cobra are fine, and the ledger is fine. The numbers that are being pulled don't make any sense - on both ARRA participants, the amount at 102% is showing for dental and vision rather than the 35%, and the medical amount is a seemingly random number. It's only for this one client - the report for the rest of our clients was error-free. I'd rather not post the client or member information on this message board, but Travis Support said they cannot access this information, and that I would need to submit a case through the Dev Center. Someone please email me directly at ntodd@beyondb.com. Thanks.

Post by: mgoetz | RE: Paid-Thru date

Wednesday, 01 Jul 2009 07:05 CST

Thank you, I really appreciate the help.

Marty

Post by: travis | RE: Paid-Thru date

Tuesday, 30 Jun 2009 03:50 CST

Also of note: qbgroupinformationcache.ExpectedPre miumsPaidThrough is the date that the system expects any balance to pay them through.

So while the PremiumsPaidTo field is an absolute 'they have paid through the current month', the ExpectedPremiumsPaidThrough shows 'given the current rates, this QB will likely pay through 2 months from now.

Post by: travis | RE: Paid-Thru date

Tuesday, 30 Jun 2009 03:48 CST

qbgroupinformationcache.PremiumsPaid To

Although thats the "TO" date, so the THRU would be the midnight before.

Post by: travis | RE: Billing Contact Field

Tuesday, 30 Jun 2009 03:47 CST

The Billing Contact is the QB.QBAutoID (and the qbgroups.designatedgroupcontact), so if you want a full list of QBs you could

select qb.* from qb
inner join qbgroups on qbgroups.designatedgroupcontact = qb.qbautoid

Post by: mgoetz | Paid-Thru date

Tuesday, 30 Jun 2009 01:38 CST

Where can I find the Paid-Thru date.

Thank You
Marty

Post by: blanier_webcobradev | Billing Contact Field

Tuesday, 30 Jun 2009 01:25 CST

On which table will I find the Billing Contact field? I am working on a report for accountiny, and I only want to include the PQB (no dependents).

Thank you for your assistance,
Brooke Lanier

Post by: travis | RE: Information on Partial Payments

Monday, 29 Jun 2009 09:26 CST

Try this query out; it should show you anyone who's overdue and the premium they need to pay to become current.

select distinct
qbgroups.description, qbgroups.nickname,qbgroupinformatio ncache.AccountBalance, qbgroupinformationcache.PremiumDueD ate
from dbo.AccountingRegister
inner join accountingXRF on accountingXRF.accountingXRFid = AccountingRegister.accountingXRFid
inner join QBGroups on QBGroups.qbgroupid = accountingXRF.moduleident
inner join qbgroupinformationcache on qbgroupinformationcache.qbgroupid = qbgroups.qbgroupid
where
Type = 1
and
PercentofBillPaid < 100 -- A premium is open
and
qbgroupinformationcache.AccountBalance > 0 -- they have a balance that needs to be paid
and
qbgroupinformationcache.dayspastdue > 0 -- they are overdue
and
QBGroups.qbgroupstatusid != 5 -- they are not terminated

Post by: kotradov | Information on Partial Payments

Monday, 29 Jun 2009 07:34 CST

Hello, I am looking to see if we can get information on partial payments. For any member with balances due, what we need to get is the amount showing partially paid and the month of the partial payment. Your assistance is greatly appreciated.

Post by: travis | RE: Code Error on Web Cobra web site

Friday, 19 Jun 2009 03:38 CST

for the XML, replace the brackets with [ and ]

Post by: travis | RE: Code Error on Web Cobra web site

Friday, 19 Jun 2009 03:37 CST

If you'll paste in the XML you're using (only this QBs), we might be able to figure out what you're doing wrong.

Your alternatives are the DevCenter and our professional services department (paid consulting)

Post by: dwgross | RE: Code Error on Web Cobra web site

Friday, 19 Jun 2009 11:52 CST

This an XML file.
The 'BenefitPlanUserDefinedKey' I am sending is F-489MS and F-489R for this person and I checked and both keys exist and are linked to a plan on the web site.

Post by: travis | RE: Code Error on Web Cobra web site

Friday, 19 Jun 2009 11:33 CST

Are you uploading XML or CSV?

The error is saying that you have not correctly mapped the benefit plan in the file to a plan in the website.

Post by: dwgross | RE: ADD QB XML Problem ERROR

Friday, 19 Jun 2009 10:11 CST

Then how do I get this resolved. When I called the 281-496-3737 phone number they said they don't support XML and this was the only way to get an answer?

Post by: dwgross | Code Error on Web Cobra web site

Friday, 19 Jun 2009 09:35 CST

Am unable to load file because of error on web site.

the message I get is:
193 - **ERROR** in QB Benefit Assignment: Object reference not set to an instance of an object. --> BP UDK: F-489MS QBName: SCHAFFER,GAVIN Group Name: GAVIN SCHAFFER Company: F-489

This has no meaning to me. I looks as if some variable on the web site is not working correctly "Object reference not set to an instance of an object"

Post by: travis | RE: Need Help Writing Report!

Friday, 12 Jun 2009 02:01 CST

@slarson -- Here's where all that data is located.

QB Name - QB.FirstName + QB.LastName
SSN - QB.SSN
Event Description - QBEvents.Description (through QBEventAssignments)
Event Date - QBEventAssignments.QualifyingEventD ate
End Date - Max(qbbenefitassignments.DayPQBNoLo ngerCovered)

Plan - Delta Dental,etc. - Link to BenefitPlans through QBBenefitAssignment
Coverage month 5/1/09-5/31/09
Payment date - AccountingPayment.CheckDate
payment amount - AccountingPayment.AmountPayed
Paid through date - QBGroupInformationCache.PremiumsPaidTo

For the coverage month -- if you want to see payments as they apply to AccouningLineItems, there's the AccountingRegister table.

Post by: slarson | Need Help Writing Report!

Friday, 12 Jun 2009 08:55 CST

Hello,

We are new to the webbased version of the program and we need to generate a report out of access - the fields we need to show are as follows:

QB Name
SSN
Event Description
Event Date
End Date

Plan - Delta Dental,etc.
Coverage month 5/1/09-5/31/09
Payment date
payment amount
Paid through date

Could someone tell me which tables to use and how they should be linked and also the field names?

thank you,
Sue

Post by: travis | RE: Undescribed Error with a Relational API Request.

Friday, 12 Jun 2009 08:02 CST

It looks like the request was made around 5PM Central time, and the RelationAPI was busy with other requests through 8PM, which was past the time it was allowed to pick up a new job.

We've updated the schedule to both start earlier and stay open later.

However, that explains why you didn't get the API download until this morning.

Post by: kotradov | RE: Undescribed Error with a Relational API Request.

Friday, 12 Jun 2009 03:21 CST

Last night I made another request and since then it has been in a status of Queued and Waiting. This has been this way for over 10 hours now. This is twice as long as I have ever had to wait before. Can you determine if there is a issue with the Relational API request?

Thanks,
Kirk Otradovec

Post by: travis | RE: Undescribed Error with a Relational API Request.

Tuesday, 02 Jun 2009 03:02 CST

The best answer we can give right now is that our storage mechanism for these files (Amazon's S3 service) is returning a 403 Forbidden error.

This could be a hiccup on their end, or the randomized string that appends to the filename could have an invalid character.

Future versions will auto-retry, but for now you'll need to watch and resubmit if this seemingly random occurrence takes place.

Post by: kotradov | RE: Undescribed Error with a Relational API Request.

Tuesday, 02 Jun 2009 10:12 CST

It did work after I resubmitted. Do you have any explaination why it would have failed? I will be working on several downloads in the next couple of weeks and would like to make sure we don't encounter the issue again.

Post by: kotradov | RE: ARRA Eligible

Tuesday, 02 Jun 2009 08:12 CST

Please ignore, I think I found it in another post.

Post by: kotradov | ARRA Eligible

Tuesday, 02 Jun 2009 08:09 CST

On WebCobra, this is a Y/N indicator. Can you let me know where this is stored in the Relational API?

Thanks,
Kirk

Post by: travis | RE: Undescribed Error with a Relational API Request.

Saturday, 30 May 2009 09:20 CST

@kotradov looks like you resubmitted and were able to download

Post by: kotradov | Undescribed Error with a Relational API Request.

Friday, 29 May 2009 07:27 CST

I am trying to get a RElational API request for BCBSGA and am getting a Undescribed Error as a response. Can you please advise and correct so we can get a download?

Thanks,
Kirk Otradovec

Post by: travis | RE: Finding when a benefit is dropped

Friday, 29 May 2009 01:07 CST

@dandrews: Nothing concrete unfortunately... you could join through the QBBenefitAssignments table based on the QBAutoID and the drop date, but if a QB has two plans with the same MED code and same drop date (possible if different sponsors involved), it could not differentiate the two.

If you can ensure plan codes are unique in your environment, that's one way to go. You may need us to build you a new tracking mechanism through custom programming though.

See if you can make anything happen with #1 and if not -- well I think the system tracking as is has taken you as far as it can.

Post by: dandrews | RE: Finding when a benefit is dropped

Friday, 29 May 2009 11:52 CST

Thank you very much for your help, I'm nearly where I need to be with this.

It seems the OriginalValue field only shows the Plan Code (nickname) of the plan that was dropped. I am going back to QBBenefitAssignment and BenefitPlan to grab the assignment that was changed, but it's only joining on the nickname.

Is there any other data source that would tell me the sponsor nickname as well, so I can join in by both sponsor and plan?

Post by: travis | RE: Finding when a benefit is dropped

Thursday, 28 May 2009 07:28 CST

@dandrews -- No, a benefit plan delete is generally when a QB was originally offered the capability to enroll in a plan but chose not to.

Post by: dandrews | RE: Finding when a benefit is dropped

Thursday, 28 May 2009 01:54 CST

Looks like there are also "Benefit Plan Delete" records in that table. What is the difference between the two? I don't work much with the interface of Webcobra, just the data. Could that also mean the plan was dropped?

Post by: travis | RE: Finding when a benefit is dropped

Thursday, 28 May 2009 12:55 CST

@dandrews -- absent from a custom programming request to get this information into a nice tracking table for you, I think the QBHistoryDetail table works best

For example, the following query will return results for all benefit plan drops occuring for 1 QB

select * from qbhistorydetail where qbautoid = 100640 and fieldname like '%Benefit Plan Drop%'

FieldName: Benefit Plan Drop on 1/15/2009
OriginalValue: MED
DateStamp: 2009-01-27 13:44:19.000

That'll be able to show you the effective date (in the field name), the plan code (original value), and the date of the drop (datestamp)

Post by: dandrews | RE: Finding when a benefit is dropped

Thursday, 28 May 2009 09:24 CST

Yes, we have to send out reports of all changes made in a given time period.

If a QB has 3 plans, and one of them drops, this does not seem to be reflected in QBTermination because the group is still active. (That makes sense.) We probably should not use QBTermination for this change report.

This is the problem we are running into. We have a case where a change was made:

The change was made on 5/15 but the effective date of termination of the benefit assignment was 4/30. For the report we have to turn in, which should show all changes made 5/12-5/19, that change on 5/15 will not show because the end date of the plan is 4/30. We are using plan end dates to determine when drops are made.

What I need to know is how I can determine the date the change was made as well as the effective date of the change.

Post by: travis | RE: Finding when a benefit is dropped

Wednesday, 27 May 2009 04:56 CST

@dandrews -- so you're looking for when a plan is dropped, not necessarily the effective date of the drop, right?

Post by: dandrews | Finding when a benefit is dropped

Wednesday, 27 May 2009 01:57 CST

Is there an efficient way to find when a benefit is dropped? We are currently using QBTermination to see when a QB is terminated, but if the QB drops 1 out of 3 coverages, then the QB itself has not terminated but rather that single coverage.

One option I am looking at is the QBHistoryDetail table, looking for a particular QBAutoID or QBGroupID, with an Action of 6, but the PK for those items is always 0. That means I'm not able to identify which plan they dropped.

For instance, here is one record:
Action = 6
PKField = BenefitAssignmentID
PK = 0
FieldName = Benefit Plan Drop on 4/30/2009
OriginalValue = DENTAL

So I can tell that they dropped a Dental plan, but there's no way to tie that into an actual plan record because PK is 0.

Is there a better way to do this?

Post by: travis | RE: Data Researching

Tuesday, 26 May 2009 09:14 CST

@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.

Post by: blambrecht | RE: Data Researching

Tuesday, 26 May 2009 03:42 CST

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

Post by: travis | RE: Data Researching

Tuesday, 26 May 2009 02:51 CST

@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.

Post by: blambrecht | RE: Data Researching

Tuesday, 26 May 2009 12:50 CST

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!

Post by: travis | RE: Data Researching

Friday, 22 May 2009 09:17 CST

@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.

Post by: blambrecht | RE: Data Researching

Friday, 22 May 2009 04:11 CST

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

Post by: blambrecht | RE: Data Researching

Friday, 22 May 2009 03:34 CST

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

Post by: travis | RE: Data Researching

Friday, 22 May 2009 03:09 CST

@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.

Post by: blambrecht | RE: Data Researching

Friday, 22 May 2009 11:32 CST

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

Post by: travis | RE: Issues With Web API

Monday, 18 May 2009 08:55 CST

@Julie.Dickens -- Welcome to WebCOBRA!

I'm not sure what you'd like to talk about, but if you have questions regarding data structures, you can post them here (and read other people's comments and questions that blazed trails before you).

Post by: Julie.Dickens | RE: Issues With Web API

Monday, 18 May 2009 12:39 CST

We just switched to WEBCOBRA I was not informed that we would have to recreate our Access Reports. Who can I talk to on this issue? Julie

Post by: Steve.Strecker | RE: File Size Limitations - Microsoft Access DB (2 GB)

Wednesday, 13 May 2009 08:33 CST

Thank you for the quick response. We appreciate it...

Post by: travis | RE: File Size Limitations - Microsoft Access DB (2 GB)

Wednesday, 13 May 2009 06:13 CST

@Steve - we don't have anything in the pipeline right now

I think we'd either
1 split you across multiple instances
2 have you on source code by then
3 work with you on a custom basis for a solution

Post by: Steve.Strecker | File Size Limitations - Microsoft Access DB (2 GB)

Wednesday, 13 May 2009 02:55 CST

When we first began using the Relational API we were told of a file size limit for the API file - I believe this was 2 GB (this is a Microsoft Access DB limit). Until recently (we requested the deletion of old data in the system integrator), our file was over 900 MB. We expect a continued rise in participant counts in WebCOBRA and wanted to inquire about what will happen if/when we do exceed the 2 GB file limit. Is there another method in place to obtain the API extract?

This is not an urgent case - but we did want to pose the question before it became an urgent issue.

Post by: travis | RE: ARRA in RelationalAPI

Friday, 08 May 2009 01:35 CST

45 only means they've been renotified of a possibility of getting into ARRA. I would not use this unless you're looking for a list of people who you notified of ARRA.

42 means they have been Authorized to be on ARRA.

Post by: dandrews | RE: ARRA in RelationalAPI

Friday, 08 May 2009 12:25 CST

Thanks for that. I am looking at a QB who should be included as an ARRA QB.

The QB has a date type 45 record in the date tracking table, but no date type 42 record. She should be ARRA, so should the rule for determining this be:

"If the QB is enrolled and has a 45 or a 42 datetracking record, then the QB is ARRA."

Post by: travis | RE: ARRA in RelationalAPI

Thursday, 07 May 2009 11:04 CST

@Dave -- this was answered over on http://devcenter.webcobra.com/conve rsations/77.aspx in the post by jwolgamott at 04/14/2009 11:01 AM

Post by: dandrews | ARRA in RelationalAPI

Thursday, 07 May 2009 10:12 CST

Hello--

I need to implement ARRA handling in some reports that use the access database download. Where in that database can I figure out if a QB is in ARRA and therefore eligible for the reduced premium?

Thanks,
Dave Andrews
dandrews@lbmc.com

Post by: travis | RE: Unable To Find Rate Structure

Wednesday, 29 Apr 2009 08:31 CST

Great to hear... and good to have that on here for future reference!

Post by: dyoung | RE: Unable To Find Rate Structure

Wednesday, 29 Apr 2009 02:43 CST

Figured it out - due to AssignmentStartDate being before the RateStructure effective date - had to re-import plans with RateStructure history and that fixed the issue

Post by: dyoung | RE: BenefitPlan

Tuesday, 28 Apr 2009 11:15 CST

I tried this out following the example and received a pre-processing error:
PreProcessing Error:It appears John Smith with an event date of 12/01/2008 already exists in the system. Cannot proceed while QB exists

I used activity=3 for the event, but still get this error. If I remove the event node, I get the error
Module QB Benefit Assignment threw error: Object reference not set to an instance of an object.

Is there a way to avoid the pre-processing error?

Post by: dyoung | Unable To Find Rate Structure

Tuesday, 28 Apr 2009 10:16 CST

I started getting this error during testing - Module QB Benefit Assignment threw error: Unable To Find Rate Structure. The xml I am using follows the examples given in IT resources -

WebCobra>
[Customer>
[Company Activity="3" UserDefinedKey="2">
[BenefitPlans>

[Sponsor Activity="3" UserDefinedKey="KNS">

[BenefitPlan Activity="1" UserDefinedKey="G_301:2103" BenefitPlanNumber="656375-0001" Name="301_4" Description="Kaiser - A.P.P.L.E. FamilyWorks - $15 Copay Ages 50-54" QBBenefitPlanTypeCode="1" PlanAnniversaryDate="01-01-2009" BenefitPlanCostingTypeCode="1">
[RateStructure Activity="1" BeginDate="01-01-2008" EndDate="">
[TierPricing Activity="1" Description="Employee" Cost="513.06" IsFamily="1"/>
[TierPricing Activity="1" Description="Employee+Spouse" Cost="1066.92" IsFamily="1"/>
[TierPricing Activity="1" Description="Employee+Child/Childre n" Cost="846.60" IsFamily="1"/>
[TierPricing Activity="1" Description="Employee+Family" Cost="1363.74" IsFamily="1"/>
[/RateStructure>
[/BenefitPlan>
[/Sponsor>

[/BenefitPlans>
[/Company>
[/Customer>
[/WebCobra>

And this is the QBBenefitAssignment -
[BenefitPlans>
[QBBenefitAssignment Activity="1" BenefitPlanUserDefinedKey="G_301:21 03" CoverageLevel="A" OriginalCoverageDate="01-01-2008" AssignmentStartDate="12-01-2008" AssignmentEndDate="" FlatFeeCosting="" SubsidyDollarAmount="" SubsidyPercentageAmount="" DayPQBNoLongerCovered="" />
[/BenefitPlans>

Granted I have deleted and re-created the plan a few times for testing purposes - would that affect the rate structure?

Post by: travis | RE: Example XML for matrix based plans?

Tuesday, 28 Apr 2009 09:28 CST

Here's an example we found. We used the CSV, which auto converts over to XML to create the following.

[?xml version="1.0" encoding="UTF-8"?>
[WebCobra DateConverted="04/28/2009" Version="web-controller, Version=6.0.21.27844, Culture=neutral, PublicKeyToken=null" IsOnlyPayments="False">
[Customer Name="Customer" Login="" TPA="">
[Company Activity="3" UserDefinedKey="AUSTECH">
[QBGroup Activity="1" Name="Johnson,Brian" DepartmentAutoGUID="" StatusCode="1">
[QBDateTracking>
[QBDate Activity="1" QBDateTypeCode="1" Date="04/28/2009"/>
[/QBDateTracking>
[QB Activity="1" QBTypeCode="9" Title="0" Suffix="0" LastName="Johnson" FirstName="Brian" MiddleInit="" SSN="845-56-5696" QBID="" EmployeeID="" DateOfBirth="01/01/1975" HireDate="" Sex="0" OnSSDisability="0" MedicaidParticiplant="0" DoNotTerminate="0" ReceiveEmails="0" EventOccuredToThisQB="1" IncludeInAddressBlock="1" ReceiveOwnCopyOfLetters="1" MonthsOfCoreCreditableCoverage="" DaysOfCoreCreditableCoverage="" CoreCreditableCoverageStartDate="" MonthsOfPreExistingConditionMaximum Exclusion="" DaysOfPreExistingConditionMaximumEx clusion="" IsPrimaryBillingContact="1" YearsOfService="0" Salary="0" Beneficiary="" PensionEmployee="0" ARRAEligible="0">
[Addresses>
[Address Activity="1" Line1="34 Longview Drive" Line2="" City="Houston" State="TX" PostalCode="77079" AddressTypeCode="2"/>
[/Addresses>
[Phones>
[Phone Activity="1" PhoneTypeCode="4" Number="" Ext=""/>
[/Phones>
[Emails>
[Email Activity="1" Address=""/>
[/Emails>
[BenefitPlans>
[QBBenefitAssignment Activity="1" BenefitPlanUserDefinedKey="HP2009" CoverageLevel="A" OriginalCoverageDate="10/28/2007" AssignmentStartDate="02/01/2009" AssignmentEndDate="" FlatFeeCosting="0" SubsidyEndDate="" SubsidyDollarAmount="0" SubsidyPercentageAmount="0" DayPQBNoLongerCovered="" PremiumBillingStartDate="02/01/2009"/>
[/BenefitPlans>
[Events>
[Event Activity="1" QBEventCode="1" QualifyingEventDate="01/31/2009" EventNotificationDate="01/01/2009" IsCurrentAssignment="1" IsSecondaryEvent="0" EventDescriptionOverride=""/>
[/Events>
[/QB>
[/QBGroup>
[/Company>
[/Customer>
[/WebCobra>

Post by: dyoung | RE: QBBenefitAssignment for dependents

Monday, 27 Apr 2009 04:24 CST

thanks for the info - will update my code accordingly.

Post by: travis | RE: QBBenefitAssignment for dependents

Monday, 27 Apr 2009 04:23 CST

Using XML you'll want to specify each plan assignment for each QB/dependent. To not specify will mean that the QB was not assigned to that plan for that period.

Post by: dyoung | QBBenefitAssignment for dependents

Monday, 27 Apr 2009 01:20 CST

Is it sufficient to specify QBBenefitAssignment for primary QB with correct coverage level and not specify for dependents, or should QBBenefitAssignment be specified for each dependent with the same coverage as the primary QB?

Post by: dyoung | RE: Example XML for matrix based plans?

Monday, 27 Apr 2009 12:51 CST

Can you provide sample xml for adding matrix based plans for QBs? It doesn't appear that QBBenefitAssignment supports matrix based plans - and I read another thread where using BenefitPlan was recommended against - so any examples you can provide would be greatly appreciated.

Post by: travis | RE: Data Researching

Friday, 24 Apr 2009 02:26 CST

@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.

Post by: blambrecht | RE: Data Researching

Friday, 24 Apr 2009 01:22 CST

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

Post by: dyoung | RE: QB benefit assignments

Thursday, 23 Apr 2009 05:28 CST

I figured out the error - I missed the hire date for the QB.

Post by: dyoung | QB benefit assignments

Thursday, 23 Apr 2009 04:36 CST

Hi -
I have a couple questions about the QBBenefitAssignment node:

How does the CoverageLevel attribute work with rate matrices - specifically with plans that are based on age? Is there an example I can reference?

I am also receiving an error when trying to add a QB benefit - **ERROR** in QB Benefit Assignment: Object reference not set to an instance of an object

I read the related thread in this forum but didn't see any obvious solution. This is the node - I followed the existing example in the IT resources:
[QBBenefitAssignment Activity="1" BenefitPlanUserDefinedKey="G_771" CoverageLevel="A" OriginalCoverageDate="07-01-2007" AssignmentStartDate="02-01-2009" AssignmentEndDate="" FlatFeeCosting="" SubsidyDollarAmount="" SubsidyPercentageAmount="" DayPQBNoLongerCovered="" />

There is a plan that exists with that key, and I am adding the QB.

Thanks

Post by: dyoung | RE: Example XML for matrix based plans?

Wednesday, 22 Apr 2009 06:56 CST

Thanks - that is exactly what I needed to see.

Post by: travis | RE: Example XML for matrix based plans?

Wednesday, 22 Apr 2009 08:52 CST

Here is an example of XML that works, take a look at that and see what's different from your example.

Off looking at your example, you have MatrixCostingHorizontalID in the wrong place -- it should be on the Horizontal attribute

[BenefitPlan BenefitPlanAutoGUID="00020MED03PSLM D+" Name="03PSLMD+" Description="2003, SelectMed Plus" QBBenefitPlanTypeCode="1" UserDefinedKey="03PSLMD+" >
[RateStructure RateStructureID="03PSLMD+04/01/2003 " BeginDate="04/01/2003" EndDate="03/31/2004">
[MatrixCosting>
[Matrix MatrixID="00020MED03PSLMD+SINGLE1" Name="Level1" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2003, Single - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="188.3600"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="192.8000"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="210.5600"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="223.8800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="253.4800"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="268.2800"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="306.7600"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="360.0400"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="423.6800"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="508.0400"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="528.7600"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE2" Name="Level2" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2003, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="410.7200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="427.0000"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="449.2000"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="468.4400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="490.6400"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="518.7600"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="573.5200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="683.0400"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="794.0400"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="958.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1048.6000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE3" Name="Level3" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2003, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="410.7200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="427.0000"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="449.2000"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="468.4400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="490.6400"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="518.7600"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="573.5200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="683.0400"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="794.0400"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="958.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1048.6000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE4" Name="Level4" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2003, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="668.9200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="697.0400"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="747.3600"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="788.8000"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="842.0800"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="890.9200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="927.9200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="970.8400"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1038.9200"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1183.9600"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1212.0800"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE5" Name="Level5" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2003, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="668.9200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="697.0400"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="747.3600"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="788.8000"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="842.0800"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="890.9200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="927.9200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="970.8400"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1038.9200"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1183.9600"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1212.0800"/>
[/Vertical>
[/Matrix>
[/MatrixCosting>
[/RateStructure>
[RateStructure RateStructureID="03PSLMD+04/01/2004" BeginDate="04/01/2004" EndDate="03/31/2005">
[MatrixCosting>
[Matrix MatrixID="00020MED03PSLMD+SINGLE1" Name="Level1" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2004, Single - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="210.6700"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="216.9500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="235.5700"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="249.1100"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="283.6000"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="301.1200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="341.7000"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="401.9100"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="473.7000"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="568.3000"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="631.0600"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE2" Name="Level2" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2004, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="459.7200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="477.6500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="500.0400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="522.6800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="543.3100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="576.3000"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="640.4200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="763.9300"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="886.4000"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1072.2000"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1251.4000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE3" Name="Level3" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2004, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="459.7200"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="477.6500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="500.0400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="522.6800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="543.3100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="576.3000"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="640.4200"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="763.9300"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="886.4000"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1072.2000"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1251.4000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE4" Name="Level4" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2004, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="747.6100"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="778.1800"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="831.6700"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="879.1800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="934.6100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="989.6200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1035.0600"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1082.6100"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1159.5900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1321.6800"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1440.5600"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE5" Name="Level5" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2004, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="747.6100"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="778.1800"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="831.6700"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="879.1800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="934.6100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="989.6200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1035.0600"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1082.6100"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1159.5900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1321.6800"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1440.5600"/>
[/Vertical>
[/Matrix>
[/MatrixCosting>
[/RateStructure>
[RateStructure RateStructureID="03PSLMD+04/01/2005" BeginDate="04/01/2005" EndDate="03/31/2006">
[MatrixCosting>
[Matrix MatrixID="00020MED03PSLMD+SINGLE1" Name="Level1" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2005, Single - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="234.7300"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="241.7300"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="262.4500"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="277.5300"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="311.0100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="330.1900"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="373.6800"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="438.7200"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="517.6900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="620.9000"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="754.8100"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE2" Name="Level2" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2005, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="512.2800"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="532.2500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="557.1800"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="582.3800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="595.9200"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="632.0100"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="700.5000"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="835.2500"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="968.8500"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1171.5700"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1497.1000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE3" Name="Level3" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2005, 2 Party - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="512.2800"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="532.2500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="557.1800"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="582.3800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="595.9200"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="632.0100"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="700.5000"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="835.2500"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="968.8500"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1171.5700"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1497.1000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE4" Name="Level4" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2005, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="833.1700"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="867.1900"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="926.7400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="979.6400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="1025.3500"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="1085.5200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1132.6100"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1184.5000"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1268.4900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1445.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1719.8100"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE5" Name="Level5" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2005, Family - SelectMed Plus" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="833.1700"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="867.1900"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="926.7400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="979.6400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="1025.3500"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="1085.5200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1132.6100"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1184.5000"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1268.4900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1445.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1719.8100"/>
[/Vertical>
[/Matrix>
[/MatrixCosting>
[/RateStructure>
[RateStructure RateStructureID="03PSLMD+04/01/2006" BeginDate="04/01/2006" EndDate="03/31/2007">
[MatrixCosting>
[Matrix MatrixID="00020MED03PSLMD+SINGLE1" Name="Level1" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2006-2007, Single - SelectMed+" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="234.7300"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="241.7300"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="262.4500"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="277.5300"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="311.0100"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="330.1900"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="373.6800"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="438.7200"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="517.6900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="620.9000"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="754.8100"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE2" Name="Level2" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2006-2007, 2 Party-SelectMed+" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="512.2800"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="532.2500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="557.1800"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="582.3800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="595.9200"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="632.0100"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="700.5000"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="835.2500"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="968.8500"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1171.5700"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1497.1000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE3" Name="Level3" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2006-2007, 2 Party-SelectMed+" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="512.2800"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="532.2500"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="557.1800"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="582.3800"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="595.9200"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="632.0100"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="700.5000"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="835.2500"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="968.8500"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1171.5700"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1497.1000"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE4" Name="Level4" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2006-2007, Family - SelectMed+" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="833.1700"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="867.1900"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="926.7400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="979.6400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="1025.3500"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="1085.5200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1132.6100"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1184.5000"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1268.4900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1445.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1719.8100"/>
[/Vertical>
[/Matrix>
[Matrix MatrixID="00020MED03PSLMD+SINGLE5" Name="Level5" Description="" VerticalName="Dependent' Age" VerticalDescription="" HorizontalName="QB Age" HorizontalDescription="">
[MatrixHorizontalTags MatrixCostingHorizontalID="1" Name="0-19" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="2" Name="20-24" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="3" Name="25-29" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="4" Name="30-34" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="5" Name="35-39" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="6" Name="40-44" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="7" Name="45-49" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="8" Name="50-54" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="9" Name="55-59" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="10" Name="60-64" Description=""/>
[MatrixHorizontalTags MatrixCostingHorizontalID="11" Name="65-999" Description=""/>
[Vertical MatrixCostingVerticalID="S" Name="0-0 2006-2007, Family - SelectMed+" Description="" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal MatrixCostingHorizontalID="1" MatrixCostingID="1" Name="0-19" Description="" IsNumericRange="1" BeginInt="0" EndInt="19" Cost="833.1700"/>
[Horizontal MatrixCostingHorizontalID="2" MatrixCostingID="2" Name="20-24" Description="" IsNumericRange="1" BeginInt="20" EndInt="24" Cost="867.1900"/>
[Horizontal MatrixCostingHorizontalID="3" MatrixCostingID="3" Name="25-29" Description="" IsNumericRange="1" BeginInt="25" EndInt="29" Cost="926.7400"/>
[Horizontal MatrixCostingHorizontalID="4" MatrixCostingID="4" Name="30-34" Description="" IsNumericRange="1" BeginInt="30" EndInt="34" Cost="979.6400"/>
[Horizontal MatrixCostingHorizontalID="5" MatrixCostingID="5" Name="35-39" Description="" IsNumericRange="1" BeginInt="35" EndInt="39" Cost="1025.3500"/>
[Horizontal MatrixCostingHorizontalID="6" MatrixCostingID="6" Name="40-44" Description="" IsNumericRange="1" BeginInt="40" EndInt="44" Cost="1085.5200"/>
[Horizontal MatrixCostingHorizontalID="7" MatrixCostingID="7" Name="45-49" Description="" IsNumericRange="1" BeginInt="45" EndInt="49" Cost="1132.6100"/>
[Horizontal MatrixCostingHorizontalID="8" MatrixCostingID="8" Name="50-54" Description="" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1184.5000"/>
[Horizontal MatrixCostingHorizontalID="9" MatrixCostingID="9" Name="55-59" Description="" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1268.4900"/>
[Horizontal MatrixCostingHorizontalID="10" MatrixCostingID="10" Name="60-64" Description="" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1445.3200"/>
[Horizontal MatrixCostingHorizontalID="11" MatrixCostingID="11" Name="65-999" Description="" IsNumericRange="1" BeginInt="65" EndInt="999" Cost="1719.8100"/>
[/Vertical>
[/Matrix>
[/MatrixCosting>
[/RateStructure>
[/BenefitPlan>

Post by: dyoung | Example XML for matrix based plans?

Wednesday, 22 Apr 2009 03:08 CST

Hi - attempting to integrate age banded plans, but getting stuck on **ERROR** in Matrix Merging: MatrixCostingHorizontalID is a required Attribute. - although I do have that attribute specified. I am not completely confident I have the xml structured correctly for this type of plan though - is there an example xml I can reference? This is an example of xml I am creating for reference:

[BenefitPlan Activity="1" UserDefinedKey="G_241" BenefitPlanNumber="656294-0000" Name="Med_241" Description="Kaiser - AASK America - $30 Copay" QBBenefitPlanTypeCode="1" PlanAnniversaryDate="01-01-2009" BenefitPlanCostingTypeCode="2">
[RateStructure Activity="1" BeginDate="01-01-2009">
[MatrixCosting>
[Matrix Activity="1" Name="Age Banded Rates" VerticalName="FI" HorizontalName="Age">
[MatrixHorizontalTags Activity="1" MatrixCostingHorizontalID="1" Name="Age Bands" />
[Vertical Activity="1" Name="Employee" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="0" EndInt="29" Cost="242.76" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="30" EndInt="39" Cost="268.26" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="40" EndInt="49" Cost="345.78" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="450.84" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="569.16" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="701.76" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="65" EndInt="125" Cost="796.62" />
[/Vertical>
[MatrixHorizontalTags Activity="1" MatrixCostingHorizontalID="1" Name="Age Bands" />
[Vertical Activity="1" Name="Employee+Spouse" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="0" EndInt="29" Cost="678.30" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="30" EndInt="39" Cost="729.30" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="40" EndInt="49" Cost="795.60" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="936.36" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1195.44" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1333.14" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="65" EndInt="125" Cost="1720.74" />
[/Vertical>
[MatrixHorizontalTags Activity="1" MatrixCostingHorizontalID="1" Name="Age Bands" />
[Vertical Activity="1" Name="Employee+Child/Children" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="0" EndInt="29" Cost="667.08" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="30" EndInt="39" Cost="685.44" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="40" EndInt="49" Cost="656.88" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="743.58" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="850.68" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="938.40" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="65" EndInt="125" Cost="1197.48" />
[/Vertical>
[MatrixHorizontalTags Activity="1" MatrixCostingHorizontalID="1" Name="Age Bands" />
[Vertical Activity="1" Name="Employee+Family" IsNumericRange="0" BeginInt="0" EndInt="0">
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="0" EndInt="29" Cost="943.50" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="30" EndInt="39" Cost="1043.46" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="40" EndInt="49" Cost="1050.60" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="50" EndInt="54" Cost="1196.46" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="55" EndInt="59" Cost="1374.96" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="60" EndInt="64" Cost="1556.42" />
[Horizontal Activity="1" Name="Age Band" IsNumericRange="1" BeginInt="65" EndInt="125" Cost="1892.10" />
[/Vertical>
[/Matrix>
[/MatrixCosting>
[/RateStructure>
[/BenefitPlan>

Post by: jwolgamott | RE: Cache problem with imported

Thursday, 16 Apr 2009 09:53 CST

The actual logged error was

The '=' character, hexadecimal value 0x3D, cannot be included in a name. Line 1, position 300006.

So it looks like the XML was invalid

Post by: dyoung | RE: Cache problem with imported

Wednesday, 15 Apr 2009 10:13 CST

JW -
thanks - the import was successful. I started importing QB records using the XML format and they were showing up immediately. I tested a couple, then deleted them. I attempted to run another import, but now getting:
A Major error was reported in WebCOBRA.com
* The error was automatically reported
* Date of Error: 4/15/2009 at 10:12:49 PM
* Module of Error: SystemIntegratorPerform

Your error number is: 4686

Any idea what happened? Did I break something?

Post by: blambrecht | RE: Data Researching

Wednesday, 15 Apr 2009 04:07 CST

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

Post by: jwolgamott | RE: Data Researching

Wednesday, 15 Apr 2009 04:01 CST

@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

Post by: blambrecht | RE: Data Researching

Wednesday, 15 Apr 2009 11:57 CST

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

Post by: blambrecht | RE: Data Researching

Wednesday, 15 Apr 2009 11:37 CST

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

Post by: jwolgamott | RE: Cache problem with imported

Tuesday, 14 Apr 2009 08:45 CST

@Darrell -- You should be good to go

Also, we've tweaked the Company add process (when using the system integrator) and you might not need to get us to refresh the cache after. (hopefully). Let us know if you need it done in the future.

Post by: dyoung | RE: Cache problem with imported

Tuesday, 14 Apr 2009 02:54 CST

JW,
I completed a successful integration of Companies, Sponsors and Plans - please reset the caching on the site as soon as you have time available so that our client can audit the data and make corrections. The site code is S3T.

Thanks -
Darrell
S3 Technical Solutions

Post by: dyoung | RE: Cache problem with imported

Tuesday, 14 Apr 2009 11:35 CST

JW - Thanks for the recommendations - I am taking your advice and going with option 2, and verifying data with the WebAPI. I will let you know when I am done so you can refresh the cache.
Thanks.

Post by: jwolgamott | RE: Data Researching

Tuesday, 14 Apr 2009 11:01 CST

@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

Post by: jwolgamott | RE: Cache problem with imported

Monday, 13 Apr 2009 08:04 CST

@dyoung -- I recommend pursuing one of two options:

1) Manually adding client/employers via the web interface, and then import your plans after the fact
2) importing your employers via XML. Once complete, post here and we'll manually refresh the cache for you

You'll probably want to go with #2.. We won't have a fix for at least 2 weeks due to ARRA commitments, so with a deadline I think either of those is your best bet.

Post by: blambrecht | RE: Data Researching

Monday, 13 Apr 2009 07:02 CST

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

Post by: dyoung | Cache problem with imported clients,sponsors,plans

Monday, 13 Apr 2009 05:03 CST

Hi JW -
I am bringing this issue into a new thread. You mentioned in the previous thread:

>@dyoung -- we reset the caching and it looks like they're >appearing now.

>When you add your next company via the system integrator, >try editing your user, that could force a refresh.

>If it doesn't post here and we'll work in a fix.

After you reset the caching, the imported clients were there. I deleted them in order to resume testing, as I need to import 400 clients along with sponsors and plans.
Now, the problem has returned - after a successful import with the system integrator, I cannot access the imported clients, sponsors or plans from the WebCobra front end - they just aren't there - but I can verify the data is there via a query with the WebAPI_Simple sample app. I tried changing the address on the user to force a refresh, and that did not work. I am fast approaching a deadline to integrate our customer's 400 clients with WebCOBRA, so if you have a fix ready, or have other suggestions for me to try, I would greatly appreciate it.

Post by: dyoung | RE: Minimum required data to import new client?

Friday, 10 Apr 2009 01:36 CST

Still no luck - I modified current user address, contact info - no refresh. Is there something else I can try? otherwise I need a fix.

Post by: dyoung | RE: Minimum required data to import new client?

Wednesday, 08 Apr 2009 12:05 CST

An observation - if I create a new client/employer from the front end, they show up immediately, however the imported clients do not, but I can verify the data exists for the imported client via the WebAPI sample app. Just so I understand better - is the imported data kept in a separate partition, then later exposed to front end?

Post by: dyoung | RE: Minimum required data to import new client?

Wednesday, 08 Apr 2009 11:58 CST

I attempted to add another client after deleting the previous test clients. The new client hasn't shown up yet - I tried editing the current user info via the "My User Info" link at the top right hand corner by saving security settings and changing address, but that seemed to have no effect. Is there something else I can try?

Post by: jwolgamott | RE: Minimum required data to import new client?

Wednesday, 08 Apr 2009 08:55 CST

@dyoung -- we reset the caching and it looks like they're appearing now.

When you add your next company via the system integrator, try editing your user, that could force a refresh.

If it doesn't post here and we'll work in a fix.

Post by: dyoung | RE: Minimum required data to import new client?

Tuesday, 07 Apr 2009 08:46 CST

To add the client, I used activity 1 in a previous file. To add benefit sponsors & plans, I followed the example and used 3 for company and 1 for sponsor and benefits after adding the company. I downloaded the MDB this morning and verified the companies I added are in the companies table.

Post by: jwolgamott | RE: Minimum required data to import new client?

Tuesday, 07 Apr 2009 08:41 CST

@dyoung -- I checked the XML and it looks like

[Company Activity="3" UserDefinedKey="3"]

Is that how you uploaded it? Activity=3 designated that you are only definining a user-key rather than editing (Activity=2) or adding (Activity=1).

Try

[Company Activity="1" UserDefinedKey="3"]

Post by: dyoung | RE: Minimum required data to import new client?

Tuesday, 07 Apr 2009 09:59 CST

The admincode is s3t - let me know if you need more info. Thanks for checking.

Post by: jwolgamott | RE: Minimum required data to import new client?

Tuesday, 07 Apr 2009 08:10 CST

@dyoung - it might be a caching problem. What's your admin code (admincode.webcobra.com)... I'll take a look and if we do have a significant delay between when you add a company via the system integrator and seeing it on the site we'll tweak that.

Post by: dyoung | RE: Minimum required data to import new client?

Tuesday, 07 Apr 2009 02:39 CST

I verified the data is being saved to the database via the WebAPI - but for some reason the clients do not show up on the front end. I have set up parameters, events, plan sponsor and plan for a test client via the integrator, but still the client does not show up in the list to manage - any ideas?

Post by: dyoung | RE: Minimum required data to import new client?

Monday, 06 Apr 2009 09:02 CST

geeze - you are right. Sorry to waste your time - I was up late staring at it and that didn't register. Thanks so much for your help.

Post by: jwolgamott | RE: Minimum required data to import new client?

Monday, 06 Apr 2009 08:58 CST

@dyoung -- the XMLElement is called 'Client' in your XML, but you'll want to call it a 'Company'... it should work after that (although admittedly I did not test it)

Post by: dyoung | RE: Minimum required data to import new client?

Monday, 06 Apr 2009 12:12 CST

My goal is to get at least the clients in so that our client can verify, then import plans, and have clients add new plans that aren't in our system manually.

Post by: dyoung | RE: Minimum required data to import new client?

Monday, 06 Apr 2009 11:23 CST

I am trying something simple like this:

[?xml version="1.0" encoding="utf-8"?>
[WebCobra>
[Customer>
[Client UserDefinedKey="2" Name="A.P.P.L.E. Family Works" CompanyCode="C2" Activity="1">
[Addresses>
[Address Activity="1" AddressTypeCode="1" Line1="123 Joe Court" Line2="" City="San Rafael" State="CA" PostalCode="94903" />
[/Addresses>
[/Client>
[/Customer>
[/WebCobra>

Post by: jwolgamott | RE: Minimum required data to import new client?

Monday, 06 Apr 2009 08:28 CST

@dyoung -- try pasting the text of the XML you're using in here and we'll see if there's something obviously missing.

Replace the open-brackets "<" with [ and it should let you post.

Post by: dyoung | Minimum required data to import new client?

Monday, 06 Apr 2009 02:34 CST

Hi,

I am trying to use system integration to import new clients - I have created test files with minimal client data - no contacts, carriers, plans etc. Although the management console does not complain about the file and says file was successfully integrated, I do not see the new client. The idea is to setup the clients, then import the rest of the setup, or in some cases have the client setup manually completed.

Thanks

Post by: jwolgamott | RE: Data Researching

Tuesday, 31 Mar 2009 09:17 CST

@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)

Post by: blambrecht | RE: Data Researching

Monday, 30 Mar 2009 06:36 CST

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

Post by: jwolgamott | RE: Company/Client Address and Contact Info

Wednesday, 25 Mar 2009 03:38 CST

The thread on http://devcenter.webcobra.com/conve rsations/63.aspx will help you out

Post by: rodneysmiley | Company/Client Address and Contact Info

Wednesday, 25 Mar 2009 03:05 CST

Hi - within the R-API, where would I find address and contact information for companies/clients? I've looked through the tables and I can't find one that contains that info - I did find an addresses table but there wasn't anything that would link it back to a specific company.

Thanks!

Post by: cbartkovich | RE: XML Load Phones format

Wednesday, 25 Mar 2009 02:16 CST

It didnt work:
**ERROR** in QB Phones: Could not continue in QB Phones --> Number: (407) 555-5556

Post by: jwolgamott | RE: XML Load Phones format

Tuesday, 24 Mar 2009 08:05 CST

In general we use the format
(555) 555-5555

If that's not working, I recommend getting with support to drill down to the true problem

Post by: cbartkovich | XML Load Phones format

Tuesday, 24 Mar 2009 04:29 CST

What is the correct format for importing Phone Number? I've tried many formats, including with and without Ext. I get the following message:
Could not continue in QB Phones --> Number: 932-333-6288

Post by: tslusher | RE: Undescribed Errors

Tuesday, 17 Mar 2009 01:20 CST

I have now tried the others. It gets the error for all the admin codes.

Post by: tslusher | RE: Undescribed Errors

Monday, 16 Mar 2009 07:16 CST

CBIZ1, I have not yet tried the others.

Post by: jwolgamott | RE: Undescribed Errors

Saturday, 14 Mar 2009 02:41 CST

@tslusher -- which CBIZ admin code fails for you? It'll be CBIZ1 ... CBIZ5. Or are all failing?

Post by: tslusher | Undescribed Errors

Friday, 13 Mar 2009 07:43 CST

I'm getting Undescribed Errors today and yesterday for the Relational API download. (Admin code CBIZ) Can you fix it? Thanks!

Post by: dhazzard | Relational API - Premium Report

Tuesday, 10 Mar 2009 09:45 CST

I'm working on creating a query that will give me the following information:

-Company Code
-EE Location Code
-EE First Name
-EE Last Name
-EE SSN
-Premium Paid Start Date
-Premium Paid End Date
-Admin Fee
-Premium (Amount)
-Description (Benefit Description)

This query will be dumped to csv for a client import. I'm having some difficulty finding the data for this. The Premium Paid Start/End Dates are the dates for which the premium is being paid (ex. employee paid $1250 which covers 1/1/2009 through 3/1/2009).

Any help would be greatly appreciated.

Thanks,
-Dana

Post by: cbartkovich | RE: What more can we do?

Wednesday, 04 Mar 2009 06:11 CST

Do you support bulk delete of Cobra Notice Employees? We inadvertantly loaded members and the data has become stale. We would prefer to load anew instead of submitting updates.

Post by: dkreynolds | RE: Scheduled End of Continuing Coverage

Tuesday, 03 Mar 2009 02:07 CST

Thanks. That solves my problem.

Post by: jwolgamott | RE: Scheduled End of Continuing Coverage

Tuesday, 03 Mar 2009 01:16 CST

@dkreynolds: that specific field isnt stored, its calculatated, but here's a way for you to calculate itself. The end of Continuation Coverage is the maximum of all plans for either:
* COBRA End Date
* State End Date
* 11 months after the last of the previous 2 based on Social Security End Date

Here's a way you could get the QBGroups maximum date for each of the following. You'd then need to process these three into a table based on which of the 3 is the maximum.

select qbgroups.qbgroupid,max(qbbenefitass ignments.dayPQBNoLongerCovered) as 'Last Day of Federal', max(NoLongerOnStateCoverage) as 'Last Day of State', max(SocialSecurityEndDate) as 'Social Security End date'
from qbbenefitassignments
inner join qb on qbbenefitassignments.qbautoid = qb.qbautoid
inner join qbgroups on qbgroups.designatedgroupcontact = qb.qbautoid
inner join qbgroupinformationcache on qbgroupinformationcache.qbgroupid = qbgroups.qbgroupid

group by QBGroups.QBGroupID

Post by: dkreynolds | Scheduled End of Continuing Coverage

Tuesday, 03 Mar 2009 12:08 CST

I'm trying to determine where to find this in the relational api database. I want to pull the exact same value that you show on the QB Detail Information page (particularly in the case where benefits have been extended due to disability). Thanks in advance for your help.

Post by: jwolgamott | RE: Relational API

Friday, 27 Feb 2009 08:13 CST

@rlalouf - Relational API links expire after 48 hours, so if you waiting until after 48 hours to download from the website, you'll receive an access denied error.

You need to re-request a RelationalAPI request.

Post by: rlalouf | Relational API

Thursday, 26 Feb 2009 04:42 CST

I generated a Relational API report on 02/24 and the report has compeleted. I never received an e-mail with the link to follow. I tried selecting the download button next to the report but it would tell "the website decline to show me this webpage" most likely cause "this website requires you to login". I have an open case with support, but it's going on 2 days wiythout a resolution. I do have the Enterprise edition of webCOBRA so I should be able to use the relational API reporting function. I need to get the report to download ASAP. Can anyone help?

Post by: jwolgamott | RE: Group Information from the Relational Database

Wednesday, 25 Feb 2009 04:57 CST

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

Post by: kotradov | RE: Group Information from the Relational Database

Wednesday, 25 Feb 2009 02:33 CST

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?

Post by: pmckenna | RE: Issues With Web API

Tuesday, 24 Feb 2009 09:52 CST

Hi Team - I am trying to download the Access API data and it get to the 4th step, Getting QBS and I am getting a popup box stating "Sorry, we had an error: Unspecified Error in your request". If I click OK, the process runs on the Getting QBs, but after a half hour, I still don't have any progress. Have you seen this before with the API downloader to the access file?

Paul

Post by: jwolgamott | RE: Group Information from the Relational Database

Tuesday, 24 Feb 2009 09:45 CST

@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

Post by: kotradov | RE: Group Information from the Relational Database

Tuesday, 24 Feb 2009 08:54 CST

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?

Post by: kotradov | RE: Group Information from the Relational Database

Monday, 23 Feb 2009 03:49 CST

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

Post by: jwolgamott | RE: QBs table date end's on May 31, 2007

Friday, 13 Feb 2009 02:31 CST

@Judy -- I think you were getting an error about a duplicate key... please try it again

Post by: dandrews | RE: Missing payments from webservice

Friday, 13 Feb 2009 02:04 CST

Thanks for your help. It appears to be working great now.

Post by: jwolgamott | RE: Missing payments from webservice

Friday, 13 Feb 2009 02:03 CST

@dandrews

I just verified that you can now use multiple where clauses in your WQS query

Please let me know if you have any more trouble, but this should get you to where you need to be!

Post by: JudyTatton | RE: QBs table date end's on May 31, 2007

Friday, 13 Feb 2009 12:07 CST

Hate to be a pain, but I'm still not seeing any recently terminated employees showing up on any of the reports.

Any idea, what I might be doing wrong?

Post by: jwolgamott | Single Sign On Launched

Tuesday, 10 Feb 2009 04:17 CST

We launched Single Sign On this last weekend in WebCOBRA as part of our WebCOBRA9 release.

This lets you create a signed (HMAC-SHA1) link using a shared secret key and auto-sign in a user to WebCOBRA from your secured Website.

More details at our Blog (blog.travisoft.com) -- specifically http://blog.travisoft.com/new-featu res

If there's interest, ask questions and we'll answer them ASAP.

Post by: jwolgamott | RE: Missing payments from webservice

Monday, 09 Feb 2009 11:21 CST

Great to know our fix got you closer to your goal.

We're appending an errand "AND" to the end of our query if you specify more than one where clause.

We'll get this fixed in the next couple of days and let you know. Sorry about that.

Post by: dandrews | RE: Not all QBs returning

Monday, 09 Feb 2009 11:12 CST

Thanks! I checked it earlier this morning actually now and I am able to query for a single QB.

However, after that update I ran into this issue as posted in another message.

http://devcenter.webcobra.com/conve rsations/118.aspx

I appreciate your help!

Post by: jwolgamott | RE: Not all QBs returning

Monday, 09 Feb 2009 11:09 CST

@dandrews -- Please try this again, we posted an update this weekend that will allow you to use a Where clause effectively in the SQS and your programs.

Post by: dandrews | RE: Missing payments from webservice

Monday, 09 Feb 2009 09:53 CST

Thanks, it appears that payments past #2000 are being reported correct now. But unfortunately I have run into another problem, using >= and <= on a date.

This query worked great before the fix, but fails now:

select id, dateentered, paymentamount, qb_id, paymentmethod, TransactionIdent, Void, VoidedOn from qb_payment where dateentered >= '2/5/2009' and dateentered <= '2/5/2009'

Where I am using a startdate and end date in my report. However, when I try to do a >= AND <= on that datefield now it fails.

This query works:

select id, dateentered, paymentamount, qb_id, paymentmethod, TransactionIdent, Void, VoidedOn from qb_payment where dateentered >= '2/5/2009'

And This query works:

select id, dateentered, paymentamount, qb_id, paymentmethod, TransactionIdent, Void, VoidedOn from qb_payment where dateentered <= '2/5/2009'

But the combination of the >= and the <= fails with a SoapException: Unspecified Error

Post by: JudyTatton | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 04:06 CST

6606 in QB table

But if I create a report in access by going:

Reports -> Create report by using wizard -> I'm using QBs (terminated employees names and event type)and ClientEmployers (to get the company names).

It's not pulling all of the most recent terminations.

I could sort from the QB table if it had the client name added.

Post by: jwolgamott | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 03:39 CST

Judy --

How many total records are in the QB table?

Post by: JudyTatton | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 03:22 CST

gbsb

Post by: jwolgamott | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 03:14 CST

Judy --

Sorry I wasn't clearer -- I just needed your login code, not your username/password.

I edited your post to remove the password, but you should reset your password in webcobra since this is a public forum

Post by: JudyTatton | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 11:29 CST

WebAPI

JudyTatton
******

Post by: jwolgamott | RE: QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 11:21 CST

Judy --

Are you using the RelationalAPI or the WebAPI? also, could you post in your login code?

We can take a look and see what's what.

Post by: JudyTatton | QBs table date end's on May 31, 2007

Friday, 06 Feb 2009 09:27 CST

I'm trying to view terminations as an event type with a date range from 9/1/08 to current and the data stops on 5/31/07. I'm using Access to the the QB table. Any idea what I'm missing?

Post by: jwolgamott | RE: Error trying to get Relational API

Tuesday, 03 Feb 2009 03:20 CST

Sounds good on the each afternoon (but that might get old).

I really don't see this happening again ... the reason it failed was because we were trying to send down a table that contained a breakdown of all your data-usage (and that breakdown daily since you started using WebCOBRA).

The XML that was generated was just taking up too much memory and was generated out of memory errors.

Post by: kotradov | RE: Error trying to get Relational API

Tuesday, 03 Feb 2009 02:23 CST

We don't have the AI part for the UNI account, but I can and will start requesting a copy each afternoon.

If we would have a problem on the 25th/26th like this one, if we contact you, can you fix it relatively quickly? In your opinion, do you see anything like this problem happening again?

Thanks,
Kirk

Post by: jwolgamott | RE: Error trying to get Relational API

Tuesday, 03 Feb 2009 02:01 CST

Kirk --

I can't provide any assurances, but I can recommend that you either download a copy daily, or subscribe to the RelationalAPI Robot which will download a copy daily.

If it doesn't work for you on the 24th (because of some new hard limit we reach), we can diagnose then and make sure you get your data.

Post by: kotradov | RE: Error trying to get Relational API

Tuesday, 03 Feb 2009 10:56 CST

As a FYI, I just received notification that I could download the bcbsga data and did so successfully. I can only guess that you requested the data for me again and it worked.

I have a concern at this point. For the UNI data, I need to request the data at 6pm on 2/25 so that it is ready for me as soon as possible on 2/26 because I have a hard deadline for that day. What assurances do I have that this problem won't appear again for that download?

Thanks,
Kirk

Post by: kotradov | RE: Error trying to get Relational API

Tuesday, 03 Feb 2009 07:29 CST

This morning, I received the e-mail to download the Relational API for UNI, but the error still exists for bcbsga.

Thanks,
Kirk

Post by: kotradov | RE: Error trying to get Relational API

Monday, 02 Feb 2009 07:07 CST

I noticed you tried submitting 2 to the bcbsga and both had the error. I have since requested another from UNI and BCBSGA and I will let you know if those work.

Thanks,
Kirk

Post by: jwolgamott | RE: Error trying to get Relational API

Monday, 02 Feb 2009 04:43 CST

It looks like the DataUsageBreakDownHistory_XML table was overloading our memory... So we removed those two tables (which listed out detailed information about data usage over time) from the relationalAPI.

We re-submitted your BCBS/GA to your email address, so it should go through now. If it doesn't please post back here.

Post by: kotradov | Error trying to get Relational API

Monday, 02 Feb 2009 12:27 CST

Hello,
I have opened a Ticket, #25613, on this as well, but thought I would post too. I am trying to request a Relational API. I set my e-mail address and continue. I do not get a e-mail and when I log back into the WebCobra Relational API request screen later, it gives a Undescribed Error in the Download column. I had previously gotten a good download on 12/22/08 for this admin code, "UNI".

I also tried this for admin code "bcbsga" and got the same error this morning. I had been able to get the data for this one in the past as well.

Your assistance is appreciated.

Thanks,
Kirk

Post by: jwolgamott | RE: Payment and Billing data

Friday, 30 Jan 2009 04:18 CST

Sure you can --

On the AccountingRegisterTable, in your example, the record would look like

Type=1
Closed=0
Amount=750
InvoiceAmountDue=50
InvoiceAmountPaid=700

Post by: arca2027 | RE: Payment and Billing data

Friday, 30 Jan 2009 02:47 CST

Thanks much. Is there a way to match month to month payments verses what is owned. Example: Jane ownes $750 for April and she pays us $700 in April so she now owes us $50 in the month of April.

Thanks again for your help.

Post by: jwolgamott | RE: Payment and Billing data

Friday, 30 Jan 2009 01:14 CST

That info is the accountingregister table

Type=1 : Invoice
Type=2 : Payment Application

If the Invoice is Closed=1 then it's been paid. Otherwise, you can get the PercentOfBillPaid

You will have 1 Type=2 entry for every time a payment is used on an invoice... So if our theoretical payment that paid for some of May, June, and some of July, you'd see three entries.

Post by: arca2027 | RE: Payment and Billing data

Friday, 30 Jan 2009 11:29 CST

Do you want to show Payment breakdowns? Or maybe just show open premiums vs paid premuims? Or just a list of payments?

I need to determine how much the QB paid in in a given period vs. what was owed, pending balance if any. Thanks.

Post by: jwolgamott | RE: Missing payments from webservice

Thursday, 29 Jan 2009 02:54 CST

Dave --

we haven't fixed the results coming over the WebAPI... It's currently only returning the results that match your query (in the first 2,000 results)

You're not doing anything wrong, we just overlooked this with our last paging optimization.

We most likely won't have a fix for another 10 days or so. If you need something up and running before then, you may want to look at the RelationalAPI, which would refresh a local copy of your data daily.

[We will fix this problem though]

Post by: dandrews | Missing payments from webservice

Thursday, 29 Jan 2009 01:26 CST

I am running a query to the webservice that pulls all payments from 1/28/2009. This should return around 50 payments. However, I am only getting back 18 payments from the query. All other days I have tried return the correct number of results. What am I doing wrong?

===================================
select id, dateentered, paymentamount, qb_id, paymentmethod, TransactionIdent, Void, VoidedOn from qb_payment where dateentered >= '1/28/2009' and dateentered <= '1/28/2009'
===================================

(The query is in a report which takes a date range, which is why I am using >= and <=)

Dave Andrews
dandrews@lbmc.com

Post by: jwolgamott | RE: Payment and Billing data

Thursday, 29 Jan 2009 12:38 CST

No problem --

_Query to show Billed amounts by group and department_

-- This should show that to yo.. The type on AccountingRegister will be 1 for invoice

select companies.companyname, isnull(QBGroupAssociations.Descript ion, 'N/A'), billingtimeperiods.FirstFullDay, QB.LastName, QB.FirstName, QB.SSN , AccountingRegister.Amount
from billingtimeperiods
inner join AccountingRegister on AccountingRegister.billingtimeperio did = billingtimeperiods.billingtimeperiodid and type = 1
inner join QBGroups on QBGroups.QBGroupID = AccountingRegister.qbgroupid
left outer join QBGroupMembers on QBGroupMembers.QBGroupID = QBGroups.QBGroupID
left outer join QBGroupAssociations on QBGroupAssociations.QBGroupAssociationID = QBGroupMembers.QBGroupAssociationID
inner join QB on QBGroups.DesignatedGroupContact = QB.QBAutoID
inner join companies on companies.companyid = qbgroups.companyid
order by companies.companyname, billingtimeperiods.FirstFullDay, QB.LastName, QB.FirstName

_What is Paid by a QB_
This gets a little complicated -- If a QB sends in a payment in May, it could be split out and pay for some money in April, fully pay for May, and some for June (and all sorts of variations in between.

Do you want to show Payment breakdowns? Or maybe just show open premiums vs paid premuims? Or just a list of payments?

Post by: arca2027 | RE: Payment and Billing data

Thursday, 29 Jan 2009 11:20 CST

Sorry just noticed my redundant question. Can you point me in this direction?:

1) One report that lists what is billed for a given time period, listed by QB and company association.

2) A Separate report that lists what is paid by a QB in a given time period, also associated by company.

Thanks.

Post by: evannielsen | RE: integrator error

Wednesday, 28 Jan 2009 03:11 CST

I did finally get the working after I realized that I need to add a web reference to my project instead of a "Service Reference" as Visual Studio 2008 is calling it now. Refer to the link below for instuctions.

http://www.techtoolblog.com/archive s/add-web-references-to-visual-stud io-2008

Post by: jwolgamott | RE: integrator error

Tuesday, 27 Jan 2009 08:38 CST

OK, can you try changing your bindings to https://app.webcobra.com/exec/Syste mIntegrator.asmx from the http?

I think you're receiving a redirect, which is coming from our load balancer and its coming back as text.

It's then complaining that its not XML coming back (but its never hitting our servers)

If that's not it we can install VB.NET 3.5 and debug into it for you.

Post by: jwolgamott | RE: integrator error

Monday, 26 Jan 2009 08:49 CST

Sure, I can take a look for you.

Email it to me at my username @ travisoft.com -- make sure you include the XML file or text you're sending across.

Post by: evannielsen | RE: integrator error

Monday, 26 Jan 2009 01:44 CST

I am using straight soap. Could I send the projec to you in an email?

Post by: jwolgamott | RE: integrator error

Monday, 26 Jan 2009 01:25 CST

ok, are you trying to do WCF, or just straight SOAP communication?

We'll probably end up needing to get and run your code to see what the deal is. can you package that up and publish to a location we can download it?

Post by: evannielsen | RE: integrator error

Monday, 26 Jan 2009 11:36 CST

I am using .net 3.5 and posting to the systemintegrator.asmx page.

Post by: jwolgamott | RE: integrator error

Monday, 26 Jan 2009 11:06 CST

What about your codeset, .NET 2.0 or something like ruby/php?

It's possible that we're returning HTML instead of XML to you, but other users haven't reported a problem.

And just to make sure, are you posting to the api.asmx page?

Post by: evannielsen | RE: integrator error

Monday, 26 Jan 2009 06:48 CST

I am using the web service and I am sending xml.

Post by: jwolgamott | RE: integrator error

Saturday, 24 Jan 2009 08:09 CST

Are you uploading a file, or are you trying to integrate automatically using the web-service?

It seems like the file you're uploading isn't xml, but that's hard to tell from here.

[Sorry for the late reply -- Feedburner seems to have had problems in the last week]

Post by: jwolgamott | RE: Data

Saturday, 24 Jan 2009 08:08 CST

The ParameterTypeID links to the ParameterTypes table, so you can do a simple query from there.

You'll find that's the setup on almost all tables -- if there is a typeid, it'll link to the primarykey of that table.

So ParameterModuleData.ParameterTypeID = ParameterTypes.ParameterTypeID

[Sorry for the late reply -- Feedburner seems to have had problems in the last week]

Post by: jwolgamott | RE: API documentation

Saturday, 24 Jan 2009 08:07 CST

If you would like to schedule some professional services help, please contact Sarah Knight. She can get you the contract and get the process started.

Post by: jwolgamott | RE: Payment and Billing data

Saturday, 24 Jan 2009 08:06 CST

The AccountingPayment table lists Payments, the AccountingInvoices table and the AccountingInvoiceLineItem table show the amount billed for each month.

The two tables are linked together in the AccountingRegister table, where the payments are applied to the invoices.

If you're looking for simply what is billed, the AccouningInvoices table will do the trick.

[Sorry for the late reply -- Feedburner seems to have had problems in the last week]

Post by: evannielsen | integrator error

Wednesday, 21 Jan 2009 01:46 CST

I received the following error while attempting to import some xml using the system integrator. What should i do to fix this problem?

The content type text/html; charset=utf-8 of the response message does not match the content type of the binding (text/xml; charset=utf-8). If using a custom encoder, be sure that the IsContentTypeSupported method is implemented properly.

Post by: arca2027 | Data

Monday, 19 Jan 2009 01:13 CST

Please identify the ParameterTypeID data items in the ParameterModuleData table.

Post by: arca2027 | RE: API documentation

Sunday, 18 Jan 2009 10:47 CST

Still looking for some help in this area. A few minutes on the phone with a developer would go a long way to clearing up my issues. My dime.

Post by: arca2027 | Payment and Billing data

Sunday, 18 Jan 2009 10:35 CST

I need to develop a report that shows how much is billed and how much was billed on a given month. A good overview of the the accounting tables would be useful. I need to understand how the table are linked. Thanks.

Post by: jwolgamott | RE: Not all QBs returning

Friday, 16 Jan 2009 04:01 CST

@dandrews -- this should work and you're doing everything correctly.

It's broken because we've been optimizing the WebAPI for the access data loader, and neglected to let you pull one QB. The QB in question is in page-5 of the downloaded results, but should be page 1 based on your query.

We'll get this worked out and post an update here within the next couple of days.

Post by: dandrews | RE: Not all QBs returning

Friday, 16 Jan 2009 08:28 CST

Yes the admin code is bb.

Here is one example, copied from the sample program that allows you to run queries. I had to update the URL it was pointing to but it works.

=================================== ==============

select qb_id from qb_payment

.... results truncated ....
QB_ID='3xD0000089608', ID='5xD0000002279'

Complete. Enter Q to quit, else to restart
----------------------------------- --------------
WebCOBRA.com Sample WebAPI Query
-------------------------------------------------

Enter your Query or enter 'sample' for sample query
select firstname, lastname from qb where id = '3xD0000089608'
Request complete with 0 results

Complete. Enter Q to quit, else to restart

============================================

As you can see, I pulled the QB_ID result from the QB_Payment and tried to query for the name of the QB who made the payment, but I received 0 results from my QB query.

This query returns 0 results as well:

select firstname, lastname from qb where id like '%89608'

Please help! The result of the process I'm developing will be faster up-to-date payment reports for the company and less queries to webcobra.

Post by: jwolgamott | RE: Not all QBs returning

Thursday, 15 Jan 2009 06:16 CST

@dandrews -- can you give an example of an ID that isn't coming over for you? It's non-personally identifier, so its safe to post here.

Also, you're in the 'BB' instance, right?

Post by: dandrews | Not all QBs returning

Thursday, 15 Jan 2009 04:16 CST

I'm using the web api (webservice) and I am trying to download just the payments for today. Those come through fine, but I use the QB_ID from the QB_Payments to verify that I have a record of the QB who made the payment.

However, in many cases if I try to download a missing QB by querying select .... from QB where ID = '[qb_id field from payment record]' I don't get a QB back! Why are these QBs not returning?

Post by: arca2027 | RE: API documentation

Wednesday, 14 Jan 2009 02:58 CST

How about a Database Schema? Tables with their associated Primary Keys and Foreign Keys would be a good. Thanks.

Post by: jwolgamott | RE: Group Information from the Relational Database

Wednesday, 14 Jan 2009 08:56 CST

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.

Post by: kotradov | RE: Group Information from the Relational Database

Tuesday, 13 Jan 2009 01:43 CST

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

Thanks,
Kirk

Post by: kotradov | RE: Group Information from the Relational Database

Tuesday, 13 Jan 2009 01:23 CST

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

Post by: jwolgamott | RE: API documentation

Tuesday, 13 Jan 2009 01:22 CST

@arca2027 -- We may upgrade the Devcenter to include a wiki style format for tables in the database, but at this point this format of support is what we can offer you.

I think it gets the information across and lets us know where to focus help on with the tables.

Post by: jwolgamott | RE: Error at Step 4

Tuesday, 13 Jan 2009 01:20 CST

Kirk --

Sorry, I'm just sharing some information that I thought you'd find useful. If you'd like to speak one on one with a consultant, we have Professional Services that can help you out with that. It's a billable feature at an hourly rate -- please ask support for more information.

It might be easier if you would start a new thread and list out each field you're curious about in one post -- we could then respond in one post and the question/answer would seem more fluid.

With the connection errors -- The WebAPI just doesn't perform well on large datasets. If you were using it to integrate a phone system or send 1 QB queries, I think you'd find better success with it, but with an organization of your volume, I think you've just outgrown it. If I were you, I would commit to the RelationalAPI.

Post by: arca2027 | API documentation

Tuesday, 13 Jan 2009 11:22 CST

I know we can post questions, but some basic documentation would be nice. How about starting with a Database Schema? Tables with their associated Primary Keys and Foreign Keys would be a good start. Thanks much.

Post by: kotradov | RE: Error at Step 4

Tuesday, 13 Jan 2009 10:15 CST

Jesse,
If you have a few minutes, could you give me a call and we could discuss some of the fields in the WebAPI and how I can find them in the Relational API.

My information should be in your support documentation.

Thanks,
Kirk Otradovec

Post by: jwolgamott | RE: Group Information from the Relational Database

Tuesday, 13 Jan 2009 08:39 CST

@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.

Post by: kotradov | RE: Error at Step 4

Tuesday, 13 Jan 2009 08:30 CST

The last error wasn't a duplicate key error. Please note the error I received in step 5.

Post by: jwolgamott | RE: Error at Step 4

Tuesday, 13 Jan 2009 08:27 CST

@kotradov -- well you can certainly continue down that road; however I highly recommend you standardize on the RelationalAPI and download it daily.

When you get the connection errors on the WebAPI, please try it again. If you get the duplicate key errors on the WebAPI, you can either alter the Access Database to allow it, or you can edit the C# code to handle that eventuality.

Post by: dandrews | Problem loading flex account benefit for QB

Monday, 12 Jan 2009 02:25 CST

We are trying to load a QB who has a QBBenefitAssignment of a Flex benefit plan. The XML for that particular assignment looks like this (removed the < and >)

QBBenefitAssignment Activity="1" BenefitPlanUserDefinedKey="HCFSA" OriginalCoverageDate="01/01/2009" FlatFeeCosting="21.67" AssignmentStartDate="3/1/2009" CoverageLevel="3"

When I try to run the integration I get an error msg saying that either I'm trying to assign a coveragelevel, or the FSA account is not in it's own type 3 container. I tried removing the coveragelevel attribute, but then I am told by the integrator that CoverageLevel is a required attribute. How can I get around that problem?

Post by: kotradov | RE: Error at Step 4

Monday, 12 Jan 2009 01:53 CST

Yes, I am trying to utilize both. The WebAPI seems more straightforward in trying to get QB data. I am trying to get the group data from the Relational. At this point, I am to far along with this route using both, so that is my preference.

Post by: jwolgamott | RE: Error at Step 4

Monday, 12 Jan 2009 01:50 CST

@kotradov -- most of your other posts include trying to find information in the RelationalAPI, but this thread is about the WebAPI.

Are you going to utilize both systems? It seems like if you were to just utilize the RelationalAPI, you might find more success that way.

Post by: kotradov | RE: Group Information from the Relational Database

Monday, 12 Jan 2009 12:47 CST

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

Thanks,

Post by: kotradov | RE: Error at Step 4

Monday, 12 Jan 2009 10:28 CST

With that done, I was able to complete step 4. Then it went to step 5 and after about 5 minutes, the following pop up message appeared:Sorry, we had an error: The underlying connection was closed: An unexpedted error occurred on a receive.

Can you try to run it on your end if you have my login information an see if you have the issue? If not, could you send run it and send me a the Access database so I have the data and could start working with it?

Post by: chadada | RE: Error at Step 4

Monday, 12 Jan 2009 09:50 CST

okay, so it looks like that qb was assigned to the same department twice, which was causing him to come back twice in the view, hence the dupe key error. fixed the data so you can download.

Post by: kotradov | RE: Error at Step 4

Friday, 09 Jan 2009 01:51 CST

It is encountering the error on record 9732 in the QB table in the web api. The record id is 3xD0000036578.

Post by: kotradov | RE: Error at Step 4

Friday, 09 Jan 2009 01:48 CST

I tried a download again and received the same error in step 4. Please help!!!

Post by: chadada | RE: Error at Step 4

Friday, 09 Jan 2009 01:03 CST

there were some dupes in the qbgroupinformationcache table that was causing the error. removed those records for you so you should be good to go.

Post by: kotradov | RE: Group Information from the Relational Database

Friday, 09 Jan 2009 10:20 CST

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

Post by: kotradov | RE: Group Information from the Relational Database

Friday, 09 Jan 2009 10:17 CST

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

Post by: kotradov | RE: Error at Step 4

Thursday, 08 Jan 2009 02:14 CST

The full error message is:
We could not connect to the Access Database. Actual Error: The changes you requested to the table were not successfull because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Post by: dandrews | RE: QBs have red block error

Thursday, 08 Jan 2009 12:08 CST

We resolved the issue with a phone call. The records were missing a few specific QBDateTracking entries.

Codes 22, 24, 2, 6, 7 needed to be added to the records.

Post by: chadada | RE: QBs have red block error

Thursday, 08 Jan 2009 11:13 CST

Dave,
this is usually a data error. i can help you get to the bottom of this. i need a little help though. could you post some more info about the groups that you are having problems with? also, could you give your admin code so that i know which database to look in?
thx

Post by: kotradov | RE: Group Information from the Relational Database

Thursday, 08 Jan 2009 08:14 CST

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?

Post by: kotradov | RE: Error at Step 4

Thursday, 08 Jan 2009 08:04 CST

UNI

Post by: chadada | RE: Group Information from the Relational Database

Wednesday, 07 Jan 2009 04:17 CST

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?

Post by: chadada | RE: Error at Step 4

Wednesday, 07 Jan 2009 04:16 CST

kirk,
you are getting a 'Duplicate Primary ID is preventing the database from being loaded' error?

if so, what admincode?

Post by: dandrews | QBs have red block error

Wednesday, 07 Jan 2009 02:35 CST

I've imported some QBs successfully using system integrator but when I pull them up I have a big red block error that says:
-------------------------
This Record encountered a read access error

This usually means there is something wrong with the record, and correspondence might not create successfully. We have shown you the information the system was able to calculate.

Technical Information
Module: PastDueDays

-------------------------

I'm just trying to import the QBGroups and not their plans. Also, it says the first day after loss of coverage is 12/31/9999... any ideas?

Dave

Post by: kotradov | RE: Error at Step 4

Wednesday, 07 Jan 2009 11:25 CST

I am getting a similar message now as well. My case is 24386. The response I am getting from support is that it going to need a program fix to correct and they can't give me a time frame. Can you give any further information.

Post by: kotradov | RE: Group Information from the Relational Database

Wednesday, 07 Jan 2009 10:18 CST

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.

Post by: chadada | RE: Group Information from the Relational Database

Tuesday, 06 Jan 2009 03:32 CST

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.

Post by: kotradov | RE: Group Information from the Relational Database

Tuesday, 06 Jan 2009 01:56 CST

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

Post by: chadada | RE: Group Information from the Relational Database

Monday, 05 Jan 2009 01:47 CST

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.

Post by: kotradov | RE: Group Information from the Relational Database

Monday, 05 Jan 2009 10:16 CST

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

Post by: chadada | RE: Frequent WebCOBRA Access Loader Error

Friday, 02 Jan 2009 04:33 CST

J-
It appears that there were some extra records in a table that we use to cache qb info, qbgroupinformationcache. That was leading to the qb in question showing up multiple times. I removed those records, so you should be in good shape now. We will look into why this happened, but there isn't a way for you to manipulate the data.
Thx

Post by: jkubicina | Frequent WebCOBRA Access Loader Error

Friday, 02 Jan 2009 03:26 CST

I am receiving an error stating that a Duplicate Primary ID is preventing the database from being loaded. I have logged a support case and listed the name of the employee that was last loaded (Case #24655). Admin code: HRX.

If you could fix asap, that'd be great. January premiums post to the ledger balance on the first of the month, so we need to load the API today.

This seems to happen to us every other month or so. Is there a way for us to fix on our end rather than being dependent on Travis Developer Support turnaround time?

Thanks,
J.Kubicina

Post by: jkubicina | RE: Error at Step 4

Wednesday, 31 Dec 2008 03:29 CST

Post by: kotradov | Group Information from the Relational Database

Tuesday, 30 Dec 2008 12:58 CST

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.

Post by: chadada | RE: Relational API documentation?

Tuesday, 23 Dec 2008 08:42 CST

we don't have a document, but there have been a ton of questions asked here in the forums that you can search through to find answers.

Post by: kotradov | RE: Relational API documentation?

Tuesday, 23 Dec 2008 07:48 CST

I did not get a e-mail response to the post above, and was not sure if anyone else did.

Post by: kotradov | RE: Relational API documentation?

Monday, 22 Dec 2008 10:14 CST

Do you have any documentation on Table contents and table relationships now? I have a list of items I am trying to find in the Relational database, but I am not having much luck.

Post by: chadada | RE: Error at Step 4

Thursday, 18 Dec 2008 02:21 CST

Hey kirk,
Give support a call please so they can get this worked out for you.
Thanks.

Post by: jwolgamott | Automate your RelationalAPI

Wednesday, 17 Dec 2008 11:22 CST

You can now automate the downloading of your RelationalAPI. Imagine not having to request your API, but instead getting a link either in the morning or at night!

Learn more about this add-on capabilities at our blog posting: http://tinyurl.com/5zdayg

Post by: kotradov | RE: Error at Step 4

Monday, 15 Dec 2008 11:24 CST

Hello,

I am now using admin code "UNI" and I am getting the same error at step 4. Please help!!!

Thanks,
Kirk

Post by: chadada | RE: Error at Step 4

Wednesday, 26 Nov 2008 09:24 CST

there were some dates that were in an incorrect format(they were 00/00/0000 instead of null). i cleaned those up so you should be good to go.

Post by: kotradov | RE: Error at Step 4

Tuesday, 25 Nov 2008 03:33 CST

My code is "bcbsga"

Thanks

Post by: chadada | RE: Error at Step 4

Tuesday, 25 Nov 2008 03:24 CST

That normally means there is something wonky in your data and the view that gets it is giving an error. What is your administrator code so I can have a look?

Post by: kotradov | Error at Step 4

Tuesday, 25 Nov 2008 09:23 CST

I am trying to download the Access API data and it get to the 4th step, Getting QBS and I am getting a popup box stating "Sorry, we had an error: Unspecified Error in your request". If I click OK, the process runs on the Getting QBs, but after a half hour, I still don't have any progress. Have you seen this before with the API downloader to the access file?

Post by: chadada | RE: Data Researching

Tuesday, 18 Nov 2008 10:19 CST

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.

Post by: chadada | RE: Data Researching

Tuesday, 18 Nov 2008 08:45 CST

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.

Post by: chadada | RE: Daily RelationalAPI Downloads!

Monday, 17 Nov 2008 04:22 CST

Hi-
You can request a new copy at any time. The error was somewhere between WebCobra and the S3 data storage facility that is used to host your API files.
Sorry for any inconvenience.

Post by: blambrecht | RE: Request RelationalAPI Download

Friday, 14 Nov 2008 03:10 CST

Could someone check on our request that was made this morning at 9:22 AM CST - it seems to be stuck in processing.

The email address is should go to is:
bill.lambrecht@wageworks.com

Thank you,
-bl

Post by: jkubicina | RE: Daily RelationalAPI Downloads!

Friday, 14 Nov 2008 10:25 CST

Hello! I requested a copy of the relational database on Wednesday, but never received a copy. I see the download status is listed as undescribed error. Can you please research and let me know if I need to request a new copy? It was to be sent to hdurante@hrxcel.com.

Thanks.

Post by: MBENEFIEL | RE: Any Idea why?

Friday, 07 Nov 2008 12:18 CST

Had the same error. Research identified the coverage start date coming from our data source was earlier that the start date of the benefit plan defined in WebCOBRA. We're still deciding what we're going to do. But that is the answer we found.

Post by: chadada | RE: Paid Thru Date

Friday, 31 Oct 2008 04:19 CST

24 QB Paid Thru in Prior Activity

Post by: SSUser | Paid Thru Date

Friday, 31 Oct 2008 10:26 CST

I would like to add people to webcobra who are already on cobra so would like to assign them a paid thru date without having to add payments. WHich of the date fields do I need to set in order to do this? Thanks

QB Date Type Codes
Code Name
1 QB Created
2 Enrollment Form Received
3 Incomplete Enrollment Received
4 Waiver Form Received
5 Eligibility Notice Printed
6 Enrollment Period Begin
7 Payment Window Begin
8 Late Enrollment Form Received
9 QB Correspondence Package Approved
10 Employee has been Notified
11 Employee Transferred to QB
12 QB Termination
14 Premium Notice Generated
15 Late Premium Notice Generated
16 Reinstatement of QB Group
17 Manual Termination of QB Group
18 Refund Balance
19 Suspend Termination - Payment
20 Medicare Letter
21 Notification of End of Continuation Coverage
22 Enrolled
23 Eligibility Confirmation Letter
24 QB Paid Thru in Prior Activity
25 Social Security Rate Change
26 Secondary Event Wizard
27 System Integration Creation
28 Federal To State Coverage Letter Sent
29 Plan Subsidy End Date
30 Employee Inactivated
31 Employee ReActivated
32 Quick Cert HIPAA Certification Sent
33 Date Applied for TAA
34 Date Approved for TAA
35 Carrier/Plan Sponsor Notified Of Enrollment
36 Enrollment Form Post Mark Date

Post by: chadada | RE: ADD QB XML Problem ERROR

Thursday, 30 Oct 2008 10:48 CST

The message board is for more general/theoretical questions. If you have one of those, feel free to post it, but we will not dive into questions about specific errors in this forum.

Post by: blambrecht | RE: Data Researching

Thursday, 30 Oct 2008 09:30 CST

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

Post by: MBENEFIEL | ADD QB XML Problem ERROR in QB Benefit Assignment

Wednesday, 29 Oct 2008 02:19 CST

Trying to ADD using XML.

Get the following errors:
356 ERROR in QB Benefit Assignment: The Benefit Plan Costing Type ID Was Invalid For: 434 BP UDK: BMA0238 QBName: XXXX,CGroup Name: BMA023240517447 Company: BMA023
357 ERROR in QB Benefit Assignment: The Benefit Plan Type ID Was Invalid For: 434 BP UDK: BMA0238 QBName: XXXX,XXXXXXX Group Name: BMA023240517447 Company: BMA023
358 ERROR in QB Benefit Assignment: Could not retrieve Parameter BP UDK: BMA0238 QBName: SMART,JONATHAN Group Name: BMA023240517447 Company: BMA023

The XML uploaded is still there you you to inspect.
TPABenefits.

Post by: jwolgamott | RE: Access Dataloader - login failed

Saturday, 25 Oct 2008 09:43 CST

Robin --

It looks like you are running the downloader from a network location -- that's what the system.net.webpermission exception is telling us.

Try moving it to your local drive and running from there.

You'll get told explicitly that you don't have enterprise if you don't.

Post by: clarkr | Access Dataloader - login failed

Friday, 24 Oct 2008 08:06 CST

Hello,

I have an emergency request to extract detailed data from our new WebCobra system, and the fastest way looks like the sample code for the Access Dataloader. My problem is that my login is failing with the following message..

"Your login failed due to unhandled exception: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. "

I saw someone on the message board say that you need the enterprise version of WebCobra - not sure if we have this or not. Thanks.

Robin

Post by: evannielsen | RE: XML Format

Thursday, 23 Oct 2008 12:00 CST

What is the "dontterminate" attribute on the QB used for?

What would be the procedure for sending QBs if they employee is deceased or in the case of a divorce?

Post by: blambrecht | RE: Data Researching

Monday, 13 Oct 2008 05:03 CST

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

Post by: chadada | RE: Data Researching

Thursday, 09 Oct 2008 03:48 CST

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.

Post by: chadada | RE: Data Researching

Thursday, 09 Oct 2008 03:44 CST

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

Post by: chadada | RE: XML Responses

Thursday, 09 Oct 2008 03:37 CST

The response is a struct with 3 attributes:

bool Successful, string Message, string AreaOfSystem

The Successful boolean reports success or failure of the integration.

The Message string reports back any specifics about the problems or successes of the xml you passed in. (Ex. Success, Transaction Aborted, etc.)

The AreaOfSystem string reports back what part of the system had a problem with your data.

Post by: blambrecht | RE: Data Researching

Thursday, 09 Oct 2008 08:23 CST

Hello,

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

Thank you,
-bl

Post by: blambrecht | RE: Data Researching

Monday, 06 Oct 2008 12:37 CST

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

Post by: evannielsen | XML Responses

Thursday, 02 Oct 2008 09:52 CST

When sending an XML file through the system integrator you are returned a response. Is there any specs on the structure of that response?

Post by: jwolgamott | RE: XML upload error

Wednesday, 01 Oct 2008 06:30 CST

I would try adding the QB as a CSV file, and comparing the XML created. That will tell you the differences.

Also, if the CSV upload fails with the same problem, then we know it's a system problem, and you should contact support to get it diagnosed and addressed.

Post by: toby | RE: XML upload error

Tuesday, 30 Sep 2008 09:15 CST

We are adding a QB and adding the QB benefits that the QB is assigned to. It's based off the format in the System Integrator automated edits to System Integrator document posted in the IT Resources section of webcobra.com

Post by: jwolgamott | RE: xml file load

Friday, 26 Sep 2008 03:57 CST

If you login to the WebCOBRA system as a supervisor user, and go to IT Resources under Supervisor Only, it will be there.

Post by: jwolgamott | RE: XML upload error

Friday, 26 Sep 2008 03:57 CST

In your test.xml document, are you adding QBs as well as adding QB Benefit Assignments?

Another idea I have is to create a CSV (which is easier to create), upload it to the site, and then look at the converted XML. You could then compare your XML with the system XML and discover what you have wrong.

Post by: evannielsen | RE: xml file load

Friday, 26 Sep 2008 12:35 CST

Where do i find the "System Integrator Terms and Key Dictionary"

Post by: toby | RE: XML upload error

Friday, 26 Sep 2008 08:49 CST

Ok, so now I have an empty test client setup with no QB's added, and I still get the same exact error message when trying to import my test.xml file.

**ERROR** in QB Benefit Assignment: Object reference not set to an instance of an object. --> BP UDK: FLEX QBName: User 2,Test Group Name: TestUser 2 Company: TEST

I have a Benefit Plan defined as FLEX in the Key Management site.

Have any other ideas?

Post by: jwolgamott | RE: Request RelationalAPI Download

Tuesday, 23 Sep 2008 11:37 CST

No worries Bill!! I had to try downloading a couple of different ways (after making sure the file did exist) before thinking of the right-click.

Seems like out storage engine (provided by Amazon S3) is trying to stream the file out as text/XML instead of sending the right mime-type. At least, that's what I think is going on. We'll monitor and see if that continues and if we need to do something about it.

Post by: blambrecht | RE: Request RelationalAPI Download

Tuesday, 23 Sep 2008 11:35 CST

Yeah, that worked - now I feel only a little foolish for not thinking of that before....

Thanks.

Post by: jwolgamott | RE: Request RelationalAPI Download

Tuesday, 23 Sep 2008 11:31 CST

Bill --

Please try right-clicking on the link and saying "save target as"

Post by: blambrecht | RE: Request RelationalAPI Download

Tuesday, 23 Sep 2008 11:07 CST

We are getting an error when trying to download the latest API Refresh from both the email link and from the website:

An invalid character was found in text content. Error processing resource 'https://s3.amazonaws.com/relationa lapi.webcobras...

Please advise on how to proceed, thank you.

-bl

Post by: jwolgamott | RE: Request RelationalAPI Download

Wednesday, 17 Sep 2008 08:40 CST

I checked on your request, and it looks like the system auto-corrected itself on the 12th, and sucessfully completed.

Post by: jwolgamott | RE: XML upload error

Wednesday, 17 Sep 2008 07:55 CST

Toby --

The XML doc specifies that "The QBBenefitAssignment Node will only be used to ADD QBs."

What this means is that you cannot use this XML Element to add a new plan to an existing QB.

It might be possible to use the BenefitPlan node under QB, but I don't recommend trying.

The System Integrator did not anticipate this functionality. I suggest submitting an enhancement request to Support to add the ability to use the QBBenefitAssignment node for existing QBs.

Post by: toby | RE: XML upload error

Tuesday, 16 Sep 2008 02:47 CST

yes, and yes.

I believe this is what you are asking for:
BenefitPlans
QBBenefitAssignment Activity="1" BenefitPlanUserDefinedKey="BP1" CoverageLevel="2" OriginalCoverageDate="xx-xx-xxxx" AssignmentStartDate="xx-xx-xxxx" AssignmentEndDate="xx-xx-xxxx"
BenefitPlans

Post by: jwolgamott | RE: XML upload error

Monday, 15 Sep 2008 08:14 CST

You're trying to assign a benefit plan to an existing QB, right? And you're using the XML format?

Which benefit assignment element are you using? Can you post just that element in here (without any personal info, and without any brackets)?

Post by: toby | XML upload error

Monday, 15 Sep 2008 01:37 CST

Any ideas on how to resolve this error, '**ERROR** in QB Benefit Assignment: Object reference not set to an instance of an object. --> BP UDK: BP1 QBName: LastName,FirstName Group Name: FirstNameLastName Company: Company Naame TEST'? I have set the User Defined Key to BP1 in the Key Management area. At least I think I have set to the correct QB relating to the code type specified in the xml file.

Post by: jkubicina | RE: Request RelationalAPI Download

Friday, 12 Sep 2008 02:29 CST

HRXCEL requested a copy of the relational API last Friday, September 5th and it has not been delivered. It won't allow us to request another copy.

Post by: jwolgamott | RE: Getting Payment Info from the Access API

Thursday, 11 Sep 2008 03:02 CST

Sure -- QB Groups (families) make payments, and those payments are stored in the AccountingPayment table.

You'll link QBGroups.QBGroupID = AccountingPayment.DisconnectedQBGro upID

The best way to find the amount that the QB Group will be asked to pay each month is by looking at the QBGroupInformationCache table. The TotalMonthlyPremium + TotalMonthlyAdminFee are important, and the TotalMonthlyDue will be the amount they'll be asked to pay.

Note: I keep saying "asked" to pay because the payments rarely are entered on a monthly schedule and do not always match the amount asked. That's why mapping a payment to a monthly invoice is hard (although that information is in the AccountingRegister table)

Let me know if you need any more info or the theory behind the tables!

Post by: kotradov | RE: Getting Payment Info from the Access API

Thursday, 11 Sep 2008 11:04 CST

I guess first to be clear, I want to explain that we are looking for this information based on monthly payments each individual will be making.

Secondly, can you explain in more detail the relationship that exists that links the QB and the payyments in the Accounting Payment table?

Thanks

Post by: jwolgamott | RE: Getting Payment Info from the Access API

Monday, 08 Sep 2008 04:07 CST

Here's were you can find the information

Employer Group number - depends on where you add it, most likely the Company Code in Companies.Code
Member number - Likely the QB ID, although SSN or QB Group ID works. Check for QB.QBID, QB.SSN, or QBGroups.NickName
Member name - Likely QB.FirstName and QB.LastName
Bill Due Date - QBGroupInformationCache.PremiumDueD ate
Check number - AccountingPayment.TransactionIdent
Check amount - AccountingPayment.AmountPayed
Deposit date - AccountingPayment.CheckDate or AccountingPayment.DateEntered

The relationship is
Companies > QBGroups > QB

Then, QBGroups have cached information (calculated data) and payments, QBGroupInformationCache AccountingPayment

I hope that answers your question; if not, please let me what you mean by "member name" so we can locate the data for you.

Post by: kotradov | Getting Payment Info from the Access API

Monday, 08 Sep 2008 09:57 CST

Hello,
I am looking to get the following information from the MSAccess Relational API:
Employer Group number
Member number
Member name
Bill Due Date
Check number
Check amount
Deposit date

Can anyone give me the information on where this information is in the tables and what the relationship is between the tables?

Thanks

Post by: jwolgamott | RE: Files bigger than 4MB

Saturday, 06 Sep 2008 08:55 CST

You can open a case with the self-service portal, and the support rep will create an FTP box for you.

However, unless you can upload, diagnose, and fix the errors in the file yourself, you will most likely not have a good experience.

I highly recommend splitting the file into smaller chunks and upload them yourself.

Post by: SSUser | Files bigger than 4MB

Friday, 05 Sep 2008 06:23 CST

Where do we send files that are bigger than 4 MB to be loaded by support?

Post by: jwolgamott | RE: API Refresh Frequency Schedule Reset

Thursday, 28 Aug 2008 04:44 CST

Mike --

There will be an add-on module in the future that allows you to automate the download of the Relation API.

Your specific case about the RelationalAPI request just made it's way through support, and we deleted your original request, which will let you make another.

We haven't seen this situation (open ended pickup) before, so we'll add some checks in to make sure that this won't happen again.

Post by: MBENEFIEL | RE: API Refresh Frequency Schedule Reset

Thursday, 28 Aug 2008 11:35 CST

Sorry for the typo's.

Our current Relational API request has been pending for over 27 hours. We cannot request another.

Is there something wrong?

Thanks - Mike

Post by: MBENEFIEL | RE: API Refresh Frequency Schedule Reset

Thursday, 28 Aug 2008 11:25 CST

We have just recently completed most of the work on our ETL process using the Relational API (MS Access) data refreshes. Things are looking good.

However, yesterday morning we requested our daily download. The schedule log states that we still have a request pending. It has not been over 24 hours. The status states that we have a request pending. We are not allowed to request another.

We were using the WebAPI and were happy with it, save we were not getting several required data element.

After talking to your staff, we went with the Relational method. We were also told that your future releases will allow us to schedule and possibly automate the process, which how we work with most of our imports.

However, at this time we can work with a daily refresh for now. But our business users are pressuring us to load the new data. Is there a problem with the Relational API process?

Mike

Post by: jwolgamott | RE: Request RelationalAPI Download

Monday, 11 Aug 2008 12:54 CST

Mike --

Do you have a current API request that errored? If that's the case, you can initiate a new request via the website.

Post by: MBENEFIEL | RE: Request RelationalAPI Download

Monday, 11 Aug 2008 12:50 CST

Request: Please prepare a current RelationalAPI Download for BMA TPABenefits, Inc. We made corrections to the data and need to test those changes ASAP.

Please CC: the email link to TPASupport@TPAbenefits.com. That will help me document the logs.

Thanks you - Mike Benefiel, TPABenefits. 210-641-7715.

Post by: jwolgamott | RE: MAX Cobra Effective Date / Nbr Months

Thursday, 07 Aug 2008 01:58 CST

No problem ---

Check out the QBBenefitAssignments table

FirstDayAfterLossCoverage = day that qb is effective "on COBRA" (if they enroll)
DayPQBNoLongerCovered = end of Federal COBRA

Note: if you have state extensions (QBBenefitAssignments.NoLongerOnSta teCoverage) or SS Disability Extenstions (qbgroupinformationcache.SocialSecu rityEndDate), this is not the date for you.

Further Note: This is the maximum Allowed coverage period, not the time they are actually on coverage.

select distinct qbgroups.Description, QB.FirstName, QB.LastName, qbbenefitassignments.FirstDayAfterLossCoverage, qbbenefitassignments.DayPQBNoLongerCovered
from qbgroups
inner join qb on qbgroups.designatedGroupContact = qb.qbautoid
inner join qbbenefitassignments on qbbenefitassignments.qbautoid = qb.qbautoid

Hope that helps!

Post by: MBENEFIEL | MAX Cobra Effective Date / Nbr Months

Thursday, 07 Aug 2008 12:18 CST

1. We are looking for a data map to the maximum Cobra effective date, that is, a date that defines the maximum time of coverage for an individual. I thought that was documented previously, in a payment table, but could not find it.

2. We also want to calculate the Number of Months coverage by subtracting the beginning effective date of coverage from this date. I believe that date calculation was also documented, which is the correct effective date mapping for this calculation?

Can you please reply with this answer - I could not find it in the quick search.

Thank you!

Post by: jwolgamott | RE: Data Researching

Wednesday, 06 Aug 2008 07:51 CST

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).

Post by: blambrecht | RE: Data Researching

Tuesday, 05 Aug 2008 03:52 CST

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

Thank you,
Bill

Post by: jwolgamott | RE: Data Researching

Tuesday, 05 Aug 2008 10:37 CST

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.

Post by: blambrecht | RE: Data Researching

Tuesday, 05 Aug 2008 08:35 CST

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

Post by: jwolgamott | RE: All Tables via WebAPI?

Tuesday, 05 Aug 2008 07:21 CST

No problem...

GetAgeFromDOB: standard function with no real uniqueness on WebCOBRA's part.

GetQBModuleData: function will get a value out of our centralized repository for parameters. This can be replaced with a join, but this works pretty well in the VIEW.

-----------
CREATE FUNCTION [dbo].[GetAgeFromDOB]
(@DOB datetime,
@CurrentDate datetime)
RETURNS int
AS
BEGIN

DECLARE @AGE int

select @AGE = year(@CurrentDate) - year(@DOB) -
case when month(@CurrentDate) > month(@DOB) then 0
else
case when month(@CurrentDate) < month(@DOB) then 1
else
case when day(@CurrentDate) < day(@DOB) then 1
else 0
end
end
end

Return @AGE
END

------------------
CREATE FUNCTION [dbo].[GetQBModuleData]
(
@QBAutoID bigint,
@ParameterTypeID bigint
)
RETURNS varchar(256)
AS
BEGIN
DECLARE @Return varchar(256)

-- Is The Parameter a "Option Based" Item
If(Exists(Select * From ParameterOptions WHERE ParameterTypeID = @ParameterTypeID))
BEGIN
SELECT @Return = dbo.ParameterOptions.OptionText
FROM dbo.ParameterOptions INNER JOIN
dbo.ParameterModuleData ON dbo.ParameterOptions.ParameterTypeI D = dbo.ParameterModuleData.ParameterTy peID AND
dbo.ParameterOptions.OptionValue = dbo.ParameterModuleData.Parameter
WHERE dbo.ParameterModuleData.ModuleIdent = @QBAutoID AND
dbo.ParameterModuleData.ModuleTypeID=3 AND
dbo.ParameterModuleData.ParameterTypeID=@ParameterTypeID
END
ELSE
BEGIN
Select @Return = Parameter FROM ParameterModuleData WHERE ModuleIdent = @QBAutoID AND ModuleTypeID=3 AND ParameterTypeID=@ParameterTypeID
END

RETURN @Return
END

Post by: MBENEFIEL | RE: All Tables via WebAPI?

Monday, 04 Aug 2008 05:17 CST

Thanks a bunch.

I just need to determine the logic for the user defined functions:

GetQBModuleData()
GetAgeFromDOB() -- should be easy

Once I get the first one, we should be in red beans and rice.

Post by: jwolgamott | RE: All Tables via WebAPI?

Monday, 04 Aug 2008 04:07 CST

Here you go!

CREATE VIEW [dbo].[WebAPI_BenefitPlan]
AS
with StartStructure as
(
select min (BenefitRateStructures.BeginDate) as 'StartDate', BenefitPlans.BenefitPlanID
from BenefitRateStructures
inner join BenefitPlans on dbo.BenefitPlans.BenefitPlanID = dbo.BenefitRateStructures.BenefitPl anID
group by BenefitPlans.BenefitPlanID
),
EndStructure as
(
select max (
case BenefitRateStructures.EndDate
WHEN '1900-01-01 00:00:00.000' THEN '12/31/9999'
ELSE BenefitRateStructures.EndDate
END
) as 'EndDate', BenefitPlans.BenefitPlanID
from BenefitRateStructures
inner join BenefitPlans on dbo.BenefitPlans.BenefitPlanID = dbo.BenefitRateStructures.BenefitPl anID
group by BenefitPlans.BenefitPlanID
)

select dbo.WEBAPI_GetID(2, dbo.BenefitPlans.BenefitPlanID) AS ID
, dbo.WEBAPI_GetID(12, dbo.BenefitPlans.SponsorID) AS 'CarrierSponsor_ID'
, [Description] as 'Name'
, [NickName] as 'Code'
, StartStructure.StartDate
, EndStructure.EndDate
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.BenefitPlans.BenefitPlanID)) AS PagingID

from benefitplans
left outer join StartStructure on Benefitplans.benefitplanid = StartStructure.BenefitPlanID
left outer join EndStructure on Benefitplans.benefitplanid = EndStructure.BenefitPlanID

--------------------
CREATE view [dbo].[WebAPI_CarrierSponsor]
as
-- NOTE: You can't edit/save this through SQL 2005 (no SP)

with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', Sponsors.SponsorID
FROM dbo.Sponsors INNER JOIN
dbo.SystemInformationLinkTable ON Sponsors.SponsorID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 2) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY Sponsors.SponsorID
)

SELECT
dbo.WEBAPI_GetID(12, dbo.sponsors.SponsorID) AS ID,
dbo.WEBAPI_GetID(1, dbo.sponsors.CompanyID) AS ClientEmployer_ID,
Sponsors.[Name] as 'Name',
Sponsors.NickName as 'Code',
isnull(dbo.Addresses.AddressLine1, '') as AddressLine1,
isnull(dbo.Addresses.AddressLine2, '') as AddressLine2,
isnull(dbo.Addresses.City, '') as City,
isnull(dbo.Addresses.State, '') as State,
isnull(dbo.Addresses.PostalCode, '') AS ZipCode
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.sponsors.SponsorID)) AS PagingID

FROM Sponsors
Left Outer Join FirstAddress on FirstAddress.SponsorID = Sponsors.SponsorID
Left Outer join Addresses on FirstAddress.ID = Addresses.AddressID

---------------------
CREATE view [dbo].[WebAPI_ClientEmployer]
as
with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', Companies.CompanyID
FROM dbo.Companies AS Companies INNER JOIN
dbo.SystemInformationLinkTable ON Companies.CompanyID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 1) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY Companies.CompanyID
)

SELECT dbo.WEBAPI_GetID(1, dbo.Companies.CompanyID) AS ID
, dbo.Companies.CompanyCode AS CODE
, dbo.Companies.CompanyName AS NAME
, Addresses_1.AddressLine1
, Addresses_1.AddressLine2
, Addresses_1.City
, Addresses_1.State
, Addresses_1.PostalCode as 'ZipCode'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.Companies.CompanyID)) AS PagingID

FROM dbo.Companies LEFT OUTER JOIN
FirstAddress ON FirstAddress.CompanyID = dbo.Companies.CompanyID inner JOIN
dbo.Addresses AS Addresses_1 ON Addresses_1.AddressID = FirstAddress.ID

---------------------
CREATE VIEW [dbo].[WebAPI_QB_BenefitPlan]
AS

SELECT
distinct dbo.WEBAPI_GetID(15, dbo.QBBenefitAssignments.BenefitAssignmentID) AS 'ID',
dbo.WEBAPI_GetID(2, dbo.BenefitPlans.BenefitPlanID) AS 'BenefitPlan_ID', dbo.WEBAPI_GetID(3, dbo.QBBenefitAssignments.QBAutoID) AS 'QB_ID',
dbo.QBGroupInformationBenefitCache.CurrentPremium,
dbo.QBGroupInformationBenefitCache.CurrentFee,
dbo.QBBenefitAssignments.FirstDayAfterLossCoverage as 'FirstDayAfterLossOfCoverage'
,CASE dbo.benefitplans.PlanTypeID
WHEN 7 THEN dbo.qbbenefitassignments.FlatFee
ELSE NULL
END as 'LifePlanCoverageAmount'
,
CASE dbo.benefitplans.benefitplancostingtypeid
WHEN 1 THEN BenefitRateTiers.description
WHEN 2 THEN MatrixCostingMatrixInformation.[Name]
WHEN 3 THEN 'Flat Fee'
ELSE NULL
END as 'CoverageLevelDescription'

, dbo.BenefitSubsidy.SubsidyEndDate as 'SubsidyEndDate'
, dbo.BenefitSubsidy.DollarAmount as 'SubsidyDollarAmount'
, dbo.BenefitSubsidy.PercentageAmount as 'SubsidyPercentage'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.QBBenefitAssignments.BenefitAssignmentID)) AS PagingID

FROM dbo.BenefitPlans
INNER JOIN dbo.QBBenefitAssignments ON dbo.BenefitPlans.BenefitPlanID = dbo.QBBenefitAssignments.BenefitPlanID
INNER JOIN dbo.QBGroupInformationBenefitCache ON dbo.BenefitPlans.BenefitPlanID = dbo.QBGroupInformationBenefitCache.BenefitPlanID
INNER JOIN dbo.QB ON dbo.QBBenefitAssignments.QBAutoID = dbo.QB.QBAutoID
INNER JOIN dbo.QBGroups ON dbo.QB.QBGroupID = dbo.QBGroups.QBGroupID AND dbo.QBGroupInformationBenefitCache.QBGroupID = dbo.QBGroups.QBGroupID
LEFT OUTER JOIN dbo.BenefitSubsidy
ON dbo.qbgroups.qbgroupid = dbo.BenefitSubsidy.qbgroupid
AND dbo.qbbenefitassignments.benefitplanid = dbo.BenefitSubsidy.benefitplanid

left outer join BenefitRateTiers on BenefitRateTiers.TierID = QBBenefitAssignments.TierID
left outer join dbo.MatrixCosting on MatrixCosting.MatrixCostingID = QBBenefitAssignments.MatrixCostingID
left outer JOIN MatrixCostingMatrixInformation on MatrixCostingMatrixInformation.MatrixID = MatrixCosting.MatrixID

------------------------
CREATE view [dbo].[WebAPI_QB]
as

with FirstAddress as
(
SELECT MIN(dbo.Addresses.AddressID)as 'ID', QB.QBAutoID
FROM dbo.QB INNER JOIN
dbo.SystemInformationLinkTable ON QB.QBAutoID = dbo.SystemInformationLinkTable.ModuleIdent INNER JOIN
dbo.Addresses ON dbo.Addresses.AddressID = dbo.SystemInformationLinkTable.TableLinkIdent
WHERE
(dbo.SystemInformationLinkTable.ModuleTypeID = 3) AND (dbo.SystemInformationLinkTable.InformationTypeID = 1)
GROUP BY QB.QBAutoID
)
,
Terminations as
(
SELECT MAX(dbo.QBTermination.QBTerminationID)as 'QBTerminationID', QBGroups.QBGroupID
FROM dbo.QBGroups
INNER JOIN QBTermination on QBTermination.QBGroupID = QBgroups.QBGroupID
INNER JOIN QBTerminationReason on QBTerminationReason.QBTerminationReasonID = QBTermination.QBTerminationReasonID
GROUP BY QBGroups.QBGroupID
)

SELECT dbo.WEBAPI_GetID(3, dbo.QB.QBAutoID) AS ID, dbo.WEBAPI_GetID(1, dbo.QBGroups.CompanyID) AS ClientEmployer_ID, dbo.WEBAPI_GetID(3,
dbo.QBGroups.DesignatedGroupContact) AS BillingQBID, dbo.QB.LastName, dbo.QB.FirstName, dbo.QB.SSN, dbo.QB.QBID,
dbo.QBGroupInformationCache.PremiumsPaidTo, dbo.QBGroupInformationCache.TotalMonthlyPremium,
dbo.QBGroupInformationCache.TotalMonthlyAdminFee, dbo.QBGroupInformationCache.TotalMonthlyDue,
dbo.QBGroupInformationCache.AccountBalance, dbo.QBGroupInformationCache.MinimumMonthlyDue,
dbo.QBGroupInformationCache.PremiumDueDate, dbo.QBGroupInformationCache.EnrollmentDate, dbo.QBGroupInformationCache.EnrollmendEndDate,
dbo.QBGroupInformationCache.TotalDueToCompleteEnrollment, dbo.QBGroupInformationCache.SocialSecurityStartDate,
dbo.QBGroupInformationCache.EnrollmendEndDate as 'EnrollmentEndDate', dbo.QBGroupInformationCache.ExpectedPremiumsPaidThrough, dbo.GetQBModuleData(dbo.QB.QBAutoID, 169) AS DateOfBirth,
dbo.GetAgeFromDOB(dbo.GetQBModuleData(dbo.QB.QBAutoID, 169), GETDATE()) AS Age, dbo.GetQBModuleData(dbo.QB.QBAutoID, 167) AS EmployeeID,
case rtrim(dbo.GetQBModuleData(dbo.QB.QBAutoID, 172))
when '' then '01/01/1900'
else dbo.GetQBModuleData(dbo.QB.QBAutoID, 172)
end as 'HireDate',
dbo.Addresses.AddressLine1, dbo.Addresses.AddressLine2, dbo.Addresses.City, dbo.Addresses.State,
dbo.Addresses.PostalCode AS ZipCode,

case QBGroupCobraCoverageType
when -1 then 'Employee'
when 1 then 'Federal'
when 2 then 'FederalAndState'
when 3 then 'State'
when 4 then 'DeniedCoverage'
else 'NotSpecified'
END as 'ContinuationCoverageType',

case QBGroupStatusID
when 1 then 'AddedNotNotified'
when 2 then 'Notified'
when 3 then 'EnrolledPendingPayment'
when 4 then 'Enrolled'
when 5 then 'Terminated'
when 6 then 'Employee'
when 7 then 'Employee'
when 8 then 'Denial'
when 9 then 'Denial'
when 10 then 'Enrolled'
else 'NotSpecified'
END as 'Status',

isnull(QBgroupAssociations.NickName, '') as 'DepartmentCode',
isnull(QBgroupAssociations.Description, '') as 'DepartmentName',
isnull(QBTermination.EffectiveDate, '01/01/1900') as 'TerminationDate',
isnull(QBTerminationReason.Description, '') as 'TerminationReason',
dbo.GetQBModuleData(dbo.QB.QBAutoID, 173) as 'Gender',
QBTypes.Description as 'Relationship'
,isnull(dbo.GetQBModuleData(dbo.QB.QBAutoID, 170),'') as 'Title'
,isnull(dbo.DateTimeFormat(dbo.qb.CreatedOn),'01/01/1900') as 'EntryDate'
,isnull(dbo.QBGroupInformationCache.SocialSecurityEndDate, '01/01/1900') as 'SocialSecurityEndDate'
,isnull(dbo.qbevents.Description, '') as 'EventType'
,isnull(dbo.qbeventassignments.QualifyingEventDate, '01/01/1900') as 'EventDate'
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.QB.QBAutoID)) AS PagingID

FROM dbo.QBGroupInformationCache
INNER JOIN dbo.QBGroups ON dbo.QBGroupInformationCache.QBGroupID = dbo.QBGroups.QBGroupID
INNER JOIN dbo.QB ON dbo.QBGroups.QBGroupID = dbo.QB.QBGroupID
LEFT OUTER JOIN FirstAddress ON dbo.QB.QBAutoID = FirstAddress.QBAutoID
INNER JOIN dbo.Addresses ON dbo.Addresses.AddressID = FirstAddress.ID
LEFT OUTER JOIN QBGroupMembers on QBGroupMembers.QBgroupID = QBgroups.QBGroupID
LEFT OUTER JOIN QBgroupAssociations on QBgroupAssociations.QBgroupAssociationID = QBGroupMembers.QBgroupAssociationID
LEFT OUTER JOIN Terminations on Terminations.QBGroupID = QBGroups.QBGroupID
LEFT OUTER JOIN QBTermination on Terminations.QBTerminationID = QBTermination.QBTerminationID
LEFT OUTER JOIN QBTerminationReason on QBTerminationReason.QBTerminationReasonID = QBTermination.QBTerminationReasonID
INNER JOIN QBTypes on QBTypes.QBTypeID = QB.QBTypeID
LEFT OUTER JOIN dbo.qbeventassignments
ON dbo.qbeventassignments.qbautoid = dbo.qb.qbautoid
AND dbo.qbeventassignments.currentassignment = 1
LEFT OUTER JOIN dbo.qbevents
ON dbo.qbevents.qbeventid = dbo.qbeventassignments.qbeventid

------------------------
CREATE VIEW [dbo].[WebAPI_QB_Payment]
AS
SELECT dbo.WEBAPI_GetID(20, dbo.AccountingPayment.PaymentHistoryID) AS ID, dbo.WEBAPI_GetID(3, dbo.QB.QBAutoID) AS QB_ID,
dbo.AccountingPayment.AmountPayed AS PaymentAmount, dbo.AccountingPayment.DateEntered, dbo.AccountingPayment.CheckDate,
(SELECT CASE dbo.AccountingPayment.Void WHEN 1 THEN 'True' ELSE 'False' END AS Expr1) AS Void,
(SELECT CASE WHEN dbo.AccountingPayment.VoidedOn IS NULL THEN CAST('01/01/1900' AS datetime) ELSE voidedon END AS Expr1)
AS VoidedOn, dbo.AccountingPaymentMethod.Description AS PaymentMethod, dbo.AccountingPayment.Memo,
dbo.AccountingPayment.TransactionIdent
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.AccountingPayment.PaymentHistoryID)) AS PagingID
FROM dbo.AccountingPayment INNER JOIN
dbo.AccountingPaymentMethod ON dbo.AccountingPayment.PaymentMethodID = dbo.AccountingPaymentMethod.PaymentMethodID INNER JOIN
dbo.QBGroups ON dbo.AccountingPayment.DisconnectedQBGroupID = dbo.QBGroups.QBGroupID INNER JOIN
dbo.QB ON dbo.QBGroups.DesignatedGroupContact = dbo.QB.QBAutoID

GO
------------------------
CREATE VIEW [dbo].[WebAPI_QB_Correspondence]
AS
SELECT dbo.WEBAPI_GetID(0, dbo.CorrespondenceStorage.CorrespondenceStorageID) AS ID
, dbo.WEBAPI_GetID(3, dbo.CorrespondenceStorage.QBAutoID) AS QB_ID
, dbo.CorrespondenceStorage.QBAutoID as LetterTemplateTypeID
, dbo.LetterTemplateType.Name as LetterTemplateType
, dbo.CorrespondenceStorage.PrintedOn as PrintedDate
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.CorrespondenceStorage.CorrespondenceStorageID)) AS PagingID

FROM dbo.CorrespondenceStorage
INNER JOIN LetterTemplateType
ON dbo.CorrespondenceStorage.TemplateTypeID = dbo.LetterTemplateType.TemplateTypeID
WHERE dbo.CorrespondenceStorage.QBAutoID <> -1

Post by: jwolgamott | RE: Data Researching

Monday, 04 Aug 2008 04:01 CST

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.

Post by: blambrecht | RE: Data Researching

Monday, 04 Aug 2008 12:29 CST

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

Post by: MBENEFIEL | RE: All Tables via WebAPI?

Monday, 04 Aug 2008 11:28 CST

Thanks, I was able to build a view using the query you posted. Please show the others if you will.

CREATE VIEW [dbo].[View_QB_Payment]
AS
SELECT PaymentHistoryID AS ID, QBID AS QB_ID, dbo.AccountingPayment.AmountPayed AS PaymentAmount, dbo.AccountingPayment.DateEntered,
dbo.AccountingPayment.CheckDate, dbo.AccountingPayment.Void AS Void, AccountingPayment.VoidedOn AS VoidedOn,
dbo.AccountingPaymentMethod.Descrip tion AS PaymentMethod, dbo.AccountingPayment.Memo, dbo.AccountingPayment.TransactionId ent,
Row_Number() OVER (ORDER BY PaymentHistoryID) AS PagingID
FROM dbo.AccountingPayment INNER JOIN
dbo.AccountingPaymentMethod ON dbo.AccountingPayment.PaymentMethodID = dbo.AccountingPaymentMethod.PaymentMethodID INNER JOIN
dbo.QBGroups ON dbo.AccountingPayment.DisconnectedQBGroupID = dbo.QBGroups.QBGroupID INNER JOIN
dbo.QB ON dbo.QBGroups.DesignatedGroupContact = dbo.QB.QBAutoID

Post by: jwolgamott | RE: All Tables via WebAPI?

Monday, 04 Aug 2008 10:56 CST

Mike --

Just to make sure I understand -- does this give you want you want for the QB_Payment table? If it does, we can post the queries that build the WebAPI views.

Note: the WEBAPI_GetID is a function that will not be needed in the RelationalAPI.

In addition, the Paging ID becomes irrelevant.

Finally, unless you import the RelationalAPI into SQL Server, these views will be useless, so you'll need to extract their logic into SQL statements.

CREATE VIEW [dbo].[WebAPI_QB_Payment]
AS
SELECT dbo.WEBAPI_GetID(20, dbo.AccountingPayment.PaymentHistor yID) AS ID, dbo.WEBAPI_GetID(3, dbo.QB.QBAutoID) AS QB_ID,
dbo.AccountingPayment.AmountPayed AS PaymentAmount, dbo.AccountingPayment.DateEntered, dbo.AccountingPayment.CheckDate,
(SELECT CASE dbo.AccountingPayment.Void WHEN 1 THEN 'True' ELSE 'False' END AS Expr1) AS Void,
(SELECT CASE WHEN dbo.AccountingPayment.VoidedOn IS NULL THEN CAST('01/01/1900' AS datetime) ELSE voidedon END AS Expr1)
AS VoidedOn, dbo.AccountingPaymentMethod.Descrip tion AS PaymentMethod, dbo.AccountingPayment.Memo,
dbo.AccountingPayment.TransactionIdent
, Row_Number() OVER (ORDER BY dbo.WEBAPI_GetID(0, dbo.AccountingPayment.PaymentHistoryID)) AS PagingID
FROM dbo.AccountingPayment INNER JOIN
dbo.AccountingPaymentMethod ON dbo.AccountingPayment.PaymentMethodID = dbo.AccountingPaymentMethod.PaymentMethodID INNER JOIN
dbo.QBGroups ON dbo.AccountingPayment.DisconnectedQBGroupID = dbo.QBGroups.QBGroupID INNER JOIN
dbo.QB ON dbo.QBGroups.DesignatedGroupContact = dbo.QB.QBAutoID

GO

Post by: MBENEFIEL | RE: All Tables via WebAPI?

Monday, 04 Aug 2008 10:49 CST

Several months ago we wrote an application to extract data via the WebAPI. However due to several missing data elements we require for our data warehouse, we we unable to complete the project. After talking to Alan Williams and doing our own research, we decided to use the MS Access database in our ETL process. But this has proven to present other issues.

Is it possible that you would show the SQL underlying the denormalized views seen in the WebAPI? Specifically these:
BenefitPlan
CarrierSponsor
ClientEmployer
QB
QB_BenefitPlan
QB_Payment

This would save us a great deal of research and speed our implementation.

Thanks - Mike benefiel

Post by: jwolgamott | RE: Systemt Integrator instructions ?

Thursday, 17 Jul 2008 02:07 CST

For an automated process, you'll want to post the XML string using SOAP to

https://app.webcobra.com/exec/Syste mIntegrator.asmx

The method name, the only one with that web service, is ImportXMLToWebCobraResult

When using the automated method, you will need to include the node SecurityLogin AdministratorLoginCode="" AdministratorUserName="" AdministratorPassword=""

Post by: tbeaupre | Systemt Integrator instructions ?

Thursday, 17 Jul 2008 12:15 CST

Is there any documentation on this process actually works? Specifically where or how do you send the .xml file for an automated process?

Post by: jwolgamott | RE: Data Researching

Monday, 30 Jun 2008 04:29 CST

--------
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.

Post by: jwolgamott | RE: Data Researching

Monday, 30 Jun 2008 04:26 CST

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.

Post by: blambrecht | RE: Data Researching

Monday, 30 Jun 2008 02:02 CST

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

Post by: blambrecht | RE: Data Researching

Monday, 30 Jun 2008 11:02 CST

...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

Post by: blambrecht | RE: Data Researching

Monday, 30 Jun 2008 08:05 CST

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

Post by: jwolgamott | RE: XML QB load

Friday, 27 Jun 2008 02:50 CST

You'll need to both set that the QB Group is in a current status, and save the date the QB was notified.

Status:
QBGroup Name="" StatusCode="2"

Date Letter was sent:
QBDateTracking
QBDate Activity="1" QBDateTypeCode="5" Date="mm/dd/yyyy"
QBDate Activity="1" QBDateTypeCode="6" Date="mm/dd/yyyy"

The above would set the QB to "Notified, Waiting for QB Response" and store the date the Cobra Election Notice was sent (type 5)

Date type 6 is the date the Enrollment Window began for this QB.

Post by: jwolgamott | RE: XML Format

Friday, 27 Jun 2008 12:49 CST

You'll need to manually set this through the system and not through XML.

Post by: jwolgamott | RE: xml file load

Friday, 27 Jun 2008 12:49 CST

The "System Integrator Terms and Key Dictionary" lists that you should use 0 and 1 for Male / Female

so sex=1 would be female.

Post by: SSUser | XML QB load

Friday, 27 Jun 2008 10:13 CST

When loading QB's thru the XML they are all showing up as added not notified, this particular group of QB's are already on COBRA so what tag do I use to set the date they were notified?

Post by: SSUser | RE: XML Format

Friday, 27 Jun 2008 08:38 CST

But is it settable thru the XML when I load the benefit plans?

thanks

Post by: SSUser | xml file load

Friday, 27 Jun 2008 08:34 CST

I am loading QB's using the XML file format and even though I am passing sex = either male or female all Qb's are showing up as Male. Is this a known bug in the system?

Post by: jwolgamott | RE: XML Format

Thursday, 26 Jun 2008 02:50 CST

That isn't set at the QB level, but it is set at the Benefit Plan Level.

So you can view the Benefit Plan Tier Election page, and set what options the QB can elect.

For example, you can set the QB and Family to downgrade to all options, but set the QB Only to non-downgrade. Any options work.

Your QB will have these options shown to him/her based on their Tier assignment, so no work needed per QB once the one-time setup is complete.

Post by: SSUser | XML Format

Thursday, 26 Jun 2008 11:17 CST

When creating new QB's is there a way to set the election choices?

For example, if a QB currently has family coverage how do I set the election choices so that this QB could also choose QB Only, QB + Spouse etc....

Post by: jwolgamott | RE: Issues With Web API

Wednesday, 25 Jun 2008 02:58 CST

Paul --

I found the duplicate QB Group in the cache and removed him for you -- please try the download again.

Post by: jwolgamott | RE: Data Researching

Wednesday, 25 Jun 2008 02:52 CST

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.

Post by: jwolgamott | RE: Missing options in parameter section of xml

Wednesday, 25 Jun 2008 02:49 CST

You are correct, those have not been ported to the XML conversion.

If you have a need for those, I recommend filling out an enhancement request form to get them added.

Post by: pmckenna | RE: Issues With Web API

Wednesday, 25 Jun 2008 01:39 CST

Hi Jesse - I'm receiving this error again today. Please let me know if you would like me to send you the QB data, thanks.

Paul

Post by: blambrecht | RE: Data Researching

Wednesday, 25 Jun 2008 11:13 CST

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

Post by: SSUser | Missing options in parameter section of xml

Wednesday, 25 Jun 2008 10:53 CST

There are 6 categories of options that are available on the options tab under client\employers that are not listed in the parameters section of the XML:

Premium Subsidy Options
USERRA
QB and Client Login
Electronic Payments
Premium Disbursements
Automated QB Bill Payments

Are these options not settable thru the xml file load?

thanks

Post by: jwolgamott | RE: QB File Import Erroirs

Tuesday, 24 Jun 2008 03:42 CST

OK, then yeah, without seeing the file and the dataset it's hard for us to diagnose over the DevCenter. It seems localized to something with your setup or files -- I recommend calling Support to diagnose the problem.

Post by: SSUser | RE: QB File Import Erroirs

Tuesday, 24 Jun 2008 01:44 CST

SSN

Post by: jwolgamott | RE: QB File Import Erroirs

Tuesday, 24 Jun 2008 01:43 CST

I'm curious -- are you using an SSN or a QBID for your main identifier in your type 2 and type 3 records?

Post by: jwolgamott | RE: Linking Users to a Company

Tuesday, 24 Jun 2008 11:25 CST

Justin --

Yes, but since Users can have access to All (or a subset) of companies, it gets a little complicated.

Here's a query you can use that will show either All Companies or have 1 record per Company that the user has access to.

select users.userlogin, isnull(companies.companyname, 'ALL COMPANIES') from users
left outer join securityxrf on users.userid = securityxrf.userid
left outer join companies on companies.companyid = securityxrf.companyid
order by users.userlogin

Post by: jcox | Linking Users to a Company

Tuesday, 24 Jun 2008 07:36 CST

Hello,

Is there a way to link a user in the Users table to a company in the Companies table?

I see that this is possible if the user type is 3 becuase you could use the QBAutoID to join to QB and so on to join to Companies. But is there a way to link to a Company for the other user types?

Thanks,
Justin

Post by: SSUser | RE: QB File Import Erroirs

Monday, 23 Jun 2008 02:49 CST

so you need a coverage type 3 record even for the dependents?

Post by: jwolgamott | RE: QB File Import Erroirs

Monday, 23 Jun 2008 02:09 CST

No, the layout has not changed.

It sounds possible that you are not including a "PQB Benefit Plan Assignment Record - Record Type 3" record

If that's not the problem, then your issue is too specific to resolve via the DevCenter, and you should contact Travis Support at 281-496-3737.

Post by: SSUser | RE: QB File Import Erroirs

Monday, 23 Jun 2008 11:29 CST

yes the file loads when I remove the dependent records. Again the error I get on the dependent records is coverage level and assignment start date missing. I do not see these fields on the CSV file layout on your website. Has the layout changed???

Post by: jwolgamott | RE: Data Researching

Friday, 20 Jun 2008 09:16 CST

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!

Post by: blambrecht | RE: Data Researching

Friday, 20 Jun 2008 08:28 CST

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

Post by: jwolgamott | RE: QB File Import Erroirs

Tuesday, 17 Jun 2008 08:47 CST

Does your upload work without the dependent record? I would remove record type 2 (dependent) and see if it will integrate.

Post by: SSUser | RE: QB File Import Erroirs

Tuesday, 17 Jun 2008 03:15 CST

yes

Post by: jwolgamott | RE: QB File Import Erroirs

Tuesday, 17 Jun 2008 12:46 CST

Hi --

I removed the QB's name from this posting for privacy reasons.

Should I assume that the QB that you posted is a Dependent QB and not the primary QB?

Post by: jwolgamott | RE: Data Researching

Tuesday, 17 Jun 2008 12:43 CST

"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

Post by: jwolgamott | RE: Data Researching

Tuesday, 17 Jun 2008 12:38 CST

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!

Post by: SSUser | QB File Import Erroirs

Tuesday, 17 Jun 2008 11:43 CST

I am trying to load a QB csv file. I keep getting the same error on all of my dependent records:

1 - PreProcessing Error:CoverageLevel is missing: QB Assignment for QB: yyy, xxx
2 - PreProcessing Error:AssignmentStartDate is missing: QB Assignment for QB: yyy, xxx

I do not see a coverage level field on the Record type 2 (family member record) so not sure why I am getting this error?

Post by: blambrecht | RE: Data Researching

Tuesday, 17 Jun 2008 10:02 CST

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

Post by: blambrecht | RE: Data Researching

Tuesday, 17 Jun 2008 07:43 CST

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

Thank you,
Bill Lambrecht

Post by: blambrecht | RE: Data Researching

Wednesday, 11 Jun 2008 12:51 CST

This is extremely helpful, thanks again!
-bl

Post by: jwolgamott | RE: Data Researching

Wednesday, 11 Jun 2008 12:48 CST

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

Post by: blambrecht | RE: Data Researching

Wednesday, 11 Jun 2008 10:25 CST

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

Post by: SSUser | RE: Help Loading XML - not very helpful error

Wednesday, 11 Jun 2008 10:14 CST

found the problem, thanks

Post by: jwolgamott | RE: Help Loading XML - not very helpful error

Wednesday, 11 Jun 2008 09:37 CST

Based on the error, it appears your XML is not formatted validly.

The error reported is
-------
'?' is an unexpected token. The expected token is ''>''. Line 1, position 20.
-------

Sometimes the "?" is just a weird character, such as ~.

That's where I would start, seeing if you can open the document in IE and view it there, and then expanding your search using TextPad for weird characters.

Post by: jwolgamott | RE: Daily RelationalAPI Downloads!

Wednesday, 11 Jun 2008 08:41 CST

Bill -- The URL is being encrypted, which is causing some problems when you're downloading it.

We'll paste in the true URL into an Email and send it off to you and get the Encrypted taken care of within the next couple of days.

Post by: blambrecht | RE: Daily RelationalAPI Downloads!

Wednesday, 11 Jun 2008 08:30 CST

Jesse,

we are having issues where we see the download link on the API request and history page, but when we click on it we get a "Webpage cannot be displayed" error. I submitted the issue through the Travis Support e-mail Monday evening and have not recevied any response. We have some processes and reports depedent upon the data refresh we are several days behind on, as we have not been able to get any API files since last Thursday.

If there is anything you could do to assist us it would be greatly appreciated.

Thank you,
Bill Lambrecht
WageWorks, Inc

Post by: SSUser | RE: Help Loading XML - not very helpful error

Wednesday, 11 Jun 2008 08:18 CST

https://ss.webcobra.com/exec/

Post by: jwolgamott | RE: Help Loading XML - not very helpful error

Tuesday, 10 Jun 2008 09:27 CST

Hi --

What is your administrator code (URL)? ... we need that to pull up your error record and give you some hints on what's wrong with your XML file.

Post by: SSUser | Help Loading XML - not very helpful error

Tuesday, 10 Jun 2008 02:56 CST

I am trying to load my first XML file containing a new company, their benefit plans, and their rate structure. I get an error when I try and integrate the file:

A Major error was reported in WebCOBRA.com.

The following information is available:

The error was automatically reported
Date of Error: 6/10/2008 at 2:54:54 PM
Module of Error: SystemIntegratorPerform
Your error number is: 60

If you continue to receive this error please contact Travis Support

Post by: jwolgamott | RE: Data Researching

Monday, 09 Jun 2008 07:39 CST

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.

Post by: blambrecht | RE: Data Researching

Monday, 09 Jun 2008 04:40 CST

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

Post by: jwolgamott | RE: Daily RelationalAPI Downloads!

Monday, 09 Jun 2008 03:27 CST

Nick --

These types of errors are usually communication errors, and they no longer count against your daily download.

You can request a second download to be delivered.

We expect to have the transport problems solved in the short-term, but until then you can just request again.

Post by: nfroemming | RE: Daily RelationalAPI Downloads!

Monday, 09 Jun 2008 03:23 CST

There is an unspecified error under the download column. Please research and let us know how to proceed.

thanks,

Nick

Post by: jwolgamott | RE: Daily RelationalAPI Downloads!

Monday, 09 Jun 2008 03:20 CST

Nick --

If you log into WebCOBRA, you can now download the RelationalAPI without the email.

We added that functionality this weekend, and it should make the fact that SPAM filters do not like our emails a moot point.

Enjoy!

Post by: nfroemming | RE: Daily RelationalAPI Downloads!

Monday, 09 Jun 2008 03:17 CST

Can you please check on the status of a request for a copy from our production environment? The email link was not received.

nicholas.froemming@wageworks.com

Thanks,

Nick

Post by: jwolgamott | RE: Data Researching

Friday, 06 Jun 2008 10:52 CST

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.

Post by: blambrecht | RE: Data Researching

Friday, 06 Jun 2008 08:38 CST

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

Post by: jwolgamott | RE: Data Researching

Friday, 06 Jun 2008 06:11 CST

Bill --

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

Post by: blambrecht | RE: Data Researching

Thursday, 05 Jun 2008 04:21 CST

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

Post by: jwolgamott | RE: Automated data refresh via FTP?

Tuesday, 03 Jun 2008 01:36 CST

Bill --

The best route for an enhancement like this is through our support department. It'll then be considered by Travis and given a priority.

They'll also be able to direct you to the custom programming department for the ability for organization to have processes and features that aren't in the main product plan.

You can reach them at spt@travisoft.com

Post by: blambrecht | RE: Automated data refresh via FTP?

Tuesday, 03 Jun 2008 08:33 CST

Hi Jesse,

the availability of the daily API has been a great value for us. As you know our ultimate goal is to have a process which can be fully auotmated.

Now that the daily API is available is it possible for Travis to revisit the idea of a nightly/daily post (based upon your schedule and processes) to an FTP server (yours or ours) whereby we can then build a process to automate the rest on our end?

We are pretty open to whatever would fit best on your end including time schedule and file format, even a backup of the SQL Server db would work if you are open to that.

Even if at this point we can just start a conversation on this and explore some options that may be considered.

Thanks,
Bill Lambrecht

Post by: jwolgamott | RE: Daily RelationalAPI Downloads!

Monday, 02 Jun 2008 02:24 CST

Ron --

Your request was resubmitted -- You should receive it shortly.

Post by: ronald.driver | RE: Daily RelationalAPI Downloads!

Monday, 02 Jun 2008 02:19 CST

Can you please check on the status of a request for a copy from our production environment? The email link was not received.

ronald.driver@wageworks.com

Thanks,

Ron

Post by: blambrecht | RE: Daily RelationalAPI Downloads!

Friday, 30 May 2008 04:08 CST

Can you please check on the status of a request for a copy from our test environment? Log shows it completed around 3:30 but link e-mail was not received.

bill.lambrecht@wageworks.com

Thank you,
Bill Lambrecht

Post by: jwolgamott | RE: QB Status Report

Friday, 30 May 2008 02:09 CST

Kipp --

If you get your Daily RelationalAPI tomorrow (May 31), you'll be able to backtrace status changes for QBs throughout history.

This should be exactly what you're looking for, please let us know if it helps.

What we did was create a onetime process that created the timeline for all terminated QBs in the system; that process is running right now and will complete before the end of the night.

Post by: jkubicina | RE: Issues With Web API

Friday, 30 May 2008 10:50 CST

Jesse,
I'm receiving the same error today on step 4 of the load process. Would you be able to advise? Do you need me to provide the last QB that was added to the table via email?
Thanks,
Jimmy

Post by: jwolgamott | RE: QB Status Report

Friday, 30 May 2008 06:42 CST

Kipp--

Well, they'll be history for May 07, only of the people enrolled as of 5/25/08.

I think it's important to have history of terminated participants, so we're currently looking into running the process on terminated records to get their history.

I'll post here when we have some findings within the next day or so.

Post by: kippfeldt | RE: QB Status Report

Thursday, 29 May 2008 04:30 CST

Great, this should help.

So, just to make sure I understand correctly there will be no history within this table prior to 5/25/2008. Such as I won't be able to look for history for May of last year (2007)?

Thanks,
Kipp

Post by: jwolgamott | RE: Daily RelationalAPI Downloads!

Thursday, 29 May 2008 03:12 CST

It did complete around 9:30 AM --- I asked support to send you along the link to download the DB.

Post by: jwolgamott | RE: QB Status Report

Thursday, 29 May 2008 03:11 CST

Kipp --

There's a new table in the RelationalAPI database called QBGroupStatusHistory. This will track the information you're searching for for anyone who was enrolled or pending on/after 5/25/2008 (when the latest release was installed).

We just added this functionality to accomplish exactly what you were requesting.

select companies.companyname, qbgroups.description, qbgroups.nickname, qb.firstname, qb.lastname from QBGroupStatusHistory
inner join qbgroups on qbgroups.qbgroupid = QBGroupStatusHistory.qbgroupid
inner join qb on qb.qbgroupid = qbgroups.qbgroupid
inner join companies on companies.companyid = qbgroups.companyid
where
QBGroupStatusHistory.qbgroupstatusi d in (1,2,3)
and begindate <= '04/30/08'
and enddate >= '04/01/08'

order by companies.companyname, qbgroups.nickname, qbgroups.description

Post by: blambrecht | RE: Daily RelationalAPI Downloads!

Thursday, 29 May 2008 01:41 CST

Greetings - we posteed a request this morning around 8:30 CST and the status on the web page shows it was completed before 10:00 but I did not receive the link.

The e-mail address is bill.lambrecht@wageworks.com

Thank you!
Bill Lambrecht

Post by: kippfeldt | QB Status Report

Thursday, 29 May 2008 09:42 CST

Hello,

I am trying to create a report with the Relational DB data for QB Status. I can easily get to the QB status information but it only looks like it is for their current status.

Basically, I need to be able to enter a date range (04/01/2008 - 04/30/2008) and pull back the QB information (Basic info, qualifying event date, coverage begin date, QB status, etc...) at that point in time even though today they could be terminated.

Is this data available? Any help would be greatly appreciated.

Thanks,
Kipp

Post by: jwolgamott | RE: Data Researching

Wednesday, 28 May 2008 07:43 CST

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.

Post by: blambrecht | RE: Data Researching

Tuesday, 27 May 2008 03:59 CST

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

Post by: blambrecht | RE: API Refresh Frequency Schedule Reset

Tuesday, 27 May 2008 01:36 CST

Jesse - this is awesome! Actually I was not aware that is was available so thanks for posting this!

Best regards,

Bill

Post by: jwolgamott | RE: API Refresh Frequency Schedule Reset

Tuesday, 27 May 2008 01:10 CST

Bill --

You probably already know, but the daily refresh is now a reality.

Enjoy!

Post by: jwolgamott | Daily RelationalAPI Downloads!

Tuesday, 27 May 2008 01:09 CST

On Sunday, May 25th, we opened the floodgates to Enterprise WebCOBRA customers and you can now download a RelationalAPI database every day.

Not only that, it doesn't care about Time anymore; so if you forget to request a RelationalAPI until 3pm, you don't have to wait until 3pm anymore; midnight you'll be free to download again.

Also, we've received reports from customers about not receiving emails from the RelationalAPI -- please add the following email address to your white lists:
do-not-reply dot relational_api at app.webcobra.com

If the above seems gibberish to you, email Travis Support and we'll send you an easily readable email address to white list.

We hope you continue to find value in the RelationalAPI!

Post by: jwolgamott | RE: Data Researching

Thursday, 22 May 2008 04:53 CST

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.

Post by: blambrecht | RE: Data Researching

Thursday, 22 May 2008 01:23 CST

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

Post by: blambrecht | RE: Data Researching

Monday, 19 May 2008 08:54 CST

Jesse,

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

Post by: jwolgamott | RE: Data Researching

Friday, 16 May 2008 06:06 CST

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.

Post by: blambrecht | Data Researching

Friday, 16 May 2008 11:04 CST

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

Post by: tslusher | RE: Introducing the Relational API

Friday, 16 May 2008 07:18 CST

Got the email, thanks!

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 15 May 2008 04:36 CST

tslusher --

I ran through the CBIZ relational download again and you should be receiving the email shortly (within the next hour).

Post by: tslusher | RE: Introducing the Relational API

Thursday, 15 May 2008 10:19 CST

I'm having the same problem that Bill and mhowell had: I never get the email with the link to the Relational API when it is processed. Administrator code is CBIZ. Thanks!

Post by: jwolgamott | RE: Web API: QB Benefit Plan Assignments

Monday, 12 May 2008 07:45 CST

Paul --

Yep, I think that's the best course of action for now. It's likely that it was a communications error that presented itself as a lack of information (no records).

Post by: pmckenna | RE: Web API: QB Benefit Plan Assignments

Monday, 12 May 2008 04:57 CST

Hi - I did check the QB_BenefitPlan table and there was no data. I ended up redoing everything again and it worked. That being said, should I just continuet to monitor for now and post another message if the issue should resurface? Let me know, thanks.

PM

Post by: jwolgamott | RE: Web API: QB Benefit Plan Assignments

Monday, 12 May 2008 04:18 CST

Paul --

I was looking over the open code for this project, an it's possible that if there are exactly 2,000 QB Benefit Assignments being returned in the last query, it may popup this message.

Could you check the Access database and see if it did load any entries into the QB_BenefitPlan table?

Post by: pmckenna | Web API: QB Benefit Plan Assignments

Monday, 12 May 2008 12:02 CST

Hi Team - When attempting to load teh QB Benefit Plan Assignments I received the following error:

"You Have No QB Benefits in the System"

I use the Web API weekly and this is the first time that I experienced this issue. Please advise, thanks.

Paul

Post by: kippfeldt | RE: WebAPI QB_Payment data in Relational?

Saturday, 10 May 2008 02:06 CST

This will be perfect! Thanks for the help!

Post by: jwolgamott | RE: WebAPI QB_Payment data in Relational?

Friday, 09 May 2008 01:41 CST

Kipp

That info is all in the AccountingPayment, AccountingRegister, and AccountingRegisterPaymentDetails

- AccountingPayment is the static entry of payments
- AccountingRegister is the ledger; the list of what invoices and payments and how they come together
- AccountingRegisterPaymentDetails is the detailed breakdown of payments to individual line items

Here's a query that lists the QB's by payment and shows which payments are unapplied and by how much.

This should give you what you need, and I included a basic where clause for your dates if you need that.

select QBGroups.Description,QB.LastName, QB.FirstName, AmountPayed,AccountingPayment.DateE ntered,
-- lets get the sum of applied payments
(select sum (AmountAppliedToLineItemFromPayment ) from AccountingRegisterPaymentDetails ARPD where ARPD.PaymentHistoryID = AccountingPayment.PaymentHistoryID) as 'Total_Applied_Payments',
-- lets get the difference of paid applied
AmountPayed - (select sum (AmountAppliedToLineItemFromPayment) from AccountingRegisterPaymentDetails ARPD where ARPD.PaymentHistoryID = AccountingPayment.PaymentHistoryID)
from AccountingPayment
inner join QBGroups on QBgroups.QBGroupID = AccountingPayment.DisconnectedQBGroupID
inner join QB on QB.QBAutoID = QBGroups.DesignatedGroupContact
where
AmountPayed -(select sum (AmountAppliedToLineItemFromPayment) from AccountingRegisterPaymentDetails ARPD where ARPD.PaymentHistoryID = AccountingPayment.PaymentHistoryID) > 0
and AccountingPayment.DateEntered > '01/01/2008'

Post by: kippfeldt | RE: WebAPI QB_Payment data in Relational?

Friday, 09 May 2008 01:27 CST

I am trying to figure out how to query the relational database for all unapplied payments within a given date range (4/1/2008 - 4/30/2008). I need to know the QB, payment, etc...

Currently there is a Premium Receipt report within WebCobra that shows a unapplied payment total and I would like to query the relational database to find out what makes up this total.

Thanks for the help,

Kipp

Post by: jwolgamott | RE: Introducing the Relational API

Friday, 09 May 2008 07:42 CST

Bill --

Sorry, I missed the point of your question and went off on a tangent.

I just checked a system integrator payment upload, and the CreatedBy gets set to be -1 during that process.

Post by: blambrecht | RE: Introducing the Relational API

Thursday, 08 May 2008 09:03 CST

Jesse,

Sorry, my question was not very clear. Can you clarify - is the CreatedBy value of -1 only used for migrations? Or could other processes like an electronic file upload also result in a CreatedBy value of -1?

Thanks,
Bill

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 08 May 2008 12:53 CST

Bill --

I think your query may have a misplaced comma in it -- The CreatedOn values are all date stamps, and the createdBy field can be a -1 (for migrations)

You can use the CreatedOn field for your tables; it will be the date the row was inserted into the table. So for your data conversions, they will have nearly the same data; all future payments entered will have their creation date.

Post by: blambrecht | RE: Introducing the Relational API

Thursday, 08 May 2008 11:24 CST

Hello,

for records that have a CreatedBy value of -1, is that used only for "backoffice" processes like migration from Desktop, or could that also be used for records that were uploaded via an electronic file?

The reason for asking - if I want to get records (like Payments) that were made within a specific date range using the CreatedOn value results in a lot of older payments returning on the report if those CreatedOn dates fall within the date range and those records were added as part of a migration.

So is it safe to assume that the CreatedBy = -1 is only used for migrations or could other processes use -1 as well?

Thanks,
Bill Lambrecht

Post by: jwolgamott | RE: Introducing the Relational API

Wednesday, 07 May 2008 11:21 CST

Bill --

That's exactly the right approach to capture the date the QB becomes enrolled (which has no relation to their first day of being covered under the health plan and being enrolled).

For the 100% subsidy, that's a harder question. The most common way of entering a 100% subsidy is having them become 100% subsidized on the completion of the QB Add Wizard. This method will not leave an entry in the QB Date Tracking.

For these participants, your best bet is to get their Created_On date in the QBGroups table. Or, you can define their enrollment date as either their EventDate or their first day after loss of coverage. As a final alternative, you can choose their "Enrollment Window Begin" date, which is in the QB Date Tracking.

Post by: blambrecht | RE: Introducing the Relational API

Wednesday, 07 May 2008 11:11 CST

Hi,

I want to capture when a QB becomes enrolled. Currently I am derriving this from the existence of a row in the [QBDateTracking] table where DateTypeID = 22 (Enrolled),
and then using the QBGroupID to find the other information needed.

This leads me to 2 questions:

1) Is DateTypeID value 22 QBStatus of Enrolled or Enrolled with 100% Subsidy or does is this a broader range of Enrolled status?

2) Is there an alternative method for identifying when a QB becomes enrolled?

Thank you,
Bill Lambrecht

Post by: amyapplewhite | RE: How does the Access Data Loader Work?

Monday, 05 May 2008 08:42 CST

It worked - thanks!!

Post by: jwolgamott | RE: How does the Access Data Loader Work?

Monday, 05 May 2008 08:35 CST

Yep -- that's the problem. Your Admin Code isn't LAMAR, its 'LAM'

you can tell this from when you've logged into webcobra, your URL is https://{admincode}.webcobra.com

Post by: amyapplewhite | RE: How does the Access Data Loader Work?

Monday, 05 May 2008 08:28 CST

I'm using LAMAR.

Post by: jwolgamott | RE: How does the Access Data Loader Work?

Saturday, 03 May 2008 10:11 CST

It sounds like your administrator code is incorrect; which are you using?

Post by: amyapplewhite | RE: How does the Access Data Loader Work?

Friday, 02 May 2008 03:48 CST

The popup message is "Your login failed with the following message: Server was unable to process request. ---> Could not find server location for customerid 0."
Yes, I am Enterprise level.

Post by: jwolgamott | RE: How does the Access Data Loader Work?

Friday, 02 May 2008 12:43 CST

Does it popup an error message? Also, please make sure your account is an Enterprise level account with WebCOBRA.

Post by: amyapplewhite | RE: How does the Access Data Loader Work?

Friday, 02 May 2008 10:53 CST

I unzipped the file and am trying to run the Webcobra_loader.exe, but my log-in information does not work. I am using the same info that I log into WebCobra with.

Post by: amyapplewhite | RE: How does the Access Data Loader Work?

Friday, 02 May 2008 10:25 CST

My log-in and password do not work. Do I use the same info as when I log into WebCOBRA??

Post by: blambrecht | RE: API Refresh Frequency Schedule Reset

Friday, 02 May 2008 08:29 CST

Thanks Jesse - we are very much looking forward to the daily refresh and greatly appreciate Travis' responses to all our requests!

Bill Lambrecht
WageWorks, Inc

Post by: jwolgamott | RE: API Refresh Frequency Schedule Reset

Friday, 02 May 2008 07:45 CST

Bill --

We've reset your account to only care about the days and not the hours/minutes.

Hopefully this will tide you over for another 1-2 weeks. That's when we'll announce a daily download of the Relational API.

Post by: blambrecht | API Refresh Frequency Schedule Reset

Thursday, 01 May 2008 04:21 CST

Is it possible to get our refresh schedule reset so that we can make the request on Friday mornings? We were at that point, however there were some re-submissions the last couple of weeks and that has pushed up our time line now to Friday evening.

It would be nice if the weekly limit only calculated by day of week and not to the hours and minutes.

Thank you,
Bill Lambrecht
WageWorks, Inc

bill.lambrecht@wageworks.com

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 01 May 2008 08:42 CST

Bill --

The ChangedOn and CreatedOn are handled by our Stored Procedures, not necessarily the triggers.

You can trust that address changes are being tracked in by the ChangedOn table though; those are being properly tracked in the table.

If its important to tell _what_ changed, in addition to the fact that the entire record changes. The following query will show all address edits for QB addresses:

select * from qbhistorydetail
inner join QB on QB.QBAutoID = qbhistorydetail.QBAutoID
where
qbhistorydetail.PKField = 'AddressID'
and Action = 2 -- ActionID2 is Edit

Post by: blambrecht | RE: Introducing the Relational API

Thursday, 01 May 2008 07:54 CST

Greetings,

Are the ChangedOn columns in the tables updated with every update to the row automatically such as via a GETDATE() in the stored proc or a trigger on the tables themselves?

For example, we are looking to generate a report for when an address changes and was hoping that we could use the ChangedOn colummn in the Addresses table for the indicator. That would be the most straight forward approach.

If not, is there another way within the database to infer the change of address such as a letter being sent out or other event/activity captured within the database?

Thanks,
Bill Lambrecht

Post by: jwolgamott | RE: Issues With Web API

Tuesday, 29 Apr 2008 08:57 CST

Paul --

I'm glad you were able to run the API loader this morning without a problem.

We didn't do anything on our end; The problem may have been (but I can't tell for sure) consistency . It's possbile as you made the 30th API call, another user has inserted a record into page 2, which moves a QB onto your current page.

For our larger customers we move to a nightly refresh of the API data, which solves some of the consistency issues. We're rolling this out site wide within the next 1-2 weeks.

Basically, this shouldn't be a problem for you moving forward.

Post by: jwolgamott | RE: Issues With Web API

Tuesday, 29 Apr 2008 07:39 CST

PM--

The API Loader goes through several stages when getting records from the WebAPI and inserted them into Access.

Which stage are you receiving the Access Insert error?

Once we know this, you should look into that table (QB for example) and note the last record successfully inserted.

We can then diagnose why the loader is trying to insert the record twice.

(Don't post that person's information here.. Once you have the record, email it to spt@travisoft.com with a link to this page, and we can diagnose together).

Post by: pmckenna | Issues With Web API

Monday, 28 Apr 2008 04:36 CST

Hi Team - When using the WebAPI_Loader.exe\Load Access Database, I am receiving the following error:

"We could not connect to the Access Database. Actual Error: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Can you please clarify what the issue is? If the issue resides in WebCOBRA, how can I determine where the duplicate values reside? I appreciate your help.

PM

Post by: jwolgamott | RE: Introducing the Relational API

Monday, 28 Apr 2008 07:25 CST

Bill --

The field QBGroups.DesignatedGroupContact is the QB (QB.QBAutoID) in the Group who is the billing contact; usually the former employee who lost coverage.

I think you might be wanting the name of the Client/Employer personnel. This would be the name of the Contact when you view the Client/Employer detail screen.

To get this, you want to get the Contact ID from the contact table of the Client/Employer.

Here's a sample query to do so:
select * from companies
inner join SystemInformationLinkTable on SystemInformationLinkTable.ModuleId ent = companies.companyid and SystemInformationLinkTable.ModuleTy peID = 1 and SystemInformationLinkTable.InformationTypeID = 2
inner join Contacts on Contacts.ContactID = SystemInformationLinkTable.TableLinkIdent

Our conversation on http://devcenter.webcobra.com/conversations/63.aspx explains the magic constants used.

This will return all contacts, not just the top 1. Also, this does not return their contact information, but a seperate query could return that.

Post by: blambrecht | RE: Introducing the Relational API

Sunday, 27 Apr 2008 10:09 CST

Hi,
I need to locate the contact name for customers for a report. I see the field DesignatedGroupContact in QBGroups, but when I look in the Contacts and Users table the ID values do not match up at all.

Is there a different table that links DesignatedGroupContact to an account rep?

Thank you,
Bill Lambrecht

Post by: jwolgamott | RE: Introducing the Relational API

Friday, 25 Apr 2008 02:02 CST

Bill -- We'll check into why it wasn't picked up until just now, but if you check the status you'll see its now PickedUp

Since you requested it from the main non-test account of yours, it'll probably be fairly quick to get to you.

Post by: blambrecht | RE: Introducing the Relational API

Friday, 25 Apr 2008 01:57 CST

Can someone please check on the status of a request for a Relational API refresh I entered around 9:15 AM CST and have not recevied the download link as of 2:00 CST.

Thank you,
Bill Lambrecht
bill.lambrecht@wageworks.com

Post by: blambrecht | RE: WebAPI QB_Payment data in Relational?

Thursday, 24 Apr 2008 01:08 CST

This is VERY helpful! Thanks again.

Post by: jwolgamott | RE: WebAPI QB_Payment data in Relational?

Thursday, 24 Apr 2008 08:45 CST

Bill--
COBRA Expiration Date --- I'm going to assume this is the date the QB could have normally continued coverage through, if they had paid everything. Also, this would include any Social Security Extension dates.

Use QBBenefitAssignments.DayPQBNoLonger Covered ... this is a per QB per Benefit Plan date, as each QB and dependent may be on a plan that has a different COBRA End Date.

note: State continuation end date is the NoLongerOnStateCoverage in the same table.

Paid Thru Date -- use the QBGroupInformationCache.PremiumsPai dTo ... this is the actual date that premiums are paid to (not through). So a 5/1/2008 date would mean they have paid through 4/30/2008

Note: Future dates are a little tricky; there is the QBGroupInformationCache.ExpectedPremiumsPaidThrough date -- this shows what is likely to happen in the future (if rates stay the same, etc). So if I've paid 6 months into the future, my PremiumsPaidTo might only say 5/1/2008, but my ExpectedPremiumsPaidThrough would say 11/30/2008

Hope that helps!

Post by: blambrecht | RE: WebAPI QB_Payment data in Relational?

Thursday, 24 Apr 2008 08:26 CST

Greetings,

I am trying to build a report and am having trouble locating where to find the following data pieces in the Relational API:
COBRA Expiration Date
Paid Thru Date

Any assistance would be greatly appreciated!

Thanks as always,
Bill Lambrecht

Post by: jwolgamott | RE: BenefitPlan

Thursday, 17 Apr 2008 03:32 CST

Charlie --

I've uploaded an example of what it takes to add a Benefit Assignment to a QB. My example only has 1 QB, but you'll want to copy the nodes for additional QBs (because each QB has a benefit assignment)

https://devcenter.webcobra.com.s3.a mazonaws.com/Thread69-existingQB_Ad dBenefit.xml

Things to notice:
- These are the set of required fields for this task; it's longer than I would have expected, but it is what it is
- The events and addresses nodes have a type of "3" -- because they are just there for definitions only

You'll need to have the following User Defined Keys defined:
Company UDK: COMP1
QB Group UDK: QBG1
QB UDK: QBA
Benefit Plan UDK: PLAN646

One more important detail -- this was tested on the version of the software going live this weekend. I recommend proceeding on the programming front to create this XML, but don't upload and test until Monday. You will receive an error about the Addresses needing a User Defined Key prior to Monday.

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 17 Apr 2008 09:47 CST

Bill --

Thanks for letting us know -- I resubmmitted the request to the queue and it's being processed right now. It will email you when its complete; probably 1-2 hours from now.

Post by: blambrecht | RE: Introducing the Relational API

Thursday, 17 Apr 2008 09:38 CST

I requested a download from our test environment yesterday morning but did not receive the e-mail notification that it was ready.

My e-mail address is bill.lambrecht@wageworks.com

Thanks,
Bill Lambrecht

Post by: cgomez | RE: BenefitPlan

Thursday, 17 Apr 2008 08:07 CST

We are adding Benefit Plans into an existing QB. I also ran into errors when not including the address node. I get a invaild zip code. We are not making changes to the address, so I and your support team didn't think it was needed. I had it one way where the QBGroup and QB node just had the userdefinedfield only with a Activity set to 3. I had the Benefit Plan Acitivity set to 1. That gave an error of LastName is needed in the QB node, which I didn't think was needed if the Userdefinedfield is filled out with a Activity of 3. A support team member also said that I have to add the Event node as well, but that gave an error that the event already exist, even though I set the Activity to 3, 2, or 1 so I removed it. I did that based on an example of the Account Payment you had posted.

I just need an example of just adding a Benefit Plan to an existing QB with the UserDefinedField filled out.

Post by: jwolgamott | RE: BenefitPlan

Wednesday, 16 Apr 2008 04:26 CST

Charlie --

What's your use case for this? Are you adding a new plan assignment to an existing QB?

If that's the case, I'll create and post an example for you tomorrow morning. Just let me know!

Post by: cgomez | BenefitPlan

Wednesday, 16 Apr 2008 10:38 CST

Is there any examples of the BenefitPlan node. We are currently using the QBBenefitAssignment, so I can match up values from that node to the BenefitPlan node. It is the other ones that I can't. What are the optional and required fields. For example, where do I get the RateStructureID information or is even needed. I do need to know where I can get the information on the required fields that I cannot match.

So I am asking where is this information?

Post by: jwolgamott | RE: Simple Query

Tuesday, 15 Apr 2008 08:30 CST

Charlie --

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

Post by: cgomez | RE: Simple Query

Tuesday, 15 Apr 2008 02:37 CST

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

Post by: jwolgamott | RE: Simple Query

Friday, 11 Apr 2008 01:46 CST

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.

Post by: cgomez | RE: Simple Query

Friday, 11 Apr 2008 10:33 CST

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.

Post by: jwolgamott | RE: Introducing the Relational API

Friday, 11 Apr 2008 09:14 CST

Bill --

Yes, records can and will be deleted; in fact, the [DELETED] column has been deprecated in our codebase, but still exists for legacy queries on our end.

It can be safely ignored in all cases.

Post by: blambrecht | RE: Introducing the Relational API

Friday, 11 Apr 2008 09:12 CST

Are there ever times where records may be physically deleted from tables as opposed to just having the [DELETED] column updated?

Just wondering so that we can make sure our processes are accounting for that.

Thanks!
Bill Lambrecht

Post by: jwolgamott | RE: Simple Query

Tuesday, 08 Apr 2008 02:22 CST

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.

Post by: jwolgamott | RE: All Tables via WebAPI?

Tuesday, 08 Apr 2008 02:20 CST

Jimmy --

Adding these tables would be possible.

If you'd like to see these tables added, please contact support and fill out an enhancement request form.

The enhancement team meets regularly and you'll be able to get an idea of if this would be added to the WebAPI.

Post by: jkubicina | RE: All Tables via WebAPI?

Tuesday, 08 Apr 2008 12:15 CST

Would you be able to add the Disbursements table, as well as the table that contains Insufficient Funds Fees?

These fields are needed for ongoing automated audits that are performed and the WebAPI would be the most effective and up-to-the-minute method to pull this information...

Thanks,
Jimmy Kubicina

Post by: cgomez | RE: Simple Query

Tuesday, 08 Apr 2008 09:25 CST

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?

Post by: blambrecht | RE: Relational API documentation?

Tuesday, 08 Apr 2008 08:04 CST

Jesse,

thanks again for timely responses to my questions. Your help is always greatly appreciated!

-bl

Post by: jwolgamott | RE: Relational API documentation?

Tuesday, 08 Apr 2008 08:01 CST

Bill --

Here you go for the SystemInformationLink Table:

InformationLinkID - AutoID
ModuleTypeID - (Company, QB, Customer, User [values in my post in this thread on 03/26/2008 08:03 AM])
InformationTypeID - (Address, contact, email [values below])
ModuleIdent - Auto ID of the table (QB for ModuleTypeID QB, etc)
TableLinkIdent - Auto ID of the contact table (Addresses for address)

InformationTypeID
1 - Address
2 - Contacts
3 - Phones
4 - Email Address

Note: Contacts themselves can have contact information... The idea here is that a Company can have company addresses, and personnel (both are information for the company). The personnel can then have an email and address of their own.

So, a QB's address would be linked in this table in the following way:
QB.QBAutoID = 35676
Addresses.AddressID = 232

InformationLinkID - PrimaryKey
ModuleTypeID - 3
InformationTypeID - 1
ModuleIdent - 35676
TableLinkIdent - 232

Post by: jwolgamott | RE: Relational API documentation?

Monday, 07 Apr 2008 04:45 CST

Bill --

QB Groups Table: the most common use of the QB Groups table is a family. Since each participant under a plan (primary or dependent) has equal rights, each QB has a benefit assignment to them.

I'd recommend

Companies -> Sponsors -> Benefit Plans -> QB Benefit Assignments -> QB

If you need to only show the Billing contact, let me know.

I'll get the Information link IDs for you by tomorrow.

Post by: blambrecht | RE: Relational API documentation?

Monday, 07 Apr 2008 12:58 CST

In the SystemInformationLink table, is there a way to get a list of each ID and a description for the values in column [InformationTypeID]?

Thank you,
Bill Lambrecht

Post by: blambrecht | RE: Relational API documentation?

Monday, 07 Apr 2008 11:58 CST

Jesse,

can you elaborate on what the QBGroups table data represents?
I am trying to get benefit plans by company and I beleive there is some link within the QBGroups table, but would like to know more detail about that tables' function.

Thank you,
Bill Lambrecht

Post by: jwolgamott | RE: WebAPI QB_Payment data in Relational?

Friday, 04 Apr 2008 03:08 CST

Kipp --

Well it all depends ... I'll go over both scenarios.. In each I started from the QBGroups table rather than the QB table.

1) Simple listing of Payments
select * from QBGroups
inner join accountingpayment on accountingpayment.DisconnectedQBGro upID = QBGroups.QBGroupID

2) Breakdown of payments vs premiums (what I bet you want)
select * from qbgroups
inner join accountingregister on accountingregister.qbgroupid = qbgroups.qbgroupid
inner join AccountingRegisterPaymentDetails on AccountingRegisterPaymentDetails.Ac countingRegisterID = accountingregister.AccountingRegisterID

#2 will actually show you what payment pays for what portion of what line item -- This will be useful to see Which payments paid for the AdminFee of February (for example)

The accountingpayment table is simply the list of payments and the accountinginvoice table is a list of invoices. How each payment pays for each invoice (any many, many combinations thereof) is calculated in the AccountingRegister table.

Hope that helps!

Post by: kippfeldt | WebAPI QB_Payment data in Relational?

Friday, 04 Apr 2008 12:59 CST

Hello,

I am trying to find the relational API data that would be the same data that comes over in the WebAPI QB_Payment table. The fields I am specifically looking for are DateEntered and Void (names from WebAPI data). I have found a few payment tables within the relational data but unsure how to make the linking (starting with the QB table).

Any help would be great.

Thanks,
Kipp

Post by: jwolgamott | RE: Automated data refresh via FTP?

Monday, 31 Mar 2008 04:26 CST

Jimmy--

We are working towards a solution to enable a less-restrictive Relational API policy.

We'll post it here when we've completed our testing. Based on current results, we'll be able to offer:

* Daily downloaded of the Relational API
* Not be so restrictive about the exact 24 hour nature

Post by: jwolgamott | RE: All Tables via WebAPI?

Monday, 31 Mar 2008 04:25 CST

Jimmy --

At this time, we have no plans to expand the WebAPI to pull all tables -- it exists as a de-normalized view with the most frequently access information.

We do plan on adding fields and tables to the WebAPI (as we did with the Spring '08 release).

The Relational API contains all tables and all information.

Post by: jwolgamott | RE: Missing Web Services Data

Monday, 31 Mar 2008 04:23 CST

Jimmy --

We had to change the WebAPI to only pull 2,000 records at a time in order to pull for our very largest customers.

The code in the sample WebAPI project demonstrates how to pull more records. You basically loop and keep pulling records until it is complete.

Post by: jwolgamott | RE: QBAutoID Column in QB table

Monday, 31 Mar 2008 04:21 CST

Bill --

QBAutoID in the QB table is an autogenerated ID from the production DB... It will be constant each time the Relational API is created.

We actually don't have any fields that will change with each Relational API creation.

Post by: jkubicina | All Tables via WebAPI?

Monday, 31 Mar 2008 02:16 CST

Hello,

Currently the WebAPI (or web services portion of WebCOBRA), only allows us to obtain data from a few limited tables. It has been suggested that the WebAPI is intended for "quick answers" or for a telephony integration. Are there any plans to expand the WebAPI to make *all* tables available for our programmers to utilize via web services?

That'd be great.

Thanks,
Jimmy Kubicina

Post by: jkubicina | RE: Automated data refresh via FTP?

Monday, 31 Mar 2008 02:10 CST

Hi,
Just wondered if there has been any progress on the question above?
Thanks again,
Jimmy Kubicina

Post by: jkubicina | Missing Web Services Data

Monday, 31 Mar 2008 02:06 CST

Hello,

One of our programmers ran a web services query off of our WebCOBRA data and was only able to pull 1999 out of 10,000 QBs. The system responded to his web services query and only provided employers that begin with "A" through "I" (20 out of 55 employers).

Why would this problem be occurring? If you would like to further discuss the details of the specific clients that are missing, we can move this conversation offline...(the Travis Support reps forwarded me to this forum for web services support).

Thanks for any assistance,
Jimmy Kubicina
jkubicina@hrxcel.com

Post by: blambrecht | QBAutoID Column in QB table

Monday, 31 Mar 2008 02:01 CST

Hello,

Is the QBAutoID column an identity or other generated number within the production database that is coppied when to the Access database, or is it regenerated each time the Relational API Access db is created?

Thank you,
Bill Lambrecht

Post by: jwolgamott | RE: Simple Query

Friday, 28 Mar 2008 03:38 CST

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)

Post by: cgomez | RE: Simple Query

Friday, 28 Mar 2008 10:28 CST

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

Post by: jwolgamott | RE: Simple Query

Friday, 28 Mar 2008 07:47 CST

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.

Post by: cgomez | RE: Simple Query

Thursday, 27 Mar 2008 04:06 CST

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.

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 27 Mar 2008 03:52 CST

Bill -- I'm having the system regenerate the RelationalAPI -- it's just picked up the job now, so it should email out to you within an hour.

Post by: blambrecht | RE: Introducing the Relational API

Thursday, 27 Mar 2008 03:48 CST

Jesse,

I requested a copy yesterday but did not get the e-mail notice that it was ready. I e-mailed the support group and was told to use the message board to to request the link be resent.

Thank you,
Bill Lambrecht - WageWorks, Inc
bill.lambrecht@wageworks.com

Post by: jwolgamott | RE: Relational API documentation?

Thursday, 27 Mar 2008 12:52 CST

Kipp --

I did some precursory checking, and I can't find where the system would be switching the two values.

Looking at the amounts, its obvious that the premium really is 828.44 and the fee is 16.57.

In my examples, that's how its coming across in both the RelationalAPI and the WebAPI.

The QBGroupInformationBenefitCache in the RelationalAPI will be the system of record -- use that if theres a disparity. To answer your specific question, the RelationalAPI is right, and yes, to show current premium costs the easiest way is the QBGroupInformationBenefitCache.

However, can you send over the query you're using in the WebAPI to show the Fee being 828.44? Just send it to spt@travisoft.com and include a link to this page and it'll get to me.

Thanks!

Post by: cgomez | RE: Simple Query

Thursday, 27 Mar 2008 12:41 CST

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

Post by: jwolgamott | RE: Simple Query

Thursday, 27 Mar 2008 12:41 CST

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!

Post by: kippfeldt | RE: Relational API documentation?

Thursday, 27 Mar 2008 12:24 CST

Thanks for the info.

I have another question and it deals with the possible difference between the Relational API and WebAPI.

Within the WebAPI "QB_BenefitPlan" table there are two fields called CurrentPremium and CurrentFee, and within the RelationalAPI "QBGroupInformationBenefitCache" table the same fields are there, but it looks like the values are reversed.

example:
WebAPI CurrentFee = 828.44 CurrentPremium = 16.57
Relational API CurrentFee = 16.57 CurrentPremium = 828.44

I could be just looking at the wrong table to get this information, but not sure. We use these two fields within the WebAPI, so I want to make sure that we continue to use them correctly and that I need to reserve the values, look in another table, or wait for a table fix for the Relational API.

Thanks for any help,

Kipp

Post by: cgomez | RE: Simple Query

Thursday, 27 Mar 2008 10:06 CST

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.

Post by: jwolgamott | RE: Simple Query

Thursday, 27 Mar 2008 09:32 CST

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)?

Post by: cgomez | RE: Simple Query

Thursday, 27 Mar 2008 08:40 CST

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.

Post by: jwolgamott | RE: Simple Query

Wednesday, 26 Mar 2008 09:10 CST

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.

Post by: cgomez | RE: Simple Query

Wednesday, 26 Mar 2008 04:36 CST

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

Post by: cgomez | RE: Simple Query

Wednesday, 26 Mar 2008 11:07 CST

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

Post by: jwolgamott | RE: Simple Query

Wednesday, 26 Mar 2008 10:52 CST

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.

Post by: cgomez | RE: Simple Query

Wednesday, 26 Mar 2008 09:18 CST

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.

Post by: jwolgamott | RE: Relational API documentation?

Wednesday, 26 Mar 2008 08:03 CST

Both Contact information and what we call parameters (gender, Date of Birth) are centralized. Client/Employers and QBs both have addresses, so there is only one address table.

Benefit Plans and QBs both have parameters, so there is one ParameterModuleData table.

To get contact information:
select * from qb
inner join SystemInformationLinkTable on SystemInformationLinkTable.ModuleId ent = QB.QBAutoID and SystemInformationLinkTable.ModuleTy peID = 3 and SystemInformationLinkTable.InformationTypeID = 1
inner join Addresses on SystemInformationLinkTable.TableLinkIdent = Addresses.AddressID

To get the parameter information for a QB with the fake SSN of 555-55-5555

select parametertypes.ParameterName, parametermoduledata.Parameter, qb.* from qb
inner join parametermoduledata on parametermoduledata.moduleident = qb.qbautoid and parametermoduledata.moduletypeid = 3
inner join parametertypes on parametertypes.parametertypeid = parametermoduledata.parametertypeid
where qb.ssn = '555-55-5555'

You can see the ModuleTypeID exists in both queries... here's the types:
Company: 1
Benefit Plans: 2
QB: 3
Contacts (personnel): 4
Customer (TPA): 5
Users: 8
Departments: 10
Brokers: 15

Post by: jwolgamott | RE: Simple Query

Tuesday, 25 Mar 2008 07:42 CST

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.

Post by: cgomez | Simple Query

Tuesday, 25 Mar 2008 04:35 CST

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.

Post by: kippfeldt | RE: Relational API documentation?

Tuesday, 25 Mar 2008 03:15 CST

Thanks for your reply.

Currently I am trying to find the linking for addresses (Adresses table) and don't see any place within the QB tables to get to this information. I am also trying to find general QB data (gender, DOB, age, etc...) and cannot find this information either.

I downloaded the relational API on 3/4/08, so I am downloading again in case there have been changes/fixes added to it.

Thanks,
Kipp

Post by: jwolgamott | RE: Relational API documentation?

Tuesday, 25 Mar 2008 02:15 CST

Kipp --

At the current time we don't have an Entity Relationship Diagram (or a different version) to provide.

The main tables are the same hierarchy however:
Customer > Company > Sponsor > BenefitPlan
and
Customer > Company > QB Groups > QB > QBBenefitAssignments

We can answer how specific tables relate to each other though -- just let us know which you're wondering about!

Post by: jwolgamott | RE: SecurityLogin

Tuesday, 25 Mar 2008 02:13 CST

I can understand why you were confused on that! I had to look it up myself.

The Core Creditable Coverage fields are only used when adding current Employees.

When adding a DOL Employee through the system, it will ask you if you want to set the Core Creditable Coverage date for the employee. If it is set, then the "COBRA Coverage Confirmation" will print out along with the COBRA General Notice, which is the DOL Notice to newly covered employees.

The fields below only apply to DOL Employees:
MonthsOfCoreCreditableCoverage="" DaysOfCoreCreditableCoverage="" CoreCreditableCoverageStartDate="" MonthsOfPreExistingConditionMaximum Exclusion="" DaysOfPreExistingConditionMazimumEx clusion=""

Bottom line -- just remove the attribute completely if you're adding or manipulating QBs.

Hope that helps!

Post by: kippfeldt | Relational API documentation?

Monday, 24 Mar 2008 10:39 CST

Hello,

I was looking for the documentation for the Relational API database. I have looked within the IT resources section and read through other postings and can't seem to find what I need. I am looking for information on how the tables relate (join) to each other and specific definitions/terms for fields within the database tables.

The WEB API had relationships set up so it was easy it figure out how the tables related to each other, but this API does not.

Any help would be great, so I can start developing.

Thanks,
Kipp

Post by: dpbarbella | RE: SecurityLogin

Monday, 24 Mar 2008 09:30 CST

The sample 'Add QB Groups' shows attributes with values of {EmptyString} -> i.e. MonthsOfCoreCreditableCoverage=""

- What happens if these attributes are left out of the posting XML (as with null)?
- Do they have default values (ints & dates etc.)?

Thanks,
Dan

Post by: jwolgamott | RE: SecurityLogin

Monday, 10 Mar 2008 08:44 CST

At this time we don't have one -- but I think it's an excellent idea.

It'll be on our list of resources to make available to developers.

The best thing we have now are the examples in IT Resources.

Post by: dpbarbella | RE: SecurityLogin

Monday, 10 Mar 2008 08:37 CST

Can you supply us with an XSD schema for your import xml?

Post by: jwolgamott | RE: SecurityLogin

Monday, 10 Mar 2008 08:18 CST

It depends on what you're trying to do. The most used case is adding a Qualified Beneficiary. When doing that, you'll need keys for:

Client/Employer (Company)
Sponsor(s)
Benefit Plan(s)
Departments (if using)

Post by: dpbarbella | RE: SecurityLogin

Monday, 10 Mar 2008 08:16 CST

ERROR: Module Company threw error: Edit Mode requires a User Defined Key!

Do ALL WebCobra records require a UDK for the service to work?

Post by: jkubicina | RE: Automated data refresh via FTP?

Monday, 10 Mar 2008 07:34 CST

Hello Jesse,

The "once per week" is a severe limitation for our situation. And, honestly, even if "enhancements" were made and the Relational API became available twice per week or even three times per week, that would still not be sufficient for our needs. What we are needing is "live" full data -- every day, every hour. Is there any discussion surrounding this need?

Thanks,
Jimmy Kubicina
HR XCEL, LLC

Post by: jwolgamott | RE: Automated data refresh via FTP?

Monday, 10 Mar 2008 07:24 CST

Jimmy --

Yes, the Relational API was designed with your situation in mind. Full, complete data.

The WebAPI is designed for instant answers, for example connecting a telephone IVR system, or integrating with a website. It's also easier to use and report on.

When you need to dive deep into the data, you'll want the Relational API.

You did ask if it was "as often as you want it" -- there is currently a limitation on the Relational API of once per week. You can read more about our plans for that in previous posts on this thread, but the once per week won't last forever.

Post by: jkubicina | RE: Automated data refresh via FTP?

Friday, 07 Mar 2008 01:17 CST

P.S. Wasn't intentionally trying to use your "As you can imagine" intro line... It just came out the same.

Post by: jkubicina | RE: Automated data refresh via FTP?

Friday, 07 Mar 2008 01:13 CST

Hello Jesse,
As you can imagine, because we are a third-party administrator, it is absolutely imperative that we have full, fresh data to run reports/recons for our clients everyday... So, I wondered: the Web Services portion of WebCOBRA used to only allow us to connect to a very limited number of tables; now that the "full relational API" has been released, are we now able to connect to ALL tables via Web Services? If so, this would give us "full, fresh data" as often as we want it...
Thanks,
Jimmy Kubicina
HR XCEL, LLC

Post by: jwolgamott | RE: Introducing the Relational API

Friday, 07 Mar 2008 10:25 CST

The WebAPI could be documented without much work -- the code is all there, so you could automate the queries, and even the loading of a Database.

That could either be a Windows Service, or a console application that you use a Windows Scheduled Task to download the information you need.

Regarding the documentation on the Relational API -- if you go into the IT Resources section, you'll see information on how to use the Relational API.

If you have specific questions on tables and queries, please open up a new thread in the Relational API forum (http://devcenter.webcobra.com/foru ms/5.aspx)

Post by: cgomez | RE: Introducing the Relational API

Friday, 07 Mar 2008 10:21 CST

Where is the documentation to use this Relational API? We are currently using the Simple Query to pull the information on a daily basis. We are looking for a automation solution. We cannot have a manually process for someone to run the WebAPI on a daily basis. Do you have any examples to the Relational API, so that we know how to use it?

Post by: mhowell | RE: Introducing the Relational API

Thursday, 06 Mar 2008 03:44 CST

Got it, thanks!

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 06 Mar 2008 03:26 CST

I had the system regenerate a new copy of the Relational API and it shows it emailed you earlier today.

If you didn't receive it, please email spt@travisoft.com -- and paste in this URL, and we'll email you out the link you need to download it.

Post by: mhowell | RE: Introducing the Relational API

Thursday, 06 Mar 2008 01:34 CST

Administrator code is HRX

Thanks...

Post by: jwolgamott | RE: Automated data refresh via FTP?

Thursday, 06 Mar 2008 01:25 CST

As you can imagine, the creation of a full backup is a processor intensive activity. Our current plan is to monitor the usage of the Relational API for a period of 1-2 months, and then make adjustments on the frequency limit.

One thing to consider would be a nightly process that you write, connecting to the WebAPI and do an update on current QB statuses. That is, of course, if that was your concern.

Post by: jwolgamott | RE: Introducing the Relational API

Thursday, 06 Mar 2008 11:29 CST

Sure! I just need to know your Administrator Code and I can track it down for you.

Post by: mhowell | RE: Introducing the Relational API

Thursday, 06 Mar 2008 11:27 CST

We requested a copy of our database on 3/2/08 but never received the email with the link to download. Is there a way to request this again, instead of going through the 7 day waiting period?

Post by: blambrecht | Automated data refresh via FTP?

Thursday, 06 Mar 2008 09:24 CST

For our reporting needs we would ideally like to get a data set refresh on a daily basis. Would it be possible to explore the potential of an automated process where the Relational API file is created nightly and either sent to our FTP site or we were able to pull it from an FTP site?

Thank you,
Bill Lambrecht
WageWorks, Inc

Post by: blambrecht | RE: Introducing the Relational API

Wednesday, 05 Mar 2008 02:28 CST

Hi Jesse,

The steps you have outlined - using various versions of SQL Server .mdf files - would definitely work for our company as an solution to the 2GB Access limit.

In the future would you be willing to consider utilizing compressed SQL Server backup files such as with a tool like LiteSpeed to control file sizes and download times?

Thanks for your feedback,
Bill

Post by: jwolgamott | RE: Introducing the Relational API

Wednesday, 05 Mar 2008 10:13 CST

Bill --

We took the 2GB limit on Access files into account when we decided on what datastore to use; Access was chosen because of the network effect ("everybody" has Access)

We also wanted to have a path to go down when we reach the 2GB limit. The techniques we're using to build and populate the Access Database could be ported over and create a SQL Server 2005 Express database file. We'd get up to 4 GB there. And we could automate a full SQL Server instance when we get above that.

Based on our calculations, we'll be able to store 196,000 QBs in a 2GB Access File.

We can do this because the Access file has no indexes or binary data (PDF storage).

Other techniques we can use if/when we approach the 400,000 QB barrier (in a 4 GB Express file) is to shard the database into several instances.

So, in conclusion, I think you're right to consider the 2GB a limitation, but it is in the plans to be able to handle it in the next 2-5 years.

Post by: blambrecht | RE: Introducing the Relational API

Tuesday, 04 Mar 2008 07:54 CST

We are very pleased to see this new feature in place. One issue that our company will likely face as we migrate more clients to WebCobra is the 2GB size limit for Access database files.

Ideally, one way to resolve this would be if it were possible that the Access file would only contain records that were added and updated since the previous download.

Most of the tables have what would appear to be audit columns of inserts and edits. If that assumption is correct, would that be a feasible accommodation that could be made in the near future?

Bill Lambrecht
WageWorks, Inc

Post by: jwolgamott | RE: SecurityLogin

Monday, 03 Mar 2008 03:30 CST

Thanks for including your solution to the DevCenter --

The problem may have ended up being an InnerText vs OuterText situation. So the string containing the XML, as received by the WebCOBRA webservice, did not contain the outer WebCOBRA node.

Post by: dpbarbella | RE: SecurityLogin

Monday, 03 Mar 2008 03:23 CST

Thanks for the code snippet...

The problem with my code was using the 'CreateElement' method to create the XmlElement. I used the 'DocumentElement' property per your example and same code works fine.

Post by: jwolgamott | RE: SecurityLogin

Monday, 03 Mar 2008 12:37 CST

I received the XML you sent to spt@travisoft.com, and when using it I don't receive the same message you're receiving.

I receive that the credentials aren't valid, which of course they are not because of the XXXX on the password.

I've uploaded the XML uploader that I used to validate the service.

http://devcenter.webcobra.com.s3.am azonaws.com/XMLLoader.zip

You will probably need to strip out and spaces in the link --

When I used your XML and a username with a space in it, it worked perfectly.

Would you confirm for me that you still get the "not supplied" error?

The next step will be for us to get your actual password, but I'd like to avoid that if possible.

Post by: jwolgamott | RE: SecurityLogin

Monday, 03 Mar 2008 11:10 CST

Do you feel comfortable posting a link to your XML that you're sending in? Make sure you XXXXX out your password.

Or, you can just email the XML document to spt@travisoft.com --- paste in the link to this thread and it'll get sent over to me and I can take a look.

It seems very likely that the formatting is slightly off of the XML document, so we should get an answer to you very soon.

Post by: jwolgamott | Introducing the Relational API

Monday, 03 Mar 2008 09:17 CST

With the Spring 2008 release of WebCOBRA, we introduced a feature that should go above and beyond what you've been asking for in the WebAPI.

The Relational API is a full and complete copy of your WebCOBRA.com database.

We've optimized it to reduce size, and ported it to Access (We use SQL Server), but its all fields.

You can download it through the WebCOBRA IT Resources; you'll receive an email with the link within a couple of hours.

Some Terms and Conditions for this feature:
1) It's not as easy to use as the WebAPI. This is a relational database, so your data is spread across multiple fields
2) You can download a copy once per week
3) All support for the Relational API will occur here, on the DevCenter.
4) Letters and other binary images are not downloaded

We are extremely excited about this new feature -- it's entirely unique in our space, and will give you access to every piece of data you could need.

Post by: jwolgamott | Upgrades to Data Downloader

Monday, 03 Mar 2008 09:10 CST

We've added several fields and an entirely new table to the Data Downloader.

You can view the field listing over at http://devcenter.webcobra.com/web_a pi_field_listing.aspx

Most importantly, you can now download the QB_Correspondence table that lists out when each letter was printed for each QB.

You will need to download the new version of the WebAPI Data Downloader in the Sample Code section.

We're pretty excited about this version, and hope you can make use of the new fields.

Post by: jwolgamott | RE: Data Loader -- all data

Monday, 03 Mar 2008 09:05 CST

For us to be able to diagnose the issue, we'll need to have your login information and an example of the QB that isn't downloading.

This forum is not the best place for that -- please email spt@travisoft.com with that information (paste in this devcenter conversation link as well), and we'll hopefully be able to diagnose for you.

One thing to make sure of -- the QB should be in Access tables themselves -- sometimes if you write custom reports or queries they will exclude records even though they are really there.

Post by: jwolgamott | RE: Any Idea why?

Monday, 03 Mar 2008 09:02 CST

All QBs have to be assigned to a plan when imported into the system.

Based on the error message, the system is having trouble finding the correct time segment to attach the QB to.

Things to check for include:
1) Make sure the QB First Day after Loss of Coverage (or possible event date) is not before the Benefit Plan Start Date
2) the plan that MEDICAL points to has not ended

Those have been the only setup problems that customers have had when getting the "Rate Structure" error.

Hope that helps!

Post by: dpbarbella | RE: SecurityLogin

Monday, 03 Mar 2008 08:03 CST

Tried the 'Do Nothing' version of the XML with new results...

Error: /WebCobra/SecurityLogin/@Administra torLoginCode not supplied

However I have supplied the correct authentication and the XML structure appears fine.

Post by: jsterling | RE: Data Loader -- all data

Monday, 03 Mar 2008 06:08 CST

Jwaolgamott

This still doesn't appear to be working correctly. I download data via the access data downloader and not all QB's are being downloaded. For example, when I run a report in WEBCOBRA and view all enrolled for one of my clients, not all of those QBs are being downloaded into the QB table. Please advise, this is causing serious issues with our ability to report to our clients.

Thank you.

Post by: HRIS | Any Idea why?

Friday, 29 Feb 2008 02:43 CST

After uploading an Integration XML File and then pressing the "Integrate Selected Files" button, I received the following Integrate Message, (Module QB Benefit Assignment threw error: Unable To Find Rate Structure: BP UDK: MEDICAL QBName: XX Group Name: XX Company: XX)

The file I am integrating do not have a rate structure named BP UDK nor does WebCobra, so why is the system looking for it. Any ideas? ...

XX equals the employee and company name

Post by: jwolgamott | RE: WebCobra Tables

Wednesday, 27 Feb 2008 10:35 CST

You'll want to use the DateTracking node that is a child of the QB Groups element to track dates -- its in the XML Format, but I agree that it could use some documentation as to its purpose.

For QBID -- the max length for QBID is 255 characters.

You can safely assume that the max length for strings is 255 characters, and for Codes (Company Code, Plan Code), the max is
9 characters.

Post by: cgomez | RE: WebCobra Tables

Wednesday, 27 Feb 2008 10:29 CST

We saw the dictionary, but for example the QB Date Type Codes are in the dictionary and not in the example. I have learned recently about the QBDateTracking element which is not in the examples or in the XML Structure layout this in the same section. With that there is any documentation on how the fields are used or what effect they will have when applied. Is there anything that will explain the XML structure and what fields do. Another thing that was asked is the limit size or type on the fields within the elements. What is the limit size for the QBID in the QB element. Can I put a variable size of 1000 or is 50 the max size I can use. We learned that the dates will not accept slashes, but using dashes in the date worked. If we knew that ahead of time, it would of saved us time on programming.

Post by: jwolgamott | SecurityLogin

Wednesday, 27 Feb 2008 09:40 CST

Excellent! it sounds like the XML file you're sending across doesn't have the correct formatting.

You can test this be sending in just a normal XML file that doesn't do anything. Step 1 is for it to authenticate you.

Here's an XML stub that I've changed my password with... does yours look like that?

http://s3.amazonaws.com/devcenter.w ebcobra.com/thread46-securitylogin. xml

(You might have to remove spaces in the link)

(And the username with a space shouldn't matter)

Post by: jwolgamott | RE: WebCobra Tables

Wednesday, 27 Feb 2008 09:35 CST

OK, I've got a better understanding of the background of what you're trying to accomplish.

Here's some resources to help you out:

- In IT Resources in WebCOBRA, there is a dictionary... It contains an explanation of the IDs that you'll need to use when importing a QB, like Event Codes (termination vs divorce, etc), and Date Types, like QB Created and Paid Through date.

Our customers have had success with importing the Company structure (Companies, Insurance Carriers, Benefit Plans) with the XML file, and then using the simpler CSV file for QB creation.

The complete listing of fields in the XML file is found in the "XML Format - Download the XML Format shell" link --- it should give you a head start.

Post by: cgomez | RE: WebCobra Tables

Tuesday, 26 Feb 2008 05:42 CST

This for WebCobra. I am converting from a system called "Cobra Solutions" to WebCobra. I am having to use the XML file, because I was told that was only way to populate the UserDefinedKey to a person. I am loading history into the system, so I keep running into issue because of fields that are missing from the example that I can use. Now if I had some documentation on what fields are in each element that would be what we are looking for. You list the elements in the "Edit Example" section, but to what fields are available is missing. The table structure in question is the elements in the XML file. In order for us, to export this information to you each element has to be put into a table, so we were just asking the fields to correctly setup these tables.

Post by: dpbarbella | RE: NEW ERROR 3

Tuesday, 26 Feb 2008 02:40 CST

Thanks for all your assistance :)
I think we are almost there.

ERROR: /WebCobra/SecurityLogin/@Administra torLoginCode not supplied

I have included the 'SecurityLogin' node with the correct data. NOTE: our username is 2 words separated by a space.

Post by: jwolgamott | RE: NEW ERROR 2

Tuesday, 26 Feb 2008 01:11 CST

OK, I ran through an entire process and logged which procedures needed to be granted execute rights for you.

You shouldn't have anymore 229 execute permissions errors from now on.

Post by: jwolgamott | RE: WebCobra Tables

Tuesday, 26 Feb 2008 01:09 CST

When you say "table structure" -- what database are you referring to? Is this TravisCobra, or another system that you are converting from?

The CSV and XML are indeed different; the CSV is intended to bring over information easily and matches the TravisCobra import file format.

Post by: dpbarbella | RE: NEW ERROR 2

Monday, 25 Feb 2008 10:49 CST

Server was unable to process request. ---> SQL Exception 229 caught on procedure SystemIntegrationUserKeys. Error: The EXECUTE permission was denied on the object 'SystemIntegrationUserKeys', database...

New Error Msg

Post by: jwolgamott | RE: NEW ERROR:

Monday, 25 Feb 2008 09:13 CST

It looks ilke we had denied permission to the system ingrator webservice inadvertantly after our migration. This only affected the automated webservice -- Uploading the file manually through the system would have worked.

If you try again, it will be able to authenticate your request.

Post by: dpbarbella | RE: NEW ERROR:

Monday, 25 Feb 2008 08:39 CST

The EXECUTE permission was denied on the object 'SystemIntegratorSecurity', database...

Does this mean we need to setup permissions for the SystemIntegrator WS in some way?

Post by: cgomez | WebCobra Tables

Friday, 22 Feb 2008 04:36 CST

I need to know what the table structure are to help build the XML feeds into WebCobra. We know some of the fields that we can load, but what are max lengths or values that can be store on the XML. We are building the table structure here in order to build the XML file. Each table would be related to each element on the XML file. For now I just need the QB tables structure and relationship between each other. I also need to know the values that are acceptable and lengths. The example on the website only shows a few fields and we are already notice a field difference between the CSV file and XML File.

Post by: jwolgamott | RE: How to link CarrierSponsor

Saturday, 09 Feb 2008 02:21 CST

Matthew --

Sorry, I assumed you were doing the SQL queries in the Access Data Loader. Based on your reply (and you're other posts), you're using the raw API, right?

If so, you're right, you can't do either SELECT *'s or Inner joins, so that's a bad example.

To get a Benefit Plan and Carrier array of objects, you'll want to get an array of carriers from a query. Then, do a foreach loop through them, and execute a separate query. Something along the lines of:

Carriers[] = get_carrier_list()
foreach (carrier in Carriers)
{
Plans_for_Carrier = API.Query("select id, name from BenefitPlans where CarrierSponsor_ID = " + carrier.id)
// do something interesting with your plan list
}

Hope that pseudo code makes sense.

Post by: jwolgamott | RE: Data Loader -- all data

Friday, 08 Feb 2008 02:16 CST

That does sound like a support issue --- If you see a payment on the screen, you should see a payment in the Access database.

However, one thing to make sure if that you're talking about payments (cash in) versus premiums paid, especially if you mark a QB as being Paid Through a certain date.

Post by: jwolgamott | ERROR: The request failed with an empty response

Friday, 08 Feb 2008 02:11 CST

One final note: on the URL entered above, you shouldn't have any spaces... probably obvious, but just wanted to put that out there.

Post by: jwolgamott | ERROR: The request failed with an empty response

Friday, 08 Feb 2008 02:09 CST

Daniel --

I just created a sample app, and have a suggestion for you..

In your settings.settings file, it stores the location of the web service, and it looks like Visual Studio stored it without the SSL part of the URL

If you'll change this to
https://app.webcobra .com/exec/SystemInte grator.asmx

Everything should work for you.

The "empty response" was likely a 301 or 302 redirection to enforce SSL encryption.

Post by: mhowell | RE: How to link CarrierSponsor

Friday, 08 Feb 2008 01:57 CST

That makes sense about the title being too long. I'll be less verbose :-)

About the ID field, cool, I'll try that next then, but your SQL prompted another question: Are we able to do a SELECT * ... query, or do we have to list each field separately? Can we do inner joins? If so, what kind of results object should we cast that to so we can cycle through the rows?

Matthew

Post by: jwolgamott | RE: How to link CarrierSponsor

Friday, 08 Feb 2008 01:52 CST

Each table has an "ID" column, which represents that entity's ID.

so if a BenefitPlan had a CarrierSponsor_ID of 25, you'd look for CarrierSponsor that had a CarrierSponsor.ID = 25

Here's a sample query:

---
select * from BenefitPlans
inner join CarrierSponsors on CarrierSponsors.ID = BenefitPlans.Carrier Sponsor_ID
---

You could easily add where clauses and groupings.

And finally... I think the error on postings are with the size of the Subject field.. if it's too long, we get an error.

Post by: mhowell | How to link CarrierSponsor to BenefitPlan

Friday, 08 Feb 2008 11:53 CST

I was looking at the list of fields to pull from, and noticed that the BenefitPlan table had 5 fields, one being CarrierSponsor_ID. The description for that field is - ID of parent carrier-sponsor. But when I look at the fields for the table CarrierSponsor, the CarrerSponsor_ID field is not listed - only the clientEmployer_ID.

If I were to pull down all data in both tables, how would I know which benefit plan goes with which carrier?

Similar problem for the ClientEmployer table.

And for some reason, I always have error posting, like the forum engine doesn't like special characters. What are those characters?

Post by: mhowell | RE: Content Types not matching

Friday, 08 Feb 2008 11:43 CST

Thanks, that update helped. I was also able to determine that, using Visual Studio 2008 (.NET 3.5), you need to add a web reference which is not the same as a service reference. Also, since the WSDL is on an HTTPS connection, after you add the web reference, VS does not save the HTTPS, essentially converting it to HTTP and you will get blank responses. You need to go into the .config file and add the 's' to the HTTP.

Post by: dpbarbella | ERROR: The request failed with an empty response

Thursday, 07 Feb 2008 09:22 CST

I continue to receive the error ("The request failed with an empty response") when attempting an import to your API. However, when submitting the same XML through the Management Console everything is fine.

I'm using .NET 2.0 referencing your SystemIntegrator service and running the ImportXMLToWebCobra method.

Post by: jsterling | RE: Data Loader -- all data

Monday, 04 Feb 2008 06:26 CST

I mean that when I run a query via the API query tool I get different results than when I run a query via the access data base. I don't appear to be pulling all payments that are currently posted in WEBCOBRA, only a handful actually make it down to the database. Any thoughts on why I might be getting different results than what is actually in WEBCOBAR?

Post by: jwolgamott | RE: Content Types not matching

Thursday, 31 Jan 2008 04:04 CST

The sample_query project that was compiled had the correct reference, but the code was indeed pointing to localhost. This has been corrected, so you can download the project and code from the Sample code section.

You can, however, download the WSDL directly from https://app.webcobra .com/exec/api.asmx?w sdl

Post by: jwolgamott | RE: QB plan assignment.

Thursday, 31 Jan 2008 03:09 CST

I think you'll be happy with the new relational api coming out in the Spring; it will have all information in the system, each table, so you can get to this level of detail if needed.

Post by: jwolgamott | Data Loader -- all data

Thursday, 31 Jan 2008 03:00 CST

When you say all data, do you mean that some participants are in the live WebCOBRA system, but not in the access file?

Or do you mean that you see a field in the live system that you expected to be in the access file?

I anticipate you mean that there is data in the live system that you'd like to see in the API. If that's the case, please fill out an enhancement request through Travis Support.

Post by: mhowell | Content Types not matching

Thursday, 31 Jan 2008 11:06 CST

After trying to write a simple interface to the WebAPI, I kept getting the same error. I have wrote code in VB.NET as well as C#, neither work and both give the same problem.

The problem is:

The content type text/html; charset=utf-8 of the response message does not match the content type of the binding (text/xml; charset=utf-8). If using a custom encoder, be sure that the IsContentTypeSupport ed method is implemented properly. The first 1024 bytes of the response were: '

...

And my code is below. To create these probjects, I didn't do too much more than create a new console application project, add a service reference to "https://app.webcobr a.com/exec/api.asmx", and type the code below (which just tries to log in). It breaks at run time.

Most of the code is taken directly from the query sample application from the DevCenter's Sample Code section. After looking at that code, it appears that the web reference it uses is to a local file (http://localhost/blankwebsite/API.asmx) and attempts to instaniate an API object, which I have not been able to do (the closest I can find is the APISoapClient object; I cannot verify that the WSDL is different since the one referenced is local).

Can an updated project be posted that uses the actual WSDL URI (https://app.webcobra.com/exec/api.asmx)?

Thanks, Matthew

' VB.NET Version:

Module wcTest1module
Sub Main()
Console.WriteLine("Initializing...")
Dim wcAPI As New WebCOBRA.API()
Dim credentials As WebCOBRA.ServiceCredentials

credentials = login(wcAPI)
End Sub

Public Function login(ByRef api As WebCOBRA.APISoapClient) As WebCOBRA.ServiceCredentials
login = Nothing

' Breaks on next line
login = api.Login("admin", "user", "password")
End Function
End Module

-------------------
// C# Version
using System;
using System.Collections.Generic;
using System.Text;

namespace wcTest2
{
class test2cs
{
static void Main(string[] args)
{
Console.WriteLine("Initializing....");

// Declare our API Object
WebCOBRA.APISoapClient API = new wcTest2.WebCOBRA.APISoapClient();
WebCOBRA.ServiceCredentials credits;

login(API);
}

public static WebCOBRA.ServiceCredentials login(WebCOBRA.APISoapClient api)
{
WebCOBRA.ServiceCredentials ret = null;

ret = api.Login("admin", "user", "password");

return ret;
}
}
}

Post by: jsterling | Data Load Doesn't Appear to be fetching all data

Tuesday, 22 Jan 2008 04:57 CST

We're experimenting with the Access DB. When we review the table content downloaded, it doesn't appear all data is downloaded to the DB. Does anyone have any advice on what the issue might be?

Thank you.

Post by: DianeM | QB plan assignment.

Tuesday, 15 Jan 2008 12:42 CST

We must have these fields made available in a table, whether placed in an existing table or a new one. They are benefit plan end date and benefit plan start date. When members change plans or drop dependents, we have no way to attach to these date specific changes to adjust our eligibility file. This is our biggest client and we have to have this done. Please respond asap.

Diane Muschetto
702-248-2542 *125
dmuschetto@mediversa l.com
Mediversal, Inc.
Our serial number is 21182

Post by: jwolgamott | RE: Addition to the Data Loader

Thursday, 10 Jan 2008 02:26 CST

Yes, this Event Type and Date has been scheduled for addition for the February release of WebCOBRA.

Once available you should be able to create the report easily.

Post by: jwolgamott | RE: Access Data Loader

Thursday, 10 Jan 2008 02:23 CST

Judy ---

Most of the time we see this on the ClientEmployer and the BenefitPlan tables; most likely you have a Company or Plan that has a long (greater than 50 characters) name.

You can follow these instructions to extend those Access tables length.

1 - Open the WebCOBRA_Reporting_S ystem.MDB file in Microsoft Access
2 - Right click on the ClientEmployers table and select Design View
3 - Select the Name field
4 - In the General tab below, change the Field Size from 50 to 254
5 - Press save
6 - Repeat steps 3 and 4 for the BenefitPlans table
7 - Run the WebAPI_Loader

You should be set!

Post by: JudyTatton | Access Data Loader

Thursday, 20 Dec 2007 03:38 CST

I'm getting the following error message while I'm trying to load Access Data Loader:

We could not connect to the Access Database. Actual Error: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

How do I customize the field info for the load window?

Post by: ChrisLothian | Addition to the Data Loader

Tuesday, 11 Dec 2007 03:36 CST

A client has requested a report that includes what they refer to as "QE(Qualifying Event)Type". Further enquiry on my part elicited these examples of the contents of the field: Terminated, Layed Off, Ineligible, Death, Divorce, etc.
Is it possible to add this field to the list of available WebAPI field names?

Post by: gp28551 | RE: What more can we do?

Wednesday, 18 Jul 2007 12:42 CST

We, Aon Consulting, are currently running the client/server version of TravisCOBRA. I have approximately 200 external applications built through MS Access, which connects to a copy of the Travis tables. We want to upgrade to your Web version, but it doesn’t look like your current API configurations will work for our external reporting purposes. We use many more fields and tables than you currently have built into the API, and don't want to have to use our resources in recoding every external application that we currently use. Is it possible to configure the API fields to be more in line with the current table structure being used for the client/server version? We use pretty much all of the tables and a majority of the fields.

Post by: jwolgamott | RE: WebCOBRA API Data Loader

Sunday, 24 Jun 2007 11:54 CST

It's possible that you are trying to run the Application from a network drive. Right now, you should copy it locally, either to your C: or your desktop and run from there.

In a future release, we hope to make this not an issue.

Post by: tkeith | WebCOBRA API Data Loader

Thursday, 14 Jun 2007 09:20 CST

When attempting to login to API Data Loader, I am gettting an error message of "Request for the permission of type 'System.Net.WebPermi ssion, System, Version=2.0.0.0, Culture=neutral, PublicKey Token=b77a5c561934e0 89' failed.

What am I supposed to do to be able to log in?

Post by: jwolgamott | New Field to API

Thursday, 07 Jun 2007 08:10 CST

By popular demand, we've added a field to the API. Now on the QB_BenefitPlan object, you can get the FirstDayAfterLossOfC overage. This is the date after the Qualified Beneficiary lost coverage under his/her previous benefit plan.

This is commonly the Start of COBRA (but could also be USERRA or State Continuation).

Enjoy!

Post by: jwolgamott | Updates to WebAPI

Friday, 27 Apr 2007 09:06 CST

We've made some exciting updates to the WebAPI since we launched earlier this year.

For the QB record, we've added
- DepartmentName
- DepartmentCode
- Gender
- TerminationDate
- TerminationReason
- Relationship (Spouse, Child, etc)

And for the QB_BenefitPlan record, we've also added:
- Coverage Level Description

Our Access Loader code has these as of today (4/27/07), but you can write your own application just as easily.

- jesse

Post by: kippfeldt | RE: How does the Access Data Loader Work?

Thursday, 29 Mar 2007 03:14 CST

I guess I posted to quickly!

This article is how I solved the problem:
http://www.mischefam ily.com/nathan/index .cfm/2007/2/6/ColdFusion-and-ASPNET-Web-Service-Interoperability-or-Lack-Thereof-Part-I

All I had to do is going into the projects app.config file and change:

http://app.webcobra.com/exec/api.asmx

to:

https://app.webcobra.com/exec/api.asmx

Hope this helps someone else or webcobra will release a new Data Loader download.

Kipp

Post by: kippfeldt | RE: How does the Access Data Loader Work?

Thursday, 29 Mar 2007 02:33 CST

We downloaded the Webcobra Loader opened the project and ran the code in debug mode and get this error:

Your login failed due to unhandled exception: The request failed with an empty response.

Error occurs in login.cs:
F.Credentials = F.APIObject.Login(tx tAdminCode.Text.Trim (), txtUsername.Text.Trim(), txtPassword.Text.Trim());

The compiled version that was downloaded works and can login to download access data.

Any help would be greatly appreciated.
kfeldt@tekniq.com

Post by: charles | RE: Enterprise or Professional?

Friday, 02 Mar 2007 10:05 CST

I would recommend the enterprise version!!!

Post by: jwolgamott | Large XML Files

Sunday, 07 Jan 2007 12:37 CST

With the System Integrator you can only upload files less than 4MB -- that's due to prevent denial of service attacks.

Generally, this is far more than people need, but if you are writing a custom conversion program where you are taking hundreds of thousands of QBs into WebCOBRA, it's possible you'll reach this ceiling.

We recommend that you either:
* Break into multiple files, generally one file per Client/Employer
* Get with Travis Support. We can probably accomodate uploading and running the file for you, depending on our scheduling and load at the time. Email support@webcobra.com for more information.

Post by: jwolgamott | What more can we do?

Sunday, 07 Jan 2007 12:34 CST

What tables would you like to see added to the API?

What about for DevCenter itself? Do you want to be able to upload projects?

All ideas welcome.

Post by: jwolgamott | What SQL Syntax does WQL use?

Sunday, 07 Jan 2007 12:29 CST

First, what is WQL? -- it's short for WebCOBRA Query Language, and it's how we get information out of the API. It's very similar to SQL (Structured Query Language), but it does not have all of the features. It's designed to be simple and easy to use.

SELECT fieldname(s) FROM tablename WHERE fieldname CONDITION value

Conditions:
* =
* like
* <
* <=
* >
* >=

Want to see other constructs or need to do something? Let us know here!

Post by: jwolgamott | How could I create a custom EDI Application?

Sunday, 07 Jan 2007 12:26 CST

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

Post by: jwolgamott | Enterprise or Professional?

Sunday, 07 Jan 2007 12:22 CST

A common question is do you need to be on Enterprise to use the System Integrator?

Nope. While you do need to be on enterprise to use the WebAPI, all of our other web services are open.

We want you to be able to get feeds from your systems (or your client systems) and upload to WebCOBRA with as little pain as possible.

That's why we accept feeds in either XML, or we can convert CSV files to XML format for you.

Post by: jwolgamott | How does the Access Data Loader Work?

Sunday, 07 Jan 2007 12:19 CST

We've provided sample code to create a local read-only version of your database in Microsoft Access.

To run the Data Loader, you'll need to create an account, then visit the Sample Code section and:

1) Download the zip file.
2) Unzip the contents
3) Run the Webcobra_Loader.exe
4) Click on Access Database
5) Enter your webcobra administrator code, username, and password
6) Click Load

From here, the application will download your Client/Employers, Benefit Plans, QBs, and their payments into an Access Database.

To refresh your data, follow the same 6 steps. It will clean out your Access DB and download all new data.