Join APOBL to APOBP, then you can filter as of a cut-off date. Or run a trial balance, and look in APAGED.

OK I have a working query in APOBL/APOBP how can i tie these tables back to the GL Batch/Entry number they hit the GL on?

APPJH.GLBATCH, APPJH.GLENTRY

Thanks Jay...ok I brought in the GL Batch # and GL entry numbers, but now I am confused, and I wonder if it is because I am using the amount field AMTDUEHC as opposed to adding invoice and payment amounts together.....

The good news I have a query that shows the correct balance owing, and this balance while large (over $6M) is only $80.53 different from my GL Balance....

however using the Fiscal Period and Fiscal year fields, this balance is composed over time very differently than the GL balance by period...but again same result at the end.

Thats why I wanted to use GL Batch #/entry number to again compare to the GL....and when I do this, the net amounts per GL Batch/entry number do not agree with the GL version (by batch/entry number).....but I am guessing, its because I am using the AMTDUEHC field in both cases. Does this make sense?

Thanks Jay...ok I brought in the GL Batch # and GL entry numbers, but now I am confused, and I wonder if it is because I am using the amount field AMTDUEHC as opposed to adding invoice and payment amounts together.....

The good news I have a query that shows the correct balance owing, and this balance while large (over $6M) is only $80.53 different from my GL Balance....

however using the Fiscal Period and Fiscal year fields, this balance is composed over time very differently than the GL balance by period...but again same result at the end.

Thats why I wanted to use GL Batch #/entry number to again compare to the GL....and when I do this, the net amounts per GL Batch/entry number do not agree with the GL version (by batch/entry number).....but I am guessing, its because I am using the AMTDUEHC field in both cases. Does this make sense?

Correct, if you work off of APPJH.GLBATCH, you need to be adding APPJD.AMTEXTNDHC.

Joining APPJD to the mix, I have exploded my record count....
I joined APPJD to APPJH using TYPEBTCH / POSTSEQNCE / CNTBTCH / CNTITEM. How do you suggest joining them to get the intended result?

GROUP BY and SUM subqueries

Ah your killing me....and here i thought, all i had to do was connect the gl batch and entry numbers from the APPJD and APPJH tables in addition to what I had already done (you can tell I do as little as possible in SQL)...

There's a one-to-many relationship from APOBL to APPJx, so you have to use subqueries.

Looking at APPJD, it has the values I want...I see that...but the GL Batch id and entry numbers are blank....if APPJD and APPJH are linked (and of course they are, as APPJH is the header record)....how are they actually linked? I keep getting data explosions as I try to link them....and I am now just isolating these two tables together. I figure if I can work this out, then I can bring the APOBL table in later.

Join APPJH to APPJD with TYPEBTCH, POSTSEQNCE, CNTBTCH, CNTITEM

Played it safe, joined APPJD to APPJD as you noted, got GL Batch numbers, and went back to comparing to GL table. Put two side by side in Excel....there seemed to be numerous differences, the majority netting to zero in the next batch.....not sure why the GL would act that way....but in comparing, there was one batch with NO match to GL - and it totalled exactly what I was looking for. Problem solved. thanks Jay.

The fact that you have a missing GL batch means your GL integration settings are incorrect, and a user screwed up. All subledgers should be configured to have their GL batches automatically posted.