In this post, an SQL script to link sales invoices and returns from the Sales Order Processing Module with associated journal entries from the General Ledger module.
Tables Included:
· SOP30200 | Sales Transactions History
· SOP30300 | Sales Transactions Amounts History
· GL00100 | Account Master
· GL00105 | Account Index Master
· GL20000 | Year to Date Transactions Open
· GL30000 | Account Transaction History
SELECT *
FROM ( SELECT CASE A.SOPTYPE
WHEN 1 THEN 'Quote'
WHEN 2 THEN 'Order'
WHEN 3 THEN 'Invoice'
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Back Order'
WHEN 6 THEN 'Fulfillment Order'
ELSE ''
END AS SOPTYPE ,
A.SOPNUMBE ,
A.CUSTNMBR ,
A.TRXSORCE ,
A.CUSTNAME ,
A.DOCDATE ,
A.GLPOSTDT ,
B.ITEMNMBR ,
B.ITEMDESC ,
B.QUANTITY ,
B.NONINVEN ,
B.UOFM ,
B.LOCNCODE ,
B.INVINDX ,
B.UNITCOST ,
B.UNITPRCE ,
B.XTNDPRCE ,
B.EXTDCOST ,
GLMS.ACTNUMST AS InventoryAccount ,
GLMS.ACTDESCR AS InventoryAccountDescription
FROM SOP30200 AS A
LEFT OUTER JOIN dbo.SOP30300 AS B
ON A.SOPNUMBE = B.SOPNUMBE
LEFT OUTER JOIN ( SELECT X.ACTINDX ,
X.ACTDESCR ,
Y.ACTNUMST
FROM dbo.GL00100 AS X
LEFT OUTER JOIN
dbo.GL00105 AS Y
ON X.ACTINDX = Y.ACTINDX
) AS GLMS ON GLMS.ACTINDX = B.INVINDX
WHERE A.SOPTYPE IN ( 3, 4 )
) AS SLS
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 = 3
UNION ALL
SELECT SERIES ,
JRNENTRY ,
DEBITAMT ,
CRDTAMNT ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
FROM dbo.GL30000
WHERE SERIES = 3
) AS GL
GROUP BY SERIES ,
JRNENTRY ,
ORGNTSRC ,
ORCTRNUM ,
ORDOCNUM ,
ORMSTRID
) AS GL ON SLS.TRXSORCE = GL.ORGNTSRC
AND SLS.SOPNUMBE = GL.ORDOCNUM
AND SLS.SOPNUMBE = GL.ORCTRNUM
AND SLS.CUSTNMBR = GL.ORMSTRID
Best Regards,
Mahmoud M. AlSaadi
No comments:
Post a Comment