In this post, an SQL script is provided to link purchasing transactions from the Purchase Order Processing Module with associated journal entries from the General Ledger module.
Tables Included:
· POP30300 | Purchasing Receipt History
· POP10500 | Purchasing Receipt Line Quantities
· GL20000 | Year to Date Transaction Open
· GL30000 | Account Transaction History
SELECT *
FROM ( SELECT B.PONUMBER ,
B.VENDORID ,
B.POPRCTNM ,
A.receiptdate ,
A.TRXSORCE ,
CASE B.POPTYPE
WHEN 1 THEN 'Shipment'
WHEN 2 THEN 'Invoice'
WHEN 3 THEN 'Shipment-Invoice'
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Return with Credit'
WHEN 6 THEN 'Inventory Return'
WHEN 7 THEN 'Inventory Return with Credit'
WHEN 8 THEN 'InTransit'
ELSE ''
END AS POPTYPE ,
B.QTYSHPPD ,
B.QTYINVCD ,
B.ITEMNMBR ,
B.TRXLOCTN ,
B.UOFM ,
B.DATERECD ,
B.INVINDX ,
B.ACPURIDX ,
B.OREXTCST
FROM dbo.POP30300 AS A
LEFT OUTER JOIN dbo.POP10500 AS B
ON A.POPRCTNM = B.POPRCTNM
AND A.VENDORID = B.VENDORID
) AS POP
LEFT OUTER JOIN ( SELECT SERIES ,
JRNENTRY ,
SUM(DEBITAMT) AS DEBIT ,
SUM(CRDTAMNT) AS CREDIT ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
FROM ( SELECT SERIES ,
JRNENTRY ,
DEBITAMT ,
CRDTAMNT ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
FROM dbo.GL20000
WHERE SERIES = 4
UNION ALL
SELECT SERIES ,
JRNENTRY ,
DEBITAMT ,
CRDTAMNT ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
FROM dbo.GL30000
WHERE SERIES = 4
) AS GL
GROUP BY SERIES ,
JRNENTRY ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
) AS GL ON ( GL.ORGNTSRC = POP.TRXSORCE
AND GL.ORDOCNUM = POP.POPRCTNM
AND GL.ORCTRNUM = POP.POPRCTNM
AND GL.ORMSTRID = POP.VENDORID
)
Best Regards,
Mahmoud M. AlSaadi
Thank you for the script. Please advise how do I add the GL account as well?
ReplyDeleteWhich GL Accounts ?
DeleteBest Regards,
Mahmoud M. AlSaadi