Microsoft,Dynamics,SQL,Scripti computer Microsoft Dynamics GP SQL Scripting Historical Aged AP Tri
Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc ----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a
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 issueCREATE 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.VOIDEDfrom --PM30200 (selectVCHRNMBR,VENDORID,DOCTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM30200union selectVCHRNMBR,VENDORID,DOCTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM20000)aleft 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, APTVCHNMunionselect 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=b.APTVCHNM and a.DOCTYPE=b.APTODCTY and a.DOCTYPE<=4 and a.VOIDED=0where a.DOCDATE<=@CutoffDate and b.ApplyToGLPostDate<=@CutoffDateunionselect a.VCHRNMBR,a.VENDORID,a.DOCTYPE,a.DOCDATE, a.POSTEDDT, a.DOCNUMBR,-a.DOCAMNT,case when b.VCHRNMBR is null then a.DOCAMNT else a.DOCAMNT-b.APPLDAMTend 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.VOIDEDfrom --PM30200 (selectVCHRNMBR,VENDORID,DOCTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM30200union selectVCHRNMBR,VENDORID,DOCTYPE,DOCDATE, POSTEDDT, DOCNUMBR,DOCAMNT,DEX_ROW_ID,DISCAMNT, DUEDATE, PYMTRMID, VOIDEDfrom PM20000)aleft join --PM30300 (select DOCTYPE, VCHRNMBR, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM30300 group by DOCTYPE, VCHRNMBRunionselect DOCTYPE, VCHRNMBR, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM20100 group by DOCTYPE, VCHRNMBR)b on a.VCHRNMBR=b.VCHRNMBR and a.DOCTYPE=b.DOCTYPE and a.DOCTYPE>4 and a.VOIDED=0where a.DOCDATE<=@CutoffDate and b.ApplyToGLPostDate<=@CutoffDate
Microsoft,Dynamics,SQL,Scripti