AX Senior Consultant

Monday, July 8, 2013

AX 2012 Resource Pages

Microsoft Dynamics AX Resource Pages

http://community.dynamics.com/ax/b/axresources/default.aspx

AX 2012 Data Tables & Table Groups

MS Dynamics AX 2012 Standard에서 사용중인 Tables과 Table Groups 목록을 볼 수 있는 Excel Sheet 파일입니다.

총 4584개의 테이블이 있는데, R2에는 더 추가가 되었겠죠.

http://www.microsoft.com/en-us/download/details.aspx?id=17093

Table type	(All)
System table	(All)
Visible	(All)
Shared? (SaveDataPerCompany = No)	(All)

Row Labels	Count of Table name
Framework	199
Group	778
Main	836
Miscellaneous	1148
Parameter	205
Reference	242
Transaction	654
TransactionHeader	24
TransactionLine	41
Worksheet	111
WorksheetHeader	112
WorksheetLine	234
Grand Total	4584

Friday, July 5, 2013

AX 2012 Enterprise Portal

아래 화면은 Sharepoint 2010 기반의 AX 2012 R2 Enterprise Portal 화면입니다.

상단의 Top Navigation 메뉴들은 아래와 같이 AX 모듈별로 나누어져 있습니다.

- HOME

- Procurement Sales

- Compliance

- Project management

- Service management

- Vendor portal

- Customer self-service

- Budgeting

- Retail

AX 전 모듈에서 웹상으로 봐야 할 리포트나 기본적인 트랜잭션이 필요한 화면들이 기본적으로 제공이 되지만, 매력적인건 이런 기능을 추가하거나 커스터마이징이 가능하는 것이죠.

아직은 프로젝트에 적용하기 위해 스터디 중이지만, 조만간 담당하고 있는 모듈의 EDD(Enterprise Design Document) 작성이 마무리 되면, 프로젝트에서 나온 요구사항을 반영하는 Vendor portal 개발 작업에 들어간답니다.

작업하면서 깨우치거나 느끼는 부분에 대해서는 Blog에 남기도록 하겠습니다.

Wednesday, July 3, 2013

AX 2012 Database Entity Relationship Diagrams

You can get the AX 2012 ERD from MS AxERD Home.

http://www.microsoft.com/dynamics/ax/erd/ax2012r2/Default.htm

Tuesday, May 7, 2013

Product Item Master Upload 프로그램 설치 가이드

Create ProductItemManagement inbound Service

EcoRes*
InventInventItemLocation*
InventItemBarcodeService*

InventItemService*

Activate Button을 통해서 ProductItemManagement inbound Service 실행

Import Custom inbound Service from XPO file
- AX2012 Client에서 Ctrl + D 를 눌러 New Development Workspace를 실행
- New Development Workspace의 Mainmenu > Command > Import를 실행시켜 PrivateProject_TKRProductItemUploadProject.xpo 를 Import
- 아래와 같이 AOT(Application Object Tree)가 화면에 보이지 않는다면 Ctrl + D를 눌러 AOT 실행

AX2012 > System administrator > Setup > Services and Application Integration Framework > Inbound ports에서 SGCusAddDetailInfo inbound Service가 있는지 확인 및 Activate 시킴

Item Upload 프로그램에서 사용하기 위해 WSDL URI 에 들어가 있는 URI 정보를 notepad등에 복사해 둠

Friday, March 23, 2012

이번엔 찾은 값을 다른값으로 변경해주는 기능까지 포함된 Stored Procedure입니다.
유용하게 쓰셈!

--
CREATE PROC [dbo].[SearchAndReplace]

(
@SearchStr nvarchar(100),
@ReplaceStr 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 and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

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
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

모든 테이블에서 특정값을 찾고 싶다면...

SQL Server Database내의 모든 테이블의 'char', 'varchar', 'nchar', 'nvarchar' 값에 들어있는 특정값을 찾아서 출력해 주는 Stored Procedure입니다.

향후 데이터 변경 프로젝트에서 사용하면 유용할 듯 합니다.

--

CREATE PROC [dbo].[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

DECLARE @Results TABLE(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