Jordev

Filter Dynamics GP Lookups Using VBA

Daoudm — Tue, 18 Nov 2008 20:04:00 GMT

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.

Filling Missing Transactions Numbers in GP

Daoudm — Tue, 11 Nov 2008 22:01:00 GMT

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.

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

Daoudm — Tue, 11 Nov 2008 19:58:00 GMT

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.

Check Sources below:

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

DEX.INI Commands Explanation

Daoudm — Tue, 11 Nov 2008 16:43:00 GMT

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=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE - Turn on Dex SQL Logging (all 3 lines)
ShowAdvancedMacroMenu=TRUE - Turn on the Advanced Macro Menu
C:\DPS1\DEX.INI DPSInstance=1
C:\DPS2\DEX.INI DPSInstance=2
C:\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.EXE
IMExecPath=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).

GL Query For Posted and Unposted GL Transactions

Daoudm — Tue, 11 Nov 2008 16:34:00 GMT

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) */

Vendor Statement For GP

Daoudm — Tue, 11 Nov 2008 16:34:00 GMT

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 INNER
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

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

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

Dynamically Get GP database connection information when using VBA

Daoudm — Tue, 11 Nov 2008 16:33:00 GMT

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.

Drop and recreate database Logins

Daoudm — Tue, 11 Nov 2008 16:32:00 GMT

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.

Item Transactions Analysis

Daoudm — Tue, 11 Nov 2008 16:30:00 GMT

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)

List of inventory items that have not sold between two dates

Daoudm — Tue, 11 Nov 2008 16:26:00 GMT

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)

Microsoft Dynamics GP (Great Plains) Row Level Filtering (Row Level Security)

Daoudm — Mon, 13 Oct 2008 12:34:00 GMT

Guys,

David Musgrave the Microsoft Dynamics GP Specialist proofed that the GP could have Row Level Security customized by developing the tool for the sample company, the tool is great and contains all what we need to filter GP rows.

You can find this tool under the following link:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/08/13/row-level-filtering-proof-of-concept.aspx?CommentPosted=true

But unfortunately the tool is still proof of concept, and not officially released, lets hope to have it one day as one of GP modules!

Regards,

Auto login for Microsoft Dynamics GP!

Daoudm — Sun, 12 Oct 2008 12:44:00 GMT

Auto login for Microsoft Dynamics GP

I found the below idea in one of the forums and liked it!

1. Create new text file in “C:\Program Files\Microsoft Dynamics\GP” call it Login.mac

2. Paste the code below inside it and save.

3. Replace “sa” with your username.

4. Replace “123” with your password.

5. Replace “1” in “'(L) Company Names' item 1 # ''” with the company order of your companies selection list

6. Save the file.

7. Go to GP shortcut, right click and properties.

8. Change the target of the shortcut to include “Login.mac”

Old: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set

New: "C:\Program Files\Microsoft Dynamics\GP$OULABI\Dynamics.exe" Dynamics.set Login.mac

9. Open GP from this shortcut.

Logging file 'macro.log'

CheckActiveWin dictionary 'default' form Login window Login

MoveTo field 'User ID'

TypeTo field 'User ID' , 'sa'

MoveTo field Password

TypeTo field Password , '123'

MoveTo field 'OK Button'

ClickHit field 'OK Button'

NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'

ClickHit field '(L) Company Names' item 1 # ''

MoveTo field 'OK Button'

ClickHit field 'OK Button'

CommandExec dictionary 'default' form 'Command_System' command CloseAllWindows

ActivateWindow dictionary 'default' form Toolbar window 'Main_Menu_1'

Fix for Slow Correcting JE Lookups

Daoudm — Sat, 11 Oct 2008 22:24:00 GMT

Fix for Slow Correcting JE Lookups

I found the following post @ Mark's [MVP] blog and thought it will be helpful!

When you do a correcting Journal Entry in GP, the lookup to find the entry to correct can be brutally slow. it's so slow because it's looking up every journal entry you've done in, like, forever. It's so torturously slow that that the CIA is considering it for use at Guantanamo.

