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.

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 )