Inventory Levels

SELECT TOP 100 PERCENT dbo.IV00101.ITMCLSCD, dbo.IV00101.ITEMDESC, dbo.IV00101.ITEMNMBR, dbo.IV40201.BASEUOFM, dbo.IV00101.ITMGEDSC, dbo.IV00102.LOCNCODE, dbo.IV00102.QTYONHND, dbo.IV00102.ATYALLOC, dbo.IV00102.QTYCOMTD, ISNULL(dbo.IV00102.QTYONHND, 0.00000) - ISNULL(dbo.IV00102.ATYALLOC, 0.00000) - ISNULL(dbo.IV00102.QTYCOMTD, 0.00000) AS QTYAVAIL, dbo.IV00101.STNDCOST * dbo.IV00102.QTYONHND AS VALUEQTYONHND, dbo.IV00101.STNDCOST * dbo.IV00102.ATYALLOC AS VALUEATYALLOC, dbo.IV00101.STNDCOST * dbo.IV00102.QTYCOMTD AS VALUEQTYCOMTD, dbo.IV00101.STNDCOST * (ISNULL(dbo.IV00102.QTYONHND, 0.00000) - ISNULL(dbo.IV00102.ATYALLOC, 0.00000) - ISNULL(dbo.IV00102.QTYCOMTD, 0.00000)) AS VALUEQTYAVAILFROM dbo.IV00101 LEFT OUTER JOIN dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR LEFT OUTER JOIN dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDLWHERE (dbo.IV00102.RCRDTYPE = 2) AND (dbo.IV00102.QTYONHND + dbo.IV00102.ATYALLOC + dbo.IV00102.QTYCOMTD <> 0)ORDER BY dbo.IV00101.STNDCOST * (ISNULL(dbo.IV00102.QTYONHND, 0.00000) - ISNULL(dbo.IV00102.ATYALLOC, 0.00000) - ISNULL(dbo.IV00102.QTYCOMTD, 0.00000)) DESC

Payables Transactions not in GL

Picture this: the auditors are in, they asking for myriads of reports, and precisely one of the things they ask is something you cannot easily achieved from the slur of reports and SmartLists available from within Microsoft Dynamics GP. To make matters worst, the "go to" guy for queries and special requests is out of the office sick with the flu. Well worry no more! If you are asked to show all the payables transactions that were never recorded or posted to GL you can run the following query from SQL Server Management Studio (or Query Analyzer if you still happen to be on SQL Server 2000):

SELECT
   a.vendorid,
   a.docnumbr,
   a.docdate,
   a.cntrlnum,
   gl.jrnentry,
   gl.trxdate,
   gl.actindx,
   c.actnumst,
   d.actdescr,
   gl.refrence,
   gl.ortrxtyp,
   gl.orctrnum,
   gl.ormstrid,
   gl.ormstrnm,
   gl.ordocnum
FROM pm00400 a left outer join
  ( SELECT jrnentry, trxdate, refrence, ortrxtyp, actindx, orctrnum, ormstrid,
      ormstrnm, ordocnum
    FROM dbo.GL20000
    WHERE series = 4

    UNION ALL

    SELECT jrnentry, trxdate, refrence, ortrxtyp, actindx, orctrnum, ormstrid,
      ormstrnm, ordocnum
    FROM GL30000
    WHERE series = 4
  ) gl ON(a.vendorid = gl.ormstrid) and (a.cntrlnum = gl.orctrnum) and (a.docnumbr = gl.ordocnum)
LEFT OUTER JOIN GL00105 c ON (gl.actindx = c.actindx)
LEFT OUTER JOIN GL00100 d ON (gl.actindx = d.actindx)

Knowing what transactions have not been posted or recorded in GL is particularly useful when attempting to balance GL and AP. It will also allow you to identify those records loaded as begining balances from within AP.

With little or no effort, this query can be added to SmartList Builder (SLB), by creating a SQL Server view with the inner query, and exposing the view to SLB. Don't forget to run the Grant.sql query to assign permissions to the DYNGRP.

Where is my company ID?

SELECT * FROM DYNAMICS.dbo.SY01500