Well, today Jivtesh points us to a Microsoft Knowledge Base article that can help fix that. KB 925326 provides a small SQL script that adds a non-clustered index to the GL20000 and GL30000 tables.

That should help speed things up a bit. Of course, if you simply never made any mistakes, it would never be a problem.

Posted by Mark 7/10/2008 04:14:00 PM

http://msdynamicsgp.blogspot.com/2008/07/fix-for-slow-correcting-je-lookups.html

Copy Microsoft Dynamics GP Setup

Daoudm — Sat, 11 Oct 2008 12:59:00 GMT

To copy Microsoft Dynamics GP setup from company to another company DTS the following tables via SQL

Finance
GL00100 Chart of Accounts
Note Do not copy the GL00101
GL00102 Account Category Master
GL00103 Fixed Allocation Accounts
GL00104 Variable Allocation Accounts
GL00105 Account Index Master
CM00100 Checkbook Master
CM40100 Cash Management Setup
CM40101 Cash Management Transaction Type Setup
GL00200 Budget Master file
GL00201 Budget Master file
GL40000 General Ledger Setup
GL40100 Quick Journal Setup
GL40101 Quick Journal Setup
GL40200 Segment Description Master
SY04100 Bank Master
MC40000 Multicurrency Setup
MC40100 Multicurrency Setup
ASI*.* Advanced Lookup Files

Purchase
PM00100 PM Class Master
PM00101 Vendor Class Accounts
PM00200 Vendor Master
PM00203 Vendor Accounts
PM00300 Address Master
PM40100 PM Setup File
PM40101 PM Setup File
PM40102 PM Setup File
PM40103 PM Setup File
POP00101 Buyer Master
POP40100 Purchasing Setup File
POP40600 Purchasing Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Sales
IVC40100 Invoicing Setup
IVC40101 Invoicing Setup
RM00101 Customer Master
RM00102 Customer Master Address
RM00105 National Accounts Master
RM00201 Class Master
RM00301 Salesperson Master files
RM00303 Sales Territory Master
RM40101 RM Setup
RM40201 RM Period Setup
RM40401 RM Document Type Setup
SOP00100 Process Holds Master
SOP00200 Prospect Master
SOP40100 Sales Setup
SOP40200 Type ID setup
SOP40201 Default Process Holds Setup
SOP40300 Document Setup
SOP40400 User Defined Table Setup
SOP40500 Master Number Setup
SOP40600 Non-IV Item Currency Setup
ASI*.* Advanced Lookup Files

Inventory
BM00101 Bill of Materials Header
BM00111 Bill of Materials Component
BM40100 Bill of Materials Setup
IV00101 Item Master
IV00102 Item Quantity Master
IV00103 Vendor Master
IV00104 Kit Master
IV00105 Item Currency Master
IV00106 Item Purchasing
IV00107 Item Price List Options
IV00108 Item Price List
IV00109 Item Serial Number Mask
IV40100 Inventory Control Setup
IV40201 U of M Setup
IV40202 U of M Setup
IV40400 Item Class Setup
IV40401 Item Class Currency Setup
IV40500 Item Lot Category Setup
IV40600 Item Category Setup
IV40700 Item Site Setup
IV40800 Price Level Setup Company Folder
IV40900 Price Group Master
IV41000 Stock Calendar
IV41001 Stock Calendar Exception Days
ASI*.* Advanced Lookup Files

Company
SY00300 Account Format Setup
SY01100 Posting Account Master
SY02200 Posting Journal Destinations
SY02300 Posting Setup
SY03000 Shipping Methods
SY03100 Credit Card Setup
SY03300 Payment Terms Setup
SY40100 Fiscal Period Setup
SY40101 Fiscal Period Setup
TX00101 Tax Schedule Header Master
TX00102 Tax Schedule Master
TX00201 Tax Master
STN*.* Named Printers Setup
ASI*.* Dynamics Explorer Files

U.S. Payroll
When you copy payroll setup information from one company to another company,
the following setup tables are used:

