- 3 Posts
- 408 Views
AP Balance Query
Hello - I'm looking for a query to pull YTD AP Subledger balances. If you have the query or know which tables to go against please share.
Thanks
@Dharmesh Patel PS_VCHR_ACCTG_LINE contains the information you need.
Hello Dharmesh,
If you don't care about the YTD as of a prior month, you can check the Amount Vouchered YTD field (AYPD) in the F0401. (This assumes that you run the Update YTD Voucher Amount R04820A at the end of every year.)
If you want your YTD amount as of a prior month, then a query would work best. Here's a sample query that I wrote to grab the AP Ledger (F0411) amounts by specific branches:
select rpan8, wwmlnm, aladd1, aladd2, alcty1, aladds, aladdz, sum(rpag / 100) Amount
from jde_data.proddta.f0411 F01 (nolock), jde_data.proddta.f0116 F02 (nolock), jde_data.proddta.f0111 f03 (nolock)
where f01.rpmcu in (' 32020', ' 43020', ' 32080', ' 49170', ' 43000', ' 32090', ' 49340') and f01.rpdgj > 114000 and wwidln = 0 and f01.rpan8 = f02.alan8 and f02.alan8 = f03.wwan8
group by rpan8, wwmlnm, aladd1, aladd2, alcty1, aladds, aladdz
This provides one line per vendor, with the vendor address. If you don't need the vendor address, then remove the F0116 and F0111 portions of the query.
I hope that helps.
Gayle Larson