Number to Words in Report Writer

By David Musgrave

One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words. After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters. This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.

This example using the "Check with Stub on Top" report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation. The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.

The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table. When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.

NOTE: This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Example code for v8.0, v9.0 & v10.0 is found under the following link:
http://blogs.msdn.com/developingfordynamicsgp/attachment/8968837.ashx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Microsoft Dynamics GP Service Pack 3!

Link below will redirect you to the download page for GP SP3:

https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP10_ServicePack3.htm

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Filter Dynamics GP Lookups Using VBA

Guys,

A got allot of mails requesting to have Item Security functionality to distribute sites items and show only certain items for the warehouse keeper.

For those who are looking for such functionality, you need to follow the steps below:

1. Create a new table in the database that contains “User ID”, “Item Number” fields. This table will hold the items that will be mapped to each user.
2. Add “Items Lookup” form to VBA and include Item Number field.
3. Get the current connected user from Globals.
4. Loop the following code on all the items returned for the logged user:

Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
If ItemNumber.Value “Does not exist in our table” Then
RejectLine = True
End If
End Sub

Please let me know if you may need any help, this code is applicable for all lookup forms and details windows.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Filling Missing Transactions Numbers in GP

If the end user created any kind of transactions in GP that holds let’s say the ID 9 and another one that holds 10, then deleted 9; the next number will be 11 and 9 will be ignored.

For the clients that do not accept these gaps, and since almost all of GP transaction tables have setups for loading next ID number, I have created the following solution:

Create a trigger on your transactions table For INSERT and DELETE that perform the following actions:

UPDATE “Setup-Table” SET “Next-ID” =

(SELECT TOP 1 Covert(BIGINT, A.Number) + 1 As NextID From Table As A LEFT OUTER JOIN Table As B on Covert(BIGINT, A.Number) + 1 = Covert(BIGINT, B.Number) WHERE B.Number IS NULL)

WHERE SETUPKEY = XXX

Replace the “Number” with column name and Table with your table name.
Now each time a transaction added or deleted, your trigger will get the next available number and update the defined next number.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Dynamics GP, AX, SL, NAV into one ERP called Project Green?

Project Green is Dead!

Project Green started making headlines in late summer 2003, a few months before Microsoft made the formal announcement of what was supposed to become a horizontally integrated ERP suite. Project Green would do for ERP applications what bundling did for Office: put together disparate but related applications in a tidy, integrated package and eventually bring them to a single code base.

But Project Green seemed doomed right from its conception, with the horizontal-bundling task running into logistical and management problems. What looked good in concept proved harder to execute, and the channel may have been one of the major reasons.

http://www.eweek.com/c/a/Enterprise-Applications/Project-Green-is-Dead151for-the-Foreseeable-Future-at-Least/
http://fscavo.blogspot.com/2007/03/microsofts-project-green-is-dead.html
http://www.microsoft-watch.com/content/business_applications/rip_project_green_2003_2007.html


Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

DEX.INI Commands Explanation

Interested to learn more about DEX.ini file and what does it effect? I found a post under (http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-dexini-settings.html) and found it interesting, below the parameters definition for DEX.ini File:

SuppressChangeDateDialog=TRUE - Turns off the Date Change dialogue that opens at midnight
Synchronize = TRUE - Forces a synchronization of the chart of accounts format
OLEPath=\\server\folder\ole - Sets the path for linked and embedded files. This is often set up inconsistently in the initial setup. You'll need to include your path in place of file://server/folder/ole
NoPrintDialogs=TRUE - Suppress the print dialogue box
ShowResids=TRUE - Make resource ID's visible in Dexterity
SQLLogSQLStmt=TRUESQLLogODBCMessages=TRUESQLLogAllODBCMessages=TRUE - Turn on Dex SQL Logging (all 3 lines)
ShowAdvancedMacroMenu=TRUE - Turn on the Advanced Macro Menu
C:\DPS1\DEX.INI DPSInstance=1C:\DPS2\DEX.INI DPSInstance=2C:\DPS3\DEX.INI DPSInstance=3 - Run multiple process servers on a single machine. Specifics on CustomerSource here. (One line for each process server)
AutoInstallChunks=TRUE - Add CNK files without the "Add New Code?" dialogue
ExportOneLineBody=TRUE - eliminate line wrapping when exporting reports to comma or tab delimited files.
IMPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IM.EXEIMExecPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IMRun.EXE - Set the path to Integration Manager so it will launch properly from the menu. (2 lines)
WindowMax=TRUE - Open the GP main window full screen
SampleDateMsg=FALSE - prevents the sample company dialog box from being displayed
SampleDateMMDDYYYY=00000000 - Prevents the sample company dialogue box from displaying and sets the date to the current date.
SampleDateMMDDYYYY=MMDDYYYY - Prevents the sample company dialogue box from displaying and sets the sample company to the date defined. (5/1/08 would be 050102008).

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

