SQL Script for Displaying Payments Based on Account Segments

(0) Share

Report

Posted on by Community Member

Hello:

The end user says that the script below, when run a few months back, successfully displayed payables payments based on segment 4 of the chart of accounts.

Now, she says that the script does not work at all.

Granted, the GL20000 is one of the tables. And, that would lead one to believe that the year-end close has something to do with it.

But, she said that she had run this since the year-end close. Again, it worked at one time, and now she says that it doesn't.

I'm a little surprised that it worked, to begin with. The programming appears a little "lean". I would think that there would be more joins involved.

Anyway, please let me know why this would have worked at one time and now doesn't.

Thank you!

John

Select distinct ACTNUMST, ACTDESCR , DOCAMNT,b.DOCDATE,TRXDSCRN, h.PONUMBER, i.VNDITDSC

from GL20000 a join PM30200 b on a.ORGNTSRC = b.TRXSORCE

join GL00105 c on a.ACTINDX = c.ACTINDX

join GL00100 d on a.ACTINDX = d.ACTINDX

join PM30300 e on e.VCHRNMBR = b.VCHRNMBR

join POP30300 f on VNDDOCNM = e.APTODCNM

join POP10500 g on g.POPRCTNM = f.POPRCTNM

join POP10100 h on g.PONUMBER = h.PONUMBER

join POP10110 i on i.PONUMBER = h.PONUMBER

Where d.ACTNUMBR_4 in ('604', '605')

--and b.DOCDATE between '1/1/2017' and '11/16/2017'

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Hi John,

There are 2 things I noticed here.. the script mixes up Current (work) and Historical tables.. which is usually not a good sign, unless that's wanted.

In your case, the join with the GL20000 (current year) with PM30200 and also POP30300 to some extend, which means if no one has yet moved PO's to history, nothing might show up..

I could be wrong.. but worth for checking.

PS: maybe using the out-of-the-box SQL views like 'PayablesTransactions' & 'Accounts' might get a better result, as it usually includes both current & historical data..

Was this reply helpful? Yes No
Suggested answer

MG-16101311-0 26,225 on at

Like (0)

Report

There's no way in you know where that this script did what they said it would do. Also, if 2017 is closed, this script wouldn't pick up any GL information. I will be happy to add a script, but I am running into a meeting. This script needs some major rework.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, Mariano! I am attempting a re-write and will run it later today. I'll let you know how it goes.

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, Beat!

John

Was this reply helpful? Yes No
Verified answer

MG-16101311-0 26,225 on at

Like (0)

Report
I would start with this. The PO info is easy to add once you are sure this is what they want.

with Payments as ( select * from PM20000 where DOCTYPE = 6 union all select * from PM30200 where DOCTYPE = 6 ), GLTransactions as ( select JRNENTRY, ACTINDX, ORDOCNUM, ORTRXTYP, ORCTRNUM, SERIES from GL20000 WHERE SERIES = 4 AND ORTRXTYP = 6 union all select JRNENTRY, ACTINDX, ORDOCNUM, ORTRXTYP, ORCTRNUM, SERIES from GL30000 WHERE SERIES = 4 AND ORTRXTYP = 6 ) select * from Payments p left outer join GLTransactions gl on (p.DOCTYPE = gl.ORTRXTYP) and (p.VCHRNMBR = gl.ORCTRNUM) left outer join GL00105 ix on (gl.ACTINDX = ix.ACTINDX) left outer join GL00100 m on (ix.ACTINDX = m.ACTINDX) where m.ACTNUMBR_4 in ('604', '605')

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, Mariano!

John

Was this reply helpful? Yes No