UPR40100 Payroll Unemployment Setup
UPR40101 Payroll Unemployment TSA
UPR40200 SetupUPR40300 Department Setup
UPR40301 Job Title Setup
UPR40500 Accounts Setup
UPR40501 Tax Expense/Withholding Setup
UPR40600 Pay Code Setup
UPR40700 Workers Comp Setup
UPR40800 Benefit Setup
UPR40801 Benefit Based On Setup
UPR40900 Deduction Setup
UPR40901 Deduction Based On Setup
UPR40902 Deduction Sequence Setup
UPR41100 State Code Setup
UPR41200 Class Setup
UPR41201 Class Detail Setup
UPR41400 Local Tax Setup
UPR41401 Local Tax Table Setup
UPR41500 Shift Code Setup
UPR41700 Supervisor Setup
UPR41800 Payroll Maximum Deduction Setup (only in Microsoft Dynamics GP 10.0)
UPR41801 Payroll State/Fed Setup (only in Microsoft Dynamics GP 10.0)
UPR41900 Payroll Earnings Setup (only in Microsoft Dynamics GP 10.0)
UPR41901 Payroll Earnings Paycode (only in Microsoft Dynamics GP 10.0)
UPR41902 Payroll Earnings Deductions (only in Microsoft Dynamics GP 10.0)

Note If you copy the UPR40500 file, the posting accounts will be identical
to those of the company that you are copying.

Payroll Extensions (deduction in arrears, payables integration to payroll,
overtime rate manager)
ORM_UPR_SETP_OT_DTL
ORM_UPR_SETP_OT_HDR
UPR40600_OT
APR_DIA40100
APR_DIA40200
APR_UPR40500
APR_UPR40900
APR_PIP40100

Advanced Payroll
APR40600
APR41100
APR41101
APR41501
APR41601
APR_APR70901
APR_APR70900
APR_UPR40500
APR_APR40101
APR_APR40100

Canadian Payroll
CPY10010 CDN Payroll Employer Master
CPY10020 CDN Payroll Department Master
CPY10030 CDN Payroll Employee Job Titles
CPY10050 CDN Payroll Employee Class
CPY10051 CDN Payroll Class Attached Pay codes File
CPY10060 CDN Payroll Pay code Master
CPY10061 CDN Payroll Pay code Attached Pay codes
CPY10062 CDN Payroll Income Attached Pay Codes
CPY10063 CDN Payroll Rate Table Codes
CPY10064 CDN Payroll Rate Tables
CPY10070 CDN Payroll WCB Master
CPY10075 CDN Payroll WCB Administration
CPY10080 CDN Payroll User Paid By
CPY10081 CDN Payroll User Drop Down Strings
CPY10082 CDN Payroll Reporting Codes
CPY10170 CDN Payroll Employee Unions
CPY10171 CDN Payroll UnionAttached Pay codes
CPY20200 CDN Payroll Job Master
CPY20201 CDN Payroll Phase Master
CPY20700 P_Security_Group_MSTR
CPY20705 P_Security_Group_Detail
CPY20710 P_Security_User_MSTR

If the following information is the same, you can also copy these files:
CPY20100 CDN Payroll Control Master
CPY20110 CDN Payroll CSB Setup Information
CPY20111 CDN Payroll CSB Pay codes

Human Resource
BE020230 HR_Benefit_SETP
BE021030 BEN2_FMLA_Line
BE031000 BEN_FMLA_INFO
HR2Ben21 HR_Benefit_Tiers_SETP
HR2Ben11 HR_Benefit_Fund
HR2Ben12 HR_Benefit_MDVE_Table
HR2Ben13 HR_Benefit_Life_Premiums
HR2Ben14 HR_Venefit_MDVE_Types
HR2Div02 HR_Division2
HR2Tra01 HR_Train_Course
HR2Tra03 HR_Train_Class
HRCom022 HR_Company2_extra
HRDep022 HR_Department2_Extra
HRDiv022 HR_Division2_Extra
HRPBen05 HRP_BEN_FMLA_Set12Month
HRPro022 HR_Property
HRPppc01 HRP_Position_Pay_Code
HRsax012 HR_Salary_Matrix
HRsax022 HR_Salary_Matrix_Table
HRsax042 HR_Salary_Matrix_Col
HRsax032 HR Salary Matrix rows
HRtra042 HR_Train_Class_Skills
HRtrpc02 HR_Train_Position_Course_Class
HRtrps01 HR_Train_Position_Course
RV010221 HR_Review_LINE_V2
RV020221 HR_Review_Setup_LINE_V2
RV030221 HR_Review_Words_Setup_LINE
SK010230 HR_Skills_Line
TAAC0130 TA_SETP_Accrual_Type
TAPY0130 TA_Payroll_Link
Note The TAPY0130 TA_Payroll_Link table was removed in Microsoft Dynamics GP
9.0 and in Microsoft Dynamics GP 10.0.
TAST0130 TA_Setup
TAST0230 TA_Attendance_reason
TAST0330 TA_Attendance_Types
TAST0532 TA_Pay_Period_accrual_LINE
TATM0130 TA_SETP_Types

