Vendor Statement with Analytical Accounting Information

 

one of my clients is a constructions firm whose not interested to activate the project accounting and wanted to distribute their project management over analytical accounting dimensions.

Therefore we have implemented Dynamics GP and created dimension for projects to distribute all expenses accounts of analytical accounting codes.

One of their requirements been to have a vendor statement per project which is reasonable for such a line of business, therefore I have created the below view that extracts the information including from AP, GL and AA and wanted to share the idea to avoid reinventing the weel for people who works with the same situation, below is the script I used:

DECLARE @FROMDATE    DATETIME
DECLARE @TODATE        DATETIME
DECLARE @FROMVENDOR VARCHAR(MAX)
DECLARE @TOVENDOR    VARCHAR(MAX)

SET @FROMDATE    = '1900-01-01'
SET @TODATE        = '2017-04-17'
SET @FROMVENDOR = 'ACETRAVE0001'
SET @TOVENDOR    = 'BLOOMING0001'

SELECT 
'Beginning Balance'        AS TRXSOURCE, 
@FROMDATE                AS DOCDATE, 
'Beginning Balance'        AS DOCNUMBER, 
VENDORID, 
SUM(CREDIT)                AS CREDIT, 
SUM(DEBIT)                AS DEBIT, 
VENDNAME, 
'Beginning Balance'        AS VCHRNMBR, 
'Beginning Balance'        AS TRXDESC, 
0                        AS JRNENTRY, 
0                        AS aaGLHdrID, 
aaTrxDimCode, 
aaTrxDimCodeDescr,
'' AS LOCNCODE
FROM
(
SELECT DISTINCT 
VENDORTRANSACTION.TRXSOURCE, 
VENDORTRANSACTION.DOCDATE, 
VENDORTRANSACTION.DOCNUMBER, 
VENDORTRANSACTION.VENDORID, 
AAG3000240002.CRDTAMNT AS CREDIT, 
AAG3000240002.DEBITAMT AS DEBIT, 
VENDORTRANSACTION.VENDNAME, 
VENDORTRANSACTION.VCHRNMBR, 
VENDORTRANSACTION.TRXDESC, 
GL2000030000.JRNENTRY, 
dbo.AAG30000.aaGLHdrID, 
dbo.AAG00401.aaTrxDimCode, 
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE

FROM                 
        (SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
        UNION 
        SELECT AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
        ) AS AAG3000240002         
INNER JOIN
        (SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
        UNION 
        SELECT AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003
        ) AS AAG3000340003 
        ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID AND 
        AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID LEFT OUTER JOIN
        dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID         
RIGHT OUTER JOIN
        (SELECT aaGLHdrID, aaGLDistID FROM AAG30001
        UNION
        SELECT aaGLHdrID, aaGLDistID FROM AAG40001
        ) AS AAG3000140001 
        ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID 
RIGHT OUTER JOIN
        dbo.AAG30000 ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID 
RIGHT OUTER JOIN
(
SELECT     
    CASE 
        WHEN dbo.PM20000.DOCTYPE = 1 THEN 'Invoice' 
        WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges' 
        WHEN dbo.PM20000.DOCTYPE = 3 THEN 'Mis Charges' 
        WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return' 
        WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo' 
        WHEN dbo.PM20000.DOCTYPE = 6 THEN 'Payment' 
        END 
AS TRXSOURCE, 
'Open' AS Status, 

    CASE 
        WHEN dbo.PM20000.TRXDSCRN = '' THEN '-' 
        ELSE dbo.PM20000.TRXDSCRN 
        END 
AS TRXDESC, 
dbo.PM20000.DOCDATE, 
dbo.PM20000.VCHRNMBR, 
dbo.PM20000.VENDORID, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 1 THEN dbo.PM20000.DOCAMNT 
        WHEN dbo.PM20000.DOCTYPE = 2 THEN dbo.PM20000.DOCAMNT 
        WHEN dbo.PM20000.DOCTYPE = 3 THEN dbo.PM20000.DOCAMNT 
        END, 0) 
AS Credit, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 4 THEN dbo.PM20000.DOCAMNT 
        WHEN dbo.PM20000.DOCTYPE = 5 THEN dbo.PM20000.DOCAMNT 
        WHEN dbo.PM20000.DOCTYPE = 6 THEN dbo.PM20000.DOCAMNT 
        END, 0) 