List of inventory items that have not sold between two dates

View below list the inventory items that have not sold between two dates:

SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
AND dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
WHERE (dbo.SOP30200.DOCDATE BETWEEN
CONVERT(DATETIME, '2006-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-10-30 00:00:00', 102))
))
AND (dbo.IV00102.QTYONHND > 0)

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Item Transactions Analysis

Interested in analyzing your items transaction? Check view below:

SELECT
dbo.SOP10100.DOCDATE,
dbo.SOP10200.LOCNCODE as TRXLOCTN,
CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN 'Return Unposted' When dbo.SOP10200.SOPTYPE = 3 Then 'Sales Unposted' end AS TrxType,
dbo.SOP10200.SOPNUMBE as DOCNUMBR,
dbo.SOP10200.ITEMNMBR,
dbo.SOP10200.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP10200.SOPTYPE = 3 THEN dbo.SOP10200.QUANTITY ELSE 0 END), 0) AS QTYOUT
FROM dbo.SOP10200 INNER JOIN
dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 ON dbo.SOP10200.SOPNUMBE = dbo.SOP10100.SOPNUMBE

UNION ALL


SELECT dbo.SOP30200.DOCDATE, dbo.SOP30300.LOCNCODE as TRXLOCTN, CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN 'Sales Posted' WHEN dbo.SOP30300.SOPTYPE = 4 THEN 'Return Posted' end AS TrxType, dbo.SOP30300.SOPNUMBE as DOCNUMBR, dbo.SOP30300.ITEMNMBR, dbo.SOP30300.ITEMDESC,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 4 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYIN,
ISNULL((CASE WHEN dbo.SOP30300.SOPTYPE = 3 THEN dbo.SOP30300.QUANTITY ELSE 0 END), 0) AS QTYOUT

FROM dbo.SOP30300 INNER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.SOP30200 ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Adjustment Unposted' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV10001.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY > 0 THEN dbo.IV10001.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN dbo.IV10001.TRXQTY < 0 THEN ABS(dbo.IV10001.TRXQTY) ELSE 0 END, 0) AS QTYOUT

FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 1)or (dbo.IV10001.IVDOCTYP = 2)
UNION ALL


SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Adjustment Posted' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR, dbo.IV00101.ITEMDESC,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) > 0 THEN dbo.IV30300.TRXQTY ELSE 0 END, 0) AS QTYIN,
ISNULL(CASE WHEN (dbo.IV30300.TRXQTY) < 0 THEN ABS(dbo.IV30300.TRXQTY) ELSE 0 END, 0) AS QTYOut

FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 1) or (dbo.IV30300.DOCTYPE = 2)
UNION ALL