Advanced Human Resource
APR_BLM41500
APR_BLM41501
APR_BLM41600
APR_BLM41601
APR_BLM41400
APR_BLM41401
APR_BLM41100
APR_BLM41101
APR_BLM41300
APR_BLM41301
APR_BLM41200
APR_BLM41201
APR_BLM42100
APR_BLM42101
APR_BLM42200
APR_BLM42201
APR_BLM43100
APR_BLM43200
APR_BLM43201
APR_BLM43300
APR_BLM43301
APR_APR40500
EHW40100
EHW40201
EHW40200
EHW40300
EHW40400
EHW40501
EHW40500
CLM40100
CLM40300
CLM40700
CLM40701
CLM40600
CLM40500
CLM40400
CLM40200

PTO Manager
PTO40100
PTO40101
PTO40200
PTO40201

Posted Orginally by MicrosoftSlave in Microsoft Dynamics Forum.

Why does Microsoft Dynamics GP encrypt passwords?

Daoudm — Mon, 06 Oct 2008 22:17:00 GMT

Why does Microsoft Dynamics GP encrypt passwords?

Check post below:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx

Microsoft Dynamics GP: You are using an earlier version of Dynamics.dic than the one that's currently installed on the network!!

Daoudm — Fri, 22 Aug 2008 13:39:00 GMT

Microsoft Dynamics Great Plains:

You are using an earlier version of Dynamics.dic than the one that's currently installed on the network!!

Cause

I had both GP 9.0 and GP10.0 installed on the same machine for testing purposes, by mistake I have logged in by using GP 10.0 into the ODBC that pointed to GP 9.0 database, it returned an error that the database need to be upgraded, which is fair.

I returned to GP 9.0 and guess what I got? You are using an earlier version of Dynamics.dic than the one that's currently installed on the network!!

It's a wired problem, since GP should not perform any operations on the database without using GP utilities! I checked the partner source for any help and searched the internet with no success.

Therefore I had to trace GP database operations step by step to check where it stuck, I found it changing table "DU000020" in DYNAMICS database and updates the first line that indicates product ID "0" which is the Dynamics dictionary and changes its version Major and Version Build

Solution:

Run the following script to update the version:

UPDATE DU000020 SET VersionMajor = (Select VersionMajor From DU000020 Where PRODID = 309 AND CompanyID = -32767), VersionBuild = (Select VersionBuild From DU000020 Where PRODID = 309 AND CompanyID = -32767) WHERE PRODID = 0 AND CompanyID = -32767

Have Fun

Convert Numbers To Arabic Words (Tafqeet)

Daoudm — Tue, 01 Jul 2008 06:24:00 GMT

Function to Convert Numbers To Arabic Words (Tafqeet):

You Can Call This Function By:

Select Tafkeet(9875)

Code Below:

