Balancing Payables to General Ledger Microsoft Dynamics GP

In this video I walk through the steps I use when I start a project balancing Payables to the General Ledger in Microsoft Dynamics GP.

Originally Posted August 8, 2013

In this video I walk through the steps I use when I start a project balancing Payables to the General Ledger in Microsoft Dynamics GP.

balance

 

Below is the view I created and use when I balance Payables to the General Ledger:

 

SELECT OP.vendorid AS Vendor_ID,
       OP.docdate  AS Document_Date,
       CASE OP.doctype
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Finance Charge'
         WHEN 3 THEN 'Misc Charge'
         WHEN 4 THEN 'Return'
         WHEN 5 THEN 'Credit Memo'
         WHEN 6 THEN 'Payment'
         ELSE ''
       END         AS Document_Type,
       OP.vchrnmbr AS Voucher,
       OP.docnumbr AS Document_Number,
       OP.docamnt  AS Document_Amount,
       GL.actnumst AS GL_Account,
       CASE OD.disttype
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Payable'
         WHEN 3 THEN 'Discount Available'
         WHEN 4 THEN 'Discount Taken'
         WHEN 5 THEN 'Finance Charge'
         WHEN 6 THEN 'Purchase'
         WHEN 7 THEN 'Trade Disc.'
         WHEN 8 THEN 'Misc. Charge'
         WHEN 9 THEN 'Freight'
         WHEN 10 THEN 'Taxes'
         WHEN 11 THEN 'Writeoffs'
         WHEN 12 THEN 'Other'
         WHEN 13 THEN 'GST Disc'
         WHEN 14 THEN 'PPS Amount'
         ELSE ''
       END         AS Distribution_Type,
       OD.debitamt AS Debit_Amount,
       OD.crdtamnt AS Credit_Amount,
       OP.ptdusrid AS Posted_By
FROM   dbo.pm20000 AS OP
       INNER JOIN dbo.pm10100 AS OD
               ON OP.vchrnmbr = OD.vchrnmbr
       INNER JOIN dbo.gl00105 AS GL
               ON OD.dstindx = GL.actindx
WHERE  ( OP.voided = 0 )
UNION
SELECT HP.vendorid AS Vendor_ID,
       HP.docdate  AS Document_Date,
       CASE HP.doctype
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Finance Charge'
         WHEN 3 THEN 'Misc Charge'
         WHEN 4 THEN 'Return'
         WHEN 5 THEN 'Credit Memo'
         WHEN 6 THEN 'Payment'
         ELSE ''
       END         AS Document_Type,
       HP.vchrnmbr AS Voucher,
       HP.docnumbr AS Document_Number,
       HP.docamnt  AS Document_Amount,
       GL.actnumst AS GL_Account,
       CASE HD.disttype
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Payable'
         WHEN 3 THEN 'Discount Available'
         WHEN 4 THEN 'Discount Taken'
         WHEN 5 THEN 'Finance Charge'
         WHEN 6 THEN 'Purchase'
         WHEN 7 THEN 'Trade Disc.'
         WHEN 8 THEN 'Misc. Charge'
         WHEN 9 THEN 'Freight'
         WHEN 10 THEN 'Taxes'
         WHEN 11 THEN 'Writeoffs'
         WHEN 12 THEN 'Other'
         WHEN 13 THEN 'GST Disc'
         WHEN 14 THEN 'PPS Amount'
         ELSE ''
       END         AS Distribution_Type,
       HD.debitamt AS Debit_Amount,
       HD.crdtamnt AS Credit_Amount,
       HP.ptdusrid AS Posted_By
FROM   dbo.pm20000 AS HP
       INNER JOIN dbo.pm10100 AS HD
               ON HP.vchrnmbr = HD.vchrnmbr
       INNER JOIN dbo.gl00105 AS GL
               ON HD.dstindx = GL.actindx
WHERE  ( HP.voided = 0 )

Author: Belinda Allen

Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 24 years. Belinda began implementing ERP systems so long ago that Windows was not an operating system, but an application. Larger businesses used old Main Frames with monitors that were Green and Black and smaller business did accounting by hand. Having seen the evolution that has taken place over the years from pieces of paper to analytics, Belinda still gets excited every time she helps a business improve. Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.” In her time off, Belinda enjoys sailing, woodturning, crochet/knitting, sewing/quilting, reading and playing the ukulele (Belinda often participates in Ukulele Jams in NYC.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s