SELECT dbo.POP10300.receiptdate as DOCDATE , dbo.POP10310.LOCNCODE as TRXLOCTN,
'Unposted Purchasing' AS TrxType, dbo.POP10300.POPRCTNM as DOCNUMBR, dbo.POP10310.ITEMNMBR,
dbo.POP10310.ITEMDESC,
CASE WHEN dbo.POP10300.POPTYPE <> 2 and dbo.POP10310.UNITCOST <>0 THEN (ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP10300.POPTYPE = 2 and dbo.POP10310.UNITCOST <>0 THEN ISNULL(dbo.POP10310.EXTDCOST / dbo.POP10310.UNITCOST, 0)
ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP10310 ON dbo.IV00101.ITEMNMBR = dbo.POP10310.ITEMNMBR INNER JOIN
dbo.POP10300 ON dbo.POP10310.POPRCTNM = dbo.POP10300.POPRCTNM
UNION ALL

SELECT dbo.POP30300.receiptdate as DOCDATE, dbo.POP30310.LOCNCODE as TRXLOCTN, 'Posted Purchasing' AS TrxType, dbo.POP30300.POPRCTNM as DOCNUMBR, dbo.POP30310.ITEMNMBR,
dbo.POP30310.ITEMDESC,
CASE WHEN dbo.POP30300.POPTYPE <> 2 and dbo.POP30310.UNITCOST <>0 THEN (ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0))
ELSE 0 END AS QTYIN,
CASE WHEN dbo.POP30300.POPTYPE = 2 and dbo.POP30310.UNITCOST <>0 THEN ISNULL(dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST, 0)ELSE 0 END AS QTYOUT
FROM dbo.IV00101 INNER JOIN
dbo.POP30310 ON dbo.IV00101.ITEMNMBR = dbo.POP30310.ITEMNMBR INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM

UNION ALL

SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRNSTLOC as TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10001.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR

WHERE (dbo.IV10001.IVDOCTYP = 3)

UNION ALL
SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRNSTLOC as TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV30300.ITEMNMBR,
dbo.IV00101.ITEMDESC, ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYIN, 0 AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

UNION ALL
SELECT dbo.IV10000.DOCDATE, dbo.IV10001.TRXLOCTN, 'Unposted Transfer' AS TrxType, dbo.IV10000.IVDOCNBR as DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN, ISNULL(dbo.IV10001.TRXQTY, 0) AS QTYOUT
FROM dbo.IV10001 INNER JOIN
dbo.IV00101 ON dbo.IV10001.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV10000 ON dbo.IV10001.IVDOCNBR = dbo.IV10000.IVDOCNBR
WHERE (dbo.IV10001.IVDOCTYP = 3)


UNION ALL

SELECT dbo.IV30300.DOCDATE, dbo.IV30300.TRXLOCTN, 'Posted Transfer' AS TrxType, dbo.IV30300.DOCNUMBR, dbo.IV00101.ITEMNMBR,
dbo.IV00101.ITEMDESC, 0 AS QTYIN , ISNULL(dbo.IV30300.TRXQTY, 0) AS QTYOUT
FROM dbo.IV30300 INNER JOIN
dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.IV30300.DOCTYPE = 3)

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Drop and recreate database Logins

Script below loop database users then drop and recreate them one by one:

DECLARE @UserName Varchar(500)
DECLARE UsersCurr Cursor For Select Name FROM sysusers WHERE (islogin = 1) AND (name <> 'dbo') AND (name <> 'guest')
Open UsersCurr
Fetch Next From UsersCurr Into @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_revokedbaccess @UserName
EXEC sp_droplogin @UserName
EXEC sp_addlogin @UserName, '123'
EXEC sp_grantdbaccess @UserName, @UserName
EXEC sp_addrolemember 'DYNGRP', @UserName
Fetch Next From UsersCurr Into @UserName
END
Close UsersCurr
DEALLOCATE UsersCurr

You can modify the above script to fit your needs.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Dynamically Get GP database connection information when using VBA

Dynamically Get GP database connection information when using VBA:

'Class Module: GP Dex_VBA_Link
'Author: Matt Connor
'Version: 2.01
'Updated: 06-Apr-2006

'Connection Properties
Public PasswordUnencrypted As Boolean
Public SQLPassword, SQLPasswordDecoded As String
Public UserID, SQLDataSourceName, RuntimeVersion As String
Public IntercompanyID, CompanyName As String
Public UserDate As Date

Private GP_App As Object
Private GP_Product As Object