CREATE FUNCTION dbo.Tafkeet (@TheNo numeric(18,3))
returns varchar(1000) as
begin
if @TheNo <= 0   return 'zero'
declare @TheNoAfterReplicate varchar(15)
set @TheNoAfterReplicate = right(replicate('0',15)+cast(floor(@TheNo) as varchar(15)),15)
declare @ComWithWord varchar(1000),@TheNoWithDecimal as varchar(400),@ThreeWords as int
set @ThreeWords=0
set @ComWithWord = ' فقط '
declare   @Tafket TABLE (num int, NoName varchar(100))
INSERT INTO @Tafket VALUES (0,'')
INSERT INTO @Tafket VALUES (1,'واحد')
INSERT INTO @Tafket VALUES (2,'اثنان')
INSERT INTO @Tafket VALUES (3,'ثلاثة')
INSERT INTO @Tafket VALUES (4,'اربعة')
INSERT INTO @Tafket VALUES (5,'خمسة')
INSERT INTO @Tafket VALUES (6,'ستة')
INSERT INTO @Tafket VALUES (7,'سبعة')
INSERT INTO @Tafket VALUES (8,'ثمانية')
INSERT INTO @Tafket VALUES (9,'تسعة')
INSERT INTO @Tafket VALUES (10,'عشرة')
INSERT INTO @Tafket VALUES (11,'احدى عشر')
INSERT INTO @Tafket VALUES (12,'اثنى عشر')
INSERT INTO @Tafket VALUES (13,'ثلاثة عشر')
INSERT INTO @Tafket VALUES (14,'اربعة عشر')
INSERT INTO @Tafket VALUES (15,'خمسة عشر')
INSERT INTO @Tafket VALUES (16,'ستة عشر')
INSERT INTO @Tafket VALUES (17,'سبعة عشر')
INSERT INTO @Tafket VALUES (18,'ثمانية عشر')
INSERT INTO @Tafket VALUES (19,'تسعة عشر')
INSERT INTO @Tafket VALUES (20,'عشرون')
INSERT INTO @Tafket VALUES (30,'ثلاثون')
INSERT INTO @Tafket VALUES (40,'اربعون')
INSERT INTO @Tafket VALUES (50,'خمسون')
INSERT INTO @Tafket VALUES (60,'ستون')
INSERT INTO @Tafket VALUES (70,'سبعون')
INSERT INTO @Tafket VALUES (80,'ثمانون')
INSERT INTO @Tafket VALUES (90,'تسعون')
INSERT INTO @Tafket VALUES (100,'مائة')
INSERT INTO @Tafket VALUES (200,'مائتان')
INSERT INTO @Tafket VALUES (300,'ثلاثمائة')
INSERT INTO @Tafket VALUES (400,'أربعمائة')
INSERT INTO @Tafket VALUES (500,'خمسمائة')
INSERT INTO @Tafket VALUES (600,'ستمائة')
INSERT INTO @Tafket VALUES (700,'سبعمائة')
INSERT INTO @Tafket VALUES (800,'ثمانمائة')
INSERT INTO @Tafket VALUES (900,'تسعمائة')
INSERT INTO @Tafket SELECT FirstN.num+LasteN.num,LasteN.NoName+' و '+FirstN.NoName
FROM (SELECT * FROM @Tafket WHERE num BETWEEN 20 AND 90) FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 9) LasteN
INSERT INTO @Tafket SELECT FirstN.num+LasteN.num,FirstN.NoName+' و '+LasteN.NoName
FROM (SELECT * FROM @Tafket WHERE num BETWEEN 100 AND 900) FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 99) LasteN
if left(@TheNoAfterReplicate,3) > 0
    set @ComWithWord = @ComWithWord + ISNULL((select NoName from @Tafket where num=left(@TheNoAfterReplicate,3)),'')+ ' ترليون'
if left(right(@TheNoAfterReplicate,12),3) > 0 and left(@TheNoAfterReplicate,3) > 0
set @ComWithWord=@ComWithWord+ ' و '
if left(right(@TheNoAfterReplicate,12),3) > 0
    set @ComWithWord = @ComWithWord +ISNULL((select NoName from @Tafket where num=left(right(@TheNoAfterReplicate,12),3)),'') + ' بليون'
if left(right(@TheNoAfterReplicate,9),3) > 0
begin
    set @ComWithWord=@ComWithWord + case when @TheNo>999000000 then ' و' else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,9),3)
       set @ComWithWord = @ComWithWord + ISNULL((select case when   @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,9),3)),'') + case when @ThreeWords=2 then ' مليونان' when   @ThreeWords between 3 and 10 then ' ملايين' else ' مليون' end
