AR Aging report

[PaulBrown1965]

I need to extract the same data set as the AR Aging report using SQL SELECT statement. Below is my SQL SELECT statement. I have been looking through the arrept.prg file but cant find the missing piece. I am returning too many records that the ar aging report does not have. I am using VBS to create the data set.

sql="Select CUSTNO,INVNO,INVDTE,BALANCE,PNET,ARSTAT"&_
" INVAMT,DTEPAID,ARTYPE,SALESMN,ORNUM,PONUM,REFNO"&_
" from ARYMST01 where balance <> 0 and arstat <> 'V' "&_
" union "&_
" Select CUSTNO,INVNO,INVDTE,BALANCE,PNET,ARSTAT"&_
" INVAMT,DTEPAID,ARTYPE,SALESMN,ORNUM,PONUM,REFNO"&_
" from ARMAST01 where balance <> 0 and arstat <> 'V' "&_
" order by CUSTNO"

thank you
Paul

 

[ettienne]

That's not for Sage 300, which program are you using?
In Sage 300 you can run the aging view AR0055 and then query the ARAGED table, that will give you exactly what you need.

Sage 300 Certified Consultant

 

[Jay Converse]

That's from Sage Pro (PFW). You're showing a union query with the same parameters, which will double everything up.

 

[ettienne]

Sage Pro and Sage PFW are 2 different programs

Sage 300 Certified Consultant

 

[PaulBrown1965]

The program is SBT using foxpro. The join is for the current ARMAST table and the history ATMAST table, we need all records.

 

[ettienne]

Sorry, no real SBT people here. Most here are Sage 300, aka Sage Accpac ERP or Accpac. Not the Accpac Pro or SBT product.
You might want to try on the Sage forums

https://sagecity.na.sage.com/support_communities/sage_pro_erp/,

but SBT is essentially dead and support for it has ceased.

Sage 300 Certified Consultant