Released on: 12, June 2007
, Author: Alba Spectrum Group
, Audience: Computers related
Alba Spectrum Group. http://www.albaspectrum.comhe lp@albaspectrum.com 1-866-528-0577, 1-630-961-5918
In this small technical article we will give SQL script to build up something likeCrystal 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, itis good idea to give you some highlights on the join we utilize here. We have tounionize GP open and history PM tables. In Great Plains the posting logic movesunapplied 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  bothdocuments go to history: PM30200 table. Now you see the reason to unionizehistorical and open tables. You also should be aware about PM20100 and PM30300tables: Payment Apply To Open and History. If you would like to analyze MicrosoftDexterity tables descriptions, you should launch GP and go to: Tools->ResourceDescription->T ables. Please, note here, that we use DEX_ROW_ID as unique value toavoid union known line duplication issue
CREATE PROCEDURE SP_HistoricalAP @CutoffDate datetime AS
select
a.VCHRNMBR,a.VENDORID,a.DO CTYPE,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 (selectVCHRNMBR,VENDORID, DOCTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID ,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM30200union selectVCHRNMBR,VENDORID,DO CTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID ,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM20000)
aleft join --PM30300 (select APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) asApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM30300 group by APTODCTY,APTVCHNMunionselec t APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) asApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM20100 group by APTODCTY,APTVCHNM)b on a.VCHRNMBR
Source: Express-Press-Release.com
Related downloads
Integrate everything to Microsoft CRM. Scheduled imports/exports and synchronizations. The only tool you need in your Microsoft CRM Implementation project. Free download and Try!
Recently Aged - Lifetime Milestone Calculator is a fun way to review your lifetime statistics and to learn your exact age (down to minute/second). A companion to the "Handbook for the Recently Aged".
Download free historical stock quotes and store historical stock prices in metastock format. Intraday and End-of-day quotes for mutual funds, market indices and stocks. Metastock and ASCII format supported. Split adjusted historical data.
R-Mail for Outlook Express is a tool designed to undelete Outlook Express e-mail messages and recover damaged *.dbx files. The messages are recovered in the .eml format and can be imported into Outlook Express mail and news bases.
R-Mail for Outlook Express is a tool designed to undelete Outlook Express e-mail messages and recover damaged *.dbx files. The messages are recovered in the .eml format and can be imported into Outlook Express mail and news bases.
R-Mail for Outlook recovers accidentally deleted Outlook e-mail messages, contacts, notes, tasks and other items, and repairs damaged Outlook data files (*.pst) files where Outlook stores folders with the data.
MB Free Balance Number Software is a simple and easy to use software that helps you locate your personal balance number, which in turn, can give you much needed guidance to deal with complicated or challenging situations.
Free PDF converter software trial: Convert PDF To Doc or doc files to pdf. Convert PDF files to RTF or rich text format.
Your toddler will love these stimulating educational games. Lucky kids aged 1-4 will play these games again and again, learning a little more each time. The upbeat music, graphics, and animated segments will have the whole family excited to play!
I Can Press Keys is a fun game designed for children in the 1 to 3 year age range. It offers a number of different animated scenes and sound effects which are activated simply by pressing keys on the keyboard.
