Custom Aged Payables Query

[murderface]

Hello everyone. I did some search around, both on the forum and in the database, but still need help.

I am trying to generate a custom aged payables report - the only custom part being a where clause on the account number (gl00100.actnumbr2). The report that runs in great plains shows every shows every account.

Forgive any ignorance that I display - I am a humble network administrator and had this job dropped on me.

 

[murderface]

To help give an idea of what I am looking to do, the following query produces a report in crystal reports which is almost what we need - only with vendors instead of customers - an aged payables report for all vendors under a specific account.

 SELECT DISTINCT "RM20101"."CUSTNMBR", "RM00101"."CUSTNAME", "RM20101"."DOCDATE", "RM20101"."DOCNUMBR", "RM20101"."DUEDATE", "RM20101"."CURTRXAM", "RM20101"."AGNGBUKT", "RM20101"."RMDTYPAL", "RM20101"."CSPORNBR", "SOP30200"."LOCNCODE", "RM20101"."TRXDSCRN"

 FROM   ("SIG"."dbo"."RM20101" "RM20101" INNER JOIN "SIG"."dbo"."RM00101" "RM00101" ON "RM20101"."CUSTNMBR"="RM00101"."CUSTNMBR") LEFT OUTER JOIN "SIG"."dbo"."SOP30200" "SOP30200" ON (("RM20101"."CUSTNMBR"="SOP30200"."CUSTNMBR") AND ("RM20101"."TRXSORCE"="SOP30200"."TRXSORCE")) AND ("RM20101"."DOCNUMBR"="SOP30200"."SOPNUMBE")

 WHERE  "RM20101"."CURTRXAM"<>0

 

[VictoriaYudin]

All posted but unpaid transactions will be in the PM20000 table, the vendor master is PM00200. There are no aging buckets automatically calculated in GP for payables, so you'll have to calculate them yourself in your report.

What specifically do you need to filter in your where clause with GL account numbers?

Here are a few resources that might help:
PM tables:

http://victoriayudin.com/gp-reports/pm-tables/

View showing GL distributions for payables transactions:

http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/

Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[tek1967]

I have installed Microsoft dynamics GP version 10, trail verson in my laptop which has the O/S Windows Vista bussiness some months before. I have forgotten my 'sa' password. Is there any way to retrieve