dd_PMApply_SEL

It's much easier (faster) to work if you have a query already written when you start. This is a template query that starts with the PM apply table and joins in the check and the voucher, and then shows the purchasing distribution.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

declare @VendorIDStart varchar(15)
declare @VendorIDEnd varchar(15)
 
select
       @VendorIDStart = '021685',
       @VendorIDEnd = '021685'
 
SELECT
              inv.VendorID,
              v.VENDNAME,
              chk.CHEKBKID as CheckbookID,
              --apy.VCHRNMBR,
              apy.APFRDCNM as CheckNumber,
              apy.ApplyFromGLPostDate as CheckDate,
              chk.DOCAMNT as CheckAmount,
              apy.aptvchnm as InvVoucherNumber,
 
              --apy.APTODCTY,
              apy.APTODCNM as InvoiceNumber,
              inv.DOcdate as InvoiceDate,
              inv.DOCAMNT as InvoiceAmount,
              --apy.DOCTYPE,
              'PURCH' AS DistType,
 
              --apy.APPLDAMT,
              g5.ACTNUMST as Account,
              g0.ACTDESCR as AccountDesc,
              dist.DEBITAMT as DebitAmt,
              dist.CRDTAMNT as CreditAmt
       FROM PM30300 apy
              left join (
                     select vchrnmbr, doctype, DOCAMNT, CHEKBKID
                           from pm20000 pt
                     union all
                     select vchrnmbr, doctype, DOCAMNT, CHEKBKID
                           from pm30200 pt
                     ) chk on chk.VCHRNMBR = apy.VCHRNMBR and chk.DOCTYPE = apy.doctype
              left join (
                     select vchrnmbr, doctype, DOCAMNT,pt.VENDORID, docdate
                           from pm20000 pt
                     union all
                     select vchrnmbr, doctype, DOCAMNT, pt.VENDORID, DOCDATE
                           from pm30200 pt
                     ) inv on inv.VCHRNMBR = apy.APTVCHNM and inv.DOCTYPE = apy.APTODCTY
              left join pm00200 v on v.VENDORID = inv.VENDORID
              left join (
                     select vchrnmbr, 1 as doctype, DEBITAMT, CRDTAMNT,DSTINDX, DISTTYPE
                           from pm10100 dist
                     union all
                     select vchrnmbr, doctype, DEBITAMT, CRDTAMNT,DSTINDX, DISTTYPE
                           from pm30600 dist
              ) dist on dist.VCHRNMBR = apy.APTVCHNM and dist.doctype = apy.APTODCTY and dist.DISTTYPE = 6
              left join gl00105 g5 on g5.ACTINDX = dist.DSTINDX
              left join gl00100 g0 on g0.ACTINDX = dist.DSTINDX
       WHERE apy.VENDORID between @VendorIDStart and @VendorIDEnd
              AND APFRDCNM like '%0002'

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences