Released on = June 12, 2007, 3:44 pm

Press Release Author = Alba Spectrum Group

Industry = Computers

Press Release Summary = In this small technical article we will give SQL script to
build up something like Crystal Report or even now becoming popular SRS (MS SQL
Server Reporting Services) report. You can envelop this script into SQL stored
procedure.

Press Release Body = Alba Spectrum Group. http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577, 1-630-961-5918

In this small technical article we will give SQL script to build up something like
Crystal Report or even now becoming popular SRS (MS SQL Server Reporting Services)
report. You can envelop this script into SQL stored procedure.

Prior to the nice time when you will be looking and analyzing SQL script itself, it
is good idea to give you some highlights on the join we utilize here. We have to
unionize GP open and history PM tables. In Great Plains the posting logic moves
unapplied Vendor Invoices to work table PM20000, in the same table all the payments,
credit and debit memo go. When you apply completely payment to invoice - both
documents go to history: PM30200 table. Now you see the reason to unionize
historical and open tables. You also should be aware about PM20100 and PM30300
tables: Payment Apply To Open and History. If you would like to analyze Microsoft
Dexterity tables descriptions, you should launch GP and go to: Tools->Resource
Description->Tables. Please, note here, that we use DEX_ROW_ID as unique value to
avoid union known line duplication issue

CREATE PROCEDURE SP_HistoricalAP
@CutoffDate datetime
AS

select

a.VCHRNMBR,
a.VENDORID,
a.DOCTYPE,
a.DOCDATE,
a.POSTEDDT,
a.DOCNUMBR,
a.DOCAMNT,
case
when b.APTVCHNM is null then a.DOCAMNT
else (a.DOCAMNT-b.APPLDAMT)
end as CURRAMT,
a.DEX_ROW_ID as DEX_ROW_ID1,
b.DEX_ROW_ID as DEX_ROW_ID2,
a.DISCAMNT,
a.DUEDATE,
a.PYMTRMID,
a.VOIDED

from

--PM30200
(
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM30200
union
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM20000
)

a
left join
--PM30300
(
select APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as
ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM30300 group by APTODCTY,
APTVCHNM
union
select APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as
ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM20100 group by APTODCTY,
APTVCHNM
)
b on a.VCHRNMBR

Web Site = http://www.albaspectrum.com

Contact Details = Alba Spectrum Group. http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577, 1-630-961-5918