WebCOBRA.com Community Message Boards

kippfeldt
Posts: 18
Since: 02/08/2007

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

jwolgamott
Posts: 257
Since: 09/26/2005

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!

blambrecht
Posts: 65
Since: 02/05/2008

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

jwolgamott
Posts: 257
Since: 09/26/2005

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!

blambrecht
Posts: 65
Since: 02/05/2008

This is VERY helpful! Thanks again.

kippfeldt
Posts: 18
Since: 02/08/2007

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

jwolgamott
Posts: 257
Since: 09/26/2005

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'

kippfeldt
Posts: 18
Since: 02/08/2007

This will be perfect! Thanks for the help!

PBAMis
Posts: 3
Since: 06/24/2009

When an individual makes a payment with ARRA, any future months the amount paid for ARRA is not displayed or as far as we can tell available in the Relational API.

This individual paid for July and August. The full two-month payment is seen but only the July ARRA is seen. Augusts' is not.

travis
Posts: 44
Since: 04/22/2009

@PBAMis - cross answered at your post on http://devcenter.webcobra.com/conve rsations/155.aspx