Announcements
*This post is locked for comments
Hi,
I think Victoria Yudin has exactly what you need here:
victoriayudin.com/.../sql-view-to-show-all-gl-distributions-for-ap-transactions
You will need to create a SQL view using Victoria's query, but it has it all. If you want to build it yourself, don't forget you will also need the GL Account Master for the Account Number and description.
Kind regards,
Leslie
PS Here is Victoria's script.
CREATE VIEW view_AP_Distributions
AS
--************************************************
--view_AP_Distributions
--Shows GL distributions for all AP transactions
--Created Nov 28 2008 by Victoria Yudin, Flexible Solutions
--For updates see victoriayudin.com/gp-reports
--Does not exclude voided transactions
--Transactions with no MC information will show
-- an exchange rate of 0
--Updated Feb 11, 2009 to correct link to GL account number
--Updated May 13, 2009 to add distribution reference
--Updated Nov 10, 2009 to add GP posting date and more
-- user friendly column names
--Updated Jan 22, 2010 to add Document Date
--Updated Mar 10, 2010 to add Batch ID, Trx Description,
-- and include unposted transactions
--Updated May 23, 2010 to add Voucher Number
--Updated Jun 25, 2010 to add additional distribution types,
-- currency, exchange rate and originating amounts
--Updated Jun 27, 2010 to fix copying issue
--Updated Jun 30, 2010 to add Voided field
--Updated Dec 17, 2010 to add Vendor Name & GL Account Name
--Updated Jan 31, 2011 to add ROUND distribution type
--Updated Mar 8, 2011 to add PM10300 and PM10400 tables
--Updated Dec 20, 2011 to add PO Number
--*************************************************
SELECT D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
D.PSTGDATE GL_Posting_Date,
K.DOCDATE Document_Date,
CASE K.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'
END Document_Type,
G.ACTNUMST Account_Number,
A.ACTDESCR Account_Name,
CASE D.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'
WHEN 16 THEN 'Round'
WHEN 17 THEN 'Realized Gain'
WHEN 18 THEN 'Realized Loss'
WHEN 19 THEN 'Due To'
WHEN 20 THEN 'Due From'
END Distribution_Type,
D.DEBITAMT Debit_Amount,
D.CRDTAMNT Credit_Amount,
D.DistRef Distribution_Reference,
T.BACHNUMB Batch_ID,
T.TRXDSCRN Trx_Description,
T.STAT Trx_Status,
D.VCHRNMBR Voucher_Number,
D.CURNCYID Currency_ID,
coalesce(D.XCHGRATE,0) Exchange_Rate,
D.ORDBTAMT Originating_Debit_Amount,
D.ORCRDAMT Originating_Credit_Amount,
CASE T.VOIDED
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END Voided,
T.PORDNMBR PO_Number
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE, CURNCYID,
ORDBTAMT, ORCRDAMT, XCHGRATE
FROM PM10100
UNION ALL
-- all historical trx distributions
SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT,
P.CRDTAMNT, P.DSTINDX, P.DISTTYPE, P.DistRef,
P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT,
M.XCHGRATE
FROM PM30600 P
LEFT OUTER JOIN -- historical exchange rate
MC020103 M
ON P.VCHRNMBR = M.VCHRNMBR
AND P.DOCTYPE = M.DOCTYPE) D
-- add document number and type
LEFT OUTER JOIN
PM00400 K
ON D.VCHRNMBR = K.CNTRLNUM
AND D.CNTRLTYP = K.CNTRLTYP
--add GL account number
LEFT OUTER JOIN
GL00105 G
ON D.DSTINDX = G.ACTINDX
--add status, batch and trx description
LEFT OUTER JOIN
(SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR
FROM PM10000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Open', VOIDED, PORDNMBR
FROM PM20000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'History', VOIDED, PORDNMBR
FROM PM30200
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
STAT = 'Work', VOIDED = 0, PORDNMBR = ''
FROM PM10300
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR = ''
FROM PM10400) T
ON T.VCHRNMBR = D.VCHRNMBR
AND T.CNTRLTYP = D.CNTRLTYP
--add GL account name
LEFT OUTER JOIN
GL00100 A
ON A.ACTINDX = D.DSTINDX
--add vendor name
LEFT OUTER JOIN
PM00200 N
ON N.VENDORID = D.VENDORID
--the following will grant permissions to this view to DYNGRP
--leave this section off if you do not want to grant permissions
GO
GRANT SELECT ON view_AP_Distributions TO DYNGRP
Create a new SQL view and add it into GP smartlist.
Use the below distribution tables for Payables Transactions to create the SQL view.
PM10000 - Work/Unposted Transactions
PM20000 – Open/Posted Transactions
PM30200 – Historical/Paid Transactions
PM10100 – GL Distributions for Work and Open Transactions
PM30600 – GL Distributions for Historical Transactions
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
