Field Level Security is not working!?!?

 

Few months back, one of my clients reported an issue with the field level security module in Dynamics GP that it only works for “Sa”, and this morning I noticed a question in the community reminding me to post about this issue! Below are the circumstances:

1. Only the sa user can see the module on GP even by giving the power user to other users.

2. The restrictions made on the field level security module are applied only to sa user.

Solution clearly shows that this is not an issue with the application and it is an issue with database privileges, simply go to “C:\Program Files\Microsoft Dynamics\GP2010\SQL\Util” and locate a script called “Grant.SQL”, the script will grant the DYNGRP role in SQL the access on all objects in the database and will resolve your issue, below the script if needed:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN
G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC
(@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE
G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where type = 'P'

set nocount on
OPEN
G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC
(@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE
G_cursor

Regards,

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

Advertisements

Microsoft Dynamics Salary Survey 2012

 

Banner Large

Nigel Frank International would like to invite you to complete our annual survey of global Microsoft Dynamics salaries. The survey will only take a couple of minutes to complete and your response and any personal details will be kept strictly confidential.

Complete the survey by the closing date and you will automatically be entered into our prize draw to win one of five amazing prizes:

1st Prize = Apple iPad2 16gb with Wi-Fi + 3G

2nd Prize = Microsoft Xbox 360 250gb + Kinect

3rd Prize = Kindle Keyboard with Free 3G + Wi-Fi

4th Prize = Microsoft LifeCam Studio Webcam

5th Prize = Microsoft Arc Touch Mouse

You will also receive a FREE copy of the Salary Survey report once it has been compiled.

Please click on the following link to complete the Microsoft Dynamics Salary Survey 2012:

http://survey.nigelfrank.com/en/microsoft-dynamics-salary-survey-2012.html

The survey is available in 12 languages for your convenience.


Regards,

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

How to change SQL Collation for SQL Server 2008 R2

 

Have you ever been in a situation where you wanted to change the SQL Collation to match SQL Database Collation?? Last year I have posted a script that resolves this for SQL Server 2005, but the same syntax is not applicable for SQL 2008 R2, while investigating this I found the correct syntax that need to be used as below:

E:\SQL\setup.exe” /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS=”DAOUD-PC\DAOUD

E:\SQL\setup.exe will need to be replaced with the locations of the SQL Server 2008 R2 setup.exe path.

MSSQLSERVER represents the default instance, if you have a named instance this must be changed to hold the instance name.

P@ssW0rd: is the new SA user password, it must be strong password or otherwise the repair will fail.

Arabic_CI_AI: is the new collation name.

DAOUD-PC\DAOUD need to be replaced with the local administrator of the server or PC you are installing the SQL on.

Paste above command into CMD and wait for 3-4 minutes, you will have your SQL Collation changed.

Warning: this operation will recreate your master database, were all existing setting will be reset, if you had databases attached it will be de-attached and will be found in the database folder.


Regards,

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

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

GL Summary Reconcile

 

One of my clients requested to reconcile their GL Account Summary to GL Account Details periodically using SQL which is not currently available in Dynamics GP, I suggested to schedule a macro run but they wanted this to be done at the SQL level, therefore I have written the SQL command below to empty GL10110 table and repopulate the records to the tables using figures in GL20000.

Find the script below or download it from this location.

Disclaimer: This script works when your fiscal periods are matched with calendar periods only as it uses month of the transaction date as the transaction period, on the other hand, I tested this on limited data, if you find an issue or have any idea for script enhancement, please let feel free to comment.


Regards,

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mohammad R. Daoud
-- Create date: 2012-01-19
-- Description: Procedure to reconcile GL Summary
-- =============================================
CREATE PROCEDURE DI_GLRECONCILE

AS
BEGIN
DELETE FROM
[GL10110]

INSERT INTO [GL10110]
([ACTINDX]
,[YEAR1]
,[PERIODID]
,[PERDBLNC]
,[ACTNUMBR_1]
,[ACTNUMBR_2]
,[ACTNUMBR_3]
,[ACTNUMBR_4]
,[ACTNUMBR_5]
,[ACTNUMBR_6]
,[ACTNUMBR_7]
,[ACTNUMBR_8]
,[ACTNUMBR_9]
,[ACTNUMBR_10]
,[ACCATNUM]
,[CRDTAMNT]
,[DEBITAMT])

SELECT dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE) AS YEAR1, MONTH(dbo.GL20000.TRXDATE) AS Period,
SUM(dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT) AS [Period Balance], dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2,
dbo.GL00100.ACTNUMBR_3, dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7,
dbo.GL00100.ACTNUMBR_8, dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, SUM(dbo.GL20000.CRDTAMNT)
AS CRDTAMNT, SUM(dbo.GL20000.DEBITAMT) AS DEBITAMT
FROM dbo.GL20000 INNER JOIN
dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX
GROUP BY dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE), dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2, dbo.GL00100.ACTNUMBR_3,
dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7, dbo.GL00100.ACTNUMBR_8,
dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, MONTH(dbo.GL20000.TRXDATE)
END

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Crystal Report: Sub Report did not show up on client machine

 

I been in a situation where I had to create a report with sub-report in crystal and the report worked perfectly on my machine.

while implementing the report on the client machines I noticed that the sub-report is always empty on all machines and even on the server.

I tried everything with no luck, I even recreated the report with no luck as well, after deep investigations in the issue I have noticed that both Main Report and Sub-Report are using commands and both has the same identical name “command”, changing the command name in the sub-report to “command_1” resolved my issue!!!

Hope that this does not happen to you and helps reducing research time!


Regards,

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

Give me 5–The Dynamics Promotion

 

clip_image0014

 

For those who are still thinking about when to purchase Dynamics ERP’s, I wanted to remind you that the give me 5 promotion which will allow you to purchase 5 users for 3,000$ is around to expire, and you have less than 22 days to take the decision!

Below what quoted from Microsoft Dynamics Website:

Get 5 users for one low price
To renew or not renew, that is the question. Your annual maintenance agreement is right around the corner. You know that the money you spend year after year provides little value and keeps you tied to software that hinders your business growth.

Grow Your Business with Microsoft Dynamics ERP
Microsoft Dynamics ERP gives you greater control over and visibility into your entire business. With greater control, you can feel extra confident about the everyday decisions you and your employees make. Decisions that will help improve profitability, turn margins into cash flow, and ultimately drive your business growth.


Regards,

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