AS Debit,
(SELECT     VENDNAME FROM         dbo.PM00200 WHERE     (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME, 
dbo.PM20000.CURNCYID, 
dbo.PM20000.DOCNUMBR AS DOCNUMBER, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        END, 0) 
AS ORCREDIT, 
ISNULL(CASE 
        WHEN dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
        WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
        END, 0) 
AS ORDEBIT
FROM         dbo.PM20000 
LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE     (dbo.PM20000.VOIDED <> 1)

UNION ALL

SELECT CASE 
        WHEN dbo.PM30200.DOCTYPE = 1 THEN 'Invoice' 
        WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges' 
        WHEN dbo.PM30200.DOCTYPE = 3 THEN 'Mis Charges' 
        WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return' 
        WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo' 
        WHEN dbo.PM30200.DOCTYPE = 6 THEN 'Payment' 
        END 
AS TRXSOURCE, 
'History' AS Status, 

CASE 
    WHEN dbo.PM30200.TRXDSCRN = '' THEN '-' 
    ELSE dbo.PM30200.TRXDSCRN 
    END 
AS TRXDESC, 
DOCDATE, 
VCHRNMBR, 
VENDORID, 

ISNULL(CASE 
        WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT 
        END, 0) 
AS Credit, 
ISNULL(CASE 
        WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT 
        END, 0) 
AS Debit,
(SELECT     VENDNAME FROM         dbo.PM00200 AS PM00200_1 WHERE     (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME, 
CURNCYID, 
DOCNUMBR, 
ISNULL(CASE 
        WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT 
        END, 0) 
AS ORCREDIT, 
ISNULL(CASE 
        WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT 
        WHEN dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT 
        END, 0) 
AS ORDEBIT
FROM         dbo.PM30200
WHERE     (VOIDED <> 1)) AS VENDORTRANSACTION 
LEFT OUTER JOIN dbo.POP30310 
INNER JOIN dbo.POP30300 
ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM 
ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR 
LEFT OUTER JOIN
(SELECT JRNENTRY, ORCTRNUM FROM GL20000 
UNION 
SELECT JRNENTRY, ORCTRNUM FROM GL30000) 
AS GL2000030000  
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON 
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY

WHERE     (VENDORTRANSACTION.VENDORID >= @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID <= @TOVENDOR) AND(AAG3000340003.aaTrxDimID = 3) AND VENDORTRANSACTION.DOCDATE <@FROMDATE
) AS SOURCE
GROUP BY 
VENDORID, 
VENDNAME, 
aaTrxDimCode, 
aaTrxDimCodeDescr

UNION ALL

SELECT DISTINCT 
VENDORTRANSACTION.TRXSOURCE, 
VENDORTRANSACTION.DOCDATE, 
VENDORTRANSACTION.DOCNUMBER, 
VENDORTRANSACTION.VENDORID, 
AAG3000240002.CRDTAMNT AS CREDIT, 
AAG3000240002.DEBITAMT AS DEBIT, 
VENDORTRANSACTION.VENDNAME, 
VENDORTRANSACTION.VCHRNMBR, 
VENDORTRANSACTION.TRXDESC, 
GL2000030000.JRNENTRY, 
dbo.AAG30000.aaGLHdrID, 
dbo.AAG00401.aaTrxDimCode, 
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE
FROM         
(SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
        UNION 
SELECT AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
) AS AAG3000240002 

INNER JOIN
(SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
        UNION 
SELECT AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003) AS AAG3000340003 
ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID 
    AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID 
    AND AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID 
LEFT OUTER JOIN
dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID 
    AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID 
RIGHT OUTER JOIN
(SELECT aaGLHdrID, aaGLDistID FROM AAG30001
UNION
SELECT aaGLHdrID, aaGLDistID FROM AAG40001
) AS AAG3000140001 
ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID 
RIGHT OUTER JOIN dbo.AAG30000 
ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID 
RIGHT OUTER JOIN
(SELECT     CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM20000.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM20000.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'Open' AS Status, 
CASE WHEN dbo.PM20000.TRXDSCRN = '' THEN '-' ELSE dbo.PM20000.TRXDSCRN END AS TRXDESC, dbo.PM20000.DOCDATE, 
dbo.PM20000.VCHRNMBR, dbo.PM20000.VENDORID, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN dbo.PM20000.DOCAMNT WHEN dbo.PM20000.DOCTYPE = 2 THEN dbo.PM20000.DOCAMNT WHEN
dbo.PM20000.DOCTYPE = 3 THEN dbo.PM20000.DOCAMNT END, 0) AS Credit, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 4 THEN dbo.PM20000.DOCAMNT WHEN dbo.PM20000.DOCTYPE = 5 THEN dbo.PM20000.DOCAMNT WHEN
dbo.PM20000.DOCTYPE = 6 THEN dbo.PM20000.DOCAMNT END, 0) AS Debit,
(SELECT     VENDNAME
FROM         dbo.PM00200
WHERE     (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME, dbo.PM20000.CURNCYID, dbo.PM20000.DOCNUMBR AS DOCNUMBER, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) END, 0) AS OrCredit, 
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) 
WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) END, 0) AS OrDebit
FROM         dbo.PM20000 LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE     (dbo.PM20000.VOIDED <> 1)
UNION ALL
SELECT     CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM30200.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM30200.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'History' AS Status, 
CASE WHEN dbo.PM30200.TRXDSCRN = '' THEN '-' ELSE dbo.PM30200.TRXDSCRN END AS TRXDESC, DOCDATE, VCHRNMBR, VENDORID, 
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT END, 0) AS Credit, 
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT END, 0) AS Debit,
(SELECT     VENDNAME
FROM         dbo.PM00200 AS PM00200_1
WHERE     (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME, CURNCYID, DOCNUMBR AS DOCNUMBER, 
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT END, 0) AS OrCredit, 
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT END, 0) AS OrDebit
FROM         dbo.PM30200
WHERE     (VOIDED <> 1)) AS VENDORTRANSACTION LEFT OUTER JOIN
dbo.POP30310 INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR LEFT OUTER JOIN
(SELECT JRNENTRY, ORCTRNUM FROM GL20000 
UNION 
SELECT JRNENTRY, ORCTRNUM FROM GL30000) 
AS GL2000030000 
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON 
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY
WHERE     (VENDORTRANSACTION.VENDORID > = @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID < = @TOVENDOR) AND (AAG3000340003.aaTrxDimID = 3) AND 
VENDORTRANSACTION.DOCDATE > = @FROMDATE AND VENDORTRANSACTION.DOCDATE <= @TODATE
ORDER BY DOCDATE

Hope that this helps.

---

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com