AX 2009 SalesLineDelete Error

*-*-* Requires access to run SQL syntax against the database.*-*-*

Problem:
The user is attempting to remove a line from an SO but is prevented from doing so due to an error stating that the record already exist within the SalesLineDelete table.

Required Information:
- Lot ID of the SO line.
This is found by right click>> SO Line in question >> select Record Info>> select Show All Fields.
Under the inventory section you will see an ID for "Lot ID".
-SO #

SQL Syntax:
-- DELETE FROM SALESLINEDELETE WHERE SALESID = '#' and INVENTTRANSID = 'Lot ID'


Why the Lot ID. Within the SalesLineDelete table the SO may have many lines associated to it. The InventTransID (Lot ID) is the unique identifier for the line within the SO that needs to be removed.

Once completed the user can remove the line from the SO using the normal sequence within AX.

Journey to AX 2009 Knowledge

Captains Log STAR DATE unknown.

Recently I moved out of the MS GP world and entered a strange new territory, MS AX2009. This is a completely different ball game now. AX it is my opinion 10 times the size of GP. It requires a lot more resources for support and administration that any other ERP I have ever worked with. The interface is much the same as GP with the exception that it has a lot more going on within the HCI than GP does. From a developer perspective AX has an AOT (application object tree), this tool/function is a good sent to understanding how stuff works. I am currently working on building some custom reports and this AOT is like a bible for out AX2009 implementation.

I will be posting my SQL syntax for a Sale Report by Sales Persons (sales group) showing invoice lines not just invoice total.

Using DEX SQL LOG | DEX.ini

Prequel:

Recently I encountered a problem within GP where if you select a particular link to open a window (eg. Cash Receipts (Transaction --> Sales --> Cash Receipts)) the system would go unresponsive and you will have to close GP using other means (task manager).

Engage Warp Speed:

I was baffled for sometime as to why this was happening so I spoke to a buddy of my mind (Cowmaster - http://cowmasterscorner.blogspot.com/) and he suggested i look at the SQL locks.
He even gave me a sql scipt that will clear the locks:

delete from DYNAMICS..ACTIVITY where USERID not in (select loginame from
master..sysprocesses)delete from tempdb..DEX_SESSION where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK where session_id not in (select SQLSESID from
DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800 where USERID not in (select USERID from
DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801

I used the script but that didnt work.

 

So i decide to try an SQL logging approach (yeh i know you would have done this first.  Thanks for telling me now smart a$$).

To do this logging i used the DEX.ini (C:\Program Files\Dynamics GP\Data) file to engage the SQL logs which will be written to the DEXSQL.log (C:\Program Files\Dynamics GP\Data) file.

How did i engage the logs?

Within the DEX.ini file you can set the following variables to TRUE.

SQLLogSQLStmt=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE  

 

I then just view the DEXSQL.log file and found that my problem is somewhere between the TX30000 and RM00401 tables.

 

Oh an dont forget the set back the DEX.ini files variables back to false.

 

References:

More info on Dex.ini

http://www.meritsolutions.com/meritmatters/index.php?/archives/98-Dex.Ini-Settings-to-Make-Dynamics-GP-More-Comfortable.html 

General Table info

http://victoriayudin.com

MS Dyanmics GP10 Currency Setup

I was recently ask to setup a currency for a particular company. This is how it was done.
This article take for granted the fact that your would have already have setup your Exchange Gain Loss Acct and Financial Multicurrency Setup

Lets Begin!
Navigate to MSDynamics>>Tools>>Setup>>Financial>>Multicurrency Setup

Figure 1.1 Shows the Currency Setup (please note the Default Transaction Rate Type)
"Financial = AVERAGE ; Sales = BUY ; Purchasing = SELL"

Now that your know what are the mapped default rates for each module you now know that any foreign currency you setup much have these Exchange tables available.

Setting up a currency

MSDynamics>>Tools>>Setup>>System>>Currency

Figure 1.2 Shows the Setup for GBP currency. Please note the Accounts (bottom righthand side) need to be placed here.

Once this is completed the exchange table is next. As stated earlier an exchange table needs to be setup for each of your "default transaction rate type" eg. Buy Sell Average

MSDynamics>>Tools>>Setup>>System>>Exchange Table

Figure 1.3 Shows the Multicurrency Exchange Table setup. Please note a vaild rate needs to be enter (bottom righthand side).

Once you have created the currency and setup the exchange table you are almost done. Your last steps are the setup of the default rate types and grant the company access to the currency and associate exchange table.
To grant the company access to the newly created currency and associated exchange tables, all user must exit Dynamics GP and then you navigate to
MSDynamics>>Tools>>Setup>>System>>Multicurrency Access.
(no screenshot available sorry :( )
Within this window select the currency and check the grant privilege and do the same for the listed exchange tables.

Once you have granted the company access to the currency and exchange table click ok then navigate to MSDynamics>>Tools>>Setup>>Financial>>Rate Type

Figure 1.4 Shows the Multicurrency Rate Types. Please note the available rate types and the Selected rate types.

For this GBP BUY to function, I selected the buy rate and moved it to the selected rate type.
Now i can use this GBP currency and GBP BUY exchange table.

Change the Install Location of the TS Web Access Web Site

The Scenario:
I wanted to install TS Web Access but not in the default location (the default website).

Solution:
Install IIS.
Within IIS create a new website

My site is called "labadmin.com".
Please note that the Physical Path is "C:\Windows\Web".
Once the site is create now you move to editing the registry.

Launch the registry editor (regedit)
Create the following registry entry within:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Terminal Server Web Access
Then create a string value "Website" with its data value as "labadmin.com".

Once completed exit registry editor (regedit).

Now we install TS Web Access.

Once you've done everything correctly you should see this



But your not done yet!

Finally you now edit the host file located at "C:\Windows\System32\drivers\etc"

Remember to remove the comments symbol "#".

References are:

http://www.thejoyofcode.com/Setting_up_two_web_sites_in_IIS7.aspx

http://technet.microsoft.com/en-us/library/ff519183(WS.10).aspx

Uploaded!

Well i've submitted. Was is what i thought it would be? Not entirely. There is so much more that could be added. So much more that can be done. But I must now wait and see what it to be.

At present I would refer to it as a Schroedinger Cat. It is both complete and incomplete at the same time.

Ready, Set, GO!

......And were off!.....
Proposal submitted. Timeline drafted. Will i be able to keep with this timeline i have outlined, or will my actual timeline differ from my projected as it normally does.
My investigation into cybercrime prevention and detection begins.