Private Sub Get_GP_Info()
'Gets connection properties from dexterity and assigns to class properties
On Error GoTo ErrorHandler
Dim dex As String, dex_error_msg As String, MsgBoxTitle As String
MsgBoxTitle = "Get_GP_Info"
Set GP_App = CreateObject("Dynamics.Application")
GP_App.CurrentProductID = 0
GP_App.SetParamHandler Me
dex = ""
dex = dex & "local boolean Dummy, PasswordUnencrypted; "
dex = dex & "local string SQLPassword, SQLPasswordDecoded; "
dex = dex & "local string UserID, SQLDataSourceName, IntercompanyID, CompanyName, RuntimeVersion; "
dex = dex & "local date UserDate; "
dex = dex & " "
dex = dex & "set UserID to 'User ID' of globals; "
dex = dex & "set PasswordUnencrypted to 'Password Unencrypted' of globals; "
dex = dex & "set SQLPassword to 'SQLPassword' of globals; "
dex = dex & "set SQLPasswordDecoded to Utility_DecodeString(SQLPassword); "
dex = dex & "set IntercompanyID to 'Intercompany ID' of globals; "
dex = dex & "set CompanyName to 'Company Name' of globals; "
dex = dex & "set SQLDataSourceName to 'SQLDataSourceName' of globals; "
dex = dex & "set UserDate to 'User Date' of globals; "
dex = dex & "set RuntimeVersion to Runtime_GetVersionNum(); "
dex = dex & " "
dex = dex & "set Dummy to OLE_SetProperty(""UserID"", UserID); "
dex = dex & "set Dummy to OLE_SetProperty(""PasswordUnencrypted"", str(PasswordUnencrypted)); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLPassword"", SQLPassword); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLPasswordDecoded"", SQLPasswordDecoded); "
dex = dex & "set Dummy to OLE_SetProperty(""IntercompanyID"", IntercompanyID); "
dex = dex & "set Dummy to OLE_SetProperty(""CompanyName"", CompanyName); "
dex = dex & "set Dummy to OLE_SetProperty(""SQLDataSourceName"", SQLDataSourceName); "
dex = dex & "set Dummy to OLE_SetProperty(""RuntimeVersion"", RuntimeVersion); "
dex = dex & "set Dummy to OLE_SetProperty(""UserDate"", str(UserDate)); "
dex_error_msg = ""
GP_App.ExecuteSanscript dex, dex_error_msg
If dex_error_msg <> "" Then
msg = "An unexpected error has occurred running Dexterity script:" & vbCrLf & vbCrLf & dex_error_msg
MsgBox msg, vbCritical, MsgBoxTitle
Exit Sub
End If
Set GP_App = Nothing
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Else
MsgBox "An unexpected error has occurred:" & vbCr & vbCr & Err.Source & vbCr & "Error " & Err.Number & vbCr & Err.Description, vbCritical, MsgBoxTitle
End Select
End Sub

Note: Applicable on versions earlier than GP 10.0.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

GL Query For Posted and Unposted GL Transactions

View Trial Balance for Posted and Unposted GL Transactions:

SELECT
'HISTORY' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL30000.JRNENTRY,
DBO.GL30000.ACTINDX,
DBO.GL30000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL30000
INNER JOIN DBO.DTA10100 ON DBO.GL30000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL30000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL30000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID


UNION ALL

SELECT
'POSTED' AS POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL10001.JRNENTRY,
DBO.GL10001.ACTINDX,
DBO.GL10000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORCRDAMT,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORDBTAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
GL10000.TRXDATE
FROM DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.GL10001 ON DBO.GL00105.ACTINDX = DBO.GL10001.ACTINDX
INNER JOIN DBO.DTA10100
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID AND DBO.DTA10100.DOCNUMBR = DBO.DTA10200.DOCNUMBR AND DBO.DTA10100.RMDTYPAL = DBO.DTA10200.RMDTYPAL ON DBO.GL10001.JRNENTRY = DBO.DTA10100.JRNENTRY AND DBO.GL10001.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL10001.SQNCLINE = DBO.DTA10100.SEQNUMBR
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID
INNER JOIN DBO.GL10000 ON DBO.GL10001.JRNENTRY = DBO.GL10000.JRNENTRY