end
if left(right(@TheNoAfterReplicate,6),3) > 0
begin
    set @ComWithWord=@ComWithWord + case when @TheNo>999000 then ' و' else '' end
    set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
    set @ComWithWord = @ComWithWord + ISNULL((select case when @ThreeWords>2 then NoName end from @Tafket where num=left(right(@TheNoAfterReplicate,6),3)),'')+ case when @ThreeWords=2 then ' الفان' when @ThreeWords between 3 and 10 then ' الاف' else ' الف' end
end
if right(@TheNoAfterReplicate,3) > 0
begin
if @TheNo>999
begin
set @ComWithWord=@ComWithWord + ' و'
end
end
set @ThreeWords=right(@TheNoAfterReplicate,2)
set @ComWithWord = @ComWithWord +   ISNULL((select NoName from @Tafket where @ThreeWords>2 AND num=right(@TheNoAfterReplicate,3)),'')
set @ComWithWord = @ComWithWord +' '+ case when @ThreeWords=2 then ' ريالان' when @ThreeWords between 3 and 10 then ' ريالات' else ' ريالا' end
if right(rtrim(@ComWithWord),1)=',' set @ComWithWord = substring(@ComWithWord,1,len(@ComWithWord)-1)
if right(@TheNo,len(@TheNo)-charindex('.',@TheNo)) >0 and charindex('.',@TheNo)<>0
begin
  set @ThreeWords=left(right(round(@TheNo,2),3),2)
  SELECT @TheNoWithDecimal= ' و' + ISNULL((SELECT NoName from @Tafket where num=left(right(round(@TheNo,2),3),2) AND @ThreeWords >2),'')
  set @TheNoWithDecimal = @TheNoWithDecimal+ case when @ThreeWords=2 then ' هللتان' when @ThreeWords between 3 and 10 then ' هللات' else ' هللة' end
set @ComWithWord = @ComWithWord + @TheNoWithDecimal
END
set @ComWithWord = @ComWithWord + ' سعودي لا غير'
return rtrim(@ComWithWord)
end

How to search all columns of all tables in a database for a keyword?

Daoudm — Tue, 01 Jul 2008 06:15:00 GMT

How to search all columns of all tables in a database for a keyword?

BY http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

This procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:


CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT


	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

Microsoft Worldwide Telescope is Out - Download Now

Najeeb Jarrar — Tue, 13 May 2008 16:13:00 GMT

A couple of hours ago Microsoft Research released the first public beta of Worldwide Telescope since it’s debut last month. The software is a seamless combination of various state-of-the-art Microsoft technologies including Live Lab’s Photosynth and Seadragon with of course the magical high-resolution pictures from numerous imaging satellites including the Hubble. Those who’ve played with it already will probably agree with how breathtaking the experience is.

Microsoft offer Pro Photo Tools for free

Najeeb Jarrar — Thu, 01 May 2008 12:48:00 GMT

Microsoft likes digital photography enthusiasts as customers, and on Thursday plans to release a free new utility designed to keep them wedded to Windows.

Pro Photo Tools is geared for photography professionals and enthusiasts, and its first notable feature is the ability to geotag photos, or add geographic information showing where the picture was taken. Geotagging is an onerous chore with today's technology, but camera makers are working to build it into cameras, and it can pay off down the road.

Link: CNet

Office Search command is available!! (for office 2007)

Najeeb Jarrar — Mon, 28 Apr 2008 21:02:00 GMT

Search Commands helps you find commands, options, wizards, and galleries in Microsoft Office 2007 Word, Excel and PowerPoint. Just type what you’re looking for in your own words and click the command you need. Search Commands also includes Guided Help, which acts as a tour guide for specific tasks.

Even if the ribbon interface is great...sometimes we search for some feature & tats the time this helps!

Please note, this is only supported on English 32 & 64 bit versions of Microsoft Office 2007 Word, Excel and PowerPoint.

