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.
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 )
Belinda Allen, Microsoft MVP Business Solutions 