/*WHERE DBO.GL10000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL00100.ACTINDX IN (5)"*/

UNION ALL

SELECT
'UNPOSTED' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL20000.JRNENTRY,
DBO.GL20000.ACTINDX,
DBO.GL20000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL20000
INNER JOIN DBO.DTA10100 ON DBO.GL20000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL20000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL20000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID

/* WHERE DBO.GL20000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL20000.ACTINDX IN (5) */


Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/

Vendor Statement For Dynamics GP

Looking for a complete Vendor Statement that displays Posted and unposted transactions? Check the view below:

----------POP UNPOSTED----------------------------
SELECT
'RECIEVING UNPOSTED' AS TRXSOURCE,
dbo.POP10300.RECEIPTDATE AS DOCDATE,
dbo.POP10300.VNDDOCNM AS DOCNUMBR,
dbo.POP10300.VENDORID,
dbo.POP10300.SUBTOTAL-dbo.POP10300.TRDISAMT+dbo.POP10300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP10300
INNER JOIN dbo.PM00200 ON dbo.POP10300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
--------------------POP POSTED---------------------------------
SELECT
'RECIEVING POSTED' AS TRXSOURCE,
dbo.POP30300.RECEIPTDATE AS DOCDATE,
dbo.POP30300.POPRCTNM AS DOCNUMBR,
dbo.POP30300.VENDORID,
dbo.POP30300.SUBTOTAL-dbo.POP30300.TRDISAMT+dbo.POP30300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP30300
INNER JOIN dbo.PM00200 ON dbo.POP30300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
-------------------PAYMENT UNPOSTED--------------------
SELECT
'PAYMENT UNPOSTED' AS TRXSOURCE,
dbo.PM10400.DOCDATE ,
dbo.PM10400.PMNTNMBR AS DOCNUMBR,
dbo.PM10400.VENDORID,
0 AS CREDIT,
dbo.PM10400.DOCAMNT AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10400
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10400.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED-------------------------
SELECT
CASE

WHEN dbo.PM20000.DOCTYPE=1 THEN 'INVOICE POSTED'
WHEN dbo.PM20000.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=3 THEN 'MIS CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=4 THEN 'RETURN POSTED'
WHEN dbo.PM20000.DOCTYPE=5 THEN 'CREDIT MEMO POSTED'
WHEN dbo.PM20000.DOCTYPE=6 THEN 'PAYMENT POSTED'
END AS TRXSOURCE,
dbo.PM20000.DOCDATE,
dbo.PM20000.DOCNUMBR AS DOCNUMBR,
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,
dbo.PM00200.VENDNAME
FROM dbo.PM20000 INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM20000.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED HISTORY-------------------------
SELECT
CASE
WHEN dbo.PM30200.DOCTYPE=1 THEN 'INVOICE POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=3 THEN 'MIS CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=4 THEN 'RETURN POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=5 THEN 'CREDIT MEMO POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=6 THEN 'PAYMENT POSTED HISTORY'
END AS TRXSOURCE,
dbo.PM30200.DOCDATE,
dbo.PM30200.DOCNUMBR AS DOCNUMBR,
dbo.PM30200.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,
dbo.PM00200.VENDNAME
FROM dbo.PM30200
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM30200.VENDORID
WHERE PM30200.VOIDED = 0
---------------------------------------------------------------
UNION ALL
----------PM UNPOSTED-----------------------------------------------
SELECT
CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN 'INVOICE UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=2 THEN 'FINANCE CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=3 THEN 'MIS CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=4 THEN 'RETURN UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=5 THEN 'CREDIT MEMO UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=6 THEN 'PAYMENT UNPOSTED'
END AS TRXSOURCE,
dbo.PM10000.DOCDATE,
dbo.PM10000.DOCNUMBR AS DOCNUMBR,
dbo.PM10000.VENDORID,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=2 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=3 THEN dbo.PM10000.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=4 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=5 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=6 THEN dbo.PM10000.DOCAMNT END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10000
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10000.VENDORID

Hope that this helps!

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