Download: Office 2007 Search Command ) @ Microsoft Office Labs

Jordev 2008 Servers and Tools Community Lanuch Invitation :

Najeeb Jarrar — Mon, 28 Apr 2008 11:23:00 GMT

You have been invited to the 2008 Servers and Tools Community Launch on Wednesday , April 30^th, 2008.

Click to view full size

Interact with the Professionals around you, learn something new, and enjoy your time hanging out with the Community, this Wednesday at the Princess Sumaya University for Technology (PSUT) from 6:00 PM - 8:30 PM!

NOTE: This session will be in conjunction with the Jordan SharePoint User Group Monthly Meeting

For Directions to the Princess Sumaya University for Technology, Click Here.

For more Information please contact:

Ayman Farouk @ 0795727344

Najeeb Jarrar @ 0788630145

We'll be looking forward to seeing you there!

It’s time to introduce the world to IT24-7!

Najeeb Jarrar — Thu, 17 Apr 2008 20:40:00 GMT

We are excited to introduce “The Server Unleashed” ad campaign featuring Windows Server 2008 personified as a robot delivering super-human reliability, stability, management and security. Just as Windows Server is not your average server, this is not your average campaign. The website provides great resources like case studies, product information and downloads and even cool screensavers….check it out!

Why “IT24-7?”

Across IT infrastructures, both integration and consolidation are happening at ever increasing rates. This can bring huge benefits for IT Pros and enterprises, but it also puts great pressure on the server OS to deliver unprecedented levels of security, manageability and reliability. As the infrastructure becomes more interconnected, the OS must deliver rock-solid reliability.

Windows Server 2008 is up to the challenge. As positioned in our integrated campaign, Windows Server 2008 is the innovative must-have solution to meet the stringent new demands of the changing IT infrastructure. Windows Server 2008, with its significant improvements and innovations, delivers the game-changing reliability needed to run a changing IT infrastructure.

To bring this to life we created the Server Unleashed campaign which rests squarely on the strong shoulders of the “IT24-7” Robot, who embodies innovation, strength, and unstoppable reliability. The IT24-7 robot is the server’s potential unleashed. It is a server OS that finally combines the forward-thinking features that IT Pros want with the never-failing reliability that they need.

More @ Source

Release dates for Windows XP SP3

Najeeb Jarrar — Tue, 15 Apr 2008 17:46:00 GMT

With Service Pack 3 for Windows XP just over the horizon, we’ve managed to get our hands on the internal schedule for the release of the highly anticipated update to the aging operating system. As you can see in the list below, most of the stages will occur before the end of the month, though forced automatic updates won’t land until June. This delay should give system administrators an ample amount of time to prepare for the upgrade or simply come up with an excuse when things go awry.

April 14, 2008: Support is available for the release version of Service Pack 3 for Windows XP
April 21, 2008: Original Equipment Manufacturers, Volume License, Connect, and MSDN and TechNet subscribers
April 29, 2008: Microsoft Update, Windows Update, Download Center
June 10, 2008: Automatic Updates

More details about Service Pack 3 for Windows XP available in PDF at Microsoft’s site.

Free version of Microsoft's robotics platform available now

Najeeb Jarrar — Fri, 11 Apr 2008 15:23:00 GMT

Microsoft has released its first preview of Microsoft Robotics Developer Studio 2008, a re-named and re-designed version of its 2006 robotics studio software.

Microsoft Robotics Studio 06, according to the company, had over 200,000 copies downloaded and over 50 companies in the partner program. Today's update was unveiled at the Robobusiness Conference and Exposition in Pittsburgh, the same conference that hosted the premiere of the earlier version.

This version promises better runtime performance, improved distributed computing capabilities with LINQ support, and some new tools. The virtual simulation environment (VSE), for example, received the ability to record and playback simulations and a new floorplan editor.

The software is freely downloadable for noncommercial applications, and with its availability, Microsoft also announced a virtual robotics competition launching on April 21.

Robochamps (site requires Silverlight) will see Microsoft paired up with SimplySim in getting users of all skill levels to experiment using Robotics Developer Studio and simulate their results.

Source